Error warehouse does not allow negative inventory

[KiênDX - Việt Nam]

Hello community,

I have 1 problem when use warehouse check Disallow Negative Inventory.

Product use Attribute Set.

With 1 ASI in m_storageonhand have multi datematerialpolicy.

Example:

Product A In Onhand

 ASI_ID = 1000001, Qtyonhand = 1, datematerialpolicy = 01-01-2021

 ASI_ID = 1000001, Qtyonhand = 1, datematerialpolicy = 10-01-2021

create Shipment Customer: Product A with ASI_ID = 1000001, MovementQty = 2.

When complete Shipment Customer

Error: warehouse does not allow negative inventory for Product Product A, ASI = 1000001 ...

I check code MInOut.java (method CompleteIt): Code get only 1 record StorageOnhand.

I think it bug. I hope Idempiere will fix it soon

[reua...@gmail.com]

Are these items stored at different locators?
Maybe You are using user defined locator types?

If so then check the locator type settings. 

Warehouse > Locator > then click the Locator Type label.
Make sure that Available for Reservation and Available for Shipping is checked. Otherwise you can not ship from the locator.

 

[KiênDX - Việt Nam]

Dear,

Are these items stored at different locators?

No, I only use 1 Warehouse 1 Locator default.

Maybe You are using user defined locator types?

I do not config Locator Type in Locator.

Sorry, I change some info

With 1 ASI in m_storageonhand have multi datematerialpolicy.

Example:

Product A In Onhand

 ASI_ID = 1000001, Qtyonhand = 1, datematerialpolicy = 01-01-2021, m_locator_id =  1000001  , ad_org_id = 1000001

 ASI_ID = 1000001, Qtyonhand = 1, datematerialpolicy = 10-01-2021, m_locator_id =  1000001  , ad_org_id = 1000001

In class MInOut.java. I see method CompleteIt

                                          Timestamp dateMPolicy= null;

MStorageOnHand[] storages = MStorageOnHand.getWarehouse(getCtx(), 0,

sLine.getM_Product_ID(), sLine.getM_AttributeSetInstance_ID(), null,

MClient.MMPOLICY_FiFo.equals(product.getMMPolicy()), false,

sLine.getM_Locator_ID(), get_TrxName());

for (MStorageOnHand storage : storages) {

if (storage.getQtyOnHand().compareTo(sLine.getMovementQty()) >= 0) {

dateMPolicy = storage.getDateMaterialPolicy();

break;

}

}

if (dateMPolicy == null && storages.length > 0)

dateMPolicy = storages[0].getDateMaterialPolicy();

if(dateMPolicy==null)

dateMPolicy = getMovementDate();

// Fallback: Update Storage - see also VMatch.createMatchRecord

if (!MStorageOnHand.add(getCtx(), getM_Warehouse_ID(),

sLine.getM_Locator_ID(),

sLine.getM_Product_ID(),

sLine.getM_AttributeSetInstance_ID(),

Qty,dateMPolicy,get_TrxName()))

{

String lastError = CLogger.retrieveErrorString("");

m_processMsg = "Cannot correct Inventory OnHand [" + product.getValue() + "] - " + lastError;

return DocAction.STATUS_Invalid;

}

So with datematerialpolicy only find 1 record in storage onhand. Next , I see method addQtyOnHand in MStorageOnHand.java

public void addQtyOnHand(BigDecimal addition) {

final String sql = "UPDATE M_StorageOnHand SET QtyOnHand=QtyOnHand+?, Updated=SYSDATE, UpdatedBy=? " +

"WHERE M_Product_ID=? AND M_Locator_ID=? AND M_AttributeSetInstance_ID=? AND DateMaterialPolicy=?";

DB.executeUpdateEx(sql, 

new Object[] {addition, Env.getAD_User_ID(Env.getCtx()), getM_Product_ID(), getM_Locator_ID(), getM_AttributeSetInstance_ID(), getDateMaterialPolicy()}, 

get_TrxName());

load(get_TrxName());

if (getQtyOnHand().signum() == -1) {

MWarehouse wh = MWarehouse.get(Env.getCtx(), getM_Warehouse_ID());

if (wh.isDisallowNegativeInv()) {

throw new NegativeInventoryDisallowedException(getCtx(), getM_Warehouse_ID(), getM_Product_ID(), getM_AttributeSetInstance_ID(), getM_Locator_ID(),

getQtyOnHand().subtract(addition), addition.negate());

}

}

}

