CREATE PROCEDURE CreateCourseAttendeeReservation @AMainReferenceNumber INT, @ALastname VARCHAR(50), @AFirstname VARCHAR(50), @AAttendeeReferenceNumber INT OUTPUT AS

/*
  20160208 RN - Initial version
*/
BEGIN
  SET NOCOUNT ON

  DECLARE @NewReferenceNumber INT
  DECLARE @AllotmentNumber INT
  DECLARE @CancellationNumber INT
  DECLARE @FK_ProjectCode INT
  DECLARE @HotelNumber INT
  DECLARE @OriginType INT
  DECLARE @ReservationType INT
  DECLARE @StaffID INT
  DECLARE @ResConfLineNumber INT
  DECLARE @NewResConfLineNumber INT
  DECLARE @NewAddressNumber INT
  DECLARE @ConferenceTypeNumber INT

  EXEC GetNextNumber 'RESERVATIONIDENTITY', 1, @NewReferenceNumber OUTPUT

  INSERT INTO ReservationReservationXtab (FK_ReferenceNumber, FK_ReferenceNumberSub) VALUES (@AMainReferenceNumber, @NewReferenceNumber)
  
  -- Copy ReservationMain contents first:
  SELECT *
  INTO #TempReservationMain
  FROM RESERVATIONMAIN WITH (NOLOCK)
  WHERE RESERVATIONMAIN.REFERENCENUMBER = @AMainReferenceNumber

  UPDATE #TempReservationMain
  SET REFERENCENUMBER = @NewReferenceNumber
  WHERE REFERENCENUMBER = @AMainReferenceNumber

  INSERT INTO RESERVATIONMAIN SELECT * FROM #TempReservationMain

  DROP TABLE #TempReservationMain

  -- Copy Reservations content:
  SELECT * 
  INTO #TempReservations
  FROM Reservations WITH (NOLOCK)
  WHERE Reservations.ReferenceNumber = @AMainReferenceNumber 

  UPDATE #TempReservations
  SET ReferenceNumber = @NewReferenceNumber
  WHERE ReferenceNumber = @AMainReferenceNumber

  INSERT INTO RESERVATIONS SELECT * FROM #TempReservations

  DROP TABLE #TempReservations
  
  -- TODO: Add ReservationAddress and ReservationAddressXtab for AddressTypeNumber = 1
  -- TODO: And something about POS, Segment etc.

  EXEC GetNextNumber 'RESERVATIONADDRESSIDENTITY', 1, @NewAddressNumber OUTPUT

  INSERT INTO ReservationAddress (AddressNumber, AddressType, Lastname, Firstname) VALUES (@NewAddressNumber, 1, @ALastname, @AFirstname)
  INSERT INTO RESERVATIONADDRESSXTAB (REFERENCENUMBER, GUESTNUMBER, AddressTypeNumber, ADDRESSNUMBER) VALUES (@NewReferenceNumber, 0, 1, @NewAddressNumber)

  -- Copy relevant Hall lines:
  SELECT ReservationConferenceLine.*
  INTO #TempReservationConferenceLine
  FROM ReservationConferenceLine WITH (NOLOCK)
  INNER JOIN ConferenceRoomTypes WITH (NOLOCK) ON ReservationConferenceLine.BookedConferenceRoomTypeNumber = ConferenceRoomTypes.ConferenceRoomTypeNumber
  WHERE ConferenceRoomTypes.DoCopyType = 1
    AND ReservationConferenceLine.ReferenceNumber = @AMainReferenceNumber
	AND RESERVATIONCONFERENCELINE.GuestNo = 0

  UPDATE #TempReservationConferenceLine
  SET ReferenceNumber = @NewReferenceNumber
  WHERE ReferenceNumber = @AMainReferenceNumber

  SELECT @ConferenceTypeNumber = ConferenceRoomTypeNumber FROM CONFERENCEROOMTYPES WHERE ResourceType = 16 -- htrtMainReservation

  UPDATE #TempReservationConferenceLine
  SET Adults = 1
  WHERE BookedConferenceRoomTypeNumber = @ConferenceTypeNumber

  DECLARE TempCursor CURSOR FORWARD_ONLY FOR
    SELECT ReservationConfLineNumber
	FROM #TempReservationConferenceLine WITH (NOLOCK)

  OPEN TempCursor
  FETCH NEXT FROM TempCursor INTO @ResConfLineNumber
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    
	EXEC GetNextNumber 'RESERVATIONROOMLINEIDENTITY', 1, @NewResConfLineNumber OUTPUT

    UPDATE #TempReservationConferenceLine
	SET ReservationConfLineNumber = @NewResConfLineNumber
	WHERE ReservationConfLineNumber = @ResConfLineNumber
    FETCH NEXT FROM TempCursor INTO @ResConfLineNumber
  END

  CLOSE TempCursor
  DEALLOCATE TempCursor

  INSERT INTO RESERVATIONCONFERENCELINE SELECT * FROM #TempReservationConferenceLine

  SET @AAttendeeReferenceNumber = @NewReferenceNumber
END;