USP_DATALIST_COMMUNITYMEMBERTEAMFUNDRAISINGSPONSOR

This datalist returns a list of all sponsors that donated to a specific Blackbaud Internet Solutions team fundraiser.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTIDAPPEALID	nvarchar(72)	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_COMMUNITYMEMBERTEAMFUNDRAISINGSPONSOR
                (
                    @CONSTITUENTIDAPPEALID nvarchar(72)
                )
                as
                    set nocount on;

                    declare @CONSTITUENTID uniqueidentifier;
                    declare @APPEALID uniqueidentifier;

                    set @CONSTITUENTID = left(@CONSTITUENTIDAPPEALID, 36);
                    if len(@CONSTITUENTIDAPPEALID) = 72
                        set @APPEALID = right(@CONSTITUENTIDAPPEALID, 36);

                    declare @NETCOMMUNITYREVENUENOTETYPECODEID uniqueidentifier;
                    select top (1)
                        @NETCOMMUNITYREVENUENOTETYPECODEID = REVENUENOTETYPECODEID
                    from
                        dbo.NETCOMMUNITYDEFAULTCODEMAP;

                    select
                        CONSTITUENT.ID [DONORCONSTITUENTID],
                        CONSTITUENT.NAME [DONORNAME],
                        REVENUE.ID [REVENUEID],
                        REVENUE.DATE,
                        REVENUESOLICITOR.AMOUNT,
                        (
                            select top 1
                                [REVENUENOTE].[TEXTNOTE]
                            from
                                dbo.REVENUENOTE
                            where
                                [REVENUENOTE].[REVENUEID] = [REVENUE].[ID]
                                and [REVENUENOTE].[REVENUENOTETYPECODEID] = @NETCOMMUNITYREVENUENOTETYPECODEID
                            order by
                                [REVENUENOTE].[DATEADDED]
                        ) as [BBNCCOMMENT]
                    from
                        dbo.TEAMFUNDRAISER
                        inner join dbo.REVENUESOLICITOR on TEAMFUNDRAISER.CONSTITUENTID = REVENUESOLICITOR.CONSTITUENTID
                        inner join dbo.REVENUESPLIT on REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID
                        inner join dbo.REVENUE on
                            REVENUESPLIT.REVENUEID = REVENUE.ID
                            and TEAMFUNDRAISER.APPEALID = REVENUE.APPEALID
                        inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
                    where
                        TEAMFUNDRAISER.CONSTITUENTID = @CONSTITUENTID
                        and TEAMFUNDRAISER.APPEALID = @APPEALID
                        and
                        ( -- TMV 10/04/2007 CR284708-100307 Don't double count pledge payments

                            (REVENUE.TRANSACTIONTYPECODE = 1 and REVENUESPLIT.APPLICATIONCODE = 0) --Pledge

                            or
                            (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0, 3)) --Payment (Gift or Recurring gift payment)

                        )
                    order by
                        REVENUE.DATE desc;