ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 35CBBB3F96488581E06362FB09056702Y 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 US Test segment value :hierarchy_name :ledger 1 10 :ledger LOV custom Y select gl.name id, gl.name value, gl.ledger_id||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv, gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav, fnd_responsibility_vl frv where (frv.responsibility_key=:$flex$.responsibility_key or (:$flex$.responsibility_key is null and frv.responsibility_id=fnd_global.resp_id)) and gasna.access_set_id=fnd_profile.value_specific('GL_ACCESS_SET_ID',null,frv.responsibility_id,frv.application_id,null,null) and gasna.ledger_id=glsnav.ledger_set_id(+)and gl.ledger_id = nvl(glsnav.ledger_id,gasna.ledger_id) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by id US Ledger