Patch Installs

-- ##############################################################################
--     PATCH INSTALLS
-- ##############################################################################
	
-- simple summary
-- useful for pasting into an SR in plain-text format

    SELECT DISTINCT
           e.patch_name || '______' || d.patch_abstract 
      FROM applsys.ad_bugs a
      JOIN applsys.ad_patch_run_bugs b ON a.bug_id = b.bug_id
      JOIN applsys.ad_patch_runs c ON b.patch_run_id = c.patch_run_id
      JOIN applsys.ad_patch_drivers d ON c.patch_driver_id = d.patch_driver_id
      JOIN applsys.ad_applied_patches e ON d.applied_patch_id = e.applied_patch_id
     WHERE c.end_date > '10-JUN-2016';

 -- How to check if a patch has been applied with adpatch [ID 472820.1]
	 
  SELECT DISTINCT
         aap.patch_name patch
--       , ab.bug_number
--       , aprb.applied_flag
       , apr.end_date
       , aap.creation_date
       , apd.patch_abstract
    FROM applsys.ad_bugs ab
    JOIN applsys.ad_patch_run_bugs aprb ON ab.bug_id =            aprb.bug_id
    JOIN applsys.ad_patch_runs apr      ON apr.patch_run_id =     aprb.patch_run_id
    JOIN applsys.ad_patch_drivers apd   ON apd.patch_driver_id =  apr.patch_driver_id
    JOIN applsys.ad_applied_patches aap ON aap.applied_patch_id = apd.applied_patch_id
   WHERE 1 = 1
     AND ab.bug_number = '15874569'
--     AND aap.patch_name = '15874569'
--     AND aap.creation_date > '01-APR-2016'
     AND 1 = 1;

-- ##############################################################################
--      PATCH COUNTING
-- ##############################################################################
	 
-- count of bug fixes applied per month
	
	select to_char(creation_date, 'YYYY-MM') dd
	     , count(*) ct 
	  from ad_bugs
	 where creation_date > '01-JAN-2015'
  group by to_char(creation_date, 'YYYY-MM')
  order by to_char(creation_date, 'YYYY-MM') desc;

-- count of patches applied per month
	
	select to_char(creation_date, 'YYYY-MM') dd
	     , count(*) ct 
	  from ad_applied_patches
	 where creation_date > '01-JAN-2015'
  group by to_char(creation_date, 'YYYY-MM')
  order by to_char(creation_date, 'YYYY-MM') desc;

-- count of bugs included per patch install, since a patch can include many bug fixes
	
    SELECT e.patch_name
         , e.creation_date patch_created
         , count(*) bug_count
         , d.patch_abstract 
      FROM applsys.ad_bugs a
      JOIN applsys.ad_patch_run_bugs b ON a.bug_id = b.bug_id
      JOIN applsys.ad_patch_runs c ON b.patch_run_id = c.patch_run_id
      JOIN applsys.ad_patch_drivers d ON c.patch_driver_id = d.patch_driver_id
      JOIN applsys.ad_applied_patches e ON d.applied_patch_id = e.applied_patch_id
     WHERE c.end_date > '01-APR-2016' 
  GROUP BY e.patch_name
         , e.creation_date
         , d.patch_abstract 
  ORDER BY e.creation_date DESC;

-- ##############################################################################
--      RPC LEVEL
-- ##############################################################################

select distinct patch_name PatchNum,
decode(patch_name,
		'7303029','Oracle E-Business Suite Consolidated Upgrade Patch 1 (CUP1) for R12.1.1',
		'16791553','Oracle E-Business Suite Consolidated Upgrade Patch 2 (CUP2) for R12.1.1',
		'20203366','Oracle E-Business Suite Release 12.1.3+ Recommended Patch Collection 3 [RPC3]',
		'21236633','Oracle E-Business Suite Release 12.1.3+ Recommended Patch Collection 4 [RPC4]',
		'22644544','Oracle E-Business Suite Release 12.1.3+ Recommended Patch Collection 5 [RPC5]') PatchName
from (select patch_name from ad_applied_patches union
select bug_number from ad_bugs)
where patch_name in ('7303029', '16791553', '20203366', '21236633', '22644544')
order by patchnum desc;