Reports2017-11-18T12:27:27+00:00

Blitz Reports

➜  Back to Library
Description
Categories: Application, Enginatics, Setup
Blitz Reports with parameters and assignments 

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
) 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
) 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
) y)
select
xrv.report_name,
xxen_api.category(xrv.report_id) category,
xrv.description,
&columns
xxen_util.user_name(xrv.created_by) created_by,
xrv.creation_date,
xxen_util.user_name(xrv.last_updated_by) last_updated_by,
xrv.last_update_date,
xxen_util.meaning(nvl(xrv.enabled,'N'),'YES_NO',0) enabled,
anchors.anchors,
lexicals.lexicals,
binds.binds,
xrv.report_id,
xrv.guid
from
xxen_reports_v xrv,
(select xrpv.* from xxen_report_parameters_v xrpv where '&enable_parameters'='Y' and xrpv.display_sequence is not null) 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.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
y.execution_count desc nulls last,
xrv.report_name,
xrpv.display_sequence,
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
Last Updated By 
xrv.last_updated_by in (select fu.user_id from fnd_user fu where fu.user_name=:last_updated_by)
 LOV
Not Created By 
xrv.created_by not in (select fu.user_id from fnd_user fu where fu.user_name=:not_created_by)
 LOV
Created By 
xrv.created_by in (select fu.user_id from fnd_user fu where fu.user_name=:created_by)
 LOV
Show Enabled Only 
xrv.enabled='Y'
 LOV
Show Assignments 
Y
 LOV
Show Parameters 
xrpv.parameter_name,
xrpv.sql_text,
xrpv.parameter_type_dsp,
xrpv.lov_name,
xrpv.matching_value,
xrpv.default_value,
xrpv.required,
 LOV
Report Name 
upper(xrv.report_name) like upper(:report_name)
 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
Last Updated By 
xr.last_updated_by in (select fu.user_id from fnd_user fu where fu.user_name=:last_updated_by)
Last Update Date From 
xr.last_update_date>=:last_update_date_from
Creation Date From 
xr.creation_date>=:creation_date_from
Not Created By 
xr.created_by not in (select fu.user_id from fnd_user fu where fu.user_name=:not_created_by)
Created By 
xr.created_by in (select fu.user_id from fnd_user fu where fu.user_name=:created_by)
Default Value contains 
xr.report_id in (select xrp.report_id from xxen_report_parameters xrp where lower(xrp.default_value) like '%'||:default_value||'%')
Default Value contains 
Y
Default Value contains 
xrpv.parameter_name,
xrpv.sql_text,
xrpv.parameter_type_dsp,
xrpv.lov_name,
xrpv.matching_value,
xrpv.default_value,
xrpv.required,
Matching Value contains 
xr.report_id in (select xrp.report_id from xxen_report_parameters xrp where lower(xrp.matching_value) like '%'||:matching_value||'%')
Matching Value contains 
Y
Matching Value contains 
xrpv.parameter_name,
xrpv.sql_text,
xrpv.parameter_type_dsp,
xrpv.lov_name,
xrpv.matching_value,
xrpv.default_value,
xrpv.required,
Using LOV 
xr.report_id in (select xrp.report_id from xxen_report_parameters xrp where
(xrp.parameter_type, xrp.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
))
Using LOV 
Y
Using LOV 
xrpv.parameter_name,
xrpv.sql_text,
xrpv.parameter_type_dsp,
xrpv.lov_name,
xrpv.matching_value,
xrpv.default_value,
xrpv.required,
Parameter SQL contains 
xr.report_id in (select xrp.report_id from xxen_report_parameters xrp where lower(xrp.sql_text) like '%'||lower(:param_sql_text)||'%')
Parameter SQL contains 
Y
Parameter SQL contains 
xrpv.parameter_name,
xrpv.sql_text,
xrpv.parameter_type_dsp,
xrpv.lov_name,
xrpv.matching_value,
xrpv.default_value,
xrpv.required,
Show Enabled Only 
xr.enabled='Y'
Show executions within x days 
Y
Show executions within x days 
y.execution_count,
Show Assignments 
decode(xrav.include_exclude,'I','Include','E','Exclude') include_exclude,
xrav.assignment_level_desc assignment_level,
xrav.level_value,
Show Parameters 
Y
Parameter Name 
xr.report_id in (select xrpv.report_id from xxen_report_parameters_v xrpv where lower(xrpv.parameter_name) like lower(:parameter_name))
Parameter Name 
Y
Parameter Name 
xrpv.parameter_name,
xrpv.sql_text,
xrpv.parameter_type_dsp,
xrpv.lov_name,
xrpv.matching_value,
xrpv.default_value,
xrpv.required,
Category 
xr.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
)
Report Name 
xr.report_id in (select xrt.report_id from xxen_reports_tl xrt where xrt.report_name=:report_name and xrt.language=userenv('lang'))
Last Update Date From 
xrv.last_update_date>=:last_update_date_from
 Date
Creation Date From 
xrv.creation_date>=:creation_date_from
 Date
Default Value contains 
lower(xrpv.default_value) like '%'||:default_value||'%'
Matching Value contains 
lower(xrpv.matching_value) like '%'||:matching_value||'%'
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
Parameter SQL contains 
lower(xrpv.sql_text) like '%'||lower(:param_sql_text)||'%'
Show executions within x days 
xrr.creation_date>sysdate-:exec_count_days
 Number
Parameter Name 
xrpv.parameter_name=:parameter_name
 LOV

Download
 

Related Blitz Reports
Blitz Report Execution History
Blitz Report Execution Summary
Blitz Report License Users
Blitz Reports Without Example Files
Blitz Reports with Example Files (Web View)
Blitz Report Parameter Default Values
Blitz Report Example XLSX Files
Blitz Report Deletion History
Blitz Report LOVs

© 2018 Copyright by Enginatics GmbH