USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSSUMMARYOPPORTUNITIES2

        CREATE procedure dbo.USP_DATAFORMTEMPLATE_DATALIST_FUNDRAISERSSUMMARYOPPORTUNITIES2
        (
          @CURRENTAPPUSERID uniqueidentifier = null, 
          @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
          @PROSPECTPLANTYPECODEID uniqueidentifier = null,
          @SECURITYFEATUREID uniqueidentifier = null,
          @SECURITYFEATURETYPE tinyint = null
        )
        as begin
          set nocount on;

          declare @OVERDUERESPONSESCOUNT int;
          declare @OVERDUERESPONSESAMOUNT money;
          declare @OVERDUEASKSCOUNT int;
          declare @OVERDUEASKSAMOUNT money;
          declare @FUZZYTODAY dbo.UDT_FUZZYDATE;
          declare @CURRENCYID uniqueidentifier;
          declare @ORGANIZATIONCURRENCYID uniqueidentifier;

          set @FUZZYTODAY = convert(nvarchar(8), getdate(), 112);
          set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
          set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

          if @ORGPOSITIONSSELECTIONID is null
            begin
              select
                @OVERDUEASKSCOUNT = count(*),
                @OVERDUEASKSAMOUNT = sum(O.AMOUNTINCURRENCY)
              from 
                dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) O
                inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
              where
                O.STATUSCODE=1
                and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                and O.ASKDATE<@FUZZYTODAY
                and (
                  select count(*) 
                  from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.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

              select
                @OVERDUERESPONSESCOUNT = count(*),
                @OVERDUERESPONSESAMOUNT = sum(O.AMOUNTINCURRENCY)
              from 
                dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) O
                inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
              where
                O.STATUSCODE=2
                and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                and O.RESPONSEDATE<@FUZZYTODAY
                and (
                  select count(*) 
                  from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.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
            end
          else
            begin
              declare @IDS as table(ID uniqueidentifier);
              insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;

              select
                @OVERDUERESPONSESCOUNT = count(*),
                @OVERDUERESPONSESAMOUNT = sum(AMOUNT)
              from
              (
               select
                    distinct O.ID as ID, 
                    AMOUNTINCURRENCY as AMOUNT 
                from 
                    dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) O
                    inner join dbo.PROSPECTPLAN PP on O.PROSPECTPLANID = PP.ID
                    inner join dbo.V_OPPORTUNITYSOLICITOR OS on OS.OPPORTUNITYID = O.ID
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = OS.FUNDRAISERID
                    where
                    O.STATUSCODE=2
                    and OPH.ID in (select SELECTION.ID from @IDS as SELECTION)
                    and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                    and O.RESPONSEDATE<@FUZZYTODAY
                    and (
                        select count(*) 
                        from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.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 (
                            coalesce(O.RESPONSEDATE, O.ASKDATE, O.EXPECTEDASKDATE) between OPH.DATEFROM and coalesce(OPH.DATETO, convert(nvarchar(8), getdate(), 112))
                            or coalesce(O.RESPONSEDATE, O.ASKDATE, O.EXPECTEDASKDATE) is null
                        )
                ) as OVERDUERESPONSERESULT

              select
                @OVERDUEASKSCOUNT = count(*),
                @OVERDUEASKSAMOUNT = sum(AMOUNT)
              from
              (
                select
                    distinct O.ID as ID, 
                    AMOUNTINCURRENCY as AMOUNT 
                from 
                    dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID) O
                    inner join dbo.PROSPECTPLAN PP on O.PROSPECTPLANID = PP.ID
                    inner join dbo.V_OPPORTUNITYSOLICITOR OS on OS.OPPORTUNITYID = O.ID
                    inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = OS.FUNDRAISERID
                  where
                    O.STATUSCODE=1
                    and OPH.ID in (select SELECTION.ID from @IDS as SELECTION)
                    and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                    and O.ASKDATE<@FUZZYTODAY
                    and (
                      select count(*) 
                      from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.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 (
                          coalesce(O.RESPONSEDATE, O.ASKDATE, O.EXPECTEDASKDATE) between OPH.DATEFROM and coalesce(OPH.DATETO, convert(nvarchar(8), getdate(), 112))
                            or coalesce(O.RESPONSEDATE, O.ASKDATE, O.EXPECTEDASKDATE) is null
                        )
             ) as OVERDUERESULT   
            end

          select 
            @OVERDUEASKSCOUNT 'Overdue asks count',
            @OVERDUEASKSAMOUNT 'Overdue asks amount',
            @OVERDUERESPONSESCOUNT 'Overdue response count',
            @OVERDUERESPONSESAMOUNT 'Overdue response amount',
            @CURRENCYID CURRENCYID

          return 0;
        end