--DROP PROCEDURE dbo.CopyReservation

CREATE PROCEDURE dbo.CopyReservation @OldReferenceNumber INT, @OldGuestNumber INT, @NewReferenceNumber INT OUTPUT, @NewGuestNumber INT OUTPUT, @UserIDNumber INT, @FirstAddressNumber INT OUTPUT, @FirstRoomLineNumber INT OUTPUT, @NewReferenceNumberOverwrite INT=0 AS
SET NOCOUNT ON
/*  
    DEPENDENCIES: CopyReservationGroup, UpdateArrivalAndDeparture2

    Created by Jacob Pedersen 13-06-2002

    Last updated:
    20100209/BA: BUG: CR012459/Cf/Ean (ReservationAddress.CompanyForm) did only copy 10 of the 15 required characters.
    20100112/BA: BUG: CR011929/StaffID wasn't included when Rebooking.
    20080528/BA: EXT: Case# CR001611. There is an error when copying a reservation using a allotment with only one left.
                      This error would be hard to fix. Therefore the user can enable or disable the copying of the
                      allotment using the CopySaveAllotmentDisabling Hotel Setting. The Setting is locates at:
                      HotelSetup->Basic->Booking->Copy/Save allotment disabling.
	20070726 JP: EXT: removed NameListCreated field
             20070410 AV: Use the new DepartmentXTab for ArrangementLines
             20070321 AV BUG:  the change just below: Stil use the old exdepartment col until all code do not access the old col anymore
	20070228 AV: BUG: Arrangement manuel lines: forgot to use the new fk_exdepartment col in cursor
	20070228 AV: EXT: added missing fields to arrrangment: PacketChargeDepartment
	20070215 JP: EXT: added missing fields; GuestIsSmoker, RoomKey, BedAndBreakfast and Project    
             20070212 AV: BUG: copy reservationConferenceline. Arrivaldate/departuredate was calculated wrong toward systemdate
             20070206 JP: BUG: PlaceText has no length so only one char is copied!
	20070205 JP: BUG: removed change of locktimeout to 0
             20060518 JP: BUG: CreditCard number is only defined as VARCHAR(20), must be VARCHAR(128)
             20060507 AV : not compatible with 3.36 : arrangementlines.exdepartment moved to fk_exdepartment
	20050822 JP: added ContactPersonCustomerNumber to ReservationAddress
	20050526 JP: changed to use GetNextNumber SP
	20050223 JP: added AddressTypeNumber, GuestAddressNumber, CompanyAddressNumber, AgencyAddressNumber, AgentAddressNumber
	20050131 AV: new fields: reservations table: PassportNumber. ReservationAddress: BuyersOrderID, BuyersAccount
	20050107 AV: field creditcardtype removed
	20041018 JP: do not read userid from old reservation, use the one of the person calling this SP
	20040921 JP: CompanyForm
	20040916 JP: SplitBillNumber1-4
	20040721 AV schema booking, link linenumber
	20040716: JP: changed: new field types
	2004-06-28 JP: Followup. Fixed Followup.Note bug and uses SystemDateTime instead of SystemDate
	2004-06-09 JP: ReservationAttachment table is deleted. StoredProc fails
	01-03-2004 Jacob Pedersen added CallSetInterface field
	26-09-2003 Jacob Pedersen copy NameListCreateFlag and NameListSetting
	18-08-2003 Jacob Pedersen added missing fields to Reservation
	09-07-2003 Jacob Pedersen removes roomnumber in more cases
	14-04-2003 Jacob Pedersen fixes
	20-03-2003 Jacob Pedersen @FirstAddressNumber INT OUTPUT, @FirstRoomLineNumber INT OUTPUT

    NOTE: - This routine makes a copy of a NOX reservation and gives it a new referencenumber. It returns the new referencenumber

                -  ALL Identity tables are locked for the duration of the SP if it is called within a transaction, which it always should be!!!!!  This has been fixed by updating identitytables outside transaction and providing them as parameters here

                -  If @NewReferenceNumberOverwrite, @FirstAddressNumber or @FirstRoomLineNumber is 0 (zero) then own values will be taken from Identity tables!

                -  If copying a subres within same referencenumber it will get a new roomline!  This a decision made with KA.  It also fixes the problem with checking if there is room for more people in roomtype

    QUESTIONS: How do one rebook a whole group? First make a copy of the group if it is outofhouse with CopyResGroup. Then rebook everyone

    PROBLEMS: If something goes wrong all changes to identity tables are rolled back. This is not a big problem as the tables are locked for the duration. This however is a BIG PROBLEM for huge namelists. Solution? Drop the identity tables!
*/

--SET lock_timeout 3000

DECLARE @SystemDate DATETIME
SELECT @SystemDate=CONVERT (DATETIME, SystemDate, 112) FROM System WITH (NOLOCK) WHERE Number=1

DECLARE @CurrentAddressNumber INT
SET @CurrentAddressNumber = @FirstAddressNumber
DECLARE @CurrentRoomLineNumber INT
SET @CurrentRoomLineNumber = @FirstRoomLineNumber

DECLARE @NewLineNumber INT
DECLARE @MainResStatus INT
DECLARE @StayInSameGroup INT
SET @StayInSameGroup = 0
DECLARE @RemoveRoomNumbers BIT

DECLARE @ReferenceNumber INT
DECLARE @HotelNumber SMALLINT
DECLARE @ReservationType SMALLINT
DECLARE @AllotmentNumber INT
DECLARE @StaffID INT -- BA/20100112: CR011929/StaffID wasn't included when Rebooking.

