Duplicate Customer Relationship In HZ_CUST_ACCT_RELATE_ALL (Doc ID 1247597.1)

Last updated on JULY 25, 2016

Applies to:

Oracle Receivables - Version: 12.0.6 and later   [Release: 12.0 and later ]
Information in this document applies to any platform.

Symptoms


AR creates a new row in HZ_CUST_ACCT_RELATE_ALL when there is already a row in HZ_CUST_ACCT_RELATE_ALL with status=I
Because of this, Form OEXOEORD (OM order Organizer) header flexfield fail with error
"Cause: FDFGVD failed due to ORA-01422: exact fetch returns more than requested number of rows."  

SQL> select party_name, party_id from hz_parties where party_name in ('Jackie CUST10', 'Jackie CUST01'); 
 
PARTY_NAME PARTY_ID 
---------------------------- ---------- 
Jackie CUST01 303602 
Jackie CUST10 303611 
 
SQL> select cust_account_id, account_number, status from hz_cust_accounts where party_id in (303602,303611); 
 
CUST_ACCOUNT_ID ACCOUNT_NUMBER S 
--------------- ------------------------------ - 
96166 5447Change A 
96175 5456 A 
 
SQL> select cust_account_id, related_cust_account_id, status from HZ_CUST_ACCT_RELATE_ALL 
           where cust_account_id =96175; 
 
CUST_ACCOUNT_ID RELATED_CUST_ACCOUNT_ID S 
--------------- ----------------------- - 
96175 96166 A 
 
Inactivate the customer relationship 

SQL> select cust_account_id, related_cust_account_id, status from HZ_CUST_ACCT_RELATE_ALL
           where cust_account_id =96175; 
 
CUST_ACCOUNT_ID RELATED_CUST_ACCOUNT_ID S 
--------------- ----------------------- - 
96175 96166 I 
 
Then restore the relationship 
 
SQL> select cust_account_id, related_cust_account_id, status from 
HZ_CUST_ACCT_RELATE_ALL where cust_account_id =96175; 
 
CUST_ACCOUNT_ID RELATED_CUST_ACCOUNT_ID S 
--------------- ----------------------- - 
96175 96166 A 

Now inactivate the relationship again by changing the status from Active to Inactive 
 
This time instead of Restore the previous relationship, I created a new 
customer relationship with Customer with customer number 5447Change 

SQL> select cust_account_id, related_cust_account_id, status from HZ_CUST_ACCT_RELATE_ALL
          where cust_account_id =96175; 
 
CUST_ACCOUNT_ID RELATED_CUST_ACCOUNT_ID S 
--------------- ----------------------- - 
96175 96166 I 
96175 96166 A 
 
So the system allows you to create another Active relationship with the 
Inactivated relationship existing in the HZ_CUST_ACCT_RELATE_ALL table. 

Now you cannot restore the inactivated relationship, you get error: 
Error 
You cannot activate this relationship because an active customer account 
relationship already exists between accounts 96175 and 96166. 

Try to remove the Active relationship between accounts 96175 and 96166. 
 
SQL> select cust_account_id, related_cust_account_id, status from HZ_CUST_ACCT_RELATE_ALL
           where cust_account_id =96175; 
 
CUST_ACCOUNT_ID RELATED_CUST_ACCOUNT_ID S 
--------------- ----------------------- - 
96175 96166 I 
96175 96166 I

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