In this blog I will try to share my experience, which I believe will help others and shorten the learning curve.
The posts will generally talk about lessons from my professional work, however it can contain content from life outside work also.

Friday, December 20, 2013

How to identify the Activity ID for a Oracle Workflow Activity

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)