[Home] [Help]
PACKAGE BODY: APPS.FND_LDAP_USER
Source
1 package body fnd_ldap_user as
2 /* $Header: AFSCOLUB.pls 120.43.12010000.3 2008/11/20 10:09:57 bmasa ship $ */
3 --
4 -------------------------------------------------------------------------------
5 -- Start of Package Globals
6
7 G_CREATE constant pls_integer := 1;
8 G_UPDATE constant pls_integer := 2;
9 G_MODULE_SOURCE constant varchar2(80) := 'fnd.plsql.oid.fnd_ldap_user.';
10
11 -- End of Package Globals
12 --
13 -------------------------------------------------------------------------------
14
15 function create_user_nodes(p_ldap_session in dbms_ldap.session, p_mod_array in dbms_ldap.mod_array, p_ldap_user in fnd_ldap_util.ldap_user_type,userDN out nocopy varchar2) return pls_integer ;
16 function create_user_subscription(p_guid in raw) return pls_integer;
17 procedure decode_dates(p_user_name in varchar2, p_start_date in date default null, p_end_date in date default null, x_orclisEnabled out nocopy varchar2, x_user_id out nocopy fnd_user.user_id%type);
18 function delete_user_by_name(p_ldap_session in dbms_ldap.session, p_cn in varchar2) return pls_integer;
19 function delete_user_nodes(p_ldap_session in dbms_ldap.session, p_orclguid in fnd_user.user_guid%type) return pls_integer;
20 function delete_user_subscription(p_ldap_session in dbms_ldap.session, guid raw) return pls_integer;
21 function delete_uniquemember(p_ldap_session in dbms_ldap.session, p_orclguid in fnd_user.user_guid%type) return pls_integer;
22 procedure disable_user(p_user_guid in raw,p_user_name in varchar2,x_result out nocopy pls_integer);
23 function process_attributes(p_ldap_user in fnd_ldap_util.ldap_user_type,
24 p_operation_type in pls_integer default G_CREATE,
25 x_atts out nocopy dbms_ldap.string_collection,
26 x_att_values out nocopy dbms_ldap.string_collection) return number;
27
28 function update_user(p_ldap_user in fnd_ldap_util.ldap_user_type) return pls_integer;
29 function update_user(p_ldap_user in fnd_ldap_util.ldap_user_type, p_expire_password in pls_integer) return pls_integer;
30 function update_user_nodes(p_ldap_session in dbms_ldap.session, p_mod_array in dbms_ldap.mod_array, p_orclguid in raw) return pls_integer;
31
32
33 --
34 -------------------------------------------------------------------------------
35 --- REMOVED
36 -- function add_uniquemember(p_ldap_user in fnd_ldap_util.ldap_user_type) return pls_integer is
37 -- translate_ldap_error: Internal
38 -- Will attempt to translate the sqlerrms from an dbms_ldap operation into a FND message
39
40 -------------------------------------------------------------------------------
41
42 procedure delete_user(ldapSession in dbms_ldap.session, p_user_guid in fnd_user.user_guid%type ,
43 x_result out nocopy pls_integer) is
44
45
46 l_module_source varchar2(256) := G_MODULE_SOURCE || 'delete_user: ';
47 l_orclappname varchar2(256);
48 l_user_name varchar2(256);
49 subsNode varchar2(1000);
50 --ldapSession dbms_ldap.session;
51 l_message dbms_ldap.message := null;
52 l_entry dbms_ldap.message := null;
53 l_attrs dbms_ldap.string_collection;
54 l_attrs_vals dbms_ldap.string_collection;
55 l_isenabled varchar2(100);
56 l_creatorname varchar2(1000);
57 searchNodes dbms_ldap.string_collection;
58 l_filter varchar2(256);-- := 'cn=' || p_user_name; Commented out by scheruku to use orclguid instead
59 l_base varchar2(1000);
60 l_guid raw(256);
61 l_fnd_op pls_integer;
62 l_oid_op pls_integer;
63 sso_registration_failure exception;
64 -- l_apps_user_key_type fnd_oid_util.apps_user_key_type;
65 l_orclguid fnd_user.user_guid%type;
66 begin
67
68 -- initializing
69 l_module_source := G_MODULE_SOURCE || 'delete_user: ';
70 x_result := fnd_ldap_util.G_SUCCESS;
71
72 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
73 then
74 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
75 end if;
76
77 --scheruku :: Added logic to get orclguid from fnd_user
78 -- l_apps_user_key_type := fnd_oid_util.get_fnd_user(p_user_name => p_user_name);
79 -- l_orclguid := l_apps_user_key_type.user_guid;
80 l_orclguid := p_user_guid;
81
82 if(l_orclguid IS NULL)
83 then
84 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
85 then
86 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
87 'NULL guid in FND_USER');
88 end if;
89 x_result := fnd_ldap_util.G_FAILURE;
90 else
91 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
92 then
93 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
94 'FND_USER GUID::'||l_orclguid);
95 end if;
96 l_filter := 'orclguid='||l_orclguid;
97 --ldapSession := fnd_ldap_util.get_oid_session;
98 --searchNodes := fnd_ldap_util.get_search_nodes;
99 l_base := ''; -- don't need any more for guid search
100 l_attrs(0) := 'orclisenabled';
101 l_attrs(1) := 'creatorsname';
102 l_attrs(2) := 'orclguid';
103
104 -- search and delete the user only if the creator is the current apps instance and if the user is disabled.
105
106 --for i in 0..searchNodes.count-1 loop
107 --l_base := searchNodes(i);
108 x_result := dbms_ldap.search_s(ld => ldapSession, base => l_base,
109 scope => dbms_ldap.SCOPE_SUBTREE, filter => l_filter, attrs => l_attrs, attronly => 0, res => l_message);
110 if (x_result is not NULL) then
111
112 l_entry := dbms_ldap.first_entry(ldapSession, l_message);
113 if l_entry is not null then
114
115 -- get the first entry
116 l_entry := dbms_ldap.first_entry(ldapSession, l_message);
117
118 l_attrs_vals := dbms_ldap.get_values(ldapSession, l_entry, 'creatorsname');
119 l_creatorname := l_attrs_vals(0);
120 l_attrs_vals := dbms_ldap.get_values(ldapSession, l_entry, 'orclisenabled');
121 if l_attrs_vals is not NULL and l_attrs_vals.count > 0 then
122 l_isenabled := l_attrs_vals(0);
123 end if;
124 l_attrs_vals := dbms_ldap.get_values(ldapSession, l_entry, 'orclguid');
125 l_guid := l_attrs_vals(0);
126
127 if (upper(l_creatorname) = upper(fnd_ldap_util.get_orclappname)
128 and l_isenabled is not NULL
129 and (upper(l_isenabled) = 'INACTIVE' or upper(l_isenabled) = 'DISABLED')
130 ) then
131 x_result := delete_user_subscription(ldapSession, l_guid);
132 -- x_result := delete_uniquemember(ldapSession, p_user_name);
133 -- x_result := delete_user_nodes(ldapSession, p_user_name);
134
135 --scheruku: Calling the APIS which use the GUID instead
136 x_result := delete_uniquemember(ldapSession, l_orclguid);
137 x_result := delete_user_nodes(ldapSession, l_orclguid);
138
139 end if;
140 end if;
141 end if;
142 --end loop;
143 --x_result := fnd_ldap_util.unbind(ldapSession);
144 if (x_result = dbms_ldap.SUCCESS) then
145 x_result := fnd_ldap_util.G_SUCCESS;
146 end if;
147 end if;-- fnd_user guid null check if block ends here
148
149 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
150 then
151 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
152 end if;
153
154 exception
155 when others then
156 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
157 then
158 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
159 end if;
160 x_result := fnd_ldap_util.G_FAILURE;
161
162 end delete_user;
163
164 procedure delete_user(p_user_guid in fnd_user.user_guid%type ,
165 x_result out nocopy pls_integer) is
166
167 ldapSession dbms_ldap.session;
168 dummy pls_integer;
169 BEGIN
170 ldapSession := fnd_ldap_util.c_get_oid_session(dummy);
171 delete_user(ldapSession,p_user_guid,x_result);
172 fnd_ldap_util.c_unbind(ldapSession,dummy);
173 end delete_user;
174 --
175
176 -------------------------------------------------------------------------------
177 procedure change_password(p_user_guid in raw,
178 p_user_name in varchar2,
179 p_new_pwd in varchar2,
180 p_expire_password in pls_integer,
181 x_password out nocopy varchar2,
182 x_result out nocopy pls_integer) is
183
184 usertype fnd_ldap_util.ldap_user_type;
185 l_module_source varchar2(256);
186 no_such_user_exp exception;
187 l_user_id fnd_user.user_id%type;
188 l_local_login varchar2(30);
189 l_allow_sync varchar2(1);
190 l_profile_defined boolean;
191 l_to_synch boolean;
192
193 PRAGMA EXCEPTION_INIT (no_such_user_exp, -20001);
194
195 begin
196 l_module_source := G_MODULE_SOURCE || 'change_password: ';
197
198 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
199 then
200 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
201 end if;
202
203 select user_id into l_user_id
204 from fnd_user
205 where user_name = p_user_name;
206
207 l_to_synch := TRUE;
208
209 fnd_profile.get_specific(
210 name_z => 'APPS_SSO_LOCAL_LOGIN',
211 user_id_z => l_user_id,
212 val_z => l_local_login,
213 defined_z => l_profile_defined);
214
215 if (not l_profile_defined or l_local_login = fnd_oid_util.G_LOCAL) then
216 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
217 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'value of APPS_SSO_LOCAL_LOGIN:: '|| l_local_login);
218 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Local user dont sych '|| p_user_name);
219 end if;
220 l_to_synch := FALSE;
221 else
222 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
223 user_id_z => l_user_id,
224 val_z => l_allow_sync,
225 defined_z => l_profile_defined);
226 if (not l_profile_defined or l_allow_sync = fnd_oid_util.G_N) then
227 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
228 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'value of APPS_SSO_LDAP_SYNC '|| l_allow_sync);
229 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Synch profile is disabled for user ...dont sych '|| p_user_name);
230 end if;
231 l_to_synch := FALSE;
232 else
233 l_to_synch := TRUE;
234 end if;
235 end if;
236
237 if (l_to_synch) then
238
239 usertype.userPassword := p_new_pwd;
240 usertype.orclGUID := p_user_guid;
241
242 x_result := update_user(usertype, p_expire_password);
243
244 else
245 x_result := fnd_ldap_util.G_SUCCESS;
246 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
247 then
248 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
249 'User is a local user or synch is disabled for this user.');
250 end if;
251 end if;
252
253 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
254 then
255 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
256 end if;
257
258 if x_result <> fnd_ldap_util.G_SUCCESS then
259 -- raise no_such_user_exp;
260 -- do not raise exception, the message should be in the FND error stack
261 x_result := fnd_ldap_util.G_FAILURE;
262
263 else
264 fnd_profile.get_specific(
265 name_z => 'APPS_SSO_LOCAL_LOGIN',
266 user_id_z => l_user_id,
267 val_z => l_local_login,
268 defined_z => l_profile_defined);
269
270 if (l_local_login = 'SSO') then
271 x_password := fnd_web_sec.EXTERNAL_PWD;
272 end if;
273 end if;
274
275 exception
276 when no_such_user_exp then
277 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
278 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
279 then
280 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
281 end if;
282 x_result := fnd_ldap_util.G_FAILURE;
283 when others then
284 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
285 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
286 then
287 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
288 end if;
289 --x_result := fnd_ldap_util.G_FAILURE;
290 -- bug 4573677
291 raise;
292
293 end change_password;
294 --
295 -------------------------------------------------------------------------------
296 function user_exists_by_guid( guid in raw ) return pls_integer
297 is
298 dn varchar2(2000);
299 result pls_integer;
300 begin
301
302 dn := fnd_ldap_util.get_dn_for_guid(guid);
303 if (dn is not null) then
304 result := FND_LDAP_UTIL.G_SUCCESS;
305 else
306 result := FND_LDAP_UTIL.G_FAILURE;
307 end if;
308 return result;
309 end user_exists_by_guid;
310 --
311 -------------------------------------------------------------------------------
312 procedure change_user_name(p_user_guid in raw,
313 p_old_user_name in varchar2,
314 p_new_user_name in varchar2,
315 x_result out nocopy pls_integer) is
316
317 l_orclisEnabled varchar2(256);
318 usertype fnd_ldap_util.ldap_user_type;
319 l_module_source varchar2(256);
320 no_such_user_exp exception;
321 invalid_new_user_exp exception;
322 l_nickname varchar2(256);
323
324 l_user_id fnd_user.user_id%type;
325 l_local_login varchar2(30);
326 l_allow_sync varchar2(1);
327 l_profile_defined boolean;
328 l_to_synch boolean;
329
330 l_old_nick_name varchar2(4000);
331 l_old_user_name_same pls_integer;
332 l_new_user_name_exists pls_integer;
333
334 PRAGMA EXCEPTION_INIT (no_such_user_exp, -20001);
335
336 begin
337 l_module_source := G_MODULE_SOURCE || 'change_user_name: ';
338
339 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
340 then
341 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
342 end if;
343
344 /** to do - what if there are multiple linked users ? **/
345
346 decode_dates(p_user_name => p_old_user_name,
347 x_orclisEnabled => l_orclisEnabled,
348 x_user_id => l_user_id);
349
350 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
351 then
352 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'USER id:: '||l_user_id);
353 end if;
354
355
356 l_to_synch := TRUE;
357 fnd_profile.get_specific(
358 name_z => 'APPS_SSO_LOCAL_LOGIN',
359 user_id_z => l_user_id,
360 val_z => l_local_login,
361 defined_z => l_profile_defined);
362
363 if (not l_profile_defined or l_local_login = fnd_oid_util.G_LOCAL)
364 then
365 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
366 then
367 if(l_profile_defined)
368 then
369 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
370 'APPS_SSO_LOCAL_LOGIN profile defined');
371 else
372 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
373 'APPS_SSO_LOCAL_LOGIN profile not defined');
374
375 end if;
376 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
377 'value of APPS_SSO_LOCAL_LOGIN:: '|| l_local_login);
378 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
379 'Local user dont sych '|| p_old_user_name);
380 end if;
381 l_to_synch := FALSE;
382 else
383 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
384 user_id_z => l_user_id,
385 val_z => l_allow_sync,
389 then
386 defined_z => l_profile_defined);
387
388 if (not l_profile_defined or l_allow_sync = fnd_oid_util.G_N)
390 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
391 then
392 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
393 'value of APPS_SSO_LDAP_SYNC '|| l_allow_sync);
394 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
395 'Synch profile is disabled for user ...dont sych '|| p_old_user_name);
396 end if;
397 l_to_synch := FALSE;
398 else
399 l_to_synch := TRUE;
400 end if;
401 end if;
402
403
404 if (l_to_synch)
405 then
406 usertype.uid := p_new_user_name;
407 usertype.orclGUID := p_user_guid;
408
409 l_nickname := fnd_ldap_util.get_orclcommonnicknameattr(usertype.uid);
410
411 /** to do - fnd should send us user_name so that we can propagate user_name changes . the logic below should mimic create_user **/
412
413 if (upper(l_nickname) = fnd_ldap_util.G_MAIL) then
414 usertype.mail := p_new_user_name;
415 elsif (upper(l_nickname) = fnd_ldap_util.G_FACSIMILETELEPHONENUMBER) then
416 usertype.facsimileTelephoneNumber := p_new_user_name;
417 end if;
418
419 --Please refer to following location for a flowchart for the following logic
420 -- http://files.oraclecorp.com/content/MySharedFolders/Single%20Sign-On/Design%20Documentation/
421 -- Release%203.1/Username_Change.gif
422 -- Refer to bug 4655260.
423
424 l_new_user_name_exists := FND_LDAP_UTIL.G_TRUE;
425 l_old_nick_name := upper(fnd_oid_util.get_oid_nickname(p_user_guid => p_user_guid));
426
427 if(l_old_nick_name = p_old_user_name)
428 then
429 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
430 then
431 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
432 'Nickname of the oid user for the old fnd username is same: '|| l_old_nick_name);
433 end if;
434
435 l_new_user_name_exists := user_exists(p_user_name => p_new_user_name);
436
437 if(l_new_user_name_exists = FND_LDAP_UTIL.G_FALSE)
438 then
439 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
440 then
441 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
442 'User with following value for nickname(new) attribute does not exist:: '||p_new_user_name);
443 end if;
444 x_result := update_user(usertype);
445 if (x_result <> fnd_ldap_util.G_SUCCESS)
446 then
447 raise no_such_user_exp;
448 end if;
449 else -- if(l_new_user_name_exists = FND_LDAP_UTIL.G_TRUE)
450 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
451 then
452 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
453 'User with following value for nickname(new) attribute does exist:: '||p_new_user_name);
454 end if;
455 raise invalid_new_user_exp;
456 end if;
457
458 else -- if(l_old_nick_name != p_old_user_name)
459
460 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
461 then
462 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Current Nickname: '|| l_old_nick_name);
463 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Current FND_USER user_name '|| p_old_user_name);
464 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
465 'Nickname of the oid user for the old fnd username is not same: ');
466 end if;
467 if(l_old_nick_name = p_new_user_name)
468 then
469 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
470 then
471 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Current OID nickname and new fnd username match '
472 || l_old_nick_name);
473 end if;
474
475 x_result := fnd_ldap_util.G_SUCCESS;
476 else --if(l_old_nick_name != p_new_user_name)
477 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
478 then
479 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Current OID nickname and new fnd username dont match '
480 || l_old_nick_name);
481 end if;
482
483 l_new_user_name_exists := user_exists(p_user_name => p_new_user_name);
484
485 if(l_new_user_name_exists = FND_LDAP_UTIL.G_FALSE)
486 then
487 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
488 then
489 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'New fnd user_name does not exist in OID ');
490 end if;
491 x_result := fnd_ldap_util.G_SUCCESS;
492 else
493 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
494 then
495 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'New fnd user_name does exist in OID ');
496 end if;
497 raise invalid_new_user_exp;
498 end if;
499 end if;
500 end if;
501 else
502 x_result := fnd_ldap_util.G_SUCCESS;
503
504 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
505 then
509
506 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User is a local user or Synch profile is disabled.');
507 end if;
508 end if;
510 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
511 then
512 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
513 end if;
514
515 exception
516 when invalid_new_user_exp then
517 fnd_message.set_name ('FND', 'FND_SSO_INVALID_NEW_USER_NAME');
518 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
519 then
520 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
521 end if;
522 x_result := fnd_ldap_util.G_FAILURE;
523 when no_such_user_exp then
524 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
525 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
526 then
527 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
528 end if;
529 x_result := fnd_ldap_util.G_FAILURE;
530 when others then
531 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
532 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
533 then
534 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
535 end if;
536 x_result := fnd_ldap_util.G_FAILURE;
537 end change_user_name;
538 --
539 -------------------------------------------------------------------------------
540 procedure create_user(p_user_name in varchar2,
541 p_password in varchar2,
542 p_start_date in date default sysdate,
543 p_end_date in date default null,
544 p_description in varchar2 default null,
545 p_email_address in varchar2 default null,
546 p_fax in varchar2 default null,
547 p_expire_password in pls_integer,
548 x_user_guid out nocopy raw,
549 x_password out nocopy varchar2,
550 x_result out nocopy pls_integer) is
551
552 usertype fnd_ldap_util.ldap_user_type;
553 l_module_source varchar2(256);
554 l_start_date varchar2(256);
555 l_end_date varchar2(656);
556 l_local_login varchar2(100);
557 l_profile_defined boolean;
558 l_nickname varchar2(256);
559 l_password varchar2(256);
560 l_enabled varchar2(30);
561 l_du_result pls_integer;
562 l_cp_result pls_integer;
563 user_name fnd_user.user_name%type;
564
565 begin
566 l_module_source := G_MODULE_SOURCE || 'create_user: ';
567 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
568 then
569 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
570 end if;
571
572 l_enabled := fnd_oid_util.G_ENABLED;
573
574 if ((p_start_date is not null and p_start_date > sysdate)
575 or
576 (p_end_date is not null and p_end_date <= sysdate))
577 then
578 usertype.orclisEnabled := fnd_oid_util.G_DISABLED;
579 else
580 usertype.orclisEnabled := fnd_oid_util.G_ENABLED;
581 end if;
582
583 if (p_expire_password = fnd_ldap_util.G_TRUE) then
584 l_password := p_password;
585 else
586 l_password := null;
587 end if;
588
589 /* If self service user and pending user, create the user as enabled user first and then change
590 the flag to enabled after the password has been updated by proxying as that user. This is because
591 we cannot proxy as a disabled user. */
592 if ( (l_password is null) and (usertype.orclisEnabled = fnd_oid_util.G_DISABLED) ) then
593 l_enabled := fnd_oid_util.G_DISABLED;
594 usertype.orclisEnabled := fnd_oid_util.G_ENABLED;
595 end if;
596
597 -- first create user. If self service, then pass in a null password
598 user_name := p_user_name;
599 l_nickname := fnd_ldap_util.get_orclcommonnicknameattr(user_name);
600
601 usertype.uid := p_user_name;
602 usertype.sn := p_user_name;
603 usertype.cn := p_user_name;
604 usertype.userPassword := l_password;
605 usertype.description := p_description;
606
607 if (upper(l_nickname) = fnd_ldap_util.G_MAIL) then
608 usertype.mail := p_user_name;
609 else
610 usertype.mail := p_email_address;
611 end if;
612
613 if (upper(l_nickname) = fnd_ldap_util.G_FACSIMILETELEPHONENUMBER) then
614 usertype.facsimileTelephoneNumber := p_user_name;
615 else
616 usertype.facsimileTelephoneNumber := p_fax;
617 end if;
618
619 x_result := create_user(usertype);
620
621 if (x_result = fnd_ldap_util.G_SUCCESS) then
622 x_user_guid := get_user_guid(p_user_name);
623
624 fnd_profile.get_specific(
625 name_z => 'APPS_SSO_LOCAL_LOGIN',
626 user_id_z => -1,
627 val_z => l_local_login,
628 defined_z => l_profile_defined);
629
630 if (l_local_login = 'SSO') then
631 x_password := fnd_web_sec.EXTERNAL_PWD;
632 end if;
633
634 -- if p_expire_psasword = false then update the user password (and password only)
635 if ( (l_password is null) and (x_result = fnd_ldap_util.G_SUCCESS) )then
636
637 change_password(x_user_guid,
638 p_user_name,
639 p_password,
640 p_expire_password,
641 x_password,
645 disable_user(x_user_guid,
642 l_cp_result);
643
644 if (l_enabled = fnd_oid_util.G_DISABLED) then
646 p_user_name,
647 l_du_result);
648 end if;
649
650 if ( (l_cp_result = fnd_ldap_util.G_FAILURE ) or (l_du_result = fnd_ldap_util.G_FAILURE) ) then
651 delete_user(x_user_guid, x_result);
652 else
653 x_result := fnd_ldap_util.G_SUCCESS;
654 end if;
655
656 end if;
657
658 else
659 fnd_message.set_name ('FND', 'FND_SSO_USER_EXISTS');
660 end if;
661
662 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
663 then
664 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
665 end if;
666 exception
667 when others then
668 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
669 then
670 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
671 end if;
672 raise;
673
674 end create_user;
675 --
676 -------------------------------------------------------------------------------
677 function create_user(p_ldap_user in fnd_ldap_util.ldap_user_type)
678 return pls_integer is
679
680 l_module_source varchar2(256);
681 subsNode varchar2(1000);
682 usersNode varchar2(1000);
683 usersNodes dbms_ldap.string_collection;
684 result pls_integer;
685 retval pls_integer;
686 num_attributes number;
687 ldapSession dbms_ldap.session;
688 modArray dbms_ldap.mod_array;
689 modvalues dbms_ldap.string_collection;
690 modmultivalues dbms_ldap.string_collection;
691 mandatory_att varchar2(256);
692 modtype varchar2(256);
693 i number;
694 break pls_integer;
695 l_atts dbms_ldap.string_collection;
696 l_att_values dbms_ldap.string_collection;
697 l_link varchar2(10);
698 l_profile_defined boolean;
699 l_nickname varchar2(256);
700 l_orclguid fnd_user.user_guid%type;
701 l_userDN varchar2(4000);
702 l_guid raw(16);
703 flag pls_integer;
704
705 begin
706 l_module_source := G_MODULE_SOURCE || 'create_user: ';
707 -- set default value to failure. change to success when user created successfully
708 retval := fnd_ldap_util.G_FAILURE;
709 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
710 then
711 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
712 end if;
713
714
715 ldapSession := fnd_ldap_util.c_get_oid_session(flag);
716
717 result := user_exists(ldapSession, p_ldap_user.uid);
718 -- only proceed if user doesn't exist and all mandatory attributes are present
719 if (result = fnd_ldap_util.G_FAILURE) and (p_ldap_user.sn is not null) and
720 (p_ldap_user.cn is not null) /*and (p_ldap_user.userPassword is not null)*/ then
721
722 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
723 then
724 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User ' || p_ldap_user.sn || ' does not exist and mandatory attributes are present');
725 end if;
726
727
728
729 num_attributes := process_attributes(p_ldap_user, x_atts => l_atts,
730 x_att_values => l_att_values);
731
732 -- 1 xtra attribute for object class
733 modArray := dbms_ldap.create_mod_array(num => num_attributes + 1);
734
735
736 modmultivalues(0) := 'inetorgperson';
737 modmultivalues(1) := 'orcluserv2';
738 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => dbms_ldap.mod_add, mod_type => 'objectclass', modval => modmultivalues);
739
740 for i in 0..l_atts.count-1 loop
741 modtype := l_atts(i);
742 modvalues(0) := l_att_values(i);
743 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => dbms_ldap.mod_add, mod_type => modtype, modval => modvalues);
744 end loop;
745
746 retval := create_user_nodes(ldapSession, modArray, p_ldap_user,l_userDN);
747 if ((retval <> fnd_ldap_util.G_SUCCESS) and (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL))
748 then
749 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User creation failed');
750 end if;
751
752
753 if (retval = fnd_ldap_util.G_SUCCESS) then
754 l_guid := fnd_ldap_util.get_guid_for_dn(ldapSession,l_userDN);
755 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
756 then
757 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Creating susbscriptions');
758 end if;
759
760 retval := create_user_subscription(l_guid);
761 if ((retval <> fnd_ldap_util.G_SUCCESS) and (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL))
762 then
763 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Subscription creation failed');
764 end if;
765
766 end if;
767
768 -- if retval=false or result=false, that means partial creation, therefore
769 -- delete all created entries (rollback)
770 if ( (retval <> fnd_ldap_util.G_SUCCESS) ) then
771 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
772 then
773 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Deleting user due previous fail');
774 end if;
775
776 result := delete_user_by_name(ldapSession, p_ldap_user.cn);
777 end if;
781
778
779 dbms_ldap.free_mod_array(modptr => modArray);
780
782 elsif (result = fnd_ldap_util.G_SUCCESS) then
783
784 fnd_profile.get_specific(
785 name_z => 'APPS_SSO_LINK_SAME_NAMES',
786 val_z => l_link,
787 defined_z => l_profile_defined);
788
789 if (l_link = 'Y') then
790 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
791 then
792 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User exists but APPS_SSO_LINK_SAME_NAMES is Enabled, adding user to subscription list');
793 end if;
794 --AQUI
795 l_guid:= get_user_guid(p_ldap_user.uid);
796 retval := create_user_subscription(l_guid);
797
798 end if;
799
800 end if;
801 fnd_ldap_util.c_unbind(ldapSession,flag);
802
803 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
804 then
805 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
806 end if;
807
808 return retval;
809
810 exception
811 when others then
812 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
813 then
814 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
815 end if;
816 raise;
817
818 end create_user;
819 --
820 -------------------------------------------------------------------------------
821 function create_user_nodes(p_ldap_session in dbms_ldap.session, p_mod_array in dbms_ldap.mod_array, p_ldap_user in fnd_ldap_util.ldap_user_type,
822 userDN out nocopy varchar2)
823 return pls_integer is
824
825 l_module_source varchar2(256);
826 usersNode varchar2(1000);
827 usersNodes dbms_ldap.string_collection;
828 result pls_integer;
829 retval pls_integer;
830 i number;
831 break pls_integer;
832 dn varchar2(1000);
833 user_name fnd_user.user_name%type;
834 --l_data varchar2(200);
835
836 begin
837 l_module_source := G_MODULE_SOURCE || 'create_user_nodes: ';
838 -- set default value to failure. change to success when user created successfully
839 retval := fnd_ldap_util.G_FAILURE;
840 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
841 then
842 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
843 end if;
844
845 user_name := p_ldap_user.uid ;
846 dn :=fnd_oid_plug.getDN(user_name);
847 if ((retval <> fnd_ldap_util.G_SUCCESS) and (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL))
848 then
849 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Usename:'||user_name||' dn:'||dn);
850 end if;
851
852 result := dbms_ldap.add_s(ld => p_ldap_session, entrydn => dn , modptr => p_mod_array);
853 if result = dbms_ldap.SUCCESS then
854 --result := dbms_ldap.get_session_info(p_ldap_session, 1, l_data);
855 retval := fnd_ldap_util.G_SUCCESS;
856 userDN := dn;
857 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
858 then
859 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'created DN' || userDN);
860 end if;
861
862 else
863 retval := fnd_ldap_util.G_FALSE;
864 userDN := null;
865 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
866 then
867 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Nothig was created');
868 end if;
869 end if;
870 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
871 then
872 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'retval = ' || retval);
873 end if;
874
875 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
876 then
877 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
878 end if;
879 return retval;
880
881 exception
882 when others then
883 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
884 then
885 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
886 end if;
887 raise;
888
889 end create_user_nodes;
890 --
891 -------------------------------------------------------------------------------
892 /*
893 get_ACCOUNT_dn: returns where to store subscription information at OiD.
894 Now is simple, in the future may change for multiples realms.
895 */
896 function get_ACCOUNT_dn(p_guid in raw) return varchar2 is
897 begin
898 return 'cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,' || fnd_ldap_util.get_orclappname;
899 end get_ACCOUNT_dn;
900 --
901 -------------------------------------------------------------------------------
902 function create_user_subscription(p_guid in raw)
903 return pls_integer is
904
905 l_module_source varchar2(256);
906 subsNode varchar2(4000);
907 acctNode varchar2(4000);
908 userDN varchar2(4000);
909 result pls_integer;
910 retval pls_integer;
911 ldapSession dbms_ldap.session;
912 modArray dbms_ldap.mod_array;
913 modmultivalues dbms_ldap.string_collection;
914 i number;
915 flag pls_integer;
916 begin
917 l_module_source := G_MODULE_SOURCE || 'create_user_subscription: ';
918 -- set default value to failure. change to success when user created successfully
922 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin guid='||p_guid);
919 retval := fnd_ldap_util.G_FAILURE;
920 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
921 then
923 end if;
924
925 ldapSession := fnd_ldap_util.c_get_oid_session(flag);
926
927 userDN := fnd_ldap_util.get_dn_for_guid(p_guid);
928
929 acctNode := get_ACCOUNT_dn(p_guid);
930 -- num_attributes := process_attributes(p_ldap_user, x_atts => l_atts,
931 -- x_att_values => l_att_values);
932
933 modArray := dbms_ldap.create_mod_array(num => 2);
934
935 modmultivalues(0) := 'orclServiceSubscriptionDetail';
936 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => dbms_ldap.mod_add, mod_type => 'objectclass', modval => modmultivalues);
937
938 modmultivalues(0) := userDN;
939 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => dbms_ldap.mod_add, mod_type => 'seeAlso', modval => modmultivalues);
940
941 subsNode := 'orclOwnerGUID=' || p_guid|| ',' || acctNode;
942 retval := dbms_ldap.add_s(ld => ldapSession, entrydn => subsNode, modptr => modArray);
943
944 if (retval = dbms_ldap.SUCCESS) then
945 --retval := add_uniquemember(p_ldap_user);
946 fnd_ldap_util.add_attribute_M(ldapSession,acctNode,'uniqueMember',userDN);
947 retval:= fnd_ldap_util.G_SUCCESS;
948 else
949 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
950 then
951 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Failed! retval='||retval||' subsNode:'||subsNode);
952 end if;
953 end if;
954
955 dbms_ldap.free_mod_array(modptr => modArray);
956 fnd_ldap_util.c_unbind(ldapSession,flag);
957
958 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
959 then
960 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
961 end if;
962
963 return retval;
964
965 exception
966 when others then
967 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
968 then
969 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
970 end if;
971 raise;
972
973 end create_user_subscription;
974 --
975 -------------------------------------------------------------------------------
976 procedure decode_dates(p_user_name in varchar2, p_start_date in date, p_end_date in date, x_orclisEnabled out nocopy varchar2, x_user_id out nocopy fnd_user.user_id%type) is
977
978 cursor fnd_dates is
979 select user_id, decode(p_start_date, fnd_user_pkg.null_date, null,
980 null, start_date,
981 p_start_date) l_start_date,
982 decode(p_end_date, fnd_user_pkg.null_date, null,
983 null, end_date,
984 p_end_date) l_end_date
985 from fnd_user
986 where user_name = p_user_name;
987
988 l_rec fnd_dates%rowtype;
989 l_found boolean;
990 l_user_id fnd_user.user_id%type;
991 l_start_date date;
992 l_end_date date;
993 l_module_source varchar2(256);
994 no_such_user_exp exception;
995
996 begin
997 l_module_source := G_MODULE_SOURCE || 'decode_dates: ';
998 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
999 then
1000 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1001 end if;
1002
1003 open fnd_dates;
1004 fetch fnd_dates into l_rec;
1005 l_found := fnd_dates%found;
1006 close fnd_dates;
1007
1008 if (not l_found)
1009 then
1010 raise no_such_user_exp;
1011 end if;
1012
1013
1014 -- Fetching the user_id also in order to fetch user level profiles after call to this procedure.
1015 x_user_id := l_rec.user_id;
1016
1017 if ((l_rec.l_start_date is not null and l_rec.l_start_date > sysdate)
1018 or
1019 (l_rec.l_end_date is not null and l_rec.l_end_date <= sysdate))
1020 then
1021 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1022 then
1023 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User is disabled');
1024 end if;
1025 x_orclisEnabled := fnd_oid_util.G_DISABLED;
1026
1027 else
1028 x_orclisEnabled := fnd_oid_util.G_ENABLED;
1029 end if;
1030
1031 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1032 then
1033 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1034 end if;
1035
1036 exception
1037 when others then
1038 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1039 then
1040 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1041 end if;
1042 raise_application_error(-20001, 'FND_SSO_USER_NOT_FOUND');
1043
1044 end decode_dates;
1045 --
1046 -------------------------------------------------------------------------------
1047 /* TO REMOVE */
1048 function delete_user_by_name(p_ldap_session in dbms_ldap.session, p_cn in varchar2)
1049 return pls_integer is
1050
1051 l_module_source varchar2(256);
1052 result pls_integer;
1053 subsNode varchar2(1000);
1054 usersNode varchar2(1000);
1055 usersNodes dbms_ldap.string_collection;
1056 l_guid fnd_user.user_guid%type;
1057
1058 begin
1062 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1059 l_module_source := G_MODULE_SOURCE || 'delete_user: ';
1060 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1061 then
1063 end if;
1064 -- subsNode := 'cn=' || p_cn || ',cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,' || fnd_ldap_util.get_orclappname;
1065 -- result := dbms_ldap.delete_s(ld => p_ldap_session, entrydn => subsNode);
1066 l_guid := get_user_guid(p_ldap_session,p_cn);
1067 delete_user(p_ldap_session,l_guid,result);
1068
1069 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1070 then
1071 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End res='||result);
1072 end if;
1073 return result;
1074
1075 exception
1076 when others then
1077 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1078 then
1079 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1080 end if;
1081 raise;
1082
1083 end delete_user_by_name;
1084
1085 --
1086 --Added by scheruku for Nickname changes
1087 -------------------------------------------------------------------------------
1088 function delete_user_nodes(p_ldap_session in dbms_ldap.session,
1089 p_orclguid in fnd_user.user_guid%type) return pls_integer is
1090
1091 l_module_source varchar2(256);
1092 usersNode varchar2(1000);
1093 usersNodes dbms_ldap.string_collection;
1094 l_result pls_integer;
1095
1096 begin
1097 l_module_source := G_MODULE_SOURCE || 'delete_user_nodes: ';
1098 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1099 then
1100 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1101 end if;
1102
1103
1104
1105 usersNode := fnd_ldap_util.get_dn_for_guid(p_orclguid => p_orclguid);
1106
1107 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1108 then
1109 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'DN for user with GUID::'
1110 ||p_orclguid||' DN::'||usersNode);
1111 end if;
1112
1113 l_result := dbms_ldap.delete_s(ld => p_ldap_session, entrydn => usersNode);
1114
1115 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1116 then
1117 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1118 end if;
1119 return l_result;
1120 end delete_user_nodes;
1121 --
1122 ------------------------------------------------------------------------------
1123 function delete_user_subscription(p_ldap_session in dbms_ldap.session, guid in raw)
1124 return pls_integer is
1125
1126 l_module_source varchar2(256);
1127 subsNode varchar2(1000);
1128 l_user_guid raw(256) := guid;
1129 l_attrs dbms_ldap.string_collection;
1130 l_message dbms_ldap.message := null;
1131 l_result pls_integer;
1132 l_entry dbms_ldap.message := null;
1133
1134 begin
1135 l_module_source := G_MODULE_SOURCE || 'delete_user_subscription ';
1136 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1137 then
1138 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1139 end if;
1140
1141 -- delete subcriptions with orclOwnerGUID
1142 subsNode := 'cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,' || fnd_ldap_util.get_orclappname;
1143 l_result := dbms_ldap.search_s(ld => p_ldap_session, base => subsNode,
1144 scope => dbms_ldap.SCOPE_SUBTREE, filter => 'orclOwnerGUID=' || l_user_guid, attrs => l_attrs, attronly => 0, res => l_message);
1145 if (l_result is not NULL) then
1146 l_entry := dbms_ldap.first_entry(p_ldap_session, l_message);
1147 if l_entry is not null then
1148 l_result := dbms_ldap.delete_s(ld => p_ldap_session, entrydn => 'orclOwnerGUID=' || l_user_guid||','||subsNode);
1149 end if;
1150 end if;
1151
1152 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1153 then
1154 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1155 end if;
1156 return l_result;
1157 end delete_user_subscription;
1158 --
1159 -------------------------------------------------------------------------------
1160 function delete_uniquemember(p_ldap_session in dbms_ldap.session,
1161 p_orclguid in fnd_user.user_guid%type) return pls_integer is
1162
1163 l_module_source varchar2(256);
1164 subsNode varchar2(1000);
1165 usersNode varchar2(1000);
1166 usersNodes dbms_ldap.string_collection;
1167 result pls_integer;
1168 retval pls_integer;
1169 modArray dbms_ldap.mod_array;
1170 modmultivalues dbms_ldap.string_collection;
1171 i number;
1172
1173 begin
1174 l_module_source := G_MODULE_SOURCE || 'delete_uniquemember: ';
1175 -- set default value to failure. change to success when added successfully
1176 retval := fnd_ldap_util.G_FAILURE;
1177 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1178 then
1179 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1180 end if;
1181
1182 subsNode := 'cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,' || fnd_ldap_util.get_orclappname;
1183
1184 modArray := dbms_ldap.create_mod_array(num => 1);
1185
1186 modmultivalues(0) := fnd_ldap_util.get_dn_for_guid(p_orclguid);
1187
1191
1188 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => dbms_ldap.mod_delete, mod_type => 'uniquemember', modval => modmultivalues);
1189
1190 retval := dbms_ldap.modify_s(ld => p_ldap_Session, entrydn => subsNode, modptr => modArray);
1192
1193 if (retval = dbms_ldap.SUCCESS) then
1194 retval := fnd_ldap_util.G_SUCCESS;
1195 end if;
1196
1197 dbms_ldap.free_mod_array(modptr => modArray);
1198
1199 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1200 then
1201 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1202 end if;
1203
1204 return retval;
1205
1206 exception
1207 when others then
1208 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1209 then
1210 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1211 end if;
1212 raise;
1213
1214 end delete_uniquemember;
1215 --
1216 -------------------------------------------------------------------------------
1217 procedure disable_user(p_user_guid in raw,
1218 p_user_name in varchar2,
1219 x_result out nocopy pls_integer) is
1220
1221 usertype fnd_ldap_util.ldap_user_type;
1222 l_module_source varchar2(256);
1223 no_such_user_exp exception;
1224 l_user_id fnd_user.user_id%type;
1225 l_local_login varchar2(30);
1226 l_allow_sync varchar2(1);
1227 l_profile_defined boolean;
1228 l_to_synch boolean;
1229
1230 PRAGMA EXCEPTION_INIT (no_such_user_exp, -20001);
1231
1232 begin
1233 l_module_source := G_MODULE_SOURCE || 'disable_user: ';
1234
1235 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1236 then
1237 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1238 end if;
1239
1240 usertype.orclisEnabled := fnd_oid_util.G_DISABLED;
1241 usertype.orclGUID := p_user_guid;
1242
1243 x_result := update_user(usertype);
1244
1245 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1246 then
1247 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1248 end if;
1249
1250 if x_result <> fnd_ldap_util.G_SUCCESS then
1251 raise no_such_user_exp;
1252 end if;
1253
1254 exception
1255 when no_such_user_exp then
1256 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
1257 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1258 then
1259 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1260 end if;
1261 x_result := fnd_ldap_util.G_FAILURE;
1262 when others then
1263 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
1264 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1265 then
1266 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1267 end if;
1268 x_result := fnd_ldap_util.G_FAILURE;
1269
1270 end disable_user;
1271 --
1272 -------------------------------------------------------------------------------
1273 function get_attribute_value(p_user_name in varchar2, p_attribute_name in varchar2) return varchar2 is
1274
1275 l_module_source varchar2(256);
1276 result pls_integer;
1277 l_user_guid raw(256);
1278 l_message dbms_ldap.message := null;
1279 l_entry dbms_ldap.message := null;
1280 l_attrs dbms_ldap.string_collection;
1281 searchBase varchar2(1000);
1282 searchFilter varchar2(1000);
1283 l_attribute_value varchar2(1000);
1284 ldapSession dbms_ldap.session;
1285
1286 l_attr_list ldap_attr_list;
1287 l_guid varchar2(256);
1288 l_user_name varchar2(256);
1289 l_attr_value varchar2(1000);
1290 flag pls_integer;
1291 begin
1292 l_module_source := G_MODULE_SOURCE || 'get_attribute_value: ';
1293
1294 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1295 then
1296 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1297 end if;
1298
1299 ldapSession := fnd_ldap_util.c_get_oid_session(flag);
1300
1301 searchBase := fnd_oid_plug.getSearchBase(p_user_name);
1302 searchFilter := fnd_oid_plug.getSearchFilter(p_user_name);
1303
1304 l_attrs(0) := upper(p_attribute_name);
1305 result := dbms_ldap.search_s(ld => ldapSession, base => searchBase, scope => dbms_ldap.SCOPE_SUBTREE, filter => searchFilter, attrs => l_attrs, attronly => 0, res => l_message);
1306
1307 -- get the first entry
1308 l_entry := dbms_ldap.first_entry(ldapSession, l_message);
1309 l_attrs := dbms_ldap.get_values(ldapSession, l_entry, p_attribute_name);
1310 l_attribute_value := l_attrs(0);
1311
1312 fnd_ldap_util.c_unbind(ldapSession,flag);
1313
1314 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1315 then
1316 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
1317 end if;
1318 return l_attribute_value;
1319
1320 exception
1321 when others then
1322 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1323 then
1324 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1325 end if;
1326 raise;
1327
1328 end get_attribute_value;
1329 --
1330 ---------------------------------
1331 --
1332 -- Return the first GUID found for this username
1333 -- also return in n the number of entryes found
1334 --
1335 --
1339 l_module_source varchar2(256);
1336 function get_user_guid_and_count(p_user_name in varchar2, n out nocopy pls_integer)
1337 return varchar2 is
1338
1340 result pls_integer;
1341 l_user_guid raw(256);
1342 l_message dbms_ldap.message := null;
1343 l_entry dbms_ldap.message := null;
1344 l_attrs dbms_ldap.string_collection;
1345 orclguid varchar2(1000);
1346 search_filter varchar2(1000);
1347 search_base varchar2(1000);
1348 ldapSession dbms_ldap.session;
1349 flag pls_integer;
1350 begin
1351 l_module_source := G_MODULE_SOURCE || 'get_user_guid_and_count: ';
1352 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1353 then
1354 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin p_username='||p_user_name);
1355 end if;
1356 search_filter := fnd_oid_plug.getSearchFilter(p_user_name);
1357 search_base := '' ; -- going with this , as is what SSO Login does
1358 -- Other options are
1359 -- search_base := fnd_ldap_util.get_default_realm;
1360 -- search_base := fnd_ldap_util.get_user_base_dn(fnd_ldap_util.get_default_realm);
1361 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1362 then
1363 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' filter : ' || search_filter);
1364 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' search_base : ' || search_base);
1365 end if;
1366
1367 ldapSession := fnd_ldap_util.c_get_oid_session(flag);
1368
1369 l_attrs(0) := 'orclguid';
1370 result := dbms_ldap.search_s(ld => ldapSession, base => search_base, scope => dbms_ldap.SCOPE_SUBTREE, filter => search_filter , attrs => l_attrs, attronly => 0, res => l_message);
1371
1372 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1373 then
1374 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' dbms_ldap.search_s returned '||result);
1375 end if;
1376 -- get the counter
1377 n := dbms_ldap.count_entries(ldapSession,l_message);
1378 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1379 then
1380 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' FOUND N='||n);
1381 end if;
1382 if (n=0) then
1383 -- not found
1384 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1385 then
1386 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' NOT FOUND');
1387 end if;
1388 orclguid:=null;
1389 else
1390 -- get the first entry
1391 l_entry := dbms_ldap.first_entry(ldapSession, l_message);
1392 l_attrs := dbms_ldap.get_values(ldapSession, l_entry, 'orclguid');
1393 orclguid := l_attrs(0);
1394 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1395 then
1396 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' FOUND orclguid='||orclguid);
1397 end if;
1398 end if ;
1399
1400
1401 fnd_ldap_util.c_unbind(ldapSession,flag);
1402
1403 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1404 then
1405 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, ' END orclguid='||orclguid||' n='||n);
1406 end if;
1407 return orclguid;
1408
1409
1410 end get_user_guid_and_count;
1411 -------------------------------------------------------------------------------
1412 function get_user_guid(p_ldap_session in dbms_ldap.session, p_user_name in varchar2)
1413 return raw is
1414
1415 l_module_source varchar2(256);
1416 result pls_integer;
1417 l_user_guid raw(256);
1418 l_message dbms_ldap.message := null;
1419 l_entry dbms_ldap.message := null;
1420 l_attrs dbms_ldap.string_collection;
1421 searchBase varchar2(1000);
1422 searchFilter varchar2(1000);
1423 orclguid varchar2(1000);
1424 ldapSession dbms_ldap.session;
1425 dummy dbms_ldap.session;
1426
1427 begin
1428 l_module_source := G_MODULE_SOURCE || 'get_user_guid: ';
1429 -- retval := fnd_ldap_util.G_FAILURE;
1430 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1431 then
1432 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1433 end if;
1434
1435 ldapSession := p_ldap_session;
1436 --usersNodes := fnd_ldap_util.get_users_nodes;
1437 --usersNode := 'cn=' || p_user_name || ',' || usersNodes(0);
1438 searchBase := fnd_oid_plug.getSearchBase(p_user_name);
1439 searchFilter := fnd_oid_plug.getSearchFilter(p_user_name);
1440
1441 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1442 then
1443 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'base='||searchBase||' ('||searchFilter||')');
1444 end if;
1445
1446 l_attrs(0) := 'orclguid';
1447 result := dbms_ldap.search_s(ld => ldapSession, base => searchBase, scope => dbms_ldap.SCOPE_SUBTREE, filter => searchFilter, attrs => l_attrs, attronly => 0, res => l_message);
1448
1449 -- get the first entry
1450 l_entry := dbms_ldap.first_entry(ldapSession, l_message);
1451 if (l_entry is not null) then
1452 l_attrs := dbms_ldap.get_values(ldapSession, l_entry, 'orclguid');
1453 orclguid := l_attrs(0);
1454 else
1455 orclguid := null;
1456 end if;
1457
1458 l_user_guid := orclguid;
1459
1460 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1461 then
1465 --result := fnd_ldap_util.unbind(ldapSession);
1462 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'GUID found = ' || l_user_guid);
1463 end if;
1464
1466
1467 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1468 then
1469 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1470 end if;
1471 return l_user_guid;
1472
1473 exception
1474 when others then
1475 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1476 then
1477 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1478 -- print stack just for 7306960
1479 result:= -99;
1480 dummy := fnd_ldap_util.c_get_oid_session(result);
1481 end if;
1482 raise;
1483
1484 end get_user_guid;
1485
1486 function get_user_guid( p_user_name in varchar2)
1487 return raw is
1488 l_ldap dbms_ldap.session;
1489 ret fnd_user.user_guid%type;
1490 dummy pls_integer;
1491 BEGIN
1492 l_ldap := fnd_ldap_util.c_get_oid_session(dummy);
1493 ret := get_user_guid(l_ldap,p_user_name);
1494 fnd_ldap_util.C_unbind(l_ldap,dummy);
1495 return ret;
1496 END get_user_guid;
1497
1498 --
1499 -------------------------------------------------------------------------------
1500 procedure link_user(p_user_name in varchar2,
1501 x_user_guid out nocopy raw,
1502 x_password out nocopy varchar2,
1503 x_result out nocopy pls_integer) is
1504
1505 l_module_source varchar2(256);
1506 l_user_exists pls_integer;
1507 l_result pls_integer;
1508 l_orclguid fnd_user.user_guid%type;
1509 l_local_login varchar2(100);
1510 l_profile_defined boolean;
1511 l_nickname varchar2(256);
1512
1513 begin
1514 l_module_source := G_MODULE_SOURCE || 'link_user: ';
1515 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1516 then
1517 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1518 end if;
1519
1520
1521 l_orclguid := get_user_guid(p_user_name);
1522 -- only proceed if user exists
1523
1524 if (l_orclguid is not null ) then
1525
1526
1527 fnd_oid_util.add_user_to_OID_sub_list(p_orclguid => l_orclguid, x_result => l_result);
1528
1529 x_result := l_result;
1530
1531 if (l_result = fnd_ldap_util.G_SUCCESS) then
1532 x_user_guid := l_orclguid;
1533
1534 fnd_profile.get_specific(
1535 name_z => 'APPS_SSO_LOCAL_LOGIN',
1536 user_id_z => -1,
1537 val_z => l_local_login,
1538 defined_z => l_profile_defined);
1539
1540 if (l_local_login = 'SSO') then
1541 x_password := fnd_web_sec.EXTERNAL_PWD;
1542 end if;
1543
1544 end if;
1545
1546 -- user does not exist in OID
1547 else
1548 fnd_message.set_name('FND', 'FND_SSO_USER_NOT_FOUND');
1549 x_result := fnd_ldap_util.G_FAILURE;
1550 end if;
1551
1552 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1553 then
1554 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1555 end if;
1556
1557 exception
1558 when others then
1559 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1560 then
1561 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1562 end if;
1563 raise;
1564
1565 end link_user;
1566 --
1567 -------------------------------------------------------------------------------
1568 function process_attributes(p_ldap_user in fnd_ldap_util.ldap_user_type,
1569 p_operation_type in pls_integer default G_CREATE,
1570 x_atts out nocopy dbms_ldap.string_collection,
1571 x_att_values out nocopy dbms_ldap.string_collection)
1572 return number is
1573
1574 l_module_source varchar2(256);
1575 num_attributes number;
1576
1577 begin
1578 l_module_source := G_MODULE_SOURCE || 'process_attributes: ';
1579 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1580 then
1581 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1582 end if;
1583
1584 num_attributes := 0;
1585
1586 if (p_ldap_user.sn is not null) then
1587 x_atts(num_attributes) := 'sn';
1588 x_att_values(num_attributes) := p_ldap_user.sn;
1589 num_attributes := num_attributes + 1;
1590 end if;
1591 if (p_ldap_user.cn is not null) then
1592 x_atts(num_attributes) := 'cn';
1593 x_att_values(num_attributes) := p_ldap_user.cn;
1594 num_attributes := num_attributes + 1;
1595 end if;
1596 if (p_ldap_user.uid is not null) then
1597 x_atts(num_attributes) := 'uid';
1598 x_att_values(num_attributes) := p_ldap_user.uid;
1599 num_attributes := num_attributes + 1;
1600 end if;
1601 if (p_ldap_user.userPassword is not null) then
1602 x_atts(num_attributes) := 'userPassword';
1603 x_att_values(num_attributes) := p_ldap_user.userPassword;
1604 num_attributes := num_attributes + 1;
1605 end if;
1606 if (p_ldap_user.telephoneNumber is not null) then
1607 x_atts(num_attributes) := 'telephoneNumber';
1608 x_att_values(num_attributes) := p_ldap_user.telephoneNumber;
1609 num_attributes := num_attributes + 1;
1610 end if;
1614 num_attributes := num_attributes + 1;
1611 if (p_ldap_user.street is not null) then
1612 x_atts(num_attributes) := 'street';
1613 x_att_values(num_attributes) := p_ldap_user.street;
1615 end if;
1616 if (p_ldap_user.postalCode is not null) then
1617 x_atts(num_attributes) := 'postalCode';
1618 x_att_values(num_attributes) := p_ldap_user.postalCode;
1619 num_attributes := num_attributes + 1;
1620 end if;
1621 if (p_ldap_user.physicalDeliveryOfficeName is not null) then
1622 x_atts(num_attributes) := 'physicalDeliveryOfficeName';
1623 x_att_values(num_attributes) := p_ldap_user.physicalDeliveryOfficeName;
1624 num_attributes := num_attributes + 1;
1625 end if;
1626 if (p_ldap_user.st is not null) then
1627 x_atts(num_attributes) := 'st';
1628 x_att_values(num_attributes) := p_ldap_user.st;
1629 num_attributes := num_attributes + 1;
1630 end if;
1631 if (p_ldap_user.l is not null) then
1632 x_atts(num_attributes) := 'l';
1633 x_att_values(num_attributes) := p_ldap_user.l;
1634 num_attributes := num_attributes + 1;
1635 end if;
1636 if (p_ldap_user.displayName is not null) then
1637 x_atts(num_attributes) := 'displayName';
1638 x_att_values(num_attributes) := p_ldap_user.displayName;
1639 num_attributes := num_attributes + 1;
1640 end if;
1641 if (p_ldap_user.givenName is not null) then
1642 x_atts(num_attributes) := 'givenName';
1643 x_att_values(num_attributes) := p_ldap_user.givenName;
1644 num_attributes := num_attributes + 1;
1645 end if;
1646 if (p_ldap_user.homePhone is not null) then
1647 x_atts(num_attributes) := 'homePhone';
1648 x_att_values(num_attributes) := p_ldap_user.homePhone;
1649 num_attributes := num_attributes + 1;
1650 end if;
1651 if (p_ldap_user.mail is not null) then
1652 x_atts(num_attributes) := 'mail';
1653 x_att_values(num_attributes) := p_ldap_user.mail;
1654 num_attributes := num_attributes + 1;
1655 end if;
1656 if (p_ldap_user.c is not null) then
1657 x_atts(num_attributes) := 'c';
1658 x_att_values(num_attributes) := p_ldap_user.c;
1659 num_attributes := num_attributes + 1;
1660 end if;
1661 if (p_ldap_user.facsimileTelephoneNumber is not null) then
1662 x_atts(num_attributes) := 'facsimileTelephoneNumber';
1663 x_att_values(num_attributes) := p_ldap_user.facsimileTelephoneNumber;
1664 num_attributes := num_attributes + 1;
1665 end if;
1666 if (p_ldap_user.description is not null) then
1667 x_atts(num_attributes) := 'description';
1668 x_att_values(num_attributes) := p_ldap_user.description;
1669 num_attributes := num_attributes + 1;
1670 end if;
1671 if (p_ldap_user.orclisEnabled is not null) then
1672 x_atts(num_attributes) := 'orclisEnabled';
1673 x_att_values(num_attributes) := p_ldap_user.orclisEnabled;
1674 num_attributes := num_attributes + 1;
1675 end if;
1676 if (p_ldap_user.orclActiveStartDate is not null) then
1677 x_atts(num_attributes) := 'orclActiveStartDate';
1678 x_att_values(num_attributes) := p_ldap_user.orclActiveStartDate;
1679 num_attributes := num_attributes + 1;
1680 end if;
1681 if (p_ldap_user.orclActiveEndDate is not null) then
1682 x_atts(num_attributes) := 'orclActiveEndDate';
1683 x_att_values(num_attributes) := p_ldap_user.orclActiveEndDate;
1684 num_attributes := num_attributes + 1;
1685 end if;
1686
1687 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1688 then
1689 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1690 end if;
1691
1692 return num_attributes;
1693
1694 exception
1695 when others then
1696 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1697 then
1698 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1699 end if;
1700 raise;
1701
1702 end process_attributes;
1703 --
1704 -------------------------------------------------------------------------------
1705 procedure unlink_user(p_user_guid in fnd_user.user_guid%type,
1706 p_user_name in varchar2,
1707 x_result out nocopy pls_integer) is
1708
1709 cursor linked_users is
1710 select user_name
1711 from fnd_user
1712 where user_guid = p_user_guid
1713 and user_name <> p_user_name;
1714
1715 l_rec linked_users%rowtype;
1716 l_found boolean;
1717 l_module_source varchar2(256);
1718 l_local_login varchar2(100);
1719 l_profile_defined boolean;
1720 l_ldap_session dbms_ldap.session :=null;
1721 l_user_exists pls_integer;
1722 dn varchar2(2000);
1723 dummy pls_integer;
1724
1725 begin
1726 l_module_source := G_MODULE_SOURCE || 'unlink_user: ';
1727 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1728 then
1729 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1730 end if;
1731
1732 open linked_users;
1733 fetch linked_users into l_rec;
1734 l_found := linked_users%found;
1735 close linked_users;
1736
1737 -- no other user linked
1738 if (not l_found)
1739 then
1740
1741 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1742 then
1743 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'No other FND users linked to this OID User');
1744 end if;
1745 l_user_exists := user_exists_by_guid( p_user_guid);
1749 l_ldap_session := fnd_ldap_util.c_get_oid_session(dummy);
1746
1747 if (l_user_exists = fnd_ldap_util.G_SUCCESS) then
1748
1750
1751 x_result := delete_user_subscription(l_ldap_session, p_user_guid);
1752 x_result := delete_uniquemember(l_ldap_session, p_user_guid);
1753
1754 -- user does not exist in OID
1755 else
1756 fnd_message.set_name('FND', 'FND_SSO_USER_NOT_FOUND');
1757 x_result := fnd_ldap_util.G_FAILURE;
1758 end if;
1759
1760 -- other users linked
1761 else
1762
1763 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1764 then
1765 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Other FND users linked to this OID User');
1766 end if;
1767
1768 x_result := fnd_ldap_util.G_FAILURE;
1769 fnd_message.set_name ('FND', 'FND_SSO_USER_MULT_LINKED');
1770
1771 end if;
1772 if ( l_ldap_session is not null) then fnd_ldap_util.c_unbind(l_ldap_session,dummy); end if ;
1773 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1774 then
1775 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1776 end if;
1777
1778 exception
1779 when others then
1780 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1781 then
1782 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1783 end if;
1784 raise;
1785
1786 end unlink_user;
1787 --
1788 procedure update_user(p_user_guid in raw,
1789 p_user_name in varchar2,
1790 p_password in varchar2 default null,
1791 p_start_date in date default null,
1792 p_end_date in date default null,
1793 p_description in varchar2 default null,
1794 p_email_address in varchar2 default null,
1795 p_fax in varchar2 default null,
1796 x_result out nocopy pls_integer) is
1797 l_password pls_integer;
1798 begin
1799 update_user(p_user_guid ,
1800 p_user_name,
1801 p_password ,
1802 p_start_date ,
1803 p_end_date,
1804 p_description,
1805 p_email_address ,
1806 p_fax,
1807 l_password,
1808 x_result );
1809 end update_user;
1810 -------------------------------------------------------------------------------
1811
1812 procedure update_user(p_user_guid in raw,
1813 p_user_name in varchar2,
1814 p_password in varchar2 default null,
1815 p_start_date in date default null,
1816 p_end_date in date default null,
1817 p_description in varchar2 default null,
1818 p_email_address in varchar2 default null,
1819 p_fax in varchar2 default null,
1820 x_password out nocopy varchar2,
1821 x_result out nocopy pls_integer) is
1822
1823
1824 l_orclisEnabled varchar2(256);
1825 usertype fnd_ldap_util.ldap_user_type;
1826 l_module_source varchar2(256);
1827 no_such_user_exp exception;
1828 l_nickname varchar2(256);
1829
1830 l_user_id fnd_user.user_id%type;
1831 l_local_login varchar2(30);
1832 l_allow_sync varchar2(1);
1833 l_profile_defined boolean;
1834 l_to_synch boolean;
1835
1836 PRAGMA EXCEPTION_INIT (no_such_user_exp, -20001);
1837
1838 begin
1839
1840 l_module_source := G_MODULE_SOURCE || 'update_user[proc]: ';
1841
1842 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1843 then
1844 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1845 end if;
1846
1847 /** to do - what if there are multiple linked users ? **/
1848
1849 decode_dates(p_user_name, p_start_date, p_end_date,
1850 x_orclisEnabled => l_orclisEnabled,
1851 x_user_id => l_user_id);
1852
1853
1854 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1855 then
1856 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'USER id:: '||l_user_id);
1857 end if;
1858
1859 l_to_synch := TRUE;
1860 fnd_profile.get_specific(
1861 name_z => 'APPS_SSO_LOCAL_LOGIN',
1862 user_id_z => l_user_id,
1863 val_z => l_local_login,
1864 defined_z => l_profile_defined);
1865
1866 if (not l_profile_defined or l_local_login = fnd_oid_util.G_LOCAL)
1867 then
1868 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1869 then
1870 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1871 'value of APPS_SSO_LOCAL_LOGIN:: '|| l_local_login);
1872 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1873 'Local user dont sych '|| p_user_name);
1874 end if;
1875 l_to_synch := FALSE;
1876 else
1877 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
1878 user_id_z => l_user_id,
1879 val_z => l_allow_sync,
1880 defined_z => l_profile_defined);
1881
1882 if (not l_profile_defined or l_allow_sync = fnd_oid_util.G_N)
1883 then
1887 'value of APPS_SSO_LDAP_SYNC '|| l_allow_sync);
1884 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1885 then
1886 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1888 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1889 'Synch profile is disabled for user ...dont sych '|| p_user_name);
1890 end if;
1891 l_to_synch := FALSE;
1892 else
1893 l_to_synch := TRUE;
1894 end if;
1895 end if;
1896
1897 if (l_to_synch) then
1898
1899 -- usertype.uid := p_user_name;
1900 usertype.userPassword := p_password;
1901 usertype.description := p_description;
1902
1903 if (l_orclisEnabled = fnd_oid_util.G_ENABLED)
1904 then
1905 usertype.orclisEnabled := fnd_oid_util.G_ENABLED;
1906 end if;
1907
1908 usertype.orclGUID := p_user_guid;
1909
1910 --l_nickname := fnd_ldap_util.get_orclcommonnicknameattr;
1911 l_nickname := fnd_oid_plug.getNickNameattr(p_user_name);
1912
1913 /** to do - fnd should send us user_name so that we can propagate user_name changes . the logic below should mimic create_user **/
1914
1915 if (upper(l_nickname) <> fnd_ldap_util.G_MAIL) then
1916 usertype.mail := p_email_address;
1917 end if;
1918
1919 if (upper(l_nickname) <> fnd_ldap_util.G_FACSIMILETELEPHONENUMBER) then
1920 usertype.facsimileTelephoneNumber := p_fax;
1921 end if;
1922
1923 x_result := update_user(usertype);
1924
1925 else
1926 x_result := fnd_ldap_util.G_SUCCESS;
1927 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1928 then
1929 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1930 'User is a local user or synch is disabled for this user.');
1931 end if;
1932 end if;
1933
1934 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1935 then
1936 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1937 end if;
1938
1939 if x_result <> fnd_ldap_util.G_SUCCESS then
1940 raise no_such_user_exp;
1941 else
1942 fnd_profile.get_specific(
1943 name_z => 'APPS_SSO_LOCAL_LOGIN',
1944 user_id_z => l_user_id,
1945 val_z => l_local_login,
1946 defined_z => l_profile_defined);
1947
1948 if (l_local_login = 'SSO') then
1949 x_password := fnd_web_sec.EXTERNAL_PWD;
1950 end if;
1951 end if;
1952
1953 exception
1954 when no_such_user_exp then
1955 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
1956 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1957 then
1958 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1959 end if;
1960 x_result := fnd_ldap_util.G_FAILURE;
1961 when others then
1962 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
1963 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1964 then
1965 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
1966 end if;
1967 x_result := fnd_ldap_util.G_FAILURE;
1968
1969 end update_user;
1970 --
1971 -------------------------------------------------------------------------------
1972 procedure update_user(p_user_guid in raw,
1973 p_user_name in varchar2,
1974 p_password in varchar2 default null,
1975 p_start_date in date default null,
1976 p_end_date in date default null,
1977 p_description in varchar2 default null,
1978 p_email_address in varchar2 default null,
1979 p_fax in varchar2 default null,
1980 p_expire_password in pls_integer,
1981 x_password out nocopy varchar2,
1982 x_result out nocopy pls_integer) is
1983
1984 l_orclisEnabled varchar2(256);
1985 usertype fnd_ldap_util.ldap_user_type;
1986 l_module_source varchar2(256);
1987 no_such_user_exp exception;
1988 l_nickname varchar2(256);
1989 l_user_id fnd_user.user_id%type;
1990 l_local_login varchar2(30);
1991 l_allow_sync varchar2(1);
1992 l_profile_defined boolean;
1993 l_to_synch boolean;
1994
1995 PRAGMA EXCEPTION_INIT (no_such_user_exp, -20001);
1996
1997 begin
1998 l_module_source := G_MODULE_SOURCE || 'update_user: ';
1999
2000 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2001 then
2002 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2003 end if;
2004
2005 if (p_password is not null) then
2006 change_password(p_user_guid,
2007 p_user_name,
2008 p_password,
2009 p_expire_password,
2010 x_password,
2011 x_result);
2012 end if;
2013
2014 /** to do - what if there are multiple linked users ? **/
2015
2016 decode_dates(p_user_name, p_start_date, p_end_date,
2017 x_orclisEnabled => l_orclisEnabled,
2018 x_user_id => l_user_id);
2019
2020
2021 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2022 then
2023 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'USER id:: '||l_user_id);
2024 end if;
2025
2026 l_to_synch := TRUE;
2027 fnd_profile.get_specific(
2031 defined_z => l_profile_defined);
2028 name_z => 'APPS_SSO_LOCAL_LOGIN',
2029 user_id_z => l_user_id,
2030 val_z => l_local_login,
2032
2033 if (not l_profile_defined or l_local_login = fnd_oid_util.G_LOCAL)
2034 then
2035 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2036 then
2037 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
2038 'value of APPS_SSO_LOCAL_LOGIN:: '|| l_local_login);
2039 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
2040 'Local user dont sych '|| p_user_name);
2041 end if;
2042 l_to_synch := FALSE;
2043 else
2044 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
2045 user_id_z => l_user_id,
2046 val_z => l_allow_sync,
2047 defined_z => l_profile_defined);
2048
2049 if (not l_profile_defined or l_allow_sync = fnd_oid_util.G_N)
2050 then
2051 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2052 then
2053 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
2054 'value of APPS_SSO_LDAP_SYNC '|| l_allow_sync);
2055 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
2056 'Synch profile is disabled for user ...dont sych '|| p_user_name);
2057 end if;
2058 l_to_synch := FALSE;
2059 else
2060 l_to_synch := TRUE;
2061 end if;
2062 end if;
2063
2064 if (l_to_synch) then
2065
2066 usertype.orclGUID := p_user_guid;
2067 usertype.description := p_description;
2068
2069 if (l_orclisEnabled = fnd_oid_util.G_ENABLED) then
2070 usertype.orclisEnabled := fnd_oid_util.G_ENABLED;
2071 end if;
2072
2073 --l_nickname := fnd_ldap_util.get_orclcommonnicknameattr;
2074 l_nickname := fnd_oid_plug.getNickNameattr(p_user_name);
2075
2076 /** to do - fnd should send us user_name so that we can propagate user_name changes . the logic below should mimic create_user **/
2077
2078 if (upper(l_nickname) <> fnd_ldap_util.G_MAIL) then
2079 usertype.mail := p_email_address;
2080 end if;
2081
2082 if (upper(l_nickname) <> fnd_ldap_util.G_FACSIMILETELEPHONENUMBER) then
2083 usertype.facsimileTelephoneNumber := p_fax;
2084 end if;
2085
2086 x_result := update_user(usertype);
2087
2088 else
2089 x_result := fnd_ldap_util.G_SUCCESS;
2090 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2091 then
2092 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
2093 'User is a local user or synch is disabled for this user.');
2094 end if;
2095 end if;
2096
2097 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2098 then
2099 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
2100 end if;
2101
2102 if x_result <> fnd_ldap_util.G_SUCCESS then
2103 raise no_such_user_exp;
2104 else
2105 fnd_profile.get_specific(
2106 name_z => 'APPS_SSO_LOCAL_LOGIN',
2107 user_id_z => l_user_id,
2108 val_z => l_local_login,
2109 defined_z => l_profile_defined);
2110
2111 if (l_local_login = 'SSO') then
2112 x_password := fnd_web_sec.EXTERNAL_PWD;
2113 end if;
2114 end if;
2115
2116 exception
2117 when no_such_user_exp then
2118 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
2119 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2120 then
2121 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
2122 end if;
2123 x_result := fnd_ldap_util.G_FAILURE;
2124 when others then
2125 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
2126 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2127 then
2128 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
2129 end if;
2130 --x_result := fnd_ldap_util.G_FAILURE;
2131 -- bug 4573677
2132 raise;
2133
2134 end update_user;
2135 --
2136 -------------------------------------------------------------------------------
2137 function update_user(p_ldap_user in fnd_ldap_util.ldap_user_type)
2138 return pls_integer is
2139
2140 l_module_source varchar2(256);
2141 subsNode varchar2(1000);
2142 usersNode varchar2(1000);
2143 usersNodes dbms_ldap.string_collection;
2144 result pls_integer;
2145 retval pls_integer;
2146 num_attributes number;
2147 ldapSession dbms_ldap.session;
2148 modArray dbms_ldap.mod_array;
2149 modvalues dbms_ldap.string_collection;
2150 modmultivalues dbms_ldap.string_collection;
2151 mandatory_att varchar2(256);
2152 modtype varchar2(256);
2153 i number;
2154 break pls_integer;
2155 l_atts dbms_ldap.string_collection;
2156 l_att_values dbms_ldap.string_collection;
2157 dn varchar2(1000);
2158 flag pls_integer;
2159
2160 begin
2161 l_module_source := G_MODULE_SOURCE || 'update_user: ';
2162 -- set default value to failure. change to success when user updated successfully
2163 retval := fnd_ldap_util.G_FAILURE;
2164 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2165 then
2166 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2167 end if;
2168
2169 /* result := user_exists(username => p_ldap_user.sn); */
2173 if (result = fnd_ldap_util.G_SUCCESS) and (p_ldap_user.orclGUID is not null)
2170 result := user_exists_by_guid(p_ldap_user.orclGUID);
2171
2172 -- only proceed if user exists and all mandatory attributes are present
2174 then
2175 ldapSession := fnd_ldap_util.c_get_oid_session(flag);
2176 num_attributes := process_attributes(p_ldap_user, G_UPDATE,
2177 x_atts => l_atts, x_att_values => l_att_values);
2178 if (num_attributes>0)
2179 then
2180 modArray := dbms_ldap.create_mod_array(num => num_attributes);
2181 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2182 then
2183 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' num_attributes='||num_attributes);
2184 end if;
2185
2186 for i in 0..l_atts.count-1 loop
2187 modtype := l_atts(i);
2188 modvalues(0) := l_att_values(i);
2189 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => dbms_ldap.mod_replace, mod_type => modtype, modval => modvalues);
2190 end loop;
2191
2192 retval := update_user_nodes(ldapSession, modArray, p_ldap_user.orclGUID);
2193
2194 dbms_ldap.free_mod_array(modptr => modArray);
2195 -- 7306960
2196 -- fnd_ldap_util.c_unbind(ldapSession,flag);
2197 else
2198 retval :=fnd_ldap_util.G_SUCCESS;
2199 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2200 then
2201 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' num_attributes=0 -> No changes to OiD');
2202 end if;
2203 end if;
2204 -- bug 7306960
2205 fnd_ldap_util.c_unbind(ldapSession,flag);
2206 end if;
2207
2208 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2209 then
2210 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
2211 end if;
2212
2213 return retval;
2214
2215 exception
2216 when others then
2217 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2218 then
2219 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
2220 end if;
2221 raise;
2222
2223 end update_user;
2224 --
2225 -------------------------------------------------------------------------------
2226 function update_user(p_ldap_user in fnd_ldap_util.ldap_user_type, p_expire_password in pls_integer)
2227 return pls_integer is
2228
2229 l_module_source varchar2(256);
2230 subsNode varchar2(1000);
2231 usersNode varchar2(1000);
2232 usersNodes dbms_ldap.string_collection;
2233 result pls_integer;
2234 retval pls_integer;
2235 num_attributes number;
2236 ldapSession dbms_ldap.session;
2237 modArray dbms_ldap.mod_array;
2238 modvalues dbms_ldap.string_collection;
2239 modmultivalues dbms_ldap.string_collection;
2240 mandatory_att varchar2(256);
2241 modtype varchar2(256);
2242 i number;
2243 break pls_integer;
2244 l_atts dbms_ldap.string_collection;
2245 l_att_values dbms_ldap.string_collection;
2246
2247 begin
2248 l_module_source := G_MODULE_SOURCE || 'update_user: ';
2249 -- set default value to failure. change to success when user updated successfully
2250 retval := fnd_ldap_util.G_FAILURE;
2251 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2252 then
2253 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2254 end if;
2255
2256 result := user_exists_by_guid( p_ldap_user.orclGUID);
2257
2258 -- only proceed if user exists and all mandatory attributes are present
2259 if (result = fnd_ldap_util.G_SUCCESS) and (p_ldap_user.orclGUID is not null)
2260 then
2261 -- WARN do not use c_get_oid_session, since proxied session cannot be cached
2262 if (p_expire_password = fnd_ldap_util.G_TRUE) then
2263 ldapSession := fnd_ldap_util.get_oid_session;
2264 else
2265 fnd_ldap_util.proxy_as_user(p_orclguid => p_ldap_user.orclGUID,
2266 x_ldap_session => ldapSession);
2267 end if;
2268
2269 num_attributes := process_attributes(p_ldap_user, G_UPDATE,
2270 x_atts => l_atts, x_att_values => l_att_values);
2271 modArray := dbms_ldap.create_mod_array(num => num_attributes);
2272
2273 for i in 0..l_atts.count-1 loop
2274 modtype := l_atts(i);
2275 modvalues(0) := l_att_values(i);
2276 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => dbms_ldap.mod_replace, mod_type => modtype, modval => modvalues);
2277 end loop;
2278
2279 retval := update_user_nodes(ldapSession, modArray, p_ldap_user.orclGUID);
2280
2281 dbms_ldap.free_mod_array(modptr => modArray);
2282 result := fnd_ldap_util.unbind(ldapSession);
2283 end if;
2284
2285 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2286 then
2287 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
2288 end if;
2289
2290 return retval;
2291
2292 exception
2293 when others then
2294 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2295 then
2296 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
2297 end if;
2298 raise;
2299
2300 end update_user;
2301 --
2302 -------------------------------------------------------------------------------
2306 l_module_source varchar2(256);
2303 function update_user_nodes(p_ldap_session in dbms_ldap.session, p_mod_array in dbms_ldap.mod_array, p_orclguid in raw)
2304 return pls_integer is
2305
2307 usersNode varchar2(1000);
2308 retval pls_integer;
2309 l_message varchar2(200);
2310
2311 begin
2312 l_module_source := G_MODULE_SOURCE || 'update_user_nodes: ';
2313 -- set default value to failure. change to success when user created successfully
2314 retval := fnd_ldap_util.G_FAILURE;
2315 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2316 then
2317 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2318 end if;
2319
2320 usersNode := fnd_ldap_util.get_dn_for_guid(p_orclguid, p_ldap_session);
2321 -- dbms_ldap.use_exception := true;
2322
2323 retval := dbms_ldap.modify_s(ld => p_ldap_session, entrydn => usersNode, modptr => p_mod_array);
2324
2325 if (retval = dbms_ldap.SUCCESS) then
2326 retval := fnd_ldap_util.G_SUCCESS;
2327 end if;
2328
2329 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2330 then
2331 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
2332 end if;
2333 return retval;
2334
2335 exception
2336 when others then
2337 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2338 then
2339 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
2340 end if;
2341 raise;
2342
2343 end update_user_nodes;
2344 function user_exists(p_user_name in varchar2)
2345 return pls_integer is
2346 ldap dbms_ldap.session;
2347 ret pls_integer;
2348 ret2 pls_integer;
2349 flag pls_integer;
2350 begin
2351 ldap := fnd_ldap_util.c_get_oid_session(flag);
2352 ret := user_exists(ldap,p_user_name);
2353 fnd_ldap_util.c_unbind(ldap,flag);
2354 return ret;
2355 end user_exists;
2356 --
2357 -------------------------------------------------------------------------------
2358 function user_exists(ldap in dbms_ldap.session,p_user_name in varchar2)
2359 return pls_integer is
2360
2361 l_module_source varchar2(256);
2362 --result pls_integer;
2363 retval pls_integer;
2364 --l_nickname varchar2(256);
2365
2366
2367 guid raw(16);
2368
2369 begin
2370 l_module_source := G_MODULE_SOURCE || 'user_exists: ';
2371 retval := fnd_ldap_util.G_FAILURE;
2372 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2373 then
2374 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2375 end if;
2376
2377 guid := get_user_guid(p_user_name);
2378 if (guid is not null ) then
2379 retval := fnd_ldap_util.G_SUCCESS;
2380 else
2381 retval :=fnd_ldap_util.G_FAILURE;
2382 end if;
2383
2384
2385 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2386 then
2387 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'retval=' || retval);
2388 end if;
2389
2390 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2391 then
2392 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
2393 end if;
2394
2395 return retval;
2396
2397 exception
2398 when others then
2399 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2400 then
2401 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
2402 end if;
2403 raise;
2404
2405 end user_exists;
2406 --
2407 -------------------------------------------------------------------------------
2408 -- REMOVED
2409 -- function user_exists_with_filter(p_attr_name in varchar2, p_attr_value in varchar2) return pls_integer is
2410
2411 --
2412 -------------------------------------------------------------------------------
2413 /**
2414 * FUNCTION comparePassword: Internal
2415 * Returns true if the password is the same as the stored at OiD for the given DN
2416 * If Not, or any exceptions occurs, returns false
2417 * It can be used repeteadly since this comparision does not count as failed attempts.
2418 * Parameters:
2419 * ldapSession: OiD connection to use
2420 * user_dn: user DN
2421 * p_password: password
2422 **/
2423
2424 function comparePassword(ldapSession in dbms_ldap.session, user_dn in varchar2 , p_password in varchar2) return boolean is
2425 l_result pls_integer;
2426 result boolean;
2427 l_module_source varchar2(256);
2428 begin
2429 l_module_source := G_MODULE_SOURCE || 'comparePassword: ';
2430 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2431 then
2432 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'BEGIN DN:'||user_dn);
2433 end if;
2434 l_result := dbms_ldap.compare_s(ld => ldapSession, dn => user_dn, attr => 'userpassword', value => p_password);
2435 result := l_result= dbms_ldap.COMPARE_TRUE;
2436 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2437 then
2438 if (result) then
2439 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END: Yes');
2440 else
2441 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END: NO');
2442
2443 end if;
2444 end if;
2445
2446 return result;
2447 exception when others then
2451 end if;
2448 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2449 then
2450 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'Exception: '||sqlcode||' - '||sqlerrm);
2452 return false;
2453
2454 end comparePassword;
2455 --
2456 -------------------------------------------------------------------------------
2457 function validate_login(p_user_name in varchar2, p_password in varchar2) return pls_integer is
2458
2459 l_module_source varchar2(256);
2460 l_host varchar2(256);
2461 l_port varchar2(256);
2462
2463 result pls_integer;
2464 retval pls_integer;
2465 l_user_guid raw(256);
2466 l_user_name fnd_user.user_name%type;
2467 l_enabled boolean;
2468 l_ldap_attr_list ldap_attr_list;
2469 ldapSession dbms_ldap.session;
2470 l_retval pls_integer;
2471 user_dn varchar2(4000);
2472 l_ldap_auth varchar2(256);
2473 l_db_wlt_url varchar2(256);
2474 l_db_wlt_pwd varchar2(256);
2475 l_message varchar2(2000);
2476
2477 begin
2478 l_module_source := G_MODULE_SOURCE || 'validate_login: ';
2479
2480 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2481 then
2482 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
2483 end if;
2484
2485
2486 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2487 then
2488 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Username: '||p_user_name);
2489 end if;
2490
2491 if (p_user_name is null or p_password is null ) then
2492 fnd_message.set_name('FND','FND_SSO_USER_PASSWD_EMPTY');
2493 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2494 then
2495 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'END: refusing to validate empty username and/or password');
2496 end if;
2497 return fnd_ldap_util.G_FAILURE;
2498 end if;
2499
2500 -- Find the DN of the linked guid
2501 begin
2502 select user_guid into l_user_guid from fnd_user where user_name=p_user_name;
2503 if (l_user_guid is null ) then
2504 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2505 then
2506 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'END: Null guid in FND_USER for: '||p_user_name);
2507 end if;
2508 fnd_message.set_name('FND','FND_SSO_NOT_LINKED');
2509 return fnd_ldap_util.G_FAILURE;
2510 end if;
2511 exception when no_data_found then
2512 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2513 then
2514 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END: user not found');
2515 end if;
2516 fnd_message.set_name('FND','FND_SSO_LOGIN_FAILED'); -- do no disclusre the real causeL
2517 return fnd_ldap_util.G_FAILURE;
2518 when others then
2519 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2520 then
2521 fnd_log.string(fnd_log.LEVEL_UNEXPECTED ,l_module_source, 'END with exception: '||sqlcode||'-'||sqlerrm);
2522 end if;
2523 fnd_message.set_name('FND','FND-9914'); -- unexpected error
2524 return fnd_ldap_util.G_FAILURE;
2525 end;
2526
2527 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2528 then
2529 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'GUID:'||l_user_guid);
2530 end if;
2531
2532
2533 -- Obtain the user DN using the GUID
2534 begin
2535 user_dn := fnd_Ldap_util.get_dn_for_guid(l_user_guid); -- may raise no data found for invalid guids
2536 exception when no_data_found then
2537 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2538 then
2539 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'Guid['||l_user_guid||'] for '||p_user_name||' is not a valid guid');
2540 end if;
2541 fnd_message.set_name('FND','FND_SSO_USER_NOT_FOUND'); -- Carefull, this is INVALID GUID message, wrong acronym though
2542 return fnd_ldap_util.G_FAILURE;
2543 end;
2544
2545 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2546 then
2547 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'DN:'||user_dn);
2548 end if;
2549
2550
2551
2552 l_host := fnd_preference.get(FND_LDAP_UTIL.G_INTERNAL, FND_LDAP_UTIL.G_LDAP_SYNCH, FND_LDAP_UTIL.G_HOST);
2553 l_port := fnd_preference.get(FND_LDAP_UTIL.G_INTERNAL, FND_LDAP_UTIL.G_LDAP_SYNCH, FND_LDAP_UTIL.G_PORT);
2554
2555 if (l_host is null or l_port is null) then
2556 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2557 then
2558 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'Invalid OiD Setup: host:'||l_host||' port:'||l_port);
2559 end if;
2560
2561 fnd_message.set_name('FND','FND-9903'); -- OID setup is incomplete
2562 return fnd_ldap_util.G_FAILURE;
2563 end if;
2564
2565 l_ldap_auth := fnd_preference.get(FND_LDAP_UTIL.G_INTERNAL, FND_LDAP_UTIL.G_LDAP_SYNCH, FND_LDAP_UTIL.G_DBLDAPAUTHLEVEL);
2566
2567 if (l_ldap_auth>0) then
2568 l_db_wlt_url := fnd_preference.get(FND_LDAP_UTIL.G_INTERNAL, FND_LDAP_UTIL.G_LDAP_SYNCH, FND_LDAP_UTIL.G_DBWALLETDIR);
2572 then
2569 l_db_wlt_pwd := fnd_preference.eget(FND_LDAP_UTIL.G_INTERNAL, FND_LDAP_UTIL.G_LDAP_SYNCH, FND_LDAP_UTIL.G_DBWALLETPASS, FND_LDAP_UTIL.G_LDAP_PWD);
2570 if (l_db_wlt_url is null or l_db_wlt_pwd is null) then
2571 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2573 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'Invalid Wallet Setup: authLEvel:'
2574 ||l_ldap_auth||' url:'||l_db_wlt_url||' pwd:'||l_db_wlt_url);
2575 end if;
2576
2577 fnd_message.set_name('FND','FND-9903'); -- OID setup is incomplete
2578 return fnd_ldap_util.G_FAILURE;
2579 end if;
2580 else
2581 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2582 then
2583 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'WARNING: NON-SSL connection to OiD, check that the Net is secure');
2584 end if;
2585 end if;
2586
2587 dbms_ldap.use_exception := TRUE;
2588
2589 begin
2590 begin
2591 ldapSession := DBMS_LDAP.init(l_host, l_port);
2592 exception when dbms_ldap.init_failed then
2593 if (fnd_log.LEVEL_UNEXPECTED>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2594 then
2595 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'Cannot contact OID (init failed) at '||l_host||':'||l_port||':'||sqlcode||'-'||sqlerrm);
2596 end if;
2597 fnd_message.set_name('FND','FND_SSO_SYSTEM_NOT_AVAIL');
2598 return fnd_ldap_util.G_FAILURE;
2599 when others then
2600 raise;
2601 end;
2602
2603 if (l_ldap_auth>0) then
2604
2605 begin
2606 l_retval := dbms_ldap.open_ssl(ldapSession, 'file:'||l_db_wlt_url, l_db_wlt_pwd, l_ldap_auth);
2607 exception when others then
2608 if (fnd_log.LEVEL_UNEXPECTED>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2609 then
2610 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,' Cannot establish SSL channel to OiD: '||sqlcode||'-'||sqlerrm);
2611 end if;
2612
2613 fnd_message.set_name('FND','FND_SSO_INV_AUTH_MODE'); -- Invalid SSL authcode... it is enouggh description
2614 return fnd_ldap_util.G_FAILURE;
2615 end;
2616
2617 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2618 then
2619 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Excellent!! Using SSL to contact OiD');
2620 end if;
2621 end if;
2622
2623
2624 l_retval := dbms_ldap.simple_bind_s(ldapSession, user_dn , p_password);
2625
2626 -- we do analyze in extense the possible DBMS_LDAP exceptions to return accurate messages
2627
2628
2629 exception
2630 when dbms_ldap.general_error then
2631 -- here comes the explanation
2632 l_message := sqlerrm;
2633 -- first we check if the password is real,
2634
2635 if (instr(l_message,':9000:')>0 )then
2636 fnd_message.set_name('FND','FND_SSO_PASSWORD_EXPIRED'); --Your account is locked
2637 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2638 then
2639 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD account password expired ');
2640 end if;
2641 elsif (instr(l_message,':9001:')>0 )then
2642 fnd_message.set_name('FND','FND_SSO_LOCKED'); --Your account is locked
2643 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2644 then
2645 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD account locked');
2646 end if;
2647
2648 else
2649 if (comparePassword(ldapSession, user_dn , p_password) )then
2650 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2651 then
2652 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD password match but ..');
2653 end if;
2654 if (instr(l_message,':9050:')>0) then
2655 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2656 then
2657 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD account is disabled');
2658 end if;
2659 fnd_message.set_name('FND','FND_SSO_USER_DISABLED'); --Your account is disabled
2660 elsif (instr(l_message,':9053:')>0) then
2661 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2662 then
2663 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD account is not active: today is out of [start,end] dates ');
2664 end if;
2665 fnd_message.set_name('FND','FND_SSO_NOT_ACTIVE'); --Your account not active. Either past end_date or future start_date
2666 else --unknown reason
2667 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2668 then
2669 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'cannot bind because:'||l_message);
2670 end if;
2671 -- maybe is not the reason, but it is enough for return , I guess
2672 fnd_message.set_name('FND','FND_APPL_LOGIN_FAILED'); -- invalid username password
2673 end if;
2674
2675 else
2676 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2677 then
2681 fnd_message.set_name('FND','FND_APPL_LOGIN_FAILED'); -- invalid username password
2678 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD password did not match');
2679 end if;
2680 -- maybe is not the reason, but it is enough for return , I guess
2682 end if;
2683 end if;
2684 if (fnd_log.LEVEL_PROCEDURE>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2685 then
2686 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END: bind error: '||l_message);
2687 end if;
2688 return fnd_ldap_util.G_FAILURE;
2689 when others then
2690 if (fnd_log.LEVEL_UNEXPECTED>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2691 then
2692 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'END: unexpected'||l_message);
2693 end if;
2694 return fnd_ldap_util.G_FAILURE;
2695 end;
2696
2697
2698 l_retval:= dbms_ldap.unbind_s(ldapSession);
2699 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2700 then
2701 fnd_log.string(fnd_log.LEVEL_PROCEDURE , l_module_source, 'END: Valid Username/password');
2702 end if;
2703 return fnd_ldap_util.G_SUCCESS;
2704
2705 exception when others then
2706 if (fnd_log.LEVEL_UNEXPECTED>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2707 then
2708 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'END: unexpected '||sqlcode||' - '||sqlerrm);
2709 end if;
2710 fnd_message.set_name('FND','FND-9914'); -- unexpected error
2711 return fnd_ldap_util.G_FAILURE;
2712 end validate_login;
2713
2714 --
2715 -------------------------------------------------------------------------------
2716
2717 end fnd_ldap_user;