Load Maximo Failure codes or Failure class hierarchy using sql scripts

Failure Class is a hierarchy with Problem, Cause and Remedy for a specific category of failure, for example, PUMPS, ECIRCUIT etc.,

Failure Class can be associated with an Asset or Location. It will be copied to a Work Order if it is linked to this Asset or Location. 

Load the Failure Class and Failure Codes
Failure codes are PB01, CA01, RY01 etc.,  failureclass --> PUMPS.

Load the Failure Class and Failure Codes using MXLoader or using DML sql scripts. 

MXLoader:

Failure Codes

Failure Class

SQL:

insert into failurecode (failurecode, description, orgid, failurecodeid, langcode, hasld) 

values ( ' STOPPED' , 'Stopped', 'EAGLENA', failurecodeseq.nextval, 'EN' , 0  ) ; 

-- create failure code to form hierarchy. These codes can be a problem, cause or remedy defined by type field in failurelist table

insert into failurecode (failurecode, description, orgid, failurecodeid, langcode, hasld) 

values ( ' PUMPS' , 'Failure Class for PUMPS', 'EAGLENA', failurecodeseq.nextval, 'EN' , 0  ) ; 

-- create failure class in failurecode table

insert into failurelist (failurelist,failurecode, type, orgid, parent) 

values ( (select failurecodeid from failurecode where failurecode = 'PUMPS' and orgid = 'EAGLENA')  ,'PUMPS', null,'EAGLENA', null);

-- create failure class in failurelist table to display in Failure Code application

PARENT column in the insert DML statement is retrieved using the SELECT subquery. The sql scripts for the entire hierarchy need to be prepared using the CONCAT formula in EXCEL

Create Problem with failure codes  

insert into failurelist (failurelist,failurecode, type, orgid, parent) values (failurelistseq.nextval,'PB01','PROBLEM','EAGLENA', (select failurelist from failurelist where failurecode ='PUMPS' and orgid='EAGLENA'));

PB01 is the problem code ; PUMPS is the failure class under which the Problem-Cause-Remedy hierarchy is going to be formed

Create Cause with failure codes  

insert into failurelist (failurelist,failurecode, type, orgid, parent) values (failurelistseq.nextval,'CA01','CAUSE', 'EAGLENA', (select failurelist from failurelist where type = 'PROBLEM' and failurecode ='PB01' and orgid='EAGLENA'));

PB01 is the problem code already created in step 2 ; CA01 is the cause under PB01 ; PB01 --> CA01

Create Remedy with failure codes 

insert into failurelist (failurelist, failurecode,type, orgid, parent) values (failurelistseq.nextval,'RY01','REMEDY','EAGLENA', (select failurelist from failurelist where failurecode ='CA01' and type = 'CAUSE' and orgid = 'EAGLENA' and parent = (select failurelist from failurelist where orgid = ' EAGLENA' and type = 'PROBLEM' and failurecode = 'PB01'))); 

PB01 is the problem code already created in step 2  ; CA01 is the cause already created in step 3

RY01 is remedy created for PB01 -> CA01 -> RY01

To form the insert scripts, we would use excel =CONCAT formula in the worksheet.

Use sql to verify the data loaded into Maximo

Pros:

Failure Class hierarchy can be loaded using other methods such as MIF enterprise service or MXLoader. 

MXLoader will be slow if the number of records are in thousands. 

If loading error happens at any point - the re-loading starts from the first row and debugging the load failure is difficult.

Conventional method using MIF Object Structure and Enterprise service is faster.

But, we need to prepare the data for parent field with failurelist ID from the database. So, it is time consuming.

If we have a repetitive data load of failure hierarchy for different Organizations within a same company, then creation of failure hierarchy using sql scripts would be easier as it needs only a change of ORGID for loading them.

Cons:

 

We will be bypassing the Maximo Data validation. for example, we can load a problem type of failurelist without an actual failurecode available.

Reference:

associating-failure-classes-records

bportaluri-load-failure-codes-in-maximo