Blitz Report LOVs

Description
Categories: Enginatics
Repository: Github
Blitz Report list of values
Run Blitz Report LOVs and other Oracle EBS reports with Blitz Report™ on our demo environment
select
xrplv.lov_name,
xrplv.description,
xxen_util.meaning(xrplv.validate_from_list,'YES_NO',0) validate_from_list,
xxen_util.meaning(xrplv.filter_before_display,'YES_NO',0) filter_before_display,
decode(xrplv.usage_count,0,to_number(null),xrplv.usage_count) usage_count,
xrplv.version,
(select max(xrplh.creation_date) from xxen_report_parameter_lovs_h xrplh where xrplv.lov_id=xrplh.lov_id) last_sql_update_date,
xxen_util.user_name(xrplv.created_by) created_by,
xxen_util.client_time(xrplv.creation_date) creation_date,
xxen_util.user_name(xrplv.last_updated_by) last_updated_by,
xxen_util.client_time(xrplv.last_update_date) last_update_date,
xrplv.lov_query,
length(xrplv.lov_query) lov_query_size
from
(
select
(select count(*) from xxen_report_parameters xrp where xrp.parameter_type='LOV' and xrplv.lov_id=xrp.lov_id) usage_count,
xrplv.*
from
xxen_report_parameter_lovs_v xrplv
) xrplv
where
1=1
Parameter Name SQL text Validation
LOV Name starts with
lower(xrplv.lov_name) like lower(:lov_name)||'%'
Char
SQL Text contains
lower(xrplv.lov_query) like '%'||lower(:search_text)||'%'
Char
Based on Table
lower(xrplv.lov_query) like '%'||lower(:search_text)||'%'
LOV
Used by Parameter
xrplv.lov_id in (
select
xrpv.lov_id
from
xxen_report_parameters_vl xrpv
where
xrpv.parameter_name=:parameter_name and
xrpv.parameter_type='LOV' and
xrpv.lov_id is not null
)
LOV
Updated within x Days
xrplv.last_update_date>=sysdate-:days
Number
Last Update Date From
xrplv.last_update_date>=:date_from
Date
Last Updated By
xrplv.last_updated_by=xxen_util.user_id(:last_updated_by)
LOV
Used by a Parameter
xrplv.lov_id in (select xrp.lov_id from xxen_report_parameters xrp where xrp.parameter_type='LOV' and xrp.lov_id is not null)
LOV
Not Used by any Parameter
xrplv.lov_id not in (select xrp.lov_id from xxen_report_parameters xrp where xrp.parameter_type='LOV' and xrp.lov_id is not null)
LOV