EAR 9.1+ AR_EMAIL_ADV Abends at Step AR_EMAIL_ADV.PMTLOOP.Step02 (SQL) -- RC = 805 for Multiple MICR IDs with Same Effective Date for a Customer (Doc ID 2024477.1)

Last updated on SEPTEMBER 16, 2016

Applies to:

PeopleSoft Enterprise FIN Receivables - Version 9.1 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On : 9.1 version, Direct Debit bundle 31

AR_EMAIL_ADV AE process abends for multiple MICR IDs with same effective date for a customer
AR_EMAIL_ADV AE abends at Step AR_EMAIL_ADV.PMTLOOP.Step02 (SQL) -- RC = 805.

ERROR
-----------------------
(SQL error. Stmt #: 1620 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADMIN.PS_RADV_HDR_TAO) violated Failed SQL stmt:INSERT INTO PS_RADV_HDR_TAO (PROCESS_INSTANCE , RADVISE_KEY , PARTY_ID , NAME1 , COU NTRY, ADDRESS1, ADDRESS2..

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Set Up Financials/Supply Chain >>Product Related> Receivables> Payments> Direct Debit Profile - select the Email Notify option on the Direct Debit Profile page;
2. Customer / Customer Information / MICR Information / add another MICR ID with same effective date with existing MICR ID or If there is an existing MICR ID then add 1 new with same effective date.
3. Customer / Customer Information / General Information / Bill To Options page, the Payment Method field value must be DD (Direct Debit) and the Direct Debit Profile ID field value must be one that has the Email Notify option selected;
4. Customer / Contact Information / Customer Contact / The contact must have the Primary Bill To option selected on the Contact Customer page;
5. Customer / Contact Information / Contact / The Email ID field for the contact must have a valid Email address on the Contact page;
6. Accounts Receivables / Pending Item / Create a pending item online with a DD payment method and post through ARUPDATE;
7. Accounts Receivables / Direct Debit / Create Direct Debits, Remit them and Post;
8. Accounts Receivables / Receivables Update / Run ARUPDATE;
9. Accounts Receivables / Direct Debit / Remit to Bank/ Run Email Remittance Advice;

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot complete AR_EMAIL_ADV process .

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