USP_DATALIST_PROSPECT_CONSTITUENTGROUPMEMBERPROSPECTPLANS

List of prospect plans for a constituent group's members.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@GROUPID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@INCLUDEINACTIVEPLANS	bit	IN	Include historical plans
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.

Definition

Copy



                CREATE procedure dbo.USP_DATALIST_PROSPECT_CONSTITUENTGROUPMEMBERPROSPECTPLANS
                (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @GROUPID uniqueidentifier,
                    @INCLUDEINACTIVEPLANS bit = 0,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                ) as begin
                set nocount on;

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                select 
                    PP.ID,
                    PP.PROSPECTID,
                    NF_PROSPECT.NAME,
                    PPT.DESCRIPTION,
                    PP.NAME,
                    dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID),
                    NF_PM.NAME,
                    PSC.DESCRIPTION,
                    LI.ACTUALDATE,
                    NI.EXPECTEDDATE
                from dbo.GROUPMEMBER GM
                    inner join dbo.PROSPECTPLAN PP on PP.PROSPECTID = GM.MEMBERID
                    left outer join dbo.PROSPECTPLANTYPECODE PPT on PPT.ID = PP.PROSPECTPLANTYPECODEID
                    left outer join dbo.PROSPECTPLANSTATUSCODE PSC on PSC.ID = PP.PROSPECTPLANSTATUSCODEID
                    left outer join dbo.INTERACTION LI on LI.ID = dbo.UFN_PROSPECTPLAN_GETLASTSTEP(PP.ID)
                    left outer join dbo.INTERACTION NI on NI.ID = dbo.UFN_PROSPECTPLAN_GETNEXTSTEP(PP.ID)
                    left outer join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PROSPECTID) NF_PROSPECT
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PRIMARYMANAGERFUNDRAISERID) NF_PM
                where GM.GROUPID = @GROUPID
                    and (@INCLUDEINACTIVEPLANS = 1 or PP.ISACTIVE = 1)
                    and (
                        select count(*) 
                        from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PROSPECTPLANSITE 
                        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
                    ) > 0
                    and (
                        (GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                        or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE)
                    )

                end