Project and Portfolio Management Practitioners Forum
cancel

where is the workflow date of last workflow step saved

SOLVED
Go to solution
Highlighted
Ramazan1
Super Contributor.

where is the workflow date of last workflow step saved

Hello,

 

I can find the whole workflow steps and the dates of each step of a project, but I cannot find the last Workflow step. where is it saved? I need this for a query, to show on which date a project is really finished.

 

Regards,

Ramazan

3 REPLIES
JHarris941
Super Contributor.

Re: where is the workflow date of last workflow step saved

Hello,

You will have to join the step transaction table to the request table. the transaction table will have the last update date for that step. filter on I believe its step_type_code = 'COMPLETE'
Mohit_Agrawal
Outstanding Contributor.

Re: where is the workflow date of last workflow step saved

Hi Ramzan,

 

First get the workflow id from kwfl_workflows. and then run the query to get the last step added to workflow. It might work..

 

 SELECT step_name
  FROM kwfl_workflow_steps
 WHERE workflow_id = 
   AND workflow_step_id = (SELECT MAX (workflow_step_id)
                             FROM kwfl_workflow_steps
                            WHERE workflow_id = );

 

Thanks!!

Mohit Agrawal

:: Click KUDOS if you like..

Sangliraja1
Trusted Contributor.
Solution

Re: where is the workflow date of last workflow step saved

Hi Ramazan,

 

if you are looking to find the date at which a request reached a step in a workflow, u can use the following code. But u have to pass some hard code values namely Workflow Name, Step Name for which u need the transaction date. If you are going to use it in a portlet then the code can be tweaked a little to include your request type.

 

If the piece of code, the italic, bolded parameters aer the one you may have to replace

 

SELECT max(TRAN.LAST_UPDATE_DATE)
FROM kcrt_requests req,
kcrt_request_types rt,
KWFL_WORKFLOWS_NLS wf,
KWFL_WORKFLOW_STEPS wf_st,
kcrt_statuses ws,
kwfl_workflow_instances ins,
kwfl_step_transactions tran,
KWFL_WORKFLOW_INSTANCE_STEPS instep
WHERE rt.request_type_name LIKE <REQUEST TYPE NAME>

AND wf.workflow_name LIKE <Worfklow Name>

AND rt.request_type_id = req.request_type_id
AND wf_st.workflow_id = WF.WORKFLOW_ID
AND wf_st.step_type_code = 'APPROVAL'
AND INS.WORKFLOW_ID = WF.WORKFLOW_ID
and INS.TOP_INSTANCE_SOURCE_ID = REQ.REQUEST_ID
and INS.INSTANCE_SOURCE_TYPE_CODE like 'IR' --- for requests
and INSTEP.WORKFLOW_ID = WF.WORKFLOW_ID
and INSTEP.WORKFLOW_STEP_ID = WF_ST.WORKFLOW_STEP_ID
and INSTEP.WORKFLOW_INSTANCE_ID = INS.WORKFLOW_INSTANCE_ID
and WS.STATUS_ID(+) = WF_ST.PARENT_STATUS
AND TRAN.WORKFLOW_ID = WF.WORKFLOW_ID
AND TRAN.INSTANCE_SOURCE_ID = REQ.REQUEST_ID
and TRAN.WORKFLOW_INSTANCE_ID = INS.WORKFLOW_INSTANCE_ID
and TRAN.WORKFLOW_STEP_ID = WF_ST.WORKFLOW_STEP_ID
and TRAN.WORKFLOW_INSTANCE_STEP_ID = INSTEP.WORKFLOW_INSTANCE_STEP_ID
and TRAN.INSTANCE_SOURCE_TYPE_CODE = 'IR'
and WF_ST.STEP_NAME like <Step Name>

and REQ.REQUEST_ID = <REQUEST_ID>

 

Thanks

Sangiliraja.P