UFN_CONSTITUENTSTEWARDSHIPPLANSTEP_GETLIST

create function dbo.UFN_CONSTITUENTSTEWARDSHIPPLANSTEP_GETLIST
(
    @CONSTITUENTID uniqueidentifier,
    @STARTDATE datetime,
    @ENDDATE datetime,
    @TYPEFILTER uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint,
    @SITESSELECTED xml,
    @SECURITYFEATUREID uniqueidentifier,
    @SECURITYFEATURETYPE tinyint
)
returns table
as return
(
select
    STEP.ID,
    STEP.[DATE],
    STEP.[STATUS],
    STEP.OBJECTIVE,
    STEP.CONTACTMETHODCODEID INTERACTIONTYPECODEID,
    STEP.CONSTITUENTID ASSIGNEDTOID,
    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 HASDOCUMENTATION,
    STEP.EVENTID,
    CATEGORYCODE.[DESCRIPTION] CATEGORY,
    dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLAN.ID) SITES,
    STEP.TARGETSTARTTIME STARTTIME,
    STEP.TARGETENDTIME ENDTIME,
    STEP.TIMEZONEENTRYID
from
    dbo.STEWARDSHIPPLANSTEP STEP
    join dbo.STEWARDSHIPPLAN on STEP.PLANID = STEWARDSHIPPLAN.ID
    left join dbo.STEWARDSHIPSTEPCATEGORYCODE CATEGORYCODE on STEP.CATEGORYCODEID = CATEGORYCODE.ID
where
    (STEP.CONTACTPERSONID = @CONSTITUENTID
    or STEWARDSHIPPLAN.CONSTITUENTID = @CONSTITUENTID)
    and STEP.ISINTERACTION = 1
    and (
        (STEP.[DATE] between @STARTDATE and @ENDDATE)
            or (@STARTDATE is null and @ENDDATE is null)
            or (STEP.[DATE] >= @STARTDATE and @ENDDATE is null)
            or (@STARTDATE is null and STEP.[DATE] <= @ENDDATE)
    )
    and (
        (STEP.CONTACTMETHODCODEID = @TYPEFILTER)
            or (@TYPEFILTER is null)
    ) 
    and (
        select count(*) 
        from dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID(STEWARDSHIPPLAN.ID) 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 STEWARDSHIPPLAN.ID in (
            select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
            from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
            )
    )
)