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;