create or replace procedure expand_dept_sgm ( p_groupname IN varchar2 default 'Departments' ) AS type t_entity is ref cursor; c_dept t_entity; c_institute t_entity; c_group t_entity; c_singlegroup t_entity; c_newid t_entity; v_newid number; v_singlegroupid number; v_facultypartgroupid number; v_facultyfullgroupid number; v_facultygroupid number; v_studentgroupid number; v_studentfirstgroupid number; v_studentsophgroupid number; v_studentjrgroupid number; v_studentsrgroupid number; v_psgmid number; v_instid number; v_instname varchar2(50); v_deptid number; v_deptdesc varchar2(50); v_moddate date; BEGIN delete from rodmess; commit; open c_dept for select --find all groups this group is in group_id from grouping where group_name = p_groupname and group_type_code = 'PSGM' and marked_for_deletion !=1; fetch c_dept into v_psgmid; 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; open c_institute for select --find all institutions in system cnav_member_id, display_name from member where member_type_code = 'INSTITUTE' and (marked_for_deletion is null or marked_for_deletion !=1); insert into rodmess values ('Before institute loop'); commit; loop fetch c_institute into v_instid,v_instname; insert into rodmess values ('For Institute: ' || to_char(v_instid) ||' '|| v_instname); commit; exit when c_institute%NOTFOUND; open c_dept for select --find all departments department_id, department_desc -- in this insitution from department where institution_id = v_instid and (marked_for_deletion is null or marked_for_deletion !=1); loop fetch c_dept into v_deptid,v_deptdesc; --loop through each department in institution insert into rodmess values ('For Department: ' || to_char(v_deptid) ||' '|| v_deptdesc); commit; exit when c_dept%NOTFOUND; v_facultygroupid := null; --start of institute dept faculty group open c_singlegroup for select -- does institute dept faculty group exist group_id from grouping where parent_group_id = v_psgmid and entity_id = v_deptid and generation_sequence = 1; fetch c_singlegroup into v_facultygroupid; insert into rodmess values ('For Dept Group: ' || to_char(v_deptid) ||' '|| to_char(v_psgmid) || ' Seq: 1'||' Found Id: ' || to_char(v_facultygroupid)); commit; if (c_singlegroup%rowcount = 0) then -- does group exist? v_facultygroupid := null; end if; insert_into_grouping ( v_facultygroupid, --group_id , v_instname||' '||v_deptdesc||' '||'Faculty', --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, v_deptid, --entity_id, 'DEPARTMENT',--entity_type_code, null, --alias_group_prefix, null, --alias_group_suffix, v_psgmid, --parent_group_id, 0, --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 ); insert_relation(v_facultygroupid,v_psgmid,0); --put dept group into departments group v_studentgroupid := null; --start of institute dept student group open c_singlegroup for select -- does institute dept student group exist group_id from grouping where parent_group_id = v_psgmid and entity_id = v_deptid and generation_sequence = 2; fetch c_singlegroup into v_studentgroupid; insert into rodmess values ('For Dept Group: ' || to_char(v_deptid) ||' '|| to_char(v_psgmid) || ' Seq: 2'||' Found Id: ' || to_char(v_studentgroupid)); commit; if (c_singlegroup%rowcount = 0) then -- does group exist? v_studentgroupid := null; end if; insert_into_grouping ( v_studentgroupid, --group_id , v_instname||' '||v_deptdesc||' '||'Student', --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, v_deptid, --entity_id, 'DEPARTMENT',--entity_type_code, null, --alias_group_prefix, null, --alias_group_suffix, v_psgmid, --parent_group_id, 0, --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 2 -- generation_sequence ); insert_relation(v_studentgroupid,v_psgmid,0); --put dept group into departments group v_facultyfullgroupid := null; --start of institute dept faculty fulltime group open c_singlegroup for select -- does institute dept faculty group exist group_id from grouping where parent_group_id = v_facultygroupid and entity_id = v_deptid and generation_sequence = 3; fetch c_singlegroup into v_facultyfullgroupid; insert into rodmess values ('For Dept Group: ' || to_char(v_deptid) ||' '|| to_char(v_psgmid) || ' Seq: 3'||' Found Id: ' || to_char(v_facultyfullgroupid)); commit; if (c_singlegroup%rowcount = 0) then -- does group exist? v_facultyfullgroupid := null; end if; insert_into_grouping ( v_facultyfullgroupid, --group_id , v_instname||' '||v_deptdesc||' '||'Faculty Full', --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, v_deptid, --entity_id, 'DEPARTMENT',--entity_type_code, null, --alias_group_prefix, null, --alias_group_suffix, v_facultygroupid, --parent_group_id, 0, --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 3 -- generation_sequence ); insert_relation(v_facultyfullgroupid,v_facultygroupid,0); --put faculty full group into faculty group open c_singlegroup for select -- put all full time employees in group exist grouping.group_id from grouping,employee where employee.department_id=v_deptid and employee.employment_type_code='FULL' and employee.cnav_member_id=grouping.entity_id and employee.job_title_code='IA'; loop fetch c_singlegroup into v_singlegroupid; --loop through each full time employee exit when c_singlegroup%NOTFOUND; insert_relation(v_singlegroupid,v_facultyfullgroupid,0); --put full time faculty into faculty full group end loop; -- end full time employees v_facultypartgroupid := null; --start of institute dept faculty part time group open c_singlegroup for select -- does institute dept faculty part time group exist group_id from grouping where parent_group_id = v_facultygroupid and entity_id = v_deptid and generation_sequence = 4; fetch c_singlegroup into v_facultypartgroupid; insert into rodmess values ('For Dept Group: ' || to_char(v_deptid) ||' '|| to_char(v_psgmid) || ' Seq: 4'||' Found Id: ' || to_char(v_facultypartgroupid)); commit; if (c_singlegroup%rowcount = 0) then -- does group exist? v_facultypartgroupid := null; end if; insert_into_grouping ( v_facultypartgroupid, --group_id , v_instname||' '||v_deptdesc||' '||'Faculty Part', --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, v_deptid, --entity_id, 'DEPARTMENT',--entity_type_code, null, --alias_group_prefix, null, --alias_group_suffix, v_facultygroupid, --parent_group_id, 0, --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 4 -- generation_sequence ); insert_relation(v_facultypartgroupid,v_facultygroupid,0); --put faculty part group into faculty group v_studentfirstgroupid := null; --start of institute dept student first group open c_singlegroup for select -- does institute dept student first group exist group_id from grouping where parent_group_id = v_studentgroupid and entity_id = v_deptid and generation_sequence = 5; fetch c_singlegroup into v_studentfirstgroupid; insert into rodmess values ('For Dept Group: ' || to_char(v_deptid) ||' '|| to_char(v_psgmid) || ' Seq: 5'||' Found Id: ' || to_char(v_studentfirstgroupid)); commit; if (c_singlegroup%rowcount = 0) then -- does group exist? v_studentfirstgroupid := null; end if; insert_into_grouping ( v_studentfirstgroupid, --group_id , v_instname||' '||v_deptdesc||' '||'Student First Year', --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, v_deptid, --entity_id, 'DEPARTMENT',--entity_type_code, null, --alias_group_prefix, null, --alias_group_suffix, v_studentgroupid, --parent_group_id, 0, --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 5 -- generation_sequence ); insert_relation(v_studentfirstgroupid,v_studentgroupid,0); --put student first group into student group v_studentsophgroupid := null; --start of institute dept student soph group open c_singlegroup for select -- does institute dept student soph group exist group_id from grouping where parent_group_id = v_studentgroupid and entity_id = v_deptid and generation_sequence = 6; fetch c_singlegroup into v_studentsophgroupid; insert into rodmess values ('For Dept Group: ' || to_char(v_deptid) ||' '|| to_char(v_psgmid) || ' Seq: 6'||' Found Id: ' || to_char(v_studentsophgroupid)); commit; if (c_singlegroup%rowcount = 0) then -- does group exist? v_studentsophgroupid := null; end if; insert_into_grouping ( v_studentsophgroupid, --group_id , v_instname||' '||v_deptdesc||' '||'Student Sophomore', --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, v_deptid, --entity_id, 'DEPARTMENT',--entity_type_code, null, --alias_group_prefix, null, --alias_group_suffix, v_studentgroupid, --parent_group_id, 0, --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 6 -- generation_sequence ); insert_relation(v_studentsophgroupid,v_studentgroupid,0); --put student soph group into student group v_studentjrgroupid := null; --start of institute dept student jr group open c_singlegroup for select -- does institute dept student jr group exist group_id from grouping where parent_group_id = v_studentgroupid and entity_id = v_deptid and generation_sequence = 7; fetch c_singlegroup into v_studentjrgroupid; insert into rodmess values ('For Dept Group: ' || to_char(v_deptid) ||' '|| to_char(v_psgmid) || ' Seq: 7'||' Found Id: ' || to_char(v_studentjrgroupid)); commit; if (c_singlegroup%rowcount = 0) then -- does group exist? v_studentjrgroupid := null; end if; insert_into_grouping ( v_studentjrgroupid, --group_id , v_instname||' '||v_deptdesc||' '||'Student Junior', --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, v_deptid, --entity_id, 'DEPARTMENT',--entity_type_code, null, --alias_group_prefix, null, --alias_group_suffix, v_studentgroupid, --parent_group_id, 0, --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 7 -- generation_sequence ); insert_relation(v_studentjrgroupid,v_studentgroupid,0); --put student jr group into student group v_studentsrgroupid := null; --start of institute dept student sr group open c_singlegroup for select -- does institute dept student sr group exist group_id from grouping where parent_group_id = v_studentgroupid and entity_id = v_deptid and generation_sequence = 8; fetch c_singlegroup into v_studentsrgroupid; insert into rodmess values ('For Dept Group: ' || to_char(v_deptid) ||' '|| to_char(v_psgmid) || ' Seq: 8'||' Found Id: ' || to_char(v_studentsrgroupid)); commit; if (c_singlegroup%rowcount = 0) then -- does group exist? v_studentsrgroupid := null; end if; insert_into_grouping ( v_studentsrgroupid, --group_id , v_instname||' '||v_deptdesc||' '||'Student Senior', --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, v_deptid, --entity_id, 'DEPARTMENT',--entity_type_code, null, --alias_group_prefix, null, --alias_group_suffix, v_studentgroupid, --parent_group_id, 0, --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 8 -- generation_sequence ); insert_relation(v_studentsrgroupid,v_studentgroupid,0); --put student sr group into student group end loop; -- end department loop end loop; -- end institution loop --insert_relation(memberugaid,groupugaid,1); --insert_member(memberugaid,memberugaid); END expand_dept_sgm; / show errors;