USP_DATALIST_FUNDRAISERPROSPECTPLANS

        CREATE procedure dbo.USP_DATALIST_FUNDRAISERPROSPECTPLANS
        (
          @CURRENTAPPUSERID uniqueidentifier,
          @FUNDRAISERID uniqueidentifier,
          @SITEFILTERMODE tinyint = 0,
          @SITESSELECTED xml = null,
          @SECURITYFEATUREID uniqueidentifier = null,
          @SECURITYFEATURETYPE tinyint = null,
          @INCLUDEHISTORICALPLANS bit = 0
        )
        as begin
          set nocount on;

          declare @TODAY datetime;
          set @TODAY=getdate();

          declare @CURRENTDATEEARLIESTTIME datetime;
          set @CURRENTDATEEARLIESTTIME= dbo.UFN_DATE_GETEARLIESTTIME(@TODAY);

          declare @RESULTS as table
          (
            CONSTITUENTID uniqueidentifier,
            ID uniqueidentifier,
            LASTSTEP datetime,
            NORECENT nvarchar(100),
            NEXTSTEP datetime,
            CONSTITUENTNAME nvarchar(700),
            CONSTITUENTKEYNAME nvarchar(100),
            PROSPECTPLANTYPE nvarchar(100),
            PROSPECTPLANSTATUS nvarchar(100),
            FUNDRAISERROLE nvarchar(100),
            OPPORTUNITYAMOUNT money,
            OPPORTUNITYAMOUNTRANGENAME nvarchar(100),
            SITENAME nvarchar(max),
            PROSPECTPLANDATECHANGED datetime,
            HASPRIMARYMANAGER tinyint,
            HASSECONDARYMANAGER tinyint,
            PROSPECTMANAGER nvarchar(700),
            HASPROSPECTMANAGER tinyint,
            ISSTEWARDSHIPPLAN tinyint,
            BASECURRENCYID uniqueidentifier,
            QUALIFIEDAMOUNT money,
            RESPONSEPENDINGAMOUNT money,
            VIEWDATAFORMID uniqueidentifier,
            ISACTIVE bit
          );

          declare @RANGECURRENCYID uniqueidentifier;

          select distinct @RANGECURRENCYID = R.BASECURRENCYID
          from dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) R;

          insert into @RESULTS
          select
            CONSTITUENT.ID,
            PROSPECTPLAN.ID,
            (select max(ACTUALDATE) from dbo.INTERACTION where PROSPECTPLANID=PROSPECTPLAN.ID) LASTSTEP,
            case when PROSPECTPLAN.ISACTIVE = 1 and datediff(day,(select max(ACTUALDATE) from dbo.INTERACTION where PROSPECTPLANID=PROSPECTPLAN.ID),@TODAY)>=90 then 'RES:warning' else '' end NORECENT,
            (select INTERACTION.EXPECTEDDATE from dbo.INTERACTION where INTERACTION.ID = dbo.UFN_PROSPECTPLAN_GETNEXTSTEP(PROSPECTPLAN.ID)) NEXTSTEP,
            PROSPECT_NF.NAME,
            CONSTITUENT.KEYNAME,
            dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PROSPECTPLAN.PROSPECTPLANTYPECODEID),
            dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PROSPECTPLAN.PROSPECTPLANSTATUSCODEID),
            FUNDRAISERPROSPECTPLAN.FUNDRAISERROLE,
            PLANOPPORTUNITIES.PROSPECTPLANAMOUNT,
            OPPORTUNITYAMOUNTRANGE.NAME,
            dbo.UFN_PROSPECTPLAN_GETSITELIST(PROSPECTPLAN.ID) SITENAME,
            PROSPECTPLAN.DATECHANGED,
            --If manager's end date is a past date, It is assumed that no manager is associated with that prospect/plan

            (case when PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID is null or PROSPECTPLAN.PRIMARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then 0 else 1 end) HASPRIMARYMANAGER, 
            (case when PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID is null or PROSPECTPLAN.SECONDARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then 0 else 1 end) HASSECONDARYMANAGER,
            (case when PROSPECTMANAGER_NF.NAME is null or PROSPECT.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then '' else PROSPECTMANAGER_NF.NAME end) PROSPECTMANAGER,
            case when PROSPECT.PROSPECTMANAGERFUNDRAISERID is null or PROSPECT.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then 0 else 1 end as HASPROSPECTMANAGER,
            0 ISSTEWARDSHIPPLAN,
            PROSPECTPLAN.BASECURRENCYID as BASECURRENCYID,
