USP_DATALIST_STEWARDSHIPPLANTEMPLATES

List of stewardship plan templates.

Parameters

Parameter	Parameter Type	Mode	Description
@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_STEWARDSHIPPLANTEMPLATES
(
@CURRENTAPPUSERID uniqueidentifier = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as begin
    set nocount on;

    select 
        STEWARDSHIPPLANTEMPLATE.ID,
        STEWARDSHIPPLANTEMPLATE.NAME,
        (select count(ID) from dbo.STEWARDSHIPPLANTEMPLATESTEP where TEMPLATEID = STEWARDSHIPPLANTEMPLATE.ID) as NUMSTEPS,
        dbo.UFN_STEWARDSHIPPLANTEMPLATE_GETSITELIST(STEWARDSHIPPLANTEMPLATE.ID) SITELIST
    from
        dbo.STEWARDSHIPPLANTEMPLATE
      where 
           (
            select 
                count(*) 
            from 
                dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANTEMPLATEID(STEWARDSHIPPLANTEMPLATE.ID) as STEWARDSHIPPLANTEMPLATESITE 
            where 
                (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[STEWARDSHIPPLANTEMPLATESITE].[SITEID] or (SITEID is null and [STEWARDSHIPPLANTEMPLATESITE].[SITEID] is null)))
            ) > 0
        and (
            @SITEFILTERMODE = 0                                
            or 
            STEWARDSHIPPLANTEMPLATE.ID in 
                (
                    select 
                        STEWARDSHIPPLANTEMPLATESITE.STEWARDSHIPPLANTEMPLATEID
                    from 
                        dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                        inner join dbo.STEWARDSHIPPLANTEMPLATESITE on STEWARDSHIPPLANTEMPLATESITE.SITEID = SITEFILTER.SITEID
                )
            )

    group by STEWARDSHIPPLANTEMPLATE.ID, STEWARDSHIPPLANTEMPLATE.NAME

    order by NAME
end