You can use the following query to get the description of COA of Oracle EBS by using apps.gl_flexfields_pkg.get_description_sql and avoid hideous number of joins back to FND_FLEX_VALUES_TL and generally hard-code in value set ids.
SELECT gcc.code_combination_id
,gcc.segment1
,gcc.segment2
,gcc.segment3
,gcc.segment4
,gcc.segment5
,gcc.segment6
,gcc.segment7
,gcc.segment8
,DECODE(gcc.segment1,NULL,'',apps.gl_flexfields_pkg.get_description_sql
(gcc.chart_of_accounts_id,1,gcc.segment1)) seg1_desc
,DECODE(gcc.segment2,NULL,'',apps.gl_flexfields_pkg.get_description_sql
( gcc.chart_of_accounts_id,2,gcc.segment2)) seg2_desc
,DECODE(gcc.segment3,NULL,'',apps.gl_flexfields_pkg.get_description_sql
(gcc.chart_of_accounts_id,3,gcc.segment3)) seg3_desc
,DECODE(gcc.segment4,NULL,'',apps.gl_flexfields_pkg.get_description_sql
( gcc.chart_of_accounts_id,4,gcc.segment4)) seg4_desc
,DECODE(gcc.segment5,NULL,'',apps.gl_flexfields_pkg.get_description_sql
( gcc.chart_of_accounts_id,5,gcc.segment5)) seg5_desc
,DECODE(gcc.segment6,NULL,'',apps.gl_flexfields_pkg.get_description_sql
( gcc.chart_of_accounts_id,6,gcc.segment6)) seg6_desc
,DECODE(gcc.SEGMENT7,NULL,'',apps.gl_flexfields_pkg.get_description_sql
( gcc.chart_of_accounts_id,7,gcc.segment7)) seg7_desc
,DECODE(gcc.SEGMENT9,NULL,'',apps.gl_flexfields_pkg.get_description_sql
( gcc.chart_of_accounts_id,8,gcc.segment8)) seg8_desc
,gcc.chart_of_accounts_id chart_of_accounts_id
,gcc.account_type
FROM gl_code_combinations gcc;
Thanks - http://oracleared.blogspot.com/2012/07/chart-of-accounts-segment-descriptions.html
