ORA-01422

PPR Error: ORA-01422 Exact Fetch Returns More Than Requested Number Of Rows [ID 1305446.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-01422 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

April 28, 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

The table below details scenarios that have been known to cause this symptom in the Payment Processing Request (PPR Process).

1) Build Payments process

#	Release	Presentation / Error Stack	Root Cause	Fixed File (or fix)	Recommended Patch
1	12.0.6 12.1.1	When attempting to execute the Build program , gets the following error: the following error occurs: Error: R12 AMER : BUILD PROGRAM ERROR - CANNOT VALIDATE DOCUMENTS From FND Debug Log ------------------ IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets: Bank Acct is not present at supplier site level IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets: No Address OU level Payee Exists IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets: No Address level Payee Exists IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets: ORA-01422: exact fetch returns more than requested number of rows Exception occured when validating documents. Document validation will be aborted and no document statuses will be committed for payment request 4606 Exception occured when validating documents. Document validation will be aborted and no document statuses will be committed for payment request 4606 SQLCODE: 1 SQLCODE: 1 SQLERRM: User-Defined Exception SQLERRM: User-Defined Exception Build program error: Exception occured when attempting to validate the documents payable of the provided payment service request.	Document Validation errors out while validating supplier bank account assignment in following cases: 1) If party has more than one Address-OU or Address-level payees and bank account which is attached to document is not assigned to a supplier site. 2) If a bank account is attached to more than one payee across the hierarchy. Bug 9248800 AMER:PEN:R12:PYMT FORMAT FAILS SAME BANK ACCT ATTACHED TO DIFFERENT SUPPLIERS BUG 9914416 - ORA-1422 IBY_VALIDATIONSETS_PUB.APPLYDOCUMENT VALIDATIONSETS BUILD PROGRAM	iby patch/115/sql ibyvallb.pls 120.60.12010000.25 or higher	R12.1.x: Patch.9966455:R12.IBY.B No password is required. NOTE: As of 4/14/2011, the latest versions of file ibyvallb.pls can be found on : R12.0.x: Patch 11806195:R12.IBY.A Contact Oracle Support for password. R12.1.x: Patch.11819029:R12.IBY.B Contact Oracle Support for password.

2) Format Payment Instructions 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

@ Original patches reported to fix (use in a pinch if customer cannot apply latest code)

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 12.0.x codeline NOTE 557869.1
For the 12.1.x codeline NOTE 954704.1

Patch Wizard

Important: Whilst a patch may contain several hundred files the impact in 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.