Thursday, July 19, 2012

Chart of Accounts Segment Descriptions in Oracle EBS

4:19 PM    4 comments

Chart of Accounts Segment Descriptions in Oracle EBS R12

 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

 Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

4 comments:

  1. PravinFebruary 6, 2013 at 4:13 PM

    very very nice....

    ReplyDelete
  2. Haseeb SanaFebruary 7, 2013 at 10:15 AM

    You welcome Pravin

    ReplyDelete
  3. selvamJuly 3, 2017 at 12:37 PM

    GOod and helped me to get ot

    ReplyDelete
  4. Bronco AustinFebruary 10, 2021 at 8:06 PM


    Iam so thrilled because of finding your alluring website here.Actually i was searching for Oracle SQL.Your blog is so astounding and informative too..Iam very happy to find such a creative blog. Iam also find another one by mistake while am searching the same topicOracle APEX.Thank you soo much..

    ReplyDelete

Subscribe to: Post Comments (Atom)