USP_RECORDOPERATION_REVENUEOPPORTUNITYUNLINK

CREATE procedure dbo.USP_RECORDOPERATION_REVENUEOPPORTUNITYUNLINK
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as begin

  -- cache current context information

  declare @contextCache varbinary(128);
  set @contextCache = CONTEXT_INFO();

  -- set CONTEXT_INFO to @CHANGEAGENTID

  set CONTEXT_INFO @CHANGEAGENTID;

  -- Remove payment links

  delete from dbo.REVENUEOPPORTUNITY
  where REVENUEOPPORTUNITY.ID in
  (
    select
        FINANCIALTRANSACTIONLINEITEM.ID
    from dbo.FINANCIALTRANSACTIONLINEITEM
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID

    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 = @ID
  );

  -- reset CONTEXT_INFO to previous value

  if not @contextCache is null
    set CONTEXT_INFO @contextCache;

  return 0;
end