1: procedure user_group_type_module_add ( p_group_type IN varchar,
2: p_module_key IN varchar )
3: IS
4: v_module_pretty_name acs_modules.pretty_name%TYPE;
5: v_section_type varchar(100);
6: v_group_id user_groups.group_id%TYPE;
7: v_section_key varchar(100);
8:
9: -- Prepare the cursor to pull out all the user groups we need to modify
10:
11: CURSOR c_groups_to_update ( v_group_type IN varchar, v_module_key IN varchar ) IS
12: select ug.group_id, uniq_group_module_section_key(v_module_key, ug.group_id)
13: from user_groups ug
14: where ug.group_type=v_group_type
15: and not exists (select 1
16: from content_sections cs
17: where cs.scope='group'
18: and cs.group_id=ug.group_id
19: and cs.module_key=v_module_key)
20: for update;
21:
22: BEGIN
23:
24: BEGIN
25: -- pull out the pretty name and section type from the acs_modules table.
26: -- If we get an exception, then the specified module doesn't exist -
27: -- Return right away as we can't really do anything else!
28: select pretty_name, section_type_from_module_key(module_key) into v_module_pretty_name, v_section_type
29: from acs_modules
30: where module_key=p_module_key;
31: EXCEPTION WHEN OTHERS THEN
32: raise_application_error(-20000, 'Module key ' || p_module_key || ' not found in acs_modules tables');
33: END;
34:
35: -- We take special care here not to remap the same module key twice! This means
36: -- re-running this procedure for the same group_type/module_key will have no
37: -- side-effects
38: insert into user_group_type_modules_map
39: (group_type_module_id, group_type, module_key)
40: select group_type_modules_id_sequence.nextval, p_group_type, p_module_key
41: from dual
42: where not exists (select 1
43: from user_group_type_modules_map
44: where group_type=p_group_type
45: and module_key=p_module_key);
46:
47:
48: -- Now we need to select out all the groups of this group_type which do not already have this
49: -- module installed.
50:
51: open c_groups_to_update (p_group_type, p_module_key);
52:
53: LOOP
54: FETCH c_groups_to_update into v_group_id, v_section_key;
55: EXIT WHEN c_groups_to_update%NOTFOUND;
56:
57: -- associate the specified module key with the user group
58: insert into content_sections
59: (section_id, scope, section_type, requires_registration_p, visibility, group_id,
60: section_key, module_key, section_pretty_name, enabled_p)
61: values
62: (content_section_id_sequence.nextval, 'group', v_section_type, 'f', 'public', v_group_id,
63: v_section_key, p_module_key, v_module_pretty_name, 't');
64:
65: END LOOP;
66:
67: END;
|