USP_DATALIST_PROSPECTPLANSVALIDFORSTEWARDSHIPPLAN

List of prospect plans for the given constituent who is associated with a stewardship plan.

Parameters

Parameter	Parameter Type	Mode	Description
@STEWARDSHIPPLANID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@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_PROSPECTPLANSVALIDFORSTEWARDSHIPPLAN
        (
            @STEWARDSHIPPLANID uniqueidentifier = null,
            @CURRENTAPPUSERID uniqueidentifier,
            @SECURITYFEATUREID uniqueidentifier,
            @SECURITYFEATURETYPE tinyint
        )
        as
        begin
            set nocount on;

            declare @CONSTITUENTID uniqueidentifier;
            select @CONSTITUENTID = CONSTITUENTID from dbo.STEWARDSHIPPLAN where ID = @STEWARDSHIPPLANID

            select
                distinct(PROSPECTPLAN.ID) as VALUE,
                PROSPECTPLAN.NAME as LABEL
            from 
                dbo.PROSPECTPLAN
            where 
                PROSPECTPLAN.PROSPECTID = @CONSTITUENTID
                and (
                    select count(*) 
                    from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.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

            order by PROSPECTPLAN.NAME

        end