Running a Maximo Escalation to catch an event every 30d

I normally read through the Maximo forums for new ideas or lend a helping hand when I can. A recent question was asked on how to have a Maximo Escalation send a notification every 3 months.

Looking at the question, there were a couple of points raised:

  • The escalation needs to catch any work order that may be past due on a 30d frequency.
  • The escalation needs to repeat every 30d, so the Escalation should send another notice on a 30d frequency.
  • The escalation needs to run daily so the recipients don't get overwhelmed with notifications if the Escalation was run every 30d.

Forum user @winterZZR1400 came up with the start of an elegant solution:

Try in the Condition MOD(((statusdate - sysdate)- MOD(statusdate - sysdate,1))/90,1)= 0

I took what he posted and used a literal translation for testing, but that didn't work for me. The problem I ran into was the escalation ran once a day and the modulus on statusdate may not accurately catch a work order based modulus value with getdate() and the run time of the Escalation.

I looked at the code and determined the date/time stamp of statusdate and getdate() needed to be converted over to a date format. I converted both statusdate and getdate() to a yyyy-mm-dd ISO8601 format to see if the difference between the dates as an integer in days and check that the result was a modulus of 30.

The new Escalation Point: (DATEDIFF(day, CONVERT(date, statusdate, 23),CONVERT(date, getdate(), 23)) % 30) = 0

The overall Escalation looks like this:

Now when the a work order is over due, based on a 30d frequency, the maintenance team will get the following reminder:

The following work order has not had a status update in more than 180d. Please review the WO and check to see if the record needs to be forced to CLOSED status. If not, review the WO for a more appropriate status until further action can be taken.  

WO Num: 888888 
Last Status Date: 4/9/19 3:24 PM 
Asset: A113

* * Automated Message #1234 - Please Do Not Respond *

What... you don't have a cotton candy machine where you work?