CREATE PROCEDURE dbo.CopyReservationToDB @DestDB VARCHAR(50), @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 20070206 Based on old CopyReservation

    Last updated:
      20070726 JP: EXT: removed NameListCreated field
      20070517 JP: BUG: wrong column used for mapping roomtype
      20070410 JP: EXT: Use the new ArrangementLineDepartmentXTab when copying arrangementlines 
      20070215 JP: BUG: fix for copy when same referencenumber in both dbs. Also fixed copy of arr

    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!

    LIMITATIONS: Arrangement is not copied
*/

DECLARE
@AltResNo VARCHAR(20)

SELECT @AltResNo=AltResNo FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=@OldGuestNumber

IF (@AltResNo = '') OR (@AltResNo = NULL)
BEGIN
  IF @DestDB <> ''
    SET @DestDB = '['+@DestDB+'].'

DECLARE 
@SystemDate DATETIME,
@CurrentAddressNumber INT,
@CurrentRoomLineNumber INT,
@NewLineNumber INT,
@MainResStatus INT,
@StayInSameGroup INT,
@RemoveRoomNumbers BIT,
@ReferenceNumber INT,
@GuestNumber INT,
@HotelNumber SMALLINT,
@ReservationType TINYINT,
@AllotmentNumber INT,
@S NVARCHAR(2000),
@P NVARCHAR(200)

--SELECT @SystemDate=CONVERT (DATETIME, SystemDate, 112) FROM System (NOLOCK) WHERE Number=1
SET @S= N'SELECT @SystemDate=CONVERT (DATETIME, SystemDate, 112) FROM '+@DestDB+'dbo.System WITH (NOLOCK) WHERE Number=1'
SET @P = N'@SystemDate DATETIME OUTPUT'
EXEC sp_executesql @S, @P, @SystemDate OUTPUT

SET @CurrentAddressNumber = @FirstAddressNumber
SET @CurrentRoomLineNumber = @FirstRoomLineNumber
SET @StayInSameGroup = 0

IF @OldGuestNumber > 0
BEGIN
  IF ISNULL(@NewReferenceNumberOverwrite, 0) = 0 -- used for groups
  BEGIN
    SELECT @MainResStatus=Status FROM dbo.Reservations WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=0 -- OK source
    IF @MainResStatus > 5 -- we cannot rebook a subres because mainres is NOX
    BEGIN
      SET @NewReferenceNumber=-1
      SET @NewGuestNumber=-1
      RETURN -- Dangerous!!!
    END
    SELECT @NewGuestNumber=MAX (GuestNumber)+1 FROM dbo.Reservations WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber  -- New guestnumber but keep same group referencenumber -- OK source
    SET @NewReferenceNumber=@OldReferenceNumber  -- Keep old referencenumber
  END
  ELSE IF @NewReferenceNumberOverwrite > 0
  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 (NOLOCK) WHERE ReferenceNumber=@NewReferenceNumberOverwrite AND GuestNumber=0
    SET @S= N'SELECT @ReferenceNumber=ReferenceNumber FROM '+@DestDB+'dbo.Reservations WITH (NOLOCK) WHERE ReferenceNumber='+CONVERT(VARCHAR, @NewReferenceNumberOverwrite)+' AND GuestNumber=0'

    SET @P = N'@ReferenceNumber INT OUTPUT'
    EXEC sp_executesql @S, @P, @ReferenceNumber OUTPUT

    IF @ReferenceNumber=@NewReferenceNumberOverwrite -- did row exist?
    BEGIN
      -- Check if OldGuestNumber exists. If it does then we need to find a new one!
--      SELECT @GuestNumber=@OldGuestNumber FROM Reservations (NOLOCK) WHERE ReferenceNumber=@NewReferenceNumberOverwrite AND GuestNumber=@OldGuestNumber
      SET @S= N'SELECT @GuestNumber=GuestNumber FROM '+@DestDB+'dbo.Reservations WITH (NOLOCK) WHERE ReferenceNumber='+CONVERT(VARCHAR, @NewReferenceNumberOverwrite)+' AND GuestNumber='+CONVERT(VARCHAR, @OldGuestNumber)
      SET @P = N'@GuestNumber INT OUTPUT'
      EXEC sp_executesql @S, @P, @GuestNumber OUTPUT

      IF @GuestNumber=@OldGuestNumber -- did row exist?
      BEGIN
--        SELECT @NewGuestNumber=MAX (GuestNumber)+1 FROM Reservations (NOLOCK) WHERE ReferenceNumber=@NewReferenceNumberOverwrite  -- New guestnumber but keep same group referencenumber
        SET @S= N'SELECT @NewGuestNumber=MAX (GuestNumber)+1 FROM '+@DestDB+'dbo.Reservations WITH (NOLOCK) WHERE ReferenceNumber='+CONVERT(VARCHAR, @NewReferenceNumberOverwrite) -- New guestnumber but keep same group referencenumber
        SET @P = N'@NewGuestNumber INT OUTPUT'
        EXEC sp_executesql @S, @P, @NewGuestNumber OUTPUT
      END
      ELSE
        SET @NewGuestNumber=@OldGuestNumber
    END
    ELSE
      SET @NewGuestNumber=0
    SET @NewReferenceNumber=@NewReferenceNumberOverwrite  -- Keep old referencenumber
  END
END
ELSE IF @OldGuestNumber = 0
BEGIN
  IF ISNULL(@NewReferenceNumberOverwrite, 0) = 0
  BEGIN
--    EXEC GetNextNumber 'RESERVATIONIDENTITY', 1, @NewReferenceNumber OUTPUT
    SET @S= N'EXEC '+@DestDB+'dbo.GetNextNumber ''RESERVATIONIDENTITY'', 1, @NewReferenceNumber OUTPUT'
    SET @P = N'@NewReferenceNumber INT OUTPUT'
    EXEC sp_executesql @S, @P, @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 --
--IF @OldReferenceNumber <> @NewReferenceNumber
BEGIN
--  SELECT @ReservationType=ReservationType FROM ReservationMain (NOLOCK) WHERE ReferenceNumber=@NewReferenceNumber
  SET @S= N'SELECT @ReservationType=ReservationType FROM '+@DestDB+'dbo.ReservationMain WITH (NOLOCK) WHERE ReferenceNumber='+CONVERT(VARCHAR, @NewReferenceNumber)
  SET @P = N'@ReservationType TINYINT OUTPUT'
  EXEC sp_executesql @S, @P, @ReservationType OUTPUT

  IF @ReservationType IS NULL  -- test if new main res is already there
  BEGIN
    SELECT @HotelNumber=HotelNumber, @ReservationType=ReservationType, @AllotmentNumber=AllotmentNumber FROM ReservationMain WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber
--    INSERT INTO ReservationMain (ReferenceNumber, HotelNumber, ReservationType, AllotmentNumber) VALUES (@NewReferenceNumber, @HotelNumber, @ReservationType, @AllotmentNumber)

    EXEC dbo.MapValue @DestDB, 'dbo.Allotments', 'AllotmentNumber', 'Name', @AllotmentNumber OUTPUT, 0

    SET @S= N'INSERT INTO '+@DestDB+'dbo.ReservationMain (ReferenceNumber, HotelNumber, ReservationType, AllotmentNumber) VALUES ('+ 
      CONVERT(VARCHAR, @NewReferenceNumber)+', '+ISNULL(CONVERT(VARCHAR, @HotelNumber), 0)+', '+CONVERT(VARCHAR, @ReservationType)+', '+ ISNULL(CONVERT(VARCHAR,@AllotmentNumber), 'NULL')+')' 

    EXEC sp_executesql @S
  END
END

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Reservations --
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 @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 @LastCurrencyRate money
DECLARE @DiscountCustomerType tinyint
DECLARE @TurnawayNumber int
DECLARE @CallSetInterface bit
DECLARE @PassPortNumber VARCHAR (25),
@GuestIsSmoker tinyint,
@RoomKey tinyint, 
@BedAndBreakfast tinyint,
@Project SMALLDATETIME

SELECT @AltResNo=AltResNo, @Status=Status, @MARKETSEGMENT=MARKETSEGMENT, @POINTOFSALE=POINTOFSALE, @GUESTTYPE=GUESTTYPE, @PURPOSECODE=PURPOSECODE, @CANCELCODE=CANCELCODE, @CREDITMAX=CREDITMAX,
@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, @LastCurrencyRate= LastCurrencyRate, @DiscountCustomerType=DiscountCustomerType, @TurnawayNumber=TurnawayNumber, @CallSetInterface=CallSetInterface, @PassPortNumber=PassPortNumber,
@GuestIsSmoker=GuestIsSmoker, @RoomKey=RoomKey, @BedAndBreakfast=BedAndBreakfast, @Project=Project
FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=@OldGuestNumber

