Syspro Stock Take System – find Sales Orders that will result in a negative ShipQty

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:

 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.