Ensure Asset’s serial number uniqueness

Sometimes you need to ensure the uniqueness of a certain database column in the Maximo database. A common example of this scenario is to ensure that asset’s serial numbers are not duplicated. This can prevent the creation of duplicate assets.

In this post I describe two techniques to achieve this using a unique database index or an automation script.

Unique Index

The simplest and most reliable way of achieving this is to define a unique database index on the SERIALNUM field of the ASSET table. However, we must make some considerations first. The asset serial is not unique alone for two reasons:

1. Typically you enter the manufacturer’s S/N in the Asset serial number field. In theory two different assets with different part numbers can have the same serial number. This means that we typically have to include the ASSET.ITEMNUM field in the unique index.
2. Maximo creates duplicate rows when moving an asset form one site to another. This means that we have to include the ASSET.SITEID field in the unique index.

Before proceeding it is important to perform a final check. We must ensure there are no existing rows in the ASSET table having index duplicates. This is to prevent errors during the database configuration fhase when Maximo will try to create the database index. This can be performed with an SQL query.

select SERIALNUM, ITEMNUM, SITEID
from ASSET
group by SERIALNUM, ITEMNUM, SITEID
having count(*)>1;

You will probably find some duplicated rows for assets having null values in the SERIALNUM or ITEMNUM. If you are able to solve all the issues (maybe inserting dummy values in duplicated rows) you can proceed creating the index in Database Configuration application.

Warning! Do not create the unique index if the above query returns data. The database configuration process will fail and you will have problems rolling back.

Automation Script

If you are not able to fix those problems you can use a dfferent approach based on a custom automation script to perform the same check. This allows to display a better error message when a user tries to saving an asset with a duplicated SERIALNUM, ITEMNUM, SITEID triplet.

# Script: ASSETSNVALIDATE
# Launch Point: Save - Add/Update - Before Save
# Object: ASSET
# Check for duplicated serial numbers

assetset = mbo.getMboSet("SAMEASSET")
if not assetset.isEmpty():
  params=[mbo.getString("ASSETNUM"), mbo.getString("SERIALNUM")]
  service.error("mxd", "dupassetsn") 

The launch point of the script must be on the ASSET – Before Save event.

A system message mxd.dupassetsn must be defined to correctly display the error to the user.

Asset {0} has the same serial number {1}

One last thing is to create the ASSET.SAMEASSET relationship:

• Object: ASSET
• Child Object: ASSET
• Relationship: SAMEASSET
• Where: assetnum!=:assetnum and itemnum=:itemnum and serialnum=:serialnum and siteid=:siteid