Blitz Reports

Description
Categories: Enginatics, Toolkit - Setup & Support
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,
xxen_api.category(xrv.report_id) category,
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
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,
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.template_count,0,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.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_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
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 (select xrp.report_id from xxen_report_parameters xrp where xrp.last_update_date>:modified_date_from)
)
Date
Created By
xrv.created_by=xxen_util.user_id(:created_by)
LOV
Not Created By
xrv.created_by<>xxen_util.user_id(: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=xxen_util.user_id(:last_updated_by)
LOV
Not Last Updated By
xrv.last_updated_by<>xxen_util.user_id(:last_updated_by)
LOV
Updated By (Report or Param)
(xrv.last_updated_by=xxen_util.user_id(:last_updated_by) or xrpv.last_updated_by=xxen_util.user_id(:last_updated_by))
LOV
Update Date From (Report or Param)
(xrv.last_update_date>=:last_update_date or xrpv.last_update_date>=:last_update_date)
Char
Non merged copied reports
exists (select null from xxen_reports_h xrh0 where xrv.copied_from_guid=xrh0.guid and nvl((select max(xrh.creation_date) from xxen_reports_h xrh where xrv.report_id=xrh.report_id and lower(xrh.comments) like '%merged%'),xrv.creation_date)<xrh0.creation_date)
LOV
Blitz Report™