1 PACKAGE BODY HR_SIGNON AS
2 /* $Header: hrsignon.pkb 120.1.12010000.2 2008/10/30 04:56:00 ubhat ship $ */
3 -----------------------------------------------------------------------
4 -----------------------------------------------------------------------
5 -- PRIVATE FUNCTIONS
6 -----------------------------------------------------------------------
7 -----------------------------------------------------------------------
8 --
9 --------------------------------------------------------------------
10 --< Generic_Error >-------------------------------------------------
11 --------------------------------------------------------------------
12 --
13 -- Description:
14 --
15 --
16 --
17 PROCEDURE Generic_Error
18 (p_routine IN VARCHAR2
19 ,p_errcode IN NUMBER
20 ,p_errmsg IN VARCHAR2
21 )
22 IS
23 BEGIN
24 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
25 fnd_message.set_token('ROUTINE', p_routine);
26 fnd_message.set_token('ERRNO', p_errcode);
27 fnd_message.set_token('REASON', p_errmsg);
28 fnd_message.raise_error;
29 END Generic_Error;
30 --
31 --------------------------------------------------------------------
32 --< Get_BG_ID >-----------------------------------------------------
33 --------------------------------------------------------------------
34 --
35 -- Description:
36 -- This function will return the business group to be used for
37 -- the session.
38 --
39 -- Depending on the setting of the 'ENABLE_SECURITY_GROUPS'
40 -- profile option the business group will be derived in one of
41 -- two ways:
42 --
43 -- ENABLE_SECURITY_GROUPS = 'Y'
44 -- Multi security groups can be used for the same responsibility.
45 -- The business group is derived by querying PER_BUSINESS_GROUPS
46 -- with the security group that the user selected at signon.
47 --
48 -- ENABLE_SECURITY_GROUPS = 'N'
49 -- Single security group model is in use, so the business group
50 -- is found by finding the value of the business group profile
51 -- set on the responsibility the user is logged into.
52 --
53 FUNCTION Get_BG_ID
54 (p_security_group_id IN NUMBER
55 ,p_responsibility_id IN NUMBER
56 ,p_resp_app_id IN NUMBER
57 )
58 RETURN NUMBER
59 IS
60 --
61 l_business_group_id NUMBER DEFAULT NULL;
62 --
63 BEGIN
64 --
65 -- Find the business group (if there is one)
66 --
67 --
68 -- If the enable security groups profile is set to Y then retrieve
69 -- the business group by scanning the per_business_group view with
70 -- the supplied security_group_id
71 --
72 BEGIN
73 SELECT business_group_id
74 INTO l_business_group_id
75 FROM per_business_groups
76 WHERE security_group_id = to_char(p_security_group_id);
77 EXCEPTION
78 WHEN no_data_found THEN
79 l_business_group_id := NULL;
80 END;
81
82 -- This is required to handle model 2 of multi tenancy (PEO)
83 -- i.e. the security group corresponds to the enterprise rather than the
84 -- business group.
85 -- Note:
86 -- 1. For model 1 of multi tenancy (BPO) this is not required as the
87 -- security group corresponds to the business group.
88 -- 2. By the time this method is called the enterprise OLS context should
89 -- have been set.
90 IF l_business_group_id is null AND
91 hr_multi_tenancy_pkg.is_multi_tenant_system THEN
92 l_business_group_id := hr_multi_tenancy_pkg.get_bus_grp_from_sec_grp
93 (p_security_group_id => p_security_group_id);
94 END IF;
95 --
96 RETURN l_business_group_id;
97 --
98 END Get_BG_ID;
99 --
100 --------------------------------------------------------------------
101 --< Check_Business_Group_Lockout >----------------------------------
102 --------------------------------------------------------------------
103 --
104 -- Description:
105 -- Checks if the specified business group has been 'locked out'.
106 --
107 -- This is a feature used by the Data Migrator project for
108 -- Fidelity.
109 --
110 -- When a specific business group is being migrated, a database
111 -- profile is set with its ID - if a user then tries to login to
112 -- this business group whilst the migration is in progress then
113 -- they will not be permitted to enter any HR forms since we null
114 -- the profile options for business group and security profile.
115 --
116 PROCEDURE Check_Business_Group_Lockout
117 (p_business_group_id IN NUMBER
118 ,p_security_group_id IN NUMBER
119 ,p_sg_enabled IN BOOLEAN
120 )
121 IS
122 --
123 -- Data migrator BG lockout cursor
124 --
125 CURSOR c_chk_bg_locked (l_business_group_id IN NUMBER)
126 IS
127 SELECT 'Y'
128 FROM per_business_groups
129 WHERE security_group_id = to_char(p_security_group_id)
130 AND business_group_id = l_business_group_id;
131 --
132 l_bg_lockout NUMBER DEFAULT NULL;
133 l_exists VARCHAR2(1);
134 l_lockout_exception EXCEPTION;
135 --
136 BEGIN
137 --
138 --
139 -- Get the value for the data migratior business group lockout
140 -- profile
141 --
142 l_bg_lockout :=
143 TO_NUMBER(fnd_profile.value('HR_DM_BG_LOCKOUT'));
144 --
145 -- If the profile has been set (ie. a migration is in progress)
146 -- then do some checking
147 --
148 IF l_bg_lockout IS NOT NULL THEN
149 --
150 -- Now check if the business group the current user is trying to
151 -- log into is currently locked (ie. it is being migrated)
152 --
153 IF (p_sg_enabled) THEN
154 OPEN c_chk_bg_locked(l_bg_lockout);
155 --
156 FETCH c_chk_bg_locked INTO l_exists;
157 --
158 IF c_chk_bg_locked%FOUND THEN
159 CLOSE c_chk_bg_locked;
160 --
161 -- Set the BG/SP profiles to null to ensure that no
162 -- business group specific data can be entered by the user
163 --
164 fnd_profile.put
165 (name => 'PER_SECURITY_PROFILE_ID'
166 ,val => NULL
167 );
168 fnd_profile.put
169 (name => 'PER_BUSINESS_GROUP_ID'
170 ,val => NULL
171 );
172 --
173 -- Now raise an exception to break out of this procedure
174 --
175 RAISE l_lockout_exception;
176 END IF;
177 --
178 CLOSE c_chk_bg_locked;
179 ELSE
180 IF (l_bg_lockout = p_business_group_id) THEN
181 --
182 -- Set the BG/SP profiles to null to ensure that no
183 -- business group specific data can be entered by the user
184 --
185 fnd_profile.put
186 (name => 'PER_SECURITY_PROFILE_ID'
187 ,val => NULL
188 );
189 fnd_profile.put
190 (name => 'PER_BUSINESS_GROUP_ID'
191 ,val => NULL
192 );
193 --
194 -- Now raise an exception to break out of this procedure
195 --
196 RAISE l_lockout_exception;
197 END IF;
198 END IF;
199 END IF;
200 --
201 EXCEPTION
202 WHEN OTHERS THEN
203 RAISE;
204 END Check_Business_Group_Lockout;
205 --
206 -----------------------------------------------------------------------
207 --< Get_SP_ID >--------------------------------------------------------
208 -----------------------------------------------------------------------
209 --
210 -- Description:
211 -- This function will return the security profile to be used for
212 -- the session.
213 --
214 -- Unlike in R11 we no longer determine the security profile for a
215 -- session by inspecting the relavent profile option at resp/site
216 -- level.
217 --
218 -- For R11i all security profile assignments are maintained through
219 -- the 'assign security profiles' form, and are stored in the
220 -- PER_SEC_PROFILE_ASSIGNMENTS table.
221 --
222 --
223 FUNCTION Get_SP_ID
224 (p_user_id IN NUMBER
225 ,p_responsibility_id IN NUMBER
226 ,p_resp_app_id IN NUMBER
227 ,p_business_group_id IN NUMBER
228 ,p_security_group_id IN NUMBER
229 )
230 RETURN NUMBER
231 IS
232 --
233 CURSOR c_get_sp_assignment
234 IS
235 SELECT p1.security_profile_id
236 FROM per_sec_profile_assignments p1
237 ,per_security_profiles p2
238 WHERE p1.security_profile_id = p2.security_profile_id
239 AND p1.user_id = p_user_id
240 AND p1.responsibility_id = p_responsibility_id
241 AND p1.responsibility_application_id = p_resp_app_id
242 AND p1.business_group_id = p_business_group_id
243 AND p1.security_group_id = p_security_group_id
244 -- AND SYSDATE BETWEEN p1.start_date -- modified for the bug 6344997
245 AND trunc(SYSDATE) BETWEEN p1.start_date
246 AND NVL(p1.end_date, hr_general.END_OF_TIME);
247 --
248 CURSOR c_get_implicit_sp
249 IS
250 SELECT MIN(security_profile_id)
251 FROM per_security_profiles
252 WHERE business_group_id = p_business_group_id
253 AND view_all_flag = 'Y';
254 --
255 l_security_profile_id NUMBER DEFAULT NULL;
256 --
257 BEGIN
258 --
259 -- First of all try and get the security_profile_id by scanning
260 -- for active security profile assignments
261 --
262 OPEN c_get_sp_assignment;
263 --
264 FETCH c_get_sp_assignment INTO l_security_profile_id;
265 --
266 IF c_get_sp_assignment%NOTFOUND THEN
267 --
268 l_security_profile_id := NULL;
269 --
270 END IF;
271 --
272 CLOSE c_get_sp_assignment;
273 --
274 IF l_security_profile_id IS NOT NULL THEN
275 RETURN l_security_profile_id;
276 END IF;
277 --
278 -- There is no assignment - try and find the id of the view all
279 -- profile for the business group
280 --
281 OPEN c_get_implicit_sp;
282 --
283 FETCH c_get_implicit_sp INTO l_security_profile_id;
284 --
285 IF c_get_implicit_sp%NOTFOUND THEN
286 --
287 l_security_profile_id := NULL;
288 --
289 END IF;
290 --
291 CLOSE c_get_implicit_sp;
292 --
293 RETURN l_security_profile_id;
294 --
295 END Get_SP_ID;
296 --
297 -----------------------------------------------------------------------
298 --< Set_Profile_Values >-----------------------------------------------
299 -----------------------------------------------------------------------
300 --
301 -- Description:
302 -- This procedure sets the values for the PER_SECURITY_PROFILE_ID
303 -- and PER_BUSINESS_GROUP_ID profiles in the server-side profile
304 -- cache.
305 --
306 -- Note: The values set here will override any database settings
307 -- that exist for these profiles.
308 --
309 PROCEDURE Set_Profile_Values
310 (p_business_group_id IN NUMBER
311 ,p_security_profile_id IN NUMBER
312 )
313 IS
314 --
315 BEGIN
316 --
317 fnd_profile.put(name => 'PER_SECURITY_PROFILE_ID'
318 ,val => p_security_profile_id
319 );
320 fnd_profile.put(name => 'PER_BUSINESS_GROUP_ID'
321 ,val => p_business_group_id
322 );
323 --
324 END Set_Profile_Values;
325 --
326 -----------------------------------------------------------------------
327 --< Security_Groups_Enabled >------------------------------------------
328 -----------------------------------------------------------------------
329 --
330 -- Description:
331 -- This function checks whether or not security groups are enabled.
332 -- This is done by primarily checking the profile
333 -- ENABLE_SECURITY_GROUPS. If this is set then an additional check
334 -- is made to ensure that the enable security groups concurrent
335 -- program has been run - this is done by querying the
336 -- per_business_groups view for any business groups (other than the
337 -- setup business group) with a security_group_id of 0.
338 -- Note: any business groups created whilst security groups are not
339 -- enabled have a default value of 0.
340 --
341 FUNCTION Security_Groups_Enabled
342 RETURN VARCHAR2
343 IS
344 --
345 l_dummy VARCHAR2(1);
346 --
347 CURSOR c_check_sec_process_run
348 IS
349 SELECT 'Y'
350 FROM per_business_groups
351 WHERE business_group_id <> 0
352 AND security_group_id = '0';
353 --
354 BEGIN
355 --
356 -- check if the enable_security_groups profile is set to 'Y'
357 IF fnd_profile.value('ENABLE_SECURITY_GROUPS') = 'Y' THEN
358 -- profile is enabled... make sure that the enable security groups
359 -- concurrent process has been run... otherwise return false else
360 -- the HR security initialization code will fail
361 --
362 -- to tell if the concurrent process has been run we can just look
363 -- for any business groups other than the setup business group that
364 -- have a security group ID of 0.
365 OPEN c_check_sec_process_run;
366 --
367 FETCH c_check_sec_process_run INTO l_dummy;
368 --
369 IF c_check_sec_process_run%NOTFOUND THEN
370 --
371 CLOSE c_check_sec_process_run;
372 RETURN 'Y';
373 --
374 ELSE
375 --
376 CLOSE c_check_sec_process_run;
377 RETURN 'N';
378 --
379 END IF;
380 ELSE
381 -- profile is not enabled, so return false
382 RETURN 'N';
383 END IF;
384 --
385 END Security_Groups_Enabled;
386 --
387 -----------------------------------------------------------------------
388 --< Get_Security_Profile_ID >------------------------------------------
389 -----------------------------------------------------------------------
390 --
391 -- Description:
392 -- This function returns the security profile to be used for a
393 -- session, given the user/resp/app/security group that the user
394 -- has signed into.
395 --
396 -- Depending whether or not multiple security groups are enabled
397 -- for the application, the business group and security profile
398 -- profile options may also be setup here.
399 --
400 -- Arguments
401 -- p_user_id User ID for the session
402 -- p_responsibility_id Responsibility ID for the session
403 -- p_application_id Responsibility application ID for the session
404 -- p_security_group_id Security group ID for the session
405 --
406 -- Returns
407 -- Security profile ID to use for the session (this value is used
408 -- to setup the cached global in fnd_client_info)
409 --
410 FUNCTION Get_Security_Profile_ID
411 (p_user_id IN NUMBER
412 ,p_responsibility_id IN NUMBER
413 ,p_application_id IN NUMBER
414 ,p_security_group_id IN NUMBER
415 ) RETURN NUMBER
416 IS
417 --
418 l_business_group_id NUMBER DEFAULT NULL;
419 l_security_profile_id NUMBER DEFAULT NULL;
420 --
421 BEGIN
422 --
423 -- Retrieve the value for the ENABLE_SECURITY_GROUPS profile option.
424 -- If this is enabled then the business group for the session is
425 -- derived from the security group that the user selected at login.
426 -- If security groups are enabled then the security profile assignment
427 -- for the session is found by querying PER_SEC_PROFILE_ASSIGNMENTS
428 -- and if no explicit assignment exists then default view-all profile
429 -- for the business group is assigned.
430 --
431 -- If security groups are not enabled then the business group and
432 -- security profile are assigned by setting the profile options and
433 -- as such no processing will be done here.
434 --
435 IF Security_Groups_Enabled = 'Y' THEN
436 --
437 -- Retrieve the business group to be used for the session.
438 --
439 l_business_group_id
440 := Get_BG_ID
441 (p_security_group_id => p_security_group_id
442 ,p_responsibility_id => p_responsibility_id
443 ,p_resp_app_id => p_application_id
444 );
445 --
446 IF l_business_group_id IS NOT NULL THEN
447 --
448 -- Check if the business group has been 'locked out'.
449 -- This is a feature used by the Data Migrator project for Fidelity.
450 -- When a specific business group is being migrated, a database
451 -- profile is set with its ID - if a user then tries to login to this
452 -- business group whilst the migration is in progress then they will
453 -- not be permitted to enter any HR forms since we null the profile
454 -- options for business group and security profile.
455 --
456 Check_Business_Group_Lockout
457 (p_business_group_id => l_business_group_id
458 ,p_security_group_id => p_security_group_id
459 ,p_sg_enabled => TRUE
460 );
461 --
462 -- Get the security profile for the session.
463 -- Unlike in R11 we no longer determine the security profile for a
464 -- session by inspecting the relavent profile option at resp/site
465 -- level.
466 -- For R11i all security profile assignments are maintained through
467 -- the 'assign security profiles' form, and are stored in the
468 -- PER_SEC_PROFILE_ASSIGNMENTS table.
469 --
470 l_security_profile_id
471 := Get_SP_ID
472 (p_user_id => p_user_id
473 ,p_responsibility_id => p_responsibility_id
474 ,p_resp_app_id => p_application_id
475 ,p_business_group_id => l_business_group_id
476 ,p_security_group_id => p_security_group_id
477 );
478 --
479 -- Store the retrieved values for the BG/SP profile options in the
480 -- server-side cache.
481 --
482 Set_Profile_Values
483 (p_business_group_id => l_business_group_id
484 ,p_security_profile_id => l_security_profile_id
485 );
486 ELSE
487 --
488 -- The site level defaults will be used for this session so
489 -- retrieve the value from the profile cache now..
490 --
491 l_security_profile_id
492 := TO_NUMBER(fnd_profile.value('PER_SECURITY_PROFILE_ID'));
493 --
494 END IF;
495 ELSE
496 BEGIN
497 Check_Business_Group_Lockout
498 (p_business_group_id =>
499 TO_NUMBER(fnd_profile.value('PER_BUSINESS_GROUP_ID'))
500 ,p_security_group_id => 0
501 ,p_sg_enabled => FALSE
502 );
503 --
504 l_security_profile_id
505 := TO_NUMBER(fnd_profile.value('PER_SECURITY_PROFILE_ID'));
506 EXCEPTION
507 WHEN OTHERS THEN
508 NULL;
509 END;
510 END IF;
511 --
512 RETURN l_security_profile_id;
513 --
514 END Get_Security_Profile_ID;
515 --
516 -----------------------------------------------------------------------
517 ------------------------< derive_legislation >-------------------------
518 -----------------------------------------------------------------------
519 --
520 PROCEDURE derive_legislation IS
521 --
522 l_leg_code varchar2(150);
523 l_business_group_id number(15);
524 --
525 BEGIN
526 --
527 l_leg_code := hr_api.return_legislation_code(
528 fnd_profile.value('PER_BUSINESS_GROUP_ID'));
529 --
530 --
531 hr_api.set_legislation_context(l_leg_code);
532 --
533 EXCEPTION
534 WHEN NO_DATA_FOUND THEN
535 null;
536 --
537 END derive_legislation;
538 --
539 -----------------------------------------------------------------------
540 -----------------------------------------------------------------------
541 -- PUBLIC FUNCTIONS
542 -----------------------------------------------------------------------
543 -----------------------------------------------------------------------
544 --
545 -----------------------------------------------------------------------
546 --< Initialize_HR_Security >-------------------------------------------
547 -----------------------------------------------------------------------
548 --
549 -- Description:
550 -- This procedure is called during initialization of applications
551 -- that use the HR secure user functionality. It determines the
552 -- security profile and business group to use for the session based
553 -- on the user_id, responsibility_id, application_id and
554 -- security_group_id of the session and set the appropriate profile
555 -- values.
556 --
557 -- Arguments
558 -- None.
559 --
560 PROCEDURE Initialize_HR_Security
561 IS
562 --
563 l_security_profile_id NUMBER;
564 l_null_row per_security_profiles%rowtype;
565 --
566 BEGIN
567
568
569 --
570 -- 2876315
571 --
572 session_context := fnd_global.session_context ;
573
574 -- HR Multi Tenancy Addition:- Bug 7501793
575 if hr_multi_tenancy_pkg.is_multi_tenant_system then
576 hr_multi_tenancy_pkg.set_context(null);
577 end if;
578
579 --
580 -- Reset the package global just in case the initialization fails
581 -- since we want to prevent the old value being used in a different
582 -- session.
583 --
584 g_hr_security_profile := l_null_row;
585 --
586 -- Get the security profile to use for this session - note that this
587 -- function call will transparently set the profile values for
588 -- PER_SECURITY_PROFILE_ID and PER_BUSINESS_GROUP_ID.
589 --
590 l_security_profile_id :=
591 Get_Security_Profile_ID
592 (fnd_global.user_id
593 ,fnd_global.resp_id
594 ,fnd_global.resp_appl_id
595 ,fnd_global.security_group_id
596 );
597 --
598 -- If a security profile was found/returned then populate the package
599 -- level global variable with the appropriate row for the ID
600 -- from per_security_profiles.
601 --
602 IF l_security_profile_id IS NOT NULL THEN
603 BEGIN
604 select *
605 into hr_signon.g_hr_security_profile
606 from per_security_profiles
607 where security_profile_id = l_security_profile_id;
608 EXCEPTION
609 when NO_DATA_FOUND then
610 --
611 -- Ignore NO_DATA_FOUND error if context=0
612 --
613 if l_security_profile_id = 0 then
614 null;
615 else
616 raise;
617 end if;
618 END;
619 ELSE
620 null;
621 END IF;
622 --
623 -- call private procedure to identify legislation_code from bg_id
624 -- and call hr_api to setup the application context
625 -- HR_ESSION_DATA namespace LEG_CODE with the session's legislation_code.
626 --
627 derive_legislation;
628
629 EXCEPTION
630 WHEN OTHERS THEN
631 generic_error('HR_SIGNON.INITIALIZE_HR_SECURITY', sqlcode, sqlerrm);
632 END Initialize_HR_Security;
633 --
634 END HR_SIGNON;
[Home] [Help]