1 PACKAGE BODY pa_ci_security_pkg AS
2 /* $Header: PACISECB.pls 120.2 2007/02/08 11:57:15 sukhanna 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
15 FUNCTION check_view_project(
16 p_project_id NUMBER,
17 p_ci_id NUMBER,
18 p_user_id NUMBER DEFAULT fnd_global.user_id,
19 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
20 RETURN VARCHAR2
21 IS
22 l_class_code pa_ci_types_b.ci_type_class_code%TYPE;
23 BEGIN
24 IF G_user_id <> p_user_id THEN
25 G_user_id := p_user_id;
26 G_resp_id := p_resp_id;
27 G_party_id := pa_control_items_utils.getPartyId(p_user_id);
28 G_proj_auth_tab.DELETE;
29 G_view_proj_i_tab.DELETE;
30 G_view_proj_cr_tab.DELETE;
31 G_view_proj_co_tab.DELETE;
32 G_ci_type_tab.DELETE;
33 ELSIF G_resp_id <> p_resp_id THEN
34 G_resp_id := p_resp_id;
35 G_proj_auth_tab.DELETE;
36 G_view_proj_i_tab.DELETE;
37 G_view_proj_cr_tab.DELETE;
38 G_view_proj_co_tab.DELETE;
39 G_ci_type_tab.DELETE;
40 END IF;
41
42 SELECT ci_type_class_code
43 INTO l_class_code
44 FROM pa_ci_types_b cit,
45 pa_control_items ci
46 WHERE ci.ci_id = p_ci_id
47 AND cit.ci_type_id = ci.ci_type_id;
48
49 IF l_class_code = 'ISSUE' THEN
50 IF NOT G_view_proj_i_tab.EXISTS(p_project_id) THEN
51 G_view_proj_i_tab(p_project_id) := pa_security_pvt.check_user_privilege(
52 p_privilege => 'PA_CTRL_ISSUES',
53 p_object_name => 'PA_PROJECTS',
54 p_object_key => p_project_id);
55 END IF;
56
57 RETURN G_view_proj_i_tab(p_project_id);
58
59 ELSIF l_class_code = 'CHANGE_REQUEST' THEN
60 IF NOT G_view_proj_cr_tab.EXISTS(p_project_id) THEN
61 G_view_proj_cr_tab(p_project_id) := pa_security_pvt.check_user_privilege(
62 p_privilege => 'PA_CTRL_CHG_REQS',
63 p_object_name => 'PA_PROJECTS',
64 p_object_key => p_project_id);
65 END IF;
66
67 RETURN G_view_proj_cr_tab(p_project_id);
68
69 ELSIF l_class_code = 'CHANGE_ORDER' THEN
70 IF NOT G_view_proj_co_tab.EXISTS(p_project_id) THEN
71 G_view_proj_co_tab(p_project_id) := pa_security_pvt.check_user_privilege(
72 p_privilege => 'PA_CTRL_CHG_ORDS',
73 p_object_name => 'PA_PROJECTS',
74 p_object_key => p_project_id);
75 END IF;
76
77 RETURN G_view_proj_co_tab(p_project_id);
78 END IF;
79 END check_view_project;
80
81 FUNCTION check_view_project (
82 p_project_id NUMBER,
83 p_ci_id NUMBER,
84 p_ci_type_class_code VARCHAR2,
85 p_user_id NUMBER DEFAULT fnd_global.user_id,
86 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
87 RETURN VARCHAR2
88 IS
89 l_class_code pa_ci_types_b.ci_type_class_code%TYPE;
90 BEGIN
91 IF G_user_id <> p_user_id THEN
92 G_user_id := p_user_id;
93 G_resp_id := p_resp_id;
94 G_party_id := pa_control_items_utils.getPartyId(p_user_id);
95 G_proj_auth_tab.DELETE;
96 G_view_proj_i_tab.DELETE;
97 G_view_proj_cr_tab.DELETE;
98 G_view_proj_co_tab.DELETE;
99 G_ci_type_tab.DELETE;
100 ELSIF G_resp_id <> p_resp_id THEN
101 G_resp_id := p_resp_id;
102 G_proj_auth_tab.DELETE;
103 G_view_proj_i_tab.DELETE;
104 G_view_proj_cr_tab.DELETE;
105 G_view_proj_co_tab.DELETE;
106 G_ci_type_tab.DELETE;
107 END IF;
108
109 l_class_code := p_ci_type_class_code;
110
111 IF l_class_code = 'ISSUE' THEN
112 IF NOT G_view_proj_i_tab.EXISTS(p_project_id) THEN
113 G_view_proj_i_tab(p_project_id) := pa_security_pvt.check_user_privilege(
114 p_privilege => 'PA_CTRL_ISSUES',
115 p_object_name => 'PA_PROJECTS',
116 p_object_key => p_project_id);
117 END IF;
118
119 RETURN G_view_proj_i_tab(p_project_id);
120
121 ELSIF l_class_code = 'CHANGE_REQUEST' THEN
122 IF NOT G_view_proj_cr_tab.EXISTS(p_project_id) THEN
123 G_view_proj_cr_tab(p_project_id) := pa_security_pvt.check_user_privilege(
124 p_privilege => 'PA_CTRL_CHG_REQS',
125 p_object_name => 'PA_PROJECTS',
126 p_object_key => p_project_id);
127 END IF;
128
129 RETURN G_view_proj_cr_tab(p_project_id);
130
131 ELSIF l_class_code = 'CHANGE_ORDER' THEN
132 IF NOT G_view_proj_co_tab.EXISTS(p_project_id) THEN
133 G_view_proj_co_tab(p_project_id) := pa_security_pvt.check_user_privilege(
134 p_privilege => 'PA_CTRL_CHG_ORDS',
135 p_object_name => 'PA_PROJECTS',
136 p_object_key => p_project_id);
137 END IF;
138
139 RETURN G_view_proj_co_tab(p_project_id);
140 END IF;
141 END check_view_project;
142
143
144 FUNCTION check_proj_auth_ci(
145 p_project_id NUMBER,
146 p_user_id NUMBER,
147 p_resp_id NUMBER)
148 RETURN VARCHAR2
149 IS
150 BEGIN
151 --mthai_debug_msg('a('||p_project_id||', '||p_user_id||', '||p_resp_id||')');
152 IF G_user_id <> p_user_id THEN
153 G_user_id := p_user_id;
154 G_resp_id := p_resp_id;
155 G_party_id := pa_control_items_utils.getPartyId(p_user_id);
156 G_proj_auth_tab.DELETE;
157 G_view_proj_i_tab.DELETE;
158 G_view_proj_cr_tab.DELETE;
159 G_view_proj_co_tab.DELETE;
160 G_ci_type_tab.DELETE;
161 --mthai_debug_msg('Reset 1');
162 ELSIF G_resp_id <> p_resp_id THEN
163 G_resp_id := p_resp_id;
164 G_proj_auth_tab.DELETE;
165 G_view_proj_i_tab.DELETE;
166 G_view_proj_cr_tab.DELETE;
167 G_view_proj_co_tab.DELETE;
168 G_ci_type_tab.DELETE;
169 --mthai_debug_msg('Reset 2');
170 END IF;
171
172 IF NOT G_proj_auth_tab.EXISTS(p_project_id) THEN
173 G_proj_auth_tab(p_project_id) := pa_security_pvt.check_user_privilege(
174 p_privilege => 'PA_CI_UPDATE',
175 p_object_name => 'PA_PROJECTS',
176 p_object_key => p_project_id);
177 --mthai_debug_msg('pa not found, query returns '||G_proj_auth_tab(p_project_id));
178 END IF;
179
180 RETURN G_proj_auth_tab(p_project_id);
181 END check_proj_auth_ci;
182
183 FUNCTION is_owner(
184 p_ci_id NUMBER,
185 p_user_id NUMBER)
186 RETURN VARCHAR2
187 IS
188 l_status_code VARCHAR2(100);
189 l_owner_party_id NUMBER;
190 l_creator_user_id NUMBER;
191 BEGIN
192 --mthai_debug_msg('o('||p_ci_id||', '||p_user_id||')');
193 --Clearing cache if user_id is changed
194 IF G_user_id <> p_user_id THEN
195 G_user_id := p_user_id;
196 G_resp_id := -999;
197 G_party_id := pa_control_items_utils.getPartyId(p_user_id);
198 G_proj_auth_tab.DELETE;
199 G_view_proj_i_tab.DELETE;
200 G_view_proj_cr_tab.DELETE;
201 G_view_proj_co_tab.DELETE;
202 G_ci_type_tab.DELETE;
203 --mthai_debug_msg('Reset 3');
204 END IF;
205
206 SELECT s.project_system_status_code, ci.owner_id, ci.created_by
207 INTO l_status_code, l_owner_party_id, l_creator_user_id
208 FROM pa_control_items ci,
209 pa_project_statuses s
210 WHERE ci.ci_id = p_ci_id
211 AND s.status_type = 'CONTROL_ITEM'
212 AND s.project_status_code = ci.status_code;
213
214 IF (l_status_code='CI_DRAFT' AND l_creator_user_id = G_user_id) OR
215 (l_status_code<>'CI_DRAFT' AND l_owner_party_id = G_party_id) THEN
216 RETURN 'T';
217 END IF;
218
219 RETURN 'F';
220 END is_owner;
221
222 FUNCTION check_view_access(
223 p_ci_id NUMBER,
224 p_user_id NUMBER DEFAULT fnd_global.user_id,
225 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
226 RETURN VARCHAR2
227 IS
228 l_status_code VARCHAR2(100);
229 l_project_id NUMBER;
230 l_tmp NUMBER;
231 BEGIN
232 SELECT ci.project_id, s.project_system_status_code
233 INTO l_project_id, l_status_code
234 FROM pa_control_items ci,
235 pa_project_statuses s
236 WHERE ci.ci_id = p_ci_id
237 AND s.status_type = 'CONTROL_ITEM'
238 AND s.project_status_code = ci.status_code;
239
240 --Only the creator can see a draft item.
241 IF l_status_code = 'CI_DRAFT' AND
242 check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'F' AND
243 is_owner(p_ci_id, p_user_id) = 'F' THEN
244 RETURN 'F';
245 END IF;
246
247 --Need to have access to the project to see the control items
248 IF check_view_project(l_project_id, p_ci_id, p_user_id, p_resp_id) = 'F' THEN
249 --Allowing view access if user ever has an action
250 BEGIN
251 SELECT 1
252 INTO l_tmp
253 FROM pa_ci_actions a
254 WHERE a.ci_id = p_ci_id
255 AND a.assigned_to = G_party_id
256 AND ROWNUM = 1;
257 EXCEPTION
258 WHEN OTHERS THEN
259 RETURN 'F';
260 END;
261 END IF;
262
263 RETURN 'T';
264 END;
265
266 FUNCTION check_view_access(
267 p_ci_id NUMBER,
268 p_project_id NUMBER,
269 p_sys_stat_code VARCHAR2,
270 p_ci_type_class_code VARCHAR2,
271 p_user_id NUMBER DEFAULT fnd_global.user_id,
272 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
273 RETURN VARCHAR2
274 IS
275 l_status_code VARCHAR2(100);
276 l_project_id NUMBER;
277 l_tmp NUMBER;
278 BEGIN
279 l_status_code := p_sys_stat_code;
280 l_project_id := p_project_id;
281
282 --Only the creator can see a draft item.
283 IF l_status_code = 'CI_DRAFT' AND
284 check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'F' AND
285 is_owner(p_ci_id, p_user_id) = 'F' THEN
286 RETURN 'F';
287 END IF;
288
289 --Need to have access to the project to see the control items
290 IF check_view_project(l_project_id, p_ci_id, p_ci_type_class_code, p_user_id, p_resp_id) = 'F' THEN
291 --Allowing view access if user ever has an action
292 BEGIN
293 SELECT 1
294 INTO l_tmp
295 FROM pa_ci_actions a
296 WHERE a.ci_id = p_ci_id
297 AND a.assigned_to = G_party_id
298 AND ROWNUM = 1;
299 EXCEPTION
300 WHEN OTHERS THEN
301 RETURN 'F';
302 END;
303 END IF;
304
305 RETURN 'T';
306 END;
307
308 FUNCTION check_update_access(
309 p_ci_id NUMBER,
310 p_user_id NUMBER DEFAULT fnd_global.user_id,
311 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
312 RETURN VARCHAR2
313 IS
314 l_tmp NUMBER;
315 l_project_id NUMBER;
316 l_project_org_id NUMBER;
317 BEGIN
318 SELECT ci.project_id, ppa.org_id
319 INTO l_project_id, l_project_org_id
320 FROM pa_control_items ci,
321 pa_projects_all ppa
322 WHERE ppa.project_id=ci.project_id
323 AND ci.ci_id=p_ci_id;
324
325 --Control item cannot be updated across OU
326 --Bug#4519391.Modified the if below to use the function pa_moac_utils.check_access.Passing the org_id
327 --for the project in concern to this function. The function would return N if the project is not secured.
328 IF (pa_moac_utils.check_access(l_project_org_id) = 'N') THEN
329 RETURN 'F';
330 END IF;
331
332 --Project Authorities and Owner can update the item
333 IF check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'T' OR
334 is_owner(p_ci_id, p_user_id) = 'T' THEN
335 RETURN 'T';
336 END IF;
337
338 --People w/ an open Update action can update the item
339 BEGIN
340 SELECT 1
341 INTO l_tmp
342 FROM pa_ci_actions a,
343 pa_project_statuses s
344 WHERE a.ci_id = p_ci_id
345 AND s.project_status_code = a.status_code
346 AND s.status_type = 'CI_ACTION'
347 AND s.project_system_status_code = 'CI_ACTION_OPEN'
348 AND a.type_code = 'UPDATE'
349 -- AND a.assigned_to = pa_control_items_utils.getPartyId(p_user_id)
350 AND a.assigned_to = G_party_id
351 AND ROWNUM = 1;
352
353 RETURN 'T';
354 EXCEPTION
355 WHEN NO_DATA_FOUND THEN
356 RETURN 'F';
357 END;
358
359 RETURN 'F';
360 END;
361
362 FUNCTION check_update_access1(
363 p_ci_id NUMBER,
364 p_project_id NUMBER,
365 p_proj_org_id NUMBER,
366 p_user_id NUMBER DEFAULT fnd_global.user_id,
367 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
368 RETURN VARCHAR2
369 IS
370 l_tmp NUMBER;
371 l_project_id NUMBER;
372 l_project_org_id NUMBER;
373 BEGIN
374 /*
375 SELECT ci.project_id, ppa.org_id
376 INTO l_project_id, l_project_org_id
377 FROM pa_control_items ci,
378 pa_projects_all ppa
379 WHERE ppa.project_id=ci.project_id
380 AND ci.ci_id=p_ci_id;
381 */
382
383 l_project_id := p_project_id;
384 l_project_org_id := p_proj_org_id;
385
386 --Control item cannot be updated across OU
387 --Bug#4519391.Modified the if below to use the function pa_moac_utils.check_access.Passing the org_id
388 --for the project in concern to this function. The function would return N if the project is not secured.
389 IF (pa_moac_utils.check_access(l_project_org_id) = 'N') THEN
390 RETURN 'F';
391 END IF;
392
393 --Project Authorities and Owner can update the item
394 IF check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'T' OR
395 is_owner(p_ci_id, p_user_id) = 'T' THEN
396 RETURN 'T';
397 END IF;
398
399 --People w/ an open Update action can update the item
400 BEGIN
401 SELECT 1
402 INTO l_tmp
403 FROM pa_ci_actions a,
407 AND s.status_type = 'CI_ACTION'
404 pa_project_statuses s
405 WHERE a.ci_id = p_ci_id
406 AND s.project_status_code = a.status_code
408 AND s.project_system_status_code = 'CI_ACTION_OPEN'
409 AND a.type_code = 'UPDATE'
410 -- AND a.assigned_to = pa_control_items_utils.getPartyId(p_user_id)
411 AND a.assigned_to = G_party_id
412 AND ROWNUM = 1;
413
414 RETURN 'T';
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 RETURN 'F';
418 END;
419
420 RETURN 'F';
421 END;
422
423 FUNCTION check_change_owner_access(
424 p_ci_id NUMBER,
425 p_user_id NUMBER DEFAULT fnd_global.user_id,
426 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
427 RETURN VARCHAR2
428 IS
429 l_tmp NUMBER;
430 l_project_id NUMBER;
431 l_project_org_id NUMBER;
432 BEGIN
433 SELECT ci.project_id, ppa.org_id
434 INTO l_project_id, l_project_org_id
435 FROM pa_control_items ci, pa_projects_all ppa
436 WHERE ci.project_id=ppa.project_id
437 AND ci.ci_id = p_ci_id;
438
439 --Control item cannot be updated across OU
440 --Bug#4519391.Modified the if below to use the function pa_moac_utils.check_access.Passing the org_id
441 --for the project in concern to this function. The function would return N if the project is not secured.
442 IF (pa_moac_utils.check_access(l_project_org_id) = 'N') THEN
443 RETURN 'F';
444 END IF;
445
446 --Project Authorities and Owner can change the owner the item
447 IF check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'T' OR
448 is_owner(p_ci_id, p_user_id) = 'T' THEN
449 RETURN 'T';
450 END IF;
451
452 RETURN 'F';
453 END;
454
455 FUNCTION check_change_status_access(
456 p_ci_id NUMBER,
457 p_user_id NUMBER DEFAULT fnd_global.user_id,
458 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
459 RETURN VARCHAR2
460 IS
461 BEGIN
462 RETURN check_change_owner_access(p_ci_id, p_user_id, p_resp_id);
463 END;
464
465 FUNCTION check_highlight_access(
466 p_ci_id NUMBER,
467 p_user_id NUMBER DEFAULT fnd_global.user_id,
468 p_resp_id NUMBER DEFAULT fnd_global.resp_id,
469 p_project_id NUMBER DEFAULT NULL)
470 RETURN VARCHAR2
471 IS
472 l_tmp NUMBER;
473 l_project_id NUMBER := p_project_id;
474 BEGIN
475 IF p_ci_id > 0 THEN
476 SELECT project_id
477 INTO l_project_id
478 FROM pa_control_items
479 WHERE ci_id = p_ci_id;
480 END IF;
481
482 --Only Project Authorities can highlight the item
483 IF check_proj_auth_ci(l_project_id, p_user_id, p_resp_id) = 'T' THEN
484 RETURN 'T';
485 END IF;
486
487 RETURN 'F';
488 END;
489
490 FUNCTION check_implement_impact_access(
491 p_ci_id NUMBER,
492 p_user_id NUMBER DEFAULT fnd_global.user_id,
493 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
494 RETURN VARCHAR2
495 IS
496 BEGIN
497 RETURN check_change_owner_access(p_ci_id, p_user_id, p_resp_id);
498 END;
499
500
501 FUNCTION check_create_action(
502 p_ci_id IN NUMBER,
503 p_user_id IN NUMBER DEFAULT fnd_global.user_id,
504 p_calling_context IN VARCHAR2 DEFAULT 'UI')-- Bug 5676037. Added the parameter to identify the amg context.
505 return VARCHAR2
506 IS
507 l_result varchar2(1);
508 l_party_id number;
509 l_ci_action_id number;
510 l_status_code varchar2(30);
511 l_status_result varchar2(1);
512 l_project_id NUMBER;
513 l_project_org_id NUMBER;
514
515 Cursor person_with_open_action is
516 select a.ci_action_id
517 from pa_ci_actions a,
518 pa_project_statuses s
519 where a.assigned_to = l_party_id
520 and a.ci_id = p_ci_id
521 and s.project_status_code = a.status_code
522 AND s.status_type = 'CI_ACTION'
523 and s.project_system_status_code = 'CI_ACTION_OPEN';
524
525 BEGIN
526
527 l_party_id := PA_CONTROL_ITEMS_UTILS.GetPartyId(p_user_id);
528
529 if(l_party_id IS NULL) then
530 return 'F';
531 end if;
532
533 --Bug 5676037. Added the if condition to identify the AMG context. From AMG context
534 -- CheckCIActionAllowed should not be called.
535 if(p_calling_context <> 'AMG') then
536 l_status_result := PA_CONTROL_ITEMS_UTILS.CheckCIActionAllowed(null,null,'CONTROL_ITEM_ALLOW_ACTION', p_ci_id);
537 if (l_status_result = 'N') then
538 return 'F';
539 end if;
540 end if;-- if(p_calling_context <> 'AMG') then
541
542 SELECT ci.project_id, ppa.org_id
543 INTO l_project_id, l_project_org_id
544 FROM pa_control_items ci, pa_projects_all ppa
545 WHERE ci.project_id=ppa.project_id
546 AND ci.ci_id = p_ci_id;
547
548 --Control item cannot be updated across OU
549 --Bug#4519391.Modified the if below to use the function pa_moac_utils.check_access.Passing the org_id
550 --for the project in concern to this function. The function would return N if the project is not secured.
551 IF (pa_moac_utils.check_access(l_project_org_id) = 'N') THEN
552 RETURN 'F';
553 END IF;
554
558 END IF;
555 IF check_proj_auth_ci(l_project_id, p_user_id, fnd_global.resp_id) = 'T' OR
556 is_owner(p_ci_id, p_user_id) = 'T' THEN
557 RETURN 'T';
559
560 Open person_with_open_action;
561 fetch person_with_open_action into l_ci_action_id;
562 if (person_with_open_action%FOUND) then
563 close person_with_open_action;
564 return 'T';
565 end if;
566 close person_with_open_action;
567
568 RETURN 'F';
569
570 END check_create_action;
571
572 FUNCTION check_item_owner_project_auth(
573 p_ci_id in NUMBER,
574 p_user_id IN NUMBER DEFAULT fnd_global.user_id)
575 RETURN VARCHAR2
576 IS
577 l_project_id number;
578 l_project_org_id number;
579
580 BEGIN
581 SELECT ci.project_id, ppa.org_id
582 INTO l_project_id, l_project_org_id
583 FROM pa_control_items ci, pa_projects_all ppa
584 WHERE ci.project_id=ppa.project_id
585 AND ci.ci_id = p_ci_id;
586
587 --Control item cannot be updated across OU
588 --Bug#4519391.Modified the if below to use the function pa_moac_utils.check_access.Passing the org_id
589 --for the project in concern to this function. The function would return N if the project is not secured.
590 IF (pa_moac_utils.check_access(l_project_org_id) = 'N') THEN
591 RETURN 'F';
592 END IF;
593
594 IF check_proj_auth_ci(l_project_id, p_user_id, fnd_global.resp_id) = 'T' OR
595 is_owner(p_ci_id, p_user_id) = 'T' THEN
596 RETURN 'T';
597 END IF;
598
599 RETURN 'F';
600 END check_item_owner_project_auth;
601
602
603 FUNCTION check_open_action_assigned_to(
604 p_ci_action_id IN NUMBER,
605 p_user_id IN NUMBER DEFAULT fnd_global.user_id)
606 return varchar2
607 IS
608 l_result varchar2(1);
609 l_party_id number;
610
611 Cursor comment_or_close_action is
612 select 'T'
613 from pa_ci_actions a,
614 pa_project_statuses s
615 where a.ci_action_id = p_ci_action_id
616 and a.assigned_to = l_party_id
617 and s.project_status_code = a.status_code
618 AND s.status_type = 'CI_ACTION'
619 and s.project_system_status_code = 'CI_ACTION_OPEN';
620
621 BEGIN
622 l_party_id := PA_CONTROL_ITEMS_UTILS.GetPartyId(p_user_id);
623
624 if(l_party_id IS NULL) then
625 return 'F';
626 end if;
627
628 Open comment_or_close_action;
629 fetch comment_or_close_action into l_result;
630 if (comment_or_close_action%NOTFOUND) then
631 l_party_id := NULL;
632 close comment_or_close_action;
633 return 'F';
634 end if;
635 close comment_or_close_action;
636
637 return l_result;
638 EXCEPTION
639 WHEN OTHERS THEN -- catch the exceptins here
640 RAISE;
641 END check_open_action_assigned_to;
642
643 FUNCTION check_updatable_comment(
644 p_ci_comment_id IN NUMBER,
645 p_user_id IN NUMBER DEFAULT fnd_global.user_id)
646 return varchar2
647 IS
648 l_ci_id number;
649 l_created_by number;
650
651 Cursor comment_owner is
652 select ci_id, created_by
653 from pa_ci_comments
654 where ci_comment_id = p_ci_comment_id;
655
656 BEGIN
657
658 Open comment_owner;
659 fetch comment_owner into l_ci_id,l_created_by;
660 if (comment_owner%NOTFOUND) then
661 close comment_owner;
662 return 'F';
663 end if;
664 close comment_owner;
665
666 if (l_created_by = p_user_id) then
667 return 'T';
668 end if;
669
670 return check_item_owner_project_auth(l_ci_id, p_user_id);
671
672 EXCEPTION
673 WHEN OTHERS THEN -- catch the exceptins here
674 RAISE;
675 END check_updatable_comment;
676
677 FUNCTION check_create_CI(
678 p_ci_type_id NUMBER,
679 p_project_id NUMBER,
680 p_user_id NUMBER DEFAULT fnd_global.user_id,
681 p_resp_id NUMBER DEFAULT fnd_global.resp_id)
682 RETURN VARCHAR2
683 IS
684 l_tmp NUMBER;
685 l_allow_all_usage_flag VARCHAR2(1);
686 l_access_level NUMBER;
687 l_return_status VARCHAR2(1) := 'S';
688 l_msg_count NUMBER := 0;
689 l_msg_data VARCHAR2(4000) := '';
690 l_resource_id NUMBER := -999;
691 BEGIN
692 IF G_user_id<>p_user_id OR G_resp_id<>p_resp_id THEN
693 G_user_id := p_user_id;
694 G_resp_id := p_resp_id;
695 G_party_id := pa_control_items_utils.getPartyId(p_user_id);
696 G_project_id := p_project_id;
697 G_proj_auth_tab.DELETE;
698 G_view_proj_i_tab.DELETE;
699 G_view_proj_cr_tab.DELETE;
700 G_view_proj_co_tab.DELETE;
701 G_ci_type_tab.DELETE;
702 ELSIF G_project_id<>p_project_id THEN
703 G_project_id := p_project_id;
704 G_ci_type_tab.DELETE;
705 END IF;
706
707 IF G_ci_type_tab.EXISTS(p_ci_type_id) THEN
708 RETURN G_ci_type_tab(p_ci_type_id);
709 END IF;
710
711 SELECT allow_all_usage_flag
712 INTO l_allow_all_usage_flag
713 FROM pa_ci_types_b
714 WHERE ci_type_id = p_ci_type_id;
715
716 IF l_allow_all_usage_flag = 'N' THEN
717 BEGIN
718 SELECT 1
719 INTO l_tmp
720 FROM pa_ci_type_usage citu,
721 pa_projects_all ppa,
722 pa_project_types_all ppt
723 WHERE ppa.project_id = p_project_id
724 AND ppt.project_type = ppa.project_type
725 AND citu.project_type_id = ppt.project_type_id
726 AND citu.ci_type_id = p_ci_type_id
727 AND ROWNUM = 1;
728 EXCEPTION
729 WHEN NO_DATA_FOUND THEN
730 G_ci_type_tab(p_ci_type_id) := 'F';
731 RETURN G_ci_type_tab(p_ci_type_id);
732 END;
733 END IF;
734
735 IF check_proj_auth_ci(p_project_id, p_user_id, p_resp_id) = 'T' THEN
736 G_ci_type_tab(p_ci_type_id) := 'T';
737 RETURN G_ci_type_tab(p_ci_type_id);
738 END IF;
739
740 BEGIN
741 l_resource_id := pa_resource_utils.get_resource_id(NULL, p_user_id);
742
743 SELECT 1
744 INTO l_tmp
745 FROM pa_object_dist_lists l,
746 pa_dist_list_items i,
747 pa_project_parties p
748 WHERE l.object_type = 'PA_CI_TYPES'
749 AND l.object_id = p_ci_type_id
750 AND i.list_id = l.list_id
751 AND p.project_id = p_project_id
752 AND p.resource_id = l_resource_id
753 AND ( i.recipient_type = 'ALL_PROJECT_PARTIES'
754 OR ( i.recipient_type = 'PROJECT_ROLE'
755 AND p.project_role_id = i.recipient_id
756 )
757 )
758 AND ROWNUM = 1;
759
760 G_ci_type_tab(p_ci_type_id) := 'T';
761 RETURN G_ci_type_tab(p_ci_type_id);
762 EXCEPTION
763 WHEN OTHERS THEN
764 NULL;
765 END;
766
767 G_ci_type_tab(p_ci_type_id) := 'F';
768 RETURN G_ci_type_tab(p_ci_type_id);
769 END;
770
771
772 FUNCTION is_to_owner_allowed(
773 p_ci_id NUMBER,
774 p_owner_id NUMBER)
775 RETURN VARCHAR2
776 IS
777 l_tmp NUMBER;
778 l_project_id NUMBER;
779 l_owner_id NUMBER;
780
781 cursor c_owner (p_project_id NUMBER) is
782 select distinct resource_party_id
783 from PA_PROJECT_PARTIES_V
784 where party_type <> 'ORGANIZATION'
785 and project_id = p_project_id
786 and resource_party_id = p_owner_id;
787
788 BEGIN
789
790 SELECT project_id
791 INTO l_project_id
792 FROM pa_control_items
793 WHERE ci_id = p_ci_id;
794
795 open c_owner(l_project_id);
796 fetch c_owner into l_owner_id;
797
798 If c_owner%NOTFOUND then
799 close c_owner;
800 RETURN 'F';
801 End if;
802 close c_owner;
803
804 RETURN 'T';
805
806 EXCEPTION
807 WHEN OTHERS THEN
808 RAISE;
809 END;
810
811 END pa_ci_security_pkg;
[Home] [Help]