[Home] [Help]
PACKAGE BODY: APPS.PER_CTR_BUS
Source
1 Package Body per_ctr_bus as
2 /* $Header: pectrrhi.pkb 120.0.12000000.2 2007/02/19 11:59:36 ssutar ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ctr_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_contact_relationship_id number default null;
15 --
16 -- Bug 3114717 Start
17 -- ---------------------------------------------------------------------------
18 -- |----------------------------< chk_y_or_n>--------------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure chk_y_or_n
22 (p_effective_date in date
23 ,p_flag in varchar2
24 ,p_flag_name in varchar2)
25 IS
26 l_proc VARCHAR2(72) := g_package||'chk_y_or_n';
27 begin
28 hr_utility.set_location('Entering:'|| l_proc, 10);
29 --
30 --
31 IF hr_api.not_exists_in_hrstanlookups
32 (p_effective_date => p_effective_date
33 ,p_lookup_type => 'YES_NO'
34 ,p_lookup_code => p_flag
35 ) THEN
36 fnd_message.set_name('801','HR_52970_COL_Y_OR_N');
37 fnd_message.set_token('COLUMN',p_flag_name);
38 fnd_message.raise_error;
39 end if;
40 --
41 hr_utility.set_location('Leaving:'||l_proc, 20);
42 --
43 end chk_y_or_n;
44 -- Bug 3114717 End
45
46 -- ---------------------------------------------------------------------------
47 -- |--------------------------< chk_person_id >------------------------------|
48 -- ---------------------------------------------------------------------------
49 --
50 -- Description:
51 -- Validates that the person ID exists in PER_PEOPLE_F.
52 --
53 -- Pre-Requisites:
54 -- None.
55 --
56 -- In Parameters:
57 -- p_person_id
58 -- p_business_group_id
59 --
60 -- Post Success:
61 -- Processing continues if the person_id is valid.
62 --
63 -- Post Failure:
64 -- An Application Error is raised and processing is terminated if the
65 -- person_id is invalid.
66 --
67 -- Access Status:
68 -- Internal Development use only.
69 --
70 -- ---------------------------------------------------------------------
71 --
72 procedure chk_person_id
73 (p_person_id in number
74 ,p_business_group_id in number
75 ,p_effective_date in date
76 )
77 is
78 --
79 l_business_group_id number(15);
80 l_proc varchar2(72) := g_package||'chk_person_id';
81 --
82 cursor csr_get_bus_grp is
83 select ppf.business_group_id
84 from per_all_people_f ppf -- bug 3577703. Use table instead of secure view.
85 where ppf.person_id = p_person_id
86 and p_effective_date between
87 ppf.effective_start_date and ppf.effective_end_date;
88 --
89 --
90 begin
91 hr_utility.set_location('Entering:'|| l_proc, 1);
92 --
93 -- Check mandatory parameters have been set
94 --
95 hr_api.mandatory_arg_error
96 (p_api_name => l_proc
97 ,p_argument => 'person_id'
98 ,p_argument_value => p_person_id
99 );
100 --
101 hr_utility.set_location(l_proc, 10);
102 --
103 -- Check that the person id is a valid value
104 --
105 open csr_get_bus_grp;
106 fetch csr_get_bus_grp into l_business_group_id;
107 if csr_get_bus_grp%NOTFOUND then
108 close csr_get_bus_grp;
109 hr_utility.set_message(801, 'HR_51389_CRT_INV_PERSON_ID');
110 hr_utility.raise_error;
111 --
112 end if;
113 close csr_get_bus_grp;
114 hr_utility.set_location(l_proc,20);
115 --
116 -- Check that person business group is the same as
117 -- the contact relationship business group
118 --
119 if l_business_group_id <> p_business_group_id then
120 --
121 hr_utility.set_message(801, 'HR_7374_ASG_INVALID_BG_PERSON');
122 hr_utility.raise_error;
123 --
124 end if;
125 --
126 hr_utility.set_location(' Leaving:'|| l_proc, 30);
127 end chk_person_id;
128 --
129 -- ---------------------------------------------------------------------------
130 -- |--------------------------< chk_contact_person_id >------------------------------|
131 -- ---------------------------------------------------------------------------
132 --
133 -- Description:
134 -- Validates that the contact_person ID exists in PER_PEOPLE_F.
135 --
136 -- Pre-Requisites:
137 -- None.
138 --
139 -- In Parameters:
140 -- p_contact_person_id
141 -- p_business_group_id
142 --
143 -- Post Success:
144 -- Processing continues if the person_id is valid.
145 --
146 -- Post Failure:
147 -- An Application Error is raised and processing is terminated if the
148 -- contact_person_id is invalid.
149 --
150 -- Access Status:
151 -- Internal Development use only.
152 --
153 -- ---------------------------------------------------------------------
154 --
155 procedure chk_contact_person_id
156 (p_contact_person_id in number
157 ,p_business_group_id in number
158 ,p_effective_date in date
159 )
160 is
161 --
162 l_business_group_id number(15);
163 l_proc varchar2(72) := g_package||'chk_person_id';
164 --
165 cursor csr_get_bus_grp is
166 select ppf.business_group_id
167 from per_all_people_f ppf -- Bug 3577703. Use table per_all_people_f.
168 where ppf.person_id = p_contact_person_id
169 and p_effective_date between
170 ppf.effective_start_date and ppf.effective_end_date;
171 --
172 --
173 begin
174 hr_utility.set_location('Entering:'|| l_proc, 1);
175 --
176 -- Check mandatory parameters have been set
177 --
178 hr_api.mandatory_arg_error
179 (p_api_name => l_proc
180 ,p_argument => 'contact_person_id'
181 ,p_argument_value => p_contact_person_id
182 );
183 --
184 hr_utility.set_location(l_proc, 10);
185 --
186 -- Check that the person id is a valid value
187 --
188 open csr_get_bus_grp;
189 fetch csr_get_bus_grp into l_business_group_id;
190 if csr_get_bus_grp%NOTFOUND then
191 close csr_get_bus_grp;
192 hr_utility.set_message(801, 'HR_51389_CRT_INV_PERSON_ID');
193 hr_utility.raise_error;
194 --
195 end if;
196 close csr_get_bus_grp;
197 hr_utility.set_location(l_proc,20);
198 --
199 -- Check that person business group is the same as
200 -- the contact relationship business group
201 --
202 if (l_business_group_id <> p_business_group_id AND
203 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='N') then
204 --
205 hr_utility.set_message(801, 'HR_7374_ASG_INVALID_BG_PERSON');
206 hr_utility.raise_error;
207 --
208 end if;
209 --
210 hr_utility.set_location(' Leaving:'|| l_proc, 30);
211 end chk_contact_person_id;
212 --
213 -- ---------------------------------------------------------------------------
214 -- |--------------------------< chk_contact_type >---------------------------|
215 -- ---------------------------------------------------------------------------
216 --
217 -- Description:
218 -- Validates that the contact type exists in hr_lookups
219 --
220 -- Pre-Requisites
221 -- None
222 --
223 -- In Parameters:
224 -- p_effective_date
225 -- p_object_version_number
226 -- p_contact_type
227 -- p_contact_relationship_id
228 --
229 -- Post Success:
230 -- Processing continues if the contact type is valid
231 --
232 -- Post Failure:
233 -- An Application error is raised and processing is terminated if the
234 -- contact type is invalid.
235 --
236 -- Access Status:
237 -- Internal Development use only.
238 --
239 -- -----------------------------------------------------------------------
240 --
241 procedure chk_contact_type
242 (p_effective_date in date,
243 p_object_version_number in
244 per_contact_relationships.object_version_number%TYPE,
245 p_contact_type in
246 per_contact_relationships.contact_type%TYPE,
247 p_contact_relationship_id in
248 per_contact_relationships.contact_relationship_id%TYPE
249 ) is
250 --
251 l_proc varchar2(72) := g_package||'chk_contact_type';
252 l_api_updating boolean;
253 --
254 begin
255 --
256 hr_utility.set_location('Entering:'|| l_proc,1);
257 --
258 -- Check mandatory parameters have been set
259 --
260 hr_api.mandatory_arg_error
261 (p_api_name => l_proc
262 ,p_argument => 'effective date'
263 ,p_argument_value => p_effective_date
264 );
265 --
266 --
267 -- Check to see if record updated.
268 --
269 l_api_updating := per_ctr_shd.api_updating
270 (p_contact_relationship_id => p_contact_relationship_id
271 ,p_object_version_number => p_object_version_number);
272 --
273 hr_utility.set_location(l_proc, 2);
274 --
275 if ((l_api_updating
276 and per_ctr_shd.g_old_rec.contact_type <> p_contact_type)
277 or (NOT l_api_updating))
278 then
279 --
280 if p_contact_type is null then
281 hr_utility.set_message(801,'HR_51379_CTR_INV_CONT_TYPE');
282 hr_utility.raise_error;
283 end if;
284 -- 05/28/97 Check for mandatory argument for update only
285 IF l_api_updating THEN
286 hr_api.mandatory_arg_error
287 (p_api_name => l_proc
288 ,p_argument => 'object version number'
289 ,p_argument_value => p_object_version_number
290 );
291 --
292 hr_api.mandatory_arg_error
293 (p_api_name => l_proc
294 ,p_argument => 'contact relationship id'
295 ,p_argument_value => p_contact_relationship_id
296 );
297 --
298 END IF;
299 -- 05/28/97 Change Ends
300 --
301 -- Bug 1472162.
302 --
303 -- if hr_api.not_exists_in_hr_lookups
304 if hr_api.not_exists_in_leg_lookups
305 (p_effective_date => p_effective_date
306 ,p_lookup_type => 'CONTACT'
307 ,p_lookup_code => p_contact_type
308 ) then
309 -- Error: Invalid contact_type
310 hr_utility.set_location(l_proc, 10);
311 hr_utility.set_message(801,'HR_51379_CTR_INV_CONT_TYPE');
312 hr_utility.raise_error;
313 end if;
314 --
315 end if;
316 --
317 hr_utility.set_location(' Leaving:'||l_proc, 20);
318 --
319 end chk_contact_type;
320 --
321 -- ---------------------------------------------------------------------
322 -- |--------------------< chk_primary_contact >-------------------------|
323 -- ---------------------------------------------------------------------
324 --
325 -- Description:
326 -- Validates that the person only has one primary contact at any
327 -- point in time.
328 --
329 -- Pre-Requisites:
330 -- None.
331 --
332 -- In Parameters:
333 -- p_contact_relationship_id
334 -- p_person_id
335 -- p_object_version_number
336 -- p_primary_contact_flag
337 -- p_date_start
338 -- p_date_end
339 --
340 -- Post Success:
341 -- Processing continues if the p_contact_relationship_id and person_id
342 -- are valid.
343 --
344 -- Post Failure:
345 -- An Application Error is raised and processing is terminated if either the
346 -- p_contact_relationship_id or person_id is invalid.
347 --
348 -- Access Status:
349 -- Internal Development use only.
350 --
351 -- ---------------------------------------------------------------------
352 procedure chk_primary_contact(p_contact_relationship_id number,
353 p_person_id NUMBER,
354 p_object_version_number in number,
355 p_primary_contact_flag in
356 per_contact_relationships.primary_contact_flag%TYPE,
357 p_date_start date,
358 p_date_end date) is
359 cursor csr_chk_primary_cnt is
360 select 'Y'
361 from per_contact_relationships
362 where person_id = p_person_id
363 and primary_contact_flag = 'Y'
364 and (nvl(date_end,hr_general.end_of_time)
365 > nvl(p_date_start,hr_general.start_of_time)
366 and nvl(date_start,hr_general.start_of_time)
367 < nvl(p_date_end,hr_general.end_of_time));
368 --
369 l_exists varchar2(1) := 'N';
370 l_proc varchar2(72) := g_package||'chk_contact_type';
371 --
372 l_api_updating boolean;
373 begin
374 --
375 -- Check to see if record updated.
376 --
377 l_api_updating := per_ctr_shd.api_updating
378 (p_contact_relationship_id => p_contact_relationship_id
379 ,p_object_version_number => p_object_version_number);
380 --
381 hr_utility.set_location(l_proc, 2);
382 --
386 then
383 if ((l_api_updating
384 and per_ctr_shd.g_old_rec.primary_contact_flag <> p_primary_contact_flag)
385 or (NOT l_api_updating))
387 --
388 if p_primary_contact_flag not in ('Y','N') then
389 hr_utility.set_message(801,'HR_51388_CTR_INV_P_CONT_FLAG');
390 hr_utility.raise_error;
391 end if;
392 --
393 hr_utility.set_location('Entering:'|| l_proc,1);
394 --
395 if p_primary_contact_flag = 'Y' then
396 --
397 open csr_chk_primary_cnt;
398 fetch csr_chk_primary_cnt into l_exists;
399 if csr_chk_primary_cnt%FOUND then
400 close csr_chk_primary_cnt;
401 hr_utility.set_message(801, 'PER_7125_EMP_CON_PRIMARY');
402 hr_utility.raise_error;
403 --
404 end if;
405 close csr_chk_primary_cnt;
406 --
407 end if;
408 --
409 end if;
410 hr_utility.set_location('Leaving: '|| l_proc,1);
411 --
412 end chk_primary_contact;
413 -- ---------------------------------------------------------------------
414 -- |--------------------< chk_start_life_reason_id >-------------------------|
415 -- ---------------------------------------------------------------------
416 --
417 -- Description:
418 -- Validates that the start_life_reason_id exists in BEN_LER_F on the
419 -- effective_date.
420 --
421 -- Pre-Requisites:
422 -- None.
423 --
424 -- In Parameters:
425 -- p_contact_relationship_id
426 -- p_start_life_reason_id
427 -- p_effective_date
428 -- p_object_version_number
429 --
430 --
431 -- Post Success:
432 -- Processing continues if the start_life_reason_id is valid.
433 --
434 --
435 -- Post Failure:
436 -- An Application Error is raised and processing is terminated if
437 -- start_life_reason_id is invalid.
438 --
439 -- Access Status:
440 -- Internal Development use only.
441 --
442 -- ---------------------------------------------------------------------
443 procedure chk_start_life_reason_id(p_contact_relationship_id number,
444 p_start_life_reason_id in number,
445 p_effective_date in date,
446 p_object_version_number in number) is
447 cursor csr_chk_start_life_reason_id is
448 select ler_id
449 from BEN_LER_F
450 where p_start_life_reason_id = ler_id
451 and p_effective_date between effective_start_date and
452 effective_end_date;
453 --
454 l_start_life_reason_id number;
455 l_proc varchar2(72) := g_package||'chk_start_life_reason_id';
456 --
457 begin
458 --
459 hr_utility.set_location('Entering:'|| l_proc,5);
460 --
461 if (p_contact_relationship_id is not null
462 and per_ctr_shd.g_old_rec.start_life_reason_id
463 <> p_start_life_reason_id
464 and p_start_life_reason_id is not null)
465 or (p_contact_relationship_id is null
466 and p_start_life_reason_id is NOT NULL)
467 then
468 open csr_chk_start_life_reason_id;
469 fetch csr_chk_start_life_reason_id into l_start_life_reason_id;
470 if csr_chk_start_life_reason_id%NOTFOUND then
471 close csr_chk_start_life_reason_id;
472 hr_utility.set_message(800, 'PER_52380_START_LIFE_REASON');
473 hr_utility.raise_error;
474 --
475 end if;
476 close csr_chk_start_life_reason_id;
477 end if;
478 --
479 hr_utility.set_location(' Leaving:'||l_proc, 10);
480 --
481 end chk_start_life_reason_id;
482
483 -- ---------------------------------------------------------------------
484 -- |--------------------< chk_end_life_reason_id >-------------------------|
485 -- ---------------------------------------------------------------------
486 --
487 -- Description:
488 -- Validates that the end_life_reason_id exists in BEN_LER_F for a set
489 -- effective_date.
490 --
491 -- Pre-Requisites:
492 -- None.
493 --
494 -- In Parameters:
495 -- p_contact_relationship_id
496 -- p_end_life_reason_id
497 -- p_effective_date
498 -- p_object_version_number
499 --
500 --
501 -- Post Success:
502 -- Processing continues if the end_life_reason_id is valid in BEN_LER_F
503 -- on the set effective_date.
504 --
505 -- Post Failure:
506 -- An Application Error is raised and processing is terminated if
507 -- end_life_reason_id is invalid for that effective_date.
508 --
509 -- Access Status:
510 -- Internal Development use only.
511 --
512 -- ---------------------------------------------------------------------
513 procedure chk_end_life_reason_id(p_contact_relationship_id number
514 ,p_end_life_reason_id in number
515 ,p_effective_date in date
516 ,p_object_version_number in number) is
517 --
518 cursor csr_chk_end_life_reason_id is
519 select ler_id
520 from BEN_LER_F
521 where p_end_life_reason_id = ler_id
522 and p_effective_date between effective_start_date and
523 effective_end_date;
524 --
525 l_end_life_reason_id number;
526 l_proc varchar2(72) := g_package||'chk_end_life_reason_id';
527 --
528 begin
529 --
533 if (p_contact_relationship_id is not null
530 hr_utility.set_location('Entering:'|| l_proc,5);
531 --
532 --
534 and per_ctr_shd.g_old_rec.end_life_reason_id
535 <> p_end_life_reason_id
536 and p_end_life_reason_id is not null)
537 or (p_contact_relationship_id is null
538 and p_end_life_reason_id is NOT NULL)
539 then
540 open csr_chk_end_life_reason_id;
541 fetch csr_chk_end_life_reason_id into l_end_life_reason_id;
542 if csr_chk_end_life_reason_id%NOTFOUND then
543 close csr_chk_end_life_reason_id;
544 hr_utility.set_message(800, 'PER_52381_END_LIFE_REASON');
545 hr_utility.raise_error;
546 --
547 end if;
548 close csr_chk_end_life_reason_id;
549 --
550 end if;
551 --
552 hr_utility.set_location(' Leaving:'||l_proc, 10);
553 --
554 end chk_end_life_reason_id;
555 --
556 -- ---------------------------------------------------------------------
557 -- |--------------------< chk_date_start_end >-------------------------|
558 -- ---------------------------------------------------------------------
559 --
560 -- Description:
561 -- Validates that date_end is later than date_start and that date_end
562 -- cannot be entered if date_start is null.
563 --
564 -- Pre-Requisites:
565 -- None.
566 --
567 -- In Parameters:
568 -- p_contact_relationship_id
569 -- p_date_start
570 -- p_date_end
571 -- p_object_version_number
572 --
573 --
574 -- Post Success:
575 -- Processing continues if date_end is later than date_start and
576 -- therefore both are valid and date_end is not entered if date_start
577 -- is null.
578 --
579 --
580 -- Post Failure:
581 -- An Application Error is raised and processing is terminated if
582 -- date_end is invalid.
583 --
584 -- Access Status:
585 -- Internal Development use only.
586 --
587 -- ---------------------------------------------------------------------
588 procedure chk_date_start_end(p_contact_relationship_id number
589 ,p_date_start in date
590 ,p_date_end in date
591 ,p_object_version_number in number) is
592 --
593 l_proc varchar2(72) := g_package||'chk_date_start_end';
594 --
595 begin
596 --
597 hr_utility.set_location('Entering:'|| l_proc,5);
598 --
599 if p_date_start is NOT NULL and
600 -- changed for bug 1995269 p_date_start >= nvl(p_date_end, hr_general.end_of_time) then
601 p_date_start > nvl(p_date_end, hr_general.end_of_time) then
602 hr_utility.set_message(800,'PER_7003_ALL_DATE_FROM_TO');
603 hr_utility.raise_error;
604 end if;
605 --
606 if p_date_start is NULL and
607 p_date_end is NOT NULL then
608 hr_utility.set_message(800,'PER_52384_START_END_DATE');
609 hr_utility.raise_error;
610 end if;
611 --
612 hr_utility.set_location(' Leaving:'||l_proc, 10);
613 --
614 end chk_date_start_end;
615
616
617 -- ---------------------------------------------------------------------
618 -- |--------------------< chk_time_validation >-------------------------|
619 -- ---------------------------------------------------------------------
620 --
621 -- Description:
622 -- Validates that only one relationship of the same type exists in
623 -- PER_CONTACT_RELATIONSHIPS on the same effective date.
624 --
625 -- Pre-Requisites:
626 -- None.
627 --
628 -- In Parameters:
629 -- p_contact_type
630 -- p_person_id
631 -- p_contact_person_id
632 -- p_date_start
633 -- p_date_end
634 -- p_object_version_number
635 --
636 --
637 -- Post Success:
638 -- Processing continues if there is no other contact relationship
639 -- of the same type existing at the same time.
640 --
641 --
642 -- Post Failure:
643 -- An Application Error is raised and processing is terminated if
644 -- another relationship of the same type exists at the same time.
645 --
646 -- Access Status:
647 -- Internal Development use only.
648 --
649 -- ---------------------------------------------------------------------
650 procedure chk_time_validation(p_contact_type varchar2,
651 p_person_id in number,
652 p_contact_person_id in number,
653 p_contact_relationship_id in number,
654 p_date_start DATE,
655 p_date_end DATE,
656 p_object_version_number in number) is
657 l_records varchar2(1);
658 l_start_of_time date := hr_general.start_of_time;
659 l_end_of_time date := hr_general.end_of_time;
660
661 cursor csr_chk_time_validation is
662 select 'X'
663 from per_contact_relationships per
664 where per.person_id = p_person_id
665 and per.contact_person_id = p_contact_person_id
666 and (per.contact_relationship_id <> p_contact_relationship_id
667 or p_contact_relationship_id is null)
668 and per.contact_type = p_contact_type
669 and nvl(p_date_end,l_end_of_time) >= nvl(date_start,l_start_of_time)
670 and nvl(p_date_start,l_start_of_time) <= nvl(date_end,l_end_of_time);
671 --
672 l_proc varchar2(72) := g_package||'chk_time_validation';
676 hr_utility.set_location('Entering:'|| l_proc,5);
673 --
674 begin
675 --
677 --
678 if (p_contact_relationship_id is not null
679 and nvl(per_ctr_shd.g_old_rec.date_start, hr_api.g_date)
680 <> nvl(p_date_start, hr_api.g_date)
681 or nvl(per_ctr_shd.g_old_rec.date_end, hr_api.g_date)
682 <> nvl(p_date_end, hr_api.g_date)
683 or nvl(per_ctr_shd.g_old_rec.contact_type, hr_api.g_varchar2)
684 <> nvl(p_contact_type, hr_api.g_varchar2))
685 or p_contact_relationship_id is null then
686 --
687 open csr_chk_time_validation;
688 fetch csr_chk_time_validation into l_records;
689 if csr_chk_time_validation%FOUND then
690 hr_utility.set_message(800,'PER_6996_REL_CURR_EXISTS');
691 hr_utility.raise_error;
692 --
693 end if;
694
695 close csr_chk_time_validation;
696 --
697 end if;
698 --
699 hr_utility.set_location(' Leaving:'||l_proc, 10);
700 --
701 end chk_time_validation;
702 --
703 -- ----------------------------------------------------------------------------
704 -- |------------------< chk_sequence_number >---------------------------------|
705 -- ----------------------------------------------------------------------------
706 --
707 -- Description:
708 -- Validates that the sequence number for all relationships between two people
709 -- are the same and that this sequence number is unique for that person_id. ie
710 -- the person with the contact does not have the same sequence number for a
711 -- relationship with any other person. It also validates that the sequence
712 -- number can only be updated from null.
713 --
714 --
715 -- Pre-Requisites:
716 -- None.
717 --
718 -- In Parameters:
719 -- p_contact_relationship_id
720 -- p_sequence_number
721 -- p_contact_person_id
722 -- p_person_id
723 -- p_object_version_number
724 --
725 -- Post Success:
726 -- Processing continues if the sequence number matches the sequence number
727 -- for any other relationship between these two people and does not match
728 -- a sequence number for a relationship between the person a different contact.
729 -- Also continues if updating the sequence number from null.
730 --
731 --
732 -- Post Failure:
733 -- An Application Error is raised and processing is terminated if the sequence
734 -- number is not the same as an existing sequence number for a relationship
735 -- between these two people, or if the sequence number already exists for
736 -- a relationship between the person and a different contact. Processing is
737 -- also terminated if an update is attempted where the sequence number is not
738 -- null.
739 --
740 --
741 -- Access Status:
742 -- Internal Development use only.
743 -------------------------------------------------------------------------------
744 procedure chk_sequence_number(p_contact_relationship_id in number,
745 p_sequence_number in number,
746 p_contact_person_id in number,
747 p_person_id in number,
748 p_object_version_number in number) is
749 --
750 l_proc varchar2(72) := g_package||'chk_sequence_number';
751 l_sequence_number number;
752 l_sequence_other number;
753 --
754 cursor csr_seq is
755 select sequence_number
756 from per_contact_relationships con
757 where con.person_id = p_person_id
758 and con.contact_person_id = p_contact_person_id
759 and con.sequence_number <> p_sequence_number;
760 --
761 cursor csr_seq_others is
762 select sequence_number
763 from per_contact_relationships con
764 where con.person_id = p_person_id
765 and con.contact_person_id <> p_contact_person_id
766 and con.sequence_number = p_sequence_number;
767 --
768 begin
769 --
770 hr_utility.set_location(l_proc, 1);
771 --
772 if p_contact_relationship_id is null or
776 --
773 (p_contact_relationship_id is not null and
774 nvl(per_ctr_shd.g_old_rec.sequence_number,hr_api.g_number)
775 <> nvl(p_sequence_number,hr_api.g_number)) then
777 if p_sequence_number is not null and
778 p_contact_person_id is not null then
779 if per_ctr_shd.g_old_rec.sequence_number is null then
780 open csr_seq;
781 fetch csr_seq into l_sequence_number;
782 if csr_seq%FOUND then
783 close csr_seq;
784 hr_utility.set_message('800','PER_52509_USE_SEQ_NO');
785 hr_utility.raise_error;
786 else
787 open csr_seq_others;
788 fetch csr_seq_others into l_sequence_other;
789 if csr_seq_others%FOUND then
790 close csr_seq_others;
791 hr_utility.set_message('800','PER_52510_DIFF_SEQ_NO');
792 hr_utility.raise_error;
793 end if;
794 end if;
795 --fix 1322770 Sequence no. may only be UPDATED from null.
796 elsif (p_contact_relationship_id is not null
797 and p_sequence_number <> per_ctr_shd.g_old_rec.sequence_number) then
798 hr_utility.set_message('800','PER_52511_SEQ_NO_UPD');
799 hr_utility.raise_error;
800 end if;
801 elsif (p_contact_relationship_id is not null
802 and per_ctr_shd.g_old_rec.sequence_number is not null) then
803 hr_utility.set_message('800','PER_52511_SEQ_NO_UPD');
804 hr_utility.raise_error;
805 end if;
806 end if;
807 end chk_sequence_number;
808 --
809 -- ---------------------------------------------------------------------------
810 -- |--------------------------< chk_date_of_birth >------------------------------|
811 -- ---------------------------------------------------------------------------
812 -- Bug fix 3326964.
813 -- Description:
814 -- Validates that the relationship start date is greater or equal to
815 -- person's date of birth.
816 --
817 -- Pre-Requisites:
818 -- None.
819 --
820 -- In Parameters:
821 -- p_person_id
822 -- p_contact_person_id
823 -- p_date_start
824 --
825 --
826 -- Post Success:
827 -- Processing continues if the relationship start date is valid.
828 --
829 -- Post Failure:
830 -- An Application Error is raised and processing is terminated if the
831 -- - relationship start date is less than person's date of brith
832 --
833 -- Access Status:
834 -- Internal Development use only.
835 --
836 -- ---------------------------------------------------------------------
837 --
838 procedure chk_date_of_birth
839 (p_person_id in number
840 ,p_contact_person_id in number
841 ,p_date_start in date
842 )
843 is
844 --
845 l_business_group_id number(15);
846 l_proc varchar2(72) := g_package||'chk_date_of_birth';
847 --
848 cursor csr_get_date_of_birth( l_person_id number ) is
849 select date_of_birth
850 from per_people_f ppf
851 where ppf.person_id = l_person_id;
852 --
853 l_contact_date_of_birth date;
854 l_person_date_of_birth date;
855 --
856 begin
857 hr_utility.set_location('Entering:'|| l_proc, 1);
858 --
859 -- Check mandatory parameters have been set
860 --
861 hr_api.mandatory_arg_error
862 (p_api_name => l_proc
863 ,p_argument => 'person_id'
864 ,p_argument_value => p_person_id
865 );
866 --
867 hr_utility.set_location(l_proc, 10);
868 --
869 --
870 -- Check mandatory parameters have been set
871 --
872 hr_api.mandatory_arg_error
873 (p_api_name => l_proc
874 ,p_argument => 'contact_person_id'
875 ,p_argument_value => p_contact_person_id
876 );
877 --
878 hr_utility.set_location(l_proc, 20);
879 --
880
881 -- To fetch person date of birth.
882
883 open csr_get_date_of_birth( p_person_id );
884 fetch csr_get_date_of_birth into l_person_date_of_birth;
885 close csr_get_date_of_birth;
886
887 -- To fetch contact date of birth.
888
889 open csr_get_date_of_birth( p_contact_person_id );
890 fetch csr_get_date_of_birth into l_contact_date_of_birth;
891 close csr_get_date_of_birth;
892
893
894 -- condition to check whether the relationship start date
895 -- is less than person's date of birth or less than contact date of birth.
896
897 if p_date_start is not null and
898 ( p_date_start < nvl( l_person_date_of_birth,hr_api.g_sot ) or
899 p_date_start < nvl( l_contact_date_of_birth,hr_api.g_sot ) ) then
900 hr_utility.set_message('800','PER_50386_CON_SDT_LES_EMP_BDT');
901 hr_utility.raise_error;
902 end if;
903 --
904
905 --
906 hr_utility.set_location('Leaving:'|| l_proc, 20);
907 --
908 end chk_date_of_birth;
909 -- ----------------------------------------------------------------------------
910 -- |--------------------< check_non_updateable_args >-------------------------|
911 -- ----------------------------------------------------------------------------
912 Procedure check_non_updateable_args(p_rec in per_ctr_shd.g_rec_type)
913 is
914 --
918 --
915 l_proc varchar2(72) := g_package||'check_non_updateable_args';
916 l_error exception;
917 l_argument varchar2(30);
919 begin
920 hr_utility.set_location('Entering:'||l_proc, 5);
921 --
922 -- Only proceed with validation if a row exists for
923 -- the current record in the HR Schema
924 --
925 if not per_ctr_shd.api_updating
926 (p_contact_relationship_id => p_rec.contact_relationship_id
927 ,p_object_version_number => p_rec.object_version_number
928 ) then
929 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
930 hr_utility.set_message_token('PROCEDURE', l_proc);
931 hr_utility.set_message_token('STEP', '5');
932 hr_utility.raise_error;
933 end if;
934 --
935 hr_utility.set_location(l_proc, 6);
936 --
937 --
938 if nvl(p_rec.business_group_id, hr_api.g_number) <>
939 per_ctr_shd.g_old_rec.business_group_id then
940 l_argument := 'business_group_id';
941 raise l_error;
942 end if;
943 --
944 if nvl(p_rec.person_id, hr_api.g_number) <>
945 per_ctr_shd.g_old_rec.person_id then
946 l_argument := 'person_id';
947 raise l_error;
948 end if;
949 --
950 if nvl(p_rec.contact_person_id, hr_api.g_number) <>
951 per_ctr_shd.g_old_rec.contact_person_id then
952 l_argument := 'contact_person_id';
953 raise l_error;
954 end if;
955 --
956 exception
957 when l_error then
958 hr_api.argument_changed_error
959 (p_api_name => l_proc
960 ,p_argument => l_argument);
961 when others then
962 raise;
963 hr_utility.set_location(' Leaving:'||l_proc, 14);
964 end check_non_updateable_args;
965 --
966 -- ---------------------------------------------------------------------------
967 -- |----------------------< chk_df >----------------------------------------|
968 -- ---------------------------------------------------------------------------
969 --
970 -- Description:
971 -- Validates all the Descriptive Flexfield values.
972 --
973 -- Pre-conditions:
974 -- All other columns have been validated. Must be called as the
975 -- last step from insert_validate and update_validate.
976 --
977 -- In Arguments:
978 -- p_rec
979 --
980 -- Post Success:
981 -- If the Descriptive Flexfield structure column and data values are
982 -- all valid this procedure will end normally and processing will
983 -- continue.
984 --
985 -- Post Failure:
986 -- If the Descriptive Flexfield structure column value or any of
987 -- the data values are invalid then an application error is raised as
988 -- a PL/SQL exception.
989 --
990 -- Access Status:
991 -- Internal Table Handler Use Only.
992 -- ---------------------------------------------------------------------------
993 procedure chk_df
994 (p_rec in per_ctr_shd.g_rec_type) is
995 --
996 l_proc varchar2(72) := g_package||'chk_df';
997 --
998 begin
999 hr_utility.set_location('Entering:'||l_proc, 10);
1000 --
1001 if ((p_rec.contact_relationship_id is not null) and (
1002 nvl(per_ctr_shd.g_old_rec.cont_attribute_category, hr_api.g_varchar2) <>
1003 nvl(p_rec.cont_attribute_category, hr_api.g_varchar2) or
1004 nvl(per_ctr_shd.g_old_rec.cont_attribute1, hr_api.g_varchar2) <>
1005 nvl(p_rec.cont_attribute1, hr_api.g_varchar2) or
1006 nvl(per_ctr_shd.g_old_rec.cont_attribute2, hr_api.g_varchar2) <>
1007 nvl(p_rec.cont_attribute2, hr_api.g_varchar2) or
1008 nvl(per_ctr_shd.g_old_rec.cont_attribute3, hr_api.g_varchar2) <>
1009 nvl(p_rec.cont_attribute3, hr_api.g_varchar2) or
1010 nvl(per_ctr_shd.g_old_rec.cont_attribute4, hr_api.g_varchar2) <>
1011 nvl(p_rec.cont_attribute4, hr_api.g_varchar2) or
1012 nvl(per_ctr_shd.g_old_rec.cont_attribute5, hr_api.g_varchar2) <>
1013 nvl(p_rec.cont_attribute5, hr_api.g_varchar2) or
1014 nvl(per_ctr_shd.g_old_rec.cont_attribute6, hr_api.g_varchar2) <>
1015 nvl(p_rec.cont_attribute6, hr_api.g_varchar2) or
1016 nvl(per_ctr_shd.g_old_rec.cont_attribute7, hr_api.g_varchar2) <>
1017 nvl(p_rec.cont_attribute7, hr_api.g_varchar2) or
1018 nvl(per_ctr_shd.g_old_rec.cont_attribute8, hr_api.g_varchar2) <>
1019 nvl(p_rec.cont_attribute8, hr_api.g_varchar2) or
1020 nvl(per_ctr_shd.g_old_rec.cont_attribute9, hr_api.g_varchar2) <>
1021 nvl(p_rec.cont_attribute9, hr_api.g_varchar2) or
1022 nvl(per_ctr_shd.g_old_rec.cont_attribute10, hr_api.g_varchar2) <>
1023 nvl(p_rec.cont_attribute10, hr_api.g_varchar2) or
1024 nvl(per_ctr_shd.g_old_rec.cont_attribute11, hr_api.g_varchar2) <>
1025 nvl(p_rec.cont_attribute11, hr_api.g_varchar2) or
1026 nvl(per_ctr_shd.g_old_rec.cont_attribute12, hr_api.g_varchar2) <>
1027 nvl(p_rec.cont_attribute12, hr_api.g_varchar2) or
1028 nvl(per_ctr_shd.g_old_rec.cont_attribute13, hr_api.g_varchar2) <>
1029 nvl(p_rec.cont_attribute13, hr_api.g_varchar2) or
1030 nvl(per_ctr_shd.g_old_rec.cont_attribute14, hr_api.g_varchar2) <>
1031 nvl(p_rec.cont_attribute14, hr_api.g_varchar2) or
1032 nvl(per_ctr_shd.g_old_rec.cont_attribute15, hr_api.g_varchar2) <>
1033 nvl(p_rec.cont_attribute15, hr_api.g_varchar2) or
1034 nvl(per_ctr_shd.g_old_rec.cont_attribute16, hr_api.g_varchar2) <>
1038 nvl(per_ctr_shd.g_old_rec.cont_attribute18, hr_api.g_varchar2) <>
1035 nvl(p_rec.cont_attribute16, hr_api.g_varchar2) or
1036 nvl(per_ctr_shd.g_old_rec.cont_attribute17, hr_api.g_varchar2) <>
1037 nvl(p_rec.cont_attribute17, hr_api.g_varchar2) or
1039 nvl(p_rec.cont_attribute18, hr_api.g_varchar2) or
1040 nvl(per_ctr_shd.g_old_rec.cont_attribute19, hr_api.g_varchar2) <>
1041 nvl(p_rec.cont_attribute19, hr_api.g_varchar2) or
1042 nvl(per_ctr_shd.g_old_rec.cont_attribute20, hr_api.g_varchar2) <>
1043 nvl(p_rec.cont_attribute20, hr_api.g_varchar2)))
1044 or
1045 (p_rec.contact_relationship_id is null) then
1046 --
1047 -- Only execute the validation if absolutely necessary:
1048 -- a) During update, the structure column value or any
1049 -- of the attribute values have actually changed.
1050 -- b) During insert.
1051 --
1052 hr_dflex_utility.ins_or_upd_descflex_attribs
1053 (p_appl_short_name => 'PER'
1054 ,p_descflex_name => 'PER_CONTACTS'
1055 ,p_attribute_category => p_rec.cont_attribute_category
1056 ,p_attribute1_name => 'CONT_ATTRIBUTE1'
1057 ,p_attribute1_value => p_rec.cont_attribute1
1058 ,p_attribute2_name => 'CONT_ATTRIBUTE2'
1059 ,p_attribute2_value => p_rec.cont_attribute2
1060 ,p_attribute3_name => 'CONT_ATTRIBUTE3'
1061 ,p_attribute3_value => p_rec.cont_attribute3
1062 ,p_attribute4_name => 'CONT_ATTRIBUTE4'
1063 ,p_attribute4_value => p_rec.cont_attribute4
1064 ,p_attribute5_name => 'CONT_ATTRIBUTE5'
1065 ,p_attribute5_value => p_rec.cont_attribute5
1066 ,p_attribute6_name => 'CONT_ATTRIBUTE6'
1067 ,p_attribute6_value => p_rec.cont_attribute6
1068 ,p_attribute7_name => 'CONT_ATTRIBUTE7'
1069 ,p_attribute7_value => p_rec.cont_attribute7
1070 ,p_attribute8_name => 'CONT_ATTRIBUTE8'
1071 ,p_attribute8_value => p_rec.cont_attribute8
1072 ,p_attribute9_name => 'CONT_ATTRIBUTE9'
1073 ,p_attribute9_value => p_rec.cont_attribute9
1074 ,p_attribute10_name => 'CONT_ATTRIBUTE10'
1075 ,p_attribute10_value => p_rec.cont_attribute10
1076 ,p_attribute11_name => 'CONT_ATTRIBUTE11'
1077 ,p_attribute11_value => p_rec.cont_attribute11
1078 ,p_attribute12_name => 'CONT_ATTRIBUTE12'
1079 ,p_attribute12_value => p_rec.cont_attribute12
1080 ,p_attribute13_name => 'CONT_ATTRIBUTE13'
1081 ,p_attribute13_value => p_rec.cont_attribute13
1082 ,p_attribute14_name => 'CONT_ATTRIBUTE14'
1083 ,p_attribute14_value => p_rec.cont_attribute14
1084 ,p_attribute15_name => 'CONT_ATTRIBUTE15'
1085 ,p_attribute15_value => p_rec.cont_attribute15
1086 ,p_attribute16_name => 'CONT_ATTRIBUTE16'
1087 ,p_attribute16_value => p_rec.cont_attribute16
1088 ,p_attribute17_name => 'CONT_ATTRIBUTE17'
1089 ,p_attribute17_value => p_rec.cont_attribute17
1090 ,p_attribute18_name => 'CONT_ATTRIBUTE18'
1091 ,p_attribute18_value => p_rec.cont_attribute18
1092 ,p_attribute19_name => 'CONT_ATTRIBUTE19'
1093 ,p_attribute19_value => p_rec.cont_attribute19
1094 ,p_attribute20_name => 'CONT_ATTRIBUTE20'
1095 ,p_attribute20_value => p_rec.cont_attribute20
1096 );
1097 end if;
1098 --
1099 hr_utility.set_location('Leaving:'||l_proc,20);
1100 end chk_df;
1101 --
1102 -- ---------------------------------------------------------------------------
1103 -- |----------------------< chk_ddf >----------------------------------------|
1104 -- ---------------------------------------------------------------------------
1105 --
1106 -- Description:
1107 -- Validates all the Developer Descriptive Flexfield values.
1108 --
1109 -- Pre-conditions:
1110 -- All other columns have been validated. Must be called as the
1111 -- last step from insert_validate and update_validate.
1112 --
1113 -- In Arguments:
1114 -- p_rec
1115 --
1116 -- Post Success:
1117 -- If the Descriptive Flexfield structure column and data values are
1118 -- all valid this procedure will end normally and processing will
1119 -- continue.
1120 --
1121 -- Post Failure:
1122 -- If the Descriptive Flexfield structure column value or any of
1123 -- the data values are invalid then an application error is raised as
1124 -- a PL/SQL exception.
1125 --
1126 -- Access Status:
1127 -- Internal Table Handler Use Only.
1128 -- ---------------------------------------------------------------------------
1129 procedure chk_ddf
1130 (p_rec in per_ctr_shd.g_rec_type) is
1131 --
1132 l_proc varchar2(72) := g_package||'chk_ddf';
1133 --
1134 begin
1135 hr_utility.set_location('Entering:'||l_proc, 10);
1136 --
1137 if ((p_rec.contact_relationship_id is not null) and (
1138 nvl(per_ctr_shd.g_old_rec.cont_information_category, hr_api.g_varchar2) <>
1139 nvl(p_rec.cont_information_category, hr_api.g_varchar2) or
1140 nvl(per_ctr_shd.g_old_rec.cont_information1, hr_api.g_varchar2) <>
1141 nvl(p_rec.cont_information1, hr_api.g_varchar2) or
1142 nvl(per_ctr_shd.g_old_rec.cont_information2, hr_api.g_varchar2) <>
1143 nvl(p_rec.cont_information2, hr_api.g_varchar2) or
1144 nvl(per_ctr_shd.g_old_rec.cont_information3, hr_api.g_varchar2) <>
1148 nvl(per_ctr_shd.g_old_rec.cont_information5, hr_api.g_varchar2) <>
1145 nvl(p_rec.cont_information3, hr_api.g_varchar2) or
1146 nvl(per_ctr_shd.g_old_rec.cont_information4, hr_api.g_varchar2) <>
1147 nvl(p_rec.cont_information4, hr_api.g_varchar2) or
1149 nvl(p_rec.cont_information5, hr_api.g_varchar2) or
1150 nvl(per_ctr_shd.g_old_rec.cont_information6, hr_api.g_varchar2) <>
1151 nvl(p_rec.cont_information6, hr_api.g_varchar2) or
1152 nvl(per_ctr_shd.g_old_rec.cont_information7, hr_api.g_varchar2) <>
1153 nvl(p_rec.cont_information7, hr_api.g_varchar2) or
1154 nvl(per_ctr_shd.g_old_rec.cont_information8, hr_api.g_varchar2) <>
1155 nvl(p_rec.cont_information8, hr_api.g_varchar2) or
1156 nvl(per_ctr_shd.g_old_rec.cont_information9, hr_api.g_varchar2) <>
1157 nvl(p_rec.cont_information9, hr_api.g_varchar2) or
1158 nvl(per_ctr_shd.g_old_rec.cont_information10, hr_api.g_varchar2) <>
1159 nvl(p_rec.cont_information10, hr_api.g_varchar2) or
1160 nvl(per_ctr_shd.g_old_rec.cont_information11, hr_api.g_varchar2) <>
1161 nvl(p_rec.cont_information11, hr_api.g_varchar2) or
1162 nvl(per_ctr_shd.g_old_rec.cont_information12, hr_api.g_varchar2) <>
1163 nvl(p_rec.cont_information12, hr_api.g_varchar2) or
1164 nvl(per_ctr_shd.g_old_rec.cont_information13, hr_api.g_varchar2) <>
1165 nvl(p_rec.cont_information13, hr_api.g_varchar2) or
1166 nvl(per_ctr_shd.g_old_rec.cont_information14, hr_api.g_varchar2) <>
1167 nvl(p_rec.cont_information14, hr_api.g_varchar2) or
1168 nvl(per_ctr_shd.g_old_rec.cont_information15, hr_api.g_varchar2) <>
1169 nvl(p_rec.cont_information15, hr_api.g_varchar2) or
1170 nvl(per_ctr_shd.g_old_rec.cont_information16, hr_api.g_varchar2) <>
1171 nvl(p_rec.cont_information16, hr_api.g_varchar2) or
1172 nvl(per_ctr_shd.g_old_rec.cont_information17, hr_api.g_varchar2) <>
1173 nvl(p_rec.cont_information17, hr_api.g_varchar2) or
1174 nvl(per_ctr_shd.g_old_rec.cont_information18, hr_api.g_varchar2) <>
1175 nvl(p_rec.cont_information18, hr_api.g_varchar2) or
1176 nvl(per_ctr_shd.g_old_rec.cont_information19, hr_api.g_varchar2) <>
1177 nvl(p_rec.cont_information19, hr_api.g_varchar2) or
1178 nvl(per_ctr_shd.g_old_rec.cont_information20, hr_api.g_varchar2) <>
1179 nvl(p_rec.cont_information20, hr_api.g_varchar2)))
1180 or
1181 (p_rec.contact_relationship_id is null) then
1182 --
1183 -- Only execute the validation if absolutely necessary:
1184 -- a) During update, the structure column value or any
1185 -- of the attribute values have actually changed.
1186 -- b) During insert.
1187 --
1188 hr_dflex_utility.ins_or_upd_descflex_attribs
1189 (p_appl_short_name => 'PER'
1190 ,p_descflex_name => 'Contact Relship Developer DF'
1191 ,p_attribute_category => p_rec.cont_information_category
1192 ,p_attribute1_name => 'CONT_INFORMATION1'
1193 ,p_attribute1_value => p_rec.cont_information1
1194 ,p_attribute2_name => 'CONT_INFORMATION2'
1195 ,p_attribute2_value => p_rec.cont_information2
1196 ,p_attribute3_name => 'CONT_INFORMATION3'
1197 ,p_attribute3_value => p_rec.cont_information3
1198 ,p_attribute4_name => 'CONT_INFORMATION4'
1199 ,p_attribute4_value => p_rec.cont_information4
1200 ,p_attribute5_name => 'CONT_INFORMATION5'
1201 ,p_attribute5_value => p_rec.cont_information5
1202 ,p_attribute6_name => 'CONT_INFORMATION6'
1203 ,p_attribute6_value => p_rec.cont_information6
1204 ,p_attribute7_name => 'CONT_INFORMATION7'
1205 ,p_attribute7_value => p_rec.cont_information7
1206 ,p_attribute8_name => 'CONT_INFORMATION8'
1207 ,p_attribute8_value => p_rec.cont_information8
1208 ,p_attribute9_name => 'CONT_INFORMATION9'
1209 ,p_attribute9_value => p_rec.cont_information9
1210 ,p_attribute10_name => 'CONT_INFORMATION10'
1211 ,p_attribute10_value => p_rec.cont_information10
1212 ,p_attribute11_name => 'CONT_INFORMATION11'
1213 ,p_attribute11_value => p_rec.cont_information11
1214 ,p_attribute12_name => 'CONT_INFORMATION12'
1215 ,p_attribute12_value => p_rec.cont_information12
1216 ,p_attribute13_name => 'CONT_INFORMATION13'
1217 ,p_attribute13_value => p_rec.cont_information13
1218 ,p_attribute14_name => 'CONT_INFORMATION14'
1219 ,p_attribute14_value => p_rec.cont_information14
1220 ,p_attribute15_name => 'CONT_INFORMATION15'
1221 ,p_attribute15_value => p_rec.cont_information15
1222 ,p_attribute16_name => 'CONT_INFORMATION16'
1223 ,p_attribute16_value => p_rec.cont_information16
1224 ,p_attribute17_name => 'CONT_INFORMATION17'
1225 ,p_attribute17_value => p_rec.cont_information17
1226 ,p_attribute18_name => 'CONT_INFORMATION18'
1227 ,p_attribute18_value => p_rec.cont_information18
1228 ,p_attribute19_name => 'CONT_INFORMATION19'
1229 ,p_attribute19_value => p_rec.cont_information19
1230 ,p_attribute20_name => 'CONT_INFORMATION20'
1231 ,p_attribute20_value => p_rec.cont_information20
1232 );
1233 end if;
1234 --
1235 hr_utility.set_location('Leaving:'||l_proc,20);
1236 end chk_ddf;
1237 --
1238 -- ----------------------------------------------------------------------------
1242 ,p_effective_date in date) is
1239 -- |---------------------------< insert_validate >----------------------------|
1240 -- ----------------------------------------------------------------------------
1241 Procedure insert_validate(p_rec in per_ctr_shd.g_rec_type
1243 --
1244 l_proc varchar2(72) := g_package||'insert_validate';
1245 --
1246 Begin
1247 hr_utility.set_location('Entering:'||l_proc, 5);
1248 --
1249 -- Call all supporting business operations. Mapping to the
1250 -- appropriate Business Rules in perctr.bru is provided (where
1251 -- relevant)
1252 --
1253 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1254 --
1255 -- Bug 3114717 Start
1256 --
1257 -- Validate primary_contact_flag
1258 --
1259 chk_y_or_n
1260 (p_effective_date => p_effective_date
1261 ,p_flag => p_rec.primary_contact_flag
1262 ,p_flag_name => 'PRIMARY CONTACT FLAG'
1263 );
1264 --
1265 -- Validate third_party_pay_flag
1266 --
1267 chk_y_or_n
1268 (p_effective_date => p_effective_date
1269 ,p_flag => p_rec.third_party_pay_flag
1270 ,p_flag_name => 'THIRD PARTY PAY FLAG');
1271 --
1272 -- Validate rltd_per_rsds_w_dsgntr_flag
1273 --
1274 chk_y_or_n
1275 (p_effective_date => p_effective_date
1276 ,p_flag => p_rec.rltd_per_rsds_w_dsgntr_flag
1277 ,p_flag_name => 'SHARED RESIDENCY FLAG');
1278 --
1279 -- Validate personal_flag
1280 --
1281 chk_y_or_n
1282 (p_effective_date => p_effective_date
1283 ,p_flag => p_rec.personal_flag
1284 ,p_flag_name => 'PERSONAL FLAG');
1285 --
1286 -- Validate beneficiary_flag
1287 --
1288 chk_y_or_n
1289 (p_effective_date => p_effective_date
1290 ,p_flag => p_rec.beneficiary_flag
1291 ,p_flag_name => 'BENEFICIARY FLAG');
1292 --
1293 -- Validate dependent_flag
1294 --
1295 chk_y_or_n
1296 (p_effective_date => p_effective_date
1297 ,p_flag => p_rec.dependent_flag
1298 ,p_flag_name => 'DEPENDENT_FLAG');
1299 -- Bug 3114717 End
1300
1301 --
1302 -- Validate person_id
1303 --
1304 per_ctr_bus.chk_person_id
1305 (p_person_id => p_rec.person_id
1306 ,p_business_group_id => p_rec.business_group_id
1307 ,p_effective_date => p_effective_date
1308 );
1309 --
1310 hr_utility.set_location(l_proc, 10);
1311 --
1312 --
1313 /* JZYLI 5/8/00 disable Global contact relation for now.
1314 Will enable it after OK from OAB.
1315 per_ctr_bus.chk_contact_person_id
1316 (p_contact_person_id => p_rec.contact_person_id
1317 */
1318 per_ctr_bus.chk_person_id
1319 (p_person_id => p_rec.contact_person_id
1320 ,p_business_group_id => p_rec.business_group_id
1321 ,p_effective_date => p_effective_date
1322 );
1323 hr_utility.set_location(l_proc, 20);
1324 --
1325 --
1326 per_ctr_bus.chk_contact_type
1327 (p_effective_date => p_effective_date
1328 ,p_contact_relationship_id => p_rec.contact_relationship_id
1329 ,p_contact_type => p_rec.contact_type
1330 ,p_object_version_number => p_rec.object_version_number
1331 );
1332 --
1333 -- Check that there's only one primary contact
1334 --
1335 chk_primary_contact(p_contact_relationship_id => p_rec.contact_relationship_id,
1336 p_person_id => p_rec.person_id,
1337 p_object_version_number => p_rec.object_version_number,
1338 p_primary_contact_flag => p_rec.primary_contact_flag,
1339 p_date_start => p_rec.date_start,
1340 p_date_end => p_rec.date_end
1341 );
1342 --
1343 hr_utility.set_location(l_proc, 30);
1344 --
1345 chk_start_life_reason_id(p_contact_relationship_id => p_rec.contact_relationship_id
1346 ,p_start_life_reason_id => p_rec.start_life_reason_id
1347 ,p_effective_date => p_effective_date
1348 ,p_object_version_number => p_rec.object_version_number
1349 );
1350 --
1351 hr_utility.set_location(l_proc, 40);
1352 --
1353 chk_end_life_reason_id(p_contact_relationship_id => p_rec.contact_relationship_id
1354 ,p_end_life_reason_id => p_rec.end_life_reason_id
1355 ,p_effective_date => p_effective_date
1356 ,p_object_version_number => p_rec.object_version_number
1357 );
1358
1359 --
1360 hr_utility.set_location(' Leaving:'||l_proc, 50);
1361 --
1362 chk_date_start_end(p_contact_relationship_id => p_rec.contact_relationship_id
1363 ,p_date_start => p_rec.date_start
1364 ,p_date_end => p_rec.date_end
1365 ,p_object_version_number => p_rec.object_version_number
1366 );
1367 --
1371 ,p_person_id => p_rec.person_id
1368 hr_utility.set_location(' Leaving:'||l_proc, 60);
1369 --
1370 chk_time_validation(p_contact_type => p_rec.contact_type
1372 ,p_contact_person_id => p_rec.contact_person_id
1373 ,p_contact_relationship_id => p_rec.contact_relationship_id
1374 ,p_date_start => p_rec.date_start
1375 ,p_date_end => p_rec.date_end
1376 ,p_object_version_number => p_rec.object_version_number
1377 );
1378 --
1379 hr_utility.set_location(' Leaving:'||l_proc, 70);
1380 --
1381 chk_sequence_number(p_contact_relationship_id => p_rec.contact_relationship_id
1382 ,p_sequence_number => p_rec.sequence_number
1383 ,p_contact_person_id => p_rec.contact_person_id
1384 ,p_person_id => p_rec.person_id
1385 ,p_object_version_number => p_rec.object_version_number
1386 );
1387 --
1388 hr_utility.set_location(' Leaving:'||l_proc,80);
1389 --
1390 -- Bug fix 3326964.
1391 -- Function call to check whether relation ship start date is less tha
1392 -- contact's or person's date of birth.
1393
1394 chk_date_of_birth( p_person_id => p_rec.person_id
1395 ,p_contact_person_id => p_rec.contact_person_id
1396 ,p_date_start => p_rec.date_start
1397 );
1398 --
1399 hr_utility.set_location(' Leaving:'||l_proc,90);
1400 --
1401
1402
1403 chk_ddf(p_rec => p_rec);
1404 --
1405 chk_df(p_rec => p_rec);
1406 --
1407 End insert_validate;
1408 --
1409 -- ----------------------------------------------------------------------------
1410 -- |---------------------------< update_validate >----------------------------|
1411 -- ----------------------------------------------------------------------------
1412 Procedure update_validate(p_rec in per_ctr_shd.g_rec_type
1413 ,p_effective_date in date) is
1414 --
1415 l_proc varchar2(72) := g_package||'update_validate';
1416 --
1417 Begin
1418 hr_utility.set_location('Entering:'||l_proc, 5);
1419 --
1420 --
1421 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1422 --
1423 -- Check the non-updateable arguments have in fact not been modified
1424 --
1425 per_ctr_bus.check_non_updateable_args(p_rec => p_rec);
1426 --
1427 --
1428 -- bug fix 3982865
1429 -- Validation added to check the flag value while updating
1430 -- contact relationship.
1431
1432 if ( p_rec.primary_contact_flag <> hr_api.g_varchar2 OR
1433 p_rec.primary_contact_flag is null ) then
1434 chk_y_or_n
1435 (p_effective_date => p_effective_date
1436 ,p_flag => p_rec.primary_contact_flag
1437 ,p_flag_name => 'PRIMARY CONTACT FLAG'
1438 );
1439 end if;
1440 --
1441 -- Validate third_party_pay_flag
1442 --
1443 if ( p_rec.third_party_pay_flag <> hr_api.g_varchar2 OR
1444 p_rec.third_party_pay_flag is null ) then
1445 chk_y_or_n
1446 (p_effective_date => p_effective_date
1447 ,p_flag => p_rec.third_party_pay_flag
1448 ,p_flag_name => 'THIRD PARTY PAY FLAG');
1449 end if;
1450 --
1451 -- Validate rltd_per_rsds_w_dsgntr_flag
1452 --
1453 if ( p_rec.rltd_per_rsds_w_dsgntr_flag <> hr_api.g_varchar2 OR
1454 p_rec.rltd_per_rsds_w_dsgntr_flag is null ) then
1455 chk_y_or_n
1456 (p_effective_date => p_effective_date
1457 ,p_flag => p_rec.rltd_per_rsds_w_dsgntr_flag
1458 ,p_flag_name => 'SHARED RESIDENCY FLAG');
1459 end if;
1460 --
1464 p_rec.personal_flag is null ) then
1461 -- Validate personal_flag
1462 --
1463 if ( p_rec.personal_flag <> hr_api.g_varchar2 OR
1465 chk_y_or_n
1466 (p_effective_date => p_effective_date
1467 ,p_flag => p_rec.personal_flag
1468 ,p_flag_name => 'PERSONAL FLAG');
1469 end if;
1470 --
1471 -- Validate beneficiary_flag
1472 --
1473 if ( p_rec.beneficiary_flag <> hr_api.g_varchar2 OR
1474 p_rec.beneficiary_flag is null ) then
1475 chk_y_or_n
1476 (p_effective_date => p_effective_date
1477 ,p_flag => p_rec.beneficiary_flag
1478 ,p_flag_name => 'BENEFICIARY FLAG');
1479 end if;
1480 --
1481 -- Validate dependent_flag
1482 --
1483 if ( p_rec.dependent_flag <> hr_api.g_varchar2 OR
1484 p_rec.dependent_flag is null ) then
1485 chk_y_or_n
1486 (p_effective_date => p_effective_date
1487 ,p_flag => p_rec.dependent_flag
1488 ,p_flag_name => 'DEPENDENT_FLAG');
1489 end if;
1490
1491 -- bug 3982865 ends here.
1492
1493
1494 per_ctr_bus.chk_contact_type
1495 (p_effective_date => p_effective_date
1496 ,p_contact_relationship_id => p_rec.contact_relationship_id
1497 ,p_contact_type => p_rec.contact_type
1498 ,p_object_version_number => p_rec.object_version_number
1499 );
1500 --
1501 -- Check that there's only one primary contact
1502 --
1503 chk_primary_contact(p_contact_relationship_id => p_rec.contact_relationship_id,
1504 p_person_id => p_rec.person_id,
1505 p_primary_contact_flag => p_rec.primary_contact_flag,
1506 p_object_version_number => p_rec.object_version_number,
1507 p_date_start => p_rec.date_start,
1508 p_date_end => p_rec.date_end
1509 );
1510 --
1511 hr_utility.set_location(' Leaving:'||l_proc, 10);
1512 --
1513 chk_start_life_reason_id(p_contact_relationship_id => p_rec.contact_relationship_id,
1514 p_start_life_reason_id => p_rec.start_life_reason_id,
1515 p_effective_date => p_effective_date,
1516 p_object_version_number => p_rec.object_version_number
1517 );
1518 --
1519 hr_utility.set_location('Leaving:'||l_proc, 20);
1520 --
1521 chk_end_life_reason_id(p_contact_relationship_id => p_rec.contact_relationship_id
1522 ,p_end_life_reason_id => p_rec.end_life_reason_id
1523 ,p_effective_date => p_effective_date
1524 ,p_object_version_number => p_rec.object_version_number
1525 );
1526 --
1527 hr_utility.set_location('Leaving:'||l_proc, 30);
1528 --
1529 chk_date_start_end(p_contact_relationship_id => p_rec.contact_relationship_id
1530 ,p_date_start => p_rec.date_start
1531 ,p_date_end => p_rec.date_end
1532 ,p_object_version_number => p_rec.object_version_number
1533 );
1534 --
1535 hr_utility.set_location(' Leaving:'||l_proc, 40);
1536 --
1537 chk_time_validation(p_contact_type => p_rec.contact_type
1538 ,p_person_id => p_rec.person_id
1539 ,p_contact_person_id => p_rec.contact_person_id
1540 ,p_contact_relationship_id => p_rec.contact_relationship_id
1541 ,p_date_start => p_rec.date_start
1542 ,p_date_end => p_rec.date_end
1543 ,p_object_version_number => p_rec.object_version_number
1544 );
1545 --
1546 hr_utility.set_location(' Leaving:'||l_proc, 50);
1547 --
1548 chk_sequence_number(p_contact_relationship_id => p_rec.contact_relationship_id
1549 ,p_sequence_number => p_rec.sequence_number
1550 ,p_contact_person_id => p_rec.contact_person_id
1551 ,p_person_id => p_rec.person_id
1552 ,p_object_version_number => p_rec.object_version_number
1553 );
1554 --
1555 hr_utility.set_location(' Leaving:'||l_proc,60);
1556 --
1557 -- Bug fix 3326964.
1558 -- Function call to check whether relation ship start date is less tha
1559 -- contact's or person's date of birth.
1560 chk_date_of_birth( p_person_id => p_rec.person_id
1561 ,p_contact_person_id => p_rec.contact_person_id
1562 ,p_date_start => p_rec.date_start
1563 );
1564 --
1565 hr_utility.set_location(' Leaving:'||l_proc,70);
1566 --
1567 chk_ddf(p_rec => p_rec);
1568 --
1569 chk_df(p_rec => p_rec);
1570 --
1571 End update_validate;
1572 --
1573 -- ----------------------------------------------------------------------------
1574 -- |---------------------------< delete_validate >----------------------------|
1575 -- ----------------------------------------------------------------------------
1576 Procedure delete_validate(p_rec in per_ctr_shd.g_rec_type) is
1577 --
1578 l_proc varchar2(72) := g_package||'delete_validate';
1579 --
1580 Begin
1581 hr_utility.set_location('Entering:'||l_proc, 5);
1582 --
1583 -- Call all supporting business operations
1584 --
1585 hr_utility.set_location(' Leaving:'||l_proc, 10);
1586 End delete_validate;
1587 --
1588 -- ---------------------------------------------------------------------------
1589 -- |---------------------< return_legislation_code >-------------------------|
1590 -- ---------------------------------------------------------------------------
1591 --
1592 function return_legislation_code
1593 (p_contact_relationship_id in number
1594 ) return varchar2 is
1595 --
1596 -- Declare cursor
1597 --
1598 cursor csr_leg_code is
1599 select pbg.legislation_code
1600 from per_business_groups pbg
1601 , per_contact_relationships ctr
1602 where ctr.contact_relationship_id = p_contact_relationship_id
1603 and pbg.business_group_id = ctr.business_group_id;
1604 --
1605 -- Declare local variables
1606 --
1607 l_legislation_code varchar2(150);
1608 l_proc varchar2(72) := g_package||'return_legislation_code';
1609 begin
1610 hr_utility.set_location('Entering:'|| l_proc, 10);
1611 --
1612 -- Ensure that all the mandatory parameter are not null
1613 --
1614 hr_api.mandatory_arg_error(p_api_name => l_proc,
1615 p_argument => 'contact_relationship_id',
1616 p_argument_value => p_contact_relationship_id);
1617 --
1618 if nvl(g_contact_relationship_id, hr_api.g_number) = p_contact_relationship_id then
1619 --
1620 -- The legislation code has already been found with a previous
1621 -- call to this function. Just return the value in the global
1622 -- variable.
1623 --
1624 l_legislation_code := g_legislation_code;
1625 hr_utility.set_location(l_proc, 20);
1626 else
1627 --
1628 -- The ID is different to the last call to this function
1629 -- or this is the first call to this function.
1630 --
1631 open csr_leg_code;
1632 fetch csr_leg_code into l_legislation_code;
1633 if csr_leg_code%notfound then
1634 close csr_leg_code;
1635 --
1636 -- The primary key is invalid therefore we must error
1637 --
1638 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1639 hr_utility.raise_error;
1640 end if;
1641 --
1642 close csr_leg_code;
1643 g_contact_relationship_id:= p_contact_relationship_id;
1644 g_legislation_code := l_legislation_code;
1645 end if;
1646 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1647 --
1648 return l_legislation_code;
1649 end return_legislation_code;
1650 --
1651 end per_ctr_bus;