USP_DATALIST_FUNDRAISINGMANAGERPROSPECTS

        CREATE procedure dbo.USP_DATALIST_FUNDRAISINGMANAGERPROSPECTS
        (
          @FUNDRAISERID uniqueidentifier,
          @CURRENTAPPUSERID uniqueidentifier,
          @SITEFILTERMODE tinyint = 0,
          @SITESSELECTED xml = null,
          @SECURITYFEATUREID uniqueidentifier = null,
          @SECURITYFEATURETYPE tinyint = null,
          @INCLUDEHISTORICALPLANS bit = 0
        )
        as

        set nocount on;
        declare @CURRENTDATEEARLIESTTIME datetime;
        set @CURRENTDATEEARLIESTTIME= dbo.UFN_DATE_GETEARLIESTTIME(getdate());

        select
          [PROSPECTPLANSUMMARY].ID,
          [PROSPECTPLANSUMMARY].LASTSTEP,
          case
            when exists (select top 1 ID from dbo.PROSPECTPLAN where PROSPECTID = PROSPECT.ID and ISACTIVE = 1) and
              datediff(day,
              (
                select max(ACTUALDATE)
                from dbo.PROSPECTPLAN PP
                  inner join dbo.INTERACTION I on I.PROSPECTPLANID = PP.ID
                where PP.PROSPECTID = PROSPECT.ID
              ), getdate()) >= 90 then 'RES:warning'
            else ''
          end NORECENT,
          [PROSPECTPLANSUMMARY].NEXTSTEP,
          [PROSPECTPLANSUMMARY].NAME,
          [PROSPECTPLANSUMMARY].KEYNAME,
          [PROSPECTPLANSUMMARY].SITES,
          'Prospect Manager' ROLE,
          Cast(1 as bit) ISPROSPECTMANAGER,
          null PROSPECTTEAMID,
          PROSPECT.DATECHANGED,
          PROSPECT.ID as [RSSID],
          'Prospect manager' ASSIGNMENTTYPE,
          case
            when exists (select top 1 ID from dbo.PROSPECTPLAN where PROSPECTID = PROSPECT.ID and ISACTIVE = 1) then 1
            when PROSPECTPLAN.ID is null then 1
            else 0
          end as ISACTIVE
        from dbo.UFN_PROSPECTPLANSUMMARY() as [PROSPECTPLANSUMMARY]
          inner join dbo.PROSPECT on [PROSPECTPLANSUMMARY].ID = PROSPECT.ID
          left outer join dbo.PROSPECTPLAN on PROSPECTPLAN.PROSPECTID = PROSPECT.ID
        where
          PROSPECT.PROSPECTMANAGERFUNDRAISERID = @FUNDRAISERID and
          (
              PROSPECT.PROSPECTMANAGERENDDATE is null or
              PROSPECT.PROSPECTMANAGERENDDATE >= @CURRENTDATEEARLIESTTIME
          ) and
          (
            --Only apply security/filtering if there's a plan

            PROSPECTPLAN.ID is null or
            (
              (
                select count(*)
                from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE
                where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
              ) > 0
              and
              (
                @SITEFILTERMODE = 0
                or PROSPECTPLAN.ID in
                (
                  select PROSPECTPLANSITE.PROSPECTPLANID
                  from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                  inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
                )
              )
              and (@INCLUDEHISTORICALPLANS = 1 or PROSPECTPLAN.ISACTIVE = 1)
            )
          )

        union

        select
          [PROSPECTPLANSUMMARY].ID,
          [PROSPECTPLANSUMMARY].LASTSTEP,
          case
            when exists (select top 1 ID from dbo.PROSPECTPLAN where PROSPECTID = [PROSPECTPLANSUMMARY].ID and ISACTIVE = 1) and
              datediff(day,
              (
                select max(ACTUALDATE)
                from dbo.PROSPECTPLAN PP
                  inner join dbo.INTERACTION I on I.PROSPECTPLANID = PP.ID
                where PP.PROSPECTID = [PROSPECTPLANSUMMARY].ID
              ), getdate()) >= 90 then 'RES:warning'
            else ''
          end NORECENT,
          [PROSPECTPLANSUMMARY].NEXTSTEP,
          [PROSPECTPLANSUMMARY].NAME,
          [PROSPECTPLANSUMMARY].KEYNAME,
          [PROSPECTPLANSUMMARY].SITES,
          TEAM.ROLEDESCRIPTION ROLE,
          Cast(0 as bit) ISPROSPECTMANAGER,
          TEAM.ID PROSPECTTEAMID,
          TEAM.DATECHANGED,
          TEAM.ID as [RSSID],
          'Prospect team' ASSIGNMENTTYPE,
          case
            when exists (select top 1 ID from dbo.PROSPECTPLAN where PROSPECTID = [PROSPECTPLANSUMMARY].ID and ISACTIVE = 1) then 1
            when PROSPECTPLAN.ID is null then 1
            else 0
          end as ISACTIVE
        from dbo.UFN_PROSPECTPLANSUMMARY() as [PROSPECTPLANSUMMARY]
          inner join dbo.V_PROSPECTTEAM TEAM on [PROSPECTPLANSUMMARY].ID = TEAM.PROSPECTID 
          left outer join dbo.PROSPECTPLAN on PROSPECTPLAN.PROSPECTID = TEAM.PROSPECTID 
        where
          TEAM.MEMBERID = @FUNDRAISERID and
          (TEAM.DATETO is null OR TEAM.DATETO >= GETDATE())
          and
          (
            --Only apply security/filtering if there's a plan

            PROSPECTPLAN.ID is null or
            (
              (
                select count(*)
                from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE 
                where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
              ) > 0
              and
              (
                @SITEFILTERMODE = 0
                or PROSPECTPLAN.ID in
                (
                  select PROSPECTPLANSITE.PROSPECTPLANID
                  from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                  inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
                )
              )
              and (@INCLUDEHISTORICALPLANS = 1 or PROSPECTPLAN.ISACTIVE = 1)
            )
          )
        order by KEYNAME;