1 PACKAGE BODY JTF_UM_UTIL_PVT as
2 /* $Header: JTFVUUTB.pls 120.8 2006/02/14 00:16:14 snellepa ship $ */
3
4 MODULE_NAME CONSTANT VARCHAR2(50) := 'JTF.UM.PLSQL.JTF_UM_UTIL_PVT';
5 l_is_debug_parameter_on boolean := JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME);
6
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_UM_UTIL_PVT';
8 G_FILE_NAME CONSTANT VARCHAR2(12) := 'JTFVUUTB.pls';
9
10 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
11 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
12
13 /**
14 * Procedure : validate_Email
15 * Type : Private
16 * Pre_reqs :
17 * Description : this returns true if p_actual_email is the email that is to be used.
18 * Parameters :
19 * input parameters
20 * p_requested_email - the email address at which the user wants to receive email
21 * p_actual_email - the email address at which workflow can send the email
22 * output parameters
23 * true if p_requested_email = p_actual_email or p_requested_email is NULL
24 *
25 * Errors :
26 * Other Comments :
27 */
28 function validate_email(p_requested_email in varchar2,
29 p_actual_email in varchar2) return boolean is
30 begin
31 if p_requested_email is not NULL and p_actual_email is not NULL
32 and upper(p_requested_email) = upper(p_actual_email) then
33 return true;
34 elsif p_requested_email is NULL and p_actual_email is not NULL then
35 return true;
36 else
37 return false;
38 end if;
39 end validate_email;
40
41
42
43
44 /**
45 * Procedure : get_user_name
46 * Type : Private
47 * Pre_reqs :
48 * Description : this procedure returns the user given the email address
49 * Parameters : None
50 * input parameters
51 * param requester_email - email address the requester would like to use.
52 * (*) required fields
53 * output parameters
54 * param x_user
55 * param x_email
56 * param x_wf_user_name
57 * param x_return_status
58 * Errors : Expected Errors
59 * requester_user_name and email is null
60 * requester_user_name is not a valid user
61 * requester_email does not correspond to a valid user
62 * Other Comments :
63 * DEFAULTING LOGIC
64 *
65 * 1. User_name from fnd_user where email_address = p_requester_email_Address
66 * 2. User_name from fnd_user where employee_id = person_id (retrieved from
67 * per_all_people_f using the email_address)
68 * 3. User_name from fnd_user where customer_id = hz_contact_points.owner_type_id
69 * and owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL'
70 * and contact_point = p_requester_email_Address
71 *
72 * In all the above cases the user, employee, party etc. have to be valid.
73 *
74 * The same logic is used to validate the requester_email.
75 */
76 procedure get_user_name(p_requester_email in varchar2,
77 x_user_name out NOCOPY varchar2,
78 x_email out NOCOPY varchar2,
79 x_wf_user_name out NOCOPY varchar2,
80 x_return_Status out NOCOPY varchar2) is
81 cursor c_user is
82
83 -- from fnd tables
84 select user_name
85 from fnd_user
86 where upper(email_address) = p_requester_email
87 and (nvl(end_date, sysdate + 1) > sysdate or
88 to_char(END_DATE) = to_char(FND_API.G_MISS_DATE))
89
90
91 union
92
93 -- from HR tables
94 select fnd.user_name
95 from fnd_user fnd, per_all_people_f per
96 where per.person_id = fnd.employee_id
97 and per.effective_end_date > sysdate
98 and (nvl(fnd.end_date, sysdate+1) > sysdate or
99 to_char(fnd.END_DATE) = to_char(FND_API.G_MISS_DATE))
100 and upper(per.email_address) = p_requester_email
101
102 union
103
104 -- from TCA tables
105 select fnd.user_name
106 from hz_contact_points hcp, fnd_user fnd
107 where hcp.owner_table_id = fnd.customer_id
108 and hcp.owner_table_name = 'HZ_PARTIES'
109 and hcp.contact_point_type = 'EMAIL'
110 and upper(hcp.email_address) = p_requester_email;
111
112
113 cursor c_wf_user(l_user_name in varchar2, l_email in varchar2) is
114 select name
115 from wf_users
116 where name = l_user_name
117 and upper(email_address) = l_email;
118
119 -- local variables
120
121 l_party_id pls_integer;
122 l_party_type varchar2(200);
123 l_wf_user_name varchar2(200);
124 user_count pls_integer := 0; -- keeps track of number of users with same email
125 begin
126
127 -- initialize return parameters
128 x_email := p_requester_email;
129 x_return_status := FND_API.G_RET_STS_SUCCESS;
130
131 if p_requester_email is not NULL then
132 for i in C_user loop
133 x_user_name := i.user_name;
134 user_count := user_count + 1;
135 end loop;
136 end if;
137
138 l_wf_user_name := x_user_name;
139
140 if x_user_name is NULL then
141 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
142 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_NO_USER');
143 FND_MESSAGE.Set_TOKEN('0', p_requester_email, FALSE);
144 FND_MSG_PUB.ADD;
145 END IF;
146 RAISE FND_API.G_EXC_ERROR;
147 end if;
148
149 if user_count > 1 then
150 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
151 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_MULTIPLE_USER');
152 --FND_MESSAGE.Set_TOKEN('0', p_requester_email, FALSE);
153 FND_MSG_PUB.ADD;
154 END IF;
155 RAISE FND_API.G_EXC_ERROR;
156 end if;
157
158 -- check if this user is a valid user in wf_user
159 if x_user_name is not NULL and l_wf_user_name is not NULL then
160 open c_wf_user(l_wf_user_name, p_requester_email);
161 fetch c_wf_user into x_wf_user_name;
162 close c_wf_user;
163 end if;
164
165 end get_user_name;
166
167
168
169 /**
170 * Procedure : get_email
171 * Type : Private
172 * Pre_reqs :
173 * Description : this procedure returns the email of the user given the user
174 * name. If a email address is passed as an input parameter it
175 * checks to see if the email address is a valid one.
176 * Parameters : None
177 * input parameters
178 * param requester_user_name (*) - user name of the requester
179 * param requester_email - email address the requester would like to use.
180 * (*) required fields
181 * output parameters
182 * param x_email
183 * param x_wf_user_name
184 * param x_return_status
185 * Errors : Expected Errors
186 * requester_user_name and email is null
187 * requester_user_name is not a valid user
188 * requester_email does not correspond to a valid user
189 * Other Comments :
190 * DEFAULTING LOGIC
191 * If only the user name is passed then the email is defaulted using the following logic
192 * 1. Email address from fnd_users where user_name = p_requester_user_name
193 * 2. Email from per_all_people_F where person_id = employee_id
194 * (retrieved from fnd_users using the user_name)
195 * 3. Email from hz_contact_points where owner_type_id = party_id and
196 * owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL'.
197 * Party_id here is obtained from the customer id stored in fnd_user where
198 * user_name = p_requester_user_name.
199 * In all the above cases the user, employee, party etc. have to be valid.
200 *
201 * The same logic is used to validate the requester_email.
202 */
203 procedure get_email(p_requester_user_name in varchar2,
204 p_requester_email in varchar2 := null,
205 x_user out NOCOPY varchar2,
206 x_email out NOCOPY varchar2,
207 x_wf_user_name out NOCOPY varchar2,
208 x_return_Status out NOCOPY varchar2) is
209
210 cursor c_user(l_user_name in varchar2) is
211 select email_address, customer_id, employee_id
212 from fnd_user
213 where user_name = l_user_name
214 and (nvl(end_date, sysdate + 1) > sysdate or
215 to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
216
217 cursor c_employee(p_employee_id in number) is
218 select email_address
219 from per_all_people_f
220 where person_id = p_employee_id
221 and effective_end_date > sysdate;
222 /*
223 cursor c_customer(p_customer_id in number) is
224 select hzp.party_type
225 from hz_parties hzp
226 where hzp.party_id = p_customer_id;
227
228
229 cursor c_subject(p_customer_id in number) is
230 select subject_id
231 from hz_party_relationships
232 where party_id = p_customer_id
233 and party_relationship_type = 'EMPLOYEE_OF'
234 and nvl(end_date, sysdate+1) > sysdate;
235 */
236 cursor c_contact_point(p_party_id in number) is
237 select EMAIL_ADDRESS
238 from hz_contact_points
239 where owner_table_id = p_party_id
240 and owner_table_name = 'HZ_PARTIES'
241 and contact_point_type = 'EMAIL';
242
243 cursor c_wf_user(l_user_name in varchar2, l_email in varchar2) is
244 select name
245 from wf_users
246 where name = l_user_name
247 and email_address = l_email;
248
249 -- local variables
250 l_employee_id pls_integer;
251 l_customer_id pls_integer;
252 l_party_id pls_integer;
253 l_party_type varchar2(200);
254 l_wf_user_name varchar2(200);
255
256 begin
257
258 -- initialize return parameters
259 x_user := p_requester_user_name;
260 -- wf_user_name is different only if we find the email address in hz
261 l_wf_user_name := p_requester_user_name;
262 x_return_status := FND_API.G_RET_STS_SUCCESS;
263
264 if p_requester_user_name is not NULL then
265 -- check to see if email available in fnd_user
266 open c_user(p_requester_user_name);
267 fetch c_user into x_email, l_customer_id, l_employee_id;
268 if (c_user%NOTFOUND) then
269 close c_user;
270 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
271 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_INVALID_USER');
272 FND_MESSAGE.Set_TOKEN('0', p_requester_user_name, FALSE);
273 FND_MSG_PUB.ADD;
274 END IF;
275 x_user := NULL;
276 RAISE FND_API.G_EXC_ERROR;
277 end if;
278 close c_user;
279
280 -- validate the input email.
281 if not validate_email(p_requester_email, x_email) then
282 x_email := null;
283 end if;
284
285 -- if email is still null check if it can be found in per_all_people_F
286 if x_email is null and l_employee_id is not NULL then
287 open c_employee(l_employee_id);
288 fetch c_employee into x_email;
289 close c_employee;
290
291 -- validate the input email.
292 if not validate_email(p_requester_email, x_email) then
293 x_email := null;
294 end if;
295 end if;
296
297 -- if email is still null check if the email is available in TCA
298 if x_email is null and l_customer_id is not NULL then
299 /* open c_customer(l_customer_id);
300 fetch c_customer into l_party_type;
301 close c_customer;
302
303 if l_party_type = 'PERSON' or l_party_type = 'ORGANIZATION' then
304 l_party_id := l_customer_id;
305 elsif l_party_type = 'PARTY_RELATIONSHIP' then
306 open c_subject(l_customer_id);
307 fetch c_subject into l_party_id;
308 close c_subject;
309 end if;
310 */
311
312 open c_contact_point(l_customer_id);
313 fetch c_contact_point into x_email;
314 close c_contact_point;
315
316 -- validate the input email.
317 if not validate_email(p_requester_email, x_email) then
318 x_email := null;
319 end if;
320
321 -- set the wf_user_name
322 l_wf_user_name := 'HZ_PARTY:'||l_party_id;
323 end if;
324
325 -- if x_email is null raise an error
326 if x_email is NULL then
327 if p_requester_email is NULL then
328 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
329 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_NO_EMAIL');
330 --FND_MESSAGE.Set_TOKEN('USER', x_user, FALSE);
331 FND_MESSAGE.Set_TOKEN('0', x_user, FALSE);
332 FND_MSG_PUB.ADD;
333 END IF;
334 else
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_USER_EMAIL');
337 FND_MESSAGE.Set_TOKEN('0', x_user, FALSE);
338 FND_MESSAGE.Set_TOKEN('1', p_requester_email, FALSE);
339 --FND_MESSAGE.Set_TOKEN('USER', x_user, FALSE);
340 --FND_MESSAGE.Set_TOKEN('EMAIL', p_requester_email, FALSE);
341 FND_MSG_PUB.ADD;
342 END IF;
343 end if;
344 RAISE FND_API.G_EXC_ERROR;
345 end if;
346
347 -- check to see if wf_user has this user name
348 -- the reason for this check - a valid user, email combination may still
349 -- not exist in wf_user and if not checked the email will be sent to the
350 -- wrong email address.
351
352 if x_email is not NULL and l_wf_user_name is not NULL then
353 open c_wf_user(l_wf_user_name, x_email);
354 fetch c_wf_user into x_wf_user_name;
355 close c_wf_user;
356 end if;
357 end if;
358 end get_email;
359
360 /**
361 * Procedure : get_user_name
362 * Type : Private
363 * Pre_reqs :
364 * Description : this procedure returns the user given the email address
365 * Parameters : None
366 * input parameters
367 * param requester_email - email address the requester would like to use.
368 * (*) required fields
369 * output parameters
370 * param x_user
371 * param x_email
372 * param x_wf_user_name
373 * param x_return_status
374 * Errors : Expected Errors
375 * requester_user_name and email is null
376 * requester_user_name is not a valid user
377 * requester_email does not correspond to a valid user
378 * Other Comments :
379 * DEFAULTING LOGIC
380 *
381 * 1. User_name from fnd_user where email_address = p_requester_email_Address
382 * 2. User_name from fnd_user where employee_id = person_id (retrieved from
383 * per_all_people_f using the email_address)
384 * 3. User_name from fnd_user where customer_id = hz_contact_points.owner_type_id
385 * and owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL'
386 * and contact_point = p_requester_email_Address
387 *
388 * In all the above cases the user, employee, party etc. have to be valid.
389 *
390 * The same logic is used to validate the requester_email.
391 */
392 /* -- new get_user_name validates if there are multiple users with the same email address
393
394
395 procedure get_user_name(p_requester_email in varchar2,
396 x_user_name out NOCOPY varchar2,
397 x_email out NOCOPY varchar2,
398 x_wf_user_name out NOCOPY varchar2,
399 x_return_Status out NOCOPY varchar2) is
400 cursor c_user is
401 select user_name
402 from fnd_user
403 where upper(email_address) = p_requester_email
404 and (nvl(end_date, sysdate + 1) > sysdate or
405 to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
406
407 cursor c_employee is
408 select fnd.user_name
409 from fnd_user fnd, per_all_people_f per
410 where per.person_id = fnd.employee_id
411 and per.effective_end_date > sysdate
412 and (nvl(fnd.end_date, sysdate+1) > sysdate or
413 to_char(fnd.END_DATE) = to_char(FND_API.G_MISS_DATE))
414 and upper(per.email_address) = p_requester_email;
415
416 cursor c_party is
417 select fnd.user_name
418 from hz_contact_points hcp, fnd_user fnd
419 where hcp.owner_table_id = fnd.customer_id
420 and hcp.owner_table_name = 'HZ_PARTIES'
421 and hcp.contact_point_type = 'EMAIL'
422 and upper(hcp.email_address) = p_requester_email;
423
424 cursor c_user1(l_party_id in number) is
425 select user_name
426 from fnd_user
427 where customer_id = l_party_id
428 and (nvl(end_date, sysdate+1) > sysdate or
429 to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
430
431 cursor c_wf_user(l_user_name in varchar2, l_email in varchar2) is
432 select name
433 from wf_users
434 where name = l_user_name
435 and upper(email_address) = l_email;
436
437 -- local variables
438
439 l_party_id pls_integer;
440 l_party_type varchar2(200);
441 l_wf_user_name varchar2(200);
442 begin
443
444 -- initialize return parameters
445 x_email := p_requester_email;
446 x_return_status := FND_API.G_RET_STS_SUCCESS;
447
448 if p_requester_email is not NULL then
449
450 -- get the user name from fnd_user
451 open c_user;
452 fetch c_user into x_user_name;
453 close c_user;
454
455 if x_user_name is null then
456 -- try to get the user by checking if there is an employee with the
457 -- given email address
458 open c_employee;
459 fetch c_employee into x_user_name;
460 close c_employee;
461 end if;
462
463 l_wf_user_name := x_user_name;
464
465 if x_user_name is null then
466 -- try to get the name from the email in hz_parties
467 open c_party;
468 fetch c_party into x_user_name;
469 close c_party;
470
471 l_wf_user_name := 'HZ_PARTY:'||l_party_id;
472
473 end if;
474
475 if x_user_name is NULL then
476 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
477 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_NO_USER');
478 FND_MESSAGE.Set_TOKEN('0', p_requester_email, FALSE);
479 FND_MSG_PUB.ADD;
480 END IF;
481 RAISE FND_API.G_EXC_ERROR;
482 end if;
483
484 -- check if this user is a valid user in wf_user
485 if x_user_name is not NULL and l_wf_user_name is not NULL then
486 open c_wf_user(l_wf_user_name, p_requester_email);
487 fetch c_wf_user into x_wf_user_name;
488 close c_wf_user;
489 end if;
490 end if;
491 end get_user_name;
492 */
493
494
495 /**
496 * Procedure : get_wf_user
497 * Type : Private
498 * Pre_reqs :
499 * Description : this procedure returns the user name, email and wf_user_name of a given user.
500 * If a email address is passed as an input parameter it
501 * checks to see if the email address is a valid one.
502 * If x_user or x_email is NULL then a valid email /user could
503 * not be found
504 * If x_wf_user is NULL, and x_user and x_email are not NULL then
505 * the user /email combination is valid but does not have a valid
506 * user in wf_user.
507 * Parameters : None
508 * input parameters
509 * param x_requester_user_name (*) - user name of the requester
510 * param x_requester_email - email address the requester would like to use.
511 * (*) required fields
512 * output parameters
513 * param x_requester_user_name
514 * param x_requester_email
515 * param x_wf_user_name
516 * param x_return_status
517 * Errors : Expected Errors
518 * requester_user_name and email is null
519 * requester_user_name is not a valid user
520 * requester_email does not correspond to a valid user
521 * Other Comments :
522 * DEFAULTING LOGIC
523 * If only the user name is passed then the email is defaulted using the following logic
524 * 1. Email address from fnd_users where user_name = x_requester_user_name
525 * 2. Email from per_all_people_F where person_id = employee_id
526 * (retrieved from fnd_users using the user_name)
527 * 3. Email from hz_contact_points where owner_type_id = party_id
528 * and owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL'.
529 * Party_id here is obtained from the customer id stored in fnd_user where
530 * user_name = x_requester_user_name.
531 * In all the above cases the user, employee, party etc. have to be valid.
532 *
533 * If only the email address is specified, the user name is determined using a similar logic
534 * 1. User_name from fnd_user where email_address = x_requester_email_Address
535 * 2. User_name from fnd_user where employee_id = person_id (retrieved from
536 * per_all_people_f using the email_address)
537 * 3. User_name from fnd_user where customer_id = hz_contact_points.owner_type_id
538 * and owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL'
539 * and contact_point = x_requester_email_Address
540 *
541 */
542 procedure get_wf_user(p_api_version_number in number,
543 p_init_msg_list in varchar2 := FND_API.G_FALSE,
544 p_commit in varchar2 := FND_API.G_FALSE,
545 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
546 x_requester_user_name in out NOCOPY varchar2,
547 x_requester_email in out NOCOPY varchar2,
548 x_wf_user out NOCOPY varchar2,
549 x_return_status out NOCOPY varchar2,
550 x_msg_count out NOCOPY number,
551 x_msg_data out NOCOPY varchar2
552 ) is
553
554 l_api_version_number NUMBER := 1.0;
555 l_api_name VARCHAR2(50) := 'GET_WF_USER';
556 l_email varchar2(240);
557 l_requester_user_name varchar2(240) := upper(x_requester_user_name);
558 l_requester_email varchar2(240) := upper(x_requester_email);
559 l_wf_user_name varchar2(240);
560 l_password varchar2(240);
561
562 /*
563 cursor c_update_email(l_user_name in varchar2) is
564 select email_address
565 from fnd_user
566 where user_name = l_user_name;
567 */
568
569 begin
570
571 -- Write to debug log
572 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Entering API send_Password ...');
573 --
574
575 -- Standard Start of API savepoint
576 SAVEPOINT get_wf_user;
577
578 -- Standard call to check for call compatibility.
579 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
580 p_api_version_number,
581 l_api_name,
582 G_PKG_NAME)
583 THEN
584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
585 END IF;
586
587
588 -- Initialize message list if p_init_msg_list is set to TRUE.
589 IF FND_API.to_Boolean( p_init_msg_list ) THEN
590 FND_MSG_PUB.initialize;
591 END IF;
592
593 -- Initialize API return status to success
594 x_return_status := FND_API.G_RET_STS_SUCCESS;
595
596 --
597 -- API body
598 --
599
600 -- Validate required fields for not null values
601
602 if (x_requester_user_name is null and x_requester_email is null) then
603 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
604 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_REQUIRED_FIELD');
605 --FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME, FALSE);
606 FND_MESSAGE.Set_Token('API_NAME', 'resetting the password', FALSE);
607 FND_MESSAGE.Set_Token('FIELD', 'USER_NAME, EMAIL', FALSE);
608 FND_MSG_PUB.ADD;
609 END IF;
610 RAISE FND_API.G_EXC_ERROR;
611 end if;
612
613
614 -- default the email address if user name is not NULL
615 -- the email should be picked up from FND_USER, PER_PEOPLE_F or TCA
616
617 if (l_requester_user_name is not null) then
618 get_email(p_requester_user_name => l_requester_user_name,
619 p_requester_email => l_requester_email,
620 x_user => x_requester_user_name,
621 x_email => x_requester_email,
622 x_wf_user_name => x_wf_user,
623 x_return_Status => x_return_status);
624 end if;
625 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
626 RAISE FND_API.G_EXC_ERROR;
627 end if;
628
629 -- default the user if email is not NULL
630
631 if (l_requester_user_name is NULL and l_requester_email is not null) then
632 get_user_name(p_requester_email => l_requester_email,
633 x_user_name => x_requester_user_name,
634 x_email => x_requester_email,
635 x_wf_user_name => x_wf_user,
636 x_return_Status => x_return_status);
637 end if;
638 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
639 RAISE FND_API.G_EXC_ERROR;
640 end if;
641
642 --
643 -- End of API body
644 --
645
646 -- Standard check for p_commit
647 IF FND_API.to_Boolean( p_commit )
648 THEN
649 COMMIT WORK;
650 END IF;
651
652 -- Standard call to get message count and if count is 1, get message info.
653
654 FND_MSG_PUB.Count_And_Get(
655 p_count => x_msg_count,
656 p_data => x_msg_data);
657
658 -- Write to debug log
659 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Exiting API send_Password ...');
660 --
661
662 EXCEPTION
663 WHEN FND_API.G_EXC_ERROR THEN
664 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
665 P_API_NAME => L_API_NAME
666 ,P_PKG_NAME => G_PKG_NAME
667 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
668 ,P_SQLCODE => SQLCODE
669 ,P_SQLERRM => SQLERRM
670 ,X_MSG_COUNT => X_MSG_COUNT
671 ,X_MSG_DATA => X_MSG_DATA
672 ,X_RETURN_STATUS => X_RETURN_STATUS);
673
674 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
675 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
676 P_API_NAME => L_API_NAME
677 ,P_PKG_NAME => G_PKG_NAME
678 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
679 ,P_SQLCODE => SQLCODE
680 ,P_SQLERRM => SQLERRM
681 ,X_MSG_COUNT => X_MSG_COUNT
682 ,X_MSG_DATA => X_MSG_DATA
683 ,X_RETURN_STATUS => X_RETURN_STATUS);
684
685 WHEN OTHERS THEN
686 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
687 P_API_NAME => L_API_NAME
688 ,P_PKG_NAME => G_PKG_NAME
689 ,P_EXCEPTION_LEVEL => JTF_DEBUG_PUB.G_EXC_OTHERS
690 ,P_SQLCODE => SQLCODE
691 ,P_SQLERRM => SQLERRM
692 ,X_MSG_COUNT => X_MSG_COUNT
693 ,X_MSG_DATA => X_MSG_DATA
694 ,X_RETURN_STATUS => X_RETURN_STATUS);
695 end get_wf_user;
696
697 /**
698 * Procedure : GetAdHocUser
699 * Type : Private
700 * Pre_reqs : WF_DIRECTORY.CreateAdHocUser and
701 * WF_DIRECTORY.SetAdHocUserAttr
702 * Description : This API tries to create an adhoc user with the provided
703 * username. If the username is already being used in the
704 * database, just update input attributes.
705 * Parameters :
706 * input parameters
707 * @param
708 * p_username
709 * description: The adhoc username.
710 * required : Y
711 * p_display_name
712 * description: The adhoc display name.
713 * required : N
714 * default : null
715 * p_language
716 * description: The value of the database NLS_LANGUAGE initialization
717 * parameter that specifies the default language-dependent
718 * behavior of the user's notification session. If null,
719 * the procedure resolves this to the language setting of
720 * your current session.
721 * required : N
722 * default : null
723 * p_territory
724 * description: The value of the database NLS_TERRITORY initialization
725 * parameter that specifies the default territory-dependant
726 * date and numeric formatting used in the user's
727 * notification session. If null, the procedure resolves
728 * this to the territory setting of your current session.
729 * required : N
730 * default : null
731 * p_description
732 * description: Description for the user.
733 * required : N
734 * default : null
735 * p_notification_preference
736 * description: Indicate how this user prefers to receive notifications:
737 * 'MAILTEXT', 'MAILHTML', 'MAILATTH', 'QUERY' or 'SUMMARY'.
738 * If null, the procedure sets the notification preference
739 * to 'MAILHTML'.
740 * required : N
741 * default : 'MAILTEXT'
742 * p_email_address
743 * description: Electronic mail address for this user.
744 * required : Y
745 * p_fax
746 * description: Fax number for the user
747 * required : N
748 * default : null
749 * p_status
750 * description: The availability of the user to participate in a
751 * workflow process. The possible statuses are 'ACTIVE',
752 * 'EXTLEAVE', 'INACTIVE', and 'TMPLEAVE'. If null, the
753 * procedure sets the status to 'ACTIVE'.
754 * required : N
755 * default : 'ACTIVE'
756 * p_expiration_date
757 * description: The date at which the user is no longer valid in the
758 * directory service. If null, the procedure defaults the
759 * expiration date to sysdate.
760 * required : N
761 * default : sysdate
762 * output parameters
763 * @return
764 * Errors :
765 * Other Comments :
766 */
767 PROCEDURE GetAdHocUser (p_api_version_number in number,
768 p_init_msg_list in varchar2 default FND_API.G_FALSE,
769 p_commit in varchar2 default FND_API.G_FALSE,
770 p_validation_level in number default FND_API.G_VALID_LEVEL_FULL,
771 p_username in varchar2,
772 p_display_name in varchar2 default null,
773 p_language in varchar2 default null,
774 p_territory in varchar2 default null,
775 p_description in varchar2 default null,
776 p_notification_preference in varchar2 default 'MAILTEXT',
777 p_email_address in varchar2,
778 p_fax in varchar2 default null,
779 p_status in varchar2 default 'ACTIVE',
780 p_expiration_date in date default sysdate,
781 x_return_status out NOCOPY varchar2,
782 x_msg_data out NOCOPY varchar2,
783 x_msg_count out NOCOPY varchar2) is
784
785 l_api_version_number NUMBER := 1.0;
786 l_api_name VARCHAR2 (50) := 'GetAdHocUser';
787 l_username VARCHAR2 (100) := p_username;
788 l_display_name VARCHAR2 (100);
789 duplicated_user EXCEPTION;
790 PRAGMA EXCEPTION_INIT (duplicated_user, -20002);
791
792 BEGIN
793 -- Write to debug log
794 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Entering API send_Password ...');
795
796 -- Standard call to check for call compatibility.
797 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
798 p_api_version_number,
799 l_api_name,
800 G_PKG_NAME)
801 THEN
802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
803 END IF;
804
805 -- call the user hook if it is a public api. Private APIs do not require a user hook call.
806
807 -- Standard Start of API savepoint
808 SAVEPOINT GetAdHocUser;
809
810 -- Initialize message list if p_init_msg_list is set to TRUE.
811 IF FND_API.to_Boolean (p_init_msg_list) THEN
812 FND_MSG_PUB.initialize;
813 END IF;
814
815 -- Initialize API return status to success
816 x_return_status := FND_API.G_RET_STS_SUCCESS;
817
818 --
819 -- Beginning of API body
820 --
821 -- Validate required fields
822 IF (p_username is null) THEN
823 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
824 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_EMAIL_MISS_USERNAME');
825 FND_MESSAGE.Set_Token('0', 'GetAdHocUser', FALSE);
826 FND_MSG_PUB.ADD;
827 END IF;
828 RAISE FND_API.G_EXC_ERROR;
829 END IF;
830
831 IF (p_email_address is null) THEN
832 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
833 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_EMAIL_MISS_EMAIL');
834 FND_MESSAGE.Set_Token('0', 'GetAdHocUser', FALSE);
835 FND_MSG_PUB.ADD;
836 END IF;
837 RAISE FND_API.G_EXC_ERROR;
838 END IF;
839
840 IF (p_display_name is null) THEN
841 l_display_name := p_username;
842 ELSE
843 l_display_name := p_display_name;
844 END IF;
845
846 BEGIN
847 WF_DIRECTORY.CreateAdHocUser (l_username,
848 l_display_name,
849 p_language,
850 p_territory,
851 p_description,
852 p_notification_preference,
853 p_email_address,
854 p_fax,
855 p_status,
856 p_expiration_date);
857 EXCEPTION
858 WHEN duplicated_user THEN
859 WF_DIRECTORY.SetAdHocUserAttr (p_username,
860 p_display_name,
861 p_notification_preference,
862 p_language,
863 p_territory,
864 p_email_address,
865 p_fax);
866 END;
867
868 --
869 -- End of API body
870 --
871
872 -- Standard check for p_commit
873 IF FND_API.to_Boolean (p_commit) THEN
874 COMMIT WORK;
875 END IF;
876
877 -- Standard call to get message count and if count is 1, get message info.
878 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
879 p_data => x_msg_data);
880
881 -- call the user hook if it is a public api. Private APIs do not require a user hook call.
882
883 -- Write to debug log
884 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Exiting API send_Password ...');
885
886 EXCEPTION
887 WHEN FND_API.G_EXC_ERROR THEN
888 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS (P_API_NAME => L_API_NAME,
889 P_PKG_NAME => G_PKG_NAME,
890 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
891 P_SQLCODE => SQLCODE,
892 P_SQLERRM => SQLERRM,
893 X_MSG_COUNT => X_MSG_COUNT,
894 X_MSG_DATA => X_MSG_DATA,
895 X_RETURN_STATUS => X_RETURN_STATUS);
896
897 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
898 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS (P_API_NAME => L_API_NAME,
899 P_PKG_NAME => G_PKG_NAME,
900 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
901 P_SQLCODE => SQLCODE,
902 P_SQLERRM => SQLERRM,
903 X_MSG_COUNT => X_MSG_COUNT,
904 X_MSG_DATA => X_MSG_DATA,
905 X_RETURN_STATUS => X_RETURN_STATUS);
906
907 WHEN OTHERS THEN
908 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS (P_API_NAME => L_API_NAME,
909 P_PKG_NAME => G_PKG_NAME,
910 P_EXCEPTION_LEVEL => JTF_DEBUG_PUB.G_EXC_OTHERS,
911 P_SQLCODE => SQLCODE,
912 P_SQLERRM => SQLERRM,
913 X_MSG_COUNT => X_MSG_COUNT,
914 X_MSG_DATA => X_MSG_DATA,
915 X_RETURN_STATUS => X_RETURN_STATUS);
916
917 END GetAdHocUser;
918
919 /**
920 * Procedure : LAUNCH_WORKFLOW
921 * Type : Private
922 * Pre_reqs : WF_ENGINE.CREATEPROCESS, WF_ENGINE.SETITEMATTRTEXT, and
923 * WF_ENGINE.STARTPROCESS.
924 * Description : Create and Start workflow process
925 * Parameters :
926 * input parameters
927 * @param
928 * p_username
929 * description: FND user's username. The recipient of the notification.
930 * required : N
931 * validation : Must be a valid FND User.
932 * default : null
933 * p_subject
934 * description: The subject of the notification.
935 * required : Y
936 * p_text_body
937 * description: Text version of the notification body.
938 * required : Y
939 * p_HTML_body
940 * description: HTML version of the notification body.
941 * required : N
942 * default : null
943 * output parameters
944 * @return
945 * Errors : possible errors raised by this API
946 * Other Comments :
947 */
948 PROCEDURE LAUNCH_WORKFLOW (p_username in varchar2,
949 p_subject in varchar2,
950 p_text_body in varchar2,
951 p_HTML_body in varchar2 default null) is
952
953 cursor get_next_itemkey is
954 select JTF_UM_EMAIL_NOTIFICATION_S.NEXTVAL
955 from dual;
956
957 l_itemtype VARCHAR2 (8) := 'JTAUMEMN';
958 l_itemkey NUMBER;
959
960 BEGIN
961
962 OPEN get_next_itemkey;
963 FETCH get_next_itemkey INTO l_itemkey;
964 CLOSE get_next_itemkey;
965
966 -- Call the Workflow API to send the notification.
967 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
968 itemkey => l_itemkey,
969 process => 'SEND_EMAIL_NOTIFICATION',
970 owner_role => FND_GLOBAL.USER_NAME);
971
972 -- Set Workflow Item Attributes.
973 WF_ENGINE.SETITEMATTRTEXT (l_itemtype, l_itemkey, 'RECIPIENT_USERNAME', p_username);
974 WF_ENGINE.SETITEMATTRTEXT (l_itemtype, l_itemkey, 'SUBJECT', p_subject);
975 WF_ENGINE.SETITEMATTRTEXT (l_itemtype, l_itemkey, 'TEXT_BODY', p_text_body);
976
977 IF (p_HTML_body is null) THEN
978 -- The Notification Preference is HTML but p_HTML_body is null, we need
979 -- to add <pre> and </pre> into the text body. This way, it will
980 -- preserve the format of the text mail in the browser when reading.
981 WF_ENGINE.SETITEMATTRTEXT (l_itemtype, l_itemkey, 'HTML_BODY', '<pre>' || p_text_body || '</pre>');
982 ELSE
983 WF_ENGINE.SETITEMATTRTEXT (l_itemtype, l_itemkey, 'HTML_BODY', p_HTML_body);
984 END IF;
985
986 WF_ENGINE.STARTPROCESS (l_itemtype, l_itemkey);
987 END LAUNCH_WORKFLOW;
988
989 /**
990 * Procedure : EMAIL_NOTIFICATION
991 * Type : Private
992 * Pre_reqs : WF_NOTIFICATION.Send, WF_ENGINE.SetItemAttrText
993 * Description : Send email notification to user with a username or/and
994 * email provided as input parameters.
995 * Parameters :
996 * input parameters
997 * @param
998 * p_username
999 * description: FND user's username. The recipient of the notification.
1000 * required : N
1001 * validation : Must be a valid FND User.
1002 * default : null
1003 * p_email_address
1004 * description: Send to this email.
1005 * required : N
1006 * default : null
1007 * p_subject
1008 * description: The subject of the notification.
1009 * required : Y
1010 * p_text_body
1011 * description: Text version of the notification body.
1012 * required : Y
1013 * p_HTML_body
1014 * description: HTML version of the notification body.
1015 *l required : N
1016 * default : null
1017 * output parameters
1018 * @return
1019 * Errors : possible errors raised by this API
1020 * Other Comments :
1021 */
1022 PROCEDURE EMAIL_NOTIFICATION (p_api_version_number in number,
1023 p_init_msg_list in varchar2 default FND_API.G_FALSE,
1024 p_commit in varchar2 default FND_API.G_FALSE,
1025 p_validation_level in number default FND_API.G_VALID_LEVEL_FULL,
1026 p_username in varchar2 default null,
1027 p_email_address in varchar2 default null,
1028 p_subject in varchar2,
1029 p_text_body in varchar2,
1030 p_HTML_body in varchar2 default null,
1031 x_return_status out NOCOPY varchar2,
1032 x_msg_data out NOCOPY varchar2,
1033 x_msg_count out NOCOPY varchar2) is
1034
1035 l_api_version_number NUMBER := 1.0;
1036 l_api_name VARCHAR2 (50) := 'EMAIL_NOTIFICATION';
1037 l_username VARCHAR2 (100) := p_username;
1038 l_adhoc_username VARCHAR2 (100) := 'JTFUM-';
1039 l_wf_username VARCHAR2 (360);
1040 l_email_address VARCHAR2 (2000) := p_email_address;
1041 l_error_msg VARCHAR2 (20);
1042
1043 BEGIN
1044 -- Write to debug log
1045 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Entering API send_Password ...');
1046
1047 -- Standard call to check for call compatibility.
1048 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1049 p_api_version_number,
1050 l_api_name,
1051 G_PKG_NAME)
1052 THEN
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1054 END IF;
1055
1056 -- call the user hook if it is a public api. Private APIs do not require a user hook call.
1057
1058 -- Standard Start of API savepoint
1059 SAVEPOINT EMAIL_NOTIFICATION;
1060
1061 -- Initialize message list if p_init_msg_list is set to TRUE.
1062 IF FND_API.to_Boolean (p_init_msg_list) THEN
1063 FND_MSG_PUB.initialize;
1064 END IF;
1065
1066 -- Initialize API return status to success
1067 x_return_status := FND_API.G_RET_STS_SUCCESS;
1068
1069 --
1070 -- Beginning of API body
1071 --
1072 -- Validate required fields for not null values
1073
1074 -- Both username and email_address cannot be null.
1075 IF (p_username is null) AND (p_email_address is null) THEN
1076 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1077 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_EMAIL_MISS_USER_EMAIL');
1078 FND_MESSAGE.Set_Token('0', 'EmailNotification', FALSE);
1079 FND_MSG_PUB.ADD;
1080 END IF;
1081 RAISE FND_API.G_EXC_ERROR;
1082 END IF;
1083
1084 IF (p_subject is null) OR (p_text_body is null) THEN
1085 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1086 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_REQUIRED_FIELD');
1087 FND_MESSAGE.Set_Token('API_NAME', 'UM Send Notification', FALSE);
1088 IF (p_subject is null) THEN
1089 l_error_msg := 'p_subject';
1090 END IF;
1091 IF (p_text_body is null) THEN
1092 IF (p_subject is null) THEN
1093 l_error_msg := l_error_msg || ', ';
1094 END IF;
1095 l_error_msg := l_error_msg || 'p_text_body';
1096 END IF;
1097 FND_MESSAGE.Set_Token('FIELD', l_error_msg, FALSE);
1098 FND_MSG_PUB.ADD;
1099 END IF;
1100 RAISE FND_API.G_EXC_ERROR;
1101 END IF;
1102
1103 -- Call GET_WF_USER to get the username, email, and username in the
1104 -- wf_user table.
1105 GET_WF_USER (p_api_version_number => 1.0,
1106 x_requester_user_name => l_username,
1107 x_requester_email => l_email_address,
1108 x_wf_user => l_wf_username,
1109 x_return_status => x_return_status,
1110 x_msg_count => x_msg_count,
1111 x_msg_data => x_msg_data);
1112
1113 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1114 RAISE FND_API.G_EXC_ERROR;
1115 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1117 END IF;
1118
1119 IF (l_wf_username is null) THEN
1120 -- We need to send out workflow twice. One to the l_username and
1121 -- the other to the adhoc user. So that the user would receive both
1122 -- email and notification.
1123
1124 -- First, we need to create an adhoc user.
1125 -- Just take the first 94 characters then in case the username is too
1126 -- long to add the 'JTFUM-' prefix.
1127 l_adhoc_username := l_adhoc_username || SUBSTR (l_username, 1, 94);
1128
1129 GetAdHocUser (p_api_version_number => 1.0,
1130 p_username => l_adhoc_username,
1131 p_email_address => l_email_address,
1132 x_return_status => x_return_status,
1133 x_msg_data => x_msg_data,
1134 x_msg_count => x_msg_count);
1135
1136 LAUNCH_WORKFLOW (l_adhoc_username, p_subject, p_text_body, p_HTML_body);
1137
1138 END IF;
1139
1140 LAUNCH_WORKFLOW (l_username, p_subject, p_text_body, p_HTML_body);
1141
1142 --
1143 -- End of API body
1144 --
1145
1146 -- Standard check for p_commit
1147 IF FND_API.to_Boolean (p_commit) THEN
1148 COMMIT WORK;
1149 END IF;
1150
1151 -- Standard call to get message count and if count is 1, get message info.
1152 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1153 p_data => x_msg_data);
1154
1155 -- call the user hook if it is a public api. Private APIs do not require a user hook call.
1156
1157 -- Write to debug log
1158 -- JTF_DEBUG_PUB.add_debug_msg('EVENT', G_MODULE, 'Exiting API send_Password ...');
1159
1160 EXCEPTION
1161 WHEN FND_API.G_EXC_ERROR THEN
1162 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS (P_API_NAME => L_API_NAME,
1163 P_PKG_NAME => G_PKG_NAME,
1164 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
1165 P_SQLCODE => SQLCODE,
1166 P_SQLERRM => SQLERRM,
1167 X_MSG_COUNT => X_MSG_COUNT,
1168 X_MSG_DATA => X_MSG_DATA,
1169 X_RETURN_STATUS => X_RETURN_STATUS);
1170
1171 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1172 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS (P_API_NAME => L_API_NAME,
1173 P_PKG_NAME => G_PKG_NAME,
1174 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1175 P_SQLCODE => SQLCODE,
1176 P_SQLERRM => SQLERRM,
1177 X_MSG_COUNT => X_MSG_COUNT,
1178 X_MSG_DATA => X_MSG_DATA,
1179 X_RETURN_STATUS => X_RETURN_STATUS);
1180
1181 WHEN OTHERS THEN
1182 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS (P_API_NAME => L_API_NAME,
1183 P_PKG_NAME => G_PKG_NAME,
1184 P_EXCEPTION_LEVEL => JTF_DEBUG_PUB.G_EXC_OTHERS,
1185 P_SQLCODE => SQLCODE,
1186 P_SQLERRM => SQLERRM,
1187 X_MSG_COUNT => X_MSG_COUNT,
1188 X_MSG_DATA => X_MSG_DATA,
1189 X_RETURN_STATUS => X_RETURN_STATUS);
1190
1191 end EMAIL_NOTIFICATION;
1192
1193
1194
1195 /*
1196 ** GET_SPECIFIC - Get a profile value for a specific user/resp/appl.
1197 ** Does not go up the hierarchy to retrieve the profile
1198 ** values if input values are null.
1199 */
1200 procedure GET_SPECIFIC(name_z in varchar2,
1201 user_id_z in number default null,
1202 responsibility_id_z in number default null,
1203 resp_appl_id_z in number default null,
1204 application_id_z in number default null,
1205 site_id_z in boolean default false,
1206 val_z out NOCOPY varchar2,
1207 defined_z out NOCOPY boolean) is
1208 pid number;
1209 aid number;
1210 l_name_z varchar2(240) := UPPER(name_z);
1211
1212 --
1213 -- this cursor fetches profile information that will
1214 -- allow subsequent fetches to be more efficient
1215 --
1216 cursor profile_info is
1217 select profile_option_id,
1218 application_id
1219 from fnd_profile_options
1220 where profile_option_name = l_name_z
1221 and start_date_active <= sysdate
1222 and nvl(end_date_active, sysdate) >= sysdate;
1223
1224 --
1225 -- this cursor fetches profile option values for site, application,
1226 -- and user levels (10001/10002/10004)
1227 --
1228 cursor value_uas(pid number, aid number, lid number, lval number) is
1229 select profile_option_value
1230 from fnd_profile_option_values
1231 where profile_option_id = pid
1232 and application_id = aid
1233 and level_id = lid
1234 and level_value = lval;
1235
1236 --
1237 -- this cursor fetches profile option values at the responsibility
1238 -- level (10003)
1239 --
1240 cursor value_resp(pid number, aid number, lval number, laid number) is
1241 select profile_option_value
1242 from fnd_profile_option_values
1243 where profile_option_id = pid
1244 and application_id = aid
1245 and level_id = 10003
1246 and level_value = lval
1247 and level_value_application_id = laid
1248 ;
1249
1250 begin
1251 val_z := NULL;
1252 defined_z := FALSE;
1253
1254 open profile_info;
1255 fetch profile_info into pid, aid;
1256 if (profile_info%NOTFOUND) then
1257 return;
1258 end if;
1259 close profile_info;
1260
1261 -- USER level --
1262 if user_id_z is not NULL then
1263 for c1 in value_uas(pid, aid, 10004, user_id_z) loop
1264 defined_z := TRUE;
1265 val_z := c1.profile_option_value;
1266 return;
1267 end loop;
1268 end if;
1269
1270 -- RESPONSIBILITY level --
1271 if responsibility_id_z is not NULL then
1272 for c1 in value_resp(pid, aid,
1273 responsibility_id_z,
1274 resp_appl_id_z) loop
1275 defined_z := TRUE;
1276 val_z := c1.profile_option_value;
1277 return;
1278 end loop;
1279 end if;
1280
1281 -- APPLICATION level --
1282 if application_id_z is not NULL then
1283 for c1 in value_uas(pid, aid, 10002,
1284 application_id_z) loop
1285 defined_z := TRUE;
1286 val_z := c1.profile_option_value;
1287 return;
1288 end loop;
1289 end if;
1290
1291 -- SITE level --
1292 if site_id_z then
1293 for c1 in value_uas(pid, aid, 10001, 0) loop
1294 defined_z := TRUE;
1295 val_z := c1.profile_option_value;
1296 return;
1297 end loop;
1298 end if;
1299 end GET_SPECIFIC;
1300
1301
1302
1303 /*
1304 ** VALUE_SPECIFIC - Get profile value for a specific user/resp/appl combo
1305 **
1306 */
1307 function VALUE_SPECIFIC(NAME in varchar2,
1308 USER_ID in number default null,
1309 RESPONSIBILITY_ID in number default null,
1310 RESP_APPL_ID in number default null,
1311 APPLICATION_ID in number default null,
1312 SITE_LEVEL in boolean default false)
1313 return varchar2 is
1314 RETVALUE varchar2(255);
1315 DEFINED boolean;
1316 begin
1317 GET_SPECIFIC(NAME, USER_ID, RESPONSIBILITY_ID, RESP_APPL_ID, APPLICATION_ID,
1318 SITE_LEVEL, RETVALUE, DEFINED);
1319 if (DEFINED) then
1320 return (RETVALUE);
1321 else
1322 return(NULL);
1323 end if;
1324 end VALUE_SPECIFIC;
1325
1326
1327 /**
1328 * This procedure gets the default appl and resp id using the following logic
1329 *
1330 * If appl id and resp id are null - get the user value of the profiles
1331 * JTF_PROFILE_DEFAULT_RESPONSIBILITY, JTF_PROFILE_DEFAULT_APPLICATION. These
1332 * values can be set to 'Pending appr' if user requires approval.
1333 * In this case we use the resp of the usertype the user has registered to.
1334 * these values could still be null if the user was registered from fnd.
1335 *
1336 */
1337
1338 procedure getDefaultAppRespId (P_USERNAME IN VARCHAR2,
1339 P_RESP_ID IN NUMBER := null,
1340 P_APPL_ID IN NUMBER := null,
1341 X_RESP_ID out NOCOPY NUMBER,
1342 X_APPL_ID out NOCOPY NUMBER) is
1343 l_user_id number;
1344
1345 -- determine the application from the responsibility
1346 cursor C_appl_id(p_resp_id in number) is
1347 select application_id from fnd_responsibility
1348 where responsibility_id = p_resp_id;
1349
1350 -- determine the userid given the username
1351 cursor C_user_id(p_username in varchar2) is
1352 select user_id from fnd_user
1353 where user_name = p_username;
1354
1355 -- determine whether or not the resp id corresponds to "jtf_pending_approval"
1356 cursor C_is_pending_resp(p_resp_id in number, p_appl_id in number) is
1357 select responsibility_id from fnd_responsibility
1358 where application_id = 690
1359 and responsibility_key = 'JTF_PENDING_APPROVAL';
1360
1361 -- select default responsibility for the usertype
1362 cursor c_default_resp(p_user_id in number) is
1363 select fnd.responsibility_id, fnd.application_id
1364 from jtf_um_usertype_reg reg,
1365 jtf_um_usertype_resp resp,
1366 fnd_responsibility fnd
1367 where reg.user_id = p_user_id
1368 and reg.usertype_id = resp.usertype_id
1369 and resp.responsibility_key = fnd.responsibility_key
1370 and resp.application_id = fnd.application_id;
1371
1372 -- using hardcoded value
1373 l_pending_appr_resp_id number := null;
1374
1375 begin
1376 -- initialize return parameters
1377 x_resp_id := p_resp_id;
1378 x_appl_id := p_appl_id;
1379
1380 if P_RESP_ID is NULL and P_APPL_ID is NULL then
1381
1382 if p_username is not NULL then
1383 open C_user_id(p_username);
1384 fetch C_user_id into l_user_id;
1385 close C_user_id;
1386 end if;
1387
1388 if l_user_id is not NULL then
1389 x_resp_id := value_specific(name=>'JTF_PROFILE_DEFAULT_RESPONSIBILITY',
1390 user_id => l_user_id);
1391
1392 x_appl_id := value_specific(name => 'JTF_PROFILE_DEFAULT_APPLICATION',
1393 user_id => l_user_id);
1394
1395 -- if user is created from fnd or if the responsibility is pending
1396 -- approval then we check for default responsibility of the usertype
1397
1398 if (x_resp_id is not null and x_appl_id is not null) then
1399 open C_is_pending_resp (x_resp_id, x_appl_id);
1400 fetch C_is_pending_resp into l_pending_appr_resp_id;
1401 close C_is_pending_resp;
1402 end if;
1403
1404 if x_resp_id is NULL or x_resp_id = l_pending_appr_resp_id then
1405 open C_default_Resp(l_user_id);
1406 fetch C_default_resp into x_resp_id, x_appl_id;
1407 close C_default_Resp;
1408 end if;
1409 end if;
1410
1411 elsif p_resp_id is not null and p_appl_id is NULL then
1412
1413 open C_appl_id(p_resp_id);
1414 fetch C_appl_id into x_appl_id;
1415 close C_appl_id;
1416
1417 end if;
1418
1419 end getDefaultAppRespId;
1420
1421 /*
1422 * Name : VALIDATE_USER_ID
1423 * Pre_reqs : None
1424 * Description : Will validate the user_id
1425 * Parameters :
1426 * input parameters
1427 * @param
1428 * p_user_id:
1429 * description: The user_id of a user
1430 * required : Y
1431 *
1432 * output parameters
1433 * None
1434 *
1435 * Notes:
1436 *
1437 * This is a package private helper function.
1438 */
1439
1440 function VALIDATE_USER_ID(p_user_id number) return boolean is
1441
1442 cursor find_user_id is select user_id from fnd_user where
1443 user_id = p_user_id and (nvl(end_date, sysdate+1) > sysdate or
1444 to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
1445
1446 l_procedure_name CONSTANT varchar2(30) := 'VALIDATE_USER_ID';
1447 l_dummy_value number;
1448
1449 begin
1450 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1451 p_message => l_procedure_name
1452 );
1453
1454 if l_is_debug_parameter_on then
1455 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1456 p_message => 'p_user_id:' || p_user_id
1457 );
1458 end if;
1459
1460 open find_user_id;
1461 fetch find_user_id into l_dummy_value;
1462
1463 if find_user_id%NOTFOUND then
1464 close find_user_id;
1465 return false;
1466 else
1467 close find_user_id;
1468 return true;
1469
1470 end if;
1471
1472 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1473 p_message => l_procedure_name
1474 );
1475
1476
1477 end VALIDATE_USER_ID;
1478
1479
1480 /*
1481 * Name : VALIDATE_SUBSCRIPTION_ID
1482 * Pre_reqs : None
1483 * Description : Will validate the subscription_id
1484 * Parameters :
1485 * input parameters
1486 * @param
1487 * p_subscription_id:
1488 * description: The subscription_id of the subscription
1489 * required : Y
1490 *
1491 * output parameters
1492 * None
1493 *
1494 * Notes:
1495 *
1496 * This is a package private helper function.
1497 */
1498
1499 function VALIDATE_SUBSCRIPTION_ID(p_subscription_id number) return boolean is
1500
1501 l_procedure_name CONSTANT varchar2(30) := 'VALIDATE_SUBSCRIPTION_ID';
1502 cursor find_subscription_id is select subscription_id from jtf_um_subscriptions_b where
1503 subscription_id = p_subscription_id and nvl(effective_end_date, sysdate+1) > sysdate;
1504
1505 l_dummy_value number;
1506
1507 begin
1508
1509 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1510 p_message => l_procedure_name
1511 );
1512
1513 if l_is_debug_parameter_on then
1514 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1515 p_message => 'p_subscription_id:' || p_subscription_id
1516 );
1517 end if;
1518
1519
1520 open find_subscription_id;
1521 fetch find_subscription_id into l_dummy_value;
1522
1523 if find_subscription_id%NOTFOUND then
1524 close find_subscription_id;
1525 return false;
1526 else
1527 close find_subscription_id;
1528 return true;
1529
1530 end if;
1531 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1532 p_message => l_procedure_name
1533 );
1534
1535
1536 end VALIDATE_SUBSCRIPTION_ID;
1537
1538 /*
1539 * Name : check_role
1540 * Pre_reqs : None
1541 * Description : Will determine if a user has a specific role or not
1542 * Parameters :
1543 * input parameters
1544 * @param p_user_id
1545 * description: The user_id of a user
1546 * required : Y
1547 * validation : Must be a valid user_id
1548 * @param p_auth_principal_id
1549 * description: The jtf_auth_principal_id of a role
1550 * required : Y
1551 * validation : Must be a valid jtf_auth_principal_id
1552 *
1553 * Note:
1554 *
1555 * This API will raise an exception if a user name or a jtf_auth_principal_id
1556 * is invalid
1557 */
1558
1559 function check_role(
1560 p_user_id in number,
1561 p_auth_principal_id in number
1562 ) return boolean IS
1563
1564 l_procedure_name CONSTANT varchar2(30) := 'check_role';
1565 CURSOR VALIDATE_ROLE IS SELECT JTF_AUTH_PRINCIPAL_ID FROM JTF_AUTH_PRINCIPALS_B
1566 WHERE JTF_AUTH_PRINCIPAL_ID = p_auth_principal_id AND IS_USER_FLAG = 0;
1567
1568 CURSOR CHECK_ROLE_ASSIGNMENT IS SELECT JTF_AUTH_PRINCIPAL_MAPPING_ID FROM JTF_AUTH_PRINCIPAL_MAPS
1569 WHERE JTF_AUTH_PARENT_PRINCIPAL_ID = p_auth_principal_id
1570 AND JTF_AUTH_PRINCIPAL_ID IN
1571 (SELECT JTF_AUTH_PRINCIPAL_ID FROM JTF_AUTH_PRINCIPALS_B ROLE, FND_USER FU
1572 WHERE FU.USER_NAME = ROLE.PRINCIPAL_NAME AND FU.USER_ID = p_user_id);
1573
1574 l_role_id number;
1575 l_dummy number;
1576
1577 BEGIN
1578
1579 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1580 p_message => l_procedure_name
1581 );
1582
1583 if l_is_debug_parameter_on then
1584 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1585 p_message => 'p_user_id:' || p_user_id || '+' || 'p_auth_principal_id:' || p_auth_principal_id
1586 );
1587 end if;
1588
1589
1590 IF NOT VALIDATE_USER_ID(p_user_id) THEN
1591 JTF_DEBUG_PUB.LOG_EXCEPTION( p_module => MODULE_NAME,
1592 p_message => JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('user_id')
1593 );
1594
1595 RAISE_APPLICATION_ERROR(-20000, JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('user_id'));
1596 END IF;
1597
1598 OPEN VALIDATE_ROLE;
1599 FETCH VALIDATE_ROLE INTO l_role_id;
1600
1601 IF VALIDATE_ROLE%NOTFOUND THEN
1602 CLOSE VALIDATE_ROLE;
1603 JTF_DEBUG_PUB.LOG_EXCEPTION( p_module => MODULE_NAME,
1604 p_message => JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('role_id')
1605 );
1606
1607 RAISE_APPLICATION_ERROR(-20000, JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('role_id'));
1608 END IF;
1609
1610 OPEN CHECK_ROLE_ASSIGNMENT;
1611 FETCH CHECK_ROLE_ASSIGNMENT INTO l_dummy;
1612
1613 IF CHECK_ROLE_ASSIGNMENT%FOUND THEN
1614
1615 CLOSE CHECK_ROLE_ASSIGNMENT;
1616 RETURN TRUE;
1617
1618 ELSE
1619
1620 CLOSE CHECK_ROLE_ASSIGNMENT;
1621 RETURN FALSE;
1622
1623 END IF;
1624
1625 END check_role;
1626
1627 /*
1628 * Name : check_role
1629 * Pre_reqs : None
1630 * Description : Will determine if a user has a specific role or not
1631 * Parameters :
1632 * input parameters
1633 * @param p_user_id
1634 * description: The user_id of a user
1635 * required : Y
1636 * validation : Must be a valid user_id
1637 * @param p_principal_name
1638 * description: The principal_name of a role
1639 * required : Y
1640 * validation : Must be a valid principal_name
1641 *
1642 * Note:
1643 *
1644 * This API will raise an exception if a user name or a principal_name
1645 * is invalid
1646 */
1647
1648 function check_role(
1649 p_user_id in number,
1650 p_principal_name in varchar2
1651 ) return boolean IS
1652
1653 l_procedure_name CONSTANT varchar2(30) := 'check_role';
1654 CURSOR VALIDATE_ROLE_NAME IS SELECT JTF_AUTH_PRINCIPAL_ID FROM JTF_AUTH_PRINCIPALS_B
1655 WHERE PRINCIPAL_NAME = p_principal_name AND IS_USER_FLAG = 0;
1656
1657 l_role_id number;
1658
1659 BEGIN
1660 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1661 p_message => l_procedure_name
1662 );
1663
1664 if l_is_debug_parameter_on then
1665 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1666 p_message => 'p_user_id:' || p_user_id || '+' || 'p_principal_name:' || p_principal_name
1667 );
1668 end if;
1669
1670
1671 OPEN VALIDATE_ROLE_NAME;
1672 FETCH VALIDATE_ROLE_NAME INTO l_role_id;
1673
1674 IF VALIDATE_ROLE_NAME%NOTFOUND THEN
1675 CLOSE VALIDATE_ROLE_NAME;
1676 JTF_DEBUG_PUB.LOG_EXCEPTION( p_module => MODULE_NAME,
1677 p_message => JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('role_name')
1678 );
1679
1680 RAISE_APPLICATION_ERROR(-20000, JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('role_name'));
1681 END IF;
1682
1683 return check_role(
1684 p_user_id => p_user_id,
1685 p_auth_principal_id => l_role_id
1686 );
1687
1688 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1689 p_message => l_procedure_name
1690 );
1691
1692 END check_role;
1693
1694
1695 /**
1696 * Procedure : grant_roles
1697 * Type : Private
1698 * Pre_reqs : None
1699 * Description : Will grant roles to users
1700 * Parameters :
1701 * input parameters
1702 * p_user_name:
1703 * description: The user_name of the user
1704 * required : Y
1705 * validation : Must be a valid user_name
1706 * p_role_id
1707 * description: The value of the JTF_AUTH_PRINCIPAL_ID
1708 * required : Y
1709 * validation : Must exist as a JTF_AUTH_PRONCIPAL_ID
1710 * in the table JTF_AUTH_PRINCIPALS_B
1711 * p_source_name
1712 * description: The value of the name of the source
1713 * required : Y
1714 * validation : Must be "USERTYPE" or "ENROLLMENT"
1715 * p_source_id
1716 * description: The value of the id associated with the source
1717 * required : Y
1718 * validation : Must be a usertype_id or a subscription_id
1719 * output parameters
1720 * None
1721 */
1722 procedure grant_roles (
1723 p_user_name in varchar2,
1724 p_role_id in number,
1725 p_source_name in varchar2,
1726 p_source_id in varchar2
1727 ) IS
1728
1729 l_procedure_name CONSTANT varchar2(30) := 'grant_roles';
1730 CURSOR FIND_ROLE_NAME IS SELECT PRINCIPAL_NAME FROM JTF_AUTH_PRINCIPALS_B
1731 WHERE JTF_AUTH_PRINCIPAL_ID = p_role_id;
1732 l_role_name JTF_AUTH_PRINCIPALS_B.PRINCIPAL_NAME%TYPE;
1733
1734 BEGIN
1735 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1736 p_message => l_procedure_name
1737 );
1738
1739 if l_is_debug_parameter_on then
1740 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1741 p_message => 'p_user_name:' || p_user_name || '+' || 'p_role_id:' || p_role_id || '+' || 'p_source_name:' || p_source_name || '+' || 'p_source_id:' || p_source_id
1742 );
1743 end if;
1744
1745
1746 OPEN FIND_ROLE_NAME;
1747 FETCH FIND_ROLE_NAME INTO l_role_name;
1748 CLOSE FIND_ROLE_NAME;
1749
1750 IF l_role_name IS NOT NULL THEN
1751
1752 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
1753 ( USER_NAME => p_user_name,
1754 ROLE_NAME => l_role_name,
1755 OWNERTABLE_NAME => p_source_name,
1756 OWNERTABLE_KEY => p_source_id
1757 );
1758
1759 END IF;
1760
1761 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1762 p_message => l_procedure_name
1763 );
1764
1765 END grant_roles;
1766
1767
1768 /*
1769 * Name : GET_USER_ID
1770 * Pre_reqs : None
1771 * Description : Will get user id from username
1772 * Parameters :
1773 * input parameters
1774 * @param
1775 * p_user_name:
1776 * description: The user_name of a user
1777 * required : Y
1778 *
1779 * output parameters
1780 * None
1781 *
1782 * Notes:
1783 * This function will return null, if it can not find username
1784 *
1785 */
1786
1787 function GET_USER_ID(p_user_name varchar2) return NUMBER IS
1788
1789 l_procedure_name CONSTANT varchar2(30) := 'GET_USER_ID';
1790
1791 CURSOR GET_ID IS SELECT USER_ID FROM FND_USER WHERE USER_NAME = p_user_name;
1792 l_user_id NUMBER;
1793
1794 BEGIN
1795
1796 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1797 p_message => l_procedure_name
1798 );
1799
1800 if l_is_debug_parameter_on then
1801 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1802 p_message => 'p_user_name:' || p_user_name
1803 );
1804 end if;
1805
1806 OPEN GET_ID;
1807 FETCH GET_ID INTO l_user_id;
1808 CLOSE GET_ID;
1809
1810 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1811 p_message => l_procedure_name
1812 );
1813 return l_user_id;
1814
1815 END GET_USER_ID;
1816
1817
1818 /*
1819 * Name : GET_USERTYPE_ID
1820 * Pre_reqs : None
1821 * Description : Will get user type id for a user
1822 * Parameters :
1823 * input parameters
1824 * @param
1825 * p_user_id:
1826 * description: The user_id of a user
1827 * required : Y
1828 *
1829 * output parameters
1830 * None
1831 *
1832 * Notes:
1833 * This function will return null, if it can not find username
1834 *
1835 */
1836
1837 function GET_USERTYPE_ID(p_user_id NUMBER) return NUMBER IS
1838
1839 l_procedure_name CONSTANT varchar2(30) := 'GET_USERTYPE_ID';
1840
1841 CURSOR GET_ID IS SELECT USERTYPE_ID FROM JTF_UM_USERTYPE_REG
1842 WHERE USER_ID = p_user_id
1843 AND NVL(EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
1844 AND EFFECTIVE_START_DATE < SYSDATE;
1845
1846 l_usertype_id NUMBER;
1847
1848 BEGIN
1849
1850 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1851 p_message => l_procedure_name
1852 );
1853
1854 if l_is_debug_parameter_on then
1855 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1856 p_message => 'p_user_id:' || p_user_id
1857 );
1858 end if;
1859
1860 OPEN GET_ID;
1861 FETCH GET_ID INTO l_usertype_id;
1862 CLOSE GET_ID;
1863
1864 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1865 p_message => l_procedure_name
1866 );
1867 return l_usertype_id;
1868
1869 END GET_USERTYPE_ID;
1870
1871 function CHECK_PARTY_TYPE(p_party_id NUMBER) return VARCHAR2
1872 is
1873 l_party_type HZ_PARTIES.party_type%type;
1874
1875 CURSOR c_party_type
1876 is
1877 SELECT hzp.party_type
1878 FROM hz_parties hzp
1879 WHERE hzp.party_id = p_party_id;
1880 BEGIN
1881
1882 OPEN c_party_type;
1883 FETCH c_party_type INTO l_party_type;
1884 CLOSE c_party_type;
1885
1886 return l_party_type;
1887 END CHECK_PARTY_TYPE;
1888
1889 /*
1890 Wrapper for FND_USER_PKG.validate_user_name, which is a procedure and raises an
1891 exception. We require this wrapper as we dont know whether the exception is due
1892 to invalid username or something other exception .We check the sqlcode and accordingly
1893 process.We cannot have a boolean function as we cannot access it from the JDBC layer.
1894
1895 Code Changes for 5033237/5033238, the errMsg from FND_MESSAGE Stack is being re-used.
1896 */
1897
1898 function validate_user_name(username varchar2, errMsg out NOCOPY varchar2) return number
1899
1900 is
1901
1902
1903 begin
1904 fnd_user_pkg.validate_user_name(username);
1905 return 1;
1906 exception
1907 when others then
1908 IF sqlcode = -20001 then
1909 errMsg := FND_MESSAGE.get;
1910 return 0;
1911 else
1912 raise_application_error(-20001,sqlerrm);
1913 end if;
1914
1915 end validate_user_name;
1916
1917 function validate_user_name_in_use(username varchar2) return number
1918 is
1919 x_return_status pls_integer;
1920 begin
1921 x_return_status := fnd_user_pkg.TestUserName (x_user_name => username);
1922
1923 IF x_return_status = fnd_user_pkg.USER_OK_CREATE then
1924 return 1;
1925 Else
1926 return 0;
1927 End if;
1928
1929 end validate_user_name_in_use;
1930
1931
1932 /* function to get the constant FND_API.G_MISS_DATE and use it in sql*/
1933 FUNCTION GET_G_MISS_DATE return DATE
1934 is
1935 BEGIN
1936 return FND_API.G_MISS_DATE;
1937
1938 END GET_G_MISS_DATE;
1939
1940
1941
1942
1943 /*
1944 bug 4903775 - for name formatting based on region territory
1945 */
1946
1947
1948 function format_user_name(fname varchar2, lname varchar2) return varchar
1949 is
1950
1951 l_return_status varchar2(100);
1952 l_msg_count number;
1953 l_msg_data varchar2(100);
1954 l_person_name varchar(100);
1955 l_formatted_name varchar2(100);
1956 l_formatted_lines_cnt number;
1957 l_formatted_name_tbl HZ_FORMAT_PUB.string_tbl_type;
1958 l_nls_territory varchar2(30);
1959 l_territory_code varchar2(30);
1960
1961 begin
1962 fnd_profile.get(
1963 name => 'ICX_TERRITORY',
1964 val => l_nls_territory
1965 );
1966
1967
1968
1969 select territory_code into l_territory_code
1970 from fnd_territories
1971 where nls_territory = l_nls_territory
1972 and OBSOLETE_FLAG = 'N'
1973 and rownum = 1;
1974
1975
1976 hz_format_pub.format_name (
1977 -- input parameters
1978 -- context info
1979
1980 p_ref_territory_code => l_territory_code,
1981 -- name info
1982 p_person_first_name => fname,
1983 p_person_last_name => lname,
1984
1985 -- output parameters
1986 x_return_status => l_return_status,
1987 x_msg_count => l_msg_count,
1988 x_msg_data => l_msg_data,
1989 x_formatted_name => l_person_name,
1990 x_formatted_lines_cnt => l_formatted_lines_cnt,
1991 x_formatted_name_tbl => l_formatted_name_tbl
1992 );
1993
1994 return l_person_name;
1995
1996 exception
1997 when others then
1998
1999 if l_is_debug_parameter_on then
2000 JTF_DEBUG_PUB.LOG_DEBUG(2, MODULE_NAME, sqlerrm);
2001 end if;
2002 l_person_name := fname || ' ' || lname;
2003 return l_person_name;
2004
2005
2006 end format_user_name;
2007
2008 end JTF_UM_UTIL_PVT;
[Home] [Help]