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. 



Previous posts:

  1. Migrating legacy text into Maximo Long Descriptions

    One of the trickier tasks a Maximo administrator will run into is migrating information from a legacy system into Maximo. And one of the trickiest migrations can be importing long text notes from a legacy system into Maximo's Long Description fields. When I have to migrate information into Maximo, understanding …

  2. Maximo Automation Script on Duplicate Records

    One of the frustrations I always have with Maximo is knowing something I want to do can be done, but not having the technical skills to cross the finish line. Automation Scripting continues to be in that category for me. This week I was asked by a couple of users …

  3. Setting the sort order of Select Value dialog box in Maximo

    One of the under-appreciated functions in Maximo is the ability to create custom value lists for a field using a Domain. The use of a Domain value list helps guide a Maximo user to use a specific set of choices to align the data choices used on a given field …

  4. Extending BIRT's hidden parameters - Pt 2

    I've continued to experiment with the use of Maximo BIRT's hidden parameter. In my previous post I was able to display values that were generated by related information of the Maximo user who ran the report. But the original data selection used the hidden username parameter in the SQL where …

  5. Extending the use of Maximo's hidden BIRT parameter

    One of the common queries used by Maximo power users is:

    siteid = (select defsite from maxusers where userid = :user)
    

    This selection limits records to the default site the current Maximo user is checked into. So if a query is saved for future use, it will work with any site a …

Top