The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT address_style
INTO l_context
FROM fnd_territories
WHERE territory_code = p_territory_code
AND l_application_id = 222
AND address_style = p_context ;
SELECT descriptive_flex_context_code
INTO l_context
FROM fnd_descr_flex_contexts_vl
WHERE application_id = l_application_id
AND descriptive_flexfield_name = l_descriptive_flexfield_name
AND descriptive_flex_context_code = p_context;
SELECT 'Exists'
INTO l_exists
FROM fnd_columns col, fnd_tables tbl
WHERE tbl.table_id = col.table_id
AND tbl.application_id = col.application_id
AND tbl.application_id= l_application_id
AND col.column_name = p_loc_comp
AND tbl.table_name = p_location_table_name
AND col.column_name NOT IN ('LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY',
'LAST_UPDATE_DATE', 'LAST_UPDATE_LOGIN');
SELECT 'Exists'
INTO l_exists
FROM hz_geography_types_b
WHERE geography_type = UPPER(p_geo_type);
SELECT geography_type, geography_element_column
FROM hz_geo_structure_levels
WHERE country_code = p_country
START WITH parent_geography_type = p_parent_geo_type
AND country_code = p_country
CONNECT BY PRIOR geography_type = parent_geography_type
AND country_code = p_country;
SELECT geography_element_column
INTO p_geo_element_col
FROM hz_geo_structure_levels
WHERE geography_type = p_geography_type
AND parent_geography_type = p_parent_geography_type
AND country_code = p_country;
SELECT level+1 seq_num
FROM hz_geo_structure_levels
WHERE geography_type = c_geo_type
START WITH parent_geography_type = 'COUNTRY'
AND country_code = c_country
CONNECT BY PRIOR geography_type = parent_geography_type
AND country_code = c_country;
SELECT territory_code
INTO l_country
FROM fnd_territories
WHERE territory_code = UPPER(p_geo_struct_map_rec.country_code);
BEGIN --insert row
SELECT HZ_GEO_STRUCT_MAP_S.nextval
INTO l_map_id
FROM dual;
hz_geo_struct_map_pvt.insert_row(
x_map_row_id,
l_map_id,
p_geo_struct_map_rec.country_code,
p_geo_struct_map_rec.loc_tbl_name,
p_geo_struct_map_rec.address_style);
hz_geo_struct_map_dtl_pvt.insert_row(
x_map_dtl_row_id,
l_map_id,
l_geo_struct_map_dtl_tbl(i).loc_seq_num,
l_geo_struct_map_dtl_tbl(i).loc_comp,
l_geo_struct_map_dtl_tbl(i).geo_type,
l_geo_struct_map_dtl_tbl(i).geo_element_col);
END; -- insert row
PROCEDURE delete_geo_struct_mapping(
p_map_id IN NUMBER,
p_location_table_name IN VARCHAR2,
p_country IN VARCHAR2,
p_address_style IN VARCHAR2,
p_geo_struct_map_dtl_tbl IN geo_struct_map_dtl_tbl_type,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_map_id NUMBER;
SELECT usage_id
FROM hz_address_usages
WHERE map_id = p_map_id;
SELECT dtl.usage_id
FROM Hz_address_usages usg, Hz_address_usage_dtls dtl
WHERE usg.map_id = p_map_id
AND dtl.geography_type = c_geography_type
AND dtl.usage_id = usg.usage_id;
l_delete_mapping_also VARCHAR(1);
SELECT map_id
INTO l_map_id
FROM hz_geo_struct_map
WHERE country_code = p_country
AND loc_tbl_name = p_location_table_name
AND address_style IS NULL;
SELECT map_id
INTO l_map_id
FROM hz_geo_struct_map
WHERE country_code = p_country
AND loc_tbl_name = p_location_table_name
AND address_style = p_address_style;
SELECT map_id
INTO l_map_id
FROM hz_geo_struct_map
WHERE map_id = p_map_id;
SELECT count(*)
INTO l_count
FROM hz_geo_struct_map_dtl
WHERE map_id = p_map_id;
l_delete_mapping_also := 'N';
SELECT geography_type
INTO l_del_geo_type
FROM hz_geo_struct_map_dtl
WHERE map_id = p_map_id
AND geography_type <> p_geo_struct_map_dtl_tbl(i).geo_type;
l_delete_mapping_also := 'Y';
l_delete_mapping_also := 'Y';
HZ_ADDRESS_USAGES_PUB.delete_address_usages(
p_usage_id => l_usage_id,
p_address_usage_dtls_tbl => l_address_usage_dtls_tbl,
p_init_msg_list => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
hz_geo_struct_map_dtl_pvt.delete_row(l_map_id,p_geo_struct_map_dtl_tbl(i).geo_type);
hz_geo_struct_map_dtl_pvt.delete_row(l_map_id,l_del_geo_type);
IF (l_delete_mapping_also = 'Y') THEN
-- delete mapping record also
hz_geo_struct_map_pvt.delete_row(l_map_id);
SELECT count(*)
INTO l_count
FROM hz_address_usage_dtls
WHERE usage_id = i.usage_id;
HZ_ADDRESS_USAGES_PUB.delete_address_usages(
p_usage_id => i.usage_id,
p_address_usage_dtls_tbl => l_address_usage_dtls_tbl,
p_init_msg_list => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT count(*)
INTO l_count
FROM hz_geo_struct_map_dtl
WHERE map_id = l_map_id;
hz_geo_struct_map_dtl_pvt.delete_row(l_map_id);
hz_geo_struct_map_pvt.delete_row(l_map_id);
END delete_geo_struct_mapping;
SELECT country_code, loc_tbl_name
FROM hz_geo_struct_map
WHERE map_id = c_map_id;
SELECT count(*)
FROM hz_geo_struct_map_dtl
WHERE map_id = c_map_id;
SELECT level+1 seq_num, geography_element_column
FROM hz_geo_structure_levels
WHERE geography_type = c_geo_type
START WITH parent_geography_type = 'COUNTRY'
AND country_code = c_country
CONNECT BY PRIOR geography_type = parent_geography_type
AND country_code = c_country;
BEGIN --insert row
i := l_geo_struct_map_dtl_tbl.first;
hz_geo_struct_map_dtl_pvt.insert_row(
x_map_dtl_row_id,
p_map_id,
l_geo_struct_map_dtl_tbl(i).loc_seq_num,
l_geo_struct_map_dtl_tbl(i).loc_comp,
l_geo_struct_map_dtl_tbl(i).geo_type,
l_geo_struct_map_dtl_tbl(i).geo_element_col);
END; -- insert row
PROCEDURE update_geo_struct_map_dtls
(p_map_id IN NUMBER,
p_geo_struct_map_dtl_tbl IN geo_struct_map_dtl_tbl_type,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
p_index_name VARCHAR2(30);
SELECT country_code, loc_tbl_name
FROM hz_geo_struct_map
WHERE map_id = c_map_id;
SELECT rowid, loc_seq_num,geo_element_col
FROM hz_geo_struct_map_dtl
WHERE map_id = p_map_id
AND geography_type = c_geography_type;
SAVEPOINT update_geo_struct_map_dtls;
hz_geo_struct_map_dtl_pvt.Update_Row (
x_rowid => l_rowid,
x_map_id => p_map_id,
x_loc_seq_num => l_loc_seq_num,
x_loc_component => p_geo_struct_map_dtl_tbl(i).loc_comp,
x_geography_type => p_geo_struct_map_dtl_tbl(i).geo_type,
x_geo_element_col => l_geo_element_column);
SELECT count(*)
INTO l_count
FROM Hz_address_usages usg, Hz_address_usage_dtls dtl
WHERE usg.map_id = p_map_id
AND usg.status_flag = 'A'
AND dtl.usage_id = usg.usage_id;
ROLLBACK TO update_geo_struct_map_dtls;
ROLLBACK TO update_geo_struct_map_dtls;
ROLLBACK TO update_geo_struct_map_dtls;
ROLLBACK TO update_geo_struct_map_dtls;
END update_geo_struct_map_dtls;