UFN_PLEDGE_GETBALANCEASOF_BULK

create function dbo.UFN_PLEDGE_GETBALANCEASOF_BULK
(
    @ASOFDATE datetime
)
returns table
as
return
(
    select
        REVENUE.ID
        ,(REVENUE.TRANSACTIONAMOUNT - isnull(PAYMENT.AMOUNT, 0) - isnull(WRITEOFF.AMOUNT, 0)) [BALANCE]
        ,REVENUE.TRANSACTIONCURRENCYID
        ,REVENUE.CALCULATEDDATE [DATE]
        ,REVENUE.TYPECODE [TRANSACTIONTYPECODE]
        ,REVENUE.TYPE [TRANSACTIONTYPE]
        ,REVENUE.CONSTITUENTID
        ,REVENUE.TRANSACTIONAMOUNT
    from dbo.FINANCIALTRANSACTION REVENUE with (nolock)
    inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
    left join (
        select INSTALLMENTSPLITPAYMENT.PLEDGEID, SUM(PAY_LI.TRANSACTIONAMOUNT) AMOUNT
        from dbo.INSTALLMENTSPLITPAYMENT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM PAY_LI on PAY_LI.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
        inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = PAY_LI.ID
        inner join dbo.FINANCIALTRANSACTION PAY on PAY.ID = PAY_LI.FINANCIALTRANSACTIONID
        where PAY_LI.DELETEDON is null and PAY_LI.TYPECODE = 0
            and PAY.CALCULATEDDATE <= @ASOFDATE
        group by INSTALLMENTSPLITPAYMENT.PLEDGEID
    ) PAYMENT on PAYMENT.PLEDGEID = REVENUE.ID
    left join (
        select INSTALLMENTSPLIT.PLEDGEID, SUM(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT
        from dbo.INSTALLMENTSPLITWRITEOFF
        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
        inner join dbo.FINANCIALTRANSACTION WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
        where WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null
            and WRITEOFF.CALCULATEDDATE <= @ASOFDATE
        group by INSTALLMENTSPLIT.PLEDGEID
    ) WRITEOFF on WRITEOFF.PLEDGEID = REVENUE.ID
    where REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and REVENUE.DELETEDON is null 
)