create or replace procedure insert_member
(
memberid IN number,
groupid IN number
)
AS 
type t_member is ref cursor;
v_member t_member;
returnvisit varchar2(2000);
newvisitset varchar2(2000);
pos number;
newmemberset varchar2(2000);
v_rule_id number;
v_member_id number;
v_group_id number;
BEGIN 
  open v_member for  select  --detemine if this group is choked 
		  group_id 
		from
		  grouping
	     where
		  group_id = groupid and
		  choke_point_flag = 1;
   fetch v_member into v_group_id;
   if (v_member%rowcount = 1) then 
        insert into rodmess values ('insert member CHOKED: ' || to_char(groupid));
        commit; 
        return;    
   end if;         
   open v_member for select --look for hardcode remove on this relation
		  rule_id 
		from
		  group_member
	     where
		  group_id = groupid and
		  member_group_id = memberid and
		  hard_code_remove_flag = 1;
   fetch v_member into v_rule_id;          
   if (v_member%rowcount= 1) then    
      return;
   end if; 
   open v_member for select 
		  rule_id, 
		  hard_code_add_flag,
          hard_code_remove_flag 
		from
		  group_member
	     where
		  group_id = groupid and
		  member_group_id = memberid and 
		  hard_code_remove_flag != 1;
    open v_member for  select 
		  rule_id, member_group_id 
		from
		  group_member
	     where
		  group_id = groupid and
		  hard_code_remove_flag != 1 and
		  to_char(RULE_ID) = PARENT_RULE_GROUP and
		  group_id != member_group_id;
 newmemberset :='';
 newvisitset :='';
 pos :=v_member%rowcount; 
  insert into rodmess values (to_char(pos));
 commit;
    loop
         fetch v_member into v_rule_id,v_member_id;          
         exit when v_member%NOTFOUND;
     insert into rodmess values (to_char(v_member_id) || ':' || to_char(groupid));
     commit;
	  if (ListFind(to_char(v_member_id) || ':' || to_char(groupid),newvisitset,',' ) = 0 ) then
         newvisitset := newvisitset || to_char(v_member_id) || ':' || to_char(groupid) || ',';
	     newmemberset := newmemberset || to_char(v_member_id) || ',';
      end if;
     insert into rodmess values (newvisitset);
     commit;
     insert into rodmess values (newmemberset);
     commit;
    end loop; 
    close v_member;
       insert into rodmess values ('befinn newmem: ' || newmemberset);
     commit;
if length(newmemberset)>0 then
     insert into rodmess values ('innsert newmem: ' || newmemberset);
     commit;
     expanddown (groupid,newmemberset,newvisitset,returnvisit);
 end if;	 
end insert_member; 
/
show errors;
grant execute on insert_member to public;