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.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

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 576: DELETE from JTF_TTY_GEO_TERR_VALUES

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

Line 579: FROM jtf_tty_geo_terr t

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:

Line 584: DELETE from JTF_TTY_GEO_TERR_RSC

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

Line 587: FROM jtf_tty_geo_terr t

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;

Line 590: DELETE from JTF_TTY_GEO_TERR

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:

Line 690: SELECT jtf_tty_geo_terr_s.nextval, terr_group_name

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

Line 699: insert into jtf_tty_geo_terr

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,

Line 727: insert into jtf_tty_geo_terr_rsc

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,

Line 739: SELECT jtf_tty_geo_terr_rsc_s.nextval,

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,

Line 762: DELETE from JTF_TTY_GEO_TERR_VALUES gtv

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

Line 764: (SELECT geo_territory_id FROM jtf_tty_geo_terr

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

Line 835: FROM jtf_tty_geo_terr_rsc gtr,

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

Line 836: jtf_tty_geo_terr gt

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

Line 851: FROM jtf_tty_geo_terr_rsc gtr,

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

Line 852: jtf_tty_geo_terr gt,

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

Line 867: jtf_tty_geo_terr gt

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)

Line 873: FROM jtf_tty_geo_terr_rsc gtr

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,

Line 911: DELETE from JTF_TTY_GEO_TERR_VALUES gtv

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

Line 914: FROM JTF_TTY_GEO_TERR gt

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

Line 917: FROM JTF_TTY_GEO_TERR gt1

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
920: AND gt1.owner_rsc_role_code = p_rsc_role_code
921: AND gt1.parent_geo_terr_id = p_territory_id)

Line 925: DELETE from JTF_TTY_GEO_TERR_VALUES gtv

921: AND gt1.parent_geo_terr_id = p_territory_id)
922: CONNECT BY PRIOR gt.geo_territory_id = gt.parent_geo_terr_id);
923: /* Delete goes for the geo terrs created by the given resource
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

Line 928: FROM JTF_TTY_GEO_TERR gt1

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

Line 936: DELETE from JTF_TTY_GEO_TERR_RSC gtr

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

Line 939: FROM JTF_TTY_GEO_TERR gt

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

Line 942: FROM JTF_TTY_GEO_TERR gt1

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)

Line 950: DELETE from JTF_TTY_GEO_TERR_RSC gtr

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

Line 953: FROM JTF_TTY_GEO_TERR gt1

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

Line 958: DELETE from JTF_TTY_GEO_TERR_RSC gtr

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;

Line 967: DELETE from jtf_tty_geo_terr t

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

Line 970: FROM JTF_TTY_GEO_TERR gt

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

Line 973: FROM JTF_TTY_GEO_TERR gt1

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)

Line 982: DELETE from jtf_tty_geo_terr t

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;

Line 1004: insert into jtf_tty_geo_terr_rsc

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,

Line 1016: VALUES(jtf_tty_geo_terr_rsc_s.nextval,

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,

Line 1046: update jtf_tty_geo_terr

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

Line 1055: DELETE from JTF_TTY_GEO_TERR_RSC gtr

1051: and owner_resource_id = p_replaced_owner_resource_id;
1052:
1053: -- delete the replaced owner from geo terr assignment
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
1057: AND gtr.resource_id = p_replaced_owner_resource_id
1058: AND gtr.rsc_group_id = p_rsc_group_id
1059: AND gtr.rsc_role_code = p_rsc_role_code;

Line 1063: end JTF_TTY_GEO_TERRGP;

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;