Workflow Step Start and Finish Dates
Is anyone aware of (or has had the need to create) a PPM portlet or report that would / could provide workflow step actual start and actual finish dates for a particular project(s)?
What I am really struggling with is determing which KWFL tables would be used to provide this level of detail (workstep start and finish) for a particular project/projects.
Here is the query to identify the active workflow step with the date; you need to modify it based on your requirements.
SELECT kr.request_id, kwis.last_update_date, kws.step_name FROM kwfl_workflow_instances kwi, kwfl_workflow_instance_steps kwis, kwfl_workflow_steps_nls kws, kcrt_requests kr WHERE kwi.top_instance_source_type_code = 'IR' AND kr.workflow_id = kws.workflow_id AND kr.request_type_id = (SELECT request_type_id FROM kcrt_request_types WHERE request_type_name = 'Request Type Name') AND kr.status_code IN ('NEW', 'IN_PROGRESS') AND kwi.top_instance_source_set_id = kr.request_id AND kwi.top_instance_source_id = kr.request_id AND kwi.workflow_instance_id = kwis.workflow_instance_id AND kwi.current_flag = 'Y' AND kwis.active_flag = 'Y' AND kwis.workflow_step_id = kws.workflow_step_id -- AND kws.step_type_code NOT IN ('WORKFLOW', 'CONDITION')
-- Remember to give Kudos to answers! (click the KUDOS star)
Thanks for the script which is getting me closer to what I need. My problem now is that the step this script shows as the active step is actually a workflow source type which links to a sub-workflow, which is where the actual "active" step is. How would I be able to see the active step in the associated sub-workflow? Thanks.