SELECT
	od.orderdetailid,
	od.orderid,
	DATE(o.orderdate),
	od.quantity,
	od.qtyonhold,
    COALESCE(SUM(CASE WHEN vm.vendorcode = 'DEY' THEN d.MI + d.TX + d.WI_Milwaukee + d.CO + d.KS + d.NE + d.ND + d.IA_DesMoines + d.MN_Duluth + d.WI_GreenBay + d.IA_Waterloo + d.SD + d.MN_VadnaisHeights ELSE 0 END), 0) AS DEY_TotalStock,
	o.orderstatus,
	od.productnote,
    p.productcode
FROM
    custopayted.orders o
JOIN
    custopayted.orderdetails od ON o.orderid = od.orderid
JOIN
    products.pins p ON od.productcode = p.productcode
JOIN
    products.vendor_map vm ON p.productcode = vm.productcode
LEFT JOIN
    products._DEY d ON vm.MFG_SKU = d.MFG_SKU
WHERE vm.vendorcode = 'DEY'
AND od.qtyonhold > 0
AND o.orderstatus = 'processing'
AND (od.productnote LIKE '%SPC%' OR od.productnote LIKE '%ETA%' OR od.productnote LIKE '%NOO%' OR od.productnote LIKE '')
AND o.orderdate < CURRENT_DATE - INTERVAL 7 DAY
AND o.ordernotes NOT LIKE '%BACKREV%'
GROUP BY
    od.orderdetailid
HAVING 
    COALESCE(SUM(CASE WHEN vm.vendorcode = 'DEY' THEN d.MI + d.TX + d.WI_Milwaukee + d.CO + d.KS + d.NE + d.ND + d.IA_DesMoines + d.MN_Duluth + d.WI_GreenBay + d.IA_Waterloo + d.SD + d.MN_VadnaisHeights ELSE 0 END), 0) > 0