1 PACKAGE BODY HR_SECURITY AS
2 /* $Header: hrscsec.pkb 120.9.12020000.8 2013/02/21 09:33:54 karthmoh ship $ */
3 --
4 -- PRIVATE FUNCTIONS AND PROCEDURES
5 --
6 --
7 -- This is the security profile id for the view security
8 -- profile which is associated with the setup business group
9 --
10 VIEW_ALL_PROFILE CONSTANT NUMBER := 0;
11 --
12 --
13 -- 1999-07-19 Bug 775399. A value of -1 for the ORG_ID
14 -- component of the 'client_info' string indicates that
15 -- the user connected to apps but should not see any rows
16 --
17 VIEW_NO_ROWS_ORG_ID CONSTANT NUMBER := -1;
18 --
19 g_apps_schema_mode VARCHAR2(3);
20 g_user_id NUMBER;
21 g_resp_id NUMBER;
22 g_resp_appl_id NUMBER;
23 g_security_group_id NUMBER;
24 g_person_id NUMBER;
25 g_context per_security_profiles%ROWTYPE;
26 g_view_no_rows BOOLEAN;
27 g_effective_date DATE := sysdate;
28 g_is_rtm boolean := false;
29 type g_vac_list_tbl_typ is table of number index by binary_integer;
30 g_vac_list_tbl g_vac_list_tbl_typ;
31 TYPE per_list is table of boolean index by binary_integer;
32 g_person_list per_list;
33
34 --
35 -- DK 2001-11-17
36 -- 2086208. Cache the value of ICX_SEC.G_SESSION_ID so that the
37 -- person list can be rebuilt on a change of login session even if
38 -- the user,resp,sec group remain the same. An alternative scheme is
39 -- to track session switching based on the value of
40 -- FND_GLOBAL.SESSION_CONTEXT. This would cause the person list to
41 -- be rebuilt on each call to FND_GLOBAL.APPS_INITIALIZE.
42 --
43 g_icx_session_id NUMBER := 0 ;
44
45
46 --
47 -----------------------------------------------------------------------
48 -----------------------------------------------------------------------
49 -- begin BIS/discoverer section
50 -----------------------------------------------------------------------
51 --
52 g_org_id_initialized BOOLEAN := FALSE;
53 g_org_id VARCHAR2(15);
54 g_mo_context per_security_profiles%ROWTYPE;
55 g_mo_person_id NUMBER;
56 g_mo_org_sec_known BOOLEAN;
57 --
58 -----------------------------------------------------------------------
59 -- end BIS/discoverer section
60 -----------------------------------------------------------------------
61 --
62 -----------------------------------------------------------------------
63 --< raise_error >------------------------------------------------------
64 -----------------------------------------------------------------------
65 --
66 -- Description:
67 -- Raise an internal error. Not translated.
68 --
69 PROCEDURE raise_error
70 (p_message in varchar2
71 )
72 IS
73 BEGIN
74 raise_application_error(-20001, p_message);
75 END raise_error;
76 --
77 -- PUBLIC FUNCTIONS AND PROCEDURES
78 --
79 -----------------------------------------------------------------------
80 --< get_hr_security_context >------------------------------------------
81 -----------------------------------------------------------------------
82 --
83 FUNCTION get_hr_security_context
84 RETURN NUMBER
85 IS
86 BEGIN
87 return (hr_signon.g_hr_security_profile.security_profile_id);
88 END get_hr_security_context;
89
90 -----------------------------------------------------------------------
91 --< is_user_rtm >------------------------------------------
92 -----------------------------------------------------------------------
93 --
94 FUNCTION is_user_rtm
95 RETURN boolean
96 IS
97 BEGIN
98 return (g_is_rtm);
99 END is_user_rtm;
100
101 --
102 --
103 -----------------------------------------------------------------------
104 --< get_security_profile >---------------------------------------------
105 -----------------------------------------------------------------------
106 --
107 FUNCTION get_security_profile
108 RETURN NUMBER
109 IS
110 --
111 l_security_profile_id number := -1;
112 --
113 CURSOR get_reporting_id
114 IS
115 SELECT security_profile_id
116 FROM per_security_profiles
117 WHERE reporting_oracle_username = USER;
118 --
119 BEGIN
120 --
121 -- Check the schema mode. APPS schemas have one of the following
122 -- 'U' Universal (APPS schemas)
123 -- 'M' Multi-lingual
124 -- 'K' Multi-currency
125 --
126 -- In this case check the security profile
127 --
128 IF ( g_apps_schema_mode = 'Y' ) THEN
129 --
130 -- If the security context is not set then use the
131 -- seeded view all security profile.
132 --
133 l_security_profile_id := NVL(get_hr_security_context,VIEW_ALL_PROFILE);
134 ELSE
135 --
136 -- If the current schema is attached to a security profile
137 -- then return that otherwise we are in a custom schema and
138 -- so the view_all profile can be returned.
139 --
140 OPEN get_reporting_id;
141 FETCH get_reporting_id INTO l_security_profile_id;
142 --
143 IF get_reporting_id%NOTFOUND THEN
144 CLOSE get_reporting_id;
145 l_security_profile_id := VIEW_ALL_PROFILE;
146 END IF;
147 END IF;
148 --
149 RETURN (l_security_profile_id);
150 END get_security_profile;
151 --
152 -----------------------------------------------------------------------
153 --< get_person_id >------------------------------------------------
154 -----------------------------------------------------------------------
155 --
156 function get_person_id return number is
157 --
158 cursor get_sec_person_id(p_security_profile_id number) is
159 select named_person_id
160 from per_security_profiles
161 where security_profile_id=p_security_profile_id;
162 --
163 cursor get_user_person_id(p_user_id number) is
164 select employee_id
165 from fnd_user
166 where user_id=p_user_id;
167 --
168 l_person_id number;
169 --
170 begin
171 --
172 open get_sec_person_id(get_security_profile);
173 fetch get_sec_person_id into l_person_id;
174 close get_sec_person_id;
175 if l_person_id is null then
176 open get_user_person_id(g_user_id);
177 fetch get_user_person_id into l_person_id;
178 close get_user_person_id;
179 end if;
180 --
181 return l_person_id;
182 --
183 end get_person_id;
184 --
185 --
186 -----------------------------------------------------------------------
187 --< Sync_Person_Cache >------------------------------------------------
188 -----------------------------------------------------------------------
189 --
190 -- Description:
191 --
192 -- For the mean-time, two sets of person cache are maintained.
193 -- This is not ideal, but has been done to prevent regressions, both
194 -- functional and performance, using the evaluate_access method;
195 -- g_person_list will be obsoleted going forward.
196 -- Here people in hr_security_internal.g_per_tbl are added to
197 -- g_person_list for backwards compatibility.
198 --
199 PROCEDURE sync_person_cache
200 IS
201
202 i NUMBER;
203
204 BEGIN
205
206 --
207 -- Sync the two sets of cache.
208 --
209 IF hr_security_internal.g_per_tbl.COUNT > 0 THEN
210 i := hr_security_internal.g_per_tbl.FIRST;
211 WHILE i <= hr_security_internal.g_per_tbl.LAST LOOP
212 g_person_list(i) := TRUE;
213 i := hr_security_internal.g_per_tbl.NEXT(i);
214 END LOOP;
215 END IF;
216
217 END sync_person_cache;
218 --
219 -----------------------------------------------------------------------
220 --< Initialise_Globals >-----------------------------------------------
221 -----------------------------------------------------------------------
222 --
223 -- Description:
224 -- This procedure will initialise all the package globals. It is
225 -- called when any procedure in the package is first run, and also
226 -- whenever the user switches responsibility (and hence calls
227 -- the get_security_profile_id function which then in turns calls
228 -- this procedure).
229 --
230 PROCEDURE Initialise_Globals
231 IS
232 --
233 -- Retrieves the mode of the current schema together with
234 -- the ORG_ID part of CLIENT_INFO for Bug 775399
235 --
236 -- 1999-07-19
237 -- 1. Change SUBSTR to SUBSTRB
238 -- 2. First column now returns 'Y' if the schema is of apps type
239 -- 'U' Universal (APPS schemas)
240 -- 'M' Multi-lingual
241 -- 'K' Multi-currency
242 --
243 CURSOR csr_get_schema_mode
244 IS
245 SELECT DECODE(READ_ONLY_FLAG,'U', 'Y'
246 ,'M', 'Y'
247 ,'K', 'Y'
248 ,'N') schema_mode,
249 DECODE(SUBSTRB(USERENV('CLIENT_INFO'), 1, 1),' ', NULL,
250 SUBSTRB(USERENV('CLIENT_INFO'),1, 10))
251 FROM FND_ORACLE_USERID
252 WHERE ORACLE_USERNAME = user;
253 --
254 -- Get the row from per_security_profiles corresponding to the
255 -- security profile for the session
256 --
257 CURSOR csr_get_sec_prf(p_security_profile_id number)
258 IS
259 SELECT *
260 FROM per_security_profiles
261 WHERE security_profile_id = p_security_profile_id;
262
263 --
264 -- Get if the person is an RTM
265 --
266
267 CURSOR csr_get_if_rtm(p_person_id number)
268 IS
269 SELECT vacancy_id
270 FROM irc_rec_team_members
271 WHERE person_id = p_person_id;
272
273 --
274 -- Gets the person stored against a given user.
275 --
276 CURSOR csr_get_person
277 (p_user_id IN NUMBER) IS
278 SELECT fndu.employee_id
279 FROM fnd_user fndu
280 WHERE p_user_id IS NOT NULL
281 AND fndu.user_id = p_user_id;
282
283 --
284 -- Cursors to build security cache.
285 -- Bug 3346940.
286 -- Added the "granted_user_id is null" clause to prevent
287 -- this from picking up static user lists.
288 --
289 cursor get_people(p_security_profile_id number) is
290 select person_id
291 from per_person_list
292 where security_profile_id=p_security_profile_id
293 and granted_user_id is null;
294
295 --
296 -- Bug 3584578.
297 -- All supervisor security is now evaluated in
298 -- hr_security_internal.evaluate_access.
299 --
300 /* cursor get_super_people(p_top_person_id number,
301 p_max_levels number) is
302 select asg.person_id
303 from (select a.person_id,
304 a.supervisor_id
305 from per_all_assignments_f a
306 where trunc(sysdate) between a.effective_start_date
307 and a.effective_end_date
308 and assignment_type <> 'B') asg
309 connect by asg.supervisor_id = prior asg.person_id
310 and Level<=nvl(p_max_levels,Level)+1
311 start with asg.person_id=p_top_person_id;
312 --
313 cursor get_super_people_ppl(p_security_profile_id number,
314 p_top_person_id number,
315 p_max_levels number) is
316 select asg.person_id
317 from (select a.person_id,
318 a.supervisor_id
319 from per_all_assignments_f a
320 where trunc(sysdate) between a.effective_start_date
321 and a.effective_end_date
322 and assignment_type <> 'B') asg
323 where exists (select null
324 from per_person_list ppl
325 where ppl.security_profile_id=p_security_profile_id
326 and ppl.person_id=asg.person_id)
327 connect by asg.supervisor_id = prior asg.person_id
328 and Level<=nvl(p_max_levels,Level)+1
329 start with asg.person_id=p_top_person_id ;
330 --
331 cursor get_super_people_primary(p_top_person_id number,
332 p_max_levels number) is
333 select asg.person_id
334 from (select a.person_id,
335 a.supervisor_id
336 from per_all_assignments_f a
337 where trunc(sysdate) between a.effective_start_date
338 and a.effective_end_date
339 and assignment_type <> 'B'
340 and a.primary_flag='Y') asg
341 connect by asg.supervisor_id = prior person_id
342 and Level<=nvl(p_max_levels,Level)+1
343 start with asg.person_id=p_top_person_id ;
344 --
345 cursor get_super_people_primary_ppl(p_security_profile_id number,
346 p_top_person_id number,
347 p_max_levels number) is
348 select asg.person_id
349 from (select a.person_id,
350 a.supervisor_id
351 from per_all_assignments_f a
352 where trunc(sysdate) between a.effective_start_date
353 and a.effective_end_date
354 and assignment_type <> 'B'
355 and a.primary_flag='Y' ) asg
356 where exists (select null
357 from per_person_list ppl
358 where ppl.security_profile_id=p_security_profile_id
359 and ppl.person_id=asg.person_id)
360 connect by asg.supervisor_id = prior asg.person_id
361 and Level<=nvl(p_max_levels,Level)+1
362 start with asg.person_id=p_top_person_id ;
363 */
364
365 l_security_profile_id number;
366 l_what_to_evaluate number;
367 l_use_static_lists boolean;
368 l_vacancy_id number;
369 --
370 BEGIN
371 --
372 -- DK 2001-11-17
373 -- 2086208. Save the ICX session id when the person list is created.
374 -- check_person_list calls initialize_globals if the value saved is
375 -- different to the current value of icx_sec.g_session_id
376 g_icx_session_id := icx_sec.g_session_id;
377 --
378 -- Get the schema mode and org_id from client_info
379 --
380 OPEN csr_get_schema_mode;
381 FETCH csr_get_schema_mode into g_apps_schema_mode, g_org_id;
382 CLOSE csr_get_schema_mode;
383 --
384 l_security_profile_id:=hr_security.get_security_profile;
385 g_user_id:=fnd_global.user_id;
386 g_resp_id:=fnd_global.resp_id;
387 g_resp_appl_id:=fnd_global.resp_appl_id;
388 g_security_group_id:=fnd_global.security_group_id;
389
390 -- g_person_id:=get_person_id; -- Bug 2807573 see below
391 -- g_person_list.delete; --6012095(forward port of 5985232)
392
393 --
394 IF (RTRIM(g_org_id) = TO_CHAR(VIEW_NO_ROWS_ORG_ID) AND g_apps_schema_mode = 'Y' ) THEN
395 g_view_no_rows := TRUE;
396 g_context := null;
397 ELSE
398 g_view_no_rows := FALSE;
399
400 --
401 -- Get the security profile information
402 --
403 OPEN csr_get_sec_prf(l_security_profile_id);
404 FETCH csr_get_sec_prf INTO g_context;
405 --
406 IF csr_get_sec_prf%NOTFOUND THEN
407 CLOSE csr_get_sec_prf;
408 raise_error('HR SECURITY ERROR : INVALID PROFILE VALUE '||l_security_profile_id);
409 ELSE
410 CLOSE csr_get_sec_prf;
411 END IF;
412
413 --
414 -- Bug 2807573 DK 17-FEB-2003
415 --
416 -- Initialization of g_person_id moved to avoid potentially
417 -- unnecessary query.
418 IF ( g_context.view_all_flag = 'N' ) THEN
419 g_person_id:=get_person_id;
420 -- bug 8335005
421 open csr_get_if_rtm(g_person_id);
422 fetch csr_get_if_rtm into l_vacancy_id;
423 IF csr_get_if_rtm%notfound THEN
424 g_is_rtm := FALSE;
425 ELSE
426 g_is_rtm := TRUE;
427 END IF;
428 END IF;
429
430 --
431 -- Reset this flag to false whenever security is re-initialised.
432 -- This tells show_bis_record to re-evaluate organization security
433 -- permissions using g_mo_context instead of g_context.
434 --
435 g_mo_org_sec_known := FALSE;
436
437 --
438 -- Get the security profile as set in the profile option
439 -- 'MO:Security Profile'.
440 --
441 OPEN csr_get_sec_prf(p_security_profile_id =>
442 to_number(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL')));
443 FETCH csr_get_sec_prf INTO g_mo_context;
444 CLOSE csr_get_sec_prf;
445
446 --
447 -- If MO: Security Profile is not set, use HR: Security Profile.
448 --
449 IF g_mo_context.security_profile_id IS NULL THEN
450 g_mo_context := g_context;
451 g_mo_person_id := g_person_id;
452 ELSE
453 --
454 -- Fetch the person from the MO profile.
455 --
456 IF (NVL(g_mo_context.view_all_flag, 'Y') = 'N') THEN
457 IF g_mo_context.named_person_id IS NOT NULL THEN
458 g_mo_person_id := g_mo_context.named_person_id;
459 ELSE
460 OPEN csr_get_person(g_user_id);
461 FETCH csr_get_person INTO g_mo_person_id;
462 CLOSE csr_get_person;
463 END IF;
464 END IF;
465 END IF;
466
467 --
468 -- Bug 3584578.
469 -- All supervisor security is now evaluated in
470 -- hr_security_internal.evaluate_access.
471
472 /*
473 --
474 -- look to see if we are using supervisor hierarchies
475
476 -- DKERR 5/2002
477 -- Performance fixes for Bug 2374967 made to
478 --
479 -- get_super_people
480 -- get_super_people_ppl
481 -- get_super_people_primary
482 -- get_super_people_primary_ppl
483 --
484 -- See also 2041460
485 --
486 -- In each cursor we construct the list of all assignments as of today
487 -- before we apply the hierarchical query condition ie
488 -- "supervisor = prior person_id". This performs much better than the
489 -- original version which applied the date restriction to assignment rows
490 -- as part of the query condition. However these queries still require a
491 -- of high amount of i/o and performance will depend on how much of
492 -- the assignment table is already in the buffer cache.
493 -- For this reason and also the amount of session memory required to
494 -- cache potentially tens of thousands of person ids make this a less
495 -- scaleable solution than building the cache on a demand basis from
496 -- from a fixed number - possibly 3 levels.
497 --
498 -- Bug 3346940.
499 -- The "supervisor_flag = 'Y'" excludes assignment-based supervisor
500 -- hierarchies. These are built separately in evaluate_access.
501 -- Person-based hierarchies are build below, but only if there are
502 -- not any user-based org or user-based pos restrictions.
503 -- If there are user-based org or user-based pos restrictions, the
504 -- person-based hierarchies are built in evaluate_access, not here.
505 --
506 if g_context.restrict_by_supervisor_flag = 'Y' then
507 if g_context.view_all_organizations_flag='Y'
508 and g_context.view_all_positions_flag='Y'
509 and g_context.view_all_payrolls_flag='Y'
510 and g_context.custom_restriction_flag='N' then
511 --
512 -- we are only restricting by supervisor so do not
513 -- join to per_person_list
514 --
515 if g_context.exclude_secondary_asgs_flag='Y' then
516 --
517 -- find all of the people who are in the supervisor hierarchy of
518 -- primary assignments
519 --
520 for per_rec in get_super_people_primary(g_person_id
521 ,g_context.supervisor_levels)
522 loop
523 g_person_list(per_rec.person_id):=TRUE;
524 end loop;
525 else
526 -- find all of the people who are in the supervisor hierarchy of
527 -- any assignments
528 for per_rec in get_super_people(g_person_id
529 ,g_context.supervisor_levels) loop
530 g_person_list(per_rec.person_id):=TRUE;
531 end loop;
532 end if;
533
534 elsif NVL(g_context.top_organization_method, 'S') <> 'U'
535 and NVL(g_context.top_position_method, 'S') <> 'U'
536 and NVL(g_context.custom_restriction_flag, 'N') <> 'U'
537 and NVL(g_context.restrict_on_individual_asg, 'N') <> 'Y' then
538 --
539 -- Bug 3346940.
540 -- Only evaluate person-based supervisor security if user-based
541 -- org, pos and custom security is not in use and (bug 3507431)
542 -- the security is not on an individual assignment level.
543 --
544 -- we are also restricting by another thing, so join to
545 -- per_person_list
546 --
547 if g_context.exclude_secondary_asgs_flag='Y' then
548 --
549 -- find all of the people who are in the supervisor hierarchy of
550 -- primary assignments as well as the other security restrictions
551 --
552 for per_rec in get_super_people_primary_ppl(l_security_profile_id
553 ,g_person_id
554 ,g_context.supervisor_levels)
555 loop
556 g_person_list(per_rec.person_id):=TRUE;
557 end loop;
558
559 else
560 --
561 -- find all of the people who are in the supervisor hierarchy of
562 -- any assignments as well as the other security restrictions
563 --
564 for per_rec in get_super_people_ppl(l_security_profile_id
565 ,g_person_id
566 ,g_context.supervisor_levels) loop
567 g_person_list(per_rec.person_id):=TRUE;
568 end loop;
569 end if;
570 end if;
571 end if;
572
573 --
574 -- The static per_person_list is now cached during evaluate_access
575 -- so this code can be commented out. Although this is cached
576 -- into a separate table and synched up at the moment, it is
577 -- expected that g_person_list can be obsoleted and replaced by
578 -- g_per_tbl.
579 --
580 else
581 --
582 -- Bug 2807573 DK 17-FEB-2003
583 --
584 -- For a view all security profile we don't need to get
585 -- the per_person_list. It should be empty for such a profile
586 -- but checking involves a range scan and hence unnecessary i/o.
587 --
588 IF ( g_context.view_all_flag = 'N' ) THEN
589
590 -- we are not restricting by hierarchy, so
591 -- find all of the people who are in the security profile
592 for per_rec in get_people(l_security_profile_id) loop
593 g_person_list(per_rec.person_id):=TRUE;
594 end loop;
595 END IF ;
596
597 end if;
598 */
599 --
600 -- The below call to evaluate_access determines all the security
601 -- permissions for the logged on user and caches lists of their
602 -- orgs, positions, people, etc.
603 --
604 -- Where user-based security or assignment-level security is used,
605 -- the security is dynamically assessed, otherwise it picks up
606 -- the permissions from per_person_list.
607 --
608 -- hr_security_internal.evaluate_access keeps a separate person
609 -- cache at the moment, although it is expected that g_person_list
610 -- can be completely replaced by g_per_tbl in the near future
611 -- (this exercise requires references to g_person_list to be
612 -- replaced by g_per_tbl).
613 --
614 if (g_context.view_all_flag = 'N' ) then
615 --
616 -- Fetch the parameters that allow different modelling options.
617 -- THESE PARAMETERS ARE FOR DEVELOPMENT USE ONLY AT PRESENT.
618 --
619 g_effective_date := hr_security_internal.get_effective_date;
620 l_what_to_evaluate := hr_security_internal.get_what_to_evaluate;
621 l_use_static_lists := hr_security_internal.get_use_static_lists;
622
623 hr_security_internal.evaluate_access
624 (p_person_id => g_person_id
625 ,p_user_id => g_user_id
626 ,p_effective_date => g_effective_date
627 ,p_sec_prof_rec => g_context
628 ,p_what_to_evaluate => l_what_to_evaluate
629 ,p_use_static_lists => l_use_static_lists);
630
631 --
632 -- The two sets of person cache are synched.
633 --
634 --sync_person_cache; --6012095(Forward Port of 5985232)
635
636 end if;
637
638 /* --
639 -- GRANTED USERS ARE NOW ADDED IN EVALUATE_ACCESS
640 --
641 -- add granted users if using a restricted profile.
642 -- A user can have been granted access to a person but still be using
643 -- a view all responsiblity. In which case there will be rows in
644 -- PER_PERSON_LIST but as this is a view all profile we can ignore them.
645 --
646 -- 2807573 21-FEB-2003
647 -- Only deal with grant access when using a restricted profile.
648 --
649 if (g_context.view_all_flag = 'N' ) then
650 if g_context.allow_granted_users_flag='Y' then
651 for per_rec in get_granted_users(g_user_id) loop
652 g_person_list(per_rec.person_id):=TRUE;
653 end loop;
654 end if;
655 end if;
656 */
657
658 -- remove the current user if required.
659 if g_context.exclude_person_flag='Y' then
660 --g_person_list.delete(g_person_id);--6012095 (Forward port of 5985232)
661 hr_security_internal.g_per_tbl.delete(g_person_id);
662 end if;
663 --
664
665 END IF;
666
667 END Initialise_Globals;
668 --
669 -----------------------------------------------------------------------
670 --< view_all >---------------------------------------------------------
671 -----------------------------------------------------------------------
672 --
673 FUNCTION view_all return varchar2
674 IS
675 BEGIN
676
677 --
678 -- 2876315
679 --
680 if ( hr_signon.session_context <> fnd_global.session_context )
681 then
682 hr_signon.initialize_hr_security;
683 initialise_globals;
684 end if;
685
686
687 RETURN (NVL(hr_signon.g_hr_security_profile.view_all_flag
688 ,g_context.view_all_flag));
689
690
691 END view_all;
692 --
693 -----------------------------------------------------------------------
694 --< no_restrictions >--------------------------------------------------
695 -----------------------------------------------------------------------
696 --
697 FUNCTION no_restrictions return boolean
698 IS
699 BEGIN
700
701 --
702 -- Bug 2638726
703 -- DK 18-NOV-2002 Modified to use hr_signon cache
704 --
705
706 if (NVL(hr_signon.g_hr_security_profile.restrict_by_supervisor_flag,
707 g_context.restrict_by_supervisor_flag) = 'N'
708 and NVL(hr_signon.g_hr_security_profile.view_all_organizations_flag,
709 g_context.view_all_organizations_flag) = 'Y'
710 and NVL(hr_signon.g_hr_security_profile.view_all_positions_flag,
711 g_context.view_all_positions_flag) = 'Y'
712 and NVL(hr_signon.g_hr_security_profile.view_all_payrolls_flag,
713 g_context.view_all_payrolls_flag) = 'Y'
714 and NVL(hr_signon.g_hr_security_profile.custom_restriction_flag,
715 g_context.custom_restriction_flag) = 'N' ) then
716 RETURN true;
717 else
718 RETURN false;
719 end if;
720 END no_restrictions;
721 -----------------------------------------------------------------------
722 --< view_all_applicants >----------------------------------------------
723 -----------------------------------------------------------------------
724 --
725 FUNCTION view_all_applicants
726 RETURN BOOLEAN
727 IS
728 BEGIN
729 if (NVL(hr_signon.g_hr_security_profile.view_all_applicants_flag
730 ,g_context.view_all_applicants_flag) = 'Y') then
731 RETURN TRUE;
732 else
733 return FALSE;
734 end if;
735 END view_all_applicants;
736 --
737
738 -----------------------------------------------------------------------
739 --< view_all_cwk >-----------------------------------------------------
740 -----------------------------------------------------------------------
741 --
742 FUNCTION view_all_cwk
743 RETURN BOOLEAN
744 IS
745 BEGIN
746 if NVL(hr_signon.g_hr_security_profile.view_all_cwk_flag
747 ,g_context.view_all_cwk_flag) = 'Y' then
748 return true;
749 else
750 return false;
751 end if;
752 END view_all_cwk;
753 --
754 -----------------------------------------------------------------------
755 --< view_all_contacts >------------------------------------------------
756 -----------------------------------------------------------------------
757 --
758 FUNCTION view_all_contacts
759 RETURN BOOLEAN
760 IS
761 BEGIN
762 if NVL(hr_signon.g_hr_security_profile.view_all_contacts_flag
763 ,g_context.view_all_contacts_flag) = 'Y' then
764 return true;
765 else
766 return false;
767 end if;
768 END view_all_contacts;
769 --
770 -----------------------------------------------------------------------
771 --< view_all_candidates >----------------------------------------------
772 -----------------------------------------------------------------------
773 --
774 function view_all_candidates return boolean is
775 --
776 begin
777 -- This function will return TRUE if iRecruitment is not installed
778 -- or view_all_candidates_flag is set to 'All'.
779 if (nvl(hr_signon.g_hr_security_profile.view_all_candidates_flag,
780 g_context.view_all_candidates_flag) = 'Y' or
781 nvl(fnd_profile.value('IRC_INSTALLED_FLAG'), 'N') = 'N') then
782 --
783 return true;
784 --
785 else
786 --
787 return false;
788 --
789 end if;
790 --
791 end view_all_candidates;
792 --
793 -----------------------------------------------------------------------
794 --< view_all_employees >-----------------------------------------------
795 -----------------------------------------------------------------------
796 --
797 FUNCTION view_all_employees
798 RETURN BOOLEAN
799 IS
800 BEGIN
801 if NVL(hr_signon.g_hr_security_profile.view_all_employees_flag
802 ,g_context.view_all_employees_flag) = 'Y' then
803 return true;
804 else
805 return false;
806 end if;
807 END view_all_employees;
808 --
809 -----------------------------------------------------------------------
810 --< restricted_applicants >----------------------------------------------
811 -----------------------------------------------------------------------
812 --
813 FUNCTION restricted_applicants
814 RETURN BOOLEAN
815 IS
816 BEGIN
817 if (NVL(hr_signon.g_hr_security_profile.view_all_applicants_flag
818 ,g_context.view_all_applicants_flag) = 'N') then
819 RETURN TRUE;
820 else
821 return FALSE;
822 end if;
823 END restricted_applicants;
824 --
825 -----------------------------------------------------------------------
826 --< restricted_cwk >-----------------------------------------------------
827 -----------------------------------------------------------------------
828 --
829 FUNCTION restricted_cwk
830 RETURN BOOLEAN
831 IS
832 BEGIN
833 if NVL(hr_signon.g_hr_security_profile.view_all_cwk_flag
834 ,g_context.view_all_cwk_flag) = 'N' then
835 return true;
836 else
837 return false;
838 end if;
839 END restricted_cwk;
840 --
841 -----------------------------------------------------------------------
842 --< restricted_contacts >------------------------------------------------
843 -----------------------------------------------------------------------
844 --
845 FUNCTION restricted_contacts
846 RETURN BOOLEAN
847 IS
848 BEGIN
849 if NVL(hr_signon.g_hr_security_profile.view_all_contacts_flag
850 ,g_context.view_all_contacts_flag) = 'N' then
851 return true;
852 else
853 return false;
854 end if;
855 END restricted_contacts;
856 --
857 -----------------------------------------------------------------------
858 --< restricted_employees >-----------------------------------------------
859 -----------------------------------------------------------------------
860 --
861 FUNCTION restricted_employees
862 RETURN BOOLEAN
863 IS
864 BEGIN
865 if NVL(hr_signon.g_hr_security_profile.view_all_employees_flag
866 ,g_context.view_all_employees_flag) = 'N' then
867 return true;
868 else
869 return false;
870 end if;
871 END restricted_employees;
872 --
873 -----------------------------------------------------------------------
874 --< view_all_organizations >-------------------------------------------
875 -----------------------------------------------------------------------
876 --
877 FUNCTION view_all_organizations
878 RETURN BOOLEAN
879 IS
880 BEGIN
881 RETURN
882 (NVL(hr_signon.g_hr_security_profile.view_all_organizations_flag
883 ,g_context.view_all_organizations_flag) = 'Y' );
884 END view_all_organizations;
885 --
886 -----------------------------------------------------------------------
887 --< view_all_positions >-----------------------------------------------
888 -----------------------------------------------------------------------
889 --
890 FUNCTION view_all_positions
891 RETURN BOOLEAN
892 IS
893 BEGIN
894 RETURN
895 (NVL(hr_signon.g_hr_security_profile.view_all_positions_flag
896 ,g_context.view_all_positions_flag) = 'Y' );
897 END view_all_positions;
898 --
899 -----------------------------------------------------------------------
900 --< restrict_by_supervisor >-------------------------------------------
901 -----------------------------------------------------------------------
902 --
903 FUNCTION restrict_by_supervisor
904 RETURN BOOLEAN
905 IS
906 BEGIN
907 RETURN
908 (NVL(hr_signon.g_hr_security_profile.restrict_by_supervisor_flag
909 ,g_context.restrict_by_supervisor_flag) = 'Y' );
910 END restrict_by_supervisor;
911 --
912 --
913 --
914 -----------------------------------------------------------------------
915 --< view_all_payrolls >------------------------------------------------
916 -----------------------------------------------------------------------
917 --
918 FUNCTION view_all_payrolls
919 RETURN BOOLEAN
920 IS
921 BEGIN
922 RETURN
923 (NVL(hr_signon.g_hr_security_profile.view_all_payrolls_flag
924 ,g_context.view_all_payrolls_flag) = 'Y' );
925 END view_all_payrolls;
926 --
927 --
928 --
929 -----------------------------------------------------------------------
930 --< exclude_person >------------------------------------------------
931 -----------------------------------------------------------------------
932 --
933 FUNCTION exclude_person
934 RETURN BOOLEAN
935 IS
936 BEGIN
937 RETURN
938 (NVL(hr_signon.g_hr_security_profile.exclude_person_flag
939 ,g_context.exclude_person_flag) = 'Y' );
940 END exclude_person;
941 --
942 -----------------------------------------------------------------------
943 --< check_person_list >------------------------------------------------
944 -----------------------------------------------------------------------
945 --
946 FUNCTION check_person_list
947 (p_person_id IN NUMBER
948 )
949 RETURN BOOLEAN
950 IS
951 begin
952
953 IF globals_need_refreshing THEN
954 hr_signon.initialize_hr_security;
955 initialise_globals;
956 END IF;
957 --
958 -- return g_person_list.exists(p_person_id); -- Fixed for bug 5985232
959 return hr_security_internal.g_per_tbl.exists(p_person_id); -- Fixed for bug 5985232 (6320769)
960
961 END check_person_list;
962 --
963 -- Added for Bug 8465433
964 -----------------------------------------------------------------------
965 --< check_vac_person_list >------------------------------------------------
966 -----------------------------------------------------------------------
967 --
968 FUNCTION check_vac_person_list
969 (p_person_id IN NUMBER
970 )
971 RETURN BOOLEAN
972 IS
973 begin
974
975 IF globals_need_refreshing THEN
976 initialise_globals;
977 END IF;
978 return hr_security_internal.g_vac_per_tbl.exists(p_person_id);
979
980 END check_vac_person_list;
981 --
982 -----------------------------------------------------------------------
983 --< globals_need_refreshing >------------------------------------------
984 -----------------------------------------------------------------------
985 --
986 FUNCTION globals_need_refreshing
987 RETURN BOOLEAN
988 IS
989
990 l_return BOOLEAN;
991
992 BEGIN
993
994 --- DK 2001-11-17
995 ---
996 --- Bug 2086208
997 --- Along with changes in the cached values of user,resp and security group
998 --- a change in the ICX session id causes the person list to be rebuilt.
999 --- Ideally this would be signalled via the product initialization code
1000 ---
1001
1002 IF g_user_id <> fnd_global.user_id
1003 or g_resp_id <> fnd_global.resp_id
1004 or g_resp_appl_id <> fnd_global.resp_appl_id
1005 or g_security_group_id <> fnd_global.security_group_id
1006 or g_icx_session_id <> icx_sec.g_session_id
1007 THEN
1008 l_return := TRUE;
1009 ELSE
1010 l_return := FALSE;
1011
1012 END IF;
1013 --
1014 return l_return;
1015
1016 END globals_need_refreshing;
1017 --
1018 -----------------------------------------------------------------------
1019 --< check_organization_list >------------------------------------------
1020 -----------------------------------------------------------------------
1021 --
1022 FUNCTION check_organization_list
1023 (p_organization_id IN NUMBER
1024 )
1025 RETURN BOOLEAN
1026 IS
1027 --
1028 CURSOR chk_org_list
1029 IS
1030 SELECT 1
1031 FROM per_organization_list
1032 WHERE security_profile_id = get_security_profile
1033 AND organization_id = p_organization_id;
1034 --
1035 l_return_value BOOLEAN;
1036 l_dummy NUMBER;
1037 --
1038 BEGIN
1039 OPEN chk_org_list;
1040 FETCH chk_org_list INTO l_dummy;
1041 l_return_value := chk_org_list%FOUND;
1042 CLOSE chk_org_list;
1043 --
1044 RETURN (l_return_value);
1045 END check_organization_list;
1046 --
1047 -----------------------------------------------------------------------
1048 --< check_position_list >----------------------------------------------
1049 -----------------------------------------------------------------------
1050 --
1051 FUNCTION check_position_list
1052 (p_position_id IN NUMBER
1053 )
1054 RETURN BOOLEAN
1055 IS
1056 CURSOR chk_pos_list IS
1057 SELECT 1
1058 FROM per_position_list
1059 WHERE security_profile_id = get_security_profile
1060 AND position_id = p_position_id;
1061 --
1062 l_return_value BOOLEAN;
1063 l_dummy NUMBER;
1064 --
1065 BEGIN
1066 OPEN chk_pos_list;
1067 FETCH chk_pos_list INTO l_dummy;
1068 l_return_value := chk_pos_list%FOUND;
1069 CLOSE chk_pos_list;
1070 --
1071 RETURN (l_return_value);
1072 END check_position_list;
1073 --
1074 -----------------------------------------------------------------------
1075 --< check_payroll_list >-----------------------------------------------
1076 -----------------------------------------------------------------------
1077 --
1078 FUNCTION check_payroll_list
1079 (p_payroll_id IN NUMBER
1080 )
1081 RETURN BOOLEAN
1082 IS
1083 CURSOR chk_pay_list
1084 IS
1085 SELECT 1
1086 FROM pay_payroll_list
1087 WHERE security_profile_id = get_security_profile
1088 AND payroll_id = p_payroll_id;
1089 --
1090 l_return_value boolean;
1091 l_dummy number;
1092 --
1093 BEGIN
1094 OPEN chk_pay_list;
1095 FETCH chk_pay_list INTO l_dummy;
1096 l_return_value := chk_pay_list%FOUND;
1097 CLOSE chk_pay_list;
1098 --
1099 RETURN (l_return_value);
1100 END check_payroll_list;
1101 --
1102 -----------------------------------------------------------------------
1103 --< show_person >-- overloaded and called directly from secure views --
1104 -----------------------------------------------------------------------
1105 --
1106 function show_person(
1107 p_person_id in number
1108 ,p_current_applicant_flag in varchar2
1109 ,p_current_employee_flag in varchar2
1110 ,p_current_npw_flag in varchar2
1111 ,p_employee_number in varchar2
1112 ,p_applicant_number in varchar2
1113 ,p_npw_number in varchar2
1114 ) return varchar2 is
1115 --
1116 begin
1117 -- if the profile excludes users, prevent the logged on user from seeing
1118 -- themselves under any circumstances.
1119 if (p_person_id = g_person_id and exclude_person) then
1120 --
1121 return 'FALSE';
1122 --
1123 end if;
1124 -- Return TRUE if the security profile has no person restrictions.
1125 if (view_all = 'Y' or
1126 (view_all_employees and view_all_applicants and view_all_cwk and
1127 view_all_contacts and view_all_candidates)) then
1128 --
1129 return 'TRUE';
1130 --
1131 end if;
1132 -- Return TRUE if the security profile has no work structure
1133 -- restrictions and the person restriction is "Restricted" for this
1134 -- type of person.
1135 if (no_restrictions and
1136 ((p_current_employee_flag = 'Y' and restricted_employees) or
1137 (p_current_applicant_flag = 'Y' and restricted_applicants) or
1138 (p_current_npw_flag = 'Y' and restricted_cwk) or
1139 (p_employee_number is null and p_applicant_number is null and
1140 p_npw_number is null and restricted_contacts and
1141 view_all_candidates))) then
1142 --
1143 return 'TRUE';
1144 --
1145 end if;
1146 -- Return TRUE if the security profile is view all contacts or you
1147 -- can see all the other types of people (and so contacts too)
1148 -- and where this person is a contact.
1149
1150 -- A condition with view_all_contacts_flag = All and
1151 -- view_all_candidates_flag = None, will not be taken care in below
1152 -- IF condition. ie: in such a scenario, its been decided that contacts
1153 -- will be populated in per_person_list through PERSLM. Thereby this
1154 -- function (SHOW_PERSON) will return a TRUE through CHECK_PERSON_LIST.
1155
1156 -- Contacts Candidates Contacts cached
1157 -- ------------------------------------------
1158 -- All All No
1159 -- All None Yes
1160 -- Restricted All Yes
1161 -- Restricted None Yes
1162 if view_all_contacts and view_all_candidates and
1163 p_employee_number is null and p_applicant_number is null and
1164 p_npw_number is null then
1165 --
1166 return 'TRUE';
1167 --
1168 end if;
1169 -- Return TRUE if the profile has restrictions but they
1170 -- are not relevant to this person.
1171
1172 -- Applicants are treated different: they must be only
1173 -- an applicant and not an employee / contingent worker
1174 -- to immediately return TRUE. This prevents emps or
1175 -- cwks being visible in an applicant-only security
1176 -- profile. Applicants who are also emps and cwks will
1177 -- have their security determined by listgen so the person
1178 -- list must be checked in this example.
1179 if (p_current_employee_flag = 'Y' and view_all_employees) or
1180 (p_current_npw_flag = 'Y' and view_all_cwk) or
1181 (p_current_applicant_flag ='Y' and nvl(p_current_npw_flag, 'N') = 'N'
1182 and nvl(p_current_employee_flag, 'N') = 'N'
1183 and view_all_applicants) then
1184 --
1185 return 'TRUE';
1186 --
1187 end if;
1188 --
1189 if view_all_applicants and p_applicant_number is not null and
1190 p_employee_number is null and p_npw_number is null then
1191 -- Profile is view all applicants, person is or has been an applicant
1192 -- and they person have not been an employee/cont worker so grant
1193 -- access. If the person is/was an Emp/CWK then grant access based
1194 -- on Emp/CWK criteria i.e. if the person is also an Emp and is
1195 -- visible then grant access. This does mean that an Ex-Emp and Apl
1196 -- will disappear from a view_all_applicants/restricted employees
1197 -- profile on termination of the application if the terminated Emp
1198 -- assignment does not allow access to this person for this profile.
1199 -- i.e. the profile allows access to Emps in "Org 1" but when the
1200 -- person was an employee they were in "Org 2".
1201
1202 -- This is slightly inconsistent with behaviour of PERSLM when
1203 -- granting access to Ex-Emp and Ex-Apl people for profiles which
1204 -- are restricted_employees and restricted_applicants but is better
1205 -- than the current situation.
1206
1207 -- We could/do have similar problems with view_all_emp and
1208 -- view_all_npw profiles but it's less likely that customers have
1209 -- view_all_emp/npw profiles. For now we'll ignore these cases.
1210 return 'TRUE';
1211 --
1212 end if;
1213
1214 -- code start for bug 8242764
1215 if ( no_restrictions and view_all_employees) then
1216 if (( (not view_all_cwk) and nvl(p_current_npw_flag, 'N') = 'Y' )
1217 or
1218 ( (not view_all_applicants) and nvl(p_current_applicant_flag, 'N') = 'Y' ))
1219 then
1220 NULL;
1221 else
1222 if(HR_GENERAL2.is_person_type(p_person_id,'EX_EMP',g_effective_date)) then
1223 return 'TRUE';
1224 end if;
1225 end if;
1226 end if;
1227 -- code end for bug 8242764
1228
1229
1230 -- code start for bug 13954957
1231 if(view_all_cwk) then
1232
1233 if(HR_GENERAL2.is_person_type(p_person_id,'EX_CWK',g_effective_date)) then
1234 return 'TRUE';
1235 end if;
1236 end if;
1237 -- code end for bug 13954957
1238
1239 -- If security evaluation was deferred at logon, or if the person/
1240 -- assignment permissions are unknown for some other reason, use
1241 -- caching on demand to evaluate permissions on the fly.
1242 if not hr_security_internal.per_access_known then
1243 -- Passing a value to p_what_to_evaluate avoids evaluating
1244 -- permissions for irrelevant security criteria.
1245 hr_security_internal.evaluate_access(
1246 p_person_id => g_person_id
1247 ,p_user_id => g_user_id
1248 ,p_effective_date => g_effective_date
1249 ,p_sec_prof_rec => g_context
1250 ,p_what_to_evaluate => hr_security_internal.g_per_sec_only);
1251 -- The two sets of person cache are synched.
1252 --sync_person_cache;--Fixed for bug 6012095(Fwd port of 5985232)
1253 --
1254 end if;
1255 -- We must check the person list to determine this person's security.
1256 if check_person_list(p_person_id) then
1257 --
1258 return 'TRUE';
1259 --
1260 end if;
1261 -- This person cannot be visible.
1262 return 'FALSE';
1263 --
1264 end show_person;
1265
1266 --SSHR
1267 function show_person(
1268 p_person_id in number
1269 ,p_current_applicant_flag in varchar2
1270 ,p_current_employee_flag in varchar2
1271 ,p_current_npw_flag in varchar2
1272 ,p_employee_number in varchar2
1273 ,p_applicant_number in varchar2
1274 ,p_npw_number in varchar2
1275 ,p_top_person_id in number
1276 ) return varchar2 is
1277 --
1278 begin
1279 -- if the profile excludes users, prevent the logged on user from seeing
1280 -- themselves under any circumstances.
1281 if (p_person_id = g_person_id and exclude_person) then
1282 --
1283 return 'FALSE';
1284 --
1285 end if;
1286 -- Return TRUE if the security profile has no person restrictions.
1287 if (view_all = 'Y' or
1288 (view_all_employees and view_all_applicants and view_all_cwk and
1289 view_all_contacts and view_all_candidates)) then
1290 --
1291 return 'TRUE';
1292 --
1293 end if;
1294 -- Return TRUE if the security profile has no work structure
1295 -- restrictions and the person restriction is "Restricted" for this
1296 -- type of person.
1297 if (no_restrictions and
1298 ((p_current_employee_flag = 'Y' and restricted_employees) or
1299 (p_current_applicant_flag = 'Y' and restricted_applicants) or
1300 (p_current_npw_flag = 'Y' and restricted_cwk) or
1301 (p_employee_number is null and p_applicant_number is null and
1302 p_npw_number is null and restricted_contacts and
1303 view_all_candidates))) then
1304 --
1305 return 'TRUE';
1306 --
1307 end if;
1308 -- Return TRUE if the security profile is view all contacts or you
1309 -- can see all the other types of people (and so contacts too)
1310 -- and where this person is a contact.
1311
1312 -- A condition with view_all_contacts_flag = All and
1313 -- view_all_candidates_flag = None, will not be taken care in below
1314 -- IF condition. ie: in such a scenario, its been decided that contacts
1315 -- will be populated in per_person_list through PERSLM. Thereby this
1316 -- function (SHOW_PERSON) will return a TRUE through CHECK_PERSON_LIST.
1317
1318 -- Contacts Candidates Contacts cached
1319 -- ------------------------------------------
1320 -- All All No
1321 -- All None Yes
1322 -- Restricted All Yes
1323 -- Restricted None Yes
1324 if view_all_contacts and view_all_candidates and
1325 p_employee_number is null and p_applicant_number is null and
1326 p_npw_number is null then
1327 --
1328 return 'TRUE';
1329 --
1330 end if;
1331 -- Return TRUE if the profile has restrictions but they
1332 -- are not relevant to this person.
1333
1334 -- Applicants are treated different: they must be only
1335 -- an applicant and not an employee / contingent worker
1336 -- to immediately return TRUE. This prevents emps or
1337 -- cwks being visible in an applicant-only security
1338 -- profile. Applicants who are also emps and cwks will
1339 -- have their security determined by listgen so the person
1340 -- list must be checked in this example.
1341 if (p_current_employee_flag = 'Y' and view_all_employees) or
1342 (p_current_npw_flag = 'Y' and view_all_cwk) or
1343 (p_current_applicant_flag ='Y' and nvl(p_current_npw_flag, 'N') = 'N'
1344 and nvl(p_current_employee_flag, 'N') = 'N'
1345 and view_all_applicants) then
1346 --
1347 return 'TRUE';
1348 --
1349 end if;
1350 --
1351 if view_all_applicants and p_applicant_number is not null and
1352 p_employee_number is null and p_npw_number is null then
1353 -- Profile is view all applicants, person is or has been an applicant
1354 -- and they person have not been an employee/cont worker so grant
1355 -- access. If the person is/was an Emp/CWK then grant access based
1356 -- on Emp/CWK criteria i.e. if the person is also an Emp and is
1357 -- visible then grant access. This does mean that an Ex-Emp and Apl
1358 -- will disappear from a view_all_applicants/restricted employees
1359 -- profile on termination of the application if the terminated Emp
1360 -- assignment does not allow access to this person for this profile.
1361 -- i.e. the profile allows access to Emps in "Org 1" but when the
1362 -- person was an employee they were in "Org 2".
1363
1364 -- This is slightly inconsistent with behaviour of PERSLM when
1365 -- granting access to Ex-Emp and Ex-Apl people for profiles which
1366 -- are restricted_employees and restricted_applicants but is better
1367 -- than the current situation.
1368
1369 -- We could/do have similar problems with view_all_emp and
1370 -- view_all_npw profiles but it's less likely that customers have
1371 -- view_all_emp/npw profiles. For now we'll ignore these cases.
1372 return 'TRUE';
1373 --
1374 end if;
1375
1376 -- code start for bug 8242764
1377 if ( no_restrictions and view_all_employees) then
1378 if (( (not view_all_cwk) and nvl(p_current_npw_flag, 'N') = 'Y' )
1379 or
1380 ( (not view_all_applicants) and nvl(p_current_applicant_flag, 'N') = 'Y' ))
1381 then
1382 NULL;
1383 else
1384 if(HR_GENERAL2.is_person_type(p_person_id,'EX_EMP',g_effective_date)) then
1385 return 'TRUE';
1386 end if;
1387 end if;
1388 end if;
1389 -- code end for bug 8242764
1390
1391 -- If security evaluation was deferred at logon, or if the person/
1392 -- assignment permissions are unknown for some other reason, use
1393 -- caching on demand to evaluate permissions on the fly.
1394 -- if not hr_security_internal.per_access_known then - sshr perf issue
1395 -- Passing a value to p_what_to_evaluate avoids evaluating
1396 -- permissions for irrelevant security criteria.
1397 hr_security_internal.evaluate_access(
1398 p_person_id => g_person_id
1399 ,p_user_id => g_user_id
1400 ,p_effective_date => g_effective_date
1401 ,p_sec_prof_rec => g_context
1402 ,p_what_to_evaluate => hr_security_internal.g_per_sec_only
1403 ,p_top_person_id => p_top_person_id);
1404 -- The two sets of person cache are synched.
1405 --sync_person_cache;--Fixed for bug 6012095(Fwd port of 5985232)
1406 --
1407 --end if;
1408 g_IS_SSHR := 0;
1409 -- We must check the person list to determine this person's security.
1410 if check_person_list(p_person_id) then
1411 --
1412 return 'TRUE';
1413 --
1414 end if;
1415 -- This person cannot be visible.
1416 return 'FALSE';
1417 --
1418 EXCEPTION
1419 WHEN others THEN
1420 g_IS_SSHR := 0;
1421 hr_utility.set_location('Leaving: Show_person', 999);
1422
1423 end show_person;
1424
1425
1426 --SSHR
1427 --
1428 -----------------------------------------------------------------------
1429 --< show_person >--- original called from show_record -----------------
1430 -----------------------------------------------------------------------
1431 --
1432 FUNCTION show_person
1433 (p_person_type_id IN NUMBER
1434 ,p_person_id IN NUMBER
1435 ,p_employee_number IN VARCHAR2
1436 ,p_applicant_number IN VARCHAR2
1437 )
1438 RETURN VARCHAR2
1439 IS
1440 BEGIN
1441 -- added for bug 4193763
1442 if (p_person_id = g_person_id and exclude_person) then
1443 --
1444 return 'FALSE';
1445 --
1446 end if;
1447
1448 -- added for bug 4193763
1449 --
1450 -- If View All is set to 'Yes' OR
1451 -- the profile is view all contact and both the numbers are null OR
1452 -- the profile is view all emp/apl/cwk
1453 --
1454 IF view_all = 'Y'
1455 OR (view_all_contacts AND
1456 view_all_employees AND
1457 view_all_applicants AND
1458 view_all_cwk) THEN
1459 RETURN 'TRUE';
1460 END IF;
1461
1462 --
1463 -- Return TRUE if the security profile is view all contacts and
1464 -- this person is a contact.
1465 --
1466 IF view_all_contacts AND
1467 p_employee_number is null AND
1468 p_applicant_number is null THEN
1469 return 'TRUE';
1470 END IF;
1471
1472 --
1473 -- If View All Employees is 'Yes' and this is an employee
1474 --
1475 IF (view_all_employees AND p_employee_number IS NOT NULL)
1476 OR (view_all_employees AND p_employee_number IS NOT NULL) THEN
1477 --
1478 -- If this is the excluding person return false
1479 --
1480 -- added for bug 4193763
1481 -- commented the if condition
1482 -- if exclude_person and p_person_id=g_person_id then
1483 -- RETURN 'FALSE';
1484 -- else
1485 RETURN 'TRUE';
1486 -- end if;
1487 -- added for bug 4193763
1488 --
1489 -- If View All Applicants is 'Yes' and this is an applicant
1490 --
1491 ELSIF p_applicant_number IS NOT NULL THEN
1492 if view_all_applicants and view_all_employees and view_all_cwk then
1493 RETURN 'TRUE';
1494 end if;
1495 END IF;
1496
1497 --
1498 -- If security evaluation was deferred at logon,
1499 -- or if the person / assignment permissions are unknown for
1500 -- some other reason, use caching on demand to evaluate
1501 -- permissions on the fly.
1502 --
1503 IF NOT hr_security_internal.per_access_known THEN
1504 --
1505 -- Passing a value to p_what_to_evaluate avoids evaluating
1506 -- permissions for irrelevant security criteria.
1507 --
1508 hr_security_internal.evaluate_access
1509 (p_person_id => g_person_id
1510 ,p_user_id => g_user_id
1511 ,p_effective_date => g_effective_date
1512 ,p_sec_prof_rec => g_context
1513 ,p_what_to_evaluate => hr_security_internal.g_PER_SEC_ONLY);
1514
1515 --
1516 -- The two sets of person cache are synched.
1517 --
1518 --sync_person_cache; --6012095(Forward Port of 5985232)
1519
1520 END IF;
1521
1522 --
1523 -- Check the global pl/sql table for the person
1524 --
1525 IF check_person_list(p_person_id) THEN
1526 RETURN 'TRUE';
1527
1528 END IF;
1529 RETURN 'FALSE';
1530 END show_person;
1531
1532 --SSHR
1533
1534 FUNCTION show_person
1535 (p_person_type_id IN NUMBER
1536 ,p_person_id IN NUMBER
1537 ,p_employee_number IN VARCHAR2
1538 ,p_applicant_number IN VARCHAR2
1539 ,p_top_person_id IN NUMBER
1540 )
1541 RETURN VARCHAR2
1542 IS
1543 BEGIN
1544 -- added for bug 4193763
1545 if (p_person_id = g_person_id and exclude_person) then
1546 --
1547 return 'FALSE';
1548 --
1549 end if;
1550
1551 -- added for bug 4193763
1552 --
1553 -- If View All is set to 'Yes' OR
1554 -- the profile is view all contact and both the numbers are null OR
1555 -- the profile is view all emp/apl/cwk
1556 --
1557 IF view_all = 'Y'
1558 OR (view_all_contacts AND
1559 view_all_employees AND
1560 view_all_applicants AND
1561 view_all_cwk) THEN
1562 RETURN 'TRUE';
1563 END IF;
1564
1565 --
1566 -- Return TRUE if the security profile is view all contacts and
1567 -- this person is a contact.
1568 --
1569 IF view_all_contacts AND
1570 p_employee_number is null AND
1571 p_applicant_number is null THEN
1572 return 'TRUE';
1573 END IF;
1574
1575 --
1576 -- If View All Employees is 'Yes' and this is an employee
1577 --
1578 IF (view_all_employees AND p_employee_number IS NOT NULL)
1579 OR (view_all_employees AND p_employee_number IS NOT NULL) THEN
1580 --
1581 -- If this is the excluding person return false
1582 --
1583 -- added for bug 4193763
1584 -- commented the if condition
1585 -- if exclude_person and p_person_id=g_person_id then
1586 -- RETURN 'FALSE';
1587 -- else
1588 RETURN 'TRUE';
1589 -- end if;
1590 -- added for bug 4193763
1591 --
1592 -- If View All Applicants is 'Yes' and this is an applicant
1593 --
1594 ELSIF p_applicant_number IS NOT NULL THEN
1595 if view_all_applicants and view_all_employees and view_all_cwk then
1596 RETURN 'TRUE';
1597 end if;
1598 END IF;
1599
1600 --
1601 -- If security evaluation was deferred at logon,
1602 -- or if the person / assignment permissions are unknown for
1603 -- some other reason, use caching on demand to evaluate
1604 -- permissions on the fly.
1605 --
1606 -- IF NOT hr_security_internal.per_access_known THEN - SSHR PERF ISSUE
1607 --
1608 -- Passing a value to p_what_to_evaluate avoids evaluating
1609 -- permissions for irrelevant security criteria.
1610 --
1611 hr_security_internal.evaluate_access
1612 (p_person_id => g_person_id
1613 ,p_user_id => g_user_id
1614 ,p_effective_date => g_effective_date
1615 ,p_sec_prof_rec => g_context
1616 ,p_what_to_evaluate => hr_security_internal.g_PER_SEC_ONLY
1617 ,p_top_person_id => p_top_person_id);
1618
1619 --
1620 -- The two sets of person cache are synched.
1621 --
1622 --sync_person_cache; --6012095(Forward Port of 5985232)
1623
1624 -- END IF;
1625 g_IS_SSHR := 0;
1626
1627 --
1628 -- Check the global pl/sql table for the person
1629 --
1630 IF check_person_list(p_person_id) THEN
1631 RETURN 'TRUE';
1632
1633 END IF;
1634 RETURN 'FALSE';
1635
1636 EXCEPTION
1637 WHEN others THEN
1638 g_IS_SSHR := 0;
1639 hr_utility.set_location('Leaving: Show_person', 999);
1640 END show_person;
1641
1642 --SSHR
1643 --
1644 -----------------------------------------------------------------------
1645 --< show_asg_for_per >-------------------------------------------------
1646 -----------------------------------------------------------------------
1647 --
1648 -- This function is private. To make use of this function, use the
1649 -- wrapper function show_record (which is public).
1650 --
1651 -- This function has been renamed from show_assignment to
1652 -- show_asg_for_per as part of the assignment and user security
1653 -- changes (bug 3346940). This function assesses assignment security
1654 -- at the person level, i.e., if you can see the person you can see
1655 -- all their assignments.
1656 --
1657 -- show_record calls this function by default unless another parameter
1658 -- is passed to show_record, in which case it calls show_assignment.
1659 --
1660 FUNCTION show_asg_for_per
1661 (p_assignment_id IN NUMBER
1662 ,p_person_id IN NUMBER
1663 ,p_assignment_type IN VARCHAR2
1664 )
1665 RETURN VARCHAR2 IS
1666
1667 BEGIN
1668 --
1669 -- added for bug 4193763
1670 if (p_person_id = g_person_id and exclude_person) then
1671 --
1672 return 'FALSE';
1673 --
1674 end if;
1675 -- added for bug 4193763
1676 --
1677 IF ((view_all = 'Y')
1678 OR (view_all_employees AND
1679 view_all_applicants AND
1680 view_all_cwk AND
1681 view_all_contacts)
1682 OR (no_restrictions))
1683 THEN
1684 RETURN 'TRUE';
1685 ELSIF (view_all_applicants AND p_assignment_type = 'A') THEN
1686 RETURN 'TRUE';
1687 ELSIF (view_all_employees AND p_assignment_type = 'E') THEN
1688 RETURN 'TRUE';
1689 ELSIF (view_all_CWK AND p_assignment_type = 'C') THEN
1690 RETURN 'TRUE';
1691 ELSIF (check_person_list(p_person_id)) THEN
1692 RETURN 'TRUE';
1693 ELSE
1694 RETURN 'FALSE';
1695 END IF;
1696 END show_asg_for_per;
1697 --
1698 -----------------------------------------------------------------------
1699 --< show_assignment >--------------------------------------------------
1700 -----------------------------------------------------------------------
1701 --
1702 -- This function is private. To make use of this function, use the
1703 -- wrapper function show_record (which is public).
1704 --
1705 -- This function has been added as part of the assignment
1706 -- and user security changes (bug 3346940). The previous
1707 -- show_assignment, which assesses security at a person level, has
1708 -- been re-named to show_asg_for_per.
1709 --
1710 -- This function assesses security for each individual assignment.
1711 --
1712 -- show_record calls this function if an additional parameter is
1713 -- passed to show_record.
1714 --
1715 FUNCTION show_assignment
1716 (p_assignment_id IN NUMBER
1717 ,p_person_id IN NUMBER
1718 ,p_assignment_type IN VARCHAR2
1719 )
1720 RETURN VARCHAR2 IS
1721
1722 BEGIN
1723
1724 --
1725 -- Exclude the current user or named user if set.
1726 --
1727 IF exclude_person
1728 AND p_person_id = g_person_id
1729 THEN
1730 RETURN 'FALSE';
1731 END IF;
1732
1733 --
1734 -- Assess the permissions using the given parameters if
1735 -- possible.
1736 --
1737 IF ((view_all = 'Y')
1738 OR (view_all_employees AND
1739 view_all_applicants AND
1740 view_all_cwk AND
1741 view_all_contacts)
1742 OR (no_restrictions))
1743 THEN
1744 RETURN 'TRUE';
1745 ELSIF (view_all_applicants AND p_assignment_type = 'A') THEN
1746 RETURN 'TRUE';
1747 ELSIF (view_all_employees AND p_assignment_type = 'E') THEN
1748 RETURN 'TRUE';
1749 ELSIF (view_all_cwk AND p_assignment_type = 'C') THEN
1750 RETURN 'TRUE';
1751 END IF;
1752
1753 --
1754 -- If security evaluation was deferred at logon,
1755 -- or if the person / assignment permissions are unknown for
1756 -- some other reason, use caching on demand to evaluate
1757 -- permissions on the fly.
1758 --
1759 IF NOT hr_security_internal.per_access_known THEN
1760 --
1761 -- Passing a value to p_what_to_evaluate avoids evaluating
1762 -- permissions for irrelevant security criteria.
1763 --
1764 hr_security_internal.evaluate_access
1765 (p_person_id => g_person_id
1766 ,p_user_id => g_user_id
1767 ,p_effective_date => g_effective_date
1768 ,p_sec_prof_rec => g_context
1769 ,p_what_to_evaluate => hr_security_internal.g_PER_SEC_ONLY);
1770
1771 --
1772 -- The two sets of person cache are synched.
1773 --
1774 --sync_person_cache;--(Fwd port of 5985232)
1775
1776 END IF;
1777
1778 --
1779 -- If restricting at an individual assignment level, check
1780 -- the assignments list, rather than the person list.
1781 --
1782 IF NVL(g_context.restrict_on_individual_asg, 'N') = 'Y'
1783 THEN
1784 IF hr_security_internal.g_asg_tbl.EXISTS(p_assignment_id) THEN
1785 RETURN 'TRUE';
1786 ELSE
1787 RETURN 'FALSE';
1788 END IF;
1789 ELSE
1790 --
1791 -- For safety, continue using check_person_list rather than
1792 -- referencing hr_security_internal.g_per_tbl until
1793 -- evaluate_access does all the work.
1794 --
1795 IF check_person_list(p_person_id) THEN
1796 RETURN 'TRUE';
1797 ELSE
1798 RETURN 'FALSE';
1799 END IF;
1800 END IF;
1801
1802 END show_assignment;
1803 --sshr
1804 /* This function is added for SSHR Hierarchical page Performance Issue.
1805 This overloaded function has additional parameter p_top_person_id which will have the value
1806 for the top_person_id using which the hierarchical query will be executed.
1807 Also commented the check for the fnd_session_context changed
1808 */
1809
1810 FUNCTION show_assignment
1811 (p_assignment_id IN NUMBER
1812 ,p_person_id IN NUMBER
1813 ,p_assignment_type IN VARCHAR2
1814 ,p_top_person_id IN NUMBER
1815 )
1816 RETURN VARCHAR2 IS
1817
1818 BEGIN
1819
1820 --
1821 -- Exclude the current user or named user if set.
1822 --
1823 IF exclude_person
1824 AND p_person_id = g_person_id
1825 THEN
1826 RETURN 'FALSE';
1827 END IF;
1828
1829 --
1830 -- Assess the permissions using the given parameters if
1831 -- possible.
1832 --
1833 IF ((view_all = 'Y')
1834 OR (view_all_employees AND
1835 view_all_applicants AND
1836 view_all_cwk AND
1837 view_all_contacts)
1838 OR (no_restrictions))
1839 THEN
1840 RETURN 'TRUE';
1841 ELSIF (view_all_applicants AND p_assignment_type = 'A') THEN
1842 RETURN 'TRUE';
1843 ELSIF (view_all_employees AND p_assignment_type = 'E') THEN
1844 RETURN 'TRUE';
1845 ELSIF (view_all_cwk AND p_assignment_type = 'C') THEN
1846 RETURN 'TRUE';
1847 END IF;
1848
1849 --
1850 -- If security evaluation was deferred at logon,
1851 -- or if the person / assignment permissions are unknown for
1852 -- some other reason, use caching on demand to evaluate
1853 -- permissions on the fly.
1854 --
1855 -- IF NOT hr_security_internal.per_access_known THEN - sshr perf issue
1856 --
1857 -- Passing a value to p_what_to_evaluate avoids evaluating
1858 -- permissions for irrelevant security criteria.
1859 --
1860 hr_security_internal.evaluate_access
1861 (p_person_id => g_person_id
1862 ,p_user_id => g_user_id
1863 ,p_effective_date => g_effective_date
1864 ,p_sec_prof_rec => g_context
1865 ,p_what_to_evaluate => hr_security_internal.g_PER_SEC_ONLY
1866 ,p_top_person_id => p_top_person_id);
1867
1868 --
1869 -- The two sets of person cache are synched.
1870 --
1871 --sync_person_cache;--(Fwd port of 5985232)
1872
1873 -- END IF;
1874 g_IS_SSHR := 0;
1875 --
1876 -- If restricting at an individual assignment level, check
1877 -- the assignments list, rather than the person list.
1878 --
1879 IF NVL(g_context.restrict_on_individual_asg, 'N') = 'Y'
1880 THEN
1881 IF hr_security_internal.g_asg_tbl.EXISTS(p_assignment_id) THEN
1882 RETURN 'TRUE';
1883 ELSE
1884 RETURN 'FALSE';
1885 END IF;
1886 ELSE
1887 --
1888 -- For safety, continue using check_person_list rather than
1889 -- referencing hr_security_internal.g_per_tbl until
1890 -- evaluate_access does all the work.
1891 --
1892 IF check_person_list(p_person_id) THEN
1893 RETURN 'TRUE';
1894 ELSE
1895 RETURN 'FALSE';
1896 END IF;
1897 END IF;
1898 EXCEPTION
1899 WHEN others THEN
1900 g_IS_SSHR := 0;
1901 hr_utility.set_location('Leaving: Show_assignment', 999);
1902
1903 END show_assignment;
1904
1905 --sshr
1906 --
1907 -----------------------------------------------------------------------
1908 --< show_organization >------------------------------------------------
1909 -----------------------------------------------------------------------
1910 --
1911 FUNCTION show_organization
1912 (p_organization_id IN NUMBER
1913 )
1914 RETURN VARCHAR2
1915 IS
1916 BEGIN
1917
1918 --
1919 -- The revised changes here made for enhancement 3346940
1920 -- obsolete check_organization_list (it is no longer used).
1921 -- Instead the cached organization list is accessed directly.
1922 --
1923
1924 --
1925 -- Immediately return true if there is no security.
1926 --
1927 IF (view_all = 'Y' OR view_all_organizations) THEN
1928 RETURN 'TRUE';
1929 END IF;
1930
1931 --
1932 -- If security evaluation was deferred at logon,
1933 -- or if organization permissions are unknown for
1934 -- some other reason, use caching on demand to evaluate
1935 -- permissions on the fly.
1936 --
1937 IF NOT hr_security_internal.org_access_known THEN
1938 --
1939 -- Passing a value to p_what_to_evaluate avoids evaluating
1940 -- permissions for non-org security criteria.
1941 --
1942 hr_security_internal.evaluate_access
1943 (p_person_id => g_person_id
1944 ,p_user_id => g_user_id
1945 ,p_effective_date => g_effective_date
1946 ,p_sec_prof_rec => g_context
1947 ,p_what_to_evaluate => hr_security_internal.g_ORG_SEC_ONLY);
1948 END IF;
1949
1950 IF hr_security_internal.g_org_tbl.EXISTS(p_organization_id) THEN
1951 RETURN 'TRUE';
1952 ELSE
1953 RETURN 'FALSE';
1954 END IF;
1955
1956 END show_organization;
1957 --
1958 -----------------------------------------------------------------------
1959 --< show_position >----------------------------------------------------
1960 -----------------------------------------------------------------------
1961 --
1962 FUNCTION show_position
1963 (p_position_id IN NUMBER
1964 )
1965 RETURN VARCHAR2
1966 IS
1967 BEGIN
1968
1969 --
1970 -- The revised changes here made for enhancement 3346940
1971 -- obsolete check_position_list (it is no longer used).
1972 -- Instead the cached position list is accessed directly.
1973 --
1974
1975 --
1976 -- Immediately return true if there is no security.
1977 --
1978 IF (view_all = 'Y' OR view_all_positions) THEN
1979 RETURN 'TRUE';
1980 END IF;
1981
1982 --
1983 -- If security evaluation was deferred at logon,
1984 -- or if position permissions are unknown for
1985 -- some other reason, use caching on demand to evaluate
1986 -- permissions on the fly.
1987 --
1988 IF NOT hr_security_internal.pos_access_known THEN
1989 --
1990 -- Passing a value to p_what_to_evaluate avoids evaluating
1991 -- permissions for non-pos security criteria.
1992 --
1993 hr_security_internal.evaluate_access
1994 (p_person_id => g_person_id
1995 ,p_user_id => g_user_id
1996 ,p_effective_date => g_effective_date
1997 ,p_sec_prof_rec => g_context
1998 ,p_what_to_evaluate => hr_security_internal.g_POS_SEC_ONLY);
1999 END IF;
2000
2001 IF hr_security_internal.g_pos_tbl.EXISTS(p_position_id) THEN
2002 RETURN 'TRUE';
2003 ELSE
2004 RETURN 'FALSE';
2005 END IF;
2006
2007 END show_position;
2008 --
2009 -----------------------------------------------------------------------
2010 --< show_payroll >-----------------------------------------------------
2011 -----------------------------------------------------------------------
2012 --
2013 FUNCTION show_payroll
2014 (p_payroll_id IN NUMBER
2015 )
2016 RETURN VARCHAR2
2017 IS
2018 BEGIN
2019
2020 --
2021 -- The revised changes here made for enhancement 3346940
2022 -- obsolete check_payroll_list (it is no longer used).
2023 -- Instead the cached payroll list is accessed directly.
2024 --
2025
2026 --
2027 -- Immediately return true if there is no security.
2028 --
2029 IF (view_all = 'Y' OR view_all_payrolls) THEN
2030 RETURN 'TRUE';
2031 END IF;
2032
2033 --
2034 -- If security evaluation was deferred at logon,
2035 -- or if payroll permissions are unknown for
2036 -- some other reason, use caching on demand to evaluate
2037 -- permissions on the fly.
2038 --
2039 IF NOT hr_security_internal.pay_access_known THEN
2040 --
2041 -- Passing a value to p_what_to_evaluate avoids evaluating
2042 -- permissions for non-pos security criteria.
2043 --
2044 hr_security_internal.evaluate_access
2045 (p_person_id => g_person_id
2046 ,p_user_id => g_user_id
2047 ,p_effective_date => g_effective_date
2048 ,p_sec_prof_rec => g_context
2049 ,p_what_to_evaluate => hr_security_internal.g_PAY_SEC_ONLY);
2050 END IF;
2051
2052 IF hr_security_internal.g_pay_tbl.EXISTS(p_payroll_id) THEN
2053 RETURN 'TRUE';
2054 ELSE
2055 RETURN 'FALSE';
2056 END IF;
2057
2058 END show_payroll;
2059 --
2060 -----------------------------------------------------------------------
2061 --< show_vacancy >-----------------------------------------------------
2062 -----------------------------------------------------------------------
2063 --
2064 FUNCTION show_vacancy
2065 (p_vacancy_id IN NUMBER
2066 ,p_organization_id IN NUMBER
2067 ,p_position_id IN NUMBER
2068 ,p_manager_id IN NUMBER
2069 ,p_security_method IN VARCHAR2
2070 ,p_business_group_id IN VARCHAR2 default null
2071 )
2072 RETURN VARCHAR2
2073 IS
2074 CURSOR CSR_TEAM is
2075 Select 1
2076 from irc_rec_team_members team
2077 ,per_all_people_f per
2078 ,fnd_user usr
2079 where team.vacancy_id = p_vacancy_id
2080 and team.party_id = nvl(per.party_id, usr.customer_id)
2081 and per.person_id(+) = usr.employee_id
2082 and sysdate between per.effective_start_date
2083 and per.effective_end_date
2084 and usr.user_id = g_user_id;
2085 l_dummy number;
2086 l_user_in_team boolean;
2087 l_bg_id number;
2088 BEGIN
2089
2090 -- Bug 5188828
2091 -- Vacancies should be restricted to BG of security profile when profile is local
2092
2093 l_bg_id := get_sec_profile_bg_id;
2094 if (l_bg_id is not null and
2095 p_business_group_id is not null and
2096 l_bg_id <> p_business_group_id ) then
2097 return 'FALSE';
2098 end if;
2099
2100 /*
2101 ** If the security profile is "View All" or the vacancy is
2102 ** "Unsecured" then allow access.
2103 */
2104 IF ( view_all = 'Y'
2105 OR p_security_method = 'U') THEN
2106 return 'TRUE';
2107 END IF;
2108
2109 /*
2110 ** Check for Team security.
2111 */
2112 IF p_security_method = 'T' THEN
2113 open csr_team;
2114 fetch csr_team into l_dummy;
2115 IF csr_team %found THEN
2116 close csr_team;
2117 return 'TRUE';
2118 ELSE
2119 close csr_team;
2120 return 'FALSE';
2121 END IF;
2122 /*
2123 ** Check for Business and Team security.
2124 */
2125 ELSIF nvl(p_security_method,'B') = 'B' THEN
2126 --Added for bug#11850092
2127 /*** Business and Team
2128 changed for bug 7451146
2129 ** First check for Team if no access using Team then check profile
2130 */
2131 open csr_team;
2132 fetch csr_team into l_dummy;
2133 IF csr_team %found THEN
2134 close csr_team;
2135 return 'TRUE';
2136 ELSE
2137 close csr_team;
2138 --Added for bug#11850092
2139
2140
2141 IF p_organization_id IS NULL
2142 AND p_position_id IS NULL
2143 AND ((restrict_by_supervisor AND p_manager_id IS NULL)
2144 OR (NOT restrict_by_supervisor)) THEN
2145 /*
2146 ** The organization and position are NULL and either not using
2147 ** supervisor security or we are using supervisor security but
2148 ** the vacancy manager is NULL so allow access as there is nothing
2149 ** to restrict by.
2150 */
2151 RETURN 'TRUE';
2152 ELSIF (restrict_by_supervisor
2153 AND (p_manager_id IS NULL
2154 OR
2155 ( p_manager_id IS NOT NULL
2156 AND check_vac_person_list(p_manager_id))))
2157 OR (NOT restrict_by_supervisor) THEN
2158 /*
2159 ** We are EITHER using supervisor security and either the manager is
2160 ** NULL or we have access to the manager OR we are not using
2161 ** supervisor security so grant access based Org and Pos.
2162 */
2163 IF ( p_organization_id IS NOT NULL
2164 AND p_position_id IS NULL) THEN
2165 RETURN show_organization(p_organization_id);
2166 ELSIF ( p_organization_id IS NOT NULL
2167 AND p_position_id IS NOT NULL) THEN
2168 IF ( show_organization(p_organization_id) = 'TRUE'
2169 AND show_position(p_position_id) = 'TRUE' )
2170 THEN
2171 RETURN 'TRUE';
2172 -- ELSE
2173 -- RETURN 'FALSE';
2174 END IF;
2175 ELSIF ( p_position_id IS NULL
2176 AND p_organization_id IS NULL) then
2177 RETURN 'TRUE';
2178 ELSE
2179 RETURN 'FALSE';
2180 END IF;
2181 END IF;
2182 /*
2183 ** No access based on org, pos and supervisor so check the team
2184 ** access for this user.
2185 */
2186 --removed the code for bug#11850092
2187 return 'FALSE';
2188 END IF;
2189 END IF; /* security_method = 'B' */
2190 END show_vacancy;
2191 --
2192 -----------------------------------------------------------------------
2193 --< show_record >------------------------------------------------------
2194 -----------------------------------------------------------------------
2195 --
2196 FUNCTION show_record
2197 (p_table_name IN VARCHAR2
2198 ,p_unique_id IN NUMBER
2199 ,p_val1 IN VARCHAR2 DEFAULT NULL
2200 ,p_val2 IN VARCHAR2 DEFAULT NULL
2201 ,p_val3 IN VARCHAR2 DEFAULT NULL
2202 ,p_val4 IN VARCHAR2 DEFAULT NULL
2203 ,p_val5 IN VARCHAR2 DEFAULT NULL
2204 ,p_val6 IN VARCHAR2 DEFAULT NULL -- TOP_PERSON_ID
2205 ,p_val7 IN VARCHAR2 DEFAULT NULL -- TOP PERSON ASSIGNMENT ID
2206 )
2207 RETURN VARCHAR2
2208 IS
2209 BEGIN
2210
2211 --
2212 -- 3676633
2213 --
2214 IF ( globals_need_refreshing ) THEN
2215 hr_signon.initialize_hr_security;
2216 initialise_globals;
2217 END IF;
2218
2219
2220 IF (g_view_no_rows) THEN
2221 RETURN 'FALSE';
2222 END IF;
2223 --
2224 IF p_val6 IS NOT NULL
2225 THEN
2226 g_IS_SSHR := 1;
2227 END IF;
2228
2229 IF (p_table_name = 'PER_ALL_PEOPLE_F') THEN
2230 IF (g_IS_SSHR = 1) THEN
2231 RETURN (show_person(p_person_id => p_unique_id,
2232 p_person_type_id => p_val1,
2233 p_employee_number => p_val2,
2234 p_applicant_number => p_val3,
2235 p_top_person_id => p_val6));
2236 ELSE
2237 RETURN (show_person(p_person_id => p_unique_id,
2238 p_person_type_id => p_val1,
2239 p_employee_number => p_val2,
2240 p_applicant_number => p_val3));
2241 END IF;
2242
2243 ELSIF (p_table_name = 'PER_ALL_ASSIGNMENTS_F') THEN
2244 --
2245 -- Assess assignment level security if the extra parameter
2246 -- is passed in, otherwise assess security at the person
2247 -- level (show_asg_for_per).
2248 --
2249 IF NVL(p_val3, 'N') = 'Y' THEN
2250 IF (g_IS_SSHR = 1) THEN
2251 RETURN (show_assignment(p_assignment_id => p_unique_id,
2252 p_person_id => p_val1,
2253 p_assignment_type => p_val2,
2254 p_top_person_id => p_val6));
2255 ELSE
2256 RETURN (show_assignment(p_assignment_id => p_unique_id,
2257 p_person_id => p_val1,
2258 p_assignment_type => p_val2 ));
2259 END IF;
2260
2261 ELSE
2262 RETURN (show_asg_for_per(p_assignment_id => p_unique_id,
2263 p_person_id => p_val1,
2264 p_assignment_type => p_val2 ));
2265 END IF;
2266 ELSIF (p_table_name = 'HR_ALL_ORGANIZATION_UNITS') THEN
2267 RETURN (show_organization(p_organization_id => p_unique_id ));
2268 ELSIF (p_table_name = 'PER_ALL_POSITIONS' ) THEN
2269 RETURN (show_position(p_position_id => p_unique_id));
2270 ELSIF (p_table_name = 'PAY_ALL_PAYROLLS_F') THEN
2271 RETURN (show_payroll(p_payroll_id => p_unique_id));
2272 ELSIF (p_table_name = 'PER_ALL_VACANCIES') THEN
2273 RETURN (show_vacancy(p_vacancy_id => p_unique_id,
2274 p_organization_id => p_val1,
2275 p_position_id => p_val2,
2276 p_manager_id => p_val3,
2277 p_security_method => p_val4,
2278 p_business_group_id => p_val5));
2279 ELSE
2280 raise_error ('HR_SECURITY : INVALID TABLE NAME');
2281 END IF;
2282 END show_record;
2283 --
2284 -----------------------------------------------------------------------
2285 --< Show_BIS_Record >--------------------------------------------------
2286 -----------------------------------------------------------------------
2287 --
2288 -- Description:
2289 -- This procedure is used from BIS views to restrict records based
2290 -- on the organization.
2291 --
2292 FUNCTION Show_BIS_Record
2293 ( p_org_id in NUMBER
2294 )
2295 RETURN VARCHAR2
2296 IS
2297
2298 l_pv_org_id number;
2299 l_org_id number;
2300
2301 --
2302 -- Checks to see if there are any records in org_access for
2303 -- the current responsibility. fnd_global.resp_appl_id is used
2304 -- to improve index performance.
2305 --
2306 CURSOR c_chk_resp_in_org_access IS
2307 SELECT null
2308 FROM org_access oa
2309 WHERE oa.resp_application_id = g_resp_appl_id
2310 AND oa.responsibility_id = g_resp_id;
2311
2312 --
2313 -- Returns a single record in org_access that matches the
2314 -- current responsibility and p_org_id (if one exists).
2315 -- fnd_global.resp_appl_id is used to improve index performance.
2316 --
2317 CURSOR c_get_org_access_org IS
2318 SELECT oa.organization_id
2319 FROM org_access oa
2320 WHERE oa.resp_application_id = g_resp_appl_id
2321 AND oa.responsibility_id = g_resp_id
2322 AND oa.organization_id = p_org_id;
2323
2324 --
2325 -- Gets all inventory orgs belonging to a particular operating unit.
2326 --
2327 CURSOR c_get_inventory_org (org_id IN NUMBER) IS
2328 SELECT null
2329 FROM hr_organization_information oi
2330 WHERE oi.organization_id = p_org_id
2331 AND oi.org_information_context = 'Accounting Information'
2332 AND to_number(oi.org_information3) = org_id;
2333
2334
2335 BEGIN
2336
2337 --
2338 -- If p_org_id is null then always show the record.
2339 --
2340 IF (p_org_id IS NULL) THEN
2341 RETURN 'TRUE';
2342 END IF;
2343
2344 IF globals_need_refreshing THEN
2345 --
2346 -- Bug 3476231.
2347 -- This bug-fix adds support for all HRMS organization security
2348 -- features. In addition to supporting operating unit and
2349 -- inventory org security features, it supports organization
2350 -- hierarchy, include and exclude orgs and user-based organization
2351 -- security.
2352 -- To do this effectively, it is now necessary to re-initialise
2353 -- security whenever the user, resp, etc. changes, hence the
2354 -- globals_need_refreshing function call.
2355 --
2356 -- This function re-evaluates organization security using the
2357 -- MO: Security Profile instead of HR: Security Profile
2358 -- by calling evaluate_access with the g_mo_contexts (see below).
2359 -- If MO: Security Profile has no value, the context is already
2360 -- set to HR: Security Profile.
2361 --
2362 hr_signon.initialize_hr_security;
2363 initialise_globals;
2364 END IF;
2365
2366 --
2367 -- Immediately return TRUE if there is no security.
2368 --
2369 IF g_mo_context.security_profile_id IS NULL OR
2370 NVL(g_mo_context.view_all_flag, 'Y') = 'Y' OR
2371 NVL(g_mo_context.view_all_organizations_flag, 'Y') = 'Y' OR
2372 NVL(g_mo_context.org_security_mode, 'NONE') = 'NONE'
2373 THEN
2374 RETURN 'TRUE';
2375 END IF;
2376
2377 --
2378 -- Evaluate organization security by operating unit.
2379 --
2380 IF g_mo_context.org_security_mode = 'OU' THEN
2381 --
2382 -- The org security mode is operating unit only. Get the
2383 -- 'MO:Operating Unit' profile option.
2384 --
2385 l_pv_org_id := to_number(fnd_profile.value('ORG_ID'));
2386
2387 --
2388 -- The value of the profile option 'MO:Operating Unit' is
2389 -- validated against p_org_id.
2390 --
2391 IF l_pv_org_id = p_org_id THEN
2392 RETURN 'TRUE';
2393 ELSE
2394 RETURN 'FALSE';
2395 END IF;
2396
2397 --
2398 -- Evaluate organization security by operating unit
2399 -- and inventory organizations.
2400 --
2401 ELSIF g_mo_context.org_security_mode = 'OU_INV' THEN
2402 --
2403 -- The org_security_mode is operating unit and inventory orgs.
2404 -- Get the 'MO:Operating Unit' profile option.
2405 --
2406 l_pv_org_id := to_number(fnd_profile.value('ORG_ID'));
2407
2408 --
2409 -- The value of the profile option 'MO:Operating Unit' is
2410 -- compared against p_org_id.
2411 --
2412 IF l_pv_org_id = p_org_id THEN
2413 RETURN 'TRUE';
2414 END IF;
2415
2416 --
2417 -- Get the org_access rows and see if any orgs match. If there are
2418 -- no matches against p_org_id, FALSE is returned. If no rows
2419 -- exist for the current responsibility, p_org_id is checked against
2420 -- the inventory orgs for the operating unit via hr_organization_units.
2421 --
2422 OPEN c_chk_resp_in_org_access;
2423 FETCH c_chk_resp_in_org_access into l_org_id;
2424
2425 IF c_chk_resp_in_org_access%FOUND THEN
2426 --
2427 -- There are matching records, so see if any orgs in org_access
2428 -- match p_org_id.
2429 --
2430 OPEN c_get_org_access_org;
2431 FETCH c_get_org_access_org INTO l_org_id;
2432
2433 IF c_get_org_access_org%FOUND THEN
2434 CLOSE c_chk_resp_in_org_access;
2435 CLOSE c_get_org_access_org;
2436 RETURN 'TRUE';
2437 ELSE
2438 CLOSE c_chk_resp_in_org_access;
2439 CLOSE c_get_org_access_org;
2440 RETURN 'FALSE';
2441 END IF;
2442
2443 ELSE
2444 --
2445 -- There are no records in org_access that match the responsibility
2446 -- so get the inventory orgs for the operating unit.
2447 --
2448 CLOSE c_chk_resp_in_org_access;
2449
2450 OPEN c_get_inventory_org (l_pv_org_id);
2451 FETCH c_get_inventory_org into l_org_id;
2452
2453 IF c_get_inventory_org%FOUND THEN
2454 CLOSE c_get_inventory_org;
2455 RETURN 'TRUE';
2456 ELSE
2457 CLOSE c_get_inventory_org;
2458 RETURN 'FALSE';
2459 END IF;
2460
2461 END IF;
2462
2463 --
2464 -- Evaluate organization security by organization hierarchy
2465 -- and / or a discrete list of organizations.
2466 --
2467 ELSIF g_mo_context.org_security_mode = 'HIER' THEN
2468 --
2469 -- This flag indicates whether the organization permissions have
2470 -- already been cached using g_mo_context.
2471 --
2472 IF NOT g_mo_org_sec_known THEN
2473 --
2474 -- Re-evaluate organization security using the g_mo_context.
2475 --
2476 hr_security_internal.evaluate_access
2477 (p_person_id => g_mo_person_id
2478 ,p_user_id => g_user_id
2479 ,p_effective_date => g_effective_date
2480 ,p_sec_prof_rec => g_mo_context
2481 ,p_what_to_evaluate => hr_security_internal.g_ORG_SEC_ONLY);
2482
2483 --
2484 -- Set this flag so that the permissions are not re-evaluated
2485 -- with each function call. This flag is reset back to false
2486 -- when the user's logon attributes change (for example, the
2487 -- user changes responsibility).
2488 --
2489 g_mo_org_sec_known := TRUE;
2490
2491 END IF;
2492
2493 IF hr_security_internal.g_org_tbl.EXISTS(p_org_id) THEN
2494 RETURN 'TRUE';
2495 ELSE
2496 RETURN 'FALSE';
2497 END IF;
2498
2499 END IF;
2500
2501 RETURN 'FALSE';
2502
2503 END Show_BIS_Record;
2504 --
2505 -----------------------------------------------------------------------
2506 --< add_assignment >---------------------------------------------------
2507 -----------------------------------------------------------------------
2508 --
2509 procedure add_assignment
2510 (p_person_id number
2511 ,p_assignment_id number) is
2512 begin
2513
2514 IF globals_need_refreshing THEN
2515 hr_signon.initialize_hr_security;
2516 initialise_globals;
2517 END IF;
2518
2519 IF g_context.view_all_flag <> 'Y' AND
2520 NVL(g_context.restrict_on_individual_asg, 'N') = 'Y' AND
2521 p_person_id IS NOT NULL AND
2522 p_assignment_id IS NOT NULL
2523 THEN
2524 hr_security_internal.g_asg_tbl(p_assignment_id) := p_person_id;
2525 END IF;
2526
2527 end add_assignment;
2528 --
2529 -----------------------------------------------------------------------
2530 --< add_person >-------------------------------------------------------
2531 -----------------------------------------------------------------------
2532 --
2533 procedure add_person(p_person_id number) is
2534 --
2535 begin
2536 --
2537 if globals_need_refreshing then
2538 hr_signon.initialize_hr_security;
2539 initialise_globals;
2540 end if;
2541 --
2542 if g_context.view_all_flag <> 'Y' then
2543 --
2544 --g_person_list(p_person_id) := TRUE;--6012095(Forward port of 5985232)
2545 hr_security_internal.g_per_tbl(p_person_id) := TRUE;
2546 --
2547 end if;
2548 --
2549 end add_person;
2550 --
2551 -----------------------------------------------------------------------
2552 --< remove_person >----------------------------------------------------
2553 -----------------------------------------------------------------------
2554 --
2555 procedure remove_person(p_person_id number) is
2556 begin
2557 if g_context.view_all_flag<>'Y' then
2558 -- g_person_list.delete(p_person_id); --6012095(Forward port of 5985232)
2559 hr_security_internal.g_per_tbl.delete(p_person_id);
2560 end if;
2561 end remove_person;
2562 --
2563 -----------------------------------------------------------------------
2564 --< add_organization >-------------------------------------------------
2565 -----------------------------------------------------------------------
2566 --
2567 procedure add_organization
2568 (p_organization_id number,
2569 p_security_Profile_id number) is
2570 begin
2571 --
2572 IF globals_need_refreshing THEN
2573 hr_signon.initialize_hr_security;
2574 initialise_globals;
2575 END IF;
2576 --
2577 IF g_context.view_all_flag <> 'Y' AND
2578 g_context.view_all_organizations_flag = 'N' AND
2579 p_organization_id IS NOT NULL
2580 THEN
2581 hr_security_internal.g_org_tbl(p_organization_id) := TRUE;
2582 END IF;
2583 --
2584 IF (NVL(g_context.top_organization_method, 'S') <> 'U') THEN
2585 hr_security_internal.add_org_to_security_list(p_security_Profile_id,
2586 p_organization_id);
2587 END IF;
2588 --
2589 end add_organization;
2590 --
2591 --
2592 -----------------------------------------------------------------------
2593 --< add_position >-----------------------------------------------------
2594 -----------------------------------------------------------------------
2595 --
2596 procedure add_position
2597 (p_position_id number,
2598 p_security_profile_id number) is
2599 begin
2600 --
2601 IF globals_need_refreshing THEN
2602 hr_signon.initialize_hr_security;
2603 initialise_globals;
2604 END IF;
2605 --
2606 IF g_context.view_all_flag <> 'Y' AND
2607 g_context.view_all_positions_flag = 'N' AND
2608 p_position_id IS NOT NULL
2609 THEN
2610 hr_security_internal.g_pos_tbl(p_position_id) := TRUE;
2611 END IF;
2612 --
2613 IF (NVL(g_context.top_position_method, 'S') <> 'U') THEN
2614 hr_security_internal.add_pos_to_security_list(p_security_profile_id,
2615 p_position_id);
2616 END IF;
2617 --
2618 end add_position;
2619 --
2620 --
2621 -----------------------------------------------------------------------
2622 --< add_payroll >------------------------------------------------------
2623 -----------------------------------------------------------------------
2624 --
2625 procedure add_payroll
2626 (p_payroll_id number) is
2627 begin
2628
2629 IF globals_need_refreshing THEN
2630 hr_signon.initialize_hr_security;
2631 initialise_globals;
2632 END IF;
2633
2634 IF g_context.view_all_flag <> 'Y' AND
2635 g_context.view_all_payrolls_flag = 'N' AND
2636 p_payroll_id IS NOT NULL
2637 THEN
2638 hr_security_internal.g_pay_tbl(p_payroll_id) := TRUE;
2639 END IF;
2640
2641 end add_payroll;
2642 --
2643 -------------------------------------------------------------------------
2644 ---------------------< get_sec_profile_bg_id >---------------------------
2645 -------------------------------------------------------------------------
2646 --
2647 FUNCTION get_sec_profile_bg_id
2648 RETURN NUMBER
2649 is
2650 begin
2651 if fnd_global.user_id <> -1 then
2652
2653 if globals_need_refreshing then
2654 hr_signon.initialize_hr_security;
2655 initialise_globals;
2656 end if;
2657
2658 return g_context.business_group_id;
2659
2660 else
2661 return null;
2662 end if;
2663 end get_sec_profile_bg_id;
2664 --
2665 -------------------------------------------------------------------------
2666 ---------------------< restrict_on_individual_asg >----------------------
2667 -------------------------------------------------------------------------
2668 --
2669 FUNCTION restrict_on_individual_asg
2670 RETURN BOOLEAN
2671 IS
2672
2673 BEGIN
2674 --
2675 -- Ensure the cache is up to date.
2676 --
2677 IF globals_need_refreshing THEN
2678 hr_signon.initialize_hr_security;
2679 initialise_globals;
2680 END IF;
2681
2682 --
2683 -- Return the restrict on individual assignment flag.
2684 --
2685 RETURN (NVL(hr_signon.g_hr_security_profile.restrict_on_individual_asg
2686 ,NVL(g_context.restrict_on_individual_asg, 'N')) = 'Y');
2687
2688 END restrict_on_individual_asg;
2689 --
2690 -------------------------------------------------------------------------
2691 ---------------------< restrict_by_supervisor_flag >---------------------
2692 -------------------------------------------------------------------------
2693 --
2694 FUNCTION restrict_by_supervisor_flag
2695 RETURN VARCHAR2
2696 IS
2697
2698 BEGIN
2699 --
2700 -- Ensure the cache is up to date.
2701 --
2702 IF globals_need_refreshing THEN
2703 hr_signon.initialize_hr_security;
2704 initialise_globals;
2705 END IF;
2706
2707 --
2708 -- Return the type of supervisor security.
2709 --
2710 RETURN (NVL(hr_signon.g_hr_security_profile.restrict_on_individual_asg
2711 ,NVL(g_context.restrict_on_individual_asg, 'N')));
2712
2713 END restrict_by_supervisor_flag;
2714 --
2715 --
2716 PROCEDURE delete_list_for_bg(p_business_group_id NUMBER)
2717 IS
2718 BEGIN
2719 hr_security_internal.delete_security_list_for_bg(p_business_group_id);
2720 END;
2721 --
2722 --
2723 PROCEDURE delete_per_from_list(p_person_id number)
2724 IS
2725 BEGIN
2726 hr_security_internal.delete_per_from_security_list(p_person_id);
2727 END;
2728 --
2729 --
2730 PROCEDURE delete_org_from_list(p_organization_id number)
2731 IS
2732 BEGIN
2733 hr_security_internal.delete_org_from_security_list(p_organization_id);
2734 END;
2735 --
2736 --
2737 PROCEDURE delete_pos_from_list(p_position_id number)
2738 IS
2739 BEGIN
2740 hr_security_internal.delete_pos_from_security_list(p_position_id);
2741 END;
2742 --
2743 --
2744 PROCEDURE delete_payroll_from_list(p_payroll_id number)
2745 IS
2746 BEGIN
2747 hr_security_internal.delete_pay_from_security_list(p_payroll_id);
2748 END;
2749 --
2750 --
2751 -------------------------------------------------------------------------
2752 ---------------------< PACKAGE INITIALIZATION >--------------------------
2753 -------------------------------------------------------------------------
2754 --
2755 BEGIN
2756 --
2757 -- Initialise package global variables
2758 --
2759 --Added for the bug#12774028
2760 --hr_signon.initialize_hr_security;
2761 if NOT hr_general2.reporting_user then
2762 hr_signon.initialize_hr_security;
2763 end if;
2764
2765 Initialise_Globals;
2766 --
2767 END HR_SECURITY;
[Home] [Help]