How to Populate a base table in Siebel CRM

To populate a base table in Siebel, the EIM tables should be identified. This can be done through Siebel Tools or by query the database directly. Follow the steps to quickly identify the EIM tables you need to populate.

1. Identify which base table you want to populate. If there is an existing view or applet, you can go in About Record to identify the business components and through Siebel Tools, identify the base tables.
2. Once you know which base tables you are going to populate, run the following query to identify which EIM populates the specific base table

3. SELECT distinct IT.NAME “EIM Table”

   FROM S_COLUMN IC, S_COLUMN BC, S_EIM_ATT_MAP MA,

   S_TABLE BT, S_EIM_TBL_MAP MT, S_TABLE IT, S_REPOSITORY R

   WHERE

   MA.IFTAB_DATA_COL_ID = IC.ROW_ID

   AND MA.BTAB_ATT_COL_ID = BC.ROW_ID

   AND MA.INACTIVE_FLG = ‘N’

   AND MT.ROW_ID = MA.EIM_TBL_MAP_ID

   AND MT.DEST_TBL_ID = BT.ROW_ID

   AND MT.INACTIVE_FLG = ‘N’

   AND IT.ROW_ID = MT.IF_TBL_ID

   AND R.ROW_ID = IT.REPOSITORY_ID

   AND R.NAME = ‘Siebel Repository’

   AND BT.NAME LIKE ‘S_PROD_SHIPMENT’ – TO REPLACE WITH BASE TABLE NAME

4. From the results you obtain above, you need to identify now the column name in the base tables, the EIMs are going to populate. The query below return the base tables

5. SELECT

   BT.NAME “Destination Table”,

   BC.NAME “Destination Column”,

   CASE

   WHEN BC.USR_KEY_SEQUENCE IS NULL THEN ‘x’

   ELSE TO_CHAR(BC.USR_KEY_SEQUENCE) END “UK1″, BC.REQUIRED “Req”,

   CASE WHEN BC.LOV_TYPE_CD IS NULL THEN NULL

   ELSE

   CASE BC.LOV_BOUNDED

   WHEN ‘N’ THEN ‘LOV’

   ELSE CASE WHEN BC.TRANS_TABLE_ID IS NULL THEN ‘LOVB’

   ELSE ‘MLOV’

   END END END “DType”,

   BC.LOV_TYPE_CD “DValue”,

   BC.USER_NAME “Destination Description”,

   IC.NAME “Source Column”,

   IC.DATA_TYPE “Data Type”,

   IC.LENGTH “Len”, NULL “PC Intersect Table”

   FROM S_COLUMN IC, S_COLUMN BC, S_EIM_ATT_MAP MA,

   S_TABLE BT, S_EIM_TBL_MAP MT, S_TABLE IT, S_REPOSITORY R

   WHERE

   MA.IFTAB_DATA_COL_ID = IC.ROW_ID

   AND MA.BTAB_ATT_COL_ID = BC.ROW_ID

   AND MA.INACTIVE_FLG = ‘N’

   AND MT.ROW_ID = MA.EIM_TBL_MAP_ID

   AND MT.DEST_TBL_ID = BT.ROW_ID

   AND MT.INACTIVE_FLG = ‘N’

   AND IT.ROW_ID = MT.IF_TBL_ID

   AND IT.NAME = ‘EIM_PDSHIP_DTL’ – TO REPLACE WITH EIM_TABLE NAME

   AND R.ROW_ID = IT.REPOSITORY_ID

   AND R.NAME = ‘Siebel Repository’

   UNION

   SELECT BT.NAME “Destination Table”, BC.NAME “Destination Column”,

   CASE WHEN BC.USR_KEY_SEQUENCE IS NULL THEN ‘x’

   ELSE TO_CHAR(BC.USR_KEY_SEQUENCE) END “UK1″, BC.REQUIRED “Req”,

   ‘PC’ “DType”, FT.NAME “DValue”,

   BC.USER_NAME “Destination Description”,

   IC.NAME “Source Column”,

   IC.DATA_TYPE “Data Type”,

   IC.LENGTH “Len”, ITS.NAME “PC Intersect Table”

   FROM S_COLUMN IC, S_TABLE BT, S_TABLE FT, S_COLUMN BC,

   S_EIM_EXPPR_MAP ME, S_TABLE ITS,

   S_EIM_TBL_MAP MT, S_TABLE IT, S_REPOSITORY R

   WHERE

   ME.IFTAB_PRFLG_COL_ID = IC.ROW_ID

   AND BC.TBL_ID = BT.ROW_ID

   AND BC.FKEY_TBL_ID = FT.ROW_ID

   AND ME.BTAB_PC_COL_ID = BC.ROW_ID

   AND ME.INACTIVE_FLG = ‘N’

   AND MT.ROW_ID = ME.EIM_TBL_MAP_ID

   AND MT.DEST_TBL_ID = ITS.ROW_ID

   AND MT.INACTIVE_FLG = ‘N’

   AND IT.ROW_ID = MT.IF_TBL_ID

   AND IT.NAME =‘EIM_PDSHIP_DTL’ – TO REPLACE WITH EIM TABLE NAME

   AND R.ROW_ID = IT.REPOSITORY_ID

   AND R.NAME = ‘Siebel Repository’

   UNION

   SELECT BT.NAME “Destination Table”, BC.NAME “Destination Column”,

   CASE WHEN BC.USR_KEY_SEQUENCE IS NULL THEN ‘x’

   ELSE TO_CHAR(BC.USR_KEY_SEQUENCE) END “UK1″, BC.REQUIRED “Req”,

   ‘FK’ “DType”, FT.NAME “DValue”,

   BC.USER_NAME “Destination Description”,

   IC.NAME “Source Column”,

   IC.DATA_TYPE “Data Type”,

   IC.LENGTH “Len”, NULL “PC Intersect Table”

   FROM S_COLUMN IC, S_TABLE FT, S_COLUMN BC,

   S_EIM_FK_MAPCOL MFC, S_EIM_FK_MAP MF,

   S_TABLE BT, S_EIM_TBL_MAP MT, S_TABLE IT, S_REPOSITORY R

   WHERE

   MFC.IFTAB_COL_ID = IC.ROW_ID

   AND BC.FKEY_TBL_ID = FT.ROW_ID

   AND MF.FK_COL_ID = BC.ROW_ID

   AND MFC.INACTIVE_FLG = ‘N’

   AND MF.ROW_ID = MFC.EIM_FK_MAP_ID

   AND MF.INACTIVE_FLG = ‘N’

   AND MT.ROW_ID = MF.EIM_TBL_MAP_ID

   AND MT.DEST_TBL_ID = BT.ROW_ID

   AND MT.INACTIVE_FLG = ‘N’

   AND IT.ROW_ID = MT.IF_TBL_ID

   AND IT.NAME =‘EIM_PDSHIP_DTL’ – TO REPLACE WITH EIM TABLE NAME

   AND R.ROW_ID = IT.REPOSITORY_ID

   AND R.NAME = ‘Siebel Repository’

   ORDER BY 1,3

6. The above results summarizes the LOV type, mandatory fields, EIM Tables, Base Tables and Foreign Keys