Oracle-EBS-SQL/Blitz Report Templates/Blitz Report Templates.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
72
73
74
75
76
/*************************************************************************/
/*                                                                       */
/*                       (c) 2010-2024 Enginatics GmbH                   */
/*                              www.enginatics.com                       */
/*                                                                       */
/*************************************************************************/
-- Report Name: Blitz Report Templates
-- Description: Blitz Report column or pivot aggregation layout templates
-- Excel Examle Output: https://www.enginatics.com/example/blitz-report-templates/
-- Library Link: https://www.enginatics.com/reports/blitz-report-templates/
-- Run Report: https://demo.enginatics.com/

select distinct
xrtv.report_name,
xrtv.report_description,
xrtv.category,
xrtv.template_name,
xrtv.description,
xrtv.owner,
xrtv.sharing,
xxen_util.meaning(xrtv.global_default,'YES_NO',0) global_default,
xxen_util.meaning(xrtv.distinct_flag,'YES_NO',0) distinct_flag,
count(*) over (partition by xrtv.template_id) column_count,
max(xxen_util.meaning(xrtc.aggregation,'AMS_EXPN_BUILDER_OPERATORS',530)) over (partition by xrtv.template_id) max_aggregation,
max(nvl2(xrtc.sort_order,xrtc.direction||' ',null)||xrtc.sort_order_) over (partition by xrtv.template_id) max_sort_order,
xxen_util.zero_to_null(xrtp.filter_count) filter_count,
xxen_util.zero_to_null(xrtp.column_count) column_count,
xxen_util.zero_to_null(xrtp.row_count) row_count,
xxen_util.zero_to_null(xrtp.value_count) value_count,
(select sum(length(xrtf.file_data)) from xxen_report_template_files xrtf where xrtv.template_id=xrtf.template_id) file_size,
xrtv.file_name excel_file_name,
(select xrtf.sheet_name from xxen_report_template_files xrtf where xrtv.template_id=xrtf.template_id and xrtf.sheet_type='data') data_sheet_name,
xxen_util.client_time(xrtv.file_last_modified_date) file_last_modified,
xxen_util.user_name(xrtv.created_by) created_by,
xxen_util.client_time(xrtv.creation_date) creation_date,
xxen_util.user_name(xrtv.last_updated_by) last_updated_by,
xxen_util.client_time(xrtv.last_update_date) last_update_date,
&columns
xrtv.template_id
from
xxen_report_templates_v xrtv,
(
select
xxen_util.meaning(xrtc.aggregation,'AMS_EXPN_BUILDER_OPERATORS',530) aggregation_meaning,
xxen_util.meaning(case when xrtc.sort_order<0 then 2 else sign(xrtc.sort_order) end,'WMS_SORT_ORDER',700) direction,
abs(xrtc.sort_order) sort_order_,
xxen_util.meaning(xrtc.sheet_break,'YES_NO',0) sheet_break_,
(select distinct listagg(xrtp.field_type,', ') within group (order by xrtp.field_type) over () pivot_field_type from xxen_report_template_pivot xrtp where xrtc.template_id=xrtp.template_id and xrtc.column_name=xrtp.column_name) pivot_field_type,
xrtc.*
from
xxen_report_template_columns xrtc
where
'&show_columns'='Y' and
xrtc.display_sequence is not null
) xrtc,
(
select
xrtp.template_id,
count(decode(xrtp.field_type,'FILTER',1)) filter_count,
count(decode(xrtp.field_type,'COLUMN',1)) column_count,
count(decode(xrtp.field_type,'ROW',1)) row_count,
count(decode(xrtp.field_type,'VALUE',1)) value_count
from
xxen_report_template_pivot xrtp
group by
xrtp.template_id
) xrtp
where
1=1 and
xrtv.template_id=xrtc.template_id(+) and
xrtv.template_id=xrtp.template_id(+)
order by
report_name,
owner,
xrtv.template_name
&column_order_by