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