UFN_INSTALLMENT_GETINSTALLMENTBALANCE

Returns the unpaid balance for an installment

Return

Return Type
money

Parameters

Parameter	Parameter Type	Mode	Description
@INSTALLMENTID	uniqueidentifier	IN

Definition

Copy



            CREATE function dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE
            (
                @INSTALLMENTID uniqueidentifier    
            )
            returns money
            with execute as caller
            as begin
                declare @BALANCE money;

                --select 

                --    @BALANCE = INSTALLMENT.BALANCE

                --from 

                --    dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE_BULK() as INSTALLMENT 

                --where 

                --    INSTALLMENT.ID = @INSTALLMENTID;

                select @BALANCE = INSTALLMENT.TRANSACTIONAMOUNT - (
                    isnull((select sum(INSTALLMENTPAYMENT.AMOUNT) from dbo.INSTALLMENTPAYMENT where INSTALLMENTPAYMENT.INSTALLMENTID = @INSTALLMENTID),0)
                    +
                    isnull((select sum(INSTALLMENTWRITEOFF.TRANSACTIONAMOUNT) from dbo.INSTALLMENTWRITEOFF where INSTALLMENTWRITEOFF.INSTALLMENTID = @INSTALLMENTID),0)
                    )
                from dbo.INSTALLMENT
                where INSTALLMENT.ID = @INSTALLMENTID

                return @BALANCE;
            end