CREATE PROCEDURE DBO.TransferArrangementToNewRef @OldReferencenumber int,@OldGuestNumber int ,@NewReferencenumber int,@NewGuestNumber int AS
SET NOCOUNT ON

--Last Changed
-- 2013 0802 Calculate arrangementline date relative to old arrangementdate, CR062910
-- 2012 1121 Replaced IS NULL with ISNULL function
-- 2007 0410 use the new dep xtab instead
-- 2007 0321 BUG: forgot to copy the new col: FK_PacketChargeDepartment

   DECLARE @ArrangementLineNumber int
   DECLARE @Date char (8)
   DECLARE @PacketItemNumber int
   DECLARE @Time char(5)
   DECLARE @Price real
   DECLARE @TotalPrice real
   DECLARE @Pieces int
   DECLARE @WareText varchar (255)
--   DECLARE @ArrDepartments Char(32) not used anymore --- cross tab instead
  DECLARE @PK_Department INT
   DECLARE @FK_Exdepartment int
   DECLARE @FK_PacketChargeDepartment int
   DECLARE @PlaceText Varchar
   DECLARE @WareNumber int
   DECLARE @HallNumber int
   DECLARE @Note Varchar (8000)
   DECLARE @PacketItemTIme VARCHAR(5)
   DECLARE @PacketItemWareNumber INT
   DECLARE @PacketItemWareText VARCHAR(80)
   DECLARE @PacketItemPrice MONEY

   DECLARE @IdentityValue int

   DECLARE @ArrNumber  int
   DECLARE @NewLineNumber int
   DECLARE @LineNumber int
   DECLARE @PlusProductLineNumber int
   DECLARE @ReferenceNumber int     
   DECLARE @GuestNumber int

  DECLARE @OldMainResArrival datetime
  DECLARE @NewMainResArrival datetime  
  DECLARE @SystemDate DATETIME

  SELECT @SystemDate=CONVERT (DATETIME, SystemDate, 112) FROM System WITH (NOLOCK) WHERE Number=1
  
  SELECT @OldMainResArrival=CONVERT (DATETIME, Arrivaldate) FROM Reservations WITH (NOLOCK) WHERE referencenumber=@OldReferencenumber and guestnumber=0
  SELECT @NewMainResArrival = CONVERT(DATETIME, ArrivalDate) FROM RESERVATIONS WITH (NOLOCK) WHERE ReferenceNumber = @NewReferencenumber and GuestNumber = 0

