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.
