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.


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:
SQL error during statement preparation.  Statement: SELECT
       prh.segment1 requisition_number,
       hre.full_name preparer_name,
       prh.authorization_status auth_status,
          ) closed_code,
       ibycc.masked_cc_number card_number,
       prh.transferred_to_oe_flag placed_on_so_flag,
          ) change_pending_flag,
       NVL(prh.active_shopping_cart_flag, 'N') active_shopping_cart_flag,
       'POR_VIEW_TO_APPR_REQS' view_mode,
       org.name organization,
       prh.org_id ,
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  ,
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',
                          ) 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

## 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 .


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