Details of the active workitems for a workflow definition(Process) using DQL

Below is the query to get the details of the active workitems for a workflow definition(Process).
This query will give the below details

1. Document’s Object Id (document_object_id) – This document is attached to the workflow
2. Object Name of the Document
3. Activity Name (activity_name) – Activity Name of the current workitem
4. Workitem Id (workitem_id) – r_object_id of the currently active workitem
5. Workflow Id (workflow_id) – r_object_id of the currently active workflow
6. Queue name (a_wq_name) – Current queue name of the workitem
7. Performer Name (r_performer_name) – Will be the name of the Performer
8. Runtime State (r_runtime_state) – 0 for dormant, 1 for active, 2 for finished and greater than 2 for Paused state

1. DQL Query

SELECT 
		doc.r_object_id AS document_object_id,
		doc.object_name AS document_object_name,
		act.object_name AS activity_name,
		witem.r_object_id AS workitem_id,
		witem.r_workflow_id AS workflow_id,
		witem.a_wq_name,
		witem.r_performer_name,
		witem.r_runtime_state
	FROM
		dm_process process,
		dm_workflow wflow,
		dmi_workitem witem,
		dmi_package pck,
		dm_document doc,
		dm_activity act
	WHERE
		ANY pck.r_component_id = doc.r_object_id AND
		pck.r_workflow_id = witem.r_workflow_id AND
		wflow.r_object_id = witem.r_workflow_id AND
		act.r_object_id = witem.r_act_def_id AND
		witem.r_runtime_state < 2 AND
		wflow.process_id = process.r_object_id AND
		process.object_name = 'DCTMGuruWorkflow'

2. Relationship Diagram

Below diagram show the relationship between the objects