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

Link Between FA and GL table using XLA tables

/* Formatted on 5/18/2017 4:39:48 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   GL_JE_HEADERS GJH,
         GL_JE_LINES GJL,
         GL_IMPORT_REFERENCES GIR,
         XLA_AE_LINES XAL,
         XLA_AE_HEADERS XAH,
         FA_TRANSACTION_HEADERS FTH,
         FA_ADJUSTMENTS FAA
 WHERE       GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
         AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
         AND GJL.JE_LINE_ID = GIR.JE_LINE_ID
         AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
         AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
         AND XAH.EVENT_ID = FTH.EVENT_ID
         AND FTH.TRANSACTION_HEADER_ID = FAA.TRANSACTION_HEADER_ID

Link between GL and AR through XLA

The main link to bind information together is the GL_SL_LINK_ID. This field exists in GL_JE_LINES, GL_IMPORT_REFERENCES and XLA_AE_LINES tables.

Also, the XLA_DISTRIBUTION_LINKS table contains the application_id, event_id, ae_header_id, ae_line_num from the XLA Tables and source_distribution_id_num_1 will be the cust_trx_line_gl_dist_id in the case of a transaction.

Query 1: For complete Transaction

/* Formatted on 5/18/2017 4:37:29 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   RA_CUSTOMER_TRX_ALL RCTA,
         RA_CUST_TRX_LINE_GL_DIST_ALL RCTG,
         XLA_TRANSACTION_ENTITIES XTE,
         XLA_EVENTS XE
 WHERE       RCTA.CUSTOMER_TRX_ID = RCTG.CUSTOMER_TRX_ID
         AND RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1
         AND XTE.ENTITY_ID = XE.ENTITY_ID

Query 2: After Create accounting Run

/* Formatted on 5/18/2017 4:38:04 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   RA_CUST_TRX_LINE_GL_DIST_ALL RCTG,
         XLA_DISTRIBUTION_LINKS XDL,
         XLA_AE_LINES XAL,
         XLA_AE_HEADERS XAH
 WHERE       RCTG.CUST_TRX_LINE_GL_DIST_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
         AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
         AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID

Query 3 : After Run Transfer to GL

/* Formatted on 5/18/2017 4:38:33 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   XLA_AE_LINES XAL,
         GL_JE_LINES GJL,
         GL_IMPORT_REFERENCES GIR,
         GL_JE_HEADERS GJH
 WHERE       XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID
         AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
         AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
         AND GIR.JE_HEADER_ID = GJH.JE_HEADER_ID

API - Create New Code Combination In GL

DECLARE
   CURSOR c1
   IS
      SELECT *
        FROM temp_gl_code;

   v_chart_of_accounts_id    VARCHAR2 (200);
   v_n_code_combination_id   VARCHAR2 (200);
BEGIN
   FOR i in c1
   LOOP
      SELECT gsob.chart_of_accounts_id      -- To Get the Chart of Accounts id
        INTO v_chart_of_accounts_id
        FROM gl_sets_of_books gsob
       WHERE gsob.set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');

      v_n_code_combination_id :=
         fnd_flex_ext.get_ccid ('SQLGL',
                                'GL#',
                                v_chart_of_accounts_id,
                                TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                                i.CODE_COBIN
                               );

      IF v_n_code_combination_id <> 0
      THEN
         DBMS_OUTPUT.put_line ('success');
      ELSE
         DBMS_OUTPUT.put_line ('failure');
      END IF;
   END LOOP;
END;

Query to find accounting flexfield structure in oracle apps

/* Formatted on 3/27/2017 4:33:18 PM (QP5 v5.114.809.3010) */
  SELECT   sob.name Ledger_Name,
           sob.ledger_id Ledger_Id,
           sob.chart_of_accounts_id coa_id,
           fifst.id_flex_structure_name struct_name,
           ifs.segment_name,
           ifs.application_column_name column_name,
           sav1.attribute_value BALANCING,
           sav2.attribute_value COST_CENTER,
           sav3.attribute_value NATURAL_ACCOUNT,
           sav4.attribute_value INTERCOMPANY,
           sav5.attribute_value SECONDARY_TRACKING,
           sav6.attribute_value GLOBAL,
           ffvs.flex_value_set_name,
           ffvs.flex_value_set_id
    FROM   fnd_id_flex_structures fifs,
           fnd_id_flex_structures_tl fifst,
           fnd_segment_attribute_values sav1,
           fnd_segment_attribute_values sav2,
           fnd_segment_attribute_values sav3,
           fnd_segment_attribute_values sav4,
           fnd_segment_attribute_values sav5,
           fnd_segment_attribute_values sav6,
           fnd_id_flex_segments ifs,
           fnd_flex_value_sets ffvs,
           gl_ledgers sob
   WHERE       1 = 1
           AND fifs.id_flex_code = 'GL#'
           AND fifs.application_id = fifst.application_id
           AND fifs.id_flex_code = fifst.id_flex_code
           AND fifs.id_flex_num = fifst.id_flex_num
           AND fifs.application_id = ifs.application_id
           AND fifs.id_flex_code = ifs.id_flex_code
           AND fifs.id_flex_num = ifs.id_flex_num
           AND sav1.application_id = ifs.application_id
           AND sav1.id_flex_code = ifs.id_flex_code
           AND sav1.id_flex_num = ifs.id_flex_num
           AND sav1.application_column_name = ifs.application_column_name
           AND sav2.application_id = ifs.application_id
           AND sav2.id_flex_code = ifs.id_flex_code
           AND sav2.id_flex_num = ifs.id_flex_num
           AND sav2.application_column_name = ifs.application_column_name
           AND sav3.application_id = ifs.application_id
           AND sav3.id_flex_code = ifs.id_flex_code
           AND sav3.id_flex_num = ifs.id_flex_num
           AND sav3.application_column_name = ifs.application_column_name
           AND sav4.application_id = ifs.application_id
           AND sav4.id_flex_code = ifs.id_flex_code
           AND sav4.id_flex_num = ifs.id_flex_num
           AND sav4.application_column_name = ifs.application_column_name
           AND sav5.application_id = ifs.application_id
           AND sav5.id_flex_code = ifs.id_flex_code
           AND sav5.id_flex_num = ifs.id_flex_num
           AND sav5.application_column_name = ifs.application_column_name
           AND sav6.application_id = ifs.application_id
           AND sav6.id_flex_code = ifs.id_flex_code
           AND sav6.id_flex_num = ifs.id_flex_num
           AND sav6.application_column_name = ifs.application_column_name
           AND sav1.segment_attribute_type = 'GL_BALANCING'
           AND sav2.segment_attribute_type = 'FA_COST_CTR'
           AND sav3.segment_attribute_type = 'GL_ACCOUNT'
           AND sav4.segment_attribute_type = 'GL_INTERCOMPANY'
           AND sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING'
           AND sav6.segment_attribute_type = 'GL_GLOBAL'
           AND ifs.id_flex_num = sob.chart_of_accounts_id
           AND ifs.flex_value_set_id = ffvs.flex_value_set_id
           AND sob.ledger_id =
                 NVL (fnd_profile.VALUE ('GL_SET_OF_BKS_ID'), sob.ledger_id)
