UFN_PLEDGE_GETBALANCEASOFINCURRENCY_2

            CREATE function dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_2
            (
                @PLEDGEID uniqueidentifier,
                @ASOFDATE datetime,
                @CURRENCYID uniqueidentifier
            ) 
      returns money
            with execute as caller
            as begin
                declare @RESULT money = 0;
                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @CURRENTDATE datetime = getdate(); -- Use today's date to get the exchange rate.


        select
          @RESULT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
          @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
        from
                    dbo.FINANCIALTRANSACTION
                where 
                    FINANCIALTRANSACTION.ID = @PLEDGEID
          and FINANCIALTRANSACTION.DELETEDON is null;

        if @RESULT <> 0
        begin
          set @RESULT = @RESULT - 
                        (
                          coalesce(
                            (select sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
                             from dbo.INSTALLMENTSPLITPAYMENT 
                                                 inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                                 inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = REVENUESPLIT.REVENUEID
                                                 where INSTALLMENTSPLITPAYMENT.PLEDGEID = @PLEDGEID
                                                 and FINANCIALTRANSACTION.DATE <= @ASOFDATE
                            ), 0) +

                          coalesce(
                            (select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) 
                                                 from dbo.INSTALLMENTSPLITWRITEOFF 
                                                 inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
                                                 inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                                                 where INSTALLMENTSPLIT.PLEDGEID = @PLEDGEID
                                                 and WRITEOFF.DATE <= @ASOFDATE
                            ), 0)
                        );

          if @RESULT <> 0
          begin
            if @CURRENCYID is not null and @TRANSACTIONCURRENCYID is not null and @CURRENCYID <> @TRANSACTIONCURRENCYID
                    begin
                        declare @CURRENCYEXCHANGERATEID uniqueidentifier;
                        set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@TRANSACTIONCURRENCYID, @CURRENCYID, @CURRENTDATE, 1, null);
                        if (@CURRENCYEXCHANGERATEID is not null)
                            set @RESULT = dbo.UFN_CURRENCY_CONVERT(@RESULT, @CURRENCYEXCHANGERATEID)
                        else    
                            set @RESULT = dbo.UFN_CURRENCY_CONVERTINVERSE(@RESULT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@CURRENCYID, @TRANSACTIONCURRENCYID, @CURRENTDATE, 1, null))
                    end
          end
        end

                return @RESULT;
            end