USP_DATALIST_STEWARDSHIPPLANS_GROUP

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

Parameter	Parameter Type	Mode	Description
@GROUPID	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.
Definition

Copy


              CREATE procedure dbo.USP_DATALIST_STEWARDSHIPPLANS_GROUP
              (
                @GROUPID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED xml = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null
              )
              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 
                      STEWARDSHIPPLAN.CONSTITUENTID in 
                          (
                              select @GROUPID

                              union all

                              (select ID from dbo.UFN_GROUP_GETCURRENTMEMBERSNOPERMISSIONCHECK(@GROUPID))
                          )

                  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 = @GROUPID

                  select 
                    STEWARDSHIPPLAN.ID,
                    NF.NAME CONSTITUENTNAME,
                    STEWARDSHIPPLAN.NAME,
                    NF_MANAGER.NAME MANAGER,
                    dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLAN.ID) as SITESLIST,
                    dbo.UFN_STEWARDSHIPPLANTYPECODE_GETDESCRIPTION(STEWARDSHIPPLAN.PLANTYPECODEID) as PLANTYPE,
                    dbo.UFN_STEWARDSHIPPLANSUBTYPECODE_GETDESCRIPTION(STEWARDSHIPPLAN.PLANSUBTYPECODEID) as PLANSUBTYPE,
                    STEWARDSHIPPLAN.STARTDATE,
                    STEWARDSHIPPLAN.DATEADDED
                  from dbo.STEWARDSHIPPLAN
                  inner join @STEWARDIDS STEWARD on STEWARDSHIPPLAN.CONSTITUENTID = STEWARD.ID and STEWARD.PLANID = STEWARDSHIPPLAN.ID
                  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTID) NF
                  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.MANAGERID) NF_MANAGER
                  where 
                    (
                        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, 
                    NF.NAME, 
                    STEWARDSHIPPLAN.NAME, 
                    NF_MANAGER.NAME, 
                    STEWARDSHIPPLAN.PLANTYPECODEID, 
                    STEWARDSHIPPLAN.PLANSUBTYPECODEID,
                    STEWARDSHIPPLAN.STARTDATE,
                    STEWARDSHIPPLAN.DATEADDED
                  order by CONSTITUENTNAME, STEWARDSHIPPLAN.NAME