R12:AP/IBY: Start Action on Invoice Pending Review Status Return With JBO-27122 and ORA-00904 Error

(Doc ID 2141881.1)

Last updated on MARCH 08, 2017

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

On : 12.1.3 version, E-Business Suite Specific

When trying to take action on the Payment Process Request (PPR) that is under "Invoice Pending Review" status, the following error:

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

-----------------------

Exception details shows the following message:

Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT ApSelectedInvoicesEO.CHECKRUN_NAME,
ApSelectedInvoicesEO.INVOICE_ID,
ApSelectedInvoicesEO.PAYMENT_NUM,
ApSelectedInvoicesEO.SET_OF_BOOKS_ID,
ApSelectedInvoicesEO.VENDOR_ID,
ApSelectedInvoicesEO.VENDOR_SITE_ID,
ApSelectedInvoicesEO.VENDOR_NUM,
ApSelectedInvoicesEO.VENDOR_NAME,
ApSelectedInvoicesEO.VENDOR_SITE_CODE,
ApSelectedInvoicesEO.CUSTOMER_NUM,
ApSelectedInvoicesEO.ADDRESS_LINE1,
ApSelectedInvoicesEO.ADDRESS_LINE2,
ApSelectedInvoicesEO.ADDRESS_LINE3,
ApSelectedInvoicesEO.CITY,
ApSelectedInvoicesEO.STATE,
ApSelectedInvoicesEO.ZIP,
ApSelectedInvoicesEO.PROVINCE,
ApSelectedInvoicesEO.COUNTRY,
ApSelectedInvoicesEO.ATTENTION_AR_FLAG,
ApSelectedInvoicesEO.INVOICE_NUM,
ApSelectedInvoicesEO.INVOICE_DATE,
ApSelectedInvoicesEO.VOUCHER_NUM,
ApSelectedInvoicesEO.INVOICE_AMOUNT,
ApSelectedInvoicesEO.AMOUNT_PAID,
ApSelectedInvoicesEO.DISCOUNT_AMOUNT_TAKEN,
ApSelectedInvoicesEO.DUE_DATE,
ApSelectedInvoicesEO.DISCOUNT_DATE,
ApSelectedInvoicesEO.INVOICE_DESCRIPTION,
ApSelectedInvoicesEO.PAYMENT_PRIORITY,
ApSelectedInvoicesEO.AMOUNT_REMAINING,
ApSelectedInvoicesEO.DISCOUNT_AMOUNT_REMAINING,
ApSelectedInvoicesEO.PAYMENT_AMOUNT,
ApSelectedInvoicesEO.DISCOUNT_AMOUNT,
ApSelectedInvoicesEO.SEQUENCE_NUM,
ApSelectedInvoicesEO.OK_TO_PAY_FLAG,
ApSelectedInvoicesEO.DONT_PAY_REASON_CODE,
ApSelectedInvoicesEO.ALWAYS_TAKE_DISCOUNT_FLAG,
ApSelectedInvoicesEO.AMOUNT_MODIFIED_FLAG,
ApSelectedInvoicesEO.CHECK_NUMBER,
ApSelectedInvoicesEO.INVOICE_EXCHANGE_RATE,
ApSelectedInvoicesEO.PAYMENT_CROSS_RATE,
ApSelectedInvoicesEO.BANK_ACCOUNT_TYPE,
ApSelectedInvoicesEO.ORIGINAL_INVOICE_ID,
ApSelectedInvoicesEO.ORIGINAL_PAYMENT_NUM,
ApSelectedInvoicesEO.ATTRIBUTE_CATEGORY,
ApSelectedInvoicesEO.ATTRIBUTE1,
ApSelectedInvoicesEO.ATTRIBUTE2,
ApSelectedInvoicesEO.ATTRIBUTE3,
ApSelectedInvoicesEO.ATTRIBUTE4,
ApSelectedInvoicesEO.ATTRIBUTE5,
ApSelectedInvoicesEO.ATTRIBUTE6,
ApSelectedInvoicesEO.ATTRIBUTE7,
ApSelectedInvoicesEO.ATTRIBUTE8,
ApSelectedInvoicesEO.ATTRIBUTE9,
ApSelectedInvoicesEO.ATTRIBUTE10,
ApSelectedInvoicesEO.ATTRIBUTE11,
ApSelectedInvoicesEO.ATTRIBUTE12,
ApSelectedInvoicesEO.ATTRIBUTE13,
ApSelectedInvoicesEO.ATTRIBUTE14,
ApSelectedInvoicesEO.ATTRIBUTE15,
ApSelectedInvoicesEO.BANK_ACCOUNT_NUM,
ApSelectedInvoicesEO.BANK_NUM,
ApSelectedInvoicesEO.EXCLUSIVE_PAYMENT_FLAG,
ApSelectedInvoicesEO.PROPOSED_PAYMENT_AMOUNT,
ApSelectedInvoicesEO.PAY_SELECTED_CHECK_ID,
ApSelectedInvoicesEO.PRINT_SELECTED_CHECK_ID,
ApSelectedInvoicesEO.ORG_ID,
ApSelectedInvoicesEO.WITHHOLDING_AMOUNT,
ApSelectedInvoicesEO.INVOICE_PAYMENT_ID,
ApSelectedInvoicesEO.DONT_PAY_DESCRIPTION,
ApSelectedInvoicesEO.TRANSFER_PRIORITY,
ApSelectedInvoicesEO.EXTERNAL_BANK_ACCOUNT_ID,
ApSelectedInvoicesEO.ADDRESS_LINE4,
ApSelectedInvoicesEO.IBAN_NUMBER,
ApSelectedInvoicesEO.LEGAL_ENTITY_ID,
ApSelectedInvoicesEO.PAYMENT_CURRENCY_CODE,
ApSelectedInvoicesEO.PAYMENT_GROUPING_NUMBER,
ApSelectedInvoicesEO.CHECKRUN_ID,
ApSelectedInvoicesEO.PAYMENT_EXCHANGE_RATE,
ApSelectedInvoicesEO.PAYMENT_EXCHANGE_RATE_TYPE,
ApSelectedInvoicesEO.PAYMENT_EXCHANGE_DATE,
ApSelectedInvoicesEO.WITHHOLDING_STATUS_LOOKUP_CODE,
ApSelectedInvoicesEO.AP_CCID,
ApSelectedInvoicesEO.FUTURE_PAY_DUE_DATE,
AP_PAYMENT_UTIL_PKG.Get_Interest_due(
ApSelectedInvoicesEO.checkrun_id,
ApSelectedInvoicesEO.invoice_id,
ApSelectedInvoicesEO.payment_num) AS interest_due,
(NVL(ApSelectedInvoicesEO.payment_amount, 0)
+ NVL(AP_PAYMENT_UTIL_PKG.Get_Interest_due(
ApSelectedInvoicesEO.checkrun_id,
ApSelectedInvoicesEO.invoice_id,
ApSelectedInvoicesEO.payment_num), 0)) AS total_amt_payable,
AI.INVOICE_AMOUNT - NVL(AI.AMOUNT_PAID, 0) AS unpaid_Inv_Amt, --7668936
AI.invoice_currency_code,
AP_PAYMENT_UTIL_PKG.get_ou_name(ApSelectedInvoicesEO.org_id) AS operating_unit,
AP_PAYMENT_UTIL_PKG.get_le_name(AI.legal_entity_id) AS le_name,
NVL(APS.gross_amount, 0) AS amt_due,
AP_PAYMENT_UTIL_PKG.Get_gain_loss_amount gain_loss_amount,
AI.invoice_type_lookup_code,
ALC1.displayed_field AS INVOICE_TYPE,
'N' as SELECT_FLAG,
/* Bug 9715718 */
nvl(ApSelectedInvoicesEO.REMIT_TO_SUPPLIER_NAME,ApSelectedInvoicesEO.VENDOR_NAME) REMIT_TO_SUPPLIER_NAME ,
ApSelectedInvoicesEO.AFFECTS_REJECTION_LEVEL

