USP_DATALIST_OPPORTUNITYPYRAMID

                CREATE procedure dbo.USP_DATALIST_OPPORTUNITYPYRAMID(
                    @STATUSCODE tinyint = 0,
                    @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @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 = OPPORTUNITYAMOUNTBRACKETGROUP.BASECURRENCYID
                    from
                        dbo.OPPORTUNITYAMOUNTBRACKETGROUP
                        inner join dbo.OPPORTUNITYAMOUNTBRACKETSITES
                            on OPPORTUNITYAMOUNTBRACKETSITES.OPPORTUNITYAMOUNTBRACKETGROUPID = OPPORTUNITYAMOUNTBRACKETGROUP.ID
                    where
                        OPPORTUNITYAMOUNTBRACKETSITES.SITEID = @USERSITEID;

                    declare @DECIMALDIGITS tinyint;
                    declare @ROUNDINGTYPECODE tinyint;

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

                    if @ORGPOSITIONSSELECTIONID is null
                        begin
                        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 on PROSPECTPLAN.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 (
                                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
                    else
                        begin
                        declare @IDS as table(ID uniqueidentifier);
                        insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;                             

                        with AMOUNTS_CTE as (
              select 
                  ID,
                  sum(TOTALAMOUNT) as TOTALAMOUNT,
                  count(*) as NUMOPPS
               from 
               (
                                select distinct
                    O.ID as OPPORTUNITYID,
                    R.ID as ID,
                                    O.AMOUNTINCURRENCY as TOTALAMOUNT                                
                                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 on PROSPECTPLAN.ID = O.PROSPECTPLANID
                  inner join dbo.V_OPPORTUNITYSOLICITOR OS on OS.OPPORTUNITYID = O.ID
                  inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = OS.FUNDRAISERID
                        where ((O.STATUSCODE = @STATUSCODE and O.STATUSCODE in (1,2,3)) or (@STATUSCODE = 0 and O.STATUSCODE in (1,2,3)))
                                    and ( coalesce(O.ASKDATE, O.EXPECTEDASKDATE) between OPH.DATEFROM and coalesce(OPH.DATETO, O.ASKDATE, O.EXPECTEDASKDATE))
                                    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
                    and OPH.ID in (select SELECTION.ID from @IDS as SELECTION)
                          ) as RESULT                         
                              group by 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