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