UFN_CAMPAIGN_GETTOTALPLANNEDGIFTSINCURRENCY

Returns total planned gifts for a campaign converting amounts to the specified currency.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@HIERARCHYPATH	hierarchyid	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CURRENCYID	uniqueidentifier	IN
@ORGANIZATIONCURRENCYID	uniqueidentifier	IN

Definition

Copy



            CREATE function dbo.UFN_CAMPAIGN_GETTOTALPLANNEDGIFTSINCURRENCY
            (
                @HIERARCHYPATH hierarchyid,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @CURRENCYID uniqueidentifier = null,
                @ORGANIZATIONCURRENCYID uniqueidentifier = null
            ) 
            returns table as
            return
                with PLANNEDGIFT_CTE as (
                    select
                        case when @CURRENCYID = coalesce(@ORGANIZATIONCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()) then
                            REVENUESPLIT.ORGANIZATIONAMOUNT
                        when @CURRENCYID = REVENUESPLIT.BASECURRENCYID then
                            REVENUESPLIT.AMOUNT
                        else 
                            dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID)
                        end as TOTALPLANNEDGIFTS
                    from
                        dbo.REVENUESPLIT
                        inner join (
                            select 
                                distinct REVENUESPLITCAMPAIGN.REVENUESPLITID
                            from
                                dbo.REVENUESPLITCAMPAIGN
                            inner join
                                dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
                            where
                                CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
                        ) as CAMPAIGNREVENUESPLIT on REVENUESPLIT.ID = CAMPAIGNREVENUESPLIT.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 = 4
                )

                select sum(TOTALPLANNEDGIFTS) as TOTALPLANNEDGIFTS from PLANNEDGIFT_CTE