Daniel Ng
Email Me Contact Me

You're welcome to email me at dng11@sympatico.ca.

While I do like hearing from you, I am not interested in SEO services nor offshoring my business, so please don't offer me any.

Follow me on Twitter Subscribe to my RSS feed

How To Import Data Into Maximo Through MIF Interface Tables [Example: MATUSETRANS]
Originally posted by Daniel Ng on July 14, 2012.

The Maximo Integration Framework (MIF) is essentially a collection of applications which we can use to set up integration between Maximo and other external information systems. For those who are new to MIF (or the previous incarnation, MEA), it can be confusing to comprehend because there are many parts and components and not to mention many new terminologies. In this tutorial, I will walk you through the process of setting up a basic MIF to import data into Maximo through a set of database tables called interface tables. This hopefully will be enough to get your feet wet.

By the way, in the File Download section, I have included a couple of IBM white papers on MIF. Be sure to check them out as well.

Background Information

Before we begin with the tutorial, I would like to share with you some background information which I deemed important. You are welcome to skip ahead to the tutorial portion if you like.

A Real World Example

In this tutorial, I will be using inventory issuing as an example and here is the context:

The user interface (UI) for issuing inventory in Maximo is not conducive to batch issuing. In fact, it can be downright time-consuming. My users have demanded a more productive UI and one that can work with a barcode reader. For instance, to issue an item from a storeroom, they would simply scan the item using a barcode reader. The transaction would then be recorded into Maximo somehow. In other words, my users wanted something that operates basically like a check-out counter at the grocery store.

To fulfill my user's request, I have developed a custom .NET Windows application with a streamlined UI for doing issuing and has support for barcode reader. Since we are now doing inventory issuing outside of Maximo, the next challenge is to get the transaction data into Maximo. After all, Maximo remains our inventory system and it is important that we keep the inventory level up-to-date. This data exchange, of course, is made possible by MIF.

This custom application is not the focus of this tutorial. But if you are curious about how it works, you can find out more from here.

How Inventory Issue Works Behind The Scene

It is beneficial for you to know how inventory issue works at the database level so that you can verify the results easily once you have the integration setup and running.

There is a Maximo database table named MATUSETRANS that keeps track of all material use transactions. Whenever you issue or return an inventory item via Maximo front-end, a new row is added to this table by the system. So in a sense, what we hope to accomplish with MIF is importing data into this table. To verify whether your integration works and data are correct, simply run a SQL SELECT statement on this table after. From a business-logic standpoint, you should only be adding record to this table and not updating existing record.

One other important note. The MATUSETRANS table has well over 70 columns. You DO NOT need to supply a value for every single columns. You do need to figure out which columns are essential, which are not required, and which will be calculated by the system if left blank. I will share my observation with you in the tutorial portion below.

Integration Components To Build

Figure 1: Integration components to be created. Integration Components

What we ultimately need to create in Maximo is a new External System object (refer to Figure 1). An External System object can be think of as a container that contains other child objects, e.g., a Publish Channel object, an End Point object and an Enterprise Service object. A child object may also be a parent container itself containing other child objects as depicted in Figure 1.

Since we are only concern with importing data into Maximo and not exporting, we will skip creating the Publish Channel object which controls the outbound process exclusively. The Enterprise Service object, on the other hand, governs the inbound process and will be created in this tutorial.

Also in practice, the order of creation must be from inside and out. In other words, you need to build the child object first (e.g., Object Structure) before you can build the parent object (e.g., Enterprise Service). Once you have an End Point object and an Enterprise Service object built, you can then work on the External System object.

One other note. The two End Points as depicted in Figure 1 warrant some explanation because it is slightly confusing. An End Point can either be the source or the destination depending on the context. Furthermore, there are different types of End Point, e.g., XML, flat file, interface tables, and etc. The End Point on the External System level is required and can be used by both the Publish Channel and the Enterprise Service if both the source and the destination are the same. The End Point inside the Publish Channel is optional and is used to overwrite the End Point at the External System level. For example, if we have the End Point at the External System level pointing to interface tables and we wish to output a flat file, we can configure the End Point for the Publish Channel accordingly.


How It Will Work

Here is a preview of how the system will work once we have it set up. Please refer to the following illustration and the explanation ensued.

Figure 2: Overview on how data flow from external application into Maximo. Final Setup
  1. Here we have an external database application writing issued transaction data to a custom database table named INVISSUETRANS. This process is outside the scope of this tutorial, hence it will not be discussed.
  2. An Insert Trigger on the INVISSUETRANS table will monitor for any new addition. If detected, the trigger will write data to two Maximo interface tables, i.e., MXIN_INTER_TRANS and MXIFACE_INVISSUE. The former table will contain an action for the JMS queues and the latter will contain the actual business data.
  3. The IFACETABLECONSUMER cron task in Maximo will monitor the interface queue table (i.e., MXIN_INTER_TRANS) for any new data.
  4. When new data are detected, they will be sent to the JMS queues for further processing.
  5. If error-free, new data will be imported into the Maximo MATUSETRANS table and the process is complete.

