Look Ahead LOV "Deliver-To Location" In iProcurement Giving Unable to Fetch Results or Unexpected Error and SQLStmtException: JBO-27122
(Doc ID 1949938.1)
Last updated on JULY 06, 2022
Applies to:
Oracle iProcurement - Version 12.1.3 to 12.2.4 [Release 12.1 to 12.2]Information in this document applies to any platform.
Symptoms
In Oracle iProcurement, Release 12.1.3 and Release 12.2, two different errors occur when using the look-ahead feature in the Deliver-To Location field on the Checkout: Requisition Information page.
a) When typing the first three letters of the desired deliver-to location and then waiting for a few seconds, the following message appears:
Unable to fetch results. Please invoke the LOV window for search results.
b) Then, an error appears when clicking the LOV icon (magnifying glass) to open the list of values window:
Error Page
You have encountered an unexpected error. Please contact the system administrator for assistance.
The exception details are:
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT hrlt.location_code || ' (' || ood.organization_code || ')' as location_display,
PO_POAPPROVAL_INIT1.Get_Formatted_Address(hrl.location_id) as address,
hrlt.description as description,
hrlt.location_code as location_code,
hrl.location_id as location_id,
ood.organization_id as deliver_to_org_id,
ood.organization_code as organization_code,
papf.full_name as contact
FROM hr_locations_all hrl,
hr_locations_all_tl hrlt,
org_organization_definitions ood,
per_all_people_f papf
WHERE hrl.location_id = hrlt.location_id
and hrlt.language = :1
and hrl.inventory_organization_id is null
and ood.organization_id = :2
and ood.set_of_books_id = :3
and sysdate < nvl(hrl.inactive_date, sysdate + 1)
and trunc(sysdate) < nvl(ood.disable_date, trunc(sysdate + 1))
and (hrl.business_group_id is null or
hrl.business_group_id = :4)
and hrl.location_id <> nvl(:5, -1)
and hrl.designated_receiver_id = papf.person_id(+)
and trunc(sysdate) between
nvl(papf.effective_start_date, trunc(sysdate-1)) and nvl(papf.effective_end_date,trunc(sysdate+1))
UNION ALL
SELECT hrlt.location_code || ' (' || ood.organization_code || ')' as location_display,
PO_POAPPROVAL_INIT1.Get_Formatted_Address(hrl.location_id) as address,
hrlt.description as description,
hrlt.location_code as location_code,
hrl.location_id as location_id,
ood.organization_id as deliver_to_org_id,
ood.organization_code as organization_code,
papf.full_name as contact
FROM hr_locations_all hrl,
hr_locations_all_tl hrlt,
org_organization_definitions ood,
per_all_people_f papf
WHERE hrl.location_id = hrlt.location_id
and hrlt.language = :6
and ood.organization_id = hrl.inventory_organization_id
and ood.set_of_books_id = :7
and sysdate < nvl(hrl.inactive_date, sysdate + 1)
and trunc(sysdate) < nvl(ood.disable_date, trunc(sysdate + 1))
and (hrl.business_group_id is null or
hrl.business_group_id = :8)
and hrl.location_id <> nvl(:9, -1)
and hrl.designated_receiver_id = papf.person_id(+)
and trunc(sysdate) between
nvl(papf.effective_start_date, trunc(sysdate-1)) and nvl(papf.effective_end_date,trunc(sysdate+1))) QRSLT WHERE (( UPPER(LOCATION_CODE) like UPPER(:9) AND (LOCATION_CODE like :10 OR LOCATION_CODE like :11 OR LOCATION_CODE like :12 OR LOCATION_CODE like :13))) ORDER BY LOCATION_CODE ASC, ORGANIZATION_CODE ASC
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1169)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1435)
at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2685)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1950)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:550)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:438)
at _OA._jspService(_OA.java:212)
..
..
## Detail 0 ##
java.sql.SQLException: Missing IN or OUT parameter at index:: 14
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1752)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3444)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
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:4566)
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:439)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(OAViewObjectImpl.java:743)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(OAWebBeanHelper.java:2342)
at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processRequestAfterController(OAListOfValuesHelper.java:834)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:662)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processRequest(OAListOfValuesHelper.java:446)
at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processRequest(OAListOfValuesBean.java:413)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:976)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:943)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:663)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)
at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2630)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1950)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:550)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:438)
at _OA._jspService(_OA.java:212)
..
..
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT hrlt.location_code || ' (' || ood.organization_code || ')' as location_display,
PO_POAPPROVAL_INIT1.Get_Formatted_Address(hrl.location_id) as address,
hrlt.description as description,
hrlt.location_code as location_code,
hrl.location_id as location_id,
ood.organization_id as deliver_to_org_id,
ood.organization_code as organization_code,
papf.full_name as contact
FROM hr_locations_all hrl,
hr_locations_all_tl hrlt,
org_organization_definitions ood,
per_all_people_f papf
WHERE hrl.location_id = hrlt.location_id
and hrlt.language = :1
and hrl.inventory_organization_id is null
and ood.organization_id = :2
and ood.set_of_books_id = :3
and sysdate < nvl(hrl.inactive_date, sysdate + 1)
and trunc(sysdate) < nvl(ood.disable_date, trunc(sysdate + 1))
and (hrl.business_group_id is null or
hrl.business_group_id = :4)
and hrl.location_id <> nvl(:5, -1)
and hrl.designated_receiver_id = papf.person_id(+)
and trunc(sysdate) between
nvl(papf.effective_start_date, trunc(sysdate-1)) and nvl(papf.effective_end_date,trunc(sysdate+1))
UNION ALL
SELECT hrlt.location_code || ' (' || ood.organization_code || ')' as location_display,
PO_POAPPROVAL_INIT1.Get_Formatted_Address(hrl.location_id) as address,
hrlt.description as description,
hrlt.location_code as location_code,
hrl.location_id as location_id,
ood.organization_id as deliver_to_org_id,
ood.organization_code as organization_code,
papf.full_name as contact
FROM hr_locations_all hrl,
hr_locations_all_tl hrlt,
org_organization_definitions ood,
per_all_people_f papf
WHERE hrl.location_id = hrlt.location_id
and hrlt.language = :6
and ood.organization_id = hrl.inventory_organization_id
and ood.set_of_books_id = :7
and sysdate < nvl(hrl.inactive_date, sysdate + 1)
and trunc(sysdate) < nvl(ood.disable_date, trunc(sysdate + 1))
and (hrl.business_group_id is null or
hrl.business_group_id = :8)
and hrl.location_id <> nvl(:9, -1)
and hrl.designated_receiver_id = papf.person_id(+)
and trunc(sysdate) between
nvl(papf.effective_start_date, trunc(sysdate-1)) and nvl(papf.effective_end_date,trunc(sysdate+1))) QRSLT WHERE (( UPPER(LOCATION_CODE) like UPPER(:9) AND (LOCATION_CODE like :10 OR LOCATION_CODE like :11 OR LOCATION_CODE like :12 OR LOCATION_CODE like :13))) ORDER BY LOCATION_CODE ASC, ORGANIZATION_CODE ASC
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1169)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1435)
at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2685)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1950)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:550)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:438)
at _OA._jspService(_OA.java:212)
..
..
## Detail 0 ##
java.sql.SQLException: Missing IN or OUT parameter at index:: 14
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1752)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3444)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
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:4566)
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:439)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(OAViewObjectImpl.java:743)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(OAWebBeanHelper.java:2342)
at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processRequestAfterController(OAListOfValuesHelper.java:834)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:662)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processRequest(OAListOfValuesHelper.java:446)
at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processRequest(OAListOfValuesBean.java:413)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:976)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:943)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:663)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(OABodyBean.java:353)
at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2630)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1950)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:550)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:438)
at _OA._jspService(_OA.java:212)
..
..
The issue began to occur after applying a series of patches for a variety of EBS products, including Purchasing and iProcurement.
17863140:R12.ICX.B Latest Recommended Patch Collection for Oracle Purchasing
18200706:R12.PO.B Corrective patch for Req to PO autosourcing.
16923041:R12.FND.B Corrective patch for Receiving Transaciton Manager
17378128:R12.PO.B Invoice price variance report error
15987200:R12.PO.B In Oracle Purchasing, Active encumbrance exists on a Finally closed PO.
16033513:R12.ICX.B Requisitions can be submitted without AME
18221238:R12.PO.B Patch Project - BPA Upload of file not working
18183575:R12.ICX.B Requisition can be self approved if no approvers found in ame apprv groups
18554841:R12.PO.B Source# on Req Ref Agreement to Exclude Defect
17863140:R12.ICX.B Latest Recommended Patch Collection for Oracle Purchasing
18200706:R12.PO.B Corrective patch for Req to PO autosourcing.
16923041:R12.FND.B Corrective patch for Receiving Transaciton Manager
17378128:R12.PO.B Invoice price variance report error
15987200:R12.PO.B In Oracle Purchasing, Active encumbrance exists on a Finally closed PO.
16033513:R12.ICX.B Requisitions can be submitted without AME
18221238:R12.PO.B Patch Project - BPA Upload of file not working
18183575:R12.ICX.B Requisition can be self approved if no approvers found in ame apprv groups
18554841:R12.PO.B Source# on Req Ref Agreement to Exclude Defect
Steps To Reproduce
The issue can be reproduced at will with the following steps:
1. Log in to Oracle Applications, using an iProcurement responsibility.
2. Add an item to cart (if not already done).
3. View Cart and Checkout.
4. Proceed to Checkout.
5. On the Checkout: Requisition Information page (Step 1 of 3), a default value appears in the Deliver-To Location field.
a. Click in the field and use the Backspace key to remove the value
b. Enter the first three values of the desired location and wait 3+ seconds --> the message appears: "Unable to fetch results. Please invoke the LOV window for search results."
c. Then, click the magnifying glass icon to open the list of values (LOV) window. The new LOV window opens but it displays the unexpected error message (mentioned above).
As a result users cannot select new values for the Deliver-To Location field during the requisition checkout process.
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 |
| Cause |
| Solution |
| References |