UFN_CAMPAIGN_GETTOTALRECEIVED

            CREATE function dbo.UFN_CAMPAIGN_GETTOTALRECEIVED (
                @HIERARCHYPATH hierarchyid,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null
            ) returns table as
            return
                select
                    coalesce(sum(REVENUESPLIT.ORGANIZATIONAMOUNT), 0) TOTALRECEIVED
                from
                    dbo.REVENUESPLIT
                inner join (
                    select 
                        distinct REVENUESPLITCAMPAIGN.REVENUESPLITID
                    from
                        dbo.REVENUESPLITCAMPAIGN
                    inner join
                        dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
                    where
                        CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
                ) as CAMPAIGNREVENUESPLIT on REVENUESPLIT.ID = CAMPAIGNREVENUESPLIT.REVENUESPLITID    
                left outer join
                    dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                where
                    (@STARTDATE <= REVENUE.DATE or @STARTDATE is null)
                and
                    (REVENUE.DATE <= @ENDDATE or @ENDDATE is null)
                and
                    REVENUE.TRANSACTIONTYPECODE = 0
                and
                    REVENUESPLIT.APPLICATIONCODE in (0, 6, 7, 3, 10)
                and 
                    (REVENUESPLIT.APPLICATIONCODE <> 10 or REVENUESPLIT.TYPECODE = 0)