FROM AP_SELECTED_INVOICES_ALL ApSelectedInvoicesEO,
ap_invoices_all AI,
ap_payment_schedules_all APS,
ap_lookup_codes ALC1
WHERE ApSelectedInvoicesEO.invoice_id = AI.invoice_id
AND ApSelectedInvoicesEO.invoice_id = APS.invoice_id
AND ApSelectedInvoicesEO.payment_num = APS.payment_num
AND ALC1.LOOKUP_TYPE (+) = 'INVOICE TYPE'
AND ALC1.LOOKUP_CODE (+) = AI.INVOICE_TYPE_LOOKUP_CODE
AND ApSelectedInvoicesEO.ok_to_pay_flag = 'Y'
AND ApSelectedInvoicesEO.checkrun_id = :1
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.ap.payments.ps.webui.SelectedPsCO.processRequest(SelectedPsCO.java:71)
.......

## Detail 0 ##
java.sql.SQLSyntaxErrorException: ORA-00904: "APSELECTEDINVOICESEO"."AFFECTS_REJECTION_LEVEL": invalid identifier
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Payables Payment Manager Home
2. Submit a Payment Process Requests and check the option ="Stop Process for Review After Scheduled Payment Selection" under Processing tab.
3. Payment Process Request will stop into "Invoices Pending Review" status
4. Click on the Start Action icon

You have confirmed that you are at the latest version of the following files:

AP_PAYMENT_UTIL_PKG appayuts.pls 120.12.12010000.4
AP_PAYMENT_UTIL_PKG appayutb.pls 120.25.12010000.11
ApSelectedInvoicesEO.xml  120.0.12010000.5
PsrAMImpl.java  120.21.12010000.17
SelectedPsCO.java  120.10.12010000.6

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