USP_SEARCHLIST_STEWARDSHIPPLANSTEP

Search for stewardship plan steps

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CONSTITUENTID	uniqueidentifier	IN	Constituent
@CONTACTPERSONID	uniqueidentifier	IN	Contact person
@PLANNAME	nvarchar(100)	IN	Plan name
@OBJECTIVE	nvarchar(100)	IN	Step objective
@TARGETDATE_FROM	date	IN	Target date from
@TARGETDATE_TO	date	IN	To
@CONTACTMETHODCODEID	uniqueidentifier	IN	Contact method
@ASSIGNEDTO	uniqueidentifier	IN	Assigned to
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@INCLUDEHISTORICAL	bit	IN

Definition

Copy



CREATE procedure dbo.USP_SEARCHLIST_STEWARDSHIPPLANSTEP
(
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier = null,
    @CONTACTPERSONID uniqueidentifier = null,
    @PLANNAME nvarchar(100) = null, 
    @OBJECTIVE nvarchar(100) = null,
    @TARGETDATE_FROM date = null,
    @TARGETDATE_TO date = null,
    @CONTACTMETHODCODEID uniqueidentifier = null,
    @ASSIGNEDTO uniqueidentifier = null,
    @MAXROWS smallint = 500,
    @INCLUDEHISTORICAL bit = 0
)
as begin
    set nocount on;

    select top(@MAXROWS)
        STEWARDSHIPPLANSTEP.ID,
        NF_CONSTITUENT.NAME "CONSTITUENT",
        STEWARDSHIPPLAN.NAME "PLAN",
        STEWARDSHIPPLANSTEP.OBJECTIVE,
        STEWARDSHIPSTEPCATEGORYCODE.DESCRIPTION as CATEGORY,
        STEWARDSHIPPLANSTEP.TARGETDATE,
        INTERACTIONTYPECODE.DESCRIPTION as CONTACTMETHOD,
        NF_CONTACT.NAME "CONTACTPERSON",
        NF_ASSIGNEDTO.NAME "ASSIGNEDTO",
        dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLAN.ID) SITES,
        CASE WHEN STEWARDSHIPPLAN.ISACTIVE=1 THEN 0 ELSE 1 END "HISTORICAL"
    from
        dbo.STEWARDSHIPPLANSTEP
    inner join dbo.STEWARDSHIPPLAN
      on STEWARDSHIPPLAN.ID = STEWARDSHIPPLANSTEP.PLANID
    left outer join dbo.INTERACTIONTYPECODE 
            on STEWARDSHIPPLANSTEP.CONTACTMETHODCODEID = INTERACTIONTYPECODE.ID
    left outer join dbo.STEWARDSHIPSTEPCATEGORYCODE
            on STEWARDSHIPPLANSTEP.CATEGORYCODEID = STEWARDSHIPSTEPCATEGORYCODE.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.CONSTITUENTID) NF_CONSTITUENT
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLANSTEP.CONTACTPERSONID) NF_CONTACT
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLANSTEP.CONSTITUENTID) NF_ASSIGNEDTO
    where 
      (@PLANNAME is null or STEWARDSHIPPLAN.NAME like @PLANNAME + '%') and 
      (@OBJECTIVE is null or STEWARDSHIPPLANSTEP.OBJECTIVE like @OBJECTIVE + '%') and 
      (@CONSTITUENTID is null or STEWARDSHIPPLAN.CONSTITUENTID = @CONSTITUENTID) and
      (@CONTACTPERSONID is null or STEWARDSHIPPLANSTEP.CONTACTPERSONID = @CONTACTPERSONID) and
      (
        ((@TARGETDATE_TO is null) or STEWARDSHIPPLANSTEP.TARGETDATE <= @TARGETDATE_TO) and
        ((@TARGETDATE_FROM is null) or STEWARDSHIPPLANSTEP.TARGETDATE >= @TARGETDATE_FROM)
      ) and
      (@CONTACTMETHODCODEID is null or STEWARDSHIPPLANSTEP.CONTACTMETHODCODEID = @CONTACTMETHODCODEID) and 
      (@ASSIGNEDTO is null or STEWARDSHIPPLANSTEP.CONSTITUENTID = @ASSIGNEDTO) and
      (@INCLUDEHISTORICAL = 1 or STEWARDSHIPPLAN.ISACTIVE=1)
    order by OBJECTIVE
end