[Home] [Help]
PACKAGE BODY: APPS.UMX_PASSWORD_PVT
Source
1 PACKAGE BODY UMX_PASSWORD_PVT AS
2 /* $Header: UMXVUPWB.pls 120.5.12020000.2 2012/07/18 14:34:53 avelu ship $ */
3
4 g_itemtype wf_item_types.name%type := 'UMXUPWD';
5 -- Private function to get the email address of the active user from
6 -- 1) WF local roles
7 -- 2) FND User
8 -- 3) The first TCA party
9 procedure get_email_address (p_user_name in fnd_user.user_name%type,
10 x_role_name out nocopy varchar2,
11 x_email_address out nocopy varchar2,
12 x_notification_preference out nocopy varchar2,
13 x_message_name out nocopy varchar2,
14 x_message_data out nocopy varchar2) is
15
16 -- TCA Party declares email address as varchar2 2000, largest amount the
17 -- three schema.
18 l_role_display_name wf_local_roles.display_name%type;
19 l_language wf_local_roles.language%type;
20 l_territory wf_local_roles.territory%type;
21
22 cursor get_fnd_email (p_user_name in fnd_user.user_name%type) is
23 SELECT email_address
24 FROM fnd_user
25 WHERE user_name = p_user_name
26 AND start_date <= sysdate
27 AND nvl(end_date, sysdate + 1) > sysdate;
28
29 cursor get_tca_email (p_user_name in fnd_user.user_name%type) is
30 SELECT hzp.email_address
31 FROM hz_parties hzp, fnd_user fu
32 WHERE hzp.party_id = fu.person_party_id
33 AND fu.user_name = p_user_name;
34
35 begin
36
37 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
38 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
39 'fnd.plsql.UMXVUPWB.get_email_address.begin',
40 'p_user_name: ' || p_user_name);
41 end if;
42
43 -- First get the email from wf directory
44 WF_DIRECTORY.GetRoleInfo (p_user_name, l_role_display_name, x_email_address,
45 x_notification_preference, l_language, l_territory);
46
47 if x_email_address is not null then
48 x_role_name := p_user_name;
49 else
50 -- Try to get the email from fnd_user
51 open get_fnd_email (p_user_name);
52 fetch get_fnd_email into x_email_address;
53 if (get_fnd_email%NOTFOUND) then
54 x_message_name := 'UMX_FORGOT_PWD_INVALID_ACCT';
55 fnd_message.set_name('FND', x_message_name);
56 x_message_data := fnd_message.get;
57 else
58 if x_email_address is null then
59 -- if email is still null then get it from tca
60 -- check if there is a valid party email
61 for party in get_tca_email (p_user_name) loop
62 x_email_address := party.email_address;
63 exit when x_email_address is not null;
64 end loop;
65 end if;
66 end if;
67 close get_fnd_email;
68 end if;
69
70 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
71 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
72 'fnd.plsql.UMXVUPWB.get_email_address.end',
73 'x_role_name: ' || x_role_name ||
74 ' | x_email_address: ' || x_email_address ||
75 ' | x_message_name: ' || x_message_name ||
76 ' | x_message_data: ' || x_message_data);
77 end if;
78
79 end get_email_address;
80
81 function get_username_from_userid (p_user_id in fnd_user.user_id%type) return fnd_user.user_name%type is
82
83 cursor get_username_from_userid is
84 select user_name
85 from fnd_user
86 where user_id = p_user_id;
87
88 l_user_name fnd_user.user_name%type;
89
90 begin
91
92 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
93 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
94 'fnd.plsql.UMXVUPWB.get_username_from_userid.begin',
95 'p_user_id: ' || p_user_id);
96 end if;
97
98 open get_username_from_userid;
99 fetch get_username_from_userid into l_user_name;
100 if (get_username_from_userid%notfound) then
101 close get_username_from_userid;
102 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
103 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
104 'fnd.plsql.UMXVUPWB.ForgotPwd',
105 'Username cannot be found with userid (' || p_user_id || ')');
106 end if;
107 fnd_message.set_name ('FND', 'UMX_COMMON_UNEXPECTED_ERR_MSG');
108 raise_application_error ('-20000', fnd_message.get);
109 end if;
110 close get_username_from_userid;
111
112 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
113 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
114 'fnd.plsql.UMXVUPWB.get_username_from_userid.end',
115 'l_user_name: ' || l_user_name);
116 end if;
117
118 return l_user_name;
119
120 end;
121
122 -- Procedure : validate_password
123 -- Type : Private
124 -- Pre_reqs :
125 -- Description: This API will validate the user's password.
126 -- Parameters
127 -- input parameters :
128 -- p_username - username of the password's owner
129 -- p_password - password to validate
130 -- output parameters:
131 -- x_return_status - Returns FND_API.G_RET_STS_SUCCESS if success
132 -- - Returns FND_API.G_RET_STS_ERROR if failed
133 -- x_message_data - Reason why it is failed.
134 -- Errors :
135 -- Other Comments :
136 Procedure validate_password (p_username in fnd_user.user_name%type,
137 p_password in varchar2,
138 x_return_status out NOCOPY varchar2,
139 x_message_data out NOCOPY varchar2) is
140
141 BEGIN
142
143 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
144 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
145 'fnd.plsql.UMXVUPWB.validate_password.begin',
146 'p_username: ' || p_username ||
147 ' | p_password: ' || p_password);
148 end if;
149
150 -- Get username from user id if username is null
151 if (fnd_web_sec.validate_password (p_username, p_password) = 'N') then
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 x_message_data := fnd_message.get ();
154 else
155 x_return_status := FND_API.G_RET_STS_SUCCESS;
156 end if;
157
158 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
159 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
160 'fnd.plsql.UMXVUPWB.validate_password.end',
161 'x_return_status: ' || x_return_status ||
162 ' | x_message_data: ' || x_message_data);
163 end if;
164
165 END;
166
167 /**
168 * Function : generate_password
169 * Type : Private
170 * Pre_reqs :
171 * Description : Creates a password. The length of the password is obtained
172 * from the profile SIGNON_PASSWORD_LENGTH.
173 * Parameters
174 * input parameters : None
175 * output parameters
176 * @return returns a String that can be used as the password
177 * Errors :
178 * Other Comments :
179 */
180
181 function generate_password(p_username in fnd_user.user_name%type) return varchar2 is
182
183 l_password_len int := 6;
184 x_password varchar2(40);
185 ascii_offset int := 65;
186 user_id number;
187
188 begin
189
190 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
191 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
192 'fnd.plsql.UMXVUPWB.generate_password.begin', '');
193 end if;
194
195 -- initialize the random number generator
196 --dbms_random.initialize(dbms_utility.get_time);
197
198 -- using the profile, determine the length of the random number
199 begin
200 select USER_ID into USER_ID from FND_USER where USER_NAME= P_USERNAME;
201 EXCEPTION
202 when NO_DATA_FOUND then
203 USER_ID := -1;
204 end;
205 l_password_len := greatest(nvl(to_number(fnd_profile.VALUE_SPECIFIC('SIGNON_PASSWORD_LENGTH',user_id)), l_password_len), l_password_len);
206
207 -- generate a random number to determine where to use an alphabet or a
208 -- numeric character for a given position in the password
209
210 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
211 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
212 'fnd.plsql.UMXVUPWB.generate_password',
213 'l_password_len: ' || l_password_len);
214 end if;
215
216 for j in 1..l_password_len loop
217 if (mod(abs(dbms_random.random),2) = 1) then
218 -- generate number
219 x_password := x_password || mod(abs(FND_CRYPTO.SmallRandomNumber),10);
220 else
221 -- generate character
222 x_password := x_password || fnd_global.local_chr(mod(abs(FND_CRYPTO.SmallRandomNumber),26)
223 + ascii_offset);
224 end if;
225 end loop;
226
227 -- terminate the random number generator
228 --dbms_random.terminate;
229
230 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
231 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
232 'fnd.plsql.UMXVUPWB.generate_password.end',
233 'x_password: ' || x_password);
234 end if;
235
236 return x_password;
237
238 end generate_password;
239
240 Procedure SetPassword (p_username in fnd_user.user_name%type,
241 x_password in out NOCOPY varchar2) is
242
243 l_result varchar2(10);
244 v_counter BINARY_INTEGER := 1;
245
246 begin
247
248 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
249 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
250 'fnd.plsql.UMXVUPWB.SetPassword.begin',
251 'p_username: ' || p_username ||
252 ' | x_password: ' || x_password);
253 end if;
254
255 if (x_password is null) then
256 x_password := generate_password(p_username);
257
258 -- code for validating the generated username
259
260 -- loop till password clears the validations
261 l_result := FND_WEB_SEC.validate_password (p_username, x_password);
262 WHILE ((l_result <> 'Y') AND (v_counter <= 100)) LOOP
263 v_counter := v_counter + 1;
264 x_password := generate_password(p_username);
265 l_result := FND_WEB_SEC.validate_password (p_username, x_password);
266 END LOOP;
267
268 IF (( v_counter > 100 ) and ( l_result <> 'Y' )) THEN
269 -- Throw exception as even though generated password 100 times, but
270 -- cannot pass validation criteria
271 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
272 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
273 'fnd.plsql.UMXVUPWB.ForgotPwd',
274 'Could not generated password automatically which satisfies validation requirements.');
275 end if;
276 fnd_message.set_name ('FND', 'UMX_COMMON_UNEXPECTED_ERR_MSG');
277 raise_application_error ('-20000', fnd_message.get);
278 END IF;
279
280 -- end of code for validating username
281
282 end if;
283
284 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
285 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
286 'fnd.plsql.UMXVUPWB.SetPassword.end',
287 'x_password: ' || x_password);
288 end if;
289
290 end;
291
292 Procedure SetPassword_WF(itemtype in varchar2,
293 itemkey in varchar2,
294 actid in number,
295 funcmode in varchar2,
296 resultout in out NOCOPY varchar2) is
297
298 l_password varchar2(40);
299 l_user_name fnd_user.user_name%type;
300
301 begin
302 if (funcmode = 'RUN') then
303 -- Check if the password is already set.
304 l_password := WF_ENGINE.GetActivityAttrText (
305 itemtype => itemtype,
306 itemkey => itemkey,
307 actid => actid,
308 aname => 'PASSWORD');
309
310 if (l_password is null) then
311 -- code for validating the generated username
312 -- get the username
313 l_user_name := WF_ENGINE.GetActivityAttrText(
314 itemtype => itemtype,
315 itemkey => itemkey,
316 actid => actid,
317 aname => 'USER_NAME');
318
319 SetPassword (p_username => l_user_name,
320 x_password => l_password);
321
322 wf_engine.SetItemAttrText (itemtype => itemtype,
323 itemkey => itemkey,
324 aname => 'PASSWORD',
325 avalue => l_password);
326
327 end if;
328 end if;
329
330 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
331
332 exception
333 when others then
334 Wf_Core.Context('UMX_PASSWORD_PVT', 'SetPassword_WF', itemtype, itemkey, actid);
335 raise;
336 end;
337
338 -------------------------------------------------------------------
339 -- Name: UpdatePassword_WF
340 -- Description: Calls FND_USER_PKG.UpdateUserParty
341 -------------------------------------------------------------------
342 Procedure UpdatePassword_WF(itemtype in varchar2,
343 itemkey in varchar2,
344 actid in number,
345 funcmode in varchar2,
346 resultout in out NOCOPY varchar2) is
347
348 begin
349
350 if (funcmode = 'RUN') then
351
352 FND_USER_PKG.UpdateUserParty (
353 x_user_name =>
354 WF_ENGINE.GetActivityAttrText(itemtype, itemkey, actid,
355 'USER_NAME', TRUE),
356 x_owner => null,
357 x_unencrypted_password =>
358 WF_ENGINE.GetActivityAttrText(itemtype, itemkey, actid,
359 'PASSWORD', TRUE),
360 x_password_date => fnd_user_pkg.null_date);
361
362 end if;
363
364 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
365
366 exception
367 when others then
368 Wf_Core.Context('UMX_PASSWORD_PVT', 'UpdatePassword_WF', itemtype, itemkey,
369 actid);
370 raise;
371 end;
372
373
374
375 /* this method returns a formatted name to be used for display name*/
376 function getDisplayName(username in varchar2) return varchar2 is
377 cursor C_party_id is
378 SELECT person_party_id
379 FROM fnd_user
380 where user_name = username;
381
382 x_return_status varchar2(40);
383 x_msg_count NUMBER;
384 x_msg_data VARCHAR2(4000);
385 x_formatted_name VARCHAR2(4000);
386 x_formatted_lines_cnt NUMBER;
387 x_formatted_name_tbl hz_format_pub.string_tbl_type;
388 l_party_id number;
389 begin
390 for i in C_party_id loop
391 l_party_id := i.person_party_id;
392 end loop;
393
394 if l_party_id is null then
395 return username;
396 else
397 Hz_format_pub.format_name (
398 p_party_id => l_party_id,
399 x_return_status => x_return_status,
400 x_msg_count => x_msg_count,
401 x_msg_data => x_msg_data,
402 x_formatted_name => x_formatted_name,
403 x_formatted_lines_cnt => x_formatted_lines_cnt,
404 x_formatted_name_tbl => x_formatted_name_tbl );
405
406 return x_formatted_name;
407 end if;
408 end getDisplayName;
409 -------------------------------------------------------------------
410 -- Name: CreateRole
411 -- Description: Creates an adhoc role with notification preference always set
412 -- to 'MAIL'. This would ensure that the user would get an email
413 -- for all password related notifications. The name of the role
414 -- is set to FND-username
415 -------------------------------------------------------------------
416 Procedure CreateRole(itemtype in varchar2,
417 itemkey in varchar2,
418 actid in number,
419 funcmode in varchar2,
420 resultout in out NOCOPY varchar2) is
421
422 l_role_name wf_local_roles.name%type;
423 l_notification_preference wf_local_roles.notification_preference%type;
424 l_username fnd_user.user_name%type;
425 l_display_name varchar2(4000);
426 begin
427
428 if (funcmode = 'RUN') then
429
430 l_role_name := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'X_USER_ROLE');
431 l_notification_preference := WF_ENGINE.GetItemAttrText (itemtype, itemkey, 'NOTIFICATION_PREFERENCE');
432
433 if (l_role_name is NULL) or (l_notification_preference not like 'MAIL%') then
434 -- No role with the user_name, create an ad hoc role.
435 l_username := upper(WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'USER_NAME'));
436 l_role_name := 'FNDPWD_' || itemkey || '_' || l_username;
437 l_display_name := getDisplayName(l_username);
438 WF_DIRECTORY.CreateAdHocRole (
439 role_name => l_role_name,
440 role_display_name => l_display_name,
441 email_address => WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'EMAIL_ADDRESS'));
442 wf_engine.SetItemAttrText (itemtype => itemtype,
443 itemkey => itemkey,
444 aname => 'X_USER_ROLE',
445 avalue => l_role_name);
446 end if;
447 end if;
448 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
449
450 exception
451 when others then
452 Wf_Core.Context('UMX_PASSWORD_PVT', 'CreateRole', itemtype, itemkey,
453 actid);
454 raise;
455 end CreateRole;
456
457 -- Private Method to start Workflow
458 procedure start_workflow (p_user_name in varchar2,
459 p_password in varchar2,
460 p_email_address in varchar2,
461 p_role_name in varchar2,
462 p_notification_preference in varchar2,
463 p_user_appr_msg_name in varchar2,
464 p_pwd_reset_msg_name in varchar2,
465 p_check_identity in varchar2,
466 p_htmlagent in varchar2,
467 x_itemkey out nocopy varchar2) is
468 begin
469
470 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
471 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
472 'fnd.plsql.UMXVUPWB.start_workflow.begin',
473 'p_user_name: ' || p_user_name ||
474 ' | p_email_address: ' || p_email_address ||
475 ' | p_role_name: ' || p_role_name ||
476 ' | p_user_appr_msg_name: ' || p_user_appr_msg_name ||
477 ' | p_pwd_reset_msg_name: ' || p_pwd_reset_msg_name ||
478 ' | p_check_identity: ' || p_check_identity ||
479 ' | p_htmlagent: ' || p_htmlagent);
480 end if;
481
482 select to_char (UMX_PASSWORD_WF_S.Nextval) into x_itemkey from dual;
483
484 -- start the workflow that will send the notification and reset
485 -- the password
486
487 wf_engine.CreateProcess(itemtype => g_itemtype,
488 itemkey => x_itemkey,
489 process => 'RESETPASSWD');
490
491 wf_engine.SetItemAttrText(itemtype => g_itemtype,
492 itemkey => x_itemkey,
493 aname => 'USER_NAME',
494 avalue => p_user_name);
495
496 wf_engine.SetItemAttrText(itemtype => g_itemtype,
497 itemkey => x_itemkey,
498 aname => 'PASSWORD',
499 avalue => p_password);
500
501 if p_htmlagent is not null then
502 wf_engine.SetItemAttrText (itemtype => g_itemtype,
503 itemkey => x_itemkey,
504 aname => 'HTMLAGENT',
505 avalue => p_htmlagent);
506 end if;
507
508 if p_email_address is not null then
509 wf_engine.SetItemAttrText (itemtype => g_itemtype,
510 itemkey => x_itemkey,
511 aname => 'EMAIL_ADDRESS',
512 avalue => p_email_address);
513 end if;
514
515 if p_role_name is not null then
516 wf_engine.SetItemAttrText (itemtype => g_itemtype,
517 itemkey => x_itemkey,
518 aname => 'X_USER_ROLE',
519 avalue => p_role_name);
520 end if;
521
522 if p_notification_preference is not null then
523 wf_engine.SetItemAttrText (itemtype => g_itemtype,
524 itemkey => x_itemkey,
525 aname => 'NOTIFICATION_PREFERENCE',
526 avalue => p_notification_preference);
527 end if;
528
529 if p_user_appr_msg_name is not null then
530 wf_engine.SetItemAttrText(itemtype => g_itemtype,
531 itemkey => x_itemkey,
532 aname => 'APPR_MESSAGE',
533 avalue => p_user_appr_msg_name);
534 end if;
535
536 if p_pwd_reset_msg_name is not null then
537 wf_engine.SetItemAttrText(itemtype => g_itemtype,
538 itemkey => x_itemkey,
539 aname => 'CONFIRM_MSG',
540 avalue => p_pwd_reset_msg_name);
541 end if;
542
543 -- Only update if the Notify User Flag is 'Y' or 'N'
544 if (p_check_identity = 'Y') or (p_check_identity = 'N') then
545 wf_engine.SetItemAttrText (itemtype => g_itemtype,
546 itemkey => x_itemkey,
547 aname => 'L_CHECK_IDENTITY_FLAG',
548 avalue => p_check_identity);
549 end if;
550
551 wf_engine.StartProcess(itemtype => g_itemtype,
552 itemkey => x_itemkey);
553
554 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
555 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
556 'fnd.plsql.UMXVUPWB.start_workflow.end',
557 'x_itemkey: ' || x_itemkey);
558 end if;
559 end start_workflow;
560
561 -- Private Method
562 procedure ResetPwdPvt (p_username in fnd_user.user_name%type,
563 p_password in varchar2 default null,
564 p_user_appr_msg_name in varchar2 default null,
565 p_pwd_reset_msg_name in varchar2 default null,
566 p_check_identity in varchar2 default 'Y',
567 p_report_no_email_error in varchar2 default 'N',
568 p_htmlagent in varchar2 default null,
569 x_return_status out NOCOPY varchar2,
570 x_message_name out NOCOPY varchar2,
571 x_message_data out NOCOPY varchar2) is
572
573 l_user_name fnd_user.user_name%type := upper(p_username);
574 l_email_address varchar2(2000);
575 l_role_name wf_local_roles.name%type;
576 l_password varchar2(40);
577 l_notification_preference wf_local_roles.notification_preference%type;
578 l_result wf_item_activity_statuses.activity_result_code%type;
579 l_status wf_item_activity_statuses.activity_status%type;
580 l_itemkey wf_items.item_key%type;
581 l_pwdChangeable boolean := null;
582 l_nonExistentUser boolean := false;
583
584 begin
585
586 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
587 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
588 'fnd.plsql.UMXVUPWB.ResetPwdPvt.begin',
589 'p_username: ' || p_username ||
590 ' | p_htmlagent: ' || p_htmlagent ||
591 ' | p_password: ' || p_password ||
592 ' | p_user_appr_msg_name: ' || p_user_appr_msg_name ||
593 ' | p_pwd_reset_msg_name: ' || p_pwd_reset_msg_name ||
594 ' | p_check_identity: ' || p_check_identity);
595 end if;
596
597 -- initialize the return status
598 x_return_status := FND_API.G_RET_STS_ERROR;
599
600 -- validate required fields
601 -- validate user name
602 if l_user_name is NULL then
603 x_message_name := 'UMX_FORGOT_PWD_NULL_USER';
604 fnd_message.set_name('FND', x_message_name);
605 x_message_data := fnd_message.get;
606 else
607 if FND_SSO_Manager.isPasswordChangeable (l_user_name) THEN
608 get_email_address (l_user_name, l_role_name, l_email_address,
609 l_notification_preference, x_message_name,
610 x_message_data);
611 if (x_message_name is null) then
612 if (l_email_address is not null) then
613 -- Start Workflow to reset user's password.
614 start_workflow (l_user_name, p_password, l_email_address, l_role_name,
615 l_notification_preference, p_user_appr_msg_name,
616 p_pwd_reset_msg_name, p_check_identity, p_htmlagent, l_itemkey);
617 -- Check if the workflow is in error status
618 wf_engine.itemstatus (g_itemtype, l_itemkey, l_status, l_result);
619 if (l_status = 'ERROR') then
620 -- Error status
621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
622 x_message_name := 'UMX_FORGOT_PWD_UNEXP_ERR_MSG';
623 else
624 -- Not error, return notified message.
625 x_return_status := FND_API.G_RET_STS_SUCCESS;
626 x_message_name := 'UMX_FORGOT_PWD_NOTIFY';
627 end if;
628 fnd_message.set_name('FND', x_message_name);
629 x_message_data := fnd_message.get;
630 else -- email address is null
631 if (p_report_no_email_error = 'Y') then
632 -- At the moment, only forgot password will require to raise
633 -- error message if email address is missing.
634 x_message_name := 'UMX_FORGOT_PWD_NULL_EMAIL';
635 fnd_message.set_name ('FND', x_message_name);
636 x_message_data := fnd_message.get;
637 else
638 -- Reset the password without starting a workflow
639 l_password := p_password;
640 SetPassword (p_username => l_user_name,
641 x_password => l_password);
642 FND_USER_PKG.UpdateUserParty (
643 x_user_name => l_user_name,
644 x_owner => null,
645 x_unencrypted_password => l_password,
646 x_password_date => fnd_user_pkg.null_date);
647 x_return_status := FND_API.G_RET_STS_SUCCESS;
648 end if;
649 end if;
650 end if;
651 else -- cannot change password for this user
652
653 /*
654 -- Second phase will allow re-direct to different site to change password
655 -- else if l_auth_mode = 'EXTERNAL'
656 -- then
657 -- get the value of the profile option FND_PASSWORD_EXTERNAL_SITE
658 -- create the link woth this url (redirection to the whatever the
659 -- profile options says. If null give them the standard error message below
660 -- owa_util.redirect_url(l_external_password_site);th_mode = 'EXTERNAL' then
661 */
662
663 x_message_name := 'UMX_FORGOT_PWD_EXTERNAL';
664 fnd_message.set_name('FND', x_message_name);
665 x_message_data := fnd_message.get;
666
667 end if;
668 end if;
669
670 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
671 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
672 'fnd.plsql.UMXVUPWB.ResetPwdPvt.end',
673 'x_return_status: ' || x_return_status ||
674 ' | x_message_name: ' || x_message_name ||
675 ' | x_message_data: ' || x_message_data);
676 end if;
677 EXCEPTION
678 when FND_SSO_MANAGER.userNotFound then
679 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
680 --x_message_name := 'UMX_FORGOT_PWD_UNEXP_ERR_MSG';
681 x_message_name := 'UMX_FORGOT_PWD_INVALID_ACCT';
682 fnd_message.set_name('FND', x_message_name);
683 x_message_data := fnd_message.get;
684
685 END ResetPwdPvt;
686
687
688 procedure ResetPwd (p_username in fnd_user.user_name%type,
689 p_password in varchar2 default null,
690 p_user_appr_msg_name in varchar2 default null,
691 p_pwd_reset_msg_name in varchar2 default null,
692 p_check_identity in varchar2 default 'Y',
693 p_htmlagent in varchar2 default null,
694 x_return_status out NOCOPY varchar2,
695 x_message_data out NOCOPY varchar2) is
696
697 x_message_name varchar2(80);
698
699 begin
700
701 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
702 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
703 'fnd.plsql.UMXVUPWB.ResetPwd.begin',
704 'p_username: ' || p_username ||
705 ' | p_htmlagent: ' || p_htmlagent ||
706 ' | p_password: ' || p_password ||
707 ' | p_user_appr_msg_name: ' || p_user_appr_msg_name ||
708 ' | p_pwd_reset_msg_name: ' || p_pwd_reset_msg_name ||
709 ' | p_check_identity: ' || p_check_identity);
710 end if;
711
712 ResetPwdPvt (p_username => p_username,
713 p_password => p_password,
714 p_user_appr_msg_name => p_user_appr_msg_name,
715 p_pwd_reset_msg_name => p_pwd_reset_msg_name,
716 p_check_identity => p_check_identity,
717 p_htmlagent => p_htmlagent,
718 x_return_status => x_return_status,
719 x_message_name => x_message_name,
720 x_message_data => x_message_data);
721
722 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
723 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
724 'fnd.plsql.UMXVUPWB.ResetPwd.end',
725 'x_return_status: ' || x_return_status ||
726 ' | x_message_data: ' || x_message_data);
727 end if;
728
729 end ResetPwd;
730
731 procedure ForgotPwd(p_username in fnd_user.user_name%type,
732 p_user_appr_msg_name in varchar2 default null,
733 p_pwd_reset_msg_name in varchar2 default null,
734 p_check_identity in varchar2 default 'Y',
735 p_htmlagent in varchar2 default null,
736 x_return_status out NOCOPY varchar2,
737 x_message_name out NOCOPY varchar2,
738 x_message_data out NOCOPY varchar2) is
739 begin
740
741 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
742 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
743 'fnd.plsql.UMXVUPWB.ForgotPwd.begin',
744 'p_username: ' || p_username ||
745 ' | p_htmlagent: ' || p_htmlagent ||
746 ' | p_user_appr_msg_name: ' || p_user_appr_msg_name ||
747 ' | p_pwd_reset_msg_name: ' || p_pwd_reset_msg_name ||
748 ' | p_check_identity: ' || p_check_identity);
749 end if;
750
751 ResetPwdPvt (p_username => p_username,
752 p_user_appr_msg_name => p_user_appr_msg_name,
753 p_pwd_reset_msg_name => p_pwd_reset_msg_name,
754 p_check_identity => p_check_identity,
755 p_report_no_email_error => 'Y',
756 p_htmlagent => p_htmlagent,
757 x_return_status => x_return_status,
758 x_message_name => x_message_name,
759 x_message_data => x_message_data);
760
761 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
762 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
763 'fnd.plsql.UMXVUPWB.ForgotPwd.end',
764 'x_return_status: ' || x_return_status ||
765 ' | x_message_name: ' || x_message_name ||
766 ' | x_message_data: ' || x_message_data);
767 end if;
768
769 end ForgotPwd;
770
771 -------------------------------------------------------------------
772 -- Name: clean_up_ad_hoc_role
773 -- Description: This API set the status to inactive and expiration
774 -- date to sysdate of the ad hoc role created by the password
775 -- workflow.
776 -------------------------------------------------------------------
777 Procedure clean_up_ad_hoc_role (itemtype in varchar2,
778 itemkey in varchar2,
779 actid in number,
780 funcmode in varchar2,
781 resultout in out NOCOPY varchar2) is
782
783 l_role_name wf_local_roles.name%type;
784
785 begin
786
787 if (funcmode = 'RUN') then
788 l_role_name := WF_ENGINE.GetItemAttrText (itemtype, itemkey, 'X_USER_ROLE');
789
790 -- First check Ad Hoc Role is being used.
791 if (l_role_name = 'FNDPWD_' || itemkey || '_' || upper(WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'USER_NAME'))) then
792
793 -- Make Ad Hoc Role to expire.
794 -- The expiration_date is set to +30 based on the recommandation from WF.
795 wf_directory.SetAdHocRoleExpiration (role_name => l_role_name,
796 expiration_date => sysdate + 30);
797
798 -- Set Ad Hoc Role to inactive
799 wf_directory.SetAdHocRoleStatus (role_name => l_role_name,
800 status => 'INACTIVE');
801 end if;
802 end if;
803
804 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
805
806 exception
807 when others then
808 Wf_Core.Context('UMX_PASSWORD_PVT', 'clean_up_ad_hoc_role', itemtype, itemkey,
809 actid);
810 raise;
811 end;
812
813 END UMX_PASSWORD_PVT;