UFN_KPI_CAMPAIGNFUNDRAISER_GOALAMOUNTTOTALINCURRENCY

Measures a campaign fundraiser's goal amount in the given currency.
Return

Return Type
money
Parameters

Parameter	Parameter Type	Mode
@ASOFDATE	datetime	IN
@FUNDRAISERID	uniqueidentifier	IN
@CAMPAIGNID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CURRENCYID	uniqueidentifier	IN
Definition

Copy


            create function dbo.UFN_KPI_CAMPAIGNFUNDRAISER_GOALAMOUNTTOTALINCURRENCY
            (
                @ASOFDATE datetime,
                @FUNDRAISERID uniqueidentifier,
                @CAMPAIGNID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @CURRENCYID uniqueidentifier
            )
            returns money
            with execute as caller
            as begin
                declare @VALUE money

                if @ENDDATE is null or datediff(day, @ASOFDATE, @ENDDATE) > 0
                begin
                    set @ENDDATE = @ASOFDATE;
                end

                select
                    @VALUE = coalesce(sum(dbo.UFN_REVENUESOLICITOR_GETAMOUNTINCURRENCY(REVENUESOLICITOR.ID, @CURRENCYID)), 0)
                from dbo.REVENUESOLICITOR
                    inner join dbo.REVENUESPLIT on REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID
                    inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID    
                    left outer join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                where (
                        REVENUE.TRANSACTIONTYPECODE in (1,6)  --Pledges or grant awards

                            or (
                                REVENUE.TRANSACTIONTYPECODE = 0  --Payments...

                                    and not REVENUESPLIT.APPLICATIONCODE in (2,7,8)  --sans pledge, MGC, and grant award payments

                            )
                            or(
                                REVENUE.TRANSACTIONTYPECODE = 4  --Planned gifts with a payment

                                    and exists (
                                        select 1
                                        from INSTALLMENTPAYMENT PGINSTALLMENT
                                            inner join REVENUESPLIT PGREVENUESPLIT on PGREVENUESPLIT.ID = PGINSTALLMENT.PAYMENTID
                                            inner join REVENUE PGREVENUE on PGREVENUE.ID = PGREVENUESPLIT.REVENUEID
                                        where PGINSTALLMENT.PLEDGEID=REVENUE.ID 
                                            and (PGREVENUE.DATE >= @STARTDATE or @STARTDATE is null)
                                            and (PGREVENUE.DATE <= @ENDDATE or @ENDDATE is null)
                                    )
                            )
                    )
                    and REVENUESOLICITOR.CONSTITUENTID = @FUNDRAISERID
                    and REVENUESPLITCAMPAIGN.CAMPAIGNID = @CAMPAIGNID
                    and (@STARTDATE <= REVENUE.DATE or @STARTDATE is null)
                    and (REVENUE.DATE <= @ENDDATE or @ENDDATE is null);


                return @VALUE
            end