CREATE PROCEDURE dbo.DeleteRoomTypes (@fromroomtypenumber INT, @toroomtypenumber INT) AS
BEGIN
	SET NOCOUNT ON;

  DECLARE @cnt INT
  DECLARE @s VARCHAR(500)

  DECLARE @HotelNumber1 INT
  DECLARE @HotelNumber2 INT

  SELECT @cnt = COUNT(*)
  FROM RateLevelInvRuleRoomTypeXtab WITH (NOLOCK)
  WHERE FK_RoomTypeNumber = @fromroomtypenumber

  Select @HotelNumber1 = HotelNumber FROM ROOMTYPES (NOLOCK)
  WHERE RoomTypeNumber = @fromroomtypenumber

  Select @HotelNumber2 = HotelNumber FROM ROOMTYPES (NOLOCK)
  WHERE RoomTypeNumber = @toroomtypenumber

  BEGIN TRY
    IF ISNULL(@cnt, 0) > 0
    BEGIN
      SET @s = 'Error in storedprocedure <<DeleteRoomTypes>>: Room type is in use in Rate Intelligence. Not allowed to delete room type'
      RAISERROR(@s, 15, 1)
    END    

	IF @HotelNumber1 <> @HotelNumber2
	BEGIN
      SET @s = 'Error in storedprocedure <<DeleteRoomTypes>>: Hotel number in source and destination room types are not equal.'
      RAISERROR(@s, 15, 1)
    END  

    update rooms set roomtypenumber=@toroomtypenumber where roomtypenumber=@fromroomtypenumber
    --update reservations set bookedtypenumber=@toroomtypenumber where bookedtypenumber=@fromroomtypenumber
    update reservationroomline set bookedtypenumber=@toroomtypenumber where bookedtypenumber=@fromroomtypenumber
	update ReservationRoomLineBackup set bookedtypenumber=@toroomtypenumber where bookedtypenumber=@fromroomtypenumber
    update roomtypes set upgradetypenumber=@toroomtypenumber where upgradetypenumber=@fromroomtypenumber
    update onlineroomtypes set roomtypenumber=@toroomtypenumber where roomtypenumber=@fromroomtypenumber
    update ChainRoomTypes set productroomtypenumber=@toroomtypenumber where productroomtypenumber =@fromroomtypenumber
    --update allotmentlines set roomtypenumber =@toroomtypenumber where roomtypenumber=@fromroomtypenumber    --udkommenter det hvis den fejller
    DELETE from OTAInvCountNotifLog where FKroomtypenumber=@fromroomtypenumber 
    DELETE from OTAAvailNotifLog where FKroomtypenumber=@fromroomtypenumber 
    DELETE from OTAAvailNotifLogMulti where FKroomtypenumber=@fromroomtypenumber 
    DELETE OTARateAmountNotifLog WHERE FKRoomTypeNumber=@fromroomtypenumber 
    delete from productprice where roomtypenumber=@fromroomtypenumber
    delete from allotmentlines where roomtypenumber=@fromroomtypenumber  ---aktiver det hvis du udkomenter update allotment men den stadig fejller
    delete from roombookingstatus where roomtypenumber=@fromroomtypenumber
    delete from YieldRoomTypeXtab where roomtype=@fromroomtypenumber
    delete from RoomTypeDifferentialItem where FK_RoomTypeNumber = @FromRoomTypeNumber
    delete from RoomTypeOverbooking where FK_RoomTypeNumber = @FromRoomTypeNumber
	delete from RoomTypeRoomTypeXTAB where PrimaryRoomTypeNumber = @FromRoomTypeNumber
	delete from RoomTypeRoomTypeXTAB where SecondayRoomTypeNumber = @FromRoomTypeNumber
    delete from RateLevelRoomTypeRestrictionXTab where FKRateLevelRoomTypeXTabId in (select Id from RateLevelRoomTypeXTab(nolock) where FKRoomTypeNumber = @FromRoomTypeNumber)
    delete from RateLevelRoomTypeRestrictionMulti where FKRateLevelRoomTypeXTabId in (select Id from RateLevelRoomTypeXTab(nolock) where FKRoomTypeNumber = @FromRoomTypeNumber)
    delete from RateLevelRoomTypeXTab where FKRoomTypeNumber = @FromRoomTypeNumber
    delete from roomtypes where roomtypenumber=@fromroomtypenumber
    delete from productprice where roomtypenumber>0 and not roomtypenumber in (select roomtypenumber from roomtypes(nolock))
    delete from allotmentlines where not roomtypenumber in (select roomtypenumber from roomtypes(nolock))
  END TRY
  BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(MAX)
    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT

    SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
  END CATCH
END