[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_GEO_TERRGP
Source
1 PACKAGE BODY JTF_TTY_GEO_TERRGP AS
2 /* $Header: jtftggpb.pls 120.6.12010000.2 2010/02/02 11:55:52 rajukum 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
450 WHEN NO_DATA_FOUND THEN
447 ,get_terr_grp_roles.LAST_UPDATE_DATE);
448
449 EXCEPTION
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 ,TRANS_ACCESS_CODE)
471 select
472 jtf_tty_role_access_s.nextval
473 ,lp_terr_grp_role_id
474 ,ACCESS_TYPE
475 , 1
476 ,CREATED_BY
477 ,CREATION_DATE
478 ,LAST_UPDATED_BY
479 ,LAST_UPDATE_DATE
480 ,trans_access_code
481 from jtf_terr_rsc_access_all
482 where terr_rsc_id = get_terr_grp_roles.terr_rsc_id;
483
484 EXCEPTION
485 WHEN NO_DATA_FOUND THEN
486 NULL;
487 WHEN OTHERS THEN
488 x_return_status := 'E';
489 x_msg_data := substr(sqlerrm, 1, 200) ;
490 return;
491
492 END;
493
494 --
495 end loop;
496 --
497 commit;
498
499 BEGIN
500 --
501 update jtf_terr_all
502 set terr_group_id = l_terr_grp_id
503 , terr_group_flag = 'Y'
504 , catch_all_flag = 'N'
505 , geo_territory_id = l_geo_terr_id
506 where terr_id = p_terr_id;
507
508 EXCEPTION
509 WHEN NO_DATA_FOUND THEN
510 NULL;
511 WHEN OTHERS THEN
512 x_return_status := 'E';
513 x_msg_data := substr(sqlerrm, 1, 200) ;
514 return;
515
516 END;
517
518 commit;
519 x_return_status := 'S';
520
521 EXCEPTION
522 when FND_API.G_EXC_ERROR then
523
524 x_return_status := 'E';
525 x_msg_data := substr(sqlerrm, 1, 200) ;
526 return;
527
528 when others then
529 x_return_status := 'E';
530 x_msg_data := substr(sqlerrm, 1, 200) ;
531 return;
532
533 END POPULATE_SELF_SRV_SCHEMA;
534
535
536 PROCEDURE log_event(p_object_id IN NUMBER,
537 p_action_type IN VARCHAR2,
538 p_from_where IN VARCHAR2,
539 p_object_type IN VARCHAR2,
540 p_user_id in NUMBER)
541 IS
542 BEGIN
543 INSERT INTO JTF_TTY_NAMED_ACCT_CHANGES(
544 NAMED_ACCT_CHANGE_ID,
545 OBJECT_VERSION_NUMBER,
546 OBJECT_TYPE,
547 OBJECT_ID,
548 CHANGE_TYPE,
549 FROM_WHERE,
550 CREATED_BY,
551 CREATION_DATE,
552 LAST_UPDATED_BY,
553 LAST_UPDATE_DATE
554 )
555 VALUES(JTF_TTY_NAMED_ACCT_CHANGES_S.nextval,
556 1,
557 p_object_type,
558 p_object_id,
559 p_action_type,
560 p_from_where,
561 p_user_id,
562 sysdate,
563 p_user_id,
564 sysdate);
565
566 END log_event;
567
568 PROCEDURE delete_terrgp(p_terr_gp_id IN NUMBER)
569 AS
570 p_user_id NUMBER;
571 BEGIN
572
573 /* delete the geos or postal code belonging to the geo territores of this
574 /* geo territory group */
575
576 DELETE from JTF_TTY_GEO_TERR_VALUES
577 WHERE geo_territory_id IN
578 (SELECT t.geo_territory_id
579 FROM jtf_tty_geo_terr t
580 WHERE t.terr_group_id = p_terr_gp_id);
581
582 /* delete all the geo territories assignments for the geo terr group */
583
584 DELETE from JTF_TTY_GEO_TERR_RSC
585 WHERE geo_territory_id IN
586 (SELECT t.geo_territory_id
587 FROM jtf_tty_geo_terr t
588 WHERE t.terr_group_id = p_terr_gp_id);
589
590 DELETE from JTF_TTY_GEO_TERR
591 WHERE terr_group_id = p_terr_gp_id;
592
593 /* delete all the geographies for the geo terr group */
594
595 DELETE from JTF_TTY_GEO_GRP_VALUES
596 WHERE terr_group_id = p_terr_gp_id;
597
598
599 /* delete all the terr gp owners, access and product */
600 delete from jtf_tty_terr_grp_owners
601 where terr_group_id = p_terr_gp_id;
602
603 delete from jtf_tty_role_prod_int
604 where terr_group_role_id in
605 (select terr_group_role_id from jtf_tty_terr_grp_roles
606 where terr_group_id = p_terr_gp_id);
607
608
609 delete from jtf_tty_role_access
610 where terr_group_role_id in
611 (select terr_group_role_id from jtf_tty_terr_grp_roles
612 where terr_group_id = p_terr_gp_id);
613
614 delete from jtf_tty_terr_grp_roles
615 where terr_group_id = p_terr_gp_id;
616
617 /* finally delete the terr gp itself */
618
619 delete from jtf_tty_terr_groups
620 where terr_group_id = p_terr_gp_id;
621
622 /* ACHANDA : added to log the event of territory group delete for GTP to do incremental process */
623 log_event(p_terr_gp_id, 'DELETE', 'Delete Territory Group', 'TG', fnd_global.user_id);
624 commit;
625 END delete_terrgp;
629 */
626 /*
627 * Adds the geography to the geo terr group
628 * Invoked during create or update of geo terr group
630 PROCEDURE delete_geo_from_grp(p_terr_gp_id IN NUMBER)
631 AS
632 BEGIN
633 DELETE from jtf_tty_geo_grp_values
634 where TERR_GROUP_ID = p_terr_gp_id;
635
636 COMMIT;
637 END delete_geo_from_grp;
638 /*
639 * Adds the geography to the geo terr group
640 * Invoked during create or update of geo terr group
641 */
642 PROCEDURE add_geo_to_grp(p_terr_gp_id IN NUMBER,
643 p_geo_id_from IN NUMBER,
644 p_geo_id_to IN NUMBER,
645 p_operator IN VARCHAR2,
646 p_geo_type IN VARCHAR2,
647 p_user_id IN NUMBER)
648 AS
649 BEGIN
650
651 INSERT into jtf_tty_geo_grp_values(
652 GEO_GRP_VALUES_ID,
653 OBJECT_VERSION_NUMBER,
654 TERR_GROUP_ID,
655 COMPARISON_OPERATOR,
656 GEO_TYPE,
657 GEO_ID_FROM,
658 GEO_ID_TO,
659 CREATED_BY,
660 CREATION_DATE,
661 LAST_UPDATED_BY,
662 last_update_date)
663 VALUES(
664 jtf_tty_geo_grp_values_s.nextval,
665 1,
666 p_terr_gp_id,
667 p_operator,
668 p_geo_type,
669 p_geo_id_from,
670 p_geo_id_to,
671 p_user_id,
672 sysdate,
673 p_user_id,
674 sysdate);
675
676 COMMIT;
677 END add_geo_to_grp;
678 /*
679 * create a top level geo territory for the geo terr group
680 * and assigns it to the owners of the geo terr group
681 * Invoked during create geo terr group
682 */
683 PROCEDURE create_grp_geo_terr(p_terr_gp_id IN NUMBER,
684 p_user_id IN NUMBER)
685 AS
686 p_geo_territory_id NUMBER;
687 p_geo_territory_name VARCHAR2(80);
688 p_territory_label VARCHAR2(80);
689 BEGIN
690 SELECT jtf_tty_geo_terr_s.nextval, terr_group_name
691 INTO p_geo_territory_id, p_geo_territory_name
692 FROM jtf_tty_terr_groups
693 WHERE terr_group_id = p_terr_gp_id;
694 /*
695 fnd_message.set_name('JTF', 'JTF_TTY_TERR_LABEL');
696 p_territory_label := fnd_message.Get();
697 */
698 /* create a top-level geo territory */
699 insert into jtf_tty_geo_terr
700 (geo_territory_id,
701 parent_geo_terr_id,
702 object_version_number,
703 child_node_flag,
704 geo_terr_name,
705 terr_group_id,
706 owner_resource_id ,
707 owner_rsc_group_id,
708 owner_rsc_role_code,
709 created_by,
710 creation_date,
711 last_updated_by,
712 last_update_date)
713 values( p_geo_territory_id,
714 - p_geo_territory_id,
715 1,
716 'N',
717 p_geo_territory_name,
718 p_terr_gp_id,
719 -999,
720 -999,
721 -999,
722 p_user_id,
723 sysdate,
724 p_user_id,
725 sysdate);
726 /* Assign the top level territory to all the geo terr gp owners */
727 insert into jtf_tty_geo_terr_rsc
728 (geo_terr_resource_id,
729 object_version_number,
730 geo_territory_id,
731 resource_id,
732 rsc_group_id,
733 rsc_role_code,
734 assigned_flag,
735 created_by,
736 creation_date,
737 last_updated_by,
738 last_update_date)
739 SELECT jtf_tty_geo_terr_rsc_s.nextval,
740 1,
741 p_geo_territory_id,
742 tgo.resource_id,
743 tgo.rsc_group_id,
744 tgo.rsc_role_code,
745 'N',
746 p_user_id,
747 sysdate,
748 p_user_id,
749 sysdate
750 FROM jtf_tty_terr_grp_owners tgo
751 WHERE tgo.terr_group_id = p_terr_gp_id;
752
753 COMMIT;
754 END create_grp_geo_terr;
755 /*
756 * Deletes the removed geographies from all the geo territories
757 * belong to this geo terr group
758 */
759 PROCEDURE delete_geos_from_terrs(p_terr_gp_id IN NUMBER)
760 AS
761 BEGIN
762 DELETE from JTF_TTY_GEO_TERR_VALUES gtv
763 WHERE gtv.geo_territory_id IN
764 (SELECT geo_territory_id FROM jtf_tty_geo_terr
765 where terr_group_id = p_terr_gp_id)
766 AND gtv.geo_id NOT IN
767 (SELECT g.geo_id FROM jtf_tty_geographies g, jtf_tty_geo_grp_values ggv, jtf_tty_geographies g1
768 WHERE ggv.terr_group_id = p_terr_gp_id
769 AND ggv.geo_type = 'COUNTRY'
770 AND ggv.geo_id_from = g1.geo_id
771 AND g.geo_type = 'POSTAL_CODE'
772 AND g.country_code = g1.country_code
773 UNION
774 SELECT g.geo_id FROM jtf_tty_geographies g, jtf_tty_geo_grp_values ggv, jtf_tty_geographies g1
775 WHERE ggv.terr_group_id = p_terr_gp_id
776 AND ggv.geo_type = 'STATE'
777 AND ggv.geo_id_from = g1.geo_id
778 AND g.geo_type = 'POSTAL_CODE'
779 AND g.country_code = g1.country_code
780 AND g.state_code = g1.state_code
781 UNION
782 SELECT g.geo_id FROM jtf_tty_geographies g, jtf_tty_geo_grp_values ggv, jtf_tty_geographies g1
783 WHERE ggv.terr_group_id = p_terr_gp_id
784 AND ggv.geo_type = 'PROVINCE'
788 AND g.province_code = g1.province_code
785 AND ggv.geo_id_from = g1.geo_id
786 AND g.geo_type = 'POSTAL_CODE'
787 AND g.country_code = g1.country_code
789 UNION
790 SELECT g.geo_id FROM jtf_tty_geographies g, jtf_tty_geo_grp_values ggv, jtf_tty_geographies g1
791 WHERE ggv.terr_group_id = p_terr_gp_id
792 AND ggv.geo_type = 'CITY'
793 AND ggv.geo_id_from = g1.geo_id
794 AND g.geo_type = 'POSTAL_CODE'
795 AND g.country_code = g1.country_code
796 AND ((g.state_code = g1.state_code AND g1.province_code is null)
797 or
798 (g1.province_code = g.province_code AND g1.state_code is null))
799 AND (g1.county_code is null or g.county_code = g1.county_code)
800 AND g.city_code = g1.city_code
801 UNION
802 SELECT ggv.geo_id_from FROM jtf_tty_geo_grp_values ggv
803 WHERE ggv.terr_group_id = p_terr_gp_id
804 AND ggv.geo_type = 'POSTAL_CODE'
805 AND ggv.comparison_operator = '='
806 UNION
807 SELECT g.geo_id
808 FROM jtf_tty_geographies g,
809 jtf_tty_geo_grp_values ggv,
810 jtf_tty_geographies g1,
811 jtf_tty_geographies g2
812 WHERE ggv.terr_group_id = p_terr_gp_id
813 AND ggv.geo_type = 'POSTAL_CODE'
814 AND ggv.comparison_operator = 'BETWEEN'
815 AND g1.geo_id = ggv.geo_id_from
816 AND g2.geo_id = ggv.geo_id_to
817 AND g.geo_name BETWEEN g1.geo_name and g2.geo_name);
818
819 commit;
820
821
822 END delete_geos_from_terrs;
823 /*
824 * Updates the geo terr assinments for removed and added owners
825 * of a geo territory group, invoked only for update geo terr group
826 * and if owners are updated
827 */
828 PROCEDURE update_geo_grp_assignments (p_terr_gp_id IN NUMBER)
829 AS
830 CURSOR removed_owners_c IS
831 SELECT gtr.resource_id,
832 gtr.rsc_group_id,
833 gtr.rsc_role_code,
834 gtr.geo_territory_id
835 FROM jtf_tty_geo_terr_rsc gtr,
836 jtf_tty_geo_terr gt
837 WHERE gt.terr_group_id = p_terr_gp_id
838 AND gt.geo_territory_id = gtr.geo_territory_id
839 AND gt.owner_resource_id = -999
840 AND gtr.rsc_group_id
841 NOT IN (SELECT tgo.rsc_group_id
842 FROM jtf_tty_terr_grp_owners tgo
843 WHERE tgo.terr_group_id = p_terr_gp_id);
844
845 CURSOR replaced_owners_c IS
846 SELECT tgo1.resource_id new_owner_resource_id,
847 gtr.rsc_group_id,
848 gtr.rsc_role_code,
849 gtr.geo_territory_id,
850 gtr.resource_id replaced_owner_resource_id
851 FROM jtf_tty_geo_terr_rsc gtr,
852 jtf_tty_geo_terr gt,
853 jtf_tty_terr_grp_owners tgo1
854 WHERE gt.terr_group_id = p_terr_gp_id
855 AND gt.geo_territory_id = gtr.geo_territory_id
856 AND gt.owner_resource_id = -999
857 and tgo1.terr_group_id = p_terr_gp_id
858 and tgo1.rsc_group_id = gtr.rsc_group_id
859 and gtr.resource_id <> tgo1.resource_id;
860
861 CURSOR added_owners_c IS
862 SELECT tgo.resource_id,
863 tgo.rsc_group_id,
864 tgo.rsc_role_code,
865 gt.geo_territory_id
866 FROM JTF_TTY_TERR_GRP_OWNERS tgo,
867 jtf_tty_geo_terr gt
868 WHERE gt.terr_group_id = p_terr_gp_id
869 AND tgo.terr_group_id = p_terr_gp_id
870 AND gt.owner_resource_id = -999
871 AND (tgo.resource_id, tgo.rsc_group_id, tgo.rsc_role_code)
872 NOT IN (SELECT gtr.resource_id, gtr.rsc_group_id, gtr.rsc_role_code
873 FROM jtf_tty_geo_terr_rsc gtr
874 WHERE gt.geo_territory_id = gtr.geo_territory_id);
875 BEGIN
876 for removed_owners IN removed_owners_c LOOP
877 delete_geo_terr_rsc(removed_owners.geo_territory_id,
878 removed_owners.resource_id,
879 removed_owners.rsc_group_id,
880 removed_owners.rsc_role_code);
881 END LOOP;
882 for added_owners IN added_owners_c LOOP
883 assign_geo_terr(added_owners.geo_territory_id,
884 added_owners.resource_id,
885 added_owners.rsc_group_id,
886 added_owners.rsc_role_code);
887 END LOOP;
888 for replaced_owners IN replaced_owners_c LOOP
889 replace_geo_terr_rsc(replaced_owners.geo_territory_id,
890 replaced_owners.new_owner_resource_id,
891 replaced_owners.rsc_group_id,
892 replaced_owners.rsc_role_code,
893 replaced_owners.replaced_owner_resource_id);
894 END LOOP;
895
896
897
898 END update_geo_grp_assignments;
899 /*
900 * delete the geo terr assignments for removed owner/Sales Rep
901 * for the given geo territory and all the children geo territories
902 */
903 PROCEDURE delete_geo_terr_rsc (p_territory_id IN NUMBER,
904 p_resource_id IN NUMBER,
905 p_rsc_group_id IN NUMBER,
906 p_rsc_role_code IN VARCHAR2)
907 AS
908 BEGIN
909 /* Delete goes for the geo terrs assigned by the given resource and down
910 * from the given territory */
911 DELETE from JTF_TTY_GEO_TERR_VALUES gtv
912 WHERE gtv.geo_territory_id IN
913 (SELECT gt.geo_territory_id
914 FROM JTF_TTY_GEO_TERR gt
915 START WITH gt.geo_territory_id IN
916 (SELECT gt1.geo_territory_id
917 FROM JTF_TTY_GEO_TERR gt1
918 WHERE gt1.owner_resource_id = p_resource_id
919 AND gt1.owner_rsc_group_id = p_rsc_group_id
923 /* Delete goes for the geo terrs created by the given resource
920 AND gt1.owner_rsc_role_code = p_rsc_role_code
921 AND gt1.parent_geo_terr_id = p_territory_id)
922 CONNECT BY PRIOR gt.geo_territory_id = gt.parent_geo_terr_id);
924 * from the given territory */
925 DELETE from JTF_TTY_GEO_TERR_VALUES gtv
926 WHERE gtv.geo_territory_id IN
927 (SELECT gt1.geo_territory_id
928 FROM JTF_TTY_GEO_TERR gt1
929 WHERE gt1.owner_resource_id = p_resource_id
930 AND gt1.owner_rsc_group_id = p_rsc_group_id
931 AND gt1.owner_rsc_role_code = p_rsc_role_code
932 AND gt1.parent_geo_terr_id = p_territory_id);
933
934 /* Delete for the geo terrs assignments by the given resource and down
935 * from the given territory */
936 DELETE from JTF_TTY_GEO_TERR_RSC gtr
937 WHERE gtr.geo_territory_id IN
938 (SELECT gt.geo_territory_id
939 FROM JTF_TTY_GEO_TERR gt
940 START WITH gt.geo_territory_id IN
941 (SELECT gt1.geo_territory_id
942 FROM JTF_TTY_GEO_TERR gt1
943 WHERE gt1.owner_resource_id = p_resource_id
944 AND gt1.owner_rsc_group_id = p_rsc_group_id
945 AND gt1.owner_rsc_role_code = p_rsc_role_code
946 AND gt1.parent_geo_terr_id = p_territory_id)
947 CONNECT BY PRIOR gt.geo_territory_id = gt.parent_geo_terr_id);
948 /* Delete geo terrs assignments created by the given resource
949 * from the given territory */
950 DELETE from JTF_TTY_GEO_TERR_RSC gtr
951 WHERE gtr.geo_territory_id IN
952 (SELECT gt1.geo_territory_id
953 FROM JTF_TTY_GEO_TERR gt1
954 WHERE gt1.owner_resource_id = p_resource_id
955 AND gt1.owner_rsc_group_id = p_rsc_group_id
956 AND gt1.owner_rsc_role_code = p_rsc_role_code
957 AND gt1.parent_geo_terr_id = p_territory_id);
958 DELETE from JTF_TTY_GEO_TERR_RSC gtr
959 WHERE gtr.geo_territory_id = p_territory_id
960 AND gtr.resource_id = p_resource_id
961 AND gtr.rsc_group_id = p_rsc_group_id
962 AND gtr.rsc_role_code = p_rsc_role_code;
963
964 /* Now delete the geo territories down */
965 /* first delete the geo territories created by the resource's
966 * directs from the given territory */
967 DELETE from jtf_tty_geo_terr t
968 WHERE t.geo_territory_id IN
969 (SELECT gt.geo_territory_id
970 FROM JTF_TTY_GEO_TERR gt
971 START WITH gt.geo_territory_id IN
972 (SELECT gt1.geo_territory_id
973 FROM JTF_TTY_GEO_TERR gt1
974 WHERE gt1.owner_resource_id = p_resource_id
975 AND gt1.owner_rsc_group_id = p_rsc_group_id
976 AND gt1.owner_rsc_role_code = p_rsc_role_code
977 AND gt1.parent_geo_terr_id = p_territory_id)
978 CONNECT BY PRIOR gt.geo_territory_id = gt.parent_geo_terr_id);
979
980 /* now delete the geo territories created by the given resource and
981 * from the given territory */
982 DELETE from jtf_tty_geo_terr t
983 WHERE t.owner_resource_id = p_resource_id
984 AND t.owner_rsc_group_id = p_rsc_group_id
985 AND t.owner_rsc_role_code = p_rsc_role_code
986 AND t.parent_geo_terr_id = p_territory_id;
987
988 commit;
989 END delete_geo_terr_rsc;
990 /*
991 * delete the geo terr assignments for removed owner/Sales Rep
992 * for the given geo territory and all the children geo territories
993 */
994 PROCEDURE assign_geo_terr(p_territory_id IN NUMBER,
995 p_resource_id IN NUMBER,
996 p_rsc_group_id IN NUMBER,
997 p_rsc_role_code IN VARCHAR2)
998 AS
999 p_user_id NUMBER;
1000 BEGIN
1001 p_user_id := fnd_global.user_id;
1002
1003 /* Assign the top level territory to the geo terr gp owner/sales rep */
1004 insert into jtf_tty_geo_terr_rsc
1005 (geo_terr_resource_id,
1006 object_version_number,
1007 geo_territory_id,
1008 resource_id,
1009 rsc_group_id,
1010 rsc_role_code,
1011 assigned_flag,
1012 created_by,
1013 creation_date,
1014 last_updated_by,
1015 last_update_date)
1016 VALUES(jtf_tty_geo_terr_rsc_s.nextval,
1017 1,
1018 p_territory_id,
1019 p_resource_id,
1020 p_rsc_group_id,
1021 p_rsc_role_code,
1022 'N',
1023 p_user_id,
1024 sysdate,
1025 p_user_id,
1026 sysdate);
1027
1028 COMMIT;
1029 END assign_geo_terr;
1030 /**
1031 * replace the geo terr assignments for removed owner/Sales Rep
1032 * for the given geo territory and all the children geo territories
1033 */
1034 PROCEDURE replace_geo_terr_rsc(p_territory_id IN NUMBER,
1035 p_new_owner_resource_id IN NUMBER,
1036 p_rsc_group_id IN NUMBER,
1037 p_rsc_role_code IN VARCHAR2,
1038 p_replaced_owner_resource_id IN NUMBER)
1039 AS
1040 p_user_id NUMBER;
1041 BEGIN
1042 p_user_id := fnd_global.user_id;
1043 -- change the owner of all the territories created by replaced owner
1044 -- from this territory (as a parent)
1045
1046 update jtf_tty_geo_terr
1047 set owner_resource_id = p_new_owner_resource_id,
1048 owner_rsc_group_id = p_rsc_group_id,
1049 owner_rsc_role_code = p_rsc_role_code
1050 where parent_geo_terr_id = p_territory_id
1051 and owner_resource_id = p_replaced_owner_resource_id;
1052
1053 -- delete the replaced owner from geo terr assignment
1057 AND gtr.resource_id = p_replaced_owner_resource_id
1054 -- the territory is assigned to the new owner by assign geo terr api
1055 DELETE from JTF_TTY_GEO_TERR_RSC gtr
1056 WHERE gtr.geo_territory_id = p_territory_id
1058 AND gtr.rsc_group_id = p_rsc_group_id
1059 AND gtr.rsc_role_code = p_rsc_role_code;
1060 COMMIT;
1061 END replace_geo_terr_rsc;
1062
1063 end JTF_TTY_GEO_TERRGP;