Maximo

Expand all | Collapse all

Select WO actuals and include task actuals?

Jump to Best Answer
User1971

User1971Fri November 06, 2020 08:54 PM

  • 1.  Select WO actuals and include task actuals?

    Posted Thu November 05, 2020 09:49 PM
    Edited by User1971 Wed September 22, 2021 08:50 PM

    Maximo 7.6.1.2; Oracle 19c


    I want to write a SQL query that selects WOs (non-tasks) - and include the costs of their tasks.

    The query would mimic the Actuals column in the View Costs window in Work Order Tracking:

       (WOTRACK --> More Actions --> View --> Costs)

    What's the simplest way to select WO actuals (and include the task actuals)?


    Related:
    Use WOTOTAL non-persistent object in WOTRACK
    SQL Window Functions: Rollup WO costs (including tasks)

    Reminder:
    If we query the TRANS tables (like LABTRANS), then we need to remember to account for approved/unapproved transactions. We might want to exclude unapproved transactions from our queries.



  • 2.  RE: Select WO actuals and include task actuals?

    Posted Fri November 06, 2020 02:17 AM
    Have you looked at using a subquery against the WOANCESTOR table to get all child WO's, filter tasks as needed, and then summing up WORKORDER.ACTxxx as needed?  That would be my first approach to this, and it would give you some flexibility further down the road in terms of adjusting it to suit larger work order hierarchies, or just single workorders with tasks.


    ------------------------------
    Henrik Christiansen
    ------------------------------



  • 3.  RE: Select WO actuals and include task actuals?

    User Group Leader
    Posted Fri November 06, 2020 04:10 AM
    Maybe something along these lines:

    select wonum, siteid, a.actlabcost+(
    select nvl(sum(b.actlabcost),0) from workorder b where b.istask=1 and b.parent=a.wonum and b.siteid=a.siteid) as totlabcost,
    a.actmatcost+(
    select nvl(sum(b.actmatcost),0) from workorder b where b.istask=1 and b.parent=a.wonum and b.siteid=a.siteid) as totmatcost,
    a.actservcost+(
    select nvl(sum(b.actservcost),0) from workorder b where b.istask=1 and b.parent=a.wonum and b.siteid=a.siteid) as totservcost,
    a.acttoolcost+(
    select nvl(sum(b.acttoolcost),0) from workorder b where b.istask=1 and b.parent=a.wonum and b.siteid=a.siteid) as tottoolcost
    from workorder a
    where istask=0;

    Give it a try and see if you can work with it to at least give you a start

    Regards
    Steve

    ------------------------------
    Steve Lee
    Maximo Technical Sales Specialist
    IBM
    Leeds
    ------------------------------



  • 4.  RE: Select WO actuals and include task actuals?

    Posted Fri November 06, 2020 10:17 AM
    A work order and all of its tasks share a WOGROUP, which is the WONUM of the main work order. So, given WO1, CHILDWO1 and TASK1 are a hierarchy, WO1 and TASK1 will both have WO1 for their WOGROUP, and CHILDWO1 will have its own, separate WOGROUP of CHILDWO1.

    WONUM, WOGROUP, PARENT
    WO1, WO1, (null)
    CHILDWO1, CHILDWO1, WO1
    TASK1, WO1, WO1

    WOANCESTOR would have

    WONUM, ANCESTOR
    WO1, WO1
    CHILDWO1, CHILDWO1
    CHILDWO1, WO1
    TASK1, WO1
    TASK1, TASK1

    ------------------------------
    Blessings,
    Jason Uppenborn
    Sr. Technical Maximo Consultant
    Ontracks Consulting
    ------------------------------



  • 5.  RE: Select WO actuals and include task actuals?

    Posted Mon September 20, 2021 08:45 AM
    Edited by User1971 Thu September 23, 2021 10:30 PM

    Thanks Jason. I doubt I would have ever known about WOGROUP if you hadn't mentioned it.


    It's strange how there doesn't seem to be any proper documentation about WOGROUP. I tried googling "maximo" "wogroup" "task" but didn't get anywhere. 


    The ERD description isn't helpful either:
    "This column is used to enhance performance of the Work Orders app."

    That doesn't tell us anything about the logic behind the column.


    Like you said, WOGROUP groups together WOs and their tasks. It doesn't group child WOs with their parent WOs.




    As you have suggested elsewhere, I should be able to simplify my query:



    select
        wogroup as wonum,
        sum(actlabcost)  as actlabcost_incl_tasks,
        sum(actmatcost) as actmatcost_incl_tasks,
        sum(actservcost) as actservcost_incl_tasks,
        sum(acttoolcost)  as acttoolcost_incl_tasks,
        sum(actlabcost + actmatcost + actservcost + acttoolcost) as acttotalcost_incl_tasks
    from
        maximo.workorder
    group by
        wogroup




    The actuals columns can't ever be null (they're mandatory fields in the DB). So we don't need to use NVL() or COALESCE() to convert nulls to zeros. And we're not doing any joins, so we're not going to "create" nulls where there weren't any records to join to.




  • 6.  RE: Select WO actuals and include task actuals?

    Posted Fri November 06, 2020 03:40 PM
    Edited by Tanvi Kakodkar Mon November 09, 2020 05:00 AM
    There are many ways to do this but relying on the WOGroup avoids having to bring the WOAncestor into it.  Below are 2 options:

    SELECT WO.WONum, WO.WorkType,
    (SELECT SUM(ActLabCost) FROM WorkOrder AC WHERE AC.WOGroup = WO.WOGroup) as "Actual Labor Cost",
    (SELECT SUM(ActMatCost) FROM WorkOrder AC WHERE AC.WOGroup = WO.WOGroup) as "Actual Material Cost"
    FROM WorkOrder WO
    WHERE WO.IsTask = 0
    ORDER BY WO.WONum
    ;

    This option avoids having to use a GROUP BY but the multiple embedded SELECTs may not perform as well as the next option.

    SELECT WO.WONum, MIN(WO.WorkType) as "Work Type",
    SUM(AC.ActLabCost) as "Actual Labor Cost",
    SUM(AC.ActMatCost) as "Actual Material Cost"
    FROM WorkOrder WO
    INNER JOIN WorkOrder AC
    ON AC.WOGroup = WO.WOGroup
    WHERE WO.IsTask = 0
    GROUP BY WO.WONum
    ORDER BY WO.WONum
    ;

    This option relies on a JOIN between the Non-Task WorkOrder records and all WorkOrder records with the same WOGroup including the Non-Task WO.

    Using the "MIN(WO.WorkType)" avoids having to include every field you want to include from the Non-Task WO in the GROUP BY clause.but it may be a bit harder to read.  Just using "WO.WorkType" in the main SELECT and repeating all the "WO.Xxx..." columns in the GROUP BY clause accomplishes the same thing.




    ------------------------------
    Julio Hernandez
    ------------------------------



  • 7.  RE: Select WO actuals and include task actuals?

    Posted Fri November 06, 2020 08:54 PM
    Edited by User1971 Tue November 10, 2020 11:52 AM


  • 8.  RE: Select WO actuals and include task actuals?

    Posted Sat November 07, 2020 07:03 PM
    Edited by User1971 Tue December 22, 2020 02:09 PM


  • 9.  RE: Select WO actuals and include task actuals?

    Posted Tue November 10, 2020 11:42 AM
    That's actually not a good approach.  When you group by "coalesce(parent, wonum)", the costs charged directly to a child WO will be included with the Parent WO and the ClassificationID won't be what you want either.


  • 10.  RE: Select WO actuals and include task actuals?

    Posted Tue November 10, 2020 11:56 AM
    Good point. That query was provided by someone on Stack Overflow (a non-Maximo person).
    https://stackoverflow.com/questions/64722995/select-attributes-from-parent-rows-and-include-cost-of-children

    I hadn't planned to use that query; I'm planning to use the second query instead (the longer one that works well for selecting lots of columns).

    I've deleted the query in question (the first query) from this post.

    Thanks.


  • 11.  RE: Select WO actuals and include task actuals?

    Posted Mon December 21, 2020 06:19 PM
    Edited by User1971 Fri September 03, 2021 03:40 PM


  • 12.  RE: Select WO actuals and include task actuals?

    Posted Tue December 22, 2020 12:45 PM

    If you have multiple Sites, your JOIN should include the SiteID in a WHERE clause and/or in the ON clause.

    You used NVL around the cost fields in the main SELECT but not in the Tasks GROUP BY Query.  Since the Act... columns are not flagged as REQUIRED, you should use NVL throughout to be safe.




  • 13.  RE: Select WO actuals and include task actuals?

    Posted Tue December 22, 2020 01:59 PM
    Edited by User1971 Tue December 29, 2020 01:11 AM

    Thanks for you help.

    When I look at the WORKORDER table, it looks like the act___ columns don't allow nulls. Is that good enough?

    More info here: Can the WORKORDER actuals columns ever be null?

    -------------------------------------------



  • 14.  RE: Select WO actuals and include task actuals?
    Best Answer

    Posted Tue December 22, 2020 02:12 PM
    Edited by User1971 Wed September 22, 2021 08:48 PM

    Here is the updated query.

    select
        nt.wonum,
        nvl(nt.actlabcost,0) + nvl(t.t_actlabcost,0) as actlabcost,
        nvl(nt.actmatcost,0) + nvl(t.t_actmatcost,0) as actmatcost,
        nvl(nt.actservcost,0) + nvl(t.t_actservcost,0) as actservcost,
        nvl(nt.acttoolcost,0) + nvl(t.t_acttoolcost,0) as acttoolcost,
        nvl(t.t_acttotalcost,0) + nvl(nt.actlabcost,0) + nvl(nt.actmatcost,0) + nvl(nt.actservcost,0) + nvl(nt.acttoolcost,0) as acttotalcost
    from
        maximo.workorder nt --non-task WOs
    left join
        (
        select
            parent,
            sum(actlabcost) as t_actlabcost,
            sum(actmatcost) as t_actmatcost,
            sum(actservcost) as t_actservcost,
            sum(acttoolcost) as t_acttoolcost,
            sum(actlabcost + actmatcost + actservcost + acttoolcost) as t_acttotalcost
        from
            maximo.workorder
        where
            istask = 1
            and woclass in ('WORKORDER', 'ACTIVITY')
            and siteid = 'SERVICES'
        group by
            parent
        ) t --tasks
        on nt.wonum = t.parent
    where
        nt.istask = 0
        and woclass in ('WORKORDER', 'ACTIVITY')
        and siteid = 'SERVICES'