1 PACKAGE BODY FND_OID_PLUG AS
2 /* $Header: AFSCOPGB.pls 120.15.12020000.2 2012/07/19 17:05:26 ctilley ship $ */
3
4
5 g_realm varchar2(4000);
6 g_user_search varchar2(4000);
7 G_NICKNAMEATT varchar2(4000);
8
9 g_user_repo varchar2(4000);
10 g_cnatt varchar2(4000);
11
12 G_STATIC_DESC varchar2(100):='STATIC';
13 /*
14
15 PREFERENCES
16 ==============
17
18
19
20 REALM:
21 NICKNAMEATTR:
22 COMMONNAMEATTR:
23 SEARCH_BASE:
24 CREATE_BASE:
25
26 PLUGIN_NAME: if null, all above are filled with defaults.
27 if not null all are null and the plugin must implement
28 getRealm(Username)
29 getDN(username)
30 getUserRepository(username)
31 getRDN(username)
32
33
34
35 */
36 /*
37 * Constants
38 */
39 G_MODULE_SOURCE varchar2(100):= 'fnd.plsql.oid.fnd_oid_plug.';
40 G_PREF_USER varchar2(20):='#INTERNAL';
41 G_PREF_MODULE varchar2(20):='OID_CONF';
42
43 /**
44 * OPTION opt_mode
45 * ============
46 * If the option has the opt_mode 'STATIC' the value in the preference is the one to use.
47 * If it is 'DYNAMIC' then the value is a PL/SQL statement to execute.
48 * If it is 'RUNTIME' then the preference value is not relevant, and the actual must be recalculated
49 * For example 'RUNTIME','NICKNAME_ATTR' means there will be a QUERY to ldap to calculate the value,
50 * and the result will be cached in the package.
51 * 'NOCACHE' is the same as 'RUNTIME' but the value is calculated every time. Not implemented
52 * for now, since values are oftenly catched in other places too, so it may inefective unless a detailed
53 * analysis is done for the value,i.e., remove from ALL the code any place where nicknameAttName is cached
54 *
55 **/
56
57 G_STATIC pls_integer := 0;
58 G_DYNAMIC pls_integer := 1;
59 G_RUNTIME pls_integer := 2;
60
61
62 type option_type_rec is record (
63 opt_mode pls_integer,
64 val varchar2(4000)
65 );
66
67 type option_type is table of option_type_rec index by varchar2(30);
68
69 g_option option_type;
70
71 /*
72 * GLOBALS
73 */
74 plugin_type pls_integer := null;
75 invalid_deployment exception;
76 single_init boolean := false;
77 g_cached_realm varchar2(4000);
78
79 validated boolean:= false; -- plugin version
80
81
82
83
84 FUNCTION loadOption( name in varchar2, opt in out nocopy option_type_rec) return boolean
85 IS
86
87 val varchar2(40);
88 found boolean := TRUE;
89 l_module_source varchar2(200) := G_MODULE_SOURCE || 'loadOption: ';
90
91 BEGIN
92 val := fnd_preference.get(G_PREF_USER,G_PREF_MODULE,name||'_opt_mode');
93 IF val is null then found := FALSE;
94 ELSIF (val='STATIC') THEN opt.opt_mode:=0;
95 ELSIF (val='DYNAMIC') THEN opt.opt_mode:=1;
96 ELSIF (val='RUNTIME') THEN opt.opt_mode:=1;
97 ELSE raise invalid_deployment;
98 END IF;
99 IF found THEN
100 opt.val := fnd_preference.get(G_PREF_USER,G_PREF_MODULE,name);
101 found := opt.val is not null;
102 if (found) THEN
103 g_option(name):= opt;
104 END IF;
105 END IF;
106 return found;
107 EXCEPTION WHEN OTHERS THEN
108 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
109 then
110 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
111 end if;
112 raise;
113
114 END loadOption;
115
116 PROCEDURE saveOption( name in varchar2, opt_mode in pls_INTEGER , value in varchar2)
117 is
118 l_module_source varchar2(200) := G_MODULE_SOURCE || 'saveOption: ';
119 BEGIN
120 if (opt_mode=0) THEN
121 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,name||'_opt_mode','STATIC');
122 elsif (opt_mode=1) THEN
123 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,name||'_opt_mode','DYNAMIC');
124 elsif (opt_mode=2) THEN
125 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,name||'_opt_mode','RUNTIME');
126 else
127 raise invalid_deployment;
128 END IF;
129 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,name,value);
130
131 if (g_option.exists(name)) then
132 g_option.delete(name); --cancel the cache
133 end if;
134 EXCEPTION WHEN OTHERS THEN
135 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
136 then
137 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
138 end if;
139 raise;
140
141 END saveOption;
142
143
144 PROCEDURE deleteOption( name in varchar2)
145 is
146 l_module_source varchar2(200) := G_MODULE_SOURCE || 'saveOption: ';
147 BEGIN
148 fnd_preference.remove(G_PREF_USER,G_PREF_MODULE,name);
149 fnd_preference.remove(G_PREF_USER,G_PREF_MODULE,name||'_opt_mode');
150 if (g_option.exists(name)) then
151 g_option.delete(name); --cancel the cache
152 end if;
153
154 END deleteOption;
155 FUNCTION getOption(name in varchar, opt in out nocopy option_type_rec) return boolean
156 IS
157 l_module_source varchar2(200):= G_MODULE_SOURCE || 'getOption: ';
158 BEGIN
159 IF (g_option.exists(name)) then
160 opt.opt_mode := g_option(name).opt_mode;
161 opt.val := g_option(name).val;
162 return true;
163 else return loadOption(name,opt);
164 END IF;
165 EXCEPTION WHEN OTHERS THEN
166 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
167 then
168 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
169 end if;
170 raise;
171
172 END getOption;
173
174 /*
175 * FOWARD declarations
176 */
177 function get_username_from_guid(p_user_guid IN fnd_user.user_guid%type ) return varchar2;
178
179
180
181
182
183 procedure getDefaultRealm(realm out nocopy varchar2) IS
184 opt option_type_rec;
185 b boolean;
186 BEGIN
187
188 if NOT getOption('DEFAULT_REALM',opt) THEN
189 raise CONFIGURATION_ERROR;
190 END IF;
191 IF opt.opt_mode=G_STATIC THEN
192 realm := opt.val;
193 ELSIF opt.opt_mode=G_RUNTIME THEN
194 realm := FND_SSO_REGISTRATION.getDefaultRealm;
195 ELSIF opt.opt_mode=G_DYNAMIC THEN
196 execute immediate opt.val using realm;
197 END IF;
198
199 END getDefaultRealm;
200
201 procedure getDefaultCreateBase(realm in varchar2, parentDN out nocopy varchar2 ) IS
202 opt option_type_rec;
203
204 BEGIN
205 if NOT getOption('DEFAULT_CREATE_BASE',opt) THEN
206 raise configuration_error;
207 END IF;
208 IF opt.opt_mode=G_STATIC THEN
209 parentDN := opt.val;
210 ELSIF opt.opt_mode=G_RUNTIME THEN
211 parentDN := FND_SSO_REGISTRATION.get_realm_attribute(realm,'orclCommonCreateUserBase');
212 ELSIF opt.opt_mode=G_DYNAMIC THEN
213 execute immediate opt.val using realm,parentDN;
214 END IF;
215 END getDefaultCreateBase;
216
217 procedure getCreateBase( user_id in INTEGER, user_name in varchar2,realm in varchar2, parentDn out nocopy varchar2) IS
218 opt option_type_rec;
219 BEGIN
220 if NOT getOption('CREATE_BASE',opt) THEN
221 raise CONFIGURATION_ERROR;
222 END IF;
223 IF opt.opt_mode=G_STATIC THEN
224 parentDN := opt.val;
225 ELSIF opt.opt_mode=G_RUNTIME THEN
226 getDefaultCreateBase(realm,parentDN);
227 ELSIF opt.opt_mode=G_DYNAMIC THEN
228 execute immediate opt.val using user_id,user_name,realm,parentDN;
229 END IF;
230
231 END getCreateBase;
232
233 procedure getRealm( user_id in INTEGER, user_name in varchar2, realmDn out nocopy varchar2) IS
234 opt option_type_rec;
235 BEGIN
236 if NOT getOption('REALM',opt) THEN
237 raise CONFIGURATION_ERROR;
238 END IF;
239 IF opt.opt_mode=G_STATIC THEN
240 realmDn := opt.val;
241 ELSIF opt.opt_mode=G_RUNTIME THEN
242 getDefaultRealm(realmDn);
243 ELSIF opt.opt_mode=G_DYNAMIC THEN
244 execute immediate opt.val using user_id,user_name,realmDn;
245 END IF;
246 END getRealm;
247
248
249
250 /**
251 * PRIVATE FUNCTIONS
252 */
253
254 /*
255 * Validate Template
256 * Given a plug in , we need to validate if
257 * 1) declares the function
258 * 2) has the same paratermer
259 * We validate EXACT MATCH: that is parameters must have the same NAME and TYPE.
260 * RETURN: TRUE if all test passed
261 * FALSE : if sometyhign different was detected
262 * OUT: error_msg: whe return false, a message indicating the problem
263 *
264 */
265 function validateTemplate (template in varchar2 , name VARCHAR2, error_msg out nocopy varchar2) return boolean is
266
267 prop_overload DBMS_DESCRIBE.NUMBER_TABLE;
268 prop_position DBMS_DESCRIBE.NUMBER_TABLE;
269 prop_c_level DBMS_DESCRIBE.NUMBER_TABLE;
270 prop_arg_name DBMS_DESCRIBE.VARCHAR2_TABLE;
271 prop_dty DBMS_DESCRIBE.NUMBER_TABLE;
272 prop_def_val DBMS_DESCRIBE.NUMBER_TABLE;
273 prop_p_opt_mode DBMS_DESCRIBE.NUMBER_TABLE;
274 prop_length DBMS_DESCRIBE.NUMBER_TABLE;
275 prop_precision DBMS_DESCRIBE.NUMBER_TABLE;
276 prop_scale DBMS_DESCRIBE.NUMBER_TABLE;
277 prop_radix DBMS_DESCRIBE.NUMBER_TABLE;
278 prop_spare DBMS_DESCRIBE.NUMBER_TABLE;
279
280 exp_overload DBMS_DESCRIBE.NUMBER_TABLE;
281 exp_position DBMS_DESCRIBE.NUMBER_TABLE;
282 exp_c_level DBMS_DESCRIBE.NUMBER_TABLE;
283 exp_arg_name DBMS_DESCRIBE.VARCHAR2_TABLE;
284 exp_dty DBMS_DESCRIBE.NUMBER_TABLE;
285 exp_def_val DBMS_DESCRIBE.NUMBER_TABLE;
286 exp_p_opt_mode DBMS_DESCRIBE.NUMBER_TABLE;
287 exp_length DBMS_DESCRIBE.NUMBER_TABLE;
288 exp_precision DBMS_DESCRIBE.NUMBER_TABLE;
289 exp_scale DBMS_DESCRIBE.NUMBER_TABLE;
290 exp_radix DBMS_DESCRIBE.NUMBER_TABLE;
291 exp_spare DBMS_DESCRIBE.NUMBER_TABLE;
292 datatypes dbms_ldap.string_collection;
293 idx INTEGER := 0;
294
295 BEGIN
296 DBMS_DESCRIBE.DESCRIBE_PROCEDURE(name, null, null,prop_overload,prop_position,prop_c_level,prop_arg_name,
297 prop_dty,prop_def_val,prop_p_opt_mode,prop_length,prop_precision,prop_scale,prop_radix,prop_spare);
298
299 DBMS_DESCRIBE.DESCRIBE_PROCEDURE(template, null, null,
300 exp_overload,exp_position, exp_c_level,exp_arg_name,exp_dty,
301 exp_def_val,exp_p_opt_mode, exp_length,exp_precision, exp_scale,exp_radix,exp_spare);
302
303 error_msg :=null;
304 while idx is not null LOOP
305 BEGIN
306 idx := idx + 1;
307 if (prop_arg_name(idx)<>exp_arg_name(idx) ) THEN
308 error_msg :='Parameter '||idx||':Expected name '||exp_arg_name(idx)||' but found '||prop_arg_name(idx);
309 ELSIF (prop_dty(idx)<>exp_dty(idx)) THEN
310 error_msg :='Parameter '||idx||': incorrect datatype for '||exp_arg_name(idx);
311 ELSIF (prop_dty(idx)<>exp_dty(idx)) THEN
312 error_msg :='Parameter '||idx||': incorrect in/out opt_mode for '||exp_arg_name(idx);
313 END IF;
314 if (error_msg is not null) then
315 return false;
316 END IF;
317 EXCEPTION WHEN NO_DATA_FOUND THEN idx:= null;
318 END;
319 END LOOP;
320 return true;
321 EXCEPTION
322 WHEN OTHERS THEN
323 error_msg := 'Error:'||sqlcode||' - '||sqlerrm;
324 return false;
325
326
327 END validateTemplate;
328
329
330
331
332 function getRealmNickNameattr(realm in varchar2) return varchar2
333 is
334 --ldap dbms_ldap.session;
335 ret varchar2(80):= null;
336 realm_idx pls_integer;
337 --flag pls_integer;
338 l_module_source varchar2(1000) ;
339 BEGIN
340 l_module_source := G_MODULE_SOURCE || 'getRealmNickNameattr: ';
341
342 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
343 then
344 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin realm='||realm);
345 end if;
346
347 ret:= FND_SSO_REGISTRATION.get_realm_attribute(realm,'orclCommonNickNameAttribute');
348
349 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
350 then
351 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END nicknameAttr='||ret);
352 end if;
353 return ret;
354 EXCEPTION WHEN OTHERS THEN
355 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
356 then
357 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
358 end if;
359 raise;
360
361 end getRealmNickNameattr;
362
363
364
365 function PluginVersion return NUMBER
366
367 is
368 l_version varchar2(4000);
369 l_plug_ver number;
370 l_module_source varchar2(1000);
371 BEGIN
372 l_module_source := G_MODULE_SOURCE || 'PluginVersion';
373
374 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
375 then
376 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
377 end if;
378
379 l_version := FND_PREFERENCE.get('#INTERNAL','OID_CONF','PLUGIN_VERSION');
380
381 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
382 then
383 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Retrieved PLUGIN_VERSION preference');
384 end if;
385
386 if (l_version is not null) THEN
387
388 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
389 then
390 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'plugin version is: '||l_version);
391 end if;
392
393 -- Converting to force NLS numeric format regardless of user preference.
394 -- Reference bug 9358444
395 l_plug_ver := to_number(l_version,'9D9','NLS_NUMERIC_CHARACTERS=''.,''');
396
397 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
398 then
399 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'Converted value: '||to_char(l_plug_ver));
400 end if;
401
402 return l_plug_ver;
403
404 ELSIF FND_PREFERENCE.exists('#INTERNAL','OID_CONF','CREATE_BASE') THEN
405
406 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
407 then
408 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'CREATE_BASE exists - PLUGIN_VERSION did not - return 1.0');
409 end if;
410
411 return 1.0;
412 ELSE
413
414 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
415 then
416 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'Neither preference exists - return 0.9');
417 end if;
418
419 return 0.9;
420 END IF;
421
422 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
423 then
424 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
425 end if;
426
427 END PluginVersion;
428 /*
429 * The syntax changed from previous versions.
430 * And needs to be represented in the new syntax.
431 * However, the only supported deployments where
432 * setPlugin()
433 * setPlugin(defaultCreateBase);
434 * we only concern about them
435 * So, with the exception of the defaultCreateBase, everything comes from the defaultRealm
436 * Upgrades to version 1.1
437 *
438 */
439 procedure UpgradePlugin
440 IS
441 type params_t is table of varchar2(4000) index by varchar2(200);
442 type list_t is table of varchar2(200) ;
443
444 old_val params_t;
445 params list_t;
446 version number;
447 l_module_source varchar2(200) := G_MODULE_SOURCE || 'UpgradePlugin: ';
448
449
450 BEGIN
451
452 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
453 then
454 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'BEGIN' );
455 end if;
456 version := PluginVersion;
457 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
458 then
459 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'current version:'||version );
460 end if;
461
462 if (version > 1.0 ) THEN
463 -- no changes needed this time
464 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
465 then
466 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END(no changes)' );
467 end if;
468 return ;
469 END IF;
470 if (version=1.0) THEN
471 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
472 then
473 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'upgrading from 1.0');
474 end if;
475 params := list_t('CN_ATT_CACHED','CREATE_BASE','NICK_ATT_CACHED','REALM','SEARCH_BASE_CACHED','TYPE');
476 for p in params.first .. params.last loop
477 old_val(params(p)) := FND_PREFERENCE.GET('#INTERNAL','OID_CONF',params(p));
478 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
479 then
480 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'old '||params(p)||'='||old_val(params(p)) );
481 end if;
482 end loop;
483
484 ELSE
485 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
486 then
487 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'very old or non existent registration');
488 end if;
489 old_val('REALM') := fnd_sso_registration.getdefaultrealm;
490 old_val('CREATE_BASE') := FND_SSO_REGISTRATION.get_realm_attribute(old_val('REALM') ,'orclCommonUserCreatebase');
491 old_val('CN_ATT_CACHED') := FND_SSO_REGISTRATION.get_realm_attribute(old_val('REALM') ,'orclCommonNamingAttribute');
492 -- remove null values from the list
493 if old_val('REALM') is null then old_val.delete('REALM'); end if;
494 if old_val('CREATE_BASE') is null then old_val.delete('CREATE_BASE'); end if;
495 if old_val('CN_ATT_CACHED') is null then old_val.delete('CN_ATT_CACHED'); end if;
496
497 END IF;
498
499
500 -- remove all
501 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
502 then
503 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'removing previous preferences');
504 end if;
505 FND_PREFERENCE.DELETE_ALL('#INTERNAL','OID_CONF');
506
507 if NOT old_val.exists('REALM') THEN
508 old_val('REALM') := fnd_sso_registration.getdefaultrealm;
509 END IF;
510 if NOT old_val.exists('CREATE_BASE') THEN
511 old_val('CREATE_BASE') := FND_SSO_REGISTRATION.get_realm_attribute(old_val('REALM') ,'orclCommonUserCreatebase');
512 END IF;
513
514 if NOT old_val.exists('CN_ATT_CACHED') THEN
515 old_val('CN_ATT_CACHED') := FND_SSO_REGISTRATION.get_realm_attribute(old_val('REALM') ,'orclCommonNamingAttribute');
516 END IF;
517
518 saveOption('DEFAULT_REALM',G_STATIC,old_val('REALM'));
519 saveOption('REALM',G_STATIC,old_val('REALM'));
520 saveOption('DEFAULT_CREATE_BASE',G_STATIC,old_val('CREATE_BASE'));
521 saveOption('CREATE_BASE',G_STATIC,old_val('CREATE_BASE'));
522 saveOption('RDN',G_STATIC,old_val('CN_ATT_CACHED') );
523 FND_PREFERENCE.put('#INTERNAL','OID_CONF','PLUGIN_VERSION','1.1');
524 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
525 then
526 params := list_t('DEFAULT_REALM','REALM','DEFAULT_CREATE_BASE','CREATE_BASE','RDN','PLUGIN_VERSION');
527 for p in params.first .. params.last loop
528 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,' pref set : '||params(p)||'='||FND_PREFERENCE.GET('#INTERNAL','OID_CONF',params(p)));
529 end loop;
530 end if;
531 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
532 then
533 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'BEGIN' );
534 end if;
535 EXCEPTION WHEN OTHERS THEN
536 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
537 then
538 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
539 end if;
540 raise;
541
542 END UpgradePlugin;
543
544 procedure validateVersion
545 IS
546 BEGIN
547 IF (NOT validated) THEN
548 if PluginVersion<1.1 THEN
549 UpgradePlugin;
550 END IF;
551 validated:=true;
552 END IF;
553 END validateVersion;
554
555
556
557
558 --
559 procedure SetPlugIn (
560 p_defaultRealm in varchar2 default null,
561 p_default_user_repository in varchar2 default null,
562 plugin_name in varchar2 default null
563 )
564 is
565 l_module_source VARCHAR2(1000) := G_MODULE_SOURCE || 'SetPlugin(package): ';
566 i pls_integer;
567 c pls_integer;
568 o dba_objects.owner%type;
569 n dba_objects.object_name%type;
570 errmsg varchar2(4000);
571 opt option_type_rec;
572 val varchar2(4000);
573 realm varchar2(4000);
574 usePlugin boolean := false;
575 idx pls_integer;
576 ldap dbms_ldap.session;
577 flag pls_integer ;
578 guid varchar2(4000);
579 testing_mode pls_integer := 1;
580 l_session_flag boolean := false;
581
582 BEGIN
583 -- if there is no session , create one , so we cann LOG acctions
584 -- VALIDATE THERE IS A PACKAGE
585 UpgradePlugin;
586 ldap:= FND_LDAP_UTIl.c_get_oid_session(flag);
587 l_session_flag := true; /* fix for bug 8271359 */
588
589 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
590 then
591 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag = true ' );
592 end if;
593
594 if (plugin_name is not null ) THEN
595 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
596 then
597 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'plugin_name is: '||plugin_name);
598 end if;
599 n:= upper(plugin_name);
600 i:= instr(plugin_name,'.');
601 IF (i=0) THEN
602 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
603 then
604 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Verify plugin package is valid ' );
605 end if;
606
607 select count(*) into c from user_objects where object_name=n
608 and object_type in ('PACKAGE','PACKAGE BODY') and status='VALID';
609 IF (c<>2) THEN
610 raise_application_error(-20100,'Check existence and validity of '||plugin_name||' body and specs');
611 END IF;
612 ELSE
613 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
614 then
615 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Verify plugin package for owner is valid ' );
616 end if;
617 o:= substr(upper(plugin_name),0,i-1);
618 n:= substr(upper(plugin_name),i+1);
619 select count(*) into c from dba_objects where
620 owner=o and
621 object_name=n and
622 object_type in ('PACKAGE','PACKAGE BODY') and status='VALID';
623
624 IF (c<>2) THEN
625 raise_application_error(-20100,'Check existence and validity of '||plugin_name||' body and specs');
626 END IF;
627 END IF;
628 usePlugin:=true;
629 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
630 then
631 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'usePlugin set to true');
632 end if;
633 ELSE
634 usePlugin :=false;
635 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
636 then
637 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'usePlugin set to false');
638 end if;
639 END IF;
640
641 -- DEPLOY WHAT PACKAGE PROVIDES
642 if (testing_mode=1) THEN
643 if usePlugin AND ValidateTemplate('FND_OID_PLUG.getDefaultRealm_Template',plugin_name||'.getDefaultRealm',errmsg) THEN
644 saveOption('DEFAULT_REALM',G_DYNAMIC,'BEGIN '||plugin_name||'.getDefaultRealm(:1); END;');
645 ELSE
646 if (p_defaultRealm is not null) THEN
647 if (p_defaultRealm<>FND_SSO_REGISTRATION.getdefaultrealm) THEN
648 raise_application_error(-20100,'Only default realm can be used');
649 END IF;
650 END IF;
651
652 if (p_defaultRealm is not null and getOption('DEFAULT_REALM',opt) and opt.opt_mode=G_STATIC and opt.val=p_defaultRealm) THEN
653 -- NO CHANGES
654 val := null;
655 ELSE
656 val := p_defaultRealm;
657 if (val is null) THEN
658 val := FND_SSO_REGISTRATION.getdefaultrealm;
659 END IF;
660 if ( getOption('DEFAULT_REALM',opt) and opt.opt_mode=G_STATIC and opt.val=val) THEN
661 -- no changes
662 val:= null;
663 ELSE
664 saveOption('DEFAULT_REALM',G_STATIC,val);
665 -- delete all realm related options
666 deleteOption('REALM');
667 deleteOption('DEFAULT_CREATE_BASE');
668 deleteOption('CREATE_BASE');
669 END IF;
670 END IF;
671 END IF;
672 ELSIF testing_mode=1 THEN
673
674 saveOption('DEFAULT_REALM',G_STATIC,FND_SSO_REGISTRATION.getdefaultrealm);
675
676 END IF;
677
678 if (testing_mode=1) THEN
679 if usePlugin AND ValidateTemplate('FND_OID_PLUG.getRealm_Template',plugin_name||'.getRealm',errmsg) THEN
680 saveOption('REALM',G_DYNAMIC,'BEGIN '||plugin_name||'.getRealm(:1,:2); END;');
681 ELSE
682
683 if getOption('DEFAULT_REALM',opt) THEN
684 saveOption('REALM',opt.opt_mode,opt.val);
685 ELSE
686 val := p_defaultRealm;
687 if (val is null) THEN
688 val := FND_SSO_REGISTRATION.getdefaultrealm;
689 END IF;
690 saveOption('REALM',G_STATIC,val);
691 END IF;
692 END IF;
693 ELSIF (testing_mode=0) THEN
694 saveOption('REALM',G_STATIC,FND_SSO_REGISTRATION.getdefaultrealm);
695
696 END IF;
697
698 if (testing_mode=1) THEN
699 if usePlugin AND ValidateTemplate('FND_OID_PLUG.getDefaultCreateBase_Template',plugin_name||'.getDefaultCreateBase',errmsg) THEN
700 saveOption('DEFAULT_CREATE_BASE',G_DYNAMIC,'BEGIN '||plugin_name||'.getDefaultCreateBase(:1,:2); END;');
701 ELSE
702 val := null;
703
704 if (p_default_user_repository is not null) THEN
705
706 -- validation
707 BEGIN
708 guid := FND_LDAP_UTIL.get_guid_for_dn(ldap,p_default_user_repository);
709 EXCEPTION WHEN OTHERS THEN
710 guid:=NULL;
711 raise_application_error(-20100,'Given createBaseDn does not exists :'||p_default_user_repository );
712
713 END;
714 idx := FND_SSO_REGISTRATION.getuserrealmindex(p_default_user_repository);
715 if (idx is not null and idx>=0 ) THEN
716 saveOption('DEFAULT_CREATE_BASE',G_STATIC,p_default_user_repository);
717 ELSE
718 raise_application_error(-20100,'Given createBaseDn is not part of any realm :'||p_default_user_repository );
719 END IF;
720
721 ELSE
722 if getOption('REALM',opt) THEN
723 if opt.opt_mode=G_DYNAMIC or opt.opt_mode=G_RUNTIME THEN
724 val := '1';
725 saveOption('DEFAULT_CREATE_BASE',G_RUNTIME,'VOID' );
726 END IF;
727
728 END IF;
729 if (val is null ) THEN
730 getDefaultRealm(realm);
731 BEGIN
732 getDefaultCreateBase(realm,val);
733 EXCEPTION WHEN OTHERS THEN
734 val:= FND_SSO_REGISTRATION.get_realm_attribute(realm,'orclCommonUserCreateBase');
735 END;
736 if (val is null ) THEN
737 val := 'cn=Users,'||realm;
738 END IF;
739 BEGIN
740 guid := FND_LDAP_UTIL.get_guid_for_dn(ldap,val);
741 EXCEPTION WHEN OTHERS THEN
742 raise_application_error(-20100,'Default create user base does not exists:'||val);
743 END;
744
745 saveOption('DEFAULT_CREATE_BASE',G_STATIC,val);
746 END IF;
747 END IF;
748 END IF;
749
750 ELSIF testing_mode=0 THEN
751 saveOption('DEFAULT_CREATE_BASE',G_STATIC,
752 FND_SSO_REGISTRATION.get_realm_attribute(FND_SSO_REGISTRATION.getdefaultrealm,'orclCommonUserCreateBase'));
753
754 END IF;
755
756 if testing_mode=1 THEN
757 if usePlugin AND ValidateTemplate('FND_OID_PLUG.getCreateBase_Template',plugin_name||'.getCreateBase',errmsg) THEN
758 saveOption('CREATE_BASE',G_DYNAMIC,'BEGIN '||plugin_name||'.getCreateBase(:1,:2,:3,:4); END;' );
759
760 ELSE
761 val := null;
762 if getOption('DEFAULT_CREATE_BASE',opt) THEN
763 if opt.opt_mode=G_DYNAMIC or opt.opt_mode=G_RUNTIME THEN
764 val := '1';
765 saveOption('CREATE_BASE',G_DYNAMIC,'BEGIN FND_OID_PLUG.getCreateBase(:1,:2,:3,:4); END;' );
766 END IF;
767 END IF;
768 if (val is null) THEN
769 FND_OID_PLUG.getDefaultRealm(realm);
770 FND_OID_PLUG.getDefaultCreateBase(realm,val);
771 saveOption('CREATE_BASE',G_STATIC,val);
772 END IF;
773 END IF;
774 ELSIF testing_mode=0 THEN
775 saveOption('CREATE_BASE',G_STATIC,
776 FND_SSO_REGISTRATION.get_realm_attribute(FND_SSO_REGISTRATION.getdefaultrealm,'orclCommonUserCreateBase'));
777
778 END IF;
779
780
781 if usePlugin AND ValidateTemplate('FND_OID_PLUG.getRDN_Template',plugin_name||'.getRDN',errmsg) THEN
782 saveOption('RDN',G_DYNAMIC,'BEGIN '||plugin_name||'.getRDN(:1,:2,:3,:4,:5); END;');
783
784 ELSE
785 val:= null;
786 if getOption('REALM',opt) THEN
787 if opt.opt_mode=G_DYNAMIC or opt.opt_mode=G_RUNTIME THEN
788 saveOption('RDN',G_RUNTIME,'VOID');
789 val:='VOID';
790 END IF;
791 END IF;
792 if val is null THEN
793 FND_OID_PLUG.getDefaultRealm(realm);
794 val:= FND_SSO_REGISTRATION.get_Realm_Attribute(realm ,'orclCommonNamingAttribute');
795 saveOption('RDN',G_STATIC,val);
796 END IF;
797 END IF;
798 -- FixupLDAPUser
799 if usePlugin AND ValidateTemplate('FND_OID_PLUG.FixupLDAPUser_Template',plugin_name||'.FixupLDAPUser',errmsg) THEN
800 saveOption('FIXUP',G_DYNAMIC,'BEGIN '||plugin_name||'.FixupLDAPUser(:1,:2,:3); END;');
801
802 ELSE
803 saveOption('FIXUP',G_STATIC,'NONE');
804 END IF;
805 FND_LDAP_UTIl.c_unbind(ldap,flag);
806 l_session_flag := false;
807 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
808 then
809 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag : = false ' );
810 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'LDAP SESSION CLOSED NORMALLY : ' );
811 end if;
812
813 EXCEPTION WHEN OTHERS THEN
814 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
815 then
816 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
817 end if;
818 /* Fix for 8271359*/
819 if l_session_flag = true then
820 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
821 then
822 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closing in EXCEPTION BLOCK - START ' );
823 end if;
824 fnd_ldap_util.c_unbind(ldap,flag);
825
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, 'LDAP SESSION closed in EXCEPTION BLOCK - END ');
829 end if;
830 end if;
831
832 raise;
833
834 -- COMPLETE DEFAULTS
835 -- CLEANUP
836
837 END SetPlugIn;
838
839 procedure setPlugin_old(
840 default_realm in varchar2 default null,
841 default_user_repository in varchar2 default null,
842 plugin_name in varchar2 default null) AS
843 ldap dbms_ldap.session;
844 res pls_integer;
845 flag pls_integer;
846 l_module_source varchar2(1000) ;
847 isDynamic boolean:= false;
848 errmsg varchar2(4000);
849 l_session_flag boolean := false;
850 BEGIN
851 l_module_source := G_MODULE_SOURCE || 'setPlugin: ';
852
853 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
854 then
855 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin' );
856 end if;
857 /*
858 if (plugin_name is not null) then
859 -- if ( validateTemplate('FND_OID_PLUG.getRDN',plugin_name||'.getRDN',msg) )THEN
860 -- fnd_preference.put(G_PREF_USER,G_PREF_MODULE,L_RDN,'begin '||plugin_name||'.getRDN(:1,:2,:3:,:4,:5); end;');
861 -- isDynamic := true;
862 -- ELSE
863 -- if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
864 -- then
865 -- fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'getRDN setup failed: '|| msg );
866 -- msg:=null;
867 -- end if;
868 -- END IF;
869
870
871 if ( validateTemplate('FND_OID_PLUG.getParentDN',plugin_name||'.getParentDN',errmsg) )THEN
872 null;
873 END IF;
874 if ( validateTemplate('FND_OID_PLUG.getRealm',plugin_name||'.getRealm',errmsg) )THEN
875 null;
876 END IF;
877 if ( validateTemplate('FND_OID_PLUG.fixup',plugin_name||'.fixup',errmsg) )THEN
878 null;
879 END IF;
880
881
882 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,L_TYPE,G_DYNAMIC_DESC);
883 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,L_REALM,'begin :1 := '||plugin_name||'.Realm(:2); end;');
884 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,L_REPOSITORY,'begin :1 := '||plugin_name||'.UserRepository(:2); end;');
885 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
886 then
887 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End' );
888 end if;
889 return;
890 else
891 */
892 ldap := fnd_ldap_util.c_get_oid_session(flag);
893 l_session_flag := true; /* fix for bug 8271359 */
894
895 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
896 then
897 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag = true ' );
898 end if;
899
900 if (default_realm is not null) then
901 g_realm := default_realm;
902 else
903 g_realm := FND_LDAP_UTIl.getLDAPAttribute(ldap,'cn=Common,cn=Products,cn=OracleContext','orclDefaultSubscriber');
904 end if;
905
906 if (default_user_repository is not null) then
907 g_user_repo := default_user_repository;
908 else
909 g_user_repo := FND_LDAP_UTIl.getLDAPAttribute(ldap,'cn=Common,cn=Products,cn=OracleContext,'||g_realm,'orclcommondefaultusercreatebase');
910 end if;
911 if (g_user_repo is null) then
912 g_user_repo := 'cn=Users,'||g_realm;
913 end if;
914
915
916
917 g_user_search := FND_LDAP_UTIL.getLDAPAttribute(ldap,'cn=Common,cn=Products,cn=OracleContext,'||g_realm,'orclcommonusersearchbase');
918
919 if (g_user_search is null) then
920 g_user_search := 'cn=Users,'||g_realm;
921 end if;
922
923 g_cnatt := FND_LDAP_UTIL.getLDAPAttribute(ldap,'cn=Common,cn=Products,cn=OracleContext,'||g_realm,'orclcommonnamingattribute');
924 if (g_cnatt is null) then
925 g_cnatt := 'cn';
926 end if;
927
928 g_nicknameatt := FND_LDAP_UTIL.getLDAPAttribute(ldap,'cn=Common,cn=Products,cn=OracleContext,'||g_realm,'orclcommonnicknameattribute');
929 if (g_nicknameatt is null) then
930 g_nicknameatt := 'uid';
931 end if;
932
933
934 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,L_TYPE,G_STATIC_DESC);
935 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,L_REALM,g_realm);
936 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,L_CN_ATT,g_cnatt);
937 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,L_NICK_ATT,g_nicknameatt);
938 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,L_SEARCH,g_user_search);
939 fnd_preference.put(G_PREF_USER,G_PREF_MODULE,L_REPOSITORY,g_user_repo);
940
941 fnd_ldap_util.c_unbind(ldap,flag);
942 l_session_flag := false;
943 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
944 then
945 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag : = false ' );
946 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'LDAP SESSION CLOSED NORMALLY : ' );
947 end if;
948
949 -- end if;
950
951 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
952 then
953 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End' );
954 end if;
955 EXCEPTION WHEN OTHERS THEN
956 /* Fix for 8271359*/
957 if l_session_flag = true then
958 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
959 then
960 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closing in EXCEPTION BLOCK - START ' );
961 end if;
962 fnd_ldap_util.c_unbind(ldap,flag);
963
964 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
965 then
966 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closed in EXCEPTION BLOCK - END ');
967 end if;
968 end if;
969
970 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
971 then
972 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
973 end if;
974 raise;
975
976 END setPlugin_old;
977
978 PROCEDURE upgrade_preferences is
979
980 l_realm varchar2(4000);
981 l_repo varchar2(4000);
982 ldap dbms_ldap.session;
983 flag pls_integer;
984 l_module_source varchar2(1000);
985 l_session_flag boolean := false;
986 BEGIN
987 l_module_source := G_MODULE_SOURCE || 'upgrade_preferences: ';
988
989 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
990 then
991 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
992 end if;
993 ldap := fnd_ldap_util.c_get_oid_session(flag);
994 l_session_flag := true; /* fix for bug 8271359 */
995
996 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
997 then
998 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag = true ' );
999 end if;
1000 l_realm := FND_LDAP_UTIL.getLDAPAttribute(ldap,'cn=Common,cn=Products,cn=OracleContext','orclDefaultSubscriber');
1001 l_repo := FND_LDAP_UTIL.getLDAPAttribute(ldap,'cn=Common,cn=Products,cn=OracleContext,'||l_realm,'orclcommondefaultusercreatebase');
1002 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1003 then
1004 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'AUTO UPGRADE');
1005 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, '=============');
1006 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'realm:'||l_realm);
1007 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'user creation base:'||l_repo);
1008 end if;
1009
1010 setPlugin_old( default_realm => l_realm ,default_user_repository=>l_repo,plugin_name =>null);
1011 fnd_ldap_util.c_unbind(ldap,flag);
1012 l_session_flag := false;
1013 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1014 then
1015 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag : = false ' );
1016 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'LDAP SESSION CLOSED NORMALLY : ' );
1017 end if;
1018
1019 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1020 then
1021 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'end');
1022 end if;
1023
1024
1025 EXCEPTION WHEN OTHERS THEN
1026 /* Fix for 8271359*/
1027 if l_session_flag = true then
1028 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1029 then
1030 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closing in EXCEPTION BLOCK - START ' );
1031 end if;
1032 fnd_ldap_util.c_unbind(ldap,flag);
1033
1034 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1035 then
1036 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closed in EXCEPTION BLOCK - END ');
1037 end if;
1038 end if;
1039
1040 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1041 then
1042 fnd_log.string(fnd_log.LEVEL_UNEXPECTED , l_module_source, 'Error '||sqlerrm);
1043 end if;
1044
1045 raise;
1046 END upgrade_preferences;
1047
1048
1049 function pluginType_old return integer AS
1050 val varchar2(20);
1051 l_module_source varchar2(1000) ;
1052 BEGIN
1053 l_module_source := G_MODULE_SOURCE || 'pluginType: ';
1054
1055 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1056 then
1057 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1058 end if;
1059 return null;
1060 END pluginType_old;
1061
1062
1063 function sql_execute(stmt in varchar2, param in varchar ) return varchar2 as
1064 c pls_integer;
1065 res pls_integer;
1066 result varchar2(4000);
1067 l_module_source varchar2(1000) ;
1068 BEGIN
1069 l_module_source := G_MODULE_SOURCE || 'sql_execute: ';
1070
1071 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1072 then
1073 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1074 end if;
1075 c := dbms_sql.open_cursor;
1076 dbms_sql.parse(c, stmt, dbms_sql.NATIVE);
1077 dbms_sql.bind_variable(c, ':2', param);
1078 dbms_sql.bind_variable(c,':1',result,4000);
1079 res := dbms_sql.execute(c);
1080 dbms_sql.variable_value(c,':1',result);
1081 dbms_sql.close_cursor(c);
1082
1083 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1084 then
1085 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END');
1086 end if;
1087 return result;
1088 EXCEPTION WHEN OTHERS THEN
1089 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1090 then
1091 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
1092 end if;
1093 raise;
1094
1095 END sql_execute;
1096
1097
1098
1099 FUNCTION getNewUserRealm(username in varchar2) return varchar2
1100 IS
1101 opt option_type_rec;
1102 l_module_source varchar2(4000):=G_MODULE_SOURCE || 'getNewUserRalm: ';
1103 BEGIN
1104 IF NOT getOption('REALM',opt )
1105 THEN
1106 return FND_SSO_REGISTRATION.getDefaultRealm();
1107 END IF;
1108 --AQUI
1109 CASE opt.opt_mode
1110 WHEN G_STATIC THEN BEGIN
1111 return opt.val;
1112 END;
1113 WHEN G_RUNTIME THEN BEGIN
1114 IF g_cached_realm is null THEN
1115 g_cached_realm:= FND_SSO_REGISTRATION.getDefaultRealm();
1116 END IF;
1117 return g_cached_realm;
1118 END;
1119 WHEN G_DYNAMIC THEN BEGIN
1120 return sql_execute(opt.val,username);
1121 END ;
1122 ELSE
1123 raise configuration_error;
1124 END CASE;
1125 EXCEPTION WHEN OTHERS THEN
1126 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1127 then
1128 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
1129 end if;
1130 raise;
1131
1132
1133 END getNewUserRealm;
1134 /*
1135 * Do not use it. Too costly.
1136 * Except when you rally want the realm for an EXISTING user.
1137 * Preffer getRealmDNNewUser for new users
1138 *
1139 * CALCULATION steps
1140 * 1- If the user exists in FND_USER and has a GUID return get_realm_from_guid
1141 * If guid is incorrec raise an error
1142 * 2- IF user does not exist in FND_USER user FND_LDAP_USER.Search(username,ouy guid)
1143 * This function can be costly, but worst. It is possible on multiples realms
1144 * to have NON-UNIQUE usernames (non-wide uniqeu attributes, or differet nna ).
1145 * In that case ANY of those will be returned.
1146 *
1147 */
1148
1149 function getRealmDN(username varchar2) return varchar2 AS
1150 result varchar2(4000);
1151 l_module_source varchar2(1000) ;
1152 l_guid FND_USER.user_guid%type;
1153 user_rec FND_LDAP_USER.ldap_user_type;
1154 BEGIN
1155 validateVersion;
1156
1157 l_module_source := G_MODULE_SOURCE || 'getRealmDN: ';
1158 result := null;
1159
1160 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1161 then
1162 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin '||username);
1163 end if;
1164
1165 BEGIN
1166 select user_guid into l_guid from FND_USER
1167 where user_name=username;
1168 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1169 then
1170 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Found on FND_USER guid: '||NVL(l_guid,'**NULL*'));
1171 end if;
1172
1173 EXCEPTION WHEN NO_DATA_FOUND THEN
1174 l_guid:=null;
1175 END;
1176
1177 IF (l_guid IS NOT NULL )THEN
1178 result := FND_SSO_REGISTRATION.getGuidRealm(l_guid);
1179
1180 if (result is null ) THEN
1181
1182 if (fnd_log.LEVEL_EXCEPTION>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1183 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'Invalid GUID guid: '||l_guid);
1184 END IF;
1185
1186 raise NO_DATA_FOUND;
1187 END IF;
1188
1189 END IF;
1190 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1191 then
1192 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Looking at LDAP for the user '||username);
1193 end if;
1194 IF FND_LDAP_USER.SearchUser(username,user_rec) THEN
1195 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1196 then
1197 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'FOUND!! dn: '||user_rec.dn);
1198 end if;
1199 result := user_rec.realmDN;
1200 ELSE
1201 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1202 then
1203 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'NOT FOUND, will retrun real for new user then');
1204 end if;
1205 result := getNewUserRealm(username);
1206 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1207 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'RealmDN:'||result);
1208 end if;
1209 END IF;
1210 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1211 then
1212 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->'||result);
1213 end if;
1214 return result;
1215 EXCEPTION WHEN OTHERS THEN
1216 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1217 then
1218 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'ERROR:'||sqlerrm);
1219 end if;
1220 raise;
1221 END getRealmDN;
1222
1223 function getUserRepository(p_ldap_user IN OUT nocopy fnd_ldap_user.ldap_user_type ) return varchar2 AS
1224 result varchar2(4000);
1225 l_module_source varchar2(1000) ;
1226 opt option_type_rec;
1227 baseList dbms_ldap.string_collection;
1228 BEGIN
1229 l_module_source := G_MODULE_SOURCE || 'getUserRepository: ';
1230 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1231 then
1232 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin ');
1233 end if;
1234
1235 IF (p_ldap_user.parent_DN is not null) THEN
1236 result:=p_ldap_user.parent_DN ;
1237 ELSIF getOption('CREATE_BASE',opt) THEN
1238 CASE opt.opt_MODE
1239 WHEN G_STATIC THEN result:= opt.val;
1240 WHEN G_RUNTIME THEN
1241 BEGIN
1242 result := fnd_sso_registration.get_realm_attribute(p_ldap_user.realmDN ,'orclCommonUserCreateBase',0);
1243
1244 END ;
1245 WHEN G_DYNAMIC THEN
1246 BEGIN
1247
1248 EXECUTE immediate opt.val USING
1249 IN p_ldap_user.user_id,
1250 IN p_ldap_user.user_name,
1251 IN p_ldap_user.realmDN,
1252 OUT result;
1253 END;
1254 ELSE raise FND_OID_PLUG.CONFIGURATION_ERROR;
1255 END CASE;
1256
1257 ELSE
1258
1259 if (p_ldap_user.realmDN is null) THEN
1260 -- must set the realm before calling
1261 return null;
1262 END IF;
1263 result := fnd_sso_registration.get_realm_attribute(p_ldap_user.realmDN ,'orclCommonUserCreateBase',0);
1264 END IF;
1265 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1266 then
1267 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END-> '||result);
1268 end if;
1269
1270 return result;
1271 EXCEPTION WHEN OTHERS THEN
1272 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1273 then
1274 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'ERROR:'||sqlerrm);
1275 end if;
1276 raise;
1277
1278 END getUserRepository;
1279
1280 function getNickNameattr(username in varchar2) return varchar2 AS
1281
1282 result varchar2(4000);
1283 l_module_source varchar2(1000) ;
1284 BEGIN
1285 validateVersion;
1286 l_module_source := G_MODULE_SOURCE || 'getNickNameattr: ';
1287 return FND_LDAP_USER.getnicknameattr(username);
1288 END getNickNameattr;
1289
1290
1291 PROCEDURE getRDN
1292 (
1293 username IN VARCHAR2,
1294 userid IN pls_integer ,
1295 rdn_att IN OUT nocopy VARCHAR2 ,
1296 rdn_val IN OUT nocopy VARCHAR2 ,
1297 replaceFlag IN OUT nocopy BOOLEAN )
1298 AS
1299 result VARCHAR2(4000);
1300 l_module_source VARCHAR2(1000) ;
1301 q VARCHAR2(1000);
1302 aux pls_integer;
1303 opt option_type_rec;
1304 idx pls_integer;
1305
1306 BEGIN
1307 l_module_source := G_MODULE_SOURCE || 'getRDN: ';
1308 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1309 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1310 END IF;
1311 IF (NOT getOption('RDN',opt) )THEN
1312 opt.opt_mode:=G_STATIC;
1313 opt.val := FND_SSO_REGISTRATION.get_Realm_Attribute( FND_SSO_REGISTRATION.getdefaultrealm,'orclCommonNamingAttribute');
1314 saveOption('RDN',opt.opt_mode,opt.val);
1315 END IF;
1316 CASE opt.opt_mode
1317 WHEN G_STATIC THEN
1318 BEGIN
1319 rdn_att := opt.val;
1320 rdn_val := username;
1321 replaceFlag := true;
1322 END;
1323 WHEN G_RUNTIME THEN
1324 BEGIN
1325 idx := FND_SSO_REGISTRATION.getUserRealmIndex(username);
1326 rdn_att := FND_SSO_REGISTRATION.get_Realm_Attribute(idx,'orclCommonNamingAttribute');
1327 rdn_val := username;
1328 replaceFlag:= true;
1329 END ;
1330 WHEN G_DYNAMIC THEN
1331 BEGIN
1332 q := opt.val;
1333 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1334 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Testing dynamic');
1335 END IF;
1336 EXECUTE immediate q USING IN username,
1337 IN userid,
1338 IN OUT rdn_att,
1339 IN OUT rdn_val,
1340 OUT aux;
1341 replaceFlag := (AUX=1);
1342 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1343 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'RND:'||rdn_att||'='||rdn_val);
1344 END IF;
1345 END;
1346 ELSE BEGIN
1347
1348 raise FND_OID_PLUG.CONFIGURATION_ERROR;
1349 END;
1350 END CASE;
1351 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1352 fnd_log.
1353 string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->'||result);
1354 END IF;
1355 RETURN ;
1356 EXCEPTION
1357 WHEN OTHERS THEN
1358 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1359 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
1360 END IF;
1361 raise;
1362 END getRDN;
1363
1364 /*
1365 function getSearchBase(username in varchar2) return varchar2 AS
1366 res varchar2(1000);
1367 ldap dbms_ldap.session;
1368 dummy pls_integer;
1369 result varchar2(4000);
1370 l_module_source varchar2(1000) ;
1371 BEGIN
1372 l_module_source := G_MODULE_SOURCE || 'getSearchBase: ';
1373
1374 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1375 then
1376 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1377 end if;
1378
1379 if (plugin_type is null) then plugin_type:=pluginType; end if;
1380 if (plugin_type=G_STATIC) then
1381 result:= g_user_search;
1382 else
1383 ldap := fnd_ldap_util.c_get_oid_session(dummy);
1384 result:= FND_LDAP_UTIL.getLDAPAttribute(ldap,'cn=Common,cn=Products,cn=OracleContext,'||getRealmDN(username),'orclcommonusersearchbase');
1385 fnd_ldap_util.c_unbind(ldap,dummy);
1386 end if;
1387
1388 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1389 then
1390 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->'||result);
1391 end if;
1392
1393
1394 return result;
1395 EXCEPTION WHEN OTHERS THEN
1396 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1397 then
1398 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
1399 end if;
1400 raise;
1401
1402 END getSearchBase;
1403 */
1404 /*
1405 function getSearchFilter(username in varchar2) return varchar2 AS
1406 result varchar2(4000);
1407 l_module_source varchar2(1000) ;
1408 BEGIN
1409 l_module_source := G_MODULE_SOURCE || 'getSearchFilter: ';
1410
1411 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1412 then
1413 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1414 end if;
1415
1416 if (plugin_type is null) then plugin_type:=pluginType; end if;
1417 if (plugin_type=G_STATIC) then
1418 result:= g_nicknameatt||'='||username;
1419 else
1420 result:=g_nicknameatt||'='||username;
1421 end if;
1422
1423 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1424 then
1425 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->'||result);
1426 end if;
1427
1428
1429 return result;
1430 EXCEPTION WHEN OTHERS THEN
1431 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1432 then
1433 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
1434 end if;
1435 raise;
1436
1437 END getSearchFilter;
1438 */
1439
1440 function getNickNameAttributeFromRealm(realmDn in varchar2) return varchar2
1441 is
1442
1443 ldap dbms_ldap.session;
1444 dummy pls_integer;
1445 result varchar2(4000);
1446 l_module_source varchar2(1000) ;
1447 l_session_flag boolean := false;
1448 BEGIN
1449 l_module_source := G_MODULE_SOURCE || 'getNickNameAttributeFromRealm: ';
1450
1451 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1452 then
1453 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1454 end if;
1455
1456 ldap := fnd_ldap_util.c_get_oid_session(dummy);
1457 l_session_flag := true; /* fix for bug 8271359 */
1458
1459 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1460 then
1461 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag = true ' );
1462 end if;
1463
1464 result:= FND_LDAP_UTIL.getLDAPAttribute(ldap,'cn=Common,cn=Products,cn=OracleContext,'||realmDn,'orclcommonnicknameattribute');
1465 fnd_ldap_util.c_unbind(ldap,dummy);
1466
1467 l_session_flag := false;
1468 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1469 then
1470 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag : = false ' );
1471 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'LDAP SESSION CLOSED NORMALLY : ' );
1472 end if;
1473
1474 if (result is null) then result:='uid'; end if;
1475
1476 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1477 then
1478 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->'||result);
1479 end if;
1480
1481
1482 return result;
1483 EXCEPTION WHEN OTHERS THEN
1484 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1485 then
1486 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1487 end if;
1488 /* Fix for 8271359*/
1489 if l_session_flag = true then
1490 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1491 then
1492 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closing in EXCEPTION BLOCK - START ' );
1493 end if;
1494 fnd_ldap_util.c_unbind(ldap,dummy);
1495
1496 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1497 then
1498 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closed in EXCEPTION BLOCK - END ');
1499 end if;
1500 end if;
1501
1502 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1503 then
1504 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
1505 end if;
1506 raise;
1507
1508 END getNickNameAttributeFromRealm;
1509
1510 function getUsernameFromEntry(attr_list ldap_attr_list) return varchar2
1511 is
1512 j pls_integer;
1513 result varchar2(4000);
1514 l_module_source varchar2(1000) ;
1515 BEGIN
1516 l_module_source := G_MODULE_SOURCE || 'getUsernameFromEntry: ';
1517
1518 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1519 then
1520 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1521 end if;
1522
1523 if (attr_list is not null AND attr_list.count > 0)
1524 then
1525 for j in attr_list.first .. attr_list.last
1526 loop
1527
1528 if(upper(attr_list(j).attr_name) = 'ORCLGUID') then
1529 result:= get_username_from_guid(attr_list(j).attr_value);
1530 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1531 then
1532 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->'||result);
1533 end if;
1534
1535 return result;
1536 end if;
1537 end loop;
1538 end if;
1539 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1540 then
1541 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END -> raise NO_DATA_FOUND');
1542 end if;
1543 raise no_data_found;
1544 EXCEPTION WHEN OTHERS THEN
1545 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1546 then
1547 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
1548 end if;
1549 raise;
1550
1551 end getUsernameFromEntry;
1552
1553 function get_entry_dn(ldap in out nocopy dbms_ldap.session, p_guid IN raw ) return varchar2
1554 is
1555 result pls_integer;
1556 entry_dn varchar(4000);
1557 l_message dbms_ldap.message ;
1558 l_attrs dbms_ldap.string_collection;
1559 l_entry dbms_ldap.message;
1560
1561 l_module_source varchar2(1000) ;
1562 BEGIN
1563 l_module_source := G_MODULE_SOURCE || 'get_entry_dn: ';
1564
1565 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1566 then
1567 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1568 end if;
1569
1570 l_attrs(0) := 'dn'; -- NO_ATTRS
1571 result := dbms_ldap.search_s(ld => ldap
1572 , base => ''
1573 , scope => dbms_ldap.SCOPE_SUBTREE
1574 , filter => 'orclguid='||p_guid
1575 , attrs => l_attrs
1576 , attronly => 0
1577 , res => l_message);
1578 l_entry := dbms_ldap.first_entry(ldap, l_message);
1579 entry_dn := dbms_ldap.get_dn(ldap,l_entry);
1580 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1581 then
1582 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->'||entry_dn);
1583 end if;
1584
1585 return entry_dn;
1586 EXCEPTION WHEN OTHERS THEN
1587 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1588 then
1589 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
1590 end if;
1591 raise;
1592
1593 end get_entry_dn;
1594
1595 function get_realm_from_user_dn(ldap in out nocopy dbms_ldap.session, user_dn in varchar2 )
1596 return varchar2
1597
1598 is
1599 cn_list dbms_ldap.STRING_COLLECTION;
1600 len pls_integer;
1601 i pls_integer;
1602 j pls_integer;
1603 dn varchar2(4000);
1604 g varchar2(1000);
1605 result varchar2(4000);
1606 l_module_source varchar2(1000) ;
1607 l_realm_idx pls_integer;
1608 dev_version number := 2.0 ;
1609 BEGIN
1610 validateVersion;
1611
1612 l_module_source := G_MODULE_SOURCE || 'get_realm_from_user_dn: ';
1613
1614 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1615 then
1616 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin '||dev_version);
1617 end if;
1618
1619 if (dev_version>=1.0 and dev_version<2.0) THEN
1620 cn_list := dbms_ldap.explode_dn(user_dn,0);
1621 len := cn_list.count;
1622 i:= 0;
1623 while (i<len) loop
1624
1625 -- construct a new dn from 0..1
1626 dn := cn_list(i);
1627 j:=i+1;
1628 while(j<len) loop
1629 dn := dn||','||cn_list(j);
1630 j:=j+1;
1631 end loop;
1632 BEGIN
1633 g := FND_LDAP_UTIL.getLDAPAttribute(ldap,dn,'orclguid','objectclass=orclSubscriber');
1634 if (dev_version>=1.1) THEN -- we check something more
1635 g := FND_LDAP_UTIL.getLDAPAttribute(ldap,'cn=Common,cn=Products,cn=OracleContext,'||dn,'orclcommonnicknameattribute','objectclass=*');
1636 END IF;
1637 EXCEPTION
1638 WHEN DBMS_LDAP.general_error THEN
1639 g:= null;
1640 WHEN OTHERS THEN
1641 g:=null;
1642 END;
1643 if (g is not null) then
1644 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1645 then
1646 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->'||dn);
1647 end if;
1648 return dn;
1649 end if;
1650 i:=i+1;
1651 end loop;
1652
1653 ELSIF dev_version=2.0 THEN
1654 l_realm_idx := fnd_sso_registration.getUserRealmIndex(dn);
1655 if (l_realm_idx is not null) then
1656 dn := fnd_sso_registration.find_realm(l_realm_idx);
1657 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1658 then
1659 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END->'||dn);
1660 end if;
1661 return dn;
1662 end if;
1663 else
1664 raise NO_DATA_FOUND;
1665 END IF;
1666 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1667 then
1668 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END NULL!');
1669 end if;
1670 return null;
1671 EXCEPTION WHEN OTHERS THEN
1672 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1673 then
1674 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
1675 end if;
1676 raise;
1677
1678 end get_realm_from_user_dn;
1679
1680
1681 function get_username_from_guid(p_user_guid IN fnd_user.user_guid%type ) return varchar2
1682 is
1683 ldap dbms_ldap.session;
1684 dummy pls_integer;
1685 user_dn varchar2(4000);
1686 realm_dn varchar2(4000);
1687 user_name varchar2(1000);
1688 nickNameAttr varchar2(50);
1689 l_module_source varchar2(1000) ;
1690 realm_idx pls_integer;
1691 l_session_flag boolean := false;
1692 BEGIN
1693 l_module_source := G_MODULE_SOURCE || 'get_username_from_guid: ';
1694
1695 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1696 then
1697 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1698 end if;
1699 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1700 then
1701 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'guid='||p_user_guid);
1702 end if;
1703
1704 ldap := fnd_ldap_util.c_get_oid_session(dummy);
1705 l_session_flag := true; /* fix for bug 8271359 */
1706
1707 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1708 then
1709 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag = true ' );
1710 end if;
1711
1712 user_dn := get_entry_dn(ldap,p_user_guid);
1713 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1714 then
1715 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'guid=>DN:'||user_dn);
1716 end if;
1717 if user_dn is null then
1718
1719 if (fnd_log.LEVEL_EXCEPTION>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1720 then
1721 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'DN not found , invalid GUID ');
1722 end if;
1723 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
1724 raise NO_DATA_FOUND ;
1725 end if;
1726
1727 realm_dn := get_realm_from_user_dn(ldap,user_dn);
1728 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1729 then
1730 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'DN=>RealmDN:'||realm_dn);
1731 end if;
1732 if realm_dn is null then
1733 if (fnd_log.LEVEL_EXCEPTION>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1734 then
1735 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'Cannot locate Realm for DN:'||user_dn);
1736 end if;
1737 fnd_message.set_name ('FND', 'FND-9903'); -- cannot find realm for user
1738 raise NO_DATA_FOUND ;
1739 end if;
1740
1741
1742 realm_idx := FND_SSO_REGISTRATION.find_realm_index(realm_dn);
1743 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1744 then
1745 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'RealmDN=>idx:'||realm_idx);
1746 end if;
1747
1748 if realm_idx < 0 then
1749 if (fnd_log.LEVEL_EXCEPTION>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1750 then
1751 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'Realm not loaded: Realm:'||realm_dn);
1752 end if;
1753 fnd_message.set_name ('FND', 'FND-9903'); -- cannot find realm idx
1754 raise NO_DATA_FOUND ;
1755 end if;
1756
1757 --nickNameAttr:=FND_LDAP_UTIL.getLDAPAttribute(ldap,'cn=Common,cn=Products,cn=OracleContext,'||realm_dn,'orclCommonNicknameAttribute');
1758 nickNameAttr := FND_SSO_REGISTRATION.get_realm_attribute(realm_idx,'orclCommonNicknameAttribute');
1759 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1760 then
1761 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Realm=>NickNameAttrubute:'||nickNameAttr);
1762 end if;
1763
1764 if nickNameAttr is null then
1765 if (fnd_log.LEVEL_EXCEPTION>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1766 then
1767 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'Cannot read Realm nickname attribute, Realm:'||realm_dn);
1768 end if;
1769
1770 fnd_message.set_name ('FND', 'FND-9903'); -- cannot find nickname attribute specification
1771 raise NO_DATA_FOUND ;
1772 end if;
1773
1774 user_name := FND_LDAP_UTIL.getLDAPAttribute(ldap,user_dn,nickNameAttr);
1775 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1776 then
1777 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'DN+NickNameAttrubute=>user_name:'||user_name);
1778 end if;
1779
1780 if user_name is null then
1781 if (fnd_log.LEVEL_EXCEPTION>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1782 then
1783 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'Record does not have '||nickNameAttr||' attribute DN:'||user_dn);
1784 end if;
1785 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND'); --user has no nickname
1786 raise NO_DATA_FOUND ;
1787 end if;
1788 fnd_ldap_util.c_unbind(ldap,dummy);
1789 l_session_flag := false;
1790 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1791 then
1792 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag : = false ' );
1793 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'LDAP SESSION CLOSED NORMALLY : ' );
1794 end if;
1795
1796 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1797 then
1798 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->'||user_name);
1799 end if;
1800
1801 return user_name;
1802 EXCEPTION when NO_DATA_FOUND THEN
1803 fnd_ldap_util.c_unbind(ldap,dummy);
1804 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1805 then
1806 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, ' Not found guid='||p_user_guid);
1807 end if;
1808 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1809 then
1810 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->EXCEPTION');
1811 end if;
1812
1813 raise;
1814 end get_username_from_guid;
1815
1816
1817 function get_default_realm return varchar2
1818 is
1819 res varchar2(1000);
1820 ldap dbms_ldap.session;
1821 dummy pls_integer;
1822 result varchar2(4000);
1823 l_module_source varchar2(1000) ;
1824 BEGIN
1825 validateVersion;
1826 l_module_source := G_MODULE_SOURCE || 'get_default_realm: ';
1827
1828 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1829 then
1830 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1831 end if;
1832 result := FND_SSO_REGISTRATION.getDefaultRealm;
1833 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1834 then
1835 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->'||result);
1836 end if;
1837 return result;
1838 EXCEPTION WHEN OTHERS THEN
1839 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1840 then
1841 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
1842 end if;
1843 raise;
1844
1845 END get_default_realm;
1846 ---
1847 ----
1848 ----- INTERNAL SSO
1849 ------
1850 FUNCTION get_realm_dn
1851 ( p_user_guid IN raw DEFAULT NULL,
1852 p_user_name IN VARCHAR2 DEFAULT NULL)
1853 RETURN VARCHAR2
1854 IS
1855 ldap dbms_ldap.session;
1856 flag pls_integer;
1857 result VARCHAR2(4000);
1858 l_module_source VARCHAR2(1000) ;
1859 l_dn VARCHAR2(4000);
1860 l_session_flag boolean := false;
1861 BEGIN
1862 validateVersion;
1863 l_module_source := G_MODULE_SOURCE || 'get_realm_dn: ';
1864
1865 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1866 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1867 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1868 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'guid:'||p_user_guid);
1869 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'username:'||p_user_name);
1870 END IF;
1871 END IF;
1872
1873 IF (p_user_guid IS NULL AND p_user_name IS NULL) THEN
1874 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1875 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'No guid or username=> returning the default realm');
1876 END IF;
1877 result:= FND_OID_PLUG.get_default_realm;
1878 ELSE
1879 ldap := fnd_ldap_util.c_get_oid_session(flag);
1880 l_session_flag := true; /* fix for bug 8271359 */
1881
1882 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1883 then
1884 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag = true ' );
1885 end if;
1886
1887 result := NULL;
1888
1889 IF (p_user_guid IS NOT NULL) THEN
1890 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1891 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Attempt to retreive the actual realm for that guid');
1892 END IF;
1893 l_dn := get_entry_dn(ldap,p_user_guid);
1894 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1895 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'DN:'||l_dn);
1896 END IF;
1897 result :=get_realm_from_user_dn(ldap,l_dn);
1898 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1899 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'GUID: realm:'||result);
1900 END IF;
1901 END IF;
1902 IF (result IS NULL AND p_user_name IS NOT NULL) THEN
1903 result := getRealmDN(p_user_name);
1904 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1905 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Using username, realm:'||result);
1906 END IF;
1907 END IF;
1908 IF (result IS NULL) THEN
1909 result := get_default_realm;
1910 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1911 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'fallback to default realm:'||result);
1912 END IF;
1913 END IF;
1914 fnd_ldap_util.c_unbind(ldap,flag);
1915 l_session_flag := false;
1916 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1917 then
1918 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag : = false ' );
1919 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'LDAP SESSION CLOSED NORMALLY : ' );
1920 end if;
1921
1922 END IF;
1923 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1924 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END ->'||result);
1925 END IF;
1926 RETURN result;
1927 EXCEPTION WHEN OTHERS THEN
1928 if l_session_flag = true then
1929 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1930 then
1931 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closing in EXCEPTION BLOCK - START ' );
1932 end if;
1933 fnd_ldap_util.c_unbind(ldap,flag);
1934
1935 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1936 then
1937 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closed in EXCEPTION BLOCK - END ');
1938 end if;
1939 end if;
1940 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1941 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'END ->'||sqlerrm);
1942 END IF;
1943 raise;
1944 END get_realm_dn;
1945
1946 function count_attributes(ldap in out nocopy dbms_ldap.session, dn in varchar2, attName in varchar2)
1947 return integer
1948 is
1949 l_result pls_integer;
1950 l_attrs dbms_ldap.string_collection;
1951 l_message dbms_ldap.message := NULL;
1952 l_entry dbms_ldap.message := NULL;
1953 l_module_source varchar2(4000);
1954 begin
1955 l_module_source := G_MODULE_SOURCE || 'count_attributes: ';
1956
1957 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1958 then
1959 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1960 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1961 then
1962 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'dn:'||dn);
1963 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'attribute:'||attName);
1964 end if;
1965 end if;
1966 l_attrs(0):= attName;
1967 l_result := dbms_ldap.search_s(ld => ldap
1968 , base => dn
1969 , scope => dbms_ldap.SCOPE_BASE
1970 , filter => 'objectclass=*'
1971 , attrs => l_attrs
1972 , attronly => 0
1973 , res => l_message);
1974 l_entry := dbms_ldap.first_entry(ldap, l_message);
1975 if (l_entry is null ) then
1976 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1977 then
1978 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End(not found)');
1979 end if;
1980 return 0;
1981 end if;
1982 l_attrs := dbms_ldap.get_values(ldap, l_entry, attName);
1983 l_result := l_attrs.count;
1984 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1985 then
1986 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'result:'||l_result);
1987 end if;
1988 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1989 then
1990 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END');
1991 end if;
1992 return l_result;
1993
1994 EXCEPTION when others then
1995 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1996 then
1997 fnd_log.string(fnd_log.LEVEL_UNEXPECTED , l_module_source, 'Error '||sqlerrm);
1998 end if;
1999 raise;
2000 end count_attributes;
2001
2002
2003
2004 function getRealmCommonNameAttribute(realm in varchar2) return varchar2
2005 IS
2006 idx pls_integer;
2007 ret varchar2(200);
2008 BEGIN
2009 idx := FND_SSO_REGISTRATION.find_realm_index(realm);
2010 ret := FND_SSO_REGISTRATION.get_realm_attribute(idx,'orclcommonnamingattribute');
2011 if (ret is null) then
2012 ret := 'uid';
2013 end if;
2014 return ret;
2015 END getRealmCommonNameAttribute;
2016
2017 function getDN(p_ldap_user IN OUT nocopy fnd_ldap_user.ldap_user_type) return varchar2
2018 is
2019 l_module_source varchar2(1000) ;
2020 name varchar2(200);
2021 val VARCHAR2(2000);
2022 replaceFlag boolean;
2023 i pls_integer;
2024 BEGIN
2025 l_module_source := G_MODULE_SOURCE || 'getDN: ';
2026
2027 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2028 then
2029 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
2030 end if;
2031 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2032 then
2033 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' Caculating all for "'||p_ldap_user.user_name||'"');
2034 end if;
2035
2036 p_ldap_user.realmDN:= getRealmDN(p_ldap_user.user_name);
2037 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2038 then
2039 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' Realm "'||p_ldap_user.realmDN||'"');
2040 end if;
2041
2042
2043
2044 p_ldap_user.parent_DN := getUserRepository(p_ldap_user);
2045
2046 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2047 then
2048 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' User repository "'||p_ldap_user.parent_DN||'"');
2049 end if;
2050
2051 name:= getRealmCommonNameAttribute(p_ldap_user.realmDN);
2052 val := p_ldap_user.user_name;
2053
2054 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2055 then
2056 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' CommonName(realm) Attribute "'||name||'"');
2057 end if;
2058
2059 FND_OID_PLUG.getRDN(p_ldap_user.user_name,p_ldap_user.user_id,name,val,replaceFlag);
2060
2061 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2062 then
2063 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' Custom RDN: '||name||'='||val);
2064 end if;
2065
2066
2067 FND_LDAP_USER.setAttribute(p_ldap_user,name,val,replaceFlag);
2068
2069 p_ldap_user.RDN_ATT_NAME := name;
2070 p_ldap_user.RDN_VALUE := val;
2071
2072 p_ldap_user.dn := name||'='||val||','||p_ldap_user.parent_DN ;
2073
2074 p_ldap_user.NickName_ATT_NAME := getRealmNickNameattr(p_ldap_user.realmDN);
2075
2076 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2077 then
2078 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' Nicknameattribute : '|| p_ldap_user.NickName_ATT_NAME);
2079 end if;
2080
2081 name:=p_ldap_user.NickName_ATT_NAME;
2082 val :=p_ldap_user.user_name;
2083 FND_LDAP_USER.setAttribute(p_ldap_user,name,val,true);
2084
2085 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2086 then
2087 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' replaced '|| p_ldap_user.NickName_ATT_NAME||'='||p_ldap_user.user_name);
2088 end if;
2089 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2090 then
2091 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END->'||p_ldap_user.dn);
2092 end if;
2093 return p_ldap_user.dn;
2094
2095 EXCEPTION WHEN OTHERS THEN
2096 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2097 then
2098 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, sqlerrm);
2099 end if;
2100 raise;
2101 END getDN;
2102
2103 function Helper_NewEmptyCollection return dbms_ldap.STRING_COLLECTION
2104 is
2105 t dbms_ldap.STRING_COLLECTION ;
2106 begin
2107 return t;
2108 end Helper_NewEmptyCollection;
2109
2110 procedure copyData( d1 in out nocopy FND_LDAP_UTIl.ldap_record_values, d2 in out nocopy FND_LDAP_UTIL.ldap_record_values )
2111 is
2112 atName varchar2(4000);
2113 idx pls_integer;
2114 begin
2115 d2.delete;
2116 atName := d1.first;
2117 while atName is not null loop
2118 if d1.exists(atName) then
2119 idx := d1(atName).first;
2120 d2(atName) := Helper_NewEmptyCollection;
2121 while idx is not null loop
2122 d2(atName)(d2(atName).count) := d1(atName)(idx);
2123 idx := d1(atName).next(idx);
2124 end loop;
2125 end if;
2126 atName:= d1.next(atName);
2127 end loop;
2128 end copyData;
2129
2130
2131 Procedure completeForCreate(ldap in dbms_ldap.session ,p_ldap_user IN OUT nocopy fnd_ldap_user.ldap_user_type )
2132 is
2133 replaceFlag boolean;
2134 rdn varchar2(200);
2135 val varchar2(4000);
2136 opt option_type_rec;
2137 l_module_source varchar2(1000) ;
2138
2139 begin
2140 l_module_source := G_MODULE_SOURCE || 'completeForCreate: ';
2141
2142 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2143 then
2144 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'BEGIN:'||p_ldap_user.dn);
2145 end if;
2146
2147 validateVersion;
2148
2149 if (p_ldap_user.realmDN is null) THEN
2150 p_ldap_user.realmDN := getNewUserRealm(p_ldap_user.user_name);
2151 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2152 then
2153 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'RealmDN set to:'||p_ldap_user.realmDN);
2154 end if;
2155 END IF;
2156 if (p_ldap_user.parent_DN is null) THEN
2157 p_ldap_user.parent_DN :=getUserRepository(p_ldap_user);
2158 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2159 then
2160 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'parent_DN set to:'||p_ldap_user.parent_DN);
2161 end if;
2162
2163 END IF ;
2164 if (p_ldap_user.NickName_ATT_NAME is null ) THEN
2165 p_ldap_user.NickName_ATT_NAME :=FND_SSO_REGISTRATION.get_realm_attribute(p_ldap_user.realmDN,'orclCommonNickNameAttribute');
2166 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2167 then
2168 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'NickName_ATT_NAME set to:'||p_ldap_user.NickName_ATT_NAME);
2169 end if;
2170 END IF;
2171 iF (p_ldap_user.RDN_ATT_NAME is null or p_ldap_user.RDN_VALUE is null ) THEN
2172 getRDN(p_ldap_user.user_name,p_ldap_user.user_id,
2173 rdn,val, replaceFlag );
2174 p_ldap_user.RDN_ATT_NAME:= rdn;
2175 p_ldap_user.RDN_VALUE:=val;
2176 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2177 then
2178 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'RND set to:'||p_ldap_user.RDN_ATT_NAME||'='||p_ldap_user.RDN_VALUE);
2179 end if;
2180
2181 FND_LDAP_USER.setAttribute(p_ldap_user, rdn,val,replaceFlag);
2182
2183 END IF;
2184 p_ldap_user.dn := p_ldap_user.RDN_ATT_NAME||'='||p_ldap_user.RDN_VALUE
2185 ||','|| p_ldap_user.parent_DN;
2186 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2187 then
2188 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'dn set to:'||p_ldap_user.dn);
2189 end if;
2190
2191
2192 EXCEPTION WHEN OTHERS THEN
2193 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2194 then
2195 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, sqlerrm);
2196 end if;
2197 raise;
2198 end completeForCreate;
2199
2200
2201 function getRealmList return dbms_ldap.string_collection
2202 IS
2203 ret dbms_ldap.string_collection;
2204 l_module_source varchar2(200) := G_MODULE_SOURCE || 'getRealmList: ';
2205
2206 /*
2207 * 1.0: just return the default realm
2208 * 1.1: return all realms
2209 */
2210 dev_version number := 1.0;
2211
2212 BEGIN
2213 validateVersion;
2214
2215 if (dev_version=1.0) THEN
2216 -- just one realm for now
2217 ret(0) := get_default_realm();
2218 return ret;
2219 ELSIF dev_version=1.1 THEN
2220 -- return all realms
2221 return ret;
2222 ELSE
2223 raise NO_DATA_FOUND;
2224 END IF;
2225 EXCEPTION WHEN OTHERS THEN
2226 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2227 then
2228 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
2229 end if;
2230 raise;
2231
2232 END getRealmList;
2233
2234
2235 PROCEDURE FixupLDAPUser(p_ldap_user IN OUT nocopy FND_LDAP_USER.ldap_user_type, operation pls_integer) IS
2236 opt option_type_rec;
2237 l_module_source varchar2(400):=G_MODULE_SOURCE||'FixupLDAPUser';
2238 BEGIN
2239 -- DataCreationFixup
2240 IF getOption('FIXUP',opt ) THEN
2241 IF opt.opt_mode=G_DYNAMIC THEN
2242 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2243 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Calling Dynamic Fixup:'||opt.val);
2244 END IF;
2245 copyData(p_ldap_user.user_data,user_data);
2246 EXECUTE immediate opt.val USING IN p_ldap_user.user_id, IN p_ldap_user.user_name,
2247 IN operation;
2248 copyData(user_data,p_ldap_user.user_data);
2249
2250 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2251 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Returned from Fixup');
2252 END IF;
2253 END IF;
2254 END IF;
2255 END FixupLDAPUser;
2256
2257 procedure getDefaultRealm_Template(realm out nocopy varchar2) IS
2258 BEGIN
2259 raise NO_DATA_FOUND;
2260 END getDefaultRealm_Template;
2261
2262 procedure getDefaultCreateBase_Template(realm in varchar2, parentDN out nocopy varchar2 ) IS
2263 BEGIN
2264 raise NO_DATA_FOUND;
2265 END getDefaultCreateBase_Template;
2266
2267 procedure getCreateBase_Template(user_id in INTEGER,
2268 user_name in varchar2,
2269 realm in varchar2,
2270 parentDn out nocopy varchar2) IS
2271 BEGIN
2272 raise NO_DATA_FOUND;
2273 END getCreateBase_Template;
2274
2275 procedure getRealm_Template( user_id in INTEGER, user_name in varchar2, realmDn out nocopy varchar2) IS
2276 BEGIN
2277 raise NO_DATA_FOUND;
2278 END getRealm_Template;
2279
2280 procedure getRDN_Template(user_name in varchar2,
2281 user_id in pls_integer,
2282 RDN_attName in out nocopy varchar2,
2283 RND_value in out nocopy varchar2,
2284 replaceFlag in out nocopy pls_integer) IS
2285 BEGIN
2286 raise NO_DATA_FOUND;
2287 END getRDN_Template;
2288
2289 procedure FixupLDAPUser_Template( user_id in INTEGER, user_name in varchar2, operation in pls_integer)
2290 is BEGIN
2291 raise NO_DATA_FOUND;
2292 END FixupLDAPUser_Template;
2293
2294
2295 END FND_OID_PLUG;