How to find out your Application AD Level?

For 11.5.X Single Node

SET head off Lines 120 pages 100
col n_patch format A65
col bug_number format A10
col patch_name format A10
spool ad_ptch_level.txt
select ' ad ' FROM dual;
/
select bug_number, decode(bug_number
,'1351004' ,'11i.AD.A'
,'1460640' ,'11i.AD.B'
,'1475426' ,'11i.AD.C'
,'1627493' ,'11i.AD.D'
,'1945611' ,'11i.AD.E'
,'2141471' ,'11i.AD.F'
,'2344175' ,'11i.AD.G'
,'2673262' ,'11i.AD.H'
,'4038964' ,'11i.AD.I.1'
,'4229931' ,'11i.AD.I.2'
,'4337683' ,'11i.AD.I.2'
,'4502904' ,'11i.AD.I.3'
,'4605654' ,'11i.AD.I.4 Delta.4'
,'4712847' ,'11i.AD.I.3'
,'4712852' ,'11i.AD.I.4'
,'5161676' ,'11i.AD.I.5'
,'5161680' ,'11i.AD.I.5'
,'6502079' ,'11i.AD.I.6'
,'6502082' ,'11i.AD.I.6'
) n_patch, last_update_date
FROM ad_bugs
WHERE bug_number IN ( '1351004' ,'1460640' ,'1475426' ,'1627493' ,'1945611' ,'2141471' ,'2344175' ,'2673262' ,'4038964' ,'4229931' ,'4337683' ,'4502904' ,'4605654' ,'4712847' ,'4712852' ,'5161676' ,'5161680' ,'6502079' ,'6502082' );

FOR 11.5.x Multi Node

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( '1351004'
,'1460640'
,'1475426'
,'1627493'
,'1945611'
,'2141471'
,'2344175'
,'2673262'
,'4038964'
,'4229931'
,'4337683'
,'4502904'
,'4605654'
,'4712847'
,'4712852'
,'5161676'
,'5161680'
);
a_abstract := a_abstract_array_type( '11i.AD.A'
,'11i.AD.B'
,'11i.AD.C'
,'11i.AD.D'
,'11i.AD.E'
,'11i.AD.F'
,'11i.AD.G'
,'11i.AD.H'
,'11i.AD.I.1'
,'11i.AD.I.2'
,'11i.AD.I.2'
,'11i.AD.I.3'
,'11i.AD.I.4 Delta.4'
,'11i.AD.I.3'
,'11i.AD.I.4'
,'11i.AD.I.5'
,'11i.AD.I.5'
);


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 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( '4502962'
,'5905728'
,'6014659'
);
a_abstract := a_abstract_array_type( 'R12.AD.A'
,'R12.AD.A.1'
,'R12.AD.A.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;
/