[Home] [Help]
122: l_st_abbrev pay_us_states.state_abbrev%TYPE;
123: l_state_Name pay_us_states.state_name%TYPE;
124: l_county_code pay_us_counties.county_code%TYPE;
125: l_county_Name pay_us_counties.county_name%TYPE;
126: l_city_code pay_us_city_names.city_code%TYPE;
127: l_city_Name pay_us_city_names.city_name%TYPE;
128: lv_city_Name pay_us_city_names.city_name%TYPE;
129:
130: lv_found varchar2(1);
123: l_state_Name pay_us_states.state_name%TYPE;
124: l_county_code pay_us_counties.county_code%TYPE;
125: l_county_Name pay_us_counties.county_name%TYPE;
126: l_city_code pay_us_city_names.city_code%TYPE;
127: l_city_Name pay_us_city_names.city_name%TYPE;
128: lv_city_Name pay_us_city_names.city_name%TYPE;
129:
130: lv_found varchar2(1);
131: lv_new_geo varchar2(1);
124: l_county_code pay_us_counties.county_code%TYPE;
125: l_county_Name pay_us_counties.county_name%TYPE;
126: l_city_code pay_us_city_names.city_code%TYPE;
127: l_city_Name pay_us_city_names.city_name%TYPE;
128: lv_city_Name pay_us_city_names.city_name%TYPE;
129:
130: lv_found varchar2(1);
131: lv_new_geo varchar2(1);
132:
196:
197:
198: select city_name
199: into lv_city_name
200: from pay_us_city_names
201: where state_code= l_st_code
202: and county_code = l_county_code
203: and city_name = l_city_name;
204:
216: (l_st_code,
217: l_county_code,
218: p_city_code);
219:
220: insert into pay_us_city_names
221: (city_name,
222: state_code,
223: county_code,
224: city_code,
253:
254: ELSE
255: /* insert it as a secondary city if the city_name does not
256: exist already */
257: /* We only need to insert into pay_us_city_names */
258: /* as the geocode already exist*/
259: BEGIN
260:
261: select city_name
259: BEGIN
260:
261: select city_name
262: into lv_city_name
263: from pay_us_city_names
264: where state_code = l_st_code
265: and county_code = l_county_code
266: and city_code = p_city_code
267: and city_name = l_city_Name ;
272: END;
273:
274: IF SQL%NOTFOUND THEN
275:
276: insert into pay_us_city_names
277: (city_name,
278: state_code,
279: county_code,
280: city_code,
316: l_st_abbrev pay_us_states.state_abbrev%TYPE;
317: l_stateName pay_us_states.state_name%TYPE;
318: l_co_code pay_us_counties.county_code%TYPE;
319: l_countyName pay_us_counties.county_name%TYPE;
320: l_ci_code pay_us_city_names.city_code%TYPE;
321: l_cityName pay_us_city_names.city_name%TYPE;
322:
323: CURSOR c_city_code IS
324: select city_code
317: l_stateName pay_us_states.state_name%TYPE;
318: l_co_code pay_us_counties.county_code%TYPE;
319: l_countyName pay_us_counties.county_name%TYPE;
320: l_ci_code pay_us_city_names.city_code%TYPE;
321: l_cityName pay_us_city_names.city_name%TYPE;
322:
323: CURSOR c_city_code IS
324: select city_code
325: from pay_us_city_geocodes
397: l_n_ci_name := -1;
398: BEGIN
399: select city_code
400: into l_ci_code
401: from pay_us_city_names
402: where city_name = l_cityName and county_code = l_co_code and
403: state_code = l_st_code;
404: EXCEPTION
405: WHEN no_data_found THEN l_n_ci_name := 0;
461: values
462: (p_zi_start, p_zi_end, l_st_code, l_co_code, l_ci_code);
463: hr_utility.trace ('Inserted a zip code.');
464:
465: insert into pay_us_city_names
466: (city_name, state_code, county_code, city_code, primary_flag, disable)
467: values
468: (l_cityName, l_st_code, l_co_code, l_ci_code, 'N', p_disable);
469: hr_utility.trace ('Inserted an unknown city.');
510:
511: /* In case the user is also updating the disable flag as well as
512: entering a new zip range for an existing city */
513:
514: UPDATE pay_us_city_names
515: SET disable = p_disable
516: WHERE city_name = l_cityName
517: AND city_code = l_ci_code
518: AND county_code = l_co_code
561: AND city_code = p_city_code
562: AND zip_start = l_zip_start
563: AND zip_end = l_zip_end;
564: --
565: CURSOR C2 is SELECT rowid from pay_us_city_names
566: WHERE state_code = p_state_code
567: AND county_code= p_county_code
568: AND city_code = p_city_code
569: AND city_name = p_city_name;
652: --
653: CURSOR C IS SELECT * FROM pay_us_zip_codes
654: WHERE rowid = p_zprowid FOR UPDATE of zip_start NOWAIT ;
655: --
656: CURSOR C2 IS SELECT * FROM pay_us_city_names
657: WHERE rowid = p_cirowid FOR UPDATE OF city_name NOWAIT;
658: --
659: CURSOR C3 IS SELECT * FROM pay_us_city_geocodes
660: WHERE rowid = p_gerowid FOR UPDATE of City_code NOWAIT;
766: hr_utility.set_message_token('STEP','1');
767: hr_utility.raise_error;
768: end if;
769: --
770: UPDATE pay_us_city_names
771: SET disable = p_disable
772: WHERE state_code = p_state_code
773: AND county_code = p_county_code
774: AND city_code = p_city_code
814:
815: SELECT count(a.zip_start)
816: INTO zip_counter
817: FROM pay_us_zip_codes a,
818: pay_us_city_names b
819: WHERE b.rowid = p_cirowid
820: AND a.city_code = b.city_code
821: AND a.county_code = b.county_code
822: AND a.state_code = b.state_code;
822: AND a.state_code = b.state_code;
823:
824: IF (zip_counter = 0) THEN
825: --
826: -- Deleting from the pay_us_city_names
827: --
828: DELETE FROM pay_us_city_names
829: WHERE rowid = p_cirowid;
830: --
824: IF (zip_counter = 0) THEN
825: --
826: -- Deleting from the pay_us_city_names
827: --
828: DELETE FROM pay_us_city_names
829: WHERE rowid = p_cirowid;
830: --
831: -- Deleting from the pay_us_city_geocodes
832: --