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 = 'ESC' THEN _ESC.stock ELSE 0 END), 0) AS ESC_TotalStock,
MAX(CASE WHEN vm.VendorCode = 'ESC' THEN vm.cost ELSE NULL END) AS ESCVendorCost,
    MAX(CASE WHEN vm.vendorcode = 'ESC' THEN _ESC.MFG_SKU ELSE NULL END) AS ESC_SKU,
    COALESCE(SUM(CASE WHEN vm.vendorcode = 'SCV' THEN _SCV.stock ELSE 0 END), 0) AS SCV_TotalStock,
MAX(CASE WHEN vm.VendorCode = 'SCV' THEN vm.cost ELSE NULL END) AS SCVVendorCost,
    MAX(CASE WHEN vm.vendorcode = 'SCV' THEN _SCV.MFG_SKU ELSE NULL END) AS SCV_SKU,
    COALESCE(SUM(CASE WHEN vm.vendorcode = 'BIS' THEN _BIS.stock ELSE 0 END), 0) AS BIS_TotalStock,
MAX(CASE WHEN vm.VendorCode = 'BIS' THEN vm.cost ELSE NULL END) AS BISVendorCost,
    MAX(CASE WHEN vm.vendorcode = 'BIS' THEN _BIS.MFG_SKU ELSE NULL END) AS BIS_SKU,
    COALESCE(SUM(CASE WHEN vm.vendorcode = 'TAC' THEN _TAC.stock ELSE 0 END), 0) AS TAC_TotalStock,
MAX(CASE WHEN vm.VendorCode = 'TAC' THEN vm.cost ELSE NULL END) AS TACVendorCost,
    MAX(CASE WHEN vm.vendorcode = 'TAC' THEN _TAC.MFG_SKU ELSE NULL END) AS TAC_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._ESC ON vm.MFG_SKU = _ESC.MFG_SKU
LEFT JOIN
    products._SCV ON vm.MFG_SKU = _SCV.MFG_SKU
LEFT JOIN
    products._BIS ON vm.MFG_SKU = _BIS.MFG_SKU
LEFT JOIN
    products._TAC ON vm.MFG_SKU = _TAC.MFG_SKU
WHERE
    vm.vendorcode IN ('ESC', 'SCV', 'BIS', 'TAC')
    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 = 'vacuum'
GROUP BY
    od.orderdetailid, vm.VendorCode
HAVING
    (COALESCE(SUM(_ESC.stock), 0) +
     COALESCE(SUM(_SCV.stock), 0) +
     COALESCE(SUM(_BIS.stock), 0) +
     COALESCE(SUM(_TAC.stock), 0)) > od.quantity;