UFN_CONSTITUENT_GETMATCHEDTOTALINCURRENCY

Returns the total amount matched for a constituent by an organization for revenue in a specific currency.

Return

Return Type
money

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@MATCHINGORGANIZATIONID	uniqueidentifier	IN
@TRANSACTIONCURRENCYID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_CONSTITUENT_GETMATCHEDTOTALINCURRENCY
(
  @CONSTITUENTID uniqueidentifier = null,
  @MATCHINGORGANIZATIONID uniqueidentifier = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null
) 
returns money
as
begin
  declare @r money;

  if @TRANSACTIONCURRENCYID is null
    set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  declare @CONSTITUENT_GIFT table
  (
    ID uniqueidentifier
  );

  insert into
    @CONSTITUENT_GIFT
  select
    ID
  from
    dbo.FINANCIALTRANSACTION
  where
    CONSTITUENTID = @CONSTITUENTID
    and
    DELETEDON is null;

  select
    @r = coalesce(sum(MGREVENUE.TRANSACTIONAMOUNT), 0)
  from
    @CONSTITUENT_GIFT as ORIGINALREVENUE
  inner join
    dbo.REVENUEMATCHINGGIFT as RMG on RMG.MGSOURCEREVENUEID = ORIGINALREVENUE.ID
  inner join
    dbo.FINANCIALTRANSACTION as MGREVENUE on MGREVENUE.ID = RMG.ID
  where
    MGREVENUE.CONSTITUENTID = @MATCHINGORGANIZATIONID
    and
    MGREVENUE.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
    and
    MGREVENUE.DELETEDON is null;

  return @r;
end