UFN_PLEDGE_GETBALANCEINCURRENCY

            CREATE function dbo.UFN_PLEDGE_GETBALANCEINCURRENCY
            (
                @PLEDGEID uniqueidentifier,
                @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 = 
                        case when REVENUE.TRANSACTIONTYPECODE = 7 then --Auction donations do not have installments

                            REVENUE.TRANSACTIONAMOUNT - 
                                coalesce((    select 
                                                sum(WRITEOFFSPLIT.TRANSACTIONAMOUNT) 
                                            from dbo.WRITEOFFSPLIT
                                            inner join dbo.WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.WRITEOFFID
                                            where WRITEOFF.REVENUEID = REVENUE.ID
                                         ), 0)

                        else
                            REVENUE.TRANSACTIONAMOUNT - 

                                  (coalesce((select 
                                                sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
                                            from dbo.INSTALLMENTSPLITPAYMENT 
                                            where INSTALLMENTSPLITPAYMENT.PLEDGEID = @PLEDGEID), 0) + 

                                  coalesce((select
                                                sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) 
                                            from dbo.INSTALLMENTSPLITWRITEOFF 
                                            inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
                                            where INSTALLMENTSPLIT.PLEDGEID = @PLEDGEID), 0))
                        end,
                        @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
                from
                      dbo.REVENUE
                where 
                      REVENUE.ID = @PLEDGEID;

                set @CURRENCYID = coalesce(@CURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY());
                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