List of Value (LOV) Load

Introduction:

This post describes the method for loading List of Values popularly known as LOV load through EIM. Though LOV load may appear quite simpler and straightforward, its not so. In my career the most important and crucial load that I found in the LOV load. The complexity increases when there are hierarchies in the values, most common are SR Type (Grandparent), SR Area (Parent), SR Subarea (Child) or SR Type (Grandparent), SR Status (Parent) and SR Sub status (Child) in Service Request module. Similarly there are other 3-level hierarchies in product and other entities. LOV load may sometimes become bottleneck during go-live if the impact is not properly not analysed. The configuration of hierarchies field is also very important for lov load.

Lets see what an LOV is. List of values are simply values which may be digits or alphanumeric displayed on Siebel UI as drop down values on a field. There are some pre-defined values which comes as seed data and other data. These pre-defined values can be seen at UI SiteMap --> Administration - Data --> List of values. This is the place where one can see the LOVs that will be loaded through EIM.

Loading LOV through EIM:

There are various approaches to load data into siebel. Here we will take a simple one.

1. Put the LOV data in a CSV format (which can be opened in excel for editing).
2. Create a staging/intermediate table to store the data temporarily (this is very useful if some data transformation is to be done before loading in EIM tables).
3. Loading the csv data into staging table through SQLLDR utility.
4. Execute the SQL scripts to load the staging table data in EIM table.
5. Run the EIM job from UI.
6. Check the results in EIM table and from UI.
7. Downloadable scripts
8. Independent LOV Interface (Downloadable ZIP File)

Lets discuss these in detail:

1. Prepare the data in CSV format:

A sample csv data file can be downloaded from here. Fill the data strictly as per the header columns. Save it as lov.csv and close it.

2. Creating staging table:

Execute the below script to create the staging table.

CREATE TABLE "STAGING"."STG_LOV"

  (

    "LOV_TYPE"       VARCHAR2(30 BYTE),

    "LOV_NAME"       VARCHAR2(50 BYTE),

    "LOV_VAL"        VARCHAR2(30 BYTE),

    "LOV_HIGH"       VARCHAR2(100 BYTE),

    "LOV_LOW"        VARCHAR2(100 BYTE),

    "TGT_HIGH"       NUMBER,

    "TGT_LOW"        NUMBER,

    "SUB_TYPE"       VARCHAR2(30 BYTE),

    "ORDER_BY"       NUMBER,

    "LOV_BU"         VARCHAR2(100 BYTE),

    "LOV_ACTIVE_FLG" CHAR(1 BYTE),

    "DESCRIPTION"    VARCHAR2(255 BYTE),

    "PAR_NAME"       VARCHAR2(50 BYTE),

    "PAR_TYPE"       VARCHAR2(30 BYTE),

    "PAR_VAL"        VARCHAR2(30 BYTE),

    "PAR_SUB_TYPE"   VARCHAR2(30 BYTE),

    "PAR_BU"         VARCHAR2(100 BYTE)

  );

COMMIT;

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON STAGING.STG_LOV TO SIEBEL;

COMMIT;

3. Loading the data in staging table through SQLLDR utility.

Prepare a control file (.ctl) and a batch (.bat) file an place them at the same location as the csv file. (Let say in D:\LOV\).

Structure of control file (lov.ctl):

LOAD DATA

TRUNCATE

INTO TABLE STAGING.STG_LOV

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

(

LOV_TYPE,

LOV_NAME,

LOV_VAL,

LOV_HIGH,

LOV_LOW,

TGT_HIGH,

TGT_LOW,

SUB_TYPE,

ORDER_BY,

LOV_BU,

LOV_ACTIVE_FLG,

DESCRIPTION,

PAR_BU,

PAR_NAME,

PAR_TYPE,

PAR_VAL,

PAR_SUB_TYPE

)

Structure of Batch file (lov.bat):

@echo off

set ORCL_PATH=D:\Oracle\product\11.2.0\client_1\BIN

set DB_USR=XXXXXX

set DB_PAS=XXXXXX

set DB_CON=XXXXXX

set CUR_PATH=D:\LOV

%ORCL_PATH%\sqlldr %DB_USR%/%DB_PAS%@%DB_CON% CONTROL=%CUR_PATH%\lov.ctl, DATA=%CUR_PATH%\lov.csv, LOG=%CUR_PATH%\SQLLDR_LOV.log, SKIP=1, ERRORS=1000, BAD=%CUR_PATH%\lov.bad, DISCARD=%CUR_PATH%\lov.dis

