pa patches

set serveroutput on size 1000000
spool pa_patches
declare
  l_instance       v$instance.instance_name%type;
  l_host           v$instance.host_name%type;
  l_db_version     v$instance.version%type;
  l_release        fnd_product_groups.release_name%type;
  l_patch_level    fnd_product_installations.patch_level%type;
  l_patch_num      ad_applied_patches.patch_name%type;
  l_patch_name     varchar2(100);
  l_patch_date     date;
  l_apps_affected  varchar2(15);
  l_last_patch     varchar2(200);
  l_last_applied   date;
  cursor get_patch is
  select distinct(patch_name) PatchNum,
         decode(patch_name,
            '3140000', 'Maintenance Pack 11.5.10',
            '3397153', '(11i.PJ_PF.L10) Projects Family Pack L10',
            '3640000', '(11.5.10) Consolidated Update 1',
            '4027334', '(11.5.10) Projects Consolidated Patch for CU1',
            '3480000', '(11.5.10) Consolidated Update 2',
            '3460000', '(11.5.10) Consolidated Update 2',
            '4285356', '(11.5.10) Projects Consolidated Patch for CU2',
            '4667949', '(11i.PJ_PF.L10) Rollup 1',
            '3485155', '(11i.PJ_PF.M) Projects Family Pack M',
            '4461989', '(11i.PJ_PF.M) Rollup',
            '4997599', '(11i.PJ_PF.M) Rollup 2',
            '5105878', '(11i.PJ_PF.M) Rollup 3',
            '5644830', '(11i.PJ_PF.M) Rollup 4',
            '5484000', 'Release Update Pack 12.0.2',
            '6022657', '(R12.PJ_PF.A.DELTA.2) Projects Update Pack 2',
            '6141000', 'Release Update Pack 12.0.3',
            '6266113', '(R12.PJ_PF.A.DELTA.3) Projects Update Pack 3',
            '6435000', 'Release Update Pack 12.0.4',
            '6512963', '(R12.PJ_PF.A.DELTA.4) Projects Update Pack 4',
            '6728000', 'Release Update Pack 12.0.6',
            '7292354', '(R12.PJ_PF.A.DELTA.6) Projects Update Pack 6',
            '7303030', 'Release Update Pack 12.1.1',
            '7456340', '(R12.PJ_PF.B.DELTA.1) Projects Upd Pack 1 for 12.1',
            '7303033', 'Release Update Pack 12.1.2',
            '8504800', '(R12.PJ_PF.B.DELTA.2) Projects Upd Pack 2 for 12.1',
            '9239090', 'Release Update Pack 12.1.3',
            '9147711', '(R12.PJ_PF.B.DELTA.3) Projects Upd Pack 3 for 12.1',
           '12378114', '(R12.PJ_PF.B.DELTA.4) Projects Upd Pack 4 for 12.1',
           '14162290', '(R12.PJ_PF.B.DELTA.5) Projects Upd Pack 5 for 12.1',
           '17839156', '(R12.PJ_PF.B.DELTA.6) Projects Upd Pack 6 for 12.1',
           '22687240', '(R12.PJ_PF.B.DELTA.7) Projects Upd Pack 7 for 12.1',
           '14222221', 'Release Update Pack 12.2.1',
           '14230739', '(R12.PJ_PF.C.DELTA.1) Projects Upd Pack 1 for 12.2',
           '16207672', 'Release Update Pack 12.2.2',
           '16188964', '(R12.PJ_PF.C.DELTA.2) Projects Upd Pack 2 for 12.2',
           '17020683', 'Release Update Pack 12.2.3',
           '17027643', '(R12.PJ_PF.C.DELTA.3) Projects Upd Pack 3 for 12.2',
           '17919161', 'Release Update Pack 12.2.4',
           '17934640', '(R12.PJ_PF.C.DELTA.4) Projects Upd Pack 4 for 12.2',
           '19676458', 'Release Update Pack 12.2.5',
           '19677790', '(R12.PJ_PF.C.DELTA.5) Projects Upd Pack 5 for 12.2',
           '21900901', 'Release Update Pack 12.2.6',
           '22116538', '(R12.PJ_PF.C.DELTA.6) Projects Upd Pack 6 for 12.2',
           '24690690', 'Release Update Pack 12.2.7',
           '24715258', '(R12.PJ_PF.C.DELTA.7) Projects Upd Pack 7 for 12.2'
         ) PatchName,
         creation_date
  from (select patch_name, creation_date from ad_applied_patches union
        select bug_number, creation_date  from ad_bugs)
  where patch_name in (
   '3140000', '3397153', '3640000', '4027334', '3480000', '3460000', '4285356',
   '4667949', '3485155', '4461989', '4997599', '5105878', '5644830', -- end 11i
   '5484000', '6022657', '6141000', '6266113', '6435000', '6512963',
   '6728000', '7292354',  -- end R12.0
   '7303030', '7456340', '7303033', '8504800', '9239090', '9147711',
   '12378114', '14162290', '17839156', '22687240', -- end R12.1
   '14222221', '14230739', '16207672', '16188964', '17020683', '17027643',
   '17919161', '17934640 ', '19676458', '19677790', '21900901', '22116538',
   '24690690', '24715258' -- end R12.2
  )
  order by decode(patch_name,
   '3140000',1,'3397153',1,'3640000',2,'4027334',2,'3480000',3,
   '3460000',3,'4285356',3,'4667949',8,'3485155',9,'4461989',10,
   '4997599',11,'5105878',12,'5644830',13, -- end 11i
   '5484000',14,'6022657',14,'6141000',16,'6266113',16,'6435000',18,
   '6512963',18,'6728000',20,'7292354',20, -- end R12
   '7303030',21,'7456340',22,'7303033',23,'8504800',24,
   '9239090',25,'9147711',26,'12378114',27,'14162290',28,
   '17839156',29,'22687240',30, -- end R12.1
   '14222221',31,'14230739',32,'16207672',33,'16188964',34,
   '17020683',35,'17027643',36,'17919161',37,'1793464031',38,
   '19676458',39,'19677790',40,'21900901',41,'22116538',
   '24690690',42,'24715258',43, -- end R12.2
   100) desc, creation_date desc;
  cursor get_all(pdate date) is
  select ap.patch_name, max(pr.start_date) applied_date,
         upper(prb.application_short_name) app
  from ad_applied_patches ap,
       ad_patch_drivers pd,
       ad_patch_runs pr,
       ad_patch_run_bugs prb
  where pr.start_date > pdate
  and   ap.patch_name != 'merged'
  and   pd.applied_patch_id = ap.applied_patch_id
  and   pr.patch_driver_id = pd.patch_driver_id
  and   prb.patch_run_id = pr.patch_run_id
  and   upper(prb.application_short_name) in ('PA','GMS','OKE')
  group by ap.patch_name, prb.application_short_name
  order by ap.patch_name,
           decode(upper(prb.application_short_name),'PA',1,'GMS',2,3),
           max(pr.start_date);
  patch_error exception;
