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;
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
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.
3 Responses to “Key Flex Field Structures & How to Retrieve Information about segments based on code combination id”
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;
February 10, 2022 at 8:56 PM
excellent queries and explanation
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