create or replace procedure expandup
(
 memberid IN number,
 groupid IN number,
 prg IN varchar2,
 rid IN varchar2,
 nolistarg IN varchar2,
 visitgrp IN varchar2
)
AS
type t_member is ref cursor;
type int_array is table of number index by binary_integer;
nextruleid int_array;
nextgroupid int_array;
v_member t_member;
x number;
pos number;
thecount number;
newvisitgrp varchar2(2000);
templist varchar2(2000);
v_rule_id number;
v_group_id number;
newprg varchar(2000);
nolist varchar(2000);
v_hardcodeadd number;
v_hardcoderem number;
p number;
currentruleid number;
BEGIN
  insert into rodmess values (' ExpUp Member: ' || to_char(memberid) || ' Grp: ' || to_char(groupid) || ' PRG: ' || prg || ' RID: ' || rid || ' Visit ' || visitgrp || ' No List: ' || nolistarg);
  commit;
  newvisitgrp := visitgrp || to_char(groupid) || ',';
  templist := nolistarg;
  pos:=instr(templist,',');
  if (pos = 1) then
    templist:=substr(templist,pos+1);  
  end if;
  if (',' = substr(templist,length(templist),1)) then
    templist:=substr(templist,1,length(templist)-1);  
  end if;
  x:=0;
  while x!= -1 loop
     x:=-1;
     pos:=instr(templist,',');
     if pos != 0 then
       if pos != 1 then
         x:=to_number(substr(templist,1,pos-1));
       end if;
       templist:=substr(templist,pos+1);
      else
       if (length(templist)>0) then
         x:=to_number(templist);
         templist:='';
       end if;     
     end if;
     if (x = groupid) then
       insert into rodmess values (' In No List: ' || nolistarg || ' Grp: ' || to_char(groupid));
       commit;
	   return;
     end if;
  end loop; 
  open v_member for select rule_id --safety check to see if member is hardcoded out of group  
		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
     insert into rodmess values (' Hard 2: ' || to_char(v_rule_id) || ' Member : ' || to_char(memberid) || ' Grp: ' || to_char(groupid));
     commit;
     close v_member;
     return;
  end if;
  close v_member;
  open v_member for select rule_id, hard_code_add_flag  --determine if member hardcoded in group
		from
		  group_member
	     where
		  group_id = groupid and
		  member_group_id = memberid and 
		  --hard_code_add_flag = 1 and
		  to_char(RULE_ID) = PARENT_RULE_GROUP;     
  fetch v_member into v_rule_id,v_hardcodeadd;
  if (v_member%rowcount = 1) then  --see if we have hit this rule already
    templist := prg;
    pos:=instr(templist,'.');
    if (pos = 1) then
      templist:=substr(templist,pos+1);  
    end if;
    if ('.' = substr(templist,length(templist),1)) then
      templist:=substr(templist,1,length(templist)-1);  
    end if;
    x:=0;
    while x!= -1 loop
      x:=-1;
      pos:=instr(templist,'.');
      if pos != 0 then
        if pos != 1 then
          x:=to_number(substr(templist,1,pos-1));
        end if;
        templist:=substr(templist,pos+1);
       else
        if (length(templist)>0) then
          x:=to_number(templist);
        end if;     
      end if;
      if (x = v_rule_id) then
        insert into rodmess values (' Cycle  Rule: ' || to_char(v_rule_id) || ' Member: ' || to_char(memberid) || ' Grp: ' || to_char(groupid) || ' PRG: ' || prg || ' Rid: ' || rid);
        commit;
        return;
      end if;
    end loop;
  end if;   
  newprg := rid || '.' || prg;
  templist := newprg;
  pos:=instr(templist,'.');
  if (pos = 1) then
    templist:=substr(templist,pos+1);  
  end if;
  if ('.' = substr(templist,length(templist),1)) then
    templist:=substr(templist,1,length(templist)-1);  
  end if;
   x:=-1;
   pos:=instr(templist,'.');
   if pos != 0 then
     if pos != 1 then
       x:=to_number(substr(templist,1,pos-1));
     end if;
     templist:=substr(templist,pos+1);
    else
     if (length(templist)>0) then
       x:=to_number(templist);
       templist:='';
     end if; 
   end if;    
  while x!= -1 loop
    p :=x;
    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
          -- to_char(p) in (replace(parent_rule_group,'.',',')) and
		  hard_code_remove_flag != 1 and
		  PARENT_RULE_GROUP = newprg;
    fetch v_member into v_rule_id,v_hardcodeadd,v_hardcoderem;
    if (v_member%rowcount = 1) then
              UPDATE
                  group_member
                SET		
					 touched_flag = 1,
				     LAST_MODIFIED_DATE =  sysdate,
		             LAST_MODIFIED_BY = 5772
	             WHERE
				   rule_id = v_rule_id;  
            commit;                       
            insert into rodmess values (' HardAdd: ' || to_char(v_hardcodeadd) || ' HardRem: ' || to_char(v_hardcoderem) || ' Rule: ' || to_char(v_rule_id) || ' Member: ' || to_char(memberid) || ' Grp: ' || to_char(groupid));
            commit;
            currentruleid := v_rule_id;
            exit;
   else
       open v_member for  select 
		        rule_id_seq.nextval as newseq
		      from 
		        dual;             
        fetch v_member into currentruleid;
        insert into rodmess values (' Insert Rule: ' || to_char(currentruleid) || ' Member: ' || to_char(memberid) || ' Grp: ' || to_char(groupid) || ' PRG: ' || newprg || ' Par ID: ' || to_char(p));
        commit;
        close v_member;
         INSERT INTO
	           group_member
		          (
                     RULE_ID,
					 MEMBER_GROUP_ID,
					 GROUP_ID,
					 HARD_CODE_ADD_FLAG,
                     HARD_CODE_REMOVE_FLAG,
					 PARENT_RULE_GROUP,
					 PARENT_RULE_ID,
                     DYNAMIC_ADD_FLAG,
                     TOUCHED_FLAG,
                     DYNAMIC_DELETION_FLAG,
                     MARKED_FOR_DELETION,
                     PROCESS_CHANGE_FLAG,
                     PROCESS_INSERT_FLAG,
                     SOURCE_INSTITUTION_ID,
                     DATA_SOURCE_KEY,                     
		             CREATED_DATE,
		             CREATED_BY,
		             LAST_MODIFIED_DATE,
		             LAST_MODIFIED_BY
		            )
	              VALUES
	                (
					 currentruleid,
					 memberid,    
					 groupid,
					 0,
                     0,
					 newprg,
					 p,
                     0,
                     1,
                     0,
                     0,
                     0,
                     0,
                     0,
                     'CNAV',                     
   		             sysdate,
		             5772,
		             sysdate,
		             5772
			        );
                    commit;
                    exit;
    end if;     
    x:=-1;
    pos:=instr(templist,'.');
    if pos != 0 then
      if pos != 1 then
        x:=to_number(substr(templist,1,pos-1));
      end if;
      templist:=substr(templist,pos+1);
     else
      if (length(templist)>0) then
        x:=to_number(templist);
       templist:='';
      end if; 
    end if;          
 end loop;
  open v_member for  select  --determine 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 ('Expand Up CHOKED: ' || to_char(groupid));
        commit; 
        return;    
   end if;        
 open v_member for  select  --get groups that this group is hardcoded out 
		  group_id 
		from
		  group_member
	     where
		  member_group_id = groupid and
		  hard_code_remove_flag = 1;
    nolist := nolistarg; --make a list from results
     loop
         fetch v_member into v_group_id;
         exit when v_member%NOTFOUND;
         nolist := nolist || to_char(v_group_id) || ',';
     end loop; 
    open v_member for select  --find all groups this group is in
		  rule_id, group_id 
		from
		  group_member
	     where
		  member_group_id = groupid and
		  hard_code_remove_flag != 1 and
		  to_char(RULE_ID) = PARENT_RULE_GROUP;
     thecount := 1;
     loop
         fetch v_member into v_rule_id,v_group_id;
         exit when v_member%NOTFOUND;
          nextruleid(thecount) := v_rule_id;
          nextgroupid(thecount) := v_group_id;
	     thecount := thecount + 1;
     end loop;     
 thecount := thecount - 1;
 FOR i in 1 .. thecount loop
    if (memberid != nextgroupid(i) and
          ListFind(to_char(nextgroupid(i)),newvisitgrp,',') = 0 and     
          ListFind(to_char(nextruleid(i)),newprg,'.') = 0) then       
          expandup (memberid,nextgroupid(i),newprg,to_char(nextruleid(i)),nolist,newvisitgrp);
     else
        insert into rodmess values ('Stopped Rec: ' || to_char(memberid) || to_char(nextgroupid(i)) || newprg || to_char(nextruleid(i)) || nolist || newvisitgrp);
        commit;
     end if; 
end loop;  
end expandup; 
/
show errors;
grant execute on expandup to public;