[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_PASSWORD_PVT
Source
1 PACKAGE BODY JTF_UM_PASSWORD_PVT as
2 /* $Header: JTFVUMPB.pls 120.3.12020000.2 2012/07/19 07:00:43 anurtrip 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 -- validate end date and email address using user_name
331 Open c_user(l_requester_user_name);
332 Fetch c_user into l_requester_email;
333 If (c_user%NOTFOUND) then
334 Close c_user;
335 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
336 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_INVALID_FIELD');
337 --FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME, FALSE);
338 FND_MESSAGE.Set_Token('API_NAME', 'sending the password', FALSE);
339 FND_MESSAGE.Set_Token('FIELD', 'USER', FALSE);
340 FND_MESSAGE.Set_Token('VALUE', p_requester_user_name, FALSE);
341 FND_MSG_PUB.ADD;
342 END IF;
343 RAISE FND_API.G_EXC_ERROR;
344 end if;
345 Close c_user;
346 -- get email address to which the password needs to be sent
347 if l_wf_user_name is NULL then
348 JTF_UM_UTIL_PVT.get_wf_user(p_api_version_number => 1.0,
349 x_requester_user_name => l_requester_user_name,
350 x_requester_email => l_requester_email ,
351 x_wf_user => l_wf_user_name,
352 x_return_status => x_return_Status,
353 x_msg_count => x_msg_count,
354 x_msg_data => x_msg_Data );
355
356 if x_return_status = FND_API.G_RET_STS_ERROR then
357 RAISE FND_API.G_EXC_ERROR;
358 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
360 end if;
361 -- if wf_user is NULL then we need to create an adhoc user
362 -- create adhoc user
363 if l_wf_user_name is NULL then
364 l_wf_user_name := 'JTFUM-'|| l_requester_user_name;
365 JTF_UM_UTIL_PVT.GetAdHocUser (p_api_version_number => 1.0,
366 p_username => l_wf_user_name,
367 p_display_name => l_requester_user_name,
368 p_email_address => l_requester_email,
369 x_return_status => x_return_status,
370 x_msg_data => x_msg_data,
371 x_msg_count => x_msg_count);
372 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
374 end if;
375 end if;
376
377 end if;
378 --
379 If l_requester_email is NULL then
380 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
381 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_REQUIRED_EMAIL');
382 --FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME, FALSE);
383 FND_MESSAGE.Set_Token('API_NAME', 'sending the password', FALSE);
384 FND_MESSAGE.Set_Token('USER_NAME',p_requester_user_name, FALSE);
385 FND_MSG_PUB.ADD;
386 END IF;
387 RAISE FND_API.G_EXC_ERROR;
388 end if;
389
390
391
392 -- default the responsibility and application id if needed
393 JTF_UM_UTIL_PVT.getDefaultAppRespId(P_USERNAME => p_requester_user_name,
394 P_RESP_ID => l_responsibility_id,
395 P_APPL_ID => l_application_id,
396 X_RESP_ID => l_responsibility_id,
397 X_APPL_ID => l_application_id);
398
399
400 -- setting workflow parameters
401 -- the WF process owner should be the merchant sysadmin
402 processOwner := nvl (JTF_UM_UTIL_PVT.VALUE_SPECIFIC (
403 name => 'JTA_UM_WORKFLOW_OWNER',
404 responsibility_id => l_responsibility_id,
405 resp_appl_id => l_application_id,
406 application_id => l_application_id,
407 site_level => true), 'SYSADMIN');
408
409 itemtype := nvl(JTF_UM_UTIL_PVT.VALUE_SPECIFIC(
410 name => 'JTA_UM_PASSWORD_GEN_WKF',
411 responsibility_id => l_responsibility_id,
412 resp_appl_id => l_application_id,
413 application_id => l_application_id,
414 site_level => true), 'JTAUMPSW');
415
416 Open c_item_key;
417 Fetch c_item_key into itemkey;
418 Close c_item_key;
419
420 --processName := 'SEND_PASSWORD';
421 --
422 -- Start Process
423 --
424 wf_engine.CreateProcess (itemtype,
425 itemkey,
426 processName);
427
428 -- Set Workflow Attributes
429
430 -- set user item key
431 wf_engine.SetItemUserKey (itemtype => itemtype,
432 itemkey => itemkey,
433 UserKey => substr(p_requester_user_name|| ' request for password',1,80));
434
435 -- set user name
436 wf_engine.SetItemAttrText (itemtype => itemtype,
437 itemkey => itemkey,
438 aname => 'REQUESTER_USER_NAME',
439 avalue => l_requester_user_name);
440
441 -- set user name
442 wf_engine.SetItemAttrText (itemtype => itemtype,
443 itemkey => itemkey,
444 aname => 'WF_USER_NAME',
445 avalue => l_wf_user_name);
446
447 -- set password
448 wf_engine.SetItemAttrText (itemtype => itemtype,
449 itemkey => itemkey,
450 aname => 'REQUESTER_PASSWORD',
451 avalue => p_requester_password);
452
453 -- set name
454 wf_engine.SetItemAttrText (itemtype => itemtype,
455 itemkey => itemkey,
456 aname => 'REQUESTER_LAST_NAME',
457 avalue => p_requester_last_name);
458
459 wf_engine.SetItemAttrText (itemtype => itemtype,
460 itemkey => itemkey,
461 aname => 'REQUESTER_FIRST_NAME',
462 avalue => p_requester_first_name);
463 -- set user type id
464 wf_engine.SetItemAttrNumber (itemtype => itemtype,
465 itemkey => itemkey,
466 aname => 'REQUESTER_USERTYPE_ID',
467 avalue => p_usertype_id);
468
469 -- set responsibility id
470 wf_engine.SetItemAttrNumber (itemtype => itemtype,
471 itemkey => itemkey,
472 aname => 'RESPONSIBILITY_ID',
473 avalue => l_responsibility_id);
474
475 -- set application id
476 wf_engine.SetItemAttrNumber (itemtype => itemtype,
477 itemkey => itemkey,
478 aname => 'APPLICATION_ID',
479 avalue => l_application_id);
480 -- set confirmation id
481 wf_engine.SetItemAttrText (itemtype => itemtype,
482 itemkey => itemkey,
483 aname => 'CONFIRMATION_NUMBER',
484 avalue => p_confirmation_number);
485
486 -- set first time user
487 wf_engine.SetItemAttrText (itemtype => itemtype,
488 itemkey => itemkey,
489 aname => 'FIRST_TIME_USER',
490 avalue => p_first_time_user);
491
492 -- set user verified
493 wf_engine.SetItemAttrText (itemtype => itemtype,
494 itemkey => itemkey,
495 aname => 'USER_VERIFIED',
496 avalue => p_user_verified);
497
498 -- set enrollment only
499 wf_engine.SetItemAttrText (itemtype => itemtype,
500 itemkey => itemkey,
501 aname => 'ENROLLMENT_ONLY',
502 avalue => p_enrollment_only);
503
504 -- set enrollment information
505 wf_engine.SetItemAttrText (itemtype => itemtype,
506 itemkey => itemkey,
507 aname => 'ENROLLMENTS',
508 avalue => p_enrollment_list);
509
510 --
511 -- Launch the send password workflow
512 --
513
514 wf_engine.startProcess(itemtype => itemType,
515 itemkey => itemKey);
516
517 --
518 -- End of API body
519 --
520
521 -- Standard check for p_commit
522 IF FND_API.to_Boolean( p_commit )
523 THEN
524 COMMIT WORK;
525 END IF;
526
527 -- Standard call to get message count and if count is 1, get message info.
528
529 FND_MSG_PUB.Count_And_Get(
530 p_count => x_msg_count,
531 p_data => x_msg_data);
532
533 -- Write to debug log
534 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Exiting API send_Password ...');
535 --
536
537 EXCEPTION
538 WHEN FND_API.G_EXC_ERROR THEN
539 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
540 P_API_NAME => L_API_NAME
541 ,P_PKG_NAME => G_PKG_NAME
542 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
543 ,P_SQLCODE => SQLCODE
544 ,P_SQLERRM => SQLERRM
545 ,X_MSG_COUNT => X_MSG_COUNT
546 ,X_MSG_DATA => X_MSG_DATA
547 ,X_RETURN_STATUS => X_RETURN_STATUS);
548
549 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
550 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
551 P_API_NAME => L_API_NAME
552 ,P_PKG_NAME => G_PKG_NAME
553 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
554 ,P_SQLCODE => SQLCODE
555 ,P_SQLERRM => SQLERRM
556 ,X_MSG_COUNT => X_MSG_COUNT
557 ,X_MSG_DATA => X_MSG_DATA
558 ,X_RETURN_STATUS => X_RETURN_STATUS);
559
560 WHEN OTHERS THEN
561 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
562 P_API_NAME => L_API_NAME
563 ,P_PKG_NAME => G_PKG_NAME
564 ,P_EXCEPTION_LEVEL => JTF_DEBUG_PUB.G_EXC_OTHERS
565 ,P_SQLCODE => SQLCODE
566 ,P_SQLERRM => SQLERRM
567 ,X_MSG_COUNT => X_MSG_COUNT
568 ,X_MSG_DATA => X_MSG_DATA
569 ,X_RETURN_STATUS => X_RETURN_STATUS);
570
571
572 end send_password;
573
574
575
576 /**
577 * Procedure : set_parameters
578 * Type : Private
579 * Pre_reqs :
580 * Description : this procedure sets all the parameters needed for the email / notifications.
581 * Parameters :
582 * input parameters (see workflow parameters for description)
583 * param requester_user_name
584 * param requester_password
585 * param requester_name
586 * param usertype_id
587 * param responsibility_id
588 * param application_id
589 * param first_time_user
590 * param send_password
591 * param confirmation_id
592 * output parameters
593 * param x_return_status
594 * param x_msg_data
595 * param x_msg_count
596 * Errors :
597 * Other Comments :
598 */
599 procedure set_parameters (
600 itemtype in varchar2,
601 itemkey in varchar2,
602 p_requester_user_name in varchar2,
603 p_usertype_id in number,
604 p_responsibility_id in number ,
605 p_application_id in number,
606 p_requester_first_name in varchar2,
607 p_requester_last_name in varchar2,
608 x_return_status out NOCOPY varchar2) is
609
610 l_approval_needed varchar2(1) := 'F';
611 l_appl_url varchar2(1000);
612 l_sender varchar2(1000);
613 l_support_contact varchar2(1000);
614 l_merchant_name varchar2(1000);
615 l_application_id number;
616
617 cursor approval_needed(user_type_id in number) is
618 select 'T' from jtf_um_usertypes_b
619 where usertype_id = user_type_id
620 and approval_id is not NULL;
621
622 cursor appl_id(p_resp_id in number) is
623 select application_id from fnd_responsibility
624 where responsibility_id = p_resp_id;
625
626 begin
627 -- Initialize API return status to success
628 x_return_status := FND_API.G_RET_STS_SUCCESS;
629
630 l_application_id := p_application_id;
631
632 -- set approval_required
633 -- check if approval is required using the usertype id. If usertype_id
634 -- is NULL then approval_needed is defaulted to 'F'
635
636 if p_usertype_id is not NULL then
637 open approval_needed(p_usertype_id);
638 fetch approval_needed into l_approval_needed;
639 close approval_needed;
640 end if;
641 --dbms_output.put_line('approval needed '||l_approval_needed);
642 wf_engine.SetItemAttrText (itemtype => itemtype,
643 itemkey => itemkey,
644 aname => 'APPROVAL_REQUIRED',
645 avalue => l_approval_needed);
646
647
648 /* -- get application id from responsibility id if needed / possible
649
650 if l_application_id is NULL
651 and p_responsibility_id is not NULL then
652 open appl_id(p_responsibility_id);
653 fetch appl_id into l_application_id;
654 close appl_id;
655 end if; */
656
657 -- set the URL
658 l_appl_url := JTF_UM_UTIL_PVT.value_specific(name => 'JTA_UM_APPL_URL',
659 responsibility_id => p_responsibility_id,
660 resp_appl_id => p_application_id,
661 application_id => p_application_id,
662 site_level => true);
663
664 wf_engine.SetItemAttrText (itemtype => itemtype,
665 itemkey => itemkey,
666 aname => 'APPL_URL',
667 avalue => l_appl_url);
668
669 -- set support contact
670 l_support_contact := JTF_UM_UTIL_PVT.value_specific(
671 name => 'JTA_UM_SUPPORT_CONTACT',
672 responsibility_id => p_responsibility_id,
673 resp_appl_id => p_application_id,
674 application_id => p_application_id,
675 site_level => true);
676
677 wf_engine.SetItemAttrText (itemtype => itemtype,
678 itemkey => itemkey,
679 aname => 'SUPPORT_CONTACT',
680 avalue => l_support_contact);
681
682 -- set merchant name
683 l_merchant_name := JTF_UM_UTIL_PVT.value_specific(name => 'JTF_UM_MERCHANT_NAME',
684 responsibility_id => p_responsibility_id,
685 resp_appl_id => p_application_id,
686 application_id => l_application_id,
687 site_level => true);
688
689 wf_engine.SetItemAttrText (itemtype => itemtype,
690 itemkey => itemkey,
691 aname => 'MERCHANT_NAME',
692 avalue => l_merchant_name);
693
694 -- set sender
695 l_sender := JTF_UM_UTIL_PVT.value_specific(name => 'JTA_UM_SENDER',
696 responsibility_id => p_responsibility_id,
697 resp_appl_id => p_application_id,
698 application_id => l_application_id,
699 site_level => true);
700
701 --l_sender := nvl(l_sender, l_merchant_name);
702 wf_engine.SetItemAttrText (itemtype => itemtype,
703 itemkey => itemkey,
704 aname => 'SENDER',
705 avalue => l_sender);
706 -- set name
707 if p_requester_first_name is NULL and p_requester_last_name is NULL then
708 wf_engine.SetItemAttrText (itemtype => itemtype,
709 itemkey => itemkey,
710 aname => 'REQUESTER_LAST_NAME',
711 avalue => p_requester_user_name);
712 /*
713 wf_engine.SetItemAttrText (itemtype => itemtype,
714 itemkey => itemkey,
715 aname => 'ENROLLMENTS',
716 avalue => p_requester_user_name);
717 */
718 end if;
719
720 end set_parameters;
721
722
723 /**
724 * Procedure : set_parameters
725 * Type : Private
726 * Pre_reqs :
727 * Description : this procedure sets all the parameters needed for the email / notifications.
728 * Parameters :
729 * input parameters
730 * itemtype - A valid item type from (WF_ITEM_TYPES table).
731 * itemkey - A string generated from the application object's primary key.
732 * actid - The function activity(instance id).
733 * funcmode - Run/Cancel/Timeout
734 * output parameters
735 * Resultout - 'COMPLETE:T' if all parameters are set properly
736 * - 'COMPLETE:F' if parameters could not be set
737 *
738 * Errors :
739 * Other Comments :
740 */
741
742 procedure set_parameters ( itemtype in varchar2,
743 itemkey in varchar2,
744 actid in number,
745 funcmode in varchar2,
746 resultout out NOCOPY varchar2) is
747 l_requester_user_name varchar2(1000);
748 l_usertype_id number;
749 l_responsibility_id number;
750 l_application_id number;
751 l_confirmation_id number;
752 l_requester_first_name varchar2(1000);
753 l_requester_last_name varchar2(1000);
754 x_return_status varchar2(10);
755 begin
756
757 if (funcmode = 'RUN') then
758 --
759 -- RUN mode - normal process execution
760 --
761 -- get user name
762 l_requester_user_name := wf_engine.GetItemAttrText (
763 itemtype => itemtype,
764 itemkey => itemkey,
765 aname => 'REQUESTER_USER_NAME');
766
767 -- get first and last name
768 l_requester_first_name := wf_engine.GetItemAttrText (
769 itemtype => itemtype,
770 itemkey => itemkey,
771 aname => 'REQUESTER_FIRST_NAME');
772
773 l_requester_last_name := wf_engine.GetItemAttrText (
774 itemtype => itemtype,
775 itemkey => itemkey,
776 aname => 'REQUESTER_LAST_NAME');
777
778 -- get user type id
779 l_usertype_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
780 itemkey => itemkey,
781 aname => 'REQUESTER_USERTYPE_ID');
782
783 -- get responsibility id
784 l_responsibility_id := wf_engine.GetItemAttrNumber (
785 itemtype => itemtype,
786 itemkey => itemkey,
787 aname => 'RESPONSIBILITY_ID');
788
789 -- get application id
790 l_application_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
791 itemkey => itemkey,
792 aname => 'APPLICATION_ID');
793
794 -- set values of other parameters
795 set_parameters (
796 itemtype => itemtype,
797 itemkey => itemkey,
798 p_requester_user_name => l_requester_user_name,
799 p_usertype_id => l_usertype_id,
800 p_responsibility_id => l_responsibility_id,
801 p_application_id => l_application_id,
802 p_requester_first_name=> l_requester_first_name,
803 p_requester_last_name => l_requester_last_name,
804 x_return_status => x_return_status);
805
806
807 --
808 -- CANCEL mode
809 --
810 elsif (funcmode = 'CANCEL') then
811
812 resultout := 'COMPLETE:';
813 return;
814
815 end if;
816 exception
817 when others then
818 wf_core.context ('JTF_UM_PASSWORD_PVT', 'set_parameters ');
819 raise;
820
821 end set_parameters;
822
823 /**
824 * Procedure : is_first_time_user
825 * Type : Private
826 * Pre_reqs :
827 * Description : this procedure returns 'T' if the user is a first time user
828 * Parameters :
829 * input parameters
830 * itemtype - A valid item type from (WF_ITEM_TYPES table).
831 * itemkey - A string generated from the application object's primary key.
832 * actid - The function activity(instance id).
833 * funcmode - Run/Cancel/Timeout
834 * output parameters
835 * Resultout - 'COMPLETE:T' if the user is a first time user
836 * - 'COMPLETE:F' if the user is not a first time user
837 *
838 * Errors :
839 * Other Comments :
840 */
841 procedure is_first_time_user (itemtype in varchar2,
842 itemkey in varchar2,
843 actid in number,
844 funcmode in varchar2,
845 resultout out NOCOPY varchar2) is
846 l_result varchar2(10);
847 begin
848 l_result := wf_engine.GetItemAttrText (
849 itemtype => itemtype,
850 itemkey => itemkey,
851 aname => 'FIRST_TIME_USER');
852 if l_result = 'N' then
853 resultout := 'COMPLETE:F';
854 else
855 resultout := 'COMPLETE:T';
856 end if;
857
858 return;
859 exception
860 when others then
861 wf_core.context ('JTF_UM_SEND_PASSWORD_WF', 'is_first_time_user');
862 raise;
863 end;
864
865
866 /**
867 * Procedure : approval_required
868 * Type : Private
869 * Pre_reqs :
870 * Description : this procedure returns whether or not an approval is required
871 * Parameters :
872 * input parameters
873 * itemtype - A valid item type from (WF_ITEM_TYPES table).
874 * itemkey - A string generated from the application object's primary key.
875 * actid - The function activity(instance id).
876 * funcmode - Run/Cancel/Timeout
877 * output parameters
878 * Resultout - 'COMPLETE:T' if approval is required
879 * - 'COMPLETE:F' if approval is not required
880 *
881 * Errors :
882 * Other Comments :
883 */
884 procedure is_approval_required (itemtype in varchar2,
885 itemkey in varchar2,
886 actid in number,
887 funcmode in varchar2,
888 resultout out NOCOPY varchar2) is
889 l_result varchar2(10);
890 l_requester_user_name varchar2(360);
891 l_adhoc_role varchar2(360);
892 l_wf_roles boolean ;
893
894
895 CURSOR c_wf_adhoc_role(l_display_name in varchar2) IS
896 select name
897 from wf_roles
898 where display_name = upper(l_display_name);
899 begin
900
901
902
903
904 l_result := wf_engine.GetItemAttrText (
905 itemtype => itemtype,
906 itemkey => itemkey,
907 aname => 'APPROVAL_REQUIRED');
908
909
910 l_requester_user_name := wf_engine.GetItemAttrText (
911 itemtype => itemtype,
912 itemkey => itemkey,
913 aname => 'REQUESTER_USER_NAME');
914
915
916 --bug 7524239 check to see if the user is active in wf_roles
917 --bug 14083201 using wf_directory.userActive instead of
918 --WF_DIRECTORY.IsPerformer
919 l_wf_roles := wf_directory.UserActive(l_requester_user_name);
920
921 if not l_wf_roles then
922 Open c_wf_adhoc_role(l_requester_user_name);
923 Fetch c_wf_adhoc_role into l_adhoc_role;
924 if (c_wf_adhoc_role%NOTFOUND) then
925 Close c_wf_adhoc_role;
926 else
927 Close c_wf_adhoc_role;
928 end if;
929
930 if l_adhoc_role is not null then
931 if l_result = 'T' then
932 wf_engine.SetItemAttrText (itemtype => itemtype,
933 itemkey => itemkey,
934 aname => 'APPROVAL_REQUIRED_USER_NAME',
935 avalue => l_adhoc_role);
936 elsif l_result = 'F' then
937 wf_engine.SetItemAttrText (itemtype => itemtype,
938 itemkey => itemkey,
939 aname => 'REQUESTER_USER_NAME',
940 avalue => l_adhoc_role);
941 end if;
942 end if;
943 elsif l_result = 'T' then
944 wf_engine.SetItemAttrText (itemtype => itemtype,
945 itemkey => itemkey,
946 aname => 'APPROVAL_REQUIRED_USER_NAME',
947 avalue => l_requester_user_name);
948
949 end if;
950
951 if l_result = 'F' then
952 resultout := 'COMPLETE:F';
953 else
954 resultout := 'COMPLETE:T';
955 end if;
956
957 return;
958 exception
959 when others then
960 wf_core.context ('JTF_UM_SEND_PASSWORD_WF', 'is_approval_required ');
961 raise;
962 end;
963
964
965 /**
966 * Procedure : user_verified
967 * Type : Private
968 * Pre_reqs :
969 * Description : this procedure returns 'T' if a user is verified and a password can be sent to the user
970 * Parameters :
971 * input parameters
972 * itemtype - A valid item type from (WF_ITEM_TYPES table).
973 * itemkey - A string generated from the application object's primary key.
974 * actid - The function activity(instance id).
975 * funcmode - Run/Cancel/Timeout
976 * output parameters
977 * Resultout - 'COMPLETE:T' if user is verified
978 * - 'COMPLETE:F' if user is not verified
979 *
980 * Errors :
981 * Other Comments :
982 */
983 procedure is_user_verified (itemtype in varchar2,
984 itemkey in varchar2,
985 actid in number,
986 funcmode in varchar2,
987 resultout out NOCOPY varchar2) is
988 l_result varchar2(10);
989 begin
990 l_result := wf_engine.GetItemAttrText (
991 itemtype => itemtype,
992 itemkey => itemkey,
993 aname => 'USER_VERIFIED');
994 if l_result = 'Y' then
995 resultout := 'COMPLETE:T';
996 else
997 resultout := 'COMPLETE:F';
998 end if;
999
1000 return;
1001 exception
1002 when others then
1003 wf_core.context ('JTF_UM_SEND_PASSWORD_WF', 'is_user_verified');
1004 raise;
1005 end;
1006
1007
1008 /**
1009 * Procedure : enrollment_only
1010 * Type : Public
1011 * Pre_reqs :
1012 * Description : this procedure returns 'T' if only enrollment information should be sent to the user.
1013 * Parameters :
1014 * input parameters
1015 * itemtype - A valid item type from (WF_ITEM_TYPES table).
1016 * itemkey - A string generated from the application object's primary key.
1017 * actid - The function activity(instance id).
1018 * funcmode - Run/Cancel/Timeout
1019 * output parameters
1020 * Resultout - 'COMPLETE:T' if user is verified
1021 * - 'COMPLETE:F' if user is not verified
1022 *
1023 * Errors :
1024 * Other Comments :
1025 */
1026 procedure enrollment_only (itemtype in varchar2,
1027 itemkey in varchar2,
1028 actid in number,
1029 funcmode in varchar2,
1030 resultout out NOCOPY varchar2) is
1031 l_result varchar2(10);
1032 begin
1033 l_result := wf_engine.GetItemAttrText (
1034 itemtype => itemtype,
1035 itemkey => itemkey,
1036 aname => 'ENROLLMENT_ONLY');
1037 if l_result = 'Y' then
1038 resultout := 'COMPLETE:T';
1039 else
1040 resultout := 'COMPLETE:F';
1041 end if;
1042
1043 return;
1044 exception
1045 when others then
1046 wf_core.context ('JTF_UM_SEND_PASSWORD_WF', 'enrollment_only');
1047 raise;
1048
1049 end enrollment_only;
1050
1051
1052 /*
1053 procedure write_debug_log(debug_msg varchar2) is
1054 PRAGMA AUTONOMOUS_TRANSACTION;
1055 begin
1056 insert into test_temp
1057 values
1058 (debug_msg)
1059 ;
1060 COMMIT;
1061 end write_debug_log;
1062 */
1063
1064 /**
1065 * Procedure : reset_password
1066 * Type : Private
1067 * Pre_reqs :
1068 * Description : this procedure resets the password and sends an email to the user.
1069 * Also, it inserts a user into wf_local_user if a valid username
1070 * and email combination does not
1071 already exist in wf_user.
1072 * Parameters : None
1073 * input parameters
1074 * param requester_user_name
1075 * param requester_email
1076 * (*) required fields
1077 * output parameters
1078 * param x_return_status
1079 * param x_msg_data
1080 * param x_msg_count
1081 * Errors : Expected Errors
1082 * requester_user_name and email is null
1083 * requester_user_name is not a valid user
1084 * requester_email does not correspond to a valid user
1085 * Other Comments :
1086 * FND_USER update : The update of fnd_user table is done using fnd_user_pkg
1087 * procedure as recommended by fnd (bug 1713101)
1088 * DEFAULTING LOGIC
1089 * If only the user name is passed then the email is defaulted using the following logic
1090 * 1. Email address from fnd_users where user_name = p_requester_user_name
1091 * 2. Email from per_all_people_F where person_id = employee_id
1092 * (retrieved from fnd_users using the user_name)
1093 * 3. Email from hz_contact_points where owner_type_id = party_id and
1094 * owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL'.
1095 * Party_id here is obtained from the customer id stored in fnd_user
1096 * where user_name = p_requester_user_name.
1097 * In all the above cases the user, employee, party etc. have to be valid.
1098 *
1099 * If only the email address is specified, the user name is determined using a similar logic
1100 * 1. User_name from fnd_user where email_address = p_requester_email_Address
1101 * 2. User_name from fnd_user where employee_id = person_id (retrieved from per_all_people_f
1102 * using the email_address)
1103 * 3. User_name from fnd_user where customer_id = hz_contact_points.owner_type_id and
1104 * owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL' and
1105 * contact_point = p_requester_email_Address
1106 *
1107 * If both email and user name are passed, the combination is validated using the above logic.
1108 */
1109
1110
1111 procedure reset_password(p_api_version_number in number,
1112 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1113 p_commit in varchar2 := FND_API.G_FALSE,
1114 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1115 p_requester_user_name in varchar2 := null,
1116 p_requester_email in varchar2 := null,
1117 p_application_id in number := null,
1118 p_responsibility_id in number := null,
1119 x_return_status out NOCOPY varchar2,
1120 x_msg_count out NOCOPY number,
1121 x_msg_data out NOCOPY varchar2
1122 ) is
1123
1124 l_api_version_number NUMBER := 1.0;
1125 l_api_name VARCHAR2(50) := 'RESET_PASSWORD';
1126 l_email varchar2(240);
1127 l_requester_user_name varchar2(240) := UPPER(p_requester_user_name);
1128 l_requester_email varchar2(240) := p_requester_email;
1129 l_responsibility_id number := p_responsibility_id;
1130 l_application_id number := p_application_id;
1131 l_wf_user_name varchar2(240);
1132 l_password varchar2(240);
1133 l_result varchar2(10);
1134 v_counter BINARY_INTEGER := 1;
1135
1136 begin
1137
1138 -- Write to debug log
1139 if l_is_debug_parameter_on then
1140 JTF_DEBUG_PUB.log_debug(2, G_MODULE, 'Entering API reset_Password ...');
1141 end if;
1142 --
1143
1144 -- Standard Start of API savepoint
1145 SAVEPOINT reset_password;
1146
1147 -- Standard call to check for call compatibility.
1148 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1149 p_api_version_number,
1150 l_api_name,
1151 G_PKG_NAME)
1152 THEN
1153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1154 END IF;
1155
1156
1157 -- Initialize message list if p_init_msg_list is set to TRUE.
1158 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1159 FND_MSG_PUB.initialize;
1160 END IF;
1161
1162 -- Initialize API return status to success
1163 x_return_status := FND_API.G_RET_STS_SUCCESS;
1164
1165 --
1166 -- API body
1167 --
1168
1169 -- Validate required fields for not null values
1170
1171 if (p_requester_user_name is null and p_requester_email is null) then
1172 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1173 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_USER_OR_EMAIL');
1174 FND_MSG_PUB.ADD;
1175 END IF;
1176 RAISE FND_API.G_EXC_ERROR;
1177 end if;
1178
1179 -- validate / default username/email
1180
1181 --write_debug_log('before calling get_wf_user');
1182
1183 JTF_UM_UTIL_PVT.get_wf_user(p_api_version_number => 1.0,
1184 x_requester_user_name => l_requester_user_name,
1185 x_requester_email => l_requester_email ,
1186 x_wf_user => l_wf_user_name,
1187 x_return_status => x_return_Status,
1188 x_msg_count => x_msg_count,
1189 x_msg_data => x_msg_Data );
1190
1191 if x_return_status = FND_API.G_RET_STS_ERROR then
1192 RAISE FND_API.G_EXC_ERROR;
1193 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1195 end if;
1196
1197 -- if wf_user is NULL then we need to create an adhoc user
1198 -- create adhoc user
1199 if l_wf_user_name is NULL then
1200 l_wf_user_name := 'JTFUM-'|| l_requester_user_name;
1201 JTF_UM_UTIL_PVT.GetAdHocUser (p_api_version_number => 1.0,
1202 p_username => l_wf_user_name,
1203 p_display_name => l_requester_user_name,
1204 p_email_address => l_requester_email,
1205 x_return_status => x_return_status,
1206 x_msg_data => x_msg_data,
1207 x_msg_count => x_msg_count);
1208 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1210 end if;
1211 end if;
1212
1213
1214 -- generate password
1215 generate_password (p_api_version_number => 1.0,
1216 x_password => l_password,
1217 x_return_status => x_return_status,
1218 x_msg_count => x_msg_count,
1219 x_msg_data => x_msg_data
1220 );
1221
1222
1223
1224 -- loop till password clears the validations
1225 l_result := FND_WEB_SEC.validate_password( l_requester_user_name, l_password );
1226 WHILE (( l_result <> 'Y') AND ( v_counter <=100) ) LOOP
1227
1228 -- incrementing the counter
1229 v_counter := v_counter + 1;
1230 -- generate password
1231 generate_password (p_api_version_number => 1.0,
1232 x_password => l_password,
1233 x_return_status => x_return_status,
1234 x_msg_count => x_msg_count,
1235 x_msg_data => x_msg_data
1236 );
1237
1238 l_result := FND_WEB_SEC.validate_password( l_requester_user_name, l_password );
1239 IF ( v_counter = 100 ) THEN
1240 IF ( l_result <> 'Y' ) THEN
1241 -- Throw exception as even though generated password 100 times, but
1242 -- cannot pass validation criteria
1243 raise_application_error (-20000, 'Could not generated password automatically which satisfies validation requirements.');
1244 END IF;
1245 END IF;
1246 END LOOP;
1247 -- end of code for validating username
1248
1249
1250 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1252 end if;
1253
1254 if not fnd_user_pkg.changePassword(username => l_requester_user_name,
1255 newpassword => l_password) then
1256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1257 end if;
1258
1259 -- update the password date to null to force user to change psswd upon
1260 -- first login
1261 fnd_user_pkg.UpdateUser (
1262 x_user_name => l_requester_user_name,
1263 x_owner => 'CUST',
1264 x_unencrypted_password => l_password,
1265 x_password_date => null);
1266
1267
1268
1269 -- update the password date to null. this will force the user to change
1270 -- password upon first logon
1271 -- not needed now as FND API allows setting the password date to null
1272 /*
1273 --begin
1274 update fnd_user set
1275 password_date = null
1276 where user_name = l_requester_user_name;
1277 */
1278 /* exception
1279 when others
1280 raise;
1281 end;*/
1282
1283 --write_debug_log('before call to send_password');
1284 -- initiate the workflow to send the password
1285 send_password(p_api_version_number => 1.0,
1286 p_requester_user_name => l_requester_user_name,
1287 p_requester_password => l_password,
1288 p_responsibility_id => l_responsibility_id,
1289 p_application_id => l_application_id,
1290 p_wf_user_name => l_wf_user_name,
1291 p_first_time_user => 'N',
1292 p_user_verified => 'Y',
1293 x_return_status => x_return_status,
1294 x_msg_count => x_msg_count,
1295 x_msg_data => x_msg_data
1296 );
1297 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1299 end if;
1300 --
1301 -- End of API body
1302 --
1303
1304 -- Standard check for p_commit
1305 IF FND_API.to_Boolean( p_commit )
1306 THEN
1307 COMMIT WORK;
1308 END IF;
1309
1310 -- Standard call to get message count and if count is 1, get message info.
1311
1312 FND_MSG_PUB.Count_And_Get(
1313 p_count => x_msg_count,
1314 p_data => x_msg_data);
1315
1316 -- Write to debug log
1317 if l_is_debug_parameter_on then
1318 JTF_DEBUG_PUB.log_debug(2, G_MODULE, 'Exiting API reset_Password ...');
1319 end if;
1320 --
1321
1322 EXCEPTION
1323 WHEN FND_API.G_EXC_ERROR THEN
1324 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
1325 P_API_NAME => L_API_NAME
1326 ,P_PKG_NAME => G_PKG_NAME
1327 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1328 ,P_SQLCODE => SQLCODE
1329 ,P_SQLERRM => SQLERRM
1330 ,X_MSG_COUNT => X_MSG_COUNT
1331 ,X_MSG_DATA => X_MSG_DATA
1332 ,X_RETURN_STATUS => X_RETURN_STATUS);
1333
1334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1335 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
1336 P_API_NAME => L_API_NAME
1337 ,P_PKG_NAME => G_PKG_NAME
1338 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1339 ,P_SQLCODE => SQLCODE
1340 ,P_SQLERRM => SQLERRM
1341 ,X_MSG_COUNT => X_MSG_COUNT
1342 ,X_MSG_DATA => X_MSG_DATA
1343 ,X_RETURN_STATUS => X_RETURN_STATUS);
1344
1345 WHEN OTHERS THEN
1346 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
1347 P_API_NAME => L_API_NAME
1348 ,P_PKG_NAME => G_PKG_NAME
1349 ,P_EXCEPTION_LEVEL => JTF_DEBUG_PUB.G_EXC_OTHERS
1350 ,P_SQLCODE => SQLCODE
1351 ,P_SQLERRM => SQLERRM
1352 ,X_MSG_COUNT => X_MSG_COUNT
1353 ,X_MSG_DATA => X_MSG_DATA
1354 ,X_RETURN_STATUS => X_RETURN_STATUS);
1355 end reset_password;
1356
1357 Procedure enrollment_info(document_id in varchar2,
1358 display_type in varchar2,
1359 document in out NOCOPY varchar2,
1360 document_type in out NOCOPY varchar2) is
1361
1362 Cursor c_enrollment(p_subscr_reg_id in number) is
1363 select svl.subscription_name||decode(reg.status_code,'PENDING', '(*)') name
1364 from jtf_um_subscriptions_vl svl, jtf_um_subscription_reg reg
1365 where svl.subscription_id = reg.subscription_id
1366 and reg.subscription_reg_id = p_subscr_reg_id
1367 and status_code in ('APPROVED', 'PENDING')
1368 and NVL(reg.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE;
1369
1370
1371 l_document_id varchar2(4000) := document_id;
1372 pos integer;
1373 type id_table is table of integer index by binary_integer;
1374 id id_table;
1375
1376 enroll_count integer := 0;
1377
1378 begin
1379
1380 -- document id is a list of subscr reg id separated by :
1381 if l_document_id is not NULL then
1382 pos := instr(l_document_id,':',1);
1383 while pos > 0 loop
1384 id(nvl(id.LAST,0) + 1) := to_number(substr(l_document_id, 1, pos-1));
1385 l_document_id := substr(l_document_id, pos+1);
1386 pos := instr(l_document_id,':',1);
1387 end loop;
1388 id(nvl(id.LAST,0) + 1) := l_document_id;
1389
1390
1391 -- document := 'document id is '||document_id ||'display type is ' || display_type|| JTF_DBSTRING_UTILS.getLineFeed;
1392
1393 document := FND_MESSAGE.get_string('JTF', 'JTA_UM_ENROLL_HEADER')||NEWLINE||NEWLINE;
1394
1395
1396 for j in 1..id.count loop
1397 for i in c_enrollment(id(j)) loop
1398 document := document ||i.name ||NEWLINE;
1399 enroll_count := enroll_count + 1;
1400 end loop;
1401 end loop;
1402
1403 document := document||NEWLINE||FND_MESSAGE.get_string('JTF', 'JTA_UM_ENROLL_FOOTER');
1404
1405 -- if there are no approved or pending enrollments then do not have the doc info
1406 if enroll_count = 0 then
1407 document := null;
1408 end if;
1409
1410 document_type := 'text/plain';
1411 end if;
1412 end;
1413
1414
1415 End JTF_UM_PASSWORD_PVT;