ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Report of list of KMs in each Interface

This query lists all the KMs used in each Interfaces under a particular Folder or Project with Interface  Name , Interface Type ie. Permanent_Interface (Blue) or Temporary_Interface (Yellow) ,LKM , IKM , CKM and Source Set Number since there can be multiple Sources feeding a single target in an Interface.

SELECT SP.pop_name AS INTERFACE_NAME ,
CASE WHEN SP.WSTAGE=’E’ THEN ‘TEMP_INTERFACE’
ELSE ‘PERM_INTERFACE’
END AS INTERFACE_TYPE,
NVL(STL.trt_name,’None’) AS LKM_USED ,
NVL(STI.trt_name,’None’) AS IKM_USED ,
NVL(SSS.src_set_name,’None’) AS INPUT_SET_NAME ,
NVL(STC.TRT_NAME,’NONE’) AS CKM_USED
FROM snp_pop SP
LEFT OUTER JOIN snp_src_set SSS
ON SP.i_pop = SSS.i_pop
LEFT OUTER JOIN snp_trt STL
ON SSS.i_trt_klm = STL.i_trt
LEFT OUTER JOIN snp_folder SF
ON SP.i_folder = SF.i_folder
LEFT OUTER JOIN snp_trt STI
ON STI.i_trt = SP.i_trt_kim
LEFT OUTER JOIN SNP_TRT STC
ON stc.i_trt = SP.I_TRT_KCM
LEFT OUTER JOIN snp_project SPJ
ON SPJ.i_project = SF.i_project
WHERE SF.folder_name = ‘<FOLDER_NAME>’
AND spj.project_name = ‘<PROJECT_NAME>’
ORDER BY SP.pop_name, SSS.src_set_name;

Sample Output

image

3 Comments

Leave a Reply

Required fields are marked *.


This site uses Akismet to reduce spam. Learn how your comment data is processed.