SELECT od.orderid, od.orderdetailid, od.productnote, od.productcode, od.quantity, od.qtyonhold, od.qtyshipped, od.qtyonpackingslip,
  CASE
    WHEN od.qtyonpackingslip = 0 AND od.qtyonhold = 0 AND od.qtyshipped > 0 THEN 'SHIPPED'
    WHEN od.qtyonpackingslip IS NULL THEN 'SHIPPING'
    WHEN od.qtyonhold > 0 THEN 'NOT RECEIVED'
    ELSE ''
  END AS status
FROM orderdetails od
JOIN (
  SELECT orderid
  FROM orders
  WHERE batchnumber = (SELECT MAX(batchnumber) FROM orders)
) o ON od.orderid = o.orderid
WHERE od.qtyonpackingslip IS NULL OR od.qtyonpackingslip >= 0
GROUP BY od.orderid, od.orderdetailid, od.qtyonhold, status
HAVING SUM(od.qtyonhold) > 0
ORDER BY od.orderid DESC