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