CREATE PROCEDURE [dbo].[CalcPacketPriceAccExt]  @DayProduct Bit, @RoomtypeNumber int, @DateStamp Char(8), @PacketNumber int, @ProductNumber int, @Adult int, @Children1 int, @Children2 int, 
  @NumberOfRooms INT, @HotelCustomerNumber INT, @PackagePrice Float OUTPUT, @VatByPacketValue Float Output, @NamelistSetting int = -1, @OnePersonPaysForAll Bit = 0, 
  @PriceOverwritten BIT = 0, @FixedPrice BIT = 0, @PackageDepartment SMALLINT = 1, @AReferenceNumber INT = -1, @GuestNumber INT = -1, 
  @PriceoverwriteValue Money = 0, @DiscountValue FLOAT = 0, @Pwln BIGINT = -1, @ForAllotment INT = 0 AS

SET NOCOUNT ON

/*
  Created by Kristian November 2006.
  Used for MIS calculation
  Populates #PacketWareTable declared in GenerateTurnoverMIS

  Changes:
    KJ 20250409: Menu ware allowed even if splitdoes not add up if UseForResidualAmount is set
    KJ 20241022: Bug: Fix for "Department/hotelnumber is null". Case #CR249989
    KJ 20241009: Ext: Added From/To Time
    KJ 20241008: Bug: Fix for "Wrong hotel number used when charge packet department enabled".
    KJ 20240521: Bug: "Fix" for "Arithmetic overflow error converting expression to data type int
    SG 20220502: Added param @ForAllotment
    SG 20210310: Added BookedWareCount to #PacketWareTable
	  SG 20200526: Added WareType and WareNumber to #PacketWareTable
    KJ 20150105: Bug: Fix for "Error when post of more than one pieces of a menu ware not in percent". Case #CR081859
    KJ 20140624: Ext: Menu ware split in whole amount. Req #1561
    OF 20120806: Ext: Discount trial.
    OF 20120808: Ext: ProductNumber added to mis and WareGroupNumber resolved as Sortnumber
    KJ 20120713: Ext: Forecast calculation is removed.
    OF 20120706: Ext: New mis attempt rev. 1
    KJ 20120705: Bug: Another try to fix "Arrival package not calculated when +product and charge package department enabled".
    KJ 20120606: Bug: Fix for "Discount in package not calculated correct". Case #CR043844
    OF 20120113: Bug: Wrong calculation of menuwares for mis. Text added for wares.
    KJ 20110928: Bug: Fix for "Debtype 2+6+9 calculated wrong if not priceoverwritten". Case #CR034373
    OF 20110913: Ext: Storing of data for mistable.
    KJ 20110919: Bug: Fix for "Debtype 2+6+9 wrong calculated when priceoverwritten or when running PackagesplitAfterReduction". Case #CR034026
    KJ 20110307: Ext: Menuware in package. Req# 818
    KJ 20100212: Ext: Added raise error if illegal department
    KJ 20090910: Ext: Budget per department + charge packet department enabled
    KJ 20090812: Ext: Enable budget per department
    KJ 20090122: Bug: Wrong Packet lines if Charge by package department
    OF 20081107: Bug: Fix for no calc of package value when "ChargePacketDepartmentEnabled" is true.
    KJ 20081118: Bug: Major rewrite of dayproducts.
    KJ 20071116: Bug: Added @UpdateTheForecast
    KJ 20071114: Bug: Fix for "When charge by package department then package are not calculated"
    KJ 20070412: Department rewrite
    KJ 20070320: Fix for Wrong price if no vat on account.
    KJ 20070303: Added debug stuff
    KJ 20070226: Some bugfixes.
    KJ 20070216: Only forecast for packagelines with department attached to the hotel
    KJ 20070216: Removed PackageAccount param - not used.
    KJ 20070215: Added @NumberOfRooms as input param
    KJ 20070215: Packagelines should be forecasted ex VAT
    KJ 20070215: Added @HotelCustomerNumber as input param. Used in UpdateForecast
    KJ 20070215: Added updateforecast because all package value is "posted" on one account regardless of multiple accounts.
    KJ 20070215: Fix for Packagelines could be assigned to specific Roomtype
    KJ 20070215: Cleanup.
*/

DECLARE @ShowDebug BIT
SET @ShowDebug = 0
IF @ShowDebug = 1
  Print 'START CalcPacketPriceAccExt'

DECLARE @TempAdult INT
DECLARE @TempChild1 INT
DECLARE @TempChild2 INT
DECLARE @DummySortNumber INT

