Example of Troubleshooting the Import of EIM Account Data into the S_ORG_EXT Table

The examples shown in this topic use the EIM_ACCOUNT interface table. You can replace EIM_ACCOUNT with the appropriate EIM table for importing contact data as needed.

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

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

For all account records. verify that the value S_PARTY.ROW_ID is set to the same value in S_ORG_EXT.ROW_ID and S_ORG_EXT.PAR_ROW_ID. If a record exists in S_PARTY, then a corresponding record should also exist in S_ORG_EXT. Run the two SQL queries that follow to validate the data in these tables.
1. Query against S_ORG_EXT:
```
SELECT ROW_ID FROM S_ORG_EXT 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 = 'Organization' AND
NOT EXISTS
(SELECT * FROM S_ORG_EXT O1
WHERE O1.PAR_ROW_ID = P1.ROW_ID) 
```
  This statement should return zero rows.
For all account records, make sure that the corresponding S_PARTY.PARTY_TYPE_CD is set to ‘Organization’. Use the following SQL statement to validate that this is set correctly:
```
SELECT ROW_ID FROM S_PARTY T1
WHERE EXISTS (SELECT * FROM S_ORG_EXT T2 WHERE T1.ROW_ID = T2.PAR_ROW_ID)
AND T1.PARTY_TYPE_CD <> 'Organization' 
```
This statement should return zero rows.
Populate the exact PARTY_UID value in the EIM_ACCOUNT table as is in the base table. For example, if a record is created in the Siebel Client UI, 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_ACCOUNT.PARTY_UID.
The following SQL statement checks for any mismatch in the PARTY_UID values between the EIM_ACCOUNT and S_ORG_EXT tables:
```
SELECT PARTY_UID FROM EIM_ACCOUNT T1 WHERE T1.PARTY_TYPE_CD = 'Organization' 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_ACCOUNT.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_ACCOUNT
WHERE IF_ROW_BATCH_NUM = <EIM Batch Number>
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 both the external system and through the Siebel Client UI, make sure that the correct PARTY_UID values are populated in the EIM_ACCOUNT table. For example, for externally generated accounts, EIM_ACCOUNT.PARTY_UID column will have a user-defined value, but for account records created using the Siebel Client UI, the value in EIM_ACCOUNT.PARTY_UID is set to the value in S_PARTY.ROW_ID for the Account.

If externally generated PARTY_UID values are incorrectly populated for account records created using 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 records and tries to insert these records into the S_ORG_EXT table. Because the PARTY_UID value already exists in S_ORG_EXT, the EIM process fails with the unique constraint violation error for the S_ORG_EXT table.

If an EIM batch contains both account records with user-defined PARTY_UID and records created through the Siebel Client UI, the two solutions that follow 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_PART.PARTY_UID matches the format you use when loading data into the EIM_ACCOUNT table.
Option 2. If you have user-defined PARTY_UID values in the S_PARTY table, then before running the EIM process, run the following SQL statements to identify any records that exist where an EIM_ACCOUNT.PARTY_UID value does not match with an existing S_PARTY.PARTY_UID value. If records like this exist, then update the EIM_ACCOUNT 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 the EIM table:
```
SELECT PARTY_UID FROM EIM_ACCOUNT T1 WHERE T1.PARTY_TYPE_CD = 'Organization' AND
NOT EXISTS (SELECT * FROM S_PARTY T2 WHERE T1.PARTY_UID = T2.PARTY_UID) 
```
To correct the data, populate the appropriate values for PARTY_UID in the EIM_ACCOUNT table matching the base table S_PARTY.PARTY_UID for such records.