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

Blitz Reports

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
Not Last Updated By
xrv.last_updated_by not in (select fu.user_id from fnd_user fu where fu.user_name=:last_updated_by)
LOV
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
Not Last Updated By
xr.last_updated_by not in (select fu.user_id from fnd_user fu where fu.user_name=:last_updated_by)
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

By continuing to use the site, you agree to the use of cookies. Accept