The Easiest way to get Chart of Accounts Segment Descriptions

Such as account descriptions or cost centre descriptions as well as the value:

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

Otherwise you have to do a hideous number of joins back to FND_FLEX_VALUES_TL and generally hard-code in value set ids 🙂

Have a fun day
Jo

3 thoughts on “The Easiest way to get Chart of Accounts Segment Descriptions

  1. Please dont stop blogging…..i may not have commented before….but you were the only oracle apps + blogger + chocolate loving friend i had in the vast expanse of the cyber world…..I have been visiting this site on and off….and i haven’t seen any new posts in a very long time. I hope that your long absence is because something wonderful is happening in your life.

    Take care.
    – Sujay

  2. Hi Sujay
    LOL – I just got caught up with a big project and forgot to blog for a while (the dangers of consulting, huh?)! I shall make a concerted effort to post some stuff up – perhaps while I’m at OpenWorld next week I’ll get a chance!
    Thanks for reading!
    Jo

  3. Anonymous

    W O N D E R F U L!!!
    It works in seconds!

    Thanks a lot for sharing!
    Marilyne

Leave a Reply

Your email address will not be published. Required fields are marked *