10EIM: Examples of Common Usage

EIM: Examples of Common Usage

This chapter provides examples that illustrate the Siebel EIM processes. The information is organized as follows:

• EIM Import Process Examples

• EIM Merge Process Example

• EIM Delete Process Examples

• Examples of Resolving Foreign Keys

• Other Examples

EIM Import Process Examples

This section provides examples that can be applied to your running of import processes.

Example of Importing from Multiple EIM Tables in a Single .IFB File

You use shell processes to import multiple EIM tables in a single .IFB file. In the sample .IFB file that follows, first EIM_CONTACT is imported, then EIM_ACCOUNT is imported.

[Siebel Interface Manager]
   PROCESS = Import Contacts and Accounts
[Import Contacts and Accounts]
   TYPE = SHELL
   INCLUDE = "Import Contacts"
   INCLUDE = "Import Accounts"
[Import Contacts]
   TYPE = IMPORT
   TABLE = EIM_CONTACT
   BATCH = 100
[Import Accounts]
   TYPE = IMPORT
   TABLE = EIM_ACCOUNT
   BATCH = 200

Example of Updating a Table in a One-to-One Relationship with Its Parent

To update a table that has a one-to-one relationship with its parent table, make sure that the EIM table has only one record matching the user key of the target table.

For example, to update column values in S_ORG_EXT_X using EIM_ACCNT_DTL, there can be only one record in EIM_ACCNT_DTL that matches the user key of the S_ORG_EXT_X table. If more than one record with the same user key is inserted into this EIM table, then EIM might select the wrong record for update, and update IF_ROW_STAT with DUP_RECORD_EXISTS for the rest of the records.

Example of Updating Columns When There Are Two Records with the Same User Key in a Single Batch

EIM does not update columns in the following scenario: you have two records with same user key in the same batch, but with different nonuser keys to be updated.

This cannot be done because there is no way for EIM—which runs set-based operations—to know which record updates which of the non-user keys in one batch. EIM chooses the row with MIN(ROW_ID) and marks the other rows as duplicates.

To perform this kind of update, for which you are updating a record more than twice, you must run two different batches.

Example of Updating Columns When There Are Two Non-Target Base Tables Mapped to One EIM Table

If there are two non-target base tables mapped to one EIM table and one of the non-target base tables has a foreign key pointing to the other one, the data in the same row of the EIM table cannot be inserted into these two tables in one batch or one session. In cases like this, run EIM twice.

The reason is that, in Step 4 in EIM Import Process, the non-target parent base table is queried to resolve the foreign key of the non-target child base table, but the new row in the parent table has not been inserted.

Take for example, the EIM_CONTACT mapping. You cannot insert the data in one EIM_CONTACT row into S_PARTY, S_CONTACT, S_ADDR_PER, and S_CON_ADDR simultaneously, because the foreign key of the non-target child base table S_CON_ADDR is pointing to the non-target parent table S_ADDR_PER.

In the first run, a new row will be created in S_PARTY, S_CONTACT, S_ADDR_PER. In the second run, a new row will be inserted in S_CON_ADDR.

Example of Importing Primary Keys

In order to import a primary column, you must populate the following interface columns:

• These interface columns:

  • ROW_ID

  • IF_ROW_BATCH_NUM

  • IF_ROW_STAT

• The interface columns that map to the user key columns of the EIM table’s target base table

• The interface columns that map to the user key columns of the primary column’s base table

• The primary flag interface column that maps to the primary base column

• The interface columns that map to the primary’s intersection table

The intersection row must exist before setting the primary. If you want to import the intersection row and set it as the primary at the same time, you must also populate the interface columns that map to the intersection table’s required columns.

For example, If you want to update the S_ORG_EXT.PR_POSTN_ID primary column with the EIM_ACCOUNT interface table, you must populate the following:

• The interface columns:

  • ROW_ID

  • IF_ROW_BATCH_NUM

  • IF_ROW_STAT

• The interface columns that map to the user keys of the S_PARTY table (EIM_ACCOUNT’s target base table):

  • PARTY_UID

  • PARTY_TYPE_CD

• The interface columns that map to the user keys of the S_ORG_EXT table:

  • NAME

  • LOC

  • ACCNT_BU

• The primary flag interface column that maps to S_ORG_EXT.PR_POSTN_ID:

  • ACC_PR_POSTN

• The interface columns that map to the S_ACCNT_POSTN table (S_ORG_EXT.PR_POSTN_ID primary’s intersection table):

  • NAME

  • LOC

  • ACCNT_BU

  • POSTN_NAME

  • POSTN_DIVN

  • POSTN_LOC

  • POSTN_BU

  Note: You can find the S_ORG_EXT.PR_POSTN_ID primary’s intersection table using Siebel Tools. In Table, query and select S_ORG_EXT > Column, then query and select PR_POSTN_ID > Primary Inter Table Name property value.

The following are .IFB settings that you can use when running an EIM task that populates an EIM table to update a S_ORG_EXT row’s PR_POSTN_ID primary position to reference the S_POSTN row:

[Siebel Interface Manager]
USER NAME = "SADMIN"
PASSWORD = "<SADMIN's password>"
RUN PROCESS = Update S_ORG_EXT.PR_POSTN_ID
[Update S_ORG_EXT.PR_POSTN_ID]
TYPE = IMPORT
BATCH = 1
TABLE = EIM_ACCOUNT
ONLY BASE TABLES = S_PARTY, S_ORG_EXT, S_ACCNT_POSTN
INSERT ROWS = S_PARTY, FALSE
UPDATE ROWS = S_PARTY, FALSE
INSERT ROWS = S_ORG_EXT, FALSE
ONLY BASE COLUMNS = S_PARTY.PARTY_UID, \
S_PARTY.PARTY_TYPE_CD, \
S_ORG_EXT.NAME, \
S_ORG_EXT.LOC, \
S_ORG_EXT.BU_ID, \
S_ORG_EXT.PR_POSTN_ID, \
S_ACCNT_POSTN.OU_EXT_ID, \
S_ACCNT_POSTN.POSITION_ID

There are some cases that require you to include the MISC SQL parameter to set the primaries. For more information, see MISC SQL Parameter.

Example of Setting a Primary

As one example of setting a primary, you can populate the PR_PROD_LN_ID column in the S_PROD_INT base table by completing the following procedure:

To populate the PR_PROD_LN_ID column in the S_PROD_INT base table

1. Populate the S_PROD_INT base table using the EIM_PROD_INT interface table.

2. Populate the S_PROD_LN base table using the EIM_PROD_LN interface table.

3. Populate S_PROD_LN_PROD using EIM_PROD_INT1 and specifying the primary product lines by setting PROD_PR_PROD_LN to Y.

Visibility of Fields: Example of Importing Party Objects

Loading of party objects affects visibility of fields. You should be aware that, in most cases, an organization table should be populated along with the party object table.

For example, when a user clicks the Account field to open the MVG applet in the Contact form applet, the Account field disappears and returns to a null value after the EIM process is run.

This is because there is an association between Contacts and Accounts that is stored in the intersection table S_PARTY_PER. So to establish this relationship, you should fill in the columns for only the S_PARTY, S_CONTACT, and S_PARTY_PER table.

Visibility of Fields: Example of Importing Accounts

To view all accounts, the data must be inserted into the S_PARTY, S_ACCNT_POSTN, S_ORG_EXT, and S_ORG_BU tables, as well as other relevant tables. S_ORG_BU must be populated for visibility in the All Accounts view.

To insert the data into the required tables, you can use the EIM_ACCOUNT interface table. Make sure the values in the OU_NUM and MASTER_OU_ID columns of the S_ORG_EXT base table are populated.

The EIM_ACCOUNT and EIM_ORG_BU interface tables are mapped to S_ORG_BU. You can use EIM_ACCOUNT and EIM_ORG_BU to populate S_ORG_BU.

MASTER_OU_ID in S_ORG_EXT must be populated for visibility in any of the Accounts views. If S_ORG_EXT.MASTER_OU_ID is not populated, the imported accounts will be visible only in the Accounts/Orgs view in the Data Administration screen. The imported accounts will not be visible in the Accounts view in the Data Administration screen, or any other view including My Accounts, All Accounts, and All Accounts Across Organizations.

Note: When loading account addresses, make sure to set an explicit primary. The default setting is implicit, which means that primaries are not set until a record is retrieved in the application. This can cause queries, such as on the State field, to return incomplete or inconsistent data. For more information, see About Explicit Primary Mappings.

The sample .IFB file that follows can be used for importing accounts. The account visibility depends on S_ORG_BU to resolve the organization and S_ACCT_POSTN for the position.

[Siebel Interface Manager]
      USER NAME = "SADMIN"
      PASSWORD = "SADMIN"
      PROCESS = Import Account
[Import Account]
      TYPE = IMPORT
      BATCH = 555
      TABLE = EIM_ACCOUNT
ONLY BASE TABLES = S_PARTY, S_ACCNT_POSTN,  S_ORG_EXT, S_ORG_BU
DEFAULT COLUMN = ACCNT_FLG,  "Y"
DEFAULT COLUMN = ACTIVE_FLG,  "Y"
DEFAULT COLUMN = BUYING_GROUP_FLG,  "N"
DEFAULT COLUMN = CG_DEDN_AUTH_FLG,  "Y"
DEFAULT COLUMN = CG_SVP_A_LOCK_FLG,  "N"
DEFAULT COLUMN = CG_SVP_LOCK_FLG,  "N"
DEFAULT COLUMN = CG_SVP_SKIP_FLG,  "N"
DEFAULT COLUMN = CL_SITE_FLG,  "N"
DEFAULT COLUMN = DISA_CLEANSE_FLG,  "N"
DEFAULT COLUMN = EVT_LOC_FLG,  "N"
DEFAULT COLUMN = FCST_ORG_FLG,  "N"
DEFAULT COLUMN = FUND_ELIG_FLG,  "N"
DEFAULT COLUMN = INCL_FLG,  "N"
DEFAULT COLUMN = INT_ORG_FLG,  "N"
DEFAULT COLUMN = PLAN_GROUP_FLG,  "N"
DEFAULT COLUMN = PROSPECT_FLG,  "N"
DEFAULT COLUMN = PRTNR_FLG,  "N"
DEFAULT COLUMN = PRTNR_PUBLISH_FLG,  "N"
DEFAULT COLUMN = RPLCD_WTH_CMPT_FLG,  "N"
DEFAULT COLUMN = SKIP_PO_CRDCHK_FLG, "N

Visibility of Fields: Example of Importing Contacts

This example provides a sample .IFB file for importing contacts. The contact visibility depends on S_CONTACT_BU to resolve the organization and S_POSTN_CON for the position.

[Siebel Interface Manager]
      USER NAME = "SADMIN"
      PASSWORD = "SADMIN"
      PROCESS = Import Contact
[Import Contact]
      TYPE = SHELL
      INCLUDE = "Import Contact Informationen"
      INCLUDE = "Import POSTN_CON Informationen"
[Import Contact Informationen]
      TYPE = IMPORT
      TABLE= EIM_CONTACT
      BATCH = 555
      ONLY BASE TABLES = S_PARTY, S_CONTACT, S_CONTACT_BU
    DEFAULT COLUMN = CON_ACTIVE_FLG, "Y"
    DEFAULT COLUMN = CON_DISACLEANSEFLG, "N"
    DEFAULT COLUMN = CON_DISPIMGAUTHFLG, "N"
    DEFAULT COLUMN = CON_EMAILSRUPD_FLG, "N"
    DEFAULT COLUMN = CON_EMP_FLG, "N"
    DEFAULT COLUMN = CON_PRIV_FLG, "N"
    DEFAULT COLUMN = CON_INVSTGTR_FLG, "N"
    DEFAULT COLUMN = CON_PO_PAY_FLG, "N"
    DEFAULT COLUMN = CON_PROSPECT_FLG, "N"
    DEFAULT COLUMN = CON_PTSHPCONTACTFL, "N"
    DEFAULT COLUMN = CON_PTSHPKEYCONFLG, "N"
    DEFAULT COLUMN = CON_SENDSURVEY_FLG, "N"
    DEFAULT COLUMN = CON_SPEAKER_FLG, "N"
    DEFAULT COLUMN = CON_SUPPRESSEMAILF, "N"
    DEFAULT COLUMN = CON_SUPPRESSFAXFLG, "N" 
[Import POSTN_CON Informationen]
      TYPE = IMPORT
      TABLE= EIM_CONTACT1
      BATCH = 555
      ONLY BASE TABLES = S_PARTY, S_CONTACT, S_POSTN_CON

Visibility of Fields: Example of Importing Employees

This example provides a sample .IFB file for importing employees. The employee visibility depends on S_CONTACT_BU to resolve the organization, S_POSTN_CON for the position, S_PER_RESP for responsibility, and S_PARTY_PER for the relationship between the S_PARTY and S_CONTACT.

[Siebel Interface Manager]
      USER NAME = "SADMIN"
      PASSWORD = "SADMIN"
      PROCESS = Import New Employee
[IMPORT New Employee]
TYPE = SHELL
INCLUDE = "Import Employee"
INCLUDE = "Import Contact"
INCLUDE = "Import Contact1"
[Import Employee]
      TYPE = IMPORT
      BATCH = 666
      TABLE = EIM_EMPLOYEE
ONLY BASE TABLES = S_PARTY, S_CONTACT, S_EMP_PER, S_PARTY_PER, S_PER_RESP, S_USER
; For S-contact
DEFAULT COLUMN = CON_ACTIVE_FLG, "Y"
      DEFAULT COLUMN = CON_DISACLEANSEFLG, "N"
      DEFAULT COLUMN = CON_EMAILSRUPD_FLG, "N"
      DEFAULT COLUMN = CON_DISPIMGAUTHFLG, "N"
      DEFAULT COLUMN = CON_EMP_FLG, "Y"
      DEFAULT COLUMN = CON_PO_PAY_FLG, "N"
      DEFAULT COLUMN = CON_PRIV_FLG, "N"
      DEFAULT COLUMN = CON_PROSPECT_FLG, "N"
      DEFAULT COLUMN = CON_PTSHPCONTACTFL, "N"
      DEFAULT COLUMN = CON_PTSHPKEYCONFLG, "N"
      DEFAULT COLUMN = CON_SENDSURVEY_FLG, "N"
      DEFAULT COLUMN = CON_SUPPRESSEMAILF, "N"
      DEFAULT COLUMN = CON_SUPPRESSFAXFLG, "N"
; For vertical version
      DEFAULT COLUMN = CON_COURT_PAY_FLG, "N"
      DEFAULT COLUMN = CON_INVSTGTR_FLG, "N"
      DEFAULT COLUMN = CON_SPEAKER_FLG, "N"
      DEFAULT COLUMN = CON_SUSPECT_FLG, "N"
; For S-EMP_PER
DEFAULT COLUMN = ACCEPT_SR_ASGN_FLG, "N"
      DEFAULT COLUMN = CNTRCTR_FLG, "N"
      DEFAULT COLUMN = INT_NEWS_APPR_FLG, "N"
      DEFAULT COLUMN = EMP_CPFINALAPPRFLG, "N"
      DEFAULT COLUMN =  STORE_BUDGET_FLG, "N"
      DEFAULT COLUMN = STORE_FORECAST_FLG, "N"
