Monday, May 5, 2014

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