[Home] [Help]
PACKAGE BODY: APPS.UMX_LOGIN_HELP_PVT
Source
1 PACKAGE BODY UMX_LOGIN_HELP_PVT AS
2 /* $Header: UMXLHLPB.pls 120.7.12020000.5 2013/03/13 10:45:17 avelu 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_UNEXP_ERROR';
239 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
240 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
241 'fnd.plsql.UMXVUPWB.get_email_address',
242 'UMX_LOGIN_HELP_INVALID_ACCT');
243 end if;
244 --fnd_message.set_name('FND', x_message_name);
245 --x_message_data := fnd_message.get;
246 else
247 if x_email_address is null then
248 -- if email is still null then get it from tca
249 -- check if there is a valid party email
250 for party in get_tca_email (p_user_name) loop
251 x_email_address := party.email_address;
252 exit when x_email_address is not null;
253 end loop;
254 end if;
255 end if;
256 close get_fnd_email;
257 end if;
258
259 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
260 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
261 'fnd.plsql.UMXVUPWB.get_email_address.end',
262 'x_role_name: ' || x_role_name ||
263 ' | x_email_address: ' || x_email_address ||
264 ' | x_message_name: ' || x_message_name);
265 end if;
266
267 end get_email_address;
268
269
270 procedure Find_user_w_email (p_email in fnd_user.email_address%type,
271 x_role_name out nocopy varchar2,
272 x_user_name out nocopy varchar2,
273 x_notification_preference out nocopy varchar2,
274 x_message_name out nocopy varchar2) is
275
276 -- TCA Party declares email address as varchar2 2000, largest amount the
277 -- three schema.
278 l_role_display_name wf_local_roles.display_name%type;
279 l_language wf_local_roles.language%type;
280 l_territory wf_local_roles.territory%type;
281 l_email_address fnd_user.email_address%type;
282
283 cursor get_user_fnd(p_email_address in fnd_user.email_address%type) is
284 SELECT user_name FROM fnd_user
285 WHERE email_address = p_email_address
286 AND start_date <= sysdate AND nvl(end_date, sysdate + 1) > sysdate;
287
288
289 cursor get_user_hz (p_email_address in fnd_user.user_name%type) is
290 SELECT fu.user_name
291 FROM hz_parties p, fnd_user fu
292 WHERE p.party_id = fu.person_party_id
293 AND p.email_address = p_email_address;
294
295
296 begin
297
298 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
299 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
300 'fnd.plsql.UMXVUPWB.find_user_w_email.begin',
301 'p_email: ' || p_email);
302 end if;
303
304 -- First get the email from wf directory
305 /*
306 WF_DIRECTORY.GetRoleInfo (p_user_name, l_role_display_name, x_email_address,
307 x_notification_preference, l_language, l_territory);
308 */
309
310 -- Try to get the email from fnd_user
311 open get_user_fnd (p_email);
312 fetch get_user_fnd into x_user_name;
313 if (get_user_fnd%NOTFOUND) then
314 --x_message_name := 'UMX_FORGOT_PWD_INVALID_ACCT';
315 --fnd_message.set_name('FND', x_message_name);
316 --x_message_data := fnd_message.get;
317 --else
318 --if x_user_name is null then
319 -- if email is still null then get it from tca
320 -- check if there is a valid party email
321 for party in get_user_hz(p_email) loop
322 x_user_name := party.user_name;
323 exit when x_user_name is not null;
324 end loop;
325 --end if;
326 end if;
327 close get_user_fnd;
328
329 if (x_user_name is not null ) then
330 WF_DIRECTORY.GetRoleInfo (x_user_name, l_role_display_name, l_email_address,
331 x_notification_preference, l_language, l_territory);
332 end if;
333
334 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
335 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
336 'fnd.plsql.UMXVUPWB.find_user_w_email.end',
337 'x_role_name: ' || x_role_name ||
338 ' | x_user_name: ' || x_user_name ||
339 ' | x_message_name: ' || x_message_name);
340 end if;
341
342 end find_user_w_email;
343
344 procedure Find_users_w_email( p_email in fnd_user.email_address%type,
345 x_users out NOCOPY UsersWEmail ) is
346
347 -- TCA Party declares email address as varchar2 2000, largest amount the
348 -- three schema.
349 l_role_display_name wf_local_roles.display_name%type;
350 l_language wf_local_roles.language%type;
351 l_territory wf_local_roles.territory%type;
352 l_email_address fnd_user.email_address%type;
353 l_user_name fnd_user.user_name%type;
354 l_notification_preference wf_local_roles.notification_preference%type;
355 i pls_integer := 0;
356
357 cursor get_user_fnd(p_email_address in fnd_user.email_address%type) is
358 SELECT user_name FROM fnd_user
359 WHERE UPPER(email_address) = UPPER(p_email_address)
360 AND start_date <= sysdate AND nvl(end_date, sysdate + 1) > sysdate;
361
362
363 cursor get_user_hz (p_email_address in fnd_user.user_name%type) is
364 SELECT fu.user_name
365 FROM hz_parties p, fnd_user fu
366 WHERE p.party_id = fu.person_party_id
367 AND UPPER(p.email_address) = UPPER(p_email_address);
368
369
370 begin
371
372 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
373 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
374 'fnd.plsql.UMXVUPWB.find_users_w_email.begin',
375 'p_email: ' || p_email);
376 end if;
377
378 -- Try to get the email from fnd_user
379 --open get_user_fnd (p_email);
380 --fetch get_user_fnd into l_user_name;
381 for aUser in get_user_fnd(p_email) loop
382 WF_DIRECTORY.GetRoleInfo ( aUser.user_name, l_role_display_name, l_email_address,
383 l_notification_preference, l_language, l_territory);
384 x_users(i).user_name := aUser.user_name;
385 x_users(i).notification_preference := l_notification_preference;
386 i := i + 1;
387 end loop;
388
389 if ( i = 0 ) then
390 for party in get_user_hz(p_email) loop
391 l_user_name := party.user_name;
392 WF_DIRECTORY.GetRoleInfo (l_user_name, l_role_display_name, l_email_address,
393 l_notification_preference, l_language, l_territory);
394 x_users(i).user_name := party.user_name;
395 x_users(i).notification_preference := l_notification_preference;
396 i := i + 1;
397 end loop;
398 end if;
399
400 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
401 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
402 'fnd.plsql.UMXVUPWB.find_users_w_email.end',
403 ' | l_user_name: ' || l_user_name );
404 end if;
405
406 end find_users_w_email;
407
408
409
410
411 procedure Start_workflow( p_request_type in varchar2,
412 p_user_name in varchar2,
413 p_email_address in varchar2,
414 p_role_name in varchar2,
415 p_notification_preference in varchar2,
416 x_itemkey out nocopy varchar2) is
417 begin
418
419 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
420 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
421 'fnd.plsql.UMX_LOGIN_HELP_PVT.start_workflow.begin',
422 'p_user_name: ' || p_user_name ||
423 ' | p_email_address: ' || p_email_address ||
424 ' | p_role_name: ' || p_role_name );
425 end if;
426
427 select to_char (UMX_LOGIN_HELP_WF_S.Nextval) into x_itemkey from dual;
428
429 -- start the workflow that will send the notification and reset
430 -- the password
431
432 wf_engine.CreateProcess(itemtype => g_itemtype,
433 itemkey => x_itemkey,
434 process => 'LOGIN_HELP');
435
436 wf_engine.SetItemAttrText(itemtype => g_itemtype,
437 itemkey => x_itemkey,
438 aname => 'REQUEST_TYPE',
439 avalue => p_request_type);
440
441 if ( p_user_name is not null ) then
442 wf_engine.SetItemAttrText(itemtype => g_itemtype,
443 itemkey => x_itemkey,
444 aname => 'USER_NAME',
445 avalue => p_user_name);
446 end if;
447
448 if p_email_address is not null then
449 wf_engine.SetItemAttrText (itemtype => g_itemtype,
450 itemkey => x_itemkey,
451 aname => 'EMAIL_ADDRESS',
452 avalue => p_email_address);
453 end if;
454
455 if p_role_name is not null then
456 wf_engine.SetItemAttrText (itemtype => g_itemtype,
457 itemkey => x_itemkey,
458 aname => 'X_USER_ROLE',
459 avalue => p_role_name);
460 end if;
461
462 if p_notification_preference is not null then
463 wf_engine.SetItemAttrText (itemtype => g_itemtype,
464 itemkey => x_itemkey,
465 aname => 'NOTIFICATION_PREFERENCE',
466 avalue => p_notification_preference);
467 end if;
468
469 wf_engine.StartProcess(itemtype => g_itemtype,
470 itemkey => x_itemkey);
471
472 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
473 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
474 'fnd.plsql.UMXVUPWB.start_workflow.end',
475 'x_itemkey: ' || x_itemkey);
476 end if;
477
478 end start_workflow;
479
480
481 procedure ForgottenPwdPvt
482 (p_username in fnd_user.user_name%type,
483 x_return_status out NOCOPY varchar2,
484 x_message_name out NOCOPY varchar2) is
485
486 l_user_name fnd_user.user_name%type := upper(p_username);
487 l_email_address varchar2(2000);
488 l_role_name wf_local_roles.name%type;
489 l_password varchar2(40);
490 l_notification_preference wf_local_roles.notification_preference%type;
491 l_result wf_item_activity_statuses.activity_result_code%type;
492 l_status wf_item_activity_statuses.activity_status%type;
493 l_itemkey wf_items.item_key%type;
494 l_pwdChangeable boolean := null;
495 l_nonExistentUser boolean := false;
496 l_max_req pls_integer := getMaxNoActiveReqs();
497 l_too_many_prev_reqs boolean := false;
498
499
500 begin
501
502 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
503 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
504 'fnd.plsql.UMXVUPWB.ForgottenPwdPvt.begin',
505 'p_username: ' || p_username );
506 end if;
507
508 -- initialize the return status
509 x_return_status := FND_API.G_RET_STS_ERROR;
510
511
512 if ( getCurrNoActiveReqs(l_user_name) >= l_max_req ) then
513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
514 x_message_name := 'UMX_LOGIN_HELP_2_MANY_REQS';
515 return;
516 end if;
517 -- validate required fields
518 -- validate user name
519 if FND_SSO_Manager.isPasswordChangeable (l_user_name) THEN
520 get_email_address (l_user_name, l_role_name, l_email_address,
521 l_notification_preference, x_message_name);
522 if (x_message_name is null) then
523 if (l_email_address is not null) then
524 -- Start Workflow to reset user's password.
525 start_workflow ( 'P', l_user_name, l_email_address, l_role_name,
526 l_notification_preference, l_itemkey);
527 -- Check if the workflow is in error status
528 wf_engine.itemstatus (g_itemtype, l_itemkey, l_status, l_result);
529 if (l_status = 'ERROR') then
530 -- Error status
531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532 x_message_name := 'UMX_LOGIN_HELP_UNEXP_ERROR';
533 else
534 -- Not error, return notified message.
535 x_return_status := FND_API.G_RET_STS_SUCCESS;
536 x_message_name := 'UMX_LOGIN_HELP_SUB_SUC_MSG_1';
537 end if;
538 else -- email address is null
539 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
540 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
541 'fnd.plsql.UMXVUPWB.ResetPwdPvt', 'UMX_LOGIN_HELP_NULL_EMAIL');
542 end if;
543 x_message_name := 'UMX_LOGIN_HELP_UNEXP_ERROR';
544 end if;
545 end if;
546 else -- cannot change password for this user
547 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
548 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
549 'fnd.plsql.UMXVUPWB.ResetPwdPvt', 'UMX_LOGIN_HELP_PWD_EXTERNAL');
550 end if;
551 x_message_name := 'UMX_LOGIN_HELP_UNEXP_ERROR';
552 end if;
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.ResetPwdPvt.end',
557 'x_return_status: ' || x_return_status ||
558 ' | x_message_name: ' || x_message_name);
559 end if;
560 EXCEPTION
561 when FND_SSO_MANAGER.userNotFound then
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563 --x_message_name := 'UMX_FORGOT_PWD_UNEXP_ERR_MSG';
564 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
565 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
566 'fnd.plsql.UMXVUPWB.ResetPwdPvt', 'UMX_LOGIN_HELP_INVALID_ACCT');
567 end if;
568 x_message_name := 'UMX_LOGIN_HELP_UNEXP_ERROR';
569
570 END ForgottenPwdPvt;
571
572
573 procedure ForgottenPwd(p_username in fnd_user.user_name%type,
574 x_return_status out NOCOPY varchar2,
575 x_message_name out NOCOPY varchar2) is
576 begin
577
578 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
579 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
580 'fnd.plsql.UMXVUPWB.ForgottenPwdPvt.begin',
581 'p_username: ' || p_username
582 );
583 end if;
584
585 ForgottenPwdPvt
586 (p_username => p_username,
587 x_return_status => x_return_status,
588 x_message_name => x_message_name);
589
590
591 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
592 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
593 'fnd.plsql.UMXVUPWB.ForgottenPwdPvt.end',
594 'x_return_status: ' || x_return_status ||
595 ' | x_message_name: ' || x_message_name);
596 end if;
597
598 end ForgottenPwd;
599
600
601
602
603
604 procedure ForgottenUnamePvt
605 (p_email in fnd_user.email_address%type,
606 x_return_status out NOCOPY varchar2,
607 x_message_name out NOCOPY varchar2) is
608
609 l_email_address varchar2(2000) := p_email;
610 l_role_name wf_local_roles.name%type;
611 l_notification_preference wf_local_roles.notification_preference%type;
612 l_result wf_item_activity_statuses.activity_result_code%type;
613 l_status wf_item_activity_statuses.activity_status%type;
614 l_itemkey wf_items.item_key%type;
615 l_user_name fnd_user.user_name%type;
616 l_user_list UsersWEmail;
617 i pls_integer;
618 l_first pls_integer;
619 e pls_integer := 0;
620 l_max_req pls_integer := g_def_no_max_request;
621 l_too_many_prev_reqs boolean := false;
622
623 begin
624
625 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
626 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
627 'fnd.plsql.UMXVUPWB.ForgottenUnamePvt.begin',
628 'p_email: ' || p_email );
629 end if;
630
631 -- initialize the return status
632 x_return_status := FND_API.G_RET_STS_SUCCESS;
633 x_message_name := 'UMX_LOGIN_HELP_SUB_SUC_MSG_2';
634
635 find_users_w_email( l_email_address, l_user_list );
636
637 i := l_user_list.first();
638 l_first := i;
639 if ( i is null ) then
640 x_return_status := FND_API.G_RET_STS_ERROR;
641 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
642 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
643 'fnd.plsql.UMXVUPWB.ForgottenUnamePvt.begin',
644 'UMX_LOGIN_HELP_NO_USER_FOUND' );
645 end if;
646 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
647 else
648 WHILE ( i is not null and x_return_status = FND_API.G_RET_STS_SUCCESS ) LOOP
649 l_user_name := l_user_list(i).user_name;
650 l_notification_preference := l_user_list(i).notification_preference;
651 -- Bug13583453 To avoid creating new Ad-Hoc role
652 l_role_name := l_user_name;
653
654 if ( i = l_first ) then
655 l_max_req := getMaxNoActiveReqs();
656 if ( getCurrNoActiveReqs(l_user_name) < l_max_req ) then
657 l_too_many_prev_reqs := false;
658 else
659 l_too_many_prev_reqs := true;
660 end if;
661 end if;
662
663 if ( not l_too_many_prev_reqs ) then
664 -- Start Workflow to reset user's password.
665 start_workflow ( 'U', l_user_name, l_email_address, l_role_name,
666 l_notification_preference, l_itemkey);
667 -- Check if the workflow is in error status
668
669 wf_engine.itemstatus (g_itemtype, l_itemkey, l_status, l_result);
670 if (l_status = 'ERROR') then
671 -- Error status
672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
673 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
674 return;
675 end if;
676 else
677 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
678 x_message_name := 'UMX_LOGIN_HELP_2_MANY_REQS';
679 return;
680 end if;
681
682 i := l_user_list.NEXT( i );
683 END LOOP; --End of UserList Loop
684 END IF;
685
686
687 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
688 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
689 'fnd.plsql.UMXLHLPB.ForgottenUnamePvt.end',
690 'x_return_status: ' || x_return_status ||
691 ' | x_message_name: ' || x_message_name);
692 end if;
693 EXCEPTION
694 when others then
695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
696 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
697
698 END ForgottenUnamePvt;
699
700
701 procedure ForgottenUname
702 (p_email in fnd_user.email_address%type,
703 x_return_status out NOCOPY varchar2,
704 x_message_name out NOCOPY varchar2) is
705 begin
706
707 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
708 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
709 'fnd.plsql.UMXVUPWB.ForgottenUname.begin',
710 'p_email: ' || p_email
711 );
712 end if;
713
714 ForgottenUnamePvt
715 (p_email => p_email,
716 x_return_status => x_return_status,
717 x_message_name => x_message_name);
718
719
720 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
721 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
722 'fnd.plsql.UMXVUPWB.ForgottenPwdPvt.end',
723 'x_return_status: ' || x_return_status ||
724 ' | x_message_name: ' || x_message_name);
725 end if;
726
727 end ForgottenUname;
728
729
730 function GenerateAuthKey return varchar2 is
731
732 l_password_len int := 20;
733 x_auth_key varchar2(400);
734 ascii_offset int := 65;
735
736 begin
737
738 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
739 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
740 'fnd.plsql.UMX_LOGIN_HELP_PVT.GenerateAuthKey.begin', '');
741 end if;
742
743
744 for j in 1..l_password_len loop
745 if (mod(abs(dbms_random.random),2) = 1) then
746 -- generate number
747 x_auth_key := x_auth_key || mod(abs(FND_CRYPTO.SmallRandomNumber),10);
748 else
749 -- generate character
750 x_auth_key := x_auth_key || fnd_global.local_chr(mod(abs(FND_CRYPTO.SmallRandomNumber),26)
751 + ascii_offset);
752 end if;
753 end loop;
754
755 -- terminate the random number generator
756 --dbms_random.terminate;
757
758 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
759 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
760 'fnd.plsql.UMX_LOGIN_HELP_PVT.GenerateAuthKey.end',
761 'x_auth_key: ' || x_auth_key);
762 end if;
763
764 return x_auth_key;
765
766 end GenerateAuthKey;
767
768
769 Procedure GenAuthKey(itemtype in varchar2,
770 itemkey in varchar2,
771 actid in number,
772 funcmode in varchar2,
773 resultout in out NOCOPY varchar2) is
774
775 l_auth_key varchar2(400);
776 l_user_name fnd_user.user_name%type;
777
778 begin
779 if (funcmode = 'RUN') then
780 -- Check if the password is already set.
781 /*
782 l_auth_key := WF_ENGINE.GetActivityAttrText (
783 itemtype => itemtype,
784 itemkey => itemkey,
785 actid => actid,
786 aname => 'AUTH_KEY');
787 */
788
789 l_auth_key := GenerateAuthKey();
790 if (l_auth_key is not null) then
791 -- code for validating the generated username
792 -- get the username
793 /*
794 l_user_name := WF_ENGINE.GetActivityAttrText(
795 itemtype => itemtype,
796 itemkey => itemkey,
797 actid => actid,
798 aname => 'USER_NAME');
799 */
800
801 wf_engine.SetItemAttrText (itemtype => itemtype,
802 itemkey => itemkey,
803 aname => 'AUTH_KEY',
804 avalue => l_auth_key);
805
806 end if;
807 end if;
808
809 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
810
811 exception
812 when others then
813 Wf_Core.Context('UMX_LOGIN_HELP_PVT', 'GenAuthKey', itemtype, itemkey, actid);
814 raise;
815 end;
816
817
818 Procedure GenUrl2ResetPwdPg(itemtype in varchar2,
819 itemkey in varchar2,
820 actid in number,
821 funcmode in varchar2,
822 resultout in out NOCOPY varchar2) is
823 l_auth_key varchar2(400);
824 l_param varchar2(32000);
825 l_url varchar2(32000) := 'http://yahoo.com';
826 l_user_name fnd_user.user_name%type;
827
828 l_link_name varchar2(400) := 'Click here';
829
830
831 begin
832
833 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
834 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
835 'fnd.plsql.UMXVUPWB.GenUrl2ResetPwdPg.begin', '');
836 end if;
837
838 if (funcmode = 'RUN') then
839 -- Check if the password is already set.
840
841
842 l_auth_key := WF_ENGINE.GetItemAttrText (
843 itemtype => itemtype,
844 itemkey => itemkey,
845 aname => 'AUTH_KEY');
846
847
848 l_user_name := WF_ENGINE.GetItemAttrText(
849 itemtype => itemtype,
850 itemkey => itemkey,
851 aname => 'USER_NAME');
852
853 l_param := '&AUTH_KEY=' || l_auth_key || '&ITEM_KEY=' || itemkey;
854
855 /*
856 function get_run_function_url ( p_function_name in varchar2,
857 p_resp_appl in varchar2,
858 p_resp_key in varchar2,
859 p_security_group_key in varchar2,
860 p_parameters in varchar2 default null,
861 p_override_agent in varchar2 default null )
862 */
863 l_url := FND_RUN_FUNCTION.get_run_function_url(
864 p_function_name => 'UMX_SELF_RESET_PWD',
865 p_resp_appl => null,
866 p_resp_key => null,
867 p_security_group_key => null,
868 p_parameters => l_param);
869
870
871 wf_engine.SetItemAttrText (itemtype => itemtype,
872 itemkey => itemkey,
873 aname => 'RESETURL',
874 avalue => l_url);
875
876 end if;
877
878 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
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.GenUrl2ResetPwdPg.end',
883 'l_url: ' || l_url);
884 end if;
885
886 exception
887 when others then
888 wf_engine.SetItemAttrText (itemtype => itemtype,
889 itemkey => itemkey,
890 aname => 'DEBUG_INFO',
891 avalue => to_char(actid));
892 Wf_Core.Context('UMX_LOGIN_HELP_PVT', 'GenUrl2ResetPwdPg', itemtype, itemkey, actid);
893 raise;
894 end GenUrl2ResetPwdPg;
895
896 Procedure GenUrl2LoginPg(itemtype in varchar2,
897 itemkey in varchar2,
898 actid in number,
899 funcmode in varchar2,
900 resultout in out NOCOPY varchar2) is
901 l_url varchar2(32000);
902 begin
903
904 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
905 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
906 'fnd.plsql.UMXVUPWB.GenUrl2LoginPg.begin', '');
907 end if;
908
909 if (funcmode = 'RUN') then
910 l_url := FND_SSO_MANAGER.getloginurl;
911
912 wf_engine.SetItemAttrText (itemtype => itemtype,
913 itemkey => itemkey,
914 aname => 'LOGINURL',
915 avalue => l_url);
916 end if;
917
918 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
919
920
921 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
922 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
923 'fnd.plsql.UMXVUPWB.GenUrl2LoginPg.end',
924 'l_url: ' || l_url);
925 end if;
926
927 end GenUrl2LoginPg;
928
929
930 Procedure DisableAccount(itemtype in varchar2,
931 itemkey in varchar2,
932 actid in number,
933 funcmode in varchar2,
934 resultout in out NOCOPY varchar2) is
935 l_user_name fnd_user.user_name%type;
936 begin
937
938 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
939 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
940 'fnd.plsql.UMXVUPWB.DisableAccount.begin', '');
941 end if;
942
943 if (funcmode = 'RUN') then
944 l_user_name := WF_ENGINE.GetItemAttrText (
945 itemtype => itemtype,
946 itemkey => itemkey,
947 aname => 'USER_NAME');
948
949 if ( l_user_name is not null ) then
950 fnd_user_pkg.disableuser( username => l_user_name );
951 end if;
952 end if;
953
954 resultout := WF_ENGINE.eng_completed || ':' || WF_ENGINE.eng_null;
955
956
957 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
958 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
959 'fnd.plsql.UMXVUPWB.DisableAccount.end',
960 'l_user_name: ' || l_user_name);
961 end if;
962
963 end DisableAccount;
964 PROCEDURE ValidateAuthKey( p_authkey in varchar2,
965 p_itemkey in varchar2,
966 x_no_attempts out NOCOPY varchar2,
967 x_return_status out NOCOPY varchar2,
968 x_message_name out NOCOPY varchar2)
969 IS
970 l_user_name fnd_user.user_name%type;
971 l_authkey varchar2(400);
972 l_notification_preference wf_local_roles.notification_preference%type;
973 l_email_address varchar2(2000);
974 l_pwd_changeable boolean := true;
975 l_role_name wf_local_roles.name%type;
976 l_item_status varchar2(8);
977 l_item_result varchar2(30);
978
979 BEGIN
980 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
981 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
982 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidateResetPwdReq.begin',
983 ' | p_authkey: ' || p_authkey ||
984 ' | p_itemkey: ' || p_itemkey );
985 end if;
986
987
988 WF_ENGINE.itemstatus( g_itemtype, p_itemkey, l_item_status, l_item_result );
989
990 if ( l_item_status <> 'ACTIVE' ) then
991 x_return_status := 'E';
992 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
993 return;
994 end if;
995 x_no_attempts := WF_ENGINE.GetItemAttrText(g_itemtype, p_itemkey, 'MAX_NO_ATTEMPT');
996 if ( to_number( x_no_attempts ) <= 0 ) then
997 x_return_status := 'E';
998 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
999 return;
1000 end if;
1001
1002
1003 l_authkey := WF_ENGINE.GetItemAttrText(g_itemtype, p_itemkey, 'AUTH_KEY');
1004
1005 if ( l_authkey = p_authkey ) then
1006 x_return_status := 'S';
1007 return;
1008 else
1009 x_return_status := 'E';
1010 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
1011 decrementAttemptCounter( p_itemkey, x_no_attempts);
1012 return;
1013 end if;
1014
1015
1016 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1017 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1018 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidateResetPwdReq.end',
1019 'x_return_status: ' || x_return_status ||
1020 'x_message_name: ' || x_message_name );
1021 end if;
1022
1023 exception
1024 when others then
1025 x_return_status := 'E';
1026
1027 if ( x_message_name is null ) then
1028 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
1029 end if;
1030
1031 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1032 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1033 'Exception in fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePwdResetReq.end',
1034 'x_return_status: ' || x_return_status ||
1035 'x_message_name: ' || x_message_name ||
1036 fnd_message.get );
1037 end if;
1038
1039 end ValidateAuthKey;
1040
1041
1042
1043 PROCEDURE ValidateResetPwdReq (p_username in fnd_user.user_name%type,
1044 p_authkey in varchar2,
1045 p_itemkey in varchar2,
1046 x_no_attempts out NOCOPY varchar2,
1047 x_return_status out NOCOPY varchar2,
1048 x_message_name out NOCOPY varchar2)
1049 IS
1050 l_user_name fnd_user.user_name%type;
1051 l_authkey varchar2(400);
1052 l_notification_preference wf_local_roles.notification_preference%type;
1053 l_email_address varchar2(2000);
1054 l_pwd_changeable boolean := true;
1055 l_role_name wf_local_roles.name%type;
1056 l_item_status varchar2(8);
1057 l_item_result varchar2(30);
1058
1059 BEGIN
1060 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1061 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1062 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidateResetPwdReq.begin',
1063 'p_username: ' || p_username ||
1064 ' | p_authkey: ' || p_authkey ||
1065 ' | p_itemkey: ' || p_itemkey );
1066 end if;
1067
1068 WF_ENGINE.itemstatus( g_itemtype, p_itemkey, l_item_status, l_item_result );
1069
1070 if ( l_item_status <> 'ACTIVE' ) then
1071 x_return_status := 'E';
1072 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
1073 return;
1074 end if;
1075
1076 x_no_attempts := WF_ENGINE.GetItemAttrText(g_itemtype, p_itemkey, 'MAX_NO_ATTEMPT');
1077
1078 if ( to_number( x_no_attempts ) <= 0 ) then
1079 x_return_status := 'E';
1080 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
1081 return;
1082 end if;
1083
1084 l_user_name := WF_ENGINE.GetItemAttrText(g_itemtype, p_itemkey, 'USER_NAME');
1085 l_authkey := WF_ENGINE.GetItemAttrText(g_itemtype, p_itemkey, 'AUTH_KEY');
1086
1087 if ( l_authkey = p_authkey ) then
1088 x_return_status := 'S';
1089 else
1090 x_return_status := 'E';
1091 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
1092 decrementAttemptCounter( p_itemkey, x_no_attempts);
1093 return;
1094 end if;
1095
1096 if ( l_user_name <> p_username ) then
1097 x_return_status := 'E';
1098 x_message_name := 'UMX_RESET_PWD2_INVALID_UNAME';
1099 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) then
1100 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1101 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPwdPvt.ValidateResetPwdReq',
1102 'failed');
1103 end if;
1104 decrementAttemptCounter( p_itemkey => p_itemKey,
1105 x_no_attempts => x_no_attempts);
1106 return;
1107 end if;
1108
1109 if not FND_SSO_Manager.isPasswordChangeable(p_username) then
1110 x_return_status := 'E';
1111 x_message_name := 'UMX_LOGIN_HELP_PWD_EXTERNAL';
1112 return;
1113 end if;
1114
1115 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1116 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1117 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidateResetPwdReq.end',
1118 'x_return_status: ' || x_return_status ||
1119 'x_message_name: ' || x_message_name );
1120 end if;
1121
1122 exception
1123 when others then
1124 x_return_status := 'E';
1125
1126 if ( x_message_name is null ) then
1127 x_message_name := 'UMX_RESET_PWD2_VALID_FAILED';
1128 end if;
1129
1130 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1131 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1132 'Exception in fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePwdResetReq.end',
1133 'x_return_status: ' || x_return_status ||
1134 'x_message_name: ' || x_message_name ||
1135 fnd_message.get );
1136 end if;
1137
1138 end ValidateResetPwdReq;
1139
1140
1141 PROCEDURE ValidatePassword( p_username in fnd_user.user_name%type,
1142 x_password in out NOCOPY varchar2,
1143 x_return_status out NOCOPY varchar2,
1144 x_message_name out NOCOPY varchar2,
1145 x_message_data out NOCOPY varchar2 )
1146 IS
1147 l_result varchar2(10);
1148 begin
1149
1150 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1151 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1152 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePassword.begin',
1153 'p_username: ' || p_username ||' | x_password: ' || x_password);
1154 end if;
1155
1156
1157 l_result := FND_WEB_SEC.validate_password (p_username, x_password);
1158
1159 IF ( l_result <> 'Y' ) THEN
1160 -- Throw exception as even though generated password 100 times, but
1161 -- cannot pass validation criteria
1162 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1163 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1164 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePassword',
1165 'Validate Password failed.');
1166 end if;
1167 x_return_status := 'E';
1168 x_message_name := 'UMX_RESET_PWD2_VALID_FAIL';
1169 x_message_data := fnd_message.get;
1170 ELSE
1171 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1172 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1173 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePassword',
1174 'Validate Password success.');
1175 end if;
1176 x_return_status := 'S';
1177 x_message_name := 'UMX_RESET_PWD2_CONFIRM_MSG';
1178 END IF;
1179 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1180 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1181 'fnd.plsql.UMX_LOGIN_HELP_PVT.ValidatePassword.end',
1182 'x_password: ' || x_password);
1183 end if;
1184
1185 end;
1186 procedure decrementAttemptCounter(
1187 p_itemkey in varchar2,
1188 x_no_attempts out NOCOPY varchar2) IS
1189 l_no_attempt pls_integer := 0;
1190 begin
1191 -- retrieve no of attempts from wf
1192 l_no_attempt := wf_engine.getitemattrnumber(
1193 itemtype => g_itemtype,
1194 itemkey => p_itemkey,
1195 aname => 'MAX_NO_ATTEMPT');
1196 l_no_attempt := l_no_attempt - 1;
1197
1198 wf_engine.SetItemAttrNumber (itemtype => g_itemtype,
1199 itemkey => p_itemkey,
1200 aname => 'MAX_NO_ATTEMPT',
1201 avalue => l_no_attempt );
1202 x_no_attempts := to_char( l_no_attempt );
1203
1204 end decrementAttemptCounter;
1205
1206
1207 -- Private Method
1208 procedure ResetPwdPvt (p_username in fnd_user.user_name%type,
1209 --p_usernameurl in fnd_user.user_name%type,
1210 p_password in varchar2 default null,
1211 p_itemkey in varchar2,
1212 p_authkey in varchar2,
1213 x_no_attempts out NOCOPY varchar2,
1214 x_return_status out NOCOPY varchar2,
1215 x_message_name out NOCOPY varchar2,
1216 x_message_data out NOCOPY varchar2) IS
1217
1218 l_user_name fnd_user.user_name%type := upper(p_username);
1219 -- l_email_address varchar2(2000);
1220 -- l_role_name wf_local_roles.name%type;
1221 l_password varchar2(40) := p_password;
1222 -- l_notification_preference wf_local_roles.notification_preference%type;
1223 l_result wf_item_activity_statuses.activity_result_code%type;
1224 -- l_status wf_item_activity_statuses.activity_status%type;
1225 -- l_itemkey wf_items.item_key%type;
1226 l_pwdChangeable boolean := null;
1227 l_updatePwdFailedException exception;
1228
1229 l_item_status varchar2(8);
1230 l_item_result varchar2(30);
1231
1232 begin
1233 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1234 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1235 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPwdPvt.begin',
1236 'p_username: ' || p_username || ' | p_password: ' || p_password);
1237 end if;
1238
1239
1240 x_return_status := FND_API.G_RET_STS_ERROR;
1241
1242 ValidateResetPwdReq( p_username => p_username,
1243 p_authkey => p_authkey,
1244 p_itemkey => p_itemkey,
1245 x_no_attempts => x_no_attempts,
1246 x_return_status => x_return_status,
1247 x_message_name => x_message_name );
1248
1249 if ( x_return_status <> 'S' ) then
1250 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) then
1251 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1252 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPwdPvt.ValidateResetPwdReq',
1253 'failed');
1254 end if;
1255 return;
1256 end if;
1257
1258
1259 -- validate the user's new password for system-enforcement
1260 ValidatePassword( p_username => l_user_name,
1261 x_password => l_password,
1262 x_return_status => x_return_status,
1263 x_message_name => x_message_name,
1264 x_message_data => x_message_data );
1265
1266 if ( x_return_status = 'S' ) then
1267 BEGIN
1268
1269 FND_USER_PKG.UpdateUserParty (
1270 x_user_name => l_user_name,
1271 x_owner => l_user_name,
1272 x_unencrypted_password => l_password,
1273 x_password_date => sysdate);
1274 EXCEPTION
1275 when others then
1276 x_return_status := 'E';
1277 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
1278 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
1279 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
1280 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPwdPvt.FND_USER_PKG.UpdateUserParty',
1281 'x_no_attempts: ' || x_no_attempts ||
1282 'x_return_status: ' || x_return_status ||
1283 ' | x_message_name: ' || x_message_name );
1284 end if;
1285 END;
1286 end if;
1287
1288 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1289 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1290 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPwdPvt.end',
1291 'x_return_status: ' || x_return_status ||
1292 ' | x_message_name: ' || x_message_name );
1293 end if;
1294 EXCEPTION
1295 when others then
1296 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
1297 x_message_data := fnd_message.get;
1298
1299 END ResetPwdPvt;
1300
1301
1302 procedure ResetPassword( p_username in fnd_user.user_name%type,
1303 --p_usernameurl in fnd_user.user_name%type,
1304 p_password in varchar2 default null,
1305 p_itemkey in varchar2,
1306 p_authkey in varchar2,
1307 x_no_attempts out NOCOPY varchar2,
1308 x_return_status out NOCOPY varchar2,
1309 x_message_name out NOCOPY varchar2,
1310 x_message_data out NOCOPY varchar2) is
1311
1312 begin
1313
1314 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1315 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1316 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPassword.begin',
1317 'p_username: ' || p_username ||
1318 ' | p_password: ' || p_password);
1319 end if;
1320
1321 /*
1322 x_return_status := 'S';
1323 x_message_name := 'UMX_RESET_PWD2_CONFIRM_MSG';
1324 x_message_data := 'TEST';
1325 */
1326 ResetPwdPvt (p_username => p_username,
1327 --p_usernameurl => p_usernameurl,
1328 p_password => p_password,
1329 p_itemkey => p_itemkey,
1330 p_authkey => p_authkey,
1331 x_no_attempts => x_no_attempts,
1332 x_return_status => x_return_status,
1333 x_message_name => x_message_name,
1334 x_message_data => x_message_data);
1335
1336 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1337 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1338 'fnd.plsql.UMX_LOGIN_HELP_PVT.ResetPassword.end',
1339 'x_return_status: ' || x_return_status ||
1340 ' | x_message_name: ' || x_message_name);
1341 end if;
1342
1343 end ResetPassword;
1344
1345 procedure CompleteActivity( p_itemKey in varchar2,
1346 x_return_status out NOCOPY varchar2,
1347 x_message_name out NOCOPY varchar2) is
1348 l_item_status varchar2(8);
1349 l_item_result varchar2(30);
1350 l_role_name varchar2(200);
1351 -- changes to figure out if role is a ad hoc role,7445188
1352 l_orig_system wf_local_roles.orig_system%TYPE;
1353 l_orig_system_id wf_local_roles.orig_system_id%type;
1354 begin
1355
1356 x_return_status := 'S';
1357 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1358 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1359 'fnd.plsql.UMX_LOGIN_HELP_PVT.CompleteActivity.begin',
1360 ' p_itemkey: ' || p_itemKey );
1361 end if;
1362
1363 l_role_name := WF_ENGINE.GetItemAttrText (
1364 itemtype => g_itemtype,
1365 itemkey => p_itemKey,
1366 aname => 'X_USER_ROLE');
1367 -- get orig system info and check if this is an ad-hoc role ,7445188
1368 wf_directory.getroleOrigsysinfo(l_role_name,l_orig_system,l_orig_system_id);
1369 if l_orig_system ='WF_LOCAL_ROLES' then
1370 wf_directory.setAdHocRoleExpiration(l_role_name);
1371 end if;
1372
1373 wf_engine.abortprocess(
1374 itemtype => g_itemtype,
1375 itemkey => p_itemKey,
1376 process => null,
1377 result => null,
1378 verify_lock => false,
1379 cascade => true);
1380
1381 x_return_status := 'S';
1382
1383 if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) then
1384 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1385 'fnd.plsql.UMX_LOGIN_HELP_PVT.CompleteActivity.end',
1386 'x_return_status: ' || x_return_status ||
1387 ' | x_message_name: ' || x_message_name);
1388 end if;
1389 EXCEPTION
1390 when others then
1391 x_return_status := 'E';
1392 x_message_name := 'UMX_RESET_PWD2_UNEXP_ERROR';
1393
1394 end CompleteActivity;
1395
1396 END UMX_LOGIN_HELP_PVT;