USP_DATALIST_STEWARDSHIPPLANSTEWARDS

This datalist returns all stewards for a stewardship plan.

Parameters

Parameter	Parameter Type	Mode	Description
@STEWARDSHIPPLANID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@VIEWINACTIVE	bit	IN	Show prior stewards

Definition

Copy



CREATE procedure dbo.USP_DATALIST_STEWARDSHIPPLANSTEWARDS
(
    @STEWARDSHIPPLANID uniqueidentifier = null,
    @VIEWINACTIVE bit = 0
)
as
    set nocount on;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    declare @MINCOMPARERESULT int;
    set @MINCOMPARERESULT = case @VIEWINACTIVE when 0 then 0 else 1 end;

    select
        STEWARDSHIPPLANSTEWARD.ID,
        STEWARDSHIPPLANSTEWARD.CONSTITUENTID,
        NF.NAME,
        coalesce(STEWARDSHIPSTEWARDROLECODE.DESCRIPTION, '') as ROLE,
        STEWARDSHIPPLANSTEWARD.STARTDATE,
        STEWARDSHIPPLANSTEWARD.ENDDATE,
        case dbo.UFN_DATE_COMPARETODATERANGE(@CURRENTDATE, STEWARDSHIPPLANSTEWARD.STARTDATE, STEWARDSHIPPLANSTEWARD.ENDDATE) when -1 then 'Future' when 0 then 'Current' when 1 then 'Prior' else '' end TIMEFRAME
    from dbo.STEWARDSHIPPLANSTEWARD    
    inner join dbo.CONSTITUENT
        on CONSTITUENT.ID = STEWARDSHIPPLANSTEWARD.CONSTITUENTID    
    left outer join dbo.STEWARDSHIPSTEWARDROLECODE
        on STEWARDSHIPSTEWARDROLECODE.ID = STEWARDSHIPPLANSTEWARD.ROLECODEID    
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF    
    where STEWARDSHIPPLANSTEWARD.PLANID = @STEWARDSHIPPLANID and 
        dbo.UFN_DATE_COMPARETODATERANGE(@CURRENTDATE, STEWARDSHIPPLANSTEWARD.STARTDATE, STEWARDSHIPPLANSTEWARD.ENDDATE) <= @MINCOMPARERESULT
    order by
        TIMEFRAME, NAME, ROLE;