Maximo Version of SMRP PM Compliance

One of the metrics commonly adopted by an organization early in their reliability journey is the Preventive Maintenance Compliance metric.[^92032] The goal of the metric is to understand how effective the maintenance and operations teams are getting PM actions completed. The goal is to get at least 90% of the PM's completed before its due date each month. When you look at the metric, it's a pretty simple calculation on the surface.

PM/PdM WO's completed by due date this month / PM/PdM WO's due this month

For example, if an organization had 384 PM2 work orders due in February, and got 318 work orders done on time,3 that equates to 82.8% completion. The challenge most organizations have is converting a simple definition into the complex SQL query needed to extract that definition out of their Maximo work history.

Defining the PM Compliance metric

SMRP defines PM Compliance as:1

This metric is a review of completed preventive maintenance (PM) and predictive maintenance (PdM) work orders, wherein the evaluation is against preset criteria for executing and completing the work.

The metric goes on to include guidance of an overall goal of an organization completing at least 90% of the PM work orders before its due date and giving 3 options to consider in determining if a PM/PdM activity was completed on time:

  1. A PM & PdM work order is considered completed on time if completed by the required date.
  2. A PM & PdM work order is considered completed on time if completed by the required date + one day.
  3. A PM & PdM work order is considered completed on time if completed by the required date + 20 % of the PM and PdM frequency up to a maximum of 28 days.

The 20% grace period is based on the following: 6

PM Frequency Grace Period
> 1 Year 28 Days
24 Months 28 Days
12 Months 28 Days
6 Months 28 Days
4 Months 24 Days
3 Months 18 Days
2 Months 12 Days
8 Weeks 11 Days
6 Weeks 8 Days
1 Month 6 Days
4 Weeks 6 Days
2 Weeks 3 Days
1 Weeks 1 Day

This post takes a look at how to create a metric in Maximo that uses the 20% grace period.

Translating SMRP into SQL

The first step in creating a metric that we can use in Maximo is to take the definition of the metric and convert it into a SQL query. The best method is to describe what the SQL statement should capture and then create the actual code. From the SMRP definition, one description could be:

Count all PM work orders that have been completed by a due date after the 1st of day of the previous month and before the last day of the previous month divided by all PM work orders due date after the 1st of day of the previous month and before the last day of the previous month

The basics of the query are:

count (wonum_PM_on_time) / count (wonum_all_PM)

Breaking this down into 2 parts, the numerator and the denominator, the SQL needed for each part can be created separately.

Finding the PM/PdM work orders completed on time

The numerator of the equation requires identifying all the PM's completed on time in the previous month. 

select count(wonum) 
from workorder
where pmnum is not null and (status = 'COMP' or status = 'CLOSED') and
pm_duedate >= dateadd(month,datediff(month,1,getdate()) -1,0) and
pm_dudate < dateadd(month,datediff(month,1,getdate()) -0,0) and 
actfinish <= dateadd(month,datediff(month,1,getdate()) -0,0)

Unfortunately out of the box Maximo does not have a field pm_duedate on the WORKORDER object.4 So this date will need to be calculated within the SQL query. Using the table above, due date rules can be set up inside a SQL Query:

(select case 
    when pm.frequnit='DAYS' and pm.frequency < 140 then (pm.frequency * 0.2 * 1.0) + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='DAYS' and pm.frequency >= 140 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency < 20 then pm.frequency * 0.2 * 7.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency >=20 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency < 5 then pm.frequency * 0.2 * 30.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency >= 5 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='YEARS' then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    end as freq_unit
from pm 
where pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid )

Reliability professionals will debate the choice of using isnull(workorder.schedstart, workorder.targstartdate) as part of the grace period because teams should be scheduling their work, so the workorder.schedstart should not be an empty field. We'll save that debate once a site hits 90% PM Compliance consistently.

Putting this all together gives us a SQL query of:

declare @worksite char(10)
set @worksite ='lnc'

