1 PACKAGE BODY PAY_US_CITY_PKG as
2 /* $Header: pyusukno.pkb 120.1 2005/08/17 09:48:12 rmonge noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1995 Oracle Corporation |
5 | All rights reserved. |
6 +============================================================================+
7 Name
8 pay_us_city_pkg
9 Purpose
10 Supports the city block in the form pyusukcy (US CITIES).
11 Notes
12
13 History
14 25-AUG-95 F. Assadi 40.0 Date created.
15 03-Dec-97 K.Mundair 40.4(110.1) Bug 509120. Added functionality
16 to allow zip codes to be entered
17 via the form and to allow city names
18 and zip ranges existing in one
19 county to be entered into another.
20 17-Sep-01 ptitoren 115.2 Added explicit list of columns
21 to insert statements.
22 15-Jan-03 ssouresr 115.3 Allow values for the new column disable
23 to be inserted/updated
24 18-mar-03 rmonge 115.4 Modified the chk_city_in_addr procedure
25 to display a more descriptive error
26 message. The new message has been
27 created for this purpose, since the old
28 or generic message did not explain
29 what the problem is.
30 26-Nov-03 ssattini 115.5 Modified the create_unkn_city procedure
31 to use initcap when validating the
32 county_name. Fix for bug#3262647.
33 29-Jan-04 kvsankar 115.8 Modified queries for cursor C1 and
34 cursor C2 in PROCEDURE 'chk_city_in_addr'
35 for performance enhancement
36 (Bug No. 3346024)
37 29-Jan-04 kvsankar 115.9 Corrected queries for cursor C1 and
38 cursor C2 in PROCEDURE 'chk_city_in_addr'
39 for compilation errors.
40 (Bug No. 3346024)
41
42 12-Aug-05 rmonge 115.10 City Form enhacement. As part of the
43 geocode enhacement project, this
44 package has been modified to include
45 a new procedure create_new_geocode.
46 The new procedure is called from
47 the cities form allowing the user
48 to insert a brand new valid geocode
49 as per Vertex monthly updates.
50 The procedure create_new_geocode
51 checks if the geocode does not
52 exist and inserts it.
53 If the geocode does not exists, it
54 first checks for an existing city
55 name in the same state and county.
56 And, if there are no other city name
57 the same, then, it inserts the geocode
58 If the geocode exist, then, it checks
59 if the city name exists. If the
60 geocode exist with a different city
61 name, the geocode is inserted as
62 a secondary city.
63
64
65 ============================================================================*/
66 --
67 /*
68 --
69 USAGE
70 This is called from a form that allows the user to enter
71 information about the unknown city to be created.
72 --
73 DESCRIPTION
74 Used to create an unknown city in the table structure set up
75 for the state, county, and city geocodes and corresponding
76 zip codes.
77 */
78 --
79
80 PROCEDURE local_error(p_error_mesg varchar2,
81 p_procedure varchar2,
82 p_step number) IS
83 BEGIN
84
85 hr_utility.set_message(801, p_error_mesg);
86 hr_utility.set_message_token('PROCEDURE', 'pyusukno.'||p_procedure);
87 hr_utility.set_message_token('STEP', p_step);
88 hr_utility.raise_error;
89
90 END local_error;
91
92 --------------------------------------------------------------------------
93 -- Name --
94 -- Create_new_geocode
95 -- Added by Rmonge
96 -- Enhacement to allow a user to insert a new geocode.
97 -- Purpose --
98 -- Procedure that supports the insert of a new geocode via the city form
99 -- Notes --
100 -- --
101 -----------------------------------------------------------------------------
102 --
103 PROCEDURE Create_new_geocode (p_city_code IN OUT NOCOPY VARCHAR2,
104 p_zprowid IN OUT NOCOPY VARCHAR2,
105 p_cirowid IN OUT NOCOPY VARCHAR2,
106 p_gerowid IN OUT NOCOPY VARCHAR2,
107 p_state_code VARCHAR2,
108 p_county_code VARCHAR2,
109 p_state_name VARCHAR2,
110 p_county_name VARCHAR2,
111 p_city_name VARCHAR2,
112 p_zip_start VARCHAR2,
113 p_zip_end VARCHAR2,
114 p_disable VARCHAR2
115 ) is
116
117 -- Define Local Variables to hold local information
118
119 l_zip_start pay_us_zip_codes.zip_start%TYPE;
120 l_zip_end pay_us_zip_codes.zip_end%TYPE;
121 l_st_code pay_us_states.state_code%TYPE;
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);
131 lv_new_geo varchar2(1);
132
133
134
135 BEGIN
136 -- Add the following to make sure the Names are initcap
137
138 l_state_Name := initcap(p_state_name);
139 l_county_Name := initcap(p_county_name);
140 l_city_Name := initcap(p_city_name);
141
142 lv_new_geo := 'N';
143 /* get state, county and city codes. */
144
145 BEGIN
146 select state_code, state_abbrev
147 into l_st_code, l_st_abbrev
148 from pay_us_states
149 where state_name = l_state_Name;
150
151 EXCEPTION WHEN no_data_found THEN
152 hr_utility.trace ('Error: Failed to find state codes.');
153 local_error ('HR_7952_ADDR_NO_STATE_CODE', 'create_new_geocode', 1);
154
155 END ;
156
157 BEGIN
158 select county_code
159 into l_county_code
160 from pay_us_counties
161 where state_code = l_st_code
162 and initcap(county_name) = l_county_Name;
163
164 EXCEPTION WHEN no_data_found THEN
165 hr_utility.trace ('Error: Failed to find county codes.');
166 local_error ('HR_7953_ADDR_NO_COUNTY_FOUND', 'create_new_geocode', 2);
167
168 END;
169
170 BEGIN
171 select city_code
172 into l_city_code
173 from pay_us_city_geocodes
174 where state_code = l_st_code
175 and county_code = l_county_code
176 and city_code = p_city_code;
177
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 lv_found := 'N';
181
182 END ;
183
184
185 IF SQL%NOTFOUND THEN
186
187 /* City Code does not exist */
188 /* Check to see if there is another city name */
189 /* exactly the same in the same state and county */
190 /* if the same name if found, then, we have to raise */
191 /* an error as this will cause a problem with tax */
192 /* records */
193 BEGIN
194
195 lv_new_geo := 'N';
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
205 EXCEPTION WHEN NO_DATA_FOUND THEN
206 lv_new_geo := 'Y' ;
207 END ;
208
209 IF lv_new_geo ='Y' THEN
210
211 insert into pay_us_city_geocodes
212 (state_code,
213 county_code,
214 city_code)
215 values
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,
225 primary_flag,
226 disable)
227 values
228 (l_city_Name,
229 l_st_code,
230 l_county_code,
231 p_city_code,
232 'Y',
233 p_disable);
234
235
236 insert into pay_us_zip_codes
237 (zip_start,
238 zip_end,
239 state_code,
240 county_code,
241 city_code)
242 values
243 (p_zip_start,
244 p_zip_end,
245 l_st_code,
246 l_county_code,
247 p_city_code);
248 ELSE /* lv_new_geo = 'N' */
249 /* Display a more meaningful error here */
250 hr_utility.trace ('Error: Given city is already created.');
251 local_error ('PAY_DUPL_CITY_NAME', 'create_new_geocode', 3);
252 END IF;
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
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 ;
268
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 lv_found := 'N';
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,
281 primary_flag,
282 disable)
283 values
284 (l_city_Name,
285 l_st_code,
286 l_county_code,
287 l_city_code,
288 'N',
289 p_disable);
290
291
292 ELSE
293 hr_utility.trace ('Error: Given city is already created.');
297 END create_new_geocode ;
294 local_error ('PAY_DUPL_CITY_NAME', 'create_new_geocode', 4);
295 END IF; /* SQL NOT FOUND */
296 END IF; /* not found */
298
299
300
301 PROCEDURE create_unkn_city (p_ci_code IN OUT NOCOPY varchar2,
302 p_st_name IN varchar2,
303 p_co_name IN varchar2,
304 p_ci_name IN varchar2,
305 p_zi_start IN OUT NOCOPY varchar2,
306 p_zi_end IN OUT NOCOPY varchar2,
307 p_disable IN varchar2) IS
308
309 l_n_ci_code number := 0;
310 l_max_ci_code number := 0;
311 l_n_ci_name number := 0;
312
313 l_zip_start pay_us_zip_codes.zip_start%TYPE;
314 l_zip_end pay_us_zip_codes.zip_end%TYPE;
315 l_st_code pay_us_states.state_code%TYPE;
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
325 from pay_us_city_geocodes
326 where state_code = l_st_code and county_code = l_co_code
327 and city_code like 'U%';
328
329 CURSOR zip_exist_c IS
330 SELECT zc.state_code,
331 zc.county_code,
332 zc.city_code,
333 zc.zip_start,
334 zc.zip_end
335 FROM pay_us_zip_codes zc
336 WHERE zc.state_code = l_st_code
337 AND zc.county_code = l_co_code
338 AND zc.city_code = l_ci_code
339 AND (l_zip_start BETWEEN zc.zip_start AND zc.zip_end
340 OR l_zip_end BETWEEN zc.zip_start AND zc.zip_end
341 OR zc.zip_start BETWEEN l_zip_start AND l_zip_end
342 OR zc.zip_end BETWEEN l_zip_start AND l_zip_end);
343
344 zip_exist_rec zip_exist_c%ROWTYPE;
345
346 BEGIN
347
348 /* force case of all passed-in parameters to match that stored in DB. */
349
350 hr_utility.trace ('County Name: '||p_co_name);
351 hr_utility.trace ('State Name: '||p_st_name);
352
353 l_stateName := initcap(p_st_name);
354 l_countyName := initcap(p_co_name);
355 l_cityName := initcap(p_ci_name);
356
357 hr_utility.trace ('County name after initcap :'||l_countyName);
358 hr_utility.trace ('State name after initcap :'||l_stateName);
359 /* get state, county and city codes. */
360
361 BEGIN
362 select state_code, state_abbrev
363 into l_st_code, l_st_abbrev
364 from pay_us_states
365 where state_name = l_stateName;
366
367 EXCEPTION WHEN no_data_found THEN
368 hr_utility.trace ('Error: Failed to find state codes.');
369 local_error ('HR_7952_ADDR_NO_STATE_CODE', 'create_unkn_city', 1);
370 END;
371
372 hr_utility.trace ('selected state code: ' || l_st_code);
373 hr_utility.trace ('l_countyName: ' || l_countyName);
374
375 BEGIN
376 select county_code
377 into l_co_code
378 from pay_us_counties
379 where state_code = l_st_code
380 and initcap(county_name) = l_countyName;
381
382 EXCEPTION WHEN no_data_found THEN
383 hr_utility.trace ('Error: Failed to find county codes.');
384 local_error ('HR_7953_ADDR_NO_COUNTY_FOUND', 'create_unkn_city', 2);
385 END;
386
387 hr_utility.trace ('selected county code: ' || l_co_code);
388
389 /* see if this city is unknown. */
390
391 if hr_us_ff_udfs.addr_val(l_st_abbrev, l_countyName, l_cityName,
392 p_zi_start,'Y') = '00-000-0000' then
393 /*
394 check for an existing city whose zip codes are wrong or
395 altered, which addr_val would still return '00-000-0000'.
396 */
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;
406 WHEN others THEN l_n_ci_name := 1;
407 END;
408
409 if l_n_ci_name = 0 then
410
411 /* get number of unknown cities previous entered. */
412 select count(city_code)
413 into l_n_ci_code
414 from pay_us_city_geocodes
415 where state_code = l_st_code and county_code = l_co_code
416 and city_code like 'U%';
417
418 hr_utility.trace ('Number of unknown cities '||l_n_ci_code);
419
420 if l_n_ci_code <= 0 then
421 l_ci_code := 'U000';
422 hr_utility.trace ('Initial city code is:'||l_ci_code);
423 else
424 /*
425 use explicit cursor to step through each of the unknown
429
426 city codes to find the latest city code.
427 */
428 for l_city_rec in c_city_code loop
430 if fnd_number.canonical_to_number(substr(l_city_rec.city_code,2,3)) >
431 l_max_ci_code then
432
433 l_max_ci_code :=
434 fnd_number.canonical_to_number(substr(l_city_rec.city_code,2,3));
435 hr_utility.trace ('Max city code is:'||
436 l_city_rec.city_code);
437
438 end if;
439 end loop;
440
441 if l_max_ci_code >= 0 and l_max_ci_code < 9 then
442 l_ci_code := 'U00'||to_char(l_max_ci_code + 1);
443 elsif l_max_ci_code >= 9 and l_max_ci_code < 99 then
444 l_ci_code := 'U0'||to_char(l_max_ci_code + 1);
445 else
446 l_ci_code := 'U'||to_char(l_max_ci_code + 1);
447 end if;
448
449 hr_utility.trace ('Final city code is:'||l_ci_code);
450 end if;
451 p_ci_code := l_ci_code;
452
453 insert into pay_us_city_geocodes
454 (state_code, county_code, city_code)
455 values
456 (l_st_code, l_co_code, l_ci_code);
457 hr_utility.trace ('Inserted a geocode.');
458
459 insert into pay_us_zip_codes
460 (zip_start, zip_end, state_code, county_code, city_code)
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.');
470
471 elsif (l_n_ci_name = 1) then
472 hr_utility.trace ('Error: Given city is already created.');
473 local_error ('HR_7954_ADDR_NOT_UNKNOWN_CITY',
474 'create_unkn_city', 3);
475 else
476 /* a new zip range for an existing city has been entered */
477 l_zip_end := p_zi_end;
478 l_zip_start := p_zi_start;
479
480 OPEN zip_exist_c;
481 LOOP
482 FETCH zip_exist_c INTO zip_exist_rec;
483 EXIT WHEN zip_exist_c%NOTFOUND;
484 IF zip_exist_rec.zip_start < l_zip_start THEN
485 l_zip_start := zip_exist_rec.zip_start;
486 END IF;
487 IF zip_exist_rec.zip_end > l_zip_end THEN
488 l_zip_end := zip_exist_rec.zip_end;
489 END IF;
490
491 DELETE FROM pay_us_zip_codes
492 WHERE zip_start = zip_exist_rec.zip_start
493 AND zip_end = zip_exist_rec.zip_end
494 AND state_code = zip_exist_rec.state_code
495 AND county_code = zip_exist_rec.county_code
496 AND city_code = zip_exist_rec.city_code;
497
498 END LOOP;
499 CLOSE zip_exist_c;
500
501 INSERT INTO pay_us_zip_codes
502 (ZIP_START, ZIP_END, STATE_CODE, COUNTY_CODE, CITY_CODE)
503 VALUES
504 (l_zip_start,l_zip_end,l_st_code,l_co_code,l_ci_code);
505 hr_utility.trace ('Inserted a new zip code for an existing city.');
506
507 p_ci_code := l_ci_code;
508 p_zi_end := l_zip_end;
509 p_zi_start := l_zip_start;
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
519 AND state_code = l_st_code;
520
521 end if;
522 else
523 hr_utility.trace ('Error: Given city is already created.');
524 local_error ('HR_7954_ADDR_NOT_UNKNOWN_CITY', 'create_unkn_city', 4);
525 end if;
526
527 END create_unkn_city;
528 --------------------------------------------------------------------------
529 -- Name --
530 -- Insert_Row --
531 -- Purpose --
532 -- Table handler procedure that supports the insert of a new city via --
533 -- the city form. --
534 -- Notes --
535 -- --
536 -----------------------------------------------------------------------------
537 --
538 PROCEDURE Insert_Row(p_city_code IN OUT NOCOPY VARCHAR2,
539 p_zprowid IN OUT NOCOPY VARCHAR2,
540 p_cirowid IN OUT NOCOPY VARCHAR2,
541 p_gerowid IN OUT NOCOPY VARCHAR2,
542 p_state_code VARCHAR2,
543 p_county_code VARCHAR2,
544 p_state_name VARCHAR2,
548 p_zip_end VARCHAR2,
545 p_county_name VARCHAR2,
546 p_city_name VARCHAR2,
547 p_zip_start VARCHAR2,
549 p_disable VARCHAR2
550 ) is
551
552 --
553 --
554 --
555 l_zip_start pay_us_zip_codes.zip_start%TYPE;
556 l_zip_end pay_us_zip_codes.zip_end%TYPE;
557
558 CURSOR C IS SELECT rowid FROM pay_us_zip_codes
559 WHERE state_code = p_state_code
560 AND county_code= p_county_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;
570 --
571 CURSOR C3 is SELECT rowid from pay_us_city_geocodes
572 WHERE state_code = p_state_code
573 AND county_code= p_county_code
574 AND city_code = p_city_code;
575
576 --
577 BEGIN
578 --
579 /* hr_utility.trace_on('Y','CACITY');*/
580 l_zip_start := p_zip_start;
581 l_zip_end := p_zip_end;
582
583 create_unkn_city (p_city_code,
584 p_state_name,
585 p_county_name,
586 p_city_name,
587 l_zip_start,
588 l_zip_end,
589 p_disable);
590 --
591 OPEN C;
592 FETCH C INTO p_zprowid;
593 IF (C%NOTFOUND) THEN
594 CLOSE C;
595 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
596 hr_utility.set_message_token('PROCEDURE',
597 'pay_us_city_pkg.Insert_Row');
598 hr_utility.set_message_token('STEP','1');
599 hr_utility.raise_error;
600 END if;
601
602 --
603 OPEN C2;
604 FETCH C2 INTO p_cirowid;
605 IF (C2%NOTFOUND) THEN
606 CLOSE C2;
607 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
608 hr_utility.set_message_token('PROCEDURE',
609 'pay_us_city_pkg.Insert_Row');
610 hr_utility.set_message_token('STEP','2');
611 hr_utility.raise_error;
612 END if;
613
614 --
615 OPEN C3;
616 FETCH C3 INTO p_gerowid;
617 IF (C3%NOTFOUND) THEN
618 CLOSE C3;
619 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
620 hr_utility.set_message_token('PROCEDURE',
621 'pay_us_city_pkg.Insert_Row');
622 hr_utility.set_message_token('STEP','3');
623 hr_utility.raise_error;
624 END if;
625
626 /* hr_utility.trace_off; */
627 END Insert_Row;
628
629 -----------------------------------------------------------------------------
630 -- Name --
631 -- Lock_Row --
632 -- Purpose --
633 -- Table handler procedure that supports the update and delete --
634 -- of a city by applying a lock on a city block in City Form. --
635 -- Arguments --
636 -- See below. --
637 -- Notes --
638 -- None. --
639 -----------------------------------------------------------------------------
640 --
641 PROCEDURE Lock_Row( p_zprowid VARCHAR2,
642 p_cirowid VARCHAR2,
643 p_gerowid VARCHAR2,
644 p_state_code VARCHAR2,
645 p_county_code VARCHAR2,
646 p_city_code VARCHAR2,
647 p_state_name VARCHAR2,
648 p_county_name VARCHAR2,
649 p_city_name VARCHAR2,
650 p_zip_start VARCHAR2,
651 p_zip_end VARCHAR2) IS
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;
661 --
662 l_recinfo C%ROWTYPE;
663 l_recinfo2 C2%ROWTYPE;
664 l_recinfo3 C3%ROWTYPE;
665 --
666 BEGIN
667 --
668 OPEN C;
669 FETCH C INTO l_recinfo;
670 if (C%NOTFOUND) then
671 CLOSE C;
672 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
673 hr_utility.set_message_token('PROCEDURE',
674 'pay_us_city_pkg.lock_row');
675 hr_utility.set_message_token('STEP','1');
676 hr_utility.raise_error;
677 end if;
678 --
679 OPEN C2;
680 FETCH C2 INTO l_recinfo2;
681 if (C2%NOTFOUND) then
682 CLOSE C2;
683 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
684 hr_utility.set_message_token('PROCEDURE',
685 'pay_us_city_pkg.lock_row');
686 hr_utility.set_message_token('STEP','2');
687 hr_utility.raise_error;
688 end if;
689 --
690 OPEN C3;
691 FETCH C3 INTO l_recinfo3;
692 if (C3%NOTFOUND) then
693 CLOSE C3;
694 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
695 hr_utility.set_message_token('PROCEDURE',
696 'pay_us_city_pkg.lock_row');
697 hr_utility.set_message_token('STEP','3');
698 hr_utility.raise_error;
699 end if;
700
701 --
702 -- Remove trailing spaces.
706 l_recinfo.zip_start := rtrim(l_recinfo.zip_start);
703 l_recinfo.state_code := rtrim(l_recinfo.state_code);
704 l_recinfo.county_code := rtrim(l_recinfo.county_code);
705 l_recinfo.city_code := rtrim(l_recinfo.city_code);
707 l_recinfo.zip_end := rtrim(l_recinfo.zip_end);
708 --
709 l_recinfo2.state_code := rtrim(l_recinfo2.state_code);
710 l_recinfo2.county_code := rtrim(l_recinfo2.county_code);
711 l_recinfo2.city_code := rtrim(l_recinfo2.city_code);
712 l_recinfo2.city_name := rtrim(l_recinfo2.city_name);
713 --
714 IF ( ( (l_recinfo.zip_start = p_zip_start)
715 OR ( (l_recinfo.zip_start IS NULL)
716 AND (p_zip_start IS NULL)))
717 AND ( (l_recinfo.zip_end = p_zip_end)
718 OR ( (l_recinfo.zip_end IS NULL)
719 AND (p_zip_end IS NULL)))
720 AND ( (l_recinfo2.city_name = p_city_name)
721 OR ( (l_recinfo2.city_name IS NULL)
722 AND (p_city_name IS NULL)))
723 ) then
724 return;
725 else
726 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
727 APP_EXCEPTION.RAISE_EXCEPTION;
728 end if;
729 --
730 END Lock_Row;
731 --
732 -----------------------------------------------------------------------------
733 -- Name --
734 -- Update_Row --
735 -- Purpose --
736 -- Table handler procedure that supports the update of a city via --
737 -- the city form. --
738 -- Arguments --
739 -- See below. --
740 -- Notes --
741 -- None. --
742 -----------------------------------------------------------------------------
743 --
744 PROCEDURE Update_Row(p_zprowid VARCHAR2,
745 p_zip_start VARCHAR2,
746 p_zip_end VARCHAR2,
747 p_state_code VARCHAR2,
748 p_county_code VARCHAR2,
749 p_city_code VARCHAR2,
750 p_city_name VARCHAR2,
751 p_disable VARCHAR2) IS
752 BEGIN
753 --
754 -- The appropriate tables need to be locked during the updating process.
755 --
756 --
757 UPDATE pay_us_zip_codes
758 SET zip_start = p_zip_start,
759 zip_end = p_zip_end
760 WHERE rowid = p_zprowid;
761 --
762 if (SQL%NOTFOUND) then
763 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
764 hr_utility.set_message_token('PROCEDURE',
765 'pay_us_new_cities_pkg.update_row');
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
775 AND city_name = p_city_name;
776 --
777 if (SQL%NOTFOUND) then
778 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
779 hr_utility.set_message_token('PROCEDURE',
780 'pay_us_new_cities_pkg.update_row');
781 hr_utility.set_message_token('STEP','1');
782 hr_utility.raise_error;
783 end if;
784 --
785 END Update_Row;
786 --
787 -----------------------------------------------------------------------------
788 -- Name --
789 -- Delete_Row --
790 -- Purpose --
791 -- Table handler procedure that supports the delete of a city via --
792 -- the create city form. --
793 -- Arguments --
794 -- See below. --
795 -- Notes --
796 -- --
797 -----------------------------------------------------------------------------
798 --
799 PROCEDURE Delete_Row(p_zprowid VARCHAR2,
800 p_cirowid VARCHAR2,
801 p_gerowid VARCHAR2) IS
802 --
803
804 zip_counter NUMBER;
805
806 BEGIN
807 --
808 --
809 --
810 -- Deleting from the pay_us_zip_codes table.
811 --
812 DELETE FROM pay_us_zip_codes
813 WHERE rowid = p_zprowid;
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;
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 --
831 -- Deleting from the pay_us_city_geocodes
832 --
833 DELETE FROM pay_us_city_geocodes
834 WHERE rowid = p_gerowid;
835
836 END IF;
837 --
838 if (SQL%NOTFOUND) then
839 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
843 hr_utility.raise_error;
840 hr_utility.set_message_token('PROCEDURE',
841 'pay_us_city_pkg.delete_row');
842 hr_utility.set_message_token('STEP','3');
844 end if;
845 END Delete_Row;
846 --
847 -- The Next procedure is used for checking and validating data
848 --
849 ------------------------------------------------------------------------------
850 -- Name
851 -- chk_city_in_addr
852 -- Purpose
853 -- To check a given city exists in the per_addresses table
854 -- It would not allow the city to be deleted if it is referenced in
855 -- the per_addresses table. Validation check to prevent deletion
856 -- if city is referenced.
857 -- Notes
858 -- This only confirms the existence of a city in the per_addresses
859 -- table if and only if the city within an state and county
860 -- matches to that of the per_addresses table.
861 ------------------------------------------------------------------------------
862 PROCEDURE chk_city_in_addr(p_state_abbrev VARCHAR2,
863 p_county_name VARCHAR2,
864 p_city_name VARCHAR2) IS
865 --
866 l_prov_abbrev varchar2(5);
867
868 /*Modified query for performance enhancement Bug No. 3346024 */
869 CURSOR C1 is SELECT 'x'
870 FROM dual
871 where exists(
872 SELECT region_1,town_or_city
873 FROM per_addresses
874 WHERE region_1 = l_prov_abbrev
875 AND town_or_city = p_city_name
876 AND ROWNUM < 2);
877
878 /*Modified query for performance enhancement Bug No. 3346024 */
879 CURSOR C2 is SELECT 'x'
880 FROM dual
881 where exists(
882 SELECT region_1,region_2,town_or_city
883 -- Note that in per_addresses table town_or_city, region 2 and region 1
884 -- are refered to as
885 -- city name, state abbreviation and county name respectively.
886 FROM per_addresses
887 WHERE region_2 = p_state_abbrev
888 AND region_1 = p_county_name
889 AND town_or_city = p_city_name
890 AND ROWNUM < 2);
891
892 l_recinfo1 C1%ROWTYPE;
893 l_recinfo2 C2%ROWTYPE;
894 --
895 BEGIN
896
897 /* If we are dealing with a Canadian city then region_2 is ignored
898 because it is not populated */
899
900 IF p_state_abbrev = 'CN' THEN
901
902 SELECT county_abbrev
903 INTO l_prov_abbrev
904 FROM pay_us_counties
905 WHERE county_name = p_county_name
906 AND state_code = '70';
907
908 OPEN C1;
909 FETCH C1 INTO l_recinfo1;
910 IF (C1%FOUND) THEN
911 /* rosie monge 18-mar-03 2844658 */
912 /*
913 hr_utility.set_message (801,'HR_6153_all_PROCEDURE_FAIL');
914 hr_utility.set_message_token ('PROCEDURE',
915 'pay_us_chk_addr_pkg.chk_city_in_addr');
916 hr_utility.set_message_token ('STEP', '1');
917 */
918 hr_utility.set_message(801,'PAY_74153_CITY_CANNOT_DISABLE');
919 hr_utility.raise_error;
920 END IF;
921 CLOSE C1;
922
923 ELSE
924 OPEN C2;
925 FETCH C2 INTO l_recinfo2;
926 IF (C2%FOUND) THEN
927 /* rmonge fix for bug 2844658 */
928 /*
929 hr_utility.set_message (801,'HR_6153_all_PROCEDURE_FAIL');
930 hr_utility.set_message_token ('PROCEDURE',
931 'pay_us_chk_addr_pkg.chk_city_in_addr');
932 hr_utility.set_message_token ('STEP', '1');
933 */
934 hr_utility.set_message(801,'PAY_74153_CITY_CANNOT_DISABLE');
935 hr_utility.raise_error;
936 END IF;
937 CLOSE C2;
938
939 END IF;
940
941 RETURN;
942
943 END chk_city_in_addr;
944
945 END PAY_US_CITY_PKG;
[Home] [Help]