Blitz Report LOV SQL Validation

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Validates Blitz Report LOV SQLs for valid syntax.
This can be useful after mass migrating reports from other tools such as Discoverer, Excl4apps, splashBI or Polaris Reporting Workbench into Blitz Report.
Run Blitz Report LOV SQL Validation and other Oracle EBS reports with Blitz Report™ on our demo environment
select
z.*
from
(
select
y.*,
nvl2(y.error_message,'Error','Valid') validation_result
from
(
select
x.lov_name,
x.report_name,
x.parameter_name,
xxen_report.validate_sql(replace(x.lov_query,':$flex$.',':'),'parse') error_message,
x.lov_query
from
(
select
xrpl.lov_name,
null report_name,
null parameter_name,
xrpl.lov_query
from
xxen_report_parameter_lovs xrpl
where
1=1
union all
select
null lov_name,
xrpv.report_name,
xrpv.parameter_name,
xrpv.lov_query
from
xxen_report_parameters_v xrpv
where
2=2 and
xrpv.parameter_type='LOV' and
xrpv.lov_id is null
) x
) y
) z
where
3=3
order by
z.lov_name
Parameter Name SQL text Validation
LOV Name like 
lower(xrpl.lov_name) like lower(:lov_name_like)
 LOV
Category 
xrpl.lov_id in (
select
xrp.lov_id
from
xxen_report_categories_v xrcv,
xxen_report_category_assigns xrca,
xxen_report_parameters xrp
where
xrcv.category=:category and
xrcv.category_id=xrca.category_id and
xrca.report_id=xrp.report_id and
xrp.parameter_type='LOV'
)
 LOV
Report Name like 
xrpl.lov_id in (select xrpv.lov_id from xxen_report_parameters_v xrpv where lower(xrpv.report_name) like lower(:report_name_like) and xrpv.parameter_type='LOV')
 LOV
Validation Result 
y.validation_result=:validation_result
 LOV
Download
Related Blitz Reports
Blitz Report Text Search Blitz Report LOV Comparison between environments DIS End User Layers Blitz Report LOVs Blitz Report Parameter Comparison between reports Blitz Report Parameter Default Values Blitz Report Parameter Dependencies CAC Calculate ICP PII Item Costs Blitz Report Execution History
Blitz Report™

Blitz Report™ provides multiple benefits: