Blitz Report Text Search

Description
Categories: Enginatics
Repository: Github
This report can be used to understand which reports, parameters or LOVs contain a certain SQL Text string, or which reports currently use a specific LOV.

It is used to preview all records that would be changed through the Blitz Report mass change functionality in Setup Window>Tools>Mass Change
Run Blitz Report Text Search and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
select
xrv.category,
xrv.report_name,
'Report SQL' record_type,
xrv.report_name name,
xrv.sql_text text,
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
from
xxen_reports_v xrv
where
1=1 and
(:case_sensitive is null and regexp_like(xrv.sql_text_full,xxen_util.regexp_escape(:sql_text),'i') or xrv.sql_text_full like '%'||replace(:sql_text,'_','\_')||'%' escape '\')
union all
select
xrpv.category,
xrpv.report_name,
'Parameter SQL Text' record_type,
xrpv.parameter_name name,
xrpv.sql_text text,
xxen_util.user_name(xrpv.created_by) created_by,
xxen_util.client_time(xrpv.creation_date) creation_date,
xxen_util.user_name(xrpv.last_updated_by) last_updated_by,
xxen_util.client_time(xrpv.last_update_date) last_update_date
from
xxen_report_parameters_v xrpv
where
2=2 and
5=5 and
(:case_sensitive is null and regexp_like(xrpv.sql_text,xxen_util.regexp_escape(:sql_text),'i') or xrpv.sql_text like '%'||replace(:sql_text,'_','\_')||'%' escape '\')
union all
select
xrpv.category,
xrpv.report_name,
'Parameter Default Value' record_type,
xrpv.parameter_name name,
to_clob(xrpv.default_value) text,
xxen_util.user_name(xrpv.created_by) created_by,
xxen_util.client_time(xrpv.creation_date) creation_date,
xxen_util.user_name(xrpv.last_updated_by) last_updated_by,
xxen_util.client_time(xrpv.last_update_date) last_update_date
from
xxen_report_parameters_v xrpv
where
2=2 and
6=6 and
(:case_sensitive is null and regexp_like(xrpv.default_value,xxen_util.regexp_escape(:sql_text),'i') or xrpv.default_value like '%'||replace(:sql_text,'_','\_')||'%' escape '\')
union all
select
xrpv.category,
xrpv.report_name,
'Parameter Custom LOV Query' record_type,
xrpv.parameter_name name,
xrpv.lov_query text,
xxen_util.user_name(xrpv.created_by) created_by,
xxen_util.client_time(xrpv.creation_date) creation_date,
xxen_util.user_name(xrpv.last_updated_by) last_updated_by,
xxen_util.client_time(xrpv.last_update_date) last_update_date
from
xxen_report_parameters_v xrpv
where
2=2 and
7=7 and
(:case_sensitive is null and regexp_like(xrpv.lov_query,xxen_util.regexp_escape(:sql_text),'i') or xrpv.lov_query like '%'||replace(:sql_text,'_','\_')||'%' escape '\')
union all
select
null category,
null report_name,
'LOV Query' record_type,
xrpl.lov_name name,
xrpl.lov_query text,
xxen_util.user_name(xrpl.created_by) created_by,
xxen_util.client_time(xrpl.creation_date) creation_date,
xxen_util.user_name(xrpl.last_updated_by) last_updated_by,
xxen_util.client_time(xrpl.last_update_date) last_update_date
from
xxen_report_parameter_lovs xrpl
where
3=3 and
(:case_sensitive is null and regexp_like(xrpl.lov_query,xxen_util.regexp_escape(:sql_text),'i') or xrpl.lov_query like '%'||replace(:sql_text,'_','\_')||'%' escape '\')
) x
where
4=4
order by
x.record_type,
x.report_name,
x.name
Parameter Name SQL text Validation
SQL Text contains
 
Char
Category
xrv.report_id in (
select
xrca.report_id
from
xxen_report_categories_v xrcv,
xxen_report_category_assigns xrca
where
xrcv.category=:category and
xrcv.category_id=xrca.category_id
)
LOV
Record Type
x.record_type=:record_type
LOV
Case sensitive
 
LOV
SQL Text does not contain
(not regexp_like(xrv.sql_text_full,xxen_util.regexp_escape(:not_sql_text),'i') or :case_sensitive='Y' and xrv.sql_text_full not like '%'||:not_sql_text||'%')
Char