eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JTF_TTY_GEO_TERRGP dependencies on JTF_TTY_GEO_TERR

Line 1: PACKAGE BODY JTF_TTY_GEO_TERRGP AS

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

Line 120: delete from jtf_tty_geo_terr_rsc where geo_territory_id = l_geo_terr_id;

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:

Line 122: delete from jtf_tty_geo_terr where geo_territory_id = l_geo_terr_id;

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

Line 130: select jtf_tty_geo_terr_s.nextval

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;

Line 187: insert into jtf_tty_geo_terr

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,

Line 229: insert into jtf_tty_geo_terr_rsc

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,

Line 243: SELECT jtf_tty_geo_terr_rsc_s.nextval

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

Line 574: DELETE from JTF_TTY_GEO_TERR_VALUES

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);

Line 577: FROM jtf_tty_geo_terr t

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:

Line 582: DELETE from JTF_TTY_GEO_TERR_RSC

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);

Line 585: FROM jtf_tty_geo_terr t

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;

Line 588: DELETE from JTF_TTY_GEO_TERR

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:

Line 688: SELECT jtf_tty_geo_terr_s.nextval, terr_group_name

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: /*

Line 697: insert into jtf_tty_geo_terr

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,

Line 725: insert into jtf_tty_geo_terr_rsc

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,

Line 737: SELECT jtf_tty_geo_terr_rsc_s.nextval,

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,

Line 760: DELETE from JTF_TTY_GEO_TERR_VALUES gtv

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

Line 762: (SELECT geo_territory_id FROM jtf_tty_geo_terr

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

Line 833: FROM jtf_tty_geo_terr_rsc gtr,

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

Line 834: jtf_tty_geo_terr gt

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

Line 849: FROM jtf_tty_geo_terr_rsc gtr,

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

Line 850: jtf_tty_geo_terr gt,

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

Line 865: jtf_tty_geo_terr gt

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)

Line 871: FROM jtf_tty_geo_terr_rsc gtr

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,

Line 909: DELETE from JTF_TTY_GEO_TERR_VALUES gtv

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

Line 912: FROM JTF_TTY_GEO_TERR gt

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

Line 915: FROM JTF_TTY_GEO_TERR gt1

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)

Line 923: DELETE from JTF_TTY_GEO_TERR_VALUES gtv

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

Line 926: FROM JTF_TTY_GEO_TERR gt1

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);

Line 934: DELETE from JTF_TTY_GEO_TERR_RSC gtr

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

Line 937: FROM JTF_TTY_GEO_TERR gt

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

Line 940: FROM JTF_TTY_GEO_TERR gt1

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)

Line 948: DELETE from JTF_TTY_GEO_TERR_RSC gtr

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

Line 951: FROM JTF_TTY_GEO_TERR gt1

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);

Line 956: DELETE from JTF_TTY_GEO_TERR_RSC gtr

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;

Line 965: DELETE from jtf_tty_geo_terr t

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

Line 968: FROM JTF_TTY_GEO_TERR gt

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

Line 971: FROM JTF_TTY_GEO_TERR gt1

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)

Line 980: DELETE from jtf_tty_geo_terr t

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;

Line 1002: insert into jtf_tty_geo_terr_rsc

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,

Line 1014: VALUES(jtf_tty_geo_terr_rsc_s.nextval,

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,

Line 1044: update jtf_tty_geo_terr

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

Line 1053: DELETE from JTF_TTY_GEO_TERR_RSC gtr

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;

Line 1061: end JTF_TTY_GEO_TERRGP;

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;