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


DECLARE @CacheDate AS TABLE
(
  GoodsCode VARCHAR(15),
  Drug INT, 
  Insurance INT, 
  FreeCount INT ,
  Qty INT  
)

SELECT @DrugCeiling = Saghf_Forosh, @InsuranceCeiling= 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 @DrugCeiling = 0 
BEGIN
  IF @InsuranceCeiling = 0 
  BEGIN
    SET @QtyResult = @Qty
	SET @FreeCount = 0
  END 
  ELSE 
  BEGIN
    SET @QtyResult = @InsuranceCeiling
	SET @FreeCount = @Qty - @InsuranceCeiling
  END 
END 
ELSE 
BEGIN
  IF (@InsuranceCeiling = 0 ) OR (@InsuranceCeiling = @DrugCeiling)
  BEGIN
    SET @QtyResult = @DrugCeiling
	SET @FreeCount = 0
  END
  ELSE
  BEGIN
	IF @DrugCeiling > @InsuranceCeiling 
	BEGIN
      SET @QtyResult = @InsuranceCeiling
	  IF  (@Qty > @InsuranceCeiling AND @Qty < @DrugCeiling)
		SET @FreeCount = @Qty - @InsuranceCeiling
	  ELSE
	    SET @FreeCount = @DrugCeiling - @InsuranceCeiling
    END 
	ELSE IF @DrugCeiling < @InsuranceCeiling
	BEGIN
      SET @QtyResult = @DrugCeiling
      SET @FreeCount = 0
    END 
  END 
END


INSERT INTO @CacheDate(GoodsCode,Drug, Insurance, FreeCount,Qty)
VALUES(@GoodsCode, @DrugCeiling, @InsuranceCeiling, @FreeCount, @QtyResult)

SELECT * FROM @CacheDate
