UFN_PLEDGE_GETDESIGNATIONBALANCE

            CREATE function dbo.UFN_PLEDGE_GETDESIGNATIONBALANCE
            (
                @PLEDGEID uniqueidentifier,
                @DESIGNATIONID uniqueidentifier
            )
            returns money
            with execute as caller
            as begin
                declare @RESULT money = 0

                select
                    @RESULT = 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 and
                                        (INSTALLMENTSPLIT.DESIGNATIONID = @DESIGNATIONID or @DESIGNATIONID is null)
                                ) ,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 and
                                        (INSTALLMENTSPLIT.DESIGNATIONID = @DESIGNATIONID or @DESIGNATIONID is null)
                                ) ,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 = @DESIGNATIONID or @DESIGNATIONID is null)
                        group by FINANCIALTRANSACTIONLINEITEM.ID) CALCULATED

                return @RESULT
            end