[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_USERTYPE_CREDENTIALS
Source
1 PACKAGE BODY JTF_UM_USERTYPE_CREDENTIALS as
2 /* $Header: JTFUMUCB.pls 120.14 2006/06/23 09:54:36 snellepa 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 /****************************************************************
487 * Procedure Name :
488 * ASSIGN_USERTYPE_CREDENTIALS
489 * Description :
490 * This procedure is called upon manual/auto approval of an User
491 * Parameters
492 * X_USER_NAME : The User Name
493 * X_USER_ID : The User ID
494 * X_USERTYPE_ID: The Usertype to which the User belongs
495 *
496 ************************************************************/
497
498 PROCEDURE ASSIGN_USERTYPE_CREDENTIALS
499 (
500 X_USER_NAME VARCHAR2,
501 X_USER_ID NUMBER,
502 X_USERTYPE_ID NUMBER
503 )
504 IS
505
506 l_procedure_name CONSTANT varchar2(30) := 'ASSIGN_USERTYPE_CREDENTIALS';
507 p_usertype_resp_id number;
508 p_usertype_app_id NUMBER;
509 p_principal_name VARCHAR2(255);
510 l_version FND_RESPONSIBILITY_VL.VERSION%TYPE;
511 l_resp_id number;
512 l_app_id number;
513 l_resp_key FND_RESPONSIBILITY_VL.RESPONSIBILITY_KEY%TYPE;
514 l_resp_name FND_RESPONSIBILITY_VL.RESPONSIBILITY_NAME%TYPE;
515
516 userStartDate DATE;
517 userEndDate DATE;
518
519 -- added variables for 4276972
520 eventName constant varchar2(33):='oracle.apps.jtf.um.approveUTEvent';
521 l_usertype_reg_id number;
522 l_usertype_key varchar2(30);
523 l_parameter_list wf_parameter_list_t :=
524 wf_parameter_list_t();
525
526 --added variables for 4287135
527 isDefaultRespPresent boolean :=false;
528
529 CURSOR USERTYPE_RESP is select FR.RESPONSIBILITY_ID, UT.APPLICATION_ID, FR.VERSION
530 FROM JTF_UM_USERTYPE_RESP UT, FND_RESPONSIBILITY_VL FR
531 WHERE UT.USERTYPE_ID = X_USERTYPE_ID
532 AND FR.APPLICATION_ID = UT.APPLICATION_ID
533 AND FR.RESPONSIBILITY_KEY = UT.RESPONSIBILITY_KEY
534 AND (UT.EFFECTIVE_END_DATE IS NULL OR UT.EFFECTIVE_END_DATE > SYSDATE)
535 AND UT.EFFECTIVE_START_DATE < SYSDATE;
536
537 CURSOR USERTYPE_ROLES IS SELECT PRINCIPAL_NAME
538 FROM JTF_UM_USERTYPE_ROLE
539 WHERE USERTYPE_ID = X_USERTYPE_ID
540 AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)
541 AND EFFECTIVE_START_DATE < SYSDATE;
542
543
544 BEGIN
545
546 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
547 p_message => l_procedure_name
548 );
549
550 if l_is_debug_parameter_on then
551 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
552 p_message => 'X_USER_NAME:' || X_USER_NAME || '+' || 'X_USER_ID:' || X_USER_ID || '+' || 'X_USERTYPE_ID:' || X_USERTYPE_ID
553 );
554 end if;
555
556
557 --changes for 3899304
558 -- the user name which has been reserved has to be enabled.
559 -- enable user only if the user is a pending user.
560
561 select start_date,end_date into userStartDate,userEndDate from FND_USER
562 where user_id = X_USER_ID;
563
564 if to_char(userStartDate) = to_char(FND_API.G_MISS_DATE)
565 and to_char(userEndDate) = to_char(FND_API.G_MISS_DATE) then
566 fnd_user_pkg.EnableUser (
567 username => X_USER_NAME,
568 start_date => sysdate,
569 end_date => fnd_user_pkg.null_date);
570 end if;
571 --end of changes for 3899304
572 -- removing JTF_PENDING_REMOVAL resp assigned during registration process
573 -- as we now have a check to assign default responsibility only if there
574 -- are no previous default resp
575
576
577 JTF_UM_USERTYPE_CREDENTIALS.REVOKE_RESPONSIBILITY
578 ( X_USER_ID => X_USER_ID,
579 X_RESPONSIBILITY_KEY => 'JTF_PENDING_APPROVAL',
580 X_APPLICATION_ID => 690
581 );
582 -- Assign Responsibilites based on user type
583
584 /*check if any default responsibility is already present.
585 this check added for re-registration process of 4287135
586 in which case the user already has a default responsibility
587 and that should not change.*/
588
589
590 JTF_UM_USERTYPE_CREDENTIALS.get_default_login_resp(
591 p_user_id => X_USER_ID,
592 x_resp_id => l_resp_id,
593 x_app_id => l_app_id,
594 x_resp_key => l_resp_key,
595 x_resp_name => l_resp_name
596 );
597
598
599
600 IF l_resp_id IS NOT NULL AND l_app_id IS NOT NULL THEN
601 isDefaultRespPresent:=true;
602 END IF;
603
604
605 OPEN USERTYPE_RESP;
606 LOOP
607 FETCH USERTYPE_RESP INTO p_usertype_resp_id, p_usertype_app_id,l_version;
608 EXIT WHEN USERTYPE_RESP%NOTFOUND;
609
610 ASSIGN_RESPONSIBILITY
611 (
612 X_USER_ID => X_USER_ID,
613 X_RESPONSIBILITY_ID => p_usertype_resp_id,
614 X_APPLICATION_ID => p_usertype_app_id
615 );
616
617 -- Make this responsibility a default one if it is web based
618 IF NOT isDefaultRespPresent THEN
619 IF l_version = 'W' THEN
620
621 set_default_login_resp(
622 p_user_id => X_USER_ID,
623 p_resp_id => p_usertype_resp_id,
624 p_app_id => p_usertype_app_id
625 );
626 isDefaultRespPresent :=true;
627 END IF;
628 END IF;
629
630 END LOOP;
631 CLOSE USERTYPE_RESP;
632
633 -- Assign Roles based on user type
634
635 OPEN USERTYPE_ROLES;
636
637 LOOP
638 FETCH USERTYPE_ROLES INTO p_principal_name;
639 EXIT WHEN USERTYPE_ROLES%NOTFOUND;
640
641
642 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
643 ( USER_NAME => X_USER_NAME,
644 ROLE_NAME => p_principal_name,
645 OWNERTABLE_NAME => 'JTF_UM_USERTYPES_B',
646 OWNERTABLE_KEY => X_USERTYPE_ID
647 );
648
649
650 END LOOP;
651 CLOSE USERTYPE_ROLES;
652
653 -- Update the status
654
655 UPDATE JTF_UM_USERTYPE_REG SET STATUS_CODE='APPROVED'
656 WHERE USERTYPE_ID = X_USERTYPE_ID
657 AND USER_ID = X_USER_ID
658 and status_code='PENDING' and nvl(effective_end_date, SYSDATE + 1) > SYSDATE;
659
660 /* Bug fix: 3549056
661 Revoke Pending approval responsibility
662 No need to check whether usertype has responsibility associated to it or not
663
664
665
666 get_usertype_resp(
667 p_usertype_id => X_USERTYPE_ID,
668 p_resp_id => l_resp_id,
669 p_app_id => l_app_id
670 );
671
672 IF l_resp_id IS NOT NULL AND l_app_id IS NOT NULL THEN
673 */
674
675 -- END IF; Bug fix: 3549056
676
677
678
679
680
681
682 -- Bug Fix: 3493035
683 IF (nvl(FND_PROFILE.value('JTA_UM_AUTO_ACCT_CREATION'),'Y') = 'Y') THEN
684
685 QUERY_ACCOUNT (
686 X_USER_ID => X_USER_ID
687 );
688 END IF;
689
690 -- raise event for Approval : 4276972
691 -- Get the values for creation of parameters for the event
692
693 JTF_DEBUG_PUB.LOG_DEBUG (2, MODULE_NAME, 'Start Raising Event');
694
695 Select ut.APPLICATION_ID,ut.USERTYPE_KEY,reg.USERTYPE_REG_ID
696 Into l_app_id, l_usertype_key, l_usertype_reg_id
697 From JTF_UM_USERTYPES_B ut , JTF_UM_USERTYPE_REG reg
698 where ut.USERTYPE_ID=reg.USERTYPE_ID and reg.USER_ID=X_USER_ID
699 and reg.status_code='APPROVED' and
700 nvl(reg.EFFECTIVE_END_DATE,sysdate+1) > sysdate;
701
702 JTF_DEBUG_PUB.LOG_DEBUG (2, MODULE_NAME, 'Parameters '|| l_app_id ||' '||l_usertype_key|| ' '||l_usertype_reg_id );
703
704 -- create the parameter list
705 wf_event.AddParameterToList(
706 p_name => 'USERTYPEREG_ID',
707 p_value=>to_char(l_usertype_reg_id),
708 p_parameterlist=>l_parameter_list
709 );
710 wf_event.AddParameterToList(
711 p_name => 'APPID',
712 p_value=>to_char(l_app_id),
713 p_parameterlist=>l_parameter_list
714 );
715 wf_event.AddParameterToList(
716 p_name => 'USER_TYPE_KEY',
717 p_value=>l_usertype_key,
718 p_parameterlist=>l_parameter_list
719 );
720
721 -- raise the event
722 wf_event.raise(
723 p_event_name =>eventName,
724 p_event_key =>X_USER_ID ,
725 p_parameters => l_parameter_list
726 );
727
728 -- delete parameter list as it is no longer required
729 l_parameter_list.DELETE;
730
731 -- end of event handling
732
733
734 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
735 p_message => l_procedure_name
736 );
737
738
739 END ASSIGN_USERTYPE_CREDENTIALS;
740
741 PROCEDURE ASSIGN_ACCOUNT
742 (
743 P_PARTY_ID NUMBER,
744 P_USERTYPE_KEY VARCHAR2,
745 P_ORG_PARTY_ID NUMBER:=FND_API.G_MISS_NUM
746 )
747 IS
748
749 CURSOR FIND_APPROVAL IS SELECT APPROVAL_ID FROM JTF_UM_USERTYPES_B
750 WHERE USERTYPE_KEY = P_USERTYPE_KEY
751 AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE);
752
753 l_party_type HZ_PARTIES.party_type%type;
754
755 p_approval_id NUMBER := -1;
756 p_account_number NUMBER;
757 x_return_status VARCHAR2(100);
758 x_msg_count NUMBER;
759 x_msg_data VARCHAR2(1000);
760 x_cust_account_id NUMBER;
761 x_cust_account_number VARCHAR2(30);
762 x_party_id NUMBER;
763 x_party_number VARCHAR2(30);
764 x_profile_id NUMBER;
765 x_cust_account_role_id NUMBER;
766 cust_acct_roles_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.CUST_ACCOUNT_ROLE_REC_TYPE;
767
768 l_procedure_name varchar2(30) := 'ASSIGN_ACCOUNT';
769 BEGIN
770 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
771 p_message => l_procedure_name);
772
773
774
775
776 OPEN FIND_APPROVAL;
777 FETCH FIND_APPROVAL INTO p_approval_id;
778 CLOSE FIND_APPROVAL;
779
780 l_party_type := JTF_UM_UTIL_PVT.check_party_type(P_PARTY_ID);
781
782 if l_is_debug_parameter_on then
783 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
784 p_message =>'l_party_type:' || l_party_type
785 );
786 end if;
787
788 SELECT hz_account_num_s.nextval into p_account_number FROM DUAL;
789 if(p_approval_id is null or p_approval_id = -1 OR l_party_type='PARTY_RELATIONSHIP')
790 then
791
792 --if P_USERTYPE_KEY = 'INDIVIDUALUSER' then
793 if l_party_type = 'PERSON' then
794
795 -- create new account for Individual User
796
797 jtf_customer_accounts_pvt.create_account(
798 p_api_version => 1,
799 p_init_msg_list => 'T',
800 p_commit => 'F',
801 p_party_id => P_PARTY_ID,
802 p_account_number => p_account_number,
803 p_create_amt => 'F',
804 p_party_type => 'P',
805 x_return_status => x_return_status,
806 x_msg_count => x_msg_count,
807 x_msg_data => x_msg_data,
808 x_cust_account_id => x_cust_account_id,
809 x_cust_account_number => x_cust_account_number,
810 x_party_id => x_party_id,
811 x_party_number => x_party_number,
812 x_profile_id => x_profile_id
813 );
814
815 else
816
817 -- create new account for Primary User or businessuser who was not assigned
818 -- one when he was approved.
819
820 jtf_customer_accounts_pvt.create_account(
821 p_api_version => 1,
822 p_init_msg_list => 'T',
823 p_commit => 'F',
824 p_party_id => P_ORG_PARTY_ID,
825 p_account_number => p_account_number,
826 p_create_amt => 'F',
827 p_party_type => 'O',
828 x_return_status => x_return_status,
829 x_msg_count => x_msg_count,
830 x_msg_data => x_msg_data,
831 x_cust_account_id => x_cust_account_id,
832 x_cust_account_number => x_cust_account_number,
833 x_party_id => x_party_id,
834 x_party_number => x_party_number,
835 x_profile_id => x_profile_id
836 );
837 if l_is_debug_parameter_on then
838 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
839 p_message =>'X_return_status@create_account:' ||x_return_status
840 );
841 end if;
842
843 end if;
844 if(x_return_status <> fnd_api.g_ret_sts_success)
845 then
846 jtf_debug_pub.log_exception(p_module => MODULE_NAME
847 ,p_message => ' message stack in account creation:'||x_msg_data);
848 raise_application_error(-20101,'Failed to create account:'||x_msg_data);
849
850 end if;
851 -- Creating record in hz_cust_account_roles
852
853 if x_cust_account_id is not null then
854
855 cust_acct_roles_rec.cust_account_id := x_cust_account_id;
856 cust_acct_roles_rec.party_id := P_PARTY_ID;
857 cust_acct_roles_rec.role_type := 'CONTACT';
858 cust_acct_roles_rec.status := 'A';
859 cust_acct_roles_rec.created_by_module := 'JTA_USER_MANAGEMENT';
860 hz_cust_account_role_v2pub.create_cust_account_role(
861 p_init_msg_list =>'T',
862 p_cust_account_role_rec => cust_acct_roles_rec,
863 x_cust_account_role_id => x_cust_account_role_id,
864 x_return_status => x_return_status,
865 x_msg_count => x_msg_count,
866 x_msg_data => x_msg_data
867 );
868
869 if(x_return_status <> fnd_api.g_ret_sts_success)
870 then
871 raise_application_error(-20101,'Failed to create cust account role:'||x_msg_data);
872 end if;
873 end if;
874
875 end if;
876 END ASSIGN_ACCOUNT;
877
878 PROCEDURE REJECT_DELETED_PEND_USER (P_USERNAME in VARCHAR2,
879 X_PENDING_USER out NOCOPY VARCHAR2)
880
881 IS
882
883 CURSOR FIND_UT_APPWF_INFO IS
884 SELECT reg.WF_ITEM_TYPE, to_char (reg.USERTYPE_REG_ID)
885 FROM JTF_UM_USERTYPE_REG reg, FND_USER fu
886 WHERE fu.USER_NAME = P_USERNAME
887 AND fu.USER_ID = reg.USER_ID
888 AND STATUS_CODE = 'PENDING'
889 AND (reg.EFFECTIVE_END_DATE is null
890 OR reg.EFFECTIVE_END_DATE > sysdate);
891
892 itemtype varchar2 (8);
893 itemkey varchar2 (240);
894
895 BEGIN
896
897 OPEN FIND_UT_APPWF_INFO;
898 FETCH FIND_UT_APPWF_INFO INTO itemtype, itemkey;
899 IF (FIND_UT_APPWF_INFO%NOTFOUND) THEN
900 X_PENDING_USER := 'N';
901 CLOSE FIND_UT_APPWF_INFO;
902 RETURN;
903 END IF;
904 CLOSE FIND_UT_APPWF_INFO;
905
906 JTF_UM_WF_APPROVAL.COMPLETEAPPROVALACTIVITY (itemtype, itemkey, 'REJECTED', 'User deleted');
907
908 -- Need to Cancel Notification
909 JTF_UM_WF_APPROVAL.CANCEL_NOTIFICATION (itemtype, itemkey);
910 X_PENDING_USER := 'Y';
911
912 END REJECT_DELETED_PEND_USER;
913
914 PROCEDURE ASSIGN_DEF_RESP(P_USERNAME in VARCHAR2,
915 P_ACCOUNT_TYPE in VARCHAR2)
916 IS
917
918 l_usertype_id NUMBER;
919 l_usertype_key VARCHAR2(30);
920 l_responsibility_key VARCHAR2(30);
921 l_application_id NUMBER;
922 l_user_id NUMBER;
923
924 cursor find_default_resp is select responsibility_key, application_id from
925 jtf_um_usertype_resp where usertype_id = l_usertype_id
926 and (effective_end_date is null or effective_end_date > sysdate) ;
927
928 cursor find_usertype is select usertype_id from jtf_um_usertypes_b
929 where usertype_key = l_usertype_key
930 and (effective_end_date is null or effective_end_date > sysdate) ;
931
932 cursor find_user_id is select user_id from fnd_user where user_name = P_USERNAME;
933
934 BEGIN
935
936 -- Map account type to user type
937
938 IF UPPER(P_ACCOUNT_TYPE) = 'BUSINESSUSER' THEN
939 l_usertype_key := 'BUSINESSUSER';
940
941 ELSE
942 l_usertype_key := 'INDIVIDUALUSER';
943
944 END IF;
945
946 -- Find out user type id
947
948 open find_usertype;
949 fetch find_usertype into l_usertype_id;
950 close find_usertype;
951
952 -- Find out default responsibility
953
954 open find_default_resp;
955 fetch find_default_resp into l_responsibility_key, l_application_id;
956 close find_default_resp;
957
958 -- Assign default responsibility
959 if l_responsibility_key is not null and l_application_id is not null then
960
961 open find_user_id;
962 fetch find_user_id into l_user_id;
963 close find_user_id;
964
965 -- Make sure that user_id is not null
966 if l_user_id is not null then
967 ASSIGN_DEFAULT_RESPONSIBILITY
968 (
969 X_USER_ID => l_user_id,
970 X_RESPONSIBILITY_KEY => l_responsibility_key,
971 X_APPLICATION_ID => l_application_id
972 );
973 end if;
974 end if;
975
976 END ASSIGN_DEF_RESP;
977
978 PROCEDURE ASSIGN_DEF_ROLES(P_USERNAME in VARCHAR2,
979 P_ACCOUNT_TYPE in VARCHAR2)
980 IS
981
982 l_usertype_id NUMBER;
983 l_usertype_key VARCHAR2(30);
984 l_principal_name VARCHAR2(255);
985
986 cursor find_default_role is select principal_name from
987 jtf_um_usertype_role where usertype_id = l_usertype_id
988 and (effective_end_date is null or effective_end_date > sysdate) ;
989
990 cursor find_usertype is select usertype_id from jtf_um_usertypes_b
991 where usertype_key = l_usertype_key
992 and (effective_end_date is null or effective_end_date > sysdate) ;
993
994 BEGIN
995
996 -- Map account type to user type
997
998 IF UPPER(P_ACCOUNT_TYPE) = 'BUSINESSUSER' THEN
999 l_usertype_key := 'BUSINESSUSER';
1000
1001 ELSE
1002 l_usertype_key := 'INDIVIDUALUSER';
1003
1004 END IF;
1005
1006 -- Find out user type id
1007
1008 open find_usertype;
1009 fetch find_usertype into l_usertype_id;
1010 close find_usertype;
1011
1012 -- Find out default roles
1013
1014 open find_default_role;
1015
1016 loop
1017 fetch find_default_role into l_principal_name;
1018 exit when find_default_role%NOTFOUND;
1019
1020 -- Assign default roles
1021 if l_principal_name is not null then
1022
1023 -- Make sure that user name is not null
1024 if P_USERNAME is not null then
1025 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
1026 ( USER_NAME => P_USERNAME,
1027 ROLE_NAME => l_principal_name,
1028 OWNERTABLE_NAME => 'JTF_UM_USERTYPES_B',
1029 OWNERTABLE_KEY => l_usertype_id
1030 );
1031 end if;
1032 end if;
1033 end loop;
1034 close find_default_role;
1035
1036 END ASSIGN_DEF_ROLES;
1037
1038
1039 /**
1040 * Procedure : get_usertype_resp
1041 * Type : Private
1042 * Pre_reqs : None
1043 * Description : Will determine the responsibility attached to the usertype
1044 * Parameters :
1045 * input parameters
1046 * @param p_usertype_id
1047 * description: The usertyp_id
1048 * required : Y
1049 * validation : Must be a valid usertype_id
1050 * output parameters
1051 * x_resp_id
1052 * description: The responsibility_id associated to the responsibility
1053 * associated to the usertype
1054 * x_app_id
1055 * description: The app_id associated to the responsibility
1056 * associated to the usertype
1057 **/
1058 procedure get_usertype_resp(
1059 p_usertype_id in number,
1060 p_resp_id out NOCOPY number,
1061 p_app_id out NOCOPY number
1062 ) IS
1063
1064 l_procedure_name CONSTANT varchar2(30) := 'get_usertype_resp';
1065 CURSOR FIND_UT_RESP IS SELECT UTRESP.APPLICATION_ID, FURESP.RESPONSIBILITY_ID
1066 FROM JTF_UM_USERTYPE_RESP UTRESP, FND_RESPONSIBILITY_VL FURESP
1067 WHERE UTRESP.USERTYPE_ID = p_usertype_id
1068 AND NVL(UTRESP.EFFECTIVE_END_DATE,SYSDATE+1) > SYSDATE
1069 AND UTRESP.EFFECTIVE_START_DATE < SYSDATE
1070 AND UTRESP.RESPONSIBILITY_KEY = FURESP.RESPONSIBILITY_KEY;
1071
1072 BEGIN
1073
1074 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1075 p_message => l_procedure_name
1076 );
1077 if l_is_debug_parameter_on then
1078 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1079 p_message => 'p_usertype_id:' || p_usertype_id
1080 );
1081 end if;
1082
1083 OPEN FIND_UT_RESP;
1084 FETCH FIND_UT_RESP INTO p_app_id, p_resp_id;
1085 CLOSE FIND_UT_RESP;
1086
1087 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1088 p_message => l_procedure_name
1089 );
1090
1091 END get_usertype_resp;
1092
1093
1094 /**
1095 * Procedure : grant_roles
1096 * Type : Private
1097 * Pre_reqs : None
1098 * Description : Will grant roles to users
1099 * Parameters :
1100 * input parameters
1101 * p_user_name:
1102 * description: The user_name of the user
1103 * required : Y
1104 * validation : Must be a valid user_name
1105 * p_role_id
1106 * description: The value of the JTF_AUTH_PRINCIPAL_ID
1107 * required : Y
1108 * validation : Must exist as a JTF_AUTH_PRONCIPAL_ID
1109 * in the table JTF_AUTH_PRINCIPALS_B
1110 * p_source_name
1111 * description: The value of the name of the source
1112 * required : Y
1113 * validation : Must be "USERTYPE" or "ENROLLMENT"
1114 * p_source_id
1115 * description: The value of the id associated with the source
1116 * required : Y
1117 * validation : Must be a usertype_id or a subscription_id
1118 * output parameters
1119 * None
1120 */
1121 procedure grant_roles (
1122 p_user_name in varchar2,
1123 p_role_id in number,
1124 p_source_name in varchar2,
1125 p_source_id in varchar2
1126 ) IS
1127
1128 CURSOR FIND_ROLE_NAME IS SELECT PRINCIPAL_NAME FROM JTF_AUTH_PRINCIPALS_B
1129 WHERE JTF_AUTH_PRINCIPAL_ID = p_role_id AND IS_USER_FLAG = 0;
1130
1131 l_owner_table_name varchar2(50);
1132 l_principal_name JTF_AUTH_PRINCIPALS_B.PRINCIPAL_NAME%TYPE;
1133
1134 BEGIN
1135
1136 IF p_source_name <> 'USERTYPE' OR p_source_name <> 'ENROLLMENT' THEN
1137 RAISE_APPLICATION_ERROR(-20000,'The source name is incorrect');
1138 END IF;
1139
1140 OPEN FIND_ROLE_NAME;
1141 FETCH FIND_ROLE_NAME INTO l_principal_name;
1142
1143 IF FIND_ROLE_NAME%NOTFOUND THEN
1144 CLOSE FIND_ROLE_NAME;
1145 RAISE_APPLICATION_ERROR(-20000,'The role id is incorrect');
1146 END IF;
1147
1148 CLOSE FIND_ROLE_NAME;
1149
1150 IF p_source_name = 'USERTYPE' THEN
1151
1152 l_owner_table_name := 'JTF_UM_USERTYPES_B';
1153
1154 ELSIF p_source_name = 'ENROLLMENT' THEN
1155
1156 l_owner_table_name := 'ENROLLMENT';
1157
1158 END IF;
1159
1160
1161 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
1162 ( USER_NAME => p_user_name,
1163 ROLE_NAME => l_principal_name,
1164 OWNERTABLE_NAME => l_owner_table_name,
1165 OWNERTABLE_KEY => p_source_id
1166 );
1167
1168 END grant_roles;
1169
1170 /**
1171 * Procedure : set_default_login_responsibility
1172 * Type : Private
1173 * Pre_reqs : None
1174 * Description : Will set the default responsibility of a user
1175 * Parameters :
1176 * input parameters
1177 * @param p_user_id
1178 * description: The user_id of a user
1179 * validation : Must be a valid user_id
1180 * @param p_resp_id
1181 * description: The responsibility_id associated to the default logon
1182 * responsibility of a user
1183 * required : Y
1184 * validation : Must be a valid responsibility_id
1185 * @param p_app_id
1186 * description: The app_id associated to the default logon
1187 * responsibility of a user
1188 * required : Y
1189 * validation: Must be a valid application_id
1190 * output parameters
1191 * None
1192 **/
1193
1194 procedure set_default_login_resp(
1195 p_user_id in number,
1196 p_resp_id in number,
1197 p_app_id in number
1198 ) IS
1199 l_procedure_name CONSTANT varchar2(30) := 'set_default_login_resp';
1200 p_assign_def_resp boolean;
1201 p_assign_def_appl boolean;
1202
1203 BEGIN
1204
1205 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1206 p_message => l_procedure_name
1207 );
1208 if l_is_debug_parameter_on then
1209 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1210 p_message => 'p_user_id:' || p_user_id || '+' || 'p_resp_id:' || p_resp_id || '+' || 'p_app_id:' || p_app_id
1211 );
1212 end if;
1213
1214 p_assign_def_resp := fnd_profile.save
1215 (X_NAME => 'JTF_PROFILE_DEFAULT_RESPONSIBILITY',
1216 X_VALUE => p_resp_id,
1217 X_LEVEL_NAME => 'USER',
1218 X_LEVEL_VALUE => p_user_id);
1219
1220 p_assign_def_appl := fnd_profile.save
1221 (X_NAME => 'JTF_PROFILE_DEFAULT_APPLICATION',
1222 X_VALUE => p_app_id,
1223 X_LEVEL_NAME => 'USER',
1224 X_LEVEL_VALUE => p_user_id);
1225
1226 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1227 p_message => l_procedure_name
1228 );
1229 END set_default_login_resp;
1230
1231 /**
1232 * Procedure : set_default_login_resp
1233 * Type : Private
1234 * Pre_reqs : None
1235 * Description : Will set the default responsibility of a user
1236 * Parameters :
1237 * input parameters
1238 * @param p_user_name
1239 * description: The user_name of a user
1240 * validation : Must be a valid user_name
1241 * @param p_resp_id
1242 * description: The responsibility_id associated to the default logon
1243 * responsibility of a user
1244 * required : Y
1245 * validation : Must be a valid responsibility_id
1246 * @param p_app_id
1247 * description: The app_id associated to the default logon
1248 * responsibility of a user
1249 * required : Y
1250
1251 * output parameters
1252 * None
1253 **/
1254
1255 procedure set_default_login_resp(
1256 p_user_name in varchar2,
1257 p_resp_id in number,
1258 p_app_id in number
1259 ) IS
1260
1261 l_procedure_name CONSTANT varchar2(30) := 'set_default_login_resp';
1262
1263 BEGIN
1264
1265 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1266 p_message => l_procedure_name
1267 );
1268 if l_is_debug_parameter_on then
1269 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1270 p_message => 'p_user_name:' || p_user_name || '+' || 'p_resp_id:' || p_resp_id || '+' || 'p_app_id:' || p_app_id
1271 );
1272 end if;
1273
1274
1275 set_default_login_resp(
1276 p_user_id => JTF_UM_UTIL_PVT.get_user_id(p_user_name),
1277 p_resp_id => p_resp_id,
1278 p_app_id => p_app_id
1279 );
1280
1281
1282 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1283 p_message => l_procedure_name
1284 );
1285
1286 END set_default_login_resp;
1287
1288
1289 /**
1290 * Procedure : get_default_login_resp
1291 * Type : Private
1292 * Pre_reqs : None
1293 * Description : Will set the default responsibility of a user
1294 * Parameters :
1295 * input parameters
1296 * @param p_user_name
1297 * description: The user_name of a user
1298 * validation : Must be a valid user_name
1299 * output parameters
1300 * @param x_resp_id
1301 * description: The responsibility_id associated to the default logon
1302 * responsibility of a user
1303 * @param x_app_id
1304 * description: The app_id associated to the default logon
1305 * responsibility of a user
1306 * @param x_resp_key
1307 * description: The responsibility_key associated to the default logon
1308 * responsibility of a user
1309 * @param x_resp_name
1310 * description: The responsibility_name associated to the default logon
1311 * responsibility of a user
1312 *
1313 * None
1314 **/
1315
1316 procedure get_default_login_resp(
1317 p_user_name in varchar2,
1318 x_resp_id out NOCOPY number,
1319 x_app_id out NOCOPY number,
1320 x_resp_key out NOCOPY varchar2,
1321 x_resp_name out NOCOPY varchar2
1322 ) IS
1323
1324 l_procedure_name CONSTANT varchar2(30) := 'get_default_login_resp';
1325
1326
1327 BEGIN
1328
1329 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1330 p_message => l_procedure_name
1331 );
1332 if l_is_debug_parameter_on then
1333 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1334 p_message => 'p_user_name:' || p_user_name
1335 );
1336 end if;
1337
1338
1339 get_default_login_resp(
1340 p_user_id => JTF_UM_UTIL_PVT.get_user_id(p_user_name),
1341 x_resp_id => x_resp_id,
1342 x_app_id => x_app_id ,
1343 x_resp_key => x_resp_key ,
1344 x_resp_name => x_resp_name
1345 );
1346
1347
1348 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1349 p_message => l_procedure_name
1350 );
1351
1352 END get_default_login_resp;
1353
1354
1355 /**
1356 * Procedure : get_default_login_resp
1357 * Type : Private
1358 * Pre_reqs : None
1359 * Description : Will set the default responsibility of a user
1360 * Parameters :
1361 * input parameters
1362 * @param p_user_id
1363 * description: The user_name of a user
1364 * validation : Must be a valid user_id
1365 * output parameters
1366 * @param x_resp_id
1367 * description: The responsibility_id associated to the default logon
1368 * responsibility of a user
1369 * @param x_app_id
1370 * description: The app_id associated to the default logon
1371 * responsibility of a user
1372 * @param x_resp_key
1373 * description: The responsibility_key associated to the default logon
1374 * responsibility of a user
1375 * @param x_resp_name
1376 * description: The responsibility_name associated to the default logon
1377 * responsibility of a user
1378 *
1379 * None
1380 **/
1381
1382 procedure get_default_login_resp(
1383 p_user_id in number,
1384 x_resp_id out NOCOPY number,
1385 x_app_id out NOCOPY number,
1386 x_resp_key out NOCOPY varchar2,
1387 x_resp_name out NOCOPY varchar2
1388 ) IS
1389
1390 l_procedure_name CONSTANT varchar2(30) := 'get_default_login_resp';
1391 l_app_id_defined boolean;
1392 l_resp_id_defined boolean;
1393 CURSOR FIND_RESP_INFO IS SELECT RESPONSIBILITY_KEY, RESPONSIBILITY_NAME
1394 FROM FND_RESPONSIBILITY_VL
1395 WHERE RESPONSIBILITY_ID = x_resp_id
1396 AND APPLICATION_ID = x_app_id;
1397
1398 BEGIN
1399
1400 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1401 p_message => l_procedure_name
1402 );
1403 if l_is_debug_parameter_on then
1404 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1405 p_message => 'p_user_id:' || p_user_id
1406 );
1407 end if;
1408
1409 JTF_UM_UTIL_PVT.GET_SPECIFIC(
1410 name_z => 'JTF_PROFILE_DEFAULT_APPLICATION',
1411 user_id_z => p_user_id,
1412 val_z => x_app_id,
1413 defined_z => l_app_id_defined
1414 );
1415
1416 JTF_UM_UTIL_PVT.GET_SPECIFIC(
1417 name_z => 'JTF_PROFILE_DEFAULT_RESPONSIBILITY',
1418 user_id_z => p_user_id,
1419 val_z => x_resp_id,
1420 defined_z => l_resp_id_defined
1421 );
1422
1423 OPEN FIND_RESP_INFO;
1424 FETCH FIND_RESP_INFO INTO x_resp_key,x_resp_name;
1425 CLOSE FIND_RESP_INFO;
1426
1427 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1428 p_message => l_procedure_name
1429 );
1430
1431
1432 END get_default_login_resp;
1433
1434 /**
1435 * Procedure : UPGRADE_PRIMARY_USER
1436 * Type : Private
1437 * Pre_reqs : None
1438 * Description : Concurrent program to upgrade primary users
1439 * Parameters :
1440 * OUT parameters
1441 * As required by concurrent program standards
1442 **/
1443
1444 PROCEDURE UPGRADE_PRIMARY_USER(ERRBUF out NOCOPY VARCHAR2,
1445 RETCODE out NOCOPY VARCHAR2
1446 ) IS
1447
1448 l_new_usertype_id NUMBER := 0;
1449 l_pending_users NUMBER := 0;
1450
1451 l_new_ut_key JTF_UM_USERTYPES_B.USERTYPE_KEY%TYPE := 'BUSINESSUSER';
1452 l_old_ut_key JTF_UM_USERTYPES_B.USERTYPE_KEY%TYPE := 'PRIMARYUSER';
1453
1454 l_user_name varchar2(100);
1455 l_org_name varchar2(360);
1456 l_email varchar2(200);
1457
1458
1459 CURSOR FIND_BUS_UT_ID IS
1460 SELECT USERTYPE_ID
1461 FROM JTF_UM_USERTYPES_B
1462 WHERE USERTYPE_KEY = l_new_ut_key
1463 AND NVL(EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE;
1464
1465 CURSOR PRIMARY_USERS IS SELECT USERTYPE_REG_ID, USER_ID
1466 FROM JTF_UM_USERTYPE_REG UTREG
1467 WHERE UTREG.USERTYPE_ID IN (SELECT USERTYPE_ID FROM JTF_UM_USERTYPES_B
1468 WHERE USERTYPE_KEY = l_old_ut_key)
1469 AND UTREG.STATUS_CODE = 'APPROVED'
1470 AND NVL(UTREG.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
1471 AND NOT EXISTS
1472 (SELECT SUBSCRIPTION_REG_ID FROM JTF_UM_SUBSCRIPTION_REG SUBREG
1473 WHERE SUBREG.USER_ID = UTREG.USER_ID
1474 AND SUBREG.STATUS_CODE = 'PENDING'
1475 AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
1476 );
1477 /*
1478 CURSOR PENDING_USERS IS SELECT f.user_name, p.party_name, f.email_address
1479 FROM JTF_UM_USERTYPE_REG UTREG, JTF_UM_USERTYPES_B UT, hz_parties p, hz_relationships r, fnd_user f
1480 WHERE UT.USERTYPE_KEY = l_old_ut_key
1481 AND UT.USERTYPE_ID = UTREG.USERTYPE_ID
1482 AND UTREG.user_id = f.user_Id
1483 AND UTREG.STATUS_CODE = 'PENDING'
1484 AND p.party_id = r.object_id and r.party_id = f.customer_id
1485 AND NVL(utreg.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE;
1486 */
1487 CURSOR PENDING_USERS IS
1488 SELECT f.user_name, p.party_name, f.email_address
1489 FROM JTF_UM_USERTYPE_REG UTREG, JTF_UM_USERTYPES_B UT, hz_parties p, hz_relationships r, fnd_user f
1490 WHERE UT.USERTYPE_KEY = 'PRIMARYUSER'
1491 AND UT.USERTYPE_ID = UTREG.USERTYPE_ID
1492 AND R.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1493 AND R.object_table_name = 'HZ_PARTIES'
1494 AND R.START_DATE < SYSDATE
1495 AND NVL(R.END_DATE, SYSDATE + 1) > SYSDATE
1496 AND p.party_id = r.object_id and r.party_id = f.customer_id
1497 AND NVL(utreg.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
1498 AND UTREG.user_id = f.user_Id
1499 AND
1500 (
1501 UTREG.STATUS_CODE = 'PENDING'
1502 OR
1503 ( UTREG.STATUS_CODE = 'APPROVED'
1504 AND
1505 EXISTS
1506 (
1507 SELECT SUBSCRIPTION_REG_ID
1508 FROM JTF_UM_SUBSCRIPTION_REG SUBREG
1509 WHERE SUBREG.USER_ID = UTREG.USER_ID
1510 AND
1511 SUBREG.STATUS_CODE = 'PENDING'
1512 AND
1513 NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE
1514 )
1515 )
1516 );
1517
1518
1519 BEGIN
1520
1521 RETCODE := FND_API.G_RET_STS_SUCCESS;
1522
1523 OPEN FIND_BUS_UT_ID;
1524 FETCH FIND_BUS_UT_ID INTO l_new_usertype_id;
1525 IF FIND_BUS_UT_ID%NOTFOUND THEN
1526 ERRBUF := ERRBUF || fnd_global.newline || 'Could not find valid Business User type';
1527 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
1528 END IF;
1529 CLOSE FIND_BUS_UT_ID;
1530
1531
1532 fnd_message.set_name('JTF','JTA_UM_PRI_UP_USER_HEADER');
1533 fnd_file.new_line(fnd_file.log,1);
1534 fnd_file.put_line(fnd_file.log, fnd_message.get);
1535
1536 IF RETCODE = FND_API.G_RET_STS_SUCCESS THEN
1537
1538 FOR i IN PRIMARY_USERS LOOP
1539 fnd_message.set_name('JTF','JTA_UM_PRI_UP_USERS');
1540 UPDATE JTF_UM_USERTYPE_REG SET USERTYPE_ID = l_new_usertype_id
1541 WHERE USERTYPE_REG_ID = i.USERTYPE_REG_ID;
1542
1543 COMMIT;
1544 -- this code can result in exceptions hence enclosing in a seperate block
1545 -- as the following block is used for logging
1546 BEGIN
1547 select f.user_name, p.party_name, f.email_address into l_user_name, l_org_name, l_email
1548 from hz_parties p, hz_relationships r, fnd_user f
1549 where
1550 p.party_id = r.object_id and r.party_id = f.customer_id
1551 AND R.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1552 AND R.object_table_name = 'HZ_PARTIES'
1553 AND R.START_DATE < SYSDATE
1554 AND NVL(R.END_DATE, SYSDATE + 1) > SYSDATE
1555 and f.user_id = i.user_id
1556 and r.relationship_code='EMPLOYEE_OF';
1557
1558 fnd_message.set_token( 'USER_NAME', l_user_name );
1559 fnd_message.set_token( 'ORG_NAME', l_org_name );
1560 fnd_message.set_token( 'EMAIL', l_email );
1561 fnd_file.put_line(fnd_file.log, fnd_message.get);
1562 EXCEPTION
1563 WHEN OTHERS THEN
1564 fnd_message.set_token( 'USER_NAME', i.user_id );
1565 fnd_message.set_token( 'ORG_NAME', 'Could not be retrived' );
1566 fnd_message.set_token( 'EMAIL', 'Could not be retrived' );
1567 fnd_file.put_line(fnd_file.log, fnd_message.get);
1568 END;
1569 END LOOP;
1570
1571 END IF;
1572
1573 fnd_message.set_name('JTF','JTA_UM_PENDING_USER_HEADER');
1574 fnd_file.new_line(fnd_file.log,1);
1575 fnd_file.put_line(fnd_file.log, fnd_message.get);
1576
1577 FOR i IN PENDING_USERS LOOP
1578 fnd_message.set_name('JTF','JTA_UM_PENDING_USERS');
1579 fnd_message.set_token( 'USER_NAME', i.user_name );
1580 fnd_message.set_token( 'ORG_NAME', i.party_name );
1581 fnd_message.set_token( 'EMAIL', i.email_address );
1582 fnd_file.put_line(fnd_file.log, fnd_message.get);
1583 l_pending_users := l_pending_users + 1;
1584 END LOOP;
1585
1586 fnd_file.new_line(fnd_file.log,1);
1587 /*
1588 SELECT COUNT(*) INTO l_pending_users
1589 FROM JTF_UM_USERTYPE_REG UTREG, JTF_UM_USERTYPES_B UT
1590 WHERE UT.USERTYPE_KEY = l_old_ut_key
1591 AND UT.USERTYPE_ID = UTREG.USERTYPE_ID
1592 AND UTREG.STATUS_CODE = 'PENDING';
1593 */
1594 IF l_pending_users = 0 THEN
1595 fnd_message.set_name('JTF','JTA_UM_PRI_UP_COMPLETE');
1596 ELSE
1597 fnd_message.set_name('JTF','JTA_UM_PRI_UP_INCOMPLETE');
1598 fnd_message.set_token('USERS',l_pending_users);
1599 END IF;
1600
1601 fnd_file.new_line(fnd_file.log,1);
1602 fnd_file.put_line(fnd_file.log, fnd_message.get);
1603
1604 END UPGRADE_PRIMARY_USER;
1605 END JTF_UM_USERTYPE_CREDENTIALS;