UFN_RECURRINGGIFT_PAYMENTDUE

This function returns the total amount that is due on a given recurring gift as of a given date.

Return

Return Type
money

Parameters

Parameter	Parameter Type	Mode	Description
@REVENUEID	uniqueidentifier	IN
@ASOFDATE	date	IN

Definition

Copy



CREATE function dbo.UFN_RECURRINGGIFT_PAYMENTDUE(
    @REVENUEID uniqueidentifier,
    @ASOFDATE date
)
returns money
with execute as caller
as begin
    declare @INSTALLMENTS table (BALANCE money);
    declare @PAYMENTDUE money;

    insert into @INSTALLMENTS
    select dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(ID)
    from dbo.RECURRINGGIFTINSTALLMENT
    where REVENUEID = @REVENUEID
    and DATE <= @ASOFDATE
    and dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(ID) > 0;

    insert into @INSTALLMENTS
    select R.TRANSACTIONAMOUNT
    from FINANCIALTRANSACTION R
    cross apply dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS(R.ID) I
    where R.ID = @REVENUEID
    and I.DATE <= @ASOFDATE;

    select @PAYMENTDUE = isnull(sum(BALANCE),0)
    from @INSTALLMENTS;

    return @PAYMENTDUE;
end