informatica list-folders-workflows-sessions-mappings-sources-targets.sql

SELECT

F.SUBJ_NAME AS FOLDER_NAME,

WF.TASK_NAME AS WORKFLOW_NAME,

SE.INSTANCE_NAME AS SESSION_NAME,

M.MAPPING_NAME AS MAPPING_NAME,

SRC.INSTANCE_NAME AS SOURCE_NAME,

TGT.INSTANCE_NAME AS TARGET_NAME

FROM 

OPB_SUBJECT F,

OPB_TASK WF,

(SELECT 

WORKFLOW_ID,

INSTANCE_ID,

TASK_ID,

TASK_TYPE,

INSTANCE_NAME,

MAX(VERSION_NUMBER)

FROM OPB_TASK_INST SESS

WHERE 

SESS.TASK_TYPE=68 

GROUP BY

WORKFLOW_ID,INSTANCE_ID,TASK_ID,TASK_TYPE,INSTANCE_NAME) SE,

(SELECT SESSION_ID,MAPPING_ID,MAX(VERSION_NUMBER) FROM OPB_SESSION GROUP BY SESSION_ID,MAPPING_ID) S,

opb_mapping m,

(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) from opb_widget_inst GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) SRC,

(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) from opb_widget_inst GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) TGT

where WF.IS_VISIBLE = 1

AND WF.SUBJECT_ID = F.SUBJ_ID

AND SE.WORKFLOW_ID = WF.TASK_ID

AND WF.TASK_TYPE = 71

AND se.task_id = s.session_id

AND s.mapping_id = m.mapping_id

AND M.IS_VISIBLE = 1

AND SRC.MAPPING_ID=m.mapping_id

AND SRC.WIDGET_TYPE=1

AND TGT.MAPPING_ID=m.mapping_id

AND TGT.WIDGET_TYPE=2

ORDER BY FOLDER_NAME , WORKFLOW_NAME , SESSION_NAME

-- AND F.SUBJ_NAME = 'MY_FOLDER'