USP_DATALIST_PROSPECTPLANSWITHACCEPTEDOPPORTUNITIES

List of prospect plans for the given constituent with one or more accepted opportunities.

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_PROSPECTPLANSWITHACCEPTEDOPPORTUNITIES
(
    @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
    inner join dbo.OPPORTUNITY
        on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
    where 
        PROSPECTPLAN.PROSPECTID = @CONSTITUENTID
        and OPPORTUNITY.STATUSCODE = 3                -- Accepted

        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