Blitz Report Parameter Custom LOV Duplication Validation

Description

Categories: Enginatics
Repository: Github

Blitz report parameters using custom LOVs with the same query more than once so that they should be set up as a shared LOV instead

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

select
x.*
from
(
select
count(*) over (partition by dbms_lob.substr(xrpv.lov_query_dsp,4000,1)) dupl_count,
count(nvl2(xrpv.lov_id,null,1)) over (partition by dbms_lob.substr(xrpv.lov_query_dsp,4000,1)) custom_count,
xrpv.report_name,
xrpv.category,
xrpv.display_sequence,
xrpv.parameter_name,
xrpv.parameter_type_dsp,
xrpv.lov_name,
dbms_lob.substr(xrpv.lov_query_dsp,4000,1) lov_query_dsp 
from
xxen_report_parameters_v xrpv
where
1=1 and
xrpv.parameter_type='LOV'
) x
where
x.dupl_count>1 and
x.custom_count>0
order by
x.dupl_count desc,
x.lov_query_dsp

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
)