dqMan Templates

Hello readers,

Here are some dqMan templates I would like to share with you.
They helped me a lot over the past few years.

Running Workflows
select p.object_name as process_name, wf.supervisor_name, wi.r_performer_name, a.object_name as activity_name, wi.r_creation_date as wi_creation_date, wi.r_runtime_state as wi_state, wf.r_start_date as wf_start_date, wf.r_runtime_state as wf_state, wi.r_exec_os_error as error, wi.r_act_seqno, 'after this' as r_object_id, a.r_object_id as activity_id, wi.r_object_id as workitem_id, wf.r_object_id as workflow_id, p.r_object_id as process_id
from dmi_workitem wi, dm_workflow wf, dm_process(all) p, dm_activity a
where wf.process_id = p.r_object_id
and wi.r_workflow_id = wf.r_object_id
and a.r_object_id = wi.r_act_def_id
and p.object_name = '{Process Name}'
and a.object_name like '{Activity Name}%'
and wf.supervisor_name like '{Supervisor Name}%'
and wi.r_performer_name like '{Performer Name}%'
and wi.r_runtime_state {Runtime State}
order by wf.r_object_id desc, wi.r_act_seqno desc

{Options
  Process Name.Label = "Process Name"
  Process Name.Mandatory = "yes"
  Process Name.ValueList = "select object_name from dm_process order by object_name"
  Process Name.ValueListType = "DQL query"
  Runtime State.Label = "Runtime State"
  Runtime State.Mandatory = "yes"
  Runtime State.ValueList = "= 0","= 1","= 2","= 3","= 4","= 5","<> 2","> 2"
  Runtime State.ValueListType = "Fixed list"
}

Running Workflow Activities
select w.r_object_id as workflow_id, wi.r_object_id as workitem_id, wi.r_performer_name, p.object_name, a.object_name as activity_name, wi.r_runtime_state, r_exec_os_error as os_error
from dm_process(all) p, dm_workflow w, dmi_workitem wi, dm_activity a
where w.r_object_id = '{Workflow Id}'
and w.r_object_id = wi.r_workflow_id
and wi.r_act_def_id = a.r_object_id
and p.r_object_id = w.process_id
order by wi.r_object_id desc;

Object in Workflow?
select distinct p.r_workflow_id, pc.object_name, p.r_package_name, p.r_package_type, p.r_component_id
from dmi_package p, dm_process(all) pc, dm_workflow w
where w.r_object_id = p.r_workflow_id and w.process_id = pc.r_object_id and any p.r_component_id = '{Object Id}'
enable (row_based);

Workflow Packages
select distinct r_package_type, r_component_id
from dmi_package
where r_workflow_id = '{WorkflowId}'
order by r_package_type
enable(ROW_BASED);

Fetch Workflow SDT
select *
from {std_name}_sp
where workflow_id = '{workflow_id}'

{Options
  std_name.Label = "STD Name"
  std_name.Mandatory = "yes"
  std_name.ValueList = "select name from dm_type where super_name = 'dmc_wfsd_element';"
  std_name.ValueListType = "DQL query"
  workflow_id.Label = "Workflow ID"
  workflow_id.Mandatory = "yes"
}

My Inbox Tasks
SELECT qi.r_object_id, p.object_name as process_name, qi.task_name, qi.task_state, qi.name as performer, qi.date_sent, wi.r_object_id as workitem_id, wf.r_object_id as workflow_id, p.r_object_id as process_id
FROM dmi_queue_item qi, dm_workflow wf, dm_process(all) p, dmi_workitem wi
WHERE qi.router_id = wf.r_object_id
and wf.process_id = p.r_object_id
and qi.item_id = wi.r_object_id
and qi.delete_flag = false
and qi.name = USER
ORDER BY qi.date_sent DESC

User Group Memberships
select distinct r_object_id, group_name, group_display_name from dm_group where group_class = 'group' and ANY i_all_users_names = '{User Name}' order by group_display_name;

{Options
  User Name.Mandatory = "yes"
  User Name.ValueList = "select user_name from dm_user;"
  User Name.ValueListType = "DQL query"
  User Name.Label = "User Name"
}

Kind regards, Marcus