Workflow related queries

To know details of the tasks related with workflow
select * from dmi_queue_item where router_id='Workflow_ID'




List of component objects in user inbox
select r_object_id from dm_sysobject where r_object_id in (select r_component_id from dmi_package where r_workflow_id in (select router_id from dmi_queue_item where name='’))
List of inbox items for a given task
select count(name), name FROM dmi_queue_item WHERE task_name = ‘ and delete_flag=0 AND (task_state = ‘dormant’ OR task_state = ‘acquired’ OR task_state = ‘paused’ OR task_state = ‘ready’) AND (router_id != ‘0000000000000000′ AND router_id IS NOT NULLSTRING AND NOT (a_operations = ‘queue’)) group by name order by name

List of WF’s in a user inbox
select * from dmi_queue_item WHERE name='’ AND delete_flag=0 AND (task_state = ‘dormant’ OR task_state = ‘acquired’ OR task_state = ‘paused’ OR task_state = ‘ready’) AND (router_id != ‘0000000000000000′ AND router_id IS NOT NULLSTRING AND NOT (a_operations = ‘queue’)) UNION ALL SELECT * FROM dmi_queue_item WHERE name IN (SELECT ALL group_name FROM dm_group WHERE ANY i_all_users_names = ‘’) AND delete_flag=0 AND (task_state = ‘dormant’ OR task_state = ‘acquired’ OR task_state = ‘paused’ OR task_state = ‘ready’) AND (router_id != ‘0000000000000000′ AND router_id IS NOT NULLSTRING AND NOT (a_operations = ‘queue’))

*************************************************************
DQL to uninstall workflows
Retrieve the workflow that needs to be uninstalled

SELECT r_object_id, object_name, r_definition_state FROM dm_process WHERE object_name = ‘‘

Change the definition of the workflow to DRAFT (0 = Draft)
UPDATE dm_process OBJECT SET r_definition_state = 0 WHERE r_object_id = ‘‘
Change the definition of the activities corresponding the above
workflow to DRAFT (0 = Draft)
UPDATE dm_activity OBJECT SET r_definition_state = 0 WHERE r_object_id IN (SELECT r_object_id FROM dm_activity WHERE r_object_id IN (SELECT r_act_def_id FROM dm_process WHERE r_object_id = ‘‘))

Verify that all activities are in the DRAFT state
SELECT object_name, r_definition_state FROM dm_activity WHERE r_object_id IN (SELECT r_act_def_id FROM dm_process WHERE r_object_id = ‘‘)

*****************************************************
How do I get the complete comments of a workflow?
Note: In a workflow, a performer can enter comments
while he/she is performing the task. And those comments
are carried to the next performer in this workflow.
When using WebPublisher workflow report to review
the workflow instances, and in workflow history, you
are only able to get the truncated comments back from
WP's interface. Especially for those already aborted
workflow instances, there is no way that you can get
the complete comments back from WP's GUI. In some
cases, those comments are very important to our
customers and they need to find a way to get those
comments back.

Here are the steps to get those comments back:

1) Identify the workflow from the dm_workflow table,
get the r_object_id of the workflow:
select r_object_id, object_name from dm_workflow
where object_name = 'your work flow name'

2) Identify the notes that are carried by this workflow:
select r_note_id from dmi_package where r_workflow_id
= ' the object id of the workflow'

3) Get the content id of each of those note ids returned:
select r_object_id from dmr_content where any
parent_id = 'the note id'

4) Go to DA, Administration->Job Management->Administration,
use the "GET_PATH" method to find out the path of the files
which stores the comments.

Following the above steps, you will be able to find all
the comments carried by a specific workflow instance.
One other way to get this information is to enable the
session DMCL trace and look into the log file.

****************************************************
Query to find active workflows, supervisors of the workflows

select r_object_id, object_name, title, owner_name,
r_object_type, r_creation_date, r_modify_date,
a_content_type from dm_document where r_object_id
in(select r_component_id from dmi_package where
r_workflow_id in (select r_object_id from dm_workflow
where r_runtime_state = 1)) order by
r_creation_date desc

**************************************************
DQL to list worrkflow information

select task_name, task_state, actual_start_date,dequeued_date
from dmi_queue_item where router_id = ‘workflowId’
****************************************************
To know the workflow and their respective workflows

select *,r_act_name FROM "dm_workflow"
******************************************************
This will be used to clear inbox

delete dmi_queue_item objects where delete_flag=0
******************************************************