When a stock take result will cause a Sales Order’s Ship Qty to go to negative or zero – the Stock Take Confirmation process will halt – until all the relevant Sales Order detail lines are de-allocated (‘available’). Syspro’s standard report shows a list of StockCodes – so one would need to find the relating Sales Order(s) (via Inventory Enquiry) – and de-allocate accordingly. Quite a lengthy process!
But here’s a SQL query that will produce a (crude) list of Sales Order numbers that are preventing the Stock Take Confirmation to complete:
SELECT DISTINCT h.SalesOrder, h.ShipAddress1, d.MWarehouse, d.MStockCode, d.MStockDes, d.MOrderQty, d.MShipQty FROM SorDetail d, SorMaster h WHERE h.CancelledFlag <> 'Y' AND h.OrderStatus <> '9' AND h.OrderStatus <> '*' AND h.OrderStatus <> '\' AND d.SalesOrderDetStat <> 'C' AND d.SalesOrder = h.SalesOrder AND h.DocumentType <> 'C' AND d.LineType <> '6' AND (d.MShipQty + d.MBackOrderQty) > 0 AND CONCAT(d.MWarehouse, d.MStockCode) IN ( SELECT CONCAT(w.Warehouse, w.StockCode) FROM InvWarehouse w, InvStockTake s WHERE w.Warehouse = s.Warehouse AND w.StockCode = s.StockCode AND (s.OrigQtyOnHand - s.QtyCaptured) > 0 -- Only interested in REDUCTIONS AND ((w.QtyOnHand-(w.QtyAllocated-w.QtyOnBackOrder)) - (s.OrigQtyOnHand - s.QtyCaptured)) < 0 -- Only where ShipQtys will be affected ) ORDER BY h.SalesOrder
This might speed this up!
PS: It might even speed things up more – if you run this BEFORE the stock take – and reduce allocations where more than 90% of the stock holding is allocated to Sales Orders.