1 PACKAGE BODY igw_security AS
2 --$Header: igwcoseb.pls 115.11 2002/04/23 23:09:57 pkm ship $
3
4 -----------------------------------------------------------------------------
5
6 FUNCTION allow_create(p_function_name IN varchar2,
7 p_proposal_id IN number,
8 p_user_id IN number)
9 RETURN varchar2 IS
10 BEGIN
11
12 RETURN allow_modify(p_function_name,p_proposal_id,p_user_id);
13
14 END allow_create;
15
16 -----------------------------------------------------------------------------
17
18 FUNCTION allow_modify( p_function_name IN varchar2,
19 p_proposal_id IN number,
20 p_user_id IN number)
21 RETURN varchar2 IS
22
23 CURSOR cur_status IS
24 SELECT proposal_status
25 FROM igw_proposals_all
26 WHERE proposal_id = p_proposal_id;
27
28 l_proposal_status varchar2(1);
29
30 l_dummy varchar2(1);
31
32 BEGIN
33
34 -- return 'Y' if current user is a proposal super user
35
36 IF fnd_profile.value_wnps('IGW_PROPOSAL_SUPER_USER') = 'Y' THEN
37
38 RETURN 'Y';
39
40 END IF;
41
42 OPEN cur_status;
43 FETCH cur_status INTO l_proposal_status;
44 CLOSE cur_status;
45
46 IF p_function_name = 'GENERAL' THEN
47
48 SELECT distinct 'X'
49 INTO l_dummy
50 FROM igw_prop_user_roles ur,
51 igw_role_rights rr
52 WHERE ur.proposal_id = p_proposal_id
53 AND ur.user_id = p_user_id
54 AND rr.role_id = ur.role_id
55 AND rr.right_code in ('MODIFY_GENERAL');
56
57 IF l_proposal_status IN ('P','R') THEN
58
59 RETURN 'Y';
60
61 END IF;
62
63 ELSIF p_function_name = 'BUDGET' THEN
64
65 SELECT distinct 'X'
66 INTO l_dummy
67 FROM igw_prop_user_roles ur,
68 igw_role_rights rr
69 WHERE ur.proposal_id = p_proposal_id
70 AND ur.user_id = p_user_id
71 AND rr.role_id = ur.role_id
72 AND rr.right_code in ('MODIFY_BUDGET');
73
74 IF l_proposal_status IN ('P','R') THEN
75
76 RETURN 'Y';
77
78 END IF;
79
80 ELSIF p_function_name = 'NARRATIVE' THEN
81
82 SELECT distinct 'X'
83 INTO l_dummy
84 FROM igw_prop_user_roles ur,
85 igw_role_rights rr
86 WHERE ur.proposal_id = p_proposal_id
87 AND ur.user_id = p_user_id
88 AND rr.role_id = ur.role_id
89 AND rr.right_code in ('MODIFY_NARRATIVE');
90
91 IF l_proposal_status IN ('P','R','I') THEN
92
93 RETURN 'Y';
94
95 END IF;
96
97 ELSIF p_function_name = 'CHECKLIST' THEN
98
99 SELECT distinct 'X'
100 INTO l_dummy
101 FROM igw_prop_user_roles ur,
102 igw_role_rights rr
103 WHERE ur.proposal_id = p_proposal_id
104 AND ur.user_id = p_user_id
105 AND rr.role_id = ur.role_id
106 AND rr.right_code in ('MODIFY_CHECKLIST');
107
108 IF l_proposal_status IN ('P','R','I') THEN
109
110 RETURN 'Y';
111
112 END IF;
113
114 ELSIF p_function_name = 'APPROVAL' THEN
115
116 SELECT distinct 'X'
117 INTO l_dummy
118 FROM igw_prop_user_roles ur,
119 igw_role_rights rr
120 WHERE ur.proposal_id = p_proposal_id
121 AND ur.user_id = p_user_id
122 AND rr.role_id = ur.role_id
123 AND rr.right_code in ('MODIFY_APPROVAL');
124
125 IF l_proposal_status IN ('P','R') THEN
126
127 RETURN 'Y';
128
129 END IF;
130
131 ELSIF p_function_name = 'SPONSOR_ACTION' THEN
132
133 SELECT distinct 'X'
134 INTO l_dummy
135 FROM igw_prop_user_roles ur,
136 igw_role_rights rr
137 WHERE ur.proposal_id = p_proposal_id
138 AND ur.user_id = p_user_id
139 AND rr.role_id = ur.role_id
140 AND rr.right_code in ('MODIFY_SPONSOR_ACTION');
141
142 IF l_proposal_status IN ('A') THEN
143
144 RETURN 'Y';
145
146 END IF;
147
148 ELSIF p_function_name = 'AWARD' THEN
149
150 SELECT distinct 'X'
151 INTO l_dummy
152 FROM igw_prop_user_roles ur,
153 igw_role_rights rr
154 WHERE ur.proposal_id = p_proposal_id
155 AND ur.user_id = p_user_id
156 AND rr.role_id = ur.role_id
157 AND rr.right_code in ('MODIFY_AWARD');
158
159 IF l_proposal_status IN ('A') THEN
160
161 RETURN 'Y';
162
163 END IF;
164
165 ELSIF p_function_name = 'PRINT_PROPOSAL' THEN
166
167 SELECT distinct 'X'
168 INTO l_dummy
169 FROM igw_prop_user_roles ur,
170 igw_role_rights rr
171 WHERE ur.proposal_id = p_proposal_id
172 AND ur.user_id = p_user_id
173 AND rr.role_id = ur.role_id
174 AND rr.right_code in ('PRINT_PROPOSAL');
175
176 RETURN 'Y';
177
178 END IF;
179
180 RETURN 'N';
181
182 EXCEPTION
183
184 WHEN no_data_found THEN
185 RETURN 'N';
186
187 WHEN others THEN
188 RETURN 'E';
189
190 END allow_modify;
191
192 -----------------------------------------------------------------------------
193
194 function allow_query( p_function_name IN VARCHAR2,
195 p_proposal_id IN NUMBER,
196 p_user_id IN NUMBER)
197 return varchar2 is
198 l_dummy varchar2(1);
199 begin
200
201 -- return 'Y' if current user is a proposal super user
202
203 IF fnd_profile.value_wnps('IGW_PROPOSAL_SUPER_USER') = 'Y' THEN
204
205 RETURN 'Y';
206
207 END IF;
208
209 -- The assumption is that the user who has create or modify rights
210 -- gets the query right by default
211
212 if p_function_name = 'PROPOSAL' then
213
214 select distinct 'X'
215 into l_dummy
216 from igw_prop_user_roles ppur,
217 igw_role_rights prr
218 where ppur.role_id = prr.role_id
219 and ppur.proposal_id = p_proposal_id
220 and ppur.user_id = p_user_id
221 and prr.right_code in
222 ('VIEW_PROPOSAL','MODIFY_GENERAL','MODIFY_BUDGET',
223 'MODIFY_NARRATIVE','MODIFY_CHECKLIST','MODIFY_APPROVAL',
224 'MODIFY_SPONSOR_ACTION','MODIFY_AWARD');
225
226 end if;
227
228 return 'Y';
229
230 exception
231
232 when no_data_found then
233 return 'N';
234
235 when others then
236 return 'E';
237
238 end allow_query;
239
240
241
242
243 ---------- function gms_enabled ----------------------------------
244 -----------------------------------------------------
245 -- Function to check the implementation status of OGM
246 -- for an Organization
247 -- Returns
248 -- Y if OGM is implemented for the
249 -- Login Responsibility .
250 -- N if OGM is not implemented for the
251 -- Login Responsibility .
252 ---------------------------------
253 function gms_enabled return varchar2 is
254 begin
255
256 if gms_install.enabled then
257 return 'Y';
258 end if;
259
260 RETURN 'N';
261
262 end gms_enabled ;
263
264
265
266 end igw_security;
[Home] [Help]