USP_DATALIST_STEWARDSHIPPLAN_COMPLETEDSTEPS_ORG

List of completed steps for an organization'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.
@INCLUDESUBSIDIARIES	bit	IN	Include subsidiaries
@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_ORG
(
    @PLANID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @INCLUDESUBSIDIARIES bit = 0,
    @SECURITYFEATUREID uniqueidentifier,
    @SECURITYFEATURETYPE tinyint
)
as begin
    set nocount on;

    select 
        STEP.ID,
        0 as ISGROUPORGSTEP,
        0 as ISSUBSIDIARYSTEP,
        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
        and STEP.COMPLETED = 1
    order by
        STEP.ACTUALDATE, STEP.DATEADDED
end