MAS DB2 wont support Oracle Supported SQL - DB2_COMPATIBILITY_VECTOR registry to ORA

MAS DB2 won't support Oracle Compatible SQL commands due to security concerns. 

Database parameter DB2_COMPATIBILITY_VECTOR registry won't be set to ORA by the Support team, which was supported in SaaS Flex Maximo 7.6 version.

As part of Upgrade, application development team need to re-write queries in Maximo BIRT reports and relationships. 

Following are some of the queries that can be referred for conversion:

• rownum vs fetch first n rows only

SaaS Flex DB2: siteid=:siteid and ponum = :ponum and rownum=1

MAS DB2: siteid=:siteid and ponum = :ponum FETCH FIRST 1 ROWS ONLY

• CONNECT BY PRIOR vs WITH Clause

Classification Hierarchy Path generation from Maximo. Both the queries work in Out of Box Maximo

SaaS DB2 with Oracle Compatibility

SELECT

    level, classstructureid, parent, classificationid,

    sys_connect_by_path(classificationid, '**') AS path

FROM   maximo.classstructure

START WITH   parent IS NULL

CONNECT BY

    PRIOR classstructureid = parent

ORDER BY

    level, parent, classstructureid

MAS DB2 recursive sql using WITH clause

WITH classhier  ( level, classstructureid, parent, classificationid, path )

 AS (

    SELECT

        1, classstructureid, parent,  classificationid,

        '' || classificationid

    FROM  maximo.classstructure

    WHERE  parent IS NULL

    UNION ALL

    SELECT

        level + 1, c1.classstructureid, c1.parent,  c1.classificationid,

        classhier.path  || '\'  || c1.classificationid

    FROM  maximo.classstructure c1, classhier

    WHERE classhier.classstructureid = c1.parent  AND c1.parent IS NOT NULL

)

SELECT * FROM  classhier

Reference: db2-compatibility-vector-registry-variable

MAS Manage 8.x Supports Barcode fonts in BIRT report

In MAS, IBM Product team supports bar code fonts in BIRT.

Currently, there are 3 fonts that are validated in MAS: Free3Of9 Extended, IDAutomation and 3 of 9 Barcode.

In order for bar codes to work, we need to install them in Maximo server and client machine. 

Bar Code fonts in developer (or Client Machine) 

A bar code is a type of font and it needs same steps for installation in a system. Download the font, open it and click on Install button.

Enabling bar code font on MAS 

There are 2 files fontsConfig.xml and fontsConfig_pdf.xml in the BIRT jar file org.eclipse.birt.report.engine.fonts_4.3.1.v201308301349 located in Maximo installed path IBM/SMP/maximo/applications/maximo/maximouiweb/webmodule/WEB-INF/birt/platform/plugins

Rename the extension of .jar to .zip and view the list of font configuration files. 

fontsConfig.xml - add highlighted line 

fontsConfig_pdf.xml - add highlighted line 

After modifying the .xml files, update the files in .zip file, then rename it to .jar

Customization zip file needs to be prepared for MAS pod. It will be applied on every deployment of the Maximo application.

