Error When Opening Requisition Approval Notification From the Worklist - JBO-27122 - SQLException: Attempt to set a parameter name that does not occur in the SQL (Doc ID 2162716.1)

Last updated on OCTOBER 14, 2016

Applies to:

Oracle iProcurement - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

In Oracle iProcurement, Release 12.1.3, an error occurs when attempting to open the requisition approval notification from the worklist.
Due to this issue, approvers are unable to take approval action on requisitions.


Error

Error Page
You have encountered an unexpected error. Please contact the System Administrator for assistance.
Click here for exception details.

 

Exception Details.
 oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT prl.requisition_header_id,
  prl.requisition_line_id,
  prl.line_num AS LINE,
  msi.concatenated_segments AS ITEM_NUMBER,
  prl.item_revision,
  prl.item_description AS DESCRIPTION,
  por_apprv_wf_util_grp.get_cost_center(prl.requisition_line_id) AS COST_CENTER,
  nvl(muom.unit_of_measure_tl, prl.unit_meas_lookup_code) AS UOM,
  prl.quantity AS QUANTITY,
  prl.unit_price AS UNIT_PRICE,
  decode(nvl(order_type_lookup_code, 'QUANTITY'), 'FIXED PRICE', prl.amount, 'RATE', prl.amount, prl.quantity * prl.unit_price) AS LINE_AMOUNT,
  lkup.meaning as contractor_status,
  PO_POAPPROVAL_INIT1.Get_Formatted_Full_Name(prl.candidate_first_name, prl.candidate_last_name) AS CONTRACTOR_NAME,
  prl.assignment_start_date AS START_DATE,
  prl.assignment_end_date AS END_DATE,
  prl.need_by_date AS NEED_BY,
  hrt.location_code AS LOCATION,
  per.full_name AS REQUESTOR,
  decode(nvl(prl.contractor_status, 'NULL'), 'PENDING', fnd_message.get_string('ICX', 'ICX_POR_SEE_DETAILS'), decode(prl.source_type_code,'VENDOR', prl.suggested_vendor_name, org.organization_code ||' - '|| org.organization_name)) AS SUGG_SUPPLIER,
  decode(nvl(prl.contractor_status, 'NULL'), 'PENDING', fnd_message.get_string('ICX', 'ICX_POR_SEE_DETAILS'), decode(prl.source_type_code, 'VENDOR',prl.suggested_vendor_location,'')) AS SUGG_SITE,
  nvl(prl.currency_code, :1) AS TXN_CURRENCY_CODE,
  nvl(prl.currency_unit_price, prl.unit_price) AS CURRENCY_UNIT_PRICE,
  decode(nvl(order_type_lookup_code, 'QUANTITY'), 'FIXED PRICE', nvl(prl.currency_amount, prl.amount), 'RATE', nvl(prl.currency_amount, prl.amount), prl.quantity * nvl(prl.currency_unit_price, prl.unit_price)) AS CURRENCY_LINE_AMOUNT,
  decode(nvl(prl.order_type_lookup_code, 'QUANTITY'), 'FIXED PRICE', 0, decode(prl.currency_unit_price, null, 0, 1)) AS CURR_UNIT_PRICE_DISPLAY_FLAG,
decode(nvl(prl.order_type_lookup_code, 'QUANTITY'), 'FIXED PRICE', decode(prl.currency_amount, null, 0, 1),
decode(prl.currency_unit_price, null, 0, 1)) AS CURR_AMOUNT_DISPLAY_FLAG,
xxatc_get_req_project_no(prl.requisition_line_id) AS Project
  FROM po_requisition_lines_all prl,
  mtl_system_items_kfv msi,
  hr_locations_all hrt,
  per_all_people_f per,
  mtl_units_of_measure muom,
  org_organization_definitions org,
  fnd_lookup_values lkup
 WHERE prl.requisition_header_id = :2
  AND NVL(prl.cancel_flag,'N') = 'N'
  AND NVL(prl.modified_by_agent_flag, 'N') = 'N'
  AND NVL(closed_code, 'OPEN') != 'FINALLY CLOSED'
  AND hrt.location_id (+) = prl.deliver_to_location_id
  AND prl.item_id = msi.inventory_item_id(+)
  AND nvl(msi.organization_id, prl.destination_organization_id) =
  prl.destination_organization_id
  AND prl.to_person_id = per.person_id(+)
  AND per.effective_start_date(+) <= trunc(sysdate)
  AND per.effective_end_date(+) >= trunc(sysdate)
  AND prl.source_organization_id = org.organization_id (+)
  AND muom.unit_of_measure(+) = prl.unit_meas_lookup_code
  AND lkup.lookup_type(+) ='ICX_POR_CONTRACTOR_STATUS'
  AND lkup.lookup_code(+) = prl.contractor_status
  AND lkup.language(+) = :3
 ORDER BY prl.line_num
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:153)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:764)
at oracle.apps.icx.por.wf.webui.ReqLinesNotificationsCO.processRequest(ReqLinesNotificationsCO.java:97)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:604)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(OAStackLayoutBean.java:350)
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.layout.OAStackLayoutBean.processRequest(OAStackLayoutBean.java:350)
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)
..
..
## Detail 0 ##
java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: 4
at oracle.jdbc.driver.OraclePreparedStatement.setObjectAtName(OraclePreparedStatement.java:11915)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObjectAtName(OraclePreparedStatementWrapper.java:815)
at oracle.jbo.server.OracleSQLBuilderImpl.bindParamValue(OracleSQLBuilderImpl.java:3966)
at oracle.jbo.server.BaseSQLBuilderImpl.bindParametersForStmt(BaseSQLBuilderImpl.java:3335)
at oracle.jbo.server.ViewObjectImpl.bindParametersForCollection(ViewObjectImpl.java:14008)
at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:804)
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:4560)
at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:743)
at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:892)
at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:806)
at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:800)
at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3674)
at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:439)
at oracle.apps.icx.por.wf.server.ReqLinesNotificationsVOImpl.initQuery(ReqLinesNotificationsVOImpl.java:111)
at oracle.apps.icx.por.wf.server.ReqApprovalNotificationsAMImpl.initReqLinesNotfnVOQry(ReqApprovalNotificationsAMImpl.java:76)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:190)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:153)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:764)
at oracle.apps.icx.por.wf.webui.ReqLinesNotificationsCO.processRequest(ReqLinesNotificationsCO.java:97)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:604)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(OAStackLayoutBean.java:350)
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)
..
..

 


Steps To Reproduce

The issue can be reproduced at will with the following steps:
1. The requester user creates a requisition in iProcurement and submits it for approval.
2. The approver user logs in to Oracle Applications.
3. On the Oracle Applications Home Page, the Worklist shows the approval notification. Click the link to view the notification and the error occurs at this point.



Changes

 

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