Project and Portfolio Management Practitioners Forum
cancel

How to get Request Transaction Details from the Database

Highlighted
Monica Trevino
Frequent Contributor.

How to get Request Transaction Details from the Database

Hi All,

I'm trying to get the information that is shown in the link Transaction Details in a request but from the Data Base.
I need the Date when a request was set in a specific Status and when it was Closed or Canceled.
Any help is welcome.

Thanks in advance.
Monica.
3 REPLIES
Sangiliraja_1
Honored Contributor.

Re: How to get Request Transaction Details from the Database

Hi Monica,

You can use the following tables.

kwfl_workflows, kwfl_workflow_steps, kwfl_workflow_instances, kwfl_step_transactions, kwfl_step_transaction_history, KWFL_WORKFLOW_INSTANCE_STEPS

Thanks
Sangiliraja.P
Monica Trevino
Frequent Contributor.

Re: How to get Request Transaction Details from the Database

Thanks Sangiliraja!!!
Any help on how to link those tables to get exactly: Request on Specific Status, Date since the request is in that Status?
Sangiliraja_1
Honored Contributor.

Re: How to get Request Transaction Details from the Database

Hi Monica,

Following part of query can be useful for you.

FROM kwfl_workflows wfl,
kwfl_workflow_steps wfst,
kwfl_workflow_instances ins,
kwfl_step_transactions stptr,
kwfl_step_transaction_history his, knta_users_v v,
kcrt_req_header_Details rht, knta_users_v cv
WHERE wfl.workflow_name LIKE 'DPSG-WF-DMR Workflow'
AND wfst.workflow_id = wfl.workflow_id
AND wfst.step_type_code = 'APPROVAL'
AND ins.workflow_id = wfl.workflow_id
AND stptr.workflow_step_id = wfst.workflow_step_id
AND stptr.workflow_instance_id = ins.workflow_instance_id
AND stptr.instance_source_type_code = 'IR'
AND stptr.instance_source_id = ins.instance_source_id
AND his.step_transaction_id = stptr.step_transaction_id
AND his.status = 'ELIGIBLE'
AND v.user_id = stptr.LAST_UPDATED_BY
AND rht.REQUEST_ID = ins.instance_source_id
AND rht.BATCH_NUMBER = 1
AND cv.user_id = rht.CREATED_BY

It gives u steps that are active now and how many they are in the current status.

If you some more requirement, let me know and i can give much better query.

Thanks
Sangiliraja.P