Top queries to find Patch applied in Oracle apps

Last updated on August 24th, 2016 at 06:46 pm

We often get the task to check patch applied in oracle apps environment. Here are the Top queries to find Patch applied in ERP

How to find if the bug number or patch is applied to Apps ERP

select substr(APPLICATION_SHORT_NAME,1,10) Product,substr(BUG_NUMBER,1,10),Version,last_update_date applied_date from ad_bugs where BUG_NUMBER= to_char(‘&bug_no’);

How to find the patch level of particular application
select pi.patch_level, Application_short_name from
fnd_product_installations pi, fnd_application fa where fa.application_id
= pi.application_id and Application_short_name like ‘&App_Short_Name’

select patch_level from fnd_product_installations where patch_level LIKE (‘%&1%’);

How to find EWT version

unzip -l $FND_TOP/java/jar/fndewt.jar | grep 3_

How to list out the various information of various product
set linesize 1000
column application_id format a999999 heading “APPL|ID”
column application_short_name format a10 heading “APPL|SHORT NAME”
column application_name format a50 heading “APPLICATION NAME”
column language format a4 heading “LANG”
select a.application_id,
from fnd_application a,
fnd_application_tl at
where at.application_id = a.application_id
and at.language=’US’
group by a.application_id,a.application_short_name,a.basepath,at.application_name,at.language
order by a.application_id

How to find the installed status of all the products
set linesize 1000
column APPS format a10

select decode(nvl(a.APPLICATION_short_name,’Not Found’),
‘Not Found’,’id ‘||to_char(fpi.application_id),
a.APPLICATION_short_name) apps,
‘N’,’Inactive’,fpi.status) status,
nvl(fpi.patch_level,’– Not Available –‘) Patchset,
to_char(fpi.last_update_date,’dd-Mon-RRRR’) “Update Date”
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
and fpi.oracle_id = o.oracle_id(+)
order by 1,2

This script helps in identifying  the various versions of a particular file, the associated BUG NUMBERS, the Date on which it was applied.Input is the FILE NAME whose VERSION is to be determined

set linesize 1000
column filename format a15
column bug_number format a10
column version format a15
set pages 250
set verify off
select a.bug_number,h.filename,g.version,
to_char(c.start_date,’DD-MON-YYYY HH:MI:SS’)start_time,
to_char(c.end_date,’DD-MON-YYYY HH:MI:SS’)end_time,
to_char(to_date(’00:00:00′,’HH24:MI:SS’) +(c.end_date – c.start_date), ‘HH24:MI:SS’) Duration
from ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d,
ad_applied_patches e,
ad_patch_run_bug_actions f,
ad_file_versions g,
ad_files h
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 b.patch_run_bug_id = f.patch_run_bug_id
and f.patch_file_version_id = g.file_version_id
and g.file_id = h.file_id
and h.filename=’&File_Name’
order by c.end_date desc

How to Finds the version information of any Database Package Information

select text
from user_source
where name=’&package_name’ and
text like ‘%$Header%’

You can tell if you’re on OAF 5.7 version OAF 5.10 on the database tier by running the following SQL:

select jdr_mds_internal.getRepositoryVersion from dual;

If it returns something like then you are on OAF 5.7. If it
returns then you are on OAF 5.10.

how to find .o file version

adident Header FNDCRM | grep afpcrm.oc

Leave a Reply