CREATE PROCEDURE DBO.[GetHalls] @ReferenceNumber INT, @GuestNumber INT, @FixedPriceProduct INT = NULL AS
SET NOCOUNT ON
CREATE TABLE #PriceHalls(LineNumber INT NOT NULL
, PriceTotal MONEY NULL
, TaxTotal MONEY NULL
, PriceArrival MONEY NULL
, TaxArrival MONEY NULL
)
EXEC dbo.GetContentHallLine @ReferenceNumber, @GuestNumber, 0, NULL
SELECT PriceTotal, TaxTotal AS VatTotal, PriceArrival, TaxArrival AS VatArrival
, RCL.ReservationConfLineNumber AS LineNumber, RCL.BookedConferenceRoomTypeNumber AS BookedTypeNumber
, Convert(DateTime, RCL.ArrivalDate) AS ArrivalDate, Convert(DateTime, RCL.DepartureDate) AS DepartureDate
, RCL.DepartureTime, RCL.ProductNumber, RCL.NumberOfRooms, RCL.PackageNumber, RCL.Price, RCL.TotalPrice, RCL.PriceOverWritten
, RCL.ConferenceRoomSetupNumber, RCL.ConferenceRoomNumber, RCL.Adults, RCL.Children1, RCL.Children2
, RCL.ScreenLine, RCL.ReferenceNumber, RCL.SchemePattern, RCL.SchemeWeekDays
, RCL.LinkLineNumber, RCL.RebookedUnderLineNumber
, Products.FixedPrice, Products.ProductName, Products.Description AS ProductDescription
, ConferenceRooms.Name
, CONFERENCEROOMTYPES_1.Type AS BookedType, CONFERENCEROOMTYPES.Type AS HallType
, CONFERENCEROOMTYPES_1.ConferenceRoomTypeNumber AS BookedTypeNumber, CONFERENCEROOMTYPES.ConferenceRoomTypeNumber AS HallTypeNumber
, CONFERENCEROOMTABLES.Text AS HallTableName, CONFERENCEROOMTYPES.Text AS HallTypeName
FROM #PriceHalls WITH (NOLOCK) INNER JOIN
  RESERVATIONCONFERENCELINE RCL WITH (NOLOCK) ON RCL.ReservationConfLineNumber = #PriceHalls.LineNumber LEFT OUTER JOIN
  CONFERENCEROOMTABLES WITH (NOLOCK) ON RCL.ConferenceRoomSetupNumber = CONFERENCEROOMTABLES.ConferenceRoomTableNumber LEFT OUTER JOIN
  Products WITH (NOLOCK) ON RCL.ProductNumber = Products.ProductNumber LEFT OUTER JOIN
  CONFERENCEROOMTYPES WITH (NOLOCK) INNER JOIN
  CONFERENCEROOMS WITH (NOLOCK) ON CONFERENCEROOMTYPES.ConferenceRoomTypeNumber = CONFERENCEROOMS.ConferenceRoomTypeNumber
    ON RCL.ConferenceRoomNumber = CONFERENCEROOMS.ConferenceRoomNumber LEFT OUTER JOIN
  CONFERENCEROOMTYPES AS CONFERENCEROOMTYPES_1 WITH (NOLOCK) ON RCL.BookedConferenceRoomTypeNumber = CONFERENCEROOMTYPES_1.ConferenceRoomTypeNumber
WHERE RCL.ReferenceNumber=@ReferenceNumber
  ORDER BY RCL.ArrivalDate, RCL.DepartureDate, RCL.ArrivalTime, RCL.DepartureTime, RCL.ReservationConfLineNumber
DROP TABLE #PriceHalls
