SELECT
*
FROM
(
SELECT 
        pins.PIN, 
		MAX(vendor_map.MFG_SKU) AS SEN_Order,
        pins.Manufacturer, 
		pins.SKU, 
        pins.Parent AS Sub, 
        orderdetails.ProductCode, 
        orderdetails.Quantity, 
        orderdetails.QtyOnHold, 
        orderdetails.QtyShipped, 
        _VPW.Stock AS Inv_Verify_QtyOnHand, 
        pins.Cost,
        (IFNULL(_VPW.Stock,0) - CASE WHEN MAX(Already_Applied.Total_Unshipped) IS NULL THEN 0 ELSE MAX(Already_Applied.Total_Unshipped) END) AS Available_Inv, 
        --ROUND(AVG(orderdetails.Vendor_Price),2) AS AVG_VP,
        orderdetails.ProductName, 
        orderdetails.OrderID, 
        orderdetails.ProductNote, 
        orderdetails.ShipDate, 
        orderdetails.OrderDetailID, 
        orders.orderstatus, 
        (orderdetails.Quantity * orderdetails.Vendor_Price) AS COGS,
        pins.Popularity AS Popularity,
        orders.OrderDate,
		orders.ShipState,
        orders.Custom_Field_CATStatus,
        orders.Custom_Field_CATReason
FROM custopayted.orderdetails 
JOIN custopayted.orders 
        ON orders.OrderID = orderdetails.OrderID 
JOIN products.pins
		ON orderdetails.ProductCode = pins.ProductCode
JOIN products.vendor_map
		ON pins.ProductCode = vendor_map.ProductCode AND vendor_map.VendorCode = 'SEN'
LEFT JOIN products._VPW
		ON pins.PIN = _VPW.MFG_SKU
		
LEFT OUTER JOIN (
        SELECT pins.PIN AS Location, SUM(orderdetails.Quantity) AS Total_Unshipped
        FROM custopayted.orderdetails
        JOIN products.pins
                ON orderdetails.ProductCode = pins.ProductCode
        WHERE orderdetails.ProductNote LIKE '%STOCK%'
        AND orderdetails.Quantity > 0
        AND orderdetails.QtyShipped = 0
        GROUP BY pins.PIN
) AS Already_Applied
        ON pins.PIN = Already_Applied.Location        
WHERE orderdetails.QtyOnHold > 0
AND orders.orderstatus = 'Processing'
GROUP BY 
        pins.PIN, 
        orderdetails.ProductCode, 
        pins.Manufacturer, 
        pins.SKU, 
                pins.Parent,
        orderdetails.Quantity, 
        orderdetails.QtyOnHold, 
        orderdetails.QtyShipped, 
        _VPW.Stock,
        pins.Cost,
        (_VPW.Stock - Already_Applied.Total_Unshipped),
        orderdetails.ProductName, 
        orderdetails.OrderID, 
        orderdetails.ProductNote, 
        orderdetails.ShipDate, 
        orderdetails.OrderDetailID, 
        orders.orderstatus, 
        orderdetails.Vendor_Price,
        pins.Popularity,
        orders.OrderDate, orders.ShipState,
        orders.Custom_Field_CATStatus,
        orders.Custom_Field_CATReason
	) AS Senco
WHERE Available_Inv < 1