ODI iInterface Mapping Query (Work Repository)

SELECT DISTINCT
       SNP_PROJECT.PROJECT_NAME AS PROJECT_NAME,
       SNP_FOLDER.FOLDER_NAME AS FOLDER_NAME,
       SNP_POP.POP_NAME AS INTERFACE_NAME,
       CASE
          WHEN SNP_POP.WSTAGE = 'E' THEN 'TABLE_TO_TABLE_INF'
          ELSE 'TEMP_INTERFACE'
       END
          AS INTERFACE_TYPE,
       SNP_POP.LSCHEMA_NAME AS TARGET_SCHEMA,
       SNP_POP.TABLE_NAME AS TARGET_TABLE,
       SNP_POP.MOD_CODE AS TARGET_MODEL,
       TARGET_MODEL.LSCHEMA_NAME AS TARGET_LSCHEMA_NAME,
       /*SNP_POP_COL.COL_NAME AS TARGET_COLUMN,
       CASE WHEN SNP_POP.WSTAGE='E'
            THEN T_COL.SOURCE_DT||' ('||T_COL.LONGC||')'
            ELSE SNP_POP_COL.SOURCE_DT||' ('||SNP_POP_COL.LONGC||')'
       END AS TRG_DATATYPE,*/
       SNP_SOURCE_TAB.LSCHEMA_NAME AS SOURCE_SCHEMA,
       SNP_TABLE.TABLE_NAME AS SOURCE_TABLE,
       SNP_SOURCE_TAB.MOD_CODE AS SOURCE_MODEL,
       SOURCE_MODEL.LSCHEMA_NAME  AS SOURCE_LSCHEMA_NAME
       /*, SNP_COL.COL_NAME AS SOURCE_COLUMN ,
       SNP_COL.SOURCE_DT||' ('||SNP_COL.LONGC||')' AS SRC_DATATYPE,
       --rowtocol('SELECT DISTINCT TXT FROM SNP_TXT WHERE I_TXT='||i_txt ) AS COLUMN_MAPPING
       SNP_TXT.TXT AS COLUMN_MAPPING*/
       -- to use without rowtocol use SNP_TXT.TXT AS COLUMN_MAPPING
       FROM SNP_PROJECT
       LEFT OUTER JOIN SNP_FOLDER ON SNP_FOLDER.I_PROJECT=SNP_PROJECT.I_PROJECT
       LEFT OUTER JOIN SNP_POP ON SNP_POP.I_FOLDER=SNP_FOLDER.I_FOLDER
       LEFT OUTER JOIN SNP_POP_COL ON SNP_POP_COL.I_POP=SNP_POP.I_POP
       LEFT OUTER JOIN SNP_POP_CLAUSE ON SNP_POP_CLAUSE.I_POP=SNP_POP.I_POP
       LEFT OUTER JOIN SNP_TXT S_TXT ON S_TXT.I_TXT=SNP_POP_CLAUSE.I_TXT_SQL
       LEFT OUTER JOIN SNP_TXT ON SNP_TXT.I_TXT= SNP_POP_COL.I_TXT_MAP
       LEFT OUTER JOIN SNP_SOURCE_TAB ON SNP_SOURCE_TAB.I_POP=SNP_POP.I_POP
       LEFT OUTER JOIN SNP_TXT_CROSSR ON SNP_TXT_CROSSR.I_TXT=SNP_TXT.I_TXT
       LEFT OUTER JOIN SNP_COL ON SNP_COL.I_COL=SNP_TXT_CROSSR.I_COL
       LEFT OUTER JOIN SNP_TABLE ON SNP_TABLE.I_TABLE= SNP_COL.I_TABLE
       LEFT OUTER JOIN SNP_MODEL TARGET_MODEL ON SNP_POP.I_MOD= TARGET_MODEL.I_MOD
       LEFT OUTER JOIN SNP_MODEL SOURCE_MODEL ON SNP_SOURCE_TAB.I_MOD= SOURCE_MODEL.I_MOD
       --LEFT OUTER JOIN SNP_COL T_COL ON T_COL.I_COL=SNP_POP_COL.I_COL
       --WHERE UPPER(SNP_TABLE.TABLE_NAME) LIKE '%TBLSERVICES%'
ORDER BY SNP_POP.POP_NAME

ODI Topology Connection Detail (Master Repository)

