CREATE PROCEDURE [dbo].[GenerateArrangement] @Old_ProductNumber int, @New_ProductNumber int, @Old_PacketNumber int, @New_PacketNumber int, @Old_StartDate datetime, @New_StartDate datetime, @Old_EndDate datetime, @New_Enddate datetime, @MainResArrival datetime,
@MainResDeparture datetime, @Referencenumber int, @GuestNumber int, @Old_NumberOfPersons int, @New_NumberOfpersons int, @Old_NumberOfChildren int, @New_NumberOfChildren int, @Old_NumberOfChildren2 int, @New_NumberOfChildren2 int, @Roomline int, @FromProduct int, @Linenumber int, @Old_pieces int, @New_pieces int, @DiscountPercent money = 0, @HotelNumber int = -1, @AFromRecreate bit = 0, @HotelCustomerNumber int = -1
AS
  SET NOCOUNT ON


  -- 20150506 RN: Bug fixes, Rewrite how we update pieces, missing ArrangementRoomXtab entries is now added when using "Use Arrangement Tolerance"
  -- 20150302 RN: Modified "SuggestHallNumberOnPacketLine" setting to be Domino aware.
  -- 20141204 RN: Added check for "Use Arrangement tolerance" setting.
  -- sidst rettet 2012 1121 RN - Replaced IS NULL with ISNULL function
  -- sidst rettet 2009 0909 RN - Added check for tolerance use.
  -- sidst rettet 2008 0409 fix for writing departure time on arrangementline even thoug packet has no departuretime speficied.
  -- sidsr rettet 20070621 Pretty big rewrite when selecting the sql for the packetitems. Code made more pretty and IT NOW SUPPORTS Children2 (A WISH FOR TECHOTEL SWEDEN )
  -- sidst rettet 20070621 call of UDF IsDepartmentInHotelList removed. Left outer Joins used instead
  -- sidst rettet 20070410 New Department Stuff NOT COMPATIBLE WITH 3.38
  -- sidst rettet 20060905 sql optimized in the block where the manual lines is moved
  -- sidst rettet 20060621 new variable : @PacketChargeDepartment
  -- sidst rettet 20060601 new parameter: @HotelNumber
  -- sidst rettet 20060507 ikke kompatibel med 3.36 FK_exdepartment
  -- sidst rettet 2005 1228 : arrival and departuretime is relative to hall arrival time
  -- sidst rettet 2005 0909 departuretime added to PacketItem_New table and this value is transferred to arrangementline. Only if BookTimeInMinutes is null
  -- sidst rettet 20050330 discount only if ware allows it
  -- sidst rettet 20050305 new paratmeter added. discountpercent
  -- sidst ændret 20041213 take number of minutes from ware and add it to time
  -- sidst ændret 20041123 hall number set to null if halline not found
  -- sidst ændret 20040811 select replaced with "if exists" to avoid returning datasets
  -- Note:
  --   The Parameter roomline is a boolean
  --   The Parameter fromProduct is a boolean (indicates whether the arrangement should be generated from a product or a packet.

  DECLARE @PacketItemNumber int
  DECLARE @PacketNumber int
  DECLARE @WareNumber int
  DECLARE @Time char(5)
  DECLARE @Time2 char(5)
  DECLARE @DepTime char(5)
  DECLARE @DepartureTime char(5)
  DECLARE @TextOverWrite char(80)
  DECLARE @PriceOverWrite real
  DECLARE @DebType int
  DECLARE @PK_Department int
  DECLARE @Note varchar(4000)
  DECLARE @BookTimeInMinutes int
  DECLARE @reservationdiscount bit
  DECLARE @PacketChargeDepartment int
  DECLARE @PacketItemMandatory bit
  DECLARE @WareHasRule bit
  DECLARE @FK_RecurringPatternId int
  DECLARE @IsArrLineMoved bit  
  DECLARE @FK_WareLinkNumber int

  DECLARE @PersonRessourceBookingEnabled bit
  DECLARE @ChargePacketDepartmentEnabled bit

  --New Variables 20051228 Used for adjust packetTime to the arrivaltime for the hall relatively
  DECLARE @AdjustTimeRelative bit
  DECLARE @HallArrivalTime char(5)
  DECLARE @AddMinutes int
  DECLARE @MinPacketTime char(5)
  DECLARE @Arr24HoursStarts char(5)

  DECLARE @TotalPrice real
  DECLARE @WeReadFromPacket int

  DECLARE @ArrivalPacket int
  DECLARE @IdentityValue int --used to get the key value from the Identitytable for arrangementlines
  DECLARE @Pieces int
  DECLARE @TempPieces int
  DECLARE @newPrice real

  DECLARE @found int
  DECLARE @Savelinenumber int
  DECLARE @Tolerance char(5)
  DECLARE @USETOLERANCE bit
  DECLARE @AdultDayProductPrice real
  DECLARE @ChildDayProductPrice real

  --new variables 220102
  DECLARE @FoundPacketItemNumber int
  DECLARE @FoundPrice real
  DECLARE @FoundPieces int

  DECLARE @ArrNumber int
  DECLARE @ConfID int
  DECLARE @ComeFromProduct int --it indicates whether an arrangementline comes from a reservationline with or without product
  DECLARE @PlusProductLineNumber int
  DECLARE @TempArrNumber int
  DECLARE @TempToleranceArrNumber int
  DECLARE @DateStamp char(8)
  DECLARE @HallNumber int

  --Declare and initiate variables for looping through the days of the arrangement

  DECLARE @FormatedStartDate char(8)
  DECLARE @FormatedEndDate char(8)
  DECLARE @FormatedOldStartDate char(8)
  DECLARE @FormatedOldEnddate char(8)
  DECLARE @CurrentDate char(8)
  DECLARE @FormatedMainResArrival char(8)
  DECLARE @FormatedMainResDeparture char(8)
  DECLARE @OffSet int
  DECLARE @NewArrangementDate datetime
  DECLARE @hallLineID int
  DECLARE @SuggestHallNumberOnPacketLine int
  DECLARE @CustomerNumber int

  DECLARE @CurrentDateDT datetime

  DECLARE @CurrentDateAndTime DATETIME
  DECLARE @OriginalStartDateAndTime DATETIME
  DECLARE @OriginalEndDateAndTime DATETIME

  DECLARE @MainCourseResNoArrPacketLines BIT

  DECLARE @TimeDiff datetime
  DECLARE @ToleranceDiff INT
  DECLARE @DTTime datetime
  DECLARE @OrigArrTime TIME
  DECLARE @OrigDepTime TIME
  DECLARE @OrigArrDate DATE
  DECLARE @OrigDepDate DATE
  DECLARE @DTArr24HoursStart TIME
  DECLARE @PacketItemTime TIME
  DECLARE @AdjustedArrDatetime DATETIME
  DECLARE @AdjustedDepDatetime DATETIME

  DECLARE @ArrLinePacketItemNumber INT

  SET @OriginalStartDateAndTime = @New_StartDate
  SET @OriginalEndDateAndTime = @New_Enddate
  SET @OrigArrDate = CAST(@OriginalStartDateAndTime AS DATE)
  SET @OrigArrTime = CAST(@OriginalStartDateAndTime AS TIME)
  SET @OrigDepDate = CAST(@OriginalEndDateAndTime AS DATE)
  SET @OrigDepTime = CAST(@OriginalEndDateAndTime AS TIME)

  -- Other
  DECLARE @MyRowCount int
  DECLARE @IsNewArrangement int
  DECLARE @FromNameList int

  DECLARE @UseAdjustArrLinesToBookingTime BIT -- RQ3119

  SELECT @UseAdjustArrLinesToBookingTime = CONVERT(bit, KeyValue) 
  FROM HotelSetup WITH (NOLOCK)
  WHERE SearchKey = 'ArrAdjustPacketLinesFromBookingTime'


  SELECT @Arr24HoursStarts = KeyValue 
  FROM HotelSetUp WITH (NOLOCK)
  WHERE SearchKey = 'Arr24HoursStarts'
  SET @DTArr24HoursStart = CAST(@Arr24HoursStarts AS TIME)
  
  SET @FromNameList = 0
  SET @IsArrLineMoved = 0

  SELECT @FormatedStartDate = CONVERT(char(10), @New_StartDate, 112)
  SELECT @FormatedEndDate = CONVERT(char(10), @New_EndDate, 112)
  SELECT @FormatedOldEndDate = CONVERT(char(10), @Old_EndDate, 112)

  SELECT @FormatedOldStartDate = CONVERT(char(10), @Old_StartDate, 112)
  SELECT @FormatedMainResArrival = CONVERT(char(10), @MainResArrival, 112)
  SELECT @FormatedMainResDeparture = CONVERT(char(10), @MainResDeparture, 112)

  SELECT @CurrentDate = CONVERT(char(10), @New_StartDate, 112)

  SELECT @PersonRessourceBookingEnabled = 0

  SELECT @PersonRessourceBookingEnabled = CONVERT(bit, KeyValue)
  FROM HotelSetUp WITH (NOLOCK)
  WHERE SearchKey LIKE 'useDepTimeInArrangement'

  SELECT @ChargePacketDepartmentEnabled = CONVERT(integer, ISNULL(KeyValue, 0))
  FROM HotelSetup WITH (NOLOCK)
  WHERE searchkey = 'ChargePacketDepartmentEnabled'

  SET @CustomerNumber = @HotelCustomerNumber 
  IF @CustomerNumber = -1
  BEGIN
    SELECT @CustomerNumber = CustomerNumber
    FROM Customer WITH (NOLOCK)
    WHERE HotelNumber = @HotelNumber
      AND CustomerTypeNumber = 7
  END
  
  ------------------------------------------------------------------------------------------------------------------------------------
  -- Cache package info and product prices for performance

  IF @AFromRecreate = 0
  BEGIN
    CREATE TABLE #PackageInfo
    (
      View_DateStamp DATETIME,
      View_Id INT,
      View_FK_ProductNumber INT,
      View_PacketType INT
    )
  END

  CREATE TABLE #TempPacketItems (
        PacketItemNumber int,
        PacketNumber int,
        WareNumber int,
        Time char(5),
        DepartureTime char(5),
        TextOverWrite varchar(255),
        PriceOverWrite money,
        debtype int,
        note varchar(4000),
        ReservationDisCount tinyint,
        BookTimeInMinutes int,
		PacketItemMandatory bit,
		FK_RecurringPatternId int,
        FK_WareLinkNumber int
  )

  --print @new_startdate
  --print @new_enddate
  --print @New_ProductNumber

  DECLARE @TempStartDate date
  DECLARE @TempEndDate date
  DECLARE @PackageCount int

  SET @TempStartDate = CAST(@New_StartDate AS date)
  SET @TempEndDate = CAST(@New_Enddate as date)

  IF @AFromRecreate = 0
  BEGIN
    DECLARE @MinArrivalDate VARCHAR(8)
    DECLARE @MaxDepartureDate VARCHAR(8)
	DECLARE @ProductNumber INT
    
    SELECT @MinArrivalDate = MIN(ArrivalDate), @MaxDepartureDate = MAX(DepartureDate) FROM RESERVATIONS (NOLOCK) WHERE ReferenceNumber = @Referencenumber

    SET @TempStartDate = CAST(@MinArrivalDate AS date)
    SET @TempEndDate = CAST(@MaxDepartureDate as date)

    DECLARE TmpCursor CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT ISNULL(Productnumber,-1) FROM Reservations (NOLOCK) WHERE ReferenceNumber = @Referencenumber
      UNION
      SELECT ISNULL(ProductNumber2,-1) FROM Reservations (NOLOCK) WHERE ReferenceNumber = @Referencenumber
      UNION
      SELECT ISNULL(Productnumber3,-1) FROM Reservations (NOLOCK) WHERE ReferenceNumber = @Referencenumber
      UNION
      SELECT ISNULL(Productnumber4,-1) FROM Reservations (NOLOCK) WHERE ReferenceNumber = @Referencenumber
	  UNION
	  SELECT ISNULL(ProductNumber, -1) FROM RESERVATIONROOMLINE (NOLOCK)
	  INNER JOIN RESERVATIONROOMLINEXTAB (NOLOCK) ON RESERVATIONROOMLINEXTAB.LINENUMBER = RESERVATIONROOMLINE.LINENUMBER
	    AND RESERVATIONROOMLINEXTAB.ReferenceNumber = @Referencenumber
      UNION
	  SELECT ISNULL(ProductNumber2, -1) FROM RESERVATIONROOMLINE (NOLOCK)
	  INNER JOIN RESERVATIONROOMLINEXTAB (NOLOCK) ON RESERVATIONROOMLINEXTAB.LINENUMBER = RESERVATIONROOMLINE.LINENUMBER
	    AND RESERVATIONROOMLINEXTAB.ReferenceNumber = @Referencenumber
      UNION
	  SELECT ISNULL(ProductNumber3, -1) FROM RESERVATIONROOMLINE (NOLOCK)
	  INNER JOIN RESERVATIONROOMLINEXTAB (NOLOCK) ON RESERVATIONROOMLINEXTAB.LINENUMBER = RESERVATIONROOMLINE.LINENUMBER
	    AND RESERVATIONROOMLINEXTAB.ReferenceNumber = @Referencenumber
      UNION
	  SELECT ISNULL(ProductNumber4, -1) FROM RESERVATIONROOMLINE (NOLOCK)
	  INNER JOIN RESERVATIONROOMLINEXTAB (NOLOCK) ON RESERVATIONROOMLINEXTAB.LINENUMBER = RESERVATIONROOMLINE.LINENUMBER
  	    AND RESERVATIONROOMLINEXTAB.ReferenceNumber = @Referencenumber
      UNION
      SELECT ISNULL(Productnumber,-1) FROM ReservationConferenceline (NOLOCK) WHERE ReferenceNumber = @Referencenumber
    OPEN TmpCursor
    FETCH NEXT FROM TmpCursor INTO @ProductNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF @ProductNumber > 0
        INSERT INTO #PackageInfo EXEC ProductPricePacketInfo @TempStartDate, @TempEndDate, @ProductNumber  
      FETCH NEXT FROM TmpCursor INTO @ProductNumber
    END
    CLOSE TmpCursor
    DEALLOCATE TmpCursor
  END
  ELSE
  BEGIN
    SELECT @PackageCount = COUNT(*) FROM #PackageInfo WITH (NOLOCK) WHERE View_FK_ProductNumber = @New_ProductNumber AND View_DateStamp BETWEEN @TempStartDate AND @TempEndDate
	IF @PackageCount = 0
      INSERT INTO #PackageInfo EXEC ProductPricePacketInfo @TempStartDate, @TempEndDate, @New_ProductNumber
  END

  --SELECT @PackageCount = COUNT(*) FROM #PackageInfo
  --Print @PackageCount

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  ---THE FOLLOWING PIECE OF CODE IS USED IF PRODUCT HAS CHANGED ON THE RESERVATIONLINE ...  

  IF ((@New_productNumber <> @Old_ProductNumber)) -- or (@New_ProductNumber = 0)) --and (@FromProduct=1)  --Another product is chosen or the reservationline has been deleted (The product has been deleted from the reservationLine)
  BEGIN
    DECLARE DeleteCur CURSOR FORWARD_ONLY FOR
    SELECT
      ArrangementLineNumber,
      Linenumber,
      PlusProductLineNumber,
      PacketItemNumber
    FROM ArrangementRoomXtab WITH (NOLOCK)
    WHERE LineNumber = @LineNumber

    OPEN DeleteCur
    FETCH NEXT FROM DeleteCur INTO @ArrNumber, @confID, @PacketItemNumber, @PlusProductLineNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF NOT EXISTS (SELECT
          ArrangementLineNumber
        FROM ArrangementRoomXtab WITH (NOLOCK)
        WHERE ArrangementLineNumber = @ArrNumber
        AND LineNumber <> @LineNumber)
      BEGIN
	    SELECT @ArrLinePacketItemNumber = ISNULL(PacketItemNumber, 0) FROM ArrangementLines (NOLOCK) WHERE ArrangementLineNumber= @ArrNumber
		IF @ArrLinePacketItemNumber > 0
		BEGIN
          DELETE FROM ArrangementRoomXtab
          WHERE ArrangementLineNumber = @Arrnumber
          DELETE FROM ArrangementReservationsXtab
          WHERE ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementLineDepartmentXTab
          WHERE FK_ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementReservationConferenceLineXtab
          WHERE ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementLines
          WHERE ArrangementLineNumber = @ArrNumber
		END
      END
      ELSE
      BEGIN
	    SELECT @ArrLinePacketItemNumber = ISNULL(PacketItemNumber, 0) FROM ArrangementLines (NOLOCK) WHERE ArrangementLineNumber= @ArrNumber

        SELECT
          @DebType = PacketItem_New.DebType
        FROM ArrangementLines WITH (NOLOCK)
        INNER JOIN ArrangementRoomXtab WITH (NOLOCK)
          ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
        INNER JOIN PacketItem_NEW WITH (NOLOCK)
          ON PacketItem_NEW.PacketItemNumber = ArrangementLines.PacketItemNumber
        WHERE ArrangementRoomXtab.ArrangementLineNumber = @ArrNumber
        AND ArrangementRoomXtab.Linenumber = @Linenumber

        IF @DebType IN (1, 4, 7)
          SELECT
            @Pieces = 1 * @Old_pieces
        ELSE
        IF @Debtype IN (5, 6)
          SELECT
            @Pieces = @Old_NumberOfChildren * @Old_Pieces
        ELSE
        IF @Debtype IN (8, 9)
          SELECT
            @Pieces = @Old_NumberOfChildren2 * @Old_Pieces
        ELSE --debtype is 2 or 3....
          SELECT
            @Pieces = @Old_NumberOfPersons * @Old_Pieces

        UPDATE ArrangementLines
        SET Pieces = Pieces - @Pieces
        WHERE ArrangementLineNumber = @ArrNumber

        SELECT
          @Pieces = Pieces
        FROM ArrangementLines WITH (NOLOCK)
        WHERE ArrangementLineNumber = @Arrnumber --If pieces in an arrangementline has turned to 0 then delete it
        IF @Pieces = 0
        BEGIN
		  IF @ArrLinePacketItemNumber > 0
		  BEGIN
            DELETE FROM ArrangementRoomXtab
            WHERE ArrangementLineNumber = @Arrnumber
            DELETE FROM ArrangementReservationsXtab
            WHERE ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementLineDepartmentXTab
            WHERE FK_ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementReservationConferenceLineXtab
            WHERE ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementLines
            WHERE ArrangementLineNumber = @Arrnumber
		  END
        END
        DELETE FROM ArrangementRoomXtab
        WHERE LineNumber = @LineNumber
          AND ArrangementLineNumber = @Arrnumber --should we also delete from arrangeemntreservationsxtab yes we should
      --we can only delete from the cross table if no other reservationilines point at the specific arrangementline  and we know that at least one more reservationline does that.
      --delete from ArrangementReservationsXtab where arrangementLineNumber = @ArrNumber and referencenumber= @Referencenumber and guestNumber=@GuestNumber
      END

      FETCH NEXT FROM DeleteCur INTO @ArrNumber, @ConfID, @PacketItemNumber, @PlusProductLineNumber
    END -- While (@@FETCH_STATUS <> -1)

    CLOSE DeleteCur
    DEALLOCATE DeleteCur

  END -- if ((@New_productNumber <>@Old_ProductNumber))

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  ---THE FOLLOWING PIECE OF CODE IS USED IF PACKET HAS CHANGED ON THE RESERVATIONLINE ...   (IT MEANS THAT THE RESERVATIONLINE DOESNT HAVE A PRODUCT BUT A PACKET)

  IF ((@New_PacketNumber <> @Old_PacketNumber))  --or (@New_PacketNumber = 0)) --and (@FromProduct=0)  --Another packet is chosen or the reservationline has been deleted (or the packet has been deleted from the reservationLine)
  BEGIN
    DECLARE DeleteCur CURSOR FORWARD_ONLY FOR
    SELECT
      ArrangementLineNumber,
      Linenumber,
      PlusProductLineNumber,
      PacketItemNumber
    FROM ArrangementRoomXtab WITH (NOLOCK)
    WHERE LineNumber = @LineNumber
    OPEN DeleteCur
    FETCH NEXT FROM DeleteCur INTO @ArrNumber, @ConfID, @PacketItemNumber, @PlusProductLineNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF NOT EXISTS (SELECT
          ArrangementLineNumber
        FROM ArrangementRoomXtab WITH (NOLOCK)
        WHERE ArrangementLineNumber = @ArrNumber
        AND Linenumber <> @LineNumber)
      BEGIN
	    SELECT @ArrLinePacketItemNumber = ISNULL(PacketItemNumber, 0) FROM ArrangementLines (NOLOCK) WHERE ArrangementLineNumber= @ArrNumber
		IF @ArrLinePacketItemNumber > 0
		BEGIN
          DELETE FROM ArrangementRoomXtab
          WHERE ArrangementLineNumber = @Arrnumber
          DELETE FROM ArrangementReservationsXtab
          WHERE ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementLineDepartmentXTab
          WHERE FK_ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementReservationConferenceLineXtab
          WHERE ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementLines
          WHERE ArrangementLineNumber = @ArrNumber
		END
      END
      ELSE
      BEGIN
        SELECT
          @DebType = PacketItem_New.DebType
        FROM ArrangementLines WITH (NOLOCK)
        INNER JOIN ArrangementRoomXtab WITH (NOLOCK)
          ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
        INNER JOIN PacketItem_NEW WITH (NOLOCK)
          ON PacketItem_NEW.PacketItemNumber = ArrangementLines.PacketItemNumber
        WHERE ArrangementRoomXtab.ArrangementLineNumber = @ArrNumber
        AND ArrangementRoomXtab.Linenumber = @Linenumber

        IF @DebType IN (1, 4, 7)
          SELECT
            @Pieces = 1 * @Old_Pieces
        ELSE
        IF @Debtype IN (5, 6)
          SELECT
            @Pieces = @Old_NumberOfChildren * @Old_Pieces
        ELSE
        IF @Debtype IN (8, 9)
          SELECT
            @Pieces = @Old_NumberOfChildren2 * @Old_Pieces
        ELSE --debtype is 2 or 3....
          SELECT
            @Pieces = @Old_NumberOfPersons * @Old_Pieces

        UPDATE ArrangementLines
        SET Pieces = Pieces - @Pieces
        WHERE ArrangementLineNumber = @ArrNumber
        SELECT
          @Pieces = Pieces
        FROM ArrangementLines WITH (NOLOCK)
        WHERE ArrangementLineNumber = @Arrnumber --If pieces in an arrangementline has turned to 0 then delete it
        IF @Pieces = 0
        BEGIN
 	      SELECT @ArrLinePacketItemNumber = ISNULL(PacketItemNumber, 0) FROM ArrangementLines (NOLOCK) WHERE ArrangementLineNumber= @ArrNumber
		  IF @ArrLinePacketItemNumber > 0
		  BEGIN
            DELETE FROM ArrangementRoomXtab
            WHERE ArrangementLineNumber = @Arrnumber
            DELETE FROM ArrangementReservationsXtab
            WHERE ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementLineDepartmentXTab
            WHERE FK_ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementReservationConferenceLineXtab
            WHERE ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementLines
            WHERE ArrangementLineNumber = @Arrnumber
		  END
        END
        DELETE FROM ArrangementRoomXtab
        WHERE LineNumber = @LineNumber
          AND ArrangementLineNumber = @Arrnumber
      -- we can only delete from the cross table if no other reservationilines point at the specific arrangementline  and we know that at least one more reservationline does that.
      -- delete from ArrangementReservationsXtab where arrangementLineNumber = @ArrNumber and referencenumber= @Referencenumber and guestNumber=@GuestNumber
      END

      FETCH NEXT FROM DeleteCur INTO @ArrNumber, @ConfID, @PacketItemNumber, @PlusProductLineNumber
    END -- While  (@@FETCH_STATUS <> -1)

    CLOSE DeleteCur
    DEALLOCATE DeleteCur
  END -- if ((@New_PacketNumber <> @Old_PacketNumber))

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  ---THE FOLLOWING PIECE OF CODE DELETES NECESSARY ARRANGEMENTLINES IF DATE HAS CHANGED ON THE RESERVATIONLINE. 

  IF @Old_EndDate > @New_Enddate
  BEGIN
    DECLARE DeleteCursor CURSOR FORWARD_ONLY FOR
    SELECT DISTINCT
      ArrangementLines.ArrangementLineNumber
    FROM ArrangementLines WITH (NOLOCK)
    INNER JOIN ArrangementRoomXtab WITH (NOLOCK)
      ON ArrangementLines.ArrangementLineNumber = ArrangementRoomXtab.ArrangementLineNumber
    WHERE (Date >= CONVERT(char(8), @New_Enddate + 1, 112))
    AND (Date <= CONVERT(char(8), @Old_EndDate, 112))
    AND (ArrangementRoomXtab.Linenumber = @Linenumber)

    OPEN DeleteCursor
    FETCH NEXT FROM DeleteCursor INTO @ArrNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF NOT EXISTS (SELECT
          ArrangementLineNumber
        FROM ArrangementRoomXtab WITH (NOLOCK)
        WHERE ArrangementLineNumber = @ArrNumber
        AND Linenumber <> @Linenumber)
      BEGIN
	    SELECT @ArrLinePacketItemNumber = ISNULL(PacketItemNumber, 0) FROM ArrangementLines (NOLOCK) WHERE ArrangementLineNumber= @ArrNumber
		IF @ArrLinePacketItemNumber > 0
		BEGIN
          DELETE FROM ArrangementRoomXtab
          WHERE ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementReservationsXtab
          WHERE ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementLineDepartmentXTab
          WHERE FK_ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementReservationConferenceLineXtab
          WHERE ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementLines
          WHERE ArrangementLineNumber = @ArrNumber
		END
      END
      ELSE --Another reservationline points at this arrangementline so we cannot delete the arrrangementline, we have to update it
      BEGIN
        SELECT
          @DebType = PacketItem_New.DebType
        FROM ArrangementLines WITH (NOLOCK)
        INNER JOIN ArrangementRoomXtab WITH (NOLOCK)
          ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
        INNER JOIN PacketItem_NEW WITH (NOLOCK)
          ON PacketItem_NEW.PacketItemNumber = ArrangementLines.PacketItemNumber
        WHERE ArrangementRoomXtab.ArrangementLineNumber = @ArrNumber
        AND ArrangementRoomXtab.Linenumber = @Linenumber

        IF @DebType IN (1, 4, 7)
          SELECT
            @Pieces = 1 * @Old_pieces
        ELSE
        IF @DebType IN (5, 6)
          SELECT
            @Pieces = @Old_NumberOfChildren * @Old_Pieces
        ELSE
        IF @DebType IN (8, 9)
          SELECT
            @Pieces = @Old_NumberOfChildren2 * @Old_Pieces
        ELSE --debtype is 2 or 3....
          SELECT
            @Pieces = @Old_NumberOfPersons * @Old_Pieces

        UPDATE ArrangementLines
        SET Pieces = Pieces - @Pieces
        WHERE ArrangementLineNumber = @ArrNumber

        SELECT
          @Pieces = Pieces
        FROM ArrangementLines WITH (NOLOCK)
        WHERE ArrangementLineNumber = @Arrnumber --If pieces in an arrangementline has turned to 0 then delete it
        IF @Pieces = 0
        BEGIN
  	      SELECT @ArrLinePacketItemNumber = ISNULL(PacketItemNumber, 0) FROM ArrangementLines (NOLOCK) WHERE ArrangementLineNumber= @ArrNumber
		  IF @ArrLinePacketItemNumber > 0
		  BEGIN
            DELETE FROM ArrangementRoomXtab
            WHERE ArrangementLineNumber = @Arrnumber
            DELETE FROM ArrangementReservationsXtab
            WHERE ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementLineDepartmentXTab
            WHERE FK_ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementReservationConferenceLineXtab
            WHERE ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementLines
            WHERE ArrangementLineNumber = @Arrnumber
		  END
        END
        DELETE FROM ArrangementRoomXtab
        WHERE LineNumber = @LineNumber
          AND ArrangementLineNumber = @Arrnumber --230101 is this correct
      -- we can only delete from the cross table if no other reservationilines point at the specific arrangementline  and we know that at least one more reservationline does that.
      -- delete from ArrangementReservationsXtab where arrangementLineNumber = @ArrNumber and referencenumber= @Referencenumber and guestNumber=@GuestNumber
      END
      FETCH NEXT FROM DeleteCursor INTO @ArrNumber
    END
    CLOSE Deletecursor
    DEALLOCATE Deletecursor
  END

  IF @Old_StartDate < @New_StartDate
  BEGIN
    DECLARE DeleteCur CURSOR FORWARD_ONLY FOR
    SELECT DISTINCT
      ArrangementLines.ArrangementLineNumber
    FROM ArrangementLines WITH (NOLOCK)
    INNER JOIN ArrangementRoomXtab WITH (NOLOCK)
      ON ArrangementLines.ArrangementLineNumber = ArrangementRoomXtab.ArrangementLineNumber
    WHERE (Date >= CONVERT(char(8), @Old_Startdate, 112))
    AND (Date < CONVERT(char(8), @New_Startdate, 112))
    AND (ArrangementRoomXtab.Linenumber = @Linenumber)

    OPEN DeleteCur
    FETCH NEXT FROM DeleteCur INTO @ArrNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
	  SELECT @ArrLinePacketItemNumber = ISNULL(PacketItemNumber, 0) FROM ArrangementLines (NOLOCK) WHERE ArrangementLineNumber= @ArrNumber
      IF NOT EXISTS (SELECT
          ArrangementLineNumber
        FROM ArrangementRoomXtab WITH (NOLOCK)
        WHERE ArrangementLineNumber = @ArrNumber
        AND Linenumber <> @Linenumber)
      BEGIN
		IF @ArrLinePacketItemNumber > 0
		BEGIN
          DELETE FROM ArrangementRoomXtab
          WHERE ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementReservationsXtab
          WHERE ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementLineDepartmentXTab
          WHERE FK_ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementReservationConferenceLineXtab
          WHERE ArrangementLineNumber = @ArrNumber
          DELETE FROM ArrangementLines
          WHERE ArrangementLineNumber = @ArrNumber
		END
      END
      ELSE --Another reservationline points at this arrangementline so we cannot delete the arrrangementline, we have to update it
      BEGIN
        SELECT
          @DebType = PacketItem_New.DebType
        FROM ArrangementLines WITH (NOLOCK)
        INNER JOIN ArrangementRoomXtab WITH (NOLOCK)
          ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
        INNER JOIN PacketItem_NEW WITH (NOLOCK)
          ON PacketItem_NEW.PacketItemNumber = ArrangementLines.PacketItemNumber
        WHERE ArrangementRoomXtab.ArrangementLineNumber = @ArrNumber
        AND ArrangementRoomXtab.Linenumber = @Linenumber

        IF @DebType IN (1, 4, 7)
          SELECT
            @Pieces = 1 * @Old_Pieces
        ELSE
        IF @DebType IN (5, 6)
          SELECT
            @Pieces = @Old_NumberOfChildren * @Old_Pieces
        ELSE
        IF @DebType IN (8, 9)
          SELECT
            @Pieces = @Old_NumberOfChildren2 * @Old_Pieces
        ELSE --debtype is 2 or 3....
          SELECT
            @Pieces = @Old_NumberOfPersons * @Old_Pieces

        UPDATE ArrangementLines
        SET Pieces = Pieces - @Pieces
        WHERE ArrangementLineNumber = @ArrNumber

        SELECT
          @Pieces = Pieces
        FROM ArrangementLines WITH (NOLOCK)
        WHERE ArrangementLineNumber = @Arrnumber --If pieces in an arrangementline has turned to 0 then delete it
        IF @Pieces = 0
        BEGIN
		  IF @ArrLinePacketItemNumber > 0
		  BEGIN
            DELETE FROM ArrangementRoomXtab
            WHERE ArrangementLineNumber = @Arrnumber
            DELETE FROM ArrangementReservationsXtab
            WHERE ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementLineDepartmentXTab
            WHERE FK_ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementReservationConferenceLineXtab
            WHERE ArrangementLineNumber = @ArrNumber
            DELETE FROM ArrangementLines
            WHERE ArrangementLineNumber = @Arrnumber
		  END
        END
        DELETE FROM ArrangementRoomXtab
        WHERE LineNumber = @LineNumber
          AND ArrangementLineNumber = @Arrnumber --230101 is this correct
      -- we can only delete from the cross table if no other reservationilines point at the specific arrangementline  and we know that at least one more reservationline does that.
      END
      FETCH NEXT FROM DeleteCur INTO @ArrNumber
    END
    CLOSE DeleteCur
    DEALLOCATE DeleteCur
  END

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  --The following code is used when dates has changed on a reservationline. We have to move the manual arrangementlines, so they wont get 'lost'
  /* 20180903/RN: Removed, done in ArrangementUtilsMX.pas
  IF (((@FormatedOldEnddate <> '18991230') AND (@FormatedOldStartDate <> '18991230')) AND ((@FormatedEndDate <> @FormatedOldEnddate) OR (@FormatedStartDate <> @FormatedOldStartDate)))
  BEGIN
    DECLARE MoveCur CURSOR FORWARD_ONLY READ_ONLY FOR
    SELECT DISTINCT
      Arrangementlines.ArrangementLineNumber,
      HallNumber,
      Date
    FROM ArrangementLines WITH (NOLOCK)
    INNER JOIN ArrangementReservationsXtab WITH (NOLOCK)
      ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
      AND ReferenceNumber = @Referencenumber AND GuestNumber = @GuestNumber
    LEFT OUTER JOIN ArrangementRoomXtab WITH (NOLOCK)
      ON Arrangementlines.ArrangementLineNumber = ArrangementRoomXtab.ArrangementLineNumber
    WHERE ISNULL(ArrangementRoomXtab.ArrangementLineNumber, 0) = 0

    OPEN MoveCur
    FETCH NEXT FROM MoveCur INTO @ArrNumber, @HallNumber, @DateStamp
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      SELECT
        @hallLineID = NULL
      SELECT
        @hallLineID = ReservationConfLineNumber
      FROM RESERVATIONCONFERENCELINE WITH (NOLOCK)
      WHERE ReferenceNumber = @Referencenumber
      AND ConferenceRoomNumber = @HallNumber

      IF ISNULL(@hallLineID, 0) = 0 -- is null
        SET @HallNumber = NULL

      IF ISNULL(@HallNumber, 0) = 0 -- is null
      BEGIN
        IF (@DateStamp > @FormatedEndDate)
          AND (@DateStamp > @FormatedMainResDeparture)
          UPDATE ArrangementLines
          SET Date = @FormatedMainResDeparture
          WHERE ArrangementLineNumber = @ArrNumber
        ELSE
        IF @DateStamp < @formatedStartDate
          AND @DateStamp < @FormatedMainResArrival
          UPDATE ArrangementLines
          SET Date = @FormatedMainResArrival
          WHERE ArrangementLineNumber = @ArrNumber
      END
      ELSE
      BEGIN
        -- find the conferencelinenumber which is associated to the arrangementline
        SELECT
          @hallLineID = ReservationConfLineNumber
        FROM RESERVATIONCONFERENCELINE WITH (NOLOCK)
        WHERE ReferenceNumber = @Referencenumber
        AND ConferenceRoomNumber = @HallNumber

        IF DATEDIFF(DAY, @New_EndDate, @Old_Enddate) = DATEDIFF(DAY, @New_StartDate, @Old_StartDate)
        BEGIN
          SELECT
            @OffSet = DATEDIFF(DAY, @New_EndDate, @Old_Enddate)  --Number of days------
          IF @hallLineID = @LineNumber ---ok we know that the arrangementline is asssociated to this conference line
          BEGIN
            SELECT
              @NewArrangementDate = (CONVERT(datetime, ArrangementLines.Date, 112) - @OffSet)
            FROM ArrangementLines WITH (NOLOCK)
            WHERE ArrangementLinenumber = @ArrNumber

            UPDATE ArrangementLines
            SET Date = CONVERT(char(8), @NewArrangementDate, 112)
            WHERE ArrangementLineNumber = @ArrNumber
          END
        END
        ELSE
        BEGIN
          -- we have changed departuredate on the reservationline, but we have also changed number of days,so we cannot categorize it as a ' pure move'               
          IF @HallLineId = @LineNumber --ok we know that the arrangementline is asssociated to this conference line
          BEGIN
            IF @DateStamp < @FormatedStartDate
              UPDATE ArrangementLines
              SET Date = @FormatedStartDate
              WHERE ArrangementLineNumber = @ArrNumber
            ELSE
            IF @DateStamp > @formatedEnddate
              UPDATE ArrangementLines
              SET Date = @FormatedEndDate
              WHERE ArrangementLineNumber = @ArrNumber
          END
        END
      END
      FETCH NEXT FROM MoveCur INTO @ArrNumber, @HallNumber, @DateStamp
    END
    CLOSE MoveCur
    DEALLOCATE MoveCur
  END
  */
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  -- LOOK IF THE RESERVATIONLINE HAS AFFECTED THE ARRANGEMENT BEFORE (LOOK IN CROSS TABLE)
  SELECT
    @Found = 1
  IF NOT EXISTS (SELECT
      Linenumber
    FROM ArrangementRoomXtab WITH (NOLOCK)
    WHERE Linenumber = @Linenumber)
    SELECT
      @Found = 0

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  -- EXIT IF NOTHING HAS CHANGED AND THE LINE HAS AFFECTED THE ARRANGEMENT BEFORE

  IF (@Old_ProductNumber = @New_ProductNumber)
    AND (@Old_StartDate = @New_StartDate)
    AND (@Old_EndDate = @New_Enddate)
    AND (@Old_NumberOfPersons = @New_NumberOfpersons)
    AND (@Old_NumberOfChildren = @New_NumberOfChildren)
    AND (@Old_NumberOfChildren2 = @New_NumberOfChildren2)
    AND (@Old_Pieces = @New_Pieces)
    AND (@found = 1)
  BEGIN
    RETURN
  END

  SELECT @MainCourseResNoArrPacketLines = dbo.GetHotelSetupKeyValue('MainCourseResNoArrPacketLines', @CustomerNumber)
  IF @MainCourseResNoArrPacketLines = 1 -- Do not generate arrangmentlines for Main Course reservations
  BEGIN
    IF EXISTS (SELECT FK_ReferenceNumber FROM ReservationReservationXtab WITH (NOLOCK) WHERE FK_ReferenceNumber = @Referencenumber) -- It is a Main Course reservation
      RETURN
  END
  
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  --
  -- IF EXISTS USECHECKTOLERANCE THEN USE THE VALUE ELSE 

  SELECT
    @USETOLERANCE = KeyValue
  FROM HOTELSETUP WITH (NOLOCK)
  WHERE SEARCHKEY = 'UseCheckTolerance'
  SET @USETOLERANCE = ISNULL(@USETOLERANCE, 1)

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  -- FIND THE TOLERANCE IN HOTELSETUP
  SELECT  @Tolerance = Keyvalue
  FROM HotelSetup WITH (NOLOCK)
  WHERE SearchKey = 'ArrTimeTolerance'

  SET @ToleranceDiff = ABS(DATEDIFF(MINUTE, @Tolerance, CONVERT(datetime, '00:00', 108)))

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  WHILE @CurrentDate <= @New_EndDate
  BEGIN
    SET @WeReadFromPacket = 1
    -- FOR EACH DAY GO GET THE PACKET FOR THE PRODUCT
    IF @FromProduct = 1
    BEGIN
      SELECT TOP 1 @ArrivalPacket = ProductPricePacket.FK_PacketNumber
      FROM #PackageInfo WITH (NOLOCK)
      INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
	  WHERE #PackageInfo.View_FK_ProductNumber = @New_ProductNumber
		    AND #PackageInfo.View_DateStamp = @CurrentDate
		AND #PackageInfo.View_PacketType = 0
      ORDER BY #PackageInfo.View_Id DESC
    END
    ELSE
      SELECT
        @ArrivalPacket = @New_PacketNumber

    --AV 2005 1228
    IF @PersonRessourceBookingEnabled = 1
      SELECT
        @AdjustTimeRelative = ISNULL(AdjustTimeRelative, 0)
      FROM Packet WITH (NOLOCK)
      WHERE PacketNumber = @ArrivalPacket
    ELSE
      SELECT
        @AdjustTimeRelative = 0

    IF @AdjustTimeRelative = 1
    BEGIN
      SELECT
        @Arr24HoursStarts = KeyValue
      FROM HotelSetUp WITH (NOLOCK)
      WHERE SearchKey = 'Arr24HoursStarts'
      SELECT
        @HallArrivalTime = ArrivalTime
      FROM ReservationConferenceLine WITH (NOLOCK)
      WHERE ReservationConfLineNumber = @LineNumber

      SELECT
        @MinPacketTime = MIN(Time)
      FROM PacketItem_New WITH (NOLOCK)
      WHERE PacketNumber = @ArrivalPacket
      AND Time >= @Arr24HoursStarts
      AND PacketItem_New.Arrangement = 1 --A Hack : It is required that at least one packet time < @Arr24HoursStarts

      IF ISNULL(@MinPacketTime, '') <> '' --  IS NOT NULL
        SET @AddMinutes = DATEDIFF(MINUTE, CONVERT(datetime, @MinPacketTime), CONVERT(datetime, @HallArrivalTime))
      ELSE
        SET @AddMinutes = 0
    END

    SET @IsNewArrangement = 1

    DECLARE ArrCursor CURSOR FOR
    SELECT DISTINCT
      ArrangementRoomXtab.ArrangementLineNumber
    FROM ArrangementRoomXtab WITH (NOLOCK)
    INNER JOIN ArrangementLines WITH (NOLOCK)
      ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
      AND ArrangementLines.Date = @CurrentDate
    WHERE Linenumber = @LineNumber
    OPEN ArrCursor
    FETCH NEXT FROM ArrCursor INTO @TempArrNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      SET @IsNewArrangement = 0

      SELECT
        @PacketItemNumber = PacketItemNumber,
        @WareNumber = WareNumber,
        @PriceOverWrite = Price,
        @Time = Time
      FROM ArrangementLines WITH (NOLOCK)
      WHERE ArrangementLineNumber = @TempArrNumber

 	  SET @DTTime = CONVERT(datetime, @Time, 108)

      SELECT
        @DebType = DebType
      FROM PacketItem_NEW WITH (NOLOCK)
      WHERE PacketItemNumber = @PacketItemNumber

      EXEC dbo.GetArrangementDebTypePieces @IsNewArrangement,
                                           @DebType,
                                           @Found,
                                           @Old_ProductNumber,
                                           @New_ProductNumber,
                                           @CurrentDate,
                                           @FormatedOldEndDate,
                                           @FormatedOldStartDate,
                                           @FormatedStartDate,
                                           @FormatedEndDate,
                                           @Old_NumberOfPersons,
                                           @New_NumberOfpersons,
                                           @Old_NumberOfChildren,
                                           @New_NumberOfChildren,
                                           @Old_NumberOfChildren2,
                                           @New_NumberOfChildren2,
                                           @Old_Pieces,
                                           @New_Pieces,
                                           @FromNameList,
                                           @USETOLERANCE,
                                           @Pieces OUTPUT,
                                           @PriceOverWrite OUTPUT,
                                           @TotalPrice OUTPUT

      IF ISNULL(@reservationdiscount, 0) = 0 -- is null
        SELECT
          @reservationdiscount = 0
      IF (@DisCountPercent > 0)
        AND (@reservationdiscount = 1)
        SET @PriceOverWrite = @PriceOverWrite * (1 - @DisCountPercent / 100)

      IF @AdjustTimeRelative = 1
        AND @AddMinutes <> 0  -- AV 2005 1228
      BEGIN
        SELECT
          @Time = CONVERT(char(5), DATEADD(MINUTE, @AddMinutes, CONVERT(datetime, @Time)), 108)
        IF ISNULL(@DepTime, '') <> ''
          AND LEN(@DepTime) >= 5
          SELECT
            @DepTime = CONVERT(char(5), DATEADD(MINUTE, @AddMinutes, CONVERT(datetime, @DepTime)), 108)
      END

      IF @USETOLERANCE = 1
      BEGIN
        SET @TempToleranceArrNumber = -1

        IF ISNULL(@PriceOverWrite, 0) = 0
          SELECT
            @PriceOverWrite = PacketItem_NEW.PriceOverwrite
          FROM PacketItem_NEW WITH (NOLOCK)
          WHERE PacketItem_NEW.PacketItemNumber = @PacketItemNumber

        SELECT
          @TempToleranceArrNumber = ArrangementLinenumber,
          @FoundPrice = Price,
          @FoundPieces = Pieces
        FROM ArrangementLines WITH (NOLOCK)
        WHERE ISNULL(PacketItemNumber, 0) > 0 
	    AND WareNumber = @WareNumber
        AND Price = @PriceOverwrite
        AND (ISNULL(FK_exdepartment, 0) = 0)
        AND (Time = @Time
		 OR (ABS(DATEDIFF(MINUTE, @DTTime, TimeDT)) <= @ToleranceDiff))
        AND Date = @currentdate
        AND ArrangementLines.ArrangementLineNumber IN (SELECT DISTINCT
          ArrangementReservationsXTab.ArrangementLineNumber
        FROM ArrangementReservationsXtab WITH (NOLOCK)
        WHERE ArrangementReservationsXtab.Referencenumber = @Referencenumber)

		IF @TempToleranceArrNumber > 0
  	  	  SELECT @ArrLinePacketItemNumber = ISNULL(PacketItemNumber, 0) FROM ArrangementLines (NOLOCK) WHERE ArrangementLineNumber= @TempToleranceArrNumber


        IF @TempToleranceArrNumber > -1
          IF @found = 0 --AV 20040714 if different debtype then split arrangementlines
            IF EXISTS (SELECT
                Searchkey
              FROM HotelSetup WITH (NOLOCK)
              WHERE searchkey = 'SplitArrLinesDifferentDebType'
              AND KeyValue = 1)
              IF EXISTS (SELECT
                  DebType
                FROM PacketItem_New WITH (NOLOCK)
                WHERE PacketItemNumber IN (SELECT
                  PacketItemNumber
                FROM ArrangementRoomXtab WITH (NOLOCK)
                WHERE ArrangementLineNumber = @TempToleranceArrNumber)
                AND DebType <> @DebType)
                SET @TempToleranceArrNumber = -1

        IF @TempToleranceArrNumber > -1
        BEGIN
          UPDATE ArrangementLines
          SET Pieces = Pieces + @Pieces
          WHERE ArrangementLines.ArrangementLineNumber = @TempToleranceArrNumber
		    AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
        END
      END

      IF @TempToleranceArrNumber = -1
      BEGIN
        UPDATE ArrangementLines
        SET Pieces = Pieces + @Pieces
        WHERE ArrangementLines.ArrangementLineNumber = @TempArrNumber
  	      AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
      END

      SELECT
        @TempPieces = Pieces
      FROM Arrangementlines WITH (NOLOCK)
      WHERE ArrangementLinenumber = @TempArrNumber --If pieces in an arrangementline has turned to 0 then delete it
      IF @TempPieces = 0
      BEGIN
	    IF @ArrLinePacketItemNumber > 0
		BEGIN
          DELETE FROM ArrangementRoomXtab
          WHERE Arrangementlinenumber = @TempArrNumber
          DELETE FROM ArrangementReservationsXtab
          WHERE ArrangementLineNumber = @TempArrNumber
          DELETE FROM ArrangementReservationConferenceLineXtab
          WHERE ArrangementLineNumber = @TempArrNumber
	      DELETE FROM ArrangementLineDepartmentXTab
	      WHERE FK_ArrangementLineNumber = @TempArrNumber
          DELETE FROM arrangementlines
          WHERE Arrangementlinenumber = @TempArrNumber
		END
      END
      FETCH NEXT FROM ArrCursor INTO @TempArrNumber
    END
    CLOSE ArrCursor
    DEALLOCATE ArrCursor

    IF @IsNewArrangement = 1 -- (@Old_NumberOfPersons <> @New_NumberOfpersons) OR (@Old_NumberOfChildren <> @New_NumberOfChildren) OR (@Old_NumberOfChildren2 <> @New_NumberOfChildren2) OR (@IsNewArrangement = 1)
    BEGIN
      IF (@Old_NumberOfPersons = 0)
        AND (@New_NumberOfpersons = 0)
        AND (@Old_NumberOfChildren = 0)
        AND (@New_NumberOfChildren = 0)
        AND (@Old_NumberOfChildren2 = 0)
        AND (@New_NumberOfChildren2 = 0)  --No pax at all
      BEGIN
        DECLARE CopyCur CURSOR FOR
        SELECT DISTINCT
          PacketItem_New.PacketItemNumber,
          PacketNumber,
          PacketItem_New.WareNumber,
          Time,
          DepartureTime,
          TextOverWrite,
          PriceOverWrite,
          debtype,
          CONVERT(varchar(4000), note),
          BookTimeInMinutes,
          reservationdiscount,
		  IsMandatory,
		  (SELECT TOP 1 FK_RecurringPatternId 
		   FROM RecurringPatternWareXTab WITH (NOLOCK)
		   INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternId = RecurringPattern.Id
		   WHERE RecurringPatternWareXTab.FK_WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_RecurringPatternId,
		  (SELECT TOP 1 WareLinkNumber
		   FROM Ware WITH (NOLOCK)
		   INNER JOIN WareLinkGroup WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id
           INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = WareLinkGroup.FK_RecurringPatternId
           WHERE Ware.WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_WareLinkNumber
        FROM PacketItem_New WITH (NOLOCK)
        INNER JOIN Ware WITH (NOLOCK)
          ON PacketItem_new.WareNumber = Ware.WareNumber
        LEFT OUTER JOIN PacketItem_NewDepartmentXTab WITH (NOLOCK)
          ON PacketItem_NewDepartmentXTab.PacketItemNumber = PacketItem_New.PacketItemNumber
          AND PacketItem_NewDepartmentXTab.Type = 1
        LEFT OUTER JOIN Departments WITH (NOLOCK)
          ON Departments.PK_Department = PacketItem_NewDepartmentXTab.FK_Department
        WHERE PacketNumber = @ArrivalPacket
        AND PacketItem_New.Arrangement = 1
        AND (debtype IN (1, 2, 3))
        AND (1 = 2) --dummy so we just get an empty dataset
        AND ((@HotelNumber = -1)
        OR (Departments.HotelNumber = @HotelNumber))
      END
      ELSE
      IF (@Old_NumberOfPersons <> 0
        OR @New_NumberOfpersons <> 0)
        AND (@Old_NumberOfChildren2 = 0)
        AND (@New_NumberOfChildren2 = 0)
        AND (@Old_NumberOfChildren = 0)
        AND (@New_NumberOfChildren = 0) --Only Adults
      BEGIN
        DECLARE CopyCur CURSOR FOR
        SELECT DISTINCT
          PacketItem_New.PacketItemNumber,
          PacketNumber,
          PacketItem_New.WareNumber,
          Time,
          DepartureTime,
          TextOverWrite,
          PriceOverWrite,
          debtype,
          CONVERT(varchar(4000), note),
          BookTimeInMinutes,
          reservationdiscount,
		  IsMandatory,
		  (SELECT TOP 1 FK_RecurringPatternId 
		   FROM RecurringPatternWareXTab WITH (NOLOCK)
		   INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternId = RecurringPattern.Id
		   WHERE RecurringPatternWareXTab.FK_WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_RecurringPatternId,
		  (SELECT TOP 1 WareLinkNumber
		   FROM Ware WITH (NOLOCK)
		   INNER JOIN WareLinkGroup WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id
           INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = WareLinkGroup.FK_RecurringPatternId
		   WHERE Ware.WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_WareLinkNumber
        FROM PacketItem_New WITH (NOLOCK)
        INNER JOIN Ware WITH (NOLOCK)
          ON PacketItem_new.WareNumber = Ware.WareNumber
        LEFT OUTER JOIN PacketItem_NewDepartmentXTab WITH (NOLOCK)
          ON PacketItem_NewDepartmentXTab.PacketItemNumber = PacketItem_New.PacketItemNumber
          AND PacketItem_NewDepartmentXTab.Type = 1
        LEFT OUTER JOIN Departments WITH (NOLOCK)
          ON Departments.PK_Department = PacketItem_NewDepartmentXTab.FK_Department
        WHERE PacketNumber = @ArrivalPacket
        AND PacketItem_New.Arrangement = 1
        AND (debtype IN (1, 2, 3))
        AND ((@HotelNumber = -1)
        OR (Departments.HotelNumber = @HotelNumber))
      END
      ELSE
      IF (@Old_NumberOfPersons = 0)
        AND (@New_NumberOfpersons = 0)
        AND (@Old_NumberOfChildren2 = 0)
        AND (@New_NumberOfChildren2 = 0)
        AND (@Old_NumberOfChildren <> 0
        OR @New_NumberOfChildren <> 0) --Only Children1
      BEGIN
        DECLARE CopyCur CURSOR FOR
        SELECT DISTINCT
          PacketItem_New.PacketItemNumber,
          PacketNumber,
          PacketItem_New.WareNumber,
          Time,
          DepartureTime,
          TextOverWrite,
          PriceOverWrite,
          debtype,
          CONVERT(varchar(4000), note),
          BookTimeInMinutes,
          reservationdiscount,
		  IsMandatory,
		  (SELECT TOP 1 FK_RecurringPatternId 
		   FROM RecurringPatternWareXTab WITH (NOLOCK)
		   INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternId = RecurringPattern.Id
		   WHERE RecurringPatternWareXTab.FK_WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_RecurringPatternId,
		  (SELECT TOP 1 WareLinkNumber
		   FROM Ware WITH (NOLOCK)
		   INNER JOIN WareLinkGroup WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id
           INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = WareLinkGroup.FK_RecurringPatternId
		   WHERE Ware.WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_WareLinkNumber
        FROM PacketItem_New WITH (NOLOCK)
        INNER JOIN Ware WITH (NOLOCK)
          ON PacketItem_new.WareNumber = Ware.WareNumber
        LEFT OUTER JOIN PacketItem_NewDepartmentXTab WITH (NOLOCK)
          ON PacketItem_NewDepartmentXTab.PacketItemNumber = PacketItem_New.PacketItemNumber
          AND PacketItem_NewDepartmentXTab.Type = 1
        LEFT OUTER JOIN Departments WITH (NOLOCK)
          ON Departments.PK_Department = PacketItem_NewDepartmentXTab.FK_Department
        WHERE PacketNumber = @ArrivalPacket
        AND PacketItem_New.Arrangement = 1
        AND (debtype IN (4, 5, 6))
        AND ((@HotelNumber = -1)
        OR (Departments.HotelNumber = @HotelNumber))
      END
      ELSE
      IF (@Old_NumberOfPersons = 0)
        AND (@New_NumberOfpersons = 0)
        AND (@Old_NumberOfChildren = 0)
        AND (@New_NumberOfChildren = 0)
        AND (@Old_NumberOfChildren2 <> 0
        OR @New_NumberOfChildren2 <> 0)--Only children 2
      BEGIN
        DECLARE CopyCur CURSOR FOR
        SELECT DISTINCT
          PacketItem_New.PacketItemNumber,
          PacketNumber,
          PacketItem_New.WareNumber,
          Time,
          DepartureTime,
          TextOverWrite,
          PriceOverWrite,
          debtype,
          CONVERT(varchar(4000), note),
          BookTimeInMinutes,
          reservationdiscount,
		  IsMandatory,
		  (SELECT TOP 1 FK_RecurringPatternId 
		   FROM RecurringPatternWareXTab WITH (NOLOCK)
		   INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternId = RecurringPattern.Id
		   WHERE RecurringPatternWareXTab.FK_WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_RecurringPatternId,
		  (SELECT TOP 1 WareLinkNumber
		   FROM Ware WITH (NOLOCK)
		   INNER JOIN WareLinkGroup WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id
           INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = WareLinkGroup.FK_RecurringPatternId
		   WHERE Ware.WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_WareLinkNumber
        FROM PacketItem_New WITH (NOLOCK)
        INNER JOIN Ware WITH (NOLOCK)
          ON PacketItem_new.WareNumber = Ware.WareNumber
        LEFT OUTER JOIN PacketItem_NewDepartmentXTab WITH (NOLOCK)
          ON PacketItem_NewDepartmentXTab.PacketItemNumber = PacketItem_New.PacketItemNumber
          AND PacketItem_NewDepartmentXTab.Type = 1
        LEFT OUTER JOIN Departments WITH (NOLOCK)
          ON Departments.PK_Department = PacketItem_NewDepartmentXTab.FK_Department
        WHERE PacketNumber = @ArrivalPacket
        AND PacketItem_New.Arrangement = 1
        AND (debtype IN (7, 8, 9))
        AND ((@HotelNumber = -1)
        OR (Departments.HotelNumber = @HotelNumber))
      END
      ELSE
      IF (@Old_NumberOfPersons <> 0
        OR @New_NumberOfpersons <> 0)
        AND (@Old_NumberOfChildren2 <> 0
        OR @New_NumberOfChildren2 <> 0)
        AND (@Old_NumberOfChildren = 0)
        AND (@New_NumberOfChildren = 0) --Only Adults and children2
      BEGIN
        DECLARE CopyCur CURSOR FOR
        SELECT DISTINCT
          PacketItem_New.PacketItemNumber,
          PacketNumber,
          PacketItem_New.WareNumber,
          Time,
          DepartureTime,
          TextOverWrite,
          PriceOverWrite,
          debtype,
          CONVERT(varchar(4000), note),
          BookTimeInMinutes,
          reservationdiscount,
		  IsMandatory,
		  (SELECT TOP 1 FK_RecurringPatternId 
		   FROM RecurringPatternWareXTab WITH (NOLOCK)
		   INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternId = RecurringPattern.Id
		   WHERE RecurringPatternWareXTab.FK_WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_RecurringPatternId,
		  (SELECT TOP 1 WareLinkNumber
		   FROM Ware WITH (NOLOCK)
		   INNER JOIN WareLinkGroup WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id
           INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = WareLinkGroup.FK_RecurringPatternId
		   WHERE Ware.WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_WareLinkNumber
        FROM PacketItem_New WITH (NOLOCK)
        INNER JOIN Ware WITH (NOLOCK)
          ON PacketItem_new.WareNumber = Ware.WareNumber
        LEFT OUTER JOIN PacketItem_NewDepartmentXTab WITH (NOLOCK)
          ON PacketItem_NewDepartmentXTab.PacketItemNumber = PacketItem_New.PacketItemNumber
          AND PacketItem_NewDepartmentXTab.Type = 1
        LEFT OUTER JOIN Departments WITH (NOLOCK)
          ON Departments.PK_Department = PacketItem_NewDepartmentXTab.FK_Department
        WHERE PacketNumber = @ArrivalPacket
        AND PacketItem_New.Arrangement = 1
        AND (debtype IN (1, 2, 3, 7, 8, 9))
        AND ((@HotelNumber = -1)
        OR (Departments.HotelNumber = @HotelNumber))
      END
      ELSE
      IF (@Old_NumberOfPersons <> 0
        OR @New_NumberOfpersons <> 0)
        AND (@Old_NumberOfChildren <> 0
        OR @New_NumberOfChildren <> 0)
        AND (@Old_NumberOfChildren2 = 0)
        AND (@New_NumberOfChildren2 = 0) --Only Adults and children1
      BEGIN
        DECLARE CopyCur CURSOR FOR
        SELECT DISTINCT
          PacketItem_New.PacketItemNumber,
          PacketNumber,
          PacketItem_New.WareNumber,
          Time,
          DepartureTime,
          TextOverWrite,
          PriceOverWrite,
          debtype,
          CONVERT(varchar(4000), note),
          BookTimeInMinutes,
          reservationdiscount,
		  IsMandatory,
		  (SELECT TOP 1 FK_RecurringPatternId 
		   FROM RecurringPatternWareXTab WITH (NOLOCK)
		   INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternId = RecurringPattern.Id
		   WHERE RecurringPatternWareXTab.FK_WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_RecurringPatternId,
		  (SELECT TOP 1 WareLinkNumber
		   FROM Ware WITH (NOLOCK)
		   INNER JOIN WareLinkGroup WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id
           INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = WareLinkGroup.FK_RecurringPatternId
		   WHERE Ware.WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_WareLinkNumber
        FROM PacketItem_New WITH (NOLOCK)
        INNER JOIN Ware WITH (NOLOCK)
          ON PacketItem_new.WareNumber = Ware.WareNumber
        LEFT OUTER JOIN PacketItem_NewDepartmentXTab WITH (NOLOCK)
          ON PacketItem_NewDepartmentXTab.PacketItemNumber = PacketItem_New.PacketItemNumber
          AND PacketItem_NewDepartmentXTab.Type = 1
        LEFT OUTER JOIN Departments WITH (NOLOCK)
          ON Departments.PK_Department = PacketItem_NewDepartmentXTab.FK_Department
        WHERE PacketNumber = @ArrivalPacket
        AND PacketItem_New.Arrangement = 1
        AND (debtype IN (1, 2, 3, 4, 5, 6))
        AND ((@HotelNumber = -1)
        OR (Departments.HotelNumber = @HotelNumber))
      END
      ELSE
      IF (@Old_NumberOfPersons <> 0
        OR @New_NumberOfPersons <> 0)
        AND (@Old_NumberOfChildren <> 0
        OR @New_NumberOfChildren <> 0)
        AND (@Old_NumberOfChildren2 <> 0
        OR @New_NumberOfChildren2 <> 0) --Adults, children1 and children 2
      BEGIN
        DECLARE CopyCur CURSOR FOR
        SELECT DISTINCT
          PacketItem_New.PacketItemNumber,
          PacketNumber,
          PacketItem_New.WareNumber,
          Time,
          DepartureTime,
          TextOverWrite,
          PriceOverWrite,
          debtype,
          CONVERT(varchar(4000), note),
          BookTimeInMinutes,
          reservationdiscount,
		  IsMandatory,
		  (SELECT TOP 1 FK_RecurringPatternId 
		   FROM RecurringPatternWareXTab WITH (NOLOCK)
		   INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternId = RecurringPattern.Id
		   WHERE RecurringPatternWareXTab.FK_WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_RecurringPatternId,
		  (SELECT TOP 1 WareLinkNumber
		   FROM Ware WITH (NOLOCK)
		   INNER JOIN WareLinkGroup WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id
           INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = WareLinkGroup.FK_RecurringPatternId
		   WHERE Ware.WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_WareLinkNumber
        FROM PacketItem_New WITH (NOLOCK)
        INNER JOIN Ware WITH (NOLOCK)
          ON PacketItem_new.WareNumber = Ware.WareNumber
        LEFT OUTER JOIN PacketItem_NewDepartmentXTab WITH (NOLOCK)
          ON PacketItem_NewDepartmentXTab.PacketItemNumber = PacketItem_New.PacketItemNumber
          AND PacketItem_NewDepartmentXTab.Type = 1
        LEFT OUTER JOIN Departments WITH (NOLOCK)
          ON Departments.PK_Department = PacketItem_NewDepartmentXTab.FK_Department
        WHERE PacketNumber = @ArrivalPacket
        AND PacketItem_New.Arrangement = 1
        AND ((@HotelNumber = -1)
        OR (Departments.HotelNumber = @HotelNumber))
      END
      ELSE
      IF (@Old_NumberOfPersons = 0)
        AND (@new_NumberOfPersons = 0)
        AND (@Old_NumberOfChildren <> 0
        OR @New_NumberOfChildren <> 0)
        AND (@Old_NumberOfChildren2 <> 0
        OR @New_NumberOfChildren2 <> 0) --only children1 and children 2
      BEGIN
        DECLARE CopyCur CURSOR FOR
        SELECT DISTINCT
          PacketItem_New.PacketItemNumber,
          PacketNumber,
          PacketItem_New.WareNumber,
          Time,
          DepartureTime,
          TextOverWrite,
          PriceOverWrite,
          debtype,
          CONVERT(varchar(4000), note),
          BookTimeInMinutes,
          reservationdiscount,
		  IsMandatory,
		  (SELECT TOP 1 FK_RecurringPatternId 
		   FROM RecurringPatternWareXTab WITH (NOLOCK)
		   INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternId = RecurringPattern.Id
		   WHERE RecurringPatternWareXTab.FK_WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_RecurringPatternId,
		  (SELECT TOP 1 WareLinkNumber
		   FROM Ware WITH (NOLOCK)
		   INNER JOIN WareLinkGroup WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id
           INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = WareLinkGroup.FK_RecurringPatternId
		   WHERE Ware.WareNumber = PacketItem_NEW.WareNumber
		  ) AS FK_WareLinkNumber
        FROM PacketItem_New WITH (NOLOCK)
        INNER JOIN Ware WITH (NOLOCK)
          ON PacketItem_new.WareNumber = Ware.WareNumber
        LEFT OUTER JOIN PacketItem_NewDepartmentXTab WITH (NOLOCK)
          ON PacketItem_NewDepartmentXTab.PacketItemNumber = PacketItem_New.PacketItemNumber
          AND PacketItem_NewDepartmentXTab.Type = 1
        LEFT OUTER JOIN Departments WITH (NOLOCK)
          ON Departments.PK_Department = PacketItem_NewDepartmentXTab.FK_Department
        WHERE PacketNumber = @ArrivalPacket
        AND PacketItem_New.Arrangement = 1
        AND (debtype IN (4, 5, 6, 7, 8, 9))
        AND ((@HotelNumber = -1)
        OR (Departments.HotelNumber = @HotelNumber))
      END

      /*
           so the 8 situations just above is:
           0. no adults, no children 1, no children 2
           1. Only adults
           2. Only children 1 
           3. Only Children 2
           4. Adults + children 2 
           5. Adults + children 1 
           6. Adults + children 1+ Children 2
           7. children1 + children 2
           --is there more situations ?
      */

      OPEN CopyCur
      FETCH NEXT FROM CopyCur INTO @PacketItemNumber, @PacketNumber, @WareNumber, @Time, @DepTime, @TextOverWrite, @PriceOverWrite, @debtype, @note, @BookTimeInMinutes, @reservationdiscount, @PacketItemMandatory, @FK_RecurringPatternId, @FK_WareLinkNumber

      --Loop through records for this packet and update arrangement table. Do this for every day in the arrangement period

      WHILE (@@FETCH_STATUS <> -1)
      BEGIN
		SET @DTTime = CONVERT(datetime, @Time, 108)
		SET @WareHasRule = 0
		IF (ISNULL(@FK_RecurringPatternId,0) > 0) OR (ISNULL(@FK_WareLinkNumber,0) > 0)
		  SET @WareHasRule = 1

        EXEC dbo.GetArrangementDebTypePieces @IsNewArrangement,
                                             @DebType,
                                             @Found,
                                             @Old_ProductNumber,
                                             @New_ProductNumber,
                                             @CurrentDate,
                                             @FormatedOldEndDate,
                                             @FormatedOldStartDate,
                                             @FormatedStartDate,
                                             @FormatedEndDate,
                                             @Old_NumberOfPersons,
                                             @New_NumberOfpersons,
                                             @Old_NumberOfChildren,
                                             @New_NumberOfChildren,
                                             @Old_NumberOfChildren2,
                                             @New_NumberOfChildren2,
                                             @Old_Pieces,
                                             @New_Pieces,
                                             @FromNameList,
                                             @USETOLERANCE,
                                             @Pieces OUTPUT,
                                             @PriceOverwrite OUTPUT,
                                             @TotalPrice OUTPUT

        IF ISNULL(@PriceOverWrite, 0) = 0 -- is null 
          SELECT
            @PriceOverWrite = 0

        /* AV 20050330 */
        IF ISNULL(@reservationdiscount, 0) = 0 -- is null
          SELECT
            @reservationdiscount = 0

        IF (@DisCountPercent > 0)
          AND (@reservationdiscount = 1)
          SET @PriceOverWrite = @Priceoverwrite * (1 - @DisCountPercent / 100)

        SET LOCK_TIMEOUT 500

        IF @AdjustTimeRelative = 1
          AND @AddMinutes <> 0  -- AV 2005 1228
        BEGIN
          SELECT
            @Time = CONVERT(char(5), DATEADD(MINUTE, @AddMinutes, CONVERT(datetime, @Time)), 108)
          IF ISNULL(@DepTime, '') <> ''
            AND LEN(@DepTime) >= 5
            SELECT
              @DepTime = CONVERT(char(5), DATEADD(MINUTE, @AddMinutes, CONVERT(datetime, @DepTime)), 108)
        END

        IF @Pieces > 0
        BEGIN
          SELECT
            @HallNumber = Conferenceroomnumber
          FROM ReservationConferenceLine WITH (NOLOCK)
          WHERE ReservationConfLinenumber = @LineNumber
          SELECT
            @SuggestHallNumberOnPacketLine = dbo.GetHotelSetupKeyValue('SuggestHallNumberOnPacketLine', @CustomerNumber)
          IF @SuggestHallNumberOnPacketLine = 0
            SET @Hallnumber = NULL

          SET @DepartureTime = NULL
          IF @PersonRessourceBookingEnabled = 1
          BEGIN
            IF ISNULL(@DepTime, '') <> '' -- is not null              
              SELECT
                @Departuretime = @Deptime
            ELSE
            IF ISNULL(@BookTimeInMinutes, 0) > 0
              SELECT
                @DepartureTime = CONVERT(varchar(5), CONVERT(datetime, DATEADD(MINUTE, @BookTimeInMinutes, @Time)), 108)
          END

          IF @ChargePacketDepartmentEnabled = 1
          BEGIN
            SELECT
              @PacketChargeDepartment = dbo.GetPacketItemChargeDepartment(@PacketItemNumber, @HotelNumber)
            IF @PacketChargeDepartment = 0
              SET @PacketChargeDepartment = NULL
          END
          ELSE
            SET @PacketChargeDepartment = NULL

          IF @USETOLERANCE = 1
          BEGIN
            SET @TempToleranceArrNumber = -1

            IF ISNULL(@PriceOverWrite, 0) = 0
              SELECT
                @PriceOverWrite = PacketItem_NEW.PriceOverwrite
              FROM PacketItem_NEW WITH (NOLOCK)
              WHERE PacketItem_NEW.PacketItemNumber = @PacketItemNumber

            IF @DebType = 2
            BEGIN
              SELECT
                @PriceOverWrite = 0
              SELECT
                @TotalPrice = 0
            END

            SELECT
              @TempToleranceArrNumber = ArrangementLinenumber,
              @FoundPrice = Price,
              @FoundPieces = Pieces
            FROM ArrangementLines WITH (NOLOCK)
            WHERE WareNumber = @WareNumber
			  AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
            AND Price = @PriceOverwrite
            AND (ISNULL(FK_exdepartment, 0) = 0)
            AND (Time = @Time
             OR (ABS(DATEDIFF(MINUTE, @DTTime, TimeDT)) <= @ToleranceDiff))
            AND Date = @currentdate
            AND ArrangementLines.ArrangementLineNumber IN (SELECT DISTINCT
              ArrangementReservationsXTab.ArrangementLineNumber
            FROM ArrangementReservationsXtab WITH (NOLOCK)
            WHERE ArrangementReservationsXtab.Referencenumber = @Referencenumber)

            IF @TempToleranceArrNumber > -1
              IF @found = 0 --AV 20040714 if different debtype then split arrangementlines
                IF EXISTS (SELECT
                    Searchkey
                  FROM HotelSetup WITH (NOLOCK)
                  WHERE searchkey = 'SplitArrLinesDifferentDebType'
                  AND KeyValue = 1)
                  IF EXISTS (SELECT
                      DebType
                    FROM PacketItem_New WITH (NOLOCK)
                    WHERE PacketItemNumber IN (SELECT
                      PacketItemNumber
                    FROM ArrangementRoomXtab WITH (NOLOCK)
                    WHERE ArrangementLineNumber = @TempToleranceArrNumber)
                    AND DebType <> @DebType)
                    SET @TempToleranceArrNumber = -1

            IF @TempToleranceArrNumber > -1
            BEGIN
			  IF @UseAdjustArrLinesToBookingTime = 1
			  BEGIN
			    -- Handle arr/dep time before Arr24HoursStart
			    SET @AdjustedArrDatetime = @OriginalStartDateAndTime
			    IF (@OrigArrTime < @DTArr24HoursStart)
			      SET @AdjustedArrDatetime = CAST(@OrigArrDate AS DATETIME) + CAST(@DTArr24HoursStart AS DATETIME)

			    SET @AdjustedDepDatetime = @OriginalEndDateAndTime
			    IF (@OrigDepTime < @DTArr24HoursStart)
			      SET @AdjustedDepDatetime = CAST(@OrigDepDate AS DATETIME) + CAST('23:59:59' AS DATETIME)

  			    SET @PacketItemTime = CAST(@DTTime AS TIME)

			    IF ((@OrigArrTime < @DTArr24HoursStart) AND (@PacketItemTime < @DTArr24HoursStart) AND (@PacketItemTime >= @OrigArrTime) AND @PacketItemMandatory = 0)
				BEGIN
                  UPDATE ArrangementLines
                  SET Pieces = Pieces + @Pieces
                  WHERE ArrangementLines.ArrangementLineNumber = @TempToleranceArrNumber
				    AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
				END  

			    IF ((@OrigDepTime < @DTArr24HoursStart) AND (@PacketItemTime < @DTArr24HoursStart) AND @PacketItemMandatory = 0)
			    BEGIN
                  UPDATE ArrangementLines
                  SET Pieces = Pieces + @Pieces
                  WHERE ArrangementLines.ArrangementLineNumber = @TempToleranceArrNumber
				    AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
				END

  			    SET @CurrentDateAndTime = CONVERT(datetime, @CurrentDate + ' ' + @Time, 112)
			    IF ((@CurrentDateAndTime >= @AdjustedArrDatetime) AND (@CurrentDateAndTime <= @AdjustedDepDatetime)) OR (@PacketItemMandatory = 1)
				BEGIN
                  UPDATE ArrangementLines
                  SET Pieces = Pieces + @Pieces
                  WHERE ArrangementLines.ArrangementLineNumber = @TempToleranceArrNumber
				    AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
				END
			  END
			  ELSE
			  BEGIN
                UPDATE ArrangementLines
                SET Pieces = Pieces + @Pieces
                WHERE ArrangementLines.ArrangementLineNumber = @TempToleranceArrNumber
			      AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
			  END

              IF NOT EXISTS (SELECT
                  ArrangementLineNumber
                FROM ArrangementRoomXtab WITH (NOLOCK)
                WHERE ArrangementRoomXtab.Linenumber = @Linenumber
                AND ArrangementRoomXtab.ArrangementLineNumber = @TempToleranceArrNumber)
                INSERT INTO ArrangementRoomXtab
                  VALUES (@TempToleranceArrNumber, @Linenumber, 0, @PacketItemNumber)
              IF NOT EXISTS (SELECT
                  GuestNumber
                FROM ArrangementReservationsXtab WITH (NOLOCK)
                WHERE Referencenumber = @ReferenceNumber
                AND GuestNumber = @GuestNumber
                AND ArrangementLineNumber = @TempToleranceArrNumber
                AND PlusProductLineNumber = 0)
                INSERT INTO ArrangementReservationsXtab
                  VALUES (@TempToleranceArrNumber, @Referencenumber, @GuestNumber, 0)
            END
          END
          ELSE
            SET @TempToleranceArrNumber = -1

          IF @TempToleranceArrNumber = -1
          BEGIN
		    SET @IdentityValue = 0
		    IF @UseAdjustArrLinesToBookingTime = 1
			BEGIN
			  SET @AdjustedArrDatetime = @OriginalStartDateAndTime
			  IF (@OrigArrTime < @DTArr24HoursStart)
			    SET @AdjustedArrDatetime = CAST(@OrigArrDate AS DATETIME) + CAST(@DTArr24HoursStart AS DATETIME)

			  SET @AdjustedDepDatetime = @OriginalEndDateAndTime
			  IF (@OrigDepTime < @DTArr24HoursStart)
			    SET @AdjustedDepDatetime = CAST(@OrigDepDate AS DATETIME) + CAST('23:59:59' AS DATETIME)

			  SET @PacketItemTime = CAST(@DTTime AS TIME)

			  IF ((@OrigArrTime < @DTArr24HoursStart) AND (@PacketItemTime < @DTArr24HoursStart) AND (@PacketItemTime >= @OrigArrTime) AND @PacketItemMandatory = 0)
			  BEGIN
                INSERT INTO ArrangementLines (Date, PacketItemNumber, Time, DepartureTime, Price, Pieces, WareText, WareNumber, HallNumber, Note, FK_PacketChargeDepartment, PacketItemTime, IsUserModified, 
				                              PacketItemWareNumber, PacketItemWareText, PacketItemPrice, WareHasRule, IsArrLineMoved)
                  VALUES (@CurrentDate, @PacketItemNumber, @Time, @DepartureTime, @PriceoverWrite, @Pieces, @TextoverWrite, @WareNumber, @HallNumber, @Note, @PacketChargeDepartment, @Time, 0,
				          @WareNumber, @TextOverWrite, @PriceOverWrite, @WareHasRule, @IsArrLineMoved)			  
		        SELECT @IdentityValue = SCOPE_IDENTITY()
			  END

			  IF ((@OrigDepTime < @DTArr24HoursStart) AND (@PacketItemTime < @DTArr24HoursStart) AND @PacketItemMandatory = 0)
			  BEGIN
                INSERT INTO ArrangementLines (Date, PacketItemNumber, Time, DepartureTime, Price, Pieces, WareText, WareNumber, HallNumber, Note, FK_PacketChargeDepartment, PacketItemTime, IsUserModified,
  				                              PacketItemWareNumber, PacketItemWareText, PacketItemPrice, WareHasRule, IsArrLineMoved)
                  VALUES (@CurrentDate, @PacketItemNumber, @Time, @DepartureTime, @PriceoverWrite, @Pieces, @TextoverWrite, @WareNumber, @HallNumber, @Note, @PacketChargeDepartment, @Time, 0,			 
				          @WareNumber, @TextOverWrite, @PriceOverWrite, @WareHasRule, @IsArrLineMoved)			  
		        SELECT @IdentityValue = SCOPE_IDENTITY()
			  END

  			  SET @CurrentDateAndTime = CONVERT(datetime, @CurrentDate + ' ' + @Time, 112)

			  IF ((@CurrentDateAndTime >= @AdjustedArrDatetime) AND (@CurrentDateAndTime <= @AdjustedDepDatetime)) OR (@PacketItemMandatory = 1)
			  BEGIN
                INSERT INTO ArrangementLines (Date, PacketItemNumber, Time, DepartureTime, Price, Pieces, WareText, WareNumber, HallNumber, Note, FK_PacketChargeDepartment, PacketItemTime, IsUserModified,
  				                              PacketItemWareNumber, PacketItemWareText, PacketItemPrice, WareHasRule, IsArrLineMoved)
                  VALUES (@CurrentDate, @PacketItemNumber, @Time, @DepartureTime, @PriceoverWrite, @Pieces, @TextoverWrite, @WareNumber, @HallNumber, @Note, @PacketChargeDepartment, @Time, 0,			  
				          @WareNumber, @TextOverWrite, @PriceOverWrite, @WareHasRule, @IsArrLineMoved)			  
				SELECT @IdentityValue = SCOPE_IDENTITY()
                --SELECT @IdentityValue = @@identity FROM ArrangementLines WITH (NOLOCK)
			  END
			END
			ELSE
			BEGIN
              INSERT INTO ArrangementLines (Date, PacketItemNumber, Time, DepartureTime, Price, Pieces, WareText, WareNumber, HallNumber, Note, FK_PacketChargeDepartment, PacketItemTime, IsUserModified,
 				                            PacketItemWareNumber, PacketItemWareText, PacketItemPrice, WareHasRule, IsArrLineMoved)
                VALUES (@CurrentDate, @PacketItemNumber, @Time, @DepartureTime, @PriceoverWrite, @Pieces, @TextoverWrite, @WareNumber, @HallNumber, @Note, @PacketChargeDepartment, @Time, 0,
				        @WareNumber, @TextOverWrite, @PriceOverWrite, @WareHasRule, @IsArrLineMoved)			 
 			  SELECT @IdentityValue = SCOPE_IDENTITY()
              --SELECT @IdentityValue = @@identity FROM ArrangementLines WITH (NOLOCK)
			END

			IF @IdentityValue > 0
			BEGIN
              INSERT INTO ArrangementReservationsXtab VALUES (@IdentityValue, @Referencenumber, @GuestNumber, 0)
              INSERT INTO ArrangementRoomXtab VALUES (@IdentityValue, @LineNumber, 0, @PacketItemNumber)

              DECLARE DepCur CURSOR FORWARD_ONLY FOR
              SELECT DISTINCT FK_Department
              FROM PacketItem_NewDepartmentXTab WITH (NOLOCK)
              LEFT OUTER JOIN Departments WITH (NOLOCK) ON PacketItem_NewDepartmentXTab.FK_Department = Departments.PK_Department
              WHERE PacketItemNumber = @PacketItemNumber
                AND Type = 1
                AND (@HotelNumber = -1
                OR Departments.HotelNumber = @HotelNumber)
              OPEN DepCur
              FETCH NEXT FROM DepCur INTO @PK_Department
              WHILE (@@FETCH_STATUS <> -1)
              BEGIN
                IF NOT EXISTS (SELECT
                                 FK_ArrangementLineNumber
                               FROM ArrangementLineDepartmentXTab WITH (NOLOCK)
                               WHERE FK_ArrangementLineNumber = @IdentityValue
                                 AND FK_DepartmentNumber = @PK_Department)
                INSERT INTO ArrangementLineDepartmentXTab (FK_ArrangementLineNumber, FK_DepartmentNumber) VALUES (@IdentityValue, @PK_Department)
                FETCH NEXT FROM DepCur INTO @PK_Department
              END
              CLOSE DepCur
              DEALLOCATE DepCur
            END
          END
        END
        FETCH NEXT FROM CopyCur INTO @PacketItemNumber, @PacketNumber, @WareNumber, @Time, @DepTime, @TextOverWrite, @PriceOverWrite, @debtype, @note, @BookTimeInMinutes, @reservationdiscount, @PacketItemMandatory, @FK_RecurringPatternId, @FK_WareLinkNumber
      END
      CLOSE CopyCur
      DEALLOCATE CopyCur
    END
    SET @New_StartDate = @New_StartDate + 1
    SELECT
      @CurrentDate = CONVERT(char(10), @New_StartDate, 112)
  END
  DROP TABLE #TempPacketItems
  IF @AFromRecreate = 0
    DROP TABLE #PackageInfo
