[Home] [Help]
31: CURSOR c_get_zones(c_object_type IN VARCHAR2, c_geography_id IN NUMBER) IS
32: SELECT subject_id
33: FROM hz_relationships
34: WHERE subject_type = p_zone_type
35: AND subject_table_name = 'HZ_GEOGRAPHIES'
36: AND object_type = c_object_type
37: AND object_id = c_geography_id
38: AND object_table_name = 'HZ_GEOGRAPHIES'
39: AND relationship_type = 'TAX'
34: WHERE subject_type = p_zone_type
35: AND subject_table_name = 'HZ_GEOGRAPHIES'
36: AND object_type = c_object_type
37: AND object_id = c_geography_id
38: AND object_table_name = 'HZ_GEOGRAPHIES'
39: AND relationship_type = 'TAX'
40: AND directional_flag = 'F'
41: AND relationship_code = 'PARENT_OF'
42: AND p_date BETWEEN start_date and end_date;
157: -- if l_ref_geo_type is COUNTRY get geography_id from hz_geograpies
158: IF l_ref_geo_type = 'COUNTRY' THEN
159: SELECT geography_id
160: INTO l_geography_id
161: FROM hz_geographies
162: WHERE country_code = l_country_code
163: AND geography_type = l_ref_geo_type
164: -- Bug 5410283 (Added on 07-Aug-2006)
165: AND p_date BETWEEN start_date AND end_date;
294: SELECT geography_id, geography_code, geography_name
295: INTO l_zone_tbl_final(l_tbl_count+1).zone_id,
296: l_zone_tbl_final(l_tbl_count+1).zone_code,
297: l_zone_tbl_final(l_tbl_count+1).zone_name
298: FROM hz_geographies
299: WHERE geography_id = l_zone_tbl(i).zone_id
300: -- Bug 5410283 (Added on 07-Aug-2006)
301: AND p_date BETWEEN start_date AND end_date;
302: l_zone_tbl_final(l_tbl_count+1).zone_type := p_zone_type;
303: END IF;
304: ELSE
305: SELECT geography_id, geography_code, geography_name
306: INTO l_zone_tbl_final(i).zone_id, l_zone_tbl_final(i).zone_code, l_zone_tbl_final(i).zone_name
307: FROM hz_geographies
308: WHERE geography_id = l_zone_tbl(i).zone_id
309: -- Bug 5410283 (Added on 07-Aug-2006)
310: AND p_date BETWEEN start_date AND end_date;
311:
367:
368: Begin
369: Begin
370: select geography_element1_id, geography_type into l_country_id, l_geo_type
371: from hz_geographies
372: where geography_id = l_geo_id;
373: Exception
374: When no_data_found then
375: return hz_utility_v2pub.Get_LookupMeaning('AR_LOOKUPS', 'HZ_GEO_DISPLAY_ONLY', 'WORLD');
385: end loop;
386: close g_structure;
387:
388: OPEN conc_geo FOR
389: 'select '||l_clause||' from hz_geographies where geography_id = :1' USING l_geo_id;
390: fetch conc_geo into l_display_val;
391: close conc_geo;
392: return l_display_val;
393: end get_conc_name;