Solved

Inventory summary shows negative receipts for fully posted transactions

  • 18 June 2021
  • 3 replies
  • 51 views

Userlevel 2
Badge +1

As the title says, we have 3 items that show Unassigned for the location and show negative Purchase Order Quantity, which upon drill down shows they are PO Receipts that are fully released all the way into the INTran record.  Our SysAdmin is on vacation, and I can’t find the root cause or solution.  The items are serialized.  Also worth noting, these are not for “real” purchase orders and therefore are never processed for AP Bills.

Looking for insight on where to look for the root cause or how to clear this up.

 

 

 

 

 

icon

Best answer by brianstevens 26 July 2021, 14:05

This issue was isolated to 3 incidents over a 6 week span and has not recurred for months.  We believe this to have been an unidentified bug from several months ago that appears to have been fixed.  The following SQL scripts were used to correct the perceived data corruption, 1 item/LotSerialNbr at a time.

 

DECLARE @CompanyID int, @InventoryID int, @LotSerialNbr nvarchar(50);  
SET @CompanyID = 4;
SET @InventoryID = 1401;
Set @LotSerialNbr = 'don'

Use AcumaticaDB;

/* View Records Before Update */

Select InventoryCD, Descr
From InventoryItem Where CompanyID = @CompanyID and InventoryID = @InventoryID;
Select CompanyID, InventoryID, SiteID, LocationID, LotSerialNbr, PlanDate, PlanID, PlanType, OrigPlanType, PlanQty, RefEntityType, CreatedByScreenID, CreatedDateTime, LotSerialNbr
From INItemPlan Where CompanyID = @CompanyID and InventoryID = @InventoryID;
Select CompanyID, InventoryID, SiteID, LocationID, QtyPOOrders, QtyPOReceipts
From INLocationStatus Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0);
Select CompanyID, InventoryID, SiteID, QtyPOOrders, QtyPOReceipts
From INSiteStatus Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0);
Select CompanyID, InventoryID, SiteID, LocationID, LotSerialNbr, QtyPOOrders, QtyPOReceipts
From INLotSerialStatus Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0) and LotSerialNbr = @LotSerialNbr;
Select CompanyID, InventoryID, LotSerialNbr, QtyOnHand, QtyAvail, QtyOnReceipt
From INItemLotSerial Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyAvail <> 0 or QtyOnReceipt <> 0) and LotSerialNbr = @LotSerialNbr;
Select CompanyID, InventoryID, ReceiptNbr, ReceiptDate, LotSerialNbr, OrigQty, QtyOnHand, LotSerialNbr
From INReceiptStatus Where CompanyID = @CompanyID and InventoryID = @InventoryID and LotSerialNbr = @LotSerialNbr;


/* Apply DB Updates after confirming proper selection - comment out until ready to execute! */

Update INLocationStatus Set QtyPOOrders = 0, QtyPOReceipts = 0 Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0);
Update INSiteStatus Set QtyPOOrders = 0, QtyPOReceipts = 0 Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0);
Update INLotSerialStatus Set QtyPOOrders = 0, QtyPOReceipts = 0 Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0) and LotSerialNbr = @LotSerialNbr;
Update INItemLotSerial Set QtyAvail = 0, QtyOnReceipt = 0 Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyAvail <> 0 or QtyOnReceipt <> 0) and LotSerialNbr = @LotSerialNbr;
Delete INItemPlan Where CompanyID = @CompanyID and InventoryID = @InventoryID;

Reported problem confirmed resolved, and no subsequent adverse affects reported.

View original

3 replies

Userlevel 3
Badge

Hi @brianstevens Thanks for the update

Userlevel 2
Badge +1

This issue was isolated to 3 incidents over a 6 week span and has not recurred for months.  We believe this to have been an unidentified bug from several months ago that appears to have been fixed.  The following SQL scripts were used to correct the perceived data corruption, 1 item/LotSerialNbr at a time.

 

DECLARE @CompanyID int, @InventoryID int, @LotSerialNbr nvarchar(50);  
SET @CompanyID = 4;
SET @InventoryID = 1401;
Set @LotSerialNbr = 'don'

Use AcumaticaDB;

/* View Records Before Update */

Select InventoryCD, Descr
From InventoryItem Where CompanyID = @CompanyID and InventoryID = @InventoryID;
Select CompanyID, InventoryID, SiteID, LocationID, LotSerialNbr, PlanDate, PlanID, PlanType, OrigPlanType, PlanQty, RefEntityType, CreatedByScreenID, CreatedDateTime, LotSerialNbr
From INItemPlan Where CompanyID = @CompanyID and InventoryID = @InventoryID;
Select CompanyID, InventoryID, SiteID, LocationID, QtyPOOrders, QtyPOReceipts
From INLocationStatus Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0);
Select CompanyID, InventoryID, SiteID, QtyPOOrders, QtyPOReceipts
From INSiteStatus Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0);
Select CompanyID, InventoryID, SiteID, LocationID, LotSerialNbr, QtyPOOrders, QtyPOReceipts
From INLotSerialStatus Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0) and LotSerialNbr = @LotSerialNbr;
Select CompanyID, InventoryID, LotSerialNbr, QtyOnHand, QtyAvail, QtyOnReceipt
From INItemLotSerial Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyAvail <> 0 or QtyOnReceipt <> 0) and LotSerialNbr = @LotSerialNbr;
Select CompanyID, InventoryID, ReceiptNbr, ReceiptDate, LotSerialNbr, OrigQty, QtyOnHand, LotSerialNbr
From INReceiptStatus Where CompanyID = @CompanyID and InventoryID = @InventoryID and LotSerialNbr = @LotSerialNbr;


/* Apply DB Updates after confirming proper selection - comment out until ready to execute! */

Update INLocationStatus Set QtyPOOrders = 0, QtyPOReceipts = 0 Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0);
Update INSiteStatus Set QtyPOOrders = 0, QtyPOReceipts = 0 Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0);
Update INLotSerialStatus Set QtyPOOrders = 0, QtyPOReceipts = 0 Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyPOReceipts <> 0 or QtyPOOrders <> 0) and LotSerialNbr = @LotSerialNbr;
Update INItemLotSerial Set QtyAvail = 0, QtyOnReceipt = 0 Where CompanyID = @CompanyID and InventoryID = @InventoryID and (QtyAvail <> 0 or QtyOnReceipt <> 0) and LotSerialNbr = @LotSerialNbr;
Delete INItemPlan Where CompanyID = @CompanyID and InventoryID = @InventoryID;

Reported problem confirmed resolved, and no subsequent adverse affects reported.

Userlevel 3
Badge

Hi @brianstevens Please try running the VALIDATE INVENTORY process for the item, and verify if the issue is resolved. If it is not resolved, please verify the “Know Issues” list for your Acumatica Version to verify if this is a known issue.

Thanks

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved