Siebel EIM Tables

This chapter discusses Siebel EIM tables (also known as interface tables) and how EIM uses them. Siebel EIM tables are intermediate database tables that act as a staging area between the base tables in the Siebel database and other databases. This chapter is organized into the following sections:

EIM Tables Overview

Siebel EIM tables are intermediate database tables that act as a staging area between the base tables in the Siebel database and other databases. EIM tables are designed to be simple and straightforward so they can be loaded or read by way of external programs. This section provides an overview of how EIM works with these EIM tables and how table names are derived.

Preparing EIM Tables for Merge, Update, or Import Processes

Before EIM can be used in a merge, update, or import process, a Siebel administrator or a database administrator must populate the EIM tables with data, using any method supported by the database. A Siebel administrator then invokes EIM to process this data. EIM makes multiple passes through the tables to complete the specified process.

Each EIM table usually supports a group of base tables that can be imported or exported in a single batch. Base tables are the tables within the Siebel database that contain your data. Base tables are the final destination of data imported into the Siebel database and the source of data exported from the Siebel database.

Note: If the Siebel administrator is importing into base tables that use the UTC (Universal Time Coordinated) time scale, the Siebel administrator or a database administrator must convert the local time in the data into UTC before loading data into the EIM tables.

EIM Table Naming Conventions

EIM tables in the Siebel database use a three-part naming convention; the syntax is: PREFIX_NAME_SUFFIX. These three parts are described as follows:

PREFIX. All interface tables used by EIM have the prefix EIM_ (such as EIM_ACCOUNT). These EIM tables support Organizations, so they can be used for all EIM processes.

Note: Previous versions of EIM used a different set of EIM (interface) tables, identified by the prefix S_ and the suffix _IF. These tables still appear in the Siebel database, but are inactive. These tables will not be included in the Siebel database in future versions. For help activating these tables temporarily, contact your Oracle sales representative for Oracle Advanced Customer Services to request assistance from Oracle's Application Expert Services.
NAME. A unique table name that is generally composed of an abbreviation of an entity type name. If more than one EIM table is required to fully support an entity, a sequential number may be added to the name of each table after the first one.
SUFFIX. A supertype name may be followed by a suffix that indicates the types of data supported by the EIM table or to distinguish it as an EIM table.

For more information, see Viewing EIM Table Mappings to Base Tables.

EIM Table Columns

Running EIM is an iterative process, with each step accomplishing specific tasks and moving toward successful completion of the entire process. To process on a row-by-row basis, EIM uses several columns common to every EIM table. These columns are described in this section.

Several columns are mandatory. Others are conditionally mandatory, depending on the conditions of your import. To determine mandatory columns, use Siebel Tools to view each column in an EIM table and the EIM table’s target base table columns.

By following the recommended import sequence, you make sure that the appropriate data dependencies are established.

Note: For import and merge processes, you must populate the ROW_ID, IF_ROW_STAT, and IF_ROW_BATCH_NUM columns in the EIM tables. This also must be done for delete processes when you run DELETE EXACT. For merge processes, you also need to populate the IF_ROW_MERGE_ID column. Do not populate these required columns with spaces because a space does not equal a NULL value.

Mandatory Columns for EIM Processing

ROW_ID. For an EIM table row to be eligible for processing, you must initialize its ROW_ID. The ROW_ID, in combination with the value of IF_ROW_BATCH_NUM, must yield a unique value. The ROW_ID values in the EIM tables are not the ROW_ID values that are assigned to the row when it is loaded into the base table. An EIM-generated ROW_ID has a ##-###-### format. A regular ROW_ID that is assigned to the row has a #-## format.

IF_ROW_BATCH_NUM. You must set the values in this column to the same integer, greater than or equal to 0, as an identifying number for all rows to be processed as a batch. The maximum value is 2147483647. Use this column as the first key of any new indexes created on an EIM table.

IF_ROW_MERGE_ID. You can set this column to one of two values:

NULL. This value identifies the remaining record, or merged-into-row.
ROW_ID. This value identifies the ROW_ID number in the EIM table where the row will be merged.

Note: This value is the ROW_ID of records in the EIM table, not the base tables.

IF_ROW_STAT. EIM updates this column after processing the row to indicate the status of the record. The IF_ROW_STAT column is not used by EIM when determining which rows to process. When populating the EIM tables, you can set this column to any value except NULL. You can initially set this value to FOR_IMPORT to indicate that the row has not been imported. After processing, if certain rows were not imported due to a data error, you should change:

IF_ROW_BATCH_NUM value for the rows that require reimporting
BATCH line in the configuration file

If EIM updates this column to NOT_ALLOWED after processing a row, EIM has attempted to insert a new row but the action is not allowed. In such cases, the INSERT ROWS parameter may have been set to FALSE.

IF_ROW_STAT_NUM. After processing, this column contains a zero (0) if a row was successfully processed to completion. If processing failed, this column contains the pass number where the pass failed.

Temporary columns. EIM uses temporary columns to manipulate data during processing. For example, EIM might store the ROW_ID value for a Siebel base table in a temporary column. These column names begin with T_ and indicate the table or column for which they are used. Because EIM uses these columns internally during processing, do not manipulate these columns in the EIM tables.

For detailed information about each EIM table, generate a table mapping report, see Generating EIM Table Mapping Reports.

File Attachment Columns

Three EIM table columns must be populated in order to import file attachments. The following information describes these columns and uses the attachment file budget99.doc as an example.

Column	Description	Example
FILE_NAME	This column requires the root filename of the file attachment.	FILE_NAME=”budget99”
FILE_EXT	This column requires the extension type of the file attachment (DOC, XLS, or TXT).	FILE_EXT="doc"
FILE_SRC_TYPE	This column requires the value "FILE" or the rows cannot be imported.	FILE_SRC_TYPE="FILE"

You can also use these columns to define hyperlinks, as shown in the following information.

Column	Setting
FILE_NAME	Set to actual URL
FILE_EXT	NULL
FILE_SRC_TYPE	‘URL’

Organization Columns

The EIM_ type interface tables use the xxx_BU/xxx_BI column pairs to map organizations. For example, the CON_BU/CON_BI column in the EIM_CONTACT interface table is mapped to the BU_ID column in the S_CONTACT base table.

In order for organizations to be resolved properly, you need to populate the xxx_BU column with the organization name and leave the xxx_BI column empty. Do not populate the xxx_BU column with the organization ROW_ID. EIM looks up the ROW_ID for the organization in xxx_BU and puts it in the corresponding xxx_BI column.

EIM Table and Column Mappings

EIM uses EIM table mappings to map columns from EIM tables to Siebel base tables. Siebel predefined EIM mappings are fixed and cannot be remapped. Using Siebel Tools, you can:

View EIM table mappings to Siebel base tables
View interface column mappings to Siebel base table columns
View Siebel base table mappings to EIM tables
Print EIM table reports

Some base tables may not be mapped to a corresponding EIM table. In such cases, use Siebel VB to load data into these base tables and create a service request (SR) on My Oracle Support regarding the missing mapping. EIM does not interfere with Siebel VB code because Siebel VB works at the business object layer, and EIM works at the data object layer. You can also use the EIM Table Mapping Wizard to add missing mappings. For more information, see Configuring Siebel Business Applications.

For information on using Siebel VB, see Siebel VB Language Reference.

Database Extensibility and EIM

If you have licensed Database Extensibility and created extensions, you can use the Column Mapping view to specify mappings to your new fields. Database Extensibility and EIM support mappings between columns in extension tables and EIM tables only if these columns share the same base table. To map EIM table extensions to base table extensions, you must specify which column the extended field will point to in the base table. For more information on Database Extensibility, see Configuring Siebel Business Applications.

EIM Table Mappings Provided as Common Parents to Nontarget EIM Table Mappings

Some EIM table mappings (usually to the target base table) are provided only as a common parent to nontarget EIM table mappings. An example of this type of EIM table mapping is mapping from the EIM_OPTY_DTL interface table to the S_OPTY base table. These EIM table mappings have a comment in the Siebel repository, indicating that they do not support inserting or updating data.

In such EIM table mappings, only the user key columns are mapped. Except for updating the primary foreign key columns, EIM does not support inserting and updating rows using these EIM table mappings.

Parameters to Set

For stability of EIM when using these EIM tables, follow the template in the default.ifb file by including the following parameters for the relevant section in the EIM configuration file:

INSERT ROWS = optional parent_table, FALSE
UPDATE ROWS = optional parent_table, FALSE

Caution: If you do not include these parameters, the EIM process may fail or some exceptions may occur.

Exception to Recommended Parameter Settings

An exception to using the recommended parameter settings is when you want to update the primary foreign key columns in the parent table. In this case, you do not want to include the following parameter in the EIM configuration file:

UPDATE ROWS = parent_table, FALSE

For example, EIM_ACCOUNT1 maps to the user key columns of S_ORG_EXT only. You can use EIM_ACCOUNT1 to update the primary foreign keys in S_ORG_EXT if the explicit primary mappings exist, such as S_ORG_EXT.PR_INDUST_ID, in the explicit primary mapping contained in the table mapping of S_ORG_INDUST. For more information, see About Explicit Primary Mappings.

In this case, you should use the default setting, UPDATE ROWS = S_ORG_EXT, TRUE in the EIM configuration file. If you do not need to update primary foreign keys in S_ORG_EXT, then you should set UPDATE ROWS = S_ORG_EXT, FALSE in the EIM configuration file.

Creating New EIM Table Mappings to Existing Base Tables

You can create new EIM table mappings from an EIM table into a base table if either of the following conditions is true:

Mappings already exist from the EIM table to the base table.
The base table is an extension table and mappings already exist from the EIM table to the corresponding base table.

For example, you could create a new column in EIM_ACCNT_DTL and map this either to a new extension column in S_ORG_EXT or to an existing column in the extension table S_ORG_EXT_X. These mappings are defined using Siebel Tools.

If you create an extension column to a base table, then run the EIM Table Mapping Wizard, the Wizard creates the following mappings:

The mapping for the newly added extension column
The mappings for all unmapped columns in the base table, including unmapped Siebel base columns

In general, manually creating mappings to an existing Siebel base column in Siebel Tools is not supported. For further information, contact your Oracle sales representative for Oracle Advanced Customer Services to request assistance from Oracle's Application Expert Services.

About Explicit Primary Mappings

The Siebel Data Model uses primary foreign keys (or primaries) to point from a parent base table to a child base table. Primaries enable business logic in the Siebel Data Model, such as identifying the primary position for an account. Moreover, primaries improve performance by eliminating repeating subqueries when data from both the parent table and the primary child table are displayed. If you do not use primaries, then you must execute a new query to identify any child records each time a parent record is displayed.

For more information, see the following sections:

Setting Explicit Primary Mappings

Primary foreign keys are columns that have names usually beginning with PR_ and are defined as primaries in the data model. If both the parent table and the primary child table of a primary foreign key are mapped to the same EIM table, then you should see an explicit primary mapping for this primary foreign key under the table mapping of the primary child table.

Note: Before you can create an explicit primary mapping, both the parent and the primary child table must be mapped to the same EIM table.

If an explicit primary mapping exists, you can use EIM to set the primary explicitly during import or update by setting the primary flag column in the EIM table. For an example of this, see Example of Setting Explicit Primary Mappings.

Setting Explicit Primaries for Many-to-Many Relationships

The example of setting a primary key in 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 also an intersection table to consider. For an example, see Example of Setting Explicit Primary Mappings for Many-to-Many Relationships.

About Viewing EIM Table Mappings

Before viewing EIM table mappings, you need to make sure your View settings are correct in Siebel Tools so that you can see the appropriate object types in the Object Explorer. Information on viewing EIM table mappings is organized as follows:

Verifying Your Object Explorer View Settings

In order to be able to view all the EIM object types in the Siebel Tools Object Explorer, verify that your settings are correct.

To verify your view settings for the Object Explorer

Start Siebel Tools.
From the View menu, choose Options.
In the Development Tools Options dialog box, click the Object Explorer tab.
From the Object Explorer Hierarchy, find the EIM Interface Table object.
The EIM Interface Table may appear enabled, but inactive.
Deselect the EIM Interface Table object, then select it again.
The EIM Interface Table object appears enabled, and active.
In the Object Explorer Types tab, expand the EIM Interface Table object.
The rest of the EIM object types appear beneath the EIM Interface Table object type.

Viewing EIM Table Mappings to Base Tables

Use Siebel Tools to view EIM table mappings to base tables.

To view EIM table mappings to base tables

Start Siebel Tools.
In the Object Explorer, click the Types tab.
Click EIM Interface Table.
In the EIM Tables window, select the EIM table for which you want to view the mappings.
In the Object Explorer, expand EIM Interface Table.
Click EIM Table Mapping.
The EIM Table Mappings window displays all base table mappings for the selected EIM table.

You can view mappings for all interface columns, but you can only add or modify mappings for extended columns in the base schema to extended columns in the EIM tables.

The following figure shows an example of viewing the EIM table mappings for the EIM_ACCOUNT interface table. In the EIM Table Mappings list applet, you can find information about each base table that has been mapped to the selected EIM table. The Destination Table field contains the physical name of the mapped base table. You can also see which temporary columns (T_*) EIM is using when processing a mapped base table.

The EIM table mappings for the EIM_ACCOUNT interface table: This image is described in the surrounding text.

Viewing Interface Column Mappings to Base Tables

Use Siebel Tools to view interface column mappings to base table columns.

To view interface column mappings to base tables

Complete Viewing EIM Table Mappings to Base Tables.
In the EIM Table Mappings window, select a base table.
In the Object Explorer, expand EIM Table Mapping.
Click Attribute Mapping.
The Attribute Mappings window displays column mappings for the selected base table.

The following figure shows an example of viewing column mappings for the S_ADDR_ORG base table. (This example is specific to horizontal Siebel CRM applications.) In the Attribute Mappings list applet, for a selected base table mapping, you can find information about the mapping that has been defined between the EIM table column and the base table column. For example, the following figure shows that the S_ADDR_ORG.ADDR_NAME column has been mapped to the ADDR_ADDR_NAME (EIM_ACCOUNT) interface column.

Viewing Interface Column Mappings to Base Tables: This image is described in the surrounding text.

Viewing Base Table Mappings to EIM Tables

Use Siebel Tools to view base table mappings to EIM tables.

To search for an EIM table mapping to a specific base table

Start Siebel Tools.
In the Object Explorer, click the Types tab.
Expand EIM Interface Table, and click EIM Table Mapping.
Execute a query for a base table mapping, entering the name of the base table in the Destination Table field.
The query returns all EIM tables that include a mapping to the base table. The EIM table to which the base table is mapped is shown in the Parent EIM Interface Table field. Some base tables may be mapped to more than one EIM table.

The following figure shows an example of viewing the EIM table mappings for the S_ADDR_ORG base table. (This example is specific to horizontal Siebel CRM applications.) Note that the S_ADDR_ORG base table maps to many EIM tables.

EIM table mappings for the S_ADDR_ORG base Table: This image is described in the surrounding text.

Generating EIM Table Mapping Reports

You can view and produce reports on any and all EIM tables in your repository, or query select EIM tables, including EIM tables that you have added.

Caution: In the following procedure, unless you query for specific EIM tables, the default action is to print every EIM table in the repository. Because outputting a report for every EIM table takes a significant amount of time to prepare and print, it is recommended that you perform a query prior to submitting your report request.

Note: To generate and print EIM table mapping reports, you need to have a running instance of Oracle BI Publisher server. For more information, see Siebel Reports Guide. The Siebel Bookshelf is available on Oracle Technology Network (OTN) and Oracle Software Delivery Cloud. It might also be installed locally on your intranet or on a network location.

To generate a report for a specific EIM interface table

Navigate to Administration - Application screen, then the EIM Tables view.
The EIM Tables list appears.
From Menu (the cogwheel icon), select New Query, then specify the name of the specific EIM table.
Click Reports on the application toolbar.
1. Select EIM Interface Tables Report from the Report Name drop-down list..
2. Choose the desired output type, such as PDF or HTML, from the Output Type drop-down list.
3. Click Submit to generate the report.
  The File Download dialog box appears.
4. When prompted, click to open, save, or cancel the report.

About the Second Row Property on EIM Table Mapping Objects

The Second Row property is set when a base table is mapped for the second time in an EIM table. If a base table always has data row pairs, it is useful to map the base table twice in an EIM table such that one row in the EIM table will become two different rows in the base table. An example of this is the table mappings of S_INV_LGR_ENTRY in EIM_INV_TXN.

EIM Table Mappings to Base Tables Without User Keys

Some EIM tables contain table mappings to base tables without user keys. When using these EIM tables, you should note the EIM behavior for the relevant process type as described in Process Issues for Base Tables Without User Keys.

EIM Tables and Base Tables Without User Keys

The following information lists some examples of EIM tables containing table mappings to base tables without user keys.

EIM Table	Target Base Table Without User Key
EIM_ACCNT_DTL	S_NOTE_ACCNT
EIM_ACCSRCPIDTL	S_NOTE_ACCSRCPI
EIM_ACC_SRC_DTL	S_NOTE_ACC_SRC
EIM_ACT_DTL	S_NOTE_ACT
EIM_ASGN_GRP	S_ASGN_RESULT
EIM_ASSET_DTL	S_NOTE_ASSET
EIM_BASELN_DTL	S_NOTE_BASELINE
EIM_CON_DTL	S_NOTE_CON
EIM_CONSUM_DTL	S_NOTE_CONSUME
EIM_CON_PI_DTL	S_NOTE_CON_PI
EIM_DCP_DTL	S_NOTE_DCP
EIM_DEFECT_DTL	S_NOTE_DEFECT
EIM_INVC_DTL	S_NOTE_INVOICE
EIM_NOTE	S_NOTE
EIM_OPTY_DTL	S_NOTE_OPTY
EIM_ORDER1	S_NOTE_ORDER
EIM_ORDER_ITEM1	S_NOTE_ORDER_IT
EIM_GROUP_DTL	S_NOTE_ORGGROUP
EIM_PRDINT_DTL	S_NOTE_PROD_INT
EIM_PROJECTDTL	S_NOTE_PROJ
EIM_PROJITMDTL	S_NOTE_PROJITEM
EIM_PROJRSRCDTL	S_NOTE_PROJRSRC
EIM_QUOTE_DTL	S_NOTE_QUOTE
EIM_QUO_IT_DTL	S_NOTE_QUOTE_IT
EIM_PDSHIP_DTL	S_NOTE_SHIPMENT
EIM_SR_DTL	S_NOTE_SR
EIM_SRC_DTL	S_NOTE_SRC
EIM_TARGET_DTL	S_NOTE_TARGET
EIM_USR_MSG_DTL	S_NOTE_USR_MSG
EIM_WFM_ACTION	S_ACTION_ARG
EIM_WFM_RULE	S_ESCL_ACTION

Process Issues for Base Tables Without User Keys

This subsection describes issues that you should be aware of when performing EIM processes involving base tables without user keys.

Importing Data into Base Tables Without User Keys. Import works but EIM does not check and prevent duplicate records from being imported into the base tables without user keys. If an import batch is executed repeatedly, the same records are imported repeatedly because EIM cannot check whether the records to be imported already exist in the base table without user keys.
Updating Data in Base Tables Without User Keys. Update on base tables without user keys cannot work, because EIM cannot uniquely identify the record to update.
Exporting Data from Base Tables Without User Keys. Exporting data from base tables without user keys is done the same way as exporting data from base tables with user keys.
Deleting Data from Base Tables Without User Keys. DELETE ALL ROWS and DELETE MATCHES can be used to delete data in target base tables. If a table without a user key is the target table, then delete works as it does for base tables with user keys. In most cases, however, a table without a user key is a secondary table and its data can only be deleted with the table as a child of its parent table.

Note: EIM_NOTE_DEL and EIM_SKLI_DEL are special EIM tables used for deleting from the S_NOTE* and S_*SKILL_IT tables, which do not have the normal U1 user key.
Merging Data in Base Tables Without User Keys. Merge does not work on base tables without user keys.

Deleting EIM Table Rows

When you have successfully imported most of your EIM table rows, you can delete them. However, you might want to leave rows that were not fully imported in order to examine and correct them. If you want to do this, remember that each EIM table imports data into one or more target base tables. For example, EIM_ACCOUNT imports into S_PARTY, S_ORG_EXT, S_ORG_BU, S_PARTY_PER, S_ORG_REL, S_ACCNT_POSTN, S_ADDR_ORG, and S_CTLG_CAT_ORG.

Each EIM table includes a separate temporary column that contains a status code for each base table into which it has imported data. The names of these columns are contractions of the target base table name.
For example, T_ORG_EXT__STA. T_ indicates that this is a temporary column; ORG_EXT is the first three letters of each word in the target base table name (S_ORG_EXT), and __STA indicates that this is the status column. Note that the extension begins with two underscores.
During import, a row’s status column is set to 0 for those tables into which the row was successfully imported. The IF_ROW_STAT is set to IMPORTED if a row is successfully imported into all target base tables, or PARTIALLY IMPORTED if it is successfully imported into at least one target.
- To delete rows that were successfully imported into all target base tables, you could use the following SQL statement:
```
   delete from EIM_ACCOUNT
   where (IF_ROW_STAT = 'IMPORTED')
```
- To delete rows that were successfully imported into specific target base tables, you could use the following SQL statement:
```
   delete from EIM_ACCOUNT
   where (if_row_stat = 'partially_imported' and
   t_org_ext__sta = 0 and t_addorg__sta = 0)
```
- You can also use ONLY BASE TABLES to limit processing.

Finding Differences in EIM Tables Between Repositories

The Siebel Data Model changes from release to release, and EIM mappings change accordingly. You can use the UTLEIMDIFF utility to find EIM mapping differences between two repositories for a list of EIM tables that you input. The results can be used to help you update your EIM data loading scripts, programs, and so on.

To use the UTLEIMDIFF utility

Create the view S_EIM_MAP_V in the database.
The database-platform-independent script for creating this view is called create_EIM_MAP_V.sql. This script can be found in the <dbsrvr>\common directory.

Find the executable UTLEIMDIFF.EXE in the <tools>\bin directory. Use the following switches for the program:

Switch	Entry	Description
/U	[username]	Siebel username
/P	[password]	Siebel password
/C	[connect string]	ODBC connect string
/D	[table owner]	Database table owner
/N	“[new Siebel repository]”	Required. Name of the new repository. Note: Enclose the repository name in quotation marks.
/O	“[old Siebel repository]”	Required. Name of the old repository. Note: Enclose the repository name in quotation marks.
/I	[input filename]	This file contains the list of EIM tables to be compared. The default input file (eim_tbl_lst.inp) is in the <tools>\bin directory. You can edit this file.
/M	[report filename]	Required. This is the output report. The default name is eim_diff.txt.
/L	[log filename]	The default name is eim_diff.log.

The program may run for several minutes, depending on the number of tables to be compared.

Interpret the three parts of the output file as follows:
- Part 1 - Interface Table Difference. Part 1 compares all the EIM tables in the two repositories.
- Part 2 - Interface Table Mapping Difference. Part 2 compares the EIM tables listed in the input file.
- Part 3 - Interface Column Mapping Difference. Part 3 compares the interface columns for the tables listed in the input file. “UK” means “User Key sequence.” “Req'd” indicates that the column in the base table is required.

The first column of each part is the repository name. If there is an entry in one repository but not the other, then that means that the entry exists in one repository but not the other. If the same entry appears in both repositories, then that means that the entry has been modified.