UFN_PLEDGE_CALCULATECONTRIBUTEDINCOMEBALANCE

            CREATE function dbo.UFN_PLEDGE_CALCULATECONTRIBUTEDINCOMEBALANCE
            (
                @PLEDGEID uniqueidentifier
            )
            returns money
            with execute as caller
            as begin
                declare @CONTRIBUTEDAMOUNT money = 0

                select @CONTRIBUTEDAMOUNT = sum(CALCULATED.AMOUNT)
                from (select sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT)
                            - coalesce((select sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
                                    from dbo.INSTALLMENTSPLITPAYMENT 
                                    inner join INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                    where INSTALLMENTSPLITPAYMENT.PLEDGEID = @PLEDGEID and INSTALLMENTSPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID), 0) 
                                - coalesce((select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) 
                                    from dbo.WRITEOFF 
                                    inner join dbo.INSTALLMENTSPLITWRITEOFF on WRITEOFF.ID = INSTALLMENTSPLITWRITEOFF.WRITEOFFID 
                                    inner join INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                where WRITEOFF.REVENUEID = @PLEDGEID and INSTALLMENTSPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID), 0) as AMOUNT
                from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID 
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                    and REVENUESPLIT_EXT.DESIGNATIONID is not null
                group by FINANCIALTRANSACTIONLINEITEM.ID) CALCULATED

                return @CONTRIBUTEDAMOUNT
            end