DECLARE @ChargePacketDepartmentEnabled BIT
DECLARE @WareType INT
DECLARE @WareNumber INT
DECLARE @WareGroupNumber smallint
DECLARE @HandleAsMenuWare BIT
DECLARE @MenuProcentage MONEY
DECLARE @MenuVatValueNumber INT
DECLARE @MenuVatValueWare FLOAT
DECLARE @MenuWarePrice FLOAT
DECLARE @MenuWareResultAccountNumber INT
DECLARE @InPercent BIT

DECLARE @IsFixedPriceProduct BIT
DECLARE @FixAmountSum MONEY
DECLARE @ReductionAfterSplitSum MONEY
DECLARE @PackagesplitAfterReduction BIT
DECLARE @Pieces INT
DECLARE @SubWareFromReduction INT
DECLARE @DummyPercentDebType BIT
DECLARE @WareName VARCHAR(50)

DECLARE @LFromTime DATETIME
DECLARE @LToTime DATETIME

DECLARE @CalcPriceBasedOnOnePersonPaysForAll bit
SELECT @CalcPriceBasedOnOnePersonPaysForAll = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey ='CalcPriceBasedOnOnePersonPaysForAll'
SET @CalcPriceBasedOnOnePersonPaysForAll = ISNULL(@CalcPriceBasedOnOnePersonPaysForAll, 0)

IF @CalcPriceBasedOnOnePersonPaysForAll = 0
BEGIN
  SET @NamelistSetting = -1
  SET @OnePersonPaysForAll = -1
END

IF @NamelistSetting = 1 AND @OnePersonPaysForAll=0
BEGIN
  SET @TempAdult = @Adult
  SET @TempChild1 = @Children1
  SET @TempChild2 = @Children2

  SET @Adult = 1
  SET @Children1 = 1
  SET @Children2 = 1
END

IF ISNULL(@PacketNumber, 0)=0 
  SET @PacketNumber = 0

