Mumbai, India
ecmnotes.com parag@ecmnotes.com

Workflows

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 = '<workflow ID>' and r_act_seqno = 0

Clean inbox for failured objects:

DELETE dmi_queue_item objects where event = 'event_type'

Leave a Reply