AD Applied Patches 11i

Description
Categories: Enginatics, Toolkit - DBA
Repository: Github Columns: Start Date, End Date, Patch, Included Patches, Patch Description, Patch Type, Instance Name, Appl Top Name, Driver File Name, Patch Top ...
AD applied patches, patch runs, included bugs, filenames and actions.
select distinct
xxen_util.client_time(nvl(aprba.creation_date,apr.start_date)) start_date,
xxen_util.client_time(apr.end_date) end_date,
case when apd.driver_file_name in ('uprepare.drv','ufinalize.drv','ucutover.drv','ucleanup.drv','uactualize.drv','uabort.drv') then
decode(apd.driver_file_name,'uprepare.drv','PREPARE','ufinalize.drv','FINALIZE','ucutover.drv','CUTOVER','ucleanup.drv','CLEANUP','uactualize.drv','ACTUALIZE_ALL','uabort.drv','ABORT') else
aap.patch_name
end patch,
(
select distinct
listagg(x.bug_number,', ') within group (order by x.bug_number) over () patch_number
from
(select distinct acp.patch_driver_id, ab.bug_number from ad_comprising_patches acp, ad_bugs ab where acp.bug_id=ab.bug_id) x
where
apd.patch_driver_id=x.patch_driver_id
) included_patches,
coalesce(
decode(apd.driver_file_name,'uprepare.drv','adop phase=prepare','ufinalize.drv','adop phase=finalize','ucutover.drv','adop phase=cutover','ucleanup.drv','adop phase=cleanup','uactualize.drv','adop phase=actualize_all','uabort.drv','adop phase=abort'),
decode(apd.merged_driver_flag,'Y','Merged Patch')
) patch_description,
aap.patch_type,
aat.applications_system_name instance_name,
aat.name appl_top_name,
apd.driver_file_name,
apr.patch_top,
&col_bug
&col_action
xxen_util.meaning(aprba.executed_flag,'YES_NO',0) bug_action_executed,
xxen_util.meaning(apr.success_flag,'YES_NO',0) run_success,
xxen_util.meaning(aprb.applied_flag,'YES_NO',0) bug_applied,
xxen_util.meaning(aprb.success_flag,'YES_NO',0) bug_success
from
ad_appl_tops aat,
ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
(select aprb.* from ad_patch_run_bugs aprb where '&enable_bug'='Y') aprb,
ad_bugs ab,
(select aprba.* from ad_patch_run_bug_actions aprba where '&enable_action'='Y') aprba,
ad_patch_common_actions apca,
ad_files af,
ad_file_versions afv1,
ad_file_versions afv2,
fnd_application_vl fav
where
1=1 and
aat.appl_top_id=apr.appl_top_id and
aap.applied_patch_id=apd.applied_patch_id and
apd.patch_driver_id=apr.patch_driver_id and
apr.patch_run_id=aprb.patch_run_id(+) and
aprb.bug_id=ab.bug_id(+) and
aprb.patch_run_bug_id=aprba.patch_run_bug_id(+) and
aprba.common_action_id=apca.common_action_id(+) and
aprba.file_id=af.file_id(+) and
aprba.onsite_file_version_id=afv1.file_version_id(+) and
aprba.patch_file_version_id=afv2.file_version_id(+) and
upper(aprb.application_short_name)=fav.application_short_name(+)
order by
start_date desc
&sort_action
Parameter Name SQL text Validation
Application Name
upper(aprb.application_short_name) in (select fav.application_short_name from fnd_application_vl fav where fav.application_name=:application_name)
LOV
Show Executed Actions Only
aprba.executed_flag(+)='Y'
LOV
Show Successfuly Applied Only
apr.success_flag(+)='Y' and
aprb.applied_flag(+)='Y' and
aprb.success_flag(+)='Y'
LOV
Application Short Name
upper(aprb.application_short_name)=:application_short_name
LOV
Display Level
Y
LOV
Filename
af.filename=:filename
LOV
Patch
(
aap.patch_name=:patch_name or
apr.patch_run_id in (select aprb.patch_run_id from ad_bugs ab, ad_patch_run_bugs aprb where ab.bug_number=:patch_name and ab.bug_id=aprb.bug_id)
)
LOV
Bug Number
ab.bug_number=:bug_number
LOV
Applied within Days
nvl(aprba.creation_date,apr.start_date)>=sysdate-:applied_within_days
Number
Applied Date from
nvl(aprba.creation_date,apr.start_date)>=:applied_date_from
DateTime
Applied Date to
nvl(aprba.creation_date,apr.start_date)<=:applied_date_to
DateTime
Display Level
Y
Display Level
af.app_short_name appl_top,
af.subdir,
af.filename,
afv1.version onsite_version,
afv2.version patch_version,
apca.action_code action,
apca.concat_attribs,
aprba.action_id,
Display Level
ab.bug_number,
upper(aprb.application_short_name) application_short_name,
fav.application_name,
Display Level
ab.bug_number,
upper(aprb.application_short_name) application_short_name,
fav.application_name,
Display Level
Y
Display Level
,aprba.action_id desc
Application Name
ab.bug_number,
upper(aprb.application_short_name) application_short_name,
fav.application_name,
Application Name
Y
Application Short Name
ab.bug_number,
upper(aprb.application_short_name) application_short_name,
fav.application_name,
Application Short Name
Y
Filename
af.app_short_name appl_top,
decode(af.subdir,'DUMMY',null,af.subdir) directory,
af.filename,
afv1.version||decode(nvl(afv1.translation_level,0),0,null,':'||to_char(afv1.translation_level)) onsite_version,
afv2.version||decode(nvl(afv2.translation_level,0),0,null,':'||to_char(afv2.translation_level)) patch_version,
apca.action_code action,
apca.concat_attribs,
aprba.action_id,
Filename
ab.bug_number,
upper(aprb.application_short_name) application_short_name,
fav.application_name,
Filename
Y
Filename
Y
Bug Number
ab.bug_number,
upper(aprb.application_short_name) application_short_name,
fav.application_name,
Bug Number
Y