Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Database Location for Approver of Current Workflow step

Highlighted
skbd
Regular Collector

Database Location for Approver of Current Workflow step

I searched these forums, but could not find this so I apologize if I am repeating any previous postings.

I have a request from a PPM user to create a report where they can enter just a CR Type and it will provide a listing of all open CRs of that type sorted by the individual(s)/Group(s) who have the approval authority for that step.

I have run into a snag early in that I cannot seem to locate table/column for where this information is kept.

I thought I had it on the KWFL_WORKFLOW_STEPS_NLS with the columns ASSIGNED_TO_USER_TOKEN and ASSIGNED_TO_GROUP_TOKEN, but those fields are null for all of my currently open requests...

Anyone out there done anything similar to this who could point me in the right direction?

Thanks!

Steve

1 REPLY
Derek Giedd
Frequent Visitor

Re: Database Location for Approver of Current Workflow step

This is not stored in a table where you can easily look it up. You have to build the info. Here is some code that should point you in the correct direction. You will need to replace <workflow name> and <request id> with your values. There are probably ways to clean this up a little. This will return the eligible step name and the security groups/users that have approval authority.  The fields you were looking at are used by the Parent Assigned to fields, if you use a token.

SELECT ws.step_name,  
       CASE 
         WHEN wss.user_name IS NOT NULL THEN 
           wss.user_name
         WHEN wss.security_group_name IS NOT NULL THEN
           wss.security_group_name
         WHEN wss.token_type_code in ('STANDARD', 'USER_DEFINED') AND rst.security_type_code like 'USER%' THEN
           rst.username
         WHEN wss.token_type_code in ('STANDARD', 'USER_DEFINED') AND rst.security_type_code like 'SECURITY%' THEN
           rst.security_group_name
       END approval_authority
  FROM kwfl_workflows wf, kwfl_workflow_steps ws, kwfl_workflow_instances wi, kwfl_workflow_instance_steps wis,
       kwfl_step_transactions st, kwfl_workflow_step_security_v wss, kwfl_resolved_sec_tokens_v rst
 WHERE wf.workflow_name = '<workflow name>'
   AND ws.workflow_id = wf.workflow_id
   AND wi.top_instance_source_id = <request id>
   AND wi.workflow_id = wf.workflow_id
   AND wi.status = 'OPEN'
   AND wis.workflow_instance_id = wi.workflow_instance_id
   AND wis.workflow_step_id = ws.workflow_step_id
   AND wis.active_flag = 'Y'
   AND st.workflow_instance_step_id = wis.workflow_instance_step_id
   AND st.status = 'ELIGIBLE'
   AND wss.workflow_step_id = ws.workflow_step_id
   AND RST.Resolved_Sec_Token_ID = WSS.Workflow_Step_Security_ID;
-- Remember to give Kudos to answers! (click the KUDOS star)
//Add this to "OnDomLoad" event