1 PACKAGE BODY PAY_CA_CITY_PKG as
2 /* $Header: pycaukno.pkb 120.0 2011/03/03 10:51:20 rgottipa noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1995 Oracle Corporation |
5 | All rights reserved. |
6 +============================================================================+
7 Name
8 PAY_CA_CITY_PKG
9 Purpose
10 Supports the city block in the form pycaukcy (CA CITIES).
11 Notes
12
13 History
14 03-Mar-2011 rgottipa 9840322 115.0 Data created.
15 ============================================================================*/
16 --
17 /*
18 --
19 USAGE
20 This is called from a form that allows the user to enter
21 information about the unknown city to be created.
22 --
23 DESCRIPTION
24 Used to create an unknown city in the table structure set up
25 for the state, county, and city geocodes and corresponding
26 zip codes.
27 */
28 --
29
30 PROCEDURE local_error(p_error_mesg varchar2,
31 p_procedure varchar2,
32 p_step number) IS
33 BEGIN
34
35 hr_utility.set_message(801, p_error_mesg);
36 hr_utility.set_message_token('PROCEDURE', 'pyusukno.'||p_procedure);
37 hr_utility.set_message_token('STEP', p_step);
38 hr_utility.raise_error;
39
40 END local_error;
41
42 --------------------------------------------------------------------------
43 -- Name --
44 -- Create_new_geocode
45 -- Added by Rmonge
46 -- Enhacement to allow a user to insert a new geocode.
47 -- Purpose --
48 -- Procedure that supports the insert of a new geocode via the city form
49 -- Notes --
50 -- --
51 -----------------------------------------------------------------------------
52 --
53 PROCEDURE Create_new_geocode (p_city_code IN OUT NOCOPY VARCHAR2,
54 p_zprowid IN OUT NOCOPY VARCHAR2,
55 p_cirowid IN OUT NOCOPY VARCHAR2,
56 p_gerowid IN OUT NOCOPY VARCHAR2,
57 p_state_code VARCHAR2,
58 p_county_code VARCHAR2,
59 p_state_name VARCHAR2,
60 p_county_name VARCHAR2,
61 p_city_name VARCHAR2,
62 p_zip_start VARCHAR2,
63 p_zip_end VARCHAR2,
64 p_disable VARCHAR2
65 ) is
66
67 -- Define Local Variables to hold local information
68
69 l_zip_start pay_us_zip_codes.zip_start%TYPE;
70 l_zip_end pay_us_zip_codes.zip_end%TYPE;
71 l_st_code pay_us_states.state_code%TYPE;
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);
81 lv_new_geo varchar2(1);
82
83
84
85 BEGIN
86 -- Add the following to make sure the Names are initcap
87
88 l_state_Name := initcap(p_state_name);
89 l_county_Name := initcap(p_county_name);
90 l_city_Name := p_city_name;
91
92 lv_new_geo := 'N';
93 /* get state, county and city codes. */
94
95 BEGIN
96 select state_code, state_abbrev
97 into l_st_code, l_st_abbrev
98 from pay_us_states
99 where state_name = l_state_Name;
100
101 EXCEPTION WHEN no_data_found THEN
102 hr_utility.trace ('Error: Failed to find state codes.');
103 local_error ('HR_7952_ADDR_NO_STATE_CODE', 'create_new_geocode', 1);
104
105 END ;
106
107 BEGIN
108 select county_code
109 into l_county_code
110 from pay_us_counties
111 where state_code = l_st_code
112 and initcap(county_name) = l_county_Name;
113
114 EXCEPTION WHEN no_data_found THEN
115 hr_utility.trace ('Error: Failed to find county codes.');
116 local_error ('HR_7953_ADDR_NO_COUNTY_FOUND', 'create_new_geocode', 2);
117
118 END;
119
120 BEGIN
121 select city_code
122 into l_city_code
123 from pay_us_city_geocodes
124 where state_code = l_st_code
125 and county_code = l_county_code
126 and city_code = p_city_code;
127
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 lv_found := 'N';
131
132 END ;
133
134
135 IF SQL%NOTFOUND THEN
136
137 /* City Code does not exist */
138 /* Check to see if there is another city name */
139 /* exactly the same in the same state and county */
140 /* if the same name if found, then, we have to raise */
141 /* an error as this will cause a problem with tax */
142 /* records */
143 BEGIN
144
145 lv_new_geo := 'N';
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
155 EXCEPTION WHEN NO_DATA_FOUND THEN
156 lv_new_geo := 'Y' ;
157 END ;
158
159 IF lv_new_geo ='Y' THEN
160
161 insert into pay_us_city_geocodes
162 (state_code,
163 county_code,
164 city_code)
165 values
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,
175 primary_flag,
176 disable)
177 values
178 (l_city_Name,
179 l_st_code,
180 l_county_code,
181 p_city_code,
182 'Y',
183 p_disable);
184
185
186 insert into pay_us_zip_codes
187 (zip_start,
188 zip_end,
189 state_code,
190 county_code,
191 city_code)
192 values
193 (p_zip_start,
194 p_zip_end,
195 l_st_code,
196 l_county_code,
197 p_city_code);
198 ELSE /* lv_new_geo = 'N' */
199 /* Display a more meaningful error here */
200 hr_utility.trace ('Error: Given city is already created.');
201 local_error ('PAY_DUPL_CITY_NAME', 'create_new_geocode', 3);
202 END IF;
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
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 ;
218
219 EXCEPTION
220 WHEN NO_DATA_FOUND THEN
221 lv_found := 'N';
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,
231 primary_flag,
232 disable)
233 values
234 (l_city_Name,
235 l_st_code,
236 l_county_code,
237 l_city_code,
238 'N',
239 p_disable);
240
241
242 ELSE
243 hr_utility.trace ('Error: Given city is already created.');
244 local_error ('PAY_DUPL_CITY_NAME', 'create_new_geocode', 4);
245 END IF; /* SQL NOT FOUND */
246 END IF; /* not found */
247 END create_new_geocode ;
248
249
250
251 PROCEDURE create_unkn_city (p_ci_code IN OUT NOCOPY varchar2,
252 p_st_name IN varchar2,
253 p_co_name IN varchar2,
254 p_ci_name IN varchar2,
255 p_zi_start IN OUT NOCOPY varchar2,
256 p_zi_end IN OUT NOCOPY varchar2,
257 p_disable IN varchar2) IS
258
259 l_n_ci_code number := 0;
260 l_max_ci_code number := 0;
261 l_n_ci_name number := 0;
262
263 l_zip_start pay_us_zip_codes.zip_start%TYPE;
264 l_zip_end pay_us_zip_codes.zip_end%TYPE;
265 l_st_code pay_us_states.state_code%TYPE;
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
275 from pay_us_city_geocodes
276 where state_code = l_st_code and county_code = l_co_code
277 and city_code like 'U%';
278
279 CURSOR zip_exist_c IS
280 SELECT zc.state_code,
281 zc.county_code,
282 zc.city_code,
283 zc.zip_start,
284 zc.zip_end
285 FROM pay_us_zip_codes zc
286 WHERE zc.state_code = l_st_code
287 AND zc.county_code = l_co_code
288 AND zc.city_code = l_ci_code
289 AND (l_zip_start BETWEEN zc.zip_start AND zc.zip_end
290 OR l_zip_end BETWEEN zc.zip_start AND zc.zip_end
291 OR zc.zip_start BETWEEN l_zip_start AND l_zip_end
292 OR zc.zip_end BETWEEN l_zip_start AND l_zip_end);
293
294 zip_exist_rec zip_exist_c%ROWTYPE;
295
296 BEGIN
297
298 /* force case of all passed-in parameters to match that stored in DB. */
299
300 hr_utility.trace ('County Name: '||p_co_name);
301 hr_utility.trace ('State Name: '||p_st_name);
302
303 l_stateName := initcap(p_st_name);
304 l_countyName := initcap(p_co_name);
305 l_cityName := p_ci_name;
306
307 hr_utility.trace ('County name after initcap :'||l_countyName);
308 hr_utility.trace ('State name after initcap :'||l_stateName);
309 /* get state, county and city codes. */
310
311 BEGIN
312 select state_code, state_abbrev
313 into l_st_code, l_st_abbrev
314 from pay_us_states
315 where state_name = l_stateName;
316
317 EXCEPTION WHEN no_data_found THEN
318 hr_utility.trace ('Error: Failed to find state codes.');
319 local_error ('HR_7952_ADDR_NO_STATE_CODE', 'create_unkn_city', 1);
320 END;
321
322 hr_utility.trace ('selected state code: ' || l_st_code);
323 hr_utility.trace ('l_countyName: ' || l_countyName);
324
325 BEGIN
326 select county_code
327 into l_co_code
328 from pay_us_counties
329 where state_code = l_st_code
330 and initcap(county_name) = l_countyName;
331
332 EXCEPTION WHEN no_data_found THEN
333 hr_utility.trace ('Error: Failed to find county codes.');
334 local_error ('HR_7953_ADDR_NO_COUNTY_FOUND', 'create_unkn_city', 2);
335 END;
336
337 hr_utility.trace ('selected county code: ' || l_co_code);
338
339 /* see if this city is unknown. */
340
341 if hr_us_ff_udfs.addr_val(l_st_abbrev, l_countyName, l_cityName,
342 p_zi_start,'Y') = '00-000-0000' then
343 /*
344 check for an existing city whose zip codes are wrong or
345 altered, which addr_val would still return '00-000-0000'.
346 */
347 l_n_ci_name := -1;
348 BEGIN
349 select city_code
350 into l_ci_code
351 from pay_us_city_names
355 WHEN no_data_found THEN l_n_ci_name := 0;
352 where city_name = l_cityName and county_code = l_co_code and
353 state_code = l_st_code;
354 EXCEPTION
356 WHEN others THEN l_n_ci_name := 1;
357 END;
358
359 if l_n_ci_name = 0 then
360
361 /* get number of unknown cities previous entered. */
362 select count(city_code)
363 into l_n_ci_code
364 from pay_us_city_geocodes
365 where state_code = l_st_code and county_code = l_co_code
366 and city_code like 'U%';
367
368 hr_utility.trace ('Number of unknown cities '||l_n_ci_code);
369
370 if l_n_ci_code <= 0 then
371 l_ci_code := 'U000';
372 hr_utility.trace ('Initial city code is:'||l_ci_code);
373 else
374 /*
375 use explicit cursor to step through each of the unknown
376 city codes to find the latest city code.
377 */
378 for l_city_rec in c_city_code loop
379
380 if fnd_number.canonical_to_number(substr(l_city_rec.city_code,2,3)) >
381 l_max_ci_code then
382
383 l_max_ci_code :=
384 fnd_number.canonical_to_number(substr(l_city_rec.city_code,2,3));
385 hr_utility.trace ('Max city code is:'||
386 l_city_rec.city_code);
387
388 end if;
389 end loop;
390
391 if l_max_ci_code >= 0 and l_max_ci_code < 9 then
392 l_ci_code := 'U00'||to_char(l_max_ci_code + 1);
393 elsif l_max_ci_code >= 9 and l_max_ci_code < 99 then
394 l_ci_code := 'U0'||to_char(l_max_ci_code + 1);
395 else
396 l_ci_code := 'U'||to_char(l_max_ci_code + 1);
397 end if;
398
399 hr_utility.trace ('Final city code is:'||l_ci_code);
400 end if;
401 p_ci_code := l_ci_code;
402
403 insert into pay_us_city_geocodes
404 (state_code, county_code, city_code)
405 values
406 (l_st_code, l_co_code, l_ci_code);
407 hr_utility.trace ('Inserted a geocode.');
408
409 insert into pay_us_zip_codes
410 (zip_start, zip_end, state_code, county_code, city_code)
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.');
420
421 elsif (l_n_ci_name = 1) then
422 hr_utility.trace ('Error: Given city is already created.');
423 local_error ('HR_7954_ADDR_NOT_UNKNOWN_CITY',
424 'create_unkn_city', 3);
425 else
426 /* a new zip range for an existing city has been entered */
427 l_zip_end := p_zi_end;
428 l_zip_start := p_zi_start;
429
430 OPEN zip_exist_c;
431 LOOP
432 FETCH zip_exist_c INTO zip_exist_rec;
433 EXIT WHEN zip_exist_c%NOTFOUND;
434 IF zip_exist_rec.zip_start < l_zip_start THEN
435 l_zip_start := zip_exist_rec.zip_start;
436 END IF;
437 IF zip_exist_rec.zip_end > l_zip_end THEN
438 l_zip_end := zip_exist_rec.zip_end;
439 END IF;
440
441 DELETE FROM pay_us_zip_codes
442 WHERE zip_start = zip_exist_rec.zip_start
443 AND zip_end = zip_exist_rec.zip_end
444 AND state_code = zip_exist_rec.state_code
445 AND county_code = zip_exist_rec.county_code
446 AND city_code = zip_exist_rec.city_code;
447
448 END LOOP;
449 CLOSE zip_exist_c;
450
451 INSERT INTO pay_us_zip_codes
452 (ZIP_START, ZIP_END, STATE_CODE, COUNTY_CODE, CITY_CODE)
453 VALUES
454 (l_zip_start,l_zip_end,l_st_code,l_co_code,l_ci_code);
455 hr_utility.trace ('Inserted a new zip code for an existing city.');
456
457 p_ci_code := l_ci_code;
458 p_zi_end := l_zip_end;
459 p_zi_start := l_zip_start;
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
469 AND state_code = l_st_code;
470
471 end if;
472 else
473 hr_utility.trace ('Error: Given city is already created.');
474 local_error ('HR_7954_ADDR_NOT_UNKNOWN_CITY', 'create_unkn_city', 4);
475 end if;
476
477 END create_unkn_city;
478 --------------------------------------------------------------------------
479 -- Name --
480 -- Insert_Row --
481 -- Purpose --
482 -- Table handler procedure that supports the insert of a new city via --
483 -- the city form. --
484 -- Notes --
488 PROCEDURE Insert_Row(p_city_code IN OUT NOCOPY VARCHAR2,
485 -- --
486 -----------------------------------------------------------------------------
487 --
489 p_zprowid IN OUT NOCOPY VARCHAR2,
490 p_cirowid IN OUT NOCOPY VARCHAR2,
491 p_gerowid IN OUT NOCOPY VARCHAR2,
492 p_state_code VARCHAR2,
493 p_county_code VARCHAR2,
494 p_state_name VARCHAR2,
495 p_county_name VARCHAR2,
496 p_city_name VARCHAR2,
497 p_zip_start VARCHAR2,
498 p_zip_end VARCHAR2,
499 p_disable VARCHAR2
500 ) is
501
502 --
503 --
504 --
505 l_zip_start pay_us_zip_codes.zip_start%TYPE;
506 l_zip_end pay_us_zip_codes.zip_end%TYPE;
507
508 CURSOR C IS SELECT rowid FROM pay_us_zip_codes
509 WHERE state_code = p_state_code
510 AND county_code= p_county_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;
520 --
521 CURSOR C3 is SELECT rowid from pay_us_city_geocodes
522 WHERE state_code = p_state_code
523 AND county_code= p_county_code
524 AND city_code = p_city_code;
525
526 --
527 BEGIN
528 --
529 /* hr_utility.trace_on('Y','CACITY');*/
530 l_zip_start := p_zip_start;
531 l_zip_end := p_zip_end;
532
533 create_unkn_city (p_city_code,
534 p_state_name,
535 p_county_name,
536 p_city_name,
537 l_zip_start,
538 l_zip_end,
539 p_disable);
540 --
541 OPEN C;
542 FETCH C INTO p_zprowid;
543 IF (C%NOTFOUND) THEN
544 CLOSE C;
545 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
546 hr_utility.set_message_token('PROCEDURE',
547 'PAY_CA_CITY_PKG.Insert_Row');
548 hr_utility.set_message_token('STEP','1');
549 hr_utility.raise_error;
550 END if;
551
552 --
553 OPEN C2;
554 FETCH C2 INTO p_cirowid;
555 IF (C2%NOTFOUND) THEN
556 CLOSE C2;
557 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
558 hr_utility.set_message_token('PROCEDURE',
559 'PAY_CA_CITY_PKG.Insert_Row');
560 hr_utility.set_message_token('STEP','2');
561 hr_utility.raise_error;
562 END if;
563
564 --
565 OPEN C3;
566 FETCH C3 INTO p_gerowid;
567 IF (C3%NOTFOUND) THEN
568 CLOSE C3;
569 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
570 hr_utility.set_message_token('PROCEDURE',
571 'PAY_CA_CITY_PKG.Insert_Row');
572 hr_utility.set_message_token('STEP','3');
573 hr_utility.raise_error;
574 END if;
575
576 /* hr_utility.trace_off; */
577 END Insert_Row;
578
579 -----------------------------------------------------------------------------
580 -- Name --
581 -- Lock_Row --
582 -- Purpose --
583 -- Table handler procedure that supports the update and delete --
584 -- of a city by applying a lock on a city block in City Form. --
585 -- Arguments --
586 -- See below. --
587 -- Notes --
588 -- None. --
589 -----------------------------------------------------------------------------
590 --
591 PROCEDURE Lock_Row( p_zprowid VARCHAR2,
592 p_cirowid VARCHAR2,
593 p_gerowid VARCHAR2,
594 p_state_code VARCHAR2,
595 p_county_code VARCHAR2,
596 p_city_code VARCHAR2,
597 p_state_name VARCHAR2,
598 p_county_name VARCHAR2,
599 p_city_name VARCHAR2,
600 p_zip_start VARCHAR2,
601 p_zip_end VARCHAR2) IS
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;
611 --
612 l_recinfo C%ROWTYPE;
613 l_recinfo2 C2%ROWTYPE;
614 l_recinfo3 C3%ROWTYPE;
615 --
616 BEGIN
617 --
618 OPEN C;
619 FETCH C INTO l_recinfo;
620 if (C%NOTFOUND) then
621 CLOSE C;
622 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
623 hr_utility.set_message_token('PROCEDURE',
624 'PAY_CA_CITY_PKG.lock_row');
625 hr_utility.set_message_token('STEP','1');
626 hr_utility.raise_error;
627 end if;
628 --
629 OPEN C2;
630 FETCH C2 INTO l_recinfo2;
631 if (C2%NOTFOUND) then
632 CLOSE C2;
633 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
634 hr_utility.set_message_token('PROCEDURE',
635 'PAY_CA_CITY_PKG.lock_row');
636 hr_utility.set_message_token('STEP','2');
640 OPEN C3;
637 hr_utility.raise_error;
638 end if;
639 --
641 FETCH C3 INTO l_recinfo3;
642 if (C3%NOTFOUND) then
643 CLOSE C3;
644 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
645 hr_utility.set_message_token('PROCEDURE',
646 'PAY_CA_CITY_PKG.lock_row');
647 hr_utility.set_message_token('STEP','3');
648 hr_utility.raise_error;
649 end if;
650
651 --
652 -- Remove trailing spaces.
653 l_recinfo.state_code := rtrim(l_recinfo.state_code);
654 l_recinfo.county_code := rtrim(l_recinfo.county_code);
655 l_recinfo.city_code := rtrim(l_recinfo.city_code);
656 l_recinfo.zip_start := rtrim(l_recinfo.zip_start);
657 l_recinfo.zip_end := rtrim(l_recinfo.zip_end);
658 --
659 l_recinfo2.state_code := rtrim(l_recinfo2.state_code);
660 l_recinfo2.county_code := rtrim(l_recinfo2.county_code);
661 l_recinfo2.city_code := rtrim(l_recinfo2.city_code);
662 l_recinfo2.city_name := rtrim(l_recinfo2.city_name);
663 --
664 IF ( ( (l_recinfo.zip_start = p_zip_start)
665 OR ( (l_recinfo.zip_start IS NULL)
666 AND (p_zip_start IS NULL)))
667 AND ( (l_recinfo.zip_end = p_zip_end)
668 OR ( (l_recinfo.zip_end IS NULL)
669 AND (p_zip_end IS NULL)))
670 AND ( (l_recinfo2.city_name = p_city_name)
671 OR ( (l_recinfo2.city_name IS NULL)
672 AND (p_city_name IS NULL)))
673 ) then
674 return;
675 else
676 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
677 APP_EXCEPTION.RAISE_EXCEPTION;
678 end if;
679 --
680 END Lock_Row;
681 --
682 -----------------------------------------------------------------------------
683 -- Name --
684 -- Update_Row --
685 -- Purpose --
686 -- Table handler procedure that supports the update of a city via --
687 -- the city form. --
688 -- Arguments --
689 -- See below. --
690 -- Notes --
691 -- None. --
692 -----------------------------------------------------------------------------
693 --
694 PROCEDURE Update_Row(p_zprowid VARCHAR2,
695 p_zip_start VARCHAR2,
696 p_zip_end VARCHAR2,
697 p_state_code VARCHAR2,
698 p_county_code VARCHAR2,
699 p_city_code VARCHAR2,
700 p_city_name VARCHAR2,
701 p_disable VARCHAR2) IS
702 BEGIN
703 --
704 -- The appropriate tables need to be locked during the updating process.
705 --
706 --
707 UPDATE pay_us_zip_codes
708 SET zip_start = p_zip_start,
709 zip_end = p_zip_end
710 WHERE rowid = p_zprowid;
711 --
712 if (SQL%NOTFOUND) then
713 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
714 hr_utility.set_message_token('PROCEDURE',
715 'pay_us_new_cities_pkg.update_row');
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
725 AND city_name = p_city_name;
726 --
727 if (SQL%NOTFOUND) then
728 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
729 hr_utility.set_message_token('PROCEDURE',
730 'pay_us_new_cities_pkg.update_row');
731 hr_utility.set_message_token('STEP','1');
732 hr_utility.raise_error;
733 end if;
734 --
735 END Update_Row;
736 --
737 -----------------------------------------------------------------------------
738 -- Name --
739 -- Delete_Row --
740 -- Purpose --
741 -- Table handler procedure that supports the delete of a city via --
742 -- the create city form. --
743 -- Arguments --
744 -- See below. --
745 -- Notes --
746 -- --
747 -----------------------------------------------------------------------------
748 --
749 PROCEDURE Delete_Row(p_zprowid VARCHAR2,
750 p_cirowid VARCHAR2,
751 p_gerowid VARCHAR2) IS
752 --
753
754 zip_counter NUMBER;
755
756 BEGIN
757 --
758 --
759 --
760 -- Deleting from the pay_us_zip_codes table.
761 --
762 DELETE FROM pay_us_zip_codes
763 WHERE rowid = p_zprowid;
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;
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 --
781 -- Deleting from the pay_us_city_geocodes
782 --
783 DELETE FROM pay_us_city_geocodes
784 WHERE rowid = p_gerowid;
785
786 END IF;
787 --
788 if (SQL%NOTFOUND) then
789 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
790 hr_utility.set_message_token('PROCEDURE',
791 'PAY_CA_CITY_PKG.delete_row');
792 hr_utility.set_message_token('STEP','3');
793 hr_utility.raise_error;
794 end if;
795 END Delete_Row;
796 --
797 -- The Next procedure is used for checking and validating data
798 --
799 ------------------------------------------------------------------------------
800 -- Name
801 -- chk_city_in_addr
802 -- Purpose
803 -- To check a given city exists in the per_addresses table
804 -- It would not allow the city to be deleted if it is referenced in
805 -- the per_addresses table. Validation check to prevent deletion
806 -- if city is referenced.
807 -- Notes
808 -- This only confirms the existence of a city in the per_addresses
809 -- table if and only if the city within an state and county
810 -- matches to that of the per_addresses table.
811 ------------------------------------------------------------------------------
812 PROCEDURE chk_city_in_addr(p_state_abbrev VARCHAR2,
813 p_county_name VARCHAR2,
814 p_city_name VARCHAR2) IS
815 --
816 l_prov_abbrev varchar2(5);
817
818 /*Modified query for performance enhancement Bug No. 3346024 */
819 CURSOR C1 is SELECT 'x'
820 FROM dual
821 where exists(
822 SELECT region_1,town_or_city
823 FROM per_addresses
824 WHERE region_1 = l_prov_abbrev
825 AND town_or_city = p_city_name
826 AND ROWNUM < 2);
827
828 /*Modified query for performance enhancement Bug No. 3346024 */
829 CURSOR C2 is SELECT 'x'
830 FROM dual
831 where exists(
832 SELECT region_1,region_2,town_or_city
833 -- Note that in per_addresses table town_or_city, region 2 and region 1
834 -- are refered to as
835 -- city name, state abbreviation and county name respectively.
836 FROM per_addresses
837 WHERE region_2 = p_state_abbrev
838 AND region_1 = p_county_name
839 AND town_or_city = p_city_name
840 AND ROWNUM < 2);
841
842 l_recinfo1 C1%ROWTYPE;
843 l_recinfo2 C2%ROWTYPE;
844 --
845 BEGIN
846
847 /* If we are dealing with a Canadian city then region_2 is ignored
848 because it is not populated */
849
850 IF p_state_abbrev = 'CN' THEN
851
852 SELECT county_abbrev
853 INTO l_prov_abbrev
854 FROM pay_us_counties
855 WHERE county_name = p_county_name
856 AND state_code = '70';
857
858 OPEN C1;
859 FETCH C1 INTO l_recinfo1;
860 IF (C1%FOUND) THEN
861 /* rosie monge 18-mar-03 2844658 */
862 /*
863 hr_utility.set_message (801,'HR_6153_all_PROCEDURE_FAIL');
864 hr_utility.set_message_token ('PROCEDURE',
865 'pay_us_chk_addr_pkg.chk_city_in_addr');
866 hr_utility.set_message_token ('STEP', '1');
867 */
868 hr_utility.set_message(801,'PAY_74153_CITY_CANNOT_DISABLE');
869 hr_utility.raise_error;
870 END IF;
871 CLOSE C1;
872
873 ELSE
874 OPEN C2;
875 FETCH C2 INTO l_recinfo2;
876 IF (C2%FOUND) THEN
877 /* rmonge fix for bug 2844658 */
878 /*
879 hr_utility.set_message (801,'HR_6153_all_PROCEDURE_FAIL');
880 hr_utility.set_message_token ('PROCEDURE',
881 'pay_us_chk_addr_pkg.chk_city_in_addr');
882 hr_utility.set_message_token ('STEP', '1');
883 */
884 hr_utility.set_message(801,'PAY_74153_CITY_CANNOT_DISABLE');
885 hr_utility.raise_error;
886 END IF;
887 CLOSE C2;
888
889 END IF;
890
891 RETURN;
892
893 END chk_city_in_addr;
894
895 END PAY_CA_CITY_PKG;
[Home] [Help]