Key Flexfield Segment Parent/Child hierarchy Queries

Parent Value:A parent value is a value that has one or more child values associated with it. A parent value can be assigned to a rollup group. You create parent-child relationships by defining a range of child values that belong to a parent value. You can use parent-child relationships for reporting and other application purposes.
Child Value

Child Value:
A child value is a value that lies in a range of values belonging to a parent value. A child value can belong to more than one parent value. A child value is not a dependent value; that is, the actual value of the child does not depend on the value of another segment. You create parent-child relationships by defining a range of child values that belong to a parent value.

Following query will derive Parent and Children Accounting Flexfield values, the query can be modified to derive the parent/child segment values for other segments. 

Parameters:

:P_Segment_Num -- To derive the segment number -- Required parameter

:P_Parent_Flex_Value --  For the parent segment value for which the children are required, if no value is passed it the query will fetch all the parent, child values for the segment mentioned in P_Segment_Num

 Note: Replace "<n>" with the segment number for which you want to find the 

          children

 SELECT gcc.code_combination_id
      ,ffvc.parent_flex_value
      ,gcc.segment1 Balancing_Segment
      ,gcc.segment<n>,ffvc.description
  FROM gl_code_combinations gcc,
       fnd_flex_value_children_v ffvc,
       fnd_id_flex_segments fifs
 WHERE 1=1
   AND fifs.flex_value_set_id = ffvc.flex_value_set_id
   AND fifs.id_flex_code='GL#'
   AND ffvc.parent_flex_value = NVL(:P_Parent_Flex_value, ffvc.parent_flex_value)
   AND ffvc.flex_value = gcc.segment<n>
   AND fifs.application_column_name = 'SEGMENT'||:P_Segment_Num;