UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCEASOF
Returns the unpaid balance for a given pledge installment split as of a given date.
Return
| Return Type |
|---|
| money |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @INSTALLMENTSPLITID | uniqueidentifier | IN | |
| @ASOFDATE | datetime | IN |
Definition
Copy
create function dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCEASOF
(
@INSTALLMENTSPLITID uniqueidentifier,
@ASOFDATE datetime
)
returns money
with execute as caller
as begin
declare @BALANCE money;
select
@BALANCE = INSTALLMENTSPLIT.TRANSACTIONAMOUNT -
(coalesce((select
sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
where INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = @INSTALLMENTSPLITID
and REVENUE.DATE <= @ASOFDATE), 0) +
coalesce((select
sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
from dbo.INSTALLMENTSPLITWRITEOFF
inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
where INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = @INSTALLMENTSPLITID
and WRITEOFF.DATE <= @ASOFDATE), 0))
from
dbo.INSTALLMENTSPLIT
where
INSTALLMENTSPLIT.ID = @INSTALLMENTSPLITID;
return @BALANCE;
end