[Home] [Help]
PACKAGE BODY: APPS.POS_REG_WF_PKG
Source
1 package body POS_REG_WF_PKG as
2 /* $Header: POSREGWB.pls 120.5.12010000.2 2008/09/20 17:47:13 sthoppan ship $ */
3
4 /*---------------------------------------
5
6 private constants used by this package
7
8 ----------------------------------------*/
9
10 -- user responsibility profile options
11 PON_USR_RESP_PROF CONSTANT VARCHAR(30) := 'PON_DEFAULT_EXT_USER_RESP';
12 POS_USR_RESP_PROF CONSTANT VARCHAR(30) := 'POS_DEFAULT_EXT_USER_RESP';
13 MSC_USR_RESP_PROF CONSTANT VARCHAR(30) := 'POS_COLLAB_RESPONSIBILITY';
14 POS_ISP_COLLAB_RESP_PROF CONSTANT VARCHAR(30) := 'POS_ISP_COLLAB_RESP';
15
16 -- the application shortname for FND message stack
17 -- we use '' so that we get clean message text
18 FND_MSG_APP CONSTANT VARCHAR2(3) := '';
19
20 -- the default security attribute value for supplier site and contact
21 pos_default_sec_attr_value CONSTANT NUMBER := -9999;
22
23 -- the invitation response page URL
24 POS_INV_REPLY_PAGE CONSTANT VARCHAR2(4000) := 'OA_HTML/jsp/pos/registration/RegistrationReply.jsp?registrationKey=';
25
26 -- the user approval page URL
27 POS_APPROVAL_PAGE CONSTANT VARCHAR2(4000) := 'OA_HTML/OA.jsp?akRegionCode=POS_APPROVE_MAIN_RGN&akRegionApplicationId=177®istrationKey=';
28
29 -- a global error message holder
30 ERROR_MESSAGE FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := NULL;
31
32 -- error message names
33 MSG_USER_RESP_FAIL CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_USER_RESP_FAIL';
34
35 MSG_UNEXPECTED_ERROR CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_REG_UNEXPECTED_ERR';
36 MSG_FND_USER_DUPLICATE CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'SECURITY-DUPLICATE USER';
37 MSG_USER_CREATION_FAIL CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_REG_USER_CREATION_FAIL';
38 MSG_VENDORUSER_CREATION_FAIL CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_REG_VNDR_USR_CREATE_FAIL';
39 MSG_POS_SITE_SECATTR_FAIL CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_REG_SITE_SECATTR_FAIL';
40 MSG_POS_CONTACT_SECATTR_FAIL CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_REG_CONT_SECATTR_FAIL';
41 MSG_POS_SEC_ATTR_FAIL CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_REG_SECATTR_FAIL';
42 MSG_REG_LOCK_FAIL CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_REG_REGLOCK_FAIL';
43 MSG_APPRV_LOCK_FAIL CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_REG_APPRVLOCK_FAIL';
44 MSG_REG_DATA_MISSING CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_REG_DATA_MISSING';
45 MSG_POS_SUPP_SECATTR_FAIL CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_SUPP_SECATTR_FAIL';
46 MSG_VENDORUSER_DETAILS_FAIL CONSTANT FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := 'POS_REG_VNDR_USR_DETAIL_FAIL';
47
48 -- global variable for logging
49 g_log_module_name VARCHAR2(30) := 'pos.plsql.POSREGWB';
50 g_log_proc_start VARCHAR2(30) := 'start';
51 g_log_proc_end VARCHAR2(30) := 'end';
52 g_log_reg_key_notfound VARCHAR2(30) := 'Registration key is NOT FOUND.';
53 g_log_reg_id_invalid VARCHAR2(30) := 'Registration ID is INVALID.';
54
55 TYPE g_refcur IS REF CURSOR;
56
57 /*----------------------------------------
58 PRIVATE FUNCTION decrypt
59 decrypt initial password set by OSN
60 registration request
61 ----------------------------------------*/
62
63 function decrypt(key in varchar2, value in varchar2) return varchar2
64 as language java name 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
65
66 /*******************************************************************
67 PROCEDURE NAME: get_wf_role_for_users
68 this is the same function as that in PO_REQAPPROVAL_INIT1 (private func)
69
70 DESCRIPTION :
71 Given a list of users, the procedure looks through the wf_user_roles
72 to get a role that has exactly same set of input list of users.
73
74 parameters :
75 Input:
76 p_list_of_users - String containing the list of users
77 Example string: 'GE1', 'GE2', 'GE22'
78 p_num_users - number of users in the above list
79 Output:
80 A string containg the role name ( or null , if such role
81 does not exist ).
82 *******************************************************************/
83
84 FUNCTION get_wf_role_for_users(p_list_of_users in varchar2, p_num_users in number) return varchar2 IS
85 l_refcur g_refcur;
86 l_role_name WF_USER_ROLES.ROLE_NAME%TYPE;
87 l_progress varchar2(255);
88 BEGIN
89 open l_refcur for
90 --'select role_name , count(role_name)
91 'select role_name
92 from wf_user_roles
93 where
94 role_name in
95 (
96 select role_name
97 from wf_user_roles
98 where user_name in (' || p_list_of_users || ')
99 group by role_name
100 having count(role_name) = :1
101 )
102 group by role_name
103 having count(role_name) = :2 '
104 using p_num_users, p_num_users;
105
106 LOOP
107 fetch l_refcur into l_role_name;
108 if l_refcur%notfound then
109 exit;
110 end if;
111 close l_refcur;
112 exit;
113 END LOOP;
114 return l_role_name;
115 EXCEPTION
116 WHEN OTHERS THEN
117 return null;
118 END;
119
120 /*----------------------------------------
121 function get_approver_role_for_osn_request
122 for user registration coming from Oracle
123 Supplier Network. We want the notification
124 be sent to all users that can approve
125 external user registrations.
126
127 this function will create a role for this
128 user list, so that notification REG_ADMIN_NTF
129 will be sent to this role.
130 ----------------------------------------*/
131
132 function get_approver_role_for_osn
133 return varchar2
134
135 is
136 x_refcur g_refcur;
137 l_approver_func_id NUMBER := null;
138 l_user_name FND_USER.USER_NAME%TYPE := null;
139 l_num_users number := 0;
140 l_approverlist varchar2(2000):=null;
141 l_approverlist_sql varchar2(2000):=null;
142 l_role_name WF_USER_ROLES.ROLE_NAME%TYPE := null;
143 l_role_display_name varchar2(100):=null;
144 l_expiration_date DATE;
145
146 begin
147
148 --step 1: find the users that can approve external user registrations
149
150 SELECT function_id
151 INTO l_approver_func_id
152 FROM fnd_form_functions
153 WHERE function_name = 'POS_REG_APPROVE_EXT_USERS';
154
155 OPEN x_refcur FOR
156 'SELECT DISTINCT fu.user_name
157 FROM fnd_user fu,
158 fnd_responsibility fr,
159 wf_user_roles wur
160 WHERE fr.menu_id in
161 (SELECT fme.menu_id
162 FROM fnd_menu_entries fme
163 START WITH fme.function_id = :1
164 CONNECT BY PRIOR menu_id = sub_menu_id
165 )
166 AND fr.application_id = 177
167 AND wur.role_name like ''FND_RESP|%|%|STANDARD''
168 AND WUR.ROLE_ORIG_SYSTEM = ''FND_RESP''
169 AND WUR.ROLE_ORIG_SYSTEM_ID = FR.RESPONSIBILITY_ID
170 AND WUR.ASSIGNMENT_TYPE IN (''D'', ''B'')
171 AND wur.user_name = fu.user_name'
172 using l_approver_func_id;
173
174
175 --step 2: build the approver list
176
177 loop
178 fetch x_refcur into l_user_name;
179 exit when x_refcur%NOTFOUND;
180 l_num_users := l_num_users + 1;
181 if(l_approverlist is null) then
182 l_approverlist:=l_user_name;
183 l_approverlist_sql := ''''||l_user_name||'''';
184 else
185 l_approverlist:=l_approverlist || ' ' || l_user_name;
186 l_approverlist_sql:=l_approverlist_sql||','||''''||l_user_name||'''';
187 end if;
188 end loop;
189 close x_refcur;
190
191 --step 3: given the approver list, find an existing matching role
192 -- or create a new role
193
194 if(l_approverlist is not null) then
195 l_role_name:= get_wf_role_for_users(l_approverlist_sql, l_num_users);
196
197 if(l_role_name is null ) then
198
199 l_expiration_date := sysdate + 30; -- this role expires in 30 days
200
201 WF_DIRECTORY.CreateAdHocRole(l_role_name, l_role_display_name,
202 null,
203 null,
204 null,
205 'MAILHTML',
206 l_approverlist,
207 null,
208 null,
209 'ACTIVE',
210 l_expiration_date);
211 end if;
212
213 return l_role_name;
214
215 end if;
216
217 --the approver list is null
218 return null;
219
220 exception
221 when others then
222 return null;
223 end get_approver_role_for_osn;
224
225 /*----------------------------------------
226
227 private FUNCTION GetAdHocUserName
228
229 Generate an adhoc user name given a registration username
230
231 PARAMS:
232 p_username IN VARCHAR2 : the username a user normally use
233
234 RETURN:
235 VARCHAR2 : an adhoc username as a workflow local username
236
237 ----------------------------------------*/
238
239 FUNCTION GetAdHocUserName (p_username IN VARCHAR2) RETURN VARCHAR2
240 IS
241 lv_adhoc_username WF_LOCAL_USERS.NAME%TYPE;
242 BEGIN
243 -- it probably won't exceed the column restraint, but just to take precaution
244 lv_adhoc_username := 'POS_REG_' || p_username;
245 RETURN lv_adhoc_username;
246 END GetAdHocUserName;
247
248
249 /*----------------------------------------
250
251 private PROCEDURE ClearError
252
253 Set the error message to be NULL
254
255 PARAMS:
256 none
257
258 ----------------------------------------*/
259
260 PROCEDURE ClearError
261 IS
262 BEGIN
263 ERROR_MESSAGE := NULL;
264 END ClearError;
265
266 /*----------------------------------------
267
268 private FUNCTION CheckError
269
270 Check whether the error message has been set.
271
272 PARAMS:
273 none
274
275 RETURN:
276 VARCHAR2 : the error message that has been set, or NULL if not yet set
277
278 ----------------------------------------*/
279
280 FUNCTION CheckError RETURN VARCHAR2
281 IS
282 BEGIN
283 RETURN ERROR_MESSAGE;
284 END CheckError;
285
286
287 /*----------------------------------------
288
289 private PROCEDURE SetErrMsg
290
291 Private procedure. Put message on FND message stack to signal an error
292 attributes. This procedure only supports up to two tokens.
293
294 PARAMS:
295 p_err_msg IN VARCHAR2 : the FND message name
296 p_token1 IN VARCHAR2 DEFAULT NULL : the name of token 1
297 p_token1_val IN VARCHAR2 DEFAULT NULL : the token 1 value
298 p_token2 IN VARCHAR2 DEFAULT NULL : the name of token 2
299 p_token2_val IN VARCHAR2 DEFAULT NULL : the token 2 value
300 p_translate IN BOOLEAN DEFAULT TRUE : translation flag for tokens
301
302 ----------------------------------------*/
303
304 PROCEDURE SetErrMsg
305 (
306 p_err_msg IN VARCHAR2
307 , p_token1 IN VARCHAR2 DEFAULT NULL
308 , p_token1_val IN VARCHAR2 DEFAULT NULL
309 , p_token2 IN VARCHAR2 DEFAULT NULL
310 , p_token2_val IN VARCHAR2 DEFAULT NULL
311 , p_translate IN BOOLEAN DEFAULT TRUE
312 )
313
314 IS
315 lv_prev_msg FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
316 lv_proc_name VARCHAR2(30) := 'SetErrMsg';
317 BEGIN
318
319 -- just to lot previous messages if any
320 lv_prev_msg := FND_MESSAGE.get();
321
322 IF ( lv_prev_msg IS NOT NULL ) THEN
323
324 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
325 FND_LOG.string(fnd_log.level_statement, g_log_module_name || '.' || lv_proc_name, 'Previous error message: ' || lv_prev_msg);
326 END IF;
327
328 END IF;
329
330 ERROR_MESSAGE := p_err_msg;
331
332 FND_MESSAGE.set_name(FND_MSG_APP, p_err_msg);
333
334 IF ( p_token1 IS NOT NULL ) THEN
335 FND_MESSAGE.set_token(p_token1, p_token1_val, p_translate);
336 END IF;
337
338 IF ( p_token2 IS NOT NULL ) THEN
339 FND_MESSAGE.set_token(p_token2, p_token2_val, p_translate);
340 END IF;
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 RAISE;
345 END SetErrMsg;
346
347
348 /*----------------------------------------
349
350 private FUNCTION GetEnterpriseName
351
352 Returns the Enterprise Name
353
354 PARAMS:
355 none
356
357 RETURN:
358 VARCHAR2 : the Enterprise Name or NULL if error
359
360 ----------------------------------------*/
361
362 FUNCTION GetEnterpriseName RETURN VARCHAR2
363 IS
364
365 lv_party_name HZ_PARTIES.PARTY_NAME%TYPE;
366 lv_exception_msg VARCHAR2(32000);
367 lv_status VARCHAR2(240);
368 lv_proc_name VARCHAR2(30) := 'GetEnterpriseName';
369
370 BEGIN
371
372 POS_ENTERPRISE_UTIL_PKG.get_enterprise_party_name( lv_party_name, lv_exception_msg, lv_status);
373
374 IF ( lv_status <> 'S' ) THEN
375
376 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
377 FND_LOG.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Error getting the Enterprise Name: ' || lv_exception_msg);
378 END IF;
379
380 RETURN '';
381 ELSE
382 RETURN lv_party_name;
383 END IF;
384
385 EXCEPTION
386 WHEN OTHERS THEN
387 RETURN '';
388 END GetEnterpriseName;
389
390 /*----------------------------------------
391
392 private FUNCTION GetRegPageURL
393
394 Returns the URL of the registration page with the invitation key. The
395 URL will be in the format of protocol://host:port/page?param=val...
396
397 PARAMS:
398 p_inv_key IN VARCHAR2 : the invitation key
399
400 RETURN:
401 VARCHAR2 : the registration page with the invitation key, or NULL if error
402
403 ----------------------------------------*/
404
405 FUNCTION GetRegPageURL (
406 p_inv_key IN VARCHAR2,
407 p_reg_lang_code IN VARCHAR2
408 ) RETURN VARCHAR2
409 IS
410
411 BEGIN
412
413 RETURN pos_url_pkg.get_external_url || POS_INV_REPLY_PAGE || p_inv_key || '®Lang=' || p_reg_lang_code;
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 RETURN '';
418 END GetRegPageURL;
419
420
421 /*----------------------------------------
422
423 private FUNCTION GetApprPageURL
424
425 Returns the URL of the approval page with the registration key. The
426 URL will be in the format of protocol://host:port/page?param=val...
427
428 PARAMS:
429 p_reg_key IN VARCHAR2 : the registration key
430
431 RETURN:
432 VARCHAR2 : the approval page with the registsration key, or NULL if error
433
434 ----------------------------------------*/
435
436 FUNCTION GetApprPageURL (
437 p_reg_key IN VARCHAR2
438 ) RETURN VARCHAR2
439 IS
440
441 lv_fwk_agent VARCHAR2(4000) := NULL;
442
443 BEGIN
444
445 FND_PROFILE.get('APPS_FRAMEWORK_AGENT', lv_fwk_agent);
446
447
448 IF ( lv_fwk_agent IS NULL ) THEN
449 RETURN '';
450 ELSIF ( substr(lv_fwk_agent, -1, 1) = '/' ) THEN
451 RETURN lv_fwk_agent || POS_APPROVAL_PAGE || p_reg_key;
452 ELSE
453 RETURN lv_fwk_agent || '/' || POS_APPROVAL_PAGE || p_reg_key;
454 END IF;
455
456 EXCEPTION
457 WHEN OTHERS THEN
458 RETURN '';
459 END GetApprPageURL;
460
461
462 /*----------------------------------------
463
464 private FUNCTION GetLogonPageURL
465
466 Returns the URL of the logon page. The URL will be in the format
467 of protocol://host:port/page?param=val...
468
469 PARAMS:
470 none
471
472 RETURN:
473 VARCHAR2 : the logon page URL, or NULL if error
474
475 ----------------------------------------*/
476
477 FUNCTION GetLogonPageURL (
478 p_internal_flag IN VARCHAR2
479 ) RETURN VARCHAR2
480 IS
481
482 BEGIN
483
484 IF ( p_internal_flag = 'Y' OR p_internal_flag = 'y' ) THEN
485 RETURN pos_url_pkg.get_internal_login_url;
486 ELSE
487 RETURN pos_url_pkg.get_external_login_url;
488 END IF;
489
490 EXCEPTION
491 WHEN OTHERS THEN
492 RETURN '';
493 END GetLogonPageURL;
494
495 /*----------------------------------------
496
497 private PROCEDURE UpdItemTypeKeyHelper
498
499 Convenient procedure for updating item type and item key
500
501 PARAMS:
502 p_itemtype VARCHAR2 - the item type
503 p_itemkey VARCHAR2 - the item key
504 p_itemtype_fieldname VARCHAR2 - the item type field_name
505 p_itemkey_fieldname VARCHAR2 - the item key field_name
506
507 ----------------------------------------*/
508
509 PROCEDURE UpdItemTypeKeyHelper (
510 p_itemtype IN VARCHAR2
511 , p_itemkey IN VARCHAR2
512 , p_itemtype_fieldname IN VARCHAR2
513 , p_itemkey_fieldname IN VARCHAR2
514 )
515 IS
516
517 ln_reg_id NUMBER := -1;
518 ln_app_id NUMBER := -1;
519 lv_reg_type FND_REGISTRATIONS.REGISTRATION_TYPE%TYPE := NULL;
520
521 lv_registration_key WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
522 lv_proc_name VARCHAR2(30) := 'UpdItemTypeKeyHelper';
523
524 BEGIN
525
526
527 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
528 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
529 END IF;
530
531
532 lv_registration_key := WF_ENGINE.GetItemAttrText(p_itemtype, p_itemkey, 'REGISTRATION_KEY');
533
534 ln_reg_id := FND_REGISTRATION_UTILS_PKG.get_reg_id_from_key(lv_registration_key);
535
536 ln_app_id := WF_ENGINE.GetItemAttrNumber(p_itemtype, p_itemkey, 'APPLICATION_ID');
537
538 lv_reg_type := WF_ENGINE.GetItemAttrText(p_itemtype, p_itemkey, 'REGISTRATION_TYPE');
539
540 FND_REGISTRATION_PKG.insert_fnd_reg_details(
541 ln_reg_id, ln_app_id, lv_reg_type, p_itemtype_fieldname,
542 'VARCHAR2', NULL, p_itemtype, NULL, NULL);
543
544 FND_REGISTRATION_PKG.insert_fnd_reg_details(
545 ln_reg_id, ln_app_id, lv_reg_type, p_itemkey_fieldname,
546 'VARCHAR2', NULL, p_itemkey, NULL, NULL);
547
548
549 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
550 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
551 END IF;
552
553
554 RETURN;
555
556 EXCEPTION
557 WHEN OTHERS THEN
558
559 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
560 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
561 END IF;
562
563 RAISE;
564 END UpdItemTypeKeyHelper;
565
566 /*----------------------------------------
567
568 public PROCEDURE LockReg
569
570 Workflow activity function. Lock the registration record to prevent
571 simultaneous responses to the same invitation.
572
573 PARAMS:
574 WF Standard API.
575
576 RETURN:
577 COMPLETE:SUCCESS - if the record is successfully locked.
578 COMPLETE:ERROR - if the record's status has changed when getting the lock
579
580 ----------------------------------------*/
581
582 PROCEDURE LockReg(
583 itemtype IN VARCHAR2
584 , itemkey IN VARCHAR2
585 , actid IN NUMBER
586 , funcmode IN VARCHAR2
587 , resultout OUT NOCOPY VARCHAR2
588 )
589 IS
590
591 lv_reg_key WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
592 lv_reg_status FND_REGISTRATIONS.REGISTRATION_STATUS%TYPE;
593 lv_proc_name VARCHAR2(30) := 'LockReg';
594
595 BEGIN
596
597
598 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
599 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
600 END IF;
601
602
603 ClearError;
604
605 IF ( funcmode = 'RUN' ) then
606
607 lv_reg_key := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRATION_KEY', TRUE);
608 IF ( lv_reg_key IS NULL ) THEN
609
610 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
611 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, g_log_reg_key_notfound);
612 END IF;
613
614 RAISE NO_DATA_FOUND;
615 END IF;
616
617 -- lock the registration record row
618 SELECT registration_status
619 INTO lv_reg_status
620 FROM fnd_registrations
621 WHERE registration_key = lv_reg_key
622 FOR UPDATE;
623
624 -- check the precondition of registration
625 -- for nun-OSN request, it should be INVITED,
626 -- for OSN request, it's OSNREQUESTED before raising the 'Registered' event
627 IF ( lv_reg_status <> 'INVITED' AND lv_reg_status <> 'OSNREQUESTED' ) THEN
628 SetErrMsg(MSG_REG_LOCK_FAIL);
629 resultout := 'COMPLETE:ERROR';
630
631 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
632 fnd_log.string(fnd_log.level_error, g_log_module_name || '.' || lv_proc_name, 'Registration status is '||lv_reg_status);
633 END IF;
634
635 -- hack. just to raise an arbitrary exception
636 RAISE NO_DATA_FOUND;
637 END IF;
638
639 resultout := 'COMPLETE:SUCCESS';
640
641
642 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
643 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
644 END IF;
645
646
647 RETURN;
648
649 END IF;
650
651 EXCEPTION
652 WHEN OTHERS THEN
653
654 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
655 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
656 END IF;
657
658 IF ( CheckError() IS NULL ) THEN
659 SetErrMsg(MSG_UNEXPECTED_ERROR);
660 resultout := 'COMPLETE:ERROR';
661 END IF;
662 WF_CORE.CONTEXT (V_PACKAGE_NAME, 'LOCKREG', itemtype, itemkey, to_char(actid), funcmode);
663 RAISE;
664 END LockReg;
665
666 /*----------------------------------------
667
668 public PROCEDURE LockApprv
669
670 Workflow activity function. Lock the registration record (to be approved)
671 to prevent simultaneous approving process.
672
673 PARAMS:
674 WF Standard API.
675
676 RETURN:
677 COMPLETE:SUCCESS - if the record is successfully locked.
678 COMPLETE:ERROR - if the record's status has changed when getting the lock
679
680 ----------------------------------------*/
681
682 PROCEDURE LockApprv(
683 itemtype IN VARCHAR2
684 , itemkey IN VARCHAR2
685 , actid IN NUMBER
686 , funcmode IN VARCHAR2
687 , resultout OUT NOCOPY VARCHAR2
688 )
689 IS
690
691 lv_reg_key WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
692 lv_reg_status FND_REGISTRATIONS.REGISTRATION_STATUS%TYPE;
693 lv_proc_name VARCHAR2(30) := 'LockApprv';
694
695 BEGIN
696
697
698 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
699 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
700 END IF;
701
702
703 ClearError;
704
705 IF ( funcmode = 'RUN' ) then
706
707 lv_reg_key := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRATION_KEY', TRUE);
708
709 IF ( lv_reg_key IS NULL ) THEN
710
711 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
712 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, g_log_reg_key_notfound);
713 END IF;
714
715 RAISE NO_DATA_FOUND;
716 END IF;
717
718 -- lock the registration record row
719 SELECT registration_status
720 INTO lv_reg_status
721 FROM fnd_registrations
722 WHERE registration_key = lv_reg_key
723 FOR UPDATE;
724
725 -- check the pre-condition of an approval process
726 IF ( lv_reg_status <> 'REGISTERED' ) THEN
727
728 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
729 fnd_log.string(fnd_log.level_error, g_log_module_name || '.' || lv_proc_name, 'Registration status is '||lv_reg_status);
730 END IF;
731
732 SetErrMsg(MSG_APPRV_LOCK_FAIL);
733 resultout := 'COMPLETE:ERROR';
734 -- hack. just to raise an arbitrary exception
735 RAISE NO_DATA_FOUND;
736 END IF;
737
738 resultout := 'COMPLETE:SUCCESS';
739
740 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
741 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
742 END IF;
743
744 RETURN;
745
746 END IF;
747
748 EXCEPTION
749 WHEN OTHERS THEN
750
751 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
752 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
753 END IF;
754
755 IF ( CheckError() IS NULL ) THEN
756 SetErrMsg(MSG_UNEXPECTED_ERROR);
757 resultout := 'COMPLETE:ERROR';
758 END IF;
759 WF_CORE.CONTEXT (V_PACKAGE_NAME, 'LOCKAPPRV', itemtype, itemkey, to_char(actid), funcmode);
760 RAISE;
761 END LockApprv;
762
763 /*----------------------------------------
764
765 public PROCEDURE LockRjct
766
767 Workflow activity function. Lock the registration record (to be rejected)
768 to prevent simultaneous rejection.
769
770 Exactly the same as LockApprv.
771
772 PARAMS:
773 WF Standard API.
774
775 RETURN:
776 COMPLETE:SUCCESS - if the record is successfully locked.
777 COMPLETE:ERROR - if the record's status has changed when getting the lock
778
779 ----------------------------------------*/
780
781 PROCEDURE LockRjct(
782 itemtype IN VARCHAR2
783 , itemkey IN VARCHAR2
784 , actid IN NUMBER
785 , funcmode IN VARCHAR2
786 , resultout OUT NOCOPY VARCHAR2
787 )
788 IS
789 lv_proc_name VARCHAR2(30) := 'LockRjct';
790 BEGIN
791
792
793 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
794 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
795 END IF;
796
797 LockApprv(itemtype, itemkey, actid, funcmode, resultout);
798
799 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
800 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
801 END IF;
802
803
804 END LockRjct;
805
806
807 /*----------------------------------------
808
809 public PROCEDURE UpdInvTypeKey
810
811 Workflow activity function. Update registration details with the itemtype
812 and item key
813
814 PARAMS:
815 WF Standard API.
816
817 RETURN:
818 COMPLETE : always return COMPLETE. do not raise errors.
819
820 ----------------------------------------*/
821
822 PROCEDURE UpdInvTypeKey(
823 itemtype IN VARCHAR2
824 , itemkey IN VARCHAR2
825 , actid IN NUMBER
826 , funcmode IN VARCHAR2
827 , resultout OUT NOCOPY VARCHAR2
828 )
829 IS
830 lv_proc_name VARCHAR2(30) := 'UpdInvTypeKey';
831 BEGIN
832
833
834 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
835 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
836 END IF;
837
838
839 ClearError;
840
841 IF ( funcmode = 'RUN' ) then
842
843 UpdItemTypeKeyHelper(itemtype, itemkey,
844 FND_REGISTRATION_UTILS_PKG.INVITATION_WF_ITEM_TYPE,
845 FND_REGISTRATION_UTILS_PKG.INVITATION_WF_ITEM_KEY);
846
847 resultout := 'COMPLETE';
848
849 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
850 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
851 END IF;
852
853 RETURN;
854
855 END IF;
856
857 EXCEPTION
858 WHEN OTHERS THEN
859
860 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
861 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
862 END IF;
863
864 -- do not raise exception. if something wrong, just skip it
865 resultout := 'COMPLETE';
866 RETURN;
867 END UpdInvTypeKey;
868
869 /*----------------------------------------
870
871 public PROCEDURE UpdRegTypeKey
872
873 Workflow activity function. Update registration details with the itemtype
874 and item key
875
876 PARAMS:
877 WF Standard API.
878
879 RETURN:
880 COMPLETE : always return COMPLETE. do not raise errors.
881
882 ----------------------------------------*/
883
884 PROCEDURE UpdRegTypeKey(
885 itemtype IN VARCHAR2
886 , itemkey IN VARCHAR2
887 , actid IN NUMBER
888 , funcmode IN VARCHAR2
889 , resultout OUT NOCOPY VARCHAR2
890 )
891 IS
892 lv_proc_name VARCHAR2(30) := 'UpdRegTypeKey';
893 BEGIN
894
895
896 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
897 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
898 END IF;
899
900
901 IF ( funcmode = 'RUN' ) then
902
903 UpdItemTypeKeyHelper(itemtype, itemkey,
904 FND_REGISTRATION_UTILS_PKG.REGISTRATION_WF_ITEM_TYPE,
905 FND_REGISTRATION_UTILS_PKG.REGISTRATION_WF_ITEM_KEY);
906
907 resultout := 'COMPLETE';
908
909 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
910 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
911 END IF;
912
913 RETURN;
914
915 END IF;
916
917 EXCEPTION
918 WHEN OTHERS THEN
919
920 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
921 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
922 END IF;
923
924 -- do not raise exception. if something wrong, just skip it
925 resultout := 'COMPLETE';
926 RETURN;
927 END UpdRegTypeKey;
928
929 /*----------------------------------------
930
931 public PROCEDURE UpdApprvTypeKey
932
933 Workflow activity function. Update registration details with the itemtype
934 and item key
935
936 PARAMS:
937 WF Standard API.
938
939 RETURN:
940 COMPLETE : always return COMPLETE. do not raise errors.
941
942 ----------------------------------------*/
943
944 PROCEDURE UpdApprvTypeKey(
945 itemtype IN VARCHAR2
946 , itemkey IN VARCHAR2
947 , actid IN NUMBER
948 , funcmode IN VARCHAR2
949 , resultout OUT NOCOPY VARCHAR2
950 )
951 IS
952 lv_proc_name VARCHAR2(30) := 'UpdApprvTypeKey';
953 BEGIN
954
955
956 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
957 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
958 END IF;
959
960
961 IF ( funcmode = 'RUN' ) then
962
963 UpdItemTypeKeyHelper(itemtype, itemkey,
964 FND_REGISTRATION_UTILS_PKG.APPROVAL_WF_ITEM_TYPE,
965 FND_REGISTRATION_UTILS_PKG.APPROVAL_WF_ITEM_KEY);
966
967 resultout := 'COMPLETE';
968
969 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
970 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
971 END IF;
972
973 RETURN;
974
975 END IF;
976
977 EXCEPTION
978 WHEN OTHERS THEN
979
980 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
981 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
982 END IF;
983
984 -- do not raise exception. if something wrong, just skip it
985 resultout := 'COMPLETE';
986 RETURN;
987 END UpdApprvTypeKey;
988
989 /*----------------------------------------
990
991 public PROCEDURE UpdRjctTypeKey
992
993 Workflow activity function. Update registration details with the itemtype
994 and item key
995
996 PARAMS:
997 WF Standard API.
998
999 RETURN:
1000 COMPLETE : always return COMPLETE. do not raise errors.
1001
1002 ----------------------------------------*/
1003
1004 PROCEDURE UpdRjctTypeKey(
1005 itemtype IN VARCHAR2
1006 , itemkey IN VARCHAR2
1007 , actid IN NUMBER
1008 , funcmode IN VARCHAR2
1009 , resultout OUT NOCOPY VARCHAR2
1010 )
1011 IS
1012 lv_proc_name VARCHAR2(30) := 'UpdRjctTypeKey';
1013 BEGIN
1014
1015
1016 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1017 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
1018 END IF;
1019
1020
1021 IF ( funcmode = 'RUN' ) then
1022
1023 UpdItemTypeKeyHelper(itemtype, itemkey,
1024 FND_REGISTRATION_UTILS_PKG.REJECTION_WF_ITEM_TYPE,
1025 FND_REGISTRATION_UTILS_PKG.REJECTION_WF_ITEM_KEY);
1026
1027 resultout := 'COMPLETE';
1028
1029 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1030 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
1031 END IF;
1032
1033 RETURN;
1034
1035 END IF;
1036
1037 EXCEPTION
1038 WHEN OTHERS THEN
1039
1040 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1041 fnd_log.string(fnd_log.level_error,g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
1042 END IF;
1043
1044 -- do not raise exception. if something wrong, just skip it
1045 resultout := 'COMPLETE';
1046 RETURN;
1047 END UpdRjctTypeKey;
1048
1049
1050 /*----------------------------------------
1051
1052 public PROCEDURE SetInvItemAttrValues
1053
1054 Workflow activity function. Set item attribute values in 'USER_INVITED'
1055 process.
1056 Following attributes are set:
1057
1058 * REGISTRANT_LANGUAGE * NOTE
1059 * REGISTRANT_EMAIL * ADHOC_USER_NAME
1060 * ENTERPRISE_NAME * REG_PAGE_URL
1061
1062 PARAMS:
1063 WF Standard API.
1064
1065 RETURN:
1066 COMPLETE:SUCCESS - if everything is ok.
1067 COMPLETE:ERROR - if critical attribute cannot be set.
1068
1069 ----------------------------------------*/
1070
1071 PROCEDURE SetInvItemAttrValues(
1072 itemtype IN VARCHAR2
1073 , itemkey IN VARCHAR2
1074 , actid IN NUMBER
1075 , funcmode IN VARCHAR2
1076 , resultout OUT NOCOPY VARCHAR2
1077 )
1078 IS
1079
1080 lv_reg_key WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
1081 ln_reg_id NUMBER;
1082
1083 lv_registrant_lang FND_REGISTRATIONS.LANGUAGE_CODE%TYPE;
1084 lv_registrant_email FND_REGISTRATIONS.EMAIL%TYPE;
1085
1086 lv_enterprise_name HZ_PARTIES.PARTY_NAME%TYPE;
1087 lv_reg_page_url VARCHAR2(32000);
1088
1089 lv_notes FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE;
1090
1091 lv_x_field_type FND_REGISTRATION_DETAILS.FIELD_TYPE%TYPE;
1092 lv_x_field_format FND_REGISTRATION_DETAILS.FIELD_FORMAT%TYPE;
1093 ln_x_field_value_number FND_REGISTRATION_DETAILS.FIELD_VALUE_NUMBER%TYPE;
1094 lv_x_field_value_date FND_REGISTRATION_DETAILS.FIELD_VALUE_DATE%TYPE;
1095 lv_proc_name VARCHAR2(30) := 'SetInvItemAttrValues';
1096
1097 BEGIN
1098
1099
1100 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1101 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
1102 END IF;
1103
1104
1105 IF ( funcmode = 'RUN' ) then
1106
1107 lv_reg_key := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRATION_KEY', TRUE);
1108 IF ( lv_reg_key IS NULL ) THEN
1109
1110 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1111 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, g_log_reg_key_notfound);
1112 END IF;
1113
1114 RAISE NO_DATA_FOUND;
1115 END IF;
1116
1117 ln_reg_id := FND_REGISTRATION_UTILS_PKG.get_reg_id_from_key(lv_reg_key);
1118
1119 BEGIN
1120 SELECT NVL(language_code, 'US'), email
1121 INTO lv_registrant_lang, lv_registrant_email
1122 FROM fnd_registrations
1123 WHERE registration_id = ln_reg_id;
1124 EXCEPTION WHEN OTHERS THEN
1125
1126 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1127 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, g_log_reg_id_invalid);
1128 END IF;
1129
1130 RAISE;
1131 END;
1132
1133 IF ( lv_registrant_email IS NULL ) THEN
1134
1135 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1136 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Email is null.');
1137 END IF;
1138
1139 SetErrMsg(MSG_REG_DATA_MISSING);
1140 resultout := 'COMPLETE:ERROR';
1141 -- hack!!! just to raise an arbitrary exception
1142 RAISE NO_DATA_FOUND;
1143 END IF;
1144
1145 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REGISTRANT_LANGUAGE', lv_registrant_lang);
1146 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REGISTRANT_EMAIL', lv_registrant_email);
1147 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'ADHOC_USER_NAME', GetAdHocUserName(ln_reg_id));
1148
1149 lv_enterprise_name := GetEnterpriseName();
1150 IF ( lv_enterprise_name IS NULL ) THEN
1151
1152 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1153 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Enterprise name is not found.');
1154 END IF;
1155
1156 RAISE NO_DATA_FOUND;
1157 END IF;
1158
1159 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'ENTERPRISE_NAME', lv_enterprise_name);
1160
1161 BEGIN
1162 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Note', lv_x_field_type, lv_x_field_format, lv_notes, ln_x_field_value_number, lv_x_field_value_date);
1163 EXCEPTION WHEN OTHERS THEN
1164 lv_notes := NULL;
1165 END;
1166
1167 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'NOTE', lv_notes);
1168
1169 lv_reg_page_url := GetRegPageURL(lv_reg_key, lv_registrant_lang);
1170 IF ( lv_reg_page_url IS NULL ) THEN
1171
1172 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1173 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Invitation response page URL is not found.');
1174 END IF;
1175
1176 RAISE NO_DATA_FOUND;
1177 END IF;
1178
1179 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REG_PAGE_URL', lv_reg_page_url);
1180
1181 resultout := 'COMPLETE:SUCCESS';
1182
1183 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1184 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
1185 END IF;
1186
1187 RETURN;
1188
1189 END IF;
1190 EXCEPTION
1191 WHEN OTHERS THEN
1192
1193 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1194 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
1195 END IF;
1196
1197 IF ( CheckError() IS NULL ) THEN
1198 SetErrMsg(MSG_UNEXPECTED_ERROR);
1199 resultout := 'COMPLETE:ERROR';
1200 END IF;
1201 WF_CORE.CONTEXT (V_PACKAGE_NAME, 'SETINVITEMATTRVALUES', itemtype, itemkey, to_char(actid), funcmode);
1202 RAISE;
1203 END SetInvItemAttrValues;
1204
1205 /*----------------------------------------
1206
1207 public PROCEDURE SetRegItemAttrValues
1208
1209 Workflow activity function. Set item attribute values in 'USER_REGISTERED'
1210 process.
1211 Following attributes are set:
1212
1213 * APPROVER_ROLE * NOTE
1214 * FIRST_NAME * LAST_NAME
1215 * VENDOR_NAME * APPROVAL_PAGE_URL
1216 * ENTERPRISE_NAME * LOGON_PAGE_URL
1217
1218 PARAMS:
1219 WF Standard API.
1220
1221 RETURN:
1222 COMPLETE:SUCCESS - if everything is ok.
1223 COMPLETE:ERROR - if critical attribute cannot be set.
1224
1225 ----------------------------------------*/
1226
1227 PROCEDURE SetRegItemAttrValues(
1228 itemtype IN VARCHAR2
1229 , itemkey IN VARCHAR2
1230 , actid IN NUMBER
1231 , funcmode IN VARCHAR2
1232 , resultout OUT NOCOPY VARCHAR2
1233 )
1234 IS
1235
1236 lv_reg_key WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
1237 ln_reg_id NUMBER;
1238
1239 lv_first_name FND_REGISTRATIONS.FIRST_NAME%TYPE := NULL;
1240 lv_last_name FND_REGISTRATIONS.LAST_NAME%TYPE := NULL;
1241 lv_vendor_name FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE := NULL;
1242 lv_osn_tp_name FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE := NULL;
1243 ln_approver_id NUMBER := NULL;
1244 --lv_approver_role FND_USER.USER_NAME%TYPE := NULL; --varchar2(100)
1245 lv_approver_role WF_USER_ROLES.ROLE_NAME%TYPE := NULL; --varchar2(320)
1246 lv_approval_page_url VARCHAR2(32000) := NULL;
1247 lv_notes FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE := NULL;
1248 lv_enterprise_name HZ_PARTIES.PARTY_NAME%TYPE;
1249 lv_logon_page_url VARCHAR2(32000) := NULL;
1250
1251 lv_x_field_type FND_REGISTRATION_DETAILS.FIELD_TYPE%TYPE;
1252 lv_x_field_format FND_REGISTRATION_DETAILS.FIELD_FORMAT%TYPE;
1253 lv_x_field_value_string FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE;
1254 ln_x_field_value_number FND_REGISTRATION_DETAILS.FIELD_VALUE_NUMBER%TYPE;
1255 lv_x_field_value_date FND_REGISTRATION_DETAILS.FIELD_VALUE_DATE%TYPE;
1256 lv_proc_name VARCHAR2(30) := 'SetRegItemAttrValues';
1257
1258 BEGIN
1259
1260
1261 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1262 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
1263 END IF;
1264
1265
1266 IF ( funcmode = 'RUN' ) then
1267
1268 lv_reg_key := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRATION_KEY', TRUE);
1269 IF ( lv_reg_key IS NULL ) THEN
1270
1271 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1272 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, g_log_reg_key_notfound);
1273 END IF;
1274
1275 RAISE NO_DATA_FOUND;
1276 END IF;
1277
1278 ln_reg_id := FND_REGISTRATION_UTILS_PKG.get_reg_id_from_key(lv_reg_key);
1279
1280 BEGIN
1281 SELECT first_name, last_name
1282 INTO lv_first_name, lv_last_name
1283 FROM fnd_registrations
1284 WHERE registration_id = ln_reg_id;
1285 EXCEPTION WHEN OTHERS THEN
1286
1287 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1288 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, g_log_reg_id_invalid);
1289 END IF;
1290
1291 RAISE;
1292 END;
1293
1294 IF ( lv_first_name IS NULL OR lv_last_name IS NULL ) THEN
1295
1296 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1297 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'First or last name is missing.');
1298 END IF;
1299
1300 SetErrMsg(MSG_REG_DATA_MISSING);
1301 resultout := 'COMPLETE:ERROR';
1302 RAISE NO_DATA_FOUND;
1303 END IF;
1304
1305 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'FIRST_NAME', lv_first_name);
1306 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'LAST_NAME', lv_last_name);
1307
1308 -- check if the registration is from Oracle Supplier Network
1309 -- and set the approver role attribute
1310 BEGIN
1311 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'OSN Request ID', lv_x_field_type, lv_x_field_format, lv_x_field_value_string, ln_x_field_value_number, lv_x_field_value_date);
1312 EXCEPTION WHEN OTHERS THEN
1313 ln_x_field_value_number := NULL;
1314 END;
1315
1316 if (ln_x_field_value_number is not null) then
1317 -- this request is from OSN
1318 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'IS_OSNREQUEST', 'Y');
1319 lv_approver_role := get_approver_role_for_osn();
1320 else
1321 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'IS_OSNREQUEST', 'N');
1322 end if;
1323
1324 -- if this request is not from OSN, or failed to get approver role
1325 if (ln_x_field_value_number is null
1326 or lv_approver_role is null) then
1327
1328 BEGIN
1329 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Approver ID', lv_x_field_type, lv_x_field_format, lv_x_field_value_string, ln_approver_id, lv_x_field_value_date);
1330 EXCEPTION WHEN OTHERS THEN
1331 ln_approver_id := NULL;
1332 END;
1333
1334 IF ( ln_approver_id IS NULL ) THEN
1335
1336 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1337 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Approver ID is missing');
1338 END IF;
1339
1340 SetErrMsg(MSG_REG_DATA_MISSING);
1341 resultout := 'COMPLETE:ERROR';
1342 RAISE NO_DATA_FOUND;
1343 END IF;
1344
1345 BEGIN
1346 SELECT user_name
1347 INTO lv_approver_role
1348 FROM fnd_user
1349 WHERE user_id = ln_approver_id;
1350 EXCEPTION WHEN OTHERS THEN
1351
1352 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1353 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Approver ID is INVALID');
1354 END IF;
1355
1356 RAISE;
1357 END;
1358
1359 end if; -- if this request is not from OSN, or failed to get approver role
1360
1361 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'APPROVER_ROLE', lv_approver_role);
1362
1363 BEGIN
1364 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Supplier Name', lv_x_field_type, lv_x_field_format, lv_vendor_name, ln_x_field_value_number, lv_x_field_value_date);
1365 EXCEPTION WHEN OTHERS THEN
1366 lv_vendor_name := NULL;
1367 END;
1368
1369 --if it's an OSN request, could use trading partner when supplier is null
1370 IF ( lv_vendor_name IS NULL ) THEN
1371 BEGIN
1372 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'OSN TP Name', lv_x_field_type, lv_x_field_format, lv_osn_tp_name, ln_x_field_value_number, lv_x_field_value_date);
1373
1374 lv_vendor_name := lv_osn_tp_name;
1375 EXCEPTION WHEN OTHERS THEN
1376 lv_osn_tp_name := NULL;
1377 END;
1378 END IF;
1379
1380 IF ( lv_vendor_name IS NULL ) THEN
1381
1382 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1383 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Vendor name is not found.');
1384 END IF;
1385
1386 SetErrMsg(MSG_REG_DATA_MISSING);
1387 resultout := 'COMPLETE:ERROR';
1388 RAISE NO_DATA_FOUND;
1389 END IF;
1390
1391 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'VENDOR_NAME', lv_vendor_name);
1392
1393 BEGIN
1394 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Note', lv_x_field_type, lv_x_field_format, lv_notes, ln_x_field_value_number, lv_x_field_value_date);
1395 EXCEPTION WHEN OTHERS THEN
1396 lv_notes := NULL;
1397 END;
1398
1399 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'NOTE', lv_notes);
1400
1401 lv_enterprise_name := GetEnterpriseName();
1402 IF ( lv_enterprise_name IS NULL ) THEN
1403
1404 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1405 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Enterprise name is not found.');
1406 END IF;
1407
1408 RAISE NO_DATA_FOUND;
1409 END IF;
1410
1411 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'ENTERPRISE_NAME', lv_enterprise_name);
1412
1413 lv_approval_page_url := GetApprPageURL(lv_reg_key);
1414 IF ( lv_approval_page_url IS NULL ) THEN
1415
1416 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1417 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Approval page URL is not found.');
1418 END IF;
1419
1420 RAISE NO_DATA_FOUND;
1421 END IF;
1422
1423 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'APPROVAL_PAGE_URL', lv_approval_page_url);
1424
1425 lv_logon_page_url := GetLogonPageURL('Y');
1426 IF ( lv_logon_page_url IS NULL ) THEN
1427
1428 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1429 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Logon page URL is NOT FOUND.');
1430 END IF;
1431
1432 RAISE NO_DATA_FOUND;
1433 END IF;
1434
1435 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'LOGON_PAGE_URL', lv_logon_page_url);
1436
1437 resultout := 'COMPLETE:SUCCESS';
1438
1439 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1440 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
1441 END IF;
1442
1443 RETURN;
1444
1445 END IF;
1446
1447 EXCEPTION
1448 WHEN OTHERS THEN
1449
1450 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1451 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
1452 END IF;
1453
1454 IF ( CheckError() IS NULL ) THEN
1455 SetErrMsg(MSG_UNEXPECTED_ERROR);
1456 resultout := 'COMPLETE:ERROR';
1457 END IF;
1458 WF_CORE.CONTEXT (V_PACKAGE_NAME, 'SETREGITEMATTRVALUES', itemtype, itemkey, to_char(actid), funcmode);
1459 RAISE;
1460 END SetRegItemAttrValues;
1461
1462 /*----------------------------------------
1463
1464 public PROCEDURE SetApprvItemAttrValues
1465
1466 Workflow activity function. Set item attribute values in 'USER_APPROVED'
1467 process.
1468 Following attributes are set:
1469
1470 * ENTERPRISE_NAME * LOGON_PAGE_URL
1471 * CONTACT_EMAIL * IS_INVITED
1472 * ADHOC_USER_NAME * REQUESTED_USER_NAME
1473 * REGISTRANT_EMAIL * VENDOR_ID
1474 * POS_SELECTED * PON_SELECTED
1475 * FIRST_NAME * LAST_NAME
1476 * REGISTRANT_TITLE * REGISTRANT_PHONE
1477 * REGISTRANT_PHONE_EXT * REGISTRANT_FAX
1478 * REGISTRANT_JOB_TITLE * MIDDLE_NAME
1479 * SC_SELECTED
1480
1481 PARAMS:
1482 WF Standard API.
1483
1484 RETURN:
1485 COMPLETE:SUCCESS - if everything is ok.
1486 COMPLETE:ERROR - if critical attribute cannot be set.
1487
1488 ----------------------------------------*/
1489
1490 PROCEDURE SetApprvItemAttrValues(
1491 itemtype IN VARCHAR2
1492 , itemkey IN VARCHAR2
1493 , actid IN NUMBER
1494 , funcmode IN VARCHAR2
1495 , resultout OUT NOCOPY VARCHAR2
1496 )
1497 IS
1498
1499 lv_reg_key WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
1500 ln_reg_id NUMBER;
1501
1502 lv_requested_user_name FND_REGISTRATIONS.REQUESTED_USER_NAME%TYPE := NULL;
1503 lv_user_email FND_REGISTRATIONS.EMAIL%TYPE := NULL;
1504 lv_first_name FND_REGISTRATIONS.FIRST_NAME%TYPE := NULL;
1505 lv_last_name FND_REGISTRATIONS.LAST_NAME%TYPE := NULL;
1506 lv_middle_name FND_REGISTRATIONS.MIDDLE_NAME%TYPE := NULL;
1507 lv_title FND_REGISTRATIONS.USER_TITLE%TYPE := NULL;
1508 lv_phone FND_REGISTRATIONS.PHONE%TYPE := NULL;
1509 lv_phone_ext FND_REGISTRATIONS.PHONE_EXTENSION%TYPE := NULL;
1510 lv_fax FND_REGISTRATIONS.FAX%TYPE := NULL;
1511
1512 ln_approver_id NUMBER;
1513 lv_contact_email FND_USER.EMAIL_ADDRESS%TYPE;
1514 lv_logon_page_url VARCHAR2(32000) := NULL;
1515 lv_enterprise_name HZ_PARTIES.PARTY_NAME%TYPE;
1516 lv_is_invited_flag FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE;
1517 ln_vendor_id NUMBER;
1518 lv_pos_flag FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE;
1519 lv_pon_flag FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE;
1520 lv_sc_flag FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE;
1521 lv_notes FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE;
1522 lv_job_title FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE;
1523
1524 lv_x_field_type FND_REGISTRATION_DETAILS.FIELD_TYPE%TYPE;
1525 lv_x_field_format FND_REGISTRATION_DETAILS.FIELD_FORMAT%TYPE;
1526 lv_x_field_value_string FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE;
1527 ln_x_field_value_number FND_REGISTRATION_DETAILS.FIELD_VALUE_NUMBER%TYPE;
1528 lv_x_field_value_date FND_REGISTRATION_DETAILS.FIELD_VALUE_DATE%TYPE;
1529 lv_proc_name VARCHAR2(30) := 'SetApprvItemAttrValues';
1530
1531 BEGIN
1532
1533
1534 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1535 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
1536 END IF;
1537
1538
1539 IF ( funcmode = 'RUN' ) then
1540
1541 lv_reg_key := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRATION_KEY',
1542 TRUE);
1543 IF ( lv_reg_key IS NULL ) THEN
1544
1545 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1546 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, g_log_reg_key_notfound);
1547 END IF;
1548
1549 RAISE NO_DATA_FOUND;
1550 END IF;
1551
1552 ln_reg_id := FND_REGISTRATION_UTILS_PKG.get_reg_id_from_key(lv_reg_key);
1553
1554 BEGIN
1555 SELECT requested_user_name, email, first_name, last_name, middle_name, user_title, phone, phone_extension, fax
1556 INTO lv_requested_user_name, lv_user_email, lv_first_name, lv_last_name, lv_middle_name, lv_title, lv_phone, lv_phone_ext, lv_fax
1557 FROM fnd_registrations
1558 WHERE registration_id = ln_reg_id;
1559 EXCEPTION WHEN OTHERS THEN
1560
1561 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1562 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, g_log_reg_id_invalid);
1563 END IF;
1564
1565 RAISE;
1566 END;
1567
1568 IF ( lv_requested_user_name IS NULL OR lv_user_email IS NULL OR lv_first_name IS NULL OR lv_last_name IS NULL OR lv_phone IS NULL) THEN
1569
1570 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1571 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Requested user name, or email, or first name, or last name, or phone is missing');
1572 END IF;
1573
1574 SetErrMsg(MSG_REG_DATA_MISSING);
1575 resultout := 'COMPLETE:ERROR';
1576 RAISE NO_DATA_FOUND;
1577 END IF;
1578
1579 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REQUESTED_USER_NAME', lv_requested_user_name);
1580 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'ADHOC_USER_NAME', GetAdHocUserName(ln_reg_id));
1581 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REGISTRANT_EMAIL', lv_user_email);
1582 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'FIRST_NAME', lv_first_name);
1583 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'LAST_NAME', lv_last_name);
1584 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'MIDDLE_NAME', lv_middle_name);
1585 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REGISTRANT_TITLE', lv_title);
1586 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REGISTRANT_PHONE', lv_phone);
1587 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REGISTRANT_PHONE_EXT', lv_phone_ext);
1588 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REGISTRANT_FAX', lv_fax);
1589
1590 lv_enterprise_name := GetEnterpriseName();
1591 IF ( lv_enterprise_name IS NULL ) THEN
1592
1593 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1594 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Enterprise name is not found.');
1595 END IF;
1596
1597 RAISE NO_DATA_FOUND;
1598 END IF;
1599
1600 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'ENTERPRISE_NAME', lv_enterprise_name);
1601
1602 BEGIN
1603 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Invited Flag', lv_x_field_type, lv_x_field_format, lv_is_invited_flag, ln_x_field_value_number, lv_x_field_value_date);
1604 EXCEPTION WHEN OTHERS THEN
1605 lv_is_invited_flag := NULL;
1606 END;
1607
1608 IF ( lv_is_invited_flag IS NULL ) THEN
1609 lv_is_invited_flag := 'N';
1610 ELSIF ( upper(substr(lv_is_invited_flag, 1, 1)) = 'Y' ) THEN
1611 lv_is_invited_flag := 'Y';
1612 ELSE
1613 lv_is_invited_flag := 'N';
1614 END IF;
1615
1616 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'IS_INVITED', lv_is_invited_flag);
1617
1618 BEGIN
1619 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Approver ID', lv_x_field_type, lv_x_field_format, lv_x_field_value_string, ln_approver_id, lv_x_field_value_date);
1620 EXCEPTION WHEN OTHERS THEN
1621 ln_approver_id := NULL;
1622 END;
1623
1624 IF ( ln_approver_id IS NULL ) THEN
1625
1626 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1627 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Approver ID is missing');
1628 END IF;
1629
1630 SetErrMsg(MSG_REG_DATA_MISSING);
1631 resultout := 'COMPLETE:ERROR';
1632 RAISE NO_DATA_FOUND;
1633 END IF;
1634
1635 BEGIN
1636 SELECT email_address
1637 INTO lv_contact_email
1638 FROM fnd_user
1639 WHERE user_id = ln_approver_id;
1640
1641 EXCEPTION WHEN OTHERS THEN
1642
1643 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1644 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Approver ID is INVALID');
1645 END IF;
1646
1647 RAISE;
1648 END;
1649
1650 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'CONTACT_EMAIL', lv_contact_email);
1651
1652 BEGIN
1653 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Supplier Number', lv_x_field_type, lv_x_field_format, lv_x_field_value_string, ln_vendor_id, lv_x_field_value_date);
1654 EXCEPTION WHEN OTHERS THEN
1655 ln_vendor_id := NULL;
1656 END;
1657
1658 IF ( ln_vendor_id IS NULL ) THEN
1659
1660 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1661 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Vendor ID is not found.');
1662 END IF;
1663
1664 SetErrMsg(MSG_REG_DATA_MISSING);
1665 resultout := 'COMPLETE:ERROR';
1666 RAISE NO_DATA_FOUND;
1667 END IF;
1668
1669 WF_ENGINE.SetItemAttrNumber(itemtype, itemkey, 'VENDOR_ID', ln_vendor_id);
1670
1671 BEGIN
1672 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'ISP', lv_x_field_type, lv_x_field_format, lv_pos_flag, ln_x_field_value_number, lv_x_field_value_date);
1673 EXCEPTION WHEN OTHERS THEN
1674 lv_pos_flag := NULL;
1675 END;
1676
1677 BEGIN
1678 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Sourcing', lv_x_field_type, lv_x_field_format, lv_pon_flag, ln_x_field_value_number, lv_x_field_value_date);
1679 EXCEPTION WHEN OTHERS THEN
1680 lv_pon_flag := NULL;
1681 END;
1682
1683 BEGIN
1684 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'CollaborativePlanning', lv_x_field_type, lv_x_field_format, lv_sc_flag, ln_x_field_value_number, lv_x_field_value_date);
1685 EXCEPTION WHEN OTHERS THEN
1686 lv_sc_flag := NULL;
1687 END;
1688
1689 IF ( lv_pos_flag IS NULL ) THEN
1690 lv_pos_flag := 'N';
1691 ELSIF ( upper(substr(lv_pos_flag, 1, 1)) = 'Y' ) THEN
1692 lv_pos_flag := 'Y';
1693 ELSE
1694 lv_pos_flag := 'N';
1695 END IF;
1696
1697 IF ( lv_pon_flag IS NULL ) THEN
1698 lv_pon_flag := 'N';
1699 ELSIF ( upper(substr(lv_pon_flag, 1, 1)) = 'Y' ) THEN
1700 lv_pon_flag := 'Y';
1701 ELSE
1702 lv_pon_flag := 'N';
1703 END IF;
1704
1705 IF ( lv_sc_flag IS NULL ) THEN
1706 lv_sc_flag := 'N';
1707 ELSIF ( upper(substr(lv_sc_flag, 1, 1)) = 'Y' ) THEN
1708 lv_sc_flag := 'Y';
1709 ELSE
1710 lv_sc_flag := 'N';
1711 END IF;
1712
1713 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'POS_SELECTED', lv_pos_flag);
1714 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'PON_SELECTED', lv_pon_flag);
1715 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'SC_SELECTED', lv_sc_flag);
1716
1717 BEGIN
1718 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Note', lv_x_field_type, lv_x_field_format, lv_notes, ln_x_field_value_number, lv_x_field_value_date);
1719 EXCEPTION WHEN OTHERS THEN
1720 lv_notes := NULL;
1721 END;
1722
1723 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'NOTE', lv_notes);
1724
1725 BEGIN
1726 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Job Title', lv_x_field_type, lv_x_field_format, lv_job_title, ln_x_field_value_number, lv_x_field_value_date);
1727 EXCEPTION WHEN OTHERS THEN
1728 lv_job_title := NULL;
1729 END;
1730
1731 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REGISTRANT_JOB_TITLE', lv_job_title);
1732
1733 lv_logon_page_url := GetLogonPageURL('N');
1734 IF ( lv_logon_page_url IS NULL ) THEN
1735
1736 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1737 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Logon page URL is NOT FOUND.');
1738 END IF;
1739
1740 RAISE NO_DATA_FOUND;
1741 END IF;
1742
1743 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'LOGON_PAGE_URL', lv_logon_page_url);
1744
1745 -- check if the registration is from Oracle Supplier Network
1746 -- and set the IS_OSNREQUEST attribute
1747 BEGIN
1748 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'OSN Request ID', lv_x_field_type, lv_x_field_format, lv_x_field_value_string, ln_x_field_value_number, lv_x_field_value_date);
1749 EXCEPTION WHEN OTHERS THEN
1750 ln_x_field_value_number := NULL;
1751 END;
1752 if (ln_x_field_value_number is not null) then
1753 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'IS_OSNREQUEST', 'Y');
1754 else
1755 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'IS_OSNREQUEST', 'N');
1756 end if;
1757
1758 resultout := 'COMPLETE:SUCCESS';
1759
1760 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1761 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
1762 END IF;
1763
1764 RETURN;
1765
1766 END IF;
1767
1768 EXCEPTION
1769 WHEN OTHERS THEN
1770
1771 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1772 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
1773 END IF;
1774
1775 IF ( CheckError() IS NULL ) THEN
1776 SetErrMsg(MSG_UNEXPECTED_ERROR);
1777 resultout := 'COMPLETE:ERROR';
1778 END IF;
1779 WF_CORE.CONTEXT (V_PACKAGE_NAME, 'SETAPPRVITEMATTRVALUES', itemtype, itemkey, to_char(actid), funcmode);
1780 RAISE;
1781 END SetApprvItemAttrValues;
1782
1783 /*----------------------------------------
1784
1785 public PROCEDURE SetRjctItemAttrValues
1786
1787 Workflow activity function. Set item attribute values in 'USER_REJECTED'
1788 process.
1789 Following attributes are set:
1790
1791 * REGISTRANT_LANGUAGE * IS_INVITED
1792 * REGISTRANT_EMAIL * ADHOC_USER_NAME
1793 * ENTERPRISE_NAME * CONTACT_EMAIL
1794
1795 PARAMS:
1796 WF Standard API.
1797
1798 RETURN:
1799 COMPLETE:SUCCESS - if everything is ok.
1800 COMPLETE:ERROR - if critical attribute cannot be set.
1801
1802 ----------------------------------------*/
1803
1804 PROCEDURE SetRjctItemAttrValues(
1805 itemtype IN VARCHAR2
1806 , itemkey IN VARCHAR2
1807 , actid IN NUMBER
1808 , funcmode IN VARCHAR2
1809 , resultout OUT NOCOPY VARCHAR2
1810 )
1811 IS
1812
1813 lv_reg_key WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
1814 ln_reg_id NUMBER;
1815
1816 lv_registrant_lang FND_REGISTRATIONS.LANGUAGE_CODE%TYPE;
1817 lv_registrant_email FND_REGISTRATIONS.EMAIL%TYPE;
1818
1819 lv_enterprise_name HZ_PARTIES.PARTY_NAME%TYPE;
1820
1821 ln_approver_id NUMBER;
1822 lv_contact_email FND_USER.EMAIL_ADDRESS%TYPE;
1823 lv_is_invited_flag FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE;
1824
1825 lv_x_field_type FND_REGISTRATION_DETAILS.FIELD_TYPE%TYPE;
1826 lv_x_field_format FND_REGISTRATION_DETAILS.FIELD_FORMAT%TYPE;
1827 lv_x_field_value_string FND_REGISTRATION_DETAILS.FIELD_VALUE_STRING%TYPE;
1828 ln_x_field_value_number FND_REGISTRATION_DETAILS.FIELD_VALUE_NUMBER%TYPE;
1829 lv_x_field_value_date FND_REGISTRATION_DETAILS.FIELD_VALUE_DATE%TYPE;
1830 lv_proc_name VARCHAR2(30) := 'SetRjctItemAttrValues';
1831 BEGIN
1832
1833
1834 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1835 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
1836 END IF;
1837
1838
1839 IF ( funcmode = 'RUN' ) then
1840
1841 lv_reg_key := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRATION_KEY', TRUE);
1842 IF ( lv_reg_key IS NULL ) THEN
1843
1844 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1845 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, g_log_reg_key_notfound);
1846 END IF;
1847
1848 RAISE NO_DATA_FOUND;
1849 END IF;
1850
1851 ln_reg_id := FND_REGISTRATION_UTILS_PKG.get_reg_id_from_key(lv_reg_key);
1852
1853 BEGIN
1854 SELECT NVL(language_code, 'US'), email
1855 INTO lv_registrant_lang, lv_registrant_email
1856 FROM fnd_registrations
1857 WHERE registration_id = ln_reg_id;
1858 EXCEPTION WHEN OTHERS THEN
1859
1860 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1861 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, g_log_reg_id_invalid);
1862 END IF;
1863
1864 RAISE;
1865 END;
1866
1867 IF ( lv_registrant_email IS NULL ) THEN
1868
1869 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1870 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Email is MISSING');
1871 END IF;
1872
1873 SetErrMsg(MSG_REG_DATA_MISSING);
1874 resultout := 'COMPLETE:ERROR';
1875 RAISE NO_DATA_FOUND;
1876 END IF;
1877
1878 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REGISTRANT_LANGUAGE', lv_registrant_lang);
1879 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'REGISTRANT_EMAIL', lv_registrant_email);
1880 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'ADHOC_USER_NAME', GetAdHocUserName(ln_reg_id));
1881
1882 lv_enterprise_name := GetEnterpriseName();
1883 IF ( lv_enterprise_name IS NULL ) THEN
1884
1885 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1886 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Enterprise name is not found.');
1887 END IF;
1888
1889 RAISE NO_DATA_FOUND;
1890 END IF;
1891
1892 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'ENTERPRISE_NAME', lv_enterprise_name);
1893
1894 BEGIN
1895 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Invited Flag', lv_x_field_type, lv_x_field_format, lv_is_invited_flag, ln_x_field_value_number, lv_x_field_value_date);
1896 EXCEPTION WHEN OTHERS THEN
1897 lv_is_invited_flag := NULL;
1898 END;
1899
1900 IF ( lv_is_invited_flag IS NULL ) THEN
1901 lv_is_invited_flag := 'N';
1902 ELSIF ( upper(substr(lv_is_invited_flag, 1, 1)) = 'Y' ) THEN
1903 lv_is_invited_flag := 'Y';
1904 ELSE
1905 lv_is_invited_flag := 'N';
1906 END IF;
1907
1908 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'IS_INVITED', lv_is_invited_flag);
1909
1910 BEGIN
1911 FND_REGISTRATION_PKG.retrieve_fnd_reg_details( ln_reg_id, 'Approver ID', lv_x_field_type, lv_x_field_format, lv_x_field_value_string, ln_approver_id, lv_x_field_value_date);
1912 EXCEPTION WHEN OTHERS THEN
1913 ln_approver_id := NULL;
1914 END;
1915
1916 IF ( ln_approver_id IS NULL ) THEN
1917
1918 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1919 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Approver ID is MISSING');
1920 END IF;
1921
1922 SetErrMsg(MSG_REG_DATA_MISSING);
1923 resultout := 'COMPLETE:ERROR';
1924 RAISE NO_DATA_FOUND;
1925 END IF;
1926
1927 BEGIN
1928 SELECT email_address
1929 INTO lv_contact_email
1930 FROM fnd_user
1931 WHERE user_id = ln_approver_id;
1932
1933 EXCEPTION WHEN OTHERS THEN
1934
1935 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1936 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Approver ID is INVALID');
1937 END IF;
1938
1939 RAISE;
1940 END;
1941
1942 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'CONTACT_EMAIL', lv_contact_email);
1943
1944 resultout := 'COMPLETE:SUCCESS';
1945
1946 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1947 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
1948 END IF;
1949
1950 RETURN;
1951
1952 END IF;
1953 EXCEPTION
1954 WHEN OTHERS THEN
1955
1956 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1957 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
1958 END IF;
1959
1960 IF ( CheckError() IS NULL ) THEN
1961 SetErrMsg(MSG_UNEXPECTED_ERROR);
1962 resultout := 'COMPLETE:ERROR';
1963 END IF;
1964 WF_CORE.CONTEXT (V_PACKAGE_NAME, 'SETRJCTITEMATTRVALUES', itemtype, itemkey, to_char(actid), funcmode);
1965 RAISE;
1966 END SetRjctItemAttrValues;
1967
1968 /*----------------------------------------
1969
1970 public PROCEDURE CreateLocalUser
1971
1972 Workflow activity function. Create a workflow ad-hoc (local) user.
1973
1974 PARAMS:
1975 WF Standard API.
1976
1977 RETURN:
1978 COMPLETE:SUCCESS - if everything is ok
1979 COMPLETE:ERROR - if anything wrong
1980
1981 ----------------------------------------*/
1982
1983 PROCEDURE CreateLocalUser(
1984 itemtype IN VARCHAR2
1985 , itemkey IN VARCHAR2
1986 , actid IN NUMBER
1987 , funcmode IN VARCHAR2
1988 , resultout OUT NOCOPY VARCHAR2
1989 )
1990 IS
1991
1992 lv_user_name WF_LOCAL_USERS.NAME%TYPE;
1993 lv_user_email WF_LOCAL_USERS.EMAIL_ADDRESS%TYPE;
1994 lv_user_language WF_LOCAL_USERS.LANGUAGE%TYPE;
1995 lv_nls_lang FND_LANGUAGES.NLS_LANGUAGE%TYPE;
1996 lv_proc_name VARCHAR2(30) := 'CreateLocalUser';
1997
1998 BEGIN
1999
2000
2001 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2002 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
2003 END IF;
2004
2005
2006 IF ( funcmode = 'RUN' ) then
2007
2008 lv_user_name := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'ADHOC_USER_NAME');
2009 lv_user_email := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRANT_EMAIL');
2010 lv_user_language := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRANT_LANGUAGE');
2011
2012 SELECT nls_language
2013 INTO lv_nls_lang
2014 FROM fnd_languages
2015 WHERE language_code = lv_user_language;
2016
2017 /*
2018 * Add the notification_preference parameter in method 'CreateAdHocUser', to remove the attachment in
2019 * invitation mail notification. Please refer the bug 7424124 for mre info.
2020 */
2021
2022 WF_DIRECTORY.CreateAdHocUser( name => lv_user_name,
2023 display_name => lv_user_name,
2024 language => lv_nls_lang,
2025 notification_preference => 'MAILHTM2',
2026 email_address => lv_user_email);
2027
2028
2029 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2030 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, 'ad hoc user created');
2031 END IF;
2032
2033
2034
2035 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2036 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, 'confirm guest user responsibility');
2037 END IF;
2038
2039
2040 POS_ANON_PKG.confirm_has_resp('POS_SUPPLIER_GUEST_USER');
2041
2042
2043 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2044 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, 'guest user responsibility confirmed');
2045 END IF;
2046
2047
2048 resultout := 'COMPLETE:SUCCESS';
2049
2050 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2051 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
2052 END IF;
2053
2054 RETURN;
2055
2056 END IF;
2057
2058 EXCEPTION
2059 WHEN OTHERS THEN
2060
2061 IF ( fnd_log.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2062 fnd_log.string(fnd_log.level_error,g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
2063 END IF;
2064
2065 IF ( CheckError() IS NULL ) THEN
2066 SetErrMsg(MSG_UNEXPECTED_ERROR);
2067 resultout := 'COMPLETE:ERROR';
2068 END IF;
2069 WF_CORE.CONTEXT (V_PACKAGE_NAME, 'CREATELOCALUSER', itemtype, itemkey, to_char(actid), funcmode);
2070 RAISE;
2071 END CreateLocalUser;
2072
2073 /*----------------------------------------
2074
2075 public PROCEDURE DeleteLocalUser
2076
2077 Workflow activity function. Delete a workflow ad-hoc (local) user.
2078 Do not raise exception. If the deletion fails, just skip it.
2079
2080 PARAMS:
2081 WF Standard API.
2082
2083 RETURN:
2084 WF Standard API.
2085
2086 ----------------------------------------*/
2087
2088 PROCEDURE DeleteLocalUser(
2089 itemtype IN VARCHAR2
2090 , itemkey IN VARCHAR2
2091 , actid IN NUMBER
2092 , funcmode IN VARCHAR2
2093 , resultout OUT NOCOPY VARCHAR2
2094 )
2095 IS
2096
2097 lv_user_name WF_LOCAL_USERS.NAME%TYPE;
2098
2099 BEGIN
2100
2101 IF ( funcmode = 'RUN' ) then
2102
2103 lv_user_name := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'ADHOC_USER_NAME');
2104
2105 DELETE FROM wf_local_users
2106 WHERE name = lv_user_name;
2107
2108 resultout := 'COMPLETE';
2109 RETURN;
2110
2111 END IF;
2112
2113 EXCEPTION
2114 WHEN OTHERS THEN
2115 RETURN;
2116 END DeleteLocalUser;
2117
2118 /*----------------------------------------
2119
2120 PROCEDURE AssginResp
2121
2122 Assgin responsibility to user
2123
2124 PARAM:
2125 p_user_id IN NUMBER - the FND_USER id of the assignee
2126 p_resp_app_id IN NUMBER - the application id associated with the resp
2127 p_resp_key IN VARCHAR2 - the responsibility key
2128
2129 EXCEPTION:
2130 none. Do not raise exception.
2131
2132 ----------------------------------------*/
2133 PROCEDURE AssginResp(
2134 p_user_id IN NUMBER
2135 , p_resp_app_id IN NUMBER
2136 , p_resp_key IN VARCHAR2
2137 )
2138 IS
2139
2140 lv_proc_name VARCHAR2(30) := 'AssginResp';
2141 ln_resp_id NUMBER := NULL;
2142
2143 CURSOR l_resp_id_cur (p_app_id NUMBER, p_resp_key VARCHAR2) IS
2144 SELECT responsibility_id
2145 FROM fnd_responsibility
2146 WHERE application_id = p_app_id
2147 AND responsibility_key = p_resp_key
2148 AND (end_date IS NULL OR end_date > start_date);
2149
2150 BEGIN
2151
2152 OPEN l_resp_id_cur(p_resp_app_id, p_resp_key);
2153 FETCH l_resp_id_cur INTO ln_resp_id;
2154 CLOSE l_resp_id_cur;
2155 IF ln_resp_id IS NULL THEN
2156
2157 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2158 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' ||
2159 lv_proc_name, 'Invalid responsibility key ' || p_resp_key);
2160 END IF;
2161
2162 RETURN;
2163 END IF;
2164
2165 FND_USER_RESP_GROUPS_API.insert_assignment
2166 ( user_id => p_user_id,
2167 responsibility_id => ln_resp_id,
2168 responsibility_application_id => p_resp_app_id,
2169 security_group_id => 0,
2170 start_date => sysdate,
2171 end_date => NULL,
2172 description => p_resp_key);
2173
2174 EXCEPTION
2175 WHEN OTHERS THEN
2176
2177 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2178 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' ||
2179 lv_proc_name, 'Failed to assign responsibility ' ||
2180 p_resp_key || '. sqlerrm: '||sqlerrm);
2181 END IF;
2182
2183 END AssginResp;
2184
2185 /*----------------------------------------
2186
2187 PROCEDURE SetSecAttr
2188
2189 Set security atttribute to the user.
2190
2191 PARAM:
2192 p_user_id IN NUMBER - the FND_USER id of the assignee
2193 p_attribute_code IN VARCHAR2 - the security attribute code
2194 p_app_id IN NUMBER - the application id associated with the
2195 security code
2196 p_varchar2_value IN VARCHAR2 - the VARCHAR2 value DEFAULT NULL
2197 p_date_value IN DATE - date value DEFAULT NULL
2198 p_number_value IN NUMBER - number value DEFAULT NULL
2199
2200 Note: one of the three values must be NOT NULL.
2201
2202 EXCEPTION:
2203
2204 Raise exceptions.
2205
2206 ----------------------------------------*/
2207 PROCEDURE SetSecAttr(
2208 p_user_id IN NUMBER
2209 , p_attribute_code IN VARCHAR2
2210 , p_app_id IN NUMBER
2211 , p_varchar2_value IN VARCHAR2 DEFAULT NULL
2212 , p_date_value IN DATE DEFAULT NULL
2213 , p_number_value IN NUMBER DEFAULT NULL
2214 )
2215 IS
2216
2217 lv_proc_name VARCHAR2(30) := 'SetSecAttr';
2218 lv_exception_msg VARCHAR2(4000);
2219 lv_status VARCHAR2(30);
2220 ln_msg_count NUMBER := 0;
2221
2222 BEGIN
2223 BEGIN
2224 ICX_USER_SEC_ATTR_PVT.Create_user_sec_attr
2225 ( p_api_version_number => 1.0,
2226 p_return_status => lv_status,
2227 p_msg_count => ln_msg_count,
2228 p_msg_data => lv_exception_msg,
2229 p_web_user_id => p_user_id,
2230 p_attribute_code => p_attribute_code,
2231 p_attribute_appl_id =>p_app_id,
2232 p_varchar2_value => p_varchar2_value,
2233 p_date_value => p_date_value,
2234 p_number_value => p_number_value,
2235 p_created_by => fnd_global.user_id,
2236 p_creation_date => sysdate,
2237 p_last_updated_by => fnd_global.user_id,
2238 p_last_update_date => sysdate,
2239 p_last_update_login => fnd_global.login_id);
2240 EXCEPTION
2241 WHEN OTHERS THEN
2242
2243 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2244 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' ||
2245 lv_proc_name, 'failed in ICX_USER_SEC_ATTR_PVT.create_user_sec_attr, sqlerrm ' || Sqlerrm);
2246 END IF;
2247
2248 RAISE;
2249 END;
2250
2251 IF lv_status = 'S' THEN
2252 return;
2253 END IF;
2254
2255 IF ln_msg_count > 0 THEN
2256
2257 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2258 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name,
2259 'Failed to set securing attribute for ' ||
2260 ' user_id ' || p_user_id ||
2261 ', attribute code ' || p_attribute_code ||
2262 ', app id ' || p_app_id ||
2263 ', varchar2 value ' || p_varchar2_value ||
2264 ', date value ' || p_date_value ||
2265 ', number value ' || p_number_value
2266 );
2267 END IF;
2268
2269 END IF;
2270
2271 IF ln_msg_count = 1 THEN
2272
2273 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2274 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Error message: '||lv_exception_msg);
2275 END IF;
2276
2277 RAISE NO_DATA_FOUND;
2278 ELSIF ln_msg_count > 1 THEN
2279 FOR l_index IN 1..fnd_msg_pub.count_msg LOOP
2280 lv_exception_msg := fnd_msg_pub.get(l_index);
2281
2282 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2283 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Error message no. ' || l_index || ': ' || lv_exception_msg);
2284 END IF;
2285
2286 END LOOP;
2287 RAISE NO_DATA_FOUND;
2288 END IF;
2289 END SetSecAttr;
2290
2291 /*----------------------------------------
2292
2293 private PROCEDURE create_resp_sec_attr_ifneeded
2294
2295 Create the responsibility security attributes if it is not yet created
2296
2297 PARAM:
2298 p_resp_id IN NUMBER - responsibility id
2299 p_resp_appl_id IN NUMBER - responsibility application id
2300 p_sec_attr_code IN VARCHAR2 - security attribute code
2301 p_sec_attr_appl_id IN NUMBER - security attribute application id
2302
2303 EXCEPTION:
2304
2305 Raise exceptions.
2306
2307 ----------------------------------------*/
2308
2309 PROCEDURE create_resp_sec_attr_ifneeded
2310 (p_resp_id IN NUMBER,
2311 p_resp_appl_id IN NUMBER,
2312 p_sec_attr_code IN VARCHAR2,
2313 p_sec_attr_appl_id IN NUMBER
2314 )
2315 IS
2316 CURSOR l_cur IS
2317 SELECT 1
2318 FROM ak_resp_security_attributes
2319 WHERE responsibility_id = p_resp_id AND
2320 resp_application_id = p_resp_appl_id AND
2321 attribute_code = p_sec_attr_code AND
2322 attribute_application_id = p_sec_attr_appl_id;
2323 l_num NUMBER;
2324 lv_proc_name VARCHAR2(30) := 'create_resp_sec_attr_ifneeded';
2325
2326 BEGIN
2327 OPEN l_cur;
2328 FETCH l_cur INTO l_num;
2329 IF l_cur%found THEN
2330 CLOSE l_cur;
2331
2332 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2333 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, 'Responsibility Security Attribute for resp_id=' || p_resp_id || ' and attribute_code=' || p_sec_attr_code || ' exists.');
2334 END IF;
2335
2336 RETURN;
2337 END IF;
2338 CLOSE l_cur;
2339 --
2340
2341 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2342 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, 'Creating Responsibility Security Attribute for resp_id=' || p_resp_id || ' and attribute_code=' || p_sec_attr_code);
2343 END IF;
2344
2345
2346 INSERT INTO ak_resp_security_attributes
2347 (responsibility_id,
2348 resp_application_id,
2349 attribute_code,
2350 attribute_application_id,
2351 created_by,
2352 creation_date,
2353 last_updated_by,
2354 last_update_date,
2355 last_update_login
2356 )
2357 VALUES
2358 (p_resp_id,
2359 p_resp_appl_id,
2360 p_sec_attr_code,
2361 p_sec_attr_appl_id,
2362 fnd_global.user_id,
2363 Sysdate,
2364 fnd_global.user_id,
2365 Sysdate,
2366 fnd_global.login_id
2367 );
2368
2369
2370 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2371 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, 'Responsibility Security Attribute created');
2372 END IF;
2373
2374
2375 EXCEPTION
2376 WHEN OTHERS THEN
2377
2378 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2379 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
2380 END IF;
2381
2382 IF ( CheckError() IS NULL ) THEN
2383 SetErrMsg(MSG_UNEXPECTED_ERROR);
2384 END IF;
2385 RAISE;
2386 END create_resp_sec_attr_ifneeded;
2387
2388 /*----------------------------------------
2389
2390 private PROCEDURE set_resp_sec_attrval_ifneeded
2391
2392 Set the responsibility security attributes value if it is not yet set
2393
2394 PARAM:
2395 p_resp_id IN NUMBER - responsibility id
2396 p_resp_appl_id IN NUMBER - responsibility application id
2397 p_sec_attr_code IN VARCHAR2 - security attribute code
2398 p_sec_attr_appl_id IN NUMBER - security attribute application id
2399 p_varchar2_value IN VARCHAR2 DEFAULT NULL - the varchar2 value
2400 p_date_value IN DATE DEFAULT NULL - the data value
2401 p_number_value IN NUMBER DEFAULT NULL - the number value
2402
2403 EXCEPTION:
2404
2405 Raise exceptions.
2406
2407 ----------------------------------------*/
2408
2409 PROCEDURE set_resp_sec_attrval_ifneeded
2410 (p_resp_id IN NUMBER,
2411 p_resp_appl_id IN NUMBER,
2412 p_sec_attr_code IN VARCHAR2,
2413 p_sec_attr_appl_id IN NUMBER,
2414 p_varchar2_value IN VARCHAR2 DEFAULT NULL,
2415 p_date_value IN DATE DEFAULT NULL,
2416 p_number_value IN NUMBER DEFAULT NULL
2417 )
2418 IS
2419 CURSOR l_cur IS
2420 SELECT 1
2421 FROM ak_resp_security_attr_values
2422 WHERE responsibility_id = p_resp_id AND
2423 resp_application_id = p_resp_appl_id AND
2424 attribute_code = p_sec_attr_code AND
2425 attribute_application_id = p_sec_attr_appl_id;
2426 l_num NUMBER;
2427 lv_proc_name VARCHAR2(30) := 'set_resp_sec_attrval_ifneeded';
2428
2429 BEGIN
2430 OPEN l_cur;
2431 FETCH l_cur INTO l_num;
2432 IF l_cur%found THEN
2433 CLOSE l_cur;
2434
2435 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2436 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, 'No need to set Responsibility Security Attribute value for resp_id=' || p_resp_id || ' and attribute_code=' || p_sec_attr_code);
2437 END IF;
2438
2439 RETURN;
2440 END IF;
2441 CLOSE l_cur;
2442 --
2443
2444 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2445 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, 'Setting Responsibility Security Attribute value for resp_id=' || p_resp_id || ' and attribute_code=' || p_sec_attr_code);
2446 END IF;
2447
2448
2449 INSERT INTO ak_resp_security_attr_values
2450 (responsibility_id,
2451 resp_application_id,
2452 attribute_code,
2453 attribute_application_id,
2454 varchar2_value,
2455 date_value,
2456 number_value
2457 )
2458 VALUES
2459 (p_resp_id,
2460 p_resp_appl_id,
2461 p_sec_attr_code,
2462 p_sec_attr_appl_id,
2463 p_varchar2_value,
2464 p_date_value,
2465 p_number_value
2466 );
2467
2468
2469 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2470 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, 'Responsibility Security Attribute value set');
2471 END IF;
2472
2473
2474 EXCEPTION
2475 WHEN OTHERS THEN
2476
2477 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2478 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
2479 END IF;
2480
2481 IF ( CheckError() IS NULL ) THEN
2482 SetErrMsg(MSG_UNEXPECTED_ERROR);
2483 END IF;
2484 RAISE;
2485 END set_resp_sec_attrval_ifneeded;
2486
2487 /*----------------------------------------
2488
2489 PROCEDURE check_isp_resp_sec_attr
2490
2491 This procedure will create iSP securing attributes
2492 (ICX_SUPPLIER_ORG_ID, ICX_SUPPLIER_SITE_ID, ICX_SUPPLIER_CONTACT_ID)
2493 for the responsibility, if they are not defined. It will also
2494 set the default value (-9999) for the securing attributes
2495 ICX_SUPPLIER_SITE_ID and ICX_SUPPLIER_CONTACT_ID for the responsibility,
2496 if they are not defined.
2497
2498 PARAM:
2499 p_resp_id IN NUMBER - responsibility id
2500 p_resp_appl_id IN NUMBER - responsibility application id
2501
2502 EXCEPTION:
2503
2504 Raise exceptions.
2505
2506 ----------------------------------------*/
2507
2508 PROCEDURE check_isp_resp_sec_attr
2509 (p_resp_id IN NUMBER,
2510 p_resp_appl_id IN NUMBER)
2511 IS
2512 CURSOR l_app_id_cur IS
2513 SELECT application_id
2514 FROM fnd_application
2515 WHERE application_short_name = 'POS';
2516 l_isp_appl_id NUMBER;
2517 lv_proc_name VARCHAR2(30) := 'set_resp_sec_attrval_ifneeded';
2518
2519 BEGIN
2520 OPEN l_app_id_cur;
2521 FETCH l_app_id_cur INTO l_isp_appl_id;
2522 IF l_app_id_cur%notfound THEN
2523 CLOSE l_app_id_cur;
2524
2525 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2526 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'Application ID for POS is not found');
2527 END IF;
2528
2529 RAISE NO_DATA_FOUND;
2530 END IF;
2531 CLOSE l_app_id_cur;
2532 --
2533 -- only create the attribute, not set default value
2534 -- because we do want the value be set at the user level for ICX_SUPPLIER_ORG_ID
2535 create_resp_sec_attr_ifneeded(p_resp_id => p_resp_id,
2536 p_resp_appl_id => p_resp_appl_id,
2537 p_sec_attr_code => 'ICX_SUPPLIER_ORG_ID',
2538 p_sec_attr_appl_id => l_isp_appl_id
2539 );
2540 --
2541 create_resp_sec_attr_ifneeded(p_resp_id => p_resp_id,
2542 p_resp_appl_id => p_resp_appl_id,
2543 p_sec_attr_code => 'ICX_SUPPLIER_SITE_ID',
2544 p_sec_attr_appl_id => l_isp_appl_id
2545 );
2546 --
2547 set_resp_sec_attrval_ifneeded(p_resp_id => p_resp_id,
2548 p_resp_appl_id => p_resp_appl_id,
2549 p_sec_attr_code => 'ICX_SUPPLIER_SITE_ID',
2550 p_sec_attr_appl_id => l_isp_appl_id,
2551 p_varchar2_value => NULL,
2552 p_date_value => NULL,
2553 p_number_value => pos_default_sec_attr_value
2554 );
2555 --
2556 create_resp_sec_attr_ifneeded(p_resp_id => p_resp_id,
2557 p_resp_appl_id => p_resp_appl_id,
2558 p_sec_attr_code => 'ICX_SUPPLIER_CONTACT_ID',
2559 p_sec_attr_appl_id => l_isp_appl_id
2560 );
2561 --
2562 set_resp_sec_attrval_ifneeded(p_resp_id => p_resp_id,
2563 p_resp_appl_id => p_resp_appl_id,
2564 p_sec_attr_code => 'ICX_SUPPLIER_CONTACT_ID',
2565 p_sec_attr_appl_id => l_isp_appl_id,
2566 p_varchar2_value => NULL,
2567 p_date_value => NULL,
2568 p_number_value => pos_default_sec_attr_value
2569 );
2570 EXCEPTION
2571 WHEN OTHERS THEN
2572 IF ( CheckError() IS NULL ) THEN
2573 SetErrMsg(MSG_UNEXPECTED_ERROR);
2574 END IF;
2575 RAISE;
2576 END check_isp_resp_sec_attr;
2577
2578 /*----------------------------------------
2579
2580 PROCEDURE check_isp_resp_sec_attr
2581
2582 Overload check_isp_resp_sec_attr(NUMBER, NUMBER) to take the responsibility
2583 key.
2584
2585 PARAM:
2586 p_resp_key IN VARCHAR2 - the responsibility key
2587 p_resp_appl_id IN NUMBER - responsibility application id
2588
2589 EXCEPTION:
2590
2591 Raise exceptions.
2592
2593 ----------------------------------------*/
2594
2595 PROCEDURE check_isp_resp_sec_attr
2596 (p_resp_key IN VARCHAR2,
2597 p_resp_appl_id IN NUMBER)
2598 IS
2599 lv_proc_name VARCHAR2(30) := 'check_isp_resp_sec_attr';
2600 ln_resp_id NUMBER;
2601 --
2602 CURSOR l_resp_id_cur (p_appl_id NUMBER, p_resp_key VARCHAR2) IS
2603 SELECT responsibility_id
2604 FROM fnd_responsibility
2605 WHERE application_id = p_appl_id
2606 AND responsibility_key = p_resp_key
2607 AND (end_date IS NULL OR end_date > start_date);
2608 BEGIN
2609 OPEN l_resp_id_cur(p_resp_appl_id, p_resp_key);
2610 FETCH l_resp_id_cur INTO ln_resp_id;
2611 CLOSE l_resp_id_cur;
2612 IF ln_resp_id IS NULL THEN
2613
2614 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2615 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' ||
2616 lv_proc_name, 'Invalid responsibility key ' || p_resp_key);
2617 END IF;
2618
2619 RETURN;
2620 END IF;
2621 --
2622 check_isp_resp_sec_attr(ln_resp_id, p_resp_appl_id);
2623 END check_isp_resp_sec_attr;
2624
2625 /*----------------------------------------
2626
2627 public PROCEDURE CreateUser
2628
2629 Workflow activity function. Actually create the user in FND_USER.
2630 Following activities are done:
2631 * generate random user password
2632 * insert FND_USER
2633 * insert TCA
2634 * assign user responsibility
2635 * set user security attributes
2636
2637 Two item attribute values are set:
2638
2639 * ASSIGNED_USER_NAME * FIRST_LOGON_KEY
2640
2641 PARAMS:
2642 WF Standard API.
2643
2644 RETURN:
2645 WF Standard API.
2646
2647 ----------------------------------------*/
2648
2649 PROCEDURE CreateUser(
2650 itemtype IN VARCHAR2
2651 , itemkey IN VARCHAR2
2652 , actid IN NUMBER
2653 , funcmode IN VARCHAR2
2654 , resultout OUT NOCOPY VARCHAR2
2655 )
2656 IS
2657
2658 lv_reg_key WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
2659 ln_reg_id NUMBER := -1;
2660
2661 -- user info needed to update FND_USER and TCA
2662
2663 lv_user_name FND_USER.USER_NAME%TYPE;
2664 lv_unencrypted_password VARCHAR2(30);
2665
2666 --OSN: the encrypted initial password
2667 l_encrypted_initial_password VARCHAR2(240);
2668 l_retcode VARCHAR2(1);
2669
2670 lv_user_description FND_USER.DESCRIPTION%TYPE;
2671 lv_user_email FND_USER.EMAIL_ADDRESS%TYPE;
2672 ln_vendor_id NUMBER;
2673 lv_user_firstname WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
2674 lv_user_lastname WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
2675 lv_user_middlename WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
2676 lv_title WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
2677 lv_phone WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
2678 lv_phone_ext WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
2679 lv_fax WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
2680 lv_job_title WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;
2681
2682 ln_user_fnd_id NUMBER := -1;
2683 ln_pon_app_id NUMBER := -1;
2684 ln_pos_app_id NUMBER := -1;
2685 lv_is_pos_selected VARCHAR2(1) := 'N';
2686 lv_is_pon_selected VARCHAR2(1) := 'N';
2687 lv_is_msc_selected VARCHAR2(1) := 'N';
2688 lv_resp_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE := NULL;
2689 ln_site_id NUMBER := -1;
2690 ln_contact_id NUMBER := -1;
2691 ln_resp_code VARCHAR2(240);
2692 ln_resp_id NUMBER := -1;
2693 ln_resp_app_id NUMBER := -1;
2694 lvr_exception_msg VARCHAR2(4000);
2695 lvr_status VARCHAR2(30);
2696
2697 lv_external_web_agent fnd_profile_option_values.profile_option_value%TYPE := NULL;
2698 lv_ext_servlet_agent fnd_profile_option_values.profile_option_value%TYPE := NULL;
2699
2700 -- out parameters
2701 ln_party_id NUMBER := -1;
2702 ln_relationship_id NUMBER := -1;
2703 lv_exception_msg VARCHAR2(4000);
2704 lv_status VARCHAR2(30);
2705 ln_msg_count NUMBER := 0;
2706
2707 ln_counter NUMBER := 0;
2708 lv_proc_name VARCHAR2(30) := 'CreateUser';
2709
2710 CURSOR l_app_id_cur(p_app_short_name VARCHAR2) IS
2711 SELECT application_id
2712 FROM fnd_application
2713 WHERE application_short_name = p_app_short_name;
2714
2715 CURSOR l_site_cont_id_cur (p_reg_id NUMBER, p_field_name VARCHAR2) IS
2716 SELECT field_value_number
2717 FROM fnd_registration_details
2718 WHERE registration_id = p_reg_id
2719 AND field_name like p_field_name||'%';
2720
2721 CURSOR l_user_resp_app_cur (p_reg_id NUMBER, p_field_name VARCHAR2) IS
2722 SELECT field_value_string
2723 FROM fnd_registration_details
2724 WHERE registration_id = p_reg_id
2725 AND field_name like p_field_name||'%';
2726
2727 CURSOR l_fnd_user_cur(l_user_id IN NUMBER) IS
2728 SELECT * FROM fnd_user WHERE user_id = l_user_id;
2729
2730 l_fnd_user_rec l_fnd_user_cur%ROWTYPE;
2731
2732 lv_pattern VARCHAR2(40);
2733 lv_flag VARCHAR2(40);
2734
2735
2736 l_return_status VARCHAR2(1);
2737 l_msg_count NUMBER;
2738 l_msg_data VARCHAR2(3000);
2739
2740 l_resp_ids po_tbl_number;
2741 l_resp_app_ids po_tbl_number;
2742 l_sec_attr_codes po_tbl_varchar30;
2743 l_sec_attr_numbers po_tbl_number;
2744
2745 l_vendor_party_id NUMBER;
2746
2747 BEGIN
2748
2749
2750 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2751 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
2752 END IF;
2753
2754
2755 IF ( funcmode = 'RUN' ) then
2756
2757 -- retrieve user info
2758 lv_reg_key := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRATION_KEY',TRUE);
2759 IF ( lv_reg_key IS NULL ) THEN
2760
2761 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2762 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, g_log_reg_key_notfound);
2763 END IF;
2764
2765 RAISE NO_DATA_FOUND;
2766 END IF;
2767
2768 ln_reg_id := FND_REGISTRATION_UTILS_PKG.get_reg_id_from_key(lv_reg_key);
2769
2770 lv_user_name := upper(WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REQUESTED_USER_NAME'));
2771 lv_user_email := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRANT_EMAIL');
2772 lv_user_firstname := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'FIRST_NAME');
2773 lv_user_lastname := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'LAST_NAME');
2774 lv_user_middlename := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'MIDDLE_NAME');
2775 ln_vendor_id := WF_ENGINE.GetItemAttrNumber(itemtype, itemkey, 'VENDOR_ID');
2776 lv_title := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRANT_TITLE');
2777 lv_phone := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRANT_PHONE');
2778 lv_phone_ext := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRANT_PHONE_EXT');
2779 lv_fax := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRANT_FAX');
2780 lv_job_title := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'REGISTRANT_JOB_TITLE');
2781 lv_is_pos_selected := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'POS_SELECTED');
2782 lv_is_pon_selected := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'PON_SELECTED');
2783 lv_is_msc_selected := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'SC_SELECTED');
2784
2785 -- password generation
2786
2787 -- OSN: retrieve initial pwd set from OSN request, only when
2788 -- the initial password is not set do we generate user pwd.
2789 BEGIN
2790 l_encrypted_initial_password := NULL;
2791
2792 SELECT field_value_string
2793 INTO l_encrypted_initial_password
2794 FROM fnd_registration_details
2795 WHERE registration_id = ln_reg_id
2796 AND field_name = 'Initial Pass';
2797
2798 lv_unencrypted_password := decrypt (
2799 lv_reg_key,
2800 l_encrypted_initial_password
2801 );
2802
2803 EXCEPTION
2804 WHEN NO_DATA_FOUND THEN
2805 --if Initial Pass not found, then it's not an OSN request
2806 l_encrypted_initial_password := NULL;
2807 lv_unencrypted_password := NULL;
2808 END;
2809
2810 IF lv_unencrypted_password IS NULL THEN
2811 lv_unencrypted_password := pos_password_util_pkg.generate_user_pwd();
2812 END IF;
2813
2814 IF lv_unencrypted_password IS NULL THEN
2815 lv_unencrypted_password := fnd_crypto.smallrandomnumber();
2816
2817 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2818 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'unable to generate password');
2819 END IF;
2820
2821 END IF;
2822
2823 --insert FND_USER
2824 BEGIN
2825
2826 SELECT 1
2827 INTO ln_counter
2828 FROM fnd_user
2829 WHERE user_name = lv_user_name
2830 AND ROWNUM = 1;
2831
2832 SetErrMsg(MSG_FND_USER_DUPLICATE);
2833 resultout := 'COMPLETE:ERROR';
2834 RAISE TOO_MANY_ROWS;
2835
2836 EXCEPTION
2837 WHEN NO_DATA_FOUND THEN
2838 NULL; -- good. to create user later
2839
2840 WHEN OTHERS THEN
2841
2842 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2843 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.selectfnduser', Sqlerrm);
2844 END IF;
2845
2846 IF ( CheckError() IS NULL ) THEN
2847 SetErrMsg(MSG_USER_CREATION_FAIL);
2848 resultout := 'COMPLETE:ERROR';
2849 END IF;
2850 RAISE;
2851 END;
2852
2853 -- Create User Party in TCA
2854
2855 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2856 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Creating vendor user');
2857 END IF;
2858
2859 SELECT party_id INTO l_vendor_party_id FROM po_vendors WHERE vendor_id = ln_vendor_id;
2860
2861 pos_supp_contact_pkg.create_supplier_contact
2862 (p_vendor_party_id => l_vendor_party_id,
2863 p_first_name => lv_user_firstname,
2864 p_last_name => lv_user_lastname,
2865 p_middle_name => NULL,
2866 p_contact_title => NULL,
2867 p_job_title => NULL,
2868 p_phone_area_code => NULL,
2869 p_phone_extension => NULL,
2870 p_fax_area_code => NULL,
2871 p_fax_number => NULL,
2872 p_email_address => lv_user_email,
2873 x_return_status => l_return_status,
2874 x_msg_count => l_msg_count,
2875 x_msg_data => l_msg_data,
2876 x_person_party_id => ln_party_id
2877 );
2878
2879 IF l_return_status IS NULL OR l_return_status <> fnd_api.g_ret_sts_success THEN
2880
2881 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2882 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name, 'POS_VENDOR_UTIL_PKG error message: '||lv_exception_msg);
2883 END IF;
2884
2885 SetErrMsg(MSG_VENDORUSER_CREATION_FAIL);
2886 resultout := 'COMPLETE:ERROR';
2887 -- hack!!! just to raise an exception
2888 RAISE NO_DATA_FOUND;
2889 END IF;
2890
2891
2892 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2893 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Vendor user created');
2894 END IF;
2895
2896 pos_user_admin_pkg.create_supplier_user_account
2897 (p_user_name => lv_user_name,
2898 p_user_email => lv_user_email,
2899 p_person_party_id => ln_party_id,
2900 p_resp_ids => l_resp_ids,
2901 p_resp_app_ids => l_resp_app_ids,
2902 p_sec_attr_codes => l_sec_attr_codes,
2903 p_sec_attr_numbers => l_sec_attr_numbers,
2904 p_password => lv_unencrypted_password,
2905 x_return_status => l_return_status,
2906 x_msg_count => l_msg_count,
2907 x_msg_data => l_msg_data,
2908 x_user_id => ln_user_fnd_id,
2909 x_password => lv_unencrypted_password
2910 );
2911
2912 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2913 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Creating vendor user details');
2914 END IF;
2915
2916 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'ASSIGNED_USER_NAME', upper(lv_user_name));
2917
2918 WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'FIRST_LOGON_KEY', lv_unencrypted_password);
2919
2920 -- get application ids
2921 OPEN l_app_id_cur('POS');
2922 FETCH l_app_id_cur INTO ln_pos_app_id;
2923 CLOSE l_app_id_cur;
2924
2925 OPEN l_app_id_cur('PON');
2926 FETCH l_app_id_cur INTO ln_pon_app_id;
2927 CLOSE l_app_id_cur;
2928
2929 -- Set user Responsibility
2930
2931
2932 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2933 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Setting user responsibility');
2934 END IF;
2935
2936 OPEN l_user_resp_app_cur(ln_reg_id, 'POS_SUPPLIER_RESP_ID');
2937 ln_counter := 0;
2938 LOOP
2939
2940 FETCH l_user_resp_app_cur INTO ln_resp_code;
2941 EXIT WHEN l_user_resp_app_cur%NOTFOUND;
2942 BEGIN
2943 -- Break the string into numbers
2944 ln_resp_id := TO_NUMBER(SUBSTR(ln_resp_code, 0, INSTR(ln_resp_code, ':') - 1));
2945 ln_resp_app_id := TO_NUMBER(SUBSTR(ln_resp_code, INSTR(ln_resp_code, ':') + 1));
2946 POS_USER_ADMIN_PKG.grant_user_resp(ln_user_fnd_id, ln_resp_id, ln_resp_app_id, lvr_status, lvr_exception_msg);
2947 IF ln_resp_app_id = ln_pos_app_id THEN
2948 check_isp_resp_sec_attr(ln_resp_id,ln_resp_app_id);
2949 END IF;
2950 ln_counter := ln_counter + 1;
2951 EXCEPTION
2952 WHEN OTHERS THEN
2953 SetErrMsg(MSG_USER_RESP_FAIL);
2954 resultout := 'COMPLETE:ERROR';
2955 RAISE;
2956 END;
2957 END LOOP;
2958 CLOSE l_user_resp_app_cur;
2959
2960 -- set supplier security attribute
2961
2962
2963 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2964 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Setting supplier security attribute');
2965 END IF;
2966
2967 BEGIN
2968 --SetSecAttr(ln_user_fnd_id, 'ICX_SUPPLIER_ORG_ID', ln_pos_app_id, NULL, NULL, ln_vendor_id);
2969
2970 OPEN l_site_cont_id_cur(ln_reg_id, 'POS_SUPPLIER_ID');
2971 ln_counter := 0;
2972 LOOP
2973 FETCH l_site_cont_id_cur INTO ln_site_id;
2974 EXIT WHEN l_site_cont_id_cur%NOTFOUND;
2975 BEGIN
2976 SetSecAttr(ln_user_fnd_id, 'ICX_SUPPLIER_ORG_ID', ln_pos_app_id, NULL, NULL, ln_site_id);
2977 END;
2978 END LOOP;
2979 CLOSE l_site_cont_id_cur;
2980 EXCEPTION
2981 WHEN OTHERS THEN
2982 SetErrMsg(MSG_POS_SUPP_SECATTR_FAIL);
2983 resultout := 'COMPLETE:ERROR';
2984 RAISE;
2985 END;
2986
2987
2988 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2989 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Supplier security attribute set');
2990 END IF;
2991
2992
2993 -- set site security attribute
2994
2995 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2996 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Setting site security attribute');
2997 END IF;
2998
2999 OPEN l_site_cont_id_cur(ln_reg_id, 'POS_SUPPLIER_SITE_ID');
3000 ln_counter := 0;
3001 LOOP
3002 FETCH l_site_cont_id_cur INTO ln_site_id;
3003 EXIT WHEN l_site_cont_id_cur%NOTFOUND;
3004 BEGIN
3005 SetSecAttr(ln_user_fnd_id, 'ICX_SUPPLIER_SITE_ID', ln_pos_app_id, NULL, NULL, ln_site_id);
3006 ln_counter := ln_counter + 1;
3007 EXCEPTION
3008 WHEN OTHERS THEN
3009 SetErrMsg(MSG_POS_SITE_SECATTR_FAIL);
3010 resultout := 'COMPLETE:ERROR';
3011 RAISE;
3012 END;
3013 END LOOP;
3014 CLOSE l_site_cont_id_cur;
3015
3016
3017 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3018 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Site security attribute set');
3019 END IF;
3020
3021
3022 -- set contact security attribute
3023
3024 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3025 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Setting contact security attribute');
3026 END IF;
3027
3028 OPEN l_site_cont_id_cur(ln_reg_id, 'POS_SUPPLIER_CONTACT_ID');
3029 ln_counter := 0;
3030 LOOP
3031 FETCH l_site_cont_id_cur INTO ln_contact_id;
3032 EXIT WHEN l_site_cont_id_cur%NOTFOUND;
3033 BEGIN
3034 SetSecAttr(ln_user_fnd_id, 'ICX_SUPPLIER_CONTACT_ID', ln_pos_app_id, NULL, NULL, ln_contact_id);
3035 ln_counter := ln_counter + 1;
3036 EXCEPTION
3037 WHEN OTHERS THEN
3038 SetErrMsg(MSG_POS_CONTACT_SECATTR_FAIL);
3039 resultout := 'COMPLETE:ERROR';
3040 RAISE;
3041 END;
3042 END LOOP;
3043 CLOSE l_site_cont_id_cur;
3044
3045
3046 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3047 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Contact security attribute set');
3048 END IF;
3049
3050
3051 -- set user level APPS_WEB_AGENT profile option value
3052 set_profile_opt_ext_user(p_userid => ln_user_fnd_id);
3053
3054 --OSN: need to set the profile to identify this user as a local user
3055 IF l_encrypted_initial_password IS NOT NULL THEN
3056 IF ( fnd_profile.save(
3057 X_NAME => 'APPS_SSO_LOCAL_LOGIN',
3058 -- 'Applications SSO Login Types' (Both/Local/SSO)
3059 X_VALUE => 'Local',
3060 X_LEVEL_NAME => 'USER',
3061 X_LEVEL_VALUE => to_char(ln_user_fnd_id),
3062 X_LEVEL_VALUE_APP_ID => NULL) ) THEN
3063 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3064 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_SSO_LOCAL_LOGIN profile option value set');
3065 END IF;
3066 ELSE
3067 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3068 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_SSO_LOCAL_LOGIN profile option value fail');
3069 END IF;
3070 END IF;
3071 END IF;
3072
3073 resultout := 'COMPLETE:SUCCESS';
3074
3075 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3076 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
3077 END IF;
3078
3079 RETURN;
3080
3081 END IF; -- funcmode = 'RUN'
3082
3083 EXCEPTION
3084 WHEN OTHERS THEN
3085
3086 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3087 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', Sqlerrm);
3088 END IF;
3089
3090 IF ( CheckError() IS NULL ) THEN
3091 SetErrMsg(MSG_UNEXPECTED_ERROR);
3092 resultout := 'COMPLETE:ERROR';
3093 END IF;
3094 WF_CORE.CONTEXT (V_PACKAGE_NAME, 'CREATEUSER', itemtype, itemkey, to_char(actid), funcmode);
3095 RAISE;
3096 END CreateUser;
3097
3098
3099 /*---------------------------------------
3100
3101 public
3102 Procedure to set profile options for external user.
3103 This procedure set the APPS_FRAMEWORK_AGENT and
3104 APPS_WEB_AGENT for external user
3105
3106 */
3107
3108 PROCEDURE set_profile_opt_ext_user
3109 (p_userid in number)
3110 is
3111 lv_external_web_agent fnd_profile_option_values.profile_option_value%TYPE := NULL;
3112 lv_ext_servlet_agent fnd_profile_option_values.profile_option_value%TYPE := NULL;
3113 lv_pattern VARCHAR2(40);
3114 lv_flag VARCHAR2(40);
3115 lv_proc_name VARCHAR2(30) := 'set_profile_opt_ext_user';
3116 begin
3117
3118 fnd_profile.get('POS_EXTERNAL_URL', lv_external_web_agent);
3119 fnd_profile.get('POS_EXTERNAL_URL', lv_ext_servlet_agent);
3120
3121 IF ( lv_external_web_agent IS NOT NULL ) THEN
3122
3123 lv_pattern := '/pls';
3124 lv_flag := ''; -- we want it to be case sensitive for now.
3125 If (owa_pattern.match(lv_external_web_agent,lv_pattern, lv_flag)) then
3126 -- The external profile still points to icx web site.
3127
3128 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3129 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'Setting user level APPS_WEB_AGENT profile option value');
3130 END IF;
3131
3132 IF ( fnd_profile.save( x_name => 'APPS_WEB_AGENT',
3133 x_value => lv_external_web_agent,
3134 x_level_name => 'USER',
3135 x_level_value => p_userid ) ) THEN
3136
3137 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3138 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_WEB_AGENT profile option value set');
3139 END IF;
3140
3141 ELSE
3142
3143 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3144 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_WEB_AGENT profile option value fail');
3145 END IF;
3146
3147 END IF;
3148
3149 owa_pattern.change(lv_ext_servlet_agent, '/pls.*', '/oa_servlets/');
3150 IF ( fnd_profile.save( x_name => 'APPS_SERVLET_AGENT',
3151 x_value => lv_ext_servlet_agent,
3152 x_level_name => 'USER',
3153 x_level_value => p_userid ) ) THEN
3154
3155 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3156 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_SERVLET_AGENT profile option value set');
3157 END IF;
3158
3159 ELSE
3160
3161 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3162 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_SERVLET_AGENT profile option value fail');
3163 END IF;
3164
3165 END IF;
3166
3167 owa_pattern.change(lv_ext_servlet_agent, '/oa_servlets.*', '');
3168 IF ( fnd_profile.save( x_name => 'APPS_FRAMEWORK_AGENT',
3169 x_value => lv_ext_servlet_agent,
3170 x_level_name => 'USER',
3171 x_level_value => p_userid ) ) THEN
3172
3173 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3174 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value set');
3175 END IF;
3176
3177 ELSE
3178
3179 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3180 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value fail');
3181 END IF;
3182
3183 END IF;
3184 ELSE
3185 IF ( fnd_profile.save( x_name => 'APPS_FRAMEWORK_AGENT',
3186 x_value => lv_ext_servlet_agent,
3187 x_level_name => 'USER',
3188 x_level_value => p_userid ) ) THEN
3189
3190 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3191 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value set');
3192 END IF;
3193
3194 ELSE
3195
3196 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3197 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_FRAMEWORK_AGENT profile option value fail');
3198 END IF;
3199
3200 END IF;
3201 -- set only the framework agent. there is no way to set web agent
3202 -- as we dont know the external dbc name.
3203 lv_ext_servlet_agent := lv_ext_servlet_agent || '/oa_servlets';
3204 IF ( fnd_profile.save( x_name => 'APPS_SERVLET_AGENT',
3205 x_value => lv_ext_servlet_agent,
3206 x_level_name => 'USER',
3207 x_level_value => p_userid ) ) THEN
3208
3209 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3210 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_servlet_AGENT profile option value set');
3211 END IF;
3212
3213 ELSE
3214
3215 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3216 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'User level APPS_servlet_AGENT profile option value fail');
3217 END IF;
3218
3219 END IF;
3220 End if;
3221
3222 ELSE
3223
3224 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3225 fnd_log.string(fnd_log.level_procedure, g_log_module_name || '.' || lv_proc_name, 'POS_EXTERNAL_URL is not set');
3226 END IF;
3227
3228 END IF;
3229
3230
3231 end set_profile_opt_ext_user;
3232
3233
3234 /*----------------------------------------
3235
3236 public PROCEDURE IsInvited
3237
3238 Workflow activity function. Check whether the registration is invited.
3239
3240 PARAMS:
3241 WF Standard API.
3242
3243 RETURN:
3244 COMPLETE:Y - if the registration is invited;
3245 COMPLETE:N - otherwise
3246
3247 ----------------------------------------*/
3248
3249 PROCEDURE IsInvited(
3250 itemtype IN VARCHAR2
3251 , itemkey IN VARCHAR2
3252 , actid IN NUMBER
3253 , funcmode IN VARCHAR2
3254 , resultout OUT NOCOPY VARCHAR2
3255 )
3256 IS
3257
3258 lv_invitation_flag VARCHAR2(30) := NULL;
3259 lv_proc_name VARCHAR2(30) := 'IsInvited';
3260
3261 BEGIN
3262
3263
3264 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3265 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
3266 END IF;
3267
3268
3269 IF ( funcmode = 'RUN' ) then
3270
3271 lv_invitation_flag := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'IS_INVITED');
3272
3273 IF ( lv_invitation_flag IS NOT NULL AND lv_invitation_flag = 'Y') THEN
3274 resultout := 'COMPLETE:Y';
3275 ELSE
3276 resultout := 'COMPLETE:N';
3277 END IF;
3278 END IF;
3279
3280 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3281 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
3282 END IF;
3283
3284 RETURN;
3285
3286 EXCEPTION
3287 WHEN OTHERS THEN
3288 -- do not raise exception. if something wrong, just assume non-invitation
3289
3290 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3291 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', sqlerrm);
3292 END IF;
3293
3294 resultout := 'COMPLETE:N';
3295 RETURN;
3296 END IsInvited;
3297
3298 /*----------------------------------------
3299
3300 public PROCEDURE CheckRejectMailSent
3301
3302 Workflow activity function. Check whether the rejection email has been
3303 sent or not
3304
3305 PARAMS:
3306 WF Standard API.
3307
3308 RETURN:
3309 COMPLETE:Y - if the mail has been sent
3310 COMPLETE:N - otherwise
3311
3312 ----------------------------------------*/
3313
3314 PROCEDURE CheckRejectMailSent(
3315 itemtype IN VARCHAR2
3316 , itemkey IN VARCHAR2
3317 , actid IN NUMBER
3318 , funcmode IN VARCHAR2
3319 , resultout OUT NOCOPY VARCHAR2
3320 )
3321 IS
3322 lv_proc_name VARCHAR2(30) := 'CheckRejectMailSent';
3323 BEGIN
3324
3325
3326 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3327 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
3328 END IF;
3329
3330
3331 IF ( funcmode = 'RUN' ) then
3332
3333 SELECT DECODE(wn.mail_status, 'MAIL', 'COMPLETE:N', 'COMPLETE:Y')
3334 INTO resultout
3335 FROM wf_item_activity_statuses_v ws, wf_notifications wn
3336 WHERE ws.item_type = itemtype
3337 AND ws.item_key = itemkey
3338 AND ws.notification_id = wn.notification_id;
3339
3340 RETURN;
3341
3342 END IF;
3343
3344
3345 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3346 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
3347 END IF;
3348
3349
3350 EXCEPTION WHEN OTHERS THEN
3351 -- do not raise exception. if something wrong, just assume 'SENT'
3352
3353 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3354 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', sqlerrm);
3355 END IF;
3356
3357 resultout := 'COMPLETE:Y';
3358 RETURN;
3359 END CheckRejectMailSent;
3360
3361 /*----------------------------------------
3362
3363 public PROCEDURE MarkSuccess
3364
3365 Workflow activity function. Mark the success of this workflow process
3366 by pushing "success" message.
3367
3368 PARAMS:
3369 WF Standard API.
3370
3371 RETURN:
3372 WF Standard API.
3373
3374 ----------------------------------------*/
3375
3376 PROCEDURE MarkSuccess(
3377 itemtype IN VARCHAR2
3378 , itemkey IN VARCHAR2
3379 , actid IN NUMBER
3380 , funcmode IN VARCHAR2
3381 , resultout OUT NOCOPY VARCHAR2
3382 )
3383 IS
3384
3385 lv_proc_name VARCHAR(30) := 'MarkSuccess';
3386
3387 BEGIN
3388
3389
3390 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3391 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
3392 END IF;
3393
3394
3395 IF ( funcmode = 'RUN' ) then
3396
3397 SetErrMsg(FND_REGISTRATION_UTILS_PKG.EVENT_SUCCESS);
3398 resultout := 'COMPLETE';
3399
3400 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3401 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
3402 END IF;
3403
3404 RETURN;
3405 END IF;
3406
3407 EXCEPTION WHEN OTHERS THEN
3408 IF ( CheckError() IS NULL ) THEN
3409 SetErrMsg(MSG_UNEXPECTED_ERROR);
3410 resultout := 'COMPLETE';
3411 END IF;
3412 WF_CORE.CONTEXT (V_PACKAGE_NAME, 'MARKSUCCESS', itemtype, itemkey, to_char(actid), funcmode);
3413 RAISE;
3414 END MarkSuccess;
3415
3416 function isPasswordChangeable(username in varchar2) return varchar2
3417 is
3418 BEGIN
3419 if ( fnd_sso_manager.isPasswordChangeable(username) ) then
3420 return 'Y';
3421 else
3422 return 'N';
3423 end if;
3424 END isPasswordChangeable;
3425
3426
3427 /*----------------------------------------
3428 function set_initial_password
3429 set the initial password for osn
3430 registration request, so the user
3431 doesn't need to change password at
3432 first logon
3433
3434 set_initial_password needs to be called
3435 after the user is created
3436 ----------------------------------------*/
3437 function set_initial_password(l_reg_id NUMBER)
3438 return varchar2
3439
3440 is
3441 l_user_name FND_USER.USER_NAME%TYPE;
3442 l_osn_req_id NUMBER;
3443 l_internal_id NUMBER;
3444 l_reg_key FND_REGISTRATIONS.REGISTRATION_KEY%TYPE;
3445 l_encrypted_initial_password VARCHAR2(240);
3446 l_initial_password VARCHAR2(30);
3447 l_retcode VARCHAR2(1);
3448 begin
3449 l_encrypted_initial_password := NULL;
3450 l_initial_password := NULL;
3451
3452 --r12 requirement to use centralized password management
3453 select r.requested_user_name, d1.field_value_number, d2.field_value_number
3454 into l_user_name, l_osn_req_id, l_internal_id
3455 from fnd_registrations r,
3456 fnd_registration_details d1, fnd_registration_details d2
3457 where r.registration_id = l_reg_id
3458 and d1.registration_id = l_reg_id
3459 and d2.registration_id = l_reg_id
3460 and d1.field_name = 'OSN Request ID'
3461 and d2.field_name = 'OSN Request InternalID';
3462
3463 l_initial_password := fnd_vault.get('POS_OSN',
3464 to_char(l_osn_req_id) || '_' || to_char(l_internal_id) );
3465
3466 -- to be compatible with old requests
3467 if (l_initial_password is NULL) then
3468 select d.field_value_string, r.requested_user_name, r.registration_key
3469 into l_encrypted_initial_password, l_user_name, l_reg_key
3470 from fnd_registration_details d, fnd_registrations r
3471 where r.registration_id = l_reg_id
3472 and d.registration_id = l_reg_id
3473 and d.field_name = 'Initial Pass';
3474
3475 l_initial_password := decrypt (
3476 l_reg_key,
3477 l_encrypted_initial_password
3478 );
3479 end if;
3480
3481 if (l_initial_password is NULL) then
3482 return 'N';
3483 end if;
3484
3485 --call fnd_web_sec.change_password: the same routine
3486 --when user first time logon and change his/her password
3487 l_retcode := fnd_web_sec.change_password (
3488 l_user_name,
3489 l_initial_password
3490 );
3491
3492 fnd_vault.del('POS_OSN',
3493 to_char(l_osn_req_id) || '_' || to_char(l_internal_id) );
3494
3495 return l_retcode;
3496 exception
3497 when others then
3498 return 'N';
3499 end set_initial_password;
3500
3501 /*----------------------------------------
3502
3503 public PROCEDURE IsOsnRequest
3504
3505 Workflow activity function. Check whether the registration is
3506 from Oracle Supplier Network.
3507
3508 PARAMS:
3509 WF Standard API.
3510
3511 RETURN:
3512 COMPLETE:Y - if the registration is from OSN;
3513 COMPLETE:N - otherwise
3514
3515 ----------------------------------------*/
3516
3517 PROCEDURE IsOsnRequest(
3518 itemtype IN VARCHAR2
3519 , itemkey IN VARCHAR2
3520 , actid IN NUMBER
3521 , funcmode IN VARCHAR2
3522 , resultout OUT NOCOPY VARCHAR2
3523 )
3524 IS
3525
3526 lv_osnrequest_flag VARCHAR2(30) := NULL;
3527 lv_proc_name VARCHAR2(30) := 'IsOsnRequest';
3528
3529 BEGIN
3530
3531
3532 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3533 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_start);
3534 END IF;
3535
3536
3537 IF ( funcmode = 'RUN' ) then
3538
3539 lv_osnrequest_flag := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'IS_OSNREQUEST');
3540
3541 IF ( lv_osnrequest_flag IS NOT NULL AND lv_osnrequest_flag = 'Y') THEN
3542 resultout := 'COMPLETE:Y';
3543 ELSE
3544 resultout := 'COMPLETE:N';
3545 END IF;
3546 END IF;
3547
3548 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3549 fnd_log.string(fnd_log.level_procedure,g_log_module_name || '.' || lv_proc_name, g_log_proc_end);
3550 END IF;
3551
3552 RETURN;
3553
3554 EXCEPTION
3555 WHEN OTHERS THEN
3556 -- do not raise exception. if something's wrong, just assume non-osnrequest
3557
3558 IF ( fnd_log.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3559 fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.exception', sqlerrm);
3560 END IF;
3561
3562 resultout := 'COMPLETE:N';
3563 RETURN;
3564 END IsOsnRequest;
3565
3566 END POS_REG_WF_PKG;
3567