Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0050776
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 04. Warehouse managementmajoralways2022-11-08 14:392022-11-15 18:09
ReporterAtulOpenbravoView Statuspublic 
Assigned ToTriage Omni WMS 
PrioritynormalResolutionfixedFixed in VersionPR23Q1
StatusclosedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Merge Request Statusapproved
Review Assigned To
OBNetwork customerGold
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0050776: Error in InventoryCountProcess - constraint "m_storage_detail_check1" violation

DescriptionWhen updating Inventory Count using InventoryCountProcess, DB constraint
m_storage_detail_check1 is not satisfied when m_product_uom_id IS NULL

qtyorderonhand IS NULL AND m_product_uom_id IS NULL AND preqtyorderonhand IS NULL

OR

qtyorderonhand IS NOT NULL AND m_product_uom_id IS NOT NULL AND preqtyorderonhand IS NOT NULL
Steps To Reproduce-
Proposed SolutionUse similar condition to set parameters to be sent to M_UPDATE_INVENTORY procedure i.e qtyorderonhand and preqtyorderonhand as in the parameter m_product_uom_id

invCountLine.getOrderUOM() != null ? BigDecimal.ZERO : null
TagsFASH
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0049551 closedmtaal Openbravo ERP No storage detail automatically removed when the product does not have attribute set 
depends on backport 0050910PR22Q4.1 closedAtulOpenbravo Openbravo ERP Error in InventoryCountProcess - constraint "m_storage_detail_check1" violation 
related to defect 0049991 closedprakashmurugesan88 Retail Modules Check Constraint(m_storage_detail_check1) violation when we try to integrate physical inventory by API for some products 
related to defect 0050039 closedAtulOpenbravo Retail Modules Check Constraint(m_storage_detail_check1) violation when we try to integrate physical inventory by API for some products 

-  Notes
(0143245)
hgbot (developer)
2022-11-08 15:31

 Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/754 [^]
(0143631)
vmromanos (viewer)
2022-11-15 17:41

 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.
(0143635)
hgbot (developer)
2022-11-15 18:08

 Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/754 [^]
(0143636)
hgbot (developer)
2022-11-15 18:08

 Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/openbravo [^]
Changeset: c9ca7f452ccce568f6ba11bc1bc0ff9857758c14
Author: Atul Gaware <atul.gaware@openbravo.com>
Date: 15-11-2022 16:50:48
URL: https://gitlab.com/openbravo/product/openbravo/-/commit/c9ca7f452ccce568f6ba11bc1bc0ff9857758c14 [^]

Fixes BUG-0050776: Error in InventoryCountProcess - constraint
"m_storage_detail_check1" violation

**To fulfill DB m_storage_detail_check1 when OrderUOM is null,
QtyOrderOnHand and PreQtyOrderOnHand should be also null when
updating the inventory

---
M src/org/openbravo/materialmgmt/InventoryCountProcess.java
---
(0143637)
vmromanos (viewer)
2022-11-15 18:09

 Note that the fix won't delete the existing storage detail with qtyonhand=0. I don't consider it a big deal because that is exactly as it was behaving before 0049551

- Issue History
Date Modified Username Field Change
2022-11-08 14:39 AtulOpenbravo New Issue
2022-11-08 14:39 AtulOpenbravo Assigned To => Triage Omni WMS
2022-11-08 14:39 AtulOpenbravo OBNetwork customer => No
2022-11-08 14:39 AtulOpenbravo Modules => Core
2022-11-08 14:39 AtulOpenbravo Triggers an Emergency Pack => No
2022-11-08 14:39 AtulOpenbravo Status new => scheduled
2022-11-08 15:31 hgbot Merge Request Status => open
2022-11-08 15:31 hgbot Note Added: 0143245
2022-11-08 15:39 rafaroda Relationship added related to 0049991
2022-11-08 15:39 rafaroda Relationship added related to 0050039
2022-11-08 15:49 rafaroda Tag Attached: FASH
2022-11-11 15:05 avicente OBNetwork customer No => Gold
2022-11-15 17:07 vmromanos Relationship added related to 0049551
2022-11-15 17:08 vmromanos Status scheduled => acknowledged
2022-11-15 17:09 vmromanos Status acknowledged => scheduled
2022-11-15 17:41 vmromanos Note Added: 0143631
2022-11-15 18:08 hgbot Merge Request Status open => approved
2022-11-15 18:08 hgbot Resolution open => fixed
2022-11-15 18:08 hgbot Status scheduled => closed
2022-11-15 18:08 hgbot Note Added: 0143635
2022-11-15 18:08 hgbot Fixed in Version => PR23Q1
2022-11-15 18:08 hgbot Note Added: 0143636
2022-11-15 18:09 vmromanos Note Added: 0143637

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker