UFN_PLEDGE_GETREVALUEDSPLITBALANCE_NO_MULTICURRENCY

create function dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCE_NO_MULTICURRENCY
(
    @REVENUESPLITID uniqueidentifier
    ,@ASOFDATE datetime
)
returns money
as 
begin

    declare @BALANCE money = 0;

    select @BALANCE = LI.TRANSACTIONAMOUNT
    from dbo.FINANCIALTRANSACTIONLINEITEM LI
    where LI.ID = @REVENUESPLITID
        and LI.DELETEDON is null
        and LI.TYPECODE != 1;

    select @BALANCE = @BALANCE - isnull(sum(isnull(INSTALLMENTSPLITPAYMENT.AMOUNT, 0)),0)
    from dbo.INSTALLMENTSPLIT
    inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTLINEITEM on PAYMENTLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
    inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = PAYMENTLINEITEM.FINANCIALTRANSACTIONID
    where INSTALLMENTSPLIT.REVENUESPLITID = @REVENUESPLITID
        and PAYMENT.CALCULATEDDATE <= @ASOFDATE 
        and PAYMENT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
        and PAYMENT.DELETEDON is NULL;

    select @BALANCE = @BALANCE - isnull(sum(isnull(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT, 0)),0)
    from dbo.INSTALLMENTSPLIT
    inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
    inner join dbo.FINANCIALTRANSACTION as WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
    where INSTALLMENTSPLIT.REVENUESPLITID = @REVENUESPLITID
        and WRITEOFF.CALCULATEDDATE <= @ASOFDATE
        and WRITEOFF.TYPECODE = 20
        and WRITEOFF.DELETEDON is NULL;

    return @BALANCE;
end