1 PACKAGE BODY pa_ci_security_pkg AS
2 /* $Header: PACISECB.pls 120.6.12020000.3 2013/03/19 14:23:02 svmohamm ship $ */
3 TYPE t_cache IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
4 G_proj_auth_tab t_cache;
5 G_ci_type_tab t_cache;
6 G_view_proj_i_tab t_cache;
7 G_view_proj_cr_tab t_cache;
8 G_view_proj_co_tab t_cache;
9
10 G_user_id NUMBER := -999;
11 G_party_id NUMBER := -999;
12 G_resp_id NUMBER := -999;
13 G_project_id NUMBER := -999;
14 G_debug_flag varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
15 G_PKG_NAME varchar2(30) :='PA_CI_SECURITY_PKG';
16
17 FUNCTION check_view_project(
18 p_project_id NUMBER,
19 p_ci_id NUMBER,
20 p_user_id NUMBER DEFAULT fnd_global.user_id,
21 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
22 RETURN VARCHAR2
23 IS
24 l_class_code pa_ci_types_b.ci_type_class_code%TYPE;
25 BEGIN
26 IF G_user_id <> p_user_id THEN
27 G_user_id := p_user_id;
28 G_resp_id := p_resp_id;
29 G_party_id := pa_control_items_utils.getPartyId(p_user_id);
30 G_proj_auth_tab.DELETE;
31 G_view_proj_i_tab.DELETE;
32 G_view_proj_cr_tab.DELETE;
33 G_view_proj_co_tab.DELETE;
34 G_ci_type_tab.DELETE;
35 ELSIF G_resp_id <> p_resp_id THEN
36 G_resp_id := p_resp_id;
37 G_proj_auth_tab.DELETE;
38 G_view_proj_i_tab.DELETE;
39 G_view_proj_cr_tab.DELETE;
40 G_view_proj_co_tab.DELETE;
41 G_ci_type_tab.DELETE;
42 END IF;
43
44 SELECT ci_type_class_code
45 INTO l_class_code
46 FROM pa_ci_types_b cit,
47 pa_control_items ci
48 WHERE ci.ci_id = p_ci_id
49 AND cit.ci_type_id = ci.ci_type_id;
50
51 IF l_class_code = 'ISSUE' THEN
52 IF NOT G_view_proj_i_tab.EXISTS(p_project_id) THEN
53 G_view_proj_i_tab(p_project_id) := pa_security_pvt.check_user_privilege(
54 p_privilege => 'PA_CTRL_ISSUES',
55 p_object_name => 'PA_PROJECTS',
56 p_object_key => p_project_id);
57 END IF;
58
59 RETURN G_view_proj_i_tab(p_project_id);
60
61 ELSIF l_class_code = 'CHANGE_REQUEST' THEN
62 IF NOT G_view_proj_cr_tab.EXISTS(p_project_id) THEN
63 G_view_proj_cr_tab(p_project_id) := pa_security_pvt.check_user_privilege(
64 p_privilege => 'PA_CTRL_CHG_REQS',
65 p_object_name => 'PA_PROJECTS',
66 p_object_key => p_project_id);
67 END IF;
68
69 RETURN G_view_proj_cr_tab(p_project_id);
70
71 ELSIF l_class_code = 'CHANGE_ORDER' THEN
72 IF NOT G_view_proj_co_tab.EXISTS(p_project_id) THEN
73 G_view_proj_co_tab(p_project_id) := pa_security_pvt.check_user_privilege(
74 p_privilege => 'PA_CTRL_CHG_ORDS',
75 p_object_name => 'PA_PROJECTS',
76 p_object_key => p_project_id);
77 END IF;
78
79 RETURN G_view_proj_co_tab(p_project_id);
80 END IF;
81 END check_view_project;
82
83 FUNCTION check_view_project (
84 p_project_id NUMBER,
85 p_ci_id NUMBER,
86 p_ci_type_class_code VARCHAR2,
87 p_user_id NUMBER DEFAULT fnd_global.user_id,
88 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
89 RETURN VARCHAR2
90 IS
91 l_class_code pa_ci_types_b.ci_type_class_code%TYPE;
92 BEGIN
93 IF G_user_id <> p_user_id THEN
94 G_user_id := p_user_id;
95 G_resp_id := p_resp_id;
96 G_party_id := pa_control_items_utils.getPartyId(p_user_id);
97 G_proj_auth_tab.DELETE;
98 G_view_proj_i_tab.DELETE;
99 G_view_proj_cr_tab.DELETE;
100 G_view_proj_co_tab.DELETE;
101 G_ci_type_tab.DELETE;
102 ELSIF G_resp_id <> p_resp_id THEN
103 G_resp_id := p_resp_id;
104 G_proj_auth_tab.DELETE;
105 G_view_proj_i_tab.DELETE;
106 G_view_proj_cr_tab.DELETE;
107 G_view_proj_co_tab.DELETE;
108 G_ci_type_tab.DELETE;
109 END IF;
110
111 l_class_code := p_ci_type_class_code;
112
113 IF l_class_code = 'ISSUE' THEN
114 IF NOT G_view_proj_i_tab.EXISTS(p_project_id) THEN
115 G_view_proj_i_tab(p_project_id) := pa_security_pvt.check_user_privilege(
116 p_privilege => 'PA_CTRL_ISSUES',
117 p_object_name => 'PA_PROJECTS',
118 p_object_key => p_project_id);
119 END IF;
120
121 RETURN G_view_proj_i_tab(p_project_id);
122
123 ELSIF l_class_code = 'CHANGE_REQUEST' THEN
124 IF NOT G_view_proj_cr_tab.EXISTS(p_project_id) THEN
125 G_view_proj_cr_tab(p_project_id) := pa_security_pvt.check_user_privilege(
126 p_privilege => 'PA_CTRL_CHG_REQS',
127 p_object_name => 'PA_PROJECTS',
128 p_object_key => p_project_id);
129 END IF;
130
131 RETURN G_view_proj_cr_tab(p_project_id);
132
133 ELSIF l_class_code = 'CHANGE_ORDER' THEN
134 IF NOT G_view_proj_co_tab.EXISTS(p_project_id) THEN
135 G_view_proj_co_tab(p_project_id) := pa_security_pvt.check_user_privilege(
136 p_privilege => 'PA_CTRL_CHG_ORDS',
137 p_object_name => 'PA_PROJECTS',
138 p_object_key => p_project_id);
139 END IF;
140
141 RETURN G_view_proj_co_tab(p_project_id);
142 END IF;
143 END check_view_project;
144
145
146 FUNCTION check_proj_auth_ci(
147 p_project_id NUMBER,
148 p_user_id NUMBER,
149 p_resp_id NUMBER)
150 RETURN VARCHAR2
151 IS
152 BEGIN
153 --mthai_debug_msg('a('||p_project_id||', '||p_user_id||', '||p_resp_id||')');
154 IF G_user_id <> p_user_id THEN
155 G_user_id := p_user_id;
156 G_resp_id := p_resp_id;
157 G_party_id := pa_control_items_utils.getPartyId(p_user_id);
158 G_proj_auth_tab.DELETE;
159 G_view_proj_i_tab.DELETE;
160 G_view_proj_cr_tab.DELETE;
161 G_view_proj_co_tab.DELETE;
162 G_ci_type_tab.DELETE;
163 --mthai_debug_msg('Reset 1');
164 ELSIF G_resp_id <> p_resp_id THEN
165 G_resp_id := p_resp_id;
166 G_proj_auth_tab.DELETE;
167 G_view_proj_i_tab.DELETE;
168 G_view_proj_cr_tab.DELETE;
169 G_view_proj_co_tab.DELETE;
170 G_ci_type_tab.DELETE;
171 --mthai_debug_msg('Reset 2');
172 END IF;
173
174 IF NOT G_proj_auth_tab.EXISTS(p_project_id) THEN
175 G_proj_auth_tab(p_project_id) := pa_security_pvt.check_user_privilege(
176 p_privilege => 'PA_CI_UPDATE',
177 p_object_name => 'PA_PROJECTS',
178 p_object_key => p_project_id);
179 --mthai_debug_msg('pa not found, query returns '||G_proj_auth_tab(p_project_id));
180 END IF;
181
182 RETURN G_proj_auth_tab(p_project_id);
183 END check_proj_auth_ci;
184
185 FUNCTION is_owner(
186 p_ci_id NUMBER,
187 p_user_id NUMBER)
188 RETURN VARCHAR2
189 IS
190 l_status_code VARCHAR2(100);
191 l_owner_party_id NUMBER;
192 l_creator_user_id NUMBER;
193 BEGIN
194 --mthai_debug_msg('o('||p_ci_id||', '||p_user_id||')');
195 --Clearing cache if user_id is changed
196 IF G_user_id <> p_user_id THEN
197 G_user_id := p_user_id;
198 G_resp_id := -999;
199 G_party_id := pa_control_items_utils.getPartyId(p_user_id);
200 G_proj_auth_tab.DELETE;
201 G_view_proj_i_tab.DELETE;
202 G_view_proj_cr_tab.DELETE;
203 G_view_proj_co_tab.DELETE;
204 G_ci_type_tab.DELETE;
205 --mthai_debug_msg('Reset 3');
206 END IF;
207
208 SELECT s.project_system_status_code, ci.owner_id, ci.created_by
209 INTO l_status_code, l_owner_party_id, l_creator_user_id
210 FROM pa_control_items ci,
211 pa_project_statuses s
212 WHERE ci.ci_id = p_ci_id
213 AND s.status_type = 'CONTROL_ITEM'
214 AND s.project_status_code = ci.status_code;
215
216 IF (l_status_code='CI_DRAFT' AND l_creator_user_id = G_user_id) OR
217 (l_status_code<>'CI_DRAFT' AND l_owner_party_id = G_party_id) THEN
218 RETURN 'T';
219 END IF;
220
221 RETURN 'F';
222 END is_owner;
223
224 FUNCTION check_view_access(
225 p_ci_id NUMBER,
226 p_user_id NUMBER DEFAULT fnd_global.user_id,
227 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
228 RETURN VARCHAR2
229 IS
230 l_status_code VARCHAR2(100);
231 l_project_id NUMBER;
232 l_tmp NUMBER;
233 BEGIN
234 SELECT ci.project_id, s.project_system_status_code
235 INTO l_project_id, l_status_code
236 FROM pa_control_items ci,
237 pa_project_statuses s
238 WHERE ci.ci_id = p_ci_id
239 AND s.status_type = 'CONTROL_ITEM'
240 AND s.project_status_code = ci.status_code;
241
242 --Only the creator can see a draft item.
243 IF l_status_code = 'CI_DRAFT' AND
244 check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'F' AND
245 is_owner(p_ci_id, p_user_id) = 'F' THEN
246 RETURN 'F';
247 END IF;
248
249 --Need to have access to the project to see the control items
250 IF check_view_project(l_project_id, p_ci_id, p_user_id, p_resp_id) = 'F' THEN
251 --Allowing view access if user ever has an action
252 BEGIN
253 SELECT 1
254 INTO l_tmp
255 FROM pa_ci_actions a
256 WHERE a.ci_id = p_ci_id
257 AND a.assigned_to = G_party_id
258 AND ROWNUM = 1;
259 EXCEPTION
260 WHEN OTHERS THEN
261 RETURN 'F';
262 END;
263 END IF;
264
265 RETURN 'T';
266 END;
267
268 FUNCTION check_view_access(
269 p_ci_id NUMBER,
270 p_project_id NUMBER,
271 p_sys_stat_code VARCHAR2,
272 p_ci_type_class_code VARCHAR2,
273 p_user_id NUMBER DEFAULT fnd_global.user_id,
274 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
275 RETURN VARCHAR2
276 IS
277 l_status_code VARCHAR2(100);
278 l_project_id NUMBER;
279 l_tmp NUMBER;
280 BEGIN
281 l_status_code := p_sys_stat_code;
282 l_project_id := p_project_id;
283
284 --Only the creator can see a draft item.
285 IF l_status_code = 'CI_DRAFT' AND
286 check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'F' AND
287 is_owner(p_ci_id, p_user_id) = 'F' THEN
288 RETURN 'F';
289 END IF;
290
291 --Need to have access to the project to see the control items
292 IF check_view_project(l_project_id, p_ci_id, p_ci_type_class_code, p_user_id, p_resp_id) = 'F' THEN
293 --Allowing view access if user ever has an action
294 BEGIN
295 SELECT 1
296 INTO l_tmp
297 FROM pa_ci_actions a
298 WHERE a.ci_id = p_ci_id
299 AND a.assigned_to = G_party_id
300 AND ROWNUM = 1;
301 EXCEPTION
302 WHEN OTHERS THEN
303 RETURN 'F';
304 END;
305 END IF;
306
307 RETURN 'T';
308 END;
309
310 FUNCTION check_update_access(
311 p_ci_id NUMBER,
312 p_user_id NUMBER DEFAULT fnd_global.user_id,
313 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
314 RETURN VARCHAR2
315 IS
316 l_tmp NUMBER;
317 l_project_id NUMBER;
318 l_project_org_id NUMBER;
319 l_locked_flag varchar2(1) := 'N';
320 BEGIN
321 -- Bug#8668693 the follwoing code is to make sure that the pages get rendered in read only mode when the
322 -- document gets locked.
323
324 select nvl(locked_flag,'N')
325 INTO l_locked_flag
326 from pa_control_items
327 where ci_id = p_ci_id;
328
329 if(l_locked_flag = 'Y') then
330 RETURN 'F';
331 end if;
332
333 SELECT ci.project_id, ppa.org_id
334 INTO l_project_id, l_project_org_id
335 FROM pa_control_items ci,
336 pa_projects_all ppa
337 WHERE ppa.project_id=ci.project_id
338 AND ci.ci_id=p_ci_id;
339
340 --Control item cannot be updated across OU
341 --Bug#4519391.Modified the if below to use the function pa_moac_utils.check_access.Passing the org_id
342 --for the project in concern to this function. The function would return N if the project is not secured.
343 IF (pa_moac_utils.check_access(l_project_org_id) = 'N') THEN
344 RETURN 'F';
345 END IF;
346
347 --Project Authorities and Owner can update the item
348 IF check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'T' OR
349 is_owner(p_ci_id, p_user_id) = 'T' THEN
350 RETURN 'T';
351 END IF;
352
353 --People w/ an open Update action can update the item
354 BEGIN
355 SELECT 1
356 INTO l_tmp
357 FROM pa_ci_actions a,
358 pa_project_statuses s
359 WHERE a.ci_id = p_ci_id
360 AND s.project_status_code = a.status_code
361 AND s.status_type = 'CI_ACTION'
362 AND s.project_system_status_code = 'CI_ACTION_OPEN'
363 AND a.type_code = 'UPDATE'
364 -- AND a.assigned_to = pa_control_items_utils.getPartyId(p_user_id)
365 AND a.assigned_to = G_party_id
366 AND ROWNUM = 1;
367
368 RETURN 'T';
369 EXCEPTION
370 WHEN NO_DATA_FOUND THEN
371 RETURN 'F';
372 END;
373
374 RETURN 'F';
375 END;
376
377 FUNCTION check_update_access1(
378 p_ci_id NUMBER,
379 p_project_id NUMBER,
380 p_proj_org_id NUMBER,
381 p_user_id NUMBER DEFAULT fnd_global.user_id,
382 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
383 RETURN VARCHAR2
384 IS
385 l_tmp NUMBER;
386 l_project_id NUMBER;
387 l_project_org_id NUMBER;
388 BEGIN
389 /*
390 SELECT ci.project_id, ppa.org_id
391 INTO l_project_id, l_project_org_id
392 FROM pa_control_items ci,
393 pa_projects_all ppa
394 WHERE ppa.project_id=ci.project_id
395 AND ci.ci_id=p_ci_id;
396 */
397
398 l_project_id := p_project_id;
399 l_project_org_id := p_proj_org_id;
400
401 --Control item cannot be updated across OU
402 --Bug#4519391.Modified the if below to use the function pa_moac_utils.check_access.Passing the org_id
403 --for the project in concern to this function. The function would return N if the project is not secured.
404 IF (pa_moac_utils.check_access(l_project_org_id) = 'N') THEN
405 RETURN 'F';
406 END IF;
407
408 --Project Authorities and Owner can update the item
409 IF check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'T' OR
410 is_owner(p_ci_id, p_user_id) = 'T' THEN
411 RETURN 'T';
412 END IF;
413
414 --People w/ an open Update action can update the item
415 BEGIN
416 SELECT 1
417 INTO l_tmp
418 FROM pa_ci_actions a,
419 pa_project_statuses s
420 WHERE a.ci_id = p_ci_id
421 AND s.project_status_code = a.status_code
422 AND s.status_type = 'CI_ACTION'
423 AND s.project_system_status_code = 'CI_ACTION_OPEN'
424 AND a.type_code = 'UPDATE'
425 -- AND a.assigned_to = pa_control_items_utils.getPartyId(p_user_id)
426 AND a.assigned_to = G_party_id
427 AND ROWNUM = 1;
428
429 RETURN 'T';
430 EXCEPTION
431 WHEN NO_DATA_FOUND THEN
432 RETURN 'F';
433 END;
434
435 RETURN 'F';
436 END;
437
438 FUNCTION check_change_owner_access(
439 p_ci_id NUMBER,
440 p_user_id NUMBER DEFAULT fnd_global.user_id,
441 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
442 RETURN VARCHAR2
443 IS
444 l_tmp NUMBER;
445 l_project_id NUMBER;
446 l_project_org_id NUMBER;
447 BEGIN
448 SELECT ci.project_id, ppa.org_id
449 INTO l_project_id, l_project_org_id
450 FROM pa_control_items ci, pa_projects_all ppa
451 WHERE ci.project_id=ppa.project_id
452 AND ci.ci_id = p_ci_id;
453
454 --Bug#9791886
455 -- The Function pa_moac_utils.check_access was failing since MOAC initialization was not done
456 pa_moac_utils.initialize;
457 --Control item cannot be updated across OU
458 --Bug#4519391.Modified the if below to use the function pa_moac_utils.check_access.Passing the org_id
459 --for the project in concern to this function. The function would return N if the project is not secured.
460 IF (pa_moac_utils.check_access(l_project_org_id) = 'N') THEN
461 RETURN 'F';
462 END IF;
463 --Project Authorities and Owner can change the owner the item
464 IF check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'T' OR
465 is_owner(p_ci_id, p_user_id) = 'T' THEN
466 RETURN 'T';
467 END IF;
468 RETURN 'F';
469 END;
470
471 FUNCTION check_change_status_access(
472 p_ci_id NUMBER,
473 p_user_id NUMBER DEFAULT fnd_global.user_id,
474 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
475 RETURN VARCHAR2
476 IS
477 BEGIN
478 RETURN check_change_owner_access(p_ci_id, p_user_id, p_resp_id);
479
480 END;
481
482 FUNCTION check_highlight_access(
483 p_ci_id NUMBER,
484 p_user_id NUMBER DEFAULT fnd_global.user_id,
485 p_resp_id NUMBER DEFAULT fnd_global.resp_id,
486 p_project_id NUMBER DEFAULT NULL)
487 RETURN VARCHAR2
488 IS
489 l_tmp NUMBER;
490 l_project_id NUMBER := p_project_id;
491 BEGIN
492 IF p_ci_id > 0 THEN
493 SELECT project_id
494 INTO l_project_id
495 FROM pa_control_items
496 WHERE ci_id = p_ci_id;
497 END IF;
498
499 --Only Project Authorities can highlight the item
500 IF check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'T' THEN
501 RETURN 'T';
502 END IF;
503
504 RETURN 'F';
505 END;
506
507 FUNCTION check_implement_impact_access(
508 p_ci_id NUMBER,
509 p_user_id NUMBER DEFAULT fnd_global.user_id,
510 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
511 RETURN VARCHAR2
512 IS
513 BEGIN
514 RETURN check_change_owner_access(p_ci_id, p_user_id, p_resp_id);
515 END;
516
517
518 FUNCTION check_create_action(
519 p_ci_id IN NUMBER,
520 p_user_id IN NUMBER DEFAULT fnd_global.user_id,
521 p_calling_context IN VARCHAR2 DEFAULT 'UI')-- Bug 5676037. Added the parameter to identify the amg context.
522 return VARCHAR2
523 IS
524 l_result varchar2(1);
525 l_party_id number;
526 l_ci_action_id number;
527 l_status_code varchar2(30);
528 l_status_result varchar2(1);
529 l_project_id NUMBER;
530 l_project_org_id NUMBER;
531
532 Cursor person_with_open_action is
533 select a.ci_action_id
534 from pa_ci_actions a,
535 pa_project_statuses s
536 where a.assigned_to = l_party_id
537 and a.ci_id = p_ci_id
538 and s.project_status_code = a.status_code
539 AND s.status_type = 'CI_ACTION'
540 and s.project_system_status_code = 'CI_ACTION_OPEN';
541
542 BEGIN
543
544 l_party_id := PA_CONTROL_ITEMS_UTILS.GetPartyId(p_user_id);
545
546 if(l_party_id IS NULL) then
547 return 'F';
548 end if;
549
550 --Bug 5676037. Added the if condition to identify the AMG context. From AMG context
551 -- CheckCIActionAllowed should not be called.
552 if(p_calling_context <> 'AMG') then
553 l_status_result := PA_CONTROL_ITEMS_UTILS.CheckCIActionAllowed(null,null,'CONTROL_ITEM_ALLOW_ACTION', p_ci_id);
554 if (l_status_result = 'N') then
555 return 'F';
556 end if;
557 end if;-- if(p_calling_context <> 'AMG') then
558
559 SELECT ci.project_id, ppa.org_id
560 INTO l_project_id, l_project_org_id
561 FROM pa_control_items ci, pa_projects_all ppa
562 WHERE ci.project_id=ppa.project_id
563 AND ci.ci_id = p_ci_id;
564
565 --Control item cannot be updated across OU
566 --Bug#4519391.Modified the if below to use the function pa_moac_utils.check_access.Passing the org_id
567 --for the project in concern to this function. The function would return N if the project is not secured.
568 IF (pa_moac_utils.check_access(l_project_org_id) = 'N') THEN
569 RETURN 'F';
570 END IF;
571
572 IF check_proj_auth_ci(l_project_id, p_user_id, fnd_global.resp_id) = 'T' OR
573 is_owner(p_ci_id, p_user_id) = 'T' THEN
574 RETURN 'T';
575 END IF;
576
577 Open person_with_open_action;
578 fetch person_with_open_action into l_ci_action_id;
579 if (person_with_open_action%FOUND) then
580 close person_with_open_action;
581 return 'T';
582 end if;
583 close person_with_open_action;
584
585 RETURN 'F';
586
587 END check_create_action;
588
589 FUNCTION check_item_owner_project_auth(
590 p_ci_id in NUMBER,
591 p_user_id IN NUMBER DEFAULT fnd_global.user_id)
592 RETURN VARCHAR2
593 IS
594 l_project_id number;
595 l_project_org_id number;
596
597 BEGIN
598 SELECT ci.project_id, ppa.org_id
599 INTO l_project_id, l_project_org_id
600 FROM pa_control_items ci, pa_projects_all ppa
601 WHERE ci.project_id=ppa.project_id
602 AND ci.ci_id = p_ci_id;
603
604 --Control item cannot be updated across OU
605 --Bug#4519391.Modified the if below to use the function pa_moac_utils.check_access.Passing the org_id
606 --for the project in concern to this function. The function would return N if the project is not secured.
607 IF (pa_moac_utils.check_access(l_project_org_id) = 'N') THEN
608 RETURN 'F';
609 END IF;
610
611 IF check_proj_auth_ci(l_project_id, p_user_id, fnd_global.resp_id) = 'T' OR
612 is_owner(p_ci_id, p_user_id) = 'T' THEN
613 RETURN 'T';
614 END IF;
615
616 RETURN 'F';
617 END check_item_owner_project_auth;
618
619
620 FUNCTION check_open_action_assigned_to(
621 p_ci_action_id IN NUMBER,
622 p_user_id IN NUMBER DEFAULT fnd_global.user_id)
623 return varchar2
624 IS
625 l_result varchar2(1);
626 l_party_id number;
627
628 Cursor comment_or_close_action is
629 select 'T'
630 from pa_ci_actions a,
631 pa_project_statuses s
632 where a.ci_action_id = p_ci_action_id
633 and a.assigned_to = l_party_id
634 and s.project_status_code = a.status_code
635 AND s.status_type = 'CI_ACTION'
636 and s.project_system_status_code = 'CI_ACTION_OPEN';
637
638 BEGIN
639 l_party_id := PA_CONTROL_ITEMS_UTILS.GetPartyId(p_user_id);
640
641 if(l_party_id IS NULL) then
642 return 'F';
643 end if;
644
645 Open comment_or_close_action;
646 fetch comment_or_close_action into l_result;
647 if (comment_or_close_action%NOTFOUND) then
648 l_party_id := NULL;
649 close comment_or_close_action;
650 return 'F';
651 end if;
652 close comment_or_close_action;
653
654 return l_result;
655 EXCEPTION
656 WHEN OTHERS THEN -- catch the exceptins here
657 RAISE;
658 END check_open_action_assigned_to;
659
660 FUNCTION check_updatable_comment(
661 p_ci_comment_id IN NUMBER,
662 p_user_id IN NUMBER DEFAULT fnd_global.user_id)
663 return varchar2
664 IS
665 l_ci_id number;
666 l_created_by number;
667
668 Cursor comment_owner is
669 select ci_id, created_by
670 from pa_ci_comments
671 where ci_comment_id = p_ci_comment_id;
672
673 BEGIN
674
675 Open comment_owner;
676 fetch comment_owner into l_ci_id,l_created_by;
677 if (comment_owner%NOTFOUND) then
678 close comment_owner;
679 return 'F';
680 end if;
681 close comment_owner;
682
683 if (l_created_by = p_user_id) then
684 return 'T';
685 end if;
686
687 return check_item_owner_project_auth(l_ci_id, p_user_id);
688
689 EXCEPTION
690 WHEN OTHERS THEN -- catch the exceptins here
691 RAISE;
692 END check_updatable_comment;
693
694 FUNCTION check_create_CI(
695 p_ci_type_id NUMBER,
696 p_project_id NUMBER,
697 p_user_id NUMBER DEFAULT fnd_global.user_id,
698 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
699 RETURN VARCHAR2
700 IS
701 l_tmp NUMBER;
702 l_allow_all_usage_flag VARCHAR2(1);
703 l_access_level NUMBER;
704 l_return_status VARCHAR2(1) := 'S';
705 l_msg_count NUMBER := 0;
706 l_msg_data VARCHAR2(4000) := '';
707 l_resource_id NUMBER := -999;
708 l_project_org_id NUMBER; -- Bug#12847666
709 BEGIN
710 SELECT org_id INTO l_project_org_id
711 FROM pa_projects_all WHERE project_id=p_project_id;
712
713 --Control item cannot be updated across OU
714 --Bug#12847666. Added the if below to use the function pa_moac_utils.check_access.Passing the org_id
715 --for the project in concern to this function. The function would return N if the project is not secured.
716 IF (pa_moac_utils.check_access(l_project_org_id) = 'N') THEN
717 G_ci_type_tab(p_ci_type_id) := 'F';
718 RETURN G_ci_type_tab(p_ci_type_id);
719 END IF;
720
721 IF G_user_id<>p_user_id OR G_resp_id<>p_resp_id THEN
722 G_user_id := p_user_id;
723 G_resp_id := p_resp_id;
724 G_party_id := pa_control_items_utils.getPartyId(p_user_id);
725 G_project_id := p_project_id;
726 G_proj_auth_tab.DELETE;
727 G_view_proj_i_tab.DELETE;
728 G_view_proj_cr_tab.DELETE;
729 G_view_proj_co_tab.DELETE;
730 G_ci_type_tab.DELETE;
731 ELSIF G_project_id<>p_project_id THEN
732 G_project_id := p_project_id;
733 G_ci_type_tab.DELETE;
734 END IF;
735
736 IF G_ci_type_tab.EXISTS(p_ci_type_id) THEN
737 RETURN G_ci_type_tab(p_ci_type_id);
738 END IF;
739
740 SELECT allow_all_usage_flag
741 INTO l_allow_all_usage_flag
742 FROM pa_ci_types_b
743 WHERE ci_type_id = p_ci_type_id;
744
745 IF l_allow_all_usage_flag = 'N' THEN
746 BEGIN
747 SELECT 1
748 INTO l_tmp
749 FROM pa_ci_type_usage citu,
750 pa_projects_all ppa,
751 pa_project_types_all ppt
752 WHERE ppa.project_id = p_project_id
753 AND ppt.project_type = ppa.project_type
754 AND citu.project_type_id = ppt.project_type_id
755 AND citu.ci_type_id = p_ci_type_id
756 AND ROWNUM = 1;
757 EXCEPTION
758 WHEN NO_DATA_FOUND THEN
759 G_ci_type_tab(p_ci_type_id) := 'F';
760 RETURN G_ci_type_tab(p_ci_type_id);
761 END;
762 END IF;
763
764 IF check_proj_auth_ci(p_project_id, p_user_id, p_resp_id) = 'T' THEN
765 G_ci_type_tab(p_ci_type_id) := 'T';
766 RETURN G_ci_type_tab(p_ci_type_id);
767 END IF;
768
769 BEGIN
770 l_resource_id := pa_resource_utils.get_resource_id(NULL, p_user_id);
771
772 SELECT 1
773 INTO l_tmp
774 FROM pa_object_dist_lists l,
775 pa_dist_list_items i,
776 pa_project_parties p
777 WHERE l.object_type = 'PA_CI_TYPES'
778 AND l.object_id = p_ci_type_id
779 AND i.list_id = l.list_id
780 AND p.project_id = p_project_id
781 AND p.resource_id = l_resource_id
782 AND ( i.recipient_type = 'ALL_PROJECT_PARTIES'
783 OR ( i.recipient_type = 'PROJECT_ROLE'
784 AND p.project_role_id = i.recipient_id
785 )
786 )/*added the condition for bug#12847813*/
787 and sysdate between p.start_date_active and nvl(p.end_date_active,
788 sysdate+1)
789 AND ROWNUM = 1;
790
791 G_ci_type_tab(p_ci_type_id) := 'T';
792 RETURN G_ci_type_tab(p_ci_type_id);
793 EXCEPTION
794 WHEN OTHERS THEN
795 NULL;
796 END;
797
798 G_ci_type_tab(p_ci_type_id) := 'F';
799 RETURN G_ci_type_tab(p_ci_type_id);
800 END;
801
802
803 FUNCTION is_to_owner_allowed(
804 p_ci_id NUMBER,
805 p_owner_id NUMBER)
806 RETURN VARCHAR2
807 IS
808 l_tmp NUMBER;
809 l_project_id NUMBER;
810 l_owner_id NUMBER;
811
812 cursor c_owner (p_project_id NUMBER) is
813 select distinct resource_party_id
814 from PA_PROJECT_PARTIES_V
815 where party_type <> 'ORGANIZATION'
816 and project_id = p_project_id
817 and resource_party_id = p_owner_id;
818
819 BEGIN
820
821 SELECT project_id
822 INTO l_project_id
823 FROM pa_control_items
824 WHERE ci_id = p_ci_id;
825
826 open c_owner(l_project_id);
827 fetch c_owner into l_owner_id;
828
829 If c_owner%NOTFOUND then
830 close c_owner;
831 RETURN 'F';
832 End if;
833 close c_owner;
834
835 RETURN 'T';
836
837 EXCEPTION
838 WHEN OTHERS THEN
839 RAISE;
840 END;
841
842 -- This procedure loops through all the responsibilities that the user is
843 -- assigned with and check if he has access to a page. If he does, return
844 -- resp_key and appl_short_name .
845 -- Added as part of Bug#13683760.
846 PROCEDURE GET_RESP_WITH_ACCESS
847 (
848 p_privilege IN VARCHAR2,
849 p_project_id IN NUMBER,
850 x_has_access out NOCOPY varchar2,
851 x_resp_key out NOCOPY VARCHAR2,
852 x_appl_short_name out NOCOPY VARCHAR2,
853 x_return_status out NOCOPY varchar2,
854 x_msg_count out NOCOPY number,
855 x_msg_data out NOCOPY varchar2) is
856
857
858 --Get all the responsibilities for the user
859 CURSOR user_resp_csr(p_user_id NUMBER) is
860 select a.responsibility_id, c.application_id,
861 b.responsibility_key, c.application_short_name
862 from fnd_user_resp_groups_all a,
863 fnd_responsibility_vl b,
864 fnd_application c
865 where a.user_id = p_user_id
866 and sysdate between NVL(a.start_date,sysdate) and NVL(a.end_date,sysdate)
867 and a.responsibility_id = b.responsibility_id
868 and a.responsibility_application_id = b.application_id
869 and b.application_id = c.application_id;
870
871 l_user_id NUMBER;
872 l_old_resp_id NUMBER;
873 l_old_resp_appl_id NUMBER;
874 l_ret_code VARCHAR2(1) := 'F';
875 l_resp_key VARCHAR2(30);
876 l_appl_short_name VARCHAR2(50);
877
878 Begin
879 pa_debug.Init_err_stack ( 'GET_RESP_WITH_ACCESS');
880 x_msg_count := 0;
881 x_msg_data := null;
882 x_return_status := fnd_api.g_ret_sts_success;
883 x_has_access := 'F';
884
885 l_old_resp_id := FND_GLOBAL.RESP_ID;
886 l_old_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
887 l_user_id := FND_GLOBAL.USER_ID;
888
889 IF G_debug_flag = 'Y' THEN
890 pa_debug.write_file('check_access_exist: ' || 'LOG',' p_privilege '||p_privilege||' l_user_id '||l_user_id);
891 END IF;
892 --Get the responsibility that has access to view budget.
893 FOR user_resp_rec IN user_resp_csr(l_user_id) LOOP
894
895 FND_GLOBAL.Apps_Initialize
896 ( user_id => l_user_id
897 , resp_id => user_resp_rec.responsibility_id
898 , resp_appl_id => user_resp_rec.application_id
899 );
900 PA_SECURITY_PVT.check_user_privilege
901 ( p_privilege => p_privilege
902 ,p_object_name => 'PA_PROJECTS'
903 ,p_object_key => to_number(NULL) --Bug 14308345.
904 ,x_ret_code => l_ret_code
905 ,x_return_status => x_return_status
906 ,x_msg_count => x_msg_count
907 ,x_msg_data => x_msg_data
908 );
909 IF G_debug_flag = 'Y' THEN
910 pa_debug.write_file('check_access_exist: ' || 'LOG',' resp_id '||user_resp_rec.responsibility_id||' l_ret_code '||l_ret_code);
911 END IF;
912 IF l_ret_code = 'T' THEN
913 x_resp_key := user_resp_rec.responsibility_key;
914 x_appl_short_name := user_resp_rec.application_short_name;
915 x_has_access := 'T';
916 EXIT;
917 END IF;
918
919 END LOOP;
920 IF l_ret_code <> 'T' THEN
921 x_has_access := 'F';
922 x_resp_key := '';
923 x_appl_short_name := '';
924 END IF;
925
926 IF G_debug_flag = 'Y' THEN
927 pa_debug.write_file('check_access_exist: ' || 'LOG',' x_has_access '||x_has_access);
928 pa_debug.write_file('check_access_exist: ' || 'LOG',' x_resp_key '||x_resp_key||' x_appl_short_name '||x_appl_short_name);
929 END IF;
930
931 FND_GLOBAL.Apps_Initialize
932 ( user_id => l_user_id
933 , resp_id => l_old_resp_id
934 , resp_appl_id => l_old_resp_appl_id
935 );
936
937 EXCEPTION
938 WHEN OTHERS THEN
939 fnd_msg_pub.add_exc_msg
940 (p_pkg_name => G_PKG_NAME,
941 p_procedure_name =>'get_resp_with_access' );
942
943 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
944 x_has_access := 'F';
945 x_resp_key := '';
946 x_appl_short_name := '';
947
948 FND_MSG_PUB.Count_And_Get
949 (p_count => x_msg_count ,
950 p_data => x_msg_data
951 );
952 END GET_RESP_WITH_ACCESS;
953
954 END pa_ci_security_pkg;
[Home] [Help]