How to check which Techstack patchsets have been applied on 11i or R12

How to check which Techstack patchsets have been applied on 11i or R12 ? [ID 390864.1]
Modified 09-FEB-2010 Type HOWTO Status PUBLISHED

In this Document
Goal
Solution
References

Applies to:
Application Install - Version: 11.5.10 to 12.1.2 - Release: to 12.1
Information in this document applies to any platform.
Goal
How to check which Techstack patchsets have been applied
Solution

For Single Tier Release 11i :



SET head off Lines 120 pages 100
col n_patch format A65
col bug_number format A10
col patch_name format A10
spool atg_pf_ptch_level.txt
select ' atg_pf ' FROM dual;
/

select bug_number, decode(bug_number,
'3438354', '11i.ATG_PF.H'
,'4017300' ,'11i.ATG_PF.H.RUP1'
,'4125550' ,'11i.ATG_PF.H.RUP2'
,'4334965' ,'11i.ATG_PF.H RUP3'
,'4676589' ,'11i.ATG_PF.H RUP4'
,'5382500' ,'11i.ATG_PF.H RUP5 HELP'
,'5473858' ,'11i.ATG_PF.H.5'
,'5674941' ,'11i.ATG_PF.H RUP5 SSO Integrat'
,'5903765' ,'11i.ATG_PF.H RUP6'
,'6117031' ,'11i.ATG_PF.H RUP6 SSO 10g Integration'
,'6330890' ,'11i.ATG_PF.H RUP6 HELP'
) n_patch, last_update_date
FROM ad_bugs
WHERE bug_number
IN ( '3438354', '4017300', '4125550', '4334965', '4676589', '5382500', '5473858', '5674941', '5903765', '6117031', '6330890' );



For Multi Tier Release 11i :


set serveroutput on size 100000
DECLARE
TYPE p_patch_array_type is varray(100) of varchar2(10);
TYPE a_abstract_array_type is varray(100) of varchar2(60);
p_patchlist p_patch_array_type;
a_abstract a_abstract_array_type;
p_appltop_name VARCHAR2(50);
p_patch_status VARCHAR2(15);
p_appl_top_id NUMBER;

CURSOR alist_cursor 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_cursor;

p_patchlist := p_patch_array_type( '3438354'
,'4017300'
,'4125550'
,'4334965'
,'4676589'
,'5382500'
,'5473858'
,'5674941'
,'5903765'
,'6117031'
,'6330890'
);
a_abstract := a_abstract_array_type( '11i.ATG_PF.H'
,'11i.ATG_PF.H.RUP1'
,'11i.ATG_PF.H.RUP2'
,'11i.ATG_PF.H RUP3'
,'11i.ATG_PF.H RUP4'
,'11i.ATG_PF.H RUP5 HELP'
,'11i.ATG_PF.H.5'
,'11i.ATG_PF.H RUP5 SSO Integrat'
,'11i.ATG_PF.H RUP6'
,'11i.ATG_PF.H RUP6 SSO 10g Integration'
,'11i.ATG_PF.H RUP6 HELP'
);


LOOP
FETCH alist_cursor INTO p_appl_top_id, p_appltop_name;
EXIT WHEN alist_cursor%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 ' || a_abstract(i)
||' '||p_patchlist(i)||' was '||
p_patch_status);
END LOOP;
END IF;
println('.');
END LOOP;
close alist_cursor;
END;
/

For Single Tier Release 12 :


SET head off Lines 120 pages 100
col n_patch format A65
col bug_number format A10
col patch_name format A10
spool atg_pf_ptch_level.txt
select ' atg_pf ' FROM dual;
/

select bug_number, decode(bug_number,
'6272680', 'R12.ATG_PF.A.delta.4'
,'6077669', 'R12.ATG_PF.A.delta.3'
,'5917344', 'R12.ATG_PF.A.delta.2'
) n_patch, last_update_date
FROM ad_bugs
WHERE bug_number
IN ('6272680', '6077669', '5917344');


For Multi Tier Release R12 :

set serveroutput on size 100000
DECLARE
TYPE p_patch_array_type is varray(100) of varchar2(10);
TYPE a_abstract_array_type is varray(100) of varchar2(60);
p_patchlist p_patch_array_type;
a_abstract a_abstract_array_type;
p_appltop_name VARCHAR2(50);
p_patch_status VARCHAR2(15);
p_appl_top_id NUMBER;

CURSOR alist_cursor 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_cursor;

p_patchlist := p_patch_array_type( '6272680'
,'6077669'
,'5917344'
);
a_abstract := a_abstract_array_type( 'R12.ATG_PF.A.delta.4'
,'R12.ATG_PF.A.delta.3'
,'R12.ATG_PF.A.delta.2'
);


LOOP
FETCH alist_cursor INTO p_appl_top_id, p_appltop_name;
EXIT WHEN alist_cursor%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 ' || a_abstract(i)
||' '||p_patchlist(i)||' was '||
p_patch_status);
END LOOP;
END IF;
println('.');
END LOOP;
close alist_cursor;
END;
/


NB:

For the new coming patches 11i ATG RUP7, or the new R12 patches and so on..., please edit the select script and add the patch number you wish to check