CREATE PROCEDURE dbo.GetReferenceNumberFromInHouseRoomNumber @AScreenRoomNumber INT, @AReferenceNumber INT OUTPUT , @AGuestNumber INT OUTPUT, @AHotelNumber INT = -1 AS

--Created AV 2007 1210 used in onlinebooking, Martin
--Lst Changed AV 20080114 sidehouse
  DECLARE @RoomNumberID INT
  SET @AReferenceNumber = 0
  SET @AGuestNumber = 0
  SELECT @RoomNumberID = ISNULL(RoomNumber, 0) FROM Rooms WITH (NOLOCK) WHERE Number = @AScreenRoomNumber AND (HotelNumber = @AHotelNumber OR @AHotelNumber = -1)
  IF @RoomNumberID > 0 
  BEGIN
    SELECT DISTINCT  @AReferenceNumber =  ISNULL(R.ReferenceNumber, 0), @AGuestNumber = ISNULL(R.GuestNumber, 0) FROM dbo.Reservations R WITH (NOLOCK) 
    INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.ReferenceNumber>0 AND R.Status=5
    INNER JOIN dbo.ReservationRoomLine RL WITH (NOLOCK) ON  RL.LineNumber=RLX.LineNumber AND RL.RoomNumber=@RoomNumberID
    IF @AReferenceNumber = 0 OR @AReferenceNumber IS NULL -- Look after Sidehouse
    BEGIN
      SELECT DISTINCT  @AReferenceNumber =  ISNULL(R.ReferenceNumber, 0), @AGuestNumber = ISNULL(R.GuestNumber, 0) FROM dbo.Reservations R WITH (NOLOCK) 
      INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.ReferenceNumber>0 AND R.Status=5
      INNER JOIN dbo.ReservationRoomLine RL WITH (NOLOCK) ON  RL.LineNumber=RLX.LineNumber INNER JOIN Rooms WITH (NOLOCK) ON RL.RoomNumber= Rooms.RoomNumber AND Rooms.SideHouseNumber = @AScreenRoomNumber
    END
 
  END


