Como Automatizar Siebel EIM Mapping con SQL

Tomado de www.onlysiebel.blogspot.com mejorando el script del artículo "Automate Siebel EIM Mapping using SQL (Oracle) Just Replace"

SELECT   EIM_TAB.NAME "EIM Table",
         EIM_COL.NAME "EIM Column Name",
         EIM_COL.USER_NAME "EIM Column User Name",
         CASE EIM_COL.DATA_TYPE
            WHEN 'V'
               THEN 'Varchar'
            WHEN 'C'
               THEN 'Character'
            WHEN 'N'
               THEN 'Number'
            WHEN 'U'
               THEN 'Date/Time'
            WHEN 'D'
               THEN 'Date/Time'
            WHEN 'S'
               THEN 'Date/Time'
         END AS "EIM Data Type",
         EIM_COL.LENGTH "EIM Length",
         BASE_TAB.NAME "Base Table ",
         BASE_COL.NAME "Base Column Name",
         BASE_COL.USER_NAME "Base Column User Name",
         BASE_COL.REQUIRED "Required Flag",
         BASE_COL.INACTIVE_FLG "Inactive Flag",
         BASE_COL.USR_KEY_SEQUENCE "User Key",
         CASE BASE_COL.DATA_TYPE
            WHEN 'V'
               THEN 'Varchar'
            WHEN 'C'
               THEN 'Character'
            WHEN 'N'
               THEN 'Number'
            WHEN 'U'
               THEN 'Date/Time'
            WHEN 'D'
               THEN 'Date/Time'
            WHEN 'S'
               THEN 'Date/Time'
         END AS "Data Type",
         BASE_COL.LENGTH "Length",
         BASE_COL.LOV_BOUNDED,
         BASE_COL.LOV_TYPE_CD,
         ISNULL
             (CASE
                 WHEN BASE_COL.DEFAULT_VAL IS NOT NULL
                    THEN BASE_COL.DEFAULT_VAL
                 ELSE (   (CASE BASE_COL.LOV_BOUNDED
                              WHEN 'Y'
                                 THEN 'LOVB-->'
                              ELSE NULL
                           END
                          )
                       || BASE_COL.LOV_TYPE_CD
                      )
              END,
              ''
             ) AS "Default"
FROM     SIEBEL.S_TABLE EIM_TAB INNER JOIN SIEBEL.S_REPOSITORY REP
         ON EIM_TAB.REPOSITORY_ID = REP.ROW_ID
         INNER JOIN SIEBEL.S_EIM_TBL_MAP BASE_TAB
         ON BASE_TAB.IF_TBL_ID = EIM_TAB.ROW_ID
         INNER JOIN SIEBEL.S_EIM_ATT_MAP ATT_MAP
         ON BASE_TAB.ROW_ID = ATT_MAP.EIM_TBL_MAP_ID
         LEFT OUTER JOIN SIEBEL.S_COLUMN BASE_COL
         ON BASE_COL.ROW_ID = ATT_MAP.BTAB_ATT_COL_ID
         LEFT OUTER JOIN SIEBEL.S_COLUMN EIM_COL
         ON EIM_COL.ROW_ID = ATT_MAP.IFTAB_DATA_COL_ID
WHERE    EIM_TAB.NAME = 'EIM_FN_ASSET1'
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,
         EIM_COL.USER_NAME,
         EIM_COL.DATA_TYPE,
         EIM_COL.LENGTH,
         BASE_TAB.NAME,
         BASE_COL.NAME,
         BASE_COL.USER_NAME,
         BASE_COL.REQUIRED,
         BASE_COL.INACTIVE_FLG,
         BASE_COL.USR_KEY_SEQUENCE,
         BASE_COL.DATA_TYPE,
         BASE_COL.LENGTH,
         BASE_COL.LOV_BOUNDED,
         BASE_COL.LOV_TYPE_CD,
         BASE_COL.DEFAULT_VAL,
         BASE_COL.LOV_BOUNDED,
         BASE_COL.LOV_TYPE_CD
