[Home] [Help]
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;
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 (
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
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 */
629: */
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;
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,
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,
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'
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'
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'
785: AND ggv.geo_id_from = g1.geo_id
786: AND g.geo_type = 'POSTAL_CODE'
786: AND g.geo_type = 'POSTAL_CODE'
787: AND g.country_code = g1.country_code
788: AND g.province_code = g1.province_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'
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
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'