CREATE PROCEDURE dbo.ReturnArrangementStatus @refno int, @guestno int, @Status int output AS
SET NOCOUNT ON

--Last Changed 2007 1008 AV rewrite to inner joins
/*
The procedure returns:
  0 if no arrangement
  1 if only packet arrangement
  2 if only manual lines (extra)
  3 if both
*/

  SET @status = 0

  IF EXISTS (SELECT ArrangementLines.ArrangementLineNumber FROM ArrangementLines WITH (NOLOCK) INNER JOIN ArrangementReservationsXtab WITH (NOLOCK)  ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber AND ReferenceNumber = @RefNo  AND ((@GuestNo > 0 AND GuestNumber=@GuestNo) OR @GuestNo = 0)
                      INNER JOIN ArrangementRoomXTab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementRoomXTab.ArrangementLineNumber)
  BEGIN
    SET @Status = 1
    IF EXISTS (SELECT ArrangementLines.ArrangementLineNumber FROM ArrangementLines WITH (NOLOCK) INNER JOIN ArrangementReservationsXtab WITH (NOLOCK)  ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber AND ArrangementReservationsXtab.ReferenceNumber=@RefNo AND ArrangementReservationsXtab.GuestNumber=@GuestNo
                      LEFT OUTER  JOIN ArrangementRoomXTab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementRoomXTab.ArrangementLineNumber WHERE ArrangementRoomXTab.ArrangementLineNumber IS NULL)
      SET @Status = 3  
  END
  ELSE IF EXISTS (SELECT ArrangementLines.ArrangementLineNumber FROM ArrangementLines WITH (NOLOCK) INNER JOIN ArrangementReservationsXtab WITH (NOLOCK)  ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber AND ArrangementReservationsXtab.ReferenceNumber=@RefNo AND ArrangementReservationsXtab.GuestNumber=@GuestNo
                     LEFT OUTER  JOIN ArrangementRoomXTab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementRoomXTab.ArrangementLineNumber WHERE ArrangementRoomXTab.ArrangementLineNumber IS NULL)
    SET @Status = 2

