SESSION SQL

Disclaimer: This post may not appeal EIM war horses but might be of some interest to newbies in EIM.

There are two types of Data, the one you look up and the one you make up. EIM is the technique which is used for the later type. As per siebel Bookshelf "Siebel EIM manages the exchange of data between Siebel database tables and other corporate databases". The EIM process constitue of two major steps:

1 - The data is first copied from external source(external table, csv , excel) into the interface tables.

2 - This data from the interface tables is then loaded into predefined destination columns in the base tables using the configuration file.

This configuration file, popularly known as ifb file, hosts the magical words which can be used to import, update, delete, extract, merge data. A more detailed list of paramters is available in bookshelf. One of the interesting parameters in the list is "SESSION SQL" which can perform operations after the EIM execution. This can be very handy if anybody wants to update or delete from the Base table or Interface table after EIM processing is done as defined in the configuration file. A sample requirement could be setting Primary Organization in Contact. If EIM champs are still reading then definitely thought of using "EIM Explicit Primary Mapping" will be lurking in mind. But unfortunately the Primary Organization Id (BU_ID) is not mapped in the explicit primary settings in EIM_CONTACT table.

But still we can achieve the desired task by using "SESSION SQL". The idea is we can update BU_ID in the S_CONTACT with the ROW_ID of Organization which we want to be primary after we are done with the loading of "S_CONTACT_BU" base table. The key here is to have an identifier defined in the interface table that will specify which BU is the primary in the Interface table. We can either extend interface table or reuse any of the existing column(ensure that is not used in the EIM operation).

[Siebel Interface Manager]

.

.

.

.

[Update Primary Org]

SESSION SQL ="UPDATE SIEBEL.S_CONTACT SCON SET SCON.BU_ID = (SELECT SBU.ROW_ID FROM SIEBEL.S_BU SBU,SIEBEL.EIM_CONTACT ECON WHERE SBU.NAME = ECON.NAME AND ECON.X_PR_FLG = 'Y' AND ECON.T_CONTACT__RID = SCON.ROW_ID) WHERE SCON.ROW_ID IN (SELECT T_CONTACT__RID FROM SIEBEL.EIM_CONTACT WHERE T_CONTACT__RID IS NOT NULL AND X_PR_FLG = 'Y')"

TYPE = IMPORT

BATCH = 9999

TABLE = EIM_CONTACT

ONLY BASE TABLES = S_PARTY, S_CONTACT, S_CONTACT_BU

Interestingly we can have only one SESSION SQL statement per block and did i tell you we can also call procedures from this awesome parameter for complex processing.

Happy Data Load!!