Document Sequence in Maximo with DB Trigger - IBM Maximo

Maximo uses Autonumbering, which basically a Unique value with optionally prefix or suffix. 

Whenever a user hit the New (#) button in any application, Maximo generate a AutoNumber, user can change that number but the CurrVal of sequence has moved to NextVal. 

One of my client's audit requirement is to have an auto incremented unique sequencial document control numbering without jumping(missing numbers), without losing even a signal number.  

We handled this situation at database level to insert into a new field with an increment of number before insert.

1. create a DOCNUM field in PO Object by using Database Configuration, check filterable and Wildcard.

2. Create a trigger with below mentioned code to fire on PRE-INSERT, get max(DOCNUM) from all POs , increment with 1 and Assign value to DOCNUM. 

3. If PO type is STD then a word 'PO' will be prefixed with Number else 'SO'

4. also Concat the fiscal year between prefix and actual number (SO-16-12345)

Trigger Code is as below:

CREATE OR REPLACE TRIGGER MAXIMO.PO_TEST
  NO CASCADE BEFORE INSERT
  ON MAXIMO.PO
  REFERENCING
    NEW AS N
    OLD AS O
  FOR EACH ROW
BEGIN
DECLARE POTYPE1 VARCHAR(10);
DECLARE YY INTEGER;
  IF N.POTYPE = 'STD' THEN
    SET POTYPE1='PO';
  ELSE
    SET POTYPE1='SO';
  END IF;
  Select to_char(add_months(sysdate,7),'YY') INTO YY from sysibm.sysdummy1;
SET N.ROWSTAMP = NEXTVAL FOR MAXSEQ
   ,N.DOCNUM = POTYPE1||YY||(select LPAD(MAX(SUBSTR(DOCNUM,7,3))+1,5,'0') from PO);
END;