ALTER PROCEDURE [dbo].[AmazonInventory_s] @ProductID NVARCHAR(50) AS DECLARE @ClientID int; DECLARE @TotalFBA INT; DECLARE @CompanyId INT; DECLARE @ASIN NVARCHAR(226); -- strange number, but that is the size of the field as of now --first determine if @ProductID is the parentSKU or if it is a shadow, and if so then find the parent SELECT @ASIN = p.ASIN, @ClientID = p.ClientID, @Companyid = p.CompanyID, @ProductID = ISNULL(NULLIF(p.ShadowOf, ''), p.ID) FROM bvc_product p WHERE p.ID = @ProductID SELECT @TotalFBA = sum(pw.InventoryAvailableQty) FROM ProductWarehouse pw INNER JOIN bvc_product p ON p.ID = pw.ProductID INNER JOIN bvc_Product_Properties_eBay ppe ON p.ID = ppe.ProductID INNER JOIN Warehouse w ON pw.WarehouseID = w.ID WHERE p.ASIN = @ASIN AND p.CompanyID = @Companyid AND w.WarehouseType = 1 SELECT CASE WHEN @TotalFBA > 0 THEN 0 --There is inventory in FBA so send MerchantFulfilled quantity as zero WHEN @TotalFBA < 1 AND ppe.Replenishable = 1 THEN --There is no inventory in FBA so precead normally for replenishable items CASE WHEN ppa.AmazonUseFixedInventoryQty = 0 --use the product based setting for fixed quantity THEN ppa.AmazonFixedInventoryQty WHEN ppa.AmazonUseFixedInventoryQty = 1 --use the company based setting for default quantity THEN am.AmazonDefaultFixedQty END WHEN @TotalFBA < 1 AND ppe.Replenishable = 0 THEN --There is no inventory in FBA so precead normally for non-replenishable items CASE WHEN ppa.InventoryMaxQtyForAmazonEnabled = 1 AND ppa.InventoryMaxQtyForAmazon < p.InventoryAvailableQty --inventory max is enabled, so use the lesser of the max AND available quantity THEN ppa.InventoryMaxQtyForAmazon WHEN ppa.InventoryMaxQtyForAmazonEnabled = 1 AND ppa.InventoryMaxQtyForAmazon > p.InventoryAvailableQty THEN p.InventoryAvailableQty WHEN ppa.InventoryMaxQtyForAmazonEnabled = 0 --no inventory max enabled, so use regular quantity THEN p.InventoryAvailableQty END END AS Qty FROM bvc_Product p INNER JOIN bvc_Product_Properties_eBay ppe ON ppe.ProductID = p.id INNER JOIN bvc_Product_Properties_Amazon ppa ON ppa.ProductID = p.id INNER JOIN Amazon_Merchants am ON am.ID = p.CompanyID WHERE p.ID = @ProductID