select count(workorder.wonum)*1.0
from workorder 
join pm on pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid
where workorder.siteid = @worksite and 
workorder.istask=0 and workorder.pmnum is not null 
and (workorder.status = 'COMP' or workorder.status='CLOSE')
and (select case 
    when pm.frequnit='DAYS' and pm.frequency < 140 then (pm.frequency * 0.2 * 1.0) + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='DAYS' and pm.frequency >= 140 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency < 20 then pm.frequency * 0.2 * 7.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency >=20 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency < 5 then pm.frequency * 0.2 * 30.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency >= 5 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='YEARS' then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    end as freq_unit
from pm 
where pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid) >=  dateadd(month,datediff(month,1,getdate()) -1,0) 
and (select case 
    when pm.frequnit='DAYS' and pm.frequency < 140 then (pm.frequency * 0.2 * 1.0) + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='DAYS' and pm.frequency >= 140 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency < 20 then pm.frequency * 0.2 * 7.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency >=20 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency < 5 then pm.frequency * 0.2 * 30.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency >= 5 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='YEARS' then 28 + isnull(workorder.schedstart, workorder.targstartdate)
end as freq_unit
from pm 
where pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid) < dateadd(month,datediff(month,1,getdate()) -0,0) 
and workorder.actfinish < dateadd(month,datediff(month,1,getdate()) -0,0);

Running this query would give us a result of 318 work orders against our sample data set.

Finding the PM/PdM work orders due that month

The denominator of the PM Compliance metric requires identifying all the PM's that were due the previous month. 

select count(wonum) 
from workorder
where pmnum is not null and (status = 'COMP' or status = 'CLOSED') and
pm_duedate >= dateadd(month,datediff(month,1,getdate()) -1,0) and
pm_dudate < dateadd(month,datediff(month,1,getdate()) -0,0) and

The initial SQL query is pretty much the same as the numerator, minus actfinish <= dateadd(month,datediff(month,1,getdate()) -0,0). Using this logic, we've already done all the work from the numerator query and we get:

declare @worksite char(10)
set @worksite ='lnc'

select count(workorder.wonum)
from workorder 
join pm on pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid
where workorder.siteid = @worksite and 
workorder.istask=0 and workorder.pmnum is not null 
and (select case 
    when pm.frequnit='DAYS' and pm.frequency < 140 then (pm.frequency * 0.2 * 1.0) + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='DAYS' and pm.frequency >= 140 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency < 20 then pm.frequency * 0.2 * 7.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency >=20 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency < 5 then pm.frequency * 0.2 * 30.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency >= 5 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='YEARS' then 28 + isnull(workorder.schedstart, workorder.targstartdate)
end as freq_unit
from pm 
where pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid) >= dateadd(month,datediff(month,1,getdate()) -1,0)
and (select case 
    when pm.frequnit='DAYS' and pm.frequency < 140 then (pm.frequency * 0.2 * 1.0) + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='DAYS' and pm.frequency >= 140 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency < 20 then pm.frequency * 0.2 * 7.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency >=20 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency < 5 then pm.frequency * 0.2 * 30.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency >= 5 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='YEARS' then 28 + isnull(workorder.schedstart, workorder.targstartdate) 
end as freq_unit
from pm 
where pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid) < dateadd(month,datediff(month,1,getdate()) -0,0);

Running this query would give us a result of 384 work orders against our sample data set.

Putting the two parts together

Putting the two parts of the queries together require doing math inside a SQL query. The basic concept is to do select the previous select statements:

