UFN_PLEDGESPLIT_GETBALANCEASOF

            CREATE function dbo.UFN_PLEDGESPLIT_GETBALANCEASOF
            (
                @PLEDGESPLITID uniqueidentifier,
                @ASOFDATE datetime
            ) 
            returns money
            with execute as caller
            as begin

                declare @RESULT money;

                select 
                    @RESULT = 
                        case when FINANCIALTRANSACTION.TYPECODE = 7 then --Auction donations do not have installments

                            FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT - 
                            coalesce((select
                                        sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) as AMOUNT
                                    from dbo.INSTALLMENTSPLIT
                                    inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
                                    inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID                            
                                    where 
                                        INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID and
                                        INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID and
                                        WRITEOFF.DATE <= @ASOFDATE), 0)

                        else
                            FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT - 

                                    (coalesce((select 
                                                sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
                                            from dbo.INSTALLMENTSPLITPAYMENT
                                                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                                inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTSPLIT on PAYMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
                                            where
                                                INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID and
                                                INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID and
                                                FINANCIALTRANSACTION.DATE <= @ASOFDATE), 0) + 

                                    coalesce((select
                                                sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) as AMOUNT
                                            from dbo.INSTALLMENTSPLIT
                                            inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
                                            inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID 
                                            where 
                                                INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID and
                                                INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID and
                                                WRITEOFF.DATE <= @ASOFDATE), 0))
                        end
                from dbo.FINANCIALTRANSACTIONLINEITEM
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                where 
                      FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID;

                return @RESULT;
            end