Peoplesoft General Ledger certifications - 1Z0-228

Back in 2012 when I did this certification  I was  afraid to have one But at last I have done certification in AP,AR too .it is relatively easy if you have Oracle student guide for Peoplesoft General Ledger .

In coming weeks I will share my knowledge to clear the exam

Voucher not posting due to Inter/Intraunit setup

       Sometimes the Voucher Posting (AP_PSTVCHR) application engine run status in Process Monitor will  'Success' although the Voucher hasn't been posted due to a missing setup.

These are the example of errors:

The InterUnit Processor Returned an Error for Business Unit 'PXXXM' and Voucher ID '01360752'.

Inter/IntraUnit Setup not found when trying to create balancing row(s) for P2XXG / P9XXG.

      The problem is users will not be aware that Vouchers haven't been posted due to missing Inter/Intra Unit setup.  As a result, the Vouchers which are still not posted might just sit there for a long time until someone discovers something went wrong.

     This application engine does not have the ability to set the process status to warning when Vouchers are not posted and the problem is rectified in the Oracle bug fix FMS Financials 9.0 Bundle #26

      

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