APP-AR-96282: Unique Constraint AR.HZ_CUST_ACCT_SITES_U2 Violated

Posted by: Edi Yanto on: January 7, 2009

• In: Oracle
• Comment!

After entering the address details in Create Customer Form, get error when saving:
“APP-AR-96282:Error The following SQL error occured
ORA-00001 : unique constraint (AR.HZ_CUST_ACCT_SITES_U2) violated”

Cause

The current value of sequence hz_cust_acct_sites_s is smaller than the existing data in hz_cust_acct_sites_all.orig_system_reference.

select hz_cust_acct_sites_s.currval from dual;
returns
3188

SQL> select column_name from dba_ind_columns where index_name=’HZ_CUST_ACCT_SITES_U2′;
COLUMN_NAME
ORIG_SYSTEM_REFERENCE
ORG_ID

But client has records in hz_cust_acct_sites_all table with value greater than 901000 for orig_system_reference column.

Solution

1. DROP sequence ar.hz_cust_acct_sites_s;
2. CREATE SEQUENCE AR.HZ_CUST_ACCT_SITES_S
START WITH 10001
MAXVALUE 900999
MINVALUE 10001
NOCYCLE
NOCACHE;
Note: the start value, minimum/maximum value depends on your needs and  the sequence must be set higher than the largest value stored in HZ_CUST_ACCT_SITES_All.orig_system_reference.

Reference: Doc ID 388728.1

Tags: ebiz, troubleshooting