SQL Scripting to Manage Person Records in Maximo

One of the most frustrating tasks I've had over the years as a Maximo administrator is cleaning up records for an employee that has separated from the company. It's frustrating because the call/ticket I will likely receive from a Maximo user is asking about how to work around an error message like this when changing the employee's Person record from ACTIVE to INACTIVE:

Searching IBM Support, you can find a couple of documents on what a Maximo user needs to do to inactivate a Person record:

And it's the last suggested support page that is the most annoying.

Why? Because a Maximo user will normally recognize that the previous employee can't be on an active record and they did their due diligence to remove the previous employee. But when they do a search on the employee, the Maximo user will see the previous employee is still listed on historical copies of a record - e.g. a Job Plan.

From the out of the box Maximo UI, there is no way for a Maximo user to update a REVISED Job Plan, so now a Maximo administrator will have to get involved with this.

Back end scripting to the rescue

The work around is the Maximo/SQL1 administrator has to remove the previous employee PERSONID from some of the historical records so the Person record can be set to Inactive. The challenge most administrators will find is that this issue can occur on several fields in Maximo:

  • Supervisor on Job Plans
  • Lead on Job Plans
  • Planned Labor on Job Plans2

Over the years I've come up with a SQL script to clear out where a Person record could be used from sources that will prevent a Person record being inactivated.

CAUTION: The script below should be tested before using on your Productive/Live environment.

One caveat to this script: In the script I've created, the Person record that you are trying to inactivated will be set to a NULL value in the various fields. Some organizations have made the related Person field a required field. For example the JOBPLAN.SUPERVISOR field on may be a required field. This script will set the value for JOBPLAN.SUPERVISOR to NULL. The next time a PM attempts to use that Job Plan, the PM WO Gen will error out because a required field is now blank.

SQL Script to remove Person fields:

declare @emp_id char(20)
set @emp_id ='WAYNEB01'

delete joblabor where laborcode = @emp_id and exists(select null from jobplan where jpnum=joblabor.jpnum and jobplanid=jobplan.jobplanid and status='ACTIVE')     
delete joblabor where laborcode = @emp_id and exists(select null from jobplan where jpnum=joblabor.jpnum and jobplanid=jobplan.jobplanid and status='INACTIVE')      
delete joblabor where laborcode = @emp_id and exists(select null from jobplan where jpnum=joblabor.jpnum and jobplanid=jobplan.jobplanid and status='REVISED')      
update jobplan set supervisor = null where supervisor = @emp_id and status='REVISED'
update jobplan set supervisor = null where supervisor = @emp_id and status='INACTIVE'
update jobplan set supervisor = null where supervisor = @emp_id and status='ACTIVE'
update jobplan set laborcode = null where laborcode = @emp_id and status='REVISED'
update jobplan set laborcode = null where laborcode = @emp_id and status='INACTIVE'
update jobplan set laborcode = null where laborcode = @emp_id and status='ACTIVE'
update pm set lead = null where lead = @emp_id
update pm set supervisor = null where supervisor = @emp_id
delete from persongroupteam where respparty = @emp_id

The script will remove the Person record from:

  • Supervisor on all Job Plans
  • Lead on all Job Plans
  • Planned Labor on all Job Plans
  • Supervisor on all PM's
  • Lead on all PM's
  • Remove Person from all Person Groups

After the script is run, all restricted fields should have been cleared with the employee's Person record. Maximo may3 give other error messages when attempting to set the Person record to INACTIVE. Likely those will be active transactional records (e.g. open work orders) that will have to migrate through their lifecycle until the transactional records are closed.

At this point, the Person record can be set to INACTIVE.

  1. To the organizations that don't give their Maximo administrator(s) SQL rights... why? ↩

  2. Technically the JOBLABOR table ↩

  3. Ok... probably will. ↩

Comments

Top