--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?
--
--Tables related to Flexi
fields:
FND_ID_FLEXS
FND_ID_FLEX_SEGMENTS
FND_ID_FLEX_STRUCTURES
FND_FLEX_VALUES
FND_FLEX_VALUE_HIERARCHIES
--Below queries are 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;