USP_KPI_OPPORTUNITYAMOUNTBYSELECTION

          CREATE procedure dbo.USP_KPI_OPPORTUNITYAMOUNTBYSELECTION
            @VALUE money output,
            @ASOFDATE datetime, 
            @FUNDRAISERID uniqueidentifier = null,
            @SELECTIONID uniqueidentifier,
            @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
            @USEREVENUECOMMITTEDAMOUNT bit = 0,
            @CURRENCYID uniqueidentifier = null
          with execute as owner
          as
            set nocount on;

            declare @SQLTOEXEC nvarchar(max);

            declare @SELECTIONDBOBJECTNAME nvarchar(128);
            declare @SELECTIONDBOBJECTTYPE smallint;

            declare @POSITIONDBOBJECTNAME nvarchar(128);
            declare @POSITIONDBOBJECTTYPE smallint;

            if @SELECTIONID is not null begin
              if not exists(select ID from dbo.IDSETREGISTER where ID = @SELECTIONID) raiserror('ID set does not exist in the database.', 15, 1);
              select @SELECTIONDBOBJECTNAME = DBOBJECTNAME, @SELECTIONDBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @SELECTIONID;
              if @SELECTIONDBOBJECTTYPE = 1 set @SELECTIONDBOBJECTNAME = @SELECTIONDBOBJECTNAME + '()';
              else if @SELECTIONDBOBJECTTYPE = 2 set @SELECTIONDBOBJECTNAME = @SELECTIONDBOBJECTNAME + '(''' + convert(nvarchar(36), @SELECTIONID) + ''')';
            end

            if @ORGPOSITIONSSELECTIONID is not null begin
              if not exists(select ID from dbo.IDSETREGISTER where ID = @ORGPOSITIONSSELECTIONID) raiserror('ID set does not exist in the database.', 15, 1);
              select @POSITIONDBOBJECTNAME = DBOBJECTNAME, @POSITIONDBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @ORGPOSITIONSSELECTIONID;
              if @POSITIONDBOBJECTTYPE = 1 set @POSITIONDBOBJECTNAME = @POSITIONDBOBJECTNAME + '()';
              else if @POSITIONDBOBJECTTYPE = 2 set @POSITIONDBOBJECTNAME = @POSITIONDBOBJECTNAME + '(''' + convert(nvarchar(36), @ORGPOSITIONSSELECTIONID) + ''')';
            end

            set @SQLTOEXEC = 
              'select 
                @VALUE =
                  coalesce(sum(AMOUNT),0) 
                   from 
                   (                  
                    select distinct O.ID, 
                      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
                      as AMOUNT
              from
                dbo.OPPORTUNITY O
                inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID' + nchar(13);

            if @SELECTIONID is not null
              set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @SELECTIONDBOBJECTNAME + ' as SELECTION on O.[ID] = SELECTION.[ID]' + nchar(13);

            if @ORGPOSITIONSSELECTIONID is not null
              set @SQLTOEXEC = @SQLTOEXEC + 
                '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 ' + @POSITIONDBOBJECTNAME + ' as POSITIONS on OPH.[ID] = POSITIONS.[ID]' + nchar(13);


            set @SQLTOEXEC = @SQLTOEXEC + 'where (@FUNDRAISERID is null or exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = @FUNDRAISERID AND OS.OPPORTUNITYID = O.ID))';

            set @SQLTOEXEC = @SQLTOEXEC + ' ) as RESULT '

            exec sp_executesql @SQLTOEXEC, 
                N'@VALUE money output, @FUNDRAISERID uniqueidentifier, @USEREVENUECOMMITTEDAMOUNT tinyint, @CURRENCYID uniqueidentifier',
                @VALUE=@VALUE output, @FUNDRAISERID=@FUNDRAISERID, @USEREVENUECOMMITTEDAMOUNT=@USEREVENUECOMMITTEDAMOUNT, @CURRENCYID=@CURRENCYID;