Maximo Open Forum

Nancy Lerner posted 08-01-2022 13:46

Marvin, please provide some additional details about your processes and data. 

First: How do you identify that a work order is a leak investigation?  Is there a particular problem code or work order classification that stores this information? 

Second: What is the process for documenting a repair in Maximo?  Do you always create a follow-up work order?  Or do you create a child work order for the repair?  Or is it a mix of both?  How do you capture the fact that this is a leak repar?  Is there a particular problem code, work classification, or specification attribute that captures this?  Or do you assume that a work order is a leak repair if it has a leak investigation as a parent or originating record? 

Third: Is your process followed consistently?  In other words, if the process is to use a particular problem code on all leak repairs or investigations, is it safe to assume that all such repairs or investigations include this code and that nothing other than a leak repair or investigation carries it?  If there are inconsistencies in following the process, what are some other clues to determining whether a work order is a leak investigation or repair?

Once these details are established, creating the SQL statements for the KPI should be pretty straightforward.

Craig Kokay posted 08-02-2022 10:29

Hi Marvin

Some background first.  When you create a follow-up work order the work order from which you generated the follow-up goes into the Originating Record field of the follow-up.  Phew, now say that three times in a row and this is your first clue.

The user is of course taken to the follow-up work order to complete any information needed to get that bit of work done,  You can, of course, go to the Related Records tab to see the immediate records upstream and downstream.  This is stored in the RELATEDRECORD table.  This is your second clue.

Also, the originating work order has the HASFOLLOWUPWORK flag enabled.  Beware as this is not quite correct if you're only looking for follow-up work orders,  The HASFOLLOWUPWORK field means any follow-up.  This could be an SR, work order, incident, change, release, problem, MOC.... It all depends on what industry solution you're using, but their all some sort of follow-up.

So, I've created a little SQL statement that shows the count of RELATEDRECORDs that are only work order based and are a follow-up from the work order you're enquiring from.

SELECT wonum, w.HASFOLLOWUPWORK ,(SELECT count(*) FROM RELATEDRECORD r WHERE RECORDKEY = w.wonum AND r.SITEID = w.siteid AND RELATEDRECCLASS= 'WORKORDER' AND RELATETYPE= 'FOLLOWUP')
FROM workorder w where problemcode = 'LEAK';

or maybe

SELECT wonum, w.HASFOLLOWUPWORK ,
(SELECT count(*) FROM RELATEDRECORD r WHERE RECORDKEY = w.wonum AND r.SITEID = w.siteid AND RELATEDRECCLASS= 'WORKORDER' AND RELATETYPE= 'FOLLOWUP') AS c1
FROM workorder w
WHERE (SELECT count(*) FROM RELATEDRECORD r WHERE RECORDKEY = w.wonum AND r.SITEID = w.siteid AND RELATEDRECCLASS= 'WORKORDER' AND RELATETYPE= 'FOLLOWUP') >=1;


I hope that this gives you some insight and an idea for the query.

Marvin R posted 08-04-2022 16:12

Thank you Nancy Lerner and Craig Kokay for attempting to take a stab at it, my apologies as well for not providing enough background for you both to properly provide feedback, you have provided Craig is definitely helpful; here I have added a few more details for better overall understanding of the challenge. 

For example when a customer calls for a potential threat of a gas leak, or a gas odor the phone operators on the line would create a Work Order that calls for an investigation. 

First thing that should be documented is the usage of the corresponding "Job Plan" so if that phone calls determines an  investigation the WO should be treated as such and the Job Plan should be "INVEST-LEAK-D" From that point forward if the investigation is consider a false alarm than the WO gets completed and the corresponding logs indicating false alarm would be recorded.

However, if the investigation turns into a real threat a follow up WO would need to be created to repair, amend, or correct the problem.  Now those WO's should have the following the Job Plans (RSPND-LK-C1-D, REPR-CL3LK-D, REPR-LK-ABOVE-T, REPR-CL12LK-D, RSPN-GO-D) So the problem that we're encountering is that some operator that go out to do the inspection are not creating a follow up WO and just adding the repair information in the "logs" which is something we need to request for a report to be able to bring along all the logs and literally go through the report logs to find all of the WO's where the corrective action took place and was recorded in the logs.  Now for that I don't think there is a query that could bring all that info across, which is fine but we will have to create a report for that. 

The real question is now is how could we potentially query the Investigation WO's so we can bring up all of the associated follow up WO's created, based on the "has follow up WO's" checked mark selected on the WO, or has "children WO's" associated to that main WO.   I know Craig provided me with some query I have yet tested, but upon further review I think I may be able to replace the problem code = 'LEAK' for  jpnum  IN = ('RSPND-GO-D', 'REPR-CL3LK-D', 'RSPND-LK-C1-D')