Project and Portfolio Management Practitioners Forum
cancel

How to find the steps a request has transitioned in the database

Highlighted
b460
Super Contributor.

How to find the steps a request has transitioned in the database

Hi,

I was wondering if anyone could let me know the database tables which stores the data of all the steps a request has transistioned through a workflow.

Thanks.

8 REPLIES
b460
Super Contributor.

Re: How to find the steps a request has transitioned in the database

What im trying to do is to create an SQL query to determine all the steps a request has traversed in a workflow.
Sascha Mohr
Acclaimed Contributor.

Re: How to find the steps a request has transitioned in the database

What you are looking for is in the table "KWFL_STEP_TRANSACTIONS".
b460
Super Contributor.

Re: How to find the steps a request has transitioned in the database

Do you know how this table is linked to kcrt_requests?
b460
Super Contributor.

Re: How to find the steps a request has transitioned in the database

What I require is a query that will return:

The request id, request name, and the name of all the steps that the request has currently transitioned through.

Thanks.
b460
Super Contributor.

Re: How to find the steps a request has transitioned in the database

I have managed to write this script to list all of the traversed steps of a request:

select
rq.request_id, rq.description, rt.request_type_name, ws.step_name, st.status

from
kcrt_requests rq,
kcrt_request_types rt,
kwfl_workflows wf,
kwfl_workflow_steps ws,
kwfl_step_transactions st
where
rq.request_type_id = rt.request_type_id
and rq.workflow_id = wf.workflow_id
and wf.workflow_id = ws.workflow_id
and ws.workflow_step_id = st.workflow_step_id
and rq.request_id = st.instance_source_set_id
and rq.request_id = '30199'
order by
st.creation_date

However it does not include the steps within sub-workflows that are in the workflow. Does anyone know how I would be able to include the subworkflows?
Sascha Mohr
Acclaimed Contributor.

Re: How to find the steps a request has transitioned in the database

You need to filter for TOP_INSTANCE_
SOURCE_ID of KWFL_STEP_TRANSACTIONS; this is the id of the primary wokflow also in steps that belong to a subworkflow.
b460
Super Contributor.

Re: How to find the steps a request has transitioned in the database

Hi Sascha,

Could you please assist in how I can apply TOP_INSTANCE_SOURCE_ID to my query.

I still cant get it to retrieve the sub-workflow steps as well.

Thanks.
b460
Super Contributor.

Re: How to find the steps a request has transitioned in the database

I managed to get it. The script I used was:

select
rq.request_id, rq.description, rt.request_type_name, ws.step_name, st.status
from
kcrt_requests rq,
kcrt_request_types rt,
kwfl_workflows wf,
kwfl_workflow_steps ws,
kwfl_step_transactions st
where
rq.request_type_id = rt.request_type_id
and rq.request_id = st.top_instance_source_id
and st.workflow_id = wf.workflow_id
and wf.workflow_id = ws.workflow_id
and ws.workflow_step_id = st.workflow_step_id

Thanks