create or replace procedure expand_members
AS
type t_entity is ref cursor;
c_member t_entity;
c_singlegroup t_entity;
v_memberid number;
v_newid number;
v_membergroupid number;
v_displayname varchar2(50);
BEGIN  
delete from rodmess;
commit;
update grouping 
  set last_modified_date = sysdate
  where entity_id in 
    (select cnav_member_id from member);
commit;
             insert into grouping (
                  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 ,
                   member.display_name, --group_name,  
                   'E',      --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,
                   member.cnav_member_id,    --entity_id,
                   'MEMBER',--entity_type_code,
                   null,        --alias_group_prefix,
                   null,        --alias_group_suffix,
                   null,     --parent_group_id,
                   member.cnav_member_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,
                   sysdate,     --last_modified_date,
                   0,           --marked_for_deletion,
                   0,           --process_change_flag,
                   0,           --process_insert_flag,
                   5772,        --source_institution_id,
                   'CNAV',       --data_source_key 
                   1             --generation_sequence                                  
               from member,grouping
               where grouping.entity_id(+)=member.cnav_member_id
               and grouping.entity_id is null;
               commit;
END expand_members;
/
show errors;