1 PACKAGE BODY pa_security AS
2 /* $Header: PAPLSECB.pls 120.1.12010000.3 2009/06/08 12:00:28 paljain ship $ */
3
4
5 /* ----------------------------------------------------------------------
6 ||
7 || Function Name: Initialize
8 ||
9 || Input Parameters:
10 || X_user_id <-- identifier of the application user
11 || X_calling_module <-- hard-coded string that refers to the
12 || module that is calling pa_security
13 || functions
14 ||
15 || Description:
16 || This function is called to initialize package globals that are
17 || referenced by the security functions. Each form that uses
18 || views that are secured MUST execute this procedure during form
19 || startup, otherwise the logic in the security functions built into
20 || secured views is not executed, and the views return all rows.
21 ||
22 || This built-in default (return unsecured data if package globals
23 || are not enabled) enables system administrators using SQL*Plus to
24 || query data to have access all data.
25 ||
26 || In order to secure data in external applications or custom
27 || modules that are not part of core PA code, this procedure must
28 || be called before querying secured views.
29 ||
30 || ---------------------------------------------------------------------
31 */
32
33 PROCEDURE Initialize ( X_user_id IN NUMBER
34 , X_calling_module IN VARCHAR2 )
35 IS
36 v_resp_id NUMBER;
37 v_resp_appl_id NUMBER;
38 BEGIN
39
40 IF ( X_user_id IS NOT NULL ) THEN
41 G_user_id := X_user_id;
42 G_person_id := pa_utils.GetEmpIdFromUser( G_user_id );
43 ELSE
44 G_person_id := NULL;
45 END IF;
46
47 v_resp_id := fnd_global.resp_id;
48 v_resp_appl_id := fnd_global.resp_appl_id;
49
50 IF fnd_profile.value_specific('PA_SUPER_PROJECT',x_user_id, v_resp_id, v_resp_appl_id) = 'Y' THEN
51 G_cross_project_user := 'Y';
52 ELSE
53 G_cross_project_user := 'N';
54 END IF;
55
56 IF fnd_profile.value_specific('PA_SUPER_PROJECT_VIEW',x_user_id, v_resp_id, v_resp_appl_id) = 'Y' THEN
57 G_cross_project_view := 'Y';
58 ELSE
59 G_cross_project_view := 'N';
60 END IF;
61
62 G_module_name := X_calling_module;
63
64 IF ( G_module_name = 'PAXTRAPE.CROSS-PROJECT' ) THEN
65 G_query_allowed := 'Y';
66 G_update_allowed := 'Y';
67 G_view_labor_costs := 'Y';
68 ELSE
69 G_view_labor_costs := NULL;
70 G_query_allowed := NULL;
71 G_update_allowed := NULL;
72 END IF;
73
74 END Initialize;
75
76
77
78 /* ----------------------------------------------------------------------
79 ||
80 || Function Name: allow_query
81 ||
82 || Input Parameters:
83 || X_project_id <-- project identifier
84 ||
85 || Description:
86 || This function determines whether a user has query access to a
87 || particular project.
88 ||
89 || The function first checks if the package variable G_query_allowed
90 || has been initiated with a value. This global variable is set
91 || during the Initialize procedure and is used to override normal
92 || validation in the allow_query function (this enables users who
93 || connect to the database in custom modules or in SQL*Plus to
94 || access all data in secured views without enforcing project-based
95 || security).
96 ||
97 || If the global variable is not set, then this function calls the
98 || client extension API to determine whether or not the user has
99 || query privileges for the given project.
100 ||
101 || The default validation that PA seeds in the client extension for
102 || 'ALLOW_QUERY' is to verify that the user has cross project view/update
103 || access, or has the project authority role in the organizational domain
104 || of this project, or is a key member of the project.
105 ||
106 || There are only two valid values returned from the extension
107 || procedure: 'Y' or 'N'. If the value returned is not one of
108 || these values, then this function returns 'Y'.
109 || -- changed the return value to 'N' for bug 2635016
110 ||
114 FUNCTION allow_query ( X_project_id IN NUMBER) RETURN VARCHAR2
111 || ---------------------------------------------------------------------
112 */
113
115 IS
116 V_allow_query VARCHAR2(1);
117 V_allow_update VARCHAR2(1); /* added for bug 2686117 */
118
119 BEGIN
120
121 /*
122 * Bug# 918652
123 */
124 IF ( G_module_name IS NULL ) THEN
125 RETURN('Y');
126 END IF;
127
128 /* Bug 2686117 Start */
129 IF ( G_update_allowed = 'Y') THEN
130 RETURN( G_update_allowed );
131 END IF;
132
133 pa_security_extn.check_project_access(
134 X_project_id
135 , G_person_id
136 , G_cross_project_user
137 , G_module_name
138 , 'ALLOW_UPDATE'
139 , V_allow_update );
140
141 IF ( V_allow_update = 'Y') THEN
142 RETURN( V_allow_update );
143 /* Bug 2686117 End */
144
145 ELSE
146
147 IF ( G_query_allowed IS NOT NULL ) THEN
148 RETURN( G_query_allowed );
149 END IF;
150
151 pa_security_extn.check_project_access(
152 X_project_id
153 , G_person_id
154 , G_cross_project_user
155 , G_module_name
156 , 'ALLOW_QUERY'
157 , V_allow_query
158 , G_cross_project_view );
159
160 IF ( V_allow_query IN ('Y', 'N') ) THEN
161 RETURN( V_allow_query );
162 ELSE
163 /* changed the return value from 'Y' to N for bug 2635016 */
164 RETURN( 'N' );
165 END IF;
166 END IF;
167 END allow_query;
168
169
170
171
172 /* ----------------------------------------------------------------------
173 ||
174 || Function Name: allow_update
175 ||
176 || Input Parameters:
177 || X_project_id <-- project identifier
178 ||
179 || Description:
180 || This function determines whether a user has update privileges for
181 || a particular project.
182 ||
183 || The structure is identical to the allow_query function. The default
184 || validation that PA seeds in the client extension for 'ALLOW_QUERY' is
185 || to verify that the user has cross project update access, or has the
186 || project authority role in the organizational domain of this project,
187 || or is a key member of the project.
188 ||
189 || ---------------------------------------------------------------------
190 */
191
192 FUNCTION allow_update ( X_project_id IN NUMBER) RETURN VARCHAR2
193 IS
194 V_allow_update VARCHAR2(1);
195
196 BEGIN
197
198 /*
199 * Bug# 918652
200 */
201 IF ( G_module_name IS NULL ) THEN
202 RETURN('Y');
203 END IF;
204
205 IF ( G_update_allowed IS NOT NULL ) THEN
206 RETURN( G_update_allowed );
207 END IF;
208
209 pa_security_extn.check_project_access(
210 X_project_id
211 , G_person_id
212 , G_cross_project_user
213 , G_module_name
214 , 'ALLOW_UPDATE'
215 , V_allow_update );
216
217 IF ( V_allow_update IN ('Y', 'N') ) THEN
218 RETURN( V_allow_update );
219 ELSE
220 /* changed the return value from 'Y' to 'N' for bug 2635016 */
221 RETURN( 'N' );
222 END IF;
223
224 END allow_update;
225
226
227
228 /* ----------------------------------------------------------------------
229 ||
230 || Function Name: view_labor_costs
231 ||
232 || Input Parameters:
233 || X_project_id <-- project identifier
234 ||
235 || Description:
236 || This function determines whether or not labor cost amounts are
237 || displayed when the user queries detail project expenditure items.
238 ||
239 || The structure of the function is identical to the allow_query
240 || function. The default validation that PA seeds in the client
241 || extension for 'VIEW_LABOR_COSTS' is to verify that the user
242 || is a valid key member for the project and that his/her project
243 || role type of this assignment is defined with Query Labor Cost
244 || privilege.
245 ||
246 || ---------------------------------------------------------------------
247 */
248
249 FUNCTION view_labor_costs ( X_project_id IN NUMBER) RETURN VARCHAR2
250 IS
251 V_view_labor_costs VARCHAR2(1);
252
253 BEGIN
254
255 /*
256 * Bug# 918652
257 */
258 IF ( G_module_name IS NULL ) THEN
259 RETURN('Y');
260 END IF;
261
262 IF ( G_view_labor_costs IS NOT NULL ) THEN
263 RETURN( G_view_labor_costs );
264 END IF;
265
266 pa_security_extn.check_project_access(
267 X_project_id
268 , G_person_id
269 , G_cross_project_user
270 , G_module_name
271 , 'VIEW_LABOR_COSTS'
272 , V_view_labor_costs );
273
277 RETURN( 'Y' );
274 IF ( V_view_labor_costs IN ('N', 'Y') ) THEN
275 RETURN( V_view_labor_costs );
276 ELSE
278 END IF;
279
280 END view_labor_costs;
281
282 /* ----------------------------------------------------------------------
283 ||
284 || Function Name: view_labor_costs_new
285 ||
286 || Input Parameters:
287 || X_project_id <-- project identifier
288 ||
289 || Description:
290 || This function determines whether or not labor cost amounts are
291 || displayed when the user queries detail project expenditure items.
292 || Caching logic is implemented for performance
293 ||
294 || The structure of the function is identical to the allow_query
295 || function. The default validation that PA seeds in the client
296 || extension for 'VIEW_LABOR_COSTS' is to verify that the user
297 || is a valid key member for the project and that his/her project
298 || role type of this assignment is defined with Query Labor Cost
299 || privilege.
300 ||
301 || 15-01-2009 anuragar Added this for caching purpose so that
302 || performance doesnt take a hit for bug7192736
303 ||
304 || ---------------------------------------------------------------------
305 */
306
307 FUNCTION view_labor_costs_new ( X_project_id IN NUMBER) RETURN NUMBER
308 IS
309 V_view_labor_costs VARCHAR2(1);
310
311 BEGIN
312
313 --For bug 7192736
314 Initialize (fnd_global.user_id, 'RESOURCE_SUMMARY') ;
315
316 G_proj_id := X_project_id;
317 V_view_labor_costs := view_labor_costs(X_project_id);
318 if(V_view_labor_costs = 'Y')
319 then G_allow_result := 1;
320 else
321 G_allow_result := NULL;
322 end if;
323 return G_allow_result;
324
325 END view_labor_costs_new;
326
327 /* ----------------------------------------------------------------------
328 ||
329 || Function Name: view_labor_costs_new2
330 ||
331 || Input Parameters:
332 || X_project_id <-- project identifier
333 ||
334 || Description:
335 || This function determines whether or not labor cost amounts are
336 || displayed when the user queries detail project expenditure items.
337 || This is used from Task Summary Drilldown pages.
338 ||
339 || The structure of the function is identical to the allow_query
340 || function. The default validation that PA seeds in the client
341 || extension for 'VIEW_LABOR_COSTS' is to verify that the user
342 || is a valid key member for the project and that his/her project
343 || role type of this assignment is defined with Query Labor Cost
344 || privilege.
345 ||
346 || 23-04-2009 paljain Adding this function for Bug fix 8460451.
347 || Please check the bug for more details.
348 || ---------------------------------------------------------------------
349 */
350 FUNCTION view_labor_costs_new2 ( X_project_id IN NUMBER) RETURN VARCHAR2
351 IS
352 V_view_labor_costs VARCHAR2(1);
353 x_return_status VARCHAR2(1);
354 BEGIN
355 Initialize (fnd_global.user_id, 'TASK_SUMMARY') ;
356
357 G_proj_id := X_project_id;
358 V_view_labor_costs := view_labor_costs(X_project_id);
359 if(V_view_labor_costs = 'Y')
360 then x_return_status := 'T';
361 else
362 x_return_status := 'F';
363 end if;
364 return x_return_status;
365
366 END view_labor_costs_new2;
367
368 /* ----------------------------------------------------------------------
369 ||
370 || Procedure Name: set_value
371 ||
372 || Input Parameters:
373 || X_security_level <-- Hard-Coded value to specify which
374 || level of security to set global values
375 || X_value <-- The value to assign to the package
376 || global. Once set, this package global
377 || is returned when the security function
378 || for that level is called instead of
379 || executing the function validation code.
380 ||
381 || Description:
382 || This procedure is called to assign to a given value to the
383 || security package global variable specified. It is used in
384 || forms that drilldown to details for a specific project so that
385 || security validation is only executed once.
386 ||
387 || For example, labor cost security is implemented by embedding the
388 || view_labor_costs function in the view definition that displays
389 || expenditure item details for a project. Since the function is
390 || row dependent (ie, based on project and expenditure type), it is
391 || executed for each record queried. Since the Expenditure Inquiry
392 || form (PROJECT mode) queries expenditure items for a
393 || specific project, the logic in the view_labor_costs function
394 || needs to be executed only once. Therefore, the package global
395 || variable for view labor cost security is initialized when
396 || the project number/name specified in the form is validated. When
400 || ---------------------------------------------------------------------
397 || expenditure items are queried, the security function returns
398 || the global value instead of executing its validation logic.
399 ||
401 */
402
403 PROCEDURE set_value ( X_security_level IN VARCHAR2
404 , X_value IN VARCHAR2 )
405 IS
406 BEGIN
407
408 IF ( X_security_level = 'VIEW_LABOR_COSTS' ) THEN
409 IF ( X_value IS NULL ) THEN
410 G_view_labor_costs := NULL;
411 ELSE
412 G_view_labor_costs := X_value;
413 END IF;
414 ELSIF ( X_security_level = 'ALLOW_UPDATE' ) THEN
415 IF ( X_value IS NULL ) THEN
416 G_update_allowed := NULL;
417 ELSE
418 G_update_allowed := X_value;
419 END IF;
420 ELSIF ( X_security_level = 'ALLOW_QUERY' ) THEN
421 IF ( X_value IS NULL ) THEN
422 G_query_allowed := NULL;
423 ELSE
424 G_query_allowed := X_value;
425 END IF;
426 END IF;
427
428 END set_value;
429
430
431
432 /* ----------------------------------------------------------------------
433 ||
434 || Function Name: check_key_member
435 ||
436 || Input Parameters:
437 || X_person_id <-- Identifier of the person
438 || X_project_id <-- Identifier of the project
439 ||
440 || Return value:
441 || Y <-- Indicates that the person specified is an active key
442 || member for the project specified as of the current date
443 || N <-- The person is not an active key member for the project
444 || NULL <-- Return value if either input parameter is not given
445 ||
446 || ---------------------------------------------------------------------
447 */
448
449 FUNCTION check_key_member ( X_person_id IN NUMBER
450 , X_project_id IN NUMBER ) RETURN VARCHAR2
451 IS
452 dummy NUMBER;
453
454 BEGIN
455 IF ( X_person_id IS NULL OR X_project_id IS NULL ) THEN
456 RETURN( NULL );
457 END IF;
458
459 BEGIN
460 SELECT 1
461 INTO dummy
462 FROM pa_project_players
463 WHERE project_id = X_project_id
464 AND person_id = X_person_id
465 AND TRUNC(sysdate) >= trunc(start_date_active)
466 AND TRUNC(sysdate) <= trunc(NVL(end_date_active, sysdate+1));
467 RETURN( 'Y' );
468
469 EXCEPTION
470 WHEN NO_DATA_FOUND THEN
471 RETURN( 'N' );
472 WHEN TOO_MANY_ROWS THEN
473 RETURN ('Y');
474 WHEN OTHERS THEN
475 RETURN ('N');
476
477 END;
478
479 END check_key_member;
480
481
482
483 /* ----------------------------------------------------------------------
484 ||
485 || Function Name: check_labor_cost_access
486 ||
487 || Input Parameters:
488 || X_person_id <-- Identifier of the person
489 || X_project_id <-- Identifier of the project
490 ||
491 || Return value:
492 || Y <-- Indicates that the person specified is a key member
493 || with a project role type that allows query of labor costs
494 || for the specified project
495 || N <-- The person is not an active key member for the project or
496 || is an active key member but with a project role type that
497 || does not permit query of labor cost amounts
498 || NULL <-- Return value if either input parameter is not given
499 ||
500 || ---------------------------------------------------------------------
501 */
502
503 FUNCTION check_labor_cost_access ( X_person_id IN NUMBER
504 , X_project_id IN NUMBER )
505 RETURN VARCHAR2
506 IS
507 dummy NUMBER;
508 BEGIN
509 IF ( X_person_id IS NULL OR X_project_id IS NULL ) THEN
510 RETURN( NULL );
511 END IF;
512
513 BEGIN
514 SELECT 1
515 INTO dummy
516 --FROM pa_project_role_types rt --bug 4004821
517 FROM pa_project_role_types_b rt
518 , pa_project_players pp
519 , pa_role_controls rc -- Added for bug 3058844
520 WHERE rt.project_role_type = pp.project_role_type
521 /* Below code added for bug 3058844 */
522 AND rt.project_role_id = rc.project_role_id
523 AND rc.role_control_code = 'ALLOW_QUERY_LABOR_COST'
524 /* Code addition ends for bug 3058844 */
525 AND TRUNC(sysdate) >= trunc(pp.start_date_active)
526 AND TRUNC(sysdate) <= trunc(NVL(pp.end_date_active, sysdate+1))
527 AND pp.person_id = X_person_id
528 AND pp.project_id = X_project_id;
529
530 RETURN( 'Y' );
531
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 RETURN( 'N' );
535 WHEN TOO_MANY_ROWS THEN
536 RETURN ('Y');
537 WHEN OTHERS THEN
538 RETURN ('N');
539
540 END;
541
542 END check_labor_cost_access;
543
544
545 /* ----------------------------------------------------------------------
546 ||
547 || Function Name: check_project_authority
548 ||
549 || Input Parameters:
550 || X_person_id <-- Identifier of the person
551 || X_project_id <-- Identifier of the project
552 ||
556 || N <-- The person is not a project authority, or is a project authority
553 || Return value:
554 || Y <-- Indicates that the person specified has the project authority
555 || role that permits access to the specified project
557 || for an organization that does not encompass the specified project
558 || NULL <-- Return value if either input parameter is not given
559 ||
560 || ---------------------------------------------------------------------
561 */
562
563 FUNCTION check_project_authority ( X_person_id IN NUMBER,
564 X_project_id IN NUMBER ) RETURN VARCHAR2
565 IS
566 CURSOR c1 IS
567 SELECT '1'
568 FROM pa_projects_all ppa,
569 pa_project_role_types_b ppr, -- Added for bug 3224170
570 fnd_grants fg,
571 fnd_objects fo
572 WHERE
573 --fg.grantee_key = 'PER:'||X_person_id and /* commenting this line for 11.5.10 security changes */
574 fg.grantee_key = PA_SECURITY_PVT.get_grantee_key( 'PERSON', X_person_id, 'N') and
575 /* replaced the above line with this call. The last paramater will assert that the function will not write to database. */
576 fg.grantee_type = 'USER' and
577 fg.menu_id = ppr.menu_id and -- Added for bug 3224170
578 ppr.project_role_id = 3 and -- Added for bug 3224170
579 ppa.project_id = X_project_id and
580 to_char(ppa.carrying_out_organization_id) = fg.instance_pk1_value and -- bug 2777621
581 fg.object_id = fo.object_id and
582 fo.obj_name = 'ORGANIZATION' and
583 fg.instance_type = 'INSTANCE' and
584 TRUNC(sysdate) >= trunc(fg.start_date) and
585 trunc(sysdate) <= trunc(NVL(fg.end_date, sysdate+1));
586
587 v_dummy VARCHAR2(1);
588 BEGIN
589 IF( X_person_id IS NULL or X_project_id IS NULL ) THEN
590 RETURN( NULL );
591 END IF;
592
593 open c1;
594 fetch c1 into v_dummy;
595 IF c1%notfound THEN
596 close c1;
597 RETURN( 'N' );
598 ELSE
599 close c1;
600 RETURN( 'Y' );
601 END IF;
602 EXCEPTION
603 WHEN OTHERS THEN
604 RETURN( 'N' );
605 END check_project_authority;
606
607 /* ----------------------------------------------------------------------
608 ||
609 || Function Name: check_key_member_no_dates
610 ||
611 || Input Parameters:
612 || X_person_id <-- Identifier of the person
613 || X_project_id <-- Identifier of the project
614 ||
615 || Return value:
616 || Y <-- Indicates that the person specified is a
617 || member for the project.
618 || N <-- The person is not a member for the project.
619 || NULL <-- Return value if either input parameter is not given
620 ||
621 || ---------------------------------------------------------------------
622 */
623
624 FUNCTION check_key_member_no_dates ( X_person_id IN NUMBER
625 , X_project_id IN NUMBER ) RETURN VARCHAR2
626 IS
627 dummy NUMBER;
628
629 BEGIN
630 IF ( X_person_id IS NULL OR X_project_id IS NULL ) THEN
631 RETURN( NULL );
632 END IF;
633
634 BEGIN
635 SELECT 1
636 INTO dummy
637 FROM pa_project_players
638 WHERE project_id = X_project_id
639 AND person_id = X_person_id;
640
641 RETURN( 'Y' );
642
643 EXCEPTION
644 WHEN NO_DATA_FOUND THEN
645 RETURN( 'N' );
646 WHEN TOO_MANY_ROWS THEN
647 RETURN ('Y');
648 WHEN OTHERS THEN
649 RETURN ('N');
650 END;
651
652 END check_key_member_no_dates;
653
654 /* ----------------------------------------------------------------------
655 ||
656 || Function Name: check_forecast_authority
657 ||
658 || Input Parameters:
659 || p_person_id <-- Identifier of the person
660 || p_project_id <-- Identifier of the project
661 ||
662 || Return value:
663 || Y <-- Indicates that the person specified has the forecast authority
664 || role that permits access to the specified project
665 || N <-- The person is not a forecast authority
666 || NULL <-- Return value if either input parameter is not given
667 ||
668 || ---------------------------------------------------------------------
669 */
670
671 /*Enhancement 6519194 - commenting out this function*/
672 /*FUNCTION check_forecast_authority ( X_person_id IN NUMBER,
673 X_project_id IN NUMBER ) RETURN VARCHAR2
674 IS
675 CURSOR c1 IS
676 SELECT '1'
677 FROM pa_projects_all ppa,
678 fnd_grants fg,
679 fnd_objects fo,
680 fnd_menus fm
681 WHERE */
682 --fg.grantee_key = 'PER:'||X_person_id and /* commenting this line for 11.5.10 security changes */
683 /* fg.grantee_key = PA_SECURITY_PVT.get_grantee_key( 'PERSON', X_person_id, 'N') and*/
684 /* replaced the above line with this call. The last paramater will assert that the function will not write to database. */
685 /* fg.grantee_type = 'USER' and
686 ppa.project_id = X_project_id and
687 to_char(ppa.carrying_out_organization_id) = fg.instance_pk1_value and -- bug2777621
688 fg.object_id = fo.object_id and
689 fm.menu_name = 'PA_PRM_FCST_AUTH' and
690 fg.menu_id = fm.menu_id and
691 fo.obj_name = 'ORGANIZATION' and
692 fg.instance_type = 'INSTANCE' and
693 TRUNC(sysdate) >= trunc(fg.start_date) and
694 trunc(sysdate) <= trunc(NVL(fg.end_date, sysdate+1));
695
696 v_dummy VARCHAR2(1);
697 BEGIN
698
699 IF( X_person_id IS NULL or X_project_id IS NULL ) THEN
700 RETURN( NULL );
701 END IF;
702
703 open c1;
704 fetch c1 into v_dummy;
705 IF c1%notfound THEN
706 close c1;
707 RETURN( 'N' );
708 ELSE
709 close c1;
710 RETURN( 'Y' );
711 END IF;
712
713 EXCEPTION
714 WHEN OTHERS THEN
715 RETURN( 'N' );
716
717 END check_forecast_authority;
718 */
719 END pa_security;
[Home] [Help]