CREATE VIEW [dbo].[MISTableViewDeleted]
/*
************************************
PLEASE NOTE.....
Also change the MISTableView view.......
************************************



  Changes:
  KJ 20250521: Added warecount
  KJ 20230606: Booked price columns can contain data even if date back in time
  KJ 20220506: Allotment included by default
  SG 20220502: Added exclude allotment
  SG 20200501: Removed date-clause for Booked-values in order to allow deleted reservation data back in time
  KJ Created as a copy of MisTableView
  SG 20200401: Exclude status 7 and 8, because these will now be included in MISTABLE (bo be backward compatible)
	SG 20190710: Removed DateStamp in join
	SG/KJO: 20190611: Datestamp added to MISTABLEPackSum
	OF 20120809: Ext: ProductNumber added.
	OF 20120706: Ext: New mis attempt rev. 1
	OF 20120507: Ext: Owner reservation added again.
	OF 20120501: Bug: Owner reservation did not work. Temporarily uncommented.
	OF 20120426: Ext: Owner calculation included.
	OF 20120315: Ext: Calculations used in mis added.
	OF 20120125: New view for getting sum of packages together with logi despite on which days packages are located.
	OF 20120417: GuestNumber added
*/
AS
SELECT     TOP 100 PERCENT dbo.MISTABLE.Room_HallType, dbo.MISTABLE.DateStamp, dbo.MISTABLE.SegmentNumber, dbo.MISTABLE.PointOfSaleNumber, 
                      dbo.MISTABLE.RoomTypeNumber, dbo.MISTABLE.ActualAdult, dbo.MISTABLE.ActualChildren1, dbo.MISTABLE.ActualChildren2, dbo.MISTABLE.ActualNumberOfRooms, 
                      dbo.MISTABLE.BookedAdult, dbo.MISTABLE.BookedChildren1, dbo.MISTABLE.BookedChildren2, dbo.MISTABLE.BookedNumberOfRooms, dbo.MISTABLE.ValueType, 
                      dbo.MISTABLE.ReadOnly, dbo.MISTABLE.HotelCustomerNumber, dbo.MISTABLE.ReservationStatus, dbo.MISTABLE.StaffID, dbo.MISTABLE.CampaignCode, 
                      dbo.MISTABLE.ProjectCode, dbo.MISTABLE.AccountNumber, dbo.MISTABLE.MISData, dbo.MISTABLE.DepartmentNumber, dbo.MISTABLE.ProductWareLinkNumber, 
                      dbo.MISTABLE.ReferenceNumber, dbo.MISTABLE.FolioNumber, dbo.MISTABLE.GuestNumber,  dbo.MISTABLE.ActualWareCount, dbo.MISTABLE.BookedWareCount,
                      CASE WHEN dbo.MISTABLE.DateStamp >= dbo.SYSTEM.SYSTEMDATE THEN 0 ELSE dbo.MISTABLE.ActualPrice + ISNULL(MISPackagesSum_1.PackageSum, 0) 
                      END AS ActualPriceInclPackage, 
                      CASE WHEN dbo.MISTABLE.DateStamp >= dbo.SYSTEM.SYSTEMDATE THEN 0 ELSE (dbo.MISTABLE.ActualPrice + ISNULL(MISPackagesSum_1.PackageSum, 0)) 
                      - (dbo.MISTABLE.ActualVatOfPrice + ISNULL(MISPackagesSum_1.PackageVatOfSum, 0)) END AS ActualPriceInclPackageExVat, 
                      CASE WHEN dbo.MISTABLE.DateStamp >= dbo.SYSTEM.SYSTEMDATE THEN 0 ELSE dbo.MISTABLE.ActualPrice END AS ActualPriceExPackage, 
                      CASE WHEN dbo.MISTABLE.DateStamp >= dbo.SYSTEM.SYSTEMDATE THEN 0 ELSE dbo.MISTABLE.ActualPrice - dbo.MISTABLE.ActualVatOfPrice END AS ActualPriceExPackageExVat,
                       dbo.MISTABLE.ActualPackage AS ActualOnlyPackage, dbo.MISTABLE.ActualPackage - dbo.MISTABLE.ActualVatOfPackage AS ActualOnlyPackageExVat, 

                      CASE WHEN dbo.MISTABLE.DateStamp < dbo.SYSTEM.SYSTEMDATE THEN 0 ELSE dbo.MISTABLE.BookedPrice END AS BookedPriceInclPackage,
                      CASE WHEN dbo.MISTABLE.DateStamp < dbo.SYSTEM.SYSTEMDATE THEN 0 ELSE dbo.MISTABLE.BookedPrice - dbo.MISTABLE.BookedVatOfPrice END AS BookedPriceInclPackageExVat,
                      CASE WHEN dbo.MISTABLE.DateStamp < dbo.SYSTEM.SYSTEMDATE THEN 0 ELSE dbo.MISTABLE.BookedPrice - ISNULL(MISPackagesSum_1.PackageSum, 0) END AS BookedPriceExPackage,
                      CASE WHEN dbo.MISTABLE.DateStamp < dbo.SYSTEM.SYSTEMDATE THEN 0 ELSE dbo.MISTABLE.BookedPrice - dbo.MISTABLE.BookedVatOfPrice - ISNULL(MISPackagesSum_1.PackageSum, 0) + ISNULL(MISPackagesSum_1.PackageVatOfSum, 0) END AS BookedPriceExPackageExVat,
                      CASE WHEN dbo.MISTABLE.DateStamp < dbo.SYSTEM.SYSTEMDATE THEN 0 ELSE dbo.MISTABLE.BookedPackage END AS BookedOnlyPackage,
                      CASE WHEN dbo.MISTABLE.DateStamp < dbo.SYSTEM.SYSTEMDATE THEN 0 ELSE dbo.MISTABLE.BookedPackage - dbo.MISTABLE.BookedVatOfPackage END AS BookedOnlyPackageExVat,
					  dbo.MISTABLE.OwnerReservation, dbo.MISTABLE.IsPackage, 
                      dbo.MISTABLE.CorrectionMoved, dbo.MISTABLE.CorrectionDeleted, dbo.MISTABLE.Text, dbo.MISTABLE.WareGroupNumber,
