Thursday 13 June 2013

Trial Balance Report Output mismatched egin_balance and end_balance amounts in oracle apps

/* Formatted on 11/16/2016 1:21:22 PM (QP5 v5.114.809.3010) */
  SELECT   GCC.SEGMENT5 "ACCOUNT",
           FND.DESCRIPTION "DESCRIPTION",
           GJH.JE_SOURCE "SOURCE",
           (SELECT   SUM (NVL (GJL2.ACCOUNTED_DR, 0))
                     - SUM (NVL (GJL2.ACCOUNTED_CR, 0))
              FROM   GL_JE_LINES GJL2, GL_JE_HEADERS GJH2
             WHERE       1 = 1
                     AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                     AND GJL2.LEDGER_ID = 2021
                     AND TRUNC (GJL2.EFFECTIVE_DATE) <
                           (SELECT   TRUNC (START_DATE)
                              FROM   GL_PERIODS
                             WHERE   PERIOD_NAME = 'Apr-16')
                     AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
                     AND GJH2.ACTUAL_FLAG = 'A'
                     AND GJH2.JE_SOURCE != 'Consolidation'
                     AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
                     AND GJH2.JE_SOURCE = GJH.JE_SOURCE
                     AND GJL2.STATUS = 'P')
              "BEGIN_BALANCE",
           (  SELECT   SUM (NVL (GJL1.ACCOUNTED_DR, 0))
                FROM   GL_JE_LINES GJL1, GL_JE_HEADERS GJH1
               WHERE       1 = 1
                       AND GJL1.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                       AND GJL1.LEDGER_ID = 2021
                       AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
                       AND GJH1.ACTUAL_FLAG = 'A'
                       AND GJH1.JE_SOURCE != 'Consolidation'
                       AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
                       AND TRUNC (GJL1.EFFECTIVE_DATE) BETWEEN (SELECT   TRUNC(START_DATE)
                                                                  FROM   GL_PERIODS
                                                                 WHERE   PERIOD_SET_NAME =
                                                                            'CORPORATE'
                                                                         AND PERIOD_NAME =
                                                                               'Apr-16')
                                                           AND  (SELECT   TRUNC(END_DATE)
                                                                   FROM   GL_PERIODS
                                                                  WHERE   PERIOD_SET_NAME =
                                                                             'CORPORATE'
                                                                          AND PERIOD_NAME =
                                                                                'Apr-16')
                       AND GJH1.JE_SOURCE = GJH.JE_SOURCE
                       AND GJL1.STATUS = 'P'
            GROUP BY   GCC.SEGMENT5, FND.DESCRIPTION, GJH.JE_SOURCE)
              "DEBIT",
           (  SELECT   SUM (NVL (GJL1.ACCOUNTED_CR, 0))
                FROM   GL_JE_LINES GJL1, GL_JE_HEADERS GJH1
               WHERE       1 = 1
                       AND GJL1.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                       AND GJL1.LEDGER_ID = 2021
                       AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
                       AND GJH1.ACTUAL_FLAG = 'A'
                       AND GJH1.JE_SOURCE != 'Consolidation'
                       AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
                       AND TRUNC (GJL1.EFFECTIVE_DATE) BETWEEN (SELECT   TRUNC(START_DATE)
                                                                  FROM   GL_PERIODS
                                                                 WHERE   PERIOD_SET_NAME =
                                                                            'CORPORATE'
                                                                         AND PERIOD_NAME =
                                                                               'Apr-16')
                                                           AND  (SELECT   TRUNC(END_DATE)
                                                                   FROM   GL_PERIODS
                                                                  WHERE   PERIOD_SET_NAME =
                                                                             'CORPORATE'
                                                                          AND PERIOD_NAME =
                                                                                'Apr-16')
                       AND GJH1.JE_SOURCE = GJH.JE_SOURCE
                       AND GJL1.STATUS = 'P'
            GROUP BY   GCC.SEGMENT5, FND.DESCRIPTION, GJH.JE_SOURCE)
              "CREDIT",
           (SELECT   SUM (NVL (GJL2.ACCOUNTED_DR, 0))
                     - SUM (NVL (GJL2.ACCOUNTED_CR, 0))
              FROM   GL_JE_LINES GJL2, GL_JE_HEADERS GJH2
             WHERE       1 = 1
                     AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                     AND GJL2.LEDGER_ID = 2021
                     AND UPPER (GJL2.EFFECTIVE_DATE) <=
                           (SELECT   TRUNC (END_DATE)
                              FROM   GL_PERIODS
                             WHERE   PERIOD_NAME = 'Apr-16')
                     AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
                     AND GJH2.ACTUAL_FLAG = 'A'
                     AND GJH2.JE_SOURCE != 'Consolidation'
                     AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
                     AND GJH2.JE_SOURCE = GJH.JE_SOURCE
                     AND GJL2.STATUS = 'P')
              "END_BALANCE"
    FROM   GL_CODE_COMBINATIONS GCC,
           FND_FLEX_VALUES_VL FND,
           GL_JE_LINES GJL,
           GL_JE_HEADERS GJH
   WHERE       1 = 1
           AND GCC.CHART_OF_ACCOUNTS_ID = 50420
--           AND GCC.SEGMENT1 BETWEEN P_SEGMENT1_LOW AND P_SEGMENT1_HIGH
           AND GCC.SUMMARY_FLAG = 'N'
           AND GCC.TEMPLATE_ID IS NULL
           AND FND.FLEX_VALUE = GCC.SEGMENT5
           AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
           AND GJL.LEDGER_ID = 2021
           AND GJL.STATUS = 'P'
           AND GJH.CURRENCY_CODE = 'USD'
           AND GJH.LEDGER_ID = GJL.LEDGER_ID
           AND GJH.ACTUAL_FLAG = 'A'
           AND GJH.JE_SOURCE != 'Consolidation'
           AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
           --AND NVL2(P_SOURCE,GJH.JE_SOURCE,1) = NVL2(P_SOURCE,P_SOURCE,1)
           --ADDED ADDITIONAL LOGIC FOR RESTRICTING ADDITIONAL ACCOUNTS WHICH ARE NOT OCCURING IN TRIAL BALANCE REPORT
           AND (   (SELECT   SUM (BEGIN_BALANCE_DR) - SUM (BEGIN_BALANCE_CR)
                      FROM   GL_BALANCES
                     WHERE       CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                             AND LEDGER_ID = GJL.LEDGER_ID
                             AND CURRENCY_CODE = 'USD'
                             AND PERIOD_NAME IN ('Apr-16')) != 0
                OR (SELECT   SUM (PERIOD_NET_DR)
                      FROM   GL_BALANCES
                     WHERE       CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                             AND LEDGER_ID = GJL.LEDGER_ID
                             AND CURRENCY_CODE = 'USD'
                             AND PERIOD_NAME IN ('Apr-16')) != 0
                OR (SELECT   SUM (PERIOD_NET_CR)
                      FROM   GL_BALANCES
                     WHERE       CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                             AND LEDGER_ID = GJL.LEDGER_ID
                             AND CURRENCY_CODE = 'USD'
                             AND PERIOD_NAME IN ('Apr-16')) != 0)
---------------------END OF ADDITIONAL LOGIC
GROUP BY   GCC.SEGMENT5,
           FND.DESCRIPTION,
           GJH.JE_SOURCE,
           GCC.CODE_COMBINATION_ID
ORDER BY   1;

1 comment:

  1. Hi all, Oracle technical and fusion cloud SCM online classes, if you are interested please whatsapp to this number +91 7382582893, thank you.

    ReplyDelete