CREATE PROCEDURE  [dbo].[Sp_Duty]  @IdHavaleh Bigint , @TotDuty MONEY, @ActionCode TinyInt
AS
 iF (Select Count(Price_Row) Icount From Tmp_Over_Under_Factor WITH (NOLOCK)
     Where Price_Row = 1 And ShowPrice = 1 And Id_Havaleh = @IdHavaleh) > 0 
 Begin

    DECLARE @TotPrice Money
	DECLARE @TmpOverUnder AS TABLE
    (
	  Autoid BIGINT,
	  Id_Havaleh BIGINT,
	  Type char(1),
	  Duty_Flag CHAR(1),
	  Price_Row MONEY,
	  ShowPrice CHAR(1),
	  [percent] REAL ,
	  Action_Code SMALLINT,
	  price Money	  
	)
  
	INSERT INTO @TmpOverUnder(Autoid, Id_Havaleh, Type,Duty_Flag, Price_Row,ShowPrice,[percent],Action_Code, price)
	SELECT AutoInc, Id_Havaleh,dbo.Over_Under.Type,Duty_Flag, Price_Row,dbo.Tmp_Over_Under_Factor.ShowPrice,[percent],Action_Code, Price 
	FROM dbo.Tmp_Over_Under_Factor WITH (NOLOCK)
	INNER JOIN dbo.Over_Under WITH (NOLOCK) ON OVER_Under.AutoId = Code_Over_Under
	WHERE Id_Havaleh=  @IdHavaleh

	
	SELECT @TotPrice = ISNULL(@TotDuty,0) - Abs(SUM(CASE WHEN [@TmpOverUnder].Type = 1 THEN price ELSE 0 END) - SUM(CASE WHEN [@TmpOverUnder].Type = 0 THEN price ELSE 0 END)) 
	FROM @TmpOverUnder WHERE Duty_Flag = 1 AND Action_Code = @ActionCode
	GROUP BY Id_Havaleh

	IF ISNULL(@TotPrice,0) > 0 
	Begin
		UPDATE dbo.Tmp_Over_Under_Factor SET Price = Round(ISNULL(@TotPrice,0) * Tmp_Over_Under_Factor.[percent]/100,0) FROM dbo.Tmp_Over_Under_Factor
		INNER JOIN @TmpOverUnder ON [@TmpOverUnder].Autoid = Tmp_Over_Under_Factor.AutoInc
		WHERE Tmp_Over_Under_Factor.Id_Havaleh=  @IdHavaleh And Tmp_Over_Under_Factor.Price_Row = 1 And Tmp_Over_Under_Factor.ShowPrice = 1 AND Duty_Flag = 0 
	End
End

