1 package body FND_USER_PKG as
2 /* $Header: AFSCUSRB.pls 120.48.12010000.3 2009/01/16 22:51:42 fskinner ship $ */
3
4 /* START PARTY */
5 C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_USER_PKG';
6 C_LOG_HEAD CONSTANT VARCHAR2(30) := 'fnd.plsql.FND_USER_PKG.';
7
8 /* One level cache for derive_person_party_id */
9 Z_EMPLOYEE_ID NUMBER := NULL;
10 Z_CUSTOMER_ID NUMBER := NULL;
11 Z_PERSON_PARTY_ID NUMBER := NULL;
12
13 /* One level cache for derive_customer_and_or_emp */
14 Z_REV_EMPLOYEE_ID NUMBER := NULL;
15 Z_REV_CUSTOMER_ID NUMBER := NULL;
16 Z_REV_PERSON_PARTY_ID NUMBER := NULL;
17 /* END PARTY */
18
19 /* bug 2504562 */
20 g_old_user_name varchar2(100) := NULL;
21
22 /* bug 4318754, 4352995 SSO related changes */
23 g_old_user_guid RAW(16) := NULL;
24 g_old_person_party_id NUMBER := NULL;
25
26 ----------------------------------------------------------------------------
27 --
28 -- boolRet (INTERNAL)
29 -- Translate Y/N to boolean
30 --
31 function boolRet(ret varchar2) return boolean is
32 begin
33 if (ret = 'Y') then
34 return TRUE;
35 end if;
36 return FALSE;
37 end;
38
39 ---------------------------------------------------------------------------
40 -- ldap_wrapper_create_user (INTERNAL)
41 --
42 procedure ldap_wrapper_create_user(x_user_name in varchar2,
43 x_unencrypted_password in varchar2,
44 x_start_date in date,
45 x_end_date in date,
46 x_description in varchar2,
47 x_email_address in varchar2,
48 x_fax in varchar2,
49 x_expire_pwd in pls_integer,
50 x_user_guid in out nocopy raw,
51 x_oid_pwd in out nocopy varchar2) is
52 l_result pls_integer;
53 reason varchar2(2000);
54 l_pwd varchar2(100);
55 pwdCaseOpt varchar2(1);
56 begin
57
58 l_result := null;
59
60 -- Bug 5161497
61 l_pwd := x_unencrypted_password;
62 pwdCaseOpt := null;
63 -- Bug 5162136 Note: Creating user so use SITE level profile value.
64 pwdCaseOpt := fnd_profile.value('SIGNON_PASSWORD_CASE');
65 if (pwdCaseOpt is null) or (pwdCaseOpt = '1') then
66 l_pwd := lower(x_unencrypted_password);
67 end if;
68
69 -- Bug: 5375111
70 -- Calling ldap_wrapper_wrapper with the new expire_pwd flag.
71 fnd_ldap_wrapper.create_user(x_user_name, l_pwd, x_start_date, x_end_date,
72 x_description, x_email_address, x_fax, x_expire_pwd, x_user_guid,
73 x_oid_pwd, l_result);
74
75 if (l_result <> fnd_ldap_wrapper.G_SUCCESS) then
76 reason := fnd_message.get;
77 fnd_message.set_name('FND', 'LDAP_WRAPPER_CREATE_USER_FAIL');
78 fnd_message.set_token('USER_NAME', x_user_name);
79 fnd_message.set_token('REASON', reason);
80 app_exception.raise_exception;
81 end if;
82
83 exception
84 when others then
85 fnd_message.set_name('FND', 'LDAP_WRAPPER_CREATE_USER_FAIL');
86 fnd_message.set_token('USER_NAME', x_user_name);
87 fnd_message.set_token('REASON', sqlerrm);
88 app_exception.raise_exception;
89 end;
90
91 ----------------------------------------------------------------------^M
92 --
93 -- UpdateUserInternal (PRIVATE)
94 -- Internal api for UpdateUser and UpdateUserParty.
95 -- Not exposed publicly, use UpdateUser or UpdateUserParty wrappers.
96 --
97 procedure UpdateUserInternal (
98 x_user_name in varchar2,
99 x_owner in varchar2,
100 x_unencrypted_password in varchar2,
101 x_session_number in number,
102 x_start_date in date,
103 x_end_date in date,
104 x_last_logon_date in date,
105 x_description in varchar2,
106 x_password_date in date,
107 x_password_accesses_left in number,
108 x_password_lifespan_accesses in number,
109 x_password_lifespan_days in number,
110 x_employee_id in number,
111 x_email_address in varchar2,
112 x_fax in varchar2,
113 x_customer_id in number,
114 x_supplier_id in number,
115 x_person_party_id in number,
116 x_old_password in varchar2,
117 x_mode in varchar2,
118 x_last_update_date in date default sysdate,
119 x_user_guid in raw default null,
120 x_change_source in number default null)
121 is
122 l_api_name varchar2(30) := 'UPDATEUSERINTERNAL';
123 owner_id number := 0;
124 ret varchar2(1);
125 reason varchar2(32000);
126 l_session_number number;
127 l_start_date date;
128 l_end_date date;
129 l_last_logon_date date;
130 l_description varchar2(240);
131 l_password_date date;
132 l_password_accesses_left number;
133 l_password_lifespan_accesses number;
134 l_password_lifespan_days number;
135 l_employee_id number;
136 l_email_address varchar2(240);
137 l_fax varchar2(80);
138 l_customer_id number;
139 l_supplier_id number;
140 l_person_party_id number;
141 -- Added for Function Security Cache Invalidation
142 l_user_id number;
143 l_user_guid raw(16);
144
145 -- This cursor was added to fix Bug#3663908
146 l_old_customer_id number;
147 l_old_employee_id number;
148 l_old_person_party_id number;
149 l_old_user_guid RAW(16);
150
151 cursor usercur is
152 select customer_id, employee_id, person_party_id, user_guid
153 from fnd_user
154 where user_name = upper(x_user_name);
155
156 -- Bug 5235329
157 l_expire_pwd pls_integer;
158
159 begin
160 -- Bug 7687370
161 if (x_owner is null) then
162 owner_id := fnd_global.user_id;
163 else
164 owner_id := fnd_load_util.owner_id(x_owner);
165 end if;
166
167 -- This was added to fix Bug#3663908
168 open usercur;
169 fetch usercur into l_old_customer_id, l_old_employee_id,
170 l_old_person_party_id, l_old_user_guid;
171 close usercur;
172
173 -- bug 4318754 for SSO stuff
174 g_old_person_party_id := l_old_person_party_id;
175 g_old_user_guid := l_old_user_guid;
176 -- end bug 4318754
177
178 -- Bug4680643 get values for start_date and end_date from the database.
179
180 select start_date, end_date
181 into l_start_date, l_end_date
182 from fnd_user
183 where user_name = upper(x_user_name);
184
185 -- Bug4680643 Determine and set the value of start_date and end_date
186 -- outside of the decode statement which was truncating the actual
187 -- value when it is already set.
188
189 if (x_start_date = fnd_user_pkg.null_date) then
190 l_start_date := null;
191 elsif (x_start_date is not null) then
192 l_start_date := x_start_date;
193 end if;
194 -- Bug 4901996. Truncate the time stamp
195 l_start_date := trunc(l_start_date);
196
197 if (x_end_date = fnd_user_pkg.null_date) then
198 l_end_date := null;
199 elsif (x_end_date is not null) then
200 l_end_date := x_end_date;
201 end if;
202 -- Bug 4901996. Truncate the time stamp
203 l_end_date := trunc(l_end_date);
204
205 -- Translate *NULL* parameter values into real nulls,
206 -- treat null values as no-change.
207 begin
208 select decode(x_session_number, fnd_user_pkg.null_number, null,
209 null, u.session_number,
210 x_session_number),
211 -- bug 6608790 this preserves timestamp for iRecruitement
212 decode(x_last_logon_date, fnd_user_pkg.null_date, to_date(null),
213 to_date(null), u.last_logon_date,
214 x_last_logon_date),
215 decode(x_description, fnd_user_pkg.null_char, null,
216 null, u.description,
217 x_description),
218 decode(x_password_date, fnd_user_pkg.null_date, null,
219 null, u.password_date,
220 x_password_date),
221 decode(x_password_accesses_left, fnd_user_pkg.null_number, null,
222 null, u.password_accesses_left,
223 x_password_accesses_left),
224 decode(x_password_lifespan_accesses, fnd_user_pkg.null_number, null,
225 null, u.password_lifespan_accesses,
226 x_password_lifespan_accesses),
227 decode(x_password_lifespan_days, fnd_user_pkg.null_number, null,
228 null, u.password_lifespan_days,
229 x_password_lifespan_days),
230 decode(x_employee_id, fnd_user_pkg.null_number, null,
231 null, u.employee_id,
232 x_employee_id),
233 decode(x_email_address, fnd_user_pkg.null_char, null,
234 null, u.email_address,
235 x_email_address),
236 decode(x_fax, fnd_user_pkg.null_char, null,
237 null, u.fax,
238 x_fax),
239 decode(x_customer_id, fnd_user_pkg.null_number, null,
240 null, u.customer_id,
241 x_customer_id),
242 decode(x_supplier_id, fnd_user_pkg.null_number, null,
243 null, u.supplier_id,
244 x_supplier_id),
245 decode(x_person_party_id, fnd_user_pkg.null_number, null,
246 null, u.person_party_id,
247 x_person_party_id),
248 decode(x_user_guid, fnd_user_pkg.null_raw, null,
249 null, u.user_guid,
250 x_user_guid )
251 into l_session_number,
252 l_last_logon_date, l_description, l_password_date,
253 l_password_accesses_left, l_password_lifespan_accesses,
254 l_password_lifespan_days, l_employee_id,
255 l_email_address, l_fax, l_customer_id, l_supplier_id,
256 l_person_party_id , l_user_guid
257 from fnd_user u
258 where u.user_name = upper(x_user_name);
259 exception
260 when others then
261 fnd_message.set_name('FND', 'FND_INVALID_USER');
262 fnd_message.set_token('USER_NAME', X_USER_NAME);
263 app_exception.raise_exception;
264 end;
265
266 -- PARTY
267 if (x_mode = 'PARTY') then
268 -- Called from UpdateUserParty
269 -- Derive customer/employee_ids from party_id
270 -- This was added to fix Bug#3663908
271 if (nvl(l_old_person_party_id, 0) <> nvl(l_person_party_id, 0)) then
272 fnd_user_pkg.derive_customer_employee_id(x_user_name, l_person_party_id,
273 l_customer_id, l_employee_id);
274 end if;
275 else
276 -- Called from UpdateUser
277 -- Derive the party_id from the customer/employee_ids.
278 -- This was added to fix Bug#3663908
279 if (nvl(l_old_customer_id, 0) <> nvl(l_customer_id, 0)) or (nvl(l_old_employee_id, 0) <> nvl(l_employee_id, 0)) then
280 l_person_party_id := fnd_user_pkg.derive_person_party_id(
281 x_user_name, l_customer_id, l_employee_id);
282 end if;
283 end if;
284
285
286
287 SAVEPOINT update_user;
288
289 if (x_unencrypted_password is not null) then
290
291 -- If old_password is provided, change the password of an
292 -- applications user after verifying the existing password. This
293 -- change is being added for bug 2658982 and 1722166.
294 if (x_old_password is not null) then
295 -- Add fifth argument to not use autonomous transaction when chaning
296 -- passowrd. This is for bug 5087728
297 ret := fnd_web_sec.change_password(x_user_name,
298 x_old_password,
299 x_unencrypted_password,
300 x_unencrypted_password,
301 FALSE);
302 -- Otherwise, change the password of an applications user without
303 -- verifying the existing password
304 else
305 -- Add third argument to not use autonomous transaction when chaning
306 -- passowrd. This is for bug 5087728
307 ret := fnd_web_sec.change_password(x_user_name,
308 x_unencrypted_password, FALSE);
309 -- Bug 5355566/5840007 reset unsuccessful logins start date since
310 -- password is new and login with this password has not yet occurred.
311 if (x_last_logon_date is null) then
312 l_last_logon_date := sysdate;
313 end if;
314
315 end if;
316
317 if (ret = 'N') then
318 reason := fnd_message.get();
319 fnd_message.set_name('FND', 'FND_CHANGE_PASSWORD_FAILED');
320 fnd_message.set_token('USER_NAME', X_USER_NAME);
321 fnd_message.set_token('REASON', reason);
322 app_exception.raise_exception;
323 end if;
324 end if;
325
326 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
327 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
328 c_log_head || l_api_name || '.before_upduser',
329 'UpdateUser(): before updating user');
330 end if;
331
332 -- Update all of the non-password columns
333 update fnd_user set
334 last_update_date = x_last_update_date,
335 last_updated_by = owner_id,
336 last_update_login = owner_id,
337 session_number = l_session_number,
338 start_date = l_start_date,
339 end_date = l_end_date,
340 last_logon_date = l_last_logon_date,
341 description = l_description,
342 password_date = l_password_date,
343 password_accesses_left = l_password_accesses_left,
344 password_lifespan_accesses = l_password_lifespan_accesses,
345 password_lifespan_days = l_password_lifespan_days,
346 employee_id = l_employee_id,
347 email_address = l_email_address,
348 fax = l_fax,
349 customer_id = l_customer_id,
350 supplier_id = l_supplier_id,
351 person_party_id = l_person_party_id,
352 user_guid = l_user_guid
353 where user_name = upper(x_user_name);
354
355 if (SQL%NOTFOUND) then
356 fnd_message.set_name('FND', 'FND_INVALID_USER');
357 fnd_message.set_token('USER_NAME', X_USER_NAME);
358 app_exception.raise_exception;
359 else
360 -- Added for Function Security Cache Invalidation
361 select user_id into l_user_id
362 from fnd_user
363 where user_name = upper(x_user_name);
364
365 fnd_function_security_cache.update_user(l_user_id);
366 end if;
367
368
369 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
370 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
371 'Start calling ldap_wrapper_update_user');
372 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
373 'x_user_name = '||x_user_name);
374 end if;
375
376 -- Enhancement 5027812
377 if (x_change_source is null or
378 x_change_source <> fnd_user_pkg.change_source_oid ) then
379 begin
380 -- 5235329, 5375111 Let ldap/oid know whether to expire password
381 -- But only if there is a new password.
382 if (x_unencrypted_password is not null and
383 x_unencrypted_password <> FND_WEB_SEC.EXTERNAL_PWD) then
384 begin
385 select fnd_ldap_wrapper.G_TRUE
386 into l_expire_pwd
387 from fnd_user
388 where user_name = upper(x_user_name)
389 and (PASSWORD_DATE is NULL or
390 (PASSWORD_LIFESPAN_ACCESSES is not NULL and
391 nvl(PASSWORD_ACCESSES_LEFT, 0) < 1) or
392 (PASSWORD_LIFESPAN_DAYS is not NULL and
393 SYSDATE >= PASSWORD_DATE + PASSWORD_LIFESPAN_DAYS));
394 exception
395 when no_data_found then
396 l_expire_pwd := fnd_ldap_wrapper.G_FALSE;
397 end;
398 end if;
399 -- end 5235329, 5375111
400 ldap_wrapper_update_user(upper(x_user_name), x_unencrypted_password,
401 x_start_date, x_end_date, x_description,
402 x_email_address, x_fax, l_expire_pwd);
403 exception
404 when others then
405 ROLLBACK to update_user;
406 reason := fnd_message.get();
407 fnd_message.set_name('FND', 'FND_UPDATE_USER_FAILED');
408 fnd_message.set_token('USER_NAME', X_USER_NAME);
409 fnd_message.set_token('REASON', reason);
410 app_exception.raise_exception;
411 end;
412 end if;
413 -- End bug 4318754, 4424225
414
415 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
416 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
417 'After calling ldap_wrapper_update_user');
418 end if;
419
420 -- Sync user change to LDAP
421 fnd_user_pkg.user_synch(x_user_name);
422
423 end UpdateUserInternal;
424
425 ----------------------------------------------------------------------
426 --
427 -- LOAD_ROW (PRIVATE)
428 -- Overloaded version for backward compatibility only.
429 -- Use version below.
430 --
431 procedure LOAD_ROW (
432 X_USER_NAME in VARCHAR2,
433 X_OWNER in VARCHAR2,
434 X_ENCRYPTED_USER_PASSWORD in VARCHAR2,
435 X_SESSION_NUMBER in VARCHAR2,
436 X_START_DATE in VARCHAR2,
437 X_END_DATE in VARCHAR2,
438 X_LAST_LOGON_DATE in VARCHAR2,
439 X_DESCRIPTION in VARCHAR2,
440 X_PASSWORD_DATE in VARCHAR2,
441 X_PASSWORD_ACCESSES_LEFT in VARCHAR2,
442 X_PASSWORD_LIFESPAN_ACCESSES in VARCHAR2,
443 X_PASSWORD_LIFESPAN_DAYS in VARCHAR2,
444 X_EMAIL_ADDRESS in VARCHAR2,
445 X_FAX in VARCHAR2 ) is
446
447 begin
448 fnd_user_pkg.LOAD_ROW (
449 X_USER_NAME=>X_USER_NAME,
450 X_OWNER=> X_OWNER,
451 X_ENCRYPTED_USER_PASSWORD=>X_ENCRYPTED_USER_PASSWORD,
452 X_SESSION_NUMBER=> X_SESSION_NUMBER,
453 X_START_DATE=> X_START_DATE,
454 X_END_DATE=> X_END_DATE,
455 X_LAST_LOGON_DATE=> X_LAST_LOGON_DATE,
456 X_DESCRIPTION=> X_DESCRIPTION,
457 X_PASSWORD_DATE=> X_PASSWORD_DATE,
458 X_PASSWORD_ACCESSES_LEFT=> X_PASSWORD_ACCESSES_LEFT,
459 X_PASSWORD_LIFESPAN_ACCESSES=> X_PASSWORD_LIFESPAN_ACCESSES,
460 X_PASSWORD_LIFESPAN_DAYS=> X_PASSWORD_LIFESPAN_DAYS,
461 X_EMAIL_ADDRESS=> X_EMAIL_ADDRESS,
462 X_FAX=> X_FAX,
463 X_CUSTOM_MODE=> '',
464 X_LAST_UPDATE_DATE=> '');
465
466 end LOAD_ROW;
467
468 ----------------------------------------------------------------------
469 --
470 -- LOAD_ROW (PRIVATE)
471 -- Insert/update a new row of data.
472 -- Only for use by FNDLOAD, other apis should use LoadUser below.
473 --
474 procedure LOAD_ROW (
475 X_USER_NAME in VARCHAR2,
476 X_OWNER in VARCHAR2,
477 X_ENCRYPTED_USER_PASSWORD in VARCHAR2,
478 X_SESSION_NUMBER in VARCHAR2,
479 X_START_DATE in VARCHAR2,
480 X_END_DATE in VARCHAR2,
481 X_LAST_LOGON_DATE in VARCHAR2,
482 X_DESCRIPTION in VARCHAR2,
483 X_PASSWORD_DATE in VARCHAR2,
484 X_PASSWORD_ACCESSES_LEFT in VARCHAR2,
485 X_PASSWORD_LIFESPAN_ACCESSES in VARCHAR2,
486 X_PASSWORD_LIFESPAN_DAYS in VARCHAR2,
487 X_EMAIL_ADDRESS in VARCHAR2,
488 X_FAX in VARCHAR2,
489 X_CUSTOM_MODE in VARCHAR2,
490 X_LAST_UPDATE_DATE in VARCHAR2,
491 X_PERSON_PARTY_NAME in VARCHAR2 default NULL) is
492
493 owner_id number := 0;
494 ret boolean;
495 f_luby number; -- entity owner in file
496 f_ludate date; -- entity update date in file
497 db_luby number; -- entity owner in db
498 db_ludate date; -- entity update date in db
499 l_end_date date;
500 l_last_logon_date date;
501 l_password_date date;
502 l_password_accesses_left number;
503 l_password_lifespan_accesses number;
504 l_password_lifespan_days number;
505 l_person_party_id number;
506 l_party_type varchar2(30);
507
508 -- simple local proc to save redunant code - added for bug 3254311
509 PROCEDURE DoPassword_update( puser_name in varchar2, pcrypt_pass in varchar2 ) is
510 encPwd varchar2(100);
511 BEGIN
512 -- The insert/update didn't include the password, because
513 -- those apis can't decrypt it.
514 -- Set it directly now.
515
516 -- NULL password means no update -- bug 7687370
517 if (pcrypt_pass is null) then
518 return;
519 end if;
520
521 -- bug 4047740 - as a byproduct of FND_WEB_SEC.set_reencrypted_password
522 -- NOT calling change_password we need this check to complete the checks
523 -- below as in FND_WEB_SEC - also added update to both encrypted rows for
524 -- the fnd_user update below for completeness.
525 if(false = fnd_sso_manager.isPasswordChangeable(puser_name)) then
526 encPwd := 'EXTERNAL';
527 else
528 encPwd := pcrypt_pass;
529 end if;
530
531 if (encPwd in ('EXTERNAL', 'INVALID')) then
532 -- The password was 'EXTERNAL' or 'INVALID', just set it directly
533 -- without trying to re-encrypt
534 update fnd_user
535 set encrypted_foundation_password = encPwd,
536 encrypted_user_password = encPwd
537 where user_name = puser_name;
538
539 -- print warning in log file if it was 'INVALID'
540 if (encPwd = 'INVALID') then
541 fnd_file.put_line(fnd_file.Log,'Invalid password for user ' ||puser_name );
542 end if;
543 else
544 ret := fnd_user_pkg.SetReEncryptedPassword(puser_name, encPwd, 'LOADER');
545 end if;
546 END;
547
548 begin
549 -- Translate owner to file_last_updated_by
550 f_luby := fnd_load_util.owner_id(x_owner);
551
552 -- Translate char last_update_date to date
553 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
554
555 /*Bug2896887 - Modified code to analyze and set up the correct value
556 for the nullable parameters to be passed to the
557 UpdateUser and CreateUser procedures. */
558
559 if (X_END_DATE = fnd_user_pkg.null_char) then
560 l_end_date := fnd_user_pkg.null_date;
561 else
562 l_end_date := to_date(X_END_DATE, 'YYYY/MM/DD');
563 end if;
564
565 if (X_PASSWORD_DATE = fnd_user_pkg.null_char) then
566 l_password_date := fnd_user_pkg.null_date;
567 else
568 l_password_date := to_date(X_PASSWORD_DATE, 'YYYY/MM/DD');
569 end if;
570
571 if (X_LAST_LOGON_DATE = fnd_user_pkg.null_char) then
572 l_last_logon_date := fnd_user_pkg.null_date;
573 else
574 l_last_logon_date := to_date(X_LAST_LOGON_DATE, 'YYYY/MM/DD');
575 end if;
576
577 if (X_PASSWORD_ACCESSES_LEFT = fnd_user_pkg.null_char) then
578 l_password_accesses_left := fnd_user_pkg.null_number;
579 else
580 l_password_accesses_left := to_number(X_PASSWORD_ACCESSES_LEFT);
581 end if;
582
583 if (X_PASSWORD_LIFESPAN_ACCESSES = fnd_user_pkg.null_char) then
584 l_password_lifespan_accesses := fnd_user_pkg.null_number;
585 else
586 l_password_lifespan_accesses := to_number(X_PASSWORD_LIFESPAN_ACCESSES);
587 end if;
588
589 if (X_PASSWORD_LIFESPAN_DAYS = fnd_user_pkg.null_char) then
590 l_password_lifespan_days := fnd_user_pkg.null_number;
591 else
592 l_password_lifespan_days := to_number(X_PASSWORD_LIFESPAN_DAYS);
593 end if;
594
595 begin
596 select LAST_UPDATED_BY, LAST_UPDATE_DATE
597 into db_luby, db_ludate
598 from FND_USER
599 where USER_NAME = X_USER_NAME;
600
601 /* PARTY */
602 l_person_party_id := null;
603 begin
604 FND_OAM_USER_INFO.HZ_PARTY_NAME_TO_ID(x_person_party_name,
605 l_person_party_id,
606 l_party_type);
607 exception
608 when no_data_found then
609 l_person_party_id := null;
610 end;
611
612 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
613 db_ludate, X_CUSTOM_MODE)) then
614 UpdateUserInternal(
615 x_user_name => x_user_name,
616 x_owner => x_owner, -- bug 7687370
617 x_unencrypted_password => '',
618 x_session_number => to_number(x_session_number),
619 x_start_date => to_date(X_START_DATE, 'YYYY/MM/DD'),
620 x_end_date => L_END_DATE,
621 x_last_logon_date => L_LAST_LOGON_DATE,
622 x_description => X_DESCRIPTION,
623 x_password_date => L_PASSWORD_DATE,
624 x_password_accesses_left => L_PASSWORD_ACCESSES_LEFT,
625 x_password_lifespan_accesses => L_PASSWORD_LIFESPAN_ACCESSES,
626 x_password_lifespan_days => L_PASSWORD_LIFESPAN_DAYS,
627 x_employee_id => null,
628 x_email_address => x_email_address,
629 x_fax => x_fax,
630 x_customer_id => null,
631 x_supplier_id => null,
632 x_person_party_id => l_person_party_id,
633 x_old_password => null,
634 x_mode => 'EMPLOYEE',
635 x_last_update_date => f_ludate);
636
637 -- added for bug 3254311 by mskees
638 DoPassword_update(X_USER_NAME, X_ENCRYPTED_USER_PASSWORD);
639 end if;
640 exception
641 when no_data_found then
642 -- bug 4047740 changed dummy password from 'welcome' to new FND_WEB_SEC
643 -- constant, requires AFSCJAVS.pls 115.27 and AFSCJAVB.pls 115.63
644 fnd_user_pkg.createuser(
645 X_USER_NAME,
646 X_OWNER, -- bug 7687370
647 FND_WEB_SEC.INVALID_PWD,
648 to_number(X_SESSION_NUMBER),
649 to_date(X_START_DATE, 'YYYY/MM/DD'),
650 L_END_DATE,
651 L_LAST_LOGON_DATE,
652 X_DESCRIPTION,
653 L_PASSWORD_DATE,
654 L_PASSWORD_ACCESSES_LEFT,
655 L_PASSWORD_LIFESPAN_ACCESSES,
656 L_PASSWORD_LIFESPAN_DAYS,
657 null,
658 X_EMAIL_ADDRESS,
659 X_FAX,
660 null,
661 null);
662 -- added for bug 3254311 by mskees
663 DoPassword_update(X_USER_NAME, X_ENCRYPTED_USER_PASSWORD);
664 end;
665
666
667 end LOAD_ROW;
668
669 ----------------------------------------------------------------------
670 --
671 -- CreateUserIdInternal (PRIVATE)
672 -- Internal wrapper for CreateUserId and CreateUserIdParty
673 --
674 function CreateUserIdInternal (
675 x_user_name in varchar2,
676 x_owner in varchar2,
677 x_unencrypted_password in varchar2,
678 x_session_number in number,
679 x_start_date in date,
680 x_end_date in date,
681 x_last_logon_date in date,
682 x_description in varchar2,
683 x_password_date in date,
684 x_password_accesses_left in number,
685 x_password_lifespan_accesses in number,
686 x_password_lifespan_days in number,
687 x_employee_id in number,
688 x_email_address in varchar2,
689 x_fax in varchar2,
690 x_customer_id in number,
691 x_supplier_id in number,
692 x_person_party_id in number,
693 x_mode in varchar2,
694 x_user_guid in raw default null,
695 x_change_source in number default null)
696 return number is
697 l_api_name varchar2(30):= 'CREATEUSERIDINTERNAL';
698 owner_id number := 0;
699 user_id number;
700 ret varchar2(1) := 'N';
701 reason varchar2(32000);
702
703 pwd varchar2(100); /* 4351689 */
704 l_user_guid raw(16);
705 l_oid_pwd varchar2(30);
706
707 l_expire_pwd pls_integer;
708
709 begin
710 -- Bug 7687370
711 if (x_owner is null) then
712 owner_id := fnd_global.user_id;
713 else
714 owner_id := fnd_load_util.owner_id(x_owner);
715 end if;
716
717 begin
718 -- Before creating the user, we should validate the user_name
719 -- to make sure that there is no special characters.
720 validate_user_name(x_user_name);
721
722 /* bug 4351689 - allow the create functions to pass in null for
723 External password control*/
724 pwd := nvl( x_unencrypted_password, FND_WEB_SEC.EXTERNAL_PWD );
725
726 SAVEPOINT create_user;
727
728 ret := fnd_web_sec.create_user(x_user_name,pwd,user_id);
729
730 if (ret = 'Y') then
731 -- Enhancement 5027812
732 if ((x_change_source is null or
733 x_change_source <> fnd_user_pkg.CHANGE_SOURCE_OID) and
734 pwd <> fnd_web_sec.external_pwd) then
735 -- We need to translate the external constant to null. Otherwise
736 -- fnd_ldap_wrapper.create_user will use that constant as real password.
737 if (pwd = fnd_web_sec.external_pwd) then
738 pwd := '';
739 end if;
740
741 -- Bug#4118749 - Create user in OID
742 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
743 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
744 'Start calling ldap_wrapper_create_user');
745 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
746 'x_user_name = '||x_user_name);
747 end if;
748
749 begin
750 -- 5235329, 5375111 Let ldap/oid know whether to expire password
751 if (x_password_date is null or
752 (x_password_lifespan_accesses is not null and
753 nvl(x_password_accesses_left, 0) < 1) or
754 (x_password_lifespan_days is not null and
755 sysdate >= x_password_date + x_password_lifespan_days)) then
756 l_expire_pwd := fnd_ldap_wrapper.G_TRUE;
757 else
758 l_expire_pwd := fnd_ldap_wrapper.G_FALSE;
759 end if;
760 -- end 5235329, 5375111
761
762 -- Begin Bug 4318754, 4424225
763 ldap_wrapper_create_user(upper(x_user_name), pwd, x_start_date,
764 x_end_date, x_description, x_email_address,
765 x_fax, l_expire_pwd, l_user_guid, l_oid_pwd);
766 -- If wrapper gives back EXTERNAL password, we need to update
767 -- the user with external password. l_oid_pwd will be used later
768 -- in the UpdateUser call. setting to null means no change.
769 if (l_oid_pwd <> fnd_web_sec.external_pwd) then
770 l_oid_pwd := '';
771 end if;
772 -- End Bug 4318754, 4424225
773 exception
774 when others then
775 ROLLBACK to create_user;
776 reason := fnd_message.get();
777 fnd_message.set_name('FND', 'FND_CREATE_USER_FAILED');
778 fnd_message.set_token('USER_NAME', X_USER_NAME);
779 fnd_message.set_token('REASON', reason);
780 app_exception.raise_exception;
781 end;
782
783 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
784 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
785 'After calling ldap_wrapper_create_user');
786 end if;
787 else
788 l_user_guid := x_user_guid;
789 end if;
790
791 -- the createuser java code uses the user_id as created_by which
792 -- is not correct. We must correct here.
793 update fnd_user
794 set created_by = owner_id
795 where user_name = upper(x_user_name);
796
797 -- update the rest of the data except password
798 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
799 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
800 c_log_head || l_api_name || '.call_upduser',
801 'CreateUser(): calling fnd_user_pkg.UpdateUser');
802 end if;
803
804 -- NOTE: This bit is using UpdateUser (rather than direct sql update)
805 -- so that the user synch code in UpdateUser is triggered.
806 -- Don't change this without taking that into account.
807 --
808 if (x_mode = 'PARTY') then
809 -- fnd_user_pkg.UpdateUserParty(
810 -- Begin bug 4318754, 4424225
811 -- In order to handle user_guid, calls UpdateUserInternal directly
812 -- instead of UpdateUserParty which does not take user_guid as argument.
813 fnd_user_pkg.UpdateUserInternal(
814 -- End bug 4318754, 4424225
815 x_user_name => x_user_name,
816 x_owner => x_owner,
817 x_unencrypted_password => l_oid_pwd,
818 x_session_number => x_session_number,
819 x_start_date => x_start_date,
820 x_end_date => x_end_date,
821 x_last_logon_date => x_last_logon_date,
822 x_description => x_description,
823 x_password_date => x_password_date,
824 x_password_accesses_left => x_password_accesses_left,
825 x_password_lifespan_accesses => x_password_lifespan_accesses,
826 x_password_lifespan_days => x_password_lifespan_days,
827 x_employee_id => null,
828 x_email_address => x_email_address,
829 x_fax => x_fax,
830 x_customer_id => null,
831 x_supplier_id => null,
832 x_person_party_id => x_person_party_id,
833 x_old_password => null,
834 x_mode => 'PARTY',
835 x_user_guid => l_user_guid,
836 x_change_source => fnd_user_pkg.change_source_oid);
837 else
838 fnd_user_pkg.UpdateUser(
839 x_user_name => x_user_name,
840 x_owner => x_owner,
841 x_unencrypted_password => l_oid_pwd,
842 x_session_number => x_session_number,
843 x_start_date => x_start_date,
844 x_end_date => x_end_date,
845 x_last_logon_date => x_last_logon_date,
846 x_description => x_description,
847 x_password_date => x_password_date,
848 x_password_accesses_left => x_password_accesses_left,
849 x_password_lifespan_accesses => x_password_lifespan_accesses,
850 x_password_lifespan_days => x_password_lifespan_days,
851 x_employee_id => x_employee_id,
852 x_email_address => x_email_address,
853 x_fax => x_fax,
854 x_customer_id => x_customer_id,
855 x_supplier_id => x_supplier_id,
856 x_old_password => null,
857 x_user_guid => l_user_guid,
858 x_change_source => fnd_user_pkg.change_source_oid);
859 end if;
860
861 else -- fnd_web_sec.create_user must have failed.
862 -- The java layer puts message onto the message stack.
863 reason := fnd_message.get();
864 fnd_message.set_name('FND', 'FND_CREATE_USER_FAILED');
865 fnd_message.set_token('USER_NAME', X_USER_NAME);
866 fnd_message.set_token('REASON', reason);
867 app_exception.raise_exception;
868 end if;
869
870 return(user_id);
871 end;
872 end CreateUserIdInternal;
873
874 ----------------------------------------------------------------------
875 --
876 -- CreateUserId (PUBLIC)
877 -- Insert new user record into FND_USER table.
878 -- If that user exists already, exception raised with the error message.
879 -- There are three input arguments must be provided. All the other columns
880 -- in FND_USER table can take the default value.
881 --
882 -- *** NOTE: This version accepts the old customer_id/employee_id
883 -- keys foreign keys to the "person". Use CreateUserIdParty to create
884 -- a user with the new person_party_id key.
885 --
886 -- Input (Mandatory)
887 -- x_user_name: The name of the new user
888 -- x_owner: 'SEED' or 'CUST'(customer)
889 -- x_unencrypted_password: The password for this new user
890 -- Returns
891 -- User_id of created user
892 --
893 function CreateUserId (
894 x_user_name in varchar2,
895 x_owner in varchar2,
896 x_unencrypted_password in varchar2 default null,
897 x_session_number in number default 0,
898 x_start_date in date default sysdate,
899 x_end_date in date default null,
900 x_last_logon_date in date default null,
901 x_description in varchar2 default null,
902 x_password_date in date default null,
903 x_password_accesses_left in number default null,
904 x_password_lifespan_accesses in number default null,
905 x_password_lifespan_days in number default null,
906 x_employee_id in number default null,
907 x_email_address in varchar2 default null,
908 x_fax in varchar2 default null,
909 x_customer_id in number default null,
910 x_supplier_id in number default null)
911 return number is
912 begin
913 return CreateUserIdInternal(
914 x_user_name => x_user_name,
915 x_owner => x_owner,
916 x_unencrypted_password => x_unencrypted_password,
917 x_session_number => x_session_number,
918 x_start_date => x_start_date,
919 x_end_date => x_end_date,
920 x_last_logon_date => x_last_logon_date,
921 x_description => x_description,
922 x_password_date => x_password_date,
923 x_password_accesses_left => x_password_accesses_left,
924 x_password_lifespan_accesses => x_password_lifespan_accesses,
925 x_password_lifespan_days => x_password_lifespan_days,
926 x_employee_id => x_employee_id,
927 x_email_address => x_email_address,
928 x_fax => x_fax,
929 x_customer_id => x_customer_id,
930 x_supplier_id => x_supplier_id,
931 x_person_party_id => null,
932 x_mode => 'EMPLOYEE');
933 end CreateUserId;
934
935 ----------------------------------------------------------------------
936 --
937 -- CreateUserIdParty (PUBLIC)
938 -- Insert new user record into FND_USER table.
939 -- If that user exists already, exception raised with the error message.
940 -- There are three input arguments must be provided. All the other columns
941 -- in FND_USER table can take the default value.
942 --
943 -- *** NOTE: This version accepts the new person_party_id foreign key
944 -- to the "person". Use CreateUserId to create a user with the old
945 -- customer_id/employee_id keys.
946 --
947 -- Input (Mandatory)
948 -- x_user_name: The name of the new user
949 -- x_owner: 'SEED' or 'CUST'(customer)
950 -- x_unencrypted_password: The password for this new user
951 -- Returns
952 -- User_id of created user
953 --
954 function CreateUserIdParty (
955 x_user_name in varchar2,
956 x_owner in varchar2,
957 x_unencrypted_password in varchar2 default null,
958 x_session_number in number default 0,
959 x_start_date in date default sysdate,
960 x_end_date in date default null,
961 x_last_logon_date in date default null,
962 x_description in varchar2 default null,
963 x_password_date in date default null,
964 x_password_accesses_left in number default null,
965 x_password_lifespan_accesses in number default null,
966 x_password_lifespan_days in number default null,
967 x_email_address in varchar2 default null,
968 x_fax in varchar2 default null,
969 x_person_party_id in number default null)
970 return number is
971 begin
972 return CreateUserIdInternal(
973 x_user_name => x_user_name,
974 x_owner => x_owner,
975 x_unencrypted_password => x_unencrypted_password,
976 x_session_number => x_session_number,
977 x_start_date => x_start_date,
978 x_end_date => x_end_date,
979 x_last_logon_date => x_last_logon_date,
980 x_description => x_description,
981 x_password_date => x_password_date,
982 x_password_accesses_left => x_password_accesses_left,
983 x_password_lifespan_accesses => x_password_lifespan_accesses,
984 x_password_lifespan_days => x_password_lifespan_days,
985 x_employee_id => null,
986 x_email_address => x_email_address,
987 x_fax => x_fax,
988 x_customer_id => null,
989 x_supplier_id => null,
990 x_person_party_id => x_person_party_id,
991 x_mode => 'PARTY');
992 end CreateUserIdParty;
993
994 ----------------------------------------------------------------------
995 --
996 -- CreateUser (PUBLIC)
997 -- Insert new user record into FND_USER table.
998 -- If that user exists already, exception raised with the error message.
999 -- There are three input arguments must be provided. All the other columns
1000 -- in FND_USER table can take the default value.
1001 --
1002 -- *** NOTE: This version accepts the old customer_id/employee_id
1003 -- keys foreign keys to the "person". Use CreateUserParty to create
1004 -- a user with the new person_party_id key.
1005 --
1006 -- Input (Mandatory)
1007 -- x_user_name: The name of the new user
1008 -- x_owner: 'SEED' or 'CUST'(customer)
1009 -- x_unencrypted_password: The password for this new user
1010 --
1011 procedure CreateUser (
1012 x_user_name in varchar2,
1013 x_owner in varchar2,
1014 x_unencrypted_password in varchar2 default null,
1015 x_session_number in number default 0,
1016 x_start_date in date default sysdate,
1017 x_end_date in date default null,
1018 x_last_logon_date in date default null,
1019 x_description in varchar2 default null,
1020 x_password_date in date default null,
1021 x_password_accesses_left in number default null,
1022 x_password_lifespan_accesses in number default null,
1023 x_password_lifespan_days in number default null,
1024 x_employee_id in number default null,
1025 x_email_address in varchar2 default null,
1026 x_fax in varchar2 default null,
1027 x_customer_id in number default null,
1028 x_supplier_id in number default null)
1029 is
1030 dummy number;
1031 begin
1032 dummy := fnd_user_pkg.CreateUserId(
1033 x_user_name,
1034 x_owner,
1035 x_unencrypted_password,
1036 x_session_number,
1037 x_start_date,
1038 x_end_date,
1039 x_last_logon_date,
1040 x_description,
1041 x_password_date,
1042 x_password_accesses_left,
1043 x_password_lifespan_accesses,
1044 x_password_lifespan_days,
1045 x_employee_id,
1046 x_email_address,
1047 x_fax,
1048 x_customer_id,
1049 x_supplier_id);
1050 end CreateUser;
1051
1052 ----------------------------------------------------------------------
1053 --
1054 -- CreateUserParty (PUBLIC)
1055 -- Insert new user record into FND_USER table.
1056 -- If that user exists already, exception raised with the error message.
1057 -- There are three input arguments must be provided. All the other columns
1058 -- in FND_USER table can take the default value.
1059 --
1060 -- *** NOTE: This version accepts the new person_party_id foreign key
1061 -- to the "person". Use CreateUser to create a user with the old
1062 -- customer_id/employee_id keys.
1063 --
1064 -- Input (Mandatory)
1065 -- x_user_name: The name of the new user
1066 -- x_owner: 'SEED' or 'CUST'(customer)
1067 -- x_unencrypted_password: The password for this new user
1068 --
1069 procedure CreateUserParty (
1070 x_user_name in varchar2,
1071 x_owner in varchar2,
1072 x_unencrypted_password in varchar2 default null,
1073 x_session_number in number default 0,
1074 x_start_date in date default sysdate,
1075 x_end_date in date default null,
1076 x_last_logon_date in date default null,
1077 x_description in varchar2 default null,
1078 x_password_date in date default null,
1079 x_password_accesses_left in number default null,
1080 x_password_lifespan_accesses in number default null,
1081 x_password_lifespan_days in number default null,
1082 x_email_address in varchar2 default null,
1083 x_fax in varchar2 default null,
1084 x_person_party_id in number default null)
1085 is
1086 dummy number;
1087 begin
1088 dummy := fnd_user_pkg.CreateUserIdParty(
1089 x_user_name,
1090 x_owner,
1091 x_unencrypted_password,
1092 x_session_number,
1093 x_start_date,
1094 x_end_date,
1095 x_last_logon_date,
1096 x_description,
1097 x_password_date,
1098 x_password_accesses_left,
1099 x_password_lifespan_accesses,
1100 x_password_lifespan_days,
1101 x_email_address,
1102 x_fax,
1103 x_person_party_id);
1104 end CreateUserParty;
1105
1106 ----------------------------------------------------------------------
1107 --
1108 -- UpdateUser (Public)
1109 -- Update any column for a particular user record. If that user does
1110 -- not exist, exception raised with error message.
1111 -- You can use this procedure to update a user's password for example.
1112 --
1113 -- *** NOTE: This version accepts the old customer_id/employee_id
1114 -- keys foreign keys to the "person". Use UpdateUserParty to update
1115 -- a user with the new person_party_id key.
1116 --
1117 -- Usage Example in pl/sql
1118 -- begin fnd_user_pkg.updateuser('SCOTT', 'SEED', 'DRAGON'); end;
1119 --
1120 -- Mandatory Input Arguments
1121 -- x_user_name: An existing user name
1122 -- x_owner: 'SEED' or 'CUST'(customer)
1123 --
1124 procedure UpdateUser (
1125 x_user_name in varchar2,
1126 x_owner in varchar2,
1127 x_unencrypted_password in varchar2 default null,
1128 x_session_number in number default null,
1129 x_start_date in date default null,
1130 x_end_date in date default null,
1131 x_last_logon_date in date default null,
1132 x_description in varchar2 default null,
1133 x_password_date in date default null,
1134 x_password_accesses_left in number default null,
1135 x_password_lifespan_accesses in number default null,
1136 x_password_lifespan_days in number default null,
1137 x_employee_id in number default null,
1138 x_email_address in varchar2 default null,
1139 x_fax in varchar2 default null,
1140 x_customer_id in number default null,
1141 x_supplier_id in number default null,
1142 x_old_password in varchar2 default null)
1143 is
1144 begin
1145 UpdateUserInternal(
1146 x_user_name => x_user_name,
1147 x_owner => x_owner,
1148 x_unencrypted_password => x_unencrypted_password,
1149 x_session_number => x_session_number,
1150 x_start_date => x_start_date,
1151 x_end_date => x_end_date,
1152 x_last_logon_date => x_last_logon_date,
1153 x_description => x_description,
1154 x_password_date => x_password_date,
1155 x_password_accesses_left => x_password_accesses_left,
1156 x_password_lifespan_accesses => x_password_lifespan_accesses,
1157 x_password_lifespan_days => x_password_lifespan_days,
1158 x_employee_id => x_employee_id,
1159 x_email_address => x_email_address,
1160 x_fax => x_fax,
1161 x_customer_id => x_customer_id,
1162 x_supplier_id => x_supplier_id,
1163 x_person_party_id => null,
1164 x_old_password => x_old_password,
1165 x_mode => 'EMPLOYEE');
1166 end UpdateUser;
1167
1168 ----------------------------------------------------------------------
1169 --
1170 -- UpdateUserParty (Public)
1171 -- Update any column for a particular user record. If that user does
1172 -- not exist, exception raised with error message.
1173 -- You can use this procedure to update a user's password for example.
1174 --
1175 -- *** NOTE: This version accepts the new person_party_id foreign key
1176 -- to the "person". Use UpdateUser to update a user with the old
1177 -- customer_id/employee_id keys.
1178 --
1179 -- Usage Example in pl/sql
1180 -- begin fnd_user_pkg.updateuser('SCOTT', 'SEED', 'DRAGON'); end;
1181 --
1182 -- Mandatory Input Arguments
1183 -- x_user_name: An existing user name
1184 -- x_owner: 'SEED' or 'CUST'(customer)
1185 --
1186 procedure UpdateUserParty (
1187 x_user_name in varchar2,
1188 x_owner in varchar2,
1189 x_unencrypted_password in varchar2 default null,
1190 x_session_number in number default null,
1191 x_start_date in date default null,
1192 x_end_date in date default null,
1193 x_last_logon_date in date default null,
1194 x_description in varchar2 default null,
1195 x_password_date in date default null,
1196 x_password_accesses_left in number default null,
1197 x_password_lifespan_accesses in number default null,
1198 x_password_lifespan_days in number default null,
1199 x_email_address in varchar2 default null,
1200 x_fax in varchar2 default null,
1201 x_person_party_id in number default null,
1202 x_old_password in varchar2 default null)
1203 is
1204 begin
1205 UpdateUserInternal(
1206 x_user_name => x_user_name,
1207 x_owner => x_owner,
1208 x_unencrypted_password => x_unencrypted_password,
1209 x_session_number => x_session_number,
1210 x_start_date => x_start_date,
1211 x_end_date => x_end_date,
1212 x_last_logon_date => x_last_logon_date,
1213 x_description => x_description,
1214 x_password_date => x_password_date,
1215 x_password_accesses_left => x_password_accesses_left,
1216 x_password_lifespan_accesses => x_password_lifespan_accesses,
1217 x_password_lifespan_days => x_password_lifespan_days,
1218 x_employee_id => null,
1219 x_email_address => x_email_address,
1220 x_fax => x_fax,
1221 x_customer_id => null,
1222 x_supplier_id => null,
1223 x_person_party_id => x_person_party_id,
1224 x_old_password => x_old_password,
1225 x_mode => 'PARTY');
1226 end UpdateUserParty;
1227
1228 ----------------------------------------------------------------------------
1229 --
1230 -- LoadUser (Public)
1231 -- Create or Update user, as appropriate.
1232 --
1233 -- *** NOTE: This version accepts the old customer_id/employee_id
1234 -- keys foreign keys to the "person". Use LoadUserParty to load
1235 -- a user with the new person_party_id key.
1236 --
1237 procedure LoadUser(
1238 x_user_name in varchar2,
1239 x_owner in varchar2,
1240 x_unencrypted_password in varchar2 default null,
1241 x_session_number in number default null,
1242 x_start_date in date default null,
1243 x_end_date in date default null,
1244 x_last_logon_date in date default null,
1245 x_description in varchar2 default null,
1246 x_password_date in date default null,
1247 x_password_accesses_left in number default null,
1248 x_password_lifespan_accesses in number default null,
1249 x_password_lifespan_days in number default null,
1250 x_employee_id in number default null,
1251 x_email_address in varchar2 default null,
1252 x_fax in varchar2 default null,
1253 x_customer_id in number default null,
1254 x_supplier_id in number default null) /* PARTY */
1255 is
1256 l_api_name varchar2(30) := 'LOADUSER';
1257 exists_flag varchar2(1);
1258 reason varchar2(32000);
1259 begin
1260
1261 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1262 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1263 c_log_head || l_api_name || '.before_updstmnt',
1264 'LoadUser(): before update statement');
1265 end if;
1266 begin
1267 select 'Y'
1268 into exists_flag
1269 from fnd_user u
1270 where u.user_name = x_user_name;
1271 exception
1272 when no_data_found then
1273 exists_flag := 'N';
1274 end;
1275
1276 if (exists_flag = 'Y') then
1277 fnd_user_pkg.UpdateUser(x_user_name,
1278 x_owner,
1279 x_unencrypted_password,
1280 x_session_number,
1281 x_start_date,
1282 x_end_date,
1283 x_last_logon_date,
1284 x_description,
1285 x_password_date,
1286 x_password_accesses_left,
1287 x_password_lifespan_accesses,
1288 x_password_lifespan_days,
1289 x_employee_id,
1290 x_email_address,
1291 x_fax,
1292 x_customer_id,
1293 x_supplier_id,
1294 null);
1295 else -- Must be new user
1296 -- insert the new user if x_session_number and x_start_date are provided
1297 if (x_unencrypted_password is not null) then
1298
1299 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1300 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1301 c_log_head || l_api_name || '.call_creatuser',
1302 'LoadUser(): calling CreateUser');
1303 end if;
1304 fnd_user_pkg.createuser(x_user_name,
1305 x_owner,
1306 x_unencrypted_password,
1307 x_session_number,
1308 x_start_date,
1309 x_end_date,
1310 x_last_logon_date,
1311 x_description,
1312 x_password_date,
1313 x_password_accesses_left,
1314 x_password_lifespan_accesses,
1315 x_password_lifespan_days,
1316 x_employee_id,
1317 x_email_address,
1318 x_fax,
1319 x_customer_id,
1320 x_supplier_id);
1321 else
1322 fnd_message.set_name('FND', 'FND_NO_PASSWORD_PROVIDED');
1323 fnd_message.set_token('USER_NAME', X_USER_NAME);
1324 fnd_message.set_token('ROUTINE', 'FND_USER_PKG.LOADUSER');
1325 app_exception.raise_exception;
1326 end if;
1327 end if;
1328 end LoadUser;
1329
1330 ----------------------------------------------------------------------------
1331 --
1332 -- LoadUserParty (Public)
1333 -- Create or Update user, as appropriate.
1334 --
1335 -- *** NOTE: This version accepts the new person_party_id foreign key
1336 -- to the "person". Use LoadUser to load a user with the old
1337 -- customer_id/employee_id keys.
1338 --
1339 procedure LoadUserParty(
1340 x_user_name in varchar2,
1341 x_owner in varchar2,
1342 x_unencrypted_password in varchar2 default null,
1343 x_session_number in number default null,
1344 x_start_date in date default null,
1345 x_end_date in date default null,
1346 x_last_logon_date in date default null,
1347 x_description in varchar2 default null,
1348 x_password_date in date default null,
1349 x_password_accesses_left in number default null,
1350 x_password_lifespan_accesses in number default null,
1351 x_password_lifespan_days in number default null,
1352 x_email_address in varchar2 default null,
1353 x_fax in varchar2 default null,
1354 x_person_party_id in number default null)
1355 is
1356 l_api_name varchar2(30) := 'LOADUSERPARTY';
1357 exists_flag varchar2(1);
1358 reason varchar2(32000);
1359 begin
1360
1361 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1362 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1363 c_log_head || l_api_name || '.before_updstmnt',
1364 'LoadUser(): before update statement');
1365 end if;
1366 begin
1367 select 'Y'
1368 into exists_flag
1369 from fnd_user u
1370 where u.user_name = x_user_name;
1371 exception
1372 when no_data_found then
1373 exists_flag := 'N';
1374 end;
1375
1376 if (exists_flag = 'Y') then
1377 fnd_user_pkg.UpdateUserParty(x_user_name,
1378 x_owner,
1379 x_unencrypted_password,
1380 x_session_number,
1381 x_start_date,
1382 x_end_date,
1383 x_last_logon_date,
1384 x_description,
1385 x_password_date,
1386 x_password_accesses_left,
1387 x_password_lifespan_accesses,
1388 x_password_lifespan_days,
1389 x_email_address,
1390 x_fax,
1391 x_person_party_id,
1392 null);
1393 else -- Must be new user
1394 -- insert the new user if x_session_number and x_start_date are provided
1395 if (x_unencrypted_password is not null) then
1396
1397 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1398 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1399 c_log_head || l_api_name || '.call_creatuser',
1400 'LoadUser(): calling CreateUser');
1401 end if;
1402 fnd_user_pkg.createuserparty(x_user_name,
1403 x_owner,
1404 x_unencrypted_password,
1405 x_session_number,
1406 x_start_date,
1407 x_end_date,
1408 x_last_logon_date,
1409 x_description,
1410 x_password_date,
1411 x_password_accesses_left,
1412 x_password_lifespan_accesses,
1413 x_password_lifespan_days,
1414 x_email_address,
1415 x_fax,
1416 x_person_party_id);
1417 else
1418 fnd_message.set_name('FND', 'FND_NO_PASSWORD_PROVIDED');
1419 fnd_message.set_token('USER_NAME', X_USER_NAME);
1420 fnd_message.set_token('ROUTINE', 'FND_USER_PKG.LOADUSER');
1421 app_exception.raise_exception;
1422 end if;
1423 end if;
1424 end LoadUserParty;
1425
1426 ----------------------------------------------------------------------------
1427 --
1428 -- DisableUser (PUBLIC)
1429 -- Sets end_date to sysdate for a given user. This is to terminate that user.
1430 -- You longer can log in as this user anymore. If username is not valid,
1431 -- exception raised with error message.
1432 --
1433 -- Usage example in pl/sql
1434 -- begin fnd_user_pkg.disableuser('SCOTT'); end;
1435 --
1436 -- Input (Mandatory)
1437 -- username: User Name
1438 --
1439 procedure DisableUser(username varchar2) is
1440 begin
1441 -- bug 3043856 phase 1 of long term plan on Guest User
1442 -- here we do not disable Guest account
1443 if( upper(username) = 'GUEST' ) then
1444 return;
1445 end if;
1446 fnd_user_pkg.UpdateUser(x_user_name => DisableUser.username,
1447 x_owner => null,
1448 x_end_date => sysdate);
1449 end DisableUser;
1450 ----------------------------------------------------------------------------
1451 --
1452 -- ValidateLogin (PUBLIC)
1453 -- Test if password is good for this given user.
1454 --
1455 -- Usage example in pl/sql
1456 -- begin fnd_user_pkg.validatelogin('SCOTT', 'TIGER'); end;
1457 --
1458 -- Input (Mandatory)
1459 -- username: User Name
1460 -- password: User Password
1461 --
1462 function ValidateLogin(username varchar2,
1463 password varchar2) return boolean is
1464 begin
1465 return boolRet(fnd_web_sec.validate_login(username, password));
1466 end ValidateLogin;
1467 ----------------------------------------------------------------------------
1468 --
1469 -- ChangePassword (PUBLIC)
1470 -- Set new password for a given user without having to provide
1471 -- the old password.
1472 --
1473 -- Usage example in pl/sql
1474 -- begin fnd_user_pkg.changepassword('SCOTT', 'WELCOME'); end;
1475 --
1476 -- Input (Mandatory)
1477 -- username: User Name
1478 -- newpassword New Password
1479 --
1480 function ChangePassword(username varchar2,
1481 newpassword varchar2) return boolean is
1482 begin
1483 return boolRet(fnd_web_sec.Change_Password(username, newpassword, FALSE));
1484 end;
1485 ----------------------------------------------------------------------------
1486 --
1487 -- ChangePassword (PUBLIC)
1488 -- Set new password for a given user if the existing password needed to be
1489 -- validated before changing to the new password.
1490 --
1491 -- Usage example in pl/sql
1492 -- begin fnd_user_pkg.changepassword('SCOTT', 'TIGER', 'WELCOME'); end;
1493 --
1494 -- Input (Mandatory)
1495 -- username: User Name
1496 -- oldpassword Old Password
1497 -- newpassword New Password
1498 --
1499 function ChangePassword(username varchar2,
1500 oldpassword varchar2,
1501 newpassword varchar2) return boolean is
1502
1503 begin
1504 return boolRet(fnd_web_sec.Change_Password(username, oldpassword,
1505 newpassword, newpassword, FALSE));
1506 end;
1507 ----------------------------------------------------------------------------
1508 --
1509 -- GetReEncryptedPassword (PUBLIC)
1510 -- Return user password encrypted with new key. This just returns the
1511 -- newly encrypted password. It does not set the password in FND_USER table.
1512 --
1513 -- Usage example in pl/sql
1514 -- declare
1515 -- newpass varchar2(100);
1516 -- begin
1517 -- newpass := fnd_user_pkg.getreencryptedpassword('SCOTT', 'NEWKEY'); end;
1518 -- end;
1519 --
1520 -- Input (Mandatory)
1521 -- username: User Name
1522 -- newkey New Key
1523 --
1524 function GetReEncryptedPassword(username varchar2,
1525 newkey varchar2) return varchar2 is
1526 begin
1527 return (fnd_web_sec.get_reencrypted_password(username, newkey));
1528 end;
1529
1530 ----------------------------------------------------------------------------
1531 -- SetReEncryptedPassword (PUBLIC)
1532 -- Set user password from value returned from GetReEncryptedPassword.
1533 -- This is to update column ENCRYPTED_USER_PASSWORD in table FND_USER
1534 --
1535 -- Usage example in pl/sql
1536 -- declare
1537 -- newpass varchar2(100);
1538 -- begin
1539 -- newpass := fnd_user_pkg.getreencryptedpassword('SCOTT', 'NEWKEY'); end;
1540 -- fnd_user_pkg.setreencryptedpassword('SCOTT', newpass, 'NEWKEY'); end;
1541 -- end;
1542 --
1543 -- Input (Mandatory)
1544 -- username: User Name
1545 -- reencpwd: Reencrypted Password
1546 -- newkey New Key
1547 --
1548 function SetReEncryptedPassword(username varchar2,
1549 reencpwd varchar2,
1550 newkey varchar2) return boolean is
1551 begin
1552 return boolRet(fnd_web_sec.set_reencrypted_password(username,reencpwd,newkey));
1553 end;
1554 ----------------------------------------------------------------------------
1555 -- MergeCustomer (PUBLIC)
1556 -- This is the procedure being called during the Party Merge.
1557 -- FND_USER.MergeCustomer() has been registered in Party Merge Data Dict.
1558 -- The input/output arguments format matches the document PartyMergeDD.doc.
1559 -- The goal is to fix the customer id in fnd_user table to point to the
1560 -- same party when two similar parties are begin merged.
1561 --
1562 -- Usage example in pl/sql
1563 -- This procedure should only be called from the PartyMerge utility.
1564 --
1565 procedure MergeCustomer(p_entity_name in varchar2,
1566 p_from_id in number,
1567 p_to_id in out nocopy number,
1568 p_from_fk_id in number,
1569 p_to_fk_id in number,
1570 p_parent_entity_name in varchar2,
1571 p_batch_id in number,
1572 p_batch_party_id in number,
1573 p_return_status in out nocopy varchar2) is
1574 begin
1575 p_return_status := FND_API.G_RET_STS_SUCCESS;
1576
1577 if (p_from_fk_id <> p_to_fk_id) then
1578
1579 update fnd_user
1580 set customer_id = p_to_fk_id
1581 where customer_id = p_from_fk_id;
1582
1583 -- Added for Function Security Cache Invalidation
1584 fnd_function_security_cache.update_user(p_from_id);
1585
1586 end if;
1587
1588 end MergeCustomer;
1589
1590 --------------------------------------------------------------------------
1591 --
1592 -- user_change - The rule function for FND's subscription on the
1593 -- oracle.apps.wf.entmgr.user.change event. This function
1594 -- retrieves the user's information and updates the
1595 -- corresponding fnd_user as needed, if the user exists.
1596 --
1597 FUNCTION user_change(p_subscription_guid in raw,
1598 p_event in out nocopy wf_event_t)
1599 return varchar2 is
1600 my_ent_type varchar2(50);
1601 my_username varchar2(256); -- one-way code only
1602 my_mode varchar2(256); -- one-way code only
1603 my_cachekey varchar2(256);
1604 my_user varchar2(256);
1605 my_user_id number;
1606 l_allow_sync varchar2(1);
1607 l_local_login varchar(10);
1608 l_profile_defined boolean;
1609 my_guid varchar2(256);
1610 old_desc varchar2(256);
1611 new_desc varchar2(256);
1612 old_email varchar2(256);
1613 new_email varchar2(256);
1614 old_fax varchar2(256);
1615 new_fax varchar2(256);
1616 new_email2 varchar2(256); -- one-way code only
1617 new_fax2 varchar2(256); -- one-way code only
1618 new_desc2 varchar2(256); -- one-way code only
1619 l_api_name varchar2(30) := 'user_change';
1620
1621 old_guid raw(16);
1622
1623 cursor existing_users(userGuid in varchar2) is
1624 select nvl(description, '*NULL*'),
1625 nvl(email_address, '*NULL*'),
1626 nvl(fax, '*NULL*'),
1627 user_name,
1628 user_id
1629 from fnd_user
1630 where user_guid = userGuid;
1631
1632 begin
1633
1634 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1635 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1636 'Start user_change');
1637 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1638 'p_subscription_guid ='||p_subscription_guid);
1639 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1640 'event_name ='||p_event.GetEventName);
1641 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1642 'parameter CHANGE_SOURCE ='||
1643 p_event.GetValueForParameter('CHANGE_SOURCE'));
1644 end if;
1645
1646 -- Ignore our own changes
1647 if (p_event.GetValueForParameter('CHANGE_SOURCE') = 'FND_USR') then
1648 return 'SUCCESS';
1649
1650 -- If CHANGE_SOURCE is LDAP, it means it's raised from one-way sync code.
1651 elsif (p_event.GetValueForParameter('CHANGE_SOURCE') = 'LDAP') then
1652
1653 my_username := p_event.GetEventKey();
1654 my_mode := p_event.GetValueForParameter('CHANGE_TYPE');
1655
1656 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1657 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1658 'LDAP - event_key ='||my_username);
1659 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1660 'LDAP - parameter CHANGE_TYPE ='||my_mode);
1661 end if;
1662
1663 if (my_mode = 'DELETE') then
1664 begin
1665 fnd_user_pkg.disableUser(my_username);
1666 exception
1667 when others then null;
1668 end;
1669
1670 elsif (my_mode in ('ADD','MODIFY','LOAD')) then
1671 --
1672 -- First check to see if user exists and get the existing attribute
1673 -- values. If the user does not exist, we're done.
1674 -- Convert nulls to *NULL* so that old values are either *NULL*
1675 -- or an actual value.
1676 --
1677 begin
1678 select nvl(description, '*NULL*'),
1679 nvl(email_address, '*NULL*'),
1680 nvl(fax, '*NULL*'),
1681 user_guid
1682 into old_desc, old_email, old_fax, old_guid
1683 from fnd_user
1684 where user_name = my_username;
1685 exception
1686 when others then return 'SUCCESS';
1687 end;
1688
1689 --
1690 -- Fetch the new values from the attribute cache
1691 -- New values can either be *NULL*, *UNKNOWN*, or an actual value
1692 --
1693 my_ent_type := wf_entity_mgr.get_entity_type(p_event.GetEventName());
1694
1695 -- NOTE: While we have the ability to distinguish between null
1696 -- and "known to be null", the standard apis do not.
1697 -- For now, we're must pass null regardless which is
1698 -- treated as a "don't change". We do not yet support
1699 -- the ability to "null out" an attribute value.
1700
1701 new_desc := wf_entity_mgr.get_attribute_value(my_ent_type, my_username,
1702 'DESCRIPTION');
1703 new_email := wf_entity_mgr.get_attribute_value(my_ent_type, my_username,
1704 'MAIL');
1705 new_fax := wf_entity_mgr.get_attribute_value(my_ent_type, my_username,
1706 'FACSIMILETELEPHONENUMBER');
1707 --
1708 -- Determine if there are any changes to the attributes we're
1709 -- interested in. And if so, update the user record.
1710 --
1711
1712 if (wf_entity_mgr.isChanged(new_desc, old_desc) OR
1713 wf_entity_mgr.isChanged(new_email, old_email) OR
1714 wf_entity_mgr.isChanged(new_fax, old_fax)) then
1715
1716 -- at least one of the attributes has changed -> update the user --
1717
1718 --
1719 -- NOTE: the following conversions are necessary until we resolve
1720 -- null and "null out"
1721 --
1722 --Start bug 3147423 change null to null_char
1723 if (new_desc = '*NULL*' or new_desc = '*UNKNOWN*') then
1724 new_desc2 := null_char;
1725 else
1726 new_desc2 := new_desc;
1727 end if;
1728
1729 if (new_fax = '*NULL*' or new_fax = '*UNKNOWN*') then
1730 new_fax2 := null_char;
1731 else
1732 new_fax2 := new_fax;
1733 end if;
1734
1735 if (new_email = '*NULL*' or new_email = '*UNKNOWN*') then
1736 new_email2 := null_char;
1737 else
1738 new_email2 := new_email;
1739 end if;
1740 --End bug 3147423
1741 -- end of conversions --
1742
1743 fnd_user_pkg.UpdateUser(
1744 x_user_name => my_username,
1745 x_owner => 'CUST',
1746 x_description => new_desc2,
1747 x_email_address => new_email2,
1748 x_fax => new_fax2,
1749 -- This api is called by LDAP so pass the change source so that we don't
1750 -- start the synch loop.
1751 x_user_guid => old_guid,
1752 x_change_source => fnd_user_pkg.change_source_oid);
1753
1754 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1755 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1756 'LDAP - finished fnd_user_pkg.UpdateUser('||my_username||')');
1757 end if;
1758 end if;
1759 end if;
1760
1761 -- If CHANGE_SOURCE is OID, it means it's raised from two-way sync code.
1762 elsif (p_event.GetValueForParameter('CHANGE_SOURCE') = 'OID') then
1763
1764 my_cachekey := p_event.GetEventKey();
1765 my_ent_type := wf_entity_mgr.get_entity_type(p_event.GetEventName());
1766 my_guid := wf_entity_mgr.get_attribute_value(my_ent_type, my_cachekey,
1767 'ORCLGUID');
1768 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1769 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1770 'OID - event_key ='||my_cachekey);
1771 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1772 'OID - entity_type ='||my_ent_type);
1773 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1774 'OID - guid ='||my_guid);
1775 end if;
1776
1777 open existing_users(my_guid);
1778 LOOP
1779 FETCH existing_users INTO old_desc, old_email, old_fax, my_user, my_user_id;
1780 EXIT WHEN existing_users%NOTFOUND;
1781
1782 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
1783 user_id_z => my_user_id,
1784 val_z => l_allow_sync,
1785 defined_z => l_profile_defined);
1786
1787 fnd_profile.get_specific(name_z => 'APPS_SSO_LOCAL_LOGIN',
1788 user_id_z => my_user_id,
1789 val_z => l_local_login,
1790 defined_z => l_profile_defined);
1791
1792 -- Don't sync users who have 'N' for profile value APPS_SSO_LDAP_SYNC
1793 -- or 'LOCAL' for profile value APPS_SSO_LOCAL_LOGIN
1794
1795 if ( (l_allow_sync = 'Y') and (l_local_login <> 'LOCAL') ) then
1796
1797 if (p_event.GetValueForParameter('CHANGE_TYPE') = 'DELETE') then
1798 begin
1799 fnd_user_pkg.disableUser(my_user);
1800 exception
1801 when others then null;
1802 end;
1803
1804 else
1805 --
1806 -- Fetch the new values from the attribute cache.
1807 -- New values will be *NULL*, *UNKNOWN*, or an actual value.
1808 --
1809 -- NOTE: While we have the ability to distinguish between null
1810 -- and "known to be null", the standard apis do not.
1811 -- For now, we're must pass null regardless which is
1812 -- treated as a "don't change". fnd_user_pkg apis do
1813 -- not yet support the ability to "null out" an
1814 -- attribute value. Weak.
1815 --
1816 new_desc := wf_entity_mgr.get_attribute_value(my_ent_type, my_cachekey,
1817 'DESCRIPTION');
1818 new_email := wf_entity_mgr.get_attribute_value(my_ent_type, my_cachekey,
1819 'MAIL');
1820 new_fax := wf_entity_mgr.get_attribute_value(my_ent_type, my_cachekey,
1821 'FACSIMILETELEPHONENUMBER');
1822 --
1823 -- Determine if there are any changes to the attributes we're
1824 -- interested in. And if so, update the user record.
1825 --
1826 if (wf_entity_mgr.isChanged(new_desc, old_desc) OR
1827 wf_entity_mgr.isChanged(new_email, old_email) OR
1828 wf_entity_mgr.isChanged(new_fax, old_fax)) then
1829 --Start Bug 3147423
1830 if (new_desc in ('*NULL*', '*UNKNOWN*')) then
1831 new_desc := null_char;
1832 end if;
1833
1834 if (new_fax in ('*NULL*', '*UNKNOWN*')) then
1835 new_fax := null_char;
1836 end if;
1837
1838 if (new_email in ('*NULL*', '*UNKNOWN*')) then
1839 new_email := null_char;
1840 end if;
1841
1842 fnd_user_pkg.UpdateUser(
1843 x_user_name => my_user,
1844 x_owner => 'CUST',
1845 x_description => new_desc,
1846 x_email_address => new_email,
1847 x_fax => new_fax);
1848
1849 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1850 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1851 'OID - finished fnd_user_pkg.UpdateUser('||my_user||')');
1852 end if;
1853 end if;
1854 end if;
1855 else
1856 null;
1857 end if;
1858 END LOOP;
1859 CLOSE existing_users;
1860 end if;
1861
1862 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1863 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1864 'End user_change');
1865 end if;
1866 return wf_rule.default_rule(p_subscription_guid, p_event);
1867 end user_change;
1868
1869 --------------------------------------------------------------------------
1870 --
1871 -- user_create_rf - The rule function for FND's 2nd subscription on the
1872 -- oracle.apps.wf.entmgr.user.change event. This function
1873 -- retrieves the user's information and creates the
1874 -- corresponding fnd_user if the user does not already exist.
1875 --
1876 FUNCTION user_create_rf(p_subscription_guid in raw,
1877 p_event in out nocopy wf_event_t)
1878 return varchar2
1879 is
1880 my_ent_type varchar2(50);
1881 my_cachekey varchar2(256);
1882 my_username varchar2(256); -- one-way code only
1883 existing_user varchar2(1);
1884 new_desc varchar2(256);
1885 new_email varchar2(256);
1886 new_fax varchar2(256);
1887 new_guid varchar2(256);
1888 new_email2 varchar2(256); -- one-way code only
1889 new_fax2 varchar2(256); -- one-way code only
1890 new_desc2 varchar2(256); -- one-way code only
1891 l_api_name varchar2(30) := 'user_create_rf';
1892
1893 begin
1894
1895 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1896 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1897 'Start user_create_rf');
1898 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1899 'p_subscription_guid ='||p_subscription_guid);
1900 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1901 'event_name ='||p_event.GetEventName);
1902 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1903 'parameter CHANGE_SOURCE ='||
1904 p_event.GetValueForParameter('CHANGE_SOURCE'));
1905 end if;
1906 -- Ignore our own changes
1907 if (p_event.GetValueForParameter('CHANGE_SOURCE') = 'FND_USR') then
1908 return 'SUCCESS';
1909
1910 -- If CHANGE_SOURCE is LDAP, it means it's raised from one-way sync code.
1911 elsif (p_event.GetValueForParameter('CHANGE_SOURCE') = 'LDAP') then
1912 my_username := p_event.GetEventKey();
1913
1914 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1915 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1916 'LDAP - event_key ='||my_username);
1917 end if;
1918
1919 begin
1920 select 'Y' into existing_user
1921 from fnd_user
1922 where user_name = my_username;
1923
1924 return 'SUCCESS';
1925 exception
1926 when others then
1927
1928 -- user doesn't exist yet, we have work to do --
1929 my_ent_type := wf_entity_mgr.get_entity_type(p_event.GetEventName());
1930
1931 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1932 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1933 'LDAP - entity_type ='||my_ent_type);
1934 end if;
1935
1936 --
1937 -- Fetch the new values from the attribute cache
1938 -- New values can either be *NULL*, *UNKNOWN*, or an actual value
1939 --
1940 new_desc := wf_entity_mgr.get_attribute_value(my_ent_type, my_username,
1941 'DESCRIPTION');
1942 new_email := wf_entity_mgr.get_attribute_value(my_ent_type, my_username,
1943 'MAIL');
1944 new_fax := wf_entity_mgr.get_attribute_value(my_ent_type, my_username,
1945 'FACSIMILETELEPHONENUMBER');
1946 --
1947 -- NOTE: the following conversions are necessary until we resolve
1948 -- null and "null out"
1949 --
1950 --Start Bug 3147423
1951 if (new_desc = '*NULL*' or new_desc = '*UNKNOWN*') then
1952 new_desc2 := null_char;
1953 else
1954 new_desc2 := new_desc;
1955 end if;
1956
1957 if (new_fax = '*NULL*' or new_fax = '*UNKNOWN*') then
1958 new_fax2 := null_char;
1959 else
1960 new_fax2 := new_fax;
1961 end if;
1962
1963 if (new_email = '*NULL*' or new_email = '*UNKNOWN*') then
1964 new_email2 := null_char;
1965 else
1966 new_email2 := new_email;
1967 end if;
1968
1969 --End Bug 3147423
1970
1971 -- end of conversions --
1972
1973 fnd_user_pkg.CreateUser(
1974 x_user_name => my_username,
1975 x_owner => 'CUST',
1976 x_unencrypted_password => null,
1977 x_description => new_desc2,
1978 x_email_address => new_email2,
1979 x_fax => new_fax2);
1980
1981 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1982 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1983 'LDAP - finished fnd_user_pkg.CreateUser('||my_username||')');
1984 end if;
1985 end;
1986
1987 -- If CHANGE_SOURCE is OID, it means it's raised from two-way sync code.
1988 elsif (p_event.GetValueForParameter('CHANGE_SOURCE') = 'OID') then
1989
1990 my_cachekey := p_event.GetEventKey();
1991 my_ent_type := wf_entity_mgr.get_entity_type(p_event.GetEventName());
1992 new_guid := wf_entity_mgr.get_attribute_value(my_ent_type, my_cachekey,
1993 'ORCLGUID');
1994 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1995 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1996 'OID - event_key ='||my_cachekey);
1997 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
1998 'OID - entity_type ='||my_ent_type);
1999 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2000 'OID - guid ='||new_guid);
2001 end if;
2002 begin
2003 select 'Y' into existing_user
2004 from fnd_user
2005 where user_name = my_cachekey;
2006
2007 return 'SUCCESS';
2008
2009 exception
2010 when others then -- CREATE NEW USER --
2011
2012 new_desc := wf_entity_mgr.get_attribute_value(my_ent_type, my_cachekey,
2013 'DESCRIPTION');
2014 new_email := wf_entity_mgr.get_attribute_value(my_ent_type, my_cachekey,
2015 'MAIL');
2016 new_fax := wf_entity_mgr.get_attribute_value(my_ent_type, my_cachekey,
2017 'FACSIMILETELEPHONENUMBER');
2018 if (new_desc in ('*NULL*', '*UNKNOWN*')) then
2019 new_desc := null;
2020 end if;
2021
2022 if (new_fax in ('*NULL*', '*UNKNOWN*')) then
2023 new_fax := null;
2024 end if;
2025
2026 if (new_email in ('*NULL*', '*UNKNOWN*')) then
2027 new_email := null;
2028 end if;
2029
2030 fnd_user_pkg.CreateUser(
2031 x_user_name => my_cachekey,
2032 x_owner => 'CUST',
2033 x_unencrypted_password => null,
2034 x_description => new_desc,
2035 x_email_address => new_email,
2036 x_fax => new_fax);
2037
2038 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2039 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2040 'OID - finished fnd_user_pkg.CreateUser('||my_cachekey||')');
2041 end if;
2042
2043 --
2044 -- guid will eventually be managed centrally so didn't want to add it
2045 -- to the fnd_user_pkg apis...hence the separate update here
2046 --
2047 update fnd_user set user_guid = new_guid
2048 where user_name = my_cachekey;
2049
2050 end;
2051 end if;
2052
2053 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2054 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2055 'End user_create_rf');
2056 end if;
2057
2058 return wf_rule.default_rule(p_subscription_guid, p_event);
2059 end user_create_rf;
2060
2061 -- Private
2062 -- Called by user_synch when user_name is changed
2063 procedure UpdateUserNameChildren(old_name in varchar2,
2064 new_name in varchar2) is
2065 colnames fnd_dictionary_pkg.NameArrayTyp;
2066 colold fnd_dictionary_pkg.NameArrayTyp;
2067 colnew fnd_dictionary_pkg.NameArrayTyp;
2068 l_api_name CONSTANT varchar2(30) := 'UpdateUsernameChildren';
2069 tmpbuf varchar2(240);
2070 reason varchar2(2000);
2071 ret boolean;
2072 begin
2073 -- need to call pk update to do cascade foreign key children update
2074 colnames(0) := 'USER_NAME';
2075 colnames(1) := '';
2076 colold(0) := old_name;
2077 colold(1) := '';
2078 colnew(0) := new_name;
2079 colnew(1) := '';
2080
2081 tmpbuf := 'Start calling fnd_dictionary_pkg.updatepkcolumns('||
2082 old_name||','||new_name||')';
2083 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2084 fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
2085 tmpbuf);
2086 end if;
2087 ret := fnd_dictionary_pkg.updatepkcolumns('FND', 'FND_USER', colnames, colold,
2088 colnew);
2089 tmpbuf := 'Finished fnd_dictionary_pkg.updatepkcolumns';
2090 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2091 fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
2092 tmpbuf);
2093 end if;
2094 exception
2095 when others then
2096 reason := fnd_message.get;
2097 fnd_message.set_name('FND', 'FND_FAILED_UPDATE_UNAME_CHILD');
2098 fnd_message.set_token('OLD_NAME', old_name);
2099 fnd_message.set_token('NEW_NAME', new_name);
2100 fnd_message.set_token('REASON', reason);
2101 app_exception.raise_exception;
2102 end UpdateUserNameChildren;
2103 --------------------------------------------------------------------------
2104 --
2105 -- user_synch - The centralized routine for communicating user changes
2106 -- with wf and entity mgr.
2107 --
2108 PROCEDURE user_synch(p_user_name in varchar2) is
2109 my_userid number;
2110 my_email varchar2(240);
2111 my_desc varchar2(240);
2112 my_fax varchar2(80);
2113 my_pwd varchar2(100);
2114 my_empid number;
2115 my_partyid number;
2116 my_exp date;
2117 my_start date;
2118 my_guid varchar2(32);
2119 myList wf_parameter_list_t;
2120 ch_exp varchar2(20);
2121 ch_start varchar2(20);
2122 --<rwunderl:3203225>
2123 l_defined_z BOOLEAN;
2124 myLang VARCHAR2(240);
2125 myTerr VARCHAR2(240);
2126 l_userNTFPref VARCHAR2(8); -- bug 3280951
2127 l_party_type varchar2(30);
2128 --</rwunderl:3203225>
2129 dummy number(1);
2130 -- <bug 2850261 (enhancement request) >
2131 ptyName varchar2(360);
2132 --</bug 2852061>
2133
2134 l_api_name varchar2(30) := 'UserSynch';
2135
2136 begin
2137 -- fetch info for synch --
2138
2139 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2140 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2141 'Start user_synch');
2142 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2143 'p_user_name = '||p_user_name);
2144 end if;
2145
2146 select user_id, email_address, description, fax, employee_id,
2147 person_party_id,
2148 start_date, end_date, user_guid, encrypted_user_password,
2149 to_char(start_date, 'YYYYMMDDHH24MISS'),
2150 to_char(end_date, 'YYYYMMDDHH24MISS')
2151 into my_userid, my_email, my_desc, my_fax, my_empid, my_partyid,
2152 my_start, my_exp, my_guid, my_pwd, ch_start, ch_exp
2153 from fnd_user
2154 where user_name = upper(p_user_name);
2155
2156 -- construct attribute list for wf synch --
2157 -- unstubbed the code
2158 wf_event.AddParameterToList('MAIL', my_email, mylist);
2159 wf_event.AddParameterToList('DESCRIPTION', my_desc, mylist);
2160 wf_event.AddParameterToList('FACSIMILETELEPHONENUMBER', my_fax, mylist);
2161 wf_event.AddParameterToList('USER_NAME', upper(p_user_name), mylist);
2162 wf_event.AddParameterToList('CN', upper(p_user_name), mylist);
2163 wf_event.AddParameterToList('SN', upper(p_user_name), mylist);
2164 wf_event.AddParameterToList('ORCLACTIVESTARTDATE', ch_start, mylist);
2165 if ((my_exp is null) OR
2166 (trunc(sysdate) between my_start and my_exp)) then
2167 wf_event.AddParameterToList('ORCLISENABLED', 'ACTIVE', mylist);
2168 else
2169 wf_event.AddParameterToList('ORCLISENABLED', 'INACTIVE', mylist);
2170 end if;
2171 wf_event.AddParameterToList('ORCLACTIVEENDDATE', ch_exp, mylist);
2172 wf_event.AddParameterToList('ORCLGUID', my_guid, mylist);
2173 -- bug 4318754
2174
2175 wf_event.AddParameterToList('OLD_ORCLGUID', g_old_user_guid, mylist);
2176 -- end bug 4318754
2177
2178 wf_event.AddParameterToList('USERPASSWORD', my_pwd, mylist);
2179 wf_event.AddParameterToList('PER_PERSON_ID',
2180 fnd_number.number_to_canonical(my_empid), mylist);
2181 wf_event.AddParameterToList('PERSON_PARTY_ID',
2182 fnd_number.number_to_canonical(my_partyid), mylist);
2183 -- bug 4318754
2184 wf_event.AddParameterToList('OLD_PERSON_PARTY_ID',
2185 fnd_number.number_to_canonical(g_old_person_party_id), mylist);
2186 -- end bug 4318754
2187
2188 -- begin 2850261
2189 begin
2190 FND_OAM_USER_INFO.HZ_PARTY_ID_TO_NAME(my_partyid, ptyName, l_party_type);
2191 exception
2192 when no_data_found then
2193 ptyName := p_user_name;
2194 end;
2195 wf_event.AddParameterToList('DISPLAYNAME', upper(ptyName), mylist);
2196 -- end 2852061
2197
2198 -- begin bug 2504562
2199 wf_event.AddParameterToList('OLD_USER_NAME', g_old_user_name, mylist);
2200 /* set g_old_user_name to null here to cover calls to user_synch initiated
2201 from Forms or from the fnd_user_pkg.change_user_name PL/SQL api */
2202 --Comment out this following call because I need it to do the pk children
2203 --later. After pk children update, then I will reset g_old_user_name.
2204 --dummy := fnd_user_pkg.set_old_user_name(NULL);
2205 -- end bug 2504562
2206
2207 -- <rwunderl:3203225>
2208 -- Added calls for the lang/territory and notification preference.
2209 fnd_profile.get_specific(name_z=>'ICX_LANGUAGE', user_id_z=>my_userid,
2210 val_z=>myLang, defined_z=>l_defined_z);
2211 wf_event.AddParameterToList('PREFERREDLANGUAGE', myLang, myList);
2212
2213 fnd_profile.get_specific(name_z=>'ICX_TERRITORY', user_id_z=>my_userid,
2214 val_z=>myTerr, defined_z=>l_defined_z);
2215
2216 wf_event.AddParameterToList('ORCLNLSTERRITORY', myTerr, myList);
2217
2218 -- begin bug 3280951
2219 -- Retrieve the notification preference for the user
2220 -- using substr since fnd_preference.get returns varchar2(240)
2221 -- and this maps to varchar2(8) column on WF side.
2222 l_userNTFPref := substr(fnd_preference.get(UPPER(p_user_name), 'WF', 'MAILTYPE'), 1, 8);
2223
2224 if (l_userNTFPref is NULL) then
2225 --There is no preference for the user, so retrieving the global.
2226 l_userNTFPref := substr(fnd_preference.get('-WF_DEFAULT-', 'WF', 'MAILTYPE'), 1, 8);
2227
2228 end if;
2229
2230 wf_event.AddParameterToList('ORCLWORKFLOWNOTIFICATIONPREF',
2231 nvl(l_userNTFPref, 'MAILHTML'), mylist);
2232
2233 -- end bug 3280951
2234
2235 --Bug 3277794
2236 --Add the over-write parameter to the attribute list
2237 wf_event.AddParameterToList('WFSYNCH_OVERWRITE','TRUE',mylist);
2238
2239 -- </rwunderl:3203225>
2240
2241 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2242 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2243 'Calling wf_local_synch.propagate_user');
2244 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2245 'ORCLGUID = '|| my_guid);
2246 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2247 'PER_PERSON_ID = '|| my_empid);
2248 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2249 'PERSON_PARTY_ID = '|| my_partyid);
2250 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2251 'DISPLAY_NAME(PARTY_NAME) = '|| ptyName);
2252 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2253 'OLD_USER_NAME = '|| g_old_user_name);
2254 end if;
2255 -- update wf and the entity manager --
2256 wf_local_synch.propagate_user('FND_USR',my_userid, myList, my_start, my_exp);
2257 -- end of unstub
2258
2259 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2260 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2261 'Finished wf_local_synch.propagate_user');
2262 end if;
2263 -- -----------------------------------------------------------------
2264 -- since wf_local_synch is temporarily stubbed out, also make the
2265 -- direct calls to entity-mgr to compensate (jvc)
2266 -- -----------------------------------------------------------------
2267
2268 -- don't make this call anymore - code unstubbed
2269
2270 -- wf_entity_mgr.put_attribute_value('USER', upper(p_user_name),
2271 -- 'MAIL', my_email);
2272 -- wf_entity_mgr.put_attribute_value('USER', upper(p_user_name),
2273 -- 'DESCRIPTION', my_desc);
2274 -- wf_entity_mgr.put_attribute_value('USER', upper(p_user_name),
2275 -- 'FACSIMILETELEPHONENUMBER', my_fax);
2276 -- wf_entity_mgr.put_attribute_value('USER', upper(p_user_name),
2277 -- 'USER_NAME', upper(p_user_name));
2278 -- wf_entity_mgr.put_attribute_value('USER', upper(p_user_name),
2279 -- 'SN', upper(p_user_name));
2280 -- wf_entity_mgr.put_attribute_value('USER', upper(p_user_name),
2281 -- 'ORCLACTIVESTARTDATE', ch_start);
2282 -- wf_entity_mgr.put_attribute_value('USER', upper(p_user_name),
2283 -- 'ORCLACTIVEENDDATE', ch_exp);
2284 -- wf_entity_mgr.put_attribute_value('USER', upper(p_user_name),
2285 -- 'ORCLGUID', my_guid);
2286 -- wf_entity_mgr.put_attribute_value('USER', upper(p_user_name),
2287 -- 'USERPASSWORD', my_pwd);
2288 -- wf_entity_mgr.put_attribute_value('USER', upper(p_user_name),
2289 -- 'PER_PERSON_ID',
2290 -- fnd_number.number_to_canonical(my_empid));
2291 -- wf_entity_mgr.process_changes('USER', upper(p_user_name), 'FND_USR');
2292
2293 -- Added for bug 3804617
2294 -- If a user_name is changed, we need to update all foreign key children.
2295 if (g_old_user_name is not null) then
2296 UpdateUsernameChildren(g_old_user_name, p_user_name);
2297 end if;
2298 -- set g_old_user_name to null here to cover calls to user_synch initiated
2299 -- from Forms or from the fnd_user_pkg.change_user_name PL/SQL api
2300 dummy := fnd_user_pkg.set_old_user_name(NULL);
2301 dummy := fnd_user_pkg.set_old_person_party_id(NULL);
2302 dummy := fnd_user_pkg.set_old_user_guid(NULL);
2303
2304 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
2305 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name,
2306 'End user_synch');
2307 end if;
2308 exception
2309 -- Bug 3617474: This NO_DATA_FOUND exception handler was placed in the event
2310 -- that fnd_user_pkg.user_synch() was passed an invalid or null user_name,
2311 -- i.e a user_name that does not exist in fnd_user.
2312 when NO_DATA_FOUND then
2313 null;
2314
2315 end user_synch;
2316
2317 --------------------------------------------------------------------------
2318 --
2319 -- DelResp (PUBLIC)
2320 -- Detach a responsibility which is currently attached to this given user.
2321 -- If any of the username or application short name or responsibility key or
2322 -- security group is not valid, exception raised with error message.
2323 --
2324 -- Usage example in pl/sql
2325 -- begin fnd_user_pkg.delresp('SCOTT', 'FND', 'APPLICATION_DEVELOPER',
2326 -- 'STANDARD'); end;
2327 -- Input (Mandatory)
2328 -- username: User Name
2329 -- resp_app: Application Short Name
2330 -- resp_key: Responsibility Key
2331 -- security_group: Security Group Key
2332 --
2333 procedure DelResp(username varchar2,
2334 resp_app varchar2,
2335 resp_key varchar2,
2336 security_group varchar2) is
2337 userid number := -1;
2338 respid number := -1;
2339 appid number := -1;
2340 secid number := -1;
2341 startdate date;
2342 begin
2343 begin
2344 select user_id into userid
2345 from fnd_user
2346 where user_name = DelResp.username;
2347
2348 select application_id into appid
2349 from fnd_application
2350 where application_short_name = DelResp.Resp_app;
2351
2352 /* Bug4600645 - Modified to get actual start_date instead of using */
2353 /* sysdate in call to fnd_user_resp_groups_api.update_assignment */
2354
2355 select responsibility_id
2356 into respid
2357 from fnd_responsibility
2358 where application_id = appid
2359 and responsibility_key = DelResp.resp_key;
2360
2361 select security_group_id into secid
2362 from fnd_security_groups
2363 where security_group_key = DelResp.security_group;
2364
2365 /*Bug4600645 - Get actual start_date value*/
2366
2367 select start_date into startdate
2368 from fnd_user_resp_groups_all
2369 where user_id = userid
2370 and responsibility_id = respid
2371 and responsibility_application_id = appid;
2372
2373 exception
2374 when no_data_found then
2375 fnd_message.set_token('USER_NAME', username);
2376 fnd_message.set_name('FND', 'INVALID_RESPONSIBILITY_DATA');
2377 fnd_message.set_token('APPS_NAME', resp_app);
2378 fnd_message.set_token('RESP_KEY', resp_key);
2379 fnd_message.set_token('SECURITY_GROUP', security_group);
2380 app_exception.raise_exception;
2381 end;
2382
2383 if (fnd_user_resp_groups_api.assignment_exists(
2384 userid, respid, appid, secid)) then
2385 fnd_user_resp_groups_api.update_assignment(
2386 user_id => userid,
2387 responsibility_id => respid,
2388 responsibility_application_id => appid,
2389 security_group_id => secid,
2390 start_date => startdate,
2391 end_date => sysdate,
2392 description => null);
2393 end if;
2394 end DelResp;
2395
2396 --------------------------------------------------------------------------
2397 --
2398 -- AddResp (PUBLIC)
2399 -- For a given user, attach a valid responsibility.
2400 -- If user name or application short name or responsbility key name
2401 -- or security group key is not valid, exception raised with error message.
2402 --
2403 -- Usage example in pl/sql
2404 -- begin fnd_user_pkg.addresp('SCOTT', 'FND', 'APPLICATION_DEVELOPER',
2405 -- 'STANDARD', 'DESCRIPTION', sysdate, null); end;
2406 -- Input (Mandatory)
2407 -- username: User Name
2408 -- resp_app: Application Short Name
2409 -- resp_key: Responsibility Key
2410 -- security_group: Security Group Key
2411 -- description: Description
2412 -- start_date: Start Date
2413 -- end_date: End Date
2414 --
2415 procedure AddResp(username varchar2,
2416 resp_app varchar2,
2417 resp_key varchar2,
2418 security_group varchar2,
2419 description varchar2,
2420 start_date date,
2421 end_date date) is
2422 userid number := -1;
2423 respid number := -1;
2424 appid number := -1;
2425 secid number := -1;
2426 begin
2427
2428 begin
2429 select user_id into userid
2430 from fnd_user
2431 where user_name = AddResp.username;
2432
2433 select application_id into appid
2434 from fnd_application
2435 where application_short_name = AddResp.resp_app;
2436
2437 select responsibility_id into respid
2438 from fnd_responsibility
2439 where application_id = appid
2440 and responsibility_key = AddResp.resp_key;
2441
2442 select security_group_id into secid
2443 from fnd_security_groups
2444 where security_group_key = AddResp.security_group;
2445
2446 exception
2447 when no_data_found then
2448 fnd_message.set_token('USER_NAME', username);
2449 fnd_message.set_name('FND', 'INVALID_RESPONSIBILITY_DATA');
2450 fnd_message.set_token('APPS_NAME', resp_app);
2451 fnd_message.set_token('RESP_KEY', resp_key);
2452 fnd_message.set_token('SECURITY_GROUP', security_group);
2453 app_exception.raise_exception;
2454 end;
2455
2456 if (fnd_user_resp_groups_api.assignment_exists(
2457 userid, respid, appid, secid)) then
2458 fnd_user_resp_groups_api.update_assignment(
2459 user_id => userid,
2460 responsibility_id => respid,
2461 responsibility_application_id => appid,
2462 security_group_id => secid,
2463 start_date => AddResp.start_date,
2464 end_date => AddResp.end_date,
2465 description => AddResp.description);
2466 else
2467 fnd_user_resp_groups_api.insert_assignment(
2468 user_id => userid,
2469 responsibility_id => respid,
2470 responsibility_application_id => appid,
2471 security_group_id => secid,
2472 start_date => AddResp.start_date,
2473 end_date => AddResp.end_date,
2474 description => AddResp.description);
2475 end if;
2476
2477 end AddResp;
2478
2479 -------------------------------------------------------------------
2480 -- Name: isPasswordChangeable
2481 -- Description: Checks if user us externally authenticatied
2482 ----------------------------------------------------------------------
2483 Function isPasswordChangeable(
2484 p_user_name in varchar2)
2485 return boolean
2486 is
2487 begin
2488 return(fnd_sso_manager.isPasswordChangeable(p_user_name));
2489 end isPasswordChangeable;
2490
2491 -------------------------------------------------------------------
2492 -- Name: UpdatePassword_WF
2493 -- Description: Calls FND_USER_PKG.UpdateUser
2494 -------------------------------------------------------------------
2495 Procedure UpdatePassword_WF(itemtype in varchar2,
2496 itemkey in varchar2,
2497 actid in number,
2498 funcmode in varchar2,
2499 resultout in out nocopy varchar2) is
2500
2501 begin
2502
2503 if (funcmode = 'RUN') then
2504 FND_USER_PKG.UpdateUser(
2505 x_user_name=>
2506 WF_ENGINE.GetActivityAttrText(itemtype, itemkey, actid,
2507 'X_USER_NAME'),
2508 x_owner=>'CUST',
2509 x_unencrypted_password=>
2510 WF_ENGINE.GetActivityAttrText(itemtype, itemkey, actid,
2511 'X_UNENCRYPTED_PASSWORD',
2512 TRUE),
2513 x_password_date=>
2514 WF_ENGINE.GetActivityAttrDate(itemtype, itemkey, actid,
2515 'X_PASSWORD_DATE', TRUE),
2516 x_password_accesses_left=>
2517 WF_ENGINE.GetActivityAttrNumber(itemtype, itemkey, actid,
2518 'X_PASSWORD_ACCESSES_LEFT',
2519 TRUE),
2520 x_password_lifespan_accesses=>
2521 WF_ENGINE.GetActivityAttrNumber(itemtype, itemkey, actid,
2522 'X_PASSWORD_LIFESPAN_ACCESSES',
2523 TRUE),
2524 x_password_lifespan_days=>
2525 WF_ENGINE.GetActivityAttrNumber(itemtype, itemkey, actid,
2526 'X_PASSWORD_LIFESPAN_DAYS',
2527 TRUE));
2528
2529 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
2530
2531 else
2532 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
2533
2534 end if;
2535
2536 exception
2537 when others then
2538 Wf_Core.Context('FND_WF_STANDARD', 'UpdatePassword', itemtype, itemkey,
2539 actid);
2540 raise;
2541 end;
2542
2543 ----------------------------------------------------------------------------
2544 --
2545 -- DERIVE_PERSON_PARTY_ID
2546 -- Derive the person_party_id, given a customer_id and employee_id
2547 -- IN
2548 -- user_name - User name (used for error messages)
2549 -- customer_id - Customer_id
2550 -- employee_id - Employee_id
2551 -- RETURNS
2552 -- person_party_id - Derived party_id
2553 --
2554 Function DERIVE_PERSON_PARTY_ID(
2555 user_name in varchar2,
2556 customer_id in number,
2557 employee_id in number,
2558 log_exception in varchar2 default 'Y')
2559 return number is
2560 l_api_name constant varchar2(30) := 'DERIVE_PERSON_PARTY_ID';
2561 l_employee_id number;
2562 l_customer_id number;
2563 l_cust_person_party_id number;
2564 l_cust_person_party_name varchar2(360);
2565 l_emp_person_party_id number;
2566 l_emp_person_party_name varchar2(360);
2567 l_party_type varchar2(30);
2568 l_party_name varchar2(360);
2569 l_err_to_raise boolean;
2570 begin
2571 -- if we have the value cached, just return the cached value.
2572 if ( ( (z_customer_id = customer_id)
2573 or (z_customer_id is null and customer_id is null))
2574 and ( (z_employee_id = employee_id)
2575 or (z_employee_id is null and employee_id is null))) then
2576 return z_person_party_id;
2577 end if;
2578
2579 l_employee_id := employee_id;
2580 l_customer_id := customer_id;
2581 l_cust_person_party_id := NULL;
2582 l_emp_person_party_id := NULL;
2583
2584 -- *** Derive party for customer_id ***
2585 if (l_customer_id is not NULL) then
2586 -- Get party type of current customer_id
2587 begin
2588 FND_OAM_USER_INFO.HZ_PARTY_ID_TO_NAME(l_customer_id,
2589 l_party_name, l_party_type);
2590 exception
2591 when no_data_found then
2592 if (log_exception = 'Y') then
2593 fnd_message.set_name('FND', 'USER_INVALID_CUSTOMER');
2594 fnd_message.set_token('USER', user_name);
2595 fnd_message.set_token('CUSTID', l_customer_id);
2596 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
2597 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2598 c_log_head || l_api_name || '.not_in_hz_parties', FALSE);
2599 end if;
2600 app_exception.raise_exception;
2601 else
2602 return null;
2603 end if;
2604 end;
2605
2606 if (l_party_type = 'PERSON') then
2607 -- Customer_id is already party, just copy value to party_id
2608 l_cust_person_party_id := l_customer_id;
2609 elsif (l_party_type = 'PARTY_RELATIONSHIP') then
2610 -- This is a relationship party. Get the person_party
2611 -- associated with this relationship as the party_id
2612 begin
2613 l_cust_person_party_id :=
2614 FND_OAM_USER_INFO.GET_ORGANIZATION_ID(l_customer_id);
2615 exception
2616 when no_data_found then
2617 if (log_exception = 'Y') then
2618 fnd_message.set_name('FND', 'USER_INVALID_PARTY_REL');
2619 fnd_message.set_token('USER', user_name);
2620 fnd_message.set_token('PNAME', l_party_name);
2621 fnd_message.set_token('CUSTID', l_customer_id);
2622 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
2623 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2624 c_log_head || l_api_name || '.not_in_hz_party_relationships',
2625 FALSE);
2626 end if;
2627 app_exception.raise_exception;
2628 else
2629 return null;
2630 end if;
2631 end;
2632 else
2633 if (log_exception = 'Y') then
2634 -- Invalid party type, raise error
2635 fnd_message.set_name('FND', 'USER_INVALID_PARTY_TYPE');
2636 fnd_message.set_token('USER', user_name);
2637 fnd_message.set_token('PNAME', l_party_name);
2638 fnd_message.set_token('PARTYID', l_customer_id);
2639 fnd_message.set_token('PARTY_TYPE', l_party_type);
2640 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
2641 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2642 c_log_head || l_api_name || '.bad_party_type', FALSE);
2643 end if;
2644 app_exception.raise_exception;
2645 else
2646 return null;
2647 end if;
2648 end if;
2649
2650 end if;
2651
2652 -- *** Derive party for employee_id ***
2653 if (l_employee_id is not NULL) then
2654 begin
2655 /*
2656 select party_id
2657 into l_emp_person_party_id
2658 from per_all_people_f
2659 where person_id = l_employee_id
2660 and trunc(sysdate) between effective_start_date
2661 and effective_end_date;
2662 */
2663
2664 -- Modified above SQL for bug 3094664
2665
2666 l_emp_person_party_id := FND_OAM_USER_INFO.GET_PARTY_ID(l_employee_id);
2667 exception
2668 when no_data_found then
2669 if (log_exception = 'Y') then
2670 fnd_message.set_name('FND', 'USER_INVALID_EMPLOYEE');
2671 fnd_message.set_token('USER', user_name);
2672 fnd_message.set_token('EMPID', l_employee_id);
2673 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
2674 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2675 c_log_head || l_api_name || '.bad_empid', FALSE);
2676 end if;
2677 app_exception.raise_exception;
2678 else
2679 return null;
2680 end if;
2681 end;
2682 end if;
2683
2684 -- Get person_party_id from customer or employee version
2685 if (l_cust_person_party_id is not null) then
2686 if (l_emp_person_party_id is not null) then
2687 -- Both found, check for mismatch
2688 if (l_cust_person_party_id <> l_emp_person_party_id) then
2689 if (log_exception = 'Y') then
2690 begin
2691 FND_OAM_USER_INFO.HZ_PARTY_ID_TO_NAME(l_cust_person_party_id,
2692 l_cust_person_party_name,
2693 l_party_type);
2694 exception
2695 when no_data_found then
2696 fnd_message.set_name('FND', 'USER_CUST_PARTY_NOT_FOUND');
2697 fnd_message.set_token('USER', user_name);
2698 fnd_message.set_token('CUSTID', l_customer_id);
2699 if (fnd_log.LEVEL_EXCEPTION >=
2700 fnd_log.g_current_runtime_level) then
2701 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2702 c_log_head || l_api_name || '.cust_emp_mismatch', FALSE);
2703 end if;
2704 app_exception.raise_exception;
2705 end;
2706
2707 begin
2708 FND_OAM_USER_INFO.HZ_PARTY_ID_TO_NAME(l_emp_person_party_id,
2709 l_emp_person_party_name,
2710 l_party_type);
2711 exception
2712 when no_data_found then
2713 fnd_message.set_name('FND', 'USER_EMP_PARTY_NOT_FOUND');
2714 fnd_message.set_token('USER', user_name);
2715 fnd_message.set_token('EMPID', l_emp_person_party_id);
2716 if (fnd_log.LEVEL_EXCEPTION >=
2717 fnd_log.g_current_runtime_level) then
2718 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2719 c_log_head || l_api_name || '.cust_emp_mismatch', FALSE);
2720 end if;
2721 app_exception.raise_exception;
2722 end;
2723
2724 fnd_message.set_name('FND', 'USER_CUST_EMP_MISMATCH');
2725 fnd_message.set_token('USER', user_name);
2726 fnd_message.set_token('CUSTID', l_customer_id);
2727 fnd_message.set_token('CUSTPARTY', l_cust_person_party_name);
2728 fnd_message.set_token('EMPID', l_employee_id);
2729 fnd_message.set_token('EMPPARTY', l_emp_person_party_name);
2730 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
2731 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2732 c_log_head || l_api_name || '.cust_emp_mismatch', FALSE);
2733 end if;
2734 app_exception.raise_exception;
2735 else
2736 return null;
2737 end if;
2738 end if;
2739 end if;
2740 -- Either no empid, or a matching one, use customer version
2741 z_person_party_id := l_cust_person_party_id;
2742 else
2743 -- Use employee version
2744 z_person_party_id := l_emp_person_party_id;
2745 end if;
2746
2747 -- Save away keys in cache and return
2748 z_customer_id := customer_id;
2749 z_employee_id := employee_id;
2750 return(z_person_party_id);
2751 end DERIVE_PERSON_PARTY_ID;
2752
2753 ----------------------------------------------------------------------
2754 --
2755 -- DERIVE_CUSTOMER_EMPLOYEE_ID
2756 -- Update customer and employee ids if person_party_id is changed
2757 -- IN
2758 -- user_name - User name (used for error messages)
2759 -- person_party_id - Party id
2760 -- OUT
2761 -- customer_id - Derived customer id
2762 -- employee_id - Derived employee id
2763 --
2764 Procedure DERIVE_CUSTOMER_EMPLOYEE_ID(
2765 user_name in varchar2,
2766 person_party_id in number,
2767 customer_id out nocopy number,
2768 employee_id out nocopy number)
2769 is
2770 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_CUSTOMER_EMPLOYEE_ID';
2771 l_person_party_id number;
2772 l_employee_id number;
2773 l_customer_id number;
2774 l_person_party_name varchar2(360);
2775 l_party_type varchar2(30);
2776 l_cursorid integer;
2777 l_blockstr varchar2(1000);
2778 l_dummy integer;
2779 l_party_id number;
2780 l_person_id number(30);
2781 l_matches varchar2(1);
2782
2783 begin
2784 l_person_party_id := person_party_id;
2785
2786 -- if we have the value cached, just return the cached value.
2787 if ( ( (z_rev_person_party_id = l_person_party_id)
2788 or (z_rev_person_party_id is NULL and l_person_party_id is NULL)))
2789 then
2790 customer_id := z_rev_customer_id;
2791 employee_id := z_rev_employee_id;
2792 return;
2793 end if;
2794
2795 -- Validate person_party_id is correct type
2796 if (l_person_party_id is not null) then
2797 begin
2798 FND_OAM_USER_INFO.HZ_PARTY_ID_TO_NAME(l_person_party_id,
2799 l_person_party_name,
2800 l_party_type);
2801 exception
2802 when no_data_found then
2803 fnd_message.set_name('FND', 'USER_INVALID_PARTY');
2804 fnd_message.set_token('USER', user_name);
2805 fnd_message.set_token('PARTYID', l_person_party_id);
2806 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
2807 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2808 c_log_head || l_api_name || '.not_in_hz_parties', FALSE);
2809 end if;
2810 app_exception.raise_exception;
2811 end;
2812 if (l_party_type <> 'PERSON') then
2813 fnd_message.set_name('FND', 'USER_INVALID_PARTY_TYPE');
2814 fnd_message.set_token('USER', user_name);
2815 fnd_message.set_token('PARTYID', l_person_party_id);
2816 fnd_message.set_token('PARTY_TYPE', l_party_type);
2817 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
2818 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2819 c_log_head || l_api_name || '.bad_party_type', FALSE);
2820 end if;
2821 app_exception.raise_exception;
2822 end if;
2823 end if;
2824
2825 -- Set customer_id to new party_id
2826 l_customer_id := l_person_party_id;
2827
2828 -- Fix for Bug#3776819 - Derive employee id
2829 -- Use Dynamic SQL to execute the HR API to derive the person id
2830 begin
2831 l_cursorid := dbms_sql.open_cursor;
2832 l_blockstr :=
2833 'BEGIN
2834 hr_tca_utility.get_person_id(p_party_id => :l_party_id, p_person_id => :l_person_id, p_matches => :l_matches);
2835 END;';
2836
2837 dbms_sql.parse(l_cursorid, l_blockstr, dbms_sql.v7);
2838
2839 dbms_sql.bind_variable(l_cursorid, ':l_party_id', l_person_party_id);
2840 dbms_sql.bind_variable(l_cursorid, ':l_person_id', l_person_id);
2841 dbms_sql.bind_variable(l_cursorid, ':l_matches', l_matches, 1);
2842
2843 l_dummy := dbms_sql.execute(l_cursorid);
2844
2845 dbms_sql.variable_value(l_cursorid, ':l_person_id', l_person_id);
2846 dbms_sql.variable_value(l_cursorid, ':l_matches', l_matches);
2847 dbms_sql.close_cursor(l_cursorid);
2848
2849 -- Set the employee id
2850 if (l_person_id is not null) then
2851 l_employee_id := l_person_id;
2852 else
2853 -- Clear the employee_id
2854 l_employee_id := null;
2855 end if;
2856
2857 -- Clear the employee_id
2858 --l_employee_id := null;
2859
2860 exception
2861 when others then
2862 l_employee_id := null;
2863 dbms_sql.close_cursor(l_cursorid);
2864 end;
2865
2866 -- Return
2867 customer_id := l_customer_id;
2868 employee_id := l_employee_id;
2869
2870 -- Cache my new values
2871 z_rev_person_party_id := l_person_party_id;
2872 z_rev_customer_id := l_customer_id;
2873 z_rev_employee_id := l_employee_id;
2874
2875 end DERIVE_CUSTOMER_EMPLOYEE_ID;
2876
2877 ----------------------------------------------------------------------------
2878 --
2879 -- EnableUser (PUBLIC)
2880 -- Sets the start_date and end_date as requested. By default, the
2881 -- start_date will be set to sysdate and end_date to null.
2882 -- This is to enable that user.
2883 -- You can log in as this user from now.
2884 -- If username is not valid, exception raised with error message.
2885 --
2886 -- Usage example in pl/sql
2887 -- begin fnd_user_pkg.enableuser('SCOTT'); end;
2888 -- begin fnd_user_pkg.enableuser('SCOTT', sysdate+1, sysdate+30); end;
2889 --
2890 -- Input (Mandatory)
2891 -- username: User Name
2892 -- Input (Non-Mandatory)
2893 -- start_date: Start Date
2894 -- end_date: End Date
2895 --
2896 procedure EnableUser(username varchar2,
2897 start_date date default sysdate,
2898 end_date date default fnd_user_pkg.null_date) is
2899 begin
2900 fnd_user_pkg.UpdateUser(x_user_name => EnableUser.username,
2901 x_owner => NULL,
2902 x_start_date => EnableUser.start_date,
2903 x_end_date => EnableUser.end_date);
2904 end EnableUser;
2905
2906
2907 ----------------------------------------------------------------------------
2908 --
2909 -- CreatePendingUser (PUBLIC)
2910 -- Create a user whose start_date and end_date = FND_API.G_MISS_DATE as
2911 -- a pending user.
2912 -- Pending user is created when a user registers a user account through
2913 -- UMX with an aproval process.
2914 --
2915 --
2916 -- Usage example in pl/sql
2917 -- begin fnd_user_pkg.creatependinguser('SCOTT', 'SEED', 'welcome'); end;
2918 -- begin fnd_user_pkg.creatependinguser('SCOTT', 'SEED'); end;
2919 --
2920 -- Input (Mandatory)
2921 -- x_user_name: User Name
2922 -- x_owner: 'SEED' or 'CUST'(customer)
2923 --
2924 function CreatePendingUser(
2925 x_user_name in varchar2,
2926 x_owner in varchar2,
2927 x_unencrypted_password in varchar2 default null,
2928 x_session_number in number default 0,
2929 x_description in varchar2 default null,
2930 x_password_date in date default null,
2931 x_password_accesses_left in number default null,
2932 x_password_lifespan_accesses in number default null,
2933 x_password_lifespan_days in number default null,
2934 x_email_address in varchar2 default null,
2935 x_fax in varchar2 default null,
2936 x_person_party_id in number default null) return number
2937 is
2938 uid number;
2939 begin
2940 uid := fnd_user_pkg.CreateUserIdParty(
2941 x_user_name,
2942 x_owner,
2943 x_unencrypted_password,
2944 x_session_number,
2945 FND_API.G_MISS_DATE,
2946 FND_API.G_MISS_DATE,
2947 null,
2948 x_description,
2949 x_password_date,
2950 x_password_accesses_left,
2951 x_password_lifespan_accesses,
2952 x_password_lifespan_days,
2953 x_email_address,
2954 x_fax,
2955 x_person_party_id);
2956 return(uid);
2957
2958
2959 end CreatePendingUser;
2960
2961
2962 ----------------------------------------------------------------------------
2963 --
2964 -- RemovePendingUser (PUBLIC)
2965 -- Delete this user from fnd_user table only if this is a pending user.
2966 -- If this is not a valid username or is not a pending user, raise error.
2967 -- Pending user is created when a user registers a user account through
2968 -- UMX with an aproval process.
2969 --
2970 -- Usage example in pl/sql
2971 -- begin fnd_user_pkg.removependinguser('SCOTT'); end;
2972 --
2973 -- Input (Mandatory)
2974 -- username: User Name
2975 --
2976 procedure RemovePendingUser(username varchar2) is
2977 l_user_id number;
2978 retval pls_integer;
2979 l_user_guid raw(16);
2980 begin
2981
2982 -- Added for Function Security Cache Invalidation
2983 -- RSHEH: Need to have exception trapping for no_data_found exception
2984 begin
2985 select user_id, user_guid
2986 into l_user_id, l_user_guid
2987 from fnd_user
2988 where user_name = upper(username)
2989 and to_char(start_date) = to_char(FND_API.G_MISS_DATE)
2990 and to_char(end_date) = to_char(FND_API.G_MISS_DATE);
2991
2992 exception
2993 when no_data_found then
2994 fnd_message.set_name('FND', 'FND_INVALID_USER');
2995 fnd_message.set_token('USER_NAME', username);
2996 app_exception.raise_exception;
2997 end;
2998
2999 -- Bug 4318754. Synch up with SSO
3000 if (l_user_guid is not null) then
3001 begin
3002 fnd_ldap_wrapper.delete_user(l_user_guid, retval);
3003 if (retval <> fnd_ldap_wrapper.G_SUCCESS) then
3004 app_exception.raise_exception;
3005 end if;
3006 exception
3007 when others then
3008 app_exception.raise_exception;
3009 end;
3010 end if;
3011
3012 -- Only allow to delete a PENDING user
3013 delete from fnd_user
3014 where user_name = upper(username)
3015 and to_char(start_date) = to_char(FND_API.G_MISS_DATE)
3016 and to_char(end_date) = to_char(FND_API.G_MISS_DATE);
3017
3018 if (sql%rowcount = 0) then
3019 fnd_message.set_name('FND', 'FND_INVALID_USER');
3020 fnd_message.set_token('USER_NAME', username);
3021 app_exception.raise_exception;
3022 else
3023 -- Added for Function Security Cache Invalidation
3024 fnd_function_security_cache.delete_user(l_user_id);
3025 end if;
3026
3027 end RemovePendingUser;
3028
3029 ----------------------------------------------------------------------------
3030 --
3031 -- AssignPartyToUser (PUBLIC)
3032 -- Assign a TCA party to a given user
3033 --
3034 -- Usage example in pl/sql
3035 -- begin fnd_user_pkg.assignpartytouser('SCOTT', 1001); end;
3036 --
3037 -- Input (Mandatory)
3038 -- x_user_name: User Name
3039 -- x_party_id: Party Name Id
3040 --
3041 procedure AssignPartyToUser(
3042 x_user_name in varchar2,
3043 x_party_id in number) is
3044 pid number;
3045 begin
3046
3047 --07/03/03: party_name in table hz_parties is not unique. Therefore, change
3048 --the API to take party_id instead of party_name.
3049 /*
3050 -- Get party id
3051 begin
3052 select party_id
3053 into pid
3054 from hz_parties
3055 where upper(party_name) = upper(x_party);
3056 exception
3057 when no_data_found then
3058 fnd_message.set_name('FND', 'FND_INVALID_PARTY');
3059 fnd_message.set_token('PARTY_NAME', x_party);
3060 app_exception.raise_exception;
3061 end;
3062
3063 fnd_user_pkg.UpdateUserParty(x_user_name => x_user_name,
3064 x_owner => 'SEED',
3065 x_person_party_id => pid);
3066 */
3067
3068 fnd_user_pkg.UpdateUserParty(x_user_name => x_user_name,
3069 x_owner => 'SEED',
3070 x_person_party_id => x_party_id);
3071
3072 end AssignPartyToUser;
3073
3074 -- Internal. Called by change_user_name and the two parameters
3075 -- ldap_wrapper_change_user_name.
3076 procedure ldap_wrapper_change_user_name(x_user_guid in raw,
3077 x_old_user_name in varchar2,
3078 x_new_user_name in varchar2) is
3079
3080 l_result number;
3081 reason varchar2(2000);
3082 begin
3083
3084 l_result := null;
3085
3086 fnd_ldap_wrapper.change_user_name(x_user_guid, x_old_user_name,
3087 x_new_user_name, l_result);
3088 if (l_result <> fnd_ldap_wrapper.G_SUCCESS) then
3089 reason := fnd_message.get();
3090 fnd_message.set_name('FND', 'LDAP_WRAPPER_CHANGE_USER_FAIL');
3091 fnd_message.set_token('REASON', reason);
3092 app_exception.raise_exception;
3093 end if;
3094 exception
3095 when others then
3096 fnd_message.set_name('FND', 'LDAP_WRAPPER_CHANGE_USER_FAIL');
3097 fnd_message.set_token('REASON', sqlerrm);
3098 app_exception.raise_exception;
3099 end;
3100
3101 -- begin bug 2504562
3102
3103 ----------------------------------------------------------------------------
3104 --
3105 -- change_user_name (PUBLIC)
3106 -- This api changes username, deals with encryption changes and
3107 -- update foreign keys that were using the old username.
3108 --
3109 -- Usage example in pl/sql
3110 -- begin fnd_user_pkg.change_user_name('SOCTT', 'SCOTT'); end;
3111 --
3112 -- Input (Mandantory)
3113 -- x_old_user_name: Old User Name
3114 -- x_new_user_name: New User Name
3115 --
3116 procedure change_user_name(x_old_user_name in varchar2,
3117 x_new_user_name in varchar2,
3118 x_change_source in number default null) is
3119 newpass varchar2(100);
3120 dummy number(1);
3121 ret boolean;
3122 l_user_id number;
3123 l_user_guid raw(16);
3124 l_api_name CONSTANT varchar2(30) := 'change_user_name';
3125 tmpbuf varchar2(240);
3126 reason varchar2(240);
3127 encpwd varchar2(100);
3128 begin
3129 -- ensure x_old_user_name exists in fnd_user before we proceed
3130 select USER_GUID, encrypted_user_password
3131 into l_user_guid, encpwd
3132 from fnd_user
3133 where user_name = upper(x_old_user_name);
3134
3135 begin
3136 -- ensure x_new_user_name doesn't already exist in fnd_user
3137 select null into dummy from fnd_user
3138 where user_name = upper(x_new_user_name);
3139 fnd_message.set_name('FND', 'SECURITY-DUPLICATE USER');
3140 app_exception.raise_exception;
3141 exception
3142 when no_data_found then
3143 -- Start bug 5866089 just adding the following if check
3144 if (x_change_source is null) then
3145 -- Start bug 4625235
3146 -- Move ldap_wrapper synch call to here before we do FND user update
3147 begin
3148 tmpbuf := 'Calling ldap_wrapper_change_user_name to change '||
3149 x_old_user_name|| ' to '|| x_new_user_name;
3150 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3151 fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
3152 tmpbuf);
3153 end if;
3154 if (l_user_guid is not null) then
3155 ldap_wrapper_change_user_name(l_user_guid, upper(x_old_user_name),
3156 upper(x_new_user_name));
3157 end if;
3158 exception
3159 when others then
3160 app_exception.raise_exception;
3161 end;
3162 -- end bug 4625235
3163
3164 end if;
3165 -- end bug 5866089
3166
3167 -- capture x_old_user_name in package variable g_old_user_name
3168 g_old_user_name := upper(x_old_user_name);
3169
3170 -- change old username to new username
3171 update fnd_user set user_name = upper(x_new_user_name)
3172 where user_name = upper(x_old_user_name);
3173
3174 -- This code was moved before updating FND_USER due to a change in
3175 -- FND_WEB_SEC.change_password having an autonomous transaction pragma.
3176 -- Password changes failed due to said change. See bug 2426407.
3177 -- handle password encryption with new username
3178 tmpbuf := 'Recrypting '||x_new_user_name|| ' password';
3179 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3180 fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
3181 tmpbuf);
3182 end if;
3183
3184 -- Only have to do password reencryption if is not EXTERNAL, INVALID or Hash mode
3185 if ( (encpwd not in ('EXTERNAL', 'INVALID')) and (substr(encpwd, 1, 1) <> 'X') ) then
3186 newpass := fnd_user_pkg.GetReEncryptedPassword(x_new_user_name, 'NEWKEY');
3187 ret := fnd_user_pkg.SetReEncryptedPassword(x_new_user_name,newpass,'NEWKEY');
3188 end if;
3189
3190 tmpbuf := 'updating fnd_user for new user_name '||x_new_user_name;
3191 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3192 fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
3193 tmpbuf);
3194 end if;
3195
3196 -- In the below WHERE clause, 'upper' is added, otherwise if the
3197 -- x_new_user_name contains any small case letters, it will enter into
3198 -- 'NO-DATA-FOUND' (since in the above statement, UPPER(x_new_user_name)
3199 -- is stored) exception and returns FND_CHANGE_USER_FAILED error.
3200 -- Added for Function Security Cache Invalidation
3201 select user_id
3202 into l_user_id
3203 from fnd_user
3204 where user_name = upper(x_new_user_name);
3205
3206 fnd_function_security_cache.update_user(l_user_id);
3207
3208 -- propagate username change to WF and entity mgr
3209 tmpbuf := 'Start calling fnd_user_pkg.user_synch('||x_new_user_name||')';
3210 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3211 fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
3212 tmpbuf);
3213 end if;
3214 begin
3215 fnd_user_pkg.user_synch(upper(x_new_user_name));
3216 exception
3217 when others then
3218 reason := fnd_message.get;
3219 fnd_message.set_name('FND', 'FND_FAILED_WF_USER_SYNCH');
3220 fnd_message.set_token('OLD_NAME', x_old_user_name);
3221 fnd_message.set_token('NEW_NAME', x_new_user_name);
3222 fnd_message.set_token('REASON', reason);
3223 app_exception.raise_exception;
3224 end;
3225
3226 -- Added for bug 4676568
3227 -- A temp fix to update fnd_grants.grantee_key
3228 -- No need to check SQL%NOTFOUND because if there is no data to be
3229 -- updated in fnd_grants.grantee_key, that is perfectly fine.
3230 update fnd_grants
3231 set grantee_key = x_new_user_name
3232 where grantee_key = x_old_user_name
3233 and grantee_type = 'USER';
3234
3235 tmpbuf := 'Finished fnd_user_pkg.user_synch';
3236 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
3237 fnd_log.string(FND_LOG.LEVEL_STATEMENT, C_LOG_HEAD || l_api_name || '.',
3238 tmpbuf);
3239 end if;
3240
3241 end;
3242
3243 exception
3244 when no_data_found then
3245 -- old username does not exist in fnd_user
3246 fnd_message.set_name('FND', 'FND_CHANGE_USER_FAILED');
3247 fnd_message.set_token('USER_NAME', x_old_user_name);
3248 app_exception.raise_exception;
3249
3250
3251 end change_user_name;
3252
3253 ----------------------------------------------------------------------------
3254 --
3255 -- set_old_user_name (PUBLIC)
3256 -- This function is called from Forms to set the global variable,
3257 -- g_old_user_name since this cannot be set directly from Forms.
3258 -- This function returns a number which can be used to check for success
3259 -- from Forms.
3260 --
3261 -- Usage example in pl/sql
3262 -- declare
3263 -- retval number := null;
3264 -- begin retval := fnd_user_pkg.set_old_user_name('SOCTT'); end;
3265 --
3266 -- Input (Mandantory)
3267 -- x_old_user_name: Old User Name
3268 --
3269 function set_old_user_name(x_old_user_name in varchar2) return number is
3270 retval number := null;
3271 begin
3272 g_old_user_name := x_old_user_name;
3273 if (g_old_user_name is not null) then
3274 retval := 1;
3275 else
3276 retval := 0;
3277 end if;
3278 return retval;
3279 end;
3280
3281 -- end bug 2504562
3282
3283 ----------------------------------------------------------------------------
3284 -- MergePartyId (PUBLIC)
3285 -- This is the procedure being called during the Party Merge.
3286 -- FND_USER.MergePartyId() has been registered in Party Merge Data Dict.
3287 -- The input/output arguments format matches the document PartyMergeDD.doc.
3288 -- The goal is to fix the person_party_id in fnd_user table to point to the
3289 -- same party when two similar parties are begin merged.
3290 --
3291 -- Usage example in pl/sql
3292 -- This procedure should only be called from the PartyMerge utility.
3293 --
3294 procedure MergePartyId(p_entity_name in varchar2,
3295 p_from_id in number,
3296 p_to_id in out nocopy number,
3297 p_from_fk_id in number,
3298 p_to_fk_id in number,
3299 p_parent_entity_name in varchar2,
3300 p_batch_id in number,
3301 p_batch_party_id in number,
3302 p_return_status in out nocopy varchar2) is
3303 begin
3304 p_return_status := FND_API.G_RET_STS_SUCCESS;
3305
3306 if (p_from_fk_id <> p_to_fk_id) then
3307
3308 update fnd_user
3309 set person_party_id = p_to_fk_id
3310 where person_party_id = p_from_fk_id;
3311
3312 -- Added for Function Security Cache Invalidation
3313 fnd_function_security_cache.update_user(p_from_id);
3314
3315 end if;
3316
3317 end MergePartyId;
3318
3319 -- Public function
3320 -- Make sure that the user_name does not contain invalid character.
3321 -- For now: We only care about '/' and ':' because they are known problem.
3322 -- 01/19/05: we now have more invalid characters info from bug 4116239, so
3323 -- I am adding more characters.
3324 -- Rewrite later: checking for any non-printable character.
3325 -- make sure multibyte character is ok.
3326 procedure validate_user_name(x_user_name in varchar2) is
3327 msg varchar2(2000);
3328 uname varchar2(100);
3329 begin
3330 if (x_user_name is null or
3331 rtrim(x_user_name, ' ') is null) then
3332 fnd_message.set_name('FND', 'INVALID_USER_NAME_NULL');
3333 app_exception.raise_exception;
3334 elsif (rtrim(x_user_name, ' ') <> x_user_name or
3335 ltrim(x_user_name, ' ') <> x_user_name) then
3336 fnd_message.set_name('FND', 'INVALID_USER_NAME_SPACE');
3337 app_exception.raise_exception;
3338 elsif (instr(x_user_name, '/') > 0 OR
3339 instr(x_user_name, '"') > 0 OR
3340 instr(x_user_name, '(') > 0 OR
3341 instr(x_user_name, ')') > 0 OR
3342 instr(x_user_name, '*') > 0 OR
3343 instr(x_user_name, '+') > 0 OR
3344 instr(x_user_name, ',') > 0 OR
3345 instr(x_user_name, ';') > 0 OR
3346 instr(x_user_name, '<') > 0 OR
3347 instr(x_user_name, '>') > 0 OR
3348 instr(x_user_name, '\') > 0 OR
3349 instr(x_user_name, '~') > 0 OR
3350 instr(x_user_name, ':') > 0 ) then
3351 fnd_message.set_name('FND', 'INVALID_USER_NAME');
3352 fnd_message.set_token('UNAME', x_user_name);
3353 app_exception.raise_exception;
3354 else
3355 -- we pass the generic validation, it is time to call any
3356 -- customized user name validation if there is any.
3357 begin
3358 wf_event.raise(p_event_name => 'oracle.apps.fnd.user.name.validate',
3359 p_event_key => x_user_name,
3360 p_event_data => NULL,
3361 p_parameters => NULL,
3362 p_send_date => Sysdate);
3363 exception
3364 when others then
3365 msg := fnd_message.get_encoded;
3366 if (msg is not null) then
3367 fnd_message.set_encoded(msg);
3368 else
3369 fnd_message.set_name('FND', 'FND_CUST_UNAME_VALIDATE_FAILED');
3370 end if;
3371 app_exception.raise_exception;
3372 end;
3373 -- past generic and customized validation
3374 end if;
3375
3376 end validate_user_name;
3377
3378 --
3379 -- CreateUser (PUBLIC)
3380 --
3381 -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
3382 -- Overloaded procedure to create user
3383 -- Accepts User GUID as a parameter in addition to the other parameters
3384 --
3385 --
3386 procedure CreateUser (
3387 x_user_name in varchar2,
3388 x_owner in varchar2,
3389 x_unencrypted_password in varchar2 default null,
3390 x_session_number in number default 0,
3391 x_start_date in date default sysdate,
3392 x_end_date in date default null,
3393 x_last_logon_date in date default null,
3394 x_description in varchar2 default null,
3395 x_password_date in date default null,
3396 x_password_accesses_left in number default null,
3397 x_password_lifespan_accesses in number default null,
3398 x_password_lifespan_days in number default null,
3399 x_employee_id in number default null,
3400 x_email_address in varchar2 default null,
3401 x_fax in varchar2 default null,
3402 x_customer_id in number default null,
3403 x_supplier_id in number default null,
3404 x_user_guid in raw,
3405 x_change_source in number default null)
3406 is
3407 l_result number;
3408 begin
3409 l_result := fnd_user_pkg.CreateUserId(
3410 x_user_name,
3411 x_owner,
3412 x_unencrypted_password,
3413 x_session_number,
3414 x_start_date,
3415 x_end_date,
3416 x_last_logon_date,
3417 x_description,
3418 x_password_date,
3419 x_password_accesses_left,
3420 x_password_lifespan_accesses,
3421 x_password_lifespan_days,
3422 x_employee_id,
3423 x_email_address,
3424 x_fax,
3425 x_customer_id,
3426 x_supplier_id,
3427 x_user_guid,
3428 x_change_source);
3429 end CreateUser;
3430
3431 ----------------------------------------------------------------------
3432 --
3433 -- CreateUserId (PUBLIC)
3434 --
3435 -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
3436 -- Overloaded procedure to create user
3437 -- Accepts User GUID as a parameter in addition to the other parameters
3438 --
3439 -- Returns
3440 -- User_id of created user
3441 --
3442
3443 function CreateUserId (
3444 x_user_name in varchar2,
3445 x_owner in varchar2,
3446 x_unencrypted_password in varchar2 default null,
3447 x_session_number in number default 0,
3448 x_start_date in date default sysdate,
3449 x_end_date in date default null,
3450 x_last_logon_date in date default null,
3451 x_description in varchar2 default null,
3452 x_password_date in date default null,
3453 x_password_accesses_left in number default null,
3454 x_password_lifespan_accesses in number default null,
3455 x_password_lifespan_days in number default null,
3456 x_employee_id in number default null,
3457 x_email_address in varchar2 default null,
3458 x_fax in varchar2 default null,
3459 x_customer_id in number default null,
3460 x_supplier_id in number default null,
3461 x_user_guid in raw,
3462 x_change_source in number default null)
3463 return number is
3464 begin
3465 return CreateUserIdInternal(
3466 x_user_name => x_user_name,
3467 x_owner => x_owner,
3468 x_unencrypted_password => x_unencrypted_password,
3469 x_session_number => x_session_number,
3470 x_start_date => x_start_date,
3471 x_end_date => x_end_date,
3472 x_last_logon_date => x_last_logon_date,
3473 x_description => x_description,
3474 x_password_date => x_password_date,
3475 x_password_accesses_left => x_password_accesses_left,
3476 x_password_lifespan_accesses => x_password_lifespan_accesses,
3477 x_password_lifespan_days => x_password_lifespan_days,
3478 x_employee_id => x_employee_id,
3479 x_email_address => x_email_address,
3480 x_fax => x_fax,
3481 x_customer_id => x_customer_id,
3482 x_supplier_id => x_supplier_id,
3483 x_person_party_id => null,
3484 x_mode => 'EMPLOYEE',
3485 x_user_guid => x_user_guid,
3486 x_change_source => x_change_source);
3487 end CreateUserId;
3488
3489 ----------------------------------------------------------------------
3490 --
3491 -- UpdateUser (Public)
3492 --
3493 -- Bug#3904339 - SSO: Add user_guid parameter in fnd_user_pkg apis
3494 -- Overloaded procedure to update user
3495 -- Accepts User GUID as a parameter in addition to the other parameters
3496 --
3497 procedure UpdateUser (
3498 x_user_name in varchar2,
3499 x_owner in varchar2,
3500 x_unencrypted_password in varchar2 default null,
3501 x_session_number in number default null,
3502 x_start_date in date default null,
3503 x_end_date in date default null,
3504 x_last_logon_date in date default null,
3505 x_description in varchar2 default null,
3506 x_password_date in date default null,
3507 x_password_accesses_left in number default null,
3508 x_password_lifespan_accesses in number default null,
3509 x_password_lifespan_days in number default null,
3510 x_employee_id in number default null,
3511 x_email_address in varchar2 default null,
3512 x_fax in varchar2 default null,
3513 x_customer_id in number default null,
3514 x_supplier_id in number default null,
3515 x_old_password in varchar2 default null,
3516 x_user_guid in raw,
3517 x_change_source in number default null)
3518 is
3519 begin
3520 UpdateUserInternal(
3521 x_user_name => x_user_name,
3522 x_owner => x_owner,
3523 x_unencrypted_password => x_unencrypted_password,
3524 x_session_number => x_session_number,
3525 x_start_date => x_start_date,
3526 x_end_date => x_end_date,
3527 x_last_logon_date => x_last_logon_date,
3528 x_description => x_description,
3529 x_password_date => x_password_date,
3530 x_password_accesses_left => x_password_accesses_left,
3531 x_password_lifespan_accesses => x_password_lifespan_accesses,
3532 x_password_lifespan_days => x_password_lifespan_days,
3533 x_employee_id => x_employee_id,
3534 x_email_address => x_email_address,
3535 x_fax => x_fax,
3536 x_customer_id => x_customer_id,
3537 x_supplier_id => x_supplier_id,
3538 x_person_party_id => null,
3539 x_old_password => x_old_password,
3540 x_mode => 'EMPLOYEE',
3541 x_user_guid => x_user_guid,
3542 x_change_source => x_change_source);
3543 end UpdateUser;
3544
3545 ----------------------------------------------------------------------
3546 -- userExists (Public)
3547 --
3548 -- This function checks if the user exists and returnes 'True' or 'False'
3549 -- Input (Mandatory)
3550 -- username: User Name
3551
3552 function userExists(x_user_name in varchar2) return boolean is
3553 dummy number;
3554 begin
3555 select 1 into dummy from fnd_user
3556 where user_name = upper(x_user_name);
3557 return TRUE;
3558 exception
3559 when no_data_found then
3560 return FALSE;
3561 end userExists;
3562
3563 -- begin bug 4318754, 4424225
3564 ----------------------------------------------------------------------------
3565 --
3566 -- TestUserName (PUBLIC)
3567 -- This api test whether a username exists in FND and/or in OID.
3568 --
3569 -- Usage example in pl/sql
3570 -- declare ret number;
3571 -- begin ret := fnd_user_pkg.testusername('SOCTT'); end;
3572 --
3573 -- Input (Mandantory)
3574 -- x_user_name: User Name that you want to test
3575 --
3576 -- Output
3577 -- USER_INVALID_NAME : User name is not valid
3578 -- USER_OK_CREATE : User does not exist in either FND or OID
3579 -- USER_EXISTS_IN_FND : User exists in FND
3580 -- USER_SYNCH : User exists in OID and next time when this user gets created
3581 -- in FND, the two will be synched together.
3582 -- USER_EXISTS_NO_LINK_ALLOWED: User exists in OID and no synching allowed.
3583 --
3584 function TestUserName(x_user_name in varchar2) return pls_integer is
3585 pf varchar2(1);
3586 retval pls_integer;
3587 begin
3588
3589 pf := 'N';
3590
3591 begin
3592 fnd_user_pkg.validate_user_name(x_user_name);
3593 exception
3594 when others then
3595 -- error message is already on the stack from validate_user_name()
3596 -- Either a generic validation error message or specific message from
3597 -- the subscriber of "fnd.user.name.validate"
3598 return(USER_INVALID_NAME);
3599 end;
3600
3601 if (fnd_user_pkg.userExists(x_user_name)) then
3602 fnd_message.set_name('FND', 'FND_USER_EXISTS_IN_FND');
3603 return(USER_EXISTS_IN_FND);
3604 else
3605 begin
3606 retval := fnd_ldap_wrapper.user_exists(x_user_name);
3607 exception
3608 when others then
3609 app_exception.raise_exception;
3610 end;
3611
3612 if (retval = 1) then
3613 -- The above check return that user exists in oid.
3614 fnd_profile.get('APPS_SSO_LINK_SAME_NAMES', pf);
3615
3616 if(pf = 'Y') then
3617 fnd_message.set_name('FND', 'FND_USER_SYNCHED');
3618 fnd_message.set_token('USER_NAME', x_user_name);
3619 return(USER_SYNCHED);
3620 -- next time when this user gets created in fnd, it will be
3621 -- linked to each other.
3622 else
3623 fnd_message.set_name('FND', 'FND_USER_EXISTS_NO_LINK');
3624 return(USER_EXISTS_NO_LINK_ALLOWED);
3625 end if;
3626 else
3627 return(USER_OK_CREATE);
3628 end if;
3629 end if;
3630
3631 end TestUserName;
3632
3633
3634 ----------------------------------------------------------------------------
3635 --
3636 -- set_old_person_party_id (PUBLIC)
3637 -- This function is called from Forms to set the global variable,
3638 -- g_old_person_party_id since this cannot be set directly from Forms.
3639 -- This function returns a number which can be used to check for success
3640 -- from Forms.
3641 --
3642 -- Usage example in pl/sql
3643 -- declare
3644 -- retval number := null;
3645 -- begin retval := fnd_user_pkg.set_old_person_party_id(12345); end;
3646 --
3647 -- Input (Mandantory)
3648 -- x_old_person_party_id: Old Person Party Id
3649 --
3650 function set_old_person_party_id(x_old_person_party_id in varchar2)
3651 return number is
3652 retval number := null;
3653 begin
3654 g_old_person_party_id := x_old_person_party_id;
3655 if (g_old_person_party_id is not null) then
3656 retval := 1;
3657 else
3658 retval := 0;
3659 end if;
3660 return retval;
3661 end;
3662
3663
3664 ----------------------------------------------------------------------------
3665 --
3666 -- set_old_user_guid (PUBLIC)
3667 -- This function is called from Forms to set the global variable,
3668 -- g_old_user_guid since this cannot be set directly from Forms.
3669 -- This function returns a number which can be used to check for success
3670 -- from Forms.
3671 --
3672 -- Usage example in pl/sql
3673 -- declare
3674 -- retval number := null;
3675 -- guid raw(16);
3676 -- begin
3677 -- guid := 'F9374D4B80AB1A86E034080020B2612C';
3678 -- retval := fnd_user_pkg.set_old_user_guid(guid); end;
3679 --
3680 -- Input (Mandantory)
3681 -- x_old_user_guid: Old USER GUID
3682 --
3683 function set_old_user_guid(x_old_user_guid in raw)
3684 return number is
3685 retval number := null;
3686 begin
3687 g_old_user_guid := x_old_user_guid;
3688 if (g_old_person_party_id is not null) then
3689 retval := 1;
3690 else
3691 retval := 0;
3692 end if;
3693 return retval;
3694 end;
3695
3696 -- Internal
3697 function ldap_wrp_update_user_helper(x_user_name in varchar2,
3698 x_unencrypted_password in varchar2,
3699 x_start_date in date,
3700 x_end_date in date,
3701 x_description in varchar2,
3702 x_email_address in varchar2,
3703 x_fax in varchar2,
3704 x_expire_pwd in pls_integer) return varchar2
3705 is
3706 l_user_guid raw(16);
3707 l_result number;
3708 reason varchar2(2000);
3709 l_pwd varchar2(1000);
3710 pwdCaseOpt varchar2(1);
3711 l_pwd_ret varchar2(1000);
3712 userid number; -- bug 5162136
3713 isOverrideFuncAssigned boolean; /* 7043484 */
3714 begin
3715
3716 l_user_guid := null;
3717 l_result := null;
3718 l_pwd_ret := null;
3719 userid := null;
3720
3721 -- bug 5162136 Obtain the user_id for later use
3722 select user_guid, user_id into l_user_guid, userid
3723 from fnd_user
3724 where user_name = x_user_name;
3725
3726 if (l_user_guid is null) then
3727 return null;
3728 end if;
3729
3730 -- Only check the profile and expiration if password is not null
3731 l_pwd := x_unencrypted_password;
3732 if (x_unencrypted_password is not null) then
3733 -- Bug 5161497
3734 -- If profile is not set or case insensitive, then password passed to
3735 -- sso/oid should be lower. This has been discussed with SSO to
3736 -- reach agreement because entering lower case is more common for case
3737 -- insensitive mode.
3738 pwdCaseOpt := null;
3739 -- 5162136 SIGNON_PASSWORD_CASE Profile Check
3740 -- Get the profile value at the user level for the affected user and
3741 -- encrypt accordingly. If not set at the user level, this will default to
3742 -- the site level profile value.
3743 /* Code change for bug 7043484 */
3744 isOverrideFuncAssigned := fnd_function.test('OVERRIDE_PASSWORD_POLICY_PERM','N');
3745 if(isOverrideFuncAssigned = true) then
3746 pwdCaseOpt := fnd_profile.value('SIGNON_PASSWORD_CASE');
3747 else
3748 pwdCaseOpt := fnd_profile.value_specific('SIGNON_PASSWORD_CASE', userid);
3749 end if;
3750 if (pwdCaseOpt is null) or (pwdCaseOpt = '1') then
3751 l_pwd := lower(x_unencrypted_password);
3752 end if;
3753 end if; -- End if x_unencrypted_password is not null
3754
3755 begin
3756
3757 fnd_ldap_wrapper.update_user(l_user_guid, x_user_name,
3758 l_pwd, x_start_date, x_end_date, x_description,
3759 x_email_address, x_fax, x_expire_pwd, l_pwd_ret, l_result);
3760
3761 if (l_result <> fnd_ldap_wrapper.G_SUCCESS) then
3762 reason := fnd_message.get();
3763 fnd_message.set_name('FND', 'LDAP_WRAPPER_UPDATE_USER_FAIL');
3764 fnd_message.set_token('USER_NAME', x_user_name);
3765 fnd_message.set_token('REASON', reason);
3766 app_exception.raise_exception;
3767 -- Bug 5605892
3768 end if;
3769
3770 -- Bug 5605892
3771 if (l_pwd_ret = fnd_web_sec.external_pwd) then
3772 return('EXTERNAL');
3773 else
3774 return(null);
3775 end if;
3776 exception
3777 when others then
3778 fnd_message.set_name('FND', 'LDAP_WRAPPER_UPDATE_USER_FAIL');
3779 fnd_message.set_token('USER_NAME', x_user_name);
3780 fnd_message.set_token('REASON', sqlerrm);
3781 app_exception.raise_exception;
3782 end;
3783 exception
3784 when others then
3785 app_exception.raise_exception;
3786 end;
3787
3788 ----------------------------------------------------------------------------
3789 --
3790 -- ldap_wrapper_update_user (PUBLIC)
3791 -- This is called by the fnd_user_pkg and fnd_web_sec
3792 -- It serves as a helper routine to call fnd_ldap_wrapper.update_user
3793 -- when we need to synch the user update to OID.
3794 -- Note
3795 -- Please note that even this is public procedure, it does not mean for
3796 -- other public usage. This is mainly created as a helper routine to
3797 -- service the user form and the user package.
3798 procedure ldap_wrapper_update_user(x_user_name in varchar2,
3799 x_unencrypted_password in varchar2,
3800 x_start_date in date,
3801 x_end_date in date,
3802 x_description in varchar2,
3803 x_email_address in varchar2,
3804 x_fax in varchar2,
3805 x_expire_pwd in pls_integer default 0) is
3806 l_pwd_ret varchar2(100);
3807 begin
3808
3809 l_pwd_ret := null;
3810
3811 l_pwd_ret := ldap_wrp_update_user_helper(x_user_name,
3812 x_unencrypted_password, x_start_date, x_end_date, x_description,
3813 x_email_address, x_fax, x_expire_pwd);
3814 if (l_pwd_ret is not null) then
3815 -- If the return password from ldap is not null, that means ldap
3816 -- is informing us that this user is externally managed so we need
3817 -- to update the password to EXTERNAL.
3818 update fnd_user
3819 set encrypted_foundation_password = l_pwd_ret,
3820 encrypted_user_password = l_pwd_ret
3821 where user_name = upper(x_user_name);
3822 end if;
3823
3824 end;
3825
3826
3827 --
3828 -- ldap_wrapper_create_user (PUBLIC)
3829 -- This is called by user form and the fnd_user_pkg.
3830 -- It serves as a helper routine to call fnd_ldap_wrapper.create_user
3831 -- when we need to synch that new FND user to OID.
3832 -- It also takes care of updating fnd_user with the user_guid and oid_pwd
3833 -- coming back from ldap_wrapper layer.
3834 -- Note
3835 -- Please note that even this is public procedure, it does not mean for
3836 -- other public usage. This is mainly created as a helper routine to
3837 -- service the user form and the user package.
3838
3839 procedure ldap_wrapper_create_user(x_user_name in varchar2,
3840 x_unencrypted_password in varchar2,
3841 x_start_date in date,
3842 x_end_date in date,
3843 x_description in varchar2,
3844 x_email_address in varchar2,
3845 x_fax in varchar2,
3846 x_expire_pwd in pls_integer default 0) is
3847 l_user_guid raw(16);
3848 l_oid_pwd varchar2(30);
3849 ret varchar2(1);
3850 reason varchar2(2000);
3851
3852 begin
3853
3854 l_user_guid := null;
3855 l_oid_pwd := null;
3856 ldap_wrapper_create_user(x_user_name, x_unencrypted_password,
3857 x_start_date, x_end_date,
3858 x_description,x_email_address,x_fax,x_expire_pwd,
3859 l_user_guid, l_oid_pwd);
3860 if (l_user_guid is not null) then
3861 update fnd_user
3862 set user_guid = l_user_guid
3863 where user_name = x_user_name;
3864 end if;
3865
3866 if (l_oid_pwd = fnd_web_sec.external_pwd) then
3867 -- Add third argument to not use autonomous transaction when chaning
3868 -- passowrd. This is for bug 5087728
3869 ret := fnd_web_sec.change_password(x_user_name, l_oid_pwd, FALSE);
3870 if (ret = 'N') then
3871 reason := fnd_message.get();
3872 fnd_message.set_name('FND', 'FND_CHANGE_PASSWORD_FAILED');
3873 fnd_message.set_token('USER_NAME', X_USER_NAME);
3874 fnd_message.set_token('REASON', reason);
3875 app_exception.raise_exception;
3876 end if;
3877 end if;
3878
3879 exception
3880 when others then
3881 app_exception.raise_exception;
3882 end;
3883
3884 -- end bug 4318754
3885
3886 --
3887 -- ldap_wrapper_change_user_name (PUBLIC)
3888 -- This is called by user form. When there is user name changed inside
3889 -- User form, we need to synch with ldap.
3890 --
3891 -- Note
3892 -- Please note that even this is public procedure, it does not mean for
3893 -- other public usage. This is mainly created as a helper routine to
3894 -- service the user form and the user package.
3895 procedure ldap_wrapper_change_user_name(x_old_user_name in varchar2,
3896 x_new_user_name in varchar2) is
3897
3898 l_user_guid raw(16);
3899 begin
3900
3901 l_user_guid := null;
3902
3903 select user_guid
3904 into l_user_guid
3905 from fnd_user
3906 where user_name = x_old_user_name;
3907
3908 if (l_user_guid is not null) then
3909 ldap_wrapper_change_user_name(l_user_guid, x_old_user_name, x_new_user_name);
3910 end if;
3911
3912 exception
3913 when others then
3914 app_exception.raise_exception;
3915 end;
3916
3917 ----------------------------------------------------------------------------
3918 --
3919 -- form_ldap_wrapper_update_user (PUBLIC)
3920 -- This is called by user form.
3921 -- It serves as a helper routine to call fnd_ldap_wrapper.update_user
3922 -- when we need to synch the user update to OID.
3923 -- Note
3924 -- Please note that even this is public procedure, it does not mean for
3925 -- other public usage. This is mainly created as a helper routine to
3926 -- service the user form.
3927 procedure form_ldap_wrapper_update_user(x_user_name in varchar2,
3928 x_unencrypted_password in varchar2,
3929 x_start_date in date,
3930 x_end_date in date,
3931 x_description in varchar2,
3932 x_email_address in varchar2,
3933 x_fax in varchar2,
3934 x_out_pwd in out nocopy varchar2) is
3935 l_end_date date;
3936 l_description varchar2(240);
3937 l_email_address varchar2(240);
3938 l_fax varchar2(80);
3939 l_pwd_ret varchar2(1000);
3940 begin
3941
3942 -- Bug 5161134
3943 -- If is from Form, we can not use our rule about null means no change.
3944 -- If form passs in null value, that means change it to null.
3945 if (x_end_date is null) then
3946 l_end_date := fnd_user_pkg.null_date;
3947 else
3948 l_end_date := x_end_date;
3949 end if;
3950
3951 if (x_description is null) then
3952 l_description := fnd_user_pkg.null_char;
3953 else
3954 l_description := x_description;
3955 end if;
3956
3957 if (x_email_address is null) then
3958 l_email_address := fnd_user_pkg.null_char;
3959 else
3960 l_email_address := x_email_address;
3961 end if;
3962
3963 if (x_fax is null) then
3964 l_fax := fnd_user_pkg.null_char;
3965 else
3966 l_fax := x_fax;
3967 end if;
3968
3969 -- Call our wrapper update helper. Passing G_TRUE for expiring password
3970 -- because any password update in USER form should result password
3971 -- expiration.
3972 l_pwd_ret := ldap_wrp_update_user_helper(x_user_name,
3973 x_unencrypted_password, x_start_date, l_end_date, l_description,
3974 l_email_address, l_fax, fnd_ldap_wrapper.G_TRUE );
3975
3976 -- Return the ldap out password to User form.
3977 x_out_pwd := l_pwd_ret;
3978
3979 exception
3980 when others then
3981 app_exception.raise_exception;
3982 end;
3983
3984 ----------------------------------------------------------------------------
3985 -- This routine is for AOL INTERNAL USE ONLY !!!!!!!
3986 --
3987 -- ldap_wrp_update_user_loader
3988 -- This is called by the fnd_user_pkg and fnd_web_sec.
3989 -- It serves as a helper routine to call fnd_ldap_wrapper.update_user
3990 -- when we need to synch the user update to OID.
3991 procedure ldap_wrp_update_user_loader(x_user_name in varchar2,
3992 x_hashed_password in varchar2,
3993 x_start_date in date,
3994 x_end_date in date,
3995 x_description in varchar2,
3996 x_email_address in varchar2,
3997 x_fax in varchar2,
3998 x_expire_pwd in pls_integer default 1)
3999 is
4000 l_pwd_ret varchar2(100);
4001 l_user_guid raw(16);
4002 l_result number;
4003 reason varchar2(2000);
4004 userid number; -- bug 5162136
4005 begin
4006
4007 l_user_guid := null;
4008 l_result := null;
4009 l_pwd_ret := null;
4010 userid := null;
4011
4012 select user_guid, user_id into l_user_guid, userid
4013 from fnd_user
4014 where user_name = x_user_name;
4015
4016 if (l_user_guid is null) then
4017 return;
4018 end if;
4019
4020 fnd_ldap_wrapper.update_user(l_user_guid, x_user_name,
4021 x_hashed_password, x_start_date, x_end_date, x_description,
4022 x_email_address, x_fax, x_expire_pwd, l_pwd_ret, l_result);
4023
4024 if (l_result <> fnd_ldap_wrapper.G_SUCCESS) then
4025 reason := fnd_message.get();
4026 fnd_message.set_name('FND', 'LDAP_WRAPPER_UPDATE_USER_FAIL');
4027 fnd_message.set_token('USER_NAME', x_user_name);
4028 fnd_message.set_token('REASON', reason);
4029 app_exception.raise_exception;
4030 end if;
4031
4032 if (l_pwd_ret = fnd_web_sec.external_pwd) then
4033 update fnd_user
4034 set encrypted_foundation_password = 'EXTERNAL',
4035 encrypted_user_password = 'EXTERNAL'
4036 where user_name = upper(x_user_name);
4037 end if;
4038
4039 exception
4040 when others then
4041 fnd_message.set_name('FND', 'LDAP_WRAPPER_UPDATE_USER_LOADER_FAIL');
4042 fnd_message.set_token('USER_NAME', x_user_name);
4043 fnd_message.set_token('REASON', sqlerrm);
4044 app_exception.raise_exception;
4045
4046 end;
4047
4048 end FND_USER_PKG;
[Home] [Help]