UFN_CAMPAIGNPRIORITY_GETTOTALRECEIVEDINCURRENCY_FORCAMPAIGN

      CREATE function dbo.UFN_CAMPAIGNPRIORITY_GETTOTALRECEIVEDINCURRENCY_FORCAMPAIGN(
          @HIERARCHYPATH hierarchyid,
          @PRIORITYID uniqueidentifier,
          @STARTDATE datetime = null,
          @ENDDATE datetime = null,
          @CURRENCYID uniqueidentifier = null
      ) returns table as

      return 
      (
          with CAMPAIGNPRIORITY_REVENUESPLIT_CTE as (
              select 
                  distinct REVENUESPLITCAMPAIGN.REVENUESPLITID
              from
                  dbo.REVENUESPLITCAMPAIGN
              inner join
                  dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
              inner join 
                  CAMPAIGNSUBPRIORITY on REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID = CAMPAIGNSUBPRIORITY.ID
              where  
                  CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
              and 
                  CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID = @PRIORITYID
          )
          select
              coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID)), 0) TOTALRECEIVED
          from
              dbo.REVENUESPLIT
          inner join
              CAMPAIGNPRIORITY_REVENUESPLIT_CTE on REVENUESPLIT.ID = CAMPAIGNPRIORITY_REVENUESPLIT_CTE.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, 1, 5, 6, 7, 3, 10, 13)
          and 
              (REVENUESPLIT.APPLICATIONCODE not in (10, 1) or REVENUESPLIT.TYPECODE = 0)
      )