iProcurement Receiving Gets Unexpected Error and SQLException: ORA-01427: single-row subquery
(Doc ID 2135254.1)
Last updated on MARCH 31, 2023
Applies to:
Oracle iProcurement - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
User encounters the following error message is displayed when selecting Receiving in iProcurement.
ERROR
You have encountered an unexpected error. Please contact the System Administrator for assistance.
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select prhr.rcv_transaction_id, prhr.creation_date,
prhr.transaction_date, prhr.employee_id, prhr.receipt_num, prhr.item_id,
prhr.item_description, prhr.vendor, prhr.shipment_header_id,
rcvt.vendor_id, (select requisition_line_id from po_requisition_lines_all where line_location_id = rcvt.po_line_location_id) AS req_line_id,
decode(nvl(to_char(prhr.item_id), ICX_RVW_RATING_UPDATE_PKG.checkreviewaccess(rcvt.REQUISITION_LINE_ID,rcvt.po_line_location_id)), 'N', 'WriteDisabled', 'WriteEnabled') AS WRITE_SWITCHER
from por_rcv_home_receipts_v prhr INNER JOIN rcv_transactions rcvt on rcvt.transaction_id = prhr.rcv_transaction_id
where PRHR.EMPLOYEE_ID = :1
and prhr.creation_date >= sysdate-60) QRSLT ORDER BY creation_date desc, rcv_transaction_id desc
## Detail 0 ##
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)
at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:135)
at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860)
at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3754)
at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4593)
at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:751)
at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:900)
at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:814)
at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:808)
at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3674)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:441)
at oracle.apps.icx.por.rcv.server.ReceivingHomeAMImpl.queryReceiptsAtGlance(ReceivingHomeAMImpl.java:198)
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select prhr.rcv_transaction_id, prhr.creation_date,
prhr.transaction_date, prhr.employee_id, prhr.receipt_num, prhr.item_id,
prhr.item_description, prhr.vendor, prhr.shipment_header_id,
rcvt.vendor_id, (select requisition_line_id from po_requisition_lines_all where line_location_id = rcvt.po_line_location_id) AS req_line_id,
decode(nvl(to_char(prhr.item_id), ICX_RVW_RATING_UPDATE_PKG.checkreviewaccess(rcvt.REQUISITION_LINE_ID,rcvt.po_line_location_id)), 'N', 'WriteDisabled', 'WriteEnabled') AS WRITE_SWITCHER
from por_rcv_home_receipts_v prhr INNER JOIN rcv_transactions rcvt on rcvt.transaction_id = prhr.rcv_transaction_id
where PRHR.EMPLOYEE_ID = :1
and prhr.creation_date >= sysdate-60) QRSLT ORDER BY creation_date desc, rcv_transaction_id desc
## Detail 0 ##
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)
at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:135)
at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860)
at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3754)
at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4593)
at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:751)
at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:900)
at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:814)
at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:808)
at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3674)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:441)
at oracle.apps.icx.por.rcv.server.ReceivingHomeAMImpl.queryReceiptsAtGlance(ReceivingHomeAMImpl.java:198)
STEPS TO REPRODUCE
1. Navigate to iProcurement
2. Select the Receiving tab
- get the error
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |