create or replace procedure expand_sgm    ----fake generation of acad department tree
(                                              ----rod tosten rtosten@cnavsystems.com
 p_groupname IN varchar2 default null, ----parameter if different group name is desired
 p_recuridlist IN long default null,
 p_groupid IN number default null,
 p_generationlevel IN number default null
) 
AS
type t_entity is ref cursor;
c_dept t_entity;
v_psgmkey varchar2(2000);
v_moddate date;
v_genseq number;
v_gensuff varchar2(2000);
v_parseq number;
v_pargrp number;
v_membergroupid number;
v_groupid number;
v_groupname varchar2(2000);
v_membersql varchar2(2000);
v_parentsql varchar2(2000);
v_updatesql varchar2(2000);
v_creategrpsql varchar2(2000);
v_command varchar2(2000);
v_cursor number;
v_rows number;
v_status number;
v_temprecurlist long;
v_tempentityid number;
v_tempnextgeneration number;
v_generationlevel number;
cursor c_nextgeneration is 
    select entity_id 
    from grouping
    where parent_group_id=v_groupid
        and generation_level=p_generationlevel
        and generation_sequence=1
        and live_flag=1;
cursor c_remgroupmember is 
    select member_group_id,group_id 
    from action_group_member
    where remove_flag=1
    order by generation_sequence,generation_level;
cursor c_addgroupmember is 
    select member_group_id,group_id 
    from action_group_member
    where add_flag=1
    order by generation_sequence,generation_level;
BEGIN 
 select sysdate into v_moddate from dual;   -- use the same mod date for all groups
v_generationlevel:=p_generationlevel;
if (v_generationlevel = 1) then
  delete from action_group_member;
  commit; 
  delete from new_group_member;
  commit; 
  delete from old_group_member;
  commit; 
  delete from rodmess;
  commit;
