Blitz Report Templates

Description

Categories: Enginatics
Repository: Github

Blitz Report column or pivot aggregation layout templates

Blitz Report Templates and other Oracle EBS reports with Blitz Report™ on our demo environment

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

Report Name

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

LOV

Template Name

xrtv.template_name=:template_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
)

LOV

Owner

xrtv.owner_id=xxen_util.user_id(:created_by)

LOV

Creation Date From

xrtv.creation_date>=:creation_date_from

Date

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