UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER_NOSECURITY

Returns opportunity amount brackets with a calculated upper limit for a given group, not restricting records by site if no appuser ID is supplied.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN
Definition

Copy


            create function dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER_NOSECURITY(@CURRENTAPPUSERID uniqueidentifier)
            returns  @amountBrackets table 
            (ID uniqueidentifier, 
             NAME nvarchar(100),
             LOWERLIMIT money, 
             UPPERLIMIT money,
             BASECURRENCYID uniqueidentifier)            
            as    
            begin

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

            insert into @amountBrackets
            select
                    L.ID,
                    L.NAME,
                    L.LOWERLIMIT,
                    coalesce(min(U.LOWERLIMIT)- power(10.0000,-CURRENCY.DECIMALDIGITS),922337203685477.58) UPPERLIMIT,
                    G.BASECURRENCYID
                from          
                    dbo.OPPORTUNITYAMOUNTBRACKETGROUP G
                    inner join dbo.CURRENCY on CURRENCY.ID = G.BASECURRENCYID
                    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               
                where @CURRENTAPPUSERID is null or G.ID = dbo.UFN_OPPORTUNITYAMOUNTBRACKETSGROUP_GETBYSITE(@USERSITEID)
                group by 
                    L.ID,
                    L.NAME,
                    L.LOWERLIMIT,
                    G.BASECURRENCYID,
                    CURRENCY.DECIMALDIGITS

            if (select count(*) from @amountBrackets) = 0 and (select ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID) = 1
            begin
                declare @FIRSTBRACKETGROUPID uniqueidentifier;
                select top 1 @FIRSTBRACKETGROUPID = ID from dbo.OPPORTUNITYAMOUNTBRACKETGROUP order by DATEADDED;

                insert into @amountBrackets
                select
                        L.ID,
                        L.NAME,
                        L.LOWERLIMIT,
                        coalesce(min(U.LOWERLIMIT)-power(10.0000,-CURRENCY.DECIMALDIGITS),922337203685477.58) UPPERLIMIT,
                        G.BASECURRENCYID
                    from          
                        dbo.OPPORTUNITYAMOUNTBRACKETGROUP G
                        inner join dbo.CURRENCY on CURRENCY.ID = G.BASECURRENCYID
                        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               
                    where G.ID = @FIRSTBRACKETGROUPID
                    group by 
                        L.ID,
                        L.NAME,
                        L.LOWERLIMIT,
                        G.BASECURRENCYID,
                        CURRENCY.DECIMALDIGITS
            end
        return
        end