ORDER BY   sob.name, sob.chart_of_accounts_id, ifs.application_column_name;

Oracle GL Account Code Combination ID’s (CCID’s) through APIs

1] FND_FLEX_EXT.GET_COMBINATION_ID:

This API Finds combination_id for given set of key flexfield segment values. Segment values must be input in segments(1) – segments(n_segments) in the order displayed. 

It also creates a new combination if it is valid and the flexfield allows dynamic inserts and the combination does not already exist. It commit the transaction soon after calling this function since if a combination is created it will prevent other users creating similar combinations on any flexfield until a commit is issued.

It performs all checks on values including security and cross-validation. Value security rules will be checked for the current user identified in the FND_GLOBAL package. 

Generally pass in SYSDATE for validation date. If validation date is null, this function considers expired values valid and checks all cross-validation rules even if they are outdated.

This function returns TRUE if combination valid or FALSE and sets error message using FND_MESSAGE utility on error or if invalid. If this function returns FALSE, use GET_MESSAGE to get the text of the error message in the language of the database, or GET_ENCODED_MESSAGE to get the error message in a language-independent encoded format.
 
The Combination_id output may be NULL if combination is invalid.

/* Formatted on 3/27/2017 4:30:27 PM (QP5 v5.114.809.3010) */
SET serveroutput ON;