[Import Contact]
TYPE = IMPORT
        BATCH = 666
        USE INDEX HINTS = TRUE
TABLE = EIM_CONTACT
ONLY BASE TABLES = S_PARTY, S_CONTACT_BU 
[Import Contact1]
TYPE = IMPORT
        BATCH = 666
TABLE = EIM_CONTACT1
ONLY BASE TABLES = S_PARTY, S_CONTACT, S_POSTN_CON

Visibility of Fields: Example of Importing Opportunities

To make opportunity records visible in the GUI, populate the following tables and columns.

S_REVN  
      REVN_ITEM_NUM,
      SUMMARY_FLG,
      OPTY_ID,
      ASGN_USR_EXCLD_FLG,
      COMMIT_FLG,
      BU_ID,
      CRDT_POSTN_ID,
      SPLIT_FLG,
      AUTOQUOTE_APPL_FLG,
      REVN_AMT_CURCY_CD,
      DYNMC_GRP_NUM,
      EFFECTIVE_DT,
      PROD_DESC_TEXT
S_OPTY_POSTN
      ROW_STATUS,
      PRIORITY_FLG,
      COMMITTED_FLG,
      ASGN_SYS_FLG,
      OPTY_ID,
      POSITION_ID,
      CREDIT_ALLC_PCT,
      FCST_CLS_DT,
      FCST_REVN_CURCY_CD,
      ASGN_MANL_FLG,
      ASGN_DNRM_FLG,      
SECURE_FLG,
      OPTY_BU_ID,
      SUM_COMMIT_FLG,
      SUM_EFFECTIVE_DT,
      CONSUMER_OPTY_FLG,
      SUM_REVN_AMT,
      OPTY_NAME,
      OPTY_CLOSED_FLG
S_OPTY_BU
      OPTY_ID,
      BU_ID,
SUM_COMMIT_FLG,
      SUM_EFFECTIVE_DT,
      SUM_REVN_AMT,
      OPTY_NAME
S_OPTY
      PR_POSTN_ID,
      NUM_RC_PERIODS,
      SUM_COMMIT_FLG,
      CONSUMER_OPTY_FLG,
      PR_REP_DNRM_FLG,
      PR_TERR_ID,
      SECURE_FLG,
      PR_REP_SYS_FLG,
      NAME,
      PR_REP_MANL_FLG,
      STATUS_CD,
      BU_ID,
      CLOSED_FLG,
      SUM_REVN_ITEM_ID,
      SALES_METHOD_ID,
      REVN_SPLIT_FLG,
      APPL_OWNER_TYPE_CD,
      STG_START_DT,
      SUM_EFFECTIVE_DT,
      CURCY_CD,
      EXEC_PRIORITY_FLG,
      ASGN_USR_EXCLD_FLG

Visibility of Fields: Example of Importing Assets

To make asset records visible in the GUI, populate the following tables and columns.

S_ASSET
      PR_POSTN_ID,
      ALT_FUEL_FLG,
      CAUTION_FLG,
      INTEGRATION_ID,
      ASSET_VAL_EXCH_DT,
      REGISTERED_DT,
      CUTOFF_FLG,
      ASSET_VAL_CURCY_CD,
      BU_ID,
      ASSET_NUM,
      ROOT_ASSET_ID,
      QTY,
      INSTALL_DT,
      BASE_CURRENCY_CD,
      PROD_ID,
      CUSTOMIZABLE_FLG,
      PR_EMP_ID
S_ASSET_POSTN
      ASGN_MANL_FLG,
      ASSET_ID,
      POSITION_ID,
      ASGN_SYS_FLG,
      ASGN_DNRM_FLG
S_ASSET_EMP
      ASSET_ID,
      EMP_ID
S_ASSET_BU
      ASSET_ID,
      BU_ID

Example of Troubleshooting the Import of Extension Columns

Use the guidelines that follow to troubleshoot an import failure that occurs when extension columns are added to some Siebel tables and the EIM import task failed to populate data to these columns.

To troubleshoot the import of extension columns

1. Delete the diccache.dat file from the <siebel server>\bin directory and test the EIM task again.

   EIM will rebuild this file from the information in the repository if the file does not exist.

2. Run the DBCHCK utility to make sure the EIM table in the repository is in synch with the EIM table in the database. For example, use the following command:

   dbchck /u SADMIN /p <SADMIN's password> /t <table owner> /r "Siebel Repository" 
   /l dbchck.log /d /s <ODBC data source> <interface table name>
   

   For information on running the DBCHCK utility, see Checking the Repository.

   1. If the repository is not in synch with the database, log in to Siebel Tools, and in the Table object, select the EIM table records.

   2. Click the Apply and Activate buttons to apply and activate all changes on the EIM table to the database.

   3. Run the DBCHCK utility again.

3. Follow the instructions to set event logging in Viewing the EIM Log File and run the EIM task.

   This generates a detailed EIM log file. Review the log file to see whether there are any errors causing the import failure.

4. If the extension column is:

   • A foreign key or primary column, its mapping should only be created through the EIM Table Mapping Wizard, or by Oracle's Application Expert Services.

   • A foreign key column, its foreign key mapping can be created by running the EIM Table Mapping Wizard on the base table of the extension column.

   • A primary column for a M:M relationship (that is, an EIM table is defined in the extension primary column's Primary Inter Table property), its EIM Explicit Primary Mapping can be created by running the EIM Table Mapping Wizard on the intersection table.

   • A primary column for a 1:M relationship (that is, no EIM table is defined in the extension primary column's Primary Inter Table property), its EIM Explicit Primary Mapping can be created by running the EIM Table Mapping Wizard on the primary child table (as defined in the extension primary column’s Primary Child Table property).

5. Check the mappings for the extension columns.

   1. Log in to Siebel Tools.

   2. Navigate to the EIM Interface Table object and query to select the interface table EIM Table Mapping.

      If the extension column is:

      • Not a foreign key or primary column, it should only have an Attribute Mapping under its base table’s EIM Table Mapping.

      • A foreign key column, it should not have any Attribute Mapping defined. It should have a Foreign Key Mapping.

      • A primary column, it should not have any Attribute Mapping or Foreign Key Mapping defined.

      If the extension primary column is:

      • For a M:M relationship, it should have an EIM Explicit Primary Mapping under its intersection table’s EIM Table Mapping.

      • For a 1:M relationship, it should have an EIM Explicit Primary Mapping under its primary child table’s EIM Table Mapping.

Checking the Repository

Step 2 in Example of Troubleshooting the Import of Extension Columns asks you to run the DBCHCK utility to make sure the EIM table in the repository is in synch with the EIM table in the database.

Both the DBCHCK and DICTUTL utilities are run from the DOS prompt in the siebsrvr\bin\ directory. DBCHCK verifies that the physical schema is in synch with the repository. DICTUTL verifies that all dock objects and rule definitions are correct.

To check the repository using DBCHCK and DICTUTL

1. Run the siebenv.bat file to make sure that the Siebel application environment variables are set correctly.

   Note: There should be no quotes around the parameters in siebenv.bat.

2. Make sure there are no quotes around the value to which SIEBEL_REPOSITORY is set.

   If this value is set incorrectly, you will encounter error messages.

3. Run the DBCHCK utility to verify that the physical schema is in synch with the repository. A typical command to run DBCHCK and generate a log file is the following:

   Prompt>dbchck /S <ODBC_DATASOURCE> /U <USERNAME> /P <PASSWORD> /T <TABLE OWNER> 
   /R <REPOSITORY> /L <LOGFILE> /D <CHECK_AGAINST_DICTIONARY> /A <ALL_TABLES> 
   

   1. Use the /A option to specify whether you are running the DBCHCK against all tables; use a Boolean ‘Y’ (no quotation marks) to specify that you are.

   2. To view all of the options for DBCHCK, run DBCHCK at the DOS prompt without any options.

      This provides all the options that can be used in conjunction with DBCHCK. The following are some of the common options used in conjunction with DBCHCK:

      Option	Description
      /S	Specifies the ODBC source to use for the database.
      /U	Specifies the username to log in to the database.
      /P	Specifies the user password to log in to the database.
      /T	Specifies the username of the table owner.
      /R	Specifies the repository name for the dictionary.
      /L	Specifies the log file name for errors.
      /D	Checks tables against the dictionary only.
      /A	Checks all Siebel tables in the database.

   3. Check the <LOGFILE> for unacceptable errors.

      Unacceptable errors may occur if data types are mismatched. Acceptable errors may occur if a schema object (such as a table or an index) is intentionally external to the repository.

4. Run DICTUTL to verify that all dock objects and rule definitions are correct. A typical command to run DICTUTL and generate a log file is the following:

   Prompt>dictutl /C <ODBC_DATASOURCE> /U <USERNAME> /P <PASSWORD> /D <TABLEOWNER> 
   /N <REPOSITORY_NAME> /A <IGNORE_DICTONARY_CACHE> y > LOGFILE.log 
   

   Further command options are explained as follows:

   Option	Description
   /A	Y means ignore the dictionary cache.
   > LOGFILE.log	LOGFILE is the log file that you designate for DICTUTL.

5. Review the LOGFILE.log file to check for errors.

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:

• Contact data import. See Example of Troubleshooting the Import of EIM Contact Data into the S_CONTACT Table.

• Account data import. See Example of Troubleshooting the Import of EIM Account Data into the S_ORG_EXT Table.

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 
      

      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.

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' 
   

   This statement should return zero 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) 
   

   This 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 
   

   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.

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

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 
      

      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.

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' 
   

   This 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) 
   

   This 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 
   

   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.

