select
x.application,
x.category,
x.report_name,
x."EIS Report ID",
x.view_name,
x.seeded,
x.condition_seq,
x.condition_name,
x.condition_type,
x.enabled,
x.detail_seq,
x.item_type,
x.item,
x.operator,
x.value,
x.value2,
x.advanced_template,
x.condition_text,
xxen_util.yes(x.hardcoded) hardcoded
from
(
select
y.*,
case when y.condition_text is not null and count(case when regexp_like(y.condition_text,':[[:alpha:]]') then 1 end) over (partition by y.condition_id)=0 then 'Y' end hardcoded
from
(
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,
errch.display_order condition_seq,
errch.condition_name,
initcap(replace(errch.condition_type,'_',' ')) condition_type,
xxen_util.yes(errch.enabled) enabled,
errcd.display_order detail_seq,
d.item_type,
d.item,
d.operator,
d.value1 value,
d.value2,
decode(errch.condition_type,'ADVANCED',trim(errch.advanced_condition)) advanced_template,
case
when d.free_text is not null then d.free_text
when d.item is null then null
when d.operator in ('is null','is not null') then d.item||' '||d.operator
when d.value1 is null then null
when d.operator='between' then d.item||' between '||d.value1||' and '||d.value2
when d.operator in ('in','not in') then d.item||' '||d.operator||' ('||d.value1||')'
else d.item||' '||d.operator||' '||d.value1
end condition_text,
errch.condition_id,
errcd.detail_id
from
xxeis.eis_rs_reports err,
xxeis.eis_rs_applications era,
xxeis.eis_rs_report_categories errc,
xxeis.eis_rs_report_cond_headers errch,
xxeis.eis_rs_report_cond_details errcd,
(
select
o.detail_id,
o.operator,
o.free_text,
max(decode(o.role,1,o.operand_type)) item_type,
max(decode(o.role,1,o.operand)) item,
max(decode(o.role,2,o.operand)) value1,
max(decode(o.role,3,o.operand)) value2
from
(
select
c.detail_id,
c.role,
c.operator,
c.free_text,
case
when c.text is not null then 'Expression'
when c.view_column_id is not null and ervc.view_column_id is not null then 'View Column'
when c.column_id is not null then 'Report Column'
when errp.parameter_id is not null then 'Parameter'
end operand_type,
case
when c.text is not null then c.text
when c.view_column_id is null and c.derived_flag='Y' then c.calculation_column
when ervc.view_column_id is not null then
coalesce(
ervc_comp.alias_name,
(
select
min(ervc_comp2.alias_name)
from
xxeis.eis_rs_report_views errv,
xxeis.eis_rs_view_components ervc_comp2
where
c.report_id=errv.report_id and
ervc.view_id=errv.view_id and
errv.view_component_id=ervc_comp2.view_component_id
having
count(*)=1
),
erv.view_alias
)||'.'||ervc.column_name
when c.column_id is not null then c.column_name
when errp.parameter_id is not null then ':'||errp.parameter_name
end operand
from
(
select
z.*,
errc_col.column_id,
errc_col.column_name,
errc_col.derived_flag,
errc_col.calculation_column,
nvl(z.view_column_id,errc_col.view_column_id) resolved_view_column_id,
nvl(z.view_component_id,errc_col.view_component_id) resolved_view_component_id
from
(
select
errch.report_id,
errcd.detail_id,
rowgen.column_value role,
decode(errcd.operator,
'IN','in',
'NOTIN','not in',
'EQUALS','=',
'NOTEQUALS','<>',
'NOT_EQUAL','<>',
'GREATER_THAN','>',
'GREATER_THAN_EQUALS','>=',
'LESS_THAN','<',
'LESS_THAN_EQUALS','<=',
'LIKE','like',
'BETWEEN','between',
'IS','is',
'IS_NULL','is null',
'IS_NOT_NULL','is not null'
) operator,
regexp_replace(errcd.free_text,'^\s*and\s*|\s+$','',1,0,'i') free_text,
trim(decode(rowgen.column_value,1,errcd.item,2,errcd.value1,3,errcd.value2)) text,
decode(rowgen.column_value,1,errcd.item_view_column_id,2,errcd.value1_view_column_id,3,errcd.value2_view_column_id) view_column_id,
decode(rowgen.column_value,1,errcd.item_view_component_id,2,to_number(errcd.value1_view_component_id),3,to_number(errcd.value2_view_component_id)) view_component_id,
decode(rowgen.column_value,1,errcd.item_report_column_id,2,errcd.value1_report_column_id,3,errcd.value2_report_column_id) report_column_id,
decode(rowgen.column_value,1,errcd.item_report_parameter_id,2,errcd.value1_report_parameter_id,3,errcd.value2_report_parameter_id) parameter_id
from
xxeis.eis_rs_report_cond_headers errch,
xxeis.eis_rs_report_cond_details errcd,
table(xxen_util.rowgen(3)) rowgen
where
errch.condition_id=errcd.condition_id
) z,
xxeis.eis_rs_report_columns errc_col
where
z.report_column_id=errc_col.column_id(+)
) c,
xxeis.eis_rs_view_columns ervc,
xxeis.eis_rs_views erv,
xxeis.eis_rs_view_components ervc_comp,
xxeis.eis_rs_report_parameters errp
where
c.resolved_view_column_id=ervc.view_column_id(+) and
ervc.view_id=erv.view_id(+) and
c.resolved_view_component_id=ervc_comp.view_component_id(+) and
c.parameter_id=errp.parameter_id(+)
) o
group by
o.detail_id,
o.operator,
o.free_text
) d
where
1=1 and
err.application_id=era.application_id and
err.category_id=errc.category_id(+) and
err.report_id=errch.report_id and
errch.condition_id=errcd.condition_id and
errcd.detail_id=d.detail_id
) y
) x
where
2=2
order by
x.application,
x.category,
x.report_name,
x.condition_seq,
x.condition_id,
x.detail_seq,
x.detail_id |