/* 10g MASTER REPOSITIRY TOPOLOGY MAPPING */
SELECT DISTINCT ODIM.SNP_TECHNO.TECHNO_NAME AS TECHNOLOGY,
                  ODIM.SNP_CONNECT.CON_NAME AS DATA_SERVER,
                  ODIM.SNP_CONNECT.USER_NAME AS USERNAME,
                  ODIM.SNP_CONNECT.PASS AS PASSWD,
                  ODIM.SNP_PSCHEMA.EXT_NAME AS PHYSICAL_SCHEMA,
                  ODIM.SNP_PSCHEMA.SCHEMA_NAME AS SCHEMA_NAME,
                  ODIM.SNP_PSCHEMA.WSCHEMA_NAME AS WORK_SCHEMA,
                  ODIM.SNP_CONTEXT.CONTEXT_NAME AS CONTEXT_NAME,
                  ODIM.SNP_LSCHEMA.LSCHEMA_NAME AS LOGICAL_SCHEMA,
                  ODIM.SNP_CONNECT.JAVA_DRIVER AS DRIVER_INFO,
                  ODIM.SNP_MTXT_PART.TXT AS URL
    FROM ODIM.SNP_TECHNO
         LEFT OUTER JOIN ODIM.SNP_CONNECT ON ODIM.SNP_CONNECT.I_TECHNO = ODIM.SNP_TECHNO.I_TECHNO
         LEFT OUTER JOIN ODIM.SNP_PSCHEMA ON ODIM.SNP_PSCHEMA.I_CONNECT = ODIM.SNP_CONNECT.I_CONNECT
         LEFT OUTER JOIN ODIM.SNP_PSCHEMA_CONT ON ODIM.SNP_PSCHEMA_CONT.I_PSCHEMA = ODIM.SNP_PSCHEMA.I_PSCHEMA
         LEFT OUTER JOIN ODIM.SNP_LSCHEMA ON ODIM.SNP_LSCHEMA.I_LSCHEMA = ODIM.SNP_PSCHEMA_CONT.I_LSCHEMA
         LEFT OUTER JOIN ODIM.SNP_CONTEXT ON ODIM.SNP_CONTEXT.I_CONTEXT = ODIM.SNP_PSCHEMA_CONT.I_CONTEXT
         LEFT OUTER JOIN ODIM.SNP_MTXT_PART ON ODIM.SNP_MTXT_PART.I_TXT = ODIM.SNP_CONNECT.I_TXT_JAVA_URL
   WHERE ODIM.SNP_CONNECT.CON_NAME IS NOT NULL
ORDER BY ODIM.SNP_TECHNO.TECHNO_NAME;

11g
  SELECT DISTINCT
         SNP_TECHNO.TECHNO_NAME AS TECHNOLOGY,
         SNP_CONNECT.CON_NAME AS DATA_SERVER,
         SNP_CONNECT.USER_NAME AS USERNAME,
         SNP_CONNECT.PASS AS PASSWD,

         SNP_PSCHEMA.EXT_NAME AS PHYSICAL_SCHEMA,
         SNP_PSCHEMA.SCHEMA_NAME AS SCHEMA_NAME,
         SNP_PSCHEMA.WSCHEMA_NAME AS WORK_SCHEMA,
         SNP_CONTEXT.CONTEXT_NAME AS CONTEXT_NAME,
         SNP_LSCHEMA.LSCHEMA_NAME AS LOGICAL_SCHEMA,
         SNP_CONNECT.JAVA_DRIVER AS DRIVER_INFO,
         COALESCE (SNP_MTXT_PART.TXT, DBMS_LOB.SUBSTR (SNP_MTXT.FULL_TXT, 1000, 1)) AS URL,
         SNP_CONNECT.USER_NAME,
         SNP_CONNECT.PASS
    FROM SNP_TECHNO
         LEFT OUTER JOIN SNP_CONNECT
            ON SNP_CONNECT.I_TECHNO = SNP_TECHNO.I_TECHNO
         LEFT OUTER JOIN SNP_PSCHEMA
            ON SNP_PSCHEMA.I_CONNECT = SNP_CONNECT.I_CONNECT
         LEFT OUTER JOIN SNP_PSCHEMA_CONT
            ON SNP_PSCHEMA_CONT.I_PSCHEMA = SNP_PSCHEMA.I_PSCHEMA
         LEFT OUTER JOIN SNP_LSCHEMA
            ON SNP_LSCHEMA.I_LSCHEMA = SNP_PSCHEMA_CONT.I_LSCHEMA
         LEFT OUTER JOIN SNP_CONTEXT
            ON SNP_CONTEXT.I_CONTEXT = SNP_PSCHEMA_CONT.I_CONTEXT
         LEFT OUTER JOIN SNP_MTXT_PART
            ON SNP_MTXT_PART.I_TXT = SNP_CONNECT.I_TXT_JAVA_URL
         LEFT OUTER JOIN SNP_MTXT
            ON SNP_MTXT.I_TXT = SNP_CONNECT.I_TXT_JAVA_URL
   WHERE SNP_CONNECT.CON_NAME IS NOT NULL
ORDER BY SNP_TECHNO.TECHNO_NAME;

Reference : http://odiexperts.com/odi-topology-connection-detail/