How to load failure code hierarchy using MIF

Recently on one of my projects I had a requirement to load failure codes and create failure code hierarchies. To achieve this I decided to use MIF (I know there are other options beside MIF to load this but I decided to go with MIF). Below are steps that I did to load failure codes:

  • Configure Maximo to use interface tables to load failure code:

First step is to create object structure, so Go To> Integration> Object Structures and create new object structure :

Name: ADFAILURELIST

Description : Custom OS to load failure codes and create hierarchy

Consumed By: INTEGRATION

Support Flat Structure: true

This object structure shoul have two objects associated to it:

First Object (parent):

Object: FAILURELIST

Parent object: null

Second object (child):

Object: FAILURECODE

Parent Object:FAILURELIST

Relationship: FAILURECODE

When you create this object structure you can go and just get rid of unnecessary fields by Select Action > Exclude/Include Fields and for FAILURECODE object exclude HASLD and LANGCODE fields. Also you will need to change alias for this object structure, so Select Action > Add/modify Alias opens up dialog where for object FAILURECODE  and attribute FAILURECODE  you need to set alias FCFAILURECODE.

After object structure is defined, we are ready to create enterprise service:

Enterprise Service: ADFAILURECODE

Description: Ent. Service to import failure codes and build hierarchy

Object Structure: ADFAILURECODE

Interface Table: ADFAILURECODE

Now when this enterprise service is ready, we can go and crate new external system with interface table  endpoint  or use existing one. Lets assume we created new external system ADLOADFC, assigned to it MXIFACETABLE interface table endpoint and added to this external system our new enterprise service ADFAILURECODE. What I like to do is to create new instance of IFACETABLECONSUMER cron task so that I have always separate cron task that will have setup parameters for external system, enterprise service and endpoint so it imports only specific records like in this case failure codes.

  • Prepare data for load:

We have now MIF part configured on Maximo side that is ready to load data. What we require now is to prepare some data and import it into interface table. To do this you will have to create table as:

create table ADFAILURECODELOAD( 
   FAILURECODE VARCHAR2 (20 BYTE), 
   DESCRIPTION VARCHAR2 (256), 
   TYPE VARCHAR2 (BYTE 20), 
   FAILURECLASS VARCHAR2 (BYTE 20), 
   PROBLEM VARCHAR2(BYTE 20), 
   CAUSE VARCHAR2(BYTE 20), 
   PARENT NUMBER)

This table will be used to prepare data before it gets sent to interface table. Now let’s see how this data should look like in order to get imported. As we all know failure code hierarchy is relationship is tree structure where for root we have failure class, than for each class we have a problem related in second level of a tree, then on third level related to problems we have causes and for those cases we have related remedy records as leafs on forth level. This tree structure for each failure class in Maximo is represented in FAILURELIST table, each record in that table has a reference to parent record and also a type field that defines if record in tree is problem or cause or remedy, only failure classes (root elements in a tree) have null value for parent and type fields in this table.

Lets see an example:

Failure Class:

STWHEEL – Steering Wheel

Problems for Failure class STWHEEL:

VIBRATE – Vibration

STIFF – Stiff

Causes for STIFF failure

LEAKAGE – leakage of steerring fluid

DAMAGE – damaged mechanizm

Remedy for LEAKAGE cause

ADDFLUID – Add steering fluid

REPLACE- replace

Remedy for DAMAGE:

REPLACE – replace

Causes for VIBRATE problem:

TIREUNBAL- Tire inbalanced

BADBREAKS – Bad breaks

Remedy for TIREUNBAL cause

BALANCE – Balance tires

Remedy for BADBREAKS cause

REPLACE – replace

Importing a structure like this is done in steps, but we can prepare excel for this first and then use that excel to import into temp table ADFAILURECODELOAD. Excel should look like this:

failurecode-ex

  • Configure data into Maximo:

Now this file can be imported into ADFAILURECODELOAD table. We are now ready to start loading into interface table. First we need to load into Maximo failure classes since those are roots of failure code hierarchy. We shall use SQL script below:

create sequence importseq minvalue 1 maxvalue 999999999999999999 increment by 1 start by 1 with 1 cache 20 noorder nocycle;
insert into ADFAILURECODE (failurecode, orgid, parent, type, description, fcfailurecode, transid, transseq) select failurecode, 'YOURORGID', null, null,description, failurecode, importseq.nextval, 1 from ADFAILURECODELOAD where type is null;
commit;
insert into MX_INTER_TRANS(action, ifacename, extsysname, transid) select 'Add', 'ADFAILURECODE','ADLOADFC', transid from ADFAILURECODE
commit;

So let’s see what this script does, first statement is just to create sequence that we shall use for inserting transaction id our interface table, Second statement in loading our interface table, third is commit, forth is inserting into MX_INTER_TRANS table (or however you have named this table in your endpoint) so that Maximo can pickup record from interface table and process them. Final statement is just a commit.

