CREATE PROCEDURE [dbo].[GenerateArrangementFromNameList] @Old_ProductNumber int, @New_ProductNumber int, @WhichDay int, @Old_StartDate DateTime, @New_StartDate datetime, @Old_EndDate DateTime, @New_Enddate datetime,@MainResArrival DateTime,
@MainResDeparture datetime, @Referencenumber int, @GuestNumber int, @Old_NumberOfPersons int, @New_NumberOfpersons int,  @Old_NumberOfChildren1 int, @New_NumberOfChildren1 int,  @Old_NumberOfChildren2 int, @New_NumberOfChildren2 int, @Old_Pieces int, @New_Pieces int, @StarProduct int, @OldStarProduct int, @FromNameList int, @Linenumber int,@RoomTypeNumber int, @DiscountPercent money =0, @HotelNumber int = -1 , @AFromRecreate bit = 0, @ACustomerNumber int = -1
AS
  SET NOCOUNT ON
  DECLARE @LOCKTIMEOUT INT
  SET @LOCKTIMEOUT = @@LOCK_TIMEOUT
  SET LOCK_TIMEOUT 5000

    -- 20170608 RN: Small performance improvements
	-- 20170606 RN: Rewrite how to find packet number
    -- 20150430 RN: Rewrite how pieces is updated (not using PacketItemNumber from Product/Packet anymore for updates)
    -- 20150325 RN: Added use of departuretime for arrangementlines
    -- 20150302 RN: Modified "SuggestHallNumberOnPacketLine" setting to be Domino aware.
    -- 20141204 RN: Added check for "Use Arrangement tolerance" setting.
    -- 20121121 RN: Replaced IS NULL with ISNULL function
	-- 20120127 RN: Added Deletion of ArrangementReservationConferenceLineXtab relations.
	-- sidst rettet 2008 0514 CopyfirstRoomLine RoomNumber to Place col if hotelsetup says so (a solution for feriebureau Bornholm)
	-- sidst rettet 2007 1002 use MAX when selecting packenumber from productprice table
	-- sidst rettet 20070621 call of UDF IsDepartmentInHotelList removed. Left outer Joins used instead
	-- sidst rettet 20070410 New Department Stuff NOT COMPATIBLE WITH 3.38
	-- sidst rettet 20060905 sql optimized in the block where the manual lines is moved
	-- sidst rettet 20060828 Join on PacketItemNEwRoomTypeXtab, Danparcs 
	-- sidst rettet 20060621 new variable : @PacketChargeDepartment
	-- sidst rettet 20060601 new parameter: @HotelNumber
	-- sidst rettet 20060507 ikke kompatibel med 3.36 FK_exdepartment
	-- sidst rettet 20050330 discount only if ware allows it
	-- sidst rettet 20050305 new paratmeter added. discountpercent
	-- sidst rettet 20040811   Avoid locktimeout: always delete through the key in the table (or through a index)  */      

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

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

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

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

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

  DECLARE @MainCourseResNoArrPacketLines BIT

  DECLARE @WeReadFromPacket int

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

  DECLARE @SplitArrLinesDifferentDebType int
  DECLARE @oldPacketItemNumberCount int

  -- Other
  DECLARE @MyRowCount int
  DECLARE @TempCount int
  DECLARE @RandSeed INT

  DECLARE @UseAdjustArrLinesToBookingTime BIT -- RQ3119

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

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


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

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

  DECLARE @PacketItemToleranceTime INT 
  DECLARE @PacketItemUseToleranceTime INT -- 0: Use HotelSetup Tolerance, 1: Use packetline tolerance, 2: Do not use tolerance (always create another arrangementline

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

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

  SELECT @FormatedMainResArrival = CONVERT(char(10), @MainResArrival, 112)
  SELECT @FormatedMainResDeparture = CONVERT(char(10), @MainResDeparture, 112)
  
  SELECT @CurrentDate = CONVERT(char(10), @New_StartDate, 112)

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

  DECLARE @ChargePacketDepartmentEnabled bit
  DECLARE @PersonRessourceBookingEnabled bit
  SELECT @PersonRessourceBookingEnabled = CONVERT(bit, KeyValue)
  FROM HotelSetUp WITH (NOLOCK)
  WHERE SearchKey LIKE 'useDepTimeInArrangement'

  SELECT @ChargePacketDepartmentEnabled = CONVERT(integer, ISNULL(KeyValue, 0))
  FROM HotelSetup WITH (NOLOCK)
  WHERE searchkey = 'ChargePacketDepartmentEnabled'

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

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

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

  SET @IsArrLineMoved = 0

--  PRINT '*****************************************************'
  DECLARE @PlusProductSamePeriod bit
  DECLARE @Day2 int
  DECLARE @Day3 int
  DECLARE @Day4 int
  DECLARE @NbrOfPlusDays int
  DECLARE @RLArrivalDate VARCHAR(8)
  DECLARE @RLDepartureDate VARCHAR(8)

  SELECT @PlusProductSamePeriod = PlusProductSamePeriod FROM ReservationRoomLine WITH (NOLOCK) WHERE LineNumber = @Linenumber
  SET @NbrOfPlusDays = 0

--  IF @PlusProductSamePeriod = 1 
--    PRINT 'IsPlusProductSamePeriod'

  --IF @StarProduct = 1
  --  PRINT 'IsPlusProduct'

--  PRINT 'WhichDay ' + CAST(@WhichDay AS CHAR(1))
  
  --20220919/RN: @WhichDay is not enough, we need to know how many plusproduct days there are
  IF @PlusProductSamePeriod = 1
  BEGIN
--    PRINT '@Linenumber: ' + CAST(@Linenumber AS VARCHAR(10))
    SELECT 
	  @Day2 = Day2,
	  @Day3 = Day3,
	  @Day4 = Day4,
	  @RLArrivalDate = ArrivalDate,
	  @RLDepartureDate = DepartureDate
	FROM ReservationRoomLine WITH (NOLOCK)
	WHERE LINENUMBER = @Linenumber
  END

  SET @NbrOfPlusDays = 1 --20220922/RN: @LoopCounter from ReCreateArrangement is @WhichDay here and is always at least 1
  IF @Day2 > 0
    SET @NbrOfPlusDays = 2
  IF @Day3 > 0
    SET @NbrOfPlusDays = 3
  IF @Day4 > 0
    SET @NbrOfPlusDays = 4

--  PRINT 'NbrOfPlusDays ' + CAST(@NbrOfPlusDays AS CHAR(1))
    
  ------------------------------------------------------------------------------------------------------------------------------------
  -- Cache package info and product prices for performance

  IF @AFromRecreate = 0
  BEGIN
    CREATE TABLE #PackageInfo
    (
      View_DateStamp DATETIME,
      View_Id INT,
      View_FK_ProductNumber INT,
      View_PacketType INT
    )
  END

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

  --print @new_startdate
  --print @new_enddate
  --print @New_ProductNumber

  DECLARE @TempStartDate date
  DECLARE @TempEndDate date
  DECLARE @PackageCount int

  SET @TempStartDate = CAST(@New_StartDate AS date)
  SET @TempEndDate = CAST(@New_Enddate as date)

  IF @AFromRecreate = 0
  BEGIN
    DECLARE @MinArrivalDate VARCHAR(8)
    DECLARE @MaxDepartureDate VARCHAR(8)
	DECLARE @ProductNumber INT
    
    SELECT @MinArrivalDate = MIN(ArrivalDate), @MaxDepartureDate = MAX(DepartureDate) FROM RESERVATIONS (NOLOCK) WHERE ReferenceNumber = @Referencenumber

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

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

  --SELECT @PackageCount = COUNT(*) FROM #PackageInfo
  --Print @PackageCount

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

  -- LOOK IF THE RESERVATIONLINE HAS AFFECTED THE ARRANGMENT BEFORE (LOOK IN CROSS TABLE)

  SELECT @found = 1

  IF NOT EXISTS (SELECT lineNumber
                 FROM arrangementRoomXtab WITH (NOLOCK)
                 WHERE lineNumber = @linenumber)
    SELECT @found = 0

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

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

  IF @fromnamelist = 1
  BEGIN
    IF (@Old_ProductNumber = @New_ProductNumber) AND 
	   (@Old_StartDate = @New_StartDate) AND 
	   (@Old_EndDate = @New_Enddate) AND 
	   (@Old_NumberOfPersons = @New_NumberOfpersons) AND 
	   (@Old_NumberOfChildren1 = @New_NumberOfChildren1) AND 
	   (@Old_NumberOfChildren2 = @New_NumberOfChildren2) AND 
	   (@New_Pieces = @Old_Pieces)
      RETURN
  END
  ELSE
  BEGIN
    IF (@Old_ProductNumber = @New_ProductNumber) AND 
	   (@Old_StartDate = @New_StartDate) AND 
	   (@Old_EndDate = @New_Enddate) AND 
	   (@Old_NumberOfPersons = @New_NumberOfpersons) AND 
	   (@Old_NumberOfChildren1 = @New_NumberOfChildren1) AND 
	   (@Old_NumberOfChildren2 = @New_NumberOfChildren2) AND 
	   (@New_Pieces = @Old_Pieces) AND 
	   (@found = 1)
      RETURN
  END

  SELECT @MainCourseResNoArrPacketLines = dbo.GetHotelSetupKeyValue('MainCourseResNoArrPacketLines', @CustomerNumber)
  IF @MainCourseResNoArrPacketLines = 1 -- Do not generate arrangmentlines for Main Course reservations
  BEGIN
    IF EXISTS (SELECT FK_ReferenceNumber FROM ReservationReservationXtab WITH (NOLOCK) WHERE FK_ReferenceNumber = @Referencenumber) -- It is a Main Course reservation
      RETURN
  END

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  --
  -- IF EXISTS USECHECKTOLERANCE THEN USE THE VALUE ELSE 

  SELECT @USETOLERANCE = KeyValue
  FROM HOTELSETUP WITH (NOLOCK)
  WHERE SEARCHKEY = 'UseCheckTolerance'
  SET @USETOLERANCE = ISNULL(@USETOLERANCE, 1)

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  --
  -- FIND THE TOLERANCE IN HOTELSETUP
  SELECT  @Tolerance = Keyvalue
  FROM hotelsetup WITH (NOLOCK)
  WHERE SearchKey = 'ArrTimeTolerance'

  SET @ToleranceDiffDT = ABS(DATEDIFF(MINUTE, @Tolerance, CONVERT(datetime, '00:00', 108)))

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

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

	--Print @CurrentDate

	IF (@PlusProductSamePeriod = 1)
	BEGIN
	  
	  IF (@CurrentDate = @RLArrivalDate) --(@FoundPacket = 0) AND (@CurrentDate = @FormatedStartDate) AND (@WhichDay = 1)
	  BEGIN
--	    PRINT '+productsameperiod arr packet'
-- 	    PRINT 'CurrDate: ' + @CurrentDate
		SELECT @TempProductNumber = Productnumber FROM RESERVATIONROOMLINE (NOLOCK) WHERE LINENUMBER = @Linenumber
 	    --SELECT @ProductName = ProductName FROM Products (NOLOCK) WHERE ProductNumber =  @New_ProductNumber
 	    SELECT @ProductName = ProductName FROM Products (NOLOCK) WHERE ProductNumber =  @TempProductNumber
--	    PRINT 'ProductNumber ' + CAST(@New_ProductNumber AS VARCHAR(10)) + ' ' + @ProductName
        SELECT TOP 1 @Packet = ProductPricePacket.FK_PacketNumber
        FROM #PackageInfo WITH (NOLOCK)
        INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
        --WHERE #PackageInfo.View_FK_ProductNumber = @New_ProductNumber
        WHERE #PackageInfo.View_FK_ProductNumber = @TempProductNumber
		  AND #PackageInfo.View_DateStamp = @CurrentDate
		  AND #PackageInfo.View_PacketType = 0
        ORDER BY #PackageInfo.View_Id DESC
		SELECT @PacketName = Text FROM Packet (NOLOCK) WHERE PACKETNUMBER = @Packet
--		PRINT 'ARR Packet ' + CAST(@Packet AS VARCHAR(10)) + ' ' + @PacketName
	  END
	  ELSE IF (@CurrentDate = @RLDepartureDate) -- (@FoundPacket = 0) AND (@CurrentDate = @FormatedEndDate) AND (@WhichDay > 1)
	  BEGIN
--	    PRINT ''
--	    PRINT '+productsameperiod dep packet'
--	    PRINT 'CurrDate: ' + @CurrentDate

		IF ISNULL(@Day4, 0) > 0
  		  SELECT @TempProductNumber = ProductNumber4 FROM RESERVATIONROOMLINE (NOLOCK) WHERE LINENUMBER = @Linenumber
        ELSE IF ISNULL(@Day3, 0) > 0
  		  SELECT @TempProductNumber = ProductNumber3 FROM RESERVATIONROOMLINE (NOLOCK) WHERE LINENUMBER = @Linenumber
        ELSE IF ISNULL(@Day2, 0) > 0
  		  SELECT @TempProductNumber = ProductNumber2 FROM RESERVATIONROOMLINE (NOLOCK) WHERE LINENUMBER = @Linenumber

 	    --SELECT @ProductName = ProductName FROM Products (NOLOCK) WHERE ProductNumber =  @New_ProductNumber
 	    SELECT @ProductName = ProductName FROM Products (NOLOCK) WHERE ProductNumber =  @TempProductNumber
--	    PRINT 'ProductNumber ' + CAST(@New_ProductNumber AS VARCHAR(10)) + ' ' + @ProductName
        SELECT TOP 1 @Packet = ProductPricePacket.FK_PacketNumber
        FROM #PackageInfo WITH (NOLOCK)
        INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
        --WHERE #PackageInfo.View_FK_ProductNumber = @New_ProductNumber
        WHERE #PackageInfo.View_FK_ProductNumber = @TempProductNumber
		  AND #PackageInfo.View_DateStamp = @CurrentDate
		  AND #PackageInfo.View_PacketType = 2
        ORDER BY #PackageInfo.View_Id DESC
		SELECT @PacketName = Text FROM Packet (NOLOCK) WHERE PACKETNUMBER = @Packet
--		PRINT 'DEP Packet ' + CAST(@Packet AS VARCHAR(10)) + ' ' + @PacketName
	  END
	  ELSE IF (@CurrentDate > @RLArrivalDate) AND (@CurrentDate < @RLDepartureDate)-- AND (@WhichDay > 1)
	  --IF (@FoundPacket = 0) AND ((@CurrentDate > @FormatedStartDate) /*AND (@CurrentDate < @FormatedEndDate)*/) AND (@WhichDay > 1)
	  BEGIN
--	    PRINT ''
--	    PRINT '+productsameperiod stay packet'
-- 	    PRINT 'CurrDate: ' + @CurrentDate

		IF ISNULL(@Day4, 0) > 0
  		  SELECT @TempProductNumber = ProductNumber4 FROM RESERVATIONROOMLINE (NOLOCK) WHERE LINENUMBER = @Linenumber
        ELSE IF ISNULL(@Day3, 0) > 0
  		  SELECT @TempProductNumber = ProductNumber3 FROM RESERVATIONROOMLINE (NOLOCK) WHERE LINENUMBER = @Linenumber
        ELSE IF ISNULL(@Day2, 0) > 0
  		  SELECT @TempProductNumber = ProductNumber2 FROM RESERVATIONROOMLINE (NOLOCK) WHERE LINENUMBER = @Linenumber

        --SELECT @ProductName = ProductName FROM Products (NOLOCK) WHERE ProductNumber =  @New_ProductNumber
        SELECT @ProductName = ProductName FROM Products (NOLOCK) WHERE ProductNumber =  @TempProductNumber
--	    PRINT 'ProductNumber ' + CAST(@New_ProductNumber AS VARCHAR(10)) + ' ' + @ProductName
        SELECT TOP 1 @Packet = ProductPricePacket.FK_PacketNumber
        FROM #PackageInfo WITH (NOLOCK)
        INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
        --WHERE #PackageInfo.View_FK_ProductNumber = @New_ProductNumber
        WHERE #PackageInfo.View_FK_ProductNumber = @TempProductNumber
		  AND #PackageInfo.View_DateStamp = @CurrentDate
		  AND #PackageInfo.View_PacketType = 1
        ORDER BY #PackageInfo.View_Id DESC
		SELECT @PacketName = Text FROM Packet (NOLOCK) WHERE PACKETNUMBER = @Packet
--		PRINT 'STAY Packet ' + CAST(@Packet AS VARCHAR(10)) + ' ' + @PacketName
--		PRINT ''
	  END
--	  PRINT 'Finished selecting PlusProductSamePeriod packages'
	END
	ELSE
	BEGIN
      IF @CurrentDate = @FormatedStartDate
      BEGIN
        IF @New_ProductNumber = -1  ---1 means that it is a NULL product
        BEGIN
	      SET @Packet = @NullArrPacket
        END
        ELSE
        IF @New_ProductNumber = 0  -- means that no product is chosen on the reservationline, 
        BEGIN
          IF (@fromnamelist = 1)
            AND (@GuestNumber > 1)
            AND (@Old_ProductNumber = 0)
            AND (@Old_NumberOfPersons = 0)
          BEGIN
            --EXEC GetPackageNumber @New_ProductNumber, 0, @CurrentDateDT, @Packet OUTPUT
            SELECT TOP 1 @Packet = ProductPricePacket.FK_PacketNumber
            FROM #PackageInfo WITH (NOLOCK)
            INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
		    WHERE #PackageInfo.View_FK_ProductNumber = @New_ProductNumber
		      AND #PackageInfo.View_DateStamp = @CurrentDate
		      AND #PackageInfo.View_PacketType = 0
            ORDER BY #PackageInfo.View_Id DESC
          END
          ELSE
		    SET @Packet = @EmptyArrPacket 
        END
        ELSE
        IF @StarProduct = 1 AND @WhichDay > 0
        BEGIN
          --EXEC GetPackageNumber @New_ProductNumber, 0, @CurrentDateDT, @Packet OUTPUT
          SELECT TOP 1 @Packet = ProductPricePacket.FK_PacketNumber
          FROM #PackageInfo WITH (NOLOCK)
          INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
          WHERE #PackageInfo.View_FK_ProductNumber = @New_ProductNumber
  	        AND #PackageInfo.View_DateStamp = @CurrentDate
		    AND #PackageInfo.View_PacketType = 0
          ORDER BY #PackageInfo.View_Id DESC
        END
        ELSE
        BEGIN
          --EXEC GetPackageNumber @New_ProductNumber, 0, @CurrentDateDT, @Packet OUTPUT
          SELECT TOP 1 @Packet = ProductPricePacket.FK_PacketNumber
          FROM #PackageInfo WITH (NOLOCK)
          INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
 	      WHERE #PackageInfo.View_FK_ProductNumber = @New_ProductNumber
		    AND #PackageInfo.View_DateStamp = @CurrentDate
		    AND #PackageInfo.View_PacketType = 0
          ORDER BY #PackageInfo.View_Id DESC
        END
      END
      ELSE
      IF @CurrentDate = @FormatedEnddate
      BEGIN
        IF @New_ProductNumber = -1 ---1 means that it is a NULL product
        BEGIN
	      SET @Packet = @NullDepPacket
        END
        ELSE
        IF @New_ProductNumber = 0  -- means that no product is chosen on the reservationline, 
        BEGIN
          IF (@fromnamelist = 1)
            AND (@GuestNumber > 1)
            AND (@Old_ProductNumber = 0)
            AND (@Old_NumberOfPersons = 0)
          BEGIN
            --EXEC GetPackageNumber @New_ProductNumber, 2, @CurrentDateDT, @Packet OUTPUT
            SELECT TOP 1 @Packet = ProductPricePacket.FK_PacketNumber
            FROM #PackageInfo WITH (NOLOCK)
            INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
		    WHERE #PackageInfo.View_FK_ProductNumber = @New_ProductNumber
		      AND #PackageInfo.View_DateStamp = @CurrentDate
		      AND #PackageInfo.View_PacketType = 2
            ORDER BY #PackageInfo.View_Id DESC
          END
          ELSE
		    SET @Packet = @EmptyDepPacket
        END
        ELSE
        BEGIN
          --EXEC GetPackageNumber @New_ProductNumber, 2, @CurrentDateDT, @Packet OUTPUT
          SELECT TOP 1 @Packet = ProductPricePacket.FK_PacketNumber
          FROM #PackageInfo WITH (NOLOCK)
          INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
	      WHERE #PackageInfo.View_FK_ProductNumber = @New_ProductNumber
		    AND #PackageInfo.View_DateStamp = @CurrentDate
		    AND #PackageInfo.View_PacketType = 2
          ORDER BY #PackageInfo.View_Id DESC
        END
      END
      ELSE --Stay
      BEGIN
        IF @New_ProductNumber = -1 ---1 means that it is a NULL product
        BEGIN
	      SET @Packet = @NullStayPacket
        END
        ELSE IF @New_ProductNumber = 0  -- means that no product is chosen on the reservationline, 
        BEGIN
          IF (@FromNameList = 1) AND (@GuestNumber > 1) AND (@Old_ProductNumber = 0) AND (@Old_NumberOfPersons = 0)
          BEGIN
            --EXEC GetPackageNumber @New_ProductNumber, 1, @CurrentDateDT, @Packet OUTPUT
            SELECT TOP 1 @Packet = ProductPricePacket.FK_PacketNumber
            FROM #PackageInfo WITH (NOLOCK)
            INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
		    WHERE #PackageInfo.View_FK_ProductNumber = @New_ProductNumber
		      AND #PackageInfo.View_DateStamp = @CurrentDate
		      AND #PackageInfo.View_PacketType = 1
            ORDER BY #PackageInfo.View_Id DESC
          END
          ELSE
		    SET @Packet = @EmptyStayPacket
        END
        ELSE
        BEGIN
          --EXEC GetPackageNumber @New_ProductNumber, 1, @CurrentDateDT, @Packet OUTPUT
          SELECT TOP 1 @Packet = ProductPricePacket.FK_PacketNumber
          FROM #PackageInfo WITH (NOLOCK)
          INNER JOIN ProductPricePacket WITH (NOLOCK) ON ProductPricePacket.Id = #PackageInfo.View_Id
	      WHERE #PackageInfo.View_FK_ProductNumber = @New_ProductNumber
		    AND #PackageInfo.View_DateStamp = @CurrentDate
		    AND #PackageInfo.View_PacketType = 1
          ORDER BY #PackageInfo.View_Id DESC
        END
	  END
    END

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

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

  DECLARE @SQLStr nvarchar(2000)

  SET @SQLStr = 'SELECT DISTINCT ' +
        'PacketItem_New.PacketItemNumber, ' +
        'PacketNumber, ' +
        'PacketItem_New.WareNumber, ' +
        'Time, ' +
        'DepartureTime, ' +
        'TextOverWrite, ' +
        'PriceOverWrite, ' +
        'debtype, ' +
        'CONVERT(varchar(4000), note), ' +
        'ReservationDisCount, ' +
        'Ware.BookTimeInMinutes, ' +
		'PacketItem_NEW.IsMandatory, ' +
		'UseToleranceTime, ' +
		'ToleranceTimeInMinutes, ' +
		'(SELECT TOP 1 FK_RecurringPatternId ' +
        ' FROM RecurringPatternWareXTab WITH (NOLOCK) ' +
        ' INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternId = RecurringPattern.Id ' +
        ' WHERE RecurringPatternWareXTab.FK_WareNumber = PacketItem_New.WareNumber ' +
        ') AS  FK_RecurringPatternId, ' +
		'(SELECT TOP 1 WareLinkNumber ' +
		' FROM Ware WITH (NOLOCK) ' +
		' INNER JOIN WareLinkGroup WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id ' +
    ' INNER JOIN WareLinkGroupRecurringPatternXTab WITH (NOLOCK) ON WareLinkGroupRecurringPatternXTab.FKWareLinkGroupId = WareLinkGroup.Id ' +
		' INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = WareLinkGroupRecurringPatternXTab.FKRecurringPatternID ' +
		' WHERE Ware.WareNumber = PacketItem_NEW.WareNumber ' +
		' ) AS FK_WareLinkNumber ' +
      'FROM PacketItem_New WITH (NOLOCK) ' +
      'INNER JOIN ware WITH (NOLOCK) ON PacketItem_new.WareNumber = Ware.WareNumber ' +
      'LEFT OUTER JOIN PacketItem_NEWRoomTypeXTab WITH (NOLOCK) ON PacketItem_NEWRoomTypeXTab.PacketItemNumber = PacketItem_NEW.PacketItemNumber ' +
      'LEFT OUTER JOIN PacketItem_NewDepartmentXTab WITH (NOLOCK) ON PacketItem_NewDepartmentXTab.PacketItemNumber = PacketItem_New.PacketItemNumber AND PacketItem_NewDepartmentXTab.Type = 1 ' +
      'LEFT OUTER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = PacketItem_NewDepartmentXTab.FK_Department ' +
      'WHERE PacketNumber = ' + CAST(ISNULL(@Packet, 0) AS VARCHAR(5)) +
      '  AND PacketItem_New.Arrangement = 1' +
      '  AND (PacketItem_NEWRoomTypeXTab.RoomTypeNumber = ' + CAST(@RoomTypeNumber AS VARCHAR(5)) + ' OR ISNULL(PacketItem_NEWRoomTypeXTab.RoomTypeNumber, 0) = 0)' +
      '  AND ((' + CAST(@HotelNumber AS VARCHAR(3)) + ' = -1) OR (Departments.HotelNumber = ' + CAST(@HotelNumber AS VARCHAR(3)) + '))'

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

  --IF @Packet > 0
--    print @SQLStr

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

  IF @AFromRecreate = 0
  BEGIN
    IF OBJECT_ID('tempdb..#TempPacketItems') IS NOT NULL
      TRUNCATE TABLE #TempPacketItems
	ELSE
	BEGIN
      CREATE TABLE #TempPacketItems (
        PacketItemNumber int,
        PacketNumber int,
        WareNumber int,
        Time char(5),
        DepartureTime char(5),
        TextOverWrite varchar(255),
        PriceOverWrite money,
        debtype int,
        note varchar(4000),
        ReservationDisCount tinyint,
        BookTimeInMinutes int,
		PacketItemMandatory bit,
		UseToleranceTime int,
		ToleranceTimeInMinutes int,
		FK_RecurringPatternId int,
        FK_WareLinkNumber int
      )
	END
  END

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

  OPEN CopyCur
  FETCH NEXT FROM CopyCur
  INTO @PacketItemNumber, @PacketNumber, @WareNumber, @Time, @DepTime, @TextOverWrite, @PriceOverWrite, @debtype, @note, @reservationDiscount, @BookTimeInMinutes, @PacketItemMandatory, @PacketItemUseToleranceTime, @PacketItemToleranceTime, @FK_RecurringPatternId, @FK_WareLinkNumber 

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

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    SET @TimeDT = CONVERT(datetime, @Time, 108)
	
      SELECT 
        @PacketItemToleranceTime = ISNULL(ToleranceTimeInMinutes, -1), 
        @PacketItemUseToleranceTime = UseToleranceTime
      FROM PacketItem_NEW WITH (NOLOCK)
      WHERE PacketItemNumber = @PacketItemNumber
	
    
	IF ISNULL(@PriceOverWrite, 0) = 0 -- is null 
      SELECT @PriceOverWrite = 0

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

    SELECT @WareHasCreateTable = WareCreateTable FROM Ware WITH (NOLOCK) WHERE WareNumber = @WareNumber

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

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

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

      IF @USETOLERANCE = 1
      BEGIN
        IF @@RowCount = 0
        BEGIN
		
          SELECT @TempArrNumber = ArrangementLinenumber
          FROM ArrangementLines WITH (NOLOCK)
          WHERE ISNULL(PacketItemNumber, 0) > 0
            AND WareNumber = @WareNumber
            AND Price = @PriceOverwrite
            AND (ISNULL(FK_Exdepartment, 0) = 0)
            AND (Time = @Time OR (ABS(DATEDIFF(MINUTE, @TimeDT, TimeDT)) <= @ToleranceDiffDT))			  
            AND Date = @CurrentDate
            AND arrangementLines.ArrangementLineNumber IN (SELECT DISTINCT ArrangementReservationsXTab.ArrangementLineNumber
                                                           FROM ArrangementReservationsXtab WITH (NOLOCK)
                                                           WHERE ArrangementReservationsXtab.Referencenumber = @Referencenumber)

          -- 20240214/RN: If @PacketItemUseToleranceTime = 1 then use the packetitems own tolerance time
          IF (@PacketItemUseToleranceTime = 1) AND (@PacketItemToleranceTime > -1)
          BEGIN
            SELECT @TempArrNumber = ISNULL(ArrangementLinenumber, -1)
            FROM ArrangementLines WITH (NOLOCK)
            WHERE ISNULL(PacketItemNumber, 0) > 0
              AND WareNumber = @WareNumber
              AND Price = @PriceOverwrite
              AND (ISNULL(FK_exdepartment, 0) = 0)
              AND ((TIME = @Time) OR (ABS(DATEDIFF(MINUTE, @TimeDT, TimeDT)) <= @PacketItemToleranceTime))
              AND DATE = @currentdate
              AND ArrangementLines.ArrangementLineNumber IN (
                         SELECT DISTINCT ArrangementReservationsXTab.ArrangementLineNumber
                         FROM ArrangementReservationsXtab WITH (NOLOCK)
                         WHERE ArrangementReservationsXtab.Referencenumber = @Referencenumber)
          END

		  IF (@PacketItemUseToleranceTime = 2)
		  BEGIN
			SET @TempArrNumber = -1
		  END

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

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

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

			END
		  END
        END

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

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

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


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

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

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

			SET @IdentityValue = 0
            IF @UseAdjustArrLinesToBookingTime = 1
			BEGIN
			  SET @CurrentDateAndTime = CONVERT(datetime, @CurrentDate + ' ' + @Time, 112)
			  IF ((@CurrentDateAndTime >= @OriginalStartDateAndTime) AND (@CurrentDateAndTime <= @OriginalEndDateAndTime)) OR (@PacketItemMandatory = 1)
			  BEGIN
			    SELECT @RandSeed = -MAX(ArrangementLineNumber) + 1 FROM ArrangementLines WITH (NOLOCK)
                INSERT INTO ArrangementLines (Date, PacketItemNumber, Time, DepartureTime, Price, Pieces, WareText, WareNumber, HallNumber, Note, FK_PacketChargeDepartment, PacketItemTime, IsUserModified,
 				                              PacketItemWareNumber, PacketItemWareText, PacketItemPrice, WareHasRule, IsArrLineMoved, WareHasCreateTable, PacketItemUseToleranceTime, PacketItemToleranceTime, AttachedRoomLine, PacketItemDebType, ArrangementLineUniqueID)
                  VALUES (@CurrentDate, @PacketItemNumber, @Time, @DepartureTime, @PriceoverWrite, @Pieces, @TextoverWrite, @WareNumber, @HallNumber, @Note, @PacketChargeDepartment, @Time, 0,
				          @WareNumber, @TextOverWrite, @PriceOverWrite, @WareHasRule, @IsArrLineMoved, @WareHasCreateTable, ISNULL(@PacketItemUseToleranceTime, 0), @PacketItemToleranceTime, @ALineNumber, @DebType, @RandSeed)			  
				SELECT @IdentityValue = SCOPE_IDENTITY()
			  END			  
			END
			ELSE
			BEGIN
  		      SELECT @RandSeed = -MAX(ArrangementLineNumber) + 1 FROM ArrangementLines WITH (NOLOCK)
			  INSERT INTO ArrangementLines (Date, PacketItemNumber, Time, DepartureTime, Price, /*TotalPrice,*/ Pieces, WareText, WareNumber, note, hallnumber, FK_PacketChargeDepartment, PlaceText, PacketItemTime, IsUserModified,
 				                            PacketItemWareNumber, PacketItemWareText, PacketItemPrice, WareHasRule, IsArrLineMoved, WareHasCreateTable, PacketItemUseToleranceTime, PacketItemToleranceTime, AttachedRoomLine, PacketItemDebType, ArrangementLineUniqueID)
                VALUES (@CurrentDate, @PacketItemNumber, @Time, @DepartureTime, @PriceoverWrite, /*@TotalPrice,*/ @Pieces, @TextoverWrite, @WareNumber, @note, @hallnumber, @PacketChargeDepartment, @Place, @Time, 0,
				        @WareNumber, @TextOverWrite, @PriceOverWrite, @WareHasRule, @IsArrLineMoved, @WareHasCreateTable, ISNULL(@PacketItemUseToleranceTime, 0), @PacketItemToleranceTime, @Linenumber, @DebType, @RandSeed)			  
			  SELECT @IdentityValue = SCOPE_IDENTITY()
			END

			IF @IdentityValue > 0
			BEGIN
