DECLARE @DepartmentNumber CHAR(1)
DECLARE @CentralBookingType INT
DECLARE @HotelCustomerNumber INT
DECLARE @HotelNumber INT
DECLARE @ErrorStr VARCHAR(100)

SELECT @CentralBookingType = CONVERT(INT, ISNULL(KeyValue, 0)) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'CentralBookingType'

IF @CentralBookingType=7
BEGIN
  CREATE TABLE #tmpHotel (HotelCustomerNumber INT)
  
  INSERT INTO #tmpHotel
    SELECT DISTINCT HotelCustomerNumber FROM Transactions WITH (NOLOCK) 
    WHERE DepartmentOverwrite IS NULL OR DepartmentOverwrite NOT IN (SELECT DepartmentNumber FROM Departments WITH (NOLOCK))

  DECLARE #db_cur CURSOR FOR 
    SELECT HotelCustomerNumber FROM #tmpHotel
  OPEN #db_cur
  
  FETCH NEXT FROM #db_cur INTO @HotelCustomerNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    -- First see if any autocharge department exists
    SELECT TOP 1 @DepartmentNumber = DepartmentNumber, @HotelNumber = c.HotelNumber 
    FROM Departments d WITH (NOLOCK)
    INNER JOIN Customer c WITH (NOLOCK) ON d.HotelNumber = c.HotelNumber 
      AND CustomerTypeNumber = 7
      AND CustomerNumber = @HotelCustomerNumber
    WHERE d.AutoCharge = 1
    ORDER BY DepartmentNumber DESC

    -- if not use any department
    IF ISNULL(@DepartmentNumber, '') = ''
      SELECT TOP 1 @DepartmentNumber = DepartmentNumber, @HotelNumber = c.HotelNumber 
      FROM Departments d WITH (NOLOCK)
      INNER JOIN Customer c WITH (NOLOCK) ON d.HotelNumber = c.HotelNumber 
        AND CustomerTypeNumber = 7
      AND CustomerNumber = @HotelCustomerNumber
      ORDER BY DepartmentNumber DESC

    -- No department for the selected hotel
    IF ISNULL(@DepartmentNumber, '') = ''
    BEGIN
      SET @ErrorStr = 'Error in UpdateDepartmentOnTransactions: No department for the selected hotelCustomerNumber: ' + ISNULL(CONVERT(VARCHAR, @HotelCustomerNumber), '')
      RAISERROR(@ErrorStr, 20, 1) --WITH LOG
    END
    ELSE
    BEGIN
      UPDATE Transactions SET DepartmentOverwrite = @DepartmentNumber 
      WHERE (DepartmentOverwrite IS NULL OR DepartmentOverwrite NOT IN (SELECT DepartmentNumber FROM Departments WITH (NOLOCK)))
      AND HotelCustomerNumber = @HotelCustomerNumber
    END

    FETCH NEXT FROM #db_cur INTO @HotelCustomerNumber
  END
  CLOSE #db_cur
  DEALLOCATE #db_cur
  DROP TABLE #tmpHotel  
END
ELSE
BEGIN
  SELECT TOP 1 DepartmentNumber FROM Departments WITH (NOLOCK)
  WHERE AutoCharge = 1
  ORDER BY DepartmentNumber DESC

  UPDATE Transactions SET DepartmentOverwrite = @DepartmentNumber 
  WHERE DepartmentOverwrite IS NULL
  OR DepartmentOverwrite NOT IN (SELECT DepartmentNumber FROM Departments WITH (NOLOCK))
END
