UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK_NO_MULTICURRENCY

create function dbo.UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK_NO_MULTICURRENCY
(
  @ASOFDATE datetime
)
returns table
as
return
(
    select
        REVENUE.ID,
        REVENUE.TRANSACTIONAMOUNT - coalesce(PLEDGEAMOUNT.AMOUNT,0) - coalesce(WRITEOFFAMOUNT.AMOUNT,0) as BALANCEINCURRENCY,
        REVENUE.CALCULATEDDATE [DATE],
        REVENUE.TYPECODE TRANSACTIONTYPECODE,
        REVENUE.TYPE TRANSACTIONTYPE,
        REVENUE.CONSTITUENTID,
        REVENUE.TRANSACTIONAMOUNT,
        REVENUE_EXT.APPEALID
    from dbo.FINANCIALTRANSACTION REVENUE with (nolock)
    inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
    left join (
        select sum(INSTALLMENTSPLITPAYMENT.AMOUNT) as AMOUNT, INSTALLMENTSPLITPAYMENT.PLEDGEID
        from dbo.INSTALLMENTSPLITPAYMENT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
        inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
        where PAYMENT.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
        and PAYMENT.CALCULATEDDATE <= @ASOFDATE
        group by INSTALLMENTSPLITPAYMENT.PLEDGEID) PLEDGEAMOUNT on PLEDGEAMOUNT.PLEDGEID = REVENUE.ID
    left join (
        select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT, WRITEOFF.PARENTID
        from dbo.INSTALLMENTSPLITWRITEOFF
        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 WRITEOFF.PARENTID) WRITEOFFAMOUNT on WRITEOFFAMOUNT.PARENTID  = REVENUE.ID
    where REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and REVENUE.DELETEDON is null
 )