SELECT @UserID=UserIDCode FROM Personel WITH (NOLOCK) WHERE UserIDNumber=@UserIDNumber -- OK. just a redundant string

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

  SET @S= N'SELECT @Status = CONVERT (INT, ''0''+KeyValue) FROM '+@DestDB+'dbo.HotelSetup WITH (NOLOCK) WHERE SearchKey=''DefaultResStatus'''
  SET @P = N'@Status TINYINT OUTPUT'
  EXEC sp_executesql @S, @P, @Status OUTPUT

  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

-- Map forreign keys
EXEC dbo.MapValue @DestDB, 'dbo.Segments', 'Number', 'Text', @MARKETSEGMENT OUTPUT
EXEC dbo.MapValue @DestDB, 'dbo.PointOfSales', 'Number', 'Text', @POINTOFSALE OUTPUT
EXEC dbo.MapValue @DestDB, 'dbo.GuestTypes', 'TypeNumber', 'Text', @GUESTTYPE OUTPUT
EXEC dbo.MapValue @DestDB, 'dbo.PurposeCodes', 'PurposeCodeNumber', 'Text', @PURPOSECODE OUTPUT
EXEC dbo.MapValue @DestDB, 'dbo.Ware', 'WareNumber', 'WareName', @METHODOFPAYMENT OUTPUT, 0
EXEC dbo.MapValue @DestDB, 'dbo.Products', 'ProductNumber', 'ProductName', @ProductNumber OUTPUT, 0
EXEC dbo.MapValue @DestDB, 'dbo.Products', 'ProductNumber', 'ProductName', @ProductNumber2 OUTPUT, 0
EXEC dbo.MapValue @DestDB, 'dbo.Products', 'ProductNumber', 'ProductName', @ProductNumber3 OUTPUT, 0
EXEC dbo.MapValue @DestDB, 'dbo.Products', 'ProductNumber', 'ProductName', @ProductNumber4 OUTPUT, 0
EXEC dbo.MapValue @DestDB, 'dbo.SplitBill', 'SplitBillNumber', 'Name', @SPLITBILL OUTPUT, 0
EXEC dbo.MapValue @DestDB, 'dbo.SplitBill', 'SplitBillNumber', 'Name', @SplitBillNumber1 OUTPUT, 0
EXEC dbo.MapValue @DestDB, 'dbo.SplitBill', 'SplitBillNumber', 'Name', @SplitBillNumber2 OUTPUT, 0
EXEC dbo.MapValue @DestDB, 'dbo.SplitBill', 'SplitBillNumber', 'Name', @SplitBillNumber3 OUTPUT, 0
EXEC dbo.MapValue @DestDB, 'dbo.SplitBill', 'SplitBillNumber', 'Name', @SplitBillNumber4 OUTPUT, 0

-- TODO: missing fields: GuestIsSmoker, RoomKey, BedAndBreakfast, Project     

SET @S = N'INSERT INTO '+@DestDB+'dbo.Reservations (ReferenceNumber, GuestNumber, AltResNo, Status, MARKETSEGMENT, POINTOFSALE, GUESTTYPE, PURPOSECODE, CANCELCODE, 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, LastCurrencyRate, DiscountCustomerType, TurnawayNumber, CallSetInterface, PassPortNumber,
GuestIsSmoker, RoomKey, BedAndBreakfast, Project)
VALUES ('+
CONVERT(VARCHAR,@NewReferenceNumber)+', '+
CONVERT(VARCHAR, @NewGuestNumber)+', '+
''''+ISNULL(@AltResNo, '')+''', '+
ISNULL(CONVERT(VARCHAR, @Status), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @MARKETSEGMENT), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @POINTOFSALE), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @GUESTTYPE), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @PURPOSECODE), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @CANCELCODE), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @CREDITMAX), 0)+', '+
''''+ISNULL(@CREDITCARDNUMBER, '')+''', '+
ISNULL(CONVERT(VARCHAR, @METHODOFPAYMENT), 'NULL')+', '+
''''+ISNULL(@VOUCHER, '')+''', '+
''''+ISNULL(@USERID, '')+''', '+
ISNULL(CONVERT(VARCHAR, @ArrivalDate), 0)+', '+

''''+ISNULL(CONVERT(VARCHAR, @ArrivalTime), 0)+''', '+
ISNULL(CONVERT(VARCHAR, @DepartureDate), 0)+', '+
''''+ISNULL(CONVERT(VARCHAR, @DepartureTime), 0)+''', '+
ISNULL(CONVERT(VARCHAR, @Adults), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @Children1), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @Children2), 'NULL')+', '+

