So, I still not sure that the query is correct. I like your style I like your style of query though my brain says, nope. I'm thinking that you have not excluded task work orders.
There should be no way for a PM to be run twice by a cron task, by that, I mean the PM once generated should no longer be eligible until it reaches the next frequency unit. This would be a tad easier if the PM counter was passed over as well.
So run the below SQL using the theory that when a PM with a route is generated, there will always be parent work order assuming you have selected the option to generate as child WO rather than tasks. Adjust as needed.
SELECT WONUM, pmnum, ROUTE , reportdate,
(SELECT count(*) FROM workorder w2 WHERE w2.parent = w.wonum AND w2.siteid = w.siteid AND istask=0) AS num_child
FROM workorder w
WHERE pmnum IS NOT NULL
AND istask = 0;
Looking at your timestamp, one would assume that your work orders are not being manually generated. Let's see what result you get now?
------------------------------
===============================
Craig Kokay,
Lead Senior Maximo/IoT Consultant
ISW
Sydney, NSW, Australia
Ph: 0411-682-040
=================================
#IBMChampion2021
------------------------------
Original Message:
Sent: Mon May 10, 2021 08:05 PM
From: User1971
Subject: Determine if WO was created from a scheduled PM -or- manual PM action?
@Craig Kokay
I have a weird issue where my scheduled PMs (large WO hierarchies) sometimes run twice, and are sometimes missing child WOs.
There should be 1683 child WOs in every WO hierarchy -- since there are 1683 route stops in the route. And the PM should only run once per day:
I had wondered if the PM had been manually run via the Generate Work Order action in some cases -- which would have explained the duplicate WOs. But I realized after posting this question that I could just query WORKORDER.REPORTEDBY='MAXADMIN' to only get the WOs that were automatically generated/scheduled. So it looks like all those WOs were created automatically by the system. So I'm not sure what's going on.
For what it's worth, here is the source query for the resultset above:
--p = source is parent WO records--c = source is child WO recordsselect p.wonum as p_wonum, p.pmnum as p_pmnum, p.route as p_route, p.reportdate as p_reportdate, null as space, c.parent as c_parent, c.pmnum as c_pmnum, c.route as c_route, c.wo_count as c_wo_count, c.min_reportdate as c_min_reportdate, c.max_reportdate as c_max_reportdate, c.processing_time_hrs as c_processing_time_hrsfrom (select * from maximo.workorder where parent is null and (pmnum like 'BC%' or pmnum like 'SDW%') and reportedby = 'MAXADMIN') pleft join ( select parent as parent, pmnum as pmnum, route as route, max(reportedby) as max_reportedby, count(*) as wo_count, min(reportdate) as min_reportdate, max(reportdate) as max_reportdate, round((max(reportdate) - min(reportdate)) * 24,1) as processing_time_hrs from maximo.workorder group by parent, pmnum, route, changeby order by min_reportdate desc ) c on p.wonum = c.parentorder by p.reportdate desc
.
I'll admit that I'm new to PMs though, and have probably been staring at this for too long. I've likely just made a silly mistake somewhere.
Cheers.
#Maximo
#AssetandFacilitiesManagement