Many a times we notice that thorugh our line is in Deferred Status, but it still not processed by the Workflow background process (WBP), on the other had if we execute RETRY activity, it progress further from Deferred status.
In case cases there are fare chances that issue is with the Deferred Workflow Queue.Below are the steps about how to identifies if the issue is with Deferred Queue or the workflow.
As usual my examples are based on Oracle Order Management.
NOTE - When ever workflow background process Pick the line for process, irrespective of the final outcome of the processing, begin_date in the wf_item_activity_statuses will changes. So one quick check is, after the workflow background process is completed , check the begin_date of the activity that was in deferred status before the WBP and remain same status after the WBP, if the begin_date changed that means workflow background process do picked the line for processing , but it failed and stay in same status for some other valid reason.But if begin_date is same as old , that for sure means this line is not processed by workflow background process.
Step #1 - run the below Query.parameter for the Query is Order Line ID. Run it for the line where Workflow is in Deferred status, but still not Processed by the workflow Background Process.
Select w.user_data.itemtype "Item Type", w.user_data.itemkey "Item Key",
decode(w.state, 0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained/Processed',
3, '3 = Exception',
w.priority, w.ENQ_TIME, w.DEQ_TIME, w.msgid
from wf_deferred_table_m w
and w.user_data.itemkey = '&Enter_line_id'
Meaning of various Statuses -
Ready = Activity is ready to be processed
Delayed = Activity will be processed later
Retained = Activity was already processed
Exception = Activity had an error
Note - How to run the Workflow Background Engine per developments recommendation?
Oracle Development has provided recommendations about how to run this program in order to avoid performance with it. The recommendation is to run three separate background engines at different intervals:
a. Run a background engine to handle only deferred activities every 5 to 60 minutes.
b. Run a background engine to handle only timed out activities every 1 to 24 hours as needed.
c. Run a background engine to handle only stuck processes once a week to once a month, when the load on the system is low.
If the status of the line from the Step# 1 is READY in WF_DEFERRED_TABLE_M queue and still it is no processed by Workflow Background engine, then for sure issue is with the Deferred Queue ( by saying this I mean your lines that are stuck and not processed by WBP are not properly InQueue to Deferred Queue).Please refere Note.1176723.1 for step by step instructions to recreate the WF_DEFERRED_TABLE_M queue