UFN_CAMPAIGN_GETPLEDGEBALANCE

            CREATE function dbo.UFN_CAMPAIGN_GETPLEDGEBALANCE (
                @HIERARCHYPATH hierarchyid,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null
            ) returns table as
            return
                with CAMPAIGNREVENUESPLIT_CTE as (
                    select 
                        distinct REVENUESPLITCAMPAIGN.REVENUESPLITID
                    from
                        dbo.REVENUESPLITCAMPAIGN
                    inner join
                        dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.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
                        CAMPAIGNREVENUESPLIT_CTE on REVENUESPLIT.ID = CAMPAIGNREVENUESPLIT_CTE.REVENUESPLITID
                )
                select 
                    coalesce(sum(RDS.ORGANIZATIONAMOUNT), 0)
                        -
                    (
                    coalesce(( --Subtract Writeoffs of the above pledges

                        select 
                            sum(WOS.ORGANIZATIONAMOUNT)
                        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
                        CAMPAIGNREVENUESPLIT_CTE on RDS.ID = CAMPAIGNREVENUESPLIT_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