ALTER PROCEDURE [dbo].[AmazonInventory_s] 
@ProductID NVARCHAR(50)
AS
DECLARE @IsFBASellable BIT
--this is a custom column that was added
DECLARE @Qty INT
SELECT @ProductID = ISNULL(NULLIF(p.ShadowOf, ''), p.ID)
FROM bvc_Product p
WHERE ID = @ProductiD
SELECT @IsFBASellable = ppud.IsFBASellable
FROM bvc_Product_Properties_UserDefined ppud
WHERE ppud.ProductId = @ProductiD
SELECT @Qty = (
        SELECT CASE @IsFBASellable --depending on whether this is checked or NOT, include fba warehouses
                WHEN 0
                    THEN (
                            SELECT (IsNull(Sum(pw.InventoryAvailableQty), 0))
                            FROM dbo.ProductWarehouse pw
                            INNER JOIN dbo.Warehouse w ON pw.WarehouseID = w.ID
                            INNER JOIN dbo.bvc_Product p ON pw.ProductID = p.ID
                            WHERE (
                                    p.ID = @ProductID
                                    OR p.ShadowOf = @ProductID
                                    )
                                AND w.WarehouseType = 0 -- DEFAULT warehouse type
                            )
                WHEN 1
                    THEN (
                            SELECT (IsNull(Sum(pw.InventoryAvailableQty), 0))
                            FROM dbo.ProductWarehouse pw
                            INNER JOIN dbo.Warehouse w ON pw.WarehouseID = w.ID
                            INNER JOIN dbo.bvc_Product p ON pw.ProductID = p.ID
                            WHERE (
                                    p.ID = @ProductID
                                    OR p.ShadowOf = @ProductID
                                    )
                                AND (
                                    w.WarehouseType = 0
                                    OR w.WarehouseType = 1
                                    ) -- DEFAULT OR FBA warehouse types
                            )
                ELSE --in case the new custom column is null
                    (
                        SELECT (IsNull(Sum(pw.InventoryAvailableQty), 0))
                        FROM dbo.ProductWarehouse pw
                        INNER JOIN dbo.Warehouse w ON pw.WarehouseID = w.ID
                        INNER JOIN dbo.bvc_Product p ON pw.ProductID = p.ID
                        WHERE (
                                p.ID = @ProductID
                                OR p.ShadowOf = @ProductID
                                )
                            AND w.WarehouseType = 0 -- DEFAULT warehouse type
                        )
                END
        )
SELECT CASE 
        WHEN @Qty <= 0
            THEN 0
        ELSE @Qty
        END AS Qty