SELECT
    od.orderid,
    od.orderdetailid,
    DATE(o.orderdate),
    od.quantity,
    od.qtyonhold,
    o.orderstatus,
    od.productnote,
    p.productcode,
    p.vertical,
    COALESCE(SUM(CASE WHEN vm.vendorcode = 'GAR' THEN _GAR.stock ELSE 0 END), 0) AS GAR_TotalStock,
    MAX(CASE WHEN vm.VendorCode = 'GAR' THEN vm.cost ELSE NULL END) AS GARVendorCost,
    MAX(CASE WHEN vm.vendorcode = 'GAR' THEN _GAR.MFG_SKU ELSE NULL END) AS GAR_SKU,
    COALESCE(SUM(CASE WHEN vm.vendorcode = 'DWB' THEN _DWB.stock ELSE 0 END), 0) AS DWB_TotalStock,
    MAX(CASE WHEN vm.VendorCode = 'DWB' THEN vm.cost ELSE NULL END) AS DWBVendorCost,
    MAX(CASE WHEN vm.vendorcode = 'DWB' THEN _DWB.MFG_SKU ELSE NULL END) AS DWB_SKU,
    COALESCE(SUM(CASE WHEN vm.vendorcode = 'MAK' THEN _MAK.stock ELSE 0 END), 0) AS MAK_TotalStock,
    MAX(CASE WHEN vm.VendorCode = 'MAK' THEN vm.cost ELSE NULL END) AS MAKVendorCost,
    MAX(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,
    MAX(CASE WHEN vm.VendorCode = 'MAR' THEN vm.cost ELSE NULL END) AS MARVendorCost,
    MAX(CASE WHEN vm.vendorcode = 'MAR' THEN _MAR.MFG_SKU ELSE NULL END) AS MAR_SKU
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._GAR ON vm.MFG_SKU = _GAR.MFG_SKU
LEFT JOIN
    products._DWB ON vm.MFG_SKU = _DWB.MFG_SKU
LEFT JOIN
    products._MAK ON vm.MFG_SKU = _MAK.MFG_SKU
LEFT JOIN
    products._MAR ON vm.MFG_SKU = _MAR.MFG_SKU
WHERE
    vm.vendorcode IN ('GAR', 'DWB', 'MAK', 'MAR')
    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%'
    AND p.vertical = 'tool'
GROUP BY
    od.orderdetailid, vm.VendorCode
HAVING
    (COALESCE(SUM(_GAR.stock), 0) +
     COALESCE(SUM(_DWB.stock), 0) +
     COALESCE(SUM(_MAK.stock), 0) +
     COALESCE(SUM(_MAR.stock), 0)) > od.quantity;