Key Flexfields segments value set details (Values/Effective/Hierarchy/Qualifiers)
SELECT ffvs.flex_value_set_name,
ffv.flex_value,flex_value_meaning,
ffvt.description,
ffv.start_date_active,
ffv.end_date_active,
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 acct_type_desc2 FROM gl_acct_typ gat WHERE gat.acct_type_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,
value_category
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 fifs.id_flex_code = 'GL#'
AND ffvs.flex_value_set_name = :P_GL_ACCOUNT
-- AND ffv.flex_value_set_id = 1016027
AND ffv.structured_hierarchy_level = ffhv.hierarchy_id(+)
ORDER BY ffv.flex_value