Similar New Records Throw Errors ORA-00001, SBL-DAT-00381, SBL-DAT-00382 During UCM Batch Process (Doc ID 1599177.1)

Last updated on JUNE 28, 2016

Applies to:

Siebel Universal Customer Master - Version 8.0 SIA [20405] and later
Information in this document applies to any platform.

Symptoms

If 2 new records for the same Account are in the same batch (e.g. from 2 different external systems), UCM Batch Manager will hit a unique user key (S_ORG_EXT_U1) error for the second record, and the whole process will halt and not process further records.

This issue can be reproduced with the following scenarios:

System "Alpha" and System "Beta" are both daily batch external systems, which will provide Account delta loads at End-of-Day. Coincidentally, if the same new account (say "Star Light Ltd") appears in both systems, then at the end of the day during consolidation, 2 new SDH records will be created:
 

NameExternal SystemExt System Id
"Star Light Ltd" "Alpha" "Alpha-001"
"Star Light Ltd" "Beta" "Beta-001"

 



 

 

Since the company "Star Light Ltd" does not exist in the UCM database yet, the UCMBatchMgr log will show that the UCMBatchMgr component will earmark both records as "Insert". Subsequently record Alpha-001 gets processed successfully, but then when trying to Insert again for Beta-001, it will hit a user key error due to the fact that a conflict of NAME, LOC, and BU_ID has occurred. In the end, the "Beta-001" record will stay "Incomplete" in the SDH.

Error messages:
SBL-DBC-00111: An error has occurred writing to a record.
Please continue or ask your systems administrator to check your application configuration if the problem persists.
ORA-00001: unique constraint (SIEBEL.S_CONTACT_U1) violated
SBL-DAT-00381: A record that contains identical values to the record you have created already exists.
If you would like to enter a new record, please ensure that the field values are unique.
SBL-DAT-00382: The same values for 'Name, Location' already exist.
If you would like to enter a new record, please ensure that the field values are unique.

Testing some other similar scenarios, the following hit the same error:
1) Having multiple occurrence of a NEW Account in a batch with different Ext System (above scenario) -> Error
2) Having multiple occurrence of a NEW Account in a batch with same Ext System -> Error
3) Having multiple occurrence of a EXISTING Account in a batch with same Ext System -> Passed, account matched and marked for upsert instead insert

If such error is encountered in the middle of the Batch, subsequent records will not be processed after that. E.g. if there are 10 records to process, record #4 is hit with this issue, then records #5-#10 will be ignored and remain with UCM Type Code = "Batch".

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