How to correct committed quantities that don't add up.

IMPORTANT: Committed quantities will not reconcile if there are open orders on file that need to be canceled. To cancel an order use F11 Recall in POS to view list of open workorders, layaways, and backorders. Any old unfulfilled orders should be canceled by recalling the order to POS, remove line item entries, then tender and save changes. If a deposit was taken cashier will be prompted to retain deposit (the default = 0.00) or enter amount to refund to customer. If you wish to retain deposit under the customer account, refund to Cash then immediately apply refunded amount as payment on account (Shift-F4, select account, enter amount and tender as Cash). Customer can apply the amount as credit on a future sale. 

If committed quantities do not reconcile with current sales orders, use the script below to update the committed items to their correct value based on the open layways, workorders and backorders.

1. Run Store Operations Administrator, File, Connect, enter SQL password, then select Query, New.
2. Cut and paste the entire script below into the query box.  

UPDATE Item SET QuantityCommitted = (CASE WHEN A.QtyCommitted IS NULL THEN 0 ELSE A.QtyCommitted END) + (CASE WHEN B.QtyCommitted IS NULL THEN 0 ELSE B.QtyCommitted END) FROM Item LEFT JOIN 

(SELECT ItemID, SUM(QuantityOnOrder) AS QtyCommitted FROM OrderEntry LEFT JOIN [Order] ON OrderEntry.OrderID = [Order].ID WHERE [Order].Type IN (2,4,5) GROUP BY ItemID) A ON Item.ID = A.ItemID 

LEFT JOIN 

(SELECT ItemID, SUM(QuantityReceived) AS QtyCommitted FROM PurchaseOrderEntry LEFT JOIN PurchaseOrder ON PurchaseOrderEntry.PurchaseOrderID = PurchaseOrder.ID WHERE POType = 3 AND Status <> 2 GROUP BY ItemID) B ON Item.ID = B.ItemID WHERE Item.ItemType <> 7 

3. Press F5 to run script (or click green arrow in toolbar).
4. Close Store Operations Administrator.

IMPORTANT: If you are an HQ user (multi-store), you should next reconcile your store data with HQ by running WS 501 for the affected store location, wait for the worksheet to be processed, then run HQ Manager Inventory Wizard Task 190 to reconcile the store level data with HQ.

If committed quantity still appears to be off, run Store Operations Manager, Reports, Custom, Order (Detailed) Report, filter by the problem Item Lookup Code, Status = Open. This shows any open sales order for that item (layaway, workorder, backorder). In an HQ environment also check for any missing Transfers In/Out or uncommitted transfers.

Note: A negative committed quantity typically indicates a manual adjustment was made under Item Properties with an open order on file then the order was later fulfilled. You can use script above to resolve the discrepancy or manually adjust committed quantity.