Platform:	Oracle	App:	Oracle Applications	Tool:	Oracle Workflow
DB Ver:	9.2	App Ver:	11.5.8	Tool Ver:	2.6.2
Revision Date:	15-Sep-2006	Keywords:	HOWTO, EXPEDITE, MASS,GLOBAL,RETRY,SKIP,WORKFLOW,ERROR
Goal
To expedite errored workflows en masse
Facts
Solution
Sometimes a situation can occur when there are many in-process workflows that hit an error. This could occur, for example, if a database package has become INVALID. The workflow background engine may run, attempt to process the workflows, hit the invalid package and throw an error putting the workflow in to an errored state. If this happens, what you would normally do is fix the problem, recompile the package and have the system administrator expedite the workflow (using 'RETRY'). This would retry the failed process activity, which you would then expect to complete successfully. To do this from the SQL*Plus command line, you could use the following supplied SQL script: wfretry.sql

However, this only does one flow instance at a time. So here is a couple of scripts to retry every failed workflow:
First, use this SQL script to see the current errors:

-- -----------------------------------------
-- Find all errored workflows in the system
-- by item type
-- -----------------------------------------
SELECT
ias.item_type
,pa.instance_label
,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
,pa.instance_label
,ias.activity_result_code
/

Then, run the following script to RETRY these workflows. You can use '%' for each value to retry all errors. REMEMBER to COMMIT this after you have finished if you want to keep the results.

-- -----------------------------------------
-- RETRY all errored workflows in the system
-- by item type, item key and/or instance
-- label
-- REMEMBER TO COMMIT
-- -----------------------------------------
DECLARE

CURSOR c_retry IS

SELECT
ias.item_type
,ias.item_key
,pa.instance_label
,ias.activity_result_code Result
FROM
wf_item_activity_statuses ias
,wf_process_activities pa
WHERE 1=1
AND ias.item_type LIKE '&item_type'
AND ias.item_key LIKE '&item_key'
AND pa.instance_label LIKE '&instance_label'
AND ias.process_activity = pa.instance_id
AND ias.activity_status = 'ERROR'
;

BEGIN

DBMS_OUTPUT.PUT_LINE('Workflow Global retry..... ');
FOR r_retry IN c_retry LOOP

DBMS_OUTPUT.PUT_LINE('Retrying workflow item_type='

||r_retry.item_type
||', item_key='||r_retry.item_key
||', label='||r_retry.instance_label
);

Wf_Engine.HandleError(itemtype => r_retry.item_type

,itemkey => r_retry.item_key
,activity => r_retry.instance_label
,command => 'RETRY'
,result => NULL
);

END LOOP;

END;