How to find GL Code Combination ID and Description

Find GL Code Combination Id & Description

1)  ---Only in 11i ----

SELECT    gcc.segment1

       || '.'

       || gcc.segment2

       || '.'

       || gcc.segment3

       || '.'

       || gcc.segment4

       || '.'

       || gcc.segment5

          ACCOUNT_CODE,

          a1.description

       || '-'

       || a2.description

       || '-'

       || a3.description

       || '-'

       || a4.description

       || '-'

       || a5.description

          ACCOUNT_DECS

  FROM fnd_flex_values_vl A1,

       fnd_flex_values_vl A2,

       fnd_flex_values_vl A3,

       fnd_flex_values_vl A4,

       fnd_flex_values_vl A5,

       gl_code_combinations gcc

 WHERE     a1.flex_value = gcc.segment1

       AND a1.flex_value_set_id IN

              (SELECT FLEX_VALUE_SET_ID

                 FROM fnd_id_flex_segments

                WHERE     application_id = 101

                      AND id_flex_code = 'GL#'

                      AND enabled_flag = 'Y'

                      AND application_column_name = 'SEGMENT1')

       AND a2.flex_value = gcc.segment2

       AND a2.flex_value_set_id IN

              (SELECT flex_value_set_id

                 FROM fnd_id_flex_segments

                WHERE     application_id = 101

                      AND id_flex_code = 'GL#'

                      AND enabled_flag = 'Y'

                      AND application_column_name = 'SEGMENT2')

       AND a3.flex_value = gcc.segment3

       AND a3.flex_value_set_id IN

              (SELECT flex_value_set_id

                 FROM fnd_id_flex_segments

                WHERE     application_id = 101

                      AND id_flex_code = 'GL#'

                      AND enabled_flag = 'Y'

                      AND application_column_name = 'SEGMENT3')                      

       AND a4.flex_value = gcc.segment4

       AND a4.flex_value_set_id IN

              (SELECT flex_value_set_id

                 FROM fnd_id_flex_segments

                WHERE     application_id = 101

                      AND id_flex_code = 'GL#'

                      AND enabled_flag = 'Y'

                      AND application_column_name = 'SEGMENT4')

       AND a5.flex_value = gcc.segment5

       AND a5.flex_value_set_id IN

              (SELECT flex_value_set_id

                 FROM fnd_id_flex_segments

                WHERE     application_id = 101

                      AND id_flex_code = 'GL#'

                      AND enabled_flag = 'Y'

                      AND application_column_name = 'SEGMENT5');

2)  ---Only in R12----

SELECT gcc.concatenated_segments ACCOUNT_CODE,

       GL_FLEXFIELDS_PKG.get_concat_description (gcc.chart_of_accounts_id,

                                                 gcc.code_combination_id)

          ACCOUNT_DECS

  FROM gl_code_combinations_kfv gcc;

FND_PROFILE and FND_GLOBAL values

[For more information, please visit http://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T462356.htm]

Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');

   fnd_profile.value('MFG_ORGANIZATION_ID');

   fnd_profile.value('ORG_ID');

   fnd_profile.value('LOGIN_ID');

   fnd_profile.value('USER_ID');

   fnd_profile.value('USERNAME');

   fnd_profile.value('CONCURRENT_REQUEST_ID');

   fnd_profile.value('GL_SET_OF_BKS_ID');

   fnd_profile.value('SO_ORGANIZATION_ID');

   fnd_profile.value('APPL_SHRT_NAME');

   fnd_profile.value('RESP_NAME');

   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;

   FND_GLOBAL.APPS_INTIALIZE;

   FND_GLOBAL.LOGIN_ID;

   FND_GLOBAL.CONC_LOGIN_ID;

   FND_GLOBAL.PROG_APPL_ID;

   FND_GLOBAL.CONC_PROGRAM_ID;

   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;

   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;

   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;

   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');

   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;

   v_resp_id       := fnd_global.resp_id;

   v_user_id       := fnd_global.user_id;

     

   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);