This is cause error when shipment receipt with qty = 2 but only get 1 record storage onhand with qtyonhand = 1.

Vào lúc 14:03:36 UTC+7 ngày Thứ Năm, 15 tháng 4, 2021, reua...@gmail.com đã viết:

[Carlos Antonio Ruiz Gomez]

Can you please provide steps to reproduce that can be followed in one of the test servers?

Regards,

Carlos Ruiz

Am 15.04.21 um 12:48 schrieb KiênDX - Việt Nam:

KiênDX - Việt Nam schrieb am Donnerstag, 15. April 2021 um 05:31:14 UTC+2:

Hello community,

I have 1 problem when use warehouse check Disallow Negative Inventory.

Product use Attribute Set.

With 1 ASI in m_storageonhand have multi datematerialpolicy.

Example:

Product A In Onhand

 ASI_ID = 1000001, Qtyonhand = 1, datematerialpolicy = 01-01-2021

 ASI_ID = 1000001, Qtyonhand = 1, datematerialpolicy = 10-01-2021

create Shipment Customer: Product A with ASI_ID = 1000001, MovementQty = 2.

When complete Shipment Customer

Error: warehouse does not allow negative inventory for Product Product A, ASI = 1000001 ...

I check code MInOut.java (method CompleteIt): Code get only 1 record StorageOnhand.

I think it bug. I hope Idempiere will fix it soon

--

[KiênDX - Việt Nam]

I test on https://demo.globalqss.com/webui/

This is my steps:

1. Create Product Category T-Shirt Category, Accounting - use Costing method: Average PO, Costing level: Batch/Lot

2. Create Attribute Set: Shirt Polo (Check Lot, Check Mandatory Lot, Madatory type: When shipping). Attribute Use: Color and Size

Tab Exclude: Add C_OrderLine. Sales Transaction = Y

                       Add C_OrderLine. Sales Transaction = N 

3. Create Product: T-Shirt Polo. Set Attribute Set: Shirt Polo. (Check Stocked, Check Purchased, Check Sold)

Tab: Price: Add Purchase 2003: Price = 100

Add Standard 2003: Price = 200

4. Create Warehouse: HQ - ST 01 and Locator HQ - ST 01. Oraganization : HQ. Check Disallow Negative Inventory.

5. Create PO, Warehouse HQ- Warehouse, Price List: Standard, DateOrder: 01-04-2021. Document No = 800020. PO Line: Choose Product T-Shirt Polo, Qty = 2.

Complete PO. It's OK

6. Create Material Receipt From PO 800020.MovementDate: 01-04-2021. In Receipt Line: Create new Attribute Set Instance (Color: blue, Size: Medium, Lotno: 01042021) M_AttributeSetInstance_ID=1000014

Complete Material Receipt. It's OK.

7. Create Inventory Move: MovementDate: 01-04-2021. From HQ - Warehouse To HQ - ST 01. DocumentNo = 10000002

Product: T-Shirt Polo, Qty = 2.  With M_AttributeSetInstance_ID=1000014. 

Complete Inventory. It's OK.

8. Create Sales Order With DocumentType = POS Order, Warehouse: HQ - ST 01. DocumentNo = 80013

Sales OrderLine:  Product: T-Shirt Polo, Qty = 1. M_AttributeSetInstance_ID=1000014. Date = 01-04-2021.

Complete SO. It's OK. Shipment created. It's OK.

Then 8 steps: HQ Warehouse: QtyOnhand = 0, HQ - ST 01: QtyOnhand = 1.

