UFN_PLEDGE_GETBALANCEIGNOREPAYMENT

            create function dbo.UFN_PLEDGE_GETBALANCEIGNOREPAYMENT
            (
                @PLEDGEID uniqueidentifier,
                @PAYMENTID uniqueidentifier
            ) 
            returns money
            with execute as caller
            as begin
                declare @RESULT money;

                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 
                                                inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                            where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID
                                                and REVENUESPLIT.REVENUEID <> @PAYMENTID), 0) + 

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

                return @RESULT;
            end