/*
Created by Cheon.jh
Creeted on 2012.05.25
ODI Version. 11.1.1.6
Using on showing the description of all Interface.
*/
SELECT P.TABLE_NAME,
'/*
Description
Job Path: '||F.PROJECT_NAME|| ' - ' ||
F.FOLDER_PATH|| '
Interface Name: ' ||
P.POP_NAME|| '
Source Table: ' ||
LISTAGG(S.LSCHEMA_NAME||'.'||S.TABLE_NAME ,', ') WITHIN GROUP ( ORDER BY S.TAB_ORD) || '
Target Table: ' ||
P.LSCHEMA_NAME||'.'||P.TABLE_NAME||'
*/' SQL_Query
FROM SNP_POP P,
SNP_DATA_SET DS,
SNP_SOURCE_TAB S,
(
SELECT I_FOLDER,
PROJECT_NAME,
SYS_CONNECT_BY_PATH(FOLDER_NAME, '/') FOLDER_PATH
FROM SNP_FOLDER F,
SNP_PROJECT P
WHERE 1=1
AND F.I_PROJECT = P.I_PROJECT
START WITH PAR_I_FOLDER IS NULL
CONNECT BY PRIOR I_FOLDER = PAR_I_FOLDER
) F
WHERE 1=1
AND P.I_POP = DS.I_POP
AND DS.I_DATA_SET = S.I_DATA_SET
AND P.I_FOLDER = F.I_FOLDER
GROUP BY
F.PROJECT_NAME,
F.FOLDER_PATH,
P.POP_NAME,
P.LSCHEMA_NAME,
P.TABLE_NAME