CREATE FUNCTION dbo.VatValueCalc (@AAccountNumber INT, @AAmount MONEY)
RETURNS MONEY AS  
BEGIN 
  /*
    Created by Kent Johansen Marts 2007
    Returns the Amount with added vat based on vatvalue on the selected accountnumber
  */

  DECLARE @Result MONEY
  SET @Result = @AAmount

  DECLARE @VatValueWareText VARCHAR(5)
  DECLARE @VatValueNumber INT
  DECLARE @VatValue FLOAT

  DECLARE @VatValueIndex1 FLOAT
  DECLARE @VatValueIndex2 FLOAT
  DECLARE @VatValueIndex3 FLOAT
  DECLARE @VatValueIndex4 FLOAT
  DECLARE @VatValueIndex5 FLOAT

  SELECT @VatValueWareText = KeyValue FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='VATValue1'
  IF (CHARINDEX(',', @VatValueWareText)>0)
  SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',', @VatValueWareText) ,1, '.')
  SET @VatValueIndex1 = CONVERT(FLOAT, @VatValueWareText) 

  SELECT @VatValueWareText = KeyValue FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='VATValue2'
  IF (CHARINDEX(',', @VatValueWareText)>0)
  SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',', @VatValueWareText) ,1, '.')
  SET @VatValueIndex2 = CONVERT(FLOAT, @VatValueWareText) 

  SELECT @VatValueWareText = KeyValue FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='VATValue3'
  IF (CHARINDEX(',', @VatValueWareText)>0)
  SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',', @VatValueWareText) ,1, '.')
  SET @VatValueIndex3 = CONVERT(FLOAT, @VatValueWareText) 

  SELECT @VatValueWareText = KeyValue FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='VATValue4'
  IF (CHARINDEX(',', @VatValueWareText)>0)
  SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',', @VatValueWareText) ,1, '.')
  SET @VatValueIndex4 = CONVERT(FLOAT, @VatValueWareText) 

  SELECT @VatValueWareText = KeyValue FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='VATValue5'
  IF (CHARINDEX(',', @VatValueWareText)>0)
  SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',', @VatValueWareText) ,1, '.')
  SET @VatValueIndex5 = CONVERT(FLOAT, @VatValueWareText) 

  IF ISNULL(@VatValueIndex1,0) = 0  SET @VatValueIndex1 = 0
  IF ISNULL(@VatValueIndex2,0) = 0  SET @VatValueIndex2 = 0
  IF ISNULL(@VatValueIndex3,0) = 0  SET @VatValueIndex3 = 0
  IF ISNULL(@VatValueIndex4,0) = 0  SET @VatValueIndex4 = 0
  IF ISNULL(@VatValueIndex5,0) = 0  SET @VatValueIndex5 = 0

  SELECT @VatValueNumber = VatValueNumber FROM Accounts WITH (NOLOCK) WHERE AccountNumber = @AAccountNumber

  SET @VatValue = CASE @VatValueNumber 
    WHEN 1 THEN @VatValueIndex1
    WHEN 2 THEN @VatValueIndex2
    WHEN 3 THEN @VatValueIndex3
    WHEN 4 THEN @VatValueIndex4
    WHEN 5 THEN @VatValueIndex5
  END

  IF @VatValue > 0
    SET @Result = @AAmount*((@VatValue/100)+1)

  RETURN @Result
END

