Unable To Approve Requisition In iPprocurement Due To Error Oracle.jbo.SQLStmtException: JBO-27122 (Doc ID 1634281.1)

Last updated on JUNE 01, 2016

Applies to:

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

Symptoms

On Applications 12.1.3 and 12.2.3
When attempting to approve the requisition , the following error occurs.

-- Error

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException:
JBO-27122:
SQL error during statement preparation.  Statement: SELECT
prh.requisition_header_id,
       prh.segment1 requisition_number,
       prh.preparer_id,
       hre.full_name preparer_name,
       prh.description,
       prh.authorization_status auth_status,
       prh.cancel_flag,
       NVL(prh.closed_code,
           'OPEN'
          ) closed_code,
       ibycc.masked_cc_number card_number,
       prh.creation_date,
       prh.transferred_to_oe_flag placed_on_so_flag,
       prh.emergency_po_num,
       NVL(prh.change_pending_flag,
           'N'
          ) change_pending_flag,
       prh.contractor_status,
       prh.contractor_requisition_flag,
       prh.supplier_notified_flag,
       NVL(prh.active_shopping_cart_flag, 'N') active_shopping_cart_flag,
       'POR_VIEW_TO_APPR_REQS' view_mode,
       prh.wf_item_type,
       prh.wf_item_key,
       org.name organization,
       prh.org_id ,
       GSB.currency_code
FROM   po_requisition_headers_all prh,
       ap_cards_all apc,
       iby_creditcard ibycc,
       per_all_people_f hre,
       wf_notification_attributes wna,
       wf_notifications wn,
       wf_user_roles wlur,
       hr_all_organization_units_tl org  ,
       FINANCIALS_SYSTEM_PARAMS_ALL FSP,
       GL_SETS_OF_BOOKS GSB
WHERE  prh.authorization_status<>'SYSTEM_SAVED' AND
       wlur.user_name = :1 AND
       prh.org_id = org.organization_id AND
       org.language = USERENV('LANG') AND
       prh.requisition_header_id = wna.number_value AND
       wna.name = 'DOCUMENT_ID' AND
       wna.notification_id = wn.notification_id AND
       wn.recipient_role = wlur.role_name AND
       wn.status = 'OPEN' AND
       wn.message_type IN (SELECT DISTINCT wf_approval_itemtype
                           FROM   po_document_types_all_b pdt
                           WHERE  pdt.org_id = prh.org_id
                           AND    pdt.document_type_code = 'REQUISITION'
                          ) AND
       wn.message_name IN ('PO_REQ_APPROVE',
                           'PO_REQ_REMINDER1',
                           'PO_REQ_APPROVE_WEB_MSG',
                           'PO_REQ_REMINDER2',
                           'PO_REQ_REMINDER1_WEB',
                           'PO_REQ_REMINDER2_WEB',
                           'PO_REQ_APPROVE_JRAD',
                           'PO_REQ_APPROVE_SIMPLE',
                           'PO_REQ_APPROVE_SIMPLE_JRAD',
                           'PO_REQ_REMINDER1_JRAD',
                           'PO_REQ_REMINDER2_JRAD'
                          ) AND
       prh.preparer_id = hre.person_id AND
       TRUNC(sysdate) BETWEEN hre.effective_start_date AND
       hre.effective_end_date AND
       wlur.user_orig_system = 'PER' AND
       sysdate < nvl(wlur.expiration_date, sysdate+1) AND
       prh.pcard_id = apc.card_id (+) AND
       apc.card_reference_id = ibycc.instrid (+)
       AND FSP.org_id = prh.org_id
       AND FSP.set_of_books_id = GSB.set_of_books_id
at
oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1435)


## Detail 0 ##
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1752)

at oracle.apps.icx.por.reqmgmt.server.AllReqsVOImpl.executeQuery(AllReqsVOImpl.java:182)
at oracle.apps.icx.por.reqmgmt.webui.RMBaseCO.performAction(RMBaseCO.java:891)

-- What is working
 If we try to approve requisition from notification, there is no issue it gets approved. The error is raised while Approving from iProcurement page.

 -- Steps
The issue can be reproduced at will with the following steps.
1. Login into iProcurement -> Requisition -> Search for Requisitions To Approve.
2. Open the requisition and click on the Approve button .



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