UFN_CONSTITUENT_INTERACTIONS

CREATE function dbo.UFN_CONSTITUENT_INTERACTIONS
(
  @CONSTITUENTID uniqueidentifier,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @TYPEFILTER uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @STATUS tinyint = null
)
returns table
as return
  -- Using a union on the four different types of data getting pulled in (instead of a view) 

  -- allows the compiler to choose a plan that filters on constituent ID first.\


  -- Interactions for the constituent

  select    
    INTERACTION.ID,
    INTERACTION.DATE,
    INTERACTION.STATUS,
    OWNERNAME.NAME as OWNER,
    INTERACTION.FUNDRAISERID as OWNERID,
    INTERACTION.OBJECTIVE,
    INTERACTIONTYPECODE.DESCRIPTION as TYPE,
    case
      when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID = INTERACTION.ID)
        or exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID = INTERACTION.ID)
        or exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID = INTERACTION.ID) then 1
      else 0
    end as HASDOCUMENTATION,
    case
      when INTERACTION.PROSPECTPLANID is null then 0
      else 1
    end as ISMOVE,
    EVENT.NAME EVENT,
    case
      when exists(select 1 from dbo.INTERACTIONRESPONSE where INTERACTIONRESPONSE.INTERACTIONID = INTERACTION.ID) then 1
      else 0
    end as HASRESPONSES,
    INTERACTIONCATEGORY.NAME as CATEGORY,
    INTERACTIONSUBCATEGORY.NAME as SUBCATEGORY,
    case
      when INTERACTION.PROSPECTPLANID is null then 'b525985b-be02-4f02-a9b8-c110411cf936'
      else '6ce4b0e8-cfcf-4f57-94c3-00eea94fb499'
    end as VIEWFORMID,
    case
      when INTERACTION.PROSPECTPLANID is null then dbo.UFN_CONSTITUENTINTERACTION_GETSITELIST(INTERACTION.ID)
      else dbo.UFN_PROSPECTPLAN_GETSITELIST(INTERACTION.PROSPECTPLANID)
    end as SITES,
    INTERACTION.STARTTIME as STARTTIME,
    INTERACTION.ENDTIME as ENDTIME,
    TIMEZONEENTRY.DISPLAYNAME TIMEZONE,
    0 as ISSTEWARDSHIPSTEP,
    case
      when CONSTITUENT.ISGROUP = 1 then 0
      when CONSTITUENT.ISORGANIZATION = 1 then 0
      else 1
    end as ISINDIVIDUALSTEP,
    INTERACTION.EXPECTEDDATE,
    INTERACTION.ACTUALDATE,
    INTERACTION.EXPECTEDSTARTTIME,
    INTERACTION.ACTUALSTARTTIME,
    INTERACTION.EXPECTEDENDTIME,
    INTERACTION.ACTUALENDTIME,
    INTERACTION.COMMENT,
    case
      when exists(select 1 from dbo.INTERACTIONPARTICIPANT where INTERACTIONPARTICIPANT.INTERACTIONID = INTERACTION.ID) then 1
      else 0
    end as HASPARTICIPANTS,
    INTERACTION.LOCATION
  from dbo.INTERACTION
    left join dbo.INTERACTIONSUBCATEGORY on INTERACTIONSUBCATEGORY.ID = INTERACTION.INTERACTIONSUBCATEGORYID
    left join dbo.INTERACTIONCATEGORY on INTERACTIONCATEGORY.ID = INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID
    inner join dbo.INTERACTIONTYPECODE on INTERACTION.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
    left join dbo.TIMEZONEENTRY on INTERACTION.TIMEZONEENTRYID = TIMEZONEENTRY.ID
    left join dbo.EVENT on EVENT.ID = INTERACTION.EVENTID
    left join dbo.CONSTITUENT on CONSTITUENT.ID = INTERACTION.CONSTITUENTID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTION.FUNDRAISERID) OWNERNAME
  where
    INTERACTION.ISINTERACTION = 1
    and INTERACTION.STATUSCODE <> 0
    and INTERACTION.CONSTITUENTID = @CONSTITUENTID
    and (
      INTERACTION.STATUSCODE = @STATUS or
      @STATUS is null
    ) 
    and (
      (INTERACTION.DATE between @STARTDATE and @ENDDATE) or
      (@STARTDATE is null and @ENDDATE is null) or
      (INTERACTION.DATE >= @STARTDATE and @ENDDATE is null) or
      (@STARTDATE is null and INTERACTION.DATE <= @ENDDATE)
    ) 
    and (@TYPEFILTER is null or INTERACTION.INTERACTIONTYPECODEID = @TYPEFILTER) 
    and exists(
      select top 1 INTERACTIONSITE.SITEID from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) INTERACTIONSITE
      where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[INTERACTIONSITE].[SITEID] or (SITEID is null and [INTERACTIONSITE].[SITEID] is null)))
    ) 
    and    (
      @SITEFILTERMODE = 0 or
      (INTERACTION.PROSPECTPLANID is null and INTERACTION.ID in 
        (
          select INTERACTIONSITE.INTERACTIONID
          from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
          inner join dbo.INTERACTIONSITE on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
        )
      ) or
      (INTERACTION.PROSPECTPLANID is not null and INTERACTION.PROSPECTPLANID in
        (
          select PROSPECTPLANSITE.PROSPECTPLANID
          from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
          inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
        )
      )
    )

  union all

  -- Interactions where the constituent is a participant

  select 
    INTERACTION.ID,
    INTERACTION.DATE,
    INTERACTION.STATUS,
    OWNERNAME.NAME as OWNER,
    INTERACTION.FUNDRAISERID as OWNERID,
    INTERACTION.OBJECTIVE,
    INTERACTIONTYPECODE.DESCRIPTION as TYPE,
    case
      when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID = INTERACTION.ID)
        or exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID = INTERACTION.ID)
        or exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID = INTERACTION.ID) then 1
      else 0
    end as HASDOCUMENTATION,
    case
      when INTERACTION.PROSPECTPLANID is null then 0
      else 1
      end as ISMOVE,
    EVENT.NAME EVENT,
    case
      when exists(select 1 from dbo.INTERACTIONRESPONSE where INTERACTIONRESPONSE.INTERACTIONID = INTERACTION.ID) then 1
      else 0
    end as HASRESPONSES,
    INTERACTIONCATEGORY.NAME as CATEGORY,
    INTERACTIONSUBCATEGORY.NAME as SUBCATEGORY,
    case
      when INTERACTION.PROSPECTPLANID is null then 'b525985b-be02-4f02-a9b8-c110411cf936'
      else '6ce4b0e8-cfcf-4f57-94c3-00eea94fb499'
    end as VIEWFORMID,
    case
      when INTERACTION.PROSPECTPLANID is null then dbo.UFN_CONSTITUENTINTERACTION_GETSITELIST(INTERACTION.ID)
      else dbo.UFN_PROSPECTPLAN_GETSITELIST(INTERACTION.PROSPECTPLANID)
    end as SITES,
    INTERACTION.STARTTIME as STARTTIME,
    INTERACTION.ENDTIME as ENDTIME,
    TIMEZONEENTRY.DISPLAYNAME TIMEZONE,
    0 as ISSTEWARDSHIPSTEP,
    case
      when CONSTITUENT.ISGROUP = 1 then 0
      when CONSTITUENT.ISORGANIZATION = 1 then 0
      else 1
    end as ISINDIVIDUALSTEP,
    INTERACTION.EXPECTEDDATE,
    INTERACTION.ACTUALDATE,
    INTERACTION.EXPECTEDSTARTTIME,
    INTERACTION.ACTUALSTARTTIME,
    INTERACTION.EXPECTEDENDTIME,
    INTERACTION.ACTUALENDTIME,
    INTERACTION.COMMENT,
    case
      when exists(select 1 from dbo.INTERACTIONPARTICIPANT where INTERACTIONPARTICIPANT.INTERACTIONID = INTERACTION.ID) then 1
      else 0
    end as HASPARTICIPANTS,
    INTERACTION.LOCATION
  from dbo.INTERACTION
    left join dbo.INTERACTIONSUBCATEGORY on INTERACTIONSUBCATEGORY.ID = INTERACTION.INTERACTIONSUBCATEGORYID
    left join dbo.INTERACTIONCATEGORY on INTERACTIONCATEGORY.ID = INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID
    inner join dbo.INTERACTIONTYPECODE on INTERACTION.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
    left join dbo.TIMEZONEENTRY on INTERACTION.TIMEZONEENTRYID = TIMEZONEENTRY.ID
    left join dbo.EVENT on EVENT.ID = INTERACTION.EVENTID
    left join dbo.CONSTITUENT on CONSTITUENT.ID = INTERACTION.CONSTITUENTID
    inner join dbo.INTERACTIONPARTICIPANT on INTERACTIONPARTICIPANT.INTERACTIONID = INTERACTION.ID        
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTION.FUNDRAISERID) OWNERNAME
  where
    INTERACTIONPARTICIPANT.CONSTITUENTID = @CONSTITUENTID 
    and INTERACTION.CONSTITUENTID <> @CONSTITUENTID
    and INTERACTION.ISINTERACTION = 1
    and INTERACTION.STATUSCODE <> 0
    and (
      INTERACTION.STATUSCODE = @STATUS or
      @STATUS is null
    ) 
    and (
      (INTERACTION.DATE between @STARTDATE and @ENDDATE) or
      (@STARTDATE is null and @ENDDATE is null) or
      (INTERACTION.DATE >= @STARTDATE and @ENDDATE is null) or
      (@STARTDATE is null and INTERACTION.DATE <= @ENDDATE)
    ) 
    and (@TYPEFILTER is null or INTERACTION.INTERACTIONTYPECODEID = @TYPEFILTER) 
    and exists(
      select top 1 INTERACTIONSITE.SITEID from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) INTERACTIONSITE
      where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[INTERACTIONSITE].[SITEID] or (SITEID is null and [INTERACTIONSITE].[SITEID] is null)))
    ) 
    and    (
      @SITEFILTERMODE = 0 or
      (INTERACTION.PROSPECTPLANID is null and INTERACTION.ID in 
        (
          select INTERACTIONSITE.INTERACTIONID
          from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
          inner join dbo.INTERACTIONSITE on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
        )
      ) or
      (INTERACTION.PROSPECTPLANID is not null and INTERACTION.PROSPECTPLANID in
        (
          select PROSPECTPLANSITE.PROSPECTPLANID
          from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
          inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
        )
      )
    )

  union all

  -- Stewardship plan steps for the constituent

  select 
    STEWARDSHIPPLANSTEP.ID,
    STEWARDSHIPPLANSTEP.DATE,
    STEWARDSHIPPLANSTEP.STATUS,
    OWNERNAME.NAME as OWNER,
    STEWARDSHIPPLANSTEP.CONSTITUENTID as OWNERID,
    STEWARDSHIPPLANSTEP.OBJECTIVE,
    INTERACTIONTYPECODE.DESCRIPTION as TYPE,
    case
      when exists(select 1 from dbo.STEWARDSHIPNOTE where STEWARDSHIPNOTE.STEPID = STEWARDSHIPPLANSTEP.ID)
        or exists(select 1 from dbo.STEWARDSHIPATTACHMENT where STEWARDSHIPATTACHMENT.STEPID = STEWARDSHIPPLANSTEP.ID)
        or exists(select 1 from dbo.STEWARDSHIPMEDIALINK where STEWARDSHIPMEDIALINK.STEPID = STEWARDSHIPPLANSTEP.ID) then 1
      else 0
    end as HASDOCUMENTATION,
    0 as ISMOVE,
    EVENT.NAME EVENT,
    0 as HASRESPONSES,
    STEWARDSHIPSTEPCATEGORYCODE.DESCRIPTION as CATEGORY,
    null as SUBCATEGORY,
    '0a9d7c4b-018d-48b5-aa14-589cf6c0d97b' as VIEWFORMID,
    dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLANSTEP.PLANID) as SITES, 
    STEWARDSHIPPLANSTEP.STARTTIME as STARTTIME,
    STEWARDSHIPPLANSTEP.ENDTIME as ENDTIME,
    TIMEZONEENTRY.DISPLAYNAME TIMEZONE,
    1 as ISSTEWARDSHIPSTEP,
    case
      when CONSTITUENT.ISGROUP = 1 then 0
      when CONSTITUENT.ISORGANIZATION = 1 then 0
      else 1
    end as ISINDIVIDUALSTEP,
    STEWARDSHIPPLANSTEP.TARGETDATE as EXPECTEDDATE,
    STEWARDSHIPPLANSTEP.ACTUALDATE,
    STEWARDSHIPPLANSTEP.TARGETSTARTTIME as EXPECTEDSTARTTIME,
    STEWARDSHIPPLANSTEP.ACTUALSTARTTIME,
    STEWARDSHIPPLANSTEP.TARGETENDTIME as EXPECTEDENDTIME,
    STEWARDSHIPPLANSTEP.ACTUALENDTIME,
    null,
    case
      when exists(select 1 from dbo.INTERACTIONPARTICIPANT where INTERACTIONPARTICIPANT.INTERACTIONID = STEWARDSHIPPLANSTEP.ID) then 1
      else 0
    end as HASPARTICIPANTS,
    null as LOCATION
  from
    dbo.STEWARDSHIPPLANSTEP
    inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEWARDSHIPPLANSTEP.PLANID
    left join dbo.STEWARDSHIPSTEPCATEGORYCODE on STEWARDSHIPSTEPCATEGORYCODE.ID = STEWARDSHIPPLANSTEP.CATEGORYCODEID
    inner join dbo.INTERACTIONTYPECODE on STEWARDSHIPPLANSTEP.CONTACTMETHODCODEID = INTERACTIONTYPECODE.ID
    left join dbo.TIMEZONEENTRY on STEWARDSHIPPLANSTEP.TIMEZONEENTRYID = TIMEZONEENTRY.ID
    left join dbo.EVENT on EVENT.ID = STEWARDSHIPPLANSTEP.EVENTID
    left join dbo.CONSTITUENT on CONSTITUENT.ID = STEWARDSHIPPLAN.CONSTITUENTID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLANSTEP.CONSTITUENTID) OWNERNAME
  where
    STEWARDSHIPPLANSTEP.ISINTERACTION = 1
    and STEWARDSHIPPLAN.CONSTITUENTID = @CONSTITUENTID
    and
    (
      case STEWARDSHIPPLANSTEP.STATUSCODE
        when 0 then 1
        when 1 then 2
        when 2 then 4
        when 3 then 5
      end = @STATUS 
      or @STATUS is null
    ) 
    and (
      (STEWARDSHIPPLANSTEP.DATE between @STARTDATE and @ENDDATE) or
      (@STARTDATE is null and @ENDDATE is null) or
      (STEWARDSHIPPLANSTEP.DATE >= @STARTDATE and @ENDDATE is null) or
      (@STARTDATE is null and STEWARDSHIPPLANSTEP.DATE <= @ENDDATE)
    ) and
    (@TYPEFILTER is null or STEWARDSHIPPLANSTEP.CONTACTMETHODCODEID = @TYPEFILTER) and
    exists(
      select top 1 STEWARDSHIPPLANSTEPSITE.SITEID from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(STEWARDSHIPPLANSTEP.ID) STEWARDSHIPPLANSTEPSITE
      where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=STEWARDSHIPPLANSTEPSITE.[SITEID] or (SITEID is null and STEWARDSHIPPLANSTEPSITE.[SITEID] is null)))
    ) 
    and (
      @SITEFILTERMODE = 0 or
      STEWARDSHIPPLANSTEP.PLANID in
        (
          select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
          from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
          inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
        )
    )

  union all

  -- Stewardship plan steps where the constituent is the contact person

  select 
    STEWARDSHIPPLANSTEP.ID,
    STEWARDSHIPPLANSTEP.DATE,
    STEWARDSHIPPLANSTEP.STATUS,
    OWNERNAME.NAME as OWNER,
    STEWARDSHIPPLANSTEP.CONSTITUENTID as OWNERID,
    STEWARDSHIPPLANSTEP.OBJECTIVE,
    INTERACTIONTYPECODE.DESCRIPTION as TYPE,
    case
      when exists(select 1 from dbo.STEWARDSHIPNOTE where STEWARDSHIPNOTE.STEPID = STEWARDSHIPPLANSTEP.ID)
        or exists(select 1 from dbo.STEWARDSHIPATTACHMENT where STEWARDSHIPATTACHMENT.STEPID = STEWARDSHIPPLANSTEP.ID)
        or exists(select 1 from dbo.STEWARDSHIPMEDIALINK where STEWARDSHIPMEDIALINK.STEPID = STEWARDSHIPPLANSTEP.ID) then 1
      else 0
    end as HASDOCUMENTATION,
    0 as ISMOVE,
    EVENT.NAME EVENT,
    0 as HASRESPONSES,
    STEWARDSHIPSTEPCATEGORYCODE.DESCRIPTION as CATEGORY,
    null as SUBCATEGORY,
    '0a9d7c4b-018d-48b5-aa14-589cf6c0d97b' as VIEWFORMID,
    dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLANSTEP.PLANID) as SITES, 
    STEWARDSHIPPLANSTEP.STARTTIME as STARTTIME,
    STEWARDSHIPPLANSTEP.ENDTIME as ENDTIME,
    TIMEZONEENTRY.DISPLAYNAME TIMEZONE,
    1 as ISSTEWARDSHIPSTEP,
    case
      when CONSTITUENT.ISGROUP = 1 then 0
      when CONSTITUENT.ISORGANIZATION = 1 then 0
      else 1
    end as ISINDIVIDUALSTEP,
    STEWARDSHIPPLANSTEP.TARGETDATE as EXPECTEDDATE,
    STEWARDSHIPPLANSTEP.ACTUALDATE,
    STEWARDSHIPPLANSTEP.TARGETSTARTTIME as EXPECTEDSTARTTIME,
    STEWARDSHIPPLANSTEP.ACTUALSTARTTIME,
    STEWARDSHIPPLANSTEP.TARGETENDTIME as EXPECTEDENDTIME,
    STEWARDSHIPPLANSTEP.ACTUALENDTIME,
    null,
    case
      when exists(select 1 from dbo.INTERACTIONPARTICIPANT where INTERACTIONPARTICIPANT.INTERACTIONID = STEWARDSHIPPLANSTEP.ID) then 1
      else 0
    end as HASPARTICIPANTS,
    null as LOCATION
  from dbo.STEWARDSHIPPLANSTEP
    inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEWARDSHIPPLANSTEP.PLANID
    left join dbo.STEWARDSHIPSTEPCATEGORYCODE on STEWARDSHIPSTEPCATEGORYCODE.ID = STEWARDSHIPPLANSTEP.CATEGORYCODEID
    inner join dbo.INTERACTIONTYPECODE on STEWARDSHIPPLANSTEP.CONTACTMETHODCODEID = INTERACTIONTYPECODE.ID
    left join dbo.TIMEZONEENTRY on STEWARDSHIPPLANSTEP.TIMEZONEENTRYID = TIMEZONEENTRY.ID
    left join dbo.EVENT on EVENT.ID = STEWARDSHIPPLANSTEP.EVENTID
    left join dbo.CONSTITUENT on CONSTITUENT.ID = STEWARDSHIPPLAN.CONSTITUENTID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLANSTEP.CONSTITUENTID) OWNERNAME
  where
    STEWARDSHIPPLANSTEP.ISINTERACTION = 1
    and STEWARDSHIPPLANSTEP.CONTACTPERSONID = @CONSTITUENTID
    and STEWARDSHIPPLAN.CONSTITUENTID <> @CONSTITUENTID
    and
    (
      case STEWARDSHIPPLANSTEP.STATUSCODE
        when 0 then 1
        when 1 then 2
        when 2 then 4
        when 3 then 5
      end = @STATUS 
      or @STATUS is null
    ) 
    and (
      (STEWARDSHIPPLANSTEP.DATE between @STARTDATE and @ENDDATE) or
      (@STARTDATE is null and @ENDDATE is null) or
      (STEWARDSHIPPLANSTEP.DATE >= @STARTDATE and @ENDDATE is null) or
      (@STARTDATE is null and STEWARDSHIPPLANSTEP.DATE <= @ENDDATE)
    ) and
    (@TYPEFILTER is null or STEWARDSHIPPLANSTEP.CONTACTMETHODCODEID = @TYPEFILTER) and
    exists(
      select top 1 STEWARDSHIPPLANSTEPSITE.SITEID from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(STEWARDSHIPPLANSTEP.ID) STEWARDSHIPPLANSTEPSITE
      where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=STEWARDSHIPPLANSTEPSITE.[SITEID] or (SITEID is null and STEWARDSHIPPLANSTEPSITE.[SITEID] is null)))
    ) 
    and (
      @SITEFILTERMODE = 0 or
      STEWARDSHIPPLANSTEP.PLANID in
        (
          select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
          from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
          inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
        )
    )