USP_KPI_MAJORGIVING_OPPORTUNITIESCOUNTFUNDRAISER

          CREATE procedure dbo.USP_KPI_MAJORGIVING_OPPORTUNITIESCOUNTFUNDRAISER
            @VALUE int output,
            @ASOFDATE datetime,
            @INCLUDEUNQUALIFIED bit = 0,
            @INCLUDEQUALIFIED bit = 0,
            @INCLUDERESPONSEPENDING bit = 0,
            @INCLUDEACCEPTED bit = 0,
            @INCLUDEREJECTED bit = 0,
            @INCLUDECANCELED bit = 0,
            @DESIGNATIONID uniqueidentifier = null,
            @PROSPECTPLANTYPECODEID uniqueidentifier = null,
            @PROSPECTPLANSTATUSCODEID uniqueidentifier = null,
            @PROSPECTSTATUSCODEID uniqueidentifier = null,
            @OPPORTUNITYAMOUNTBRACKETID uniqueidentifier = null,
            @ASKDATEFILTER tinyint = null,
            @FUNDRAISERID uniqueidentifier = null,
            @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
            @CURRENTAPPUSERID uniqueidentifier = null
          as
            set nocount on;

            declare @STARTDATE datetime;
            declare @ENDDATE datetime;

            -- JamesWill 2011-04-26 WI151871 Copy what the amounts KPI does so unqualified opportunities (which don't have ask dates) work with this KPI

            -- Leave STARTDATE and ENDDATE null if all dates is selected

            -- so that these date filters aren't applied at all.  Otherwise,

            -- if the opportunity doesn't have a date set, it would never

            -- be included if USP_RESOLVEDATEFILTER was called with all dates

            -- since it sets STARTDATE and ENDDATE to min and max dates.

            if @ASKDATEFILTER is not null and @ASKDATEFILTER <> 10
              exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER=@ASKDATEFILTER, @STARTDATE=@STARTDATE output, @ENDDATE=@ENDDATE output, @ASOFDATE=@ASOFDATE;

            if @ORGPOSITIONSSELECTIONID is null
              select
                @VALUE = coalesce(count(distinct O.ID), 0)
              from
                dbo.OPPORTUNITY O
                inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
                inner join dbo.PROSPECT P on P.ID = PP.PROSPECTID
                left outer join dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER_NOSECURITY(@CURRENTAPPUSERID) R on dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID,R.BASECURRENCYID) between R.LOWERLIMIT and R.UPPERLIMIT
                --left outer join dbo.UFN_OPPORTUNITYAMOUNTRANGES() R on dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID,R.BASECURRENCYID) between R.LOWERLIMIT and R.UPPERLIMIT

                left join dbo.OPPORTUNITYDESIGNATION OD on OD.OPPORTUNITYID = O.ID
              where
                (   -- JamesWill 2011-04-26 WI151871 Copy what the amounts KPI does so unqualified opportunities (which don't have ask dates) work with this KPI

                    -- If STARTDATE and ENDDATE are null, it indicates all records should be included.  Using this

                    -- rather than having STARTDATE and ENDDATE set to min and max date since opportunities with

                    -- null dates wouldn't be included

                    (@STARTDATE is null and @ENDDATE is null) or
                    (O.ASKDATE between @STARTDATE and @ENDDATE)
                  )
                and (@FUNDRAISERID is null or exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = @FUNDRAISERID AND OS.OPPORTUNITYID = O.ID))
                and (@DESIGNATIONID is null or OD.DESIGNATIONID = @DESIGNATIONID)
                and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                and (@PROSPECTPLANSTATUSCODEID is null or PP.PROSPECTPLANSTATUSCODEID = @PROSPECTPLANSTATUSCODEID)
                and (@PROSPECTSTATUSCODEID is null or P.PROSPECTSTATUSCODEID = @PROSPECTSTATUSCODEID)
                and (@OPPORTUNITYAMOUNTBRACKETID is null or  R.ID = @OPPORTUNITYAMOUNTBRACKETID)
                and (@INCLUDEUNQUALIFIED = 1 or O.STATUSCODE != 0)
                and (@INCLUDEQUALIFIED = 1 or O.STATUSCODE != 1)
                and (@INCLUDERESPONSEPENDING = 1 or O.STATUSCODE != 2)
                and (@INCLUDEACCEPTED = 1 or O.STATUSCODE != 3)
                and (@INCLUDEREJECTED = 1 or O.STATUSCODE != 4)
                and (@INCLUDECANCELED = 1 or O.STATUSCODE != 5);
            else
            begin
              declare @IDS as table(ID uniqueidentifier);
              insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, null;

              select
                @VALUE = coalesce(count(distinct O.ID), 0)
              from
                dbo.OPPORTUNITY O
                inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
                inner join dbo.PROSPECT P on P.ID = PP.PROSPECTID
                inner join dbo.V_OPPORTUNITYSOLICITOR OS on OS.OPPORTUNITYID = O.ID
                inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = OS.FUNDRAISERID
                    and O.ASKDATE between OPH.DATEFROM and coalesce(OPH.DATETO, O.ASKDATE)
                inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
                left outer join dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER_NOSECURITY(@CURRENTAPPUSERID) R on dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID,R.BASECURRENCYID) between R.LOWERLIMIT and R.UPPERLIMIT
                left join dbo.OPPORTUNITYDESIGNATION OD on OD.OPPORTUNITYID = O.ID
              where
                 (   -- JamesWill 2011-04-26 WI151871 Copy what the amounts KPI does so unqualified opportunities (which don't have ask dates) work with this KPI

                    -- If STARTDATE and ENDDATE are null, it indicates all records should be included.  Using this

                    -- rather than having STARTDATE and ENDDATE set to min and max date since opportunities with

                    -- null dates wouldn't be included

                    (@STARTDATE is null and @ENDDATE is null) or
                    (O.ASKDATE between @STARTDATE and @ENDDATE)
                  )
                and (@FUNDRAISERID is null or @FUNDRAISERID = OS.FUNDRAISERID)
                and (@DESIGNATIONID is null or OD.DESIGNATIONID = @DESIGNATIONID)
                and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                and (@PROSPECTPLANSTATUSCODEID is null or PP.PROSPECTPLANSTATUSCODEID = @PROSPECTPLANSTATUSCODEID)
                and (@PROSPECTSTATUSCODEID is null or P.PROSPECTSTATUSCODEID = @PROSPECTSTATUSCODEID)
                and (@OPPORTUNITYAMOUNTBRACKETID is null or R.ID = @OPPORTUNITYAMOUNTBRACKETID)
                and (@INCLUDEUNQUALIFIED = 1 or O.STATUSCODE != 0)
                and (@INCLUDEQUALIFIED = 1 or O.STATUSCODE != 1)
                and (@INCLUDERESPONSEPENDING = 1 or O.STATUSCODE != 2)
                and (@INCLUDEACCEPTED = 1 or O.STATUSCODE != 3)
                and (@INCLUDEREJECTED = 1 or O.STATUSCODE != 4)
                and (@INCLUDECANCELED = 1 or O.STATUSCODE != 5);

            end