I'm taking the liberty of linking and referencing two blog posts by Adi Jaradat.
The first blog post explains how to create queries using Oracle-specific fuctions (CONNECT_BY_ROOT) to find the full hierarchy - and it provides a great explanation of the relationships between work orders. The SQL here is very similar to your second option.
https://adijaradat.com/follow-up-work-orders/
What I do most appreciate though, is the follow-up blog post where it's shown how this can be utilized to indicate which WO's can be closed, depending on their ultimate parent/child status. This is a great practical example of how to utilize this - and I believe those who stumble upon this thread sometime in the future might find this of interest too.
https://adijaradat.com/work-order-closure/
A. Jaradat further explains this visually, which I find to be of great help.
With all credits to A. Jaradat; these are the two SQL queries utilized for identifying these hierarchies:
SQL hierarchy:
WITH FOLLOWUP_RELATEDRECRODS AS (
SELECT *
FROM RELATEDRECORD
WHERE CLASS IN ('ACTIVITY','WORKORDER')
AND RELATETYPE='FOLLOWUP'
)
, WO_RELATEDRECORDS AS (
SELECT W.WONUM
,W.SITEID
,W.PARENT
,W.WORKTYPE
,W.REPORTDATE
,W.WOCLASS
,W.STATUS
,RR.RECORDKEY
,RR.CLASS
,RR.RELATEDRECKEY
,RR.RELATEDRECCLASS
,RR.RELATETYPE
FROM WORKORDER W
,FOLLOWUP_RELATEDRECRODS RR
WHERE W.WONUM=RR.RECORDKEY(+)
AND W.REPORTDATE >= TRUNC(SYSDATE,'YEAR')
)
, WOREKATEDRECORDS_HIERARCHY AS (
SELECT RR.*
,LEVEL WORRLEVEL
,CONNECT_BY_ROOT RR.WONUM AS TOPLEVELWO
,SUBSTR(SYS_CONNECT_BY_PATH(RR.WONUM||'('||RR.WORKTYPE||','||RR.STATUS||')', '->'), 3) AS WORELATEPATH
,SUBSTR(SYS_CONNECT_BY_PATH(RR.WORKTYPE, '->'), 3) AS WTRELATEPATH
,CONNECT_BY_ISLEAF AS WOLEAF
FROM WO_RELATEDRECORDS RR
CONNECT BY RR.WONUM = PRIOR RR.RELATEDRECKEY
)
SELECT DISTINCT WTRELATEPATH
,WORELATEPATH
,SITEID
FROM WOREKATEDRECORDS_HIERARCHY
WHERE WORRLEVEL > 1
SQL hieararchy for WO Closure:
WITH ROOT_WORKORDERS AS (
SELECT WONUM
FROM WORKORDER W
WHERE W.STATUS = 'COMP'
AND W.ISTASK = 0
)
, WO_TREE AS (
SELECT WONUM
,PARENT
,CONNECT_BY_ROOT WONUM AS TOPLEVEL_WO
,SUBSTR(SYS_CONNECT_BY_PATH(WONUM, '->'), 3) AS WO_PATH
,LEVEL WO_LEVEL
,CONNECT_BY_ISLEAF AS WO_IS_LEAF
,STATUS
,(CASE WHEN STATUS IN ('CAN','COMP','CLOSE') THEN 0 ELSE 1 END) STATUS_INDICATOR
,ROW_NUMBER() OVER(PARTITION BY WONUM ORDER BY LEVEL DESC) RN
,0 MR_INDICATOR
,0 PR_INDICATOR
,0 PO_INDICATOR
FROM WORKORDER
WHERE ISTASK = 0
START WITH WONUM IN (SELECT WONUM FROM ROOT_WORKORDERS)
CONNECT BY NOCYCLE PARENT=PRIOR WONUM
)
, ALL_WORKORDERS AS (
SELECT DISTINCT AWO.WONUM
,AWO.PARENT
,AWO.TOPLEVEL_WO
,AWO.WO_PATH
,AWO.WO_LEVEL
,AWO.WO_IS_LEAF
,AWO.STATUS
,AWO.STATUS_INDICATOR
,NVL2(MR.STATUS, (CASE WHEN MR.STATUS NOT IN (SELECT VALUE FROM SYNONYMDOMAIN WHERE DOMAINID='MRSTATUS' AND MAXVALUE IN ('CAN','CLOSE')) THEN 1 ELSE 0 END),0) MR_INDICATOR
,NVL2(PR.STATUS, (CASE WHEN PR.STATUS NOT IN (SELECT VALUE FROM SYNONYMDOMAIN WHERE DOMAINID='PRSTATUS' AND MAXVALUE IN ('CAN','COMP')) THEN 1 ELSE 0 END),0) PR_INDICATOR
,NVL2(PO.STATUS, (CASE WHEN PO.STATUS NOT IN (SELECT VALUE FROM SYNONYMDOMAIN WHERE DOMAINID='POSTATUS' AND MAXVALUE IN ('CAN','CLOSE')) THEN 1 ELSE 0 END),0) PO_INDICATOR
FROM WO_TREE AWO
,MRLINE
,MR
,PRLINE
,PR
,POLINE
,PO
WHERE AWO.RN=1
AND AWO.WONUM=MRLINE.REFWO(+)
AND MRLINE.MRNUM=MR.MRNUM(+)
AND AWO.WONUM=PRLINE.REFWO(+)
AND PRLINE.PRNUM=PR.PRNUM(+)
AND AWO.WONUM=POLINE.REFWO(+)
AND POLINE.PONUM=PO.PONUM(+)
)
, DISTINCT_WORKORDERS AS (
SELECT AWO.WONUM
,AWO.PARENT
,AWO.TOPLEVEL_WO
,AWO.WO_PATH
,AWO.WO_LEVEL
,WO_IS_LEAF
,STATUS
,SUM(AWO.STATUS_INDICATOR) STATUS_INDICATOR
,SUM(AWO.MR_INDICATOR) MR_INDICATOR
,SUM(AWO.PR_INDICATOR) PR_INDICATOR
,SUM(AWO.PO_INDICATOR) PO_INDICATOR
FROM ALL_WORKORDERS AWO
GROUP BY AWO.WONUM
,AWO.PARENT
,AWO.TOPLEVEL_WO
,AWO.WO_PATH
,AWO.WO_LEVEL
,AWO.WO_IS_LEAF
,AWO.STATUS
)
, CALCULATED_WORKORDERS AS (
SELECT TOPLEVEL_WO
,PARENT
,WONUM
,WO_PATH
,TOTAL_READY_INDICATOR
,TOTAL_STATUS_INDICATOR
,TOTAL_MR_INDICATOR
,TOTAL_PR_INDICATOR
,TOTAL_PO_INDICATOR
FROM DISTINCT_WORKORDERS
MODEL
PARTITION BY(TOPLEVEL_WO)
DIMENSION BY(PARENT, WONUM)
MEASURES(WO_LEVEL
,WO_PATH
,WO_IS_LEAF
,STATUS
,STATUS_INDICATOR
,MR_INDICATOR
,PR_INDICATOR
,PO_INDICATOR
,0 TOTAL_READY_INDICATOR
,0 TOTAL_STATUS_INDICATOR
,0 TOTAL_MR_INDICATOR
,0 TOTAL_PR_INDICATOR
,0 TOTAL_PO_INDICATOR
)
(
TOTAL_READY_INDICATOR [ANY,ANY] ORDER BY WO_LEVEL DESC,WONUM = NVL(SUM(STATUS_INDICATOR)[CV(WONUM),ANY],0)+STATUS_INDICATOR[CV(),CV()]
+ NVL(SUM(MR_INDICATOR)[CV(WONUM),ANY],0)+MR_INDICATOR[CV(),CV()]
+ NVL(SUM(PR_INDICATOR)[CV(WONUM),ANY],0)+PR_INDICATOR[CV(),CV()]
+ NVL(SUM(PO_INDICATOR)[CV(WONUM),ANY],0)+PO_INDICATOR[CV(),CV()]
,TOTAL_STATUS_INDICATOR[ANY,ANY] ORDER BY WO_LEVEL DESC,WONUM = NVL(SUM(STATUS_INDICATOR)[CV(WONUM),ANY],0)+STATUS_INDICATOR[CV(),CV()]
,TOTAL_MR_INDICATOR [ANY,ANY] ORDER BY WO_LEVEL DESC,WONUM = NVL(SUM(MR_INDICATOR)[CV(WONUM),ANY],0)+MR_INDICATOR[CV(),CV()]
,TOTAL_PR_INDICATOR [ANY,ANY] ORDER BY WO_LEVEL DESC,WONUM = NVL(SUM(PR_INDICATOR)[CV(WONUM),ANY],0)+PR_INDICATOR[CV(),CV()]
,TOTAL_PO_INDICATOR [ANY,ANY] ORDER BY WO_LEVEL DESC,WONUM = NVL(SUM(PO_INDICATOR)[CV(WONUM),ANY],0)+PO_INDICATOR[CV(),CV()]
)
ORDER BY WO_PATH
,TOPLEVEL_WO
,PARENT NULLS FIRST
,WONUM
)
SELECT *
FROM CALCULATED_WORKORDERS
WHERE TOTAL_READY_INDICATOR=0
------------------------------
Henrik Christiansen
------------------------------
Original Message:
Sent: Mon January 11, 2021 09:32 PM
From: The legend of User1971
Subject: Select WOs' top ancestor (top work package)
MAM 7.6.1.2:
I want to write an SQL query that does the following:
- Indicates the WO's top ancestor (top work package)
- Includes the level/order within the group
| WONUM | PARENT | CLASSIFICATION | TOPANCESTOR (TOPWORKPACKAGE) | LEVEL | WOHIERARCHY |
|---|
| WO55016 | | ROADS | WO55016 | 1 | WO55016 |
| WO55015 | WO55016 | ROADS \ WINTER | WO55016 | 2 | WO55016 \ WO55015 |
| WO43181 | WO55015 | ROADS \ WINTER | WO55016 | 3 | WO55016 \ WO55015 \ WO43181 |
| WO43183 | WO43181 | ROADS \ WINTER \ A | WO55016 | 4 | WO55016 \ WO55015 \ WO43181 \ WO43183 |
| WO37342 | WO43181 | ROADS \ WINTER \ B | WO55016 | 4 | WO55016 \ WO55015 \ WO43181 \ WO37342 |
| WO43182 | WO43181 | ROADS \ WINTER \ C | WO55016 | 4 | WO55016 \ WO55015 \ WO43181 \ WO43182 |
Is there a way to do that in an SQL query?
(Oracle 19c)
#AssetandFacilitiesManagement
#Maximo