Project and Portfolio Management Practitioners Forum

Database Location for Approver of Current Workflow step

Super Contributor.

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?



Derek Giedd
Honored Contributor.

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,  
         WHEN wss.user_name IS NOT NULL THEN 
         WHEN wss.security_group_name IS NOT NULL THEN
         WHEN wss.token_type_code in ('STANDARD', 'USER_DEFINED') AND rst.security_type_code like 'USER%' THEN
         WHEN wss.token_type_code in ('STANDARD', 'USER_DEFINED') AND rst.security_type_code like 'SECURITY%' THEN
       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)