R12 Duplicate Payee records in IBY_EXTERNAL_PAYEES_ALL (Doc ID 1308849.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Payables - Version 12.0.3 to 12.2.5 [Release 12.0 to 12.2]
Oracle Payments - Version 12.0.3 to 12.2.5 [Release 12.0 to 12.2]
Information in this document applies to any platform.

Symptoms

When attempting to make Payment to Payment Request type Invoices (AR Refund invoices), the following error occurs.

ERROR
-----------------------
APP-FND-1934: ORA-01422: exact fetch returns more than requested number of rows in Package IBY_DISBURSEMENT_COMP_PUB Procedure
Get_Default_Payment_Attributes

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create Refund in AR
2. Find Payment Request type Invoice in AP
3. Try to Make Payment
Payment > Entry > Payment > Enter/Adjust Invoices

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot make Payments to Customers for Refund


Please run the below SQL to identify the records with this issue:

SELECT
A.EXT_PAYEE_ID ,
A.PAYEE_PARTY_ID ,
A.PAYMENT_FUNCTION ,
A.EXCLUSIVE_PAYMENT_FLAG ,
A.PARTY_SITE_ID ,
A.SUPPLIER_SITE_ID ,
A.ORG_ID ,
A.ORG_TYPE ,
A.DEFAULT_PAYMENT_METHOD_CODE ,
A.ECE_TP_LOCATION_CODE ,
A.BANK_CHARGE_BEARER ,
A.BANK_INSTRUCTION1_CODE ,
A.BANK_INSTRUCTION2_CODE ,
A.BANK_INSTRUCTION_DETAILS ,
A.PAYMENT_REASON_CODE ,
A.PAYMENT_REASON_COMMENTS ,
A.INACTIVE_DATE ,
A.PAYMENT_TEXT_MESSAGE1 ,
A.PAYMENT_TEXT_MESSAGE2 ,
A.PAYMENT_TEXT_MESSAGE3 ,
A.DELIVERY_CHANNEL_CODE ,
A.PAYMENT_FORMAT_CODE ,
A.SETTLEMENT_PRIORITY ,
A.REMIT_ADVICE_DELIVERY_METHOD ,
A.REMIT_ADVICE_EMAIL ,
A.REMIT_ADVICE_FAX
FROM iby_external_payees_all a
WHERE EXISTS (SELECT 'duplicates'
FROM iby_external_payees_all b
WHERE a.payee_party_id = b.payee_party_id
AND a.payment_function = b.payment_function
AND NVL(a.party_site_id, '0') = NVL(b.party_site_id, '0')
AND NVL(a.supplier_site_id, '0') = NVL(b.supplier_site_id, '0')
AND NVL(a.org_id, '0') = NVL(b.org_id, '0')
AND NVL(a.org_type, '0') = NVL(b.org_type, '0')
AND a.ext_payee_id <> b.ext_payee_id
)
ORDER BY a.PAYEE_PARTY_ID, a.last_update_date DESC;

If the above SQL returns data, then the solution in this document is applicable.

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