UNION ALL
SELECT   T6.NAME "EIM Table",
         REPLACE (T2.NAME, '_BI', '_BU') "EIM Column Name",
         T2.USER_NAME "EIM Column User Name",
         CASE T2.DATA_TYPE
            WHEN 'V'
               THEN 'Varchar'
            WHEN 'C'
               THEN 'Character'
            WHEN 'N'
               THEN 'Number'
            WHEN 'U'
               THEN 'Date/Time'
            WHEN 'D'
               THEN 'Date/Time'
            WHEN 'S'
               THEN 'Date/Time'
         END AS "EIM Data Type",
         T2.LENGTH "EIM Length",
         T5.NAME "Base Table",
         T3.NAME "Base Column",
         COLS.USER_NAME "Base Column User Name",
         T2.REQUIRED "Required Flag",
         T2.INACTIVE_FLG "Inactive Flag",
         T2.USR_KEY_SEQUENCE "User Key",
         COLS.DATA_TYPE "Data Type",
         COLS.LENGTH "Length",
         COLS.LOV_BOUNDED,
         COLS.LOV_TYPE_CD,
         ISNULL (CASE
                    WHEN T2.DEFAULT_VAL IS NOT NULL
                       THEN T2.DEFAULT_VAL
                    ELSE (   (CASE T2.LOV_BOUNDED
                                 WHEN 'Y'
                                    THEN 'LOVB-->'
                                 ELSE NULL
                              END
                             )
                          || T2.LOV_TYPE_CD
                         )
                 END,
                 ''
                ) AS "Default"
FROM     SIEBEL.S_EIM_FK_MAPCOL T1 INNER JOIN SIEBEL.S_COLUMN T2
         ON T1.IFTAB_COL_ID = T2.ROW_ID
         INNER JOIN
         (SIEBEL.S_EIM_FK_MAP T3
         INNER JOIN
         (SELECT S_TABLE.NAME TBL,
                 S_TABLE.ROW_ID TBL_ID,
                 S_COLUMN.NAME COL,
                 S_COLUMN.USER_NAME,
                 S_COLUMN.ROW_ID COL_ID,
                 CASE S_COLUMN.DATA_TYPE
                    WHEN 'V'
                       THEN 'Varchar'
                    WHEN 'C'
                       THEN 'Character'
                    WHEN 'N'
                       THEN 'Number'
                    WHEN 'U'
                       THEN 'Date/Time'
                    WHEN 'D'
                       THEN 'Date/Time'
                    WHEN 'S'
                       THEN 'Date/Time'
                 END AS DATA_TYPE,
                 S_COLUMN.LENGTH,
                 S_COLUMN.LOV_BOUNDED,
                 S_COLUMN.LOV_TYPE_CD
          FROM   (SIEBEL.S_TABLE INNER JOIN SIEBEL.S_COLUMN
                 ON S_TABLE.ROW_ID = S_COLUMN.TBL_ID)
                 ) COLS ON COLS.COL = T3.NAME)
         ON T1.EIM_FK_MAP_ID = T3.ROW_ID
         INNER JOIN SIEBEL.S_USER_KEY_ATT T4 ON T1.USER_KEY_ATT_ID = T4.ROW_ID
         LEFT OUTER JOIN SIEBEL.S_EIM_TBL_MAP T5 ON T3.EIM_TBL_MAP_ID =
                                                                     T5.ROW_ID
         LEFT OUTER JOIN SIEBEL.S_TABLE T6 ON T5.IF_TBL_ID = T6.ROW_ID
         LEFT OUTER JOIN SIEBEL.S_PROJECT T7 ON T6.PROJECT_ID = T7.ROW_ID
         LEFT OUTER JOIN SIEBEL.S_REPOSITORY T8 ON T1.REPOSITORY_ID =
                                                                     T8.ROW_ID
         LEFT OUTER JOIN SIEBEL.S_COLUMN T9 ON T1.MAP_COL_ID = T9.ROW_ID
WHERE    T6.NAME = 'EIM_FN_ASSET1'
AND      T5.NAME = COLS.TBL
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,
         T2.NAME,
         T2.USER_NAME,
         T2.DATA_TYPE,
         T2.LENGTH,
         T5.NAME,
         T3.NAME,
         COLS.USER_NAME,
         T2.REQUIRED,
         T2.INACTIVE_FLG,
         T2.USR_KEY_SEQUENCE,
         COLS.DATA_TYPE,
         COLS.LENGTH,
         COLS.LOV_BOUNDED,
         COLS.LOV_TYPE_CD,
         T2.DEFAULT_VAL,
         T2.LOV_BOUNDED,
         T2.LOV_TYPE_CD
