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

Need SQL for Workflow and Sub Workflow link

SOLVED
Go to solution
Highlighted
JHarris941
Regular Collector

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
Honored 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
Regular Collector

Re: Need SQL for Workflow and Sub Workflow link

Magnifico! Thanks Utkarsh!

//Add this to "OnDomLoad" event