Blitz Reports

➜ Back to Library

Description
Categories: Application, Enginatics, Setup Blitz Reports with parameters and assignments more less

Copy

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,
case when xrv.row_num<=30 then 'Y' end free_30_reports,
&columns
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,
anchors.anchors,
lexicals.lexicals,
binds.binds,
xrv.report_id,
xrv.guid
from
(
select
x.*,
row_number() over (order by x.seeded_flag nulls first,x.report_id desc) row_num
from
(
select
xrv.*,
(select 'Y' from fnd_user fu where fu.user_name in ('ANONYMOUS','ENGINATICS') and xrv.created_by=fu.user_id) seeded_flag
from
xxen_reports_v xrv
) x
) 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
&order_by_free_30_reports
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

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, case when xrv.row_num<=30 then 'Y' end free_30_reports, &columns 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, anchors.anchors, lexicals.lexicals, binds.binds, xrv.report_id, xrv.guid from ( select x.*, row_number() over (order by x.seeded_flag nulls first,x.report_id desc) row_num from ( select xrv.*, (select 'Y' from fnd_user fu where fu.user_name in ('ANONYMOUS','ENGINATICS') and xrv.created_by=fu.user_id) seeded_flag from xxen_reports_v xrv ) x ) 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 &order_by_free_30_reports 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

Sort by Free 30 Reports

xrv.row_num,

LOV

Show Enabled Only

xrv.enabled='Y'

LOV

Show Assignments

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

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

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

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

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.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

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

xrpv.parameter_name,
xrpv.sql_text,
xrpv.parameter_type_dsp,
xrpv.lov_name,
xrpv.matching_value,
xrpv.default_value,
xrpv.required,