USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSSUMMARYFUTURE

Fetches summary information for opportunities and asks.
Parameters

Parameter	Parameter Type	Mode	Description
@DATEFILTER	tinyint	IN
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ORGPOSITIONSSELECTIONID	uniqueidentifier	IN
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSSUMMARYFUTURE
                (
                    @DATEFILTER tinyint, 
                    @CURRENTAPPUSERID uniqueidentifier = null, 
                    @ORGPOSITIONSSELECTIONID uniqueidentifier = null
                )
                as begin
                    set nocount on;

                    declare @STARTDATE datetime;
                    declare @ENDDATE datetime;

                    exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER, @STARTDATE output, @ENDDATE output;

                    declare @OUTSTANDINGASKSCOUNT int;
                    declare @OUTSTANDINGASKSAMOUNT money;

                    if @ORGPOSITIONSSELECTIONID is null
                        select
                            @OUTSTANDINGASKSCOUNT = count(*),
                            @OUTSTANDINGASKSAMOUNT = coalesce(sum(O.AMOUNT), 0)
                        from 
                            dbo.OPPORTUNITY O
                        where
                            O.STATUSCODE=2
                            and (O.RESPONSEDATE is null
                                or (O.RESPONSEDATE>=convert(nvarchar(8), getdate(), 112)
                                and O.RESPONSEDATE>=convert(nvarchar(8), @STARTDATE, 112) 
                                and O.RESPONSEDATE<=convert(nvarchar(8), @ENDDATE, 112))
                            );
                    else
                    begin
                        declare @IDS as table(ID uniqueidentifier);
                        insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;

                        select
                            @OUTSTANDINGASKSCOUNT = count(*),
                            @OUTSTANDINGASKSAMOUNT = coalesce(sum(O.AMOUNT), 0)
                        from 
                            dbo.OPPORTUNITY O
                        inner join dbo.PROSPECTPLAN PP
                            on O.PROSPECTPLANID = PP.ID
                        inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH 
                            on OPH.CONSTITUENTID = PP.PRIMARYMANAGERFUNDRAISERID and O.RESPONSEDATE between OPH.DATEFROM and coalesce(OPH.DATETO, O.RESPONSEDATE)
                        inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
                        where
                            O.STATUSCODE=2
                            and (O.RESPONSEDATE is null
                                or (O.RESPONSEDATE>=convert(nvarchar(8), getdate(), 112)
                                and O.RESPONSEDATE>=convert(nvarchar(8), @STARTDATE, 112) 
                                and O.RESPONSEDATE<=convert(nvarchar(8), @ENDDATE, 112))
                            );
                    end

                    select 
                        @OUTSTANDINGASKSCOUNT,
                        @OUTSTANDINGASKSAMOUNT

                    return 0;
                end