IF @OldGuestNumber > 0
BEGIN
  IF @NewReferenceNumberOverwrite = 0
  BEGIN
    SELECT @MainResStatus=Status FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=0
    IF @MainResStatus > 5 -- we cannot rebook a subres because mainres is NOX
    BEGIN
      SET @NewReferenceNumber=-1
      SET @NewGuestNumber=-1
      RETURN
    END
    SELECT @NewGuestNumber=MAX (GuestNumber)+1 FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber  -- New guestnumber but keep same group referencenumber
    SET @NewReferenceNumber=@OldReferenceNumber  -- Keep old referencenumber
  END
  ELSE
  BEGIN
    -- Check if NewRef no exists if it doesn't then we have to rebook subres as single, hence NewGuestNumber=0!
    SELECT @ReferenceNumber=ReferenceNumber FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber=@NewReferenceNumberOverwrite AND GuestNumber=0
    IF @@rowcount = 1
    BEGIN
      -- Check if OldGuestNumber exists. If it does then we need to find a new one!
      SELECT @ReferenceNumber=ReferenceNumber FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber=@NewReferenceNumberOverwrite AND GuestNumber=@OldGuestNumber
      IF @@rowcount = 1
       SELECT @NewGuestNumber=MAX (GuestNumber)+1 FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber=@NewReferenceNumberOverwrite  -- New guestnumber but keep same group referencenumber
      ELSE
        SET @NewGuestNumber=@OldGuestNumber
    END
    ELSE
      SET @NewGuestNumber=0
    SET @NewReferenceNumber=@NewReferenceNumberOverwrite  -- Keep old referencenumber
  END
END
ELSE IF @OldGuestNumber = 0
BEGIN
  IF @NewReferenceNumberOverwrite=0
  BEGIN
/*
    BEGIN TRAN
    UPDATE ReservationIdentity SET @NewReferenceNumber = IdentityField = IdentityField + 1  -- New referencenumber
    COMMIT TRAN
*/
    EXEC GetNextNumber 'RESERVATIONIDENTITY', 1, @NewReferenceNumber OUTPUT
  END
  ELSE
    SET @NewReferenceNumber=@NewReferenceNumberOverwrite  -- Keep old referencenumber - Used when copying a whole group with CopyReservationGroup. First call gives new refnr, then succeeding calls reuses this
  SET @NewGuestNumber=@OldGuestNumber   -- Keep same guestnumber
END

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ReservationMain --

