Blitz Report Templates

➜ Back to Library
Description
Categories: Enginatics, Kcapps
Repository: Github
Blitz Report column or pivot aggregation layout templates
Run Blitz Report Templates and other Oracle EBS reports with Blitz Report™ on our demo environment
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(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,
 LOV
Download
 
Related Blitz Reports
Blitz Reports INV Transaction Historical Summary RWB Reports RWB Execution History GL Account Analysis (Distributions) GL Account Analysis GL Balance by Account Hierarchy XXGF GL Balance by Account Hierarchy XDO Publisher Data Definitions
Blitz Report™

Blitz Report™ provides multiple benefits: