Blitz Reports

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Blitz Reports with parameters and assignments.
If you are using the free version of Blitz Report, column 'Free 30 Reports' shows which reports are currently included.
Run Blitz Reports and other Oracle EBS reports with Blitz Report™ on our demo environment
with
anchors as (
select distinct
y.report_id,
listagg(y.anchor,', ') within group (order by y.anchor) over (partition by y.report_id) anchors
from
(
select distinct
xr.report_id,
dbms_lob.substr(regexp_substr(regexp_substr(xr.sql_text,'(\D|^)(\d+)=\2(\D|$)',1,x.column_value),'\d+=\d+')) anchor
from
(select xr.report_id, regexp_replace(replace(xr.sql_text,''''''),'''[^'']*''','''x''') sql_text from xxen_reports xr where 3=3) xr,
table(xxen_util.rowgen(regexp_count(xr.sql_text,'(\D|^)(\d+)=\2(\D|$)'))) x
where '&enable_anchors_lexicals_binds'='Y'
) y),
lexicals as (
select distinct
y.report_id,
listagg(y.lexical,', ') within group (order by y.lexical) over (partition by y.report_id) lexicals
from
(
select distinct
xr.report_id,
lower(dbms_lob.substr(regexp_substr(xr.sql_text,'&\w+',1,x.column_value))) lexical
from
(select xr.report_id, xr.sql_text from xxen_reports xr where 3=3 and xr.sql_text like '%&%') xr,
table(xxen_util.rowgen(regexp_count(xr.sql_text,'&\w+'))) x
where '&enable_anchors_lexicals_binds'='Y'
) y),
binds as (
select distinct
y.report_id,
listagg(y.bind,', ') within group (order by y.bind) over (partition by y.report_id) binds
from
(
select distinct
xr.report_id,
lower(dbms_lob.substr(regexp_substr(xr.sql_text,':\w+',1,x.column_value))) bind
from
(select xr.report_id, regexp_replace(replace(xr.sql_text,''''''),'''[^'']*''','''x''') sql_text from xxen_reports xr where 3=3 and xr.sql_text like '%:%') xr,
table(xxen_util.rowgen(regexp_count(xr.sql_text,':\w+'))) x
where '&enable_anchors_lexicals_binds'='Y'
) y)
select
xrv.report_name,
xxen_util.application_name(substr(xrv.report_name,1,instr(xrv.report_name,' ')-1)) application,
xrv.category,
decode(xrv.type,'P','Protected','S','System','U','Upload') type,
xxen_util.meaning(case when xrv.row_num<=30 or xrv.seeded_blitz_report_flag='Y' then 'Y' end,'YES_NO',0) free_30_reports,
&columns
xrv.description,
&modification
xrv.version,
xrv0.report_name copied_from,
(select max(xrh.creation_date) from xxen_reports_h xrh where xrv.copied_from_guid=xrh.guid) copied_from_last_update_date,
xrv.db_package,
xrv.email,
xrv.output_format,
xrv.row_limit,
xrv.time_limit,
xrv.custom_postprocess,
xrv.output_filename,
xrv.additional_out_dir_apps,
xrv.additional_out_dir_db,
xrv.additional_out_fname,
xrv.request_type,
xrv.target_database,
xxen_util.user_name(xrv.created_by) created_by,
xxen_util.client_time(xrv.creation_date) creation_date,
xxen_util.user_name(xrv.last_updated_by) last_updated_by,
xxen_util.client_time(xrv.last_update_date) last_update_date,
xxen_util.meaning(nvl(xrv.enabled,'N'),'YES_NO',0) enabled,
decode(xrv.parameter_count,0,to_number(null),xrv.parameter_count) parameter_count,
decode(xrv.template_count,0,to_number(null),xrv.template_count) template_count,
(select count(distinct xra.id1||','||xra.id2) from xxen_report_assignments xra where xrv.report_id=xra.report_id and xra.include_exclude='I') assignments_count,
(select count(distinct xra.id1||','||xra.id2) from xxen_report_assignments xra where xrv.report_id=xra.report_id and xra.assignment_level='A' and xra.include_exclude='I') application_assignments,
(select count(distinct xra.id1||','||xra.id2) from xxen_report_assignments xra where xrv.report_id=xra.report_id and xra.assignment_level='G' and xra.include_exclude='I') request_group_assignments,
(select count(distinct xra.id1||','||xra.id2) from xxen_report_assignments xra where xrv.report_id=xra.report_id and xra.assignment_level='F' and xra.include_exclude='I') forms_assignments,
&anchors_lexicals_binds
xrv.required_parameters,
xrv.required_parameters_message,
xrv.sql_length,
xrv.sql_text,
xrv.report_id,
xrv.guid
from
(
select
x.*,
row_number() over (order by x.seeded_flag nulls first,x.seeded_blitz_report_flag nulls first,x.report_id desc) row_num
from
(
select
xrv.*,
(select count(*) from xxen_report_parameters xrp where xrv.report_id=xrp.report_id and xrp.display_sequence is not null) parameter_count,
(select count(*) from xxen_report_templates xrt where xrv.report_id=xrt.report_id) template_count,
(select 'Y' from fnd_user fu where fu.user_name in ('ANONYMOUS','ENGINATICS') and xrv.created_by=fu.user_id) seeded_flag,
xxen_report.is_seeded_blitz_report(xrv.guid) seeded_blitz_report_flag
from
xxen_reports_v xrv
) x
) xrv,
xxen_reports_v xrv0,
(select xrpv.* from xxen_report_parameters_v xrpv where '&enable_parameters'='Y') xrpv,
(select xrav.* from xxen_report_assignments_v xrav where '&enable_assignments'='Y') xrav,
(select count(*) execution_count, xrr.report_id from xxen_report_runs xrr where 2=2 and '&enable_exec_count'='Y' group by xrr.report_id) y,
anchors,
lexicals,
binds
where
1=1 and
xrv.copied_from_guid=xrv0.guid(+) and
xrv.report_id=xrpv.report_id(+) and
xrv.report_id=xrav.report_id(+) and
xrv.report_id=y.report_id(+) and
xrv.report_id=anchors.report_id(+) and
xrv.report_id=lexicals.report_id(+) and
xrv.report_id=binds.report_id(+)
order by
&order_by_free_30_reports
y.execution_count desc nulls last,
xrv.report_name,
xrpv.sort_order,
xrav.include_exclude desc,
decode(xrav.assignment_level_desc,
'Site',1,
'Application',2,
'Organization',3,
'Request Group',4,
'Responsibility',5,
'User',6
),
xrav.level_value
Parameter Name SQL text Validation
Report Name 
xrv.report_name=:report_name
 LOV
