List all workflows supervised by the current user:
SELECT process_id, object_name FROM dm_workflow WHERE supervisor_name = USER
List all workflows supervised by any users within a specific list of users:
SELECT r_object_id, supervisor_name FROM dm_workflow WHERE supervisor_name IN ('', '', '')
Return all tasks that were started a month or more late:
SELECT task_number, supervisor_name FROM dm_tasks_queued WHERE DATEDIFF(month,plan_start_date,actual_start_date) >= 1
Return all tasks that were started more than one week ago:
SELECT task_number, r_task_user FROM dm_tasks_queued WHERE DATEDIFF(week,actual_start_date,DATE(TODAY)) >= 1
Return all tasks that started more than a week ago but are not yet finished:
SELECT task_number, supervisor_name FROM dm_tasks_queued WHERE DATEADD(week, 1 ,actual_start_date) < DATE (TODAY) and actual_completion_date IS NULLDATE and NOT(actual_start_date IS NULLDATE)
Include method in workflow manager:
UPDATE dm_method objects set a_special_app='Workflow' where object_name='method_name'
Retrieve the state of the documents in workflow:
SELECT a.item_id,a.task_name,a.date_sent,a.task_state,c.object_name,b.r_performer_name from dmi_queue_item a,dmi_workitem b,dmi_package d,dm_sysobject c where ANY d.r_component_id = c.r_object_id and b.r_workflow_id=d.r_workflow_id and b.r_workflow_id = a.router_id and a.task_state='dormant' or a.task_state='acquired' and a.task_name='draft'
Retrieve the list of tasks aquired before n no of days:
SELECT r_object_id, name, date_sent, item_name from dmi_queue_item where item_name='dormant' and DATEDIFF(day,"date_sent",date(today))>n
Retreive the list of sysobjects which are not the part of the workflow package:
SELECT r_object_id, object_name from dm_sysobject where r_object_id not in (select r_component_id from dmi_package)
Retreive the list of objects packaged to a specific workflow:
SELECT r_object_id, r_component_id, r_package_name, r_package_type, i_package_oprtn, r_note_id from dmi_package where r_workflow_id = '' and r_act_seqno = 0
Clean inbox for failured objects:
DELETE dmi_queue_item objects where event = 'event_type'