Maximo Yes/No/Cancel Exception using Automation Script on Work Order Status Change

Many clients want to avoid customization and use only Automation Scripts for implementing business logic; One of the frequent request is to throw warning on Work Order status change.

The best triggering event is to have an attribute launch point on object:attribute WOCHANGESTATUS:STATUS to throw error or warning. 

code automationscripts/attributelaunchpoint/wochangestatusscript.py

Create the below items for the script to run in the environment

1. Relationship name - C_OPENINVUSE ; Parent - WPMATERIAL ; Child - INVUSELINE

(refwo=:wonum or refwo in (select wonum from workorder where parent=:wonum and siteid=:siteid)) and itemnum = :itemnum and itemsetid = :itemsetid and siteid = :siteid and invusenum in (select invusenum from invuse where siteid = :siteid and status in ('ENTERED', 'STAGED'))

2. Maximo Messages 

i) Message Key: AVOIDRECORD  Message Group: WPINVUSE 

Display Method: MSGBOX  Error

Value: Work Order Plan Materials is used in open Inventory Usage {0}

Buttons: OK ? 

ii) Message Key: CANAPPROVEWO  Message Group: WORKORDER 

Display Method: MSGBOX  Information

Value: Planned materials are not yet issued to WO from Inventory Usage {0}. Completing the work order will block the Inventory issue and retain the materials in storeroom. Would you like to proceed?

Buttons: Yes ?  No ? 

Testing:

When you try to change the status of WO to COMP and the WO has open Inventory Usage record in statuses - ENTERED and STAGED, you will get the Yes/No pop-up window.

This example doesn't include Cancel button - you can configure it in message (ii) and add a method def cancel() in the code.

References: https://www.bpdzenith.com/prompting-a-yesno-dialog-from-an-automation-script/

Maximo Asset Hierarchy BIRT Report

Asset Hierarchy is used to define the physical or functional parent-child relationship of assets created within the Location application. 

The real advantage of developing such location/asset hierarchy is to easily locate an asset to perform work orders, grouping of assets under a location to represent plant systems, etc.,

OOB Maximo has Location Hierarchy by system report, but it doesn't have similar report to display the Asset Hierarchy. 

A report for asset hierarchy using ASSET and ASSETANCESTOR tables is available in gitlab asset_hierarchy.rptdesign

The flowchart describes the nested table structure used to design the hierarchy report.

                                               

Configure the dataSet with a Parameter to accept the value from the parent table design

Parent parameter should be linked to the assetnum of parent table

Always use - Auto Layout - Fixed Layout would cause the results go in separate pages.

Report Parameters need to be filled with attribute (siteid,location,assetnum) and lookup values (site,locations,asset) to let the user choose values from the lookup.

 

Report Output:

2) We can also build an Asset Hierarchy Data structure using a SQL query. But it won't be work in BIRT design file. 

SELECT

    level,  assetnum,  parent,  lpad (' ', level * 4, ' ') || assetnum,

    description,  location,  siteid

FROM asset

WHERE  siteid = 'BEDFORD'

START WITH siteid = 'BEDFORD'  AND parent IS NULL AND children = 1 

CONNECT BY PRIOR assetnum = parent

References:

maximo-asset-hierarchy-sql
ibmtechexchange_community_post
designfile_asset_hierarchy.rptdesign
importance-of-asset-location-hierarchies/

Forecast reports using Cross Tab feature in BIRT Maximo

Why Forecast is needed ? 

For planning of work or budgeting for long term operations, we need to forecast the upcoming work along with costs/labors/services/materials/tools associated with them.

What is Cross tab ? 

A Crosstab (or Cross Tabulation) is a table showing the relationship between two or more variables for quantitative analysis by showing the correlation change from one group of variables to another. It allows for the identification of patterns, trends and probabilities within data sets. Cross Tabulation is used across various industries, job roles and analysts. It benefits many people for forecasting cost or material requirement for the company.

Sample Requirement
I would like to create steps to develop a crosstab table in Maximo BIRT reporting functionality with a use case.; PM (Preventive Maintenance) Cost Forecast Reporting using Cross Tab feature.

Development steps
Create a report with predefined template – “Tivoli Maximo List Template”

