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

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

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

  DECLARE @PersonRessourceBookingEnabled bit
  DECLARE @ChargePacketDepartmentEnabled bit

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

  DECLARE @TotalPrice real
  DECLARE @WeReadFromPacket int

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

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

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

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

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

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

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

  SET @FromNameList = 0

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

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

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

  SELECT
    @PersonRessourceBookingEnabled = 0

  SELECT
    @PersonRessourceBookingEnabled = CONVERT(bit, KeyValue)
  FROM HotelSetUp WITH (NOLOCK)
  WHERE SearchKey LIKE 'useDepTimeInArrangement'
  SELECT
    @ChargePacketDepartmentEnabled = CONVERT(integer, ISNULL(KeyValue, 0))
  FROM HotelSetup WITH (NOLOCK)
  WHERE searchkey = 'ChargePacketDepartmentEnabled'

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

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

    OPEN DeleteCur
    FETCH NEXT FROM DeleteCur INTO @ArrNumber, @confID, @PacketItemNumber, @PlusProductLineNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF NOT EXISTS (SELECT
          ArrangementLineNumber
        FROM ArrangementRoomXtab WITH (NOLOCK)
        WHERE ArrangementLineNumber = @ArrNumber
        AND LineNumber <> @LineNumber)
      BEGIN
        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
      BEGIN
        SELECT
          @DebType = PacketItem_New.DebType
        FROM ArrangementLines WITH (NOLOCK)
        INNER JOIN ArrangementRoomXtab WITH (NOLOCK)
          ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
        INNER JOIN PacketItem_NEW WITH (NOLOCK)
          ON PacketItem_NEW.PacketItemNumber = ArrangementLines.PacketItemNumber
        WHERE ArrangementRoomXtab.ArrangementLineNumber = @ArrNumber
        AND ArrangementRoomXtab.Linenumber = @Linenumber

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

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

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

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

    CLOSE DeleteCur
    DEALLOCATE DeleteCur

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

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

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

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

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

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

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

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

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

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

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

    OPEN DeleteCursor
    FETCH NEXT FROM DeleteCursor INTO @ArrNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF NOT EXISTS (SELECT
          ArrangementLineNumber
        FROM ArrangementRoomXtab WITH (NOLOCK)
        WHERE ArrangementLineNumber = @ArrNumber
        AND Linenumber <> @Linenumber)
      BEGIN
        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 = PacketItem_New.DebType
        FROM ArrangementLines WITH (NOLOCK)
        INNER JOIN ArrangementRoomXtab WITH (NOLOCK)
          ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
        INNER JOIN PacketItem_NEW WITH (NOLOCK)
          ON PacketItem_NEW.PacketItemNumber = ArrangementLines.PacketItemNumber
        WHERE ArrangementRoomXtab.ArrangementLineNumber = @ArrNumber
        AND ArrangementRoomXtab.Linenumber = @Linenumber

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

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

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

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

    OPEN DeleteCur
    FETCH NEXT FROM DeleteCur INTO @ArrNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF NOT EXISTS (SELECT
          ArrangementLineNumber
        FROM ArrangementRoomXtab WITH (NOLOCK)
        WHERE ArrangementLineNumber = @ArrNumber
        AND Linenumber <> @Linenumber)
      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 = PacketItem_New.DebType
        FROM ArrangementLines WITH (NOLOCK)
        INNER JOIN ArrangementRoomXtab WITH (NOLOCK)
          ON ArrangementRoomXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
        INNER JOIN PacketItem_NEW WITH (NOLOCK)
          ON PacketItem_NEW.PacketItemNumber = ArrangementLines.PacketItemNumber
        WHERE ArrangementRoomXtab.ArrangementLineNumber = @ArrNumber
        AND ArrangementRoomXtab.Linenumber = @Linenumber

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

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

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

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

  IF (@FormatedEndDate <> @FormatedOldEnddate)
    OR (@FormatedStartDate <> @FormatedOldStartDate)
  BEGIN
    DECLARE MoveCur CURSOR FORWARD_ONLY READ_ONLY FOR
    SELECT DISTINCT
      Arrangementlines.ArrangementLineNumber,
      HallNumber,
      Date
    FROM ArrangementLines WITH (NOLOCK)
    INNER JOIN ArrangementReservationsXtab WITH (NOLOCK)
      ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
      AND ReferenceNumber = @Referencenumber AND GuestNumber = @GuestNumber
    LEFT OUTER JOIN ArrangementRoomXtab WITH (NOLOCK)
      ON Arrangementlines.ArrangementLineNumber = ArrangementRoomXtab.ArrangementLineNumber
    WHERE ISNULL(ArrangementRoomXtab.ArrangementLineNumber, 0) = 0

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

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

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

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

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

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

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

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

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

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


  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  --
  -- 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 <= @New_EndDate
  BEGIN
    SET @WeReadFromPacket = 1
    -- FOR EACH DAY GO GET THE PACKET FOR THE PRODUCT
    IF @FromProduct = 1
      SELECT
        @ArrivalPacket = ArrivalPacketnumber,
        @AdultDayProductPrice = AdultPrice1,
        @ChildDayProductPrice = Children1Price1
      FROM ProductPrice WITH (NOLOCK)
      WHERE ProductNumber = @New_ProductNumber
      AND DateStamp = @CurrentDate
      AND Roomtypenumber = 0
    ELSE
      SELECT
        @ArrivalPacket = @New_PacketNumber

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

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

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

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

    SET @IsNewArrangement = 1

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

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

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

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

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

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

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

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

        SELECT
          @TempToleranceArrNumber = ArrangementLinenumber,
          @FoundPrice = Price,
          @FoundPieces = Pieces
        FROM ArrangementLines WITH (NOLOCK)
        WHERE 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 @TempToleranceArrNumber > -1
          IF @found = 0 --AV 20040714 if different debtype then split arrangementlines
            IF EXISTS (SELECT
                Searchkey
              FROM HotelSetup WITH (NOLOCK)
              WHERE searchkey = 'SplitArrLinesDifferentDebType'
              AND KeyValue = 1)
              IF EXISTS (SELECT
                  DebType
                FROM PacketItem_New WITH (NOLOCK)
                WHERE PacketItemNumber IN (SELECT
                  PacketItemNumber
                FROM ArrangementRoomXtab WITH (NOLOCK)
                WHERE ArrangementLineNumber = @TempToleranceArrNumber)
                AND DebType <> @DebType)
                SET @TempToleranceArrNumber = -1

        IF @TempToleranceArrNumber > -1
        BEGIN
          UPDATE ArrangementLines
          SET Pieces = Pieces + @Pieces
          WHERE ArrangementLines.ArrangementLineNumber = @TempToleranceArrNumber
        END
      END

      IF @TempToleranceArrNumber = -1
      BEGIN
        UPDATE ArrangementLines
        SET Pieces = Pieces + @Pieces
        WHERE ArrangementLines.ArrangementLineNumber = @TempArrNumber
      END

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

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

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

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

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

      WHILE (@@FETCH_STATUS <> -1)
      BEGIN
        EXEC dbo.GetArrangementDebTypePieces @IsNewArrangement,
		                                     @DebType,
                                             @Found,
                                             @Old_ProductNumber,
                                             @New_ProductNumber,
                                             @CurrentDate,
                                             @FormatedOldEndDate,
                                             @FormatedOldStartDate,
                                             @FormatedStartDate,
                                             @FormatedEndDate,
                                             @Old_NumberOfPersons,
                                             @New_NumberOfpersons,
                                             @Old_NumberOfChildren,
                                             @New_NumberOfChildren,
                                             @Old_NumberOfChildren2,
                                             @New_NumberOfChildren2,
                                             @Old_Pieces,
                                             @New_Pieces,
                                             @FromNameList,
                                             @USETOLERANCE,
                                             @Pieces OUTPUT,
                                             @PriceOverwrite OUTPUT,
                                             @TotalPrice OUTPUT

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

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

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

        SET LOCK_TIMEOUT 500

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

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

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

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

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

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

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

            SELECT
              @TempToleranceArrNumber = ArrangementLinenumber,
              @FoundPrice = Price,
              @FoundPieces = Pieces
            FROM ArrangementLines WITH (NOLOCK)
            WHERE WareNumber = @WareNumber
            AND 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 @TempToleranceArrNumber > -1
              IF @found = 0 --AV 20040714 if different debtype then split arrangementlines
                IF EXISTS (SELECT
                    Searchkey
                  FROM HotelSetup WITH (NOLOCK)
                  WHERE searchkey = 'SplitArrLinesDifferentDebType'
                  AND KeyValue = 1)
                  IF EXISTS (SELECT
                      DebType
                    FROM PacketItem_New WITH (NOLOCK)
                    WHERE PacketItemNumber IN (SELECT
                      PacketItemNumber
                    FROM ArrangementRoomXtab WITH (NOLOCK)
                    WHERE ArrangementLineNumber = @TempToleranceArrNumber)
                    AND DebType <> @DebType)
                    SET @TempToleranceArrNumber = -1

            IF @TempToleranceArrNumber > -1
            BEGIN
              UPDATE ArrangementLines
              SET Pieces = Pieces + @Pieces
              WHERE ArrangementLines.ArrangementLineNumber = @TempToleranceArrNumber

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

          IF @TempToleranceArrNumber = -1
          BEGIN
            INSERT INTO ArrangementLines (Date, PacketItemNumber, Time, DepartureTime, Price, Pieces, WareText, WareNumber, HallNumber, Note, FK_PacketChargeDepartment)
              VALUES (@CurrentDate, @PacketItemNumber, @Time, @DepartureTime, @PriceoverWrite, @Pieces, @TextoverWrite, @WareNumber, @HallNumber, @Note, @PacketChargeDepartment)

            SELECT
              @IdentityValue = @@identity
            FROM ArrangementLines WITH (NOLOCK)

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

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