Blitz Report Parameter Table Alias Validation
Description
Categories: Enginatics
Repository: Github
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.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, xrpv.report_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 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 |
|
LOV |