[Home] [Help]
PACKAGE BODY: APPS.HR_EXTERNAL_APPLICATION
Source
1 PACKAGE BODY hr_external_application AS
2 /* $Header: hrextapp.pkb 120.0 2005/05/31 00:11:58 appldev noship $ */
3
4 -- Applications username holds the site level username / password
5 g_site_user_name CONSTANT VARCHAR2(100) := 'SYSADMIN';
6
7 -- Logging code data
8 g_module CONSTANT VARCHAR2(80) :=
9 'per.pl_sql.hr_external_application';
10
11 -- named exception for external apps
12 e_extappexception EXCEPTION;
13
14 -- SSO server related constants
15
16 l_sso_extapp_launcher CONSTANT VARCHAR2(80) :=
17 'wwsso_app_admin.fapp_process_login';
18
19
20 -- displayError
21 --
22 -- Displays error text to the user
23 --
24 PROCEDURE displayError(p_msg IN VARCHAR2) IS
25
26 l_procedure VARCHAR2(31) := 'display_error';
27
28 BEGIN
29
30 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
31 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
32 MODULE => g_module || '.' || l_procedure,
33 MESSAGE => 'Entering ' || l_procedure);
34 END IF;
35
36 IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
37 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
38 MODULE => g_module || '.' || l_procedure,
39 MESSAGE => p_msg);
40 END IF;
41
42 -- show error
43 --htp.p(p_msg);
44
45 htp.htmlOpen;
46 htp.bodyOpen;
47 htp.p(p_msg);
48 htp.bodyClose;
49 htp.htmlClose;
50
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
58 END displayError;
59
60 --
61 -- Returns the URL of the routine responsible for logging into an
62 -- external application
63 --
64 FUNCTION get_extapp_url(p_app_id IN VARCHAR2) RETURN VARCHAR2 IS
65
66 l_retval VARCHAR2(255);
67 l_procedure VARCHAR2(31) := 'get_extapp_url';
68
69 BEGIN
70
71 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
72 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
73 MODULE => g_module || '.' || l_procedure,
74 MESSAGE => 'Entering ' || l_procedure);
75 END IF;
76
77 l_retval := hr_sso_utl.get_sso_query_path(l_sso_extapp_launcher)
78 || '?p_app_id='||p_app_id;
79
80 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
81 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
82 MODULE => g_module || '.' || l_procedure,
83 MESSAGE => 'extapp_url is ' || l_retval);
84 END IF;
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 => 'Exiting ' || l_procedure);
90 END IF;
91
92 RETURN (l_retval);
93
94 END get_extapp_url;
95
96 --
97 -- Used to determine whether the site level credentials should always
98 -- be used. For the time being this would entail the user level credentials
99 -- being overriden so it always returns false.
100 --
101 FUNCTION site_override RETURN BOOLEAN IS
102 BEGIN
103
104 RETURN false;
105
106 END site_override;
107
108 --
109 -- Name
110 -- call_extapp
111 --
112 -- Parameters
113 -- p_app_id app_id of External Application
114 -- p_new_window If TRUE opens a new window otherwise
115 -- replaces the current one
116 --
117 -- Purpose
118 -- Calls the External Application possibly in a new window.
119 --
120 --
121 -- Notes
122 --
123 -- The new window mode could be improved but not by much
124 --
125 PROCEDURE call_extapp(p_app_id IN VARCHAR2,
126 p_new_window IN BOOLEAN DEFAULT false ) IS
127
128 l_url VARCHAR2(255);
129 l_procedure VARCHAR2(31) := 'call_extapp';
130
131 BEGIN
132
133 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
134 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
135 MODULE => g_module || '.' || l_procedure,
136 MESSAGE => 'Entering ' || l_procedure);
137 END IF;
138
139 --
140 -- A limitation of the external application invocation routine is that
141 -- it needs to be called via the SSO DAD. This is fixable but probably
142 -- only at the expense of creating a lot of SSO synonyms in APPS.
143 --
144 l_url := get_extapp_url(p_app_id);
145
146 IF ( p_new_window ) THEN
147
148 -- Open a new window. Go back to the previous window
149 htp.script('window.open("' || l_url || '","newwindow");' ||
150 'history.go(-1);',
151 'javascript');
152
153 ELSE
154 --
155 -- Possible performance concerns using this function with IE
156 -- may be worth switching to using 'location.replace()'
157 --
158 owa_util.redirect_url(l_url);
159
160 END IF;
161
162 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
163 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
164 MODULE => g_module || '.' || l_procedure,
165 MESSAGE => 'Exiting ' || l_procedure);
166 END IF;
167
168 END call_extapp;
169
170
171 -- Name
172 -- check_app_id
173 -- Purpose
174 -- Checks whether the given app_id is valid.
175 --
176 FUNCTION check_app_id (p_app_id IN VARCHAR2) RETURN BOOLEAN IS
177
178 l_retval BOOLEAN := false;
179 l_app_name VARCHAR2(80);
180 l_apptype VARCHAR2(80);
181 l_appurl VARCHAR2(1000);
182 l_logout_url VARCHAR2(1000);
183 l_userfield VARCHAR2(80);
184 l_pwdfield VARCHAR2(80);
185 l_authneeded VARCHAR2(80);
186 l_fname1 VARCHAR2(80);
187 l_fval1 VARCHAR2(80);
188 l_fname2 VARCHAR2(80);
189 l_fval2 VARCHAR2(80);
190 l_fname3 VARCHAR2(80);
191 l_fval3 VARCHAR2(80);
192 l_fname4 VARCHAR2(80);
193 l_fval4 VARCHAR2(80);
194 l_fname5 VARCHAR2(80);
195 l_fval5 VARCHAR2(80);
196 l_fname6 VARCHAR2(80);
197 l_fval6 VARCHAR2(80);
198 l_fname7 VARCHAR2(80);
199 l_fval7 VARCHAR2(80);
200 l_fname8 VARCHAR2(80);
201 l_fval8 VARCHAR2(80);
202 l_fname9 VARCHAR2(80);
203 l_fval9 VARCHAR2(80);
204 l_procedure VARCHAR2(31) := 'check_app_id';
205
206 BEGIN
207
208 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
209 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
210 MODULE => g_module || '.' || l_procedure,
211 MESSAGE => 'Entering ' || l_procedure);
212 END IF;
213
214 BEGIN
215
216 hr_sso_utl.pstore_get_app_info(
217 p_app_id,
218 l_app_name, l_apptype,
219 l_appurl, l_logout_url,
220 l_userfield, l_pwdfield, l_authneeded,
221 l_fname1, l_fval1 ,
222 l_fname2, l_fval2 ,
223 l_fname3, l_fval3 ,
224 l_fname4, l_fval4,
225 l_fname5, l_fval5,
226 l_fname6, l_fval6,
227 l_fname7, l_fval7,
228 l_fname8, l_fval8 ,
229 l_fname9, l_fval9 );
230
231 --
232 -- If no exception raised then we assume app_id is valid
233 --
234 l_retval := true;
235
236 EXCEPTION
237 --
238 WHEN OTHERS THEN
239 l_retval := false;
240 END;
241
242 IF l_retval THEN
243 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
244 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
245 MODULE => g_module || '.' || l_procedure,
246 MESSAGE => 'valid app_id');
247 END IF;
248 ELSE
249 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
250 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
251 MODULE => g_module || '.' || l_procedure,
252 MESSAGE => 'invalid app_id');
253 END IF;
254 END IF;
255
256
257 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
258 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
259 MODULE => g_module || '.' || l_procedure,
260 MESSAGE => 'Exiting ' || l_procedure);
261 END IF;
262
263 RETURN(l_retval);
264
265 END check_app_id;
266
267 --=========================== get_app_id ================================
268 --
269 -- Description: Find the app_id of the external application
270 --
271 --
272 -- Input Parameters
273 -- p_app_code - Short code identifier for External Application
274 -- (from SSO_EXTERNAL_APPLICATIONS)
275 --
276 --
277 -- Output Parameters
278 -- l_app_id - app_id of target
279 --
280 --
281 -- ==========================================================================
282
283 --
284 FUNCTION get_app_id(p_app_code IN VARCHAR2) RETURN VARCHAR2 IS
285 --
286 l_app_id VARCHAR2(80) := NULL;
287 l_procedure VARCHAR2(31) := 'get_app_id';
288
289 cursor csr_get_app_id is
290 SELECT external_application_id
291 FROM hr_ki_ext_applications
292 WHERE external_application_name = p_app_code;
293
294 BEGIN
295
296 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
297 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
298 MODULE => g_module || '.' || l_procedure,
299 MESSAGE => 'Entering ' || l_procedure);
300 END IF;
301
302 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
303 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
304 MODULE => g_module || '.' || l_procedure,
305 MESSAGE => 'p_app_code is ' || p_app_code);
306 END IF;
307
308 OPEN csr_get_app_id;
309 FETCH csr_get_app_id INTO l_app_id;
310 IF csr_get_app_id%NOTFOUND THEN
311 CLOSE csr_get_app_id;
312 displayError('INTERNAL ERROR: No data for code ' || p_app_code);
313 RAISE e_extappexception;
314 END IF;
315 CLOSE csr_get_app_id;
316
317 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
318 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
319 MODULE => g_module || '.' || l_procedure,
320 MESSAGE => 'l_app_id is ' || NVL(l_app_id,'-null-'));
321 END IF;
322
323 IF NOT check_app_id(l_app_id) THEN
324 displayError('INTERNAL ERROR (' || p_app_code ||
325 ') No external application with id ' || l_app_id);
326 RAISE e_extappexception;
327 END IF;
328
329 IF l_app_id IS NOT NULL THEN
330 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
331 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
332 MODULE => g_module || '.' || l_procedure,
333 MESSAGE => 'app_id is ' || l_app_id);
334 END IF;
335 END IF;
336
337 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
338 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
339 MODULE => g_module || '.' || l_procedure,
340 MESSAGE => 'Exiting ' || l_procedure);
341 END IF;
342
343
344 RETURN(l_app_id);
345
346 END get_app_id;
347
348 -- =========================== get_app_auth ================================
349 --
350 -- Description: Find the SSO username / pwd for a user
351 --
352 -- Input Parameters
353 -- p_app_id - app_id of target
354 -- p_user_name - Oracle Apps Username
355 --
356 -- Output Parameters
357 -- p_app_user - ext app username
358 -- p_app_pwd - ext app password
359 --
360 -- ==========================================================================
361
362 --
363 PROCEDURE get_app_auth( p_app_id IN VARCHAR2,
364 p_user_name IN VARCHAR2,
365 p_app_user OUT NOCOPY VARCHAR2,
366 p_app_pwd OUT NOCOPY VARCHAR2,
367 p_user_prefs OUT NOCOPY VARCHAR2) IS
368 --
369 l_fname1 VARCHAR2(80);
370 l_fval1 VARCHAR2(80);
371 l_fname2 VARCHAR2(80);
372 l_fval2 VARCHAR2(80);
373 l_fname3 VARCHAR2(80);
374 l_fval3 VARCHAR2(80);
375 l_fname4 VARCHAR2(80);
376 l_fval4 VARCHAR2(80);
377 l_fname5 VARCHAR2(80);
378 l_fval5 VARCHAR2(80);
379 l_fname6 VARCHAR2(80);
380 l_fval6 VARCHAR2(80);
381 l_fname7 VARCHAR2(80);
382 l_fval7 VARCHAR2(80);
383 l_fname8 VARCHAR2(80);
384 l_fval8 VARCHAR2(80);
385 l_fname9 VARCHAR2(80);
386 l_fval9 VARCHAR2(80);
387 l_user_prefs VARCHAR2(80);
388 l_procedure VARCHAR2(31) := 'get_app_auth';
389
390 --
391 BEGIN
392
393 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
394 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
395 MODULE => g_module || '.' || l_procedure,
396 MESSAGE => 'Entering ' || l_procedure);
397 END IF;
398
399 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
400 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
401 MODULE => g_module || '.' || l_procedure,
402 MESSAGE => 'User Info for ' || p_user_name);
403 END IF;
404
405 BEGIN
406
407 hr_sso_utl.PSTORE_GET_USERINFO(
408 p_app_id,
409 p_user_name,
410 p_app_user,
411 p_app_pwd,
412 l_fname1, l_fval1,
413 l_fname2, l_fval2,
414 l_fname3, l_fval3,
415 l_fname4, l_fval4,
416 l_fname5, l_fval5,
417 l_fname6, l_fval6,
418 l_fname7, l_fval7,
419 l_fname8, l_fval8,
420 l_fname9, l_fval9,
421 l_user_prefs);
422
423 -- ??? Need an explicit name for this exception
424 EXCEPTION
425 WHEN OTHERS THEN
426 p_app_user := NULL;
427 p_app_pwd := NULL;
428
429 END;
430
431 IF p_app_pwd IS NULL THEN
432 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
433 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
434 MODULE => g_module || '.' || l_procedure,
435 MESSAGE => 'No details for ' || p_user_name);
436 END IF;
437 ELSE
438 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
439 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
440 MODULE => g_module || '.' || l_procedure,
441 MESSAGE => 'apps_user_name is ' || p_app_user);
442 END IF;
443 END IF;
444
445 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
446 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
447 MODULE => g_module || '.' || l_procedure,
448 MESSAGE => 'Exiting ' || l_procedure);
449 END IF;
450
451
452 END get_app_auth;
453
454 -- =========================== launch ================================
455 --
456 -- Description: The passed target will be:
457 -- 1. identify app_id
458 -- 2. SSO username / pwd for user found
459 -- otherwise sysadmin ones used
460 -- 3. launch ext app
461 --
462 --
463 -- Input Parameters
464 -- p_app_code - Short code identifying External Application
465 --
466 --
467 -- Output Parameters
468 -- <none>
469 --
470 --
471 -- ==========================================================================
472
473 --
474 PROCEDURE launch(p_app_code IN VARCHAR2) IS
475 --
476
477 l_app_id varchar2(80);
478 l_app_user VARCHAR2(80) := NULL;
479 l_app_pwd VARCHAR2(80);
480 l_user_name VARCHAR2(100);
481 l_user_prefs VARCHAR2(100);
482 l_procedure VARCHAR2(31) := 'launch';
483
484 --
485 BEGIN
486
487 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
488 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
489 MODULE => g_module || '.' || l_procedure,
490 MESSAGE => 'Entering ' || l_procedure);
491 END IF;
492 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
493 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
494 MODULE => g_module || '.' || l_procedure,
495 MESSAGE => 'Target is ' || p_app_code);
496 END IF;
497
498 -- find and validate application id for app code
499 l_app_id := get_app_id(p_app_code);
500
501 -- find current Oracle Apps username.
502 l_user_name := hr_sso_utl.get_user();
503
504 -- get the current ext app user/pwd
505 get_app_auth(
506 p_app_id => l_app_id,
507 p_user_name => l_user_name,
508 p_app_user => l_app_user,
509 p_app_pwd => l_app_pwd,
510 p_user_prefs => l_user_prefs);
511
512
513 -- If no credentials registered for user then copy the
514 -- 'site level' credentials from a fixed username
515
516 IF ( ( l_app_user IS NULL ) OR ( site_override ) ) THEN
517
518 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
519 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
520 MODULE => g_module || '.' || l_procedure,
521 MESSAGE => 'Using site level details');
522 END IF;
523
524 get_app_auth(
525 p_app_id => l_app_id,
526 p_user_name => g_site_user_name,
527 p_app_user => l_app_user,
528 p_app_pwd => l_app_pwd,
529 p_user_prefs => l_user_prefs);
530
531 -- We update password store for this user because of a limitation
532 -- of fapp_login where the login dialog appears even if credentials
533 -- are passed as parameters
534
535 IF l_app_user IS NOT NULL THEN
536
537 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
538 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
539 MODULE => g_module || '.' || l_procedure,
540 MESSAGE => 'Storing details for ' || l_user_name);
541 END IF;
542
543 hr_sso_utl.pstore_add_userinfo(
544 p_app_id => l_app_id,
545 p_ssouser => l_user_name,
546 p_app_user => l_app_user,
547 p_app_pwd => l_app_pwd ,
548 p_user_prefs => l_user_prefs);
549 ELSE
550
551 -- If we reach this point then no password was held for either
552 -- site level (SYSADMIN) or the current user. For now we'll allow
553 -- the login to go ahead.
554
555 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
556 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
557 MODULE => g_module || '.' || l_procedure,
558 MESSAGE => 'No details found at site level');
559 END IF;
560 END IF;
561
562 END IF;
563
564 -- For now we'll 'borrow' the 'Open New Window' profile option to
565 -- decide whether to open a new browser window. Not clear how
566 -- useful this is. It looks like the native browser 'open in new window'
567 -- will work just as well.
568 --
569 -- The launch of external applications using Basic Authentication could
570 -- be made more seamless by opening a new window.
571 --
572 call_extapp(l_app_id,
573 fnd_profile.value('HR_KPI_OPEN_NEW_WINDOW') = 'Y' );
574
575 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
576 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
577 MODULE => g_module || '.' || l_procedure,
578 MESSAGE => 'Exiting ' || l_procedure);
579 END IF;
580
581 EXCEPTION
582
583 WHEN e_extappexception THEN
584 NULL;
585
586 WHEN OTHERS THEN
587
588 displayError('LAUNCH_EXTAPP: ' || sqlerrm);
589
590 END launch;
591 --
592 -- Purpose
593 --
594 -- A PL/SQL function for error handling.
595 --
596 --
597 procedure generic_error(routine in varchar2,
598 errcode in number,
599 errmsg in varchar2) is
600 l_msg varchar2(2000);
601 begin
602 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
603 fnd_message.set_token('ROUTINE', routine);
604 fnd_message.set_token('ERRNO', errcode);
605 fnd_message.set_token('REASON', errmsg);
606 fnd_message.raise_error;
607 end;
608
609 -- Purpose
610 --
611 -- A PL/SQL function that opens URL returned from servlet
612 -- in the browser.
613 --
614 --
615
616 PROCEDURE KI_LAUNCH(p_topic IN VARCHAR2
617 ,p_provider in VARCHAR2) IS
618 --
619
620 l_procedure VARCHAR2(31) := 'KI_LAUNCH';
621 l_topic varchar2(30):=NULL;
622 l_provider VARCHAR2(30) := NULL;
623 l_servlet_agent varchar2(200);
624 l_dbc varchar2(20);
625 l_hr_ext_servlet varchar2(200);
626 l_apps_servlet_agent varchar2(200);
627 l_url varchar2(400);
628 l_servlet varchar2(100);
629 l_icx_ki_ids varchar2(100):='&KICustomSessionId=Direct Link REQUEST:';
630 l_session_id number;
631 icx_id number;
632
633 Cursor C_Sel1 is
634 select topic_id from hr_ki_topics where
635 topic_key=p_topic;
636
637 Cursor C_Sel2 is
638 select integration_id from hr_ki_integrations where
639 integration_key =p_provider ;
640
641 --
642 BEGIN
643
644 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
645 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
646 MODULE => g_module || '.' || l_procedure,
647 MESSAGE => 'Entering ' || l_procedure);
648 END IF;
649
650 Open C_Sel1;
651 Fetch C_Sel1 Into l_topic;
652 If C_Sel1%notfound Then
653 Close C_Sel1;
654 --
655 -- The topic key is invalid therefore we must error
656 --
657 fnd_message.set_name('PER', 'PER_449104_EXT_APP_TPCI_INVL');
658 fnd_message.set_token('TOPIC', p_topic);
659 displayError(fnd_message.get());
660 return;
661 End If;
662 Close C_Sel1;
663
664 Open C_Sel2;
665 Fetch C_Sel2 Into l_provider;
666 If C_Sel2%notfound Then
667 Close C_Sel2;
668 --
669 -- The provider_key is invalid therefore we must error
670 --
671 fnd_message.set_token('PROVIDER', p_provider);
672 fnd_message.set_name('PER', 'PER_449105_EXT_APP_PRV_INVL');
673 displayError(fnd_message.get());
674 return;
675 End If;
676 Close C_Sel2;
677
678 --Get dbc file
679 select fnd_web_config.database_id into l_dbc from dual;
680
681 --First get the value from HR_EXT_AGENT
682 select fnd_profile.value('HR_KPI_AGENT') into l_hr_ext_servlet
683 from dual;
684 select fnd_profile.value('HR_KPI_GENFWK_SERVLET') into l_servlet
685 from dual;
686
687 If l_hr_ext_servlet is null then
688 select fnd_profile.value('APPS_SERVLET_AGENT') into
689 l_apps_servlet_agent from dual;
690 l_hr_ext_servlet :=l_apps_servlet_agent;
691 end if;
692
693 --Add custom seesion id and icx session_id
694 l_icx_ki_ids := l_icx_ki_ids || to_char(sysdate,'hh:mi:ss')||': ';
695 select SESSION_ID into l_session_id from icx_sessions where
696 icx_sessions.login_id = to_number(fnd_profile.value('LOGIN_ID'));
697
698 l_icx_ki_ids := l_icx_ki_ids ||'&IcxSessionId='||icx_call.encrypt3(l_session_id);
699
700 --Construct the URL
701 l_url := l_hr_ext_servlet||l_servlet||'?type=INIT'|| '&' || 'uit=FRMFN'||
702 '&' || 'topic='||l_topic||'&'||'provider='||l_provider||
703 '&'|| 'dbc='||l_dbc||l_icx_ki_ids;
704
705 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
706 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
707 MODULE => g_module || '.' || l_procedure,
708 MESSAGE => 'l_url' || l_url);
709 END IF;
710
711
712 IF ( nvl(fnd_profile.value('HR_KPI_OPEN_NEW_WINDOW'), 'Y') = 'Y' ) THEN
713 -- Open a new window. Go back to the previous window
714 htp.script('window.open("' || l_url || '","newwindow");' ||
715 'history.go(-1);',
716 'javascript');
717 ELSE
718 -- Possible performance concerns using this function with IE
719 -- may be worth switching to using 'location.replace()'
720 --
721 owa_util.redirect_url(l_url);
722 END IF;
723
724 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
725 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
726 MODULE => g_module || '.' || l_procedure,
727 MESSAGE => 'Exiting ' || l_procedure);
728 END IF;
729
730 EXCEPTION
731
732 WHEN OTHERS THEN
733
734 IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
735 FND_LOG.STRING (LOG_LEVEL => FND_LOG.LEVEL_ERROR,
736 MODULE => g_module || '.' || l_procedure,
737 MESSAGE => 'Error whilst launcing URL' ||
738 ' - ' || sqlerrm);
739 END IF;
740
741 generic_error( g_module || '.' || l_procedure ,
742 sqlerrm,'Error Occured while lauching URL');
743
744
745 end;
746
747 --
748 -- Name
749 -- register
750 --
751 -- Parameters
752 -- p_app_code IN external app code
753 -- p_apptype IN application type
754 -- p_appurl IN URL for external application
755 -- p_logout_url IN URL for external application to logout
756 -- p_userfld IN name of user field
757 -- p_pwdfld IN name of password field
758 -- p_authused IN type of authentication used
759 -- p_fnameN IN additional names (N=1..9)
760 -- p_fvalN IN additional values (N=1..9)
761 --
762 -- Purpose
763 --
764 -- A PL/SQL function that registers an external application.
765 --
766 --
767 PROCEDURE register (
768 p_app_code IN VARCHAR2,
769 p_apptype IN VARCHAR2,
770 p_appurl IN VARCHAR2,
771 p_logout_url IN VARCHAR2,
772 p_userfld IN VARCHAR2,
773 p_pwdfld IN VARCHAR2,
774 p_authused IN VARCHAR2,
775 p_fname1 IN VARCHAR2 DEFAULT NULL,
776 p_fval1 IN VARCHAR2 DEFAULT NULL,
777 p_fname2 IN VARCHAR2 DEFAULT NULL,
778 p_fval2 IN VARCHAR2 DEFAULT NULL,
779 p_fname3 IN VARCHAR2 DEFAULT NULL,
780 p_fval3 IN VARCHAR2 DEFAULT NULL,
781 p_fname4 IN VARCHAR2 DEFAULT NULL,
782 p_fval4 IN VARCHAR2 DEFAULT NULL,
783 p_fname5 IN VARCHAR2 DEFAULT NULL,
784 p_fval5 IN VARCHAR2 DEFAULT NULL,
785 p_fname6 IN VARCHAR2 DEFAULT NULL,
786 p_fval6 IN VARCHAR2 DEFAULT NULL,
787 p_fname7 IN VARCHAR2 DEFAULT NULL,
788 p_fval7 IN VARCHAR2 DEFAULT NULL,
789 p_fname8 IN VARCHAR2 DEFAULT NULL,
790 p_fval8 IN VARCHAR2 DEFAULT NULL,
791 p_fname9 IN VARCHAR2 DEFAULT NULL,
792 p_fval9 IN VARCHAR2 DEFAULT NULL) IS
793
794 l_app_id NUMBER(15) := NULL;
795
796 l_tst_app_name VARCHAR2(80);
797 l_tst_apptype VARCHAR2(80);
798 l_tst_appurl VARCHAR2(1000);
799 l_tst_logout_url VARCHAR2(1000);
800 l_tst_userfield VARCHAR2(80);
801 l_tst_pwdfield VARCHAR2(80);
802 l_tst_authneeded VARCHAR2(80);
803 l_tst_fname1 VARCHAR2(80);
804 l_tst_fval1 VARCHAR2(1000);
805 l_tst_fname2 VARCHAR2(80);
806 l_tst_fval2 VARCHAR2(1000);
807 l_tst_fname3 VARCHAR2(80);
808 l_tst_fval3 VARCHAR2(1000);
809 l_tst_fname4 VARCHAR2(80);
810 l_tst_fval4 VARCHAR2(1000);
811 l_tst_fname5 VARCHAR2(80);
812 l_tst_fval5 VARCHAR2(1000);
813 l_tst_fname6 VARCHAR2(80);
814 l_tst_fval6 VARCHAR2(1000);
815 l_tst_fname7 VARCHAR2(80);
816 l_tst_fval7 VARCHAR2(1000);
817 l_tst_fname8 VARCHAR2(80);
818 l_tst_fval8 VARCHAR2(1000);
819 l_tst_fname9 VARCHAR2(80);
820 l_tst_fval9 VARCHAR2(1000);
821
822 CURSOR csr_app_id IS
823 SELECT external_application_id
824 FROM hr_ki_ext_applications
825 WHERE external_application_name = p_app_code;
826
827 BEGIN
828
829 -- see if the hr_ki_ext_applications
830 -- if no app_id, then this is a new external application
831 -- otherwise we are doing an update
832
833 hr_utility.trace('csr_app_id');
834
835 OPEN csr_app_id;
836 FETCH csr_app_id INTO l_app_id;
837 IF csr_app_id%NOTFOUND THEN
838 l_app_id := NULL;
839 END IF;
840 CLOSE csr_app_id;
841
842 hr_utility.trace('l_app_id = ' || l_app_id);
843
844 -- check if l_app_id matches an existing application
845 -- note that we can only check if the l_app_id matches an
846 -- existing application id, not on the other details
847 --
848 -- if no match, reset l_app_id to null and add the app
849
850 IF l_app_id IS NOT NULL THEN
851
852 BEGIN
853 hr_sso_utl.PSTORE_GET_APP_INFO (
854 P_APPID => l_app_id,
855 P_APP_NAME => l_tst_app_name,
856 P_APPTYPE => l_tst_apptype,
857 P_APPURL => l_tst_appurl,
858 P_LOGOUT_URL => l_tst_logout_url,
859 P_USERFIELD => l_tst_userfield,
860 P_PWDFIELD => l_tst_pwdfield,
861 P_AUTHNEEDED => l_tst_authneeded,
862 P_FNAME1 => l_tst_fname1,
863 P_FVAL1 => l_tst_fval1,
864 P_FNAME2 => l_tst_fname2,
865 P_FVAL2 => l_tst_fval2,
866 P_FNAME3 => l_tst_fname3,
867 P_FVAL3 => l_tst_fval3,
868 P_FNAME4 => l_tst_fname4,
869 P_FVAL4 => l_tst_fval4,
870 P_FNAME5 => l_tst_fname5,
871 P_FVAL5 => l_tst_fval5,
872 P_FNAME6 => l_tst_fname6,
873 P_FVAL6 => l_tst_fval6,
874 P_FNAME7 => l_tst_fname7,
875 P_FVAL7 => l_tst_fval7,
876 P_FNAME8 => l_tst_fname8,
877 P_FVAL8 => l_tst_fval8,
878 P_FNAME9 => l_tst_fname9,
879 P_FVAL9 => l_tst_fval9);
880
881 -- if exception raised, then invalid l_app_id / app_id
882 -- assuming any exception indicates the app is invalid
883 EXCEPTION
884 WHEN others THEN
885 l_app_id := NULL;
886 END;
887 END IF;
888
889
890 IF l_app_id IS NULL THEN
891 -- add the application
892 hr_sso_utl.PSTORE_ADD_APPLICATION (
893 p_appname => p_app_code,
894 p_apptype => p_apptype,
895 p_appurl => p_appurl,
896 p_logout_url => p_logout_url,
897 p_userfld => p_userfld,
898 p_pwdfld => p_pwdfld,
899 p_authused => p_authused,
900 p_fname1 => p_fname1,
901 p_fval1 => p_fval1,
902 p_fname2 => p_fname2,
903 p_fval2 => p_fval2,
904 p_fname3 => p_fname3,
905 p_fval3 => p_fval3,
906 p_fname4 => p_fname4,
907 p_fval4 => p_fval4,
908 p_fname5 => p_fname5,
909 p_fval5 => p_fval5,
910 p_fname6 => p_fname6,
911 p_fval6 => p_fval6,
912 p_fname7 => p_fname7,
913 p_fval7 => p_fval7,
914 p_fname8 => p_fname8,
915 p_fval8 => p_fval8,
916 p_fname9 => p_fname9,
917 p_fval9 => p_fval9,
918 p_appid => l_app_id);
919
920 -- insert record into hr_ki_ext_applications
921 -- after deleting any existing entry
922 DELETE FROM hr_ki_ext_applications
923 WHERE external_application_name = p_app_code;
924
925 INSERT INTO hr_ki_ext_applications
926 (
927 ext_application_id,
928 external_application_name,
929 external_application_id
930 )
931 SELECT
932 hr_ki_ext_applications_s.nextval,
933 p_app_code,
934 l_app_id
935 FROM dual;
936
937
938 ELSE
939 -- otherwise update existing
940 hr_sso_utl.PSTORE_MODIFY_APP_INFO (
941 p_appid => l_app_id,
942 p_app_name => p_app_code,
943 p_apptype => p_apptype,
944 p_appurl => p_appurl,
945 p_logout_url => p_logout_url,
946 p_userfield => p_userfld,
947 p_pwdfield => p_pwdfld,
948 p_authneeded => p_authused,
949 p_fname1 => p_fname1,
950 p_fval1 => p_fval1,
951 p_fname2 => p_fname2,
952 p_fval2 => p_fval2,
953 p_fname3 => p_fname3,
954 p_fval3 => p_fval3,
955 p_fname4 => p_fname4,
956 p_fval4 => p_fval4,
957 p_fname5 => p_fname5,
958 p_fval5 => p_fval5,
959 p_fname6 => p_fname6,
960 p_fval6 => p_fval6,
961 p_fname7 => p_fname7,
962 p_fval7 => p_fval7,
963 p_fname8 => p_fname8,
964 p_fval8 => p_fval8,
965 p_fname9 => p_fname9,
966 p_fval9 => p_fval9);
967
968 -- update external_application_id in hr_ki_ext_applications
969 -- with app_id
970 UPDATE hr_ki_ext_applications
971 SET external_application_id = l_app_id
972 WHERE external_application_name = p_app_code;
973
974
975 END IF;
976
977
978 END register;
979
980
981
982 END hr_external_application;