USP_DATALIST_PROSPECT_PROSPECTPLANS

List of prospect plans.
Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@PROSPECTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@INCLUDEINACTIVEPLANS	bit	IN	Include historical plans
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@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_PROSPECTPLANS
(
    @CURRENTAPPUSERID uniqueidentifier,
    @PROSPECTID uniqueidentifier,
    @INCLUDEINACTIVEPLANS bit = 0,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
) as begin
    set nocount on;


    declare @PROSPECTIDS table
    (
      ID uniqueidentifier,
      NAME nvarchar(700),
      PLANID uniqueidentifier
    );

    insert into @PROSPECTIDS
    select distinct
        PROSPECTPLAN.PROSPECTID ID,
        NF.NAME NAME,
        PROSPECTPLAN.ID PLANID
    from
        dbo.PROSPECTPLAN 
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PROSPECTID) NF
    where
        PROSPECTPLAN.PROSPECTID = @PROSPECTID

    union all

    select distinct
        PROSPECTPLAN.PROSPECTID ID,
        NF.NAME NAME,
        PROSPECTPLAN.ID PLANID
    from dbo.PLANPARTICIPANT
        inner join dbo.PROSPECTPLAN on PLANPARTICIPANT.PROSPECTPLANID = PROSPECTPLAN.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PROSPECTID) NF
    where PLANPARTICIPANT.CONSTITUENTID = @PROSPECTID

    declare @STEWARDIDS table
    (
      ID uniqueidentifier,
      NAME nvarchar(700),
      PLANID uniqueidentifier
    );

    insert into @STEWARDIDS
    select distinct
        STEWARDSHIPPLAN.CONSTITUENTID ID,
        NF.NAME NAME,
        STEWARDSHIPPLAN.ID PLANID
    from
        dbo.STEWARDSHIPPLAN 
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.CONSTITUENTID) NF
    where
        STEWARDSHIPPLAN.CONSTITUENTID = @PROSPECTID

    union all

    select distinct
        STEWARDSHIPPLAN.CONSTITUENTID ID,
        NF.NAME NAME,
        STEWARDSHIPPLAN.ID PLANID
    from dbo.STEWARDSHIPPLANSTEWARD 
        left join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLANSTEWARD.PLANID = STEWARDSHIPPLAN.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.CONSTITUENTID) NF
    where STEWARDSHIPPLANSTEWARD.CONSTITUENTID = @PROSPECTID

    select distinct
        PP.ID,
        'C48113CC-888F-44da-ACE1-F22A4420CC90' as VIEWDATAFORMID,
        NI.ID as NEXTINTERACTIONID,
        PP.ISACTIVE,
        0 as ISSTEWARDSHIPPLAN,
        dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) as PLANTYPE,
        PP.NAME as PLANNAME,
        dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID) SITES,
        PROSPECT.NAME CONSTITUENTNAME,
        PP.STARTDATE,
        PP.DATEADDED
    from dbo.PROSPECTPLAN PP
    inner join @PROSPECTIDS PROSPECT on PP.PROSPECTID = PROSPECT.ID and PROSPECT.PLANID = PP.ID
    left outer join dbo.INTERACTION NI on NI.ID = dbo.UFN_PROSPECTPLAN_GETNEXTSTEP(PP.ID)
    where
        (@INCLUDEINACTIVEPLANS=1 or PP.ISACTIVE=1) 
        and (
            select count(*) 
            from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PLANSITE 
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANSITE].[SITEID] or (SITEID is null and [PLANSITE].[SITEID] is null)))
        ) > 0
        and (
            @SITEFILTERMODE = 0
                or PP.ID in (
                    select PROSPECTPLANSITE.PROSPECTPLANID
                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                        inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
                )
        )

    union all

    select distinct
        STEWARDSHIPPLAN.ID,
        '30D20C5C-DF18-4DBD-AFDF-4A4034834563' as VIEWDATAFORMID,
        null as NEXTINTERACTIONID,
        STEWARDSHIPPLAN.ISACTIVE as ISACTIVE,
        1 as ISSTEWARDSHIPPLAN,
        'Stewardship' as PLANTYPE,
        STEWARDSHIPPLAN.NAME as PLANNAME,
        dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLAN.ID) SITES,
        STEWARD.NAME CONSTITUENTNAME,
        STEWARDSHIPPLAN.STARTDATE,
        STEWARDSHIPPLAN.DATEADDED
    from dbo.STEWARDSHIPPLAN
    inner join @STEWARDIDS STEWARD on STEWARDSHIPPLAN.CONSTITUENTID = STEWARD.ID and STEWARD.PLANID = STEWARDSHIPPLAN.ID
    where
        (@INCLUDEINACTIVEPLANS=1 or STEWARDSHIPPLAN.ISACTIVE=1) 
        and (
            select count(*) 
            from dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID(STEWARDSHIPPLAN.ID) as PLANSITE 
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANSITE].[SITEID] or (SITEID is null and [PLANSITE].[SITEID] is null)))
        ) > 0
        and (
            @SITEFILTERMODE = 0
                or STEWARDSHIPPLAN.ID in (
                    select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                        inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
                )
        )
    order by PLANTYPE, CONSTITUENTNAME

end