September 1st, 2008Populating a Base Table in Siebel
To populate a base table in Siebel, the EIM tables should be identified. This can be done through Siebel Tools but here I present a simple and quicker way to do same
- Identify which base table you want to populate. If there is an existing view, you can go in About Record to identify the business components and through Siebel Tools, the base tables
- Once you know which base tables you are going to populate, run the following query to identify which EIM populates the specific base table
-
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 - From the results you obtain above, you need to identify now the base tables, the EIMs are going to populate. The query below return the base tables
-
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 NAMEAND 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 NAMEAND 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 NAMEAND R.ROW_ID = IT.REPOSITORY_ID
AND R.NAME = ‘Siebel Repository’
ORDER BY 1,3
-
The above results summarizes the LOV type, mandatory fields, EIM Tables, Base Tables and Foreign Keys