9. Create PO. Warehouse: HQ Warehouse. DateOrdered: 10-04-2021. DocumentNo = 800021

Choose T-Shirt Polo, Qty = 1. AttributeSetInstance: select record exists. Choose M_AttributeSetInstance_ID=1000014.

Complete PO. It's OK. Then Create Material Receitp From PO. (DocumentNo= 10000538).

10. Create Inventory Move: MovementDate: 10-04-2021. From HQ - Warehouse To HQ - ST 01. DocumentNo = 10000003

Product: T-Shirt Polo, Qty = 1.  With M_AttributeSetInstance_ID=1000014. 

Complete Inventory Move. It's OK

Then 10 steps: HQ Warehouse: QtyOnhand = 0, HQ - ST 01: QtyOnhand = 2.

11. Create SO. DocumentNo = 50003. DateOrder: 10-04-2021. DocumentType = Standard Order. Warehouse = HQ - ST 01.

Create SO Line: choose Product T-Shirt Polo, Qty = 2. Choose M_AttributeSetInstance_ID=1000014. Complete SO. It's OK.

12. Create Shipment Customer. MovementDate: 10-04-2021. Choose Order: 50003. (DocumentNo = 500002)

Choose SO Line. 

Then Complete Shipment. Error

500002: (#1) - Line No 10: The HQ - ST 01 warehouse does not allow negative inventory for Product = T-Shirt Polo_T-Shirt Polo, ASI = Blue_Medium_«01042021», Locator = HQ - ST 01 (Shortage of 1)

Vào lúc 16:12:44 UTC+7 ngày Thứ Sáu, 16 tháng 4, 2021, car...@gmail.com đã viết:

[Carlos Antonio Ruiz Gomez]

Thanks, issue reproducible:

https://idempiere.atlassian.net/browse/IDEMPIERE-4768

Easy workaround:

- Create two lines with Qty=1

Not so easy workaround:

- Create shipment line without ASI, and fill the attributes tab

Regards,

Carlos Ruiz

Am 19.04.21 um 05:51 schrieb KiênDX - Việt Nam:

[reua...@gmail.com]

To me this looks like a legitimate issue that can occur when material is added  to and removed from an ASI over time. A complete fix would however require some diligent work because it has to take the current selection of costing method into account. (Please correct me if I'm wrong.)
Thanks KiênDX for putting it onto my radar. I would expect that we too will run into that problem sooner or later. 

Andreas

[Carlos Antonio Ruiz Gomez]

Hi Andreas, the issue is not related to costing method.

I created a draft patch that must solve the problem, but still I have things to review there.

Regards,

Carlos Ruiz

Am 19.04.21 um 17:01 schrieb reua...@gmail.com:

[KiênDX - Việt Nam]

Thanks Carlos Ruiz,

I see field datematerialpolicy in m_storageonhand only serve insert into LineMA. Example: In MInout.java method checkMaterialPolicy then needSave alway = false therefore LineMA not saved. Follow me, When user need ship for customer faster, need to reduce manipulation.

Now, I change code in MStorageOnhand.java method getWarehouse

               /* kiendx rem //  only 1 row in m_storageonhand

if (positiveOnly)

{

sql += " AND s.QtyOnHand > 0 ";

}

else

{

sql += " AND s.QtyOnHand <> 0 ";

}

*/

// kiendx

sql += " AND (s.QtyOnHand <> 0 or s.QtyOnHand = 0)";

Solution I processing, only 1 row (ad_client_id, ad_org_id, m_product_id, m_locator_id, m_attributesetinstance_id) in m_storageonhand not care datematerialpolicy. You can consult.

Vào lúc 02:59:29 UTC+7 ngày Thứ Ba, 20 tháng 4, 2021, car...@gmail.com đã viết:

[Carlos Antonio Ruiz Gomez]

Hi,pull request is complete

https://github.com/idempiere/idempiere/pull/660

Peer review, tests and feedback are welcome.

Regards,

Carlos Ruiz

Am 20.04.21 um 05:20 schrieb KiênDX - Việt Nam: