1 Package Body per_kad_bus as
2 /* $Header: pekadrhi.pkb 115.6 2002/12/06 11:27:37 pkakar ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_kad_bus.'; -- Global package name
9 --
10 -- This variable indicates if payroll is installed under US legislation.
11 -- The function that returns this value is called in the validation procedures.
12 -- To prevent the identical sql from executing multiple times, this variable
13 -- is checked when the function is called and if not null, the sql is bypassed.
14 --
15 g_us_payroll varchar2(1) default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |---------------------------< chk_person_id >----------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 -- Description:
22 -- Validates that a person id exists in table per_people_f.
23 --
24 -- Pre-conditions:
25 -- None.
26 --
27 -- In Arguments:
28 -- p_person_id
29 --
30 -- Post Success:
31 -- If a row does exist in per_people_f for the given person id then
32 -- processing continues.
33 --
34 -- Post Failure:
35 -- If a row does not exist in per_people_f for the given person id then
36 -- an application error will be raised and processing is terminated.
37 --
38 -- Access Status:
39 -- Internal Table Handler Use Only.
40 --
41 -- {End Of Comments}
42 -- ----------------------------------------------------------------------------
43 procedure chk_person_id
44 (p_person_id in per_addresses.person_id%TYPE) is
45 --
46 l_exists varchar2(1);
47 l_proc varchar2(72) := g_package||'chk_person_id';
48 --
49 cursor csr_valid_pers is
50 select null
51 from per_people_f ppf
52 where ppf.person_id = p_person_id;
53 --
54 begin
55 hr_utility.set_location('Entering:'|| l_proc, 1);
56 --
57 -- Check mandatory parameters have been set
58 --
59 hr_api.mandatory_arg_error
60 (p_api_name => l_proc
61 ,p_argument => 'person_id'
62 ,p_argument_value => p_person_id
63 );
64 hr_utility.set_location(l_proc, 2);
65 --
66 -- Check that the Person ID is linked to a
67 -- valid person on PER_PEOPLE_F
68 --
69 open csr_valid_pers;
70 fetch csr_valid_pers into l_exists;
71 if csr_valid_pers%notfound then
72 close csr_valid_pers;
73 hr_utility.set_message(801, 'HR_7298_ADD_PERSON_INVALID');
74 hr_utility.raise_error;
75 end if;
76 close csr_valid_pers;
77 hr_utility.set_location(' Leaving:'|| l_proc, 3);
78 end chk_person_id;
79 --
80 -- ---------------------------------------------------------------------------
81 -- |-------------------------< chk_address_type >---------------------------|
82 -- ---------------------------------------------------------------------------
83 --
84 -- Description:
85 -- Validates that an address type exists in table hr_lookups
86 -- where lookup_type is 'ADDRESS_TYPE'
87 -- and enabled_flag is 'Y'
88 -- and effective_date is between the active dates (if they are not null).
89 --
90 -- Pre-conditions:
91 -- Effective_date must be valid.
92 --
93 -- In Arguments:
94 -- p_address_id
95 -- p_date_from
96 -- p_address_type
97 -- p_effective_date
98 -- p_object_version_number
99 --
100 -- Post Success:
101 -- If a row does exist in hr_lookups for the given address code then
102 -- processing continues.
103 --
104 -- Post Failure:
105 -- If a row does not exist in hr_lookups for the given address code then
106 -- an application error will be raised and processing is terminated.
107 --
108 -- Access Status:
109 -- Internal Table Handler Use Only.
110 --
111 -- {End Of Comments}
112 -- ----------------------------------------------------------------------------
113 procedure chk_address_type
114 (p_address_id in per_addresses.address_id%TYPE
115 ,p_address_type in per_addresses.address_type%TYPE
116 ,p_date_from in per_addresses.date_from%TYPE
117 ,p_effective_date in date
118 ,p_object_version_number in per_addresses.object_version_number%TYPE) is
119 --
120 l_exists varchar2(1);
121 l_proc varchar2(72) := g_package||'chk_address_type';
122 l_api_updating boolean;
123 --
124 begin
125 hr_utility.set_location('Entering:'|| l_proc, 1);
126 --
127 -- Check mandatory parameters have been set
128 --
129 hr_api.mandatory_arg_error
130 (p_api_name => l_proc
131 ,p_argument => 'date_from'
132 ,p_argument_value => p_date_from
133 );
134 hr_api.mandatory_arg_error
135 (p_api_name => l_proc
136 ,p_argument => 'effective_date'
137 ,p_argument_value => p_effective_date
138 );
139 --
140 -- Only proceed with validation if :
141 -- a) The current g_old_rec is current and
142 -- b) The value for address type has changed
143 --
144 l_api_updating := per_kad_shd.api_updating
145 (p_address_id => p_address_id
146 ,p_object_version_number => p_object_version_number);
147 --
148 if ((l_api_updating and
149 nvl(per_kad_shd.g_old_rec.address_type, hr_api.g_varchar2) <>
150 nvl(p_address_type, hr_api.g_varchar2)) or
151 (NOT l_api_updating)) then
152 hr_utility.set_location(l_proc, 2);
153 --
154 -- Checks that the value for address_type is
155 -- valid and exists on hr_lookups within the
156 -- specified date range
157 --
158 if p_address_type is not null then
159 --
160 if hr_api.not_exists_in_hr_lookups
161 (p_effective_date => p_effective_date
162 ,p_lookup_type => 'ADDRESS_TYPE'
163 ,p_lookup_code => p_address_type
164 ) then
165 --
166 -- Error: Invalid address type.
167 hr_utility.set_message(801, 'HR_7299_ADD_TYPE_INVALID');
168 hr_utility.raise_error;
169 end if;
170 end if;
171 end if;
172 --
173 hr_utility.set_location(' Leaving:'|| l_proc, 4);
174 end chk_address_type;
175 --
176 -- ---------------------------------------------------------------------------
177 -- |---------------------------< chk_country >------------------------------|
178 -- ---------------------------------------------------------------------------
179 --
180 -- Description:
181 -- Validates that a country code exists in table fnd_territories
182 -- for US, GB and GENERIC address styles.
183 --
184 -- Pre-conditions:
185 -- Style (p_style) must be valid.
186 --
187 -- In Arguments:
188 -- p_country
189 -- p_address_id
190 -- p_object_version_number
191 --
192 -- Post Success:
193 -- If a row does exist in fnd_territories for the given country code then
194 -- processing continues.
195 --
196 -- Post Failure:
197 -- If a row does not exist in fnd_territories for the given country code then
198 -- an application error will be raised and processing is terminated.
199 --
200 -- Access Status:
201 -- Internal Table Handler Use Only.
202 --
203 -- {End Of Comments}
204 -- ----------------------------------------------------------------------------
205 procedure chk_country
206 (p_address_id in per_addresses.address_id%TYPE
207 ,p_style in per_addresses.style%TYPE
208 ,p_country in per_addresses.country%TYPE
209 ,p_object_version_number in per_addresses.object_version_number%TYPE)
210 is
211 --
212 l_exists varchar2(1);
213 l_proc varchar2(72) := g_package||'chk_country';
214 l_api_updating boolean;
215 --
216 cursor csr_valid_ctry is
217 select null
218 from fnd_territories ft
219 where ft.territory_code = p_country;
220 --
221 begin
222 hr_utility.set_location('Entering:'|| l_proc, 1);
223 --
224 -- Only proceed with validation if :
225 -- a) The current g_old_rec is current and
226 -- b) The value for country has changed
227 --
228 l_api_updating := per_kad_shd.api_updating
229 (p_address_id => p_address_id
230 ,p_object_version_number => p_object_version_number);
231 --
232 if ((l_api_updating and
233 nvl(per_kad_shd.g_old_rec.country, hr_api.g_varchar2) <>
234 nvl(p_country, hr_api.g_varchar2)) or
235 (NOT l_api_updating)) then
236 hr_utility.set_location(l_proc, 2);
237 --
238 -- Checks that value for country is a valid
239 -- country on fnd_territories
240 --
241 if p_style = 'US' or
242 p_style = 'GB' then
243 /* (p_style = 'JP' and p_country is not null) then */
244 open csr_valid_ctry;
245 fetch csr_valid_ctry into l_exists;
246 if csr_valid_ctry%notfound then
247 close csr_valid_ctry;
248 hr_utility.set_message(801, 'HR_7300_ADD_COUNTRY_INVALID');
249 hr_utility.raise_error;
250 end if;
251 close csr_valid_ctry;
252 end if;
253 end if;
254 --
255 hr_utility.set_location(' Leaving:'|| l_proc, 3);
256 end chk_country;
257 --
258 -- ---------------------------------------------------------------------------
259 -- |------------------------< chk_date_to >---------------------------------|
260 -- ---------------------------------------------------------------------------
261 --
262 -- Description:
263 -- Validates that date to (may be null) is greater than or equal to date
264 -- from.
265 --
266 -- Pre-conditions:
267 -- Format of p_date_from and p_date_to must be correct.
268 --
269 -- In Arguments:
270 -- p_address_id
271 -- p_date_from
272 -- p_date_to
273 -- p_object_version_number
274 --
275 -- Post Success:
276 -- If a given date to is greater than or equal to a given date from then
277 -- processing continues.
278 --
279 -- Post Failure:
280 -- If a given date to is not greater than or equal to a given date from then
281 -- an application error will be raised and processing is terminated.
282 --
283 -- Access status:
284 -- Internal Table Handler Use Only.
285 --
286 -- {End Of Comments}
287 -- ----------------------------------------------------------------------------
288 procedure chk_date_to
289 (p_address_id in per_addresses.address_id%TYPE
290 ,p_date_from in per_addresses.date_from%TYPE
291 ,p_date_to in per_addresses.date_to%TYPE
292 ,p_object_version_number in per_addresses.object_version_number%TYPE)
293 is
294 --
295 l_exists varchar2(1);
296 l_proc varchar2(72) := g_package||'chk_date_to';
297 l_date_to date;
298 l_api_updating boolean;
299 --
300 begin
301 hr_utility.set_location('Entering:'|| l_proc, 1);
302 --
303 -- Check mandatory parameters have been set
304 --
305 hr_api.mandatory_arg_error
306 (p_api_name => l_proc
307 ,p_argument => 'date_from'
308 ,p_argument_value => p_date_from
309 );
310 --
311 -- Only proceed with validation if :
312 -- a) The current g_old_rec is current and
313 -- b) The value for date to has changed
314 --
315 l_api_updating := per_kad_shd.api_updating
316 (p_address_id => p_address_id
317 ,p_object_version_number => p_object_version_number);
318 --
319 if ((l_api_updating and
320 nvl(per_kad_shd.g_old_rec.date_to, hr_api.g_eot) <>
321 nvl(p_date_to, hr_api.g_eot)) or
322 (NOT l_api_updating)) then
323 --
324 hr_utility.set_location(l_proc, 2);
325 --
326 -- Checks that the value for date_to is greater than or
327 -- equal to the corresponding value for date_from for the
328 -- same record
329 --
330 if nvl(p_date_to, hr_api.g_eot) < p_date_from then
331 hr_utility.set_message(801, 'HR_7301_ADD_DATE_TO_LATER');
332 hr_utility.raise_error;
333 end if;
334 --
335 end if;
336 --
337 hr_utility.set_location(' Leaving:'|| l_proc, 3);
338 end chk_date_to;
339 --
340 -- ---------------------------------------------------------------------------
341 -- |------------------------< chk_date_from >-------------------------------|
342 -- ---------------------------------------------------------------------------
343 --
344 -- Description:
345 -- Validates that date from is less than or equal to date to (may be null).
346 --
347 -- Pre-conditions:
348 -- Format of p_date_from and p_date_to must be correct.
349 --
350 -- In Arguments:
351 -- p_address_id
352 -- p_date_from
353 -- p_date_to
354 -- p_object_version_number
355 --
356 -- Post Success:
357 -- If a given date from is less than or equal to a given date to then
358 -- processing continues.
359 --
360 -- Post Failure:
361 -- If a given date from is not less than or equal to a given date to then
362 -- an application error will be raised and processing is terminated.
363 --
364 -- Access Status:
365 -- Internal Table Handler Use Only.
366 --
367 -- {End Of Comments}
368 -- ----------------------------------------------------------------------------
369 procedure chk_date_from
370 (p_address_id in per_addresses.address_id%TYPE
371 ,p_date_from in per_addresses.date_from%TYPE
372 ,p_date_to in per_addresses.date_to%TYPE
373 ,p_object_version_number in per_addresses.object_version_number%TYPE)
374 is
375 --
376 l_exists varchar2(1);
377 l_proc varchar2(72) := g_package||'chk_date_from';
378 l_api_updating boolean;
379 --
380 begin
381 hr_utility.set_location('Entering:'|| l_proc, 1);
382 --
383 -- Check mandatory parameters have been set
384 --
385 hr_api.mandatory_arg_error
386 (p_api_name => l_proc
387 ,p_argument => 'date_from'
388 ,p_argument_value => p_date_from
389 );
390 --
391 -- Only proceed with validation if :
392 -- a) The current g_old_rec is current and
393 -- b) The date_from value has changed
394 --
395 l_api_updating := per_kad_shd.api_updating
396 (p_address_id => p_address_id
397 ,p_object_version_number => p_object_version_number);
398 --
399 if ((l_api_updating and per_kad_shd.g_old_rec.date_from <> p_date_from) or
400 (NOT l_api_updating)) then
401 hr_utility.set_location(l_proc, 2);
402 --
403 -- Check that the date_from values is less than
404 -- or equal to the date_to value for the current
405 -- record
406 --
407 if p_date_from > nvl(p_date_to, hr_api.g_eot) then
408 hr_utility.set_message(801, 'HR_7303_ADD_DATE_FROM_EARLIER');
409 hr_utility.raise_error;
410 end if;
411 --
412 end if;
413 --
414 hr_utility.set_location(' Leaving:'|| l_proc, 3);
415 end chk_date_from;
416 --
417 -- ---------------------------------------------------------------------------
418 -- |-----------------------------< chk_style >------------------------------|
419 -- ---------------------------------------------------------------------------
420 --
421 -- Description:
422 -- Validates:
423 -- - only 'GB', 'US' , 'JP' and 'GENERIC' address styles are entered.
424 -- - a flex structure exists for a given style.
425 -- - the columns that should not be populated if address style is 'GB',
426 -- 'US' or 'GENERIC'.
427 --
428 -- Pre-conditions:
429 -- None
430 --
431 -- In Arguments:
432 -- p_style
433 --
434 -- Post Success:
435 -- Processing continues if:
436 -- - the address style is 'GB', 'US' and 'GENERIC'.
437 -- - a flex structure does exist in fnd_descr_flex_contexts for the given
438 -- territory code.
439 --
440 -- Post Failure:
441 -- An application error is raised and processing terminates if:
442 -- - the style entered is not 'GB', 'US' and 'GENERIC'.
443 -- - a flex structure does not exist in fnd_descr_flex_contexts for the
444 -- given territory code.
445 --
446 -- Access Status:
447 -- Internal Table Handler Use Only.
448 --
449 -- {End Of Comments}
450 -- ----------------------------------------------------------------------------
451 procedure chk_style
452 (p_style in varchar2)
453 is
454 --
455 l_exists varchar2(1);
456 l_token varchar2(20);
457 l_error exception;
458 l_proc varchar2(72) := g_package||'chk_style';
459 --
460 --
461 -- 70.2 change c start.
462 --
463 cursor csr_valid_flex_struc is
464 select null
465 from fnd_descr_flex_contexts
466 where descriptive_flexfield_name = 'Address Structure'
467 and descriptive_flex_context_code = p_style
468 and enabled_flag = 'Y'
469 and application_id = 800;
470 --
471 -- 70.2 change c end.
472 --
473 --
474 begin
475 hr_utility.set_location('Entering:'|| l_proc, 1);
476 --
477 -- Check mandatory parameters have been set
478 --
479 hr_api.mandatory_arg_error
480 (p_api_name => l_proc
481 ,p_argument => 'style'
482 ,p_argument_value => p_style
483 );
484 hr_utility.set_location(l_proc, 2);
485 --
486 -- Checks that the flex structure for the style
487 -- selected exists in fnd_descr_flex_contents
488 --
489 open csr_valid_flex_struc;
490 fetch csr_valid_flex_struc into l_exists;
491 if csr_valid_flex_struc%notfound then
492 close csr_valid_flex_struc;
493 hr_utility.set_message(801, 'HR_7304_ADD_NO_FORMAT');
494 hr_utility.raise_error;
495 end if;
496 close csr_valid_flex_struc;
497 hr_utility.set_location(l_proc, 3);
498 --
499 -- Only the style 'GB','US','JP' and 'GENERIC' are accepted by the API
500 --
501 if p_style <> 'GB'
502 and p_style <> 'US'
503 and p_style <> 'JP'
504 and p_style <> 'GENERIC' then
505 hr_utility.set_message(801, 'HR_7297_API_ARG_ONLY');
506 hr_utility.set_message_token('ARG_NAME', 'ADDRESS_STYLE');
507 hr_utility.set_message_token('ARG_ONLY', 'GB, US, JP or GENERIC');
508 hr_utility.raise_error;
509 end if;
510 --
511 hr_utility.set_location(' Leaving:'|| l_proc, 4);
512 end chk_style;
513 --
514 -- ---------------------------------------------------------------------------
515 -- |----------------------< chk_style_null_attr >---------------------------|
516 -- ---------------------------------------------------------------------------
517 --
518 -- Description:
519 -- Validates the columns that should not be populated if address style is
520 -- 'GB', 'US' or 'GENERIC'.
521 --
522 -- Pre-conditions:
523 -- Style (p_style) must be valid.
524 --
525 -- In Arguments:
526 -- p_style
527 -- p_region_2
528 -- p_region_3
529 -- p_telephone_number_3
530 --
531 -- Post Success:
532 -- If the style structure meets the 'GB', 'US' and 'GENERIC'
533 -- requirements (in terms of column usage) then processing continues.
534 --
535 -- Post Failure:
536 -- If the style structure does not meet the 'GB', 'US' and 'GENERIC'
537 -- requirements (in terms of column usage) then an application error is
538 -- raised and processing terminates.
539 --
540 -- Access Status:
541 -- Internal Table Handler Use Only.
542 --
543 -- {End Of Comments}
544 -- ----------------------------------------------------------------------------
545 -- 09/12/97 Change Begins
546 procedure chk_style_null_attr
547 (p_address_id in number
548 ,p_object_version_number in number
549 ,p_style in varchar2
550 ,p_region_2 in varchar2
551 ,p_region_3 in varchar2
552 ,p_telephone_number_3 in varchar2
553 )
554
555 is
556 --
557 l_token varchar2(20);
558 l_error exception;
559 l_api_updating boolean;
560 l_proc varchar2(72) := g_package||'chk_style_null_attr';
561 --
562 --
563 begin
564 hr_utility.set_location('Entering:'|| l_proc, 1);
565 --
566 -- Check 'GB' address style
567 --
568 if p_style = 'GB' then
569 if p_region_2 is not null then
570 l_token := 'region_2';
571 raise l_error;
572 elsif p_region_3 is not null then
573 l_token := 'region_3';
574 raise l_error;
575 elsif p_telephone_number_3 is not null then
576 l_token := 'telephone_number_3';
577 raise l_error;
578 end if;
579 --
580 hr_utility.set_location(l_proc, 2);
581 --
582 -- Check 'US' address style
583 --
584 elsif p_style = 'US' then
585 --
586 -- Check if region 3 is set but is unchanged on update
587 --
588 l_api_updating := per_add_shd.api_updating
589 (p_address_id => p_address_id
590 ,p_object_version_number => p_object_version_number
591 );
592 --
593 if p_region_3 is not null
594 and nvl(per_add_shd.g_old_rec.region_3, hr_api.g_varchar2)
595 = nvl(p_region_3, hr_api.g_varchar2)
596 then
597 --
598 null;
599 --
600 elsif p_region_3 is not null then
601 --
602 l_token := 'region_3';
603 raise l_error;
604 --
605 end if;
606 --
607 -- Check if telephone number 3 is set but is unchanged on update
608 --
609 if p_telephone_number_3 is not null and
610 nvl(per_add_shd.g_old_rec.telephone_number_3, hr_api.g_varchar2)
611 = nvl(p_telephone_number_3, hr_api.g_varchar2)
612 then
613 --
614 null;
615 --
616 elsif p_telephone_number_3 is not null then
617 --
618 l_token := 'telephone_number_3';
619 raise l_error;
620 --
621 end if;
622 --
623 hr_utility.set_location(l_proc, 3);
624 --
625 -- Check 'GENERIC' address style
626 --
627 elsif p_style = 'GENERIC' then
628 if p_telephone_number_3 is not null then
629 l_token := 'telephone_number_3';
630 raise l_error;
631 end if;
632 end if;
633 --
634 exception
635 when l_error then
636 hr_utility.set_message(801, 'HR_7324_ADD_ADD_ATTRIBUTE_NULL');
637 hr_utility.set_message_token('ARGUMENT', l_token);
638 hr_utility.raise_error;
639 when others then
640 raise;
641 --
642 -- 09/12/97 Change Ends
643 hr_utility.set_location(' Leaving:'|| l_proc, 4);
644
645 end chk_style_null_attr;
646 --
647 -- ---------------------------------------------------------------------------
648 -- |------------------------< chk_address_line1 >----------------------------|
649 -- ---------------------------------------------------------------------------
650 --
651 -- Description:
652 -- Validates that address line 1 is not null from US and GB styles.
653 --
654 -- Pre-conditions:
655 -- Style (p_style) must be valid.
656 --
657 -- In Arguments:
658 -- p_address_id
659 -- p_style
660 -- p_address_line1
661 -- p_object_version_number
662 --
663 -- Post Success:
664 -- If address style is 'US' or 'GB' and address line 1 is not null,
665 -- processing continues.
666 --
667 -- Post Failure:
668 -- If address style is 'US' or 'GB' and address line 1 is null, an
669 -- application error is raised and processing terminates.
670 --
671 -- Access Status:
672 -- Internal Table Handler Use Only.
673 --
674 -- {End Of Comments}
675 -- ----------------------------------------------------------------------------
676 procedure chk_address_line1
677 (p_address_id in per_addresses.address_id%TYPE
678 ,p_style in per_addresses.style%TYPE
679 ,p_address_line1 in per_addresses.region_2%TYPE
680 ,p_object_version_number in per_addresses.object_version_number%TYPE)
681 is
682 --
683 l_exists varchar2(1);
684 l_proc varchar2(72) := g_package||'chk_address_line1';
685 l_api_updating boolean;
686
687 begin
688 hr_utility.set_location('Entering:'|| l_proc, 1);
689 --
690 -- Check mandatory parameters have been set
691 --
692 hr_api.mandatory_arg_error
693 (p_api_name => l_proc
694 ,p_argument => 'style'
695 ,p_argument_value => p_style
696 );
697 --
698 -- Only proceed with validation if :
699 -- a) The current g_old_rec is current and
700 -- b) The value for address_line1 has changed
701 --
702 l_api_updating := per_kad_shd.api_updating
703 (p_address_id => p_address_id
704 ,p_object_version_number => p_object_version_number);
705 --
706 if ((l_api_updating and
707 nvl(per_kad_shd.g_old_rec.address_line1, hr_api.g_varchar2) <>
708 nvl(p_address_line1, hr_api.g_varchar2)) or
709 (NOT l_api_updating)) then
710 hr_utility.set_location(l_proc, 2);
711 --
712 -- Check that value for address_line1 is not null for 'US' and 'GB'
713 -- style.
714 --
715 if p_style = 'GB' or
716 p_style = 'US' then
717 --
718 hr_utility.set_location(l_proc, 3);
719 --
720 if p_address_line1 is null then
721 --
722 hr_utility.set_message(801, 'HR_51233_ADD_ADD_LINE1_REQ');
723 hr_utility.raise_error;
724 end if;
725 --
726 end if;
727 --
728 end if;
729 hr_utility.set_location(' Leaving:'|| l_proc, 5);
730 end chk_address_line1;
731 --
732 -- ----------------------------------------------------------------------------
733 -- |------------------------< chk_half_kana >---------------------------------|
734 -- ----------------------------------------------------------------------------
735 /* procedure chk_half_kana(p_string in varchar2) is
736
737 l_strlen number := length(p_string);
738 l_ch varchar2(2);
739 l_correct BOOLEAN := TRUE;
740 i number := 1;
741 l_proc varchar2(72) := g_package||'chk_half_kana';
742
743 begin
744 -- make sure that all the characters are half kana kana
745
746 hr_utility.set_location('Entering:'|| l_proc, 1);
747
748 while i <= l_strlen and l_correct loop
749 l_ch := substr(p_string, i, 1);
750 if l_ch between ' ' and '~' then
751 NULL;
752 else
753 l_correct := FALSE;
754 end if;
755 i := i + 1;
756 end loop;
757
758 hr_utility.set_location(l_proc, 2);
759
760 if not l_correct then
761 hr_utility.set_message(801, 'HR_51692_ADD_INVALID_KANA');
762 hr_utility.raise_error;
763 end if;
764 hr_utility.set_location(' Leaving:'|| l_proc, 3);
765 end chk_half_kana;
766 --
767 -- ----------------------------------------------------------------------------
768 -- |------------------------<chk_address1_towncity_comb >--------------------|
769 -- ----------------------------------------------------------------------------
770 --
771 procedure chk_address1_towncity_comb(
772 p_address_id in per_addresses.address_id%TYPE,
773 p_object_version_number in per_addresses.object_version_number%TYPE,
774 p_town_or_city in out nocopy per_addresses.town_or_city%type,
775 p_address_line1 in out nocopy per_addresses.address_line1%type,
776 p_region_1 in out nocopy per_addresses.region_1%type) is
777
778 -- p_town_or_city ===> district_code
779 -- p_address_line1 ===> address_line1
780 -- p_region_1 ===> address_line1_kana
781
782 cursor c1 is select * from per_jp_address_lookups
783 where district_code = p_town_or_city;
784 cursor c2 is select * from per_jp_address_lookups
785 where address_line1 = p_address_line1;
786
787 jp_address_rec per_jp_address_lookups%rowtype;
788 l_api_updating boolean;
789 l_proc varchar2(72) := g_package||'chk_address1_towncity_comb';
790
791 begin
792 hr_utility.set_location('Entering:'|| l_proc, 1);
793
794 -- Only proceed with validation if :
795 -- a) The current g_old_rec is current and
796 -- b) The value for town_or_city, address_line1, or region_1 have changed
797 --
798 l_api_updating := per_kad_shd_t.api_updating
799 (p_address_id => p_address_id
800 ,p_object_version_number => p_object_version_number);
801
802 if ((l_api_updating and
803 (nvl(per_kad_shd_t.g_old_rec.town_or_city, hr_api.g_varchar2) <>
804 nvl(p_town_or_city, hr_api.g_varchar2) or
805 nvl(per_kad_shd_t.g_old_rec.address_line1, hr_api.g_varchar2) <>
806 nvl(p_address_line1, hr_api.g_varchar2) or
807 nvl(per_kad_shd_t.g_old_rec.region_1, hr_api.g_varchar2) <>
808 nvl(p_region_1, hr_api.g_varchar2)
809 )) or (NOT l_api_updating)) then
810
811 hr_utility.set_location(l_proc, 2);
812
813 if p_town_or_city is not NULL then
814 hr_utility.set_location(l_proc, 3);
815 open c1;
816 fetch c1 into jp_address_rec;
817 if c1%notfound then
818 hr_utility.set_message(801, 'HR_51693_ADD_INVALID_DISTCODE');
819 hr_utility.raise_error;
820 end if;
821 close c1;
822
823 if p_address_line1 is not null and
824 p_address_line1 <> jp_address_rec.address_line1 then
825
826 hr_utility.set_message(801, 'HR_51694_ADD_INVALID_ADD_LINE1');
827 hr_utility.raise_error;
828 end if;
829 p_address_line1 := jp_address_rec.address_line1;
830
831 elsif p_address_line1 is not NULL then
832 hr_utility.set_location(l_proc, 4);
833 open c2;
834 fetch c2 into jp_address_rec;
835 if c2%notfound then
836 hr_utility.set_message(801, 'HR_51694_ADD_INVALID_ADD_LINE1');
837 hr_utility.raise_error;
838 end if;
839 close c2;
840 p_town_or_city := jp_address_rec.district_code;
841
842 else
843 hr_utility.set_message(801, 'HR_51695_ADD_DIST_ADD1_NULL');
844 hr_utility.raise_error;
845 end if;
846
847 if p_region_1 is not null and p_region_1 <>
848 jp_address_rec.address_line1_kana then
849 hr_utility.set_message(801, 'HR_51696_ADD_INVALID_ADD1_KANA');
850 hr_utility.raise_error;
851 end if;
852 p_region_1 := jp_address_rec.address_line1_kana;
853 end if;
854 hr_utility.set_location('Leaving:'|| l_proc, 5);
855 end chk_address1_towncity_comb;
856 --
857 -- ----------------------------------------------------------------------------
858 -- |------------------------<chk_address2_region2_comb >---------------------|
859 -- ----------------------------------------------------------------------------
860 --
861 procedure chk_address2_region2_comb(
862 p_address_id in per_addresses.address_id%TYPE,
863 p_object_version_number in per_addresses.object_version_number%TYPE,
864 p_address_line2 in per_addresses.address_line2%type,
865 p_region_2 in per_addresses.region_2%type) is
866
867 -- p_address_line2 ===> address_line2
868 -- p_region_2 ===> address_line2_kana
869
870 l_api_updating boolean;
871 l_proc varchar2(72) := g_package||'chk_address2_region2_comb';
872
873 begin
874 hr_utility.set_location('Entering:'|| l_proc, 1);
875
876 -- Only proceed with validation if :
877 -- a) The current g_old_rec is current and
878 -- b) The value for address_line2, or region_2 have changed
879 --
880 l_api_updating := per_kad_shd_t.api_updating
881 (p_address_id => p_address_id
882 ,p_object_version_number => p_object_version_number);
883
884 if ((l_api_updating and
885 (nvl(per_kad_shd_t.g_old_rec.address_line2, hr_api.g_varchar2) <>
886 nvl(p_address_line2, hr_api.g_varchar2) or
887 nvl(per_kad_shd_t.g_old_rec.region_2, hr_api.g_varchar2) <>
888 nvl(p_region_2, hr_api.g_varchar2)
889 )) or (NOT l_api_updating)) then
890
891 hr_utility.set_location(l_proc, 2);
892
893 if p_address_line2 is NULL and p_region_2 is not NULL then
894 hr_utility.set_message(801, 'HR_51697_ADD_REGION2_NOT_NULL');
895 hr_utility.raise_error;
896 end if;
897 chk_half_kana(p_region_2);
898 end if;
899
900 hr_utility.set_location('Leaving:'|| l_proc, 3);
901 end chk_address2_region2_comb;
902 --
903 -- ----------------------------------------------------------------------------
904 -- |------------------------<chk_address3_region3_comb >---------------------|
905 -- ----------------------------------------------------------------------------
906 --
907 procedure chk_address3_region3_comb(
908 p_address_id in per_addresses.address_id%TYPE,
909 p_object_version_number in per_addresses.object_version_number%TYPE,
910 p_address_line3 in per_addresses.address_line3%type,
911 p_region_3 in per_addresses.region_3%type) is
912
913 -- p_address_line3 ===> address_line3
914 -- p_region_3 ===> address_line3_kana
915
916 l_api_updating boolean;
917 l_proc varchar2(72) := g_package||'chk_address3_region3_comb';
918
919 begin
920 hr_utility.set_location('Entering:'|| l_proc, 1);
921
922 -- Only proceed with validation if :
923 -- a) The current g_old_rec is current and
924 -- b) The value for address_line3, or region_3 have changed
925 --
926 l_api_updating := per_kad_shd_t.api_updating
927 (p_address_id => p_address_id
928 ,p_object_version_number => p_object_version_number);
929
930 if ((l_api_updating and
931 (nvl(per_kad_shd_t.g_old_rec.address_line3, hr_api.g_varchar2) <>
932 nvl(p_address_line3, hr_api.g_varchar2) or
933 nvl(per_kad_shd_t.g_old_rec.region_3, hr_api.g_varchar2) <>
934 nvl(p_region_3, hr_api.g_varchar2)
935 )) or (NOT l_api_updating)) then
936
937 hr_utility.set_location(l_proc, 2);
938
939 if p_address_line3 is NULL and p_region_3 is not NULL then
940 hr_utility.set_message(801, 'HR_51698_ADD_REGION3_NOT_NULL');
941 hr_utility.raise_error;
942 end if;
943 chk_half_kana(p_region_3);
944 end if;
945
946 hr_utility.set_location('Leaving:'|| l_proc, 1);
947 end chk_address3_region3_comb;
948 */
949 --
950 -- ----------------------------------------------------------------------------
951 -- |------------------------< chk_jp_postal_code >----------------------------|
952 -- ----------------------------------------------------------------------------
953
954 procedure chk_jp_postal_code(p_string in varchar2) is
955
956 l_ch varchar2(2);
957 l_correct BOOLEAN := TRUE;
958 l_strlen number := length(p_string);
959 l_proc varchar2(72) := g_package||'chk_jp_postal_code';
960
961 begin
962 hr_utility.set_location('Entering:'|| l_proc, 1);
963
964 if l_strlen <> 3 and l_strlen <> 6 then
965 hr_utility.set_message(801, 'HR_51699_ADD_');
966 hr_utility.raise_error;
967 end if;
968
969 -- checking the first 3 characters
970
971 hr_utility.set_location(l_proc, 2);
972 for i in 1..3 loop
973 if substr(p_string, i, 1) between '0' and '9' then
974 NULL;
975 else
976 hr_utility.set_message(801, 'HR_51699_ADD_INVALID_POST_CODE');
977 hr_utility.raise_error;
978 end if;
979 end loop;
980
981 if l_strlen = 6 then
982 hr_utility.set_location(l_proc, 3);
983
984 -- checking the 4th character
985
986 l_ch := substr(p_string, 4, 1);
987 if l_ch = '-' then
988 NULL;
989 else
990 hr_utility.set_message(801, 'HR_51699_ADD_INVALID_POST_CODE');
991 hr_utility.raise_error;
992 end if;
993
994 -- checking the last 2 characters
995
996 for i in 5..6 loop
997 if substr(p_string, i, 1) between '0' and '9' then
998 NULL;
999 else
1000 hr_utility.set_message(801, 'HR_51699_ADD_INVALID_POST_CODE');
1001 hr_utility.raise_error;
1002 end if;
1003 end loop;
1004 end if;
1005
1006 hr_utility.set_location(' Leaving:'|| l_proc, 4);
1007 end chk_jp_postal_code;
1008 --
1009 -- ---------------------------------------------------------------------------
1010 -- |---------------------------< chk_postal_code >--------------------------|
1011 -- ---------------------------------------------------------------------------
1012 --
1013 -- Description:
1014 -- If address style is 'GB' then check that the postal code length is not
1015 -- more than eight characters long.
1016 --
1017 -- If address style is 'US' then check that the postal code is
1018 -- - 5 or 10 characters long.
1019 -- - first 5 characters must be numbers.
1020 -- - if postal code is 10 characters long, sixth character must be '-'
1021 -- follow by 4 numbers.
1022 -- - if US payroll is installed, postal code is mandatory.
1023 --
1024 -- Pre-conditions:
1025 -- Style (p_style) must be valid.
1026 --
1027 -- In Arguments:
1028 -- p_address_id
1029 -- p_style
1030 -- p_postal_code
1031 -- p_business_group_id
1032 -- p_object_version_number
1033 --
1034 -- Post Success:
1035 -- If address style is 'GB','US' or 'JP' and the postal code is valid,
1036 -- processing continues.
1037 --
1038 -- Post Failure:
1039 -- If address style is 'GB','US' or 'JP' and the postal code is invalid,
1040 -- an application error is raised and processing terminates.
1041 --
1042 --
1043 -- Access Status:
1044 -- Internal Table Handler Use Only.
1045 --
1046 -- {End Of Comments}
1047 -- ----------------------------------------------------------------------------
1048 procedure chk_postal_code
1049 (p_address_id in per_addresses.address_id%TYPE
1050 ,p_style in per_addresses.style%TYPE
1051 ,p_postal_code in per_addresses.postal_code%TYPE
1052 ,p_business_group_id in per_addresses.business_group_id%TYPE
1053 ,p_object_version_number in per_addresses.object_version_number%TYPE)
1054 is
1055 --
1056 l_proc varchar2(72) := g_package||'chk_postal_code';
1057 l_api_updating boolean;
1058 l_postal_code_1 varchar2(5);
1059 l_postal_code_2 varchar2(1);
1060 l_postal_code_3 varchar2(4);
1061 l_geocodes_installed varchar2(1); -- 09/12/97 Changed
1062 --
1063 begin
1064 hr_utility.set_location('Entering:'|| l_proc, 1);
1065 --
1066 -- Check mandatory parameters have been set
1067 --
1068 hr_api.mandatory_arg_error
1069 (p_api_name => l_proc
1070 ,p_argument => 'style'
1071 ,p_argument_value => p_style
1072 );
1073 --
1074 -- Only proceed with validation if :
1075 -- a) The current g_old_rec is current and
1076 -- b) The value for postal code has changed
1077 --
1078 l_api_updating := per_kad_shd.api_updating
1079 (p_address_id => p_address_id
1080 ,p_object_version_number => p_object_version_number);
1081 --
1082 if ((l_api_updating
1083 and nvl(per_kad_shd.g_old_rec.postal_code, hr_api.g_varchar2) <>
1084 nvl(p_postal_code, hr_api.g_varchar2)) or
1085 (NOT l_api_updating)) then
1086 hr_utility.set_location(l_proc, 2);
1087 --
1088 -- Check if US payroll is installed.
1089 --
1090 -- 09/12/97 Change Begins
1091 l_geocodes_installed := hr_general.chk_geocodes_installed;
1092 -- 09/12/97 Change Ends
1093 --
1094 if p_postal_code is not null then
1095 --
1096 -- Check that the GB postal code is no longer than
1097 -- 8 characters long
1098 --
1099 if p_style = 'GB' then
1100 if length(p_postal_code) > 8 then
1101 hr_utility.set_message(801, 'HR_7306_ADD_POST_CODE');
1102 hr_utility.raise_error;
1103 end if;
1104 --
1105 -- Check that the US postal code is either 5 or 10 character
1106 --
1107 elsif p_style = 'US' then
1108 hr_utility.set_location(l_proc, 3);
1109 --
1110 begin
1111 --
1112 if length(p_postal_code) = 5 then
1113 hr_utility.set_location(l_proc, 4);
1114 --
1115 -- Check if zip code is all numbers
1116 --
1117 for i in 1..5 loop
1118 if(substr(p_postal_code,i,1)
1119 not between '0' and '9') then
1120 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
1121 hr_utility.raise_error;
1122 end if;
1123 end loop;
1124 --
1125 elsif length(p_postal_code) = 10 then
1126 hr_utility.set_location(l_proc, 5);
1127 --
1128 -- Parse zip code to validate for correct format.
1129 --
1130 l_postal_code_1 := substr(p_postal_code,1,5);
1131 l_postal_code_2 := substr(p_postal_code,6,1);
1132 l_postal_code_3 := substr(p_postal_code,7,4);
1133 --
1134 -- Validate first 5 characters are numbers
1135 --
1136 for i in 1..5 loop
1137 if(substr(l_postal_code_1,i,1)
1138 not between '0' and '9') then
1139 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
1140 hr_utility.raise_error;
1141 end if;
1142 end loop;
1143 hr_utility.set_location(l_proc, 6);
1144 --
1145 -- Validate last 4 characters are numbers
1146 --
1147 for i in 1..4 loop
1148 if(substr(l_postal_code_3,i,1)
1149 not between '0' and '9') then
1150 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
1151 hr_utility.raise_error;
1152 end if;
1153 end loop;
1154 hr_utility.set_location(l_proc, 7);
1155 --
1156 -- Validate last sixth characters is '-'
1157 --
1158 if l_postal_code_2 <> '-' then
1159 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
1160 hr_utility.raise_error;
1161 end if;
1162
1163 else
1164 --
1165 -- If zip code is not 5 or 10 character long
1166 --
1167 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
1168 hr_utility.raise_error;
1169 end if;
1170 --
1171 -- If an invalid zip code character generates an
1172 -- exception
1173 --
1174 exception
1175 when others then
1176 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
1177 hr_utility.raise_error;
1178 end;
1179 /* elsif p_style = 'JP' then
1180 hr_utility.set_location(l_proc, 8);
1181 chk_jp_postal_code(p_postal_code); */
1182 end if;
1183 --
1184 -- If style is US and US payroll is installed, postal_code is mandatory.
1185 --
1186 else
1187 if p_style = 'US'
1188 and l_geocodes_installed = 'Y' then -- 09/12/97 Changed
1189 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
1190 hr_utility.raise_error;
1191 end if;
1192 end if;
1193 end if;
1194 --
1195 hr_utility.set_location(' Leaving:'|| l_proc, 9);
1196 end chk_postal_code;
1197 --
1198 -- ---------------------------------------------------------------------------
1199 -- |---------------------------< chk_region_1 >-----------------------------|
1200 -- ---------------------------------------------------------------------------
1201 --
1202 -- Description:
1203 -- If address style is 'GB' then validates that a region_1 code exists in
1204 -- table hr_lookups.
1205 --
1206 -- Pre-conditions:
1207 -- Style (p_style) must be valid.
1208 --
1209 -- In Arguments:
1210 -- p_address_id
1211 -- p_region_1
1212 -- p_style
1213 -- p_business_group_id
1214 -- p_effective_date
1215 -- p_object_version_number
1216 --
1217 -- Post Success:
1218 -- If address style is 'GB' and a row does exist in hr_lookups
1219 -- for the given region_1 code, processing continues.
1220 -- If address style is 'US' and a row does exist in pay_us_counties
1221 -- for the given region_1 code, processing continues.
1222 --
1223 -- Post Failure:
1224 -- If address style is 'GB' and a row does not exist in hr_lookups
1225 -- for the given region_1 code, an application error is raised
1226 -- and processing terminates.
1227 --
1228 -- Access Status:
1229 -- Internal Table Handler Use Only.
1230 --
1231 -- {End Of Comments}
1232 -- ----------------------------------------------------------------------------
1233 procedure chk_region_1
1234 (p_address_id in per_addresses.address_id%TYPE
1235 ,p_style in per_addresses.style%TYPE
1236 ,p_region_1 in per_addresses.region_1%TYPE
1237 ,p_business_group_id in per_addresses.business_group_id%TYPE
1238 ,p_effective_date in date
1239 ,p_object_version_number in per_addresses.object_version_number%TYPE)
1240 is
1241 --
1242 l_exists varchar2(1);
1243 l_proc varchar2(72) := g_package||'chk_region_1';
1244 l_api_updating boolean;
1245 --
1246 cursor csr_valid_us_county is
1247 select null
1248 from pay_us_counties
1249 where county_name = p_region_1;
1250 --
1251 begin
1252 hr_utility.set_location('Entering:'|| l_proc, 1);
1253 --
1254 -- Check mandatory parameters have been set
1255 --
1256 hr_api.mandatory_arg_error
1257 (p_api_name => l_proc
1258 ,p_argument => 'style'
1259 ,p_argument_value => p_style
1260 );
1261 --
1262 hr_api.mandatory_arg_error
1263 (p_api_name => l_proc
1264 ,p_argument => 'effective_date'
1265 ,p_argument_value => p_effective_date
1266 );
1267 --
1268 -- Only proceed with validation if :
1269 -- a) The current g_old_rec is current and
1270 -- b) The value for region_1 has changed
1271 --
1272 l_api_updating := per_kad_shd.api_updating
1273 (p_address_id => p_address_id
1274 ,p_object_version_number => p_object_version_number);
1275 --
1276 if ((l_api_updating and
1277 nvl(per_kad_shd.g_old_rec.region_1, hr_api.g_varchar2) <>
1278 nvl(p_region_1, hr_api.g_varchar2)) or
1279 (NOT l_api_updating)) then
1280 hr_utility.set_location(l_proc, 2);
1281 --
1282 -- Check that value for region_1 is valid
1283 --
1284 if p_region_1 is not null then
1285 hr_utility.set_location(l_proc, 3);
1286 --
1287 if p_style = 'GB' then
1288 hr_utility.set_location(l_proc, 4);
1289 --
1290 if hr_api.not_exists_in_hr_lookups
1291 (p_effective_date => p_effective_date
1292 ,p_lookup_type => 'GB_COUNTY'
1293 ,p_lookup_code => p_region_1
1294 ) then
1295 --
1296 -- Error: Invalid region 1.
1297 hr_utility.set_message(801, 'HR_7307_ADD_GB_REGION_1');
1298 hr_utility.raise_error;
1299 end if;
1300 end if;
1301 --
1302 -- Style is US and payroll is installed under US legislation.
1303 -- Region 1 is mandatory.
1304 --
1305 if p_style = 'US' then
1306 --
1307 -- If US payroll is installed.
1308 --
1309 if hr_general.chk_geocodes_installed = 'Y' then -- 09/12/97 Chg
1310 hr_utility.set_location(l_proc, 5);
1311 open csr_valid_us_county;
1312 fetch csr_valid_us_county into l_exists;
1313 if csr_valid_us_county%notfound then
1314 close csr_valid_us_county;
1315 hr_utility.set_message(801, 'HR_7953_ADDR_NO_COUNTY_FOUND');
1316 hr_utility.raise_error;
1317 end if;
1318 end if;
1319 end if;
1320 --
1321 end if;
1322 --
1323 end if;
1324 hr_utility.set_location(' Leaving:'|| l_proc, 6);
1325 end chk_region_1;
1326 --
1327 -- ---------------------------------------------------------------------------
1328 -- |---------------------------< chk_region_2 >-----------------------------|
1329 -- ---------------------------------------------------------------------------
1330 --
1331 -- Description:
1332 -- If address style is 'US', validate region_2 code (state abbreviation)
1333 -- exist in the hr_lookups table if HR installation only or non-US
1334 -- legislation, or the Vertex pay_us_states table if payroll is installed
1335 -- under US legislation.
1336 --
1337 -- Pre-conditions:
1338 -- Style (p_style) must be valid.
1339 --
1340 -- In Arguments:
1341 -- p_address_id
1342 -- p_region_2
1343 -- p_style
1344 -- p_business_group_id
1345 -- p_effective_date
1346 -- p_object_version_number
1347 --
1348 -- Post Success:
1349 -- If address style is 'US' and a row exist in hr_lookups/pay_us_states
1350 -- for the given region_2 code, processing continues.
1351 --
1352 -- Post Failure:
1353 -- If address style is 'US' and a row does not exist in
1354 -- hr_lookups/pay_us_states for the given region_2 code, an application
1355 -- error is raised and processing terminates.
1356 --
1357 -- Access Status:
1358 -- Internal Table Handler Use Only.
1359 --
1360 -- {End Of Comments}
1361 -- ----------------------------------------------------------------------------
1362 procedure chk_region_2
1363 (p_address_id in per_addresses.address_id%TYPE
1364 ,p_style in per_addresses.style%TYPE
1365 ,p_region_2 in per_addresses.region_2%TYPE
1366 ,p_business_group_id in per_addresses.business_group_id%TYPE
1367 ,p_effective_date in date
1368 ,p_object_version_number in per_addresses.object_version_number%TYPE)
1369 is
1370 --
1371 l_exists varchar2(1);
1372 l_proc varchar2(72) := g_package||'chk_region_2';
1373 l_api_updating boolean;
1374 l_geocodes_installed varchar2(1); -- 09/12/97 Changed
1375 --
1376 -- Declare cursor
1377 --
1378 cursor csr_valid_state is
1379 select null
1380 from pay_us_states
1381 where state_abbrev = p_region_2;
1382 --
1383 begin
1384 hr_utility.set_location('Entering:'|| l_proc, 1);
1385 --
1386 -- Check mandatory parameters have been set.
1387 --
1388 hr_api.mandatory_arg_error
1389 (p_api_name => l_proc
1390 ,p_argument => 'style'
1391 ,p_argument_value => p_style
1392 );
1393 --
1394 hr_api.mandatory_arg_error
1395 (p_api_name => l_proc
1396 ,p_argument => 'effective_date'
1397 ,p_argument_value => p_effective_date
1398 );
1399 --
1400 -- Only proceed with validation if :
1401 -- a) The current g_old_rec is current and
1402 -- b) The value for region_2 has changed
1403 --
1404 l_api_updating := per_kad_shd.api_updating
1405 (p_address_id => p_address_id
1406 ,p_object_version_number => p_object_version_number);
1407 --
1408 if ((l_api_updating and
1409 nvl(per_kad_shd.g_old_rec.region_2, hr_api.g_varchar2) <>
1410 nvl(p_region_2, hr_api.g_varchar2)) or
1411 (NOT l_api_updating)) then
1412 hr_utility.set_location(l_proc, 2);
1413 --
1414 -- Check if US payroll is installed.
1415 --
1416 if p_style = 'US' then
1417 -- 09/12/97 Change Begins
1418 l_geocodes_installed := hr_general.chk_geocodes_installed;
1419 -- 09/12/97 Change Ends
1420 end if;
1421 --
1422 -- Check that value for region_2 is valid.
1423 --
1424 if p_region_2 is not null then
1425 hr_utility.set_location(l_proc, 3);
1426 --
1427 if p_style = 'US'
1428 and l_geocodes_installed = 'N' then -- 09/12/97 Changed
1429 hr_utility.set_location(l_proc, 4);
1430 --
1431 if hr_api.not_exists_in_hr_lookups
1432 (p_effective_date => p_effective_date
1433 ,p_lookup_type => 'US_STATE'
1434 ,p_lookup_code => p_region_2
1435 ) then
1436 --
1437 -- Error: Invalid region 2.
1438 hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
1439 hr_utility.raise_error;
1440 end if;
1441 end if;
1442 end if;
1443 --
1444 -- If payroll is installed under US legislation.
1445 --
1446 if p_style = 'US'
1447 and l_geocodes_installed = 'Y' then -- 09/12/97 Changed
1448 hr_utility.set_location(l_proc, 5);
1449 --
1450 open csr_valid_state;
1451 fetch csr_valid_state into l_exists;
1452 if csr_valid_state%notfound then
1453 close csr_valid_state;
1454 hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
1455 hr_utility.raise_error;
1456 end if;
1457 close csr_valid_state;
1458 end if;
1459 --
1460 end if;
1461 --
1462 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1463 end chk_region_2;
1464 --
1465 -- ---------------------------------------------------------------------------
1466 -- |---------------------------< chk_town_or_city >--------------------------|
1467 -- ---------------------------------------------------------------------------
1468 --
1469 -- Description:
1470 -- If address style is 'US' and Payroll is installed under US legislation,
1471 -- validate town_or_city exist in pay_us_city_names.
1472 --
1473 -- Pre-conditions:
1474 -- Style (p_style) must be valid.
1475 --
1476 -- In Arguments:
1477 -- p_address_id
1478 -- p_town_or_city
1479 -- p_style
1480 -- p_business_group_id
1481 -- p_object_version_number
1482 --
1483 -- Post Success:
1484 -- If address style is 'US', payroll is installed under US legislation and
1485 -- a row exist in pay_us_city_names for the given town_or_city, processing
1486 -- continues.
1487 --
1488 -- Post Failure:
1489 -- If address style is 'US', payroll is installed under US legislation and
1490 -- a row does not exist in pay_us_city_names for the given town_or_city,
1491 -- an application error is raised and processing terminates.
1492 --
1493 -- Access Status:
1494 -- Internal Table Handler Use Only.
1495 --
1496 -- {End Of Comments}
1497 -- ----------------------------------------------------------------------------
1498 procedure chk_town_or_city
1499 (p_address_id in per_addresses.address_id%TYPE
1500 ,p_style in per_addresses.style%TYPE
1501 ,p_town_or_city in per_addresses.town_or_city%TYPE
1502 ,p_business_group_id in per_addresses.business_group_id%TYPE
1503 ,p_object_version_number in per_addresses.object_version_number%TYPE)
1504 is
1505 --
1506 l_exists varchar2(1);
1507 l_proc varchar2(72) := g_package||'chk_town_or_city';
1508 l_api_updating boolean;
1509 --
1510 -- Declare cursor
1511 --
1512 cursor csr_valid_town_or_city is
1513 select null
1514 from pay_us_city_names
1515 where city_name = p_town_or_city;
1516 begin
1517 hr_utility.set_location('Entering:'|| l_proc, 1);
1518 --
1519 -- Check mandatory parameters have been set
1520 --
1521 hr_api.mandatory_arg_error
1522 (p_api_name => l_proc
1523 ,p_argument => 'style'
1524 ,p_argument_value => p_style
1525 );
1526 --
1527 -- Only proceed with validation if :
1528 -- a) The current g_old_rec is current and
1529 -- b) The value for town_or_city has changed
1530 --
1531 l_api_updating := per_kad_shd.api_updating
1532 (p_address_id => p_address_id
1533 ,p_object_version_number => p_object_version_number);
1534 --
1535 if ((l_api_updating and
1536 nvl(per_kad_shd.g_old_rec.town_or_city, hr_api.g_varchar2) <>
1537 nvl(p_town_or_city, hr_api.g_varchar2)) or
1538 (NOT l_api_updating)) then
1539 hr_utility.set_location(l_proc, 2);
1540 --
1541 --
1542 hr_utility.set_location(l_proc, 3);
1543 --
1544 if p_style = 'US' then
1545 --
1546 -- If US payroll is installed.
1547 --
1548 if hr_general.chk_geocodes_installed = 'Y' then -- 09/12/97 Chg
1549 open csr_valid_town_or_city;
1550 fetch csr_valid_town_or_city into l_exists;
1551 if csr_valid_town_or_city%notfound then
1552 close csr_valid_town_or_city;
1553 hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
1554 hr_utility.raise_error;
1555 end if;
1556 hr_utility.set_location(l_proc, 4);
1557 --
1558 close csr_valid_town_or_city;
1559 end if;
1560 end if;
1561 end if;
1562 hr_utility.set_location(' Leaving:'|| l_proc, 5);
1563 end chk_town_or_city;
1564 --
1565 -- ---------------------------------------------------------------------------
1566 -- |-------------------< chk_city_state_zip_comb >----------------------------|
1567 -- ---------------------------------------------------------------------------
1568 --
1569 -- Description:
1570 -- Validates the city, state and zip code combination of a US
1571 -- address if payroll is installed under US legislation.
1572 --
1573 -- Pre-conditions:
1574 -- Style (p_style) must be valid and payroll is installed under
1575 -- US legislation.
1576 --
1577 -- In Arguments:
1578 -- p_address_id
1579 -- p_style
1580 -- p_postal_code
1581 -- p_region_2
1582 -- p_town_or_city
1583 -- p_business_group_id
1584 -- p_object_version_number
1585 --
1586 -- Post Success:
1587 -- Processing continues if:
1588 -- - the city and state combination is valid.
1589 -- - zip code is valid for the city and state.
1590 --
1591 -- Post Failure:
1592 -- Processing terminates if:
1593 -- - city and state combination is not valid.
1594 -- - zip code is not valid for the city and state.
1595 --
1596 -- Access status:
1597 -- Internal Table Handler Use Only.
1598 --
1599 -- {End Of Comments}
1600 -- ----------------------------------------------------------------------------
1601 procedure chk_city_state_zip_comb
1602 (p_address_id in per_addresses.address_id%TYPE
1603 ,p_style in per_addresses.style%TYPE
1604 ,p_postal_code in per_addresses.postal_code%TYPE
1605 ,p_region_2 in per_addresses.region_2%TYPE
1606 ,p_town_or_city in per_addresses.town_or_city%TYPE
1607 ,p_business_group_id in per_addresses.business_group_id%TYPE
1608 ,p_object_version_number in per_addresses.object_version_number%TYPE)
1609 is
1610 --
1611 l_exists varchar2(1);
1612 l_proc varchar2(72) := g_package||'chk_city_state_zip_comb';
1613 l_api_updating boolean;
1614 l_city_code pay_us_city_names.city_code%TYPE;
1615 l_state_code pay_us_city_names.state_code%TYPE;
1616 l_postal_code varchar2(6);
1617 l_geocodes_installed varchar2(1); -- 09/12/97 Changed
1618 --
1619 cursor csr_valid_city_state is
1620 select cty.city_code, cty.state_code
1621 from pay_us_city_names cty
1622 ,pay_us_states st
1623 where cty.state_code = st.state_code
1624 and st.state_abbrev = p_region_2
1625 and cty.city_name = p_town_or_city;
1626 --
1627 cursor csr_valid_zip_code is
1628 select null
1629 from pay_us_zip_codes
1630 where state_code = l_state_code
1631 and city_code = l_city_code
1632 and l_postal_code between zip_start
1633 and zip_end;
1634 --
1635 begin
1636 hr_utility.set_location('Entering:'|| l_proc, 1);
1637 --
1638 -- Only proceed with validation if :
1639 -- a) US address style and payroll is installed under US legislation and
1640 -- b) The current g_old_rec is current and
1641 -- c) The value for postal_code/region_2/town_or_city has changed.
1642 --
1643 l_api_updating := per_kad_shd.api_updating
1644 (p_address_id => p_address_id
1645 ,p_object_version_number => p_object_version_number);
1646 --
1647 if ((l_api_updating and
1648 (nvl(per_kad_shd.g_old_rec.region_2, hr_api.g_varchar2) <>
1649 nvl(p_region_2, hr_api.g_varchar2)) or
1650 (nvl(per_kad_shd.g_old_rec.postal_code, hr_api.g_varchar2) <>
1651 nvl(p_postal_code, hr_api.g_varchar2)) or
1652 (nvl(per_kad_shd.g_old_rec.town_or_city, hr_api.g_varchar2) <>
1653 nvl(p_town_or_city, hr_api.g_varchar2))) or
1654 (NOT l_api_updating)) then
1655 --
1656 hr_utility.set_location(l_proc, 2);
1657 --
1658 -- Check if US payroll installed.
1659 --
1660 -- 09/12/97 Change Begins
1661 l_geocodes_installed := hr_general.chk_geocodes_installed;
1662 -- 09/12/97 Change Ends
1663 --
1664 -- If US address style and payroll, validate for right combination of
1665 -- city, state and county.
1666 --
1667 if p_style = 'US'
1668 and l_geocodes_installed = 'Y' then -- 09/12/97 Changed
1669 open csr_valid_city_state;
1670 fetch csr_valid_city_state into l_city_code
1671 ,l_state_code;
1672 if csr_valid_city_state%notfound then
1673 close csr_valid_city_state;
1674 hr_utility.set_message(801, 'HR_51771_ADD_CITY_NOT_IN_STATE');
1675 hr_utility.raise_error;
1676 end if;
1677 close csr_valid_city_state;
1678 hr_utility.set_location(l_proc, 3);
1679 --
1680 -- Check if zip code is valid for city and state.
1681 -- Only the first 5 characters are used.
1682 --
1683 l_postal_code := substr(p_postal_code,1,5);
1684 --
1685 open csr_valid_zip_code;
1686 fetch csr_valid_zip_code into l_exists;
1687 if csr_valid_zip_code%notfound then
1688 close csr_valid_zip_code;
1689 hr_utility.set_message(801, 'HR_51282_ADD_INV_ZIP_FOR_CITY');
1690 hr_utility.raise_error;
1691 end if;
1692 close csr_valid_zip_code;
1693 hr_utility.set_location(l_proc, 4);
1694 end if;
1695 --
1696 end if;
1697 hr_utility.set_location(' Leaving:'|| l_proc, 5);
1698 --
1699 end chk_city_state_zip_comb;
1700 --
1701 -- ---------------------------------------------------------------------------
1702 -- |--------------------------< chk_del_address >----------------------------|
1703 -- ---------------------------------------------------------------------------
1704 --
1705 -- Description:
1706 -- Validates that primary addresses can only be deleted within a given
1707 -- date range if no other non primary addresses exist within that same
1708 -- date range
1709 --
1710 -- Validates that primary addresses can only be deleted if they do not
1711 -- break the contiguous nature of a primary address.
1712 --
1713 -- Non primary addresses can be deleted without any validation being
1714 -- performed
1715 --
1716 -- Pre-conditions:
1717 -- None
1718 -- In Arguments:
1719 -- None
1720 --
1721 -- Post Success:
1722 -- If no non-primary exist within the date range of the primary address
1723 -- selected for deletion and the deletion does not break up the
1724 -- the contiguous nature of the primary address then processing continues
1725 --
1726 -- Post Failure:
1727 -- If a non primary address exists within the date range of the primary
1728 -- address selected for deletion or the deletion breaks up the contiguous
1729 -- nature of the primary address then an application error is raised and
1730 -- processing terminated
1731 --
1732 -- Access Status:
1733 -- Internal Table Handler Use Only.
1734 --
1735 -- {End Of Comments}
1736 -- ----------------------------------------------------------------------------
1737 procedure chk_del_address is
1738 --
1739 l_exists varchar2(1);
1740 l_proc varchar2(72) := g_package||'chk_del_address';
1741 l_date date;
1742 --
1743 cursor csr_del_address is
1744 select null
1745 from per_addresses pa
1746 where pa.date_from between per_kad_shd.g_old_rec.date_from
1747 and l_date
1748 and pa.person_id = per_kad_shd.g_old_rec.person_id
1749 and pa.primary_flag = 'N';
1750 --
1751 cursor csr_no_del_contig_add is
1752 select null
1753 from sys.dual
1754 where exists(select null
1755 from per_addresses pa2
1756 where pa2.date_from > l_date
1757 and pa2.person_id = per_kad_shd.g_old_rec.person_id
1758 and pa2.primary_flag = 'Y');
1759 --
1760 begin
1761 l_date := nvl(per_kad_shd.g_old_rec.date_to, hr_api.g_eot);
1762 hr_utility.set_location('Entering:'|| l_proc, 1);
1763 --
1764 -- For primary addresses only
1765 -- ==========================
1766 --
1767 if per_kad_shd.g_old_rec.primary_flag = 'Y' then
1768 --
1769 -- Check that no non primary addresses
1770 -- exist within the date range of the
1771 -- currently selected primary address.
1772 -- Non primary addresses can be deleted
1773 -- at any time
1774 --
1775 open csr_del_address;
1776 fetch csr_del_address into l_exists;
1777 if csr_del_address%found then
1778 close csr_del_address;
1779 hr_utility.set_message(801, 'HR_7308_ADD_PRIMARY_DEL');
1780 hr_utility.raise_error;
1781 end if;
1782 close csr_del_address;
1783 hr_utility.set_location(l_proc, 2);
1784 --
1785 -- Check that the deletion of a primary
1786 -- address does not break the contiguous
1787 -- nature of the address
1788 --
1789 open csr_no_del_contig_add;
1790 fetch csr_no_del_contig_add into l_exists;
1791 if csr_no_del_contig_add%found then
1792 close csr_no_del_contig_add;
1793 hr_utility.set_message(801, 'HR_51030_ADDR_PRIM_GAP');
1794 hr_utility.raise_error;
1795 end if;
1796 close csr_no_del_contig_add;
1797 end if;
1798 --
1799 hr_utility.set_location(' Leaving:'|| l_proc, 3);
1800 end chk_del_address;
1801 --
1802 -- ----------------------------------------------------------------------------
1803 -- |----------------------< check_non_updateable_args >-----------------------|
1804 -- ----------------------------------------------------------------------------
1805 -- {Start Of Comments}
1806 --
1807 -- Description:
1808 -- This procedure is used to ensure that non updateable attributes have
1809 -- not been updated. If an attribute has been updated an error is generated.
1810 --
1811 -- Pre Conditions:
1812 -- g_old_rec has been populated with details of the values currently in
1813 -- the database.
1814 --
1815 -- In Arguments:
1816 -- p_rec has been populated with the updated values the user would like the
1817 -- record set to.
1818 --
1819 -- Post Success:
1820 -- Processing continues if all the non updateable attributes have not
1821 -- changed.
1822 --
1823 -- Post Failure:
1824 -- An application error is raised if any of the non updatable attributes
1825 -- (business_group_id, person_id, address_id, primary_flag or style)
1826 -- have been altered.
1827 --
1828 -- {End Of Comments}
1829 Procedure check_non_updateable_args(p_rec in per_kad_shd.g_rec_type) is
1830 --
1831 l_proc varchar2(72) := g_package||'check_non_updateable_args';
1832 l_error exception;
1833 l_argument varchar2(30);
1834 --
1835 Begin
1836 hr_utility.set_location('Entering:'||l_proc, 5);
1837 --
1838 -- Only proceed with validation if a row exists for
1839 -- the current record in the HR Schema
1840 --
1841 if not per_kad_shd.api_updating
1842 (p_address_id => p_rec.address_id,
1843 p_object_version_number => p_rec.object_version_number) then
1844 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1845 hr_utility.set_message_token('PROCEDURE', l_proc);
1846 hr_utility.set_message_token('STEP', '5');
1847 end if;
1848 --
1849 hr_utility.set_location(l_proc, 6);
1850 --
1851 if nvl(p_rec.business_group_id, hr_api.g_number) <>
1852 per_kad_shd.g_old_rec.business_group_id then
1853 l_argument := 'business_group_id';
1854 raise l_error;
1855 end if;
1856 hr_utility.set_location(l_proc, 7);
1857 --
1858 if nvl(p_rec.person_id, hr_api.g_number) <>
1859 per_kad_shd.g_old_rec.person_id then
1860 l_argument := 'person_id';
1861 raise l_error;
1862 end if;
1863 hr_utility.set_location(l_proc, 8);
1864 --
1865 if nvl(p_rec.primary_flag, hr_api.g_varchar2) <>
1866 per_kad_shd.g_old_rec.primary_flag then
1867 l_argument := 'primary_flag';
1868 raise l_error;
1869 end if;
1870 hr_utility.set_location(l_proc, 10);
1871 --
1872 if nvl(p_rec.style, hr_api.g_varchar2) <>
1873 per_kad_shd.g_old_rec.style then
1874 l_argument := 'style';
1875 raise l_error;
1876 end if;
1877 hr_utility.set_location(l_proc, 11);
1878 --
1879 exception
1880 when l_error then
1881 hr_api.argument_changed_error
1882 (p_api_name => l_proc
1883 ,p_argument => l_argument);
1884 when others then
1885 raise;
1886 hr_utility.set_location(' Leaving:'||l_proc, 12);
1887 end check_non_updateable_args;
1888 --
1889 -- ----------------------------------------------------------------------------
1890 -- |---------------------------< insert_validate >----------------------------|
1891 -- ----------------------------------------------------------------------------
1892 Procedure insert_validate
1893 (p_rec in out nocopy per_kad_shd.g_rec_type
1894 ,p_effective_date in date
1895 ) is
1896 --
1897 l_proc varchar2(72) := g_package||'insert_validate';
1898 --
1899 Begin
1900 hr_utility.set_location('Entering:'||l_proc, 5);
1901 --
1902 -- Reset global variable that indicates if payroll is installed under
1903 -- US legislation prior to validation.
1904 --
1905 g_us_payroll := NULL;
1906 --
1907 -- Call all supporting business operations.
1908 --
1909 -- Validate business group id
1910 --
1911 hr_api.validate_bus_grp_id(p_rec.business_group_id);
1912 --
1913 hr_utility.set_location(l_proc, 6);
1914 --
1915 -- Validate date from
1916 --
1917 chk_date_from
1918 (p_address_id => p_rec.address_id
1919 ,p_date_from => p_rec.date_from
1920 ,p_date_to => p_rec.date_to
1921 ,p_object_version_number => p_rec.object_version_number
1922 );
1923 --
1924 hr_utility.set_location(l_proc, 7);
1925 --
1926 -- Validate person_id
1927 --
1928 chk_person_id
1929 (p_person_id => p_rec.person_id
1930 );
1931 --
1932 hr_utility.set_location(l_proc, 8);
1933 --
1934 -- Validate style
1935 --
1936 chk_style
1937 (p_style => p_rec.style
1938 );
1939 --
1940 hr_utility.set_location(l_proc, 10);
1941 --
1942 -- Validate address type
1943 --
1944 chk_address_type
1945 (p_address_id => p_rec.address_id
1946 ,p_address_type => p_rec.address_type
1947 ,p_date_from => p_rec.date_from
1948 ,p_effective_date => p_effective_date
1949 ,p_object_version_number => p_rec.object_version_number
1950 );
1951 --
1952 hr_utility.set_location(l_proc, 11);
1953 --
1954 -- Validate country
1955 --
1956 chk_country
1957 (p_country => p_rec.country
1958 ,p_style => p_rec.style
1959 ,p_address_id => p_rec.address_id
1960 ,p_object_version_number => p_rec.object_version_number
1961 );
1962 --
1963 hr_utility.set_location(l_proc, 12);
1964 --
1965 -- Validate postal code.
1966 --
1967 chk_postal_code
1968 (p_address_id => p_rec.address_id
1969 ,p_style => p_rec.style
1970 ,p_postal_code => p_rec.postal_code
1971 ,p_business_group_id => p_rec.business_group_id
1972 ,p_object_version_number => p_rec.object_version_number
1973 );
1974 --
1975 --
1976 -- Validation specific to GB, US, GENERIC
1977 --
1978 if p_rec.style = 'GB' or
1979 p_rec.style = 'US' or
1980 p_rec.style = 'GENERIC' then
1981 -- Check null attributes for address style.
1982 --
1983 hr_utility.set_location(l_proc, 13);
1984 chk_style_null_attr
1985 -- 09/12/97 Change Begins
1986 (p_address_id => p_rec.address_id
1987 ,p_object_version_number => p_rec.object_version_number
1988 ,p_style => p_rec.style
1989 ,p_region_2 => p_rec.region_2
1990 ,p_region_3 => p_rec.region_3
1991 ,p_telephone_number_3 => p_rec.telephone_number_3
1992 );
1993 -- 09/12/97 Change Ends
1994 hr_utility.set_location(l_proc, 14);
1995 --
1996 -- Validate address_line1
1997 --
1998 chk_address_line1
1999 (p_address_id => p_rec.address_id
2000 ,p_style => p_rec.style
2001 ,p_address_line1 => p_rec.address_line1
2002 ,p_object_version_number => p_rec.object_version_number
2003 );
2004 hr_utility.set_location(l_proc, 15);
2005 --
2006 --
2007 --
2008 -- Validate date_to
2009 --
2010 -- No procedural call is made to the procedure
2011 -- chk_date_to as the insert logic is handled
2012 -- by chk_date_from
2013 --
2014 hr_utility.set_location(l_proc, 16);
2015 --
2016 -- Validate region 1.
2017 --
2018 chk_region_1
2019 (p_address_id => p_rec.address_id
2020 ,p_style => p_rec.style
2021 ,p_region_1 => p_rec.region_1
2022 ,p_business_group_id => p_rec.business_group_id
2023 ,p_effective_date => p_effective_date
2024 ,p_object_version_number => p_rec.object_version_number
2025 );
2026 --
2027 hr_utility.set_location(l_proc, 17);
2028 --
2029 -- Validate region 2.
2030 --
2031 chk_region_2
2032 (p_address_id => p_rec.address_id
2033 ,p_style => p_rec.style
2034 ,p_region_2 => p_rec.region_2
2035 ,p_business_group_id => p_rec.business_group_id
2036 ,p_effective_date => p_effective_date
2037 ,p_object_version_number => p_rec.object_version_number
2038 );
2039 --
2040 hr_utility.set_location(l_proc, 18);
2041 --
2042 -- Validate town or city.
2043 --
2044 chk_town_or_city
2045 (p_address_id => p_rec.address_id
2046 ,p_style => p_rec.style
2047 ,p_town_or_city => p_rec.town_or_city
2048 ,p_business_group_id => p_rec.business_group_id
2049 ,p_object_version_number => p_rec.object_version_number
2050 );
2051 hr_utility.set_location(l_proc, 19);
2052 --
2053 --
2054 -- This is only applicable if payroll is installed under US legislation
2055 -- and address style is 'US'.
2056 -- Validate city(town_or_city) and state(region_1)
2057 -- combination.
2058 --
2059 chk_city_state_zip_comb
2060 (p_address_id => p_rec.address_id
2061 ,p_style => p_rec.style
2062 ,p_postal_code => p_rec.postal_code
2063 ,p_region_2 => p_rec.region_2
2064 ,p_town_or_city => p_rec.town_or_city
2065 ,p_business_group_id => p_rec.business_group_id
2066 ,p_object_version_number => p_rec.object_version_number
2067 );
2068 --
2069 hr_utility.set_location(l_proc, 20);
2070 --
2071 --Validation specific to JP
2072 --
2073 /*elsif p_rec.style = 'JP' then
2074 hr_utility.set_location(l_proc, 21);
2075 --
2076 -- Check the combination checking for town_or_city(district_code)
2077 -- address_line1, and region_1
2078 --
2079 chk_address1_towncity_comb
2080 (p_address_id => p_rec.address_id
2081 ,p_object_version_number => p_rec.object_version_number
2082 ,p_town_or_city => p_rec.town_or_city
2083 ,p_address_line1 => p_rec.address_line1
2084 ,p_region_1 => p_rec.region_1
2085 );
2086 hr_utility.set_location(l_proc, 22);
2087 --
2088 -- Validate region_2 according to address_line2
2089 --
2090 chk_address2_region2_comb
2091 (p_address_id => p_rec.address_id
2092 ,p_object_version_number => p_rec.object_version_number
2093 ,p_address_line2 => p_rec.address_line2
2094 ,p_region_2 => p_rec.region_2
2095 );
2096 hr_utility.set_location(l_proc, 23);
2097 --
2098 -- Validate region_3 according to address_line3
2099 --
2100 chk_address3_region3_comb
2101 (p_address_id => p_rec.address_id
2102 ,p_object_version_number => p_rec.object_version_number
2103 ,p_address_line3 => p_rec.address_line3
2104 ,p_region_3 => p_rec.region_3
2105 );
2106 hr_utility.set_location(l_proc, 24); */
2107
2108 end if;
2109 -- Validate flexfields. This is commented out as we do not need flexfield
2110 -- validation for employee kiosk.
2111 --
2112 -- per_add_flex.df(p_rec => p_rec);
2113 --
2114 hr_utility.set_location(' Leaving:'||l_proc, 25);
2115 --
2116 End insert_validate;
2117 --
2118 -- ----------------------------------------------------------------------------
2119 -- |---------------------------< update_validate >----------------------------|
2120 -- ----------------------------------------------------------------------------
2121 Procedure update_validate
2122 (p_rec in out nocopy per_kad_shd.g_rec_type
2123 ,p_effective_date in date
2124 ) is
2125 --
2126 l_proc varchar2(72) := g_package||'update_validate';
2127 --
2128 Begin
2129 hr_utility.set_location('Entering:'||l_proc, 1);
2130 --
2131 -- Reset global variable that indicates if payroll is installed under
2132 -- US legislation prior to validation.
2133 --
2134 g_us_payroll := NULL;
2135 --
2136 -- Call all supporting business operations. Mapping to the
2137 -- appropriate Business Rules in peradd.bru is provided.
2138 --
2139 -- Validate business group id
2140 --
2141 hr_api.validate_bus_grp_id(p_rec.business_group_id);
2142 --
2143 -- Check that the columns which cannot
2144 -- be updated have not changed
2145 --
2146 check_non_updateable_args(p_rec => p_rec);
2147 --
2148 hr_utility.set_location(l_proc, 2);
2149 --
2150 -- Validate date from
2151 --
2152 chk_date_from
2153 (p_address_id => p_rec.address_id
2154 ,p_date_from => p_rec.date_from
2155 ,p_date_to => p_rec.date_to
2156 ,p_object_version_number => p_rec.object_version_number
2157 );
2158 --
2159 hr_utility.set_location(l_proc, 4);
2160 --
2161 -- Validate country.
2162 --
2163 chk_country
2164 (p_country => p_rec.country
2165 ,p_style => p_rec.style
2166 ,p_address_id => p_rec.address_id
2167 ,p_object_version_number => p_rec.object_version_number
2168 );
2169 --
2170 hr_utility.set_location(l_proc, 5);
2171 --
2172 -- Validate address type.
2173 --
2174 chk_address_type
2175 (p_address_id => p_rec.address_id
2176 ,p_address_type => p_rec.address_type
2177 ,p_date_from => p_rec.date_from
2178 ,p_effective_date => p_effective_date
2179 ,p_object_version_number => p_rec.object_version_number
2180 );
2181 --
2182 hr_utility.set_location(l_proc, 6);
2183 --
2184 -- Validate postal code.
2185 --
2186 chk_postal_code
2187 (p_address_id => p_rec.address_id
2188 ,p_style => p_rec.style
2189 ,p_postal_code => p_rec.postal_code
2190 ,p_business_group_id => p_rec.business_group_id
2191 ,p_object_version_number => p_rec.object_version_number
2192 );
2193 hr_utility.set_location(l_proc, 8);
2194 --
2195 -- Validate date to.
2196 --
2197 chk_date_to
2198 (p_address_id => p_rec.address_id
2199 ,p_date_from => p_rec.date_from
2200 ,p_date_to => p_rec.date_to
2201 ,p_object_version_number => p_rec.object_version_number
2202 );
2203 hr_utility.set_location(l_proc, 9);
2204 --
2205 --
2206 -- Validation specific to GB, US, GENERIC
2207 --
2208 if p_rec.style = 'GB' or
2209 p_rec.style = 'US' or
2210 p_rec.style = 'GENERIC' then
2211 --
2212 -- Validate region 1.
2213 --
2214 chk_region_1
2215 (p_address_id => p_rec.address_id
2216 ,p_style => p_rec.style
2217 ,p_region_1 => p_rec.region_1
2218 ,p_business_group_id => p_rec.business_group_id
2219 ,p_effective_date => p_effective_date
2220 ,p_object_version_number => p_rec.object_version_number
2221 );
2222 hr_utility.set_location(l_proc, 10);
2223 --
2224 -- Validate region 2.
2225 --
2226 chk_region_2
2227 (p_address_id => p_rec.address_id
2228 ,p_style => p_rec.style
2229 ,p_region_2 => p_rec.region_2
2230 ,p_business_group_id => p_rec.business_group_id
2231 ,p_effective_date => p_effective_date
2232 ,p_object_version_number => p_rec.object_version_number
2233 );
2234 hr_utility.set_location(l_proc, 11);
2235 --
2236 -- Validate town or city.
2237 --
2238 chk_town_or_city
2239 (p_address_id => p_rec.address_id
2240 ,p_style => p_rec.style
2241 ,p_town_or_city => p_rec.town_or_city
2242 ,p_business_group_id => p_rec.business_group_id
2243 ,p_object_version_number => p_rec.object_version_number
2244 );
2245 hr_utility.set_location(l_proc, 12);
2246 --
2247 -- Validate address_line 1.
2248 --
2249 chk_address_line1
2250 (p_address_id => p_rec.address_id
2251 ,p_style => p_rec.style
2252 ,p_address_line1 => p_rec.address_line1
2253 ,p_object_version_number => p_rec.object_version_number
2254 );
2255 hr_utility.set_location(l_proc, 13);
2256 --
2257 -- Check null attributes for address style.
2258 --
2259 chk_style_null_attr
2260 -- 09/12/97 Change Begins
2261 (p_address_id => p_rec.address_id
2262 ,p_object_version_number => p_rec.object_version_number
2263 ,p_style => p_rec.style
2264 ,p_region_2 => p_rec.region_2
2265 ,p_region_3 => p_rec.region_3
2266 ,p_telephone_number_3 => p_rec.telephone_number_3
2267 );
2268 -- 09/12/97 Change Ends
2269 hr_utility.set_location(l_proc, 14);
2270 --
2271 -- This is only applicable if payroll is installed under US legislation
2272 -- and address style is 'US'.
2273 -- Validate city(town_or_city) and state(region_2)
2274 -- and zip code(postal_code) combination.
2275 --
2276 chk_city_state_zip_comb
2277 (p_address_id => p_rec.address_id
2278 ,p_style => p_rec.style
2279 ,p_postal_code => p_rec.postal_code
2280 ,p_region_2 => p_rec.region_2
2281 ,p_town_or_city => p_rec.town_or_city
2282 ,p_business_group_id => p_rec.business_group_id
2283 ,p_object_version_number => p_rec.object_version_number
2284 );
2285 --
2286 hr_utility.set_location(l_proc, 15);
2287 --
2288 --
2289 /* elsif p_rec.style = 'JP' then
2290 hr_utility.set_location(l_proc, 16);
2291 --
2292 -- Check the combination checking for town_or_city(district_code)
2293 -- address_line1, and region_1
2294 --
2295 chk_address1_towncity_comb
2296 (p_address_id => p_rec.address_id
2297 ,p_object_version_number => p_rec.object_version_number
2298 ,p_town_or_city => p_rec.town_or_city
2299 ,p_address_line1 => p_rec.address_line1
2300 ,p_region_1 => p_rec.region_1
2301 );
2302 hr_utility.set_location(l_proc, 17);
2303 --
2304 -- Validate region_2 according to address_line2
2305 --
2306 chk_address2_region2_comb
2307 (p_address_id => p_rec.address_id
2308 ,p_object_version_number => p_rec.object_version_number
2309 ,p_address_line2 => p_rec.address_line2
2310 ,p_region_2 => p_rec.region_2
2311 );
2312 hr_utility.set_location(l_proc, 18);
2313 --
2314 -- Validate region_3 according to address_line3
2315 --
2316 chk_address3_region3_comb
2317 (p_address_id => p_rec.address_id
2318 ,p_object_version_number => p_rec.object_version_number
2319 ,p_address_line3 => p_rec.address_line3
2320 ,p_region_3 => p_rec.region_3
2321 );
2322 hr_utility.set_location(l_proc, 19); */
2323
2324 end if;
2325 --
2326 -- Validate flexfields. This is commented out as we do not need flexfields
2327 -- validation for employee kiosk.
2328 --
2329 -- per_add_flex.df(p_rec => p_rec);
2330 --
2331 hr_utility.set_location(' Leaving:'||l_proc, 20);
2332 End update_validate;
2333 --
2334 -- ----------------------------------------------------------------------------
2335 -- |---------------------------< delete_validate >----------------------------|
2336 -- ----------------------------------------------------------------------------
2337 Procedure delete_validate(p_rec in out nocopy per_kad_shd.g_rec_type) is
2338 --
2339 l_proc varchar2(72) := g_package||'delete_validate';
2340 --
2341 Begin
2342 hr_utility.set_location('Entering:'||l_proc, 5);
2343 --
2344 -- Call all supporting business operations. Mapping to the
2345 -- appropriate business rules on peradd.bru is provided
2346 --
2347 -- Validate business group id
2348 --
2349 hr_api.validate_bus_grp_id(p_rec.business_group_id);
2350 --
2351 -- Check that deletion of a primary address is not allowed
2352 -- if non primary addresses exist within the same date range
2353 -- as the primary address
2354 --
2355 chk_del_address;
2356 --
2357 hr_utility.set_location(' Leaving:'||l_proc, 10);
2358 End delete_validate;
2359 --
2360 end per_kad_bus;
[Home] [Help]