Example of Importing and Exporting Hierarchical LOVs

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

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 = "********"
   	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
   

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 these records do not exist, then create them in the Siebel client by going to the Administration - Application screen, then the 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 the following table>
      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 the following table>)
      AND A.IF_ROW_BATCH_NUM = <see Note C row in the following table>;
   

   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 = "********"
   	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
   

9. Migrate the S_LOV_REL rows using the EIM_LOV_REL interface table.