USP_DATALIST_STEWARDSHIPPLANS

This datalist returns all stewardship plans related to a constituent.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@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.
@INCLUDEINACTIVEPLANS	bit	IN
Definition

Copy


              CREATE procedure dbo.USP_DATALIST_STEWARDSHIPPLANS
              (
                @CONSTITUENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED xml = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null,
                @INCLUDEINACTIVEPLANS bit = 0
              )
              as
                  set nocount on;

                  declare @STEWARDIDS table
                  (
                    ID uniqueidentifier,
                    NAME nvarchar(300),
                    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
                      (@INCLUDEINACTIVEPLANS=1 or STEWARDSHIPPLAN.ISACTIVE=1) AND
                      STEWARDSHIPPLAN.CONSTITUENTID = @CONSTITUENTID

                  union all

                  select distinct
                      STEWARD.ID ID,
                      STEWARD.NAME NAME,
                      STEWARDSHIPPLAN.ID PLANID
                  from dbo.STEWARDSHIPPLANSTEWARD 
                  left join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLANSTEWARD.PLANID = STEWARDSHIPPLAN.ID
                  left join dbo.CONSTITUENT STEWARD on STEWARDSHIPPLAN.CONSTITUENTID = STEWARD.ID
                  where (@INCLUDEINACTIVEPLANS=1 or STEWARDSHIPPLAN.ISACTIVE=1) AND 
                  STEWARDSHIPPLANSTEWARD.CONSTITUENTID = @CONSTITUENTID

                  select 
                    STEWARDSHIPPLAN.ID,
                    STEWARDSHIPPLAN.NAME,
                    MANAGER_NF.NAME MANAGER,
                    dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLAN.ID),
                    NF.NAME CONSTITUENTNAME,
                    STEWARDSHIPPLANTYPECODE.DESCRIPTION as PLANTYPE,
                    STEWARDSHIPPLANSUBTYPECODE.DESCRIPTION as PLANSUBTYPE,
                    STEWARDSHIPPLAN.STARTDATE,
                    STEWARDSHIPPLAN.DATEADDED,
                    STEWARDSHIPPLAN.ISACTIVE
                  from dbo.STEWARDSHIPPLAN
                  inner join @STEWARDIDS STEWARD on STEWARDSHIPPLAN.CONSTITUENTID = STEWARD.ID and STEWARD.PLANID = STEWARDSHIPPLAN.ID
                  left join dbo.STEWARDSHIPPLANTYPECODE on STEWARDSHIPPLANTYPECODE.ID = STEWARDSHIPPLAN.PLANTYPECODEID
                  left join dbo.STEWARDSHIPPLANSUBTYPECODE on STEWARDSHIPPLANSUBTYPECODE.ID = STEWARDSHIPPLAN.PLANSUBTYPECODEID
                  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARD.ID) NF
                  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.MANAGERID) MANAGER_NF
                  where (@INCLUDEINACTIVEPLANS=1 or STEWARDSHIPPLAN.ISACTIVE=1) AND
                    (
                        select 
                            count(*) 
                        from 
                            dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID(STEWARDSHIPPLAN.ID) as STEWARDSHIPPLANSITE 
                        where 
                            (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[STEWARDSHIPPLANSITE].[SITEID] or (SITEID is null and [STEWARDSHIPPLANSITE].[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
                            )
                        )
                  group by 
                    STEWARDSHIPPLAN.ID, 
                    STEWARDSHIPPLAN.ISACTIVE,
                    STEWARDSHIPPLAN.NAME, 
                    MANAGER_NF.NAME,
                    NF.NAME,
                    STEWARDSHIPPLANTYPECODE.DESCRIPTION,
                    STEWARDSHIPPLANSUBTYPECODE.DESCRIPTION,
                    STEWARDSHIPPLAN.STARTDATE,
                    STEWARDSHIPPLAN.DATEADDED
                  order by 
                    STEWARDSHIPPLAN.NAME