end if;
           insert into rodmess values ('BEG: '||to_char(v_moddate,'MM-DD-YYYY:HH24:MM:SS'));
          commit; 
  if (p_groupname is null and p_groupid is null and p_recuridlist is null) then
        insert into rodmess values ('Error: No valid parameters');
        commit; 
        return;    
  end if; 
  if (p_groupname is not null) then 
     open c_dept for select  --find the parent group to generate all groups
		  group_id 
		from
		  grouping
	     where
		  group_name = p_groupname and
          live_flag=1 and
		  --group_type_code = 'PSGM' and  ---PSGM parent system generated multis
		  (marked_for_deletion is null or marked_for_deletion !=1);
     fetch c_dept into v_groupid; --- get group_id for PSGM group
     if (c_dept%rowcount = 0) then -- double check group is there
        insert into rodmess values ('Error: No group' || v_psgmkey);
        commit; 
        return;    
     end if; -----keep processing
     v_psgmkey:=to_char(v_groupid);
  end if;
  if (p_groupid is not null) then
     open c_dept for select  --find the parent group to generate all groups
		  group_name 
		from
		  grouping
	     where
		  group_id = p_groupid and
          live_flag=1 and
		  (marked_for_deletion is null or marked_for_deletion !=1);
     fetch c_dept into v_groupname; --- get group_id for PSGM group
     if (c_dept%rowcount = 0) then -- double check group is there
        insert into rodmess values ('Error: No group' || to_char(p_groupid));
        commit; 
        return;    
     end if; -----keep processing
     v_groupid:=p_groupid;
     v_psgmkey:=to_char(p_groupid);
  end if;
  if (p_recuridlist is not null) then
    v_psgmkey := p_recuridlist;
  end if;   
   open c_dept for select  --get all group types and info for each group category this parent group will generate
		  generation_sequence,  -- what generation
          suffix,  -- suffix for group name
          parent_sequence, -- the parent generation 
          parent_group_id, -- the parent group
          membersql, -- how to get members for generation groups
          updatesql, -- how to update generation groups
          creategrpsql,  -- how to create generation groups 
          parentsql -- the sql code to select this groups' parent
		from
		  psgm_suffix
	     where
		  group_id=v_groupid order by generation_sequence;
 loop  --- for each generation sequence         
   fetch c_dept into v_genseq,v_gensuff,v_parseq,
        v_pargrp,v_membersql,v_updatesql,v_creategrpsql,v_parentsql;
   exit when c_dept%NOTFOUND;
        if (v_generationlevel=1) then
                     update 
                       grouping 
                     set 
                       live_flag=2
                     where 
                         parent_group_id=v_groupid and
                         generation_sequence=v_genseq and
                         generation_level!=0;   
                  commit;                                    
        end if;
        if (v_updatesql is not null) then --touch existing children groups  
                 v_command:= 'update grouping set generation_level=:v_generationlevel,self_add_flag=5,live_flag=1, last_modified_date= :v_moddate';
                 v_command:= v_command || ' where parent_group_id=:v_groupid ';
                 v_command:= v_command || ' and generation_sequence=:v_genseq ';
                 v_command:= v_command || ' and entity_id in ( ';
                 v_command:= v_command || v_updatesql || ' )';
                  v_cursor := DBMS_SQL.OPEN_CURSOR;
                 insert into rodmess values ('just about sql: ' || v_command);
                 commit; 
                  DBMS_SQL.PARSE(v_cursor,v_command,DBMS_SQL.V7);
                  if (instr(v_command,':v_moddate')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_moddate',v_moddate);
                  end if;   
                  if (instr(v_command,':v_genseq')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_genseq',v_genseq);
                  end if;   
                  if (instr(v_command,':v_groupid')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_groupid',v_groupid);
                  end if;   
                  if (instr(v_command,':v_psgmkey')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_psgmkey',v_psgmkey);  
                  end if;
                  if (instr(v_command,':v_generationlevel')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_generationlevel',v_generationlevel);  
                  end if;
                  if (instr(v_command,':v_gensuff')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_gensuff',v_gensuff);  
                  end if;
                  v_rows := DBMS_SQL.EXECUTE(v_cursor); 
                  DBMS_SQL.CLOSE_CURSOR(v_cursor); 
        end if;                             
        if (v_creategrpsql is not null) then ---- create new children groups  
          v_command:= 'insert into grouping (';  
           v_command:= v_command || ' group_id,group_name,group_type_code,access_group_flag,live_flag,';
           v_command:= v_command || ' choke_point_flag,private_name_flag,private_membership_flag,private_resources_flag,';
           v_command:= v_command || ' hidden_name_flag,hidden_membership_flag,hidden_resources_flag,';
           v_command:= v_command || 'group_editable_flag,group_deletable_flag,auto_deletion_flag,';
           v_command:= v_command || 'self_add_flag,generate_alias_group_flag,resources_to_children_flag,resources_from_parent_flag,';
           v_command:= v_command || 'entity_id,entity_type_code,alias_group_prefix,alias_group_suffix,parent_group_id,';
           v_command:= v_command || 'owner_entity_id,viewer_entity_type_code,owner_entity_type_code,viewer_entity_id,';
           v_command:= v_command || 'effective_date,expiration_date,last_gen_date,created_by,created_date,last_modified_by,';
           v_command:= v_command || 'last_modified_date,marked_for_deletion,process_change_flag,process_insert_flag,';
           v_command:= v_command || 'source_institution_id,data_source_key,generation_sequence,generation_level) ';
           v_command:= v_command || v_creategrpsql;
          insert into rodmess values (p_recuridlist);
          commit; 
                  v_cursor := DBMS_SQL.OPEN_CURSOR;
                  DBMS_SQL.PARSE(v_cursor,v_command,DBMS_SQL.V7);
                  if (instr(v_command,':v_moddate')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_moddate',v_moddate);
                  end if;   
                  if (instr(v_command,':v_genseq')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_genseq',v_genseq);
                  end if;   
                  if (instr(v_command,':v_groupid')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_groupid',v_groupid);
                  end if;   
                  if (instr(v_command,':v_psgmkey')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_psgmkey',v_psgmkey);  
                  end if;
                  if (instr(v_command,':v_generationlevel')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_generationlevel',v_generationlevel);  
                  end if;
                  if (instr(v_command,':v_gensuff')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_gensuff',v_gensuff);  
                  end if;
                  v_rows := DBMS_SQL.EXECUTE(v_cursor); 
                  DBMS_SQL.CLOSE_CURSOR(v_cursor);         
        end if;
        if (v_parseq is not null) then  -- if parent group is a earlier generation       
           insert into rodmess values ('Parent is sequence' || to_char(v_parseq));
           commit; 
           insert into old_group_member (  ---find all members in this child group
                  member_group_id,
                  group_id
                  )
               select gm.member_group_id,gm.group_id
                from group_member gm, grouping gc
                where 
                 gc.parent_group_id = v_groupid
                 and gc.generation_level=v_generationlevel  
                 and gc.generation_sequence = v_genseq
                 and gc.group_id=gm.group_id
                 and gm.rule_id=gm.parent_rule_id;
            insert into new_group_member (   --- insert this generation of groups into their parents
                  member_group_id,
                  group_id,
                  generation_sequence
                  )
              select gc.group_id,gp.group_id,v_genseq
               from grouping gp, grouping gc
               where 
                 gp.parent_group_id = v_groupid  
                 and gp.generation_sequence = v_parseq
                 and gc.parent_group_id = v_groupid  
                 and gc.generation_sequence = v_genseq
                 and gc.entity_id = gp.entity_id
                 and gc.live_flag=1
                 and gp.live_flag=1;
         end if;      
         if (v_membersql is not null) then   
                 v_membersql:= 'insert into new_group_member (member_group_id,group_id,generation_sequence,generation_level)'||v_membersql;
                  v_cursor := DBMS_SQL.OPEN_CURSOR;
                  DBMS_SQL.PARSE(v_cursor,v_membersql,DBMS_SQL.V7);
                  if (instr(v_membersql,':v_moddate')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_moddate',v_moddate);
                  end if;   
                  if (instr(v_membersql,':v_genseq')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_genseq',v_genseq);
                  end if;   
                  if (instr(v_membersql,':v_groupid')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_groupid',v_groupid);
                  end if;   
                  if (instr(v_membersql,':v_psgmkey')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_psgmkey',v_psgmkey);  
                  end if;
                  if (instr(v_membersql,':v_generationlevel')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_generationlevel',v_generationlevel);  
                  end if;
                  if (instr(v_membersql,':v_gensuff')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_gensuff',v_gensuff);  
                  end if;
                  v_rows := DBMS_SQL.EXECUTE(v_cursor); 
                  DBMS_SQL.CLOSE_CURSOR(v_cursor);
                  COMMIT;            
        end if;                         
         if (v_parentsql is not null) then   
           insert into rodmess values ('Parent is sql' || v_parentsql);
           commit; 
           insert into old_group_member (  ---find all members in this child group
                  member_group_id,
                  group_id
                  )
               select gm.member_group_id,gm.group_id
                from group_member gm, grouping gc
                where 
                 gc.parent_group_id = v_groupid
                 and gc.generation_level=v_generationlevel  
                 and gc.generation_sequence = v_genseq
                 and gc.group_id=gm.group_id
                 and gm.rule_id=gm.parent_rule_id;
                 v_parentsql:= 'insert into new_group_member (member_group_id,group_id,generation_sequence,generation_level)'||v_parentsql;
                  v_cursor := DBMS_SQL.OPEN_CURSOR;
                  DBMS_SQL.PARSE(v_cursor,v_parentsql,DBMS_SQL.V7);
                  if (instr(v_parentsql,':v_moddate')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_moddate',v_moddate);
                  end if;   
                  if (instr(v_parentsql,':v_genseq')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_genseq',v_genseq);
                  end if;   
                  if (instr(v_parentsql,':v_groupid')!=0) then
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_groupid',v_groupid);
                  end if;   
                  if (instr(v_parentsql,':v_psgmkey')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_psgmkey',v_psgmkey);  
                  end if;
                  if (instr(v_parentsql,':v_generationlevel')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_generationlevel',v_generationlevel);  
                  end if;
                  if (instr(v_parentsql,':v_gensuff')!=0) then                  
                     DBMS_SQL.BIND_VARIABLE(v_cursor,':v_gensuff',v_gensuff);  
                  end if;
                  v_rows := DBMS_SQL.EXECUTE(v_cursor); 
                  DBMS_SQL.CLOSE_CURSOR(v_cursor);
                  COMMIT;            
        end if;                         
        if (v_pargrp is not null) then  -- if parent group is a earlier generation       
            insert into rodmess values ('Parent is group' || to_char(v_groupid));
            commit; 
            insert into old_group_member ( -- get all old(existing) relations
                  member_group_id,
                  group_id
                  )
            select gm.member_group_id,gm.group_id
               from group_member gm, grouping gc
               where 
                 gc.parent_group_id = v_groupid  
                 and gc.generation_sequence = v_genseq
                 and gc.generation_level = v_generationlevel
                 and  gc.group_id=gm.group_id
                 and gm.rule_id=gm.parent_rule_id;
             insert into new_group_member (
                  member_group_id,
                  group_id,
                  generation_sequence
                  )
            select gc.group_id,v_pargrp,v_genseq
               from grouping gc
               where 
                 gc.parent_group_id = v_groupid  
                 and gc.generation_sequence = v_genseq
                 and gc.live_flag=1;
       end if;                            
 end loop; 
 close c_dept;
 if (p_recuridlist is not null) then
   v_temprecurlist := null;
   open c_nextgeneration;
   loop 
     fetch c_nextgeneration into v_tempentityid;
     exit when c_nextgeneration%NOTFOUND;
     if (v_temprecurlist is null) then
        v_temprecurlist := to_char(v_tempentityid);
       else
        v_temprecurlist := v_temprecurlist || ',' || to_char(v_tempentityid);
     end if;
   end loop;
   close c_nextgeneration;
   if (v_temprecurlist is not null) then
      v_tempnextgeneration := v_generationlevel+1;
           insert into rodmess values ('Recur Call: ' || p_groupname ||'~'|| v_temprecurlist ||'~' || to_char(v_tempnextgeneration)||'~');
           commit; 
     expand_sgm(p_groupname,v_temprecurlist,null,v_tempnextgeneration);
   end if;
   if (p_generationlevel != 1) then
     return;
   end if;
 end if;
                   update 
                       grouping 
                     set 
                       live_flag=0
                     where 
                         live_flag=2;   
                  commit;                                    
 insert into old_group_member ( -- get old relationships
                  member_group_id,
                  group_id
                  )
            select gm.member_group_id,gm.group_id
               from group_member gm, grouping gc
               where (gc.group_id = v_groupid or gc.parent_group_id=v_groupid)
                 and gm.group_id=gc.group_id  
                 and gm.rule_id=gm.parent_rule_id;
 insert into action_group_member --get realtionships that are in new but not old...add these to cache
      (
       member_group_id,
       group_id,
       add_flag,
       remove_flag,
       generation_sequence,
       generation_level
      )                       
    select  ngm.member_group_id,ngm.group_id,1,0,ngm.generation_sequence,ngm.generation_level
       from new_group_member ngm,old_group_member ogm
              where 
                 ogm.member_group_id(+) = ngm.member_group_id 
                 and ogm.member_group_id is null
                 and ogm.group_id(+) = ngm.group_id 
                 and ogm.group_id is null;
 commit;
  insert into action_group_member  --get relationship that are in old but not new...these delete
      (
       member_group_id,
       group_id,
       add_flag,
       remove_flag,
       generation_sequence,
       generation_level
      )                       
    select  ogm.member_group_id,ogm.group_id,0,1,0,0
       from new_group_member ngm,old_group_member ogm
              where 
                 ogm.member_group_id = ngm.member_group_id(+) 
                 and ngm.member_group_id is null
                 and ogm.group_id = ngm.group_id(+) 
                 and ngm.group_id is null;
 commit;
open c_addgroupmember;   -- add new relationships
 loop 
   fetch c_addgroupmember into v_membergroupid,v_groupid;
   exit when c_addgroupmember%NOTFOUND;              
   insert_relation(v_membergroupid,v_groupid,0); --put member into group
 end loop;
 close c_addgroupmember;
open c_remgroupmember;   -- remove old ones
 loop 
   fetch c_remgroupmember into v_membergroupid,v_groupid;
   exit when c_remgroupmember%NOTFOUND;              
   cnav_remove_relation(v_status,v_membergroupid,v_groupid); --remove member from group
 end loop;
 close c_remgroupmember;
--insert_relation(memberugaid,groupugaid,1);
--insert_member(memberugaid,memberugaid);
          insert into rodmess values ('END: '||to_char(v_moddate,'MM-DD-YYYY:HH24:MM:SS'));
          commit; 
END expand_sgm;
/
show errors;