Create a path "\applications\maximo\maximouiweb\webmodule\WEB-INF\birt\platform\plugins" -> Copy the updated .jar file in this path -> create a customization.zip with this path (don't have any blank or special characters for zip name). 

Deployment of the customization zip file and creation of volume object for .ttf font file will be done by the infrastructure support team. 

If Maximo reports don't display the barcode, please raise a case to IBM Product Support team.

You might face issues on viewing the barcodes in Chrome and Edge browsers. It's known MAS 8.x limitation. The barcodes can be tested by exporting the report outside from these browsers in pdf format or direct print option in Maximo.

Sample report: inventory_barcode_mas.rptdesign

Reference: Bar Code font for MAS BIRT

Maximo Manage MAS HTTP End Point with OAuth Configuration

Maximo Manage supports OAuth 2.0 client credentials grant type where we send client ID and secret ID to an OAuth provider URL for authentication and receive an access token.

OAuth authenticated service API can be accessed from Maximo Manage for End Points HTTP handler and WEBSERVICE-JAX-WS handler.

Steps to create OAuth enabled End point

1. Most of OAuth using TLS or SSL handshakes, so we must upload the Manage trust store with the certificates from the OAuth provider 

2. Access to object Structure MXAPIOAUTHCLIENT should be given to the security group.

3. Configure the OAUTH client properties in the End Point applications -> "Add/Modify OAuth Clients" Action

Sample values for reference

4. Check the table MAXOAUTHCLIENT to confirm whether the token is generated correctly. 

The OAuth provider specifies an expiration interval for the access token. After expiration, a new token is generated when a new authentication request occurs. 

select accesstoken, granttype, clientid, * from maximo.maxoauthclient

5. Use the OAuth Client parameter in the HTTP Handler End point to use this authentication mechanism.

6. To test the HTTP end point with oauth, create an automation script with invokeEndPoint function to get the response. code oauthhttpendpointmas.py

from java.util import HashMap
from com.ibm.json.java import JSON
from psdi.iface.router import HTTPHandler
from com.ibm.json.java import JSONObject

metaData = HashMap()
headers = HashMap()
metaData.put(HTTPHandler.HTTP_HEADERPROPS, headers)
urlProps = HashMap()
urlProps.put("limit","5")
urlProps.put("offset","0")
metaData.put(HTTPHandler.HTTPGET_URLPROPS, urlProps)

response = service.invokeEndpoint("OAUTH",metaData,'')

obj = JSON.parse(response)

 

Reference: 

https://www.ibm.com/docs/en/maximo-manage/continuous-delivery?topic=handlers-configuring-oauth-access

Maximo 7.6.x HTTP end point with OAUTH 2.0 Authentication

OAuth 2.0 (Open Authorization) is standard to provide consented access and restricts actions of what a client application can perform on resources, hosted by other applications, on behalf of the user, without sharing the user's credentials.

OAuth 2.0 has different grant types to address different scenarios and they are the set of steps a client has to perform to get resource access authorization.

In this article, we will see client credentials grant type which is used for non-interactive applications e.g., automated processes, microservices, IoT etc. 

Prerequisites:

• If the Oauth APIs are https, we need to upload the certificates in the Web Sphere server (or) whitelist the Maximo server IP by receiving End point to avoid SSL Handshake error
• OAuth 2.0 is supported only from Maximo 7.6.1.3 and MAS. For lower versions of Maximo, we need to customize the End point to make calls to OAuth enabled resources

Maximo Components:

• Common library script to retrieve token
• A HTTP End point with basic configuration (URL + HTTP_METHOD)
• A calling script to get token from library script, pass on token, URL parameter and header parameter to End point and store the response for more processing 

A common library script to retrieve token from URL is written by a script without any launch point. 

The variables defined in the statements left hand side are taken as input and those on the right side are output ones code common_lib_gettoken.py 

    

Create a End Point as HTTP Handler with basic information as URL and HTTPMETHOD.  

The calling script of any launch point passes the required parameters to library script to get the token.

This token is used as the header parameter "Authorization". The token value is concatenated with String "Bearer".

Header params and URL properties (or query parameters) are defined as HashMap. 

metaData.put(HTTPHandler.HTTP_HEADERPROPS, headers)

metaData.put(HTTPHandler.HTTPGET_URLPROPS, urlProps)  

service.invokeEndPoint("ENDPOINTNAME",metaData,"") will call the end point by adding header and query parameters code oauthhttpendpoint.py

service.error("iface",response)  will throw the output as error message in the Test script to validate the output during development phase.

Once you receive the required response, you can parse them for fields to be stored into Maximo.

References: autoscript-endpoint-access-oauth2-yasutaka-nishimura/

https://auth0.com/intro-to-iam/what-is-oauth-2

https://www.ibm.com/support/pages/node/6831321

Maximo Resend failed transactions via Publish channel using Automation Script

What ?
Resending failed transactions from Maximo to External System via Publish Channel using Automation Script

Why ? 

In Maintenance projects, we are requested to resend a bulk of transactions to External System on cases where outage/connection failure in the middleware.

Data Export functionality in External System --> Publish Channel tab offers the way to retrigger the records to End point. But, for the large number of records, it will take a lot of time.

How ?

We can automate it using an Object Launch Point Automation Script.

code resendpublishchannel.py

Use Maximo MeaGlobal directory to store the file and read it from the automation script:

• SaaS Maximo 7.6.x MeaGlobal directory = ./MeaGlobalDirs
• MAS 8.x  MeaGlobal directory =  /MeaGlobalDirs

sample whereClause file

How to run the script ?

Activate the script and launch point to execute the script

Click on the "Test Script" button

If you have enabled the Message Tracking for the publish channel in the script, you can view the outbound message sent to End point.

Please disable the Launch Point and Automation Script after resending the transactions, because it will impact the Maximo functionality on the Action application.

Note: Resending transactions to External System would cause financial mismatch or reconciliation with Maximo. Please consult with end users before resending them.

Maximo execute sql scripts without Database access

What ? 
Running DML sql scripts from Maximo UI Automation scripts without write access to database

Why ? 
Some projects won't provide write access to the database. In such cases, we need to find a way to execute update or insert sql statements for our support related tasks. 

Even if we have write access to database, there will be change freeze period between 17th December to 3rd January to block the execution of DML commands. 

How ? 
Create an automation script with Object Launch point code runsqlfile.py

If you are running the script from a cron task, the connectionKey should be retrieved from MXServer instead of implicit variable mbo.

connectionKey = MXServer.getMXServer().getSystemUserInfo().getConnectionKey()

or
connectionKey = mbo.getThisMboSet().getSystemUserInfo().getConnectionKey()

Sample Object Launch point:
Object - Asset ; 

Event Condition - 1=1 or blank

Use Maximo MeaGlobal directory to store the file and read it from the automation script.

• SaaS Maximo 7.6.x MeaGlobal directory = ./MeaGlobalDirs
• MAS 8.x  MeaGlobal directory =  /MeaGlobalDirs

Sample sql script runfile.sql

How to run the script ?

Activate the script and launch point to execute the script

Click on the "Test Script" button

Click on "Test" button to run the python script 

Validate the DML scripts in the database

Please disable the Launch Point and Automation Script after running the sql scripts, because it will impact the Maximo functionality on the Asset application. 

Note: Running sql scripts directly into database is not recommended by the Product team. Please do your own due diligence on executing them

References:
https://www.linkedin.com/pulse/how-execute-sql-statements-from-automation-scripts-ibm-busuito/

https://www.linkedin.com/pulse/how-retrieve-jdbc-connection-automation-scripts-chrisman/

Maximo Prorate or Allocate Service Costs (Landed or Freight Cost) on POLINES in PO

What are Service Costs ? 

Service Cost includes Landed Cost, Freight Cost, Storage Cost, Managerial Personnel Cost, Advertisement Expenses, Customs Duty, Insurance Cost, Clearing Charges, Ground Maintenance, Plant Security Services etc. 

Why allocation of Service Cost is required ?

Allocation splits the standard service cost across stock tracked items on purchase order. We can allocate landed cost to PO line items even after the PO is invoiced. But, the POLINE item should not be consumed or shipped from the storeroom.

If we purchase an item in Inventory storeroom, unit cost is the cost price of the item. But there are other costs associated with purchasing items such as shipping cost etc.,  

Landed costs are a way to distribute these extraneous costs as they allow us to record the total cost of inventory per unit. It records the accurate profit reporting. 

How Services are represented in Maximo ?

Maximo enables the user to allocate or distribute the standard service cost on PO or Invoice approval.

Service Items are created in Service Items application. Services can be requested from internal or external vendors. When they are requested from Internal Vendor, we can record actual costs on Work orders without creating a PR. They are not linked to an Asset, it often include labor, tools and materials billed as single unit.

Service Item can be associated with vendors to restrict the access. 

Steps to prorate or allocate service cost on PO application

An Organization level MAXVAR variable INVOICEMGT is used to choose which application is used to prorate service cost. By default, the value is 1 where Invoice application is used for prorating. If you want to use it for PO application, set the value to 0 using below sql script.

UPDATE MAXVARS SET VARVALUE = 0 WHERE VARNAME = 'INVOICEMGT' AND ORGID = 'XXX';

Restart of server is required to reflect the changes in Maximo system

Create a PO with 3 POLINES each of quantity as 1: 

1. Item 1001 of unit cost 10

2. Item 1002 of unit cost 15

3. Standard Service Cleaning of unit cost 30 with Prorate Service ? checkbox selected

When you approve this PO, the cost of the standard service line will be distributed to all other POLINES. 

 

Calculation of prorated cost on polines

Prorate Factor = TotalProrateCostOfAllStandardServiceLines / TotalMaterialCostOfAllLines
                          =  30 / (10+15) = 1.2

TotalMaterialCostOfAllLines - i) must include only POLINES of type ITEM; 
ii) Exclude POLINES of direct issue items, services & materials and 
iii) Maxvar PRSPECIALDIRECT value should be set to 0. 

PRSPECIALDIRECT - specifies whether standard service costs are to be charged only to 'Direct Issue' line items in invoice. 

Prorate Cost  = Unit Cost * Prorate Factor
Loaded Cost = Unit Cost + Prorate Cost 

After PO Approval , the distribution of cost will be like

Item 1001 - Unit Cost= 10; Prorate Cost= 10 * 1.2 = 12; Loaded Cost= 10+12 = 22

Item 1002 - Unit Cost= 15; Prorate Cost= 15 * 1.2 = 18; Loaded Cost= 15+18 = 33

Standard Service - Unit Cost = 30 ; Prorate Cost = -30 (negative);  Loaded Cost = 0 (distributed to all item lines ) 

References: 
https://www.ibm.com/support/pages/prorating-or-allocating-service
https://www.ibm.com/support/pages/allocate-services-invoice
https://www.slideshare.net/CyrusSorab/ibm-maximo-prorate-services-to-add-loaded-costs-to-items-in-inventory-82554572

https://ms.aban.com/Manuals/Maintenance/Maximo/MAX%20900.00.pdf

https://help.brightpearl.com/hc/en-us/articles/212633143-Introduction-to-landed-costs?l_nav=1