How to identify the Activity ID for a Oracle Workflow Activity

1.   Introduction

Sometimes, we have to run the function attached to a workflow activity to see how it had worked for a particular run of a workflow. The standard definition of workflow functions has four input parameters viz. Item Type, Item Key, Function Mode and Activity ID. The Item Type and Item Key can easily be found on the workflow status monitor page. The Function Mode is normally RUN. Getting the value for Activity ID can be tricky. This is how the activity ID can be found in two different scenarios.

2.   Scenario – The workflow activity has encountered error.

          In this case you can get the activity by going to the Workflow Status Monitor -> Activity           History Table -> Click on the Error link on Status column. The error details page will give             you the Activity ID as shown below:

3.   Scenario – The workflow activity has completed successfully.

In this case the Activity ID is not available anywhere on the workflow status monitor page. So in this case the following query should be ran to get the Activity ID.

This query takes the ‘Activity Internal Name’,’ Item Type’ and ‘Item Key’ as input. The Activity ID is the INSTANCE_ID as available in the table WF_PROCESS_ACTIVITIES.

        SELECT WI.ITEM_TYPE

              ,WI.ITEM_KEY

              ,WI.BEGIN_DATE

              ,WPA.INSTANCE_ID ACTIVITY_ID

              ,WPA.ACTIVITY_NAME ACTIVITY_NAME

              ,WPA.PROCESS_NAME

          FROM APPS.WF_ITEMS WI

              ,APPS.WF_ITEM_ACTIVITY_STATUSES WIAS

              ,APPS.WF_PROCESS_ACTIVITIES WPA

         WHERE WI.ITEM_TYPE = WIAS.ITEM_TYPE

           AND WIAS.ITEM_TYPE = WPA.PROCESS_ITEM_TYPE

           AND WI.ITEM_KEY = WIAS.ITEM_KEY

           AND WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID

           AND WPA.ACTIVITY_NAME = UPPER('&Activity_Name')

           AND WI.ITEM_TYPE = UPPER('&Workflow_Item_Type')

           AND WIAS.ITEM_KEY = UPPER('&Workflow_Item_Key')

This query may return multiple rows in case the activity is used multiple times within a process. In such a case, you should can identify the Activity ID based on the number of times the activity has occurred within the process. 

(Hint: The Activity ID is a numeric value which keeps on increasing in value)