Skip to main content
Solved

Inventory summary shows negative receipts for fully posted transactions


Forum|alt.badge.img+4

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.

 

 

 

 

 

Best answer by Brian Stevens

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
Did this topic help you find an answer to your question?

3 replies

Forum|alt.badge.img+8
  • Semi-Pro I
  • 716 replies
  • July 5, 2021

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

 


Forum|alt.badge.img+4
  • Author
  • Pro III
  • 135 replies
  • Answer
  • July 26, 2021

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.


Forum|alt.badge.img+8
  • Semi-Pro I
  • 716 replies
  • July 26, 2021

Hi @brianstevens Thanks for the update


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings