USP_DATALIST_OPPORTUNITYAMOUNTBRACKETS

Returns a list of opportunity amount bracket groups.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@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_OPPORTUNITYAMOUNTBRACKETS
                (
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as
                    select 
                        G.ID,
                        L.NAME,
                        L.LOWERLIMIT,
                        min(U.LOWERLIMIT) - power(10.0000,-CURRENCY.DECIMALDIGITS) UPPERLIMIT,
                        G.NAME GROUPNAME,
                        G.BASECURRENCYID
                    from
                        dbo.OPPORTUNITYAMOUNTBRACKETGROUP G
                    inner join dbo.CURRENCY on G.BASECURRENCYID = CURRENCY.ID
                    inner join dbo.OPPORTUNITYAMOUNTBRACKET L on G.ID = L.OPPORTUNITYBRACKETGROUPID 
                    left outer join dbo.OPPORTUNITYAMOUNTBRACKET U on U.LOWERLIMIT>L.LOWERLIMIT and G.ID = U.OPPORTUNITYBRACKETGROUPID               
                    left outer join dbo.OPPORTUNITYAMOUNTBRACKETSITES on OPPORTUNITYAMOUNTBRACKETSITES.OPPORTUNITYAMOUNTBRACKETGROUPID = G.ID
                    where 
                        (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[OPPORTUNITYAMOUNTBRACKETSITES].[SITEID] or (SITEID is null and [OPPORTUNITYAMOUNTBRACKETSITES].[SITEID] is null))) and
                        (
                            @SITEFILTERMODE = 0 
                            or 
                            OPPORTUNITYAMOUNTBRACKETSITES.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
                        )
                    group by 
                        G.NAME,
                        G.ID,
                        L.NAME,
                        L.LOWERLIMIT,
                        U.OPPORTUNITYBRACKETGROUPID,
                        L.OPPORTUNITYBRACKETGROUPID,
                        G.BASECURRENCYID,
                        CURRENCY.DECIMALDIGITS
                    order by
                        G.name,
                        L.LOWERLIMIT desc