CREATE PROCEDURE CalcInsuranceCeiling @InsuranceCode varchar(3), @WareHouseCode varchar(3), @GoodsCode VARCHAR(15), @Qty INT
AS
DECLARE @FreeCount INT 
DECLARE @QtyResult INT 
DECLARE @SalesCeiling INT  
DECLARE @PriceRoof INT 


DECLARE @CacheDate AS TABLE
(
  GoodsCode VARCHAR(15),
  PriceRoof INT, 
  SalesCeiling INT, 
  FreeCount INT ,
  Qty INT  
)

SELECT @PriceRoof = Saghf_Forosh, @SalesCeiling= ISNULL(SalesCeiling,0)   FROM (
SELECT K_Code GoodsCode, ISNULL(Saghf_Forosh,0) Saghf_Forosh FROM dbo.Anbar WITH (NOLOCK)
WHERE K_Code = @GoodsCode
AND A_Code = @WareHouseCode
AND Active_Flag = 0
) AS Warehouse
LEFT OUTER JOIN 
(
 SELECT GoodsCode, SalesCeiling  FROM ExclusiveInsuranceCode WITH (NOLOCK)
 WHERE InsuranceCode = @InsuranceCode
) AS ExclusiveInsurance ON ExclusiveInsurance.GoodsCode = Warehouse.GoodsCode

IF @PriceRoof = 0 
BEGIN
  IF @SalesCeiling = 0 
  BEGIN
    SET @QtyResult = @Qty
	SET @FreeCount = 0
  END 
  ELSE 
  BEGIN
    SET @QtyResult = @SalesCeiling
	SET @FreeCount = @Qty - @SalesCeiling
  END 
END 
ELSE 
BEGIN
  IF (@SalesCeiling = 0 ) OR (@SalesCeiling = @PriceRoof)
  BEGIN
    SET @QtyResult = @PriceRoof
	SET @FreeCount = 0
  END

  ELSE
  BEGIN
    
	IF @PriceRoof > @SalesCeiling 
	BEGIN
	  SET @QtyResult = @SalesCeiling
	  SET @FreeCount = @PriceRoof - @SalesCeiling
    END 
	ELSE IF @PriceRoof < @SalesCeiling
	BEGIN
	  SET @QtyResult = @PriceRoof
	  SET @FreeCount = 0
    END 
  END 
END


INSERT INTO @CacheDate(GoodsCode,PriceRoof, SalesCeiling, FreeCount,Qty)
VALUES(@GoodsCode, @PriceRoof, @SalesCeiling, @FreeCount, @QtyResult)

SELECT * FROM @CacheDate
