Query related to workflow in informatica

List workflow names

 SELECT   SUBJECT_AREA, TASK_TYPE_NAME, TASK_NAME

    FROM REP_ALL_TASKS

   WHERE TASK_TYPE IN (71) 

         --AND SUBJECT_AREA= 'ABC'

ORDER BY 1, 2, 3

  

List save workflow log count

SELECT DISTINCT

A.SUBJECT_AREA,

A.TASK_NAME AS WORKFLOW_NAME,

B.ATTR_VALUE AS SAVEWFLOG

FROM

REP_ALL_TASKS A ,

REP_TASK_ATTR  B

WHERE

A.TASK_ID = B.TASK_ID

AND B.ATTR_ID = '4'

AND B.TASK_TYPE = 71

--AND B.ATTR_VALUE NOT IN (8,4) 

-- AND A.SUBJECT_AREA = 'ABC'

ORDER BY 1,2,3

  List workflow log names

 SELECT DISTINCT

      SUBJ_NAME,

      WORKFLOW_NAME,

      SUBSTR(LOG_FILE,23,300) AS EXISTING_WFLOGNAME

FROM

      OPB_WFLOW_RUN,

      OPB_SUBJECT

WHERE

       OPB_WFLOW_RUN.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID

       AND SUBSTR(LOG_FILE,23,300) != CONCAT(LOWER(WORKFLOW_NAME),'.log')

ORDER BY 1,2

Informatica

 List write backward compatible check

 SELECT DISTINCT

A.SUBJECT_AREA,A.TASK_NAME,

DECODE (B.ATTR_VALUE,0,'TO BE CHECKED',1,'CHECKED') WRITEBACKWARDCOMPATIBLE

FROM

REP_ALL_TASKS A,

OPB_TASK_ATTR B

WHERE

A.TASK_ID = B.TASK_ID

AND B.TASK_TYPE IN 71

AND B.ATTR_ID = 12

AND B.ATTR_VALUE <> 1

List fail_parent_if_task_fails objects

 SELECT

   REPOSITORY,

   FOLDER_NAME,

   WORKFLOW_OR_WORKLET,

   TASK_TYPE,

   WORKLET_OR_SESSION,

   FAIL_PARENT_IF_TASK_FAILS

FROM

(SELECT DISTINCT   

OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,

OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME,

OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,

DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL',NULL) TASK_TYPE,

OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION,

DECODE (BITAND (OPB_TASK_INST.BIT_OPTIONS, 17),17,'SELECTED','NOT SELECTED') AS FAIL_PARENT_IF_TASK_FAILS

FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,

   OPB_SUBJECT, OPB_REPOSIT_INFO

WHERE OPB_TASK_INST.TASK_TYPE != 62

AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID

AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID

AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER

AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID

AND OPB_TASK.UTC_CHECKIN <> 0

--AND OPB_SUBJECT.SUBJ_NAME NOT LIKE 'WA%'

 )

WHERE FAIL_PARENT_IF_TASK_FAILS <> 'SELECTED'

 List fail_parent_if_task_dont_run objects

SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,WORKLET_OR_SESSION,FAIL_PARENT_IF_TASK_DONT_RUN

FROM

            (SELECT DISTINCT         

         OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,

OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME, OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,

DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL',NULL) TASK_TYPE, OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION, DECODE (BITAND (OPB_TASK_INST.BIT_OPTIONS, 49),49,'SELECTED','NOT SELECTED') AS FAIL_PARENT_IF_TASK_DONT_RUN

            FROM OPB_TASK_INST, OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO

            WHERE OPB_TASK_INST.TASK_TYPE != 62

       AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID

       AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID

       AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER

       AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID

       AND OPB_TASK.UTC_CHECKIN <> 0  )

WHERE FAIL_PARENT_IF_TASK_DONT_RUN <> 'SELECTED'

ORDER BY 2

List is_task_enabled objects

SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,WORKLET_OR_SESSION,IS_TASK_ENABLED

FROM

            (SELECT DISTINCT         

          OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,

OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME, OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,

DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL', NULL) TASK_TYPE, OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION,

          DECODE (OPB_TASK_INST.IS_ENABLED,1, 'ENABLED','DISABLED') AS IS_TASK_ENABLED

            FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO

            WHERE OPB_TASK_INST.TASK_TYPE != 62

                     AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID

                    AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID

                    AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER

                   AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID

                  AND OPB_TASK.UTC_CHECKIN <> 0 )

WHERE IS_TASK_ENABLED = 'DISABLED'

ORDER BY 2,3

  List treat_input_links_as objects

SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,

WORKLET_OR_SESSION,TREAT_INPUT_LINKS_AS

FROM

(SELECT DISTINCT         

OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,

OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME,

OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,

DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL',NULL) TASK_TYPE,

OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION,

DECODE (BITAND (OPB_TASK_INST.BIT_OPTIONS, 3),1,'AND',2, 'OR') AS TREAT_INPUT_LINKS_AS

FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO WHERE OPB_TASK_INST.TASK_TYPE != 62

AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID

AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID

AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER

AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID

AND OPB_TASK.UTC_CHECKIN <> 0

)

--WHERE TREAT_INPUT_LINKS_AS = 'OR'

ORDER BY 2,3

 List all workflows whose server is not assigned

SELECT SUBJECT_AREA,WORKFLOW_NAME,SERVER_NAME

FROM REP_WORKFLOWS

WHERE SERVER_NAME IS NULL

List of workflow run details

SELECT DISTINCT OPB_SUBJECT.SUBJ_NAME,OPB_WFLOW_RUN.WORKFLOW_NAME, 

OPB_TASK_INST_RUN.INSTANCE_NAME,

TRUNC(OPB_TASK_INST_RUN.START_TIME) LOAD_DATE,

TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24:MI.SS') SESS_START_TIME,

TO_CHAR(OPB_TASK_INST_RUN.END_TIME,'HH24:MI.SS') SESS_END_TIME,

TRUNC((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) * 1440,2) DURATION_IN_MINS

FROM OPB_SUBJECT,OPB_TASK_INST_RUN,OPB_WFLOW_RUN,OPB_OBJECT_TYPE, OPB_SESS_TASK_LOG   

WHERE

  

  OPB_SUBJECT.SUBJ_ID = OPB_TASK_INST_RUN.SUBJECT_ID

     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_TASK_INST_RUN.WORKFLOW_ID

     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID

     AND OPB_TASK_INST_RUN.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID

     AND OPB_OBJECT_TYPE.OBJECT_TYPE = 68

     AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_SESS_TASK_LOG.WORKFLOW_ID

     AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID

     AND OPB_TASK_INST_RUN.WORKLET_RUN_ID = OPB_SESS_TASK_LOG.WORKLET_RUN_ID

     AND OPB_TASK_INST_RUN.INSTANCE_ID = OPB_SESS_TASK_LOG.INSTANCE_ID

ORDER BY 1,2,4