How to extract the Chart of Account (COA) from Oracle

Here are a few useful SQL scripts used by GL consultants:

1) This SQL lists all GL Segment values:

select fvs.FLEX_VALUE_SET_NAME, ffv.FLEX_VALUE, ffvt.FLEX_VALUE_MEANING, ffvt.DESCRIPTION

,ffv.ENABLED_FLAG, ffv.SUMMARY_FLAG

,ffv.START_DATE_ACTIVE ,ffv.END_DATE_ACTIVE, ffv.HIERARCHY_LEVEL

,replace(ffv.COMPILED_VALUE_ATTRIBUTES,chr(10),'_') Budget_Post

from applsys.FND_FLEX_VALUES_TL ffvt, applsys.FND_FLEX_VALUES ffv, applsys.FND_FLEX_VALUE_SETS fvs,

applsys.FND_ID_FLEX_SEGMENTS fseg, applsys.FND_ID_FLEX_STRUCTURES fifs,

applsys.FND_ID_FLEXS fif, applsys.FND_APPLICATION_TL fat

where ffvt.LANGUAGE = 'US'

and ffvt.FLEX_VALUE_ID = ffv.FLEX_VALUE_ID

and ffv.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID

and fvs.FLEX_VALUE_SET_ID = fseg.FLEX_VALUE_SET_ID

and fseg.APPLICATION_ID = fifs.APPLICATION_ID

and fseg.ID_FLEX_CODE = fifs.ID_FLEX_CODE

and fseg.ID_FLEX_NUM = fifs.ID_FLEX_NUM

and fifs.ID_FLEX_CODE = fif.ID_FLEX_CODE

and fifs.application_id = fif.application_id

and fif.application_id = fat.application_id

and fif.ID_FLEX_NAME = 'Accounting Flexfield'

and fat.APPLICATION_NAME = 'General Ledger'

and fat.LANGUAGE = 'US'

2) This SQL lists all code combinations created:

SELECT

gcc.code_combination_id

,gcc.segment1

,gcc.segment2

,gcc.segment3

,gcc.segment4

,gcc.segment5

,gcc.segment6

,gcc.segment7

,gcc.segment8

,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,1,gcc.segment1),1,40) segment1_desc

,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,2,gcc.segment2),1,40) segment2_desc

,DECODE(gcc.segment3,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,3,gcc.segment3),1,40)) segment3_desc

,DECODE(gcc.segment4,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,4,gcc.segment4),1,40)) segment4_desc

,DECODE(gcc.segment5,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,5,gcc.segment5),1,40)) segment5_desc

,DECODE(gcc.segment6,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,6,gcc.segment6),1,40)) segment6_desc

,DECODE(gcc.SEGMENT7,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,7,gcc.segment7),1,40)) segment7_desc

,DECODE(gcc.SEGMENT9,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,8,gcc.segment8),1,40)) segment8_desc

,gcc.chart_of_accounts_id chart_of_accounts_id

,gcc.account_type

FROM

gl_code_combinations gcc

-- For references visit my friend's blog here