USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSUMMARYOPPORTUNITIES

Fetches summary information for a fundraiser's opportunities and asks.
Parameters

Parameter	Parameter Type	Mode	Description
@FUNDRAISERID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSUMMARYOPPORTUNITIES(@FUNDRAISERID uniqueidentifier)
                as begin
                    set nocount on;

                    declare @QUALIFIEDOPPORTUNITIESCOUNT int;
                    declare @QUALIFIEDOPPORTUNITIESAMOUNT money;
                    declare @OVERDUEASKSCOUNT int;
                    declare @OVERDUEASKSAMOUNT money;

                    select
                        @QUALIFIEDOPPORTUNITIESCOUNT = count(*),
                        @QUALIFIEDOPPORTUNITIESAMOUNT = coalesce(sum(O.AMOUNT), 0)
                    from 
                        dbo.OPPORTUNITY O
                        inner join dbo.PROSPECTPLAN PP on PP.ID=O.PROSPECTPLANID
                    where
                        O.STATUSCODE in (1,2)
                        and PP.PRIMARYMANAGERFUNDRAISERID=@FUNDRAISERID;

                    declare @FUZZYTODAY dbo.UDT_FUZZYDATE;
                    set @FUZZYTODAY = convert(nvarchar(8), getdate(), 112);

                    select
                        @OVERDUEASKSCOUNT = count(*),
                        @OVERDUEASKSAMOUNT = coalesce(sum(O.AMOUNT), 0)
                    from 
                        dbo.OPPORTUNITY O
                        inner join dbo.PROSPECTPLAN PP on PP.ID=O.PROSPECTPLANID
                    where
                        O.STATUSCODE=2
                        and O.RESPONSEDATE<@FUZZYTODAY
                        and PP.PRIMARYMANAGERFUNDRAISERID=@FUNDRAISERID;

                    select 
                        @QUALIFIEDOPPORTUNITIESCOUNT,
                        @QUALIFIEDOPPORTUNITIESAMOUNT,
                        @OVERDUEASKSCOUNT,
                        @OVERDUEASKSAMOUNT

                    return 0;
                end