[Home] [Help]
PACKAGE BODY: APPS.PON_USER_PROFILE_PKG
Source
1 PACKAGE BODY PON_USER_PROFILE_PKG as
2 /*$Header: PONUSPRB.pls 120.14.12010000.2 2009/06/10 05:15:30 anagoel ship $ */
3
4 -- store the profile value for logging in a global constant variable
5 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 -- module prefix for logging
8 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_USER_PROFILE_PKG.';
9
10 procedure update_user_lang(
11 p_username IN VARCHAR2
12 , p_user_language IN VARCHAR2
13 , x_status OUT NOCOPY VARCHAR2
14 , x_exception_msg OUT NOCOPY VARCHAR2
15 )
16 IS
17 l_exception_msg VARCHAR2(100);
18
19 BEGIN
20
21 --store user language in fnd_preferences.
22 PON_PROFILE_UTIL_PKG.SET_WF_LANGUAGE(p_username , p_user_language);
23 PON_PROFILE_UTIL_PKG.SET_WF_LANGUAGE(UPPER(p_username) , p_user_language);
24
25 X_STATUS :='S';
26 x_exception_msg :=NULL;
27
28 -- commit;
29
30 EXCEPTION
31 WHEN PON_PROFILE_UTIL_PKG.HZ_FAIL_EXCEPTION THEN
32 rollback;
33 X_STATUS :='E';
34 raise;
35 WHEN OTHERS THEN
36 rollback;
37 X_STATUS :='U';
38 raise;
39
40 END update_user_lang;
41
42
43 --driven by UI page PersonalInfo.jsp
44 procedure update_user_info(
45 p_username IN VARCHAR2
46 , P_USER_NAME_PREFIX IN VARCHAR2
47 , P_USER_NAME_F IN VARCHAR2
48 , P_USER_NAME_M IN VARCHAR2
49 , P_USER_NAME_L IN VARCHAR2
50 , P_USER_NAME_SUFFIX IN VARCHAR2
51 , P_USER_TITLE IN VARCHAR2
52 , P_USER_EMAIL IN VARCHAR2
53 , P_USER_COUNTRY_CODE IN VARCHAR2
54 , P_USER_AREA_CODE IN VARCHAR2
55 , P_USER_PHONE IN VARCHAR2
56 , P_USER_EXTENSION IN VARCHAR2
57 , P_USER_FAX_COUNTRY_CODE IN VARCHAR2
58 , P_USER_FAX_AREA_CODE IN VARCHAR2
59 , P_USER_FAX IN VARCHAR2
60 , P_USER_FAX_EXTENSION IN VARCHAR2
61 , P_USER_TIMEZONE IN VARCHAR2
62 , P_USER_LANGUAGE IN VARCHAR2
63 , P_USER_DATEFORMAT IN VARCHAR2
64 , P_USER_LOCALE IN VARCHAR2
65 , P_USER_ENCODINGOPTION IN VARCHAR2
66 , x_status OUT NOCOPY VARCHAR2
67 , x_exception_msg OUT NOCOPY VARCHAR2
68 )
69 IS
70 l_user_party_id NUMBER;
71 l_vendor_party_id NUMBER;
72 l_exception_msg VARCHAR2(100);
73 l_count NUMBER;
74 person_rec hz_party_v2pub.person_rec_type;
75 profile_id NUMBER;
76 x_return_status VARCHAR2(1000);
77 x_msg_count NUMBER;
78 x_msg_data VARCHAR2(32767);
79 l_update_date DATE;
80 l_object_version_number number;
81 BEGIN
82 SELECT person_party_id
83 INTO l_user_party_id
84 FROM fnd_user
85 WHERE user_name = p_username;
86
87
88 SELECT vendor_party_id
89 INTO l_vendor_party_id
90 FROM pos_supplier_users_v
91 WHERE person_party_id = l_user_party_id;
92
93 -- CALL iSP API to update the Supplier Info. They in turn will update the required TCA table.
94 -- iSP package : POS_SUPP_CONTACT_PKG
95
96 POS_SUPP_CONTACT_PKG.update_supplier_contact
97 (p_contact_party_id => l_user_party_id,
98 p_vendor_party_id => l_vendor_party_id,
99 p_first_name => P_USER_NAME_F,
100 p_last_name => P_USER_NAME_L,
101 p_middle_name => P_USER_NAME_M,
102 p_contact_title => P_USER_TITLE,
103 p_job_title => NULL,
104 p_phone_area_code => P_USER_COUNTRY_CODE,
105 p_phone_number => P_USER_PHONE,
106 p_phone_extension => P_USER_EXTENSION,
107 p_fax_area_code => P_USER_FAX_AREA_CODE,
108 p_fax_number => P_USER_FAX,
109 p_email_address => P_USER_EMAIL,
110 x_return_status => x_return_status,
111 x_msg_count => x_msg_count,
112 x_msg_data => x_msg_data
113 );
114
115 IF x_return_status IS NULL OR
116 x_return_status <> fnd_api.g_ret_sts_success THEN
117 RAISE UPDATE_SUPPLIER_CONTACT_E;
118 END IF;
119
120 --set email address in fnd_user as well as TCA.
121 UPDATE fnd_user
122 SET email_address = p_user_email
123 WHERE person_party_id = l_user_party_id;
124
125 -- bug 2329157 . Need the user encoding option to be set for the user
126 PON_PROFILE_UTIL_PKG.update_or_insert_party_pref(l_user_party_id, 'PON',
127 'USER_ENCODING', p_user_encodingoption, 'User Charset Encoding Option', NULL,NULL,NULL,NULL,NULL,
128 x_status, x_exception_msg);
129
130 X_STATUS :='S';
131 x_exception_msg :=NULL;
132
133 --commit; -- jazhang 01/08
134 EXCEPTION
135 WHEN UPDATE_SUPPLIER_CONTACT_E THEN
136 X_STATUS := x_return_status;
137 x_exception_msg := x_msg_data;
138 raise;
139 WHEN PON_PROFILE_UTIL_PKG.HZ_FAIL_EXCEPTION THEN
140 --dbms_output.put_line('HZ failure ' || x_exception_msg);
141 --rollback; -- jazhang 01/08
142 X_STATUS :='E';
143 raise;
144 WHEN OTHERS THEN
145 --dbms_output.put_line('Other failure -- '||x_exception_msg);
146 --rollback; -- jazhang 01/08
147 X_STATUS :='U';
148 raise;
149 END update_user_info;
150
151 --retrieve info about a user. Notice TIMEZONE is pretty rudimentary.
152 --Don't know how we're storing, what we're doing with it, etc.
153 procedure retrieve_vendor_user_info(
154 p_username IN VARCHAR2
155 , x_user_party_id OUT NOCOPY NUMBER
156 , X_USER_NAME_PREFIX OUT NOCOPY VARCHAR2
157 , X_USER_NAME_F OUT NOCOPY VARCHAR2
158 , X_USER_NAME_M OUT NOCOPY VARCHAR2
159 , X_USER_NAME_L OUT NOCOPY VARCHAR2
160 , X_USER_NAME_SUFFIX OUT NOCOPY VARCHAR2
161 , X_USER_TITLE OUT NOCOPY VARCHAR2
162 , X_USER_EMAIL OUT NOCOPY VARCHAR2
163 , X_USER_COUNTRY_CODE OUT NOCOPY VARCHAR2
164 , X_USER_AREA_CODE OUT NOCOPY VARCHAR2
165 , X_USER_PHONE OUT NOCOPY VARCHAR2
166 , X_USER_EXTENSION OUT NOCOPY VARCHAR2
167 , X_USER_FAX_COUNTRY_CODE OUT NOCOPY VARCHAR2
168 , X_USER_FAX_AREA_CODE OUT NOCOPY VARCHAR2
169 , X_USER_FAX OUT NOCOPY VARCHAR2
170 , X_USER_FAX_EXTENSION OUT NOCOPY VARCHAR2
171 , X_USER_ENCODINGOPTION OUT NOCOPY VARCHAR2
172 , x_status OUT NOCOPY VARCHAR2
173 , x_exception_msg OUT NOCOPY VARCHAR2
174 )
175 IS
176 l_user_id NUMBER;
177 l_exception_msg VARCHAR2(100);
178 l_count NUMBER;
179 l_attribute VARCHAR2(150);
180 l_meaning VARCHAR2(150);
181 BEGIN
182
183 x_exception_msg := 'begin: retrieve_user_info';
184 -- first, get the user_id and the fk to TCA
185 SELECT person_party_id, user_id
186 INTO x_user_party_id
187 , l_user_id
188 FROM fnd_user
189 WHERE user_name = p_username;
190
191 --grab user info from TCA
192 x_exception_msg:='retrieve_user_info: select person, phone, fax';
193
194 --department and user admin not selected.... ?
195 BEGIN --{
196 SELECT hp1.person_pre_name_adjunct
197 , hp1.person_first_name
198 , hp1.person_middle_name
199 , hp1.person_last_name
200 , hp1.person_name_suffix
201 , hp1.person_title
202 , hc3.email_address
203 , hc1.phone_country_code
204 , hc1.phone_area_code
205 , hc1.phone_number
206 , hc1.phone_extension
207 , hc4.phone_country_code
208 , hc4.phone_area_code
209 , hc4.phone_number
210 , hc4.phone_extension
211 INTO
212 X_USER_NAME_PREFIX
213 , X_USER_NAME_F
214 , X_USER_NAME_M
215 , X_USER_NAME_L
216 , X_USER_NAME_SUFFIX
217 , X_USER_TITLE
218 , X_USER_EMAIL
219 , X_USER_COUNTRY_CODE
220 , X_USER_AREA_CODE
221 , X_USER_PHONE
222 , X_USER_EXTENSION
223 , X_USER_FAX_COUNTRY_CODE
224 , X_USER_FAX_AREA_CODE
225 , X_USER_FAX
226 , X_USER_FAX_EXTENSION
227 FROM HZ_PARTIES hp1 -- Person
228 , HZ_CONTACT_POINTS hc1 -- Phone
229 , HZ_CONTACT_POINTS hc3 -- Email
230 , HZ_CONTACT_POINTS hc4 -- Fax
231 , POS_SUPPLIER_USERS_V posv
232 WHERE hp1.party_id = x_user_party_id
233 AND hp1.party_id = posv.person_party_id
234 AND hp1.status = 'A'
235 AND hc1.owner_table_name(+) = 'HZ_PARTIES'
236 AND hc1.owner_table_id(+) = posv.rel_party_id
237 AND hc1.contact_point_type(+) = 'PHONE'
238 AND hc1.phone_line_type(+) = 'GEN'
239 AND hc1.status(+) = 'A'
240 AND hc1.primary_flag(+) = 'Y'
241 AND hc3.owner_table_name(+) = 'HZ_PARTIES'
242 AND hc3.owner_table_id(+) = posv.rel_party_id
243 AND hc3.contact_point_type(+) = 'EMAIL'
244 AND hc3.primary_flag(+) = 'Y'
245 AND hc3.status(+) = 'A'
246 AND hc4.owner_table_name(+) = 'HZ_PARTIES'
247 AND hc4.owner_table_id(+) = posv.rel_party_id
248 AND hc4.contact_point_type(+) = 'PHONE'
249 AND hc4.phone_line_type(+) = 'FAX'
250 AND hc4.status(+) = 'A'
251 AND hc4.primary_flag(+) = 'Y'
252 AND nvl(posv.USER_END_DATE,sysdate) >= sysdate;
253 EXCEPTION --}
254 WHEN TOO_MANY_ROWS THEN --{
255 IF (g_fnd_debug = 'Y') THEN
256 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
257 FND_LOG.string( log_level => FND_LOG.level_statement,
258 module => g_module_prefix || 'retrieve_user_data',
259 message => '501: Multiple records for person ');
260 END IF;
261 END IF;
262 SELECT hp1.person_pre_name_adjunct
263 , hp1.person_first_name
264 , hp1.person_middle_name
265 , hp1.person_last_name
266 , hp1.person_name_suffix
267 , hp1.person_title
268 , hc3.email_address
269 , hc1.phone_country_code
270 , hc1.phone_area_code
271 , hc1.phone_number
272 , hc1.phone_extension
273 , hc4.phone_country_code
274 , hc4.phone_area_code
275 , hc4.phone_number
276 , hc4.phone_extension
277 INTO
278 X_USER_NAME_PREFIX
279 , X_USER_NAME_F
280 , X_USER_NAME_M
281 , X_USER_NAME_L
282 , X_USER_NAME_SUFFIX
283 , X_USER_TITLE
284 , X_USER_EMAIL
285 , X_USER_COUNTRY_CODE
286 , X_USER_AREA_CODE
287 , X_USER_PHONE
288 , X_USER_EXTENSION
289 , X_USER_FAX_COUNTRY_CODE
290 , X_USER_FAX_AREA_CODE
291 , X_USER_FAX
292 , X_USER_FAX_EXTENSION
293 FROM HZ_PARTIES hp1 -- Person
294 , HZ_CONTACT_POINTS hc1 -- Phone
295 , HZ_CONTACT_POINTS hc3 -- Email
296 , HZ_CONTACT_POINTS hc4 -- Fax
297 , POS_SUPPLIER_USERS_V posv
298 WHERE hp1.party_id = x_user_party_id
299 AND hp1.party_id = posv.person_party_id
300 AND hp1.status = 'A'
301 AND hc1.owner_table_name(+) = 'HZ_PARTIES'
302 AND hc1.owner_table_id(+) = posv.rel_party_id
303 AND hc1.contact_point_type(+) = 'PHONE'
304 AND hc1.phone_line_type(+) = 'GEN'
305 AND hc1.status(+) = 'A'
306 AND hc1.primary_flag(+) = 'Y'
307 AND hc3.owner_table_name(+) = 'HZ_PARTIES'
308 AND hc3.owner_table_id(+) = posv.rel_party_id
309 AND hc3.contact_point_type(+) = 'EMAIL'
310 AND hc3.primary_flag(+) = 'Y'
311 AND hc3.status(+) = 'A'
312 AND hc4.owner_table_name(+) = 'HZ_PARTIES'
313 AND hc4.owner_table_id(+) = posv.rel_party_id
314 AND hc4.contact_point_type(+) = 'PHONE'
315 AND hc4.phone_line_type(+) = 'FAX'
316 AND hc4.status(+) = 'A'
317 AND hc4.primary_flag(+) = 'Y'
318 AND nvl(posv.USER_END_DATE,sysdate) >= sysdate
319 AND rownum = 1;
320 END; --}
321
322 BEGIN
323 PON_PROFILE_UTIL_PKG.retrieve_party_preference(x_user_party_id,'PON',
324 'USER_ENCODING',x_user_encodingoption,l_meaning,l_attribute,l_attribute,
325 l_attribute, l_attribute,l_attribute, x_status, x_exception_msg);
326 EXCEPTION
327 WHEN NO_DATA_FOUND THEN
328 x_user_encodingoption := '';
329 END;
330
331 x_exception_msg :=NULL;
332 X_STATUS :='S';
333 EXCEPTION
334 WHEN OTHERS THEN
335 --dbms_output.put_line('Other failure -- '||x_exception_msg);
336 X_STATUS :='U';
337 raise;
338 END retrieve_vendor_user_info;
339
340 --Need to change passwords when they expire, or when the user wants to
341 --Notice we're setting the _encrypted_ user password -- this function
342 --needs an encrypted password
343 procedure change_password(
344 p_username IN VARCHAR2
345 , p_new_password IN VARCHAR2
346 , x_status OUT NOCOPY VARCHAR2
347 , x_exception_msg OUT NOCOPY VARCHAR2
348 )
349 IS
350 BEGIN
351 --Note: user must be an Oracle Exchange User
352 UPDATE fnd_user
353 SET encrypted_user_password = p_new_password
354 , password_date = sysdate
355 , password_accesses_left = 9999999
356 , last_update_date = sysdate
357 , last_updated_by = fnd_global.user_id
358 WHERE user_name = p_username
359 AND description = 'Oracle Exchange User';
360
361 x_exception_msg :=NULL;
362 X_STATUS :='S';
363 --commit;
364 EXCEPTION
365 WHEN OTHERS THEN
366 --dbms_output.put_line('Other failure -- '||x_exception_msg);
367 --rollback; -- jazhang 01/08
368 X_STATUS :='U';
369 raise;
370 END change_password;
371
372 --checks to see if we need to force the user to change password
373 procedure login(
374 p_username IN VARCHAR2
375 , p_change_password OUT NOCOPY VARCHAR2
376 , x_status OUT NOCOPY VARCHAR2
377 , x_exception_msg OUT NOCOPY VARCHAR2
378 )
379 IS
380 l_user_id NUMBER;
381 l_session_id NUMBER;
382 BEGIN
383 --Note: user must be an Oracle Exchange user
384 SELECT user_id
385 INTO l_user_id
386 FROM fnd_user
387 WHERE user_name = p_username
388 AND description = 'Oracle Exchange User';
389
390 FND_SIGNON.new_session(l_user_id,l_session_id,p_change_password);
391
392 x_exception_msg :=NULL;
393 X_STATUS :='S';
394 EXCEPTION
395 WHEN OTHERS THEN
396 --dbms_output.put_line('Other failure -- '||x_exception_msg);
397 X_STATUS :='U';
398 raise;
399 END login;
400
401 --completely removes a user from fnd_user and TCA tables.
402 --VERY BAD MOJO if you are not sure you want this guy gone.
403 --Does not commit: if you want to commit, you must do it explicitly
404 PROCEDURE delete_user(
405 p_username IN VARCHAR2
406 , x_status OUT NOCOPY VARCHAR2
407 , x_exception_msg OUT NOCOPY VARCHAR2
408 )
409 IS
410 l_user_party_id NUMBER;
411 l_location_id NUMBER;
412 CURSOR party_site_ids IS
413 SELECT party_site_id
414 FROM hz_party_sites
415 WHERE party_id = l_user_party_id;
416 BEGIN
417 SELECT person_party_id
418 INTO l_user_party_id
419 FROM fnd_user
420 WHERE user_name = p_username;
421
422 DELETE FROM fnd_user
423 WHERE user_name = p_username;
424
425 --get rid of user profile
426 DELETE FROM hz_person_profiles
427 WHERE party_id = l_user_party_id;
428
429 --get rid of hz_parties row
430 DELETE FROM hz_parties
431 WHERE party_id = l_user_party_id;
432
433 --get rid of contact points
434 DELETE FROM hz_contact_points
435 WHERE owner_table_name = 'HZ_PARTIES'
436 AND owner_table_id = l_user_party_id;
437
438 x_status := 'S';
439 EXCEPTION
440 WHEN OTHERS THEN
441 x_status := 'E';
442 x_exception_msg := 'Failed to delete user, rolling back';
443 rollback;
444 END delete_user;
445
446
447 --To get the password challenge question and response.
448 procedure retrieve_pwd_challenge (
449 p_user_party_id IN NUMBER
450 , X_USER_PWD_QUESTION OUT NOCOPY VARCHAR2
451 , X_USER_PWD_RESPONSE OUT NOCOPY VARCHAR2
452 , x_status OUT NOCOPY VARCHAR2
453 , x_exception_msg OUT NOCOPY VARCHAR2
454 , x_enc_foundation OUT NOCOPY VARCHAR2
455 )
456 IS
457 -- l_pwd_response_encrypted VARCHAR2(240);
458 l_meaning VARCHAR2(240);
459 l_attribute VARCHAR2(150);
460 BEGIN
461
462 x_exception_msg := 'begin: retrieve_pwd_challenge';
463
464 -- retrieve the user's password challenge question and response
465 x_exception_msg := 'getting the password challenge question and response';
466 pon_profile_util_pkg.retrieve_party_preference(
467 p_party_id => p_user_party_id,
468 p_app_short_name => 'PON',
469 p_pref_name => 'PON_USER_PWD_QUESTION',
470 x_pref_value => x_user_pwd_question,
471 x_pref_meaning => l_meaning,
472 x_attribute1 => l_attribute,
473 x_attribute2 => l_attribute,
474 x_attribute3 => l_attribute,
475 x_attribute4 => l_attribute,
476 x_attribute5 => l_attribute,
477 x_status => x_status,
478 x_exception_msg => x_exception_msg);
479
480 pon_profile_util_pkg.retrieve_party_preference(
481 p_party_id => p_user_party_id,
482 p_app_short_name => 'PON',
483 p_pref_name => 'PON_USER_PWD_RESPONSE',
484 x_pref_value => x_user_pwd_response,
485 x_pref_meaning => l_meaning,
486 x_attribute1 => x_enc_foundation,
487 x_attribute2 => l_attribute,
488 x_attribute3 => l_attribute,
489 x_attribute4 => l_attribute,
490 x_attribute5 => l_attribute,
491 x_status => x_status,
492 x_exception_msg => x_exception_msg);
493
494 -- decrypt the response
495 -- PON_UTIL_MGR.get_util_info(l_pwd_response_encrypted, X_USER_PWD_RESPONSE);
496
497 x_status := 'S';
498 x_exception_msg := '';
499
500 EXCEPTION
501 WHEN NO_DATA_FOUND THEN
502 X_USER_PWD_QUESTION := '';
503 X_USER_PWD_RESPONSE := '';
504 x_status := 'S';
505 x_exception_msg := 'PON_USER_PROFILE.retrieve_pwd_challenge -- The challenge/response for the party_id: ' || p_user_party_id || ' does not exist! ';
506 WHEN OTHERS THEN
507 --dbms_output.put_line('Other failure -- '||x_exception_msg);
508 X_STATUS :='U';
509 raise;
510 END retrieve_pwd_challenge;
511
512 --to update the user's password question and response
513 procedure update_pwd_challenge (
514 p_user_party_id IN NUMBER
515 , p_user_pwd_question IN VARCHAR2
516 , p_user_pwd_response IN VARCHAR2
517 , p_enc_foundation IN VARCHAR2
518 , x_status OUT NOCOPY VARCHAR2
519 , x_exception_msg OUT NOCOPY VARCHAR2
520 )
521 IS
522 -- l_pwd_response_encrypted VARCHAR2(240);
523
524 BEGIN
525
526 --encrypt the response
527 -- PON_UTIL_MGR.pass_util_info(P_USER_PWD_RESPONSE, l_pwd_response_encrypted);
528
529 pon_profile_util_pkg.update_or_insert_party_pref(
530 p_party_id => p_user_party_id,
531 p_app_short_name => 'PON',
532 p_pref_name => 'PON_USER_PWD_QUESTION',
533 p_pref_value => p_user_pwd_question,
534 p_pref_meaning => NULL,
535 p_attribute1 => NULL,
536 p_attribute2 => NULL,
537 p_attribute3 => NULL,
538 p_attribute4 => NULL,
539 p_attribute5 => NULL,
540 x_status => x_status,
541 x_exception_msg => x_exception_msg);
542
543 pon_profile_util_pkg.update_or_insert_party_pref(
544 p_party_id => p_user_party_id,
545 p_app_short_name => 'PON',
546 p_pref_name => 'PON_USER_PWD_RESPONSE',
547 p_pref_value => p_user_pwd_response,
548 p_pref_meaning => NULL,
549 p_attribute1 => p_enc_foundation,
550 p_attribute2 => NULL,
551 p_attribute3 => NULL,
552 p_attribute4 => NULL,
553 p_attribute5 => NULL,
554 x_status => x_status,
555 x_exception_msg => x_exception_msg);
556
557 x_status := 'S';
558 x_exception_msg := '';
559
560 EXCEPTION
561 WHEN PON_PROFILE_UTIL_PKG.HZ_FAIL_EXCEPTION THEN
562 --dbms_output.put_line('HZ failure ' || x_exception_msg);
563 rollback;
564 X_STATUS :='E';
565 raise;
566 WHEN OTHERS THEN
567 --dbms_output.put_line('Other failure -- '||x_exception_msg);
568 rollback;
569 X_STATUS :='U';
570 raise;
571
572 END update_pwd_challenge;
573
574 procedure retrieve_user_data(
575 p_username IN VARCHAR2
576 , x_user_party_id OUT NOCOPY NUMBER
577 , X_USER_NAME_PREFIX OUT NOCOPY VARCHAR2
578 , X_USER_NAME_F OUT NOCOPY VARCHAR2
579 , X_USER_NAME_M OUT NOCOPY VARCHAR2
580 , X_USER_NAME_L OUT NOCOPY VARCHAR2
581 , X_USER_NAME_SUFFIX OUT NOCOPY VARCHAR2
582 , X_USER_TITLE OUT NOCOPY VARCHAR2
583 , X_USER_EMAIL OUT NOCOPY VARCHAR2
584 , X_USER_COUNTRY_CODE OUT NOCOPY VARCHAR2
585 , X_USER_AREA_CODE OUT NOCOPY VARCHAR2
586 , X_USER_PHONE OUT NOCOPY VARCHAR2
587 , X_USER_EXTENSION OUT NOCOPY VARCHAR2
588 , X_USER_FAX_COUNTRY_CODE OUT NOCOPY VARCHAR2
589 , X_USER_FAX_AREA_CODE OUT NOCOPY VARCHAR2
590 , X_USER_FAX OUT NOCOPY VARCHAR2
591 , X_USER_FAX_EXTENSION OUT NOCOPY VARCHAR2
592 , X_USER_ENCODINGOPTION OUT NOCOPY VARCHAR2
593 , X_DUMMY_DATA OUT NOCOPY VARCHAR2
594 , X_EXTRA_INFO OUT NOCOPY VARCHAR2
595 , X_ROW_IN_HR OUT NOCOPY VARCHAR2
596 , X_VENDOR_RELATIONSHIP OUT NOCOPY VARCHAR2
597 , X_ENTERPRISE_RELATIONSHIP OUT NOCOPY VARCHAR2
598 , x_status OUT NOCOPY VARCHAR2
599 , x_exception_msg OUT NOCOPY VARCHAR2
600 )
601 IS
602 l_user_id NUMBER;
603 l_vendor_id NUMBER;
604 l_enterprise_id NUMBER;
605 l_relationship_id NUMBER;
606 BEGIN
607
608 -- Flags for the data
609 x_row_in_hr := 'N';
610 x_vendor_relationship := 'N';
611 x_enterprise_relationship := 'N';
612 x_dummy_data := 'N';
613 x_extra_info := 'N';
614 x_exception_msg :=NULL;
615
616 BEGIN
617
618 select user_id into
619 l_user_id
620 from fnd_user
621 where user_name = p_username;
622
623 IF (g_fnd_debug = 'Y') THEN
624 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
625 FND_LOG.string( log_level => FND_LOG.level_statement,
626 module => g_module_prefix || 'retrieve_user_data',
627 message => '10:' || p_username || ':' || l_user_id);
628 END IF;
629 END IF;
630
631
632 -- check if a row exists in HR
633 l_enterprise_id := pos_party_management_pkg.get_emp_or_ctgt_wrkr_pty_id(l_user_id);
634
635 IF (g_fnd_debug = 'Y') THEN
636 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
637 FND_LOG.string( log_level => FND_LOG.level_statement,
638 module => g_module_prefix || 'retrieve_user_data',
639 message => '20:l_enterprise_id:' || l_enterprise_id);
640 END IF;
641 END IF;
642
643 IF l_enterprise_id <> -1 THEN
644 x_row_in_hr := 'Y';
645 END IF;
646
647 EXCEPTION
648 WHEN NO_DATA_FOUND THEN
649 x_row_in_hr := 'N';
650 END;
651
652 IF (g_fnd_debug = 'Y') THEN
653 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
654 FND_LOG.string( log_level => FND_LOG.level_statement,
655 module => g_module_prefix || 'retrieve_user_data',
656 message => 'l_user_id:' || l_user_id || ':l_enterprise_id:' ||
657 l_enterprise_id || ':x_row_in_hr:' || x_row_in_hr || ':p_username:' || p_username);
658 END IF;
659 END IF;
660
661 if x_row_in_hr = 'Y' then -- {This implies that the user is a HR user.
662
663 l_enterprise_id := POS_PARTY_MANAGEMENT_PKG.check_for_enterprise_user(p_username);
664 IF l_enterprise_id <> -1 THEN
665 x_enterprise_relationship := 'Y';
666 ELSE
667 POS_ENTERPRISE_UTIL_PKG.pos_create_enterprise_user(p_username
668 ,'First'
669 ,'Last'
670 ,'Email'
671 ,x_user_party_id
672 ,l_relationship_id
673 ,x_exception_msg
674 , x_status);
675 IF (x_status = 'S') THEN
676 x_enterprise_relationship := 'Y';
677 ELSE
678 x_enterprise_relationship := 'N';
679 END IF;
680 END IF;
681
682 retrieve_enterprise_user_info(
683 p_username
684 , x_user_party_id
685 , X_USER_NAME_PREFIX
686 , X_USER_NAME_F
687 , X_USER_NAME_M
688 , X_USER_NAME_L
689 , X_USER_NAME_SUFFIX
690 , X_USER_TITLE
691 , X_USER_EMAIL
692 , X_USER_COUNTRY_CODE
693 , X_USER_AREA_CODE
694 , X_USER_PHONE
695 , X_USER_EXTENSION
696 , X_USER_FAX_COUNTRY_CODE
697 , X_USER_FAX_AREA_CODE
698 , X_USER_FAX
699 , X_USER_FAX_EXTENSION
700 , X_USER_ENCODINGOPTION
701 , x_status
702 , x_exception_msg
703 );
704
705 -- HR BUG They populate the First Name as a ********** if it is null.
706 if x_user_name_f = '***********' then
707 x_user_name_f := '';
708 end if;
709
710 if x_user_name_l is null then
711 x_extra_info := 'Y';
712 end if;
713
714 else
715 l_vendor_id := POS_PARTY_MANAGEMENT_PKG.check_for_vendor_user(p_username);
716 if (l_vendor_id <> -1 and l_vendor_id <> -2) then --{
717 x_vendor_relationship := 'Y';
718
719 retrieve_vendor_user_info(
720 p_username
721 , x_user_party_id
722 , X_USER_NAME_PREFIX
723 , X_USER_NAME_F
724 , X_USER_NAME_M
725 , X_USER_NAME_L
726 , X_USER_NAME_SUFFIX
727 , X_USER_TITLE
728 , X_USER_EMAIL
729 , X_USER_COUNTRY_CODE
730 , X_USER_AREA_CODE
731 , X_USER_PHONE
732 , X_USER_EXTENSION
733 , X_USER_FAX_COUNTRY_CODE
734 , X_USER_FAX_AREA_CODE
735 , X_USER_FAX
736 , X_USER_FAX_EXTENSION
737 , X_USER_ENCODINGOPTION
738 , x_status
739 , x_exception_msg
740 );
741
742 --bug 8326307:removed the condition to check the first name
743 IF x_user_name_l is null
744 or x_user_email is null then
745 x_extra_info := 'Y';
746 END IF;
747
748 if x_user_name_l = '__SUPPLIER__' then
749 x_dummy_data := 'Y';
750 end if;
751
752 ELSIF (l_vendor_id = -2) then
753 x_vendor_relationship := 'M';
754
755 end if; --} end if of x_vendor_relationship
756 end if; --} x_row_in_hr
757
758 IF (g_fnd_debug = 'Y') THEN
759 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
760 FND_LOG.string( log_level => FND_LOG.level_statement,
761 module => g_module_prefix || 'retrieve_user_data',
762 message => 'x_status:' || x_status
763 || 'x_exception_msg:' || x_exception_msg
764 || 'x_dummy_data:' || x_dummy_data
765 || 'x_extra_info:' || x_extra_info
766 || 'x_vendor_relationship:' || x_vendor_relationship
767 || 'x_enterprise_relationship:' || x_enterprise_relationship
768 || 'x_row_in_hr:' || x_row_in_hr);
769 END IF;
770 END IF;
771
772 X_STATUS :='S';
773
774 EXCEPTION
775 WHEN OTHERS THEN
776 --dbms_output.put_line('Other failure -- '||x_exception_msg);
777 IF (g_fnd_debug = 'Y') THEN
778 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
779 FND_LOG.string( log_level => FND_LOG.level_exception,
780 module => g_module_prefix || 'retrieve_user_data',
781 message => 'x_status:' || x_status
782 || 'x_exception_msg:' || x_exception_msg
783 || 'x_dummy_data:' || x_dummy_data
784 || 'x_extra_info:' || x_extra_info
785 || 'x_vendor_relationship:' || x_vendor_relationship
786 || 'x_enterprise_relationship:' || x_enterprise_relationship || 'x_row_in_hr:' || x_row_in_hr);
787 END IF;
788 END IF;
789 X_STATUS :='U';
790 END retrieve_user_data;
791
792 procedure retrieve_user_info(
793 p_username IN VARCHAR2
794 , x_user_party_id OUT NOCOPY NUMBER
795 , X_USER_NAME_PREFIX OUT NOCOPY VARCHAR2
796 , X_USER_NAME_F OUT NOCOPY VARCHAR2
797 , X_USER_NAME_M OUT NOCOPY VARCHAR2
798 , X_USER_NAME_L OUT NOCOPY VARCHAR2
799 , X_USER_NAME_SUFFIX OUT NOCOPY VARCHAR2
800 , X_USER_TITLE OUT NOCOPY VARCHAR2
801 , X_USER_EMAIL OUT NOCOPY VARCHAR2
802 , X_USER_COUNTRY_CODE OUT NOCOPY VARCHAR2
803 , X_USER_AREA_CODE OUT NOCOPY VARCHAR2
804 , X_USER_PHONE OUT NOCOPY VARCHAR2
805 , X_USER_EXTENSION OUT NOCOPY VARCHAR2
806 , X_USER_FAX_COUNTRY_CODE OUT NOCOPY VARCHAR2
807 , X_USER_FAX_AREA_CODE OUT NOCOPY VARCHAR2
808 , X_USER_FAX OUT NOCOPY VARCHAR2
809 , X_USER_FAX_EXTENSION OUT NOCOPY VARCHAR2
810 , X_USER_TIMEZONE OUT NOCOPY VARCHAR2
811 , X_USER_DEFAULT_LANGUAGE OUT NOCOPY VARCHAR2
812 , X_USER_DEFAULT_DATEFORMAT OUT NOCOPY VARCHAR2
813 , X_USER_LOCALE OUT NOCOPY VARCHAR2
814 , X_USER_ENCODINGOPTION OUT NOCOPY VARCHAR2
815 , x_status OUT NOCOPY VARCHAR2
816 , x_exception_msg OUT NOCOPY VARCHAR2
817 )
818 IS
819 l_user_id NUMBER;
820 l_exception_msg VARCHAR2(100);
821 l_count NUMBER;
822 l_attribute VARCHAR2(150);
823 l_meaning VARCHAR2(150);
824 BEGIN
825
826 x_exception_msg := 'begin: retrieve_user_info';
827 -- first, get the user_id and the fk to TCA
828 SELECT person_party_id, user_id
829 INTO x_user_party_id
830 , l_user_id
831 FROM fnd_user
832 WHERE user_name = p_username;
833
834 --grab user info from TCA
835
836 x_exception_msg:='retrieve_user_info: select person, phone, fax';
837
838 --department and user admin not selected.... ?
839 SELECT hp1.person_pre_name_adjunct
840 , hp1.person_first_name
841 , hp1.person_middle_name
842 , hp1.person_last_name
843 , hp1.person_name_suffix
844 , hp1.person_title
845 , hc3.email_address
846 , hc1.phone_country_code
847 , hc1.phone_area_code
848 , hc1.phone_number
849 , hc1.phone_extension
850 , hc4.phone_country_code
851 , hc4.phone_area_code
852 , hc4.phone_number
853 , hc4.phone_extension
854 INTO
855 X_USER_NAME_PREFIX
856 , X_USER_NAME_F
857 , X_USER_NAME_M
858 , X_USER_NAME_L
859 , X_USER_NAME_SUFFIX
860 , X_USER_TITLE
861 , X_USER_EMAIL
862 , X_USER_COUNTRY_CODE
863 , X_USER_AREA_CODE
864 , X_USER_PHONE
865 , X_USER_EXTENSION
866 , X_USER_FAX_COUNTRY_CODE
867 , X_USER_FAX_AREA_CODE
868 , X_USER_FAX
869 , X_USER_FAX_EXTENSION
870 FROM HZ_PARTIES hp1 -- Person
871 , HZ_CONTACT_POINTS hc1 -- Phone
872 , HZ_CONTACT_POINTS hc3 -- Email
873 , HZ_CONTACT_POINTS hc4 -- Fax
874 WHERE hp1.party_id = x_user_party_id
875 AND hc1.owner_table_name(+) = 'HZ_PARTIES'
876 AND hc1.owner_table_id(+) = hp1.party_id
877 AND hc1.contact_point_type(+) = 'PHONE'
878 AND hc1.phone_line_type(+) = 'GEN'
879 AND hc1.status(+) = 'A'
880 AND hc1.primary_flag(+) = 'Y'
881 AND hc3.owner_table_name(+) = 'HZ_PARTIES'
882 AND hc3.owner_table_id(+) = hp1.party_id
883 AND hc3.contact_point_type(+) = 'EMAIL'
884 AND hc3.EMAIL_FORMAT(+) = 'MAILTEXT'
885 AND hc3.status(+) = 'A'
886 AND hc3.primary_flag(+) = 'Y'
887 AND hc4.owner_table_name(+) = 'HZ_PARTIES'
888 AND hc4.owner_table_id(+) = hp1.party_id
889 AND hc4.contact_point_type(+) = 'PHONE'
890 AND hc4.phone_line_type(+) = 'FAX'
891 AND hc4.status(+) = 'A'
892 AND hc4.primary_flag(+) = 'Y';
893
894 -- language, timezone, dateformat information is retrieved from fnd now
895 -- so we just set dummy values here
896 x_user_timezone := '4';
897 x_user_default_language := 'US';
898 x_user_default_dateformat := '';
899 x_user_locale := '';
900
901 BEGIN
902 PON_PROFILE_UTIL_PKG.retrieve_party_preference(x_user_party_id,'PON',
903 'USER_ENCODING',x_user_encodingoption,l_meaning,l_attribute,l_attribute,
904 l_attribute, l_attribute,l_attribute, x_status, x_exception_msg);
905 EXCEPTION
906 WHEN NO_DATA_FOUND THEN
907 x_user_encodingoption := '';
908 END;
909
910 x_exception_msg :=NULL;
911 X_STATUS :='S';
912 EXCEPTION
913 WHEN OTHERS THEN
914 --dbms_output.put_line('Other failure -- '||x_exception_msg);
915 X_STATUS :='U';
916 raise;
917 END retrieve_user_info;
918
919 procedure retrieve_enterprise_user_info(
920 p_username IN VARCHAR2
921 , x_user_party_id OUT NOCOPY NUMBER
922 , X_USER_NAME_PREFIX OUT NOCOPY VARCHAR2
923 , X_USER_NAME_F OUT NOCOPY VARCHAR2
924 , X_USER_NAME_M OUT NOCOPY VARCHAR2
925 , X_USER_NAME_L OUT NOCOPY VARCHAR2
926 , X_USER_NAME_SUFFIX OUT NOCOPY VARCHAR2
927 , X_USER_TITLE OUT NOCOPY VARCHAR2
928 , X_USER_EMAIL OUT NOCOPY VARCHAR2
929 , X_USER_COUNTRY_CODE OUT NOCOPY VARCHAR2
930 , X_USER_AREA_CODE OUT NOCOPY VARCHAR2
931 , X_USER_PHONE OUT NOCOPY VARCHAR2
932 , X_USER_EXTENSION OUT NOCOPY VARCHAR2
933 , X_USER_FAX_COUNTRY_CODE OUT NOCOPY VARCHAR2
934 , X_USER_FAX_AREA_CODE OUT NOCOPY VARCHAR2
935 , X_USER_FAX OUT NOCOPY VARCHAR2
936 , X_USER_FAX_EXTENSION OUT NOCOPY VARCHAR2
937 , X_USER_ENCODINGOPTION OUT NOCOPY VARCHAR2
938 , x_status OUT NOCOPY VARCHAR2
939 , x_exception_msg OUT NOCOPY VARCHAR2
940 )
941 IS
942 l_user_id NUMBER;
943 l_exception_msg VARCHAR2(100);
944 l_employee_id NUMBER;
945 l_count NUMBER;
946 l_attribute VARCHAR2(150);
947 l_meaning VARCHAR2(150);
948 BEGIN
949
950 x_exception_msg := 'begin: retrieve_user_info';
951 -- first, get the user_id and the fk to TCA
952 SELECT person_party_id, user_id, employee_id
953 INTO x_user_party_id
954 , l_user_id
955 , l_employee_id
956 FROM fnd_user
957 WHERE user_name = p_username;
958
959 --grab user info from TCA
960 x_exception_msg:='retrieve_user_info: select person, phone, fax';
961
962 --department and user admin not selected.... ?
963 SELECT hp1.person_pre_name_adjunct
964 , hp1.person_first_name
965 , hp1.person_middle_name
966 , hp1.person_last_name
967 , hp1.person_name_suffix
968 , hp1.person_title
969 INTO
970 X_USER_NAME_PREFIX
971 , X_USER_NAME_F
972 , X_USER_NAME_M
973 , X_USER_NAME_L
974 , X_USER_NAME_SUFFIX
975 , X_USER_TITLE
976 FROM HZ_PARTIES hp1 -- Person
977 WHERE hp1.party_id = x_user_party_id
978 and hp1.status = 'A';
979
980 begin
981 select email_address
982 into x_user_email
983 from per_all_people_f
984 where person_id = l_employee_id
985 and effective_start_date < sysdate
986 and nvl(effective_end_date,sysdate) >= sysdate;
987 EXCEPTION
988 WHEN OTHERS THEN
989 x_user_email := '';
990
991 END;
992
993 begin
994 select phone_number
995 into x_user_phone
996 from per_phones
997 where phone_type = 'W1'
998 and parent_id = l_employee_id;
999 EXCEPTION
1000 WHEN OTHERS THEN
1001 x_user_phone := '';
1002 END;
1003
1004 begin
1005 select phone_number
1006 into x_user_fax
1007 from per_phones
1008 where phone_type = 'WF'
1009 and parent_id = l_employee_id;
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 x_user_fax := '';
1013 END;
1014
1015
1016 X_USER_COUNTRY_CODE := '';
1017 X_USER_AREA_CODE := '';
1018 X_USER_EXTENSION := '';
1019 X_USER_FAX_COUNTRY_CODE := '';
1020 X_USER_FAX_AREA_CODE := '';
1021 X_USER_FAX_EXTENSION := '';
1022
1023 BEGIN
1024 PON_PROFILE_UTIL_PKG.retrieve_party_preference(x_user_party_id,'PON',
1025 'USER_ENCODING',x_user_encodingoption,l_meaning,l_attribute,l_attribute,
1026 l_attribute, l_attribute,l_attribute, x_status, x_exception_msg);
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 x_user_encodingoption := '';
1030 END;
1031
1032 x_exception_msg :=NULL;
1033 X_STATUS :='S';
1034 EXCEPTION
1035 WHEN OTHERS THEN
1036 --dbms_output.put_line('Other failure -- '||x_exception_msg);
1037 X_STATUS :='U';
1038 raise;
1039 END retrieve_enterprise_user_info;
1040
1041
1042 END PON_USER_PROFILE_PKG;