EAR9.2: A Fatal PeopleCode SQL Error Occurred in View/Update Item Details When Viewing Low Value Item.

(Doc ID 1987131.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

PeopleSoft Enterprise FIN Receivables - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Accounts and Balances

When a very low value item is created in a foreign currency resulting in the base amount value being zero, and you try to view this item through Customer Account > Item Information > View/Update Item Details the following error occurs.

ERROR
-----------------------
This scenario was tested in PUM5 and PUM9 and there is a little difference between PUM5 and PUM9, see below:

In PUM5 the following errors are triggered:
A SQL error occurred. Please consult your system log for details.
Click OK

A fatal PeopleCode SQL error occurred. Please consult your system log for details.
Click OK

Fatal SQL error occurred. (2,125) IPAC_DTL_SBR.IPAC_SENDER_TAS.RowInit PCPC:1827 Statement:19
A SQL error has occurred, review the SQL to determine the cause of the error.
Review the program for errors. If necessary, run a PeopleCode trace to determine the program that failed.
Click OK

In PUM9 the following errors are triggered:
A SQL error occurred. Please consult your system log for details.
Click OK

A fatal PeopleCode SQL error occurred. Please consult your system log for details.
Click OK

Please note the following:
Fatal SQL error occurred. (2,125) IPAC_DTL_SBR.IPAC_SENDER_TAS.RowInit PCPC:1827 Statement:19 is missing in PUM9.
However, app server log file in PUM9 is showing:

SQL error. Stmt #: 760 Error Position: 247 Return: 1476 - ORA-01476: divisor is equal to zero
Failed SQL stmt: SELECT FILL.BUSINESS_UNIT,FILL.CUST_ID,FILL.ITEM,FILL.ITEM_LINE,FILL.ITEM_SEQ_NUM,FILL.DST_SEQ_NUM,FILL.ENTRY_EVENT,FILL.PROJECT_ID,FILL.INVOICE_ID,TO_CHAR(FILL.INVOICE_DT,'YYYY-MM-DD'),FILL.IPAC_TRACE_NUM,FILL.IPAC_CLIN,FILL.IPAC_CNTRCT_NUM,FILL.IPAC_PO_ID,FILL.IPAC_QUANTITY,FILL.UNIT_OF_ISSUE,FILL.IPAC_UNIT_PRICE,FILL.IPAC_LN_AMT,FILL.OPERATING_UNIT,FILL.PRODUCT,FILL.FUND_CODE,FILL.CLASS_FLD,FILL.PROGRAM_CODE,FILL.BUDGET_REF,FILL.AFFILIATE,FILL.AFFILIATE_INTRA1,FILL.AFFILIATE_INTRA2,FILL.CHARTFIELD1,FILL.CHARTFIELD2,FILL.CHARTFIELD3,FILL.IPAC_RECVR_DEPT,FILL.IPAC_OBL_DOC_NBR,FILL.BUSINESS_UNIT_GL FROM PS_AR_IPAC_DST_VW FILL WHERE BUSINESS_UNIT = :1 AND ITEM = :2 AND CUST_ID = :3 AND ITEM_LINE = :4


STEPS
-----------------------
The issue can be reproduced at will with the following steps:

  1. AR IPAC Interface is NOT enabled in Installation Options > Receivables.
  2. Federal Customer is NOT enabled.
  3. Create invoice in currency KES (Kenyan Shillings) where Business Unit = US001, Bill Type = MSC, Bill Source = MISC, Customer = USA01, Payment terms = IMMED, Payment Method = Check and Accounting and Invoice dates one month before, to force the invoice to be out of date.
  4. Change Bill currency to KES, rate type CRRNT.
  5. On bill lines enter amount 0.04.
  6. Define AR distribution code and Revenue distribution code.
  7. Change bill status to RDY and save.
  8. Run Finalize and Print Invoices.
  9. Run Load Invoices to AR.
  10. Run ARUPDATE.
  11. Navigate to Accounts Receivable > Customer Accounts > Item Information > View/Update Item Details, open item and error occurred.


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot review these items.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms