Very Important Script During Upgrade,Patching and Version Checking.

 

Very Important Script During Upgrade,Patching and Version Checking.

 

--1. How to find versions of files in gl workflow?

 

select NAME,TYPE,LINE,text from dba_source

where name LIKE '&PKG_NAME%'                            -- GL_WF_JE_APPROVAL_PKG

and text like '%Header%'

and line = '2';

 

--2. How to find the latest version of a file on a given instance .fmb, .rdf, .pls, .ctl etc?

 

SELECT fi.file_id, filename, version

 FROM apps.ad_files fi, apps.ad_file_versions ve

 WHERE filename LIKE '&file_name_with_extension' AND ve.file_id = fi.file_id AND version =

 (SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id = fi.file_id);

 

--3. How to check for invalid objects in a particular module?

 

Select OWNER, OBJECT_NAME, OBJECT_TYPE,STATUS

 from DBA_OBJECTS

 where

OBJECT_NAME like '&NAME%'--   'CE_%'

and

STATUS = 'INVALID';

 

 

--4. How to check if a patch is applied with Bug Number?

 

SELECT DISTINCT a.bug_number,e.patch_name,

 c.end_date,b.applied_flag

 FROM ad_bugs a,

 ad_patch_run_bugs b,

 ad_patch_runs c,

 ad_patch_drivers d,

 ad_applied_patches e

 WHERE a.bug_id = b.bug_id

 AND b.patch_run_id = c.patch_run_id

 AND c.patch_driver_id = d.patch_driver_id

 AND d.applied_patch_id = e.applied_patch_id

 AND a.bug_number LIKE  :p_bugnumber --'1025000'

 ORDER BY 1 DESC ;

 

 

 

--5. How to find the patch set level for an application?

 

select substr(aa.application_short_name,1,20) "Product", a.patch_level

"Patch Level" from fnd_product_installations a, fnd_application aa

 where a.application_id = aa.application_id

 and aa.application_short_name like '%&short_name%';