Customer Merge Errors: ARP_CMERGE_ARCUS.create_same_sites: ORA-20001: This receipt method is already active for this date range (Doc ID 1608065.1)

Last updated on MAY 20, 2024

Applies to:

Symptoms

Customer Merge (RAXMRG) failed with:

ORA-20001: This receipt method is already active for this date range

1. A query of the accounts to be merged
   select party_id, account_number, cust_account_id  from hz_cust_accounts where account_number in ('38044','125325');
   
   PARTY_ID ACCOUNT_NUMBER CUST_ACCOUNT_ID
    487204   125325                    138641
    487204   38044                      38527
   The accounts being merged belong to the same party / customer.  This is indicated by the party_id being the same for both accounts.
   
   
2. A query of the accounts assigned receipt methods.
   
   SELECT  customer_id, receipt_method_id, site_use_id
   FROM    ra_cust_receipt_methods
   WHERE   customer_id IN (38527,138641)
    AND     sysdate BETWEEN start_date
    AND     nvl (end_date,to_date ('4712/12/31','YYYY/MM/DD'))
   GROUP BY customer_id, receipt_method_id, site_use_id HAVING  count (*) > 1
   
   Further analysis of the data for CUST_ACCOUNT_ID in (138641,38527) shows that the issue might be due to overlapping receipt_methods in the table ra_cust_receipt_methods
   
    CUST_RECT_METH_ID RCT_METHOD_ID SITE_USE_ID START_DATE END_DATE
    ---------------------- ----------------- ------------ ----------- --------
    11998                        1111                         66137  16-OCT-03    null
    44004                        1111                        274540 31-OCT-06    null
    51987                        1111                        274540 30-SEP-06    null
   
   Customer(customer_id=38527),  SITE_USE_ID=274540 seems having overlapping dates.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.