How to query key flexfields

It is quite common that in Oracle Applications you need to write queries listing specific account combinations. The problem with that is that the account combinations are stored as their ids and you have to go to the GL_CODE_COMBINATIONS table to find the specific segments and concatenate them one by one.

Easy to do but always waste of time. Moreover, each EBS install has a different number of segments and query written for one client needs to be rewritten for another.

Well, not any more. There are Oracle APIs that allow you to get the complete combination as a string by passing the CCID

Here is the syntax:

fnd_flex_ext.get_segs('SQLGL', 'GL#', chart_of_accounts_id, code_combination_id)

Now, if you are on release 12 and want to have the concatenated combination descriptions you can also run this:

xla_oa_functions_pkg.get_ccid_description (chart_of_accounts_id, code_combination_id)

Here is a sample query:

select

fnd_flex_ext.get_segs( 'SQLGL',

'GL#',

(select chart_of_accounts_id

from GL_SETS_OF_BOOKS

where name='Vision Operations (USA)'),

code_combination_id) account,

xla_oa_functions_pkg.get_ccid_description(

(select chart_of_accounts_id

from GL_SETS_OF_BOOKS

where name='Vision Operations (USA)'),

Code_combination_id) description

from GL_JE_LINES

The fnd_flex_ext.get_segs call is universal and will work for any key flexfield. Here is the syntax for HR position hierarchies:

FND_FLEX_EXT.GET_SEGS('PER', 'POS', id_flex_num, position_definition_id)

and here is a sample query:

select

FND_FLEX_EXT.GET_SEGS('PER', 'POS', id_flex_num, position_definition_id) position_definition

from per_position_definitions