ORB Report Parameters

select
report_id,
report_name,
rownum*10 blitz_param_display_sequence,
parameters.param_order orb_param_order,
xxen_orb_util.blitz_parameter_name(id,parameter_name) blitz_parameter_name,
parameter_name orb_parameter_name_display,
param_name orb_param_name,
id orb_parameter_id,
parameters.obj_key orb_param_obj_key,
component_type orb_component_type,
case when component_type like 'number%' then 'Number'
when component_type like 'text%' then 'Char'
when component_type like 'date%' then 'Date'
when component_type like 'date%' and
exists (select 1 from xxorb.orb_properties_flat opf where opf.meta_object_id=parameters.id and opf.property_key='INCLUDE_TIME'
and opf.property_value='true') then 'DateTime'
when component_type like '%selection-prompt' then 'LOV custom'
end blitz_datatype_report,
required_flag blitz_required_flag,
xxen_orb_util.construct_parameter_sql_text(report_id,parameters.param_order) blitz_parameter_sql_text,
case when component_type like '%selection-prompt' then xxen_orb_util.construct_lov_sql(report_id,id) end blitz_parameter_lov_sql
from 
(select 
opm.parent_id report_id,
orv.report_name,
param_order display_sequence,
case when orp.component_type like '%range%' then olf.property_value||' From' else olf.property_value end parameter_name,
orp.param_name,
orp.id,
orp.param_order,
orp.component_type,
opm.obj_key,
opm.obj_properties,
case when orp.param_required=0 then 'N' else 'Y' end required_flag
from 
xxorb.orb_meta_object opm,
xxorb.orb_report_parameter orp,
xxorb.orb_localizedstring_flat olf,
xxorb.orb_report_v orv
where 1=1 and
opm.obj_type=16 and 
olf.property_key(+)='name' and
olf.locale(+)='en' and
olf.meta_object_id(+)=orp.id and
opm.id=orp.id and 
orv.report_id=opm.parent_id
union all
select 
opm.parent_id report_id,
orv.report_name,
param_order display_sequence,
olf.property_value||' To' parameter_name,
orp.param_name||'_1',
orp.id,
orp.param_order,
orp.component_type,
opm.obj_key,
opm.obj_properties,
case when orp.param_required=0 then 'N' else 'Y' end required_flag
from 
xxorb.orb_meta_object opm,
xxorb.orb_report_parameter orp,
xxorb.orb_localizedstring_flat olf,
xxorb.orb_report_v orv
where 1=1 and
opm.obj_type=16 and 
olf.property_key(+)='name' and
olf.locale(+)='en' and
orp.component_type like '%range%' and
olf.meta_object_id(+)=orp.id and
opm.id=orp.id and 
orv.report_id=opm.parent_id
order by display_sequence,parameter_name
) parameters

select report_id, report_name, rownum*10 blitz_param_display_sequence, parameters.param_order orb_param_order, xxen_orb_util.blitz_parameter_name(id,parameter_name) blitz_parameter_name, parameter_name orb_parameter_name_display, param_name orb_param_name, id orb_parameter_id, parameters.obj_key orb_param_obj_key, component_type orb_component_type, case when component_type like 'number%' then 'Number' when component_type like 'text%' then 'Char' when component_type like 'date%' then 'Date' when component_type like 'date%' and exists (select 1 from xxorb.orb_properties_flat opf where opf.meta_object_id=parameters.id and opf.property_key='INCLUDE_TIME' and opf.property_value='true') then 'DateTime' when component_type like '%selection-prompt' then 'LOV custom' end blitz_datatype_report, required_flag blitz_required_flag, xxen_orb_util.construct_parameter_sql_text(report_id,parameters.param_order) blitz_parameter_sql_text, case when component_type like '%selection-prompt' then xxen_orb_util.construct_lov_sql(report_id,id) end blitz_parameter_lov_sql from (select opm.parent_id report_id, orv.report_name, param_order display_sequence, case when orp.component_type like '%range%' then olf.property_value||' From' else olf.property_value end parameter_name, orp.param_name, orp.id, orp.param_order, orp.component_type, opm.obj_key, opm.obj_properties, case when orp.param_required=0 then 'N' else 'Y' end required_flag from xxorb.orb_meta_object opm, xxorb.orb_report_parameter orp, xxorb.orb_localizedstring_flat olf, xxorb.orb_report_v orv where 1=1 and opm.obj_type=16 and olf.property_key(+)='name' and olf.locale(+)='en' and olf.meta_object_id(+)=orp.id and opm.id=orp.id and orv.report_id=opm.parent_id union all select opm.parent_id report_id, orv.report_name, param_order display_sequence, olf.property_value||' To' parameter_name, orp.param_name||'_1', orp.id, orp.param_order, orp.component_type, opm.obj_key, opm.obj_properties, case when orp.param_required=0 then 'N' else 'Y' end required_flag from xxorb.orb_meta_object opm, xxorb.orb_report_parameter orp, xxorb.orb_localizedstring_flat olf, xxorb.orb_report_v orv where 1=1 and opm.obj_type=16 and olf.property_key(+)='name' and olf.locale(+)='en' and orp.component_type like '%range%' and olf.meta_object_id(+)=orp.id and opm.id=orp.id and orv.report_id=opm.parent_id order by display_sequence,parameter_name ) parameters