Delete inventory transactions with zero quantity

We have a cleanup routine in Dynamics 365 Finance and Dynamics 365 Supply Chain Management that can help you delete closed and unused on-hand inventory transactions. Closed transactions contain a value of 0 (zero) for all quantities and cost values, and they are marked as closed. By deleting these transactions, you can help improve the performance of queries for on-hand inventory.

The cleanup routine can be found on the following path: Inventory management > Periodic > Clean up > On-hand entries cleanup.

But sometimes, On AX, we will get a problem related to the performance of the system once the user runs the On-hand entries cleanup job, To resolve this case, you can try to run it by T-SQL or SQL script. 

To delete inventory transactions with zero quantity, you can use the below script:

The scripts you can copy and try it:

-- Delete inventory transactions with zero quantity

DELETE FROM InventTrans WHERE Qty == 0;

 

-- Delete inventory sums with zero quantity

DELETE FROM InventSum WHERE PhysInventQty == 0;

 

-- Delete obsolete inventory on-hand records

DELETE FROM InventOnHand WHERE ExpireDate < GETDATE();

 

-- Update inventory transactions to reflect current on-hand quantities

UPDATE t

SET t.Qty = oh.PhysicalQty

FROM InventTrans t

JOIN InventOnHand oh ON t.ItemId = oh.ItemId AND t.InventDimId = oh.InventDimId;

 

-- Update inventory sums to reflect current on-hand quantities

UPDATE s

SET s.PhysInventQty = oh.PhysicalQty

FROM InventSum s

JOIN InventOnHand oh ON s.ItemId = oh.ItemId AND s.InventDimId = oh.InventDimId;