Bookshelf Home | Contents | Index | PDF     

Siebel Enterprise Integration Manager Administration Guide > EIM: Examples of Common Usage > EIM Import Process Examples >

Example of Troubleshooting the Unique Constraint Error when Importing Accounts or Contacts

This example provides further detail to complement Troubleshooting the Unique Constraint Error when Importing Accounts or Contacts.

The unique constraint error when inserting records using EIM is usually due to inconsistent data in the base tables or incorrect data populated in the interface tables. The inconsistent data may result when two different server tasks, such as Siebel eAI processes and EIM processes, are run at the same time to import the same data.

For example, you populate the EIM_ACCOUNT table with a new record to be added to the S_PARTY table. Uniqueness for the S_PARTY table is based on the S_PARTY_U1 index. However, the associated record in the S_ORG_EXT table that EIM will create may be found to be a duplicate of an existing record in the S_ORG_EXT table, because uniqueness for the S_ORG_EXT table is based on the S_ORG_EXT_U1 index. This duplicate record may have been created by another process, such as an eAI process or a process initiated through the user interface.

Because the S_ORG_EXT table is considered a 1:1 extension table of the S_PARTY table, EIM only checks if there is an existing S_ORG_EXT record that references the S_PARTY row in the PAR_ROW_ID column. In this case, no record is returned since the S_PARTY record is a new one. As a result, the S_ORG_EXT_U1 index is violated when EIM tries to insert the record into the S_ORG_EXT table. This incomplete EIM job then creates new S_PARTY rows without the associated S_ORG_EXT rows.

The PARTY_UID column is part of the user key for the S_PARTY table and is used by the EIM process to identify if an account or contact record is a new record or an existing record.

The S_PARTY.PARTY_UID is a user-definable key and can have any of the following values:

  • If contact or account data is being migrated from a system external to Siebel, then the PARTY_UID column can be any user-defined key value or contact or account key in the external system.
  • If the contact record is created using the Siebel Web Client, then S_PARTY.PARTY_UID is set to the value in S_PARTY.ROW_ID by default.
  • If the account record is created using the Siebel Web Client, then S_PARTY.PARTY_UID is set to the same value in S_PARTY.ROW_ID by default.

The remainder of this topic is divided into two parts which detail diagnostics steps for each of the following two scenarios:

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

  1. 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

    The above statement should return zero rows.

    1. 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)

    The above statement should return zero rows.

  2. 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'

    The above statement should return 0 rows.

  3. 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)

    The above statement should return zero rows.

  4. 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

    The above 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 the above query does not return any records, run the following query to find the 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.

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

The examples below 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

  1. 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

    The above statement should return zero rows.

    1. 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)

    The above statement should return zero rows.

  2. 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'

    The above statement should return zero rows.

  3. 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)

    The above statement should return zero rows.

  4. 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

    The above 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.

    
Siebel Enterprise Integration Manager Administration Guide