Monday, 22 January 2018

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

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