UFN_RECURRINGGIFT_GETBALANCEASOFINCURRENCY

Returns the balance of total payments made to a recurring gift as of a given date in the given currency.

Return

Return Type
money

Parameters

Parameter	Parameter Type	Mode
@RECURRINGID	uniqueidentifier	IN
@ASOFDATE	datetime	IN
@CURRENCYID	uniqueidentifier	IN

Definition

Copy



            CREATE function dbo.UFN_RECURRINGGIFT_GETBALANCEASOFINCURRENCY
            (
                @RECURRINGID uniqueidentifier,
                @ASOFDATE datetime,
                @CURRENCYID uniqueidentifier
            ) 
            returns money
            with execute as caller
            as begin
                declare @RESULT money;
                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @CURRENTDATE datetime = getdate(); -- Use today's date to get the exchange rate.


                select 
                    @RESULT = sum(RECURRINGGIFTACTIVITY.AMOUNT),
                    @TRANSACTIONCURRENCYID = RECURRINGGIFTACTIVITY.APPLICATIONCURRENCYID
                from 
                    dbo.RECURRINGGIFTACTIVITY
                inner join dbo.REVENUESPLIT
                    on REVENUESPLIT.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
                inner join dbo.REVENUE
                    on REVENUE.ID = REVENUESPLIT.REVENUEID
                where 
                    RECURRINGGIFTACTIVITY.SOURCEREVENUEID = @RECURRINGID
                    and REVENUE.DATE <= @ASOFDATE
                group by RECURRINGGIFTACTIVITY.APPLICATIONCURRENCYID

                if @CURRENCYID is not null and @TRANSACTIONCURRENCYID is not null and @CURRENCYID <> @TRANSACTIONCURRENCYID
                begin
                    declare @CURRENCYEXCHANGERATEID uniqueidentifier;
                    set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@TRANSACTIONCURRENCYID, @CURRENCYID, @CURRENTDATE, 1, null);
                    if (@CURRENCYEXCHANGERATEID is not null)
                        set @RESULT = dbo.UFN_CURRENCY_CONVERT(@RESULT, @CURRENCYEXCHANGERATEID)
                    else    
                        set @RESULT = dbo.UFN_CURRENCY_CONVERTINVERSE(@RESULT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@CURRENCYID, @TRANSACTIONCURRENCYID, @CURRENTDATE, 1, null))
                end
                return @RESULT;
            end