Siebel Enterprise Integration Manager Administration Guide > EIM: Examples of Common Usage > EIM Import Process Examples >

Example of Importing and Exporting Hierarchical LOVs

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

NOTE:  The .IFB settings listed in Step 1 below show ROW_ID values for Siebel Business Applications. If you are using Siebel Industry Applications (SIA), first run the SQL suggested in Alert 925 on SupportWeb to get the ROW_ID values, and then replace the corresponding ROW_ID values in the the .IFB settings for Step 1.

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 Alert 925 on SupportWeb.

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 the above three records do not exist, create them in the Siebel client at Administration - Application > 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 table below>

   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 table below>)

   AND A.IF_ROW_BATCH_NUM = <see Note C row in table below>;

   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