[Home] [Help]
PACKAGE BODY: APPS.HR_AUTH_INT
Source
1 package body hr_auth_int as
2 /* $Header: hrathint.pkb 120.0 2005/05/30 22:53:58 appldev noship $ */
3
4
5 -- Logging code data
6 g_module CONSTANT VARCHAR2(80) :=
7 'per.pl_sql.hr_auth_int';
8
9 FUNCTION get_sso_user
10 (
11 p_user_id IN NUMBER
12 )
13 RETURN VARCHAR2 IS
14
15 l_procedure VARCHAR2(31) := 'get_sso_user';
16 l_user VARCHAR2(60) := NULL;
17
18 CURSOR csr_user IS
19 SELECT user_name
20 FROM fnd_user
21 WHERE user_id = p_user_id;
22
23 --
24 BEGIN
25 --
26 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
27 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
28 MODULE => g_module || '.' || l_procedure,
29 MESSAGE => 'Entering ' || l_procedure);
30 END IF;
31
32
33 OPEN csr_user;
34 FETCH csr_user INTO l_user;
35 CLOSE csr_user;
36
37 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
38 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
39 MODULE => g_module || '.' || l_procedure,
40 MESSAGE => 'sso user is ' || l_user);
41 END IF;
42
43 IF l_user IS NULL THEN
44 IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
45 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_ERROR,
46 MODULE => g_module || '.' || l_procedure,
47 MESSAGE => 'sso user is not found for user_id ' ||
48 p_user_id);
49 END IF;
50 END IF;
51
52 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
53 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
54 MODULE => g_module || '.' || l_procedure,
55 MESSAGE => 'Exiting ' || l_procedure);
56 END IF;
57 RETURN(l_user);
58
59 --
60 END get_sso_user;
61 --
62
63
64
65 FUNCTION get_country
66 (
67 p_person_id IN NUMBER
68 )
69 RETURN VARCHAR2 IS
70
71 CURSOR csr_country IS
72 SELECT bg.legislation_code
73 FROM per_all_people_f ppl,
74 per_business_groups bg
75 WHERE ppl.person_id = p_person_id
76 AND SYSDATE BETWEEN ppl.effective_start_date
77 AND ppl.effective_end_date
78 AND ppl.business_group_id = bg.business_group_id;
79
80 l_procedure VARCHAR2(31) := 'get_country';
81 l_country VARCHAR2(60) := NULL;
82
83 --
84 BEGIN
85 --
86 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
87 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
88 MODULE => g_module || '.' || l_procedure,
89 MESSAGE => 'Entering ' || l_procedure);
90 END IF;
91
92 OPEN csr_country;
93 FETCH csr_country INTO l_country;
94 CLOSE csr_country;
95
96 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
97 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
98 MODULE => g_module || '.' || l_procedure,
99 MESSAGE => 'country is ' || l_country);
100 END IF;
101
102 IF l_country IS NULL THEN
103 IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
104 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_ERROR,
105 MODULE => g_module || '.' || l_procedure,
106 MESSAGE => 'No country associated with person_id ' ||
107 p_person_id);
108 END IF;
109 END IF;
110
111 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
112 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
113 MODULE => g_module || '.' || l_procedure,
114 MESSAGE => 'Exiting ' || l_procedure);
115 END IF;
116
117 RETURN(l_country);
118
119 --
120 END get_country;
121 --
122
123
124 FUNCTION get_url_from_profile
125 (
126 p_country IN VARCHAR2
127 )
128 RETURN VARCHAR2 IS
129
130 l_procedure VARCHAR2(31) := 'get_url_from_profile';
131 l_url VARCHAR2(2000);
132
133 --
134 BEGIN
135 --
136 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
137 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
138 MODULE => g_module || '.' || l_procedure,
139 MESSAGE => 'Entering ' || l_procedure);
140 END IF;
141
142 l_url := fnd_profile.value('HR_AUTHORIA_URL_' || p_country);
143
144 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
145 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
146 MODULE => g_module || '.' || l_procedure,
147 MESSAGE => 'url is ' || l_url);
148 END IF;
149
150 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
151 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
152 MODULE => g_module || '.' || l_procedure,
153 MESSAGE => 'Exiting ' || l_procedure);
154 END IF;
155
156 RETURN(l_url);
157
158 --
159 END get_url_from_profile;
160 --
161
162 PROCEDURE update_sso_url
163 (
164 p_url IN VARCHAR2,
165 p_app_id IN VARCHAR2
166 ) IS
167
168 l_procedure VARCHAR2(31) := 'update_sso_url';
169 l_url VARCHAR2(2000);
170 l_APP_NAME varchar2(80);
171 l_APPTYPE varchar2(80);
172 l_APPURL varchar2(80);
173 l_LOGOUT_URL varchar2(80);
174 l_USERFIELD varchar2(80);
175 l_PWDFIELD varchar2(80);
176 l_AUTHNEEDED varchar2(80);
177 l_APP_USER varchar2(80);
178 l_APP_PWD varchar2(80);
179 l_FNAME1 varchar2(80) := NULL;
180 l_FVAL1 varchar2(80) := NULL;
181 l_FNAME2 varchar2(80) := NULL;
182 l_FVAL2 varchar2(80) := NULL;
183 l_FNAME3 varchar2(80) := NULL;
184 l_FVAL3 varchar2(80) := NULL;
185 l_FNAME4 varchar2(80) := NULL;
186 l_FVAL4 varchar2(80) := NULL;
187 l_FNAME5 varchar2(80) := NULL;
188 l_FVAL5 varchar2(80) := NULL;
189 l_FNAME6 varchar2(80) := NULL;
190 l_FVAL6 varchar2(80) := NULL;
191 l_FNAME7 varchar2(80) := NULL;
192 l_FVAL7 varchar2(80) := NULL;
193 l_FNAME8 varchar2(80) := NULL;
194 l_FVAL8 varchar2(80) := NULL;
195 l_FNAME9 varchar2(80) := NULL;
196 l_FVAL9 varchar2(80) := NULL;
197
198 --
199 BEGIN
200 --
201
202 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
203 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
204 MODULE => g_module || '.' || l_procedure,
205 MESSAGE => 'Entering ' || l_procedure);
206 END IF;
207
208 -- get existing details
209
210 hr_sso_utl.PSTORE_GET_APP_INFO(
211 p_APP_ID,
212 l_APP_NAME,
213 l_APPTYPE,
214 l_APPURL,
215 l_LOGOUT_URL,
216 l_USERFIELD,
217 l_PWDFIELD,
218 l_AUTHNEEDED,
219 l_FNAME1, l_FVAL1,
220 l_FNAME2, l_FVAL2,
221 l_FNAME3, l_FVAL3,
222 l_FNAME4, l_FVAL4,
223 l_FNAME5, l_FVAL5,
224 l_FNAME6, l_FVAL6,
225 l_FNAME7, l_FVAL7,
226 l_FNAME8, l_FVAL8,
227 l_FNAME9, l_FVAL9);
228
229 -- update with new info
230
231 hr_sso_utl.PSTORE_MODIFY_APP_INFO(
232 p_APP_ID,
233 l_APP_NAME,
234 l_APPTYPE,
235 p_url,
236 l_LOGOUT_URL,
237 l_USERFIELD,
238 l_PWDFIELD,
239 l_AUTHNEEDED,
240 l_FNAME1, l_FVAL1,
241 l_FNAME2, l_FVAL2,
242 l_FNAME3, l_FVAL3,
243 l_FNAME4, l_FVAL4,
244 l_FNAME5, l_FVAL5,
245 l_FNAME6, l_FVAL6,
246 l_FNAME7, l_FVAL7,
247 l_FNAME8, l_FVAL8,
248 l_FNAME9, l_FVAL9);
249
250 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
251 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
252 MODULE => g_module || '.' || l_procedure,
253 MESSAGE => 'Exiting ' || l_procedure);
254 END IF;
255
256 --
257 END update_sso_url;
258 --
259
260
261
262 FUNCTION get_redirect_url
263 (
264 p_sso_user IN VARCHAR2,
265 p_person_id IN NUMBER,
266 p_page IN VARCHAR2,
267 p_primary_obj IN VARCHAR2
268 )
269 RETURN VARCHAR2 IS
270 --
271 l_procedure VARCHAR2(31) := 'get_redirect_url';
272 l_url VARCHAR2(2000);
273 l_app_id varchar2(80) := NULL;
274 l_APP_USER varchar2(80);
275 l_APP_PWD varchar2(80);
276 l_FNAME1 varchar2(80) := NULL;
277 l_FVAL1 varchar2(80) := NULL;
278 l_FNAME2 varchar2(80) := NULL;
279 l_FVAL2 varchar2(80) := NULL;
280 l_FNAME3 varchar2(80) := NULL;
281 l_FVAL3 varchar2(80) := NULL;
282 l_FNAME4 varchar2(80) := NULL;
283 l_FVAL4 varchar2(80) := NULL;
284 l_FNAME5 varchar2(80) := NULL;
285 l_FVAL5 varchar2(80) := NULL;
286 l_FNAME6 varchar2(80) := NULL;
287 l_FVAL6 varchar2(80) := NULL;
288 l_FNAME7 varchar2(80) := NULL;
289 l_FVAL7 varchar2(80) := NULL;
290 l_FNAME8 varchar2(80) := NULL;
291 l_FVAL8 varchar2(80) := NULL;
292 l_FNAME9 varchar2(80) := NULL;
293 l_FVAL9 varchar2(80) := NULL;
294 l_USER_PREFS varchar2(80) := NULL;
295 l_password varchar2(80) := NULL;
296 l_country varchar2(30);
297 l_error varchar2(2000);
298
299 --
300 BEGIN
301 --
302
303 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
304 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
305 MODULE => g_module || '.' || l_procedure,
306 MESSAGE => 'Entering ' || l_procedure);
307 END IF;
308
309 -- find the external app_id
310 l_country := get_country(p_person_id);
311 IF l_country IS NULL THEN
312 -- qqq
313 -- really should be a message
314 l_error := 'Unable to identify a country associated with the person.';
315 END IF;
316
317 IF l_error IS NULL THEN
318
319 BEGIN
320 l_app_id := hr_external_application.get_app_id('Authoria-' ||
321 l_country);
322
323 EXCEPTION
324 WHEN OTHERS THEN
325 IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
326 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_ERROR,
327 MODULE => g_module || '.' || l_procedure,
328 MESSAGE => 'Unable to determine SSO external ' ||
329 'application - ' || sqlerrm);
330 END IF;
331 END;
332
333 IF l_app_id IS NULL THEN
334 -- qqq
335 -- really should be a message
336 l_error := 'Unable to determine the external application details ' ||
337 'via SSO for the country ' || NVL(l_country, 'null') || '.';
338 END IF;
339 END IF;
340
341
342 IF l_error IS NULL THEN
343
344 -- set up the URL for the country
345 update_sso_url(get_url_from_profile(l_country),
346 l_app_id );
347
348 BEGIN
349
350 -- get the existing (if any) user info for this app
351
352 hr_sso_utl.PSTORE_GET_USERINFO(
353 l_APP_ID,
354 p_sso_user,
355 l_APP_USER,
356 l_APP_PWD,
357 l_FNAME1, l_FVAL1,
358 l_FNAME2, l_FVAL2,
359 l_FNAME3, l_FVAL3,
360 l_FNAME4, l_FVAL4,
361 l_FNAME5, l_FVAL5,
362 l_FNAME6, l_FVAL6,
363 l_FNAME7, l_FVAL7,
364 l_FNAME8, l_FVAL8,
365 l_FNAME9, l_FVAL9,
366 l_USER_PREFS);
367
368 EXCEPTION
369 WHEN OTHERS THEN
370 IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
371 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_ERROR,
372 MODULE => g_module || '.' || l_procedure,
373 MESSAGE => 'No user found, using defaults');
374 END IF;
375 END;
376
377 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
378 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
379 MODULE => g_module || '.' || l_procedure,
380 MESSAGE => 'APP_USER is ' || l_APP_USER);
381 END IF;
382
383 -- set SSO details
384 IF (l_APP_PWD IS NULL) THEN
385
386 -- create user for the first time
387 -- can not store a null password in the SSO
388 -- so user can not already exist
389 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
390 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
391 MODULE => g_module || '.' || l_procedure,
392 MESSAGE => 'creating new user');
393 END IF;
394
395 -- generate a password according to the current method
396 l_password := get_password(p_person_id);
397
398 BEGIN
399 hr_sso_utl.pstore_add_userinfo(
400 p_app_id => l_APP_ID,
401 p_ssouser => p_SSO_USER,
402 p_app_user => p_person_id,
403 p_app_pwd => l_password,
404 p_fname1 => 'command',
405 p_fval1 => 'showpage',
406 p_fname2 => 'page',
407 p_fval2 => p_page,
408 p_fname3 => 'primary_obj',
409 p_fval3 => p_primary_obj,
410 p_fname4 => l_FNAME4,
411 p_fval4 => l_FVAL4,
412 p_fname5 => l_FNAME5,
413 p_fval5 => l_FVAL5,
414 p_fname6 => l_FNAME6,
415 p_fval6 => l_FVAL6,
416 p_fname7 => l_FNAME7,
417 p_fval7 => l_FVAL7,
418 p_fname8 => l_FNAME8,
419 p_fval8 => l_FVAL8,
420 p_fname9 => l_FNAME9,
421 p_fval9 => l_FVAL9,
422 p_user_prefs => l_USER_PREFS);
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
427 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_ERROR,
428 MODULE => g_module || '.' || l_procedure,
429 MESSAGE => 'new user error - ' || sqlerrm);
430 END IF;
431 l_error := 'Error creating new user record in SSO - ' ||
432 sqlerrm;
433 END;
434 ELSE
435 -- updating current user
436
437 -- generate a password according to the current method
438 l_password := get_password(p_person_id);
439
440 BEGIN
441 hr_sso_utl.pstore_modify_userinfo(
442 p_app_id => l_APP_ID,
443 p_ssouser => p_SSO_USER,
444 p_app_user => p_person_id,
445 p_app_pwd => l_password,
446 p_fname1 => 'command',
447 p_fval1 => 'showpage',
448 p_fname2 => 'page',
449 p_fval2 => p_page,
450 p_fname3 => 'primary_obj',
451 p_fval3 => p_primary_obj,
452 p_fname4 => l_FNAME4,
453 p_fval4 => l_FVAL4,
454 p_fname5 => l_FNAME5,
455 p_fval5 => l_FVAL5,
456 p_fname6 => l_FNAME6,
457 p_fval6 => l_FVAL6,
458 p_fname7 => l_FNAME7,
459 p_fval7 => l_FVAL7,
460 p_fname8 => l_FNAME8,
461 p_fval8 => l_FVAL8,
462 p_fname9 => l_FNAME9,
463 p_fval9 => l_FVAL9,
464 p_user_prefs => l_USER_PREFS);
465 EXCEPTION
466 WHEN OTHERS THEN
467 IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
468 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_ERROR,
469 MODULE => g_module || '.' || l_procedure,
470 MESSAGE => 'existing user error - ' || sqlerrm);
471 END IF;
472 l_error := 'Error modifying existing user record in SSO - ' ||
473 sqlerrm;
474 END;
475 END IF;
476
477 COMMIT;
478
479 -- build url
480 l_url :=
481 hr_sso_utl.get_sso_query_path('wwsso_app_admin.fapp_process_login')
482 || '?p_app_id=' || l_app_id;
483
484 END IF;
485
486 -- display errors
487 IF l_error IS NOT NULL THEN
488 l_url := 'ERROR: ' || l_error;
489 END IF;
490
491 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
492 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
493 MODULE => g_module || '.' || l_procedure,
494 MESSAGE => 'url is ' || l_url);
495 END IF;
496
497 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
498 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
499 MODULE => g_module || '.' || l_procedure,
500 MESSAGE => 'Exiting ' || l_procedure);
501 END IF;
502
503
504 RETURN(l_url);
505
506 EXCEPTION
507 WHEN OTHERS THEN
508 IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
509 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_ERROR,
510 MODULE => g_module || '.' || l_procedure,
511 MESSAGE => 'Error whilst determining redirection URL' ||
512 ' - ' || sqlerrm);
513 END IF;
514
515
516 --
517 END get_redirect_url;
518 --
519
520 FUNCTION get_url
521 (
522 p_provider IN VARCHAR2,
523 p_user_id IN NUMBER,
524 p_person_id IN NUMBER,
525 p_page IN VARCHAR2,
526 p_primary_obj IN VARCHAR2
527 )
528 RETURN VARCHAR2 IS
529 --
530 pragma autonomous_transaction;
531 --
532
533 l_procedure VARCHAR2(31) := 'get_url';
534 l_sso_user VARCHAR2(60) := NULL;
535 l_url VARCHAR2(2000);
536 l_error VARCHAR2(2000) := NULL;
537
538 --
539 BEGIN
540 --
541 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
542 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
543 MODULE => g_module || '.' || l_procedure,
544 MESSAGE => 'Entering ' || l_procedure);
545 END IF;
546
547 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
548 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
549 MODULE => g_module || '.' || l_procedure,
550 MESSAGE => 'provider is ' || p_provider);
551 END IF;
552
553 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
554 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
555 MODULE => g_module || '.' || l_procedure,
556 MESSAGE => 'page is ' || p_page);
557 END IF;
558
559 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
560 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
561 MODULE => g_module || '.' || l_procedure,
562 MESSAGE => 'user_id is ' || p_user_id);
563 END IF;
564
565 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
566 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
567 MODULE => g_module || '.' || l_procedure,
568 MESSAGE => 'page is ' || p_page);
569 END IF;
570
571 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
572 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
573 MODULE => g_module || '.' || l_procedure,
574 MESSAGE => 'primary_obj is ' || p_primary_obj);
575 END IF;
576
577
578 -- get sso user
579 l_sso_user := get_sso_user(p_user_id);
580 IF l_sso_user IS NULL THEN
581 l_error := 'Unable to determine the SSO user for the current user.';
582 END IF;
583
584 IF l_error IS NULL THEN
585 -- get redirection URL (or error text)
586 -- after adding 'set,,' to primary obj string
587 l_url := get_redirect_url(l_sso_user,
588 p_person_id,
589 p_page,
590 'set,,' || p_primary_obj);
591
592 IF SUBSTR(l_url,5) = 'ERROR' THEN
593 l_error := l_url;
594 END IF;
595
596 END IF;
597
598 IF l_error IS NOT NULL THEN
599 l_url := 'ERROR : APPS-47368 : ' || l_error ||
600 ' Examine FND logging information for more details.';
601 END IF;
602
603 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
604 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
605 MODULE => g_module || '.' || l_procedure,
606 MESSAGE => 'Exiting ' || l_procedure);
607 END IF;
608
609 RETURN(l_url);
610
611 --
612 END get_url;
613 --
614
615 FUNCTION get_url
616 (
617 p_provider IN VARCHAR2,
618 p_user_id IN VARCHAR2,
619 p_person_id IN VARCHAR2,
620 p_page IN VARCHAR2,
621 p_primary_obj IN VARCHAR2
622 )
623 RETURN VARCHAR2 IS
624 --
625 pragma autonomous_transaction;
626 --
627
628 l_procedure VARCHAR2(31) := 'get_url';
629 l_url VARCHAR2(2000);
630 l_person_id NUMBER(15);
631 l_user_id NUMBER(15);
632
633 --
634 BEGIN
635 --
636 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
637 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
638 MODULE => g_module || '.' || l_procedure,
639 MESSAGE => 'Entering ' || l_procedure);
640 END IF;
641
642 -- need to decrypt encrypted person_id
643 l_person_id := hr_sso_utl.decrypt_ps_username(p_person_id);
644
645 -- need to decrypt encrypted user_id
646 l_user_id := hr_sso_utl.decrypt_ps_username(p_user_id);
647
648 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
649 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
650 MODULE => g_module || '.' || l_procedure,
651 MESSAGE => 'decrypted person_id is ' || l_person_id);
652 END IF;
653
654 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
655 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
656 MODULE => g_module || '.' || l_procedure,
657 MESSAGE => 'decrypted user_id is ' || l_user_id);
658 END IF;
659
660 -- now call main code
661
662 l_url := get_url(p_provider,
663 l_user_id,
664 l_person_id,
665 p_page,
666 p_primary_obj);
667
668 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
669 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
670 MODULE => g_module || '.' || l_procedure,
671 MESSAGE => 'Exiting ' || l_procedure);
672 END IF;
673
674 RETURN(l_url);
675
676 --
677 END get_url;
678 --
679
680
681 FUNCTION get_password
682 (
683 p_person_id IN NUMBER
684 )
685 RETURN VARCHAR2 IS
686
687 l_procedure VARCHAR2(31) := 'get_password';
688 l_password VARCHAR2(60);
689
690
691 --
692 BEGIN
693 --
694
695 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
696 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
697 MODULE => g_module || '.' || l_procedure,
698 MESSAGE => 'Entering ' || l_procedure);
699 END IF;
700
701 -- generate the password from the person_id passed
702 -- encrypted as a password
703 -- using the first 8 characters only - SSO limitation
704
705 l_password := substrb(icx_call.encrypt3(p_person_id),1,8);
706
707 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
708 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
709 MODULE => g_module || '.' || l_procedure,
710 MESSAGE => 'Exiting ' || l_procedure);
711 END IF;
712
713 RETURN(l_password);
714
715 --
716 END get_password;
717 --
718
719
720 FUNCTION get_page
721 (
722 p_plip_id IN NUMBER,
723 p_pl_id IN NUMBER,
724 p_ler_id IN NUMBER
725 )
726 RETURN VARCHAR2 IS
727
728 --
729
730 l_procedure VARCHAR2(31) := 'get_page';
731 l_target_page VARCHAR2(2000) := NULL;
732 l_open_flag VARCHAR2(1) := 'N';
733
734 CURSOR csr_ler IS
735 SELECT 'Y'
736 FROM ben_ler_f
737 WHERE sysdate BETWEEN effective_start_date
738 AND effective_end_date
739 AND ler_id = p_ler_id
740 AND typ_cd = 'SCHEDDO';
741
742 CURSOR csr_page IS
743 SELECT target_page
744 FROM hr_authoria_mappings
745 WHERE pl_id = p_pl_id
746 AND NVL(plip_id,-924926578) = NVL(p_plip_id,-924926578)
747 AND open_enrollment_flag = l_open_flag;
748
749 --
750 BEGIN
751 --
752
753 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
754 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
755 MODULE => g_module || '.' || l_procedure,
756 MESSAGE => 'Entering ' || l_procedure);
757 END IF;
758
759 -- Find the Life event type
760 OPEN csr_ler;
761 FETCH csr_ler INTO l_open_flag;
762 IF csr_ler%NOTFOUND THEN
763 l_open_flag := 'N';
764 END IF;
765 CLOSE csr_ler;
766
767 -- now see if we have a match
768 OPEN csr_page;
769 FETCH csr_page INTO l_target_page;
770 CLOSE csr_page;
771
772 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
773 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
774 MODULE => g_module || '.' || l_procedure,
775 MESSAGE => 'target page is ' || l_target_page);
776 END IF;
777
778 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
779 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
780 MODULE => g_module || '.' || l_procedure,
781 MESSAGE => 'Exiting ' || l_procedure);
782 END IF;
783
784 RETURN(l_target_page);
785
786 --
787 END get_page;
788 --
789
790 FUNCTION get_anchor_tag_ss
791 (
792 p_pl_id in number,
793 p_person_id in number,
794 p_plan_name in varchar2,
795 p_ler_id in number,
796 p_plip_id in number default null,
797 p_plan_url in varchar2 default null,
798 p_primary_obj_context in varchar2 default null
799 )
800 RETURN varchar2 IS
801
802 --
803
804 l_procedure VARCHAR2(31) := 'get_anchor_tag_ss';
805 l_url VARCHAR2(2000);
806 l_anchor_tag VARCHAR2(2500);
807
808 --
809 BEGIN
810 --
811 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
812 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
813 MODULE => g_module || '.' || l_procedure,
814 MESSAGE => 'Entering ' || l_procedure);
815 END IF;
816
817 l_url := get_url_ss(p_pl_id => p_pl_id,
818 p_person_id => p_person_id,
819 p_plan_name => p_plan_name,
820 p_ler_id => p_ler_id,
821 p_plip_id => p_plip_id,
822 p_plan_url => p_plan_url,
823 p_primary_obj_context => p_primary_obj_context);
824
825 IF l_url is not null THEN
826 l_anchor_tag := '<a href="'||l_url||'" target="_blank">'||p_plan_name||'</a>';
827 ELSE
828 l_anchor_tag := p_plan_name;
829 END IF;
830
831 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
832 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
833 MODULE => g_module || '.' || l_procedure,
834 MESSAGE => 'anchor tag is ' || l_anchor_tag);
835 END IF;
836
837 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
838 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
839 MODULE => g_module || '.' || l_procedure,
840 MESSAGE => 'Exiting ' || l_procedure);
841 END IF;
842
843 RETURN(l_anchor_tag);
844
845 --
846 EXCEPTION
847 --
848 WHEN others THEN
849 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
850 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
851 MODULE => g_module || '.' || l_procedure,
852 MESSAGE => 'Exiting with Exception' || l_procedure);
853 END IF;
854 RETURN p_plan_name;
855 --
856 END get_anchor_tag_ss;
857 --
858
859 FUNCTION get_url_ss
860 (
861 p_pl_id in number,
862 p_person_id in number,
863 p_plan_name in varchar2,
864 p_ler_id in number,
865 p_plip_id in number default null,
866 p_plan_url in varchar2 default null,
867 p_primary_obj_context in varchar2 default null
868 )
869 RETURN varchar2 IS
870
871 --
872
873 l_procedure VARCHAR2(31) := 'get_url_ss';
874
875 l_hr_authoria_enabled fnd_profile_option_values.profile_option_value%type;
876 l_hr_kpi_agent fnd_profile_option_values.profile_option_value%type;
877 l_apps_servlet_agent fnd_profile_option_values.profile_option_value%type;
878 l_hr_kpi_servlet fnd_profile_option_values.profile_option_value%type;
879 l_dbc_filename varchar2(255);
880
881 l_provider varchar2(50) := 'Authoria';
882 l_request varchar2(50) := '?request=GETREDIRECT:';
883
884 l_encrypted_person_id varchar2(100);
885 l_page_name varchar2(200);
886
887 l_url varchar2(2000);
888
889 l_separator varchar2(2) := '::';
890
891 l_ret_val VARCHAR2(2000);
892
893 l_user_id fnd_user.user_id%type;
894 l_encrypted_user_id varchar2(100);
895
896 l_primary_obj varchar2(100);
897
898 --
899 BEGIN
900 --
901 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
902 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
903 MODULE => g_module || '.' || l_procedure,
904 MESSAGE => 'Entering ' || l_procedure);
905 END IF;
906
907 -- If a Plan URL has been set up for the Plan then Return that URL
908 -- Else If Authoria integration has been enabled, the Required Profile
909 -- values have been set and a Page exists in Authoria for the Plan,
910 -- Then Return Authoria URL
911 -- Else Return Null (Plan will be displayed without any link)
912
913 -- Profile values required for displaying Authoria link
914 -- HR_AUTHORIA_ENABLED = 'Y'
915 -- HR_KPI_AGENT or APPS_SERVLET_AGENT
916 -- HR_KPI_SERVLET
917
918
919 IF p_plan_url IS NULL THEN
920
921 l_hr_authoria_enabled := fnd_profile.value('HR_AUTHORIA_ENABLED');
922
923 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
924 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
925 MODULE => g_module || '.' || l_procedure,
926 MESSAGE => 'profile HR_AUTHORIA_ENABLED is ' ||
927 l_hr_authoria_enabled);
928 END IF;
929
930 IF UPPER(l_hr_authoria_enabled) = 'Y' then
931 l_hr_kpi_agent := fnd_profile.value('HR_KPI_AGENT');
932
933 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
934 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
935 MODULE => g_module || '.' || l_procedure,
936 MESSAGE => 'profile HR_KPI_AGENT is ' ||l_hr_kpi_agent);
937 END IF;
938
939 IF l_hr_kpi_agent is null then
940 l_apps_servlet_agent := fnd_profile.value('APPS_SERVLET_AGENT');
941
942 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
943 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
944 MODULE => g_module || '.' || l_procedure,
945 MESSAGE => 'profile APPS_SERVLET_AGENT is ' ||
946 l_apps_servlet_agent);
947 END IF;
948 END IF;
949
950 l_hr_kpi_servlet := fnd_profile.value('HR_KPI_SERVLET');
951 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
952 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
953 MODULE => g_module || '.' || l_procedure,
954 MESSAGE => 'profile HR_KPI_SERVLET is ' ||
955 l_hr_kpi_servlet);
956 END IF;
957
958 l_dbc_filename := fnd_web_config.database_id;
959
960 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
961 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
962 MODULE => g_module || '.' || l_procedure,
963 MESSAGE => 'dbc file name is ' || l_dbc_filename);
964 END IF;
965
966 l_user_id := ICX_SEC.G_USER_ID;
967 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
968 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
969 MODULE => g_module || '.' || l_procedure,
970 MESSAGE => 'user id is ' || l_user_id);
971 END IF;
972
973 l_encrypted_user_id := hr_sso_utl.encrypt_ps_username(l_user_id);
974 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
975 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
976 MODULE => g_module || '.' || l_procedure,
977 MESSAGE => 'encryted user id is ' ||
978 l_encrypted_user_id);
979 END IF;
980
981 l_encrypted_person_id := hr_sso_utl.encrypt_ps_username(p_person_id);
982 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
983 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
984 MODULE => g_module || '.' || l_procedure,
985 MESSAGE => 'encryted person id is ' ||
986 l_encrypted_person_id);
987 END IF;
988
989 l_page_name := hr_auth_int.get_page(p_plip_id,p_pl_id,p_ler_id);
990 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
991 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
992 MODULE => g_module || '.' || l_procedure,
993 MESSAGE => 'authoria page name is ' || l_page_name);
994 END IF;
995
996 l_primary_obj := to_char(p_pl_id)||'A'||to_char(p_plip_id)||','||
997 p_primary_obj_context;
998 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
999 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
1000 MODULE => g_module || '.' || l_procedure,
1001 MESSAGE => 'primary object is ' || l_primary_obj);
1002 END IF;
1003
1004
1005 IF l_page_name IS NOT NULL
1006 and NVL(l_hr_kpi_agent, l_apps_servlet_agent) IS NOT NULL
1007 and l_hr_kpi_servlet IS NOT NULL
1008 and l_encrypted_person_id IS NOT NULL
1009 and l_dbc_filename IS NOT NULL
1010 THEN
1011
1012 l_url := nvl(l_hr_kpi_agent,l_apps_servlet_agent)|| l_hr_kpi_servlet ||
1013 l_request || l_dbc_filename || l_separator || l_provider ||
1014 l_separator || l_encrypted_user_id || l_separator ||
1015 l_encrypted_person_id ||l_separator ||
1016 l_page_name || l_separator || l_primary_obj;
1017
1018 l_ret_val := l_url;
1019 END IF;
1020 END IF;
1021 END IF;
1022
1023 IF l_ret_val IS NULL THEN
1024 l_ret_val := p_plan_url;
1025 END IF;
1026
1027 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1028 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
1029 MODULE => g_module || '.' || l_procedure,
1030 MESSAGE => 'return url is ' || l_ret_val);
1031 END IF;
1032
1033 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1034 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
1035 MODULE => g_module || '.' || l_procedure,
1036 MESSAGE => 'Exiting ' || l_procedure);
1037 END IF;
1038
1039 return(l_ret_val);
1040 --
1041 EXCEPTION
1042 --
1043 WHEN others THEN
1044 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1045 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
1046 MODULE => g_module || '.' || l_procedure,
1047 MESSAGE => 'Exiting with Exception' || l_procedure);
1048 END IF;
1049 return p_plan_url;
1050 --
1051 END get_url_ss;
1052 --
1053
1054 END hr_auth_int;