ORA-00904

PPR Error: ORA-00904 [ID 1311855.1]

Applies to:

Oracle Payables - Version: 12.0.0 to 12.1.3 - Release: 12.0 to 12.1
Information in this document applies to any platform.
PPR Payment Batch

Purpose

The purpose of this note is to explain steps in diagnosing and troubleshooting the ORA-00904 database error in the EBS Payables module, Payment Batch(PPR).  This note attempts to step the reader through all known setup issues as well as known bugs that can cause this error to occur. 

Note:  If you are on older code versions (10.7, 11.0, 11.5.0 - 11.5.9, 12.0.0 - 12.0.5), your solution may not be included in this article.  Please expand your search to include the archived solutions using the selection method shown below

Last Review Date

March 13, 2011

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

1) Build Payments process

#	Release	Presentation / Error Stack	Root Cause	Fixed File (or fix)	Recommended Patch
1	12.0	The Build Payments Process fails with: ERROR FV_FEDERAL_PAYMENT_FIELDS_PKG.GET_PAY_INSTR_SEQ_NUM: Payment Instruction Sequence Assignment not set for org_id = 0 and payment_reason_code. When the Build was run in debug mode, the log file showed: ORA-00904: "PAYEE_BANK"."BRANCH_NUMBER": invalid	Build program errors out with - IBY_EXT_BANK_ACCOUNTS does not have the column BRANCH_NUMBER. This issue is because in the payment process profile, the branch_number is provided as a sort option which is erroring out with the code for ibypymib.pls. BUG 8291512 ORA-00904: "PAYEE_BANK"."BRANCH_NUMBER": INVALID IDENTIFIER	Resolved by application of latest version of Package - IBY_PAYINSTR_PUB ibypymib.pls 120.74.12000000.15	R12.1.x - Patch 10630448:R12.IBY.B R12.0.x - Patch 11872821:R12.IBY.A
2	12.0	When the customer creates a PPR for an AR refund invoice the build program fails withe the error: ERROR IBYBUILD module: Build Payments BUILD PROGRAM ERROR - CANNOT INSERT DOCUMENTS FOR PAYMENT SERVICE REQUEST IBY_DISBURSE_SUBMIT_PUB_PKG.insert_payreq_documents: Exception occurred when attempting to insert documents for calling app id 200, calling app payment service request id PRF7_Patch_Rebate IBY_DISBURSE_SUBMIT_PUB_PKG.insert_payreq_documents: SQLCODE: -904 IBY_DISBURSE_SUBMIT_PUB_PKG.insert_payreq_documents: SQLERRM: ORA-00904: "GLOBAL_ATTRIBUTE20": invalid identifier IBY_DISBURSE_SUBMIT_PUB_PKG.insert_payreq_documents: EXIT	Prerequisites of the Patch 6811355 were not applied	Resolved by application of latest version of Package - IBY_VALIDATIONSETS_PUB ibyvallb.pls.	R12.1 - Patch 11819029:R12.IBY.B R12.0 - Patch 11806195:R12.IBY.A
3	12.0	When creating payment process request the following error is received: Error: IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets: ORA-01422: exact fetch returns more than requested number of rows	Payment format error when same bank account is attached to two or more different suppliers.. Bug 9248800 AMER:PEN:R12:PYMT FORMAT FAILS SAME BANK ACCT ATTACHED TO DIFFERENT SUPPLIERS	Resolved by upgrading to the latest version of IBY_VALIDATIONSETS_PUB. ibyvallb.pls	R12.1 - Patch 11819029:R12.IBY.B R12.01 - Patch 11806195:R12.IBY.A

2) Format Payments process

#	Release	Presentation / Error Stack	Root Cause	Fixed File	Recommended Patch
1	12.0	The program Payment Format Instructions ends in error. User is getting the following error when an invoice selected for payment in PPR has interest and the PPR is creating the invoice interest: ERROR APP-SQLAP-10000: ORA-1422: exact fetch returns more than requested number of rows From FND Debug Log File IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : Params not passed to callout - completed pmts count: 1, completed docs count: 2 IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : Fatal: External app callout 'AP_PMT_CALLOUT_PKG.payments_completed', generated exception. IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQL code: -20001 IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQL err msg: ORA-20001: APP-SQLAP-10000: ORA-01422: exact fetch returns more than requested IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : Exception occured when marking payments complete for payment instruction 22438 IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQLCODE: -20001 IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : SQLERRM: ORA-20001: APP-SQLAP-10000: ORA-01422: exact fetch returns more than requested numb IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete : EXIT	The issue is that MOAC is not happening properly. While running the program, multi-org access is set but when we are querying the ap_system_parameters table to retrieve profile values, we are going to the secured synonym. As such the query is returning multiple rows. We should go to the base table and query up by the org_id of the invoice.	Resolved by upgrading to the latest version of package - AP_INTEREST_INVOICE_PKG apintinb.pls	R12.1 - Patch 9328384:R12.AP.B R12.01 - Patch 11821019:R12.AP.A

Standard Patching Process

Please consult as well the recommended patch lists for Payables. The list includes consolidated patches that contain fixes for issues that had been critical:

For the R12.0.x codeline, see Note 557869.1
For the R12.1.x codeline, see Note 954704.1

Patch Wizard

Important: While a patch may contain several hundred files, the impact on your system is most likely far less. Patches contain hard-prerequisites that are intended to make a patch work on a very low code baseline.

The Patch Wizard Utility will give you a detailed impact analysis for the patch above, please refer to
the Note 976188.1 for more information on the utility.