UFN_CAMPAIGN_GETPLEDGEBALANCEINCURRENCY

            CREATE function dbo.UFN_CAMPAIGN_GETPLEDGEBALANCEINCURRENCY (
                @HIERARCHYPATH hierarchyid,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @CURRENCYID uniqueidentifier = null,
                @ORGANIZATIONCURRENCYID  uniqueidentifier = null
            ) returns table as
            return
                with CAMPAIGNREVENUESPLIT_CTE as ( 
                    select distinct 
                        REVENUESPLIT.ID as REVENUESPLITID,
                        REVENUESPLIT.REVENUEID,
                        REVENUESPLIT.DESIGNATIONID
                    from
                        dbo.REVENUESPLITCAMPAIGN
                        inner join dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
                        inner join dbo.REVENUESPLIT on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
                        inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                    where
                        CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
                        and (REVENUE.DATE >= @STARTDATE or @STARTDATE is null) 
                        and (REVENUE.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)

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

                ),
                TOTALREVENUE_CTE as (
                    select
                        case when @CURRENCYID = coalesce(@ORGANIZATIONCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()) then
                            RDS.ORGANIZATIONAMOUNT
                        when @CURRENCYID = RDS.BASECURRENCYID then
                            RDS.AMOUNT
                        else
                            dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID, @CURRENCYID)
                        end as [TOTALAMOUNT]
                    from 
                        dbo.REVENUESPLIT RDS
                        inner join CAMPAIGNREVENUESPLIT_CTE on RDS.ID = CAMPAIGNREVENUESPLIT_CTE.REVENUESPLITID
                ),

                TOTALWRITEOFFS_CTE as (

                     --Subtract Writeoffs of the above pledges

                        select 
                            case when @CURRENCYID = coalesce(@ORGANIZATIONCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()) then
                                WOS.ORGANIZATIONAMOUNT
                            when @CURRENCYID = WOS.BASECURRENCYID then
                                WOS.AMOUNT
                            else
                                dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WOS.ID, @CURRENCYID)
                            end as [WRITEOFFAMOUNT]

                        from 
                            dbo.WRITEOFFSPLIT WOS
                            inner join dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
                            inner join CAMPAIGNREVENUESPLIT_CTE on WO.REVENUEID = CAMPAIGNREVENUESPLIT_CTE.REVENUEID
                        where
                            WOS.DESIGNATIONID = CAMPAIGNREVENUESPLIT_CTE.DESIGNATIONID
                )                

                select 
                    (select coalesce(sum(TOTALREVENUE_CTE.TOTALAMOUNT),0) from TOTALREVENUE_CTE) 
                    - 
                    (select coalesce(sum(TOTALWRITEOFFS_CTE.WRITEOFFAMOUNT),0) from TOTALWRITEOFFS_CTE) as PLEDGEBALANCE