[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_geo_location (
p_location_id IN NUMBER,
p_geo IN mdsys.sdo_geometry,
p_geo_status IN VARCHAR2,
x_count IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2
) IS
l_rowid VARCHAR2(1000);
SELECT rowid
FROM hz_locations
WHERE location_id = p_location_id;
hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_geo_location for location_id :'||
TO_CHAR(p_location_id)||'(+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row for location_id :' ||
TO_CHAR(p_location_id)||'(+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_locations_pkg.update_row (
x_rowid => l_rowid,
x_location_id => p_location_id,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_orig_system_reference => NULL,
x_country => NULL,
x_address1 => NULL,
x_address2 => NULL,
x_address3 => NULL,
x_address4 => NULL,
x_city => NULL,
x_postal_code => NULL,
x_state => NULL,
x_province => NULL,
x_county => NULL,
x_address_key => NULL,
x_address_style => NULL,
x_validated_flag => NULL,
x_address_lines_phonetic => NULL,
x_po_box_number => NULL,
x_house_number => NULL,
x_street_suffix => NULL,
x_street => NULL,
x_street_number => NULL,
x_floor => NULL,
x_suite => NULL,
x_postal_plus4_code => NULL,
x_position => NULL,
x_location_directions => NULL,
x_address_effective_date => NULL,
x_address_expiration_date => NULL,
x_clli_code => NULL,
x_language => NULL,
x_short_description => NULL,
x_description => NULL,
x_content_source_type => NULL,
x_loc_hierarchy_id => NULL,
x_sales_tax_geocode => NULL,
x_sales_tax_inside_city_limits => NULL,
x_fa_location_id => NULL,
x_geometry => p_geo,
x_geometry_status_code => p_geo_status,
x_object_version_number => NULL,
x_timezone_id => NULL,
x_created_by_module => NULL,
x_application_id => NULL,
--3326341.
x_delivery_point_code => NULL
);
hz_utility_v2pub.debug(p_message=>'Location successfully updated for location_id :'||TO_CHAR(p_location_id),
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row for location_id :' ||
TO_CHAR(p_location_id)||'(-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
fnd_message.set_name('AR','HZ_LOCATION_UPDATED');
hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_geo_location for location_id :'
|| TO_CHAR(p_location_id)||'(-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_geo_location;
PROCEDURE update_geometry (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_loc_type IN VARCHAR2 DEFAULT 'P',
p_site_use_type IN VARCHAR2 DEFAULT NULL,
p_country IN VARCHAR2 DEFAULT NULL,
p_iden_addr_only IN VARCHAR2 DEFAULT 'N',
p_incremental IN VARCHAR2 DEFAULT 'N',
p_all_partial IN VARCHAR2 DEFAULT 'ALL',
p_nb_row_update IN VARCHAR2 DEFAULT 'ALL',
p_nb_row IN NUMBER DEFAULT 20,
p_nb_try IN NUMBER DEFAULT 3
) IS
/* TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE EXISTS (SELECT 1
FROM hz_party_site_uses hpsu,
hz_party_sites hps
WHERE hpsu.site_use_type = p_site_use_type
AND hpsu.party_site_id = hps.party_site_id
AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
AND hps.location_id = hl.location_id)
AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE EXISTS (SELECT 1
FROM hz_party_site_uses hpsu,
hz_party_sites hps
WHERE hpsu.site_use_type = p_site_use_type
AND hpsu.party_site_id = hps.party_site_id
AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
AND hps.location_id = hl.location_id)
AND hl.country = p_country
AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE EXISTS (SELECT 1
FROM hz_party_site_uses hpsu,
hz_party_sites hps
WHERE hpsu.site_use_type = p_site_use_type
AND hpsu.party_site_id = hps.party_site_id
AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
AND hps.location_id = hl.location_id)
AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE EXISTS (SELECT 1
FROM hz_party_site_uses hpsu,
hz_party_sites hps
WHERE hpsu.site_use_type = p_site_use_type
AND hpsu.party_site_id = hps.party_site_id
AND hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
AND hps.location_id = hl.location_id)
AND hl.country = p_country
AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE EXISTS (SELECT 1
FROM hz_party_sites hps
WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
AND hps.location_id = hl.location_id)
AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE EXISTS (SELECT 1
FROM hz_party_sites hps
WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
AND hps.location_id = hl.location_id)
AND hl.country = p_country
AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2);
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE EXISTS (SELECT 1
FROM hz_party_sites hps
WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
AND hps.location_id = hl.location_id)
AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE EXISTS (SELECT 1
FROM hz_party_sites hps
WHERE hps.identifying_address_flag = DECODE(p_iden_addr_only, 'Y', 'Y', hps.identifying_address_flag)
AND hps.location_id = hl.location_id)
AND hl.country = p_country
AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL);
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
AND NOT EXISTS (SELECT 1
FROM HZ_PARTY_SITES hps
WHERE hps.location_id = hl.location_id );
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE hl.country = p_country
AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
AND NOT EXISTS (SELECT 1
FROM HZ_PARTY_SITES hps
WHERE hps.location_id = hl.location_id );
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL)
AND NOT EXISTS (SELECT 1
FROM HZ_PARTY_SITES hps
WHERE hps.location_id = hl.location_id );
SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4,
hl.city, hl.postal_code, hl.state, hl.country, hl.county, hl.province
FROM hz_locations hl
WHERE hl.country = p_country
AND NVL(hl.request_id, -1) <> NVL(p_request_id, -2)
AND (hl.geometry_status_code = 'DIRTY' OR hl.geometry_status_code IS NULL)
AND NOT EXISTS (SELECT 1
FROM HZ_PARTY_SITES hps
WHERE hps.location_id = hl.location_id );
cpt_update NUMBER := 0;
l_nb_row_update NUMBER DEFAULT NULL;
l_nb_update NUMBER;
/* l_nb_update := 0;
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
IF p_nb_row_update IS NULL OR p_nb_row_update = 'ALL' THEN
l_nb_row_update := 1000;
l_nb_row_update := TO_NUMBER(p_nb_row_update);
IF l_nb_row_update IS NULL OR l_nb_row_update <= 0 THEN
time_put_line('At least one row error.');
IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
time_put_line('Exiting because partial=' || p_all_partial);
time_put_line('cpt_update=' || cpt_update);
time_put_line('nb_row_update=' || l_nb_row_update);
hz_utility_v2pub.debug(p_message=>'cpt_update=' || cpt_update,
p_prefix=>'WARNING',
p_msg_level=>fnd_log.level_exception);
hz_utility_v2pub.debug(p_message=>'nb_row_update=' || l_nb_row_update,
p_prefix=>'WARNING',
p_msg_level=>fnd_log.level_exception);
cpt_update := cpt_update + 1;
OR (p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update)
THEN
-- Process the records in the array.
hz_geocode_pkg.get_spatial_coords(
p_loc_array => l_array,
p_name => NULL,
p_http_ad => l_http_ad,
p_proxy => l_proxy,
p_port => l_port,
p_retry => l_nb_retries,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
l_array.DELETE;
update_geo_location(
p_location_id => l_array(j).location_id,
p_geo => l_array(j).geometry,
p_geo_status => l_array(j).geometry_status_code,
x_count => l_nb_update,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
l_array.DELETE;
IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
time_put_line('Exiting due to update limit.');
hz_utility_v2pub.debug(p_message=>'Exiting due to update limit.',
p_prefix=>'WARNING',
p_msg_level=>fnd_log.level_exception);
l_location_ids.DELETE;
l_address1s.DELETE;
l_address2s.DELETE;
l_address3s.DELETE;
l_address4s.DELETE;
l_cities.DELETE;
l_postal_codes.DELETE;
l_counties.DELETE;
l_states.DELETE;
l_provinces.DELETE;
l_countries.DELETE;
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- expect_http_ad)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- port_number)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- nlsnumexp)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- atleastonerow)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- morethanmaxrow)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- exchttp)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- others)',
p_prefix=>'SQL ERROR',
p_msg_level=>fnd_log.level_error);
END update_geometry;
P_NB_ROW_UPDATE IN VARCHAR2,
P_STATE IN VARCHAR2,
P_PROVINCE IN VARCHAR2,
P_CITY IN VARCHAR2,
P_GEOMETRY_ACCURACY IN NUMBER,
P_GEOMETRY_SOURCE IN VARCHAR2,
p_party_site_status IN VARCHAR2,
P_MC_1 IN NUMBER,
P_MC_2 IN NUMBER,
P_MC_3 IN NUMBER,
P_MC_4 IN NUMBER,
P_MC_10 IN NUMBER,
P_MC_11 IN NUMBER,
P_MC_0 IN NUMBER
) IS
l_total_rec_processed NUMBER := P_MC_1+P_MC_2+P_MC_3+P_MC_4+P_MC_10+P_MC_11+P_MC_0;
SELECT lookup_code,description,decode(lookup_code,0,2,1)
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'HZ_SPATIAL_ACCURACY_MAPPING'
ORDER BY decode(lookup_code,0,2,1),TO_NUMBER(lookup_code);
SELECT meaning
INTO l_site_use_type
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'PARTY_SITE_USE_CODE'
AND LOOKUP_CODE = p_site_use_type;
SELECT meaning
INTO l_loc_type
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'HZ_ELOCATION_TYPE'
AND LOOKUP_CODE = p_loc_type;
SELECT meaning
INTO l_country
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'HZ_SPATIAL_VALID_COUNTRIES'
AND LOOKUP_CODE = p_country;
SELECT meaning
INTO l_geometry_source
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'HZ_GEOMETRY_SOURCE'
AND LOOKUP_CODE = p_geometry_source;
SELECT meaning
INTO l_geometry_accuracy
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'HZ_SPATIAL_ACCURACY_MAPPING'
AND LOOKUP_CODE = p_geometry_accuracy;
SELECT meaning
INTO l_party_site_status
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'HZ_LOC_STATUS'
AND LOOKUP_CODE = p_party_site_status;
fnd_file.put_line(fnd_file.log,'Update Records for: '||NVL(p_all_partial,'NULL'));
fnd_file.put_line(fnd_file.log,'Number of Records in Subset: '||NVL(p_nb_row_update,'NULL'));
PROCEDURE update_geometry_all (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_loc_type IN VARCHAR2 DEFAULT 'P',
p_site_use_type IN VARCHAR2 DEFAULT NULL,
p_country IN VARCHAR2 DEFAULT NULL,
p_iden_addr_only IN VARCHAR2 DEFAULT 'N',
p_incremental IN VARCHAR2 DEFAULT 'N',
p_all_partial IN VARCHAR2 DEFAULT 'ALL',
p_nb_row_update IN VARCHAR2 DEFAULT 'ALL',
p_state IN VARCHAR2 DEFAULT NULL,
p_province IN VARCHAR2 DEFAULT NULL,
p_city IN VARCHAR2 DEFAULT NULL,
p_geometry_accuracy IN VARCHAR2 DEFAULT NULL,
p_geometry_source IN VARCHAR2 DEFAULT 'ELOCATION',
p_party_site_status IN VARCHAR2 DEFAULT NULL,
p_nb_row IN NUMBER DEFAULT 10,
p_nb_try IN NUMBER DEFAULT 3
) IS
TYPE locationlist IS TABLE OF hz_locations.location_id%TYPE;
cpt_update NUMBER := 0;
l_nb_row_update NUMBER DEFAULT NULL;
l_nb_update NUMBER;
l_nb_update := 0;
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
IF p_nb_row_update IS NULL OR p_nb_row_update = 'ALL' THEN
l_nb_row_update := 1000;
l_nb_row_update := TO_NUMBER(p_nb_row_update);
IF l_nb_row_update IS NULL OR l_nb_row_update <= 0 THEN
time_put_line('At least one row error.');
hz_utility_v2pub.debug(p_message=>'p_nb_row :'||p_nb_row||' p_nb_try :'||p_nb_try||' p_nb_row_update :'||p_nb_row_update||' p_country :'||p_country
||' p_state :'||p_state||' p_province :'||p_province||' p_city :'||p_city||' p_geometry_accuracy :'||p_geometry_accuracy
||' p_geometry_source :'||p_geometry_source||' p_incremental :'||p_incremental
||' p_loc_type :'||p_loc_type||' p_site_use_type :'||p_site_use_type,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
l_qry_str := 'SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4, hl.city, ';
l_qry_str := l_qry_str ||' AND NOT EXISTS (SELECT 1 FROM hz_party_sites hps WHERE hps.location_id = hl.location_id ) ' ;
l_qry_str := l_qry_str ||' AND EXISTS ( SELECT 1 FROM hz_party_sites hps WHERE hps.location_id = hl.location_id ' ;
l_qry_str := l_qry_str ||' AND EXISTS ( SELECT 1 FROM hz_party_sites hps,hz_party_site_uses hpsu WHERE hps.location_id = hl.location_id ' ;
-- Exit the loop if our update limit has been exceeded.
IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
time_put_line('Exiting because partial=' || p_all_partial);
time_put_line('cpt_update=' || cpt_update);
time_put_line('nb_row_update=' || l_nb_row_update);
hz_utility_v2pub.debug(p_message=>'Exiting because partial=' || p_all_partial||' cpt_update=' || cpt_update
||' nb_row_update=' || l_nb_row_update,
p_prefix=>'WARNING',
p_msg_level=>fnd_log.level_exception);
cpt_update := cpt_update + 1;
OR (p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update)
THEN
-- Process the records in the array.
hz_geocode_pkg.get_spatial_coords(
p_loc_array => l_array,
p_name => NULL,
p_http_ad => l_http_ad,
p_proxy => l_proxy,
p_port => l_port,
p_retry => l_nb_retries,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
l_array.DELETE;
/* update_geo_location(
p_location_id => l_array(j).location_id,
p_geo => l_array(j).geometry,
p_geo_status => l_array(j).geometry_status_code,
x_count => l_nb_update,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
-- Instead of calling Table Handler changed to direct update statement.
IF l_array(j).geometry_accuracy = 0 THEN
UPDATE HZ_LOCATIONS
SET geometry = NULL,
geometry_status_code = l_array(j).geometry_status_code,
geometry_source = 'ELOCATION',
geometry_accuracy = l_array(j).geometry_accuracy,
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = l_request_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE location_id = l_array(j).location_id;
UPDATE HZ_LOCATIONS
SET geometry = l_array(j).geometry,
geometry_status_code = l_array(j).geometry_status_code,
geometry_source = 'ELOCATION',
geometry_accuracy = l_array(j).geometry_accuracy,
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = l_request_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE location_id = l_array(j).location_id;
l_array.DELETE;
IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
time_put_line('Exiting due to update limit.');
hz_utility_v2pub.debug(p_message=>'Exiting due to update limit.',
p_prefix=>'WARNING',
p_msg_level=>fnd_log.level_exception);
l_location_ids.DELETE;
l_address1s.DELETE;
l_address2s.DELETE;
l_address3s.DELETE;
l_address4s.DELETE;
l_cities.DELETE;
l_postal_codes.DELETE;
l_counties.DELETE;
l_states.DELETE;
l_provinces.DELETE;
l_countries.DELETE;
,p_nb_row_update => p_nb_row_update
,p_state => p_state
,p_province => p_province
,p_city => p_city
,p_geometry_accuracy => p_geometry_accuracy
,p_geometry_source => p_geometry_source
,p_party_site_status => p_party_site_status
,p_mc_1 => l_mc_1
,p_mc_2 => l_mc_2
,p_mc_3 => l_mc_3
,p_mc_4 => l_mc_4
,p_mc_10 => l_mc_10
,p_mc_11 => l_mc_11
,p_mc_0 => l_mc_0
);
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry_all (- expect_http_ad)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- port_number)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- nlsnumexp)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry_all (- atleastonerow)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry_all (- morethanmaxrow)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- exchttp)',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- others)',
p_prefix=>'SQL ERROR',
p_msg_level=>fnd_log.level_error);
END update_geometry_all;
SELECT status, domidx_opstatus
FROM sys.all_indexes
WHERE index_name = 'HZ_LOCATIONS_N15' and owner = x_index_owner;
SELECT object_owner, object_name, policy_name
FROM sys.all_policies
WHERE enable LIKE 'Y%'
AND object_name = 'HZ_LOCATIONS';
SELECT object_owner, object_name, policy_name
FROM sys.dba_policies
WHERE enable = 'YES'
AND object_owner = l_schema
AND object_name = 'HZ_LOCATIONS';
/* x_del_meta := 'Delete user_sdo_geom_metadata
Where table_name = ''HZ_LOCATIONS''
And column_name= ''GEOMETRY''';
x_del_meta := 'Delete MDSYS.SDO_GEOM_METADATA_TABLE
Where sdo_table_name = ''HZ_LOCATIONS''
AND sdo_column_name= ''GEOMETRY''
AND sdo_owner = '''||x_index_owner||'''';
/* x_ins_meta := 'INSERT INTO user_sdo_geom_metadata (
table_name, column_name, diminfo, srid ) VALUES (
''HZ_LOCATIONS'', ''GEOMETRY'',
mdsys.sdo_dim_array(
mdsys.sdo_dim_element(''longitude'', -180, 180, 0.00005),
mdsys.sdo_dim_element(''latitude'', -90, 90, 0.00005)), 8307 )';
x_ins_meta := 'INSERT INTO MDSYS.SDO_GEOM_METADATA_TABLE (
sdo_owner, sdo_table_name, sdo_column_name, sdo_diminfo, sdo_srid ) VALUES ('''
||x_index_owner||''', '||
'''HZ_LOCATIONS'', ''GEOMETRY'',
mdsys.sdo_dim_array(
mdsys.sdo_dim_element(''longitude'', -180, 180, 0.00005),
mdsys.sdo_dim_element(''latitude'', -90, 90, 0.00005)), 8307 )';
l_owners.DELETE;
l_objects.DELETE;
l_policies.DELETE;