Super Contributor.. JHarris941 Super Contributor..
Super Contributor..
749 views

Need SQL for Workflow and Sub Workflow link

Jump to solution

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

Tags (1)
0 Likes
1 Solution

Accepted Solutions
Established Member.. Utkarsh_Mishra
Established Member..

Re: Need SQL for Workflow and Sub Workflow link

Jump to solution

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)
2 Replies
Established Member.. Utkarsh_Mishra
Established Member..

Re: Need SQL for Workflow and Sub Workflow link

Jump to solution

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)
Super Contributor.. JHarris941 Super Contributor..
Super Contributor..

Re: Need SQL for Workflow and Sub Workflow link

Jump to solution

Magnifico! Thanks Utkarsh!

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.