UNION ALL
SELECT   T4.NAME "EIM Table",
         T2.NAME "EIM Column Name",
         T2.USER_NAME "EIM User Name",
         CASE T2.DATA_TYPE
            WHEN 'V'
               THEN 'Varchar'
            WHEN 'C'
               THEN 'Character'
            WHEN 'N'
               THEN 'Number'
            WHEN 'U'
               THEN 'Date/Time'
            WHEN 'D'
               THEN 'Date/Time'
            WHEN 'S'
               THEN 'Date/Time'
         END AS "EIM Data Type",
         T2.LENGTH,
         T3.NAME "Base Column",
         T8.NAME "Base Column Name",
         T8.USER_NAME "Base Column User Name",
         T8.REQUIRED "Required Flag",
         T8.INACTIVE_FLG "Inactive Flag",
         T8.USR_KEY_SEQUENCE "User Key",
         CASE T8.DATA_TYPE
            WHEN 'V'
               THEN 'Varchar'
            WHEN 'C'
               THEN 'Character'
            WHEN 'N'
               THEN 'Number'
            WHEN 'U'
               THEN 'Date/Time'
            WHEN 'D'
               THEN 'Date/Time'
            WHEN 'S'
               THEN 'Date/Time'
         END AS "Data Type",
         T8.LENGTH "Length",
         T8.LOV_BOUNDED,
         T8.LOV_TYPE_CD,
         ISNULL (CASE
                    WHEN T8.DEFAULT_VAL IS NOT NULL
                       THEN T8.DEFAULT_VAL
                    ELSE (   (CASE T8.LOV_BOUNDED
                                 WHEN 'Y'
                                    THEN 'LOVB-->'
                                 ELSE NULL
                              END
                             )
                          || T8.LOV_TYPE_CD
                         )
                 END,
                 ''
                ) AS "Default"
FROM     SIEBEL.S_EIM_EXPPR_MAP T1 INNER JOIN SIEBEL.S_REPOSITORY REP
         ON T1.REPOSITORY_ID = REP.ROW_ID
         INNER JOIN SIEBEL.S_COLUMN T2
         ON T1.IFTAB_PRFLG_COL_ID = T2.ROW_ID               -- EIM COLUMN NAME
         INNER JOIN SIEBEL.S_COLUMN T8
         ON T1.BTAB_PC_COL_ID = T8.ROW_ID                  -- BASE COLUMN NAME
         LEFT OUTER JOIN SIEBEL.S_EIM_TBL_MAP T3 ON T1.EIM_TBL_MAP_ID =
                                                                     T3.ROW_ID
         LEFT OUTER JOIN SIEBEL.S_TABLE T4 ON T3.IF_TBL_ID = T4.ROW_ID
         LEFT OUTER JOIN SIEBEL.S_COLUMN T5 ON T1.BTAB_PC_COL_ID = T5.ROW_ID
         LEFT OUTER JOIN SIEBEL.S_PROJECT T6 ON T4.PROJECT_ID = T6.ROW_ID
         LEFT OUTER JOIN SIEBEL.S_REPOSITORY T7 ON T1.REPOSITORY_ID =
                                                                     T7.ROW_ID
WHERE    T4.NAME = 'EIM_FN_ASSET1'
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,
         T2.NAME,
         T2.USER_NAME,
         T2.DATA_TYPE,
         T2.LENGTH,
         T3.NAME,
         T8.NAME,
         T8.USER_NAME,
         T8.REQUIRED,
         T8.INACTIVE_FLG,
         T8.USR_KEY_SEQUENCE,
         T8.DATA_TYPE,
         T8.LENGTH,
         T8.LOV_BOUNDED,
         T8.LOV_TYPE_CD,
         T8.DEFAULT_VAL,
         T8.LOV_BOUNDED,
         T8.LOV_TYPE_CD;