|
|
After a complex research here are the conclusions about this issue:
1. It is an old Core's issue, however it was very difficult (or even impossible) to reproduce.
2. However, since 0049551 fix the issue can be reproducible in several places, like from the Inventory API or even from the Physical Inventory window under some circumstances.
That's why we are backporting it to 22Q4.1 (where 0049551 was fixed).
A way to reproduce it is:
1. Pre-requisite: to have a storage detail with qtyonhand = 0 and without attribute set instance. This might happen in old instances that have been working with stock before applying 0049551 fix
2. Create a physical inventory for this stock, with qtycount = 0
3. Update quantities
4. Process the inventory
The error is thrown: org.postgresql.util.PSQLException: ERROR: el nuevo registro para la relación «m_storage_detail» viola la restricción «check» «m_storage_detail_check1»
From a technical POV the explanation is a bit complex, but here it is:
M_UPDATE_INVENTORY db function is in charge of calculating the M_Storage_Detail records. The m_storage_detail_check1 constraint is on the M_Storage_Detail table.
The M_UPDATE_INVENTORY db function is called from several places:
1. M_INVENTORYLINE_TRG trigger, each time an inventory line is created or updated (or deleted). Important: in case of updating, the M_UPDATE_INVENTORY is called two times.
2. M_INVENTORY_LISTUPDATE db function, related to the Update Quantities process
3. InventoryCountProcess java process, related to the Inventory Count process.
Each time the M_UPDATE_INVENTORY function is called it searches for an existing storage detail:
- If found --> updates it. If after updating the qtyonhand = 0, it deletes it
- If not found --> creates a new record (and it is not deleted).
So, in our scenario we have a storage detail with qtyonhand = 0.
When we insert the inventory line, the M_INVENTORYLINE_TRG launches the M_UPDATE_INVENTORY function. The storage detail is found, so it's updated and then deleted.
Then the Update Quantity is executed. This updates the inventory line (to calculate the book qty), then the M_INVENTORYLINE_TRG trigger is executed on update, which executes two times the M_UPDATE_INVENTORY db function:
- the first time the storage detail is not found, so the record is inserted.
- The second time the storage detail is found, so the record is updated and removed.
Now the Process Inventory is launched, and therefore the M_UPDATE_INVENTORY db function is executed again. The storage detail is not found, so it tries to insert it.
It fails because the parameters that the InventoryCountProcess java process don't respect the m_storage_detail_check1 check constraint.
So the bug is actually that the InventoryCountProcess java process is not sending the right values. |
|