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

  Changes:
  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 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, 
                      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, dbo.MISTABLE.BookedPackage AS BookedOnlyPackage, 
                      dbo.MISTABLE.BookedPackage - dbo.MISTABLE.BookedVatOfPackage AS BookedOnlyPackageExVat, dbo.MISTABLE.OwnerReservation, dbo.MISTABLE.IsPackage, 
                      dbo.MISTABLE.CorrectionMoved, dbo.MISTABLE.CorrectionDeleted, dbo.MISTABLE.Text, dbo.MISTABLE.WareGroupNumber,
		              dbo.MISTABLE.ProductNumber, dbo.MISTABLE.WareType, dbo.MISTABLE.WareNumber,
                      dbo.MISTABLE.ActualWareCount, dbo.MISTABLE.BookedWareCount
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 
                      --and (CONVERT(DATETIME, ISNULL(MISPackagesSum_1.Datestamp, 0), 120) = '1900-01-01 00:00:00.000' OR MISPackagesSum_1.Datestamp = MISTABLE.DateStamp)
                      LEFT OUTER JOIN
                      dbo.SYSTEM ON 1 = 1
WHERE MISTABLE.ReservationStatus NOT IN (7,8) OR dbo.MISTABLE.FolioNumber <> -1				  
ORDER BY dbo.MISTABLE.ProductWareLinkNumber
GO
