Oracle-EBS-SQL/AD Applied Patches 11i/AD Applied Patches 11i.sql at master · Enginatics/Oracle-EBS-SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
/*************************************************************************/
/*                                                                       */
/*                       (c) 2010-2024 Enginatics GmbH                   */
/*                              www.enginatics.com                       */
/*                                                                       */
/*************************************************************************/
-- Report Name: AD Applied Patches 11i
-- Description: AD applied patches, patch runs, included bugs, filenames and actions.
-- Excel Examle Output: https://www.enginatics.com/example/ad-applied-patches-11i/
-- Library Link: https://www.enginatics.com/reports/ad-applied-patches-11i/
-- Run Report: https://demo.enginatics.com/

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 '&show_bug'='Y') aprb,
ad_bugs ab,
(select aprba.* from ad_patch_run_bug_actions aprba where '&show_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