CUSTOMER UI CREATING DUPLICATES ORIG_SYSTEM_REFERENCE IN HZ_CUST_ACCOUNT_ROLES

(Doc ID 1990793.1)

Last updated on SEPTEMBER 13, 2016

Applies to:

Oracle Trading Community - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Customer User Interface (UI) is creating duplicate values for ORIG_SYSTEM_REFERENCE in HZ_CUST_ACCOUNT_ROLES in following scenarios.

Steps to Reproduce:
1) You already have records in HZ_CUST_ACCOUNT_ROLES with certain numeric values that were created via Customer Interface. You populate orig_system_reference in the interface table for the contact you import via interface. This value is imported into column ORIG_SYSTEM_REFERENCE in HZ_CUST_ACCOUNT_ROLES table.

2) Now your sequence HZ_CUST_ACCOUNT_ROLES_S current value is same as the value you are passing via the interface.

3) Go to customer UI and create a new contact at account or site level. The idea is that the current value of sequence above already exists for another row in column ORIG_SYSTEM_REFERENCE in HZ_CUST_ACCOUNT_ROLES table.

4) Save the contact and run the following query..

SELECT orig_system_reference, COUNT(*)
FROM HZ_CUST_ACCOUNT_ROLES
GROUP BY orig_system_reference
HAVING COUNT(*) > 1

You will see one of the value will belong to the contact created above.

Business Impact
------------------------
Autoinvoice because of this data issue :
Current system time is 17-SEP-2013 09:24:15
APP-AR-11526: ORA-01427: single-row subquery returns more than one row

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