Example of Troubleshooting the Import of EIM Contact Data into the S_CONTACT Table

Use the guidelines that follow to check data consistency in the Siebel tables for contact record import.

To diagnose the unique constraint error for an import of contact data

For all contact records, verify that the value in S_PARTY.ROW_ID is set to the same value in S_CONTACT.ROW_ID and S_CONTACT.PAR_ROW_ID. If a record exists in S_PARTY, then a matching record should also exist in S_CONTACT. Run the following two SQL queries to validate the data in these tables:
1. Query against S_CONTACT:
```
SELECT ROW_ID FROM S_CONTACT WHERE PAR_ROW_ID <> ROW_ID 
```
  This statement should return zero rows.
2. Query against S_PARTY:
```
SELECT PARTY_UID, NAME FROM
S_PARTY P1
WHERE PARTY_TYPE_CD = 'Person' AND
NOT EXISTS
(SELECT * FROM S_CONTACT O1
WHERE O1.PAR_ROW_ID = P1.ROW_ID) 
```
  This statement should return zero rows.
For all contact records, make sure that the corresponding S_PARTY.PARTY_TYPE_CD is set to ‘Person’. Use the following SQL statement to validate that this is set correctly:
```
SELECT ROW_ID FROM S_PARTY T1
WHERE EXISTS (SELECT * FROM S_CONTACT T2 WHERE T1.ROW_ID = T2.PAR_ROW_ID)
AND T1.PARTY_TYPE_CD <> 'Person' 
```
This statement should return zero rows.
Populate the exact PARTY_UID value in the EIM_CONTACT table as is in the base table. For example, if a record is created through the Siebel Client UI, then make sure that the value in S_PARTY.PARTY_UID is the same as the value in S_PARTY.ROW_ID. For these records, populate S_PARTY.ROW_ID into EIM_CONTACT.PARTY_UID.
The following SQL query checks for any mismatch in the PARTY_UID values between the EIM_CONTACT and S_PARTY tables:
```
SELECT PARTY_UID FROM EIM_CONTACT T1 WHERE T1.PARTY_TYPE_CD = 'Person' AND
NOT EXISTS (SELECT * FROM S_PARTY T2 WHERE T1.PARTY_UID = T2.PARTY_UID) 
```
This statement should return zero rows.
Values in the EIM_CONTACT.PARTY_UID column should be unique in an EIM batch. Use the following SQL statement to verify that there are no duplicate values in this column:
```
SELECT PARTY_UID, COUNT(*) FROM EIM_CONTACT
WHERE IF_ROW_BATCH_NUM = <eim batch#>
GROUP BY PARTY_UID
HAVING COUNT(*) > 1 
```
This statement should return zero rows. If any rows are returned, duplicate values in the PARTY_UID column exist within the same batch. The duplicate rows should be removed from this batch.

Solution

If an EIM batch has records created in an external system, but records created through the Siebel Client UI also exist, make sure that the correct PARTY_UID values are populated in the EIM_CONTACT table. For example, for externally generated contacts, EIM_ CONTACT.PARTY_UID will have a user-defined value from the external system. For contact records that were created using the Siebel Client UI, make sure that the value in EIM_CONTACT.PARTY_UID is set to the value in S_PARTY.ROW_ID.

If externally generated PARTY_UID values are incorrectly populated into contact records created through the Siebel Client UI, where the value in S_PARTY.PARTY_UID equals the value in S_PARTY.ROW_ID, EIM treats these records as new and tries to insert these records into the S_CONTACT table. Because the PARTY_UID value already exists in S_CONTACT, the EIM process fails with the unique constraint violation error for the S_CONTACT table.

If an EIM batch contains both contact records with user-defined PARTY_UID values and records created through the Siebel Client UI, the following solutions can be used to make sure this error does not occur:

Option 1. Configure your Siebel application so that for records generated through the Siebel Client UI, S_PARTY.PARTY_UID matches the format used when loading data into the EIM_CONTACT table.
Option 2. If you have user-defined PARTY_UID values in the S_PARTY table, then before running the EIM process, run the SQL statements that follow to identify any records that exist where an EIM_CONTACT.PARTY_UID value does not match an existing S_PARTY.PARTY_UID value. If records like this exist, then update the EIM_CONTACT table with the correct PARTY_UID value that matches a value in S_PARTY.PARTY_UID.
The following SQL statement can be used to identify such records in EIM table:
```
SELECT PARTY_UID FROM EIM_CONTACT T1 WHERE T1.PARTY_TYPE_CD = 'Person' AND
NOT EXISTS (SELECT * FROM S_PARTY T2 WHERE T1.PARTY_UID = T2.PARTY_UID) 
```
If this query does not return any records, then run the following query to find duplicate records:
```
SELECT CON_PERSON_UID FROM EIM_CONTACT T1 WHERE T1.PARTY_TYPE_CD = 'Person' AND
NOT EXISTS (SELECT * FROM S_CONTACT T2 WHERE T1.CON_PERSON_UID = T2.PERSON_UID) 
```
Populate the correct values for PARTY_UID in the EIM_CONTACT table matching the base table S_PARTY.PARTY_UID for such records.