Error: ORA-01427 ORA-01403 When Manually Reconcile a Receipt (Doc ID 1372241.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

When attempting to manually reconcile a receipt, receive the following error:

AVAILABLE_EVENT.update_row procedure raised unhandled exception
ORA-01427: single-row subquery returns more than one row
ORA-6512: at "APPS.ARP_CASHBOOK", line 1015
ORA-6512: at "APPS.CE_AUTO_BANK_CLEAR1", line 2161
ORA-6512: at "APPS.CE_AUTO_BANK_CLEAR", line 917
FRM-40735: ON-UPDATE trigger raised unhandled exception ORA-01427

Steps to Reproduce:
Responsibility: Cash Management User
Navigation: Bank Statements > Bank Statements and Reconciliation

• Query your bank statement
• Open the statement and try to reconcile a receipt to one of your Receipt Type lines.
• The above error is raised.
• This only happens for one receipt.

FND DEBUG LOG shows:
ar.plsql.arp_cash_receipts_pkg.fetch_p EXCEPTION: ARP_CASHBOOK.clear
fnd.forms.CEXCABMR.ON_ERROR "FND

or
fnd.forms.CEXCABMR.ON_ERROR FND FND_FORMS_ERROR_LOGGING N ERRNO -1427 N REASON N ORA-01403: no data found

Trace with binds shows:

PARSING IN CURSOR #147 len=1108 dep=1 uid=173 oct=6 lid=173 tim=1287983834947796 hv=4247451222 ad='9d878388'
UPDATE AR_TRX_BAL_SUMMARY MAIN_SUM
SET RECEIPTS_AT_RISK_VALUE =
(SELECT SUM(DECODE(RAP.APPLIED_PAYMENT_SCHEDULE_ID, -2, 0, CRH.AMOUNT))
FROM AR_CASH_RECEIPTS_ALL CR,
AR_CASH_RECEIPT_HISTORY_ALL CRH,
AR_RECEIVABLE_APPLICATIONS_ALL RAP
WHERE NVL(CR.CONFIRMED_FLAG, 'Y') = 'Y'
AND CR.REVERSAL_DATE IS NULL
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS NOT IN ('REVERSED', DECODE(CRH.FACTOR_FLAG, 'Y', 'RISK_ELIMINATED', 'N', 'CLEARED'))
AND CR.CASH_RECEIPT_ID = RAP.CASH_RECEIPT_ID (+)
AND RAP.APPLIED_PAYMENT_SCHEDULE_ID (+) = -2
AND CR.PAY_FROM_CUSTOMER = MAIN_SUM.CUST_ACCOUNT_ID
AND CR.CUSTOMER_SITE_USE_ID = DECODE(MAIN_SUM.SITE_USE_ID,-99, CR.CUSTOMER_SITE_USE_ID, MAIN_SUM.SITE_USE_ID)
AND CR.ORG_ID = MAIN_SUM.ORG_ID
AND CR.CURRENCY_CODE = MAIN_SUM.CURRENCY
GROUP BY CR.PAY_FROM_CUSTOMER,
CR.CUSTOMER_SITE_USE_ID,
CR.CURRENCY_CODE, CR.ORG_ID),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN= FND_GLOBAL.LOGIN_ID
WHERE CUST_ACCOUNT_ID = :B4
AND SITE_USE_ID = :B3
AND CURRENCY = :B2
AND NVL(ORG_ID,'-99') = NVL(:B1 ,-99)
END OF STMT

BINDS #147:
kkscoacd
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=204001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b7e92fe8 bln=22 avl=03 flg=09
value=1786
Bind#1
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=204001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b7fd458c bln=22 avl=03 flg=09
value=-99
Bind#2
oacdty=01 mxl=32(15) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=204001 frm=01 csi=31 siz=32 off=0
kxsbbbfp=b7fd43ec bln=32 avl=03 flg=09
value="MXN"
Bind#3
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=204001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b7fd41ac bln=22 avl=02 flg=09
value=85

EXEC #147:c=112983,e=188097,p=1176,cr=9610,cu=1,mis=1,r=0,dep=1,og=1,tim=1287983835135950
ERROR #147:err=1427 tim=500908915

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.