create or replace procedure expand_dept_sgm    ----fake generation of acad department tree
(                                              ----rod tosten rtosten@cnavsystems.com
 p_groupname IN varchar2 default 'Departments' ----parameter if different group name is desired
) 
AS
type t_entity is ref cursor;
c_dept t_entity;
v_psgmid number;
v_moddate date;
v_genseq number;
v_gensuff varchar2(2000);
v_parseq number;
v_pargrp number;
v_membergroupid number;
v_groupid number;
v_membersql varchar2(2000);
v_cursor number;
v_rows number;
v_status number;
cursor c_remgroupmember is 
    select member_group_id,group_id 
    from action_group_member
    where remove_flag=1
    order by generation_sequence;
cursor c_addgroupmember is 
    select member_group_id,group_id 
    from action_group_member
    where add_flag=1
    order by generation_sequence;
BEGIN 
delete from action_group_member;
commit; 
delete from new_group_member;
commit; 
delete from old_group_member;
commit; 
delete from rodmess;
commit;
   open c_dept for select  --find the parent group to generate all groups
		  group_id 
		from
		  grouping
	     where
		  group_name = p_groupname 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_psgmid; --- 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(v_psgmid));
        commit; 
        return;    
   end if; -----keep processing
   v_moddate := sysdate;   -- use the same mod date for all groups
   open c_dept for select  --get all group types and info for each group category this parent group will generate
		  generation_sequence,suffix,parent_sequence,parent_group_id,membersql 
		from
		  psgm_suffix
	     where
		  group_id = v_psgmid;
loop           
   fetch c_dept into v_genseq,v_gensuff,v_parseq,v_pargrp,v_membersql;
   exit when c_dept%NOTFOUND;
   update grouping           ---- mark existing child groups
       set last_modified_date = sysdate
       where parent_group_id = v_psgmid  
             and generation_sequence = v_genseq
             and entity_id in 
                      (select department_id from department);
    commit;
        insert into grouping (   ---- create new children groups
                  group_id ,
                  group_name,  
                  group_type_code,
                  access_group_flag,
                  live_flag,
                  choke_point_flag,
                  private_name_flag,
                  private_membership_flag,
                  private_resources_flag,
                  hidden_name_flag,
                  hidden_membership_flag,
                  hidden_resources_flag,
                  group_editable_flag,
                  group_deletable_flag,
                  auto_deletion_flag,
                  self_add_flag,
                  generate_alias_group_flag,
                  resources_to_children_flag,
                  resources_from_parent_flag,
                  entity_id,
                  entity_type_code,
                  alias_group_prefix,
                  alias_group_suffix,
                  parent_group_id,
                  owner_entity_id,
                  viewer_entity_type_code,
                  owner_entity_type_code,
                  viewer_entity_id,
                  effective_date,
                  expiration_date,
                  last_gen_date,
                  created_by,
                  created_date,
                  last_modified_by,
                  last_modified_date,
                  marked_for_deletion,
                  process_change_flag,
                  process_insert_flag,
                  source_institution_id,
                  data_source_key, 
                  generation_sequence                                  
                  )
            select 
                   group_id_seq.nextval,     --group_id ,
                   m.display_name||' '||d.department_desc||' '||v_gensuff, --group_name,  
                   'CSGM',      --group_type_code,
                   1,           --access_group_flag,
                   1,           --live_flag,
                   0,           --choke_point_flag,
                   0,           --private_name_flag,
                   0,           --private_membership_flag,
                   0,           --private_resources_flag,
                   0,           --hidden_name_flag,
                   0,           --hidden_membership_flag,
                   0,           --hidden_resources_flag,
                   0,           --group_editable_flag,
                   0,           --group_deletable_flag,
                   0,           --auto_deletion_flag,
                   0,           --self_add_flag,
                   0,           --generate_alias_group_flag,
                   0,           --resources_to_children_flag,
                   0,           --resources_from_parent_flag,
                   d.department_id,    --entity_id,
                   'DEPARTMENT',--entity_type_code,
                   null,        --alias_group_prefix,
                   null,        --alias_group_suffix,
                   v_psgmid,     --parent_group_id,
                   d.department_id,           --owner_entity_id,
                   '1',         --viewer_entity_type_code,
                   '1',         --owner_entity_type_code,
                   0,           --viewer_entity_id,
                   sysdate,     --effective_date,
                   sysdate,     --expiration_date,
                   sysdate,     --last_gen_date,
                   5772,        --created_by,
                   sysdate,     --created_date,
                   5772,        --last_modified_by,
                   v_moddate,     --last_modified_date,
                   0,           --marked_for_deletion,
                   0,           --process_change_flag,
                   0,           --process_insert_flag,
                   5772,        --source_institution_id,
                   'CNAV',       --data_source_key 
                   v_genseq             --generation_sequence                                  
               from member m,department d,grouping g
               where 
 	             m.member_type_code = 'INSTITUTE'
                 and d.institution_id = m.cnav_member_id  
                 and g.parent_group_id(+) = v_psgmid  
                 and g.entity_id(+)= d.department_id
                 and g.entity_id is null
                 and g.generation_sequence(+) = v_genseq;
        if (v_parseq is not null) then ---find all members in this child group        
            insert into old_group_member (
                  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_psgmid  
                 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 (
                  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_psgmid  
                 and gp.generation_sequence = v_parseq
                 and gc.parent_group_id = v_psgmid  
                 and gc.generation_sequence = v_genseq
                 and gc.entity_id = gp.entity_id;
             if (v_membersql is not null) then   
                 v_membersql:= 'insert into new_group_member (member_group_id,group_id,generation_sequence)'||v_membersql;
                  v_cursor := DBMS_SQL.OPEN_CURSOR;
                  DBMS_SQL.PARSE(v_cursor,v_membersql,DBMS_SQL.V7);
                  DBMS_SQL.BIND_VARIABLE(v_cursor,':v_genseq',v_genseq);
                  DBMS_SQL.BIND_VARIABLE(v_cursor,':v_psgmid',v_psgmid);  
                  v_rows := DBMS_SQL.EXECUTE(v_cursor); 
                  DBMS_SQL.CLOSE_CURSOR(v_cursor);
                  COMMIT;            
             end if;                     
           else
            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_psgmid  
                 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 (
                  member_group_id,
                  group_id,
                  generation_sequence
                  )
            select gc.group_id,v_pargrp,v_genseq
               from grouping gc
               where 
                 gc.parent_group_id = v_psgmid  
                 and gc.generation_sequence = v_genseq;
        end if;                            
 end loop; 
 insert into old_group_member ( -- get old relationships
                  member_group_id,
                  group_id
                  )
            select gm.member_group_id,v_psgmid
               from group_member gm
               where gm.group_id = v_psgmid  
                 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
      )                       
    select  ngm.member_group_id,ngm.group_id,1,0,ngm.generation_sequence
       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
      )                       
    select  ogm.member_group_id,ogm.group_id,0,1,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;
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;
--insert_relation(memberugaid,groupugaid,1);
--insert_member(memberugaid,memberugaid);
END expand_dept_sgm;
/
show errors;