Work Order Closure
Updated: Feb 25
Work Order Closure may be referred to as “History” or “Archiving” and “Business Competition” in general it means progressing from COMP status to CLOSED status. Work Orders go through multiple statuses before getting closed and part of history. The Work Order status is a progress indicator which is pivotal for reporting and KPIs.
Work Order Status
Every status demands some business rules which are either enforced by the system or commonly agreed on as a practice within the organization. Some of those enforced rules are built-in and modifying those rules requires not only some development effort but also careful business consideration of the “all-in-all” picture of Order Processing.
Out-of-the-box Maximo is provides the following statuses:
Typically, organizations may decide to revoke manual status change of users and utilize the exceptionally powerful workflow engine that ships with Maximo. Configuring a workflow for Order Processing helps the organizations to control the Work Orders status precisely by reflecting user actions, in the form of workflow assignments, into status changes.
Completing Work Orders
Work Orders are conceptually completed in two phases. The first phase is when the Work Order hits the “COMP” status indicating that the technical or physical job is done. Technically completing an Order is especially useful because it signifies the start of the second phase of the Order completion which is “CLOSE”.
The “CLOSE” status is the second phase of the Order completion and before hitting this stage you must ensure that all Material Receipts are completed since “CLOSE” status will mark the Order as “History” and immutable except with “HISTEDIT” status. After the “CLOSE” status, all inventory reservations of unused materials are permanently removed.
One of the common practices in Order processing is to allow the Order to progress through statuses all the way to “COMP” (Technically Completed) and utilize Cron Tasks (Scheduled Tasks) to move Order to “CLOSE” status given that the Orders have achieved specific organization-specific business criteria, often referred to as Settlement Rule.
Settlement Rule could be as simple as closing the Order after a certain time window and it could go as complex as business demands. Let’s take the following example, assume the rule to close Work Orders is:
Analyze Work Order hierarchy and allow Orders to be closed if the following conditions are satisfied:
Order status is “COMP”
All related Material Requisitions are either Cancelled or Closed
All related Purchase Requisitions are either Cancelled or Completed
All related Purchase Orders are either Cancelled or Closed
All Child and sibling Work Orders satisfy the previous conditions.
We can approach this challenge by designing a capable algorithm:
Acquire list of Parent Work Orders and sibling Work Orders.
Build Work Order Tree using Parent-Child relationship
Analyze every Work Order in the tree against the following dimensions:
Assign “Readiness” indicator for every Work Order which indicates if the Work Order can be closed or not given the above 4 conditions in addition to the “Readiness” indicator of the child Work Orders.
Using the above algorithm we shall be able to analyze every Work Order individually and as a hierarchy. The code below will translate the algorithm into SQL which can be part of a Cron Task. This SQL requires you to have Oracle database as it uses Oracle-specific features.
Work Order Closure
In this example, just because of “Child 122” we will be able to close all Work Orders except its ancestor Work Orders “Child 12”, “Child 1”, and “Parent”.
… and the Code
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