3 Replies Latest reply on Sep 24, 2020 8:33 PM by Tharini V

    Query to fetch the sessions, mappings and worklets details that are associated with the workflow

    Venkat Bapathu New Member

      Hi All,

       

      I Need a repository metadata query to fetch the session, mapping and worklet details that are associated with the workflow.

       

      I did followed the below query but not getting the worklet details that are associated with the workflow.

       

      SELECT

             F.SUBJ_NAME  As   FOLDER_NAME,

             WF.TASK_NAME       AS WORKFLOW_NAME,

             SESS.INSTANCE_NAME AS SESSION_NAME,

             M.MAPPING_NAME     AS MAPPING_NAME

      FROM   OPB_SUBJECT F,

             (SELECT TASK_NAME,TASK_ID,

                     SUBJECT_ID,IS_VISIBLE,

                     TASK_TYPE,MAX(VERSION_NUMBER)

              FROM   OPB_TASK

              GROUP  BY TASK_NAME,TASK_ID,SUBJECT_ID,IS_VISIBLE,

                        TASK_TYPE) WF,

             (SELECT WORKFLOW_ID,INSTANCE_ID,

                     TASK_ID,INSTANCE_NAME

              FROM   OPB_TASK_INST

              WHERE  ( WORKFLOW_ID, INSTANCE_ID, TASK_ID, VERSION_NUMBER ) IN

              (SELECT   WORKFLOW_ID,INSTANCE_ID,

                     TASK_ID,VERSION_NUMBER

                                   FROM

                     (SELECT WORKFLOW_ID,INSTANCE_ID,

                             TASK_ID,

                             MAX(VERSION_NUMBER)

                                AS VERSION_NUMBER

                     FROM  OPB_TASK_INST

                             GROUP  BY WORKFLOW_ID,INSTANCE_ID,

                                       TASK_ID))) WL,

             (SELECT WORKFLOW_ID,INSTANCE_ID,

                     TASK_ID,INSTANCE_NAME,

                     TASK_TYPE

              FROM   OPB_TASK_INST

              WHERE  ( WORKFLOW_ID, INSTANCE_ID, TASK_ID, VERSION_NUMBER ) IN

              (SELECT   WORKFLOW_ID,INSTANCE_ID,

                     TASK_ID,VERSION_NUMBER

                               FROM

                     (SELECT  WORKFLOW_ID,INSTANCE_ID,

                             TASK_ID,MAX(VERSION_NUMBER)

                               AS VERSION_NUMBER

                     FROM   OPB_TASK_INST

                            GROUP  BY WORKFLOW_ID,INSTANCE_ID,TASK_ID))) SESS,

             (SELECT SESSION_ID,MAPPING_ID,

                     MAX(VERSION_NUMBER)

              FROM   OPB_SESSION

              GROUP  BY SESSION_ID,MAPPING_ID) S,

             (SELECT MAPPING_NAME,IS_VISIBLE,

                     MAPPING_ID,MAX(VERSION_NUMBER)

              FROM   OPB_MAPPING

              GROUP  BY MAPPING_NAME,IS_VISIBLE,MAPPING_ID) M

      WHERE  WF.IS_VISIBLE = 1

             AND WF.SUBJECT_ID = F.SUBJ_ID

             AND WF.TASK_ID = WL.WORKFLOW_ID

             AND WF.TASK_TYPE = 71

             AND ( WL.TASK_ID = SESS.WORKFLOW_ID

                    OR WL.WORKFLOW_ID = SESS.WORKFLOW_ID )

             AND WF.IS_VISIBLE = 1

             AND SESS.TASK_TYPE IN (  58,68 )

             AND SESS.TASK_ID = S.SESSION_ID

             AND S.MAPPING_ID = M.MAPPING_ID

             AND M.IS_VISIBLE = 1

             AND F.SUBJ_NAME = 'FOLDER_NAME'

             AND WF.TASK_NAME ='WORKFLOW_NAME'

             group by F.SUBJ_NAME,WF.TASK_NAME,SESS.INSTANCE_NAME,M.MAPPING_NAME