IJ30823: AN EXCEPTION AFFECTS MATUSETRANS TRANSACTIONS AFFECTING DATA INTEGRITY

APAR status

• Closed as program error.

Error description

• When the user runs into an exception of any kind when trying to
  issue an item then moves on to the next issue (of an item). The
  impact is that the next transaction will not accurately process.
  This will be demonstrated by 2 ways below.
  Please note the validation error being introduced, by way of
  automation script, is strictly for the test scenarios and are in
  no way the root cause.
  
  Pre-requisite steps for setting up test data: though you do not
  need to create items, work orders, as outlined below, it is
  simpler to follow using new records
  
  1. Create work orders:
  Duplicate demo work order 1009 without tasks (go to work order
  tracking, open work order 1009.
  Select Action > duplicate work order
  Select without tasks)
  Perform this step twice to create 2 work orders, each from 1009
  Approve both work orders.
  
  For my test, I generated the following work orders:
  
  Work Order
  1237 I will use this work order in the tests to generate error
  against
  1264 I will use this work order to process issue against
  
  2. Create Item, and Associate to Storeroom
  Go to Item Master, open item 11241, duplicate and named item
  number MYITEM.
  Select Action > Add Item to Storeroom > specify CENTRAL> in the
  dialog, specify Unit Cost = 10 ,
  Default Bin=MYBIN, Current Balance=100, Issue Unit=EACH, Order
  Unit=EACH
  Select Action > Change Status > choose ACTIVE and check Roll New
  Status to Organizations and Inventory?
  Checking the database also matches:
  select * from invbalances where itemnum = 'MYITEM'
  
  At this point, there are no issue transactions
  
  select * from matusetrans where itemnum = 'MYITEM' order by
  transdate
  
  returns no records (you can also confirm by going to inventory
  application and choose view inventory
  transactions).
  
  3. Set-up error message
  
  Go to Database Configuration > Messages > click New Row to
  create a new message:
  
  Message Group: testgrp
  Message Key: testkey
  Display Method: MSGBOX
  Message ID Prefix: BMXZZ
  Message ID Suffix: E
  Value: test error message -error when saving issue transaction
  
  4. Set-up validation using automation script
  Go to automation Scripts application > create > Script with
  Object Launch Point
  
  Launch Point: SAVE
  Object: MATUSETRANS
  Object event Condition: refwo = '1237'
  Events: Save
  Add?: Checked
  Before Save: Checked
  Click next
  
  In script, specify: WT_TEST_ERROR
  Click next, in source code, specify:
  service.error('testgrp','testkey')
  Click create
  
  Steps to replicate:
  Issue # 1
  Go to Inventory- Issues and Transfers application, specify and
  select CENTRAL on List View tab
  On the Issue tab, click on New Row
  Item field: MYITEM
  Work order: 1237
  Storeroom: CENTRAL (in BEDFORD SITE)
  Quantity: 1
  
  Click on Save, you should receive the error message from the
  automation script
  
  Click OK, then click on the line delete trash icon
  
  NOTE : Do NOT click SAVE or leave this screen
  
  From the database, I checked the invbalances query and it still
  shows 100 and there are no matusetrans records yet, which is the
  expected behavior
  
  Click on New row again
  Item field: MYITEM
  Work order: 1264
  Storeroom: CENTRAL (in BEDFORD SITE)
  Quantity: 1
  
  NOTE: You should see the old transaction that failed, marked to
  be deleted (with a strikethrough line), and the new one
  
  Click Save, you should not receive any errors
  
  Checking issue transactions:
  select
  matusetransid,itemnum,storeloc,binnum,quantity,unitcost,linecost
  ,refwo, curbal from
  matusetrans where itemnum = 'MYITEM' order by transdate
  
  As expected, it created the matusetrans record, however curbal
  is incorrect (should be the beginning balance of 100)
  
  Checking inventory balances
  select * from invbalances where itemnum = 'MYITEM'
  
  Maximo did not update the invbalances table to set curbal to 99
  
  So, there is a transaction control in maximo, where matusetrans
  and invbalances table are not part of
  the same database transaction. They should both process or fail
  to process.
  
  Issue 2:
  Go back to the automation script
  Open WT_TEST_ERROR, click on Launch Points Tab, open the
  existing point, change from before save to after save
  Click ok
  Go back to the issues and transfer application, repeat the same
  two transactions in the same way (I am only changing the qty to
  5 for each of them)
  Checking matusetrans
  
  Maximo created the issue transaction but curbal is incorrect
  
  Checking inventory balance
  This time, maximo updated the balance but with the wrong number,
  it reduced it by both the issue
  transaction that failed and the issue transaction that
  processed.
  .
  Additional Comments
  To emphasize that the automation script is NOT the cause, the
  issue can also be replicated
  at the Database level by use of a Database Trigger, as follows:
  CREATE OR REPLACE TRIGGER WT_DEMO BEFORE INSERT OR UPDATE ON
  MAXIMO.MATUSETRANS FOR EACH ROW
  
  declare
  
  user_xcep EXCEPTION;
  
  PRAGMA EXCEPTION_INIT( user_xcep, -20001 );
  
  BEGIN
  
  IF (:NEW.REFWO = '1264' and :NEW.ITEMNUM ='11241') THEN
  
  raise user_xcep;
  
  END IF;
  
  END;
  
  /
  
  SHOW ERRORS;
  
  
  EXPECETD RESULT
  The Maximo Architecture issue is that ALL should pass or fail.
  If not, then any interruption seems to cause data
  inconsistencies in any of those related tables.
  .
  System Information
  
  Tivoli's process automation engine 7.6.1.1
  .
  Application
  Issues and Transfers
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * maximo                                                       *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Incorrect inventory balance for the subsequent valid         *
  * matusetrans records after an exception occurred upon         *
  * inserting a matusetrans record which user deleted.           *
  ****************************************************************
  

Problem conclusion

• This is fixed in code.
  The fix for this APAR is contained in the following maintenance
  package:
   | release\fix pack | Interim Fix for Release 7.6.1.3 Product
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Modules/Macros

• maximo
  

Fix information

• Fixed component name

  MAXIMO ASST MGM

• Fixed component ID

  5724R46AM

Applicable component levels