UFN_APPEAL_GETTOTALREVENUE

CREATE function dbo.UFN_APPEAL_GETTOTALREVENUE(@ID uniqueidentifier)
returns money
with execute as caller
as
begin
    return
        coalesce(
            ( 
                select
                    sum(REVENUESPLIT.AMOUNT)
                from
                    dbo.REVENUE 
                    inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
                where 
                    (REVENUE.APPEALID = @ID)
                    and (
                        (REVENUE.TRANSACTIONTYPECODE = 1)
                        or
                        (REVENUE.TRANSACTIONTYPECODE = 7)
                        or (
                            (REVENUE.TRANSACTIONTYPECODE = 0) 
                            and (REVENUESPLIT.APPLICATIONCODE in (0, 3, 7) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0))
                        )
                    )
            ), 0)
        - 
        -- Subtract write-offs of the above pledges

        coalesce(
            ( 
                select
                    sum(INSTALLMENTWRITEOFF.AMOUNT)
                from
                    dbo.INSTALLMENTWRITEOFF
                inner join
                    dbo.WRITEOFF on WRITEOFF.ID = INSTALLMENTWRITEOFF.WRITEOFFID
                inner join
                    dbo.REVENUE on REVENUE.ID = WRITEOFF.REVENUEID
                where 
                    REVENUE.APPEALID = @ID
                    and REVENUE.TRANSACTIONTYPECODE in (1, 7)
            ), 0)
end