Reconcile Bank Statements Error: Manual Reconciliation of a Receipt Raises: ORA-01427: single-row subquery returns more than one row

(Doc ID 1372241.1)

Last updated on JUNE 08, 2016

Applies to:

Oracle Receivables - Version 12.0.6 and later
Information in this document applies to any platform.
Form:CEXCABMR.FMB - Enter/Reconcile Bank Statements


Symptoms

Manual reconciliation of a receipt raises error message:
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


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

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