----Key segments value set details (Values/Effective date/Hierarchy/Qualifiers)
SELECT
ffvs.flex_value_set_name value_set_name,
ffv.flex_value value,
ffvt.description,
ffv.start_date_active from_date,
ffv.end_date_active to_date,
decode(ffv.enabled_flag, 'N', 'No', 'Yes') enabled,
decode(ffv.summary_flag, 'N', 'No', 'Yes') parent,
ffhv.hierarchy_code rollup_group,
-- Qualifiers
decode(substr(to_char(ffv.compiled_value_attributes), 1, 1), 'N', 'No', 'Yes') budgeting_allowed,
decode(substr(to_char(ffv.compiled_value_attributes), 3, 1), 'N', 'No', 'Yes') posting_allowed,
(
SELECT description
FROM
fnd_lookup_values_vl
WHERE 1=1
AND lookup_type = 'ACCOUNT TYPE'
AND enabled_flag = 'Y'
AND ( view_application_id = 101 )
AND lookup_code = substr(to_char(ffv.compiled_value_attributes), 5, 1)
) account_type,
decode(substr(to_char(ffv.compiled_value_attributes), 7, 1), 'N', 'No', 'Yes') third_party_control,
decode(substr(to_char(ffv.compiled_value_attributes), 9, 1), 'N', 'No', 'Yes') reconcile
FROM
fnd_flex_values ffv,
fnd_flex_values_tl ffvt,
fnd_flex_value_sets ffvs,
fnd_id_flex_segments fifs,
fnd_flex_hierarchies_vl ffhv
WHERE
ffv.flex_value_id = ffvt.flex_value_id
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND fifs.flex_value_set_id = ffvs.flex_value_set_id
AND ffvs.flex_value_set_name = 'Natural Account' -- example
AND fifs.id_flex_code = 'GL#'
AND ffv.structured_hierarchy_level = ffhv.hierarchy_id (+)
ORDER BY
ffv.flex_value;