UFN_CAMPAIGNPRIORITY_GETTOTALPLANNEDGIFTSINCURRENCY

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

                with CAMPAIGNPRIORITY_CTE as(
                    select distinct
                        REVENUESPLIT.ID,
                        dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID) as AMOUNT,
                        CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID
                    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 = 4
                        and (REVENUE.DATE >= @STARTDATE or @STARTDATE is null)
                        and (REVENUE.DATE <= @ENDDATE or @ENDDATE is null))
                select
                    @RESULT = coalesce(sum(CAMPAIGNPRIORITY_CTE.AMOUNT), 0)
                from
                    dbo.CAMPAIGNPRIORITY
                inner join CAMPAIGNPRIORITY_CTE on CAMPAIGNPRIORITY_CTE.CAMPAIGNPRIORITYID = CAMPAIGNPRIORITY.ID;

                return @RESULT;
            end