USP_DATALIST_STEWARDSHIPPLAN_PENDINGSTEPS

List of pending steps for a given stewardship 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.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_STEWARDSHIPPLAN_PENDINGSTEPS
(
    @PLANID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @SECURITYFEATUREID uniqueidentifier,
    @SECURITYFEATURETYPE tinyint
)
as begin
    set nocount on;

    declare @CONSTITUENTID uniqueidentifier;

    select @CONSTITUENTID = [PLAN].CONSTITUENTID
    from
        dbo.STEWARDSHIPPLAN [PLAN]
    where
        [PLAN].ID = @PLANID;

    select 
        STEP.ID,

        case
            when STEP.PLANID = @PLANID then 0
            else 1
        end as ISGROUPORGSTEP,

        STEWARDSHIPSTEPCATEGORYCODE.DESCRIPTION as CATEGORY,
        STEP.OBJECTIVE,
        STEP.TARGETDATE,
        INTERACTIONTYPECODE.DESCRIPTION as CONTACTMETHOD,
        NF.NAME as ASSIGNEDTO,

        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.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.RECURS,
        STEP.DATEADDED,
        NF_STEPOWNER.NAME as STEPOWNERNAME,
        STEP.TARGETSTARTTIME,
        STEP.TARGETENDTIME,
        (select DISPLAYNAME from TIMEZONEENTRY where TIMEZONEENTRY.ID = STEP.TIMEZONEENTRYID) as TIMEZONE

    from
        dbo.STEWARDSHIPPLANSTEP STEP
        inner join dbo.STEWARDSHIPPLAN [PLAN]
          on STEP.PLANID = [PLAN].ID
        left outer join dbo.INTERACTIONTYPECODE 
            on STEP.CONTACTMETHODCODEID = INTERACTIONTYPECODE.ID
        left outer join dbo.STEWARDSHIPSTEPCATEGORYCODE
            on STEP.CATEGORYCODEID = STEWARDSHIPSTEPCATEGORYCODE.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEP.CONSTITUENTID) NF
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME([PLAN].CONSTITUENTID) NF_STEPOWNER
    where
        (STEP.PLANID = @PLANID
            or [PLAN].CONSTITUENTID in (select ID from dbo.UFN_GROUP_GETCURRENTMEMBERSNOPERMISSIONCHECK(@CONSTITUENTID))
        )
        and STEP.COMPLETED = 0 
        and (
        not exists(select ID from dbo.STEWARDSHIPSTEPASSOCIATEDPLAN where STEPID = STEP.ID)
         or
        exists(
          select STEWARDSHIPSTEPASSOCIATEDPLAN.ID 
          from dbo.STEWARDSHIPSTEPASSOCIATEDPLAN
          inner join dbo.PROSPECTPLAN
            on PROSPECTPLAN.ID = STEWARDSHIPSTEPASSOCIATEDPLAN.PROSPECTPLANID
          where STEWARDSHIPSTEPASSOCIATEDPLAN.STEPID = STEP.ID 
            and (
              select count(*) 
              from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE 
              where dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, PROSPECTPLANSITE.SITEID) = 1
            ) > 0
          )
        )
    order by
        STEP.TARGETDATE, STEP.DATEADDED
end