UFN_PLEDGE_CALCULATEEARNEDINCOMEBALANCE

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

                select @EARNEDAMOUNT = 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.TYPECODE in (2,18) 
                            and REVENUESPLIT_EXT.APPLICATIONCODE in (5, 18)
                        group by FINANCIALTRANSACTIONLINEITEM.ID) CALCULATED

                return @EARNEDAMOUNT
            end