Remove the “Detail Row” section from the template. Cross tab inserted into Detail section would result in duplicated rows, so it needs to be created in Header section.

 

Merge the cells in the Table header and insert the “Cross Tab” element into the table header

Construct the Data Set with all required output columns – this report shows all OOB fields to display the PM Cost forecast report with required Work Orders per month

Create a new Data Cube 

Associate the Data Set for Data Cube. Add Groups (Dimensions) for columns that we want to display in rows section and for column header (month in this case) of Cross Tab Section.

Summary field is the one which is at the right-hand side of Cross Tab element design.

Drag and drop the fields from Dimensions to rows section of the Cross Tab element.

Place the MONTH field from Dimension to column header section of cross tab

Place the Summarize field in the column field section. 

The design file is available in gitlab - https://gitlab.com/bysurendar/maximo/-/blob/master/reports/pmcostforecast.rptdesign

Output of the report:

Rows are the list of Preventive Maintenance records which have forecast generated for the given date range

Columns are months for which the forecast exists. 

Summarized values are the Work Orders that would be needed for a month to perform the Work.

Courtesy: Vijayabanu Pitachi 

References: 

https://www.ibm.com/docs/en/elo-mc/7.6.0?topic=tab-tutorial-creating-cross

https://www.youtube.com/watch?v=LKuCNuz67YA

Repoint References from Original to New Cloned Application

In Maximo projects, we always need to have a custom application that used to be a minimal version of original base application. 

If it is developed at the beginning of the Implementation, then it will be straight forward method for providing access, referring it in Start center, creating reports etc., In case, it was requested after the Original application is used for sometime, then we need to repoint the references from Original to newly cloned application. 

For example, if we clone an Original Application (like WOTRACK) to New Application (C_WOTRACK), there will be a few references like below are need to be updated. 

•  The User created queries need to be copied to new application

insert into query (app,clausename,owner,description,clause, ispublic,queryid, langcode,intobjectname, priority, isuserlist, notes)  (select  'WOTRACK' as app , clausename, owner , description, clause, ispublic, queryseq.nextval as queryid, langcode, null as intobjectname, null  as priority,  isuserlist, null as notes from maximo.query  where app = 'C_WOTRACK' ) ;

• Reports need to be migrated to New application
• The Start center references need to be updated to new application

update layout set queryapp = 'C_WOTRACK' where queryapp = 'WOTRACK' ;

-- layout will repoint the link in the query result set

update faconfig set app = 'C_WOTRACK' where app = 'WOTRACK' ;

-- it will update the favorite application in the layout

update ACTIONSCFG set app = 'C_WOTRACK' where app = 'WOTRACK' ;

-- it will update the quick insert links in start center layout

• Delete the authorization of original application for Users and copy the same access to new application in Security Group

insert into applicationauth (groupname, app, optionname, applicationauthid , conditionnum ) (select groupname , 'C_WOTRACK' as app, optionname, APPLICATIONAUTHSEQ.nextval as applicationauthid, conditionnum  from  maximo.applicationauth where app = 'WOTRACK' and groupname not in  ('MAXADMIN' , 'MAXEVERYONE' , 'MAXDEFLTREG' ,'ANYWHERE_TECHNICIAN', 'ANYWHERE_APPROVER' ,'ANYWHERE_INSPECTOR' ) ) ;

delete from applicationauth where groupname not in  ('MAXADMIN' , 'MAXEVERYONE' , 'MAXDEFLTREG' ,'ANYWHERE_TECHNICIAN', 'ANYWHERE_APPROVER' ,'ANYWHERE_INSPECTOR') and app = 'WOTRACK' ; 

We don't need to modify the maxadmin and other Out of box groups which might affect Integration or other basic functions. 

• Create a new Object Structure for reporting (similar to REP_WORKORDER) for newly created application. It is needed for creating adhoc report by users
• Hide the original application from the Users view and position the cloned application in same place as original application. We need to restart the application servers for reflecting this change in Maximo

 update MAXMENU set position = XXXXX , image = 'appimg_wotrack.gif'  where menutype = 'MODULE' and elementtype = 'APP' and moduleapp = 'WO' and keyvalue = 'C_WOTRACK' ;

 -- XXXXX value should be lower than original application position. for example, if WOTRACK position is 11180, then C_WOTRACK is 11175

 update MAXMENU set visible = 0 where menutype = 'MODULE' and elementtype = 'APP' and moduleapp = 'WO' and keyvalue = 'WOTRACK' ;

-- visibility of original application is set 0, so that it wont be displayed in menu lookups and Work order module.

  

Maximo Automation Scripting Best Practices for Performance

Automation Scripting is a feature in Maximo used to implement business logic using any JSR 223 specification compliant Scripting language. 

The automation script code gets compiled to Java bytecode, gets cached as part of Maximo runtime. When the script is invoked, it is cached bytecode that is executed by JVM in the same thread as other Maximo business logic written in Java. A poorly written code can cause performance issue, so we need to follow a few guidelines similar to Java customization.      

Cautious of having Multiple Scripts for Single Launch Point Event

Maximo allows to include multiple scripts for the same launch point triggering event. Maximo event topic is un-ordered map, where the events gets fired without any sequence. 

 
If you write script that need to be executed in specific order or need any pre-requisite actions on the same launch point, then it cause issues on the expected output.

 
Possible solution: Ensure that there is no dependency between scripts or combine them into a single script

Use of Single script for Multiple Launch points

We should try to create a single script where we need a generic business logic and attach it in multiple launch points. 

One such business case is to restrict description to 50 characters between Maximo and other external systems.

------------------------------------------------------------------------

strLen = len(mbo.getString("description")) ;

if(strLen > 50):

   service.error("system","DescriptionFieldLimit");

--------------------------------------------------------------------------

Above script can be attached to any MBO where description need to be restricted.

Choice of Launch point and Event

Launch points decide when the script need to be triggered. Choosing a right launch point can help avoid certain performance issue. 

Case 1: skipping an outbound integration message can be done using 2 launch points: User Exit Scripting or Publish Channel Event Filter. The best launch point to use is Event Filter because the skip action happens before the serialization of MBOs. Sample script for Event Filter to skip records based on status is below:

---------------------------------------------------------------------

If service.getMbo().getString("status")== "APPR":

evalresult = False

evalresult = True

--------------------------------------------------------------------

Case 2: Initialization of Attribute value can be set using Object Launch Point or Attribute Launch Point with Event - Initialize Value. We must use Attribute Launch Point, because Object Launch point can lead to performance issues when selecting a lot of MBOs in List tab, escalations or API actions. 

Naming Convention for Automation Script

As we know the launch points play an important role on the Scripting Performance. We need to have a way to identify the launch point of the script without navigating to the launch point tab & dialog in Scripting application.

The Name can be written in 

TABLE.SCRIPTTYPE.ATTRIBUTE.EVENT.DESCRIPTION

TABLE is WORKORDER, ASSET, PO, PR etc.,

SCRIPTTYPE can be Object, Library, Action, Attribute level, Integration and Custom Condition. 

ATTRIBUTE - Attribute name; It is used only for attribute launch point scripts 

EVENT - It's predefined triggering point. Before Save, After Save, After Commit, Attribute Initialize Value, etc.,

DESCRIPTION - can be any free text. You can ignore it based on your field length.
for example: WORKORDER.OBJECT.BEFORESAVE
WORKORDER.FLD.PRIORITY.MANDATE.INITVAL
COMMON.LIB.LIMITDESC

Avoid initialization Events from List tab

You can perform heavy initialization logic once the MBO is opened in Maximo Main tab. Doing the same action when it is loaded in List tab would cause poor response time on loading a lot of records. 

-----------------------------------------------------------------------------------------------------------------------

from psdi.common.context import UIContext

if UIContext.getCurrentContext() is not None and UIContext.isFromListTab() == False:

print ("the logic goes here")

-------------------------------------------------------------------------------------------------------------------------

Use MboSet.count() only once or isEmpty() instead

MboSet.count() calls sql script to Database every time it is executed in the script. 

In order to improve the performance, write the script once and store it in a variable. 

--------------------------------------------------------------------

cnt = mboset.count()

if cnt <= 1:

        service.log("skipping this as count is " + cnt)

-------------------------------------------------------------------

