1: function download_authorized_p (v_version_id IN integer, v_user_id IN integer)
2: return varchar2
3: IS
4: v_availability download_rules.availability%TYPE;
5: v_group_id downloads.group_id%TYPE;
6: v_return_value varchar(30);
7: BEGIN
8: select availability into v_availability
9: from download_rules
10: where version_id = v_version_id;
11:
12: if v_availability = 'all'
13: then
14: return 'authorized';
15: elsif v_availability = 'group_members' then
16:
17: select group_id into v_group_id
18: from downloads d, download_versions dv
19: where dv.version_id = v_version_id
20: and dv.download_id = d.download_id;
21:
22: select decode(count(*),0,'not_authorized','authorized') into v_return_value
23: from user_group_map
24: where user_id = v_user_id
25: and group_id = v_group_id;
26:
27: return v_return_value;
28: else
29: select decode(count(*),0,'reg_required','authorized') into v_return_value
30: from users
31: where user_id = v_user_id;
32:
33: return v_return_value;
34: end if;
35:
36: END download_authorized_p;
|