MAximo workorder total labor costs and total Material costs

Question

I'm working with my DBA to try to figure out a way to roll up all costs associated with a Work Order. Since any work Order can have multiple child work orders (through multiple "generations") as well as related work orders (through the RELATEDRECORDS table), I need to be able to get the total of the ACTLABORCOST and ACTMATERIALCOST fields for all child and related work orders (as well as each of their child and related work orders). I've worked though a hierarchical query (using CONNECT BY PRIOR) to get all the children, grandchildren, etc., but I'm stuck on the related work orders. Since every work order can have a related work order with it's own children and related work orders, I need an Oracle function that drills down through the children and the related work orders and their children and related work orders. Since I would think that this is something that should be fairly common, I'm hoping that there is someone who has done this and can share what they've done.

Answer 1

Have you considered the WOGRANDTOTAL object? Its description in MAXOBJECT is "Non-Persistent table to display WO grandtotals". There is a dialog in the Work Order Tracking application that you can get to from the Select Action / More Actions menu. Since you mentioned it repeatedly, I should note that WOGRANDTOTAL values do not include joins across RELATEDRECORDS to other work order hierarchies.

You can also save yourself the complication of CONNECT BY PRIOR by joining to WOANCESTOR, which is effectively a dump from a CONNECT BY PRIOR query. (There are other %ANCESTOR tables for other hierarchies.)

I think a recursive automation script would be the best way to do what you want, if you need the results in Maximo. If you need the total cost outside of Maximo, maybe a recursive function would work.

Answer 2

Another option would be a recursive query, as suggested by Francisco Sitja. Since my Oracle didn't allow 2 UNION ALLs, I had to joint to the WOANCESTOR table in both child queries instead of dedicating a UNION ALL for doing the WO hierarchy. I was then able to use the one permitted UNION ALL for doing the RELATEDRECORD hierarchy. And it seems to run pretty quickly.

with mywos (wonum, parent, taskid, worktype, description, origrecordid, woclass, siteid) as (
    -- normal WO hierarchy
    select wo.wonum, wo.parent, wo.taskid, wo.worktype, wo.description, wo.origrecordid, wo.woclass, wo.siteid
    from woancestor a
        join workorder wo
            on a.wonum = wo.wonum
            and a.siteid = wo.siteid
    where a.ancestor = 'MY-STARTING-WONUM'
    union all
    -- WO hierarchy associated via RELATEDRECORD
    select wo.wonum, wo.parent, wo.taskid, wo.worktype, wo.description, wo.origrecordid, wo.woclass, wo.siteid
    from mywos
        join relatedrecord rr
            on mywos.woclass = rr.class
            and mywos.siteid = rr.siteid
            and mywos.wonum = rr.recordkey
            -- prevent cycle / going back up the hierarchy
            and rr.relatetype not in ('ORIGINATOR')
        join woancestor a
            on rr.relatedrecsiteid = a.siteid
            and rr.relatedreckey = a.ancestor 
        join workorder wo
            on a.siteid = wo.siteid
            and a.wonum = wo.wonum
)
select * from mywos
;

Answer 3

We finally figured out how to pull this off.

WITH WO(WONUM,
PARENT) AS
((SELECT X.WONUM,
                  X.PARENT
        FROM (SELECT R.RECORDKEY WONUM,
                                    R.RELATEDRECKEY PARENT
                        FROM MAXIMO.RELATEDRECORD R
                        WHERE R.RELATEDRECKEY = '382418'
                    UNION ALL
                    SELECT W.WONUM,
                                    W.PARENT
                        FROM MAXIMO.WORKORDER W
                        START WITH W.PARENT = '382418'
                    CONNECT BY PRIOR W.WONUM = W.PARENT) X) 
           UNION ALL 
  SELECT W.WONUM, W.PARENT FROM MAXIMO.WORKORDER W, WO WHERE W.WONUM = WO.PARENT)
SELECT DISTINCT WONUM FROM WO;

This returns a list of all of the child and related work orders for a given work order.