DECLARE
   l_application_short_name   VARCHAR2 (240);
   l_key_flex_code            VARCHAR2 (240);
   l_structure_num            NUMBER;
   l_validation_date          DATE;
   n_segments                 NUMBER;
   SEGMENTS                   APPS.FND_FLEX_EXT.SEGMENTARRAY;
   l_combination_id           NUMBER;
   l_data_set                 NUMBER;
   l_return                   BOOLEAN;
   l_message                  VARCHAR2 (240);
BEGIN
   l_application_short_name := 'SQLGL';
   l_key_flex_code := 'GL#';

   SELECT   id_flex_num
     INTO   l_structure_num
     FROM   apps.fnd_id_flex_structures
    WHERE   ID_FLEX_CODE = 'GL#'
            AND ID_FLEX_STRUCTURE_CODE = :ACCOUNTING_FLEXFIELD;

   l_validation_date := SYSDATE;
   n_segments := 6;
   segments (1) := '00101';
   segments (2) := '28506';
   segments (3) := '00000';
   segments (4) := '09063';
   segments (5) := '00000';
   segments (6) := '00000';
   l_data_set := NULL;

   l_return :=
      FND_FLEX_EXT.GET_COMBINATION_ID (
         application_short_name   => l_application_short_name,
         key_flex_code            => l_key_flex_code,
         structure_number         => l_structure_num,
         validation_date          => l_validation_date,
         n_segments               => n_segments,
         segments                 => segments,
         combination_id           => l_combination_id,
         data_set                 => l_data_set
      );
   l_message := FND_FLEX_EXT.GET_MESSAGE;

   IF l_return
   THEN
      DBMS_OUTPUT.PUT_LINE ('l_Return = TRUE');
      DBMS_OUTPUT.PUT_LINE ('COMBINATION_ID = ' || l_combination_id);
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Error: ' || l_message);
   END IF;
END;

2] FND_FLEX_EXT.get_ccid:

This API gets combination id for the specified key flexfield segments.It is identical to get_combination_id() except this function takes segment values in a string concatenated by the segment  delimiter for this flexfield, and returns a positive combination id if valid or 0 on error.

3] FND_FLEX_KEYVAL.VALIDATE_SEGS:

These key flexfields server validations API are a low level interface to key flexfields validation.  They are designed to allow access to all the flexfields functionality, and to allow the user to get only the information they need in return.  Because of their generality, these functions are more difficult to use than those in the FND_FLEX_EXT package.  Oracle strongly suggests using the functions in FND_FLEX_EXT package if at all possible.
This function finds combination from given segment values.  Segments are passed in as a concatenated string in increasing order of segment_number (display order).
Various Operations that can be performed are:

• ‘FIND_COMBINATION’ – Combination must already exist.
• ‘CREATE_COMBINATION’ – Combination is created if doesn’t exist.
• ‘CREATE_COMB_NO_AT’ – same as create_combination but does not use an autonomous transaction.
• ‘CHECK_COMBINATION’ – Checks if combination valid, doesn’t create.
• ‘DEFAULT_COMBINATION’ – Returns minimal default combination.
• ‘CHECK_SEGMENTS’ – Validates segments individually.

If validation date is NULL checks all cross-validation rules. It returns TRUE if combination valid or FALSE and sets error message on server if invalid. Use the default values if you do not want any special functionality.

/* Formatted on 3/27/2017 4:31:51 PM (QP5 v5.114.809.3010) */
SET serveroutput ON;

DECLARE
   l_segment1            GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
   l_segment2            GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
   l_segment3            GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
   l_segment4            GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
   l_segment5            GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
   l_segment6            GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
   l_valid_combination   BOOLEAN;
   l_cr_combination      BOOLEAN;
   l_ccid                GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
   l_structure_num       FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
   l_conc_segs           GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
   p_error_msg1          VARCHAR2 (240);
   p_error_msg2          VARCHAR2 (240);
