Project and Portfolio Management Practitioners Forum
cancel

Need SQL for Workflow and Sub Workflow link

SOLVED
Go to solution
Highlighted
JHarris941
Super Contributor.

Need SQL for Workflow and Sub Workflow link

Hello All,

 

Im trying to build a query to display main workflows with their associated sub workflows and linked request type. Here is what im using so far:

 

SELECT DISTINCT
reqt.request_type_name,
kw.workflow_name
FROM
kwfl_workflows kw,
kcrt_request_types reqt,
kcrt_requests req
WHERE 1=1
and reqt.request_type_id(+) = req.request_type_id
and kw.workflow_id = req.workflow_id(+)
and reqt.request_type_name = 'workflow';

 

However subworkflows are not returning. Does anyone know how to relate sub workflows to main workflows in the database?

 

Thanks,

Jajcen

2 REPLIES
Utkarsh_Mishra
Acclaimed Contributor.
Solution

Re: Need SQL for Workflow and Sub Workflow link

Here you go... this query work if there is a rule to associate the workflow and request type; in case rule is not there then in those case you can simple modify the query to join with kcrt_requests table.

 

SELECT KWV.workflow_id       work_flow_id, 
       KWV.workflow_name     workflow_name, 
       KWSV.source_name      Sub_Workflow_Name, 
       KWSV.step_source_id   sub_workflow_id, 
       KRT.request_type_name associate_request 
FROM   kwfl_workflows_v kwv 
       left join kwfl_workflow_steps_v kwsv  
ON ( KWSV.workflow_id = KWV.workflow_id AND KWSV.step_type_code = 'WORKFLOW' ) left join knta_param_rules kpr
ON ( KPR.workflow_id = kwv.workflow_id AND rule_event_code = 'ON_CREATION' ) left join knta_parameter_set_contexts kpsc
ON ( KPSC.parameter_set_context_id = KPR.parameter_set_context_id ) join kcrt_request_types krt
ON ( KRT.request_type_id = KPSC.context_value )

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
JHarris941
Super Contributor.

Re: Need SQL for Workflow and Sub Workflow link

Magnifico! Thanks Utkarsh!