[Home] [Help]
PACKAGE BODY: APPS.UMX_LOGIN_HELP_PVT
Source
1 PACKAGE BODY UMX_LOGIN_HELP_PVT AS
2 /* $Header: UMXLHLPB.pls 120.6.12010000.2 2008/10/16 07:14:21 snellepa ship $ */
3 procedure decrementAttemptCounter(
4 p_itemkey in varchar2,
5 x_no_attempts out NOCOPY varchar2);
6
7 /*
8 Things to do
9 1) Check if fnd_sso_manager.get_login_url needs to accept parameters such as lang-code
10
11 */
12 --
13 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
14 -- directory of SQL Navigator
15 --
16 -- Purpose: Briefly explain the functionality of the package body
17 --
18 -- MODIFICATION HISTORY
19 -- Person Date Comments
20 -- --------- ------ ------------------------------------------
21 -- Enter procedure, function bodies as shown below
22
23 g_itemtype wf_item_types.name%type := 'UMXLHELP';
24
25 g_def_no_max_request pls_integer := 5;
26
27
28 function GetDisplayName(username in varchar2) return varchar2 is
29 cursor C_party_id is
30 SELECT person_party_id
31 FROM fnd_user
32 where user_name = username;
33
34 x_return_status varchar2(40);
35 x_msg_count NUMBER;
36 x_msg_data VARCHAR2(4000);
37 x_formatted_name VARCHAR2(4000);
38 x_formatted_lines_cnt NUMBER;
39 x_formatted_name_tbl hz_format_pub.string_tbl_type;
40 l_party_id number;
41 begin
42 for i in C_party_id loop
43 l_party_id := i.person_party_id;
44 end loop;
45
46 if l_party_id is null then
47 return username;
48 else
49 Hz_format_pub.format_name (
50 p_party_id => l_party_id,
51 x_return_status => x_return_status,
52 x_msg_count => x_msg_count,
53 x_msg_data => x_msg_data,
54 x_formatted_name => x_formatted_name,
55 x_formatted_lines_cnt => x_formatted_lines_cnt,
56 x_formatted_name_tbl => x_formatted_name_tbl );
57
58 return x_formatted_name;
59 end if;
60 end getDisplayName;
61
62
63 -------------------------------------------------------------------
64 -- Name: CreateRole
65 -- Description: Creates an adhoc role with notification preference always set
66 -- to 'MAIL'. This would ensure that the user would get an email
67 -- for all password related notifications. The name of the role
68 -- is set to FND-username
69 -------------------------------------------------------------------
70 Procedure CreateRole(itemtype in varchar2,
71 itemkey in varchar2,
72 actid in number,
73 funcmode in varchar2,
74 resultout in out NOCOPY varchar2) is
75
76 l_role_name wf_local_roles.name%type;
77 l_notification_preference wf_local_roles.notification_preference%type;
78 l_username fnd_user.user_name%type;
79 l_display_name varchar2(4000);
80 begin
81
82 if (funcmode = 'RUN') then
83
84 l_role_name := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'X_USER_ROLE');
85
86 l_notification_preference := WF_ENGINE.GetItemAttrText (itemtype, itemkey, 'NOTIFICATION_PREFERENCE');
87
88 if (l_role_name is NULL) or (l_notification_preference not like 'MAIL%') then
89 -- No role with the user_name, create an ad hoc role.
90 l_username := upper(WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'USER_NAME'));
91
92 l_role_name := 'FNDPWD_' || itemkey || '_' || l_username;
93 l_display_name := getDisplayName(l_username);
94
95 WF_DIRECTORY.CreateAdHocRole (
96 role_name => l_role_name,
97 role_display_name => l_display_name,
98 email_address => WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'EMAIL_ADDRESS'));
99
100 wf_engine.SetItemAttrText (itemtype => itemtype,
101 itemkey => itemkey,
102 aname => 'X_USER_ROLE',
103 avalue => l_role_name);
104
105 end if;
106
107 end if;
108
109 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
110
111 exception
112 when others then
113 Wf_Core.Context('UMX_LOGIN_HELP_PVT', 'CreateRole', itemtype, itemkey,
114 actid);
115 raise;
116 end CreateRole;
117
118 Function getCurrNoActiveReqs( username IN varchar2 ) return pls_integer is
119 l_no pls_integer := 0;
120 l_item_key wf_items.item_key%type;
121 l_user_name fnd_user.user_name%type := username;
122 Begin
123 select count(*) into l_no from
124 (
125 SELECT WorkflowItemEO.ITEM_TYPE,
126 WorkflowItemEO.ITEM_KEY,
127 wf_fwkmon.getitemstatus(WorkflowItemEO.ITEM_TYPE, WorkflowItemEO.ITEM_KEY, WorkflowItemEO.END_DATE,
128 WorkflowItemEO.ROOT_ACTIVITY, WorkflowItemEO.ROOT_ACTIVITY_VERSION) STATUS_CODE
129 FROM WF_ITEMS WorkflowItemEO,
130 WF_ITEM_TYPES_VL WorkflowItemTypeEO,
131 WF_ACTIVITIES_VL ActivityEO,
132 WF_ITEM_ATTRIBUTE_VALUES attrib
133 WHERE WorkflowItemEO.ITEM_TYPE = WorkflowItemTypeEO.NAME
134 AND ActivityEO.ITEM_TYPE = WorkflowItemEO.ITEM_TYPE
135 AND ActivityEO.NAME = WorkflowItemEO.ROOT_ACTIVITY
136 AND ActivityEO.VERSION = WorkflowItemEO.ROOT_ACTIVITY_VERSION
137 AND attrib.item_type = WorkflowItemEO.ITEM_TYPE
138 AND attrib.item_key = WorkflowItemEO.ITEM_KEY
139 AND attrib.name = 'USER_NAME'
140 AND attrib.text_value = l_user_name
141 ) QRSLT
142 where
143 item_type = 'UMXLHELP'
144 AND status_code = 'ACTIVE';
145
146 return l_no;
147 END getCurrNoActiveReqs;
148
149
150 Function getMaxNoActiveReqs return pls_integer is
151 l_no pls_integer := 0;
152 l_max_no varchar2(4000) := null;
153 cursor MaxNoRequest is SELECT attr.text_value into l_max_no
154 From WF_ITEMS item, WF_ITEM_ATTRIBUTE_VALUES attr
155 Where
156 item.item_type = attr.item_type
157 And
158 item.item_key = attr.item_key
159 And
160 item.item_type = g_itemtype
161 And
162 attr.name = 'MAX_NO_PERSISTENT_REQ'
163 AND
164 rownum <= 1;
165 Begin
166
167 open MaxNoRequest;
168 fetch MaxNoRequest into l_max_no;
169 if (MaxNoRequest%NOTFOUND) then
170 l_no := g_def_no_max_request;
171 end if;
172 close MaxNoRequest;
173
174 if ( l_max_no is not null and length(l_max_no) > 0 ) then
175 l_no := to_number( l_max_no );
176 else
177 l_no := g_def_no_max_request;
178 end if;
179
180 return l_no;
181
182 exception
183 when others then
184 l_no := g_def_no_max_request;
185 return l_no;
186 END getMaxNoActiveReqs;
187
188
189
190 -- Private function to get the email address of the active user from
191 -- 1) WF local roles
192 -- 2) FND User
193 -- 3) The first TCA party
194 procedure Get_email_address (p_user_name in fnd_user.user_name%type,
195 x_role_name out nocopy varchar2,
196 x_email_address out nocopy varchar2,
197 x_notification_preference out nocopy varchar2,
198 x_message_name out nocopy varchar2) is
199
200 -- TCA Party declares email address as varchar2 2000, largest amount the
201 -- three schema.
202 l_role_display_name wf_local_roles.display_name%type;
203 l_language wf_local_roles.language%type;
204 l_territory wf_local_roles.territory%type;
205
206 cursor get_fnd_email (p_user_name in fnd_user.user_name%type) is
207 SELECT email_address
208 FROM fnd_user
209 WHERE user_name = p_user_name
210 AND start_date <= sysdate
211 AND nvl(end_date, sysdate + 1) > sysdate;
212
213 cursor get_tca_email (p_user_name in fnd_user.user_name%type) is
214 SELECT hzp.email_address
215 FROM hz_parties hzp, fnd_user fu
216 WHERE hzp.party_id = fu.person_party_id
217 AND fu.user_name = p_user_name;
218
219 begin
220
221 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
222 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
223 'fnd.plsql.UMXVUPWB.get_email_address.begin',
224 'p_user_name: ' || p_user_name);
225 end if;
226
227 -- First get the email from wf directory
228 WF_DIRECTORY.GetRoleInfo (p_user_name, l_role_display_name, x_email_address,
229 x_notification_preference, l_language, l_territory);
230
231 if x_email_address is not null then
232 x_role_name := p_user_name;
233 else
234 -- Try to get the email from fnd_user
235 open get_fnd_email (p_user_name);
236 fetch get_fnd_email into x_email_address;
237 if (get_fnd_email%NOTFOUND) then
238 x_message_name := 'UMX_LOGIN_HELP_INVALID_ACCT';
239 --fnd_message.set_name('FND', x_message_name);
240 --x_message_data := fnd_message.get;
241 else
242 if x_email_address is null then
243 -- if email is still null then get it from tca
244 -- check if there is a valid party email
245 for party in get_tca_email (p_user_name) loop
246 x_email_address := party.email_address;
247 exit when x_email_address is not null;
248 end loop;
249 end if;
250 end if;
251 close get_fnd_email;
252 end if;
253
254 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
255 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
256 'fnd.plsql.UMXVUPWB.get_email_address.end',
257 'x_role_name: ' || x_role_name ||
258 ' | x_email_address: ' || x_email_address ||
259 ' | x_message_name: ' || x_message_name);
260 end if;
261
262 end get_email_address;
263
264
265 procedure Find_user_w_email (p_email in fnd_user.email_address%type,
266 x_role_name out nocopy varchar2,
267 x_user_name out nocopy varchar2,
268 x_notification_preference out nocopy varchar2,
269 x_message_name out nocopy varchar2) is
270
271 -- TCA Party declares email address as varchar2 2000, largest amount the
272 -- three schema.
273 l_role_display_name wf_local_roles.display_name%type;
274 l_language wf_local_roles.language%type;
275 l_territory wf_local_roles.territory%type;
276 l_email_address fnd_user.email_address%type;
277
278 cursor get_user_fnd(p_email_address in fnd_user.email_address%type) is
279 SELECT user_name FROM fnd_user
280 WHERE email_address = p_email_address
281 AND start_date <= sysdate AND nvl(end_date, sysdate + 1) > sysdate;
282
283
284 cursor get_user_hz (p_email_address in fnd_user.user_name%type) is
285 SELECT fu.user_name
286 FROM hz_parties p, fnd_user fu
287 WHERE p.party_id = fu.person_party_id
288 AND p.email_address = p_email_address;
289
290
291 begin
292
293 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
294 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
295 'fnd.plsql.UMXVUPWB.find_user_w_email.begin',
296 'p_email: ' || p_email);
297 end if;
298
299 -- First get the email from wf directory
300 /*
301 WF_DIRECTORY.GetRoleInfo (p_user_name, l_role_display_name, x_email_address,
302 x_notification_preference, l_language, l_territory);
303 */
304
305 -- Try to get the email from fnd_user
306 open get_user_fnd (p_email);
307 fetch get_user_fnd into x_user_name;
308 if (get_user_fnd%NOTFOUND) then
309 --x_message_name := 'UMX_FORGOT_PWD_INVALID_ACCT';
310 --fnd_message.set_name('FND', x_message_name);
311 --x_message_data := fnd_message.get;
312 --else
313 --if x_user_name is null then
314 -- if email is still null then get it from tca
315 -- check if there is a valid party email
316 for party in get_user_hz(p_email) loop
317 x_user_name := party.user_name;
318 exit when x_user_name is not null;
319 end loop;
320 --end if;
321 end if;
322 close get_user_fnd;
323
324 if (x_user_name is not null ) then
325 WF_DIRECTORY.GetRoleInfo (x_user_name, l_role_display_name, l_email_address,
326 x_notification_preference, l_language, l_territory);
327 end if;
328
329 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
330 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
331 'fnd.plsql.UMXVUPWB.find_user_w_email.end',
332 'x_role_name: ' || x_role_name ||
333 ' | x_user_name: ' || x_user_name ||
334 ' | x_message_name: ' || x_message_name);
335 end if;
336
337 end find_user_w_email;
338
339 procedure Find_users_w_email( p_email in fnd_user.email_address%type,
340 x_users out NOCOPY UsersWEmail ) is
341
342 -- TCA Party declares email address as varchar2 2000, largest amount the
343 -- three schema.
344 l_role_display_name wf_local_roles.display_name%type;
345 l_language wf_local_roles.language%type;
346 l_territory wf_local_roles.territory%type;
347 l_email_address fnd_user.email_address%type;
348 l_user_name fnd_user.user_name%type;
349 l_notification_preference wf_local_roles.notification_preference%type;
350 i pls_integer := 0;
351
352 cursor get_user_fnd(p_email_address in fnd_user.email_address%type) is
353 SELECT user_name FROM fnd_user
354 WHERE UPPER(email_address) = UPPER(p_email_address)
355 AND start_date <= sysdate AND nvl(end_date, sysdate + 1) > sysdate;
356
357
358 cursor get_user_hz (p_email_address in fnd_user.user_name%type) is
359 SELECT fu.user_name
360 FROM hz_parties p, fnd_user fu
361 WHERE p.party_id = fu.person_party_id
362 AND UPPER(p.email_address) = UPPER(p_email_address);
363
364
365 begin
366
367 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
368 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
369 'fnd.plsql.UMXVUPWB.find_users_w_email.begin',
370 'p_email: ' || p_email);
371 end if;
372
373 -- Try to get the email from fnd_user
374 --open get_user_fnd (p_email);
375 --fetch get_user_fnd into l_user_name;
376 for aUser in get_user_fnd(p_email) loop
377 WF_DIRECTORY.GetRoleInfo ( aUser.user_name, l_role_display_name, l_email_address,
378 l_notification_preference, l_language, l_territory);
379 x_users(i).user_name := aUser.user_name;
380 x_users(i).notification_preference := l_notification_preference;
381 i := i + 1;
382 end loop;
383
384 if ( i = 0 ) then
385 for party in get_user_hz(p_email) loop
386 l_user_name := party.user_name;
387 WF_DIRECTORY.GetRoleInfo (l_user_name, l_role_display_name, l_email_address,
388 l_notification_preference, l_language, l_territory);
389 x_users(i).user_name := party.user_name;
390 x_users(i).notification_preference := l_notification_preference;
391 i := i + 1;
392 end loop;
393 end if;
394
395 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
396 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
397 'fnd.plsql.UMXVUPWB.find_users_w_email.end',
398 ' | l_user_name: ' || l_user_name );
399 end if;
400
401 end find_users_w_email;
402
403
404
405
406 procedure Start_workflow( p_request_type in varchar2,
407 p_user_name in varchar2,
408 p_email_address in varchar2,
409 p_role_name in varchar2,
410 p_notification_preference in varchar2,
411 x_itemkey out nocopy varchar2) is
412 begin
413
414 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
415 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
416 'fnd.plsql.UMX_LOGIN_HELP_PVT.start_workflow.begin',
417 'p_user_name: ' || p_user_name ||
418 ' | p_email_address: ' || p_email_address ||
419 ' | p_role_name: ' || p_role_name );
420 end if;
421
422 select to_char (UMX_LOGIN_HELP_WF_S.Nextval) into x_itemkey from dual;
423
424 -- start the workflow that will send the notification and reset
425 -- the password
426
427 wf_engine.CreateProcess(itemtype => g_itemtype,
428 itemkey => x_itemkey,
429 process => 'LOGIN_HELP');
430
431 wf_engine.SetItemAttrText(itemtype => g_itemtype,
432 itemkey => x_itemkey,
433 aname => 'REQUEST_TYPE',
434 avalue => p_request_type);
435
436 if ( p_user_name is not null ) then
437 wf_engine.SetItemAttrText(itemtype => g_itemtype,
438 itemkey => x_itemkey,
439 aname => 'USER_NAME',
440 avalue => p_user_name);
441 end if;
442
443 if p_email_address is not null then
444 wf_engine.SetItemAttrText (itemtype => g_itemtype,
445 itemkey => x_itemkey,
446 aname => 'EMAIL_ADDRESS',
447 avalue => p_email_address);
448 end if;
449
450 if p_role_name is not null then
451 wf_engine.SetItemAttrText (itemtype => g_itemtype,
452 itemkey => x_itemkey,
453 aname => 'X_USER_ROLE',
454 avalue => p_role_name);
455 end if;
456
457 if p_notification_preference is not null then
458 wf_engine.SetItemAttrText (itemtype => g_itemtype,
459 itemkey => x_itemkey,
460 aname => 'NOTIFICATION_PREFERENCE',
461 avalue => p_notification_preference);
462 end if;
463
464 wf_engine.StartProcess(itemtype => g_itemtype,
465 itemkey => x_itemkey);
466
467 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
468 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
469 'fnd.plsql.UMXVUPWB.start_workflow.end',
470 'x_itemkey: ' || x_itemkey);
471 end if;
472
473 end start_workflow;
474
475
476 procedure ForgottenPwdPvt
477 (p_username in fnd_user.user_name%type,
478 x_return_status out NOCOPY varchar2,
479 x_message_name out NOCOPY varchar2) is
480
481 l_user_name fnd_user.user_name%type := upper(p_username);
482 l_email_address varchar2(2000);
483 l_role_name wf_local_roles.name%type;
484 l_password varchar2(40);
485 l_notification_preference wf_local_roles.notification_preference%type;
486 l_result wf_item_activity_statuses.activity_result_code%type;
487 l_status wf_item_activity_statuses.activity_status%type;
488 l_itemkey wf_items.item_key%type;
489 l_pwdChangeable boolean := null;
490 l_nonExistentUser boolean := false;
491 l_max_req pls_integer := getMaxNoActiveReqs();
492 l_too_many_prev_reqs boolean := false;
493
494
495 begin
496
497 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
498 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
499 'fnd.plsql.UMXVUPWB.ForgottenPwdPvt.begin',
500 'p_username: ' || p_username );
501 end if;
502
503 -- initialize the return status
504 x_return_status := FND_API.G_RET_STS_ERROR;
505
506
507 if ( getCurrNoActiveReqs(l_user_name) >= l_max_req ) then
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509 x_message_name := 'UMX_LOGIN_HELP_2_MANY_REQS';
510 return;
511 end if;
512 -- validate required fields
513 -- validate user name
514 if FND_SSO_Manager.isPasswordChangeable (l_user_name) THEN
515 get_email_address (l_user_name, l_role_name, l_email_address,
516 l_notification_preference, x_message_name);
517 if (x_message_name is null) then
518 if (l_email_address is not null) then
519 -- Start Workflow to reset user's password.
520 start_workflow ( 'P', l_user_name, l_email_address, l_role_name,
521 l_notification_preference, l_itemkey);
522 -- Check if the workflow is in error status
523 wf_engine.itemstatus (g_itemtype, l_itemkey, l_status, l_result);
524 if (l_status = 'ERROR') then
525 -- Error status
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
527 x_message_name := 'UMX_LOGIN_HELP_UNEXPECTED_ERROR';
528 else
529 -- Not error, return notified message.
530 x_return_status := FND_API.G_RET_STS_SUCCESS;
531 x_message_name := 'UMX_LOGIN_HELP_SUB_SUC_MSG_1';
532 end if;
533 else -- email address is null
534 x_message_name := 'UMX_LOGIN_HELP_NULL_EMAIL';
535 end if;
536 end if;
537 else -- cannot change password for this user
538 x_message_name := 'UMX_LOGIN_HELP_PWD_EXTERNAL';
539 end if;
540
541 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
542 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
543 'fnd.plsql.UMXVUPWB.ResetPwdPvt.end',
544 'x_return_status: ' || x_return_status ||
545 ' | x_message_name: ' || x_message_name);
546 end if;
547 EXCEPTION
548 when FND_SSO_MANAGER.userNotFound then
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550 --x_message_name := 'UMX_FORGOT_PWD_UNEXP_ERR_MSG';
551 x_message_name := 'UMX_LOGIN_HELP_INVALID_ACCT';
552
553 END ForgottenPwdPvt;
554
555
556 procedure ForgottenPwd(p_username in fnd_user.user_name%type,
557 x_return_status out NOCOPY varchar2,
558 x_message_name out NOCOPY varchar2) is
559 begin
560
561 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
562 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
563 'fnd.plsql.UMXVUPWB.ForgottenPwdPvt.begin',
564 'p_username: ' || p_username
565 );
566 end if;
567
568 ForgottenPwdPvt
569 (p_username => p_username,
570 x_return_status => x_return_status,
571 x_message_name => x_message_name);
572
573
574 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
575 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
576 'fnd.plsql.UMXVUPWB.ForgottenPwdPvt.end',
577 'x_return_status: ' || x_return_status ||
578 ' | x_message_name: ' || x_message_name);
579 end if;
580
581 end ForgottenPwd;
582
583
584
585
586
587 procedure ForgottenUnamePvt
588 (p_email in fnd_user.email_address%type,
589 x_return_status out NOCOPY varchar2,
590 x_message_name out NOCOPY varchar2) is
591
592 l_email_address varchar2(2000) := p_email;
593 l_role_name wf_local_roles.name%type;
594 l_notification_preference wf_local_roles.notification_preference%type;
595 l_result wf_item_activity_statuses.activity_result_code%type;
596 l_status wf_item_activity_statuses.activity_status%type;
597 l_itemkey wf_items.item_key%type;
598 l_user_name fnd_user.user_name%type;
599 l_user_list UsersWEmail;
600 i pls_integer;
601 l_first pls_integer;
602 e pls_integer := 0;
603 l_max_req pls_integer := g_def_no_max_request;
604 l_too_many_prev_reqs boolean := false;
605
606 begin
607
608 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
609 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
610 'fnd.plsql.UMXVUPWB.ForgottenUnamePvt.begin',
611 'p_email: ' || p_email );
612 end if;
613
614 -- initialize the return status
615 x_return_status := FND_API.G_RET_STS_SUCCESS;
616 x_message_name := 'UMX_LOGIN_HELP_SUB_SUC_MSG_2';
617
618 find_users_w_email( l_email_address, l_user_list );
619
620 i := l_user_list.first();
621 l_first := i;
622 if ( i is null ) then
623 x_return_status := FND_API.G_RET_STS_ERROR;
624 x_message_name := 'UMX_LOGIN_HELP_NO_USER_FOUND';
625 else
626 WHILE ( i is not null and x_return_status = FND_API.G_RET_STS_SUCCESS ) LOOP
627 l_user_name := l_user_list(i).user_name;
628 l_notification_preference := l_user_list(i).notification_preference;
629
630 if ( i = l_first ) then
631 l_max_req := getMaxNoActiveReqs();
632 if ( getCurrNoActiveReqs(l_user_name) < l_max_req ) then
633 l_too_many_prev_reqs := false;
634 else
635 l_too_many_prev_reqs := true;
636 end if;
637 end if;
638
639 if ( not l_too_many_prev_reqs ) then
640 -- Start Workflow to reset user's password.
641 start_workflow ( 'U', l_user_name, l_email_address, l_role_name,
642 l_notification_preference, l_itemkey);
643 -- Check if the workflow is in error status
644
645 wf_engine.itemstatus (g_itemtype, l_itemkey, l_status, l_result);
646 if (l_status = 'ERROR') then
647 -- Error status
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
649 x_message_name := 'UMX_LOGIN_HELP_UNEXP_ERR_MSG';
650 return;
651 end if;
652 else
653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
654 x_message_name := 'UMX_LOGIN_HELP_2_MANY_REQS';
655 return;
656 end if;
657
658 i := l_user_list.NEXT( i );
659 END LOOP; --End of UserList Loop
660 END IF;
661
662
663 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
664 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
665 'fnd.plsql.UMXLHLPB.ForgottenUnamePvt.end',
666 'x_return_status: ' || x_return_status ||
667 ' | x_message_name: ' || x_message_name);
668 end if;
669 EXCEPTION
670 when others then
671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
672 x_message_name := 'UMX_LOGIN_HELP_UNEXP_ERR_MSG';
673
674 END ForgottenUnamePvt;
675
676
677 procedure ForgottenUname
678 (p_email in fnd_user.email_address%type,
679 x_return_status out NOCOPY varchar2,
680 x_message_name out NOCOPY varchar2) is
681 begin
682
683 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
684 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
685 'fnd.plsql.UMXVUPWB.ForgottenUname.begin',
686 'p_email: ' || p_email
687 );
688 end if;
689
690 ForgottenUnamePvt
691 (p_email => p_email,
692 x_return_status => x_return_status,
693 x_message_name => x_message_name);
694
695
696 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
697 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
698 'fnd.plsql.UMXVUPWB.ForgottenPwdPvt.end',
699 'x_return_status: ' || x_return_status ||
700 ' | x_message_name: ' || x_message_name);
701 end if;
702
703 end ForgottenUname;
704
705
706 function GenerateAuthKey return varchar2 is
707
708 l_password_len int := 20;
709 x_auth_key varchar2(400);
710 ascii_offset int := 65;
711
712 begin
713
714 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
715 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
716 'fnd.plsql.UMX_LOGIN_HELP_PVT.GenerateAuthKey.begin', '');
717 end if;
718
719
720 for j in 1..l_password_len loop
721 if (mod(abs(dbms_random.random),2) = 1) then
722 -- generate number
723 x_auth_key := x_auth_key || mod(abs(FND_CRYPTO.SmallRandomNumber),10);
724 else
725 -- generate character
726 x_auth_key := x_auth_key || fnd_global.local_chr(mod(abs(FND_CRYPTO.SmallRandomNumber),26)
727 + ascii_offset);
728 end if;
729 end loop;
730
731 -- terminate the random number generator
732 --dbms_random.terminate;
733
734 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
735 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
736 'fnd.plsql.UMX_LOGIN_HELP_PVT.GenerateAuthKey.end',
737 'x_auth_key: ' || x_auth_key);
738 end if;
739
740 return x_auth_key;
741
742 end GenerateAuthKey;
743
744
745 Procedure GenAuthKey(itemtype in varchar2,
746 itemkey in varchar2,
747 actid in number,
748 funcmode in varchar2,
749 resultout in out NOCOPY varchar2) is
750
751 l_auth_key varchar2(400);
752 l_user_name fnd_user.user_name%type;
753
754 begin
755 if (funcmode = 'RUN') then
756 -- Check if the password is already set.
757 /*
758 l_auth_key := WF_ENGINE.GetActivityAttrText (
759 itemtype => itemtype,
760 itemkey => itemkey,
761 actid => actid,
762 aname => 'AUTH_KEY');
763 */
764
765 l_auth_key := GenerateAuthKey();
766 if (l_auth_key is not null) then
767 -- code for validating the generated username
768 -- get the username
769 /*
770 l_user_name := WF_ENGINE.GetActivityAttrText(
771 itemtype => itemtype,
772 itemkey => itemkey,
773 actid => actid,
774 aname => 'USER_NAME');
775 */
776
777 wf_engine.SetItemAttrText (itemtype => itemtype,
778 itemkey => itemkey,
779 aname => 'AUTH_KEY',
780 avalue => l_auth_key);
781
782 end if;
783 end if;
784
785 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
786
787 exception
788 when others then
789 Wf_Core.Context('UMX_LOGIN_HELP_PVT', 'GenAuthKey', itemtype, itemkey, actid);
790 raise;
791 end;
792
793
794 Procedure GenUrl2ResetPwdPg(itemtype in varchar2,
795 itemkey in varchar2,
796 actid in number,
797 funcmode in varchar2,
798 resultout in out NOCOPY varchar2) is
799 l_auth_key varchar2(400);
800 l_param varchar2(32000);
801 l_url varchar2(32000) := 'http://yahoo.com';
802 l_user_name fnd_user.user_name%type;
803
804 l_link_name varchar2(400) := 'Click here';
805
806
807 begin
808
809 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
810 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
811 'fnd.plsql.UMXVUPWB.GenUrl2ResetPwdPg.begin', '');
812 end if;
813
814 if (funcmode = 'RUN') then
815 -- Check if the password is already set.
816
817
818 l_auth_key := WF_ENGINE.GetItemAttrText (
819 itemtype => itemtype,
820 itemkey => itemkey,
821 aname => 'AUTH_KEY');
822
823
824 l_user_name := WF_ENGINE.GetItemAttrText(
825 itemtype => itemtype,
826 itemkey => itemkey,
827 aname => 'USER_NAME');
828
829 l_param := '&AUTH_KEY=' || l_auth_key || '&ITEM_KEY=' || itemkey;
830
831 /*
832 function get_run_function_url ( p_function_name in varchar2,
833 p_resp_appl in varchar2,
834 p_resp_key in varchar2,
835 p_security_group_key in varchar2,
836 p_parameters in varchar2 default null,
837 p_override_agent in varchar2 default null )
838 */
839 l_url := FND_RUN_FUNCTION.get_run_function_url(
840 p_function_name => 'UMX_SELF_RESET_PWD',
841 p_resp_appl => null,
842 p_resp_key => null,
843 p_security_group_key => null,
844 p_parameters => l_param);
845
846
847 wf_engine.SetItemAttrText (itemtype => itemtype,
848 itemkey => itemkey,
849 aname => 'RESETURL',
850 avalue => l_url);
851
852 end if;
853
854 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
855
856 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
857 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
858 'fnd.plsql.UMXVUPWB.GenUrl2ResetPwdPg.end',
859 'l_url: ' || l_url);
860 end if;
861
862 exception
863 when others then
864 wf_engine.SetItemAttrText (itemtype => itemtype,
865 itemkey => itemkey,
866 aname => 'DEBUG_INFO',
867 avalue => to_char(actid));
868 Wf_Core.Context('UMX_LOGIN_HELP_PVT', 'GenUrl2ResetPwdPg', itemtype, itemkey, actid);
869 raise;
870 end GenUrl2ResetPwdPg;
871
872 Procedure GenUrl2LoginPg(itemtype in varchar2,
873 itemkey in varchar2,
874 actid in number,
875 funcmode in varchar2,
876 resultout in out NOCOPY varchar2) is
877 l_url varchar2(32000);
878 begin
879
880 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
881 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
882 'fnd.plsql.UMXVUPWB.GenUrl2LoginPg.begin', '');
883 end if;
884
885 if (funcmode = 'RUN') then
886 l_url := FND_SSO_MANAGER.getloginurl;
887
888 wf_engine.SetItemAttrText (itemtype => itemtype,
889 itemkey => itemkey,
890 aname => 'LOGINURL',
891 avalue => l_url);
892 end if;
893
894 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
895
896
897 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
898 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
899 'fnd.plsql.UMXVUPWB.GenUrl2LoginPg.end',
900 'l_url: ' || l_url);
901 end if;
902
903 end GenUrl2LoginPg;
904
905
906 Procedure DisableAccount(itemtype in varchar2,
907 itemkey in varchar2,
908 actid in number,
909 funcmode in varchar2,
910 resultout in out NOCOPY varchar2) is
911 l_user_name fnd_user.user_name%type;
912 begin
913
914 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
915 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
916 'fnd.plsql.UMXVUPWB.DisableAccount.begin', '');
917 end if;
918
919 if (funcmode = 'RUN') then
920 l_user_name := WF_ENGINE.GetItemAttrText (
921 itemtype => itemtype,
922 itemkey => itemkey,
923 aname => 'USER_NAME');
924
925 if ( l_user_name is not null ) then
926 fnd_user_pkg.disableuser( username => l_user_name );
927 end if;
928 end if;
929
930 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
931
932
933 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
934 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
935 'fnd.plsql.UMXVUPWB.DisableAccount.end',
936 'l_user_name: ' || l_user_name);
937 end if;
938
939 end DisableAccount;
940
941 PROCEDURE ValidateAuthKey( p_authkey in varchar2,
942 p_itemkey in varchar2,
943 x_no_attempts out NOCOPY varchar2,
944 x_return_status out NOCOPY varchar2,
945 x_message_name out NOCOPY varchar2)
946 IS
947 l_user_name fnd_user.user_name%type;
948 l_authkey varchar2(400);
949 l_notification_preference wf_local_roles.notification_preference%type;
950 l_email_address varchar2(2000);
951 l_pwd_changeable boolean := true;
952 l_role_name wf_local_roles.name%type;
953 l_item_status varchar2(8);
954 l_item_result varchar2(30);
955
956 BEGIN
957 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
958 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
959 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidateResetPwdReq.begin',
960 ' | p_authkey: ' || p_authkey ||
961 ' | p_itemkey: ' || p_itemkey );
962 end if;
963
964
965 WF_ENGINE.itemstatus( g_itemtype, p_itemkey, l_item_status, l_item_result );
966
967 if ( l_item_status <> 'ACTIVE' ) then
968 x_return_status := 'E';
969 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
970 return;
971 end if;
972
973 x_no_attempts := WF_ENGINE.GetItemAttrText(g_itemtype, p_itemkey, 'MAX_NO_ATTEMPT');
974 if ( to_number( x_no_attempts ) <= 0 ) then
975 x_return_status := 'E';
976 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
977 return;
978 end if;
979
980
981 l_authkey := WF_ENGINE.GetItemAttrText(g_itemtype, p_itemkey, 'AUTH_KEY');
982
983 if ( l_authkey = p_authkey ) then
984 x_return_status := 'S';
985 return;
986 else
987 x_return_status := 'E';
988 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
989 decrementAttemptCounter( p_itemkey, x_no_attempts);
990 return;
991 end if;
992
993
994 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
995 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
996 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidateResetPwdReq.end',
997 'x_return_status: ' || x_return_status ||
998 'x_message_name: ' || x_message_name );
999 end if;
1000
1001 exception
1002 when others then
1003 x_return_status := 'E';
1004
1005 if ( x_message_name is null ) then
1006 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
1007 end if;
1008
1009 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1010 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1011 'Exception in fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePwdResetReq.end',
1012 'x_return_status: ' || x_return_status ||
1013 'x_message_name: ' || x_message_name ||
1014 fnd_message.get );
1015 end if;
1016
1017 end ValidateAuthKey;
1018
1019
1020 PROCEDURE ValidateResetPwdReq (p_username in fnd_user.user_name%type,
1021 p_authkey in varchar2,
1022 p_itemkey in varchar2,
1023 x_no_attempts out NOCOPY varchar2,
1024 x_return_status out NOCOPY varchar2,
1025 x_message_name out NOCOPY varchar2)
1026 IS
1027 l_user_name fnd_user.user_name%type;
1028 l_authkey varchar2(400);
1029 l_notification_preference wf_local_roles.notification_preference%type;
1030 l_email_address varchar2(2000);
1031 l_pwd_changeable boolean := true;
1032 l_role_name wf_local_roles.name%type;
1033 l_item_status varchar2(8);
1034 l_item_result varchar2(30);
1035
1036 BEGIN
1037 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1038 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1039 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidateResetPwdReq.begin',
1040 'p_username: ' || p_username ||
1041 ' | p_authkey: ' || p_authkey ||
1042 ' | p_itemkey: ' || p_itemkey );
1043 end if;
1044
1045 WF_ENGINE.itemstatus( g_itemtype, p_itemkey, l_item_status, l_item_result );
1046
1047 if ( l_item_status <> 'ACTIVE' ) then
1048 x_return_status := 'E';
1049 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
1050 return;
1051 end if;
1052
1053 x_no_attempts := WF_ENGINE.GetItemAttrText(g_itemtype, p_itemkey, 'MAX_NO_ATTEMPT');
1054
1055 if ( to_number( x_no_attempts ) <= 0 ) then
1056 x_return_status := 'E';
1057 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
1058 return;
1059 end if;
1060
1061 l_user_name := WF_ENGINE.GetItemAttrText(g_itemtype, p_itemkey, 'USER_NAME');
1062 l_authkey := WF_ENGINE.GetItemAttrText(g_itemtype, p_itemkey, 'AUTH_KEY');
1063
1064 if ( l_authkey = p_authkey ) then
1065 x_return_status := 'S';
1066 else
1067 x_return_status := 'E';
1068 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
1069 decrementAttemptCounter( p_itemkey, x_no_attempts);
1070 return;
1071 end if;
1072
1073 if ( l_user_name <> p_username ) then
1074 x_return_status := 'E';
1075 x_message_name := 'UMX_RESET_PWD2_INVALID_UNAME';
1076 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) then
1077 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1078 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPwdPvt.ValidateResetPwdReq',
1079 'failed');
1080 end if;
1081
1082 decrementAttemptCounter( p_itemkey => p_itemKey,
1083 x_no_attempts => x_no_attempts);
1084
1085 return;
1086 end if;
1087
1088 if not FND_SSO_Manager.isPasswordChangeable(p_username) then
1089 x_return_status := 'E';
1090 x_message_name := 'UMX_LOGIN_HELP_PWD_EXTERNAL';
1091 return;
1092 end if;
1093
1094 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1095 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1096 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidateResetPwdReq.end',
1097 'x_return_status: ' || x_return_status ||
1098 'x_message_name: ' || x_message_name );
1099 end if;
1100
1101 exception
1102 when others then
1103 x_return_status := 'E';
1104
1105 if ( x_message_name is null ) then
1106 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
1107 end if;
1108
1109 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1110 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1111 'Exception in fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePwdResetReq.end',
1112 'x_return_status: ' || x_return_status ||
1113 'x_message_name: ' || x_message_name ||
1114 fnd_message.get );
1115 end if;
1116
1117 end ValidateResetPwdReq;
1118
1119
1120 PROCEDURE ValidatePassword( p_username in fnd_user.user_name%type,
1121 x_password in out NOCOPY varchar2,
1122 x_return_status out NOCOPY varchar2,
1123 x_message_name out NOCOPY varchar2,
1124 x_message_data out NOCOPY varchar2 )
1125 IS
1126 l_result varchar2(10);
1127 begin
1128
1129 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1130 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1131 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePassword.begin',
1132 'p_username: ' || p_username ||' | x_password: ' || x_password);
1133 end if;
1134
1135
1136 l_result := FND_WEB_SEC.validate_password (p_username, x_password);
1137
1138 IF ( l_result <> 'Y' ) THEN
1139 -- Throw exception as even though generated password 100 times, but
1140 -- cannot pass validation criteria
1141 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1142 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1143 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePassword',
1144 'Validate Password failed.');
1145 end if;
1146 x_return_status := 'E';
1147 x_message_name := 'UMX_RESET_PWD2_VALID_FAIL';
1148 x_message_data := fnd_message.get;
1149 ELSE
1150 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1151 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1152 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePassword',
1153 'Validate Password success.');
1154 end if;
1155 x_return_status := 'S';
1156 x_message_name := 'UMX_RESET_PWD2_CONFIRM_MSG';
1157 END IF;
1158 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1159 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1160 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePassword.end',
1161 'x_password: ' || x_password);
1162 end if;
1163
1164 end;
1165
1166 procedure decrementAttemptCounter(
1167 p_itemkey in varchar2,
1168 x_no_attempts out NOCOPY varchar2) IS
1169 l_no_attempt pls_integer := 0;
1170 begin
1171 -- retrieve no of attempts from wf
1172 l_no_attempt := wf_engine.getitemattrnumber(
1173 itemtype => g_itemtype,
1174 itemkey => p_itemkey,
1175 aname => 'MAX_NO_ATTEMPT');
1176 l_no_attempt := l_no_attempt - 1;
1177
1178 wf_engine.SetItemAttrNumber (itemtype => g_itemtype,
1179 itemkey => p_itemkey,
1180 aname => 'MAX_NO_ATTEMPT',
1181 avalue => l_no_attempt );
1182 x_no_attempts := to_char( l_no_attempt );
1183
1184 end decrementAttemptCounter;
1185
1186
1187 -- Private Method
1188 procedure ResetPwdPvt (p_username in fnd_user.user_name%type,
1189 --p_usernameurl in fnd_user.user_name%type,
1190 p_password in varchar2 default null,
1191 p_itemkey in varchar2,
1192 p_authkey in varchar2,
1193 x_no_attempts out NOCOPY varchar2,
1194 x_return_status out NOCOPY varchar2,
1195 x_message_name out NOCOPY varchar2,
1196 x_message_data out NOCOPY varchar2) IS
1197
1198 l_user_name fnd_user.user_name%type := upper(p_username);
1199 -- l_email_address varchar2(2000);
1200 -- l_role_name wf_local_roles.name%type;
1201 l_password varchar2(40) := p_password;
1202 -- l_notification_preference wf_local_roles.notification_preference%type;
1203 l_result wf_item_activity_statuses.activity_result_code%type;
1204 -- l_status wf_item_activity_statuses.activity_status%type;
1205 -- l_itemkey wf_items.item_key%type;
1206 l_pwdChangeable boolean := null;
1207 l_updatePwdFailedException exception;
1208
1209 l_item_status varchar2(8);
1210 l_item_result varchar2(30);
1211
1212 begin
1213 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1214 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1215 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPwdPvt.begin',
1216 'p_username: ' || p_username || ' | p_password: ' || p_password);
1217 end if;
1218
1219
1220 x_return_status := FND_API.G_RET_STS_ERROR;
1221
1222 ValidateResetPwdReq( p_username => p_username,
1223 p_authkey => p_authkey,
1224 p_itemkey => p_itemkey,
1225 x_no_attempts => x_no_attempts,
1226 x_return_status => x_return_status,
1227 x_message_name => x_message_name );
1228
1229 if ( x_return_status <> 'S' ) then
1230 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) then
1231 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1232 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPwdPvt.ValidateResetPwdReq',
1233 'failed');
1234 end if;
1235 return;
1236 end if;
1237
1238
1239 -- validate the user's new password for system-enforcement
1240 ValidatePassword( p_username => l_user_name,
1241 x_password => l_password,
1242 x_return_status => x_return_status,
1243 x_message_name => x_message_name,
1244 x_message_data => x_message_data );
1245
1246 if ( x_return_status = 'S' ) then
1247 BEGIN
1248
1249 FND_USER_PKG.UpdateUserParty (
1250 x_user_name => l_user_name,
1251 x_owner => null,
1252 x_unencrypted_password => l_password,
1253 x_password_date => sysdate);
1254 EXCEPTION
1255 when others then
1256 x_return_status := 'E';
1257 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
1258 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1259 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1260 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPwdPvt.FND_USER_PKG.UpdateUserParty',
1261 'x_no_attempts: ' || x_no_attempts ||
1262 'x_return_status: ' || x_return_status ||
1263 ' | x_message_name: ' || x_message_name );
1264 end if;
1265 END;
1266 end if;
1267
1268 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1269 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1270 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPwdPvt.end',
1271 'x_return_status: ' || x_return_status ||
1272 ' | x_message_name: ' || x_message_name );
1273 end if;
1274 EXCEPTION
1275 when others then
1276 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
1277 x_message_data := fnd_message.get;
1278
1279 END ResetPwdPvt;
1280
1281
1282 procedure ResetPassword( p_username in fnd_user.user_name%type,
1283 --p_usernameurl in fnd_user.user_name%type,
1284 p_password in varchar2 default null,
1285 p_itemkey in varchar2,
1286 p_authkey in varchar2,
1287 x_no_attempts out NOCOPY varchar2,
1288 x_return_status out NOCOPY varchar2,
1289 x_message_name out NOCOPY varchar2,
1290 x_message_data out NOCOPY varchar2) is
1291
1292 begin
1293
1294 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1295 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1296 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPassword.begin',
1297 'p_username: ' || p_username ||
1298 ' | p_password: ' || p_password);
1299 end if;
1300
1301 /*
1302 x_return_status := 'S';
1303 x_message_name := 'UMX_RESET_PWD2_CONFIRM_MSG';
1304 x_message_data := 'TEST';
1305 */
1306 ResetPwdPvt (p_username => p_username,
1307 --p_usernameurl => p_usernameurl,
1308 p_password => p_password,
1309 p_itemkey => p_itemkey,
1310 p_authkey => p_authkey,
1311 x_no_attempts => x_no_attempts,
1312 x_return_status => x_return_status,
1313 x_message_name => x_message_name,
1314 x_message_data => x_message_data);
1315
1316 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1317 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1318 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPassword.end',
1319 'x_return_status: ' || x_return_status ||
1320 ' | x_message_name: ' || x_message_name);
1321 end if;
1322
1323 end ResetPassword;
1324
1325 procedure CompleteActivity( p_itemKey in varchar2,
1326 x_return_status out NOCOPY varchar2,
1327 x_message_name out NOCOPY varchar2) is
1328 l_item_status varchar2(8);
1329 l_item_result varchar2(30);
1330 l_role_name varchar2(200);
1331 -- changes to figure out if role is a ad hoc role,7445188
1332 l_orig_system wf_local_roles.orig_system%TYPE;
1333 l_orig_system_id wf_local_roles.orig_system_id%type;
1334 begin
1335
1336 x_return_status := 'S';
1337 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1338 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1339 'fnd.plsql.UMX_LOGIN_HELP_PVT.CompleteActivity.begin',
1340 ' p_itemkey: ' || p_itemKey );
1341 end if;
1342
1343 l_role_name := WF_ENGINE.GetItemAttrText (
1344 itemtype => g_itemtype,
1345 itemkey => p_itemKey,
1346 aname => 'X_USER_ROLE');
1347 -- get orig system info and check if this is an ad-hoc role ,7445188
1348 wf_directory.getroleOrigsysinfo(l_role_name,l_orig_system,l_orig_system_id);
1349 if l_orig_system ='WF_LOCAL_ROLES' then
1350 wf_directory.setAdHocRoleExpiration(l_role_name);
1351 end if;
1352
1353 wf_engine.abortprocess(
1354 itemtype => g_itemtype,
1355 itemkey => p_itemKey,
1356 process => null,
1357 result => null,
1358 verify_lock => false,
1359 cascade => true);
1360
1361 x_return_status := 'S';
1362
1363 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1364 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1365 'fnd.plsql.UMX_LOGIN_HELP_PVT.CompleteActivity.end',
1366 'x_return_status: ' || x_return_status ||
1367 ' | x_message_name: ' || x_message_name);
1368 end if;
1369 EXCEPTION
1370 when others then
1371 x_return_status := 'E';
1372 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
1373
1374 end CompleteActivity;
1375
1376 END UMX_LOGIN_HELP_PVT;