So now up to here we have 3 files, lov.ctl, lov.csv and lov.bat at d:\lov\ location.

Execute the lov.bat file after filling the necessary parameters like db username, password etc. You can check the data in staging table by executing 

SELECT * FROM STAGING.STG_LOV;

4. Execute the SQL scripts to load the staging table data in EIM table.

Execute the below scripts the insert data into EIM table. You can change the batch numbers if required.

DELETE FROM EIM_LST_OF_VAL WHERE IF_ROW_BATCH_NUM BETWEEN 100 AND 199;

DELETE FROM EIM_LST_OF_VAL WHERE IF_ROW_BATCH_NUM BETWEEN 200 AND 299;

DELETE FROM EIM_LST_OF_VAL WHERE IF_ROW_BATCH_NUM BETWEEN 300 AND 399;

DELETE FROM EIM_LST_OF_VAL WHERE IF_ROW_BATCH_NUM BETWEEN 400 AND 499;

DELETE FROM EIM_LST_OF_VAL WHERE IF_ROW_BATCH_NUM BETWEEN 500 AND 599;

COMMIT;

/*For LOV with type as 'LOV_TYPE', Master LOV*/

insert into EIM_LST_OF_VAL

(

ROW_ID,

IF_ROW_STAT,

IF_ROW_BATCH_NUM,

LOV_TYPE,

LOV_VAL,

LOV_NAME,

LOV_ACTIVE_FLG,

LOV_DFLT_LIC_FLG,

LOV_LANG_ID,

LOV_MORG_DSALW_FLG,

LOV_MULTILINGUALFL,

LOV_REQD_LIC_FLG,

LOV_RPLCTN_LVL_CD,

LOV_SUB_TYPE,

LOV_BU,

LOV_HIGH,

LOV_LOW,

LOV_TARGET_HIGH,

LOV_TARGET_LOW,

LOV_ORDER_BY,

LOV_DESC_TEXT,

LOV_TRANSLATE_FLG,

LOV_VIS_BU,

PAR_TYPE,

PAR_VAL,

PAR_BU,

PAR_SUB_TYPE,

PAR_LANG_ID

)

SELECT

rownum,

'FOR_IMPORT',

100 + floor((rownum-1)/5000),

STG.LOV_TYPE,

STG.LOV_VAL,

STG.LOV_NAME,

STG.LOV_ACTIVE_FLG,

'N',

'ENU',

'N',

'N',

'N',

'All',

STG.SUB_TYPE,

STG.LOV_BU,

STG.LOV_HIGH,

STG.LOV_LOW,

STG.TGT_HIGH,

STG.TGT_LOW,

STG.ORDER_BY,

STG.DESCRIPTION,

'Y',

STG.LOV_BU,

STG.PAR_TYPE,

STG.PAR_VAL,

STG.PAR_BU,

STG.PAR_SUB_TYPE,

'ENU'

from STAGING.STG_LOV STG

where STG.lov_type = 'LOV_TYPE';

/*For LOV having BU and having no parent relationship*/

insert into EIM_LST_OF_VAL

(

ROW_ID,

IF_ROW_STAT,

IF_ROW_BATCH_NUM,

LOV_TYPE,

LOV_VAL,

LOV_NAME,

LOV_ACTIVE_FLG,

LOV_DFLT_LIC_FLG,

LOV_LANG_ID,

LOV_MORG_DSALW_FLG,

LOV_MULTILINGUALFL,

LOV_REQD_LIC_FLG,

LOV_RPLCTN_LVL_CD,

LOV_SUB_TYPE,

LOV_BU,

LOV_HIGH,

LOV_LOW,

LOV_TARGET_HIGH,

LOV_TARGET_LOW,

LOV_ORDER_BY,

LOV_DESC_TEXT,

LOV_TRANSLATE_FLG,

LOV_VIS_BU,

PAR_TYPE,

PAR_VAL,

PAR_BU,

PAR_SUB_TYPE,

PAR_LANG_ID

)

SELECT

rownum,

'FOR_IMPORT',

200 + floor((rownum-1)/5000),

STG.LOV_TYPE,

STG.LOV_VAL,

STG.LOV_NAME,

STG.LOV_ACTIVE_FLG,

'N',

'ENU',

'N',

'N',

'N',

'All',

STG.SUB_TYPE,

STG.LOV_BU,

STG.LOV_HIGH,

STG.LOV_LOW,

STG.TGT_HIGH,

STG.TGT_LOW,

STG.ORDER_BY,

STG.DESCRIPTION,

'Y',

STG.LOV_BU,

STG.PAR_TYPE,

STG.PAR_VAL,

STG.PAR_BU,

STG.PAR_SUB_TYPE,

'ENU'

from STAGING.STG_LOV STG

where STG.LOV_BU is not null

and STG.PAR_TYPE is null

and STG.PAR_VAL is null

and STG.PAR_BU is null

and STG.PAR_SUB_TYPE is null;

/*For LOV having no BU and having no parent relationship*/

insert into EIM_LST_OF_VAL

(

ROW_ID,

IF_ROW_STAT,

IF_ROW_BATCH_NUM,

LOV_TYPE,

LOV_VAL,

LOV_NAME,

LOV_ACTIVE_FLG,

LOV_DFLT_LIC_FLG,

LOV_LANG_ID,

LOV_MORG_DSALW_FLG,

LOV_MULTILINGUALFL,

LOV_REQD_LIC_FLG,

LOV_RPLCTN_LVL_CD,

LOV_SUB_TYPE,

LOV_BU,

LOV_HIGH,

LOV_LOW,

LOV_TARGET_HIGH,

LOV_TARGET_LOW,

LOV_ORDER_BY,

LOV_DESC_TEXT,

LOV_TRANSLATE_FLG,

LOV_VIS_BU,

PAR_TYPE,

PAR_VAL,

PAR_BU,

PAR_SUB_TYPE,

PAR_LANG_ID

)

SELECT

rownum,

'FOR_IMPORT',

300 + floor((rownum-1)/5000),

STG.LOV_TYPE,

STG.LOV_VAL,

STG.LOV_NAME,

STG.LOV_ACTIVE_FLG,

'N',

'ENU',

'N',

'N',

'N',

'All',

STG.SUB_TYPE,

STG.LOV_BU,

STG.LOV_HIGH,

STG.LOV_LOW,

STG.TGT_HIGH,

STG.TGT_LOW,

STG.ORDER_BY,

STG.DESCRIPTION,

'Y',

STG.LOV_BU,

STG.PAR_TYPE,

STG.PAR_VAL,

STG.PAR_BU,

STG.PAR_SUB_TYPE,

'ENU'

from STAGING.STG_LOV STG

where STG.LOV_BU is null

and STG.PAR_TYPE is null

and STG.PAR_VAL is null

and STG.PAR_BU is null

and STG.PAR_SUB_TYPE is null

and stg.lov_type <> 'LOV_TYPE';

/*For LOV having BU and having parent relationship*/

insert into EIM_LST_OF_VAL

(

ROW_ID,

IF_ROW_STAT,

IF_ROW_BATCH_NUM,

LOV_TYPE,

LOV_VAL,

LOV_NAME,

LOV_ACTIVE_FLG,

LOV_DFLT_LIC_FLG,

LOV_LANG_ID,

LOV_MORG_DSALW_FLG,

LOV_MULTILINGUALFL,

LOV_REQD_LIC_FLG,

LOV_RPLCTN_LVL_CD,

LOV_SUB_TYPE,

LOV_BU,

LOV_HIGH,

LOV_LOW,

LOV_TARGET_HIGH,

LOV_TARGET_LOW,

LOV_ORDER_BY,

LOV_DESC_TEXT,

LOV_TRANSLATE_FLG,

LOV_VIS_BU,

PAR_TYPE,

PAR_VAL,

PAR_BU,

PAR_SUB_TYPE,

PAR_LANG_ID

)

SELECT

rownum,

'FOR_IMPORT',

400 + floor((rownum-1)/5000),

STG.LOV_TYPE,

STG.LOV_VAL,

STG.LOV_NAME,

STG.LOV_ACTIVE_FLG,

'N',

'ENU',

'N',

'N',

'N',

'All',

STG.SUB_TYPE,

STG.LOV_BU,

STG.LOV_HIGH,

STG.LOV_LOW,

STG.TGT_HIGH,

STG.TGT_LOW,

STG.ORDER_BY,

STG.DESCRIPTION,

'Y',

STG.LOV_BU,

STG.PAR_TYPE,

STG.PAR_VAL,

STG.PAR_BU,

STG.PAR_SUB_TYPE,

'ENU'

from STAGING.STG_LOV STG

where STG.LOV_BU is not null

and STG.PAR_TYPE is not null

and STG.PAR_VAL is not null;

/*For LOV having no BU and having parent relationship*/

insert into EIM_LST_OF_VAL

(

ROW_ID,

IF_ROW_STAT,

IF_ROW_BATCH_NUM,

LOV_TYPE,

LOV_VAL,

LOV_NAME,

LOV_ACTIVE_FLG,

LOV_DFLT_LIC_FLG,

LOV_LANG_ID,

LOV_MORG_DSALW_FLG,

LOV_MULTILINGUALFL,

LOV_REQD_LIC_FLG,

LOV_RPLCTN_LVL_CD,

LOV_SUB_TYPE,

LOV_BU,

LOV_HIGH,

LOV_LOW,

LOV_TARGET_HIGH,

LOV_TARGET_LOW,

LOV_ORDER_BY,

LOV_DESC_TEXT,

LOV_TRANSLATE_FLG,

LOV_VIS_BU,

PAR_TYPE,

PAR_VAL,

PAR_BU,

PAR_SUB_TYPE,

PAR_LANG_ID

)

SELECT

rownum,

'FOR_IMPORT',

500 + floor((rownum-1)/5000),

STG.LOV_TYPE,

STG.LOV_VAL,

STG.LOV_NAME,

STG.LOV_ACTIVE_FLG,

'N',

'ENU',

'N',

'N',

'N',

'All',

STG.SUB_TYPE,

STG.LOV_BU,

STG.LOV_HIGH,

STG.LOV_LOW,

STG.TGT_HIGH,

STG.TGT_LOW,

STG.ORDER_BY,

STG.DESCRIPTION,

'Y',

STG.LOV_BU,

STG.PAR_TYPE,

STG.PAR_VAL,

STG.PAR_BU,

STG.PAR_SUB_TYPE,

'ENU'

from STAGING.STG_LOV STG

where STG.LOV_BU is null

and STG.PAR_TYPE is not null

and STG.PAR_VAL is not null;

The 5 different cases covered are:

1. LOV with type as 'LOV_TYPE' these are Master LOV
2. LOV having BU (Organization) and having no parent relationship
3. LOV having no BU (Organization) and having no parent relationship
4. LOV having BU (Organization) and having parent relationship
5. LOV having no BU (Organization) and having parent relationship

To check the data in EIM tables execute below scripts:

select COUNT(1), IF_ROW_STAT, IF_ROW_BATCH_NUM

FROM EIM_LST_OF_VAL WHERE IF_ROW_BATCH_NUM BETWEEN 100 AND 199

GROUP BY IF_ROW_STAT, IF_ROW_BATCH_NUM ORDER BY IF_ROW_BATCH_NUM;

select COUNT(1), IF_ROW_STAT, IF_ROW_BATCH_NUM

FROM EIM_LST_OF_VAL WHERE IF_ROW_BATCH_NUM BETWEEN 200 AND 299

GROUP BY IF_ROW_STAT, IF_ROW_BATCH_NUM ORDER BY IF_ROW_BATCH_NUM;

select COUNT(1), IF_ROW_STAT, IF_ROW_BATCH_NUM

FROM EIM_LST_OF_VAL WHERE IF_ROW_BATCH_NUM BETWEEN 300 AND 399

GROUP BY IF_ROW_STAT, IF_ROW_BATCH_NUM ORDER BY IF_ROW_BATCH_NUM;

select COUNT(1), IF_ROW_STAT, IF_ROW_BATCH_NUM

FROM EIM_LST_OF_VAL WHERE IF_ROW_BATCH_NUM BETWEEN 400 AND 499

GROUP BY IF_ROW_STAT, IF_ROW_BATCH_NUM ORDER BY IF_ROW_BATCH_NUM;

select COUNT(1), IF_ROW_STAT, IF_ROW_BATCH_NUM

FROM EIM_LST_OF_VAL WHERE IF_ROW_BATCH_NUM BETWEEN 500 AND 599

GROUP BY IF_ROW_STAT, IF_ROW_BATCH_NUM ORDER BY IF_ROW_BATCH_NUM;

5. Run the EIM job from UI:

Prepare the ifb file: Below IFB file parameters will load the data from EIM table to Siebel Base tables.

[Siebel Interface Manager]

      PROCESS = ImportLOV

      LOG TRANSACTIONS= FALSE

[ImportLOV]

TYPE = SHELL

INCLUDE = "TYPE_LOV"

INCLUDE = "PARENT_LOV"

INCLUDE = "PARENT_LOV_NULL_BU"

INCLUDE = "CHILD_LOV"

INCLUDE = "CHILD_LOV_NULL_BU"

[TYPE_LOV]

TYPE = IMPORT

BATCH = 100-199

TABLE = EIM_LST_OF_VAL

ONLY BASE TABLES = S_LST_OF_VAL

INSERT ROWS = S_LST_OF_VAL, TRUE

UPDATE ROWS = S_LST_OF_VAL, TRUE

ONLY BASE COLUMNS = S_LST_OF_VAL.ACTIVE_FLG,\

                           S_LST_OF_VAL.DESC_TEXT,\

                           S_LST_OF_VAL.DFLT_LIC_FLG,\

                           S_LST_OF_VAL.LANG_ID,\

                           S_LST_OF_VAL.MLTORG_DISALW_FLG,\

                           S_LST_OF_VAL.MODIFIABLE_FLG,\

                           S_LST_OF_VAL.MULTI_LINGUAL_FLG,\

                           S_LST_OF_VAL.NAME,\

                           S_LST_OF_VAL.ORDER_BY,\

                           S_LST_OF_VAL.REQD_LIC_FLG,\

                           S_LST_OF_VAL.RPLCTN_LVL_CD,\

                           S_LST_OF_VAL.SUB_TYPE,\

                           S_LST_OF_VAL.TRANSLATE_FLG,\

                           S_LST_OF_VAL.TYPE,\

                           S_LST_OF_VAL.VAL,\

  S_LST_OF_VAL.HIGH,\

  S_LST_OF_VAL.LOW,\

  S_LST_OF_VAL.TARGET_HIGH,\

  S_LST_OF_VAL.TARGET_LOW

USE INDEX HINTS  = TRUE

USE ESSENTIAL INDEX HINTS = FALSE

[PARENT_LOV]

TYPE = IMPORT

BATCH = 200-299

TABLE = EIM_LST_OF_VAL

ONLY BASE TABLES = S_LST_OF_VAL, S_LST_OF_VAL_BU

INSERT ROWS = S_LST_OF_VAL, TRUE

UPDATE ROWS = S_LST_OF_VAL, TRUE

INSERT ROWS = S_LST_OF_VAL_BU, TRUE

UPDATE ROWS = S_LST_OF_VAL_BU, TRUE

ONLY BASE COLUMNS = S_LST_OF_VAL.ACTIVE_FLG,\

                           S_LST_OF_VAL.BU_ID,\

                           S_LST_OF_VAL.DESC_TEXT,\

                           S_LST_OF_VAL.DFLT_LIC_FLG,\

                           S_LST_OF_VAL.LANG_ID,\

                           S_LST_OF_VAL.MLTORG_DISALW_FLG,\

                           S_LST_OF_VAL.MODIFIABLE_FLG,\

                           S_LST_OF_VAL.MULTI_LINGUAL_FLG,\

                           S_LST_OF_VAL.NAME,\

                           S_LST_OF_VAL.ORDER_BY,\

                           S_LST_OF_VAL.REQD_LIC_FLG,\

                           S_LST_OF_VAL.RPLCTN_LVL_CD,\

                           S_LST_OF_VAL.SUB_TYPE,\

                           S_LST_OF_VAL.TRANSLATE_FLG,\

                           S_LST_OF_VAL.TYPE,\

                           S_LST_OF_VAL.VAL,\

  S_LST_OF_VAL.HIGH,\

  S_LST_OF_VAL.LOW,\

  S_LST_OF_VAL.TARGET_HIGH,\

  S_LST_OF_VAL.TARGET_LOW,\

                           S_LST_OF_VAL_BU.BU_ID,\

                           S_LST_OF_VAL_BU.LST_OF_VAL_ID

USE INDEX HINTS  = TRUE

USE ESSENTIAL INDEX HINTS = FALSE

[PARENT_LOV_NULL_BU]

TYPE = IMPORT

BATCH = 300-399

TABLE = EIM_LST_OF_VAL

ONLY BASE TABLES = S_LST_OF_VAL

INSERT ROWS = S_LST_OF_VAL, TRUE

UPDATE ROWS = S_LST_OF_VAL, TRUE

ONLY BASE COLUMNS = S_LST_OF_VAL.ACTIVE_FLG,\

                           S_LST_OF_VAL.DESC_TEXT,\

                           S_LST_OF_VAL.DFLT_LIC_FLG,\

                           S_LST_OF_VAL.LANG_ID,\

                           S_LST_OF_VAL.MLTORG_DISALW_FLG,\

                           S_LST_OF_VAL.MODIFIABLE_FLG,\

                           S_LST_OF_VAL.MULTI_LINGUAL_FLG,\

                           S_LST_OF_VAL.NAME,\

                           S_LST_OF_VAL.ORDER_BY,\

                           S_LST_OF_VAL.REQD_LIC_FLG,\

                           S_LST_OF_VAL.RPLCTN_LVL_CD,\

                           S_LST_OF_VAL.SUB_TYPE,\

                           S_LST_OF_VAL.TRANSLATE_FLG,\

                           S_LST_OF_VAL.TYPE,\

                           S_LST_OF_VAL.VAL,\

  S_LST_OF_VAL.HIGH,\

  S_LST_OF_VAL.LOW,\

  S_LST_OF_VAL.TARGET_HIGH,\

  S_LST_OF_VAL.TARGET_LOW

USE INDEX HINTS  = TRUE

USE ESSENTIAL INDEX HINTS = FALSE

[CHILD_LOV]

TYPE = IMPORT

BATCH = 400-499

TABLE = EIM_LST_OF_VAL

ONLY BASE TABLES = S_LST_OF_VAL, S_LST_OF_VAL_BU

INSERT ROWS = S_LST_OF_VAL, TRUE

UPDATE ROWS = S_LST_OF_VAL, TRUE

INSERT ROWS = S_LST_OF_VAL_BU, TRUE

UPDATE ROWS = S_LST_OF_VAL_BU, TRUE

ONLY BASE COLUMNS = S_LST_OF_VAL.ACTIVE_FLG,\

                           S_LST_OF_VAL.BU_ID,\

                           S_LST_OF_VAL.DESC_TEXT,\

                           S_LST_OF_VAL.DFLT_LIC_FLG,\

                           S_LST_OF_VAL.LANG_ID,\

                           S_LST_OF_VAL.MLTORG_DISALW_FLG,\

                           S_LST_OF_VAL.MODIFIABLE_FLG,\

                           S_LST_OF_VAL.MULTI_LINGUAL_FLG,\

                           S_LST_OF_VAL.NAME,\

                           S_LST_OF_VAL.ORDER_BY,\

                           S_LST_OF_VAL.PAR_ROW_ID,\

                           S_LST_OF_VAL.REQD_LIC_FLG,\

                           S_LST_OF_VAL.RPLCTN_LVL_CD,\

                           S_LST_OF_VAL.SUB_TYPE,\

                           S_LST_OF_VAL.TRANSLATE_FLG,\

                           S_LST_OF_VAL.TYPE,\

                           S_LST_OF_VAL.VAL,\

  S_LST_OF_VAL.HIGH,\

  S_LST_OF_VAL.LOW,\

  S_LST_OF_VAL.TARGET_HIGH,\

  S_LST_OF_VAL.TARGET_LOW,\

                           S_LST_OF_VAL_BU.BU_ID,\

                           S_LST_OF_VAL_BU.LST_OF_VAL_ID

USE INDEX HINTS  = TRUE

USE ESSENTIAL INDEX HINTS = FALSE

[CHILD_LOV_NULL_BU]

TYPE = IMPORT

BATCH = 500-599

TABLE = EIM_LST_OF_VAL

ONLY BASE TABLES = S_LST_OF_VAL

INSERT ROWS = S_LST_OF_VAL, TRUE

UPDATE ROWS = S_LST_OF_VAL, TRUE

ONLY BASE COLUMNS = S_LST_OF_VAL.ACTIVE_FLG,\

                           S_LST_OF_VAL.DESC_TEXT,\

                           S_LST_OF_VAL.DFLT_LIC_FLG,\

                           S_LST_OF_VAL.LANG_ID,\

                           S_LST_OF_VAL.MLTORG_DISALW_FLG,\

                           S_LST_OF_VAL.MODIFIABLE_FLG,\

                           S_LST_OF_VAL.MULTI_LINGUAL_FLG,\

                           S_LST_OF_VAL.NAME,\

                           S_LST_OF_VAL.ORDER_BY,\

                           S_LST_OF_VAL.PAR_ROW_ID,\

                           S_LST_OF_VAL.REQD_LIC_FLG,\

                           S_LST_OF_VAL.RPLCTN_LVL_CD,\

                           S_LST_OF_VAL.SUB_TYPE,\

                           S_LST_OF_VAL.TRANSLATE_FLG,\

                           S_LST_OF_VAL.TYPE,\

                           S_LST_OF_VAL.VAL,\

  S_LST_OF_VAL.HIGH,\

  S_LST_OF_VAL.LOW,\

  S_LST_OF_VAL.TARGET_HIGH,\

  S_LST_OF_VAL.TARGET_LOW

USE INDEX HINTS  = TRUE

USE ESSENTIAL INDEX HINTS = FALSE

6. Check the results in EIM table and from UI:

To check the results of imported data, run the scripts as mentioned at step 4 for checking the data in EIM table. Thereafter check some sample data from UI. Run the IFB file from Siebel UI SiteMap --> Administration - Server Management --> Jobs. Create a new job and enter Enterprise Integration Mgr in Job name field, put the ifb name in configuration file parameter and fill other necessary flags for debugging.

7. Downloadable Scripts:

Staging Table Script
Control File
EIM Table Script File
IFB File

8. Independent LOV Interface (Downloadable ZIP File)

Click here to download the complete LOV interface as a standalone program. Read the instruction as in instructions.txt file inside the LOV folder.


"DO COMMENT ON THIS POST FOR FEEDBACK"

Mohammed Arif
http://siebel-eim.weebly.com

Populating column with "Expr: 'RowIdToRowIdNum ([Id])'" expression in the pre-default value property of field

Sometimes the requirement is to populate columns having the expression 'Expr: 'RowIdToRowIdNum ([Id])' in the pre-default property of the field. There are few columns in siebel where the field is pre-defaulted to this expression. 

For example, SR_NUM column of S_SRV_REQ table (Service Request table), OFFER_NUM column of S_DMND_CRTN_PRG table, MTRX_RULE_NUM column of S_STDPROD_PMTRX table etc.

Let us first understand the purpose of the Expression "RowIdToRowIdNum ([Id])":

1. Pre-default value always gets evaluated whenever a new record in created from UI. While importing records through EIM, the pre-default value is ignored.

2. As the expression itself indicates, it evaluates the value of the column by converting the value of ROW_ID column into some numeric value. This conversion algorithm/formula (ROW_ID value to numeric with a hyphen '-' in-between) is internal to siebel.

3. There are very few fields in siebel which have this expression in the pre-default property and most of them are not significant. Hence the requirement of populating these columns is also very few.

4. Moreover these columns are required to populate only when they are marked as required column in siebel tools, otherwise it can be ignored.

If the requirement is to populate the column say for example S_STDPROD_PMTRX.MTRX_RULE_NUM which is a required user key column, it can be updated with either the ROW_ID value or any other unique value like sequence etc.

Updating MTRX_RULE_NUM with ROW_ID value:

1. Run EIM to import the S_STDPROD_PMTRX rows without some unique MTRX_RULE_NUM values first, for example populate it with any database sequence value.

2. Once the import is successful, select the imported rows in step 1, and re-populate them into the interface table (with different batch number) with ROW_ID values (from siebel) in the MTRX_RULE_NUM column.

3. Run EIM import again to update S_STDPROD_PMTRX.MTRX_RULE_NUM with the values populated in step 2. Please include the following parameters in the .ifb file:

       TYPE = IMPORT
       BATCH = 2
       TABLE = EIM_ADJ_GROUP
       ONLY BASE TABLES = S_ADJ_GROUP, S_STDPROD_PMTRX
       INSERT ROWS = S_ADJ_GROUP, FALSE
       UPDATE ROWS = S_ADJ_GROUP, FALSE
       INSERT ROWS = S_STDPROD_PMTRX, FALSE
       UPDATE ROWS = S_STDPROD_PMTRX, TRUE

This way we can update the column with siebel ROW_ID values i.e by first importing then exporting and then again importing with update only.

Remember, These special columns cannot be populated with exact pre-default expression values through EIM.

For more details logon to Oracle Support portal.

Importing Contact in Siebel through EIM

Import Contact:

For importing contact in siebel you need to import below siebel tables:

S_CONTACT
S_CONTACT_BU
S_POSTN_CON
S_PARTY

Brief description of above tables:

• S_CONTACT: This base table stores all the general details related to a contact like Name, Telephone No. etc.
• S_CONTACT_BU: This is an intersection table between S_CONTACT and S_BU which stores the organization of the contact.
• S_POSTN_CON: This is an intersection table between S_CONTACT and S_POSTN. The POSTN_ID of this table specifies which employee can see this contact in his My Contact List. Associating a position to a contact is mandatory, if you do not associate, the contact will not be visible on UI.
• S_PARTY: This is the parent table for S_CONTACT, S_CONTACT being the siebel extension table for S_PARTY. Whenever a new contact is loaded in siebel S_PARTY must have an entry of the contact. You cannot import a contact in siebel without populating this table.

The EIM table that we can use is EIM_CONTACT since this table populates all the above mentioned tables.

Loading Contact:

Step 1: Run the below query, this query will insert the contact details in the eim table.

INSERT INTO EIM_CONTACT

(

ROW_ID,

IF_ROW_STAT,

IF_ROW_BATCH_NUM,

CON_PERSON_UID,

CON_PRIV_FLG,

CON_BU,

CON_ACTIVE_FLG,

CON_COURT_PAY_FLG,

CON_DISACLEANSEFLG,

CON_DISPIMGAUTHFLG,

CON_EMAILSRUPD_FLG,

CON_EMP_FLG,

CON_FST_NAME,

CON_INVSTGTR_FLG,

CON_LAST_NAME,

CON_ONDMNDSYNCFLG,

CON_PO_PAY_FLG,

CON_PROSPECT_FLG,

CON_PTSHPCONTACTFL,

CON_PTSHPKEYCONFLG,

CON_SENDSURVEY_FLG,

CON_SPEAKER_FLG,

CON_SUPPRESSEMAILF,

CON_SUPPRESSFAXFLG,

CON_SUSPECT_FLG,

CON_SUSP_WTCH_FLG,

CON_VETERAN_FLG,

PARTY_UID,

PARTY_TYPE_CD,

ROOT_PARTY_FLG,

ADMIN_ADJ_FLG,

PC_POSTN_BU,

PC_POSTN_DIVN,

PC_POSTN_NAME,

PC_ROW_STATUS,

CB_BU

)

VALUES

(

1,

'FOR_IMPORT',

1,

'EIM_TEST_1',

'N',

'Default Organization',

'Y',

'N',

'N',

'N',

'N',

'N',

'Mohammed',

'N',

'Arif',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'N',

'EIM_TEST_1',

'Person',

'N',

'N',

'Default Organization',

'INDIA',

'ACCOUNT_MANAGER-123',

'N',

'Default Organization'

);

COMMIT;

Remember that while importing new contacts, you need to populate all the required columns along with the user key columns of all the 4 base tables mentioned above.

Step 2: Check the inserted records:

select count(1), if_row_stat, if_row_batch_num

from eim_contact where if_row_batch_num = 1

group by if_row_stat, if_row_batch_num;

Step 3: Run EIM job with below IFB configuration parameters:

[Siebel Interface Manager]

PROCESS = Import_Contact

[Import_Contact]

TYPE = IMPORT

BATCH = 1

TABLE = EIM_CONTACT

ONLY BASE TABLES = S_PARTY, S_CONTACT, S_POSTN_CON, S_CONTACT_BU

Provide other necessary parameters like username, password etc. if required.

Step 4: Once the job is successfully completed, Check the IF_ROW_STAT column of the EIM table by running the below query.

select count(1), if_row_stat, if_row_batch_num

from eim_contact where if_row_batch_num = 1

group by if_row_stat, if_row_batch_num;

Step 5: Check imported records from UI.

NOTE: For any kind of errors in running EIM job, check the log file placed at /siebsrv/log/ location on the server. To get appropriate messages in the log file you need to specify below parameters while running the EIM job from UI.

Trace Flags = 3

SQL Trace Flags = 8

Error Flags = 1

SBL-EIM-00204: Invalid configuration file (ifb) name.

SBL-EIM-00204: Invalid configuration file (ifb) name.

This is a very common error that is encountered when one runs the EIM job from GUI. Though the error indicates that there is a possible error in the name of the IFB file, it may not be true at all.

I remember in my early career when I was new to EIM, i faced this error though the ifb file name was absolutely correct. I was perplexed as what went wrong with the name. I checked the name of the ifb file name almost 10 times, checking for space etc.

Finally I opened the ifb file and found a character '5' just before the mandatory parameter [Siebel Interface Manager], something like this:

5[Siebel Interface Manager]
      PROCESS = Import Product

Siebel EIM bookshelf says "The first nonblank, noncomment line of the configuration file’s header section must contain the exact information shown:
[Siebel Interface Manager]"

The character was accidentally written over there when i was making changes to the batch number for test load. So silly was the mistake but the error message popped out by EIM was sillier! :-)

Recommended number of rows that can be loaded in a single batch

What is the recommended number of rows that can be loaded in a single batch?

1. For an initial load, you can use 30,000 rows for a large batch.
2. For ongoing loads, you can use 20,000 rows for a large batch.
3. You should not exceed 100,000 rows in a large batch.
4. Furthermore, you should limit the number of records in the interface tables to those that are being processed. 
5. For example, if you have determined that the optimal batch size for your system is 19,000 rows per patch and you are going to be running 8 parallel EIM processes, then you should have 152,000 rows in the interface table. 
6. Under no circumstances should you have more than 250,000 rows in any single interface table, since this will have a tremendous negative performance impact.

NOTE: The number of rows you can load in a single batch may vary depending on your physical machine setup. To reduce demands on system resources and improve performance, you should always try to use smaller batch sizes.