[Home] [Help]
PACKAGE BODY: APPS.POS_USER_ADMIN_PKG
Source
1 PACKAGE BODY pos_user_admin_pkg as
2 /*$Header: POSADMB.pls 120.35.12020000.2 2013/02/09 13:56:22 hvutukur 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 vl_password_lifespan_days fnd_profile_option_values.profile_option_value%TYPE DEFAULT NULL;
726 l_password_lifespan_days NUMBER DEFAULT NULL;
727 event_id Number;
728 CURSOR l_party_cur IS
729 SELECT party_type, status
730 FROM hz_parties
731 WHERE party_id = p_person_party_id;
732
733 l_party_rec l_party_cur%ROWTYPE;
734
735 l_count INTEGER;
736
737 CURSOR l_fnd_user_cur IS
738 SELECT user_id
739 FROM fnd_user
740 WHERE user_name = p_user_name;
741
742 l_fnd_user_rec l_fnd_user_cur%ROWTYPE;
743 BEGIN
744 SAVEPOINT create_supp_user_account_sp;
745
746 IF p_user_name IS NULL THEN
747 x_return_status := fnd_api.g_ret_sts_error;
748 fnd_message.set_name('POS', 'POS_CRSUSER_USERNAME_NULL');
749 fnd_msg_pub.ADD;
750 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
751 RETURN;
752 END IF;
753
754 OPEN l_fnd_user_cur;
755 FETCH l_fnd_user_cur INTO l_fnd_user_rec;
756 IF l_fnd_user_cur%found THEN
757 CLOSE l_fnd_user_cur;
758 x_return_status := fnd_api.g_ret_sts_error;
759 fnd_message.set_name('POS', 'POS_CRSUSER_USERNAME_EXISTS');
760 fnd_message.set_token('USER_NAME', p_user_name);
761 fnd_msg_pub.ADD;
762 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
763 RETURN;
764 END IF;
765 CLOSE l_fnd_user_cur;
766
767 IF p_user_email IS NULL THEN
768 x_return_status := fnd_api.g_ret_sts_error;
769 fnd_message.set_name('POS', 'POS_CRSUSER_EMAIL_NULL');
770 fnd_msg_pub.ADD;
771 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
772 RETURN;
773 END IF;
774
775 IF p_person_party_id IS NULL THEN
776 x_return_status := fnd_api.g_ret_sts_error;
777 fnd_message.set_name('POS', 'POS_CRSUSER_PERSON_PARTY_NULL');
778 fnd_msg_pub.ADD;
779 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
780 RETURN;
781 END IF;
782
783 OPEN l_party_cur;
784 FETCH l_party_cur INTO l_party_rec;
785 IF l_party_cur%notfound THEN
786 CLOSE l_party_cur;
787 x_return_status := fnd_api.g_ret_sts_error;
788 fnd_message.set_name('POS', 'POS_CRSUSER_BAD_PERSON_PARTYID');
789 fnd_message.set_token('PERSON_PARTY_ID', p_person_party_id);
790 fnd_msg_pub.ADD;
791 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
792 RETURN;
793 END IF;
794 CLOSE l_party_cur;
795
796 IF l_party_rec.status <> 'A' THEN
797 x_return_status := fnd_api.g_ret_sts_error;
798 fnd_message.set_name('POS', 'POS_CRSUSER_PERSON_PARTY_INACT');
799 fnd_message.set_token('PERSON_PARTY_ID', p_person_party_id);
800 fnd_msg_pub.ADD;
801 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
802 RETURN;
803 END IF;
804
805 IF l_party_rec.party_type <> 'PERSON' THEN
806 x_return_status := fnd_api.g_ret_sts_error;
807 fnd_message.set_name('POS', 'POS_CRSUSER_PARTY_NOT_PERSON');
808 fnd_message.set_token('PERSON_PARTY_ID', p_person_party_id);
809 fnd_msg_pub.ADD;
810 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
811 RETURN;
812 END IF;
813
814 IF p_password IS NULL THEN
815 l_unencrypted_password := pos_password_util_pkg.generate_user_pwd();
816 ELSE
817 l_unencrypted_password := p_password;
818 END IF;
819
820 --sso integration, if user exists in oid
821 --and synch is allowed, then no need to pass the password
822 l_testname := FND_USER_PKG.TestUserName(p_user_name);
823 IF (l_testname = FND_USER_PKG.USER_SYNCHED) THEN
824 l_unencrypted_password := NULL;
825 END IF;
826
827 -- Bug: 14292251
828 -- Fteching the profile value
829 FND_PROFILE.GET('POS_SUPPLIER_USER_PASSWORD_EXP_DAYS',vl_password_lifespan_days);
830
831 IF (vl_password_lifespan_days IS NOT NULL OR vl_password_lifespan_days<>'') THEN
832 BEGIN
833 -- Converting fetched value to number
834 l_password_lifespan_days := to_number(vl_password_lifespan_days);
835 EXCEPTION
836 WHEN VALUE_ERROR THEN
837 --Throwing error if the conversion to number didn't happen due to invalid characters
838 --Only VALUE_ERROR can occur while conversion
839 x_return_status := fnd_api.g_ret_sts_error;
840 fnd_message.set_name('POS', 'POS_SUPP_USER_PWD_EXP_ERROR');
841 fnd_message.set_token('POS_PROFILE_OPTION_NAME', 'POS: Supplier User Account Password Expiration Days');
842 x_msg_data := fnd_message.get;
843 raise_application_error(-20001,x_msg_data,false);
844 RETURN;
845 END;
846 END IF;
847
848 x_user_id :=
849 fnd_user_pkg.createuserid
850 (x_user_name => p_user_name,
851 x_owner => NULL, -- created by current user
852 x_unencrypted_password => l_unencrypted_password,
853 x_description => p_user_name,
854 x_email_address => p_user_email,
855 x_password_lifespan_days => l_password_lifespan_days
856 );
857
858 -- set the person_party_id column in fnd_user
859 fnd_user_pkg.updateuserparty
860 (x_user_name => p_user_name,
861 x_owner => NULL,
862 x_person_party_id => p_person_party_id
863 );
864
865 x_password := l_unencrypted_password;
866
867 -- set the user workflow mail preference to HTML
868 fnd_preference.put(upper(p_user_name), 'WF', 'MAILTYPE', 'MAILHTML');
869
870 l_count := p_resp_ids.COUNT;
871 FOR l_index IN 1..l_count LOOP
872 grant_user_resp
873 ( p_user_id => x_user_id,
874 p_resp_id => p_resp_ids(l_index),
875 p_resp_app_id => p_resp_app_ids(l_index),
876 x_return_status => x_return_status,
877 x_msg_count => x_msg_count,
878 x_msg_data => x_msg_data
879 );
880 IF x_return_status IS NULL OR
881 x_return_status <> fnd_api.g_ret_sts_success THEN
882 ROLLBACK TO create_supp_user_account_sp;
883 RETURN;
884 END IF;
885 END LOOP;
886
887 -- assign securing attribute values to the user
888 l_count := p_sec_attr_codes.COUNT;
889 FOR l_index IN 1..l_count LOOP
890 createsecattr
891 ( p_user_id => x_user_id,
892 p_attribute_code => p_sec_attr_codes(l_index),
893 p_app_id => 177,
894 p_number_value => p_sec_attr_numbers(l_index)
895 );
896 END LOOP;
897
898 -- set profile options for external user
899 -- Namely: APPS_SERVLET_AGENT, APPS_WEB_AGENT
900 POS_SUPPLIER_USER_REG_PKG.set_profile_opt_ext_user(x_user_id);
901
902 x_return_status := fnd_api.g_ret_sts_success;
903
904 /* Begin Supplier Hub - Supplier Data Publication */
905 /* Raise Supplier User Creation event*/
906 event_id:= pos_appr_rej_supp_event_raise.raise_appr_rej_supp_event('oracle.apps.pos.supplier.approvesupplieruser', p_person_party_id, x_user_id);
907
908 /* End Supplier Hub - Supplier Data Publication */
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 ROLLBACK TO create_supp_user_account_sp;
913 x_return_status := fnd_api.g_ret_sts_error;
914 x_msg_count := 1;
915 x_msg_data := Sqlerrm;
916 pos_log.log_sqlerrm('POSADMB', 'in create_supplier_user_account');
917
918 END create_supplier_user_account;
919
920 -- code added for bug 7699191, to send the admin email id in supplier user notification mail
921 FUNCTION get_contact_email RETURN VARCHAR2
922 IS
923 CURSOR l_cur IS
924 SELECT email_address
925 FROM fnd_user
926 WHERE user_id = fnd_global.user_id;
927
928 l_email fnd_user.email_address%TYPE;
929 BEGIN
930 OPEN l_cur;
931 FETCH l_cur INTO l_email;
932 CLOSE l_cur;
933 RETURN l_email;
934 END get_contact_email;
935 -- code added for bug 7699191, to send the admin email id in supplier user notification mail
936
937 PROCEDURE create_supplier_user_ntf
938 (p_user_name IN VARCHAR2,
939 p_user_email IN VARCHAR2,
940 p_person_party_id IN NUMBER,
941 p_password IN VARCHAR2 DEFAULT NULL,
942 x_return_status OUT NOCOPY VARCHAR2,
943 x_msg_count OUT NOCOPY NUMBER,
944 x_msg_data OUT NOCOPY VARCHAR2,
945 x_user_id OUT NOCOPY NUMBER,
946 x_password OUT NOCOPY VARCHAR2
947 )
948 IS
949 l_resp_ids po_tbl_number;
950 l_resp_app_ids po_tbl_number;
951 l_sec_attr_codes po_tbl_varchar30;
952 l_sec_attr_numbers po_tbl_number;
953 l_enterprise_name hz_parties.party_name%TYPE;
954 lv_exception_msg VARCHAR2(32000);
955 lv_status VARCHAR2(240);
956 l_itemtype wf_items.item_type%TYPE;
957 l_itemkey wf_items.item_key%TYPE;
958 l_process wf_process_activities.process_name%TYPE;
959 l_user_in_oid VARCHAR2(1);
960
961 BEGIN
962 l_resp_ids := po_tbl_number();
963 l_resp_app_ids := po_tbl_number();
964 l_sec_attr_codes := po_tbl_varchar30();
965 l_sec_attr_numbers := po_tbl_number();
966
967 /* Bug 5680160 Start */
968 l_user_in_oid := 'N';
969 if (FND_USER_PKG.TestUserName(p_user_name) = FND_USER_PKG.USER_SYNCHED) then
970 l_user_in_oid := 'Y';
971 end if;
972 /* Bug 5680160 End */
973 create_supplier_user_account
974 (p_user_name => p_user_name,
975 p_user_email => p_user_email,
976 p_person_party_id => p_person_party_id,
977 p_resp_ids => l_resp_ids,
978 p_resp_app_ids => l_resp_app_ids,
979 p_sec_attr_codes => l_sec_attr_codes,
980 p_sec_attr_numbers => l_sec_attr_numbers,
981 p_password => p_password,
982 x_return_status => x_return_status,
983 x_msg_count => x_msg_count,
984 x_msg_data => x_msg_data,
985 x_user_id => x_user_id,
986 x_password => x_password
987 );
988
989 -- Send the email informing the supplier user
990 -- with the username and password information.
991
992 l_process := 'SEND_APPRV_REG_USER_NTF';
993 /* Bug 5680160 Start */
994 IF (l_user_in_oid = 'Y') THEN
995 l_process := 'SEND_APPRV_USER_SSOSYNC_NTF';
996 END IF;
997 /* Bug 5680160 End */
998 l_itemtype := 'POSREGV2';
999 l_itemkey := Substr('POSREGV2_' || x_user_id || '_' || 'cntusr' || '_' ||
1000 fnd_crypto.smallrandomnumber(), 0, 240);
1001
1002 wf_engine.CreateProcess(itemtype => l_itemtype,
1003 itemkey => l_itemkey,
1004 process => l_process
1005 );
1006
1007 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1008 itemkey => l_itemkey,
1009 aname => 'LOGON_PAGE_URL',
1010 avalue => pos_url_pkg.get_external_login_url
1011 );
1012
1013 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1014 itemkey => l_itemkey,
1015 aname => 'ASSIGNED_USER_NAME',
1016 avalue => Upper(p_user_name)
1017 );
1018
1019 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1020 itemkey => l_itemkey,
1021 aname => 'FIRST_LOGON_KEY',
1022 avalue => x_password
1023 );
1024
1025 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1026 itemkey => l_itemkey,
1027 aname => 'CONTACT_EMAIL',
1028 avalue => get_contact_email
1029 );
1030
1031 pos_enterprise_util_pkg.get_enterprise_party_name
1032 ( l_enterprise_name, lv_exception_msg, lv_status);
1033
1034 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1035 itemkey => l_itemkey,
1036 aname => 'ENTERPRISE_NAME',
1037 avalue => l_enterprise_name
1038 );
1039
1040 -- Bug 8325979 - Following attributes have been replaced with FND Messages
1041
1042 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1043 itemkey => l_itemkey,
1044 aname => 'POS_APPRV_REG_USER_SUBJECT',
1045 avalue => GET_SUPP_USER_ACCNT_SUBJECT(l_enterprise_name));
1046
1047
1048 wf_engine.SetItemAttrText (itemtype => l_itemtype, itemkey => l_itemkey,
1049 aname => 'POS_APPRV_REG_USER_BODY',
1050 avalue => 'PLSQLCLOB:pos_user_admin_pkg.GENERATE_SUPP_USER_ACCNT_BODY/'||l_itemtype ||':' ||l_itemkey
1051 );
1052
1053
1054 wf_engine.StartProcess (itemtype => l_itemtype,
1055 itemkey => l_itemkey
1056 );
1057
1058
1059 END create_supplier_user_ntf;
1060
1061
1062 PROCEDURE create_supplier_user_account
1063 (p_user_name IN VARCHAR2,
1064 p_user_email IN VARCHAR2,
1065 p_person_party_id IN NUMBER,
1066 p_password IN VARCHAR2 DEFAULT NULL,
1067 x_return_status OUT NOCOPY VARCHAR2,
1068 x_msg_count OUT NOCOPY NUMBER,
1069 x_msg_data OUT NOCOPY VARCHAR2,
1070 x_user_id OUT NOCOPY NUMBER,
1071 x_password OUT NOCOPY VARCHAR2
1072 )
1073 IS
1074 l_resp_ids po_tbl_number;
1075 l_resp_app_ids po_tbl_number;
1076 l_sec_attr_codes po_tbl_varchar30;
1077 l_sec_attr_numbers po_tbl_number;
1078 BEGIN
1079 l_resp_ids := po_tbl_number();
1080 l_resp_app_ids := po_tbl_number();
1081 l_sec_attr_codes := po_tbl_varchar30();
1082 l_sec_attr_numbers := po_tbl_number();
1083
1084 create_supplier_user_account
1085 (p_user_name => p_user_name,
1086 p_user_email => p_user_email,
1087 p_person_party_id => p_person_party_id,
1088 p_resp_ids => l_resp_ids,
1089 p_resp_app_ids => l_resp_app_ids,
1090 p_sec_attr_codes => l_sec_attr_codes,
1091 p_sec_attr_numbers => l_sec_attr_numbers,
1092 p_password => p_password,
1093 x_return_status => x_return_status,
1094 x_msg_count => x_msg_count,
1095 x_msg_data => x_msg_data,
1096 x_user_id => x_user_id,
1097 x_password => x_password
1098 );
1099
1100 END create_supplier_user_account;
1101
1102 PROCEDURE assign_vendor_reg_def_resp
1103 (p_user_id IN NUMBER,
1104 p_vendor_id IN NUMBER,
1105 p_pon_def_also IN VARCHAR2,
1106 x_return_status OUT NOCOPY VARCHAR2,
1107 x_msg_count OUT NOCOPY NUMBER,
1108 x_msg_data OUT NOCOPY VARCHAR2
1109 )
1110 IS
1111 l_resp_profile fnd_profile_option_values.profile_option_value%TYPE;
1112 l_instr_index NUMBER;
1113 l_resp_key fnd_responsibility.responsibility_key%TYPE;
1114 l_resp_app_id NUMBER;
1115 l_step NUMBER;
1116 BEGIN
1117 x_return_status := fnd_api.g_ret_sts_success;
1118 l_step := 1;
1119 fnd_profile.get('POS_DEFAULT_SUP_REG_RESP', l_resp_profile);
1120
1121 IF l_resp_profile IS NOT NULL THEN
1122 l_instr_index := Instr(l_resp_profile, ':');
1123 l_resp_key := Substr(l_resp_profile, 0, l_instr_index - 1);
1124 l_resp_app_id := To_number(SUBSTR(l_resp_profile, l_instr_index + 1));
1125
1126 grant_user_resp
1127 (p_user_id => p_user_id,
1128 p_resp_key => l_resp_key,
1129 p_resp_app_id => l_resp_app_id,
1130 x_return_status => x_return_status,
1131 x_msg_count => x_msg_count,
1132 x_msg_data => x_msg_data
1133 );
1134
1135 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1136 RETURN;
1137 END IF;
1138 END IF;
1139
1140 -- bug 5415703
1141 IF p_pon_def_also IS NOT NULL AND p_pon_def_also = 'Y' THEN
1142
1143 l_step := 2;
1144 l_resp_profile := NULL;
1145
1146 fnd_profile.get('PON_DEFAULT_EXT_USER_RESP', l_resp_profile);
1147
1148 IF l_resp_profile IS NOT NULL THEN
1149 l_resp_key := l_resp_profile;
1150
1151 SELECT application_id INTO l_resp_app_id
1152 FROM fnd_application WHERE application_short_name = 'PON';
1153
1154 grant_user_resp
1155 (p_user_id => p_user_id,
1156 p_resp_key => l_resp_key,
1157 p_resp_app_id => l_resp_app_id,
1158 x_return_status => x_return_status,
1159 x_msg_count => x_msg_count,
1160 x_msg_data => x_msg_data
1161 );
1162
1163 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1164 RETURN;
1165 END IF;
1166 END IF;
1167 END IF;
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 x_return_status := fnd_api.g_ret_sts_error;
1171 x_msg_count := 1;
1172 x_msg_data := Sqlerrm;
1173 pos_log.log_sqlerrm('POSADMB', 'in assign_vendor_reg_def_resp, step ' || l_step );
1174
1175 END assign_vendor_reg_def_resp;
1176
1177
1178 /* Added following procedure for Business Classification Recertification ER
1179 7489217 */
1180 procedure add_certntf_subscription
1181 ( p_user_id IN NUMBER
1182 , x_status OUT NOCOPY VARCHAR2
1183 , x_exception_msg OUT NOCOPY VARCHAR2
1184 ) IS
1185
1186 cursor c1 is
1187 select count(*)
1188 from pos_spmntf_subscription
1189 where user_id = p_user_id and
1190 event_type = 'SUPP_BUS_CLASS_RECERT_NTF';
1191
1192 l_subscr_count number;
1193 BEGIN
1194 -- Insert new record in the spm notification events table for the
1195 -- business classification recert notifications
1196
1197
1198 x_status := fnd_api.g_ret_sts_success;
1199 open c1;
1200 fetch c1 into l_subscr_count;
1201 close c1;
1202
1203 if (l_subscr_count = 0 ) then
1204 insert into pos_spmntf_subscription
1205 (subscription_id,
1206 created_by,
1207 creation_date,
1208 last_updated_by,
1209 last_update_date,
1210 last_update_login,
1211 event_type,
1212 user_id)
1213 values
1214 (POS_SPMNTF_SUBSCRIPTION_S.nextval,
1215 fnd_global.user_id,
1216 sysdate,
1217 fnd_global.user_id,
1218 sysdate,
1219 fnd_global.login_id,
1220 'SUPP_BUS_CLASS_RECERT_NTF',
1221 p_user_id);
1222 commit;
1223 x_status := fnd_api.g_ret_sts_success;
1224
1225 end if;
1226 EXCEPTION
1227 WHEN OTHERS THEN
1228 x_status := fnd_api.g_ret_sts_error;
1229 x_exception_msg := 'Error creating notification subscription'||Sqlerrm;
1230 pos_log.log_sqlerrm('POSADMB', x_exception_msg);
1231
1232 END add_certntf_subscription;
1233
1234 procedure remove_certntf_subscription
1235 ( p_user_id IN NUMBER
1236 , x_status OUT NOCOPY VARCHAR2
1237 , x_exception_msg OUT NOCOPY VARCHAR2
1238 ) IS
1239
1240 cursor c1 is
1241 select count(*)
1242 from pos_spmntf_subscription
1243 where user_id = p_user_id and
1244 event_type = 'SUPP_BUS_CLASS_RECERT_NTF';
1245
1246 l_subscr_count number := 0;
1247
1248
1249 BEGIN
1250 x_status := fnd_api.g_ret_sts_success;
1251
1252 open c1;
1253 fetch c1 into l_subscr_count;
1254 close c1;
1255 -- Delete the Notification subscription record
1256
1257 if (l_subscr_count > 0 ) then
1258 delete from pos_spmntf_subscription
1259 where event_type = 'SUPP_BUS_CLASS_RECERT_NTF'
1260 and user_id = p_user_id;
1261 commit;
1262 x_status := fnd_api.g_ret_sts_success;
1263 end if;
1264 EXCEPTION
1265 WHEN OTHERS THEN
1266 x_status := fnd_api.g_ret_sts_error;
1267 x_exception_msg := 'Error removing notification subscription'||Sqlerrm;
1268 pos_log.log_sqlerrm('POSADMB', x_exception_msg);
1269
1270 END remove_certntf_subscription;
1271
1272 procedure get_certntf_subscription
1273 ( p_user_id IN NUMBER
1274 , x_subscr_exists OUT NOCOPY VARCHAR2
1275 ) IS
1276
1277 BEGIN
1278
1279 select 'Y'
1280 into x_subscr_exists
1281 from pos_spmntf_subscription
1282 where event_type = 'SUPP_BUS_CLASS_RECERT_NTF'
1283 and user_id = p_user_id;
1284
1285 EXCEPTION
1286 WHEN NO_DATA_FOUND THEN
1287 x_subscr_exists := 'N';
1288 END get_certntf_subscription;
1289
1290 -------------------------------------------------------------------------------
1291 --Start of Comments
1292 --Name: GET_SUPP_USER_ACCNT_SUBJECT
1293 --Type:
1294 -- Function
1295 --Function:
1296 -- It returns the tokens replaced FND message to Notification Message Subject
1297 --Function Usage:
1298 -- This function is used to replace the workflow message subject by FND Message & its tokens
1299 --Logic Implemented:
1300 -- The FND Message Name 'POS_APPRV_REG_USER_SUBJECT' will be replaced with
1301 -- corresponding Message Text and tokens inside the Message Text also be replaced.
1302 -- Then, replaced FND message will be return to the corresponding attribute
1303 --Parameters:
1304 -- Enterprise Name
1305 --IN:
1306 -- Enterprise Name
1307 --OUT:
1308 -- l_document
1309 --Bug Number for reference:
1310 -- 8325979
1311 --End of Comments
1312 ------------------------------------------------------------------------------
1313
1314 FUNCTION GET_SUPP_USER_ACCNT_SUBJECT(p_enterprise_name IN VARCHAR2)
1315 RETURN VARCHAR2 IS
1316 l_document VARCHAR2(32000);
1317
1318 BEGIN
1319
1320 fnd_message.set_name('POS','POS_APPRV_REG_USER_SUBJECT');
1321 fnd_message.set_token('ENTERPRISE_NAME', p_enterprise_name);
1322 l_document := fnd_message.get;
1323 RETURN l_document;
1324 END GET_SUPP_USER_ACCNT_SUBJECT;
1325
1326 -------------------------------------------------------------------------------
1327 --Start of Comments
1328 --Name: GENERATE_SUPP_USER_ACCNT_BODY
1329 --Type:
1330 -- Procedure
1331 --Procedure:
1332 -- It returns the tokens replaced FND message to Notification Message Body
1333 --Procedure Usage:
1334 -- It is being used to replace the workflow message Body by FND Message & its tokens
1335 --Logic Implemented:
1336 -- For HTML Body:
1337 -- The FND Message Name 'POS_APPRV_REG_USER_HTML_BODY' will be replaced with
1338 -- corresponding Message Text and tokens inside the Message Text also be replaced.
1339 -- Then, replaced FND message will be return to the corresponding attribute
1340 -- For TEXT Body:
1341 -- The FND Message Name 'POS_APPRV_REG_USER_TEXT_BODY' will be replaced with
1342 -- corresponding Message Text and tokens inside the Message Text also be replaced.
1343 -- Then, replaced FND message will be return to the corresponding attribute
1344 --Parameters:
1345 -- document_id
1346 --IN:
1347 -- document_id
1348 --OUT:
1349 -- document
1350 --Bug Number for reference:
1351 -- 8325979
1352 --End of Comments
1353 ------------------------------------------------------------------------------
1354
1355 PROCEDURE GENERATE_SUPP_USER_ACCNT_BODY(p_document_id IN VARCHAR2,
1356 display_type IN VARCHAR2,
1357 document IN OUT NOCOPY CLOB,
1358 document_type IN OUT NOCOPY VARCHAR2)
1359 IS
1360
1361 NL VARCHAR2(1) := fnd_global.newline;
1362 l_document VARCHAR2(32000) := '';
1363 l_note VARCHAR2(32000) := '';
1364 l_enterprisename VARCHAR2(1000) := '';
1365 l_url VARCHAR2(3000) := '';
1366 l_email VARCHAR2(1000) := '';
1367 l_username VARCHAR2(500) := '';
1368 l_password VARCHAR2(100) := '';
1369 l_disp_type VARCHAR2(20) := 'text/plain';
1370 l_item_type wf_items.item_type%TYPE;
1371 l_item_key wf_items.item_key%TYPE;
1372
1373 BEGIN
1374
1375 l_item_type := substr(p_document_id, 1, instr(p_document_id, ':') - 1);
1376 l_item_key := substr(p_document_id, instr(p_document_id, ':') + 1, length(p_document_id));
1377
1378 l_enterprisename := wf_engine.GetItemAttrText (itemtype => l_item_type,
1379 itemkey => l_item_key,
1380 aname => 'ENTERPRISE_NAME');
1381 l_url := wf_engine.GetItemAttrText (itemtype => l_item_type,
1382 itemkey => l_item_key,
1383 aname => 'LOGON_PAGE_URL');
1384 l_username := wf_engine.GetItemAttrText (itemtype => l_item_type,
1385 itemkey => l_item_key,
1386 aname => 'ASSIGNED_USER_NAME');
1387 l_password := wf_engine.GetItemAttrText (itemtype => l_item_type,
1388 itemkey => l_item_key,
1389 aname => 'FIRST_LOGON_KEY');
1390 l_email := wf_engine.GetItemAttrText (itemtype => l_item_type,
1391 itemkey => l_item_key,
1392 aname => 'CONTACT_EMAIL');
1393 l_note :='';
1394
1395 IF display_type = 'text/html' THEN
1396 l_disp_type:= display_type;
1397 fnd_message.set_name('POS','POS_APPRV_REG_USER_HTML_BODY');
1398 fnd_message.set_token('ENTERPRISE_NAME',l_enterprisename);
1399 fnd_message.set_token('LOGON_PAGE_URL',l_url);
1400 fnd_message.set_token('ASSIGNED_USER_NAME',l_username);
1401 fnd_message.set_token('FIRST_LOGON_KEY',l_password);
1402 fnd_message.set_token('CONTACT_EMAIL',l_email);
1403 fnd_message.set_token('NOTE',l_note);
1404 l_document := l_document || NL || NL || fnd_message.get;
1405 WF_NOTIFICATION.WriteToClob(document, l_document);
1406
1407 ELSE
1408 l_disp_type:= display_type;
1409 fnd_message.set_name('POS','POS_APPRV_REG_USER_TEXT_BODY');
1410 fnd_message.set_token('ENTERPRISE_NAME',l_enterprisename);
1411 fnd_message.set_token('LOGON_PAGE_URL',l_url);
1412 fnd_message.set_token('ASSIGNED_USER_NAME',l_username);
1413 fnd_message.set_token('FIRST_LOGON_KEY',l_password);
1414 fnd_message.set_token('CONTACT_EMAIL',l_email);
1415 fnd_message.set_token('NOTE',l_note);
1416 l_document := l_document || NL || NL || fnd_message.get;
1417 WF_NOTIFICATION.WriteToClob(document, l_document);
1418
1419 END IF;
1420
1421 EXCEPTION
1422 WHEN OTHERS THEN
1423 RAISE;
1424 END GENERATE_SUPP_USER_ACCNT_BODY;
1425
1426
1427
1428 END pos_user_admin_pkg;