SELECT 
        p.PIN, 
        CASE WHEN o.Order_Entry_System = 'Marketplace' THEN 'Marketplace'
        ELSE ''
        END AS Order_Entry_System,
        p.Manufacturer, 
        CASE
	WHEN p.VendorCodes LIKE '%GAR%' THEN gar.MFG_SKU
	WHEN p.VendorCodes LIKE '%PWD%' THEN pwd.MFG_SKU
	WHEN p.VendorCodes LIKE '%CHV%' THEN chv.MFG_SKU
	WHEN p.VendorCodes LIKE '%HRZ%' THEN hrz.MFG_SKU
	WHEN vol.ProductCode LIKE 'HON-%' THEN vol.ProductCode
	ELSE ''
END AS Vendor_PartNo, 
        esc.MFG_SKU as Essco_Code,
        p.PricingType,
        vol.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 orderdetails od
JOIN orders o ON o.orderid = od.orderid
JOIN products.client_map vol ON od.productcode = vol.ProductCode AND vol.Client = 'Volusion'
JOIN products.pins p ON p.PIN = vol.PIN

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 = vol.PIN

LEFT JOIN products.vendor_map esc ON vol.PIN = esc.PIN AND esc.VendorCode = 'ESC'
LEFT JOIN products.vendor_map gar ON vol.PIN = gar.PIN AND gar.VendorCode = 'GAR'
LEFT JOIN products.vendor_map chv ON vol.PIN = chv.PIN AND chv.VendorCode = 'CHV'
LEFT JOIN products.vendor_map pwd ON vol.PIN = pwd.PIN AND pwd.VendorCode = 'PWD'
LEFT JOIN products.vendor_map hrz ON vol.PIN = hrz.PIN AND hrz.VendorCode = 'HRZ'

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

WHERE od.QtyOnHold > 0
AND p.vertical = 'rv'
AND o.OrderStatus NOT IN ('Cancelled','Canceled','Cancel Order','Shipped','Returned')
GROUP BY 
        p.PIN, 
        p.PricingType,
        od.ProductCode, 
        p.Manufacturer, 
        Vendor_PartNo, 
        esc.MFG_SKU, 
        vol.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