CREATE PROCEDURE dbo.GetProvisionInfo @AReferenceNumber INT, @AGuestNumber INT, @ADayProduct INT, @AHotelCountryNumber INT, @ARoomTypeNumber INT, @ASplitbillNumber INT,
  @AProvisionWareNumber INT OUTPUT, @AProvisionWareText VARCHAR(50) OUTPUT, @AWareTypeWare INT OUTPUT, @AProvisionRate FLOAT OUTPUT, @AProvisionType INT OUTPUT,
  @APostVAT BIT OUTPUT, @AVATValue MONEY OUTPUT, @AMISValueType INT OUTPUT, @AResultAccountNumber INT OUTPUT, @AProvisionWareGroupNumber INT OUTPUT AS
SET NOCOUNT ON
/*
  Created by Kent Johansen, April 2020 - get the provision ware info
  BaseProvisionType: 0 = OnBill, 3 = OnAccount
  Changes:
    KJ 20200520: BUG: Fix for "Prov wrong if splitbill is set".
  
*/
DECLARE @CountryNumber INT
DECLARE @MemberOfEU BIT
DECLARE @VATValueNumber INT
DECLARE @GuestType INT
DECLARE @AddressType INT

SELECT @GuestType = GuestType 
FROM SplitBillWareGroupXTab WITH (NOLOCK) 
INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = SplitBillWaregroupXTab.WaregroupNumber
  AND WareGroups.Payment = 5
WHERE SplitBillNumber = @ASplitbillNumber
 
IF ISNULL(@GuestType,1) = 1 
  SET @GuestType = 1

IF @GuestType IN (2,4,6,8)
  SET @AddressType = (@GuestType/2)
ELSE
BEGIN
  IF @GuestType = 1
    SET @AddressType = 1
  ELSE IF @GuestType = 3
    SET @AddressType = 2
  ELSE IF @GuestType = 5
    SET @AddressType = 3
  ELSE IF @GuestType = 7
    SET @AddressType = 4
END

SELECT @CountryNumber = CountryNumber, @AProvisionRate = ProvisionRate, @AProvisionType = ProvisionType 
FROM ReservationAddress WITH (NOLOCK)
INNER JOIN ReservationAddressXtab WITH (NOLOCK) ON ReservationAddressXtab.AddressNumber = ReservationAddress.AddressNumber
  AND ReservationAddressXtab.ReferenceNumber = @AReferenceNumber
  AND ReservationAddressXtab.GuestNumber = @AGuestNumber
WHERE ReservationAddress.AddressType = @AddressType

SELECT @MemberOfEU = MemberOfEU From Countries WITH (Nolock) where CountryNumber = @CountryNumber

SET @AWareTypeWare = 0

IF @MemberOfEU = 1
BEGIN
  SET @APostVAT = 1
  IF @ADayProduct = 0
  BEGIN
    IF @AProvisionType IN (2,3) -- onbill
    BEGIN
      IF  @CountryNumber = @AHotelCountryNumber
      BEGIN
        IF @AProvisionType = 2
          SET @AWareTypeWare = 10
        ELSE
          SET @AWareTypeWare = 13
      END
      ELSE
      BEGIN
        IF @AProvisionType = 2
          SET @AWareTypeWare = 11
        ELSE
          SET @AWareTypeWare = 14
      END
    END
  END
END
ELSE
BEGIN
  SET @APostVAT = 1
  IF (@ADayProduct = 0 AND @AProvisionType IN (2,3)) OR (@ADayProduct = -1)
  BEGIN
    IF @AProvisionType = 2
      SET @AWareTypeWare = 12
    ELSE
      SET @AWareTypeWare = 15
  END
END

IF @AWareTypeWare > 0
BEGIN
  SELECT @AProvisionWareNumber = Ware.WareNumber, @AProvisionWareText = Ware.WareName, @AMISValueType = WareGroups.WareType,
    @AResultAccountNumber = Ware.ResultAccountNumber, @AProvisionWareGroupNumber = WareGroups.WareGroupNumber, @VATValueNumber = VATValueNumber
  FROM Ware WITH (NOLOCK) 
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
    AND WareGroups.Payment = 5
    AND ISNULL(WareGroups.MarkAsDeleted, '0') = '0'
  INNER JOIN WareRoomTypeXTab WITH (NOLOCK) ON WareRoomTypeXTab.FK_WareNumber = Ware.WareNumber
    AND WareRoomTypeXTab.FK_RoomTypeNumber = @ARoomTypeNumber
  INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = Ware.ResultAccountNumber
  WHERE Ware.MarkAsDeleted IS NULL 
  AND Ware.Type = @AWareTypeWare 
  
  IF ISNULL(@AProvisionWareNumber, 0) <= 0
  BEGIN
    SELECT TOP 1 @AProvisionWareNumber = Ware.WareNumber, @AProvisionWareText = Ware.WareName, @AMISValueType = WareGroups.WareType,
      @AResultAccountNumber = Ware.ResultAccountNumber, @AProvisionWareGroupNumber = WareGroups.WareGroupNumber, @VATValueNumber = VATValueNumber
    FROM Ware WITH (NOLOCK) 
    INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
      AND WareGroups.Payment = 5
      AND ISNULL(WareGroups.MarkAsDeleted, '0') = '0'
    INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = Ware.ResultAccountNumber
    WHERE Ware.MarkAsDeleted IS NULL 
    AND Ware.Type = @AWareTypeWare 
  END
END

IF @AWareTypeWare IN (12,15,18) 
  SET @APostVAT = 0 -- if provision to other than dk and eu

IF @APostVAT = 0
 SET @VATValueNumber = 0

EXEC GetVatvalueFromVATValueNumber @VATValueNumber, @AVATValue OUTPUT