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 = 'MED' THEN _MED.stock ELSE 0 END), 0) AS MED_TotalStock,
MAX(CASE WHEN vm.VendorCode = 'MED' THEN vm.cost ELSE NULL END) AS MEDVendorCost,
    MAX(CASE WHEN vm.vendorcode = 'MED' THEN _MED.MFG_SKU ELSE NULL END) AS MED_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,
    COALESCE(SUM(CASE WHEN vm.vendorcode = 'HUS' THEN _HUS.stock ELSE 0 END), 0) AS HUS_TotalStock,
MAX(CASE WHEN vm.VendorCode = 'HUS' THEN vm.cost ELSE NULL END) AS HUSVendorCost,
    MAX(CASE WHEN vm.vendorcode = 'HUS' THEN _HUS.MFG_SKU ELSE NULL END) AS HUS_SKU,
    COALESCE(SUM(CASE WHEN vm.vendorcode = 'PWD' THEN _PWD.stock ELSE 0 END), 0) AS PWD_TotalStock,
MAX(CASE WHEN vm.VendorCode = 'PWD' THEN vm.cost ELSE NULL END) AS PWDVendorCost,
    MAX(CASE WHEN vm.vendorcode = 'PWD' THEN _PWD.MFG_SKU ELSE NULL END) AS PWD_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._MED ON vm.MFG_SKU = _MED.MFG_SKU
LEFT JOIN
    products._MAR ON vm.MFG_SKU = _MAR.MFG_SKU
LEFT JOIN
    products._HUS ON vm.MFG_SKU = _HUS.MFG_SKU
LEFT JOIN
    products._PWD ON vm.MFG_SKU = _PWD.MFG_SKU
WHERE
    vm.vendorcode IN ('GAR', 'MED', 'MAR', 'HUS', 'PWD')
    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 = 'yard'
GROUP BY
    od.orderdetailid, vm.VendorCode
HAVING
    (COALESCE(SUM(_GAR.stock), 0) +
     COALESCE(SUM(_MED.stock), 0) +
     COALESCE(SUM(_MAR.stock), 0) +
     COALESCE(SUM(_HUS.stock), 0) +
     COALESCE(SUM(_PWD.stock), 0)) > od.quantity;