Running BO_BAS_LOAD and unique constraint error (Doc ID 1389969.1)

Last updated on DECEMBER 02, 2015

Applies to:

PeopleSoft Enterprise CRM Online Marketing - Version 8.9 to 9.1 [Release 8.9 to 9]
Information in this document applies to any platform.

Symptoms

There are duplicate bas_sysid values found in the PS_BO_BASIC_IND table which is causing OLM emails to go to unexpected recipients.
After running the BO_BAS_LOAD process, there are still duplicates in the table.

To fix the above issue , the following steps were followed

1. Expire duplicate Worker roles in PS_BO_ROLE table
2. Update duplicate Bas_sysid rows in PS_BO_BASIC_IND, setting them = 0
3. Run BO_BAS_LOAD App Engine

But while running the BO_BAS_LOAD App Engine , the app engine failed at a different place with a unique constraint error. 
The failing statement is similar to the following


Stmt #: 1617 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_BO_BASIC_IND) violated
Failed SQL stmt:INSERT INTO PS_BO_BASIC_IND (BO_ID, ROLE_TYPE_ID, BO_REL_ID, BAS_SYSID, SETID, EXTERNAL_LIST_ID, SOURCE_SYSID, COMPANY_SYSID, BO_ID_COMPANY, ROLE_TYPE_ID_ORG, REL_TYPE_ID, DATASET_SYSID, SSN, TITLE, FUNCTIONAL_TITLE, DEPARTMENT, LANGUAGE_CD, ...........................'LDURETTE', CAST(SYSTIMESTAMP AS TIMESTAMP), 'LDURETTE', 0, CAST(SYSTIMESTAMP AS TIMESTAMP) FROM PS_BO_ROLE A, PS_BO_ROLE_TYPE B WHERE A.ROLE_TYPE_ID = 9 AND A.ROLE_TYPE_ID = B.ROLE_TYPE_ID AND B.BASIC_DATA_ENABLED = 'Y' AND A.ROLE_START_DT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') AND A.ROLE_END_DT > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') AND NOT EXISTS ( SELECT 'X' FROM PS_BO_BAS_SYS_TMP1 S WHERE S.BO_ID = A.BO_ID AND S.ROLE_TYPE_ID = A.ROLE_TYPE_ID AND S.BO_REL_ID = 0)

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