2way / 3 way / 4 way matching in Maximo

I have come across queries related to invoice matching in many occasions. What type of matching does Maximo support? Is it a three way matching or four way matching? For some of us who are not familiar with accounting, these questions lead you to a black hole.

The three way match refers to a procedure followed to avoid paying incorrect and fraudulent invoices and is used when processing an invoice received from a vendor or supplier. An invoice that is received from a supplier will be approved and paid by an organization if the purchase order and receiving report prepared by the organization are in compliance. That is in the world of maximo, we need to concentrate on the Invoice(s), Purchase Order(s) and Receipt(s) for a three way match. And the match refers to comparison of quatities, unit prices, total cost, etc. presented in the invoice record with the information on the PO and PO Line Items in the Purchase Orders application and the quantities that are actually received in the Receiving application against each PO line item. So the comparison points are:

1. Quantity billed Vs. quantity ordered
2. Invoice cost Vs. PO cost
3. Quantity billed Vs. quantity received

After the invoice has been validated by this three way match, it can be paid by taking into consideration tolerances if any. 

The four way matching refers to a process when an invoice is matched with the corresponding purchase order for quantity and amount, receiving and, inspection details. In Maximo Purchase Orders application, we can create a purchase order for a material and mark both the checkboxes Inspection Required?  and Receipt Requied? against it. 

Alternatively, in the Item Master application, you can keep the checkbox Inspect on Receipt? checked for an item. When you select that item for a line in the PO, the Inspection Required? checkbox is marked automatically for this PO Line item.

So when you go to the Receiving application to generate receipt of the material and select the ordered item(s), you can check that material with transaction type RECEIPT is in status WINSP (Waiting for inspection). 

Now you need to change the inspection status where you are presented to enter accepted and rejected quantity. 

After changing the inspection status, you can check one more row for the material with transaction type TRANSFER and status COMP. You can also see that the material has now come from the holding location to the storeroom location of the site.

In this case, we see a four way matching as invoices are matched with purchase order, receiving information and inspection information. The comparison points in this type of matching are:

1. Quantity billed Vs. quantity ordered
2. Invoice cost Vs. PO cost
3. Quantity billed Vs. qunatity received
4. Quantity billed Vs. quantity accepted

Now there is another question, if I create a puchase order with a line item for which the checkbox for Receipt Requied? is not checked. In that case maximo allows a two-way matching as invoices are matched with the purchase order only. The comparison points in this type of matching are:

1. Quantity billed Vs. quantity ordered
2. Invoce cost Vs. PO cost. 

So to answer the query about procurement and invoicing in Maximo, it supports two-way, three-way and four-way matching for invoices.

Checklist for Flat File Imports

We are familiar with flat file imports using Maximo Integration Framework and it seems any layman will work it out. But to err is human so we tend to forget (intentioanally or unintentionally) some very familiar and simple steps that would actually make us save time banging our heads when we face encounter errors.

So, here is a checklist to consider while we are creating object structure, enterprise service and external system for flat file import through MIF.

We all are aware that for flat file upload first step is to create an object strucure. We need to remember that we need to select INTEGRATION in consumed by field and we need to select the checkbox of Support Flat Structure? We move on to select the main object, other objects (if any) with selecting the parent object as the main object and selecting the required relationship. We save the object structure. We need to follow the below steps:

1. Select Action > Exclude/Include Fields - this is required to select the fields from main object and all child objects which will be kept in the flat file. 
   
   
   
   
2. Select Action > Add/Modify Alias - this step might be required if there are same attribute names in main object and child object(s). In this case, after saving the object structure the Alias Conflict? checkbox will be marked automatically. After renaming the duplicates, this checkbox will be cleared. 
   
   
   
   
3. Select Action > Inbound Setting Restrictions - this steps is required if we want MIF to use the autokey functionality for some field. If it is not restricted in the object structure MIF expects data in the flat file. For example, we are loading asset data and required attribute assetnum has a autokey defined in its object. We keep the values for this column blank in the flat file but while importing the flat file, we get an error  "BMXAA4195E - Requred field XXX is blank". To avoid getting this error this step is mandatory and we tend to forget this step frequently.
   

   

   
   

   
   
   

We move on to create the Enterprise Service for our inbound transaction. We associate out object structure, select adapter as MAXIMO and operation as SYNC.

Next step is to create an external system for which end point should be MXFLATFILE. Outbound sequenetial queue, inbound sequential queue, and inbound continuous queue should be entered and Enabled? checkbox should be selected. Then we go to the Enterprise Services tab and associate the enterprise service createt and uncheck the checkbox of Use Continuous Queue?

