Stuck Workflow Woes

We’re occasionally contacted by OpenText customers because they find “stuck” workflow items in user queues. For example, in document signing workflows, documents may be fully signed but some of the individual work items are not completing. It can often be the case that work items have got “stuck” trying to execute an automated step, such as an Item Handler step or a PowerTools step. In these cases, the problematic workflow is returned to a user’s Inbox queue so that it can be fixed or manually processes.

Here’s a typical example. The Item Handler step is supposed to add the latest signed version to the original document. The Audit tab for the step shows many attempts, so many that upon reaching the maximum attempts, the work item was moved to the user’s queue. Now it is “stuck” there.

We created SQL to reset the “stuck” work items. In particular, this SQL finds work items that are in a User Queue that should instead be assigned to the Workflow Agent. The statements below, one for MSSQL and one for Oracle, will select rows that are supposed to be completed by the Workflow Agent, but ended up in a user’s Inbox queue.

First, run the correct select statement for your database type from below and verify that the returned data looks correct.

MSSQL

Select SubWorkTask_SubWorkID, SubWorkTask_TaskID, SubWorkTask_Title from WSubWorkTask where exists (
select 1 from
WWork,
WSubWork,
WSubWorkTask swt,
WMapTask
where
SubWork_WorkID=Work_WorkID and
swt.SubWorkTask_WorkID=Work_WorkID and
swt.SubWorkTask_SubWorkID=SubWork_SubWorkID and
Work_Status=2 and
SubWork_Status=1 and
swt.SubWorkTask_Status in ( 2, 5 ) and
MapTask_MapID=SubWork_MapID and
MapTask_TaskID=swt.SubWorkTask_TaskID and
(
( MapTask_Flags & 256 ) = 256 ) and
WSubWorkTask.SubWorkTask_SubWorkID = swt.SubWorkTask_SubWorkID and
WSubWorkTask.SubWorkTask_TaskID = swt.SubWorkTask_TaskID
)

Oracle

Select SubWorkTask_SubWorkID, SubWorkTask_TaskID, SubWorkTask_Title from WSubWorkTask where exists (
select 1 from
WWork,
WSubWork,
WSubWorkTask swt,
WMapTask
where
SubWork_WorkID=Work_WorkID and
swt.SubWorkTask_WorkID=Work_WorkID and
swt.SubWorkTask_SubWorkID=SubWork_SubWorkID and
Work_Status=2 and
SubWork_Status=1 and
swt.SubWorkTask_Status in ( 2, 5 ) and
MapTask_MapID=SubWork_MapID and
MapTask_TaskID=swt.SubWorkTask_TaskID and
(
BITAND( MapTask_Flags, 256 ) = 256 ) and
WSubWorkTask.SubWorkTask_SubWorkID = swt.SubWorkTask_SubWorkID and
WSubWorkTask.SubWorkTask_TaskID = swt.SubWorkTask_TaskID
)
If the data looks correct, then perform the following step to update the work items.

Second, replace the select statement,

Select SubWorkTask_SubWorkID, SubWorkTask_TaskID, SubWorkTask_Title from WSubWorkTask where exists (

with the update statement below:

update WSubWorkTask set SubWorkTask_Status = 200 where exists (
The workflows should now be moved back to the automated process engine and continue actioning where they left off.