[Home] [Help]
PACKAGE BODY: APPS.PER_DIS_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body per_dis_bus as
2 /* $Header: pedisrhi.pkb 115.8 2002/12/04 18:57:24 pkakar noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_dis_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_disability_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_disability_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , per_disabilities_f dis
30 , per_people_f per
31 where dis.disability_id = p_disability_id
32 and dis.person_id = per.person_id
33 and pbg.business_group_id = per.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 --
40 begin
41 --
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43 --
44 -- Ensure that all the mandatory parameter are not null
45 --
46 hr_api.mandatory_arg_error
47 (p_api_name => l_proc
48 ,p_argument => 'disability_id'
49 ,p_argument_value => p_disability_id
53 fetch csr_sec_grp into l_security_group_id;
50 );
51 --
52 open csr_sec_grp;
54 --
55 if csr_sec_grp%notfound then
56 --
57 close csr_sec_grp;
58 --
59 -- The primary key is invalid therefore we must error
60 --
61 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
62 fnd_message.raise_error;
63 --
64 end if;
65 close csr_sec_grp;
66 --
67 -- Set the security_group_id in CLIENT_INFO
68 --
69 hr_api.set_security_group_id
70 (p_security_group_id => l_security_group_id
71 );
72 --
73 hr_utility.set_location(' Leaving:'|| l_proc, 20);
74 --
75 end set_security_group_id;
76 --
77 -- ---------------------------------------------------------------------------
78 -- |---------------------< return_legislation_code >-------------------------|
79 -- ---------------------------------------------------------------------------
80 --
81 Function return_legislation_code
82 (p_disability_id in number
83 )
84 Return Varchar2 Is
85 --
86 -- Declare cursor
87 --
88 cursor csr_leg_code is
89 select pbg.legislation_code
90 from per_business_groups pbg
91 , per_disabilities_f dis
92 , per_people_f per
93 where dis.disability_id = p_disability_id
94 and dis.person_id = per.person_id
95 and pbg.business_group_id = per.business_group_id;
96 --
97 -- Declare local variables
98 --
99 l_legislation_code varchar2(150);
100 l_proc varchar2(72) := g_package||'return_legislation_code';
101 --
102 Begin
103 --
104 hr_utility.set_location('Entering:'|| l_proc, 10);
105 --
106 -- Ensure that all the mandatory parameter are not null
107 --
108 hr_api.mandatory_arg_error
109 (p_api_name => l_proc
110 ,p_argument => 'disability_id'
111 ,p_argument_value => p_disability_id
112 );
113 --
114 if ( nvl(per_dis_bus.g_disability_id, hr_api.g_number)
115 = p_disability_id) then
116 --
117 -- The legislation code has already been found with a previous
118 -- call to this function. Just return the value in the global
119 -- variable.
120 --
121 l_legislation_code := per_dis_bus.g_legislation_code;
122 hr_utility.set_location(l_proc, 20);
123 else
124 --
125 -- The ID is different to the last call to this function
126 -- or this is the first call to this function.
127 --
128 open csr_leg_code;
129 fetch csr_leg_code into l_legislation_code;
130 --
131 if csr_leg_code%notfound then
132 --
133 -- The primary key is invalid therefore we must error
134 --
135 close csr_leg_code;
136 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
137 fnd_message.raise_error;
138 end if;
139 hr_utility.set_location(l_proc,30);
140 --
141 -- Set the global variables so the values are
142 -- available for the next call to this function.
143 --
144 close csr_leg_code;
145 per_dis_bus.g_disability_id := p_disability_id;
146 per_dis_bus.g_legislation_code := l_legislation_code;
147 end if;
148 hr_utility.set_location(' Leaving:'|| l_proc, 40);
149 return l_legislation_code;
150 end return_legislation_code;
151 --
152 -- ---------------------------------------------------------------------------
153 -- |--------------------------< chk_person_id >------------------------------|
154 -- ---------------------------------------------------------------------------
155 --
156 -- Desciption :
157 --
158 -- Validate that PERSON_ID is not null and that
159 -- it exists in per_people_f on the effective_date.
160 -- (Insert only - non updateable)
161 --
162 -- Pre-conditions :
163 --
164 --
165 -- In Arguments :
166 -- p_person_id
167 -- p_effective_date
168 --
169 -- Post Success :
170 -- Processing continues
171 --
172 -- Post Failure :
173 -- An application error will be raised and processing is
174 -- terminated
175 --
176 -- Access Status :
177 -- Internal Table Handler Use only.
178 --
179 -- {End of Comments}
180 --
181 -- ---------------------------------------------------------------------------
182 procedure chk_person_id
183 (p_person_id in per_disabilities_f.person_id%TYPE
184 ,p_effective_date in per_disabilities_f.effective_start_date%TYPE
185 )
186 is
187 --
188 cursor csr_person is
189 select null
190 from per_people_f ppf
191 where ppf.person_id = p_person_id
192 and p_effective_date between ppf.effective_start_date
193 and ppf.effective_end_date;
194 --
195 l_exists varchar2(1);
196 l_proc varchar2(72) := g_package||'chk_person_id';
197 --
198 --
199 begin
200 hr_utility.set_location('Entering:'|| l_proc, 1);
201 --
202 -- Check mandatory parameter is set
203 --
204 hr_api.mandatory_arg_error
205 (p_api_name => l_proc
206 ,p_argument => 'effective_date'
207 ,p_argument_value => p_effective_date
208 );
209 --
210 if p_person_id is null then
211 hr_utility.set_message(800, 'HR_52892_DIS_PERSON_NULL');
215 hr_utility.set_location(l_proc, 2);
212 hr_utility.raise_error;
213 else
214 --
216 --
217 -- Check that person exists on this effective date
218 --
219 open csr_person;
220 fetch csr_person into l_exists;
221 if csr_person%notfound then
222 close csr_person;
223 hr_utility.set_message(800, 'HR_52911_DIS_INV_PERSON');
224 hr_utility.raise_error;
225 end if;
226 close csr_person;
227 end if;
228 --
229 hr_utility.set_location(' Leaving:'|| l_proc, 3);
230 end chk_person_id;
231 --
232 --
233 -- ---------------------------------------------------------------------------
234 -- |----------------------------< chk_incident_id >--------------------------|
235 -- ---------------------------------------------------------------------------
236 --
237 -- Desciption :
238 --
239 -- Validate that INCIDENT_ID exists in per_work_incidents on the
240 -- effective_date for the person_id. Check that the value matches
241 -- that of the work incident that is parent of the medical assessment record
242 -- that has previously been linked to the disability, if one exists.
243 --
244 -- Pre-conditions :
245 --
246 --
247 -- In Arguments :
248 -- p_disability_id
249 -- p_person_id
250 -- p_effective_date
251 -- p_incident_id
252 --
253 -- Post Success :
254 -- Processing continues
255 --
256 -- Post Failure :
257 -- An application error will be raised and processing is
258 -- terminated
259 --
260 -- Access Status :
261 -- Internal Table Handler Use only.
262 --
263 -- {End of Comments}
264 --
265 -- ---------------------------------------------------------------------------
266 procedure chk_incident_id
267 (p_disability_id in per_disabilities_f.disability_id%TYPE
268 ,p_person_id in per_disabilities_f.person_id%TYPE
269 ,p_effective_date in per_disabilities_f.effective_start_date%TYPE
270 ,p_incident_id in per_disabilities_f.incident_id%TYPE
271 )
272 is
273 --
274 cursor csr_inc is
275 select null
276 from per_work_incidents pwi
277 where pwi.person_id = p_person_id
278 and pwi.incident_id = p_incident_id
279 and p_effective_date >= pwi.incident_date;
280 --
281 cursor csr_mea is
282 select null
283 from per_medical_assessments pma
284 where pma.disability_id = p_disability_id
285 and pma.incident_id <> p_incident_id
286 and pma.consultation_result = 'DIS';
287
288 l_proc varchar2(72) := g_package||'chk_incident_id';
289 l_dummy varchar2(1);
290 --
291 --
292 begin
293 hr_utility.set_location('Entering:'|| l_proc, 1);
294 --
295 -- Check mandatory parameter is set
296 --
297 hr_api.mandatory_arg_error
298 (p_api_name => l_proc
299 ,p_argument => 'effective_date'
300 ,p_argument_value => p_effective_date
301 );
302 --
303 hr_api.mandatory_arg_error
304 (p_api_name => l_proc
305 ,p_argument => 'person_id'
306 ,p_argument_value => p_person_id
307 );
308 --
309 -- validate if:
310 -- 1) inserting
311 -- 2) updating and value has changed
312 --
313 if ((p_disability_id is null) or
314 ((p_disability_id is not null) and
315 (per_dis_shd.g_old_rec.incident_id <> p_incident_id))) then
316 --
317 hr_utility.set_location(l_proc, 3);
318
319 if p_incident_id is not null then
320 --
321 -- Check the incident exists in per_work_incidents
322 -- for the person_id, with incident_date <= effective_date
323 --
324 open csr_inc;
325 fetch csr_inc into l_dummy;
326 if csr_inc%NOTFOUND then
327 close csr_inc;
328 hr_utility.set_message(800, 'HR_289017_DIS_INV_INC');
329 hr_utility.raise_error;
330 end if;
331 close csr_inc;
332 --
333 hr_utility.set_location(l_proc, 6);
334 --
335 -- The value must match that of the work incident id that is held
336 -- on the parent medical assessment record that has previously been
337 -- linked to the disability, if one exists.
338 --
339 hr_utility.set_location(l_proc, 8);
340 open csr_mea;
341 fetch csr_mea into l_dummy;
342 if csr_mea%FOUND then
343 close csr_mea;
344 hr_utility.set_message(800, 'HR_289047_DIS_INV_INC_MEA');
345 hr_utility.raise_error;
346 end if;
347 close csr_mea;
348 --
349 end if;
350 --
351 end if;
352 --
353 --
354 hr_utility.set_location(' Leaving:'|| l_proc, 10);
355 end chk_incident_id;
356 --
357 -- ---------------------------------------------------------------------------
358 -- |---------------------------< chk_category >------------------------------|
359 -- ---------------------------------------------------------------------------
360 --
361 -- Desciption :
362 --
363 -- Validate that mandatory CATEGORY value is not null and that
364 -- it exists and is enabled in hr_leg_lookups view for the type
365 -- 'DISABILITY_CATEGORY' on the validation date range.
366 --
367 --
368 -- Pre-conditions :
369 --
373 -- p_category
370 --
371 -- In Arguments :
372 -- p_disability_id
374 -- p_effective_date
375 -- p_validation_start_date
376 -- p_validation_end_date
377 --
378 -- Post Success :
379 -- Processing continues
380 --
381 -- Post Failure :
382 -- An application error will be raised and processing is
383 -- terminated
384 --
385 -- Access Status :
386 -- Internal Table Handler Use only.
387 --
388 -- {End of Comments}
389 --
390 -- ---------------------------------------------------------------------------
391 procedure chk_category
392 (p_disability_id in per_disabilities_f.disability_id%TYPE
393 ,p_category in per_disabilities_f.category%TYPE
394 ,p_effective_date in date
395 ,p_validation_start_date in date
396 ,p_validation_end_date in date
397 )
398 is
399 --
400 l_proc varchar2(72) := g_package||'chk_category';
401 --
402 begin
403 hr_utility.set_location('Entering:'|| l_proc, 10);
404 --
405 -- Check mandatory arguments
406 --
407 hr_api.mandatory_arg_error
408 (p_api_name => l_proc
409 ,p_argument => 'effective_date'
410 ,p_argument_value => p_effective_date
411 );
412 --
413 hr_api.mandatory_arg_error
414 (p_api_name => l_proc
415 ,p_argument => 'validation_start_date'
416 ,p_argument_value => p_validation_start_date
417 );
418 --
419 hr_api.mandatory_arg_error
420 (p_api_name => l_proc
421 ,p_argument => 'validation_end_date'
422 ,p_argument_value => p_validation_end_date
423 );
424 hr_utility.set_location(l_proc, 20);
425 --
426 -- Ensure mandatory category is set
427 if p_category is null then
428 hr_utility.set_message(800, 'HR_52912_DIS_CATEGORY_NULL');
429 hr_utility.raise_error;
430 else
431 --
432 -- validate if:
433 -- 1) inserting
434 -- 2) updating and value has changed
435 --
436 if ((p_disability_id is null) or
437 ((p_disability_id is not null) and
438 (per_dis_shd.g_old_rec.category <> p_category))) then
439 --
440 hr_utility.set_location(l_proc, 30);
441 --
442 if hr_api.not_exists_in_dt_leg_lookups
443 (p_effective_date => p_effective_date
444 ,p_validation_start_date => p_validation_start_date
445 ,p_validation_end_date => p_validation_end_date
446 ,p_lookup_type => 'DISABILITY_CATEGORY'
447 ,p_lookup_code => p_category
448 )
449 then
450 --
451 hr_utility.set_message(800, 'HR_52913_DIS_INV_CATEGORY');
452 hr_utility.raise_error;
453 end if;
454 end if;
455 end if;
456 --
457 hr_utility.set_location(' Leaving:'|| l_proc, 40);
458 --
459 end chk_category;
460 --
461 -- ---------------------------------------------------------------------------
462 -- |-----------------------< chk_status >--------------------------------------|
463 -- ---------------------------------------------------------------------------
464 --
465 -- Description:
466 -- Check that the passed in lookup code exists in hr_lookups for the with an
467 -- enabled flag set to 'Y' and that the effective start date of the disability
468 -- is between start date active and end date active in hr_lookups.
469 --
470 -- Pre-conditions:
471 -- None
472 --
473 -- In Arguments:
474 -- p_disability_id
475 -- p_status
476 -- p_effective_date
477 -- p_validation_start_date
478 -- p_validation_end_date
479 --
480 -- Post Success:
481 -- If lookup exists and can be derived then processing
482 -- continues
483 --
484 -- Post Failure:
485 -- If lookup is not valid or cannot be derived then an
486 -- application error is raised and processing is terminated
487 --
488 -- Access Status:
489 -- Internal Row Handler Use Only.
490 --
491 procedure chk_status
492 (p_disability_id in per_disabilities_f.disability_id%TYPE
493 ,p_status in per_disabilities_f.status%TYPE
494 ,p_effective_date in date
495 ,p_validation_start_date in date
496 ,p_validation_end_date in date
497 )
498 is
499 --
500 l_proc varchar2(72) := g_package||'chk_status';
501 l_exists varchar2(1);
502 l_api_updating boolean;
503 --
504 begin
505 hr_utility.set_location('Entering:'|| l_proc, 10);
506 --
507 -- Check mandatory parameters have been set
508 --
509 hr_api.mandatory_arg_error
510 (p_api_name => l_proc
511 ,p_argument => 'effective_date'
512 ,p_argument_value => p_effective_date
513 );
514 --
515 hr_api.mandatory_arg_error
516 (p_api_name => l_proc
517 ,p_argument => 'validation_start_date'
518 ,p_argument_value => p_validation_start_date
519 );
520 --
521 hr_api.mandatory_arg_error
522 (p_api_name => l_proc
523 ,p_argument => 'validation_end_date'
524 ,p_argument_value => p_validation_end_date
525 );
526 hr_utility.set_location(l_proc, 20);
527 --
528 -- Ensure mandatory status is set
529 if p_status is null then
530 hr_utility.set_message(800, 'HR_289016_DIS_STATUS_NULL');
531 hr_utility.raise_error;
535 -- 1) inserting
532 else
533 --
534 -- validate if:
536 -- 2) updating and value has changed
537 --
538 if ((p_disability_id is null) or
539 ((p_disability_id is not null) and
540 (per_dis_shd.g_old_rec.status <> p_status))) then
541 --
542 hr_utility.set_location(l_proc, 30);
543 --
544 if hr_api.not_exists_in_dt_leg_lookups
545 (p_effective_date => p_effective_date
546 ,p_validation_start_date => p_validation_start_date
547 ,p_validation_end_date => p_validation_end_date
548 ,p_lookup_type => 'DISABILITY_STATUS'
549 ,p_lookup_code => p_status
550 )
551 then
552 --
553 hr_utility.set_message(800, 'HR_52913_DIS_INV_STATUS');
554 hr_utility.raise_error;
555 end if;
556 end if;
557 end if;
558 --
559 hr_utility.set_location(' Leaving:'|| l_proc, 70);
560 --
561 end chk_status;
562 --
563 --
564 -- ---------------------------------------------------------------------------
565 -- |--------------------------< chk_quota_fte >------------------------------|
566 -- ---------------------------------------------------------------------------
567 --
568 -- Desciption :
569 --
570 -- Validate that mandatory quota_fte is set, and is within the range >= 0 < 100.
571 --
572 -- Pre-conditions :
573 --
574 --
575 -- In Arguments :
576 -- p_quota_fte
577 --
578 -- Post Success :
579 -- Processing continues
580 --
581 -- Post Failure :
582 -- An application error will be raised and processing is
583 -- terminated
584 --
585 -- Access Status :
586 -- Internal Table Handler Use only.
587 --
588 -- {End of Comments}
589 --
590 -- ---------------------------------------------------------------------------
591 procedure chk_quota_fte
592 (p_quota_fte in per_disabilities_f.quota_fte%TYPE) is
593 --
594 l_proc varchar2(72) := g_package||'chk_quota_fte';
595 --
596 begin
597 hr_utility.set_location('Entering:'|| l_proc, 10);
598 --
599 -- check not null
600 if p_quota_fte is null then
601 hr_utility.set_message(800, 'HR_52915_DIS_QUOTA_NULL');
602 hr_utility.raise_error;
603 elsif (p_quota_fte < 0 ) or (p_quota_fte >= 100 ) then
604 -- check not negative or 100 or over
605 hr_utility.set_message(800, 'HR_52916_DIS_INV_QUOTA');
606 hr_utility.raise_error;
607 end if;
608 --
609 hr_utility.set_location('Leaving:'|| l_proc, 20);
610 end chk_quota_fte;
611 --
612 -- ---------------------------------------------------------------------------
613 -- |-----------------------------< chk_reason >------------------------------|
614 -- ---------------------------------------------------------------------------
615 --
616 -- Desciption :
617 --
618 -- Validate that REASON value exists and is enabled in hr_leg_lookups view
619 -- for the type 'DISABILITY_REASON' on the validation date range.
620 --
621 --
622 -- Pre-conditions :
623 --
624 --
625 -- In Arguments :
626 -- p_disability_id
627 -- p_reason
628 -- p_effective_date
629 -- p_validation_start_date
630 -- p_validation_end_date
631 --
632 -- Post Success :
633 -- Processing continues
634 --
635 -- Post Failure :
636 -- An application error will be raised and processing is
637 -- terminated
638 --
639 -- Access Status :
640 -- Internal Table Handler Use only.
641 --
642 -- {End of Comments}
643 --
644 -- ---------------------------------------------------------------------------
645 procedure chk_reason
646 (p_disability_id in per_disabilities_f.disability_id%TYPE
647 ,p_reason in per_disabilities_f.reason%TYPE
648 ,p_effective_date in date
649 ,p_validation_start_date in date
650 ,p_validation_end_date in date
651 )
652 is
653 --
654 l_proc varchar2(72) := g_package||'chk_reason';
655 --
656 begin
657 hr_utility.set_location('Entering:'|| l_proc, 10);
658 --
659 -- Check mandatory arguments
660 --
661 hr_api.mandatory_arg_error
662 (p_api_name => l_proc
663 ,p_argument => 'effective_date'
664 ,p_argument_value => p_effective_date
665 );
666 --
667 hr_api.mandatory_arg_error
668 (p_api_name => l_proc
669 ,p_argument => 'validation_start_date'
670 ,p_argument_value => p_validation_start_date
671 );
672 --
673 hr_api.mandatory_arg_error
674 (p_api_name => l_proc
675 ,p_argument => 'validation_end_date'
676 ,p_argument_value => p_validation_end_date
677 );
678 hr_utility.set_location(l_proc, 20);
679 --
680 --
681 if p_reason is not null then
682 --
683 -- validate if:
684 -- 1) inserting
685 -- 2) updating and value has changed
686 --
687 if ((p_disability_id is null) or
688 ((p_disability_id is not null) and
689 (per_dis_shd.g_old_rec.reason <> p_reason))) then
690 --
691 hr_utility.set_location(l_proc, 30);
692 --
693 if hr_api.not_exists_in_dt_leg_lookups
694 (p_effective_date => p_effective_date
695 ,p_validation_start_date => p_validation_start_date
699 )
696 ,p_validation_end_date => p_validation_end_date
697 ,p_lookup_type => 'DISABILITY_REASON'
698 ,p_lookup_code => p_reason
700 then
701 --
702 hr_utility.set_message(800, 'HR_52917_DIS_INV_REASON');
703 hr_utility.raise_error;
704 end if;
705 end if;
706 end if;
707 --
708 hr_utility.set_location(' Leaving:'|| l_proc, 40);
709 --
710 end chk_reason;
711 --
712 -- ---------------------------------------------------------------------------
713 -- |-----------------------------< chk_degree >------------------------------|
714 -- ---------------------------------------------------------------------------
715 --
716 -- Desciption :
717 --
718 -- Validate that degree is greater than zero and not greater than one hundred.
719 --
720 -- Pre-conditions :
721 --
722 --
723 -- In Arguments :
724 -- p_degree
725 --
726 -- Post Success :
727 -- Processing continues
728 --
729 -- Post Failure :
730 -- An application error will be raised and processing is
731 -- terminated
732 --
733 -- Access Status :
734 -- Internal Table Handler Use only.
735 --
736 -- {End of Comments}
737 --
738 -- ---------------------------------------------------------------------------
739 procedure chk_degree
740 (p_degree in per_disabilities_f.degree%TYPE) is
741 --
742 l_proc varchar2(72) := g_package||'chk_degree';
743 --
744 begin
745 hr_utility.set_location('Entering:'|| l_proc, 10);
746 --
747 -- check within range
748 if p_degree is not null then
749 if (p_degree <= 0) or (p_degree > 100) then
750 hr_utility.set_message(800, 'HR_52918_DIS_INV_DEGREE');
751 hr_utility.raise_error;
752 end if;
753 end if;
754 --
755 hr_utility.set_location('Leaving:'|| l_proc, 20);
756 end chk_degree;
757 --
758 -- ---------------------------------------------------------------------------
759 -- |----------------------< chk_organization_id >----------------------------|
760 -- ---------------------------------------------------------------------------
761 --
762 -- Desciption :
763 --
764 -- Validate organization_id is in same business group as person, and is
765 -- external type. Ensure organization_id is defined under class of
766 -- disability_org.
767 --
768 -- Pre-conditions :
769 --
770 --
771 -- In Arguments :
772 -- p_disability_id
773 -- p_organization_id
774 -- p_business_group_id
775 -- p_validation_start_date
776 --
777 -- Post Success :
778 -- Processing continues
779 --
780 -- Post Failure :
781 -- An application error will be raised and processing is
782 -- terminated
783 --
784 -- Access Status :
785 -- Internal Table Handler Use only.
786 --
787 -- {End of Comments}
788 --
789 procedure chk_organization_id
790 (p_disability_id in per_disabilities_f.disability_id%TYPE
791 ,p_organization_id in per_disabilities_f.organization_id%TYPE
792 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
793 ,p_validation_start_date in per_disabilities_f.effective_start_date%TYPE
794 )
795 is
796 --
797 cursor csr_org is
798 select business_group_id
799 from hr_all_organization_units hou
800 where hou.organization_id = p_organization_id
801 and p_validation_start_date between hou.date_from and nvl(hou.date_to, hr_api.g_eot)
802 and hou.internal_external_flag = 'EXT';
803 --
804 cursor csr_org_inf is
805 select null
806 from hr_organization_information hoi
807 where hoi.organization_id = p_organization_id
808 and hoi.org_information_context = 'CLASS'
809 and hoi.org_information1 = 'DISABILITY_ORG'
810 and hoi.org_information2 = 'Y';
811 --
812 l_exists varchar2(1);
813 l_proc varchar2(72) := g_package||'chk_organization_id';
814 l_business_group_id per_assignments_f.business_group_id%TYPE;
815 --
816 begin
817 hr_utility.set_location('Entering:'|| l_proc, 10);
818 --
819 -- Check mandatory parameters have been set
820 --
821 hr_api.mandatory_arg_error
822 (p_api_name => l_proc
823 ,p_argument => 'business_group_id'
824 ,p_argument_value => p_business_group_id);
825 --
826 hr_api.mandatory_arg_error
827 (p_api_name => l_proc
828 ,p_argument => 'validation_start_date'
829 ,p_argument_value => p_validation_start_date);
830 --
831 if p_organization_id is not null then
832 --
833 hr_utility.set_location(l_proc, 20);
834 --
835 -- validate if:
836 -- 1) inserting
837 -- 2) updating and value has changed
838 --
839 if ((p_disability_id is null) or
840 ((p_disability_id is not null) and
841 (per_dis_shd.g_old_rec.organization_id <> p_organization_id))) then
842 --
843 hr_utility.set_location(l_proc, 30);
844 --
845 -- check org exists in hr_all_organization_units (fk) for the persons bg
846 -- within the validation date range.
847 open csr_org;
848 fetch csr_org into l_business_group_id;
849 if csr_org%notfound then
850 close csr_org;
851 -- error as org not found
855 if l_business_group_id <> p_business_group_id then
852 hr_utility.set_message(800, 'HR_52919_DIS_INV_ORG');
853 hr_utility.raise_error;
854 else
856 close csr_org;
857 -- error as org is in different business group to person
858 hr_utility.set_message(800, 'HR_52920_DIS_INV_ORG_BG');
859 hr_utility.raise_error;
860 end if;
861 end if;
862 close csr_org;
863 hr_utility.set_location(l_proc, 40);
864 --
865 -- check org exists in hr_organization_information for the relevant
866 -- organisation class.
867 open csr_org_inf;
868 fetch csr_org_inf into l_exists;
869 if csr_org_inf%notfound then
870 close csr_org_inf;
871 -- error as org is not in the correct class of disability_org
872 hr_utility.set_message(800, 'HR_52921_DIS_INV_ORG_CLASS');
873 hr_utility.raise_error;
874 end if;
875 close csr_org_inf;
876 --
877 end if;
878 end if;
879 --
880 hr_utility.set_location('Entering:'|| l_proc, 50);
881 --
882 end chk_organization_id;
883 --
884 -- ---------------------------------------------------------------------------
885 -- |-----------------------< chk_registration_details >----------------------|
886 -- ---------------------------------------------------------------------------
887 --
888 -- Desciption :
889 -- Validate registration_id, registration_date and registration_exp_date
890 --
891 -- Pre-conditions :
892 --
893 -- In Arguments :
894 -- p_registration_id
895 -- p_registration_date
896 -- p_registration_exp_date
897 -- p_organization_id
898 --
899 -- Post Success :
900 -- Processing continues
901 --
902 -- Post Failure :
903 -- An application error will be raised and processing is
904 -- terminated
905 --
906 -- Access Status :
907 -- Internal Table Handler Use only.
908 --
909 -- {End of Comments}
910 --
911 procedure chk_registration_details
912 (p_organization_id in per_disabilities_f.organization_id%TYPE
913 ,p_registration_id in per_disabilities_f.registration_id%TYPE
914 ,p_registration_date in per_disabilities_f.registration_date%TYPE
915 ,p_registration_exp_date in per_disabilities_f.registration_exp_date%TYPE
916 ) IS
917 --
918 l_proc varchar2(72) := g_package||'chk_registration_details';
919 --
920 begin
921 hr_utility.set_location('Entering:'|| l_proc, 10);
922 --
923 -- when inserting or updating
924 -- disallow registration details if org is null
925 if p_organization_id is null then
926 if p_registration_id is not null then
927 hr_utility.set_message(800, 'HR_52922_DIS_REG_ID_NULL');
928 hr_utility.raise_error;
929 elsif p_registration_date is not null then
930 hr_utility.set_message(800, 'HR_52923_DIS_REG_DATE_NULL');
931 hr_utility.raise_error;
932 elsif p_registration_exp_date is not null then
933 hr_utility.set_message(800, 'HR_52924_DIS_REG_EXP_DATE_NULL');
934 hr_utility.raise_error;
935 end if;
936 end if;
937 --
938 hr_utility.set_location(l_proc, 20);
939 --
940 -- ensure reg date is set if reg exp is set and
941 -- that it is before reg expiry date
942 if (p_registration_date is not null and p_registration_exp_date is not null) then
943 if (p_registration_date > p_registration_exp_date) then
944 hr_utility.set_message(800, 'HR_52914_DIS_INV_DATES');
945 hr_utility.raise_error;
946 end if;
947 elsif (p_registration_date is null and p_registration_exp_date is not null) then
948 hr_utility.set_message(800, 'HR_52926_DIS_REG_NOT_NULL');
949 hr_utility.raise_error;
950 end if;
951 --
952 hr_utility.set_location('Leaving:'|| l_proc, 30);
953 --
954 end chk_registration_details;
955 --
956 -- ----------------------------------------------------------------------------
957 -- |-----------------------------< chk_ddf >----------------------------------|
958 -- ----------------------------------------------------------------------------
959 --
960 -- Description:
961 -- Validates all the Developer Descriptive Flexfield values.
962 --
963 -- Prerequisites:
964 -- All other columns have been validated. Must be called as the
965 -- last step from insert_validate and update_validate.
966 --
967 -- In Arguments:
968 -- p_rec
969 --
970 -- Post Success:
971 -- If the Developer Descriptive Flexfield structure column and data values
972 -- are all valid this procedure will end normally and processing will
973 -- continue.
974 --
975 -- Post Failure:
976 -- If the Developer Descriptive Flexfield structure column value or any of
977 -- the data values are invalid then an application error is raised as
978 -- a PL/SQL exception.
979 --
980 -- Access Status:
981 -- Internal Row Handler Use Only.
982 --
983 -- ----------------------------------------------------------------------------
984 procedure chk_ddf
985 (p_rec in per_dis_shd.g_rec_type
986 ) is
987 --
988 l_proc varchar2(72) := g_package || 'chk_ddf';
989 --
990 begin
991 hr_utility.set_location('Entering:'||l_proc,10);
992 --
993 if ((p_rec.disability_id is not null) and (
994 nvl(per_dis_shd.g_old_rec.dis_information_category, hr_api.g_varchar2) <>
995 nvl(p_rec.dis_information_category, hr_api.g_varchar2) or
999 nvl(p_rec.dis_information2, hr_api.g_varchar2) or
996 nvl(per_dis_shd.g_old_rec.dis_information1, hr_api.g_varchar2) <>
997 nvl(p_rec.dis_information1, hr_api.g_varchar2) or
998 nvl(per_dis_shd.g_old_rec.dis_information2, hr_api.g_varchar2) <>
1000 nvl(per_dis_shd.g_old_rec.dis_information3, hr_api.g_varchar2) <>
1001 nvl(p_rec.dis_information3, hr_api.g_varchar2) or
1002 nvl(per_dis_shd.g_old_rec.dis_information4, hr_api.g_varchar2) <>
1003 nvl(p_rec.dis_information4, hr_api.g_varchar2) or
1004 nvl(per_dis_shd.g_old_rec.dis_information5, hr_api.g_varchar2) <>
1005 nvl(p_rec.dis_information5, hr_api.g_varchar2) or
1006 nvl(per_dis_shd.g_old_rec.dis_information6, hr_api.g_varchar2) <>
1007 nvl(p_rec.dis_information6, hr_api.g_varchar2) or
1008 nvl(per_dis_shd.g_old_rec.dis_information7, hr_api.g_varchar2) <>
1009 nvl(p_rec.dis_information7, hr_api.g_varchar2) or
1010 nvl(per_dis_shd.g_old_rec.dis_information8, hr_api.g_varchar2) <>
1011 nvl(p_rec.dis_information8, hr_api.g_varchar2) or
1012 nvl(per_dis_shd.g_old_rec.dis_information9, hr_api.g_varchar2) <>
1013 nvl(p_rec.dis_information9, hr_api.g_varchar2) or
1014 nvl(per_dis_shd.g_old_rec.dis_information10, hr_api.g_varchar2) <>
1015 nvl(p_rec.dis_information10, hr_api.g_varchar2) or
1016 nvl(per_dis_shd.g_old_rec.dis_information11, hr_api.g_varchar2) <>
1017 nvl(p_rec.dis_information11, hr_api.g_varchar2) or
1018 nvl(per_dis_shd.g_old_rec.dis_information12, hr_api.g_varchar2) <>
1019 nvl(p_rec.dis_information12, hr_api.g_varchar2) or
1020 nvl(per_dis_shd.g_old_rec.dis_information13, hr_api.g_varchar2) <>
1021 nvl(p_rec.dis_information13, hr_api.g_varchar2) or
1022 nvl(per_dis_shd.g_old_rec.dis_information14, hr_api.g_varchar2) <>
1023 nvl(p_rec.dis_information14, hr_api.g_varchar2) or
1024 nvl(per_dis_shd.g_old_rec.dis_information15, hr_api.g_varchar2) <>
1025 nvl(p_rec.dis_information15, hr_api.g_varchar2) or
1026 nvl(per_dis_shd.g_old_rec.dis_information16, hr_api.g_varchar2) <>
1027 nvl(p_rec.dis_information16, hr_api.g_varchar2) or
1028 nvl(per_dis_shd.g_old_rec.dis_information17, hr_api.g_varchar2) <>
1029 nvl(p_rec.dis_information17, hr_api.g_varchar2) or
1030 nvl(per_dis_shd.g_old_rec.dis_information18, hr_api.g_varchar2) <>
1031 nvl(p_rec.dis_information18, hr_api.g_varchar2) or
1032 nvl(per_dis_shd.g_old_rec.dis_information19, hr_api.g_varchar2) <>
1033 nvl(p_rec.dis_information19, hr_api.g_varchar2) or
1034 nvl(per_dis_shd.g_old_rec.dis_information20, hr_api.g_varchar2) <>
1035 nvl(p_rec.dis_information20, hr_api.g_varchar2) or
1036 nvl(per_dis_shd.g_old_rec.dis_information21, hr_api.g_varchar2) <>
1037 nvl(p_rec.dis_information21, hr_api.g_varchar2) or
1038 nvl(per_dis_shd.g_old_rec.dis_information22, hr_api.g_varchar2) <>
1039 nvl(p_rec.dis_information22, hr_api.g_varchar2) or
1040 nvl(per_dis_shd.g_old_rec.dis_information23, hr_api.g_varchar2) <>
1041 nvl(p_rec.dis_information23, hr_api.g_varchar2) or
1042 nvl(per_dis_shd.g_old_rec.dis_information24, hr_api.g_varchar2) <>
1043 nvl(p_rec.dis_information24, hr_api.g_varchar2) or
1044 nvl(per_dis_shd.g_old_rec.dis_information25, hr_api.g_varchar2) <>
1045 nvl(p_rec.dis_information25, hr_api.g_varchar2) or
1046 nvl(per_dis_shd.g_old_rec.dis_information26, hr_api.g_varchar2) <>
1047 nvl(p_rec.dis_information26, hr_api.g_varchar2) or
1048 nvl(per_dis_shd.g_old_rec.dis_information27, hr_api.g_varchar2) <>
1049 nvl(p_rec.dis_information27, hr_api.g_varchar2) or
1050 nvl(per_dis_shd.g_old_rec.dis_information28, hr_api.g_varchar2) <>
1051 nvl(p_rec.dis_information28, hr_api.g_varchar2) or
1052 nvl(per_dis_shd.g_old_rec.dis_information29, hr_api.g_varchar2) <>
1053 nvl(p_rec.dis_information29, hr_api.g_varchar2) or
1054 nvl(per_dis_shd.g_old_rec.dis_information30, hr_api.g_varchar2) <>
1055 nvl(p_rec.dis_information30, hr_api.g_varchar2) ))
1056 or (p_rec.disability_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 => 'Disability Developer DF'
1066 ,p_attribute_category => p_rec.DIS_INFORMATION_CATEGORY
1067 ,p_attribute1_name => 'DIS_INFORMATION1'
1068 ,p_attribute1_value => p_rec.dis_information1
1069 ,p_attribute2_name => 'DIS_INFORMATION2'
1070 ,p_attribute2_value => p_rec.dis_information2
1071 ,p_attribute3_name => 'DIS_INFORMATION3'
1072 ,p_attribute3_value => p_rec.dis_information3
1073 ,p_attribute4_name => 'DIS_INFORMATION4'
1074 ,p_attribute4_value => p_rec.dis_information4
1075 ,p_attribute5_name => 'DIS_INFORMATION5'
1076 ,p_attribute5_value => p_rec.dis_information5
1077 ,p_attribute6_name => 'DIS_INFORMATION6'
1078 ,p_attribute6_value => p_rec.dis_information6
1079 ,p_attribute7_name => 'DIS_INFORMATION7'
1080 ,p_attribute7_value => p_rec.dis_information7
1081 ,p_attribute8_name => 'DIS_INFORMATION8'
1082 ,p_attribute8_value => p_rec.dis_information8
1083 ,p_attribute9_name => 'DIS_INFORMATION9'
1084 ,p_attribute9_value => p_rec.dis_information9
1085 ,p_attribute10_name => 'DIS_INFORMATION10'
1086 ,p_attribute10_value => p_rec.dis_information10
1090 ,p_attribute12_value => p_rec.dis_information12
1087 ,p_attribute11_name => 'DIS_INFORMATION11'
1088 ,p_attribute11_value => p_rec.dis_information11
1089 ,p_attribute12_name => 'DIS_INFORMATION12'
1091 ,p_attribute13_name => 'DIS_INFORMATION13'
1092 ,p_attribute13_value => p_rec.dis_information13
1093 ,p_attribute14_name => 'DIS_INFORMATION14'
1094 ,p_attribute14_value => p_rec.dis_information14
1095 ,p_attribute15_name => 'DIS_INFORMATION15'
1096 ,p_attribute15_value => p_rec.dis_information15
1097 ,p_attribute16_name => 'DIS_INFORMATION16'
1098 ,p_attribute16_value => p_rec.dis_information16
1099 ,p_attribute17_name => 'DIS_INFORMATION17'
1100 ,p_attribute17_value => p_rec.dis_information17
1101 ,p_attribute18_name => 'DIS_INFORMATION18'
1102 ,p_attribute18_value => p_rec.dis_information18
1103 ,p_attribute19_name => 'DIS_INFORMATION19'
1104 ,p_attribute19_value => p_rec.dis_information19
1105 ,p_attribute20_name => 'DIS_INFORMATION20'
1106 ,p_attribute20_value => p_rec.dis_information20
1107 ,p_attribute21_name => 'DIS_INFORMATION21'
1108 ,p_attribute21_value => p_rec.dis_information21
1109 ,p_attribute22_name => 'DIS_INFORMATION22'
1110 ,p_attribute22_value => p_rec.dis_information22
1111 ,p_attribute23_name => 'DIS_INFORMATION23'
1112 ,p_attribute23_value => p_rec.dis_information23
1113 ,p_attribute24_name => 'DIS_INFORMATION24'
1114 ,p_attribute24_value => p_rec.dis_information24
1115 ,p_attribute25_name => 'DIS_INFORMATION25'
1116 ,p_attribute25_value => p_rec.dis_information25
1117 ,p_attribute26_name => 'DIS_INFORMATION26'
1118 ,p_attribute26_value => p_rec.dis_information26
1119 ,p_attribute27_name => 'DIS_INFORMATION27'
1120 ,p_attribute27_value => p_rec.dis_information27
1121 ,p_attribute28_name => 'DIS_INFORMATION28'
1122 ,p_attribute28_value => p_rec.dis_information28
1123 ,p_attribute29_name => 'DIS_INFORMATION29'
1124 ,p_attribute29_value => p_rec.dis_information29
1125 ,p_attribute30_name => 'DIS_INFORMATION30'
1126 ,p_attribute30_value => p_rec.dis_information30
1127 );
1128 end if;
1129 --
1130 hr_utility.set_location(' Leaving:'||l_proc,20);
1131 end chk_ddf;
1132 --
1133 -- ----------------------------------------------------------------------------
1134 -- |------------------------------< chk_df >----------------------------------|
1135 -- ----------------------------------------------------------------------------
1136 --
1137 -- Description:
1138 -- Validates all the Descriptive Flexfield values.
1139 --
1140 -- Prerequisites:
1141 -- All other columns have been validated. Must be called as the
1142 -- last step from insert_validate and update_validate.
1143 --
1144 -- In Arguments:
1145 -- p_rec
1146 --
1147 -- Post Success:
1148 -- If the Descriptive Flexfield structure column and data values are
1149 -- all valid this procedure will end normally and processing will
1150 -- continue.
1151 --
1152 -- Post Failure:
1153 -- If the Descriptive Flexfield structure column value or any of
1154 -- the data values are invalid then an application error is raised as
1155 -- a PL/SQL exception.
1156 --
1157 -- Access Status:
1158 -- Internal Row Handler Use Only.
1159 --
1160 -- ----------------------------------------------------------------------------
1161 procedure chk_df
1162 (p_rec in per_dis_shd.g_rec_type
1163 ) is
1164 --
1165 l_proc varchar2(72) := g_package || 'chk_df';
1166 --
1167 begin
1168 hr_utility.set_location('Entering:'||l_proc,10);
1169 --
1170 if ((p_rec.disability_id is not null) and (
1171 nvl(per_dis_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1172 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1173 nvl(per_dis_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1174 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1175 nvl(per_dis_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1176 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1177 nvl(per_dis_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1178 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1179 nvl(per_dis_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1180 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1181 nvl(per_dis_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1182 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1183 nvl(per_dis_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1184 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1185 nvl(per_dis_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1186 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1187 nvl(per_dis_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1188 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1189 nvl(per_dis_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1190 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1191 nvl(per_dis_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1192 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1193 nvl(per_dis_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1194 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1198 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1195 nvl(per_dis_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1196 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1197 nvl(per_dis_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1199 nvl(per_dis_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1200 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1201 nvl(per_dis_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1202 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1203 nvl(per_dis_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1204 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1205 nvl(per_dis_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1206 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1207 nvl(per_dis_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1208 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1209 nvl(per_dis_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1210 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1211 nvl(per_dis_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1212 nvl(p_rec.attribute20, hr_api.g_varchar2) or
1213 nvl(per_dis_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
1214 nvl(p_rec.attribute21, hr_api.g_varchar2) or
1215 nvl(per_dis_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
1216 nvl(p_rec.attribute22, hr_api.g_varchar2) or
1217 nvl(per_dis_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
1218 nvl(p_rec.attribute23, hr_api.g_varchar2) or
1219 nvl(per_dis_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
1220 nvl(p_rec.attribute24, hr_api.g_varchar2) or
1221 nvl(per_dis_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
1222 nvl(p_rec.attribute25, hr_api.g_varchar2) or
1223 nvl(per_dis_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
1224 nvl(p_rec.attribute26, hr_api.g_varchar2) or
1225 nvl(per_dis_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
1226 nvl(p_rec.attribute27, hr_api.g_varchar2) or
1227 nvl(per_dis_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
1228 nvl(p_rec.attribute28, hr_api.g_varchar2) or
1229 nvl(per_dis_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
1230 nvl(p_rec.attribute29, hr_api.g_varchar2) or
1231 nvl(per_dis_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
1232 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
1233 or (p_rec.disability_id is null) then
1234 --
1235 -- Only execute the validation if absolutely necessary:
1236 -- a) During update, the structure column value or any
1237 -- of the attribute values have actually changed.
1238 -- b) During insert.
1239 --
1240 hr_dflex_utility.ins_or_upd_descflex_attribs
1241 (p_appl_short_name => 'PER'
1242 ,p_descflex_name => 'PER_DISABILITIES'
1243 ,p_attribute_category => p_rec.ATTRIBUTE_CATEGORY
1244 ,p_attribute1_name => 'ATTRIBUTE1'
1245 ,p_attribute1_value => p_rec.attribute1
1246 ,p_attribute2_name => 'ATTRIBUTE2'
1247 ,p_attribute2_value => p_rec.attribute2
1248 ,p_attribute3_name => 'ATTRIBUTE3'
1249 ,p_attribute3_value => p_rec.attribute3
1250 ,p_attribute4_name => 'ATTRIBUTE4'
1251 ,p_attribute4_value => p_rec.attribute4
1252 ,p_attribute5_name => 'ATTRIBUTE5'
1253 ,p_attribute5_value => p_rec.attribute5
1254 ,p_attribute6_name => 'ATTRIBUTE6'
1255 ,p_attribute6_value => p_rec.attribute6
1256 ,p_attribute7_name => 'ATTRIBUTE7'
1257 ,p_attribute7_value => p_rec.attribute7
1258 ,p_attribute8_name => 'ATTRIBUTE8'
1259 ,p_attribute8_value => p_rec.attribute8
1260 ,p_attribute9_name => 'ATTRIBUTE9'
1261 ,p_attribute9_value => p_rec.attribute9
1262 ,p_attribute10_name => 'ATTRIBUTE10'
1263 ,p_attribute10_value => p_rec.attribute10
1264 ,p_attribute11_name => 'ATTRIBUTE11'
1265 ,p_attribute11_value => p_rec.attribute11
1266 ,p_attribute12_name => 'ATTRIBUTE12'
1267 ,p_attribute12_value => p_rec.attribute12
1268 ,p_attribute13_name => 'ATTRIBUTE13'
1269 ,p_attribute13_value => p_rec.attribute13
1270 ,p_attribute14_name => 'ATTRIBUTE14'
1271 ,p_attribute14_value => p_rec.attribute14
1272 ,p_attribute15_name => 'ATTRIBUTE15'
1273 ,p_attribute15_value => p_rec.attribute15
1274 ,p_attribute16_name => 'ATTRIBUTE16'
1275 ,p_attribute16_value => p_rec.attribute16
1276 ,p_attribute17_name => 'ATTRIBUTE17'
1277 ,p_attribute17_value => p_rec.attribute17
1278 ,p_attribute18_name => 'ATTRIBUTE18'
1279 ,p_attribute18_value => p_rec.attribute18
1280 ,p_attribute19_name => 'ATTRIBUTE19'
1281 ,p_attribute19_value => p_rec.attribute19
1282 ,p_attribute20_name => 'ATTRIBUTE20'
1283 ,p_attribute20_value => p_rec.attribute20
1284 ,p_attribute21_name => 'ATTRIBUTE21'
1285 ,p_attribute21_value => p_rec.attribute21
1286 ,p_attribute22_name => 'ATTRIBUTE22'
1287 ,p_attribute22_value => p_rec.attribute22
1288 ,p_attribute23_name => 'ATTRIBUTE23'
1289 ,p_attribute23_value => p_rec.attribute23
1290 ,p_attribute24_name => 'ATTRIBUTE24'
1294 ,p_attribute26_name => 'ATTRIBUTE26'
1291 ,p_attribute24_value => p_rec.attribute24
1292 ,p_attribute25_name => 'ATTRIBUTE25'
1293 ,p_attribute25_value => p_rec.attribute25
1295 ,p_attribute26_value => p_rec.attribute26
1296 ,p_attribute27_name => 'ATTRIBUTE27'
1297 ,p_attribute27_value => p_rec.attribute27
1298 ,p_attribute28_name => 'ATTRIBUTE28'
1299 ,p_attribute28_value => p_rec.attribute28
1300 ,p_attribute29_name => 'ATTRIBUTE29'
1301 ,p_attribute29_value => p_rec.attribute29
1302 ,p_attribute30_name => 'ATTRIBUTE30'
1303 ,p_attribute30_value => p_rec.attribute30
1304 );
1305 end if;
1306 --
1307 hr_utility.set_location(' Leaving:'||l_proc,20);
1308 end chk_df;
1309 --
1310 -- ----------------------------------------------------------------------------
1311 -- |-----------------------< chk_non_updateable_args >------------------------|
1312 -- ----------------------------------------------------------------------------
1313 -- {Start Of Comments}
1314 --
1315 -- Description:
1316 -- This procedure is used to ensure that non updateable attributes have
1317 -- not been updated. If an attribute has been updated an error is generated.
1318 --
1319 -- Pre Conditions:
1320 -- g_old_rec has been populated with details of the values currently in
1321 -- the database.
1322 --
1323 -- In Arguments:
1324 -- p_rec has been populated with the updated values the user would like the
1325 -- record set to.
1326 --
1327 -- Post Success:
1328 -- Processing continues if all the non updateable attributes have not
1329 -- changed.
1330 --
1331 -- Post Failure:
1332 -- An application error is raised if any of the non updatable attributes
1333 -- have been altered.
1334 --
1335 -- {End Of Comments}
1336 -- ----------------------------------------------------------------------------
1337 Procedure chk_non_updateable_args
1338 (p_effective_date in date
1339 ,p_rec in per_dis_shd.g_rec_type
1340 ) is
1341 --
1342 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
1343 l_error EXCEPTION;
1344 l_argument varchar2(30);
1345 --
1346 Begin
1347 --
1348 -- Only proceed with the validation if a row exists for the current
1349 -- record in the HR Schema.
1350 --
1351 if NOT per_dis_shd.api_updating
1352 (p_disability_id => p_rec.disability_id
1353 ,p_effective_date => p_effective_date
1354 ,p_object_version_number => p_rec.object_version_number
1355 ) THEN
1356 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
1357 fnd_message.set_token('PROCEDURE ', l_proc);
1358 fnd_message.set_token('STEP ', '5');
1359 fnd_message.raise_error;
1360 end if;
1361 --
1362 if nvl(p_rec.person_id, hr_api.g_number) <>
1363 nvl(per_dis_shd.g_old_rec.person_id
1364 ,hr_api.g_number) then
1365 l_argument := 'person_id';
1366 raise l_error;
1367 end if;
1368 --
1369 EXCEPTION
1370 WHEN l_error THEN
1371 hr_api.argument_changed_error
1372 (p_api_name => l_proc
1373 ,p_argument => l_argument);
1374 WHEN OTHERS THEN
1375 RAISE;
1376 End chk_non_updateable_args;
1377 --
1378 -- ----------------------------------------------------------------------------
1379 -- |--------------------------< dt_update_validate >--------------------------|
1380 -- ----------------------------------------------------------------------------
1381 -- {Start Of Comments}
1382 --
1383 -- Description:
1384 -- This procedure is used for referential integrity of datetracked
1385 -- parent entities when a datetrack update operation is taking place
1386 -- and where there is no cascading of update defined for this entity.
1387 --
1388 -- Prerequisites:
1389 -- This procedure is called from the update_validate.
1390 --
1391 -- In Parameters:
1392 --
1393 -- Post Success:
1394 -- Processing continues.
1395 --
1396 -- Post Failure:
1397 --
1398 -- Developer Implementation Notes:
1399 -- This procedure should not need maintenance unless the HR Schema model
1400 -- changes.
1401 --
1402 -- Access Status:
1403 -- Internal Row Handler Use Only.
1404 --
1405 -- {End Of Comments}
1406 -- ----------------------------------------------------------------------------
1407 Procedure dt_update_validate
1408 (p_person_id in number default hr_api.g_number
1409 ,p_datetrack_mode in varchar2
1410 ,p_validation_start_date in date
1411 ,p_validation_end_date in date
1412 ) Is
1413 --
1414 l_proc varchar2(72) := g_package||'dt_update_validate';
1415 l_integrity_error Exception;
1416 l_table_name all_tables.table_name%TYPE;
1417 --
1418 Begin
1419 --
1420 -- Ensure that the p_datetrack_mode argument is not null
1421 --
1422 hr_api.mandatory_arg_error
1423 (p_api_name => l_proc
1424 ,p_argument => 'datetrack_mode'
1425 ,p_argument_value => p_datetrack_mode
1426 );
1427 --
1428 -- Mode will be valid, as this is checked at the start of the upd.
1429 --
1430 -- Ensure the arguments are not null
1431 --
1432 hr_api.mandatory_arg_error
1433 (p_api_name => l_proc
1434 ,p_argument => 'validation_start_date'
1435 ,p_argument_value => p_validation_start_date
1436 );
1437 --
1441 ,p_argument_value => p_validation_end_date
1438 hr_api.mandatory_arg_error
1439 (p_api_name => l_proc
1440 ,p_argument => 'validation_end_date'
1442 );
1443 --
1444 If ((nvl(p_person_id, hr_api.g_number) <> hr_api.g_number) and
1445 NOT (dt_api.check_min_max_dates
1446 (p_base_table_name => 'per_all_people_f'
1447 ,p_base_key_column => 'PERSON_ID'
1448 ,p_base_key_value => p_person_id
1449 ,p_from_date => p_validation_start_date
1450 ,p_to_date => p_validation_end_date))) Then
1451 l_table_name := 'all people';
1452 raise l_integrity_error;
1453 End If;
1454 --
1455 Exception
1456 When l_integrity_error Then
1457 --
1458 -- A referential integrity check was violated therefore
1459 -- we must error
1460 --
1461 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
1462 fnd_message.set_token('TABLE_NAME', l_table_name);
1463 fnd_message.raise_error;
1464 When Others Then
1465 --
1466 -- An unhandled or unexpected error has occurred which
1467 -- we must report
1468 --
1469 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1470 fnd_message.set_token('PROCEDURE', l_proc);
1471 fnd_message.set_token('STEP','15');
1472 fnd_message.raise_error;
1473 End dt_update_validate;
1474 --
1475 -- ----------------------------------------------------------------------------
1476 -- |--------------------------< dt_delete_validate >--------------------------|
1477 -- ----------------------------------------------------------------------------
1478 -- {Start Of Comments}
1479 --
1480 -- Description:
1481 -- This procedure is used for referential integrity of datetracked
1485 -- datetracked child rows exist between the validation start and end
1482 -- child entities when either a datetrack DELETE or ZAP is in operation
1483 -- and where there is no cascading of delete defined for this entity.
1484 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1486 -- dates.
1487 --
1488 -- Prerequisites:
1489 -- This procedure is called from the delete_validate.
1490 --
1491 -- In Parameters:
1492 --
1493 -- Post Success:
1494 -- Processing continues.
1495 --
1496 -- Post Failure:
1497 -- If a row exists by determining the returning Boolean value from the
1498 -- generic dt_api.rows_exist function then we must supply an error via
1499 -- the use of the local exception handler l_rows_exist.
1500 --
1501 -- Developer Implementation Notes:
1502 -- This procedure should not need maintenance unless the HR Schema model
1503 -- changes.
1504 --
1505 -- Access Status:
1506 -- Internal Row Handler Use Only.
1507 --
1508 -- {End Of Comments}
1509 -- ----------------------------------------------------------------------------
1510 Procedure dt_delete_validate
1511 (p_disability_id in number
1512 ,p_datetrack_mode in varchar2
1513 ,p_validation_start_date in date
1514 ,p_validation_end_date in date
1515 ) Is
1516 --
1517 l_proc varchar2(72) := g_package||'dt_delete_validate';
1518 l_rows_exist Exception;
1519 l_table_name all_tables.table_name%TYPE;
1520 --
1521 Begin
1522 --
1523 -- Ensure that the p_datetrack_mode argument is not null
1524 --
1525 hr_api.mandatory_arg_error
1526 (p_api_name => l_proc
1527 ,p_argument => 'datetrack_mode'
1528 ,p_argument_value => p_datetrack_mode
1529 );
1530 --
1531 -- Only perform the validation if the datetrack mode is either
1532 -- DELETE or ZAP
1533 --
1534 If (p_datetrack_mode = hr_api.g_delete or
1535 p_datetrack_mode = hr_api.g_zap) then
1536 --
1537 --
1538 -- Ensure the arguments are not null
1539 --
1540 hr_api.mandatory_arg_error
1541 (p_api_name => l_proc
1542 ,p_argument => 'validation_start_date'
1543 ,p_argument_value => p_validation_start_date
1544 );
1545 --
1546 hr_api.mandatory_arg_error
1547 (p_api_name => l_proc
1548 ,p_argument => 'validation_end_date'
1549 ,p_argument_value => p_validation_end_date
1550 );
1551 --
1552 hr_api.mandatory_arg_error
1553 (p_api_name => l_proc
1554 ,p_argument => 'disability_id'
1555 ,p_argument_value => p_disability_id
1556 );
1557 --
1558 --
1559 --
1560 End If;
1561 --
1562 Exception
1563 When l_rows_exist Then
1564 --
1568 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
1565 -- A referential integrity check was violated therefore
1566 -- we must error
1567 --
1569 fnd_message.set_token('TABLE_NAME', l_table_name);
1570 fnd_message.raise_error;
1571 When Others Then
1572 --
1573 -- An unhandled or unexpected error has occurred which
1574 -- we must report
1575 --
1576 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1577 fnd_message.set_token('PROCEDURE', l_proc);
1578 fnd_message.set_token('STEP','15');
1579 fnd_message.raise_error;
1580 --
1581 End dt_delete_validate;
1582 --
1583 -- ----------------------------------------------------------------------------
1584 -- |---------------------------< insert_validate >----------------------------|
1585 -- ----------------------------------------------------------------------------
1586 Procedure insert_validate
1587 (p_rec in per_dis_shd.g_rec_type
1588 ,p_effective_date in date
1589 ,p_datetrack_mode in varchar2
1590 ,p_validation_start_date in date
1591 ,p_validation_end_date in date
1592 ) is
1593 --
1594 cursor csr_person is
1595 select business_group_id
1596 from per_all_people_f pap
1597 where pap.person_id = p_rec.person_id
1598 and p_validation_start_date between pap.effective_start_date and pap.effective_end_date;
1599 --
1600 l_proc varchar2(72) := g_package||'insert_validate';
1601 l_business_group_id per_all_people_f.business_group_id%TYPE;
1602 --
1603 --
1604 Begin
1605 hr_utility.set_location('Entering:'||l_proc, 5);
1606 --
1607 -- Call all supporting business operations
1608 --
1609 per_per_bus.set_security_group_id(p_person_id => p_rec.person_id);
1610 --
1611 -- Validate PERSON_ID
1612 --
1613 per_dis_bus.chk_person_id
1614 (p_person_id => p_rec.person_id
1615 ,p_effective_date => p_effective_date
1616 );
1617
1618 --
1619 -- Validate CATEGORY
1620 --
1621 per_dis_bus.chk_category
1622 (p_disability_id => p_rec.disability_id
1623 ,p_category => p_rec.category
1624 ,p_effective_date => p_effective_date
1625 ,p_validation_start_date => p_validation_start_date
1626 ,p_validation_end_date => p_validation_end_date
1627 );
1628
1629 --
1630 -- Validate STATUS
1631 --
1632 per_dis_bus.chk_status
1633 (p_disability_id => p_rec.disability_id
1634 ,p_status => p_rec.status
1635 ,p_effective_date => p_effective_date
1636 ,p_validation_start_date => p_validation_start_date
1637 ,p_validation_end_date => p_validation_end_date
1638 );
1639 --
1640 -- Validate QUOTA_FTE
1641 --
1642 per_dis_bus.chk_quota_fte
1643 (p_quota_fte => p_rec.quota_fte);
1644 --
1645 -- Validate REASON
1646 --
1647 per_dis_bus.chk_reason
1648 (p_disability_id => p_rec.disability_id
1649 ,p_reason => p_rec.reason
1650 ,p_effective_date => p_effective_date
1651 ,p_validation_start_date => p_validation_start_date
1652 ,p_validation_end_date => p_validation_end_date
1653 );
1654 --
1655 -- Validate DEGREE
1656 --
1657 per_dis_bus.chk_degree
1658 (p_degree => p_rec.degree);
1659 --
1660 --
1661 -- Validate INCIDENT_ID
1662 --
1663 per_dis_bus.chk_incident_id
1664 (p_disability_id => p_rec.disability_id
1665 ,p_person_id => p_rec.person_id
1666 ,p_effective_date => p_effective_date
1667 ,p_incident_id => p_rec.incident_id
1668 );
1669 --
1670 -- Validate ORGANIZATION_ID
1671 --
1672 open csr_person;
1673 fetch csr_person into l_business_group_id;
1674 close csr_person;
1675 per_dis_bus.chk_organization_id
1676 (p_disability_id => p_rec.disability_id
1677 ,p_organization_id => p_rec.organization_id
1678 ,p_business_group_id => l_business_group_id
1679 ,p_validation_start_date => p_validation_start_date
1680 );
1681 --
1682 -- Validate REGISTRATION details
1683 --
1684 per_dis_bus.chk_registration_details
1685 (p_organization_id => p_rec.organization_id
1689 );
1686 ,p_registration_id => p_rec.registration_id
1687 ,p_registration_date => p_rec.registration_date
1688 ,p_registration_exp_date => p_rec.registration_exp_date
1690 --
1691 per_dis_bus.chk_ddf(p_rec);
1692 --
1693 per_dis_bus.chk_df(p_rec);
1694 --
1695 hr_utility.set_location(' Leaving:'||l_proc, 10);
1696 End insert_validate;
1697 --
1698 -- ----------------------------------------------------------------------------
1699 -- |---------------------------< update_validate >----------------------------|
1700 -- ----------------------------------------------------------------------------
1701 Procedure update_validate
1702 (p_rec in per_dis_shd.g_rec_type
1703 ,p_effective_date in date
1704 ,p_datetrack_mode in varchar2
1705 ,p_validation_start_date in date
1706 ,p_validation_end_date in date
1707 ) is
1708 --
1709 cursor csr_person is
1710 select business_group_id
1711 from per_all_people_f pap
1712 where pap.person_id = p_rec.person_id
1713 and p_validation_start_date between pap.effective_start_date and pap.effective_end_date;
1714 --
1715 l_business_group_id per_all_people_f.business_group_id%TYPE;
1716 l_proc varchar2(72) := g_package||'update_validate';
1717 --
1718 Begin
1719 hr_utility.set_location('Entering:'||l_proc, 5);
1720 --
1721 -- Call all supporting business operations
1722 --
1723 per_per_bus.set_security_group_id(p_person_id => p_rec.person_id);
1724 --
1725 chk_non_updateable_args
1726 (p_effective_date => p_effective_date
1727 ,p_rec => p_rec
1728 );
1729 --
1730 -- Validate PERSON ID
1731 --
1732 per_dis_bus.chk_person_id
1733 (p_person_id => p_rec.person_id
1734 ,p_effective_date => p_effective_date
1735 );
1736 --
1737 -- Validate CATEGORY
1738 --
1739 per_dis_bus.chk_category
1740 (p_disability_id => p_rec.disability_id
1741 ,p_category => p_rec.category
1742 ,p_effective_date => p_effective_date
1743 ,p_validation_start_date => p_validation_start_date
1744 ,p_validation_end_date => p_validation_end_date
1745 );
1746 --
1747 -- Validate STATUS
1748 --
1749 per_dis_bus.chk_status
1750 (p_disability_id => p_rec.disability_id
1751 ,p_status => p_rec.status
1752 ,p_effective_date => p_effective_date
1753 ,p_validation_start_date => p_validation_start_date
1754 ,p_validation_end_date => p_validation_end_date
1755 );
1756 --
1757 -- Validate QUOTA_FTE
1758 --
1759 per_dis_bus.chk_quota_fte
1760 (p_quota_fte => p_rec.quota_fte);
1761 --
1762 -- Validate REASON
1763 --
1764 per_dis_bus.chk_reason
1765 (p_disability_id => p_rec.disability_id
1766 ,p_reason => p_rec.reason
1767 ,p_effective_date => p_effective_date
1768 ,p_validation_start_date => p_validation_start_date
1769 ,p_validation_end_date => p_validation_end_date
1770 );
1771 --
1772 -- Validate DEGREE
1773 --
1774 per_dis_bus.chk_degree
1775 (p_degree => p_rec.degree);
1776 --
1777 -- Validate INCIDENT_ID
1778 --
1779 per_dis_bus.chk_incident_id
1780 (p_disability_id => p_rec.disability_id
1781 ,p_person_id => p_rec.person_id
1782 ,p_effective_date => p_effective_date
1783 ,p_incident_id => p_rec.incident_id
1784 );
1785 --
1786 -- Validate ORGANIZATION_ID
1787 --
1788 open csr_person;
1789 fetch csr_person into l_business_group_id;
1790 close csr_person;
1791 per_dis_bus.chk_organization_id
1792 (p_disability_id => p_rec.disability_id
1793 ,p_organization_id => p_rec.organization_id
1794 ,p_business_group_id => l_business_group_id
1795 ,p_validation_start_date => p_validation_start_date
1796 );
1797 --
1798 -- Validate REGISTRATION details
1799 --
1800 per_dis_bus.chk_registration_details
1801 (p_organization_id => p_rec.organization_id
1802 ,p_registration_id => p_rec.registration_id
1803 ,p_registration_date => p_rec.registration_date
1804 ,p_registration_exp_date => p_rec.registration_exp_date
1805 );
1806 --
1807 -- Call the datetrack update integrity operation
1808 --
1809 dt_update_validate
1810 (p_person_id => p_rec.person_id
1811 ,p_datetrack_mode => p_datetrack_mode
1812 ,p_validation_start_date => p_validation_start_date
1813 ,p_validation_end_date => p_validation_end_date
1814 );
1815 --
1816 per_dis_bus.chk_ddf(p_rec);
1817 --
1818 per_dis_bus.chk_df(p_rec);
1819 --
1820 hr_utility.set_location(' Leaving:'||l_proc, 10);
1821 End update_validate;
1822 --
1823 -- ----------------------------------------------------------------------------
1824 -- |---------------------------< delete_validate >----------------------------|
1825 -- ----------------------------------------------------------------------------
1826 Procedure delete_validate
1827 (p_rec in per_dis_shd.g_rec_type
1828 ,p_effective_date in date
1829 ,p_datetrack_mode in varchar2
1833 --
1830 ,p_validation_start_date in date
1831 ,p_validation_end_date in date
1832 ) is
1834 l_proc varchar2(72) := g_package||'delete_validate';
1835 --
1836 Begin
1837 hr_utility.set_location('Entering:'||l_proc, 5);
1838 --
1839 -- Call all supporting business operations
1840 --
1841 dt_delete_validate
1842 (p_datetrack_mode => p_datetrack_mode
1843 ,p_validation_start_date => p_validation_start_date
1844 ,p_validation_end_date => p_validation_end_date
1845 ,p_disability_id => p_rec.disability_id
1846 );
1847 --
1848 hr_utility.set_location(' Leaving:'||l_proc, 10);
1849 End delete_validate;
1850 --
1851 end per_dis_bus;