Wednesday, 16 November 2016

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

No comments:

Post a Comment