4 -- ----------------------------------------------------------------------------
1 Package Body per_dpf_bus as
2 /* $Header: pedpfrhi.pkb 115.13 2002/12/05 10:20:52 pkakar ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_dpf_bus.'; -- Global package name
9 --
10 --
11 -- The following two global variables are only to be used by the
12 -- return_legislation_code function.
13 --
14 g_deployment_factor_id number default null;
15 g_legislation_code varchar2(150) default null;
16 -- ----------------------------------------------------------------------------
17 -- |-----------------------< chk_deployment_factor_id >-----------------------|
18 -- ----------------------------------------------------------------------------
19 --
20 -- Description
21 -- This procedure is used to check that the primary key for the deployment
22 -- factor table is created properly. It should be null on insert and
23 -- should not be able to be updated.
24 --
25 -- Pre Conditions
26 -- None.
27 --
28 -- In Parameters
32 --
29 -- deployment_factor_id PK of record being inserted or updated.
30 -- object_version_number Object version number of record being
31 -- inserted or updated.
33 -- Post Success
34 -- Processing continues
35 --
36 -- Post Failure
37 -- Errors handled by the procedure
38 --
39 -- Access Status
40 -- Internal table handler use only.
41 --
42 Procedure chk_deployment_factor_id(p_deployment_factor_id in number,
43 p_object_version_number in number) is
44 --
45 l_proc varchar2(72) := g_package||'chk_deployment_factor_id';
46 l_api_updating boolean;
47 --
48 Begin
49 --
50 hr_utility.set_location('Entering:'||l_proc, 5);
51 --
52 l_api_updating := per_dpf_shd.api_updating
53 (p_deployment_factor_id => p_deployment_factor_id,
54 p_object_version_number => p_object_version_number);
55 --
56 if (l_api_updating
57 and nvl(p_deployment_factor_id,hr_api.g_number)
58 <> per_dpf_shd.g_old_rec.deployment_factor_id) then
59 --
60 -- raise error as PK has changed
61 --
62 per_dpf_shd.constraint_error('PER_DEPLOYMENT_FACTORS_PK');
63 --
64 elsif not l_api_updating then
65 --
66 -- check if PK is null
67 --
68 if p_deployment_factor_id is not null then
69 --
70 -- raise error as PK is not null
71 --
72 per_esa_shd.constraint_error('PER_DEPLOYMENT_FACTORS_PK');
73 --
74 end if;
75 --
76 end if;
77 --
78 hr_utility.set_location(' Leaving:'||l_proc, 10);
79 --
80 End chk_deployment_factor_id;
81 --
82 -- ----------------------------------------------------------------------------
83 -- |-----------------------< chk_person_id >----------------------------------|
84 -- ----------------------------------------------------------------------------
85 --
86 -- Description
87 -- This procedure is used to check that the person exists as of the effective
88 -- date.
89 --
90 -- Pre Conditions
91 -- None.
92 --
93 -- In Parameters
94 -- effective_date effective date
95 -- deployment_factor_id PK of record being inserted or updated.
96 -- person_id ID of person we are checking
97 -- object_version_number Object version number of record being
98 -- inserted or updated.
99 --
100 -- Post Success
101 -- Processing continues
102 --
103 -- Post Failure
104 -- Errors handled by the procedure
105 --
106 -- Access Status
107 -- Internal table handler use only.
108 --
109 Procedure chk_person_id(p_effective_date in date,
110 p_deployment_factor_id in number,
111 p_person_id in number,
112 p_object_version_number in number) is
113 --
114 l_proc varchar2(72) := g_package||'chk_person_id';
115 l_api_updating boolean;
116 l_dummy varchar2(1);
117 --
118 cursor c1 is
119 select null
120 from per_people_f per
121 where per.person_id = p_person_id
122 and p_effective_date
123 between per.effective_start_date
124 and nvl(per.effective_end_date,hr_api.g_eot);
125 --
126 Begin
127 --
128 hr_utility.set_location('Entering:'||l_proc, 5);
129 --
130 l_api_updating := per_dpf_shd.api_updating
131 (p_deployment_factor_id => p_deployment_factor_id,
132 p_object_version_number => p_object_version_number);
133 --
134 if (l_api_updating
135 and nvl(p_person_id,hr_api.g_number)
136 <> per_dpf_shd.g_old_rec.person_id) then
137 --
138 -- raise error as person_id has changed
139 --
140 hr_utility.set_message(801,'HR_52022_DPF_CHK_PERSON_UPD');
141 hr_utility.raise_error;
142 --
143 elsif not l_api_updating then
144 --
145 -- check if person_id exists
146 --
147 if p_person_id is not null then
148 --
149 open c1;
150 --
151 fetch c1 into l_dummy;
152 if c1%notfound then
153 --
154 close c1;
155 hr_utility.set_message(800,'HR_52250_DPF_CHK_PERSON_ID');
156 hr_utility.raise_error;
157 --
158 end if;
159 --
160 close c1;
161 --
162 end if;
163 --
164 end if;
165 --
166 hr_utility.set_location(' Leaving:'||l_proc, 10);
167 --
168 End chk_person_id;
169 --
170 -- ----------------------------------------------------------------------------
171 -- |-----------------------< chk_position_id >--------------------------------|
172 -- ----------------------------------------------------------------------------
173 --
174 -- Description
175 -- This procedure is used to check that the position exists as of effective
176 -- date.
177 --
178 -- Pre Conditions
179 -- None.
180 --
181 -- In Parameters
182 -- effective_date effective date
183 -- deployment_factor_id PK of record being inserted or updated.
184 -- position_id ID of position we are checking
188 -- Post Success
185 -- object_version_number Object version number of record being
186 -- inserted or updated.
187 --
189 -- Processing continues
190 --
191 -- Post Failure
192 -- Errors handled by the procedure
193 --
194 -- Access Status
195 -- Internal table handler use only.
196 --
197 Procedure chk_position_id(p_deployment_factor_id in number,
198 p_position_id in number,
199 p_object_version_number in number,
200 p_effective_date in date) is
201 --
202 l_proc varchar2(72) := g_package||'chk_position_id';
203 l_api_updating boolean;
204 l_dummy varchar2(1);
205 --
206 -- Changed 12-Oct-99 SCNair (per_positions to hr_positions_f) Date tracked position req.
207 --
208 cursor c1 is
209 select null
210 from hr_positions_f per
211 where per.position_id = p_position_id
212 and p_effective_date
213 between effective_start_date
214 and effective_end_date
215 and p_effective_date
216 between per.date_effective
217 and nvl(per.date_end,hr_api.g_eot);
218 --
219 Begin
220 --
221 hr_utility.set_location('Entering:'||l_proc, 5);
222 --
223 l_api_updating := per_dpf_shd.api_updating
224 (p_deployment_factor_id => p_deployment_factor_id,
225 p_object_version_number => p_object_version_number);
226 --
227 if (l_api_updating
228 and nvl(p_position_id,hr_api.g_number)
229 <> per_dpf_shd.g_old_rec.position_id) then
230 --
231 -- raise error as position_id has changed
232 --
233 hr_utility.set_message(801,'HR_52023_DPF_CHK_POSITION_UPD');
234 hr_utility.raise_error;
235 --
236 elsif not l_api_updating then
237 --
238 -- check if position_id exists
239 --
240 if p_position_id is not null then
241 --
242 open c1;
243 --
244 fetch c1 into l_dummy;
245 if c1%notfound then
246 --
247 close c1;
248 per_dpf_shd.constraint_error('PER_DEPLOYMENT_FACTORS_FK2');
249 --
250 end if;
251 --
252 close c1;
253 --
254 end if;
255 --
256 end if;
257 --
258 hr_utility.set_location(' Leaving:'||l_proc, 10);
259 --
260 end chk_position_id;
261 --
262 -- ----------------------------------------------------------------------------
263 -- |----------------------------< chk_job_id >--------------------------------|
264 -- ----------------------------------------------------------------------------
265 --
266 -- Description
267 -- This procedure is used to check that the job exists as of effective date.
268 --
269 -- Pre Conditions
270 -- None.
271 --
272 -- In Parameters
273 -- deployment_factor_id PK of record being inserted or updated.
274 -- job_id ID of job we are checking
275 -- object_version_number Object version number of record being
276 -- inserted or updated.
277 -- effective_date effective date
278 --
279 -- Post Success
280 -- Processing continues
281 --
282 -- Post Failure
283 -- Errors handled by the procedure
284 --
285 -- Access Status
286 -- Internal table handler use only.
287 --
288 Procedure chk_job_id(p_deployment_factor_id in number,
289 p_job_id in number,
290 p_object_version_number in number,
291 p_effective_date in date) is
292 --
293 l_proc varchar2(72) := g_package||'chk_job_id';
294 l_api_updating boolean;
295 l_dummy varchar2(1);
296 --
297 cursor c1 is
298 select null
299 from per_jobs_v per
300 where per.job_id = p_job_id
301 and p_effective_date
302 between per.date_from
303 and nvl(per.date_to,hr_api.g_eot);
304 --
305 Begin
306 --
307 hr_utility.set_location('Entering:'||l_proc, 5);
308 --
309 l_api_updating := per_dpf_shd.api_updating
310 (p_deployment_factor_id => p_deployment_factor_id,
311 p_object_version_number => p_object_version_number);
312 --
313 if (l_api_updating
314 and nvl(p_job_id,hr_api.g_number)
315 <> per_dpf_shd.g_old_rec.job_id) then
316 --
317 -- raise error as job_id has changed
318 --
319 hr_utility.set_message(801,'HR_52025_DPF_CHK_JOB_UPD');
320 hr_utility.raise_error;
321 --
322 elsif not l_api_updating then
323 --
324 -- check if position_id exists
325 --
326 if p_job_id is not null then
327 --
328 open c1;
329 --
330 fetch c1 into l_dummy;
331 if c1%notfound then
332 --
333 close c1;
334 per_dpf_shd.constraint_error('PER_DEPLOYMENT_FACTORS_FK1');
335 --
336 end if;
337 --
338 close c1;
339 --
340 end if;
341 --
342 end if;
343 --
344 hr_utility.set_location(' Leaving:'||l_proc, 10);
345 --
346 end chk_job_id;
350 --
347 -- ----------------------------------------------------------------------------
348 -- |----------------------------< chk_mandatory_lookup >----------------------|
349 -- ----------------------------------------------------------------------------
351 -- Description
352 -- This procedure is used to check mandatory lookups exist within the lookup
353 -- type.
354 --
355 -- Pre Conditions
356 -- None.
357 --
358 -- In Parameters
359 -- deployment_factor_id PK of record being inserted or updated.
360 -- work_any_country work_any_country lookup.
361 -- work_any_location work_any_location lookup.
362 -- relocate_domestically relocate_domestically lookup.
363 -- relocate_internationally relocate_internationally lookup.
364 -- travel_required travel_required lookup.
365 -- effective_date system date.
366 -- object_version_number Object version number of record being
367 -- inserted or updated.
368 --
369 -- Post Success
370 -- Processing continues
371 --
372 -- Post Failure
373 -- Errors handled by the procedure
374 --
375 -- Access Status
376 -- Internal table handler use only.
377 --
378 -- effective_date effective date
379 Procedure chk_mandatory_lookup(p_deployment_factor_id in number,
380 p_work_any_country in varchar2,
381 p_work_any_location in varchar2,
382 p_relocate_domestically in varchar2,
383 p_relocate_internationally in varchar2,
384 p_travel_required in varchar2,
385 p_effective_date in date,
386 p_object_version_number in number) is
387 --
388 l_proc varchar2(72) := g_package||'chk_mandatory_lookup';
389 l_api_updating boolean;
390 --
391 Begin
392 --
393 hr_utility.set_location('Entering:'||l_proc, 5);
394 --
395 l_api_updating := per_dpf_shd.api_updating
396 (p_deployment_factor_id => p_deployment_factor_id,
397 p_object_version_number => p_object_version_number);
398 --
399 if (l_api_updating
400 and (nvl(p_work_any_country,hr_api.g_varchar2)
401 <> nvl(per_dpf_shd.g_old_rec.work_any_country,hr_api.g_varchar2)
402 or nvl(p_work_any_location,hr_api.g_varchar2)
403 <> nvl(per_dpf_shd.g_old_rec.work_any_location,hr_api.g_varchar2)
404 or nvl(p_relocate_domestically,hr_api.g_varchar2)
405 <> nvl(per_dpf_shd.g_old_rec.relocate_domestically,hr_api.g_varchar2)
406 or nvl(p_relocate_internationally,hr_api.g_varchar2)
407 <> nvl(per_dpf_shd.g_old_rec.relocate_internationally,hr_api.g_varchar2)
408 or nvl(p_travel_required,hr_api.g_varchar2)
409 <> nvl(per_dpf_shd.g_old_rec.travel_required,hr_api.g_varchar2))
410 or not l_api_updating) then
411 --
412 -- check if any lookup that has changed still exists in its lookup type
413 --
414 -- Check if lookup exists in YES_NO lookup type.
415 --
416 -- Work any country lookup.
417 --
418 if hr_api.not_exists_in_hr_lookups
419 (p_lookup_type => 'YES_NO',
420 p_lookup_code => p_work_any_country,
421 p_effective_date => p_effective_date) then
422 --
423 per_dpf_shd.constraint_error('PER_DPF_WORK_ANY_COUNTRY');
424 --
425 end if;
426 --
427 -- Work any location lookup.
428 --
429 if hr_api.not_exists_in_hr_lookups
430 (p_lookup_type => 'YES_NO',
431 p_lookup_code => p_work_any_location,
432 p_effective_date => p_effective_date) then
433 --
434 per_dpf_shd.constraint_error('PER_DPF_WORK_ANY_LOCATION');
435 --
436 end if;
437 --
438 -- Relocate Domestically lookup.
439 --
440 if hr_api.not_exists_in_hr_lookups
441 (p_lookup_type => 'YES_NO',
442 p_lookup_code => p_relocate_domestically,
443 p_effective_date => p_effective_date) then
444 --
445 per_dpf_shd.constraint_error('PER_DPF_RELOCATE_DOMESTICALLY');
446 --
447 end if;
448 --
449 -- Relocate Internationally lookup.
450 --
451 if hr_api.not_exists_in_hr_lookups
452 (p_lookup_type => 'YES_NO',
453 p_lookup_code => p_relocate_internationally,
454 p_effective_date => p_effective_date) then
455 --
456 per_dpf_shd.constraint_error('PER_DPF_RELOCATE_INTERNATIONALLY');
457 --
458 end if;
459 --
460 -- Travel Required lookup.
461 --
462 if hr_api.not_exists_in_hr_lookups
463 (p_lookup_type => 'YES_NO',
464 p_lookup_code => p_travel_required,
465 p_effective_date => p_effective_date) then
466 --
467 per_dpf_shd.constraint_error('PER_DPF_TRAVEL_REQUIRED');
468 --
469 end if;
470 --
471 end if;
472 --
473 hr_utility.set_location(' Leaving:'||l_proc, 10);
474 --
475 end chk_mandatory_lookup;
476 --
477 -- ----------------------------------------------------------------------------
478 -- |----------------------------< chk_country >-------------------------------|
479 -- ----------------------------------------------------------------------------
480 --
481 -- Description
482 -- This procedure is used to check that the country code exists in
483 -- FND_TERRITORIES.
484 --
485 -- Pre Conditions
486 -- None.
487 --
488 -- In Parameters
489 -- deployment_factor_id PK of record being inserted or updated.
490 -- country1 code of country we are checking.
491 -- country2 code of country we are checking.
492 -- country3 code of country we are checking.
493 -- no_country1 code of country we are checking.
494 -- no_country1 code of country we are checking.
495 -- no_country1 code of country we are checking.
496 -- object_version_number Object version number of record being
497 -- inserted or updated.
498 --
499 -- Post Success
500 -- Processing continues
501 --
502 -- Post Failure
503 -- Errors handled by the procedure
504 --
505 -- Access Status
506 -- Internal table handler use only.
507 --
508 Procedure chk_country(p_deployment_factor_id in number,
509 p_country1 in varchar2,
510 p_country2 in varchar2,
511 p_country3 in varchar2,
512 p_no_country1 in varchar2,
513 p_no_country2 in varchar2,
514 p_no_country3 in varchar2,
515 p_object_version_number in number) is
516 --
517 l_proc varchar2(72) := g_package||'chk_country';
518 l_api_updating boolean;
519 l_dummy varchar2(1);
520 --
521 cursor c1(p_code varchar2) is
522 select null
523 from fnd_territories per
524 where per.territory_code = p_code;
525 --
526 Begin
527 --
528 hr_utility.set_location('Entering:'||l_proc, 5);
529 --
530 l_api_updating := per_dpf_shd.api_updating
531 (p_deployment_factor_id => p_deployment_factor_id,
535 and (nvl(p_country1,hr_api.g_varchar2)
532 p_object_version_number => p_object_version_number);
533 --
534 if (l_api_updating
536 <> per_dpf_shd.g_old_rec.country1
537 or nvl(p_country2,hr_api.g_varchar2)
538 <> per_dpf_shd.g_old_rec.country2
539 or nvl(p_country3,hr_api.g_varchar2)
540 <> per_dpf_shd.g_old_rec.country3
541 or nvl(p_no_country1,hr_api.g_varchar2)
542 <> per_dpf_shd.g_old_rec.no_country1
543 or nvl(p_no_country2,hr_api.g_varchar2)
544 <> per_dpf_shd.g_old_rec.no_country2
545 or nvl(p_no_country3,hr_api.g_varchar2)
546 <> per_dpf_shd.g_old_rec.no_country3)
547 or not l_api_updating) then
548 --
549 -- Check if country code exists in FND_TERRITORIES.
550 --
551 if p_country1 is not null then
552 --
553 if nvl(p_country1,hr_api.g_varchar2) <>
554 per_dpf_shd.g_old_rec.country1 or
555 not l_api_updating then
556 --
557 -- check if country exists
558 --
559 open c1(p_country1);
560 --
561 fetch c1 into l_dummy;
562 if c1%notfound then
563 --
564 close c1;
565 hr_utility.set_message(801,'HR_52027_DPF_CHK_COUNTRY');
566 hr_utility.raise_error;
567 --
568 end if;
569 --
570 close c1;
571 --
572 end if;
573 --
574 end if;
575 --
576 if p_country2 is not null then
577 --
578 if nvl(p_country2,hr_api.g_varchar2) <>
579 per_dpf_shd.g_old_rec.country2 or
580 not l_api_updating then
581 --
582 -- check if country exists
583 --
584 open c1(p_country2);
585 --
586 fetch c1 into l_dummy;
587 if c1%notfound then
588 --
589 close c1;
590 hr_utility.set_message(801,'HR_52027_DPF_CHK_COUNTRY');
591 hr_utility.raise_error;
592 --
593 end if;
594 --
595 close c1;
596 --
597 end if;
598 --
599 end if;
600 --
601 if p_country3 is not null then
602 --
603 if nvl(p_country3,hr_api.g_varchar2) <>
604 per_dpf_shd.g_old_rec.country3 or
605 not l_api_updating then
606 --
607 -- check if country exists
608 --
609 open c1(p_country3);
610 --
611 fetch c1 into l_dummy;
612 if c1%notfound then
613 --
614 close c1;
615 hr_utility.set_message(801,'HR_52027_DPF_CHK_COUNTRY');
616 hr_utility.raise_error;
617 --
618 end if;
619 --
620 close c1;
621 --
622 end if;
623 --
624 end if;
625 --
626 if p_no_country1 is not null then
627 --
628 if nvl(p_no_country1,hr_api.g_varchar2) <>
629 per_dpf_shd.g_old_rec.no_country1 or
630 not l_api_updating then
631 --
632 -- check if country exists
633 --
634 open c1(p_no_country1);
635 --
636 fetch c1 into l_dummy;
637 if c1%notfound then
638 --
639 close c1;
640 hr_utility.set_message(801,'HR_52027_DPF_CHK_COUNTRY');
641 hr_utility.raise_error;
642 --
643 end if;
644 --
645 close c1;
646 --
647 end if;
648 --
649 end if;
650 --
651 if p_no_country2 is not null then
652 --
653 if nvl(p_no_country2,hr_api.g_varchar2) <>
654 per_dpf_shd.g_old_rec.no_country2 or
655 not l_api_updating then
656 --
657 -- check if country exists
658 --
659 open c1(p_no_country2);
660 --
661 fetch c1 into l_dummy;
662 if c1%notfound then
663 --
664 close c1;
665 hr_utility.set_message(801,'HR_52027_DPF_CHK_COUNTRY');
666 hr_utility.raise_error;
667 --
668 end if;
669 --
673 --
670 close c1;
671 --
672 end if;
674 end if;
675 --
676 if p_no_country3 is not null then
677 --
678 if nvl(p_no_country3,hr_api.g_varchar2) <>
679 per_dpf_shd.g_old_rec.no_country3 or
680 not l_api_updating then
681 --
682 -- check if country exists
683 --
684 open c1(p_no_country3);
685 --
686 fetch c1 into l_dummy;
687 if c1%notfound then
688 --
689 close c1;
690 hr_utility.set_message(801,'HR_52027_DPF_CHK_COUNTRY');
691 hr_utility.raise_error;
692 --
693 end if;
694 --
695 close c1;
696 --
697 end if;
698 --
699 end if;
700 --
701 end if;
702 --
703 hr_utility.set_location(' Leaving:'||l_proc, 10);
704 --
705 end chk_country;
706 -- ----------------------------------------------------------------------------
707 -- |----------------------------< chk_optional_lookup >-----------------------|
708 -- ----------------------------------------------------------------------------
709 --
710 -- Description
711 -- This procedure is used to check optional lookups exist within the lookup
712 -- type.
713 --
714 -- Pre Conditions
715 -- None.
716 --
717 -- In Parameters
718 -- deployment_factor_id PK of record being inserted or updated.
719 -- work_duration work duration lookup.
720 -- work_schedule work schedule lookup.
721 -- work_hours work hours lookup.
722 -- fte_capacity fte capacity lookup.
723 -- effective_date system date.
724 -- object_version_number Object version number of record being
725 -- inserted or updated.
726 --
727 -- Post Success
728 -- Processing continues
729 --
730 -- Post Failure
734 -- Internal table handler use only.
731 -- Errors handled by the procedure
732 --
733 -- Access Status
735 --
736 Procedure chk_optional_lookup(p_deployment_factor_id in number,
737 p_work_duration in varchar2,
738 p_work_schedule in varchar2,
739 p_work_hours in varchar2,
740 p_fte_capacity in varchar2,
741 p_effective_date in date,
742 p_object_version_number in number) is
743 --
744 l_proc varchar2(72) := g_package||'chk_optional_lookup';
745 l_api_updating boolean;
746 --
747 Begin
748 --
749 hr_utility.set_location('Entering:'||l_proc, 5);
750 --
751 l_api_updating := per_dpf_shd.api_updating
752 (p_deployment_factor_id => p_deployment_factor_id,
753 p_object_version_number => p_object_version_number);
754 --
755 if (l_api_updating
756 and (p_work_duration
757 <> nvl(per_dpf_shd.g_old_rec.work_duration,hr_api.g_varchar2)
758 or p_work_schedule
759 <> nvl(per_dpf_shd.g_old_rec.work_schedule,hr_api.g_varchar2)
760 or p_work_hours
761 <> nvl(per_dpf_shd.g_old_rec.work_hours,hr_api.g_varchar2)
762 or p_fte_capacity
763 <> nvl(per_dpf_shd.g_old_rec.fte_capacity,hr_api.g_varchar2))
764 or not l_api_updating) then
765 --
766 -- check if any lookup that has changed still exists in its lookup type
767 --
768 if p_work_duration is not null then
769 --
770 if p_work_duration <>
771 nvl(per_dpf_shd.g_old_rec.work_duration,hr_api.g_varchar2) or
772 not l_api_updating then
773 --
774 -- Check if lookup exists in PER_TIME_SCALES lookup type.
775 --
776 -- Work Duration lookup.
777 --
778 if hr_api.not_exists_in_hr_lookups
779 (p_lookup_type => 'PER_TIME_SCALES',
780 p_lookup_code => p_work_duration,
781 p_effective_date => p_effective_date) then
782 --
783 hr_utility.set_message(801,'HR_52028_DPF_CHK_WORK_DURATION');
784 hr_utility.raise_error;
785 --
786 end if;
787 --
788 end if;
789 --
790 end if;
791 --
792 if p_work_schedule is not null then
793 --
794 if p_work_schedule <>
795 nvl(per_dpf_shd.g_old_rec.work_schedule,hr_api.g_varchar2) or
796 not l_api_updating then
797 --
798 -- Check if lookup exists in PER_WORK_SCHEDULE lookup type.
799 --
800 -- Work Schedule lookup.
801 --
802 if hr_api.not_exists_in_hr_lookups
803 (p_lookup_type => 'PER_WORK_SCHEDULE',
804 p_lookup_code => p_work_schedule,
805 p_effective_date => p_effective_date) then
806 --
807 hr_utility.set_message(801,'HR_52029_DPF_CHK_WORK_SCHEDULE');
811 --
808 hr_utility.raise_error;
809 --
810 end if;
812 end if;
813 --
814 end if;
815 --
816 if p_work_hours is not null then
817 --
818 if p_work_hours <>
819 nvl(per_dpf_shd.g_old_rec.work_hours,hr_api.g_varchar2) or
820 not l_api_updating then
821 --
822 -- Check if lookup exists in PER_WORK_HOURS lookup type.
823 --
824 -- Work Hours lookup.
825 --
826 if hr_api.not_exists_in_hr_lookups
827 (p_lookup_type => 'PER_WORK_HOURS',
828 p_lookup_code => p_work_hours,
829 p_effective_date => p_effective_date) then
830 --
831 hr_utility.set_message(801,'HR_52030_DPF_CHK_WORK_HOURS');
832 hr_utility.raise_error;
833 --
834 end if;
835 --
836 end if;
837 --
838 end if;
839 --
840 if p_fte_capacity is not null then
841 --
842 if p_fte_capacity <>
843 nvl(per_dpf_shd.g_old_rec.fte_capacity,hr_api.g_varchar2) or
844 not l_api_updating then
845 --
846 -- Check if lookup exists in PER_FTE_CAPACITY lookup type.
847 --
848 -- FTE Capacity lookup.
849 --
850 if hr_api.not_exists_in_hr_lookups
851 (p_lookup_type => 'PER_FTE_CAPACITY',
852 p_lookup_code => p_fte_capacity,
853 p_effective_date => p_effective_date) then
854 --
855 hr_utility.set_message(801,'HR_52031_DPF_CHK_FTE_CAPACITY');
856 hr_utility.raise_error;
857 --
858 end if;
859 --
860 end if;
861 --
862 end if;
863 --
864 end if;
865 --
866 hr_utility.set_location(' Leaving:'||l_proc, 10);
867 --
868 end chk_optional_lookup;
869 -- ----------------------------------------------------------------------------
870 -- |---------------------< chk_person_optional_lookup >-----------------------|
871 -- ----------------------------------------------------------------------------
872 --
873 -- Description
874 -- This procedure is used to check that the person related optional lookups
875 -- exist within the lookup type.
876 --
877 -- Pre Conditions
878 -- None.
879 --
880 -- In Parameters
881 -- deployment_factor_id PK of record being inserted or updated.
882 -- visit_internationally visit internationally lookup.
883 -- only_current_location only current location lookup.
884 -- available_for_transfer available for transfer lookup.
885 -- relocation_preference relocation preference lookup.
886 -- effective_date system date.
887 -- object_version_number Object version number of record being
888 -- inserted or updated.
889 --
890 -- Post Success
891 -- Processing continues
892 --
893 -- Post Failure
894 -- Errors handled by the procedure
895 --
896 -- Access Status
897 -- Internal table handler use only.
898 --
899 Procedure chk_person_optional_lookup
900 (p_deployment_factor_id in number,
901 p_visit_internationally in varchar2,
902 p_only_current_location in varchar2,
906 p_object_version_number in number) is
903 p_available_for_transfer in varchar2,
904 p_relocation_preference in varchar2,
905 p_effective_date in date,
907 --
908 l_proc varchar2(72) := g_package||'chk_person_optional_lookup';
909 l_api_updating boolean;
910 --
911 Begin
912 --
913 hr_utility.set_location('Entering:'||l_proc, 5);
914 --
915 l_api_updating := per_dpf_shd.api_updating
916 (p_deployment_factor_id => p_deployment_factor_id,
917 p_object_version_number => p_object_version_number);
918 --
919 if (l_api_updating
920 and (nvl(p_visit_internationally,hr_api.g_varchar2)
921 <> nvl(per_dpf_shd.g_old_rec.visit_internationally,hr_api.g_varchar2)
922 or nvl(p_only_current_location,hr_api.g_varchar2)
923 <> nvl(per_dpf_shd.g_old_rec.only_current_location,hr_api.g_varchar2)
924 or nvl(p_available_for_transfer,hr_api.g_varchar2)
925 <> nvl(per_dpf_shd.g_old_rec.available_for_transfer,hr_api.g_varchar2)
926 or nvl(p_relocation_preference,hr_api.g_varchar2)
927 <> nvl(per_dpf_shd.g_old_rec.relocation_preference,hr_api.g_varchar2))
928 or not l_api_updating) then
929 --
930 -- check if any lookup that has changed still exists in its lookup type
931 --
932 if p_visit_internationally is not null then
933 --
934 if nvl(p_visit_internationally,hr_api.g_varchar2) <>
935 nvl(per_dpf_shd.g_old_rec.visit_internationally,hr_api.g_varchar2) or
939 --
936 not l_api_updating then
937 --
938 -- Check if lookup exists in YES_NO lookup type.
940 -- Visit Internationally lookup.
941 --
942 if hr_api.not_exists_in_hr_lookups
943 (p_lookup_type => 'YES_NO',
944 p_lookup_code => p_visit_internationally,
945 p_effective_date => p_effective_date) then
946 --
947 per_dpf_shd.constraint_error('PER_DPF_VISIT_INTERNATIONALLY');
948 --
949 end if;
950 --
951 end if;
952 --
953 end if;
954 --
955 if p_only_current_location is not null then
956 --
957 if nvl(p_only_current_location,hr_api.g_varchar2) <>
958 nvl(per_dpf_shd.g_old_rec.only_current_location,hr_api.g_varchar2) or
959 not l_api_updating then
960 --
961 -- Check if lookup exists in YES_NO lookup type.
962 --
963 -- Only Current Location lookup.
964 --
965 if hr_api.not_exists_in_hr_lookups
966 (p_lookup_type => 'YES_NO',
967 p_lookup_code => p_only_current_location,
968 p_effective_date => p_effective_date) then
969 --
970 per_dpf_shd.constraint_error('PER_DPF_ONLY_CURRENT_LOCATION');
971 --
972 end if;
973 --
974 end if;
975 --
976 end if;
977 --
978 if p_available_for_transfer is not null then
979 --
980 if nvl(p_available_for_transfer,hr_api.g_varchar2) <>
981 nvl(per_dpf_shd.g_old_rec.available_for_transfer,hr_api.g_varchar2) or
982 not l_api_updating then
983 --
984 -- Check if lookup exists in YES_NO lookup type.
985 --
986 -- Available for transfer lookup.
987 --
988 if hr_api.not_exists_in_hr_lookups
989 (p_lookup_type => 'YES_NO',
990 p_lookup_code => p_available_for_transfer,
991 p_effective_date => p_effective_date) then
992 --
993 per_dpf_shd.constraint_error('PER_DPF_AVAILABLE_FOR_TRANSFER');
994 --
995 end if;
996 --
997 end if;
998 --
999 end if;
1000 --
1001 if p_relocation_preference is not null then
1002 --
1003 if nvl(p_relocation_preference,hr_api.g_varchar2) <>
1004 nvl(per_dpf_shd.g_old_rec.relocation_preference,hr_api.g_varchar2) or
1005 not l_api_updating then
1006 --
1007 -- Check if lookup exists in PER_RELOCATION_PREFERENCES lookup type.
1008 --
1009 -- Relocation Preference lookup.
1010 --
1011 if hr_api.not_exists_in_hr_lookups
1012 (p_lookup_type => 'PER_RELOCATION_PREFERENCES',
1013 p_lookup_code => p_relocation_preference,
1014 p_effective_date => p_effective_date) then
1015 --
1016 hr_utility.set_message(801,'HR_52032_DPF_CHK_RELOC_PREF');
1017 hr_utility.raise_error;
1018 --
1019 end if;
1020 --
1021 end if;
1022 --
1023 end if;
1024 --
1025 end if;
1026 --
1027 hr_utility.set_location(' Leaving:'||l_proc, 10);
1028 --
1029 end chk_person_optional_lookup;
1030 --
1031 -- ----------------------------------------------------------------------------
1032 -- |---------------------< chk_job_pos_optional_lookup >----------------------|
1033 -- ----------------------------------------------------------------------------
1034 --
1035 -- Description
1036 -- This procedure is used to check that the Job/Position related optional
1037 -- lookups exist within the lookup type.
1038 --
1039 -- Pre Conditions
1040 -- None.
1041 --
1042 -- In Parameters
1043 -- deployment_factor_id PK of record being inserted or updated.
1044 -- relocation_required relocation required lookup.
1045 -- passport_required passport required lookup.
1046 -- service_minimum service minimum lookup.
1047 -- effective_date system date.
1048 -- object_version_number Object version number of record being
1049 -- inserted or updated.
1050 --
1051 -- Post Success
1052 -- Processing continues
1053 --
1054 -- Post Failure
1055 -- Errors handled by the procedure
1056 --
1057 -- Access Status
1058 -- Internal table handler use only.
1059 --
1060 Procedure chk_job_pos_optional_lookup
1061 (p_deployment_factor_id in number,
1062 p_relocation_required in varchar2,
1063 p_passport_required in varchar2,
1064 p_service_minimum in varchar2,
1065 p_effective_date in date,
1066 p_object_version_number in number) is
1067 --
1068 l_proc varchar2(72) := g_package||'chk_job_pos_optional_lookup';
1069 l_api_updating boolean;
1073 hr_utility.set_location('Entering:'||l_proc, 5);
1070 --
1071 Begin
1072 --
1074 --
1075 l_api_updating := per_dpf_shd.api_updating
1076 (p_deployment_factor_id => p_deployment_factor_id,
1077 p_object_version_number => p_object_version_number);
1078 --
1079 if (l_api_updating
1080 and (nvl(p_relocation_required,hr_api.g_varchar2)
1081 <> nvl(per_dpf_shd.g_old_rec.relocation_required,hr_api.g_varchar2)
1082 or nvl(p_passport_required,hr_api.g_varchar2)
1083 <> nvl(per_dpf_shd.g_old_rec.passport_required,hr_api.g_varchar2)
1084 or nvl(p_service_minimum,hr_api.g_varchar2)
1085 <> nvl(per_dpf_shd.g_old_rec.service_minimum,hr_api.g_varchar2))
1086 or not l_api_updating) then
1087 --
1088 -- check if any lookup that has changed still exists in its lookup type
1089 --
1090 if p_relocation_required is not null then
1091 --
1092 if nvl(p_relocation_required,hr_api.g_varchar2) <>
1093 nvl(per_dpf_shd.g_old_rec.relocation_required,hr_api.g_varchar2) or
1094 not l_api_updating then
1095 --
1096 -- Check if lookup exists in YES_NO lookup type.
1097 --
1098 -- Relocation Required lookup.
1099 --
1100 if hr_api.not_exists_in_hr_lookups
1101 (p_lookup_type => 'YES_NO',
1102 p_lookup_code => p_relocation_required,
1103 p_effective_date => p_effective_date) then
1104 --
1105 per_dpf_shd.constraint_error('PER_DPF_RELOCATION_REQUIRED');
1106 --
1107 end if;
1108 --
1109 end if;
1110 --
1111 end if;
1112 --
1113 if p_passport_required is not null then
1114 --
1115 if nvl(p_passport_required,hr_api.g_varchar2) <>
1116 nvl(per_dpf_shd.g_old_rec.passport_required,hr_api.g_varchar2) or
1117 not l_api_updating then
1118 --
1119 -- Check if lookup exists in YES_NO lookup type.
1120 --
1121 -- Passport Required lookup.
1122 --
1123 if hr_api.not_exists_in_hr_lookups
1124 (p_lookup_type => 'YES_NO',
1125 p_lookup_code => p_passport_required,
1126 p_effective_date => p_effective_date) then
1127 --
1128 per_dpf_shd.constraint_error('PER_DPF_PASSPORT_REQUIRED');
1129 --
1130 end if;
1131 --
1132 end if;
1133 --
1134 end if;
1135 --
1136 if p_service_minimum is not null then
1137 --
1138 if nvl(p_service_minimum,hr_api.g_varchar2) <>
1139 nvl(per_dpf_shd.g_old_rec.service_minimum,hr_api.g_varchar2) or
1140 not l_api_updating then
1141 --
1142 -- Check if lookup exists in PER_LENGTHS_OF_SERVICE lookup type.
1143 --
1144 -- Service Minimum lookup.
1145 --
1146 if hr_api.not_exists_in_hr_lookups
1147 (p_lookup_type => 'PER_LENGTHS_OF_SERVICE',
1148 p_lookup_code => p_service_minimum,
1149 p_effective_date => p_effective_date) then
1150 --
1151 hr_utility.set_message(801,'HR_52033_DPF_LENGTH_SERVICE');
1152 hr_utility.raise_error;
1153 --
1154 end if;
1155 --
1156 end if;
1157 --
1158 end if;
1159 --
1160 end if;
1161 --
1162 hr_utility.set_location(' Leaving:'||l_proc, 10);
1163 --
1164 end chk_job_pos_optional_lookup;
1165 -- ----------------------------------------------------------------------------
1166 -- |------------------------------< chk_location >----------------------------|
1167 -- ----------------------------------------------------------------------------
1168 --
1169 -- Description
1170 -- This procedure is used to check that the locations exist in the
1171 -- HR_LOCATIONS table.
1172 --
1173 -- Pre Conditions
1174 -- None.
1175 --
1176 -- In Parameters
1177 -- deployment_factor_id PK of record being inserted or updated.
1178 -- location1 location1 lookup.
1179 -- location2 location2 lookup.
1180 -- location3 location3 lookup.
1181 -- object_version_number Object version number of record being
1182 -- inserted or updated.
1183 --
1184 -- Post Success
1185 -- Processing continues
1186 --
1187 -- Post Failure
1188 -- Errors handled by the procedure
1189 --
1190 -- Access Status
1191 -- Internal table handler use only.
1192 --
1193 Procedure chk_location
1194 (p_deployment_factor_id in number,
1195 p_location1 in varchar2,
1196 p_location2 in varchar2,
1197 p_location3 in varchar2,
1198 p_object_version_number in number) is
1199 --
1203 --
1200 l_proc varchar2(72) := g_package||'chk_location';
1201 l_api_updating boolean;
1202 l_dummy varchar2(1);
1204 cursor c1(p_location_id varchar2) is
1205 select null
1206 from hr_locations hr
1207 where hr.location_id = p_location_id and hr.location_use = 'HR';
1208 --
1209 Begin
1210 --
1211 hr_utility.set_location('Entering:'||l_proc, 5);
1212 --
1213 l_api_updating := per_dpf_shd.api_updating
1214 (p_deployment_factor_id => p_deployment_factor_id,
1215 p_object_version_number => p_object_version_number);
1216 --
1217 if (l_api_updating
1218 and (nvl(p_location1,hr_api.g_varchar2)
1219 <> per_dpf_shd.g_old_rec.location1
1220 or nvl(p_location2,hr_api.g_varchar2)
1221 <> per_dpf_shd.g_old_rec.location2
1222 or nvl(p_location3,hr_api.g_varchar2)
1223 <> per_dpf_shd.g_old_rec.location3)
1224 or not l_api_updating) then
1225 --
1226 -- check if any location that has changed exists in HR_LOCATIONS table.
1227 --
1228 if p_location1 is not null then
1229 --
1230 if nvl(p_location1,hr_api.g_varchar2) <>
1231 per_dpf_shd.g_old_rec.location1 or
1232 not l_api_updating then
1233 --
1234 open c1(p_location1);
1235 --
1236 fetch c1 into l_dummy;
1237 if c1%notfound then
1238 --
1239 close c1;
1240 hr_utility.set_message(801,'HR_52034_DPF_LOCATION_EXIST');
1241 hr_utility.raise_error;
1242 --
1243 end if;
1244 --
1245 close c1;
1246 --
1247 end if;
1248 --
1249 end if;
1250 --
1251 if p_location2 is not null then
1252 --
1253 if nvl(p_location2,hr_api.g_varchar2) <>
1254 per_dpf_shd.g_old_rec.location2 or
1255 not l_api_updating then
1256 --
1257 open c1(p_location2);
1258 --
1259 fetch c1 into l_dummy;
1260 if c1%notfound then
1261 --
1262 close c1;
1263 hr_utility.set_message(801,'HR_52034_DPF_LOCATION_EXIST');
1264 hr_utility.raise_error;
1265 --
1266 end if;
1267 --
1268 close c1;
1269 --
1270 end if;
1271 --
1272 end if;
1273 --
1274 if p_location3 is not null then
1275 --
1276 if nvl(p_location3,hr_api.g_varchar2) <>
1277 per_dpf_shd.g_old_rec.location3 or
1278 not l_api_updating then
1279 --
1280 open c1(p_location3);
1281 --
1282 fetch c1 into l_dummy;
1283 if c1%notfound then
1284 --
1285 close c1;
1286 hr_utility.set_message(801,'HR_52034_DPF_LOCATION_EXIST');
1287 hr_utility.raise_error;
1288 --
1289 end if;
1290 --
1291 close c1;
1292 --
1293 end if;
1294 --
1295 end if;
1296 --
1297 end if;
1298 --
1299 end chk_location;
1300 -- ----------------------------------------------------------------------------
1301 -- |------------------------------< chk_id_populated >------------------------|
1302 -- ----------------------------------------------------------------------------
1303 --
1304 -- Description
1305 -- This procedure is used to check that the person , job or position id has
1306 -- been populated. A deployment factor must be related to a person, job or
1307 -- position but to only one of these.
1308 --
1309 -- Pre Conditions
1310 -- None.
1311 --
1312 -- In Parameters
1313 -- deployment_factor_id PK of record being inserted or updated.
1314 -- person_id person_id.
1315 -- position_id position_id.
1316 -- job_id job_id.
1317 -- object_version_number Object version number of record being
1318 -- inserted or updated.
1319 --
1320 -- Post Success
1321 -- Processing continues
1322 --
1323 -- Post Failure
1324 -- Errors handled by the procedure
1325 --
1326 -- Access Status
1327 -- Internal table handler use only.
1328 --
1329 Procedure chk_id_populated
1330 (p_deployment_factor_id in number,
1331 p_person_id in number,
1332 p_position_id in number,
1333 p_job_id in number,
1334 p_object_version_number in number) is
1335 --
1336 l_proc varchar2(72) := g_package||'chk_id_populated';
1337 l_api_updating boolean;
1338 --
1339 Begin
1340 --
1341 hr_utility.set_location('Entering:'||l_proc, 5);
1342 --
1343 l_api_updating := per_dpf_shd.api_updating
1344 (p_deployment_factor_id => p_deployment_factor_id,
1345 p_object_version_number => p_object_version_number);
1346 --
1347 if (l_api_updating
1348 and (nvl(p_person_id,hr_api.g_number)
1349 <> per_dpf_shd.g_old_rec.person_id
1350 or nvl(p_position_id,hr_api.g_number)
1351 <> per_dpf_shd.g_old_rec.position_id
1352 or nvl(p_job_id,hr_api.g_number)
1353 <> per_dpf_shd.g_old_rec.job_id)
1354 or not l_api_updating) then
1355 --
1356 -- Check if only one of the Job, Position, Person Id's is populated
1357 --
1358 if ((p_job_id is null and
1359 p_person_id is null and
1360 p_position_id is null)
1361 or
1362 (p_job_id is not null and
1363 (p_person_id is not null or
1364 p_position_id is not null))
1368 p_person_id is not null))
1365 or
1366 (p_position_id is not null and
1367 (p_job_id is not null or
1369 or
1370 (p_person_id is not null and
1371 (p_job_id is not null or
1372 p_position_id is not null))) then
1373 --
1374 -- Raise error as either no Id is populated or more than one Id is
1375 -- populated.
1376 --
1377 hr_utility.set_message(801,'HR_52035_DPF_CHK_ID_POPULATED');
1378 hr_utility.raise_error;
1379 --
1380 end if;
1381 --
1382 end if;
1383 --
1384 end chk_id_populated;
1385 -- ----------------------------------------------------------------------------
1386 -- |---------------------------< chk_person_attributes >----------------------|
1387 -- ----------------------------------------------------------------------------
1388 --
1389 -- Description
1390 -- This procedure is used to check that the job/position related attributes
1391 -- ae not populated if the deployment factor is for a person.
1392 --
1393 -- Pre Conditions
1394 -- None.
1395 --
1396 -- In Parameters
1397 -- deployment_factor_id PK of record being inserted or updated.
1398 -- person_id person_id.
1399 -- relocation_required relocation required.
1400 -- passport_required passport required.
1401 -- location1 location1.
1402 -- location2 location2.
1403 -- location3 location3.
1404 -- other_requirements other requirements.
1405 -- service_minimum service minimum.
1406 -- object_version_number Object version number of record being
1407 -- inserted or updated.
1408 --
1409 -- Post Success
1410 -- Processing continues
1411 --
1412 -- Post Failure
1413 -- Errors handled by the procedure
1414 --
1415 -- Access Status
1416 -- Internal table handler use only.
1417 --
1418 Procedure chk_person_attributes
1419 (p_deployment_factor_id in number,
1420 p_person_id in number,
1421 p_relocation_required in varchar2,
1422 p_passport_required in varchar2,
1423 p_location1 in varchar2,
1424 p_location2 in varchar2,
1425 p_location3 in varchar2,
1426 p_other_requirements in varchar2,
1427 p_service_minimum in varchar2,
1428 p_object_version_number in number) is
1429 --
1430 l_proc varchar2(72) := g_package||'chk_person_attributes';
1431 l_api_updating boolean;
1432 --
1433 Begin
1434 --
1435 hr_utility.set_location('Entering:'||l_proc, 5);
1436 --
1437 l_api_updating := per_dpf_shd.api_updating
1438 (p_deployment_factor_id => p_deployment_factor_id,
1439 p_object_version_number => p_object_version_number);
1440 --
1441 if (l_api_updating
1442 and (nvl(p_relocation_required,hr_api.g_varchar2)
1443 <> per_dpf_shd.g_old_rec.relocation_required
1444 or nvl(p_passport_required,hr_api.g_varchar2)
1445 <> per_dpf_shd.g_old_rec.passport_required
1446 or nvl(p_location1,hr_api.g_varchar2)
1447 <> per_dpf_shd.g_old_rec.location1
1448 or nvl(p_location2,hr_api.g_varchar2)
1449 <> per_dpf_shd.g_old_rec.location2
1450 or nvl(p_location3,hr_api.g_varchar2)
1451 <> per_dpf_shd.g_old_rec.location3
1452 or nvl(p_other_requirements,hr_api.g_varchar2)
1453 <> per_dpf_shd.g_old_rec.other_requirements
1454 or nvl(p_service_minimum,hr_api.g_varchar2)
1455 <> per_dpf_shd.g_old_rec.service_minimum)
1456 or not l_api_updating) then
1457 --
1458 -- Check if any of the following attributes are set to not null values
1459 -- RELOCATION_REQUIRED, PASSPORT_REQUIRED, LOCATION1, LOCATION2,
1460 -- LOCATION3, OTHER_REQUIREMENTS, SERVICE_MINIMUM.
1461 --
1462 if p_person_id is not null then
1463 --
1464 if (p_relocation_required is not null or
1465 p_passport_required is not null or
1466 p_location1 is not null or
1467 p_location2 is not null or
1468 p_location3 is not null or
1469 p_other_requirements is not null or
1470 p_service_minimum is not null) then
1471 --
1472 hr_utility.set_message(801,'HR_52036_DPF_PERSON_ATTRIBUTES');
1473 hr_utility.raise_error;
1474 --
1475 end if;
1476 --
1477 end if;
1478 --
1479 end if;
1480 --
1481 end chk_person_attributes;
1482 -- ----------------------------------------------------------------------------
1483 -- |--------------------------< chk_job_pos_attributes >----------------------|
1484 -- ----------------------------------------------------------------------------
1485 --
1486 -- Description
1487 -- This procedure is used to check that the person related attributes
1488 -- are not populated if the deployment factor is for a person.
1489 --
1490 -- Pre Conditions
1491 -- None.
1492 --
1493 -- In Parameters
1494 -- deployment_factor_id PK of record being inserted or updated.
1495 -- job_id person_id.
1496 -- position_id position_id.
1497 -- visit_internationally visit interantionally lookup.
1498 -- only_current_location only current location lookup.
1499 -- no_country1 no country1 value.
1500 -- no_country2 no country2 value.
1504 -- available_for_transfer available for transfer.
1501 -- no_country3 no country3 value.
1502 -- comments comments value.
1503 -- earliest_available_date earliest available date.
1505 -- relocation_preference relocation preference.
1506 -- object_version_number Object version number of record being
1507 -- inserted or updated.
1508 --
1509 -- Post Success
1510 -- Processing continues
1511 --
1512 -- Post Failure
1513 -- Errors handled by the procedure
1514 --
1515 -- Access Status
1516 -- Internal table handler use only.
1517 --
1518 Procedure chk_job_pos_attributes
1519 (p_deployment_factor_id in number,
1520 p_job_id in number,
1521 p_position_id in number,
1522 p_visit_internationally in varchar2,
1523 p_only_current_location in varchar2,
1524 p_no_country1 in varchar2,
1525 p_no_country2 in varchar2,
1526 p_no_country3 in varchar2,
1527 p_comments in varchar2,
1528 p_earliest_available_date in date,
1529 p_available_for_transfer in varchar2,
1530 p_relocation_preference in varchar2,
1531 p_object_version_number in number) is
1532 --
1533 l_proc varchar2(72) := g_package||'chk_job_pos_attributes';
1534 l_api_updating boolean;
1535 --
1536 Begin
1537 --
1538 hr_utility.set_location('Entering:'||l_proc, 5);
1539 --
1540 l_api_updating := per_dpf_shd.api_updating
1541 (p_deployment_factor_id => p_deployment_factor_id,
1542 p_object_version_number => p_object_version_number);
1543 --
1544 if (l_api_updating
1545 and (nvl(p_visit_internationally,hr_api.g_varchar2)
1546 <> per_dpf_shd.g_old_rec.visit_internationally
1547 or nvl(p_only_current_location,hr_api.g_varchar2)
1548 <> per_dpf_shd.g_old_rec.only_current_location
1549 or nvl(p_no_country1,hr_api.g_varchar2)
1550 <> per_dpf_shd.g_old_rec.no_country1
1551 or nvl(p_no_country2,hr_api.g_varchar2)
1552 <> per_dpf_shd.g_old_rec.no_country2
1553 or nvl(p_no_country3,hr_api.g_varchar2)
1554 <> per_dpf_shd.g_old_rec.no_country3
1555 or nvl(p_comments,hr_api.g_varchar2)
1556 <> per_dpf_shd.g_old_rec.comments
1557 or nvl(p_earliest_available_date,hr_api.g_date)
1558 <> per_dpf_shd.g_old_rec.earliest_available_date
1559 or nvl(p_available_for_transfer,hr_api.g_varchar2)
1560 <> per_dpf_shd.g_old_rec.available_for_transfer
1561 or nvl(p_relocation_preference,hr_api.g_varchar2)
1562 <> per_dpf_shd.g_old_rec.relocation_preference)
1563 or not l_api_updating) then
1564 --
1565 -- Check if any of the following attributes are set to not null values
1566 -- VISIT_INTERNATIONALLY, ONLY_CURRENT_LOCATION, NO_COUNTRY1, NO_COUNTRY2,
1567 -- NO_COUNTRY3, COMMENTS, EARLIEST_AVAILABLE_DATE, AVAILABLE_FOR_TRANSFER,
1568 -- RELOCATION_PREFERENCE.
1569 --
1570 if (p_job_id is not null or
1571 p_position_id is not null) then
1572 --
1573 if (p_visit_internationally is not null or
1574 p_only_current_location is not null or
1575 p_no_country1 is not null or
1576 p_no_country2 is not null or
1577 p_no_country3 is not null or
1578 p_comments is not null or
1579 p_earliest_available_date is not null or
1580 p_available_for_transfer is not null or
1581 p_relocation_preference is not null) then
1582 --
1583 hr_utility.set_message(801,'HR_52037_DPF_JOBPOS_ATTRIBUTES');
1584 hr_utility.raise_error;
1585 --
1586 end if;
1590 end if;
1587 --
1588 end if;
1589 --
1591 --
1592 end chk_job_pos_attributes;
1593 -- ----------------------------------------------------------------------------
1594 -- |--------------------------< chk_unique_key >------------------------------|
1595 -- ----------------------------------------------------------------------------
1596 --
1597 -- Description
1598 -- This procedure is used to check that the person_id, job_id ,position_id
1599 -- do not conflict with already created records in the database.
1600 --
1601 -- Pre Conditions
1602 -- None.
1603 --
1604 -- In Parameters
1605 -- deployment_factor_id PK of record being inserted or updated.
1606 -- job_id person_id.
1607 -- position_id position_id.
1608 -- person_id person_id.
1609 -- object_version_number Object version number of record being
1610 -- inserted or updated.
1611 --
1612 -- Post Success
1613 -- Processing continues
1614 --
1615 -- Post Failure
1616 -- Errors handled by the procedure
1617 --
1618 -- Access Status
1619 -- Internal table handler use only.
1620 --
1621 Procedure chk_unique_key
1622 (p_deployment_factor_id in number,
1623 p_job_id in number,
1624 p_position_id in number,
1625 p_person_id in number,
1626 p_object_version_number in number) is
1627 --
1628 l_proc varchar2(72) := g_package||'chk_unique_key';
1629 l_api_updating boolean;
1630 l_dummy varchar2(1);
1631 --
1632 cursor c1 is
1633 select null
1634 from per_deployment_factors per
1635 where per.deployment_factor_id <> nvl(p_deployment_factor_id,-1)
1636 and nvl(per.job_id,-1) = nvl(p_job_id,-1)
1637 and nvl(per.position_id,-1) = nvl(p_position_id,-1)
1638 and nvl(per.person_id,-1) = nvl(p_person_id,-1);
1639 --
1640 Begin
1641 --
1642 hr_utility.set_location('Entering:'||l_proc, 5);
1643 --
1644 l_api_updating := per_dpf_shd.api_updating
1645 (p_deployment_factor_id => p_deployment_factor_id,
1646 p_object_version_number => p_object_version_number);
1647 --
1648 if (l_api_updating
1649 and (nvl(p_person_id,hr_api.g_number)
1650 <> per_dpf_shd.g_old_rec.person_id
1651 or nvl(p_position_id,hr_api.g_number)
1652 <> per_dpf_shd.g_old_rec.position_id
1653 or nvl(p_job_id,hr_api.g_number)
1654 <> per_dpf_shd.g_old_rec.job_id)
1655 or not l_api_updating) then
1656 --
1657 -- Check if the record being inserted conflicts with a previously created
1658 -- one.
1659 --
1660 open c1;
1661 --
1662 fetch c1 into l_dummy;
1663 if c1%found then
1664 --
1665 close c1;
1666 per_dpf_shd.constraint_error('PER_DEPLOYMENT_FACTORS_UK');
1667 --
1668 end if;
1669 --
1670 close c1;
1671 --
1672 end if;
1673 --
1674 end chk_unique_key;
1675 -- ----------------------------------------------------------------------------
1676 -- |--------------------------< chk_populated_bg >----------------------------|
1677 -- ----------------------------------------------------------------------------
1678 --
1679 -- Description
1680 -- This procedure is used to check that the person_id, job_id ,position_id
1684 -- Pre Conditions
1681 -- business groups are the same as the business group for the deployment
1682 -- factor.
1683 --
1685 -- None.
1686 --
1687 -- In Parameters
1688 -- deployment_factor_id PK of record being inserted or updated.
1689 -- business_group_id business_group_id.
1690 -- job_id person_id.
1691 -- position_id position_id.
1692 -- person_id person_id.
1693 -- object_version_number Object version number of record being
1694 -- inserted or updated.
1695 --
1696 -- Post Success
1697 -- Processing continues
1698 --
1699 -- Post Failure
1700 -- Errors handled by the procedure
1701 --
1702 -- Access Status
1703 -- Internal table handler use only.
1704 --
1705 Procedure chk_populated_bg
1706 (p_deployment_factor_id in number,
1707 p_business_group_id in number,
1708 p_job_id in number,
1709 p_position_id in number,
1710 p_person_id in number,
1711 p_object_version_number in number) is
1712 --
1713 l_proc varchar2(72) := g_package||'chk_populated_bg';
1714 l_api_updating boolean;
1715 l_dummy varchar2(1);
1716 --
1717 cursor c_jobs is
1718 select null
1719 from per_jobs_v per
1720 where per.business_group_id = p_business_group_id
1721 and per.job_id = p_job_id;
1722 --
1723 --
1724 -- Changed 12-Oct-99 SCNair (per_positions to hr_positions_f) Date tracked position req.
1725 --
1726 cursor c_positions is
1727 select null
1728 from hr_positions_f per
1729 where per.business_group_id = p_business_group_id
1730 and per.position_id = p_position_id;
1731 --
1732 cursor c_person is
1733 select null
1734 from per_people_f per
1735 where per.business_group_id = p_business_group_id
1736 and per.person_id = p_person_id;
1737 --
1738 Begin
1739 --
1740 hr_utility.set_location('Entering:'||l_proc, 5);
1741 --
1742 l_api_updating := per_dpf_shd.api_updating
1743 (p_deployment_factor_id => p_deployment_factor_id,
1744 p_object_version_number => p_object_version_number);
1745 --
1746 if (l_api_updating
1747 and (nvl(p_person_id,hr_api.g_number)
1748 <> per_dpf_shd.g_old_rec.person_id
1749 or nvl(p_position_id,hr_api.g_number)
1750 <> per_dpf_shd.g_old_rec.position_id
1751 or nvl(p_job_id,hr_api.g_number)
1752 <> per_dpf_shd.g_old_rec.job_id)
1753 or not l_api_updating) then
1754 --
1755 -- Check if the record being inserted conflicts with a previously created
1756 -- one.
1757 --
1758 if p_person_id is not null then
1759 --
1760 open c_person;
1761 --
1762 fetch c_person into l_dummy;
1763 if c_person%notfound then
1764 --
1765 close c_person;
1766 hr_utility.set_message(801,'HR_52042_DPF_PERSON_BG');
1767 hr_utility.raise_error;
1768 --
1769 end if;
1770 --
1771 close c_person;
1772 --
1773 end if;
1774 --
1775 if p_job_id is not null then
1776 --
1777 open c_jobs;
1778 --
1779 fetch c_jobs into l_dummy;
1780 if c_jobs%notfound then
1781 --
1782 close c_jobs;
1783 hr_utility.set_message(801,'HR_52039_DPF_JOB_BG');
1784 hr_utility.raise_error;
1785 --
1786 end if;
1787 --
1788 close c_jobs;
1789 --
1790 end if;
1791 --
1792 if p_position_id is not null then
1793 --
1794 open c_positions;
1795 --
1796 fetch c_positions into l_dummy;
1797 if c_positions%notfound then
1798 --
1799 close c_positions;
1800 hr_utility.set_message(801,'HR_52040_DPF_POSITION_BG');
1801 hr_utility.raise_error;
1802 --
1803 end if;
1804 --
1805 close c_positions;
1806 --
1807 end if;
1808 --
1809 end if;
1810 --
1811 end chk_populated_bg;
1812 -- -----------------------------------------------------------------------
1813 -- |------------------------------< chk_df >-----------------------------|
1814 -- -----------------------------------------------------------------------
1815 --
1816 -- Description:
1817 -- Validates the all Descriptive Flexfield values.
1818 --
1819 -- Pre-conditions:
1820 -- All other columns have been validated. Must be called as the
1821 -- last step from insert_validate and update_validate.
1822 --
1823 -- In Arguments:
1824 -- p_rec
1825 --
1826 -- Post Success:
1827 -- If the Descriptive Flexfield structure column and data values are
1828 -- all valid this procedure will end normally and processing will
1829 -- continue.
1830 --
1831 -- Post Failure:
1832 -- If the Descriptive Flexfield structure column value or any of
1833 -- the data values are invalid then an application error is raised as
1834 -- a PL/SQL exception.
1835 --
1836 -- Access Status:
1837 -- Internal Row Handler Use Only.
1838 --
1839 procedure chk_df
1840 (p_rec in per_dpf_shd.g_rec_type) is
1841 --
1842 l_proc varchar2(72) := g_package||'chk_df';
1843 --
1844 begin
1848 nvl(per_dpf_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1845 hr_utility.set_location('Entering:'||l_proc, 10);
1846 --
1847 if ((p_rec.deployment_factor_id is not null) and (
1849 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1850 nvl(per_dpf_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1851 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1852 nvl(per_dpf_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1853 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1854 nvl(per_dpf_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1855 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1856 nvl(per_dpf_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1857 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1858 nvl(per_dpf_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1859 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1860 nvl(per_dpf_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1861 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1862 nvl(per_dpf_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1863 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1864 nvl(per_dpf_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1865 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1866 nvl(per_dpf_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1867 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1868 nvl(per_dpf_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1869 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1870 nvl(per_dpf_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1871 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1872 nvl(per_dpf_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1873 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1874 nvl(per_dpf_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1875 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1876 nvl(per_dpf_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1877 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1878 nvl(per_dpf_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1879 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1880 nvl(per_dpf_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1881 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1882 nvl(per_dpf_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1883 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1884 nvl(per_dpf_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1885 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1886 nvl(per_dpf_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1887 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1888 nvl(per_dpf_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1889 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1890 or
1891 (p_rec.deployment_factor_id is null) then
1892 --
1893 -- Only execute the validation if absolutely necessary:
1894 -- a) During update, the structure column value or any
1895 -- of the attribute values have actually changed.
1896 -- b) During insert.
1897 --
1898 hr_dflex_utility.ins_or_upd_descflex_attribs
1899 (p_appl_short_name => 'PER'
1900 ,p_descflex_name => 'PER_DEPLOYMENT_FACTORS'
1901 ,p_attribute_category => p_rec.attribute_category
1902 ,p_attribute1_name => 'ATTRIBUTE1'
1903 ,p_attribute1_value => p_rec.attribute1
1904 ,p_attribute2_name => 'ATTRIBUTE2'
1905 ,p_attribute2_value => p_rec.attribute2
1906 ,p_attribute3_name => 'ATTRIBUTE3'
1907 ,p_attribute3_value => p_rec.attribute3
1908 ,p_attribute4_name => 'ATTRIBUTE4'
1909 ,p_attribute4_value => p_rec.attribute4
1910 ,p_attribute5_name => 'ATTRIBUTE5'
1911 ,p_attribute5_value => p_rec.attribute5
1912 ,p_attribute6_name => 'ATTRIBUTE6'
1913 ,p_attribute6_value => p_rec.attribute6
1914 ,p_attribute7_name => 'ATTRIBUTE7'
1915 ,p_attribute7_value => p_rec.attribute7
1916 ,p_attribute8_name => 'ATTRIBUTE8'
1917 ,p_attribute8_value => p_rec.attribute8
1918 ,p_attribute9_name => 'ATTRIBUTE9'
1919 ,p_attribute9_value => p_rec.attribute9
1920 ,p_attribute10_name => 'ATTRIBUTE10'
1921 ,p_attribute10_value => p_rec.attribute10
1922 ,p_attribute11_name => 'ATTRIBUTE11'
1923 ,p_attribute11_value => p_rec.attribute11
1924 ,p_attribute12_name => 'ATTRIBUTE12'
1925 ,p_attribute12_value => p_rec.attribute12
1926 ,p_attribute13_name => 'ATTRIBUTE13'
1927 ,p_attribute13_value => p_rec.attribute13
1928 ,p_attribute14_name => 'ATTRIBUTE14'
1929 ,p_attribute14_value => p_rec.attribute14
1930 ,p_attribute15_name => 'ATTRIBUTE15'
1931 ,p_attribute15_value => p_rec.attribute15
1932 ,p_attribute16_name => 'ATTRIBUTE16'
1933 ,p_attribute16_value => p_rec.attribute16
1934 ,p_attribute17_name => 'ATTRIBUTE17'
1935 ,p_attribute17_value => p_rec.attribute17
1936 ,p_attribute18_name => 'ATTRIBUTE18'
1937 ,p_attribute18_value => p_rec.attribute18
1938 ,p_attribute19_name => 'ATTRIBUTE19'
1939 ,p_attribute19_value => p_rec.attribute19
1940 ,p_attribute20_name => 'ATTRIBUTE20'
1941 ,p_attribute20_value => p_rec.attribute20
1942 );
1943 end if;
1944 --
1945 hr_utility.set_location(' Leaving:'||l_proc, 20);
1946 end chk_df;
1947 -- ----------------------------------------------------------------------------
1948 -- |---------------------------< insert_validate >----------------------------|
1949 -- ----------------------------------------------------------------------------
1950 Procedure insert_validate(p_rec in per_dpf_shd.g_rec_type,
1954 --
1951 p_effective_date in date) is
1952 --
1953 l_proc varchar2(72) := g_package||'insert_validate';
1955 Begin
1956 hr_utility.set_location('Entering:'||l_proc, 5);
1957 --
1958 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1959 --
1960 -- Call all supporting business operations
1961 --
1962 -- Business Rule Mapping
1963 -- =====================
1964 -- CHK_DEPLOYMENT_FACTOR_ID
1965 --
1966 chk_deployment_factor_id(p_rec.deployment_factor_id,
1967 p_rec.object_version_number);
1968 --
1969 -- Business Rule Mapping
1970 -- =====================
1971 -- CHK_PERSON_ID
1972 --
1973 chk_person_id(p_effective_date,
1974 p_rec.deployment_factor_id,
1975 p_rec.person_id,
1976 p_rec.object_version_number);
1977 --
1978 -- Business Rule Mapping
1979 -- =====================
1980 -- CHK_POSITION_ID
1981 --
1982 chk_position_id(p_rec.deployment_factor_id,
1983 p_rec.position_id,
1984 p_rec.object_version_number,
1985 p_effective_date);
1986 --
1987 -- Business Rule Mapping
1988 -- =====================
1989 -- CHK_JOB_ID
1990 --
1991 chk_job_id(p_rec.deployment_factor_id,
1992 p_rec.job_id,
1993 p_rec.object_version_number,
1994 p_effective_date);
1995 --
1996 -- Business Rule Mapping
1997 -- =====================
1998 -- CHK_MANDATORY_LOOKUP
1999 --
2000 chk_mandatory_lookup(p_rec.deployment_factor_id,
2001 p_rec.work_any_country,
2002 p_rec.work_any_location,
2003 p_rec.relocate_domestically,
2004 p_rec.relocate_internationally,
2005 p_rec.travel_required,
2006 p_effective_date,
2007 p_rec.object_version_number);
2008 --
2009 -- Business Rule Mapping
2010 -- =====================
2011 -- CHK_COUNTRY
2012 --
2013 chk_country(p_rec.deployment_factor_id,
2014 p_rec.country1,
2015 p_rec.country2,
2016 p_rec.country3,
2017 p_rec.no_country1,
2018 p_rec.no_country2,
2019 p_rec.no_country3,
2020 p_rec.object_version_number);
2021 --
2022 -- Business Rule Mapping
2023 -- =====================
2024 -- CHK_OPTIONAL_LOOKUP
2025 --
2026 chk_optional_lookup(p_rec.deployment_factor_id,
2027 p_rec.work_duration,
2028 p_rec.work_schedule,
2029 p_rec.work_hours,
2030 p_rec.fte_capacity,
2031 p_effective_date,
2032 p_rec.object_version_number);
2033 --
2034 -- Business Rule Mapping
2035 -- =====================
2036 -- CHK_PERSON_OPTIONAL_LOOKUP
2037 --
2038 chk_person_optional_lookup(p_rec.deployment_factor_id,
2039 p_rec.visit_internationally,
2040 p_rec.only_current_location,
2041 p_rec.available_for_transfer,
2042 p_rec.relocation_preference,
2043 p_effective_date,
2044 p_rec.object_version_number);
2045 --
2046 -- Business Rule Mapping
2047 -- =====================
2048 -- CHK_JOB_POS_OPTIONAL_LOOKUP
2049 --
2050 chk_job_pos_optional_lookup(p_rec.deployment_factor_id,
2051 p_rec.relocation_required,
2052 p_rec.passport_required,
2053 p_rec.service_minimum,
2054 p_effective_date,
2055 p_rec.object_version_number);
2056 --
2057 -- Business Rule Mapping
2058 -- =====================
2059 -- CHK_LOCATION
2060 --
2061 chk_location(p_rec.deployment_factor_id,
2062 p_rec.location1,
2063 p_rec.location2,
2064 p_rec.location3,
2065 p_rec.object_version_number);
2066 --
2067 -- Business Rule Mapping
2068 -- =====================
2069 -- CHK_ID_POPULATED
2070 --
2071 chk_id_populated(p_rec.deployment_factor_id,
2072 p_rec.person_id,
2073 p_rec.position_id,
2074 p_rec.job_id,
2075 p_rec.object_version_number);
2076 --
2077 -- Business Rule Mapping
2078 -- =====================
2079 -- CHK_PERSON_ATTRIBUTES
2080 --
2081 chk_person_attributes(p_rec.deployment_factor_id,
2082 p_rec.person_id,
2083 p_rec.relocation_required,
2084 p_rec.passport_required,
2085 p_rec.location1,
2086 p_rec.location2,
2087 p_rec.location3,
2088 p_rec.other_requirements,
2089 p_rec.service_minimum,
2090 p_rec.object_version_number);
2091 --
2092 -- Business Rule Mapping
2093 -- =====================
2094 -- CHK_JOB_POS_ATTRIBUTES
2095 --
2096 chk_job_pos_attributes(p_rec.deployment_factor_id,
2097 p_rec.job_id,
2098 p_rec.position_id,
2099 p_rec.visit_internationally,
2100 p_rec.only_current_location,
2104 p_rec.comments,
2101 p_rec.no_country1,
2102 p_rec.no_country2,
2103 p_rec.no_country3,
2105 p_rec.earliest_available_date,
2106 p_rec.available_for_transfer,
2107 p_rec.relocation_preference,
2108 p_rec.object_version_number);
2109 --
2110 -- Business Rule Mapping
2111 -- =====================
2112 -- CHK_UNIQUE_KEY
2113 --
2114 chk_unique_key(p_rec.deployment_factor_id,
2115 p_rec.job_id,
2116 p_rec.position_id,
2117 p_rec.person_id,
2118 p_rec.object_version_number);
2119 --
2120 -- Business Rule Mapping
2121 -- =====================
2122 -- CHK_POPULATED_BG
2123 --
2124 chk_populated_bg(p_rec.deployment_factor_id,
2125 p_rec.business_group_id,
2126 p_rec.job_id,
2127 p_rec.position_id,
2128 p_rec.person_id,
2129 p_rec.object_version_number);
2130 --
2131 -- Descriptive flex check
2132 -- ======================
2133 --
2134 --
2135 per_dpf_bus.chk_df(p_rec => p_rec);
2136 --
2137 --
2138 hr_utility.set_location(' Leaving:'||l_proc, 10);
2139 End insert_validate;
2140 --
2141 -- ----------------------------------------------------------------------------
2142 -- |---------------------------< update_validate >----------------------------|
2143 -- ----------------------------------------------------------------------------
2144 Procedure update_validate(p_rec in per_dpf_shd.g_rec_type,
2145 p_effective_date in date) is
2146 --
2147 l_proc varchar2(72) := g_package||'update_validate';
2148 --
2149 Begin
2150 hr_utility.set_location('Entering:'||l_proc, 5);
2151 --
2152 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2153 --
2154 -- Call all supporting business operations
2155 --
2156 -- Business Rule Mapping
2157 -- =====================
2158 -- CHK_DEPLOYMENT_FACTOR_ID
2159 --
2160 chk_deployment_factor_id(p_rec.deployment_factor_id,
2161 p_rec.object_version_number);
2162 --
2163 -- Business Rule Mapping
2164 -- =====================
2165 -- CHK_PERSON_ID
2166 --
2167 chk_person_id(p_effective_date,
2168 p_rec.deployment_factor_id,
2169 p_rec.person_id,
2170 p_rec.object_version_number);
2171 --
2172 -- Business Rule Mapping
2173 -- =====================
2174 -- CHK_POSITION_ID
2175 --
2176 chk_position_id(p_rec.deployment_factor_id,
2177 p_rec.position_id,
2178 p_rec.object_version_number,
2179 p_effective_date);
2180 --
2181 -- Business Rule Mapping
2182 -- =====================
2183 -- CHK_JOB_ID
2184 --
2185 chk_job_id(p_rec.deployment_factor_id,
2186 p_rec.job_id,
2187 p_rec.object_version_number,
2188 p_effective_date);
2189 --
2190 -- Business Rule Mapping
2191 -- =====================
2192 -- CHK_MANDATORY_LOOKUP
2193 --
2194 chk_mandatory_lookup(p_rec.deployment_factor_id,
2195 p_rec.work_any_country,
2196 p_rec.work_any_location,
2197 p_rec.relocate_domestically,
2198 p_rec.relocate_internationally,
2199 p_rec.travel_required,
2200 p_effective_date,
2201 p_rec.object_version_number);
2202 --
2203 -- Business Rule Mapping
2204 -- =====================
2205 -- CHK_COUNTRY
2206 --
2207 chk_country(p_rec.deployment_factor_id,
2208 p_rec.country1,
2209 p_rec.country2,
2210 p_rec.country3,
2211 p_rec.no_country1,
2212 p_rec.no_country2,
2213 p_rec.no_country3,
2214 p_rec.object_version_number);
2215 --
2216 -- Business Rule Mapping
2217 -- =====================
2218 -- CHK_OPTIONAL_LOOKUP
2219 --
2220 chk_optional_lookup(p_rec.deployment_factor_id,
2221 p_rec.work_duration,
2222 p_rec.work_schedule,
2223 p_rec.work_hours,
2224 p_rec.fte_capacity,
2225 p_effective_date,
2226 p_rec.object_version_number);
2227 --
2228 -- Business Rule Mapping
2229 -- =====================
2230 -- CHK_PERSON_OPTIONAL_LOOKUP
2231 --
2232 chk_person_optional_lookup(p_rec.deployment_factor_id,
2233 p_rec.visit_internationally,
2234 p_rec.only_current_location,
2235 p_rec.available_for_transfer,
2236 p_rec.relocation_preference,
2237 p_effective_date,
2238 p_rec.object_version_number);
2239 --
2240 -- Business Rule Mapping
2241 -- =====================
2242 -- CHK_JOB_POS_OPTIONAL_LOOKUP
2243 --
2244 chk_job_pos_optional_lookup(p_rec.deployment_factor_id,
2245 p_rec.relocation_required,
2246 p_rec.passport_required,
2247 p_rec.service_minimum,
2248 p_effective_date,
2249 p_rec.object_version_number);
2250 --
2251 -- Business Rule Mapping
2252 -- =====================
2253 -- CHK_LOCATION
2254 --
2258 p_rec.location3,
2255 chk_location(p_rec.deployment_factor_id,
2256 p_rec.location1,
2257 p_rec.location2,
2259 p_rec.object_version_number);
2260 --
2261 -- Business Rule Mapping
2262 -- =====================
2263 -- CHK_ID_POPULATED
2264 --
2265 chk_id_populated(p_rec.deployment_factor_id,
2266 p_rec.person_id,
2267 p_rec.position_id,
2268 p_rec.job_id,
2269 p_rec.object_version_number);
2270 --
2271 -- Business Rule Mapping
2272 -- =====================
2273 -- CHK_PERSON_ATTRIBUTES
2274 --
2275 chk_person_attributes(p_rec.deployment_factor_id,
2276 p_rec.person_id,
2277 p_rec.relocation_required,
2281 p_rec.location3,
2278 p_rec.passport_required,
2279 p_rec.location1,
2280 p_rec.location2,
2282 p_rec.other_requirements,
2283 p_rec.service_minimum,
2284 p_rec.object_version_number);
2285 --
2286 -- Business Rule Mapping
2287 -- =====================
2288 -- CHK_JOB_POS_ATTRIBUTES
2289 --
2290 chk_job_pos_attributes(p_rec.deployment_factor_id,
2291 p_rec.job_id,
2292 p_rec.position_id,
2293 p_rec.visit_internationally,
2294 p_rec.only_current_location,
2295 p_rec.no_country1,
2296 p_rec.no_country2,
2297 p_rec.no_country3,
2298 p_rec.comments,
2299 p_rec.earliest_available_date,
2300 p_rec.available_for_transfer,
2301 p_rec.relocation_preference,
2302 p_rec.object_version_number);
2303 --
2304 -- Business Rule Mapping
2305 -- =====================
2306 -- CHK_UNIQUE_KEY
2307 --
2308 chk_unique_key(p_rec.deployment_factor_id,
2309 p_rec.job_id,
2310 p_rec.position_id,
2311 p_rec.person_id,
2312 p_rec.object_version_number);
2313 --
2314 -- Business Rule Mapping
2315 -- =====================
2316 -- CHK_POPULATED_BG
2317 --
2318 chk_populated_bg(p_rec.deployment_factor_id,
2319 p_rec.business_group_id,
2320 p_rec.job_id,
2321 p_rec.position_id,
2322 p_rec.person_id,
2323 p_rec.object_version_number);
2324 --
2325 -- Descriptive flex check
2326 -- ======================
2327 --
2328 per_dpf_bus.chk_df(p_rec => p_rec);
2329 --
2330 hr_utility.set_location(' Leaving:'||l_proc, 10);
2331 End update_validate;
2332 --
2333 -- ----------------------------------------------------------------------------
2334 -- |---------------------------< delete_validate >----------------------------|
2335 -- ----------------------------------------------------------------------------
2336 Procedure delete_validate(p_rec in per_dpf_shd.g_rec_type) is
2337 --
2338 l_proc varchar2(72) := g_package||'delete_validate';
2339 --
2340 Begin
2341 hr_utility.set_location('Entering:'||l_proc, 5);
2342 --
2343 -- Call all supporting business operations
2344 --
2345 hr_utility.set_location(' Leaving:'||l_proc, 10);
2346 End delete_validate;
2347 --
2348 -- ---------------------------------------------------------------------------
2349 -- |---------------------< return_legislation_code >-------------------------|
2350 -- ---------------------------------------------------------------------------
2351 --
2352 function return_legislation_code
2353 (p_deployment_factor_id in per_deployment_factors.deployment_factor_id%TYPE
2354 ) return varchar2 is
2355 --
2356 -- to find legislation code
2357 --
2358 cursor csr_leg_code is
2359 select pbg.legislation_code
2360 from per_business_groups pbg
2361 , per_deployment_factors pdf
2362 where pdf.deployment_factor_id = p_deployment_factor_id
2363 and pbg.business_group_id = pdf.business_group_id;
2364 --
2365 -- Declare local variables
2366 --
2367 l_legislation_code varchar2(150);
2368 l_proc varchar2(72) := g_package||'return_legislation_code';
2369 begin
2370 hr_utility.set_location('Entering:'|| l_proc, 10);
2371 --
2372 -- Ensure that all the mandatory parameter are not null
2373 --
2374 hr_api.mandatory_arg_error(p_api_name => l_proc,
2378 if nvl(g_deployment_factor_id, hr_api.g_number) = p_deployment_factor_id then
2375 p_argument => 'deployment_factor_id',
2376 p_argument_value => p_deployment_factor_id);
2377 --
2379 --
2380 -- The legislation has already been found with a previous
2381 -- call to this function. Just return the value in the global
2382 -- variable.
2383 --
2384 l_legislation_code := g_legislation_code;
2385 hr_utility.set_location(l_proc, 20);
2386 else
2387 --
2388 -- The ID is different to the last call to this function
2389 -- or this is the first call to this function.
2390 --
2391 open csr_leg_code;
2392 fetch csr_leg_code into l_legislation_code;
2393 if csr_leg_code%notfound then
2394 --
2395 -- The primary key is invalid therefore we must error
2396 --
2397 close csr_leg_code;
2398 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
2399 fnd_message.raise_error;
2400 end if;
2401 --
2402 hr_utility.set_location(' Leaving:'|| l_proc, 30);
2403 --
2404 -- Set the global variables so the vlaues are
2405 -- available for the next call to this function
2406 --
2407 close csr_leg_code;
2408 g_deployment_factor_id := p_deployment_factor_id;
2409 g_legislation_code := l_legislation_code;
2410 end if;
2411 hr_utility.set_location('Entering:'|| l_proc, 40);
2412 --
2413 return l_legislation_code;
2414 end return_legislation_code;
2415 end per_dpf_bus;
[Home] [Help]