[Home] [Help]
PACKAGE BODY: APPS.PER_ADD_BUS
Source
1 Package Body per_add_bus as
2 /* $Header: peaddrhi.pkb 120.1.12010000.3 2008/08/06 08:53:04 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_add_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 -- The following two global variables are only to be used by the
19 -- return_legislation_code function.
20 --
21 g_address_id number default null;
22 g_legislation_code varchar2(150) default null;
23 --
24 -- ----------------------------------------------------------------------------
25 -- |--------------------------< set_called_from_form >------------------------|
26 -- ----------------------------------------------------------------------------
27 procedure set_called_from_form
28 ( p_flag in boolean ) as
29 begin
30 g_called_from_form:=p_flag;
31 end;
32 --
33 -- ----------------------------------------------------------------------------
34 -- |-------------------------< chk_business_group_id >------------------------|
35 -- ----------------------------------------------------------------------------
36 -- {Start Of Comments}
37 --
38 -- Description:
39 -- This procedure ensures that business_group_id value is valid.
40 --
41 -- Pre Conditions:
42 -- g_old_rec has been populated with details of the values currently in
43 -- the database.
44 --
45 -- In Arguments:
46 -- p_business_group_id
47 -- p_address_id
48 -- p_object_version_number
49 --
50 -- Post Success:
51 -- Processing continues if business_group_id is valid and if updating,
52 -- old_rec.business_group_id is null
53 --
54 -- Post Failure:
55 -- An application error is raised
56 -- if updating and old_rec.business_group_id is not null, or
57 -- business_group_id is not valid.
58 --
59 -- {End Of Comments}
60 -- ----------------------------------------------------------------------------
61 procedure chk_business_group_id
62 (p_address_id in per_addresses.address_id%TYPE
63 ,p_object_version_number in per_addresses.object_version_number%TYPE
64 ,p_person_id in per_addresses.person_id%TYPE
65 ,p_business_group_id in per_addresses.business_group_id%TYPE
66 )
67 is
68 --
69
70 --
71 l_proc varchar2(72) := g_package||'chk_business_group_id';
72 l_api_updating boolean;
73 --
74 begin
75 hr_utility.set_location('Entering:'||l_proc, 5);
76 if p_business_group_id is not null then
77 hr_utility.set_location(l_proc, 10);
78 --
79 hr_api.validate_bus_grp_id(p_business_group_id);
80 --
81 --
82 --If BUSINESS_GROUP_ID is specified, PERSON_ID must be specified
83 --
84 if p_person_id is null then
85 --
86 hr_utility.set_message(800, 'HR_289945_INV_PERSON_ID');
87 hr_utility.raise_error;
88 --
89 end if;
90 --
91 end if;
92 --
93 l_api_updating := per_add_shd.api_updating
94 (p_address_id => p_address_id
95 ,p_object_version_number => p_object_version_number );
96 --
97 --UPDATE not allowed unless currently null(U)
98 --
99 if (l_api_updating
100 and nvl(per_add_shd.g_old_rec.business_group_id,hr_api.g_number) <> hr_api.g_number
101 and per_add_shd.g_old_rec.business_group_id <> p_business_group_id ) then
102 --
103 hr_utility.set_message(800, 'HR_289947_INV_UPD_BG_ID');
104 hr_utility.raise_error;
105 --
106
107 end if;
108 --
109 hr_utility.set_location('Leaving:'||l_proc, 40);
110 exception
111 when app_exception.application_exception then
112 if hr_multi_message.exception_add
113 (p_associated_column1 => 'PER_PREVIOUS_EMPLOYERS.BUSINESS_GROUP_ID'
114 ) then
115 hr_utility.set_location('Leaving:'||l_proc, 50);
116 raise;
117 end if;
118 hr_utility.set_location('Leaving:'||l_proc,60);
119 end chk_business_group_id;
120 --
121 -- ---------------------------------------------------------------------------
122 -- |---------------------------< chk_person_id >----------------------------|
123 -- ---------------------------------------------------------------------------
124 --
125 -- Description:
126 -- - Validates that a person id exists in table per_people_f.
127 -- - Validates that the business group of the address matches
128 -- the business group of the person.
129 --
130 -- Pre-conditions:
131 -- None.
132 --
133 -- In Arguments:
134 -- p_person_id
135 -- p_business_group_id
136 --
137 -- Post Success:
138 -- If a row does exist in per_people_f for the given person id then
139 -- processing continues.
140 --
141 -- Post Failure:
142 -- If a row does not exist in per_people_f for the given person id then
143 -- an application error will be raised and processing is terminated.
144 --
145 -- Access Status:
146 -- Internal Table Handler Use Only.
147 --
148 -- {End Of Comments}
149 -- ----------------------------------------------------------------------------
150 procedure chk_person_id
151 (p_address_id in per_addresses.address_id%TYPE
152 ,p_object_version_number in per_addresses.object_version_number%TYPE
153 ,p_person_id in per_addresses.person_id%TYPE
154 ,p_business_group_id in per_addresses.business_group_id%TYPE
155 )
156 is
157 --
158 l_proc varchar2(72) := g_package||'chk_person_id';
159 --
160 l_api_updating boolean;
161 l_business_group_id number;
162 --
163 cursor csr_valid_pers is
164 select business_group_id
165 from per_all_people_f ppf
166 where ppf.person_id = p_person_id;
167 --
168 begin
169 hr_utility.set_location('Entering:'|| l_proc, 10);
170 --
171 -- Check mandatory parameters have been set
172 --
173 hr_api.mandatory_arg_error
174 (p_api_name => l_proc
175 ,p_argument => 'person_id'
176 ,p_argument_value => p_person_id
177 );
178 --
179 hr_api.mandatory_arg_error
180 (p_api_name => l_proc
181 ,p_argument => 'business_group_id'
182 ,p_argument_value => p_business_group_id
183 );
184 hr_utility.set_location(l_proc, 20);
185 --
186 -- Check if inserting or updating with modified values
187 --
188 l_api_updating := per_add_shd.api_updating
189 (p_address_id => p_address_id
190 ,p_object_version_number => p_object_version_number
191 );
192 --
193 if ((l_api_updating and per_add_shd.g_old_rec.person_id <> p_person_id)
194 or
195 (NOT l_api_updating))
196 then
197 hr_utility.set_location(l_proc, 30);
198 --
199 -- Check that the Person ID is linked to a
200 -- valid person on PER_PEOPLE_F
201 --
202 open csr_valid_pers;
203 fetch csr_valid_pers into l_business_group_id;
204 if csr_valid_pers%notfound then
205 --
206 close csr_valid_pers;
207 hr_utility.set_message(801, 'HR_7298_ADD_PERSON_INVALID');
208 hr_utility.raise_error;
209 --
210 else
211 close csr_valid_pers;
212 hr_utility.set_location(l_proc, 40);
213 --
214 -- Check that the business group of the person is the same as the
215 -- business group of the address
216 --
217 if p_business_group_id <> l_business_group_id then
218 --
219 hr_utility.set_message(800, 'PER_52989_ADD_NOMATCH_BGP');
220 hr_utility.raise_error;
221 end if;
222 --
223 end if;
224 --
225 end if;
226 --
227 hr_utility.set_location(' Leaving:'|| l_proc, 42);
228 --
229 --UPDATE not allowed unless currently null(U)
230 --
231 if (l_api_updating
232 and nvl(per_add_shd.g_old_rec.person_id,hr_api.g_number) <> hr_api.g_number
233 and per_add_shd.g_old_rec.person_id <> p_person_id
234 ) then
235 --
236 hr_utility.set_message(800, 'HR_289948_INV_UPD_PERSON_ID');
237 hr_utility.raise_error;
238 --
239 end if;
240 hr_utility.set_location(' Leaving:'|| l_proc, 45);
241 --
242 --If BUSINESS_GROUP_ID is specified, PERSON_ID must be specified
243 --
244 if p_business_group_id is not null and p_person_id is null then
245 --
246 hr_utility.set_message(800, 'HR_289945_INV_PERSON_ID');
247 hr_utility.raise_error;
248 --
249 end if;
250 --
251 hr_utility.set_location(' Leaving:'|| l_proc, 50);
252 exception
253 when app_exception.application_exception then
254 if hr_multi_message.exception_add
255 (p_associated_column1 => 'PER_ADDRESSES.PERSON_ID'
256 ) then
257 hr_utility.set_location(' Leaving:'||l_proc,60);
258 raise;
259 end if;
260 hr_utility.set_location(' Leaving:'||l_proc,70);
261 end chk_person_id;
262 -- ---------------------------------------------------------------------------
263 -- |---------------------------< chk_party_id >----------------------------|
264 -- ---------------------------------------------------------------------------
265 --
266 -- Description:
267 -- - Validates that a party id exists in table hz_parties.
268 --
269 -- Pre-conditions:
270 -- None.
271 --
272 -- In Arguments:
273 -- p_party_id
274 --
275 -- Post Success:
276 -- If a row does exist in hz_parties for the given party id then
277 -- processing continues.
278 --
279 -- Post Failure:
280 -- If a row does not exist in hz_parties for the given party id 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_party_id
289 (p_rec in out nocopy per_add_shd.g_rec_type
290 )
291 is
292 --
293 l_proc varchar2(72) := g_package||'chk_party_id';
294 --
295 l_exists varchar2(1);
296 --
297 l_party_id per_addresses.party_id%TYPE;
298 l_party_id2 per_addresses.party_id%TYPE;
299 --
300 --
301 -- cursor to check that the party_id matches person_id
302 --
303 cursor csr_get_party_id is
304 select party_id
305 from per_all_people_f per
306 where per.person_id = p_rec.person_id
307 and p_rec.date_from
308 between per.effective_start_date
309 and nvl(per.effective_end_date,hr_api.g_eot);
310 --
311 cursor csr_valid_party_id is
312 select party_id
313 from hz_parties hzp
314 where hzp.party_id = p_rec.party_id;
315 --
316 begin
317 hr_utility.set_location('Entering:'|| l_proc, 1);
318 --
319 --
320 if p_rec.person_id is not null then
321 if hr_multi_message.no_all_inclusive_error
322 (p_check_column1 => 'PER_ADDRESSES.PERSON_ID'
323 ,p_check_column2 => 'PER_ADDRESSES.DATE_FROM'
324 ) then
325 --
326 open csr_get_party_id;
327 fetch csr_get_party_id into l_party_id;
328 close csr_get_party_id;
329 hr_utility.set_location(l_proc,20);
330 if p_rec.party_id is not null then
331 if p_rec.party_id <> nvl(l_party_id,-1) then
332 hr_utility.set_message(800, 'HR_289343_PERSONPARTY_MISMATCH');
333 hr_utility.set_location(l_proc,30);
334 hr_multi_message.add
335 (p_associated_column1 => 'PER_ADDRESSES.PERSON_ID'
336 ,p_associated_column2 => 'PER_ADDRESSES.DATE_FROM'
337 ,p_associated_column3 => 'PER_ADDRESSES.PARTY_ID'
338 );
339 end if;
340 else
341 --
342 -- derive party_id from per_all_people_f using person_id
343 --
344 hr_utility.set_location(l_proc,50);
345 p_rec.party_id := l_party_id;
346 end if;
347 end if;
348 else
349 if p_rec.party_id is null then
350 hr_utility.set_message(800, 'HR_289341_CHK_PERSON_OR_PARTY');
351 hr_utility.set_location(l_proc,60);
352 hr_multi_message.add
353 ( p_associated_column1 => 'PER_ADDRESSES.PARTY_ID'
354 );
355 else
356 open csr_valid_party_id;
357 fetch csr_valid_party_id into l_party_id2;
358 if csr_valid_party_id%notfound then
359 close csr_valid_party_id;
360 hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
361 hr_utility.set_location(l_proc,70);
362 hr_multi_message.add
363 (p_associated_column1 => 'PER_ADDRESSES.PARTY_ID'
364 );
365 else
366 --
367 close csr_valid_party_id;
368 --
369 end if;
370 end if;
371 end if;
372 --
373 hr_utility.set_location(' Leaving:'||l_proc,100);
374 End chk_party_id;
375 --
376 -- ---------------------------------------------------------------------------
377 -- |---------------------------< chk_primary_flag >-------------------------|
378 -- ---------------------------------------------------------------------------
379 --
380 -- Description:
381 -- Validates that the primary flag is either 'Y' or 'N'
382 --
383 -- Pre-conditions:
384 -- None.
385 --
386 -- In Arguments:
387 -- p_primary_flag
388 --
389 -- Post Success:
390 -- If the value is valid then processing continues
391 --
392 -- Post Failure:
393 -- If the value is invalid then an error is raised.
394 --
395 -- Access Status:
396 -- Internal Table Handler Use Only.
397 --
398 -- {End Of Comments}
399 -- ----------------------------------------------------------------------------
400 procedure chk_primary_flag
401 (p_address_id in per_addresses.address_id%TYPE
402 ,p_object_version_number in per_addresses.object_version_number%TYPE
403 ,p_primary_flag in per_addresses.primary_flag%TYPE
404 )
405 is
406 --
407 l_proc varchar2(72) := g_package||'chk_primary_flag';
408 --
409 l_api_updating boolean;
410 --
411 begin
412 hr_utility.set_location('Entering:'|| l_proc, 10);
413 --
414 -- Check mandatory parameters have been set
415 --
416 hr_api.mandatory_arg_error
417 (p_api_name => l_proc
418 ,p_argument => 'p_primary_flag'
419 ,p_argument_value => p_primary_flag
420 );
421 hr_utility.set_location(l_proc, 20);
422 --
423 -- Check if inserting or updating with modified values
424 --
425 l_api_updating := per_add_shd.api_updating
426 (p_address_id => p_address_id
427 ,p_object_version_number => p_object_version_number
428 );
429 --
430 if ((l_api_updating and per_add_shd.g_old_rec.primary_flag <> p_primary_flag)
431 or
432 (NOT l_api_updating))
433 then
434 hr_utility.set_location(l_proc, 30);
435 --
436 -- Check that the value is 'Y' or 'N'
437 --
438 if p_primary_flag not in('Y','N') then
439 --
440 per_add_shd.constraint_error
441 (p_constraint_name => 'PER_ADDR_PRIMARY_FLAG_CHK'
442 );
443 --
444 end if;
445 hr_utility.set_location(l_proc, 40);
446 --
447 end if;
448 hr_utility.set_location(' Leaving:'|| l_proc, 50);
449 end chk_primary_flag;
450 --
451 -- ---------------------------------------------------------------------------
452 -- |-------------------------< chk_address_type >---------------------------|
453 -- ---------------------------------------------------------------------------
454 --
455 -- Description:
456 -- Validates that an address type exists in table hr_lookups
457 -- where lookup_type is 'ADDRESS_TYPE'
458 -- and enabled_flag is 'Y'
459 -- and effective_date is between the active dates (if they are not null).
460 --
461 -- Pre-conditions:
462 -- Effective_date must be valid.
463 --
464 -- In Arguments:
465 -- p_address_id
466 -- p_date_from
467 -- p_address_type
468 -- p_effective_date
469 -- p_object_version_number
470 --
471 -- Post Success:
472 -- If a row does exist in hr_lookups for the given address code then
473 -- processing continues.
474 --
475 -- Post Failure:
476 -- If a row does not exist in hr_lookups for the given address code then
477 -- an application error will be raised and processing is terminated.
478 --
479 -- Access Status:
480 -- Internal Table Handler Use Only.
481 --
482 -- {End Of Comments}
483 -- ----------------------------------------------------------------------------
484 procedure chk_address_type
485 (p_address_id in per_addresses.address_id%TYPE
486 ,p_address_type in per_addresses.address_type%TYPE
487 ,p_date_from in per_addresses.date_from%TYPE
488 ,p_effective_date in date
489 ,p_object_version_number in per_addresses.object_version_number%TYPE) is
490 --
491 l_exists varchar2(1);
492 l_proc varchar2(72) := g_package||'chk_address_type';
493 l_api_updating boolean;
494 --
495 begin
496 hr_utility.set_location('Entering:'|| l_proc, 1);
497 --
498 -- Check mandatory parameters have been set
499 --
500 hr_api.mandatory_arg_error
501 (p_api_name => l_proc
502 ,p_argument => 'date_from'
503 ,p_argument_value => p_date_from
504 );
505 hr_api.mandatory_arg_error
506 (p_api_name => l_proc
507 ,p_argument => 'effective_date'
508 ,p_argument_value => p_effective_date
509 );
510 --
511 -- Only proceed with validation if :
512 -- a) The current g_old_rec is current and
513 -- b) The value for address type has changed
514 --
515 l_api_updating := per_add_shd.api_updating
516 (p_address_id => p_address_id
517 ,p_object_version_number => p_object_version_number);
518 --
519 if ((l_api_updating and
520 nvl(per_add_shd.g_old_rec.address_type, hr_api.g_varchar2) <>
521 nvl(p_address_type, hr_api.g_varchar2)) or
522 (NOT l_api_updating)) then
523 hr_utility.set_location(l_proc, 2);
524 --
525 -- Checks that the value for address_type is
526 -- valid and exists on hr_lookups within the
527 -- specified date range
528 --
529 if p_address_type is not null then
530 --
531 -- Bug 1472162.
532 --
533 -- if hr_api.not_exists_in_hr_lookups
534 if hr_api.not_exists_in_leg_lookups
535 (p_effective_date => p_effective_date
536 ,p_lookup_type => 'ADDRESS_TYPE'
537 ,p_lookup_code => p_address_type
538 ) then
539 --
540 -- Error: Invalid address type.
541 hr_utility.set_message(801, 'HR_7299_ADD_TYPE_INVALID');
542 hr_utility.raise_error;
543 end if;
544 end if;
545 end if;
546 --
547 hr_utility.set_location(' Leaving:'|| l_proc, 4);
548 exception
549 when app_exception.application_exception then
550 if hr_multi_message.exception_add
551 (p_associated_column1 => 'PER_ADDRESSES.ADDRESS_TYPE'
552 ) then
553 hr_utility.set_location(' Leaving:'||l_proc,5);
554 raise;
555 end if;
556 hr_utility.set_location(' Leaving:'||l_proc,6);
557 --
558 end chk_address_type;
559 --
560 -- ---------------------------------------------------------------------------
561 -- |---------------------------< chk_country >------------------------------|
562 -- ---------------------------------------------------------------------------
563 --
564 -- Description:
565 -- Validates that a country code exists in table fnd_territories
566 -- for US, GB and GENERIC address styles.
567 --
568 -- Pre-conditions:
569 -- Style (p_style) must be valid.
570 --
571 -- In Arguments:
572 -- p_country
573 -- p_address_id
574 -- p_object_version_number
575 --
576 -- Post Success:
577 -- If a row does exist in fnd_territories for the given country code then
578 -- processing continues.
579 --
580 -- Post Failure:
581 -- If a row does not exist in fnd_territories for the given country code then
582 -- an application error will be raised and processing is terminated.
583 --
584 -- Access Status:
585 -- Internal Table Handler Use Only.
586 --
587 -- {End Of Comments}
588 -- ----------------------------------------------------------------------------
589 procedure chk_country
590 (p_address_id in per_addresses.address_id%TYPE
591 ,p_style in per_addresses.style%TYPE
592 ,p_country in per_addresses.country%TYPE
593 ,p_object_version_number in per_addresses.object_version_number%TYPE)
594 is
595 --
596 l_exists varchar2(1);
597 l_proc varchar2(72) := g_package||'chk_country';
598 l_api_updating boolean;
599 --
600 cursor csr_valid_ctry is
601 select null
602 from fnd_territories ft
603 where ft.territory_code = p_country;
604 --
605 begin
606 hr_utility.set_location('Entering:'|| l_proc, 1);
607 --
608 -- Only proceed with validation if :
609 -- a) The current g_old_rec is current and
610 -- b) The value for country has changed
611 --
612 l_api_updating := per_add_shd.api_updating
613 (p_address_id => p_address_id
614 ,p_object_version_number => p_object_version_number);
615 --
616 if ((l_api_updating and
617 nvl(per_add_shd.g_old_rec.country, hr_api.g_varchar2) <>
618 nvl(p_country, hr_api.g_varchar2)) or
619 (NOT l_api_updating)) then
620 hr_utility.set_location(l_proc, 2);
621 --
622 -- Checks that value for country is a valid
623 -- country on fnd_territories
624 --
625 if p_style = 'US' or
626 p_style = 'GB' then
627 -- Bug 1677965
628 -- (p_style = 'JP' and p_country is not null) then
629 open csr_valid_ctry;
630 fetch csr_valid_ctry into l_exists;
631 if csr_valid_ctry%notfound then
632 close csr_valid_ctry;
633 hr_utility.set_message(801, 'HR_7300_ADD_COUNTRY_INVALID');
634 hr_utility.raise_error;
635 end if;
636 close csr_valid_ctry;
637 end if;
638 end if;
639 --
640 hr_utility.set_location(' Leaving:'|| l_proc, 3);
641 exception
642 when app_exception.application_exception then
643 if hr_multi_message.exception_add
644 (p_associated_column1 => 'PER_ADDRESSES.COUNTRY'
645 ) then
646 hr_utility.set_location(' Leaving:'||l_proc,4);
647 raise;
648 end if;
649 hr_utility.set_location(' Leaving:'||l_proc,5);
650 --
651 end chk_country;
652 --
653 -- ---------------------------------------------------------------------------
654 -- |------------------------< chk_date_to >---------------------------------|
655 -- ---------------------------------------------------------------------------
656 --
657 -- Description:
658 -- Validates that date to (may be null) is greater than or equal to date
659 -- from.
660 --
661 -- Pre-conditions:
662 -- Format of p_date_from and p_date_to must be correct.
663 --
664 -- In Arguments:
665 -- p_address_id
666 -- p_date_from
667 -- p_date_to
668 -- p_object_version_number
669 --
670 -- Post Success:
671 -- If a given date to is greater than or equal to a given date from then
672 -- processing continues.
673 --
674 -- Post Failure:
675 -- If a given date to is not greater than or equal to a given date from then
676 -- an application error will be raised and processing is terminated.
677 --
678 -- Access status:
679 -- Internal Table Handler Use Only.
680 --
681 -- {End Of Comments}
682 -- ----------------------------------------------------------------------------
683 procedure chk_date_to
684 (p_address_id in per_addresses.address_id%TYPE
685 ,p_date_from in per_addresses.date_from%TYPE
686 ,p_date_to in per_addresses.date_to%TYPE
687 ,p_object_version_number in per_addresses.object_version_number%TYPE)
688 is
689 --
690 l_exists varchar2(1);
691 l_proc varchar2(72) := g_package||'chk_date_to';
692 l_date_to date;
693 l_api_updating boolean;
694 --
695 begin
696 hr_utility.set_location('Entering:'|| l_proc, 1);
697 --
698 -- Check mandatory parameters have been set
699 --
700 hr_api.mandatory_arg_error
701 (p_api_name => l_proc
702 ,p_argument => 'date_from'
703 ,p_argument_value => p_date_from
704 );
705 if hr_multi_message.no_all_inclusive_error
706 (p_check_column1 => 'PER_ADDRESSES.DATE_FROM'
707 ,p_check_column2 => 'PER_ADDRESSES.DATE_TO'
708 ,p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
709 ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
710 ) then
711 --
712 -- Only proceed with validation if :
713 -- a) The current g_old_rec is current and
714 -- b) The value for date to has changed
715 --
716 l_api_updating := per_add_shd.api_updating
717 (p_address_id => p_address_id
718 ,p_object_version_number => p_object_version_number);
719 --
720 if ((l_api_updating and
721 nvl(per_add_shd.g_old_rec.date_to, hr_api.g_eot) <>
722 nvl(p_date_to, hr_api.g_eot)) or
723 (NOT l_api_updating)) then
724 --
725 hr_utility.set_location(l_proc, 2);
726 --
727 -- Checks that the value for date_to is greater than or
728 -- equal to the corresponding value for date_from for the
729 -- same record
730 --
731 if nvl(p_date_to, hr_api.g_eot) < p_date_from then
732 hr_utility.set_message(801, 'HR_7301_ADD_DATE_TO_LATER');
733 hr_utility.raise_error;
734 end if;
735 --
736 end if;
737 --
738 end if;
739 hr_utility.set_location(' Leaving:'|| l_proc, 3);
740 exception
741 when app_exception.application_exception then
742 if hr_multi_message.exception_add
743 (p_same_associated_columns => 'Y'
744 ) then
745 hr_utility.set_location(' Leaving:'||l_proc,4);
746 raise;
747 end if;
748 hr_utility.set_location(' Leaving:'||l_proc,5);
749 --
750 end chk_date_to;
751 --
752 -- ---------------------------------------------------------------------------
753 -- |------------------------< chk_date_from >-------------------------------|
754 -- ---------------------------------------------------------------------------
755 --
756 -- Description:
757 -- Validates that date from is less than or equal to date to (may be null).
758 --
759 -- Pre-conditions:
760 -- Format of p_date_from and p_date_to must be correct.
761 --
762 -- In Arguments:
763 -- p_address_id
764 -- p_date_from
765 -- p_date_to
766 -- p_object_version_number
767 --
768 -- Post Success:
769 -- If a given date from is less than or equal to a given date to then
770 -- processing continues.
771 --
772 -- Post Failure:
773 -- If a given date from is not less than or equal to a given date to then
774 -- an application error will be raised and processing is terminated.
775 --
776 -- Access Status:
777 -- Internal Table Handler Use Only.
778 --
779 -- {End Of Comments}
780 -- ----------------------------------------------------------------------------
781 procedure chk_date_from
782 (p_address_id in per_addresses.address_id%TYPE
783 ,p_date_from in per_addresses.date_from%TYPE
784 ,p_date_to in per_addresses.date_to%TYPE
785 ,p_object_version_number in per_addresses.object_version_number%TYPE)
786 is
787 --
788 l_exists varchar2(1);
789 l_proc varchar2(72) := g_package||'chk_date_from';
790 l_api_updating boolean;
791 --
792 begin
793 hr_utility.set_location('Entering:'|| l_proc, 1);
794 --
795 -- Check mandatory parameters have been set
796 --
797 hr_api.mandatory_arg_error
798 (p_api_name => l_proc
799 ,p_argument => 'date_from'
800 ,p_argument_value => p_date_from
801 );
802 --
803 -- Only proceed with validation if :
804 -- a) The current g_old_rec is current and
805 -- b) The date_from value has changed
806 --
807 l_api_updating := per_add_shd.api_updating
808 (p_address_id => p_address_id
809 ,p_object_version_number => p_object_version_number);
810 --
811 if ((l_api_updating and per_add_shd.g_old_rec.date_from <> p_date_from) or
812 (NOT l_api_updating)) then
813 hr_utility.set_location(l_proc, 2);
814 --
815 -- Check that the date_from values is less than
816 -- or equal to the date_to value for the current
817 -- record
818 --
819 if p_date_from > nvl(p_date_to, hr_api.g_eot) then
820 hr_utility.set_message(801, 'HR_7303_ADD_DATE_FROM_EARLIER');
821 hr_utility.raise_error;
822 end if;
823 --
824 end if;
825 --
826 hr_utility.set_location(' Leaving:'|| l_proc, 3);
827 exception
828 when app_exception.application_exception then
829 if hr_multi_message.exception_add
830 (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
831 ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
832 ) then
833 hr_utility.set_location(' Leaving:'||l_proc,4);
834 raise;
835 end if;
836 hr_utility.set_location(' Leaving:'||l_proc,5);
837 --
838 end chk_date_from;
839 --
840 -- ---------------------------------------------------------------------------
841 -- |--------------------------< chk_date_comb >------------------------------|
842 -- ---------------------------------------------------------------------------
843 --
844 -- Description:
845 --
846 -- Validates date_to/date_from for a primary address so that it
847 -- does not overlap with the date range of another primary address.
848 --
849 -- Validates that the date range of a non-primary co-exists with the
850 -- date range of a primary address.
851 --
852 -- Validate that primary addresses are contiguous.
853 --
854 -- Validates that the address_type for an address (primary or non)
855 -- is unique for a person with the given date range.
856 --
857 -- Pre-conditions:
858 -- Format of p_date_from and p_date_to must be correct.
859 --
860 -- In Arguments:
861 -- p_address_id
862 -- p_address_type
863 -- p_primary_flag
864 -- p_date_from
865 -- p_date_to
866 -- p_person_id
867 -- p_object_version_number
868 --
869 -- Post Success:
870 -- If no overlaps occur with either the address_type or primary flag then
871 -- processing continues.
872 --
873 -- If all non-primary addresses exist during the date range of one or
874 -- more contiguous primary addresses then processing continues.
875 --
876 -- If all primary addresses are contiguous then processing continues.
877 --
878 -- Post Failure:
879 -- If the date_to/date_from values cause a primary address to overlap
880 -- within the date range of another primary address for the same person,
881 -- or the address_type for either a primary or non-primary address is
882 -- not uniques within a given date range for a person then an application
883 -- error is raised and processing is terminated.
884 --
885 -- If an insert/update of a non-primary address is atempted where the
886 -- date range of the non-primary address does not co-exist with that of
887 -- a primary address then an application error is raised and processing
888 -- is terminated.
889 --
890 -- If an insert/update of a primary address causes the primary address
891 -- pattern to be non-contiguous then an application error is raised and
892 -- processing is terminated.
893 --
894 -- Access status:
895 -- Internal Table Handler Use Only.
896 --
897 -- {End Of Comments}
898 -- ----------------------------------------------------------------------------
899 procedure chk_date_comb
900 (p_address_id in per_addresses.address_id%TYPE
901 ,p_address_type in per_addresses.address_type%TYPE
902 ,p_date_from in per_addresses.date_from%TYPE
903 ,p_date_to in per_addresses.date_to%TYPE
904 ,p_person_id in per_addresses.person_id%TYPE
905 ,p_primary_flag in per_addresses.primary_flag%TYPE
906 ,p_object_version_number in per_addresses.object_version_number%TYPE
907 ,p_prflagval_override in boolean default false
908 ,p_party_id in per_addresses.party_id%TYPE -- HR/TCA merge
909 )
910 is
911 --
912 l_proc varchar2(72) := g_package||'chk_date_comb';
913 --
914 l_exists varchar2(1);
915 l_date_to date;
916 c_date_from date;
917 c_date_to date;
918 l_no_other_recs boolean := FALSE;
919 l_recs_before boolean := FALSE;
920 l_recs_after boolean := FALSE;
921 l_good_recs number := 0;
922 l_api_updating boolean;
923 l_action_eff_date date;
924 l_address_count number;
925
926 cursor csr_assignments(p_person_id in number) is
927 select assignment_id
928 from per_all_assignments_f
929 where person_id = p_person_id;
930 --
931 cursor csr_pay_roll_actions(p_assignment_id in number) is
932 select payroll_action_id
933 from pay_assignment_actions
934 where assignment_id = p_assignment_id;
935 --
936 cursor csr_dup_add_type_exists is
937 select null
938 from per_addresses pa
939 where p_date_from <= nvl(pa.date_to, hr_api.g_eot)
940 and l_date_to >= pa.date_from
941 and pa.address_type = p_address_type
942 and (pa.person_id = p_person_id OR -- HR/TCA merge
943 (pa.party_id = p_party_id and p_person_id is null)) -- #3406505
944 and (p_address_id is null
945 or (p_address_id is not null
946 and pa.address_id <> p_address_id));
947 --
948 cursor csr_dup_prim_flag is
949 select null
950 from per_addresses pa
951 where p_date_from <= nvl(pa.date_to, hr_api.g_eot)
952 and l_date_to >= pa.date_from
953 and pa.primary_flag = 'Y'
954 and (pa.person_id = p_person_id OR --
955 (pa.party_id = p_party_id and p_person_id is null)) -- HR/TCA merge -- #3406505
956 and (p_address_id is null
957 or (p_address_id is not null
958 and pa.address_id <> p_address_id));
959 --
960 cursor csr_no_primary is
961 select null
962 from per_addresses pa
963 where p_date_from >= pa.date_from
964 and exists (select null
965 from per_addresses pa2
966 where nvl(pa2.date_to, hr_api.g_eot) >= l_date_to
967 and (pa2.person_id = p_person_id OR --
968 (pa2.party_id = p_party_id and p_person_id is null)) -- HR/TCA merge -- #3406505
969 and pa2.primary_flag = 'Y')
970 and pa.primary_flag = 'Y'
971 and (pa.person_id = p_person_id OR -- HR/TCA merge
972 (pa.party_id = p_party_id and p_person_id is null)); --#3406505
973 --
974 -- Bug 2933498 starts here.
975 -- Modified the cursor csr_invalid_non_prim.
976 cursor csr_invalid_non_prim is
977 select null
978 from sys.dual
979 where exists(select null
980 from per_addresses pa
981 where ((pa.date_from < p_date_from
982 and nvl(pa.date_to, hr_api.g_eot) >=
983 (select date_from
984 from per_addresses
985 where address_id = p_address_id)
986 and p_date_from <> (select date_from
987 from per_addresses
988 where address_id = p_address_id) )
989 or (nvl(pa.date_to, hr_api.g_eot) >
990 nvl(p_date_to, hr_api.g_eot)
991 and pa.date_from <=(select nvl(date_to, hr_api.g_eot)
992 from per_addresses
993 where address_id = p_address_id)
994 and nvl(p_date_to, hr_api.g_eot) <>
995 (select nvl(date_to, hr_api.g_eot)
996 from per_addresses
997 where address_id = p_address_id) ))
998 and pa.primary_flag = 'N'
999 and (pa.person_id = p_person_id OR
1000 (pa.party_id = p_party_id and p_person_id is null)));-- HR/TCA merge --#3406505
1001 -- Bug 2933498 ends here.
1002 --
1003 cursor csr_check_other_addresses is
1004 select null
1005 from sys.dual
1006 where exists(select null
1007 from per_addresses pa
1008 where (pa.person_id = p_person_id OR
1009 (pa.party_id = p_party_id and p_person_id is null)) -- HR/TCA merge --#3406505
1010 and pa.primary_flag = 'Y'
1011 and (p_address_id is null
1012 or (p_address_id is not null
1013 and p_address_id <> pa.address_id)));
1014 --
1015 cursor csr_chk_contig_add_before is
1016 select pa.date_from,
1017 pa.date_to
1018 from per_addresses pa
1019 where (pa.person_id = p_person_id OR
1020 (pa.party_id = p_party_id and p_person_id is null)) -- HR/TCA merge --#3406505
1021 and pa.primary_flag = 'Y'
1022 and pa.date_to < p_date_from
1023 and (p_address_id is null
1024 or (p_address_id is not null
1025 and p_address_id <> pa.address_id));
1026 --
1027 cursor csr_chk_contig_add_after is
1028 select pa.date_from, pa.date_to
1029 from per_addresses pa
1030 where (pa.person_id = p_person_id OR
1031 (pa.party_id = p_party_id and p_person_id is null)) -- HR/TCA merge --#3406505
1032 and pa.primary_flag = 'Y'
1033 and pa.date_from > p_date_to
1034 and (p_address_id is null
1035 or (p_address_id is not null
1036 and p_address_id <> pa.address_id));
1037 --
1038 begin
1039 hr_utility.set_location('Entering:'|| l_proc, 10);
1040 --
1041 -- Check mandatory parameters have been set
1042 --
1043 hr_api.mandatory_arg_error
1044 (p_api_name => l_proc
1045 ,p_argument => 'date_from'
1046 ,p_argument_value => p_date_from
1047 );
1048 --
1049 if p_party_id is null then -- HR/TCA merge
1050 hr_api.mandatory_arg_error
1051 (p_api_name => l_proc
1052 ,p_argument => 'person_id'
1053 ,p_argument_value => p_person_id
1054 );
1055 end if;
1056 --
1057 hr_api.mandatory_arg_error
1058 (p_api_name => l_proc
1059 ,p_argument => 'primary_flag'
1060 ,p_argument_value => p_primary_flag
1061 );
1062 hr_utility.set_location(l_proc, 20);
1063 if hr_multi_message.no_all_inclusive_error
1064 (p_check_column1 => 'PER_ADDRESSES.DATE_FROM'
1065 ,p_check_column2 => 'PER_ADDRESSES.DATE_TO'
1066 ,p_check_column3 => 'PER_ADDRESSES.PERSON_ID'
1067 ,p_check_column4 => 'PER_ADDRESSES.PARTY_ID'
1068 ) then
1069 --
1070 -- Set the DATE_TO to entered value or the end of time
1071 --
1072 l_date_to := nvl(p_date_to, hr_api.g_eot);
1073 --
1074 -- Only proceed with validation if :
1075 -- a) The current g_old_rec is current and
1076 -- b) The value for date_to/date_from has changed
1077 --
1078 l_api_updating := per_add_shd.api_updating
1079 (p_address_id => p_address_id
1080 ,p_object_version_number => p_object_version_number
1081 );
1082 --
1083 if ((l_api_updating and
1084 per_add_shd.g_old_rec.date_from <> p_date_from) or
1085 (nvl(per_add_shd.g_old_rec.date_to, hr_api.g_eot) <>
1086 nvl(p_date_to, hr_api.g_eot)) or
1087 (nvl(per_add_shd.g_old_rec.address_type, hr_api.g_varchar2) <>
1088 nvl(p_address_type, hr_api.g_varchar2))
1089 or
1090 (NOT l_api_updating))
1091 then
1092 hr_utility.set_location(l_proc, 30);
1093 --
1094 -- For all addresses
1095 -- =================
1096 -- Checks that the date_from, date_to values for a given address
1097 -- do not cause an overlap of address_type value between two
1098 -- addresses for the same person within a given date range.
1099 --
1100 if (nvl(per_add_shd.g_old_rec.address_type, hr_api.g_varchar2) <>
1101 nvl(p_address_type, hr_api.g_varchar2)) or
1102 (NOT l_api_updating) then
1103 --
1104 if p_address_type is not null then
1105 open csr_dup_add_type_exists;
1106 fetch csr_dup_add_type_exists into l_exists;
1107 if csr_dup_add_type_exists%found then
1108 close csr_dup_add_type_exists;
1109 hr_utility.set_message(801, 'HR_51139_ADD_TYPE_ALR_EXIST');
1110 hr_multi_message.add
1111 (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1112 ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1113 ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1114 ,p_associated_column4 => 'PER_ADDRESSES.PARTY_ID'
1115 ,p_associated_column5 => 'PER_ADDRESSES.ADDRESS_TYPE'
1116 );
1117 else
1118 --
1119 close csr_dup_add_type_exists;
1120 --
1121 end if;
1122 end if;
1123 end if;
1124 --
1125 hr_utility.set_location(l_proc, 4);
1126 --
1127 -- For primary addresses only
1128 -- ==========================
1129 --
1130 --
1131 if p_primary_flag = 'Y' then
1132 --
1133 -- Check if the primary flag check is to be overriden
1134 --
1135 if not p_prflagval_override then
1136 --
1137 -- Checks that the date_from, date_to values for a given address
1138 -- do not cause an an overlap between two primary
1139 -- addresses for the same person within a given date range.
1140 --
1141 open csr_dup_prim_flag;
1142 fetch csr_dup_prim_flag into l_exists;
1143 if csr_dup_prim_flag%found then
1144 close csr_dup_prim_flag;
1145 hr_utility.set_message(801, 'HR_7327_ADD_PRIMARY_ADD_EXISTS');
1146 hr_multi_message.add
1147 (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1148 ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1149 ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1150 ,p_associated_column4 => 'PER_ADDRESSES.PARTY_ID'
1151 );
1152 else
1153 --
1154 close csr_dup_prim_flag;
1155 --
1156 end if;
1157 --
1158 end if;
1159 --
1160 --
1161 -- Verify that the primary address does not break
1162 -- the contiguous nature of the primary address
1163 --
1164 -- Firstly check whether any other addresses exist
1165 -- for a person
1166 --
1167 open csr_check_other_addresses;
1168 fetch csr_check_other_addresses into l_exists;
1169 if csr_check_other_addresses%found then
1170 --
1171 -- Check addresses before
1172 --
1173 close csr_check_other_addresses;
1174 open csr_chk_contig_add_before;
1175 loop
1176 fetch csr_chk_contig_add_before into c_date_from, c_date_to;
1177 exit when csr_chk_contig_add_before%notfound;
1178 l_recs_before := TRUE;
1179 if c_date_to = p_date_from-1 then
1180 l_good_recs := l_good_recs + 1;
1181 end if;
1182 end loop;
1183 close csr_chk_contig_add_before;
1184 --
1185 -- Check addresses after
1186 --
1187 open csr_chk_contig_add_after;
1188 loop
1189 fetch csr_chk_contig_add_after into c_date_from, c_date_to;
1190 exit when csr_chk_contig_add_after%notfound;
1191 l_recs_after := TRUE;
1192 if c_date_from = p_date_to+1 then
1193 l_good_recs := l_good_recs + 1;
1194 end if;
1195 end loop;
1196 close csr_chk_contig_add_after;
1197 --
1198 else
1199 close csr_check_other_addresses;
1200 l_no_other_recs := TRUE;
1201 end if;
1202 --
1203 -- Check for contiguity errors
1204 --
1205 if not l_no_other_recs then
1206 if ((l_good_recs = 1
1207 and l_recs_before
1208 and l_recs_after)
1209 or
1210 (l_good_recs < 1)) then
1211 hr_utility.set_message(801, 'HR_51030_ADDR_PRIM_GAP');
1212 hr_multi_message.add
1213 (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1214 ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1215 ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1216 ,p_associated_column4 => 'PER_ADDRESSES.PARTY_ID'
1217 );
1218 end if;
1219 end if;
1220 --
1221 -- Check if the primary flag check is to be overriden
1222 --
1223 if not p_prflagval_override then
1224 --
1225 -- Check that on UPDATE of date values
1226 -- for a Primary address that no Non-primary
1227 -- address is left without a corresponding
1228 -- primary
1229 --
1230 if ((per_add_shd.g_old_rec.date_from <> p_date_from) or
1231 (nvl(per_add_shd.g_old_rec.date_to, hr_api.g_eot) <>
1232 (nvl(p_date_to, hr_api.g_eot)))) and
1233 p_address_id is not null then
1234 open csr_invalid_non_prim;
1235 fetch csr_invalid_non_prim into l_exists;
1236 if csr_invalid_non_prim%found then
1237 close csr_invalid_non_prim;
1238 hr_utility.set_message(801, 'HR_7302_ADD_PRIMARY_DATES');
1239 hr_multi_message.add
1240 (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1241 ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1242 ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1243 ,p_associated_column4 => 'PER_ADDRESSES.PARTY_ID'
1244 );
1245 else
1246 --
1247 close csr_invalid_non_prim;
1248 --
1249 end if;
1250 --
1251 end if;
1252 --
1253 end if;
1254 --
1255 else -- if PRIMARY_FLAG = 'N'
1256 --
1257 -- For non-primary addresses only
1258 -- ==============================
1259 -- Checks that a primary address must
1260 -- exist during the date range of a
1261 -- non-primary address
1262 --
1263 open csr_no_primary;
1264 fetch csr_no_primary into l_exists;
1265 if csr_no_primary%notfound then
1266 close csr_no_primary;
1267 hr_utility.set_message(801, 'HR_7302_ADD_PRIMARY_DATES');
1268 hr_multi_message.add
1269 (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1270 ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1271 ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1272 ,p_associated_column4 => 'PER_ADDRESSES.PARTY_ID'
1273 );
1274 else
1275 --
1276 close csr_no_primary;
1277 --
1278 end if;
1279 --
1280 end if;
1281 --
1282 end if;
1283 --
1284 if l_api_updating and p_primary_flag = 'Y' then
1285 for assCursor in csr_assignments(p_person_id) loop
1286 for actionCursor in csr_pay_roll_actions(assCursor.assignment_id) loop
1287 select effective_date
1288 into l_action_eff_date
1289 from pay_payroll_actions
1290 where payroll_action_id = actionCursor.payroll_action_id;
1291
1292 if p_date_from > l_action_eff_date
1293 then
1294 select count(*)
1295 into l_address_count
1296 from per_addresses
1297 where person_id = p_person_id
1298 and address_id <> p_address_id
1299 and l_action_eff_date between date_from
1300 and nvl(date_to,l_action_eff_date);
1301 if l_address_count = 0 then
1302 hr_utility.set_message(800, 'PER_PAYROLL_EXISTS');
1303 hr_multi_message.add();
1304 end if;
1305 end if;
1306 end loop;
1307 end loop;
1308 end if;
1309 end if;
1310 --
1311 hr_utility.set_location(' Leaving:'|| l_proc, 5);
1312 end chk_date_comb;
1313 --
1314 -- ---------------------------------------------------------------------------
1315 -- |-----------------------------< chk_style >------------------------------|
1316 -- ---------------------------------------------------------------------------
1317 --
1318 -- Description:
1319 -- Validates:
1320 -- - a flex structure exists for a given style.
1321 --
1322 -- Pre-conditions:
1323 -- None
1324 --
1325 -- In Arguments:
1326 -- p_style
1327 --
1328 -- Post Success:
1329 -- Processing continues if:
1330 -- - a flex structure does exist in fnd_descr_flex_contexts for the given
1331 -- territory code.
1332 --
1333 -- Post Failure:
1334 -- An application error is raised and processing terminates if:
1335 -- - a flex structure does not exist in fnd_descr_flex_contexts for the
1336 -- given territory code.
1337 --
1338 -- Access Status:
1339 -- Internal Table Handler Use Only.
1340 --
1341 -- {End Of Comments}
1342 -- ----------------------------------------------------------------------------
1343 procedure chk_style
1344 (p_style in varchar2)
1345 is
1346 --
1347 l_exists varchar2(1);
1348 l_token varchar2(20);
1349 l_error exception;
1350 l_proc varchar2(72) := g_package||'chk_style';
1351 --
1352 --
1353 -- 70.2 change c start.
1354 --
1355 cursor csr_valid_flex_struc is
1356 select null
1357 from fnd_descr_flex_contexts
1358 where descriptive_flexfield_name = 'Address Structure'
1359 and descriptive_flex_context_code = p_style
1360 and enabled_flag = 'Y'
1361 and application_id = 800;
1362 --
1363 -- 70.2 change c end.
1364 --
1365 --
1366 begin
1367 hr_utility.set_location('Entering:'|| l_proc, 1);
1368 --
1369 -- Check mandatory parameters have been set
1370 --
1371 hr_api.mandatory_arg_error
1372 (p_api_name => l_proc
1373 ,p_argument => 'style'
1374 ,p_argument_value => p_style
1375 );
1376 hr_utility.set_location(l_proc, 2);
1377 --
1378 -- Checks that the flex structure for the style
1379 -- selected exists in fnd_descr_flex_contents
1380 --
1381 open csr_valid_flex_struc;
1382 fetch csr_valid_flex_struc into l_exists;
1383 if csr_valid_flex_struc%notfound then
1384 close csr_valid_flex_struc;
1385 hr_utility.set_message(801, 'HR_7304_ADD_NO_FORMAT');
1386 hr_utility.raise_error;
1387 end if;
1388 close csr_valid_flex_struc;
1389 hr_utility.set_location(l_proc, 3);
1390 --
1391 hr_utility.set_location(' Leaving:'|| l_proc, 4);
1392 exception
1393 when app_exception.application_exception then
1394 if hr_multi_message.exception_add
1395 (p_associated_column1 => 'PER_ADDRESSES.STYLE'
1396 ) then
1397 hr_utility.set_location(' Leaving:'||l_proc,5);
1398 raise;
1399 end if;
1400 -- Call to raise any errors on multi-message list
1401 -- Taking STYLE as an important parameter.
1402 hr_multi_message.end_validation_set;
1403 hr_utility.set_location(' Leaving:'||l_proc,6);
1404 --
1405 end chk_style;
1406 --
1407 -- ---------------------------------------------------------------------------
1408 -- |----------------------< chk_style_null_attr >---------------------------|
1409 -- ---------------------------------------------------------------------------
1410 --
1411 -- Description:
1412 -- Validates the columns that should not be populated if address style is
1413 -- 'GB', 'US' or 'GENERIC'.
1414 --
1415 -- Pre-conditions:
1416 -- Style (p_style) must be valid.
1417 --
1418 -- In Arguments:
1419 -- p_style
1420 -- p_region_2
1421 -- p_region_3
1422 -- p_telephone_number_3
1423 --
1424 -- Post Success:
1425 -- If the style structure meets the 'GB', 'US' and 'GENERIC'
1426 -- requirements (in terms of column usage) then processing continues.
1427 --
1428 -- Post Failure:
1429 -- If the style structure does not meet the 'GB', 'US' and 'GENERIC'
1430 -- requirements (in terms of column usage) then an application error is
1431 -- raised and processing terminates.
1432 --
1433 -- Access Status:
1434 -- Internal Table Handler Use Only.
1435 --
1436 -- {End Of Comments}
1437 -- ----------------------------------------------------------------------------
1438
1439 procedure chk_style_null_attr
1440 (p_address_id in number
1441 ,p_object_version_number in number
1442 ,p_style in varchar2
1443 ,p_region_2 in varchar2
1444 ,p_region_3 in varchar2
1445 ,p_telephone_number_3 in varchar2
1446 )
1447 is
1448 --
1449 l_token varchar2(20);
1450 l_error exception;
1451 l_api_updating boolean;
1452 l_proc varchar2(72) := g_package||'chk_style_null_attr';
1453 --
1454 begin
1455 hr_utility.set_location('Entering:'|| l_proc, 1);
1456 --
1457 -- Check 'GB' address style
1458 --
1459 if p_style = 'GB' then
1460 if p_region_2 is not null then
1461 if hr_multi_message.no_exclusive_error
1462 (p_check_column1 => 'PER_ADDRESSES.REGION_2') then
1463 --
1464 l_token := 'region_2';
1465 raise l_error;
1466 --
1467 end if;
1468 elsif p_region_3 is not null then
1469 l_token := 'region_3';
1470 raise l_error;
1471 elsif p_telephone_number_3 is not null then
1472 l_token := 'telephone_number_3';
1473 raise l_error;
1474 end if;
1475 --
1476 hr_utility.set_location(l_proc, 2);
1477 --
1478 -- Check 'US' address style
1479 --
1480 elsif p_style = 'US' then
1481 --
1482 -- Check if region 3 is set but is unchanged on update
1483 --
1484 l_api_updating := per_add_shd.api_updating
1485 (p_address_id => p_address_id
1486 ,p_object_version_number => p_object_version_number
1487 );
1488 --
1489 if p_region_3 is not null
1490 and nvl(per_add_shd.g_old_rec.region_3, hr_api.g_varchar2)
1491 = nvl(p_region_3, hr_api.g_varchar2)
1492 then
1493 --
1494 null;
1495 --
1496 elsif p_region_3 is not null then
1497 --
1498 l_token := 'region_3';
1499 raise l_error;
1500 --
1501 end if;
1502 --
1503 -- Check if telephone number 3 is set but is unchanged on update
1504 --
1505 if p_telephone_number_3 is not null
1506 and nvl(per_add_shd.g_old_rec.telephone_number_3, hr_api.g_varchar2)
1507 = nvl(p_telephone_number_3, hr_api.g_varchar2)
1508 then
1509 --
1510 null;
1511 --
1512 elsif p_telephone_number_3 is not null then
1513 --
1514 l_token := 'telephone_number_3';
1515 raise l_error;
1516 --
1517 end if;
1518 --
1519 hr_utility.set_location(l_proc, 3);
1520 --
1521 -- Check 'GENERIC' address style
1522 --
1523 elsif p_style = 'GENERIC' then
1524 if p_telephone_number_3 is not null then
1525 l_token := 'telephone_number_3';
1526 raise l_error;
1527 end if;
1528 end if;
1529 --
1530 exception
1531 when l_error then
1532 hr_utility.set_message(801, 'HR_7324_ADD_ADD_ATTRIBUTE_NULL');
1533 hr_utility.set_message_token('ARGUMENT', l_token);
1534 hr_multi_message.add(
1535 p_associated_column1 =>
1536 (per_add_shd.g_tab_nam || '.' || upper(l_token))
1537 );
1538 when others then
1539 raise;
1540 --
1541 hr_utility.set_location(' Leaving:'|| l_proc, 4);
1542 end chk_style_null_attr;
1543 --
1544 -- ---------------------------------------------------------------------------
1545 -- |------------------------< chk_address_line1 >----------------------------|
1546 -- ---------------------------------------------------------------------------
1547 --
1548 -- Description:
1549 -- Validates that address line 1 is not null from US and GB styles.
1550 --
1551 -- Pre-conditions:
1552 -- Style (p_style) must be valid.
1553 --
1554 -- In Arguments:
1555 -- p_address_id
1556 -- p_style
1557 -- p_address_line1
1558 -- p_object_version_number
1559 --
1560 -- Post Success:
1561 -- If address style is 'US' or 'GB' and address line 1 is not null,
1562 -- processing continues.
1563 --
1564 -- Post Failure:
1565 -- If address style is 'US' or 'GB' and address line 1 is null, an
1566 -- application error is raised and processing terminates.
1567 --
1568 -- Access Status:
1569 -- Internal Table Handler Use Only.
1570 --
1571 -- {End Of Comments}
1572 -- ----------------------------------------------------------------------------
1573 procedure chk_address_line1
1574 (p_address_id in per_addresses.address_id%TYPE
1575 ,p_style in per_addresses.style%TYPE
1576 ,p_address_line1 in per_addresses.region_2%TYPE
1577 ,p_object_version_number in per_addresses.object_version_number%TYPE)
1578 is
1579 --
1580 l_exists varchar2(1);
1581 l_proc varchar2(72) := g_package||'chk_address_line1';
1582 l_api_updating boolean;
1583
1584 begin
1585 hr_utility.set_location('Entering:'|| l_proc, 1);
1586 --
1587 -- Check mandatory parameters have been set
1588 --
1589 hr_api.mandatory_arg_error
1590 (p_api_name => l_proc
1591 ,p_argument => 'style'
1592 ,p_argument_value => p_style
1593 );
1594 --
1595 -- Only proceed with validation if :
1596 -- a) The current g_old_rec is current and
1597 -- b) The value for address_line1 has changed
1598 --
1599 l_api_updating := per_add_shd.api_updating
1600 (p_address_id => p_address_id
1601 ,p_object_version_number => p_object_version_number);
1602 --
1603 if ((l_api_updating and
1604 nvl(per_add_shd.g_old_rec.address_line1, hr_api.g_varchar2) <>
1605 nvl(p_address_line1, hr_api.g_varchar2)) or
1606 (NOT l_api_updating)) then
1607 hr_utility.set_location(l_proc, 2);
1608 --
1609 -- Check that value for address_line1 is not null for 'US' and 'GB'
1610 -- style.
1611 --
1612 if p_style = 'GB' or
1613 p_style = 'US' then
1614 --
1615 hr_utility.set_location(l_proc, 3);
1616 --
1617 if p_address_line1 is null then
1618 --
1619 hr_utility.set_message(801, 'HR_51233_ADD_ADD_LINE1_REQ');
1620 hr_utility.raise_error;
1621 end if;
1622 --
1623 end if;
1624 --
1625 end if;
1626 --
1627 hr_utility.set_location(' Leaving:'|| l_proc, 5);
1628 exception
1629 when app_exception.application_exception then
1630 if hr_multi_message.exception_add
1631 (p_associated_column1 => 'PER_ADDRESSES.ADDRESS_LINE1'
1632 ) then
1633 hr_utility.set_location(' Leaving:'||l_proc,6);
1634 raise;
1635 end if;
1636 hr_utility.set_location(' Leaving:'||l_proc,7);
1637 --
1638 end chk_address_line1;
1639 --
1640 -- ----------------------------------------------------------------------------
1641 -- |------------------------<chk_address1_towncity_comb >--------------------|
1642 -- ----------------------------------------------------------------------------
1643 /* Bug 1677965
1644 procedure chk_address1_towncity_comb(
1645 p_business_group_id in number,
1646 p_address_id in per_addresses.address_id%TYPE,
1647 p_object_version_number in per_addresses.object_version_number%TYPE,
1648 p_town_or_city in out nocopy per_addresses.town_or_city%type,
1649 p_address_line1 in out nocopy per_addresses.address_line1%type,
1650 p_region_1 in out nocopy per_addresses.region_1%type) is
1651
1652 -- p_town_or_city ===> district_code
1653 -- p_address_line1 ===> address_line1
1654 -- p_region_1 ===> address_line1_kana
1655
1656 l_legislation_code per_business_groups.legislation_code%TYPE;
1657 l_town_or_city per_addresses.town_or_city%type;
1658 l_address_line1 per_addresses.address_line1%type;
1659 l_region_1 per_addresses.region_1%type;
1660 l_sql_cursor integer; -- Dynamic sql cursor
1661 l_dynamic_sql varchar2(2000); -- Dynamic sql text
1662 l_rows integer; -- No of rows returned
1663
1664 l_api_updating boolean;
1665 l_proc varchar2(72) := g_package||'chk_address1_towncity_comb';
1666
1667 cursor csr_bg is
1668 select legislation_code
1669 from per_business_groups pbg
1670 where pbg.business_group_id = p_business_group_id;
1671
1672 begin
1673 hr_utility.set_location('Entering:'|| l_proc, 1);
1674 -- Bug 885806
1675 -- dbms_output.put_line('Top of dynamic sql . . .');
1676 hr_utility.trace('Top of dynamic sql . . .');
1677 open csr_bg;
1678 fetch csr_bg into l_legislation_code;
1679
1680 -- Only proceed with validation if :
1681 -- a) The current g_old_rec is current and
1682 -- b) The value for town_or_city, address_line1, or region_1 have changed
1683 --
1684 l_api_updating := per_add_shd.api_updating
1685 (p_address_id => p_address_id
1686 ,p_object_version_number => p_object_version_number);
1687
1688 if ( (l_legislation_code = 'JP')
1689 and
1690 ( (l_api_updating and
1691 (nvl(per_add_shd.g_old_rec.town_or_city, hr_api.g_varchar2) <>
1692 nvl(p_town_or_city, hr_api.g_varchar2) or
1693 nvl(per_add_shd.g_old_rec.address_line1, hr_api.g_varchar2)<>
1694 nvl(p_address_line1, hr_api.g_varchar2) or
1695 nvl(per_add_shd.g_old_rec.region_1, hr_api.g_varchar2) <>
1696 nvl(p_region_1, hr_api.g_varchar2)))
1697 or
1698 (NOT l_api_updating))
1699 ) then
1700
1701 hr_utility.set_location(l_proc, 2);
1702 --
1703 -- p_town_or_city(district_code) is not null
1704 --
1705 if p_town_or_city is not NULL then
1706 hr_utility.set_location(l_proc, 3);
1707
1708 l_dynamic_sql := 'select t.address_line_1, ' ||
1709 ' t.address_line_1_kana ' ||
1710 'from per_jp_address_lookups t ' ||
1711 'where t.district_code = :p_town_or_city';
1712
1713 --
1714 -- Dynamic sql steps:
1715 -- ==================
1716 -- 1. Open dynamic sql cursor
1717 -- 2. Parse dynamic sql
1718 -- 3. Bind dynamic sql variables
1719 -- 4. Define dynamic sql columns
1720 -- 5. Execute and fetch dynamic sql
1721 --
1722 Hr_Utility.Set_Location(l_proc, 6);
1723 l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1724 --
1725 Hr_Utility.Set_Location(l_proc, 10);
1726 dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1727 --
1728 Hr_Utility.Set_Location(l_proc, 15);
1729 dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1730 ':p_town_or_city', p_town_or_city);
1731
1732 Hr_Utility.Set_Location(l_proc, 20);
1733 dbms_sql.define_column(l_sql_cursor, 1, l_address_line1, 60); -- Step 4
1734 dbms_sql.define_column(l_sql_cursor, 2, l_region_1, 70);
1735 --
1736 Hr_Utility.Set_Location(l_proc, 30);
1737 l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
1738
1739 if l_rows = 0 then
1740 dbms_sql.close_cursor(l_sql_cursor);
1741 hr_utility.set_message(801, 'HR_72028_ADD_INVALID_DIST_CODE');
1742 hr_utility.raise_error;
1743
1744 elsif l_rows = 1 then
1745 Hr_Utility.Set_Location(l_proc, 35);
1746 dbms_sql.column_value(l_sql_cursor, 1, l_address_line1);
1747 if p_address_line1 is not null and
1748 p_address_line1 <> l_address_line1 then
1749
1750 dbms_sql.close_cursor(l_sql_cursor);
1751 hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1752 hr_utility.raise_error;
1753 end if;
1754 p_address_line1 := l_address_line1;
1755
1756 else
1757 dbms_sql.close_cursor(l_sql_cursor);
1758 hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1759 hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1760 hr_utility.raise_error;
1761 end if;
1762
1763 elsif p_address_line1 is not NULL then
1764 hr_utility.set_location(l_proc, 4);
1765
1766 l_dynamic_sql :=
1767 'select t.district_code,' ||
1768 ' t.address_line_1_kana ' ||
1769 'from per_jp_address_lookups t ' ||
1770 'where t.address_line_1 = :p_address_line1';
1771
1772
1773 -- Dynamic sql steps:
1774 -- ==================
1775 -- 1. Open dynamic sql cursor
1776 -- 2. Parse dynamic sql
1777 -- 3. Bind dynamic sql variables
1778 -- 4. Define dynamic sql columns
1779 -- 5. Execute and fetch dynamic sql
1780 --
1781 Hr_Utility.Set_Location(l_proc, 6);
1782 l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1783 --
1784 Hr_Utility.Set_Location(l_proc, 10);
1785 dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1786 --
1787 Hr_Utility.Set_Location(l_proc, 15);
1788 dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1789 ':p_address_line1', p_address_line1);
1790
1791 Hr_Utility.Set_Location(l_proc, 20);
1792 dbms_sql.define_column(l_sql_cursor, 1, l_town_or_city, 50); -- Step 4
1793 dbms_sql.define_column(l_sql_cursor, 2, l_region_1, 70);
1794 --
1795 Hr_Utility.Set_Location(l_proc, 30);
1796 l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
1797
1798 if l_rows = 0 then
1799 dbms_sql.close_cursor(l_sql_cursor);
1800 hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1801 hr_utility.raise_error;
1802
1803 elsif l_rows = 1 then
1804 Hr_Utility.Set_Location(l_proc, 35);
1805 dbms_sql.column_value(l_sql_cursor, 1, l_town_or_city);
1806 p_town_or_city := l_town_or_city;
1807
1808 else
1809 dbms_sql.close_cursor(l_sql_cursor);
1810 hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1811 hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1812 hr_utility.raise_error;
1813 end if;
1814 --
1815 --Both p_region_1 and p_address_line1 are null
1816 --
1817 else
1818 hr_utility.set_message(801, 'HR_72031_ADD_DIST_LINE1_NULL');
1819 hr_utility.raise_error;
1820 end if;
1821
1822
1823 dbms_sql.column_value(l_sql_cursor, 2, l_region_1);
1824 if p_region_1 is not null and
1825 p_region_1 <> l_region_1 then
1826 dbms_sql.close_cursor(l_sql_cursor);
1827 hr_utility.set_message(801, 'HR_72032_ADD_INVALID_KANA1');
1828 hr_utility.raise_error;
1829 end if;
1830
1831 p_region_1 := l_region_1;
1832 dbms_sql.close_cursor(l_sql_cursor);
1833 end if;
1834 hr_utility.set_location('Leaving:'|| l_proc, 5);
1835 -- Bug 885806
1836 -- dbms_output.put_line('Bottom of dynamic sql . . .');
1837 hr_utility.trace('Bottom of dynamic sql . . .');
1838 end chk_address1_towncity_comb;
1839 */
1840 --
1841 -- ----------------------------------------------------------------------------
1842 -- |------------------------<chk_address2_region2_comb >---------------------|
1843 -- ----------------------------------------------------------------------------
1844 --
1845 /* Bug 1677965
1846 procedure chk_address2_region2_comb(
1847 p_address_id in per_addresses.address_id%TYPE,
1848 p_object_version_number in per_addresses.object_version_number%TYPE,
1849 p_address_line2 in per_addresses.address_line2%type,
1850 p_region_2 in per_addresses.region_2%type) is
1851
1852 -- p_address_line2 ===> address_line2
1853 -- p_region_2 ===> address_line2_kana
1854
1855 l_api_updating boolean;
1856 l_proc varchar2(72) := g_package||'chk_address2_region2_comb';
1857 --
1858 l_output varchar2(150);
1859 l_rgeflg varchar2(10);
1860 l_region_2 per_addresses.region_2%type;
1861 --
1862 begin
1863 hr_utility.set_location('Entering:'|| l_proc, 1);
1864
1865 -- Only proceed with validation if :
1866 -- a) The current g_old_rec is current and
1867 -- b) The value for address_line2, or region_2 have changed
1868 --
1869 l_api_updating := per_add_shd.api_updating
1870 (p_address_id => p_address_id
1871 ,p_object_version_number => p_object_version_number);
1872
1873 if ((l_api_updating and
1874 (nvl(per_add_shd.g_old_rec.address_line2, hr_api.g_varchar2) <>
1875 nvl(p_address_line2, hr_api.g_varchar2) or
1876 nvl(per_add_shd.g_old_rec.region_2, hr_api.g_varchar2) <>
1877 nvl(p_region_2, hr_api.g_varchar2)
1878 )) or (NOT l_api_updating)) then
1879
1880 hr_utility.set_location(l_proc, 2);
1881
1882 if p_address_line2 is NULL and p_region_2 is not NULL then
1883 hr_utility.set_message(801, 'HR_72025_ADD_REGION2_NOT_NULL');
1884 hr_utility.raise_error;
1885 end if;
1886 l_region_2 := p_region_2;
1887 hr_chkfmt.checkformat(value => l_region_2
1888 ,format => 'KANA'
1889 ,output => l_output
1890 ,minimum => NULL
1891 ,maximum => NULL
1892 ,nullok => 'Y'
1893 ,rgeflg => l_rgeflg
1894 ,curcode => NULL);
1895 hr_utility.set_location(' Calling hr_chkfmt.checkformat2', 1);
1896 end if;
1897
1898 hr_utility.set_location('Leaving:'|| l_proc, 3);
1899 end chk_address2_region2_comb;
1900 */
1901 --
1902 -- ----------------------------------------------------------------------------
1903 -- |------------------------<chk_address3_region3_comb >---------------------|
1904 -- ----------------------------------------------------------------------------
1905 --
1906 /* Bug 1677965
1907 procedure chk_address3_region3_comb(
1908 p_address_id in per_addresses.address_id%TYPE,
1909 p_object_version_number in per_addresses.object_version_number%TYPE,
1910 p_address_line3 in per_addresses.address_line3%type,
1911 p_region_3 in per_addresses.region_3%type) is
1912
1913 -- p_address_line3 ===> address_line3
1914 -- p_region_3 ===> address_line3_kana
1915
1916 l_api_updating boolean;
1917 l_proc varchar2(72) := g_package||'chk_address3_region3_comb';
1918 --
1919 l_output varchar2(150);
1920 l_rgeflg varchar2(10);
1921 l_region_3 per_addresses.region_3%type;
1922 --
1923 begin
1924 hr_utility.set_location('Entering:'|| l_proc, 1);
1925
1926 -- Only proceed with validation if :
1927 -- a) The current g_old_rec is current and
1928 -- b) The value for address_line3, or region_3 have changed
1929 --
1930 l_api_updating := per_add_shd.api_updating
1931 (p_address_id => p_address_id
1932 ,p_object_version_number => p_object_version_number);
1933
1934 if ((l_api_updating and
1935 (nvl(per_add_shd.g_old_rec.address_line3, hr_api.g_varchar2) <>
1936 nvl(p_address_line3, hr_api.g_varchar2) or
1937 nvl(per_add_shd.g_old_rec.region_3, hr_api.g_varchar2) <>
1938 nvl(p_region_3, hr_api.g_varchar2)
1939 )) or (NOT l_api_updating)) then
1940
1941 hr_utility.set_location(l_proc, 2);
1942
1943 if p_address_line3 is NULL and p_region_3 is not NULL then
1944 hr_utility.set_message(801, 'HR_72026_ADD_REGION3_NOT_NULL');
1945 hr_utility.raise_error;
1946 end if;
1947 l_region_3 := p_region_3;
1948 hr_chkfmt.checkformat(value => l_region_3
1949 ,format => 'KANA'
1950 ,output => l_output
1951 ,minimum => NULL
1952 ,maximum => NULL
1953 ,nullok => 'Y'
1954 ,rgeflg => l_rgeflg
1955 ,curcode => NULL);
1956 hr_utility.set_location(' Calling hr_chkfmt.checkformat3', 1);
1957 end if;
1958
1959 hr_utility.set_location('Leaving:'|| l_proc, 1);
1960 end chk_address3_region3_comb;
1961 */
1962 --
1963 -- ----------------------------------------------------------------------------
1964 -- |------------------------< chk_jp_postal_code >----------------------------|
1965 -- ----------------------------------------------------------------------------
1966 /* Bug 1677965
1967 procedure chk_jp_postal_code(p_postal_code in varchar2) is
1968 l_proc varchar2(72) := g_package||'chk_jp_postal_code';
1969 l_sql_cursor integer; -- Dynamic sql cursor
1970 l_dynamic_sql varchar2(2000); -- Dynamic sql text
1971 l_rows integer; -- Num of rows returned
1972 l_dummy varchar2(1);
1973 begin
1974 hr_utility.set_location('Entering:'|| l_proc, 10);
1975 if p_postal_code is not null then
1976 --
1977 l_dynamic_sql := 'select null ' ||
1978 'from per_jp_postal_codes p ' ||
1979 'where p.postal_code = :p_postal_code';
1980 --
1981 -- Dynamic sql steps:
1982 -- ==================
1983 -- 1. Open dynamic sql cursor
1984 -- 2. Parse dynamic sql
1985 -- 3. Bind dynamic sql variables
1986 -- 4. Define dynamic sql columns
1987 -- 5. Execute and fetch dynamic sql
1988 --
1989 hr_utility.set_location(l_proc, 15);
1990 l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1991 --
1992 hr_utility.set_location(l_proc, 20);
1993 dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.native); -- Step 2
1994 --
1995 hr_utility.set_location(l_proc, 25);
1996 dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1997 ':p_postal_code', p_postal_code);
1998
1999 hr_utility.set_location(l_proc, 30);
2000 dbms_sql.define_column(l_sql_cursor, 1, l_dummy, 1); -- Step 4
2001 --
2002 hr_utility.set_location(l_proc, 35);
2003 l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
2004
2005 if l_rows = 0 then
2006 dbms_sql.close_cursor(l_sql_cursor);
2007 hr_utility.set_message(801, 'HR_72027_ADD_INVALID_POST_CODE');
2008 hr_utility.raise_error;
2009 end if;
2010 dbms_sql.close_cursor(l_sql_cursor);
2011 end if;
2012 hr_utility.set_location(' Leaving:'|| l_proc, 100);
2013 end chk_jp_postal_code;
2014 */
2015 --
2016 -- ---------------------------------------------------------------------------
2017 -- |---------------------------< chk_postal_code >--------------------------|
2018 -- ---------------------------------------------------------------------------
2019 --
2020 -- Description:
2021 -- If address style is 'GB' then check that the postal code length is not
2022 -- more than eight characters long.
2023 --
2024 -- If address style is 'US' then check that the postal code is
2025 -- - 5 or 10 characters long.
2026 -- - first 5 characters must be numbers.
2027 -- - if postal code is 10 characters long, sixth character must be '-'
2028 -- follow by 4 numbers.
2029 -- - if GEOCODES is installed, postal code is mandatory.
2030 --
2031 -- Pre-conditions:
2032 -- Style (p_style) must be valid.
2033 --
2034 -- In Arguments:
2035 -- p_address_id
2036 -- p_style
2037 -- p_postal_code
2038 -- p_business_group_id
2039 -- p_object_version_number
2040 --
2041 -- Post Success:
2042 -- If address style is 'GB','US' or 'JP' and the postal code is valid,
2043 -- processing continues.
2044 --
2045 -- Post Failure:
2046 -- If address style is 'GB','US' or 'JP' and the postal code is invalid,
2047 -- an application error is raised and processing terminates.
2048 --
2049 --
2050 -- Access Status:
2051 -- Internal Table Handler Use Only.
2052 --
2053 -- Procedure is modified by adding p_town_or_city in parameter for checking the
2054 -- valid zip code if GEOCODES is installed.
2055 --
2056 -- {End Of Comments}
2057 -- ----------------------------------------------------------------------------
2058 procedure chk_postal_code
2059 (p_address_id in per_addresses.address_id%TYPE
2060 ,p_style in per_addresses.style%TYPE
2061 ,p_postal_code in per_addresses.postal_code%TYPE
2062 ,p_business_group_id in per_addresses.business_group_id%TYPE
2063 ,p_object_version_number in per_addresses.object_version_number%TYPE
2064 ,p_town_or_city in per_addresses.town_or_city%TYPE)
2065 is
2066 -- Cursor Defination added for bug 5367066
2067 cursor get_city_address is
2068 select /*+index(Z PAY_US_ZIP_CODES_N1)
2069 index(Z PAY_US_ZIP_CODES_N2) */ c.state_abbrev
2070 , c.state_code
2071 ,a.city_name
2072 , a.city_code
2073 , z.zip_start
2074 , z.zip_end
2075 , b.county_name
2076 , b.county_code
2077 from
2078 pay_us_city_names a
2079 , pay_us_counties b
2080 , pay_us_states c
2081 , pay_us_zip_codes z
2082 where a.state_code = c.state_code
2083 and a.county_code = b.county_code
2084 and b.state_code = c.state_code
2085 and a.city_code=z.city_code
2086 and a.state_code=z.state_code
2087 and a.county_code=z.county_code
2088 and substr(p_postal_code,1,5) between z.zip_start and z.zip_end
2089 and upper(a.city_name)=upper(p_town_or_city)
2090 order by z.zip_start desc;
2091 ---- Cursor Defination end for bug 5367066
2092
2093 l_proc varchar2(72) := g_package||'chk_postal_code';
2094 l_api_updating boolean;
2095 l_postal_code_1 varchar2(5);
2096 l_postal_code_2 varchar2(1);
2097 l_postal_code_3 varchar2(4);
2098 l_geocodes_installed varchar2(1);
2099 l_count number(10);
2100 l_city_address_data get_city_address%ROWTYPE;
2101 --
2102 begin
2103 hr_utility.set_location('Entering:'|| l_proc, 1);
2104 --
2105 -- Check mandatory parameters have been set
2106 --
2107 hr_api.mandatory_arg_error
2108 (p_api_name => l_proc
2109 ,p_argument => 'style'
2110 ,p_argument_value => p_style
2111 );
2112 --
2113 -- Only proceed with validation if :
2114 -- a) The current g_old_rec is current and
2115 -- b) The value for postal code has changed
2116 --
2117 l_api_updating := per_add_shd.api_updating
2118 (p_address_id => p_address_id
2119 ,p_object_version_number => p_object_version_number);
2120 --
2121 if ((l_api_updating
2122 and nvl(per_add_shd.g_old_rec.postal_code, hr_api.g_varchar2) <>
2123 nvl(p_postal_code, hr_api.g_varchar2)) or
2124 (NOT l_api_updating))
2125 then
2126 hr_utility.set_location(l_proc, 2);
2127 --
2128 -- Check if GEOCODES is installed for a US legislation
2129 --
2130 if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y' then
2131 --
2132 -- Check that the zip code is set
2133 --
2134 if p_postal_code is null then
2135 --
2136 hr_utility.set_message(800, 'PER_52991_ADD_NO_ZIP_SET');
2137 hr_utility.raise_error;
2138 --
2139 end if;
2140 /*----------------------- Changes start for 5367066 ---------------------*/
2141
2142 begin
2143 hr_utility.set_location('Postal code ='||substr(p_postal_code,1,5), 21);
2144 hr_utility.set_location('Town city ='||p_town_or_city, 22);
2145 open get_city_address;
2146 loop
2147 fetch get_city_address into l_city_address_data;
2148 l_count := get_city_address%ROWCOUNT;
2149 hr_utility.set_location('NO of rows returned from cursor = '||l_count, 23);
2150 exit when get_city_address%NOTFOUND;
2151 if (substr(p_postal_code,1,5) between l_city_address_data.zip_start and l_city_address_data.zip_end) then
2152 null;
2153 else
2154 hr_utility.set_message(800, 'HR_7786_ADDR_US_ZIP_OOR');
2155 hr_utility.set_message_token('ZIP_START',l_city_address_data.zip_start);
2156 hr_utility.set_message_token('ZIP_END',l_city_address_data.zip_end);
2157 hr_utility.raise_error;
2158 end if;
2159 end loop;
2160 --
2161 close get_city_address;
2162 if l_count = 0 then
2163 hr_utility.set_message(800, 'HR_51195_ADD_INVALID_ZIP_CODE');
2164 hr_utility.raise_error;
2165 end if;
2166 hr_utility.set_location('Leaveing the Cursor =', 24);
2167 end;
2168
2169 begin
2170 --
2171 if length(p_postal_code) = 5 then
2172 hr_utility.set_location(l_proc, 4);
2173 --
2174 -- Check if zip code is all numbers
2175 --
2176 for i in 1..5 loop
2177 if(substr(p_postal_code,i,1)
2178 not between '0' and '9') then
2179 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2180 hr_utility.raise_error;
2181 end if;
2182 end loop;
2183 --
2184 elsif length(p_postal_code) = 10 then
2185 hr_utility.set_location(l_proc, 5);
2186 --
2187 -- Parse zip code to validate for correct format.
2188 --
2189 l_postal_code_1 := substr(p_postal_code,1,5);
2190 l_postal_code_2 := substr(p_postal_code,6,1);
2191 l_postal_code_3 := substr(p_postal_code,7,4);
2192 --
2193 -- Validate first 5 characters are numbers
2194 --
2195 for i in 1..5 loop
2196 if(substr(l_postal_code_1,i,1)
2197 not between '0' and '9') then
2198 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2199 hr_utility.raise_error;
2200 end if;
2201 end loop;
2202 hr_utility.set_location(l_proc, 6);
2203 --
2204 -- Validate last 4 characters are numbers
2205 --
2206 for i in 1..4 loop
2207 if(substr(l_postal_code_3,i,1)
2208 not between '0' and '9') then
2209 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2210 hr_utility.raise_error;
2211 end if;
2212 end loop;
2213 hr_utility.set_location(l_proc, 7);
2214 --
2215 -- Validate last sixth characters is '-'
2216 --
2217 if l_postal_code_2 <> '-' then
2218 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2219 hr_utility.raise_error;
2220 end if;
2221 else
2222 --
2223 -- If zip code is not 5 or 10 character long
2224 --
2225 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2226 hr_utility.raise_error;
2227 end if;
2228 --
2229 -- If an invalid zip code character generates an
2230 -- exception
2231 --
2232 exception
2233 when others then
2234 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2235 hr_utility.raise_error;
2236 end;
2237
2238 /*----------------------- Changes End for 5367066 ---------------------*/
2239
2240 --
2241 else
2242 --
2243 if p_postal_code is not null then
2244 --
2245 -- Check that the GB postal code is no longer than
2246 -- 8 characters long
2247 --
2248 if p_style = 'GB' then
2249 if length(p_postal_code) > 8 then
2250 hr_utility.set_message(801, 'HR_7306_ADD_POST_CODE');
2251 hr_utility.raise_error;
2252 end if;
2253 --
2254 -- Check that the US postal code is either 5 or 10 character
2255 --
2256 elsif p_style = 'US' and l_geocodes_installed = 'Y' then
2257 hr_utility.set_location(l_proc, 3);
2258 --
2259 begin
2260 --
2261 if length(p_postal_code) = 5 then
2262 hr_utility.set_location(l_proc, 4);
2263 --
2264 -- Check if zip code is all numbers
2265 --
2266 for i in 1..5 loop
2267 if(substr(p_postal_code,i,1)
2268 not between '0' and '9') then
2269 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2270 hr_utility.raise_error;
2271 end if;
2272 end loop;
2273 --
2274 elsif length(p_postal_code) = 10 then
2275 hr_utility.set_location(l_proc, 5);
2276 --
2277 -- Parse zip code to validate for correct format.
2278 --
2279 l_postal_code_1 := substr(p_postal_code,1,5);
2280 l_postal_code_2 := substr(p_postal_code,6,1);
2281 l_postal_code_3 := substr(p_postal_code,7,4);
2282 --
2283 -- Validate first 5 characters are numbers
2284 --
2285 for i in 1..5 loop
2286 if(substr(l_postal_code_1,i,1)
2287 not between '0' and '9') then
2288 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2289 hr_utility.raise_error;
2290 end if;
2291 end loop;
2292 hr_utility.set_location(l_proc, 6);
2293 --
2294 -- Validate last 4 characters are numbers
2295 --
2296 for i in 1..4 loop
2297 if(substr(l_postal_code_3,i,1)
2298 not between '0' and '9') then
2299 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2300 hr_utility.raise_error;
2301 end if;
2302 end loop;
2303 hr_utility.set_location(l_proc, 7);
2304 --
2305 -- Validate last sixth characters is '-'
2306 --
2307 if l_postal_code_2 <> '-' then
2308 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2309 hr_utility.raise_error;
2310 end if;
2311
2312 else
2313 --
2314 -- If zip code is not 5 or 10 character long
2315 --
2316 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2317 hr_utility.raise_error;
2318 end if;
2319 --
2320 -- If an invalid zip code character generates an
2321 -- exception
2322 --
2323 exception
2324 when others then
2325 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2326 hr_utility.raise_error;
2327 end;
2328 /* Bug 1677965
2329 elsif p_style = 'JP' then
2330 hr_utility.set_location(l_proc, 8);
2331 chk_jp_postal_code(p_postal_code);
2332 */
2333 end if;
2334 --
2335 end if;
2336 --
2337 end if;
2338 --
2339 end if;
2340 --
2341 hr_utility.set_location(' Leaving:'|| l_proc, 9);
2342 exception
2343 when app_exception.application_exception then
2344 if hr_multi_message.exception_add
2345 (p_associated_column1 => 'PER_ADDRESSES.POSTAL_CODE'
2346 ) then
2347 hr_utility.set_location(' Leaving:'||l_proc,10);
2348 raise;
2349 end if;
2350 hr_utility.set_location(' Leaving:'||l_proc,11);
2351 --
2352 end chk_postal_code;
2353 --
2354 -- ---------------------------------------------------------------------------
2355 -- |---------------------------< chk_tax_address_zip >----------------------|
2356 -- ---------------------------------------------------------------------------
2357 --
2358 -- Description:
2359 -- If address style is 'US' then check that the postal code is
2360 -- - 5 or 10 characters long.
2361 -- - first 5 characters must be numbers.
2362 -- - if postal code is 10 characters long, sixth character must be '-'
2363 -- follow by 4 numbers.
2364 -- - if GEOCODES is installed, postal code is mandatory.
2365 --
2366 -- Pre-conditions:
2367 -- Style (p_style) must be valid.
2368 --
2369 -- In Arguments:
2370 -- p_address_id
2371 -- p_style
2372 -- p_tax_address_zip
2373 -- p_business_group_id
2374 -- p_object_version_number
2375 --
2376 -- Post Success:
2377 -- If address style is 'US' and the postal code is valid,
2378 -- processing continues.
2379 --
2380 -- Post Failure:
2381 -- If address style is 'US' and the postal code is invalid,
2382 -- an application error is raised and processing terminates.
2383 --
2384 --
2385 -- Access Status:
2386 -- Internal Table Handler Use Only.
2387 --
2388 -- {End Of Comments}
2389 -- ----------------------------------------------------------------------------
2390 procedure chk_tax_address_zip
2391 (p_address_id in per_addresses.address_id%TYPE
2392 ,p_style in per_addresses.style%TYPE
2393 ,p_tax_address_zip in per_addresses.add_information20%TYPE
2394 ,p_business_group_id in per_addresses.business_group_id%TYPE
2395 ,p_object_version_number in per_addresses.object_version_number%TYPE)
2396 is
2397 --
2398 l_proc varchar2(72) := g_package||'chk_tax_address_zip';
2399 l_api_updating boolean;
2400 l_tax_address_zip_1 varchar2(5);
2401 l_tax_address_zip_2 varchar2(1);
2402 l_tax_address_zip_3 varchar2(4);
2403 l_geocodes_installed varchar2(1);
2404 --
2405 begin
2406 if p_tax_address_zip is not null
2407 then
2408 hr_utility.set_location('Entering:'|| l_proc, 1);
2409 --
2410 -- Check mandatory parameters have been set
2411 --
2412 hr_api.mandatory_arg_error
2413 (p_api_name => l_proc
2414 ,p_argument => 'style'
2415 ,p_argument_value => p_style
2416 );
2417 --
2418 -- Only proceed with validation if :
2419 -- a) The current g_old_rec is current and
2420 -- b) The value for add_information20 has changed
2421 --
2422 l_api_updating := per_add_shd.api_updating
2423 (p_address_id => p_address_id
2424 ,p_object_version_number => p_object_version_number);
2425 --
2426 if ((l_api_updating
2427 and nvl(per_add_shd.g_old_rec.add_information20, hr_api.g_varchar2) <>
2428 nvl(p_tax_address_zip, hr_api.g_varchar2)) or
2429 (NOT l_api_updating))
2430 then
2431 hr_utility.set_location(l_proc, 2);
2432 --
2433 -- Check if GEOCODES is installed for a US legislation
2434 --
2435 if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y'
2436 then
2437 --
2438 -- Check that the US postal code is either 5 or 10 character
2439 --
2440 begin
2441 --
2442 if length(p_tax_address_zip) = 5 then
2443 hr_utility.set_location(l_proc, 3);
2444 --
2445 -- Check if zip code is all numbers
2446 --
2447 for i in 1..5 loop
2448 if(substr(p_tax_address_zip,i,1)
2449 not between '0' and '9') then
2450 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2451 hr_utility.raise_error;
2452 end if;
2453 end loop;
2454 --
2455 elsif length(p_tax_address_zip) = 10 then
2456 hr_utility.set_location(l_proc, 4);
2457 --
2458 -- Parse zip code to validate for correct format.
2459 --
2460 l_tax_address_zip_1 := substr(p_tax_address_zip,1,5);
2461 l_tax_address_zip_2 := substr(p_tax_address_zip,6,1);
2462 l_tax_address_zip_3 := substr(p_tax_address_zip,7,4);
2463
2464 --
2465 -- Validate first 5 characters are numbers
2466 --
2467 for i in 1..5 loop
2468 if(substr(l_tax_address_zip_1,i,1)
2469 not between '0' and '9') then
2470 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2471 hr_utility.raise_error;
2472 end if;
2473 end loop;
2474 hr_utility.set_location(l_proc, 5);
2475 --
2476 -- Validate last 4 characters are numbers
2477 --
2478 for i in 1..4 loop
2479 if(substr(l_tax_address_zip_3,i,1)
2480 not between '0' and '9') then
2481 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2482 hr_utility.raise_error;
2483 end if;
2484 end loop;
2485 hr_utility.set_location(l_proc, 6);
2486 --
2487 -- Validate last sixth characters is '-'
2488 --
2489 if l_tax_address_zip_2 <> '-' then
2490 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2491 hr_utility.raise_error;
2492 end if;
2493 else
2494 --
2495 -- If zip code is not 5 or 10 character long
2496 --
2497 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2498 hr_utility.raise_error;
2499 end if;
2500 --
2501 -- If an invalid zip code character generates an
2502 -- exception
2503 --
2504 exception
2505 when others then
2506 hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2507 hr_utility.raise_error;
2508 end;
2509 end if;
2510 --
2511 end if;
2512 --
2513 end if;
2514 hr_utility.set_location(' Leaving:'|| l_proc, 7);
2515 exception
2516 when app_exception.application_exception then
2517 if hr_multi_message.exception_add
2518 (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION20'
2519 ) then
2520 hr_utility.set_location(' Leaving:'||l_proc,6);
2521 raise;
2522 end if;
2523 hr_utility.set_location(' Leaving:'||l_proc,7);
2524 --
2525 end chk_tax_address_zip;
2526 --
2527 -- ---------------------------------------------------------------------------
2528 -- |---------------------------< chk_region_1 >-----------------------------|
2529 -- ---------------------------------------------------------------------------
2530 --
2531 -- Description:
2532 -- If address style is 'GB' then validates that a region_1 code exists in
2533 -- table hr_lookups.
2534 -- If address style is 'US' and GEOCODES is installed then validates that a
2535 -- region_1 code exists in table pay_us_counties as the county code, unless
2536 -- p_validate_county is set to FALSE.
2537 --
2538 -- Pre-conditions:
2539 -- Style (p_style) must be valid.
2540 --
2541 -- In Arguments:
2542 -- p_address_id
2543 -- p_region_1
2544 -- p_style
2545 -- p_business_group_id
2546 -- p_effective_date
2547 -- p_object_version_number
2548 -- p_validate_county
2549 --
2550 -- Post Success:
2551 -- If address style is 'GB' and a row does exist in hr_lookups
2552 -- for the given region_1 code, processing continues.
2553 -- If address style is 'US' and GEOCODES is installed a row does exist
2554 -- in pay_us_counties for the given region_1 code, processing continues.
2555 --
2556 -- Post Failure:
2557 -- If address style is 'GB' and a row does not exist in hr_lookups
2558 -- for the given region_1 code, an application error is raised
2559 -- and processing terminates.
2560 --
2561 -- Access Status:
2562 -- Internal Table Handler Use Only.
2563 --
2564 -- {End Of Comments}
2565 -- ----------------------------------------------------------------------------
2566 procedure chk_region_1
2567 (p_address_id in per_addresses.address_id%TYPE
2568 ,p_style in per_addresses.style%TYPE
2569 ,p_region_1 in per_addresses.region_1%TYPE
2570 ,p_business_group_id in per_addresses.business_group_id%TYPE
2571 ,p_effective_date in date
2572 ,p_object_version_number in per_addresses.object_version_number%TYPE
2573 ,p_validate_county in boolean default TRUE)
2574 is
2575 --
2576 l_exists varchar2(1);
2577 l_proc varchar2(72) := g_package||'chk_region_1';
2578 l_api_updating boolean;
2579 --
2580 cursor csr_valid_us_county is
2581 select null
2582 from pay_us_counties
2583 where county_name = p_region_1;
2584 --
2585 begin
2586 hr_utility.set_location('Entering:'|| l_proc, 1);
2587 --
2588 -- Check mandatory parameters have been set
2589 --
2590 hr_api.mandatory_arg_error
2591 (p_api_name => l_proc
2592 ,p_argument => 'style'
2593 ,p_argument_value => p_style
2594 );
2595 --
2596 hr_api.mandatory_arg_error
2597 (p_api_name => l_proc
2598 ,p_argument => 'effective_date'
2599 ,p_argument_value => p_effective_date
2600 );
2601 --
2602 -- Only proceed with validation if :
2603 -- a) The current g_old_rec is current and
2604 -- b) The value for region_1 has changed
2605 --
2606 l_api_updating := per_add_shd.api_updating
2607 (p_address_id => p_address_id
2608 ,p_object_version_number => p_object_version_number);
2609 --
2610 if ((l_api_updating and
2611 nvl(per_add_shd.g_old_rec.region_1, hr_api.g_varchar2) <>
2612 nvl(p_region_1, hr_api.g_varchar2)) or
2613 (NOT l_api_updating))
2614 then
2615 hr_utility.set_location(l_proc, 2);
2616 --
2617 -- Check for GEOCODES in a US legislation
2618 --
2619 if p_style = 'US'
2620 and hr_general.chk_geocodes_installed = 'Y'
2621 and p_validate_county then
2622 --
2623 -- Check that the county is set
2624 --
2625 if p_region_1 is null then
2626 --
2627 hr_utility.set_message(800, 'PER_52984_ADD_NO_COUNTY_SET');
2628 hr_utility.raise_error;
2629 --
2630 end if;
2631 --
2632 hr_utility.set_location(l_proc, 5);
2633 open csr_valid_us_county;
2634 fetch csr_valid_us_county into l_exists;
2635 if csr_valid_us_county%notfound then
2636 --
2637 close csr_valid_us_county;
2638 hr_utility.set_message(801, 'HR_7953_ADDR_NO_COUNTY_FOUND');
2639 hr_utility.raise_error;
2640 --
2641 end if;
2642 --
2643 else
2644 --
2645 -- Check that value for region_1 is valid
2646 --
2647 if p_region_1 is not null then
2648 hr_utility.set_location(l_proc, 3);
2649 --
2650 if p_style = 'GB' then
2651 hr_utility.set_location(l_proc, 4);
2652 --
2653 if hr_api.not_exists_in_hr_lookups
2654 (p_effective_date => p_effective_date
2655 ,p_lookup_type => 'GB_COUNTY'
2656 ,p_lookup_code => p_region_1
2657 ) then
2658 --
2659 hr_utility.set_message(801, 'HR_7307_ADD_GB_REGION_1');
2660 hr_utility.raise_error;
2661 --
2662 end if;
2663 --
2664 end if;
2665 --
2666 end if;
2667 --
2668 end if;
2669 --
2670 end if;
2671 hr_utility.set_location(' Leaving:'|| l_proc, 6);
2672 exception
2673 when app_exception.application_exception then
2674 if hr_multi_message.exception_add
2675 (p_associated_column1 => 'PER_ADDRESSES.REGION_1'
2676 ) then
2677 hr_utility.set_location(' Leaving:'||l_proc,7);
2678 raise;
2679 end if;
2680 hr_utility.set_location(' Leaving:'||l_proc,8);
2681 --
2682 end chk_region_1;
2683 --
2684 --
2685 -- ---------------------------------------------------------------------------
2686 -- |--------------------------< chk_tax_county >-----------------------------|
2687 -- ---------------------------------------------------------------------------
2688 --
2689 -- Description:
2690 -- If address style is 'US' and GEOCODES is installed then validates that a
2691 -- add_information19 code exists in table pay_us_counties as the county code.
2692 --
2693 -- Pre-conditions:
2694 -- Style (p_style) must be valid.
2695 --
2696 -- In Arguments:
2697 -- p_address_id
2698 -- p_tax_county
2699 -- p_style
2700 -- p_business_group_id
2701 -- p_effective_date
2702 -- p_object_version_number
2703 --
2704 -- Post Success:
2705 -- If address style is 'US' and GEOCODES is installed and a row does exist
2706 -- in pay_us_counties for the given add_information19 code, processing continues.
2707 --
2708 -- Post Failure:
2709 -- If address style is 'US' and GEOCODES is installed and a row does not exist
2710 -- in pay_us_counties for the given add_information19 code, processing stops
2711 -- an application error is raised
2712 -- and processing terminates.
2713 --
2714 -- Access Status:
2715 -- Internal Table Handler Use Only.
2716 --
2717 -- {End Of Comments}
2718 -- ----------------------------------------------------------------------------
2719 procedure chk_tax_county
2720 (p_address_id in per_addresses.address_id%TYPE
2721 ,p_style in per_addresses.style%TYPE
2722 ,p_tax_county in per_addresses.add_information19%TYPE
2723 ,p_business_group_id in per_addresses.business_group_id%TYPE
2724 ,p_effective_date in date
2725 ,p_object_version_number in per_addresses.object_version_number%TYPE)
2726 is
2727 --
2728 l_exists varchar2(1);
2729 l_proc varchar2(72) := g_package||'chk_tax_county';
2730 l_api_updating boolean;
2731 --
2732 cursor csr_valid_us_county is
2733 select null
2734 from pay_us_counties
2735 where county_name = p_tax_county;
2736 --
2737 begin
2738 hr_utility.set_location('Entering:'|| l_proc, 1);
2739 --
2740 -- Check mandatory parameters have been set
2741 --
2742 if p_tax_county is not null
2743 then
2744 hr_api.mandatory_arg_error
2745 (p_api_name => l_proc
2746 ,p_argument => 'style'
2747 ,p_argument_value => p_style
2748 );
2749 --
2750 hr_api.mandatory_arg_error
2751 (p_api_name => l_proc
2752 ,p_argument => 'effective_date'
2753 ,p_argument_value => p_effective_date
2754 );
2755 --
2756 -- Only proceed with validation if :
2757 -- a) The current g_old_rec is current and
2758 -- b) The value for tax_county has changed
2759 --
2760 l_api_updating := per_add_shd.api_updating
2761 (p_address_id => p_address_id
2762 ,p_object_version_number => p_object_version_number);
2763 --
2764 if ((l_api_updating and
2765 nvl(per_add_shd.g_old_rec.add_information19, hr_api.g_varchar2) <>
2766 nvl(p_tax_county, hr_api.g_varchar2)) or
2767 (NOT l_api_updating))
2768 then
2769 hr_utility.set_location(l_proc, 2);
2770 --
2771 -- Check for GEOCODES in a US legislation
2772 --
2773 if p_style = 'US'
2774 and hr_general.chk_geocodes_installed = 'Y'
2775 then
2776 open csr_valid_us_county;
2777 fetch csr_valid_us_county into l_exists;
2778 if csr_valid_us_county%notfound then
2779 --
2780 close csr_valid_us_county;
2781 hr_utility.set_message(801, 'HR_7953_ADDR_NO_COUNTY_FOUND');
2782 hr_utility.raise_error;
2783 --
2784 end if;
2785 close csr_valid_us_county;
2786 --
2787 end if;
2788 --
2789 end if;
2790 else
2791 null;
2792 end if;
2793 hr_utility.set_location(' Leaving:'|| l_proc, 6);
2794 exception
2795 when app_exception.application_exception then
2796 if hr_multi_message.exception_add
2797 (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION19'
2798 ) then
2799 hr_utility.set_location(' Leaving:'||l_proc,7);
2800 raise;
2801 end if;
2802 hr_utility.set_location(' Leaving:'||l_proc,8);
2803 --
2804 end chk_tax_county;
2805 --
2806 -- ---------------------------------------------------------------------------
2807 -- |---------------------------< chk_region_2 >-----------------------------|
2808 -- ---------------------------------------------------------------------------
2809 --
2810 -- Description:
2811 -- If address style is 'US', validate region_2 code (state abbreviation)
2812 -- exist in the hr_lookups table if HR installation only or non-US
2813 -- legislation, or the Vertex pay_us_states table if payroll is installed
2814 -- under US legislation.
2815 --
2816 -- Pre-conditions:
2817 -- Style (p_style) must be valid.
2818 --
2819 -- In Arguments:
2820 -- p_address_id
2821 -- p_region_2
2822 -- p_style
2823 -- p_business_group_id
2824 -- p_effective_date
2825 -- p_object_version_number
2826 --
2827 -- Post Success:
2828 -- If address style is 'US' and a row exist in hr_lookups/pay_us_states
2829 -- for the given region_2 code, processing continues.
2830 --
2831 -- Post Failure:
2832 -- If address style is 'US' and a row does not exist in
2833 -- hr_lookups/pay_us_states for the given region_2 code, an application
2834 -- error is raised and processing terminates.
2835 --
2836 -- Access Status:
2837 -- Internal Table Handler Use Only.
2838 --
2839 -- {End Of Comments}
2840 -- ----------------------------------------------------------------------------
2841 procedure chk_region_2
2842 (p_address_id in per_addresses.address_id%TYPE
2843 ,p_style in per_addresses.style%TYPE
2844 ,p_region_2 in per_addresses.region_2%TYPE
2845 ,p_business_group_id in per_addresses.business_group_id%TYPE
2846 ,p_effective_date in date
2847 ,p_object_version_number in per_addresses.object_version_number%TYPE
2848 )
2849 is
2850 --
2851 l_exists varchar2(1);
2852 l_proc varchar2(72) := g_package||'chk_region_2';
2853 --
2854 l_api_updating boolean;
2855 l_geocodes_installed varchar2(1);
2856 --
2857 -- Declare cursor
2858 --
2859 cursor csr_valid_state is
2860 select null
2861 from pay_us_states
2862 where state_abbrev = p_region_2;
2863 --
2864 begin
2865 hr_utility.set_location('Entering:'|| l_proc, 1);
2866 --
2867 -- Check mandatory parameters have been set.
2868 --
2869 hr_api.mandatory_arg_error
2870 (p_api_name => l_proc
2871 ,p_argument => 'style'
2872 ,p_argument_value => p_style
2873 );
2874 --
2875 hr_api.mandatory_arg_error
2876 (p_api_name => l_proc
2877 ,p_argument => 'effective_date'
2878 ,p_argument_value => p_effective_date
2879 );
2880 --
2881 -- Only proceed with validation if :
2882 -- a) The current g_old_rec is current and
2883 -- b) The value for region_2 has changed
2884 --
2885 l_api_updating := per_add_shd.api_updating
2886 (p_address_id => p_address_id
2887 ,p_object_version_number => p_object_version_number
2888 );
2889 --
2890 if ((l_api_updating and
2891 nvl(per_add_shd.g_old_rec.region_2, hr_api.g_varchar2) <>
2892 nvl(p_region_2, hr_api.g_varchar2)) or
2893 (NOT l_api_updating))
2894 then
2895 hr_utility.set_location(l_proc, 2);
2896 --
2897 -- Check if GEOCODES is installed under a US legislation
2898 --
2899 if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y'
2900 then
2901 hr_utility.set_location(l_proc, 5);
2902 --
2903 -- Check if the state is set
2904 --
2905 if p_region_2 is null then
2906 --
2907 hr_utility.set_message(800, 'PER_52985_ADD_NO_STATE_SET');
2908 hr_utility.raise_error;
2909 --
2910 end if;
2911 --
2912 open csr_valid_state;
2913 fetch csr_valid_state into l_exists;
2914 if csr_valid_state%notfound then
2915 close csr_valid_state;
2916 hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
2917 hr_utility.raise_error;
2918 end if;
2919 close csr_valid_state;
2920 --
2921 else
2922 --
2923 -- Check that value for region_2 is valid.
2924 --
2925 if p_region_2 is not null then
2926 hr_utility.set_location(l_proc, 3);
2927 --
2928 if p_style = 'US'
2929 then
2930 hr_utility.set_location(l_proc, 4);
2931 --
2932 if hr_api.not_exists_in_hr_lookups
2933 (p_effective_date => p_effective_date
2934 ,p_lookup_type => 'US_STATE'
2935 ,p_lookup_code => p_region_2
2936 )
2937 then
2938 --
2939 -- Error: Invalid region 2.
2940 hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
2941 hr_utility.raise_error;
2942 --
2943 end if;
2944 --
2945 end if;
2946 --
2947 end if;
2948 --
2949 end if;
2950 --
2951 end if;
2952 --
2953 hr_utility.set_location(' Leaving:'|| l_proc, 10);
2954 exception
2955 when app_exception.application_exception then
2956 if hr_multi_message.exception_add
2957 (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
2958 ) then
2959 hr_utility.set_location(' Leaving:'||l_proc,11);
2960 raise;
2961 end if;
2962 hr_utility.set_location(' Leaving:'||l_proc,12);
2963 --
2964 end chk_region_2;
2965 --
2966 -- ---------------------------------------------------------------------------
2967 -- |---------------------------< chk_tax_state >-----------------------------|
2968 -- ---------------------------------------------------------------------------
2969 --
2970 -- Description:
2971 -- If address style is 'US', validate tax_state (add_information17)
2972 -- code (state abbreviation)exist in the hr_lookups table if HR
2973 -- installation only, or the Vertex pay_us_states table if payroll is installed
2974 -- under US legislation.
2975 --
2976 -- Pre-conditions:
2977 -- Style (p_style) must be valid.
2978 --
2979 -- In Arguments:
2980 -- p_address_id
2981 -- p_tax_state
2982 -- p_style
2983 -- p_business_group_id
2984 -- p_effective_date
2985 -- p_object_version_number
2986 --
2987 -- Post Success:
2988 -- If address style is 'US' and a row exist in hr_lookups/pay_us_states
2989 -- for the given tax_state code, processing continues.
2990 --
2991 -- Post Failure:
2992 -- If address style is 'US' and a row does not exist in
2993 -- hr_lookups/pay_us_states for the given tax_state code, an application
2994 -- error is raised and processing terminates.
2995 --
2996 -- Access Status:
2997 -- Internal Table Handler Use Only.
2998 --
2999 -- {End Of Comments}
3000 -- ----------------------------------------------------------------------------
3001 procedure chk_tax_state
3002 (p_address_id in per_addresses.address_id%TYPE
3003 ,p_style in per_addresses.style%TYPE
3004 ,p_tax_state in per_addresses.add_information17%TYPE
3005 ,p_business_group_id in per_addresses.business_group_id%TYPE
3006 ,p_effective_date in date
3007 ,p_object_version_number in per_addresses.object_version_number%TYPE
3008 )
3009 is
3010 --
3011 l_exists varchar2(1);
3012 l_proc varchar2(72) := g_package||'chk_tax_state';
3013 --
3014 l_api_updating boolean;
3015 l_geocodes_installed varchar2(1);
3016 --
3017 -- Declare cursor
3018 --
3019 cursor csr_valid_state is
3020 select null
3021 from pay_us_states
3022 where state_abbrev = p_tax_state ;
3023 --
3024 begin
3025 if p_tax_state is not null
3026 then
3027 hr_utility.set_location('Entering:'|| l_proc, 1);
3028 --
3029 -- Check mandatory parameters have been set.
3030 --
3031 hr_api.mandatory_arg_error
3032 (p_api_name => l_proc
3033 ,p_argument => 'style'
3034 ,p_argument_value => p_style
3035 );
3036 --
3037 hr_api.mandatory_arg_error
3038 (p_api_name => l_proc
3039 ,p_argument => 'effective_date'
3040 ,p_argument_value => p_effective_date
3041 );
3042 --
3043 -- Only proceed with validation if :
3044 -- a) The current g_old_rec is current and
3045 -- b) The value for tax_state (add_information17) has changed
3046 --
3047 l_api_updating := per_add_shd.api_updating
3048 (p_address_id => p_address_id
3049 ,p_object_version_number => p_object_version_number
3050 );
3051 --
3052 if ((l_api_updating and
3053 nvl(per_add_shd.g_old_rec.add_information17, hr_api.g_varchar2) <>
3054 nvl(p_tax_state, hr_api.g_varchar2)) or
3055 (NOT l_api_updating))
3056 then
3057 hr_utility.set_location(l_proc, 2);
3058 --
3059 -- Check if GEOCODES is installed under a US legislation
3060 --
3061 if hr_general.chk_geocodes_installed = 'Y'
3062 then
3063 hr_utility.set_location(l_proc, 5);
3064 open csr_valid_state;
3065 fetch csr_valid_state into l_exists;
3066 if csr_valid_state%notfound then
3067 close csr_valid_state;
3068 hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
3069 hr_utility.raise_error;
3070 end if;
3071 close csr_valid_state;
3072 --
3073 else
3074 --
3075 -- Check that value for tax_state is valid.
3076 --
3077 hr_utility.set_location(l_proc, 4);
3078 --
3079 if hr_api.not_exists_in_hr_lookups
3080 (p_effective_date => p_effective_date
3081 ,p_lookup_type => 'US_STATE'
3082 ,p_lookup_code => p_tax_state
3083 )
3084 then
3085 --
3086 -- Error: Invalid tax_state.
3087 hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
3088 hr_utility.raise_error;
3089 --
3090 end if;
3091 --
3092 end if;
3093 --
3094 end if;
3095 end if;
3096 --
3097 hr_utility.set_location(' Leaving:'|| l_proc, 10);
3098 exception
3099 when app_exception.application_exception then
3100 if hr_multi_message.exception_add
3101 (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3102 ) then
3103 hr_utility.set_location(' Leaving:'||l_proc,11);
3104 raise;
3105 end if;
3106 hr_utility.set_location(' Leaving:'||l_proc,12);
3107 --
3108 end chk_tax_state;
3109 --
3110 -- ---------------------------------------------------------------------------
3111 -- |---------------------------< chk_town_or_city >--------------------------|
3112 -- ---------------------------------------------------------------------------
3113 --
3114 -- Description:
3115 -- If address style is 'US' and Payroll is installed under US legislation,
3116 -- validate town_or_city exist in pay_us_city_names.
3117 --
3118 -- Pre-conditions:
3119 -- Style (p_style) must be valid.
3120 --
3121 -- In Arguments:
3122 -- p_address_id
3123 -- p_town_or_city
3124 -- p_style
3125 -- p_business_group_id
3126 -- p_object_version_number
3127 --
3128 -- Post Success:
3129 -- If address style is 'US', payroll is installed under US legislation and
3130 -- a row exist in pay_us_city_names for the given town_or_city, processing
3131 -- continues.
3132 --
3133 -- Post Failure:
3134 -- If address style is 'US', payroll is installed under US legislation and
3135 -- a row does not exist in pay_us_city_names for the given town_or_city,
3136 -- an application error is raised and processing terminates.
3137 --
3138 -- Access Status:
3139 -- Internal Table Handler Use Only.
3140 --
3141 -- {End Of Comments}
3142 -- ----------------------------------------------------------------------------
3143 procedure chk_town_or_city
3144 (p_address_id in per_addresses.address_id%TYPE
3145 ,p_style in per_addresses.style%TYPE
3146 ,p_town_or_city in per_addresses.town_or_city%TYPE
3147 ,p_business_group_id in per_addresses.business_group_id%TYPE
3148 ,p_object_version_number in per_addresses.object_version_number%TYPE)
3149 is
3150 --
3151 l_exists varchar2(1);
3152 l_proc varchar2(72) := g_package||'chk_town_or_city';
3153 l_api_updating boolean;
3154 --
3155 -- Declare cursor
3156 --
3157 cursor csr_valid_town_or_city is
3158 select null
3159 from pay_us_city_names
3160 where city_name = p_town_or_city;
3161 begin
3162 hr_utility.set_location('Entering:'|| l_proc, 1);
3163 --
3164 -- Check mandatory parameters have been set
3165 --
3166 hr_api.mandatory_arg_error
3167 (p_api_name => l_proc
3168 ,p_argument => 'style'
3169 ,p_argument_value => p_style
3170 );
3171 --
3172 -- Only proceed with validation if :
3173 -- a) The current g_old_rec is current and
3174 -- b) The value for town_or_city has changed
3175 --
3176 l_api_updating := per_add_shd.api_updating
3177 (p_address_id => p_address_id
3178 ,p_object_version_number => p_object_version_number);
3179 --
3180 if ((l_api_updating and
3181 nvl(per_add_shd.g_old_rec.town_or_city, hr_api.g_varchar2) <>
3182 nvl(p_town_or_city, hr_api.g_varchar2)) or
3183 (NOT l_api_updating))
3184 then
3185 hr_utility.set_location(l_proc, 2);
3186 --
3187 if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y' then
3188 --
3189 -- Check that the city is set
3190 --
3191 if p_town_or_city is null then
3192 --
3193 hr_utility.set_message(800, 'PER_52986_ADD_NO_CITY_SET');
3194 hr_utility.raise_error;
3195 --
3196 end if;
3197 --
3198 open csr_valid_town_or_city;
3199 fetch csr_valid_town_or_city into l_exists;
3200 if csr_valid_town_or_city%notfound then
3201 close csr_valid_town_or_city;
3202 hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3203 hr_utility.raise_error;
3204 end if;
3205 close csr_valid_town_or_city;
3206 hr_utility.set_location(l_proc, 4);
3207 --
3208 end if;
3209 --
3210 end if;
3211 --
3212 hr_utility.set_location(' Leaving:'|| l_proc, 5);
3213 exception
3214 when app_exception.application_exception then
3215 if hr_multi_message.exception_add
3216 (p_associated_column1 => 'PER_ADDRESSES.TOWN_OR_CITY'
3217 ) then
3218 hr_utility.set_location(' Leaving:'||l_proc,6);
3219 raise;
3220 end if;
3221 hr_utility.set_location(' Leaving:'||l_proc,7);
3222 --
3223 end chk_town_or_city;
3224 --
3225 -- ---------------------------------------------------------------------------
3226 -- |---------------------------< chk_tax_city >-----------------------------|
3227 -- ---------------------------------------------------------------------------
3228 --
3229 -- Description:
3230 -- If address style is 'US' and Payroll is installed under US legislation,
3231 -- validate tax_city (add_information18) exist in pay_us_city_names.
3232 --
3233 -- Pre-conditions:
3234 -- Style (p_style) must be valid.
3235 --
3236 -- In Arguments:
3237 -- p_address_id
3238 -- p_tax_city
3239 -- p_style
3240 -- p_business_group_id
3241 -- p_object_version_number
3242 --
3243 -- Post Success:
3244 -- If address style is 'US', payroll is installed under US legislation and
3245 -- a row exist in pay_us_city_names for the given tax_city, processing
3246 -- continues.
3247 --
3248 -- Post Failure:
3249 -- If address style is 'US', payroll is installed under US legislation and
3250 -- a row does not exist in pay_us_city_names for the given tax_city,
3251 -- an application error is raised and processing terminates.
3252 --
3253 -- Access Status:
3254 -- Internal Table Handler Use Only.
3255 --
3256 -- {End Of Comments}
3257 -- ----------------------------------------------------------------------------
3258 procedure chk_tax_city
3259 (p_address_id in per_addresses.address_id%TYPE
3260 ,p_style in per_addresses.style%TYPE
3261 ,p_tax_city in per_addresses.add_information18%TYPE
3262 ,p_business_group_id in per_addresses.business_group_id%TYPE
3263 ,p_object_version_number in per_addresses.object_version_number%TYPE)
3264 is
3265 --
3266 l_exists varchar2(1);
3267 l_proc varchar2(72) := g_package||'chk_tax_city';
3268 l_api_updating boolean;
3269 --
3270 -- Declare cursor
3271 --
3272 cursor csr_valid_tax_city is
3273 select null
3274 from pay_us_city_names
3275 where city_name = p_tax_city;
3276 begin
3277 if p_tax_city is not null
3278 then
3279 hr_utility.set_location('Entering:'|| l_proc, 1);
3280 --
3281 -- Check mandatory parameters have been set
3282 --
3283 hr_api.mandatory_arg_error
3284 (p_api_name => l_proc
3285 ,p_argument => 'style'
3286 ,p_argument_value => p_style
3287 );
3288 --
3289 -- Only proceed with validation if :
3290 -- a) The current g_old_rec is current and
3291 -- b) The value for tax_city has changed
3292 --
3293 l_api_updating := per_add_shd.api_updating
3294 (p_address_id => p_address_id
3295 ,p_object_version_number => p_object_version_number);
3296 --
3297 if ((l_api_updating and
3298 nvl(per_add_shd.g_old_rec.add_information18, hr_api.g_varchar2) <>
3299 nvl(p_tax_city, hr_api.g_varchar2)) or
3300 (NOT l_api_updating))
3301 then
3302 hr_utility.set_location(l_proc, 2);
3303 --
3304 if hr_general.chk_geocodes_installed = 'Y' then
3305 open csr_valid_tax_city;
3306 fetch csr_valid_tax_city into l_exists;
3307 if csr_valid_tax_city%notfound then
3308 close csr_valid_tax_city;
3309 hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3310 hr_utility.raise_error;
3311 end if;
3312 close csr_valid_tax_city;
3313 hr_utility.set_location(l_proc, 4);
3314 --
3315 end if;
3316 --
3317 end if;
3318 --
3319 end if;
3320 hr_utility.set_location(' Leaving:'|| l_proc, 5);
3321 exception
3322 when app_exception.application_exception then
3323 if hr_multi_message.exception_add
3324 (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION18'
3325 ) then
3326 hr_utility.set_location(' Leaving:'||l_proc,6);
3327 raise;
3328 end if;
3329 hr_utility.set_location(' Leaving:'||l_proc,7);
3330 --
3331 end chk_tax_city;
3332 --
3333 -- ---------------------------------------------------------------------------
3334 -- |-------------------< chk_city_state_zip_comb >----------------------------|
3335 -- ---------------------------------------------------------------------------
3336 --
3337 -- Description:
3338 -- Validates the city, state, county and zip code combination of a US
3339 -- address if payroll is installed under US legislation.
3340 -- If region_1 (county) is null then validation will occur without it.
3341 --
3342 -- Pre-conditions:
3343 -- Style (p_style) must be valid and payroll is installed under
3344 -- US legislation.
3345 --
3346 -- In Arguments:
3347 -- p_address_id
3348 -- p_style
3349 -- p_postal_code
3350 -- p_region_1
3351 -- p_region_2
3352 -- p_town_or_city
3353 -- p_business_group_id
3354 -- p_object_version_number
3355 --
3356 -- Post Success:
3357 -- Processing continues if:
3358 -- - the city, state and county combination is valid.
3359 -- - zip code is valid for the city and state.
3360 --
3361 -- Post Failure:
3362 -- Processing terminates if:
3363 -- - the city, state and county combination is not valid.
3364 -- - zip code is not valid for the city and state.
3365 --
3366 -- Access status:
3367 -- Internal Table Handler Use Only.
3368 --
3369 -- {End Of Comments}
3370 -- ----------------------------------------------------------------------------
3371 procedure chk_city_state_zip_comb
3372 (p_address_id in per_addresses.address_id%TYPE
3373 ,p_style in per_addresses.style%TYPE
3374 ,p_postal_code in per_addresses.postal_code%TYPE
3375 ,p_region_1 in per_addresses.region_1%TYPE
3376 ,p_region_2 in per_addresses.region_2%TYPE
3377 ,p_town_or_city in per_addresses.town_or_city%TYPE
3378 ,p_business_group_id in per_addresses.business_group_id%TYPE
3379 ,p_object_version_number in per_addresses.object_version_number%TYPE
3380 )
3381 is
3382 --
3383 l_proc varchar2(72) := g_package||'chk_city_state_zip_comb';
3384 l_api_updating boolean;
3385 l_exists number;
3386 l_city_code pay_us_city_names.city_code%TYPE;
3387 l_state_code pay_us_city_names.state_code%TYPE;
3388 l_postal_code varchar2(6);
3389 l_county_code varchar2(3);
3390 l_geocodes_installed varchar2(1);
3391 --
3392 cursor csr_valid_state_county
3393 is
3394 select st.state_code,
3395 cou.county_code
3396 from pay_us_states st,
3397 pay_us_counties cou
3398 where cou.state_code = st.state_code
3399 and cou.county_name =p_region_1
3400 and st.state_abbrev = p_region_2;
3401 --
3402 cursor csr_val_st_county_city
3403 is
3404 select cty.city_code
3405 from pay_us_city_names cty
3406 where cty.state_code = l_state_code
3407 and cty.county_code = l_county_code
3408 and cty.city_name = p_town_or_city;
3409 --
3410 cursor csr_valid_zip_code is
3411 select 1
3412 from pay_us_zip_codes zip,
3413 pay_us_city_names cty
3414 where zip.state_code = l_state_code
3415 and zip.county_code = l_county_code
3416 and cty.city_name = p_town_or_city
3417 and zip.state_code = cty.state_code
3418 and zip.county_code = cty.county_code
3419 and zip.city_code = cty.city_code
3420 and l_postal_code between zip.zip_start
3421 and zip.zip_end;
3422 --
3423 cursor csr_val_st_city
3424 is
3425 select st.state_code
3426 from pay_us_city_names cty
3427 , pay_us_states st
3428 where cty.state_code = st.state_code
3429 and cty.city_name = p_town_or_city
3430 and st.state_abbrev = p_region_2;
3431 --
3432 --
3433 cursor csr_valid_zip_code_no_ncty is
3434 select 1
3435 from pay_us_zip_codes zip,
3436 pay_us_city_names cty
3437 where cty.city_name = p_town_or_city
3438 and cty.state_code = l_state_code
3439 and zip.state_code = cty.state_code
3440 and zip.city_code = cty.city_code
3441 and l_postal_code between zip.zip_start
3442 and zip.zip_end;
3443 --
3444 begin
3445 hr_utility.set_location('Entering:'|| l_proc, 10);
3446 --
3447 -- Only proceed with validation if :
3448 -- a) US address style and payroll is installed under US legislation and
3449 -- b) The current g_old_rec is current and
3450 -- c) The value for postal_code/region_2/town_or_city has changed.
3451 --
3452 l_api_updating := per_add_shd.api_updating
3453 (p_address_id => p_address_id
3454 ,p_object_version_number => p_object_version_number);
3455 --
3456 if ((l_api_updating and
3457 (nvl(per_add_shd.g_old_rec.region_2, hr_api.g_varchar2) <>
3458 nvl(p_region_2, hr_api.g_varchar2)) or
3459 (nvl(per_add_shd.g_old_rec.region_1, hr_api.g_varchar2) <>
3460 nvl(p_region_1, hr_api.g_varchar2)) or
3461 (nvl(per_add_shd.g_old_rec.postal_code, hr_api.g_varchar2) <>
3462 nvl(p_postal_code, hr_api.g_varchar2)) or
3463 (nvl(per_add_shd.g_old_rec.town_or_city, hr_api.g_varchar2) <>
3464 nvl(p_town_or_city, hr_api.g_varchar2))) or
3465 (NOT l_api_updating)) then
3466 --
3467 hr_utility.set_location(l_proc, 20);
3468 --
3469 -- Check if US payroll installed.
3470 --
3471 l_geocodes_installed := hr_general.chk_geocodes_installed;
3472 --
3473 --
3474 -- If US address style and GEOCODES is installed, validate for right combination of
3475 -- city, state and county.
3476 --
3477 if p_style = 'US'
3478 and l_geocodes_installed = 'Y'
3479 then
3480 hr_utility.set_location(l_proc, 30);
3481 --
3482 -- Extract the first 5 characters of the zip code
3483 --
3484 l_postal_code := substr(p_postal_code,1,5);
3485 --
3486 --
3487 if (p_region_1 is null) then
3488 --
3489 if hr_multi_message.no_exclusive_error
3490 (p_check_column1 => 'PER_ADDRESSES.REGION_2'
3491 ,p_check_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3492 ) then
3493 --
3494 hr_utility.set_location(l_proc, 40);
3495 --
3496 -- no county is given, so procede with validation which excluses it;
3497 -- validate state and city;
3498 --
3499 open csr_val_st_city;
3500 fetch csr_val_st_city into l_state_code;
3501 if(csr_val_st_city%notfound) then
3502 close csr_val_st_city;
3503 hr_utility.set_location(l_proc, 50);
3504 hr_utility.set_message(800, 'PER_52531_ADD_INV_STCI_COMB');
3505 hr_multi_message.add
3506 (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3507 ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3508 );
3509 else
3510 close csr_val_st_city;
3511 hr_utility.set_location(l_proc, 60);
3512 --
3513 -- check for a valid state, city, zip combination
3514 --
3515 if hr_multi_message.no_exclusive_error
3516 (p_check_column1 => 'PER_ADDRESSES.POSTAL_CODE'
3517 ) then
3518 --
3519 open csr_valid_zip_code_no_ncty;
3520 fetch csr_valid_zip_code_no_ncty into l_exists;
3521 if csr_valid_zip_code_no_ncty%notfound then
3522 close csr_valid_zip_code_no_ncty;
3523 hr_utility.set_location(l_proc, 70);
3524 hr_utility.set_message(800, 'PER_52532_ADD_INV_STCIZ_COMB');
3525 hr_multi_message.add
3526 (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3527 ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3528 ,p_associated_column3 => 'PER_ADDRESSES.POSTAL_CODE'
3529 );
3530 else
3531 close csr_valid_zip_code_no_ncty;
3532 hr_utility.set_location(l_proc, 80);
3533 end if;
3534 --
3535 end if; -- no_exclusive_error check for POSTAL_CODE
3536 --
3537 end if;
3538 --
3539 end if; -- no_exclusive_error check for REGION_2 and TOWN_OR_CITY
3540 --
3541 else -- REGION_1 is not null
3542 --
3543 -- The county is supplied, so validate with it.
3544 --
3545 -- Validate the state and county combination
3546 --
3547 if hr_multi_message.no_exclusive_error
3548 (p_check_column1 => 'PER_ADDRESSES.REGION_1'
3549 ,p_check_column2 => 'PER_ADDRESSES.REGION_2'
3550 ) then
3551 --
3552 hr_utility.set_location(l_proc, 90);
3553 open csr_valid_state_county;
3554 fetch csr_valid_state_county into l_state_code, l_county_code;
3555 --
3556 if csr_valid_state_county%notfound then
3557 close csr_valid_state_county;
3558 --
3559 hr_utility.set_location(l_proc, 100);
3560 hr_utility.set_message(800, 'PER_52988_ADD_INV_STCOU_COMB');
3561 hr_multi_message.add
3562 (p_associated_column1 => 'PER_ADDRESSES.REGION_1'
3563 ,p_associated_column2 => 'PER_ADDRESSES.REGION_2'
3564 );
3565 else
3566 close csr_valid_state_county;
3567 hr_utility.set_location(l_proc, 110);
3568 end if;
3569 --
3570 -- Validate the state, county and city combination
3571 --
3572 if hr_multi_message.no_exclusive_error
3573 (p_check_column1 => 'PER_ADDRESSES.TOWN_OR_CITY'
3574 ) then
3575 open csr_val_st_county_city;
3576 fetch csr_val_st_county_city into l_city_code;
3577 --
3578 if csr_val_st_county_city%notfound then
3579 close csr_val_st_county_city;
3580 --
3581 hr_utility.set_location(l_proc, 120);
3582 hr_utility.set_message(800, 'PER_52987_ADD_INV_STCOCY_COMB');
3583 hr_multi_message.add
3584 (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3585 ,p_associated_column2 => 'PER_ADDRESSES.REGION_1'
3586 ,p_associated_column3 => 'PER_ADDRESSES.TOWN_OR_CITY'
3587 );
3588 else
3589 close csr_val_st_county_city;
3590 hr_utility.set_location(l_proc, 130);
3591 end if;
3592 --
3593 -- Validate the state, county, city and zip code combination
3594 --
3595 if hr_multi_message.no_exclusive_error
3596 (p_check_column1 => 'PER_ADDRESSES.POSTAL_CODE'
3597 ) then
3598 --
3599 open csr_valid_zip_code;
3600 fetch csr_valid_zip_code into l_exists;
3601 if csr_valid_zip_code%notfound then
3602 close csr_valid_zip_code;
3603 --
3604 hr_utility.set_location(l_proc, 140);
3605 hr_utility.set_message(801, 'HR_51282_ADD_INV_ZIP_FOR_CITY');
3606 hr_multi_message.add
3607 (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3608 ,p_associated_column2 => 'PER_ADDRESSES.REGION_1'
3609 ,p_associated_column3 => 'PER_ADDRESSES.TOWN_OR_CITY'
3610 ,p_associated_column4 => 'PER_ADDRESSES.POSTAL_CODE'
3611 );
3612 else
3613 close csr_valid_zip_code;
3614 hr_utility.set_location(l_proc, 150);
3615 end if;
3616 --
3617 end if; -- no_exclusive_error check for postal_code
3618 --
3619 end if; -- no_exclusive_error check for town_or_city
3620 --
3621 end if; -- no_exclusive_error check for region_1 and region_2
3622 --
3623 end if; -- end if for valid region_1
3624 --
3625 end if; -- end if for p_style = 'US'
3626 --
3627 end if; -- end if for api_updating check
3628 hr_utility.set_location(' Leaving:'|| l_proc, 160);
3629 --
3630 end chk_city_state_zip_comb;
3631 --
3632 --
3633 -- ---------------------------------------------------------------------------
3634 -- |-------------------< chk_tax_city_state_zip_comb >-----------------------|
3635 -- ---------------------------------------------------------------------------
3636 --
3637 -- Description:
3638 -- Validates the city, state, county and zip code combination for taxation
3639 -- address of a US address if payroll is installed under US legislation.
3640 -- If add_information19 (tax county) is null then validation will occur
3641 -- without it.
3642 --
3643 -- Pre-conditions:
3644 -- Style (p_style) must be valid and payroll is installed under
3645 -- US legislation.
3646 --
3647 -- In Arguments:
3648 -- p_address_id
3649 -- p_style
3650 -- p_tax_zip (add_information20)
3651 -- p_tax_county (add_information19)
3652 -- p_tax_state (add_information17)
3653 -- p_tax_city (add_information18)
3654 -- p_business_group_id
3655 -- p_object_version_number
3656 --
3657 -- Post Success:
3658 -- Processing continues if:
3659 -- - the taxation city, state and county combination is valid.
3660 -- - zip code is valid for the taxation city and state.
3661 --
3662 -- Post Failure:
3663 -- Processing terminates if:
3664 -- - the taxation city, state and county combination is not valid.
3665 -- - zip code is not valid for the taxation city and state.
3666 --
3667 -- Access status:
3668 -- Internal Table Handler Use Only.
3669 --
3670 -- {End Of Comments}
3671 -- ----------------------------------------------------------------------------
3672 procedure chk_tax_city_state_zip_comb
3673 (p_address_id in per_addresses.address_id%TYPE
3674 ,p_style in per_addresses.style%TYPE
3675 ,p_tax_zip in per_addresses.add_information20%TYPE
3676 ,p_tax_county in per_addresses.add_information19%TYPE
3677 ,p_tax_state in per_addresses.add_information17%TYPE
3678 ,p_tax_city in per_addresses.add_information18%TYPE
3679 ,p_business_group_id in per_addresses.business_group_id%TYPE
3680 ,p_object_version_number in per_addresses.object_version_number%TYPE
3681 )
3682 is
3683 --
3684 l_proc varchar2(72) := g_package||'chk_tax_city_state_zip_comb';
3685 l_api_updating boolean;
3686 l_exists number;
3687 l_city_code pay_us_city_names.city_code%TYPE;
3688 l_state_code pay_us_city_names.state_code%TYPE;
3689 l_postal_code varchar2(6);
3690 l_county_code varchar2(3);
3691 l_geocodes_installed varchar2(1);
3692 --
3693 cursor csr_valid_state_county
3694 is
3695 select st.state_code,
3696 cou.county_code
3697 from pay_us_states st,
3698 pay_us_counties cou
3699 where cou.state_code = st.state_code
3700 and cou.county_name = p_tax_county
3701 and st.state_abbrev = p_tax_state;
3702 --
3703 cursor csr_val_st_county_city
3704 is
3705 select cty.city_code
3706 from pay_us_city_names cty
3707 where cty.state_code = l_state_code
3708 and cty.county_code = l_county_code
3709 and cty.city_name = p_tax_city;
3710 --
3711 cursor csr_valid_zip_code is
3712 select 1
3713 from pay_us_zip_codes zip,
3714 pay_us_city_names cty
3715 where zip.state_code = l_state_code
3716 and zip.county_code = l_county_code
3717 and cty.city_name = p_tax_city
3718 and zip.state_code = cty.state_code
3719 and zip.county_code = cty.county_code
3720 and zip.city_code = cty.city_code
3721 and l_postal_code between zip.zip_start
3722 and zip.zip_end;
3723 --
3724 cursor csr_val_st_city
3725 is
3726 select st.state_code
3727 from pay_us_city_names cty
3728 , pay_us_states st
3729 where cty.state_code = st.state_code
3730 and cty.city_name = p_tax_city
3731 and st.state_abbrev = p_tax_state;
3732 --
3733 --
3734 cursor csr_valid_zip_code_no_ncty is
3735 select 1
3736 from pay_us_zip_codes zip,
3737 pay_us_city_names cty
3738 where cty.city_name = p_tax_city
3739 and cty.state_code = l_state_code
3740 and zip.state_code = cty.state_code
3741 and zip.city_code = cty.city_code
3742 and l_postal_code between zip.zip_start
3743 and zip.zip_end;
3744 --
3745 begin
3746 if p_tax_city is not null and
3747 p_tax_state is not null and
3748 p_tax_zip is not null
3749 then
3750 hr_utility.set_location('Entering:'|| l_proc, 10);
3751 --
3752 -- Only proceed with validation if :
3753 -- a) US address style and payroll is installed under US legislation and
3754 -- b) The current g_old_rec is current and
3755 -- c) The value for tax_zip (add_information20)/tax_state(add_information17)
3756 -- /tax_city(add_information18) has changed.
3757 --
3758 l_api_updating := per_add_shd.api_updating
3759 (p_address_id => p_address_id
3760 ,p_object_version_number => p_object_version_number);
3761 --
3762 if ((l_api_updating and
3763 (nvl(per_add_shd.g_old_rec.add_information17, hr_api.g_varchar2) <>
3764 nvl(p_tax_city, hr_api.g_varchar2)) or
3765 (nvl(per_add_shd.g_old_rec.add_information19, hr_api.g_varchar2) <>
3766 nvl(p_tax_county, hr_api.g_varchar2)) or
3767 (nvl(per_add_shd.g_old_rec.add_information20, hr_api.g_varchar2) <>
3768 nvl(p_tax_zip, hr_api.g_varchar2)) or
3769 (nvl(per_add_shd.g_old_rec.add_information18, hr_api.g_varchar2) <>
3770 nvl(p_tax_city, hr_api.g_varchar2))) or
3771 (NOT l_api_updating)) then
3772 --
3773 hr_utility.set_location(l_proc, 20);
3774 --
3775 -- Check if US payroll installed.
3776 --
3777 l_geocodes_installed := hr_general.chk_geocodes_installed;
3778 --
3779 --
3780 -- If US address style and GEOCODES is installed, validate for right combination of
3781 -- city, state and county.
3782 --
3783 If l_geocodes_installed = 'Y'
3784 then
3785 hr_utility.set_location(l_proc, 30);
3786 --
3787 -- Extract the first 5 characters of the zip code
3788 --
3789 l_postal_code := substr(p_tax_zip,1,5);
3790 --
3791 --
3792 if (p_tax_county is null) then
3793 if hr_multi_message.no_exclusive_error
3794 (p_check_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3795 ,p_check_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3796 ) then
3797 hr_utility.set_location(l_proc, 40);
3798 --
3799 -- no county is given, so procede with validation which excluses it;
3800 -- validate state and city;
3801 --
3802 open csr_val_st_city;
3803 fetch csr_val_st_city into l_state_code;
3804 if(csr_val_st_city%notfound) then
3805 close csr_val_st_city;
3806 hr_utility.set_location(l_proc, 50);
3807 hr_utility.set_message(800, 'PER_52531_ADD_INV_STCI_COMB');
3808 hr_multi_message.add
3809 (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3810 ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3811 );
3812 else
3813 close csr_val_st_city;
3814 hr_utility.set_location(l_proc, 60);
3815 --
3816 -- check for a valid state, city, zip combination
3817 --
3818 if hr_multi_message.no_exclusive_error
3819 (p_check_column1 => 'PER_ADDRESSES.ADD_INFORMATION20'
3820 ) then
3821 open csr_valid_zip_code_no_ncty;
3822 fetch csr_valid_zip_code_no_ncty into l_exists;
3823 if csr_valid_zip_code_no_ncty%notfound then
3824 close csr_valid_zip_code_no_ncty;
3825 hr_utility.set_location(l_proc, 70);
3826 hr_utility.set_message(800, 'PER_52532_ADD_INV_STCIZ_COMB');
3827 hr_multi_message.add
3828 (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3829 ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3830 ,p_associated_column3 => 'PER_ADDRESSES.ADD_INFORMATION20'
3831 );
3832 else
3833 --
3834 close csr_valid_zip_code_no_ncty;
3835 hr_utility.set_location(l_proc, 80);
3836 --
3837 end if;
3838 --
3839 end if; -- no_exclusive_error for ADD_INFORMATION20(p_tax_zip)
3840 --
3841 end if;
3842 --
3843 end if; -- no_exclusive_error for ADD_INFORMATION17(p_tax_state)
3844 -- and ADD_INFORMATION18(p_tax_city)
3845 else -- if county is not null
3846 --
3847 -- The county is supplied, so validate with it.
3848 --
3849 -- Validate the state and county combination
3850 --
3851 if hr_multi_message.no_exclusive_error
3852 (p_check_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3853 ,p_check_column2 => 'PER_ADDRESSES.ADD_INFORMATION19'
3854 ) then
3855 --
3856 hr_utility.set_location(l_proc, 90);
3857 open csr_valid_state_county;
3858 fetch csr_valid_state_county into l_state_code, l_county_code;
3859 --
3860 if csr_valid_state_county%notfound then
3861 close csr_valid_state_county;
3862 --
3863 hr_utility.set_location(l_proc, 100);
3864 hr_utility.set_message(800, 'PER_52988_ADD_INV_STCOU_COMB');
3865 hr_multi_message.add
3866 (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3867 ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION19'
3868 );
3869 --
3870 else
3871 --
3872 close csr_valid_state_county;
3873 hr_utility.set_location(l_proc, 110);
3874 --
3875 end if;
3876 --
3877 -- Validate the state, county and city combination
3878 --
3879 if hr_multi_message.no_exclusive_error
3880 (p_check_column1 => 'PER_ADDRESSES.ADD_INFORMATION18'
3881 ) then
3882 open csr_val_st_county_city;
3883 fetch csr_val_st_county_city into l_city_code;
3884 --
3885 if csr_val_st_county_city%notfound then
3886 close csr_val_st_county_city;
3887 --
3888 hr_utility.set_location(l_proc, 120);
3889 hr_utility.set_message(800, 'PER_52987_ADD_INV_STCOCY_COMB');
3890 hr_multi_message.add
3891 (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3892 ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3893 ,p_associated_column3 => 'PER_ADDRESSES.ADD_INFORMATION19'
3894 );
3895 --
3896 else
3897 --
3898 close csr_val_st_county_city;
3899 hr_utility.set_location(l_proc, 130);
3900 --
3901 end if;
3902 --
3903 -- Validate the state, county, city and zip code combination
3904 --
3905 if hr_multi_message.no_exclusive_error
3906 (p_check_column1 => 'PER_ADDRESSES.ADD_INFORMATION20'
3907 ) then
3908 open csr_valid_zip_code;
3909 fetch csr_valid_zip_code into l_exists;
3910 if csr_valid_zip_code%notfound then
3911 close csr_valid_zip_code;
3912 --
3913 hr_utility.set_location(l_proc, 140);
3914 hr_utility.set_message(801, 'HR_51282_ADD_INV_ZIP_FOR_CITY');
3915 hr_multi_message.add
3916 (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3917 ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3918 ,p_associated_column3 => 'PER_ADDRESSES.ADD_INFORMATION19'
3919 ,p_associated_column4 => 'PER_ADDRESSES.ADD_INFORMATION20'
3920 );
3921 --
3922 else
3923 --
3924 close csr_valid_zip_code;
3925 hr_utility.set_location(l_proc, 150);
3926 --
3927 end if;
3928 --
3929 end if; -- no_exclusive_error for ADD_INFORMATION20(p_tax_zip)
3930 --
3931 end if; -- no_exclusive_error for ADD_INFORMATION18(p_tax_city)
3932 --
3933 end if; -- no_exclusive_error for ADD_INFORMATION17(p_tax_state)
3934 -- and ADD_INFORMATION19(p_tax_county)
3935 end if;
3936 --
3937 end if;
3938 --
3939 end if;
3940 end if;
3941 hr_utility.set_location(' Leaving:'|| l_proc, 160);
3942 --
3943 end chk_tax_city_state_zip_comb;
3944 --
3945 -- ---------------------------------------------------------------------------
3946 -- |--------------------------< chk_del_address >----------------------------|
3947 -- ---------------------------------------------------------------------------
3948 --
3949 -- Description:
3950 -- Validates that primary addresses can only be deleted within a given
3951 -- date range if no other non primary addresses exist within that same
3952 -- date range
3953 --
3954 -- Validates that primary addresses can only be deleted if they do not
3955 -- break the contiguous nature of a primary address.
3956 --
3957 -- Non primary addresses can be deleted without any validation being
3958 -- performed
3959 --
3960 -- Pre-conditions:
3961 -- None
3962 -- In Arguments:
3963 -- None
3964 --
3965 -- Post Success:
3966 -- If no non-primary exist within the date range of the primary address
3967 -- selected for deletion and the deletion does not break up the
3968 -- the contiguous nature of the primary address then processing continues
3969 --
3970 -- Post Failure:
3971 -- If a non primary address exists within the date range of the primary
3972 -- address selected for deletion or the deletion breaks up the contiguous
3973 -- nature of the primary address then an application error is raised and
3974 -- processing terminated
3975 --
3976 -- Access Status:
3977 -- Internal Table Handler Use Only.
3978 --
3979 -- {End Of Comments}
3980 -- ----------------------------------------------------------------------------
3981 procedure chk_del_address is
3982 --
3983 l_exists varchar2(1);
3984 l_proc varchar2(72) := g_package||'chk_del_address';
3985 l_date date;
3986 --
3987 cursor csr_del_address is
3988 select null
3989 from per_addresses pa
3990 where pa.date_from between per_add_shd.g_old_rec.date_from
3991 and l_date
3992 and pa.person_id = per_add_shd.g_old_rec.person_id
3993 and pa.primary_flag = 'N';
3994 --
3995 cursor csr_no_del_contig_add is
3996 select null
3997 from sys.dual
3998 where exists(select null
3999 from per_addresses pa2
4000 where pa2.date_from > l_date
4001 and pa2.person_id = per_add_shd.g_old_rec.person_id
4002 and pa2.primary_flag = 'Y');
4003 --
4004 begin
4005 l_date := nvl(per_add_shd.g_old_rec.date_to, hr_api.g_eot);
4006 hr_utility.set_location('Entering:'|| l_proc, 1);
4007 --
4008 -- For primary addresses only
4009 -- ==========================
4010 --
4011 if per_add_shd.g_old_rec.primary_flag = 'Y' then
4012 --
4013 -- Check that no non primary addresses
4014 -- exist within the date range of the
4015 -- currently selected primary address.
4016 -- Non primary addresses can be deleted
4017 -- at any time
4018 --
4019 open csr_del_address;
4020 fetch csr_del_address into l_exists;
4021 if csr_del_address%found then
4022 close csr_del_address;
4023 hr_utility.set_message(801, 'HR_7308_ADD_PRIMARY_DEL');
4024 hr_utility.raise_error;
4025 end if;
4026 close csr_del_address;
4027 hr_utility.set_location(l_proc, 2);
4028 --
4029 -- Check that the deletion of a primary
4030 -- address does not break the contiguous
4031 -- nature of the address
4032 --
4033 open csr_no_del_contig_add;
4034 fetch csr_no_del_contig_add into l_exists;
4035 if csr_no_del_contig_add%found then
4036 close csr_no_del_contig_add;
4037 hr_utility.set_message(801, 'HR_51030_ADDR_PRIM_GAP');
4038 hr_utility.raise_error;
4039 end if;
4040 close csr_no_del_contig_add;
4041 end if;
4042 --
4043 hr_utility.set_location(' Leaving:'|| l_proc, 3);
4044 hr_utility.set_location(' Leaving:'||l_proc,5);
4045 end chk_del_address;
4046 --
4047 -- ----------------------------------------------------------------------------
4048 -- |----------------------< check_non_updateable_args >-----------------------|
4049 -- ----------------------------------------------------------------------------
4050 -- {Start Of Comments}
4051 --
4052 -- Description:
4053 -- This procedure is used to ensure that non updateable attributes have
4054 -- not been updated. If an attribute has been updated an error is generated.
4055 --
4056 -- Pre Conditions:
4057 -- g_old_rec has been populated with details of the values currently in
4058 -- the database.
4059 --
4060 -- In Arguments:
4061 -- p_rec has been populated with the updated values the user would like the
4062 -- record set to.
4063 --
4064 -- Post Success:
4065 -- Processing continues if all the non updateable attributes have not
4066 -- changed.
4067 --
4068 -- Post Failure:
4069 -- An application error is raised if any of the non updatable attributes
4070 -- (business_group_id, person_id, address_id, primary_flag or style)
4071 -- have been altered.
4072 --
4073 -- {End Of Comments}
4074 Procedure check_non_updateable_args(p_rec in per_add_shd.g_rec_type) is
4075 --
4076 l_proc varchar2(72) := g_package||'check_non_updateable_args';
4077 --
4078 Begin
4079 hr_utility.set_location('Entering:'||l_proc, 5);
4080 --
4081 -- Only proceed with validation if a row exists for
4082 -- the current record in the HR Schema
4083 --
4084 if not per_add_shd.api_updating
4085 (p_address_id => p_rec.address_id,
4086 p_object_version_number => p_rec.object_version_number) then
4087 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4088 hr_utility.set_message_token('PROCEDURE', l_proc);
4089 hr_utility.set_message_token('STEP', '5');
4090 end if;
4091 --
4092 hr_utility.set_location(l_proc, 6);
4093 --
4094 -- start of commenting the code for business_group_id and person_id
4095 -- are updateable if currently null
4096 /*
4097 if nvl(p_rec.business_group_id, hr_api.g_number) <>
4098 per_add_shd.g_old_rec.business_group_id then
4099 hr_api.argument_changed_error
4100 (p_api_name => l_proc
4101 ,p_argument => 'BUSINESS_GROUP_ID'
4102 ,p_base_table => per_add_shd.g_tab_nam
4103 );
4104 end if;
4105 hr_utility.set_location(l_proc, 7);
4106 --
4107 if nvl(p_rec.person_id, hr_api.g_number) <>
4108 per_add_shd.g_old_rec.person_id then
4109 hr_api.argument_changed_error
4110 (p_api_name => l_proc
4111 ,p_argument => 'PERSON_ID'
4112 ,p_base_table => per_add_shd.g_tab_nam
4113 );
4114 end if;
4115 */
4116 -- end of commenting code
4117 --
4118 hr_utility.set_location(l_proc, 8);
4119 --
4120 if not g_called_from_form then
4121 if nvl(p_rec.primary_flag, hr_api.g_varchar2) <>
4122 per_add_shd.g_old_rec.primary_flag then
4123 hr_api.argument_changed_error
4124 (p_api_name => l_proc
4125 ,p_argument => 'PRIMARY_FLAG'
4126 ,p_base_table => per_add_shd.g_tab_nam
4127 );
4128 end if;
4129 end if;
4130 hr_utility.set_location(l_proc, 11);
4131 --
4132 hr_utility.set_location(' Leaving:'||l_proc, 12);
4133 end check_non_updateable_args;
4134 --
4135 -- -----------------------------------------------------------------------------
4136 -- |--------------------------------< chk_df >---------------------------------|
4137 -- -----------------------------------------------------------------------------
4138 --
4139 procedure chk_df
4140 (p_rec in per_add_shd.g_rec_type) is
4141 --
4142 l_proc varchar2(72) := g_package||'chk_df';
4143 --
4144 Begin
4145 hr_utility.set_location('Entering:'||l_proc, 5);
4146 --
4147 -- Check if the row is being inserted or updated and a
4148 -- value has changed
4149 --
4150 if (p_rec.address_id is null)
4151 or ((p_rec.address_id is not null)
4152 and (nvl(per_add_shd.g_old_rec.addr_attribute_category, hr_api.g_varchar2)
4153 <> nvl(p_rec.addr_attribute_category, hr_api.g_varchar2)
4154 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE1, hr_api.g_varchar2)
4155 <> nvl(p_rec.ADDR_ATTRIBUTE1, hr_api.g_varchar2)
4156 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE2, hr_api.g_varchar2)
4157 <> nvl(p_rec.ADDR_ATTRIBUTE2, hr_api.g_varchar2)
4158 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE3, hr_api.g_varchar2)
4159 <> nvl(p_rec.ADDR_ATTRIBUTE3, hr_api.g_varchar2)
4160 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE4, hr_api.g_varchar2)
4161 <> nvl(p_rec.ADDR_ATTRIBUTE4, hr_api.g_varchar2)
4162 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE5, hr_api.g_varchar2)
4163 <> nvl(p_rec.ADDR_ATTRIBUTE5, hr_api.g_varchar2)
4164 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE6, hr_api.g_varchar2)
4165 <> nvl(p_rec.ADDR_ATTRIBUTE6, hr_api.g_varchar2)
4166 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE7, hr_api.g_varchar2)
4167 <> nvl(p_rec.ADDR_ATTRIBUTE7, hr_api.g_varchar2)
4168 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE8, hr_api.g_varchar2)
4169 <> nvl(p_rec.ADDR_ATTRIBUTE8, hr_api.g_varchar2)
4170 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE9, hr_api.g_varchar2)
4171 <> nvl(p_rec.ADDR_ATTRIBUTE9, hr_api.g_varchar2)
4172 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE10, hr_api.g_varchar2)
4173 <> nvl(p_rec.ADDR_ATTRIBUTE10, hr_api.g_varchar2)
4174 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE11, hr_api.g_varchar2)
4175 <> nvl(p_rec.ADDR_ATTRIBUTE11, hr_api.g_varchar2)
4176 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE12, hr_api.g_varchar2)
4177 <> nvl(p_rec.ADDR_ATTRIBUTE12, hr_api.g_varchar2)
4178 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE13, hr_api.g_varchar2)
4179 <> nvl(p_rec.ADDR_ATTRIBUTE13, hr_api.g_varchar2)
4180 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE14, hr_api.g_varchar2)
4181 <> nvl(p_rec.ADDR_ATTRIBUTE14, hr_api.g_varchar2)
4182 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE15, hr_api.g_varchar2)
4183 <> nvl(p_rec.ADDR_ATTRIBUTE15, hr_api.g_varchar2)
4184 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE16, hr_api.g_varchar2)
4185 <> nvl(p_rec.ADDR_ATTRIBUTE16, hr_api.g_varchar2)
4186 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE17, hr_api.g_varchar2)
4187 <> nvl(p_rec.ADDR_ATTRIBUTE17, hr_api.g_varchar2)
4188 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE18, hr_api.g_varchar2)
4189 <> nvl(p_rec.ADDR_ATTRIBUTE18, hr_api.g_varchar2)
4190 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE19, hr_api.g_varchar2)
4191 <> nvl(p_rec.ADDR_ATTRIBUTE19, hr_api.g_varchar2)
4192 or nvl(per_add_shd.g_old_rec.ADDR_ATTRIBUTE20, hr_api.g_varchar2)
4193 <> nvl(p_rec.ADDR_ATTRIBUTE20, hr_api.g_varchar2)
4194 )
4195 )
4196 then
4197 --
4198 hr_dflex_utility.ins_or_upd_descflex_attribs
4199 (p_appl_short_name => 'PER'
4200 ,p_descflex_name => 'PER_ADDRESSES'
4201 ,p_attribute_category => p_rec.addr_attribute_category
4202 ,p_attribute1_name => 'ADDR_ATTRIBUTE1'
4203 ,p_attribute1_value => p_rec.ADDR_ATTRIBUTE1
4204 ,p_attribute2_name => 'ADDR_ATTRIBUTE2'
4205 ,p_attribute2_value => p_rec.ADDR_ATTRIBUTE2
4206 ,p_attribute3_name => 'ADDR_ATTRIBUTE3'
4207 ,p_attribute3_value => p_rec.ADDR_ATTRIBUTE3
4208 ,p_attribute4_name => 'ADDR_ATTRIBUTE4'
4209 ,p_attribute4_value => p_rec.ADDR_ATTRIBUTE4
4210 ,p_attribute5_name => 'ADDR_ATTRIBUTE5'
4211 ,p_attribute5_value => p_rec.ADDR_ATTRIBUTE5
4212 ,p_attribute6_name => 'ADDR_ATTRIBUTE6'
4213 ,p_attribute6_value => p_rec.ADDR_ATTRIBUTE6
4214 ,p_attribute7_name => 'ADDR_ATTRIBUTE7'
4215 ,p_attribute7_value => p_rec.ADDR_ATTRIBUTE7
4216 ,p_attribute8_name => 'ADDR_ATTRIBUTE8'
4217 ,p_attribute8_value => p_rec.ADDR_ATTRIBUTE8
4218 ,p_attribute9_name => 'ADDR_ATTRIBUTE9'
4219 ,p_attribute9_value => p_rec.ADDR_ATTRIBUTE9
4220 ,p_attribute10_name => 'ADDR_ATTRIBUTE10'
4221 ,p_attribute10_value => p_rec.ADDR_ATTRIBUTE10
4222 ,p_attribute11_name => 'ADDR_ATTRIBUTE11'
4223 ,p_attribute11_value => p_rec.ADDR_ATTRIBUTE11
4224 ,p_attribute12_name => 'ADDR_ATTRIBUTE12'
4225 ,p_attribute12_value => p_rec.ADDR_ATTRIBUTE12
4226 ,p_attribute13_name => 'ADDR_ATTRIBUTE13'
4227 ,p_attribute13_value => p_rec.ADDR_ATTRIBUTE13
4228 ,p_attribute14_name => 'ADDR_ATTRIBUTE14'
4229 ,p_attribute14_value => p_rec.ADDR_ATTRIBUTE14
4230 ,p_attribute15_name => 'ADDR_ATTRIBUTE15'
4231 ,p_attribute15_value => p_rec.ADDR_ATTRIBUTE15
4232 ,p_attribute16_name => 'ADDR_ATTRIBUTE16'
4233 ,p_attribute16_value => p_rec.ADDR_ATTRIBUTE16
4234 ,p_attribute17_name => 'ADDR_ATTRIBUTE17'
4235 ,p_attribute17_value => p_rec.ADDR_ATTRIBUTE17
4236 ,p_attribute18_name => 'ADDR_ATTRIBUTE18'
4237 ,p_attribute18_value => p_rec.ADDR_ATTRIBUTE18
4238 ,p_attribute19_name => 'ADDR_ATTRIBUTE19'
4239 ,p_attribute19_value => p_rec.ADDR_ATTRIBUTE19
4240 ,p_attribute20_name => 'ADDR_ATTRIBUTE20'
4241 ,p_attribute20_value => p_rec.ADDR_ATTRIBUTE20
4242 );
4243 --
4244 end if;
4245 --
4246 hr_utility.set_location(' Leaving:'||l_proc, 10);
4247 end chk_df;
4248 --
4249 -- -----------------------------------------------------------------------------
4250 -- |-------------------------------< chk_ddf >---------------------------------|
4251 -- -----------------------------------------------------------------------------
4252 --
4253 procedure chk_ddf
4254 (p_rec in per_add_shd.g_rec_type) is
4255 --
4256 l_proc varchar2(72) := g_package||'chk_ddf';
4257 l_error exception;
4258 --
4259 Begin
4260 hr_utility.set_location('Entering:'||l_proc, 5);
4261 --
4262 -- Check if the row is being inserted or updated and a
4263 -- value has changed
4264 --
4265 if (p_rec.address_id is null)
4266 or ((p_rec.address_id is not null)
4267 and (nvl(per_add_shd.g_old_rec.style, hr_api.g_varchar2)
4268 <> nvl(p_rec.style, hr_api.g_varchar2)
4269 or nvl(per_add_shd.g_old_rec.address_line1, hr_api.g_varchar2)
4270 <> nvl(p_rec.address_line1, hr_api.g_varchar2)
4271 or nvl(per_add_shd.g_old_rec.address_line2, hr_api.g_varchar2)
4272 <> nvl(p_rec.address_line2, hr_api.g_varchar2)
4273 or nvl(per_add_shd.g_old_rec.address_line3, hr_api.g_varchar2)
4274 <> nvl(p_rec.address_line3, hr_api.g_varchar2)
4275 or nvl(per_add_shd.g_old_rec.town_or_city, hr_api.g_varchar2)
4276 <> nvl(p_rec.town_or_city, hr_api.g_varchar2)
4277 or nvl(per_add_shd.g_old_rec.region_1, hr_api.g_varchar2)
4278 <> nvl(p_rec.region_1, hr_api.g_varchar2)
4279 or nvl(per_add_shd.g_old_rec.region_2, hr_api.g_varchar2)
4280 <> nvl(p_rec.region_2, hr_api.g_varchar2)
4281 or nvl(per_add_shd.g_old_rec.region_3, hr_api.g_varchar2)
4282 <> nvl(p_rec.region_3, hr_api.g_varchar2)
4283 or nvl(per_add_shd.g_old_rec.postal_code, hr_api.g_varchar2)
4284 <> nvl(p_rec.postal_code, hr_api.g_varchar2)
4285 or nvl(per_add_shd.g_old_rec.country, hr_api.g_varchar2)
4286 <> nvl(p_rec.country, hr_api.g_varchar2)
4287 or nvl(per_add_shd.g_old_rec.telephone_number_1, hr_api.g_varchar2)
4288 <> nvl(p_rec.telephone_number_1, hr_api.g_varchar2)
4289 or nvl(per_add_shd.g_old_rec.telephone_number_2, hr_api.g_varchar2)
4290 <> nvl(p_rec.telephone_number_2, hr_api.g_varchar2)
4291 or nvl(per_add_shd.g_old_rec.telephone_number_3, hr_api.g_varchar2)
4292 <> nvl(p_rec.telephone_number_3, hr_api.g_varchar2)
4293 or nvl(per_add_shd.g_old_rec.add_information18, hr_api.g_varchar2)
4294 <> nvl(p_rec.add_information13, hr_api.g_varchar2)
4295 or nvl(per_add_shd.g_old_rec.add_information13, hr_api.g_varchar2)
4296 <> nvl(p_rec.add_information14, hr_api.g_varchar2)
4297 or nvl(per_add_shd.g_old_rec.add_information14, hr_api.g_varchar2)
4298 <> nvl(p_rec.add_information15, hr_api.g_varchar2)
4299 or nvl(per_add_shd.g_old_rec.add_information15, hr_api.g_varchar2)
4300 <> nvl(p_rec.add_information16, hr_api.g_varchar2)
4301 or nvl(per_add_shd.g_old_rec.add_information16, hr_api.g_varchar2) --Start of new code for Bug #2164019
4302 <> nvl(p_rec.add_information17, hr_api.g_varchar2)
4303 or nvl(per_add_shd.g_old_rec.add_information18, hr_api.g_varchar2)
4304 <> nvl(p_rec.add_information18, hr_api.g_varchar2)
4305 or nvl(per_add_shd.g_old_rec.add_information19, hr_api.g_varchar2)
4306 <> nvl(p_rec.add_information19, hr_api.g_varchar2)
4307 or nvl(per_add_shd.g_old_rec.add_information20, hr_api.g_varchar2)
4308 <> nvl(p_rec.add_information20, hr_api.g_varchar2) --End of new code for Bug #2164019
4309 )
4310 )
4311 then
4312 --
4313 hr_dflex_utility.ins_or_upd_descflex_attribs
4314 (p_appl_short_name => 'PER'
4315 ,p_descflex_name => 'Address Structure'
4316 ,p_attribute_category => p_rec.style
4317 ,p_attribute1_name => 'ADDRESS_LINE1'
4318 ,p_attribute1_value => p_rec.address_line1
4319 ,p_attribute2_name => 'ADDRESS_LINE2'
4320 ,p_attribute2_value => p_rec.address_line2
4321 ,p_attribute3_name => 'ADDRESS_LINE3'
4322 ,p_attribute3_value => p_rec.address_line3
4323 ,p_attribute4_name => 'TOWN_OR_CITY'
4324 ,p_attribute4_value => p_rec.town_or_city
4325 ,p_attribute5_name => 'REGION_1'
4326 ,p_attribute5_value => p_rec.region_1
4327 ,p_attribute6_name => 'REGION_2'
4328 ,p_attribute6_value => p_rec.region_2
4329 ,p_attribute7_name => 'REGION_3'
4330 ,p_attribute7_value => p_rec.region_3
4331 ,p_attribute8_name => 'POSTAL_CODE'
4332 ,p_attribute8_value => p_rec.postal_code
4333 ,p_attribute9_name => 'COUNTRY'
4334 ,p_attribute9_value => p_rec.country
4335 ,p_attribute10_name => 'TELEPHONE_NUMBER_1'
4336 ,p_attribute10_value => p_rec.telephone_number_1
4337 ,p_attribute11_name => 'TELEPHONE_NUMBER_2'
4338 ,p_attribute11_value => p_rec.telephone_number_2
4339 ,p_attribute12_name => 'TELEPHONE_NUMBER_3'
4340 ,p_attribute12_value => p_rec.telephone_number_3
4341 ,p_attribute13_name => 'ADD_INFORMATION17' --Start of new code for Bug#2164019
4342 ,p_attribute13_value => p_rec.add_information17
4343 ,p_attribute14_name => 'ADD_INFORMATION18'
4344 ,p_attribute14_value => p_rec.add_information18
4345 ,p_attribute15_name => 'ADD_INFORMATION19'
4346 ,p_attribute15_value => p_rec.add_information19
4347 ,p_attribute16_name => 'ADD_INFORMATION20'
4348 ,p_attribute16_value => p_rec.add_information20 --End of new code for Bug#2164019
4349 ,p_attribute17_name => 'ADD_INFORMATION13'
4350 ,p_attribute17_value => p_rec.add_information13
4351 ,p_attribute18_name => 'ADD_INFORMATION14'
4352 ,p_attribute18_value => p_rec.add_information14
4353 ,p_attribute19_name => 'ADD_INFORMATION15'
4354 ,p_attribute19_value => p_rec.add_information15
4355 ,p_attribute20_name => 'ADD_INFORMATION16'
4356 ,p_attribute20_value => p_rec.add_information16
4357 );
4358 --
4359 end if;
4360 --
4361 hr_utility.set_location(' Leaving:'||l_proc, 10);
4362 end chk_ddf;
4363 --
4364 -- ---------------------------------------------------------------------------
4365 -- |----------------------< df_update_validate >---------------------------|
4366 -- ---------------------------------------------------------------------------
4367 --
4368 -- Description:
4369 -- Calls the descriptive flex validation stub (per_add_flex.df) if either
4370 -- the attribute_category or attribute1..30 have changed.
4371 --
4372 -- Pre-conditions:
4373 -- Can only be called from update_validate
4374 --
4375 -- In Arguments:
4376 -- p_rec
4377 --
4378 -- Post Success:
4379 -- If the attribute_category and attribute1.30 haven't changed then the
4380 -- validation is not performed and the processing continues.
4381 -- If the attribute_category or attribute1.30 have changed then the
4382 -- per_add_flex.df validates the descriptive flex. If an exception is
4383 -- not raised then processing continues.
4384 --
4385 -- Post Failure:
4386 -- If an exception is raised within this procedure or lower
4387 -- procedure calls then it is raised through the normal exception
4388 -- handling mechanism.
4389 --
4390 -- Access Status:
4391 -- Internal Table Handler Use Only.
4392 -- ---------------------------------------------------------------------------
4393 procedure df_update_validate
4394 (p_rec in per_add_shd.g_rec_type) is
4395 --
4396 l_proc varchar2(72) := g_package||'df_update_validate';
4397 --
4398 begin
4399 hr_utility.set_location('Entering:'||l_proc, 10);
4400 --
4401 if nvl(per_add_shd.g_old_rec.addr_attribute_category, hr_api.g_varchar2) <>
4402 nvl(p_rec.addr_attribute_category, hr_api.g_varchar2) or
4403 nvl(per_add_shd.g_old_rec.addr_attribute1, hr_api.g_varchar2) <>
4404 nvl(p_rec.addr_attribute1, hr_api.g_varchar2) or
4405 nvl(per_add_shd.g_old_rec.addr_attribute2, hr_api.g_varchar2) <>
4406 nvl(p_rec.addr_attribute2, hr_api.g_varchar2) or
4407 nvl(per_add_shd.g_old_rec.addr_attribute3, hr_api.g_varchar2) <>
4408 nvl(p_rec.addr_attribute3, hr_api.g_varchar2) or
4409 nvl(per_add_shd.g_old_rec.addr_attribute4, hr_api.g_varchar2) <>
4410 nvl(p_rec.addr_attribute4, hr_api.g_varchar2) or
4411 nvl(per_add_shd.g_old_rec.addr_attribute5, hr_api.g_varchar2) <>
4412 nvl(p_rec.addr_attribute5, hr_api.g_varchar2) or
4413 nvl(per_add_shd.g_old_rec.addr_attribute6, hr_api.g_varchar2) <>
4414 nvl(p_rec.addr_attribute6, hr_api.g_varchar2) or
4415 nvl(per_add_shd.g_old_rec.addr_attribute7, hr_api.g_varchar2) <>
4416 nvl(p_rec.addr_attribute7, hr_api.g_varchar2) or
4417 nvl(per_add_shd.g_old_rec.addr_attribute8, hr_api.g_varchar2) <>
4418 nvl(p_rec.addr_attribute8, hr_api.g_varchar2) or
4419 nvl(per_add_shd.g_old_rec.addr_attribute9, hr_api.g_varchar2) <>
4420 nvl(p_rec.addr_attribute9, hr_api.g_varchar2) or
4421 nvl(per_add_shd.g_old_rec.addr_attribute10, hr_api.g_varchar2) <>
4422 nvl(p_rec.addr_attribute10, hr_api.g_varchar2) or
4423 nvl(per_add_shd.g_old_rec.addr_attribute11, hr_api.g_varchar2) <>
4424 nvl(p_rec.addr_attribute11, hr_api.g_varchar2) or
4425 nvl(per_add_shd.g_old_rec.addr_attribute12, hr_api.g_varchar2) <>
4426 nvl(p_rec.addr_attribute12, hr_api.g_varchar2) or
4427 nvl(per_add_shd.g_old_rec.addr_attribute13, hr_api.g_varchar2) <>
4428 nvl(p_rec.addr_attribute13, hr_api.g_varchar2) or
4429 nvl(per_add_shd.g_old_rec.addr_attribute14, hr_api.g_varchar2) <>
4430 nvl(p_rec.addr_attribute14, hr_api.g_varchar2) or
4431 nvl(per_add_shd.g_old_rec.addr_attribute15, hr_api.g_varchar2) <>
4432 nvl(p_rec.addr_attribute15, hr_api.g_varchar2) or
4433 nvl(per_add_shd.g_old_rec.addr_attribute16, hr_api.g_varchar2) <>
4434 nvl(p_rec.addr_attribute16, hr_api.g_varchar2) or
4435 nvl(per_add_shd.g_old_rec.addr_attribute17, hr_api.g_varchar2) <>
4436 nvl(p_rec.addr_attribute17, hr_api.g_varchar2) or
4437 nvl(per_add_shd.g_old_rec.addr_attribute18, hr_api.g_varchar2) <>
4438 nvl(p_rec.addr_attribute18, hr_api.g_varchar2) or
4439 nvl(per_add_shd.g_old_rec.addr_attribute19, hr_api.g_varchar2) <>
4440 nvl(p_rec.addr_attribute19, hr_api.g_varchar2) or
4441 nvl(per_add_shd.g_old_rec.addr_attribute20, hr_api.g_varchar2) <>
4442 nvl(p_rec.addr_attribute20, hr_api.g_varchar2) or
4443 nvl(per_add_shd.g_old_rec.add_information13, hr_api.g_varchar2) <>
4444 nvl(p_rec.add_information13, hr_api.g_varchar2) or
4445 nvl(per_add_shd.g_old_rec.add_information14, hr_api.g_varchar2) <>
4446 nvl(p_rec.add_information14, hr_api.g_varchar2) or
4447 nvl(per_add_shd.g_old_rec.add_information15, hr_api.g_varchar2) <>
4448 nvl(p_rec.add_information15, hr_api.g_varchar2) or
4449 nvl(per_add_shd.g_old_rec.add_information16, hr_api.g_varchar2) <>
4450 nvl(p_rec.add_information16, hr_api.g_varchar2) or
4451 nvl(per_add_shd.g_old_rec.add_information17, hr_api.g_varchar2) <>
4452 nvl(p_rec.add_information17, hr_api.g_varchar2) or
4453 nvl(per_add_shd.g_old_rec.add_information18, hr_api.g_varchar2) <>
4454 nvl(p_rec.add_information18, hr_api.g_varchar2) or
4455 nvl(per_add_shd.g_old_rec.add_information19, hr_api.g_varchar2) <>
4456 nvl(p_rec.add_information19, hr_api.g_varchar2) or
4457 nvl(per_add_shd.g_old_rec.add_information20, hr_api.g_varchar2) <>
4458 nvl(p_rec.add_information20, hr_api.g_varchar2) or
4459 nvl(per_add_shd.g_old_rec.party_id, hr_api.g_number) <> -- HR/TCA merge
4460 nvl(p_rec.party_id, hr_api.g_number)
4461 then
4462 -- either the attribute_category or attribute1..30 have changed
4463 -- so we must call the flex stub
4464 per_add_flex.df(p_rec => p_rec);
4465 end if;
4466 --
4467 hr_utility.set_location(' Leaving:'||l_proc, 10);
4468 end df_update_validate;
4469 --
4470 -- ----------------------------------------------------------------------------
4471 -- |---------------------------< insert_validate >----------------------------|
4472 -- ----------------------------------------------------------------------------
4473 Procedure insert_validate
4474 (p_rec in out nocopy per_add_shd.g_rec_type
4475 ,p_effective_date in date
4476 ,p_validate_county in boolean default true
4477 ) is
4478 --
4479 l_proc varchar2(72) := g_package||'insert_validate';
4480 --
4481 Begin
4482 hr_utility.set_location('Entering:'||l_proc, 10);
4483 --
4484 -- Validate Important Attributes
4485 --
4486 -- Reset global variable that indicates if payroll is installed under
4487 -- US legislation prior to validation.
4488 --
4489 g_us_payroll := NULL;
4490 --
4491 -- Call all supporting business operations.
4492 --
4493 -- if person_id is null, business_group_is isn't required
4494 -- by HR/TCA merge
4495 --
4496 if p_rec.person_id is not null then
4497 --
4498 -- Validate business group id
4499 --
4500 hr_api.validate_bus_grp_id(
4501 p_business_group_id => p_rec.business_group_id
4502 ,p_associated_column1 => per_add_shd.g_tab_nam ||
4503 '.BUSINESS_GROUP_ID'
4504 );
4505 hr_utility.set_location(l_proc, 20);
4506 --
4507 -- After validating the set of important attributes,
4508 -- if Mulitple message detection is enabled and at least
4509 -- one error has been found then abort further validation.
4510 --
4511 hr_multi_message.end_validation_set;
4512 --
4513 end if;
4514 --
4515 -- Validate Dependent Attributes
4516 --
4517 -- Validate date from
4518 --
4519 chk_date_from
4520 (p_address_id => p_rec.address_id
4521 ,p_date_from => p_rec.date_from
4522 ,p_date_to => p_rec.date_to
4523 ,p_object_version_number => p_rec.object_version_number
4524 );
4525 --
4526 hr_utility.set_location(l_proc, 30);
4527 -- HR/TCA merge
4528 --
4529 -- Validate business_group_id
4530 --
4531 chk_business_group_id
4532 (p_address_id => p_rec.address_id
4533 ,p_object_version_number => p_rec.object_version_number
4534 ,p_person_id => p_rec.person_id
4535 ,p_business_group_id => p_rec.business_group_id
4536 );
4537 hr_utility.set_location(l_proc, 35);
4538 --
4539 if p_rec.person_id is not null then
4540 --
4541 -- Validate person_id
4542 --
4543 chk_person_id
4544 (p_address_id => p_rec.address_id
4545 ,p_object_version_number => p_rec.object_version_number
4546 ,p_person_id => p_rec.person_id
4547 ,p_business_group_id => p_rec.business_group_id
4548 );
4549 hr_utility.set_location(l_proc, 40);
4550 end if;
4551 --
4552 -- Validate party_id
4553 --
4554 chk_party_id
4555 (p_rec
4556 );
4557 hr_utility.set_location(l_proc, 45);
4558 --
4559 -- Validate primary flag
4560 --
4561 chk_primary_flag
4562 (p_address_id => p_rec.address_id
4563 ,p_object_version_number => p_rec.object_version_number
4564 ,p_primary_flag => p_rec.primary_flag
4565 );
4566 hr_utility.set_location(l_proc, 50);
4567 --
4568 if not g_called_from_form then
4569 --
4570 -- Validate date/address_type combination
4571 -- Validate date/primary address combination
4572 --
4573 chk_date_comb
4574 (p_address_id => p_rec.address_id
4575 ,p_address_type => p_rec.address_type
4576 ,p_primary_flag => p_rec.primary_flag
4577 ,p_date_from => p_rec.date_from
4578 ,p_date_to => p_rec.date_to
4579 ,p_person_id => p_rec.person_id
4580 ,p_object_version_number => p_rec.object_version_number
4581 ,p_party_id => p_rec.party_id -- HR/TCA merge
4582 );
4583 end if;
4584 --
4585 hr_utility.set_location(l_proc, 60);
4586 --
4587 -- Validate style
4588 --
4589 chk_style
4590 (p_style => p_rec.style
4591 );
4592 hr_utility.set_location(l_proc, 70);
4593 --
4594 -- Validate address type
4595 --
4596 chk_address_type
4597 (p_address_id => p_rec.address_id
4598 ,p_address_type => p_rec.address_type
4599 ,p_date_from => p_rec.date_from
4600 ,p_effective_date => p_effective_date
4601 ,p_object_version_number => p_rec.object_version_number
4602 );
4603 hr_utility.set_location(l_proc, 80);
4604 --
4605 -- Validate country
4606 --
4607 chk_country
4608 (p_country => p_rec.country
4609 ,p_style => p_rec.style
4610 ,p_address_id => p_rec.address_id
4611 ,p_object_version_number => p_rec.object_version_number
4612 );
4613 hr_utility.set_location(l_proc, 90);
4614 --
4615 -- Validate postal code.
4616 --
4617 chk_postal_code
4618 (p_address_id => p_rec.address_id
4619 ,p_style => p_rec.style
4620 ,p_postal_code => p_rec.postal_code
4621 ,p_business_group_id => p_rec.business_group_id
4622 ,p_object_version_number => p_rec.object_version_number
4623 ,p_town_or_city => p_rec.town_or_city
4624 );
4625 --
4626 -- Validate taxation address zip.
4627 --
4628 If p_rec.style = 'US' and
4629 p_rec.primary_flag = 'Y' then
4630 chk_tax_address_zip
4631 (p_address_id => p_rec.address_id
4632 ,p_style => p_rec.style
4633 ,p_tax_address_zip => p_rec.add_information20
4634 ,p_business_group_id => p_rec.business_group_id
4635 ,p_object_version_number => p_rec.object_version_number
4636 );
4637 End if;
4638 hr_utility.set_location(l_proc, 100);
4639 --
4640 -- Validation specific to GB, US, GENERIC
4641 --
4642 if p_rec.style = 'GB' or
4643 p_rec.style = 'US' or
4644 p_rec.style = 'GENERIC'
4645 then
4646 --
4647 -- Check null attributes for address style.
4648 --
4649 hr_utility.set_location(l_proc, 110);
4650 chk_style_null_attr
4651 (p_address_id => p_rec.address_id
4652 ,p_object_version_number => p_rec.object_version_number
4653 ,p_style => p_rec.style
4654 ,p_region_2 => p_rec.region_2
4655 ,p_region_3 => p_rec.region_3
4656 ,p_telephone_number_3 => p_rec.telephone_number_3
4657 );
4658 hr_utility.set_location(l_proc, 120);
4659 --
4660 -- Validate address_line1
4661 --
4662 chk_address_line1
4663 (p_address_id => p_rec.address_id
4664 ,p_style => p_rec.style
4665 ,p_address_line1 => p_rec.address_line1
4666 ,p_object_version_number => p_rec.object_version_number
4667 );
4668 hr_utility.set_location(l_proc, 130);
4669 --
4670 -- Validate date_to
4671 --
4672 -- No procedural call is made to the procedure
4673 -- chk_date_to as the insert logic is handled
4674 -- by chk_date_from
4675 --
4676 -- Validate region 1.
4677 --
4678 chk_region_1
4679 (p_address_id => p_rec.address_id
4680 ,p_style => p_rec.style
4681 ,p_region_1 => p_rec.region_1
4682 ,p_business_group_id => p_rec.business_group_id
4683 ,p_effective_date => p_effective_date
4684 ,p_object_version_number => p_rec.object_version_number
4685 ,p_validate_county => p_validate_county
4686 );
4687 --
4688 -- Validate tax_county
4689 --
4690 If p_rec.style = 'US' and
4691 p_rec.primary_flag = 'Y'
4692 Then
4693 chk_tax_county
4694 (p_address_id => p_rec.address_id
4695 ,p_style => p_rec.style
4696 ,p_tax_county => p_rec.add_information19
4697 ,p_business_group_id => p_rec.business_group_id
4698 ,p_effective_date => p_effective_date
4699 ,p_object_version_number => p_rec.object_version_number
4700 );
4701 End if;
4702 hr_utility.set_location(l_proc, 140);
4703 --
4704 -- Validate region 2.
4705 --
4706 chk_region_2
4707 (p_address_id => p_rec.address_id
4708 ,p_style => p_rec.style
4709 ,p_region_2 => p_rec.region_2
4710 ,p_business_group_id => p_rec.business_group_id
4711 ,p_effective_date => p_effective_date
4712 ,p_object_version_number => p_rec.object_version_number
4713 );
4714 hr_utility.set_location(l_proc, 150);
4715 --
4716 -- Validate tax_state(add_information17)
4717 --
4718 If p_rec.style = 'US' and
4719 p_rec.primary_flag = 'Y'
4720 Then
4721 chk_tax_state
4722 (p_address_id => p_rec.address_id
4723 ,p_style => p_rec.style
4724 ,p_tax_state => p_rec.add_information17
4725 ,p_business_group_id => p_rec.business_group_id
4726 ,p_effective_date => p_effective_date
4727 ,p_object_version_number => p_rec.object_version_number
4728 );
4729 hr_utility.set_location(l_proc, 155);
4730 End if;
4731 --
4732 -- Validate town or city.
4733 --
4734 chk_town_or_city
4735 (p_address_id => p_rec.address_id
4736 ,p_style => p_rec.style
4737 ,p_town_or_city => p_rec.town_or_city
4738 ,p_business_group_id => p_rec.business_group_id
4739 ,p_object_version_number => p_rec.object_version_number
4740 );
4741 hr_utility.set_location(l_proc, 160);
4742 --
4743 -- Validate tax_city (add_information18).
4744 --
4745 If p_rec.style = 'US' and
4746 p_rec.primary_flag = 'Y' then
4747 chk_tax_city
4748 (p_address_id => p_rec.address_id
4749 ,p_style => p_rec.style
4750 ,p_tax_city => p_rec.add_information18
4751 ,p_business_group_id => p_rec.business_group_id
4752 ,p_object_version_number => p_rec.object_version_number
4753 );
4754 hr_utility.set_location(l_proc, 165);
4755 End if;
4756 --
4757 --
4758 -- This is only applicable if payroll is installed under US legislation
4759 -- and address style is 'US'.
4760 -- Validate city(town_or_city) and state(region_1)
4761 -- combination.
4762 --
4763 chk_city_state_zip_comb
4764 (p_address_id => p_rec.address_id
4765 ,p_style => p_rec.style
4766 ,p_postal_code => p_rec.postal_code
4767 ,p_region_1 => p_rec.region_1
4768 ,p_region_2 => p_rec.region_2
4769 ,p_town_or_city => p_rec.town_or_city
4770 ,p_business_group_id => p_rec.business_group_id
4771 ,p_object_version_number => p_rec.object_version_number
4772 );
4773 hr_utility.set_location(l_proc, 170);
4774 --
4775 chk_tax_city_state_zip_comb
4776 (p_address_id => p_rec.address_id
4777 ,p_style => p_rec.style
4778 ,p_tax_zip => p_rec.add_information20
4779 ,p_tax_county => p_rec.add_information19
4780 ,p_tax_state => p_rec.add_information17
4781 ,p_tax_city => p_rec.add_information18
4782 ,p_business_group_id => p_rec.business_group_id
4783 ,p_object_version_number => p_rec.object_version_number
4784 );
4785 hr_utility.set_location(l_proc, 175);
4786 --Validation specific to JP
4787 --
4788 /* Bug 1677965
4789 elsif p_rec.style = 'JP' then
4790 hr_utility.set_location(l_proc, 21);
4791 --
4792 -- Check the combination checking for town_or_city(district_code)
4793 -- address_line1, and region_1
4794 --
4795 chk_address1_towncity_comb
4796 (p_business_group_id => p_rec.business_group_id
4797 ,p_address_id => p_rec.address_id
4798 ,p_object_version_number => p_rec.object_version_number
4799 ,p_town_or_city => p_rec.town_or_city
4800 ,p_address_line1 => p_rec.address_line1
4801 ,p_region_1 => p_rec.region_1
4802 );
4803 hr_utility.set_location(l_proc, 180);
4804 --
4805 -- Validate region_2 according to address_line2
4806 --
4807 chk_address2_region2_comb
4808 (p_address_id => p_rec.address_id
4809 ,p_object_version_number => p_rec.object_version_number
4810 ,p_address_line2 => p_rec.address_line2
4811 ,p_region_2 => p_rec.region_2
4812 );
4813 hr_utility.set_location(l_proc, 190);
4814 --
4815 -- Validate region_3 according to address_line3
4816 --
4817 chk_address3_region3_comb
4818 (p_address_id => p_rec.address_id
4819 ,p_object_version_number => p_rec.object_version_number
4820 ,p_address_line3 => p_rec.address_line3
4821 ,p_region_3 => p_rec.region_3
4822 );
4823 hr_utility.set_location(l_proc, 200);
4824 */
4825 --
4826 -- DDF Validation other than GB, US, JP and GENERIC
4827 --
4828 else
4829 hr_utility.set_location(l_proc, 205);
4830 --
4831 -- Validate the DDF
4832 --
4833 chk_ddf
4834 (p_rec => p_rec
4835 );
4836 --
4837 end if;
4838 --
4839 -- Validate the DDF
4840 --
4841 chk_df
4842 (p_rec => p_rec
4843 );
4844 hr_utility.set_location(' Leaving:'||l_proc, 210);
4845 --
4846 End insert_validate;
4847 --
4848 -- ----------------------------------------------------------------------------
4849 -- |---------------------------< update_validate >----------------------------|
4850 -- ----------------------------------------------------------------------------
4851 Procedure update_validate
4852 (p_rec in out nocopy per_add_shd.g_rec_type
4853 ,p_effective_date in date
4854 ,p_prflagval_override in boolean default false
4855 ,p_validate_county in boolean default true
4856 ) is
4857 --
4858 l_proc varchar2(72) := g_package||'update_validate';
4859 --
4860 Begin
4861 hr_utility.set_location('Entering:'||l_proc, 10);
4862 --
4863 -- Validate Important Attributes
4864 --
4865 -- Reset global variable that indicates if payroll is installed under
4866 -- US legislation prior to validation.
4867 --
4868 g_us_payroll := NULL;
4869 --
4870 -- Call all supporting business operations. Mapping to the
4871 -- appropriate Business Rules in peradd.bru is provided.
4872 --
4873 -- Check that the columns which cannot
4874 -- be updated have not changed
4875 --
4876 check_non_updateable_args(p_rec => p_rec);
4877 hr_utility.set_location(l_proc, 15);
4878 --
4879 -- if person_id isn't specified,business_group_id isn't required.
4880 -- HR/TCA merge
4881 if p_rec.person_id is not null then
4882 --
4883 -- Validate business group id
4884 --
4885 hr_api.validate_bus_grp_id(
4886 p_business_group_id => p_rec.business_group_id
4887 ,p_associated_column1 => per_add_shd.g_tab_nam ||
4888 '.BUSINESS_GROUP_ID'
4889 );
4890 hr_utility.set_location(l_proc, 20);
4891 --
4892 -- After validating the set of important attributes,
4893 -- if Mulitple message detection is enabled and at least
4894 -- one error has been found then abort further validation.
4895 --
4896 hr_multi_message.end_validation_set;
4897 --
4898 end if;
4899 --
4900 -- HR/TCA merge
4901 --
4902 -- Validate business_group_id
4903 --
4904 chk_business_group_id
4905 (p_address_id => p_rec.address_id
4906 ,p_object_version_number => p_rec.object_version_number
4907 ,p_person_id => p_rec.person_id
4908 ,p_business_group_id => p_rec.business_group_id
4909 );
4910 hr_utility.set_location(l_proc, 22);
4911 --
4912 if p_rec.person_id is not null then -- If condition added for 2762677
4913 --
4914 -- Validate person_id
4915 --
4916 chk_person_id
4917 (p_address_id => p_rec.address_id
4918 ,p_object_version_number => p_rec.object_version_number
4919 ,p_person_id => p_rec.person_id
4920 ,p_business_group_id => p_rec.business_group_id
4921 );
4922 hr_utility.set_location(l_proc, 25);
4923 end if;
4924 --
4925 -- Validate Dependent Attributes
4926 --
4927 -- Validate date from
4928 --
4929 chk_date_from
4930 (p_address_id => p_rec.address_id
4931 ,p_date_from => p_rec.date_from
4932 ,p_date_to => p_rec.date_to
4933 ,p_object_version_number => p_rec.object_version_number
4934 );
4935 hr_utility.set_location(l_proc, 30);
4936 --
4937 -- Validate country.
4938 --
4939 chk_country
4940 (p_country => p_rec.country
4941 ,p_style => p_rec.style
4942 ,p_address_id => p_rec.address_id
4943 ,p_object_version_number => p_rec.object_version_number
4944 );
4945 hr_utility.set_location(l_proc, 40);
4946 --
4947 -- Validate address type.
4948 --
4949 chk_address_type
4950 (p_address_id => p_rec.address_id
4951 ,p_address_type => p_rec.address_type
4952 ,p_date_from => p_rec.date_from
4953 ,p_effective_date => p_effective_date
4954 ,p_object_version_number => p_rec.object_version_number
4955 );
4956 hr_utility.set_location(l_proc, 50);
4957 --
4958 -- Validate primary flag
4959 --
4960 chk_primary_flag
4961 (p_address_id => p_rec.address_id
4962 ,p_object_version_number => p_rec.object_version_number
4963 ,p_primary_flag => p_rec.primary_flag
4964 );
4965 hr_utility.set_location(l_proc, 50);
4966 --
4967 --
4968 --
4969 if not g_called_from_form then
4970 --
4971 -- Validate date/address_type combination
4972 -- Validate date/primary address combination
4973 --
4974 chk_date_comb
4975 (p_address_id => p_rec.address_id
4976 ,p_address_type => p_rec.address_type
4977 ,p_primary_flag => p_rec.primary_flag
4978 ,p_date_from => p_rec.date_from
4979 ,p_date_to => p_rec.date_to
4980 ,p_person_id => p_rec.person_id
4981 ,p_object_version_number => p_rec.object_version_number
4982 ,p_prflagval_override => p_prflagval_override
4983 ,p_party_id => p_rec.party_id -- HR/TCA merge
4984 );
4985 end if;
4986 --
4987 hr_utility.set_location(l_proc, 60);
4988 --
4989 -- Validate postal code.
4990 --
4991 chk_postal_code
4992 (p_address_id => p_rec.address_id
4993 ,p_style => p_rec.style
4994 ,p_postal_code => p_rec.postal_code
4995 ,p_business_group_id => p_rec.business_group_id
4996 ,p_object_version_number => p_rec.object_version_number
4997 ,p_town_or_city => p_rec.town_or_city
4998 );
4999 hr_utility.set_location(l_proc, 70);
5000 --
5001 -- Validate tax address zip.
5002 --
5003 If p_rec.style = 'US' and
5004 p_rec.primary_flag = 'Y' then
5005 chk_tax_address_zip
5006 (p_address_id => p_rec.address_id
5007 ,p_style => p_rec.style
5008 ,p_tax_address_zip => p_rec.add_information20
5009 ,p_business_group_id => p_rec.business_group_id
5010 ,p_object_version_number => p_rec.object_version_number
5011 );
5012 hr_utility.set_location(l_proc, 71);
5013 End if;
5014 --
5015 -- Validate date to.
5016 --
5017 chk_date_to
5018 (p_address_id => p_rec.address_id
5019 ,p_date_from => p_rec.date_from
5020 ,p_date_to => p_rec.date_to
5021 ,p_object_version_number => p_rec.object_version_number
5022 );
5023 hr_utility.set_location(l_proc, 80);
5024 --
5025 --
5026 -- Validation specific to GB, US, GENERIC
5027 --
5028 if p_rec.style = 'GB' or
5029 p_rec.style = 'US' or
5030 p_rec.style = 'GENERIC'
5031 then
5032 --
5033 -- Validate region 1.
5034 --
5035 chk_region_1
5036 (p_address_id => p_rec.address_id
5037 ,p_style => p_rec.style
5038 ,p_region_1 => p_rec.region_1
5039 ,p_business_group_id => p_rec.business_group_id
5040 ,p_effective_date => p_effective_date
5041 ,p_object_version_number => p_rec.object_version_number
5042 ,p_validate_county => p_validate_county
5043 );
5044 hr_utility.set_location(l_proc, 90);
5045 If p_rec.style = 'US' and
5046 p_rec.primary_flag = 'Y' then
5047 chk_tax_county
5048 (p_address_id => p_rec.address_id
5049 ,p_style => p_rec.style
5050 ,p_tax_county => p_rec.add_information19
5051 ,p_business_group_id => p_rec.business_group_id
5052 ,p_effective_date => p_effective_date
5053 ,p_object_version_number => p_rec.object_version_number
5054 );
5055 hr_utility.set_location(l_proc, 91);
5056 End if;
5057 --
5058 -- Validate region 2.
5059 --
5060 chk_region_2
5061 (p_address_id => p_rec.address_id
5062 ,p_style => p_rec.style
5063 ,p_region_2 => p_rec.region_2
5064 ,p_business_group_id => p_rec.business_group_id
5065 ,p_effective_date => p_effective_date
5066 ,p_object_version_number => p_rec.object_version_number
5067 );
5068 hr_utility.set_location(l_proc, 100);
5069 --
5070 -- Validate tax_state(add_information17).
5071 --
5072 If p_rec.style = 'US' and
5073 p_rec.primary_flag = 'Y' then
5074 chk_tax_state
5075 (p_address_id => p_rec.address_id
5076 ,p_style => p_rec.style
5077 ,p_tax_state => p_rec.add_information17
5078 ,p_business_group_id => p_rec.business_group_id
5079 ,p_effective_date => p_effective_date
5080 ,p_object_version_number => p_rec.object_version_number
5081 );
5082 hr_utility.set_location(l_proc, 105);
5083 End if;
5084 --
5085 -- Validate town or city.
5086 --
5087 chk_town_or_city
5088 (p_address_id => p_rec.address_id
5089 ,p_style => p_rec.style
5090 ,p_town_or_city => p_rec.town_or_city
5091 ,p_business_group_id => p_rec.business_group_id
5092 ,p_object_version_number => p_rec.object_version_number
5093 );
5094 hr_utility.set_location(l_proc, 110);
5095 --
5096 -- Validate tax_city.
5097 --
5098 If p_rec.style = 'US' and
5099 p_rec.primary_flag = 'Y' then
5100 chk_tax_city
5101 (p_address_id => p_rec.address_id
5102 ,p_style => p_rec.style
5103 ,p_tax_city => p_rec.add_information18
5104 ,p_business_group_id => p_rec.business_group_id
5105 ,p_object_version_number => p_rec.object_version_number
5106 );
5107 hr_utility.set_location(l_proc, 115);
5108 End if;
5109 --
5110 -- Validate address_line 1.
5111 --
5112 chk_address_line1
5113 (p_address_id => p_rec.address_id
5114 ,p_style => p_rec.style
5115 ,p_address_line1 => p_rec.address_line1
5116 ,p_object_version_number => p_rec.object_version_number
5117 );
5118 hr_utility.set_location(l_proc, 120);
5119 --
5120 -- Check null attributes for address style.
5121 --
5122 chk_style_null_attr
5123 (p_address_id => p_rec.address_id
5124 ,p_object_version_number => p_rec.object_version_number
5125 ,p_style => p_rec.style
5126 ,p_region_2 => p_rec.region_2
5127 ,p_region_3 => p_rec.region_3
5128 ,p_telephone_number_3 => p_rec.telephone_number_3
5129 );
5130 hr_utility.set_location(l_proc, 130);
5131 --
5132 -- This is only applicable if payroll is installed under US legislation
5133 -- and address style is 'US'.
5134 -- Validate city(town_or_city) and state(region_2)
5135 -- and zip code(postal_code) combination.
5136 --
5137 chk_city_state_zip_comb
5138 (p_address_id => p_rec.address_id
5139 ,p_style => p_rec.style
5140 ,p_postal_code => p_rec.postal_code
5141 ,p_region_1 => p_rec.region_1
5142 ,p_region_2 => p_rec.region_2
5143 ,p_town_or_city => p_rec.town_or_city
5144 ,p_business_group_id => p_rec.business_group_id
5145 ,p_object_version_number => p_rec.object_version_number
5146 );
5147 hr_utility.set_location(l_proc, 140);
5148 --
5149 If p_rec.style = 'US' and
5150 p_rec.primary_flag = 'Y' then
5151 chk_tax_city_state_zip_comb
5152 (p_address_id => p_rec.address_id
5153 ,p_style => p_rec.style
5154 ,p_tax_zip => p_rec.add_information20
5155 ,p_tax_county => p_rec.add_information19
5156 ,p_tax_state => p_rec.add_information17
5157 ,p_tax_city => p_rec.add_information18
5158 ,p_business_group_id => p_rec.business_group_id
5159 ,p_object_version_number => p_rec.object_version_number
5160 );
5161 hr_utility.set_location(l_proc, 145);
5162 End if;
5163 /* Bug 1677965
5164 elsif p_rec.style = 'JP' then
5165 hr_utility.set_location(l_proc, 150);
5166 --
5167 -- Check the combination checking for town_or_city(district_code)
5168 -- address_line1, and region_1
5169 --
5170 chk_address1_towncity_comb
5171 (p_business_group_id => p_rec.business_group_id
5172 ,p_address_id => p_rec.address_id
5173 ,p_object_version_number => p_rec.object_version_number
5174 ,p_town_or_city => p_rec.town_or_city
5175 ,p_address_line1 => p_rec.address_line1
5176 ,p_region_1 => p_rec.region_1
5177 );
5178 hr_utility.set_location(l_proc, 160);
5179 --
5180 -- Validate region_2 according to address_line2
5181 --
5182 chk_address2_region2_comb
5183 (p_address_id => p_rec.address_id
5184 ,p_object_version_number => p_rec.object_version_number
5185 ,p_address_line2 => p_rec.address_line2
5186 ,p_region_2 => p_rec.region_2
5187 );
5188 hr_utility.set_location(l_proc, 170);
5189 --
5190 -- Validate region_3 according to address_line3
5191 --
5192 chk_address3_region3_comb
5193 (p_address_id => p_rec.address_id
5194 ,p_object_version_number => p_rec.object_version_number
5195 ,p_address_line3 => p_rec.address_line3
5196 ,p_region_3 => p_rec.region_3
5197 );
5198 hr_utility.set_location(l_proc, 180);
5199 */
5200 --
5201 -- DDF Validation other than GB, US, JP and GENERIC
5202 --
5203 else
5204 hr_utility.set_location(l_proc, 185);
5205 --
5206 -- Validate the DDF
5207 --
5208 chk_ddf
5209 (p_rec => p_rec
5210 );
5211 --
5212 end if;
5213 --
5214 -- Validate the DDF
5215 --
5216 chk_df
5217 (p_rec => p_rec
5218 );
5219 --
5220 hr_utility.set_location(' Leaving:'||l_proc, 190);
5221 End update_validate;
5222 --
5223 -- ----------------------------------------------------------------------------
5224 -- |---------------------------< delete_validate >----------------------------|
5225 -- ----------------------------------------------------------------------------
5226 Procedure delete_validate(p_rec in out nocopy per_add_shd.g_rec_type) is
5227 --
5228 l_proc varchar2(72) := g_package||'delete_validate';
5229 --
5230 Begin
5231 hr_utility.set_location('Entering:'||l_proc, 5);
5232 --
5233 -- Call all supporting business operations. Mapping to the
5234 -- appropriate business rules on peradd.bru is provided
5235 --
5236 -- Check that deletion of a primary address is not allowed
5237 -- if non primary addresses exist within the same date range
5238 -- as the primary address
5239 --
5240 chk_del_address;
5241 --
5242 hr_utility.set_location(' Leaving:'||l_proc, 10);
5243 End delete_validate;
5244 --
5245 -- ---------------------------------------------------------------------------
5246 -- |---------------------< return_legislation_code >-------------------------|
5247 -- ---------------------------------------------------------------------------
5248 --
5249 function return_legislation_code
5250 (p_address_id in number
5251 ) return varchar2 is
5252 --
5253 -- Declare cursor
5254 --
5255 cursor csr_leg_code is
5256 select pbg.legislation_code
5257 from per_business_groups pbg
5258 , per_addresses adr
5259 where adr.address_id = p_address_id
5260 and pbg.business_group_id = adr.business_group_id;
5261
5262 --
5263 -- Declare local variables
5264 --
5265 l_legislation_code varchar2(150);
5266 l_proc varchar2(72) := g_package||'return_legislation_code';
5267 begin
5268 hr_utility.set_location('Entering:'|| l_proc, 10);
5269 --
5270 -- Ensure that all the mandatory parameter are not null
5271 --
5272 hr_api.mandatory_arg_error(p_api_name => l_proc,
5273 p_argument => 'address_id',
5274 p_argument_value => p_address_id);
5275 --
5276 if nvl(g_address_id, hr_api.g_number) = p_address_id then
5277 --
5278 -- The legislation has already been found with a previous
5279 -- call to this function. Just return the value in the global
5280 -- variable.
5281 --
5282 l_legislation_code := g_legislation_code;
5283 hr_utility.set_location(l_proc, 20);
5284 else
5285 --
5286 -- The ID is different to the last call to this function
5287 -- or this is the first call to this function.
5288 --
5289 --
5290 open csr_leg_code;
5291 fetch csr_leg_code into l_legislation_code;
5292 if csr_leg_code%notfound then
5293 close csr_leg_code;
5294 --
5295 -- WWBUG 2203479
5296 -- Special hack for irecruitment addresses
5297 --
5298 return null;
5299 --
5300 -- The primary key is invalid therefore we must error
5301 --
5302 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5303 hr_utility.set_location(l_proc, 30);
5304 hr_utility.raise_error;
5305 end if;
5306 --
5307 close csr_leg_code;
5308 --
5309 g_address_id:= p_address_id;
5310 g_legislation_code := l_legislation_code;
5311 hr_utility.set_location(' Leaving:'|| l_proc, 40);
5312 end if;
5313 return l_legislation_code;
5314 end return_legislation_code;
5315 --
5316 --
5317 end per_add_bus;