Example of Importing and Exporting Hierarchical LOVs

You can migrate a hierarchical list of values from one Siebel CRM environment to another, as shown in this example.

Note: First run the SQL suggested in 477627.1 (Doc ID) on My Oracle Support to get the ROW_ID values, and then replace the corresponding ROW_ID values in the .IFB settings for Step 1. This document was previously published as Siebel Technical Note 925.

To migrate a hierarchical list of values

1. Run an EIM export task using the following .IFB settings:

   [Siebel Interface Manager]
   	USER NAME = "SADMIN"
   	PASSWORD = "SADMIN"
   	PROCESS = Export LOV
   [Export LOV]
   	TYPE = SHELL
   	INCLUDE = "Export LOV_TYPE"
   	INCLUDE = "Export LOV_REPLICATION_LEVEL"
   	INCLUDE = "Export LOVs_Parent"
   	INCLUDE = "Export LOVs_Child"
   	USE INDEX HINTS = TRUE
   [Export LOV_TYPE]
   	TYPE = EXPORT
   	BATCH = 1
   	TABLE = EIM_LST_OF_VAL
   	EXPORT MATCHES = (TYPE = 'LOV_TYPE' and \
   		VAL <> 'LOV_TYPE' and \
   		VAL <> 'REPLICATION_LEVEL')
   	USE INDEX HINTS = TRUE
   [Export LOV_REPLICATION_LEVEL]
   	TYPE = EXPORT
   	BATCH = 2
   	TABLE = EIM_LST_OF_VAL
   	EXPORT MATCHES = (TYPE = 'REPLICATION_LEVEL' and \
   		VAL <> 'All')
   	USE INDEX HINTS = TRUE
   [Export LOVs_Parent]
   	TYPE = EXPORT
   	BATCH = 3
   	TABLE = EIM_LST_OF_VAL
   	EXPORT MATCHES = (TYPE <> 'LOV_TYPE' and \
   		PAR_ROW_ID IS NULL and \
   		ROW_ID NOT IN ('0-1EOTJ', '0-1EOTR', \
   		'0-1EOTT', '0-1EOTX', '0-1EOTZ', \
   		'0-1EOUB', '0-1EOUF', '0-1EOUH', \
   		'0-1EOUJ', '0-1EOUL', '0-1EOUN', \
   		'0-1EOUR', '0-2SRAZ', '0-3EM3U', \
   		'0-3EM3Y', '0-3EM42', '0-3G4D0', \
   		'0-3G4D2', '0-3GBNN', '0-3GFJQ', \
   		'0-3GFJV', '0-3K8OB', '0-3LEF9', \
   		'0-3LG6Z', '0-3RL6J', '0-3YWL5', \
   		'0-3YWLD', '0-40X27', '0-6ECJG', \
   		'04-AZLJB', '04-AZLJD', '04-AZLJF', \
   		'04-AZLJH', '04-BF0LX', '04-BF0LZ', \
   		'04-BF0M1', '04-BF0M3', '04-BF0M7', \
   		'04-BF0M9', '04-BF0MO', '04-BKLND', \
   		'04-BKLNN', '04-CYI2Z', '04-CYI32', \
   		'04-CYI34'))
   	USE INDEX HINTS = TRUE
   [Export LOVs_Child]
   	TYPE = EXPORT
   	BATCH = 4
   	TABLE = EIM_LST_OF_VAL
   	EXPORT MATCHES = (TYPE <> 'LOV_TYPE' and \
   		PAR_ROW_ID IS NOT NULL and \
   		ROW_ID NOT IN ('0-6DCE7', '04-AQ79M', \
   		'04-AQ79O', '04-AQ79Q'))
   	USE INDEX HINTS = TRUE
   

   Note: The ROW_ID values for LOVs with NAME greater than 30 characters must be included in the "ROW_ID NOT IN" clause of the [Export LOVs_Parent] and [Export LOVs_Child] sections. For more information, see 477627.1 (Doc ID) on My Oracle Support. This document was previously published as Siebel Technical Note 925.

2. Run the SQL statement that follows to populate the EIM_LST_OF_VAL.PAR_BI and other EIM_LST_OF_VAL.*_BU interface columns.

   Note: This SQL statement can be found in < siebel server root >\Admin\eim_export_lookup_bu_name.sql. Locate the SQL for EIM_LST_OF_VAL.

   update EIM_LST_OF_VAL IT
      set IT.BITMAP_LIT_BU = (select min(OI.NAME) from S_BU OI where OI.ROW_ID = 
   IT.BITMAP_LIT_BI)
      ,   IT.LOV_BU = (select min(OI.NAME) from S_BU OI where OI.ROW_ID = IT.LOV_BI)
      ,   IT.LOV_VIS_BU = (select min(OI.NAME) from S_BU OI where OI.ROW_ID = 
   IT.LOV_VIS_BI)
      ,   IT.PAR_BU = (select min(OI.NAME) from S_BU OI where OI.ROW_ID = IT.PAR_BI);
   

3. Make sure the target environment’s EIM_LST_OF_VAL interface table is empty, then move the exported data from the source environment’s EIM_LST_OF_VAL interface table to the target environment’s EIM_LST_OF_VAL interface table.

4. At the target environment, verify the existence of the three list of values records that follow before proceeding to Step 5.

   Type	Display Value	Replication Level
   LOV_TYPE	LOV_TYPE	All
   LOV_TYPE	REPLICATION_LEVEL	All
   REPLICATION_LEVEL	All	All

   1. If these records do not exist, then create them in the Siebel client by going to the Administration - Application screen, then the LOV Explorer view.

