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

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

  BEGIN TRY

  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
  DECLARE @PlusProductSamePeriod 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,
		UseToleranceTime int,
		ToleranceTimeInMinutes int,
		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
	SELECT @HotelNumber = HotelNumber FROM RESERVATIONMAIN WITH (NOLOCK) WHERE REFERENCENUMBER = @refno

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

	DECLARE @TmpArrLineNbr int
	DECLARE @WareHasTable bit
	DECLARE TmpArrCursor CURSOR FORWARD_ONLY FOR
	  SELECT ArrangementLineNumber FROM #TEMP WITH (NOLOCK) 
    OPEN TmpArrCursor
	FETCH NEXT FROM TmpArrCursor INTO @TmpArrLineNbr
	WHILE (@@FETCH_STATUS <> -1) 
	BEGIN
	  SELECT @WareHasTable = WareHasCreateTable FROM ArrangementLines WITH (NOLOCK) WHERE ArrangementLineNumber = @TmpArrLineNbr
	  IF @WareHasTable = 1
	    DELETE FROM ArrangementReservationConferenceLineXtab WHERE ArrangementLineNumber = @TmpArrLineNbr
 	  FETCH NEXT FROM TmpArrCursor INTO @TmpArrLineNbr
	END

	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
--	  PRINT '*** New roomline ***'
--	  PRINT 'ReCreate 1 @Linenumber: ' + CAST(@Linenumber AS VARCHAR(10))
	  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,
		  PlusProductSamePeriod
       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, @PlusProductSamePeriod
        WHILE (@@FETCH_STATUS <> -1)
        BEGIN
		  IF @PlusProductSamePeriod = 1
		  BEGIN
-- 	        PRINT 'ReCreate 2 IsPlusProductSamePeriod @Linenumber: ' + CAST(@Linenumber AS VARCHAR(10))

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

			EXEC GenerateArrangementFromNameList -3,
                                                 @ProductNumber,
                                                 1,
                                                 @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
		  ELSE
		  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)

--	            PRINT 'ReCreate 3 @Linenumber: ' + CAST(@Linenumber AS VARCHAR(10))

				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
		  END
          FETCH NEXT FROM RoomLineCur1 INTO @GuestNumber, @ProductNumber, @productnumber2, @productnumber3, @productnumber4, @day2, @day3, @day4, @arrivaldate, @ArrTime, @departuredate, @DepTime, @Adults, @Children1, @Children2, @PlusProductSamePeriod
        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,
		  @PlusProductSamePeriod = PlusProductSamePeriod
        FROM reservationroomline WITH (NOLOCK)
        WHERE lineNumber = @Linenumber
		SET @GuestNumber = 0
		IF @PlusProductSamePeriod = 1
		BEGIN
  		  SET @ArrDate = CONVERT(datetime, @ArrivalDate + ' ' + @ArrTime, 112)
		  SET @DepDate = CONVERT(datetime, @DepartureDate + ' ' + @DepTime, 112)

--	      PRINT 'ReCreate 4 IsPlusProductSamePeriod @Linenumber: ' + CAST(@Linenumber AS VARCHAR(10))

			EXEC GenerateArrangementFromNameList -3,
                                               @ProductNumber,
                                               1,
                                               @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
		ELSE
		BEGIN
			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)

 --	          PRINT 'ReCreate 5 @Linenumber: ' + CAST(@Linenumber AS VARCHAR(10))

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

    UPDATE ArrangementLines
    SET ArrangementLines.ArrangementLineUniqueID = ((ArrangementLines.ArrangementLineNumber & 0x7ffff /*19 bits*/) * 4096 /* << 12 */) | (ArrangementLines.ArrangementLineNumber / 524288  /* >> 19 */)
    WHERE (1=1)
      AND ArrangementLines.ArrangementLineNumber IN (
      SELECT ArrangementLineNumber FROM ArrangementReservationsXtab WITH (NOLOCK)
	  WHERE ArrangementReservationsXtab.ReferenceNumber = @refno)
	    AND ISNULL(ArrangementLines.PacketItemNumber, 0) <> 0

    DECLARE @Arrline INT
    DECLARE @ArrUniqueID INT
    DECLARE TmpCursor CURSOR FOR
      SELECT ArrangementLines.ArrangementlineNumber, ArrangementLines.ArrangementLineUniqueID
      FROM ArrangementLines (NOLOCK)
      INNER JOIN ArrangementReservationsXtab (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber AND (ISNULL(Arrangementlines.PacketItemNumber, 0) <> 0)
      WHERE ArrangementReservationsXtab.ReferenceNumber = @refno
    OPEN TmpCursor
    FETCH NEXT FROM TmpCursor INTO @ArrLine, @ArrUniqueID
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      UPDATE ArrangementLineDepartmentXTab
      SET FK_ArrangementlineUniqueID = @ArrUniqueID
      WHERE FK_ArrangementLineNumber = @Arrline
    FETCH NEXT FROM TmpCursor INTO @ArrLine, @ArrUniqueID
  END
  CLOSE TmpCursor
  DEALLOCATE TmpCursor

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

  END TRY
  BEGIN CATCH
    CLOSE ResCur
    DEALLOCATE Rescur

	CLOSE RoomLineCur
	DEALLOCATE RoomLineCur

	CLOSE ConfLineCur
	DEALLOCATE ConfLineCur

    DECLARE @ErrorMessage NVARCHAR(MAX)
    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT

    SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
  END CATCH

  --DECLARE @SqlStr VARCHAR(50)
  --SET @SqlStr = 'SET LOCK_TIMEOUT '+ CONVERT(VARCHAR, @LockTimeOut)
  --EXEC (@SqlStr)
