Workflow problems and resolutions

How to pause and resume a workflow?
A workflow is suspended and you would like to resume it.
The workflow was suspended because an activity failed.
Resolution 1
Please note that unfortunately, if you are not able to resume your workflows you will have to restart them.
Pausing and resuming work items
You can pause a dormant work item. Work items are dormant until they are acquired or a user delegates the work item. You cannot pause an acquired work item.
To pause a dormant work item, you must be the workflow supervisor or a user with Sysadmin or Superuser privileges. Use a Pause method to pause a work item.
Resuming a paused work item returns the work item to the dormant state. To resume a paused work item, you must be the workflow supervisor or a user with Sysadmin or Superuser privileges. Use a Resume method.
Syntax
To pause:
dmAPIExec("pause,c,workitem_id")
To resume halted workflow, halted activity, or paused work item:
dmAPIExec("resume,c,object_id")
workitem_id: specifies the work item that you want to pause.
object_id: identifies the paused workflow, activity, work item, or SysObject to resume. Use the object's object ID.
Restarting a halted ow or failed activity
Restarting a workflow removes all generated work items and packages and restarts the workflow from the beginning, with all activity instances set to dormant.
Restarting a failed activity sets the activity's state to Active.
To restart a halted workflow or failed activity, use a Restart method.
Syntax
dmAPIExec("restart,c,workflow_id")


Resolution 2
For a specific workflow, get the paused tasks by running the DQL :
select r.r_act_seqno, wf.r_object_id as workflow_id
from dmi_workitem r, dm_workflow wf, dm_activity act
where wf.r_object_id = r.r_workflow_id
and r.r_act_def_id = act.r_object_id
and r_runtime_state = 5 and wf.object_name=""

The above DQL gives u the activity sequence number and workflow id.

Then use IAPI to restart the paused task using :
restart,c,,sequence_no.


Please note: Attribute values for r_runtime_state of dmi_workitem between 4 & 6 occurs when a
workflow containing the work item is halted. So you need to restart the workflow, user restart API method to do this.

select r_object_id,r_runtime_state from dmi_workitem
r_runtime_state indicates the current state of
the workflow. Values are:
Contains the current state of the work item.
Valid value are:
0, meaning dormant 1, meaning acquired
2, meaning finished 3, meaning paused
4, meaning Dpaused (a work item in the dormant state is paused)
5, meaning Apaused (a work item in theacquired state is paused)
6, meaning Ppaused (a work item in the paused state is paused)
Values 4-6 occur when a workflow containing the work item is halted.


Is it possible to restart TERMINATED workflows?
A dm_workflow object is an instance of the dm_process Object
(The WF Template) and is used to represent an active WF. When the WF is
Terminated, these are automatically removed from the system. You can never
create, save them. These are handled primarily by the CS


I need DQL query to find all attachments names/ids from
all started workflows:

The following dql returns all the attachments
SELECT r_component_id, r_component_name
FROM dmi_wf_attachment
WHERE r_workflow_id IN(select r_object_id from dm_workflow)

The following dql returns all the packages
SELECT DISTINCT r_component_id, r_component_name
FROM dmi_package
WHERE r_workflow_id IN(select r_object_id from dm_workflow)


DQL to find no. of attachments + their names in all started workflows

the key is dmi_wf_attachment

Query to retrieve objects attached to workflows
select so.r_object_id,so.r_object_type,so.object_name,wf.object_name as wfname, wf.r_runtime_state
from dm_sysobject (ALL) so, dmi_wf_attachment wfa,dm_workflow wf
where wfa.r_component_id = so.r_object_id
and wfa.r_workflow_id = wf.r_object_id

Add any additional where clause as r_runtime_state = 1 as you please

Query to retrieve workflows with more than 1 attachment
select wf.r_object_id,wf.object_name,wf.r_runtime_state,count(wfa.r_component_id) as noofattachments
from dm_workflow wf,dmi_wf_attachment wfa
where wfa.r_workflow_id = wf.r_object_id
group by wf.r_object_id,wf.object_name,wf.r_runtime_state
having count(wfa.r_component_id) > 1

Again, add any additional where clause if required


SELECT distinct a.r_workflow_id, c.r_component_id as doc_object_id, c.r_component_name as document_name, b.object_name as activity_name
FROM dmi_workitem a, dm_activity b, dmi_package_r c, dmi_package_s d
WHERE d.r_workflow_id = a.r_workflow_id
AND c.r_object_id = d.r_object_id
AND a.r_act_def_id = b.r_object_id
ORDER BY a.r_workflow_id;
Output will be:-
r_workflow_id doc_object_id document_name activity_name