SOX Audit Report : Applied Patches (adpatch) History in Oracle EBS 11i and R12

Applied Patches (adpatch) History Report in Oracle EBS 11i and R12 for SOX Audit

You may want to be able to audit recently applied patches (adpatch) history report in Oracle EBS 11i/R12.

If you want to query information about patches applied history report:

Query for EBS R12:

select distinct aap.patch_name,  apr.patch_top,  aat.name,  aap.patch_type,                        
       aap.creation_date,  fav.APPLICATION_NAME, apd.patch_abstract
from apps.ad_patch_runs apr,
     apps.ad_patch_drivers apd,
     apps.ad_applied_patches aap,
     apps.ad_patch_run_bugs aprb,
     apps.ad_appl_tops aat,
     apps.fnd_application_vl fav
where apr.appl_top_id = aat.appl_top_id
  and apr.patch_driver_id = apd.patch_driver_id
  and apr.patch_run_id = aprb.patch_run_id
  and apd.applied_patch_id = aap.applied_patch_id
  and UPPER(aprb.application_short_name) =fav.application_short_name
  and aprb.applied_flag = 'Y'
and aap.creation_date >= to_date('01-APR-2016','DD-MM-YYYY')
and aap.creation_date <= to_date('30-APR-2016','DD-MM-YYYY')
order by aap.creation_date;

Query for EBS 11i:

select distinct aap.patch_name,  apr.patch_top,  aat.name,  aap.patch_type,                        
       aap.creation_date,  fav.APPLICATION_NAME
from apps.ad_patch_runs apr,
     apps.ad_patch_drivers apd,
     apps.ad_applied_patches aap,
     apps.ad_patch_run_bugs aprb,
     apps.ad_appl_tops aat,
     apps.fnd_application_vl fav
where apr.appl_top_id = aat.appl_top_id
  and apr.patch_driver_id = apd.patch_driver_id
  and apr.patch_run_id = aprb.patch_run_id
  and apd.applied_patch_id = aap.applied_patch_id
  and UPPER(aprb.application_short_name) =fav.application_short_name
  and aprb.applied_flag = 'Y'
and aap.creation_date >= to_date('01-APR-2016','DD-MM-YYYY')
and aap.creation_date <= to_date('30-APR-2016','DD-MM-YYYY')
order by aap.creation_date;

This will give the output with patch details which have been applied during the period of 01-APR-2016 to 30-APR-2016