Chart of Accounts Segment Descriptions in Oracle EBS R12 (Script)

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