UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY_BULK_NO_MULTICURRENCY

CREATE function dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY_BULK_NO_MULTICURRENCY
(    
    @ASOFDATE datetime
)
returns table
as return(

    select
        LI.ID,
        LI.TRANSACTIONAMOUNT - coalesce(PAYMENTS.AMOUNT,0) - coalesce(WRITEOFFS.AMOUNT,0) as [BALANCE] 
    from dbo.FINANCIALTRANSACTION as FT with (nolock)
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on FT.ID = LI.FINANCIALTRANSACTIONID
    inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
    left join (select INSTALLMENTSPLIT.REVENUESPLITID,
            sum(INSTALLMENTSPLITPAYMENT.AMOUNT) as AMOUNT
        from dbo.INSTALLMENTSPLITPAYMENT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTLINEITEM on PAYMENTLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
        inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = PAYMENTLINEITEM.FINANCIALTRANSACTIONID
        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
        where 
            PAYMENT.CALCULATEDDATE <= @ASOFDATE 
            and PAYMENT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
            and PAYMENT.DELETEDON is NULL
        group by INSTALLMENTSPLIT.REVENUESPLITID
        ) PAYMENTS on PAYMENTS.REVENUESPLITID = LI.ID
    left join ( select INSTALLMENTSPLIT.REVENUESPLITID,
            sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT
        from dbo.INSTALLMENTSPLITWRITEOFF
        inner join dbo.FINANCIALTRANSACTION as WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
        where
            WRITEOFF.CALCULATEDDATE <= @ASOFDATE
            and WRITEOFF.TYPECODE = 20
            and WRITEOFF.DELETEDON is NULL
        group by INSTALLMENTSPLIT.REVENUESPLITID
        ) WRITEOFFS on WRITEOFFS.REVENUESPLITID = LI.ID
    where 
        FT.TYPECODE in (1,3,4,6,8)
        and FT.DELETEDON is null
        and LI.DELETEDON is null
        and LI.TYPECODE != 1
)