R12: IBY: Error "oracle.jbo.SQLStmtException: JBO-27122: SQL Error During Statement Preparation" When Trying to End Date a Supplier Bank Account (Doc ID 2851206.1)

Last updated on MARCH 02, 2023

Applies to:

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

Symptoms

User cannot end date a Supplier Bank Account. Error page displays with the following:

...
UNEXPECTED:[fnd.framework.webui.OAPageLayoutHelper]: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 PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,
       PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,
       PaymentInstrumentUsesEO.INSTRUMENT_TYPE,
       PaymentInstrumentUsesEO.INSTRUMENT_ID,
       PaymentInstrumentUsesEO.PAYMENT_FUNCTION,
       PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,
       PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,
       PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,
       PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,
       PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,
       PaymentInstrumentUsesEO.DEBIT_AUTH_END,
       PaymentInstrumentUsesEO.START_DATE,
       PaymentInstrumentUsesEO.END_DATE,
       ibyextbankaccts.bank_account_id,
       ibyextbankaccts.bank_account_name,
       ibyextbankaccts.bank_account_number,
       ibyextbankaccts.currency_code,
       ibyextbankaccts.bank_name,
       ibyextbankaccts.bank_number,
       ibyextbankaccts.bank_branch_name,
       ibyextbankaccts.bank_account_type,
       ibyextbankaccts.branch_number,
       ibyextbankaccts.iban_number,
       ibyextbankaccts.eft_swift_code,
       'N' detailView,
       'N1' disableMoveUpIcon,
       'N2' disableMoveDownIcon,
       PaymentInstrumentUsesEO.PAYMENT_FLOW
FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,
       IBY_EXTERNAL_BANK_ACCOUNTS_V ibyextbankaccts
WHERE PaymentInstrumentUsesEO.instrument_id = ibyextbankaccts.bank_account_id
   AND PaymentInstrumentUsesEO.instrument_type = 'BANKACCOUNT'
-- AND ibyextbankaccts.account_classification = 'EXTERNAL'
   AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = :1
--   AND sysdate between --NVL(ibyextbankaccts.start_date,sysdate) AND
--                     NVL(ibyextbankaccts.end_date,sysdate)
   AND PaymentInstrumentUsesEO.payment_function = :2
ORDER by PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE) QRSLT WHERE (INSTRUMENT_ID=:1);
   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)
   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.iby.disbursement.setup.component.server.BankAcctAssignmentsVOImpl.initQuery(BankAcctAssignmentsVOImpl.java:82)
   at oracle.apps.iby.disbursement.setup.component.server.IbyPayeeSetupAMImpl.init(IbyPayeeSetupAMImpl.java:159)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
...
...
## Detail 0 ##
java.sql.SQLException: Missing IN or OUT parameter at index:: 3
   at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1899)
   at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3764)
   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.iby.disbursement.setup.component.server.BankAcctAssignmentsVOImpl.initQuery(BankAcctAssignmentsVOImpl.java:82)
   at oracle.apps.iby.disbursement.setup.component.server.IbyPayeeSetupAMImpl.init(IbyPayeeSetupAMImpl.java:159)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
...

STEPS TO REPRODUCE

Log into Oracle Applications using Payables Responsibility.
Open the Supplier OAF Page
(N) Suppliers > Entry
Query a Supplier.
Click on "Banking Details" on the left-side menu.
Select Account Assignment Level = Site
Click on the "End Date" (pencil) icon for the Bank Account record to end date.The error.
If Profile Name = FND: Diagnostics and set it to 'No' appears only: You have encountered an unexpected error.Please contact your System Administrator for assistance.(If FND: Diagnostics = Yes,user must see 'Click here for exception details' link in order to see the full html error ).