UFN_CAMPAIGNPRIORITY_GETTOTALBALANCE

            CREATE function dbo.UFN_CAMPAIGNPRIORITY_GETTOTALBALANCE
            (
                @CAMPAIGNPRIORITYID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null
            )
            returns money
            as
            begin
                declare @RESULT money;

                with CAMPAIGNPRIORITY_CTE as (
                    select distinct
                        REVENUESPLIT.REVENUEID
                    from
                        dbo.REVENUESPLIT
                    inner join dbo.REVENUE with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID 
                    inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
                    inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID 
                    inner join dbo.CAMPAIGNPRIORITY on CAMPAIGNPRIORITY.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
                    where
                        CAMPAIGNPRIORITY.ID = @CAMPAIGNPRIORITYID
                        and REVENUE.TRANSACTIONTYPECODE in (1, 6)
                        and (REVENUE.DATE >= @STARTDATE or @STARTDATE is null)
                        and (REVENUE.DATE <= @ENDDATE or @ENDDATE is null))
                select
                    @RESULT = coalesce(sum(REVENUESPLIT.ORGANIZATIONAMOUNT), 0)
                    -
                    (
                        coalesce(( --Subtract Writeoffs of the above pledges

                                    select
                                        SUM(WRITEOFFSPLIT.ORGANIZATIONAMOUNT)
                                    from
                                        dbo.WRITEOFFSPLIT
                                    inner join dbo.WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.WRITEOFFID
                                    inner join dbo.REVENUE on REVENUE.ID = WRITEOFF.REVENUEID 
                                    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                                    inner join CAMPAIGNPRIORITY_CTE on CAMPAIGNPRIORITY_CTE.REVENUEID = REVENUESPLIT.REVENUEID
                        ), 0)
                    )
                from
                    dbo.REVENUESPLIT
                inner join CAMPAIGNPRIORITY_CTE on CAMPAIGNPRIORITY_CTE.REVENUEID = REVENUESPLIT.REVENUEID;

                return @RESULT;
            end