[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_WEBADI_SALSTEAM_UPDATE
Source
1 PACKAGE BODY JTF_TTY_WEBADI_SALSTEAM_UPDATE AS
2 /* $Header: jtfvstub.pls 120.2 2005/09/22 21:13:13 shli noship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTF_TTY_WEBADI_salsteam_update
6 -- ---------------------------------------------------
7
8 -- PURPOSE
9 -- upload named account territory resource information into excel
10 --
11 --
12 -- PROCEDURES:
13 -- (see below for specification)
14 --
15 --
16 -- HISTORY
17 -- 05/17/2003 sbehera Package Body Created
18 -- 05/29/2003 JRADHAKR Modularized the code and added more
19 -- validations. Still need to seed message
20 -- 06/03/2003 shli message seeded.
21 -- 06/09/2003 JRADHAKR Fixed Bug 2998045,2997557
22 -- 07/18/2003 shli proxy user implemented.
23 -- 08/13/2003 arpatel added call to alignment package
24 -- 10/10/2003 sp Modified validate_resource procedure for alignment
25 --
26 -- End of Comments
27 --
28
29
30 PROCEDURE validate_resource (
31 P_RESOURCE_NAME in varchar2,
32 P_GROUP_NAME in varchar2,
33 P_ROLE_NAME in varchar2,
34 P_terr_group_id in number,
35 P_named_account_id in number,
36 P_TERR_GRP_ACCT_ID in number,
37 p_alignment_id in varchar2,
38 X_RESOURCE_id out NOCOPY number,
39 x_group_id out NOCOPY number,
40 x_role_code out NOCOPY varchar2,
41 x_error_code out NOCOPY number,
42 x_status out NOCOPY varchar2) is
43
44 counter NUMBER:=0;
45 comb NUMBER:=0;
46 l_select varchar2(10);
47 l_user_id NUMBER;
48 found NUMBER;
49 l_num_valid_rsc_id NUMBER := 0;
50 TYPE NUMBER_TABLE_TYPE IS TABLE OF NUMBER;
51 l_res_tbl NUMBER_TABLE_TYPE := NUMBER_TABLE_TYPE();
52
53 CURSOR c_get_resource_id ( c_resource_name VARCHAR2 ) IS
54 SELECT RESOURCE_id
55 FROM jtf_rs_resource_extns_vl
56 WHERE upper(resource_name) = upper(c_resource_name)
57 AND category = 'EMPLOYEE'
58 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1);
59
60 CURSOR c_get_group_id ( c_group_name VARCHAR2 ) IS
61 SELECT group_id
62 FROM jtf_rs_groups_vl
63 WHERE upper(group_name) = upper(c_group_name)
64 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1);
65
66 CURSOR c_get_role_code ( c_role_name VARCHAR2 ) IS
67 SELECT rol.role_code
68 FROM jtf_rs_roles_vl rol
69 WHERE upper(rol.role_name) = upper(c_role_name)
70 AND ( rol.role_type_code = 'SALES'
71 OR rol.role_type_code = 'TELESALES'
72 OR rol.role_type_code = 'FIELDSALES'
73 )
74 AND active_flag ='Y';
75
76
77 BEGIN
78 x_status := 'S';
79 l_user_id := fnd_global.user_id;
80
81 IF P_RESOURCE_NAME is not null
82 AND P_GROUP_NAME is not null
83 AND P_ROLE_NAME is not null
84 THEN
85
86 /* validation against LOVs. by terr group's owner resource_id allows a resource not owned by the logged in
87 user valid. The validation also blocks any resource outside the terr group.
88 */
89
90
91 -- for both NA and Alignment
92 BEGIN -- check group name and role name
93 -- check group name
94
95 counter :=0;
96 FOR group_rec IN c_get_group_id( c_group_name => p_group_name )
97 LOOP
98 counter := counter +1;
99 x_group_id := group_rec.group_id; -- group_id assigned
100
101 IF counter=2 THEN
102 x_status := 'E';
103 fnd_message.set_name ('JTF', 'JTF_TTY_NON_UNIQUE_GROUP_NAME');
104 x_error_code := 1;
105 RETURN;
106 END IF;
107 END LOOP;
108
109 IF counter=0 THEN
110 RAISE NO_DATA_FOUND;
111 END IF;
112
113 -- check role name
114 counter :=0;
115 FOR role_rec IN c_get_role_code( c_role_name => p_role_name )
116 LOOP
117 counter := counter +1;
118 x_role_code := role_rec.role_code; -- role_code assigned
119 IF counter=2 THEN
120 x_status := 'E';
121 fnd_message.set_name ('JTF', 'JTF_TTY_NON_UNIQUE_ROLE_NAME');
122 x_error_code := 1;
123 RETURN;
124 END IF;
125 END LOOP;
126
127 IF counter=0 THEN
128 RAISE NO_DATA_FOUND;
129 END IF;
130
131
132 EXCEPTION
133 WHEN NO_DATA_FOUND THEN
134 x_status := 'E';
135 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
136 x_error_code := 1;
137 RETURN;
138
139 END; -- of check group name and role name
140
141 --- check resource, group and role combination
142 BEGIN
143 counter :=0;
144 FOR res_rec IN c_get_resource_id( c_resource_name => p_resource_name )
145 LOOP
146 l_res_tbl.EXTEND;
147 counter := counter + 1;
148 l_res_tbl(counter) := res_rec.resource_id;
149 END LOOP;
150
151 IF counter = 0 THEN --no resource by this name
152 RAISE NO_DATA_FOUND;
153 ELSE
154 IF p_alignment_id is null THEN /* for NA */
155 BEGIN /* xxx */
156
157 IF (l_res_tbl IS NOT NULL) AND ( l_res_tbl.COUNT > 0 ) THEN
158 l_num_valid_rsc_id := 0;
159 FOR i IN 1 .. l_res_tbl.COUNT
160 LOOP
161 BEGIN
162 SELECT 'VALID' INTO l_select
163 FROM jtf_tty_terr_grp_accts tga,
164 jtf_tty_named_acct_rsc nar
165 WHERE nar.terr_group_account_id = tga.terr_group_account_id
166 AND nar.rsc_role_code = X_ROLE_CODE
167 AND nar.resource_id = l_res_tbl(i)
168 AND nar.rsc_group_id = X_GROUP_ID
169 AND tga.named_account_id = P_NAMED_ACCOUNT_ID
170 AND tga.terr_group_id <>P_terr_group_id
171 AND rownum < 2;
172
173 x_status := 'I'; -- it is in other TG, return with Ignore
174 RETURN;
175
176 EXCEPTION -- go on
177 WHEN NO_DATA_FOUND THEN NULL;
178 END;
179
180 BEGIN
181 SELECT 'VALID'
182 INTO l_select
183 FROM jtf_rs_group_members mem,
184 jtf_rs_roles_b rol,
185 jtf_rs_role_relations rlt
186 WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
187 AND rlt.delete_flag = 'N'
188 AND sysdate >= rlt.start_date_active
189 AND ( rlt.end_date_active is null
190 OR
191 sysdate <= rlt.end_date_active
192 )
193 AND rlt.role_id = rol.role_id
194 AND rol.role_code = x_role_code
195 AND rlt.role_resource_id = mem.group_member_id
196 AND mem.delete_flag = 'N'
197 AND mem.group_id = x_group_id
198 AND mem.resource_id = l_res_tbl(i);
199
200 x_resource_id := l_res_tbl(i);
201 l_num_valid_rsc_id := l_num_valid_rsc_id + 1;
202
203 EXCEPTION
204 WHEN NO_DATA_FOUND THEN NULL;
205 WHEN TOO_MANY_ROWS THEN RAISE TOO_MANY_ROWS; -- not common error.
206 WHEN OTHERS THEN
207 x_status := 'E';
208 x_error_code := 4;
209 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
210 END;
211 END LOOP;
212
213 IF l_num_valid_rsc_id > 1 THEN
214 RAISE TOO_MANY_ROWS; -- duplicate combination, like two Lisa in the same resource group, same role
215 ELSIF l_num_valid_rsc_id =0 THEN
216 RAISE NO_DATA_FOUND; -- the reps(by that name) are valid but not in the resource group with the role
217 END IF;
218
219 END IF; -- l_res_tbl > 0
220
221 EXCEPTION
222 WHEN NO_DATA_FOUND THEN
223 x_status := 'E';
224 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
225 x_error_code := 1;
226 RETURN;
227 WHEN TOO_MANY_ROWS THEN
228 x_status := 'E';
229 fnd_message.set_name ('JTF', 'JTF_TTY_NON_UNIQUE_SALES_DATA');
230 x_error_code := 1;
231 RETURN;
232 WHEN OTHERS THEN
233 x_status := 'E';
234 x_error_code := 4;
235 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
236 END; /* of xxx */
237
238 -- check the role code
239 BEGIN
240 SELECT 'Y'
241 INTO l_select
242 FROM jtf_tty_terr_grp_roles
243 WHERE terr_group_id=P_terr_group_id
244 AND role_code = X_ROLE_CODE;
245
246 EXCEPTION
247 WHEN NO_DATA_FOUND THEN
248 x_status := 'E';
249 x_error_code := 2;
250 fnd_message.set_name ('JTF', 'JTF_TTY_ROLE_NOT_IN_TG');
251 RETURN;
252 END;
253
254
255
256 ELSE -- for alignment
257 BEGIN /*yyy*/
258
259 IF (l_res_tbl IS NOT NULL) AND ( l_res_tbl.COUNT > 0 ) THEN
260 l_num_valid_rsc_id := 0;
261 FOR i IN 1 .. l_res_tbl.COUNT
262 LOOP
263 BEGIN
264
265 -- where clauses for alignment is validating resource as a immediate direct of
266 -- territory group owner rather than alignment owner
267 SELECT 'VALID'
268 INTO l_select
269 FROM JTF_TTY_MY_DIRECTS_V
270 WHERE current_user_id = l_user_id
271 AND resource_id = l_res_tbl(i)
272 AND group_id = X_GROUP_ID
273 AND role_code = X_ROLE_CODE;
274
275 x_resource_id := l_res_tbl(i);
276 l_num_valid_rsc_id := l_num_valid_rsc_id + 1;
277
278 EXCEPTION
279 WHEN NO_DATA_FOUND THEN NULL;
280 WHEN TOO_MANY_ROWS THEN RAISE TOO_MANY_ROWS; -- not common error.
281 WHEN OTHERS THEN
282 x_status := 'E';
283 x_error_code := 4;
284 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
285 END;
286 END LOOP;
287
288 IF l_num_valid_rsc_id > 1 THEN
289 RAISE TOO_MANY_ROWS; -- duplicate combination, like two Lisa in the same resource group, same role
290 ELSIF l_num_valid_rsc_id =0 THEN
291 RAISE NO_DATA_FOUND; -- the reps(by that name) are valid but not in the resource group with the role
292 END IF;
293 END IF; --l_res_tbl.COUNT > 0
294
295 EXCEPTION
296 WHEN NO_DATA_FOUND THEN
297 x_status := 'E';
301 WHEN TOO_MANY_ROWS THEN
298 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
299 x_error_code := 1;
300 RETURN;
302 x_status := 'E';
303 fnd_message.set_name ('JTF', 'JTF_TTY_NON_UNIQUE_SALES_DATA');
304 x_error_code := 1;
305 RETURN;
306 WHEN OTHERS THEN
307 x_status := 'E';
308 x_error_code := 4;
309 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
310
311 END; /* of yyy */
312
313
314 END IF; /* align id */
315 END IF; -- count
316
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 x_status := 'E';
320 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
321 x_error_code := 1;
322 RETURN;
323
324 END;
325
326 /* old alignment code
327 ELSE -- for alignment
328 BEGIN
329 -- where clauses for alignment is validating resource as a immediate direct of
330 -- territory group owner rather than alignment owner
331 SELECT resource_id, group_id, role_code
332 INTO x_RESOURCE_id, x_group_id, x_role_code
333 FROM JTF_TTY_MY_DIRECTS_V
334 WHERE current_user_id = l_user_id
335 AND upper(resource_name) = upper(P_RESOURCE_NAME)
336 AND upper(group_name) = upper(P_GROUP_NAME)
337 AND upper(role_name) = upper(P_ROLE_NAME)
338 AND rownum<2;
339
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 x_status := 'E';
343 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
344 x_error_code := 1;
345 RETURN;
346 END;
347 END IF; --p_alignment_id is null */
348
349
350 ELSE
351 x_status := 'E';
352 x_error_code := 3;
353 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_MANDATORY');
354 RETURN;
355 END IF;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 x_status := 'E';
360 x_error_code := 4;
361 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
362
363 END validate_resource;
364
365
366 /* Procedure which checks whether the user can add the given
367 sales person. This is same as 11.5.9 */
368
369 PROCEDURE CHECK_VALID_RESOURCE_ADD (
370 P_RESOURCE_id in number
371 , P_GROUP_ID IN NUMBER
372 , P_ROLE_CODE in varchar2
373 , P_user_id in number
374 , P_TG_ID in number
375 , x_error_code out NOCOPY number
376 , x_status out NOCOPY varchar2) is
377
378 l_select varchar2(100);
379
380 BEGIN
381 x_status := 'S';
382 /* check salesperson for the current TG */
383 BEGIN
384 SELECT 'VALID'
385 INTO l_select
386 FROM jtf_tty_srch_my_resources_v /*jtf_tty_my_resources_v*/ grv,
387 jtf_tty_terr_grp_owners jto
388 WHERE EXISTS
389 ( SELECT NULL
390 FROM JTF_RS_GROUPS_DENORM /*jtf_rs_grp_denorm_vl*/ grpd
391 WHERE /* part of Salesgroup hierarchy of Territory Group owner */
392 grpd.parent_group_id = JTO.rsc_group_id
393 /* groups I (logged-in user) am 'member' of */
394 AND grpd.group_id = GRV.group_id
395 )
396 AND jto.terr_group_id = P_TG_ID
397 AND grv.ROLE_CODE = P_ROLE_CODE
398 AND grv.GROUP_ID = P_GROUP_ID
399 AND grv.resource_id = P_RESOURCE_ID
400 AND grv.CURRENT_USER_ID = P_USER_ID
401 AND ROWNUM < 2;
402
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 x_status := 'E';
406 x_error_code := 1;
407 fnd_message.set_name ('JTF', 'JTF_TTY_SALES_DATA_NOT_VALID');
408 RETURN;
409
410 WHEN OTHERS THEN
411 x_status := 'E';
412 x_error_code := 4;
413 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
414 RETURN;
415 END;
416
417
418 END CHECK_VALID_RESOURCE_ADD;
419
420 /* Procedure which checks whether the user can remove the given
421 sales person. Same as 11.5.9*/
422
423
424 PROCEDURE CHECK_VALID_RESOURCE_REMOVE (
425 P_RESOURCE_id in number
426 , P_GROUP_ID IN NUMBER
427 , P_ROLE_CODE in varchar2
428 , P_USER_ID in number
429 , P_TG_ID IN NUMBER
430 , x_error_code out NOCOPY number
431 , x_status out NOCOPY varchar2) is
432
433
434 l_select varchar2(100);
435
436 begin
437
438 x_status := 'S';
439
440 SELECT 'VALID'
441 INTO l_select
442 FROM (
443 /* Salesperson is a member of one of his mgr's group OR
444 ** is a manager of a child group of one of his mgr's groups */
445 SELECT dir.resource_id, dir.resource_name, dir.user_id dir_user_id
449 , rol.role_code, rol.role_name
446 , MY_GRPS.group_id
447 , MY_GRPS.parent_group_id
448 , MY_GRPS.CURRENT_USER_ID
450 , MY_GRPS.current_user_role_code
451 , MY_GRPS.current_user_rsc_id
452 FROM jtf_rs_roles_vl rol
453 , jtf_rs_role_relations rlt
454 , jtf_rs_group_members grpmemo
455 , jtf_rs_resource_extns_vl dir
456
457 , ( /* MY_GRPS INLINE VIEW */
458 /* Groups logged-in user manages/administrates */
459 SELECT /*+ NO_MERGE */
460 dv.group_id
461 , dv.parent_group_id
462 , sgh.resource_id
463 , mrsc.user_id CURRENT_USER_ID
464 , mrsc.resource_id current_user_rsc_id
465 , usg.USAGE
466 , rol.role_code current_user_role_code
467 FROM jtf_rs_group_usages usg
468 , jtf_rs_groups_denorm dv
469 , jtf_rs_rep_managers sgh
470 , jtf_rs_resource_extns mrsc
471 , jtf_rs_roles_b rol
472 , jtf_rs_role_relations rlt
473 WHERE usg.usage = 'SALES'
474 AND usg.group_id = dv.group_id
475 AND rlt.role_id = rol.role_id
476 AND rlt.role_relate_id = sgh.par_role_relate_id
477 AND dv.parent_group_id = sgh.group_id
478 AND sgh.resource_id = sgh.parent_resource_id
479 AND ( sgh.hierarchy_type IN ('MGR_TO_MGR')
480 OR rol.role_code = FND_PROFILE.VALUE('JTF_TTY_NA_PROXY_USER_ROLE')
481 )
482 AND mrsc.resource_id = sgh.resource_id ) MY_GRPS
483 WHERE ( rol.member_flag = 'Y' OR rol.manager_flag = 'Y' )
484 AND rlt.role_id = rol.role_id
485 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
486 AND rlt.role_resource_id = grpmemo.group_member_id
487 AND grpmemo.resource_id = dir.resource_id
488 AND grpmemo.group_id = MY_GRPS.group_id
489
490 UNION ALL
491
492 /* Base Salesperson logged in, i.e., user is not
493 ** a manager of a salesgroup */
494 SELECT dir.resource_id
495 , dir.resource_name
496 , dir.user_id dir_user_id
497 , SALES_GRPS.group_id
498 , SALES_GRPS.parent_group_id
499 , dir.user_id CURRENT_USER_ID
500 , rol.role_code, rol.role_name
501 , rol.role_code current_user_role_code
502 , dir.resource_id current_user_rsc_id
503 FROM jtf_rs_roles_vl rol
504 , jtf_rs_role_relations rlt
505 , jtf_rs_group_members grpmemo
506 , jtf_rs_resource_extns_vl dir
507 , ( /* SALES GROUPS INLINE VIEW */
508 SELECT dv.group_id
509 , dv.group_id PARENT_GROUP_ID
510 , NULL PARENT_GROUP_NAME
511 FROM jtf_rs_group_usages usg
512 , jtf_rs_groups_b dv
513 WHERE usg.usage = 'SALES'
514 AND usg.group_id = dv.group_id
515 ) SALES_GRPS
516 WHERE rol.member_flag = 'Y'
517 AND rlt.role_id = rol.role_id
518 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
519 AND rlt.role_resource_id = grpmemo.group_member_id
520 AND grpmemo.resource_id = dir.resource_id
521 AND grpmemo.group_id = SALES_GRPS.group_id
522 AND NOT EXISTS (
523 /* Rep is not a manager */
524 SELECT NULL
525 FROM jtf_rs_rep_managers mgr
526 WHERE mgr.parent_resource_id = dir.resource_id
527 AND mgr.parent_resource_id = mgr.resource_id
528 AND mgr.group_id = grpmemo.group_id
529 AND mgr.hierarchy_type = 'MGR_TO_MGR'
530 )
531 ) MY_REPS
532 , jtf_tty_terr_grp_owners tgo
533 , jtf_tty_terr_grp_roles tgr
534 WHERE EXISTS (
535 SELECT NULL
536 FROM JTF_RS_GROUPS_DENORM /*jtf_rs_grp_denorm_vl*/ grpd
537 WHERE grpd.parent_group_id = TGO.rsc_group_id
538 AND grpd.group_id = MY_REPS.group_id )
539 AND tgr.role_code = MY_REPS.role_code
540 AND tgr.terr_group_id = tgo.terr_group_id
541 AND tgo.terr_group_id = P_TG_ID
542 AND MY_REPS.CURRENT_USER_ID = P_USER_ID
543 AND MY_REPS.role_code = P_ROLE_CODE
544 AND MY_REPS.group_id = P_GROUP_ID
545 AND MY_REPS.resource_id = P_RESOURCE_ID
546 AND ROWNUM < 2;
547
548
549 exception
550 when no_data_found then
551 x_status := 'E'; -- no error message necessary;
552 RETURN;
553 -- x_error_code := 5;
554 -- fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_SALES_REC');
555
556 when others then
557 x_status := 'E';
558 x_error_code := 4;
559 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
560
564
561 end CHECK_VALID_RESOURCE_REMOVE;
562
563
565 PROCEDURE POPULATE_SALESTEAM_ALIGNMENT (
566 P_TERRITORY_GROUP in varchar2,
567 P_RESOURCE1_NAME in varchar2,
568 P_GROUP1_NAME in varchar2,
569 P_ROLE1_NAME in varchar2,
570 P_RESOURCE2_NAME in varchar2,
571 P_GROUP2_NAME in varchar2,
572 P_ROLE2_NAME in varchar2,
573 P_RESOURCE3_NAME in varchar2,
574 P_GROUP3_NAME in varchar2,
575 P_ROLE3_NAME in varchar2,
576 P_RESOURCE4_NAME in varchar2,
577 P_GROUP4_NAME in varchar2,
578 P_ROLE4_NAME in varchar2,
579 P_RESOURCE5_NAME in varchar2,
580 P_GROUP5_NAME in varchar2,
581 P_ROLE5_NAME in varchar2,
582 P_RESOURCE6_NAME in varchar2,
583 P_GROUP6_NAME in varchar2,
584 P_ROLE6_NAME in varchar2,
585 P_RESOURCE7_NAME in varchar2,
586 P_GROUP7_NAME in varchar2,
587 P_ROLE7_NAME in varchar2,
588 P_RESOURCE8_NAME in varchar2,
589 P_GROUP8_NAME in varchar2,
590 P_ROLE8_NAME in varchar2,
591 P_RESOURCE9_NAME in varchar2,
592 P_GROUP9_NAME in varchar2,
593 P_ROLE9_NAME in varchar2,
594 P_RESOURCE10_NAME in varchar2,
595 P_GROUP10_NAME in varchar2,
596 P_ROLE10_NAME in varchar2,
597 P_RESOURCE11_NAME in varchar2,
598 P_GROUP11_NAME in varchar2,
599 P_ROLE11_NAME in varchar2,
600 P_RESOURCE12_NAME in varchar2,
601 P_GROUP12_NAME in varchar2,
602 P_ROLE12_NAME in varchar2,
603 P_RESOURCE13_NAME in varchar2,
604 P_GROUP13_NAME in varchar2,
605 P_ROLE13_NAME in varchar2,
606 P_RESOURCE14_NAME in varchar2,
607 P_GROUP14_NAME in varchar2,
608 P_ROLE14_NAME in varchar2,
609 P_RESOURCE15_NAME in varchar2,
610 P_GROUP15_NAME in varchar2,
611 P_ROLE15_NAME in varchar2,
612 P_RESOURCE16_NAME in varchar2,
613 P_GROUP16_NAME in varchar2,
614 P_ROLE16_NAME in varchar2,
615 P_RESOURCE17_NAME in varchar2,
616 P_GROUP17_NAME in varchar2,
617 P_ROLE17_NAME in varchar2,
618 P_RESOURCE18_NAME in varchar2,
619 P_GROUP18_NAME in varchar2,
620 P_ROLE18_NAME in varchar2,
621 P_RESOURCE19_NAME in varchar2,
622 P_GROUP19_NAME in varchar2,
623 P_ROLE19_NAME in varchar2,
624 P_RESOURCE20_NAME in varchar2,
625 P_GROUP20_NAME in varchar2,
626 P_ROLE20_NAME in varchar2,
627 P_RESOURCE21_NAME in varchar2,
628 P_GROUP21_NAME in varchar2,
629 P_ROLE21_NAME in varchar2,
630 P_RESOURCE22_NAME in varchar2,
631 P_GROUP22_NAME in varchar2,
632 P_ROLE22_NAME in varchar2,
633 P_RESOURCE23_NAME in varchar2,
634 P_GROUP23_NAME in varchar2,
635 P_ROLE23_NAME in varchar2,
636 P_RESOURCE24_NAME in varchar2,
637 P_GROUP24_NAME in varchar2,
638 P_ROLE24_NAME in varchar2,
639 P_RESOURCE25_NAME in varchar2,
640 P_GROUP25_NAME in varchar2,
641 P_ROLE25_NAME in varchar2,
642 P_RESOURCE26_NAME in varchar2,
643 P_GROUP26_NAME in varchar2,
644 P_ROLE26_NAME in varchar2,
645 P_RESOURCE27_NAME in varchar2,
646 P_GROUP27_NAME in varchar2,
647 P_ROLE27_NAME in varchar2,
648 P_RESOURCE28_NAME in varchar2,
649 P_GROUP28_NAME in varchar2,
650 P_ROLE28_NAME in varchar2,
651 P_RESOURCE29_NAME in varchar2,
652 P_GROUP29_NAME in varchar2,
653 P_ROLE29_NAME in varchar2,
654 P_RESOURCE30_NAME in varchar2,
655 P_GROUP30_NAME in varchar2,
656 P_ROLE30_NAME in varchar2,
657 P_TERR_GRP_ACCT_ID in varchar2,
658 P_ALIGNMENT_FLAG in varchar2,
659 P_ALIGNMENT_ID in varchar2) is
660
661 CURSOR c_res_list(l_terr_grp_acct_id number)
662 IS select RESOURCE_ID, RSC_GROUP_ID , RSC_ROLE_CODE
663 from jtf_tty_named_acct_rsc
664 where TERR_GROUP_ACCOUNT_ID = l_terr_grp_acct_id;
665
666 CURSOR c_res_list_for_align(c_terr_grp_acct_id number, c_user_id number)
667 IS SELECT narsc.resource_id resource_id,
668 narsc.rsc_group_id rsc_group_id,
669 narsc.rsc_role_code rsc_role_code
670 FROM jtf_tty_named_acct_rsc narsc
671 WHERE narsc.terr_group_account_id = c_terr_grp_acct_id
672 AND (narsc.resource_id, narsc.rsc_group_id, narsc.rsc_role_code ) IN
673 ( select /*+ NO_MERGE */ mydir.resource_id, mydir.group_id, mydir.role_code
674 from jtf_tty_srch_my_resources_v mydir
675 where mydir.current_user_id = c_user_id );
676
677 CURSOR c_get_user_id (l_align_id number )
678 IS SELECT rsc.user_id
679 FROM jtf_rs_resource_extns rsc,
680 jtf_tty_alignments al
681 WHERE rsc.resource_id = al.owner_resource_id
682 AND al.owner_resource_type = 'RS_EMPLOYEE'
683 AND al.alignment_id = l_align_id;
684
685 CURSOR c_align_res_list(l_terr_grp_acct_id number)
686 IS select pt.RESOURCE_ID, pt.RSC_GROUP_ID , pt.RSC_ROLE_CODE
687 from jtf_tty_align_pterr pt,
688 jtf_tty_pterr_accts pa,
689 jtf_tty_align_accts aa
690 where pt.align_proposed_terr_id = pa.align_proposed_terr_id
691 and pa.align_acct_id = aa.align_acct_id
695 CURSOR c_check_direct_res( c_resource_id NUMBER, c_group_id NUMBER,
692 and aa.terr_group_account_id = l_terr_grp_acct_id
693 and aa.alignment_id = p_alignment_id;
694
696 c_role_code VARCHAR2, c_user_id NUMBER )
697 IS SELECT 'Y'
698 FROM jtf_tty_my_directs_v
699 WHERE current_user_id = c_user_id
700 AND resource_id = c_resource_id
701 AND group_id = c_group_id
702 AND role_code = c_role_code;
703
704 CURSOR c_get_direct_res( c_resource_id NUMBER, c_group_id NUMBER, c_user_id NUMBER )
705 IS SELECT mydir.resource_id resource_id,
706 mydir.group_id group_id,
707 mydir.role_code role_code
708 FROM jtf_tty_my_directs_v mydir
709 WHERE mydir.current_user_id = c_user_id
710 AND mydir.dir_user_id <> c_user_id
711 AND ( mydir.resource_id, mydir.group_id, mydir.role_code) IN
712 ( SELECT /*+ NO_MERGE */
713 repmgr.parent_resource_id,
714 grpmem.group_id,
715 rol.role_code
716 FROM jtf_rs_rep_managers repmgr,
717 jtf_rs_role_relations rlt,
718 jtf_rs_roles_b rol,
719 jtf_rs_group_members grpmem
720 WHERE repmgr.resource_id = c_resource_id
721 AND repmgr.group_id = c_group_id
722 AND repmgr.par_role_relate_id = rlt.role_relate_id
723 AND SYSDATE BETWEEN repmgr.start_date_active
724 AND NVL(repmgr.end_date_active, SYSDATE+1)
725 AND rlt.role_id = rol.role_id
726 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
727 AND rlt.delete_flag = 'N'
728 AND SYSDATE BETWEEN rlt.start_date_active
729 AND NVL(rlt.end_date_active, SYSDATE+1)
730 AND rlt.role_resource_id = grpmem.group_member_id
731 AND grpmem.delete_flag = 'N'
732 );
733
734 x_msg_count number;
735 x_msg_data varchar2(2000);
736 x_return_status varchar(3);
737 l_index number:=0;
738 l_error_count number:=0;
739 l_terr_grp_acct_id number;
740 l_terr_group_id number;
741 l_resource_name varchar2(360);
742 l_resource_id number;
743 l_group varchar2(60);
744 l_role_code varchar2(20);
745 l_group_id number;
746 l_role varchar2(60);
747 i integer:=0;
748 errbuf varchar2(2000);
749 retcode number;
750 X_RESOURCE_id number;
751 x_group_id number;
752 x_role_code varchar2(30);
753 x_error_code varchar2(2);
754 x_status varchar2(3);
755 l_named_account_id number;
756 l_atleast_one_rep boolean := FALSE;
757 l_error varchar2(30);
758 l_imported_on DATE := null;
759
760 l_added_rscs_tbl JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE;
761 l_add_rscs_tbl JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE;
762 l_directs_tbl JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE;
763 l_removed_rscs_tbl JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE;
764
765 l_affected_parties_tbl JTF_TTY_NACCT_SALES_PUB.AFFECTED_PARTY_TBL_TYPE;
766 l_assign_flag varchar2(1);
767 l_whether_exist varchar2(1);
768 l_user_id NUMBER;
769
770 l_add_count NUMBER := 0;
771 l_delete_count NUMBER := 0;
772 l_found varchar2(10);
773 l_res_found BOOLEAN := FALSE;
774
775 l_result varchar2(1);
776 l_direct_flag VARCHAR2(1);
777
778 begin
779
780
781 l_result :='N';
782 l_direct_flag := 'N' ;
783
784 -- delete from tmp;
785 -- insert into tmp values('1. start','');
786 l_user_id := fnd_global.user_id;
787
788 --insert into tmp values('ali',p_alignment_id); commit;
789 l_error := 'JTF_TTY_ERROR';
790
791 --insert into tmp values('P_ALIGNMENT_ID',P_ALIGNMENT_ID); commit;
792 l_terr_grp_acct_id := P_TERR_GRP_ACCT_ID;
793
794
795 BEGIN
796 IF P_ALIGNMENT_ID IS NOT NULL THEN
797 SELECT 'VALID' INTO l_found
798 FROM JTF_TTY_ALIGNMENTS
799 WHERE alignment_id = P_ALIGNMENT_ID
800 AND l_user_id = created_by;
801 END IF;
802
803 EXCEPTION
804 WHEN NO_DATA_FOUND THEN
805 fnd_message.set_name ('JTF', 'JTF_TTY_DO_NOT_OWN_ALIGN');
806 RETURN;
807
808 END;
809
810 begin
811
812 select terr_group_id
813 into l_terr_group_id
814 from jtf_tty_terr_groups
815 where trim(terr_group_name) =P_TERRITORY_GROUP; -- deal with the trailing blank
816
817 /* a change of l_terr_grp_acct_id will be caught here */
818 SELECT named_account_id
819 INTO l_named_account_id
820 FROM jtf_tty_terr_grp_accts
821 WHERE terr_group_account_id = l_terr_grp_acct_id;
822
823 exception
824 when others then
825 fnd_message.set_name ('JTF', 'JTF_TTY_INVALID_TG');
826 return;
827
828
829 end;
830
831
832
833 begin
834
835 /* check if the salesperson(by user_id) is able to change the named account(by tgid).
839 from jtf_tty_named_acct_rsc narsc,
836 The query below was modified to consider accounts belonging to inactive reps also*/
837 /*
838 select narsc.resource_id INTO l_resource_id
840 jtf_tty_srch_my_resources_v repdn -- jtf_tty_my_resources_v
841 where narsc.resource_id = repdn.resource_id
842 and narsc.rsc_group_id = repdn.group_id
843 and repdn.current_user_id = l_user_id
844 and narsc.terr_group_account_id = l_terr_grp_acct_id
845 and rownum < 2;
846 */
847 SELECT narsc.resource_id
848 INTO l_resource_id
849 FROM jtf_tty_named_acct_rsc narsc
850 WHERE narsc.terr_group_account_id = l_terr_grp_acct_id
851 AND EXISTS (
852 SELECT 'Y'
853 FROM jtf_rs_group_members grpmemo ,
854 jtf_rs_resource_extns dir ,
855 ( SELECT /*+ NO_MERGE */ dv.group_id ,
856 mrsc.user_id CURRENT_USER_ID
857 FROM jtf_rs_group_usages usg ,
858 jtf_rs_groups_denorm dv ,
859 jtf_rs_rep_managers sgh ,
860 jtf_rs_resource_extns mrsc ,
861 jtf_rs_roles_b rol ,
862 jtf_rs_role_relations rlt
863 WHERE usg.usage = 'SALES'
864 AND usg.group_id = dv.group_id
865 AND rlt.role_id = rol.role_id
866 AND rlt.role_relate_id = sgh.par_role_relate_id
867 AND dv.parent_group_id = sgh.group_id
868 AND sgh.resource_id = sgh.parent_resource_id
869 AND (sgh.hierarchy_type IN ('MGR_TO_MGR')
870 OR rol.role_code = FND_PROFILE.VALUE('JTF_TTY_NA_PROXY_USER_ROLE'))
871 AND mrsc.resource_id = sgh.resource_id
872 AND mrsc.user_id = l_user_id
873 ) MY_GRPS
874 WHERE grpmemo.resource_id = dir.resource_id
875 AND grpmemo.group_id = MY_GRPS.group_id
876 AND grpmemo.resource_id = narsc.resource_id
877 AND grpmemo.group_id = narsc.rsc_group_id
878 UNION ALL
879 SELECT 'Y'
880 FROM jtf_rs_group_members grpmemo ,
881 jtf_rs_resource_extns dir ,
882 jtf_rs_group_usages usg
883 WHERE usg.usage = 'SALES'
884 AND grpmemo.resource_id = dir.resource_id
885 AND grpmemo.group_id = usg.group_id
886 AND dir.user_id = l_user_id
887 AND grpmemo.resource_id = narsc.resource_id
888 AND grpmemo.group_id = narsc.rsc_group_id
889 )
890 and rownum < 2;
891
892
893
894 --dbms_output.put_line('passed initial validation');
895 l_added_rscs_tbl := JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE();
896 l_affected_parties_tbl := JTF_TTY_NACCT_SALES_PUB.AFFECTED_PARTY_TBL_TYPE();
897 l_affected_parties_tbl.extend;
898 l_affected_parties_tbl(1).terr_group_account_id := l_terr_grp_acct_id;
899
900 begin
901 if P_RESOURCE1_NAME is not null or P_GROUP1_NAME is not null or P_ROLE1_NAME is not null
902 then
903
904
905 validate_resource (
906 P_RESOURCE_NAME=>P_RESOURCE1_NAME ,
907 P_GROUP_NAME=>P_GROUP1_NAME ,
908 P_ROLE_NAME=>P_ROLE1_NAME ,
909 P_terr_group_id=>l_terr_group_id ,
910 P_named_account_id=>l_named_account_id,
911 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
912 p_alignment_id => P_ALIGNMENT_ID,
913 X_RESOURCE_id=>X_RESOURCE_id ,
914 x_group_id=>x_group_id ,
915 x_role_code=>x_role_code ,
916 x_error_code =>x_error_code,
917 x_status=>x_status );
918
919 if x_status = 'S' then
920
921
922 l_atleast_one_rep := TRUE;
923
924 l_added_rscs_tbl.extend;
925
926 i:=i+1;
927 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
928 l_added_rscs_tbl(i).group_id := x_group_id;
929 l_added_rscs_tbl(i).role_code := x_role_code;
930 l_added_rscs_tbl(i).attribute1 := 'N';
931 l_added_rscs_tbl(i).attribute2 := '1';
932 elsif x_status = 'I' then NULL;
933 else
934 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '1'); end if;
935 return;
936 end if;
937 end if;
938
939 if P_RESOURCE2_NAME is not null or P_GROUP2_NAME is not null or P_ROLE2_NAME is not null
940 then
941
942 validate_resource (
946 P_terr_group_id=>l_terr_group_id ,
943 P_RESOURCE_NAME=>P_RESOURCE2_NAME ,
944 P_GROUP_NAME=>P_GROUP2_NAME ,
945 P_ROLE_NAME=>P_ROLE2_NAME ,
947 P_named_account_id=>l_named_account_id,
948 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
949 p_alignment_id => P_ALIGNMENT_ID,
950 X_RESOURCE_id=>X_RESOURCE_id ,
951 x_group_id=>x_group_id ,
952 x_role_code=>x_role_code ,
953 x_error_code =>x_error_code,
954 x_status=>x_status );
955
956
957
958 if x_status = 'S' then
959
960 -- insert into sb values('Inside the success status '|| to_char(i));
961 l_atleast_one_rep := TRUE;
962 l_added_rscs_tbl.extend;
963
964 i:=i+1;
965 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
966 l_added_rscs_tbl(i).group_id := x_group_id;
967 l_added_rscs_tbl(i).role_code := x_role_code;
968 l_added_rscs_tbl(i).attribute1 := 'N';
969 l_added_rscs_tbl(i).attribute2 := '2';
970 elsif x_status = 'I' then NULL;
971 else
972 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '2'); end if;
973 return;
974 end if;
975 end if;
976
977 if P_RESOURCE3_NAME is not null or P_GROUP3_NAME is not null or P_ROLE3_NAME is not null
978 then
979
980 validate_resource (
981 P_RESOURCE_NAME=>P_RESOURCE3_NAME ,
982 P_GROUP_NAME=>P_GROUP3_NAME ,
983 P_ROLE_NAME=>P_ROLE3_NAME ,
984 P_terr_group_id=>l_terr_group_id ,
985 P_named_account_id=>l_named_account_id,
986 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
987 p_alignment_id => P_ALIGNMENT_ID,
988 X_RESOURCE_id=>X_RESOURCE_id ,
989 x_group_id=>x_group_id ,
990 x_role_code=>x_role_code ,
991 x_error_code =>x_error_code,
992 x_status=>x_status );
993
994
995
996 if x_status = 'S' then
997
998 -- insert into sb values('Inside the success status '|| to_char(i));
999 l_atleast_one_rep := TRUE;
1000 l_added_rscs_tbl.extend;
1001
1002 i:=i+1;
1003 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1004 l_added_rscs_tbl(i).group_id := x_group_id;
1005 l_added_rscs_tbl(i).role_code := x_role_code;
1006 l_added_rscs_tbl(i).attribute1 := 'N';
1007 l_added_rscs_tbl(i).attribute2 := '3';
1008 elsif x_status = 'I' then NULL;
1009 else
1010 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '3'); end if;
1011 return;
1012 end if;
1013 end if;
1014
1015 if P_RESOURCE4_NAME is not null or P_GROUP4_NAME is not null or P_ROLE4_NAME is not null
1016 then
1017
1018 validate_resource (
1019 P_RESOURCE_NAME=>P_RESOURCE4_NAME ,
1020 P_GROUP_NAME=>P_GROUP4_NAME ,
1021 P_ROLE_NAME=>P_ROLE4_NAME ,
1022 P_terr_group_id=>l_terr_group_id ,
1023 P_named_account_id=>l_named_account_id,
1024 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1025 p_alignment_id => P_ALIGNMENT_ID,
1026 X_RESOURCE_id=>X_RESOURCE_id ,
1027 x_group_id=>x_group_id ,
1028 x_role_code=>x_role_code ,
1029 x_error_code =>x_error_code,
1030 x_status=>x_status );
1031
1032 -- insert into sb values('Return Status ' || x_status);
1033
1034 if x_status = 'S' then
1035
1036 -- insert into sb values('Inside the success status '|| to_char(i));
1037 l_atleast_one_rep := TRUE;
1038 l_added_rscs_tbl.extend;
1039
1040 i:=i+1;
1041 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1042 l_added_rscs_tbl(i).group_id := x_group_id;
1043 l_added_rscs_tbl(i).role_code := x_role_code;
1044 l_added_rscs_tbl(i).attribute1 := 'N';
1045 l_added_rscs_tbl(i).attribute2 := '4';
1046 elsif x_status = 'I' then NULL;
1047 else
1048 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '4'); end if;
1049 return;
1050 end if;
1051 end if;
1052
1053 if P_RESOURCE5_NAME is not null or P_GROUP5_NAME is not null or P_ROLE5_NAME is not null
1054 then
1055
1056 validate_resource (
1057 P_RESOURCE_NAME=>P_RESOURCE5_NAME ,
1058 P_GROUP_NAME=>P_GROUP5_NAME ,
1059 P_ROLE_NAME=>P_ROLE5_NAME ,
1060 P_terr_group_id=>l_terr_group_id ,
1061 P_named_account_id=>l_named_account_id,
1065 x_group_id=>x_group_id ,
1062 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1063 p_alignment_id => P_ALIGNMENT_ID,
1064 X_RESOURCE_id=>X_RESOURCE_id ,
1066 x_role_code=>x_role_code ,
1067 x_error_code =>x_error_code,
1068 x_status=>x_status );
1069
1070 -- insert into sb values('Return Status ' || x_status);
1071
1072 if x_status = 'S' then
1073
1074 --insert into sb values('Inside the success status '|| to_char(i));
1075 l_atleast_one_rep := TRUE;
1076 l_added_rscs_tbl.extend;
1077
1078 i:=i+1;
1079 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1080 l_added_rscs_tbl(i).group_id := x_group_id;
1081 l_added_rscs_tbl(i).role_code := x_role_code;
1082 l_added_rscs_tbl(i).attribute1 := 'N';
1083 l_added_rscs_tbl(i).attribute2 := '5';
1084 elsif x_status = 'I' then NULL;
1085 else
1086 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '5'); end if;
1087 return;
1088 end if;
1089 end if;
1090
1091 if P_RESOURCE6_NAME is not null or P_GROUP6_NAME is not null or P_ROLE6_NAME is not null
1092 then
1093
1094 validate_resource (
1095 P_RESOURCE_NAME=>P_RESOURCE6_NAME ,
1096 P_GROUP_NAME=>P_GROUP6_NAME ,
1097 P_ROLE_NAME=>P_ROLE6_NAME ,
1098 P_terr_group_id=>l_terr_group_id ,
1099 P_named_account_id=>l_named_account_id,
1100 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1101 p_alignment_id => P_ALIGNMENT_ID,
1102 X_RESOURCE_id=>X_RESOURCE_id ,
1103 x_group_id=>x_group_id ,
1104 x_role_code=>x_role_code ,
1105 x_error_code =>x_error_code,
1106 x_status=>x_status );
1107
1108
1109 if x_status = 'S' then
1110
1111 -- insert into sb values('Inside the success status '|| to_char(i));
1112 l_atleast_one_rep := TRUE;
1113 l_added_rscs_tbl.extend;
1114
1115 i:=i+1;
1116 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1117 l_added_rscs_tbl(i).group_id := x_group_id;
1118 l_added_rscs_tbl(i).role_code := x_role_code;
1119 l_added_rscs_tbl(i).attribute1 := 'N';
1120 l_added_rscs_tbl(i).attribute2 := '6';
1121 elsif x_status = 'I' then NULL;
1122 else
1123 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '6'); end if;
1124 return;
1125 end if;
1126 end if;
1127
1128 if P_RESOURCE7_NAME is not null or P_GROUP7_NAME is not null or P_ROLE7_NAME is not null
1129 then
1130
1131 validate_resource (
1132 P_RESOURCE_NAME=>P_RESOURCE7_NAME ,
1133 P_GROUP_NAME=>P_GROUP7_NAME ,
1134 P_ROLE_NAME=>P_ROLE7_NAME ,
1135 P_terr_group_id=>l_terr_group_id ,
1136 P_named_account_id=>l_named_account_id,
1137 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1138 p_alignment_id => P_ALIGNMENT_ID,
1139 X_RESOURCE_id=>X_RESOURCE_id ,
1140 x_group_id=>x_group_id ,
1141 x_role_code=>x_role_code ,
1142 x_error_code =>x_error_code,
1143 x_status=>x_status );
1144
1145
1146 if x_status = 'S' then
1147
1148 -- insert into sb values('Inside the success status '|| to_char(i));
1149 l_atleast_one_rep := TRUE;
1150 l_added_rscs_tbl.extend;
1151
1152 i:=i+1;
1153 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1154 l_added_rscs_tbl(i).group_id := x_group_id;
1155 l_added_rscs_tbl(i).role_code := x_role_code;
1156 l_added_rscs_tbl(i).attribute1 := 'N';
1157 l_added_rscs_tbl(i).attribute2 := '7';
1158 elsif x_status = 'I' then NULL;
1159 else
1160 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '7'); end if;
1161 return;
1162 end if;
1163 end if;
1164
1165 if P_RESOURCE8_NAME is not null or P_GROUP8_NAME is not null or P_ROLE8_NAME is not null
1166 then
1167
1168 validate_resource (
1169 P_RESOURCE_NAME=>P_RESOURCE8_NAME ,
1170 P_GROUP_NAME=>P_GROUP8_NAME ,
1171 P_ROLE_NAME=>P_ROLE8_NAME ,
1172 P_terr_group_id=>l_terr_group_id ,
1173 P_named_account_id=>l_named_account_id,
1174 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1175 p_alignment_id => P_ALIGNMENT_ID,
1176 X_RESOURCE_id=>X_RESOURCE_id ,
1177 x_group_id=>x_group_id ,
1178 x_role_code=>x_role_code ,
1179 x_error_code =>x_error_code,
1180 x_status=>x_status );
1184
1181
1182
1183 if x_status = 'S' then
1185 -- insert into sb values('Inside the success status '|| to_char(i));
1186 l_atleast_one_rep := TRUE;
1187 l_added_rscs_tbl.extend;
1188
1189 i:=i+1;
1190 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1191 l_added_rscs_tbl(i).group_id := x_group_id;
1192 l_added_rscs_tbl(i).role_code := x_role_code;
1193 l_added_rscs_tbl(i).attribute1 := 'N';
1194 l_added_rscs_tbl(i).attribute2 := '8';
1195 elsif x_status = 'I' then NULL;
1196 else
1197 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '8'); end if;
1198 return;
1199 end if;
1200 end if;
1201
1202 if P_RESOURCE9_NAME is not null or P_GROUP9_NAME is not null or P_ROLE9_NAME is not null
1203 then
1204
1205 validate_resource (
1206 P_RESOURCE_NAME=>P_RESOURCE9_NAME ,
1207 P_GROUP_NAME=>P_GROUP9_NAME ,
1208 P_ROLE_NAME=>P_ROLE9_NAME ,
1209 P_terr_group_id=>l_terr_group_id ,
1210 P_named_account_id=>l_named_account_id,
1211 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1212 p_alignment_id => P_ALIGNMENT_ID,
1213 X_RESOURCE_id=>X_RESOURCE_id ,
1214 x_group_id=>x_group_id ,
1215 x_role_code=>x_role_code ,
1216 x_error_code =>x_error_code,
1217 x_status=>x_status );
1218
1219
1220 if x_status = 'S' then
1221
1222 -- insert into sb values('Inside the success status '|| to_char(i));
1223 l_atleast_one_rep := TRUE;
1224 l_added_rscs_tbl.extend;
1225
1226 i:=i+1;
1227 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1228 l_added_rscs_tbl(i).group_id := x_group_id;
1229 l_added_rscs_tbl(i).role_code := x_role_code;
1230 l_added_rscs_tbl(i).attribute1 := 'N';
1231 l_added_rscs_tbl(i).attribute2 := '9';
1232 elsif x_status = 'I' then NULL;
1233 else
1234 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '9'); end if;
1235 return;
1236 end if;
1237 end if;
1238
1239 if P_RESOURCE10_NAME is not null or P_GROUP10_NAME is not null or P_ROLE10_NAME is not null
1240 then
1241
1242 validate_resource (
1243 P_RESOURCE_NAME=>P_RESOURCE10_NAME ,
1244 P_GROUP_NAME=>P_GROUP10_NAME ,
1245 P_ROLE_NAME=>P_ROLE10_NAME ,
1246 P_terr_group_id=>l_terr_group_id ,
1247 P_named_account_id=>l_named_account_id,
1248 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1249 p_alignment_id => P_ALIGNMENT_ID,
1250 X_RESOURCE_id=>X_RESOURCE_id ,
1251 x_group_id=>x_group_id ,
1252 x_role_code=>x_role_code ,
1253 x_error_code =>x_error_code,
1254 x_status=>x_status );
1255
1256
1257 if x_status = 'S' then
1258
1259 -- insert into sb values('Inside the success status '|| to_char(i));
1260 l_atleast_one_rep := TRUE;
1261 l_added_rscs_tbl.extend;
1262
1263 i:=i+1;
1264 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1265 l_added_rscs_tbl(i).group_id := x_group_id;
1266 l_added_rscs_tbl(i).role_code := x_role_code;
1267 l_added_rscs_tbl(i).attribute1 := 'N';
1268 l_added_rscs_tbl(i).attribute2 := '10';
1269 elsif x_status = 'I' then NULL;
1270 else
1271 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '10'); end if;
1272 return;
1273 end if;
1274 end if;
1275
1276 if P_RESOURCE11_NAME is not null or P_GROUP11_NAME is not null or P_ROLE11_NAME is not null
1277 then
1278
1279 validate_resource (
1280 P_RESOURCE_NAME=>P_RESOURCE11_NAME ,
1281 P_GROUP_NAME=>P_GROUP11_NAME ,
1282 P_ROLE_NAME=>P_ROLE11_NAME ,
1283 P_terr_group_id=>l_terr_group_id ,
1284 P_named_account_id=>l_named_account_id,
1285 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1286 p_alignment_id => P_ALIGNMENT_ID,
1287 X_RESOURCE_id=>X_RESOURCE_id ,
1288 x_group_id=>x_group_id ,
1289 x_role_code=>x_role_code ,
1290 x_error_code =>x_error_code,
1291 x_status=>x_status );
1292
1293
1294 if x_status = 'S' then
1295
1296 -- insert into sb values('Inside the success status '|| to_char(i));
1297 l_atleast_one_rep := TRUE;
1298 l_added_rscs_tbl.extend;
1299
1300 i:=i+1;
1301 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1302 l_added_rscs_tbl(i).group_id := x_group_id;
1306 elsif x_status = 'I' then NULL;
1303 l_added_rscs_tbl(i).role_code := x_role_code;
1304 l_added_rscs_tbl(i).attribute1 := 'N';
1305 l_added_rscs_tbl(i).attribute2 := '11';
1307 else
1308 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '11'); end if;
1309 return;
1310 end if;
1311 end if;
1312
1313 if P_RESOURCE12_NAME is not null or P_GROUP12_NAME is not null or P_ROLE12_NAME is not null
1314 then
1315
1316 validate_resource (
1317 P_RESOURCE_NAME=>P_RESOURCE12_NAME ,
1318 P_GROUP_NAME=>P_GROUP12_NAME ,
1319 P_ROLE_NAME=>P_ROLE12_NAME ,
1320 P_terr_group_id=>l_terr_group_id ,
1321 P_named_account_id=>l_named_account_id,
1322 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1323 p_alignment_id => P_ALIGNMENT_ID,
1324 X_RESOURCE_id=>X_RESOURCE_id ,
1325 x_group_id=>x_group_id ,
1326 x_role_code=>x_role_code ,
1327 x_error_code =>x_error_code,
1328 x_status=>x_status );
1329
1330
1331 if x_status = 'S' then
1332
1333 -- insert into sb values('Inside the success status '|| to_char(i));
1334 l_atleast_one_rep := TRUE;
1335 l_added_rscs_tbl.extend;
1336
1337 i:=i+1;
1338 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1339 l_added_rscs_tbl(i).group_id := x_group_id;
1340 l_added_rscs_tbl(i).role_code := x_role_code;
1341 l_added_rscs_tbl(i).attribute1 := 'N';
1342 l_added_rscs_tbl(i).attribute2 := '12';
1343 elsif x_status = 'I' then NULL;
1344 else
1345 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '12'); end if;
1346 return;
1347 end if;
1348 end if;
1349
1350 if P_RESOURCE13_NAME is not null or P_GROUP13_NAME is not null or P_ROLE13_NAME is not null
1351 then
1352
1353 validate_resource (
1354 P_RESOURCE_NAME=>P_RESOURCE13_NAME ,
1355 P_GROUP_NAME=>P_GROUP13_NAME ,
1356 P_ROLE_NAME=>P_ROLE13_NAME ,
1357 P_terr_group_id=>l_terr_group_id ,
1358 P_named_account_id=>l_named_account_id,
1359 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1360 p_alignment_id => P_ALIGNMENT_ID,
1361 X_RESOURCE_id=>X_RESOURCE_id ,
1362 x_group_id=>x_group_id ,
1363 x_role_code=>x_role_code ,
1364 x_error_code =>x_error_code,
1365 x_status=>x_status );
1366
1367
1368 if x_status = 'S' then
1369
1370 -- insert into sb values('Inside the success status '|| to_char(i));
1371 l_atleast_one_rep := TRUE;
1372 l_added_rscs_tbl.extend;
1373
1374 i:=i+1;
1375 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1376 l_added_rscs_tbl(i).group_id := x_group_id;
1377 l_added_rscs_tbl(i).role_code := x_role_code;
1378 l_added_rscs_tbl(i).attribute1 := 'N';
1379 l_added_rscs_tbl(i).attribute2 := '13';
1380 elsif x_status = 'I' then NULL;
1381 else
1382 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '13'); end if;
1383 return;
1384 end if;
1385 end if;
1386
1387 if P_RESOURCE14_NAME is not null or P_GROUP14_NAME is not null or P_ROLE14_NAME is not null
1388 then
1389
1390 validate_resource (
1391 P_RESOURCE_NAME=>P_RESOURCE14_NAME ,
1392 P_GROUP_NAME=>P_GROUP14_NAME ,
1393 P_ROLE_NAME=>P_ROLE14_NAME ,
1394 P_terr_group_id=>l_terr_group_id ,
1395 P_named_account_id=>l_named_account_id,
1396 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1397 p_alignment_id => P_ALIGNMENT_ID,
1398 X_RESOURCE_id=>X_RESOURCE_id ,
1399 x_group_id=>x_group_id ,
1400 x_role_code=>x_role_code ,
1401 x_error_code =>x_error_code,
1402 x_status=>x_status );
1403
1404
1405 if x_status = 'S' then
1406
1407 -- insert into sb values('Inside the success status '|| to_char(i));
1408 l_atleast_one_rep := TRUE;
1409 l_added_rscs_tbl.extend;
1410
1411 i:=i+1;
1412 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1413 l_added_rscs_tbl(i).group_id := x_group_id;
1414 l_added_rscs_tbl(i).role_code := x_role_code;
1415 l_added_rscs_tbl(i).attribute1 := 'N';
1416 l_added_rscs_tbl(i).attribute2 := '14';
1417 elsif x_status = 'I' then NULL;
1418 else
1419 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '14'); end if;
1420 return;
1421 end if;
1425 then
1422 end if;
1423
1424 if P_RESOURCE15_NAME is not null or P_GROUP15_NAME is not null or P_ROLE15_NAME is not null
1426
1427 validate_resource (
1428 P_RESOURCE_NAME=>P_RESOURCE15_NAME ,
1429 P_GROUP_NAME=>P_GROUP15_NAME ,
1430 P_ROLE_NAME=>P_ROLE15_NAME ,
1431 P_terr_group_id=>l_terr_group_id ,
1432 P_named_account_id=>l_named_account_id,
1433 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1434 p_alignment_id => P_ALIGNMENT_ID,
1435 X_RESOURCE_id=>X_RESOURCE_id ,
1436 x_group_id=>x_group_id ,
1437 x_role_code=>x_role_code ,
1438 x_error_code =>x_error_code,
1439 x_status=>x_status );
1440
1441
1442 if x_status = 'S' then
1443
1444 -- insert into sb values('Inside the success status '|| to_char(i));
1445 l_atleast_one_rep := TRUE;
1446 l_added_rscs_tbl.extend;
1447
1448 i:=i+1;
1449 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1450 l_added_rscs_tbl(i).group_id := x_group_id;
1451 l_added_rscs_tbl(i).role_code := x_role_code;
1452 l_added_rscs_tbl(i).attribute1 := 'N';
1453 l_added_rscs_tbl(i).attribute2 := '15';
1454 elsif x_status = 'I' then NULL;
1455 else
1456 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '15'); end if;
1457 return;
1458 end if;
1459 end if;
1460
1461 if P_RESOURCE16_NAME is not null or P_GROUP16_NAME is not null or P_ROLE16_NAME is not null
1462 then
1463
1464 validate_resource (
1465 P_RESOURCE_NAME=>P_RESOURCE16_NAME ,
1466 P_GROUP_NAME=>P_GROUP16_NAME ,
1467 P_ROLE_NAME=>P_ROLE16_NAME ,
1468 P_terr_group_id=>l_terr_group_id ,
1469 P_named_account_id=>l_named_account_id,
1470 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1471 p_alignment_id => P_ALIGNMENT_ID,
1472 X_RESOURCE_id=>X_RESOURCE_id ,
1473 x_group_id=>x_group_id ,
1474 x_role_code=>x_role_code ,
1475 x_error_code =>x_error_code,
1476 x_status=>x_status );
1477
1478
1479 if x_status = 'S' then
1480
1481 -- insert into sb values('Inside the success status '|| to_char(i));
1482 l_atleast_one_rep := TRUE;
1483 l_added_rscs_tbl.extend;
1484
1485 i:=i+1;
1486 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1487 l_added_rscs_tbl(i).group_id := x_group_id;
1488 l_added_rscs_tbl(i).role_code := x_role_code;
1489 l_added_rscs_tbl(i).attribute1 := 'N';
1490 l_added_rscs_tbl(i).attribute2 := '16';
1491 elsif x_status = 'I' then NULL;
1492 else
1493 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '16'); end if;
1494 return;
1495 end if;
1496 end if;
1497
1498 if P_RESOURCE17_NAME is not null or P_GROUP17_NAME is not null or P_ROLE17_NAME is not null
1499 then
1500
1501 validate_resource (
1502 P_RESOURCE_NAME=>P_RESOURCE17_NAME ,
1503 P_GROUP_NAME=>P_GROUP17_NAME ,
1504 P_ROLE_NAME=>P_ROLE17_NAME ,
1505 P_terr_group_id=>l_terr_group_id ,
1506 P_named_account_id=>l_named_account_id,
1507 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1508 p_alignment_id => P_ALIGNMENT_ID,
1509 X_RESOURCE_id=>X_RESOURCE_id ,
1510 x_group_id=>x_group_id ,
1511 x_role_code=>x_role_code ,
1512 x_error_code =>x_error_code,
1513 x_status=>x_status );
1514
1515
1516 if x_status = 'S' then
1517
1518 -- insert into sb values('Inside the success status '|| to_char(i));
1519 l_atleast_one_rep := TRUE;
1520 l_added_rscs_tbl.extend;
1521
1522 i:=i+1;
1523 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1524 l_added_rscs_tbl(i).group_id := x_group_id;
1525 l_added_rscs_tbl(i).role_code := x_role_code;
1526 l_added_rscs_tbl(i).attribute1 := 'N';
1527 l_added_rscs_tbl(i).attribute2 := '17';
1528 elsif x_status = 'I' then NULL;
1529 else
1530 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '17'); end if;
1531 return;
1532 end if;
1533 end if;
1534
1535 if P_RESOURCE18_NAME is not null or P_GROUP18_NAME is not null or P_ROLE18_NAME is not null
1536 then
1537
1538 validate_resource (
1539 P_RESOURCE_NAME=>P_RESOURCE18_NAME ,
1540 P_GROUP_NAME=>P_GROUP18_NAME ,
1541 P_ROLE_NAME=>P_ROLE18_NAME ,
1542 P_terr_group_id=>l_terr_group_id ,
1546 X_RESOURCE_id=>X_RESOURCE_id ,
1543 P_named_account_id=>l_named_account_id,
1544 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1545 p_alignment_id => P_ALIGNMENT_ID,
1547 x_group_id=>x_group_id ,
1548 x_role_code=>x_role_code ,
1549 x_error_code =>x_error_code,
1550 x_status=>x_status );
1551
1552
1553 if x_status = 'S' then
1554
1555 -- insert into sb values('Inside the success status '|| to_char(i));
1556 l_atleast_one_rep := TRUE;
1557 l_added_rscs_tbl.extend;
1558
1559 i:=i+1;
1560 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1561 l_added_rscs_tbl(i).group_id := x_group_id;
1562 l_added_rscs_tbl(i).role_code := x_role_code;
1563 l_added_rscs_tbl(i).attribute1 := 'N';
1564 l_added_rscs_tbl(i).attribute2 := '18';
1565 elsif x_status = 'I' then NULL;
1566 else
1567 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '18'); end if;
1568 return;
1569 end if;
1570 end if;
1571
1572 if P_RESOURCE19_NAME is not null or P_GROUP19_NAME is not null or P_ROLE19_NAME is not null
1573 then
1574
1575 validate_resource (
1576 P_RESOURCE_NAME=>P_RESOURCE19_NAME ,
1577 P_GROUP_NAME=>P_GROUP19_NAME ,
1578 P_ROLE_NAME=>P_ROLE19_NAME ,
1579 P_terr_group_id=>l_terr_group_id ,
1580 P_named_account_id=>l_named_account_id,
1581 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1582 p_alignment_id => P_ALIGNMENT_ID,
1583 X_RESOURCE_id=>X_RESOURCE_id ,
1584 x_group_id=>x_group_id ,
1585 x_role_code=>x_role_code ,
1586 x_error_code =>x_error_code,
1587 x_status=>x_status );
1588
1589
1590 if x_status = 'S' then
1591
1592 -- insert into sb values('Inside the success status '|| to_char(i));
1593 l_atleast_one_rep := TRUE;
1594 l_added_rscs_tbl.extend;
1595
1596 i:=i+1;
1597 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1598 l_added_rscs_tbl(i).group_id := x_group_id;
1599 l_added_rscs_tbl(i).role_code := x_role_code;
1600 l_added_rscs_tbl(i).attribute1 := 'N';
1601 l_added_rscs_tbl(i).attribute2 := '19';
1602 elsif x_status = 'I' then NULL;
1603 else
1604 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '19'); end if;
1605 return;
1606 end if;
1607 end if;
1608
1609 if P_RESOURCE20_NAME is not null or P_GROUP20_NAME is not null or P_ROLE20_NAME is not null
1610 then
1611
1612 validate_resource (
1613 P_RESOURCE_NAME=>P_RESOURCE20_NAME ,
1614 P_GROUP_NAME=>P_GROUP20_NAME ,
1615 P_ROLE_NAME=>P_ROLE20_NAME ,
1616 P_terr_group_id=>l_terr_group_id ,
1617 P_named_account_id=>l_named_account_id,
1618 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1619 p_alignment_id => P_ALIGNMENT_ID,
1620 X_RESOURCE_id=>X_RESOURCE_id ,
1624 x_status=>x_status );
1621 x_group_id=>x_group_id ,
1622 x_role_code=>x_role_code ,
1623 x_error_code =>x_error_code,
1625
1626
1627 if x_status = 'S' then
1628
1629 -- insert into sb values('Inside the success status '|| to_char(i));
1630 l_atleast_one_rep := TRUE;
1631 l_added_rscs_tbl.extend;
1632
1633 i:=i+1;
1634 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1635 l_added_rscs_tbl(i).group_id := x_group_id;
1636 l_added_rscs_tbl(i).role_code := x_role_code;
1637 l_added_rscs_tbl(i).attribute1 := 'N';
1638 l_added_rscs_tbl(i).attribute2 := '20';
1639 elsif x_status = 'I' then NULL;
1640 else
1641 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '20'); end if;
1642 return;
1643 end if;
1644 end if;
1645
1646 if P_RESOURCE21_NAME is not null or P_GROUP21_NAME is not null or P_ROLE21_NAME is not null
1647 then
1648
1649 validate_resource (
1650 P_RESOURCE_NAME=>P_RESOURCE21_NAME ,
1651 P_GROUP_NAME=>P_GROUP21_NAME ,
1652 P_ROLE_NAME=>P_ROLE21_NAME ,
1653 P_terr_group_id=>l_terr_group_id ,
1654 P_named_account_id=>l_named_account_id,
1655 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1656 p_alignment_id => P_ALIGNMENT_ID,
1657 X_RESOURCE_id=>X_RESOURCE_id ,
1658 x_group_id=>x_group_id ,
1659 x_role_code=>x_role_code ,
1660 x_error_code =>x_error_code,
1661 x_status=>x_status );
1662
1663
1664 if x_status = 'S' then
1665
1666 -- insert into sb values('Inside the success status '|| to_char(i));
1667 l_atleast_one_rep := TRUE;
1668 l_added_rscs_tbl.extend;
1669
1670 i:=i+1;
1671 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1672 l_added_rscs_tbl(i).group_id := x_group_id;
1673 l_added_rscs_tbl(i).role_code := x_role_code;
1674 l_added_rscs_tbl(i).attribute1 := 'N';
1675 l_added_rscs_tbl(i).attribute2 := '21';
1676 elsif x_status = 'I' then NULL;
1677 else
1678 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '21'); end if;
1679 return;
1680 return;
1681 end if;
1682 end if;
1683
1684 if P_RESOURCE22_NAME is not null or P_GROUP22_NAME is not null or P_ROLE22_NAME is not null
1685 then
1686
1687 validate_resource (
1688 P_RESOURCE_NAME=>P_RESOURCE22_NAME ,
1689 P_GROUP_NAME=>P_GROUP22_NAME ,
1690 P_ROLE_NAME=>P_ROLE22_NAME ,
1691 P_terr_group_id=>l_terr_group_id ,
1692 P_named_account_id=>l_named_account_id,
1693 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1694 p_alignment_id => P_ALIGNMENT_ID,
1695 X_RESOURCE_id=>X_RESOURCE_id ,
1696 x_group_id=>x_group_id ,
1697 x_role_code=>x_role_code ,
1698 x_error_code =>x_error_code,
1699 x_status=>x_status );
1700
1701
1702 if x_status = 'S' then
1703
1704 -- insert into sb values('Inside the success status '|| to_char(i));
1705 l_atleast_one_rep := TRUE;
1706 l_added_rscs_tbl.extend;
1707
1708 i:=i+1;
1709 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1710 l_added_rscs_tbl(i).group_id := x_group_id;
1711 l_added_rscs_tbl(i).role_code := x_role_code;
1712 l_added_rscs_tbl(i).attribute1 := 'N';
1713 l_added_rscs_tbl(i).attribute2 := '22';
1714 elsif x_status = 'I' then NULL;
1715 else
1716 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '22'); end if;
1717 return;
1718 end if;
1719 end if;
1720
1721 if P_RESOURCE23_NAME is not null or P_GROUP23_NAME is not null or P_ROLE23_NAME is not null
1722 then
1723
1724 validate_resource (
1725 P_RESOURCE_NAME=>P_RESOURCE23_NAME ,
1726 P_GROUP_NAME=>P_GROUP23_NAME ,
1727 P_ROLE_NAME=>P_ROLE23_NAME ,
1728 P_terr_group_id=>l_terr_group_id ,
1729 P_named_account_id=>l_named_account_id,
1730 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1731 p_alignment_id => P_ALIGNMENT_ID,
1732 X_RESOURCE_id=>X_RESOURCE_id ,
1733 x_group_id=>x_group_id ,
1734 x_role_code=>x_role_code ,
1735 x_error_code =>x_error_code,
1736 x_status=>x_status );
1737
1738
1739 if x_status = 'S' then
1740
1741 -- insert into sb values('Inside the success status '|| to_char(i));
1745 i:=i+1;
1742 l_atleast_one_rep := TRUE;
1743 l_added_rscs_tbl.extend;
1744
1746 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1747 l_added_rscs_tbl(i).group_id := x_group_id;
1748 l_added_rscs_tbl(i).role_code := x_role_code;
1749 l_added_rscs_tbl(i).attribute1 := 'N';
1750 l_added_rscs_tbl(i).attribute2 := '23';
1751 elsif x_status = 'I' then NULL;
1752 else
1753 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '23'); end if;
1754 return;
1755 end if;
1756 end if;
1757
1758 if P_RESOURCE24_NAME is not null or P_GROUP24_NAME is not null or P_ROLE24_NAME is not null
1759 then
1760
1761 validate_resource (
1762 P_RESOURCE_NAME=>P_RESOURCE24_NAME ,
1763 P_GROUP_NAME=>P_GROUP24_NAME ,
1764 P_ROLE_NAME=>P_ROLE24_NAME ,
1765 P_terr_group_id=>l_terr_group_id ,
1766 P_named_account_id=>l_named_account_id,
1767 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1768 p_alignment_id => P_ALIGNMENT_ID,
1769 X_RESOURCE_id=>X_RESOURCE_id ,
1770 x_group_id=>x_group_id ,
1771 x_role_code=>x_role_code ,
1772 x_error_code =>x_error_code,
1773 x_status=>x_status );
1774
1775
1776 if x_status = 'S' then
1777
1778 -- insert into sb values('Inside the success status '|| to_char(i));
1779 l_atleast_one_rep := TRUE;
1780 l_added_rscs_tbl.extend;
1781
1782 i:=i+1;
1783 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1784 l_added_rscs_tbl(i).group_id := x_group_id;
1785 l_added_rscs_tbl(i).role_code := x_role_code;
1786 l_added_rscs_tbl(i).attribute1 := 'N';
1787 l_added_rscs_tbl(i).attribute2 := '24';
1788 elsif x_status = 'I' then NULL;
1789 else
1790 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '24'); end if;
1791 return;
1792 end if;
1793 end if;
1794
1795 if P_RESOURCE25_NAME is not null or P_GROUP25_NAME is not null or P_ROLE25_NAME is not null
1796 then
1797
1798 validate_resource (
1799 P_RESOURCE_NAME=>P_RESOURCE25_NAME ,
1800 P_GROUP_NAME=>P_GROUP25_NAME ,
1801 P_ROLE_NAME=>P_ROLE25_NAME ,
1802 P_terr_group_id=>l_terr_group_id ,
1803 P_named_account_id=>l_named_account_id,
1804 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1805 p_alignment_id => P_ALIGNMENT_ID,
1806 X_RESOURCE_id=>X_RESOURCE_id ,
1807 x_group_id=>x_group_id ,
1808 x_role_code=>x_role_code ,
1809 x_error_code =>x_error_code,
1810 x_status=>x_status );
1811
1812
1813 if x_status = 'S' then
1814
1815 -- insert into sb values('Inside the success status '|| to_char(i));
1816 l_atleast_one_rep := TRUE;
1817 l_added_rscs_tbl.extend;
1818
1819 i:=i+1;
1820 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1821 l_added_rscs_tbl(i).group_id := x_group_id;
1822 l_added_rscs_tbl(i).role_code := x_role_code;
1823 l_added_rscs_tbl(i).attribute1 := 'N';
1824 l_added_rscs_tbl(i).attribute2 := '25';
1825 elsif x_status = 'I' then NULL;
1826 else
1827 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '25'); end if;
1828 return;
1829 end if;
1830 end if;
1831
1832 if P_RESOURCE26_NAME is not null or P_GROUP26_NAME is not null or P_ROLE26_NAME is not null
1833 then
1834
1835 validate_resource (
1836 P_RESOURCE_NAME=>P_RESOURCE26_NAME ,
1837 P_GROUP_NAME=>P_GROUP26_NAME ,
1838 P_ROLE_NAME=>P_ROLE26_NAME ,
1839 P_terr_group_id=>l_terr_group_id ,
1840 P_named_account_id=>l_named_account_id,
1841 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1842 p_alignment_id => P_ALIGNMENT_ID,
1843 X_RESOURCE_id=>X_RESOURCE_id ,
1844 x_group_id=>x_group_id ,
1845 x_role_code=>x_role_code ,
1846 x_error_code =>x_error_code,
1847 x_status=>x_status );
1848
1849
1850 if x_status = 'S' then
1851
1852 -- insert into sb values('Inside the success status '|| to_char(i));
1853 l_atleast_one_rep := TRUE;
1854 l_added_rscs_tbl.extend;
1855
1856 i:=i+1;
1857 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1858 l_added_rscs_tbl(i).group_id := x_group_id;
1859 l_added_rscs_tbl(i).role_code := x_role_code;
1860 l_added_rscs_tbl(i).attribute1 := 'N';
1864 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '26'); end if;
1861 l_added_rscs_tbl(i).attribute2 := '26';
1862 elsif x_status = 'I' then NULL;
1863 else
1865 return;
1866 end if;
1867 end if;
1868
1869 if P_RESOURCE27_NAME is not null or P_GROUP27_NAME is not null or P_ROLE27_NAME is not null
1870 then
1871
1872 validate_resource (
1873 P_RESOURCE_NAME=>P_RESOURCE27_NAME ,
1874 P_GROUP_NAME=>P_GROUP27_NAME ,
1875 P_ROLE_NAME=>P_ROLE27_NAME ,
1876 P_terr_group_id=>l_terr_group_id ,
1877 P_named_account_id=>l_named_account_id,
1878 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1879 p_alignment_id => P_ALIGNMENT_ID,
1880 X_RESOURCE_id=>X_RESOURCE_id ,
1881 x_group_id=>x_group_id ,
1882 x_role_code=>x_role_code ,
1883 x_error_code =>x_error_code,
1884 x_status=>x_status );
1885
1886
1887 if x_status = 'S' then
1888
1889 -- insert into sb values('Inside the success status '|| to_char(i));
1890 l_atleast_one_rep := TRUE;
1891 l_added_rscs_tbl.extend;
1892
1893 i:=i+1;
1894 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1895 l_added_rscs_tbl(i).group_id := x_group_id;
1896 l_added_rscs_tbl(i).role_code := x_role_code;
1897 l_added_rscs_tbl(i).attribute1 := 'N';
1898 l_added_rscs_tbl(i).attribute2 := '27';
1899 elsif x_status = 'I' then NULL;
1900 else
1901 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '27'); end if;
1902 return;
1903 end if;
1904 end if;
1905
1906 if P_RESOURCE28_NAME is not null or P_GROUP28_NAME is not null or P_ROLE28_NAME is not null
1907 then
1908
1909 validate_resource (
1910 P_RESOURCE_NAME=>P_RESOURCE28_NAME ,
1911 P_GROUP_NAME=>P_GROUP28_NAME ,
1912 P_ROLE_NAME=>P_ROLE28_NAME ,
1913 P_terr_group_id=>l_terr_group_id ,
1914 P_named_account_id=>l_named_account_id,
1915 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1916 p_alignment_id => P_ALIGNMENT_ID,
1917 X_RESOURCE_id=>X_RESOURCE_id ,
1918 x_group_id=>x_group_id ,
1919 x_role_code=>x_role_code ,
1920 x_error_code =>x_error_code,
1921 x_status=>x_status );
1922
1923
1924 if x_status = 'S' then
1925
1926 -- insert into sb values('Inside the success status '|| to_char(i));
1927 l_atleast_one_rep := TRUE;
1928 l_added_rscs_tbl.extend;
1929
1930 i:=i+1;
1931 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1932 l_added_rscs_tbl(i).group_id := x_group_id;
1933 l_added_rscs_tbl(i).role_code := x_role_code;
1934 l_added_rscs_tbl(i).attribute1 := 'N';
1935 l_added_rscs_tbl(i).attribute2 := '28';
1936 elsif x_status = 'I' then NULL;
1937 else
1938 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '28'); end if;
1939 return;
1940 end if;
1941 end if;
1942
1943 if P_RESOURCE29_NAME is not null or P_GROUP29_NAME is not null or P_ROLE29_NAME is not null
1944 then
1945
1946 validate_resource (
1947 P_RESOURCE_NAME=>P_RESOURCE29_NAME ,
1948 P_GROUP_NAME=>P_GROUP29_NAME ,
1949 P_ROLE_NAME=>P_ROLE29_NAME ,
1950 P_terr_group_id=>l_terr_group_id ,
1951 P_named_account_id=>l_named_account_id,
1952 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1953 p_alignment_id => P_ALIGNMENT_ID,
1954 X_RESOURCE_id=>X_RESOURCE_id ,
1955 x_group_id=>x_group_id ,
1956 x_role_code=>x_role_code ,
1957 x_error_code =>x_error_code,
1958 x_status=>x_status );
1959
1960
1961 if x_status = 'S' then
1962
1963 -- insert into sb values('Inside the success status '|| to_char(i));
1964 l_atleast_one_rep := TRUE;
1965 l_added_rscs_tbl.extend;
1966
1967 i:=i+1;
1968 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
1969 l_added_rscs_tbl(i).group_id := x_group_id;
1970 l_added_rscs_tbl(i).role_code := x_role_code;
1971 l_added_rscs_tbl(i).attribute1 := 'N';
1972 l_added_rscs_tbl(i).attribute2 := '29';
1973 elsif x_status = 'I' then NULL;
1974 else
1975 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '29'); end if;
1976 return;
1977 end if;
1978 end if;
1979
1983 validate_resource (
1980 if P_RESOURCE30_NAME is not null or P_GROUP30_NAME is not null or P_ROLE30_NAME is not null
1981 then
1982
1984 P_RESOURCE_NAME=>P_RESOURCE30_NAME ,
1985 P_GROUP_NAME=>P_GROUP30_NAME ,
1986 P_ROLE_NAME=>P_ROLE30_NAME ,
1987 P_terr_group_id=>l_terr_group_id ,
1988 P_named_account_id=>l_named_account_id,
1989 P_TERR_GRP_ACCT_ID =>P_TERR_GRP_ACCT_ID,
1990 p_alignment_id => P_ALIGNMENT_ID,
1991 X_RESOURCE_id=>X_RESOURCE_id ,
1992 x_group_id=>x_group_id ,
1993 x_role_code=>x_role_code ,
1994 x_error_code =>x_error_code,
1995 x_status=>x_status );
1996
1997
1998 if x_status = 'S' then
1999
2000 -- insert into sb values('Inside the success status '|| to_char(i));
2001 l_atleast_one_rep := TRUE;
2002 l_added_rscs_tbl.extend;
2003
2004 i:=i+1;
2005 l_added_rscs_tbl(i).resource_id := X_RESOURCE_id;
2006 l_added_rscs_tbl(i).group_id := x_group_id;
2007 l_added_rscs_tbl(i).role_code := x_role_code;
2008 l_added_rscs_tbl(i).attribute1 := 'N';
2009 l_added_rscs_tbl(i).attribute2 := '30';
2010 elsif x_status = 'I' then NULL;
2011 else
2012 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', '30'); end if;
2013 return;
2014 end if;
2015 end if;
2016
2017 end;
2018
2019
2020
2021 l_add_rscs_tbl := JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE();
2022 l_removed_rscs_tbl := JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE();
2023
2024 if p_alignment_id is null
2025 then
2026 /* Following code find out all the newly added sales info and
2027 put it into the l_add_rscs_tbl */
2028 if l_added_rscs_tbl.FIRST is not null
2029 then
2030 for j in l_added_rscs_tbl.FIRST..l_added_rscs_tbl.LAST
2031 loop
2032 begin
2033 select ASSIGNED_FLAG
2034 into l_assign_flag
2035 from jtf_tty_named_acct_rsc
2036 where TERR_GROUP_ACCOUNT_ID = l_terr_grp_acct_id
2037 and RESOURCE_ID = l_added_rscs_tbl(j).Resource_id
2038 and RSC_GROUP_ID = l_added_rscs_tbl(j).group_id
2039 and RSC_ROLE_CODE = l_added_rscs_tbl(j).role_code
2040 and RSC_RESOURCE_TYPE = 'RS_EMPLOYEE';
2041
2042 IF l_assign_flag = 'N' THEN
2043 l_add_rscs_tbl.extend;
2044 l_add_count := l_add_count + 1;
2045 l_add_rscs_tbl(l_add_count).resource_id := l_added_rscs_tbl(j).Resource_id;
2046 l_add_rscs_tbl(l_add_count).group_id := l_added_rscs_tbl(j).group_id;
2047 l_add_rscs_tbl(l_add_count).role_code := l_added_rscs_tbl(j).role_code;
2048 l_add_rscs_tbl(l_add_count).attribute1 := 'N';
2049 ELSE --l_assign_flag = 'Y',ignore
2050 NULL;
2051 END IF;
2052
2053
2054 exception
2055 when no_data_found then
2056 CHECK_VALID_RESOURCE_ADD (
2057 P_RESOURCE_id => l_added_rscs_tbl(j).Resource_id
2058 , P_GROUP_ID => l_added_rscs_tbl(j).group_id
2059 , P_ROLE_CODE => l_added_rscs_tbl(j).role_code
2060 , P_user_id => l_user_id
2061 , P_TG_id => l_terr_group_id
2062 , x_error_code => x_error_code
2063 , x_status => x_status );
2064
2065 if x_status = 'S' then
2066 l_add_rscs_tbl.extend;
2067 l_add_count := l_add_count + 1;
2068 l_add_rscs_tbl(l_add_count).resource_id := l_added_rscs_tbl(j).Resource_id;
2069 l_add_rscs_tbl(l_add_count).group_id := l_added_rscs_tbl(j).group_id;
2070 l_add_rscs_tbl(l_add_count).role_code := l_added_rscs_tbl(j).role_code;
2071 l_add_rscs_tbl(l_add_count).attribute1 := 'N';
2072 else
2073 if x_error_code < 4 then FND_MESSAGE.Set_Token ('POSITION', l_added_rscs_tbl(j).attribute2); end if;
2074 return;
2075 end if;
2076 when others then
2077 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
2078 return;
2079 end;
2080 end loop;
2081 end if; -- end of l_added_rscs table not being null
2082
2083 /* Following code find out all the removed sales info and
2084 put it into the l_removed_rscs_tbl */
2085 for c_res in c_res_list(l_terr_grp_acct_id)
2086 loop
2087 l_res_found := FALSE;
2088 if l_added_rscs_tbl.FIRST is not null
2089 then
2090 for j in l_added_rscs_tbl.FIRST..l_added_rscs_tbl.LAST
2094 and l_added_rscs_tbl(j).role_code = c_res.RSC_ROLE_CODE
2091 loop
2092 if l_added_rscs_tbl(j).Resource_id = c_res.Resource_id
2093 and l_added_rscs_tbl(j).group_id = c_res.RSC_GROUP_ID
2095 then
2096 l_res_found := TRUE;
2097 exit;
2098 END IF;
2099 end loop;
2100 end if;
2101
2102 if l_res_found = FALSE THEN
2103 Begin
2104 CHECK_VALID_RESOURCE_REMOVE (
2105 P_RESOURCE_id => c_res.Resource_id
2106 , P_GROUP_ID => c_res.RSC_GROUP_ID
2107 , P_ROLE_CODE => c_res.RSC_ROLE_CODE
2108 , P_user_id => l_user_id
2109 , P_TG_id => l_terr_group_id
2110 , x_error_code => x_error_code
2111 , x_status => x_status );
2112
2113 if x_status = 'S' then
2114 l_removed_rscs_tbl.extend;
2115 l_delete_count :=l_delete_count +1;
2116 l_removed_rscs_tbl(l_delete_count).resource_id := c_res.Resource_id;
2117 l_removed_rscs_tbl(l_delete_count).group_id := c_res.RSC_GROUP_ID;
2118 l_removed_rscs_tbl(l_delete_count).role_code := c_res.RSC_ROLE_CODE;
2119 l_removed_rscs_tbl(l_delete_count).attribute1 := 'N';
2120 end if;
2121 Exception
2122 when others then
2123 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
2124 return;
2125 end;
2126 end if; -- if l_res_found = FALSE
2127 end loop; -- end of c_res loop
2128 else -- p_alignment_id is not null
2129
2130 /* Following code find out all the newly added sales info and
2131 put it into the l_add_rscs_tbl */
2132 if l_added_rscs_tbl.FIRST is not null
2133 then
2134 for j in l_added_rscs_tbl.FIRST..l_added_rscs_tbl.LAST
2135 loop
2136 begin
2137 select 'x'
2138 into l_whether_exist
2139 from jtf_tty_align_pterr pt,
2140 jtf_tty_pterr_accts pa,
2141 jtf_tty_align_accts aa
2142 where aa.terr_group_account_id = l_terr_grp_acct_id
2143 and aa.alignment_id = p_alignment_id
2144 and aa.align_acct_id = pa.align_acct_id
2145 and pa.align_proposed_terr_id = pt.align_proposed_terr_id
2146 and pt.resource_id = l_added_rscs_tbl(j).Resource_id
2147 and pt.rsc_group_id = l_added_rscs_tbl(j).group_id
2148 and pt.rsc_role_code = l_added_rscs_tbl(j).role_code
2149 and pt.resource_type = 'RS_EMPLOYEE';
2150 exception
2151 when no_data_found then
2152 l_add_rscs_tbl.extend;
2153 l_add_count := l_add_count + 1;
2154 l_add_rscs_tbl(l_add_count).resource_id := l_added_rscs_tbl(j).Resource_id;
2155 l_add_rscs_tbl(l_add_count).group_id := l_added_rscs_tbl(j).group_id;
2156 l_add_rscs_tbl(l_add_count).role_code := l_added_rscs_tbl(j).role_code;
2157 l_add_rscs_tbl(l_add_count).attribute1 := 'N';
2158 when others then
2159 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
2160 return;
2161 end;
2162 end loop;
2163 end if; -- end of l_added_rscs table not being null
2164
2165 /* Check to see if this is the first time alignment is being uploaded */
2166 begin
2167 select imported_on
2168 into l_imported_on
2169 from jtf_tty_alignments
2170 where alignment_id = p_alignment_id;
2171 exception
2172 when no_data_found then null;
2173 end;
2174
2175 /* Following code find out all the removed sales info and
2176 put it into the l_removed_rscs_tbl */
2177
2178 if ( l_imported_on IS NOT NULL )
2179 then
2180
2181 for c_res in c_align_res_list(l_terr_grp_acct_id)
2182 loop
2183 l_res_found := FALSE;
2184 if l_added_rscs_tbl.FIRST is not null
2185 then
2186 for j in l_added_rscs_tbl.FIRST..l_added_rscs_tbl.LAST
2187 loop
2188 if l_added_rscs_tbl(j).Resource_id = c_res.Resource_id
2189 and l_added_rscs_tbl(j).group_id = c_res.RSC_GROUP_ID
2190 and l_added_rscs_tbl(j).role_code = c_res.RSC_ROLE_CODE
2191 then
2192 l_res_found := TRUE;
2193 exit;
2194 END IF;
2195 end loop;
2196 end if;
2197
2198 if l_res_found = FALSE THEN
2199 l_removed_rscs_tbl.extend;
2200 l_delete_count :=l_delete_count +1;
2201 l_removed_rscs_tbl(l_delete_count).resource_id := c_res.Resource_id;
2202 l_removed_rscs_tbl(l_delete_count).group_id := c_res.RSC_GROUP_ID;
2203 l_removed_rscs_tbl(l_delete_count).role_code := c_res.RSC_ROLE_CODE;
2207 end if;
2204 l_removed_rscs_tbl(l_delete_count).attribute1 := 'N';
2205
2206
2208 end loop; -- end of c_res loop
2209 else -- imported on is NULL
2210 for c_res in c_res_list_for_align(c_terr_grp_acct_id => l_terr_grp_acct_id,
2211 c_user_id => l_user_id)
2212 loop
2213 l_res_found := FALSE;
2214 if l_added_rscs_tbl.FIRST is not null
2215 then
2216 l_direct_flag := 'N' ;
2217 l_directs_tbl := JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE();
2218 l_index := 0;
2219 OPEN c_check_direct_res(c_resource_id => c_res.resource_id,
2220 c_group_id => c_res.rsc_group_id,
2221 c_role_code => c_res.rsc_role_code,
2222 c_user_id => l_user_id );
2223 FETCH c_check_direct_res INTO l_direct_flag;
2224 CLOSE c_check_direct_res;
2225 IF (l_direct_flag = 'Y' )
2226 THEN
2227 l_directs_tbl.extend;
2228 l_index := l_index +1;
2229 l_directs_tbl(l_index).resource_id := c_res.Resource_id;
2230 l_directs_tbl(l_index).group_id := c_res.RSC_GROUP_ID;
2231 l_directs_tbl(l_index).role_code := c_res.RSC_ROLE_CODE;
2232 l_directs_tbl(l_index).attribute1 := 'N';
2233 ELSE
2234 FOR direct_rec IN c_get_direct_res(c_resource_id => c_res.resource_id,
2235 c_group_id => c_res.rsc_group_id ,
2236 c_user_id => l_user_id)
2237 LOOP
2238 l_directs_tbl.extend;
2239 l_index := l_index +1;
2240 l_directs_tbl(l_index).resource_id := direct_rec.resource_id;
2241 l_directs_tbl(l_index).group_id := direct_rec.group_id;
2242 l_directs_tbl(l_index).role_code := direct_rec.role_code;
2243 l_directs_tbl(l_index).attribute1 := 'N';
2244 END LOOP;
2245 END IF;
2246 IF ( l_directs_tbl IS NOT NULL) AND ( l_directs_tbl.COUNT > 0 )
2247 THEN
2248 For k in l_directs_tbl.FIRST .. l_directs_tbl.LAST
2249 LOOP
2250 for j in l_added_rscs_tbl.FIRST..l_added_rscs_tbl.LAST
2251 loop
2252 if l_added_rscs_tbl(j).Resource_id = l_directs_tbl(k).resource_id
2253 and l_added_rscs_tbl(j).group_id = l_directs_tbl(k).group_id
2254 and l_added_rscs_tbl(j).role_code = l_directs_tbl(k).role_code
2255 then
2256 l_res_found := TRUE;
2257 exit;
2258 end if;
2259 end loop; -- for l_added_rscs_tbl
2260
2261 if l_res_found = FALSE THEN
2262 l_removed_rscs_tbl.extend;
2263 l_delete_count :=l_delete_count +1;
2264 l_removed_rscs_tbl(l_delete_count).resource_id := l_directs_tbl(k).resource_id;
2265 l_removed_rscs_tbl(l_delete_count).group_id := l_directs_tbl(k).group_id;
2266 l_removed_rscs_tbl(l_delete_count).role_code := l_directs_tbl(k).role_code;
2267 l_removed_rscs_tbl(l_delete_count).attribute1 := 'N';
2268 end if;
2269 END LOOP; -- l_directs_tbl
2270 END IF; -- l_directs_tbl.FIRST is NOT NULL
2271 end if; -- l_added_rscs_tbl.FIRST is NOT NULL
2272 end loop; -- end of c_res loop
2273 end if; -- end if alignment_id is null or imported_on_date is null
2274 end if; -- end if p_alignment_id is null
2275
2276 JTF_TTY_NACCT_SALES_PUB.G_ADD_SALESREP_TBL:=l_add_rscs_tbl;
2277 JTF_TTY_NACCT_SALES_PUB.G_REM_SALESREP_TBL:=l_removed_rscs_tbl;
2278 JTF_TTY_NACCT_SALES_PUB.G_AFFECT_PARTY_TBL:=l_affected_parties_tbl;
2279
2280 --insert into tmp2 values('what is p_alignment_id',p_alignment_id); commit;
2281 if p_alignment_id is null then
2282 --call named account update API
2283 --insert into tmp values('what is l_terr_group_id',l_terr_group_id); commit;
2284 JTF_TTY_NACCT_SALES_PUB.UPDATE_SALES_TEAM(
2285 p_api_version_number => 1,
2286 p_init_msg_list => 'N',
2287 p_SQL_Trace => 'N',
2288 p_Debug_Flag => 'N',
2289 x_return_status => x_return_status,
2290 x_msg_count => x_msg_count,
2291 x_msg_data => x_msg_data,
2292 p_user_resource_id => null,
2293 p_terr_group_id => l_terr_group_id,
2294 p_user_attribute1 => fnd_global.user_id,
2295 --p_user_attribute1 => 1069,
2296 p_user_attribute2 => null,
2297 p_added_rscs_tbl => l_add_rscs_tbl,
2298 p_removed_rscs_tbl => l_removed_rscs_tbl,
2299 p_affected_parties_tbl => l_affected_parties_tbl,
2300 ERRBUF => errbuf,
2301 RETCODE => retcode
2302 );
2303 else
2304 --call alignment update API
2305 --insert into tmp2 values('calling JTF_TTY_ALIGN_WEBADI_INT_PKG.UPDATE_ALIGNMENT_TEAM',p_alignment_id); commit;
2306
2307 JTF_TTY_ALIGN_WEBADI_INT_PKG.UPDATE_ALIGNMENT_TEAM(
2308 p_api_version_number => 1,
2309 p_init_msg_list => 'N',
2310 p_SQL_Trace => 'N',
2311 p_Debug_Flag => 'N',
2312 p_alignment_id => p_alignment_id,
2313 p_user_id => l_user_id,
2314 p_user_attribute1 => fnd_global.user_id,
2315 p_added_rscs_tbl => l_add_rscs_tbl,
2316 p_removed_rscs_tbl => l_removed_rscs_tbl,
2317 p_affected_parties_tbl => l_affected_parties_tbl,
2318 x_return_status => x_return_status,
2319 x_msg_count => x_msg_count,
2320 x_msg_data => x_msg_data
2321 );
2322 end if;
2323 --
2324 -- insert into tmp values('2','2'); commit;
2325
2326 -- commit;
2327
2328 exception
2329 when no_data_found then
2330 fnd_message.set_name ('JTF', 'JTF_TTY_NA_NOT_ASSIGED');
2331 return;
2332
2333 when others then
2334 --insert into tmp2 values('when others SALTEAM update','when others SALTEAM update'); commit;
2335 fnd_message.set_name ('JTF', 'JTF_TTY_UNEXPECTED_ERROR');
2336 return;
2337
2338
2339 end;
2340
2341 end POPULATE_SALESTEAM_ALIGNMENT;
2342
2343
2344 PROCEDURE POPULATE_ALIGNMENT (
2345 --P_USER_SEQUENCE in varchar2,
2346 P_NAMED_ACCOUNT in varchar2,
2347 P_SITE_TYPE in varchar2,
2348 P_TRADE_NAME in varchar2,
2349 P_DUNS in varchar2,
2350 P_GU_DUNS in varchar2,
2351 P_GU_NAME in varchar2,
2352 P_CITY in varchar2,
2353 P_STATE in varchar2,
2354 P_PROVINCE in varchar2,
2355 P_POSTAL_CODE in varchar2,
2356 P_DNB_ANNUAL_REV in varchar2,
2357 P_DNB_NUM_OF_EMP in varchar2,
2358 P_PRIOR_WON in varchar2,
2359 P_TERRITORY_GROUP in varchar2,
2360 P_RESOURCE1_NAME in varchar2,
2361 P_GROUP1_NAME in varchar2,
2362 P_ROLE1_NAME in varchar2,
2363 P_RESOURCE2_NAME in varchar2,
2364 P_GROUP2_NAME in varchar2,
2365 P_ROLE2_NAME in varchar2,
2366 P_RESOURCE3_NAME in varchar2,
2367 P_GROUP3_NAME in varchar2,
2368 P_ROLE3_NAME in varchar2,
2369 P_RESOURCE4_NAME in varchar2,
2370 P_GROUP4_NAME in varchar2,
2371 P_ROLE4_NAME in varchar2,
2372 P_RESOURCE5_NAME in varchar2,
2373 P_GROUP5_NAME in varchar2,
2374 P_ROLE5_NAME in varchar2,
2375 P_RESOURCE6_NAME in varchar2,
2376 P_GROUP6_NAME in varchar2,
2377 P_ROLE6_NAME in varchar2,
2378 P_RESOURCE7_NAME in varchar2,
2379 P_GROUP7_NAME in varchar2,
2380 P_ROLE7_NAME in varchar2,
2381 P_RESOURCE8_NAME in varchar2,
2382 P_GROUP8_NAME in varchar2,
2383 P_ROLE8_NAME in varchar2,
2384 P_RESOURCE9_NAME in varchar2,
2385 P_GROUP9_NAME in varchar2,
2386 P_ROLE9_NAME in varchar2,
2387 P_RESOURCE10_NAME in varchar2,
2388 P_GROUP10_NAME in varchar2,
2389 P_ROLE10_NAME in varchar2,
2390 P_RESOURCE11_NAME in varchar2,
2391 P_GROUP11_NAME in varchar2,
2392 P_ROLE11_NAME in varchar2,
2393 P_RESOURCE12_NAME in varchar2,
2394 P_GROUP12_NAME in varchar2,
2395 P_ROLE12_NAME in varchar2,
2396 P_RESOURCE13_NAME in varchar2,
2397 P_GROUP13_NAME in varchar2,
2398 P_ROLE13_NAME in varchar2,
2399 P_RESOURCE14_NAME in varchar2,
2400 P_GROUP14_NAME in varchar2,
2401 P_ROLE14_NAME in varchar2,
2402 P_RESOURCE15_NAME in varchar2,
2403 P_GROUP15_NAME in varchar2,
2404 P_ROLE15_NAME in varchar2,
2405 P_RESOURCE16_NAME in varchar2,
2406 P_GROUP16_NAME in varchar2,
2407 P_ROLE16_NAME in varchar2,
2408 P_RESOURCE17_NAME in varchar2,
2409 P_GROUP17_NAME in varchar2,
2410 P_ROLE17_NAME in varchar2,
2411 P_RESOURCE18_NAME in varchar2,
2412 P_GROUP18_NAME in varchar2,
2413 P_ROLE18_NAME in varchar2,
2414 P_RESOURCE19_NAME in varchar2,
2415 P_GROUP19_NAME in varchar2,
2416 P_ROLE19_NAME in varchar2,
2417 P_RESOURCE20_NAME in varchar2,
2418 P_GROUP20_NAME in varchar2,
2419 P_ROLE20_NAME in varchar2,
2420 P_RESOURCE21_NAME in varchar2,
2421 P_GROUP21_NAME in varchar2,
2422 P_ROLE21_NAME in varchar2,
2423 P_RESOURCE22_NAME in varchar2,
2424 P_GROUP22_NAME in varchar2,
2425 P_ROLE22_NAME in varchar2,
2426 P_RESOURCE23_NAME in varchar2,
2427 P_GROUP23_NAME in varchar2,
2428 P_ROLE23_NAME in varchar2,
2429 P_RESOURCE24_NAME in varchar2,
2430 P_GROUP24_NAME in varchar2,
2431 P_ROLE24_NAME in varchar2,
2432 P_RESOURCE25_NAME in varchar2,
2433 P_GROUP25_NAME in varchar2,
2434 P_ROLE25_NAME in varchar2,
2435 P_RESOURCE26_NAME in varchar2,
2436 P_GROUP26_NAME in varchar2,
2437 P_ROLE26_NAME in varchar2,
2438 P_RESOURCE27_NAME in varchar2,
2439 P_GROUP27_NAME in varchar2,
2440 P_ROLE27_NAME in varchar2,
2441 P_RESOURCE28_NAME in varchar2,
2442 P_GROUP28_NAME in varchar2,
2443 P_ROLE28_NAME in varchar2,
2444 P_RESOURCE29_NAME in varchar2,
2445 P_GROUP29_NAME in varchar2,
2446 P_ROLE29_NAME in varchar2,
2447 P_RESOURCE30_NAME in varchar2,
2448 P_GROUP30_NAME in varchar2,
2449 P_ROLE30_NAME in varchar2,
2450 P_TERR_GRP_ACCT_ID in varchar2,
2451 P_ALIGNMENT_ID in varchar2) IS
2452 BEGIN
2453
2454 POPULATE_SALESTEAM_ALIGNMENT (
2455 P_TERRITORY_GROUP,
2456 P_RESOURCE1_NAME,
2457 P_GROUP1_NAME,
2458 P_ROLE1_NAME,
2459 P_RESOURCE2_NAME,
2460 P_GROUP2_NAME,
2461 P_ROLE2_NAME,
2462 P_RESOURCE3_NAME,
2463 P_GROUP3_NAME,
2464 P_ROLE3_NAME,
2465 P_RESOURCE4_NAME,
2466 P_GROUP4_NAME,
2467 P_ROLE4_NAME,
2468 P_RESOURCE5_NAME,
2469 P_GROUP5_NAME,
2470 P_ROLE5_NAME,
2471 P_RESOURCE6_NAME,
2472 P_GROUP6_NAME,
2473 P_ROLE6_NAME,
2474 P_RESOURCE7_NAME,
2475 P_GROUP7_NAME,
2476 P_ROLE7_NAME,
2477 P_RESOURCE8_NAME,
2478 P_GROUP8_NAME,
2479 P_ROLE8_NAME,
2480 P_RESOURCE9_NAME,
2481 P_GROUP9_NAME,
2482 P_ROLE9_NAME,
2483 P_RESOURCE10_NAME,
2484 P_GROUP10_NAME,
2485 P_ROLE10_NAME,
2486 P_RESOURCE11_NAME,
2487 P_GROUP11_NAME,
2488 P_ROLE11_NAME,
2489 P_RESOURCE12_NAME,
2490 P_GROUP12_NAME,
2491 P_ROLE12_NAME,
2492 P_RESOURCE13_NAME,
2493 P_GROUP13_NAME,
2494 P_ROLE13_NAME,
2495 P_RESOURCE14_NAME,
2496 P_GROUP14_NAME,
2497 P_ROLE14_NAME,
2498 P_RESOURCE15_NAME,
2499 P_GROUP15_NAME,
2500 P_ROLE15_NAME,
2501 P_RESOURCE16_NAME,
2502 P_GROUP16_NAME,
2503 P_ROLE16_NAME,
2504 P_RESOURCE17_NAME,
2505 P_GROUP17_NAME,
2506 P_ROLE17_NAME,
2507 P_RESOURCE18_NAME,
2508 P_GROUP18_NAME,
2509 P_ROLE18_NAME,
2510 P_RESOURCE19_NAME,
2511 P_GROUP19_NAME,
2512 P_ROLE19_NAME,
2513 P_RESOURCE20_NAME,
2514 P_GROUP20_NAME,
2515 P_ROLE20_NAME,
2516 P_RESOURCE21_NAME,
2517 P_GROUP21_NAME,
2518 P_ROLE21_NAME,
2519 P_RESOURCE22_NAME,
2520 P_GROUP22_NAME,
2521 P_ROLE22_NAME,
2522 P_RESOURCE23_NAME,
2523 P_GROUP23_NAME,
2524 P_ROLE23_NAME,
2525 P_RESOURCE24_NAME,
2526 P_GROUP24_NAME,
2527 P_ROLE24_NAME,
2528 P_RESOURCE25_NAME,
2529 P_GROUP25_NAME,
2530 P_ROLE25_NAME,
2531 P_RESOURCE26_NAME,
2532 P_GROUP26_NAME,
2533 P_ROLE26_NAME,
2534 P_RESOURCE27_NAME,
2535 P_GROUP27_NAME,
2536 P_ROLE27_NAME,
2537 P_RESOURCE28_NAME,
2538 P_GROUP28_NAME,
2539 P_ROLE28_NAME,
2540 P_RESOURCE29_NAME,
2541 P_GROUP29_NAME,
2542 P_ROLE29_NAME,
2543 P_RESOURCE30_NAME,
2544 P_GROUP30_NAME,
2545 P_ROLE30_NAME,
2546 P_TERR_GRP_ACCT_ID,
2547 'Y',
2548 P_ALIGNMENT_ID);
2549
2550 END;
2551
2552 PROCEDURE POPULATE_SALES_TEAM (
2553 --P_USER_SEQUENCE in varchar2,
2554 P_NAMED_ACCOUNT in varchar2,
2555 P_SITE_TYPE in varchar2,
2556 P_TRADE_NAME in varchar2,
2557 P_DUNS in varchar2,
2558 P_GU_DUNS in varchar2,
2559 P_GU_NAME in varchar2,
2560 P_CITY in varchar2,
2561 P_STATE in varchar2,
2562 P_PROVINCE in varchar2,
2563 P_POSTAL_CODE in varchar2,
2564 P_TERRITORY_GROUP in varchar2,
2565 P_RESOURCE1_NAME in varchar2,
2566 P_GROUP1_NAME in varchar2,
2567 P_ROLE1_NAME in varchar2,
2568 P_RESOURCE2_NAME in varchar2,
2569 P_GROUP2_NAME in varchar2,
2570 P_ROLE2_NAME in varchar2,
2571 P_RESOURCE3_NAME in varchar2,
2572 P_GROUP3_NAME in varchar2,
2573 P_ROLE3_NAME in varchar2,
2574 P_RESOURCE4_NAME in varchar2,
2575 P_GROUP4_NAME in varchar2,
2576 P_ROLE4_NAME in varchar2,
2577 P_RESOURCE5_NAME in varchar2,
2578 P_GROUP5_NAME in varchar2,
2579 P_ROLE5_NAME in varchar2,
2580 P_RESOURCE6_NAME in varchar2,
2581 P_GROUP6_NAME in varchar2,
2582 P_ROLE6_NAME in varchar2,
2583 P_RESOURCE7_NAME in varchar2,
2584 P_GROUP7_NAME in varchar2,
2585 P_ROLE7_NAME in varchar2,
2586 P_RESOURCE8_NAME in varchar2,
2587 P_GROUP8_NAME in varchar2,
2588 P_ROLE8_NAME in varchar2,
2589 P_RESOURCE9_NAME in varchar2,
2590 P_GROUP9_NAME in varchar2,
2591 P_ROLE9_NAME in varchar2,
2592 P_RESOURCE10_NAME in varchar2,
2593 P_GROUP10_NAME in varchar2,
2594 P_ROLE10_NAME in varchar2,
2595 P_RESOURCE11_NAME in varchar2,
2596 P_GROUP11_NAME in varchar2,
2597 P_ROLE11_NAME in varchar2,
2598 P_RESOURCE12_NAME in varchar2,
2599 P_GROUP12_NAME in varchar2,
2600 P_ROLE12_NAME in varchar2,
2601 P_RESOURCE13_NAME in varchar2,
2602 P_GROUP13_NAME in varchar2,
2603 P_ROLE13_NAME in varchar2,
2604 P_RESOURCE14_NAME in varchar2,
2605 P_GROUP14_NAME in varchar2,
2606 P_ROLE14_NAME in varchar2,
2607 P_RESOURCE15_NAME in varchar2,
2608 P_GROUP15_NAME in varchar2,
2609 P_ROLE15_NAME in varchar2,
2610 P_RESOURCE16_NAME in varchar2,
2611 P_GROUP16_NAME in varchar2,
2612 P_ROLE16_NAME in varchar2,
2613 P_RESOURCE17_NAME in varchar2,
2614 P_GROUP17_NAME in varchar2,
2615 P_ROLE17_NAME in varchar2,
2616 P_RESOURCE18_NAME in varchar2,
2617 P_GROUP18_NAME in varchar2,
2618 P_ROLE18_NAME in varchar2,
2619 P_RESOURCE19_NAME in varchar2,
2620 P_GROUP19_NAME in varchar2,
2621 P_ROLE19_NAME in varchar2,
2622 P_RESOURCE20_NAME in varchar2,
2623 P_GROUP20_NAME in varchar2,
2624 P_ROLE20_NAME in varchar2,
2625 P_RESOURCE21_NAME in varchar2,
2626 P_GROUP21_NAME in varchar2,
2627 P_ROLE21_NAME in varchar2,
2628 P_RESOURCE22_NAME in varchar2,
2629 P_GROUP22_NAME in varchar2,
2630 P_ROLE22_NAME in varchar2,
2631 P_RESOURCE23_NAME in varchar2,
2632 P_GROUP23_NAME in varchar2,
2633 P_ROLE23_NAME in varchar2,
2634 P_RESOURCE24_NAME in varchar2,
2635 P_GROUP24_NAME in varchar2,
2636 P_ROLE24_NAME in varchar2,
2637 P_RESOURCE25_NAME in varchar2,
2638 P_GROUP25_NAME in varchar2,
2639 P_ROLE25_NAME in varchar2,
2640 P_RESOURCE26_NAME in varchar2,
2641 P_GROUP26_NAME in varchar2,
2642 P_ROLE26_NAME in varchar2,
2643 P_RESOURCE27_NAME in varchar2,
2644 P_GROUP27_NAME in varchar2,
2645 P_ROLE27_NAME in varchar2,
2646 P_RESOURCE28_NAME in varchar2,
2647 P_GROUP28_NAME in varchar2,
2648 P_ROLE28_NAME in varchar2,
2649 P_RESOURCE29_NAME in varchar2,
2650 P_GROUP29_NAME in varchar2,
2651 P_ROLE29_NAME in varchar2,
2652 P_RESOURCE30_NAME in varchar2,
2653 P_GROUP30_NAME in varchar2,
2654 P_ROLE30_NAME in varchar2,
2655 P_TERR_GRP_ACCT_ID in varchar2
2656 ) IS
2657 BEGIN
2658
2659 POPULATE_SALESTEAM_ALIGNMENT (
2660 P_TERRITORY_GROUP,
2661 P_RESOURCE1_NAME,
2662 P_GROUP1_NAME,
2663 P_ROLE1_NAME,
2664 P_RESOURCE2_NAME,
2665 P_GROUP2_NAME ,
2666 P_ROLE2_NAME ,
2667 P_RESOURCE3_NAME ,
2668 P_GROUP3_NAME ,
2669 P_ROLE3_NAME ,
2670 P_RESOURCE4_NAME ,
2671 P_GROUP4_NAME ,
2672 P_ROLE4_NAME ,
2673 P_RESOURCE5_NAME ,
2674 P_GROUP5_NAME ,
2675 P_ROLE5_NAME ,
2676 P_RESOURCE6_NAME ,
2677 P_GROUP6_NAME ,
2678 P_ROLE6_NAME ,
2679 P_RESOURCE7_NAME ,
2680 P_GROUP7_NAME ,
2681 P_ROLE7_NAME ,
2682 P_RESOURCE8_NAME ,
2683 P_GROUP8_NAME ,
2684 P_ROLE8_NAME ,
2685 P_RESOURCE9_NAME ,
2686 P_GROUP9_NAME ,
2687 P_ROLE9_NAME ,
2688 P_RESOURCE10_NAME ,
2689 P_GROUP10_NAME ,
2690 P_ROLE10_NAME ,
2691 P_RESOURCE11_NAME ,
2692 P_GROUP11_NAME ,
2693 P_ROLE11_NAME ,
2694 P_RESOURCE12_NAME ,
2695 P_GROUP12_NAME ,
2696 P_ROLE12_NAME ,
2697 P_RESOURCE13_NAME ,
2698 P_GROUP13_NAME ,
2699 P_ROLE13_NAME ,
2700 P_RESOURCE14_NAME ,
2701 P_GROUP14_NAME ,
2702 P_ROLE14_NAME ,
2703 P_RESOURCE15_NAME ,
2704 P_GROUP15_NAME ,
2705 P_ROLE15_NAME ,
2706 P_RESOURCE16_NAME ,
2707 P_GROUP16_NAME ,
2708 P_ROLE16_NAME ,
2709 P_RESOURCE17_NAME ,
2710 P_GROUP17_NAME ,
2711 P_ROLE17_NAME ,
2712 P_RESOURCE18_NAME ,
2713 P_GROUP18_NAME ,
2714 P_ROLE18_NAME ,
2715 P_RESOURCE19_NAME ,
2716 P_GROUP19_NAME ,
2717 P_ROLE19_NAME ,
2718 P_RESOURCE20_NAME ,
2719 P_GROUP20_NAME ,
2720 P_ROLE20_NAME ,
2721 P_RESOURCE21_NAME ,
2722 P_GROUP21_NAME ,
2723 P_ROLE21_NAME ,
2724 P_RESOURCE22_NAME ,
2725 P_GROUP22_NAME ,
2726 P_ROLE22_NAME ,
2727 P_RESOURCE23_NAME ,
2728 P_GROUP23_NAME ,
2729 P_ROLE23_NAME ,
2730 P_RESOURCE24_NAME ,
2731 P_GROUP24_NAME ,
2732 P_ROLE24_NAME ,
2733 P_RESOURCE25_NAME ,
2734 P_GROUP25_NAME ,
2735 P_ROLE25_NAME ,
2736 P_RESOURCE26_NAME ,
2737 P_GROUP26_NAME ,
2738 P_ROLE26_NAME ,
2739 P_RESOURCE27_NAME ,
2740 P_GROUP27_NAME ,
2741 P_ROLE27_NAME ,
2742 P_RESOURCE28_NAME ,
2743 P_GROUP28_NAME ,
2744 P_ROLE28_NAME ,
2745 P_RESOURCE29_NAME ,
2746 P_GROUP29_NAME ,
2747 P_ROLE29_NAME ,
2748 P_RESOURCE30_NAME ,
2749 P_GROUP30_NAME ,
2750 P_ROLE30_NAME ,
2751 P_TERR_GRP_ACCT_ID ,
2752 'N', -- P_ALIGNMENT_FLAG ,
2753 null --P_ALIGNMENT_ID
2754 );
2755
2756 END;
2757
2758
2759 END JTF_TTY_WEBADI_salsteam_update;