UFN_OPPORTUNITYAMOUNTBRACKETSGROUP_GETBYSITE

create function dbo.UFN_OPPORTUNITYAMOUNTBRACKETSGROUP_GETBYSITE
(
@USERSITEID uniqueidentifier = null
)
returns uniqueidentifier
with execute as caller
as begin

  declare @GROUPID uniqueidentifier;
  declare @HIERARCHYID hierarchyid;
  declare @LEVEL int;

  if @USERSITEID is null 
    begin                            
        select @GROUPID = ID from dbo.OPPORTUNITYAMOUNTBRACKETGROUP 
        where OPPORTUNITYAMOUNTBRACKETGROUP.ID not in (select distinct OPPORTUNITYAMOUNTBRACKETGROUPID from dbo.OPPORTUNITYAMOUNTBRACKETSITES)                            
    end
  else
      select 
            @GROUPID = O.OPPORTUNITYAMOUNTBRACKETGROUPID,
            @HIERARCHYID = S.HIERARCHYPATH.GetAncestor(1),
            @LEVEL = S.HIERARCHYPATH.GetLevel()
      from dbo.SITE S
      left join dbo.OPPORTUNITYAMOUNTBRACKETSITES O on S.ID = O.SITEID
      where S.ID = @USERSITEID

      if @GROUPID is not null
         return @GROUPID
      else
        if @LEVEL > 1
          begin
            select @USERSITEID = ID from dbo.SITE where HIERARCHYPATH = @HIERARCHYID
            set @GROUPID = dbo.UFN_OPPORTUNITYAMOUNTBRACKETSGROUP_GETBYSITE(@USERSITEID)
          end
        else
          begin                            
            select @GROUPID = ID from dbo.OPPORTUNITYAMOUNTBRACKETGROUP 
            where OPPORTUNITYAMOUNTBRACKETGROUP.ID not in (select distinct OPPORTUNITYAMOUNTBRACKETGROUPID from dbo.OPPORTUNITYAMOUNTBRACKETSITES)                            
        end

      return @GROUPID    
    end