SELECT 
    o.orderid,
    od.orderdetailid,
    o.orderdate,
    o.orderstatus,
    od.productnote,
    od.productcode,
    od.productname,
    p.combinedstock + IFNULL(parent.combinedstock, 0) AS combinedstock,
    p.PIN
FROM
    custopayted.orderdetails od
        JOIN
    custopayted.orders o ON od.orderid = o.orderid
        JOIN
    custopayted.payment_log pl ON o.orderid = pl.pay_orderid
        JOIN
    products.pins p ON od.productcode = p.productcode
		LEFT JOIN
	products.pins parent ON parent.ProductCode = p.Parent
WHERE
    o.lastmodified >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        AND (od.productnote LIKE 'LTBO'
        OR od.productnote LIKE 'FNLA')
        AND pl.pay_result <> 'FAILED'
        AND (p.combinedstock > 0 OR parent.combinedstock > 0)
        AND o.orderstatus = 'cancelled'
        AND o.ordernotes NOT LIKE '%BACK IN STOCK%'
        AND p.Hidden != 'Y'
	AND o.custom_field_catstatus <> 'COMPLETED'
GROUP BY od.orderdetailid
ORDER BY p.combinedstock ASC