USP_DATALIST_PROSPECTPLAN_COMPLETEDSTEWARDSHIPSTEPS

List of completed stewardship steps associated with a given prospect plan.

Parameters

Parameter	Parameter Type	Mode	Description
@PLANID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@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.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN

Definition

Copy



CREATE procedure dbo.USP_DATALIST_PROSPECTPLAN_COMPLETEDSTEWARDSHIPSTEPS
(
    @PLANID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @SECURITYFEATUREID uniqueidentifier,
    @SECURITYFEATURETYPE tinyint,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null
)
as begin
    set nocount on;
    select 
        STEP.ID,    
        STEP.PLANID,
        CATEGORYCODE.DESCRIPTION as CATEGORY,
        STEP.OBJECTIVE,
        STEP.ACTUALDATE,
        NF_CONTACT.NAME as CONTACTPERSON,
        INTERACTIONTYPECODE.DESCRIPTION as CONTACTMETHOD,
        NF_ASSIGNEDTO.NAME as ASSIGNEDTO,
        STEP.TEMPLATE,

        case when exists(select 1 from dbo.STEWARDSHIPNOTE where STEWARDSHIPNOTE.STEPID = STEP.ID)
               or exists(select 1 from dbo.STEWARDSHIPATTACHMENT where STEWARDSHIPATTACHMENT.STEPID = STEP.ID)
               or exists(select 1 from dbo.STEWARDSHIPMEDIALINK where STEWARDSHIPMEDIALINK.STEPID = STEP.ID)
        then convert(bit,1) else convert(bit,0) end as HASDOCUMENTATION,

        STEP.DATEADDED,
        (
          select dbo.UDA_BUILDLIST(distinct SITE.NAME)
          from dbo.UFN_STEWARDSHIPPLAN_GETSITES([PLAN].ID) PLANSITE
          inner join dbo.SITE on SITE.ID = PLANSITE.SITEID
        ) SITES,
        STEP.ACTUALSTARTTIME,
        STEP.ACTUALENDTIME,
        case when (select count(ID) from dbo.STEWARDSHIPSTEPASSOCIATEDPLAN where STEPID = STEP.ID) > 1
            then convert(nvarchar(50), (select count(ID) from dbo.STEWARDSHIPSTEPASSOCIATEDPLAN where STEPID = STEP.ID))
        else (
                select 
                    PROSPECTPLAN.NAME
                from
                    dbo.STEWARDSHIPSTEPASSOCIATEDPLAN
                inner join dbo.PROSPECTPLAN
                    on STEWARDSHIPSTEPASSOCIATEDPLAN.PROSPECTPLANID = PROSPECTPLAN.ID
                where STEWARDSHIPSTEPASSOCIATEDPLAN.STEPID = STEP.ID
            )
        end as ASSOCIATEDPLANS,
        STEP.STATUS
    from
        dbo.STEWARDSHIPSTEPASSOCIATEDPLAN ASSOCIATEDPLANS
        inner join dbo.STEWARDSHIPPLANSTEP STEP
            on ASSOCIATEDPLANS.STEPID = STEP.ID
        inner join dbo.STEWARDSHIPPLAN [PLAN]
            on [PLAN].ID = STEP.PLANID
        left outer join dbo.STEWARDSHIPSTEPCATEGORYCODE CATEGORYCODE
            on STEP.CATEGORYCODEID = CATEGORYCODE.ID
        left outer join dbo.INTERACTIONTYPECODE 
            on STEP.CONTACTMETHODCODEID = INTERACTIONTYPECODE.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEP.CONTACTPERSONID) NF_CONTACT
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEP.CONSTITUENTID) NF_ASSIGNEDTO
    where
        ASSOCIATEDPLANS.PROSPECTPLANID = @PLANID
        and STEP.COMPLETED = 1
        and 
        (
            select count(*) 
            from dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID([PLAN].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 
            exists
                (
                    select top 1
                        STEWARDSHIPPLANSITE.ID
                    from 
                        dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                        inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
                        where STEWARDSHIPPLANSITE.STEWARDSHIPPLANID = [PLAN].ID
                )
            )


    order by
        STEP.TARGETDATE, STEP.DATEADDED
end