BEGIN
   l_segment1 := '00101';
   l_segment2 := '28506';
   l_segment3 := '00000';
   l_segment4 := '14302';
   l_segment5 := '00455';
   l_segment6 := '00000';
   l_conc_segs :=
         l_segment1
      || '.'
      || l_segment2
      || '.'
      || l_segment3
      || '.'
      || l_segment4
      || '.'
      || l_segment5
      || '.'
      || l_segment6;
   BEGIN
      SELECT   id_flex_num
        INTO   l_structure_num
        FROM   apps.fnd_id_flex_structures
       WHERE   id_flex_code = 'GL#'
               AND id_flex_structure_code = 'EPC_GL_ACCOUNTING_FLEXFIELD';
   EXCEPTION
      WHEN OTHERS
      THEN
         l_structure_num := NULL;
   END;
   ---------------Check if CCID exits with the above Concatenated Segments---------------
   BEGIN
      SELECT   code_combination_id
        INTO   l_ccid
        FROM   apps.gl_code_combinations_kfv
       WHERE   concatenated_segments = l_conc_segs;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_ccid := NULL;
   END;

   IF l_ccid IS NOT NULL
   THEN
      ------------------------The CCID is Available----------------------
      DBMS_OUTPUT.PUT_LINE ('COMBINATION_ID= ' || l_ccid);
   ELSE
      DBMS_OUTPUT.PUT_LINE (
         'This is a New Combination. Validation Starts....'
      );
      ------------Validate the New Combination--------------------------
      l_valid_combination :=
         APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS (
            operation          => 'CHECK_COMBINATION',
            appl_short_name    => 'SQLGL',
            key_flex_code      => 'GL#',
            structure_number   => L_STRUCTURE_NUM,
            concat_segments    => L_CONC_SEGS
         );
      p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

      IF l_valid_combination
      THEN
         DBMS_OUTPUT.PUT_LINE (
            'Validation Successful! Creating the Combination...'
         );
         -------------------Create the New CCID--------------------------

         L_CR_COMBINATION :=
            APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS (
               operation          => 'CREATE_COMBINATION',
               appl_short_name    => 'SQLGL',
               key_flex_code      => 'GL#',
               structure_number   => L_STRUCTURE_NUM,
               concat_segments    => L_CONC_SEGS
            );
         p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

         IF l_cr_combination
         THEN
            -------------------Fetch the New CCID--------------------------
            SELECT   code_combination_id
              INTO   l_ccid
              FROM   apps.gl_code_combinations_kfv
             WHERE   concatenated_segments = l_conc_segs;

            DBMS_OUTPUT.PUT_LINE ('NEW COMBINATION_ID = ' || l_ccid);
         ELSE
            -------------Error in creating a combination-----------------
            DBMS_OUTPUT.PUT_LINE (
               'Error in creating the combination: ' || p_error_msg2
            );
         END IF;
      ELSE
         --------The segments in the account string are not defined in gl value set----------
         DBMS_OUTPUT.PUT_LINE (
            'Error in validating the combination: ' || p_error_msg1
         );
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLCODE || ' ' || SQLERRM);
END;

Legal Entitiers & Organizations Detail Query

