[Home] [Help]
PACKAGE BODY: APPS.POS_USER_ADMIN_PKG
Source
1 PACKAGE BODY pos_user_admin_pkg as
2 /*$Header: POSADMB.pls 120.30.12010000.2 2008/11/10 19:53:08 jburugul ship $ */
3
4 g_module CONSTANT VARCHAR2(30) := 'POS_USER_ADMIN_PKG';
5
6 procedure reset_password
7 ( p_user_id IN NUMBER
8 , x_return_status OUT NOCOPY VARCHAR2
9 , x_msg_count OUT NOCOPY NUMBER
10 , x_msg_data OUT NOCOPY VARCHAR2
11 )
12 IS
13 l_process wf_process_activities.process_name%TYPE;
14 l_itemkey wf_items.item_key%TYPE;
15 l_itemtype wf_items.item_type%TYPE;
16
17 l_unencrypted_password VARCHAR2(30);
18 l_enterprise_name VARCHAR2(240);
19 l_resetpass BOOLEAN;
20
21 CURSOR fnd_user_cur IS
22 SELECT * FROM fnd_user WHERE user_id = p_user_id;
23
24 l_fnd_user_rec fnd_user_cur%ROWTYPE;
25
26 BEGIN
27 OPEN fnd_user_cur;
28 FETCH fnd_user_cur INTO l_fnd_user_rec;
29
30 IF ( fnd_user_cur%notfound) THEN
31 CLOSE fnd_user_cur;
32 raise_application_error(-20001,'POSADMB: reset_password: Invalid user_id');
33
34 ELSE
35 CLOSE fnd_user_cur;
36 END IF;
37
38 l_process := 'POS_BPR_RESET_PASS';
39 l_itemtype := 'POSBPR';
40 l_itemkey := to_char(p_user_id) || ':' || to_char(sysdate,'RRDDDSSSSS');
41
42 l_unencrypted_password := POS_PASSWORD_UTIL_PKG.generate_user_pwd();
43 l_resetpass := FND_USER_PKG.ChangePassword(l_fnd_user_rec.user_name, l_unencrypted_password);
44
45 fnd_user_pkg.updateuser
46 ( x_user_name => l_fnd_user_rec.user_name,
47 x_owner => NULL,
48 x_end_date => l_fnd_user_rec.end_date,
49 x_password_date => fnd_user_pkg.null_date,
50 x_password_accesses_left => l_fnd_user_rec.password_accesses_left,
51 x_password_lifespan_accesses => l_fnd_user_rec.password_lifespan_accesses,
52 x_password_lifespan_days => l_fnd_user_rec.password_lifespan_days,
53 x_email_address => l_fnd_user_rec.email_address,
54 x_fax => l_fnd_user_rec.fax,
55 x_customer_id => l_fnd_user_rec.customer_id
56 );
57
58 pos_enterprise_util_pkg.get_enterprise_party_name
59 (l_enterprise_name,
60 x_msg_data,
61 x_return_status
62 );
63
64 IF x_msg_data IS NOT NULL THEN
65 x_msg_count := 1;
66 END IF;
67
68 pos_log.log_call_result
69 (p_module => 'POSADMB',
70 p_prefix => 'call fnd_user_pkg.update_user',
71 p_return_status => x_return_status,
72 p_msg_count => x_msg_count,
73 p_msg_data => x_msg_data
74 );
75
76 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
77 RETURN;
78 END IF;
79
80 -- Send the email to user about the new password
81 wf_engine.CreateProcess(itemtype => l_itemtype,
82 itemkey => l_itemkey,
83 process => l_process);
84
85 wf_engine.SetItemAttrText (itemtype => l_itemtype,
86 itemkey => l_itemkey,
87 aname => 'USERNAME',
88 avalue => l_fnd_user_rec.user_name);
89
90 wf_engine.SetItemAttrText (itemtype => l_itemtype,
91 itemkey => l_itemkey,
92 aname => 'ENTERPRISE_NAME',
93 avalue => l_enterprise_name);
94
95 wf_engine.SetItemAttrText (itemtype => l_itemtype,
96 itemkey => l_itemkey,
97 aname => 'PASSWORD',
98 avalue => l_unencrypted_password);
99
100 wf_engine.StartProcess(itemtype => l_itemtype,
101 itemkey => l_itemkey);
102
103 x_return_status := fnd_api.g_ret_sts_success;
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 pos_log.log_sqlerrm('POSADMB', 'in reset_password');
108 RAISE;
109 END reset_password;
110
111 PROCEDURE set_user_inactive_date
112 ( p_user_id IN NUMBER
113 , p_inactive_date IN DATE
114 , x_return_status OUT NOCOPY VARCHAR2
115 , x_msg_count OUT NOCOPY NUMBER
116 , x_msg_data OUT NOCOPY VARCHAR2
117 )
118 IS
119 CURSOR fnd_user_cur IS
120 SELECT user_name FROM fnd_user WHERE user_id = p_user_id;
121
122 l_fnd_user_rec fnd_user_cur%ROWTYPE;
123 l_end_date DATE;
124 l_start_date DATE;
125 l_old_inactive_date date;
126 BEGIN
127 OPEN fnd_user_cur;
128 FETCH fnd_user_cur INTO l_fnd_user_rec;
129
130 IF ( fnd_user_cur%notfound) THEN
131 CLOSE fnd_user_cur;
132 raise_application_error(-20001,'POSADMB: end_date_user_account: Invalid user_id');
133
134 ELSE
135 CLOSE fnd_user_cur;
136 END IF;
137
138 if p_inactive_date is null then
139 select start_date, end_date
140 into l_start_date,l_old_inactive_date
141 from fnd_user
142 where user_name = l_fnd_user_rec.user_name;
143
144 if (l_old_inactive_date is not null) then
145 fnd_user_pkg.enableuser(username => l_fnd_user_rec.user_name,
146 start_date => l_start_date);
147 end if;
148 else
149 l_end_date := p_inactive_date;
150 fnd_user_pkg.updateuser
151 ( x_user_name => l_fnd_user_rec.user_name,
152 x_owner => NULL,
153 x_end_date => l_end_date
154 );
155 end if;
156
157
158 x_return_status := fnd_api.g_ret_sts_success;
159
160 EXCEPTION
161 WHEN OTHERS THEN
162 pos_log.log_sqlerrm('POSADMB', 'in reset_password');
163 RAISE;
164 END set_user_inactive_date;
165
166 procedure grant_user_resp
167 ( p_user_id IN NUMBER
168 , p_resp_id IN NUMBER
169 , p_resp_app_id IN NUMBER
170 , x_return_status OUT NOCOPY VARCHAR2
171 , x_msg_count OUT NOCOPY NUMBER
172 , x_msg_data OUT NOCOPY VARCHAR2
173 )
174 IS
175 BEGIN
176 x_return_status := fnd_api.g_ret_sts_success;
177 fnd_user_resp_groups_api.upload_assignment
178 ( user_id => p_user_id,
179 responsibility_id => p_resp_id,
180 responsibility_application_id => p_resp_app_id,
181 security_group_id => 0,
182 start_date => Sysdate,
183 end_date => NULL,
184 description => 'POS User Profile'
185 );
186
187 x_return_status := fnd_api.g_ret_sts_success;
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 pos_log.log_sqlerrm
192 ('POSADMB',
193 'in grant_user_resp: p_user_id = ' || p_user_id
194 || ' p_resp_id = ' || p_resp_id
195 || ' p_resp_app_id = ' || p_resp_app_id
196 );
197 RAISE;
198 END grant_user_resp;
199
200 procedure grant_user_resp
201 ( p_user_id IN NUMBER
202 , p_resp_key IN VARCHAR2
203 , p_resp_app_id IN NUMBER
204 , x_return_status OUT NOCOPY VARCHAR2
205 , x_msg_count OUT NOCOPY NUMBER
206 , x_msg_data OUT NOCOPY VARCHAR2
207 ) IS
208 l_resp_id NUMBER;
209 BEGIN
210 x_return_status := fnd_api.g_ret_sts_success;
211 SELECT responsibility_id INTO l_resp_id
212 FROM fnd_responsibility
213 WHERE responsibility_key = p_resp_key
214 AND application_id = p_resp_app_id;
215
216 grant_user_resp
217 (p_user_id => p_user_id,
218 p_resp_id => l_resp_id,
219 p_resp_app_id => p_resp_app_id,
220 x_return_status => x_return_status,
221 x_msg_count => x_msg_count,
222 x_msg_data => x_msg_data
223 );
224
225 END grant_user_resp;
226
227 procedure grant_user_resps
228 ( p_user_id IN NUMBER
229 , p_resp_ids IN po_tbl_number
230 , p_resp_app_ids IN po_tbl_number
231 , x_return_status OUT NOCOPY VARCHAR2
232 , x_msg_count OUT NOCOPY NUMBER
233 , x_msg_data OUT NOCOPY VARCHAR2
234 )
235 IS
236 BEGIN
237 SAVEPOINT grant_user_resps_sp;
238
239 FOR i IN 1..p_resp_ids.COUNT LOOP
240 grant_user_resp
241 (p_user_id => p_user_id,
242 p_resp_id => p_resp_ids(i),
243 p_resp_app_id => p_resp_app_ids(i),
244 x_return_status => x_return_status,
245 x_msg_count => x_msg_count,
246 x_msg_data => x_msg_data
247 );
248 IF x_return_status IS NULL
249 OR x_return_status <> fnd_api.g_ret_sts_success THEN
250 ROLLBACK TO grant_user_resps_sp;
251 RETURN;
252 END IF;
253 END LOOP;
254
255 x_return_status := fnd_api.g_ret_sts_success;
256
257 END grant_user_resps;
258
259 procedure revoke_user_resp
260 ( p_user_id IN NUMBER
261 , p_resp_id IN NUMBER
262 , p_resp_app_id IN NUMBER
263 , x_return_status OUT NOCOPY VARCHAR2
264 , x_msg_count OUT NOCOPY NUMBER
265 , x_msg_data OUT NOCOPY VARCHAR2
266 )
267 IS
268 l_start_date DATE;
269 CURSOR l_cur IS
270 SELECT start_date FROM fnd_user_resp_groups
271 WHERE user_id = p_user_id AND responsibility_id = p_resp_id;
272 BEGIN
273 OPEN l_cur;
274 FETCH l_cur INTO l_start_date;
275 IF l_cur%notfound THEN
276 CLOSE l_cur;
277 x_return_status := fnd_api.g_ret_sts_success;
278 RETURN;
279 END IF;
280 fnd_user_resp_groups_api.upload_assignment
281 ( user_id => p_user_id,
282 responsibility_id => p_resp_id,
283 responsibility_application_id => p_resp_app_id,
284 security_group_id => 0,
285 start_date => l_start_date,
286 end_date => sysdate,
287 description => 'POS User Profile'
288 );
289 x_return_status := fnd_api.g_ret_sts_success;
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 pos_log.log_sqlerrm('POSADMB', 'in revoke_user_resp');
294 RAISE;
295 END revoke_user_resp;
296
297 procedure revoke_user_resps
298 ( p_user_id IN NUMBER
299 , p_resp_ids IN po_tbl_number
300 , p_resp_app_ids IN po_tbl_number
301 , x_return_status OUT NOCOPY VARCHAR2
302 , x_msg_count OUT NOCOPY NUMBER
303 , x_msg_data OUT NOCOPY VARCHAR2
304 )
305 IS
306 BEGIN
307 SAVEPOINT revoke_user_resps_sp;
308
309 FOR i IN 1..p_resp_ids.COUNT LOOP
310 revoke_user_resp
311 (p_user_id => p_user_id,
312 p_resp_id => p_resp_ids(i),
313 p_resp_app_id => p_resp_app_ids(i),
314 x_return_status => x_return_status,
315 x_msg_count => x_msg_count,
316 x_msg_data => x_msg_data
317 );
318 IF x_return_status IS NULL
319 OR x_return_status <> fnd_api.g_ret_sts_success THEN
320 ROLLBACK TO revoke_user_resps_sp;
321 RETURN;
322 END IF;
323 END LOOP;
324
325 x_return_status := fnd_api.g_ret_sts_success;
326
327 END revoke_user_resps;
328
329 procedure update_user_info
330 ( p_party_id IN NUMBER
331 , p_user_name_prefix IN VARCHAR2
332 , p_user_name_f IN VARCHAR2
333 , p_user_name_m IN VARCHAR2
334 , p_user_name_l IN VARCHAR2
335 , p_user_title IN VARCHAR2
336 , p_user_email IN VARCHAR2
337 , p_user_phone IN VARCHAR2
338 , p_user_extension IN VARCHAR2
339 , p_user_fax IN VARCHAR2
340 , x_return_status OUT NOCOPY VARCHAR2
341 , x_msg_count OUT NOCOPY NUMBER
342 , x_msg_data OUT NOCOPY VARCHAR2
343 )
344 IS
345 l_person_rec hz_party_v2pub.person_rec_type;
346 l_profile_id NUMBER;
347
348 CURSOR l_user_cur IS
349 SELECT user_name
350 FROM fnd_user
351 WHERE person_party_id = p_party_id;
352
353 CURSOR l_party_cur IS
354 SELECT object_version_number, last_update_date
355 FROM hz_parties
356 WHERE party_id = p_party_id;
357
358 l_party_rec l_party_cur%ROWTYPE;
359
360 l_return_status VARCHAR2(1);
361 l_msg_count NUMBER;
362 l_msg_data VARCHAR2(3000);
363 BEGIN
364 OPEN l_party_cur;
365 FETCH l_party_cur INTO l_party_rec;
366 IF l_party_cur%notfound THEN
367 CLOSE l_party_cur;
368 x_return_status := fnd_api.g_ret_sts_error;
369 x_msg_count := 1;
370 x_msg_data := 'invalid party id ' || p_party_id;
371 RETURN;
372 END IF;
373 CLOSE l_party_cur;
374
375 IF l_party_rec.object_version_number IS NULL THEN
376 l_party_rec.object_version_number := 0;
377 END IF;
378
379 --call TCA API's
380 --fnd_client_info.set_org_context('-3113');
381
382 l_person_rec.person_pre_name_adjunct := p_user_name_prefix;
383 l_person_rec.person_first_name := p_user_name_f;
384 l_person_rec.person_middle_name := p_user_name_m;
385 l_person_rec.person_last_name := p_user_name_l;
386 l_person_rec.person_pre_name_adjunct := p_user_title;
387 l_person_rec.party_rec.party_id := p_party_id;
388
389 UPDATE hz_person_profiles
390 SET effective_start_date=trunc(SYSDATE)
391 WHERE party_id = p_party_id;
392
393 hz_party_v2pub.update_person
394 ( p_person_rec => l_person_rec,
395 p_party_object_version_number => l_party_rec.object_version_number,
396 x_profile_id => l_profile_id,
397 x_return_status => x_return_status,
398 x_msg_count => x_msg_count,
399 x_msg_data => x_msg_data
400 );
401
402 pos_log.log_call_result
403 (p_module => 'POSADMB',
404 p_prefix => 'call hz_party_v2pub.update_person.update_person',
405 p_return_status => x_return_status,
406 p_msg_count => x_msg_count,
407 p_msg_data => x_msg_data
408 );
409
410 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
411 RETURN;
412 END IF;
413
414 pos_hz_contact_point_pkg.update_party_phone
415 ( p_party_id => p_party_id,
416 p_country_code => NULL,
417 p_area_code => NULL,
418 p_number => p_user_phone,
419 p_extension => p_user_extension,
420 x_return_status => x_return_status,
421 x_msg_count => x_msg_count,
422 x_msg_data => x_msg_data
423 );
424
425 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
426 RETURN;
427 END IF;
428
429 pos_hz_contact_point_pkg.update_party_fax
430 ( p_party_id => p_party_id,
431 p_country_code => NULL,
432 p_area_code => NULL,
433 p_number => p_user_fax,
434 p_extension => NULL,
435 x_return_status => x_return_status,
436 x_msg_count => x_msg_count,
437 x_msg_data => x_msg_data
438 );
439
440 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
441 RETURN;
442 END IF;
443
444 IF p_user_email IS NOT NULL THEN
445 pos_hz_contact_point_pkg.update_party_email
446 ( p_party_id => p_party_id,
447 p_email => p_user_email,
448 x_return_status => x_return_status,
449 x_msg_count => x_msg_count,
450 x_msg_data => x_msg_data
451 );
452 END IF;
453
454 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
455 RETURN;
456 END IF;
457
458 --set email address in fnd_user as well as TCA.
459
460 FOR l_user_rec IN l_user_cur LOOP
461 fnd_user_pkg.updateuser
462 ( x_user_name => l_user_rec.user_name,
463 x_owner => NULL,
464 x_email_address => p_user_email,
465 x_customer_id => p_party_id
466 );
467 END LOOP;
468
469 x_return_status := fnd_api.g_ret_sts_success;
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 pos_log.log_sqlerrm('POSADMB', 'in update_user_info');
474 raise_application_error(-20002,'POSADMB:UPDATE_USER_INFO: Caught an exception', true);
475 END update_user_info;
476
477 PROCEDURE createsecattr
478 ( p_user_id IN NUMBER
479 , p_attribute_code IN VARCHAR2
480 , p_app_id IN NUMBER
481 , p_varchar2_value IN VARCHAR2 DEFAULT NULL
482 , p_date_value IN DATE DEFAULT NULL
483 , p_number_value IN NUMBER DEFAULT NULL
484 )
485 IS
486 l_return_status VARCHAR2(1);
487 l_msg_count NUMBER;
488 l_msg_data VARCHAR2(2000);
489 BEGIN
490 icx_user_sec_attr_pvt.create_user_sec_attr
491 ( p_api_version_number => 1.0,
492 p_return_status => l_return_status,
493 p_msg_count => l_msg_count,
494 p_msg_data => l_msg_data,
495 p_web_user_id => p_user_id,
496 p_attribute_code => p_attribute_code,
497 p_attribute_appl_id => p_app_id,
498 p_varchar2_value => p_varchar2_value,
499 p_date_value => p_date_value,
500 p_number_value => p_number_value,
501 p_created_by => fnd_global.user_id,
502 p_creation_date => Sysdate,
503 p_last_updated_by => fnd_global.user_id,
504 p_last_update_date => Sysdate,
505 p_last_update_login => fnd_global.login_id
506 );
507
508 pos_log.log_call_result
509 (p_module => 'POSADMB',
510 p_prefix => 'call icx_user_sec_attr_pvt.create_user_sec_attr ',
511 p_return_status => l_return_status,
512 p_msg_count => l_msg_count,
513 p_msg_data => l_msg_data
514 );
515
516 IF l_return_status IS NULL OR l_return_status <> fnd_api.g_ret_sts_success THEN
517 IF l_msg_count > 0 THEN
518 pos_log.combine_fnd_msg(l_msg_count, l_msg_data);
519 END IF;
520
521 raise_application_error(-20001
522 ,'POSADMB.createsecattr '
523 || ' return status ' || l_return_status
524 || ' msg count ' || l_msg_count
525 || ' msg data ' || l_msg_data
526 );
527 END IF;
528 EXCEPTION
529 WHEN OTHERS THEN
530 pos_log.log_sqlerrm('POSADMB','in createsecattr');
531 RAISE;
532 END createsecattr;
533
534 PROCEDURE deletesecattr
535 ( p_user_id IN NUMBER
536 , p_attribute_code IN VARCHAR2
537 , p_app_id IN NUMBER
538 , p_varchar2_value IN VARCHAR2 DEFAULT NULL
539 , p_date_value IN DATE DEFAULT NULL
540 , p_number_value IN NUMBER DEFAULT NULL
541 )
542 IS
543 l_return_status VARCHAR2(1);
544 l_msg_count NUMBER;
545 l_msg_data VARCHAR2(2000);
546 BEGIN
547 icx_user_sec_attr_pvt.delete_user_sec_attr
548 ( p_api_version_number => 1.0,
549 p_return_status => l_return_status,
550 p_msg_count => l_msg_count,
551 p_msg_data => l_msg_data,
552 p_web_user_id => p_user_id,
553 p_attribute_code => p_attribute_code,
554 p_attribute_appl_id => p_app_id,
555 p_varchar2_value => p_varchar2_value,
556 p_date_value => p_date_value,
557 p_number_value => p_number_value
558 );
559
560 pos_log.log_call_result
561 (p_module => 'POSADMB',
562 p_prefix => 'call icx_user_sec_attr_pvt.delete_user_sec_attr ',
563 p_return_status => l_return_status,
564 p_msg_count => l_msg_count,
565 p_msg_data => l_msg_data
566 );
567
568 IF l_return_status IS NULL OR l_return_status <> fnd_api.g_ret_sts_success THEN
569 IF l_msg_count > 0 THEN
570 pos_log.combine_fnd_msg(l_msg_count, l_msg_data);
571 END IF;
572
573 raise_application_error(-20001
574 ,'POSADMB.deletesecattr '
575 || ' return status ' || l_return_status
576 || ' msg count ' || l_msg_count
577 || ' msg data ' || l_msg_data
578 );
579 END IF;
580 EXCEPTION
581 WHEN OTHERS THEN
582 pos_log.log_sqlerrm('POSADMB','in deletesecattr');
583 RAISE;
584 END deletesecattr;
585
586
587 -- The following are backward compatible versions of the
588 -- procedures above. The difference between these and their
589 -- corresponding versions above is that the error messages
590 -- are combined into one in the output parameter.
591 --
592 -- For new code, please use the new versions above
593
594 procedure grant_user_resp
595 ( p_user_id IN NUMBER
596 , p_resp_id IN NUMBER
597 , p_resp_app_id IN NUMBER
598 , x_status OUT NOCOPY VARCHAR2
599 , x_exception_msg OUT NOCOPY VARCHAR2
600 )
601 IS
602 l_return_status VARCHAR2(1);
603 l_msg_count NUMBER;
604 l_msg_data VARCHAR2(3000);
605 BEGIN
606 grant_user_resp
607 (p_user_id => p_user_id,
608 p_resp_id => p_resp_id,
609 p_resp_app_id => p_resp_app_id,
610 x_return_status => l_return_status,
611 x_msg_count => l_msg_count,
612 x_msg_data => l_msg_data
613 );
614
615 x_status := l_return_status;
616 IF l_return_status = fnd_api.g_ret_sts_success THEN
617 x_exception_msg := NULL;
618 ELSE
619 IF l_msg_count = 1 THEN
620 x_exception_msg := l_msg_data;
621 ELSE
622 pos_log.combine_fnd_msg(l_msg_count, x_exception_msg);
623 END IF;
624 END IF;
625 END grant_user_resp;
626
627 procedure revoke_user_resp
628 ( p_user_id IN NUMBER
629 , p_resp_id IN NUMBER
630 , p_resp_app_id IN NUMBER
631 , x_status OUT NOCOPY VARCHAR2
632 , x_exception_msg OUT NOCOPY VARCHAR2
633 )
634 IS
635 l_return_status VARCHAR2(1);
636 l_msg_count NUMBER;
637 l_msg_data VARCHAR2(3000);
638 BEGIN
639 revoke_user_resp
640 (p_user_id => p_user_id,
641 p_resp_id => p_resp_id,
642 p_resp_app_id => p_resp_app_id,
643 x_return_status => l_return_status,
644 x_msg_count => l_msg_count,
645 x_msg_data => l_msg_data
646 );
647
648 x_status := l_return_status;
649 IF l_return_status = fnd_api.g_ret_sts_success THEN
650 x_exception_msg := NULL;
651 ELSE
652 IF l_msg_count = 1 THEN
653 x_exception_msg := l_msg_data;
654 ELSE
655 pos_log.combine_fnd_msg(l_msg_count, x_exception_msg);
656 END IF;
657 END IF;
658 END revoke_user_resp;
659
660 procedure update_user_info
661 ( p_party_id IN NUMBER
662 , p_user_name_prefix IN VARCHAR2
663 , p_user_name_f IN VARCHAR2
664 , p_user_name_m IN VARCHAR2
665 , p_user_name_l IN VARCHAR2
666 , p_user_title IN VARCHAR2
667 , p_user_email IN VARCHAR2
668 , p_user_phone IN VARCHAR2
669 , p_user_extension IN VARCHAR2
670 , p_user_fax IN VARCHAR2
671 , x_status OUT NOCOPY VARCHAR2
672 , x_exception_msg OUT NOCOPY VARCHAR2
673 )
674 IS
675 l_return_status VARCHAR2(1);
676 l_msg_count NUMBER;
677 l_msg_data VARCHAR2(3000);
678 BEGIN
679 update_user_info
680 (p_party_id => p_party_id,
681 p_user_name_prefix => p_user_name_prefix,
682 p_user_name_f => p_user_name_f,
683 p_user_name_m => p_user_name_m,
684 p_user_name_l => p_user_name_l,
685 p_user_title => p_user_title,
686 p_user_email => p_user_email,
687 p_user_phone => p_user_phone,
688 p_user_extension => p_user_extension,
689 p_user_fax => p_user_fax,
690 x_return_status => l_return_status,
691 x_msg_count => l_msg_count,
692 x_msg_data => l_msg_data
693 );
694
695 x_status := l_return_status;
696 IF l_return_status = fnd_api.g_ret_sts_success THEN
697 x_exception_msg := NULL;
698 ELSE
699 IF l_msg_count = 1 THEN
700 x_exception_msg := l_msg_data;
701 ELSE
702 pos_log.combine_fnd_msg(l_msg_count, x_exception_msg);
703 END IF;
704 END IF;
705 END update_user_info;
706
707 PROCEDURE create_supplier_user_account
708 (p_user_name IN VARCHAR2,
709 p_user_email IN VARCHAR2,
710 p_person_party_id IN NUMBER,
711 p_resp_ids IN po_tbl_number,
712 p_resp_app_ids IN po_tbl_number,
713 p_sec_attr_codes IN po_tbl_varchar30,
714 p_sec_attr_numbers IN po_tbl_number,
715 p_password IN VARCHAR2 DEFAULT NULL,
716 x_return_status OUT NOCOPY VARCHAR2,
717 x_msg_count OUT NOCOPY NUMBER,
718 x_msg_data OUT NOCOPY VARCHAR2,
719 x_user_id OUT NOCOPY NUMBER,
720 x_password OUT NOCOPY VARCHAR2
721 )
722 IS
723 l_unencrypted_password VARCHAR2(200);
724 l_testname NUMBER;
725
726 CURSOR l_party_cur IS
727 SELECT party_type, status
728 FROM hz_parties
729 WHERE party_id = p_person_party_id;
730
731 l_party_rec l_party_cur%ROWTYPE;
732
733 l_count INTEGER;
734
735 CURSOR l_fnd_user_cur IS
736 SELECT user_id
737 FROM fnd_user
738 WHERE user_name = p_user_name;
739
740 l_fnd_user_rec l_fnd_user_cur%ROWTYPE;
741 BEGIN
742 SAVEPOINT create_supp_user_account_sp;
743
744 IF p_user_name IS NULL THEN
745 x_return_status := fnd_api.g_ret_sts_error;
746 fnd_message.set_name('POS', 'POS_CRSUSER_USERNAME_NULL');
747 fnd_msg_pub.ADD;
748 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
749 RETURN;
750 END IF;
751
752 OPEN l_fnd_user_cur;
753 FETCH l_fnd_user_cur INTO l_fnd_user_rec;
754 IF l_fnd_user_cur%found THEN
755 CLOSE l_fnd_user_cur;
756 x_return_status := fnd_api.g_ret_sts_error;
757 fnd_message.set_name('POS', 'POS_CRSUSER_USERNAME_EXISTS');
758 fnd_message.set_token('USER_NAME', p_user_name);
759 fnd_msg_pub.ADD;
760 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
761 RETURN;
762 END IF;
763 CLOSE l_fnd_user_cur;
764
765 IF p_user_email IS NULL THEN
766 x_return_status := fnd_api.g_ret_sts_error;
767 fnd_message.set_name('POS', 'POS_CRSUSER_EMAIL_NULL');
768 fnd_msg_pub.ADD;
769 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
770 RETURN;
771 END IF;
772
773 IF p_person_party_id IS NULL THEN
774 x_return_status := fnd_api.g_ret_sts_error;
775 fnd_message.set_name('POS', 'POS_CRSUSER_PERSON_PARTY_NULL');
776 fnd_msg_pub.ADD;
777 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
778 RETURN;
779 END IF;
780
781 OPEN l_party_cur;
782 FETCH l_party_cur INTO l_party_rec;
783 IF l_party_cur%notfound THEN
784 CLOSE l_party_cur;
785 x_return_status := fnd_api.g_ret_sts_error;
786 fnd_message.set_name('POS', 'POS_CRSUSER_BAD_PERSON_PARTYID');
787 fnd_message.set_token('PERSON_PARTY_ID', p_person_party_id);
788 fnd_msg_pub.ADD;
789 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
790 RETURN;
791 END IF;
792 CLOSE l_party_cur;
793
794 IF l_party_rec.status <> 'A' THEN
795 x_return_status := fnd_api.g_ret_sts_error;
796 fnd_message.set_name('POS', 'POS_CRSUSER_PERSON_PARTY_INACT');
797 fnd_message.set_token('PERSON_PARTY_ID', p_person_party_id);
798 fnd_msg_pub.ADD;
799 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
800 RETURN;
801 END IF;
802
803 IF l_party_rec.party_type <> 'PERSON' THEN
804 x_return_status := fnd_api.g_ret_sts_error;
805 fnd_message.set_name('POS', 'POS_CRSUSER_PARTY_NOT_PERSON');
806 fnd_message.set_token('PERSON_PARTY_ID', p_person_party_id);
807 fnd_msg_pub.ADD;
808 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
809 RETURN;
810 END IF;
811
812 IF p_password IS NULL THEN
813 l_unencrypted_password := pos_password_util_pkg.generate_user_pwd();
814 ELSE
815 l_unencrypted_password := p_password;
816 END IF;
817
818 --sso integration, if user exists in oid
819 --and synch is allowed, then no need to pass the password
820 l_testname := FND_USER_PKG.TestUserName(p_user_name);
821 IF (l_testname = FND_USER_PKG.USER_SYNCHED) THEN
822 l_unencrypted_password := NULL;
823 END IF;
824
825 x_user_id :=
826 fnd_user_pkg.createuserid
827 (x_user_name => p_user_name,
828 x_owner => NULL, -- created by current user
829 x_unencrypted_password => l_unencrypted_password,
830 x_description => p_user_name,
831 x_email_address => p_user_email
832 );
833
834 -- set the person_party_id column in fnd_user
835 fnd_user_pkg.updateuserparty
836 (x_user_name => p_user_name,
837 x_owner => NULL,
838 x_person_party_id => p_person_party_id
839 );
840
841 x_password := l_unencrypted_password;
842
843 -- set the user workflow mail preference to HTML
844 fnd_preference.put(upper(p_user_name), 'WF', 'MAILTYPE', 'MAILHTML');
845
846 l_count := p_resp_ids.COUNT;
847 FOR l_index IN 1..l_count LOOP
848 grant_user_resp
849 ( p_user_id => x_user_id,
850 p_resp_id => p_resp_ids(l_index),
851 p_resp_app_id => p_resp_app_ids(l_index),
852 x_return_status => x_return_status,
853 x_msg_count => x_msg_count,
854 x_msg_data => x_msg_data
855 );
856 IF x_return_status IS NULL OR
857 x_return_status <> fnd_api.g_ret_sts_success THEN
858 ROLLBACK TO create_supp_user_account_sp;
859 RETURN;
860 END IF;
861 END LOOP;
862
863 -- assign securing attribute values to the user
864 l_count := p_sec_attr_codes.COUNT;
865 FOR l_index IN 1..l_count LOOP
866 createsecattr
867 ( p_user_id => x_user_id,
868 p_attribute_code => p_sec_attr_codes(l_index),
869 p_app_id => 177,
870 p_number_value => p_sec_attr_numbers(l_index)
871 );
872 END LOOP;
873
874 -- set profile options for external user
875 -- Namely: APPS_SERVLET_AGENT, APPS_WEB_AGENT
876 POS_SUPPLIER_USER_REG_PKG.set_profile_opt_ext_user(x_user_id);
877
878 x_return_status := fnd_api.g_ret_sts_success;
879
880 EXCEPTION
881 WHEN OTHERS THEN
882 ROLLBACK TO create_supp_user_account_sp;
883 x_return_status := fnd_api.g_ret_sts_error;
884 x_msg_count := 1;
885 x_msg_data := Sqlerrm;
886 pos_log.log_sqlerrm('POSADMB', 'in create_supplier_user_account');
887
888 END create_supplier_user_account;
889
890
891 PROCEDURE create_supplier_user_ntf
892 (p_user_name IN VARCHAR2,
893 p_user_email IN VARCHAR2,
894 p_person_party_id IN NUMBER,
895 p_password IN VARCHAR2 DEFAULT NULL,
896 x_return_status OUT NOCOPY VARCHAR2,
897 x_msg_count OUT NOCOPY NUMBER,
898 x_msg_data OUT NOCOPY VARCHAR2,
899 x_user_id OUT NOCOPY NUMBER,
900 x_password OUT NOCOPY VARCHAR2
901 )
902 IS
903 l_resp_ids po_tbl_number;
904 l_resp_app_ids po_tbl_number;
905 l_sec_attr_codes po_tbl_varchar30;
906 l_sec_attr_numbers po_tbl_number;
907 l_enterprise_name hz_parties.party_name%TYPE;
908 lv_exception_msg VARCHAR2(32000);
909 lv_status VARCHAR2(240);
910 l_itemtype wf_items.item_type%TYPE;
911 l_itemkey wf_items.item_key%TYPE;
912 l_process wf_process_activities.process_name%TYPE;
913 l_user_in_oid VARCHAR2(1);
914
915 BEGIN
916 l_resp_ids := po_tbl_number();
917 l_resp_app_ids := po_tbl_number();
918 l_sec_attr_codes := po_tbl_varchar30();
919 l_sec_attr_numbers := po_tbl_number();
920
921 /* Bug 5680160 Start */
922 l_user_in_oid := 'N';
923 if (FND_USER_PKG.TestUserName(p_user_name) = FND_USER_PKG.USER_SYNCHED) then
924 l_user_in_oid := 'Y';
925 end if;
926 /* Bug 5680160 End */
927 create_supplier_user_account
928 (p_user_name => p_user_name,
929 p_user_email => p_user_email,
930 p_person_party_id => p_person_party_id,
931 p_resp_ids => l_resp_ids,
932 p_resp_app_ids => l_resp_app_ids,
933 p_sec_attr_codes => l_sec_attr_codes,
934 p_sec_attr_numbers => l_sec_attr_numbers,
935 p_password => p_password,
936 x_return_status => x_return_status,
937 x_msg_count => x_msg_count,
938 x_msg_data => x_msg_data,
939 x_user_id => x_user_id,
940 x_password => x_password
941 );
942
943 -- Send the email informing the supplier user
944 -- with the username and password information.
945
946 l_process := 'SEND_APPRV_REG_USER_NTF';
947 /* Bug 5680160 Start */
948 IF (l_user_in_oid = 'Y') THEN
949 l_process := 'SEND_APPRV_USER_SSOSYNC_NTF';
950 END IF;
951 /* Bug 5680160 End */
952 l_itemtype := 'POSREGV2';
953 l_itemkey := Substr('POSREGV2_' || x_user_id || '_' || 'cntusr' || '_' ||
954 fnd_crypto.smallrandomnumber(), 0, 240);
955
956 wf_engine.CreateProcess(itemtype => l_itemtype,
957 itemkey => l_itemkey,
958 process => l_process
959 );
960
961 wf_engine.SetItemAttrText (itemtype => l_itemtype,
962 itemkey => l_itemkey,
963 aname => 'LOGON_PAGE_URL',
964 avalue => pos_url_pkg.get_external_login_url
965 );
966
967 wf_engine.SetItemAttrText (itemtype => l_itemtype,
968 itemkey => l_itemkey,
969 aname => 'ASSIGNED_USER_NAME',
970 avalue => Upper(p_user_name)
971 );
972
973 wf_engine.SetItemAttrText (itemtype => l_itemtype,
974 itemkey => l_itemkey,
975 aname => 'FIRST_LOGON_KEY',
976 avalue => x_password
977 );
978
979 wf_engine.SetItemAttrText (itemtype => l_itemtype,
980 itemkey => l_itemkey,
981 aname => 'CONTACT_EMAIL',
982 avalue => p_user_email
983 );
984
985 pos_enterprise_util_pkg.get_enterprise_party_name
986 ( l_enterprise_name, lv_exception_msg, lv_status);
987
988 wf_engine.SetItemAttrText (itemtype => l_itemtype,
989 itemkey => l_itemkey,
990 aname => 'ENTERPRISE_NAME',
991 avalue => l_enterprise_name
992 );
993
994 wf_engine.StartProcess (itemtype => l_itemtype,
995 itemkey => l_itemkey
996 );
997
998
999 END create_supplier_user_ntf;
1000
1001
1002 PROCEDURE create_supplier_user_account
1003 (p_user_name IN VARCHAR2,
1004 p_user_email IN VARCHAR2,
1005 p_person_party_id IN NUMBER,
1006 p_password IN VARCHAR2 DEFAULT NULL,
1007 x_return_status OUT NOCOPY VARCHAR2,
1008 x_msg_count OUT NOCOPY NUMBER,
1009 x_msg_data OUT NOCOPY VARCHAR2,
1010 x_user_id OUT NOCOPY NUMBER,
1011 x_password OUT NOCOPY VARCHAR2
1012 )
1013 IS
1014 l_resp_ids po_tbl_number;
1015 l_resp_app_ids po_tbl_number;
1016 l_sec_attr_codes po_tbl_varchar30;
1017 l_sec_attr_numbers po_tbl_number;
1018 BEGIN
1019 l_resp_ids := po_tbl_number();
1020 l_resp_app_ids := po_tbl_number();
1021 l_sec_attr_codes := po_tbl_varchar30();
1022 l_sec_attr_numbers := po_tbl_number();
1023
1024 create_supplier_user_account
1025 (p_user_name => p_user_name,
1026 p_user_email => p_user_email,
1027 p_person_party_id => p_person_party_id,
1028 p_resp_ids => l_resp_ids,
1029 p_resp_app_ids => l_resp_app_ids,
1030 p_sec_attr_codes => l_sec_attr_codes,
1031 p_sec_attr_numbers => l_sec_attr_numbers,
1032 p_password => p_password,
1033 x_return_status => x_return_status,
1034 x_msg_count => x_msg_count,
1035 x_msg_data => x_msg_data,
1036 x_user_id => x_user_id,
1037 x_password => x_password
1038 );
1039
1040 END create_supplier_user_account;
1041
1042 PROCEDURE assign_vendor_reg_def_resp
1043 (p_user_id IN NUMBER,
1044 p_vendor_id IN NUMBER,
1045 p_pon_def_also IN VARCHAR2,
1046 x_return_status OUT NOCOPY VARCHAR2,
1047 x_msg_count OUT NOCOPY NUMBER,
1048 x_msg_data OUT NOCOPY VARCHAR2
1049 )
1050 IS
1051 l_resp_profile fnd_profile_option_values.profile_option_value%TYPE;
1052 l_instr_index NUMBER;
1053 l_resp_key fnd_responsibility.responsibility_key%TYPE;
1054 l_resp_app_id NUMBER;
1055 l_step NUMBER;
1056 BEGIN
1057 x_return_status := fnd_api.g_ret_sts_success;
1058 l_step := 1;
1059 fnd_profile.get('POS_DEFAULT_SUP_REG_RESP', l_resp_profile);
1060
1061 IF l_resp_profile IS NOT NULL THEN
1062 l_instr_index := Instr(l_resp_profile, ':');
1063 l_resp_key := Substr(l_resp_profile, 0, l_instr_index - 1);
1064 l_resp_app_id := To_number(SUBSTR(l_resp_profile, l_instr_index + 1));
1065
1066 grant_user_resp
1067 (p_user_id => p_user_id,
1068 p_resp_key => l_resp_key,
1069 p_resp_app_id => l_resp_app_id,
1070 x_return_status => x_return_status,
1071 x_msg_count => x_msg_count,
1072 x_msg_data => x_msg_data
1073 );
1074
1075 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1076 RETURN;
1077 END IF;
1078 END IF;
1079
1080 -- bug 5415703
1081 IF p_pon_def_also IS NOT NULL AND p_pon_def_also = 'Y' THEN
1082
1083 l_step := 2;
1084 l_resp_profile := NULL;
1085
1086 fnd_profile.get('PON_DEFAULT_EXT_USER_RESP', l_resp_profile);
1087
1088 IF l_resp_profile IS NOT NULL THEN
1089 l_resp_key := l_resp_profile;
1090
1091 SELECT application_id INTO l_resp_app_id
1092 FROM fnd_application WHERE application_short_name = 'PON';
1093
1094 grant_user_resp
1095 (p_user_id => p_user_id,
1096 p_resp_key => l_resp_key,
1097 p_resp_app_id => l_resp_app_id,
1098 x_return_status => x_return_status,
1099 x_msg_count => x_msg_count,
1100 x_msg_data => x_msg_data
1101 );
1102
1103 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1104 RETURN;
1105 END IF;
1106 END IF;
1107 END IF;
1108 EXCEPTION
1109 WHEN OTHERS THEN
1110 x_return_status := fnd_api.g_ret_sts_error;
1111 x_msg_count := 1;
1112 x_msg_data := Sqlerrm;
1113 pos_log.log_sqlerrm('POSADMB', 'in assign_vendor_reg_def_resp, step ' || l_step );
1114
1115 END assign_vendor_reg_def_resp;
1116
1117
1118 /* Added following procedure for Business Classification Recertification ER
1119 7489217 */
1120 procedure add_certntf_subscription
1121 ( p_user_id IN NUMBER
1122 , x_status OUT NOCOPY VARCHAR2
1123 , x_exception_msg OUT NOCOPY VARCHAR2
1124 ) IS
1125
1126 cursor c1 is
1127 select count(*)
1128 from pos_spmntf_subscription
1129 where user_id = p_user_id and
1130 event_type = 'SUPP_BUS_CLASS_RECERT_NTF';
1131
1132 l_subscr_count number;
1133 BEGIN
1134 -- Insert new record in the spm notification events table for the
1135 -- business classification recert notifications
1136
1137
1138 x_status := fnd_api.g_ret_sts_success;
1139 open c1;
1140 fetch c1 into l_subscr_count;
1141 close c1;
1142
1143 if (l_subscr_count = 0 ) then
1144 insert into pos_spmntf_subscription
1145 (subscription_id,
1146 created_by,
1147 creation_date,
1148 last_updated_by,
1149 last_update_date,
1150 last_update_login,
1151 event_type,
1152 user_id)
1153 values
1154 (POS_SPMNTF_SUBSCRIPTION_S.nextval,
1155 fnd_global.user_id,
1156 sysdate,
1157 fnd_global.user_id,
1158 sysdate,
1159 fnd_global.login_id,
1160 'SUPP_BUS_CLASS_RECERT_NTF',
1161 p_user_id);
1162 commit;
1163 x_status := fnd_api.g_ret_sts_success;
1164
1165 end if;
1166 EXCEPTION
1167 WHEN OTHERS THEN
1168 x_status := fnd_api.g_ret_sts_error;
1169 x_exception_msg := 'Error creating notification subscription'||Sqlerrm;
1170 pos_log.log_sqlerrm('POSADMB', x_exception_msg);
1171
1172 END add_certntf_subscription;
1173
1174 procedure remove_certntf_subscription
1175 ( p_user_id IN NUMBER
1176 , x_status OUT NOCOPY VARCHAR2
1177 , x_exception_msg OUT NOCOPY VARCHAR2
1178 ) IS
1179
1180 cursor c1 is
1181 select count(*)
1182 from pos_spmntf_subscription
1183 where user_id = p_user_id and
1184 event_type = 'SUPP_BUS_CLASS_RECERT_NTF';
1185
1186 l_subscr_count number := 0;
1187
1188
1189 BEGIN
1190 x_status := fnd_api.g_ret_sts_success;
1191
1192 open c1;
1193 fetch c1 into l_subscr_count;
1194 close c1;
1195 -- Delete the Notification subscription record
1196
1197 if (l_subscr_count > 0 ) then
1198 delete from pos_spmntf_subscription
1199 where event_type = 'SUPP_BUS_CLASS_RECERT_NTF'
1200 and user_id = p_user_id;
1201 commit;
1202 x_status := fnd_api.g_ret_sts_success;
1203 end if;
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 x_status := fnd_api.g_ret_sts_error;
1207 x_exception_msg := 'Error removing notification subscription'||Sqlerrm;
1208 pos_log.log_sqlerrm('POSADMB', x_exception_msg);
1209
1210 END remove_certntf_subscription;
1211
1212 procedure get_certntf_subscription
1213 ( p_user_id IN NUMBER
1214 , x_subscr_exists OUT NOCOPY VARCHAR2
1215 ) IS
1216
1217 BEGIN
1218
1219 select 'Y'
1220 into x_subscr_exists
1221 from pos_spmntf_subscription
1222 where event_type = 'SUPP_BUS_CLASS_RECERT_NTF'
1223 and user_id = p_user_id;
1224
1225 EXCEPTION
1226 WHEN NO_DATA_FOUND THEN
1227 x_subscr_exists := 'N';
1228 END get_certntf_subscription;
1229
1230
1231 END pos_user_admin_pkg;