--		      UPDATE ArrangementLines
--			  SET ArrangementLines.ArrangementLineUniqueID = ((@IdentityValue & 0x7ffff /*19 bits*/) * 4096 /* << 12 */) | (@IdentityValue / 524288  /* >> 19 */)
--			  WHERE ArrangementLines.ArrangementLineNumber = @IdentityValue

              INSERT INTO ArrangementReservationsXtab  VALUES (@IdentityValue, @Referencenumber, @GuestNumber, @whichday)
              INSERT INTO ArrangementRoomxtab (arrangementlinenumber, linenumber, plusProductLineNumber, packetitemnumber) VALUES (@IdentityValue, @LineNumber, @Whichday, @PacketItemNumber)

              SELECT DISTINCT FK_Department INTO #TempPacketDepartment
              FROM PacketItem_NewDepartmentXTab WITH (NOLOCK)
              LEFT OUTER JOIN Departments WITH (NOLOCK) ON PacketItem_NewDepartmentXTab.FK_Department = Departments.PK_Department
              WHERE PacketItemNumber = @PacketItemNumber
                AND Type = 1
                AND (@HotelNumber = -1 OR Departments.HotelNumber = @HotelNumber)

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

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

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

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

            SELECT @Pieces = Pieces
            FROM arrangementlines WITH (NOLOCK)
            WHERE arrangementlinenumber = @TempArrNumber --If pieces in an arrangementline has turned to 0 then delete it
			
            IF @Pieces = 0
            BEGIN
              DELETE FROM arrangementRoomXtab
              WHERE arrangementlinenumber = @TempArrNumber
              DELETE FROM ArrangementReservationsXtab
              WHERE arrangementLineNumber = @TempArrNumber
              DELETE FROM ArrangementReservationConferenceLineXtab
              WHERE ArrangementLineNumber = @TempArrNumber
              DELETE FROM ArrangementLineDepartmentXTab
              WHERE FK_ArrangementLineNumber = @TempArrNumber
              DELETE FROM arrangementlines
              WHERE arrangementlinenumber = @TempArrNumber
            END
          END
        END

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

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