[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;
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;