SELECT 
o.orderid,
od.orderdetailid,
o.orderdate,
o.orderstatus,
o.ordernotes,
od.productnote,
od.productcode,
vpw.Stock,
(vpw.Stock - CASE WHEN MAX(Already_Applied.Total_Unshipped) IS NULL THEN 0 ELSE MAX(Already_Applied.Total_Unshipped) END) AS Available_Inv
FROM
custopayted.orderdetails od
JOIN custopayted.orders o ON od.orderid = o.orderid
JOIN custopayted.payment_log pl ON o.orderid = pl.pay_orderid
JOIN products.pins p ON od.productcode = p.productcode
LEFT JOIN (
	SELECT p.PIN, SUM(od.quantity) AS Total_Unshipped
	FROM custopayted.orderdetails od
	JOIN custopayted.orders o ON od.orderid = o.orderid
	JOIN products.pins p ON od.productcode = p.ProductCode
	WHERE o.orderstatus = 'Processing'
	AND od.productnote LIKE '%STOCK%'
	AND od.quantity > 0
	AND od.qtyshipped = 0
	GROUP BY p.PIN
	) AS Already_Applied ON p.PIN = Already_Applied.PIN
LEFT JOIN (
	SELECT pw.MFG_SKU, pw.Stock
	FROM products.vendor_map vm
	JOIN products._VPW pw ON pw.MFG_SKU = vm.MFG_SKU AND vm.VendorCode = 'VPW'
) vpw ON vpw.MFG_SKU = p.PIN

WHERE
o.orderdate >= DATE_SUB(NOW(), INTERVAL 180 DAY)
AND od.productnote IN ('LTBO', 'FNLA')
AND pl.pay_result <> 'FAILED'
AND vpw.Stock > 0
AND o.orderstatus = 'cancelled'
AND o.ordernotes NOT LIKE '%BACK IN STOCK%'
GROUP BY od.orderdetailid
HAVING Available_Inv > 0
ORDER BY o.orderdate DESC;