CASE WHEN MISTABLE.ReservationStatus = 7 THEN
  ISNULL(
    (
      SELECT TOP 1 Date 
      FROM ReservationFollowUpXTab rfx WITH (NOLOCK) 
      INNER JOIN FollowUp f WITH (NOLOCK) ON rfx.FollowUpNumber = f.FollowUpNumber 
      WHERE RFX.ReferenceNumber = MISTABLE.ReferenceNumber 
      AND RFX.GuestNumber = MISTABLE.GuestNumber 
      AND type = 5 
      AND SubType = 3 
      ORDER BY f.FollowUpNumber
    ),
    (
      SELECT TOP 1 Date 
      FROM ReservationFollowUpXTab rfx WITH (NOLOCK) 
      INNER JOIN FollowUp f WITH (NOLOCK) ON rfx.FollowUpNumber = f.FollowUpNumber 
      WHERE RFX.ReferenceNumber = MISTABLE.ReferenceNumber 
      AND RFX.GuestNumber = 0
      AND type = 5 
      AND SubType = 3 
      ORDER BY f.FollowUpNumber
    )
  )  
ELSE  
  NULL
END AS CancellationDate,
		      dbo.MISTABLE.ProductNumber, dbo.MISTABLE.WareType, dbo.MISTABLE.WareNumber
FROM         dbo.MISTABLE WITH (NOLOCK) LEFT OUTER JOIN
                      dbo.MISTABLEPackSum AS MISPackagesSum_1 ON dbo.MISTABLE.ProductWareLinkNumber = MISPackagesSum_1.ProductWareLinkNumber AND 
                      dbo.MISTABLE.IsPackage = 0 
                      LEFT OUTER JOIN
                      dbo.SYSTEM ON 1 = 1
ORDER BY dbo.MISTABLE.ProductWareLinkNumber
GO