ISNULL(CONVERT(VARCHAR, @ProductNumber), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @Day2), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @ProductNumber2), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @Day3), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @ProductNumber3), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @Day4), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @ProductNumber4), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @Price), 0)+', '+
ISNULL(CONVERT(VARCHAR, @TotalPrice), 0)+', '+
ISNULL(CONVERT(VARCHAR, @PriceOverwritten), 0)+', '+
ISNULL(CONVERT(VARCHAR, @SPLITBILL), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @PriceOverwritten2), 0)+', '+
ISNULL(CONVERT(VARCHAR, @PriceOverwritten3), 0)+', '+
ISNULL(CONVERT(VARCHAR, @PriceOverwritten4), 0)+', '+
ISNULL(CONVERT(VARCHAR, @FirstPrice1), 0)+', '+
ISNULL(CONVERT(VARCHAR, @FirstPrice2), 0)+', '+
ISNULL(CONVERT(VARCHAR, @FirstPrice3), 0)+', '+
ISNULL(CONVERT(VARCHAR, @FirstPrice4), 0)+', '+
ISNULL(CONVERT(VARCHAR, @TotalPrice1), 0)+', '+
ISNULL(CONVERT(VARCHAR, @TotalPrice2), 0)+', '+
ISNULL(CONVERT(VARCHAR, @TotalPrice3), 0)+', '+
ISNULL(CONVERT(VARCHAR, @TotalPrice4), 0)+', '+
ISNULL(CONVERT(VARCHAR, @SplitBillNumber1), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @SplitBillNumber2), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @SplitBillNumber3), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @SplitBillNumber4), 'NULL')+', '+
'NULL,'+ --ISNULL(CONVERT(VARCHAR, @FollowUpNumber), 'NULL')+', '+ -- Isn't copied
ISNULL(CONVERT(VARCHAR, @PaysAdults), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @PaysChildren1), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @PaysChildren2), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @MappedRefNo), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @MappedGuestNo), 'NULL')+', '+
''''+ISNULL(CONVERT(VARCHAR, @ExpireDate), '19800101')+''', '+
ISNULL(CONVERT(VARCHAR, @ControlCode), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @SideHouse), 0)+', '+
ISNULL(CONVERT(VARCHAR, @CancellationPercent), 0)+', '+
ISNULL(CONVERT(VARCHAR, @Currency), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @LastCurrencyRate), 100)+', '+
ISNULL(CONVERT(VARCHAR, @DiscountCustomerType), 'NULL')+', '+
'NULL,'+ --ISNULL(CONVERT(VARCHAR, @TurnawayNumber), 'NULL')+', '+ -- TODO
ISNULL(CONVERT(VARCHAR, @CallSetInterface), 0)+', '+
''''+ISNULL(@PassPortNumber, '')+''','+
ISNULL(CONVERT(VARCHAR, @GuestIsSmoker), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @RoomKey), 'NULL')+', '+
ISNULL(CONVERT(VARCHAR, @BedAndBreakfast), 'NULL')+', '+
''''+ISNULL(CONVERT(VARCHAR, @Project), '19800101')+''')'

EXEC sp_executesql @S
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 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
  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

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 IN 
      (SELECT LineNumber FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=@OldGuestNumber) ORDER BY GuestNumber

    SET @tmpLineNumber = NULL  -- JP 20020924
    IF @NewGuestNumber > 0  -- JP 20020924 we have to make sure that main res with more than one line is also rebooked/copied correctly
    BEGIN
--      SELECT @tmpLineNumber=LineNumber FROM ReservationRoomLineXTab WHERE ReferenceNumber=@NewReferenceNumber AND GuestNumber=@tmpGuestNumber
      SET @S= N'SELECT @tmpLineNumber=LineNumber FROM '+@DestDB+'dbo.ReservationRoomLineXTab WHERE ReferenceNumber='+CONVERT(VARCHAR, @NewReferenceNumber)+' AND GuestNumber='+CONVERT(VARCHAR, @tmpGuestNumber)
      SET @P = N'@tmpLineNumber INT OUTPUT'
      EXEC sp_executesql @S, @P, @tmpLineNumber OUTPUT
    END
  END

  IF @tmpLineNumber IS NULL
  BEGIN
    IF ISNULL(@CurrentRoomLineNumber, 0) = 0
    BEGIN
--      EXEC GetNextNumber 'RESERVATIONROOMLINEIDENTITY', 1, @NewLineNumber OUTPUT
      SET @S= N'EXEC '+@DestDB+'dbo.GetNextNumber ''RESERVATIONROOMLINEIDENTITY'', 1, @NewLineNumber OUTPUT'
      SET @P = N'@NewLineNumber INT OUTPUT'
      EXEC sp_executesql @S, @P, @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=NULL  -- We need to remove roomnumber so we don't create double bookings

    -- Map forreign keys
