Reports 2017-11-18T12:27:27+00:00

Blitz Reports

  Back to Libary
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
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
Parameter Name 
xrpv.parameter_name=:parameter_name
 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
Show Assignments 
Y
 LOV
Show executions within x days 
xrr.creation_date>sysdate-:exec_count_days
 Number
Show Enabled Only 
xrv.enabled='Y'
 LOV
Parameter SQL contains 
lower(xrpv.sql_text) like '%'||lower(:param_sql_text)||'%'
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||'%'
Default Value contains 
lower(xrpv.default_value) like '%'||:default_value||'%'
Created By 
xrv.created_by in (select fu.user_id from fnd_user fu where fu.user_name=:created_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
Creation Date From 
xrv.creation_date>=:creation_date_from
 Date
Last Update Date From 
xrv.last_update_date>=:last_update_date_from
 Date
Last Updated By 
xrv.last_updated_by in (select fu.user_id from fnd_user fu where fu.user_name=:last_updated_by)
 LOV
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'))
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
)
Parameter Name 
xrpv.parameter_name,
xrpv.sql_text,
xrpv.parameter_type_dsp,
xrpv.lov_name,
xrpv.matching_value,
xrpv.default_value,
xrpv.required,
Parameter Name 
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))
Show Parameters 
Y
Show Assignments 
decode(xrav.include_exclude,'I','Include','E','Exclude') include_exclude,
xrav.assignment_level_desc assignment_level,
xrav.level_value,
Show executions within x days 
y.execution_count,
Show executions within x days 
Y
Show Enabled Only 
xr.enabled='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,
Parameter SQL contains 
Y
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)||'%')
Using LOV 
xrpv.parameter_name,
xrpv.sql_text,
xrpv.parameter_type_dsp,
xrpv.lov_name,
xrpv.matching_value,
xrpv.default_value,
xrpv.required,
Using LOV 
Y
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
))
Matching 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 
Y
Matching Value contains 
xr.report_id in (select xrp.report_id from xxen_report_parameters xrp where lower(xrp.matching_value) like '%'||:matching_value||'%')
Default Value contains 
xrpv.parameter_name,
xrpv.sql_text,
xrpv.parameter_type_dsp,
xrpv.lov_name,
xrpv.matching_value,
xrpv.default_value,
xrpv.required,
Default Value contains 
Y
Default Value contains 
xr.report_id in (select xrp.report_id from xxen_report_parameters xrp where lower(xrp.default_value) like '%'||:default_value||'%')
Created By 
xr.created_by in (select fu.user_id from fnd_user fu where fu.user_name=:created_by)
Not Created By 
xr.created_by not in (select fu.user_id from fnd_user fu where fu.user_name=:not_created_by)
Creation Date From 
xr.creation_date>=:creation_date_from
Last Update Date From 
xr.last_update_date>=:last_update_date_from
Last Updated By 
xr.last_updated_by in (select fu.user_id from fnd_user fu where fu.user_name=:last_updated_by)

Download
 

Related Reports
Blitz Report Execution History
Blitz Report Execution Summary
Blitz Report License Users
Blitz Reports with Example Files (Web View)
Blitz Reports Without Example Files
Blitz Report Deletion History
Blitz Report Example XLSX Files
Blitz Report LOVs
DBA SGA SQL Performance Summary

© 2018 Copyright by Enginatics GmbH