Query for Balance by Account / Vendor as of period : Peoplesoft Financials

            In peoplesoft financials we may have a requirement to extract the data in excel it is possible many times but when we trying to extract the data from statutory tables it may create some problem because the way this table is storing the data. Each month will  have begin balance, DR, CR and closing balance.

Navigation for balance by Account / Vendor :

Statutory Reports » Accounts Payable » Balance by Account/Vendor 

Name of the Crystal Report : APY1230-

Name of the PS Query used by this Crystal report : APY1230

If you use the delivered PS Query to extract the data in Excel for example for the period 5 it will give closing balance for each period from 1 to  5 which is not good.

Query :

SELECT A.BUSINESS_UNIT  ,
       A.ACCOUNT  ,
       A.VENDOR_SETID,
       A.VENDOR_ID  ,
       B.NAME1 NAME,
       NVL(K.RPTG_BEGIN_BALANCE, 0) AS OPENING_BALANCE,
       SUM(A.MONETARY_AMT_DR) DEBIT,
       SUM(A.MONETARY_AMT_CR) CREDIT,
       NVL(K.RPTG_BEGIN_BALANCE, 0) + SUM(A.MONETARY_AMT_DR) -
       SUM(A.MONETARY_AMT_CR) CLOSING_BALANCE
  FROM PS_VENDOR B, PS_RPTG_BALANCE A
  LEFT OUTER JOIN (
                   /*TO GET BEGINING BALANCE */
                   SELECT A.ACCOUNT,
                           A.BUSINESS_UNIT,
                           B.NAME1,
                           A.VENDOR_SETID,
                           A.VENDOR_ID,
                           A.RPTG_BEGIN_BALANCE
                     FROM PS_RPTG_BALANCE A, PS_VENDOR B
                    WHERE A.VENDOR_SETID = B.SETID
                      AND A.VENDOR_ID = B.VENDOR_ID
                      AND A.RPTG_APPL_ID = 'AP'
                      AND A.BUSINESS_UNIT = 'PXXXG'  /* BUSINESS_UNIT - YOU CAN ALSO USE LIST OF BUSINESS UNIT */
                      AND A.FISCAL_YEAR = '2014'
                      AND A.ACCOUNTING_PERIOD BETWEEN '1' AND '1' /* PERIOD FOR THE BEGINING BALANCE */
                      AND A.ACCOUNT BETWEEN '401100' AND '404010' /* PROVIDE THE LIST OF ACCOUNT*/
                   /*TO GET BEGINING BALANCE */
                   ) K ON K.VENDOR_SETID = A.VENDOR_SETID
                      AND K.ACCOUNT = A.ACCOUNT
                      AND K.VENDOR_ID = A.VENDOR_ID
                      AND A.BUSINESS_UNIT = K.BUSINESS_UNIT
WHERE A.VENDOR_SETID = B.SETID
   AND A.VENDOR_ID = B.VENDOR_ID
   AND A.RPTG_APPL_ID = 'AP'
   AND A.BUSINESS_UNIT = 'PXXXG' /* BUSINESS_UNIT - YOU CAN ALSO USE LIST OF BUSINESS UNIT */
   AND A.FISCAL_YEAR = '2014'
   AND A.ACCOUNTING_PERIOD BETWEEN '1' AND '5' /* PERIOD FOR THE REPORT */
   AND A.ACCOUNT BETWEEN '401100' AND '404010' /* PROVIDE THE LIST OF ACCOUNT*/
GROUP BY A.ACCOUNT,
          B.NAME1,
          A.VENDOR_SETID,
          A.VENDOR_ID,
          K.RPTG_BEGIN_BALANCE,
          A.BUSINESS_UNIT
ORDER BY 1, 2, 3