[Home] [Help]
72: l_st_abbrev pay_us_states.state_abbrev%TYPE;
73: l_state_Name pay_us_states.state_name%TYPE;
74: l_county_code pay_us_counties.county_code%TYPE;
75: l_county_Name pay_us_counties.county_name%TYPE;
76: l_city_code pay_us_city_names.city_code%TYPE;
77: l_city_Name pay_us_city_names.city_name%TYPE;
78: lv_city_Name pay_us_city_names.city_name%TYPE;
79:
80: lv_found varchar2(1);
73: l_state_Name pay_us_states.state_name%TYPE;
74: l_county_code pay_us_counties.county_code%TYPE;
75: l_county_Name pay_us_counties.county_name%TYPE;
76: l_city_code pay_us_city_names.city_code%TYPE;
77: l_city_Name pay_us_city_names.city_name%TYPE;
78: lv_city_Name pay_us_city_names.city_name%TYPE;
79:
80: lv_found varchar2(1);
81: lv_new_geo varchar2(1);
74: l_county_code pay_us_counties.county_code%TYPE;
75: l_county_Name pay_us_counties.county_name%TYPE;
76: l_city_code pay_us_city_names.city_code%TYPE;
77: l_city_Name pay_us_city_names.city_name%TYPE;
78: lv_city_Name pay_us_city_names.city_name%TYPE;
79:
80: lv_found varchar2(1);
81: lv_new_geo varchar2(1);
82:
146:
147:
148: select city_name
149: into lv_city_name
150: from pay_us_city_names
151: where state_code= l_st_code
152: and county_code = l_county_code
153: and city_name = l_city_name;
154:
166: (l_st_code,
167: l_county_code,
168: p_city_code);
169:
170: insert into pay_us_city_names
171: (city_name,
172: state_code,
173: county_code,
174: city_code,
203:
204: ELSE
205: /* insert it as a secondary city if the city_name does not
206: exist already */
207: /* We only need to insert into pay_us_city_names */
208: /* as the geocode already exist*/
209: BEGIN
210:
211: select city_name
209: BEGIN
210:
211: select city_name
212: into lv_city_name
213: from pay_us_city_names
214: where state_code = l_st_code
215: and county_code = l_county_code
216: and city_code = p_city_code
217: and city_name = l_city_Name ;
222: END;
223:
224: IF SQL%NOTFOUND THEN
225:
226: insert into pay_us_city_names
227: (city_name,
228: state_code,
229: county_code,
230: city_code,
266: l_st_abbrev pay_us_states.state_abbrev%TYPE;
267: l_stateName pay_us_states.state_name%TYPE;
268: l_co_code pay_us_counties.county_code%TYPE;
269: l_countyName pay_us_counties.county_name%TYPE;
270: l_ci_code pay_us_city_names.city_code%TYPE;
271: l_cityName pay_us_city_names.city_name%TYPE;
272:
273: CURSOR c_city_code IS
274: select city_code
267: l_stateName pay_us_states.state_name%TYPE;
268: l_co_code pay_us_counties.county_code%TYPE;
269: l_countyName pay_us_counties.county_name%TYPE;
270: l_ci_code pay_us_city_names.city_code%TYPE;
271: l_cityName pay_us_city_names.city_name%TYPE;
272:
273: CURSOR c_city_code IS
274: select city_code
275: from pay_us_city_geocodes
347: l_n_ci_name := -1;
348: BEGIN
349: select city_code
350: into l_ci_code
351: from pay_us_city_names
352: where city_name = l_cityName and county_code = l_co_code and
353: state_code = l_st_code;
354: EXCEPTION
355: WHEN no_data_found THEN l_n_ci_name := 0;
411: values
412: (p_zi_start, p_zi_end, l_st_code, l_co_code, l_ci_code);
413: hr_utility.trace ('Inserted a zip code.');
414:
415: insert into pay_us_city_names
416: (city_name, state_code, county_code, city_code, primary_flag, disable)
417: values
418: (l_cityName, l_st_code, l_co_code, l_ci_code, 'N', p_disable);
419: hr_utility.trace ('Inserted an unknown city.');
460:
461: /* In case the user is also updating the disable flag as well as
462: entering a new zip range for an existing city */
463:
464: UPDATE pay_us_city_names
465: SET disable = p_disable
466: WHERE city_name = l_cityName
467: AND city_code = l_ci_code
468: AND county_code = l_co_code
511: AND city_code = p_city_code
512: AND zip_start = l_zip_start
513: AND zip_end = l_zip_end;
514: --
515: CURSOR C2 is SELECT rowid from pay_us_city_names
516: WHERE state_code = p_state_code
517: AND county_code= p_county_code
518: AND city_code = p_city_code
519: AND city_name = p_city_name;
602: --
603: CURSOR C IS SELECT * FROM pay_us_zip_codes
604: WHERE rowid = p_zprowid FOR UPDATE of zip_start NOWAIT ;
605: --
606: CURSOR C2 IS SELECT * FROM pay_us_city_names
607: WHERE rowid = p_cirowid FOR UPDATE OF city_name NOWAIT;
608: --
609: CURSOR C3 IS SELECT * FROM pay_us_city_geocodes
610: WHERE rowid = p_gerowid FOR UPDATE of City_code NOWAIT;
716: hr_utility.set_message_token('STEP','1');
717: hr_utility.raise_error;
718: end if;
719: --
720: UPDATE pay_us_city_names
721: SET disable = p_disable
722: WHERE state_code = p_state_code
723: AND county_code = p_county_code
724: AND city_code = p_city_code
764:
765: SELECT count(a.zip_start)
766: INTO zip_counter
767: FROM pay_us_zip_codes a,
768: pay_us_city_names b
769: WHERE b.rowid = p_cirowid
770: AND a.city_code = b.city_code
771: AND a.county_code = b.county_code
772: AND a.state_code = b.state_code;
772: AND a.state_code = b.state_code;
773:
774: IF (zip_counter = 0) THEN
775: --
776: -- Deleting from the pay_us_city_names
777: --
778: DELETE FROM pay_us_city_names
779: WHERE rowid = p_cirowid;
780: --
774: IF (zip_counter = 0) THEN
775: --
776: -- Deleting from the pay_us_city_names
777: --
778: DELETE FROM pay_us_city_names
779: WHERE rowid = p_cirowid;
780: --
781: -- Deleting from the pay_us_city_geocodes
782: --