begin
  select instance_name, host_name, version
  into   l_instance, l_host, l_db_version
  from   v$instance
  where  rownum=1;
  select release_name into l_release
  from   fnd_product_groups
  where  rownum = 1;
  select patch_level into l_patch_level
  from fnd_product_installations
  where  application_id = 275
  and rownum=1;
  open get_patch;
  fetch get_patch into l_patch_num, l_patch_name, l_patch_date;
  if get_patch%notfound then
    close get_patch;
    raise patch_error;
  end if;
  close get_patch;
  dbms_output.put_line('Instance:   '||l_instance);
  dbms_output.put_line('Host:       '||l_host);
  dbms_output.put_line('DB Version: '||l_db_version);
  dbms_output.put_line('Release:    '||l_release);
  dbms_output.put_line('Patch Set:  '||l_patch_level||chr(10));
  dbms_output.put_line('Latest Patchset/RUP: '||l_patch_num||' '||l_patch_name);
  dbms_output.put_line('Applied:             '||
    to_char(l_patch_date,'DD-MON-YYYY HH24:MI:SS')||chr(10));
  dbms_output.put_line('Projects and Grants related patches applied since:');
  l_last_patch := null;
  l_last_applied := null;
  for rec in get_all(l_patch_date) loop
    if (l_last_patch != rec.patch_name or
        l_last_applied != rec.applied_date or
        l_last_patch is null) then
      if l_last_patch is not null then
        dbms_output.put_line(chr(9)||l_last_patch||'  ('||
          to_char(l_last_applied,'DD-MON-YYYY HH24:MI:SS')||')  '||
          l_apps_affected);
      end if;
      l_last_patch := rec.patch_name;
      l_last_applied := rec.applied_date; 
      l_apps_affected := rec.app;
    else
      l_apps_affected := l_apps_affected||','||rec.app;
    end if;
  end loop;
  dbms_output.put_line(chr(9)||l_last_patch||'  ('||
    to_char(l_last_applied,'DD-MON-YYYY HH24:MI:SS')||')  '||
    l_apps_affected);
exception
  when patch_error then
    dbms_output.put_line('Unable to determine latest patchset/rup level');
  when others then
    dbms_output.put_line('Exception in script: '||sqlerrm);
end;
/
spool off