DBA Data[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;