Maximo Filter data using LOOKUPS whereclause tag

What ? Apply filter/whereclause on lookup values 

Why ? Easier to apply a condition on a list of values without a table domain. 

Some Maximo fields like WORKORDER.WORKTYPE have a field level class and adding a table domain would add more complexity for applying a list where condition. Instead a custom lookups.xml configuration in application designer is simple approach to achieve it.

How ?  Sample use case: Display items in Work Order plans tab that are in ACTIVE status or PENDOBS (Pending Obsolesce)  status having current balance greater than zero.

Follow the below steps to filter values from a lookup

• Export the LOOKUPS.xml from Application Designer
• Open the xml file and add/modify new table section
• Add whereclause attribute on the table tag

<table id="activeitem" inputmode="readonly" selectmode="single" 

whereclause="status='ACTIVE' or (status='PENDOBS' and exists (select 1 from inventory a, invbalances b 

       where a.itemnum = item.itemnum and a.itemsetid = item.itemsetid and a.itemsetid = b.itemsetid and a.location = b.location 

   and a.itemnum = b.itemnum and a.siteid = b.siteid and b.curbal &gt; 0 and a.status in ( 'ACTIVE', 'PENDOBS')))">

• We can't use greater than and lesser than symbol directly in the lookup.xml, so replace them with equivalent characters.

         greater than  (>) -->  &gt;      lesser than (<) -->  &lt;

• Condition in whereclause runs from Item table, so use item.itemnum inside the subquery

• Import the LOOKUPS.xml

• Link the custom lookup "activeitem" to the WOTRACK.xml application on the ITEM field under the Planned Materials section 

References :  limiting-lookups-using-whereclause

Maximo server host name for hyperlink in BIRT report

What?  get the server hostname of the environment where the BIRT report is running.

Why? In a few scenarios, we need to form a URL in hyperlink property to point it to Maximo record or any other integrated application specific to environment.

Maximo BIRT framework don’t support implicit variable :HOSTNAME. This variable is handy on getting the server host name inside Maximo applications like communication template.

How? In order to achieve it inside BIRT, we need to run the sql query in MAXPROPVALUE table from the system property mxe.hostname on the beforeOpen method of the dataSet; it can also be from any instance or custom system property.

Store the hostname in a Global variable - hostname to use them inside the report.

Use the variable in a cell hyperlink property to point to specific Maximo host name

"https://" + reportContext.getPersistentGlobalVariable ("hostname") + "/maximo/ui/login?event=loadapp&amp;value=asset&amp;additionalevent=sqlwhere"

+"&amp;additionaleventvalue=assetid = (select assetid from asset where siteid = '"+row["siteid"]+"' and assetnum = '"+row["assetnum"]+"')"

report file - hostname_hyperlink_design_file

Courtesy: Madhavan

Maximo MBO Change Status Based on Other MBO Conditions using Automation script

Change Status of MBO (PO or WORKORDER) based on other MBO Conditions using Automation Script Action Launch Point.

Why we need an Automation Script for changeStatus Action ? 

If the change status of a MBO (WORKORDER or PO) is a straightforward one like close WO after status Completion or close PO after receipts field value = COMPLETE, we can use an Action record of type "Change Status".

 

In real business scenarios, we need to changeStatus of a MBO based on conditions involving other MBOs, for example, complete a PO if its POLINES are invoiced and acknowledged by an external system or move an Inventory record to OBSOLETE if it's in PENDOBS status for 3 months without any current balance in INVBALANCES.

To build such complex requirements, we need to use an Action Launch point Automation script.

Maximo Configurations and Script

Create an Action launch point automation script with Object = PO. 

Code GitlabCode-actionlaunchpoint/poactionchangestatus.py

Don't call MboSet.save method inside the action as the changeStatus method will take care of it.

Maximo creates an Action record when we create an Action launch point script and populates its parameters [Script Name, Launch Point Name, Action Name]. 

Create an escalation to get the records against which the action need to be executed; Here, we have taken Approved PO with competed receipt.s 

Invoke Direct Print of Maximo BIRT Report using Automation Script

Auto-Initiate Maximo BIRT Direct Print Function from an Automation Script.

Why we need this option ? 

We need this feature to print records automatically on event trigger or user action, for example, a scenario of printing item or PO report on receiving items.

 

Please find the steps on how to implement it: 

• Create a custom field "Is Print" on non persistent object RECEIPTINPUT
• Use this field to select the records for printing on the "Select Ordered Items" dialog

• Create a script with attribute launch point on this "Is Print ?" field validation class code receiptinput.isprint.val.py .ITEMNUM field from RECEIPTINPUT object will be collected and stored in a field in PO object.

• Create 2 attributes PRINTLIST and NPPRINTLIST (non persistent) in PO object to store the list of items selected to be printed
• On "Before Save" of PO object, copy the values from NPPRINTLIST to PRINTLIST on an automation script of launch point PO with event as before save
• On "After save" event of PO object, trigger direct print of a report based on the values selected on the "Select Ordered Items" dialog code po.directprint.obj.as.py

References: direct-print-using-automation-script
Courtesy: Madhavan

Maximo SSL certificate for Integration between Systems

SSL Certificates for Maximo Integrations using Enterprise Service and Publish Channels with an External System using https connection. 

Maximo (or external system) which calls the https service needs to store the SSL certificate on their server to successful connect to the end point. It's usually set up at the start of building the Integration. But, if you encounter the error during the application maintenance, we need to replace the expired certificate with the renewed one in the server. 

Enterprise Service: When External System is connecting to Maximo Web Service URL (https://<hostname>/meaweb_mif/services/EXTERNALSYSTEM_EnterpriseService), it need to have Maximo SSL certificate on their server. If not, we end up in the below error

PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

To resolve the connection issue, we need to extract the Maximo certificate from Web browser and share it with External system.  

Open the Maximo URL, Click on the yellow highlighted lock icon on the address bar

 

Click on "Connection is Secure" option

                                   

Click on Details tab on the Certificate window

Download the SSL certificate and share it to External System for uploading / updating in their server.

Publish Channel/End Point - When Maximo wants to connect an outbound SSL service using HTTP End point or Publish Channel, we need to collect the certificate from external party and upload it to trusted certificates into IBM Websphere (Maximo 7.6.1.x) or WAS Liberty (MAS) where Maximo is hosted. 

If you don't have certificate present in the server, then you might face an below error. 

psdi.util.MXSystemException:BMXAA1482E - The response code received from the HTTP request from endpoint is not successful 

Please collect the SSL certificates from the target endpoint system and upload them into Maximo server

Reference: adding-trusted-external-certificates

Courtesy: Siddhartha Upadhyaya

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