BenCroffut603954

Top Down (Recursive) Workflow Definition and History SQL Reports

Discussion created by BenCroffut603954 on Aug 21, 2018
Latest reply on Aug 21, 2018 by Pete Wirfs

I did a quick search and did not find the sql queries I needed for a top down Workflow Definition report to show active tasks as well as a top down (based on a RUNID) Workflow Status report. These are both simple but other columns and tables can be added fairly quickly.

 

Recursive Workflow Definition Report

 

WITH
JOB_TREE AS (
SELECT
LPAD(LEVEL, LEVEL, chr(9)) "Level"
, Subordinate
, Superordinate
, "Job Active"
FROM (
SELECT
JPP_OBJECT AS Subordinate
, OH_NAME AS Superordinate
, CASE jpp_active
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END "Job Active"
FROM uc4.JPP
INNER JOIN uc4.OH ON OH_IDNR = JPP_OH_IDNR
WHERE
OH_CLIENT = 1000
AND JPP_OTYPE IN ('JOBS', 'JOBP')
) root
START WITH Superordinate IN ('<insert workflow here>')
CONNECT BY PRIOR Subordinate = Superordinate
)
SELECT DISTINCT
Superordinate "JobPlan"
, Subordinate "Job"
, job_tree."Job Active"
--, ot_content "Command"
FROM JOB_TREE
INNER JOIN uc4.OH ON oh_name = Subordinate
INNER JOIN uc4.ot ON ot_oh_idnr = oh_idnr
--INNER JOIN uc4.jpp ON jpp_oh_idnr = oh.oh_idnr
WHERE
oh_client = 1000
-- AND ot_type = 0
--AND ot_content LIKE '%informatica.sh%'
ORDER BY
1, 2

 

This will return the Parent, The Workflow Task, and the Workflow Task Status.

 

Workflow Recursive Status Report (Based on runid)

 

WITH
HISTORY_TREE as (
SELECT
LPAD(LEVEL, LEVEL, chr(9)) "Level"
, Subordinate
, Superordinate
, "Task Status"
FROM (
SELECT AH_IDNR as Subordinate
, AH_PARENTPRC as Superordinate
, AH_STATUS as "Task Status"
FROM uc4.AH
WHERE ah_client = 1000
AND ah_otype in ('JOBS', 'EVNT', 'JOBP')
) root
START WITH Superordinate = <insert runid here>
CONNECT BY PRIOR Subordinate = Superordinate
)
SELECT DISTINCT
Superordinate "JobPlan"
, Subordinate "Job"
, history_tree."Task Status"
FROM HISTORY_TREE
ORDER BY
1, 2;

 

This will return the Parent RUNID, Child RUNID, and the Child Status Code

Outcomes