How to check if a certain Patch was applied to Oracle Applications instance using 'adpatch'?

There are few methods to check this.

Method 1

Check Patches applied in Oracle Applications Manager (OAM).

a) Connect to OAM:

http://hostname.domain:PORT/servlets/weboam/oam/oamLogin

Go to Site Map-->Maintenance-->Applied Patches

Enter Patch ID and press 'Go'

See if Patch was returned.

Method 2

Use 'adphrept' utility

Patch History report usage:

adphrept query_depth \
bug_number or ALL \
bug_product or ALL \
end_date_from (mm/dd/rr or ALL) \
end_date_to (mm/dd/rr or ALL) \
patchname/ALL \
patchtype/ALL \
level/ALL \
language/ALL \
appltop/ALL \
limit to forms server? (Y/N) \
limit to web server? (Y/N) \
limit to node server? (Y/N) \
limit to admin server? (Y/N) \
only patches that change DB? (Y/N)

Specify 1 or 2 or 3 for query_depth

1-> Details of patches only
2-> Details of patches and their Bug Fixes only
3-> Details of patches their Bug Fixes and Bug Actions

Example: To get the complete patch details for patches applied in Dec 2000:

On UNIX:

$ cd $AD_TOP/patch/115/sql
$ sqlplus /
SQL> @adphrept.sql 3 ALL ALL 12/01/00 12/31/00 ALL ALL ALL ALL ALL N N N N N

Method 3

Use the following sql.

set serveroutput on
DECLARE
TYPE p_patch_array_type is varray(10) of varchar2(10);
--
p_patchlist p_patch_array_type;
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
CURSOR alist IS
select appl_top_id, name
from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.put_line(msg);
end;
--
BEGIN
open alist;
--
p_patchlist:= p_patch_array_type('','');
--
LOOP
FETCH alist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN alist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
for i in 1..p_patchlist.count
loop
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,
p_patchlist(i));
println('..Patch ' || p_patchlist(i) || ' was ' || p_patch_status);
end loop;
END if;
println('.');
END LOOP;
--
close alist;
END;
/


Note: Please enter Patch number in place of and , e.g '3240000'

Example Output

when p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943'):

..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was EXPLICIT
..Patch 4733943 was EXPLICIT
.
dcollierpc8:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was NOT_APPLIED
..Patch 4733943 was NOT_APPLIED

Method 4

You might also use the following query, however the most reliable methods are the described above.

SQL> SELECT DISTINCT RPAD(a.bug_number,
11)|| RPAD(e.patch_name,
11)|| RPAD(TRUNC(c.end_date),
12)|| RPAD(b.applied_flag, 4) BUG_APPLIED
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 in ('','');
ORDER BY 1 DESC;

Note: Please enter Patch number in place of and , e.g '3453499'