USP_REVENUEOPPORTUNITY_UPDATEOPPORTUNITY

CREATE procedure dbo.USP_REVENUEOPPORTUNITY_UPDATEOPPORTUNITY
(
  @REVENUEID uniqueidentifier,
  @OPPORTUNITYID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @CURRENTDATE datetime = getdate();

  update REVENUEOPPORTUNITY set
    OPPORTUNITYID = @OPPORTUNITYID,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  from dbo.REVENUEOPPORTUNITY
  where
    REVENUEOPPORTUNITY.ID in
    (
      select
        FINANCIALTRANSACTIONLINEITEM.ID
      from dbo.FINANCIALTRANSACTIONLINEITEM
      where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID

      union all

      select
        FINANCIALTRANSACTIONLINEITEM.ID
      from dbo.FINANCIALTRANSACTIONLINEITEM
        left join dbo.FINANCIALTRANSACTIONLINEITEM SOURCE on SOURCE.ID = FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID
      where
        FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = SOURCE.ID and
        SOURCE.FINANCIALTRANSACTIONID = @REVENUEID
    );

  -- Add missing mappings, if any

  insert into dbo.REVENUEOPPORTUNITY
  (
    ID,
    OPPORTUNITYID,
    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
  )
  select
    FTLI.ID,
    @OPPORTUNITYID,
    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
  from  
    dbo.FINANCIALTRANSACTIONLINEITEM FTLI
    left join dbo.REVENUEOPPORTUNITY RO on RO.ID = FTLI.ID
  where
    FTLI.FINANCIALTRANSACTIONID = @REVENUEID
    and FTLI.DELETEDON is null
    and RO.ID is null;
end