USP_DATALIST_PROSPECTPLAN_PENDINGSTEWARDSHIPSTEPS

CREATE procedure dbo.USP_DATALIST_PROSPECTPLAN_PENDINGSTEWARDSHIPSTEPS
(
    @PLANID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier,
    @SECURITYFEATURETYPE tinyint
)
as begin
    set nocount on;
    select 
        STEP.ID,    
        STEP.PLANID,
        CATEGORYCODE.DESCRIPTION as CATEGORY,
        STEP.OBJECTIVE,
        STEP.TARGETDATE,
        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.RECURS,
        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,
        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.TARGETSTARTTIME,
        STEP.TARGETENDTIME,
        (select DISPLAYNAME from TIMEZONEENTRY where TIMEZONEENTRY.ID = STEP.TIMEZONEENTRYID) as TIMEZONE
    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 = 0
        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