eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.POS_SUPPLIER_USER_REG_PKG

Source


1 PACKAGE BODY POS_SUPPLIER_USER_REG_PKG AS
2 /* $Header: POSUREGB.pls 120.12 2006/11/29 02:12:54 shgao noship $ */
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_local_user_name;
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 BEGIN
948    IF p_is_invited = 'Y' THEN
949       l_process := 'SEND_APPRV_INV_USER_NTF';
950     ELSE
951       is_osn := is_osnrequest(p_registration_id);
952       IF (is_osn = 'Y') THEN
953         l_process := 'SEND_OSN_REG_USER_NTF';
954       ELSIF (p_is_user_in_oid = 'Y') THEN
955         l_process := 'SEND_APPRV_USER_SSOSYNC_NTF';
956       ELSE
957         l_process := 'SEND_APPRV_REG_USER_NTF';
958       END IF;
959    END IF;
960 
961    l_itemtype := 'POSREGV2';
962    l_itemkey := gen_wf_item_key(p_registration_id,'approve');
963 
964    wf_engine.CreateProcess(itemtype => l_itemtype,
965                            itemkey  => l_itemkey,
966                            process  => l_process
967                            );
968 
969    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
970                               itemkey    => l_itemkey,
971                               aname      => 'LOGON_PAGE_URL',
972                               avalue     => pos_url_pkg.get_external_login_url
973                               );
974 
975    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
976                               itemkey    => l_itemkey,
977                               aname      => 'ASSIGNED_USER_NAME',
978                               avalue     => p_user_name
979                               );
980 
981    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
982                               itemkey    => l_itemkey,
983                               aname      => 'FIRST_LOGON_KEY',
984                               avalue     => p_password
985                               );
986 
987    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
988                               itemkey    => l_itemkey,
989                               aname      => 'CONTACT_EMAIL',
990                               avalue     => get_contact_email
991                               );
992 
993    IF p_is_invited <> 'Y' THEN
994       wf_engine.SetItemAttrText (itemtype   => l_itemtype,
995                                  itemkey    => l_itemkey,
996                                  aname      => 'NOTE',
997                                  avalue     => get_note(p_registration_id)
998                                  );
999    END IF;
1000 
1001    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1002                               itemkey    => l_itemkey,
1003                               aname      => 'ENTERPRISE_NAME',
1004                               avalue     => get_enterprise_name
1005                               );
1006 
1007    wf_engine.StartProcess (itemtype => l_itemtype,
1008                            itemkey  => l_itemkey
1009                            );
1010 
1011 END send_approval_ntf;
1012 
1013 PROCEDURE send_respond_ntf
1014   (p_registration_id IN NUMBER,
1015    p_first_name      IN VARCHAR2,
1016    p_last_name       IN VARCHAR2,
1017    p_vendor_name     IN VARCHAR2,
1018    p_approver_role   IN VARCHAR2
1019    )
1020   IS
1021      l_itemtype        wf_items.item_type%TYPE;
1022      l_itemkey         wf_items.item_key%TYPE;
1023      l_local_user_name wf_local_users.name%TYPE;
1024      l_process         wf_process_activities.process_name%TYPE;
1025      is_osn            VARCHAR2(1);
1026      lv_approver_role  WF_USER_ROLES.ROLE_NAME%TYPE;
1027 BEGIN
1028 
1029    l_itemtype := 'POSREGV2';
1030    is_osn := is_osnrequest(p_registration_id);
1031    l_itemkey := gen_wf_item_key(p_registration_id,'respond');
1032    IF (is_osn = 'Y') THEN
1033      l_process := 'SEND_OSN_ADMIN_NTF';
1034    ELSE
1035      l_process := 'SEND_REG_ADMIN_NTF';
1036    END IF;
1037 
1038    wf_engine.CreateProcess(itemtype => l_itemtype,
1039                            itemkey  => l_itemkey,
1040                            process  => l_process
1041                            );
1042 
1043    IF (is_osn = 'Y') THEN
1044      lv_approver_role := get_approver_role_for_osn();
1045      wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1046                               itemkey    => l_itemkey,
1047                               aname      => 'APPROVER_ROLE',
1048                               avalue     => lv_approver_role
1049                               );
1050    ELSE
1051      wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1052                               itemkey    => l_itemkey,
1053                               aname      => 'APPROVER_ROLE',
1054                               avalue     => p_approver_role
1055                               );
1056    END IF;
1057 
1058    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1059                               itemkey    => l_itemkey,
1060                               aname      => 'FIRST_NAME',
1061                               avalue     => p_first_name
1062                               );
1063 
1064    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1065                               itemkey    => l_itemkey,
1066                               aname      => 'LAST_NAME',
1067                               avalue     => p_last_name
1068                               );
1069 
1070    IF (is_osn = 'Y' AND p_vendor_name IS NULL) THEN
1071      wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1072                               itemkey    => l_itemkey,
1073                               aname      => 'VENDOR_NAME',
1074                               avalue     => get_tp_name(p_registration_id)
1075                               );
1076    ELSE
1077      wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1078                               itemkey    => l_itemkey,
1079                               aname      => 'VENDOR_NAME',
1080                               avalue     => p_vendor_name
1081                               );
1082    END IF;
1083 
1084    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1085                               itemkey    => l_itemkey,
1086                               aname      => 'LOGON_PAGE_URL',
1087                               avalue     => pos_url_pkg.get_internal_login_url
1088                               );
1089 
1090    wf_engine.StartProcess (itemtype => l_itemtype,
1091                            itemkey  => l_itemkey
1092                            );
1093 
1094 END send_respond_ntf;
1095 
1096 PROCEDURE send_invitation_ntf
1097   (p_registration_id IN NUMBER,
1098    p_email           IN VARCHAR2,
1099    p_language_code   IN VARCHAR2,
1100    p_note            IN VARCHAR2,
1101    p_invitation_key  IN VARCHAR2
1102    )
1103   IS
1104      l_itemtype        wf_items.item_type%TYPE;
1105      l_itemkey         wf_items.item_key%TYPE;
1106      l_local_user_name wf_local_users.name%TYPE;
1107      l_process         wf_process_activities.process_name%TYPE;
1108 BEGIN
1109 
1110    l_itemtype := 'POSREGV2';
1111    l_itemkey := gen_wf_item_key(p_registration_id,'invite');
1112    l_process := 'SEND_INV_USER_NTF';
1113    l_local_user_name := gen_local_user_name(p_registration_id, 'invite');
1114 
1115    create_local_user
1116      (p_local_user_name => l_local_user_name,
1117       p_email           => p_email,
1118       p_language_code   => p_language_code
1119       );
1120 
1121    wf_engine.CreateProcess(itemtype => l_itemtype,
1122                            itemkey  => l_itemkey,
1123                            process  => l_process
1124                            );
1125 
1126    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1127                               itemkey    => l_itemkey,
1128                               aname      => 'ADHOC_USER_NAME',
1129                               avalue     => l_local_user_name
1130                               );
1131 
1132    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1133                               itemkey    => l_itemkey,
1134                               aname      => 'ENTERPRISE_NAME',
1135                               avalue     => get_enterprise_name
1136                               );
1137 
1138    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1139                               itemkey    => l_itemkey,
1140                               aname      => 'REG_PAGE_URL',
1141                               avalue     => get_reg_page_url(p_invitation_key,
1142                                                              p_language_code)
1143                               );
1144 
1145    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1146                               itemkey    => l_itemkey,
1147                               aname      => 'NOTE',
1148                               avalue     => p_note
1149                               );
1150 
1151    wf_engine.StartProcess (itemtype => l_itemtype,
1152                            itemkey  => l_itemkey
1153                            );
1154 
1155 END send_invitation_ntf;
1156 
1157 PROCEDURE send_rejection_ntf
1158   (p_registration_id IN NUMBER,
1159    p_email           IN VARCHAR2,
1160    p_language_code   IN VARCHAR2
1161    )
1162   IS
1163      l_itemtype        wf_items.item_type%TYPE;
1164      l_itemkey         wf_items.item_key%TYPE;
1165      l_local_user_name wf_local_users.name%TYPE;
1166      l_process         wf_process_activities.process_name%TYPE;
1167 BEGIN
1168 
1169    l_itemtype := 'POSREGV2';
1170    l_itemkey := gen_wf_item_key(p_registration_id,'reject');
1171    l_process := 'SEND_RJCT_USER_NTF';
1172    l_local_user_name := gen_local_user_name(p_registration_id, 'reject');
1173 
1174    create_local_user
1175      (p_local_user_name => l_local_user_name,
1176       p_email           => p_email,
1177       p_language_code   => p_language_code
1178       );
1179 
1180    wf_engine.CreateProcess(itemtype => l_itemtype,
1181                            itemkey  => l_itemkey,
1182                            process  => l_process
1183                            );
1184 
1185    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1186                               itemkey    => l_itemkey,
1187                               aname      => 'ADHOC_USER_NAME',
1188                               avalue     => l_local_user_name
1189                               );
1190 
1191    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1192                               itemkey    => l_itemkey,
1193                               aname      => 'CONTACT_EMAIL',
1194                               avalue     => get_contact_email
1195                               );
1196 
1197    wf_engine.SetItemAttrText (itemtype   => l_itemtype,
1198                               itemkey    => l_itemkey,
1199                               aname      => 'ENTERPRISE_NAME',
1200                               avalue     => get_enterprise_name
1201                               );
1202    wf_engine.StartProcess (itemtype => l_itemtype,
1203                            itemkey  => l_itemkey
1204                            );
1205 
1206 END send_rejection_ntf;
1207 
1208 FUNCTION is_invited
1209   (p_registration_id IN NUMBER) RETURN VARCHAR2
1210   IS
1211      CURSOR l_cur IS
1212         SELECT field_value_string
1213           FROM fnd_registration_details
1214          WHERE field_name = 'Invited Flag'
1215            AND registration_id = p_registration_id;
1216 
1217      l_value fnd_registration_details.field_value_string%TYPE;
1218 BEGIN
1219    OPEN l_cur;
1220    FETCH l_cur INTO l_value;
1221    CLOSE l_cur;
1222 
1223    IF l_value IS NULL THEN
1224       RETURN NULL;
1225    END IF;
1226 
1227    IF l_value = 'Y' OR l_value = 'y' THEN
1228       RETURN 'Y';
1229    END IF;
1230 
1231    RETURN 'N';
1232 
1233 END is_invited;
1234 
1235 PROCEDURE approve
1236   (p_registration_id IN  NUMBER,
1237    x_return_status   OUT nocopy VARCHAR2,
1238    x_msg_count       OUT nocopy NUMBER,
1239    x_msg_data        OUT nocopy VARCHAR2
1240    )
1241   IS
1242      CURSOR l_cur IS
1243         SELECT pv.party_id vendor_party_id,
1244                fr.*, jobt.field_value_string
1245           FROM po_vendors pv,
1246                fnd_registration_details frd,
1247                fnd_registrations fr, fnd_registration_details jobt
1248          WHERE frd.registration_id = fr.registration_id
1249            AND pv.vendor_id = frd.field_value_number
1250            AND frd.field_name = 'Supplier Number'
1251            AND jobt.field_name = 'Job Title'
1252            AND fr.registration_id = jobt.registration_id
1253            AND fr.application_id = jobt.application_id
1254            AND fr.registration_type = jobt.registration_type
1255            AND fr.registration_id = p_registration_id;
1256 
1257      l_rec  l_cur%ROWTYPE;
1258 
1259      l_person_party_id    NUMBER;
1260      l_password           VARCHAR2(100);
1261      l_password_returned  VARCHAR2(100);
1262      l_user_id            NUMBER;
1263      l_user_in_oid        VARCHAR2(1);
1264 
1265 BEGIN
1266    SAVEPOINT supplier_user_reg_approve_sp;
1267 
1268    lock_reg (p_registration_id);
1269 
1270    OPEN l_cur;
1271    FETCH l_cur INTO l_rec;
1272    IF l_cur%notfound THEN
1273       CLOSE l_cur;
1274       x_return_status := fnd_api.g_ret_sts_error;
1275       x_msg_count := 1;
1276       x_msg_data := 'Can not find registration data for registration ' || p_registration_id;
1277       RETURN;
1278    END IF;
1279    CLOSE l_cur;
1280 
1281    l_password := get_password (p_registration_id);
1282 
1283    -- create supplier contact
1284    pos_supp_contact_pkg.create_supplier_contact
1285     (p_vendor_party_id => l_rec.vendor_party_id,
1286      p_first_name      => l_rec.first_name,
1287      p_last_name       => l_rec.last_name,
1288      p_middle_name     => l_rec.middle_name,
1289      p_contact_title   => l_rec.user_title,
1290      p_job_title       => l_rec.field_value_string,
1291      p_phone_area_code => l_rec.phone_area_code,
1292      p_phone_number    => l_rec.phone,
1293      p_phone_extension => l_rec.phone_extension,
1294      p_fax_area_code   => l_rec.fax_area_code,
1295      p_fax_number      => l_rec.fax,
1296      p_email_address   => l_rec.email,
1297      x_return_status   => x_return_status,
1298      x_msg_count       => x_msg_count,
1299      x_msg_data        => x_msg_data,
1300      x_person_party_id => l_person_party_id
1301      );
1302 
1303    IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1304       ROLLBACK TO supplier_user_reg_approve_sp;
1305       RETURN;
1306    END IF;
1307 
1308    l_user_in_oid := 'N';
1309    if (FND_USER_PKG.TestUserName(l_rec.requested_user_name) = FND_USER_PKG.USER_SYNCHED) then
1310      l_user_in_oid := 'Y';
1311    end if;
1312 
1313    -- create supplier user account
1314    pos_user_admin_pkg.create_supplier_user_account
1315      (p_user_name         => l_rec.requested_user_name,
1316       p_user_email        => l_rec.email,
1317       p_person_party_id   => l_person_party_id,
1318       p_password          => l_password,
1319       x_return_status     => x_return_status,
1320       x_msg_count         => x_msg_count,
1321       x_msg_data          => x_msg_data,
1322       x_user_id           => l_user_id,
1323       x_password          => l_password_returned
1324       );
1325 
1326    IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1327       ROLLBACK TO supplier_user_reg_approve_sp;
1328       RETURN;
1329    END IF;
1330 
1331    -- grant responsibilities
1332    grant_resps(p_registration_id => p_registration_id,
1333                p_user_id         => l_user_id,
1334                x_return_status   => x_return_status,
1335                x_msg_count       => x_msg_count,
1336                x_msg_data        => x_msg_data
1337                );
1338 
1339    IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1340       ROLLBACK TO supplier_user_reg_approve_sp;
1341       RETURN;
1342    END IF;
1343 
1344    -- set sec attrs
1345    set_sec_attrs(p_registration_id => p_registration_id,
1346                  p_user_id         => l_user_id,
1347                  x_return_status   => x_return_status,
1348                  x_msg_count       => x_msg_count,
1349                  x_msg_data        => x_msg_data
1350                );
1351 
1352    IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1353       ROLLBACK TO supplier_user_reg_approve_sp;
1354       RETURN;
1355    END IF;
1356 
1357    set_user_profile ( p_user_id         => l_user_id,
1358                       p_registration_id => p_registration_id,
1359                       x_return_status   => x_return_status,
1360                       x_msg_count       => x_msg_count,
1361                       x_msg_data        => x_msg_data
1362                       );
1363 
1364    IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1365       ROLLBACK TO supplier_user_reg_approve_sp;
1366       RETURN;
1367    END IF;
1368 
1369    UPDATE fnd_registrations
1370      SET registration_status = 'APPROVED',
1371          last_update_date = Sysdate,
1372          last_update_login = fnd_global.login_id,
1373          last_updated_by = fnd_global.user_id
1374      WHERE registration_id = p_registration_id;
1375 
1376    send_approval_ntf
1377      (p_registration_id => p_registration_id,
1378       p_is_invited      => is_invited(p_registration_id),
1379       p_is_user_in_oid  => l_user_in_oid,
1380       p_user_name       => Upper(l_rec.requested_user_name),
1381       p_password        => l_password
1382       );
1383 
1384    IF x_return_status IS NULL OR x_return_status <> fnd_api.g_ret_sts_success THEN
1385       ROLLBACK TO supplier_user_reg_approve_sp;
1386       RETURN;
1387    END IF;
1388 
1389    x_return_status := fnd_api.g_ret_sts_success;
1390 
1391 EXCEPTION
1392    WHEN OTHERS THEN
1393       ROLLBACK TO supplier_user_reg_approve_sp;
1394       x_return_status := fnd_api.g_ret_sts_unexp_error;
1395       x_msg_data := Sqlerrm;
1396       x_msg_count := 1;
1397       RETURN;
1398 END approve;
1399 
1400 PROCEDURE reject
1401   (p_registration_id IN  NUMBER,
1402    x_return_status   OUT nocopy VARCHAR2,
1403    x_msg_count       OUT nocopy NUMBER,
1404    x_msg_data        OUT nocopy VARCHAR2
1405    )
1406   IS
1407      l_email         fnd_registrations.email%TYPE;
1408      l_language_code fnd_registrations.language_code%TYPE;
1409 BEGIN
1410    SAVEPOINT supplier_user_reject_sp;
1411    UPDATE fnd_registrations
1412      SET registration_status = 'REJECTED',
1413          last_update_date = Sysdate,
1414          last_update_login = fnd_global.login_id,
1415          last_updated_by = fnd_global.user_id
1416      WHERE registration_id = p_registration_id;
1417 
1418    SELECT email, language_code
1419      INTO l_email, l_language_code
1420      FROM fnd_registrations
1421     WHERE registration_id = p_registration_id;
1422 
1423    send_rejection_ntf
1424      (p_registration_id => p_registration_id,
1425       p_email           => l_email,
1426       p_language_code   => l_language_code
1427       );
1428 
1429    x_return_status := fnd_api.g_ret_sts_success;
1430 
1431 EXCEPTION
1432    WHEN OTHERS THEN
1433       ROLLBACK TO supplier_user_reg_reject_sp;
1434       x_return_status := fnd_api.g_ret_sts_unexp_error;
1435       x_msg_data := Sqlerrm;
1436       x_msg_count := 1;
1437       RETURN;
1438 END reject;
1439 
1440 PROCEDURE invite
1441   (p_registration_id IN  NUMBER,
1442    x_return_status   OUT nocopy VARCHAR2,
1443    x_msg_count       OUT nocopy NUMBER,
1444    x_msg_data        OUT nocopy VARCHAR2
1445    )
1446   IS
1447      l_email         fnd_registrations.email%TYPE;
1448      l_language_code fnd_registrations.language_code%TYPE;
1449      l_reg_key       fnd_registrations.registration_key%TYPE;
1450 BEGIN
1451    SAVEPOINT supplier_user_invite_sp;
1452    UPDATE fnd_registrations
1453      SET registration_status = 'INVITED',
1454          last_update_date = Sysdate,
1455          last_update_login = fnd_global.login_id,
1456          last_updated_by = fnd_global.user_id
1457      WHERE registration_id = p_registration_id;
1458 
1459    SELECT email, language_code, registration_key
1460      INTO l_email, l_language_code, l_reg_key
1461      FROM fnd_registrations
1462     WHERE registration_id = p_registration_id;
1463 
1464    send_invitation_ntf
1465      (p_registration_id => p_registration_id,
1466       p_email           => l_email,
1467       p_language_code   => l_language_code,
1468       p_note            => get_note(p_registration_id),
1469       p_invitation_key  => l_reg_key
1470       );
1471    x_return_status := fnd_api.g_ret_sts_success;
1472 EXCEPTION
1473    WHEN OTHERS THEN
1474       x_return_status := fnd_api.g_ret_sts_unexp_error;
1475       x_msg_count := 1;
1476       x_msg_data := Sqlerrm;
1477       ROLLBACK TO supplier_user_invite_sp;
1478 END invite;
1479 
1480 PROCEDURE respond
1481   (p_registration_id IN  NUMBER,
1482    x_return_status   OUT nocopy VARCHAR2,
1483    x_msg_count       OUT nocopy NUMBER,
1484    x_msg_data        OUT nocopy VARCHAR2
1485    )
1486   IS
1487      CURSOR l_cur IS
1488         SELECT fr.first_name, fr.last_name,
1489                fr.email, fr.language_code, fr.registration_key,
1490                frd1.field_value_string vendor_name,
1491                frd2.field_value_number approver_id,
1492                fu.user_name
1493           FROM fnd_registration_details frd1,
1494                fnd_registration_details frd2,
1495                fnd_registrations fr,
1496                fnd_user fu
1497          WHERE frd1.registration_id = fr.registration_id
1498            AND frd1.field_name = 'Supplier Name'
1499            AND frd2.registration_id = fr.registration_id
1500            AND frd2.field_name = 'Approver ID'
1501            AND fr.registration_id = p_registration_id
1502            AND fu.user_id = frd2.field_value_number;
1503 
1504      l_rec l_cur%ROWTYPE;
1505 BEGIN
1506    SAVEPOINT supplier_user_respond_sp;
1507    UPDATE fnd_registrations
1508      SET registration_status = 'REGISTERED',
1509          last_update_date = Sysdate,
1510          last_update_login = fnd_global.login_id,
1511          last_updated_by = fnd_global.user_id
1512      WHERE registration_id = p_registration_id;
1513 
1514    OPEN l_cur;
1515    FETCH l_cur INTO l_rec;
1516    IF l_cur%notfound THEN
1517       CLOSE l_cur;
1518       RAISE no_data_found;
1519    END IF;
1520    CLOSE l_cur;
1521 
1522    send_respond_ntf
1523      (p_registration_id => p_registration_id,
1524       p_first_name      => l_rec.first_name,
1525       p_last_name       => l_rec.last_name,
1526       p_vendor_name     => l_rec.vendor_name,
1527       p_approver_role   => l_rec.user_name
1528       );
1529 
1530    x_return_status := fnd_api.g_ret_sts_success;
1531 EXCEPTION
1532    WHEN OTHERS THEN
1533       x_return_status := fnd_api.g_ret_sts_unexp_error;
1534       x_msg_count := 1;
1535       x_msg_data := Sqlerrm;
1536       ROLLBACK TO supplier_user_respond_sp;
1537 END respond;
1538 
1539 /*---------------------------------------
1540 
1541 public
1542 Procedure to set profile options for external user.
1543 This procedure set the APPS_FRAMEWORK_AGENT and
1544 APPS_WEB_AGENT for external user
1545 
1546 */
1547 
1548 PROCEDURE set_profile_opt_ext_user
1549 (p_userid in number)
1550 is
1551 lv_external_web_agent fnd_profile_option_values.profile_option_value%TYPE := NULL;
1552 lv_ext_servlet_agent fnd_profile_option_values.profile_option_value%TYPE := NULL;
1553 lv_pattern   VARCHAR2(40);
1554 lv_flag      VARCHAR2(40);
1555 lv_proc_name VARCHAR2(30) := 'set_profile_opt_ext_user';
1556 begin
1557 
1558   fnd_profile.get('POS_EXTERNAL_URL', lv_external_web_agent);
1559   fnd_profile.get('POS_EXTERNAL_URL', lv_ext_servlet_agent);
1560 
1561   IF ( lv_external_web_agent IS NOT NULL ) THEN
1562 
1563      lv_pattern := '/pls';
1564      lv_flag    := ''; -- we want it to be case sensitive for now.
1565      If (owa_pattern.match(lv_external_web_agent,lv_pattern, lv_flag)) then
1566         -- The external profile still points to icx web site.
1567 
1568         IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1569           fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Setting user level APPS_WEB_AGENT profile option value');
1570         END IF;
1571 
1572         IF ( fnd_profile.save( x_name => 'APPS_WEB_AGENT',
1573                             x_value => lv_external_web_agent,
1574                             x_level_name => 'USER',
1575                             x_level_value => p_userid ) ) THEN
1576 
1577             IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1578               fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_WEB_AGENT profile option value set');
1579             END IF;
1580 
1581         ELSE
1582 
1583             IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1584               fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_WEB_AGENT profile option value fail');
1585             END IF;
1586 
1587         END IF;
1588 
1589         owa_pattern.change(lv_ext_servlet_agent, '/pls.*', '/OA_HTML');
1590         IF ( fnd_profile.save( x_name => 'APPS_SERVLET_AGENT',
1591                             x_value => lv_ext_servlet_agent,
1592                             x_level_name => 'USER',
1593                             x_level_value => p_userid ) ) THEN
1594 
1595             IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1596               fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_SERVLET_AGENT profile option value set');
1597             END IF;
1598 
1599         ELSE
1600 
1601             IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1602               fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_SERVLET_AGENT profile option value fail');
1603             END IF;
1604 
1605         END IF;
1606 
1607         owa_pattern.change(lv_ext_servlet_agent, '/OA_HTML.*', '');
1608         IF ( fnd_profile.save( x_name => 'APPS_FRAMEWORK_AGENT',
1609                             x_value => lv_ext_servlet_agent,
1610                             x_level_name => 'USER',
1611                             x_level_value => p_userid ) ) THEN
1612 
1613             IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1614               fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value set');
1615             END IF;
1616 
1617         ELSE
1618 
1619             IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1620               fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value fail');
1621             END IF;
1622 
1623         END IF;
1624      ELSE
1625         IF ( fnd_profile.save( x_name => 'APPS_FRAMEWORK_AGENT',
1626                             x_value => lv_ext_servlet_agent,
1627                             x_level_name => 'USER',
1628                             x_level_value => p_userid ) ) THEN
1629 
1630             IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1631               fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value set');
1632             END IF;
1633 
1634         ELSE
1635 
1636             IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1637               fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value fail');
1638             END IF;
1639 
1640         END IF;
1641         -- set only the framework agent. there is no way to set web agent
1642         -- as we dont know the external dbc name.
1643         lv_ext_servlet_agent := lv_ext_servlet_agent || '/OA_HTML';
1644         IF ( fnd_profile.save( x_name => 'APPS_SERVLET_AGENT',
1645                             x_value => lv_ext_servlet_agent,
1646                             x_level_name => 'USER',
1647                             x_level_value => p_userid ) ) THEN
1648 
1649             IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1650               fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_servlet_AGENT profile option value set');
1651             END IF;
1652 
1653         ELSE
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_servlet_AGENT profile option value fail');
1657             END IF;
1658 
1659         END IF;
1660      End if;
1661 
1662   ELSE
1663 
1664      IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1665        fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'POS_EXTERNAL_URL is not set');
1666      END IF;
1667 
1668   END IF;
1669 
1670 
1671 end set_profile_opt_ext_user;
1672 
1673 END pos_supplier_user_reg_pkg;