CREATE PROCEDURE PMIPosOtb @AHotelNumber INT, @AStartDate CHAR(8), @AEndDate CHAR(8) AS
SET NOCOUNT ON
/*
  Created by Kent Johansen March 2013
  Returns the PosOTB info for PMI/d2o interface
  Changes:
    KJ 20130619: PMI Department should just be ONE of the departments that is related to the waregroup.
    KJ 20130410: Use Mis to get OTB (on the book) amount.

*/

SELECT CONVERT(CHAR(8), MISTableView.DateStamp, 112) AS Date,  MISTableView.DepartmentNumber, 
dbo.GetVATPercent(Accounts.VATValueNumber) AS VATValue, MISTableView.WareGroupNumber,
SUM(ISNULL(MISTableView.BookedAdult, 0) + ISNULL(MISTableView.BookedChildren1, 0) + ISNULL(MISTableView.BookedChildren2, 0) + 
  ISNULL(MISTableView.ActualAdult, 0) + ISNULL(MISTableView.ActualChildren1, 0) + ISNULL(MISTableView.ActualChildren2, 0)) AS Pieces,  
ROUND(SUM(MISTableView.BookedPriceInclPackageExVat) + SUM(MISTableView.ActualPriceInclPackageExVat), 4) AS Price
FROM MISTableView WITH (NOLOCK)
INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = MISTableView.AccountNumber
  AND Accounts.Balance = 1
  AND NOT Accounts.AccountType  IN (1,2)
INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableView.HotelCustomerNumber
  AND Customer.HotelNumber = @AHotelNumber
INNER JOIN WareGroupsDepartmentsXtab WITH (NOLOCK) ON WareGroupsDepartmentsXtab.WareGroupNumber = MISTableView.WareGroupNumber
INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = WareGroupsDepartmentsXtab.FK_Department
  AND Departments.PMIDepartment = 1
WHERE CONVERT(CHAR(8), MISTableView.DateStamp, 112) >= @AStartDate 
AND CONVERT(CHAR(8), MISTableView.DateStamp, 112) <= @AEndDate
GROUP BY CONVERT(CHAR(8), MISTableView.DateStamp, 112), MISTableView.DepartmentNumber, Accounts.VATValueNumber, MISTableView.WareGroupNumber
HAVING ROUND(SUM(MISTableView.BookedPriceInclPackageExVat) + SUM(MISTableView.ActualPriceInclPackageExVat), 4) <> 0
ORDER BY CONVERT(CHAR(8), MISTableView.DateStamp, 112), MISTableView.DepartmentNumber, MISTableView.WareGroupNumber