select
era.application_name application,
errc.category_name category,
err.report_name,
err.report_id "EIS Report ID",
err.view_name,
xxen_util.yes(err.seeded_flag) seeded,
errp.display_order,
errp.parameter_name,
errp.description,
errp.data_type,
u.operator,
case when ervc.column_name is not null then ltrim(ervc_comp.alias_name||'.'||ervc.column_name,'.') end column_name,
ervc.data_type view_column_data_type,
erl.lov_name "LOV Name",
case
when erl.lov_query is not null then 'SQL'
when erl.lov_values is not null then 'Values'
when erl.value_set_id is not null then 'Value Set'
end "LOV Type",
coalesce(erl.lov_query,to_clob(erl.lov_values),to_clob(ffvs.flex_value_set_name)) "LOV Query",
case when errp.default_value is not null or errp.default_type='CURRENT_DATE' then decode(errp.default_type,'CONSTANT','Constant','CURRENT_DATE','Current Date',errp.default_type) end default_type,
errp.default_value,
xxen_util.yes(errp.required_flag) required,
xxen_util.yes(errp.display_flag) displayed,
xxen_util.yes(errp.enabled_flag) enabled,
xxen_util.yes(errp.multiple_entires_allowed) multiple_values,
errp.date_list,
xxen_util.yes(errp.expand_gl_parameter) "GL Expand",
erdpm.parent_parameter,
erdpm.parent_filter,
nvl(u.used_in,'None') used_in,
xxen_util.yes(case when regexp_like(erl.lov_query,'xxeis\.','i') or regexp_like(errp.default_value,'xxeis\.','i') then 'Y' end) "XXEIS Reference"
from
xxeis.eis_rs_reports err,
xxeis.eis_rs_applications era,
xxeis.eis_rs_report_categories errc,
xxeis.eis_rs_report_parameters errp,
xxeis.eis_rs_view_columns ervc,
xxeis.eis_rs_view_components ervc_comp,
xxeis.eis_rs_lovs erl,
fnd_flex_value_sets ffvs,
(
select
erdpm.parameter_id,
listagg(errp_p.parameter_name,', ') within group (order by errp_p.display_order) parent_parameter,
listagg(
case when erdpm.operator='FREE_TEXT' then erdpm.free_text else erdpm.column_name||' '||decode(erdpm.operator,'EQUALS','=','GREATERTHAN EQUALS','>=',erdpm.operator)||' :'||errp_p.parameter_name end||
decode(erdpm.optional,'Y',' (optional)'),chr(10)
) within group (order by errp_p.display_order) parent_filter
from
xxeis.eis_rs_dependant_param_maps erdpm,
xxeis.eis_rs_report_parameters errp_p
where
erdpm.dependant_param_id=errp_p.parameter_id
group by
erdpm.parameter_id
) erdpm,
(
select
y.parameter_id,
listagg(case when y.used_in_rank=1 then y.used_in end,', ') within group (order by y.sort_order) used_in,
listagg(
case when y.operator_rank=1 then decode(y.operator,'EQUALS','=','NOTEQUALS','<>','NOT_EQUAL','<>','GREATER_THAN','>','LESS_THAN','<','GREATER_THAN_EQUALS','>=','LESS_THAN_EQUALS','<=','NOTIN','NOT IN',replace(y.operator,'_',' ')) end,', '
) within group (order by y.operator) operator
from
(
select
x.parameter_id,
x.used_in,
x.sort_order,
x.operator,
row_number() over (partition by x.parameter_id, x.used_in order by null) used_in_rank,
row_number() over (partition by x.parameter_id, x.operator order by null) operator_rank
from
(
select
w.parameter_id,
w.used_in,
w.sort_order,
w.operator
from
(
select
p.parameter_id,
p.used_in,
p.sort_order,
p.operator,
p.name_length,
max(p.name_length) over (partition by p.text_id, regexp_instr(p.text,p.pattern,1,rowgen.column_value,0,'i')) max_name_length
from
(
select /*+ no_merge */
errp.parameter_id,
errp.name_length,
errp.pattern,
t.used_in,
t.sort_order,
t.operator,
t.text_id,
t.text
from
(
select
errp.report_id,
errp.parameter_id,
length(errp.parameter_name) name_length,
':'||regexp_replace(errp.parameter_name,'([][().\$|?*+{}])','[\1]')||'($|[^[:alnum:]_])' pattern
from
xxeis.eis_rs_report_parameters errp
) errp,
(
select
t.report_id,
t.used_in,
t.sort_order,
t.operator,
t.text_id,
regexp_replace(t.text,'--[^'||chr(10)||']*|/\*.*?\*/',null,1,0,'n') text
from
(
select
errch.report_id,
decode(rowgen.column_value,4,'Free Text','Condition') used_in,
decode(rowgen.column_value,4,2,1) sort_order,
case when rowgen.column_value<4 then errcd.operator end operator,
'D'||errcd.detail_id||'.'||rowgen.column_value text_id,
to_clob(decode(rowgen.column_value,1,errcd.item,2,errcd.value1,3,errcd.value2,errcd.free_text)) text
from
xxeis.eis_rs_report_cond_headers errch,
xxeis.eis_rs_report_cond_details errcd,
table(xxen_util.rowgen(4)) rowgen
where
errch.enabled='Y' and
errch.condition_id=errcd.condition_id and
errcd.enabled='Y' and
decode(rowgen.column_value,1,errcd.item,2,errcd.value1,3,errcd.value2,errcd.free_text) like '%:%'
union all
select
errco.report_id,
'Derived Column' used_in,
3 sort_order,
null operator,
'C'||errco.column_id text_id,
to_clob(errco.calculation_column) text
from
xxeis.eis_rs_report_columns errco
where
errco.derived_flag='Y' and
nvl(errco.enabled_flag,'Y')='Y' and
errco.calculation_column like '%:%'
union all
select
erso.report_id,
'Sort' used_in,
4 sort_order,
null operator,
'S'||erso.sort_id text_id,
to_clob(erso.free_text) text
from
xxeis.eis_rs_sorts erso
where
erso.free_text like '%:%'
union all
select
errt.report_id,
'Trigger' used_in,
5 sort_order,
null operator,
'T'||errt.trigger_id text_id,
errt.trigger_text text
from
xxeis.eis_rs_report_triggers errt
where
errt.enable_flag='Y' and
dbms_lob.instr(errt.trigger_text,':')>0
) t
) t
where
errp.report_id=t.report_id and
regexp_like(t.text,errp.pattern,'i')
) p,
table(xxen_util.rowgen(regexp_count(p.text,p.pattern,1,'i'))) rowgen
) w
where
w.name_length=w.max_name_length
union all
select
decode(rowgen.column_value,1,errcd.item_report_parameter_id,2,errcd.value1_report_parameter_id,errcd.value2_report_parameter_id) parameter_id,
'Condition' used_in,
1 sort_order,
errcd.operator
from
xxeis.eis_rs_report_cond_headers errch,
xxeis.eis_rs_report_cond_details errcd,
table(xxen_util.rowgen(3)) rowgen
where
errch.enabled='Y' and
errch.condition_id=errcd.condition_id and
errcd.enabled='Y'
union all
select
erdpm.dependant_param_id parameter_id,
'Dependent Parameter' used_in,
6 sort_order,
null operator
from
xxeis.eis_rs_dependant_param_maps erdpm
) x
where
x.parameter_id is not null
) y
group by
y.parameter_id
) u
where
1=1 and
err.application_id=era.application_id and
err.category_id=errc.category_id(+) and
err.report_id=errp.report_id and
errp.column_id=ervc.view_column_id(+) and
errp.view_component_id=ervc_comp.view_component_id(+) and
errp.lov_id=erl.lov_id(+) and
to_number(erl.value_set_id)=ffvs.flex_value_set_id(+) and
errp.parameter_id=erdpm.parameter_id(+) and
errp.parameter_id=u.parameter_id(+)
order by
era.application_name,
errc.category_name,
err.report_name,
errp.display_order,
errp.parameter_id |
