Fixing approval history updation error using retry errored workflow items

Requisitions approval history was not updated properly after concerned person has approved requisitions. If you go and check action history the action history was missing latest approval. When investigated found out that workflow finished with an error in “ UPDATE_ACTION_HISTORY_APPROVE ”

To find out the errored items in a workflow follow the steps below

Log on to Oracle Application Manager
select Workflow Manager from navigate to list box(situated top right corner) and Click go
go work items under workflow metrics and click on show button to see the graph
click on error block on the graph
this will take to you to a a page with different work item types
click the relevant work items
this will take you to a page with different work item activity stage
click the relevant work activity stage
this will take to a new page with all the activities
select the activity you are concerned with
click launch workflow monitor this will open a new page with
check the item status it will be showing (“Error”)
Retrying errored work items
Here you have three buttons abort,retry,launch workflow monitor
Clikc on retry to retry the item again
this will ask for confirmation once confimed this will be run again
if it run successfully the status of that item should be changed to active

Retrying errored work items in the backend

this select satatment will give you all the errored items, the query is a modified version of query from oracle provided script wfretry.sql

SELECT
ias.item_type
,IAS.item_key
,pa.instance_label
,pa.process_name activity
,ias.activity_result_code
,COUNT(*)
FROM
wf_item_activity_statuses ias
,wf_process_activities pa
WHERE 1=1
AND ias.item_type LIKE :item_type
AND ias.process_activity = pa.instance_id
AND ias.activity_status = 'ERROR'
GROUP BY ias.item_type, IAS.item_key, pa.instance_label, pa.process_name, ias.activity_result_code
ORDER BY pa.instance_label;

Run this procedure to execute the 'retry' action

Wf_Engine.HandleError(itemtype ,itemkey',activity,command ',result );
note :when instance lable was given procedure was giving an error so we changed this to process_name

ex:
BEGIN
Wf_Engine.HandleError(itemtype => 'REQAPPRV',itemkey => '8784-32803',activity => 'RESPONSE_APPROVE',command => 'RETRY',result => NULL );
COMMIT;
END;

Table related to workflow
wf_item_activity_statuses  - work flow items with different action and status
wf_items                             - work flow items