Report Name like 
lower(xrv.report_name) like lower(:report_name_like)
 LOV
Category 
xrv.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
Not in Category 
xrv.report_id not in (
select
xrca.report_id
from
xxen_report_category_assigns xrca,
xxen_report_categories_v xrcv
where
xrcv.category=:not_in_category and
xrcv.category_id=xrca.category_id
)
 LOV
Parameter Name 
xrpv.parameter_name=:parameter_name
 LOV
Parameter Type 
xrpv.parameter_type_dsp=:parameter_type
 LOV
Show Parameters 
xrpv.display_sequence,
xrpv.parameter_name,
nvl2(xrpv.display_sequence,'Parameter'||row_number() over (partition by xrpv.report_id order by xrpv.display_sequence),null) internal_name,
xrpv.anchor,
xrpv.sql_text parameter_sql_text,
xrpv.parameter_type_dsp,
xrpv.lov_name,
xrpv.matching_value,
xrpv.default_value,
xrpv.required,
xxen_util.user_name(xrpv.created_by) param_created_by,
xxen_util.client_time(xrpv.creation_date) param_creation_date,
xxen_util.user_name(xrpv.last_updated_by) param_last_updated_by,
xxen_util.client_time(xrpv.last_update_date) param_last_update_date,
 LOV
Show Assignments 
Y
 LOV
Show executions within x days 
xrr.creation_date>sysdate-:exec_count_days
 Number
Show Enabled only 
xrv.enabled='Y'
 LOV
DB Package exists 
xrv.db_package is not null
 LOV
Sort by Free 30 Reports 
xrv.row_num,
 LOV
Include Anchors and Binds 
anchors.anchors,
lexicals.lexicals,
binds.binds,
 LOV
Parameter SQL Text contains 
lower(xrpv.sql_text) like '%'||lower(:param_sql_text)||'%'
 Char
Using LOV 
(xrpv.parameter_type, xrpv.lov_id) in (
select 'LOV', xrplv.lov_id from xxen_report_parameter_lovs_v xrplv where xrplv.lov_name=:lov_name union all
select 'LOV Oracle', ffvs.flex_value_set_id from fnd_flex_value_sets ffvs where ffvs.flex_value_set_name=:lov_name
)
 LOV
Matching Value contains 
lower(xrpv.matching_value) like '%'||:matching_value||'%'
 Char
Default Value contains 
lower(xrpv.default_value) like '%'||:default_value||'%'
 Char
Modified Date From 
(
xrv.creation_date>:modified_date_from or
xrv.report_id in (select xrh.report_id from xxen_reports_h xrh where xrh.creation_date>:modified_date_from) or
xrv.report_id in &#