USP_DATAFORMTEMPLATE_ADD_EVENTREGISTRATIONOPPORTUNITYLINK

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_EVENTREGISTRATIONOPPORTUNITYLINK
(
    @ID uniqueidentifier = null output,
    @REGISTRANTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @OPPORTUNITYID uniqueidentifier
)
as
  set nocount on;

  set @ID = @REGISTRANTID;

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @CURRENTDATE datetime = getdate();

  begin try
    -- Add link to event registration

    insert into dbo.EVENTREGISTRATIONOPPORTUNITY
    (
      ID,
      OPPORTUNITYID,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    values
    (
      @REGISTRANTID,
      @OPPORTUNITYID,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    );

    -- Add link to event registration payments

    insert into dbo.REVENUEOPPORTUNITY
    (
      ID,
      OPPORTUNITYID,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    select
      EVENTREGISTRANTPAYMENT.PAYMENTID,
      @OPPORTUNITYID,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from dbo.EVENTREGISTRANTPAYMENT
    where EVENTREGISTRANTPAYMENT.REGISTRANTID = @REGISTRANTID;

    declare @DATE datetime;
    select
      @DATE = FT.DATE 
    from 
      dbo.EVENTREGISTRANTPAYMENT ERP
      inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = ERP.PAYMENTID
      inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
    where ERP.REGISTRANTID = @REGISTRANTID;

    if @DATE is null
      set @DATE = @CURRENTDATE;

    exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID, @DATE, @CHANGEAGENTID, @CURRENTDATE;
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;