SELECT
	od.orderid,
	od.orderdetailid,
	DATE(o.orderdate) AS OrderDate,
	od.quantity,
	od.qtyonhold,
	o.orderstatus,
	od.productnote,
    cm.productcode,
    vpw.MFG_SKU AS PIN,
	vpw.stock,
    vpw.location,
    CASE
        WHEN od.ProductNote LIKE '%SPC%' OR od.ProductNote LIKE '%ETA%' THEN 'Y'
        ELSE ''
    END AS On_Backorder,
    ut_latest.StockAppliedDate,
    CONCAT(ut_latest.OldValue, '->', ut_latest.NewValue) AS StockChange
FROM
    custopayted.orders o
JOIN
    custopayted.orderdetails od ON o.orderid = od.orderid
JOIN
    products.client_map cm ON od.productcode = cm.productcode AND cm.Client = 'Volusion'
JOIN
    products.pins p ON cm.PIN = p.PIN
JOIN
    products._VPW vpw ON cm.PIN = vpw.MFG_SKU
LEFT JOIN
    (
        SELECT
            ut.PrimaryKey,
            ut.LastMod AS StockAppliedDate,
            ut.OldValue,
            ut.NewValue
        FROM
            custopayted.user_tracking ut
        INNER JOIN
            (
                SELECT
                    PrimaryKey,
                    MAX(LastMod) AS MaxLastMod
                FROM
                    custopayted.user_tracking
                WHERE
                    ColumnName = 'Stock' AND NewValue > OldValue
                GROUP BY
                    PrimaryKey
            ) sub ON ut.PrimaryKey = sub.PrimaryKey AND ut.LastMod = sub.MaxLastMod
        WHERE
            ut.ColumnName = 'Stock' AND ut.NewValue > ut.OldValue
    ) ut_latest ON ut_latest.PrimaryKey = vpw.MFG_SKU
WHERE
    od.qtyonhold > 0
    AND o.orderstatus = 'Processing'
    AND o.orderdate < CURRENT_DATE - INTERVAL 7 DAY
    AND o.ordernotes NOT LIKE '%BACKREV%'
    AND vpw.stock > 0
GROUP BY
    od.orderdetailid,
    vpw.MFG_SKU,
    vpw.stock,
    vpw.location,
    On_Backorder,
    ut_latest.StockAppliedDate,
    ut_latest.OldValue,
    ut_latest.NewValue
ORDER BY
    On_Backorder DESC