Violation data integrity in storage transactions.

Description

User creates warehouse documents (Material Movement, Shipment, Phisical Inventory etc.) and complete it. Then we have
records in storage transaction journals: storage journal (M_StorageOnHand) and transaction journal (M_Transaction).
We can calculate inventory count by 2 way:
1) calculate from storage journal (SELECT SUM (QtyOnHand) FROM M_Storage ... )
2) calculate from transaction journal (SELECT SUM (MovementQty) FROM M_Transaction ... )
Result in both cases should be the same.

Found situations where result in both cases is different. In other words we can see violation data integrity.

Situation 1.
Create & complete Material Movement (or other warehouse document), but when system try to save record in M_Transaction any exception has occur (for ex. deadlock) and record in M_Transaction not saved.

Situation 2.
We have Table script validator (Rule) on M_Transaction on "Table Before New" event.
User create & complete Material Movement (or other warehouse document). Validator checks data and does not allow save records.

Violation data integrity possible due to an error in the java-code of warehouse documents classes (MMovement.java, MInventory.java, MInOut.java etc.).
For example, consider MMovement.java.
Find procedure completeIt().
First the system try to make an entry in the table M_StorageOnHand:

if (!MStorageOnHand.add(getCtx(),locator.getM_Warehouse_ID(),
line.getM_LocatorTo_ID(),
line.getM_Product_ID(),
M_AttributeSetInstanceTo_ID,
ma.getMovementQty(),ma.getDateMaterialPolicy(), get_TrxName()))
{
String lastError = CLogger.retrieveErrorString("");
m_processMsg = "Cannot correct Inventory OnHand (MA) - " + lastError;
return DocAction.STATUS_Invalid;
}

If error occur (for ex. negative inventory), then record not saved in M_StorageOnHand and procedure finished.
Data integrity is OK.
If no errors then record saved in M_StorageOnHand and system go to next code:

trxFrom = new MTransaction (getCtx(), line.getAD_Org_ID(),
MTransaction.MOVEMENTTYPE_MovementFrom,
line.getM_Locator_ID(), line.getM_Product_ID(), ma.getM_AttributeSetInstance_ID(),
ma.getMovementQty().negate(), getMovementDate(), get_TrxName());
trxFrom.setM_MovementLine_ID(line.getM_MovementLine_ID());
if (!trxFrom.save())
{
m_processMsg = "Transaction From not inserted (MA)";
return DocAction.STATUS_Invalid;
}

If error occur (situations see above), then record not saved in M_Transaction and procedure finished.
With that records in M_Storage stay saved!!! Violation data integrity occur.

Same situation in other warehouse documents classes.

To solve this problem i see in follow:
1. Have same code saving records in MStorage and in MTransaction in more warehouse classes are incorrect, because high chance to make bug.
2. Code saving records in MStorage and in MTransaction need to move out to one general class (for example StorageTransaction.class) and in it create procedure called from warehouse documents classes.
In this procedure provided that the recording in M_StorageOnHand and in M_Transaction complete in one commit.
And if error occur when try to saving in one or in other table then rollback all saved records.

Environment

None

Activity

Show:
Carlos Ruiz
September 16, 2015, 3:13 PM

Thanks , we recently solved which was causing the mismatch between M_Transaction and M_StorageOnHand you describe.

I think the case you describe is not correct as the MTransaction is being created on the same trxname and it must be rolled back with all the trx, so, maybe if you have a test case that can be reproducible (i.e. a patch forcing the transaction to fail on the case you describe).

Regards,

Carlos Ruiz

Carlos Ruiz
May 9, 2016, 3:40 AM

Closing as not reproducible, please reopen if you have a test case to reproduce

Assignee

Carlos Ruiz

Reporter

Денис Кузнецов

Labels

None

Tested By

None

Components

Affects versions

Priority

Critical
Configure