UFN_PLEDGE_PAYMENTDUE

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

Return

Return Type
money

Parameters

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

Definition

Copy



            CREATE function dbo.UFN_PLEDGE_PAYMENTDUE
            (
                @REVENUEID uniqueidentifier,
                @ASOFDATE as datetime
            )
            returns money
            as
            begin
                declare @paymentAmount as money
                declare @writeoffAmount as money
                declare @totalAmount as money

                select @paymentAmount = coalesce(sum(INSTALLMENTPAYMENT.AMOUNT), 0)
                from dbo.INSTALLMENTPAYMENT
                inner join dbo.INSTALLMENT
                on INSTALLMENT.ID = INSTALLMENTPAYMENT.INSTALLMENTID
                where INSTALLMENT.REVENUEID = @REVENUEID
                and INSTALLMENT.DATE <= @ASOFDATE;

                select @writeoffAmount = coalesce(sum(INSTALLMENTWRITEOFF.TRANSACTIONAMOUNT), 0)
                from dbo.INSTALLMENTWRITEOFF
                inner join dbo.INSTALLMENT
                on INSTALLMENT.ID = INSTALLMENTWRITEOFF.INSTALLMENTID
                where INSTALLMENT.REVENUEID = @REVENUEID
                and INSTALLMENT.DATE <= @ASOFDATE;

                select @totalAmount = coalesce(sum(INSTALLMENT.TRANSACTIONAMOUNT), 0)
                from dbo.INSTALLMENT
                where INSTALLMENT.REVENUEID = @REVENUEID
                and INSTALLMENT.DATE <= @ASOFDATE;

                return (@totalAmount - @paymentAmount - @writeoffAmount);
            end