IBM Maximo: Re-Opening a Work Order in Maximo 4.1.1

This article gives you the instructions and Oracle PL/SQL Source code in order to change an existing Maximo 4 Work Order (WO) from a CLOSE state (where no changes are possible) back to HANDBACK state (where the record can be updated).

This code has been tested in an Oracle 8i database environment with Maximo 4.1.1 (Service pack 3) as the front end. When you run this code and look at the audit trace of a record a new entry will show that the Work Order has changed state. You will lose the records in the Equipment Hierarchy that show the Work Order has been previously closed. If you are in a tightly regulated (i.e. Pharmaceutical) environment you should carefully study this code, run it on a test system, and study the impact it has on your audit records.

NOTE: This code was written and tested using a Product called PL/SQL Developer (by AllRoundAutomations). This allows you to have output variables in blocks of PL/SQL code. If you are not running PL/SQL Developer then you will almost certainly need to modify this block of code. It will definitely not work in SQL * Plus.

This code is in two parts, the first is a simple Oracle PL/SQL script that takes two parameters; p_WorkOrder (the Work Order number) and :p_User (the user who the change should be audited against). When executed the procedure will return a message in the :p_Result variable. This will usually be "OK" meaning everything worked or an error message if it didn't.

It should be noted for validation purposes the User specified must exist as a record in the LABOR tables.

declare
 -- Purpose: This procedure rolls back a Work Order from CLOSED state back into
 --  HANDBACK (this allows a normal maximo user to edit it).
 cursor c_getWOStatus is
   select wo.wonum,
     wo.glaccount
   from   workorder wo
   where  wo.wonum  = :p_WorkOrder
   and    wo.status = 'CLOSE';

 v_ChangeDate  date;
 v_User        labor.laborcode%Type; -- lib_labor routines require a writable string
 v_RecordCount number;
begin
 v_ChangeDate := SYSDATE; -- This ensures all changes have the same date/time stamp
 v_User       := :p_User;
 if not lib_labor.validateLaborCode(v_User) then
   :p_Result := 'ERROR: Labour code "' || v_User || '" does not exist';
 else
   v_RecordCount := 0; -- keep track of the number of records updated
   for v_WorkOrder in c_getWOStatus loop
     v_RecordCount := v_RecordCount + 1;
   
     -- Insert an audit record to make sure that this change is "historied"
     insert into WOSTATUS (WONUM, STATUS, CHANGEDATE, CHANGEBY, GLACCOUNT)
     values (:p_WorkOrder, 'HANDBACK', v_ChangeDate, v_User, v_WorkOrder.Glaccount);
 
     -- Remove the existing records in EQHIERARCHY
     delete from eqhierarchy
     where wonum = :p_WorkOrder;
   
     -- Update the Work Order itself
     update workorder wo
     set wo.status   = 'HANDBACK',
       wo.statusdate = v_ChangeDate,
       wo.changedate = v_ChangeDate
     where wo.wonum = :p_WorkOrder
     and wo.status  = 'CLOSE';
   end loop;

   -- Ensure that we return something as the result.
   if v_RecordCount = 1 then
     :p_Result := 'OK';
   elsif v_RecordCount > 1 then
     :p_Result := 'ERROR: Multiple workorders found for WO ' || :p_WorkOrder; -- *should* be impossible
     rollback; -- do not commit any changes!
   else
     :p_Result := 'ERROR: Work Order ' || :p_WorkOrder || ' does not exist/ is not in CLOSED state';
   end if;
 end if;

-- Commit changes (if any) to the database
 commit;
exception
when others then
 :p_Result := 'ERROR: PL/SQL error' || SQLERRM || ' (' || SQLERRM || ')';
 rollback;
end;

The second part of the code is the LIB_LABOR package. I created this simply to save myself some time validating labor records. There is no reason why the routines below couldn't just be copied and pasted into the script above and executed from within that (except, of course, the it's a terribly way to do ongoing development - but sometimes the terrible way to do something long term is also the way to get soemthing done quickly).

This package should be installed as your MAXIMO user (and should be accessible to the script running above):

create or replace package lib_labor is

function getEMail(
  p_LaborCode in varchar2) return varchar2;

function getFormattedContactDetails(
  p_LaborCode in varchar2,
  p_Format in varchar2) return varchar2;

function validateLaborCode(
  p_LaborCode in out varchar2) return Boolean;

end lib_labor;

create or replace package body lib_labor is

function getEMail(
  p_LaborCode in varchar2) return varchar2 as
begin
  return getFormattedContactDetails(
    p_LaborCode => p_LaborCode,
    p_Format => '%EMAIL%');
end getEMail;

function getFormattedContactDetails(
  p_LaborCode in varchar2,
  p_Format in varchar2) return varchar2 as
  pragma autonomous_transaction;

  cursor c_Labor is
    select l.Name, l.CallId extension, l.pagepin email
    from labor l
    where l.laborcode = upper(p_LaborCode)
    and rownum = 1;

  v_Result varchar2(255);
begin
  for v_Labor in c_Labor loop
    v_Result := Replace(p_Format, '%NAME%', v_Labor.Name);
    v_Result := Replace(v_Result, '%EXT%', v_Labor.extension);
    v_Result := Replace(v_Result, '%EMAIL%', v_Labor.email);
    v_Result := Replace(v_Result, '%CODE%', p_LaborCode);
  end loop;
  return v_Result;
end getFormattedContactDetails;

function validateLaborCode(
  p_LaborCode in out varchar2) return Boolean as
begin
  if p_LaborCode <> Upper(p_LaborCode) then
    p_LaborCode := Upper(p_LaborCode);
  end if;

  return (getFormattedContactDetails(p_LaborCode, '%CODE%') is not null);
end validateLaborCode;

end lib_labor;