CREATE PROCEDURE [dbo].[GenerateArrangementFromNameList] @Old_ProductNumber int, @New_ProductNumber int, @WhichDay 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_NumberOfChildren1 int, @New_NumberOfChildren1 int,  @Old_NumberOfChildren2 int, @New_NumberOfChildren2 int, @Old_Pieces int, @New_Pieces int, @StarProduct int, @OldStarProduct int, @FromNameList int, @Linenumber int,@RoomTypeNumber int, @DiscountPercent money =0, @HotelNumber int = -1 , @AFromRecreate bit = 0, @ACustomerNumber int = -1
AS
  SET NOCOUNT ON

    -- 20170608 RN: Small performance improvements
	-- 20170606 RN: Rewrite how to find packet number
    -- 20150430 RN: Rewrite how pieces is updated (not using PacketItemNumber from Product/Packet anymore for updates)
    -- 20150325 RN: Added use of departuretime for arrangementlines
    -- 20150302 RN: Modified "SuggestHallNumberOnPacketLine" setting to be Domino aware.
    -- 20141204 RN: Added check for "Use Arrangement tolerance" setting.
    -- 20121121 RN: Replaced IS NULL with ISNULL function
	-- 20120127 RN: Added Deletion of ArrangementReservationConferenceLineXtab relations.
	-- sidst rettet 2008 0514 CopyfirstRoomLine RoomNumber to Place col if hotelsetup says so (a solution for feriebureau Bornholm)
	-- sidst rettet 2007 1002 use MAX when selecting packenumber from productprice table
	-- 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 20060828 Join on PacketItemNEwRoomTypeXtab, Danparcs 
	-- sidst rettet 20060621 new variable : @PacketChargeDepartment
	-- sidst rettet 20060601 new parameter: @HotelNumber
	-- sidst rettet 20060507 ikke kompatibel med 3.36 FK_exdepartment
	-- sidst rettet 20050330 discount only if ware allows it
	-- sidst rettet 20050305 new paratmeter added. discountpercent
	-- sidst rettet 20040811   Avoid locktimeout: always delete through the key in the table (or through a index)  */      

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

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

  DECLARE @found int --used to tell whether the roomline has affected the arrangment. By looking in arrangmentRoomXtab table
  DECLARE @GuestFound int --used to tell whether the guest has affected the arrangement before. By looking in arrangementReservationsXtab
  DECLARE @ProductNo int

  DECLARE @Newlinenumber int
  DECLARE @Tolerance char(5)
  DECLARE @USETOLERANCE bit
  DECLARE @AdultProductPrice real
  DECLARE @Child1ProductPrice real
  DECLARE @Child2ProductPrice real
  DECLARE @Temp char(15)
  DECLARE @ArrNumber int
  DECLARE @RoomLineID int
  DECLARE @PlusProductLineNumber int
  DECLARE @test int
  DECLARE @GoOn int
  DECLARE @TempArrNumber int

  DECLARE @FormatedMainResArrival char(8)
  DECLARE @FormatedMainResDeparture char(8)
  DECLARE @OffSet int
  DECLARE @NewArrangementDate datetime
  DECLARE @hallLineID int
  DECLARE @DateStamp char(8)
  DECLARE @HallNumber int
  DECLARE @SuggestHallNumberOnPacketLine int
  DECLARE @HallArrivaldate char(8)
  DECLARE @HallDeparturedate char(8)
  DECLARE @CustomerNumber int
  DECLARE @BookTimeInMinutes int

  DECLARE @MainCourseResNoArrPacketLines BIT

  DECLARE @WeReadFromPacket int

  DECLARE @NullArrPacket int
  DECLARE @NullDepPacket int
  DECLARE @EmptyArrPacket int
  DECLARE @EmptyDepPacket int
  DECLARE @NullStayPacket int
  DECLARE @EmptyStayPacket int

  DECLARE @SplitArrLinesDifferentDebType int
  DECLARE @oldPacketItemNumberCount int

  -- Other
  DECLARE @MyRowCount int
  DECLARE @TempCount int

  DECLARE @UseAdjustArrLinesToBookingTime BIT -- RQ3119

  SET @CustomerNumber = @ACustomerNumber
  IF @CustomerNumber = -1
  BEGIN
    SELECT @CustomerNumber = CustomerNumber
    FROM Customer WITH (NOLOCK)
    WHERE HotelNumber = @HotelNumber
      AND CustomerTypeNumber = 7
  END
  
  SELECT @UseAdjustArrLinesToBookingTime = CONVERT(bit, KeyValue) 
  FROM HotelSetup WITH (NOLOCK)
  WHERE SearchKey = 'ArrAdjustPacketLinesFromBookingTime'

  --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 @CurrentDateDT DATETIME


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

  DECLARE @TimeDiff DATETIME
  DECLARE @TimeDT DATETIME
  DECLARE @ToleranceDiffDT INT

  SET @OriginalStartDateAndTime = @New_StartDate
  SET @OriginalEndDateAndTime = @New_Enddate

  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)

  DECLARE @AarrLineNumber int
  SET @AArrLineNumber = 0
  DECLARE @ALineNumber int
  SET @ALineNumber = 0
  DECLARE @APacketItemNumber int
  SET @APacketItemNumber = 0
  DECLARE @APlusProductLineNumber int
  SET @APlusProductLineNumber = 0

  DECLARE @ChargePacketDepartmentEnabled bit
  DECLARE @PersonRessourceBookingEnabled bit
  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'

  DECLARE @CopyRoomNumberToPlaceCol bit
  SELECT @CopyRoomNumberToPlaceCol = CONVERT(integer, ISNULL(KeyValue, 0))
  FROM HotelSetup WITH (NOLOCK)
  WHERE Searchkey = 'CopyRoomNumberToArrangementPlaceCol'

  SELECT @NullArrPacket = KeyValue FROM hotelsetup WITH (NOLOCK) WHERE Searchkey = 'NullArrPacket'
  SELECT @NullDepPacket = KeyValue FROM hotelsetup WITH (NOLOCK) WHERE Searchkey = 'NullDepPacket'
  SELECT @EmptyArrPacket = KeyValue FROM hotelsetup WITH (NOLOCK) WHERE Searchkey = 'EmptyArrPacket'
  SELECT @EmptyDepPacket = KeyValue FROM hotelsetup WITH (NOLOCK) WHERE Searchkey = 'EmptyDepPacket'
  SELECT @NullStayPacket = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'NullStayPacket'
  SELECT @EmptyStayPacket = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'EmptyStayPacket'

  SELECT @SuggestHallNumberOnPacketLine = dbo.GetHotelSetupKeyValue('SuggestHallNumberOnPacketLine', @CustomerNumber)
  SELECT @SplitArrLinesDifferentDebType = dbo.GetHotelSetupKeyValue('SplitArrLinesDifferentDebType', @CustomerNumber)

  SET @IsArrLineMoved = 0
  
  ------------------------------------------------------------------------------------------------------------------------------------
  -- 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 
  )
  */

  --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 WHERE View_FK_ProductNumber = @New_ProductNumber AND View_DateStamp BETWEEN @TempStartDate AND @TempEndDate
	IF (@PackageCount = 0) AND (@New_ProductNumber > 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 or NAMEList line /linked guest line

  IF ((@New_productNumber <> @Old_ProductNumber))  --and (@Old_productNumber <> 0)       --Another product is chosen or the reservationline has been deleted (The product has been deleted from the reservationLine)
  BEGIN
    IF @FromNameList = 0
    BEGIN
      --select all the arrangementlines where roomlinenumber = input paramter
      DECLARE ProductCur CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT
        arrangementlinenumber
      FROM ArrangementRoomxtab WITH (NOLOCK)
      WHERE ArrangementRoomxtab.Linenumber = @Linenumber
      AND ArrangementRoomXtab.PlusProductLineNumber = @Whichday
    END
    ELSE
    BEGIN
      --select all the arrangementlines where roomlinenumber = input paramter and guestnumber = inputPararameter. No we dont have to check for the roomlinenumber. It is only the guests arrrangmentlines
      DECLARE ProductCur CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT DISTINCT ArrangementReservationsXtab.ArrangementLineNumber
      FROM ArrangementReservationsXtab WITH (NOLOCK)
	  INNER JOIN ArrangementLines (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
      WHERE ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
	  AND Referencenumber = @ReferenceNumber
      AND GuestNumber = @GuestNumber
      AND PlusProductLineNumber = @whichday
    END

    OPEN ProductCur
    FETCH NEXT FROM ProductCur
    INTO @ArrNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      SET @GoOn = 1
      IF @FromNameList = 1
      BEGIN
        SET @GuestFound = 1
        -- tjeck if no other guests points at the arrangementline
        IF EXISTS (SELECT
            GuestNumber
          FROM arrangementReservationsXtab WITH (NOLOCK)
          WHERE referencenumber = @ReferenceNumber
          AND GuestNumber <> @GuestNumber
          AND ArrangementLineNumber = @Arrnumber
          AND guestnumber <> 0)
          SELECT
            @GoOn = 0 --okay the arrangementline can not be deleted : other guest(s) point(s) at it
      END
      IF NOT EXISTS (SELECT
          arrangementlinenumber
        FROM ArrangementRoomxtab WITH (NOLOCK)
        WHERE arrangementlinenumber = @ArrNumber
        AND (Linenumber <> @LineNumber
        OR PlusProductLineNumber <> @Whichday))
        AND @GoOn = 1  --if another roomline points at the arrangementline, we can not delete it - > update it..
      BEGIN
        DELETE FROM ArrangementRoomxtab
        WHERE Arrangementlinenumber = @arrnumber
        DELETE FROM ArrangementReservationsXtab
        WHERE arrangementLineNumber = @ArrNumber --and arrangementLineNumber in (select arrangementlinenumber from arrangementlines (nolock) where ArrangementLines.Date > @FormatedStartDate and ArrangementLines.Date  <= @formatedEnddate)
        DELETE FROM ArrangementLineDepartmentXTab
        WHERE FK_ArrangementLineNumber = @ArrNumber
        DELETE FROM ArrangementReservationConferenceLineXtab
        WHERE ArrangementLineNumber = @ArrNumber
        DELETE FROM arrangementlines
        WHERE arrangementlinenumber = @ArrNumber
      END
      ELSE
      BEGIN
        SELECT
          @Debtype = debtype
        FROM PacketItem_New WITH (NOLOCK)
        WHERE PacketItemNumber IN (SELECT
          PacketItemNumber
        FROM ArrangementRoomxtab WITH (NOLOCK)
        WHERE arrangementlinenumber = @arrNumber
        AND Linenumber = @LineNumber
        AND plusproductLineNumber = @whichday)
        IF @FromNamelist = 1-- we know that guestnumber is bigger than 0. It is a guest which has chosen another product
        BEGIN
          -- The guest should not point at this record anymore: delete it
          DELETE FROM arrangementReservationsXtab
          WHERE arrangementLineNumber = @ArrNumber
            AND referencenumber = @Referencenumber
            AND guestNumber = @GuestNumber
            AND PlusProductLinenumber = @whichday
          IF @debtype IN (1, 2, 3)
            SELECT
              @Pieces = @new_NumberOfPersons
          ELSE
          IF @debtype IN (4, 5, 6)
            SELECT
              @Pieces = @new_NumberOfChildren1
          ELSE
            SELECT
              @Pieces = @new_NumberOfChildren2
        END
        ELSE
        BEGIN
          IF (@debtype = 1)
            OR (@debtype = 4)
            OR (@debtype = 7)
          BEGIN
            SELECT
              @Pieces = 1 * @Old_Pieces
          END
          ELSE
          BEGIN
            IF (@debtype = 3)
              OR (@Debtype = 2)
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfPersons * @Old_Pieces
            END
            ELSE
            IF @Debtype = 5
              OR @Debtype = 6
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfChildren1 * @Old_Pieces
            END
            ELSE
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfChildren2 * @Old_Pieces
            END
          END
        END

        UPDATE ArrangementLines
        SET Pieces = Pieces - @Pieces /*, TotalPrice = TotalPrice -  (Price * @Pieces)*/
        WHERE arrangementlinenumber = @ArrNumber  --and GuestNumber =@GuestNumber   --and Date >= @FormatedStartDate and ArrangementLines.Date  <= @formatedEnddate  
		  AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
        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
          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

        IF @FromNamelist = 0 --shouldnt be done  or what ? 
        BEGIN
          -- Delete  from ArrangementRoomxtab where Linenumber = @LineNumber and Arrangementlinenumber=@arrnumber and PlusProductLineNumber = @Whichday     --AV 290101

          /* The block below is inserted 20040811 to avoid locktimeout: always delete through the key in the table (or through a index)  */
          DECLARE XtabCur CURSOR FORWARD_ONLY READ_ONLY FOR
          SELECT
            ArrangementLineNumber,
            LineNumber,
            PlusProductLineNumber,
            PacketItemNumber
          FROM ArrangementRoomXtab WITH (NOLOCK)
          WHERE Linenumber = @LineNumber
          AND Arrangementlinenumber = @arrnumber
          AND PlusProductLineNumber = @Whichday
          OPEN XtabCur
          FETCH NEXT FROM XtabCur
          INTO @AarrLineNumber, @ALineNumber, @APlusProductLineNumber, @APacketItemNumber
          WHILE (@@FETCH_STATUS <> -1)
          BEGIN
            DELETE FROM ArrangementRoomxtab
            WHERE Arrangementlinenumber = @AarrLineNumber
              AND Linenumber = @ALineNumber
              AND PlusProductLineNumber = @APlusProductLineNumber
              AND PacketItemNumber = @APacketItemNumber
            FETCH NEXT FROM XtabCur INTO @AarrLineNumber, @ALineNumber, @APlusProductLineNumber, @APacketItemNumber
          END
          CLOSE XtabCur
          DEALLOCATE XtabCur
        END
      END
      FETCH NEXT FROM ProductCur INTO @ArrNumber
    END
    CLOSE ProductCur
    DEALLOCATE ProductCur
  END -- IF ((@New_productNumber <>@Old_ProductNumber))



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

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

  --  if start and enddate of the roomline looked like this:
  --        05 feb -----------------------------------08 feb
  --  and now it look like this:
  --       05 feb-----------------6 feb
  --  then the following code deletes  (or updates if another reservationsline points at the same arrangementline) the arrangmentlines where date = 7 and 8 feb.

  IF @Old_enddate > @new_enddate
  BEGIN
    IF @FromNameList = 0
    BEGIN
      -- select all the arrangementlines where roomlinenumber = input paramter and dates are in the interval
      DECLARE DeleteCur CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT DISTINCT
        ArrangementRoomXtab.ArrangementLineNumber
      FROM ArrangementRoomXtab WITH (NOLOCK)
      INNER JOIN ArrangementLines WITH (NOLOCK)
        ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
      WHERE ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
	  AND (ArrangementLines.date >= CONVERT(char(8), @new_enddate, 112))
      AND (ArrangementLines.date <= CONVERT(char(8), @Old_enddate, 112))
      AND arrangementRoomXtab.LineNumber = @LineNumber
      AND arrangementRoomXtab.PlusProductLineNumber = @Whichday
    END
    ELSE
    BEGIN
      -- select all the arrangementlines where roomlinenumber = input paramter and guestnumber = inputPararameter and date are in the interval. No only join on the Guest table. 
      DECLARE deleteCur CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT DISTINCT
        arrangementReservationsXtab.ArrangementLineNumber
      FROM ArrangementReservationsXtab WITH (NOLOCK)
      INNER JOIN ArrangementLines WITH (NOLOCK)
        ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
      WHERE ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
	  AND (ArrangementLines.date >= CONVERT(char(8), @new_enddate, 112))
      AND (ArrangementLines.date <= CONVERT(char(8), @Old_enddate, 112))
      AND arrangementReservationsXtab.Referencenumber = @ReferenceNumber
      AND arrangementReservationsXtab.GuestNumber = @GuestNumber
      AND ArrangementReservationsXtab.PlusProductLineNumber = @WhichDay
    END
    OPEN DeleteCur
    FETCH NEXT FROM DeleteCur INTO @ArrNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      SET @GoOn = 1
      IF @FromNameList = 1
      BEGIN
        -- tjeck if no other guests points at the arrangementline
        IF EXISTS (SELECT
            GuestNumber
          FROM arrangementReservationsXtab WITH (NOLOCK)
          WHERE referencenumber = @ReferenceNumber
          AND GuestNumber <> @GuestNumber
          AND ArrangementLineNumber = @Arrnumber
          AND guestnumber <> 0)
          SELECT
            @GoOn = 0 --The arrangementline can not be deleted : other guest(s) point(s) at it
      END
      IF NOT EXISTS (SELECT
          *
        FROM ArrangementRoomxtab WITH (NOLOCK)
        WHERE arrangementlinenumber = @ArrNumber
        AND (Linenumber <> @linenumber
        OR plusProductLineNumber <> @Whichday))
        AND @GoOn = 1
      BEGIN
        DELETE FROM ArrangementRoomxtab
        WHERE arrangementlinenumber = @ArrNumber
        DELETE FROM ArrangementLineDepartmentXTab
        WHERE FK_ArrangementLineNumber = @ArrNumber
        DELETE FROM ArrangementReservationsXtab
        WHERE arrangementLineNumber = @ArrNumber
        DELETE FROM ArrangementReservationConferenceLineXtab
        WHERE ArrangementLineNumber = @ArrNumber
        DELETE FROM arrangementlines
        WHERE arrangementlinenumber = @ArrNumber
      END
      ELSE -- Another reservationline points at this arrangementline so we cannot delete the arrrangementline, we have to update it
      BEGIN
        SELECT
          @Debtype = debtype
        FROM PacketItem_New WITH (NOLOCK)
        WHERE PacketItemNumber IN (SELECT
          PacketItemNumber
        FROM ArrangementRoomxtab WITH (NOLOCK)
        WHERE arrangementlinenumber = @ArrNumber
        AND Linenumber = @Linenumber
        AND plusproductLineNumber = @whichday)
        IF @FromNamelist = 1-- we know that guestnumber is bigger than 0. It is a guest which has chosen to leave earlier 
        BEGIN
          -- The guest should not point at this record anymore: delete it
          DELETE FROM arrangementReservationsXtab
          WHERE arrangementLineNumber = @ArrNumber
            AND referencenumber = @Referencenumber
            AND guestNumber = @GuestNumber
            AND PlusProductLinenumber = @whichday
          IF @debtype IN (1, 2, 3)
            SELECT
              @Pieces = @new_NumberOfPersons
          ELSE
          IF @debtype IN (4, 5, 6)
            SELECT
              @Pieces = @new_NumberOfChildren1
          ELSE
            SELECT
              @Pieces = @new_NumberOfChildren2
        END
        ELSE
        BEGIN
          IF @debtype = 1
            OR (@debtype = 4)
            OR (@debtype = 7)
          BEGIN
            SELECT
              @Pieces = 1 * @Old_Pieces
          END
          ELSE
          BEGIN
            IF (@debtype = 3)
              OR (@Debtype = 2)
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfPersons * @Old_Pieces
            END
            ELSE
            IF @Debtype = 5
              OR @Debtype = 6
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfChildren1 * @Old_Pieces
            END
            ELSE
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfChildren2 * @Old_Pieces
            END
          END
        END

        UPDATE ArrangementLines
        SET Pieces = Pieces - @Pieces /*, TotalPrice = TotalPrice -  (Price * @Pieces)*/
        WHERE arrangementlinenumber = @ArrNumber
		  AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
        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
          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

        IF @FromNameList = 0 --shouldnt be done ?
        BEGIN
          -- Delete  from ArrangementRoomxtab where Linenumber = @LineNumber and Arrangementlinenumber=@arrnumber and plusproductLineNumber = @whichday--AV 290101

          /* The block below is inserted 20040811 to avoid locktimeout: always delete through the key in the table (or through a index)  */
          DECLARE XtabCur CURSOR FORWARD_ONLY READ_ONLY FOR
          SELECT
            ArrangementLineNumber,
            LineNumber,
            PlusProductLineNumber,
            PacketItemNumber
          FROM ArrangementRoomXtab WITH (NOLOCK)
          WHERE Linenumber = @LineNumber
          AND Arrangementlinenumber = @arrnumber
          AND PlusProductLineNumber = @Whichday
          OPEN XtabCur
          FETCH NEXT FROM XtabCur
          INTO @AarrLineNumber, @ALineNumber, @APlusProductLineNumber, @APacketItemNumber
          WHILE (@@FETCH_STATUS <> -1)
          BEGIN
            DELETE FROM ArrangementRoomxtab
            WHERE Arrangementlinenumber = @AarrLineNumber
              AND Linenumber = @ALineNumber
              AND PlusProductLineNumber = @APlusProductLineNumber
              AND PacketItemNumber = @APacketItemNumber
            FETCH NEXT FROM XtabCur INTO @AarrLineNumber, @ALineNumber, @APlusProductLineNumber, @APacketItemNumber
          END
          CLOSE XtabCur
          DEALLOCATE XtabCur
        END
      END
      FETCH NEXT FROM DeleteCur INTO @ArrNumber
    END
    CLOSE Deletecur
    DEALLOCATE Deletecur
  END -- if @Old_enddate >@new_enddate


  --  if start and enddate of the roomline looked like this:
  --        05 feb -----------------------------------08 feb
  --  and now it look like this:
  --       07 feb-----------------08 feb
  --   then the following code deletes (or updates if another reservationsline points at the same arrangementline) the arrangmentlines where date = 5 and 6 feb.

  IF @Old_StartDate < @new_StartDate
  BEGIN

    IF @FromNameList = 0
    BEGIN
      -- select all the arrangementlines where roomlinenumber = input paramter and dates are in the interval
      DECLARE DeletesCur CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT DISTINCT
        ArrangementRoomXtab.ArrangementLineNumber
      FROM ArrangementRoomXtab WITH (NOLOCK)
      INNER JOIN ArrangementLines WITH (NOLOCK)
        ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
      WHERE ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
	  AND (ArrangementLines.date >= CONVERT(char(8), @Old_Startdate, 112))
      AND (ArrangementLines.date <= CONVERT(char(8), @New_Startdate, 112))
      AND arrangementRoomXtab.LineNumber = @LineNumber
      AND plusproductLineNumber = @whichday
    END
    ELSE
    BEGIN
      -- select all the arrangementlines where roomlinenumber = input paramter and guestnumber = inputPararameter and date are in the interval. No only join on the Guest table. 
      DECLARE deletesCur CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT DISTINCT
        arrangementLines.ArrangementLineNumber
      FROM ArrangementLines WITH (NOLOCK)
      INNER JOIN ArrangementReservationsXtab WITH (NOLOCK)
        ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
      WHERE arrangementReservationsXtab.Referencenumber = @ReferenceNumber
      AND arrangementReservationsXtab.GuestNumber = @GuestNumber
      AND ArrangementReservationsXtab.PlusProductLineNumber = @WhichDay
	  AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
      AND (ArrangementLines.date >= CONVERT(char(8), @Old_Startdate, 112))
      AND (ArrangementLines.date <= CONVERT(char(8), @New_Startdate, 112))
    END
    OPEN DeletesCur
    FETCH NEXT FROM DeletesCur INTO @ArrNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      SET @GoOn = 1
      IF @FromNameList = 1
      BEGIN
        -- tjeck if no other guests points at the arrangementline
        IF EXISTS (SELECT
            GuestNumber
          FROM arrangementReservationsXtab WITH (NOLOCK)
          WHERE referencenumber = @ReferenceNumber
          AND GuestNumber <> @GuestNumber
          AND ArrangementLineNumber = @Arrnumber
          AND guestnumber <> 0)
          -- if @@RowCount > 0               
          SELECT
            @GoOn = 0 --okay the arrangementline can not be deleted : other guest(s) point(s) at it
      END
      IF NOT EXISTS (SELECT
          *
        FROM ArrangementRoomxtab WITH (NOLOCK)
        WHERE arrangementlinenumber = @arrnumber
        AND (Linenumber <> @linenumber
        OR plusproductLineNumber <> @whichday))
        AND @GoOn = 1
      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
      ELSE --Another reservationline points at this arrangementline so we cannot delete the arrrangementline, we have to update it
      BEGIN
        SELECT
          @Debtype = debtype
        FROM PacketItem_New WITH (NOLOCK)
        WHERE PacketItemNumber IN (SELECT
          PacketItemNumber
        FROM ArrangementRoomxtab WITH (NOLOCK)
        WHERE arrangementlinenumber = @arrNumber
        AND Linenumber = @Linenumber
        AND plusproductLineNumber = @whichday)
        IF @FromNamelist = 1-- we know that guestnumber is bigger than 0. It is a guest which has chosen to leave earlier 
        BEGIN
          -- The guest should not point at this record anymore: delete it
          DELETE FROM arrangementReservationsXtab
          WHERE arrangementLineNumber = @ArrNumber
            AND referencenumber = @Referencenumber
            AND guestNumber = @GuestNumber
            AND PlusProductLinenumber = @whichday
          IF @debtype IN (1, 2, 3)
            SELECT
              @Pieces = @new_NumberOfPersons
          ELSE
          IF @debtype IN (4, 5, 6)
            SELECT
              @Pieces = @new_NumberOfChildren1
          ELSE
            SELECT
              @Pieces = @new_NumberOfChildren2
        END
        ELSE
        BEGIN
          IF @debtype = 1
            OR (@debtype = 4)
            OR (@debtype = 7)
          BEGIN
            SELECT
              @Pieces = 1
          END
          ELSE
          BEGIN
            IF (@debtype = 3)
              OR (@Debtype = 2)
            BEGIN
              IF @FromNamelist = 0
                SELECT
                  @Pieces = @Old_NumberOfPersons * @Old_Pieces
              ELSE
                SELECT
                  @Pieces = 1
            END
            ELSE
            IF @Debtype = 5
              OR @Debtype = 6
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfChildren1 * @Old_Pieces
            END
            ELSE
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfChildren2 * @Old_Pieces
            END
          END
        END
        UPDATE ArrangementLines
        SET Pieces = Pieces - @Pieces /*, TotalPrice = TotalPrice -  (Price * @Pieces)*/
        WHERE arrangementlinenumber = @ArrNumber
		  AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
        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
          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

        IF @FromNameList = 0 --shouldnt be done ?
        BEGIN
          -- Delete  from ArrangementRoomxtab where Linenumber = @LineNumber and Arrangementlinenumber=@arrnumber and plusproductLineNumber = @whichday   --AV 290101

          /* The block below is inserted 20040811 to avoid locktimeout: always delete through the key in the table (or through a index)  */
          DECLARE XtabCur CURSOR FORWARD_ONLY READ_ONLY FOR
          SELECT
            ArrangementLineNumber,
            LineNumber,
            PlusProductLineNumber,
            PacketItemNumber
          FROM ArrangementRoomXtab WITH (NOLOCK)
          WHERE Linenumber = @LineNumber
          AND Arrangementlinenumber = @arrnumber
          AND PlusProductLineNumber = @Whichday
          OPEN XtabCur
          FETCH NEXT FROM XtabCur
          INTO @AarrLineNumber, @ALineNumber, @APlusProductLineNumber, @APacketItemNumber
          WHILE (@@FETCH_STATUS <> -1)
          BEGIN
            DELETE FROM ArrangementRoomxtab
            WHERE Arrangementlinenumber = @AarrLineNumber
              AND Linenumber = @ALineNumber
              AND PlusProductLineNumber = @APlusProductLineNumber
              AND PacketItemNumber = @APacketItemNumber
            FETCH NEXT FROM XtabCur INTO @AarrLineNumber, @ALineNumber, @APlusProductLineNumber, @APacketItemNumber
          END
          CLOSE XtabCur
          DEALLOCATE XtabCur
        END
      END
      FETCH NEXT FROM DeletesCur INTO @ArrNumber
    END
    CLOSE DeletesCur
    DEALLOCATE DeletesCur
  END -- @Old_StartDate <@new_StartDate


  IF @formatedStartDate < @formatedOldStartDate
  BEGIN
    -- we have to delete the old arrivalpacket lines.  We can only delete them  if no other guests or room/conferencelines points at the departurepacket lines (so update them)
    IF @FromNameList = 0
    BEGIN
      -- select all the arrangementlines where roomlinenumber = input paramter and date is @FormatedOldEnddate
      DECLARE DeletesCur CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT DISTINCT
        ArrangementRoomXtab.ArrangementLineNumber
      FROM ArrangementRoomXtab WITH (NOLOCK)
      INNER JOIN ArrangementLines WITH (NOLOCK)
        ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
      WHERE ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
	  AND (ArrangementLines.date = @FormatedOldStartDate
      AND arrangementRoomXtab.LineNumber = @LineNumber
      AND plusproductLineNumber = @whichday)
    END
    ELSE
    BEGIN
      -- select all the arrangementlines where guestnumber = inputPararameter and date is @formatedOldEnddate
      DECLARE deletesCur CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT DISTINCT
        arrangementLines.ArrangementLineNumber
      FROM ArrangementLines WITH (NOLOCK)
      INNER JOIN ArrangementReservationsXtab WITH (NOLOCK)
        ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
      WHERE ISNULL(ArrangementLines.PacketItemNumber, 0) > 0 
	  AND arrangementReservationsXtab.Referencenumber = @ReferenceNumber
      AND arrangementReservationsXtab.GuestNumber = @GuestNumber
      AND ArrangementReservationsXtab.PlusProductLineNumber = @WhichDay
      AND (ArrangementLines.date = @FormatedOldStartDate)
    END
    OPEN DeletesCur
    FETCH NEXT FROM DeletesCur INTO @ArrNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      SET @GoOn = 1
      IF @FromNameList = 1
      BEGIN
        -- tjeck if no other guests points at the arrangementline
        IF EXISTS (SELECT
            GuestNumber
          FROM arrangementReservationsXtab WITH (NOLOCK)
          WHERE referencenumber = @ReferenceNumber
          AND GuestNumber <> @GuestNumber
          AND ArrangementLineNumber = @Arrnumber
          AND guestnumber <> 0)
          SELECT
            @GoOn = 0 --okay the arrangementline can not be deleted : other guest(s) point(s) at it
      END
      IF NOT EXISTS (SELECT
          *
        FROM ArrangementRoomxtab WITH (NOLOCK)
        WHERE arrangementlinenumber = @arrnumber
        AND (Linenumber <> @linenumber
        OR plusproductLineNumber <> @whichday))
        AND @GoOn = 1
      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
      ELSE --Another reservationline points at this arrangementline so we cannot delete the arrrangementline, we have to update it
      BEGIN
        SELECT
          @Debtype = debtype
        FROM PacketItem_New WITH (NOLOCK)
        WHERE PacketItemNumber IN (SELECT
          PacketItemNumber
        FROM ArrangementRoomxtab WITH (NOLOCK)
        WHERE arrangementlinenumber = @arrNumber
        AND Linenumber = @Linenumber
        AND plusproductLineNumber = @whichday)
        IF @FromNamelist = 1-- we know that guestnumber is bigger than 0. It is a guest which has chosen to leave earlier 
        BEGIN
          -- The guest should not point at this record anymore: delete it
          DELETE FROM arrangementReservationsXtab
          WHERE arrangementLineNumber = @ArrNumber
            AND referencenumber = @Referencenumber
            AND guestNumber = @GuestNumber
            AND PlusProductLinenumber = @whichday
          IF @debtype IN (1, 2, 3)
            SELECT
              @Pieces = @new_NumberOfPersons
          ELSE
          IF @debtype IN (4, 5, 6)
            SELECT
              @Pieces = @new_NumberOfChildren1
          ELSE
            SELECT
              @Pieces = @new_NumberOfChildren2
        END
        ELSE
        BEGIN
          IF @debtype = 1
            OR (@debtype = 4)
            OR (@debtype = 7)
          BEGIN
            SELECT
              @Pieces = 1
          END
          ELSE
          BEGIN
            IF (@debtype = 3)
              OR (@Debtype = 2)
            BEGIN
              IF @FromNamelist = 0
                SELECT
                  @Pieces = @Old_NumberOfPersons * @Old_Pieces
              ELSE
                SELECT
                  @Pieces = 1
            END
            ELSE
            IF @Debtype = 5
              OR @Debtype = 6
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfChildren1 * @Old_Pieces
            END
            ELSE
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfChildren2 * @Old_Pieces
            END
          END
        END

        UPDATE ArrangementLines
        SET Pieces = Pieces - @Pieces /*, TotalPrice = TotalPrice -  (Price * @Pieces)*/
        WHERE arrangementlinenumber = @ArrNumber
		  AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
        SELECT
          @Pieces = Pieces
        FROM arrangementlines WITH (NOLOCK)
        WHERE ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
		  AND arrangementlinenumber = @Arrnumber --If pieces in an arrangementline has turned to 0 then delete it
        IF @Pieces <= 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
        IF @FromNameList = 0 --shouldnt be done ?
        BEGIN
          -- Delete  from ArrangementRoomxtab where Linenumber = @LineNumber and Arrangementlinenumber=@arrnumber and plusproductLineNumber = @whichday --AV 290101

          /* The block below is inserted 20040811 to avoid locktimeout: always delete through the key in the table (or through a index)  */
          DECLARE XtabCur CURSOR FORWARD_ONLY READ_ONLY FOR
          SELECT
            ArrangementLineNumber,
            LineNumber,
            PlusProductLineNumber,
            PacketItemNumber
          FROM ArrangementRoomXtab WITH (NOLOCK)
          WHERE Linenumber = @LineNumber
          AND Arrangementlinenumber = @arrnumber
          AND PlusProductLineNumber = @Whichday
          OPEN XtabCur
          FETCH NEXT FROM XtabCur
          INTO @AarrLineNumber, @ALineNumber, @APlusProductLineNumber, @APacketItemNumber
          WHILE (@@FETCH_STATUS <> -1)
          BEGIN
            DELETE FROM ArrangementRoomxtab
            WHERE Arrangementlinenumber = @AarrLineNumber
              AND Linenumber = @ALineNumber
              AND PlusProductLineNumber = @APlusProductLineNumber
              AND PacketItemNumber = @APacketItemNumber
            FETCH NEXT FROM XtabCur INTO @AarrLineNumber, @ALineNumber, @APlusProductLineNumber, @APacketItemNumber
          END
          CLOSE XtabCur
          DEALLOCATE XtabCur
        END
      END
      FETCH NEXT FROM DeletesCur INTO @ArrNumber
    END
    CLOSE DeletesCur
    DEALLOCATE DeletesCur
  END

  IF @FormatedEndDate > @FormatedOldEnddate
  BEGIN
    -- we have to delete the old departurepacket lines.  We can only delete them  if no other guests or room/conferencelines points at the departurepacket lines (so update them)
    IF @FromNameList = 0
    BEGIN
      -- select all the arrangementlines where roomlinenumber = input paramter and date is @FormatedOldEnddate
      DECLARE DeletesCur CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT DISTINCT
        ArrangementRoomXtab.ArrangementLineNumber
      FROM ArrangementRoomXtab WITH (NOLOCK)
      INNER JOIN ArrangementLines WITH (NOLOCK)
        ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
      WHERE ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
	    AND (ArrangementLines.date = @FormatedOldEndDate
        AND arrangementRoomXtab.LineNumber = @LineNumber
        AND plusproductLineNumber = @Whichday)
    END
    ELSE
    BEGIN
      -- select all the arrangementlines where guestnumber = inputPararameter and date is @formatedOldEnddate
      DECLARE deletesCur CURSOR FORWARD_ONLY READ_ONLY FOR
      SELECT DISTINCT
        arrangementLines.ArrangementLineNumber
      FROM ArrangementLines WITH (NOLOCK)
      INNER JOIN ArrangementReservationsXtab WITH (NOLOCK)
        ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
      WHERE arrangementReservationsXtab.Referencenumber = @ReferenceNumber
      AND arrangementReservationsXtab.GuestNumber = @GuestNumber
      AND ArrangementReservationsXtab.PlusProductLineNumber = @WhichDay
	  AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
      AND (ArrangementLines.date = @FormatedOldEnddate)
    END
    OPEN DeletesCur
    FETCH NEXT FROM DeletesCur INTO @ArrNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      SET @GoOn = 1
      IF @FromNameList = 1
      BEGIN
        -- tjeck if no other guests points at the arrangementline
        IF EXISTS (SELECT
            GuestNumber
          FROM arrangementReservationsXtab WITH (NOLOCK)
          WHERE referencenumber = @ReferenceNumber
          AND GuestNumber <> @GuestNumber
          AND ArrangementLineNumber = @Arrnumber
          AND guestnumber <> 0)
          SELECT
            @GoOn = 0 --okay the arrangementline can not be deleted : other guest(s) point(s) at it
      END
      IF NOT EXISTS (SELECT
          *
        FROM ArrangementRoomxtab WITH (NOLOCK)
        WHERE arrangementlinenumber = @arrnumber
        AND (Linenumber <> @linenumber
        OR plusproductLineNumber <> @whichday))
        AND @GoOn = 1
      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
      ELSE --Another reservationline points at this arrangementline so we cannot delete the arrrangementline, we have to update it
      BEGIN
        SELECT
          @Debtype = debtype
        FROM PacketItem_New WITH (NOLOCK)
        WHERE PacketItemNumber IN (SELECT
          PacketItemNumber
        FROM ArrangementRoomxtab WITH (NOLOCK)
        WHERE arrangementlinenumber = @arrNumber
        AND Linenumber = @Linenumber
        AND plusproductLineNumber = @whichday)
        IF @FromNamelist = 1-- we know that guestnumber is bigger than 0. It is a guest which has chosen to leave earlier 
        BEGIN
          -- The guest should not point at this record anymore: delete it
          DELETE FROM arrangementReservationsXtab
          WHERE arrangementLineNumber = @ArrNumber
            AND referencenumber = @Referencenumber
            AND guestNumber = @GuestNumber
            AND PlusProductLinenumber = @whichday
          IF @debtype IN (1, 2, 3)
            SELECT
              @Pieces = @new_NumberOfPersons
          ELSE
          IF @debtype IN (4, 5, 6)
            SELECT
              @Pieces = @new_NumberOfChildren1
          ELSE
            SELECT
              @Pieces = @new_NumberOfChildren2
        END
        ELSE
        BEGIN
          IF @debtype = 1
            OR (@debtype = 4)
            OR (@debtype = 7)
          BEGIN
            SELECT
              @Pieces = 1
          END
          ELSE
          BEGIN
            IF (@debtype = 3)
              OR (@Debtype = 2)
            BEGIN
              IF @FromNamelist = 0
                SELECT
                  @Pieces = @Old_NumberOfPersons * @Old_Pieces
              ELSE
                SELECT
                  @Pieces = 1
            END
            ELSE
            IF @Debtype = 5
              OR @Debtype = 6
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfChildren1 * @Old_Pieces
            END
            ELSE
            BEGIN
              SELECT
                @Pieces = @Old_NumberOfChildren2 * @Old_Pieces
            END
          END
        END

        UPDATE ArrangementLines
        SET Pieces = Pieces - @Pieces /*, TotalPrice = TotalPrice -  (Price * @Pieces)*/
        WHERE arrangementlinenumber = @ArrNumber
		  AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
        SELECT
          @Pieces = Pieces
        FROM arrangementlines WITH (NOLOCK)
        WHERE arrangementlinenumber = @Arrnumber --If pieces in an arrangementline has turned to 0 then delete it
		  AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
        IF @Pieces <= 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

        IF @FromNameList = 0 --shouldnt be done ?
        BEGIN
          -- Delete  from ArrangementRoomxtab where Linenumber = @LineNumber and Arrangementlinenumber=@arrnumber and plusproductLineNumber = @whichday --AV 290101

          /* The block below is inserted 20040811 to avoid locktimeout: always delete through the key in the table (or through a index)  */
          DECLARE XtabCur CURSOR FORWARD_ONLY READ_ONLY FOR
          SELECT
            ArrangementLineNumber,
            LineNumber,
            PlusProductLineNumber,
            PacketItemNumber
          FROM ArrangementRoomXtab WITH (NOLOCK)
          WHERE Linenumber = @LineNumber
          AND Arrangementlinenumber = @arrnumber
          AND PlusProductLineNumber = @Whichday
          OPEN XtabCur
          FETCH NEXT FROM XtabCur
          INTO @AarrLineNumber, @ALineNumber, @APlusProductLineNumber, @APacketItemNumber
          WHILE (@@FETCH_STATUS <> -1)
          BEGIN
            DELETE FROM ArrangementRoomxtab
            WHERE Arrangementlinenumber = @AarrLineNumber
              AND Linenumber = @ALineNumber
              AND PlusProductLineNumber = @APlusProductLineNumber
              AND PacketItemNumber = @APacketItemNumber
            FETCH NEXT FROM XtabCur INTO @AarrLineNumber, @ALineNumber, @APlusProductLineNumber, @APacketItemNumber
          END
          CLOSE XtabCur
          DEALLOCATE XtabCur
        END
      END
      FETCH NEXT FROM DeletesCur INTO @ArrNumber
    END
    CLOSE DeletesCur
    DEALLOCATE DeletesCur
  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 -- is null

    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 DATEDIFF(DAY, @New_EndDate, @Old_Enddate) = DATEDIFF(DAY, @New_StartDate, @Old_StartDate)
	    BEGIN
          SELECT @offset = DATEDIFF(DAY, @New_EndDate, @Old_Enddate)  --Number of days------
          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
		ELSE
		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
      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
  */

  /* 20190723/RN: Should not be necessary anymore as we almost always do a recreate of arrangementlines
  -- DELETE "GHOST" Arrangementlines (Arrangementlines with a PacketItemNumber that no longer exists in PacketItem_NEW table)
  SELECT ArrangementLines.ArrangementLineNumber
  INTO #tmpGhostArrLines
  FROM ArrangementLines (NOLOCK)
  INNER JOIN ArrangementReservationsXtab (NOLOCK) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
  WHERE (1=1)
    AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
    AND ArrangementReservationsXtab.ReferenceNumber = @Referencenumber
    AND (SELECT COUNT(*) FROM PacketItem_NEW WHERE PacketItem_NEW.PacketItemNumber = ArrangementLines.PacketItemNumber) = 0

  DELETE FROM ArrangementReservationsXTab
  WHERE ArrangementLineNumber IN (select ArrangementLineNumber FROM #tmpGhostArrLines WITH (NOLOCK))
  DELETE FROM ArrangementRoomXTab
  WHERE ArrangementLineNumber IN (select ArrangementLineNumber FROM #tmpGhostArrLines WITH (NOLOCK))
  DELETE FROM ArrangementLineDepartmentXTab
  WHERE FK_ArrangementLineNumber IN (select ArrangementLineNumber FROM #tmpGhostArrLines WITH (NOLOCK))
  DELETE FROM ArrangementLines
  WHERE ArrangementLineNumber IN (select ArrangementLineNumber FROM #tmpGhostArrLines WITH (NOLOCK))
  DROP TABLE #tmpGhostArrLines
  */
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  -- LOOK IF THE RESERVATIONLINE HAS AFFECTED THE ARRANGMENT 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 ARRANGMENT BEFORE


  IF @fromnamelist = 1
  BEGIN
    IF (@Old_ProductNumber = @New_ProductNumber)
      AND (@Old_StartDate = @New_StartDate)
      AND (@Old_EndDate = @New_Enddate)
      AND (@Old_NumberOfPersons = @New_NumberOfpersons)
      AND (@Old_NumberOfChildren1 = @New_NumberOfChildren1)
      AND (@Old_NumberOfChildren2 = @New_NumberOfChildren2)
      AND (@New_Pieces = @Old_Pieces)
      RETURN
  END
  ELSE
  BEGIN
    IF (@Old_ProductNumber = @New_ProductNumber)
      AND (@Old_StartDate = @New_StartDate)
      AND (@Old_EndDate = @New_Enddate)
      AND (@Old_NumberOfPersons = @New_NumberOfpersons)
      AND (@Old_NumberOfChildren1 = @New_NumberOfChildren1)
      AND (@Old_NumberOfChildren2 = @New_NumberOfChildren2)
      AND (@New_Pieces = @Old_Pieces)
      AND (@found = 1)
      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 @ToleranceDiffDT = ABS(DATEDIFF(MINUTE, @Tolerance, CONVERT(datetime, '00:00', 108)))

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

  WHILE @CurrentDate <= @FormatedEnddate
  BEGIN
    -- FOR EACH DAY GO GET THE PACKET FOR THE PRODUCT. We always read from the packet, so that we can update arrangement with e.g new - prices and packetlines
	SET @CurrentDateDT = CONVERT(datetime, @CurrentDate, 112)
    SET @Packet = NULL --AV 20030128

	SET @ArrivalPacket = NULL
	SET @StayPacket = NULL
	SET @DeparturePacket = NULL

	--Print @CurrentDate

    IF @CurrentDate = @FormatedStartDate
    BEGIN
      IF @New_ProductNumber = -1  ---1 means that it is a NULL product
      BEGIN
	    SET @Packet = @NullArrPacket
      END
      ELSE
      IF @New_ProductNumber = 0  -- means that no product is chosen on the reservationline, 
      BEGIN
        IF (@fromnamelist = 1)
          AND (@GuestNumber > 1)
          AND (@Old_ProductNumber = 0)
          AND (@Old_NumberOfPersons = 0)
        BEGIN
          --EXEC GetPackageNumber @New_ProductNumber, 0, @CurrentDateDT, @Packet OUTPUT
          SELECT TOP 1 @Packet = 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
		  SET @Packet = @EmptyArrPacket 
      END
      ELSE
      IF @StarProduct = 1 AND @WhichDay > 0
      BEGIN
        --EXEC GetPackageNumber @New_ProductNumber, 0, @CurrentDateDT, @Packet OUTPUT
        SELECT TOP 1 @Packet = 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
      BEGIN
        --EXEC GetPackageNumber @New_ProductNumber, 0, @CurrentDateDT, @Packet OUTPUT
        SELECT TOP 1 @Packet = 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
    END
    ELSE
    IF @CurrentDate = @FormatedEnddate
    BEGIN
      IF @New_ProductNumber = -1 ---1 means that it is a NULL product
      BEGIN
	    SET @Packet = @NullDepPacket
      END
      ELSE
      IF @New_ProductNumber = 0  -- means that no product is chosen on the reservationline, 
      BEGIN
        IF (@fromnamelist = 1)
          AND (@GuestNumber > 1)
          AND (@Old_ProductNumber = 0)
          AND (@Old_NumberOfPersons = 0)
        BEGIN
          --EXEC GetPackageNumber @New_ProductNumber, 2, @CurrentDateDT, @Packet OUTPUT
          SELECT TOP 1 @Packet = 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 = 2
          ORDER BY #PackageInfo.View_Id DESC
        END
        ELSE
		  SET @Packet = @EmptyDepPacket
      END
      ELSE
      BEGIN
        --EXEC GetPackageNumber @New_ProductNumber, 2, @CurrentDateDT, @Packet OUTPUT
        SELECT TOP 1 @Packet = 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 = 2
        ORDER BY #PackageInfo.View_Id DESC
      END
    END
    ELSE --Stay
    BEGIN
      IF @New_ProductNumber = -1 ---1 means that it is a NULL product
      BEGIN
	    SET @Packet = @NullStayPacket
      END
      ELSE IF @New_ProductNumber = 0  -- means that no product is chosen on the reservationline, 
      BEGIN
        IF (@FromNameList = 1) AND (@GuestNumber > 1) AND (@Old_ProductNumber = 0) AND (@Old_NumberOfPersons = 0)
        BEGIN
          --EXEC GetPackageNumber @New_ProductNumber, 1, @CurrentDateDT, @Packet OUTPUT
          SELECT TOP 1 @Packet = 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 = 1
          ORDER BY #PackageInfo.View_Id DESC
        END
        ELSE
		  SET @Packet = @EmptyStayPacket
    END
    ELSE
    BEGIN
      --EXEC GetPackageNumber @New_ProductNumber, 1, @CurrentDateDT, @Packet OUTPUT
      SELECT TOP 1 @Packet = 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 = 1
      ORDER BY #PackageInfo.View_Id DESC
    END
  END

  --Print 'Packet number: ' +  CAST(@Packet AS CHAR(5))

    -- FOR EACH DAY DECLARE A LOOP THROUGH LIST (A CURSOR) CONTAINING ONLY RELEVANT PACKETLINES .. E.G IF THERE IS NO CHILDREN ON THE RESERVATIONLINE THEN THIS CURSOR SHOULDNT
    -- CONTAIN PACKETLINES WITH DEBIT TYPES 4 - 9 (CHILDREN DEBITTYPES)

  DECLARE @SQLStr nvarchar(2000)

  SET @SQLStr = 'SELECT DISTINCT ' +
        'PacketItem_New.PacketItemNumber, ' +
        'PacketNumber, ' +
        'PacketItem_New.WareNumber, ' +
        'Time, ' +
        'DepartureTime, ' +
        'TextOverWrite, ' +
        'PriceOverWrite, ' +
        'debtype, ' +
        'CONVERT(varchar(4000), note), ' +
        'ReservationDisCount, ' +
        'Ware.BookTimeInMinutes, ' +
		'PacketItem_NEW.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_NEWRoomTypeXTab WITH (NOLOCK) ON PacketItem_NEWRoomTypeXTab.PacketItemNumber = PacketItem_NEW.PacketItemNumber ' +
      '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 = ' + CAST(@Packet AS VARCHAR(5)) +
      '  AND PacketItem_New.Arrangement = 1' +
      '  AND (PacketItem_NEWRoomTypeXTab.RoomTypeNumber = ' + CAST(@RoomTypeNumber AS VARCHAR(5)) + ' OR ISNULL(PacketItem_NEWRoomTypeXTab.RoomTypeNumber, 0) = 0)' +
      '  AND ((' + CAST(@HotelNumber AS VARCHAR(3)) + ' = -1) OR (Departments.HotelNumber = ' + CAST(@HotelNumber AS VARCHAR(3)) + '))'

  IF (@New_NumberOfChildren1 = 0)
    AND (@Old_NumberOfChildren1 = 0)
    AND (@Old_NumberOfChildren2 = 0)
    AND (@New_NumberOfChildren2 = 0) --That means that there is/has not been any children on the reservationline .. 
  BEGIN
    SET @SQLStr = @SQLStr + ' AND (Debtype IN (1,2,3))'
  END
  ELSE
  IF ((@New_NumberOfChildren1 <> @Old_NumberofChildren1) OR (@NEw_NumberofChildren1 > 0))
      AND @New_NumberOfChildren2 = 0
      AND @old_NumberOfChildren2 = 0
      AND @New_NumberOfPersons = 0
      AND @old_NumberOfPersons = 0 --Only children 1, can only happen from namelist tabsheet if one of the children 1 modifies arrangement
  BEGIN
    SET @SQLStr = @SQLStr + ' AND (Debtype IN (4,5,6))'
  END
  ELSE
  IF ((@New_NumberOfChildren2 <> @Old_NumberofChildren2) OR (@NEw_NumberofChildren2 > 0))
      AND @New_NumberOfChildren1 = 0
      AND @Old_NumberOfChildren1 = 0
      AND @New_NumberOfPersons = 0
      AND @old_NumberOfPersons = 0 --Only children 2, can only happen from namelist tabsheet if one of the children 2 modifies arrangement
  BEGIN
    SET @SQLStr = @SQLStr + ' AND (Debtype IN (7,8,9))'
  END
  ELSE
  IF ((@New_NumberOfPersons <> @Old_NumberofPersons) OR (@NEw_NumberofPersons > 0))
      AND ((@New_NumberOfChildren2 <> @Old_NumberofChildren2) OR (@NEw_NumberofChildren2 > 0))
      AND @New_NumberOfChildren1 = 0 AND @Old_NumberOfChildren1 = 0   --Adults and children 2 (no children 1)
  BEGIN
    SET @SQLStr = @SQLStr + ' AND (Debtype NOT IN (4,5,6))'
  END
  ELSE
  IF ((@New_NumberOfPersons <> @Old_NumberofPersons) OR (@NEw_NumberofPersons > 0))
      AND ((@New_NumberOfChildren1 <> @Old_NumberofChildren1) OR (@NEw_NumberofChildren1 > 0))
      AND @New_NumberOfChildren2 = 0 AND @Old_NumberOfChildren2 = 0   --Last case : Adults and children 1 (no children 2)
  BEGIN
    SET @SQLStr = @SQLStr + ' AND (Debtype NOT IN (7,8,9))'
  END
  ELSE
  IF ((@New_NumberOfPersons <> @Old_NumberofPersons) OR (@NEw_NumberofPersons > 0))
      AND ((@New_NumberOfChildren1 <> @Old_NumberofChildren1) OR (@NEw_NumberofChildren1 > 0))
      AND ((@New_NumberOfChildren2 <> @Old_NumberofChildren2) OR (@NEw_NumberofChildren2 > 0))
  BEGIN
    SET @SQLStr = @SQLStr
  END
  ELSE
  IF ((@New_NumberOfChildren1 <> @Old_NumberofChildren1) OR (@NEw_NumberofChildren1 > 0))
      AND ((@New_NumberOfChildren2 <> @Old_NumberofChildren2) OR (@NEw_NumberofChildren2 > 0))
      AND @New_NumberOfPersons = 0 AND @Old_NumberOfPersons = 0   --Children1 and children 2 (no adults)
  BEGIN
    SET @SQLStr = @SQLStr + ' AND (Debtype NOT IN (1,2,3))'
  END

  print @SQLStr

    /*
        so the seven situations just above is:
        1. Only adults
        2. Only children 1 (can only happen in namelist)
        3. Only Children 2 (can only happen in namelist)
        4. Adults + children 2 
        5. Adults + children 1 
        6. Adults + children 1+ Children 2
        7. children1 + children 2
        -- is there more situations
    */

  IF @AFromRecreate = 0
  BEGIN
    IF OBJECT_ID('tempdb..#TempPacketItems') IS NOT NULL
      TRUNCATE TABLE #TempPacketItems
	ELSE
	BEGIN
      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
      )
	END
  END

  DELETE FROM #TempPacketItems
  INSERT INTO #TempPacketItems EXEC sp_executesql @SQLStr
    
  DECLARE CopyCur CURSOR FOR
    SELECT * FROM #TempPacketItems

  OPEN CopyCur
  FETCH NEXT FROM CopyCur
  INTO @PacketItemNumber, @PacketNumber, @WareNumber, @Time, @DepTime, @TextOverWrite, @PriceOverWrite, @debtype, @note, @reservationDiscount, @BookTimeInMinutes, @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 @TimeDT = CONVERT(datetime, @Time, 108)
      IF ISNULL(@PriceOverWrite, 0) = 0 -- is null 
        SELECT @PriceOverWrite = 0

      SELECT @WareHasRule = 0
	  IF (ISNULL(@FK_RecurringPatternId, 0 ) > 0) OR (ISNULL(@FK_WareLinkNumber, 0) > 0)
	    SELECT @WareHasRule = 1

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

      IF @DebType = 1     --Fixed Price Per room /Hall (adult)
      BEGIN
        IF (@found = 0)
          OR (@New_ProductNumber <> @Old_ProductNumber)
          OR (@CurrentDate > @FormatedOldEnddate)
          OR (@CurrentDate < @FormatedOldStartDate)
          OR (@currentdate = @FormatedEnddate
          AND @formatedenddate < @FormatedOldEnddate)
          OR (@Currentdate = @FormatedStartDate
          AND @formatedStartdate > @formatedOldStartDate)
          OR (@CurrentDate = @FormatedOldEndDate
          AND @FormatedEnddate > @FormatedOldEnddate)
          OR (@CurrentDate = @FormatedOldStartDate
          AND @FormatedStartdate < @FormatedOldStartDate)
        BEGIN
          IF @FromNameList = 1
          BEGIN
            SELECT @Pieces = 1
            SELECT @TotalPrice = @PriceOverWrite
          END
          ELSE
          BEGIN
            SELECT @pieces = 1 * @New_Pieces
            SELECT @TotalPrice = @PriceoverWrite * @New_Pieces
          END
        END
        ELSE
        BEGIN
          IF @FromNameList = 1
          BEGIN
            SELECT @Pieces = 0
            SELECT @TotalPrice = 0
          END
          ELSE
          BEGIN
            SELECT @Pieces = @New_Pieces - @Old_Pieces
            SELECT @TotalPrice = 0
          END
        END
      END
      ELSE
      IF @DebType = 2   -- % of room price /Hall (adult)
      BEGIN
        IF (@found = 0)
          OR (@New_ProductNumber <> @Old_ProductNumber)
          OR (@CurrentDate > @FormatedOldEnddate)
          OR (@CurrentDate < @FormatedOldStartDate)
          OR (@currentdate = @FormatedEnddate
          AND @formatedenddate < @FormatedOldEnddate)
          OR (@Currentdate = @FormatedStartDate
          AND @formatedStartdate > @formatedOldStartDate)
          OR (@CurrentDate = @FormatedOldEndDate
          AND @FormatedEnddate > @FormatedOldEnddate)
          OR (@CurrentDate = @FormatedOldStartDate
          AND @FormatedStartdate < @FormatedOldStartDate)
        BEGIN
          IF @Fromnamelist = 1
          BEGIN
            SELECT @Pieces = @New_numberofPersons
          END
          ELSE
            SELECT @pieces = @New_NumberOfpersons * @New_Pieces
          SELECT @TotalPrice = 0
          SELECT @PriceOverWrite = 0
        END
        ELSE
        BEGIN
          IF @FromNameList = 1
          BEGIN
            IF @new_NumberOfPersons <> @Old_NumberOfPersons --is used if namelistreservationperperson = false and we have put a new person to the room
              SELECT @Pieces = @new_NumberOfPersons - @Old_NumberOfPersons
            ELSE
              SELECT @Pieces = 0
            SELECT @TotalPrice = 0
            SELECT @PriceOverWrite = 0
          END
          ELSE
          BEGIN
            IF @New_NumberOfpersons - @Old_NumberOfPersons = 0
              AND @New_Pieces - @old_Pieces = 0
            BEGIN
              SELECT @Pieces = 0
              SELECT @TotalPrice = 0
              SELECT @PriceOverWrite = 0
            END
            ELSE
            BEGIN
              SELECT @Pieces = (@New_NumberOfPersons * @New_Pieces) - (@Old_NumberOfPersons * @Old_Pieces)
              SELECT @TotalPrice = 0
              SELECT @PriceOverWrite = 0
            END
          END
        END
      END
      ELSE
      IF @DebType = 3  --Price * Adult pax
      BEGIN
        IF (@found = 0)
          OR (@New_ProductNumber <> @Old_ProductNumber)
          OR (@CurrentDate > @FormatedOldEnddate)
          OR (@CurrentDate < @FormatedOldStartDate)
          OR (@currentdate = @FormatedEnddate
          AND @formatedenddate < @FormatedOldEnddate)
          OR (@Currentdate = @FormatedStartDate
          AND @formatedStartdate > @formatedOldStartDate)
          OR (@CurrentDate = @FormatedOldEndDate
          AND @FormatedEnddate > @FormatedOldEnddate)
          OR (@CurrentDate = @FormatedOldStartDate
          AND @FormatedStartdate < @FormatedOldStartDate)
        BEGIN
          IF @Fromnamelist = 1
          BEGIN
            SELECT @Pieces = @New_numberofPersons
            SELECT @TotalPrice = @PriceoverWrite * @New_numberofPersons
          END
          ELSE
          BEGIN
            SELECT @pieces = @New_NumberOfpersons * @New_Pieces
            SELECT @TotalPrice = @New_NumberOfpersons * @PriceoverWrite * @New_Pieces
          END
        END
        ELSE
        BEGIN
          IF @FromNamelist = 1
          BEGIN
            IF @new_NumberOfPersons <> @Old_NumberOfPersons --is used if namelistreservationperperson = false and we have put a new person to the room
            BEGIN
              SELECT @Pieces = @new_NumberOfPersons - @Old_NumberOfPersons
              SELECT @TotalPrice = @PriceoverWrite * (@new_NumberOfPersons - @Old_NumberOfPersons)
            END
            ELSE
            BEGIN
              SELECT @Pieces = 0
              SELECT @TotalPrice = 0
            END
          END
          ELSE
          BEGIN
            IF @New_NumberOfpersons - @Old_NumberOfPersons = 0
              AND @New_Pieces - @old_Pieces = 0
            BEGIN
              SELECT @Pieces = 0
              SELECT @TotalPrice = 0
            END
            ELSE
            BEGIN
              SELECT @TotalPrice = ((@New_NumberOfpersons * @New_Pieces) - (@Old_NumberOfPersons * @Old_Pieces)) * @PriceOverWrite
              SELECT @Pieces = (@New_NumberOfPersons * @New_Pieces) - (@Old_NumberOfPersons * @Old_Pieces)
            END
          END
        END
      END
      ELSE
      IF @DebType = 4 --Fixed Price Per room /Hall (children 1)
      BEGIN
        IF (@found = 0)
          OR (@New_ProductNumber <> @Old_ProductNumber)
          OR (@CurrentDate > @FormatedOldEnddate)
          OR (@CurrentDate < @FormatedOldStartDate)
          OR (@currentdate = @FormatedEnddate
          AND @formatedenddate < @FormatedOldEnddate)
          OR (@Currentdate = @FormatedStartDate
          AND @formatedStartdate > @formatedOldStartDate)
          OR (@CurrentDate = @FormatedOldEndDate
          AND @FormatedEnddate > @FormatedOldEnddate)
          OR (@CurrentDate = @FormatedOldStartDate
          AND @FormatedStartdate < @FormatedOldStartDate)
        BEGIN
          IF @FromNameList = 1
          BEGIN
            SELECT @Pieces = 1
            SELECT @TotalPrice = @PriceOverWrite
          END
          ELSE
          BEGIN
            SELECT @pieces = 1 * @New_Pieces
            SELECT @TotalPrice = @PriceoverWrite
          END
        END
        ELSE
        BEGIN
          IF @FromNameList = 1
          BEGIN
            SELECT @Pieces = 0
            SELECT @TotalPrice = 0
          END
          ELSE
          BEGIN
            SELECT @Pieces = @New_Pieces - @Old_Pieces
            SELECT @TotalPrice = 0
          END
        END
      END
      ELSE
      IF @DebType = 5   --Price * Children 1 pax
      BEGIN
        IF (@found = 0)
          OR (@New_ProductNumber <> @Old_ProductNumber)
          OR (@CurrentDate > @FormatedOldEnddate)
          OR (@CurrentDate < @FormatedOldStartDate)
          OR (@currentdate = @FormatedEnddate
          AND @formatedenddate < @FormatedOldEnddate)
          OR (@Currentdate = @FormatedStartDate
          AND @formatedStartdate > @formatedOldStartDate)
          OR (@CurrentDate = @FormatedOldEndDate
          AND @FormatedEnddate > @FormatedOldEnddate)
          OR (@CurrentDate = @FormatedOldStartDate
          AND @FormatedStartdate < @FormatedOldStartDate)
        BEGIN
          IF @FromNameList = 1
          BEGIN
            SELECT @Pieces = @New_NumberofChildren1
            SELECT @TotalPrice = @PriceoverWrite * @New_NumberofChildren1
          END
          ELSE
          BEGIN
            SELECT @pieces = @New_NumberOfChildren1 * @New_Pieces
            SELECT @TotalPrice = @New_NumberOfChildren1 * @PriceoverWrite
          END
        END
        ELSE
        BEGIN
          IF @FromNameList = 1
          BEGIN
            IF @new_NumberOfChildren1 <> @Old_NumberOfChildren1 --is used if namelistreservationperperson = false and we have put a new child1 to the room
            BEGIN
              SELECT @Pieces = @new_NumberOfChildren1 - @Old_NumberOfchildren1
              SELECT @TotalPrice = @PriceoverWrite * (@new_NumberOfChildren1 - @Old_NumberOfchildren1)
            END
            ELSE
            BEGIN
              SELECT @Pieces = 0
              SELECT @TotalPrice = 0
            END
          END
          ELSE
          BEGIN
            IF @New_NumberOfChildren1 - @Old_NumberOfChildren1 = 0
              AND @New_Pieces - @old_Pieces = 0
            BEGIN
              SELECT @Pieces = 0
              SELECT @TotalPrice = 0
            END
            ELSE
            BEGIN
              SELECT @TotalPrice = ((@New_NumberOfChildren1 * @New_Pieces) - (@Old_NumberOfChildren1 * @Old_Pieces)) * @PriceOverWrite
              SELECT @Pieces = (@New_NumberOfChildren1 * @New_Pieces) - (@Old_NumberOfChildren1 * @Old_Pieces)
            END
          END
        END
      END
      ELSE
      IF @DebType = 6   -- % of room price /Hall (Children 1)
      BEGIN
        IF (@found = 0)
          OR (@New_ProductNumber <> @Old_ProductNumber)
          OR (@CurrentDate > @FormatedOldEnddate)
          OR (@CurrentDate < @FormatedOldStartDate)
          OR (@currentdate = @FormatedEnddate
          AND @formatedenddate < @FormatedOldEnddate)
          OR (@Currentdate = @FormatedStartDate
          AND @formatedStartdate > @formatedOldStartDate)
          OR (@CurrentDate = @FormatedOldEndDate
          AND @FormatedEnddate > @FormatedOldEnddate)
          OR (@CurrentDate = @FormatedOldStartDate
          AND @FormatedStartdate < @FormatedOldStartDate)
        BEGIN
          IF @Fromnamelist = 1
            SELECT @pieces = @New_NumberOfChildren1
          ELSE
            SELECT @pieces = @New_NumberOfChildren1 * @New_Pieces
          SELECT @TotalPrice = 0
          SELECT @PriceOverWrite = 0
        END
        ELSE
        BEGIN
          IF @FromNameList = 1
          BEGIN
            IF @new_NumberOfChildren1 <> @Old_NumberOfChildren1 --is used if namelistreservationperperson = false and we have put a new Child to the room
              SELECT @Pieces = @new_NumberOfChildren1 - @Old_NumberOfChildren1
            ELSE
              SELECT @Pieces = 0
            SELECT @TotalPrice = 0
            SELECT @PriceOverWrite = 0
          END
          ELSE
          BEGIN
            IF @New_NumberOfChildren1 - @Old_NumberOfChildren1 = 0
              AND @New_Pieces - @old_Pieces = 0
            BEGIN
              SELECT @Pieces = 0
              SELECT @TotalPrice = 0
              SELECT @PriceOverWrite = 0
            END
            ELSE
            BEGIN
              SELECT @Pieces = (@New_NumberOfChildren1 * @New_Pieces) - (@Old_NumberOfChildren1 * @Old_Pieces)
              SELECT @TotalPrice = 0
              SELECT @PriceOverWrite = 0
            END
          END
        END
      END
      ELSE
      IF @DebType = 7 --Fixed Price Per room /Hall (children 2)
      BEGIN
        IF (@found = 0)
          OR (@New_ProductNumber <> @Old_ProductNumber)
          OR (@CurrentDate > @FormatedOldEnddate)
          OR (@CurrentDate < @FormatedOldStartDate)
          OR (@currentdate = @FormatedEnddate
          AND @formatedenddate < @FormatedOldEnddate)
          OR (@Currentdate = @FormatedStartDate
          AND @formatedStartdate > @formatedOldStartDate)
          OR (@CurrentDate = @FormatedOldEndDate
          AND @FormatedEnddate > @FormatedOldEnddate)
          OR (@CurrentDate = @FormatedOldStartDate
          AND @FormatedStartdate < @FormatedOldStartDate)
        BEGIN
          IF @FromNameList = 1
          BEGIN
            SELECT @Pieces = 1
            SELECT @TotalPrice = @PriceOverWrite
          END
          ELSE
          BEGIN
            SELECT @pieces = 1 * @New_Pieces
            SELECT @TotalPrice = @PriceoverWrite
          END
        END
        ELSE
        BEGIN
          IF @FromNameList = 1
          BEGIN
            SELECT @Pieces = 0
            SELECT @TotalPrice = 0
          END
          ELSE
          BEGIN
            SELECT @Pieces = @New_Pieces - @Old_Pieces
            SELECT @TotalPrice = 0
          END
        END
      END
      ELSE
      IF @DebType = 8   --Price * Children 2 pax
      BEGIN
        IF (@found = 0)
          OR (@New_ProductNumber <> @Old_ProductNumber)
          OR (@CurrentDate > @FormatedOldEnddate)
          OR (@CurrentDate < @FormatedOldStartDate)
          OR (@currentdate = @FormatedEnddate
          AND @formatedenddate < @FormatedOldEnddate)
          OR (@Currentdate = @FormatedStartDate
          AND @formatedStartdate > @formatedOldStartDate)
          OR (@CurrentDate = @FormatedOldEndDate
          AND @FormatedEnddate > @FormatedOldEnddate)
          OR (@CurrentDate = @FormatedOldStartDate
          AND @FormatedStartdate < @FormatedOldStartDate)
        BEGIN
          IF @FromNameList = 1
          BEGIN
            SELECT @Pieces = @New_NumberOfChildren2
            SELECT @TotalPrice = @PriceoverWrite * @New_NumberOfChildren2
          END
          ELSE
          BEGIN
            SELECT @pieces = @New_NumberOfChildren2 * @New_Pieces
            SELECT @TotalPrice = @New_NumberOfChildren2 * @PriceoverWrite
          END
        END
        ELSE
        BEGIN
          IF @FromNameList = 1
          BEGIN
            IF @new_NumberOfChildren2 <> @Old_NumberOfchildren2 --is used if namelistreservationperperson = false and we have put a new child2 to the room
            BEGIN
              SELECT @Pieces = @new_NumberOfChildren2 - @Old_NumberOfChildren2
              SELECT @TotalPrice = @PriceoverWrite * (@new_NumberOfChildren2 - @Old_NumberOfChildren2)
            END
            ELSE
            BEGIN
              SELECT @Pieces = 0
              SELECT @TotalPrice = 0
            END
          END
          ELSE
          BEGIN
            IF @New_NumberOfChildren2 - @Old_NumberOfChildren2 = 0
              AND @New_Pieces - @old_Pieces = 0
            BEGIN
              SELECT @Pieces = 0
              SELECT @TotalPrice = 0
            END
            ELSE
            BEGIN
              SELECT @TotalPrice = ((@New_NumberOfChildren2 * @New_Pieces) - (@Old_NumberOfChildren2 * @Old_Pieces)) * @PriceOverWrite
              SELECT @Pieces = (@New_NumberOfChildren2 * @New_Pieces) - (@Old_NumberOfChildren2 * @Old_Pieces)
            END
          END
        END
      END
      ELSE
      IF @DebType = 9   -- % of room price /Hall (Children 2)
      BEGIN
        IF (@found = 0)
          OR (@New_ProductNumber <> @Old_ProductNumber)
          OR (@CurrentDate > @FormatedOldEnddate)
          OR (@CurrentDate < @FormatedOldStartDate)
          OR (@currentdate = @FormatedEnddate
          AND @formatedenddate < @FormatedOldEnddate)
          OR (@Currentdate = @FormatedStartDate
          AND @formatedStartdate > @formatedOldStartDate)
          OR (@CurrentDate = @FormatedOldEndDate
          AND @FormatedEnddate > @FormatedOldEnddate)
          OR (@CurrentDate = @FormatedOldStartDate
          AND @FormatedStartdate < @FormatedOldStartDate)
        BEGIN
          IF @Fromnamelist = 1
            SELECT @Pieces = @New_NumberOfChildren2
          ELSE
            SELECT @pieces = @New_NumberOfChildren2 * @New_Pieces
          SELECT @TotalPrice = 0
          SELECT @PriceOverWrite = 0
        END
        ELSE
        BEGIN
          IF @FromNameList = 1
          BEGIN
            IF @new_NumberOfChildren2 <> @Old_NumberOfChildren2 --is used if namelistreservationperperson = false and we have put a new Child2 to the room
              SELECT  @Pieces = @new_NumberOfChildren2 - @Old_NumberOfChildren2
            ELSE
              SELECT @Pieces = 0
            SELECT @TotalPrice = 0
            SELECT @PriceOverWrite = 0
          END
          ELSE
          BEGIN
            IF @New_NumberOfChildren2 - @Old_NumberOfChildren2 = 0
              AND @New_Pieces - @old_Pieces = 0
            BEGIN
              SELECT @Pieces = 0
              SELECT @TotalPrice = 0
              SELECT @PriceOverWrite = 0
            END
            ELSE
            BEGIN
              SELECT @Pieces = (@New_NumberOfChildren2 * @New_Pieces) - (@Old_NumberOfChildren2 * @Old_Pieces)
              SELECT @TotalPrice = 0
              SELECT @PriceOverWrite = 0
            END
          END
        END
      END
	  
      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	  
      IF NOT (@found = 1 AND @Pieces = 0 AND @totalPrice = 0)  -- do not update the records if it is not neccesary. 
      BEGIN
        SET LOCK_TIMEOUT 0 --500
        SELECT @TempArrNumber = -1 --initialize

        SELECT @TempArrNumber = Arrangementlinenumber
        FROM arrangementlines WITH (NOLOCK)
        WHERE Arrangementlines.date = @CurrentDate
		  AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
          AND ArrangementLines.ArrangementLineNumber IN (SELECT DISTINCT
                                                           ArrangementRoomXTab.ArrangementLineNumber
                                                         FROM arrangementRoomXtab WITH (NOLOCK)
                                                         WHERE arrangementRoomXtab.LineNumber = @LineNumber
                                                           AND PacketItemNumber = @PacketItemNumber)

        IF @USETOLERANCE = 1
        BEGIN
          IF @@RowCount = 0
          BEGIN
            SELECT  
			  @TempArrNumber = ArrangementLinenumber
            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, @TimeDT, TimeDT)) <= @ToleranceDiffDT))			  
              AND Date = @CurrentDate
              AND arrangementLines.ArrangementLineNumber IN (SELECT DISTINCT
                                                               ArrangementReservationsXTab.ArrangementLineNumber
                                                             FROM ArrangementReservationsXtab WITH (NOLOCK)
                                                             WHERE ArrangementReservationsXtab.Referencenumber = @Referencenumber)
            IF @TempArrNumber > -1
			BEGIN
			  SELECT @TempCount = COUNT(*)
			  FROM ArrangementReservationsXtab WITH (NOLOCK)
              WHERE Referencenumber = @ReferenceNumber 
                AND GuestNumber = @GuestNumber 
                AND ArrangementLineNumber = @TempArrNumber  
                AND PlusProductLineNumber = @WhichDay

			  IF @TempCount = 0
                INSERT INTO ArrangementReservationsXtab VALUES (@TempArrNumber, @Referencenumber, @GuestNumber, @WhichDay)  
				        
              /* Removed as it can cause Lock request timeouts
			  UPDATE ArrangementReservationsXtab
			  SET PlusProductLineNumber = @WhichDay
              WHERE Referencenumber = @ReferenceNumber 
                AND GuestNumber = @GuestNumber 
                AND ArrangementLineNumber = @TempArrNumber  
                AND PlusProductLineNumber = @WhichDay

			  IF @@ROWCOUNT = 0
			  BEGIN
			    SELECT @TempCount = COUNT(*) FROM ArrangementReservationsXtab WITH (NOLOCK) 
				WHERE ArrangementLineNumber = @TempArrNumber 
				  AND ReferenceNumber = @Referencenumber 
				  AND GuestNumber = @GuestNumber
				  AND PlusProductLineNumber = @WhichDay
                IF @TempCount = 0
                  INSERT INTO ArrangementReservationsXtab VALUES (@TempArrNumber, @Referencenumber, @GuestNumber, @WhichDay)          
			  END
			  */

 		      SELECT @TempCount = COUNT(*) FROM ArrangementReservationsXtab WITH (NOLOCK) 
			  WHERE ArrangementLineNumber = @TempArrNumber 
				AND ReferenceNumber = @Referencenumber 
				AND GuestNumber = @GuestNumber
				AND PlusProductLineNumber = @WhichDay

			  IF @TempCount = 0
                INSERT INTO ArrangementRoomxtab (ArrangementLineNumber, LineNumber, PlusProductLineNumber, PacketItemNumber) VALUES (@TempArrNumber, @LineNumber, @WhichDay, @PacketItemNumber)

              /*  Removed as it can cause Lock request timeouts
              UPDATE ArrangementRoomXtab
			  SET PlusProductLineNumber = @WhichDay
              WHERE ArrangementlineNumber = @TempArrNumber
                AND LineNumber = @LineNumber 
                AND PlusProductLineNumber = @WhichDay
                AND PacketItemNumber = @PacketItemNumber

              IF @@ROWCOUNT = 0
			  BEGIN
			    SELECT @TempCount = COUNT(*) FROM ArrangementReservationsXtab WITH (NOLOCK) 
				WHERE ArrangementLineNumber = @TempArrNumber 
				  AND ReferenceNumber = @Referencenumber 
				  AND GuestNumber = @GuestNumber
				  AND PlusProductLineNumber = @WhichDay
				IF @TempCount = 0
                  INSERT INTO ArrangementRoomxtab (ArrangementLineNumber, LineNumber, PlusProductLineNumber, PacketItemNumber) VALUES (@TempArrNumber, @LineNumber, @WhichDay, @PacketItemNumber)
			  END
			  */
			END
		  END
        END

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

        IF @UseAdjustArrLinesToBookingTime = 1
        BEGIN
          SET @CurrentDateAndTime = CONVERT(datetime, @CurrentDate + ' ' + @Time, 112)
		  IF (@CurrentDateAndTime >= @OriginalStartDateAndTime) AND (@CurrentDateAndTime <= @OriginalEndDateAndTime)
		  BEGIN
            UPDATE ArrangementLines
            SET Pieces = Pieces + @Pieces
            WHERE ArrangementLines.ArrangementLineNumber = @TempArrNumber
		      AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
		  END
		END
		ELSE
		BEGIN
          UPDATE ArrangementLines
          SET Pieces = Pieces + @Pieces
          WHERE ArrangementLineNumber = @TempArrNumber
		    AND ISNULL(ArrangementLines.PacketItemNumber, 0) > 0
		END

        IF @@ROWCOUNT = 0
        BEGIN
          IF @Pieces > 0
          BEGIN
            IF @SuggestHallNumberOnPacketLine = 1
            BEGIN
              SELECT
                @hallnumber = Conferenceroomnumber,
                @hallArrivaldate = arrivaldate,
                @HallDepartureDate = departuredate
              FROM reservationconferenceline WITH (NOLOCK)
              WHERE referencenumber = @referencenumber
              AND screenline = (SELECT
                MIN(screenline)
              FROM reservationconferenceline WITH (NOLOCK)
              WHERE referencenumber = @referencenumber)  --only take hallnumber from first conferenceline
              IF ISNULL(@hallnumber, 0) > 0 -- is not null
                IF (@currentdate < @HallArrivalDate)
                  OR (@Currentdate > @HallDepartureDate)
                  SET @hallnumber = NULL
            END


            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 @CopyRoomNumberToPlaceCol = 1 --AV 2008 0514
            BEGIN
              SELECT @Place = Rooms.Number  FROM ReservationRoomLine WITH (NOLOCK)
              INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber
              WHERE LineNumber IN (SELECT DISTINCT LineNumber
                                   FROM ReservationRoomLineXTab WITH (NOLOCK)
                                   WHERE ReferenceNumber = @ReferenceNumber)
                AND ScreenLine = 1
            END
            ELSE
              SET @Place = NULL

			SET @IdentityValue = 0
            IF @UseAdjustArrLinesToBookingTime = 1
			BEGIN
			  SET @CurrentDateAndTime = CONVERT(datetime, @CurrentDate + ' ' + @Time, 112)
			  IF ((@CurrentDateAndTime >= @OriginalStartDateAndTime) AND (@CurrentDateAndTime <= @OriginalEndDateAndTime)) 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, /*TotalPrice,*/ Pieces, WareText, WareNumber, note, hallnumber, FK_PacketChargeDepartment, PlaceText, PacketItemTime, IsUserModified,
 				                            PacketItemWareNumber, PacketItemWareText, PacketItemPrice, WareHasRule, IsArrLineMoved)
                VALUES (@CurrentDate, @PacketItemNumber, @Time, @DepartureTime, @PriceoverWrite, /*@TotalPrice,*/ @Pieces, @TextoverWrite, @WareNumber, @note, @hallnumber, @PacketChargeDepartment, @Place, @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, @whichday)
              INSERT INTO ArrangementRoomxtab (arrangementlinenumber, linenumber, plusProductLineNumber, packetitemnumber) VALUES (@IdentityValue, @LineNumber, @Whichday, @PacketItemNumber)

              SELECT DISTINCT FK_Department INTO #TempPacketDepartment
              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)

			  DELETE FROM ArrangementLineDepartmentXTab WHERE FK_ArrangementLineNumber = @IdentityValue
			  INSERT INTO ArrangementLineDepartmentXTab (FK_ArrangementLineNumber, FK_DepartmentNumber) SELECT @IdentityValue, FK_Department FROM #TempPacketDepartment
			  DROP TABLE #TempPacketDepartment
			END
          END
        END -- IF @MyRowCount
        ELSE
        BEGIN
          IF @FromNameList = 1 OR @GuestNumber = 0
          BEGIN
            SET @guestfound = 1
            -- we have to link the guest to the just updated arrangement record. We look in crosstable if he already is linked to the line else we insert a record in the arrangementreservationsXtab table

			SELECT @TempCount = COUNT(*) 
			FROM ArrangementReservationsXtab WITH (NOLOCK)
			WHERE ArrangementLineNumber = @TempArrNumber
              AND ReferenceNumber = @Referencenumber 
			  AND GuestNumber = @GuestNumber 
			  AND PlusProductLineNumber = @WhichDay

            IF @TempCount = 0
              INSERT INTO ArrangementReservationsXtab VALUES (@TempArrNumber, @referenceNumber, @GuestNumber, @whichday)

            /* Removed as it can cause Lock request timeouts
			UPDATE ArrangementReservationsXtab
			SET ArrangementLineNumber = @TempArrNumber
			WHERE ArrangementLineNumber = @TempArrNumber AND ReferenceNumber = @Referencenumber AND GuestNumber = @GuestNumber AND PlusProductLineNumber = @WhichDay

			IF @@ROWCOUNT = 0
            BEGIN
              INSERT INTO ArrangementReservationsXtab VALUES (@TempArrNumber, @referenceNumber, @GuestNumber, @whichday)
            END
			*/
          END

		  /*
		  UPDATE ArrangementRoomXtab
		  SET PacketItemNumber = @PacketItemNumber
          WHERE ArrangementlineNumber = @TempArrNumber
            AND lineNumber = @LineNumber
            AND PlusProductLineNumber = @Whichday
            AND packetItemNumber = @packetItemNumber
          */
          SELECT @oldPacketItemNumberCount = COUNT(*) 
		  FROM ArrangementRoomXtab WITH (NOLOCK)
          WHERE ArrangementlineNumber = @TempArrNumber
            AND lineNumber = @LineNumber
            AND PlusProductLineNumber = @Whichday
            AND packetItemNumber = @packetItemNumber
		  
		  IF @oldPacketItemNumberCount = 0
		  BEGIN
            INSERT INTO ArrangementRoomxtab (arrangementlinenumber, linenumber, PlusProductLineNumber, packetitemnumber)
              VALUES (@TempArrNumber, @LineNumber, @whichday, @PacketItemNumber)
		  END
          ELSE
          BEGIN
            SET @guestfound = 1

            SELECT @Pieces = Pieces
            FROM arrangementlines WITH (NOLOCK)
            WHERE arrangementlinenumber = @TempArrNumber --If pieces in an arrangementline has turned to 0 then delete it
            IF @Pieces = 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
        END

        SET LOCK_TIMEOUT 0
      END
      FETCH NEXT FROM CopyCur INTO @PacketItemNumber, @PacketNumber, @WareNumber, @Time, @DepTime, @TextOverWrite, @PriceOverWrite, @debtype, @note, @Reservationdiscount, @BookTimeInMinutes, @PacketItemMandatory, @FK_RecurringPatternId, @FK_WareLinkNumber
    END
    CLOSE copycur
    DEALLOCATE copycur
    SET @New_StartDate = @New_StartDate + 1
    SELECT @CurrentDate = CONVERT(char(10), @New_StartDate, 112)
  END
  IF @AFromRecreate = 0
  BEGIN
    DROP TABLE #TempPacketItems
    DROP TABLE #PackageInfo
  END