Maximo Open Forum

 View Only

 Need a query to identify parent work orders where all it's children have been updated to COMP status

Jump to  Best Answer
  • Work Management
Glen Holt's profile image
Glen Holt posted 01-23-2024 14:59

Looking to create a query that identifies any parent work order where all associated child work orders have been updated to COMP status.

Thanks in advance for any help that can be provided.

Nancy Lerner's profile image
Nancy Lerner posted 01-24-2024 10:26  Best Answer

Glen, if you'd like to find all open parent work orders for which all child work orders are either in COMP status or else have been closed or canceled, you can use the following where clause:

workorder.historyflag = 0 and workorder.istask = 0 and  workorder.haschildren = 1 and not exists (select 1 from workorder wo where wo.parent = workorder.wonum and wo.siteid = workorder.siteid and wo.historyflag = 0 and wo.status <> 'COMP' )

If you want to limit your results to just those parents for which all children are in COMP status (excluding parents for which any children have already been closed or canceled), you could eliminate the wo.historyflag = 0 from the nested select statement. 

If you want to that limit the results to parent work orders for which all non-task children orders are in COMP status or are already closed (i.e., ignore the status of tasks), then you could add "and wo.istask = 0" to the nested select statement.

If the business reason for this query is to find open parents that are not yet in COMP status and for which all child work orders are completed or closed, then you could add workorder.status <> 'COMP' to the beginning of the where clause.

Adam Bearden's profile image
Adam Bearden posted 01-24-2024 09:33

I think this may help.  You might have to manipulate it a little....

select * from workorder 
where wonum in(select wonum from workorder where istask = 1 and status = 'COMP')
AND parent in(select wonum from workorder where siteid = 'BEDFORD');

Hope this helps!

A.S. Bearden

Travis Herron's profile image
Travis Herron posted 01-24-2024 10:26

If you're doing what I think you're doing -- which is finding Work Orders where all the children are "done" but the parent isn't yet Completed, you may also want to consider the other ways that a child Work Order could have "ended" -- having the status changed to CLOSE or CAN.

select * from workorder
where status not in (select value from synonymdomain where domainid = 'WOSTATUS' and maxvalue in ('CAN', 'COMP', 'CLOSE')) 
and siteid = 'MYSITE' 
and wonum in (select parent from workorder) 
and wonum not in (select parent from workorder where parent is not null and siteid = 'MYSITE' and status not in (select value from synonymdomain where domainid = 'WOSTATUS' and maxvalue in ('CAN', 'COMP', 'CLOSE')))

You may need to tweak that to accommodate Tasks.

Glen Holt's profile image
Glen Holt posted 01-24-2024 13:14

I don't see a reply function hopefully clicking on Answer works. Just want to thank everyone for their input.  After utilizing some of these ideas and adding some other details and specific person group information I was able to create a query that gave me exactly the info I needed.  Thanks so much!

Related Content