Blitz Report Parameters with more than one :bind variable

Description

Categories: Enginatics
Repository: Github

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

Copy

select
xrpv.report_name,
xrpv.category,
xrpv.display_sequence,
xrpv.parameter_name,
xrpv.anchor,
xxen_api.bindvar_name(xrpv.parameter_id) bindvar,
z.binds,
xrpv.sql_text
from
xxen_report_parameters_v xrpv,
(
select y.* from (
select distinct
x.parameter_id_display,
count(*) over (partition by x.parameter_id_display) bind_count,
listagg(x.bind,', ') within group (order by x.bind) over (partition by x.parameter_id_display) binds
from
(
select distinct
xrplv.parameter_id_display,
lower(dbms_lob.substr(regexp_substr(xrplv.sql_text,':\w+',1,rowgen.column_value))) bind
from
(select xrplv.parameter_id_display, xxen_report.clear_text(xrplv.sql_text) sql_text from xxen_report_parameters_link_v xrplv where xrplv.anchor not like ':%') xrplv,
table(xxen_util.rowgen(regexp_count(xrplv.sql_text,':\w+'))) rowgen
) x
) y
where
y.bind_count>1
) z
where
1=1 and
xrpv.display_sequence is not null and
xrpv.parameter_id=z.parameter_id_display
order by
xrpv.report_name,
xrpv.sort_order

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

Category is not

xrpv.report_id not 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
)