or use mboset..isEmpty() to check if the count is zero. isEmpty() will be called once if mboset is not initialized, any more calls to this function will be accessed from memory without hitting the database.

Close the MboSet opened from MXServer.getMXServer.getMboSet("")

Maximo framework will always release the mboset that are created by launch point mbo or related objects mbo.getMboSet().

 
But, for Mbosets opened using the server MXServer.getMXServer.getMboSet("") api, the programmer is responsible to close the mbosets. If it is not handled, it may lead to OutOfMemory Error. 

------------------------

try:

    xxxxxx

finally:

    mboset.cleanup()

-------------------------

Check if logging is enabled before writing log statements

As a thumb rule, write the print or log statements only after checking whether the logging is enabled or not. The file write operations would take reasonable amount of time to complete the execution of code. 

-----------------------------------------------------------------------------

from psdi.util.logging import MXLoggerFactory

logger = MXLoggerFactory.getLogger("maximo.script”);

debugEnabled = logger.isDebugEnabled()

if debugEnabled:

 service.log("MBOs saved in DB"+mboset.count())

-------------------------------------------------------------------------------

Prefer SQLFormat Class over String concatenation

If SQL query is necessary, you can leverage SqlFormat class for MBO data filtering instead of where clause with concatenated string using +

It helps in data type formatting across databases, parsing errors and improved security by avoiding SQL injection.

# Unsafe string concatenation 

from psdi.server import MXServer

whereClause = "orgid='" +mbo.getString("ORGID")+ "' and ticketid='"+mbo.getString("ORIGRECORDID")+ "' and siteid= '"+mbo.getString("SITEID")+"'" 

srSet = MXServer.getMXServer().getMboSet("SR",MXServer.getMXServer().getSystemUserInfo())

srSet.setWhere(whereClause)

srSet.reset()

--------------------------

 

# Use of SqlFormat

from psdi.mbo import SqlFormat

from java.util import Calendar

cal = Calendar.getInstance()

today = cal.getTime()

cal.add(Calendar.DATE, -30)

thirty_days_ago = cal.getTime()

sqlf = SqlFormat(mbo, "orgid = :1 and ticketid = :2 and siteid = :3 and statusdate > :4")

sqlf.setObject(1, "SR", "ORGID", mbo.getString("ORGID"))

sqlf.setObject(2, "SR", "ticketid", mbo.getString("ORIGRECORDID"))

sqlf.setString(3, mbo.getString("SITEID"))

sqlf.setDate(4,thirty_days_ago)

srSet = mbo.getMboSet("$SRLIST", "SR", sqlf.format())

-------------------------------

Avoid calling SAVE in middle of transaction

MBOs created or updated by a launch point script or by its related mboset are always part of the single transaction.i.e., committed into database by a single SAVE. 

If you create or update a MBO by MXServer.getMXServer().getMboSet("objectname"), those changes would be outside of the script execution. You need to add it by this code: 

------------------------------------------------------------------------------------------

newMboSet = MXServer.getMXServer.getMboSet("WORKORDER")

mbo.getMXTransaction.add(newMboSet)

------------------------------------------------------------------------------------------

In real time scenario, the chance of using this code is very rare and tricky too. 

References:

Scripting_Best_Practices_For_Performance.pdf

service-methods-automation-scripts.html 

Maximo 7.6.x Barcode Generation in BIRT reports

What is Barcode ? 

A barcode, consists of bars and shapes of varying width, is a machine-readable form of alphanumeric characters that can be read with an optical barcode scanner. Barcodes are used for many applications across many Industries such as Manufacturing, Distribution, Retail, Food, Pharmaceutical, Transportation, Entertainment etc.,

Why Barcodes are needed in Maximo ?

Maximo is designed for managing assets in many industries where barcodes are used. One such use case is Spare parts management in Inventory. Stocks are scanned in front of the shelves (or bin) to enter actual balance of Inventory - it increases Inventory accuracy and Work efficiency. 

Maximo Product team don't support for any issues barcode in BIRT. So, we need to know the methods of generating barcodes for Maximo application.

Barcode Generation in BIRT Report

As Maximo uses BIRT for reporting, we will see on how barcodes can be displayed in a report. We have following methods of creating barcodes in BIRT report. 

•         Barcodes using plugins
•         Barcodes using ZXing API

Barcode by plugins

Plugins are provided by different vendors, available in BIRT marketplace. 

We need to place the barcode plugin jar files in this path <folder>\birt_431\eclipse\plugins to display an extension item for barcode in Report Item palette in Eclipse Report Designer. 

Preview Barcode in Eclipse

                    

For running the barcode report from Maximo application, we need to upload the plugin jar file in this installed path 

\IBM\SMP\maximo\applications\maximo\maximouiweb\webmodule\WEB-INF\birt\platform\plugins,

then build and deploy the EAR for making this report runtime generate barcode in browser. 

For development purpose, we used the trial version of the plugin, which will display the barcode with trial trademark.  Plugins for production are priced based on number of users.

Barcodes by ZXing API

Zxing (Zebra Crossing) - It’s open source API barcode image processing library implemented in Java. It supports one and two dimensional barcodes.

Download the zxing jars and add them to the Windows -> Preferences -> Report Design -> Classpath of report design.

The jars can be downloaded from git : jarsforzxingbarcode

Add a normal Image in the report design, right click on the Image -> click on Properties -> Edit. Choose the dynamic Image and Select Image Data from Container Data Set.

On the Data Binding of imgbar (BLOB), we write our code in Expression section for generating barcode in Image object.

Click here to view code in gitlab

You can modify the writer object to any format like Code39, QRCode etc., In this method of creating barcode, we don't need write any custom java code for deployment. It will be embedded in the report file and runs by the jars files in the Maximo libraries path. 

In order to make this report run from maximo application, we need to add the jars files in this Maximo installed path -
IBM\SMP\maximo\applications\maximo\maximouiweb\webmodule\WEB-INF\lib and proceed with deployment of EAR process.

Preview of report from Maximo application

Sample Report: inventory_barcode_zxing_mx76.rptdesign

References:

Fundamentals and Applications of Barcode Create Barcode Images Dynamically by Java Code in BIRT

Javadocs for ZXing

Sample Javascript to display barcode using ZXing

Download ZXing Jar files

Fuel Consumption Import in Maximo Transportation

Maximo Transportation Solution has an application Fuel Transaction Import (Tr) which is used to import Inventory transactions plus meter readings from Maximo.

Steps to be followed to import Fuel Transactions.

• Create a template
• Import file
• Edit records for corrections (optional)
• Reconcile and create Inventory record

Create a template

We need to create a template for loading fuel consumption data.  It need to be defined in Organization (Tr) application, Select Action -> Transportation Options -> Fuel Transactions Import Setup.

Sample template file can be found in https://gitlab.com/bysurendar/maximo/-/blob/master/templates/fuelconsumption.csv

Import file

After we define the template, we can import the data file from Data Import (Tr) -> Fuel Transactions Import (Tr) application. Click on Import File option under More Actions Section. 

In the Import File dialog, choose Template ,defined in Organization (Tr) application. 

Select whether the imported file has Inventory Transactions & Meter Readings. 

Choose the Site for Imported Readings. 

Once you upload the file, you will get a couple of system messages. The loaded transaction will be created in PENDING status.

Edit records for corrections

We can edit the records before committing the Meter reading and fuel transactions. It can be done by Imported Transactions --> Edit Records.

Reconcile and create Inventory record

In order to commit the transaction, we need to use option Imported transactions -> Create Inventory Transactions.

First Stage of committing to database is to reconcile the transactions. You can do it by choosing the Site, selecting the records and click on Reconcile button.

 

The Status of the transaction goes to "RECONCILED" Status.

Second stage is to Create Inventory transactions for reconciled records from the same dialog. It will create the Inventory transaction for item in a storeroom. 

The loaded fuel transaction of item DIESEL is recorded as ISSUE in Inventory application. 

Since we have loaded meter readings along with fuel transactions in the same file, we can create Meter readings from Fuel transactions Import (Tr) application. The detailed explanation is given in this website  Bulk Meter reading Load

Advantage: Ease of use. We don’t need to configure MIF related object structure, enterprise service or external system to import data. It is very helpful for end users to collect fuel consumption readings from external system, validate and upload them.