Wednesday, March 12, 2014

Useful Documentum Queries

---getting the work flow using workflow name
select * from dm_workflow where object_name like '3333%';
--Getting the workflow using id
select * from dm_workflow where r_object_id='abc'

--getting user name in documentum using network ID in small
select user_name from dm_user where LOWER(user_login_name)='abc'
----Getiing inbox

EXECUTE get_inbox WITH name='abc'
EXECUTE get_inbox WITH category=1,name='Weekse Alexander E',order_by=date_sent desc

--Get the completed work flow information
select * from dmc_completed_workflow where workflow_id='4d00eb438002ba00';
---Get all the completed work items
select * from dmc_completed_workitem where workflow_id='4d00eb438002ba00';

---Get User Inbox
SELECT router_id,item_id,read_flag,stamp,task_name,sent_by, date_sent,task_state,due_date,name  FROM dm_queue WHERE name = abc' AND task_name !='event' order by date_sent DESC;
--Get user status (if 1 it means locked)
select user_name,user_state from dm_user where user_os_name in ('a','b','c')


 Check the Documentum groups for a user -
select i_all_users_names, group_name from dm_group where any i_all_users_names in (select user_name from dm_user where upper(user_os_name) = upper('<<network id>>'))
 Is a user disabled - 
select * from ep_user where upper(user_os_name)=upper('<<network id>>') and user_state=1
 Check all users in a group -
select i_all_users_names, group_name from dm_group where group_name =’<<Documentum group>>’
 The below query will check the status of user on Documentum 0 = active otherwise inactive
select user_os_name,user_state from ep_user where user_os_name in ('a','b','c')
---Query to check users in a group
select i_all_users_names from dm_group where group_name like '%a%'
---checking if a user is in a documentum group
select i_all_users_names from dm_group where group_name like '%b%' and any "i_all_users_names" = 'a'

---Get the list of performer names of completed workitems for a live workflow
select r_performer_name from dmi_workitem where r_workflow_id ='4d00ea968006f2d6' and r_runtime_state in (2) ;

---Get the performer name of current workitem for a live workflow
select r_performer_name from dmi_workitem where r_workflow_id ='4d00ea968006f2d6' and r_runtime_state in (0) ;


--Get work item performer names and action names for a given workflow
---the first two columns will have comma separated values with same order
select all r_performers,r_perf_act_name,r_object_id from dm_workflow where r_object_id in ('Workflowid1','Workflowid2');
---Get the current workitem for a live workflow (router_id = workflow id , item_id  = workitem id )

Select item_id as workitem_id from dmi_queue_item a where a.router_id = 'workflow_id' and delete_flag = 0;