The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Query to determine the workflow step of a change request

SOLVED
Go to solution
Highlighted
ansmith
Occasional Advisor

Query to determine the workflow step of a change request

I'm creating a validation that returns the workflow step of a request. The request number will be provided by a token.

Does anyone know how to accomplish this?
2 REPLIES
Surendra Poosar
Frequent Visitor
Solution

Re: Query to determine the workflow step of a change request

Are you looking workflow step at the current status? The workflow could be at multiple steps :
Try this

select x.SORT_ORDER,x.STEP_NAME,x.STATUS_NAME
from
(
SELECT NVL(KWFL_WORKFLOW_UTIL.get_step_label (CST.STEP_TRANSACTION_ID),
TO_CHAR(CWS.SORT_ORDER)) SORT_ORDER,
CWS.STEP_NAME STEP_NAME,
KCRT_REQUEST_UTIL.get_req_status_name(REQ.request_id, NVL(CST.VISIBLE_USER_STATUS_VALUE, CST.VISIBLE_RESULT_VALUE),
REQ.status_code, 'N') STATUS_NAME
,REQUEST_ID
FROM KNTA_USERS CU,
KWFL_STEP_TRANSACTIONS CST,
KWFL_WORKFLOW_STEPS CWS,
KWFL_WORKFLOW_INSTANCE_STEPS CWIS,
KWFL_WORKFLOW_INSTANCES CWI,
KCRT_REQUESTS REQ
WHERE CWI.TOP_INSTANCE_SOURCE_TYPE_CODE = 'IR'
AND CWI.TOP_INSTANCE_SOURCE_ID = REQ.REQUEST_ID --279013--284660
AND CWIS.WORKFLOW_INSTANCE_ID = CWI.WORKFLOW_INSTANCE_ID
AND CWS.WORKFLOW_STEP_ID = CWIS.WORKFLOW_STEP_ID
AND CST.STEP_TRANSACTION_ID = CWIS.CURRENT_STEP_TRANSACTION_ID
AND CU.USER_ID (+) = CWIS.LAST_UPDATED_BY
ORDER BY CWIS.LAST_UPDATE_DATE, cst.step_transaction_id, cws.sort_order, cws.step_name
) x
where 1=1
and x.STATUS_NAME in ('In Progress','Eligible')
and x.REQUEST_ID=[TOKEN]
ansmith
Occasional Advisor

Re: Query to determine the workflow step of a change request

Thank you, that's exactly what I was looking for. By the way, were do I find information on how to query the workflow and using KWFL_WORKFLOW_UTIL.
//Add this to "OnDomLoad" event