GL Code Combinaton segment and description

select * from gl_code_combinations

select * from gl_code_combinations_kfv

--Query to get GL Acounting Flex Fields

SELECT *
  FROM fnd_flex_key_seg_vset_v
 WHERE application_name = 'General Ledger'
   AND id_flex_name = 'Accounting Flexfield'
  

SELECT   PARENT_SEGMENT_NAME
  FROM   fnd_flex_key_seg_vset_v
 WHERE   application_name = 'General Ledger'
         AND id_flex_name = 'Accounting Flexfield'


--Query to Get values for  which segment stores which value

       
select * from fnd_application_vl where application_id = 101

select distinct ID_FLEX_CODE,APPLICATION_ID from fnd_id_flex_segments 

SELECT *
  FROM fnd_id_flex_segments
 WHERE application_id = 101 AND id_flex_code = 'GL#'
 order by application_column_name;

SELECT application_column_name,segment_name
  FROM fnd_id_flex_segments
 WHERE application_id = 101 AND id_flex_code = 'GL#'
 order by application_column_name;


-Query to Get Segment Value and Description of Each segment.

/* Formatted on 11/17/2017 12:12:15 PM (QP5 v5.114.809.3010) */
SELECT      gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '-'
         || gcc.segment4
         || '-'
         || gcc.segment5
         || '-'
         || gcc.segment6
            ACCOUNT,
            A1.DESCRIPTION
         || '-'
         || A2.DESCRIPTION
         || '-'
         || A3.DESCRIPTION
         || '-'
         || A4.DESCRIPTION
         || '-'
         || A5.DESCRIPTION
         || '-'
         || A6.DESCRIPTION
            description
  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,
         fnd_flex_values_vl A6,
         gl_code_combinations gcc
 WHERE       a1.flex_value = gcc.segment1
         AND a2.flex_value = gcc.segment2
         AND a3.flex_value = gcc.segment3
         AND a4.flex_value = gcc.segment4
         AND a5.flex_value = gcc.segment5
         AND a6.flex_value = gcc.segment6;


SELECT gl_flexfields_pkg.get_concat_description
                                          (chart_of_accounts_id,
                                           code_combination_id
                                          )
            FROM gl_code_combinations
           


SELECT gl_flexfields_pkg.get_description_sql
                                     (chart_of_accounts_id,--- chart of account id
                                      1,----- Position of segment
                                      segment1 ---- Segment value
                                     )
FROM gl_code_combinations

SELECT gl_flexfields_pkg.get_description_sql
                                     (chart_of_accounts_id,--- chart of account id
                                      1,----- Position of segment
                                      segment2 ---- Segment value
                                     )
FROM gl_code_combinations


GL Opening Balance:

/* Formatted on 11/17/2017 12:31:55 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   (  SELECT   GL.GL_CODE_COMBINATIONS.SEGMENT1 LEGAL_ENTITY,
                     GL.GL_CODE_COMBINATIONS.SEGMENT2 ACCOUNT,
                     GL.GL_CODE_COMBINATIONS.SEGMENT5 ENTRY_IND,
                     GL.GL_BALANCES.PERIOD_NAME,
                     GL_CODE_COMBINATIONS.ACCOUNT_TYPE,
                     SUM( (NVL (BEGIN_BALANCE_DR, 0)
                           - NVL (BEGIN_BALANCE_CR, 0))
                         + (NVL (PERIOD_NET_DR, 0) - NVL (PERIOD_NET_CR, 0)))
                        YTD_ACTUAL_AMOUNT
              FROM   GL.GL_BALANCES, GL.GL_CODE_COMBINATIONS
             WHERE   GL.GL_BALANCES.CODE_COMBINATION_ID =
                        GL.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
                     AND GL.GL_CODE_COMBINATIONS.ACCOUNT_TYPE IN
                              ('O', 'L', 'A')
                     AND GL.GL_BALANCES.LEDGER_ID = 2021
                     AND GL.GL_BALANCES.PERIOD_NAME = 'Jul-17'
                     --AND GL.GL_BALANCES.CURRENCY_CODE = 'USD'
                     AND GL.GL_BALANCES.ACTUAL_FLAG = 'A'
          GROUP BY   SEGMENT1,
                     SEGMENT2,
                     SEGMENT5,
                     PERIOD_NAME,
                     ACCOUNT_TYPE
          ORDER BY   ACCOUNT_TYPE, SEGMENT1)
 WHERE   YTD_ACTUAL_AMOUNT <> 0;

GL Code Combinaton segment and description 

/* Formatted on 11/17/2017 1:02:53 PM (QP5 v5.114.809.3010) */
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
 WHERE   1 = 1
 and ACCOUNT_TYPE = 'A'
 and GCC.CODE_COMBINATION_ID = :P_ID


ACCOUNT_TYPE column:

A - Asset

E - Expense

L - Liability

O - Owners Equity

R - Revenue