


CREATE VIEW DBO.TFindReservationView AS
/*
   USED BY: InHouseSaldoForm.pas, Postering.pas

   KJ 20130321: Ext: Added RoomTypesHotel
   JP 20050113 Booking.Change: Implemented in Delphi instead
   JP 20041220 Added AgentName
   JP 20041208 optimized by not using address view
*/
SELECT rr.linenumber, r.ReferenceNumber, r.GuestNumber, r.Status, r.SPLITBILL, r.AltResNo, r.ArrivalDate, r.ArrivalTime, r.SideHouse, r.DepartureDate, r.DepartureTime, r.Adults, r.Children1, r.Children2,

rm.HotelNumber,

Rooms.Number AS SpecRoomNumber, Rooms.SideHouseNumber AS SideHouseNumber, RoomTypes.Type AS SpecBookedType,

rr.NumberOfRooms AS SpecNumberOfRooms,rr.ArrivalDate AS SpecArrivalDate, rr.ArrivalTime AS SpecArrivalTime, rr.DepartureDate AS SpecDepartureDate,
rr.DepartureTime AS SpecDepartureTime, rr.Adults AS SpecAdults, rr.Children1 AS SpecChildren1, rr.Children2 AS SpecChildren2,

ra1.lastname, ra1.Phone, ra1.firstname, ra2.lastname as CompanyName, ra3.lastname as AgencyName, ra4.lastname as AgentName,
ROOMTYPES.HotelNumber AS RoomTypesHotel
FROM dbo.RESERVATIONS R WITH (NOLOCK)
LEFT OUTER JOIN dbo.RESERVATIONMAIN RM WITH (NOLOCK) ON rm.ReferenceNumber = r.ReferenceNumber
LEFT OUTER JOIN dbo.RESERVATIONROOMLINEXTAB rrx WITH (NOLOCK) ON r.ReferenceNumber = rrx.ReferenceNumber AND  r.GuestNumber = rrx.GuestNumber
LEFT OUTER JOIN dbo.RESERVATIONROOMLINE rr WITH (NOLOCK) ON rr.LINENUMBER = rrx.LineNumber 
LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = rr.RoomNumber
LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = rr.BookedTypeNumber

LEFT OUTER JOIN ReservationAddressXTab RAX1 WITH (NOLOCK) ON R.ReferenceNumber=RAX1.ReferenceNumber AND R.GuestNumber=RAX1.GuestNumber AND RAX1.AddressTypeNumber=1
LEFT OUTER  JOIN ReservationAddress RA1 WITH (NOLOCK) ON RAX1.AddressNumber = RA1.AddressNumber
LEFT OUTER JOIN ReservationAddressXTab RAX2 WITH (NOLOCK) ON R.ReferenceNumber=RAX2.ReferenceNumber AND R.GuestNumber=RAX2.GuestNumber AND RAX2.AddressTypeNumber=2
LEFT OUTER  JOIN ReservationAddress RA2 WITH (NOLOCK) ON RAX2.AddressNumber = RA2.AddressNumber
LEFT OUTER JOIN ReservationAddressXTab RAX3 WITH (NOLOCK) ON R.ReferenceNumber=RAX3.ReferenceNumber AND R.GuestNumber=RAX3.GuestNumber AND RAX3.AddressTypeNumber=3
LEFT OUTER  JOIN ReservationAddress RA3 WITH (NOLOCK) ON RAX3.AddressNumber = RA3.AddressNumber
LEFT OUTER JOIN ReservationAddressXTab RAX4 WITH (NOLOCK) ON R.ReferenceNumber=RAX4.ReferenceNumber AND R.GuestNumber=RAX4.GuestNumber AND RAX4.AddressTypeNumber=4
LEFT OUTER  JOIN ReservationAddress RA4 WITH (NOLOCK) ON RAX4.AddressNumber = RA4.AddressNumber

