Blitz Report Templates

Description

Categories: Enginatics, Kcapps
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.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,
(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,
&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
) xrtc
where
1=1 and
xrtv.template_id=xrtc.template_id(+)
order by
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

Has Excel Template

xrtv.template_id in (select xrtf.template_id from xxen_report_template_files xrtf)

LOV

Show Columns

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