Blitz Report Parameter Table Alias Validation

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Blitz report parameters referencing table aliases, which do not exist as a table in the main report SQL
Run Blitz Report Parameter Table Alias Validation and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
select
xrpv.report_name,
xrpv.category,
xrpv.display_sequence,
xrpv.parameter_name,
xrpv.anchor,
xrpv.sql_text,
dbms_lob.substr(regexp_substr(xrpv.sql_text,'(\w+)\.\w+.',1,1,null,1)) table_alias,
dbms_lob.substr(regexp_substr(xrpv.sql_text,'\w+\.\w+(.)',1,1,null,1)) function_check,
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,
xrpv.report_id,
xrpv.parameter_id
from
xxen_report_parameters_v xrpv
where
1=1 and
xrpv.anchor not like '&%' and
xrpv.report_id in (select xrca.report_id from xxen_report_categories_v xrcv, xxen_report_category_assigns xrca where xrcv.category='Enginatics' and xrcv.category_id=xrca.category_id) and
(xrpv.display_sequence>0 or xrpv.display_sequence is null) and
xrpv.sql_text is not null
) x
where
x.table_alias not in ('xrrpv') and
x.function_check<>'(' and
x.table_alias is not null and
not exists (select null from xxen_reports xr where x.report_id=xr.report_id and regexp_like(xr.sql_text,' '||x.table_alias||'\W','i')) and
not exists (select null from xxen_report_parameters_v xrpv2 where x.report_id=xrpv2.report_id and (
x.parameter_name=xrpv2.parameter_name or
xrpv2.parameter_id in (
select
xrplv.parameter_id
from
xxen_report_param_dependencies xrpd,
xxen_report_parameters_link_v xrplv
where
x.parameter_id=xrpd.dependent_parameter_id and
xrpd.parameter_id=xrplv.parameter_id_display
)) and
xrpv2.anchor like '&%' and regexp_like(xrpv2.sql_text,' '||x.table_alias||'\W','i'))
order by
x.report_name,
x.display_sequence
Parameter Name SQL text Validation
Category 
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
)
 LOV
Download
Related Blitz Reports
GL Account Analysis (Distributions) Blitz Report Parameter Dependencies Blitz Report Execution History Blitz Report Category Assignments Blitz Report LOV Comparison between environments Blitz Report Text Search Blitz Report Parameters with more than one :bind variable Blitz Reports Blitz Report Parameter Default Values
Blitz Report™

Blitz Report™ provides multiple benefits: