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.

Blitz Reports and other Oracle EBS reports with Blitz Report™ on our demo environment

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

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

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)