USP_KPI_MAJORGIVING_OPPORTUNITIESAMOUNTFUNDRAISER

Parameters

Parameter	Parameter Type	Mode
@VALUE	money	INOUT
@ASOFDATE	datetime	IN
@INCLUDEUNQUALIFIED	bit	IN
@INCLUDEQUALIFIED	bit	IN
@INCLUDERESPONSEPENDING	bit	IN
@INCLUDEACCEPTED	bit	IN
@INCLUDEREJECTED	bit	IN
@INCLUDECANCELED	bit	IN
@DESIGNATIONID	uniqueidentifier	IN
@PROSPECTPLANTYPECODEID	uniqueidentifier	IN
@PROSPECTPLANSTATUSCODEID	uniqueidentifier	IN
@PROSPECTSTATUSCODEID	uniqueidentifier	IN
@OPPORTUNITYAMOUNTBRACKETID	uniqueidentifier	IN
@ASKDATEFILTER	tinyint	IN
@FUNDRAISERID	uniqueidentifier	IN
@ORGPOSITIONSSELECTIONID	uniqueidentifier	IN
@USEREVENUECOMMITTEDAMOUNT	bit	IN
@CURRENCYID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
Definition

Copy


          CREATE procedure dbo.USP_KPI_MAJORGIVING_OPPORTUNITIESAMOUNTFUNDRAISER
            @VALUE money 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,
            @USEREVENUECOMMITTEDAMOUNT bit = 0,
            @CURRENCYID uniqueidentifier = null,
            @CURRENTAPPUSERID uniqueidentifier = null
          as
            set nocount on;

            if @CURRENCYID is null
              set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

            declare @STARTDATE datetime;
            declare @ENDDATE datetime;

            -- 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
            begin
              with
              OPPORTUNITIES_CTE as
              (
                select
                  case when @DESIGNATIONID is null then 
                    case when O.STATUSCODE = 3 then
                      case when @USEREVENUECOMMITTEDAMOUNT = 1 then dbo.UFN_OPPORTUNITY_REVENUECOMMITTEDINCURRENCY(O.ID, @CURRENCYID)
                      else dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)
                      end
                    else
                      dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)
                    end
                  else
                    sum(dbo.UFN_OPPORTUNITYDESIGNATION_AMOUNTINCURRENCY(OD.ID, @CURRENCYID))
                  end AMOUNT
                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 join dbo.OPPORTUNITYDESIGNATION OD on OD.OPPORTUNITYID = O.ID
                where
                  (
                    -- 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)
                group by
                  O.ID, O.AMOUNT, O.STATUSCODE
              )
              select
                @VALUE = coalesce(sum(OPPORTUNITIES_CTE.AMOUNT), 0)
              from
                OPPORTUNITIES_CTE;
            end
            else
            begin
              declare @IDS as table(ID uniqueidentifier);
              insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, null;

              with
              OPPORTUNITIES_CTE as
              (
                select
                   coalesce(sum(AMOUNT),0) as AMOUNT
                from   
                (
                   select distinct O.ID,
                    case when @DESIGNATIONID is null then 
                      case when O.STATUSCODE = 3 then
                        case when @USEREVENUECOMMITTEDAMOUNT = 1 then dbo.UFN_OPPORTUNITY_REVENUECOMMITTEDINCURRENCY(O.ID, @CURRENCYID)
                        else dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)
                        end
                      else
                        dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)
                      end
                    else
                       dbo.UFN_OPPORTUNITYDESIGNATION_AMOUNTINCURRENCY(OD.ID, @CURRENCYID)
                    end AMOUNT
                  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 join dbo.OPPORTUNITYDESIGNATION OD on OD.OPPORTUNITYID = O.ID
                    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]
                  where
                    (
                      -- 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)
                  group by
                    O.ID, O.AMOUNT, O.STATUSCODE, OD.ID
                ) as RESULT
              ) 

              select
                @VALUE = coalesce(sum(OPPORTUNITIES_CTE.AMOUNT), 0)
              from
                OPPORTUNITIES_CTE;
            end