SELECT 
	_VPW.MFG_SKU,
	_VPW.location,
	MAX(pins.Name) AS Name, 
        MAX(_VPW.TX) AS TX, 
        MAX(_VPW.OH) AS OH,
        MAX(_VPW.TX + IFNULL(_VPW.OH,0)) AS TotalOnHand, 
	MAX(pins.Cost) AS COGS, 
    CASE
        WHEN MAX(pins.PricingType) = 'DEAD' OR MAX(pins.PricingType) = 'BENCH-D' THEN 0
        WHEN MAX(pins.PricingType) = 'SLOW' OR MAX(pins.PricingType) = 'BENCH-S' THEN MAX((_VPW.TX + IFNULL(_VPW.OH,0)) * pins.Cost/2) 
        ELSE MAX((_VPW.TX + IFNULL(_VPW.OH,0)) * pins.Cost) END AS Value,
	MAX(pins.Manufacturer) AS Vendor,
    MAX(REPLACE(REPLACE(pins.PricingType, 'BENCH-S', 'SLOW'), 'BENCH-D', 'DEAD')) AS Status,
    CASE
    	WHEN pins.ModelID <> '' THEN 'Whole Good'
        ELSE NULL END AS Whole_Good
FROM 
	products._VPW
JOIN products.pins
	ON _VPW.MFG_SKU = pins.PIN AND pins.Parent = ''
WHERE 
	_VPW.TX > 0 OR _VPW.OH > 0
GROUP BY _VPW.location, _VPW.MFG_SKU, pins.ModelID
ORDER BY _VPW.Location