CREATE PROCEDURE dbo.PMIPosOtb @AHotelNumber INT, @AStartDate CHAR(8), @AEndDate CHAR(8), @APerWare BIT = 0 AS
SET NOCOUNT ON
/*
  Created by Kent Johansen March 2013
  Returns the PosOTB info for PMI/d2o interface
  Changes:
    KJ 20250402: Bug: Fix for "Work-a-round for MISTable.WareGroupNumber sometimes is sort number"
    SG 20210325: Pieces selected from wareCount 
    KJ 20201109: Bug: Fix for "Amount 0 when amount is from package".
    KJ 20200622: Ext: MIS per Ware. Req #3717
    KJ 20200428: Ext: Added provision in MIS. Req #3788
    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.

*/
DECLARE @LInclProvision BIT

SELECT @LInclProvision = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='IncludeProvisionInMIS'
SET @LInclProvision = ISNULL(@LInclProvision, 0)

IF ISNULL(@APerWare, 0) = 1
BEGIN
  SELECT CONVERT(CHAR(8), MISTableView.DateStamp, 112) AS Date,  MISTableView.DepartmentNumber, 
  dbo.GetVATPercent(Accounts.VATValueNumber) AS VATValue, Ware.WareGroupNumber, MISTableView.WareNumber, Ware.WareName,
  SUM(IsNull(MISTableView.ActualWareCount, 0) + IsNull(MISTableView.BookedWareCount, 0)) AS Pieces,  
  ROUND(SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat) + SUM(MISTableView.ActualOnlyPackageExVat), 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 Ware WITH (NOLOCK) ON Ware.WareNumber = MISTableView.WareNumber
  WHERE CONVERT(CHAR(8), MISTableView.DateStamp, 112) >= @AStartDate 
  AND CONVERT(CHAR(8), MISTableView.DateStamp, 112) <= @AEndDate
  AND (@LInclProvision = 1 OR ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND Ware.WareGroupNumber IN
  (
    SELECT WareGroupsDepartmentsXtab.WareGroupNumber
	FROM WareGroupsDepartmentsXtab WITH (NOLOCK)
	INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = WareGroupsDepartmentsXtab.FK_Department
	  AND Departments.PMIDepartment = 1
  )
  GROUP BY CONVERT(CHAR(8), MISTableView.DateStamp, 112), MISTableView.DepartmentNumber, Accounts.VATValueNumber, Ware.WareGroupNumber,
    MISTableView.WareNumber, Ware.WareName
  HAVING ROUND(SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat) + SUM(MISTableView.ActualOnlyPackageExVat), 4) <> 0
  ORDER BY CONVERT(CHAR(8), MISTableView.DateStamp, 112), MISTableView.DepartmentNumber, Ware.WareGroupNumber
END
ELSE
BEGIN
  SELECT CONVERT(CHAR(8), MISTableView.DateStamp, 112) AS Date,  MISTableView.DepartmentNumber, 
  dbo.GetVATPercent(Accounts.VATValueNumber) AS VATValue, Ware.WareGroupNumber,
  SUM(IsNull(MISTableView.ActualWareCount, 0) + IsNull(MISTableView.BookedWareCount, 0)) AS Pieces,  
  ROUND(SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat) + SUM(MISTableView.ActualOnlyPackageExVat), 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 Ware WITH (NOLOCK) ON Ware.WareNumber = MISTableView.WareNumber
  WHERE CONVERT(CHAR(8), MISTableView.DateStamp, 112) >= @AStartDate 
  AND CONVERT(CHAR(8), MISTableView.DateStamp, 112) <= @AEndDate
  AND (@LInclProvision = 1 OR ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND Ware.WareGroupNumber IN
  (
    SELECT WareGroupsDepartmentsXtab.WareGroupNumber
	FROM WareGroupsDepartmentsXtab WITH (NOLOCK)
	INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = WareGroupsDepartmentsXtab.FK_Department
	  AND Departments.PMIDepartment = 1
  )
  GROUP BY CONVERT(CHAR(8), MISTableView.DateStamp, 112), MISTableView.DepartmentNumber, Accounts.VATValueNumber, Ware.WareGroupNumber
  HAVING ROUND(SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat) + SUM(MISTableView.ActualOnlyPackageExVat), 4) <> 0
  ORDER BY CONVERT(CHAR(8), MISTableView.DateStamp, 112), MISTableView.DepartmentNumber, Ware.WareGroupNumber
END
