create or replace procedure insert_relation
(
memberid IN number,
groupid IN number,
hardcode IN number
)
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;
nolistarg varchar2(2000);
nolist varchar2(2000);
thecount number;
visitgrp varchar2(2000);
newprg varchar2(2000);
v_group_id number;
currentruleid number;
newvisitgrp varchar2(2000);
v_rule_id number;
v_hardcodeadd number;
v_hardcoderem number;
BEGIN  
   open v_member for select   --does the row already exist in group_member table 
		  rule_id,
          hard_code_add_flag, 
		  hard_code_remove_flag           
		from
		  group_member
	     where
		  group_id = groupid and
          hard_code_add_flag = hardcode and
		  member_group_id = memberid and 
		  hard_code_remove_flag != 1 and
		  to_char(RULE_ID) = PARENT_RULE_GROUP;
   fetch v_member into v_rule_id,v_hardcodeadd,v_hardcoderem;
   if (v_member%rowcount= 1) then  --if yes, then make hard_code add to 1 if hardcode parameter is 1
	  if (v_hardcodeadd = 0 and v_hardcoderem=0) then -- and hardcode=1
             UPDATE
                  group_member
                SET 
				     hard_code_add_flag = hardcode, 
				     LAST_MODIFIED_DATE =  sysdate,
		             LAST_MODIFIED_BY = 5772
	             WHERE
				   rule_id = v_rule_id;
             commit;      
       end if;
       insert into rodmess values ('HardAdd: ' || to_char(v_hardcodeadd) ||'HardRem: ' || to_char(v_hardcoderem) || ' Rule: ' || to_char(v_rule_id) || ' Mem: ' || to_char(memberid) || ' Grp: ' || to_char(groupid));
       commit;
       currentruleid := v_rule_id;
   else  --insert new relation
       open v_member for select 
		        rule_id_seq.nextval as newseq
		      from 
		        dual;
       fetch v_member into v_rule_id;   
 	    currentruleid := v_rule_id;     
 	         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
	                (
					 v_rule_id,
					 memberid,    
					 groupid,
					 hardcode,
                     0,
					 to_char(v_rule_id),
					 v_rule_id,
                     0,
                     1,
                     0,
                     0,
                     0,
                     0,
                     0,
                     'CNAV',                     
   		             sysdate,
		             5772,
		             sysdate,
		             5772
		            );
                    commit;
         insert into rodmess values (' Ins: ' || to_char(v_rule_id) || ' Mem: ' || to_char(memberid) || ' Grp: ' || to_char(groupid) || ' Hard: ' || to_char(hardcode));
         commit;
  end if;
  close v_member;
    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 ('Insert Relation CHOKED: ' || to_char(groupid));
        commit; 
        return;    
   end if;        
     open v_member for select  --find all groups that current group is hardcoded out of 
		  group_id 
		from
		  group_member
	     where
		  member_group_id = groupid and
		  hard_code_remove_flag = 1;
     nolist := nolistarg;  --put hardcode out groups in a list
     loop
         fetch v_member into v_group_id;
         exit when v_member%NOTFOUND;
         nolist := nolist || to_char(v_group_id) || ',';
     end loop; 
     close v_member;
     open v_member for select  --get all groups that this group is member 
		  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  --create arrays of rule id and group id of which this group is a member
         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; 
     close v_member;     
 thecount:= thecount- 1;
 visitgrp:='';
 if ( thecount>0 ) then 
   FOR i in 1 .. thecount LOOP
    if memberid != nextgroupid(i) and
          ListFind(to_char(nextgroupid(i)),visitgrp,',') = 0 and     
          ListFind(to_char(nextruleid(i)),to_char(currentruleid),'.') = 0 then      
       visitgrp := visitgrp || ',';    
       expandup (memberid,nextgroupid(i),to_char(currentruleid),to_char(nextruleid(i)),nolist,visitgrp);
     else
       insert into rodmess values ('Stop: ' || to_char(memberid) || to_char(nextgroupid(i)) || newprg || to_char(nextruleid(i)) || nolist || newvisitgrp);
       commit;
    end if;
  end loop;
end if;
end insert_relation; 
/
show errors;