[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_CA_CITY_PKG.Insert_Row');
'PAY_CA_CITY_PKG.Insert_Row');
'PAY_CA_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_CA_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';