CREATE PROCEDURE [dbo].[ReCreateArrangement] @refno int, @AHotelNumber int = -1, @ACustomerNumber int = -1
AS
  SET NOCOUNT ON

  --sidst rettet 2017 0907 Cache package info for alle produkter i temp tabel.
  --sidst rettet 2016 0518 Genskab altid arrangementlinier for alle hoteller på en reservation
  --sidst rettet 2010 0315 DELETE fra ArrangementReservationsXTab og ArrangementRoomXTab flyttet.
  --sidst rettet 2007 0828 added new param: AHotelnumber
  --sidst retttet 2006 0313 HotelNumber bruges nu.
  --Sidst rettet 2005 0831 Brug af curser og kun sletning igennem nøgle felterne i arrangementtabeller: For at være sikker på at undgå tablescans / locktimout
  --sidste rettet 01022005 parametre til child2
  --sidste rettet 110603 delete from arrangementroomxtab.... og hvis en hall linie slutter før døgnet startet, så træk 1 fra departuredate..
  --sidst rettet 220503 Rutinen virker 100 % også med plusprodukt

  --If routine is called with -10  for parameter @refno, all reservations in system with statuses < Inhouse will be affected. Else only the given referencenumber will be affected

  DECLARE @ProductNumber int
  DECLARE @PacketNumber int
  DECLARE @WhichDay int
  DECLARE @ArrivalDate char(8)
  DECLARE @DepartureDate char(8)
  DECLARE @ArrDate datetime
  DECLARE @Depdate datetime
  DECLARE @ArrTime char(5)
  DECLARE @DepTime char(5)
  DECLARE @Adults int
  DECLARE @Children1 int
  DECLARE @Children2 int
  DECLARE @NumberOfRooms int
  DECLARE @SchemePattern int
  DECLARE @LinkLineNumber int

  DECLARE @RoomTypeNumber int
  DECLARE @RoomNumber int
  DECLARE @GuestNumber int

  DECLARE @LineNumber int
  DECLARE @NameListCreated bit

  DECLARE @FromProduct bit

  DECLARE @Day2 int
  DECLARE @Day3 int
  DECLARE @Day4 int

  DECLARE @ProductNumber2 int
  DECLARE @ProductNumber3 int
  DECLARE @ProductNumber4 int

  DECLARE @Loopcounter int
  DECLARE @DoContinue bit

  DECLARE @ArrLineNumber int
  DECLARE @ArrRoomLineNumber int
  DECLARE @ArrPlusProductLineNumber int
  DECLARE @ArrPacketItemNumber int
  DECLARE @ArrReferenceNumber int
  DECLARE @ArrGuestNumber int
  DECLARE @Arr24HoursStarts char(5)
  DECLARE @HotelNumber int
  DECLARE @DominoEnabled bit

  -- Print 'Start: ' + Convert(varchar(30), GETDATE(), 113)

  CREATE TABLE #PackageInfo
  (
    View_DateStamp DATETIME,
    View_Id INT,
    View_FK_ProductNumber INT,
    View_PacketType INT
  )

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

  DECLARE @MinArrivalDate VARCHAR(8)
  DECLARE @MaxDepartureDate VARCHAR(8)
  DECLARE @TempStartDate date
  DECLARE @TempEndDate date
  SELECT @MinArrivalDate = MIN(ArrivalDate), @MaxDepartureDate = MAX(DepartureDate) FROM RESERVATIONS (NOLOCK) WHERE ReferenceNumber = @refno

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

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

  SELECT  @Arr24HoursStarts = KeyValue FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'Arr24HoursStarts'

  SELECT @DominoEnabled = CONVERT(smallint, KeyValue)
  FROM HotelSetup WITH (NOLOCK)
  WHERE SearchKey = 'CentralBookingType'
  IF ISNULL(@DominoEnabled, 0) = 0
    SET @DominoEnabled = 0


  DECLARE @Counter int
  SET @Counter = 0

  IF @Refno = -10
  BEGIN
    IF @AHotelNumber > -1
      AND @DominoEnabled = 1
    BEGIN
      DECLARE ResCur CURSOR FORWARD_ONLY FOR
      SELECT DISTINCT reservations.referencenumber
      FROM reservations WITH (NOLOCK)
      INNER JOIN RESERVATIONMAIN WITH (NOLOCK) ON reservationmain.referencenumber = reservations.referencenumber AND reservationmain.hotelnumber = @AHotelNumber
      WHERE reservations.guestnumber = 0
      AND reservations.Status < 5
      AND reservations.ReferenceNumber > 0
      
    END
    ELSE
    BEGIN
      DECLARE ResCur CURSOR FORWARD_ONLY FOR
      SELECT DISTINCT referencenumber
      FROM reservations WITH (NOLOCK)
      WHERE guestnumber = 0
      AND Status < 5
      AND ReferenceNumber > 0
    END
  END
  ELSE
  BEGIN
    DECLARE ResCur CURSOR FORWARD_ONLY FOR
    SELECT DISTINCT referencenumber
    FROM reservations WITH (NOLOCK)
    WHERE guestnumber = 0
    AND referencenumber = @refno
  END

  OPEN ResCur
  FETCH NEXT FROM ResCur INTO @Refno
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    SET @HotelNumber = -1
	/* 20160518/RN
    IF @DominoEnabled = 1
      SELECT
        @HotelNumber = HotelNumber
      FROM ReservationMain WITH (NOLOCK)
      WHERE ReferenceNumber = @RefNo
    ELSE
      SET @HotelNumber = -1
	*/
    -- PRINT 'nu kommer ref no: ' + CONVERT(char, @refno)
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	SELECT DISTINCT al.ArrangementLineNumber INTO #TEMP
    FROM Arrangementlines al WITH (NOLOCK)
	INNER jOIN ArrangementreservationsXtab arx WITH (NOLOCK) ON al.ArrangementLineNumber = arx.ArrangementLineNumber AND arx.ReferenceNumber = @refno
	  AND ISNULL(al.PacketItemNumber, 0) <> 0
    -- 20180530/RN: Do not join ArrangementRoomXtab, we only need to check if PacketItemNumber isn't null
	-- inner join ArrangementRoomXtab arox WITH (NOLOCK) ON al.ArrangementLineNumber = arox.ArrangementLineNumber

	DELETE FROM ArrangementReservationsXTab
    WHERE ArrangementLineNumber IN (select ArrangementLineNumber FROM #TEMP WITH (NOLOCK))
	DELETE FROM ArrangementRoomXTab
	WHERE ArrangementLineNumber IN (select ArrangementLineNumber FROM #TEMP WITH (NOLOCK))
	DELETE FROM ArrangementLineDepartmentXTab
	WHERE FK_ArrangementLineNumber IN (select ArrangementLineNumber FROM #TEMP WITH (NOLOCK))
	DELETE FROM ArrangementLines
	WHERE ArrangementLineNumber IN (select ArrangementLineNumber FROM #TEMP WITH (NOLOCK))

	DROP TABLE #TEMP

    SET @counter = @counter + 1

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

    DECLARE RoomLineCur CURSOR FORWARD_ONLY FOR
    SELECT DISTINCT Linenumber
    FROM ReservationRoomLineXtab WITH (NOLOCK)
    WHERE referencenumber = @refno
    OPEN RoomLineCur
    FETCH NEXT FROM RoomLineCur INTO @linenumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
	  DELETE FROM ArrangementRoomXtab
      WHERE linenumber = @LineNumber

      IF EXISTS (SELECT *
        FROM reservationroomlinextab WITH (NOLOCK)
        WHERE linenumber = @LineNumber
        AND guestnumber = 0)
        SET @NameListCreated = 0
      ELSE
        SET @NameListCreated = 1
      SELECT
        @NumberOfRooms = Numberofrooms,
        @roomNumber = reservationroomline.RoomNumber,
        @RoomTypeNumber = ISNULL(Rooms.RoomTypeNumber, BookedTypeNumber)
      FROM reservationroomline WITH (NOLOCK)
	  LEFT OUTER JOIN rooms WITH (NOLOCK) ON reservationroomline.RoomNumber = rooms.RoomNumber
      WHERE linenumber = @LineNumber

      IF ISNULL(@RoomTypeNumber, 0) = 0 -- is null
        PRINT 'Houston we got a problem: RoomTypeNumber not found'

      IF @NameListCreated = 1
      BEGIN
        SET @NumberOfRooms = 1
	
        DECLARE RoomLineCur1 CURSOR FORWARD_ONLY FOR
        SELECT DISTINCT
          r.GuestNumber,
          ProductNumber,
          Productnumber2,
          productnumber3,
          productnumber4,
          day2,
          day3,
          day4,
          arrivaldate,
		  arrivaltime,
          departuredate,
		  DepartureTime,
          Adults,
          Children1,
          Children2
       FROM reservations r WITH (NOLOCK) 
		INNER JOIN reservationroomlinextab rlx WITH (NOLOCK) ON r.ReferenceNumber = rlx.ReferenceNumber and r.GuestNumber = rlx.GuestNumber
		and rlx.ReferenceNumber = @refno and rlx.LINENUMBER = @linenumber
        WHERE  status < 7

        OPEN RoomLineCur1
        FETCH NEXT FROM RoomLineCur1 INTO @GuestNumber, @ProductNumber, @productnumber2, @productnumber3, @productnumber4, @day2, @day3, @day4, @arrivaldate, @ArrTime, @departuredate, @DepTime, @Adults, @Children1, @Children2
        WHILE (@@FETCH_STATUS <> -1)
        BEGIN
			
          -- PRINT ' Guest: ' + CONVERT(char, @GuestNumber)
          SET @LoopCounter = 0
          SET @DoContinue = 1
          WHILE @LoopCounter <= 4
            AND @DoContinue = 1
          BEGIN
            SET @Loopcounter = @LoopCounter + 1

            IF @LoopCounter = 1
            BEGIN
              IF ISNULL(@day2, 0) = 0 -- is null --happens if no plusproduct
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate)
                SET @depdate = CONVERT(datetime, @departuredate)
                SET @DoContinue = 0
              END
              ELSE
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate)
                SET @depdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
              END
            END
            ELSE
            IF @LoopCounter = 2
            BEGIN
              SET @productnumber = @productnumber2
              IF ISNULL(@day3, 0) = 0 -- is null
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
                SET @depdate = CONVERT(datetime, @departuredate)
                SET @DoContinue = 0
              END
              ELSE
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
                SET @depdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
              END
            END
            ELSE
            IF @LoopCounter = 3
            BEGIN
              SET @productnumber = @productnumber3
              IF ISNULL(@day4, 0) = 0 -- is null
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
                SET @depdate = CONVERT(datetime, @departuredate)
                SET @DoContinue = 0
              END
              ELSE
              BEGIN
                SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
                SET @depdate = CONVERT(datetime, @Arrivaldate) + @day4 - 1
              END
            END
            ELSE
            IF @LoopCounter = 4
            BEGIN
              SET @productnumber = @productnumber4
              SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day4 - 1
              SET @depdate = CONVERT(datetime, @Departuredate)
              SET @DoContinue = 0
            END

            IF ISNULL(@Adults, 0) = 0 -- is null
              SET @Adults = 0
            IF ISNULL(@Children1, 0) = 0 -- is null
              SET @Children1 = 0
            IF ISNULL(@Children2, 0) = 0 -- is null
              SET @Children2 = 0
            IF ISNULL(@ProductNumber, 0) = 0 -- is null
              SET @productNumber = 0

            SET @ArrDate = CONVERT(datetime, @ArrDate + ' ' + @ArrTime, 112)
			SET @DepDate = CONVERT(datetime, @DepDate + ' ' + @DepTime, 112)

            EXEC GenerateArrangementFromNameList -3,
                                                 @ProductNumber,
                                                 @Loopcounter,
                                                 @arrDate,
                                                 @ArrDate,
                                                 @DepDate,
                                                 @Depdate,
                                                 @depdate,
                                                 @Depdate,
                                                 @Refno,
                                                 @GuestNumber,
                                                 @adults,
                                                 @adults,
                                                 @Children1,
                                                 @Children1,
                                                 @Children2,
                                                 @Children2,
                                                 @NumberOfRooms,
                                                 @NumberOfRooms,
                                                 0,
                                                 0,
                                                 1,
                                                 @Linenumber,
                                                 @RoomTypeNumber,
                                                 0,
                                                 @HotelNumber,
												 1,
												 @ACustomerNumber

 
          END
          FETCH NEXT FROM RoomLineCur1 INTO @GuestNumber, @ProductNumber, @productnumber2, @productnumber3, @productnumber4, @day2, @day3, @day4, @arrivaldate, @ArrTime, @departuredate, @DepTime, @Adults, @Children1, @Children2
        END
        CLOSE RoomLineCur1
        DEALLOCATE RoomLineCur1

      END
      ELSE
      BEGIN

        SELECT DISTINCT
          @ProductNumber = ProductNumber,
          @ProductNumber2 = ProductNumber2,
          @ProductNumber3 = ProductNumber3,
          @ProductNumber4 = ProductNumber4,
          @day2 = day2,
          @day3 = day3,
          @day4 = day4,
          @arrivaldate = arrivaldate,
		  @ArrTime = ArrivalTime,
          @departuredate = departuredate,
		  @DepTime = DepartureTime,
          @Adults = Adults,
          @Children1 = Children1,
          @Children2 = Children2
        FROM reservationroomline WITH (NOLOCK)
        WHERE lineNumber = @Linenumber

        SET @LoopCounter = 0
        SET @DoContinue = 1

        WHILE @LoopCounter <= 4
          AND @DoContinue = 1
        BEGIN
          SET @Loopcounter = @LoopCounter + 1
          IF @LoopCounter = 1
          BEGIN
            IF ISNULL(@day2, 0) = 0 -- is null --happens if no plusproduct
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate)
              SET @depdate = CONVERT(datetime, @departuredate)
              SET @DoContinue = 0
            END
            ELSE
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate)
              SET @depdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
            END
          END
          ELSE
          IF @LoopCounter = 2
          BEGIN
            SET @productnumber = @productnumber2
            IF ISNULL(@day3, 0) = 0 -- is null
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
              SET @depdate = CONVERT(datetime, @departuredate)
              SET @DoContinue = 0
            END
            ELSE
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day2 - 1
              SET @depdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
            END
          END
          ELSE
          IF @LoopCounter = 3
          BEGIN
            SET @productnumber = @productnumber3
            IF ISNULL(@day4, 0) = 0 -- is null
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
              SET @depdate = CONVERT(datetime, @departuredate)
              SET @DoContinue = 0
            END

            ELSE
            BEGIN
              SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day3 - 1
              SET @depdate = CONVERT(datetime, @Arrivaldate) + @day4 - 1
            END
          END
          ELSE
          IF @LoopCounter = 4
          BEGIN
            SET @productnumber = @productnumber4
            SET @arrdate = CONVERT(datetime, @Arrivaldate) + @day4 - 1
            SET @depdate = CONVERT(datetime, @Departuredate)
            SET @DoContinue = 0
          END

          SET @GuestNumber = 0

          IF ISNULL(@Adults, 0) = 0 -- is null
            SET @Adults = 0
          IF ISNULL(@Children1, 0) = 0 -- is null
            SET @Children1 = 0
          IF ISNULL(@Children2, 0) = 0 -- is null
            SET @Children2 = 0
          IF ISNULL(@ProductNumber, 0) = 0 -- is null
            SET @productNumber = 0

		  SET @ArrDate = CONVERT(datetime, @ArrDate + ' ' + @ArrTime, 112)
		  SET @DepDate = CONVERT(datetime, @DepDate + ' ' + @DepTime, 112)

          EXEC GenerateArrangementFromNameList -3,
                                               @ProductNumber,
                                               @LoopCounter,
                                               @arrDate,
                                               @ArrDate,
                                               @DepDate,
                                               @Depdate,
                                               @depdate,
                                               @Depdate,
                                               @Refno,
                                               @GuestNumber,
                                               @adults,
                                               @adults,
                                               @Children1,
                                               @Children1,
                                               @Children2,
                                               @Children2,
                                               @NumberOfRooms,
                                               @NumberOfRooms,
                                               0,
                                               0,
                                               0,
                                               @Linenumber,
                                               @RoomTypeNumber,
                                               0,
                                               @HotelNumber, 
											   1,
											   @ACustomerNumber
        END


      END

      FETCH NEXT FROM RoomLineCur INTO @LineNumber
    END

    CLOSE roomlinecur
    DEALLOCATE roomlinecur

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

    DECLARE ConflineCur CURSOR FORWARD_ONLY FOR
    SELECT DISTINCT ReservationConfLinenumber
    FROM ReservationConferenceline WITH (NOLOCK)
    WHERE referencenumber = @refno
    OPEN ConflineCur
    FETCH NEXT FROM ConflineCur INTO @linenumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
	  DELETE FROM ArrangementRoomXtab
      WHERE linenumber = @LineNumber

      SELECT DISTINCT
        @ProductNumber = ProductNumber,
        @PacketNumber = PackageNumber,
        @arrivaldate = arrivaldate,
        @arrtime = arrivaltime,
        @departuredate = departuredate,
        @deptime = departuretime,
        @Adults = Adults,
        @Children1 = Children1,
        @Children2 = Children2,
        @NumberOfRooms = NumberOfRooms,
		@SchemePattern = SchemePattern,
		@LinkLineNumber = LinkLineNumber,
		@GuestNumber = GuestNo
      FROM reservationConferenceline WITH (NOLOCK)
      WHERE ReservationConflineNumber = @Linenumber

      IF ISNULL(@ProductNumber, 0) = 0 -- is null
        SET @productNumber = 0

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

      IF @ProductNumber = 0
        SET @FromProduct = 0
      ELSE
	  BEGIN
	    IF @PacketNumber > 0
		  SET @FromProduct = 0
		ELSE
          SET @FromProduct = 1
      END

      IF ISNULL(@LinkLineNumber, 0) > 0
	  BEGIN
	    SELECT @SchemePattern = SchemePattern FROM RESERVATIONCONFERENCELINE WITH (NOLOCK) WHERE ReservationConfLineNumber = @LinkLineNumber
	  END

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

      IF ISNULL(@NumberOfRooms, 0) = 0 -- is null
        SET @NumberOfRooms = 0
      IF ISNULL(@Adults, 0) = 0 -- is null
        SET @Adults = 0
      IF ISNULL(@Children1, 0) = 0 -- is null
        SET @Children1 = 0
      IF ISNULL(@Children2, 0) = 0 -- is null
        SET @Children2 = 0

      IF @Arrtime < @Arr24HoursStarts
        SET @arrdate = CONVERT(datetime, @Arrivaldate) - 1
      ELSE
        SET @arrdate = CONVERT(datetime, @Arrivaldate)

      IF @Deptime < @Arr24HoursStarts
        SET @depdate = CONVERT(datetime, @DepartureDate) - 1
      ELSE
        SET @depdate = CONVERT(datetime, @DepartureDate)

      SET @ArrDate = CONVERT(datetime,@ArrDate + ' ' + @ArrTime, 112)
	  SET @Depdate = CONVERT(datetime, @Depdate + ' ' + @DepTime, 112)
	
      EXEC GenerateArrangement -3,
                               @ProductNumber,
                               -3,
                               @PacketNumber,
                               @ArrDate,
                               @Arrdate,
                               @DepDate,
                               @DepDate,
                               @DepDate,
                               @Depdate,
                               @Refno,
                               @GuestNumber,
                               @Adults,
                               @Adults,
                               @Children1,
                               @Children1,
                               @Children2,
                               @Children2,
                               0,
                               @FromProduct,
                               @LineNumber,
                               @NumberofRooms,
                               @NumberofRooms,
                               0,
                               @HotelNumber,
							   1,
							   @ACustomerNumber

      FETCH NEXT FROM ConflineCur INTO @linenumber
    END
    CLOSE ConfLineCur
    DEALLOCATE ConfLineCur

    FETCH NEXT FROM ResCur INTO @Refno
  END

  CLOSE ResCur
  DEALLOCATE Rescur

  DROP TABLE #PackageInfo
  DROP TABLE #TempPacketItems


  -- PRINT 'Numbers of Affected referencenumbers is: '
  -- PRINT @counter
  -- Print 'End: ' + Convert(varchar(30), GETDATE(), 113)