ENGINATICS
SYSTEM_ADMINISTRATOR
SYSADMIN
8E2FF36EDEFA79D2E0530100007F1FF2
Blitz Report Category
Y
select
xrcv.category value,
(select count(*) from xxen_report_category_assigns xrca where xrcv.category_id=xrca.category_id) description
from
xxen_report_categories_v xrcv
order by
xrcv.category
US
8E2FF36EDEA879D2E0530100007F1FF2
Blitz Report Name
Y
select
xrv.report_name value,
xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null)||substrb(xrv.description,1,4000-lengthb(xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null))) description
from
xxen_reports_v xrv
where
nvl(xrv.type,'x')<>'S' and
(fnd_profile.value('XXEN_REPORT_ACCESS')='S' or nvl(xrv.type,'x')<>'U')
order by
xrv.report_name
US
E5A2B6F8FD04BA64E0530100007F319A
select
x.*
from
(
select
xrv.report_name,
xrv.type_dsp report_type,
xrv.category,
'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
regexp_like(xrv.sql_text_full,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xrv.sql_text_full,®exp_escape(:not_sql_text)&insensitive))
union all
select
xrv.report_name,
xrv.type_dsp report_type,
xrv.category,
'Upload Excel Validation' record_type,
xrv.report_name name,
to_clob(xrv.upload_excel_validation) 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
regexp_like(xrv.upload_excel_validation,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xrv.upload_excel_validation,®exp_escape(:not_sql_text)&insensitive))
union all
select
xrpv.report_name,
xrpv.type_dsp,
xrpv.category,
'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
regexp_like(xrpv.sql_text,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xrpv.sql_text,®exp_escape(:not_sql_text)&insensitive))
union all
select
xrpv.report_name,
xrpv.type_dsp,
xrpv.category,
'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
regexp_like(xrpv.lov_query,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xrpv.lov_query,®exp_escape(:not_sql_text)&insensitive))
union all
select
xrpv.report_name,
xrpv.type_dsp,
xrpv.category,
'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
regexp_like(xrpv.default_value,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xrpv.default_value,®exp_escape(:not_sql_text)&insensitive))
union all
select
null report_name,
null report_type,
null category,
'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
regexp_like(xrpl.lov_query,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xrpl.lov_query,®exp_escape(:not_sql_text)&insensitive))
union all
select
null report_name,
null report_type,
null category,
'LOV Value to Id Query' record_type,
xrpl.lov_name name,
to_clob(xrpl.value_to_id_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
regexp_like(xrpl.value_to_id_query,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xrpl.value_to_id_query,®exp_escape(:not_sql_text)&insensitive))
union all
select
xucv.report_name,
'Upload' report_type,
xucv.category,
'Upload Column Custom LOV Query' record_type,
xucv.column_name name,
xucv.lov_query text,
xxen_util.user_name(xucv.created_by) created_by,
xxen_util.client_time(xucv.creation_date) creation_date,
xxen_util.user_name(xucv.last_updated_by) last_updated_by,
xxen_util.client_time(xucv.last_update_date) last_update_date
from
xxen_upload_columns_v xucv
where
4=4 and
regexp_like(xucv.lov_query,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xucv.lov_query,®exp_escape(:not_sql_text)&insensitive))
union all
select
xucv.report_name,
'Upload' report_type,
xucv.category,
'Upload Column Value to Id Query' record_type,
xucv.column_name name,
to_clob(xucv.value_to_id_query) text,
xxen_util.user_name(xucv.created_by) created_by,
xxen_util.client_time(xucv.creation_date) creation_date,
xxen_util.user_name(xucv.last_updated_by) last_updated_by,
xxen_util.client_time(xucv.last_update_date) last_update_date
from
xxen_upload_columns_v xucv
where
4=4 and
regexp_like(xucv.value_to_id_query,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xucv.value_to_id_query,®exp_escape(:not_sql_text)&insensitive))
union all
select
xucv.report_name,
'Upload' report_type,
xucv.category,
'Upload Column Default Value' record_type,
lower(xucv.column_name) name,
to_clob(xucv.default_value) text,
xxen_util.user_name(xucv.created_by) created_by,
xxen_util.client_time(xucv.creation_date) creation_date,
xxen_util.user_name(xucv.last_updated_by) last_updated_by,
xxen_util.client_time(xucv.last_update_date) last_update_date
from
xxen_upload_columns_v xucv
where
4=4 and
regexp_like(xucv.default_value,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xucv.default_value,®exp_escape(:not_sql_text)&insensitive))
union all
select
xucv.report_name,
'Upload' report_type,
xucv.category,
'Upload Column Comments' record_type,
xucv.column_name name,
to_clob(xucv.comments) text,
xxen_util.user_name(xucv.created_by) created_by,
xxen_util.client_time(xucv.creation_date) creation_date,
xxen_util.user_name(xucv.last_updated_by) last_updated_by,
xxen_util.client_time(xucv.last_update_date) last_update_date
from
xxen_upload_columns_v xucv
where
4=4 and
regexp_like(xucv.comments,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xucv.comments,®exp_escape(:not_sql_text)&insensitive))
union all
select
xusv.report_name,
'Upload' report_type,
xusv.category,
'Upload Result SQL' record_type,
xusv.sql_type||nvl2(xusv.anchor,': '||xusv.anchor,null) name,
to_clob(xusv.sql_text) text,
xxen_util.user_name(xusv.created_by) created_by,
xxen_util.client_time(xusv.creation_date) creation_date,
xxen_util.user_name(xusv.last_updated_by) last_updated_by,
xxen_util.client_time(xusv.last_update_date) last_update_date
from
xxen_upload_sqls_v xusv
where
5=5 and
regexp_like(xusv.sql_text,®exp_escape(:sql_text)&insensitive) and
(:not_sql_text is null or not regexp_like(xusv.sql_text,®exp_escape(:not_sql_text)&insensitive))
) x
where
10=10
order by
x.record_type,
x.report_name,
x.name
Y
US
Blitz Report Text Search
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
Enginatics
&insensitive
®exp_escape
10=10
1=1
2=2
3=3
4=4
5=5
:not_sql_text
:sql_text
1
10
:sql_text
Char
Y
US
SQL Text contains
2
20
1=1
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
Blitz Report Category
8E2FF36EDEFA79D2E0530100007F1FF2
Y
select
xrcv.category value,
(select count(*) from xxen_report_category_assigns xrca where xrcv.category_id=xrca.category_id) description
from
xxen_report_categories_v xrcv
order by
xrcv.category
US
Category
3
2=2
xrpv.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
)
US
Category
4
3=3
xrpl.lov_id in (
select
xrpv.lov_id
from
xxen_report_categories_v xrcv,
xxen_report_category_assigns xrca,
xxen_report_parameters_v xrpv
where
xrcv.category=:category and
xrcv.category_id=xrca.category_id and
xrca.report_id=xrpv.report_id and
xrpv.parameter_type='LOV'
)
US
Category
5
4=4
xucv.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
)
US
Category
6
5=5
xusv.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
)
US
Category
7
30
10=10
x.record_type=:record_type
LOV custom
Y
select 'Report SQL' value, null description from dual union all
select 'Parameter SQL Text' value, null description from dual union all
select 'Parameter Custom LOV Query' value, null description from dual union all
select 'Parameter Default Value' value, null description from dual union all
select 'Upload Column Custom LOV Query' value, null description from dual union all
select 'Upload Column Value to Id Query' value, null description from dual union all
select 'Upload Column Default Value' value, null description from dual union all
select 'Upload Column Comments' value, null description from dual union all
select 'Upload Excel Validation' value, null description from dual union all
select 'Upload Result SQL' value, null description from dual union all
select 'LOV Query' value, null description from dual union all
select 'LOV Value to Id Query' value, null description from dual
US
Record Type
8
40
&insensitive
,'i'
LOV Oracle
Yes_No
Y
select
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,description
N
N
Y
US
Match case
9
50
®exp_escape
xxen_util.regexp_escape
LOV Oracle
Yes_No
Y
select
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,description
N
N
Y
US
Regex mode
10
60
:not_sql_text
Char
US
SQL Text does not contain
11
70
1=1
xrv.report_name=:report_name
LOV
Blitz Report Name
8E2FF36EDEA879D2E0530100007F1FF2
Y
select
xrv.report_name value,
xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null)||substrb(xrv.description,1,4000-lengthb(xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null))) description
from
xxen_reports_v xrv
where
nvl(xrv.type,'x')<>'S' and
(fnd_profile.value('XXEN_REPORT_ACCESS')='S' or nvl(xrv.type,'x')<>'U')
order by
xrv.report_name
US
Report Name
12
2=2
xrpv.report_name=:report_name
US
Report Name
13
3=3
xrpl.lov_id in (select xrpv.lov_id from xxen_report_parameters_v xrpv where xrpv.report_name=:report_name and xrpv.parameter_type='LOV')
US
Report Name
14
4=4
xucv.report_name=:report_name
US
Report Name
15
5=5
xusv.report_name=:report_name
US
Report Name
Default
ENGINATICS
3
CATEGORY
7
CREATED_BY
8
CREATION_DATE
9
LAST_UPDATED_BY
10
LAST_UPDATE_DATE
5
NAME
4
4
RECORD_TYPE
1
Y
1
REPORT_NAME
3
2
REPORT_TYPE
2
6
TEXT
S
Site
Default