[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_USERTYPE_CREDENTIALS
Source
1 PACKAGE BODY JTF_UM_USERTYPE_CREDENTIALS as
2 /* $Header: JTFUMUCB.pls 120.18.12020000.2 2012/07/18 16:11:01 anurtrip ship $ */
3
4 MODULE_NAME CONSTANT VARCHAR2(50) := 'JTF.UM.PLSQL.JTF_UM_USERTYPE_CREDENTIALS';
5 l_is_debug_parameter_on boolean := JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME);
6
7
8
9 PROCEDURE CREATE_ACCOUNT
10 (
11 P_PARTY_ID NUMBER,
12 P_PARTY_TYPE VARCHAR2,
13 P_ORG_PARTY_ID NUMBER:=FND_API.G_MISS_NUM
14 )
15 IS
16
17
18
19 l_party_type HZ_PARTIES.party_type%type;
20
21 p_approval_id NUMBER := -1;
22 p_account_number NUMBER;
23 x_return_status VARCHAR2(100);
24 x_msg_count NUMBER;
25 x_msg_data VARCHAR2(1000);
26 x_cust_account_id NUMBER;
27 x_cust_account_number VARCHAR2(30);
28 x_party_id NUMBER;
29 x_party_number VARCHAR2(30);
30 x_profile_id NUMBER;
31 x_cust_account_role_id NUMBER;
32 cust_acct_roles_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.CUST_ACCOUNT_ROLE_REC_TYPE;
33 l_procedure_name varchar2(30) := 'CREATE_ACCOUNT';
34
35 l_gen_cust_no VARCHAR2(1);
36 BEGIN
37 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
38 p_message => l_procedure_name);
39
40
41
42
43 l_party_type := JTF_UM_UTIL_PVT.check_party_type(P_PARTY_ID);
44
45 if l_is_debug_parameter_on then
46 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
47 p_message =>'l_party_type:' || l_party_type
48 );
49 end if;
50
51
52 begin
53 /* SELECT generate_customer_number INTO l_gen_cust_no
54 FROM ar_system_parameters;*/
55 l_gen_cust_no := HZ_MO_GLOBAL_CACHE.get_generate_customer_number();
56
57 exception when no_data_found then
58 l_gen_cust_no := 'Y';
59 end;
60
61 IF l_gen_cust_no <> 'Y' THEN
62 SELECT hz_account_num_s.nextval into p_account_number FROM DUAL;
63 END IF;
64
65 -- SELECT hz_account_num_s.nextval into p_account_number FROM DUAL;
66
67
68 if l_party_type = 'PERSON' then
69
70 -- create new account for Individual User
71
72 jtf_customer_accounts_pvt.create_account(
73 p_api_version => 1,
74 p_init_msg_list => 'T',
75 p_commit => 'F',
76 p_party_id => P_PARTY_ID,
77 p_account_number => p_account_number,
78 p_create_amt => 'F',
79 p_party_type => 'P',
80 x_return_status => x_return_status,
81 x_msg_count => x_msg_count,
82 x_msg_data => x_msg_data,
83 x_cust_account_id => x_cust_account_id,
84 x_cust_account_number => x_cust_account_number,
85 x_party_id => x_party_id,
86 x_party_number => x_party_number,
87 x_profile_id => x_profile_id
88 );
89
90 elsif l_party_type='PARTY_RELATIONSHIP'
91 then
92
93 -- create new account for Primary User or businessuser who was not assigned
94 -- one when he was approved.
95
96 jtf_customer_accounts_pvt.create_account(
97 p_api_version => 1,
98 p_init_msg_list => 'T',
99 p_commit => 'F',
100 p_party_id => P_ORG_PARTY_ID,
101 p_account_number => p_account_number,
102 p_create_amt => 'F',
103 p_party_type => 'O',
104 x_return_status => x_return_status,
105 x_msg_count => x_msg_count,
106 x_msg_data => x_msg_data,
107 x_cust_account_id => x_cust_account_id,
108 x_cust_account_number => x_cust_account_number,
109 x_party_id => x_party_id,
110 x_party_number => x_party_number,
111 x_profile_id => x_profile_id
112 );
113
114 if l_is_debug_parameter_on then
115 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
116 p_message =>'X_return_status@create_account:' ||x_return_status
117 );
118 end if;
119
120 end if;
121 if(x_return_status <> fnd_api.g_ret_sts_success)
122 then
123 jtf_debug_pub.log_exception(p_module => MODULE_NAME
124 ,p_message => ' message stack in account creation:'||x_msg_data);
125 raise_application_error(-20101,'Failed to create account:'||x_msg_data);
126
127 end if;
128 -- Creating record in hz_cust_account_roles
129 /*
130 -- added check for bug 4291085,4480150
131 -- create a acct role only for party relationship
132 */
133
134
135 if x_cust_account_id is not null and l_party_type = 'PARTY_RELATIONSHIP' then
136
137
138 cust_acct_roles_rec.cust_account_id := x_cust_account_id;
139 cust_acct_roles_rec.party_id := P_PARTY_ID;
140 cust_acct_roles_rec.role_type := 'CONTACT';
141 cust_acct_roles_rec.status := 'A';
142 cust_acct_roles_rec.created_by_module := 'JTA_USER_MANAGEMENT';
143 hz_cust_account_role_v2pub.create_cust_account_role(
144 p_init_msg_list =>'T',
145 p_cust_account_role_rec => cust_acct_roles_rec,
146 x_cust_account_role_id => x_cust_account_role_id,
147 x_return_status => x_return_status,
148 x_msg_count => x_msg_count,
149 x_msg_data => x_msg_data
150 );
151
152 if(x_return_status <> fnd_api.g_ret_sts_success)
153 then
154 raise_application_error(-20101,'Failed to create cust account role:'||x_msg_data);
155 end if;
156 end if;
157
158 END CREATE_ACCOUNT;
159
160
161
162 PROCEDURE QUERY_ACCOUNT
163 (
164 X_USER_ID NUMBER
165 )
166 IS
167 CURSOR CUSTOMER_ID
168 IS
169 SELECT CUSTOMER_ID
170 FROM FND_USER
171 WHERE USER_ID = X_USER_ID
172 AND (NVL(END_DATE,SYSDATE+1) > SYSDATE OR to_char(END_DATE) = to_char(FND_API.G_MISS_DATE)) ;
173
174 CURSOR ORG_PARTY_ID
175 IS
176 SELECT FU.CUSTOMER_ID,HZR.SUBJECT_ID,HZR.OBJECT_ID
177 FROM hz_relationships HZR,FND_USER FU
178 WHERE FU.USER_ID = X_USER_ID
179 AND FU.CUSTOMER_ID = HZR.PARTY_ID
180 AND HZR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
181 AND HZR.subject_type = 'PERSON'
182 AND HZR.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
183 AND HZR.object_table_name = 'HZ_PARTIES'
184 AND HZR.START_DATE <= SYSDATE
185 AND NVL(HZR.END_DATE, SYSDATE + 1) > SYSDATE
186 AND ( NVL(FU.end_date,SYSDATE+1) > SYSDATE OR to_char(FU.END_DATE) = to_char(FND_API.G_MISS_DATE));
187
188 l_procedure_name CONSTANT varchar2(30) := 'QUERY_ACCOUNT';
189 l_party_type HZ_PARTIES.party_type%type;
190 l_customer_id NUMBER;
191 P_USERTYPE_KEY VARCHAR2(80);
192 P_ACCOUNT_ROLE_ID NUMBER := -1;
193 P_PARTY_ID NUMBER;
194 P_SUBJECT_ID NUMBER;
195 P_OBJECT_ID NUMBER;
196 L_ACCT_CNT NUMBER :=-1;
197
198 /* This profile is now obsoleted - Bug 3493035
199 l_profile BOOLEAN := TRUE;
200 */
201
202
203 BEGIN
204 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
205 p_message => l_procedure_name);
206
207
208
209 if l_is_debug_parameter_on then
210 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
211 p_message =>'X_USER_ID:' || X_USER_ID
212 );
213 end if;
214 OPEN CUSTOMER_ID;
215 FETCH CUSTOMER_ID INTO l_customer_id;
216 CLOSE CUSTOMER_ID;
217
218 l_party_type := JTF_UM_UTIL_PVT.check_party_type(l_customer_id);
219
220 IF(l_party_type = 'PARTY_RELATIONSHIP')
221 THEN
222
223 OPEN ORG_PARTY_ID;
224 FETCH ORG_PARTY_ID INTO P_PARTY_ID, P_SUBJECT_ID,P_OBJECT_ID;
225 CLOSE ORG_PARTY_ID;
226
227 --check if any a/c has been created for the ORG and if that a/c is
228 -- associated with this user(B2B/B2C)
229
230 SELECT COUNT(*) into L_ACCT_CNT
231 FROM HZ_CUST_ACCOUNTS WHERE
232 CUST_ACCOUNT_ID IN( SELECT CUST_ACCOUNT_ID
233 FROM HZ_CUST_ACCOUNT_ROLES
234 WHERE PARTY_ID = p_party_id
235 AND STATUS = 'A') AND STATUS='A' ;
236
237
238
239 IF l_is_debug_parameter_on THEN
240 JTF_DEBUG_PUB.LOG_PARAMETERS
241 (p_module => MODULE_NAME,
242 p_message =>'P_PARTY_ID' ||P_PARTY_ID||'+ P_SUBJECT_ID'||P_SUBJECT_ID
243 );
244 END IF;
245
246
247
248 ELSIF(l_party_type = 'PERSON')
249 THEN
250
251 P_PARTY_ID := l_customer_id;
252
253 /* Bug 3493035
254 IF (nvl(FND_PROFILE.value('JTF_INDIVIDUALUSER_ACCOUNT'),'N') <> 'Y')
255 THEN
256 l_profile := FALSE;
257 END IF;*/
258
259 --check if any a/c has been created for this user
260
261 SELECT COUNT(*) into L_ACCT_CNT
262 FROM HZ_CUST_ACCOUNTS
263 WHERE PARTY_ID =p_party_id
264 AND STATUS = 'A';
265
266
267 END IF;
268
269
270 if l_is_debug_parameter_on then
271 JTF_DEBUG_PUB.LOG_PARAMETERS
272 (p_module => MODULE_NAME,
273 p_message =>'L_ACCT_CNT:'||L_ACCT_CNT
274 );
275 end if;
276
277 IF ( L_ACCT_CNT = 0) /* AND l_profile = TRUE Bug 3493035 */
278 THEN
279
280
281 CREATE_ACCOUNT (
282 P_PARTY_ID => P_PARTY_ID ,
283 P_PARTY_TYPE => l_party_type,
284 P_ORG_PARTY_ID => P_OBJECT_ID
285 );
286
287 END IF;
288
289 END QUERY_ACCOUNT;
290 PROCEDURE REVOKE_RESPONSIBILITY
291 (
292 X_USER_ID NUMBER,
293 X_RESPONSIBILITY_ID NUMBER,
294 X_APPLICATION_ID NUMBER
295 )
296 IS
297 l_def_resp_id NUMBER;
298 l_def_app_id NUMBER;
299 l_def_resp_key FND_RESPONSIBILITY_VL.RESPONSIBILITY_KEY%TYPE;
300 l_def_resp_name FND_RESPONSIBILITY_VL.RESPONSIBILITY_NAME%TYPE;
301
302 BEGIN
303 IF Fnd_User_Resp_Groups_Api.Assignment_Exists(
304 user_id => X_USER_ID,
305 responsibility_id => X_RESPONSIBILITY_ID,
306 responsibility_application_id => X_APPLICATION_ID
307 ) THEN
308
309 /*
310 Removed this direct update call as fnd_user_resp_groups is no
311 longer a table. Converted this call to use an API instead.
312
313 UPDATE FND_USER_RESP_GROUPS SET END_DATE = SYSDATE
314 WHERE USER_ID = X_USER_ID
315 AND RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
316 AND RESPONSIBILITY_APPLICATION_ID = X_APPLICATION_ID;
317 */
318 Fnd_User_Resp_Groups_Api.UPLOAD_ASSIGNMENT(
319 user_id => X_USER_ID,
320 responsibility_id => X_RESPONSIBILITY_ID,
321 responsibility_application_id => X_APPLICATION_ID,
322 start_date => sysdate,
323 end_date => sysdate, -- Revoke the responsibility
324 description => null );
325
326
327 -- We need to reset the profile option, if the revoked responsibility
328 -- is the default login responsibility of a user
329
330 get_default_login_resp(
331 p_user_id => X_USER_ID,
332 x_resp_id => l_def_resp_id,
333 x_app_id => l_def_app_id,
334 x_resp_key => l_def_resp_key,
335 x_resp_name => l_def_resp_name
336 );
337
338 IF l_def_resp_id = X_RESPONSIBILITY_ID AND l_def_app_id = X_APPLICATION_ID THEN
339
340 set_default_login_resp(
341 p_user_id => X_USER_ID,
342 p_resp_id => null,
343 p_app_id => null
344 ) ;
345
346 END IF;
347
348 END IF;
349
350 END REVOKE_RESPONSIBILITY;
351
352 PROCEDURE ASSIGN_RESPONSIBILITY
353 (
354 X_USER_ID NUMBER,
355 X_RESPONSIBILITY_ID NUMBER,
356 X_APPLICATION_ID NUMBER
357 )
358 IS
359 BEGIN
360
361 Fnd_User_Resp_Groups_Api.UPLOAD_ASSIGNMENT(
362 user_id => X_USER_ID,
363 responsibility_id => X_RESPONSIBILITY_ID,
364 responsibility_application_id => X_APPLICATION_ID,
365 start_date => sysdate,
366 end_date => null,
367 description => null );
368
369 END ASSIGN_RESPONSIBILITY;
370
371
372 PROCEDURE ASSIGN_RESPONSIBILITY
373 (
374 X_USER_ID NUMBER,
375 X_RESPONSIBILITY_KEY VARCHAR2,
376 X_APPLICATION_ID NUMBER
377 )
378 IS
379
380 p_responsibility_id NUMBER;
381 CURSOR RESP_KEY IS SELECT RESPONSIBILITY_ID
382 FROM FND_RESPONSIBILITY_VL
383 WHERE RESPONSIBILITY_KEY = X_RESPONSIBILITY_KEY;
384 BEGIN
385
386 OPEN RESP_KEY;
387
388 FETCH RESP_KEY INTO p_responsibility_id;
389
390 CLOSE RESP_KEY;
391
392 IF NVL(p_responsibility_id,0) <> 0 THEN
393
394 ASSIGN_RESPONSIBILITY
395 (
396 X_USER_ID => X_USER_ID,
397 X_RESPONSIBILITY_ID => p_responsibility_id,
398 X_APPLICATION_ID => X_APPLICATION_ID );
399 END IF;
400
401 END ASSIGN_RESPONSIBILITY;
402
403 PROCEDURE ASSIGN_DEFAULT_RESPONSIBILITY
404 (
405 X_USER_ID NUMBER,
406 X_RESPONSIBILITY_KEY VARCHAR2,
407 X_APPLICATION_ID NUMBER
408 )
409 IS
410 p_responsibility_id NUMBER;
411 p_assign_def_resp boolean;
412 CURSOR RESP_KEY IS SELECT RESPONSIBILITY_ID
413 FROM FND_RESPONSIBILITY_VL
414 WHERE RESPONSIBILITY_KEY = X_RESPONSIBILITY_KEY;
415
416 BEGIN
417
418 OPEN RESP_KEY;
419
420 FETCH RESP_KEY INTO p_responsibility_id;
421
422 CLOSE RESP_KEY;
423
424 IF NVL(p_responsibility_id,0) <> 0 THEN
425
426 ASSIGN_RESPONSIBILITY
427 (
428 X_USER_ID => X_USER_ID,
429 X_RESPONSIBILITY_ID => p_responsibility_id,
430 X_APPLICATION_ID => X_APPLICATION_ID
431 );
432
433 -- Make assigned responsibility as default one
434
435 set_default_login_resp(
436 p_user_id => X_USER_ID,
437 p_resp_id => p_responsibility_id,
438 p_app_id => X_APPLICATION_ID
439 ) ;
440 /* This has been replaced by a method above
441 p_assign_def_resp := fnd_profile.save
442 (X_NAME => 'JTF_PROFILE_DEFAULT_RESPONSIBILITY',
443 X_VALUE => p_responsibility_id,
444 X_LEVEL_NAME => 'USER',
445 X_LEVEL_VALUE => X_USER_ID);
446 */
447 END IF;
448
449 END ASSIGN_DEFAULT_RESPONSIBILITY;
450
451
452
453
454 PROCEDURE REVOKE_RESPONSIBILITY
455 (
456 X_USER_ID NUMBER,
457 X_RESPONSIBILITY_KEY VARCHAR2,
458 X_APPLICATION_ID NUMBER
459 )
460 IS
461
462 p_responsibility_id NUMBER;
463 CURSOR RESP_KEY_ID IS SELECT RESPONSIBILITY_ID
464 FROM FND_RESPONSIBILITY_VL
465 WHERE RESPONSIBILITY_KEY = X_RESPONSIBILITY_KEY;
466 BEGIN
467
468 OPEN RESP_KEY_ID;
469
470 FETCH RESP_KEY_ID INTO p_responsibility_id;
471
472 CLOSE RESP_KEY_ID;
473
474 IF NVL(p_responsibility_id,0) <> 0 THEN
475
476 REVOKE_RESPONSIBILITY
477 (
478 X_USER_ID => X_USER_ID,
479 X_RESPONSIBILITY_ID => p_responsibility_id,
480 X_APPLICATION_ID => X_APPLICATION_ID
481 );
482 END IF;
483
484 END REVOKE_RESPONSIBILITY;
485
486 --added for bug# 7661549
487 /****************************************************************
488 * Procedure Name :
489 * setApproverNotifPref
490 * Description :
491 * This procedure is called after enabling the user in ASSIGN_USERTYPE_CREDENTIALS
492 * This procedure sets the notification preference in the wf_local_roles for the given user to the value in the corresponding adHocRole.
493 * Parameters
494 * userName : The User Name
495 * userId : The User ID
496 *
497 ************************************************************/
498 procedure setApproverNotifPref(userName varchar2, userId varchar2) is
499 l_notif_pref wf_local_roles.NOTIFICATION_PREFERENCE%type;
500 l_user_name wf_local_roles.name%type;
501 l_user_display_name wf_local_roles.display_name%type;
502 errCode varchar2(100);
503 errMsg varchar2(200);
504 begin
505 wf_directory.GetUserName(p_orig_system => 'FND_USR',
506 p_orig_system_id => userId,
507 p_name => l_user_name,
508 p_display_name => l_user_display_name);
509 if(l_user_name is not null) then
510 select NOTIFICATION_PREFERENCE into l_notif_pref
511 from jtf_um_usertype_reg ut,
512 wf_local_roles wlr
513 where ut.status_code='PENDING'
514 and '__JTA_UM'||USERTYPE_REG_ID = name
515 and ut.user_id = userId;
516
517 fnd_preference.put(p_user_name => userName, p_module_name => 'WF', p_pref_name => 'MAILTYPE', p_pref_value => l_notif_pref);
518 wf_directory.SetUserAttr(user_name => userName,
519 orig_system => 'FND_USR',
520 orig_system_id => userId,
521 notification_preference => nvl(l_notif_pref,'QUERY'));
522 end if;
523 exception
524 when others then
525 begin
526 errCode := sqlcode;
527 errMsg := sqlerrm;
528 jtf_debug_pub.log_exception(p_module => MODULE_NAME,p_message => ' Error in setApproverNotifPref code:'||errCode||', msg: '||errMsg);
529 exception
530 when others then
531 null;
532 end;
533 end;
534
535 /****************************************************************
536 * Procedure Name :
537 * ASSIGN_USERTYPE_CREDENTIALS
538 * Description :
539 * This procedure is called upon manual/auto approval of an User
540 * Parameters
541 * X_USER_NAME : The User Name
542 * X_USER_ID : The User ID
543 * X_USERTYPE_ID: The Usertype to which the User belongs
544 *
545 ************************************************************/
546
547 PROCEDURE ASSIGN_USERTYPE_CREDENTIALS
548 (
549 X_USER_NAME VARCHAR2,
550 X_USER_ID NUMBER,
551 X_USERTYPE_ID NUMBER
552 )
553 IS
554
555 l_procedure_name CONSTANT varchar2(30) := 'ASSIGN_USERTYPE_CREDENTIALS';
556 p_usertype_resp_id number;
557 p_usertype_app_id NUMBER;
558 p_principal_name VARCHAR2(255);
559 l_version FND_RESPONSIBILITY_VL.VERSION%TYPE;
560 l_resp_id number;
561 l_app_id number;
562 l_resp_key FND_RESPONSIBILITY_VL.RESPONSIBILITY_KEY%TYPE;
563 l_resp_name FND_RESPONSIBILITY_VL.RESPONSIBILITY_NAME%TYPE;
564
565 userStartDate DATE;
566 userEndDate DATE;
567
568 -- added variables for 4276972
569 eventName constant varchar2(33):='oracle.apps.jtf.um.approveUTEvent';
570 l_usertype_reg_id number;
571 l_usertype_key varchar2(30);
572 l_parameter_list wf_parameter_list_t :=
573 wf_parameter_list_t();
574
575 --added variables for 4287135
576 isDefaultRespPresent boolean :=false;
577
578 CURSOR USERTYPE_RESP is select FR.RESPONSIBILITY_ID, UT.APPLICATION_ID, FR.VERSION
579 FROM JTF_UM_USERTYPE_RESP UT, FND_RESPONSIBILITY_VL FR
580 WHERE UT.USERTYPE_ID = X_USERTYPE_ID
581 AND FR.APPLICATION_ID = UT.APPLICATION_ID
582 AND FR.RESPONSIBILITY_KEY = UT.RESPONSIBILITY_KEY
583 AND (UT.EFFECTIVE_END_DATE IS NULL OR UT.EFFECTIVE_END_DATE > SYSDATE)
584 AND UT.EFFECTIVE_START_DATE < SYSDATE;
585
586 CURSOR USERTYPE_ROLES IS SELECT PRINCIPAL_NAME
587 FROM JTF_UM_USERTYPE_ROLE
588 WHERE USERTYPE_ID = X_USERTYPE_ID
589 AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)
590 AND EFFECTIVE_START_DATE < SYSDATE;
591
592
593 BEGIN
594
595 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
596 p_message => l_procedure_name
597 );
598
599 if l_is_debug_parameter_on then
600 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
601 p_message => 'X_USER_NAME:' || X_USER_NAME || '+' || 'X_USER_ID:' || X_USER_ID || '+' || 'X_USERTYPE_ID:' || X_USERTYPE_ID
602 );
603 end if;
604
605
606 --changes for 3899304
607 -- the user name which has been reserved has to be enabled.
608 -- enable user only if the user is a pending user.
609
610 select start_date,end_date into userStartDate,userEndDate from FND_USER
611 where user_id = X_USER_ID;
612
613 if to_char(userStartDate) = to_char(FND_API.G_MISS_DATE) and to_char(userEndDate) = to_char(FND_API.G_MISS_DATE) then
614 fnd_user_pkg.EnableUser (
615 username => X_USER_NAME,
616 start_date => sysdate,
617 end_date => fnd_user_pkg.null_date);
618 setApproverNotifPref(X_USER_NAME, X_USER_ID); --added for bug# 7661549
619 --Added for bug 7661549
620 /*wf_directory.SetUserAttr(user_name => X_USER_NAME,
621 orig_system => 'FND_USR',
622 orig_system_id => X_USER_ID,
623 notification_preference => nvl(fnd_profile.value_specific('JTA_UM_MAIL_PREFERENCE'),'QUERY'));*/
624
625 end if;
626 --end of changes for 3899304
627 -- removing JTF_PENDING_REMOVAL resp assigned during registration process
628 -- as we now have a check to assign default responsibility only if there
629 -- are no previous default resp
630
631
632 JTF_UM_USERTYPE_CREDENTIALS.REVOKE_RESPONSIBILITY
633 ( X_USER_ID => X_USER_ID,
634 X_RESPONSIBILITY_KEY => 'JTF_PENDING_APPROVAL',
635 X_APPLICATION_ID => 690
636 );
637 -- Assign Responsibilites based on user type
638
639 /*check if any default responsibility is already present.
640 this check added for re-registration process of 4287135
641 in which case the user already has a default responsibility
642 and that should not change.*/
643
644
645 JTF_UM_USERTYPE_CREDENTIALS.get_default_login_resp(
646 p_user_id => X_USER_ID,
647 x_resp_id => l_resp_id,
648 x_app_id => l_app_id,
649 x_resp_key => l_resp_key,
650 x_resp_name => l_resp_name
651 );
652
653
654
655 IF l_resp_id IS NOT NULL AND l_app_id IS NOT NULL THEN
656 isDefaultRespPresent:=true;
657 END IF;
658
659
660 OPEN USERTYPE_RESP;
661 LOOP
662 FETCH USERTYPE_RESP INTO p_usertype_resp_id, p_usertype_app_id,l_version;
663 EXIT WHEN USERTYPE_RESP%NOTFOUND;
664
665 ASSIGN_RESPONSIBILITY
666 (
667 X_USER_ID => X_USER_ID,
668 X_RESPONSIBILITY_ID => p_usertype_resp_id,
669 X_APPLICATION_ID => p_usertype_app_id
670 );
671
672 -- Make this responsibility a default one if it is web based
673 IF NOT isDefaultRespPresent THEN
674 IF l_version = 'W' THEN
675
676 set_default_login_resp(
677 p_user_id => X_USER_ID,
678 p_resp_id => p_usertype_resp_id,
679 p_app_id => p_usertype_app_id
680 );
681 isDefaultRespPresent :=true;
682 END IF;
683 END IF;
684
685 END LOOP;
686 CLOSE USERTYPE_RESP;
687
688 -- Assign Roles based on user type
689
690 OPEN USERTYPE_ROLES;
691
692 LOOP
693 FETCH USERTYPE_ROLES INTO p_principal_name;
694 EXIT WHEN USERTYPE_ROLES%NOTFOUND;
695
696
697 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
698 ( USER_NAME => X_USER_NAME,
699 ROLE_NAME => p_principal_name,
700 OWNERTABLE_NAME => 'JTF_UM_USERTYPES_B',
701 OWNERTABLE_KEY => X_USERTYPE_ID
702 );
703
704
705 END LOOP;
706 CLOSE USERTYPE_ROLES;
707
708 -- Update the status
709
710 UPDATE JTF_UM_USERTYPE_REG SET STATUS_CODE='APPROVED'
711 WHERE USERTYPE_ID = X_USERTYPE_ID
712 AND USER_ID = X_USER_ID
713 and status_code='PENDING' and nvl(effective_end_date, SYSDATE + 1) > SYSDATE;
714
715 /* Bug fix: 3549056
716 Revoke Pending approval responsibility
717 No need to check whether usertype has responsibility associated to it or not
718
719
720
721 get_usertype_resp(
722 p_usertype_id => X_USERTYPE_ID,
723 p_resp_id => l_resp_id,
724 p_app_id => l_app_id
725 );
726
727 IF l_resp_id IS NOT NULL AND l_app_id IS NOT NULL THEN
728 */
729
730 -- END IF; Bug fix: 3549056
731
732
733
734
735
736
737 -- Bug Fix: 3493035
738 IF (nvl(FND_PROFILE.value('JTA_UM_AUTO_ACCT_CREATION'),'Y') = 'Y') THEN
739
740 QUERY_ACCOUNT (
741 X_USER_ID => X_USER_ID
742 );
743 END IF;
744
745 -- raise event for Approval : 4276972
746 -- Get the values for creation of parameters for the event
747
748 JTF_DEBUG_PUB.LOG_DEBUG (2, MODULE_NAME, 'Start Raising Event');
749
750 Select ut.APPLICATION_ID,ut.USERTYPE_KEY,reg.USERTYPE_REG_ID
751 Into l_app_id, l_usertype_key, l_usertype_reg_id
752 From JTF_UM_USERTYPES_B ut , JTF_UM_USERTYPE_REG reg
753 where ut.USERTYPE_ID=reg.USERTYPE_ID and reg.USER_ID=X_USER_ID
754 and reg.status_code='APPROVED' and
755 nvl(reg.EFFECTIVE_END_DATE,sysdate+1) > sysdate;
756
757 JTF_DEBUG_PUB.LOG_DEBUG (2, MODULE_NAME, 'Parameters '|| l_app_id ||' '||l_usertype_key|| ' '||l_usertype_reg_id );
758
759 -- create the parameter list
760 wf_event.AddParameterToList(
761 p_name => 'USERTYPEREG_ID',
762 p_value=>to_char(l_usertype_reg_id),
763 p_parameterlist=>l_parameter_list
764 );
765 wf_event.AddParameterToList(
766 p_name => 'APPID',
767 p_value=>to_char(l_app_id),
768 p_parameterlist=>l_parameter_list
769 );
770 wf_event.AddParameterToList(
771 p_name => 'USER_TYPE_KEY',
772 p_value=>l_usertype_key,
773 p_parameterlist=>l_parameter_list
774 );
775
776 -- raise the event
777 wf_event.raise(
778 p_event_name =>eventName,
779 p_event_key =>X_USER_ID ,
780 p_parameters => l_parameter_list
781 );
782
783 -- delete parameter list as it is no longer required
784 l_parameter_list.DELETE;
785
786 -- end of event handling
787
788
789 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
790 p_message => l_procedure_name
791 );
792
793
794 END ASSIGN_USERTYPE_CREDENTIALS;
795
796 PROCEDURE ASSIGN_ACCOUNT
797 (
798 P_PARTY_ID NUMBER,
799 P_USERTYPE_KEY VARCHAR2,
800 P_ORG_PARTY_ID NUMBER:=FND_API.G_MISS_NUM
801 )
802 IS
803
804 CURSOR FIND_APPROVAL IS SELECT APPROVAL_ID FROM JTF_UM_USERTYPES_B
805 WHERE USERTYPE_KEY = P_USERTYPE_KEY
806 AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE);
807
808 l_party_type HZ_PARTIES.party_type%type;
809
810 p_approval_id NUMBER := -1;
811 p_account_number NUMBER;
812 x_return_status VARCHAR2(100);
813 x_msg_count NUMBER;
814 x_msg_data VARCHAR2(1000);
815 x_cust_account_id NUMBER;
816 x_cust_account_number VARCHAR2(30);
817 x_party_id NUMBER;
818 x_party_number VARCHAR2(30);
819 x_profile_id NUMBER;
820 x_cust_account_role_id NUMBER;
821 cust_acct_roles_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.CUST_ACCOUNT_ROLE_REC_TYPE;
822
823 l_procedure_name varchar2(30) := 'ASSIGN_ACCOUNT';
824 BEGIN
825 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
826 p_message => l_procedure_name);
827
828
829
830
831 OPEN FIND_APPROVAL;
832 FETCH FIND_APPROVAL INTO p_approval_id;
833 CLOSE FIND_APPROVAL;
834
835 l_party_type := JTF_UM_UTIL_PVT.check_party_type(P_PARTY_ID);
836
837 if l_is_debug_parameter_on then
838 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
839 p_message =>'l_party_type:' || l_party_type
840 );
841 end if;
842
843 SELECT hz_account_num_s.nextval into p_account_number FROM DUAL;
844 if(p_approval_id is null or p_approval_id = -1 OR l_party_type='PARTY_RELATIONSHIP')
845 then
846
847 --if P_USERTYPE_KEY = 'INDIVIDUALUSER' then
848 if l_party_type = 'PERSON' then
849
850 -- create new account for Individual User
851
852 jtf_customer_accounts_pvt.create_account(
853 p_api_version => 1,
854 p_init_msg_list => 'T',
855 p_commit => 'F',
856 p_party_id => P_PARTY_ID,
857 p_account_number => p_account_number,
858 p_create_amt => 'F',
859 p_party_type => 'P',
860 x_return_status => x_return_status,
861 x_msg_count => x_msg_count,
862 x_msg_data => x_msg_data,
863 x_cust_account_id => x_cust_account_id,
864 x_cust_account_number => x_cust_account_number,
865 x_party_id => x_party_id,
866 x_party_number => x_party_number,
867 x_profile_id => x_profile_id
868 );
869
870 else
871
872 -- create new account for Primary User or businessuser who was not assigned
873 -- one when he was approved.
874
875 jtf_customer_accounts_pvt.create_account(
876 p_api_version => 1,
877 p_init_msg_list => 'T',
878 p_commit => 'F',
879 p_party_id => P_ORG_PARTY_ID,
880 p_account_number => p_account_number,
881 p_create_amt => 'F',
882 p_party_type => 'O',
883 x_return_status => x_return_status,
884 x_msg_count => x_msg_count,
885 x_msg_data => x_msg_data,
886 x_cust_account_id => x_cust_account_id,
887 x_cust_account_number => x_cust_account_number,
888 x_party_id => x_party_id,
889 x_party_number => x_party_number,
890 x_profile_id => x_profile_id
891 );
892 if l_is_debug_parameter_on then
893 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
894 p_message =>'X_return_status@create_account:' ||x_return_status
895 );
896 end if;
897
898 end if;
899 if(x_return_status <> fnd_api.g_ret_sts_success)
900 then
901 jtf_debug_pub.log_exception(p_module => MODULE_NAME
902 ,p_message => ' message stack in account creation:'||x_msg_data);
903 raise_application_error(-20101,'Failed to create account:'||x_msg_data);
904
905 end if;
906 -- Creating record in hz_cust_account_roles
907
908 if x_cust_account_id is not null then
909
910 cust_acct_roles_rec.cust_account_id := x_cust_account_id;
911 cust_acct_roles_rec.party_id := P_PARTY_ID;
912 cust_acct_roles_rec.role_type := 'CONTACT';
913 cust_acct_roles_rec.status := 'A';
914 cust_acct_roles_rec.created_by_module := 'JTA_USER_MANAGEMENT';
915 hz_cust_account_role_v2pub.create_cust_account_role(
916 p_init_msg_list =>'T',
917 p_cust_account_role_rec => cust_acct_roles_rec,
918 x_cust_account_role_id => x_cust_account_role_id,
919 x_return_status => x_return_status,
920 x_msg_count => x_msg_count,
921 x_msg_data => x_msg_data
922 );
923
924 if(x_return_status <> fnd_api.g_ret_sts_success)
925 then
926 raise_application_error(-20101,'Failed to create cust account role:'||x_msg_data);
927 end if;
928 end if;
929
930 end if;
931 END ASSIGN_ACCOUNT;
932
933 PROCEDURE REJECT_DELETED_PEND_USER (P_USERNAME in VARCHAR2,
934 X_PENDING_USER out NOCOPY VARCHAR2)
935
936 IS
937
938 CURSOR FIND_UT_APPWF_INFO IS
939 SELECT reg.WF_ITEM_TYPE, to_char (reg.USERTYPE_REG_ID)
940 FROM JTF_UM_USERTYPE_REG reg, FND_USER fu
941 WHERE fu.USER_NAME = P_USERNAME
942 AND fu.USER_ID = reg.USER_ID
943 AND STATUS_CODE = 'PENDING'
944 AND (reg.EFFECTIVE_END_DATE is null
945 OR reg.EFFECTIVE_END_DATE > sysdate);
946
947 itemtype varchar2 (8);
948 itemkey varchar2 (240);
949
950 BEGIN
951
952 OPEN FIND_UT_APPWF_INFO;
953 FETCH FIND_UT_APPWF_INFO INTO itemtype, itemkey;
954 IF (FIND_UT_APPWF_INFO%NOTFOUND) THEN
955 X_PENDING_USER := 'N';
956 CLOSE FIND_UT_APPWF_INFO;
957 RETURN;
958 END IF;
959 CLOSE FIND_UT_APPWF_INFO;
960
961 JTF_UM_WF_APPROVAL.COMPLETEAPPROVALACTIVITY (itemtype, itemkey, 'REJECTED', 'User deleted');
962
963 -- Need to Cancel Notification
964 JTF_UM_WF_APPROVAL.CANCEL_NOTIFICATION (itemtype, itemkey);
965 X_PENDING_USER := 'Y';
966
967 END REJECT_DELETED_PEND_USER;
968
969 PROCEDURE ASSIGN_DEF_RESP(P_USERNAME in VARCHAR2,
970 P_ACCOUNT_TYPE in VARCHAR2)
971 IS
972
973 l_usertype_id NUMBER;
974 l_usertype_key VARCHAR2(30);
975 l_responsibility_key VARCHAR2(30);
976 l_application_id NUMBER;
977 l_user_id NUMBER;
978
979 cursor find_default_resp is select responsibility_key, application_id from
980 jtf_um_usertype_resp where usertype_id = l_usertype_id
981 and (effective_end_date is null or effective_end_date > sysdate) ;
982
983 cursor find_usertype is select usertype_id from jtf_um_usertypes_b
984 where usertype_key = l_usertype_key
985 and (effective_end_date is null or effective_end_date > sysdate) ;
986
987 cursor find_user_id is select user_id from fnd_user where user_name = P_USERNAME;
988
989 BEGIN
990
991 -- Map account type to user type
992
993 IF UPPER(P_ACCOUNT_TYPE) = 'BUSINESSUSER' THEN
994 l_usertype_key := 'BUSINESSUSER';
995
996 ELSE
997 l_usertype_key := 'INDIVIDUALUSER';
998
999 END IF;
1000
1001 -- Find out user type id
1002
1003 open find_usertype;
1004 fetch find_usertype into l_usertype_id;
1005 close find_usertype;
1006
1007 -- Find out default responsibility
1008
1009 open find_default_resp;
1010 fetch find_default_resp into l_responsibility_key, l_application_id;
1011 close find_default_resp;
1012
1013 -- Assign default responsibility
1014 if l_responsibility_key is not null and l_application_id is not null then
1015
1016 open find_user_id;
1017 fetch find_user_id into l_user_id;
1018 close find_user_id;
1019
1020 -- Make sure that user_id is not null
1021 if l_user_id is not null then
1022 ASSIGN_DEFAULT_RESPONSIBILITY
1023 (
1024 X_USER_ID => l_user_id,
1025 X_RESPONSIBILITY_KEY => l_responsibility_key,
1026 X_APPLICATION_ID => l_application_id
1027 );
1028 end if;
1029 end if;
1030
1031 END ASSIGN_DEF_RESP;
1032
1033 PROCEDURE ASSIGN_DEF_ROLES(P_USERNAME in VARCHAR2,
1034 P_ACCOUNT_TYPE in VARCHAR2)
1035 IS
1036
1037 l_usertype_id NUMBER;
1038 l_usertype_key VARCHAR2(30);
1039 l_principal_name VARCHAR2(255);
1040
1041 cursor find_default_role is select principal_name from
1042 jtf_um_usertype_role where usertype_id = l_usertype_id
1043 and (effective_end_date is null or effective_end_date > sysdate) ;
1044
1045 cursor find_usertype is select usertype_id from jtf_um_usertypes_b
1046 where usertype_key = l_usertype_key
1047 and (effective_end_date is null or effective_end_date > sysdate) ;
1048
1049 BEGIN
1050
1051 -- Map account type to user type
1052
1053 IF UPPER(P_ACCOUNT_TYPE) = 'BUSINESSUSER' THEN
1054 l_usertype_key := 'BUSINESSUSER';
1055
1056 ELSE
1057 l_usertype_key := 'INDIVIDUALUSER';
1058
1059 END IF;
1060
1061 -- Find out user type id
1062
1063 open find_usertype;
1064 fetch find_usertype into l_usertype_id;
1065 close find_usertype;
1066
1067 -- Find out default roles
1068
1069 open find_default_role;
1070
1071 loop
1072 fetch find_default_role into l_principal_name;
1073 exit when find_default_role%NOTFOUND;
1074
1075 -- Assign default roles
1076 if l_principal_name is not null then
1077
1078 -- Make sure that user name is not null
1079 if P_USERNAME is not null then
1080 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
1081 ( USER_NAME => P_USERNAME,
1082 ROLE_NAME => l_principal_name,
1083 OWNERTABLE_NAME => 'JTF_UM_USERTYPES_B',
1084 OWNERTABLE_KEY => l_usertype_id
1085 );
1086 end if;
1087 end if;
1088 end loop;
1089 close find_default_role;
1090
1091 END ASSIGN_DEF_ROLES;
1092
1093
1094 /**
1095 * Procedure : get_usertype_resp
1096 * Type : Private
1097 * Pre_reqs : None
1098 * Description : Will determine the responsibility attached to the usertype
1099 * Parameters :
1100 * input parameters
1101 * @param p_usertype_id
1102 * description: The usertyp_id
1103 * required : Y
1104 * validation : Must be a valid usertype_id
1105 * output parameters
1106 * x_resp_id
1107 * description: The responsibility_id associated to the responsibility
1108 * associated to the usertype
1109 * x_app_id
1110 * description: The app_id associated to the responsibility
1111 * associated to the usertype
1112 **/
1113 procedure get_usertype_resp(
1114 p_usertype_id in number,
1115 p_resp_id out NOCOPY number,
1116 p_app_id out NOCOPY number
1117 ) IS
1118
1119 l_procedure_name CONSTANT varchar2(30) := 'get_usertype_resp';
1120 CURSOR FIND_UT_RESP IS SELECT UTRESP.APPLICATION_ID, FURESP.RESPONSIBILITY_ID
1121 FROM JTF_UM_USERTYPE_RESP UTRESP, FND_RESPONSIBILITY_VL FURESP
1122 WHERE UTRESP.USERTYPE_ID = p_usertype_id
1123 AND NVL(UTRESP.EFFECTIVE_END_DATE,SYSDATE+1) > SYSDATE
1124 AND UTRESP.EFFECTIVE_START_DATE < SYSDATE
1125 AND UTRESP.RESPONSIBILITY_KEY = FURESP.RESPONSIBILITY_KEY;
1126
1127 BEGIN
1128
1129 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1130 p_message => l_procedure_name
1131 );
1132 if l_is_debug_parameter_on then
1133 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1134 p_message => 'p_usertype_id:' || p_usertype_id
1135 );
1136 end if;
1137
1138 OPEN FIND_UT_RESP;
1139 FETCH FIND_UT_RESP INTO p_app_id, p_resp_id;
1140 CLOSE FIND_UT_RESP;
1141
1142 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1143 p_message => l_procedure_name
1144 );
1145
1146 END get_usertype_resp;
1147
1148
1149 /**
1150 * Procedure : grant_roles
1151 * Type : Private
1152 * Pre_reqs : None
1153 * Description : Will grant roles to users
1154 * Parameters :
1155 * input parameters
1156 * p_user_name:
1157 * description: The user_name of the user
1158 * required : Y
1159 * validation : Must be a valid user_name
1160 * p_role_id
1161 * description: The value of the JTF_AUTH_PRINCIPAL_ID
1162 * required : Y
1163 * validation : Must exist as a JTF_AUTH_PRONCIPAL_ID
1164 * in the table JTF_AUTH_PRINCIPALS_B
1165 * p_source_name
1166 * description: The value of the name of the source
1167 * required : Y
1168 * validation : Must be "USERTYPE" or "ENROLLMENT"
1169 * p_source_id
1170 * description: The value of the id associated with the source
1171 * required : Y
1172 * validation : Must be a usertype_id or a subscription_id
1173 * output parameters
1174 * None
1175 */
1176 procedure grant_roles (
1177 p_user_name in varchar2,
1178 p_role_id in number,
1179 p_source_name in varchar2,
1180 p_source_id in varchar2
1181 ) IS
1182
1183 CURSOR FIND_ROLE_NAME IS SELECT PRINCIPAL_NAME FROM JTF_AUTH_PRINCIPALS_B
1184 WHERE JTF_AUTH_PRINCIPAL_ID = p_role_id AND IS_USER_FLAG = 0;
1185
1186 l_owner_table_name varchar2(50);
1187 l_principal_name JTF_AUTH_PRINCIPALS_B.PRINCIPAL_NAME%TYPE;
1188
1189 BEGIN
1190
1191 IF p_source_name <> 'USERTYPE' OR p_source_name <> 'ENROLLMENT' THEN
1192 RAISE_APPLICATION_ERROR(-20000,'The source name is incorrect');
1193 END IF;
1194
1195 OPEN FIND_ROLE_NAME;
1196 FETCH FIND_ROLE_NAME INTO l_principal_name;
1197
1198 IF FIND_ROLE_NAME%NOTFOUND THEN
1199 CLOSE FIND_ROLE_NAME;
1200 RAISE_APPLICATION_ERROR(-20000,'The role id is incorrect');
1201 END IF;
1202
1203 CLOSE FIND_ROLE_NAME;
1204
1205 IF p_source_name = 'USERTYPE' THEN
1206
1207 l_owner_table_name := 'JTF_UM_USERTYPES_B';
1208
1209 ELSIF p_source_name = 'ENROLLMENT' THEN
1210
1211 l_owner_table_name := 'ENROLLMENT';
1212
1213 END IF;
1214
1215
1216 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
1217 ( USER_NAME => p_user_name,
1218 ROLE_NAME => l_principal_name,
1219 OWNERTABLE_NAME => l_owner_table_name,
1220 OWNERTABLE_KEY => p_source_id
1221 );
1222
1223 END grant_roles;
1224
1225 /**
1226 * Procedure : set_default_login_responsibility
1227 * Type : Private
1228 * Pre_reqs : None
1229 * Description : Will set the default responsibility of a user
1230 * Parameters :
1231 * input parameters
1232 * @param p_user_id
1233 * description: The user_id of a user
1234 * validation : Must be a valid user_id
1235 * @param p_resp_id
1236 * description: The responsibility_id associated to the default logon
1237 * responsibility of a user
1238 * required : Y
1239 * validation : Must be a valid responsibility_id
1240 * @param p_app_id
1241 * description: The app_id associated to the default logon
1242 * responsibility of a user
1243 * required : Y
1244 * validation: Must be a valid application_id
1245 * output parameters
1246 * None
1247 **/
1248
1249 procedure set_default_login_resp(
1250 p_user_id in number,
1251 p_resp_id in number,
1252 p_app_id in number
1253 ) IS
1254 l_procedure_name CONSTANT varchar2(30) := 'set_default_login_resp';
1255 p_assign_def_resp boolean;
1256 p_assign_def_appl boolean;
1257
1258 BEGIN
1259
1260 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1261 p_message => l_procedure_name
1262 );
1263 if l_is_debug_parameter_on then
1264 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1265 p_message => 'p_user_id:' || p_user_id || '+' || 'p_resp_id:' || p_resp_id || '+' || 'p_app_id:' || p_app_id
1266 );
1267 end if;
1268
1269 p_assign_def_resp := fnd_profile.save
1270 (X_NAME => 'JTF_PROFILE_DEFAULT_RESPONSIBILITY',
1271 X_VALUE => p_resp_id,
1272 X_LEVEL_NAME => 'USER',
1273 X_LEVEL_VALUE => p_user_id);
1274
1275 p_assign_def_appl := fnd_profile.save
1276 (X_NAME => 'JTF_PROFILE_DEFAULT_APPLICATION',
1277 X_VALUE => p_app_id,
1278 X_LEVEL_NAME => 'USER',
1279 X_LEVEL_VALUE => p_user_id);
1280
1281 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1282 p_message => l_procedure_name
1283 );
1284 END set_default_login_resp;
1285
1286 /**
1287 * Procedure : set_default_login_resp
1288 * Type : Private
1289 * Pre_reqs : None
1290 * Description : Will set the default responsibility of a user
1291 * Parameters :
1292 * input parameters
1293 * @param p_user_name
1294 * description: The user_name of a user
1295 * validation : Must be a valid user_name
1296 * @param p_resp_id
1297 * description: The responsibility_id associated to the default logon
1298 * responsibility of a user
1299 * required : Y
1300 * validation : Must be a valid responsibility_id
1301 * @param p_app_id
1302 * description: The app_id associated to the default logon
1303 * responsibility of a user
1304 * required : Y
1305
1306 * output parameters
1307 * None
1308 **/
1309
1310 procedure set_default_login_resp(
1311 p_user_name in varchar2,
1312 p_resp_id in number,
1313 p_app_id in number
1314 ) IS
1315
1316 l_procedure_name CONSTANT varchar2(30) := 'set_default_login_resp';
1317
1318 BEGIN
1319
1320 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1321 p_message => l_procedure_name
1322 );
1323 if l_is_debug_parameter_on then
1324 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1325 p_message => 'p_user_name:' || p_user_name || '+' || 'p_resp_id:' || p_resp_id || '+' || 'p_app_id:' || p_app_id
1326 );
1327 end if;
1328
1329
1330 set_default_login_resp(
1331 p_user_id => JTF_UM_UTIL_PVT.get_user_id(p_user_name),
1332 p_resp_id => p_resp_id,
1333 p_app_id => p_app_id
1334 );
1335
1336
1337 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1338 p_message => l_procedure_name
1339 );
1340
1341 END set_default_login_resp;
1342
1343
1344 /**
1345 * Procedure : get_default_login_resp
1346 * Type : Private
1347 * Pre_reqs : None
1348 * Description : Will set the default responsibility of a user
1349 * Parameters :
1350 * input parameters
1351 * @param p_user_name
1352 * description: The user_name of a user
1353 * validation : Must be a valid user_name
1354 * output parameters
1355 * @param x_resp_id
1356 * description: The responsibility_id associated to the default logon
1357 * responsibility of a user
1358 * @param x_app_id
1359 * description: The app_id associated to the default logon
1360 * responsibility of a user
1361 * @param x_resp_key
1362 * description: The responsibility_key associated to the default logon
1363 * responsibility of a user
1364 * @param x_resp_name
1365 * description: The responsibility_name associated to the default logon
1366 * responsibility of a user
1367 *
1368 * None
1369 **/
1370
1371 procedure get_default_login_resp(
1372 p_user_name in varchar2,
1373 x_resp_id out NOCOPY number,
1374 x_app_id out NOCOPY number,
1375 x_resp_key out NOCOPY varchar2,
1376 x_resp_name out NOCOPY varchar2
1377 ) IS
1378
1379 l_procedure_name CONSTANT varchar2(30) := 'get_default_login_resp';
1380
1381
1382 BEGIN
1383
1384 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1385 p_message => l_procedure_name
1386 );
1387 if l_is_debug_parameter_on then
1388 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1389 p_message => 'p_user_name:' || p_user_name
1390 );
1391 end if;
1392
1393
1394 get_default_login_resp(
1395 p_user_id => JTF_UM_UTIL_PVT.get_user_id(p_user_name),
1396 x_resp_id => x_resp_id,
1397 x_app_id => x_app_id ,
1398 x_resp_key => x_resp_key ,
1399 x_resp_name => x_resp_name
1400 );
1401
1402
1403 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1404 p_message => l_procedure_name
1405 );
1406
1407 END get_default_login_resp;
1408
1409
1410 /**
1411 * Procedure : get_default_login_resp
1412 * Type : Private
1413 * Pre_reqs : None
1414 * Description : Will set the default responsibility of a user
1415 * Parameters :
1416 * input parameters
1417 * @param p_user_id
1418 * description: The user_name of a user
1419 * validation : Must be a valid user_id
1420 * output parameters
1421 * @param x_resp_id
1422 * description: The responsibility_id associated to the default logon
1423 * responsibility of a user
1424 * @param x_app_id
1425 * description: The app_id associated to the default logon
1426 * responsibility of a user
1427 * @param x_resp_key
1428 * description: The responsibility_key associated to the default logon
1429 * responsibility of a user
1430 * @param x_resp_name
1431 * description: The responsibility_name associated to the default logon
1432 * responsibility of a user
1433 *
1434 * None
1435 **/
1436
1437 procedure get_default_login_resp(
1438 p_user_id in number,
1439 x_resp_id out NOCOPY number,
1440 x_app_id out NOCOPY number,
1441 x_resp_key out NOCOPY varchar2,
1442 x_resp_name out NOCOPY varchar2
1443 ) IS
1444
1445 l_procedure_name CONSTANT varchar2(30) := 'get_default_login_resp';
1446 l_app_id_defined boolean;
1447 l_resp_id_defined boolean;
1448 CURSOR FIND_RESP_INFO IS SELECT RESPONSIBILITY_KEY, RESPONSIBILITY_NAME
1449 FROM FND_RESPONSIBILITY_VL
1450 WHERE RESPONSIBILITY_ID = x_resp_id
1451 AND APPLICATION_ID = x_app_id;
1452
1453 BEGIN
1454
1455 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1456 p_message => l_procedure_name
1457 );
1458 if l_is_debug_parameter_on then
1459 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1460 p_message => 'p_user_id:' || p_user_id
1461 );
1462 end if;
1463
1464 JTF_UM_UTIL_PVT.GET_SPECIFIC(
1465 name_z => 'JTF_PROFILE_DEFAULT_APPLICATION',
1466 user_id_z => p_user_id,
1467 val_z => x_app_id,
1468 defined_z => l_app_id_defined
1469 );
1470
1471 JTF_UM_UTIL_PVT.GET_SPECIFIC(
1472 name_z => 'JTF_PROFILE_DEFAULT_RESPONSIBILITY',
1473 user_id_z => p_user_id,
1474 val_z => x_resp_id,
1475 defined_z => l_resp_id_defined
1476 );
1477
1478 OPEN FIND_RESP_INFO;
1479 FETCH FIND_RESP_INFO INTO x_resp_key,x_resp_name;
1480 CLOSE FIND_RESP_INFO;
1481
1482 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1483 p_message => l_procedure_name
1484 );
1485
1486
1487 END get_default_login_resp;
1488
1489 /**
1490 * Procedure : UPGRADE_PRIMARY_USER
1491 * Type : Private
1492 * Pre_reqs : None
1493 * Description : Concurrent program to upgrade primary users
1494 * Parameters :
1495 * OUT parameters
1496 * As required by concurrent program standards
1497 **/
1498
1499 PROCEDURE UPGRADE_PRIMARY_USER(ERRBUF out NOCOPY VARCHAR2,
1500 RETCODE out NOCOPY VARCHAR2
1501 ) IS
1502
1503 l_new_usertype_id NUMBER := 0;
1504 l_pending_users NUMBER := 0;
1505
1506 l_new_ut_key JTF_UM_USERTYPES_B.USERTYPE_KEY%TYPE := 'BUSINESSUSER';
1507 l_old_ut_key JTF_UM_USERTYPES_B.USERTYPE_KEY%TYPE := 'PRIMARYUSER';
1508
1509 l_user_name varchar2(100);
1510 l_org_name varchar2(360);
1511 l_email varchar2(200);
1512
1513
1514 CURSOR FIND_BUS_UT_ID IS
1515 SELECT USERTYPE_ID
1516 FROM JTF_UM_USERTYPES_B
1517 WHERE USERTYPE_KEY = l_new_ut_key
1518 AND NVL(EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE;
1519
1520 CURSOR PRIMARY_USERS IS SELECT USERTYPE_REG_ID, USER_ID
1521 FROM JTF_UM_USERTYPE_REG UTREG
1522 WHERE UTREG.USERTYPE_ID IN (SELECT USERTYPE_ID FROM JTF_UM_USERTYPES_B
1523 WHERE USERTYPE_KEY = l_old_ut_key)
1524 AND UTREG.STATUS_CODE = 'APPROVED'
1525 AND NVL(UTREG.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
1526 AND NOT EXISTS
1527 (SELECT SUBSCRIPTION_REG_ID FROM JTF_UM_SUBSCRIPTION_REG SUBREG
1528 WHERE SUBREG.USER_ID = UTREG.USER_ID
1529 AND SUBREG.STATUS_CODE = 'PENDING'
1530 AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
1531 );
1532 /*
1533 CURSOR PENDING_USERS IS SELECT f.user_name, p.party_name, f.email_address
1534 FROM JTF_UM_USERTYPE_REG UTREG, JTF_UM_USERTYPES_B UT, hz_parties p, hz_relationships r, fnd_user f
1535 WHERE UT.USERTYPE_KEY = l_old_ut_key
1536 AND UT.USERTYPE_ID = UTREG.USERTYPE_ID
1537 AND UTREG.user_id = f.user_Id
1538 AND UTREG.STATUS_CODE = 'PENDING'
1539 AND p.party_id = r.object_id and r.party_id = f.customer_id
1540 AND NVL(utreg.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE;
1541 */
1542 CURSOR PENDING_USERS IS
1543 SELECT f.user_name, p.party_name, f.email_address
1544 FROM JTF_UM_USERTYPE_REG UTREG, JTF_UM_USERTYPES_B UT, hz_parties p, hz_relationships r, fnd_user f
1545 WHERE UT.USERTYPE_KEY = 'PRIMARYUSER'
1546 AND UT.USERTYPE_ID = UTREG.USERTYPE_ID
1547 AND R.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1548 AND R.object_table_name = 'HZ_PARTIES'
1549 AND R.START_DATE < SYSDATE
1550 AND NVL(R.END_DATE, SYSDATE + 1) > SYSDATE
1551 AND p.party_id = r.object_id and r.party_id = f.customer_id
1552 AND NVL(utreg.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
1553 AND UTREG.user_id = f.user_Id
1554 AND p.party_type = 'ORGANIZATION'
1555 AND
1556 (
1557 UTREG.STATUS_CODE = 'PENDING'
1558 OR
1559 ( UTREG.STATUS_CODE = 'APPROVED'
1560 AND
1561 EXISTS
1562 (
1563 SELECT SUBSCRIPTION_REG_ID
1564 FROM JTF_UM_SUBSCRIPTION_REG SUBREG
1565 WHERE SUBREG.USER_ID = UTREG.USER_ID
1566 AND
1567 SUBREG.STATUS_CODE = 'PENDING'
1568 AND
1569 NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
1570 )
1571 )
1572 );
1573
1574
1575 BEGIN
1576
1577 RETCODE := FND_API.G_RET_STS_SUCCESS;
1578
1579 OPEN FIND_BUS_UT_ID;
1580 FETCH FIND_BUS_UT_ID INTO l_new_usertype_id;
1581 IF FIND_BUS_UT_ID%NOTFOUND THEN
1582 ERRBUF := ERRBUF || fnd_global.newline || 'Could not find valid Business User type';
1583 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
1584 END IF;
1585 CLOSE FIND_BUS_UT_ID;
1586
1587
1588 fnd_message.set_name('JTF','JTA_UM_PRI_UP_USER_HEADER');
1589 fnd_file.new_line(fnd_file.log,1);
1590 fnd_file.put_line(fnd_file.log, fnd_message.get);
1591
1592 IF RETCODE = FND_API.G_RET_STS_SUCCESS THEN
1593
1594 FOR i IN PRIMARY_USERS LOOP
1595 fnd_message.set_name('JTF','JTA_UM_PRI_UP_USERS');
1596 UPDATE JTF_UM_USERTYPE_REG SET USERTYPE_ID = l_new_usertype_id
1597 WHERE USERTYPE_REG_ID = i.USERTYPE_REG_ID;
1598
1599 COMMIT;
1600 -- this code can result in exceptions hence enclosing in a seperate block
1601 -- as the following block is used for logging
1602 BEGIN
1603 select f.user_name, p.party_name, f.email_address into l_user_name, l_org_name, l_email
1604 from hz_parties p, hz_relationships r, fnd_user f
1605 where
1606 p.party_id = r.object_id and r.party_id = f.customer_id
1607 AND R.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1608 AND R.object_table_name = 'HZ_PARTIES'
1609 AND R.START_DATE < SYSDATE
1610 AND NVL(R.END_DATE, SYSDATE + 1) > SYSDATE
1611 and f.user_id = i.user_id
1612 and r.relationship_code='EMPLOYEE_OF';
1613
1614 fnd_message.set_token( 'USER_NAME', l_user_name );
1615 fnd_message.set_token( 'ORG_NAME', l_org_name );
1616 fnd_message.set_token( 'EMAIL', l_email );
1617 fnd_file.put_line(fnd_file.log, fnd_message.get);
1618 EXCEPTION
1619 WHEN OTHERS THEN
1620 fnd_message.set_token( 'USER_NAME', i.user_id );
1621 fnd_message.set_token( 'ORG_NAME', 'Could not be retrived' );
1622 fnd_message.set_token( 'EMAIL', 'Could not be retrived' );
1623 fnd_file.put_line(fnd_file.log, fnd_message.get);
1624 END;
1625 END LOOP;
1626
1627 END IF;
1628
1629 fnd_message.set_name('JTF','JTA_UM_PENDING_USER_HEADER');
1630 fnd_file.new_line(fnd_file.log,1);
1631 fnd_file.put_line(fnd_file.log, fnd_message.get);
1632
1633 FOR i IN PENDING_USERS LOOP
1634 fnd_message.set_name('JTF','JTA_UM_PENDING_USERS');
1635 fnd_message.set_token( 'USER_NAME', i.user_name );
1636 fnd_message.set_token( 'ORG_NAME', i.party_name );
1637 fnd_message.set_token( 'EMAIL', i.email_address );
1638 fnd_file.put_line(fnd_file.log, fnd_message.get);
1639 l_pending_users := l_pending_users + 1;
1640 END LOOP;
1641
1642 fnd_file.new_line(fnd_file.log,1);
1643 /*
1644 SELECT COUNT(*) INTO l_pending_users
1645 FROM JTF_UM_USERTYPE_REG UTREG, JTF_UM_USERTYPES_B UT
1646 WHERE UT.USERTYPE_KEY = l_old_ut_key
1647 AND UT.USERTYPE_ID = UTREG.USERTYPE_ID
1648 AND UTREG.STATUS_CODE = 'PENDING';
1649 */
1650 IF l_pending_users = 0 THEN
1651 fnd_message.set_name('JTF','JTA_UM_PRI_UP_COMPLETE');
1652 ELSE
1653 fnd_message.set_name('JTF','JTA_UM_PRI_UP_INCOMPLETE');
1654 fnd_message.set_token('USERS',l_pending_users);
1655 END IF;
1656
1657 fnd_file.new_line(fnd_file.log,1);
1658 fnd_file.put_line(fnd_file.log, fnd_message.get);
1659
1660 END UPGRADE_PRIMARY_USER;
1661 END JTF_UM_USERTYPE_CREDENTIALS;