--    EXEC dbo.MapValue @DestDB, 'dbo.RoomTypes', 'RoomTypeNumber', 'Text', @BookedTypeNumber OUTPUT, 1
    EXEC dbo.MapValue @DestDB, 'dbo.RoomTypes', 'RoomTypeNumber', 'Type', @BookedTypeNumber OUTPUT, 1
    EXEC dbo.MapValue @DestDB, 'dbo.Rooms', 'RoomNumber', 'Number', @RoomNumber OUTPUT, 0
    EXEC dbo.MapValue @DestDB, 'dbo.Products', 'ProductNumber', 'ProductName', @ProductNumber OUTPUT, 0
    EXEC dbo.MapValue @DestDB, 'dbo.Products', 'ProductNumber', 'ProductName', @ProductNumber2 OUTPUT, 0
    EXEC dbo.MapValue @DestDB, 'dbo.Products', 'ProductNumber', 'ProductName', @ProductNumber3 OUTPUT, 0
    EXEC dbo.MapValue @DestDB, 'dbo.Products', 'ProductNumber', 'ProductName', @ProductNumber4 OUTPUT, 0
    EXEC dbo.MapValue @DestDB, 'dbo.SplitBill', 'SplitBillNumber', 'Name', @SPLITBILL OUTPUT, 0
    EXEC dbo.MapValue @DestDB, 'dbo.SplitBill', 'SplitBillNumber', 'Name', @SplitBillNumber1 OUTPUT, 0
    EXEC dbo.MapValue @DestDB, 'dbo.SplitBill', 'SplitBillNumber', 'Name', @SplitBillNumber2 OUTPUT, 0
    EXEC dbo.MapValue @DestDB, 'dbo.SplitBill', 'SplitBillNumber', 'Name', @SplitBillNumber3 OUTPUT, 0
    EXEC dbo.MapValue @DestDB, 'dbo.SplitBill', 'SplitBillNumber', 'Name', @SplitBillNumber4 OUTPUT, 0

    UPDATE ReservationRoomLine SET RebookedUnderLineNumber=@NewLineNumber WHERE LineNumber=@OldLineNumber --AV 240902 update old line. OBSOLETE! Just transfer it to the arrangement copy routine
    -- 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) 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) -- TODO
*/
    SET @S = N'INSERT INTO '+@DestDB+'dbo.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) VALUES ('+
    CONVERT(VARCHAR,@NewLineNumber)+', '+
    ISNULL(CONVERT(VARCHAR,@BookedTypeNumber), 'NULL')+', '+
    ''''+ISNULL(@BOOKEDSUBTYPE, '')+''', '+
    ISNULL(CONVERT(VARCHAR,@RoomNumber), 'NULL')+', '+
    CONVERT(VARCHAR,@NumberOfRooms)+', '+

    ISNULL(CONVERT(VARCHAR, @ArrivalDate), 0)+', '+
    ''''+ISNULL(CONVERT(VARCHAR, @ArrivalTime), 0)+''', '+
    ISNULL(CONVERT(VARCHAR, @DepartureDate), 0)+', '+
    ''''+ISNULL(CONVERT(VARCHAR, @DepartureTime), 0)+''', '+
    ISNULL(CONVERT(VARCHAR, @Adults), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @Children1), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @Children2), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @ProductNumber), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @Day2), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @ProductNumber2), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @Day3), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @ProductNumber3), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @Day4), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @ProductNumber4), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @Price), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @TotalPrice), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @PriceOverwritten), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @ScreenLine), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @PriceOverwritten2), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @PriceOverwritten3), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @PriceOverwritten4), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @FirstPrice1), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @FirstPrice2), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @FirstPrice3), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @FirstPrice4), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @TotalPrice1), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @TotalPrice2), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @TotalPrice3), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @TotalPrice4), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @SplitBillNumber1), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @SplitBillNumber2), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @SplitBillNumber3), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @SplitBillNumber4), 'NULL')+', '+
    ISNULL(CONVERT(VARCHAR, @NameListSetting), 0)+', '+
    ISNULL(CONVERT(VARCHAR, @NameListCreateFlag), 0)+')' 

    EXEC sp_executesql @S
  END
  ELSE -- @tmpLineNumber <> NULL
  BEGIN
    IF (@Status < 6) OR (@RemoveRoomNumbers = 1)
    BEGIN
      SET @RoomNumber=0  -- We need to remove roomnumber so we don't create double bookings
      UPDATE ReservationRoomLine SET RoomNumber=NULL WHERE LineNumber=@tmpLineNumber
    END

    SET @NewLineNumber=@tmpLineNumber
  END
 
  -- ReservationRoomLineXtab