Next step is to get ready with our flat file. Flat file should be with extension .dat and first row in the file should consist of External System Name, enterprise service name, action (Add/AddChange/Change/Delete/Replace/null), and language. Second row consists of all the column names specified in Exclude/Include Fields window of the Object Structure. Third row onwards consist of the data to be imported. Ensure to remove extra commas from the flat file.

Now from the Enterprise Services tab in the External Systems application, select the enterprise service for which you want to import the flat file and click on Data Import button.Select the radi box for Flat File and select the checkbox Import Preview? Choose the flat file and click OK.

If you get a message "BMXAA5621I - The data import preview mode is successful", then again click on Data Import button. Select Flat File radio button and this time do not select the Import Preview? checkbox. Choose the same flat file and click OK. Your data should be imported successfully.

PS: For better error tracking and management, you should also enable message tracking in the Enterprise Service. From the select action menu, choose option Message Tracking and in the Message Tracking Setting dialog window, check the Enable Message Tracking? checkbox.

Reversed Invoice and cost adjustments to line item

I would like to explain about one scenario where a PO gets closed when the invoice goes to paid status, but then there is a requirement to make changes in the PO Line item cost and invoice needs to be changed accordingly.

The checkbox to close the PO was checked on changing the invoice status to PAID in the maximo Invoice application. So, the status of the PO gets changed from APPR to CLOSE when invoice status is changed to PAID. Now there we came across a situation where we created a PO with one PO line item with incorrect tax code but we realized the error when the invoice for this PO was already paid. We did not want to revise the PO and go through the approval process again. So here is a solution to reverse the invoice and make changes to the PO Line item or Invoice Line item as described below.

The first step would involve the reversal of the paid invoice. As discussed in my previous blog, from the paid invoice record, you need to choose the option Reverse Invoice from the select action menu and select autonumber to create a new invoice in pending reversal status, with reversal reason, G/L posting date, etc. A new invoice is created with status as PENDREV and negative entry in the cost fields and with a reference of the original invoice number. When you change the status of the new invoice to APPR, the status of the old invoice gets changed from PAID to REVERSED.

The second step would be to create a new invoice with the same PO number and site details. You need to go to the Invoice Lines tab, and you can check the uninvoiced quatity for the line item when you select the Copy Lines button. Select the line item accordingly and make the necessary changes to the tax code.

You can verify that in the Invoice tab that invoice total and total base cost has been changed accordingly. You can also verify that total cost of the PO is not changed and it is as per the original invoice.

Now the process of changing Invoice status to approved and then to paid remains the same. This way you are saved of PO revision and approval process and changes or adjustments can be made at the Invoice application only as per your requirements.

Query Based Reports and Cardinality

I would like to draw your attention towards a very simple yet important fact about query based reports which we create on the fly in Maximo. All of us are aware that running an adhoc report from any Maximo appication, requires an Report Object Structure and user will only see the ROS he/she has access to. 

I came across an error once while running one adhoc report from Incident application which actually prompted me to look towards this end. My clients asked me to show one report to list out all the closed incidents with the cause and resolution fields. So, I simply opened the Incidents application, selected all the closed incidents and clicked on Create Report button and selected Summary report in Style tab. In the Select tab of Query Based Report dialog window. I select the required fields from main INCIDENT object, long description text (LDTEXT) from FR1CODE object labeling it as Cause and also selected  long description text (LDTEXT) from FR2CODE object labeling it as Resolution. I was shown a warning message as below:

"BMXAA7325W: The fields you have selected contain multiple relationships, therefore the report style will be changed to a Detail format to support these fields. Any defined groups will be cleared. Click OK to continue, or click Cancel to cancel the field selection."

The list report that got displayed had Resolution fields in separate section below the Incident record(s) fields. As per QBR and Reporting in Maximo, during the report creation process, the category relations are evaluated to determine if there are single or multiple relationships with the main object. Any number of categories with Single Relationships can be added to the QBR. However, when categories have Multiple Relationships, different business rules are invoked. If the user has selected a Summary report, only 1 Category with a Multiple Cardinality can be included. If the user tries to select fields from multiple categories with 1:N relationships, he will be given the choice to either change his style type to Detail, or to remove the field selection. 

I had to go check the object structure for this one. There was the INCIDENT object as parent object. FR1CODE and FR2CODE were list as child object(s) to INCIDENT object. I could see that for both these objects, parent object was INCIDENT, object order was 1 and relationship(s) were also correct, but the cardinality was 'UNDEFINED'. The cardinality value usually defaults to UNDEFINED, which assumes a multiple relationship (one to many or 1:N). Always try to select the relationship type that is applicable. In this case it should have been SINGLE. 

I hope this helps someone.

Invoice Reversal in MAXIMO 7.5

