[Home] [Help]
PACKAGE BODY: APPS.POS_SUPPLIER_USER_REG_PKG
Source
1 PACKAGE BODY POS_SUPPLIER_USER_REG_PKG AS
2 /* $Header: POSUREGB.pls 120.15.12020000.2 2013/02/09 14:23:53 hvutukur ship $ */
3
4 g_log_module_name CONSTANT VARCHAR2(30) := 'POS_SUPPLIER_USER_REG_PKG';
5
6 -- the invitation response page URL
7 POS_INV_REPLY_PAGE CONSTANT VARCHAR2(4000) := 'OA_HTML/jsp/pos/registration/RegistrationReply.jsp?registrationKey=';
8
9 -- the user approval page URL
10 POS_APPROVAL_PAGE CONSTANT VARCHAR2(4000) := 'OA_HTML/OA.jsp?akRegionCode=POS_APPROVE_MAIN_RGN&akRegionApplicationId=177®istrationKey=';
11
12 TYPE g_refcur IS REF CURSOR;
13
14 FUNCTION decrypt
15 (key IN VARCHAR2,
16 value IN VARCHAR2
17 )
18 RETURN VARCHAR2 AS language java name 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
19
20 /*----------------------------------------
21
22 private PROCEDURE create_resp_sec_attr_ifneeded
23
24 Create the responsibility security attributes if it is not yet created
25
26 PARAM:
27 p_resp_id IN NUMBER - responsibility id
28 p_resp_appl_id IN NUMBER - responsibility application id
29 p_sec_attr_code IN VARCHAR2 - security attribute code
30 p_sec_attr_appl_id IN NUMBER - security attribute application id
31
32 ----------------------------------------*/
33
34 PROCEDURE create_resp_sec_attr_ifneeded
35 (p_resp_id IN NUMBER,
36 p_resp_appl_id IN NUMBER,
37 p_sec_attr_code IN VARCHAR2,
38 p_sec_attr_appl_id IN NUMBER
39 )
40 IS
41 CURSOR l_cur IS
42 SELECT 1
43 FROM ak_resp_security_attributes
44 WHERE responsibility_id = p_resp_id AND
45 resp_application_id = p_resp_appl_id AND
46 attribute_code = p_sec_attr_code AND
47 attribute_application_id = p_sec_attr_appl_id;
48 l_num NUMBER;
49 lv_proc_name VARCHAR2(30) := 'create_resp_sec_attr_ifneeded';
50
51 BEGIN
52 OPEN l_cur;
53 FETCH l_cur INTO l_num;
54 IF l_cur%found THEN
55 CLOSE l_cur;
56 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
57 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' ||
58 lv_proc_name, 'Responsibility Security Attribute for resp_id=' ||
59 p_resp_id || ' and attribute_code=' || p_sec_attr_code || ' exists.'
60 );
61 END IF;
62 RETURN;
63 END IF;
64 CLOSE l_cur;
65
66 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
67 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name,
68 'Creating Responsibility Security Attribute for resp_id=' || p_resp_id ||
69 ' and attribute_code=' || p_sec_attr_code
70 );
71 END IF;
72
73 INSERT INTO ak_resp_security_attributes
74 (responsibility_id,
75 resp_application_id,
76 attribute_code,
77 attribute_application_id,
78 created_by,
79 creation_date,
80 last_updated_by,
81 last_update_date,
82 last_update_login
83 )
84 VALUES
85 (p_resp_id,
86 p_resp_appl_id,
87 p_sec_attr_code,
88 p_sec_attr_appl_id,
89 fnd_global.user_id,
90 Sysdate,
91 fnd_global.user_id,
92 Sysdate,
93 fnd_global.login_id
94 );
95
96
97 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
98 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name,
99 'Responsibility Security Attribute created');
100 END IF;
101
102 END create_resp_sec_attr_ifneeded;
103
104 /*----------------------------------------
105
106 private PROCEDURE set_resp_sec_attrval_ifneeded
107
108 Set the responsibility security attributes value if it is not yet set
109
110 PARAM:
111 p_resp_id IN NUMBER - responsibility id
112 p_resp_appl_id IN NUMBER - responsibility application id
113 p_sec_attr_code IN VARCHAR2 - security attribute code
114 p_sec_attr_appl_id IN NUMBER - security attribute application id
115 p_varchar2_value IN VARCHAR2 DEFAULT NULL - the varchar2 value
116 p_date_value IN DATE DEFAULT NULL - the data value
117 p_number_value IN NUMBER DEFAULT NULL - the number value
118
119 ----------------------------------------*/
120
121 PROCEDURE set_resp_sec_attrval_ifneeded
122 (p_resp_id IN NUMBER,
123 p_resp_appl_id IN NUMBER,
124 p_sec_attr_code IN VARCHAR2,
125 p_sec_attr_appl_id IN NUMBER,
126 p_varchar2_value IN VARCHAR2 DEFAULT NULL,
127 p_date_value IN DATE DEFAULT NULL,
128 p_number_value IN NUMBER DEFAULT NULL
129 )
130 IS
131 CURSOR l_cur IS
132 SELECT 1
133 FROM ak_resp_security_attr_values
134 WHERE responsibility_id = p_resp_id AND
135 resp_application_id = p_resp_appl_id AND
136 attribute_code = p_sec_attr_code AND
137 attribute_application_id = p_sec_attr_appl_id;
138 l_num NUMBER;
139 lv_proc_name VARCHAR2(30) := 'set_resp_sec_attrval_ifneeded';
140
141 BEGIN
142 OPEN l_cur;
143 FETCH l_cur INTO l_num;
144 IF l_cur%found THEN
145 CLOSE l_cur;
146
147 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
148 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name,
149 'No need to set Responsibility Security Attribute value for resp_id=' ||
150 p_resp_id || ' and attribute_code=' || p_sec_attr_code
151 );
152 END IF;
153
154 RETURN;
155 END IF;
156 CLOSE l_cur;
157
158 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
159 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name,
160 'Setting Responsibility Security Attribute value for resp_id=' ||
161 p_resp_id || ' and attribute_code=' || p_sec_attr_code);
162 END IF;
163
164 INSERT INTO ak_resp_security_attr_values
165 (responsibility_id,
166 resp_application_id,
167 attribute_code,
168 attribute_application_id,
169 varchar2_value,
170 date_value,
171 number_value
172 )
173 VALUES
174 (p_resp_id,
175 p_resp_appl_id,
176 p_sec_attr_code,
177 p_sec_attr_appl_id,
178 p_varchar2_value,
179 p_date_value,
180 p_number_value
181 );
182
183
184 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
185 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name,
186 'Responsibility Security Attribute value set'
187 );
188 END IF;
189
190 END set_resp_sec_attrval_ifneeded;
191
192 /*----------------------------------------
193
194 PROCEDURE check_isp_resp_sec_attr
195
196 This procedure will create iSP securing attributes
197 (ICX_SUPPLIER_ORG_ID, ICX_SUPPLIER_SITE_ID, ICX_SUPPLIER_CONTACT_ID)
198 for the responsibility, if they are not defined. It will also
199 set the default value (-9999) for the securing attributes
200 ICX_SUPPLIER_SITE_ID and ICX_SUPPLIER_CONTACT_ID for the responsibility,
201 if they are not defined.
202
203 PARAM:
204 p_resp_id IN NUMBER - responsibility id
205 p_resp_appl_id IN NUMBER - responsibility application id
206
207 ----------------------------------------*/
208
209 PROCEDURE check_isp_resp_sec_attr
210 (p_resp_id IN NUMBER,
211 p_resp_appl_id IN NUMBER)
212 IS
213 CURSOR l_app_id_cur IS
214 SELECT application_id
215 FROM fnd_application
216 WHERE application_short_name = 'POS';
217 l_isp_appl_id NUMBER;
218 lv_proc_name VARCHAR2(30) := 'set_resp_sec_attrval_ifneeded';
219
220 BEGIN
221 OPEN l_app_id_cur;
222 FETCH l_app_id_cur INTO l_isp_appl_id;
223 IF l_app_id_cur%notfound THEN
224 CLOSE l_app_id_cur;
225
226 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
227 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name,
228 'Application ID for POS is not found');
229 END IF;
230
231 RAISE NO_DATA_FOUND;
232 END IF;
233 CLOSE l_app_id_cur;
234 --
235 -- only create the attribute, not set default value
236 -- because we do want the value be set at the user level for ICX_SUPPLIER_ORG_ID
237 create_resp_sec_attr_ifneeded(p_resp_id => p_resp_id,
238 p_resp_appl_id => p_resp_appl_id,
239 p_sec_attr_code => 'ICX_SUPPLIER_ORG_ID',
240 p_sec_attr_appl_id => l_isp_appl_id
241 );
242 --
243 create_resp_sec_attr_ifneeded(p_resp_id => p_resp_id,
244 p_resp_appl_id => p_resp_appl_id,
245 p_sec_attr_code => 'ICX_SUPPLIER_SITE_ID',
246 p_sec_attr_appl_id => l_isp_appl_id
247 );
248 --
249 set_resp_sec_attrval_ifneeded(p_resp_id => p_resp_id,
250 p_resp_appl_id => p_resp_appl_id,
251 p_sec_attr_code => 'ICX_SUPPLIER_SITE_ID',
252 p_sec_attr_appl_id => l_isp_appl_id,
253 p_varchar2_value => NULL,
254 p_date_value => NULL,
255 p_number_value => -9999
256 );
257 --
258 create_resp_sec_attr_ifneeded(p_resp_id => p_resp_id,
259 p_resp_appl_id => p_resp_appl_id,
260 p_sec_attr_code => 'ICX_SUPPLIER_CONTACT_ID',
261 p_sec_attr_appl_id => l_isp_appl_id
262 );
263 --
264 set_resp_sec_attrval_ifneeded(p_resp_id => p_resp_id,
265 p_resp_appl_id => p_resp_appl_id,
266 p_sec_attr_code => 'ICX_SUPPLIER_CONTACT_ID',
267 p_sec_attr_appl_id => l_isp_appl_id,
268 p_varchar2_value => NULL,
269 p_date_value => NULL,
270 p_number_value => -9999
271 );
272 END check_isp_resp_sec_attr;
273
274 /*----------------------------------------
275
276 PROCEDURE check_isp_resp_sec_attr
277
278 Overload check_isp_resp_sec_attr(NUMBER, NUMBER) to take the responsibility
279 key.
280
281 PARAM:
282 p_resp_key IN VARCHAR2 - the responsibility key
283 p_resp_appl_id IN NUMBER - responsibility application id
284
285 ----------------------------------------*/
286
287 PROCEDURE check_isp_resp_sec_attr
288 (p_resp_key IN VARCHAR2,
289 p_resp_appl_id IN NUMBER)
290 IS
291 lv_proc_name VARCHAR2(30) := 'check_isp_resp_sec_attr';
292 ln_resp_id NUMBER;
293 --
294 CURSOR l_resp_id_cur (p_appl_id NUMBER, p_resp_key VARCHAR2) IS
295 SELECT responsibility_id
296 FROM fnd_responsibility
297 WHERE application_id = p_appl_id
298 AND responsibility_key = p_resp_key
299 AND (end_date IS NULL OR end_date > start_date);
300 BEGIN
301 OPEN l_resp_id_cur(p_resp_appl_id, p_resp_key);
302 FETCH l_resp_id_cur INTO ln_resp_id;
303 CLOSE l_resp_id_cur;
304 IF ln_resp_id IS NULL THEN
305
306 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
307 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' ||
308 lv_proc_name, 'Invalid responsibility key ' || p_resp_key);
309 END IF;
310
311 RETURN;
312 END IF;
313
314 check_isp_resp_sec_attr(ln_resp_id, p_resp_appl_id);
315 END check_isp_resp_sec_attr;
316
317 PROCEDURE create_local_user
318 (p_local_user_name IN VARCHAR2,
319 p_email IN VARCHAR2,
320 p_language_code IN VARCHAR2
321 )
322 IS
323 l_local_user_name wf_local_users.name%TYPE;
324 l_display_name wf_local_users.display_name%TYPE;
325 l_nls_lang fnd_languages.nls_language%TYPE;
326 BEGIN
327
328 SELECT nls_language
329 INTO l_nls_lang
330 FROM fnd_languages
331 WHERE language_code = p_language_code;
332
333 l_local_user_name := p_local_user_name;
334 l_display_name := p_email;
335
336 wf_directory.createadhocuser
337 ( name => l_local_user_name,
338 display_name => l_display_name,
339 language => l_nls_lang,
340 email_address => p_email
341 );
342
343 pos_anon_pkg.confirm_has_resp('POS_SUPPLIER_GUEST_USER');
344
345 END create_local_user;
346
347 PROCEDURE lock_reg
348 (p_registration_id IN NUMBER)
349 IS
350 l_registration_id NUMBER;
351 BEGIN
352 SELECT registration_id INTO l_registration_id
353 FROM fnd_registrations
354 WHERE registration_id = p_registration_id FOR UPDATE;
355 END lock_reg;
356
357 FUNCTION get_note (p_registration_id IN NUMBER) RETURN VARCHAR2
358 IS
359 CURSOR l_cur IS
360 SELECT field_value_string
361 FROM fnd_registration_details
362 WHERE field_name = 'Note'
363 AND registration_id = p_registration_id;
364
365 l_note fnd_registration_details.field_value_string%TYPE;
366 BEGIN
367 OPEN l_cur;
368 FETCH l_cur INTO l_note;
369 CLOSE l_cur;
370 RETURN l_note;
371 END get_note;
372
373 FUNCTION get_contact_email RETURN VARCHAR2
374 IS
375 CURSOR l_cur IS
376 SELECT email_address
377 FROM fnd_user
378 WHERE user_id = fnd_global.user_id;
379
380 l_email fnd_user.email_address%TYPE;
381 BEGIN
382 OPEN l_cur;
383 FETCH l_cur INTO l_email;
384 CLOSE l_cur;
385 RETURN l_email;
386 END get_contact_email;
387
388 FUNCTION get_enterprise_name RETURN VARCHAR2
389 IS
390 lv_party_name hz_parties.party_name%TYPE;
391 lv_exception_msg VARCHAR2(32000);
392 lv_status VARCHAR2(240);
393 BEGIN
394
395 pos_enterprise_util_pkg.get_enterprise_party_name
396 ( lv_party_name, lv_exception_msg, lv_status);
397
398 IF ( lv_status <> 'S' ) THEN
399 RETURN NULL;
400 ELSE
401 RETURN lv_party_name;
402 END IF;
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 RETURN NULL;
407 END get_enterprise_name;
408
409 FUNCTION get_password
410 (p_registration_id IN NUMBER) RETURN VARCHAR2
411 IS
412 l_encrypted_initial_password fnd_registration_details.field_value_string%TYPE;
413 lv_unencrypted_password VARCHAR2(100);
414 l_reg_key fnd_registrations.registration_key%TYPE;
415 l_osn_req_id NUMBER;
416 l_internal_id NUMBER;
417 l_user_name FND_USER.USER_NAME%TYPE;
418 l_testname NUMBER;
419
420 CURSOR l_cur IS
421 SELECT d1.field_value_number, d2.field_value_number
422 FROM fnd_registrations r,
423 fnd_registration_details d1, fnd_registration_details d2
424 WHERE r.registration_id = p_registration_id
425 AND d1.registration_id = r.registration_id
426 AND d2.registration_id = r.registration_id
427 AND d1.field_name = 'OSN Request ID'
428 AND d2.field_name = 'OSN Request InternalID';
429
430 BEGIN
431
432 -- OSN: retrieve initial pwd set from OSN request, only when
433 -- the initial password is not set do we generate user pwd.
434 lv_unencrypted_password := NULL;
435
436 --r12 requirement to use centralized password management
437 OPEN l_cur;
438 FETCH l_cur INTO l_osn_req_id, l_internal_id;
439 IF l_cur%notfound THEN
440 lv_unencrypted_password := NULL;
441 ELSE
442 lv_unencrypted_password := fnd_vault.get('POS_OSN',
443 to_char(l_osn_req_id) || '_' || to_char(l_internal_id) );
444 END IF;
445 CLOSE l_cur;
446
447 IF lv_unencrypted_password IS NULL THEN
448
449 BEGIN
450 l_encrypted_initial_password := NULL;
451
452 SELECT registration_key
453 INTO l_reg_key
454 FROM fnd_registrations
455 WHERE registration_id = p_registration_id;
456
457 SELECT field_value_string
458 INTO l_encrypted_initial_password
459 FROM fnd_registration_details
460 WHERE registration_id = p_registration_id
461 AND field_name = 'Initial Pass';
462
463 lv_unencrypted_password := decrypt (l_reg_key,
464 l_encrypted_initial_password
465 );
466
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 --if Initial Pass not found, then it's not an OSN request
470 lv_unencrypted_password := NULL;
471 END;
472
473 END IF;
474
475 IF lv_unencrypted_password IS NULL THEN
476 lv_unencrypted_password := pos_password_util_pkg.generate_user_pwd();
477 END IF;
478
479 -- sso check: for users that already have an account in oid, add synch is allowed
480 -- we should pass null as the password when creating the user account in fnd
481 select r.requested_user_name
482 into l_user_name
483 from fnd_registrations r
484 where r.registration_id = p_registration_id;
485
486 l_testname := FND_USER_PKG.TestUserName(l_user_name);
487 IF (l_testname = FND_USER_PKG.USER_SYNCHED) THEN
488 lv_unencrypted_password := NULL;
489 END IF;
490
491 RETURN lv_unencrypted_password;
492
493 END get_password;
494
495 FUNCTION get_tp_name
496 (p_registration_id IN NUMBER) RETURN VARCHAR2
497 IS
498 CURSOR l_cur IS
499 SELECT frd1.field_value_string tp_name
500 FROM fnd_registration_details frd1,
501 fnd_registrations fr
502 WHERE frd1.registration_id = fr.registration_id
503 AND frd1.field_name = 'OSN TP Name'
504 AND fr.registration_id = p_registration_id;
505
506 l_tpname fnd_registration_details.field_value_string%TYPE;
507 BEGIN
508 OPEN l_cur;
509 FETCH l_cur INTO l_tpname;
510 CLOSE l_cur;
511 RETURN l_tpname;
512 END get_tp_name;
513
514 /*----------------------------------------
515 set_initial_password
516 set the initial password for osn registration request, so the user
517 doesn't need to change password at first logon
518 set_initial_password needs to be called after the user is created
519 and should only be called for osn requests
520 ----------------------------------------*/
521 FUNCTION set_initial_password(l_reg_id NUMBER)
522 RETURN VARCHAR2
523
524 IS
525 l_user_name FND_USER.USER_NAME%TYPE;
526 l_osn_req_id NUMBER;
527 l_internal_id NUMBER;
528 l_reg_key FND_REGISTRATIONS.REGISTRATION_KEY%TYPE;
529 l_encrypted_initial_password VARCHAR2(240);
530 l_initial_password VARCHAR2(30);
531 l_retcode VARCHAR2(1);
532 BEGIN
533 l_initial_password := NULL;
534 l_initial_password := get_password(l_reg_id);
535 --for osn requests, the initial password could be
536 --1. stored in fnd_vault (r12)
537 --2. stored in Initial Pass (before r12)
538 --3. null (if the user exists in oid, but not fnd, and user synch is allowed)
539
540 IF (l_initial_password IS NULL) THEN
541 RETURN 'N';
542 END IF;
543
544 SELECT r.requested_user_name, d1.field_value_number, d2.field_value_number
545 INTO l_user_name, l_osn_req_id, l_internal_id
546 FROM fnd_registrations r,
547 fnd_registration_details d1, fnd_registration_details d2
548 WHERE r.registration_id = l_reg_id
549 AND d1.registration_id = l_reg_id
550 AND d2.registration_id = l_reg_id
551 AND d1.field_name = 'OSN Request ID'
552 AND d2.field_name = 'OSN Request InternalID';
553
554 --call fnd_web_sec.change_password: the same routine
555 --when user first time logon and change his/her password
556 l_retcode := fnd_web_sec.change_password (
557 l_user_name,
558 l_initial_password
559 );
560
561 fnd_vault.del('POS_OSN',
562 to_char(l_osn_req_id) || '_' || to_char(l_internal_id) );
563
564 RETURN l_retcode;
565 EXCEPTION
566 WHEN OTHERS THEN
567 RETURN 'N';
568 END set_initial_password;
569
570 FUNCTION is_osnrequest(p_registration_id IN NUMBER) RETURN VARCHAR2
571 IS
572 CURSOR l_cur IS
573 SELECT field_value_number
574 FROM fnd_registration_details
575 WHERE field_name = 'OSN Request ID'
576 AND registration_id = p_registration_id;
577
578 l_osnreqid fnd_registration_details.field_value_number%TYPE;
579 BEGIN
580 OPEN l_cur;
581 FETCH l_cur INTO l_osnreqid;
582 IF l_cur%notfound THEN
583 l_osnreqid := NULL;
584 END IF;
585 CLOSE l_cur;
586
587 IF (l_osnreqid IS NULL) THEN
588 RETURN 'N';
589 END IF;
590
591 RETURN 'Y';
592 END is_osnrequest;
593
594 /*******************************************************************
595 PROCEDURE NAME: get_wf_role_for_users
596 this is the same function as that in PO_REQAPPROVAL_INIT1 (private func)
597
598 DESCRIPTION :
599 Given a list of users, the procedure looks through the wf_user_roles
600 to get a role that has exactly same set of input list of users.
601
602 parameters :
603 Input:
604 p_list_of_users - String containing the list of users
605 Example string: 'GE1', 'GE2', 'GE22'
606 p_num_users - number of users in the above list
607 Output:
608 A string containg the role name ( or null , if such role
609 does not exist ).
610 *******************************************************************/
611
612 FUNCTION get_wf_role_for_users(p_list_of_users IN VARCHAR2, p_num_users IN NUMBER) RETURN VARCHAR2 IS
613 l_refcur g_refcur;
614 l_role_name WF_USER_ROLES.ROLE_NAME%TYPE;
615 l_progress VARCHAR2(255);
616
617 cursor l_role_cur is
618 SELECT final.role_name
619 FROM fnd_user fu,
620 fnd_responsibility fr,
621 wf_user_roles wur, wf_user_roles final
622 WHERE fr.menu_id in
623 (SELECT fme.menu_id
624 FROM fnd_menu_entries fme, (SELECT function_id FROM fnd_form_functions WHERE function_name = 'POS_REG_APPROVE_EXT_USERS') func
625 START WITH fme.function_id = func.function_id
626 CONNECT BY PRIOR menu_id = sub_menu_id
627 )
628 AND fr.application_id = 177
629 AND wur.role_name like 'FND_RESP|%|%|STANDARD'
630 AND WUR.ROLE_ORIG_SYSTEM = 'FND_RESP'
631 AND WUR.ROLE_ORIG_SYSTEM_ID = FR.RESPONSIBILITY_ID
632 AND WUR.ASSIGNMENT_TYPE IN ('D', 'B')
633 AND wur.user_name = fu.user_name
634 AND final.user_name = fu.user_name
635 GROUP BY final.role_name
636 having count(final.role_name) = p_num_users;
637
638 l_role_rec l_role_cur%ROWTYPE;
639
640 BEGIN
641 l_role_name := null;
642
643 for l_role_rec in l_role_cur loop
644 l_role_name := l_role_rec.role_name;
645 end loop;
646
647 RETURN l_role_name;
648
649 EXCEPTION
650 WHEN OTHERS THEN
651 RETURN null;
652 END;
653
654 /*----------------------------------------
655 function get_approver_role_for_osn_request
656 for user registration coming from Oracle
657 Supplier Network. We want the notification
658 be sent to all users that can approve
659 external user registrations.
660
661 this function will create a role for this
662 user list, so that notification REG_ADMIN_NTF
663 will be sent to this role.
664 ----------------------------------------*/
665 FUNCTION get_approver_role_for_osn
666 RETURN VARCHAR2
667
668 IS
669 x_refcur g_refcur;
670 l_approver_func_id NUMBER := NULL;
671 l_user_name FND_USER.USER_NAME%TYPE := NULL;
672 l_num_users NUMBER := 0;
673 l_approverlist VARCHAR2(2000):=NULL;
674 l_approverlist_sql VARCHAR2(2000):=NULL;
675 l_role_name WF_USER_ROLES.ROLE_NAME%TYPE := NULL;
676 l_role_display_name VARCHAR2(100):=NULL;
677 l_expiration_date DATE;
678
679 BEGIN
680
681 --step 1: find the users that can approve external user registrations
682
683 SELECT function_id
684 INTO l_approver_func_id
685 FROM fnd_form_functions
686 WHERE function_name = 'POS_REG_APPROVE_EXT_USERS';
687
688 OPEN x_refcur FOR
689 'SELECT DISTINCT fu.user_name
690 FROM fnd_user fu,
691 fnd_responsibility fr,
692 wf_user_roles wur
693 WHERE fr.menu_id in
694 (SELECT fme.menu_id
695 FROM fnd_menu_entries fme
696 START WITH fme.function_id = :1
697 CONNECT BY PRIOR menu_id = sub_menu_id
698 )
699 AND fr.application_id = 177
700 AND wur.role_name like ''FND_RESP|%|%|STANDARD''
701 AND WUR.ROLE_ORIG_SYSTEM = ''FND_RESP''
702 AND WUR.ROLE_ORIG_SYSTEM_ID = FR.RESPONSIBILITY_ID
703 AND WUR.ASSIGNMENT_TYPE IN (''D'', ''B'')
704 AND wur.user_name = fu.user_name'
705 using l_approver_func_id;
706
707
708 --step 2: build the approver list
709
710 LOOP
711 FETCH x_refcur INTO l_user_name;
712 EXIT WHEN x_refcur%NOTFOUND;
713 l_num_users := l_num_users + 1;
714 IF(l_approverlist is null) THEN
715 l_approverlist:=l_user_name;
716 l_approverlist_sql := ''''||l_user_name||'''';
717 ELSE
718 l_approverlist:=l_approverlist || ' ' || l_user_name;
719 l_approverlist_sql:=l_approverlist_sql||','||''''||l_user_name||'''';
720 END IF;
721 END LOOP;
722 CLOSE x_refcur;
723
724 --step 3: given the approver list, find an existing matching role
725 -- or create a new role
726
727 IF(l_approverlist is not null) THEN
728 l_role_name:= get_wf_role_for_users(l_approverlist_sql, l_num_users);
729
730 IF(l_role_name is null ) THEN
731
732 l_expiration_date := sysdate + 30; -- this role expires in 30 days
733
734 WF_DIRECTORY.CreateAdHocRole(l_role_name, l_role_display_name,
735 null,
736 null,
737 null,
738 'MAILHTML',
739 l_approverlist,
740 null,
741 null,
742 'ACTIVE',
743 l_expiration_date);
744 END IF;
745
746 RETURN l_role_name;
747
748 END IF;
749
750 --the approver list is null
751 RETURN null;
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 RETURN null;
756 END get_approver_role_for_osn;
757
758 PROCEDURE grant_resps
759 (p_registration_id IN NUMBER,
760 p_user_id IN NUMBER,
761 x_return_status OUT nocopy VARCHAR2,
762 x_msg_count OUT nocopy NUMBER,
763 x_msg_data OUT nocopy VARCHAR2
764 )
765 IS
766 CURSOR l_cur IS
767 SELECT field_value_string
768 FROM fnd_registration_details
769 WHERE registration_id = p_registration_id
770 AND field_name LIKE 'POS_SUPPLIER_RESP_ID%';
771
772 l_resp_id NUMBER;
773 l_resp_app_id NUMBER;
774 l_index NUMBER;
775 BEGIN
776
777 FOR l_rec IN l_cur LOOP
778 l_index := Instr(l_rec.field_value_string,':');
779 l_resp_id := TO_NUMBER(SUBSTR(l_rec.field_value_string, 0, l_index - 1));
780 l_resp_app_id := TO_NUMBER(SUBSTR(l_rec.field_value_string, l_index + 1));
781
782 pos_user_admin_pkg.grant_user_resp
783 ( p_user_id => p_user_id,
784 p_resp_id => l_resp_id,
785 p_resp_app_id => l_resp_app_id,
786 x_return_status => x_return_status,
787 x_msg_count => x_msg_count,
788 x_msg_data => x_msg_data
789 );
790
791 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
792 RETURN;
793 END IF;
794
795 IF l_resp_app_id = 177 THEN -- pos application id is 177
796 check_isp_resp_sec_attr(l_resp_id,l_resp_app_id);
797 END IF;
798 END LOOP;
799 END grant_resps;
800
801 PROCEDURE set_sec_attrs
802 (p_registration_id IN NUMBER,
803 p_user_id IN NUMBER,
804 x_return_status OUT nocopy VARCHAR2,
805 x_msg_count OUT nocopy NUMBER,
806 x_msg_data OUT nocopy VARCHAR2
807 )
808 IS
809 CURSOR l_cur IS
810 SELECT DISTINCT field_value_number, field_name
811 FROM fnd_registration_details
812 WHERE registration_id = p_registration_id
813 AND (field_name like 'POS_SUPPLIER_ID%'
814 OR field_name like 'POS_SUPPLIER_SITE_ID%'
815 OR field_name like 'POS_SUPPLIER_CONTACT_ID%'
816 );
817 l_attr_code VARCHAR2(30);
818 BEGIN
819 FOR l_rec IN l_cur LOOP
820 IF l_rec.field_name LIKE 'POS_SUPPLIER_ID%' THEN
821 l_attr_code := 'ICX_SUPPLIER_ORG_ID';
822 ELSIF l_rec.field_name LIKE 'POS_SUPPLIER_SITE_ID%' THEN
823 l_attr_code := 'ICX_SUPPLIER_SITE_ID';
824 ELSIF l_rec.field_name LIKE 'POS_SUPPLIER_CONTACT_ID%' THEN
825 l_attr_code := 'ICX_SUPPLIER_CONTACT_ID';
826 END IF;
827
828 pos_user_admin_pkg.createsecattr
829 ( p_user_id => p_user_id,
830 p_attribute_code => l_attr_code,
831 p_app_id => 177,
832 p_number_value => l_rec.field_value_number
833 );
834 END LOOP;
835 x_return_status := fnd_api.g_ret_sts_success;
836 END set_sec_attrs;
837
838 PROCEDURE set_user_profile
839 (p_user_id IN NUMBER,
840 p_registration_id IN NUMBER,
841 x_return_status OUT nocopy VARCHAR2,
842 x_msg_count OUT nocopy NUMBER,
843 x_msg_data OUT nocopy VARCHAR2
844 )
845 IS
846 l_value fnd_profile_option_values.profile_option_value%TYPE;
847 l_web_agent fnd_profile_option_values.profile_option_value%TYPE;
848 l_fwk_agent fnd_profile_option_values.profile_option_value%TYPE;
849 l_servlet_agent fnd_profile_option_values.profile_option_value%TYPE;
850 l_saved BOOLEAN;
851 l_flag VARCHAR2(1);
852 is_osn VARCHAR2(1);
853 BEGIN
854 fnd_profile.get('POS_EXTERNAL_URL', l_value);
855 l_web_agent := l_value;
856 l_fwk_agent := l_value;
857 owa_pattern.change(l_fwk_agent, '/pls.*', NULL);
858 l_servlet_agent := l_fwk_agent || '/OA_HTML/';
859
860 l_flag := NULL;
861 IF l_web_agent IS NOT NULL AND owa_pattern.match(l_web_agent,'/pls/', l_flag) THEN
862 -- The value of POS_EXTERNAL_URL still points to icx web site.
863 l_saved := fnd_profile.save( x_name => 'APPS_WEB_AGENT',
864 x_value => l_web_agent,
865 x_level_name => 'USER',
866 x_level_value => p_user_id
867 );
868 END IF;
869
870 IF l_fwk_agent IS NOT NULL THEN
871 l_saved := fnd_profile.save( x_name => 'APPS_FRAMEWORK_AGENT',
872 x_value => l_fwk_agent,
873 x_level_name => 'USER',
874 x_level_value => p_user_id
875 );
876 END IF;
877
878 IF l_servlet_agent IS NOT NULL THEN
879 l_saved := fnd_profile.save( x_name => 'APPS_SERVLET_AGENT',
880 x_value => l_servlet_agent,
881 x_level_name => 'USER',
882 x_level_value => p_user_id
883 );
884
885 END IF;
886
887 --OSN: need to set the profile to identify this user as a local user
888 is_osn := is_osnrequest(p_registration_id);
889 IF (is_osn = 'Y') THEN
890 l_saved := fnd_profile.save( x_name => 'APPS_SSO_LOCAL_LOGIN',
891 -- 'Applications SSO Login Types' (Both/Local/SSO)
892 x_value => 'Local',
893 x_level_name => 'USER',
894 x_level_value => to_char(p_user_id),
895 x_level_value_app_id => NULL
896 );
897 END IF;
898
899 x_return_status := fnd_api.g_ret_sts_success;
900
901 END set_user_profile;
902
903 FUNCTION gen_local_user_name
904 (p_registration_id IN NUMBER,
905 p_flow IN VARCHAR2
906 ) RETURN VARCHAR2
907 IS
908 BEGIN
909
910 RETURN Substr('POSREGV2_' || p_registration_id || '_' || p_flow || '_' ||
911 fnd_crypto.smallrandomnumber(), 0, 320);
912
913 END gen_local_user_name;
914
915 FUNCTION gen_wf_item_key
916 (p_registration_id IN NUMBER,
917 p_flow IN VARCHAR2
918 ) RETURN VARCHAR2
919 IS
920 BEGIN
921 RETURN Substr('POSREGV2_' || p_registration_id || '_' || p_flow || '_' ||
922 fnd_crypto.smallrandomnumber(), 0, 240);
923 END gen_wf_item_key;
924
925 FUNCTION get_reg_page_url
926 (p_inv_key IN VARCHAR2,
927 p_reg_lang_code IN VARCHAR2
928 )
929 RETURN VARCHAR2 IS
930 BEGIN
931 RETURN pos_url_pkg.get_external_url || POS_INV_REPLY_PAGE || p_inv_key ||
932 '®Lang=' || p_reg_lang_code;
933 END get_reg_page_url;
934
935 PROCEDURE send_approval_ntf
936 (p_registration_id IN NUMBER,
937 p_is_invited IN VARCHAR2,
938 p_is_user_in_oid IN VARCHAR2,
939 p_user_name IN VARCHAR2,
940 p_password IN VARCHAR2
941 )
942 IS
943 l_itemtype wf_items.item_type%TYPE;
944 l_itemkey wf_items.item_key%TYPE;
945 l_process wf_process_activities.process_name%TYPE;
946 is_osn VARCHAR2(1);
947 l_enterprise_name VARCHAR2(10000);
948 BEGIN
949 IF p_is_invited = 'Y' THEN
950 l_process := 'SEND_APPRV_INV_USER_NTF';
951 ELSE
952 is_osn := is_osnrequest(p_registration_id);
953 IF (is_osn = 'Y') THEN
954 l_process := 'SEND_OSN_REG_USER_NTF';
955 ELSIF (p_is_user_in_oid = 'Y') THEN
956 l_process := 'SEND_APPRV_USER_SSOSYNC_NTF';
957 ELSE
958 l_process := 'SEND_APPRV_REG_USER_NTF';
959 END IF;
960 END IF;
961
962 l_itemtype := 'POSREGV2';
963 l_itemkey := gen_wf_item_key(p_registration_id,'approve');
964
965 wf_engine.CreateProcess(itemtype => l_itemtype,
966 itemkey => l_itemkey,
967 process => l_process
968 );
969
970 wf_engine.SetItemAttrText (itemtype => l_itemtype,
971 itemkey => l_itemkey,
972 aname => 'LOGON_PAGE_URL',
973 avalue => pos_url_pkg.get_external_login_url
974 );
975
976 wf_engine.SetItemAttrText (itemtype => l_itemtype,
977 itemkey => l_itemkey,
978 aname => 'ASSIGNED_USER_NAME',
979 avalue => p_user_name
980 );
981
982 wf_engine.SetItemAttrText (itemtype => l_itemtype,
983 itemkey => l_itemkey,
984 aname => 'FIRST_LOGON_KEY',
985 avalue => p_password
986 );
987
988 wf_engine.SetItemAttrText (itemtype => l_itemtype,
989 itemkey => l_itemkey,
990 aname => 'CONTACT_EMAIL',
991 avalue => get_contact_email
992 );
993
994 IF p_is_invited <> 'Y' THEN
995 wf_engine.SetItemAttrText (itemtype => l_itemtype,
996 itemkey => l_itemkey,
997 aname => 'NOTE',
998 avalue => get_note(p_registration_id)
999 );
1000 END IF;
1001
1002 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1003 itemkey => l_itemkey,
1004 aname => 'ENTERPRISE_NAME',
1005 avalue => get_enterprise_name
1006 );
1007
1008 -- Bug 8325979 - Following code added to replace the message body with FND
1009 -- Message tokens
1010
1011 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1012 itemkey => l_itemkey,
1013 aname => 'POS_APPRV_REG_USER_SUBJECT',
1014 avalue => GET_APPRV_REG_USR_SUBJECT(l_enterprise_name)
1015 );
1016
1017 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1018 itemkey => l_itemkey,
1019 aname => 'POS_APPRV_REG_USER_BODY',
1020 avalue => 'PLSQLCLOB:pos_supplier_user_reg_pkg.GENERATE_APPRV_REG_USR_BODY/'||l_itemtype ||':' ||l_itemkey
1021 );
1022
1023
1024
1025 wf_engine.StartProcess (itemtype => l_itemtype,
1026 itemkey => l_itemkey
1027 );
1028
1029 END send_approval_ntf;
1030
1031 PROCEDURE send_respond_ntf
1032 (p_registration_id IN NUMBER,
1033 p_first_name IN VARCHAR2,
1034 p_last_name IN VARCHAR2,
1035 p_vendor_name IN VARCHAR2,
1036 p_approver_role IN VARCHAR2
1037 )
1038 IS
1039 l_itemtype wf_items.item_type%TYPE;
1040 l_itemkey wf_items.item_key%TYPE;
1041 l_local_user_name wf_local_users.name%TYPE;
1042 l_process wf_process_activities.process_name%TYPE;
1043 is_osn VARCHAR2(1);
1044 lv_approver_role WF_USER_ROLES.ROLE_NAME%TYPE;
1045 BEGIN
1046
1047 l_itemtype := 'POSREGV2';
1048 is_osn := is_osnrequest(p_registration_id);
1049 l_itemkey := gen_wf_item_key(p_registration_id,'respond');
1050 IF (is_osn = 'Y') THEN
1051 l_process := 'SEND_OSN_ADMIN_NTF';
1052 ELSE
1053 l_process := 'SEND_REG_ADMIN_NTF';
1054 END IF;
1055
1056 wf_engine.CreateProcess(itemtype => l_itemtype,
1057 itemkey => l_itemkey,
1058 process => l_process
1059 );
1060
1061 IF (is_osn = 'Y') THEN
1062 lv_approver_role := get_approver_role_for_osn();
1063 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1064 itemkey => l_itemkey,
1065 aname => 'APPROVER_ROLE',
1066 avalue => lv_approver_role
1067 );
1068 ELSE
1069 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1070 itemkey => l_itemkey,
1071 aname => 'APPROVER_ROLE',
1072 avalue => p_approver_role
1073 );
1074 END IF;
1075
1076 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1077 itemkey => l_itemkey,
1078 aname => 'FIRST_NAME',
1079 avalue => p_first_name
1080 );
1081
1082 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1083 itemkey => l_itemkey,
1084 aname => 'LAST_NAME',
1085 avalue => p_last_name
1086 );
1087
1088 IF (is_osn = 'Y' AND p_vendor_name IS NULL) THEN
1089 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1090 itemkey => l_itemkey,
1091 aname => 'VENDOR_NAME',
1092 avalue => get_tp_name(p_registration_id)
1093 );
1094 ELSE
1095 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1096 itemkey => l_itemkey,
1097 aname => 'VENDOR_NAME',
1098 avalue => p_vendor_name
1099 );
1100 END IF;
1101
1102 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1103 itemkey => l_itemkey,
1104 aname => 'LOGON_PAGE_URL',
1105 avalue => pos_url_pkg.get_buyer_login_url
1106 );
1107
1108 wf_engine.StartProcess (itemtype => l_itemtype,
1109 itemkey => l_itemkey
1110 );
1111
1112 END send_respond_ntf;
1113
1114 PROCEDURE send_invitation_ntf
1115 (p_registration_id IN NUMBER,
1116 p_email IN VARCHAR2,
1117 p_language_code IN VARCHAR2,
1118 p_note IN VARCHAR2,
1119 p_invitation_key IN VARCHAR2
1120 )
1121 IS
1122 l_itemtype wf_items.item_type%TYPE;
1123 l_itemkey wf_items.item_key%TYPE;
1124 l_local_user_name wf_local_users.name%TYPE;
1125 l_process wf_process_activities.process_name%TYPE;
1126 BEGIN
1127
1128 l_itemtype := 'POSREGV2';
1129 l_itemkey := gen_wf_item_key(p_registration_id,'invite');
1130 l_process := 'SEND_INV_USER_NTF';
1131 l_local_user_name := gen_local_user_name(p_registration_id, 'invite');
1132
1133 create_local_user
1134 (p_local_user_name => l_local_user_name,
1135 p_email => p_email,
1136 p_language_code => p_language_code
1137 );
1138
1139 wf_engine.CreateProcess(itemtype => l_itemtype,
1140 itemkey => l_itemkey,
1141 process => l_process
1142 );
1143
1144 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1145 itemkey => l_itemkey,
1146 aname => 'ADHOC_USER_NAME',
1147 avalue => l_local_user_name
1148 );
1149
1150 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1151 itemkey => l_itemkey,
1152 aname => 'ENTERPRISE_NAME',
1153 avalue => get_enterprise_name
1154 );
1155
1156 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1157 itemkey => l_itemkey,
1158 aname => 'REG_PAGE_URL',
1159 avalue => get_reg_page_url(p_invitation_key,
1160 p_language_code)
1161 );
1162
1163 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1164 itemkey => l_itemkey,
1165 aname => 'NOTE',
1166 avalue => p_note
1167 );
1168
1169 wf_engine.StartProcess (itemtype => l_itemtype,
1170 itemkey => l_itemkey
1171 );
1172
1173 END send_invitation_ntf;
1174
1175 PROCEDURE send_rejection_ntf
1176 (p_registration_id IN NUMBER,
1177 p_email IN VARCHAR2,
1178 p_language_code IN VARCHAR2
1179 )
1180 IS
1181 l_itemtype wf_items.item_type%TYPE;
1182 l_itemkey wf_items.item_key%TYPE;
1183 l_local_user_name wf_local_users.name%TYPE;
1184 l_process wf_process_activities.process_name%TYPE;
1185 BEGIN
1186
1187 l_itemtype := 'POSREGV2';
1188 l_itemkey := gen_wf_item_key(p_registration_id,'reject');
1189 l_process := 'SEND_RJCT_USER_NTF';
1190 l_local_user_name := gen_local_user_name(p_registration_id, 'reject');
1191
1192 create_local_user
1193 (p_local_user_name => l_local_user_name,
1194 p_email => p_email,
1195 p_language_code => p_language_code
1196 );
1197
1198 wf_engine.CreateProcess(itemtype => l_itemtype,
1199 itemkey => l_itemkey,
1200 process => l_process
1201 );
1202
1203 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1204 itemkey => l_itemkey,
1205 aname => 'ADHOC_USER_NAME',
1206 avalue => l_local_user_name
1207 );
1208
1209 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1210 itemkey => l_itemkey,
1211 aname => 'CONTACT_EMAIL',
1212 avalue => get_contact_email
1213 );
1214
1215 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1216 itemkey => l_itemkey,
1217 aname => 'ENTERPRISE_NAME',
1218 avalue => get_enterprise_name
1219 );
1220 wf_engine.StartProcess (itemtype => l_itemtype,
1221 itemkey => l_itemkey
1222 );
1223
1224 END send_rejection_ntf;
1225
1226 FUNCTION is_invited
1227 (p_registration_id IN NUMBER) RETURN VARCHAR2
1228 IS
1229 CURSOR l_cur IS
1230 SELECT field_value_string
1231 FROM fnd_registration_details
1232 WHERE field_name = 'Invited Flag'
1233 AND registration_id = p_registration_id;
1234
1235 l_value fnd_registration_details.field_value_string%TYPE;
1236 BEGIN
1237 OPEN l_cur;
1238 FETCH l_cur INTO l_value;
1239 CLOSE l_cur;
1240
1241 IF l_value IS NULL THEN
1242 RETURN NULL;
1243 END IF;
1244
1245 IF l_value = 'Y' OR l_value = 'y' THEN
1246 RETURN 'Y';
1247 END IF;
1248
1249 RETURN 'N';
1250
1251 END is_invited;
1252
1253 PROCEDURE approve
1254 (p_registration_id IN NUMBER,
1255 x_return_status OUT nocopy VARCHAR2,
1256 x_msg_count OUT nocopy NUMBER,
1257 x_msg_data OUT nocopy VARCHAR2
1258 )
1259 IS
1260 CURSOR l_cur IS
1261 SELECT pv.party_id vendor_party_id,
1262 fr.*, jobt.field_value_string
1263 FROM po_vendors pv,
1264 fnd_registration_details frd,
1265 fnd_registrations fr, fnd_registration_details jobt
1266 WHERE frd.registration_id = fr.registration_id
1267 AND pv.vendor_id = frd.field_value_number
1268 AND frd.field_name = 'Supplier Number'
1269 AND jobt.field_name = 'Job Title'
1270 AND fr.registration_id = jobt.registration_id
1271 AND fr.application_id = jobt.application_id
1272 AND fr.registration_type = jobt.registration_type
1273 AND fr.registration_id = p_registration_id;
1274
1275 l_rec l_cur%ROWTYPE;
1276
1277 l_person_party_id NUMBER;
1278 l_password VARCHAR2(100);
1279 l_password_returned VARCHAR2(100);
1280 l_user_id NUMBER;
1281 l_user_in_oid VARCHAR2(1);
1282
1283 BEGIN
1284 SAVEPOINT supplier_user_reg_approve_sp;
1285
1286 lock_reg (p_registration_id);
1287
1288 OPEN l_cur;
1289 FETCH l_cur INTO l_rec;
1290 IF l_cur%notfound THEN
1291 CLOSE l_cur;
1292 x_return_status := fnd_api.g_ret_sts_error;
1293 x_msg_count := 1;
1294 x_msg_data := 'Can not find registration data for registration ' || p_registration_id;
1295 RETURN;
1296 END IF;
1297 CLOSE l_cur;
1298
1299 l_password := get_password (p_registration_id);
1300
1301 -- create supplier contact
1302 pos_supp_contact_pkg.create_supplier_contact
1303 (p_vendor_party_id => l_rec.vendor_party_id,
1304 p_first_name => l_rec.first_name,
1305 p_last_name => l_rec.last_name,
1306 p_middle_name => l_rec.middle_name,
1307 p_contact_title => l_rec.user_title,
1308 p_job_title => l_rec.field_value_string,
1309 p_phone_area_code => l_rec.phone_area_code,
1310 p_phone_number => l_rec.phone,
1311 p_phone_extension => l_rec.phone_extension,
1312 p_fax_area_code => l_rec.fax_area_code,
1313 p_fax_number => l_rec.fax,
1314 p_email_address => l_rec.email,
1315 x_return_status => x_return_status,
1316 x_msg_count => x_msg_count,
1317 x_msg_data => x_msg_data,
1318 x_person_party_id => l_person_party_id
1319 );
1320
1321 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1322 ROLLBACK TO supplier_user_reg_approve_sp;
1323 RETURN;
1324 END IF;
1325
1326 l_user_in_oid := 'N';
1327 if (FND_USER_PKG.TestUserName(l_rec.requested_user_name) = FND_USER_PKG.USER_SYNCHED) then
1328 l_user_in_oid := 'Y';
1329 end if;
1330
1331 -- create supplier user account
1332 pos_user_admin_pkg.create_supplier_user_account
1333 (p_user_name => l_rec.requested_user_name,
1334 p_user_email => l_rec.email,
1335 p_person_party_id => l_person_party_id,
1336 p_password => l_password,
1337 x_return_status => x_return_status,
1338 x_msg_count => x_msg_count,
1339 x_msg_data => x_msg_data,
1340 x_user_id => l_user_id,
1341 x_password => l_password_returned
1342 );
1343
1344 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1345 ROLLBACK TO supplier_user_reg_approve_sp;
1346 RETURN;
1347 END IF;
1348
1349 -- grant responsibilities
1350 grant_resps(p_registration_id => p_registration_id,
1351 p_user_id => l_user_id,
1352 x_return_status => x_return_status,
1353 x_msg_count => x_msg_count,
1354 x_msg_data => x_msg_data
1355 );
1356
1357 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1358 ROLLBACK TO supplier_user_reg_approve_sp;
1359 RETURN;
1360 END IF;
1361
1362 -- set sec attrs
1363 set_sec_attrs(p_registration_id => p_registration_id,
1364 p_user_id => l_user_id,
1365 x_return_status => x_return_status,
1366 x_msg_count => x_msg_count,
1367 x_msg_data => x_msg_data
1368 );
1369
1370 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1371 ROLLBACK TO supplier_user_reg_approve_sp;
1372 RETURN;
1373 END IF;
1374
1375 set_user_profile ( p_user_id => l_user_id,
1376 p_registration_id => p_registration_id,
1377 x_return_status => x_return_status,
1378 x_msg_count => x_msg_count,
1379 x_msg_data => x_msg_data
1380 );
1381
1382 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1383 ROLLBACK TO supplier_user_reg_approve_sp;
1384 RETURN;
1385 END IF;
1386
1387 UPDATE fnd_registrations
1388 SET registration_status = 'APPROVED',
1389 last_update_date = Sysdate,
1390 last_update_login = fnd_global.login_id,
1391 last_updated_by = fnd_global.user_id
1392 WHERE registration_id = p_registration_id;
1393
1394 send_approval_ntf
1395 (p_registration_id => p_registration_id,
1396 p_is_invited => is_invited(p_registration_id),
1397 p_is_user_in_oid => l_user_in_oid,
1398 p_user_name => Upper(l_rec.requested_user_name),
1399 p_password => l_password
1400 );
1401
1402 IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1403 ROLLBACK TO supplier_user_reg_approve_sp;
1404 RETURN;
1405 END IF;
1406
1407 x_return_status := fnd_api.g_ret_sts_success;
1408
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 ROLLBACK TO supplier_user_reg_approve_sp;
1412 x_return_status := fnd_api.g_ret_sts_unexp_error;
1413 x_msg_data := Sqlerrm;
1414 x_msg_count := 1;
1415 RETURN;
1416 END approve;
1417
1418 PROCEDURE reject
1419 (p_registration_id IN NUMBER,
1420 x_return_status OUT nocopy VARCHAR2,
1421 x_msg_count OUT nocopy NUMBER,
1422 x_msg_data OUT nocopy VARCHAR2
1423 )
1424 IS
1425 l_email fnd_registrations.email%TYPE;
1426 l_language_code fnd_registrations.language_code%TYPE;
1427 event_id Number;
1428 BEGIN
1429 SAVEPOINT supplier_user_reject_sp;
1430 UPDATE fnd_registrations
1431 SET registration_status = 'REJECTED',
1432 last_update_date = Sysdate,
1433 last_update_login = fnd_global.login_id,
1434 last_updated_by = fnd_global.user_id
1435 WHERE registration_id = p_registration_id;
1436
1437 SELECT email, language_code
1438 INTO l_email, l_language_code
1439 FROM fnd_registrations
1440 WHERE registration_id = p_registration_id;
1441
1442 send_rejection_ntf
1443 (p_registration_id => p_registration_id,
1444 p_email => l_email,
1445 p_language_code => l_language_code
1446 );
1447
1448 x_return_status := fnd_api.g_ret_sts_success;
1449
1450 /* Begin Supplier Hub - Supplier Data Publication */
1451 /* Raise Supplier User Creation event*/
1452 event_id:= pos_appr_rej_supp_event_raise.raise_appr_rej_supp_event('oracle.apps.pos.supplier.rejectsupplieruser', p_registration_id, '');
1453
1454 /* End Supplier Hub - Supplier Data Publication */
1455
1456 EXCEPTION
1457 WHEN OTHERS THEN
1458 ROLLBACK TO supplier_user_reg_reject_sp;
1459 x_return_status := fnd_api.g_ret_sts_unexp_error;
1460 x_msg_data := Sqlerrm;
1461 x_msg_count := 1;
1462 RETURN;
1463 END reject;
1464
1465 PROCEDURE invite
1466 (p_registration_id IN NUMBER,
1467 x_return_status OUT nocopy VARCHAR2,
1468 x_msg_count OUT nocopy NUMBER,
1469 x_msg_data OUT nocopy VARCHAR2
1470 )
1471 IS
1472 l_email fnd_registrations.email%TYPE;
1473 l_language_code fnd_registrations.language_code%TYPE;
1474 l_reg_key fnd_registrations.registration_key%TYPE;
1475 BEGIN
1476 SAVEPOINT supplier_user_invite_sp;
1477 UPDATE fnd_registrations
1478 SET registration_status = 'INVITED',
1479 last_update_date = Sysdate,
1480 last_update_login = fnd_global.login_id,
1481 last_updated_by = fnd_global.user_id
1482 WHERE registration_id = p_registration_id;
1483
1484 SELECT email, language_code, registration_key
1485 INTO l_email, l_language_code, l_reg_key
1486 FROM fnd_registrations
1487 WHERE registration_id = p_registration_id;
1488
1489 send_invitation_ntf
1490 (p_registration_id => p_registration_id,
1491 p_email => l_email,
1492 p_language_code => l_language_code,
1493 p_note => get_note(p_registration_id),
1494 p_invitation_key => l_reg_key
1495 );
1496 x_return_status := fnd_api.g_ret_sts_success;
1497 EXCEPTION
1498 WHEN OTHERS THEN
1499 x_return_status := fnd_api.g_ret_sts_unexp_error;
1500 x_msg_count := 1;
1501 x_msg_data := Sqlerrm;
1502 ROLLBACK TO supplier_user_invite_sp;
1503 END invite;
1504
1505 PROCEDURE respond
1506 (p_registration_id IN NUMBER,
1507 x_return_status OUT nocopy VARCHAR2,
1508 x_msg_count OUT nocopy NUMBER,
1509 x_msg_data OUT nocopy VARCHAR2
1510 )
1511 IS
1512 CURSOR l_cur IS
1513 SELECT fr.first_name, fr.last_name,
1514 fr.email, fr.language_code, fr.registration_key,
1515 frd1.field_value_string vendor_name,
1516 frd2.field_value_number approver_id,
1517 fu.user_name
1518 FROM fnd_registration_details frd1,
1519 fnd_registration_details frd2,
1520 fnd_registrations fr,
1521 fnd_user fu
1522 WHERE frd1.registration_id = fr.registration_id
1523 AND frd1.field_name = 'Supplier Name'
1524 AND frd2.registration_id = fr.registration_id
1525 AND frd2.field_name = 'Approver ID'
1526 AND fr.registration_id = p_registration_id
1527 AND fu.user_id = frd2.field_value_number;
1528
1529 l_rec l_cur%ROWTYPE;
1530 BEGIN
1531 SAVEPOINT supplier_user_respond_sp;
1532 UPDATE fnd_registrations
1533 SET registration_status = 'REGISTERED',
1534 last_update_date = Sysdate,
1535 last_update_login = fnd_global.login_id,
1536 last_updated_by = fnd_global.user_id
1537 WHERE registration_id = p_registration_id;
1538
1539 OPEN l_cur;
1540 FETCH l_cur INTO l_rec;
1541 IF l_cur%notfound THEN
1542 CLOSE l_cur;
1543 RAISE no_data_found;
1544 END IF;
1545 CLOSE l_cur;
1546
1547 send_respond_ntf
1548 (p_registration_id => p_registration_id,
1549 p_first_name => l_rec.first_name,
1550 p_last_name => l_rec.last_name,
1551 p_vendor_name => l_rec.vendor_name,
1552 p_approver_role => l_rec.user_name
1553 );
1554
1555 x_return_status := fnd_api.g_ret_sts_success;
1556 EXCEPTION
1557 WHEN OTHERS THEN
1558 x_return_status := fnd_api.g_ret_sts_unexp_error;
1559 x_msg_count := 1;
1560 x_msg_data := Sqlerrm;
1561 ROLLBACK TO supplier_user_respond_sp;
1562 END respond;
1563
1564 /*---------------------------------------
1565
1566 public
1567 Procedure to set profile options for external user.
1568 This procedure set the APPS_FRAMEWORK_AGENT and
1569 APPS_WEB_AGENT for external user
1570
1571 */
1572
1573 PROCEDURE set_profile_opt_ext_user
1574 (p_userid in number)
1575 is
1576 lv_external_web_agent fnd_profile_option_values.profile_option_value%TYPE := NULL;
1577 lv_ext_servlet_agent fnd_profile_option_values.profile_option_value%TYPE := NULL;
1578 lv_pattern VARCHAR2(40);
1579 lv_flag VARCHAR2(40);
1580 lv_proc_name VARCHAR2(30) := 'set_profile_opt_ext_user';
1581 begin
1582
1583 fnd_profile.get('POS_EXTERNAL_URL', lv_external_web_agent);
1584 fnd_profile.get('POS_EXTERNAL_URL', lv_ext_servlet_agent);
1585
1586 IF ( lv_external_web_agent IS NOT NULL ) THEN
1587
1588 lv_pattern := '/pls';
1589 lv_flag := ''; -- we want it to be case sensitive for now.
1590 If (owa_pattern.match(lv_external_web_agent,lv_pattern, lv_flag)) then
1591 -- The external profile still points to icx web site.
1592
1593 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1594 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Setting user level APPS_WEB_AGENT profile option value');
1595 END IF;
1596
1597 IF ( fnd_profile.save( x_name => 'APPS_WEB_AGENT',
1598 x_value => lv_external_web_agent,
1599 x_level_name => 'USER',
1600 x_level_value => p_userid ) ) THEN
1601
1602 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1603 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_WEB_AGENT profile option value set');
1604 END IF;
1605
1606 ELSE
1607
1608 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1609 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_WEB_AGENT profile option value fail');
1610 END IF;
1611
1612 END IF;
1613
1614 owa_pattern.change(lv_ext_servlet_agent, '/pls.*', '/OA_HTML');
1615 IF ( fnd_profile.save( x_name => 'APPS_SERVLET_AGENT',
1616 x_value => lv_ext_servlet_agent,
1617 x_level_name => 'USER',
1618 x_level_value => p_userid ) ) THEN
1619
1620 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1621 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_SERVLET_AGENT profile option value set');
1622 END IF;
1623
1624 ELSE
1625
1626 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1627 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_SERVLET_AGENT profile option value fail');
1628 END IF;
1629
1630 END IF;
1631
1632 owa_pattern.change(lv_ext_servlet_agent, '/OA_HTML.*', '');
1633 IF ( fnd_profile.save( x_name => 'APPS_FRAMEWORK_AGENT',
1634 x_value => lv_ext_servlet_agent,
1635 x_level_name => 'USER',
1636 x_level_value => p_userid ) ) THEN
1637
1638 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1639 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value set');
1640 END IF;
1641
1642 ELSE
1643
1644 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1645 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value fail');
1646 END IF;
1647
1648 END IF;
1649 ELSE
1650 IF ( fnd_profile.save( x_name => 'APPS_FRAMEWORK_AGENT',
1651 x_value => lv_ext_servlet_agent,
1652 x_level_name => 'USER',
1653 x_level_value => p_userid ) ) THEN
1654
1655 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1656 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value set');
1657 END IF;
1658
1659 ELSE
1660
1661 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1662 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value fail');
1663 END IF;
1664
1665 END IF;
1666 -- set only the framework agent. there is no way to set web agent
1667 -- as we dont know the external dbc name.
1668 lv_ext_servlet_agent := lv_ext_servlet_agent || '/OA_HTML';
1669 IF ( fnd_profile.save( x_name => 'APPS_SERVLET_AGENT',
1670 x_value => lv_ext_servlet_agent,
1671 x_level_name => 'USER',
1672 x_level_value => p_userid ) ) THEN
1673
1674 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1675 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_servlet_AGENT profile option value set');
1676 END IF;
1677
1678 ELSE
1679
1680 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1681 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_servlet_AGENT profile option value fail');
1682 END IF;
1683
1684 END IF;
1685 End if;
1686
1687 ELSE
1688
1689 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1690 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'POS_EXTERNAL_URL is not set');
1691 END IF;
1692
1693 END IF;
1694
1695
1696 end set_profile_opt_ext_user;
1697
1698
1699 -------------------------------------------------------------------------------
1700 --Start of Comments
1701 --Name: GET_APPRV_REG_USR_SUBJECT
1702 --Type:
1703 -- Function
1704 --Function:
1705 -- It returns the tokens replaced FND message to Notification Message Subject
1706 --Function Usage:
1707 -- This function is used to replace the workflow message subject by FND Message & its tokens
1708 --Logic Implemented:
1709 -- The FND Message Name 'POS_APPRV_REG_USER_SUBJECT' will be replaced with
1710 -- corresponding Message Text and tokens inside the Message Text also be replaced.
1711 -- Then, replaced FND message will be return to the corresponding attribute
1712 --Parameters:
1713 -- Enterprise Name
1714 --IN:
1715 -- Enterprise Name
1716 --OUT:
1717 -- l_document
1718 --Bug Number for reference:
1719 -- 8325979
1720 --End of Comments
1721 ------------------------------------------------------------------------------
1722
1723 FUNCTION GET_APPRV_REG_USR_SUBJECT(p_enterprise_name IN VARCHAR2)
1724 RETURN VARCHAR2 IS
1725 l_document VARCHAR2(32000);
1726
1727 BEGIN
1728
1729 fnd_message.set_name('POS','POS_APPRV_REG_USER_SUBJECT');
1730 fnd_message.set_token('ENTERPRISE_NAME', p_enterprise_name);
1731 l_document := fnd_message.get;
1732 RETURN l_document;
1733 END GET_APPRV_REG_USR_SUBJECT;
1734
1735 -------------------------------------------------------------------------------
1736 --Start of Comments
1737 --Name: GENERATE_APPRV_REG_USR_BODY
1738 --Type:
1739 -- Procedure
1740 --Procedure:
1741 -- It returns the tokens replaced FND message to Notification Message Body
1742 --Procedure Usage:
1743 -- It is being used to replace the workflow message Body by FND Message & its tokens
1744 --Logic Implemented:
1745 -- For HTML Body:
1746 -- The FND Message Name 'POS_APPRV_REG_USER_HTML_BODY' will be replaced with
1747 -- corresponding Message Text and tokens inside the Message Text also be replaced.
1748 -- Then, replaced FND message will be return to the corresponding attribute
1749 -- For TEXT Body:
1750 -- The FND Message Name 'POS_APPRV_REG_USER_TEXT_BODY' will be replaced with
1751 -- corresponding Message Text and tokens inside the Message Text also be replaced.
1752 -- Then, replaced FND message will be return to the corresponding attribute
1753 --Parameters:
1754 -- document_id
1755 --IN:
1756 -- document_id
1757 --OUT:
1758 -- document
1759 --Bug Number for reference:
1760 -- 8325979
1761 --End of Comments
1762 ------------------------------------------------------------------------------
1763
1764 PROCEDURE GENERATE_APPRV_REG_USR_BODY(p_document_id IN VARCHAR2,
1765 display_type IN VARCHAR2,
1766 document IN OUT NOCOPY CLOB,
1767 document_type IN OUT NOCOPY VARCHAR2)
1768 IS
1769
1770 NL VARCHAR2(1) := fnd_global.newline;
1771 l_document VARCHAR2(32000) := '';
1772 l_note VARCHAR2(32000) := '';
1773 l_enterprisename VARCHAR2(1000) := '';
1774 l_url VARCHAR2(3000) := '';
1775 l_email VARCHAR2(1000) := '';
1776 l_username VARCHAR2(500) := '';
1777 l_password VARCHAR2(100) := '';
1778 l_disp_type VARCHAR2(20) := 'text/plain';
1779 l_item_type wf_items.item_type%TYPE;
1780 l_item_key wf_items.item_key%TYPE;
1781 BEGIN
1782
1783 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1784 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.GENERATE_APPRV_REG_USR_BODY', 'p_document_id ' || p_document_id);
1785 END IF;
1786
1787 l_item_type := substr(p_document_id, 1, instr(p_document_id, ':') - 1);
1788 l_item_key := substr(p_document_id, instr(p_document_id, ':') + 1, length(p_document_id));
1789
1790 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1791 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.GENERATE_APPRV_REG_USR_BODY', 'l_item_type ' || l_item_type);
1792 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.GENERATE_APPRV_REG_USR_BODY', 'l_item_key ' || l_item_key);
1793 END IF;
1794
1795 l_enterprisename := wf_engine.GetItemAttrText (itemtype => l_item_type,
1796 itemkey => l_item_key,
1797 aname => 'ENTERPRISE_NAME');
1798 l_url := wf_engine.GetItemAttrText (itemtype => l_item_type,
1799 itemkey => l_item_key,
1800 aname => 'LOGON_PAGE_URL');
1801 l_username := wf_engine.GetItemAttrText (itemtype => l_item_type,
1802 itemkey => l_item_key,
1803 aname => 'ASSIGNED_USER_NAME');
1804 l_password := wf_engine.GetItemAttrText (itemtype => l_item_type,
1805 itemkey => l_item_key,
1806 aname => 'FIRST_LOGON_KEY');
1807 l_email := wf_engine.GetItemAttrText (itemtype => l_item_type,
1808 itemkey => l_item_key,
1809 aname => 'CONTACT_EMAIL');
1810
1811 l_note := wf_engine.GetItemAttrText (itemtype => l_item_type,
1812 itemkey => l_item_key,
1813 aname => 'NOTE');
1814
1815 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1816 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.GENERATE_APPRV_REG_USR_BODY', 'l_enterprisename ' || l_enterprisename);
1817 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.GENERATE_APPRV_REG_USR_BODY', 'l_url ' || l_url);
1818 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.GENERATE_APPRV_REG_USR_BODY', 'l_username ' || l_username);
1819 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.GENERATE_APPRV_REG_USR_BODY', 'l_password ' || l_password);
1820 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.GENERATE_APPRV_REG_USR_BODY', 'l_adminemail ' || l_email);
1821 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.GENERATE_APPRV_REG_USR_BODY', 'l_note ' || l_note);
1822 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.GENERATE_APPRV_REG_USR_BODY', 'display_type ' || display_type);
1823 END IF;
1824
1825 IF display_type = 'text/html' THEN
1826 l_disp_type:= display_type;
1827 fnd_message.set_name('POS','POS_APPRV_REG_USER_HTML_BODY');
1828 fnd_message.set_token('ENTERPRISE_NAME',l_enterprisename);
1829 fnd_message.set_token('LOGON_PAGE_URL',l_url);
1830 fnd_message.set_token('ASSIGNED_USER_NAME',l_username);
1831 fnd_message.set_token('FIRST_LOGON_KEY',l_password);
1832 fnd_message.set_token('CONTACT_EMAIL',l_email);
1833 fnd_message.set_token('NOTE',l_note);
1834 l_document := l_document || NL || NL || fnd_message.get;
1835 WF_NOTIFICATION.WriteToClob(document, l_document);
1836
1837 ELSE
1838 l_disp_type:= display_type;
1839 fnd_message.set_name('POS','POS_APPRV_REG_USER_TEXT_BODY');
1840 fnd_message.set_token('ENTERPRISE_NAME',l_enterprisename);
1841 fnd_message.set_token('LOGON_PAGE_URL',l_url);
1842 fnd_message.set_token('ASSIGNED_USER_NAME',l_username);
1843 fnd_message.set_token('FIRST_LOGON_KEY',l_password);
1844 fnd_message.set_token('CONTACT_EMAIL',l_email);
1845 fnd_message.set_token('NOTE',l_note);
1846 l_document := l_document || NL || NL || fnd_message.get;
1847 WF_NOTIFICATION.WriteToClob(document, l_document);
1848
1849 END IF;
1850
1851 EXCEPTION
1852 WHEN OTHERS THEN
1853 RAISE;
1854 END GENERATE_APPRV_REG_USR_BODY;
1855
1856 END pos_supplier_user_reg_pkg;