5. Run the following SQL at the target environment’s database:

   UPDATE EIM_LST_OF_VAL A
      SET A.IF_ROW_BATCH_NUM = 5
      WHERE NOT EXISTS (SELECT 'x'
      FROM EIM_LST_OF_VAL B
      WHERE B.LOV_TYPE = A.PAR_TYPE
      AND B.LOV_VAL = A.PAR_VAL
      AND B.LOV_LANG_ID = A.PAR_LANG_ID
      AND (B.LOV_SUB_TYPE = A.PAR_SUB_TYPE
      OR (B.LOV_SUB_TYPE IS NULL
      AND A.PAR_SUB_TYPE IS NULL))
      AND B.LOV_BU = A.PAR_BU
      AND B.IF_ROW_BATCH_NUM <= 3)
      AND A.IF_ROW_BATCH_NUM = 4;
   

6. If the SQL listed in Step 5 has updated zero records, proceed to Step 7 . Otherwise, run the following SQL at the target environment’s database and repeat Step 6 until the SQL has updated zero records:

   UPDATE EIM_LST_OF_VAL A
      SET A.IF_ROW_BATCH_NUM = <see Note A row in the following table>
      WHERE NOT EXISTS (SELECT 'x'
      FROM EIM_LST_OF_VAL B
      WHERE B.LOV_TYPE = A.PAR_TYPE
      AND B.LOV_VAL = A.PAR_VAL
      AND B.LOV_LANG_ID = A.PAR_LANG_ID
      AND (B.LOV_SUB_TYPE = A.PAR_SUB_TYPE
      OR (B.LOV_SUB_TYPE IS NULL
      AND A.PAR_SUB_TYPE IS NULL))
      AND B.LOV_BU = A.PAR_BU
      AND B.IF_ROW_BATCH_NUM = <see Note B row in the following table>)
      AND A.IF_ROW_BATCH_NUM = <see Note C row in the following table>;
   

   Note	Value
   A	Next new batch number; that is, 6 for the first time you run, 7 for the second time you run, and so on.
   B	Last batch number; that is, 4 for the first time you run, 5 for the second time you run, and so on.
   C	Last batch number; that is, 5 for the first time you run, 6 for the second time you run, and so on.

7. Run the following SQL at the target environment’s database:

   UPDATE EIM_LST_OF_VAL
   SET IF_ROW_BATCH_NUM = <next new batch number>
   WHERE LOV_VIS_BU IS NOT NULL;
   

8. Run an EIM import task at the target environment using the following parameters:

   [Siebel Interface Manager]
   	USER NAME = "SADMIN"
   	PASSWORD = "SADMIN"
   	PROCESS = Import LOV
   [Import LOV]
   	TYPE = IMPORT
   	BATCH = 1-<last batch number as specified in step 7>
   	TABLE = EIM_LST_OF_VAL
   	USE INDEX HINTS = TRUE
   

9. Migrate the S_LOV_REL rows using the EIM_LOV_REL interface table.

EIM Merge Process Example

This section provides an example you might find useful when merging custom columns.

Example of Running a Merge with Custom Columns

In this example, you run a merge that includes two account records with the same location (LOC), and a string of information in the old record that must be copied into the new record. The two records have different values for Name because the account had a name change. The information contained in the records that result from the merge is as follows:

Record	LOC	Name	X_CUSTOM_COLUMN
Old record	1	A	top-tier account
Remaining	1	B	None

When these two accounts are merged, the information in the old record’s custom column is lost and the custom column in the remaining record appears blank.

Note: EIM behavior, whether executed from the GUI or through an EIM run, does not merge data in the base record. It simply repoints the foreign keys in the dependent child records. This applies to all columns in the base table. This could lead to unintended data loss in an extension column.

EIM Delete Process Examples

This section provides usage examples that can be applied to your running of delete processes.

Example: Using DELETE MATCHES to Delete Data from S_PARTY Extension Tables

If the EIM table’s target table is S_PARTY:

The syntax is as follows:

DELETE MATCHES = S_PARTY, [...criteria...]
DELETE MATCHES = [non-target base tables name of Siebel Extension type], 
[...criteria...]

In this example, you want to delete an existing account. This account’s data is as follows:

S_PARTY:  PARTY_TYPE_CD='Organization', PARTY_UID='1-28XIF' 
S_ORG_EXT: LOC='San Mateo', NAME='TEST', BU_ID=' 0-R9NH"

If you would like to apply criteria against the S_PARTY table, you can use the following session in the .IFB file:

[Delete Account]
TYPE = DELETE 
BATCH = 100
TABLE = EIM_ACCOUNT
DELETE MATCHES = S_PARTY, (PARTY_UID = '1-28XIF')

Or if you would like to apply criteria against the S_ORG_EXT table, you can use the following session in the .IFB file:

[Delete Account]
TYPE = DELETE 
BATCH = 100
TABLE = EIM_ACCOUNT
DELETE MATCHES = S_ORG_EXT, (NAME = 'TEST')

Both methods achieve the same result. But in this example, it is easier to use criteria against S_ORG_EXT, since you know which account you want to delete.

Note: When S_PARTY is the target base table, you cannot use the EIM table name or neglect the target base table name in DELETE MATCHES expressions.

Example: Using DELETE MATCHES to Delete Data from non-S_PARTY Extension Tables

If the EIM table’s target table is not S_PARTY:

DELETE MATCHES = [EIM table name], [...criteria...]
DELETE MATCHES = [target base table name], [...criteria...]
DELETE MATCHES = [...criteria...

For example, if you want to delete all activities created by employee SADMIN, you go to the S_EVT_ACT table and find all the records with the following:

	OWNER_LOGIN='SADMIN'

You can use the following session in your .IFB file:

	[Delete Activity]
	TYPE = DELETE 
	BATCH = 100
	TABLE = EIM_ACTIVITY
	DELETE MATCHES = <Table>, (OWNER_LOGIN = 'SADMIN')

<Table> can be replaced by EIM_ACTIVITY or S_EVT_ACT, or it can be remain empty.

Example of Using DELETE EXACT

The DELETE EXACT parameter is used to delete rows in a Siebel base table with user key values specified in the corresponding EIM table. In this case, the corresponding EIM table has to be populated.

In this example, you want to delete an existing account. This account’s user key data is as follows:

S_PARTY:  PARTY_TYPE_CD='Organization', PARTY_UID='1-28XIF' 
S_ORG_EXT: LOC='San Mateo', NAME='TEST', BU_ID=' 0-R9NH"

To delete an existing account

1. Choose the EIM_ACCOUNT table and populate this table as follows:

   EIM_ACCOUNT.LOC ='San Mateo'
   	EIM_ACCOUNT.NAME ='TEST' 
   	EIM_ACCOUNT.ACCNT_BU ='Default Organization' (corresponding to BU_ID=' 0-R9NH")
   

2. Populate the other required columns of the EIM_ACCOUNT table, such as IF_ROW_BATCH_NUM.

3. Run the EIM delete process.

   The following is an excerpt from a sample .IFB file:

   [Delete Account]
   	TYPE = DELETE 
   	BATCH = 300
   	TABLE = EIM_ACCOUNT
   	ONLY BASE TABLES = S_ORG_EXT
   	DELETE EXACT=TRUE
   

To delete an existing account using S_PARTY’s user key to populate the EIM_ACCOUNT table

1. Choose the EIM_ACCOUNT table and populate this table as follows:

   EIM_ACCOUNT : PARTY_TYPE_CD='Organization' and PARTY_UID='1-28XIF'

2. Populate the other required columns of the EIM_ACCOUNT table, such as IF_ROW_BATCH_NUM.

3. Run the EIM delete process.

The following is an excerpt from a sample .IFB file:

[Delete Account]
	TYPE = DELETE 
	BATCH = 300
	TABLE = EIM_ACCOUNT
	ONLY BASE TABLES = S_PARTY
	DELETE EXACT=TRUE

The examples shown in the previous tasks achieve the same result.

Note the following about using DELETE EXACT:

• In the .IFB file, you must specify ONLY BASE TABLES, so that only this data will be deleted.

• Only one base table can be specified in the ONLY BASE TABLES parameter. Otherwise, unexpected SQL statements will be generated

• If you want to delete data in two or more tables, you must specify two or more sessions in your .IFB file, since you can only specify one table in each session.

The following are the differences between DELETE EXACT and DELETE MATCHES:

• DELETE MATCHES does not require data population of an EIM table, while DELETE EXACT does. So DELETE MATCHES is easier to use when the deleting criterion is simple.

• DELETE MATCHES does not work well with complicated deleting criterion, because you do not get the chance to check whether you are mistakenly deleting the correct data. With DELETE EXACT, you can always check the data in the EIM table before you start the EIM delete process.

• DELETE MATCHES can only be used when the deleting criterion is against a target base table (or against its extension table if the target base table is S_PARTY), and when only one base table is involved. However, with DELETE EXACT, you can always use EIM or SQL statements to export the user key data from the base table to the EIM table, and then cleanse the data. As long as the corresponding user key columns in the EIM table can be populated, DELETE EXACT can be used to delete the data in the base table.

To find the target base table of an EIM table

1. In Siebel Tools, navigate to EIM Interface Table control, and query the EIM table name.

2. Check the Target Table property to find the target base table name.

Example of Deleting Specific Positions from Accounts

To delete specific positions from an account, you must populate the interface table EIM_ACCOUNT with an SQL script in addition to making modifications to the .IFB file. This is because DELETE MATCHES does not work for nonbase tables.

You can use the following sample .IFB file:

[Siebel Interface Manager]
    USER NAME = "SADMIN"
    PASSWORD = "SADMIN"
    PROCESS = DELETE
[DELETE]
    TYPE = SHELL
    INCLUDE = "Delete Accounts Main"
[Delete Accounts Main]
      TYPE = DELETE
      BATCH = 1
      TABLE = EIM_ACCOUNT
      ONLY BASE TABLES = S_ACCNT_POSTN
      DELETE EXACT = TRUE

Examples of Resolving Foreign Keys

The examples in this section illustrate ways of dealing with foreign keys that do not resolve to existing values.

Example 1: Resolving the Foreign Key Value

EIM reports the low-severity error that follows when the foreign key value in the base table does not match the value in the EIM table.

Note: This error example is based on the Siebel version 7.7 data model.

EIM_SRV_REQ
------------
CAT_CTLG_BI
CAT_CTLG_TYPE_CD
CAT_CTLG_NAME
CAT_CTLG_VER_NUM
CAT_CTLG_CAT_ENDDT
CAT_CTLG_CAT_NAME
Base table:
S_CTLG_CAT_SR
-----------
CTLG_CAT_ID

This is a foreign key value in the base table and the values in the interface table did not resolve to existing values. Verify that the IF columns correspond to existing base table rows. This failure caused the rows to be eliminated from further processing for this secondary base table. However, processing of the rows WILL continue for other destination base tables.

To resolve the foreign key value, you must find the user key columns in the foreign key table. Based on multiple columns, user keys are used to uniquely identify rows within tables for EIM processing.

The following information lists the user key attributes and base table columns for the EIM_SRV_REQ interface column discussed in this example.

EIM_SRV_REQ Column	User Key Attribute	Base Table Column
CAT_CTLG_BU	CTLG_ID/BU_ID/NAME	S_BU.NAME
CAT_CTLG_TYPE_CD	CTLG_ID/CTLG_TYPE_CD	S_CTLG.CTLG_TYPE_CD
CAT_CTLG_NAME	CTLG_ID/NAME	S_CTLG.NAME
CAT_CTLG_VER_NUM	CTLG_ID/VERSION_NUM	S_CTLG.VERSION_NUM
CAT_CTLG_CAT_ENDDT	EFF_END_DT	S_CTLG_CAT.EFF_END_DT
CAT_CTLG_CAT_NAME	NAME	S_CTLG_CAT.NAME

The following example task shows how the user key plays a role to identify the base column for corresponding EIM columns for the described scenario.

To resolve the foreign key value

1. Identify the foreign key table to which S_CTLG_CAT_SR.CTLG_CAT_ID points.

   1. In Siebel Tools, in the Object Explorer list, go to the Table object and query for the S_CTLG_CAT_SR table.

   2. Navigate to the Column object and query for the CTLG_CAT_ID column.

   3. Verify that the foreign key table value is S_CTLG_CAT.

2. Find the user key columns defined in the S_CTLG_CAT table.

   1. In Siebel Tools, in the Object Explorer list, go to the Table object and query for the S_CTLG_CAT table.

   2. Navigate to the User Key object and select the U1 index (S_CTLG_CAT_U1).

   3. Navigate to the User Key Column object and verify that the User Key columns for S_CTLG_CAT are CTLG_ID (FK), EFF_END_DT, and NAME.

3. In Siebel Tools, identify the foreign key table to which S_CTLG_CAT.CTLG_ID points: S_CTLG

4. Find the user key columns defined in the S_CTLG table: BU_ID (FK), CTLG_TYPE_CD, NAME, and VERSION_NUM

5. Identify the foreign key table to which S_CTLG.BU_ID points: S_BU

6. Find the user key columns defined in the S_BU table using Siebel Tools: NAME

7. Based on the previous results, populate the following interface columns as listed in the following table to resolve the S_CTLG_CAT_SR.CTLG_CAT.ID foreign key.

   Interface Column Name	Instructions
   CAT_CTLG_BU	Populate with S_BU.NAME value from Step 6.
   CAT_CTLG_TYPE_CD	Populate with S_CTLG.CTLG_TYPE_CD value from Step 4.
   CAT_CTLG_NAME	Populate with S_CTLG.NAME value from Step 4.
   CAT_CTLG_VER_NUM	Populate with S_CTLG.VERSION_NUM value from Step 4.
   CAT_CTLG_CAT_ENDDT	Populate with S_CTLG_CAT.EFF_END_DT value from Step 2c.
   CAT_CTLG_CAT_NAME	Populate with S_CTLG_CAT.NAME value from Step 2.

Example 2: Resolving the Foreign Key for Position Division

The table S_ORG_EXT is used to store the Account records and the internal Division records. The user key of S_ORG_EXT consists of the columns NAME, LOC, and BU_ID. For Division records, BU_ID always references Default Organization.

During an EIM run, in order to identify the foreign key S_POSTN.OU_ID, EIM needs information about the user key columns of S_ORG_EXT. The foreign key S_POSTN.OU_ID points to Division records in S_ORG_EXT. So the division’s NAME, LOC, and Default Organization should be used to resolve the OU_ID.

Note: S_POSTN also has a foreign key BU_ID which may or may not reference Default Organization. This BU_ID is not to be confused with the BU_ID in the user key of S_ORG_EXT. Do not use it together with the division’s NAME and LOC to resolve S_POSTN.OU_ID; doing this can result in failure if the BU_ID references organizations other than Default Organization.

Example 3: Resolving the Foreign Key Using a Special User Key

A typical example of using a special user key (rather than the normal U1 user key) to resolve foreign keys is the use of the special user key S_ADDR_PER:Communications in the resolution of foreign keys to S_ADDR_PER. This special user key contains only the column ADDR_NAME, in contrast to (ADDR_NAME, PER_ID) in the U1 user key.

The mapping of S_ORDER.BL_ADDR_ID in EIM_ORDER, for example, uses the special user key S_ADDR_PER:Communications instead of the U1 user key. Mappings of all foreign keys to S_ADDR_PER use this special user key instead of the U1 user key.

Other Examples

The following examples in this topic illustrate various ways of working with EIM: setting explicit primary mappings, improving EIM performance, defining foreign key column values, implementing a multi-org hierarchy, adding a position to a party table, and using the EIM_ASSET interface table.

Example of Setting Explicit Primary Mappings

After importing a Contact using EIM_CONTACT, you can use EIM_CONTACT3 to import the Contact’s personal email addresses into S_PER_COMM_ADDR. You can explicitly set the primary email address by populating the primary flag column CON_PR_EMAIL_ADDR with Y.

The following table shows an example of setting the primary email address for Contact “CON100” to “[e-mail].”

PARTY_UID	PARTY_TYPE_CD	CON_PERSON_UID	CON_PRIV_FLG	CON_BU	ADDR_COMMMEDIUM_CD	ADDR_ADDR	CON_PR_EMAIL_ADDR
CON100	Person	CON100	N	Default Organization	Email	[e-mail]	Not applicable
CON100	Person	CON100	N	Default Organization	Email	[e-mail]	Y
CON100	Person	CON100	N	Default Organization	Email	[e-mail]	Not applicable

If an explicit primary mapping is not used or not used properly—such as no address or more than one address flagged as the primary email address—then EIM ignores this explicit primary mapping and sets the primary implicitly.

Example of Setting Explicit Primary Mappings for Many-to-Many Relationships

Example of Setting Explicit Primary Mappings explains how to set an explicit primary for a one-to-many relationship. When setting a primary key for a many-to-many relationship, such as the relationship between Opportunities and Contacts, there is an intersection table to consider.

As an example, you can work with the primary S_OPTY.PR_CON_ID. First you import into S_CONTACT using EIM_CONTACT. Then you use EIM_OPTY to import into S_OPTY and the intersection table S_OPTY_CON, and explicitly set the primary S_OPTY.PR_CON_ID during this process.

The column definitions for one-to-many primaries are different from those of many-to-many primaries. In the case of a one-to-many primary, such as S_CONTACT.PR_EMAIL_ADDR_ID, the foreign key table and the primary child table are both defined as S_PER_COMM_ADDR, and the primary intersection table is empty. In the case of a many-to-many primary, such as S_OPTY.PR_CON_ID, the foreign key table is S_CONTACT, and both the primary child table and the primary intersection table are defined as S_OPTY_CON. The explicit primary mapping for S_OPTY.PR_CON_ID is under the table mapping of its primary child table, that is, S_OPTY_CON. It could be easy to mistake S_CONTACT as the primary child table for S_OPTY.PR_CON_ID and this could lead you to look for an explicit primary mapping. This explicit primary mapping would not be found, however, because S_CONTACT is not mapped in EIM_OPTY.

Example of Creating Mappings for Extension Columns

For an example of how to map extension columns, see the section on the EIM Table Mapping Wizard in Configuring Siebel Business Applications.

Example of Improving Performance by Dropping Indexes

Often, especially for initial EIM loads, you can improve EIM performance by determining that there are indexes present which are not being used for a particular EIM process. By pinpointing the unnecessary indexes, and by dropping them for the duration of an EIM run, you can achieve performance improvements. For an example of this, see Siebel Performance Tuning Guide.

Foreign Key Column Values: NO MATCH ROW ID versus NULL versus a Valid ROW_ID

There are three possible values that EIM can define for primary columns (foreign key columns) when it processes a batch:

• NO MATCH ROW ID. EIM sets the foreign key columns to NO MATCH ROW ID if the primary value cannot be found when EIM processes Step 10 in EIM Import Process. EIM does this because the primary key is missing in the linked table. The following are special considerations regarding NO MATCH ROW ID:

  • S_CONTACT. The export function will update the BU_ID on the S_CONTACT table to NO MATCH ROW ID when there is no record existing in the S_CONTACT_BU table for a given contact. To avoid this, every contact should have a corresponding record in the S_CONTACT_BU table.

  • S_PRI_LST_BU. The S_PRI_LST_BU table must be loaded to avoid having the UI set S_PRI_LST.BU_ID to NO MATCH ROW ID.

• NULL. If the foreign key columns allow a NULL value in the parent table, EIM carries the NULL value.

• A valid ROW_ID. If a valid ROW_ID is not defined, EIM uses the value in the primary column to determine the ROW_ID.

Example of Using the NUM_IFTABLE_LOAD_CUTOFF Parameter

When the NUM_IFTABLE_LOAD_CUTOFF parameter is enabled, EIM loads all schema mappings if the value is less than the number of EIM tables used in the run process. To enable this parameter, set the value to a positive number that is less than the number of EIM tables used in the run process. For example, if the EIM process is using one EIM table, then the setting should be NUM_IFTABLE_LOAD_CUTOFF = 0.

When this parameter is disabled, EIM loads only mappings for the EIM tables used in the run process. This speeds up the dictionary loading process in EIM. To disable this parameter, set the value to -1.

Note: NUM_IFTABLE_LOAD_CUTOFF is disabled by default.

EIM does not necessarily look at all of the EIM tables in the IFB file. EIM counts only the number of EIM tables being used in the running process.

For example, in the .IFB file that follows, there are three EIM tables: EIM_ACCOUNT, EIM_CONTACT, and EIM_OPTY. But there are only two EIM tables (EIM_ACCOUNT, EIM_CONTACT) for the process to be run (Import Objects). So with a NUM_IFTABLE_LOAD_CUTOFF value of 2, EIM does not load all of the schema mappings. If you want EIM to load all of the schema mappings in this example, set the NUM_IFTABLE_LOAD_CUTOFF value to 1 (or 0).

By setting the parameter to 2 in this example, you are disabling it because the number is equal to, not less than, the number of EIM tables used in the run process.

Sample .IFB file:

[Siebel Interface Manager]
   PROCESS = Import Objects
[Import Objects]
   TYPE = SHELL
   INCLUDE = Import Accounts
   INCLUDE = Import Contacts
[Import Accounts]
   TYPE = IMPORT
   BATCH = 100
   TABLE = EIM_ACCOUNT
[Import Contacts]
   TYPE = IMPORT
   BATCH = 100
   TABLE = EIM_CONTACT
[Export Opty]
   TYPE = Export
   BATCH = 100
   TABLE = EIM_OPTY

Example: Transaction Logging with Row-by-row Processing versus Set-based Processing

Transaction logging is enabled and disabled from within the Remote System Preferences view in the Administration - Siebel Remote screen. The preference is called Enable Transaction Logging. You can also adjust the transaction logging system preference setting by changing the LOG TRANSACTIONS parameter in the EIM configuration file. For more information, see Process Section Parameters Generic to All EIM Processes.

LOG TRANSACTIONS and SET BASED LOGGING Parameters

EIM performs row-by-row (RBR) transaction logging when LOG TRANSACTIONS is set to TRUE and SET BASED LOGGING is set to FALSE in the .IFB file. In row-by-row logging mode, EIM fetches all the data to the client.

Most of the time, SET BASED LOGGING is not explicitly set. When SET BASED LOGGING is not explicitly set, the Enable Transaction Logging system preference in the Administration - Siebel Remote screen is used to determine the processing method.

When Enable Transaction Logging is disabled, all operations (insert, update, and delete) are performed in set-based mode. If you explicitly set LOG TRANSACTIONS = FALSE in the .IFB file, then EIM does not log any transactions into the Master Transaction Log table.

When Enable Transaction Logging is enabled, all inserts and deletes are performed in set-based mode, while updates are performed in RBR mode. When Enable Transaction Logging is checked, EIM logs transactions into either the .DX files or the S_DOCK_TXN_LOG table, depending on the setting for LOG TRANSACTIONS TO FILE in the .IFB file.

When SET BASED LOGGING is explicitly set, EIM uses the value of this parameter to determine the processing mode. When SET BASED LOGGING is TRUE, all operations (insert, update, delete) are performed in set-based mode. When SET BASED LOGGING is FALSE, all operations are performed in RBR mode. For import and delete processes, it is not recommended that the SET BASED LOGGING parameter be set to TRUE because in most cases, there is no reason to set this parameter explicitly. For merge processes, SET BASED LOGGING must be set to FALSE for transaction logging to work properly.

To log every transaction separately, EIM changes its operation mode to RBR.

Logging Locations for LOG TRANSACTIONS and LOG TRANSACTIONS TO FILE

With RBR processing, data is logged according to one of three scenarios:

1. EIM logs transactions into S_DOCK_TXN_LOG table or .DX files if:

   • Remote System Preference Enable Transaction Logging is checked

     • LOG TRANSACTIONS = TRUE in the .IFB file (default)

     • LOG TRANSACTIONS TO FILE = TRUE in the .IFB file (default)

     If LOG TRANSACTIONS = TRUE and LOG TRANSACTIONS TO FILE = TRUE (which are the default values), EIM logs a transaction into the .DX files, which are stored in <SiebelFileSystem\eim> folder. EIM creates the "marker" transaction in the S_DOCK_TXN_LOG table.

     S_DOCK_TXN_LOG.LOG_DATA1 (or LOG_DATA_2, 3, 4) stores the name and the location of the .DX file as in the following example:

     N128*d:\15051sia\FS\eim\1-CP-1.dx

     In this example, 1-CP-1.dx is the name of the .DX file and it is located in the \\15051sia\FS\eim folder.

     Once the data is created in the .DX file and the marker transactions have been created in the S_DOCK_TXN_LOG table, Transaction Processor (TxnProc) captures the .DX files from the \eim folder and brings them into the TxnProc folder for Transaction Router (TxnRouter) to process.

2. EIM logs transactions into the S_DOCK_TXN_LOG table, and eventually, TxnProc or TxnMerge creates the .DX file in the <SiebSrvr\Docking\TxnProc> folder if:

   • LOG TRANSACTIONS = TRUE in the .IFB file (default)

   • LOG TRANSACTIONS TO FILE = FALSE in the .IFB file

3. EIM does not log any transactions into the S_DOCK_TXN_LOG table or in the .DX file if:

   • LOG TRANSACTIONS = FALSE in the .IFB file

     This is the case regardless of what value is defined for LOG TRANSACTIONS TO FILE.

   The LOG TRANSACTIONS parameter is explicitly used to control whether changes made by EIM to the Siebel database should be visible to remote clients (by logging transactions for use by the Docking Manager, which synchronizes the Siebel database with remote clients).

When to Use Row-by-Row Processing

For import and delete processes, you should let EIM determine which mode to use. EIM will use the method with the best performance for the functionality requested. For initial data loading, you can disable transaction logging for improved performance (EIM will use set-based mode for all operations). For ongoing operations with Mobile Web Clients, transaction logging should be enabled (EIM will choose set-based logging for inserts and deletes, and RBR for updates).

For merge processes with transaction logging enabled, you must explicitly set EIM to run in RBR mode in order for transaction logging to work properly.

The following are examples of cases when RBR logging should be used:

• Running an EIM import task using the COMMIT OPERATIONS parameter.

  Note: COMMIT OPERATIONS is useful only for RBR logging.

• Running an EIM merge task. To enable transaction logging for an EIM merge process, the EIM merge process runs in ongoing (row-by-row) mode.

Advantages and Disadvantages of Using RBR Logging

Advantages of RBR logging include the following:

• Since EIM inserts all the information (data as well as column info) into the S_DOCK_TXN_LOG table, in cases for which mobile clients bring about synchronization problems, RBR logging is beneficial in making troubleshooting easier.

• The row-by-row mode is required for logging update transactions. If the SET BASED LOGGING parameter is not set, then EIM runs in RBR for update operations when transaction logging is enabled. RBR is also required for merge processes when transaction logging is enabled.

  Note: When running merge processes with transaction logging, SET BASED LOGGING = FALSE is required. If EIM performs a merge with set-based logging instead of RBR, transactions are not written to S_DOCK_TXN_LOG even though TRANSACTION LOGGING is set to TRUE. The merge works correctly, but remote clients cannot get the transactions and are out of synch as a result.

A disadvantage of RBR logging is that RBR logging affects performance, especially with large imports and deletes.

Example of Implementing a Multi-Organization Hierarchy

If you use multi-org, this means that a single record is shared across multiple organizations. For overview information on multi-org, see the section on access control in Siebel Security Guide.

In this example, you are adding a new organization to convert a single-org to a multi-org. The process of converting a single-org to a multi-org involves adding the additional organization and its related structure, adding positions, and then associating the data to the new organization.

Note: Some data, such as Accounts, has a many-to-many relationship to organizations, while other data, such as Contacts and Service Requests do not.

To convert from single-org to multi-org

1. Add a new organization (New Org) into the Organization table.

2. Assign records to the new organization.

   You can assign records through the GUI or using EIM.

   For example, assign an Employee record (Emp1). The Employee records are stored in the S_CONTACT table. There is a many-to-many relationship between the employee and the organization, so the intersection table S_CONTACT_BU holds the relationship between the organization and the employee.

   You add a new record in the S_CONTACT_BU intersection table to hold the relationship between New Org and Emp1. Now Emp1 is available to both the original organization and New Org.

3. Verify that you can see the record in both organizations.

Example of Adding a Position to a Party Table

This example shows how positions are added to party tables, such as Account, Contact, and Employee. You are adding positions to the Account table.

You can use the EIM_ACCOUNT table to populate S_ACCNT_POSTN, which is an intersection table between Accounts and Position.

In the S_ACCNT_POSTN table, you provide information about the position you are trying to add (POSITION_ID) and the account you are trying to associate with the position (OU_EXT_ID).

In the EIM_ACCOUNT table, you provide information about the account.

To populate the EIM table, you must always include the target base table: in this case, S_PARTY. Since EIM_ACCOUNT is for account information, S_PARTY should also be filled with account information. So you set the S_PARTY.PARTY_TYPE_CD = 'Organization' since Account belongs to the Organization type. (PARTY_TYPE_CD = ‘Person’ is only used for Contact, User, Employee, or Partner.)

The .IFB file looks like this:

[Add Position]
      TYPE = IMPORT
      BATCH = 2002
      TABLE = EIM_ACCOUNT
      ONLY BASE TABLES = S_PARTY, S_ACCNT_POSTN
      INSERT ROWS = S_PARTY, FALSE
      INSERT ROWS = S_ORG_EXT, FALSE
      INSERT ROWS = S_ACCNT_POSTN, TRUE
      UPDATE ROWS = S_ACCNT_POSTN, TRUE

Example of Using the EIM_ASSET Interface Table

The following information shows an example of how to populate the EIM_ASSET interface table for an import process in order to properly display product and part number information in the Oracle’s Siebel application's Asset Management - Assets View.

Field to Populate	Description
OWNER_ACCNT_BU	The organization of which the account is part.
OWNER_ACCNT_LOC	The account site for the related asset.
OWNER_ACCNT_NAME	The account’s actual name.
AST_ASSET_NUM	The product’s serial number.
AST_PROD_BU	Can be specified as “Default Organization” if necessary.
AST_PROD_NAME	The product’s actual name.