UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEASOF
Returns the unpaid balance for a given recurring gift installment as of a particular date.
Return
| Return Type |
|---|
| money |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @INSTALLMENTID | uniqueidentifier | IN | |
| @ASOFDATE | datetime | IN |
Definition
Copy
create function dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEASOF
(
@INSTALLMENTID uniqueidentifier,
@ASOFDATE datetime
)
returns money
with execute as caller
as begin
declare @BALANCE money = 0;
select
@BALANCE = RECURRINGGIFTINSTALLMENT.TRANSACTIONAMOUNT
- coalesce(
(
select sum(RECURRINGGIFTINSTALLMENTPAYMENT.AMOUNT)
from dbo.RECURRINGGIFTINSTALLMENTPAYMENT
where RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID = @INSTALLMENTID
)
, 0)
- coalesce(
(
select sum(RECURRINGGIFTINSTALLMENTWRITEOFF.TRANSACTIONAMOUNT)
from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF
where RECURRINGGIFTINSTALLMENTWRITEOFF.RECURRINGGIFTINSTALLMENTID = @INSTALLMENTID
)
, 0)
from dbo.RECURRINGGIFTINSTALLMENT
where RECURRINGGIFTINSTALLMENT.ID = @INSTALLMENTID
and RECURRINGGIFTINSTALLMENT.DATE < @ASOFDATE;
return @BALANCE;
end