I would like to draw your attention towards a functionality of Reversing Invoices in Maximo 7.5. There may be some requirement when you need to cancel an approved/paid invoice to make corrections due to say typos in the original invoice. I had been asked by my client to define a process where they had faced a similar issue and they did not want to go through the whole process of returning transaction. There is an out of the box option available in the Select Action menu in the Invoice application which allows a user to reverse an already approved/paid invoice.

In the original invoice, you can select the option of Reverse Invoice from the select action menu. You will be presented with two statuses – PENDREV and APPR. If you select Pending Reversed status, a new reverse invoice will be created where the original invoice number will be stored in the ORIGINVOICENUM attribute and the new reverse invoice number will be stored in the original invoice’s REVINVOICENUM attribute. All the invoice information, invoice lines and invoice line costs will be duplicated from the original invoice to the new invoice.

If you select APPR status while reversing an original invoice, Maximo Invoice application will insert all the related records that were created by the original invoice/credit/debit memo by setting opposite values in the new invoice. The new Reverse Invoice status will be set as APPR and the original invoice status will be set as REVERSED. The new inserted related records will follow the same business rules as the original invoice and any variances previously created will be reversed and appropriate costs will be updated in

-          INVOICEMATCH

-          INVOICETRANS

-          MATRECTRANS

-          MATUSETRANS

-          SERVRECTRANS

-          INVENTORY.AVERAGECOST

-          INVENTORY.LASTCOST

-          INVVENDOR.LASTCOST

In the Invoice Application, from the select action menu choose option Reverse Invoice. You are required to enter a Reversal Reason and G/L Posting Date.

A new invoice gets created with negative entry and reference to original invoice number.

On changing the status of the new invoice from PENDREV to APPR, the status of the original invoice changes to REVERSED.

References-

http://www-01.ibm.com/support/docview.wss?uid=swg21683870

https://www.ibm.com/developerworks/community/blogs/a9ba1efe-b731-4317-9724-a181d6155e3a/entry/reverse_invoice10?lang=en

How complicated configdb can be?

Everyone would ask me how complicated a configdb can be for a maximo application. Yes it is very simple. You turn the admin mode on from database configurations application and then apply configuration changes and turn off the admin mode. I understand that you can even run configdb.bat or configdb.sh from command prompt, but what if you run into errors here.

I know some of you would think I have gone crazy. Yes, I had gone crazy scratching my head when I ran into error(s) while running configdb. I was working for one organization then where the team used to think if you can't figure out what to do next, run a complete database import. It was termed as classic jugaad (or jugaar which means workaround) of all times. But because of this, as a maximo user/developer I faced various issues.

We were deploying or moving some changes into production where we had only 1-2 hours of downtime and we had move a lot of changes to existing objects in Maximo so we had to apply the configuration changes and keep production system ready during that time window. From the command prompt, we ran the command configdb.bat and a series of errors we encountered.

Below are a few examples of errors we ran into and their resolution steps:

1. reorg_pending error in alter table - access the DB server and run the command                           db2 reorg table <tablename>
2. existing tables were not restored from backup - access the maximo database instance and run the SQL queries 
• select restoredata from maxtable where tablename=<tablename>
• select restoredata from maxtablecfg where tablename=<tablename>
• If any of the above query returns 1, we need to update this value to 0 in order for configdb to run successful.

PS: We were using DB2 database.

All about that Internal 500 error

We amateurs have faced many a times this cumbersome Internal 500 error while accessing maximo URL. Well, I have faced it many times. So I would like to share my experience and hopefully somebody might get some help while troubleshooting.

When you try to open the maximo url and it is really frustrating to see the Internal 500 error. We all know that this is definitely related to database connection. Don't lose your head and check the following:

1. Database password might have expired. Try to connect to the database instance with your credentials, if it says password expired, reset the password (with DBA's help if needed) and restart your server.
2. In case above step does not help you, check if configdb is running or admin mode is ON.
• Run the SQL query "select varvalue from maxvars where varname='CONFIGURING'", if it returns 1, update varvalue to 0.
• Run the SQL query "select vavalue from maxvars where varname='ADMINRESTART'", if it returns ON, update varvalue to OFF.

I know most of us have already figured out the above obvious checks by now. But I had run into 500 error beyond this too. The maximo.ear file was rebuilt and redeployed. After the server was restarted, we were ready to see the wonders our Java code has created but instead we were shown an Internal 500 error. We double and triple checked for password expiry, running configdb or admin mode ON, but could not track our culprit. Here the problem was encrypted password.

So the resolution steps followed in this case are:

1. From the folder path [IBMSMP]\maximo\applications\maximo\properties, open the file maximo.properties. Modify the file with mxe.db.password=<database password> and set mxe.encrypted=false.
2. Run the command encryptproperties.bat from [IBMSMP]\maximo\tools\maximo path.
3. Rebuild the maximo.ear file and redeploy it. Restart the server.

Hopefully, you will be rid of Internal 500 error by now.