SELECT 
        p.PIN, 
        CASE WHEN o.Order_Entry_System = 'Marketplace' THEN 'Marketplace'
        ELSE ''
        END AS Order_Entry_System,
        p.Manufacturer,
		IFNULL(GROUP_CONCAT(vm.VendorCode), '') AS Vendors, 
        IFNULL(GROUP_CONCAT(vm.MFG_SKU), '') AS Vendor_PartNo, 
        p.PricingType,
        p.Parent AS Sub, 
        od.ProductCode, 
        od.Quantity, 
        od.QtyOnHold, 
        od.QtyShipped, 
        vpw.Stock, -- AS Inv_Verify_QtyOnHand, 
        p.Cost,
        (vpw.Stock - CASE WHEN MAX(Already_Applied.Total_Unshipped) IS NULL THEN 0 ELSE MAX(Already_Applied.Total_Unshipped) END) AS Available_Inv, 
        --ROUND(AVG(od.Vendor_Price),2) AS AVG_VP,
        od.ProductName, 
        od.OrderID, 
        o.ShipCountry,
        od.ProductNote, 
        od.ShipDate, 
        od.OrderDetailID, 
        o.OrderStatus, 
        (od.Quantity * od.Vendor_Price) AS COGS,
        p.Popularity AS Popularity,
        o.OrderDate, o.ShipState,
        o.Custom_Field_CATStatus,
        o.Custom_Field_CATReason
		
FROM custopayted.orderdetails od
JOIN custopayted.orders o ON o.orderid = od.orderid
JOIN products.pins p ON od.productcode = p.ProductCode

LEFT JOIN products.vendor_map vm ON vm.ProductCode = p.ProductCode AND vm.VendorCode != 'VPW'

LEFT JOIN (
	SELECT vm.PIN, pw.Stock
	FROM products.vendor_map vm
	JOIN products._VPW pw ON pw.MFG_SKU = vm.MFG_SKU AND vm.VendorCode = 'VPW'
) vpw ON vpw.PIN = p.PIN

LEFT OUTER JOIN (
	SELECT p.PIN, SUM(od.quantity) AS Total_Unshipped
	FROM custopayted.orderdetails od
	JOIN products.pins p ON od.productcode = p.ProductCode
	WHERE od.productnote LIKE '%STOCK%'
	AND od.quantity > 0
	AND od.qtyshipped = 0
	GROUP BY p.PIN
) AS Already_Applied ON p.PIN = Already_Applied.PIN

WHERE od.QtyOnHold > 0
AND o.OrderStatus NOT IN ('Cancelled','Canceled','Cancel Order','Shipped','Returned')
GROUP BY 
        p.PIN, 
        p.PricingType,
        od.ProductCode, 
        p.Manufacturer, 
        p.Parent,
        od.Quantity, 
        od.QtyOnHold, 
        od.QtyShipped, 
        vpw.Stock,
        p.Cost,
        (vpw.Stock - Already_Applied.Total_Unshipped),
        od.ProductName, 
        od.OrderID, 
        od.ProductNote, 
        od.ShipDate, 
        od.OrderDetailID, 
        o.OrderStatus, 
        od.Vendor_Price,
        p.Popularity,
        o.OrderDate, o.ShipState,
        o.Custom_Field_CATStatus,
        o.Custom_Field_CATReason,
        o.Order_Entry_System,
        o.ShipCountry
ORDER BY 
        p.Manufacturer, 
        od.ProductCode