Query related to worklet in informatica

List worklet names

SELECT SUBJECT_AREA, TASK_TYPE_NAME, TASK_NAME

FROM REP_ALL_TASKS

WHERE TASK_TYPE IN (70)

--AND SUBJECT_AREA= 'ABC'

ORDER BY 1, 2, 3

Informatica

List hierarchies of all workflows and its worklets

SELECT DISTINCT '/' || temp1.task_id AS path, temp1.task_name AS hierarchy_structure

FROM opb_task temp1, opb_subject temp2

WHERE temp1.subject_id = temp2.subj_id

AND temp1.task_type = 71

AND temp2.subj_name = 'FOLDER_NAME'

UNION ALL

SELECT DISTINCT temp1.path, temp1.task_name AS hierarchy_structure

FROM (SELECT opb_task_inst.workflow_id,opb_task_inst.task_id,opb_task_inst.instance_id,LEVEL depth,

SYS_CONNECT_BY_PATH(opb_task_inst.workflow_id ,'/') || '/' || opb_task_inst.task_id || '/' path,

LPAD (' ', 4 * LEVEL, ' ') || SYS_CONNECT_BY_PATH(opb_task_inst.instance_name ,'/') task_name

FROM opb_task_inst WHERE opb_task_inst.task_type IN (68,70)

START WITH workflow_id IN (SELECT task_id FROM opb_task WHERE task_type = 71)

CONNECT BY PRIOR opb_task_inst.task_id = opb_task_inst.workflow_id) temp1,

opb_task temp2, opb_subject temp3

WHERE temp2.subject_id = temp3.subj_id

AND temp2.task_id = SUBSTR(temp1.path,2, INSTR(temp1.path,'/', 1, 2) -2 )

AND temp3.subj_name = 'FOLDER_NAME'

ORDER BY path ASC

Datawarehouse Architect