[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
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 */
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;
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,
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,
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'
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'
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'
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'
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
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'