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

  Changes:
  KJ 20220506: Allotment included by default
  SG 20220502: Added exclude allotment
  SG 20210302: Added ActualWareCount, BookedWareCount
  SG 20200512: Temp fix removed
  KJ 20200506: Temp fix for wrong amount if provision and axcl Package
  SG 20200501: Allow Actual values (FolioNumber <> -1) even when status in (7,8)
  KJ 20200427: Added WareType and WareNumber for provision in MIS. Req #3788
  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 MISTable.Room_HallType, MISTable.DateStamp, MISTable.SegmentNumber, MISTable.PointOfSaleNumber, 
MISTable.RoomTypeNumber, MISTable.ActualAdult, MISTable.ActualChildren1, MISTable.ActualChildren2, MISTable.ActualNumberOfRooms, 
MISTable.BookedAdult, MISTable.BookedChildren1, MISTable.BookedChildren2, MISTable.BookedNumberOfRooms, MISTable.ValueType, 
MISTable.ReadOnly, MISTable.HotelCustomerNumber, MISTable.ReservationStatus, MISTable.StaffID, MISTable.CampaignCode, 
MISTable.ProjectCode, MISTable.AccountNumber, MISTable.MISData, MISTable.DepartmentNumber, MISTable.ProductWareLinkNumber, 
MISTable.ReferenceNumber, MISTable.FolioNumber, MISTable.GuestNumber, 
CASE WHEN MISTable.DateStamp >= System.SystemDate THEN 0 ELSE MISTable.ActualPrice + ISNULL(MISPackagesSum_1.PackageSum, 0) END AS ActualPriceInclPackage, 
CASE WHEN MISTable.DateStamp >= System.SystemDate THEN 0 ELSE (MISTable.ActualPrice + ISNULL(MISPackagesSum_1.PackageSum, 0)) - (MISTable.ActualVatOfPrice + ISNULL(MISPackagesSum_1.PackageVatOfSum, 0)) END AS ActualPriceInclPackageExVat, 
CASE WHEN MISTable.DateStamp >= System.SystemDate THEN 0 ELSE MISTable.ActualPrice END AS ActualPriceExPackage, 
CASE WHEN MISTable.DateStamp >= System.SystemDate THEN 0 ELSE MISTable.ActualPrice - MISTable.ActualVatOfPrice END AS ActualPriceExPackageExVat,
MISTable.ActualPackage AS ActualOnlyPackage, 
MISTable.ActualPackage - MISTable.ActualVatOfPackage AS ActualOnlyPackageExVat, 
CASE WHEN MISTable.DateStamp < System.SystemDate THEN 0 ELSE MISTable.BookedPrice END AS BookedPriceInclPackage, 
CASE WHEN MISTable.DateStamp < System.SystemDate THEN 0 ELSE MISTable.BookedPrice - MISTable.BookedVatOfPrice END AS BookedPriceInclPackageExVat,
CASE WHEN MISTable.DateStamp < System.SystemDate THEN 0 ELSE MISTable.BookedPrice - ISNULL(MISPackagesSum_1.PackageSum, 0) END AS BookedPriceExPackage, 
CASE WHEN MISTable.DateStamp < System.SystemDate THEN 0 ELSE MISTable.BookedPrice - MISTable.BookedVatOfPrice - ISNULL(MISPackagesSum_1.PackageSum, 0) + ISNULL(MISPackagesSum_1.PackageVatOfSum, 0) END AS BookedPriceExPackageExVat, 
MISTable.BookedPackage AS BookedOnlyPackage, 
MISTable.BookedPackage - MISTable.BookedVatOfPackage AS BookedOnlyPackageExVat, 
MISTable.OwnerReservation, MISTable.IsPackage, MISTable.CorrectionMoved, MISTable.CorrectionDeleted, MISTable.Text, MISTable.WareGroupNumber,
MISTable.ProductNumber, MISTable.WareType, MISTable.WareNumber,MISTable.ActualWareCount, MISTable.BookedWareCount
FROM  MISTable WITH (NOLOCK) 
LEFT OUTER JOIN MISTablePackSum AS MISPackagesSum_1 ON MISTable.ProductWareLinkNumber = MISPackagesSum_1.ProductWareLinkNumber 
  AND MISTable.IsPackage = 0 
LEFT OUTER JOIN System ON 1 = 1
WHERE (MISTable.ReservationStatus NOT IN (7,8) OR MISTable.FolioNumber <> -1)
ORDER BY MISTable.ProductWareLinkNumber
GO