/* Formatted on 11/16/2016 1:50:54 PM (QP5 v5.114.809.3010) */
  SELECT   HRO.ORGANIZATION_ID,
           HRO.NAME,
           HOI.ORG_INFORMATION_CONTEXT,
           SOB2.NAME "LE SET OF BOOKS",
           HRO_LE.NAME "OU LEGAL ENT",
           HOI.ORG_INFORMATION2 "LE VAT CODES",
           SOB.NAME "OU SET OF BOOKS"
    FROM   HR_ALL_ORGANIZATION_UNITS_TL HRO,
           HR_ORGANIZATION_INFORMATION_V HOI,
           GL_SETS_OF_BOOKS SOB,
           GL_SETS_OF_BOOKS SOB2,
           HR_ALL_ORGANIZATION_UNITS_TL HRO_LE
   WHERE   HOI.ORG_INFORMATION_CONTEXT IN
                 ('Legal Entity Accounting', 'Operating Unit Information')
           AND HRO.ORGANIZATION_ID = HOI.ORGANIZATION_ID
           AND TO_CHAR (SOB.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION3
           AND TO_CHAR (SOB2.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION1
           AND TO_CHAR (HRO_LE.ORGANIZATION_ID(+)) = HOI.ORG_INFORMATION2
--AND SUBSTR(HRO.NAME,1,2) IN ('BE','LU','ES')
ORDER BY   2, 3

GL PERIOD STATUSES QUERY

/* Formatted on 11/16/2016 1:48:24 PM (QP5 v5.114.809.3010) */
  SELECT   sob.short_name,
           ps.period_name,
           ps.show_status,
           ps.start_date || ' to ' || ps.end_date,
           ps.period_year,
           ps.period_num
    FROM   GL_PERIOD_STATUSES_V ps, GL_SETS_OF_BOOKS sob
   WHERE   ps.set_of_Books_id = sob.SET_OF_BOOKS_ID AND application_id = 101 --and period_year = 2006
          --and substr(sob.short_name,1,2) in ('ES','LU','BE')
           AND ps.show_status NOT IN ('Never Opened')
ORDER BY   1, 5, 6 DESC

GL Journal Opening Balance query in oracle apps

/* Formatted on 11/16/2016 1:42:07 PM (QP5 v5.114.809.3010) */
  SELECT   SOB.NAME,
           GB.PERIOD_NAME,
           GCC.SEGMENT1,
           GCC.SEGMENT2,
           GCC.SEGMENT3,
           GCC.SEGMENT4,
           GCC.SEGMENT5,
           GCC.SEGMENT6,
           GCC.SEGMENT7,
           GCC.SEGMENT8,
           GCC.SEGMENT9,
           GCC.SEGMENT10,
           (CASE
               WHEN SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)) >=
                       0
               THEN
                  (SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)))
               ELSE
                  0
            END)
              "DEBIT",
           (CASE
               WHEN SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)) <=
                       0
               THEN
                  (SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0))
                   * -1)
               ELSE
                  0
            END)
              "CREDIT"
    FROM   GL_BALANCES GB, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB
   WHERE       GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
           AND GB.ACTUAL_FLAG = 'A'
           AND  GB.PERIOD_NAME = 'Apr-16'
           AND GB.CURRENCY_CODE = SOB.CURRENCY_CODE
           AND SUBSTR (SOB.SHORT_NAME, 1, 2) IN ('HK', 'JP', 'TH', 'SG', 'CN')
           AND GB.TEMPLATE_ID IS NULL
           AND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
GROUP BY   SOB.NAME,
           GB.ACTUAL_FLAG,
           GB.PERIOD_NAME,
           GCC.SEGMENT1,
           GCC.SEGMENT2,
           GCC.SEGMENT3,
           GCC.SEGMENT4,
           GCC.SEGMENT5,
           GCC.SEGMENT6,
           GCC.SEGMENT7,
           GCC.SEGMENT8,
           GCC.SEGMENT9,
           GCC.SEGMENT10,
           NVL (GB.PERIOD_NET_DR, 0),
           NVL (GB.PERIOD_NET_CR, 0)
  HAVING   SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)) <> 0
ORDER BY   1,
           2,
           3,
           4,
           5,
           6,
           7,
           8,
           9

Cross Validation Rule Detail Query in Oracle Apps

/* Formatted on 11/16/2016 1:35:59 PM (QP5 v5.114.809.3010) */
  SELECT   FST.ID_FLEX_STRUCTURE_NAME,
           R.FLEX_VALIDATION_RULE_NAME,
           R.ENABLED_FLAG--,       R.ERROR_SEGMENT_COLUMN_NAME"ERR SEG"
                         --,       TL.DESCRIPTION
                         --,       TL.ERROR_MESSAGE_TEXT"ERROR MESSAGE"
           ,
           L.ENABLED_FLAG,
           L.INCLUDE_EXCLUDE_INDICATOR "INC?",
           L.CONCATENATED_SEGMENTS_LOW "FROM",
           L.CONCATENATED_SEGMENTS_HIGH "TO",
           L.LAST_UPDATED_BY,
           L.LAST_UPDATE_DATE,
           R.APPLICATION_ID
    FROM   FND_FLEX_VALIDATION_RULES R,
           FND_FLEX_VDATION_RULES_TL TL,
           FND_FLEX_VALIDATION_RULE_LINES L,
           FND_ID_FLEX_STRUCTURES_VL FST
   WHERE       R.APPLICATION_ID = TL.APPLICATION_ID
           AND FST.ID_FLEX_NUM = R.ID_FLEX_NUM
           AND R.ID_FLEX_CODE = TL.ID_FLEX_CODE
           AND R.ID_FLEX_NUM = TL.ID_FLEX_NUM
           AND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
           AND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
           AND R.APPLICATION_ID = L.APPLICATION_ID
           AND R.ID_FLEX_CODE = L.ID_FLEX_CODE
           AND R.ID_FLEX_NUM = L.ID_FLEX_NUM
           AND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
           AND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