--First the arrangementlines
   DECLARE ARRCUR  CURSOR FORWARD_ONLY FOR --select all  arrangementlines and copy them to new reservation

   SELECT Arrangementlinenumber, Date,PacketItemNumber,Time,Price,TotalPrice,Pieces,WareText, /*Departments,*/FK_Exdepartment, FK_PacketChargeDepartment, PlaceText,WareNumber,Note, 
     PacketItemTime, PacketItemWareNumber, PacketItemWareText, PacketItemPrice
   FROM ArrangementLines WITH (nolock) where arrangementlinenumber in (select arrangementlinenumber from arrangementreservationsxtab WITH (NOLOCK) where referencenumber= @OldReferencenumber )
   AND arrangementlinenumber in (select arrangementlinenumber from arrangementroomxtab WITH (nolock))

   OPEN ARRCUR
	
   FETCH NEXT FROM ARRCUR
   INTO  @ArrNumber,@Date,@PacketItemNumber,@Time,@Price,@TotalPrice,@Pieces,@WareText,@FK_Exdepartment,@FK_PacketChargeDepartment, @PlaceText,@WareNumber,@Note, @PacketItemTime, @PacketItemWareNumber, @PacketItemWareText, @PacketItemPrice
   WHILE (@@FETCH_STATUS <> -1)
   BEGIN
        
     --20130802/RN: CR062910, Calculate date relative to new arrivaldate instead of setting it to systemdate   
     --if convert(datetime,@OldMainResArrival) <@SystemDate
     --  set @date =Convert(char, @SystemDate + ( Convert(datetime,@date) -@OldMainResArrival),112)   
       
     set @Date = CONVERT(char, CONVERT(DATETIME, @Date, 112) + DATEDIFF(dd, @OldMainResArrival, @NewMainResArrival),112)
       
     INSERT INTO ArrangementLines (Date, PacketItemNumber, Time, Price, /*TotalPrice,*/ Pieces, WareText, FK_Exdepartment,FK_PacketChargeDepartment, WareNumber, note, PacketItemTime, PacketItemWareNumber, PacketItemWareText, PacketItemPrice) 
	 VALUES                       (@Date, @PacketItemNumber, @Time, @Price, /*@TotalPrice,*/ @Pieces, @Waretext, @FK_Exdepartment, @FK_PacketChargeDepartment, @WareNumber, @note, @PacketItemTime, @PacketItemWareNumber, @PacketItemWareText, @PacketItemPrice)  
     select @identityvalue=@@identity from ArrangementLines WITH (NOLOCK)

    --LineNumber Cross tab
     DECLARE ArrRoomCur  CURSOR FORWARD_ONLY FOR 
     SELECT LineNumber,PacketItemNumber,PlusProductLineNumber FROM arrangementroomxtab WITH (NOLOCK) where Arrangementlinenumber =@Arrnumber
     Open ArrRoomCur
     FETCH NEXT FROM ArrRoomCur
     INTO  @LineNumber,@PacketItemNumber,@PlusProductLineNumber
     WHILE (@@FETCH_STATUS <> -1) --Ok we 
     BEGIN
       if ISNULL(@linenumber,0) > 0 -- is not null
       begin
         if exists (select ReservationConfLineNumber from reservationconferenceline WITH (nolock) where ReservationConfLineNumber=@LineNumber)
           select @NewLineNumber =  rebookedUnderLineNumber from ReservationConferenceline WITH (Nolock) where reservationconflinenumber=@LineNumber
         else
         begin
           select  @NewLineNumber = RebookedUnderLineNumber from ReservationRoomLine WITH (nolock) where linenumber=@LineNumber
         end
       if ISNULL(@NewLineNumber,0) > 0 -- is not null 
         if not exists (select * from arrangementroomxtab WITH (nolock) where arrangementlinenumber=@identityValue and linenumber=@NewLineNumber and PlusProductLineNumber=@PlusProductLineNumber and PacketItemNumber=@PacketItemNumber)
           Insert into arrangementroomxtab(Arrangementlinenumber,LineNumber,PlusProductLineNumber,PacketItemNumber ) values (@identityValue,@NewLineNumber,@PlusProductLineNumber,@PacketItemNumber)
       end
       FETCH NEXT FROM ArrRoomCur
       INTO @LineNumber,@PacketItemNumber,@PlusProductLineNumber             
     END
     Close ArrRoomCur
     Deallocate ArrRoomCur



    --Reservation Cross tab
     DECLARE ArrResCur  CURSOR FORWARD_ONLY FOR 
     SELECT Arrangementlinenumber, Referencenumber,GuestNumber,PlusProductLineNumber
     FROM ArrangementReservationsXtab WITH (nolock) where ArrangementLineNumber = @ArrNumber
 
     OPEN ArrResCur

     FETCH NEXT FROM ArrResCur
     INTO  @ArrangementLineNumber,@Referencenumber,@GuestNumber,@PlusProductLineNumber
     WHILE (@@FETCH_STATUS <> -1)
     BEGIN
       if not exists (select * from arrangementreservationsxtab WITH (nolock) where arrangementlinenumber=@identityValue and referencenumber=@NewReferenceNumber and guestnumber=@GuestNumber and PlusProductLineNumber=@PlusProductLineNumber)
       Insert into ArrangementReservationsxtab (Arrangementlinenumber,Referencenumber,GuestNumber,PlusProductLinenumber) values (@identityValue,@NewReferencenumber,@GuestNumber,@PlusProductLineNumber)
       FETCH NEXT FROM ArrResCur
       INTO  @ArrangementLineNumber,@Referencenumber,@GuestNumber,@PlusProductLineNumber
     END
     CLose ArrResCur
     DeAllocate ArrResCur

    -- ArrangmentLineDepartmentXTab
     DECLARE DepCur Cursor Forward_Only For
     SELECT DISTINCT FK_DepartmentNumber FROM ArrangementLineDepartmentXTab  WITH (NOLOCK)
      WHERE FK_ArrangementLineNumber=@ArrNumber 
      OPEN DepCur
      FETCH NEXT FROM DepCur INTO @PK_Department
      WHILE  (@@FETCH_STATUS <> -1)
      BEGIN
       IF NOT EXISTS (SELECT FK_ArrangementLineNumber FROM ArrangementLineDepartmentXTab WITH (NOLOCK) WHERE FK_ArrangementLineNumber = @IdentityValue AND FK_DepartmentNumber = @PK_Department)
         INSERT INTO ArrangementLineDepartmentXTab (FK_ArrangementLineNumber, FK_DepartmentNumber) VALUES (@IdentityValue, @PK_Department)
       FETCH NEXT FROM DepCur INTO @PK_Department
       END
       CLOSE DepCur
       DEALLOCATE DepCur

     FETCH NEXT FROM ARRCUR
     INTO  @ArrNumber,@Date,@PacketItemNumber,@Time,@Price,@TotalPrice,@Pieces,@WareText,@FK_Exdepartment,@FK_PacketChargeDepartment, @PlaceText,@WareNumber,@Note, @PacketItemTime, @PacketItemWareNumber, @PacketItemWareText, @PacketItemPrice
   END

  Close Arrcur
  Deallocate ArrCur

