Oracle EBS SQL Script for Key Flexfield

 SQL Script to display the Parent Value for a given Child Accounting Flexfield Segment  Value :-

Below Query to fetch the Parent Key Flexfield for a specific Child Segment value

SELECT

    ffvv_parent.flex_value        AS parent_segment_value,
    ffvt_parent.description        AS parent_segment_description,
    ffvv_child.flex_value           AS Child_segment_value,
    ffvs.flex_value_set_name

FROM

    FND_FLEX_VALUE_HIERARCHIES ffvh,
    FND_FLEX_VALUES_VL ffvv_child,
    FND_FLEX_VALUES_VL ffvv_parent,
    FND_FLEX_VALUE_SETS ffvs,
    FND_FLEX_VALUES_TL ffvt_parent 

WHERE
    1=1 
AND ffvs.flex_value_set_name = 'XX_GL' -- Replace with your value set name
AND ffvv_child.flex_value_set_id = ffvs.flex_value_set_id
AND ffvv_child.flex_value IN ( 'XXXX' ,'XXXX') -- Replace with the child segment value
AND ffvh.flex_value_set_id = ffvv_child.flex_value_set_id
AND ffvh.child_flex_value_low = ffvv_child.flex_value
AND ffvv_parent.flex_value_set_id = ffvs.flex_value_set_id
AND ffvv_parent.flex_value = ffvh.parent_flex_value
AND ffvt_parent.flex_value_id = ffvv_parent.flex_value_id

AND ffvt_parent.language = USERENV('LANG');