[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_GEO_TERRGP
Source
1 PACKAGE BODY JTF_TTY_GEO_TERRGP AS
2 /* $Header: jtftggpb.pls 120.6 2006/09/11 20:41:16 spai ship $ */
3 -- Start of Comments
4 -- PURPOSE
5 -- For handling Geography Territor Groups, like delete,create,update
6 --
7 -- NOTES
8 -- ORACLE INTERNAL USE ONLY: NOT for customer use
9 --
10 -- HISTORY
11 -- 06/02/03 SGKUMAR Created
12 -- 06/20/03 SGKUMAR Modified as per new data model
13 -- 06/24/03 SGKUMAR Added new procedure add_geo_to_grp
14 -- 12/23/03 ACHANDA Added the log_event procedure and also call to it while deleting TG
15 -- 01/07/04 SGKUMAR Checking Postal Code ranges by geo name and not id
16 -- 11/09/04 SGKUMAR Added procedure replace_geo_terr_rsc for 3889970
17 -- 09/22/05 JRADHAKR Added procedure POPULATE_SELF_SRV_SCHEMA to populate
18 -- TTY tables for self service geo territories.
19 -- End of Comments
20 -- End of Comments
21 ----
22
23 /* Procedure to populate the TTY table from TERR tables
24 for the self service geo territories */
25
26 PROCEDURE POPULATE_SELF_SRV_SCHEMA (p_terr_id IN NUMBER
27 , x_return_status OUT NOCOPY VARCHAR2
28 , x_msg_count OUT NOCOPY VARCHAR2
29 , x_msg_data OUT NOCOPY VARCHAR2)
30 IS
31 l_terr_grp_id NUMBER;
32 l_geo_terr_id NUMBER;
33 lp_terr_grp_role_id NUMBER;
34
35 L_GEO_GRP_VALUES_ID NUMBER;
36 L_GEO_ID_FROM NUMBER;
37 L_GEO_ID_TO NUMBER;
38
39 l_return_status VARCHAR2(2);
40
41
42 /* Cursor to get the roles defined for the self service geo territories */
43
44 CURSOR csr_get_terr_grp_roles(cr_terr_id IN NUMBER) IS
45 select terr_rsc_id
46 , resource_id
47 , role
48 , resource_type
49 , creation_date
50 , created_by
51 , last_update_date
52 , last_updated_by
53 from jtf_terr_rsc_all
54 where terr_id = cr_terr_id
55 and resource_type = 'RS_ROLE'; -- Need to Fix before ARCS
56
57 /* Cursor that convert the TERR go values to TTY geo values */
58
59 CURSOR csr_get_terr_grp_values(cr_terr_id IN NUMBER) IS
60 select decode(jtq.qual_usg_id, -1007, 'POSTAL_CODE'
61 , -1003, 'COUNTRY', -1013, 'PROVINCE', -1011, 'COUNTY'
62 , -1008, 'STATE', -1006,'CITY') geo_type
63 , jtv.comparison_operator
64 , jtv.low_value_char
65 , jtv.high_value_char
66 , jtq.terr_id
67 , jtv.creation_date
68 , jtv.created_by
69 , jtv.last_update_date
70 , jtv.last_updated_by
71 from jtf_terr_values_all jtv
72 , jtf_terr_qual_all jtq
73 , jtf_qual_usgs_all qsg
74 where jtv.terr_qual_id = jtq.terr_qual_id
75 and jtq.terr_id = cr_terr_id
76 and jtq.qual_usg_id = qsg.qual_usg_id
77 and jtq.org_id = qsg.org_id
78 and qsg.hierarchy_type = 'GEOGRAPHY' ;
79
80 BEGIN
81
82 l_return_status := 'S' ;
83
84 BEGIN
85 select geo_territory_id, terr_group_id
86 into l_geo_terr_id, l_terr_grp_id
87 from jtf_terr_all
88 where terr_id = p_terr_id;
89
90 EXCEPTION
91 when FND_API.G_EXC_ERROR then
92 x_return_status := 'E';
93 x_msg_data := substr(sqlerrm, 1, 200) ;
94 return;
95
96 when others then
97 x_return_status := 'E';
98 x_msg_data := substr(sqlerrm, 1, 200) ;
99 return;
100
101 END;
102
103 if l_geo_terr_id is not null
104 then
105
106 /* following code deletes the data first in case of an update and
107 coninue with the create statements */
108
109 delete from jtf_tty_role_access
110 where TERR_GROUP_ROLE_ID in (select TERR_GROUP_ROLE_ID
111 from jtf_tty_terr_grp_roles
112 where terr_group_id = l_terr_grp_id);
113
114 delete from jtf_tty_terr_grp_roles where terr_group_id = l_terr_grp_id;
115
116 delete from jtf_tty_geo_grp_values where terr_group_id = l_terr_grp_id;
117
118 delete from jtf_tty_terr_groups where terr_group_id = l_terr_grp_id;
119
120 delete from jtf_tty_geo_terr_rsc where geo_territory_id = l_geo_terr_id;
121
122 delete from jtf_tty_geo_terr where geo_territory_id = l_geo_terr_id;
123
124
125 else
126 select jtf_tty_terr_groups_s.nextval
127 into l_terr_grp_id
128 from dual;
129
130 select jtf_tty_geo_terr_s.nextval
131 into l_geo_terr_id
132 from dual;
133
134 end if;
135
136 BEGIN
137
138
139 INSERT INTO jtf_tty_terr_groups
140 ( TERR_GROUP_ID
141 , TERR_GROUP_NAME
142 , RANK
143 , ACTIVE_FROM_DATE
144 , ACTIVE_TO_DATE
145 , PARENT_TERR_ID
146 , CREATED_BY
147 , CREATION_DATE
148 , LAST_UPDATED_BY
149 , LAST_UPDATE_DATE
150 , LAST_UPDATE_LOGIN
151 , NUM_WINNERS
152 , SELF_SERVICE_TYPE
153 , DESCRIPTION
154 , OBJECT_VERSION_NUMBER
155 )
156 select l_terr_grp_id
157 , name
158 , RANK
159 , START_DATE_ACTIVE
160 , END_DATE_ACTIVE
161 , PARENT_TERRITORY_ID
162 , CREATED_BY
163 , CREATION_DATE
164 , LAST_UPDATED_BY
165 , LAST_UPDATE_DATE
166 , LAST_UPDATE_LOGIN
167 , NUM_WINNERS
168 , 'GEOGRAPHY'
169 , DESCRIPTION
170 , 1
171 from jtf_terr_all
172 where terr_id = p_terr_id;
173
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 NULL;
177 WHEN OTHERS THEN
178 x_return_status := 'E';
179 x_msg_data := substr(sqlerrm, 1, 200) ;
180 return;
181
182 END;
183
184 BEGIN
185
186
187 insert into jtf_tty_geo_terr
188 (geo_territory_id,
189 parent_geo_terr_id,
190 child_node_flag,
191 geo_terr_name,
192 terr_group_id,
193 owner_resource_id ,
194 owner_rsc_group_id,
195 owner_rsc_role_code,
196 OBJECT_VERSION_NUMBER,
197 created_by,
198 creation_date,
199 last_updated_by,
200 last_update_date)
201 select l_geo_terr_id
202 ,- l_geo_terr_id
203 ,'N'
204 ,name
205 ,l_terr_grp_id
206 ,-999
207 ,-999
208 ,-999
209 ,1
210 , CREATED_BY
211 , CREATION_DATE
212 , LAST_UPDATED_BY
213 , LAST_UPDATE_DATE
214 from jtf_terr_all
215 where terr_id = p_terr_id;
216
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 NULL;
220 WHEN OTHERS THEN
221 x_return_status := 'E';
222 x_msg_data := substr(sqlerrm, 1, 200) ;
223 return;
224
225 END;
226
227 BEGIN
228
229 insert into jtf_tty_geo_terr_rsc
230 (geo_terr_resource_id,
231 object_version_number,
232 geo_territory_id,
233 resource_id,
234 rsc_group_id,
235 rsc_role_code,
236 rsc_resource_type,
237 assigned_flag,
238 created_by,
239 creation_date,
240 last_updated_by,
241 last_update_date,
242 LAST_UPDATE_LOGIN)
243 SELECT jtf_tty_geo_terr_rsc_s.nextval
244 , 1
245 , l_geo_terr_id
246 , resource_id
247 , group_id
248 , role
249 , resource_type
250 , 'N'
251 , CREATED_BY
252 , CREATION_DATE
253 , LAST_UPDATED_BY
254 , LAST_UPDATE_DATE
255 , LAST_UPDATE_LOGIN
256 FROM jtf_terr_rsc_all
257 where terr_id = p_terr_id
258 and resource_type = 'RS_EMPLOYEE';
259
260 EXCEPTION
261 WHEN NO_DATA_FOUND THEN
262 NULL;
263 WHEN OTHERS THEN
264 x_return_status := 'E';
265 x_msg_data := substr(sqlerrm, 1, 200) ;
266 return;
267
268 END;
269
270 BEGIN
271
272 Insert into jtf_tty_terr_grp_owners
273 ( TERR_GROUP_OWNER_ID
274 , OBJECT_VERSION_NUMBER
275 , TERR_GROUP_ID
276 , RSC_GROUP_ID
277 , RESOURCE_ID
278 , RSC_ROLE_CODE
279 , RSC_RESOURCE_TYPE
280 , CREATED_BY
281 , CREATION_DATE
282 , LAST_UPDATED_BY
283 , LAST_UPDATE_DATE
284 , LAST_UPDATE_LOGIN
285 )
286 SELECT jtf_tty_terr_grp_owners_s.nextval
287 , 1
288 , l_terr_grp_id
289 , group_id
290 , resource_id
291 , role
292 , resource_type
293 , CREATED_BY
294 , CREATION_DATE
295 , LAST_UPDATED_BY
296 , LAST_UPDATE_DATE
297 , LAST_UPDATE_LOGIN
298 FROM jtf_terr_rsc_all
299 where terr_id = p_terr_id
300 and resource_type = 'RS_EMPLOYEE';
301
302 EXCEPTION
303 WHEN NO_DATA_FOUND THEN
304 NULL;
305 WHEN OTHERS THEN
306 x_return_status := 'E';
307 x_msg_data := substr(sqlerrm, 1, 200) ;
308 return;
309
310
311 END;
312
313 for get_terr_grp_values in csr_get_terr_grp_values (p_terr_id)
314 --
315 loop
316 --
317 BEGIN
318
319 IF get_terr_grp_values.geo_type = 'POSTAL_CODE'
320 THEN
321 select geo_id
322 into L_GEO_ID_FROM
323 from jtf_tty_geographies
324 where geo_type = get_terr_grp_values.geo_type
325 and geo_code = (
326 select min(geo_code)
327 from jtf_tty_geographies
328 where geo_type = get_terr_grp_values.geo_type
329 and geo_code >= get_terr_grp_values.low_value_char
330 and geo_code <= get_terr_grp_values.high_value_char);
331
332 Begin
333 select geo_id
334 into L_GEO_ID_TO
335 from jtf_tty_geographies
336 where geo_type = get_terr_grp_values.geo_type
337 and geo_code = (
338 select max(geo_code)
339 from jtf_tty_geographies
340 where geo_type = get_terr_grp_values.geo_type
341 and geo_code <= get_terr_grp_values.high_value_char
342 and geo_code >= get_terr_grp_values.low_value_char);
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 NULL;
346 END;
347 ELSE -- not postal code
348 select geo_id
349 into L_GEO_ID_FROM
350 from jtf_tty_geographies
351 where geo_type = get_terr_grp_values.geo_type
352 and geo_code = get_terr_grp_values.low_value_char
353 and rownum < 2;
354
355 Begin
356 select geo_id
357 into L_GEO_ID_TO
358 from jtf_tty_geographies
359 where geo_type = get_terr_grp_values.geo_type
360 and geo_code = get_terr_grp_values.high_value_char
361 and rownum < 2;
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 NULL;
365 END;
366 END IF;
367
368 select jtf_tty_geo_grp_values_s.nextval
369 into L_GEO_GRP_VALUES_ID
370 from dual;
371
372 insert into jtf_tty_geo_grp_values (
373 GEO_GRP_VALUES_ID
374 , OBJECT_VERSION_NUMBER
375 , TERR_GROUP_ID
376 , COMPARISON_OPERATOR
377 , GEO_TYPE
378 , GEO_ID_FROM
379 , GEO_ID_TO
380 , CREATED_BY
381 , CREATION_DATE
382 , LAST_UPDATED_BY
383 , LAST_UPDATE_DATE )
384 VALUES
385 (
386 L_GEO_GRP_VALUES_ID
387 , 1
388 , l_terr_grp_id
389 , get_terr_grp_values.comparison_operator
390 , get_terr_grp_values.geo_type
391 , L_GEO_ID_FROM
392 , L_GEO_ID_TO
393 , get_terr_grp_values.CREATED_BY
394 , get_terr_grp_values.CREATION_DATE
395 , get_terr_grp_values.LAST_UPDATED_BY
396 , get_terr_grp_values.LAST_UPDATE_DATE
397 );
398
399 EXCEPTION
400 WHEN NO_DATA_FOUND THEN
401 x_return_status := 'E';
402
403 FND_MESSAGE.Set_Name('JTF', 'JTF_TTY_NO_GEO_VALUES');
404 FND_MSG_PUB.Add;
405 FND_MSG_PUB.Count_And_Get
406 ( p_count => x_msg_count,
407 p_data => x_msg_data
408 );
409 return;
410
411 WHEN OTHERS THEN
412 x_return_status := 'E';
413 x_msg_data := substr(sqlerrm, 1, 200) ;
414 return;
415
416 END;
417
418 end loop;
419
420 for get_terr_grp_roles in csr_get_terr_grp_roles (p_terr_id)
421 --
422 loop
423 --
424 select jtf_tty_terr_grp_roles_s.nextval
425 into lp_terr_grp_role_id
426 from dual;
427 --
428 BEGIN
429
430 insert into jtf_tty_terr_grp_roles (
431 TERR_GROUP_ROLE_ID
432 ,TERR_GROUP_ID
433 ,ROLE_CODE
434 ,OBJECT_VERSION_NUMBER
435 ,CREATED_BY
436 ,CREATION_DATE
437 ,LAST_UPDATED_BY
438 ,LAST_UPDATE_DATE)
439 values(
440 lp_terr_grp_role_id
441 ,l_terr_grp_id
442 ,get_terr_grp_roles.ROLE
443 , 1
444 ,get_terr_grp_roles.CREATED_BY
445 ,get_terr_grp_roles.creation_date
446 ,get_terr_grp_roles.LAST_UPDATED_BY
447 ,get_terr_grp_roles.LAST_UPDATE_DATE);
448
449 EXCEPTION
450 WHEN NO_DATA_FOUND THEN
451 NULL;
452 WHEN OTHERS THEN
453 x_return_status := 'E';
454 x_msg_data := substr(sqlerrm, 1, 200) ;
455 return;
456
457 END;
458 --
459 BEGIN
460 --
461 insert into jtf_tty_role_access (
462 TERR_GROUP_ROLE_ACCESS_ID
463 ,TERR_GROUP_ROLE_ID
464 ,ACCESS_TYPE
465 ,OBJECT_VERSION_NUMBER
466 ,CREATED_BY
467 ,CREATION_DATE
468 ,LAST_UPDATED_BY
469 ,LAST_UPDATE_DATE)
470 select
471 jtf_tty_role_access_s.nextval
472 ,lp_terr_grp_role_id
473 ,ACCESS_TYPE
474 , 1
475 ,CREATED_BY
476 ,CREATION_DATE
477 ,LAST_UPDATED_BY
478 ,LAST_UPDATE_DATE
479 from jtf_terr_rsc_access_all
480 where terr_rsc_id = get_terr_grp_roles.terr_rsc_id;
481
482 EXCEPTION
483 WHEN NO_DATA_FOUND THEN
484 NULL;
485 WHEN OTHERS THEN
486 x_return_status := 'E';
487 x_msg_data := substr(sqlerrm, 1, 200) ;
488 return;
489
490 END;
491
492 --
493 end loop;
494 --
495 commit;
496
497 BEGIN
498 --
499 update jtf_terr_all
500 set terr_group_id = l_terr_grp_id
501 , terr_group_flag = 'Y'
502 , catch_all_flag = 'N'
503 , geo_territory_id = l_geo_terr_id
504 where terr_id = p_terr_id;
505
506 EXCEPTION
507 WHEN NO_DATA_FOUND THEN
508 NULL;
509 WHEN OTHERS THEN
510 x_return_status := 'E';
511 x_msg_data := substr(sqlerrm, 1, 200) ;
512 return;
513
514 END;
515
516 commit;
517 x_return_status := 'S';
518
519 EXCEPTION
520 when FND_API.G_EXC_ERROR then
521
522 x_return_status := 'E';
523 x_msg_data := substr(sqlerrm, 1, 200) ;
524 return;
525
526 when others then
527 x_return_status := 'E';
528 x_msg_data := substr(sqlerrm, 1, 200) ;
529 return;
530
531 END POPULATE_SELF_SRV_SCHEMA;
532
533
534 PROCEDURE log_event(p_object_id IN NUMBER,
535 p_action_type IN VARCHAR2,
536 p_from_where IN VARCHAR2,
537 p_object_type IN VARCHAR2,
538 p_user_id in NUMBER)
539 IS
540 BEGIN
541 INSERT INTO JTF_TTY_NAMED_ACCT_CHANGES(
542 NAMED_ACCT_CHANGE_ID,
543 OBJECT_VERSION_NUMBER,
544 OBJECT_TYPE,
545 OBJECT_ID,
546 CHANGE_TYPE,
547 FROM_WHERE,
548 CREATED_BY,
549 CREATION_DATE,
550 LAST_UPDATED_BY,
551 LAST_UPDATE_DATE
552 )
553 VALUES(JTF_TTY_NAMED_ACCT_CHANGES_S.nextval,
554 1,
555 p_object_type,
556 p_object_id,
557 p_action_type,
558 p_from_where,
559 p_user_id,
560 sysdate,
561 p_user_id,
562 sysdate);
563
564 END log_event;
565
566 PROCEDURE delete_terrgp(p_terr_gp_id IN NUMBER)
567 AS
568 p_user_id NUMBER;
569 BEGIN
570
571 /* delete the geos or postal code belonging to the geo territores of this
572 /* geo territory group */
573
574 DELETE from JTF_TTY_GEO_TERR_VALUES
575 WHERE geo_territory_id IN
576 (SELECT t.geo_territory_id
577 FROM jtf_tty_geo_terr t
578 WHERE t.terr_group_id = p_terr_gp_id);
579
580 /* delete all the geo territories assignments for the geo terr group */
581
582 DELETE from JTF_TTY_GEO_TERR_RSC
583 WHERE geo_territory_id IN
584 (SELECT t.geo_territory_id
585 FROM jtf_tty_geo_terr t
586 WHERE t.terr_group_id = p_terr_gp_id);
587
588 DELETE from JTF_TTY_GEO_TERR
589 WHERE terr_group_id = p_terr_gp_id;
590
591 /* delete all the geographies for the geo terr group */
592
593 DELETE from JTF_TTY_GEO_GRP_VALUES
594 WHERE terr_group_id = p_terr_gp_id;
595
596
597 /* delete all the terr gp owners, access and product */
598 delete from jtf_tty_terr_grp_owners
599 where terr_group_id = p_terr_gp_id;
600
601 delete from jtf_tty_role_prod_int
602 where terr_group_role_id in
603 (select terr_group_role_id from jtf_tty_terr_grp_roles
604 where terr_group_id = p_terr_gp_id);
605
606
607 delete from jtf_tty_role_access
608 where terr_group_role_id in
609 (select terr_group_role_id from jtf_tty_terr_grp_roles
610 where terr_group_id = p_terr_gp_id);
611
612 delete from jtf_tty_terr_grp_roles
613 where terr_group_id = p_terr_gp_id;
614
615 /* finally delete the terr gp itself */
616
617 delete from jtf_tty_terr_groups
618 where terr_group_id = p_terr_gp_id;
619
620 /* ACHANDA : added to log the event of territory group delete for GTP to do incremental process */
621 log_event(p_terr_gp_id, 'DELETE', 'Delete Territory Group', 'TG', fnd_global.user_id);
622 commit;
623 END delete_terrgp;
624 /*
625 * Adds the geography to the geo terr group
626 * Invoked during create or update of geo terr group
627 */
628 PROCEDURE delete_geo_from_grp(p_terr_gp_id IN NUMBER)
629 AS
630 BEGIN
631 DELETE from jtf_tty_geo_grp_values
632 where TERR_GROUP_ID = p_terr_gp_id;
633
634 COMMIT;
635 END delete_geo_from_grp;
636 /*
637 * Adds the geography to the geo terr group
638 * Invoked during create or update of geo terr group
639 */
640 PROCEDURE add_geo_to_grp(p_terr_gp_id IN NUMBER,
641 p_geo_id_from IN NUMBER,
642 p_geo_id_to IN NUMBER,
643 p_operator IN VARCHAR2,
644 p_geo_type IN VARCHAR2,
645 p_user_id IN NUMBER)
646 AS
647 BEGIN
648
649 INSERT into jtf_tty_geo_grp_values(
650 GEO_GRP_VALUES_ID,
651 OBJECT_VERSION_NUMBER,
652 TERR_GROUP_ID,
653 COMPARISON_OPERATOR,
654 GEO_TYPE,
655 GEO_ID_FROM,
656 GEO_ID_TO,
657 CREATED_BY,
658 CREATION_DATE,
659 LAST_UPDATED_BY,
660 last_update_date)
661 VALUES(
662 jtf_tty_geo_grp_values_s.nextval,
663 1,
664 p_terr_gp_id,
665 p_operator,
666 p_geo_type,
667 p_geo_id_from,
668 p_geo_id_to,
669 p_user_id,
670 sysdate,
671 p_user_id,
672 sysdate);
673
674 COMMIT;
675 END add_geo_to_grp;
676 /*
677 * create a top level geo territory for the geo terr group
678 * and assigns it to the owners of the geo terr group
679 * Invoked during create geo terr group
680 */
681 PROCEDURE create_grp_geo_terr(p_terr_gp_id IN NUMBER,
682 p_user_id IN NUMBER)
683 AS
684 p_geo_territory_id NUMBER;
685 p_geo_territory_name VARCHAR2(80);
686 p_territory_label VARCHAR2(80);
687 BEGIN
688 SELECT jtf_tty_geo_terr_s.nextval, terr_group_name
689 INTO p_geo_territory_id, p_geo_territory_name
690 FROM jtf_tty_terr_groups
691 WHERE terr_group_id = p_terr_gp_id;
692 /*
693 fnd_message.set_name('JTF', 'JTF_TTY_TERR_LABEL');
694 p_territory_label := fnd_message.Get();
695 */
696 /* create a top-level geo territory */
697 insert into jtf_tty_geo_terr
698 (geo_territory_id,
699 parent_geo_terr_id,
700 object_version_number,
701 child_node_flag,
702 geo_terr_name,
703 terr_group_id,
704 owner_resource_id ,
705 owner_rsc_group_id,
706 owner_rsc_role_code,
707 created_by,
708 creation_date,
709 last_updated_by,
710 last_update_date)
711 values( p_geo_territory_id,
712 - p_geo_territory_id,
713 1,
714 'N',
715 p_geo_territory_name,
716 p_terr_gp_id,
717 -999,
718 -999,
719 -999,
720 p_user_id,
721 sysdate,
722 p_user_id,
723 sysdate);
724 /* Assign the top level territory to all the geo terr gp owners */
725 insert into jtf_tty_geo_terr_rsc
726 (geo_terr_resource_id,
727 object_version_number,
728 geo_territory_id,
729 resource_id,
730 rsc_group_id,
731 rsc_role_code,
732 assigned_flag,
733 created_by,
734 creation_date,
735 last_updated_by,
736 last_update_date)
737 SELECT jtf_tty_geo_terr_rsc_s.nextval,
738 1,
739 p_geo_territory_id,
740 tgo.resource_id,
741 tgo.rsc_group_id,
742 tgo.rsc_role_code,
743 'N',
744 p_user_id,
745 sysdate,
746 p_user_id,
747 sysdate
748 FROM jtf_tty_terr_grp_owners tgo
749 WHERE tgo.terr_group_id = p_terr_gp_id;
750
751 COMMIT;
752 END create_grp_geo_terr;
753 /*
754 * Deletes the removed geographies from all the geo territories
755 * belong to this geo terr group
756 */
757 PROCEDURE delete_geos_from_terrs(p_terr_gp_id IN NUMBER)
758 AS
759 BEGIN
760 DELETE from JTF_TTY_GEO_TERR_VALUES gtv
761 WHERE gtv.geo_territory_id IN
762 (SELECT geo_territory_id FROM jtf_tty_geo_terr
763 where terr_group_id = p_terr_gp_id)
764 AND gtv.geo_id NOT IN
765 (SELECT g.geo_id FROM jtf_tty_geographies g, jtf_tty_geo_grp_values ggv, jtf_tty_geographies g1
766 WHERE ggv.terr_group_id = p_terr_gp_id
767 AND ggv.geo_type = 'COUNTRY'
768 AND ggv.geo_id_from = g1.geo_id
769 AND g.geo_type = 'POSTAL_CODE'
770 AND g.country_code = g1.country_code
771 UNION
772 SELECT g.geo_id FROM jtf_tty_geographies g, jtf_tty_geo_grp_values ggv, jtf_tty_geographies g1
773 WHERE ggv.terr_group_id = p_terr_gp_id
774 AND ggv.geo_type = 'STATE'
775 AND ggv.geo_id_from = g1.geo_id
776 AND g.geo_type = 'POSTAL_CODE'
777 AND g.country_code = g1.country_code
778 AND g.state_code = g1.state_code
779 UNION
780 SELECT g.geo_id FROM jtf_tty_geographies g, jtf_tty_geo_grp_values ggv, jtf_tty_geographies g1
781 WHERE ggv.terr_group_id = p_terr_gp_id
782 AND ggv.geo_type = 'PROVINCE'
783 AND ggv.geo_id_from = g1.geo_id
784 AND g.geo_type = 'POSTAL_CODE'
785 AND g.country_code = g1.country_code
786 AND g.province_code = g1.province_code
787 UNION
788 SELECT g.geo_id FROM jtf_tty_geographies g, jtf_tty_geo_grp_values ggv, jtf_tty_geographies g1
789 WHERE ggv.terr_group_id = p_terr_gp_id
790 AND ggv.geo_type = 'CITY'
791 AND ggv.geo_id_from = g1.geo_id
792 AND g.geo_type = 'POSTAL_CODE'
793 AND g.country_code = g1.country_code
794 AND ((g.state_code = g1.state_code AND g1.province_code is null)
795 or
796 (g1.province_code = g.province_code AND g1.state_code is null))
797 AND (g1.county_code is null or g.county_code = g1.county_code)
798 AND g.city_code = g1.city_code
799 UNION
800 SELECT ggv.geo_id_from FROM jtf_tty_geo_grp_values ggv
801 WHERE ggv.terr_group_id = p_terr_gp_id
802 AND ggv.geo_type = 'POSTAL_CODE'
803 AND ggv.comparison_operator = '='
804 UNION
805 SELECT g.geo_id
806 FROM jtf_tty_geographies g,
807 jtf_tty_geo_grp_values ggv,
808 jtf_tty_geographies g1,
809 jtf_tty_geographies g2
810 WHERE ggv.terr_group_id = p_terr_gp_id
811 AND ggv.geo_type = 'POSTAL_CODE'
812 AND ggv.comparison_operator = 'BETWEEN'
813 AND g1.geo_id = ggv.geo_id_from
814 AND g2.geo_id = ggv.geo_id_to
815 AND g.geo_name BETWEEN g1.geo_name and g2.geo_name);
816
817 commit;
818
819
820 END delete_geos_from_terrs;
821 /*
822 * Updates the geo terr assinments for removed and added owners
823 * of a geo territory group, invoked only for update geo terr group
824 * and if owners are updated
825 */
826 PROCEDURE update_geo_grp_assignments (p_terr_gp_id IN NUMBER)
827 AS
828 CURSOR removed_owners_c IS
829 SELECT gtr.resource_id,
830 gtr.rsc_group_id,
831 gtr.rsc_role_code,
832 gtr.geo_territory_id
833 FROM jtf_tty_geo_terr_rsc gtr,
834 jtf_tty_geo_terr gt
835 WHERE gt.terr_group_id = p_terr_gp_id
836 AND gt.geo_territory_id = gtr.geo_territory_id
837 AND gt.owner_resource_id = -999
838 AND gtr.rsc_group_id
839 NOT IN (SELECT tgo.rsc_group_id
840 FROM jtf_tty_terr_grp_owners tgo
841 WHERE tgo.terr_group_id = p_terr_gp_id);
842
843 CURSOR replaced_owners_c IS
844 SELECT tgo1.resource_id new_owner_resource_id,
845 gtr.rsc_group_id,
846 gtr.rsc_role_code,
847 gtr.geo_territory_id,
848 gtr.resource_id replaced_owner_resource_id
849 FROM jtf_tty_geo_terr_rsc gtr,
850 jtf_tty_geo_terr gt,
851 jtf_tty_terr_grp_owners tgo1
852 WHERE gt.terr_group_id = p_terr_gp_id
853 AND gt.geo_territory_id = gtr.geo_territory_id
854 AND gt.owner_resource_id = -999
855 and tgo1.terr_group_id = p_terr_gp_id
856 and tgo1.rsc_group_id = gtr.rsc_group_id
857 and gtr.resource_id <> tgo1.resource_id;
858
859 CURSOR added_owners_c IS
860 SELECT tgo.resource_id,
861 tgo.rsc_group_id,
862 tgo.rsc_role_code,
863 gt.geo_territory_id
864 FROM JTF_TTY_TERR_GRP_OWNERS tgo,
865 jtf_tty_geo_terr gt
866 WHERE gt.terr_group_id = p_terr_gp_id
867 AND tgo.terr_group_id = p_terr_gp_id
868 AND gt.owner_resource_id = -999
869 AND (tgo.resource_id, tgo.rsc_group_id, tgo.rsc_role_code)
870 NOT IN (SELECT gtr.resource_id, gtr.rsc_group_id, gtr.rsc_role_code
871 FROM jtf_tty_geo_terr_rsc gtr
872 WHERE gt.geo_territory_id = gtr.geo_territory_id);
873 BEGIN
874 for removed_owners IN removed_owners_c LOOP
875 delete_geo_terr_rsc(removed_owners.geo_territory_id,
876 removed_owners.resource_id,
877 removed_owners.rsc_group_id,
878 removed_owners.rsc_role_code);
879 END LOOP;
880 for added_owners IN added_owners_c LOOP
881 assign_geo_terr(added_owners.geo_territory_id,
882 added_owners.resource_id,
883 added_owners.rsc_group_id,
884 added_owners.rsc_role_code);
885 END LOOP;
886 for replaced_owners IN replaced_owners_c LOOP
887 replace_geo_terr_rsc(replaced_owners.geo_territory_id,
888 replaced_owners.new_owner_resource_id,
889 replaced_owners.rsc_group_id,
890 replaced_owners.rsc_role_code,
891 replaced_owners.replaced_owner_resource_id);
892 END LOOP;
893
894
895
896 END update_geo_grp_assignments;
897 /*
898 * delete the geo terr assignments for removed owner/Sales Rep
899 * for the given geo territory and all the children geo territories
900 */
901 PROCEDURE delete_geo_terr_rsc (p_territory_id IN NUMBER,
902 p_resource_id IN NUMBER,
903 p_rsc_group_id IN NUMBER,
904 p_rsc_role_code IN VARCHAR2)
905 AS
906 BEGIN
907 /* Delete goes for the geo terrs assigned by the given resource and down
908 * from the given territory */
909 DELETE from JTF_TTY_GEO_TERR_VALUES gtv
910 WHERE gtv.geo_territory_id IN
911 (SELECT gt.geo_territory_id
912 FROM JTF_TTY_GEO_TERR gt
913 START WITH gt.geo_territory_id IN
914 (SELECT gt1.geo_territory_id
915 FROM JTF_TTY_GEO_TERR gt1
916 WHERE gt1.owner_resource_id = p_resource_id
917 AND gt1.owner_rsc_group_id = p_rsc_group_id
918 AND gt1.owner_rsc_role_code = p_rsc_role_code
919 AND gt1.parent_geo_terr_id = p_territory_id)
920 CONNECT BY PRIOR gt.geo_territory_id = gt.parent_geo_terr_id);
921 /* Delete goes for the geo terrs created by the given resource
922 * from the given territory */
923 DELETE from JTF_TTY_GEO_TERR_VALUES gtv
924 WHERE gtv.geo_territory_id IN
925 (SELECT gt1.geo_territory_id
926 FROM JTF_TTY_GEO_TERR gt1
927 WHERE gt1.owner_resource_id = p_resource_id
928 AND gt1.owner_rsc_group_id = p_rsc_group_id
929 AND gt1.owner_rsc_role_code = p_rsc_role_code
930 AND gt1.parent_geo_terr_id = p_territory_id);
931
932 /* Delete for the geo terrs assignments by the given resource and down
933 * from the given territory */
934 DELETE from JTF_TTY_GEO_TERR_RSC gtr
935 WHERE gtr.geo_territory_id IN
936 (SELECT gt.geo_territory_id
937 FROM JTF_TTY_GEO_TERR gt
938 START WITH gt.geo_territory_id IN
939 (SELECT gt1.geo_territory_id
940 FROM JTF_TTY_GEO_TERR gt1
941 WHERE gt1.owner_resource_id = p_resource_id
942 AND gt1.owner_rsc_group_id = p_rsc_group_id
943 AND gt1.owner_rsc_role_code = p_rsc_role_code
944 AND gt1.parent_geo_terr_id = p_territory_id)
945 CONNECT BY PRIOR gt.geo_territory_id = gt.parent_geo_terr_id);
946 /* Delete geo terrs assignments created by the given resource
947 * from the given territory */
948 DELETE from JTF_TTY_GEO_TERR_RSC gtr
949 WHERE gtr.geo_territory_id IN
950 (SELECT gt1.geo_territory_id
951 FROM JTF_TTY_GEO_TERR gt1
952 WHERE gt1.owner_resource_id = p_resource_id
953 AND gt1.owner_rsc_group_id = p_rsc_group_id
954 AND gt1.owner_rsc_role_code = p_rsc_role_code
955 AND gt1.parent_geo_terr_id = p_territory_id);
956 DELETE from JTF_TTY_GEO_TERR_RSC gtr
957 WHERE gtr.geo_territory_id = p_territory_id
958 AND gtr.resource_id = p_resource_id
959 AND gtr.rsc_group_id = p_rsc_group_id
960 AND gtr.rsc_role_code = p_rsc_role_code;
961
962 /* Now delete the geo territories down */
963 /* first delete the geo territories created by the resource's
964 * directs from the given territory */
965 DELETE from jtf_tty_geo_terr t
966 WHERE t.geo_territory_id IN
967 (SELECT gt.geo_territory_id
968 FROM JTF_TTY_GEO_TERR gt
969 START WITH gt.geo_territory_id IN
970 (SELECT gt1.geo_territory_id
971 FROM JTF_TTY_GEO_TERR gt1
972 WHERE gt1.owner_resource_id = p_resource_id
973 AND gt1.owner_rsc_group_id = p_rsc_group_id
974 AND gt1.owner_rsc_role_code = p_rsc_role_code
975 AND gt1.parent_geo_terr_id = p_territory_id)
976 CONNECT BY PRIOR gt.geo_territory_id = gt.parent_geo_terr_id);
977
978 /* now delete the geo territories created by the given resource and
979 * from the given territory */
980 DELETE from jtf_tty_geo_terr t
981 WHERE t.owner_resource_id = p_resource_id
982 AND t.owner_rsc_group_id = p_rsc_group_id
983 AND t.owner_rsc_role_code = p_rsc_role_code
984 AND t.parent_geo_terr_id = p_territory_id;
985
986 commit;
987 END delete_geo_terr_rsc;
988 /*
989 * delete the geo terr assignments for removed owner/Sales Rep
990 * for the given geo territory and all the children geo territories
991 */
992 PROCEDURE assign_geo_terr(p_territory_id IN NUMBER,
993 p_resource_id IN NUMBER,
994 p_rsc_group_id IN NUMBER,
995 p_rsc_role_code IN VARCHAR2)
996 AS
997 p_user_id NUMBER;
998 BEGIN
999 p_user_id := fnd_global.user_id;
1000
1001 /* Assign the top level territory to the geo terr gp owner/sales rep */
1002 insert into jtf_tty_geo_terr_rsc
1003 (geo_terr_resource_id,
1004 object_version_number,
1005 geo_territory_id,
1006 resource_id,
1007 rsc_group_id,
1008 rsc_role_code,
1009 assigned_flag,
1010 created_by,
1011 creation_date,
1012 last_updated_by,
1013 last_update_date)
1014 VALUES(jtf_tty_geo_terr_rsc_s.nextval,
1015 1,
1016 p_territory_id,
1017 p_resource_id,
1018 p_rsc_group_id,
1019 p_rsc_role_code,
1020 'N',
1021 p_user_id,
1022 sysdate,
1023 p_user_id,
1024 sysdate);
1025
1026 COMMIT;
1027 END assign_geo_terr;
1028 /**
1029 * replace the geo terr assignments for removed owner/Sales Rep
1030 * for the given geo territory and all the children geo territories
1031 */
1032 PROCEDURE replace_geo_terr_rsc(p_territory_id IN NUMBER,
1033 p_new_owner_resource_id IN NUMBER,
1034 p_rsc_group_id IN NUMBER,
1035 p_rsc_role_code IN VARCHAR2,
1036 p_replaced_owner_resource_id IN NUMBER)
1037 AS
1038 p_user_id NUMBER;
1039 BEGIN
1040 p_user_id := fnd_global.user_id;
1041 -- change the owner of all the territories created by replaced owner
1042 -- from this territory (as a parent)
1043
1044 update jtf_tty_geo_terr
1045 set owner_resource_id = p_new_owner_resource_id,
1046 owner_rsc_group_id = p_rsc_group_id,
1047 owner_rsc_role_code = p_rsc_role_code
1048 where parent_geo_terr_id = p_territory_id
1049 and owner_resource_id = p_replaced_owner_resource_id;
1050
1051 -- delete the replaced owner from geo terr assignment
1052 -- the territory is assigned to the new owner by assign geo terr api
1053 DELETE from JTF_TTY_GEO_TERR_RSC gtr
1054 WHERE gtr.geo_territory_id = p_territory_id
1055 AND gtr.resource_id = p_replaced_owner_resource_id
1056 AND gtr.rsc_group_id = p_rsc_group_id
1057 AND gtr.rsc_role_code = p_rsc_role_code;
1058 COMMIT;
1059 END replace_geo_terr_rsc;
1060
1061 end JTF_TTY_GEO_TERRGP;