-- 09/19/2024 by Rhys Houghton
WITH purchase_orders AS (
SELECT o.OrderID, od.ProductCode, TRIM(SUBSTRING_INDEX(od.ProductNote, '|',1)) AS TrimmedProductNote, od.QtyOnHold, od.QtyShipped
FROM custopayted.orderdetails od
JOIN custopayted.orders o ON o.OrderID = od.OrderID AND o.OrderStatus = 'Processing'
-- Note: if stock is pulled twice in one day, then the lines from the previous pull will show up in the second pull
JOIN products.receiving_archive ra ON ra.OrderDetailID = od.OrderDetailID AND ra.TimeReceived >= CURDATE()
-- Ensure that there is a valid original productnote before the applied stock note (that is not null or blank)
WHERE od.ProductNote LIKE '% | STOCK%' 
AND od.ProductNote NOT LIKE 'null | STOCK%' 
AND od.ProductNote NOT LIKE ' | STOCK%'
AND od.ProductNote NOT LIKE '| STOCK%'
AND od.ProductNote NOT LIKE 'Processing % | STOCK%'
AND od.ProductNote NOT LIKE 'PreProcessing % | STOCK%'
)

SELECT

IF(ProductNote LIKE '%|%', TRIM(SUBSTRING_INDEX(ProductNote, '|',1)), ProductNote) AS POProductNote, 
ProductCode,
-- The quantity pulled is the quantity we want to cancel
SUM(QtyShipped) AS QtyToCancel

FROM custopayted.orderdetails
-- Filter by the orders and product codes we applied stock notes to today that were pulled
WHERE (OrderID, ProductCode) IN (
	SELECT OrderID, ProductCode
    FROM purchase_orders)
-- Filter by the product notes we applied stock notes to today that were pulled
AND IF(ProductNote LIKE '%|%', TRIM(SUBSTRING_INDEX(ProductNote, '|',1)), ProductNote) IN (SELECT TrimmedProductNote
																						   FROM purchase_orders)

-- Group by the ProductNote and the Productcode with the sum of the quantity shipped being greater than 0
GROUP BY POProductNote, ProductCode
HAVING SUM(QtyShipped) > 0