[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
to insert statements.
15-Jan-03 ssouresr 115.3 Allow values for the new column disable
to be inserted/updated
18-mar-03 rmonge 115.4 Modified the chk_city_in_addr procedure
to display a more descriptive error
message. The new message has been
created for this purpose, since the old
or generic message did not explain
what the problem is.
26-Nov-03 ssattini 115.5 Modified the create_unkn_city procedure
to use initcap when validating the
county_name. Fix for bug#3262647.
29-Jan-04 kvsankar 115.8 Modified queries for cursor C1 and
cursor C2 in PROCEDURE 'chk_city_in_addr'
for performance enhancement
(Bug No. 3346024)
29-Jan-04 kvsankar 115.9 Corrected queries for cursor C1 and
cursor C2 in PROCEDURE 'chk_city_in_addr'
for compilation errors.
(Bug No. 3346024)
12-Aug-05 rmonge 115.10 City Form enhacement. As part of the
geocode enhacement project, this
package has been modified to include
a new procedure create_new_geocode.
The new procedure is called from
the cities form allowing the user
to insert a brand new valid geocode
as per Vertex monthly updates.
The procedure create_new_geocode
checks if the geocode does not
exist and inserts it.
If the geocode does not exists, it
first checks for an existing city
name in the same state and county.
And, if there are no other city name
the same, then, it inserts the geocode
If the geocode exist, then, it checks
if the city name exists. If the
geocode exist with a different city
name, the geocode is inserted as
a secondary city.
============================================================================*/
--
/*
--
USAGE
This is called from a form that allows the user to enter
information about the unknown city to be created.
--
DESCRIPTION
Used to create an unknown city in the table structure set up
for the state, county, and city geocodes and corresponding
zip codes.
*/
--
PROCEDURE local_error(p_error_mesg varchar2,
p_procedure varchar2,
p_step number) IS
BEGIN
hr_utility.set_message(801, p_error_mesg);
select state_code, state_abbrev
into l_st_code, l_st_abbrev
from pay_us_states
where state_name = l_state_Name;
select county_code
into l_county_code
from pay_us_counties
where state_code = l_st_code
and initcap(county_name) = l_county_Name;
select city_code
into l_city_code
from pay_us_city_geocodes
where state_code = l_st_code
and county_code = l_county_code
and city_code = p_city_code;
select city_name
into lv_city_name
from pay_us_city_names
where state_code= l_st_code
and county_code = l_county_code
and city_name = l_city_name;
insert into pay_us_city_geocodes
(state_code,
county_code,
city_code)
values
(l_st_code,
l_county_code,
p_city_code);
insert into pay_us_city_names
(city_name,
state_code,
county_code,
city_code,
primary_flag,
disable)
values
(l_city_Name,
l_st_code,
l_county_code,
p_city_code,
'Y',
p_disable);
insert into pay_us_zip_codes
(zip_start,
zip_end,
state_code,
county_code,
city_code)
values
(p_zip_start,
p_zip_end,
l_st_code,
l_county_code,
p_city_code);
/* insert it as a secondary city if the city_name does not
exist already */
/* We only need to insert into pay_us_city_names */
/* as the geocode already exist*/
BEGIN
select city_name
into lv_city_name
from pay_us_city_names
where state_code = l_st_code
and county_code = l_county_code
and city_code = p_city_code
and city_name = l_city_Name ;
insert into pay_us_city_names
(city_name,
state_code,
county_code,
city_code,
primary_flag,
disable)
values
(l_city_Name,
l_st_code,
l_county_code,
l_city_code,
'N',
p_disable);
select city_code
from pay_us_city_geocodes
where state_code = l_st_code and county_code = l_co_code
and city_code like 'U%';
SELECT zc.state_code,
zc.county_code,
zc.city_code,
zc.zip_start,
zc.zip_end
FROM pay_us_zip_codes zc
WHERE zc.state_code = l_st_code
AND zc.county_code = l_co_code
AND zc.city_code = l_ci_code
AND (l_zip_start BETWEEN zc.zip_start AND zc.zip_end
OR l_zip_end BETWEEN zc.zip_start AND zc.zip_end
OR zc.zip_start BETWEEN l_zip_start AND l_zip_end
OR zc.zip_end BETWEEN l_zip_start AND l_zip_end);
select state_code, state_abbrev
into l_st_code, l_st_abbrev
from pay_us_states
where state_name = l_stateName;
hr_utility.trace ('selected state code: ' || l_st_code);
select county_code
into l_co_code
from pay_us_counties
where state_code = l_st_code
and initcap(county_name) = l_countyName;
hr_utility.trace ('selected county code: ' || l_co_code);
select city_code
into l_ci_code
from pay_us_city_names
where city_name = l_cityName and county_code = l_co_code and
state_code = l_st_code;
select count(city_code)
into l_n_ci_code
from pay_us_city_geocodes
where state_code = l_st_code and county_code = l_co_code
and city_code like 'U%';
insert into pay_us_city_geocodes
(state_code, county_code, city_code)
values
(l_st_code, l_co_code, l_ci_code);
hr_utility.trace ('Inserted a geocode.');
insert into pay_us_zip_codes
(zip_start, zip_end, state_code, county_code, city_code)
values
(p_zi_start, p_zi_end, l_st_code, l_co_code, l_ci_code);
hr_utility.trace ('Inserted a zip code.');
insert into pay_us_city_names
(city_name, state_code, county_code, city_code, primary_flag, disable)
values
(l_cityName, l_st_code, l_co_code, l_ci_code, 'N', p_disable);
hr_utility.trace ('Inserted an unknown city.');
DELETE FROM pay_us_zip_codes
WHERE zip_start = zip_exist_rec.zip_start
AND zip_end = zip_exist_rec.zip_end
AND state_code = zip_exist_rec.state_code
AND county_code = zip_exist_rec.county_code
AND city_code = zip_exist_rec.city_code;
INSERT INTO pay_us_zip_codes
(ZIP_START, ZIP_END, STATE_CODE, COUNTY_CODE, CITY_CODE)
VALUES
(l_zip_start,l_zip_end,l_st_code,l_co_code,l_ci_code);
hr_utility.trace ('Inserted a new zip code for an existing city.');
UPDATE pay_us_city_names
SET disable = p_disable
WHERE city_name = l_cityName
AND city_code = l_ci_code
AND county_code = l_co_code
AND state_code = l_st_code;
PROCEDURE Insert_Row(p_city_code IN OUT NOCOPY VARCHAR2,
p_zprowid IN OUT NOCOPY VARCHAR2,
p_cirowid IN OUT NOCOPY VARCHAR2,
p_gerowid IN OUT NOCOPY VARCHAR2,
p_state_code VARCHAR2,
p_county_code VARCHAR2,
p_state_name VARCHAR2,
p_county_name VARCHAR2,
p_city_name VARCHAR2,
p_zip_start VARCHAR2,
p_zip_end VARCHAR2,
p_disable VARCHAR2
) is
--
--
--
l_zip_start pay_us_zip_codes.zip_start%TYPE;
CURSOR C IS SELECT rowid FROM pay_us_zip_codes
WHERE state_code = p_state_code
AND county_code= p_county_code
AND city_code = p_city_code
AND zip_start = l_zip_start
AND zip_end = l_zip_end;
CURSOR C2 is SELECT rowid from pay_us_city_names
WHERE state_code = p_state_code
AND county_code= p_county_code
AND city_code = p_city_code
AND city_name = p_city_name;
CURSOR C3 is SELECT rowid from pay_us_city_geocodes
WHERE state_code = p_state_code
AND county_code= p_county_code
AND city_code = p_city_code;
'pay_us_city_pkg.Insert_Row');
'pay_us_city_pkg.Insert_Row');
'pay_us_city_pkg.Insert_Row');
END Insert_Row;
CURSOR C IS SELECT * FROM pay_us_zip_codes
WHERE rowid = p_zprowid FOR UPDATE of zip_start NOWAIT ;
CURSOR C2 IS SELECT * FROM pay_us_city_names
WHERE rowid = p_cirowid FOR UPDATE OF city_name NOWAIT;
CURSOR C3 IS SELECT * FROM pay_us_city_geocodes
WHERE rowid = p_gerowid FOR UPDATE of City_code NOWAIT;
PROCEDURE Update_Row(p_zprowid VARCHAR2,
p_zip_start VARCHAR2,
p_zip_end VARCHAR2,
p_state_code VARCHAR2,
p_county_code VARCHAR2,
p_city_code VARCHAR2,
p_city_name VARCHAR2,
p_disable VARCHAR2) IS
BEGIN
--
-- The appropriate tables need to be locked during the updating process.
--
--
UPDATE pay_us_zip_codes
SET zip_start = p_zip_start,
zip_end = p_zip_end
WHERE rowid = p_zprowid;
'pay_us_new_cities_pkg.update_row');
UPDATE pay_us_city_names
SET disable = p_disable
WHERE state_code = p_state_code
AND county_code = p_county_code
AND city_code = p_city_code
AND city_name = p_city_name;
'pay_us_new_cities_pkg.update_row');
END Update_Row;
PROCEDURE Delete_Row(p_zprowid VARCHAR2,
p_cirowid VARCHAR2,
p_gerowid VARCHAR2) IS
--
zip_counter NUMBER;
DELETE FROM pay_us_zip_codes
WHERE rowid = p_zprowid;
SELECT count(a.zip_start)
INTO zip_counter
FROM pay_us_zip_codes a,
pay_us_city_names b
WHERE b.rowid = p_cirowid
AND a.city_code = b.city_code
AND a.county_code = b.county_code
AND a.state_code = b.state_code;
DELETE FROM pay_us_city_names
WHERE rowid = p_cirowid;
DELETE FROM pay_us_city_geocodes
WHERE rowid = p_gerowid;
'pay_us_city_pkg.delete_row');
END Delete_Row;
CURSOR C1 is SELECT 'x'
FROM dual
where exists(
SELECT region_1,town_or_city
FROM per_addresses
WHERE region_1 = l_prov_abbrev
AND town_or_city = p_city_name
AND ROWNUM < 2);
CURSOR C2 is SELECT 'x'
FROM dual
where exists(
SELECT region_1,region_2,town_or_city
-- Note that in per_addresses table town_or_city, region 2 and region 1
-- are refered to as
-- city name, state abbreviation and county name respectively.
FROM per_addresses
WHERE region_2 = p_state_abbrev
AND region_1 = p_county_name
AND town_or_city = p_city_name
AND ROWNUM < 2);
SELECT county_abbrev
INTO l_prov_abbrev
FROM pay_us_counties
WHERE county_name = p_county_name
AND state_code = '70';