USP_DATALIST_OPPORTUNITYPYRAMIDFUNDRAISER

Returns the opportunity pyramid for a given fundraiser.

Parameters

Parameter	Parameter Type	Mode	Description
@STATUSCODE	tinyint	IN	Status
@FUNDRAISERID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.

Definition

Copy



    CREATE procedure dbo.USP_DATALIST_OPPORTUNITYPYRAMIDFUNDRAISER(
          @STATUSCODE tinyint = 0,
          @FUNDRAISERID uniqueidentifier,
          @CURRENTAPPUSERID uniqueidentifier = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
        ) as 
          set nocount on;          

          declare @ORGANIZATIONCURRENCYID uniqueidentifier;
          set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

          declare @BASECURRENCYID uniqueidentifier;

          declare @USERSITEID uniqueidentifier;
          select @USERSITEID = SITEID from dbo.APPUSER where ID = @CURRENTAPPUSERID;

          select @BASECURRENCYID = BASECURRENCYID
            from OPPORTUNITYAMOUNTBRACKETGROUP
           where OPPORTUNITYAMOUNTBRACKETGROUP.ID = dbo.UFN_OPPORTUNITYAMOUNTBRACKETSGROUP_GETBYSITE(@USERSITEID)

          declare @DECIMALDIGITS tinyint;
          declare @ROUNDINGTYPECODE tinyint;

          select
            @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
            @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
          from
            dbo.CURRENCY
          where
            CURRENCY.ID = @BASECURRENCYID;

          with AMOUNTS_CTE as (
            select R.ID,
              sum(O.AMOUNTINCURRENCY) as TOTALAMOUNT,
              count(O.ID) as NUMOPPS
            from dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) R
            inner join dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK(@BASECURRENCYID, @ORGANIZATIONCURRENCYID) O on O.AMOUNTINCURRENCY between R.LOWERLIMIT and R.UPPERLIMIT
            inner join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
            where ((O.STATUSCODE = @STATUSCODE and O.STATUSCODE in (1,2,3)) or (@STATUSCODE = 0 and O.STATUSCODE in (1,2,3)))
              and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = @FUNDRAISERID and O.ID = OS.OPPORTUNITYID)
              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
            group by R.ID
          )          

          select 
            R.NAME,
            R.LOWERLIMIT,
            R.UPPERLIMIT,
            coalesce(A.TOTALAMOUNT, 0) as TOTALAMOUNT,
            coalesce(A.NUMOPPS, 0) as NUMOPPS,
            R.BASECURRENCYID,
            CURRENCYINFO.CURRENCYSYMBOL,
            CURRENCYINFO.ISO4217,
            CURRENCYINFO.DECIMALDIGITS,
            CURRENCYINFO.SYMBOLDISPLAYSETTINGCODE
          from 
            dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) R 
            left join AMOUNTS_CTE A on R.ID = A.ID
            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(R.BASECURRENCYID) as CURRENCYINFO
          order by 
            R.LOWERLIMIT desc