Query to get Key Flexfield Values

Hi Friends, we are going to discuss about Query to get Key Flexfield Values. We will share the detail sql query which will help to extract the key flexfield values in oracle apps. Using this sql query we can able to extract the key flex field segment values and the description. If we want to know the Key Flexfield segment complete values then this query really helps to fetch those details in oracle apps. We will also share the important tables in this key flexfield query. Using this Query to get Key Flexfield Values we can also develop the custom oracle reports. Please find below the complete detail about sql Query to get Key Flexfield Values.

Query to get Key Flexfield Values

4 Important Table's in Query to get Key Flexfield Values

1.fnd_flex_values_tl

2.fnd_flex_values

3.fnd_id_flex_segments

4.gl_code_combinations

Detail SQL Query to get Key Flexfield Values

select gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||segment6||'.'||segment7||'.'||segment8 key_flex_segmnts,

(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) key_flexfield_desc

from gl_code_combinations gcc

Query to get Key Flexfield Values