Key Flex Field Structures & How to Retrieve Information about segments based on code combination id

How to fetch meanings of each segment related to a code combination id?

To fetch the details like description, meaning, id and value related to each segment based on code combination id.

Tables related to Flexi fields:

FND_ID_FLEXS
FND_ID_FLEX_SEGMENTS
FND_ID_FLEX_STRUCTURES
FND_FLEX_VALUES
FND_FLEX_VALUE_HIERARCHIES

Below are queries used by me to reach out the main query..

QUERY 1:

 The table (fnd_id_flexs) used in the below query stores registration information about key flexfields.
 Each row includes the four–character code that identifies the key flexfield and can be used to dig further
 The "id_flex_code" is the column which can be used to dig further


SELECT application_table_name
      ,DECODE (application_table_type,
               'S','Specific',
               'G','Generic'
              ) table_type
      ,concatenated_segs_view_name
      ,set_defining_column_name
      ,unique_id_column_name
      ,description
      ,id_flex_code  -- Used to drill down
FROM   fnd_id_flexs
WHERE id_flex_name LIKE 'Accounting%';



QUERY 2:

The below query gives information about the segment meanings
We got this data by using the value we got for "id_flex_code"  in query 1

SELECT segment_name
      ,segment_num
      ,enabled_flag
      ,required_flag
      ,flex_value_set_id  -- Used to drill down
      ,additional_where_clause
FROM  fnd_id_flex_segments
WHERE id_flex_code = 'GL#';


QUERY 3:
This query can be used to get various possible values for each segment.

SELECT ffv.flex_value_set_id
      ,ffv.flex_value_id
      ,ffv.flex_value
      ,ffv.enabled_flag
      ,ffvt.LANGUAGE
      ,ffvt.description
FROM   fnd_flex_values ffv
      ,fnd_flex_values_tl ffvt
WHERE  ffv.flex_value_id = ffvt.flex_value_id
AND    ffv.flex_value_set_id = '&id from query 2';


Main Query
 Below is our highlight and it can be used to get all possible information based on code combination id
 I had simplified the same. The decode statement differs from client to client. But it can be framed with use of query 2


SELECT fifs.segment_name
      ,fifs.segment_num
      ,ffv.flex_value
      ,ffvt.description
FROM  fnd_flex_values ffv
     ,fnd_flex_values_tl ffvt
     ,fnd_id_flex_segments fifs
     ,gl_code_combinations glv
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND   ffv.flex_value_set_id = fifs.flex_value_set_id
AND   fifs.id_flex_code = 'GL#'
AND   ffv.flex_value    = DECODE(fifs.segment_name
                                ,'BUSINESS UNIT',glv.segment1
                                ,'LOCATION'     ,glv.segment2
                                ,'DEPARTMENT'   ,glv.segment3
                                ,'ACCOUNT'      ,glv.segment4
                                ,'PROJECT'      ,glv.segment5
                                ,'INTERCOMPANY' ,glv.segment6
                                ,'SPARE'        ,glv.segment7
                                )
AND  glv.code_combination_id = '&code_combination_id'
ORDER BY ffv.flex_value_set_id;