In Oracle iProcurement, After Upgrade to Release 12.2.x, Feedback Tab Gives Errors JBO-27122 and ORA-01427
(Doc ID 2742512.1)
Last updated on SEPTEMBER 17, 2021
Applies to:
Oracle iProcurement - Version 12.2.5 and laterInformation in this document applies to any platform.
Symptoms
In Oracle iProcurement, Release 12.2.5 and later, with Enterprise Command Center V4 (ECCv4) or later also implemented, an error occurs when users try to access the Feedback page.
Due to this issue, users cannot access the Feedback features integrated into Oracle iProcurement by the ECC.
Error
Error Page
You have encountered an unexpected error. Please contact your System Administrator for assistance.
Click here for exception details.
oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT
PPF.FULL_NAME REVIEWER,
(select location_code from hr_locations where location_id = papf.location_id and rownum=1) LOCATION_CODE,
(SELECT COUNT(1) FROM ICX_REVIEW_USEFULL_DTLS WHERE REVIEW_ID = IRR.REVIEW_ID AND USEFULL_FLAG='Y'
AND reference_type='REVIEW') NO_OF_USEFULL_COUNT,
IRR.REVIEW_TITLE,
IRR.REVIEW_COMMENTS,
(select count(1) from ICX_REVIEW_USEFULL_DTLS
where review_id = irr.review_id
and voter_id = fnd_global.employee_id
and reference_type='REVIEW') useful_comment,
(select meaning from fnd_lookup_values_vl
where lookup_type = 'ICX_REVIEW_ENTITY_TYPE'
and lookup_code = irr.review_entity
and rownum=1) reviewtype_desc,
IRR.REVIEW_ENTITY,
(select meaning from
fnd_lookup_values_vl
where lookup_type = 'ICX_PURCHASED_FROM_LOOKUP'
and lookup_code = irr.purchased_from)PURCHASED_FROM,
irr.status,
irr.rating,
fu.user_id,
fnd_global.user_id guser_id,
fnd_global.employee_id femp_id,
fu.employee_id,
nvl(irr.last_update_date,irr.creation_date) review_date,
irr.reviewer_id,
decode(fu.employee_id,reviewer_id,1,2) MY_REVIEW,
irr.review_id,
(select meaning from
fnd_lookup_values_vl
where lookup_type='ICX_REVIEW_ENTITY_TYPE'
and lookup_code = irr.review_entity) entity_meaning,
(select subscription_flag from
ICX_RVW_SUBSCRIPTIONS
where subscriber_id = fnd_global.employee_id
and review_id = irr.review_id) subscribe_flag
,DECODE(REVIEW_ENTITY,'I',1,'S',1,'SG',2,'Q',3) REVIEW,
(select count(1) from icx_rvw_responses where review_id = irr.review_id) no_of_responses ,
irr.vendor_id,
irr.item_id,
irr.supplier_part_num,
irr.po_line_id,
irr.req_template_name,
irr.req_template_line_num,
irr.punchout_item_id,
irr.smartform_id,
(select count(1) from po_buyer_actions
where entity_id = irr.review_id
and status in ('OPEN','INPROGRESS') )action_items_count
FROM ICX_RVW_REVIEWS IRR,
PER_ALL_PEOPLE_F PPF,
PER_ALL_ASSIGNMENTS_F PAPF ,
fnd_user fu
WHERE IRR.REVIEWER_ID = PPF.PERSON_ID
AND PAPF.PERSON_ID = PPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND NVL(TRUNC(PAPF.EFFECTIVE_END_DATE),TRUNC(SYSDATE))
and irr.org_id = fnd_global.org_id
and fu.user_id = fnd_global.user_id) QRSLT WHERE (reviewer_id = :1 and REVIEW_ENTITY = 'SG');
at oracle.jbo.server.BaseSQLBuilderImpl.processException(BaseSQLBuilderImpl.java:3383)
at oracle.jbo.server.OracleSQLBuilderImpl.processException(OracleSQLBuilderImpl.java:4022)
at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:961)
at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
..
..
## 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:5099)
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:470)
at oracle.apps.icx.icatalog.shopping.server.IcxRvwReviewsAMImpl.intQueryMyPage(IcxRvwReviewsAMImpl.java:801)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:783)
at oracle.apps.icx.icatalog.shopping.webui.SuggestionCO.processRequest(SuggestionCO.java:87)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:658)
..
..
Steps To Reproduce
The issue can be reproduced at will with the following steps:
1. Log in to Oracle Applications.
2. On the Oracle Applications Home Page, select an iProcurement responsibility > iProcurement Home Page.
3. On the iProcurement Home Page, click the Feedback tab at the top of the page. The issue occurs at this point.
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 |