[Home] [Help]
PACKAGE BODY: APPS.PER_PHN_BUS
Source
1 Package Body per_phn_bus as
2 /* $Header: pephnrhi.pkb 120.0 2005/05/31 14:21:07 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_phn_bus.'; -- Global package name
9 --
10 -- -----------------------------------------------------------------
11 -- |-----------------------< chk_non_updateable_args >--------------|
12 -- -----------------------------------------------------------------
13 --
14 Procedure chk_non_updateable_args
15 (p_rec in per_phn_shd.g_rec_type
16 ) is
17 --
18 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
19 --
20 Begin
21 hr_utility.set_location('Entering:'||l_proc, 10);
22 --
23 -- Only proceed with validation if a row exists for
24 -- the current record in the HR Schema
25 --
26 if not per_phn_shd.api_updating
27 (p_phone_id => p_rec.phone_id,
28 p_object_version_number => p_rec.object_version_number)
29 then
30 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
31 hr_utility.set_message_token('PROCEDURE', l_proc);
32 hr_utility.set_message_token('STEP', '20');
33 end if;
34 hr_utility.set_location(l_proc, 30);
35 --
36 -- start of commenting of the following code, as parent_id and parent_table
37 -- are updteable if null. Present code makes it non updateable hence
38 -- commented
39 --
40 /*
41 if nvl(p_rec.parent_id, hr_api.g_number) <>
42 nvl(per_phn_shd.g_old_rec.parent_id
43 ,hr_api.g_number
44 ) then
45 --
46 hr_api.argument_changed_error
47 (p_api_name => l_proc
48 ,p_argument => 'PARENT_ID'
49 ,p_base_table => per_phn_shd.g_tab_nam
50 );
51 --
52 end if;
53 --
54 hr_utility.set_location(l_proc, 40);
55 if nvl(p_rec.parent_table, hr_api.g_varchar2) <>
56 nvl(per_phn_shd.g_old_rec.parent_table
57 ,hr_api.g_varchar2
58 ) then
59 --
60 hr_api.argument_changed_error
61 (p_api_name => l_proc
62 ,p_argument => 'PARENT_TABLE'
63 ,p_base_table => per_phn_shd.g_tab_nam
64 );
65 --
66 end if;
67 --
68 */
69 -- end of commenting the code
70 --
71 hr_utility.set_location(' Leaving:'||l_proc, 50);
72 end chk_non_updateable_args;
73 --
74 -- ---------------------------------------------------------------------------
75 -- |----------------------------< chk_date_from >----------------------------|
76 -- ---------------------------------------------------------------------------
77 --
78 -- Desciption :
79 --
80 -- DATE_FROM is mandatory
81 -- DATE_FROM must be less than DATE_TO
82 --
83 -- Pre-conditions :
84 -- Format for date_from and date_to must be correct
85 --
86 -- In Arguments :
87 -- p_phone_id
88 -- p_date_from
89 -- p_date_to
90 -- p_object_version_number
91 --
92 -- Post Success :
93 -- Processing continues
94 --
95 -- Post Failure :
96 -- An application error will be raised and processing is
97 -- terminated
98 --
99 -- Access Status :
100 -- Internal Table Handler Use only.
101 --
102 -- {End of Comments}
103 --
104 -- ---------------------------------------------------------------------------
105 procedure chk_date_from
106 (p_phone_id in per_phones.phone_id%TYPE
107 ,p_date_from in per_phones.date_from%TYPE
108 ,p_date_to in per_phones.date_to%TYPE
109 ,p_object_version_number in per_phones.object_version_number%TYPE
110 ) is
111 --
112 l_proc varchar2(72) := g_package||'chk_date_from';
113 l_api_updating boolean;
114 --
115 begin
116 hr_utility.set_location('Entering:'||l_proc, 1);
117 --
118 -- Check mandatory parameters have been set
119 --
120 hr_api.mandatory_arg_error
121 (p_api_name => l_proc
122 ,p_argument => 'date_from'
123 ,p_argument_value => p_date_from
124 );
125 --
126 -- Only proceed with validation if :
127 -- a) The current g_old_rec is current and
128 -- b) The date_from or date_to value has changed
129 --
130 l_api_updating := per_phn_shd.api_updating
131 (p_phone_id => p_phone_id
132 ,p_object_version_number => p_object_version_number);
133 --
134 if (l_api_updating and
135 (nvl(per_phn_shd.g_old_rec.date_from,hr_api.g_eot)
136 <> nvl(p_date_from,hr_api.g_eot)
137 or nvl(per_phn_shd.g_old_rec.date_to,hr_api.g_eot)
138 <> nvl(p_date_to,hr_api.g_eot)))
139 or
140 (NOT l_api_updating) then
141 hr_utility.set_location(l_proc, 2);
142 --
143 -- Check that the date_from value is less than or equal to the date_to
144 -- value for the current record
145 --
146 if p_date_from > nvl(p_date_to,hr_api.g_eot)then
147 hr_utility.set_message(801,'PER_7004_ALL_DATE_TO_FROM');
148 hr_utility.raise_error;
149 end if;
150 end if;
151 --
152 hr_utility.set_location(' Leaving:'||l_proc, 3);
153 --
154 exception
155 when app_exception.application_exception then
156 if hr_multi_message.exception_add
157 (p_associated_column1 => 'PER_PHONES.DATE_FROM'
158 ,p_associated_column2 => 'PER_PHONES.DATE_TO'
159 ) then
160 --
161 hr_utility.set_location(' Leaving:'||l_proc, 4);
162 --
163 raise;
164 end if;
165 --
166 hr_utility.set_location(' Leaving:'||l_proc, 5);
167 --
168 end chk_date_from;
169 --
170 -- ---------------------------------------------------------------------------
171 -- |-------------------------< chk_phone_type >---------------------------|
172 -- ---------------------------------------------------------------------------
173 --
174 -- Description:
175 -- Validates that an phone type exists in table hr_lookups
176 -- where lookup_type is 'PHONE_TYPE' and enabled_flag is 'Y' and
177 -- effective_date is between the active dates (if they are not null).
178 -- Phone type is mandatory.
179 -- Phone number is mandatory.
180 --
181 -- Pre-conditions:
182 -- Effective_date must be valid.
183 --
184 -- In Arguments:
185 -- p_phone_id
186 -- p_phone_type
187 -- p_phone_number
188 -- p_effective_date
189 -- p_object_version_number
190 --
191 -- Post Success:
192 -- If a row does exist in hr_lookups for the given phone code then
193 -- processing continues.
194 --
195 -- Post Failure:
196 -- If a row does not exist in hr_lookups for the given phone code then
197 -- an application error will be raised and processing is terminated.
198 --
199 -- Access Status:
200 -- Internal Table Handler Use Only.
201 --
202 -- {End Of Comments}
203 -- ----------------------------------------------------------------------------
204 procedure chk_phone_type
205 (p_phone_id in per_phones.phone_id%TYPE
206 ,p_phone_type in per_phones.phone_type%TYPE
207 ,p_phone_number in per_phones.phone_number%TYPE
208 ,p_effective_date in date
209 ,p_object_version_number in per_phones.object_version_number%TYPE) is
210 --
211 l_proc varchar2(72) := g_package||'chk_phone_type';
212 l_api_updating boolean;
213 --
214 begin
215 hr_utility.set_location('Entering:'|| l_proc, 1);
216 --
217 -- Check mandatory parameters have been set
218 --
219 hr_api.mandatory_arg_error
220 (p_api_name => l_proc
221 ,p_argument => 'effective_date'
222 ,p_argument_value => p_effective_date
223 );
224 --
225 hr_api.mandatory_arg_error
226 (p_api_name => l_proc
227 ,p_argument => 'phone_type'
228 ,p_argument_value => p_phone_type
229 );
230 --
231 if p_phone_number is null then
232 fnd_message.set_name('PER','PER_449911_MANDATORY_PHN_NUM');
233 fnd_message.raise_error;
234 end if;
235 --
236 -- Only proceed with validation if :
237 -- a) The current g_old_rec is current and
238 -- b) The value for phone type has changed
239 --
240 l_api_updating := per_phn_shd.api_updating
241 (p_phone_id => p_phone_id
242 ,p_object_version_number => p_object_version_number);
243 --
244 if ((l_api_updating
245 and nvl(per_phn_shd.g_old_rec.phone_type, hr_api.g_varchar2) <>
246 nvl(p_phone_type, hr_api.g_varchar2))
247 or
248 (NOT l_api_updating)) then
249 hr_utility.set_location(l_proc, 2);
250 --
251 -- Checks that the value for phone_type is
252 -- valid and exists on hr_lookups within the
253 -- specified date range
254 --
255 if hr_api.not_exists_in_hr_lookups
256 (p_effective_date => p_effective_date
257 ,p_lookup_type => 'PHONE_TYPE'
258 ,p_lookup_code => p_phone_type
259 ) then
260 --
261 -- Error: Invalid phone type.
262 hr_utility.set_message(801, 'HR_51529_PHN_TYPE_INVALID');
263 hr_utility.raise_error;
264 end if;
265 end if;
266 --
267 hr_utility.set_location(' Leaving:'|| l_proc, 3);
268 exception
269 when app_exception.application_exception then
270 if hr_multi_message.exception_add
271 (p_associated_column1 => 'PER_PHONES.PHONE_TYPE'
272 ) then
273 --
274 hr_utility.set_location(' Leaving:'||l_proc, 4);
275 --
276 raise;
277 end if;
278 --
279 hr_utility.set_location(' Leaving:'||l_proc, 5);
280 --
281 end chk_phone_type;
282 -- ---------------------------------------------------------------------------
283 -- |-------------------------< chk_phone_type_limits >---------------------|
284 -- ---------------------------------------------------------------------------
285 --
286 -- Description:
287 -- Only allow one primary home and one primary work per person at a given
288 -- time.
289 --
290 -- Pre-conditions:
291 --
292 -- In Arguments:
293 -- p_phone_id
294 -- p_date_from
295 -- p_date_to
296 -- p_phone_type
297 -- p_parent_id
298 -- p_parent_table
299 -- p_party_id -- HR/TCA merge
300 -- p_object_version_number
301 --
302 -- Post Success:
303 -- Processing continues.
304 --
305 -- Post Failure:
306 -- An application error will be raised and processing is terminated.
307 --
308 -- Access Status:
309 -- Internal Table Handler Use Only.
310 --
311 -- {End Of Comments}
312 -- ----------------------------------------------------------------------------
313 procedure chk_phone_type_limits
314 (p_phone_id in per_phones.phone_id%TYPE
315 ,p_date_from in per_phones.date_from%TYPE
316 ,p_date_to in per_phones.date_to%TYPE
317 ,p_phone_type in per_phones.phone_type%TYPE
318 ,p_parent_id in per_phones.parent_id%TYPE
319 ,p_parent_table in per_phones.parent_table%TYPE
320 ,p_party_id in per_phones.party_id%TYPE -- HR/TCA merge
321 ,p_object_version_number in per_phones.object_version_number%TYPE) is
322 --
323 l_proc varchar2(72) := g_package||'chk_phone_type_limits';
324 l_api_updating boolean;
325 l_dummy number;
326 --
327 cursor csr_home_phone_limit is
328 select phn.phone_id
329 from per_phones phn
330 where phn.phone_type = 'H1'
331 and phn.phone_id <> nvl(p_phone_id,hr_api.g_number)
332 and (
333 p_date_from between phn.date_from and
334 nvl(phn.date_to,hr_api.g_eot)
335 OR
336 nvl(p_date_to,hr_api.g_eot) between phn.date_from and
337 nvl(phn.date_to,hr_api.g_eot)
338 OR
339 phn.date_from between p_date_from and
340 nvl(p_date_to,hr_api.g_eot)
341 OR
342 nvl(phn.date_to,hr_api.g_eot) between p_date_from and
343 nvl(p_date_to,hr_api.g_eot)
344 )
345 and (
346 (phn.parent_id = p_parent_id --
347 and phn.parent_table = p_parent_table) -- HR/TCA merge
348 OR --
349 (phn.party_id = p_party_id and p_parent_id is null) -- 3299844
350 );
351
352 --
353 cursor csr_work_phone_limit is
354 select phn.phone_id
355 from per_phones phn
356 where phn.phone_type = 'W1'
357 and phn.phone_id <> nvl(p_phone_id,hr_api.g_number)
358 and (
359 p_date_from between phn.date_from and
360 nvl(phn.date_to,hr_api.g_eot)
361 OR
362 nvl(p_date_to,hr_api.g_eot) between phn.date_from and
363 nvl(phn.date_to,hr_api.g_eot)
364 OR
365 phn.date_from between p_date_from and
366 nvl(p_date_to,hr_api.g_eot)
367 OR
368 nvl(phn.date_to,hr_api.g_eot) between p_date_from and
369 nvl(p_date_to,hr_api.g_eot)
370 )
371 and (
372 (phn.parent_id = p_parent_id --
373 and phn.parent_table = p_parent_table) -- HR/TCA merge
374 OR --
375 (phn.party_id = p_party_id and p_parent_id is null) -- 3299844
376 );
377 --
378 begin
379 hr_utility.set_location('Entering:'|| l_proc, 1);
380 if hr_multi_message.no_all_inclusive_error
381 (p_check_column1 => 'PER_PHONES.PHONE_TYPE'
382 ,p_check_column2 => 'PER_PHONES.DATE_FROM'
383 ,p_check_column3 => 'PER_PHONES.PARENT_TABLE'
384 ,p_check_column4 => 'PER_PHONES.PARENT_ID'
385 ,p_check_column5 => 'PER_PHONES.PARTY_ID'
386 ,p_associated_column1 => 'PER_PHONES.PHONE_TYPE'
387 ,p_associated_column2 => 'PER_PHONES.DATE_FROM'
388 ,p_associated_column3 => 'PER_PHONES.PARENT_TABLE'
389 ,p_associated_column4 => 'PER_PHONES.PARENT_ID'
390 ,p_associated_column5 => 'PER_PHONES.PARTY_ID'
391 ) then
392 --
393 -- Only proceed with validation if :
394 -- a) The current g_old_rec is current and
395 -- b) The value for phone type, date_to, or date_from has changed
396 --
397 l_api_updating := per_phn_shd.api_updating
398 (p_phone_id => p_phone_id
399 ,p_object_version_number => p_object_version_number);
400 --
401 hr_utility.set_location(l_proc, 2);
402 --
403 if ((l_api_updating
404 and (per_phn_shd.g_old_rec.phone_type <> p_phone_type
405 or per_phn_shd.g_old_rec.date_from <> p_date_from
406 or nvl(per_phn_shd.g_old_rec.date_to,hr_api.g_eot) <>
407 nvl(p_date_to,hr_api.g_eot)))
408 OR
409 (NOT l_api_updating)) then
410 --
411 hr_utility.set_location(l_proc, 3);
412 --
413 -- Checks that there is only one active primary home (H1) phone number
414 --
415 if p_phone_type = 'H1' then
416 open csr_home_phone_limit;
417 fetch csr_home_phone_limit into l_dummy;
418 if csr_home_phone_limit%found then
419 close csr_home_phone_limit;
420 hr_utility.set_message(801, 'HR_51530_PHN_TYPE_HOME_LIMIT');
421 hr_utility.raise_error;
422 end if;
423 close csr_home_phone_limit;
424 end if;
425 --
426 hr_utility.set_location(l_proc, 4);
427 --
428 -- Checks that there is only one active primary work (W1) phone number
429 --
430 if p_phone_type = 'W1' then
431 open csr_work_phone_limit;
432 fetch csr_work_phone_limit into l_dummy;
433 if csr_work_phone_limit%found then
434 close csr_work_phone_limit;
435 hr_utility.set_message(801, 'HR_51531_PHN_TYPE_WORK_LIMIT');
436 hr_utility.raise_error;
437 end if;
438 close csr_work_phone_limit;
439 end if;
440 --
441 end if;
442 --
443 end if;
444 --
445 hr_utility.set_location(' Leaving:'|| l_proc, 5);
446 --
447 exception
448 when app_exception.application_exception then
449 if hr_multi_message.exception_add
450 (p_same_associated_columns => 'Y'
451 ) then
452 --
453 hr_utility.set_location(' Leaving:'||l_proc, 6);
454 --
455 raise;
456 end if;
457 --
458 hr_utility.set_location(' Leaving:'||l_proc, 7);
459 --
460 end chk_phone_type_limits;
461 -- ---------------------------------------------------------------------------
462 -- |----------------------------< chk_parent_table >-------------------------|
463 -- ---------------------------------------------------------------------------
464 --
465 -- Desciption :
466 --
467 -- If PARENT_TABLE <> 'PER_ALL_PEOPLE_F', raise an error. This is just a
468 -- temporary
469 -- solution which will require re-thinking when new parent tables are added
470 -- because we probably dont want to hard code all these.
471 --
472 -- Pre-conditions :
473 -- None.
474 --
475 -- In Arguments :
476 -- p_parent_table
477 --
478 -- Post Success :
479 -- Processing continues
480 --
481 -- Post Failure :
482 -- An application error will be raised and processing is
483 -- terminated
484 --
485 -- Access Status :
486 -- Internal Table Handler Use only.
487 --
488 -- {End of Comments}
489 --
490 -- ---------------------------------------------------------------------------
491 procedure chk_parent_table
492 (
493 p_parent_table in per_phones.parent_table%TYPE
494 ) is
495 --
496 l_proc varchar2(72) := g_package||'chk_parent_table';
497 --
498 begin
499 hr_utility.set_location('Entering:'||l_proc, 1);
500 --
501 -- Check mandatory parameters have been set
502 --
503 hr_api.mandatory_arg_error
504 (p_api_name => l_proc
505 ,p_argument => 'parent_table'
506 ,p_argument_value => p_parent_table
507 );
508 --
509 -- Check that the parent_table is in the per_people table.
510 -- This is a temporary solution.
511 --
512 hr_utility.set_location('IJH: Table name is: '||p_parent_table, 2);
513 If p_parent_table <> 'PER_ALL_PEOPLE_F' then
514 hr_utility.set_location('Failed parent Table check', 4);
515 hr_utility.set_message(801, 'HR_51532_PHN_FK_NOT_FOUND');
516 hr_utility.raise_error;
517 end if;
518
519 hr_utility.set_location(' Leaving:'||l_proc, 5);
520 --
521 exception
522 when app_exception.application_exception then
523 if hr_multi_message.exception_add
524 (p_associated_column1 => 'PER_PHONES.PARENT_TABLE'
525 ) then
526 --
527 hr_utility.set_location(' Leaving:'||l_proc, 6);
528 --
529 raise;
530 end if;
531 --
532 hr_utility.set_location(' Leaving:'||l_proc, 7);
533 --
534 end chk_parent_table;
535 --
536 -- ---------------------------------------------------------------------------
537 -- |----------------------------< chk_parent_id >----------------------------|
538 -- ---------------------------------------------------------------------------
539 --
540 -- Desciption :
541 --
542 -- If PARENT_TABLE = 'PER_ALL_PEOPLE_F', verify that the value in PARENT_ID
543 -- is in the per_all_people_f table. This is just a temporary solution
544 -- which will require re-thinking when new parent tables are added because
545 -- we probably dont want to hard code all these.
546 --
547 -- Pre-conditions :
548 --
549 --
550 -- In Arguments :
551 -- p_phone_id
552 -- p_parent_id
553 -- p_parent_table
554 -- p_object_version_number
555 --
556 -- Post Success :
557 -- Processing continues
558 --
559 -- Post Failure :
560 -- An application error will be raised and processing is
561 -- terminated
562 --
563 -- Access Status :
564 -- Internal Table Handler Use only.
565 --
566 -- {End of Comments}
567 --
568 -- ---------------------------------------------------------------------------
569 procedure chk_parent_id
570 (p_phone_id in per_phones.phone_id%TYPE
571 ,p_parent_id in per_phones.parent_id%TYPE
572 ,p_parent_table in per_phones.parent_table%TYPE
573 ,p_object_version_number in per_phones.object_version_number%TYPE
574 ) is
575 --
576 l_proc varchar2(72) := g_package||'chk_parent_id';
577 l_dummy number;
578 l_api_updating boolean;
579 --
580 cursor csr_valid_parent_id is
581 select per.person_id
582 from per_all_people_f per
583 where per.person_id = p_parent_id
584 and rownum <2; -- performance bug fix 3387297
585 --
586 begin
587 hr_utility.set_location('Entering:'||l_proc, 1);
588 --
589 -- Check mandatory parameters have been set
590 --
591 hr_api.mandatory_arg_error
592 (p_api_name => l_proc
593 ,p_argument => 'parent_id'
594 ,p_argument_value => p_parent_id
595 );
596 --
597 hr_utility.set_location(l_proc, 2);
598 --
599 if hr_multi_message.no_exclusive_error
600 (p_check_column1 => 'PER_PHONES.PARENT_TABLE'
601 ) then
602 --
603 hr_utility.set_location(l_proc, 3);
604 --
605 -- Only proceed with validation if :
606 -- a) The current g_old_rec is current and
607 -- b) The date_from value has changed
608 --
609 l_api_updating := per_phn_shd.api_updating
610 (p_phone_id => p_phone_id
611 ,p_object_version_number => p_object_version_number);
612 --
613 if ((l_api_updating and per_phn_shd.g_old_rec.parent_id <> p_parent_id)
614 or
615 (NOT l_api_updating)) then
616 --
617 hr_utility.set_location(l_proc, 4);
618 --
619 -- Check that the parent_id is in the per_people table.
620 -- This is a temporary solution.
621 --
622 open csr_valid_parent_id;
623 fetch csr_valid_parent_id into l_dummy;
624 if csr_valid_parent_id %notfound then
625 close csr_valid_parent_id;
626 hr_utility.set_message(801, 'HR_51532_PHN_FK_NOT_FOUND');
627 hr_utility.raise_error;
628 end if;
629 close csr_valid_parent_id;
630 end if;
631 --
632 hr_utility.set_location(l_proc, 10);
633 --
634 --UPDATE not allowed unless currently null
635 --
636 if (l_api_updating
637 and nvl(per_phn_shd.g_old_rec.parent_id,hr_api.g_number) <> hr_api.g_number
638 and per_phn_shd.g_old_rec.parent_id <> p_parent_id
639 ) then
640 hr_utility.set_location(l_proc, 11);
641 hr_utility.set_message(800, 'HR_289949_INV_UPD_PARENT_ID');
642 hr_utility.raise_error;
643 end if;
644 --
645 hr_utility.set_location(l_proc, 15);
646 --
647 if ((nvl(p_parent_id,hr_api.g_number) <> hr_api.g_number)
648 and (nvl(p_parent_table,hr_api.g_varchar2) = hr_api.g_varchar2)) then
649 hr_utility.set_location(l_proc, 16);
650 hr_utility.set_message(800, 'HR_289946_INV_PARENT_TABLE');
651 hr_utility.raise_error;
652 end if;
653 --
654 hr_utility.set_location(l_proc,20);
655 end if;
656 --
657 hr_utility.set_location(' Leaving:'||l_proc, 25);
658 --
659 exception
660 when app_exception.application_exception then
661 if hr_multi_message.exception_add
662 (p_associated_column1 => 'PER_PHONES.PARENT_ID'
663 ) then
664 --
665 hr_utility.set_location(' Leaving:'||l_proc, 6);
666 --
667 raise;
668 end if;
669 --
670 hr_utility.set_location(' Leaving:'||l_proc, 7);
671 --
672 end chk_parent_id;
673 --
674 -- ----------------------------------------------------------------------------
675 -- |--------------------------< chk_party_id >--------------------------------|
676 -- ----------------------------------------------------------------------------
677 --
678 --
679 -- Description:
680 -- - Validates that the person_id and the party_id are matched in
681 -- per_all_people_f
682 -- and if person_id is not null and party_id is null, derive party_id
683 -- from per_all_people_f from person_id
684 --
685 -- Pre_conditions:
686 -- A valid business_group_id
687 --
688 -- In Arguments:
689 -- A Pl/Sql record structre.
690 -- effective_date
691
692 --
693 -- Post Success:
694 -- Process continues if :
695 --
696 -- Post Failure:
697 -- An application error is raised and processing is terminated if any of
698
699 -- Access Status:
700 -- Internal Table Handler Use Only.
701 --
702 Procedure chk_party_id(
703 p_rec in out nocopy per_phn_shd.g_rec_type
704 ,p_effective_date in date
705 )is
706 --
707 l_proc varchar2(72) := g_package||'chk_party_id';
708 l_party_id per_phones.party_id%TYPE;
709 l_party_id2 per_phones.party_id%TYPE;
710 --
711 --
712 -- cursor to check that the party_id maches person_id
713 --
714 cursor csr_get_party_id is
715 select party_id
716 from per_all_people_f per
717 where per.person_id = p_rec.parent_id
718 and p_effective_date
719 between per.effective_start_date
720 and nvl(per.effective_end_date,hr_api.g_eot)
721 and rownum <2; -- Performace bug fix #3387297
722 --
723 cursor csr_valid_party_id is
724 select party_id
725 from hz_parties hzp
726 where hzp.party_id = p_rec.party_id;
727 --
728 begin
729 hr_utility.set_location('Entering:'|| l_proc, 10);
730 --
731 --
732 if p_rec.parent_id is not null then
733 if hr_multi_message.no_exclusive_error
734 (p_check_column1 => 'PER_PHONES.PARENT_ID'
735 ) then
736 --
737 open csr_get_party_id;
738 fetch csr_get_party_id into l_party_id;
739 close csr_get_party_id;
740 --
741 hr_utility.set_location(l_proc,20);
742 --
743 if p_rec.party_id is not null then
744 --
745 hr_utility.set_location(l_proc,30);
746 --
747 if p_rec.party_id <> nvl(l_party_id,-1) then
748 hr_utility.set_message(800, 'HR_289343_PERSONPARTY_MISMATCH');
749 hr_utility.set_location(l_proc,40);
750 hr_multi_message.add
751 (p_associated_column1 => 'PER_PHONES.PARENT_ID'
752 ,p_associated_column2 => 'PER_PHONES.PARTY_ID'
753 );
754 end if;
755 else
756 --
757 -- derive party_id from per_all_people_f using parent_id
758 --
759 hr_utility.set_location(l_proc,50);
760 p_rec.party_id := l_party_id;
761 end if;
762 end if; -- for no_excl_err
763 else
764 --
765 hr_utility.set_location(l_proc,60);
766 --
767 if p_rec.party_id is null then
768 hr_utility.set_message(800, 'HR_289341_CHK_PERSON_OR_PARTY');
769 hr_utility.set_location(l_proc,70);
770 hr_multi_message.add
771 (p_associated_column1 => 'PER_PHONES.PARENT_ID'
772 ,p_associated_column2 => 'PER_PHONES.PARTY_ID'
773 );
774 else
775 open csr_valid_party_id;
776 fetch csr_valid_party_id into l_party_id2;
777 --
778 hr_utility.set_location(l_proc,80);
779 --
780 if csr_valid_party_id%notfound then
781 close csr_valid_party_id;
782 hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
783 hr_utility.set_location(l_proc,90);
784 hr_multi_message.add
785 (p_associated_column1 => 'PER_PHONES.PARTY_ID'
786 );
787 else
788 close csr_valid_party_id;
789 end if;
790 end if;
791 end if;
792 --
793 hr_utility.set_location(' Leaving:'||l_proc,100);
794 End chk_party_id;
795
796 --
797 -- ---------------------------------------------------------------------------
798 -- |----------------------------< chk_object_version_number >-----------------|
799 -- ---------------------------------------------------------------------------
800 --
801 -- Desciption :
802 --
803 -- Checks that the OVN passed is not null on update and delete.
804 --
805 -- Pre-conditions :
806 -- None.
807 --
808 -- In Arguments :
809 -- p_object_version_number
810 --
811 -- Post Success :
812 -- Processing continues
813 --
814 -- Post Failure :
815 -- An application error will be raised and processing is
816 -- terminated
817 --
818 -- Access Status :
819 -- Internal Table Handler Use only.
820 --
821 -- {End of Comments}
822 --
823 -- ---------------------------------------------------------------------------
824 procedure chk_object_version_number
825 (
826 p_object_version_number in per_phones.object_version_number%TYPE
827 ) is
828 --
829 l_proc varchar2(72) := g_package||'chk_object_version_number';
830 --
831 begin
832 hr_utility.set_location('Entering:'||l_proc, 1);
833 --
834 -- Check mandatory parameters have been set
835 --
836 hr_api.mandatory_arg_error
837 (p_api_name => l_proc
838 ,p_argument => 'object_version_number'
839 ,p_argument_value => p_object_version_number
840 );
841 --
842 hr_utility.set_location(' Leaving:'||l_proc, 3);
843 --
844 end chk_object_version_number;
845 --
846 --
847 -- ---------------------------------------------------------------------------
848 -- |----------------------< df_update_validate >---------------------------|
849 -- ---------------------------------------------------------------------------
850 --
851 -- Description:
852 -- Calls the descriptive flex validation stub (per_phn_flex.df) if either
853 -- the attribute_category or attribute1..30 have changed.
854 --
855 -- Pre-conditions:
856 -- Can only be called from update_validate. RH hasn't been called from a form.
857 --
858 -- In Arguments:
859 -- p_rec
860 --
861 -- Post Success:
862 -- If the attribute_category and attribute1.30 haven't changed then the
863 -- validation is not performed and the processing continues.
864 -- If the attribute_category or attribute1.30 have changed then the
865 -- per_phn_flex.df validates the descriptive flex. If an exception is
866 -- not raised then processing continues.
867 --
868 -- Post Failure:
869 -- If an exception is raised within this procedure or lower
870 -- procedure calls then it is raised through the normal exception
871 -- handling mechanism.
872 --
873 -- Access Status:
874 -- Internal Table Handler Use Only.
875 -- ---------------------------------------------------------------------------
876 procedure df_update_validate
877 (p_rec in per_phn_shd.g_rec_type) is
878 --
879 l_proc varchar2(72) := g_package||'df_update_validate';
880 --
881 begin
882 hr_utility.set_location('Entering:'||l_proc, 10);
883 --
884 if nvl(per_phn_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
885 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
886 nvl(per_phn_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
887 nvl(p_rec.attribute1, hr_api.g_varchar2) or
888 nvl(per_phn_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
889 nvl(p_rec.attribute2, hr_api.g_varchar2) or
890 nvl(per_phn_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
891 nvl(p_rec.attribute3, hr_api.g_varchar2) or
892 nvl(per_phn_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
893 nvl(p_rec.attribute4, hr_api.g_varchar2) or
894 nvl(per_phn_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
895 nvl(p_rec.attribute5, hr_api.g_varchar2) or
896 nvl(per_phn_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
897 nvl(p_rec.attribute6, hr_api.g_varchar2) or
898 nvl(per_phn_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
899 nvl(p_rec.attribute7, hr_api.g_varchar2) or
900 nvl(per_phn_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
901 nvl(p_rec.attribute8, hr_api.g_varchar2) or
902 nvl(per_phn_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
903 nvl(p_rec.attribute9, hr_api.g_varchar2) or
904 nvl(per_phn_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
905 nvl(p_rec.attribute10, hr_api.g_varchar2) or
906 nvl(per_phn_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
907 nvl(p_rec.attribute11, hr_api.g_varchar2) or
908 nvl(per_phn_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
909 nvl(p_rec.attribute12, hr_api.g_varchar2) or
910 nvl(per_phn_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
911 nvl(p_rec.attribute13, hr_api.g_varchar2) or
912 nvl(per_phn_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
913 nvl(p_rec.attribute14, hr_api.g_varchar2) or
914 nvl(per_phn_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
915 nvl(p_rec.attribute15, hr_api.g_varchar2) or
916 nvl(per_phn_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
917 nvl(p_rec.attribute16, hr_api.g_varchar2) or
918 nvl(per_phn_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
919 nvl(p_rec.attribute17, hr_api.g_varchar2) or
920 nvl(per_phn_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
921 nvl(p_rec.attribute18, hr_api.g_varchar2) or
922 nvl(per_phn_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
923 nvl(p_rec.attribute19, hr_api.g_varchar2) or
924 nvl(per_phn_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
925 nvl(p_rec.attribute20, hr_api.g_varchar2) or
926 nvl(per_phn_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
927 nvl(p_rec.attribute21, hr_api.g_varchar2) or
928 nvl(per_phn_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
929 nvl(p_rec.attribute22, hr_api.g_varchar2) or
930 nvl(per_phn_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
931 nvl(p_rec.attribute23, hr_api.g_varchar2) or
932 nvl(per_phn_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
933 nvl(p_rec.attribute24, hr_api.g_varchar2) or
934 nvl(per_phn_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
935 nvl(p_rec.attribute25, hr_api.g_varchar2) or
936 nvl(per_phn_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
937 nvl(p_rec.attribute26, hr_api.g_varchar2) or
938 nvl(per_phn_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
939 nvl(p_rec.attribute27, hr_api.g_varchar2) or
940 nvl(per_phn_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
941 nvl(p_rec.attribute28, hr_api.g_varchar2) or
942 nvl(per_phn_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
943 nvl(p_rec.attribute29, hr_api.g_varchar2) or
944 nvl(per_phn_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
945 nvl(p_rec.attribute30, hr_api.g_varchar2) or
946 nvl(per_phn_shd.g_old_rec.party_id, hr_api.g_number) <> -- HR/TCA merge
947 nvl(p_rec.party_id, hr_api.g_number) --
948 then
949 -- either the attribute_category or attribute1..30 have changed
950 -- so we must call the flex stub
951 per_phn_flex.df(p_rec => p_rec);
952 end if;
953 --
954 hr_utility.set_location(' Leaving:'||l_proc, 10);
955 end df_update_validate;
956 --
957 -- -----------------------------------------------------------------------
958 -- |------------------------------< chk_df >-----------------------------|
959 -- -----------------------------------------------------------------------
960 --
961 -- Description:
962 -- Validates the all Descriptive Flexfield values.
963 --
964 -- Pre-conditions:
965 -- All other columns have been validated. Must be called as the
966 -- last step from insert_validate and update_validate.
967 --
968 -- In Arguments:
969 -- p_rec
970 --
971 -- Post Success:
972 -- If the Descriptive Flexfield structure column and data values are
973 -- all valid this procedure will end normally and processing will
974 -- continue.
975 --
976 -- Post Failure:
977 -- If the Descriptive Flexfield structure column value or any of
978 -- the data values are invalid then an application error is raised as
979 -- a PL/SQL exception.
980 --
981 -- Access Status:
982 -- Internal Row Handler Use Only.
983 --
984 procedure chk_df
985 (p_rec in per_phn_shd.g_rec_type) is
986 --
987 l_proc varchar2(72) := g_package||'chk_df';
988 --
989 begin
990 hr_utility.set_location('Entering:'||l_proc, 10);
991 --
992 if ((p_rec.phone_id is not null) and (
993 nvl(per_phn_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
994 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
995 nvl(per_phn_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
996 nvl(p_rec.attribute1, hr_api.g_varchar2) or
997 nvl(per_phn_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
998 nvl(p_rec.attribute2, hr_api.g_varchar2) or
999 nvl(per_phn_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1000 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1001 nvl(per_phn_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1002 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1003 nvl(per_phn_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1004 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1005 nvl(per_phn_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1006 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1007 nvl(per_phn_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1008 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1009 nvl(per_phn_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1010 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1011 nvl(per_phn_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1012 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1013 nvl(per_phn_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1014 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1015 nvl(per_phn_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1016 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1017 nvl(per_phn_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1018 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1019 nvl(per_phn_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1020 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1021 nvl(per_phn_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1022 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1023 nvl(per_phn_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1024 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1025 nvl(per_phn_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1026 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1027 nvl(per_phn_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1028 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1029 nvl(per_phn_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1030 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1031 nvl(per_phn_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1032 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1033 nvl(per_phn_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1034 nvl(p_rec.attribute20, hr_api.g_varchar2) or
1035 nvl(per_phn_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
1036 nvl(p_rec.attribute21, hr_api.g_varchar2) or
1037 nvl(per_phn_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
1038 nvl(p_rec.attribute22, hr_api.g_varchar2) or
1039 nvl(per_phn_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
1040 nvl(p_rec.attribute23, hr_api.g_varchar2) or
1041 nvl(per_phn_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
1042 nvl(p_rec.attribute24, hr_api.g_varchar2) or
1043 nvl(per_phn_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
1044 nvl(p_rec.attribute25, hr_api.g_varchar2) or
1045 nvl(per_phn_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
1046 nvl(p_rec.attribute26, hr_api.g_varchar2) or
1047 nvl(per_phn_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
1048 nvl(p_rec.attribute27, hr_api.g_varchar2) or
1049 nvl(per_phn_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
1050 nvl(p_rec.attribute28, hr_api.g_varchar2) or
1051 nvl(per_phn_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
1052 nvl(p_rec.attribute29, hr_api.g_varchar2) or
1053 nvl(per_phn_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
1054 nvl(p_rec.attribute30, hr_api.g_varchar2)))
1055 or
1056 (p_rec.phone_id is null) then
1057 --
1058 -- Only execute the validation if absolutely necessary:
1059 -- a) During update, the structure column value or any
1060 -- of the attribute values have actually changed.
1061 -- b) During insert.
1062 --
1063 hr_dflex_utility.ins_or_upd_descflex_attribs
1064 (p_appl_short_name => 'PER'
1065 ,p_descflex_name => 'PER_PHONES'
1066 ,p_attribute_category => p_rec.attribute_category
1067 ,p_attribute1_name => 'ATTRIBUTE1'
1068 ,p_attribute1_value => p_rec.attribute1
1069 ,p_attribute2_name => 'ATTRIBUTE2'
1070 ,p_attribute2_value => p_rec.attribute2
1071 ,p_attribute3_name => 'ATTRIBUTE3'
1072 ,p_attribute3_value => p_rec.attribute3
1073 ,p_attribute4_name => 'ATTRIBUTE4'
1074 ,p_attribute4_value => p_rec.attribute4
1075 ,p_attribute5_name => 'ATTRIBUTE5'
1076 ,p_attribute5_value => p_rec.attribute5
1077 ,p_attribute6_name => 'ATTRIBUTE6'
1078 ,p_attribute6_value => p_rec.attribute6
1079 ,p_attribute7_name => 'ATTRIBUTE7'
1080 ,p_attribute7_value => p_rec.attribute7
1081 ,p_attribute8_name => 'ATTRIBUTE8'
1082 ,p_attribute8_value => p_rec.attribute8
1083 ,p_attribute9_name => 'ATTRIBUTE9'
1084 ,p_attribute9_value => p_rec.attribute9
1085 ,p_attribute10_name => 'ATTRIBUTE10'
1086 ,p_attribute10_value => p_rec.attribute10
1087 ,p_attribute11_name => 'ATTRIBUTE11'
1088 ,p_attribute11_value => p_rec.attribute11
1089 ,p_attribute12_name => 'ATTRIBUTE12'
1090 ,p_attribute12_value => p_rec.attribute12
1091 ,p_attribute13_name => 'ATTRIBUTE13'
1092 ,p_attribute13_value => p_rec.attribute13
1093 ,p_attribute14_name => 'ATTRIBUTE14'
1094 ,p_attribute14_value => p_rec.attribute14
1095 ,p_attribute15_name => 'ATTRIBUTE15'
1096 ,p_attribute15_value => p_rec.attribute15
1097 ,p_attribute16_name => 'ATTRIBUTE16'
1098 ,p_attribute16_value => p_rec.attribute16
1099 ,p_attribute17_name => 'ATTRIBUTE17'
1100 ,p_attribute17_value => p_rec.attribute17
1101 ,p_attribute18_name => 'ATTRIBUTE18'
1102 ,p_attribute18_value => p_rec.attribute18
1103 ,p_attribute19_name => 'ATTRIBUTE19'
1104 ,p_attribute19_value => p_rec.attribute19
1105 ,p_attribute20_name => 'ATTRIBUTE20'
1106 ,p_attribute20_value => p_rec.attribute20
1107 ,p_attribute21_name => 'ATTRIBUTE21'
1108 ,p_attribute21_value => p_rec.attribute21
1109 ,p_attribute22_name => 'ATTRIBUTE22'
1110 ,p_attribute22_value => p_rec.attribute22
1111 ,p_attribute23_name => 'ATTRIBUTE23'
1112 ,p_attribute23_value => p_rec.attribute23
1113 ,p_attribute24_name => 'ATTRIBUTE24'
1114 ,p_attribute24_value => p_rec.attribute24
1115 ,p_attribute25_name => 'ATTRIBUTE25'
1116 ,p_attribute25_value => p_rec.attribute25
1117 ,p_attribute26_name => 'ATTRIBUTE26'
1118 ,p_attribute26_value => p_rec.attribute26
1119 ,p_attribute27_name => 'ATTRIBUTE27'
1120 ,p_attribute27_value => p_rec.attribute27
1121 ,p_attribute28_name => 'ATTRIBUTE28'
1122 ,p_attribute28_value => p_rec.attribute28
1123 ,p_attribute29_name => 'ATTRIBUTE29'
1124 ,p_attribute29_value => p_rec.attribute29
1125 ,p_attribute30_name => 'ATTRIBUTE30'
1126 ,p_attribute30_value => p_rec.attribute30
1127 );
1128 end if;
1129 --
1130 hr_utility.set_location(' Leaving:'||l_proc, 20);
1131
1132 end chk_df;
1133 --
1134 -- ---------------------------------------------------------------------------
1135 -- |----------------------------< chk_validity >----------------------------|
1136 -- ---------------------------------------------------------------------------
1137 --
1138 -- Desciption :
1139 --
1140 -- Checks for valid times phone number can be used, validated against
1141 -- HR_LOOKUPS.lookup_code table where LOOKUP_TYPE ='IRC_CONTACT_TIMES'
1142 --
1143 -- Pre-conditions :
1144 --
1145 --
1146 -- In Arguments :
1147 -- validity
1148 -- effective_date
1149 --
1150 -- Post Success :
1151 -- Processing continues
1152 --
1153 -- Post Failure :
1154 -- An application error will be raised and processing is
1155 -- terminated
1156 --
1157 -- Access Status :
1158 -- Internal Table Handler Use only.
1159 --
1160 -- {End of Comments}
1161 --
1162 -- ---------------------------------------------------------------------------
1163 procedure chk_validity
1164 ( p_effective_date in date
1165 , p_validity in per_phones.validity%TYPE
1166 )is
1167 --
1168 l_proc varchar2(72) := g_package||'chk_validity';
1169 --
1170 Begin
1171 hr_utility.set_location('Entering:'||l_proc, 5);
1172 If p_validity is not null
1173 then
1174 hr_utility.set_location(l_proc, 10);
1175 If hr_api.not_exists_in_hr_lookups
1176 (p_effective_date => p_effective_date
1177 ,p_lookup_type => 'IRC_CONTACT_TIMES'
1178 ,p_lookup_code => p_validity
1179 )
1180 then
1181 fnd_message.set_name('PER','PER_289551_BAD_PHN_VALIDITY');
1182 fnd_message.raise_error;
1183 end if;
1184 end if;
1185 hr_utility.set_location(' Leaving:'||l_proc, 20);
1186 exception
1187 when app_exception.application_exception then
1188 if hr_multi_message.exception_add
1189 (p_associated_column1 => 'PER_PHONES.VALIDITY'
1190 ) then
1191 --
1192 hr_utility.set_location(' Leaving:'||l_proc, 30);
1193 --
1194 raise;
1195 end if;
1196 --
1197 hr_utility.set_location(' Leaving:'||l_proc, 40);
1198 --
1199 End chk_validity;
1200 --
1201 -- ----------------------------------------------------------------------------
1202 -- |---------------------------< insert_validate >----------------------------|
1203 -- ----------------------------------------------------------------------------
1204 Procedure insert_validate(p_rec in out nocopy per_phn_shd.g_rec_type
1205 ,p_effective_date in date
1206 ) is
1207 --
1208 l_proc varchar2(72) := g_package||'insert_validate';
1209 --
1210 Begin
1211 hr_utility.set_location('Entering:'||l_proc, 5);
1212 --
1213 --
1214 if p_rec.parent_table = 'PER_ALL_PEOPLE_F' then
1215 per_per_bus.set_security_group_id
1216 (
1217 p_person_id => p_rec.parent_id
1218 ,p_associated_column1 => per_phn_shd.g_tab_nam||'.PARENT_ID'
1219 );
1220 end if;
1221 --
1222 -- After validating the set of important attributes,
1223 -- if Multiple Message Detection is enabled and at least
1224 -- one error has been found then abort further validation.
1225 --
1226 hr_multi_message.end_validation_set;
1227 --
1228 -- Validate Dependent Attributes
1229 --
1230 hr_utility.set_location(l_proc, 7);
1231 --
1232 -- Validiate dates
1233 --
1234 chk_date_from
1235 (p_phone_id => p_rec.phone_id
1236 ,p_date_from => p_rec.date_from
1237 ,p_date_to => p_rec.date_to
1238 ,p_object_version_number => p_rec.object_version_number
1239 );
1240 --
1241 -- Validate Phone Type
1242 --
1243 chk_phone_type
1244 (p_phone_id => p_rec.phone_id
1245 ,p_phone_type => p_rec.phone_type
1246 ,p_phone_number => p_rec.phone_number
1247 ,p_effective_date => p_effective_date
1248 ,p_object_version_number => p_rec.object_version_number
1249 );
1250 --
1251 -- if party_id is specified, parent_table and parent_id are not
1252 -- required parameter by HR/TCA merge
1253 --
1254 if p_rec.parent_table is not null then
1255 --
1256 -- Validate parent table name
1257 --
1258 chk_parent_table
1259 (p_parent_table => p_rec.parent_table);
1260 --
1261 -- Validate parent id
1262 --
1263 chk_parent_id
1264 (p_phone_id => p_rec.phone_id
1265 ,p_parent_id => p_rec.parent_id
1266 ,p_parent_table => p_rec.parent_table
1267 ,p_object_version_number => p_rec.object_version_number
1268 );
1269 end if;
1270 --
1271 -- Validate party_id by HR/TCA merge
1272 --
1273 chk_party_id
1274 (p_rec
1275 ,p_effective_date
1276 );
1277 --
1278 -- Validate validity
1279 --
1280 chk_validity
1281 ( p_effective_date =>p_effective_date
1282 , p_validity =>p_rec.validity
1283 );
1284 --
1285 -- Validate Phone Type Limits
1286 --
1287 chk_phone_type_limits
1288 (p_phone_id => p_rec.phone_id
1289 ,p_date_from => p_rec.date_from
1290 ,p_date_to => p_rec.date_to
1291 ,p_phone_type => p_rec.phone_type
1292 ,p_parent_id => p_rec.parent_id
1293 ,p_parent_table => p_rec.parent_table
1294 ,p_party_id => p_rec.party_id -- HR/TCA merge
1295 ,p_object_version_number => p_rec.object_version_number
1296 );
1297 --
1298 --
1299 -- Call Descriptive Flexfield Validation routines
1300 --
1301 per_phn_bus.chk_df(p_rec => p_rec);
1302 --
1303 --
1304 hr_utility.set_location(' Leaving:'||l_proc, 10);
1305 End insert_validate;
1306 --
1307 -- ----------------------------------------------------------------------------
1308 -- |---------------------------< update_validate >----------------------------|
1309 -- ----------------------------------------------------------------------------
1310 Procedure update_validate(p_rec in per_phn_shd.g_rec_type
1311 ,p_effective_date in date
1312 ) is
1313 --
1314 l_proc varchar2(72) := g_package||'update_validate';
1315 --
1316 Begin
1317 hr_utility.set_location('Entering:'||l_proc, 5);
1318 --
1319 if p_rec.parent_table = 'PER_ALL_PEOPLE_F' then
1320 per_per_bus.set_security_group_id
1321 (
1322 p_person_id => p_rec.parent_id
1323 ,p_associated_column1 => per_phn_shd.g_tab_nam||'.PARENT_ID'
1324 );
1325 end if;
1326 --
1327 -- After validating the set of important attributes,
1328 -- if Multiple Message Detection is enabled and at least
1329 -- one error has been found then abort further validation.
1330 --
1331 hr_multi_message.end_validation_set;
1332 --
1333 hr_utility.set_location(l_proc, 7);
1334 --
1335 -- Check whether called has tried to update non-updateable values.
1336 --
1337 chk_non_updateable_args (p_rec => p_rec);
1338 --
1339 if p_rec.parent_table is not null then
1340 --
1341 -- Validate parent table name
1342 --
1343 chk_parent_table
1344 (p_parent_table => p_rec.parent_table);
1345 --
1346 -- Validate parent id
1347 --
1348 chk_parent_id
1349 (p_phone_id => p_rec.phone_id
1350 ,p_parent_id => p_rec.parent_id
1351 ,p_parent_table => p_rec.parent_table
1352 ,p_object_version_number => p_rec.object_version_number
1353 );
1354 --
1355 end if;
1356 --
1357 -- Validate dates
1358 --
1359 chk_date_from
1360 (p_phone_id => p_rec.phone_id
1361 ,p_date_from => p_rec.date_from
1362 ,p_date_to => p_rec.date_to
1363 ,p_object_version_number => p_rec.object_version_number
1364 );
1365 --
1366 -- Validate Phone Type
1367 --
1368 chk_phone_type
1369 (p_phone_id => p_rec.phone_id
1370 ,p_phone_type => p_rec.phone_type
1371 ,p_phone_number => p_rec.phone_number
1372 ,p_effective_date => p_effective_date
1373 ,p_object_version_number => p_rec.object_version_number
1374 );
1375 --
1376 -- Validate Phone Type Limits
1377 --
1378 chk_phone_type_limits
1379 (p_phone_id => p_rec.phone_id
1380 ,p_date_from => p_rec.date_from
1381 ,p_date_to => p_rec.date_to
1382 ,p_phone_type => p_rec.phone_type
1383 ,p_parent_id => p_rec.parent_id
1384 ,p_parent_table => p_rec.parent_table
1385 ,p_party_id => p_rec.party_id -- HR/TCA merge
1386 ,p_object_version_number => p_rec.object_version_number
1387 );
1388 --
1389 -- Validate validity
1390 --
1391 chk_validity
1392 ( p_effective_date =>p_effective_date
1393 , p_validity =>p_rec.validity
1394 );
1395 --
1396 -- Validate Object Version Number
1397 --
1398 chk_object_version_number
1399 (p_object_version_number => p_rec.object_version_number);
1400 --
1401 --
1402 -- Call Descriptive Flexfield Validation routines
1403 --
1404 per_phn_bus.chk_df(p_rec => p_rec);
1405 --
1406 --
1407 hr_utility.set_location(' Leaving:'||l_proc, 10);
1408 End update_validate;
1409 --
1410 -- ----------------------------------------------------------------------------
1411 -- |---------------------------< delete_validate >----------------------------|
1412 -- ----------------------------------------------------------------------------
1413 Procedure delete_validate(p_rec in per_phn_shd.g_rec_type) is
1414 --
1415 l_proc varchar2(72) := g_package||'delete_validate';
1416 --
1417 Begin
1418 hr_utility.set_location('Entering:'||l_proc, 5);
1419 --
1420 -- Call all supporting business operations
1421 --
1422 -- Validate Object Version Number
1423 --
1424 chk_object_version_number
1425 (p_object_version_number => p_rec.object_version_number);
1426 --
1427 hr_utility.set_location(' Leaving:'||l_proc, 10);
1428 End delete_validate;
1429 --
1430 -- ---------------------------------------------------------------------------
1431 -- |---------------------< return_legislation_code >-------------------------|
1432 -- ---------------------------------------------------------------------------
1433 --
1434 function return_legislation_code
1435 (p_phone_id in number
1436 ) return varchar2 is
1437 --
1438 -- Cursor to find legislation code
1439 --
1440 cursor csr_leg_code is
1441 select pbg.legislation_code
1442 from per_business_groups pbg
1443 , per_phones phn
1444 , per_all_people_f per
1445 where phn.phone_id = p_phone_id
1446 and phn.parent_id = per.person_id
1447 and pbg.business_group_id = per.business_group_id;
1448 --
1449 -- Declare local variables
1450 --
1451 l_legislation_code varchar2(150);
1452 l_proc varchar2(72) := 'return_legislation_code';
1453 begin
1454 hr_utility.set_location('Entering:'|| l_proc, 10);
1455 --
1456 -- Ensure that all the mandatory parameter are not null
1457 --
1458 hr_api.mandatory_arg_error(p_api_name => l_proc,
1459 p_argument => 'phone_id',
1460 p_argument_value => p_phone_id);
1461 --
1462 if nvl(g_phone_id, hr_api.g_number) = p_phone_id then
1463 --
1464 -- The legislation code has already been found with a previous
1465 -- call to this function. Just return the value in the global
1466 -- variable.
1467 --
1468 l_legislation_code := g_legislation_code;
1469 hr_utility.set_location(l_proc, 20);
1470 else
1471 --
1472 -- The ID is different to the last call to this function
1473 -- or this is the first call to this function.
1474 --
1475 open csr_leg_code;
1476 fetch csr_leg_code into l_legislation_code;
1477 if csr_leg_code%notfound then
1478 --
1479 -- The primary key is invalid therefore we must error
1480 --
1481 close csr_leg_code;
1482 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1483 hr_utility.raise_error;
1484 end if;
1485 hr_utility.set_location(l_proc, 30);
1486 --
1487 -- Set the global variables so the values are
1488 -- available for the next call to this function
1489 --
1490 close csr_leg_code;
1491 g_phone_id := p_phone_id;
1492 g_legislation_code := l_legislation_code;
1493 end if;
1494 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1495 --
1496 return l_legislation_code;
1497 end return_legislation_code;
1498 --
1499
1500 function return_legislation_parent
1501 (p_parent_id in number
1502 ,p_parent_table in varchar2
1503 ) return varchar2
1504
1505 is
1506
1507 begin
1508 if p_parent_table = 'PER_ALL_PEOPLE_F'
1509 then
1510 return per_per_bus.return_legislation_code(p_person_id => p_parent_id);
1511 else
1512 hr_utility.set_message(801, 'HR_51532_PHN_FK_NOT_FOUND');
1513 hr_utility.raise_error;
1514 end if;
1515
1516 end return_legislation_parent;
1517
1518 end per_phn_bus;