DBA Data[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;