with gl as
(
select
fifs.flex_value_set_id,
fifs.application_column_name,
fifs.segment_name,
fifs.segment_num
from
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:ledger) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language='US' and
fifs.segment_num=1
)
select
case when connect_by_isleaf is null then 'P' else 'C' end id,
ffvnh.flex_value value,
replace(ffvv.description,chr(9),' ') description
from
(
select
gl.segment_num,
gl.segment_name,
gl.application_column_name,
ffvnh.parent_flex_value flex_value,
ffvnh.child_flex_value_low,
ffvnh.child_flex_value_high,
ffvnh.range_attribute,
ffvnh.flex_value_set_id,
ffvnh.created_by,
ffvnh.creation_date,
ffvnh.last_updated_by,
ffvnh.last_update_date
from
fnd_flex_value_norm_hierarchy ffvnh,
gl
where
:hierarchy_name is not null and
ffvnh.flex_value_set_id=gl.flex_value_set_id
union all
select
gl.segment_num,
gl.segment_name,
gl.application_column_name,
ffv2.flex_value flex_value,
null child_flex_value_low,
null child_flex_value_high,
'x' range_attribute,
ffv2.flex_value_set_id,
ffv2.created_by,
ffv2.creation_date,
ffv2.last_updated_by,
ffv2.last_update_date
from
fnd_flex_values ffv2,
gl
where
:hierarchy_name is not null and
ffv2.summary_flag='N' and
ffv2.flex_value_set_id=gl.flex_value_set_id
) ffvnh,
fnd_flex_values_vl ffvv
where
ffvnh.flex_value=ffvv.flex_value and
gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and
ffvnh.flex_value_set_id=ffvv.flex_value_set_id
connect by nocycle
ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and
decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag
start with
ffvnh.flex_value=:hierarchy_name
union all
select
x.*
from
(
select
decode(ffvv.summary_flag,'Y','P','C') id,
ffvv.flex_value value,
replace(ffvv.description,chr(9),' ') description
from
gl,
fnd_flex_values_vl ffvv
where
gl.flex_value_set_id=ffvv.flex_value_set_id and
gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and
:hierarchy_name is null
order by
ffvv.flex_value
) x |