select ( (select count (wonum_PM_on_time) / 
(select count (wonum_all_PM) )

Putting the two previous parts together yields:

declare @worksite char(10)
set @worksite ='lnc'

select (
select count(workorder.wonum)*1.0
from workorder 
join pm on pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid
where workorder.siteid = @worksite and 
workorder.istask=0 and workorder.pmnum is not null 
and (workorder.status = 'COMP' or workorder.status='CLOSE')
and (select case 
    when pm.frequnit='DAYS' and pm.frequency < 140 then (pm.frequency * 0.2 * 1.0) + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='DAYS' and pm.frequency >= 140 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency < 20 then pm.frequency * 0.2 * 7.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency >=20 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency < 5 then pm.frequency * 0.2 * 30.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency >= 5 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='YEARS' then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    end as freq_unit
from pm 
where pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid) >=  dateadd(month,datediff(month,1,getdate()) -1,0) 
and (select case 
    when pm.frequnit='DAYS' and pm.frequency < 140 then (pm.frequency * 0.2 * 1.0) + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='DAYS' and pm.frequency >= 140 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency < 20 then pm.frequency * 0.2 * 7.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency >=20 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency < 5 then pm.frequency * 0.2 * 30.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency >= 5 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='YEARS' then 28 + isnull(workorder.schedstart, workorder.targstartdate)
end as freq_unit
from pm 
where pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid) < dateadd(month,datediff(month,1,getdate()) -0,0) 
and workorder.actfinish < dateadd(month,datediff(month,1,getdate()) -0,0) ) 
/ 
(select count(workorder.wonum)
from workorder 
join pm on pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid
where workorder.siteid = @worksite and 
workorder.istask=0 and workorder.pmnum is not null 
and (select case 
    when pm.frequnit='DAYS' and pm.frequency < 140 then (pm.frequency * 0.2 * 1.0) + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='DAYS' and pm.frequency >= 140 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency < 20 then pm.frequency * 0.2 * 7.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency >=20 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency < 5 then pm.frequency * 0.2 * 30.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency >= 5 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='YEARS' then 28 + isnull(workorder.schedstart, workorder.targstartdate)
end as freq_unit
from pm 
where pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid) >= dateadd(month,datediff(month,1,getdate()) -1,0)
and (select case 
    when pm.frequnit='DAYS' and pm.frequency < 140 then (pm.frequency * 0.2 * 1.0) + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='DAYS' and pm.frequency >= 140 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency < 20 then pm.frequency * 0.2 * 7.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='WEEKS' and pm.frequency >=20 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency < 5 then pm.frequency * 0.2 * 30.0 + isnull(workorder.schedstart, workorder.targstartdate)
    when pm.frequnit='MONTHS' and pm.frequency >= 5 then 28 + isnull(workorder.schedstart, workorder.targstartdate)
when pm.frequnit='YEARS' then 28 + isnull(workorder.schedstart, workorder.targstartdate) 
end as freq_unit
from pm 
where pm.pmnum=workorder.pmnum and pm.siteid=workorder.siteid) < dateadd(month,datediff(month,1,getdate()) -0,0) 
) as PM_GRACE_ONTIME

That's the entire SQL query to give a facility's PM Compliance for the previous month.7 Now this SQL query can be migrated to a BIRT report and scheduled on a monthly basis to generate an organization's PM Compliance.

Once the report is generated for a few months a PM Compliance trend can be generated and corrective actions can be taken when a facility is below their prescribed target.

PM Frequency Conversion

Within the calculation of the grace period is the conversion of the PM's frequency into how many days should be added to the PM's due date. To account for this calculation, you have to decide on a standard for the conversion of the PM.FREQUNIT into a unit of "Days". For the SQL queries, I included the conversion numbers I've used in my Maximo environment.

  • YEARS = 365 days
  • MONTHS = 30 days
  • WEEKS = 7 days

Depending on the PM.FREQUNIT used for a PM work order, the 20% grace period calculation could be slightly different for an equivalent frequency. For example:

PM Frequency Freq Conversion Calculated Days Grace Period
4 Months 4 * 30 120 Days 24 Days
16 Weeks 16 * 7 112 Days 23 Days
  • 4 Months = 4 * 30 = 120 days > 24 day grace
  • 16 Weeks = 16 * 7 = 112 days > 23 day grace

The best practice is to lay out this conversions used for different PM frequencies up front to stop any debate on the grace period before it starts.

  1. 5.4.14 Preventive Maintenance (PM) & Predictive Maintenance (PdM) Compliance, From SMRP Best Practices, 6th Edition ↩

  2. I'm just going to type "PM" going forward, but this includes PM, PdM, CdM work orders - basically any work order generated by the PM module. ↩

  3. "On time" is a part of the metric later defined by SMRP. ↩

  4. 5 ↩

  5. But it should ;-) ↩

  6. The frequency conversion is listed at the end of the post. ↩

  7. On SQL Server, chat with your DBA if you want that in DB2 or Oracle. ↩

Comments

Top