IF @PacketNumber > 0
BEGIN
  DECLARE @VatValueWareText VARCHAR(10)
  DECLARE @VatValueIndex1 FLOAT
  DECLARE @VatValueIndex2 FLOAT
  DECLARE @VatValueIndex3 FLOAT
  DECLARE @VatValueIndex4 FLOAT
  DECLARE @VatValueIndex5 FLOAT

  DECLARE @HotelNumber INT
  SET @HotelNumber = -1
  IF @HotelCustomerNumber > 0 
    SELECT @HotelNumber = HotelNumber FROM Customer WITH (NOLOCK) WHERE CustomerNumber = @HotelCustomerNumber

  SELECT @VatValueWareText = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'VATValue1'
  IF (CHARINDEX(',', @VatValueWareText) > 0)
    SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',', @VatValueWareText), 1, '.')
  SET @VatValueIndex1 = CONVERT(FLOAT, @VatValueWareText) 

  SELECT @VatValueWareText = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'VATValue2'
  IF (CHARINDEX(',', @VatValueWareText) > 0)
    SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',', @VatValueWareText), 1, '.')
  SET @VatValueIndex2 = CONVERT(FLOAT, @VatValueWareText) 

  SELECT @VatValueWareText = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'VATValue3'
  IF (CHARINDEX(',', @VatValueWareText) > 0)
    SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',', @VatValueWareText), 1, '.')
  SET @VatValueIndex3 = CONVERT(FLOAT, @VatValueWareText) 

  SELECT @VatValueWareText = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'VATValue4'
  IF (CHARINDEX(',', @VatValueWareText) > 0)
    SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',', @VatValueWareText), 1, '.')
  SET @VatValueIndex4 = CONVERT(FLOAT, @VatValueWareText) 

  SELECT @VatValueWareText = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'VATValue5'
  IF (CHARINDEX(',', @VatValueWareText) > 0)
    SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',', @VatValueWareText), 1, '.')
  SET @VatValueIndex5 = CONVERT(FLOAT, @VatValueWareText) 

  IF ISNULL(@VatValueIndex1, 0) = 0 SET @VatValueIndex1 = 0
  IF ISNULL(@VatValueIndex2, 0) = 0 SET @VatValueIndex2 = 0
  IF ISNULL(@VatValueIndex3, 0) = 0 SET @VatValueIndex3 = 0
  IF ISNULL(@VatValueIndex4, 0) = 0 SET @VatValueIndex4 = 0
  IF ISNULL(@VatValueIndex5, 0) = 0 SET @VatValueIndex5 = 0

  SELECT @ChargePacketDepartmentEnabled = CONVERT(BIT, KeyValue) FROM Hotelsetup WITH (NOLOCK) WHERE SearchKey = 'ChargePacketDepartmentEnabled'
  IF ISNULL(@ChargePacketDepartmentEnabled, 0) = 0 
    SET @ChargePacketDepartmentEnabled = 0

  DECLARE @WarePrice FLOAT
  DECLARE @WarePriceExVat FLOAT
  DECLARE @DebitType SMALLINT
  DECLARE @ResultAccountNumber INT 
  DECLARE @VatValueWare FLOAT
  DECLARE @VatValueNumber INT

  DECLARE @RoomPriceAdult MONEY
  DECLARE @RoomPriceChildren1 MONEY
  DECLARE @RoomPriceChildren2 MONEY

  DECLARE @tmpDepartmentNumber SMALLINT
  DECLARE @TheDepartmentNumber SMALLINT

  DECLARE @TotalPrice MONEY
  DECLARE @IsResidual BIT
  DECLARE @ResidualNeeded BIT

  IF ISNULL(@DayProduct, 0) = 0 
    SET @DayProduct = 0

  SELECT @IsFixedPriceProduct = FixedPrice 
  FROM Products WITH (NOLOCK) 
  WHERE ProductNumber = @ProductNumber

  SELECT @PackagesplitAfterReduction = CONVERT(BIT, KeyValue) FROM Hotelsetup WITH (NOLOCK) WHERE Searchkey = 'PackagesplitAfterReduction'
  SET @PackagesplitAfterReduction = ISNULL(@PackagesplitAfterReduction, 0)

  SELECT @SubWareFromReduction = CONVERT(INT, KeyValue) FROM Hotelsetup WITH(NOLOCK) WHERE Searchkey = 'SubWareFromReduction'
  SET @SubWareFromReduction = ISNULL(@SubWareFromReduction, 0)

  SELECT @RoomPriceAdult = Adult, @RoomPriceChildren1 = ChildPax1, @RoomPriceChildren2 = ChildPax2 
  FROM dbo.GetRoomPrice (@ProductNumber, @DateStamp, @RoomTypeNumber, @Adult, @Children1, @Children2, @NameListSetting, @PriceOverwritten, @NumberOfRooms, @FixedPrice)

  IF @PriceOverwritten = 1
    SET @RoomPriceAdult = @PriceoverwriteValue

  IF (@ChargePacketDepartmentEnabled = 1) 
  BEGIN
    Declare WareCur Cursor Forward_Only For
    SELECT DISTINCT
      PacketItem_New.SortNumber, Ware.ResultAccountNumber, PacketItem_New.PriceOverWrite, PacketItem_New.DebType, 
        Accounts.VatValueNumber, Departments.PK_Department, Ware.Type, Ware.WareNumber, WareGroups.SortNumber AS WaregroupNumber,
      CASE WHEN PacketItem_New.DebType IN (2,6,9) THEN 1 ELSE 0 END AS PercentDebType,
      Ware.WareName, CASE WHEN ISDATE(ArrangementLines.Time) = 1 THEN CONVERT(DATETIME, ArrangementLines.Time, 108) ELSE 0 END, CASE WHEN ISDATE(ArrangementLines.DepartureTime) = 1 THEN CONVERT(DATETIME, ArrangementLines.DepartureTime, 108) ELSE 0 END
    FROM Ware WITH (NOLOCK)
    INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
    INNER JOIN ACCOUNTS WITH (NOLOCK) ON  Ware.ResultAccountNumber = Accounts.AccountNumber
    INNER JOIN PacketItem_New WITH (NOLOCK) ON  Ware.WareNumber = PacketItem_New.WareNumber AND PacketItem_New.PacketNumber=@PacketNumber
    INNER JOIN ArrangementRoomXTab WITH (NOLOCK) ON ArrangementRoomXTab.PacketItemNumber = PacketItem_New.PacketItemNumber
    INNER JOIN ArrangementReservationsXTab WITH (NOLOCK) on ArrangementRoomXTab.ArrangementLineNumber = ArrangementReservationsXTab.ArrangementLineNumber
      AND ArrangementReservationsXTab.ReferenceNumber = @AReferenceNumber
      AND ((ISNULL(@GuestNumber, 0) > 0 AND ArrangementReservationsXTab.GuestNumber = @GuestNumber) OR ISNULL(@GuestNumber,0) = 0)
    INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementReservationsXTab.ArrangementLinenumber = ArrangementLines.ArrangementLinenumber 
      AND CONVERT(VARCHAR(8), ArrangementLines.[Date], 112) = @DateStamp
    LEFT OUTER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = Arrangementlines.FK_PacketChargeDepartment 
    LEFT OUTER JOIN PacketItem_NewRoomTypeXtab WITH (NOLOCK) ON PacketItem_NewRoomTypeXtab.PacketItemNumber = PacketItem_New.PacketItemNumber  
    WHERE ((ISNULL(PacketItem_NewRoomTypeXtab.RoomTypeNumber,-1) = -1) OR (PacketItem_NewRoomTypeXtab.RoomTypeNumber = @RoomTypeNumber))

    UNION

    SELECT DISTINCT
      PacketItem_New.SortNumber, Ware.ResultAccountNumber, PacketItem_New.PriceOverWrite, PacketItem_New.DebType, 
        Accounts.VatValueNumber, pd.FK_Department, Ware.Type, Ware.WareNumber, WareGroups.SortNumber AS WaregroupNumber,
      CASE WHEN PacketItem_New.DebType IN (2,6,9) THEN 1 ELSE 0 END AS PercentDebType,
      Ware.WareName, CASE WHEN ISDATE(PacketItem_New.Time) = 1 THEN CONVERT(DATETIME, PacketItem_New.Time, 108) ELSE 0 END, CASE WHEN ISDATE(PacketItem_New.DepartureTime) = 1 THEN CONVERT(DATETIME, PacketItem_New.DepartureTime, 108) ELSE 0 END
    FROM Ware WITH (NOLOCK)
    INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
    INNER JOIN ACCOUNTS WITH (NOLOCK) ON  Ware.ResultAccountNumber = Accounts.AccountNumber
    INNER JOIN PacketItem_New WITH (NOLOCK) ON  Ware.WareNumber = PacketItem_New.WareNumber AND PacketItem_New.PacketNumber=@PacketNumber
    LEFT OUTER JOIN PacketItem_NewRoomTypeXtab WITH (NOLOCK) ON PacketItem_NewRoomTypeXtab.PacketItemNumber = PacketItem_New.PacketItemNumber  
    LEFT OUTER JOIN PacketItem_NewDepartmentXTab pd WITH (NOLOCK) ON pd.PacketItemNumber = PacketItem_New.PacketItemNumber AND pd.Type = 1
    LEFT OUTER JOIN Departments d WITH (NOLOCK) ON pd.FK_Department = d.PK_Department
    WHERE ((@HotelNumber = -1) OR (d.HotelNumber = @HotelNumber))
    AND ((ISNULL(PacketItem_NewRoomTypeXtab.RoomTypeNumber,-1) = -1) OR (PacketItem_NewRoomTypeXtab.RoomTypeNumber = @RoomTypeNumber))
    AND (PacketItem_New.Arrangement = 0 or @ForAllotment = 1)
    ORDER BY PercentDebType
  END
  ELSE
  BEGIN
    Declare WareCur Cursor Forward_Only For
      SELECT DISTINCT
      PacketItem_New.SortNumber, Ware.ResultAccountNumber, PacketItem_New.PriceOverWrite, PacketItem_New.DebType, Accounts.VatValueNumber, 
      ISNULL(
        Departments.PK_Department,
        (
          SELECT TOP 1 d.PK_Department
          FROM Departments d
          WHERE d.HotelNumber = @HotelNumber
          AND ISNULL(d.MarkAsDeleted, 0) = 0 
          AND d.AutoCharge = 1
        )
      ), 
      Ware.Type, Ware.WareNumber, WareGroups.SortNumber AS WaregroupNumber,
      CASE WHEN PacketItem_New.DebType IN (2,6,9) THEN 1 ELSE 0 END AS PercentDebType,
      Ware.WareName, CASE WHEN ISDATE(ArrangementLines.Time) = 1 THEN CONVERT(DATETIME, ArrangementLines.Time, 108) ELSE 0 END, CASE WHEN ISDATE(ArrangementLines.DepartureTime) = 1 THEN CONVERT(DATETIME, ArrangementLines.DepartureTime, 108) ELSE 0 END
      FROM Ware WITH (NOLOCK)
      INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
      INNER JOIN ACCOUNTS WITH (NOLOCK) ON  Ware.ResultAccountNumber = Accounts.AccountNumber
      INNER JOIN PacketItem_New WITH (NOLOCK) ON  Ware.WareNumber = PacketItem_New.WareNumber AND PacketItem_New.PacketNumber=@PacketNumber
      INNER JOIN ArrangementRoomXTab WITH (NOLOCK) ON ArrangementRoomXTab.PacketItemNumber = PacketItem_New.PacketItemNumber
      INNER JOIN ArrangementReservationsXTab WITH (NOLOCK) on ArrangementRoomXTab.ArrangementLineNumber = ArrangementReservationsXTab.ArrangementLineNumber
        AND ArrangementReservationsXTab.ReferenceNumber = @AReferenceNumber
        AND ((ISNULL(@GuestNumber, 0) > 0 AND ArrangementReservationsXTab.GuestNumber = @GuestNumber) OR ISNULL(@GuestNumber,0) = 0)
      INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementReservationsXTab.ArrangementLinenumber = ArrangementLines.ArrangementLinenumber 
        AND CONVERT(VARCHAR(8), ArrangementLines.[Date], 112) = @DateStamp
      LEFT OUTER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = Arrangementlines.FK_PacketChargeDepartment 
      LEFT OUTER JOIN PacketItem_NewRoomTypeXtab WITH (NOLOCK) ON PacketItem_NewRoomTypeXtab.PacketItemNumber = PacketItem_New.PacketItemNumber  
      WHERE ((ISNULL(PacketItem_NewRoomTypeXtab.RoomTypeNumber,-1) = -1) OR (PacketItem_NewRoomTypeXtab.RoomTypeNumber = @RoomTypeNumber))

      UNION

      SELECT -1 AS SortNumber, Ware.ResultAccountNumber, PacketItem_New.PriceOverWrite, PacketItem_New.DebType, Accounts.VatValueNumber, 
      (
        SELECT TOP 1 d.PK_Department
        FROM Departments d
        WHERE d.HotelNumber = @HotelNumber
        AND ISNULL(d.MarkAsDeleted, 0) = 0 
        AND d.AutoCharge = 1
      ),
      Ware.Type, Ware.WareNumber, WareGroups.SortNumber AS WaregroupNumber,
      CASE WHEN PacketItem_New.DebType IN (2,6,9) THEN 1 ELSE 0 END AS PercentDebType,
      Ware.WareName, CASE WHEN ISDATE(PacketItem_New.Time) = 1 THEN CONVERT(DATETIME, PacketItem_New.Time, 108) ELSE 0 END, CASE WHEN ISDATE(PacketItem_New.DepartureTime) = 1 THEN CONVERT(DATETIME, PacketItem_New.DepartureTime, 108) ELSE 0 END
      FROM Ware WITH (NOLOCK)
      INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
      INNER JOIN PacketItem_New WITH (NOLOCK) ON Ware.WareNumber = PacketItem_New.WareNumber AND PacketItem_New.PacketNumber=@PacketNumber
      LEFT OUTER JOIN PacketItem_NewRoomTypeXtab WITH (NOLOCK) ON PacketItem_NewRoomTypeXtab.PacketItemNumber = PacketItem_New.PacketItemNumber  
      INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = Ware.ResultAccountNumber
      WHERE ((ISNULL(PacketItem_NewRoomTypeXtab.RoomTypeNumber,-1) = -1) OR (PacketItem_NewRoomTypeXtab.RoomTypeNumber = @RoomTypeNumber))
      AND (PacketItem_New.Arrangement = 0 or @ForAllotment = 1)
      ORDER BY PercentDebType
  END    
  Open WareCur

  Fetch Next From WareCur Into @DummySortNumber, @ResultAccountNumber, @WarePrice, @DebitType, @VatValueNumber, @tmpDepartmentNumber, @WareType, @WareNumber, @WareGroupNumber, @DummyPercentDebType, @WareName, @LFromTime, @LToTime

  Set @VatByPacketValue=0
  Set @PackagePrice = 0
  SET @WarePriceExVat = 0
  SET @FixAmountSum = 0
  SET @ReductionAfterSplitSum = 0

  While  (@@FETCH_STATUS <> -1)
  begin
    Set @Pieces = 1

    if IsNull(@WarePrice,0) = 0 
    Begin
      SET @WarePrice = 0
      SET @Pieces = 0
    End
    if IsNull(@DebitType,0)=0 
      Set @DebitType = 1
    
    if (@DebitType=1)  --Fixed price per room (adult)
    begin
      if (@Adult)=0
      Begin
        SET @WarePrice=0 
        SET @Pieces = 0
      End
    end
    else if (@DebitType=2) --% of room price (adult)
    begin
     if (@DayProduct=0) OR (@DayProduct=1 AND @IsFixedPriceProduct=1)
     BEGIN
       IF @NumberOfRooms < 0 
         SET @Pieces = @NumberOfRooms*-1
       ELSE
         SET @Pieces = @NumberOfRooms
     END 
     else
       SET @Pieces = @Adult

      if (@Adult)=0
        SET @WarePrice=0
      else
      begin
        if @PackagesplitAfterReduction = 0
          SET @WarePrice=@RoomPriceAdult*(@WarePrice/100)
        else
          SET @WarePrice=((@RoomPriceAdult - @FixAmountSum)*(@WarePrice/100)) - @ReductionAfterSplitSum
      end
    end
    else if (@DebitType=3)  --Price * Adult
    begin
      if (@Adult)=0
      Begin
        SET @WarePrice=0
        Set @Pieces = 0
      End Else
      Begin
        SET @WarePrice=@WarePrice*@Adult
        SET @Pieces = @Adult
      End
    end
    else if (@DebitType=4) --Fixed price per room (Children1)
    begin
      if (@Children1)=0
      Begin
        SET @WarePrice=0 
        Set @Pieces = 0
      End
    end
    else if (@DebitType=5) --Price * Children1
    begin
      if (@Children1)=0
      Begin
        SET @WarePrice=0
        Set @Pieces = 0
      End 
      Else
      Begin
        SET @WarePrice=@WarePrice * @Children1
        Set @Pieces = @Children1
      End
    end
    else if (@DebitType=6) --% of room price (Children1)
    begin
      if (@DayProduct=0) OR (@DayProduct=1 AND @IsFixedPriceProduct=1)
      BEGIN
        IF @NumberOfRooms < 0 
          SET @Pieces = @NumberOfRooms*-1
        ELSE
          SET @Pieces = @NumberOfRooms
      END 
      else
        SET @Pieces = @Children1

     if (@Children1)=0
       SET @WarePrice=0
     else
     begin
       if @PackagesplitAfterReduction = 0
         SET @WarePrice=@RoomPriceChildren1*(@WarePrice/100)
       else
         SET @WarePrice=((@RoomPriceChildren1 - @FixAmountSum)*(@WarePrice/100)) - @ReductionAfterSplitSum
      end
    end
    else if (@DebitType=7) --Fixed price per room (Children2)
    begin
      if (@Children2)=0
      Begin
        SET @WarePrice=0 
        Set @Pieces = 0
      End
    end
    else if (@DebitType=8) --Price * Children2
    begin
      if (@Children2)=0
      Begin
        SET @WarePrice=0
        Set @Pieces = 0
      End 
      Else
      Begin
        SET @WarePrice=@WarePrice*@Children2
        Set @Pieces = @Children2
      End
    end
    else if (@DebitType=9) --% of room price (Children2)
    begin
      if (@DayProduct=0) OR (@DayProduct=1 AND @IsFixedPriceProduct=1)
      BEGIN
        IF @NumberOfRooms < 0 
          SET @Pieces = @NumberOfRooms*-1
        ELSE
          SET @Pieces = @NumberOfRooms
      END 
      else
        SET @Pieces = @Children2

     if (@Children2)=0
        SET @WarePrice=0
      else
      begin
        if @PackagesplitAfterReduction = 0
          SET @WarePrice=@RoomPriceChildren2*(@WarePrice/100)
        else
          SET @WarePrice=((@RoomPriceChildren2 - @FixAmountSum)*(@WarePrice/100))  - @ReductionAfterSplitSum
      end
    end

    EXEC WareIsMenuWare @WareNumber, @WarePrice, @Pieces, @HandleAsMenuWare OUTPUT

  	if @ShowDebug = 1
	    Print 'CalcPacketPriceAccExt, @WareNumber=' + convert(varchar, @WareNumber) + ', @WarePrice=' + convert(varchar, @WarePrice) + ', @Pieces=' + convert(varchar, @Pieces) + ', @HandleAsMenuWare=' + convert(varchar, @HandleAsMenuWare)

    IF @HandleAsMenuWare = 1
    BEGIN
      SET @TotalPrice = 0

      DECLARE db_ware_cur CURSOR STATIC FOR
        SELECT mw.Procentage, Accounts.VatValueNumber, w.ResultAccountNumber, wg.SortNumber AS WaregroupNumber, ISNUll(MenuWare.InPercent, 0), w.WareName, w.WareNumber, mw.UseForResidualAmount
        FROM Ware w WITH (NOLOCK)          
        INNER JOIN MenuWareXTab mw WITH (NOLOCK) ON w.WareNumber = mw.WareNumber 
          AND mw.MenuNumber = @WareNumber
		    INNER JOIN WareGroups wg WITH (NOLOCK) ON wg.WareGroupNumber = w.WareGroupNumber
        INNER JOIN ACCOUNTS WITH (NOLOCK) ON w.ResultAccountNumber = Accounts.AccountNumber
		    INNER JOIN MenuWare WITH (NOLOCK) ON MenuWare.FK_MenuNumber = mw.MenuNumber
      OPEN db_ware_cur

      FETCH FIRST FROM db_ware_cur INTO @MenuProcentage, @MenuVatValueNumber, @MenuWareResultAccountNumber, @WareGroupNumber, @InPercent, @WareName, @WareNumber, @IsResidual
      WHILE (@@FETCH_STATUS <> -1)
      BEGIN
        IF @MenuProcentage > 0 AND @InPercent = 0
          SET @TotalPrice = @TotalPrice + (@MenuProcentage * @Pieces)

        FETCH NEXT FROM db_ware_cur INTO @MenuProcentage, @MenuVatValueNumber, @MenuWareResultAccountNumber, @WareGroupNumber, @InPercent, @WareName, @WareNumber, @IsResidual
      END

      FETCH FIRST FROM db_ware_cur INTO @MenuProcentage, @MenuVatValueNumber, @MenuWareResultAccountNumber, @WareGroupNumber, @InPercent, @WareName, @WareNumber, @IsResidual

      IF (ISNULL(@InPercent, 0) = 0) AND (@TotalPrice <> @WarePrice)
        SET @ResidualNeeded = 1
      ELSE
        SET @ResidualNeeded = 0

      WHILE @@FETCH_STATUS = 0
      BEGIN
        EXEC GetVatvalueFromVATValueNumber @MenuVatValueNumber, @MenuVatValueWare OUTPUT
		    IF @InPercent = 1
          SET @MenuWarePrice = ((@WarePrice * @MenuProcentage)/100.0)
		    ELSE
        BEGIN
          SET @MenuWarePrice = @MenuProcentage

          IF @ResidualNeeded = 1 AND @IsResidual = 1
          BEGIN
            IF @ShowDebug = 1
            BEGIN
              PRINT '@NewPrice BEFORE'
              PRINT @MenuWarePrice
            END
            SET @MenuWarePrice = @MenuWarePrice + @WarePrice - @TotalPrice
            SET @ResidualNeeded = 0

            IF @ShowDebug = 1
            BEGIN
              PRINT '@NewPrice AFTER'
              PRINT @MenuWarePrice
            END
          END
        END
      
        IF ISNULL(@NumberOfRooms, 0) > 1
          SET @MenuWarePrice = @MenuWarePrice * @NumberOfRooms

        SET @PackagePrice = ISNULL(@PackagePrice, 0) + ISNULL(@MenuWarePrice, 0)

        IF ISNULL(@DiscountValue, 0) <> 0 --AND @ReservationDiscount=1
          SET @MenuWarePrice = Round(@MenuWarePrice * (1-(@DiscountValue/100)),2)

        IF ISNULL(@MenuVatValueNumber, 0) > 0
        BEGIN
          SET @VatByPacketValue = @VatByPacketValue+(@MenuWarePrice*(1-(1/((@MenuVatValueWare/100)+1))))
          SET @WarePriceExVat = @MenuWarePrice-(@MenuWarePrice*(1-(1/((@MenuVatValueWare/100)+1))))
        END
        ELSE
          SET @WarePriceExVat = @MenuWarePrice

        IF ISNULL(@tmpDepartmentNumber, 0) > 0
          SET @TheDepartmentNumber = @tmpDepartmentNumber
        ELSE
          SET @TheDepartmentNumber = @PackageDepartment
        SELECT @HotelCustomerNumber = dbo.GetHotelCustomerNumberFromDepartment (@TheDepartmentNumber)

        IF (@MenuWarePrice <> 0 or @WarePriceExVat <> 0 or @Pieces <> 0) 
        BEGIN
          INSERT INTO #PacketWareTable (ResultAccountNumber, DepartmentNumber, PacketWarePrice, VatOfPacketWarePrice, WareGroupNumber, WareName, WareType, WareNumber, ProductWareLinkNumber, ProductNumber, BookedWareCount, HotelCustomerNumber, FromTime, ToTime)
	        VALUES (@MenuWareResultAccountNumber, @TheDepartmentNumber, @MenuWarePrice, @MenuWarePrice - @WarePriceExVat, @WareGroupNumber, @WareName, @WareType, @WareNumber, @Pwln, @ProductNumber, @Pieces, @HotelCustomerNumber, @LFromTime, @LToTime)
        END
        FETCH NEXT FROM db_ware_cur INTO @MenuProcentage, @MenuVatValueNumber, @MenuWareResultAccountNumber, @WareGroupNumber, @InPercent, @WareName, @WareNumber, @IsResidual
      END
      
      CLOSE db_ware_cur
      DEALLOCATE db_ware_cur
    END
    ELSE
    BEGIN
      SET @VatValueWare = 
      CASE @VatValueNumber
        WHEN 1 THEN @VatValueIndex1
        WHEN 2 THEN @VatValueIndex2
        WHEN 3 THEN @VatValueIndex3
        WHEN 4 THEN @VatValueIndex4
        WHEN 5 THEN @VatValueIndex5
      END

      IF ISNULL(@NumberOfRooms, 0) > 1
        SET @WarePrice = @WarePrice * @NumberOfRooms
 
      SET @PackagePrice = ISNULL(@PackagePrice, 0) + ISNULL(@WarePrice, 0)

      IF ISNULL(@DiscountValue, 0) <> 0 --AND @ReservationDiscount=1
        SET @WarePrice = Round(@WarePrice * (1-(@DiscountValue/100)),2)
 
      IF ISNULL(@VatValueNumber, 0) > 0
      BEGIN
        Set @VatByPacketValue=@VatByPacketValue+(@WarePrice*(1-(1/((@VatValueWare/100)+1))))
        SET @WarePriceExVat = @WarePrice-(@WarePrice*(1-(1/((@VatValueWare/100)+1))))
      END
      ELSE
        SET @WarePriceExVat = @WarePrice

      IF @ShowDebug = 1
      BEGIN
        IF @PacketNumber=2
        BEGIN
          PRINT '@ResultAccountNumber'
          PRINT @ResultAccountNumber
          PRINT '@VatValueWare'
          PRINT @VatValueWare
          PRINT '@VatValueNumber'
          PRINT @VatValueNumber
          PRINT '@WarePrice'
          PRINT CONVERT(CHAR, @WarePrice)
          PRINT '@WarePriceExVat'
          PRINT CONVERT(CHAR, @WarePriceExVat)
          PRINT '@PackagePrice'
          PRINT CONVERT(CHAR, @PackagePrice)
        END
      END
  
      IF ISNULL(@tmpDepartmentNumber, 0) > 0
        SET @TheDepartmentNumber = @tmpDepartmentNumber
      ELSE
        SET @TheDepartmentNumber = @PackageDepartment
      SELECT @HotelCustomerNumber = dbo.GetHotelCustomerNumberFromDepartment (@TheDepartmentNumber)

      IF (@WarePrice <> 0 or @WarePriceExVat <> 0)
	    BEGIN
        INSERT INTO #PacketWareTable (ResultAccountNumber, DepartmentNumber, PacketWarePrice, VatOfPacketWarePrice, WareGroupNumber, WareName, WareType, WareNumber, ProductWareLinkNumber, ProductNumber, BookedWareCount, HotelCustomerNumber, FromTime, ToTime)
        VALUES (@ResultAccountNumber, @TheDepartmentNumber, @WarePrice, @WarePrice - @WarePriceExVat, @WareGroupNumber, @WareName, @WareType, @WareNumber, @Pwln, @ProductNumber, @Pieces, @HotelCustomerNumber, @LFromTime, @LToTime)
      END
    END  
   
    if @PackageSplitAfterReduction = 1
    Begin
      if @SubWareFromReduction = @WareNumber
        SET @ReductionAfterSplitSum = @ReductionAfterSplitSum + @WarePrice
      else if NOT(@DebitType in (2,6,9)) 
        SET @FixAmountSum = @FixAmountSum + @WarePrice
    End

    Fetch Next From WareCur Into @DummySortNumber, @ResultAccountNumber, @WarePrice, @DebitType, @VatValueNumber, @tmpDepartmentNumber, @WareType, @WareNumber, @WareGroupNumber, @DummyPercentDebType, @WareName, @LFromTime, @LToTime
  end

  CLOSE WareCur
  Deallocate WareCur

  if IsNull(@PackagePrice,0)=0
    Set @PackagePrice = 0

  if IsNull(@VatByPacketValue,0)=0 
   SET @VatByPacketValue = 0

  if @NamelistSetting = 1 AND @OnePersonPaysForAll=0
  begin
    SET @PackagePrice = @PackagePrice * ISNULL((@TempAdult + @TempChild1 + @TempChild2), 1)
    SET @VatByPacketValue = @VatByPacketValue * ISNULL((@TempAdult + @TempChild1 + @TempChild2), 1)
  end
end
else
begin
  SET @PackagePrice = 0
  SET @VatByPacketValue = 0
end