After this script you should check and see that Maximo imported all failure codes that represent our failure classes (roots of failure code hierarchies). When everything is imported we need now to load second level of hierarchy – problems. Before doing anything I would advise to delete ADFAILURECODE table. So to import problems we shall run following script first:

update ADFAILURECODELOAD fcl set parent = (select fc.failurelist from (select failurelist, parent, failurecode, level, connect_by_root failurelist, LTRIM(SYS_CONNECT_BY_PATH(failurecode,'|'),'|') as path from FAILURELIST)
start with parent is null
connect by parent prior failurelist order sibilings by failurecode) fc where fcl.path = RTRIM(fc.failureclass||'|'||fc.prolem||'|'||fc.cause||'|'||fc.remedy) and fc.type='PROBLEM';
commit;

This a bit complex query will match by path hierarchy from Maximo table FAILURELIST and out temporary table ADFAILURECODELOAD and update each problem record in ADFAILURECODELOAD with proper parent id. Now we can run script:

insert into ADFAILURECODE (failurecode, orgid, parent, type, description, fcfailurecode, transid, transseq) select failurecode, 'YOURORGID', parent, type,description, failurecode, importseq.nextval, 1 from ADFAILURECODELOAD where type='PROBLEM';
commit;
insert into MX_INTER_TRANS(action, ifacename, extsysname, transid) select 'Add', 'ADFAILURECODE','ADLOADFC', transid from ADFAILURECODE where type = 'PROBLEM'
commit;

This query will load new batch of failure codes and place them as problems in our hierarchy. Now to load causes we do same thing as in previous step just conditions at the end of statements are  restricting to type CAUSE

update ADFAILURECODELOAD fcl set parent = (select fc.failurelist from (select failurelist, parent, failurecode, level, connect_by_root failurelist, LTRIM(SYS_CONNECT_BY_PATH(failurecode,'|'),'|') as path from FAILURELIST)
start with parent is null
connect by parent prior failurelist order sibilings by failurecode) fc where fcl.path = RTRIM(fc.failureclass||'|'||fc.prolem||'|'||fc.cause||'|'||fc.remedy) and fc.type='CAUSE';
commit;
insert into ADFAILURECODE (failurecode, orgid, parent, type, description, fcfailurecode, transid, transseq) select failurecode, 'YOURORGID', parent, type,description, failurecode, importseq.nextval, 1 from ADFAILURECODELOAD where type='CAUSE';
commit;
insert into MX_INTER_TRANS(action, ifacename, extsysname, transid) select 'Add', 'ADFAILURECODE','ADLOADFC', transid from ADFAILURECODE where type = 'CAUSE'
commit;

As expected, Maximo will import these causes and place them in hierachy. With same logic finally we are going to load remedies:

update ADFAILURECODELOAD fcl set parent = (select fc.failurelist from (select failurelist, parent, failurecode, level, connect_by_root failurelist, LTRIM(SYS_CONNECT_BY_PATH(failurecode,'|'),'|') as path from FAILURELIST)
start with parent is null
connect by parent prior failurelist order sibilings by failurecode) fc where fcl.path = RTRIM(fc.failureclass||'|'||fc.prolem||'|'||fc.cause||'|'||fc.remedy) and fc.type='REMEDY';
commit;
insert into ADFAILURECODE (failurecode, orgid, parent, type, description, fcfailurecode, transid, transseq) select failurecode, 'YOURORGID', parent, type,description, failurecode, importseq.nextval, 1 from ADFAILURECODELOAD where type='REMEDY';
commit;
insert into MX_INTER_TRANS(action, ifacename, extsysname, transid) select 'Add', 'ADFAILURECODE','ADLOADFC', transid from ADFAILURECODE where type = 'REMEDY'
commit;

After Maximo processes these last records from our interface tables we can check in Maximo and see our hierarchy is there. Now during load you may experience that you get error that certain failure code already exists in hierarchy. This is caused in case you have failure code used as problem, cause or remedy in your hierarchy that represents failure class in another hierarchy. This unfortunatelly cannot be solved using MIF and for such cases you will have to go and manually insert using Maximo Failure code application into your hierarchy and exclude that record from interface table.

I hope this article is useful in case you have a huge number of failure code hierarchies. In next article I shall show you how to load hierarchies using Java code and RMI that Maximo supports with it’s java classes, this option will save you from error message when some failure code already exits.

 

Posted in IBM Maximo Tagged with: , ,
4 comments on “How to load failure code hierarchy using MIF
  1. Bruno Portaluri says:

    MxLoader can do this much more easily.

    • Dorde Popovic says:

      I guess it is easier, but I just wanted to show alternative option. Anyhow, I believe most complex part is to prepare data for import 🙂

    • Sun says:

      Yes, I rather using an API than write directly to the database. I’ve gotten burned too many times with direct database manipulation.

      • Dorde Popovic says:

        Well in my article I am not writing data into Maximo tables directly but rather into interface table and then leave mif to process it. You got probably burned updating directly maximo tables.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe for Newsletter

Categories