--           AND R.APPLICATION_ID != 801
--       OPTIONAL FILTERS BELOW TO LIMIT QUERY TO SPECIFIC CVR OR LINES
--AND    R.ERROR_SEGMENT_COLUMN_NAME = 'SEGMENT5'
--AND       TL.ERROR_MESSAGE_TEXT LIKE '%PLEASE USE A VALID R%'
--AND    R.FLEX_VALIDATION_RULE_NAME LIKE 'BE GROUP ERROR%'
--AND       TL.ERROR_MESSAGE_TEXT LIKE '%94005%'
--AND       L.INCLUDE_EXCLUDE_INDICATOR = 'E'
ORDER BY   1,
           R.FLEX_VALIDATION_RULE_NAME,
           L.INCLUDE_EXCLUDE_INDICATOR DESC,
           L.CONCATENATED_SEGMENTS_LOW

Inventory to GL link in R12

/* Formatted on 8/19/2015 6:31:42 PM (QP5 v5.240.12305.39446) */
SELECT DISTINCT glh.*
  FROM xla_transaction_entities_upg xte,
       xla_events xe,
       xla_distribution_links xdl,
       mtl_transaction_accounts mta,
       xla_ae_headers xah,
       xla_ae_lines xal,
       gl_import_references gir,
       gl_je_headers glh
 WHERE     1 = 1
       AND xte.source_id_int_1 = &transaction_id
       AND xte.entity_id = xe.entity_id
       AND mta.transaction_id = xte.source_id_int_1
       AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
       AND xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id
       AND xdl.ae_header_id = xah.ae_header_id
       AND xal.ae_header_id = xdl.ae_header_id
       AND xal.ae_header_id = xah.ae_header_id
       AND gir.gl_sl_link_table = 'XLAJEL'
       AND gir.gl_sl_link_id = xal.gl_sl_link_id
       AND gir.je_header_id = glh.je_header_id;

AR Adjustments to GL query in oracle apps

/* Formatted on 8/19/2015 6:16:05 PM (QP5 v5.240.12305.39446) */
SELECT glp.start_date,
       gjh.je_header_id,
       gjh.doc_sequence_value voucher_no,
       gjh.je_source,
       gjh.je_category,
       entity_code,
       gjh.period_name,
       gjh.status,
       gjh.actual_flag,
       gjh.default_effective_date,
       gjl.je_line_num,
       gjl.code_combination_id,
       gjl.description voucher_desc,
       xal.accounted_dr debit,
       xal.accounted_cr credit,
          gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
       || '-'
       || gcc.segment7
          account_code,
       TO_CHAR (aaa.adjustment_number) trx_num,
       aaa.apply_date trx_date
  FROM gl.gl_je_headers gjh,
       gl.gl_je_lines gjl,
       gl.gl_code_combinations gcc,
       gl.gl_periods glp,
       gl.gl_import_references imp,
       xla.xla_ae_lines xal,
       xla.xla_ae_headers xah,
       xla.xla_events xe,
       xla.xla_transaction_entities xte,
       ar_adjustments_all aaa
 WHERE     1 = 1
       AND gjh.je_header_id = gjl.je_header_id
       AND gjl.status || '' = 'P'
       AND gjl.code_combination_id = gcc.code_combination_id
       AND gjh.period_name = glp.period_name
       AND glp.period_set_name = :p_period_set_name
       AND glp.adjustment_period_flag <> 'Y'
       AND gjh.je_source = 'Receivables'
       AND gjl.je_header_id = imp.je_header_id
       AND gjl.je_line_num = imp.je_line_num
       AND imp.gl_sl_link_id = xal.gl_sl_link_id
       AND imp.gl_sl_link_table = xal.gl_sl_link_table
       AND xal.application_id = xah.application_id
       AND xal.ae_header_id = xah.ae_header_id
       AND xah.application_id = xe.application_id
       AND xah.event_id = xe.event_id
       AND xe.application_id = xte.application_id
       AND xte.application_id = 222
       AND xe.entity_id = xte.entity_id
       AND xte.entity_code = 'ADJUSTMENTS'
       AND xte.source_id_int_1 = aaa.adjustment_id
       AND gjh.default_effective_date BETWEEN :p_period_from_start_date
                                          AND :p_period_to_end_date
       AND (gjh.actual_flag = :p_actual_flag OR :p_actual_flag IS NULL)

