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