USP_DATALIST_STEWARDSHIPPLAN_COMPLETEDSTEPS_GROUP

List of completed steps for a group's 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_COMPLETEDSTEPS_GROUP
(
    @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,
        0 as ISMEMBERSTEP,
        0 as ISGROUPORGSTEP,
        STEWARDSHIPSTEPCATEGORYCODE.DESCRIPTION as CATEGORY,
        STEP.OBJECTIVE,
        STEP.ACTUALDATE,        
        STEP.ACTUALSTARTTIME,
        STEP.ACTUALENDTIME,
        NF_CONTACT.NAME as CONTACTPERSON,
        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.DATEADDED,
        NF_STEPOWNER.NAME as STEPOWNERNAME,
        STEP.STATUS
    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
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEP.CONTACTPERSONID) NF_CONTACT
    where
        (STEP.PLANID = @PLANID
            or CONTACTPERSONID = @CONSTITUENTID
            or [PLAN].CONSTITUENTID in (select ID from dbo.UFN_GROUP_GETCURRENTMEMBERSNOPERMISSIONCHECK(@CONSTITUENTID))
        )
        and STEP.COMPLETED = 1
        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.ACTUALDATE, STEP.DATEADDED
end