USP_KPI_MAJORGIVING_PROSPECTPLANSFORFUNDRAISER

          CREATE procedure dbo.USP_KPI_MAJORGIVING_PROSPECTPLANSFORFUNDRAISER
            @VALUE int output,
            @ASOFDATE datetime, 
            @PROSPECTPLANTYPECODEID uniqueidentifier = null,
            @PROSPECTPLANSTATUSCODEID uniqueidentifier = null,
            @PROSPECTSTATUSCODEID uniqueidentifier = null,
            @DESIGNATIONID uniqueidentifier = null,
            @OPPORTUNITYAMOUNTBRACKETID uniqueidentifier = null,
            @FUNDRAISERID uniqueidentifier = null,
            @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
            @CURRENTAPPUSERID uniqueidentifier = null
          as

            set nocount on;

            if @ORGPOSITIONSSELECTIONID is null
              select
                @VALUE = coalesce(count(distinct PP.ID), 0)
              from
                dbo.PROSPECTPLAN PP
                inner join dbo.PROSPECT P on PP.PROSPECTID=P.ID
                left outer join dbo.OPPORTUNITY O on O.PROSPECTPLANID = PP.ID
                left outer join dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) R on dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID,R.BASECURRENCYID) between R.LOWERLIMIT and R.UPPERLIMIT
                left outer join dbo.OPPORTUNITYDESIGNATION OD on OD.OPPORTUNITYID = O.ID
              where
                (@FUNDRAISERID is null or exists (select 1 from dbo.UFN_FUNDRAISER_PROSPECTPLANS(@FUNDRAISERID) FPP where PP.ID = FPP.ID))
                and PP.ISACTIVE = 1
                and (@PROSPECTSTATUSCODEID is null or P.PROSPECTSTATUSCODEID = @PROSPECTSTATUSCODEID)
                and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                and (@PROSPECTPLANSTATUSCODEID is null or PP.PROSPECTPLANSTATUSCODEID = @PROSPECTPLANSTATUSCODEID)
                and (@DESIGNATIONID is null or OD.DESIGNATIONID = @DESIGNATIONID)
                and (@OPPORTUNITYAMOUNTBRACKETID is null or R.ID = @OPPORTUNITYAMOUNTBRACKETID)
            else
            begin
              declare @IDS as table(ID uniqueidentifier);
              insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, null;

              select
                @VALUE = coalesce(count(distinct PP.ID), 0)
              from
                dbo.PROSPECTPLAN PP
                inner join dbo.PROSPECT P on PP.PROSPECTID=P.ID
                inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
                  on (OPH.CONSTITUENTID = PP.PRIMARYMANAGERFUNDRAISERID) or (OPH.CONSTITUENTID = PP.SECONDARYMANAGERFUNDRAISERID) or ((select count(ID) from dbo.SECONDARYFUNDRAISER SF where SF.PROSPECTPLANID = PP.ID and SF.FUNDRAISERID = OPH.CONSTITUENTID) > 0) 
                  and @ASOFDATE between OPH.DATEFROM and coalesce(OPH.DATETO, @ASOFDATE)
                inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
                left outer join dbo.OPPORTUNITY O on O.PROSPECTPLANID = PP.ID
                left outer join dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) R on dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID,R.BASECURRENCYID) between R.LOWERLIMIT and R.UPPERLIMIT
                left outer join dbo.OPPORTUNITYDESIGNATION OD on OD.OPPORTUNITYID = O.ID
              where
                (@FUNDRAISERID is null or exists (select 1 from dbo.UFN_FUNDRAISER_PROSPECTPLANS(@FUNDRAISERID) FPP where PP.ID = FPP.ID))
                and PP.ISACTIVE = 1
                and (@PROSPECTSTATUSCODEID is null or P.PROSPECTSTATUSCODEID = @PROSPECTSTATUSCODEID)
                and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
                and (@PROSPECTPLANSTATUSCODEID is null or PP.PROSPECTPLANSTATUSCODEID = @PROSPECTPLANSTATUSCODEID)
                and (@DESIGNATIONID is null or OD.DESIGNATIONID = @DESIGNATIONID)
                and (@OPPORTUNITYAMOUNTBRACKETID is null or  R.ID = @OPPORTUNITYAMOUNTBRACKETID)
            end