[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_PASSWORD_PVT
Source
1 PACKAGE BODY JTF_UM_PASSWORD_PVT as
2 /* $Header: JTFVUMPB.pls 120.2 2005/12/07 05:17:05 vimohan ship $ */
3 -- Start of Comments
4 -- Package name : JTF_UM_PASSWORD_PVT
5 -- Purpose : generate password and send email to user with the password.
6 -- History :
7
8 -- KCHERVEL 12/03/01 Created
9 -- NOTE :
10 -- End of Comments
11 /* ------------------------------------------------------------------------
12 -- Revision history
13 -- 11/26/2002 kchervel modified calls to jtf_um_util_pvt.get_wf_user as
14 -- the signature has changed
15 -- 05/23/2002 kchervel set the password date to null when the password is
16 -- reset. This forces the user to change password.
17 -- 04/14/2005 snellepa modified queries for bug 4287135
18 */
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_UM_PASSWORD_PVT';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'JTFVUMPB.pls';
22
23 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
24 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
25
26 G_MODULE VARCHAR2(40) := 'JTF.UM.PLSQL.PASSWORD';
27 l_is_debug_parameter_on boolean := JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(G_MODULE);
28
29 ascii_offset int := 65;
30 NEWLINE VARCHAR2(1) := fnd_global.newline;
31 TAB VARCHAR2(1) := fnd_global.tab;
32
33 /**
34 * Procedure : generate_password
35 * Type : Private
36 * Pre_reqs :
37 * Description : Creates a password. The length of the password is obtained from the profile
38 * SIGNON_PASSWORD_LENGTH.
39 * Parameters
40 * input parameters : None
41 * output parameters
42 * @return returns a String that can be used as the password
43 * Errors :
44 * Other Comments :
45 */
46 procedure generate_password (p_api_version_number in number,
47 p_init_msg_list in varchar2 := FND_API.G_FALSE,
48 p_commit in varchar2 := FND_API.G_FALSE,
49 p_validation_level in number
50 := FND_API.G_VALID_LEVEL_FULL,
51 x_password out NOCOPY varchar2,
52 x_return_status out NOCOPY varchar2,
53 x_msg_count out NOCOPY number,
54 x_msg_data out NOCOPY varchar2
55 ) is
56
57 l_password_len int := 6;
58 l_api_version_number NUMBER := 1.0;
59 l_api_name VARCHAR2(50) := 'generate_password';
60
61 begin
62
63 -- Write to debug log
64 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Entering API Generate_Password ...');
65 -- JTF_DEBUG_PUB.add_debug_msg('Starting at '||sysdate);
66
67 -- Standard Start of API savepoint
68 SAVEPOINT generate_password;
69
70 -- Standard call to check for call compatibility.
71 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
72 p_api_version_number,
73 l_api_name,
74 G_PKG_NAME)
75 THEN
76 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77 END IF;
78
79
80 -- Initialize message list if p_init_msg_list is set to TRUE.
81 IF FND_API.to_Boolean( p_init_msg_list ) THEN
82 FND_MSG_PUB.initialize;
83 END IF;
84
85 -- Initialize API return status to success
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 --
89 -- API body
90 --
91
92 -- initialize the random number generator
93 dbms_random.initialize(dbms_utility.get_time);
94
95 -- using the profile, determine the length of the random number
96 l_password_len := nvl(fnd_profile.value('SIGNON_PASSWORD_LENGTH'),
97 l_password_len);
98
99 -- generate a random number to determine where to use an alphabet or a
100 -- numeric character for a given position in the password
101
102 for j in 1..l_password_len loop
103 if (mod(abs(dbms_random.random),2) = 1) then
104 -- generate number
105 x_password := x_password || mod(abs(dbms_random.random),10);
106 else
107 -- generate character
108 x_password := x_password || fnd_global.local_chr(mod(abs(dbms_random.random),26)
109 + ascii_offset);
110 end if;
111 end loop;
112
113 -- terminate the random number generator
114 dbms_random.terminate;
115
116 --
117 -- End of API body
118 --
119
120 -- Standard check for p_commit
121 IF FND_API.to_Boolean( p_commit )
122 THEN
123 COMMIT WORK;
124 END IF;
125
126 -- Standard call to get message count and if count is 1, get message info.
127
128 FND_MSG_PUB.Count_And_Get(
129 p_count => x_msg_count,
130 p_data => x_msg_data);
131
132 -- Write to debug log
133 -- JTF_DEBUG_PUB.add_debug_msg('Starting at '||sysdate);
134 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Exiting API Generate_Password ...');
135 --
136
137 EXCEPTION
138 WHEN FND_API.G_EXC_ERROR THEN
139 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
140 P_API_NAME => L_API_NAME
141 ,P_PKG_NAME => G_PKG_NAME
142 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
143 ,P_SQLCODE => SQLCODE
144 ,P_SQLERRM => SQLERRM
145 ,X_MSG_COUNT => X_MSG_COUNT
146 ,X_MSG_DATA => X_MSG_DATA
147 ,X_RETURN_STATUS => X_RETURN_STATUS);
148
149 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
150 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
151 P_API_NAME => L_API_NAME
152 ,P_PKG_NAME => G_PKG_NAME
153 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
154 ,P_SQLCODE => SQLCODE
155 ,P_SQLERRM => SQLERRM
156 ,X_MSG_COUNT => X_MSG_COUNT
157 ,X_MSG_DATA => X_MSG_DATA
158 ,X_RETURN_STATUS => X_RETURN_STATUS);
159
160 WHEN OTHERS THEN
161 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
162 P_API_NAME => L_API_NAME
163 ,P_PKG_NAME => G_PKG_NAME
164 ,P_EXCEPTION_LEVEL => JTF_DEBUG_PUB.G_EXC_OTHERS
165 ,P_SQLCODE => SQLCODE
166 ,P_SQLERRM => SQLERRM
167 ,X_MSG_COUNT => X_MSG_COUNT
168 ,X_MSG_DATA => X_MSG_DATA
169 ,X_RETURN_STATUS => X_RETURN_STATUS);
170 end generate_password;
171
172 /**
173 * Procedure : send_password
174 * Type : Private
175 * Pre_reqs :
176 * Description : this procedure initiates a workflow that sends an email to the user.
177 * Parameters : None
178 * input parameters (see workflow parameters for description)
179 * param requester_user_name (*)
180 * param requester_password (*)
181 * param requester_last_name
182 * param requester_first_name
183 * param usertype_id
184 * param responsibility_id
185 * param application_id
186 * param first_time_user (Possible values 'Y', 'N')
187 * param send_password (Possible values 'Y', 'N')
188 * param confirmation_number
189 *
190 * (*) required fields
191 * output parameters
192 * param x_return_status
193 * param x_msg_data
194 * param x_msg_count
195 * Errors : Expected Errors
196 * requester_user_name or password is null
197 * user is not a valid user in fnd_user (user_name does not exist or is enddated).
198 * user does not have an email in fnd_user table.
199 * Other Comments :
200 * DEFAULTING LOGIC
201 * For the default workflow (JTAUMPSW) that is called by this API the following are valid:
202 * 1. Whether or not approval is needed is determined by the usertype_id.
203 * select 'T' from jtf_um_usertypes_b
204 * where usertype_id = p_usertype_id
205 * and approval_id is not NULL;
206 * 2. If last name and first name are not passed then the last name is defaulted
207 * to the user name and this will be used in the messages within the workflow
208 * 3. If application_id is NULL and responsibility_id is not NULL then
209 * application_id is determined using responsibility_id. Values for all the
210 * profiles (JTF_UM_APPROVAL_URL, JTA_UM_SUPPORT_CONTACT, JTF_UM_MERCHANT_NAME,
211 * JTA_UM_SENDER) are determined using the application id and responsibility_id.
212 * If both are null then the site level values are returned.
213 *
214 */
215
216
217
218 procedure send_password (p_api_version_number in number,
219 p_init_msg_list in varchar2 := FND_API.G_FALSE,
220 p_commit in varchar2 := FND_API.G_FALSE,
221 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
222 p_requester_user_name in varchar2,
223 p_requester_password in varchar2,
224 p_requester_last_name in varchar2 := null,
225 p_requester_first_name in varchar2 := null,
226 p_usertype_id in number := null,
227 p_responsibility_id in number := null,
228 p_application_id in number := null,
229 p_wf_user_name in varchar2 := null,
230 p_first_time_user in varchar2 := 'Y',
231 p_user_verified in varchar2 := 'N',
232 p_confirmation_number in varchar2 := null,
233 p_enrollment_only in varchar2 := 'N',
234 p_enrollment_list in varchar2 := null,
235 x_return_status out NOCOPY varchar2,
236 x_msg_count out NOCOPY number,
237 x_msg_data out NOCOPY varchar2
238 ) is
239
240 l_api_version_number NUMBER := 1.0;
241 l_api_name VARCHAR2(50) := 'SEND_PASSWORD';
242 itemkey number ;
243 itemtype varchar2 (80);
244 processOwner varchar2 (100);
245 processName varchar2(50) := 'SEND_PASSWORD';
246
247 l_email varchar2(240);
248 l_responsibility_id number := p_responsibility_id;
249 l_application_id number := p_application_id;
250 l_requester_user_name varchar2(250) := upper(p_requester_user_name);
251 l_requester_email varchar2(240) := null;
252 l_wf_user_name varchar2(250) := p_wf_user_name;
253
254 CURSOR c_item_key IS SELECT JTF_UM_PSW_WF_S.nextval FROM dual;
255
256 CURSOR c_user(l_user_name in varchar2) IS
257 select email_address
258 from fnd_user
259 where user_name = l_user_name
260 and (nvl(end_date, sysdate+1) > sysdate OR
261 to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
262
263 CURSOR c_wf_user(l_user_name in varchar2) IS
264 select email_address
265 from wf_users
266 where name = l_user_name ;
267
268
269
270 begin
271 JTF_DEBUG_PUB.log_entering_method(G_MODULE, l_api_name);
272
273
274 -- Write to debug log
275 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Entering API send_Password ...');
276 --
277
278 -- Standard Start of API savepoint
279 SAVEPOINT send_password;
280
281 -- Standard call to check for call compatibility.
282 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
283 p_api_version_number,
284 l_api_name,
285 G_PKG_NAME)
286 THEN
287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
288 END IF;
289
290
291 -- Initialize message list if p_init_msg_list is set to TRUE.
292 IF FND_API.to_Boolean( p_init_msg_list ) THEN
293 FND_MSG_PUB.initialize;
294 END IF;
295
296 -- Initialize API return status to success
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299 --
300 -- API body
301 --
302
303 -- Validate required fields for not null values
304
305 if (p_requester_user_name is null) then
306 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
307 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_REQUIRED_FIELD');
308 --FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME, FALSE);
309 FND_MESSAGE.Set_Token('API_NAME', 'sending the password', FALSE);
310 FND_MESSAGE.Set_Token('FIELD', 'USER_NAME', FALSE);
311 FND_MSG_PUB.ADD;
312 END IF;
313 RAISE FND_API.G_EXC_ERROR;
314 end if;
315
316 -- password can be null when sending only enrollments
317
318 if (p_requester_password is null and p_enrollment_only <> 'Y') then
319 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
320 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_REQUIRED_FIELD');
321 --FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME, FALSE);
322 FND_MESSAGE.Set_Token('API_NAME', 'sending the password', FALSE);
323 FND_MESSAGE.Set_Token('FIELD', 'PASSWORD', FALSE);
324 FND_MSG_PUB.ADD;
325 END IF;
326 RAISE FND_API.G_EXC_ERROR;
327 end if;
328
329 -- validate user_name
330
331 -- validate end date and email address using user_name
332 Open c_user(l_requester_user_name);
333 Fetch c_user into l_requester_email;
334 If (c_user%NOTFOUND) then
335 Close c_user;
336 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
337 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_INVALID_FIELD');
338 --FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME, FALSE);
339 FND_MESSAGE.Set_Token('API_NAME', 'sending the password', FALSE);
340 FND_MESSAGE.Set_Token('FIELD', 'USER', FALSE);
341 FND_MESSAGE.Set_Token('VALUE', p_requester_user_name, FALSE);
342 FND_MSG_PUB.ADD;
343 END IF;
344 RAISE FND_API.G_EXC_ERROR;
345 end if;
346 Close c_user;
347
348 -- get email address to which the password needs to be sent
349 if l_wf_user_name is NULL then
350 JTF_UM_UTIL_PVT.get_wf_user(p_api_version_number => 1.0,
351 x_requester_user_name => l_requester_user_name,
352 x_requester_email => l_requester_email ,
353 x_wf_user => l_wf_user_name,
354 x_return_status => x_return_Status,
355 x_msg_count => x_msg_count,
356 x_msg_data => x_msg_Data );
357
358 if x_return_status = FND_API.G_RET_STS_ERROR then
359 RAISE FND_API.G_EXC_ERROR;
360 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
362 end if;
363
364 -- if wf_user is NULL then we need to create an adhoc user
365 -- create adhoc user
366 if l_wf_user_name is NULL then
367 l_wf_user_name := 'JTFUM-'|| l_requester_user_name;
368 JTF_UM_UTIL_PVT.GetAdHocUser (p_api_version_number => 1.0,
369 p_username => l_wf_user_name,
370 p_display_name => l_requester_user_name,
371 p_email_address => l_requester_email,
372 x_return_status => x_return_status,
373 x_msg_data => x_msg_data,
374 x_msg_count => x_msg_count);
375 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
377 end if;
378 end if;
379
380 end if;
381
382 --
383 If l_requester_email is NULL then
384 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
385 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_REQUIRED_EMAIL');
386 --FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME, FALSE);
387 FND_MESSAGE.Set_Token('API_NAME', 'sending the password', FALSE);
388 FND_MESSAGE.Set_Token('USER_NAME',p_requester_user_name, FALSE);
389 FND_MSG_PUB.ADD;
390 END IF;
391 RAISE FND_API.G_EXC_ERROR;
392 end if;
393
394
395
396 -- default the responsibility and application id if needed
397 JTF_UM_UTIL_PVT.getDefaultAppRespId(P_USERNAME => p_requester_user_name,
398 P_RESP_ID => l_responsibility_id,
399 P_APPL_ID => l_application_id,
400 X_RESP_ID => l_responsibility_id,
401 X_APPL_ID => l_application_id);
402
403
404 -- setting workflow parameters
405 -- the WF process owner should be the merchant sysadmin
406 processOwner := nvl (JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
407 name => 'JTA_UM_WORKFLOW_OWNER',
408 responsibility_id => l_responsibility_id,
409 resp_appl_id => l_application_id,
410 application_id => l_application_id,
411 site_level => true), 'SYSADMIN');
412
413 itemtype := nvl(JTF_UM_UTIL_PVT.VALUE_SPECIFIC(
414 name => 'JTA_UM_PASSWORD_GEN_WKF',
415 responsibility_id => l_responsibility_id,
416 resp_appl_id => l_application_id,
417 application_id => l_application_id,
418 site_level => true), 'JTAUMPSW');
419
420 Open c_item_key;
421 Fetch c_item_key into itemkey;
422 Close c_item_key;
423
424 --processName := 'SEND_PASSWORD';
425 --
426 -- Start Process
427 --
428 wf_engine.CreateProcess (itemtype,
429 itemkey,
430 processName);
431
432 -- Set Workflow Attributes
433
434 -- set user item key
435 wf_engine.SetItemUserKey (itemtype => itemtype,
436 itemkey => itemkey,
437 UserKey => substr(p_requester_user_name|| ' request for password',1,80));
438
439 -- set user name
440 wf_engine.SetItemAttrText (itemtype => itemtype,
441 itemkey => itemkey,
442 aname => 'REQUESTER_USER_NAME',
443 avalue => l_requester_user_name);
444
445 -- set user name
446 wf_engine.SetItemAttrText (itemtype => itemtype,
447 itemkey => itemkey,
448 aname => 'WF_USER_NAME',
449 avalue => l_wf_user_name);
450
451 -- set password
452 wf_engine.SetItemAttrText (itemtype => itemtype,
453 itemkey => itemkey,
454 aname => 'REQUESTER_PASSWORD',
455 avalue => p_requester_password);
456
457 -- set name
458 wf_engine.SetItemAttrText (itemtype => itemtype,
459 itemkey => itemkey,
460 aname => 'REQUESTER_LAST_NAME',
461 avalue => p_requester_last_name);
462
463 wf_engine.SetItemAttrText (itemtype => itemtype,
464 itemkey => itemkey,
465 aname => 'REQUESTER_FIRST_NAME',
466 avalue => p_requester_first_name);
467 -- set user type id
468 wf_engine.SetItemAttrNumber (itemtype => itemtype,
469 itemkey => itemkey,
470 aname => 'REQUESTER_USERTYPE_ID',
471 avalue => p_usertype_id);
472
473 -- set responsibility id
474 wf_engine.SetItemAttrNumber (itemtype => itemtype,
475 itemkey => itemkey,
476 aname => 'RESPONSIBILITY_ID',
477 avalue => l_responsibility_id);
478
479 -- set application id
480 wf_engine.SetItemAttrNumber (itemtype => itemtype,
481 itemkey => itemkey,
482 aname => 'APPLICATION_ID',
483 avalue => l_application_id);
484 -- set confirmation id
485 wf_engine.SetItemAttrText (itemtype => itemtype,
486 itemkey => itemkey,
487 aname => 'CONFIRMATION_NUMBER',
488 avalue => p_confirmation_number);
489
490 -- set first time user
491 wf_engine.SetItemAttrText (itemtype => itemtype,
492 itemkey => itemkey,
493 aname => 'FIRST_TIME_USER',
494 avalue => p_first_time_user);
495
496 -- set user verified
497 wf_engine.SetItemAttrText (itemtype => itemtype,
498 itemkey => itemkey,
499 aname => 'USER_VERIFIED',
500 avalue => p_user_verified);
501
502 -- set enrollment only
503 wf_engine.SetItemAttrText (itemtype => itemtype,
504 itemkey => itemkey,
505 aname => 'ENROLLMENT_ONLY',
506 avalue => p_enrollment_only);
507
508 -- set enrollment information
509 wf_engine.SetItemAttrText (itemtype => itemtype,
510 itemkey => itemkey,
511 aname => 'ENROLLMENTS',
512 avalue => p_enrollment_list);
513
514 --
515 -- Launch the send password workflow
516 --
517
518 wf_engine.startProcess(itemtype => itemType,
519 itemkey => itemKey);
520
521
522 --
523 -- End of API body
524 --
525
526 -- Standard check for p_commit
527 IF FND_API.to_Boolean( p_commit )
528 THEN
529 COMMIT WORK;
530 END IF;
531
532 -- Standard call to get message count and if count is 1, get message info.
533
534 FND_MSG_PUB.Count_And_Get(
535 p_count => x_msg_count,
536 p_data => x_msg_data);
537
538 -- Write to debug log
539 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Exiting API send_Password ...');
540 --
541
542 EXCEPTION
543 WHEN FND_API.G_EXC_ERROR THEN
544 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
545 P_API_NAME => L_API_NAME
546 ,P_PKG_NAME => G_PKG_NAME
547 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
548 ,P_SQLCODE => SQLCODE
549 ,P_SQLERRM => SQLERRM
550 ,X_MSG_COUNT => X_MSG_COUNT
551 ,X_MSG_DATA => X_MSG_DATA
552 ,X_RETURN_STATUS => X_RETURN_STATUS);
553
554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
555 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
556 P_API_NAME => L_API_NAME
557 ,P_PKG_NAME => G_PKG_NAME
558 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
559 ,P_SQLCODE => SQLCODE
560 ,P_SQLERRM => SQLERRM
561 ,X_MSG_COUNT => X_MSG_COUNT
562 ,X_MSG_DATA => X_MSG_DATA
563 ,X_RETURN_STATUS => X_RETURN_STATUS);
564
565 WHEN OTHERS THEN
566 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
567 P_API_NAME => L_API_NAME
568 ,P_PKG_NAME => G_PKG_NAME
569 ,P_EXCEPTION_LEVEL => JTF_DEBUG_PUB.G_EXC_OTHERS
570 ,P_SQLCODE => SQLCODE
571 ,P_SQLERRM => SQLERRM
572 ,X_MSG_COUNT => X_MSG_COUNT
573 ,X_MSG_DATA => X_MSG_DATA
574 ,X_RETURN_STATUS => X_RETURN_STATUS);
575
576
577 end send_password;
578
579
580
581 /**
582 * Procedure : set_parameters
583 * Type : Private
584 * Pre_reqs :
585 * Description : this procedure sets all the parameters needed for the email / notifications.
586 * Parameters :
587 * input parameters (see workflow parameters for description)
588 * param requester_user_name
589 * param requester_password
590 * param requester_name
591 * param usertype_id
592 * param responsibility_id
593 * param application_id
594 * param first_time_user
595 * param send_password
596 * param confirmation_id
597 * output parameters
598 * param x_return_status
599 * param x_msg_data
600 * param x_msg_count
601 * Errors :
602 * Other Comments :
603 */
604 procedure set_parameters (
605 itemtype in varchar2,
606 itemkey in varchar2,
607 p_requester_user_name in varchar2,
608 p_usertype_id in number,
609 p_responsibility_id in number ,
610 p_application_id in number,
611 p_requester_first_name in varchar2,
612 p_requester_last_name in varchar2,
613 x_return_status out NOCOPY varchar2) is
614
615 l_approval_needed varchar2(1) := 'F';
616 l_appl_url varchar2(1000);
617 l_sender varchar2(1000);
618 l_support_contact varchar2(1000);
619 l_merchant_name varchar2(1000);
620 l_application_id number;
621
622 cursor approval_needed(user_type_id in number) is
623 select 'T' from jtf_um_usertypes_b
624 where usertype_id = user_type_id
625 and approval_id is not NULL;
626
627 cursor appl_id(p_resp_id in number) is
628 select application_id from fnd_responsibility
629 where responsibility_id = p_resp_id;
630
631 begin
632 -- Initialize API return status to success
633 x_return_status := FND_API.G_RET_STS_SUCCESS;
634
635 l_application_id := p_application_id;
636
637 -- set approval_required
638 -- check if approval is required using the usertype id. If usertype_id
639 -- is NULL then approval_needed is defaulted to 'F'
640
641 if p_usertype_id is not NULL then
642 open approval_needed(p_usertype_id);
643 fetch approval_needed into l_approval_needed;
644 close approval_needed;
645 end if;
646 --dbms_output.put_line('approval needed '||l_approval_needed);
647 wf_engine.SetItemAttrText (itemtype => itemtype,
648 itemkey => itemkey,
649 aname => 'APPROVAL_REQUIRED',
650 avalue => l_approval_needed);
651
652
653 /* -- get application id from responsibility id if needed / possible
654
655 if l_application_id is NULL
656 and p_responsibility_id is not NULL then
657 open appl_id(p_responsibility_id);
658 fetch appl_id into l_application_id;
659 close appl_id;
660 end if; */
661
662 -- set the URL
663 l_appl_url := JTF_UM_UTIL_PVT.value_specific(name => 'JTA_UM_APPL_URL',
664 responsibility_id => p_responsibility_id,
665 resp_appl_id => p_application_id,
666 application_id => p_application_id,
667 site_level => true);
668
669 wf_engine.SetItemAttrText (itemtype => itemtype,
670 itemkey => itemkey,
671 aname => 'APPL_URL',
672 avalue => l_appl_url);
673
674 -- set support contact
675 l_support_contact := JTF_UM_UTIL_PVT.value_specific(
676 name => 'JTA_UM_SUPPORT_CONTACT',
677 responsibility_id => p_responsibility_id,
678 resp_appl_id => p_application_id,
679 application_id => p_application_id,
680 site_level => true);
681
682 wf_engine.SetItemAttrText (itemtype => itemtype,
683 itemkey => itemkey,
684 aname => 'SUPPORT_CONTACT',
685 avalue => l_support_contact);
686
687 -- set merchant name
688 l_merchant_name := JTF_UM_UTIL_PVT.value_specific(name => 'JTF_UM_MERCHANT_NAME',
689 responsibility_id => p_responsibility_id,
690 resp_appl_id => p_application_id,
691 application_id => l_application_id,
692 site_level => true);
693
694 wf_engine.SetItemAttrText (itemtype => itemtype,
695 itemkey => itemkey,
696 aname => 'MERCHANT_NAME',
697 avalue => l_merchant_name);
698
699 -- set sender
700 l_sender := JTF_UM_UTIL_PVT.value_specific(name => 'JTA_UM_SENDER',
701 responsibility_id => p_responsibility_id,
702 resp_appl_id => p_application_id,
703 application_id => l_application_id,
704 site_level => true);
705
706 --l_sender := nvl(l_sender, l_merchant_name);
707 wf_engine.SetItemAttrText (itemtype => itemtype,
708 itemkey => itemkey,
709 aname => 'SENDER',
710 avalue => l_sender);
711 -- set name
712 if p_requester_first_name is NULL and p_requester_last_name is NULL then
713 wf_engine.SetItemAttrText (itemtype => itemtype,
714 itemkey => itemkey,
715 aname => 'REQUESTER_LAST_NAME',
716 avalue => p_requester_user_name);
717 /*
718 wf_engine.SetItemAttrText (itemtype => itemtype,
719 itemkey => itemkey,
720 aname => 'ENROLLMENTS',
721 avalue => p_requester_user_name);
722 */
723 end if;
724
725 end set_parameters;
726
727
728 /**
729 * Procedure : set_parameters
730 * Type : Private
731 * Pre_reqs :
732 * Description : this procedure sets all the parameters needed for the email / notifications.
733 * Parameters :
734 * input parameters
735 * itemtype - A valid item type from (WF_ITEM_TYPES table).
736 * itemkey - A string generated from the application object's primary key.
737 * actid - The function activity(instance id).
738 * funcmode - Run/Cancel/Timeout
739 * output parameters
740 * Resultout - 'COMPLETE:T' if all parameters are set properly
741 * - 'COMPLETE:F' if parameters could not be set
742 *
743 * Errors :
744 * Other Comments :
745 */
746
747 procedure set_parameters ( itemtype in varchar2,
748 itemkey in varchar2,
749 actid in number,
750 funcmode in varchar2,
751 resultout out NOCOPY varchar2) is
752 l_requester_user_name varchar2(1000);
753 l_usertype_id number;
754 l_responsibility_id number;
755 l_application_id number;
756 l_confirmation_id number;
757 l_requester_first_name varchar2(1000);
758 l_requester_last_name varchar2(1000);
759 x_return_status varchar2(10);
760 begin
761
762 if (funcmode = 'RUN') then
763 --
764 -- RUN mode - normal process execution
765 --
766 -- get user name
767 l_requester_user_name := wf_engine.GetItemAttrText (
768 itemtype => itemtype,
769 itemkey => itemkey,
770 aname => 'REQUESTER_USER_NAME');
771
772 -- get first and last name
773 l_requester_first_name := wf_engine.GetItemAttrText (
774 itemtype => itemtype,
775 itemkey => itemkey,
776 aname => 'REQUESTER_FIRST_NAME');
777
778 l_requester_last_name := wf_engine.GetItemAttrText (
779 itemtype => itemtype,
780 itemkey => itemkey,
781 aname => 'REQUESTER_LAST_NAME');
782
783 -- get user type id
784 l_usertype_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
785 itemkey => itemkey,
786 aname => 'REQUESTER_USERTYPE_ID');
787
788 -- get responsibility id
789 l_responsibility_id := wf_engine.GetItemAttrNumber (
790 itemtype => itemtype,
791 itemkey => itemkey,
792 aname => 'RESPONSIBILITY_ID');
793
794 -- get application id
795 l_application_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
796 itemkey => itemkey,
797 aname => 'APPLICATION_ID');
798
799 -- set values of other parameters
800 set_parameters (
801 itemtype => itemtype,
802 itemkey => itemkey,
803 p_requester_user_name => l_requester_user_name,
804 p_usertype_id => l_usertype_id,
805 p_responsibility_id => l_responsibility_id,
806 p_application_id => l_application_id,
807 p_requester_first_name=> l_requester_first_name,
808 p_requester_last_name => l_requester_last_name,
809 x_return_status => x_return_status);
810
811
812 --
813 -- CANCEL mode
814 --
815 elsif (funcmode = 'CANCEL') then
816
817 resultout := 'COMPLETE:';
818 return;
819
820 end if;
821 exception
822 when others then
823 wf_core.context ('JTF_UM_PASSWORD_PVT', 'set_parameters ');
824 raise;
825
826 end set_parameters;
827
828 /**
829 * Procedure : is_first_time_user
830 * Type : Private
831 * Pre_reqs :
832 * Description : this procedure returns 'T' if the user is a first time user
833 * Parameters :
834 * input parameters
835 * itemtype - A valid item type from (WF_ITEM_TYPES table).
836 * itemkey - A string generated from the application object's primary key.
837 * actid - The function activity(instance id).
838 * funcmode - Run/Cancel/Timeout
839 * output parameters
840 * Resultout - 'COMPLETE:T' if the user is a first time user
841 * - 'COMPLETE:F' if the user is not a first time user
842 *
843 * Errors :
844 * Other Comments :
845 */
846 procedure is_first_time_user (itemtype in varchar2,
847 itemkey in varchar2,
848 actid in number,
849 funcmode in varchar2,
850 resultout out NOCOPY varchar2) is
851 l_result varchar2(10);
852 begin
853 l_result := wf_engine.GetItemAttrText (
854 itemtype => itemtype,
855 itemkey => itemkey,
856 aname => 'FIRST_TIME_USER');
857 if l_result = 'N' then
858 resultout := 'COMPLETE:F';
859 else
860 resultout := 'COMPLETE:T';
861 end if;
862
863 return;
864 exception
865 when others then
866 wf_core.context ('JTF_UM_SEND_PASSWORD_WF', 'is_first_time_user');
867 raise;
868 end;
869
870
871 /**
872 * Procedure : approval_required
873 * Type : Private
874 * Pre_reqs :
875 * Description : this procedure returns whether or not an approval is required
876 * Parameters :
877 * input parameters
878 * itemtype - A valid item type from (WF_ITEM_TYPES table).
879 * itemkey - A string generated from the application object's primary key.
880 * actid - The function activity(instance id).
881 * funcmode - Run/Cancel/Timeout
882 * output parameters
883 * Resultout - 'COMPLETE:T' if approval is required
884 * - 'COMPLETE:F' if approval is not required
885 *
886 * Errors :
887 * Other Comments :
888 */
889 procedure is_approval_required (itemtype in varchar2,
890 itemkey in varchar2,
891 actid in number,
892 funcmode in varchar2,
893 resultout out NOCOPY varchar2) is
894 l_result varchar2(10);
895 begin
896 l_result := wf_engine.GetItemAttrText (
897 itemtype => itemtype,
898 itemkey => itemkey,
899 aname => 'APPROVAL_REQUIRED');
900 if l_result = 'F' then
901 resultout := 'COMPLETE:F';
902 else
903 resultout := 'COMPLETE:T';
904 end if;
905
906 return;
907 exception
908 when others then
909 wf_core.context ('JTF_UM_SEND_PASSWORD_WF', 'is_approval_required ');
910 raise;
911 end;
912
913
914 /**
915 * Procedure : user_verified
916 * Type : Private
917 * Pre_reqs :
918 * Description : this procedure returns 'T' if a user is verified and a password can be sent to the user
919 * Parameters :
920 * input parameters
921 * itemtype - A valid item type from (WF_ITEM_TYPES table).
922 * itemkey - A string generated from the application object's primary key.
923 * actid - The function activity(instance id).
924 * funcmode - Run/Cancel/Timeout
925 * output parameters
926 * Resultout - 'COMPLETE:T' if user is verified
927 * - 'COMPLETE:F' if user is not verified
928 *
929 * Errors :
930 * Other Comments :
931 */
932 procedure is_user_verified (itemtype in varchar2,
933 itemkey in varchar2,
934 actid in number,
935 funcmode in varchar2,
936 resultout out NOCOPY varchar2) is
937 l_result varchar2(10);
938 begin
939 l_result := wf_engine.GetItemAttrText (
940 itemtype => itemtype,
941 itemkey => itemkey,
942 aname => 'USER_VERIFIED');
943 if l_result = 'Y' then
944 resultout := 'COMPLETE:T';
945 else
946 resultout := 'COMPLETE:F';
947 end if;
948
949 return;
950 exception
951 when others then
952 wf_core.context ('JTF_UM_SEND_PASSWORD_WF', 'is_user_verified');
953 raise;
954 end;
955
956
957 /**
958 * Procedure : enrollment_only
959 * Type : Public
960 * Pre_reqs :
961 * Description : this procedure returns 'T' if only enrollment information should be sent to the user.
962 * Parameters :
963 * input parameters
964 * itemtype - A valid item type from (WF_ITEM_TYPES table).
965 * itemkey - A string generated from the application object's primary key.
966 * actid - The function activity(instance id).
967 * funcmode - Run/Cancel/Timeout
968 * output parameters
969 * Resultout - 'COMPLETE:T' if user is verified
970 * - 'COMPLETE:F' if user is not verified
971 *
972 * Errors :
973 * Other Comments :
974 */
975 procedure enrollment_only (itemtype in varchar2,
976 itemkey in varchar2,
977 actid in number,
978 funcmode in varchar2,
979 resultout out NOCOPY varchar2) is
980 l_result varchar2(10);
981 begin
982 l_result := wf_engine.GetItemAttrText (
983 itemtype => itemtype,
984 itemkey => itemkey,
985 aname => 'ENROLLMENT_ONLY');
986 if l_result = 'Y' then
987 resultout := 'COMPLETE:T';
988 else
989 resultout := 'COMPLETE:F';
990 end if;
991
992 return;
993 exception
994 when others then
995 wf_core.context ('JTF_UM_SEND_PASSWORD_WF', 'enrollment_only');
996 raise;
997
998 end enrollment_only;
999
1000
1001 /*
1002 procedure write_debug_log(debug_msg varchar2) is
1003 PRAGMA AUTONOMOUS_TRANSACTION;
1004 begin
1005 insert into test_temp
1006 values
1007 (debug_msg)
1008 ;
1009 COMMIT;
1010 end write_debug_log;
1011 */
1012
1013 /**
1014 * Procedure : reset_password
1015 * Type : Private
1016 * Pre_reqs :
1017 * Description : this procedure resets the password and sends an email to the user.
1018 * Also, it inserts a user into wf_local_user if a valid username
1019 * and email combination does not
1020 already exist in wf_user.
1021 * Parameters : None
1022 * input parameters
1023 * param requester_user_name
1024 * param requester_email
1025 * (*) required fields
1026 * output parameters
1027 * param x_return_status
1028 * param x_msg_data
1029 * param x_msg_count
1030 * Errors : Expected Errors
1031 * requester_user_name and email is null
1032 * requester_user_name is not a valid user
1033 * requester_email does not correspond to a valid user
1034 * Other Comments :
1035 * FND_USER update : The update of fnd_user table is done using fnd_user_pkg
1036 * procedure as recommended by fnd (bug 1713101)
1037 * DEFAULTING LOGIC
1038 * If only the user name is passed then the email is defaulted using the following logic
1039 * 1. Email address from fnd_users where user_name = p_requester_user_name
1040 * 2. Email from per_all_people_F where person_id = employee_id
1041 * (retrieved from fnd_users using the user_name)
1042 * 3. Email from hz_contact_points where owner_type_id = party_id and
1043 * owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL'.
1044 * Party_id here is obtained from the customer id stored in fnd_user
1045 * where user_name = p_requester_user_name.
1046 * In all the above cases the user, employee, party etc. have to be valid.
1047 *
1048 * If only the email address is specified, the user name is determined using a similar logic
1049 * 1. User_name from fnd_user where email_address = p_requester_email_Address
1050 * 2. User_name from fnd_user where employee_id = person_id (retrieved from per_all_people_f
1051 * using the email_address)
1052 * 3. User_name from fnd_user where customer_id = hz_contact_points.owner_type_id and
1053 * owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL' and
1054 * contact_point = p_requester_email_Address
1055 *
1056 * If both email and user name are passed, the combination is validated using the above logic.
1057 */
1058
1059
1060 procedure reset_password(p_api_version_number in number,
1061 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1062 p_commit in varchar2 := FND_API.G_FALSE,
1063 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1064 p_requester_user_name in varchar2 := null,
1065 p_requester_email in varchar2 := null,
1066 p_application_id in number := null,
1067 p_responsibility_id in number := null,
1068 x_return_status out NOCOPY varchar2,
1069 x_msg_count out NOCOPY number,
1070 x_msg_data out NOCOPY varchar2
1071 ) is
1072
1073 l_api_version_number NUMBER := 1.0;
1074 l_api_name VARCHAR2(50) := 'RESET_PASSWORD';
1075 l_email varchar2(240);
1076 l_requester_user_name varchar2(240) := UPPER(p_requester_user_name);
1077 l_requester_email varchar2(240) := p_requester_email;
1078 l_responsibility_id number := p_responsibility_id;
1079 l_application_id number := p_application_id;
1080 l_wf_user_name varchar2(240);
1081 l_password varchar2(240);
1082 l_result varchar2(10);
1083 v_counter BINARY_INTEGER := 1;
1084
1085 begin
1086
1087 -- Write to debug log
1088 if l_is_debug_parameter_on then
1089 JTF_DEBUG_PUB.log_debug(2, G_MODULE, 'Entering API reset_Password ...');
1090 end if;
1091 --
1092
1093 -- Standard Start of API savepoint
1094 SAVEPOINT reset_password;
1095
1096 -- Standard call to check for call compatibility.
1097 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1098 p_api_version_number,
1099 l_api_name,
1100 G_PKG_NAME)
1101 THEN
1102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1103 END IF;
1104
1105
1106 -- Initialize message list if p_init_msg_list is set to TRUE.
1107 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1108 FND_MSG_PUB.initialize;
1109 END IF;
1110
1111 -- Initialize API return status to success
1112 x_return_status := FND_API.G_RET_STS_SUCCESS;
1113
1114 --
1115 -- API body
1116 --
1117
1118 -- Validate required fields for not null values
1119
1120 if (p_requester_user_name is null and p_requester_email is null) then
1121 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1122 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_USER_OR_EMAIL');
1123 FND_MSG_PUB.ADD;
1124 END IF;
1125 RAISE FND_API.G_EXC_ERROR;
1126 end if;
1127
1128 -- validate / default username/email
1129
1130 --write_debug_log('before calling get_wf_user');
1131
1132 JTF_UM_UTIL_PVT.get_wf_user(p_api_version_number => 1.0,
1133 x_requester_user_name => l_requester_user_name,
1134 x_requester_email => l_requester_email ,
1135 x_wf_user => l_wf_user_name,
1136 x_return_status => x_return_Status,
1137 x_msg_count => x_msg_count,
1138 x_msg_data => x_msg_Data );
1139
1140 if x_return_status = FND_API.G_RET_STS_ERROR then
1141 RAISE FND_API.G_EXC_ERROR;
1142 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1144 end if;
1145
1146 -- if wf_user is NULL then we need to create an adhoc user
1147 -- create adhoc user
1148 if l_wf_user_name is NULL then
1149 l_wf_user_name := 'JTFUM-'|| l_requester_user_name;
1150 JTF_UM_UTIL_PVT.GetAdHocUser (p_api_version_number => 1.0,
1151 p_username => l_wf_user_name,
1152 p_display_name => l_requester_user_name,
1153 p_email_address => l_requester_email,
1154 x_return_status => x_return_status,
1155 x_msg_data => x_msg_data,
1156 x_msg_count => x_msg_count);
1157 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1159 end if;
1160 end if;
1161
1162
1163 -- generate password
1164 generate_password (p_api_version_number => 1.0,
1165 x_password => l_password,
1166 x_return_status => x_return_status,
1167 x_msg_count => x_msg_count,
1168 x_msg_data => x_msg_data
1169 );
1170
1171
1172
1173 -- loop till password clears the validations
1174 l_result := FND_WEB_SEC.validate_password( l_requester_user_name, l_password );
1175 WHILE (( l_result <> 'Y') AND ( v_counter <=100) ) LOOP
1176
1177 -- incrementing the counter
1178 v_counter := v_counter + 1;
1179 -- generate password
1180 generate_password (p_api_version_number => 1.0,
1181 x_password => l_password,
1182 x_return_status => x_return_status,
1183 x_msg_count => x_msg_count,
1184 x_msg_data => x_msg_data
1185 );
1186
1187 l_result := FND_WEB_SEC.validate_password( l_requester_user_name, l_password );
1188 IF ( v_counter = 100 ) THEN
1189 IF ( l_result <> 'Y' ) THEN
1190 -- Throw exception as even though generated password 100 times, but
1191 -- cannot pass validation criteria
1192 raise_application_error (-20000, 'Could not generated password automatically which satisfies validation requirements.');
1193 END IF;
1194 END IF;
1195 END LOOP;
1196 -- end of code for validating username
1197
1198
1199 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1201 end if;
1202
1203 if not fnd_user_pkg.changePassword(username => l_requester_user_name,
1204 newpassword => l_password) then
1205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1206 end if;
1207
1208 -- update the password date to null to force user to change psswd upon
1209 -- first login
1210 fnd_user_pkg.UpdateUser (
1211 x_user_name => l_requester_user_name,
1212 x_owner => 'CUST',
1213 x_unencrypted_password => l_password,
1214 x_password_date => null);
1215
1216
1217
1218 -- update the password date to null. this will force the user to change
1219 -- password upon first logon
1220 -- not needed now as FND API allows setting the password date to null
1221 /*
1222 --begin
1223 update fnd_user set
1224 password_date = null
1225 where user_name = l_requester_user_name;
1226 */
1227 /* exception
1228 when others
1229 raise;
1230 end;*/
1231
1232 --write_debug_log('before call to send_password');
1233 -- initiate the workflow to send the password
1234 send_password(p_api_version_number => 1.0,
1235 p_requester_user_name => l_requester_user_name,
1236 p_requester_password => l_password,
1237 p_responsibility_id => l_responsibility_id,
1238 p_application_id => l_application_id,
1239 p_wf_user_name => l_wf_user_name,
1240 p_first_time_user => 'N',
1241 p_user_verified => 'Y',
1242 x_return_status => x_return_status,
1243 x_msg_count => x_msg_count,
1244 x_msg_data => x_msg_data
1245 );
1246 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1248 end if;
1249 --
1250 -- End of API body
1251 --
1252
1253 -- Standard check for p_commit
1254 IF FND_API.to_Boolean( p_commit )
1255 THEN
1256 COMMIT WORK;
1257 END IF;
1258
1259 -- Standard call to get message count and if count is 1, get message info.
1260
1261 FND_MSG_PUB.Count_And_Get(
1262 p_count => x_msg_count,
1263 p_data => x_msg_data);
1264
1265 -- Write to debug log
1266 if l_is_debug_parameter_on then
1267 JTF_DEBUG_PUB.log_debug(2, G_MODULE, 'Exiting API reset_Password ...');
1268 end if;
1269 --
1270
1271 EXCEPTION
1272 WHEN FND_API.G_EXC_ERROR THEN
1273 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
1274 P_API_NAME => L_API_NAME
1275 ,P_PKG_NAME => G_PKG_NAME
1276 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1277 ,P_SQLCODE => SQLCODE
1278 ,P_SQLERRM => SQLERRM
1279 ,X_MSG_COUNT => X_MSG_COUNT
1280 ,X_MSG_DATA => X_MSG_DATA
1281 ,X_RETURN_STATUS => X_RETURN_STATUS);
1282
1283 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1284 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
1285 P_API_NAME => L_API_NAME
1286 ,P_PKG_NAME => G_PKG_NAME
1287 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1288 ,P_SQLCODE => SQLCODE
1289 ,P_SQLERRM => SQLERRM
1290 ,X_MSG_COUNT => X_MSG_COUNT
1291 ,X_MSG_DATA => X_MSG_DATA
1292 ,X_RETURN_STATUS => X_RETURN_STATUS);
1293
1294 WHEN OTHERS THEN
1295 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
1296 P_API_NAME => L_API_NAME
1297 ,P_PKG_NAME => G_PKG_NAME
1298 ,P_EXCEPTION_LEVEL => JTF_DEBUG_PUB.G_EXC_OTHERS
1299 ,P_SQLCODE => SQLCODE
1300 ,P_SQLERRM => SQLERRM
1301 ,X_MSG_COUNT => X_MSG_COUNT
1302 ,X_MSG_DATA => X_MSG_DATA
1303 ,X_RETURN_STATUS => X_RETURN_STATUS);
1304 end reset_password;
1305
1306 Procedure enrollment_info(document_id in varchar2,
1307 display_type in varchar2,
1308 document in out NOCOPY varchar2,
1309 document_type in out NOCOPY varchar2) is
1310
1311 Cursor c_enrollment(p_subscr_reg_id in number) is
1312 select svl.subscription_name||decode(reg.status_code,'PENDING', '(*)') name
1313 from jtf_um_subscriptions_vl svl, jtf_um_subscription_reg reg
1314 where svl.subscription_id = reg.subscription_id
1315 and reg.subscription_reg_id = p_subscr_reg_id
1316 and status_code in ('APPROVED', 'PENDING')
1317 and NVL(reg.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE;
1318
1319
1320 l_document_id varchar2(4000) := document_id;
1321 pos integer;
1322 type id_table is table of integer index by binary_integer;
1323 id id_table;
1324
1325 enroll_count integer := 0;
1326
1327 begin
1328
1329 -- document id is a list of subscr reg id separated by :
1330 if l_document_id is not NULL then
1331 pos := instr(l_document_id,':',1);
1332 while pos > 0 loop
1333 id(nvl(id.LAST,0) + 1) := to_number(substr(l_document_id, 1, pos-1));
1334 l_document_id := substr(l_document_id, pos+1);
1335 pos := instr(l_document_id,':',1);
1336 end loop;
1337 id(nvl(id.LAST,0) + 1) := l_document_id;
1338
1339
1340 -- document := 'document id is '||document_id ||'display type is ' || display_type|| JTF_DBSTRING_UTILS.getLineFeed;
1341
1342 document := FND_MESSAGE.get_string('JTF', 'JTA_UM_ENROLL_HEADER')||NEWLINE||NEWLINE;
1343
1344
1345 for j in 1..id.count loop
1346 for i in c_enrollment(id(j)) loop
1347 document := document ||i.name ||NEWLINE;
1348 enroll_count := enroll_count + 1;
1349 end loop;
1350 end loop;
1351
1352 document := document||NEWLINE||FND_MESSAGE.get_string('JTF', 'JTA_UM_ENROLL_FOOTER');
1353
1354 -- if there are no approved or pending enrollments then do not have the doc info
1355 if enroll_count = 0 then
1356 document := null;
1357 end if;
1358
1359 document_type := 'text/plain';
1360 end if;
1361 end;
1362
1363
1364 End JTF_UM_PASSWORD_PVT;