USP_DATALIST_OPPORTUNITYPYRAMIDDESIGNATION

Returns the opportunity pyramid for a given designation.
Parameters

Parameter	Parameter Type	Mode	Description
@STATUSCODE	tinyint	IN	Status
@DESIGNATIONID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@ORGPOSITIONSSELECTIONID	uniqueidentifier	IN	Organizational team
@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_OPPORTUNITYPYRAMIDDESIGNATION(
          @STATUSCODE tinyint = 0,
          @DESIGNATIONID uniqueidentifier,
          @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
          @CURRENTAPPUSERID uniqueidentifier = null,
          @SECURITYFEATUREID uniqueidentifier = null,
          @SECURITYFEATURETYPE tinyint = null
        ) as 
          set nocount on;

          if @ORGPOSITIONSSELECTIONID is null
            begin
            with DESIGNATIONS_CTE as (
              select D2.ID 
              from dbo.DESIGNATION D
                inner join dbo.DESIGNATION D2 on (D.DESIGNATIONLEVEL1ID = D2.DESIGNATIONLEVEL1ID
                      and (D.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                      and (D.DESIGNATIONLEVEL3ID = D2.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                      and (D.DESIGNATIONLEVEL4ID = D2.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                      and (D.DESIGNATIONLEVEL5ID = D2.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null))
              where D.ID = @DESIGNATIONID
              ),

            AMOUNTS_CTE as (
              select R.ID,
                sum(
                case OD.BASECURRENCYID when R.BASECURRENCYID then 
                  OD.AMOUNT 
                else 
                  dbo.UFN_CURRENCY_CONVERT(OD.AMOUNT,dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(OD.BASECURRENCYID,R.BASECURRENCYID,getdate(),0,null)) 
                end
                ) as TOTALAMOUNT,
                count(distinct O.ID) as NUMOPPS
              from dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) R 
              cross apply OPPORTUNITY O 
              inner join dbo.OPPORTUNITYDESIGNATION OD on O.ID = OD.OPPORTUNITYID
              inner join DESIGNATIONS_CTE D on OD.DESIGNATIONID = D.ID
              where (O.STATUSCODE = @STATUSCODE or (@STATUSCODE = 0 and O.STATUSCODE in (1,2,3)))
              and ((O.BASECURRENCYID = R.BASECURRENCYID and O.AMOUNT between R.LOWERLIMIT and R.UPPERLIMIT)
              or
              (O.BASECURRENCYID <> R.BASECURRENCYID and 
                coalesce(
                  dbo.UFN_CURRENCY_CONVERT(O.AMOUNT,dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(O.BASECURRENCYID,R.BASECURRENCYID,getdate(),0,null)),
                  0
                ) between R.LOWERLIMIT and R.UPPERLIMIT
              ))
              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
            end
          else
            begin
            declare @IDS as table(ID uniqueidentifier);
            insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;               

            with DESIGNATIONS_CTE as (
              select D2.ID 
              from dbo.DESIGNATION D
                inner join dbo.DESIGNATION D2 on (D.DESIGNATIONLEVEL1ID = D2.DESIGNATIONLEVEL1ID
                      and (D.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                      and (D.DESIGNATIONLEVEL3ID = D2.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                      and (D.DESIGNATIONLEVEL4ID = D2.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                      and (D.DESIGNATIONLEVEL5ID = D2.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null))
              where D.ID = @DESIGNATIONID
              ),
            FUNDRAISERS_CTE as (
              select F.PROSPECTPLANID, F.FUNDRAISERID, OPH.DATEFROM, OPH.DATETO
              from (
                  select ID as PROSPECTPLANID,
                    PRIMARYMANAGERFUNDRAISERID as FUNDRAISERID
                  from dbo.PROSPECTPLAN 
                  where PRIMARYMANAGERFUNDRAISERID is not null
                  union all
                  select ID as PROSPECTPLANID,
                    SECONDARYMANAGERFUNDRAISERID as FUNDRAISERID
                  from dbo.PROSPECTPLAN 
                  where SECONDARYMANAGERFUNDRAISERID is not null
                  union all
                  select PROSPECTPLANID as PROSPECTPLANID,
                    FUNDRAISERID as FUNDRAISERID
                  from dbo.SECONDARYFUNDRAISER
                ) as F
              inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH 
                on OPH.CONSTITUENTID = F.FUNDRAISERID
              inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
              ),  

            AMOUNTS_CTE as (
              select R.ID,
                sum(
                case OD.BASECURRENCYID when R.BASECURRENCYID then 
                  OD.AMOUNT 
                else 
                  dbo.UFN_CURRENCY_CONVERT(OD.AMOUNT,dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(OD.BASECURRENCYID,R.BASECURRENCYID,getdate(),0,null)) 
                end
                ) as TOTALAMOUNT,
                count(distinct O.ID) as NUMOPPS
              from dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) R 
              cross apply OPPORTUNITY O 
              inner join dbo.OPPORTUNITYDESIGNATION OD on O.ID = OD.OPPORTUNITYID
              inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = O.PROSPECTPLANID
              inner join DESIGNATIONS_CTE D on OD.DESIGNATIONID = D.ID
                where (O.STATUSCODE = @STATUSCODE or (@STATUSCODE = 0 and O.STATUSCODE in (1,2,3)))
                  and exists (select PROSPECTPLANID from FUNDRAISERS_CTE F 
                          where F.PROSPECTPLANID = O.PROSPECTPLANID 
                            and O.ASKDATE between F.DATEFROM and coalesce(F.DATETO, O.ASKDATE))
                  and ((O.BASECURRENCYID = R.BASECURRENCYID and O.AMOUNT between R.LOWERLIMIT and R.UPPERLIMIT)
                    or
                    (O.BASECURRENCYID <> R.BASECURRENCYID and 
                      coalesce(
                        dbo.UFN_CURRENCY_CONVERT(O.AMOUNT,dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(O.BASECURRENCYID,R.BASECURRENCYID,getdate(),0,null)),
                        0
                      ) between R.LOWERLIMIT and R.UPPERLIMIT
                    ))
                                  and (
                                      select count(*) 
                                      from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.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

            end