How to import Failure Codes from Excel into Maximo

This article is outdated! Checkout MxLoader tool.

This entry is part of the Maximo Integration Framework series.

Creating and maintaining the hierarchy of failure codes can be quite complex using the standard Failure Codes application. Unfortunately there is no common solution to import Maximo failure codes. This evening I have worked on creating an Excel spreadsheet that can help in such cases.

This is an experimental tool. Use with care on development environments.
Leave a comment on this page both if you have problem (I'll try to fix them) and if you are able to use it successfully in your environment.


Data Model

Before going on, we need to understand how Maximo stores failure codes and their hierarchy of problems, causes and remedies.
The FAILURELIST table stores the hierarchy of problems, causes and remedies. The PARENT field holds the parent-child relationship while the FAILURECODE field points to another table called FAILURECODE that stores the descriptions of the failure codes in the various languages.
It is important to understand that a specific failure code can be attached to several places of the failure list hierarchy. This makes quite complex to manage this structure.


Export Failure List

A simple way of exporting failure codes and list is through SQL queries.

  
select orgid, failurecode, description, langcode from failurecode;


select f.orgid, f.failurelist classid, f.failurecode class, p.failurelist problemid, p.failurecode problem, c.failurelist causeid, c.failurecode cause, r.failurelist remedyid, r.failurecode remedy
from failurelist f
left outer join failurelist p on p.parent=f.failurelist and p.type='PROBLEM'
left outer join failurelist c on c.parent=p.failurelist and c.type='CAUSE'
left outer join failurelist r on r.parent=c.failurelist and r.type='REMEDY'
where f.parent is null
order by f.failurecode, p.failurecode, c.failurecode, r.failurecode;


Import

Create the following Object Structures.
Object Structure: FLL-FAILURECODE
Object Structure: FLL-FAILURELIST

Now download the FailureCodesLoader Excel spreadsheet and open it. Go on the Config sheet and set the correct hostname of your Maximo server. Clicking of the link the browser will open and should display the following message: Servlet is running. Please use HTTP POST to post data.
Now you are ready.

The Failure Codes and Failure List sheets allow to upload data into the two tables described before.
The Failure Codes sheet is straightforward to use. Just fill the worksheet with your failure codes descriptions and click on the little arrow on the toolbar.


This will execute a VBA macro that will call the FLL-FAILURECODE Object Service through an HTTP POST request.

The Failure List sheets represents the the failure codes hierarchy in a flat table with the same structure as exported by the above SQL query.
The Excel macro will handle the necessary logic to manage the failure list hierarchy. Once the objects are created and the hierarchy is populated, the macro will fill the 'ID' columns with the correct values. Once the ID values (greyed cells) are filled, the failure node is created in the hierarchy and shouldn't be changed.

At first it may seem a little tricky at the beginning, but after having played a little bit with the tool, it will be easy to create a complete failure list hierarchy of 1000 codes and more.

Labels: , ,