--  INSERT INTO ReservationRoomLineXTab (ReferenceNumber, GuestNumber, LineNumber) VALUES (@NewReferenceNumber, @NewGuestNumber, @NewLineNumber)
  SET @S= N'INSERT INTO '+@DestDB+'dbo.ReservationRoomLineXTab (ReferenceNumber, GuestNumber, LineNumber) VALUES ('+
    CONVERT(VARCHAR, @NewReferenceNumber)+', '+CONVERT(VARCHAR, @NewGuestNumber)+', '+CONVERT(VARCHAR, @NewLineNumber)+')'
  EXEC sp_executesql @S

  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
END

CLOSE #CopyResCur
DEALLOCATE #CopyResCur

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 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(10)
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 ISNULL(@CurrentAddressNumber, 0) = 0
  BEGIN
--    EXEC GetNextNumber 'ReservationAddressIdentity', 1, @NewAddressNumber OUTPUT
    SET @S= N'EXEC '+@DestDB+'dbo.GetNextNumber ''ReservationAddressIdentity'', 1, @NewAddressNumber OUTPUT'
    SET @P = N'@NewAddressNumber INT OUTPUT'
    EXEC sp_executesql @S, @P, @NewAddressNumber OUTPUT
  END
  ELSE
  BEGIN
    SET @NewAddressNumber = @CurrentAddressNumber
    SET @CurrentAddressNumber = @CurrentAddressNumber + 1
  END

  -- Map forreign keys
  EXEC dbo.MapValue @DestDB, 'dbo.Customer', 'CustomerNumber', 'PhoneNumber', @Link OUTPUT, 0  -- PhoneNumber is the best bet we have of finding same customer
  EXEC dbo.MapValue @DestDB, 'dbo.Countries', 'CountryNumber', 'CountryCode', @CountryNumber OUTPUT, 0  -- CountryCode is the best bet of finding a match
  EXEC dbo.MapValue @DestDB, 'dbo.Ware', 'WareNumber', 'WareName', @PaymentTypeWareNumber OUTPUT, 0
  EXEC dbo.MapValue @DestDB, 'dbo.Customer', 'CustomerNumber', 'PhoneNumber', @ContactPersonCustomerNumber OUTPUT, 0  -- PhoneNumber is the best bet we have of finding same customer

  -- 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)
  SET @S = N'INSERT INTO '+@DestDB+'dbo.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 ('+
  CONVERT(VARCHAR,@NewAddressNumber)+', '+
  CONVERT(VARCHAR,@AddressType)+', '+
  ''''+ISNULL(@Title, '')+''', '+
  ''''+ISNULL(@Initial, '')+''', '+
  ''''+ISNULL(@FirstName, '')+''', '+
  ''''+ISNULL(@LastName, '')+''', '+
  ''''+ISNULL(@Address1, '')+''', '+
  ''''+ISNULL(@Address2, '')+''', '+
  ''''+ISNULL(@Address3, '')+''', '+
  ''''+ISNULL(@CountryCode, '')+''', '+

  ''''+ISNULL(@ZipCode, '')+''', '+
  ''''+ISNULL(@City, '')+''', '+
  ''''+ISNULL(@Phone, '')+''', '+
  ''''+ISNULL(@Fax, '')+''', '+
  ''''+ISNULL(@Email, '')+''', '+
  ''''+ISNULL(@AttentionName, '')+''', '+
  ISNULL(CONVERT(VARCHAR, @ProvisionType), 'NULL')+', '+
  ISNULL(CONVERT(VARCHAR, @ProvisionRate), 0)+', '+
  ISNULL(CONVERT(VARCHAR, @DiscountRate), 0)+', '+
  ISNULL(CONVERT(VARCHAR, @RoomDiscountRate), 0)+', '+
  ISNULL(CONVERT(VARCHAR, @MeetingDiscountRate), 0)+', '+
  ISNULL(CONVERT(VARCHAR, @HallDiscountRate), 0)+', '+
  ISNULL(CONVERT(VARCHAR, @PhoneDiscountRate), 0)+', '+
  ISNULL(CONVERT(VARCHAR, @Link), 'NULL')+', '+

  ISNULL(CONVERT(VARCHAR, @PaymentAfterMonth), 0)+', '+
  ISNULL(CONVERT(VARCHAR, @PaymentAfterDays), 0)+', '+
  ISNULL(CONVERT(VARCHAR, @PaymentTypeWareNumber), 0) +', '+
  ISNULL(CONVERT(VARCHAR, @CountryNumber), 'NULL') +', '+
  ''''+ISNULL(@Cell, '')+''', '+
  ''''+ISNULL(@CompanyForm, '')+''', '+
  ''''+ISNULL(@BuyersAccount, '')+''', '+
  ''''+ISNULL(@BuyersOrderID, '')+''', '+
  ISNULL(CONVERT(VARCHAR, @ContactPersonCustomerNumber), 'NULL')+')'
-- TODO? PaymentAfterRunningMonth

  EXEC sp_executesql @S

  -- ReservationAddressXTab
--  INSERT INTO ReservationAddressXTab (ReferenceNumber, GuestNumber, AddressNumber, AddressTypeNumber) VALUES (@NewReferenceNumber, @NewGuestNumber, @NewAddressNumber, @AddressType)
  SET @S= N'INSERT INTO '+@DestDB+'dbo.ReservationAddressXTab (ReferenceNumber, GuestNumber, AddressNumber, AddressTypeNumber) VALUES ('+
    CONVERT(VARCHAR, @NewReferenceNumber)+', '+CONVERT(VARCHAR, @NewGuestNumber)+', '+CONVERT(VARCHAR, @NewAddressNumber)+', '+CONVERT(VARCHAR, @AddressType)+')'
  EXEC sp_executesql @S

  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

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 (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
      EXEC GetNextNumber 'ReservationRoomLineIdentity', 1, @NewLineNumber OUTPUT
    ELSE
    BEGIN
      SET @NewLineNumber = @CurrentRoomLineNumber
      SET @CurrentRoomLineNumber = @CurrentRoomLineNumber + 1
    END

    IF (@Status < 6) --OR (@RemoveRoomNumbers = 1)
      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)
    END


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

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

     if IsNull(@TableStatus,-1 ) >= 5
       Select @TableStatus =CONVERT (INT, '0'+KeyValue) FROM HotelSetup (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 - Extras

  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 @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
   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,@PlaceText,@WareNumber,@Note

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    if convert(datetime,@OldMainResArrival) <@SystemDate
      set @date =Convert(char, @SystemDate + ( Convert(datetime,@date) -@OldMainResArrival),112)   

    -- Map forreign keys
    EXEC dbo.MapValue @DestDB, 'dbo.Ware', 'WareNumber', 'WareName', @WareNumber OUTPUT, 0
    EXEC dbo.MapValue @DestDB, 'dbo.ConferenceRooms', 'ConferenceRoomNumber', 'Number', @HallNumber OUTPUT, 0
    EXEC dbo.MapValue @DestDB, 'dbo.Departments', 'PK_Department', 'DepartmentNumber', @FK_Exdepartment OUTPUT, 0

--    Insert into ArrangementLines (Date,PacketItemNumber,Time, Price, TotalPrice,Pieces,WareText, Departments,FK_Exdepartment,PlaceText,WareNumber,note, HallNumber)
--    Values  (@Date,@PacketItemNumber,@Time, @PriceOverWrite, @ArrTotalPrice,@Pieces,@Waretext, @ArrDepartments,@FK_Exdepartment,@PlaceText,@WareNumber,@note, @HallNumber)
--    select @identityvalue=@@identity from ArrangementLines (NOLOCK) -- todo

  SET @S = N'INSERT INTO '+@DestDB+'dbo.ArrangementLines (Date,PacketItemNumber,Time, Price, Pieces,WareText, FK_Exdepartment,PlaceText,WareNumber,note, HallNumber) VALUES  ('+
  ISNULL(CONVERT(VARCHAR, @Date), 0)+','+
  ISNULL(CONVERT(VARCHAR, @PacketItemNumber), 0)+','+
  ''''+ISNULL(CONVERT(VARCHAR, @Time), 0)+''','+
  ISNULL(CONVERT(VARCHAR, @PriceOverWrite), 0)+','+
  ISNULL(CONVERT(VARCHAR, @Pieces), 0)+','+
  ''''+ISNULL(@Waretext, '')+''', '+
--  ''''+ISNULL(@ArrDepartments, '')+''', '+
--  ISNULL(CONVERT(VARCHAR, @FK_Exdepartment), 'NULL')+','+
  ISNULL(CONVERT(INT, @FK_Exdepartment), 'NULL')+','+
  ''''+ISNULL(@PlaceText, '')+''', '+
 ISNULL(CONVERT(VARCHAR, @WareNumber), 'NULL')+','+
  ''''+ISNULL(@Note, '')+''', '+
 ISNULL(CONVERT(VARCHAR, @HallNumber), 'NULL')+')'+
'select @identityvalue=@@identity'
    SET @P = N'@IdentityValue INT OUTPUT'
    EXEC sp_executesql @S, @P, @IdentityValue OUTPUT

   -- 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
     SET @S= N'INSERT INTO '+@DestDB+'dbo.ArrangementLineDepartmentXTab (FK_ArrangementLineNumber, FK_DepartmentNumber) VALUES ('+
      CONVERT(VARCHAR, @IdentityValue)+', '+CONVERT(VARCHAR, @PK_Department)+', 0, 0)'
     EXEC sp_executesql @S

     FETCH NEXT FROM DepCur INTO @PK_Department
    END
    CLOSE DepCur
    DEALLOCATE DepCur

    SET @S= N'INSERT INTO '+@DestDB+'dbo.ArrangementReservationsXtab (ArrangementLineNumber, ReferenceNumber, GuestNumber, PlusProductLineNumber) VALUES ('+
      CONVERT(VARCHAR, @IdentityValue)+', '+CONVERT(VARCHAR, @NewReferencenumber)+', 0, 0)'
    EXEC sp_executesql @S

     FETCH NEXT FROM ARRCUR
     INTO   @ArrNumber, @Date,@PacketItemNumber,@Time,@PriceoverWrite,@ArrTotalPrice,@Pieces,@WareText,@FK_Exdepartment,@PlaceText,@WareNumber,@Note
   END

  Close ArrCur
  DeAllocate ArrCur

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

    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 @HotelCustomerNumber INT

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

OPEN #CopyResCur

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

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  -- todo get proper hotelcustomernumber value
  INSERT INTO ReservationNotes (ReferenceNumber, GuestNumber, NotePage, Departments, LISTS, Text, FK_HotelCustomerNumber)
  VALUES (@NewReferenceNumber, @NewGuestNumber, @NotePage, @Departments, @LISTS, @Text, @HotelCustomerNumber)

  SET @S= N'INSERT INTO '+@DestDB+'dbo.ReservationNotes (ReferenceNumber, GuestNumber, NotePage, Departments, LISTS, Text, FK_HotelCustomerNumber) VALUES ('+
    CONVERT(VARCHAR, @NewReferenceNumber)+', '+CONVERT(VARCHAR, @NewGuestNumber)+', '+CONVERT(VARCHAR, @NotePage) +', '''+ @Departments +''', '''+ @Lists + ''', '''+ @Text+''', ' + CONVERT(VARCHAR, @HotelCustomerNumber) +  ')'
  EXEC sp_executesql @S

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

CLOSE #CopyResCur
DEALLOCATE #CopyResCur

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--EXEC UpdateArrivalAndDeparture2 @NewReferenceNumber, @NewGuestNumber
SET @S= N'EXEC '+@DestDB+'dbo.UpdateArrivalAndDeparture2 '+CONVERT(VARCHAR, @NewReferenceNumber)+', '+CONVERT(VARCHAR, @NewGuestNumber)
EXEC sp_executesql @S

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 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 (@SystemDateTime, 5, 12, 3, @Text, @SystemDateTime, @UserIDNumber, 2)
SELECT @NewFollowUpNumber = @@identity
INSERT INTO ReservationFollowUpXTab (ReferenceNumber, GuestNumber, FollowUpNumber) VALUES (@OldReferenceNumber, @OldGuestNumber, @NewFollowUpNumber)

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

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

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

-- Write Reservationumbers to AltResNo
UPDATE Reservations SET AltResNo = CONVERT(VARCHAR, @NewReferenceNumber) + '/' + CONVERT(VARCHAR, @NewGuestNumber) WHERE ReferenceNumber=@OldReferenceNumber AND GuestNumber=@OldGuestNumber
SET @S= N'UPDATE '+@DestDB+'dbo.Reservations SET AltResNo = '''+CONVERT(VARCHAR, @OldReferenceNumber) + '/' + CONVERT(VARCHAR, @OldGuestNumber)+''' WHERE ReferenceNumber='+CONVERT(VARCHAR, @NewReferenceNumber) + ' AND GuestNumber='+CONVERT(VARCHAR, @NewGuestNumber)
EXEC sp_executesql @S

END -- IF @AltResNo = ''
ELSE
  print 'Already copied!'
GO
