CREATE PROCEDURE dbo.GetProvisionWareNumber @AProvisionFolioNumber INT, @ADayProduct INT, @AHotelCountryNumber INT, @BaseProvisionType INT, @ARoomTypeNumber INT,
  @AProvisionWareNumber INT OUTPUT, @AProvisionWareText VARCHAR(50) OUTPUT, @AProvisionRate FLOAT OUTPUT, @APostVAT BIT OUTPUT, @ACountryNumber INT = -1 AS
SET NOCOUNT ON
/*
  Created by Kent Johansen, April 2013 - get the provision ware info
  BaseProvisionType: 0 = OnBill, 3 = OnAccount
  Changes:
    KJ 20170706: Bug: Fix for "Wrong ware used for posting". Case #CR129443
    KJ 20150727: Bug: Fix for "Account provision not posted". Case #CR090345
    KJ 20141103: Bug: Fix for "Account provision not posted". Case #CR079028
    KJ 20141030: Bug: Fix for "Wrong ware used for DK provision". Case #CR079028
  
*/
DECLARE @ProvisionType TINYINT
DECLARE @MemberOfEU BIT
DECLARE @WareTypeWare INT

SET @ACountryNumber = ISNULL(@ACountryNumber, -1)

IF @ACountryNumber < 0
  SELECT @ACountryNumber = CountryNumber, @AProvisionRate = PROVISIONRATE, @ProvisionType = ProvisionType 
  FROM ReservationAddress WITH (NOLOCK)
  INNER JOIN Folios WITH (NOLOCK) ON Folios.ReservationAddressNumber = ReservationAddress.AddressNumber
    AND Folios.FolioNumber = @AProvisionFolioNumber
ELSE
  SELECT @AProvisionRate = PROVISIONRATE, @ProvisionType = ProvisionType 
  FROM ReservationAddress WITH (NOLOCK)
  INNER JOIN Folios WITH (NOLOCK) ON Folios.ReservationAddressNumber = ReservationAddress.AddressNumber
    AND Folios.FolioNumber = @AProvisionFolioNumber
 
SELECT @MemberOfEU = MemberOfEU From Countries WITH (Nolock) where CountryNumber = @ACountryNumber

SET @WareTypeWare = 0

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

IF @WareTypeWare > 0
BEGIN
  SELECT @AProvisionWareNumber = Ware.WareNumber, @AProvisionWareText = Ware.WareName 
  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
  WHERE Ware.MarkAsDeleted IS NULL 
  AND Ware.Type = @WareTypeWare 
  
  IF ISNULL(@AProvisionWareNumber, 0) <= 0
  BEGIN
    SELECT TOP 1 @AProvisionWareNumber = Ware.WareNumber, @AProvisionWareText = Ware.WareName 
    FROM Ware WITH (NOLOCK) 
    INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
      AND WareGroups.Payment = 5
      AND ISNULL(WareGroups.MarkAsDeleted, '0') = '0'
    WHERE Ware.MarkAsDeleted IS NULL 
    AND Ware.Type = @WareTypeWare 
  END
END