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?
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.
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
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)