create or replace procedure expanddown
(
 memberid IN number,
 memberset IN varchar2,
 visitset IN varchar2,
 returnvisit OUT varchar2
)
AS
type t_member is ref cursor;
v_member t_member;
newvisitset varchar2(2000);
x number;
pos number;
tempmemberset varchar2(2000);
newmemberset varchar2(2000);
v_rule_id number;
v_member_id number;
v_group_id number;
BEGIN	
  tempmemberset := memberset;
  newvisitset := visitset;
  pos:=instr(tempmemberset,',');
  if (pos = 1) then
    tempmemberset:=substr(tempmemberset,pos+1);  
  end if;
  if (',' = substr(tempmemberset,length(tempmemberset),1)) then
     tempmemberset:=substr(tempmemberset,1,length(tempmemberset)-1);  
  end if;
  x:=-1;
  pos:=instr(tempmemberset,',');
  if pos != 0 then
    if pos != 1 then
      x:=to_number(substr(tempmemberset,1,pos-1));
    end if;
    tempmemberset:=substr(tempmemberset,pos+1);
   else
     if (length(tempmemberset)>0) then
        x:=to_number(tempmemberset);
        tempmemberset:='';
     end if;     
  end if;
  while x != -1 loop 
     insert_relation (x,memberid,0);   
     insert into rodmess values ('~' || to_char(x) ||'~' || tempmemberset || '~');
     commit;
     open v_member for  select  --detemine if this group x is choked 
		  group_id 
		from
		  grouping
	     where
		  group_id = x and
		  choke_point_flag = 1;
     fetch v_member into v_group_id;
     if (v_member%rowcount = 1) then  --if choked 
        insert into rodmess values ('Expand Down CHOKED: ' || to_char(x));
        commit;     
       else  -- if not        
        insert into rodmess values ('Expand Down NO_CHOKED: ' || to_char(x));
        commit;     
        open v_member for select rule_id, member_group_id from group_member where
		  group_id = x and
		  hard_code_remove_flag != 1 and
		  to_char(RULE_ID) = PARENT_RULE_GROUP and
		  group_id != member_group_id;
        newmemberset := '';     
        loop
           fetch v_member into v_rule_id, v_member_id;
           exit when v_member%NOTFOUND;          
  	       if instr(newvisitset,v_member_id || ':' || to_char(x) ) = 0 then
               newvisitset := newvisitset || v_member_id || ':' || to_char(x) || ',';
   	           newmemberset := newmemberset || v_member_id || ',';
           end if;
        end loop;
        close v_member;
        insert into rodmess values ('newmem: ' || newmemberset);
        commit;
        if length(newmemberset)>0 then
           returnvisit := '';
           expanddown (x,newmemberset,newvisitset,newvisitset);
        end if;	
     end if; -- end choked test  
     pos:=instr(tempmemberset,',');
     x:= -1;
     if pos != 0 then
       if pos != 1 then
         x:=to_number(substr(tempmemberset,1,pos-1));
       end if;
       tempmemberset:=substr(tempmemberset,pos+1);
      else
       if (length(tempmemberset)>0) then
         x:=to_number(tempmemberset);
         tempmemberset:='';
       end if;     
     end if;  
  end loop; 
  returnvisit := newvisitset || 'donevisit';
end expanddown; 
/
show errors;
grant execute on expanddown to public;