QUALIFIEDOPPORTUNITIES.QUALIFIEDAMOUNT,
            RESPONSEPENDINGOPPORTUNITIES.RESPONSEPENDINGAMOUNT,
            'C48113CC-888F-44da-ACE1-F22A4420CC90' as VIEWDATAFORMID,
            PROSPECTPLAN.ISACTIVE
          from dbo.UFN_FUNDRAISER_PROSPECTPLANS_3(@FUNDRAISERID,1,@CURRENTDATEEARLIESTTIME) FUNDRAISERPROSPECTPLAN
            inner join dbo.PROSPECTPLAN PROSPECTPLAN on PROSPECTPLAN.ID = FUNDRAISERPROSPECTPLAN.ID
            inner join dbo.CONSTITUENT on CONSTITUENT.ID = PROSPECTPLAN.PROSPECTID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) PROSPECT_NF
            inner join dbo.PROSPECT on PROSPECT.ID = PROSPECTPLAN.PROSPECTID
            left outer join
            (
              select
                OPPORTUNITY.PROSPECTPLANID,
                sum(R1.AMOUNTINCURRENCY) AMOUNT,
                sum(OPPORTUNITY.AMOUNT) as QUALIFIEDAMOUNT
              from dbo.OPPORTUNITY
                left join dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@RANGECURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()) R1 on R1.ID = OPPORTUNITY.ID
              where OPPORTUNITY.STATUSCODE = 1
              group by OPPORTUNITY.PROSPECTPLANID
            ) QUALIFIEDOPPORTUNITIES on QUALIFIEDOPPORTUNITIES.PROSPECTPLANID=PROSPECTPLAN.ID
            left outer join
            (
              select
                OPPORTUNITY.PROSPECTPLANID,
                sum(R1.AMOUNTINCURRENCY) AMOUNT,
                sum(OPPORTUNITY.AMOUNT) as RESPONSEPENDINGAMOUNT
              from dbo.OPPORTUNITY
                left join dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@RANGECURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()) R1 on R1.ID = OPPORTUNITY.ID
              where OPPORTUNITY.STATUSCODE = 2
              group by OPPORTUNITY.PROSPECTPLANID
            ) RESPONSEPENDINGOPPORTUNITIES on RESPONSEPENDINGOPPORTUNITIES.PROSPECTPLANID=PROSPECTPLAN.ID
            left outer join
            (
              select
                OPPORTUNITY.PROSPECTPLANID,
                sum(R1.AMOUNTINCURRENCY) AMOUNT,
                sum(OPPORTUNITY.AMOUNT) as PROSPECTPLANAMOUNT
              from dbo.OPPORTUNITY
                left join dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@RANGECURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()) R1 on R1.ID = OPPORTUNITY.ID
              where OPPORTUNITY.STATUSCODE in (1,2,3)
              group by OPPORTUNITY.PROSPECTPLANID
            ) PLANOPPORTUNITIES on PLANOPPORTUNITIES.PROSPECTPLANID=PROSPECTPLAN.ID
            left outer join dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) OPPORTUNITYAMOUNTRANGE on PLANOPPORTUNITIES.AMOUNT between OPPORTUNITYAMOUNTRANGE.LOWERLIMIT and OPPORTUNITYAMOUNTRANGE.UPPERLIMIT
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.PROSPECTMANAGERFUNDRAISERID) PROSPECTMANAGER_NF
          where
            dbo.UFN_CONSTITUENT_ISPROSPECT(PROSPECTPLAN.PROSPECTID) = 1
            and
            (
              select count(*)
              from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PLANSITE
              where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANSITE].[SITEID] or (SITEID is null and [PLANSITE].[SITEID] is null)))
            ) > 0
            and (@INCLUDEHISTORICALPLANS = 1 or PROSPECTPLAN.ISACTIVE = 1)

          union all

          select
            CONSTITUENT.ID,
            STEWARDSHIPPLAN.ID,
            (select max(ACTUALDATE) from dbo.STEWARDSHIPPLANSTEP where PLANID = STEWARDSHIPPLAN.ID) LASTSTEP,
            case when datediff(day, (select max(ACTUALDATE) from dbo.STEWARDSHIPPLANSTEP where PLANID = STEWARDSHIPPLAN.ID), @TODAY) >= 90 then 'RES:warning' else '' end NORECENT,
            (select STEWARDSHIPPLANSTEP.TARGETDATE from dbo.STEWARDSHIPPLANSTEP where STEWARDSHIPPLANSTEP.ID = dbo.UFN_STEWARDSHIPPLAN_GETNEXTSTEP(STEWARDSHIPPLAN.ID)) NEXTSTEP,
            PROSPECT_NF.NAME,
            CONSTITUENT.KEYNAME,
            'Stewardship',
            null,
            'Steward',
            null,
            null,
            dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLAN.ID) SITENAME,
            STEWARDSHIPPLAN.DATECHANGED,
            0 HASPRIMARYMANAGER, 
            0 HASSECONDARYMANAGER,
            isnull(PROSPECTMANAGER_NF.NAME,'') as PROSPECTMANAGER,
            case when STEWARDSHIPPLAN.MANAGERID is null then 0 else 1 end as HASPROSPECTMANAGER,
            1 ISSTEWARDSHIPPLAN,
            STEWARDSHIPPLAN.BASECURRENCYID,
            null,
            null,
            '30D20C5C-DF18-4DBD-AFDF-4A4034834563' as VIEWDATAFORMID,
            STEWARDSHIPPLAN.ISACTIVE
          from dbo.STEWARDSHIPPLAN
            inner join dbo.CONSTITUENT on CONSTITUENT.ID = STEWARDSHIPPLAN.CONSTITUENTID
            left join dbo.PROSPECT on CONSTITUENT.ID = PROSPECT.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) PROSPECT_NF
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.PROSPECTMANAGERFUNDRAISERID) PROSPECTMANAGER_NF
          where (@INCLUDEHISTORICALPLANS = 1 or STEWARDSHIPPLAN.ISACTIVE = 1) and
            (
              (
                STEWARDSHIPPLAN.MANAGERID = @FUNDRAISERID and
                (
                  STEWARDSHIPPLAN.MANAGERENDDATE is null or
                  STEWARDSHIPPLAN.MANAGERENDDATE >= @CURRENTDATEEARLIESTTIME
                )
              ) or
              exists
              (
                select CONSTITUENTID
                from dbo.STEWARDSHIPPLANSTEWARD
                where
                  PLANID = STEWARDSHIPPLAN.ID and
                  CONSTITUENTID = @FUNDRAISERID and
                  (
                    ENDDATE is null or
                    ENDDATE >= @CURRENTDATEEARLIESTTIME
                  )
              )
            ) and
            (
              select count(*)
              from dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID(STEWARDSHIPPLAN.ID) as PLANSITE
              where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANSITE].[SITEID] or (SITEID is null and [PLANSITE].[SITEID] is null)))
            ) > 0;

          select
            CONSTITUENTID,
            ID,
            LASTSTEP,
            NORECENT,
            NEXTSTEP,
            CONSTITUENTNAME,
            PROSPECTPLANTYPE,
            PROSPECTPLANSTATUS,
            FUNDRAISERROLE,
            OPPORTUNITYAMOUNT,
            OPPORTUNITYAMOUNTRANGENAME,
            SITENAME,
            PROSPECTPLANDATECHANGED,
            HASPRIMARYMANAGER,
            HASSECONDARYMANAGER,
            PROSPECTMANAGER,
            HASPROSPECTMANAGER,
            ISSTEWARDSHIPPLAN,
            BASECURRENCYID,
            QUALIFIEDAMOUNT,
            RESPONSEPENDINGAMOUNT,
            VIEWDATAFORMID,
            ISACTIVE
          from @RESULTS RESULTS
          where
            case
              when @SITEFILTERMODE = 0 then 1
              else
                case
                  when ISSTEWARDSHIPPLAN = 1 then
                  case
                    when exists
                      (
                        select top 1 STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
                        from dbo.STEWARDSHIPPLANSITE
                          inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
                        where STEWARDSHIPPLANSITE.STEWARDSHIPPLANID = RESULTS.ID
                      ) then 1
                    else 0
                  end
                  else
                    case
        when exists
                      (
                        select top 1 PROSPECTPLANSITE.PROSPECTPLANID
                        from dbo.PROSPECTPLANSITE
                          inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID  
                        where PROSPECTPLANSITE.PROSPECTPLANID = RESULTS.ID
                      ) then 1
                      else 0
                    end
                end
            end = 1
          order by CONSTITUENTKEYNAME, CONSTITUENTNAME;
        end