ICC BATCH CALLS AND Associated PMF EXECUTION IDENTIFIER LINKs

Execute the following query to know the PMF process instances executions and the associated ICC batch execution IDs.

SELECT DISTINCT PROCESSDET.V_PROCESS_NAME PROCESSNAME, QUE.V_PROCESS_INSTANCE_ID
          PROCESSINSTANCEID, COALESCE(DATA.V_ACTIVITY_ID,QUE.V_ACTIVITY_ID) JOBID,
            COALESCE(ACTIVITYDET.V_ACTIVITY_NAME,ACTIVITYDET.V_ACTIVITY_DESC)JOBNAME,
          COALESCE(DATA.STARTDATE,QUE.D_EXECUTION_START_TIME) STARTTIME,
          COALESCE(DATA.ENDDATE,QUE.D_EXECUTION_END_TIME) ENDTIME,
          NVL(QUE.V_EXECUTION_STATUS,QUE.V_STATUS) JOBSTATUS, DATA.V_BATCH_RUN_ID, DATA.V_TASK_DESCRIPTION,DATA.V_BATCH_DESCRIPTION,DATA.V_INFO_DATE, DATA.V_BATCH_STATUS, DATA.V_BATCH_ID, DATA.V_GROUP_CODE,DATA.APP_BATCH_RUN_ID, MAP.V_OBJECT_ID, AWPE.V_INSTANCE_PROPERTY FROMAAI_WF_PROCESS_ENGINE_ACT_QUE QUE INNERJOIN (SELECT PROCESSB.V_PROCESS_ID, PROCESSTL.V_PROCESS_NAME FROM AAI_WF_PROCESS_B PROCESSB, AAI_WF_PROCESS_TL
          PROCESSTLWHEREPROCESSB.V_PROCESS_ID = PROCESSTL.V_PROCESS_ID AND PROCESSTL.V_LOCALE_CODE =
        'en_US')PROCESSDETON(PROCESSDET.V_PROCESS_ID = SUBSTR (QUE.V_PROCESS_INSTANCE_ID,0,INSTR QUE.V_PROCESS_INSTANCE_ID,'_',-1)
          -1))INNERJOIN AAI_WF_APP_INSTANCE_MAP MAPON (MAP.V_PROCESS_INSTANCE_ID = QUE.V_PROCESS_INSTANCE_ID AND
            AP.V_PROCESS_INSTANCE_ID= '1702359232479_01991722-baf2-4f08-ba61-8cd2d86e12a')INNERJOIN AAI_WF_PROCESS_EXECUTION AWPEON (AWPE.V_PROCESS_ID = PROCESSDET.V_PROCESS_ID AND WPE.V_PROCESS_INSTANCE_ID= '1702359232479_01991722-baf2-4f08-ba61-8cd2d86e12a') INNERJOIN (SELECTACTB.V_PROCESS_ID, ACTTL.V_ACTIVITY_NAME, ACTB.V_ACTIVITY_ID, ACTTL.V_ACTIVITY_DESC,
          ACTTL.V_LOCALE_CODEFROMAAI_WF_ACTIVITY_B ACTB, AAI_WF_ACTIVITY_TL ACTTL WHERE
          ACTB.V_PROCESS_ID = ACTTL.V_PROCESS_IDANDACTB.V_ACTIVITY_ID = ACTTL.V_ACTIVITY_ID AND ACTB.V_EXECUTION_MODE != 'START'ANDACTTL.V_LOCALE_CODE = 'en_US'ANDACTB.V_PROCESS_ID = ACTTL.V_PROCESS_ID)ACTIVITYDETON( ACTIVITYDET.V_PROCESS_ID = SUBSTR (QUE.V_PROCESS_INSTANCE_ID,0,INSTR QUE.V_PROCESS_INSTANCE_ID,'_',-1)
          -1)ANDACTIVITYDET.V_ACTIVITY_ID = QUE.V_ACTIVITY_ID ) INNERJOIN (SELECTDISTINCT WFTASK.V_PROCESS_INSTANCE_ID, WFTASK.V_ACTIVITY_ID, WFTASK.V_TASK_REF_ID,WFTASK.V_IS_PROCESSED,WFTASK.D_DATE_PROCESSED, BATCHINFO.V_BATCH_RUN_ID, BATCHINFO.V_TASK_DESCRIPTION,BATCHINFO.V_BATCH_DESCRIPTION, BATCHINFO.V_INFO_DATE, BATCHINFO.V_BATCH_STATUS,
          BATCHINFO.V_BATCH_ID, BATCHINFO.V_GROUP_CODE,BATCHINFO.STARTDATE, BATCHINFO.ENDDATE, BATCHINFO.APP_BATCH_RUN_IDFROMAAI_WF_ASYNC_TASK_POLL_STATUS WFTASK INNER JOIN (SELECTDISTINCT BR.V_BATCH_RUN_ID, BT.V_TASK_DESCRIPTION, V_BATCH_DESCRIPTION,V_INFO_DATE,V_BATCH_STATUS, V_BATCH_ID, V_GROUP_CODE, IM.STARTDATE, IM.ENDDATE,REGEXP_REPLACE(BT.V_BATCH_RUN_ID,'',''|| BT.V_TASK_ID || '_',1,1) APP_BATCH_RUN_IDFROMBATCH_RUN BR, BATCH_TASK BT,(SELECT MSGLOG.V_BATCH_RUN_ID, MAX(MSGLOG.TIMESTAMP) ENDDATE,
          IN(MSGLOG.TIMESTAMP) STARTDATEFROMICC_MESSAGELOG MSGLOG GROUP BY V_BATCH_RUN_ID) IMWHEREBR.V_BATCH_RUN_ID = BT.V_BATCH_RUN_ID AND BR.V_BATCH_RUN_ID = M.V_BATCH_RUN_ID)BATCHINFO ON (BATCHINFO.V_BATCH_ID = WFTASK.V_TASK_REF_ID) WHERE FTASK.V_PROCESS_INSTANCE_ID= '1702359232479_01991722-baf2-4f08-ba61-8cd2d86e12a'UNIONSELECTDISTINCT WFTASKHIST.V_PROCESS_INSTANCE_ID, WFTASKHIST.V_ACTIVITY_ID, WFTASKHIST.V_TASK_REF_ID,WFTASKHIST.V_IS_PROCESSED, WFTASKHIST.D_DATE_PROCESSED, BATCHINFO.V_BATCH_RUN_ID,
          BATCHINFO.V_TASK_DESCRIPTION, BATCHINFO.V_BATCH_DESCRIPTION, BATCHINFO.V_INFO_DATE,
          BATCHINFO.V_BATCH_STATUS, BATCHINFO.V_BATCH_ID, BATCHINFO.V_GROUP_CODE,BATCHINFO.STARTDATE, BATCHINFO.ENDDATE, BATCHINFO.APP_BATCH_RUN_IDFROMAAI_WF_TASK_POLL_STATUS_HIST WFTASKHISTINNERJOIN (SELECT DISTINCT BR.V_BATCH_RUN_ID, BT.V_TASK_DESCRIPTION, V_BATCH_DESCRIPTION,
          V_INFO_DATE, V_BATCH_STATUS, V_BATCH_ID, V_GROUP_CODE, IM.STARTDATE, IM.ENDDATE,
            REGEXP_REPLACE(BT.V_BATCH_RUN_ID,'',''|| BT.V_TASK_ID || '_',1,1) APP_BATCH_RUN_ID FROMBATCH_RUN BR, BATCH_TASK BT,(SELECT MSGLOG.V_BATCH_RUN_ID, MAX(MSGLOG.TIMESTAMP) ENDDATE,
          MIN(MSGLOG.TIMESTAMP) STARTDATEFROMICC_MESSAGELOG MSGLOG GROUP BY V_BATCH_RUN_ID ) IMWHEREBR.V_BATCH_RUN_ID = BT.V_BATCH_RUN_ID AND BR.V_BATCH_RUN_ID = IM.V_BATCH_RUN_ID)
        BATCHINFO ON (BATCHINFO.V_BATCH_ID = WFTASKHIST.V_TASK_REF_ID) WHERE
            WFTASKHIST.V_PROCESS_INSTANCE_ID= '1702359232479_01991722-baf2-4f08-ba61-88cd2d86e12a')DATAON(DATA.V_PROCESS_INSTANCE_ID = QUE.V_PROCESS_INSTANCE_ID AND
          DATA.V_TASK_DESCRIPTION = QUE.V_ACTIVITY_ID)WHEREQUE.V_PROCESS_INSTANCE_ID = '1702359232479_01991722-baf2-4f08-ba61-88cd2d86e12a' UNIONSELECTDISTINCT PROCESSDET.V_PROCESS_NAME PROCESSNAME, MAP.V_PROCESS_INSTANCE_ID
          PROCESSINSTANCEID,QUE.V_ACTIVITY_ID JOBID,
            NVL(ACTIVITYDET.V_ACTIVITY_NAME,ACTIVITYDET.V_ACTIVITY_DESC)JOBNAME, QUE.D_EXECUTION_START_TIMESTARTTIME, QUE.D_EXECUTION_END_TIME ENDTIME, NVL(QUE.V_EXECUTION_STATUS,QUE.V_STATUS)
          JOBSTATUS,''V_BATCH_RUN_ID, '' V_TASK_DESCRIPTION, '' V_BATCH_DESCRIPTION, '' V_INFO_DATE, ''
          V_BATCH_STATUS, ''V_BATCH_ID, '' V_GROUP_CODE, '' APP_BATCH_RUN_ID, MAP.V_OBJECT_ID, AWPE.V_INSTANCE_PROPERTY FROMAAI_WF_APP_INSTANCE_MAP MAPLEFTOUTER JOIN AAI_WF_APP_DEFINITION_MAP DEFN ON (DEFN.V_OBJECT_TYPE = MAP.V_OBJECT_TYPE)INNERJOIN AAI_WF_PROCESS_ENGINE_ACT_QUE QUE ON (QUE.V_PROCESS_INSTANCE_ID = MAP.V_PROCESS_INSTANCE_ID) INNERJOIN (SELECTPROCESSB.V_PROCESS_ID, PROCESSTL.V_PROCESS_NAME FROM AAI_WF_PROCESS_B PROCESSB, AAI_WF_PROCESS_TL PROCESSTL WHERE PROCESSB.V_PROCESS_ID =
        PROCESSTL.V_PROCESS_IDANDPROCESSTL.V_LOCALE_CODE = 'en_US')PROCESSDETON(PROCESSDET.V_PROCESS_ID = SUBSTR (QUE.V_PROCESS_INSTANCE_ID,0,INSTR (QUE.V_PROCESS_INSTANCE_ID,'_',-1) -1)
        ) INNERJOIN (SELECTACTB.V_PROCESS_ID, ACTTL.V_ACTIVITY_NAME, ACTB.V_ACTIVITY_ID, ACTTL.V_ACTIVITY_DESC,
        ACTTL.V_LOCALE_CODEFROMAAI_WF_ACTIVITY_B ACTB, AAI_WF_ACTIVITY_TL ACTTL WHERE
        ACTB.V_PROCESS_ID = ACTTL.V_PROCESS_IDANDACTB.V_ACTIVITY_ID = ACTTL.V_ACTIVITY_ID AND ACTB.V_EXECUTION_MODE != 'START'ANDACTTL.V_LOCALE_CODE = 'en_US'ANDACTB.V_PROCESS_ID = ACTTL.V_PROCESS_ID)ACTIVITYDETON(ACTIVITYDET.V_PROCESS_ID = SUBSTR (QUE.V_PROCESS_INSTANCE_ID,0,INSTR (QUE.V_PROCESS_INSTANCE_ID,'_',-1) -1) AND
        ACTIVITYDET.V_ACTIVITY_ID = QUE.V_ACTIVITY_ID ) INNERJOIN AAI_WF_PROCESS_EXECUTION AWPEON(AWPE.V_PROCESS_ID = SUBSTR (QUE.V_PROCESS_INSTANCE_ID,0,INSTR (QUE.V_PROCESS_INSTANCE_ID,'_',-1) -1) AND
        AWPE.V_PROCESS_ID = PROCESSDET.V_PROCESS_ID AND AWPE.V_PROCESS_INSTANCE_ID =
        '1702359232479_01991722-baf2-4f08-ba61-88cd2d86e12a' )WHEREMAP.V_PROCESS_INSTANCE_ID = '1702359232479_01991722-baf2-4f08-ba61-88cd2d86e12a'
    ORDERBY JOBID, JOBNAME, STARTTIME, ENDTIME;