• Adi Jaradat

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.


Closing Rules


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:

  1. Order status is “COMP”

  2. All related Material Requisitions are either Cancelled or Closed

  3. All related Purchase Requisitions are either Cancelled or Completed

  4. All related Purchase Orders are either Cancelled or Closed

  5. All Child and sibling Work Orders satisfy the previous conditions.

We can approach this challenge by designing a capable algorithm:

  1. Acquire list of Parent Work Orders and sibling Work Orders.

  2. Build Work Order Tree using Parent-Child relationship

  3. Analyze every Work Order in the tree against the following dimensions:

  4. WO status

  5. MR status

  6. PR status

  7. PO status

  8. 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

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 

Links

https://adijaradat.com/post/follow-up-work-orders/

https://www.ibm.com/support/knowledgecenter/en/SSLKT6_7.6.0.9/com.ibm.mbs.doc/mbs_common/c_wo_status.html

5 views0 comments

Recent Posts

See All