You're welcome to email me at firstname.lastname@example.org.
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.
How To Import Data Into Maximo Through MIF Interface Tables [Example: MATUSETRANS]
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.
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.
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.
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.
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.
So, Let's Get Started
Here are the detailed steps which we are going to take:
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.
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:
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.
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:
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:
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:
In addition, go to the Enterprise Services tab. Add a new row and assign the new row with the following properties:
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:
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:
On the MXIN_INTER_TRANS table:
On the MXIFACE_INVISSUE table:
Check your inbound interface queue table (i.e., MXIN_INTER_TRANS) for the followings:
If you have any questions or comments, feel free to contact me.