Monday, 22 January 2018

SQL Query to Fetch Accounting Flex Field segment Values with GL Account Description of all Segments

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

0 comments:

Post a Comment