CREATE VIEW dbo.MISDetailView AS
/*
  Created by: Kent Johansen, June 2023
  Changes:
  20240219: Ext: Added ConfirmDate
  20230915: Ext: Added POS
  20230711: Ext: Added a couple of fields.

*/
SELECT Personel.UserIDCode, MISTableViewDeleted.ReservationStatus, MISTableViewDeleted.HotelCustomerNumber, Customer.LastName AS HotelName, MISTableViewDeleted.ReferenceNumber, MISTableViewDeleted.FolioNumber,
ReservationAddress.LastName, ReservationAddress.FirstName, ReservationAddress.Link AS CustomerNumber, Reservations.ArrivalDate, Reservations.DepartureDate, MISTableViewDeleted.DateStamp,
MISTableViewDeleted.AccountNumber, MISTableViewDeleted.ValueType, SUM(MISTableViewDeleted.BookedAdult) + SUM(MISTableViewDeleted.ActualAdult) AS Adult,
SUM(MISTableViewDeleted.BookedNumberOfRooms) + SUM(MISTableViewDeleted.ActualNumberOfRooms) AS NumberOfRooms, 
SUM(MISTableViewDeleted.BookedPriceExPackageExVat) + SUM(MISTableViewDeleted.BookedOnlyPackageExVat) + SUM(MISTableViewDeleted.ActualPriceExPackageExVat) + SUM(MISTableViewDeleted.ActualOnlyPackageExVat) AS AmountExVAT,
SUM(MISTableViewDeleted.BookedPriceExPackage) + SUM(MISTableViewDeleted.BookedOnlyPackage) + SUM(MISTableViewDeleted.ActualPriceExPackage) + SUM(MISTableViewDeleted.ActualOnlyPackage) AS AmountInclVAT,
ProjectCodes.Name AS ProjectCodeName, Departments.Name AS DepartmentName, Reservations.ReservationCreateDate, PointOfSales.Text AS POSName, ReservationsExt.ConfirmDateStamp
FROM MISTableViewDeleted WITH (NOLOCK)
LEFT OUTER JOIN Personel WITH (NOLOCK) ON Personel.UserIDNumber = MISTableViewDeleted.StaffID
LEFT OUTER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableViewDeleted.HotelCustomerNumber
LEFT OUTER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = MISTableViewDeleted.ReferenceNumber
  AND ReservationAddressXTab.GuestNumber = 0
  AND ReservationAddressXTab.AddressTypeNumber = 1
LEFT OUTER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
  AND ReservationAddress.AddressType = 1
LEFT OUTER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MISTableViewDeleted.ReferenceNumber
  AND Reservations.GuestNumber = 0
LEFT OUTER JOIN ProjectCodes WITH (NOLOCK) ON ProjectCodes.PK_ProjectCode = MISTableViewDeleted.ProjectCode
LEFT OUTER JOIN PointOfSales WITH (NOLOCK) ON PointOfSales.Number = MISTableViewDeleted.PointOfSaleNumber
LEFT OUTER JOIN ReservationsExt WITH (NOLOCK) ON ReservationsExt.FK_ReferenceNumber = MISTableViewDeleted.ReferenceNumber
  AND ReservationsExt.FK_GuestNumber = MISTableViewDeleted.GuestNumber
INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = MISTableViewDeleted.DepartmentNumber
GROUP BY Personel.UserIDCode, MISTableViewDeleted.ReservationStatus, MISTableViewDeleted.HotelCustomerNumber, Customer.LastName, MISTableViewDeleted.ReferenceNumber, MISTableViewDeleted.FolioNumber,
ReservationAddress.LastName, ReservationAddress.FirstName, ReservationAddress.Link, Reservations.ArrivalDate, Reservations.DepartureDate, MISTableViewDeleted.DateStamp,
MISTableViewDeleted.AccountNumber, MISTableViewDeleted.ValueType, ProjectCodes.Name, Departments.Name, Reservations.ReservationCreateDate, PointOfSales.Text, ReservationsExt.ConfirmDateStamp
GO