WITH total_stock AS (
SELECT PIN, SUM(Quantity) AS TotalStock
FROM products.stock_archive
WHERE TimeReceived >= CURDATE()
GROUP BY PIN
),

unReceivedLines AS (
SELECT o.OrderID, od.OrderDetailID, od.QtyOnHold, od.ProductCode
FROM custopayted.orders o
JOIN custopayted.orderdetails od ON o.OrderID = od.OrderID
WHERE o.OrderStatus = 'Processing'
AND od.ProductNote NOT LIKE '%|%'
AND od.ProductNote NOT LIKE '% MDS%'
AND od.ProductNote NOT LIKE '% DS%'
AND od.QtyShipped = 0 
AND od.QtyOnHold > 0
)

SELECT rl.OrderID, rl.OrderDetailID, cm.PIN, cm.Productcode, rl.QtyOnHold
FROM unReceivedLines rl
JOIN products.client_map cm ON cm.Productcode = rl.Productcode AND cm.Client = 'Volusion'
JOIN total_stock ts ON ts.PIN = cm.PIN
WHERE rl.QtyOnHold <= ts.TotalStock