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)
Notes from Steven Shull about how the View Cost window works

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.

#AssetandFacilitiesManagement
#Maximo

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

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

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

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.

#AssetandFacilitiesManagement
#Maximo

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

#Maximo
#AssetandFacilitiesManagement

#Maximo
#AssetandFacilitiesManagement

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.

#Maximo
#AssetandFacilitiesManagement

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.
Original Message:
Sent: Tue November 10, 2020 11:41 AM
From: Julio Hernandez
Subject: Select WO actuals and include task actuals?

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.

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


Original Message:
Sent: Fri November 06, 2020 08:53 PM
From: User1971
Subject: Select WO actuals and include task actuals?

This query works well if I'm not selecting many columns:

select
    coalesce(parent, wonum) as wonum,
    max(case when parent is null then classificationid end) as classificationid,
    sum(ACTLABCOST) as ACTLABCOST,
    sum(ACTMATCOST) as ACTMATCOST
from 
    workorder
group by
    coalesce(parent, wonum);
#AssetandFacilitiesManagement
#Maximo

#Maximo
#AssetandFacilitiesManagement

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.

#AssetandFacilitiesManagement
#Maximo

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?

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

#Maximo
#AssetandFacilitiesManagement

Here is the updated query.

create or replace view cgtaskrollup_vw as (
select
    wonum,
    task_count,
    actlabcost_tasks_incl,
    actmatcost_tasks_incl,
    acttoolcost_tasks_incl,
    actservcost_tasks_incl,
    acttotalcost_tasks_incl,
    
    case 
        when acttotalcost_tasks_incl > 0 then 1 
        else 0 
    end as has_positive_actuals,
    case
        when acttotalcost_tasks_incl < 0 then 'NEGATIVE'
        when acttotalcost_tasks_incl = 0 then 'ZERO'
        when acttotalcost_tasks_incl > 0 then 'POSITIVE'
    end as actuals_category,
    
    estlabcost_tasks_incl,
    estmatcost_tasks_incl,
    esttoolcost_tasks_incl,
    estservcost_tasks_incl,
    esttotalcost_tasks_incl,
    siteid
from
(
select
    wogroup as wonum,
    istask,
    count(taskid) over (partition by wogroup) as task_count,

    sum(actlabcost ) over (partition by wogroup) as  actlabcost_tasks_incl,
    sum(actmatcost ) over (partition by wogroup) as  actmatcost_tasks_incl,
    sum(acttoolcost) over (partition by wogroup) as acttoolcost_tasks_incl,
    sum(actservcost) over (partition by wogroup) as actservcost_tasks_incl,
    sum(actlabcost + actmatcost + acttoolcost + actservcost) over (partition by wogroup) as acttotalcost_tasks_incl,

    sum(estlabcost ) over (partition by wogroup) as  estlabcost_tasks_incl,
    sum(estmatcost ) over (partition by wogroup) as  estmatcost_tasks_incl,
    sum(esttoolcost) over (partition by wogroup) as esttoolcost_tasks_incl,
    sum(estservcost) over (partition by wogroup) as estservcost_tasks_incl,
    sum(estlabcost + estmatcost + esttoolcost + estservcost) over (partition by wogroup) as esttotalcost_tasks_incl,
    siteid
from
    workorder
where
    siteid = 'SERVICES'
)
where
    istask = 0
)

#Maximo
#AssetandFacilitiesManagement