-- BA/080528: Case# CR001611/CopySaveAllotmentDisabling
DECLARE @CopySaveAllotmentDisabling INT
SELECT @CopySaveAllotmentDisabling = CONVERT(INT, '0' + KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'CopySaveAllotmentDisabling'

--IF (@OldReferenceNumber <> @NewReferenceNumber) AND (@NewReferenceNumberOverwrite = 0)
IF @OldReferenceNumber <> @NewReferenceNumber
BEGIN
  SELECT @ReservationType=ReservationType FROM ReservationMain WITH (NOLOCK) WHERE ReferenceNumber=@NewReferenceNumber
  IF @@RowCount = 0  -- test if new main res is already there
  BEGIN
    -- BA/20100112: CR011929/StaffID wasn't included when Rebooking.
    SELECT @HotelNumber=HotelNumber, @ReservationType=ReservationType, @AllotmentNumber=AllotmentNumber, @StaffID = StaffID 
    FROM ReservationMain WITH (NOLOCK) 
    WHERE ReferenceNumber=@OldReferenceNumber

    -- BA/080528: Case# CR001611/CopySaveAllotmentDisabling
    IF @CopySaveAllotmentDisabling = 1 SET @AllotmentNumber = NULL
    
    INSERT INTO ReservationMain (ReferenceNumber,      HotelNumber,  ReservationType,  AllotmentNumber,  StaffID) 
         VALUES                 (@NewReferenceNumber, @HotelNumber, @ReservationType, @AllotmentNumber, @StaffID)
  END
END


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Reservations --
DECLARE @AltResNo VARCHAR (20)
DECLARE @Status tinyint
DECLARE @MARKETSEGMENT smallint
DECLARE @POINTOFSALE smallint
DECLARE @GUESTTYPE smallint
DECLARE @PURPOSECODE smallint
--DECLARE @CANCELCODE smallint
DECLARE @CREDITMAX money
DECLARE @ExpireDate DATETIME
DECLARE @ControlCode INT
--DECLARE @CREDITCARDTYPE smallint
--DECLARE @CREDITCARDNUMBER varchar(20)
DECLARE @CREDITCARDNUMBER varchar(128)
DECLARE @METHODOFPAYMENT smallint
DECLARE @VOUCHER varchar (20)
DECLARE @USERID varchar (10)
DECLARE @ArrivalDate char (8)
DECLARE @ArrivalTime char (5)
DECLARE @DepartureDate char (8)
DECLARE @DepartureTime char (5)
DECLARE @Adults smallint
DECLARE @Children1 smallint
DECLARE @Children2 smallint
DECLARE @ProductNumber int
DECLARE @Day2 tinyint
DECLARE @ProductNumber2 int
DECLARE @Day3 tinyint
DECLARE @ProductNumber3 int
DECLARE @Day4 tinyint
DECLARE @ProductNumber4 int
DECLARE @Price money
DECLARE @TotalPrice money
DECLARE @PriceOverwritten bit
DECLARE @SPLITBILL smallint
DECLARE @BookedTypeNumber int
DECLARE @PriceOverwritten2 bit
DECLARE @PriceOverwritten3 bit
DECLARE @PriceOverwritten4 bit
DECLARE @FirstPrice1 smallmoney
DECLARE @FirstPrice2 smallmoney
DECLARE @FirstPrice3 smallmoney
DECLARE @FirstPrice4 smallmoney
DECLARE @TotalPrice1 money
DECLARE @TotalPrice2 money
DECLARE @TotalPrice3 money
DECLARE @TotalPrice4 money,
@SplitBillNumber1 smallint,
@SplitBillNumber2 smallint,
@SplitBillNumber3 smallint,
@SplitBillNumber4 smallint
DECLARE @FollowUpNumber int
DECLARE @PaysAdults tinyint
DECLARE @PaysChildren1 tinyint
DECLARE @PaysChildren2 tinyint
DECLARE @MappedRefNo int
DECLARE @MappedGuestNo int
DECLARE @SideHouse smallint
DECLARE @CancellationPercent smallmoney
DECLARE @Currency int
DECLARE @DiscountCustomerType tinyint
--DECLARE @TurnawayNumber int
DECLARE @CallSetInterface bit
DECLARE @PassPortNumber VARCHAR (25),
@GuestIsSmoker tinyint,
@RoomKey tinyint, 
@BedAndBreakfast tinyint,
@Project SMALLDATETIME,
@FK_ReservationGroup INT

--SELECT @AltResNo=AltResNo, @Status=Status, @MARKETSEGMENT=MARKETSEGMENT, @POINTOFSALE=POINTOFSALE, @GUESTTYPE=GUESTTYPE, @PURPOSECODE=PURPOSECODE, @CANCELCODE=CANCELCODE, @CREDITMAX=CREDITMAX,
SELECT @AltResNo=AltResNo, @Status=Status, @MARKETSEGMENT=MARKETSEGMENT, @POINTOFSALE=POINTOFSALE, @GUESTTYPE=GUESTTYPE, @PURPOSECODE=PURPOSECODE, @CREDITMAX=CREDITMAX,
--@CREDITCARDNUMBER=CREDITCARDNUMBER, @METHODOFPAYMENT=METHODOFPAYMENT, @VOUCHER=VOUCHER, @USERID=USERID, @ArrivalDate=ArrivalDate, @ArrivalTime=ArrivalTime, @DepartureDate=DepartureDate, @DepartureTime=DepartureTime, @Adults=Adults,@Children1=Children1,
@CREDITCARDNUMBER=CREDITCARDNUMBER, @METHODOFPAYMENT=METHODOFPAYMENT, @VOUCHER=VOUCHER, @ArrivalDate=ArrivalDate, @ArrivalTime=ArrivalTime, @DepartureDate=DepartureDate, @DepartureTime=DepartureTime, @Adults=Adults,@Children1=Children1,
@Children2=Children2, @ProductNumber=ProductNumber, @Day2=Day2, @ProductNumber2=ProductNumber2, @Day3=Day3, @ProductNumber3=ProductNumber3, @Day4=Day4, @ProductNumber4=ProductNumber4, @Price=Price, @TotalPrice=TotalPrice, @PriceOverwritten=PriceOverwritten,
@SPLITBILL=SPLITBILL, @PriceOverwritten2=PriceOverwritten2, @PriceOverwritten3=PriceOverwritten3, @PriceOverwritten4=PriceOverwritten4, @FirstPrice1=FirstPrice1, @FirstPrice2=FirstPrice2, @FirstPrice3=FirstPrice3, @FirstPrice4=FirstPrice4,
@TotalPrice1=TotalPrice1, @TotalPrice2=TotalPrice2, @TotalPrice3=TotalPrice3, @TotalPrice4=TotalPrice4, @SplitBillNumber1=SplitBillNumber1, @SplitBillNumber2=SplitBillNumber2, @SplitBillNumber3=SplitBillNumber3, @SplitBillNumber4=SplitBillNumber4,@FollowUpNumber=FollowUpNumber, @PaysAdults=PaysAdults, @PaysChildren1=PaysChildren1, @PaysChildren2=PaysChildren2, @MappedRefNo=MappedRefNo, @MappedGuestNo=MappedGuestNo,
--@ExpireDate=ExpireDate, @ControlCode=ControlCode, @SideHouse=SideHouse, @CancellationPercent=CancellationPercent, @Currency=Currency, @DiscountCustomerType=DiscountCustomerType, @TurnawayNumber=TurnawayNumber, @CallSetInterface=CallSetInterface, @PassPortNumber=PassPortNumber,
@ExpireDate=ExpireDate, @ControlCode=ControlCode, @SideHouse=SideHouse, @CancellationPercent=CancellationPercent, @Currency=Currency, @DiscountCustomerType=DiscountCustomerType, @CallSetInterface=CallSetInterface, @PassPortNumber=PassPortNumber,
@GuestIsSmoker=GuestIsSmoker, @RoomKey=RoomKey, @BedAndBreakfast=BedAndBreakfast, @Project=Project, @FK_ReservationGroup = FK_ReservationGroup
FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=@OldGuestNumber

SELECT @UserID=UserIDCode FROM Personel WITH (NOLOCK) WHERE UserIDNumber=@UserIDNumber

SET @RemoveRoomNumbers = 0
IF @Status >= 5
BEGIN
  SELECT @Status = CONVERT (INT, '0'+KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'DefaultResStatus'
  IF @Status = 5
    SET @RemoveRoomNumbers = 1
END

IF @ArrivalDate < @SystemDate
BEGIN
  SET @DepartureDate = CONVERT (CHAR (8), CONVERT (DATETIME, @DepartureDate, 112)-CONVERT (DATETIME, @ArrivalDate, 112)+@SystemDate, 112)
  SET @ArrivalDate = CONVERT (CHAR (8), @SystemDate, 112)
  SET @RemoveRoomNumbers = 1
END

--INSERT INTO Reservations (ReferenceNumber, GuestNumber, AltResNo, Status, MARKETSEGMENT, POINTOFSALE, GUESTTYPE, PURPOSECODE, CANCELCODE, CREDITMAX, CREDITCARDNUMBER, METHODOFPAYMENT, VOUCHER, USERID, ArrivalDate, ArrivalTime, DepartureDate,
INSERT INTO Reservations (ReferenceNumber, GuestNumber, AltResNo, Status, MARKETSEGMENT, POINTOFSALE, GUESTTYPE, PURPOSECODE, CREDITMAX, CREDITCARDNUMBER, METHODOFPAYMENT, VOUCHER, USERID, ArrivalDate, ArrivalTime, DepartureDate,
DepartureTime, Adults, Children1, Children2, ProductNumber, Day2, ProductNumber2, Day3, ProductNumber3, Day4, ProductNumber4, Price, TotalPrice, PriceOverwritten, SPLITBILL, PriceOverwritten2, PriceOverwritten3, PriceOverwritten4, FirstPrice1, FirstPrice2, FirstPrice3, FirstPrice4, TotalPrice1,
--TotalPrice2, TotalPrice3, TotalPrice4, SplitBillNumber1, SplitBillNumber2, SplitBillNumber3, SplitBillNumber4, FollowUpNumber, PaysAdults, PaysChildren1, PaysChildren2, MappedRefNo, MappedGuestNo, ExpireDate, ControlCode, SideHouse, CancellationPercent, Currency, DiscountCustomerType, TurnawayNumber, CallSetInterface, PassPortNumber,
TotalPrice2, TotalPrice3, TotalPrice4, SplitBillNumber1, SplitBillNumber2, SplitBillNumber3, SplitBillNumber4, FollowUpNumber, PaysAdults, PaysChildren1, PaysChildren2, MappedRefNo, MappedGuestNo, ExpireDate, ControlCode, SideHouse, CancellationPercent, Currency, DiscountCustomerType, CallSetInterface, PassPortNumber,
GuestIsSmoker, RoomKey, BedAndBreakfast, Project, FK_ReservationGroup)
--VALUES (@NewReferenceNumber, @NewGuestNumber, @AltResNo, @Status, @MARKETSEGMENT, @POINTOFSALE, @GUESTTYPE, @PURPOSECODE, @CANCELCODE,
VALUES (@NewReferenceNumber, @NewGuestNumber, @AltResNo, @Status, @MARKETSEGMENT, @POINTOFSALE, @GUESTTYPE, @PURPOSECODE,
@CREDITMAX, @CREDITCARDNUMBER, @METHODOFPAYMENT, @VOUCHER, @USERID, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @Adults, @Children1, @Children2, @ProductNumber, @Day2, @ProductNumber2, @Day3, @ProductNumber3, @Day4,
@ProductNumber4, @Price, @TotalPrice, @PriceOverwritten, @SPLITBILL, @PriceOverwritten2, @PriceOverwritten3, @PriceOverwritten4, @FirstPrice1, @FirstPrice2, @FirstPrice3, @FirstPrice4, @TotalPrice1, @TotalPrice2, @TotalPrice3, @TotalPrice4, @SplitBillNumber1, @SplitBillNumber2, @SplitBillNumber3, @SplitBillNumber4, @FollowUpNumber, @PaysAdults,
--@PaysChildren1, @PaysChildren2, @MappedRefNo, @MappedGuestNo, @ExpireDate, @ControlCode, @SideHouse, @CancellationPercent, @Currency, @DiscountCustomerType, @TurnawayNumber, @CallSetInterface, @PassPortNumber,
@PaysChildren1, @PaysChildren2, @MappedRefNo, @MappedGuestNo, @ExpireDate, @ControlCode, @SideHouse, @CancellationPercent, @Currency, @DiscountCustomerType, @CallSetInterface, @PassPortNumber,
@GuestIsSmoker, @RoomKey, @BedAndBreakfast, @Project, @FK_ReservationGroup)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ReservationRoomLine + ReservationRoomLineXTab
DECLARE @tmpLineNumber INT
DECLARE @OldLineNumber int --AV 240902

DECLARE @BOOKEDSUBTYPE CHAR (10)
DECLARE @RoomNumber INT
DECLARE @NumberOfRooms SMALLINT
DECLARE @ScreenLine SMALLINT
DECLARE @NameListSetting TINYINT
DECLARE @NameListCreateFlag BIT

DECLARE #CopyResCur CURSOR FORWARD_ONLY FOR

  SELECT Linenumber,BookedTypeNumber, BOOKEDSUBTYPE, RoomNumber, NumberOfRooms, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, Adults, Children1, Children2, ProductNumber, Day2, ProductNumber2, Day3, ProductNumber3, Day4, ProductNumber4, Price, TotalPrice, PriceOverwritten, ScreenLine,
                 PriceOverwritten2, PriceOverwritten3, PriceOverwritten4, FirstPrice1, FirstPrice2, FirstPrice3, FirstPrice4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4, SplitBillNumber1, SplitBillNumber2, SplitBillNumber3, SplitBillNumber4, NameListSetting, NameListCreateFlag, FK_ReservationGroup
  FROM ReservationRoomLine WITH (NOLOCK) WHERE LineNumber IN (SELECT LineNumber FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=@OldGuestNumber)

OPEN #CopyResCur

FETCH NEXT FROM #CopyResCur
INTO @oldLineNumber,@BookedTypeNumber, @BOOKEDSUBTYPE, @RoomNumber, @NumberOfRooms, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @Adults, @Children1, @Children2, @ProductNumber, @Day2, @ProductNumber2, @Day3, @ProductNumber3, @Day4, @ProductNumber4, @Price, @TotalPrice,
          @PriceOverwritten, @ScreenLine, @PriceOverwritten2, @PriceOverwritten3, @PriceOverwritten4, @FirstPrice1, @FirstPrice2, @FirstPrice3, @FirstPrice4, @TotalPrice1, @TotalPrice2, @TotalPrice3, @TotalPrice4, @SplitBillNumber1, @SplitBillNumber2, @SplitBillNumber3, @SplitBillNumber4, @NameListSetting, @NameListCreateFlag, 
		  @FK_ReservationGroup

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF @NewReferenceNumber=@OldReferenceNumber
    SET @tmpLineNumber=NULL
  ELSE
  BEGIN
    DECLARE @tmpGuestNumber INT
    -- NOTE: NewGuestNumber and OldGuestNumber will be the same when booking a group under a new referencenumber!
--    SELECT TOP 1 @tmpGuestNumber = GuestNumber FROM ReservationRoomLineXTab WHERE ReferenceNumber=@OldReferenceNumber AND LineNumber=(SELECT LineNumber FROM ReservationRoomLineXTab (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=@OldGuestNumber) ORDER BY GuestNumber
    SELECT TOP 1 @tmpGuestNumber = GuestNumber FROM ReservationRoomLineXTab WHERE ReferenceNumber=@OldReferenceNumber AND LineNumber IN (SELECT LineNumber FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=@OldGuestNumber) ORDER BY GuestNumber

    IF @NewGuestNumber > 0  -- JP 20020924 we have to make sure that main res with more than one line is also rebooked/copied correctly
      SELECT @tmpLineNumber=LineNumber FROM ReservationRoomLineXTab WHERE ReferenceNumber=@NewReferenceNumber AND GuestNumber=@tmpGuestNumber
    ELSE  -- JP 20020924
      SET @tmpLineNumber = NULL  -- JP 20020924
  END

  IF @tmpLineNumber IS NULL
  BEGIN

    IF @CurrentRoomLineNumber = 0
    BEGIN
      EXEC GetNextNumber 'RESERVATIONROOMLINEIDENTITY', 1, @NewLineNumber OUTPUT
    END
    ELSE
    BEGIN
      SET @NewLineNumber = @CurrentRoomLineNumber
      SET @CurrentRoomLineNumber = @CurrentRoomLineNumber + 1
    END

    IF @ArrivalDate < @SystemDate
    BEGIN
      SET @DepartureDate = CONVERT (CHAR (8), CONVERT (DATETIME, @DepartureDate, 112)-CONVERT (DATETIME, @ArrivalDate, 112)+@SystemDate, 112)
      SET @ArrivalDate = CONVERT (CHAR (8), @SystemDate, 112)
      SET @RemoveRoomNumbers = 1
    END

    IF (@Status < 6) OR (@RemoveRoomNumbers = 1)
--      SET @RoomNumber=0  -- We need to remove roomnumber so we don't create double bookings
      SET @RoomNumber=NULL  -- We need to remove roomnumber so we don't create double bookings

print '@NewLineNumber='
print @NewLineNumber

    UPDATE ReservationRoomLine SET RebookedUnderLineNumber=@NewLineNumber WHERE LineNumber=@OldLineNumber --AV 240902
    -- ReservationRoomLine
    INSERT INTO ReservationRoomLine (LineNumber, BookedTypeNumber, BOOKEDSUBTYPE, RoomNumber, NumberOfRooms, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, Adults, Children1, Children2, ProductNumber, Day2, ProductNumber2, Day3, ProductNumber3, Day4, ProductNumber4, Price, TotalPrice,
    PriceOverwritten, ScreenLine, PriceOverwritten2, PriceOverwritten3, PriceOverwritten4, FirstPrice1, FirstPrice2, FirstPrice3, FirstPrice4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4, SplitBillNumber1, SplitBillNumber2, SplitBillNumber3, SplitBillNumber4, NameListSetting, NameListCreateFlag, FK_ReservationGroup) 
	VALUES (@NewLineNumber, @BookedTypeNumber, @BOOKEDSUBTYPE, @RoomNumber, @NumberOfRooms, @ArrivalDate,
    @ArrivalTime, @DepartureDate, @DepartureTime, @Adults, @Children1, @Children2, @ProductNumber, @Day2, @ProductNumber2, @Day3, @ProductNumber3, @Day4, @ProductNumber4, @Price, @TotalPrice, @PriceOverwritten, @ScreenLine, @PriceOverwritten2, @PriceOverwritten3, @PriceOverwritten4,
    @FirstPrice1, @FirstPrice2, @FirstPrice3, @FirstPrice4, @TotalPrice1, @TotalPrice2, @TotalPrice3, @TotalPrice4, @SplitBillNumber1, @SplitBillNumber2, @SplitBillNumber3, @SplitBillNumber4, @NameListSetting, @NameListCreateFlag, @FK_ReservationGroup) 
  END
  ELSE
  BEGIN
    IF (@Status < 6) OR (@RemoveRoomNumbers = 1)
    BEGIN
--      SET @RoomNumber=0  -- We need to remove roomnumber so we don't create double bookings
      SET @RoomNumber=null  -- We need to remove roomnumber so we don't create double bookings
      UPDATE ReservationRoomLine SET RoomNumber=@RoomNumber WHERE LineNumber=@tmpLineNumber
    END

    SET @NewLineNumber=@tmpLineNumber
  END
 
  -- ReservationRoomLineXtab
  INSERT INTO ReservationRoomLineXTab (ReferenceNumber, GuestNumber, LineNumber) VALUES (@NewReferenceNumber, @NewGuestNumber, @NewLineNumber) 

  FETCH NEXT FROM #CopyResCur
  INTO @OldLineNumber,@BookedTypeNumber, @BOOKEDSUBTYPE, @RoomNumber, @NumberOfRooms, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @Adults, @Children1, @Children2, @ProductNumber, @Day2, @ProductNumber2, @Day3, @ProductNumber3, @Day4, @ProductNumber4, @Price, @TotalPrice,
            @PriceOverwritten, @ScreenLine, @PriceOverwritten2, @PriceOverwritten3, @PriceOverwritten4, @FirstPrice1, @FirstPrice2, @FirstPrice3, @FirstPrice4, @TotalPrice1, @TotalPrice2, @TotalPrice3, @TotalPrice4, @SplitBillNumber1, @SplitBillNumber2, @SplitBillNumber3, @SplitBillNumber4, @NameListSetting, @NameListCreateFlag, @FK_ReservationGroup
END


CLOSE #CopyResCur
DEALLOCATE #CopyResCur

--moved down! EXEC UpdateArrivalAndDeparture2 @NewReferenceNumber, @NewGuestNumber

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ReservationAddress + ReservationAddressXTab
DECLARE @NewAddressNumber INT
DECLARE @AddressType SMALLINT
DECLARE @Title VARCHAR (50)
DECLARE @Initial CHAR (10)
DECLARE @FirstName VARCHAR (50)
DECLARE @LastName VARCHAR (50)
DECLARE @Address1 VARCHAR (50)
DECLARE @Address2 VARCHAR (50)
DECLARE @Address3 VARCHAR (50),
@CompanyForm VARCHAR(15)
DECLARE @CountryCode VARCHAR (3)
DECLARE @ZipCode CHAR (10)
DECLARE @City VARCHAR (50)
DECLARE @Phone VARCHAR (30)
DECLARE @Fax VARCHAR (30)
DECLARE @Email VARCHAR (100)
DECLARE @AttentionName VARCHAR (50)
DECLARE @PROVISIONTYPE SMALLINT
DECLARE @PROVISIONRATE MONEY
DECLARE @DISCOUNTRATE MONEY
DECLARE @RoomDiscountRate MONEY
DECLARE @MeetingDiscountRate MONEY
DECLARE @HallDiscountRate MONEY
DECLARE @PHONEDISCOUNTRATE MONEY
DECLARE @LINK INT
DECLARE @PaymentAfterMonth SMALLINT
DECLARE @PaymentAfterDays SMALLINT
DECLARE @PaymentTypeWareNumber INT
DECLARE @CountryNumber INT
DECLARE @Cell VARCHAR (50)
DECLARE @BuyersOrderID VARCHAR (15)
DECLARE @BuyersAccount VARCHAR (15),
@ContactPersonCustomerNumber INT

DECLARE #CopyResCur CURSOR FORWARD_ONLY FOR

  SELECT AddressType, Title, Initial, FirstName, LastName, Address1, Address2, Address3, CountryCode, ZipCode, City, Phone, Fax, Email, AttentionName, ProvisionType, ProvisionRate, DiscountRate, RoomDiscountRate, MeetingDiscountRate, HallDiscountRate, PhoneDiscountRate, Link, PaymentAfterMonth, PaymentAfterDays, PaymentTypeWareNumber, CountryNumber, Cell, CompanyForm, BuyersOrderID, BuyersAccount, ContactPersonCustomerNumber
  FROM ReservationAddress WITH (NOLOCK) WHERE AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=@OldGuestNumber)

OPEN #CopyResCur

FETCH NEXT FROM #CopyResCur
INTO @AddressType, @Title, @Initial, @FirstName, @LastName, @Address1, @Address2, @Address3, @CountryCode, @ZipCode, @City, @Phone, @Fax, @Email, @AttentionName, @ProvisionType, @ProvisionRate, @DiscountRate, @RoomDiscountRate, @MeetingDiscountRate, @HallDiscountRate, @PhoneDiscountRate, @Link,
          @PaymentAfterMonth, @PaymentAfterDays, @PaymentTypeWareNumber, @CountryNumber, @Cell, @CompanyForm, @BuyersOrderID, @BuyersAccount, @ContactPersonCustomerNumber

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF @CurrentAddressNumber = 0
  BEGIN
--      BEGIN TRAN
--  UPDATE ReservationAddressIdentity SET @NewAddressNumber = IdentityField = IdentityField + 1  -- New linenumber
--      COMMIT TRAN
      EXEC GetNextNumber 'ReservationAddressIdentity', 1, @NewAddressNumber OUTPUT
  END
  ELSE
  BEGIN
    SET @NewAddressNumber = @CurrentAddressNumber
    SET @CurrentAddressNumber = @CurrentAddressNumber + 1
  END

print 'new addressnumber'
print @NewAddressNumber


  -- ReservationAddress
  INSERT INTO ReservationAddress (AddressNumber, AddressType, Title, Initial, FirstName, LastName, Address1, Address2, Address3, CountryCode, ZipCode, City, Phone, Fax, Email, AttentionName, ProvisionType, ProvisionRate, DiscountRate, RoomDiscountRate, MeetingDiscountRate, HallDiscountRate, PhoneDiscountRate, Link, PaymentAfterMonth, PaymentAfterDays, PaymentTypeWareNumber, CountryNumber, Cell, CompanyForm, BuyersOrderID, BuyersAccount, ContactPersonCustomerNumber)
  VALUES (@NewAddressNumber, @AddressType, @Title, @Initial, @FirstName, @LastName, @Address1, @Address2, @Address3, @CountryCode, @ZipCode, @City, @Phone, @Fax, @Email, @AttentionName, @ProvisionType, @ProvisionRate, @DiscountRate, @RoomDiscountRate, @MeetingDiscountRate, @HallDiscountRate, @PhoneDiscountRate, @Link,@PaymentAfterMonth, @PaymentAfterDays, @PaymentTypeWareNumber, @CountryNumber, @Cell, @CompanyForm, @BuyersOrderID, @BuyersAccount, @ContactPersonCustomerNumber)

  -- ReservationAddressXTab
  INSERT INTO ReservationAddressXTab (ReferenceNumber, GuestNumber, AddressNumber, AddressTypeNumber) VALUES (@NewReferenceNumber, @NewGuestNumber, @NewAddressNumber, @AddressType) 

  FETCH NEXT FROM #CopyResCur
  INTO @AddressType, @Title, @Initial, @FirstName, @LastName, @Address1, @Address2, @Address3, @CountryCode, @ZipCode, @City, @Phone, @Fax, @Email, @AttentionName, @ProvisionType, @ProvisionRate, @DiscountRate, @RoomDiscountRate, @MeetingDiscountRate, @HallDiscountRate, @PhoneDiscountRate, @Link,
            @PaymentAfterMonth, @PaymentAfterDays, @PaymentTypeWareNumber, @CountryNumber, @Cell, @CompanyForm, @BuyersOrderID, @BuyersAccount, @ContactPersonCustomerNumber
END

CLOSE #CopyResCur
DEALLOCATE #CopyResCur

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ResevationConferenceLine + ReservationMeetingXTab
DECLARE @PackageNumber INT
DECLARE @ConferenceRoomSetupNumber INT
DECLARE @SchemePattern TINYINT
DECLARE @SchemeWeekDays CHAR (10)
DECLARE @LinkLineNumber INT
DECLARE @ConfLineNumber INT
DECLARE @TableBooking INT
DECLARE @ChairNumber INT
DECLARE @TableStatus INT
DECLARE @Smoker INT
DECLARE @HallLinkNumber INT
DECLARE @GuestNo INT
DECLARE @MainResArrival DATETIME
DECLARE @NumberOfDays INT
SELECT @MainResArrival = CONVERT(DATETIME, ArrivalDate,112) FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber= @OldReferenceNumber AND GuestNumber = 0


IF @OldGuestNumber = 0
BEGIN

   DECLARE #CopyResCur CURSOR FORWARD_ONLY FOR

    SELECT ReservationConfLineNumber, ReferenceNumber, BookedConferenceRoomTypeNumber, NumberOfRooms, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, ProductNumber, PackageNumber, Price, TotalPrice, PriceOverWritten, ConferenceRoomSetupNumber, ConferenceRoomNumber, Adults, Children1, ScreenLine,
                   SchemePattern, SchemeWeekDays, LinkLineNumber, TableBooking, ChairNumber,TableStatus,Smoker,HallLinkNumber,GuestNo
    FROM ReservationConferenceLine WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber
    ORDER BY Screenline --AV 20040721

  OPEN #CopyResCur

  FETCH NEXT FROM #CopyResCur
  INTO @ConfLineNumber, @ReferenceNumber, @BookedTypeNumber, @NumberOfRooms, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @ProductNumber, @PackageNumber, @Price, @TotalPrice, @PriceOverWritten, @ConferenceRoomSetupNumber, @RoomNumber, @Adults, @Children1, @ScreenLine,
            @SchemePattern, @SchemeWeekDays, @LinkLineNumber,@TableBooking,@ChairNumber,@TableStatus,@Smoker,@HallLinkNumber,@GuestNo

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    IF @CurrentRoomLineNumber = 0
    BEGIN
--      BEGIN TRAN
--      UPDATE ReservationRoomLineIdentity SET @NewLineNumber = IdentityField = IdentityField + 1  -- New linenumber
--      COMMIT TRAN
      EXEC GetNextNumber 'ReservationRoomLineIdentity', 1, @NewLineNumber OUTPUT
    END
    ELSE
    BEGIN
      SET @NewLineNumber = @CurrentRoomLineNumber
      SET @CurrentRoomLineNumber = @CurrentRoomLineNumber + 1
    END

    IF (@Status < 6) --OR (@RemoveRoomNumbers = 1)
--      SET @RoomNumber=0  -- We need to remove roomnumber so we don't create dobble bookings
      SET @RoomNumber=NULL  -- We need to remove roomnumber so we don't create dobble bookings

    IF @ArrivalDate < @SystemDate
    BEGIN
--      SET @DepartureDate = CONVERT (CHAR (8), CONVERT (DATETIME, @DepartureDate, 112)-CONVERT (DATETIME, @ArrivalDate, 112)+@SystemDate, 112)
--      SET @ArrivalDate = CONVERT (CHAR (8), @SystemDate, 112)
      SET @NumberOfDays = CONVERT(INT,  CONVERT (DATETIME, @DepartureDate, 112)-CONVERT (DATETIME, @ArrivalDate, 112))
      SET @Arrivaldate =CONVERT(CHAR(8), @SystemDate + (CONVERT(DATETIME,@Arrivaldate,112) - @MainResArrival),112) 
      SET @DepartureDate = CONVERT (CHAR (8), CONVERT (DATETIME, @ArrivalDate, 112) + @NumberOfDays, 112)
    END


    UPDATE ReservationConferenceLine SET RebookedUnderLineNumber=@NewLineNumber WHERE ReservationConfLineNumber=@ConfLineNumber

    If IsNull(@LinkLineNumber,0) > 0 --AV 20040721
    begin 
      if exists (select ReservationConfLineNumber from reservationConferenceLine WITH (nolock)  where referenceNumber=@NewReferenceNumber and IsNull(SchemePattern,0) > 0)
        select @LinkLineNumber = ReservationConfLineNumber from reservationConferenceLine WITH (nolock)  where referenceNumber=@NewReferenceNumber and IsNull(SchemePattern,0) > 0
    end

     if IsNull(@TableStatus,-1 ) >= 5
       Select @TableStatus =CONVERT (INT, '0'+KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'DefaultResStatusTables'

    INSERT INTO ReservationConferenceLine (ReservationConfLineNumber, ReferenceNumber, BookedConferenceRoomTypeNumber, NumberOfRooms, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, ProductNumber, PackageNumber, Price, TotalPrice, PriceOverWritten, ConferenceRoomSetupNumber,
                                                                         ConferenceRoomNumber, Adults, Children1, ScreenLine, SchemePattern, SchemeWeekDays, LinkLineNumber,TableBooking, ChairNumber,TableStatus,Smoker,HallLinkNumber,GuestNo)
    VALUES (@NewLineNumber, @NEWReferenceNumber, @BookedTypeNumber, @NumberOfRooms, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @ProductNumber, @PackageNumber, @Price, @TotalPrice, @PriceOverWritten, @ConferenceRoomSetupNumber, @RoomNumber, @Adults, @Children1,
                    @ScreenLine, @SchemePattern, @SchemeWeekDays, @LinkLineNumber,@TableBooking,@ChairNumber,@TableStatus,@Smoker,@HallLinkNumber,@GuestNo) 

    FETCH NEXT FROM #CopyResCur
    INTO @ConfLineNumber, @ReferenceNumber, @BookedTypeNumber, @NumberOfRooms, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @ProductNumber, @PackageNumber, @Price, @TotalPrice, @PriceOverWritten, @ConferenceRoomSetupNumber, @RoomNumber, @Adults, @Children1, @ScreenLine,
              @SchemePattern, @SchemeWeekDays, @LinkLineNumber,@TableBooking,@ChairNumber,@TableStatus,@Smoker,@HallLinkNumber,@GuestNo
  END
 CLOSE #CopyResCur
 DEALLOCATE #CopyResCur

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 --Arrangement manual lines
--

  IF @OldGuestNumber = 0 and (@OldReferenceNumber <> @NewReferenceNumber) --AND (@NewReferenceNumberOverwrite = 0)
  BEGIN
   DECLARE @ArrNumber INT
   DECLARE @Date char (8)
   DECLARE @PacketItemNumber int
   DECLARE @Time char(5)
   DECLARE @PriceOverWrite real
   DECLARE @ArrTotalPrice real
   DECLARE @Pieces int
   DECLARE @WareText varchar (255)
--   DECLARE @ArrDepartments VARCHAR(32)
   DECLARE @PK_Department INT
   DECLARE @FK_Exdepartment int
   DECLARE @EXDepartment CHAR(1)
   DECLARE @FK_PacketChargeDepartment INT
   DECLARE @PlaceText Varchar(30)
   DECLARE @WareNumber int
   DECLARE @HallNumber int
   DECLARE @Note Varchar (8000)
   DECLARE @IdentityValue int

  DECLARE @OldMainResArrival datetime
  
  SELECT @OldMainResArrival=CONVERT (DATETIME, Arrivaldate) FROM Reservations WITH (NOLOCK) WHERE referencenumber=@OldReferencenumber and guestnumber=0

   DECLARE ARRCUR  CURSOR FORWARD_ONLY FOR --select all manual arrangementlines and copy them to new reservation
   SELECT ArrangementLines.ArrangementLineNumber, Arrangementlines.Date,PacketItemNumber,Time,Price,TotalPrice,Pieces,WareText,FK_Exdepartment, /*ExDepartment,*/ PlaceText,WareNumber,Note, FK_PacketChargeDepartment
   FROM ArrangementLines WITH (nolock) where arrangementlinenumber in (select arrangementlinenumber from arrangementreservationsxtab WITH (NOLOCK) where referencenumber= @OldReferencenumber) and arrangementlinenumber not in 
   (SELECT arrangementlinenumber FROM arrangementroomxtab WITH (NOLOCK))
   OPEN ARRCUR

   FETCH NEXT FROM ARRCUR
   INTO  @ArrNumber, @Date,@PacketItemNumber,@Time,@PriceoverWrite,@ArrTotalPrice,@Pieces,@WareText,@FK_Exdepartment, /*@ExDepartment,*/ @PlaceText,@WareNumber,@Note,@FK_PacketChargeDepartment
   WHILE (@@FETCH_STATUS <> -1)
   BEGIN
      if convert(datetime,@OldMainResArrival) <@SystemDate
       set @date =Convert(char, @SystemDate + ( Convert(datetime,@date) -@OldMainResArrival),112)   
      Insert into ArrangementLines (Date,PacketItemNumber,Time, Price, /*TotalPrice,*/Pieces,WareText, FK_Exdepartment, /*ExDepartment,*/ PlaceText,WareNumber,note, FK_PacketChargeDepartment) Values  (@Date,@PacketItemNumber,@Time, @PriceOverWrite, /*@ArrTotalPrice,*/@Pieces,@Waretext, @FK_Exdepartment, /*@ExDepartment,*/ @PlaceText,@WareNumber,@note,@FK_PacketChargeDepartment)  
     select @identityvalue=@@identity from ArrangementLines WITH (NOLOCK)
     Insert into ArrangementReservationsXtab values (@IdentityValue,@NewReferencenumber,0,0)

     -- 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,@PriceoverWrite,@ArrTotalPrice,@Pieces,@WareText,@FK_Exdepartment, /*@ExDepartment,*/ @PlaceText,@WareNumber,@Note,@FK_PacketChargeDepartment
   END
  END
  Close ArrCur
  DeAllocate ArrCur
--
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

END

 -- ReservationMeetingXTab - copy old person on hall namelist
IF @OldGuestNumber > 0
BEGIN
  SELECT @Adults=Adults, @Children1=Children, @PaysAdults=PaysAdults, @PaysChildren1=PaysChildren, @NewLineNumber=ReservationConfLineNumber FROM ReservationMeetingXTab WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=@OldGuestNumber
  IF @@rowcount = 1
  BEGIN
    IF @NewReferenceNumber <> @OldReferenceNumber
    BEGIN
      SELECT @tmpLineNumber=RebookedUnderLineNumber FROM ReservationConferenceLine WHERE ReservationConfLineNumber=@NewLineNumber
      INSERT INTO ReservationMeetingXTab (ReferenceNumber, GuestNumber, ReservationConfLineNumber, Adults, Children, PaysAdults, PaysChildren) VALUES (@NewReferenceNumber, @NewGuestNumber, @tmpLineNumber, @Adults, @Children1, @PaysAdults, @PaysChildren1) 
    END
    ELSE
      INSERT INTO ReservationMeetingXTab (ReferenceNumber, GuestNumber, ReservationConfLineNumber, Adults, Children, PaysAdults, PaysChildren) VALUES (@NewReferenceNumber, @NewGuestNumber, @NewLineNumber, @Adults, @Children1, @PaysAdults, @PaysChildren1) 
  END
END

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ResevationNotes
DECLARE @NotePage SMALLINT
DECLARE @Departments VARCHAR (30)
DECLARE @LISTS VARCHAR (30) 
DECLARE @Text VARCHAR (8000)

DECLARE #CopyResCur CURSOR FORWARD_ONLY FOR
  SELECT NotePage, Departments, LISTS, Text
  FROM ReservationNotes WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=@OldGuestNumber

OPEN #CopyResCur

FETCH NEXT FROM #CopyResCur

INTO @NotePage, @Departments, @LISTS, @Text

WHILE (@@FETCH_STATUS <> -1)
BEGIN

 INSERT INTO ReservationNotes (ReferenceNumber, GuestNumber, NotePage, Departments, LISTS, Text)
 VALUES (@NewReferenceNumber, @NewGuestNumber, @NotePage, @Departments, @LISTS, @Text)

  FETCH NEXT FROM #CopyResCur
  INTO @NotePage, @Departments, @LISTS, @Text

END

CLOSE #CopyResCur
DEALLOCATE #CopyResCur

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

EXEC UpdateArrivalAndDeparture2 @NewReferenceNumber, @NewGuestNumber

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- CONTACTS (FOLLOWUP)
--ReservationFollowUpXTab + FollowUp

DECLARE @NewFollowUpNumber INT
DECLARE @SystemDateTime DATETIME

SELECT @SystemDateTime = CONVERT (DATETIME, @SystemDate, 112)+CONVERT (CHAR, GetDate(), 108)

-- INSERT INTO LOG OLD RES. WRITE THAT IT HAS BEEN REBOOKED UNDER NEW RES NO
set @Text = 'Rebooked under '+Convert (VARCHAR, @NewReferenceNumber)+'/'+Convert (VARCHAR, @NewGuestNumber)
INSERT INTO FollowUp ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType)
--VALUES (@SystemDate, 5, 12, 3, 'Rebooked under '+Convert (VARCHAR, @NewReferenceNumber)+'/'+Convert (VARCHAR, @NewGuestNumber), @SystemDateTime, @UserIDNumber, 2)
VALUES (@SystemDateTime, 5, 12, 3, @Text, @SystemDateTime, @UserIDNumber, 2)
SELECT @NewFollowUpNumber = @@identity
INSERT INTO ReservationFollowUpXTab (ReferenceNumber, GuestNumber, FollowUpNumber) VALUES (@OldReferenceNumber, @OldGuestNumber, @NewFollowUpNumber)

--exec WriteToLog @OldReferenceNumber ,@OldGuestNumber, @Note12, 2    -- no can do.. personelxtab and nightaudit user!?

-- INSERT INTO LOG NEW RES. WRITE THAT IT HAS BEEN REBOOKED FROM OLD RES NO
set @Text = 'Rebooked from '+Convert (VARCHAR, @OldReferenceNumber)+'/'+Convert (VARCHAR, @OldGuestNumber)
INSERT INTO FollowUp ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType)
--VALUES (@SystemDate, 5, 12, 3, 'Rebooked from '+Convert (VARCHAR, @OldReferenceNumber)+'/'+Convert (VARCHAR, @OldGuestNumber), @SystemDateTime, @UserIDNumber, 1)
VALUES (@SystemDateTime, 5, 12, 3, @Text, @SystemDateTime, @UserIDNumber, 1)
SELECT @NewFollowUpNumber = @@identity
INSERT INTO ReservationFollowUpXTab (ReferenceNumber, GuestNumber, FollowUpNumber) VALUES (@NewReferenceNumber, @NewGuestNumber, @NewFollowUpNumber)

--exec WriteToLog @NewReferenceNumber ,@NewGuestNumber, @Note12, 1   -- no can do.. personelxtab and nightaudit user!?
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SET @FirstAddressNumber = @CurrentAddressNumber  -- for use with copyreservationgroup so we are not given the same numbers for each line!
SET @FirstRoomLineNumber = @CurrentRoomLineNumber

--SET lock_timeout 0
GO
