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.HZ_GEOGRAPHY_STRUCTURE_PUB dependencies on HZ_GEOGRAPHIES

Line 161: WHERE obj_name='HZ_GEOGRAPHIES';

157:
158:
159: SELECT object_id into l_object_id
160: FROM FND_OBJECTS
161: WHERE obj_name='HZ_GEOGRAPHIES';
162:
163: -- Replace the ' in predicate with ''
164: l_predicate := 'GEOGRAPHY_TYPE='||''''||replace(l_geography_type,'''','''''')||'''';
165:

Line 412: FROM HZ_GEOGRAPHIES

408: -- Bug 4591502 : ISSUE # 15
409: -- Use country_code and not geography_code
410: SELECT country_code
411: INTO l_country_code
412: FROM HZ_GEOGRAPHIES
413: WHERE geography_id = p_geo_structure_rec.geography_id;
414:
415: ----dbms_output.put_line('Country_code is '||l_country_code);
416:

Line 690: --see if there exists any geography in hz_geographies where this geography_element_column being update

686: BEGIN
687: --dbms_output.put_line('l_geography_element '||l_geo_element_col);
688: --dbms_output.put_line('l_country_code '||''''||l_country_code||'''');
689: --dbms_output.put_line('before execute');
690: --see if there exists any geography in hz_geographies where this geography_element_column being update
691: -- is denormalized. If yes, do not update.
692:
693: BEGIN
694:

Line 695: /* EXECUTE IMMEDIATE 'SELECT 1 FROM hz_geographies WHERE '||l_geo_element_col||' IS NOT NULL AND country_code='||''''||l_country_code||''''||

691: -- is denormalized. If yes, do not update.
692:
693: BEGIN
694:
695: /* EXECUTE IMMEDIATE 'SELECT 1 FROM hz_geographies WHERE '||l_geo_element_col||' IS NOT NULL AND country_code='||''''||l_country_code||''''||
696: ' AND rownum <2'; */
697:
698: /* SELECT 1 into l_count from
699: hz_relationships hrl

Line 706: AND hrl.subject_id in ( SELECT geography_id from hz_geographies where

702: AND hrl.subject_type= p_parent_geography_type
703: AND hrl.object_type=p_geography_type
704: AND hrl.relationship_code='PARENT_OF'
705: AND hrl.status = 'A'
706: AND hrl.subject_id in ( SELECT geography_id from hz_geographies where
707: country_code = l_country_code )
708: AND rownum <2;
709: --dbms_output.put_line('after execute');
710: FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_ELEMENT_NONUPDATEABLE' );

Line 800: FROM hz_geographies

796: WHERE map_id = l_map_id;
797:
798: CURSOR c_get_geographies is
799: SELECT geography_id
800: FROM hz_geographies
801: WHERE geography_type = p_geography_type
802: AND country_code = l_country_code;
803:
804: CURSOR c_get_geo_name_ref is

Line 814: FROM hz_geographies hg

810: SELECT geography_id,master_ref_geography_id,geography_from,
811: start_date
812: FROM hz_geography_ranges hgr
813: WHERE (SELECT country_code
814: FROM hz_geographies hg
815: WHERE hg.geography_id = hgr.master_ref_geography_id ) = l_country_code;
816:
817: CURSOR c_get_zone_types IS
818: SELECT geography_type

Line 1026: hz_geography_types_b hgt,hz_geographies hg

1022: delete hz_relationship_types
1023: where relationship_type = 'TAX'
1024: and ((object_type = p_geography_type
1025: and subject_type in (select hgt.geography_type from
1026: hz_geography_types_b hgt,hz_geographies hg
1027: where hgt.limited_by_geography_id = hg.geography_id
1028: and hgt.geography_use = 'TAX'
1029: and hg.country_code = l_country_code ))
1030: or (subject_type = p_geography_type

Line 1032: hz_geography_types_b hgt,hz_geographies hg

1028: and hgt.geography_use = 'TAX'
1029: and hg.country_code = l_country_code ))
1030: or (subject_type = p_geography_type
1031: and object_type in (select hgt.geography_type from
1032: hz_geography_types_b hgt,hz_geographies hg
1033: where hgt.limited_by_geography_id = hg.geography_id
1034: and hgt.geography_use = 'TAX'
1035: and hg.country_code = l_country_code )));
1036:

Line 1043: delete hz_geographies

1039: loop
1040: fetch c_get_geographies into l_geography_id;
1041: exit when c_get_geographies%NOTFOUND;
1042:
1043: delete hz_geographies
1044: where geography_id = l_geography_id;
1045:
1046: -- Delete geography identifiers
1047: delete hz_geography_identifiers

Line 1082: and subject_table_name = 'HZ_GEOGRAPHIES'

1078: -- delete master_ref_geogrpahy for postal code range
1079: delete hz_relationships
1080: where subject_id = l_zone_id
1081: and object_id = l_master_ref_geography_id
1082: and subject_table_name = 'HZ_GEOGRAPHIES'
1083: and object_table_name = 'HZ_GEOGRAPHIES'
1084: and directional_flag = 'F'
1085: and relationship_type = 'TAX' ;
1086:

Line 1083: and object_table_name = 'HZ_GEOGRAPHIES'

1079: delete hz_relationships
1080: where subject_id = l_zone_id
1081: and object_id = l_master_ref_geography_id
1082: and subject_table_name = 'HZ_GEOGRAPHIES'
1083: and object_table_name = 'HZ_GEOGRAPHIES'
1084: and directional_flag = 'F'
1085: and relationship_type = 'TAX' ;
1086:
1087: delete hz_relationships

Line 1090: and subject_table_name = 'HZ_GEOGRAPHIES'

1086:
1087: delete hz_relationships
1088: where subject_id = l_master_ref_geography_id
1089: and object_id = l_zone_id
1090: and subject_table_name = 'HZ_GEOGRAPHIES'
1091: and object_table_name = 'HZ_GEOGRAPHIES'
1092: and directional_flag = 'B'
1093: and relationship_type = 'TAX';
1094:

Line 1091: and object_table_name = 'HZ_GEOGRAPHIES'

1087: delete hz_relationships
1088: where subject_id = l_master_ref_geography_id
1089: and object_id = l_zone_id
1090: and subject_table_name = 'HZ_GEOGRAPHIES'
1091: and object_table_name = 'HZ_GEOGRAPHIES'
1092: and directional_flag = 'B'
1093: and relationship_type = 'TAX';
1094:
1095: end loop;

Line 1103: from hz_geographies

1099: set postal_code_range_flag = 'N'
1100: where geography_use = 'TAX'
1101: and limited_by_geography_id is not null
1102: and (select country_code
1103: from hz_geographies
1104: where geography_id = hgt.limited_by_geography_id)
1105: = l_country_code;
1106: end if;
1107: -- delete geogrpahy ranges if any master_ref_geo is deleted

Line 1115: and subject_table_name = 'HZ_GEOGRAPHIES'

1111: -- delete relationships record both for tax and master_ref relationship_type
1112: delete hz_relationships
1113: where (object_id = l_geography_id
1114: or subject_id = l_geography_id )
1115: and subject_table_name = 'HZ_GEOGRAPHIES'
1116: and object_table_name = 'HZ_GEOGRAPHIES'
1117: and (relationship_type = 'TAX'
1118: or relationship_type = 'MASTER_REF');
1119:

Line 1116: and object_table_name = 'HZ_GEOGRAPHIES'

1112: delete hz_relationships
1113: where (object_id = l_geography_id
1114: or subject_id = l_geography_id )
1115: and subject_table_name = 'HZ_GEOGRAPHIES'
1116: and object_table_name = 'HZ_GEOGRAPHIES'
1117: and (relationship_type = 'TAX'
1118: or relationship_type = 'MASTER_REF');
1119:
1120: -- delete hierarchy nodes record for master_ref geos

Line 1124: and parent_table_name = 'HZ_GEOGRAPHIES'

1120: -- delete hierarchy nodes record for master_ref geos
1121: delete hz_hierarchy_nodes
1122: where (parent_id = l_geography_id
1123: or child_id = l_geography_id)
1124: and parent_table_name = 'HZ_GEOGRAPHIES'
1125: and child_table_name = 'HZ_GEOGRAPHIES'
1126: and hierarchy_type = 'MASTER_REF';
1127:
1128: -- delete tax zone type whose limited by geo id is deleted

Line 1125: and child_table_name = 'HZ_GEOGRAPHIES'

1121: delete hz_hierarchy_nodes
1122: where (parent_id = l_geography_id
1123: or child_id = l_geography_id)
1124: and parent_table_name = 'HZ_GEOGRAPHIES'
1125: and child_table_name = 'HZ_GEOGRAPHIES'
1126: and hierarchy_type = 'MASTER_REF';
1127:
1128: -- delete tax zone type whose limited by geo id is deleted
1129: OPEN c_get_zone_types;

Line 1142: FROM hz_geographies

1138: -- delete identifiers and relationships for tax zones to be deleted
1139:
1140: DELETE hz_geography_identifiers
1141: WHERE geography_id IN (SELECT geography_id
1142: FROM hz_geographies
1143: WHERE geography_type = l_zone_type
1144: AND geography_use = 'TAX');
1145:
1146:

Line 1149: FROM hz_geographies

1145:
1146:
1147: DELETE hz_relationships
1148: WHERE subject_id in (SELECT geography_id
1149: FROM hz_geographies
1150: WHERE geography_type = l_zone_type
1151: AND geography_use = 'TAX')
1152: AND subject_type = l_zone_type
1153: AND subject_table_name = 'HZ_GEOGRAPHIES'

Line 1153: AND subject_table_name = 'HZ_GEOGRAPHIES'

1149: FROM hz_geographies
1150: WHERE geography_type = l_zone_type
1151: AND geography_use = 'TAX')
1152: AND subject_type = l_zone_type
1153: AND subject_table_name = 'HZ_GEOGRAPHIES'
1154: AND relationship_type = 'TAX';
1155:
1156:
1157: DELETE hz_relationships

Line 1159: FROM hz_geographies

1155:
1156:
1157: DELETE hz_relationships
1158: WHERE object_id in (SELECT geography_id
1159: FROM hz_geographies
1160: WHERE geography_type = l_zone_type
1161: AND geography_use = 'TAX')
1162: AND object_type = l_zone_type
1163: AND object_table_name = 'HZ_GEOGRAPHIES'

Line 1163: AND object_table_name = 'HZ_GEOGRAPHIES'

1159: FROM hz_geographies
1160: WHERE geography_type = l_zone_type
1161: AND geography_use = 'TAX')
1162: AND object_type = l_zone_type
1163: AND object_table_name = 'HZ_GEOGRAPHIES'
1164: AND relationship_type = 'TAX';
1165:
1166: -- delete tax zone assosiated with this tax zone type
1167:

Line 1168: DELETE hz_geographies

1164: AND relationship_type = 'TAX';
1165:
1166: -- delete tax zone assosiated with this tax zone type
1167:
1168: DELETE hz_geographies
1169: WHERE geography_type = l_zone_type
1170: AND geography_use = 'TAX' ;
1171: END LOOP;
1172: CLOSE c_get_zone_types;

Line 1300: WHERE obj_name='HZ_GEOGRAPHIES';

1296:
1297:
1298: SELECT object_id into l_object_id
1299: FROM FND_OBJECTS
1300: WHERE obj_name='HZ_GEOGRAPHIES';
1301:
1302: -- Replace the ' in predicate with ''
1303: l_predicate := 'GEOGRAPHY_TYPE='||''''||replace(p_zone_type_rec.geography_type,'''','''''')||'''';
1304:

Line 1362: FROM hz_geographies

1358: IF (p_zone_type_rec.limited_by_geography_id IS NOT NULL AND p_zone_type_rec.limited_by_geography_id <> fnd_api.g_miss_num) THEN
1359: BEGIN
1360: SELECT geography_type, country_code
1361: INTO l_limited_by_geo_type, l_country_code
1362: FROM hz_geographies
1363: WHERE geography_id = p_zone_type_rec.limited_by_geography_id;
1364:
1365: SELECT geography_type
1366: INTO l_valid_geo_type

Line 1539: WHERE geography_id in (SELECT geography_id from hz_geographies

1535: -- if yes, then do not allow update
1536:
1537: SELECT count(*) INTO l_count
1538: FROM hz_geography_ranges
1539: WHERE geography_id in (SELECT geography_id from hz_geographies
1540: WHERE geography_type=p_geography_type
1541: AND end_date > sysdate)
1542: AND rownum <2;
1543:

Line 1557: FROM hz_geographies

1553:
1554: IF p_limited_by_geography_id <> l_limited_by_geo_id THEN
1555: --this can be updated only if there are no zones created for this geography_type
1556: SELECT count(*) INTO l_count
1557: FROM hz_geographies
1558: WHERE geography_type = p_geography_type
1559: AND end_date > SYSDATE
1560: AND rownum <3;
1561: