[Home] [Help]
PACKAGE BODY: APPS.FND_LDAP_WRAPPER
Source
1 package body fnd_ldap_wrapper as
2 /* $Header: AFSCOLWB.pls 120.43.12010000.2 2008/11/20 10:16:15 bmasa ship $ */
3 --
4 -- Start of Package Globals
5
6 G_MODULE_SOURCE constant varchar2(80) := 'fnd.plsql.oid.fnd_ldap_wrapper.';
7
8 -- End of Package Globals
9 --
10 -------------------------------------------------------------------------------
11 initreg boolean := false;
12 init boolean := false;
13 ssoenabled boolean := false;
14 registered boolean := false;
15 registration pls_integer := G_NO_REGISTRATION;
16 function trim_attribute(p_attr in varchar2) return varchar2;
17
18 -------------------------------------------------------------------------------
19 function get_ldap_user_name(p_user_name in fnd_user.user_name%type) return varchar2 is
20
21 l_module_source varchar2(256);
22 l_result varchar2(4000);
23 l_user_guid fnd_user.user_guid%type;
24 l_found boolean;
25
26 l_apps_sso varchar2(50);
27 l_profile_defined boolean;
28 l_orclappname varchar2(256);
29 l_obj_name varchar2(256);
30 plsql_block varchar2(500);
31 sso_registration_failure exception;
32 l_sso_version varchar2(10);
33 l_allow_sync varchar2(1);
34
35 cursor cur_fnd_users is
36 select user_guid
37 from fnd_user
38 where user_name = upper(p_user_name);
39 begin
40 l_module_source := G_MODULE_SOURCE || 'get_ldap_user_name ';
41 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
42 then
43 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
44 end if;
45
46 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
47 USER_ID_Z => -1,
48 RESPONSIBILITY_ID_Z => -1,
49 APPLICATION_ID_Z => -1,
50 ORG_ID_Z => -1,
51 val_z => l_allow_sync,
52 defined_z => l_profile_defined);
53
54 if (l_profile_defined and l_allow_sync = 'Y') then
55
56 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
57 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'APPS_SSO_LDAP_SYNC enabled.');
58 end if;
59
60 fnd_profile.get_specific(name_z => 'APPS_SSO',
61 val_z => l_apps_sso,
62 defined_z => l_profile_defined);
63
64 if (l_apps_sso = 'PORTAL') OR (l_apps_sso = 'SSWA')
65 then
66 ssoenabled := false;
67 else
68 ssoenabled := true;
69 end if;
70
71 if (ssoenabled) then
72 if (not init) then
73 init := true;
74 select object_name into l_obj_name from all_objects
75 where object_name = 'DBMS_LDAP' and object_type = 'PACKAGE BODY'
76 and status = 'VALID' and owner = 'SYS';
77 l_orclappname := get_orclappname;
78 -- no exception => everything is ok
79 registered := true;
80 end if;
81
82 if (registered) then
83 l_found := false;
84 open cur_fnd_users;
85 fetch cur_fnd_users into l_user_guid;
86 l_found := cur_fnd_users%found;
87
88 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
89 then
90 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source,
91 'L_user_guid: '||l_user_guid);
92 end if;
93
94 if (l_found)
95 then
96 plsql_block :=
97 'begin :result := fnd_oid_util.get_oid_nickname(:1); end;';
98 execute immediate plsql_block using out l_result, l_user_guid;
99 else
100 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
101 then
102 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source,
103 'no such user in FND_USER: '||p_user_name);
104 end if;
105 l_result := null;
106 end if;
107 close cur_fnd_users;
108
109 else -- if (!registered)
110 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
111 then
112 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source,
113 'OID not registered');
114 end if;
115 l_result := null;
116 end if;
117 else -- if (!ssoenabled), simply return null
118 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
119 then
120 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
121 'SSO not enabled, returning true w/o changing the user name');
122 end if;
123 l_result := null;
124 end if;
125
126 else -- APPS_SSO_LDAP_SYNC not enabled.
127
128 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
129 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'APPS_SSO_LDAP_SYNC not enabled.');
130 end if;
131
132 l_result := null;
133
134 end if;
135
136 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
137 then
138 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
139 end if;
140 return l_result;
141 exception
142 when sso_registration_failure then
143 if (cur_fnd_users%isopen)
144 then
145 close cur_fnd_users;
146 end if;
147 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
148 then
149 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
150 end if;
151 l_result := null;
152 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
153 return l_result;
154 when others then
155 if (cur_fnd_users%isopen)
156 then
157 close cur_fnd_users;
158 end if;
159 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
160 then
161 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
162 end if;
163 l_result := null;
164 return l_result;
165 end get_ldap_user_name;
166 --
167 -------------------------------------------------------------------------------
168 procedure change_user_name(p_user_guid in raw,
169 p_old_user_name in varchar2,
170 p_new_user_name in varchar2,
171 x_result out nocopy pls_integer) is
172
173 l_module_source varchar2(256);
174 plsql_block varchar2(500);
175 l_fnd_user pls_integer;
176 l_oid pls_integer;
177 l_attribute varchar2(4000);
178 l_realm varchar2(4000);
179
180 begin
181 l_module_source := G_MODULE_SOURCE || 'change_user_name: ';
182
183 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
184 then
185 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
186 end if;
187
188 l_attribute := 'user_name';
189 l_realm :=get_realm_dn(p_user_guid=>p_user_guid);
190 is_operation_allowed(p_realm=> l_realm, p_direction => G_EBIZ_TO_OID,
191 p_entity => G_IDENTITY,
192 p_operation => G_MODIFY,
193 x_attribute => l_attribute,
194 x_fnd_user => l_fnd_user,
195 x_oid => l_oid);
196
197 if (l_oid = G_SUCCESS) then
198
199 plsql_block :=
200 'begin fnd_ldap_user.change_user_name(:1, :2, :3, :4); end;';
201 execute immediate plsql_block using p_user_guid, p_old_user_name, p_new_user_name, out x_result;
202
203 else -- l_oid = G_FAILURE
204
205 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
206 then
207 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_oid is false');
208 end if;
209 x_result := l_fnd_user;
210
211 end if;
212
213 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
214 then
215 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
216 end if;
217
218 exception
219 when registration_failure_exception then
220 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
221 x_result := G_FAILURE;
222 when others then
223 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
224 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
225 then
226 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
227 end if;
228 x_result := G_FAILURE;
229
230 end change_user_name;
231 --
232 -------------------------------------------------------------------------------
233 procedure synch_user_from_LDAP(p_user_name in fnd_user.user_name%type
234 , p_result out nocopy pls_integer) is
235 l_module_source varchar2(256);
236 l_apps_sso varchar2(50);
237 l_profile_defined boolean;
238 l_orclappname varchar2(256);
239 l_obj_name varchar2(256);
240 plsql_block varchar2(500);
241 l_sso_version varchar2(10);
242 l_allow_sync varchar2(1);
243
244 begin
245 l_module_source := G_MODULE_SOURCE || 'synch_user_from_LDAP';
246
247 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
248 then
249 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
250 end if;
251
252 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
253 USER_ID_Z => -1,
254 RESPONSIBILITY_ID_Z => -1,
255 APPLICATION_ID_Z => -1,
256 ORG_ID_Z => -1,
257 val_z => l_allow_sync,
258 defined_z => l_profile_defined);
259
260 if (l_profile_defined and l_allow_sync = 'Y') then
261
262 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
263 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'APPS_SSO_LDAP_SYNC enabled.');
264 end if;
265
266
267 fnd_profile.get_specific(
268 name_z => 'APPS_SSO',
269 val_z => l_apps_sso,
270 defined_z => l_profile_defined);
271
272 if (l_apps_sso = 'PORTAL') OR (l_apps_sso = 'SSWA') then
273 ssoenabled := false;
274 else
275 ssoenabled := true;
276 end if;
277
278
279 if (ssoenabled) then
280 if (not init) then
281 init := true;
282 select object_name into l_obj_name from all_objects
283 where object_name = 'DBMS_LDAP' and object_type = 'PACKAGE BODY'
284 and status = 'VALID' and owner = 'SYS';
285 l_orclappname := get_orclappname;
286 -- no exception => everything is ok
287 registered := true;
288 end if;
289
290 if (registered) then
291 plsql_block := 'begin fnd_oid_util.synch_user_from_LDAP(:1, :2); end;';
292 execute immediate plsql_block using in p_user_name, out p_result;
293 else
294 p_result := G_FAILURE;
295 end if;
296 else -- if (!ssoenabled), simply return success without updating TCA
297 p_result := G_SUCCESS;
298 end if;
299
300 else -- APPS_SSO_LDAP_SYNC not enabled.
301
302 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
303 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'APPS_SSO_LDAP_SYNC not enabled.');
304 end if;
305
306 p_result := G_SUCCESS;
307
308 end if;
309
310 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
311 then
312 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
313 end if;
314
315 exception
316 when others then
317 fnd_message.set_name ('FND', 'OID');
318 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
319 then
320 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
321 end if;
322 p_result := G_FAILURE;
323 end synch_user_from_LDAP;
324 --
325 -------------------------------------------------------------------------------
326 procedure create_user(p_user_name in varchar2,
327 p_password in varchar2,
328 p_start_date in date default sysdate,
329 p_end_date in date default null,
330 p_description in varchar2 default null,
331 p_email_address in varchar2 default null,
332 p_fax in varchar2 default null,
333 p_expire_password in pls_integer,
334 x_user_guid out nocopy raw,
335 x_password out nocopy varchar2,
336 x_result out nocopy pls_integer) is
337
338 l_module_source varchar2(256);
339 plsql_block varchar2(500);
340 l_fnd_user pls_integer;
341 l_oid pls_integer;
342 l_attribute varchar2(4000);
343 l_allowed boolean;
344 l_password varchar2(400);
345 l_start_date date;
346 l_end_date date;
347 l_description varchar2(400);
348 l_email_address varchar2(256);
349 l_fax varchar2(50);
350 l_realm varchar2(4000);
351 l_err_code varchar2(200);
352 l_tmp_str varchar2(4000);
353 begin
354
355 l_module_source := G_MODULE_SOURCE || 'create_user: ';
356
357 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
358 then
359 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
360 end if;
361
362 l_attribute := 'userpassword,orclactivestartdate,orclactiveenddate,description,mail,facsimiletelephonenumber';
363 l_realm := get_realm_dn(p_user_name=>p_user_name);
364 is_operation_allowed(p_realm=>l_realm,p_direction => G_EBIZ_TO_OID,
365 p_entity => G_IDENTITY,
366 p_operation => G_ADD,
367 x_attribute => l_attribute,
368 x_fnd_user => l_fnd_user,
369 x_oid => l_oid);
370
371 if (l_oid = G_SUCCESS) then
372
373 l_allowed := is_present(p_attribute => 'userpassword', p_template_attr_list => l_attribute);
374 if (l_allowed) then
375 l_password := p_password;
376 else
377 l_password := null;
378 end if;
379
380 l_allowed := is_present(p_attribute => 'orclactivestartdate', p_template_attr_list => l_attribute);
381 if (l_allowed) then
382 l_start_date := p_start_date;
383 else
384 l_start_date := null;
385 end if;
386
387 l_allowed := is_present(p_attribute => 'orclactiveenddate', p_template_attr_list => l_attribute);
388 if (l_allowed) then
389 l_end_date := p_end_date;
390 else
391 l_start_date := null;
392 end if;
393
394 l_allowed := is_present(p_attribute => 'description', p_template_attr_list => l_attribute);
395 if (l_allowed) then
396 l_description := p_description;
397 else
398 l_description := null;
399 end if;
400
401 l_allowed := is_present(p_attribute => 'mail', p_template_attr_list => l_attribute);
402 if (l_allowed) then
403 l_email_address := p_email_address;
404 else
405 l_email_address := null;
406 end if;
407
408 l_allowed := is_present(p_attribute => 'facsimiletelephonenumber', p_template_attr_list => l_attribute);
409 if (l_allowed) then
410 l_fax := p_fax;
411 else
412 l_fax := null;
413 end if;
414
415 plsql_block :=
416 'begin fnd_ldap_user.create_user(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11); end;';
417 execute immediate plsql_block using p_user_name, in l_password,
418 l_start_date, l_end_date, l_description, l_email_address, l_fax, p_expire_password,
419 out x_user_guid, out x_password, out x_result;
420
421 else -- l_oid is G_FAILURE
422
423 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
424 then
425 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_oid is false');
426 end if;
427 x_result := l_fnd_user;
428
429 end if; -- l_oid
430
431 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
432 then
433 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
434 end if;
435
436 exception
437 when registration_failure_exception then
438 fnd_message.set_name ('FND', fnd_ldap_errm.FND_SSO_OID_REG_ERROR);
439 x_result := G_FAILURE;
440 when others then
441
442 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
443 then
444 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
445 end if;
446
447 l_tmp_str := sqlerrm;
448 l_err_code := fnd_ldap_errm.translate_ldap_errors(l_tmp_str);
449 fnd_message.set_name ('FND', l_err_code);
450
451 if l_err_code = fnd_ldap_errm.FND_SSO_LDAP_APPSDN_PWD_EXPIRD then
452 fnd_message.set_token('USER', l_tmp_str);
453 app_exception.raise_exception;
454 elsif l_err_code = fnd_ldap_errm.FND_SSO_LDAP_PWD_POLICY_ERR then
455 l_tmp_str := replace(l_tmp_str, 'Your', p_user_name);
456 fnd_message.set_token('REASON', l_tmp_str);
457 app_exception.raise_exception;
458 else
459 fnd_message.set_name ('FND', fnd_ldap_errm.FND_SSO_UNEXP_ERROR);
460 x_result := G_FAILURE;
461 end if;
462
463 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
464 then
465 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
466 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'l_err_code :' || l_err_code ||', l_tmp_str :' || l_tmp_str);
467 end if;
468
469 end create_user;
470 --
471 -------------------------------------------------------------------------------
472 procedure change_password(p_user_guid in raw,
473 p_user_name in varchar2,
474 p_new_pwd in varchar2,
475 p_expire_password in pls_integer,
476 x_password out nocopy varchar2,
477 x_result out nocopy pls_integer) is
478
479 l_module_source varchar2(256);
480 plsql_block varchar2(500);
481 l_fnd_user pls_integer;
482 l_oid pls_integer;
483 l_attribute varchar2(4000);
484 l_realm varchar2(4000);
485
486 begin
487 l_module_source := G_MODULE_SOURCE || 'change_password: ';
488
489 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
490 then
491 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
492 end if;
493
494 l_attribute := 'userpassword';
495 l_realm := get_realm_dn(p_user_guid=>p_user_guid,p_user_name=>p_user_name);
496
497 is_operation_allowed(p_realm=>l_realm,p_direction => G_EBIZ_TO_OID,
498 p_entity => G_IDENTITY,
499 p_operation => G_MODIFY,
500 x_attribute => l_attribute,
501 x_fnd_user => l_fnd_user,
502 x_oid => l_oid);
503
504 if (l_oid = G_SUCCESS) then
505
506 plsql_block :=
507 'begin fnd_ldap_user.change_password(:1, :2, :3, :4, :5, :6); end;';
508 execute immediate plsql_block using p_user_guid, p_user_name, p_new_pwd, p_expire_password, out x_password, out x_result;
509
510 else -- l_oid = G_FAILURE
511
512 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
513 then
514 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Password Sync Not Allowed');
515 end if;
516 x_result := l_fnd_user;
517
518 end if; -- l_oid
519
520 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
521 then
522 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
523 end if;
524
525 exception
526 when registration_failure_exception then
527 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
528 x_result := G_FAILURE;
529 when others then
530 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
531 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
532 then
533 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
534 end if;
535 x_result := G_FAILURE;
536
537 end change_password;
538 --
539 -------------------------------------------------------------------------------
540 procedure delete_user(p_user_guid in fnd_user.user_guid%type,
541 x_result out nocopy pls_integer) is
542
543 l_module_source varchar2(256);
544 plsql_block varchar2(500);
545 l_fnd_user pls_integer;
546 l_oid pls_integer;
547 l_attribute varchar2(4000);
548 l_realm varchar2(4000);
549
550 begin
551 l_module_source := G_MODULE_SOURCE || 'delete_user: ';
552
553 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
554 then
555 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
556 end if;
557 l_realm := get_realm_dn(p_user_guid=>p_user_guid);
558
559 is_operation_allowed(p_realm=>l_realm,p_direction => G_EBIZ_TO_OID,
560 p_entity => G_IDENTITY,
561 p_operation => G_DELETE,
562 x_attribute => l_attribute,
563 x_fnd_user => l_fnd_user,
564 x_oid => l_oid);
565
566 if (l_oid = G_SUCCESS) then
567
568 plsql_block :=
569 'begin fnd_ldap_user.delete_user(:1, :2); end;';
570 execute immediate plsql_block using p_user_guid, out x_result;
571
572 else -- l_oid = G_FAILURE
573
574 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
575 then
576 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_oid is false');
577 end if;
578 x_result := l_fnd_user;
579
580 end if; -- l_oid
581
582 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
583 then
584 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
585 end if;
586
587 exception
588 when registration_failure_exception then
589 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
590 x_result := G_FAILURE;
591 when others then
592 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
593 then
594 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
595 end if;
596 x_result := G_FAILURE;
597
598 end delete_user;
599 --
600 -------------------------------------------------------------------------------
601 procedure link_user(p_user_name in varchar2,
602 x_user_guid out nocopy raw,
603 x_password out nocopy varchar2,
604 x_result out nocopy pls_integer) is
605
606 l_module_source varchar2(256);
607 plsql_block varchar2(500);
608 l_fnd_user pls_integer;
609 l_oid pls_integer;
610 l_attribute varchar2(4000);
611 l_realm varchar2(4000);
612
613 begin
614
615 l_module_source := G_MODULE_SOURCE || 'link_user: ';
616
617 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
618 then
619 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
620 end if;
621 l_realm := get_realm_dn(p_user_name=>p_user_name);
622
623 is_operation_allowed(p_realm=>l_realm,p_direction => G_EBIZ_TO_OID,
624 p_entity => G_SUBSCRIPTION,
625 p_operation => G_ADD,
626 x_attribute => l_attribute,
627 x_fnd_user => l_fnd_user,
628 x_oid => l_oid);
629
630 if (l_oid = G_SUCCESS) then
631
632 plsql_block :=
633 'begin fnd_ldap_user.link_user(:1, :2, :3, :4); end;';
634 execute immediate plsql_block using p_user_name,
635 out x_user_guid, out x_password, out x_result;
636
637 else -- l_oid is G_FAILURE
638
639 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
640 then
641 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_oid id false');
642 end if;
643 x_result := l_fnd_user;
644
645 end if; -- l_oid
646
647 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
648 then
649 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
650 end if;
651
652 exception
653 when registration_failure_exception then
654 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
655 x_result := G_FAILURE;
656 when others then
657 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
658 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
659 then
660 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
661 end if;
662 x_result := G_FAILURE;
663
664 end link_user;
665 --
666 -------------------------------------------------------------------------------
667 procedure unlink_user(p_user_guid in fnd_user.user_guid%type,
668 p_user_name in varchar2,
669 x_result out nocopy pls_integer) is
670
671 l_module_source varchar2(256);
672 plsql_block varchar2(500);
673 l_fnd_user pls_integer;
674 l_oid pls_integer;
675 l_attribute varchar2(4000);
676 l_realm varchar2(4000);
677
678 begin
679
680 l_module_source := G_MODULE_SOURCE || 'unlink_user: ';
681
682 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
683 then
684 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
685 end if;
686 l_realm := get_realm_dn(p_user_guid=>p_user_guid,p_user_name=>p_user_name);
687
688 is_operation_allowed(p_realm=>l_realm,p_direction => G_EBIZ_TO_OID,
689 p_entity => G_SUBSCRIPTION,
690 p_operation => G_DELETE,
691 x_attribute => l_attribute,
692 x_fnd_user => l_fnd_user,
693 x_oid => l_oid);
694
695 if (l_oid = G_SUCCESS) then
696
697 plsql_block :=
698 'begin fnd_ldap_user.unlink_user(:1, :2, :3); end;';
699 execute immediate plsql_block using p_user_guid, p_user_name,
700 out x_result;
701
702 else -- l_oid is G_FAILURE
703
704 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
705 then
706 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_oid is false');
707 end if;
708 x_result := l_fnd_user;
709
710 end if; -- l_oid
711
712 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
713 then
714 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
715 end if;
716
717 exception
718 when registration_failure_exception then
719 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
720 x_result := G_FAILURE;
721 when others then
722 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
723 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
724 then
725 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
726 end if;
727 x_result := G_FAILURE;
728
729 end unlink_user;
730 --
731 -------------------------------------------------------------------------------
732 function user_exists(p_user_name in varchar2) return pls_integer is
733
734 l_module_source varchar2(256);
735 l_apps_sso varchar2(50);
736 l_profile_defined boolean;
737 l_orclappname varchar2(256);
738 l_obj_name varchar2(256);
739 plsql_block varchar2(500);
740 retval pls_integer;
741 sso_registration_failure exception;
742 l_sso_version varchar2(10);
743 l_allow_sync varchar2(1);
744
745 begin
746 l_module_source := G_MODULE_SOURCE || 'user_exists: ';
747
748 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
749 then
750 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
751 end if;
752
753 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
754 USER_ID_Z => -1,
755 RESPONSIBILITY_ID_Z => -1,
756 APPLICATION_ID_Z => -1,
757 ORG_ID_Z => -1,
758 val_z => l_allow_sync,
759 defined_z => l_profile_defined);
760
761 if (l_profile_defined and l_allow_sync = 'Y') then
762
763 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
764 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'APPS_SSO_LDAP_SYNC enabled.');
765 end if;
766
767 fnd_profile.get_specific(
768 name_z => 'APPS_SSO',
769 val_z => l_apps_sso,
770 defined_z => l_profile_defined);
771
772 if (l_apps_sso = 'PORTAL') OR (l_apps_sso = 'SSWA') then
773 ssoenabled := false;
774 else
775 ssoenabled := true;
776 end if;
777
778 if (ssoenabled) then
779 if (not init) then
780 init := true;
781 select object_name into l_obj_name from all_objects
782 where object_name = 'DBMS_LDAP' and object_type = 'PACKAGE BODY'
783 and status = 'VALID' and owner = 'SYS';
784 l_orclappname := get_orclappname;
785 -- no exception => everything is ok
786 registered := true;
787 end if;
788
789 if (registered) then
790 plsql_block :=
791 'begin :result := fnd_ldap_user.user_exists(:1); end;';
792 execute immediate plsql_block using out retval, p_user_name;
793 else
794 raise sso_registration_failure;
795 end if;
796 else -- if (!ssoenabled), simply return failure
797 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
798 then
799 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'SSO not enabled, returning false w/o querying OID user');
800 end if;
801 retval := G_FAILURE;
802 fnd_message.set_name ('FND', 'FND_SSO_NOT_ENABLED');
803 end if;
804
805 else -- APPS_SSO_LDAP_SYNC not enabled.
806
807 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
808 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'APPS_SSO_LDAP_SYNC not enabled.');
809 end if;
810
811 retval := G_FAILURE;
812 fnd_message.set_name ('FND', 'FND_SSO_NOT_ENABLED');
813
814 end if;
815
816 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
817 then
818 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
819 end if;
820
821 return retval;
822
823 exception
824 when sso_registration_failure then
825 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
826 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
827 then
828 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
829 end if;
830 raise;
831 when no_data_found then
832 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
833 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
834 then
835 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
836 end if;
837 raise;
838 when others then
839 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
840 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
841 then
842 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
843 end if;
844 raise;
845
846 end user_exists;
847 --
848 -------------------------------------------------------------------------------
849 procedure update_user(p_user_guid in raw,
850 p_user_name in varchar2,
851 p_password in varchar2 default null,
852 p_start_date in date default null,
853 p_end_date in date default null,
854 p_description in varchar2 default null,
855 p_email_address in varchar2 default null,
856 p_fax in varchar2 default null,
857 p_expire_password in pls_integer,
858 x_password out nocopy varchar2,
859 x_result out nocopy pls_integer) is
860
861 l_module_source varchar2(256);
862 plsql_block varchar2(500);
863 l_fnd_user pls_integer;
864 l_oid pls_integer;
865 l_attribute varchar2(4000);
866 l_allowed boolean;
867 l_password varchar2(400);
868 l_start_date date;
869 l_end_date date;
870 l_description varchar2(400);
871 l_email_address varchar2(256);
872 l_fax varchar2(50);
873 l_realm varchar2(4000);
874 l_err_code varchar2(200);
875 l_tmp_str varchar2(4000);
876 begin
877 l_module_source := G_MODULE_SOURCE || 'update_user: ';
878
879 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
880 then
881 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
882 end if;
883
884 l_attribute := 'userpassword,orclactivestartdate,orclactiveenddate,description,mail,facsimiletelephonenumber';
885 l_realm := get_realm_dn(p_user_guid=>p_user_guid,p_user_name=>p_user_name);
886
887 is_operation_allowed(p_realm=>l_realm,p_direction => G_EBIZ_TO_OID,
888 p_entity => G_IDENTITY,
889 p_operation => G_MODIFY,
890 x_attribute => l_attribute,
891 x_fnd_user => l_fnd_user,
892 x_oid => l_oid);
893
894 if (l_oid = G_SUCCESS) then
895
896 l_allowed := is_present(p_attribute => 'userpassword', p_template_attr_list => l_attribute);
897 if (l_allowed) then
898 l_password := p_password;
899 else
900 l_password := null;
901 end if;
902
903 l_allowed := is_present(p_attribute => 'orclactivestartdate', p_template_attr_list => l_attribute);
904 if (l_allowed) then
905 l_start_date := p_start_date;
906 else
907 l_start_date := null;
908 end if;
909
910 l_allowed := is_present(p_attribute => 'orclactiveenddate', p_template_attr_list => l_attribute);
911 if (l_allowed) then
912 l_end_date := p_end_date;
913 else
914 l_start_date := null;
915 end if;
916
917 l_allowed := is_present(p_attribute => 'description', p_template_attr_list => l_attribute);
918 if (l_allowed) then
919 l_description := p_description;
920 else
921 l_description := null;
922 end if;
923
924 l_allowed := is_present(p_attribute => 'mail', p_template_attr_list => l_attribute);
925 if (l_allowed) then
926 l_email_address := p_email_address;
927 else
928 l_email_address := null;
929 end if;
930
931 l_allowed := is_present(p_attribute => 'facsimiletelephonenumber', p_template_attr_list => l_attribute);
932 if (l_allowed) then
933 l_fax := p_fax;
934 else
935 l_fax := null;
936 end if;
937
938 plsql_block :=
939 'begin fnd_ldap_user.update_user(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11); end;';
940 execute immediate plsql_block using p_user_guid, p_user_name, l_password, l_start_date, l_end_date, l_description, l_email_address, l_fax, p_expire_password, out x_password, out x_result;
941
942 else -- l_oid is G_FAILURE
943
944 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
945 then
946 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_oid is false');
947 end if;
948 x_result := l_fnd_user;
949
950 end if; -- l_oid
951
952 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
953 then
954 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
955 end if;
956
957 exception
958 when registration_failure_exception then
959 fnd_message.set_name ('FND', fnd_ldap_errm.FND_SSO_OID_REG_ERROR);
960 x_result := G_FAILURE;
961 when others then
962
963 l_tmp_str := sqlerrm;
964 l_err_code := fnd_ldap_errm.translate_ldap_errors(l_tmp_str);
965 fnd_message.set_name ('FND', l_err_code);
966
967 if l_err_code = fnd_ldap_errm.FND_SSO_LDAP_APPSDN_PWD_EXPIRD then
968 fnd_message.set_token('USER', l_tmp_str);
969 app_exception.raise_exception;
970 elsif l_err_code = fnd_ldap_errm.FND_SSO_LDAP_PWD_POLICY_ERR then
971 l_tmp_str := replace(l_tmp_str, 'Your', p_user_name);
972 fnd_message.set_token('REASON', l_tmp_str);
973 app_exception.raise_exception;
974 else
975 fnd_message.set_name ('FND', fnd_ldap_errm.FND_SSO_UNEXP_ERROR);
976 x_result := G_FAILURE;
977 end if;
978
979 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
980 then
981 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
982 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'l_err_code :' || l_err_code ||', l_tmp_str :' || l_tmp_str);
983 end if;
984
985 end update_user;
986 --
987 -------------------------------------------------------------------------------
988 function validate_login(p_user_name in varchar2, p_password in varchar2) return boolean is
989
990 l_module_source varchar2(256);
991 l_apps_sso varchar2(50);
992 l_profile_defined boolean;
993 l_orclappname varchar2(256);
994 l_obj_name varchar2(256);
995 plsql_block varchar2(500);
996 retval boolean;
997 sso_registration_failure exception;
998 result pls_integer;
999 l_sso_version varchar2(10);
1000 l_allow_sync varchar2(1);
1001
1002 begin
1003 l_module_source := G_MODULE_SOURCE || 'validate_login: ';
1004
1005 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1006 then
1007 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1008 end if;
1009
1010 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
1011 USER_ID_Z => -1,
1012 RESPONSIBILITY_ID_Z => -1,
1013 APPLICATION_ID_Z => -1,
1014 ORG_ID_Z => -1,
1015 val_z => l_allow_sync,
1016 defined_z => l_profile_defined);
1017
1018 if (l_profile_defined and l_allow_sync = 'Y') then
1019
1020 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1021 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'APPS_SSO_LDAP_SYNC enabled.');
1022 end if;
1023
1024 fnd_profile.get_specific(
1025 name_z => 'APPS_SSO',
1026 val_z => l_apps_sso,
1027 defined_z => l_profile_defined);
1028
1029 if (l_apps_sso = 'PORTAL') OR (l_apps_sso = 'SSWA') then
1030 ssoenabled := false;
1031 else
1032 ssoenabled := true;
1033 end if;
1034
1035 if (ssoenabled) then
1036 if (not init) then
1037 init := true;
1038 select object_name into l_obj_name from all_objects
1039 where object_name = 'DBMS_LDAP' and object_type = 'PACKAGE BODY'
1040 and status = 'VALID' and owner = 'SYS';
1041 l_orclappname := get_orclappname;
1042 -- no exception => everything is ok
1043 registered := true;
1044 end if;
1045
1046 if (registered) then
1047 plsql_block :=
1048 'begin :result := fnd_ldap_user.validate_login(:1, :2); end;';
1049 execute immediate plsql_block using out result, p_user_name, p_password;
1050 if (result = G_SUCCESS) then
1051 retval := true;
1052 else
1053 retval := false;
1054 end if;
1055 else
1056 raise sso_registration_failure;
1057 end if;
1058 else -- if (!ssoenabled), simply return false
1059 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1060 then
1061 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'SSO not enabled, returning false');
1062 end if;
1063 retval := false;
1064 fnd_message.set_name ('FND', 'FND_SSO_NOT_ENABLED');
1065 end if;
1066
1067 else -- APPS_SSO_LDAP_SYNC not enabled.
1068
1069 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1070 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'APPS_SSO_LDAP_SYNC not enabled.');
1071 end if;
1072
1073 retval := false;
1074 fnd_message.set_name ('FND', 'FND_SSO_NOT_ENABLED');
1075
1076 end if;
1077
1078 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1079 then
1080 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1081 end if;
1082
1083 return retval;
1084
1085 exception
1086 when sso_registration_failure then
1087 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
1088 raise;
1089 when no_data_found then
1090 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
1091 raise;
1092
1093 end validate_login;
1094 --
1095 -------------------------------------------------------------------------------
1096 function get_orclappname return varchar2 is
1097
1098 l_module_source varchar2(256);
1099 orclAppName varchar2(256);
1100 sso_registration_failure exception;
1101
1102 begin
1103 l_module_source := G_MODULE_SOURCE || 'get_orclappname: ';
1104
1105 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1106 then
1107 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1108 end if;
1109
1110 select fnd_preference.get('#INTERNAL','LDAP_SYNCH', 'USERNAME')
1111 into orclAppName
1112 from dual;
1113
1114 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1115 then
1116 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1117 end if;
1118
1119 return orclAppName;
1120
1121 exception
1122 when no_data_found then
1123 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1124 then
1125 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1126 end if;
1127 raise;
1128 end get_orclappname;
1129 --
1130 -------------------------------------------------------------------------------
1131 procedure is_operation_allowed(p_realm in varchar2,p_direction in pls_integer default G_EBIZ_TO_OID,
1132 p_entity in pls_integer,
1133 p_operation in pls_integer,
1134 x_attribute in out nocopy varchar2,
1135 x_fnd_user out nocopy pls_integer,
1136 x_oid out nocopy pls_integer) is
1137
1138 l_module_source varchar2(256);
1139 l_apps_sso varchar2(50);
1140 l_profile_defined boolean;
1141 l_orclappname varchar2(256);
1142 l_obj_name varchar2(256);
1143 plsql_block varchar2(500);
1144 sso_registration_failure exception;
1145 l_registration pls_integer;
1146 l_sso_version varchar2(10);
1147 l_allow_sync varchar2(1);
1148
1149 begin
1150 l_module_source := G_MODULE_SOURCE || 'is_operation_allowed: ';
1151
1152 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1153 then
1154 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1155 end if;
1156
1157 x_fnd_user := G_SUCCESS;
1158 x_oid := G_FAILURE;
1159
1160 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
1161 USER_ID_Z => -1,
1162 RESPONSIBILITY_ID_Z => -1,
1163 APPLICATION_ID_Z => -1,
1164 ORG_ID_Z => -1,
1165 val_z => l_allow_sync,
1166 defined_z => l_profile_defined);
1167
1168 if (l_profile_defined and l_allow_sync = 'Y') then
1169
1170 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1171 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'APPS_SSO_LDAP_SYNC enabled.');
1172 end if;
1173
1174 get_registration(x_registration => l_registration);
1175 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1176 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1177 , 'Registration :: '||l_registration);
1178 end if;
1179
1180 if (l_registration = FND_LDAP_WRAPPER.G_VALID_REGISTRATION) then
1181 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1182 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1183 ,'Valid registration');
1184 end if;
1185 plsql_block :=
1186 'begin fnd_sso_registration.is_operation_allowed(:1, :2, :3, :4, :5, :6,null,:7); end;';
1187 execute immediate plsql_block using p_direction, p_entity, p_operation, in out x_attribute, out x_fnd_user, out x_oid,in p_realm;
1188
1189 elsif(l_registration = FND_LDAP_WRAPPER.G_INVALID_REGISTRATION) then
1190 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1191 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1192 ,'Invalid registration');
1193 end if;
1194 raise registration_failure_exception;
1195 elsif(l_registration = FND_LDAP_WRAPPER.G_NO_REGISTRATION) then
1196 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1197 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
1198 ,'No registration');
1199 end if;
1200 x_fnd_user := G_SUCCESS;
1201 x_oid := G_FAILURE;
1202 end if;
1203
1204 else -- APPS_SSO_LDAP_SYNC not enabled.
1205
1206 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1207 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'APPS_SSO_LDAP_SYNC not enabled.');
1208 end if;
1209
1210 end if;
1211
1212 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1213 then
1214 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1215 'out values x_fnd_user: '||x_fnd_user||' x_oid: '||x_oid);
1216 end if;
1217
1218
1219 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1220 then
1221 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1222 end if;
1223
1224 exception
1225 when registration_failure_exception then
1226 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
1227 x_fnd_user := G_FAILURE;
1228 x_oid := G_FAILURE;
1229 raise registration_failure_exception;
1230 when no_data_found then
1231 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
1232 x_fnd_user := G_FAILURE;
1233 x_oid := G_FAILURE;
1234 raise registration_failure_exception;
1235 when others then
1236 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
1237 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1238 then
1239 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1240 end if;
1241 raise;
1242 end is_operation_allowed;
1243 --
1244 -------------------------------------------------------------------------------
1245 procedure is_operation_allowed(p_realm in varchar2,p_operation in pls_integer,
1246 x_fnd_user out nocopy pls_integer,
1247 x_oid out nocopy pls_integer) is
1248
1249 l_module_source varchar2(256);
1250 l_apps_sso varchar2(50);
1251 l_profile_defined boolean;
1252 l_orclappname varchar2(256);
1253 l_obj_name varchar2(256);
1254 plsql_block varchar2(500);
1255 sso_registration_failure exception;
1256
1257 begin
1258 l_module_source := G_MODULE_SOURCE || 'is_operation_allowed: ';
1259
1260 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1261 then
1262 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1263 end if;
1264
1265 x_fnd_user := G_SUCCESS;
1266 x_oid := G_FAILURE;
1267
1268 fnd_profile.get_specific(
1269 name_z => 'APPS_SSO',
1270 val_z => l_apps_sso,
1271 defined_z => l_profile_defined);
1272
1273 if (l_apps_sso = 'PORTAL') OR (l_apps_sso = 'SSWA') then
1274 ssoenabled := false;
1275 else
1276 ssoenabled := true;
1277 end if;
1278
1279 if (ssoenabled) then
1280
1281 if (not init) then
1282 init := true;
1283 select object_name into l_obj_name from all_objects
1284 where object_name = 'DBMS_LDAP' and object_type = 'PACKAGE BODY'
1285 and status = 'VALID' and owner = 'SYS';
1286 l_orclappname := get_orclappname;
1287 -- no exception => everything is ok
1288 registered := true;
1289 end if;
1290
1291 if (registered) then
1292 plsql_block :=
1293 'begin fnd_sso_registration.is_operation_allowed(:1, :2, :3,null,:4); end;';
1294 execute immediate plsql_block using p_operation, out x_fnd_user, out x_oid,in p_realm;
1295 else
1296 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1297 then
1298 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'SSO profile enabled but improper regitration');
1299 end if;
1300 -- In this case the OID operation should be allowed so that it fail
1301 -- subsequently causing the FND operation also fail
1302 raise sso_registration_failure;
1303 end if;
1304
1305 else -- if (!ssoenabled), simply return false
1306 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1307 then
1308 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'SSO not enabled, returning false');
1309 end if;
1310 x_fnd_user := G_SUCCESS;
1311 x_oid := G_FAILURE;
1312 fnd_message.set_name ('FND', 'FND_SSO_NOT_ENABLED');
1313 end if;
1314
1315 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1316 then
1317 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1318 'out values x_fnd_user: '||x_fnd_user||' x_oid: '||x_oid);
1319 end if;
1320
1321 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1322 then
1323 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1324 end if;
1325
1326 exception
1327 when sso_registration_failure then
1328 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
1329 x_fnd_user := G_SUCCESS;
1330 x_oid := G_SUCCESS;
1331 when no_data_found then
1332 fnd_message.set_name ('FND', 'FND_SSO_OID_REG_ERROR');
1333 x_fnd_user := G_SUCCESS;
1334 x_oid := G_SUCCESS;
1335 when others then
1336 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
1337 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1338 then
1339 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1340 end if;
1341 raise;
1342 end is_operation_allowed;
1343 --
1344 -------------------------------------------------------------------------------
1345 procedure get_registration(x_registration out nocopy pls_integer) is
1346
1347 l_module_source varchar2(256);
1348 l_apps_sso varchar2(50);
1349 l_profile_defined boolean;
1350 l_return_value pls_integer;
1351 l_sso_enabled boolean;
1352
1353 l_orclappname varchar2(256);
1354 l_obj_name varchar2(256);
1355 plsql_block varchar2(500);
1356
1357 begin
1358 l_module_source := G_MODULE_SOURCE || 'get_registration ';
1359
1360 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1361 then
1362 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1363 end if;
1364
1365 fnd_profile.get_specific(
1366 name_z => 'APPS_SSO',
1367 val_z => l_apps_sso,
1368 defined_z => l_profile_defined);
1369
1370 if (l_apps_sso = 'PORTAL') OR (l_apps_sso = 'SSWA')
1371 then
1372 l_sso_enabled := false;
1373 else
1374 l_sso_enabled := true;
1375 end if;
1376
1377 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1378 then
1379 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'SSO enabled ::');
1380 end if;
1381
1382
1383 if (l_sso_enabled) then
1384 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1385 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'SSO profile is enabled');
1386 end if;
1387
1388 if (not initreg) then
1389 initreg := true;
1390 select object_name into l_obj_name from all_objects
1391 where object_name = 'DBMS_LDAP' and object_type = 'PACKAGE BODY'
1392 and status = 'VALID' and owner = 'SYS';
1393
1394 l_orclappname := get_orclappname;
1395 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1396 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_orclappname :: '||l_orclappname);
1397 end if;
1398
1399 if(l_orclappname IS NULL) then
1400 registration := G_INVALID_REGISTRATION;
1401 else
1402 registration := G_VALID_REGISTRATION;
1403 end if;
1404
1405 -- no exception => everything is ok
1406 x_registration := registration;
1407 else
1408 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1409 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Already initialized');
1410 end if;
1411
1412 x_registration := registration;
1413 end if;
1414 else
1415 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1416 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'SSO profile not set');
1417 end if;
1418
1419 x_registration := G_NO_REGISTRATION;
1420 end if;
1421
1422 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1423 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1424 end if;
1425
1426 exception
1427 when no_data_found then
1428 registration := G_INVALID_REGISTRATION;
1429 x_registration := registration;
1430
1431 when others then
1432 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
1433 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1434 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1435 end if;
1436 raise;
1437 end get_registration;
1438
1439 function is_present(p_attribute in varchar2, p_template_attr_list in varchar2) return boolean is
1440
1441 is_present boolean := false;
1442 l_module_source varchar2(256);
1443 num pls_integer := 0;
1444 st pls_integer := 0;
1445 en pls_integer := 0;
1446 l_str varchar2(4000) := '';
1447 l_tmp varchar2(4000);
1448 l_tmp2 varchar2(4000);
1449 l_template_attr_list varchar2(4000);
1450
1451 begin
1452 l_module_source := G_MODULE_SOURCE || 'is_present: ';
1453 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1454 then
1455 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1456 end if;
1457 l_template_attr_list :=trim_attribute(p_template_attr_list);
1458 st := 1;
1459 en := INSTR(l_template_attr_list,',', st, 1);
1460
1461
1462 if(en <= 0)
1463 then
1464 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1465 then
1466 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Only one attribute en = '||en);
1467 end if;
1468 l_tmp := l_template_attr_list;
1469 if(upper(p_attribute) = upper(l_tmp) ) then
1470 is_present := true;
1471 end if;
1472 else
1473
1474 WHILE (en > 0)
1475 LOOP
1476 l_tmp := SUBSTR(l_template_attr_list, st, en-st);
1477 l_tmp := trim(l_tmp);
1478 if(upper(p_attribute) = upper(l_tmp) ) then
1479 is_present := true;
1480 end if;
1481 st := en+1;
1482 en := INSTR(l_template_attr_list,',', st, 1);
1483 num := num+1;
1484 if(en = 0) then
1485 l_tmp := SUBSTR(l_template_attr_list, st, length(l_template_attr_list)-st+1);
1486 l_tmp := trim(l_tmp);
1487 if(upper(p_attribute) = upper(l_tmp) ) then
1488 is_present := true;
1489 end if;
1490 end if;
1491
1492 END LOOP;
1493 end if;
1494
1495 return is_present;
1496
1497 end is_present;
1498
1499 function trim_attribute(p_attr in varchar2) return varchar2 is
1500
1501 l_tmp varchar2(4000);
1502 begin
1503
1504 l_tmp := trim(p_attr);
1505 l_tmp := ltrim(l_tmp, '(');
1506 l_tmp := rtrim(l_tmp, ')');
1507 l_tmp := trim(l_tmp);
1508
1509 return l_tmp;
1510
1511 end trim_attribute;
1512
1513 /*
1514 * Bug 6249845
1515 * Wrapper for FND_OID_PLUG.get_realm_dn
1516 */
1517 function get_realm_dn( p_user_guid in raw default null, p_user_name in varchar2 default null)
1518 return varchar2
1519 is
1520 l_module_source varchar2(4000);
1521 l_result varchar2(4000);
1522 plsql_block varchar2(100);
1523 BEGIN
1524 l_module_source := G_MODULE_SOURCE || 'get_realm_dn';
1525 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1526 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1527 end if;
1528 if (oid_synchronization_enabled) then
1529 plsql_block := 'begin :1 := fnd_oid_plug.get_realm_dn(:2, :3); end;';
1530 execute immediate plsql_block using out l_result, p_user_guid,p_user_name;
1531 else
1532 l_result := null;
1533 end if;
1534 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1535 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'END->'||l_result);
1536 end if;
1537 return l_result;
1538
1539
1540
1541 END get_realm_dn;
1542
1543 function oid_synchronization_enabled return boolean
1544 is
1545 l_module_source varchar2(4000);
1546 l_result boolean;
1547 l_profile_defined boolean;
1548 l_allow_sync varchar2(1);
1549 registration pls_integer;
1550
1551 begin
1552 l_module_source := G_MODULE_SOURCE || 'oid_synchronization_enabled ';
1553 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1554 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1555 end if;
1556 get_registration(registration);
1557
1558 if ( registration=G_VALID_REGISTRATION) then
1559
1560 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
1561 USER_ID_Z => -1,
1562 RESPONSIBILITY_ID_Z => -1,
1563 APPLICATION_ID_Z => -1,
1564 ORG_ID_Z => -1,
1565 val_z => l_allow_sync,
1566 defined_z => l_profile_defined);
1567
1568 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1569 if (l_profile_defined) then
1570 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source ,'SITE APPS_SSO_LDAP_SYNC='||l_allow_sync );
1571 else
1572 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source ,'APPS_SSO_LDAP_SYNC not defined' );
1573 end if;
1574 end if;
1575 l_result := l_profile_defined and l_allow_sync = 'Y';
1576 else
1577 l_result := false;
1578 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1579 if(registration = FND_LDAP_WRAPPER.G_INVALID_REGISTRATION) then
1580 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source ,'Invalid registration');
1581 elsif(registration = FND_LDAP_WRAPPER.G_NO_REGISTRATION) then
1582 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source ,'No registration');
1583 else
1584 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source ,'Unknown returning status:'||registration);
1585 end if;
1586 end if;
1587 end if;
1588
1589
1590 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1591 if (l_result) then
1592 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'END-> TRUE');
1593 else
1594 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'END-> FALSE');
1595 end if;
1596 end if;
1597 return l_result;
1598 exception when others then
1599 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1600 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source ,'Unexpected exception '||sqlerrm);
1601 raise;
1602 end if;
1603
1604 end oid_synchronization_enabled;
1605
1606 end fnd_ldap_wrapper;