SQL Query to Fetch Accounting Flex Field segment Values with GL Account Description of all Segments
SQL Query to Fetch GL Accounting Flex Field segment Values with GL Account Description of all Segments. Using this query you can get the value of Key flex fields (KFF) segment values and their descriptions in oracle apps. This query is help to extract Key flex fields (KFF) segment master data in oracle apps.
select gcc.code_combination_id,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||segment6||'.'||segment7||'.'||segment8 segments,
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Company'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment1)||'.'||
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'SBU'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment2)||'.'||
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Location'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment3)||'.'||
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Cost Center'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment4)||'.'||
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Account'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment5)||'.'||
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Intercompany'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment6)||'.'||
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Future 1'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment7)||'.'||
(SELECT description
FROM fnd_flex_values_tl ffvt,
fnd_flex_values ffv,
fnd_id_flex_segments fifs
WHERE ffv.flex_value_id = ffvt.flex_value_id
AND fifs.flex_value_set_id = ffv.flex_value_set_id
AND fifs.segment_name = 'Future 2'
AND fifs.id_flex_code = 'GL#'
AND ffv.flex_value = gcc.segment8) account_description
from gl_code_combinations gcc