So, Let's Get Started

Here are the detailed steps which we are going to take:

  1. Create new Object Structure object.
  2. Create new End Point object.
  3. Create new Enterprise Service object.
  4. Create new External System object.
  5. Create interface tables.
  6. Configure cron tasks.
  7. Write data to interface tables.

Depending on your experience, you can either follow the entire tutorial step by step, or you can skip to the section that requires your attention.


Step 1: Create new Object Structure object.

Tip: Anytime you asked the system to re-create those interface tables, all previous data in those tables will be wiped out. If you want to preserve those data, you can manually modify the structure of the interface tables yourself via the database back-end instead of relying on Maximo to do it for you.
Tip: If you received an "BMXAA4214E - An unknown error has occurred" integration error, check my list of essential fields to make sure you included them all.
Tip: If your Object Structure has no Outbound Definition Class or Inbound Processing Class defined, you need to write your own class to handle the integration.
Tip: During my own testing, I discovered that the ISSUETO field value did not get transferred over to the MATUSETRANS table. I ended up having to extend the Inbound Processing Class (the code is available in the File Download section). You may find yourself having to do the same if you notice your field value is not coming across properly.

Launch the Object Structures application (i.e., Go To > Integration > Object Structures) and look up a record named "MXINVISSUE". Duplicate that record and then assign the new record with the following properties:

  • Object Structure = LCINVISSUE
  • Support Flat Structure? = Yes

Next, you need to define a list of fields which you want this object to have. Later on, Maximo will create an interface table for this object based on the structure you defined here. Recall I mentioned that the MATUSETRANS table has well over 70 columns and not every column needs to be populated with a value. Open the Exclude/Include Fields dialog window (i.e., Select Action > Exclude/Include Fields), carefully exclude fields you do not need and include those you need. All non-persistent fields should probably be excluded. Below is a list of essential fields which you absolutely MUST include for this specific object. Other fields can be included as you see fit.

1) ACTUALDATE 4) ITEMNUM 7) SENDERSYSID or OWNERSYSID
2) GLDEBITACCT 5) MATUSETRANSID 8) SITEID
3) ISSUETYPE 6) QUANTITY 9) STORELOC

Personally, I like to include fields that I really need but this can be very time-consuming and challenging especially if you do not know each field well. If you have doubt about any field, I would suggest that you leave it in. Just bear in mind that Maximo will create an interface table for this object. If you modified the object (e.g., adding/removing field) after the interface tables have been created, you will need to re-create those interface tables. While this can be done easily enough, you may still want to limit the number of times you re-creating those interface tables.

For most of the "optional" fields (e.g., GLCREDITACCT, DESCRIPTION, TRANSDATE, FINANCIALPERIOD, COMMODITYGROUP, ORGID, ENTERBY, CURBAL, UNITCOST, LINECOST, ITEMSETID, etc.), their field value will be auto-determined by the system. You are welcome to include any of them as you see fit. Just remember, if you do include these fields and if you do provide a field value to them, the system will always use your value. The onus is on you then to make sure your value is correct. If not, it is better to let the system calculates it for you. Unfortunately, there is no field guide out there that I know of that will explain this dynamic. It is all through trail and error I am afraid.

Regarding the GLDEBITACCT field, if you issued an item to an asset or a work order, you should probably leave this field blank because the system will determine the proper GL for you. If not, you should definitely supply a value to this field. Otherwise, you will receive the "BMXAA1878E - The charge details for a material issue/return have not been specified" integration error.


Step 2: Create new End Point object.

Tip: For a list of JDBC drivers, visit this site http://www.devx.com/tips/Tip/28818.
Tip: The database user account must have the CREATE TABLE permission in order to create those interface tables. Also, it must have read and write rights to those tables.

Launch the End Points application (i.e., Go To > Integration > End Points) and look up a record named "MXIFACETABLE". Duplicate that record and then assign the new record with the following properties:

  • End Point = LCIMSIFACETABLE
  • Driver = <Visit http://www.devx.com/tips/Tip/28818 for a list of appropriate values>
  • IsRemote = <Enter 1 if you wish interface tables to be created on the external database. Enter 0 if you want them to be in the Maximo database.>
  • Password = <Enter the password of a database user account>
  • URL = <Visit http://www.devx.com/tips/Tip/28818 for a list of appropriate values>
  • Username = <Enter the username of a database user account>

Step 3: Create new Enterprise Service object.

Launch the Enterprise Services application (i.e., Go To > Integration > Enterprise Services). Create a new record and then assign the new record with the following properties:

  • Enterprise Service = LCINVISSUEIN
  • Operation = Sync
  • Object Structure = LCINVISSUE
  • Interface table = MXIFACE_INVISSUE

Step 4: Create new External System object.

Tip: Use sequential queue only when the order of processing is important. Otherwise, sticks with continuous queue. When an error occurred in a sequential queue, the entire queue will be hold up until the error is resolved.

Launch the External Systems application (i.e., Go To > Integration > External Systems). Create a new record and then assign the new record with the following properties:

  • System = LCESInvIssue
  • End Point = LCIMSIFACETABLE
  • Enabled? = Yes
  • Outbound Sequential Queue = jms/maximo/int/queues/sqout
  • Inbound Sequential Queue = jms/maximo/int/queues/sqin
  • Inbound Continuous Queue = jms/maximo/int/queues/cqin

In addition, go to the Enterprise Services tab. Add a new row and assign the new row with the following properties:

  • Enterprise Service = LCINVISSUEIN
  • Enabled? = Yes
  • Use Continuous Queue? = Yes

Step 5: Create interface tables.

Tip: The inbound interface queue table will be actively managed by the system. Once a row has been processed, it will be removed from the table by the system. The interface table for the Object Structure, on the other hand, will continue to expand as you add more rows to it. You will likely want to purge the content of this table periodically.

Retrieve the new External System object you created in step 4. Open the Create Interface Tables dialog window (i.e., Select Action > Create Interface Tables). You should see one interface table listed on the screen. Select the table and then click on the Create button to create interface tables. It should take a few seconds to process your request.

The system will create three interface tables for you, i.e., one for inbound queue, one for outbound queue and one for the Object Structure. The first two interface queue tables have pre-defined name (i.e., MXIN_INTER_TRANS and MXOUT_INTER_TRANS respectively) and pre-defined fields. The third one will be named according to the name you specified in step 3 and it will have all the fields you included for your Object Structure plus a few more. The outbound queue interface table (i.e., MXOUT_INTER_TRANS) will not actually be used in this tutorial.


Step 6: Configure cron tasks.

Tip: Every time you made changes to the cron task, you need to make sure you reload the request, i.e., click on Select Action > Reload Request.

Launch the Cron Task Setup application (i.e., Go To > System Configuration > Platform Configuration > Cron Task Setup) and look up a record named "JMSQSEQCONSUMER". Make sure the two cron task instances are active.

Next, retrieve a record named "IFACETABLECONSUMER". Create a new cron task instance and assign the new instance with the following properties:

  • Cron Task Instance Name = IFaceIMS
  • Schedule = <As you see fit>
  • Active? = Yes
  • END POINT = LCIMSIFACETABLE
  • QUEUETABLE = mxin_inter_trans

Step 7: Write data to interface tables.

At this stage, everything on the Maximo side has been set up and the integration should be running as we speak. What is left for us to do is to create some kind of routine to send data to the interface tables. Data sent to the interface tables will (and should) automatically be picked up by the cron tasks according to the schedule you set.

In this tutorial, I will be using an Insert Trigger to write data to the interface tables. I have included a SQL script to create the trigger in the File Download section. This script is meant for your reference only. You will not be able to use it straight out without modifying it first to fit your environment. You do not have to use a database trigger of course. You are welcome to use any method as you see fit.

Just keep this in mind when populating the interface tables:
For every item issued, a new row needs to be added to MXIN_INTER_TRANS table and also to the MXIFACE_INVISSUE table. Here are the field values which you should set for the new row:

On the MXIN_INTER_TRANS table:

Field Value Additional Comment
ACTION Add Must be "Add". "AddChange" is not supported.
IFACENAME LCINVISSUEIN i.e., the name of the Enterprise Service object.
IMPORTMESSAGE NULL Do not set.
EXTSYSNAME LCESInvIssue i.e, the name of the External System object.
TRANSLANGUAGE NULL Do not set.
TRANSID <Any unique numeric ID> Must be a unique numeric record ID.

On the MXIFACE_INVISSUE table:
Note: You may have different fields in your table than mine depending on how you set up your Object Structure. Improvise if that is the case.

Field Value Additional Comment
ACTUALDATE <Actual datetime value>
GLDEBITACCT <Any valid GL account> The GL account must exist in Maximo
ISSUETO <Any person ID> or NULL The Person ID must exist in Maximo
ISSUETYPE ISSUE Must be "ISSUE".
ITEMNUM <Any item code> The item code must exist in Maximo.
QUANTITY <Actual issue quantity> Any positive value.
SITEID <Any site ID> The Site ID must exist in Maximo.
SENDERSYSID LCESInvIssue i.e., the name of the External System object.
STORELOC <Any storeroom name> The storeroom name must exist in Maximo.
TRANSID <Any unique numeric ID> Must use the same record ID as defined in the MXIN_INTER_TRANS table.
TRANSSEQ 1

Basic Troubleshooting

Check your inbound interface queue table (i.e., MXIN_INTER_TRANS) for the followings:

  • If your record is still there and there is no message under the IMPORTMESSAGE field, check your External System object and your Enterprise Service object to make sure they are enabled. Also, check the schedule for your IFACETABLECONSUMER cron task to see how often it is set to run.
  • If your record is still there and there is a message under the IMPORTMESSAGE field, read what the message is and then fix it accordingly.
  • If your record is no longer there, you will need to launch the Message Reprocessing application to find out what the error is and resolve it from there.

Final Notes

If you have any questions or comments, feel free to contact me.

Copyright © 2013 www.xcentricway.ca. All rights reserved.