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;