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
AS
  SET NOCOUNT ON

  -- 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 @Packet 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 @WeReadFromPacket int

  -- Other
  DECLARE @MyRowCount int


  --Declare and initiate variables for looping through the days of the arrangement
  DECLARE @FormatedStartDate char(8)
  DECLARE @FormatedEndDate char(8)
  DECLARE @FormatedOldStartDate char(8)
  DECLARE @FormatedOldEnddate char(8)
  DECLARE @CurrentDate char(8)

  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
    @CustomerNumber = CustomerNumber
  FROM Customer WITH (NOLOCK)
  WHERE HotelNumber = @HotelNumber
  AND CustomerTypeNumber = 7

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


  ---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
        ArrangementLineNumber
      FROM ArrangementReservationsXtab WITH (NOLOCK)
      WHERE 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  
        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 (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 (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
        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 (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 (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
        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 (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 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
        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



  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 (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 (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
        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

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  --The following code is used when dates has changed on a reservationline. We have to move the manual arrangementlines, so they wont get 'lost'

  IF (@formatedenddate <> @FormatedOldEnddate)
    OR (@formatedStartDate <> @FormatedOldStartDate)
  BEGIN
    /*   
       Declare MoveCur Cursor  Forward_Only Read_Only for
          select arrangementLineNumber,HallNumber,Date from arrangementlines WITH (nolock) where 
           not exists     (select * from arrangementroomxtab (nolock) where arrangementlinenumber = ArrangementLines.Arrangementlinenumber) and 
            arrangementlinenumber in (select arrangementlinenumber from arrangementreservationsxtab (nolock) where referencenumber = @Referencenumber)
    */
    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


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

  -- 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


  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  --
  -- 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'

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

  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 @Packet = NULL --AV 20030128
    IF @CurrentDate = @FormatedStartDate
    BEGIN
      IF @New_ProductNumber = -1  ---1 means that it is a NULL product
      BEGIN
        SELECT
          @Packet = KeyValue
        FROM hotelsetup WITH (NOLOCK)
        WHERE Searchkey = '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
          SELECT
            @Packet = MAX(ISNULL(arrivalPacketnumber, 0))
          FROM productPrice WITH (NOLOCK)
          WHERE ProductNumber = @New_ProductNumber
          AND datestamp = @CurrentDate --and RoomTypeNumber = @RoomTypeNumber
        END
        ELSE
          SELECT
            @Packet = KeyValue
          FROM hotelsetup WITH (NOLOCK)
          WHERE Searchkey = 'EmptyArrPacket'
      END
      ELSE
      IF @StarProduct = 1
        AND @WhichDay > 0
      BEGIN
        SELECT
          @Packet = MAX(ISNULL(arrivalPacketnumber, 0))
        FROM productPrice WITH (NOLOCK)
        WHERE ProductNumber = @New_ProductNumber
        AND datestamp = @CurrentDate --and RoomTypeNumber = @RoomTypeNumber
      END
      ELSE
      BEGIN
        SELECT
          @Packet = MAX(ISNULL(arrivalPacketnumber, 0))
        FROM productPrice WITH (NOLOCK)
        WHERE ProductNumber = @New_ProductNumber
        AND datestamp = @CurrentDate --and RoomTypeNumber = @RoomTypeNumber
      END
    END
    ELSE
    IF @CurrentDate = @FormatedEnddate
    BEGIN
      IF @New_ProductNumber = -1 ---1 means that it is a NULL product
      BEGIN
        SELECT
          @Packet = KeyValue
        FROM hotelsetup WITH (NOLOCK)
        WHERE Searchkey = '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
          SELECT
            @Packet = MAX(ISNULL(DeparturePacketnumber, 0))
          FROM productPrice WITH (NOLOCK)
          WHERE ProductNumber = @New_ProductNumber
          AND datestamp = @CurrentDate --and RoomTypeNumber = @RoomTypeNumber
        END
        ELSE
          SELECT
            @Packet = KeyValue
          FROM hotelsetup WITH (NOLOCK)
          WHERE Searchkey = 'EmptyDepPacket'
      END
      ELSE
      BEGIN
        SELECT
          @Packet = MAX(ISNULL(DeparturePacketnumber, 0))
        FROM productPrice WITH (NOLOCK)
        WHERE ProductNumber = @New_ProductNumber
        AND datestamp = @CurrentDate --and RoomTypeNumber = @RoomTypeNumber
      END
    END
    ELSE --Stay
    BEGIN
      IF @New_ProductNumber = -1 ---1 means that it is a NULL product
      BEGIN
        SELECT
          @Packet = KeyValue
        FROM hotelsetup WITH (NOLOCK)
        WHERE Searchkey = '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
          SELECT
            @Packet = MAX(ISNULL(StayPacketnumber, 0))
          FROM productPrice WITH (NOLOCK)
          WHERE ProductNumber = @New_productNumber
          AND datestamp = @CurrentDate --and RoomTypeNumber = @RoomTypeNumber
        END
        ELSE
          SELECT
            @Packet = KeyValue
          FROM hotelsetup WITH (NOLOCK)
          WHERE Searchkey = 'EmptyStayPacket'
      END
      ELSE
      BEGIN
        SELECT
          @Packet = MAX(ISNULL(StayPacketnumber, 0))
        FROM productPrice WITH (NOLOCK)
        WHERE ProductNumber = @New_ProductNumber
        AND datestamp = @CurrentDate --and RoomTypeNumber = @RoomTypeNumber
      END
    END


    -- 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)

    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
      DECLARE CopyCur CURSOR FOR
      SELECT DISTINCT
        PacketItem_New.PacketItemNumber,
        PacketNumber,
        PacketItem_New.WareNumber,
        Time,
        DepartureTime,
        TextOverWrite,
        PriceOverWrite,
        debtype,
        CONVERT(varchar(4000), note),
        ReservationDisCount,
        Ware.BookTimeInMinutes
      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 = @Packet
      AND PacketItem_New.Arrangement = 1
      AND (debtype = 1
      OR debtype = 2
      OR debtype = 3)
      AND (PacketItem_NEWRoomTypeXTab.RoomTypeNumber = @RoomTypeNumber
      OR ISNULL(PacketItem_NEWRoomTypeXTab.RoomTypeNumber, 0) = 0)
      --  AND ((@HotelNumber = -1) or (dbo.IsDepartmentInHotelList(PacketItem_New.DepartmentServiceOverWrite,Convert(Varchar(3),@HotelNumber),1) = 1))
      AND ((@HotelNumber = -1)
      OR (Departments.HotelNumber = @HotelNumber))
    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
      DECLARE CopyCur CURSOR FOR
      SELECT DISTINCT
        PacketItem_New.PacketItemNumber,
        PacketNumber,
        PacketItem_New.WareNumber,
        Time,
        DepartureTime,
        TextOverWrite,
        PriceOverWrite,
        debtype,
        CONVERT(varchar(4000), note),
        ReservationDisCount,
        Ware.BookTimeInMinutes
      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 = @Packet
      AND PacketItem_New.Arrangement = 1
      AND (debtype = 4
      OR debtype = 5
      OR debtype = 6)
      AND (PacketItem_NEWRoomTypeXTab.RoomTypeNumber = @RoomTypeNumber
      OR ISNULL(PacketItem_NEWRoomTypeXTab.RoomTypeNumber, 0) = 0)
      --    and ((@HotelNumber = -1) or (dbo.IsDepartmentInHotelList(PacketItem_New.DepartmentServiceOverWrite,Convert(Varchar(3),@HotelNumber),1) = 1))
      AND ((@HotelNumber = -1)
      OR (Departments.HotelNumber = @HotelNumber))
    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
      DECLARE CopyCur CURSOR FOR
      SELECT DISTINCT
        PacketItem_New.PacketItemNumber,
        PacketNumber,
        PacketItem_New.WareNumber,
        Time,
        DepartureTime,
        TextOverWrite,
        PriceOverWrite,
        debtype,
        CONVERT(varchar(4000), note),
        ReservationDisCount,
        Ware.BookTimeInMinutes
      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 = @Packet
      AND PacketItem_New.Arrangement = 1
      AND (debtype = 7
      OR debtype = 8
      OR debtype = 9)
      AND (PacketItem_NEWRoomTypeXTab.RoomTypeNumber = @RoomTypeNumber
      OR ISNULL(PacketItem_NEWRoomTypeXTab.RoomTypeNumber, 0) = 0)
      --   and ((@HotelNumber = -1) or (dbo.IsDepartmentInHotelList(PacketItem_New.DepartmentServiceOverWrite,Convert(Varchar(3),@HotelNumber),1) = 1))
      AND ((@HotelNumber = -1)
      OR (Departments.HotelNumber = @HotelNumber))
    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
      DECLARE CopyCur CURSOR FOR
      SELECT DISTINCT
        PacketItem_New.PacketItemNumber,
        PacketNumber,
        PacketItem_New.WareNumber,
        Time,
        DepartureTime,
        TextOverWrite,
        PriceOverWrite,
        debtype,
        CONVERT(varchar(4000), note),
        ReservationDisCount,
        Ware.BookTimeInMinutes
      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 = @Packet
      AND PacketItem_New.Arrangement = 1
      AND debtype <> 4
      AND debtype <> 5
      AND debtype <> 6
      AND (PacketItem_NEWRoomTypeXTab.RoomTypeNumber = @RoomTypeNumber
      OR ISNULL(PacketItem_NEWRoomTypeXTab.RoomTypeNumber, 0) = 0)
      --  and ((@HotelNumber = -1) or (dbo.IsDepartmentInHotelList(PacketItem_New.DepartmentServiceOverWrite,Convert(Varchar(3),@HotelNumber),1) = 1))
      AND ((@HotelNumber = -1)
      OR (Departments.HotelNumber = @HotelNumber))
    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
      DECLARE CopyCur CURSOR FOR
      SELECT DISTINCT
        PacketItem_New.PacketItemNumber,
        PacketNumber,
        PacketItem_New.WareNumber,
        Time,
        DepartureTime,
        TextOverWrite,
        PriceOverWrite,
        debtype,
        CONVERT(varchar(4000), note),
        ReservationDisCount,
        Ware.BookTimeInMinutes
      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 = @Packet
      AND PacketItem_New.Arrangement = 1
      AND debtype <> 7
      AND debtype <> 8
      AND debtype <> 9
      AND (PacketItem_NEWRoomTypeXTab.RoomTypeNumber = @RoomTypeNumber
      OR ISNULL(PacketItem_NEWRoomTypeXTab.RoomTypeNumber, 0) = 0)
      --    and ((@HotelNumber = -1) or (dbo.IsDepartmentInHotelList(PacketItem_New.DepartmentServiceOverWrite,Convert(Varchar(3),@HotelNumber),1) = 1))
      AND ((@HotelNumber = -1)
      OR (Departments.HotelNumber = @HotelNumber))
    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
      DECLARE CopyCur CURSOR FOR
      SELECT DISTINCT
        PacketItem_New.PacketItemNumber,
        PacketNumber,
        PacketItem_New.WareNumber,
        Time,
        DepartureTime,
        TextOverWrite,
        PriceOverWrite,
        debtype,
        CONVERT(varchar(4000), note),
        ReservationDisCount,
        Ware.BookTimeInMinutes
      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 = @Packet
      AND PacketItem_New.Arrangement = 1
      AND (PacketItem_NEWRoomTypeXTab.RoomTypeNumber = @RoomTypeNumber
      OR ISNULL(PacketItem_NEWRoomTypeXTab.RoomTypeNumber, 0) = 0)
      --    and ((@HotelNumber = -1) or (dbo.IsDepartmentInHotelList(PacketItem_New.DepartmentServiceOverWrite,Convert(Varchar(3),@HotelNumber),1) = 1))
      AND ((@HotelNumber = -1)
      OR (Departments.HotelNumber = @HotelNumber))
    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
      DECLARE CopyCur CURSOR FOR
      SELECT DISTINCT
        PacketItem_New.PacketItemNumber,
        PacketNumber,
        PacketItem_New.WareNumber,
        Time,
        DepartureTime,
        TextOverWrite,
        PriceOverWrite,
        debtype,
        CONVERT(varchar(4000), note),
        ReservationDisCount,
        Ware.BookTimeInMinutes
      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 = @Packet
      AND PacketItem_New.Arrangement = 1
      AND debtype <> 1
      AND debtype <> 2
      AND debtype <> 3
      AND (PacketItem_NEWRoomTypeXTab.RoomTypeNumber = @RoomTypeNumber
      OR ISNULL(PacketItem_NEWRoomTypeXTab.RoomTypeNumber, 0) = 0)
      --      and ((@HotelNumber = -1) or (dbo.IsDepartmentInHotelList(PacketItem_New.DepartmentServiceOverWrite,Convert(Varchar(3),@HotelNumber),1) = 1))
      AND ((@HotelNumber = -1)
      OR (Departments.HotelNumber = @HotelNumber))
    END

    /*
        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
    */


    OPEN CopyCur
    FETCH NEXT FROM CopyCur
    INTO @PacketItemNumber, @PacketNumber, @WareNumber, @Time, @DepTime, @TextOverWrite, @PriceOverWrite, @debtype, @note, @reservationDiscount, @BookTimeInMinutes

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

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF ISNULL(@PriceOverWrite, 0) = 0 -- is null 
        SELECT
          @PriceOverWrite = 0

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

      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 500
        SELECT @TempArrNumber = -1 --initialize

        SELECT
          @TempArrNumber = Arrangementlinenumber
        FROM arrangementlines WITH (NOLOCK)
        WHERE Arrangementlines.date = @CurrentDate
          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 WareNumber = @WareNumber
              AND Price = @PriceOverwrite
              AND (ISNULL(FK_Exdepartment, 0) = 0)
              AND (Time = @Time OR (ABS(DATEDIFF(MINUTE, CONVERT(datetime, @Time, 108), CONVERT(datetime, Time, 108))) <= ABS(DATEDIFF(MINUTE, @Tolerance, CONVERT(datetime, '00:00', 108)))))
              AND Date = @CurrentDate
              AND arrangementLines.ArrangementLineNumber IN (SELECT DISTINCT
                                                               ArrangementReservationsXTab.ArrangementLineNumber
                                                             FROM ArrangementReservationsXtab WITH (NOLOCK)
                                                             WHERE ArrangementReservationsXtab.Referencenumber = @Referencenumber)
            IF @TempArrNumber > -1
			BEGIN
            IF NOT EXISTS (SELECT GuestNumber 
                           FROM ArrangementReservationsXtab WITH (NOLOCK) 
                           WHERE Referencenumber = @ReferenceNumber 
                             AND GuestNumber = @GuestNumber 
                             AND ArrangementLineNumber = @TempArrNumber  
                             AND PlusProductLineNumber = @WhichDay) 
              INSERT INTO ArrangementReservationsXtab VALUES (@TempArrNumber, @Referencenumber, @GuestNumber, @WhichDay)          

            IF NOT EXISTS (SELECT ArrangementLineNumber 
                           FROM ArrangementRoomxtab WITH (NOLOCK) 
                           WHERE ArrangementlineNumber = @TempArrNumber
                             AND LineNumber = @LineNumber 
                             AND PlusProductLineNumber = @WhichDay
                             AND PacketItemNumber = @PacketItemNumber)  
              INSERT INTO ArrangementRoomxtab (ArrangementLineNumber, LineNumber, PlusProductLineNumber, PacketItemNumber) VALUES (@TempArrNumber, @LineNumber, @WhichDay, @PacketItemNumber)
			END
		  END
        END

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

        UPDATE ArrangementLines
        SET Pieces = Pieces + @Pieces
        WHERE ArrangementLineNumber = @TempArrNumber

        IF @@ROWCOUNT = 0
        BEGIN
          IF @Pieces > 0
          BEGIN
            SELECT
              @SuggestHallNumberOnPacketLine = dbo.GetHotelSetupKeyValue('SuggestHallNumberOnPacketLine', @CustomerNumber)

            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

            INSERT INTO ArrangementLines (Date, PacketItemNumber, Time, DepartureTime, Price, /*TotalPrice,*/ Pieces, WareText, WareNumber, note, hallnumber, FK_PacketChargeDepartment, PlaceText)
              VALUES (@CurrentDate, @PacketItemNumber, @Time, @DepartureTime, @PriceoverWrite, /*@TotalPrice,*/ @Pieces, @TextoverWrite, @WareNumber, @note, @hallnumber, @PacketChargeDepartment, @Place)
            SELECT
              @identityvalue = @@identity
            FROM ArrangementLines WITH (NOLOCK)
            INSERT INTO ArrangementReservationsXtab
              VALUES (@IdentityValue, @Referencenumber, @GuestNumber, @whichday)
            INSERT INTO ArrangementRoomxtab (arrangementlinenumber, linenumber, plusProductLineNumber, packetitemnumber)
              VALUES (@IdentityValue, @LineNumber, @Whichday, @PacketItemNumber)
            DECLARE DepCur CURSOR FORWARD_ONLY FOR
            SELECT DISTINCT
              FK_Department
            FROM PacketItem_NewDepartmentXTab WITH (NOLOCK)
            LEFT OUTER JOIN Departments WITH (NOLOCK)
              ON PacketItem_NewDepartmentXTab.FK_Department = Departments.PK_Department
            WHERE PacketItemNumber = @PacketItemNumber
            AND Type = 1
            AND (@HotelNumber = -1
            OR Departments.HotelNumber = @HotelNumber)
            OPEN DepCur
            FETCH NEXT FROM DepCur INTO @PK_Department
            WHILE (@@FETCH_STATUS <> -1)
            BEGIN
              IF NOT EXISTS (SELECT
                  FK_ArrangementLineNumber
                FROM ArrangementLineDepartmentXTab WITH (NOLOCK)
                WHERE FK_ArrangementLineNumber = @IdentityValue
                AND FK_DepartmentNumber = @PK_Department)
                INSERT INTO ArrangementLineDepartmentXTab (FK_ArrangementLineNumber, FK_DepartmentNumber)
                  VALUES (@IdentityValue, @PK_Department)
              FETCH NEXT FROM DepCur INTO @PK_Department
            END
            CLOSE DepCur
            DEALLOCATE DepCur
          END
        END -- 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
            IF NOT EXISTS (SELECT
                GuestNumber
              FROM arrangementReservationsXtab WITH (NOLOCK)
              WHERE referencenumber = @referenceNumber
              AND GuestNumber = @GuestNumber
              AND ArrangementLineNumber = @TempArrNumber
              AND PlusProductLineNumber = @whichday)
            BEGIN
              INSERT INTO ArrangementReservationsXtab
                VALUES (@TempArrNumber, @referenceNumber, @GuestNumber, @whichday)
            END
          END

          IF NOT EXISTS (SELECT
              arrangementLineNumber
            FROM arrangementRoomxtab WITH (NOLOCK)
            WHERE ArrangementlineNumber = @TempArrNumber
            AND lineNumber = @LineNumber
            AND PlusProductLineNumber = @Whichday
            AND packetItemNumber = @packetItemNumber)
          BEGIN
            -- find the arrangementlinenumber.. We already have the linenumber in the variable NewlineNumber. It is initiated in the update clause
            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 = @ArrNumber
              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
    END
    CLOSE copycur
    DEALLOCATE copycur
    SET @New_StartDate = @New_StartDate + 1
    SELECT
      @CurrentDate = CONVERT(char(10), @New_StartDate, 112)
  END