Project and Portfolio Management Practitioners Forum
cancel

Transaction details time difference

Highlighted
PPMRam
Super Contributor.

Transaction details time difference

Hello All,

 

 

Am looking for a query to calculate the time taken at each approvals for particular request from transaction details. Purpose is to know at which step much time taking to process the request.

 

I have wrote query but the request contains multiple transactions to and fro which is not giving correct result of time difference between each action taken on the request.

 

Thanks in advance.....

 

 

1 REPLY
AlfredoMonasi
Honored Contributor.

Re: Transaction details time difference

Hi PPMRam

 

I used the query below to obtain the creation and the last update of a step in my workflow. Please notice that I used a view called "ALCP_NUEVOS_PRODUCTOS_V" replace it with your KCR_REQUESTS table and conditions.

 

This query only get a specific step when entering and exiting, you will need to repeat this for each decision you need to get the date difference, also notice if the Step is a subworflow you will get the date when entering and exiting to the SWF (no the steps behind it). Hope it helps.

 

SELECT NP.REQUEST_ID,WS.STEP_NAME,WST.CREATION_DATE,WST.LAST_UPDATE_DATE
FROM ALCP_NUEVOS_PRODUCTOS_V NP,KWFL_WORKFLOW_INSTANCES WI,KWFL_WORKFLOWS W,KWFL_WORKFLOW_STEPS WS,KWFL_WORKFLOW_INSTANCE_STEPS WIS,KWFL_STEP_TRANSACTIONS WST
WHERE 1=1
AND NP.REQUEST_ID=WI.TOP_INSTANCE_SOURCE_SET_ID
AND WI.WORKFLOW_ID=W.WORKFLOW_ID
AND W.WORKFLOW_ID=WS.WORKFLOW_ID
AND WS.WORKFLOW_STEP_ID=WIS.WORKFLOW_STEP_ID
AND WI.WORKFLOW_INSTANCE_ID=WIS.WORKFLOW_INSTANCE_ID
AND WIS.WORKFLOW_INSTANCE_STEP_ID=WST.WORKFLOW_INSTANCE_STEP_ID
AND WS.STEP_NAME='Ideación'
ORDER BY NP.REQUEST_ID