Monday, April 12, 2010

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?

Guys, Here I tried to fetch the details like description, meaning, id and value related to each segment based on code combination id. During the course I came across many tables related to KEY FLEXIFIELDS like below

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;

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

Oracle Knowledge Point said...
November 27, 2017 at 7:56 PM

awesome :)
i have used this :
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
,'Company',glv.segment1
,'RC Code' ,glv.segment2
,'Department' ,glv.segment3
,'Account' ,glv.segment4
,'Future2' ,glv.segment5
,'Project' ,glv.segment6
,'Future4' ,glv.segment7
)
AND glv.code_combination_id ='2283'
ORDER BY ffv.flex_value_set_id;


Ashish G said...
February 10, 2022 at 8:56 PM

excellent queries and explanation


Ankur said...
December 7, 2022 at 6:54 AM

Can somebody please explain how to link flex field value to person assignment. I need to find out the value against a person assignment


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.