Generate EIM mapping using SQL Script

Hi Folks,

The current Siebel 8.1 Tools does not have the Reports Tab, which in Siebel 8.0 and previous versions generated a mapping sheet with all the base tables, base columns mapping.

To generate this in the 8.1 application just enter the below code with the EIM table name whose mapping you want to generate.

and the code does the rest...

you can also add other attributes as per your requirments, like data type of the Columns, length etc...

------------------------------------------------------------------------------------------------
use siebel81db

SELECT EIM_TAB.NAME "EIM Table"
, EIM_COL.NAME "EIM Column Name"
, BASE_TAB.NAME "Base Table "
, BASE_COL.NAME "Base Column Name"
, BASE_COL.REQUIRED "Required Flag"
, BASE_COL.INACTIVE_FLG "Inactive Flag"
FROM S_TABLE EIM_TAB
INNER JOIN S_REPOSITORY REP ON EIM_TAB.REPOSITORY_ID = REP.ROW_ID
INNER JOIN S_EIM_TBL_MAP BASE_TAB ON BASE_TAB.IF_TBL_ID = EIM_TAB.ROW_ID
INNER JOIN S_EIM_ATT_MAP ATT_MAP ON BASE_TAB.ROW_ID = ATT_MAP.EIM_TBL_MAP_ID
LEFT OUTER JOIN S_COLUMN BASE_COL ON BASE_COL.ROW_ID = ATT_MAP.BTAB_ATT_COL_ID
LEFT OUTER JOIN S_COLUMN EIM_COL ON EIM_COL.ROW_ID = ATT_MAP.IFTAB_DATA_COL_ID
WHERE EIM_TAB.NAME = 'EIM TABLE NAME'
AND ISNULL(EIM_TAB.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(REP.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(BASE_TAB.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(ATT_MAP.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(BASE_COL.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(EIM_COL.INACTIVE_FLG, 'N') = 'N'
GROUP BY EIM_TAB.NAME
, EIM_COL.NAME
, BASE_TAB.NAME
, BASE_COL.NAME
, BASE_COL.REQUIRED
, BASE_COL.INACTIVE_FLG
UNION ALL
SELECT T6.NAME "EIM Table"
, replace(T2.NAME, '_BI', '_BU') "EIM Column Name"
, T5.NAME "Base Table"
, T3.NAME "Base Column"
, T2.REQUIRED "Required Flag"
, T2.INACTIVE_FLG "Inactive Flag"
FROM
S_EIM_FK_MAPCOL T1
INNER JOIN S_COLUMN T2 ON T1.IFTAB_COL_ID = T2.ROW_ID
INNER JOIN S_EIM_FK_MAP T3 ON T1.EIM_FK_MAP_ID = T3.ROW_ID
INNER JOIN S_USER_KEY_ATT T4 ON T1.USER_KEY_ATT_ID = T4.ROW_ID
LEFT OUTER JOIN S_EIM_TBL_MAP T5 ON T3.EIM_TBL_MAP_ID = T5.ROW_ID
LEFT OUTER JOIN S_TABLE T6 ON T5.IF_TBL_ID = T6.ROW_ID
LEFT OUTER JOIN S_PROJECT T7 ON T6.PROJECT_ID = T7.ROW_ID
LEFT OUTER JOIN S_REPOSITORY T8 ON T1.REPOSITORY_ID = T8.ROW_ID
LEFT OUTER JOIN S_COLUMN T9 ON T1.MAP_COL_ID = T9.ROW_ID
WHERE
T6.NAME = 'EIM TABLE NAME'
AND ISNULL(T1.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T2.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T3.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T4.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T5.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T6.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T7.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T8.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T9.INACTIVE_FLG, 'N') = 'N'
GROUP BY T6.NAME
, T3.NAME
, T5.NAME
, T2.NAME
, T2.REQUIRED
, T2.INACTIVE_FLG
UNION ALL
SELECT T4.NAME "EIM Table"
, T2.NAME "EIM Column Name"
, T3.NAME "Base Column"
, T8.NAME "Base Column Name"
, T8.REQUIRED "Required Flag"
, T8.INACTIVE_FLG "Inactive Flag"
FROM S_EIM_EXPPR_MAP T1
INNER JOIN S_REPOSITORY REP ON T1.REPOSITORY_ID = REP.ROW_ID
INNER JOIN S_COLUMN T2 ON T1.IFTAB_PRFLG_COL_ID = T2.ROW_ID -- EIM COLUMN NAME
INNER JOIN S_COLUMN T8 ON T1.BTAB_PC_COL_ID = T8.ROW_ID -- BASE COLUMN NAME
LEFT OUTER JOIN S_EIM_TBL_MAP T3 ON T1.EIM_TBL_MAP_ID = T3.ROW_ID
LEFT OUTER JOIN S_TABLE T4 ON T3.IF_TBL_ID = T4.ROW_ID
LEFT OUTER JOIN S_COLUMN T5 ON T1.BTAB_PC_COL_ID = T5.ROW_ID
LEFT OUTER JOIN S_PROJECT T6 ON T4.PROJECT_ID = T6.ROW_ID
LEFT OUTER JOIN S_REPOSITORY T7 ON T1.REPOSITORY_ID = T7.ROW_ID
WHERE T4.NAME = 'EIM TABLE NAME'
AND ISNULL(T1.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T2.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T3.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T4.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T5.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T6.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T7.INACTIVE_FLG, 'N') = 'N'
AND ISNULL(T8.INACTIVE_FLG, 'N') = 'N'
GROUP BY T4.NAME
, T3.NAME
, T2.NAME
, T8.NAME
, T8.REQUIRED
, T8.INACTIVE_FLG

------------------------------------------------------------------------------------------------

Cheers,
-Ryo