UFN_CAMPAIGNPRIORITY_GETPLEDGEBALANCEINCURRENCY

      create function [dbo].UFN_CAMPAIGNPRIORITY_GETPLEDGEBALANCEINCURRENCY (
          @HIERARCHYPATH hierarchyid,
          @PRIORITYID uniqueidentifier,
          @STARTDATE datetime = null,
          @ENDDATE datetime = null,
          @CURRENCYID uniqueidentifier = null
      ) returns table as
      return
      (
          with CAMPAIGNSUBPRIORITY_CTE as (
              select 
                      ID
                  from
                      CAMPAIGNSUBPRIORITY
                  where
                      CAMPAIGNPRIORITYID = @PRIORITYID
          ),        
          CAMPAIGNPRIORITYREVENUESPLIT_CTE as (
              select 
                  distinct REVENUESPLITCAMPAIGN.REVENUESPLITID
              from
                  dbo.REVENUESPLITCAMPAIGN
              inner join
                  dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
              inner join 
                  CAMPAIGNSUBPRIORITY_CTE on REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID = CAMPAIGNSUBPRIORITY_CTE.ID            
              where
                  CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
          ),
          CAMPAIGNREVENUE_CTE as (
              select
                  distinct REVENUE.ID [REVENUEID]
              from
                  dbo.REVENUE
              inner join
                  dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
              inner join
                  CAMPAIGNPRIORITYREVENUESPLIT_CTE on REVENUESPLIT.ID = CAMPAIGNPRIORITYREVENUESPLIT_CTE.REVENUESPLITID
          )
          select 
              coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID, @CURRENCYID)), 0)
                  -
              (
              coalesce(( --Subtract Writeoffs of the above pledges

                  select 
                      sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WOS.ID, @CURRENCYID))
                  from 
                      dbo.WRITEOFFSPLIT WOS
                  inner join 
                      dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
                  inner join 
                      dbo.REVENUE R on WO.REVENUEID = R.ID
                  inner join
                      CAMPAIGNREVENUE_CTE on R.ID = CAMPAIGNREVENUE_CTE.REVENUEID                            
                  where 
                      (R.DATE >= @STARTDATE or @STARTDATE is null) 
                  and
                      (R.DATE <= @ENDDATE or @ENDDATE is null) 
                  and  -- Bug 16153 - AdamBu - Remove matching gift claims from this calculation

                      R.TRANSACTIONTYPECODE in (1,6)
              ), 0)) [PLEDGEBALANCE]
              from 
                  dbo.REVENUESPLIT RDS
              inner join 
                  dbo.REVENUE R on R.ID = RDS.REVENUEID
              inner join
                  CAMPAIGNPRIORITYREVENUESPLIT_CTE on RDS.ID = CAMPAIGNPRIORITYREVENUESPLIT_CTE.REVENUESPLITID
              where
                  (R.DATE >= @STARTDATE or @STARTDATE is null) 
              and
                  (R.DATE <= @ENDDATE or @ENDDATE is null) 
              and -- Bug 16153 - AdamBu - Remove matching gift claims from this calculation and only include planned gifts if they also have a payment. (JamesWill Earth -- Remove planned gifts entirely)

                  R.TRANSACTIONTYPECODE in (1,6) --Pledges and Grant Awards

      )