Blitz Report Templates

Description

Categories: Enginatics
Repository: Github Columns: Report Name, Category, Template Name, Owner, Public Flag, Global Default, Distinct Flag, Column Count, Max Aggregation, Max Sort Order ...

Blitz Report column or pivot aggregation layout templates

Copy

select distinct
xrtv.report_name,
xrtv.category,
xrtv.template_name,
xrtv.owner,
xxen_util.meaning(xrtv.public_flag,'YES_NO',0) public_flag,
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(xrtc.sort_order_||nvl2(xrtc.sort_order,' '||xrtc.direction,null)) over (partition by xrtv.template_id) max_sort_order,
xrtv.creation_date
&columns
from
xxen_report_templates_v xrtv,
(
select
xxen_util.meaning(xrtc.aggregation,'AMS_EXPN_BUILDER_OPERATORS',530) aggregation_meaning,
abs(xrtc.sort_order) sort_order_,
xxen_util.meaning(case when xrtc.sort_order<0 then 2 else sign(xrtc.sort_order) end,'WMS_SORT_ORDER',700) direction,
(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
) xrtc
where
1=1 and
xrtv.template_id=xrtc.template_id(+)
order by
xrtv.report_name,
owner,
xrtv.template_name
&column_order_by

Parameter Name

SQL text

Validation

Owner

xrtv.owner_id=xxen_util.user_id(:created_by)

LOV

Show Columns

,xrtc.display_sequence,
xrtc.column_name,
xxen_report.column_translation(xrtc.column_name) translation,
xrtc.aggregation_meaning aggregation,
xrtc.pivot_field_type,
xrtc.sort_order_,
xrtc.direction

LOV

Report Name

upper(xrtv.report_name) like upper(:report_name)

LOV

Category

xrtv.report_id in (
select
xrca.report_id
from
xxen_report_category_assigns xrca,
xxen_report_categories_v xrcv
where
xrcv.category=:category and
xrcv.category_id=xrca.category_id
)