CREATE FUNCTION DBO.GetArrangementValue (@AReferenceNumber INT, @AGuestNumber INT, @AFromDate CHAR(8), @AToDate CHAR(8)) RETURNS INTEGER AS
BEGIN
/* 
  Created by Kent Johansen december 2009 
  Changes:
    KJ 20141215: Bug: Fix for "Extra not calculated correctly". Case #CR080208
    KJ 20130219: Bug: Fix for "Only include for selected period". Case #CR053107
    KJ 20100111: Ext: Added @Budget Extra - setup to select if all extras og only extracharge extras should be added
*/
  DECLARE @Value INT
  DECLARE @BudgetExtra INT
  
  SELECT @BudgetExtra = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BudgetExtra'
  SET @BudgetExtra = ISNULL(@BudgetExtra, 1)

  SELECT @Value = SUM(ISNULL(al.Pieces,0)*ISNULL(al.Price,0))
  FROM ArrangementLines al WITH (NOLOCK)
  INNER JOIN ArrangementReservationsXtab arx WITH (NOLOCK) ON arx.ArrangementLinenumber = al.ArrangementLineNumber
    AND arx.ReferenceNumber = @AReferenceNumber 
    AND arx.GuestNumber = @AGuestNumber
  INNER JOIN Departments d WITH (NOLOCK) ON d.PK_Department = al.FK_ExDepartment
    AND (@BudgetExtra = 0 OR d.AutoCharge = 1)
  WHERE al.WareNumber > 0
  AND al.Date >= @AFromDate AND al.Date <= @AToDate

  RETURN @Value
END