UFN_PLEDGE_GETBALANCEASOFINCURRENCY

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


                select 
                    @RESULT = REVENUE.TRANSACTIONAMOUNT - 

                              (coalesce((select 
                                            sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
                                        from dbo.INSTALLMENTSPLITPAYMENT 
                                        inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                        inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
                                        where INSTALLMENTSPLITPAYMENT.PLEDGEID = @PLEDGEID
                                        and REVENUE.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)),
                    @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
                from
                      dbo.REVENUE
                where 
                      REVENUE.ID = @PLEDGEID;

                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
                return @RESULT;
            end