Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Need Query to get transaction details

Highlighted
RRK_19
Regular Collector

Need Query to get transaction details

Hi,

 

I am in need of a query to get the transaction details for a particular request.

 

I formed a query as below ,
SELECT stp.STEP_NAME, tr.visible_result_value
    FROM KWFL_WORKFLOW_INSTANCES ins,
      KWFL_WORKFLOWS w,
      KWFL_STEP_TRANSACTIONS tr,
      kwfl_workflow_steps stp
    WHERE ins.instance_source_id      =<request_id>
    AND ins.instance_source_type_code = 'IR'
    AND w.workflow_name               = <workflow name>
    AND w.workflow_id                 = ins.workflow_id
    AND tr.WORKFLOW_INSTANCE_ID       = ins.workflow_instance_id
    AND tr.workflow_step_id           = stp.workflow_step_id
    AND tr.STATUS                     = 'COMPLETE';

 

I am getting the results but i want exactly the way or order in which its displayed in "Transaction Details" link availbale at the bottom of each request.

 

Thanks,

Roopesh

2 REPLIES
Jason Nichols K
Honored Contributor

Re: Need Query to get transaction details

Try adding

ORDER BY stp.SORT_ORDER
JHarris941
Regular Collector

Re: Need Query to get transaction details

Hello,

 

This is mostly identical to the tranactions hyperlink. I did not include notes and errors

 

SELECT
to_char(tr.creation_date, 'Month DD, YYYY HH12:MI:SS AM') "Date",
ku.username,
stp.sort_order Step,
stp.STEP_NAME "Workflow Step Name",
tr.visible_result_value Results
FROM

KWFL_WORKFLOWS w,
KWFL_STEP_TRANSACTIONS tr,
kwfl_workflow_steps stp,
knta_users ku
WHERE 1=1
AND ku.user_id = tr.created_by
AND w.workflow_id = stp.workflow_id
--AND w.workflow_name = <workflow name>
AND tr.workflow_step_id = stp.workflow_step_id
AND stp.product_scope_code = 'RI' --returns only request steps
AND stp.step_type_code not in ('EXECUTION','WORKFLOW','CONDITION') --removes execution steps
AND tr.STATUS = 'COMPLETE';
ORDER BY 1 --tranactions are ordered by date approved
--if you need requests add this join AND req.request_id = st.top_instance_source_id as well as the KCRT_REQUESTS table

 

 

hope this helps.

 

Jajcen

//Add this to "OnDomLoad" event