SQL Query to Link GL Journal and SLA Tables for 'Misc Receipts','Receipts' and 'Reconciled Payments'

SELECT  ada.line_id, jh.je_category Category,
  jh.period_name Period_Name,
  NULL Invoice_No_or_Memo_No,
  nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0) Amount_journal,
  NVL(xal.entered_dr,0)-NVL(xal.entered_cr,0) Amount_Xla,
  NVL(ada.amount_dr,0)-NVL(ada.amount_cr,0) Amount_Receivables,
  DECODE(jh.ledger_id,2027,'USD',2029,'EUR',2023,'USD') Currency_Code,
   xdl.source_distribution_type,
  jh.ledger_id Book
FROM gl_je_headers jh,
  gl_je_lines jl,
  gl_code_combinations gcc,
  gl_import_references gir,
  xla_ae_lines xal,
  XLA_DISTRIBUTION_LINKS xdl,
  xla_ae_headers xah,
  AR_DISTRIBUTIONS_ALL ada
WHERE 1                    =1
AND jh.je_header_id        =jl.je_header_id
AND jl.code_combination_id =gcc.code_combination_id
AND gir.je_header_id       =jh.je_header_id
AND gir.je_line_num        =jl.je_line_num
AND gir.gl_sl_link_id      =xal.gl_sl_link_id
and xah.ae_header_id=xal.ae_header_id
and xal.application_id = xdl.application_id
and xah.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
and xdl.source_distribution_id_num_1 = ada.line_id
AND gcc.code_combination_id=46032
and jh.period_name like '13-Jun'
and jh.je_category in ('Misc Receipts','Receipts')
and abs(nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0))<> abs(NVL(ada.amount_dr,0)-NVL(ada.amount_cr,0))
and abs(NVL(xal.entered_dr,0)-NVL(xal.entered_cr,0)) <> abs(NVL(ada.amount_dr,0)-NVL(ada.amount_cr,0))
and jh.ledger_id = '2029';



SELECT  jh.name,jh.je_category Category,
  jh.period_name Period_Name,
  NULL Invoice_No_or_Memo_No,
  abs(nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0)) Amount_journal,
  abs (NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0)) Amount_Xla,
  abs(sum(aphd.amount)) amount_payment,
    DECODE(jh.ledger_id,2027,'USD',2029,'EUR',2023,'USD') Currency_Code,
   xdl.source_distribution_type,
  jh.ledger_id Book
FROM gl_je_headers jh,
  gl_je_lines jl,
  gl_code_combinations gcc,
  gl_import_references gir,
  xla_ae_lines xal,
  XLA_DISTRIBUTION_LINKS xdl,
  xla_ae_headers xah,
  AP_PAYMENT_HIST_DISTS aphd
WHERE 1                    =1
AND jh.je_header_id        =jl.je_header_id
AND jl.code_combination_id =gcc.code_combination_id
AND gir.je_header_id       =jh.je_header_id
AND gir.je_line_num        =jl.je_line_num
AND gir.gl_sl_link_id      =xal.gl_sl_link_id
and xah.ae_header_id=xal.ae_header_id
and xal.application_id = xdl.application_id
and xah.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
AND gcc.code_combination_id=46032
and xdl.source_distribution_type = 'AP_PMT_DIST'
and xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
and jh.je_category in ('Reconciled Payments')
and jh.ledger_id ='2029'
and jh.period_name like '13-Jun'
--and abs (nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0))  <> abs(sum(aphd.amount) )
--and NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0) <> abs(sum(aphd.amount)),
 group by  jh.name,jh.je_category,
  jh.period_name,
   nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0),
  NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0),
     DECODE(jh.ledger_id,2027,'USD',2029,'EUR',2023,'USD'),
   xdl.source_distribution_type,
  jh.ledger_id
  having --abs (nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0))  <> abs(sum(aphd.amount) );
abs( NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0)) <> abs(sum(aphd.amount));

How to Define Calender in Oracle GL

Step 1:-




Step 2:-



Step 3:-
Query the Field "Accounting"




Step 4:-

Step 5:-

Step 6:-
Concurrent run automatically


select * from GL_PERIOD_SETS where trunc(CREATION_DATE) = trunc (Sysdate)

select * from GL_PERIODS_V where trunc(CREATION_DATE) = trunc (Sysdate)