SELECT
	od.orderid,
	od.orderdetailid,
	DATE(o.orderdate),
	od.quantity,
	od.qtyonhold,
	o.orderstatus,
	od.productnote,
    cm.productcode,
	p.vertical,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'DWB' THEN _DWB.stock ELSE 0 END), 0) AS DWB_TotalStock,
	CASE WHEN vm.VendorCode = 'DWB' THEN _DWB.MFG_SKU ELSE NULL END AS DWB_SKU,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'HUS' THEN _HUS.stock ELSE 0 END), 0) AS HUS_TotalStock,
	CASE WHEN vm.VendorCode = 'HUS' THEN _HUS.MFG_SKU ELSE NULL END AS HUS_SKU,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'MAK' THEN _MAK.stock ELSE 0 END), 0) AS MAK_TotalStock,
	CASE WHEN vm.VendorCode = 'MAK' THEN _MAK.MFG_SKU ELSE NULL END AS MAK_SKU,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'MAR' THEN _MAR.stock ELSE 0 END), 0) AS MAR_TotalStock,
	CASE WHEN vm.VendorCode = 'MAR' THEN _MAR.MFG_SKU ELSE NULL END AS MAR_SKU,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'GAR' THEN _GAR.stock ELSE 0 END), 0) AS GAR_TotalStock,
	CASE WHEN vm.VendorCode = 'GAR' THEN _GAR.MFG_SKU ELSE NULL END AS GAR_SKU,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'MED' THEN _MED.stock ELSE 0 END), 0) AS MED_TotalStock,
	CASE WHEN vm.VendorCode = 'MED' THEN _MED.MFG_SKU ELSE NULL END AS MED_SKU,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'PWD' THEN _PWD.stock ELSE 0 END), 0) AS PWD_TotalStock,
	CASE WHEN vm.VendorCode = 'PWD' THEN _PWD.MFG_SKU ELSE NULL END AS PWD_SKU,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'ESC' THEN _ESC.stock ELSE 0 END), 0) AS ESC_TotalStock,
	CASE WHEN vm.VendorCode = 'ESC' THEN _ESC.MFG_SKU ELSE NULL END AS ESC_SKU,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'BIS' THEN _BIS.stock ELSE 0 END), 0) AS BIS_TotalStock,
	CASE WHEN vm.VendorCode = 'BIS' THEN _BIS.MFG_SKU ELSE NULL END AS BIS_SKU,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'SCV' THEN _SCV.stock ELSE 0 END), 0) AS SCV_TotalStock,
	CASE WHEN vm.VendorCode = 'SCV' THEN _SCV.MFG_SKU ELSE NULL END AS SCV_SKU,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'TAC' THEN _TAC.stock ELSE 0 END), 0) AS TAC_TotalStock,
	CASE WHEN vm.VendorCode = 'TAC' THEN _TAC.MFG_SKU ELSE NULL END AS TAC_SKU,
	COALESCE(SUM(CASE WHEN vm.vendorcode = 'MEY' THEN _MEY.stock ELSE 0 END), 0) AS MEY_TotalStock,
	CASE WHEN vm.VendorCode = 'MEY' THEN _MEY.MFG_SKU ELSE NULL END AS MEY_SKU

FROM
    custopayted.orders o
JOIN
    custopayted.orderdetails od ON o.orderid = od.orderid
JOIN
    products.client_map cm ON od.productcode = cm.productcode AND cm.Client = 'Volusion'
JOIN
	products.pins p ON cm.PIN = p.PIN
JOIN
    products.vendor_map vm ON cm.PIN = vm.PIN AND cm.client = 'Volusion' AND cm.Parent = ''
LEFT JOIN
    products._BIS ON vm.MFG_SKU = _BIS.MFG_SKU
LEFT JOIN
    products._DWB ON vm.MFG_SKU = _DWB.MFG_SKU
LEFT JOIN
    products._ESC ON vm.MFG_SKU = _ESC.MFG_SKU
LEFT JOIN
    products._HUS ON vm.MFG_SKU = _HUS.MFG_SKU
LEFT JOIN
    products._MAK ON vm.MFG_SKU = _MAK.MFG_SKU
LEFT JOIN
    products._MAR ON vm.MFG_SKU = _MAR.MFG_SKU
LEFT JOIN
    products._MED ON vm.MFG_SKU = _MED.MFG_SKU
LEFT JOIN
    products._MEY ON vm.MFG_SKU = _MEY.MFG_SKU
LEFT JOIN
    products._PWD ON vm.MFG_SKU = _PWD.MFG_SKU
LEFT JOIN
    products._SCV ON vm.MFG_SKU = _SCV.MFG_SKU
LEFT JOIN
    products._TAC ON vm.MFG_SKU = _TAC.MFG_SKU
LEFT JOIN
    products._GAR ON vm.MFG_SKU = _GAR.MFG_SKU
	
WHERE vm.vendorcode IN ('BIS','DWB','ESC','HUS','MAK','MAR','MED','MEY','PWD','SCV','TAC','GAR')
AND od.qtyonhold > 0
AND o.orderstatus = 'processing'
AND (od.productnote LIKE '%SPC%' OR od.productnote LIKE '%ETA%')
AND o.orderdate < CURRENT_DATE - INTERVAL 7 DAY
AND o.ordernotes NOT LIKE '%BACKREV%'
GROUP BY od.orderdetailid, vm.VendorCode

HAVING
    (p.vertical = 'vacuum' AND 
        COALESCE(SUM(CASE WHEN vm.vendorcode IN ('ESC', 'SCV', 'BIS', 'TAC') THEN vm.stock ELSE 0 END), 0) > od.quantity) OR
    (p.vertical = 'yard' AND 
        COALESCE(SUM(CASE WHEN vm.vendorcode IN ('GAR', 'MED', 'MAR', 'HUS', 'PWD') THEN vm.stock ELSE 0 END), 0) > od.quantity) OR
    (p.vertical = 'tool' AND 
        COALESCE(SUM(CASE WHEN vm.vendorcode IN ('GAR', 'DWB', 'MAK','MAR') THEN vm.stock ELSE 0 END), 0) > od.quantity)