UFN_PLEDGE_GETDESIGNATIONBALANCEINCURRENCY_BULK

            CREATE function dbo.UFN_PLEDGE_GETDESIGNATIONBALANCEINCURRENCY_BULK
            (
                @CURRENCYID uniqueidentifier
            )
      returns table
      as 
      return(
        select
          REVENUE.ID,
          REVENUESPLIT.DESIGNATIONID,
          sum(
          case when @CURRENCYID <> REVENUE.TRANSACTIONCURRENCYID then
            case when dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(REVENUE.TRANSACTIONCURRENCYID, @CURRENCYID, getdate(), 1, null) is not null then
              dbo.UFN_CURRENCY_CONVERT(REVENUESPLIT.TRANSACTIONAMOUNT - (coalesce((Q.PAYMENTS), 0) + coalesce((P.WRITEOFFS), 0)), dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(REVENUE.TRANSACTIONCURRENCYID, @CURRENCYID, getdate(), 1, null))
            else
              dbo.UFN_CURRENCY_CONVERTINVERSE(REVENUESPLIT.TRANSACTIONAMOUNT - (coalesce((Q.PAYMENTS), 0) + coalesce((P.WRITEOFFS), 0)), dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(REVENUE.TRANSACTIONCURRENCYID, @CURRENCYID, getdate(), 1, null))
            end
          else
            REVENUESPLIT.TRANSACTIONAMOUNT - (coalesce((Q.PAYMENTS), 0) + coalesce((P.WRITEOFFS), 0))
          end
          ) as BALANCEINCURRENCY
        from dbo.REVENUESPLIT with (nolock)
        inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
        left join (
            select sum(INSTALLMENTSPLITPAYMENT.AMOUNT) as PAYMENTS,INSTALLMENTSPLITPAYMENT.PLEDGEID as PAYMENTREVENUEID,INSTALLMENTSPLIT.DESIGNATIONID PAYMENTDESIGNATIONID
            from dbo.INSTALLMENTSPLITPAYMENT 
            inner join INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
            group by INSTALLMENTSPLITPAYMENT.PLEDGEID , INSTALLMENTSPLIT.DESIGNATIONID
          ) as Q on Q.PAYMENTREVENUEID = REVENUE.ID and Q.PAYMENTDESIGNATIONID=REVENUESPLIT.DESIGNATIONID
          left join(
              select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) as WRITEOFFS,WRITEOFF.REVENUEID WRITEOFFREVENUEID,INSTALLMENTSPLIT.DESIGNATIONID WRITEOFFDESIGNATIONID
              from dbo.WRITEOFF 
              inner join dbo.INSTALLMENTSPLITWRITEOFF on WRITEOFF.ID = INSTALLMENTSPLITWRITEOFF.WRITEOFFID 
              inner join INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
              group by WRITEOFF.REVENUEID,INSTALLMENTSPLIT.DESIGNATIONID
          ) as P on P.WRITEOFFREVENUEID=REVENUE.ID and P.WRITEOFFDESIGNATIONID = REVENUESPLIT.DESIGNATIONID
        group by REVENUESPLIT.DESIGNATIONID,REVENUE.ID
      )