[Home] [Help]
PACKAGE BODY: APPS.PER_PDS_BUS
Source
1 Package Body per_pds_bus as
2 /* $Header: pepdsrhi.pkb 120.7 2006/05/18 16:47:13 lsilveir ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pds_bus.'; -- Global package name
9 --
10 -- 70.4 change a start.
11 --
12 -- 70.4 change a end.
13 --
14 -- The following two global variables are only to be
15 -- used by the return_legislation_code function.
16 --
17 g_legislation_code varchar2(150) default null;
18 g_period_of_service_id number default null;
19 --
20 --
21 -- ---------------------------------------------------------------------------
22 -- |--------------------< chk_accepted_termination_date >--------------------|
23 -- ---------------------------------------------------------------------------
24 --
25 -- Description:
26 -- Validates that the following business rules are met:
27 -- a) Must be null (I)
28 --
29 -- Pre-conditions:
30 -- None
31 --
32 -- In Arguments:
33 -- p_accepted_termination_date
34 --
35 -- Post Success:
36 -- If the above business rules are satisfied then processing continues.
37 --
38 -- Post Failure:
39 -- If the above business rules then an application error will be raised and
40 -- processing is terminated.
41 --
42 -- Access Status:
43 -- Internal Table Handler Use Only.
44 --
45 -- {End Of Comments}
46 -- ----------------------------------------------------------------------------
47 procedure chk_accepted_termination_date
48 --
49 (p_accepted_termination_date in
50 per_periods_of_service.accepted_termination_date%TYPE) is
51 --
52 l_proc varchar2(72) := g_package||'chk_accepted_termination_date';
53 --
54 begin
55 hr_utility.set_location('Entering:'|| l_proc, 1);
56 --
57 if not (p_accepted_termination_date is null)
58 then
59 -- CHK_ACCEPTED_TERMINATION_DATE / a
60 --
61 hr_utility.set_message(801,'HR_7492_PDS_INV_ACT_DT_BLANK');
62 hr_utility.raise_error;
63 end if;
64 --
65 hr_utility.set_location(' Leaving:'|| l_proc, 4);
66 end chk_accepted_termination_date;
67 --
68 -- ---------------------------------------------------------------------------
69 -- |---------------------< chk_actual_termination_date >---------------------|
70 -- ---------------------------------------------------------------------------
71 --
72 -- Description:
73 -- Validates that the following business rules are met:
74 -- a) Must be <= LAST_STANDARD_PROCESS_DATE (U)
75 -- b) Must be >= DATE_START (U)
76 -- c) Must be null (I)
77 -- d) Cannot be changed from one not null value to another (U)
78 -- e) Must be after initial insert date of last assignment (U)
79 -- f) Must be after effective start date of last future change(s) (U)
80 --
81 -- Pre-conditions:
82 -- person_id, date_start, last_standard_process_date and period_of_service_id
83 -- have been successfully validated separately.
84 --
85 -- In Arguments:
86 -- p_actual_termination_date
87 -- p_date_start
88 -- p_last_standard_process_date
89 -- p_object_version_number
90 -- p_period_of_service_id
91 -- p_person_id
92 --
93 -- Post Success:
94 -- If the above business rules are satisfied then processing continues.
95 --
96 -- Post Failure:
97 -- If the above business rules then an application error will be raised and
98 -- processing is terminated.
99 --
100 -- Access Status:
101 -- Internal Table Handler Use Only.
102 --
103 -- {End Of Comments}
104 -- ----------------------------------------------------------------------------
105 procedure chk_actual_termination_date
106 --
107 (p_actual_termination_date in
108 per_periods_of_service.actual_termination_date%TYPE
109 ,p_date_start in per_periods_of_service.date_start%TYPE
110 ,p_last_standard_process_date in
111 per_periods_of_service.last_standard_process_date%TYPE
112 ,p_object_version_number in
113 per_periods_of_service.object_version_number%TYPE
114 ,p_period_of_service_id in
115 per_periods_of_service.period_of_service_id%TYPE
116 ,p_person_id in per_periods_of_service.person_id%TYPE
117 ) is
118 --
119 l_api_updating boolean;
120 l_no_data_found boolean;
121 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
122 l_assignment_id per_assignments_f.assignment_id%TYPE;
123 l_proc varchar2(72) :=
124 g_package||'chk_actual_termination_date';
125 --
126 cursor csr_get_max_asg_start_date is
127 select min(asg.effective_start_date)
128 , asg.assignment_id
129 from per_assignments_f asg
130 where asg.period_of_service_id = p_period_of_service_id
131 group by asg.assignment_id
132 order by 1 desc;
133 --
134 cursor csr_get_max_per_eff_date is
135 select max(per.effective_start_date)
136 from per_all_people_f per
137 where per.person_id = p_person_id;
138 --
139 begin
140 hr_utility.set_location('Entering:'|| l_proc, 1);
141 --
142 -- Check mandatory parameters have been set
143 --
144 hr_api.mandatory_arg_error
145 (p_api_name => l_proc
146 ,p_argument => 'date_start'
147 ,p_argument_value => p_date_start
148 );
149 --
150 hr_utility.set_location(l_proc, 20);
151 --
152 -- Check to see if record updated.
153 --
154 l_api_updating := per_pds_shd.api_updating
155 (p_period_of_service_id => p_period_of_service_id
156 ,p_object_version_number => p_object_version_number);
157 --
158 hr_utility.set_location(l_proc, 30);
159 --
160 if l_api_updating
161 then
162 --
163 if nvl(per_pds_shd.g_old_rec.actual_termination_date, hr_api.g_date) <>
164 nvl(p_actual_termination_date, hr_api.g_date)
165 and p_actual_termination_date is not null
166 then
167 hr_utility.set_location(l_proc, 40);
168 --
169 if per_pds_shd.g_old_rec.actual_termination_date is not null
170 then
171 --
172 -- Cannot be changed from one not null value to another not null value.
173 -- CHK_ACTUAL_TERMINATION_DATE / d
174 --
175 hr_utility.set_message(801,'HR_7955_PDS_INV_ATT_CHANGE');
176 hr_utility.raise_error;
177 end if;
178 hr_utility.set_location(l_proc, 60);
179 --
180 if p_actual_termination_date > p_last_standard_process_date and
181 p_last_standard_process_date is not null then
182 --
183 -- CHK_ACTUAL_TERMINATION_DATE / a
184 --
185 hr_utility.set_message(801,'HR_7505_PDS_INV_LSP_ATT_DT');
186 hr_utility.raise_error;
187 end if;
188 hr_utility.set_location(l_proc, 70);
189 --
190 if not (nvl(p_actual_termination_date, hr_api.g_eot) >=
191 p_date_start) then
192 --
193 -- CHK_ACTUAL_TERMINATION_DATE / b
194 --
195 hr_utility.set_message(801,'HR_7493_PDS_INV_ATT_DT_ST');
196 hr_utility.raise_error;
197 end if;
198 hr_utility.set_location(l_proc, 80);
199 --
200 -- Get the initial insert date of the latest assignment.
201 --
202 open csr_get_max_asg_start_date;
203 fetch csr_get_max_asg_start_date
204 into l_effective_start_date
205 , l_assignment_id;
206 --
207 if csr_get_max_asg_start_date%NOTFOUND then
208 --
209 close csr_get_max_asg_start_date;
210 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
211 hr_utility.set_message_token('PROCEDURE', l_proc);
212 hr_utility.set_message_token('STEP', '5');
213 hr_utility.raise_error;
214 --
215 elsif (p_actual_termination_date < l_effective_start_date) then
216 --
217 -- CHK_ACTUAL_TERMINATION_DATE / e
218 --
219 close csr_get_max_asg_start_date;
220 hr_utility.set_message(801,'HR_7956_PDS_INV_ATT_DT_EARLY');
221 hr_utility.raise_error;
222 end if;
223 close csr_get_max_asg_start_date;
224 hr_utility.set_location(l_proc, 110);
225 --
226 -- Get the latest effective start date for any person future changes.
227 --
228 open csr_get_max_per_eff_date;
229 fetch csr_get_max_per_eff_date
230 into l_effective_start_date;
231 close csr_get_max_per_eff_date;
232 --
233 if l_effective_start_date is null then
234 --
235 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
236 hr_utility.set_message_token('PROCEDURE', l_proc);
237 hr_utility.set_message_token('STEP', '10');
238 hr_utility.raise_error;
239 --
240 elsif not (p_actual_termination_date >= l_effective_start_date) then
241 --
242 -- CHK_ACTUAL_TERMINATION_DATE / f
243 --
244 hr_utility.set_message(801,'HR_7957_PDS_INV_ATT_FUTURE');
245 hr_utility.raise_error;
246 end if;
247 end if;
248 --
249 else
250 -- Not updating => inserting.
251 hr_utility.set_location(l_proc, 140);
252 --
253 if not (p_actual_termination_date is null) then
254 --
255 -- CHK_ACTUAL_TERMINATION_DATE / c
256 --
257 hr_utility.set_message(801,'HR_7502_PDS_INV_ATT_DT_BLANK');
258 hr_utility.raise_error;
259 end if;
260 --
261 end if;
262 --
263 hr_utility.set_location(' Leaving:'|| l_proc, 200);
264 end chk_actual_termination_date;
265 --
266 -- ---------------------------------------------------------------------------
267 -- |---------------------------< chk_date_start >----------------------------|
268 -- ---------------------------------------------------------------------------
269 --
270 -- Description:
271 -- Validates that the following business rules are met:
272 -- a) Mandatory (I,U)
273 -- NB: The unique combination of person_id and date_start is validated
274 -- via rule CHK_PERSON_ID_DATE_START.
275
276 --
277 -- Pre-conditions:
278 -- None
279 --
280 -- In Arguments:
281 -- p_date_start
282 --
283 -- Post Success:
284 -- If the above business rules are satisfied then processing continues.
285 --
286 -- Post Failure:
287 -- If the above business rules then an application error will be raised and
288 -- processing is terminated.
289 --
290 -- Access Status:
291 -- Internal Table Handler Use Only.
292 --
293 -- {End Of Comments}
294 -- ----------------------------------------------------------------------------
295 procedure chk_date_start
296 --
297 (p_date_start in per_periods_of_service.date_start%TYPE) is
298 --
299 l_proc varchar2(72) := g_package||'chk_date_start';
300 --
301 begin
302 hr_utility.set_location('Entering:'|| l_proc, 1);
303 --
304 -- CHK_DATE_START / a
305 --
306 hr_api.mandatory_arg_error
307 (p_api_name => l_proc
308 ,p_argument => 'date_start'
309 ,p_argument_value => p_date_start
310 );
311 --
312 hr_utility.set_location(' Leaving:'|| l_proc, 2);
313 end chk_date_start;
314 --
315 -- ---------------------------------------------------------------------------
316 -- |------------------------<chk_final_process_date >------------------------|
317 -- ---------------------------------------------------------------------------
318 --
319 -- Description:
320 -- Validates that the following business rules are met:
321 -- a) If the person is not assigned to any payrolls then
322 -- must be equal to ACTUAL_TERMINATION_DATE (U)
323 -- b) If the person is assigned to a payroll then
324 -- must equal the maximum period end date of all Assignments
325 -- for the current Period of Service (U)
326 -- c) Must be >= LAST_STANDARD_PROCESS_DATE (U)
327 -- d) If ACTUAL_TERMINATION_DATE is null then must be null (U)
328 -- e) Must be null (I)
329 --
330 -- Pre-conditions:
331 -- p_date_start, actual_termination_date, last_standard_process_date and
332 -- period_of_service_id have been successfully validated separately.
333 --
334 -- In Arguments:
335 -- p_actual_termination_date
336 -- p_date_start
337 -- p_final_process_date
338 -- p_last_standard_process_date
339 -- p_object_version_number
340 -- p_period_of_service_id
341 --
342 -- Post Success:
343 -- If the above business rules are satisfied then processing continues.
344 --
345 -- Post Failure:
346 -- If the above business rules then an application error will be raised and
347 -- processing is terminated.
348 --
349 -- Access Status:
350 -- Internal Table Handler Use Only.
351 --
352 -- {End Of Comments}
353 -- ----------------------------------------------------------------------------
354 procedure chk_final_process_date
355 --
356 (p_actual_termination_date in
357 per_periods_of_service.actual_termination_date%TYPE,
358 p_date_start in per_periods_of_service.date_start%TYPE,
359 p_final_process_date in
360 per_periods_of_service.final_process_date%TYPE,
361 p_last_standard_process_date in
362 per_periods_of_service.last_standard_process_date%TYPE,
363 p_object_version_number in
364 per_periods_of_service.object_version_number%TYPE,
365 p_period_of_service_id in
366 per_periods_of_service.period_of_service_id%TYPE) is
367 --
368 l_assigned_payroll boolean;
369 l_api_updating boolean;
370 l_max_end_date per_periods_of_service.final_process_date%TYPE;
371 l_proc varchar2(72) := g_package||'chk_final_process_date';
372 --
373 --
374 -- 115.30 (START)
375 --
376 CURSOR csr_next_pds IS
377 SELECT pds2.date_start
378 ,pds2.actual_termination_date
379 ,pds2.last_standard_process_date
380 ,pds2.final_process_date
381 FROM per_periods_of_service pds1
382 ,per_periods_of_service pds2
383 WHERE pds1.period_of_service_id = p_period_of_service_id
384 AND pds1.person_id = pds2.person_id
385 AND pds1.period_of_service_id <> pds2.period_of_service_id
386 AND pds2.date_start > pds1.date_start
387 ORDER BY pds2.date_start;
388 --
389 lr_next_pds csr_next_pds%ROWTYPE;
390 --
391 CURSOR csr_later_fpd IS
392 SELECT pds2.final_process_date
393 FROM per_periods_of_service pds1
397 AND pds1.period_of_service_id <> pds2.period_of_service_id
394 ,per_periods_of_service pds2
395 WHERE pds1.period_of_service_id = p_period_of_service_id
396 AND pds1.person_id = pds2.person_id
398 AND pds2.date_start > pds1.date_start;
399 --
400 l_later_fpd per_periods_of_service.final_process_date%TYPE;
401 --
402 CURSOR csr_prev_pds_fpd IS
403 SELECT MAX(pds2.final_process_date)
404 FROM per_periods_of_service pds1
405 ,per_periods_of_service pds2
406 WHERE pds1.period_of_service_id = p_period_of_service_id
407 AND pds1.person_id = pds2.person_id
408 AND pds1.period_of_service_id <> pds2.period_of_service_id
409 AND pds2.date_start < pds1.date_start;
410 --
411 l_prev_pds_fpd per_periods_of_service.final_process_date%TYPE;
412 --
413 --
414 -- 115.30 (END)
415 --
416 begin
417 hr_utility.set_location('Entering:'|| l_proc, 1);
418 --
419 -- Check mandatory parameters have been set
420 --
421 hr_api.mandatory_arg_error
422 (p_api_name => l_proc
423 ,p_argument => 'date_start'
424 ,p_argument_value => p_date_start
425 );
426 --
427 hr_utility.set_location(l_proc, 2);
428 --
429 -- Check to see if record updated.
430 --
431 l_api_updating := per_pds_shd.api_updating
432 (p_period_of_service_id => p_period_of_service_id
433 ,p_object_version_number => p_object_version_number);
434 --
435 hr_utility.set_location(l_proc, 5);
436 --
437 if l_api_updating
438 then
439 --
440 if nvl(per_pds_shd.g_old_rec.final_process_date, hr_api.g_date) <>
441 nvl(p_final_process_date, hr_api.g_date)
442 then
443 --
444 hr_utility.set_location(l_proc, 6);
445 --
446 --
447 -- 70.3 change d start.
448 --
449 --
450 -- 115.30 (START)
451 --
452 --if per_pds_shd.g_old_rec.final_process_date is not null
453 --and p_final_process_date is not null
454 --then
455 -- -- CHK_FINAL_PROCESS_DATE / g
456 -- --
457 -- hr_utility.set_message(801,'HR_7962_PDS_INV_FP_CHANGE');
458 -- hr_utility.raise_error;
459 --end if;
460 --
461 -- if FPD is changing and old FPD < new FPD then raise an error if new FPD
462 -- >= start date of next-latest PDS and old FPD < start date of next-latest
463 -- PDS. In effect, check that a PDS gap is not being updated to an overlap.
464 --
465 IF per_pds_shd.g_old_rec.final_process_date IS NOT NULL
466 AND p_final_process_date IS NOT NULL
467 AND (p_final_process_date > per_pds_shd.g_old_rec.final_process_date)
468 THEN
469 --
470 hr_utility.set_location(l_proc, 10);
471 -- Get the next latest PDS is one exists
472 OPEN csr_next_pds;
473 FETCH csr_next_pds INTO lr_next_pds;
474 IF csr_next_pds%FOUND THEN
475 -- Check if PDS gap is being updated to a overlap
476 hr_utility.set_location(l_proc, 15);
477 --
478 IF ((per_pds_shd.g_old_rec.final_process_date < lr_next_pds.date_start)
479 AND p_final_process_date >= lr_next_pds.date_start)
480 THEN
481 CLOSE csr_next_pds;
482 hr_utility.set_message(800,'HR_449744_EMP_FPD_PDS');
483 hr_utility.raise_error;
484 END IF;
485 --
486 -- 115.35 (START)
487 --
488 --
489 -- Ensure that new FPD is not equal to or greater than next PDS FPD
490 --
491 IF lr_next_pds.final_process_date IS NOT NULL
492 THEN
493 IF p_final_process_date >= lr_next_pds.final_process_date
494 THEN
495 CLOSE csr_next_pds;
496 hr_utility.set_message(800,'HR_449761_FPD_GT_PREV_PDS');
497 hr_utility.raise_error;
498 END IF;
499 END IF;
500 --
501 -- 115.35 (END)
502 --
503 END IF;
504 CLOSE csr_next_pds;
505 END IF;
506 --
507 hr_utility.set_location(l_proc, 20);
508 --
509 -- if FPD is changing and old FPD < new FPD then raise an error if old FPD
510 -- = LSPD/ATD but new FPD > LSPD/ATD.
511 --
512 IF per_pds_shd.g_old_rec.final_process_date IS NOT NULL
513 AND p_final_process_date IS NOT NULL
514 AND (p_final_process_date > per_pds_shd.g_old_rec.final_process_date)
515 THEN
516 --
517 hr_utility.set_location(l_proc, 22);
518 --
519 IF ((per_pds_shd.g_old_rec.final_process_date = NVL(p_last_standard_process_date, p_actual_termination_date))
520 AND p_final_process_date > NVL(p_last_standard_process_date, p_actual_termination_date))
521 THEN
522 hr_utility.set_message(800,'HR_449764_FPD_GT_LSPD_ATD');
523 hr_utility.raise_error;
524 END IF;
525 END IF;
526 --
527 -- 115.36 (START)
528 --
529 --
530 hr_utility.set_location(l_proc, 25);
531 --
532 IF per_pds_shd.g_old_rec.final_process_date IS NOT NULL
533 AND p_final_process_date IS NULL
537 --
534 THEN
535 --
536 hr_utility.set_location(l_proc, 26);
538 IF (per_pds_shd.g_old_rec.final_process_date = per_pds_shd.g_old_rec.actual_termination_date)
539 THEN
540 hr_utility.set_message(800,'HR_449764_FPD_GT_LSPD_ATD');
541 hr_utility.raise_error;
542 END IF;
543 END IF;
544 --
545 -- 115.36 (END)
546 --
547 --
548 hr_utility.set_location(l_proc, 30);
549 --
550 -- if FPD is changing and old FPD > new FPD then raise an error if new FPD
551 -- < start date of next-latest PDS and old FPD >= start date of next-latest
552 -- PDS. In effect, check that a PDS overlap is not being updated to an gap.
553 --
554 IF per_pds_shd.g_old_rec.final_process_date IS NOT NULL
555 AND p_final_process_date IS NOT NULL
556 AND (p_final_process_date < per_pds_shd.g_old_rec.final_process_date)
557 THEN
558 -- Get the next latest PDS is one exists
559 hr_utility.set_location(l_proc, 33);
560 --
561 OPEN csr_next_pds;
562 FETCH csr_next_pds INTO lr_next_pds;
563 IF csr_next_pds%FOUND THEN
564 -- Check if PDS overlap is being updated to a gap
565 hr_utility.set_location(l_proc, 35);
566 --
567 IF ((per_pds_shd.g_old_rec.final_process_date >= lr_next_pds.date_start)
568 AND p_final_process_date < lr_next_pds.date_start)
569 THEN
570 CLOSE csr_next_pds;
571 hr_utility.set_message(800,'HR_449744_EMP_FPD_PDS');
572 hr_utility.raise_error;
573 END IF;
574 END IF;
575 CLOSE csr_next_pds;
576 END IF;
577 --
578 hr_utility.set_location(l_proc, 40);
579 --
580 -- if FPD is changing and old FPD > new FPD then raise an error if the new
581 -- FPD <= NVL(LSPD,ATD)
582 --
583 IF per_pds_shd.g_old_rec.final_process_date IS NOT NULL
584 AND p_final_process_date IS NOT NULL
585 AND (p_final_process_date < per_pds_shd.g_old_rec.final_process_date)
586 AND (p_final_process_date <= NVL(p_last_standard_process_date, p_actual_termination_date))
587 THEN
588 hr_utility.set_message(800,'HR_449741_EMP_FPD_ATD');
589 hr_utility.raise_error;
590 END IF;
591 --
592 hr_utility.set_location(l_proc, 45);
593 --
594 -- if FPD is changing and old FPD > new FPD then raise an error if the new
595 -- FPD <= Prev PDS FPD
596 --
597 IF per_pds_shd.g_old_rec.final_process_date IS NOT NULL
598 AND p_final_process_date IS NOT NULL
599 AND (p_final_process_date < per_pds_shd.g_old_rec.final_process_date)
600 THEN
601 -- Get the previous PDS FPD
602 OPEN csr_prev_pds_fpd;
603 FETCH csr_prev_pds_fpd INTO l_prev_pds_fpd;
604 IF csr_prev_pds_fpd%FOUND THEN
605 IF p_final_process_date <= l_prev_pds_fpd THEN
606 CLOSE csr_prev_pds_fpd;
607 hr_utility.set_message(800,'HR_449761_FPD_GT_PREV_PDS');
608 hr_utility.raise_error;
609 END IF;
610 END IF;
611 CLOSE csr_prev_pds_fpd;
612 END IF;
613 --
614 hr_utility.set_location(l_proc, 50);
615 --
616 -- if FPD is changing and the new FPD is null, then raise an error is a
617 -- later PDS exists with FPD not null.
618 --
619 IF per_pds_shd.g_old_rec.final_process_date IS NOT NULL
620 AND p_final_process_date IS NULL
621 THEN
622 -- Check for a later PDS with FPD not null
623 hr_utility.set_location(l_proc, 53);
624 --
625 OPEN csr_later_fpd;
626 FETCH csr_later_fpd INTO l_later_fpd;
627 IF csr_later_fpd%FOUND THEN
628 CLOSE csr_later_fpd;
629 hr_utility.set_message(800,'HR_449743_EMP_FPD_REQD');
630 hr_utility.raise_error;
631 END IF;
632 CLOSE csr_later_fpd;
633 END IF;
634 --
635 -- 115.30 (END)
636 --
637 --
638 hr_utility.set_location(l_proc, 60);
639 if p_actual_termination_date is null
640 then
641 --
642 if not (p_final_process_date is null)
643 then
644 -- CHK_FINAL_PROCESS_DATE / d
645 --
646 hr_utility.set_message(801,'HR_7503_PDS_INV_FP_DT_BLANK');
647 hr_utility.raise_error;
648 end if;
649 --
650 end if;
651 --
652 hr_utility.set_location(l_proc, 7);
653 --
654 if p_last_standard_process_date is null
655 then
656 --
657 hr_utility.set_location(l_proc, 8);
658 --
659 if not (nvl(p_final_process_date, hr_api.g_eot) >=
660 nvl(p_actual_termination_date, hr_api.g_eot))
661 then
662 -- CHK_FINAL_PROCESS_DATE / f
663 --
664 hr_utility.set_message(801,'HR_7963_PDS_INV_FP_BEFORE_ATT');
665 hr_utility.raise_error;
666 end if;
667 else
668 --
669 -- 70.1 change a start.
670 --
671 if not (nvl(p_final_process_date, hr_api.g_eot) >=
672 p_last_standard_process_date)
673 --
674 -- 70.1 change a end.
675 --
679 hr_utility.set_message(801,'HR_7504_PDS_INV_FP_LSP_DT');
676 then
677 -- CHK_FINAL_PROCESS_DATE / c
678 --
680 hr_utility.raise_error;
681 end if;
682 end if;
683 --
684 -- 70.3 change d end.
685 --
686 hr_utility.set_location(l_proc, 8);
687 --
688 -- 70.4 change a start.
689 --
690 -- 70.4 change a end.
691 --
692 end if;
693 else
694 -- Not updating => inserting.
695 hr_utility.set_location(l_proc, 12);
696 --
697 if not (p_final_process_date is null)
698 then
699 -- CHK_FINAL_PROCESS_DATE / e
700 --
701 hr_utility.set_message(801,'HR_7496_PDS_INV_FP_DT');
702 hr_utility.raise_error;
703 end if;
704 --
705 hr_utility.set_location(l_proc, 13);
706 end if;
707 --
708 hr_utility.set_location(' Leaving:'|| l_proc, 14);
709 end chk_final_process_date;
710 --
711 -- ---------------------------------------------------------------------------
712 -- |-------------------< chk_last_standard_process_date >--------------------|
713 -- ---------------------------------------------------------------------------
714 --
715 -- Description:
716 -- Validates that the following business rules are met:
717 -- c) Must be >= ACTUAL_TERMINATION_DATE (U)
718 -- e) Must be null (I)
719 -- f) If ACTUAL_TERMINATION_DATE is null then must be null (U)
720 -- g) If US legislation then must be null (U)
721 -- h) If not US legislation and ACTUAL_TERMINATION_DATE is not null
722 -- then must not be null (U)
723 -- i) Cannot be changed from one not null value to another (U)
724 --
725 -- Pre-conditions:
726 -- p_date_start, actual_termination_date, and period_of_service_id
727 -- have been successfully validated separately.
728 --
729 -- In Arguments:
730 -- p_actual_termination_date
731 -- p_date_start
732 -- p_last_standard_process_date
733 -- p_object_version_number
734 -- p_period_of_service_id
735 --
736 -- Post Success:
737 -- If the above business rules are satisfied then processing continues.
738 --
739 -- Post Failure:
740 -- If the above business rules then an application error will be raised and
741 -- processing is terminated.
742 --
743 -- Access Status:
744 -- Internal Table Handler Use Only.
745 --
746 -- {End Of Comments}
747 -- ----------------------------------------------------------------------------
748 --
749 -- 70.3 change c start.
750 --
751 procedure chk_last_standard_process_date
752 --
753 (p_actual_termination_date in
754 per_periods_of_service.actual_termination_date%TYPE
755 ,p_business_group_id in
756 per_periods_of_service.business_group_id%TYPE
757 ,p_date_start in per_periods_of_service.date_start%TYPE
758 ,p_last_standard_process_date in
759 per_periods_of_service.last_standard_process_date%TYPE
760 ,p_object_version_number in
761 per_periods_of_service.object_version_number%TYPE
762 ,p_period_of_service_id in
763 per_periods_of_service.period_of_service_id%TYPE
764 ) is
765 --
766 l_api_updating boolean;
767 l_assigned_payroll boolean;
768 l_legislation_code per_business_groups.legislation_code%TYPE;
769 l_max_end_date per_periods_of_service.final_process_date%TYPE;
770 l_proc varchar2(72)
771 := g_package||'chk_last_standard_process_date';
772 --
773 cursor csr_get_legislation_code is
774 select bus.legislation_code
775 from per_business_groups bus
776 where bus.business_group_id = p_business_group_id;
777 --
778 --
779 begin
780 hr_utility.set_location('Entering:'|| l_proc, 1);
781 --
782 -- Check mandatory parameters have been set
783 --
784 hr_api.mandatory_arg_error
785 (p_api_name => l_proc
786 ,p_argument => 'date_start'
787 ,p_argument_value => p_date_start
788 );
789 --
790 hr_utility.set_location(l_proc, 10);
791 --
792 -- Check to see if record updated.
793 --
794 l_api_updating := per_pds_shd.api_updating
795 (p_period_of_service_id => p_period_of_service_id
796 ,p_object_version_number => p_object_version_number);
797 --
798 hr_utility.set_location(l_proc, 20);
799 --
800 if l_api_updating
801 then
802 --
803 if nvl(per_pds_shd.g_old_rec.last_standard_process_date, hr_api.g_date) <>
804 nvl(p_last_standard_process_date, hr_api.g_date)
805 then
806 --
807 hr_utility.set_location(l_proc, 30);
808 --
809 if per_pds_shd.g_old_rec.last_standard_process_date is not null
810 and p_last_standard_process_date is not null
811 then
812 -- CHK_LAST_STANDARD_PROCESS_DATE / i
813 --
814 hr_utility.set_message(801,'HR_7960_PDS_INV_LSP_CHANGE');
815 hr_utility.raise_error;
816 end if;
817 --
818 hr_utility.set_location(l_proc, 40);
819 --
820 open csr_get_legislation_code;
821 fetch csr_get_legislation_code
822 into l_legislation_code;
823 --
827 close csr_get_legislation_code;
824 if csr_get_legislation_code%NOTFOUND
825 then
826 --
828 --
829 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
830 hr_utility.set_message_token('PROCEDURE', l_proc);
831 hr_utility.set_message_token('STEP', '5');
832 hr_utility.raise_error;
833 end if;
834 --
835 close csr_get_legislation_code;
836 --
837 hr_utility.set_location(l_proc, 50);
838 --
839 -- Bug 1711085. VS. 27-Mar-2001. Commented out the code that disables
840 -- last_standard_process for US legislature.
841 /* if l_legislation_code = 'US'
842 then
843 --
844 hr_utility.set_location(l_proc, 60);
845 --
846 if not (p_last_standard_process_date is null)
847 then
848 -- CHK_LAST_STANDARD_PROCESS_DATE / g
849 --
850 hr_utility.set_message(801,'HR_7958_PDS_INV_US_LSP_BLANK');
851 hr_utility.raise_error;
852 end if;
853 end if;
854 */
855 --
856 if p_actual_termination_date is null
857 then
858 --
859 if not (p_last_standard_process_date is null)
860 then
861 -- CHK_LAST_STANDARD_PROCESS_DATE / f
862 --
863 hr_utility.set_message(801,'HR_7497_PDS_INV_LSP_DT_BLANK');
864 hr_utility.raise_error;
865 end if;
866 --
867 end if;
868 --
869 hr_utility.set_location(l_proc, 80);
870 --
871 -- 70.1 change a start.
872 --
873 if not (nvl(p_last_standard_process_date, hr_api.g_eot) >=
874 nvl(p_actual_termination_date, hr_api.g_eot))
875 --
876 -- 70.1 change a end.
877 --
878 then
879 -- CHK_LAST_STANDARD_PROCESS_DATE / c
880 --
881 hr_utility.set_message(801,'HR_7505_PDS_INV_LSP_ATT_DT');
882 hr_utility.raise_error;
883 end if;
884 --
885 hr_utility.set_location(l_proc, 90);
886 --
887 end if;
888 --
889 -- 70.16 change h start.
890 --
891 if (nvl(per_pds_shd.g_old_rec.actual_termination_date, hr_api.g_date) <>
892 nvl(p_actual_termination_date,hr_api.g_date) )
893 and (p_actual_termination_date is not null)
894 and l_legislation_code <> 'US'
895 --
896 then
897 hr_utility.set_location(l_proc, 100);
898 --
899 if p_last_standard_process_date is null
900 --
901 then
902 --
903 -- Must also be set to not null value if actual_termination_date
904 -- updated to not null value.
905 -- CHK_LAST_STANDARD_PROCESS_DATE / h
906 --
907 hr_utility.set_message(801,'HR_7959_PDS_INV_LSP_BLANK');
908 hr_utility.raise_error;
909 end if;
910 end if;
911 --
912 -- 70.16 change h end.
913 --
914 else
915 --
916 -- Not updating => inserting.
917 --
918 hr_utility.set_location(l_proc, 110);
919 --
920 if not (p_last_standard_process_date is null)
921 then
922 -- CHK_LAST_STANDARD_PROCESS_DATE / e
923 --
924 hr_utility.set_message(801,'HR_7484_PDS_INV_LSP_DT');
925 hr_utility.raise_error;
926 end if;
927 --
928 hr_utility.set_location(l_proc, 120);
929 end if;
930 --
931 hr_utility.set_location(' Leaving:'|| l_proc, 200);
932 end chk_last_standard_process_date;
933 --
934 -- 70.3 change c end.
935 --
936 -- ---------------------------------------------------------------------------
937 -- |-------------------------< chk_at_date_lsp_date >------------------------|
938 -- ---------------------------------------------------------------------------
939 --
940 -- Description:
941 -- Validates the following business rule :
942 --
943 -- If actual_termination_date is changed from a NULL value to
944 -- a NOT NULL value then last_standard_process_date must also
945 -- be changed to a NOT NULL value.
946 --
947 -- Pre-conditions:
948 -- actual_termination_date, last_standard_process_date have been
949 -- successfully validated separately.
950 --
951 -- In Arguments:
952 -- p_period_of_service_id
953 -- p_actual_termination_date
954 -- p_last_standard_process_date
955 -- p_object_version_number
956 --
957 -- Post Success:
958 -- If the above business rules are satisfied then processing continues.
959 --
960 -- Post Failure:
961 -- If the above business rules then an application error will be raised and
962 -- processing is terminated.
963 --
964 -- Access Status:
965 -- Internal Table Handler Use Only.
966 --
967 -- {End Of Comments}
968 -- ----------------------------------------------------------------------------
969 -- commented out this routine for fix of bug#2784295
970 /* procedure chk_at_date_lsp_date
971 --
972 (p_actual_termination_date in per_periods_of_service.actual_termination_date%TYPE
973 ,p_last_standard_process_date in per_periods_of_service.last_standard_process_date%TYPE
974 ,p_object_version_number in per_periods_of_service.object_version_number%TYPE
978 --
975 ,p_period_of_service_id in per_periods_of_service.period_of_service_id%TYPE
976 ,p_business_group_id in per_periods_of_service.business_group_id%TYPE
977 ) is
979 l_proc varchar2(72) := g_package||'chk_at_date_lsp_date';
980 l_api_updating boolean;
981 l_legislation_code per_business_groups.legislation_code%TYPE;
982 --
983 cursor csr_get_legislation_code is
984 select bus.legislation_code
985 from per_business_groups bus
986 where bus.business_group_id = p_business_group_id;
987 --
988 begin
989 hr_utility.set_location('Entering:'|| l_proc, 1);
990 --
991 hr_utility.set_location(l_proc, 10);
992 --
993 -- Check to see if record updated.
994 --
995 l_api_updating := per_pds_shd.api_updating
996 (p_period_of_service_id => p_period_of_service_id
997 ,p_object_version_number => p_object_version_number);
998 --
999 hr_utility.set_location(l_proc, 20);
1000 --
1001 -- Only proceed with validation if :
1002 -- a) The current g_old_rec is current and
1003 -- b) The value for actual_termination_date or last_standard_process_date
1004 -- has changed
1005 --
1006 if (l_api_updating
1007 and ((nvl(per_pds_shd.g_old_rec.actual_termination_date, hr_api.g_date)
1008 <> nvl(p_actual_termination_date, hr_api.g_date))
1009 or
1010 (nvl(per_pds_shd.g_old_rec.last_standard_process_date, hr_api.g_date)
1011 <> nvl(p_last_standard_process_date, hr_api.g_date))))
1012 or
1013 NOT l_api_updating then
1014 --
1015 hr_utility.set_location(l_proc, 30);
1016 --
1017 open csr_get_legislation_code;
1018 fetch csr_get_legislation_code
1019 into l_legislation_code;
1020 --
1021 if csr_get_legislation_code%NOTFOUND
1022 then
1023 --
1024 close csr_get_legislation_code;
1025 --
1026 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1027 hr_utility.set_message_token('PROCEDURE', l_proc);
1028 hr_utility.set_message_token('STEP', '5');
1029 hr_utility.raise_error;
1030 end if;
1031 --
1032 close csr_get_legislation_code;
1033 --
1034 hr_utility.set_location(l_proc, 50);
1035 --
1036 if l_legislation_code <> 'US' then
1037 --
1038 -- Check combination when either actual_termination_date or
1039 -- last_standard_process_date are set
1040 --
1041 if (per_pds_shd.g_old_rec.actual_termination_date is null
1042 and p_actual_termination_date is not null)
1043 and p_last_standard_process_date is null
1044 then
1045 hr_utility.set_message(801,'HR_7959_PDS_INV_LSP_BLANK');
1046 hr_utility.raise_error;
1047 end if;
1048 --
1049 end if;
1050 --
1051 end if;
1052 --
1053 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1054 end chk_at_date_lsp_date; */
1055 --
1056 -- ---------------------------------------------------------------------------
1057 -- |-------------------------< chk_leaving_reason >--------------------------|
1058 -- ---------------------------------------------------------------------------
1059 --
1060 -- Description:
1061 -- Validates that the following business rules are met:
1062 -- a) Validate against HR_LOOKUPS.lookup_code
1063 -- where LOOKUP_TYPE = 'LEAV_REAS' (U)
1064 -- b) Must be null (I)
1065 --
1066 -- Pre-conditions:
1067 -- period_of_Service_id must have been successfully validated.
1068 --
1069 -- In Arguments:
1070 -- p_leaving_reason
1071 -- p_effective_date
1072 -- p_object_version_number
1073 -- p_period_of_service_id
1074 --
1075 -- Post Success:
1076 -- If the above business rules are satisfied then processing continues.
1077 --
1078 -- Post Failure:
1079 -- If the above business rules then an application error will be raised and
1080 -- processing is terminated.
1081 --
1082 -- Access Status:
1083 -- Internal Table Handler Use Only.
1084 --
1085 -- {End Of Comments}
1086 -- ----------------------------------------------------------------------------
1087 procedure chk_leaving_reason
1088 --
1089 (p_leaving_reason in per_periods_of_service.leaving_reason%TYPE,
1090 p_effective_date in date,
1091 p_object_version_number in
1092 per_periods_of_service.object_version_number%TYPE,
1093 p_period_of_service_id in
1094 per_periods_of_service.period_of_service_id%TYPE) is
1095 --
1096 l_api_updating boolean;
1097 l_proc varchar2(72) := g_package||'chk_leaving_reason';
1098 l_rec per_pds_shd.g_rec_type;
1099 --
1100 begin
1101 hr_utility.set_location('Entering:'|| l_proc, 1);
1102 --
1103 hr_api.mandatory_arg_error
1104 (p_api_name => l_proc
1105 ,p_argument => 'effective date'
1106 ,p_argument_value => p_effective_date
1107 );
1108 --
1109 -- Check to see if record updated.
1110 --
1111 l_api_updating := per_pds_shd.api_updating
1112 (p_period_of_service_id => p_period_of_service_id
1113 ,p_object_version_number => p_object_version_number);
1114 --
1115 hr_utility.set_location(l_proc, 2);
1116 --
1120 --
1117 if l_api_updating
1118 and p_leaving_reason is not null
1119 then
1121 if nvl(per_pds_shd.g_old_rec.leaving_reason, hr_api.g_varchar2) <>
1122 nvl(p_leaving_reason, hr_api.g_varchar2)
1123 then
1124 --
1125 -- Bug 1472162.
1126 --
1127 -- if hr_api.not_exists_in_hr_lookups
1128 if hr_api.not_exists_in_leg_lookups
1129 (p_effective_date => p_effective_date
1130 ,p_lookup_type => 'LEAV_REAS'
1131 ,p_lookup_code => p_leaving_reason
1132 ) then
1133 -- Error - Invalid Leaving Reason
1134 hr_utility.set_location(l_proc, 3);
1135 hr_utility.set_message(801,'HR_7485_PDS_INV_LR');
1136 hr_utility.raise_error;
1137 end if;
1138 --
1139 end if;
1140 --
1141 else
1142 --
1143 -- Not updating => inserting.
1144 --
1145 if not (p_leaving_reason is null)
1146 then
1147 -- CHK_LEAVING_REASON / b
1148 --
1149 hr_utility.set_message(801,'HR_7489_PDS_INV_LR_BLANK');
1150 hr_utility.raise_error;
1151 end if;
1152 --
1153 end if;
1154 --
1155 hr_utility.set_location(' Leaving:'|| l_proc, 3);
1156 end chk_leaving_reason;
1157 --
1158 -- ---------------------------------------------------------------------------
1159 -- |--------------------< chk_notified_termination_date >--------------------|
1160 -- ---------------------------------------------------------------------------
1161 --
1162 -- Description:
1163 -- Validates that the following business rules are met:
1164 -- a) Must be null (I)
1165 -- b) Must be >= DATE_START (I,U)
1166 --
1167 -- Pre-conditions:
1168 -- date_start and period_of_service_id have been successfully validated
1169 -- separately.
1170 --
1171 -- In Arguments:
1172 -- p_date_start
1173 -- p_notified_termination_date
1174 -- p_object_version_number
1175 -- p_period_of_service_id
1176 --
1177 -- Post Success:
1178 -- If the above business rules are satisfied then processing continues.
1179 --
1180 -- Post Failure:
1181 -- If the above business rules then an application error will be raised and
1182 -- processing is terminated.
1183 --
1184 -- Access Status:
1185 -- Internal Table Handler Use Only.
1186 --
1187 -- {End Of Comments}
1188 -- ----------------------------------------------------------------------------
1189 procedure chk_notified_termination_date
1190 --
1191 (p_date_start in per_periods_of_service.date_start%TYPE,
1192 p_notified_termination_date in
1193 per_periods_of_service.notified_termination_date%TYPE,
1194 p_object_version_number in
1195 per_periods_of_service.object_version_number%TYPE,
1196 p_period_of_service_id in
1197 per_periods_of_service.period_of_service_id%TYPE) is
1198 --
1199 l_api_updating boolean;
1200 l_proc varchar2(72) := g_package||'chk_notified_termination_date';
1201 --
1202 begin
1203 hr_utility.set_location('Entering:'|| l_proc, 1);
1204 --
1205 -- Check mandatory parameters have been set
1206 --
1207 hr_api.mandatory_arg_error
1208 (p_api_name => l_proc
1209 ,p_argument => 'date_start'
1210 ,p_argument_value => p_date_start
1211 );
1212 --
1213 hr_utility.set_location(l_proc, 2);
1214 --
1215 -- 70.1 change a start.
1216 --
1217 if not (nvl(p_notified_termination_date, hr_api.g_eot) >=
1218 nvl(p_date_start, hr_api.g_date))
1219 --
1220 -- 70.1 change a end.
1221 --
1222 then
1223 -- CHK_NOTIFIED_TERMINATION_DATE / b
1224 --
1225 hr_utility.set_message(801,'HR_7486_PDS_INV_NFT_DT_ST');
1226 hr_utility.raise_error;
1227 end if;
1228 --
1229 hr_utility.set_location(l_proc, 5);
1230 --
1231 -- Check to see if record updated.
1232 --
1233 l_api_updating := per_pds_shd.api_updating
1234 (p_period_of_service_id => p_period_of_service_id
1235 ,p_object_version_number => p_object_version_number);
1236 --
1237 if not l_api_updating
1238 then
1239 --
1240 -- Not updating => inserting
1241 --
1242 hr_utility.set_location(l_proc, 6);
1243 --
1244 if not (p_notified_termination_date is null)
1245 then
1246 -- CHK_NOTIFIED_TERMINATION_DATE / a
1247 --
1248 hr_utility.set_message(801,'HR_7487_PDS_INV_NFT_DT_BLANK');
1249 hr_utility.raise_error;
1250 end if;
1251 --
1252 end if;
1253 --
1254 hr_utility.set_location(' Leaving:'|| l_proc, 7);
1255 end chk_notified_termination_date;
1256 --
1257 -- ---------------------------------------------------------------------------
1258 -- |---------------------------< chk_person_id >----------------------------|
1259 -- ---------------------------------------------------------------------------
1260 --
1261 -- Description:
1262 -- Validates that the following business rules are met:
1263 -- a) Mandatory (I,U)
1264 -- b) UPDATE not allowed (U)
1265 -- c) Must not be the same as TERMINATION_ACCEPTED_PERSON_ID (I,U)
1266 -- NB: The unique combination of person_id and date_start is validated
1270 -- None
1267 -- via rule CHK_PERSON_ID_DATE_START.
1268 --
1269 -- Pre-conditions:
1271 --
1272 -- In Arguments:
1273 -- p_person_id
1274 -- p_termination_accepted_person
1275 --
1276 -- Post Success:
1277 -- If the above business rules are satisfied then processing continues.
1278 --
1279 -- Post Failure:
1280 -- If the above business rules then an application error will be raised and
1281 -- processing is terminated.
1282 --
1283 -- Access Status:
1284 -- Internal Table Handler Use Only.
1285 --
1286 -- {End Of Comments}
1287 -- ----------------------------------------------------------------------------
1288 procedure chk_person_id
1289 --
1290 (p_person_id in per_periods_of_service.person_id%TYPE,
1291 p_termination_accepted_person in
1292 per_periods_of_service.termination_accepted_person_id%TYPE) is
1293 --
1294 l_proc varchar2(72) := g_package||'chk_person_id';
1295 l_rec per_pds_shd.g_rec_type;
1296 --
1297 begin
1298 hr_utility.set_location('Entering:'|| l_proc, 1);
1299 --
1300 -- CHK_PERSON_ID / a
1301 --
1302 hr_api.mandatory_arg_error
1303 (p_api_name => l_proc
1304 ,p_argument => 'person_id'
1305 ,p_argument_value => p_person_id
1306 );
1307 hr_utility.set_location(l_proc, 2);
1308 --
1309 if not (p_person_id <>
1310 nvl(p_termination_accepted_person, hr_api.g_number))
1311 then
1312 -- CHK_PERSON_ID / c
1313 --
1314 hr_utility.set_message(801,'HR_7488_PDS_INV_TAP_ID');
1315 hr_utility.raise_error;
1316 end if;
1317 --
1318 hr_utility.set_location(' Leaving:'|| l_proc, 4);
1319 end chk_person_id;
1320 --
1321 -- ---------------------------------------------------------------------------
1322 -- |-----------------------< chk_person_id_date_start >----------------------|
1323 -- ---------------------------------------------------------------------------
1324 --
1325 -- Description:
1326 -- Validates that the following business rules are met:
1327 -- a) PERSON_ID and DATE_START combination must be unique (I)
1328 -- b) PERSON_ID and DATE_START combination must exist in
1329 -- PER_ALL_PEOPLE_F where PERSON_SYSTEM_TYPE in ('EMP', 'EMP_APL')
1330 -- (I,U)
1331 --
1332 -- Pre-conditions:
1333 -- person_id and date_start have been successfully validated separately.
1334 --
1335 -- In Arguments:
1336 -- p_date_start
1337 -- p_person_id
1338 --
1339 -- Post Success:
1340 -- If the above business rules are satisfied then processing continues.
1341 --
1342 -- Post Failure:
1343 -- If the above business rules then an application error will be raised and
1344 -- processing is terminated.
1345 --
1346 -- Access Status:
1347 -- Internal Table Handler Use Only.
1348 --
1349 -- {End Of Comments}
1350 -- ----------------------------------------------------------------------------
1351 procedure chk_person_id_date_start
1352 --
1353 (p_date_start in per_periods_of_service.date_start%TYPE,
1354 p_object_version_number in per_periods_of_service.object_version_number%TYPE,
1355 p_period_of_service_id in per_periods_of_service.period_of_service_id%TYPE,
1356 p_person_id in per_periods_of_service.person_id%TYPE) is
1357 --
1358 l_api_updating boolean;
1359 l_exists varchar2(1);
1360 l_proc varchar2(72) := g_package||'chk_person_id_date_start';
1361 --
1362 cursor csr_new_pers_date is
1363 select null
1364 from per_periods_of_service pds
1365 where pds.person_id = p_person_id
1366 and pds.date_start = p_date_start;
1367 --
1368 cursor csr_valid_pers_date is
1369 select null
1370 from per_all_people_f p,
1371 per_person_types pt
1372 where p.person_id = p_person_id
1373 and p.effective_start_date = p_date_start
1374 and pt.person_type_id = p.person_type_id
1375 and pt.system_person_type in ('EMP', 'EMP_APL');
1376 --
1377 begin
1378 hr_utility.set_location('Entering:'|| l_proc, 1);
1379 --
1380 -- Check mandatory parameters have been set
1381 --
1382 hr_api.mandatory_arg_error
1383 (p_api_name => l_proc
1384 ,p_argument => 'person_id'
1385 ,p_argument_value => p_person_id
1386 );
1387 hr_utility.set_location(l_proc, 4);
1388 --
1389 hr_api.mandatory_arg_error
1390 (p_api_name => l_proc
1391 ,p_argument => 'date_start'
1392 ,p_argument_value => p_date_start
1393 );
1394 hr_utility.set_location(l_proc, 5);
1395 --
1396 -- Check to see if record updated.
1397 --
1398 l_api_updating := per_pds_shd.api_updating
1399 (p_period_of_service_id => p_period_of_service_id
1400 ,p_object_version_number => p_object_version_number);
1401 --
1402 if not l_api_updating
1403 then
1404 --
1405 -- Check that the Person ID and Date Start combination does not exist
1406 -- on PER_PERIODS_OF_SERVICE
1407 --
1408 hr_utility.set_location(l_proc, 6);
1409 --
1410 open csr_new_pers_date;
1411 --
1412 fetch csr_new_pers_date into l_exists;
1416 -- CHK_PERSON_ID_DATE_START / a
1413 --
1414 if csr_new_pers_date%FOUND
1415 then
1417 --
1418 close csr_new_pers_date;
1419 hr_utility.set_message(801, 'HR_6796_EMP_POS_EXISTS');
1420 hr_utility.raise_error;
1421 end if;
1422 --
1423 close csr_new_pers_date;
1424 end if;
1425 --
1426 hr_utility.set_location(l_proc, 7);
1427 --
1428 -- Check that the Person ID and Date Start combination exists
1429 -- on PER_ALL_PEOPLE_F for system_person_type of 'EMP' or 'EMP_APL'.
1430 --
1431 open csr_valid_pers_date;
1432 --
1433 fetch csr_valid_pers_date into l_exists;
1434 --
1435 if csr_valid_pers_date%NOTFOUND
1436 then
1437 -- CHK_PERSON_ID_DATE_START / b
1438 --
1439 close csr_valid_pers_date;
1440 hr_utility.set_message(801, 'HR_7490_PDS_INV_P_DT_ST');
1441 hr_utility.raise_error;
1442 end if;
1443 --
1444 close csr_valid_pers_date;
1445 --
1446 hr_utility.set_location(' Leaving:'|| l_proc, 9);
1447 end chk_person_id_date_start;
1448 --
1449 -- ---------------------------------------------------------------------------
1450 -- |-------------------< chk_projected_termination_date >--------------------|
1451 -- ---------------------------------------------------------------------------
1452 --
1453 -- Description:
1454 -- Validates that the following business rules are met:
1455 -- a) Must be null (I)
1456 -- b) Must be >= DATE_START (I,U)
1457 --
1458 -- Pre-conditions:
1459 -- date_start and period_of_service_id have been successfully validated
1460 -- separately.
1461 --
1462 -- In Arguments:
1463 -- p_date_start
1464 -- p_object_version_number
1465 -- p_period_of_service_id
1466 -- p_projected_termination_date
1467 --
1468 -- Post Success:
1469 -- If the above business rules are satisfied then processing continues.
1470 --
1471 -- Post Failure:
1472 -- If the above business rules then an application error will be raised and
1473 -- processing is terminated.
1474 --
1475 -- Access Status:
1476 -- Internal Table Handler Use Only.
1477 --
1478 -- {End Of Comments}
1479 -- ----------------------------------------------------------------------------
1480 procedure chk_projected_termination_date
1481 --
1482 (p_date_start in per_periods_of_service.date_start%TYPE,
1483 p_object_version_number in
1484 per_periods_of_service.object_version_number%TYPE,
1485 p_period_of_service_id in
1486 per_periods_of_service.period_of_service_id%TYPE,
1487 p_projected_termination_date in
1488 per_periods_of_service.projected_termination_date%TYPE) is
1489 --
1490 l_api_updating boolean;
1491 l_proc varchar2(72) := g_package||'chk_projected_termination_date';
1492 --
1493 begin
1494 hr_utility.set_location('Entering:'|| l_proc, 1);
1495 --
1496 -- Check mandatory parameters have been set
1497 --
1498 hr_api.mandatory_arg_error
1499 (p_api_name => l_proc
1500 ,p_argument => 'date_start'
1501 ,p_argument_value => p_date_start
1502 );
1503 --
1504 hr_utility.set_location(l_proc, 2);
1505 --
1506 -- 70.1 change a start.
1507 --
1508 if not (nvl(p_projected_termination_date, hr_api.g_eot) >=
1509 nvl(p_date_start, hr_api.g_date))
1510 --
1511 -- 70.1 change a end.
1512 --
1513 then
1514 -- CHK_PROJECTED_TERMINATION_DATE / b
1515 --
1516 hr_utility.set_message(801,'HR_7491_PDS_INV_PJT_DT_ST');
1517 hr_utility.raise_error;
1518 end if;
1519 --
1520 hr_utility.set_location(l_proc, 5);
1521 --
1522 -- Check to see if record updated.
1523 --
1524 l_api_updating := per_pds_shd.api_updating
1525 (p_period_of_service_id => p_period_of_service_id
1526 ,p_object_version_number => p_object_version_number);
1527 --
1528 if not l_api_updating
1529 then
1530 --
1531 hr_utility.set_location(l_proc, 6);
1532 --
1533 if not (p_projected_termination_date is null)
1534 then
1535 -- CHK_PROJECTED_TERMINATION_DATE / a
1536 --
1537 hr_utility.set_message(801,'HR_7499_PDS_INV_PJT_DT_BLANK');
1538 hr_utility.raise_error;
1539 end if;
1540 --
1541 end if;
1542 --
1543 hr_utility.set_location(' Leaving:'|| l_proc, 7);
1544 end chk_projected_termination_date;
1545 --
1546 -- ---------------------------------------------------------------------------
1547 -- |-------------------< chk_termination_accepted_pers >---------------------|
1548 -- ---------------------------------------------------------------------------
1549 --
1550 -- Description:
1551 -- Validates that the following business rules are met:
1552 -- a) Must exist in PER_ALL_PEOPLE_F for the ACCEPTED_TERMINATION_DATE
1553 -- (U)
1554 -- b) Must not be the same as PERSON_ID (I,U)
1555 -- c) Must be null (I)
1556 --
1557 -- Pre-conditions:
1558 -- accepted_termination_date, person_id and period_of_service_id have been
1559 -- successfully validated separately.
1560 --
1561 -- In Arguments:
1562 -- p_accepted_termination_date
1566 -- p_termination_accepted_person
1563 -- p_object_version_number
1564 -- p_period_of_service_id
1565 -- p_person_id
1567 -- p_effective_date
1568 --
1569 -- Post Success:
1570 -- If the above business rules are satisfied then processing continues.
1571 --
1572 -- Post Failure:
1573 -- If the above business rules then an application error will be raised and
1574 -- processing is terminated.
1575 --
1576 procedure chk_termination_accepted_pers
1577 --
1578 (p_accepted_termination_date in per_periods_of_service.date_start%TYPE,
1579 p_object_version_number in per_periods_of_service.object_version_number%TYPE,
1580 p_period_of_service_id in per_periods_of_service.period_of_service_id%TYPE,
1581 p_person_id in per_periods_of_service.person_id%TYPE,
1582 p_termination_accepted_person in per_periods_of_service.termination_accepted_person_id%TYPE,
1583 p_effective_date in date) is
1584 --
1585 l_api_updating boolean;
1586 l_exists varchar2(1);
1587 l_proc varchar2(72) := g_package||'chk_termination_accepted_pers';
1588 --
1589 --
1590 --Bug# 2810608 Start here
1591 -- Desciption : Changed the subquery table from per_people_f to per_all_people_f
1592 -- so that the termination accepted by person is validated from all employees
1593 --
1594 cursor csr_valid_term_person is
1595 select null
1596 from per_all_people_f per
1597 where per.person_id = p_termination_accepted_person
1598 and nvl(p_accepted_termination_date, p_effective_date) >=
1599 (select min(per2.effective_start_date)
1600 from per_all_people_f per2
1601 where per2.person_id = per.person_id)
1602 and nvl(p_accepted_termination_date, p_effective_date) <=
1603 (select max(per3.effective_end_date)
1604 from per_all_people_f per3
1605 where per3.person_id = per.person_id);
1606 --
1607 -- Bug# 2810608 End here
1608 --
1609 begin
1610 hr_utility.set_location('Entering:'|| l_proc, 1);
1611 --
1612 -- Check mandatory parameters have been set
1613 --
1614 hr_api.mandatory_arg_error
1615 (p_api_name => l_proc
1616 ,p_argument => 'person_id'
1617 ,p_argument_value => p_person_id
1618 );
1619 --
1620 hr_utility.set_location(l_proc, 5);
1621 --
1622 -- Check to see if record updated.
1623 --
1624 l_api_updating := per_pds_shd.api_updating
1625 (p_period_of_service_id => p_period_of_service_id
1626 ,p_object_version_number => p_object_version_number);
1627 --
1628 if l_api_updating
1629 then
1630 --
1631 if (nvl(per_pds_shd.g_old_rec.termination_accepted_person_id, -- Bug # 2282955. This right hand side part of the condtion
1632 hr_api.g_number) <> -- is modified from nvl(p_termination_acceted_person,
1633 nvl(p_termination_accepted_person, nvl( -- hr_api.g_number) to nvl(p_termination_acceted_person,
1634 per_pds_shd.g_old_rec.termination_accepted_person_id,hr_api.g_number))) -- nvl(per_pds_shd.g_old_rec.termination_accepted_person_id,
1635 then -- hr_api.g_number))
1636 --
1637 -- CHK_TERMINATION_ACCEPTED_PERSON_ID / b
1638 --
1639 if (nvl(p_termination_accepted_person, hr_api.g_number) =
1640 per_pds_shd.g_old_rec.person_id)
1641 then
1642 hr_utility.set_message(801,'HR_7488_PDS_INV_TAP_ID');
1643 hr_utility.raise_error;
1644 end if;
1645 --
1646 hr_utility.set_location(l_proc, 7);
1647 --
1648 open csr_valid_term_person;
1649 fetch csr_valid_term_person into l_exists;
1650 --
1651 if csr_valid_term_person%NOTFOUND then
1652 --
1653 -- CHK_TERMINATION_ACCEPTED_PERSON_ID / a
1654 --
1655 close csr_valid_term_person;
1656 hr_utility.set_message(801,'HR_7500_PDS_INV_TAP_ID');
1657 hr_utility.raise_error;
1658 end if;
1659 --
1660 close csr_valid_term_person;
1661 --
1662 hr_utility.set_location(l_proc, 9);
1663 --
1664 end if;
1665 --
1666 else
1667 --
1668 -- Not updating => inserting.
1669 --
1670 hr_utility.set_location(l_proc, 13);
1671 --
1672 if not (p_termination_accepted_person is null)
1673 then
1674 -- CHK_TERMINATION_ACCEPTED_PERSON_ID / c
1675 --
1676 hr_utility.set_message(801,'HR_7501_PDS_INV_TAP_BLANK');
1677 hr_utility.raise_error;
1678 end if;
1679 --
1680 end if;
1681 --
1682 hr_utility.set_location(' Leaving:'|| l_proc, 15);
1683 end chk_termination_accepted_pers;
1684 --
1685 -- ----------------------------------------------------------------------------
1686 -- |----------------------< check_non_updateable_args >-----------------------|
1687 -- ----------------------------------------------------------------------------
1688 -- {Start Of Comments}
1689 --
1690 -- Description:
1691 -- This procedure is used to ensure that non updatetable attributes have
1692 -- not been updated. If an attribute has been updated an error is generated.
1696 -- the database.
1693 --
1694 -- Pre Conditions:
1695 -- g_old_rec has been populated with details of the values currently in
1697 --
1698 -- In Arguments:
1699 -- p_rec has been populated with the updated values the user would like the
1700 -- record set to.
1701 --
1702 -- Post Success:
1703 -- Processing continues if all the non updateable attributes have not
1704 -- changed.
1705 --
1706 -- Post Failure:
1707 -- An application error is raised if any of the non updatable attributes
1708 -- (business_group_id, period_of_service_id, person_id)
1709 -- have been altered.
1710 --
1711 -- {End Of Comments}
1712 Procedure check_non_updateable_args
1713 --
1714 (p_rec in per_pds_shd.g_rec_type) is
1715 --
1716 l_proc varchar2(72) := g_package||'check_non_updateable_args';
1717 l_error exception;
1718 l_argument varchar2(30);
1719 --
1720 Begin
1721 hr_utility.set_location('Entering:'||l_proc, 5);
1722 --
1723 -- Only proceed with validation if a row exists for
1724 -- the current record in the HR Schema
1725 --
1726 if not per_pds_shd.api_updating
1727 (p_period_of_service_id => p_rec.period_of_service_id,
1728 p_object_version_number => p_rec.object_version_number)
1729 then
1730 --
1731 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1732 hr_utility.set_message_token('PROCEDURE', l_proc);
1733 hr_utility.set_message_token('STEP', '5');
1734 end if;
1735 --
1736 hr_utility.set_location(l_proc, 6);
1737 --
1738 if nvl(p_rec.business_group_id, hr_api.g_number) <>
1739 nvl(per_pds_shd.g_old_rec.business_group_id, hr_api.g_number)
1740 then
1741 -- CHK_BUSINESS_GROUP_ID / b
1742 --
1743 l_argument := 'business_group_id';
1744 raise l_error;
1745 end if;
1746 --
1747 hr_utility.set_location(l_proc, 7);
1748 --
1749 if nvl(p_rec.period_of_service_id, hr_api.g_number) <>
1750 nvl(per_pds_shd.g_old_rec.period_of_service_id, hr_api.g_number)
1751 then
1752 -- CHK_PERIOD_OF_SERVICE_ID / c
1753 --
1754 l_argument := 'period_of_service_id';
1755 raise l_error;
1756 end if;
1757 --
1758 hr_utility.set_location(l_proc, 8);
1759 --
1760 if nvl(p_rec.person_id, hr_api.g_number) <>
1761 nvl(per_pds_shd.g_old_rec.person_id, hr_api.g_number)
1762 then
1763 -- CHK_PERSON_ID / b
1764 --
1765 l_argument := 'person_id';
1766 raise l_error;
1767 end if;
1768 --
1769 hr_utility.set_location(l_proc, 9);
1770 --
1771 exception
1772 when l_error
1773 then
1774 --
1775 hr_api.argument_changed_error
1776 (p_api_name => l_proc
1777 ,p_argument => l_argument);
1778 when others then
1779 raise;
1780 hr_utility.set_location(' Leaving:'||l_proc, 12);
1781 end check_non_updateable_args;
1782 --
1783 -- ---------------------------------------------------------------------------
1784 -- |----------------------------< chk_df >---------------------------------|
1785 -- ---------------------------------------------------------------------------
1786 --
1787 -- Description:
1788 -- Calls the descriptive flex validation (ins_or_upd_descflex_attribs)
1789 -- if either the attribute_category or attribute1..20 have changed.
1790 --
1791 -- Pre-conditions:
1792 -- All other columns have been validated. Must be called as the
1793 -- last step from insert_validate and update_validate.
1794 --
1795 -- In Arguments:
1796 -- p_rec
1797 --
1798 -- Post Success:
1799 -- If the Descriptive Flexfield structure column and data values are
1800 -- all valid this procedure will end normally and processing will
1801 -- continue.
1802 --
1803 -- Post Failure:
1804 -- If an exception is raised within this procedure or lower
1805 -- procedure calls then it is raised through the normal exception
1806 -- handling mechanism.
1807 --
1808 -- Access Status:
1809 -- Internal Table Handler Use Only.
1810 -- ---------------------------------------------------------------------------
1811 procedure chk_df
1812 (p_rec in per_pds_shd.g_rec_type
1813 ,p_validate_df_flex in boolean default true) is
1814 --
1815 l_proc varchar2(72) := g_package||'chk_df';
1816 --
1817 begin
1818 --
1819 hr_utility.set_location('Entering:'||l_proc, 10);
1820 --
1821 -- if inserting and not required to validate flex data
1822 -- then ensure all flex data passed is null
1823 --
1824 If ((p_rec.period_of_service_id is null) and
1825 (not p_validate_df_flex)) then
1826 --
1827 hr_utility.set_location(l_proc, 15);
1828 --
1829 If (not ( (p_rec.attribute_category is null) and
1830 (p_rec.attribute1 is null) and
1831 (p_rec.attribute2 is null) and
1832 (p_rec.attribute3 is null) and
1833 (p_rec.attribute4 is null) and
1834 (p_rec.attribute5 is null) and
1835 (p_rec.attribute6 is null) and
1836 (p_rec.attribute7 is null) and
1840 (p_rec.attribute11 is null) and
1837 (p_rec.attribute8 is null) and
1838 (p_rec.attribute9 is null) and
1839 (p_rec.attribute10 is null) and
1841 (p_rec.attribute12 is null) and
1842 (p_rec.attribute13 is null) and
1843 (p_rec.attribute14 is null) and
1844 (p_rec.attribute15 is null) and
1845 (p_rec.attribute16 is null) and
1846 (p_rec.attribute17 is null) and
1847 (p_rec.attribute18 is null) and
1848 (p_rec.attribute19 is null) and
1849 (p_rec.attribute20 is null) ) )
1850 then
1851 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
1852 hr_utility.set_message_token('PROCEDURE','chk_df');
1853 hr_utility.set_message_token('STEP',1);
1854 hr_utility.raise_error;
1855 End if;
1856 End if;
1857 --
1858 --
1859 -- if ( updating and flex data has changed
1860 -- OR updating and all flex segments are NULL)
1861 -- OR ( inserting and required to validate flexdata)
1862 -- then validate flex data.
1863 --
1864 --
1865 If ( (p_rec.period_of_service_id is not null)
1866 and
1867 ( (nvl(per_pds_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1868 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1869 nvl(per_pds_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1870 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1871 nvl(per_pds_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1872 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1873 nvl(per_pds_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1874 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1875 nvl(per_pds_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1876 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1877 nvl(per_pds_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1878 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1879 nvl(per_pds_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1880 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1881 nvl(per_pds_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1882 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1883 nvl(per_pds_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1884 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1885 nvl(per_pds_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1886 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1887 nvl(per_pds_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1888 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1889 nvl(per_pds_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1890 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1891 nvl(per_pds_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1892 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1893 nvl(per_pds_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1894 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1895 nvl(per_pds_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1896 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1897 nvl(per_pds_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1898 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1899 nvl(per_pds_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1900 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1901 nvl(per_pds_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1902 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1903 nvl(per_pds_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1904 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1905 nvl(per_pds_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1906 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1907 nvl(per_pds_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1908 nvl(p_rec.attribute20, hr_api.g_varchar2)
1909 )
1910 or
1911 (
1912 -- Added the check for date_start and adjusted_svc_date for fix
1913 -- of #1577252
1914 ( nvl(per_pds_shd.g_old_rec.adjusted_svc_date, hr_api.g_date) =
1915 nvl(p_rec.adjusted_svc_date, hr_api.g_date) ) and
1916 ( nvl(per_pds_shd.g_old_rec.date_start, hr_api.g_date) =
1917 nvl(p_rec.date_start, hr_api.g_date) ) and
1918 (p_rec.attribute_category is null) and
1919 (p_rec.attribute1 is null) and
1920 (p_rec.attribute2 is null) and
1921 (p_rec.attribute3 is null) and
1922 (p_rec.attribute4 is null) and
1923 (p_rec.attribute5 is null) and
1924 (p_rec.attribute6 is null) and
1925 (p_rec.attribute7 is null) and
1926 (p_rec.attribute8 is null) and
1927 (p_rec.attribute9 is null) and
1928 (p_rec.attribute10 is null) and
1929 (p_rec.attribute11 is null) and
1933 (p_rec.attribute15 is null) and
1930 (p_rec.attribute12 is null) and
1931 (p_rec.attribute13 is null) and
1932 (p_rec.attribute14 is null) and
1934 (p_rec.attribute16 is null) and
1935 (p_rec.attribute17 is null) and
1936 (p_rec.attribute18 is null) and
1937 (p_rec.attribute19 is null) and
1938 (p_rec.attribute20 is null)
1939 )
1940 ))
1941 -- or inserting and required to validate flex
1942 or
1943 ((p_rec.period_of_service_id is null) and
1944 (p_validate_df_flex))
1945 then
1946 --
1947 hr_utility.set_location(l_proc, 20);
1948 --
1949 -- validate flex segment values
1950 --
1951 hr_dflex_utility.ins_or_upd_descflex_attribs(
1952 p_appl_short_name => 'PER'
1953 ,p_descflex_name => 'PER_PERIODS_OF_SERVICE'
1954 ,p_attribute_category => p_rec.attribute_category
1955 ,p_attribute1_name => 'ATTRIBUTE1'
1956 ,p_attribute1_value => p_rec.attribute1
1957 ,p_attribute2_name => 'ATTRIBUTE2'
1958 ,p_attribute2_value => p_rec.attribute2
1959 ,p_attribute3_name => 'ATTRIBUTE3'
1960 ,p_attribute3_value => p_rec.attribute3
1961 ,p_attribute4_name => 'ATTRIBUTE4'
1962 ,p_attribute4_value => p_rec.attribute4
1963 ,p_attribute5_name => 'ATTRIBUTE5'
1964 ,p_attribute5_value => p_rec.attribute5
1965 ,p_attribute6_name => 'ATTRIBUTE6'
1966 ,p_attribute6_value => p_rec.attribute6
1967 ,p_attribute7_name => 'ATTRIBUTE7'
1968 ,p_attribute7_value => p_rec.attribute7
1969 ,p_attribute8_name => 'ATTRIBUTE8'
1970 ,p_attribute8_value => p_rec.attribute8
1971 ,p_attribute9_name => 'ATTRIBUTE9'
1972 ,p_attribute9_value => p_rec.attribute9
1973 ,p_attribute10_name => 'ATTRIBUTE10'
1974 ,p_attribute10_value => p_rec.attribute10
1975 ,p_attribute11_name => 'ATTRIBUTE11'
1976 ,p_attribute11_value => p_rec.attribute11
1977 ,p_attribute12_name => 'ATTRIBUTE12'
1978 ,p_attribute12_value => p_rec.attribute12
1979 ,p_attribute13_name => 'ATTRIBUTE13'
1980 ,p_attribute13_value => p_rec.attribute13
1981 ,p_attribute14_name => 'ATTRIBUTE14'
1982 ,p_attribute14_value => p_rec.attribute14
1983 ,p_attribute15_name => 'ATTRIBUTE15'
1984 ,p_attribute15_value => p_rec.attribute15
1985 ,p_attribute16_name => 'ATTRIBUTE16'
1986 ,p_attribute16_value => p_rec.attribute16
1987 ,p_attribute17_name => 'ATTRIBUTE17'
1988 ,p_attribute17_value => p_rec.attribute17
1989 ,p_attribute18_name => 'ATTRIBUTE18'
1990 ,p_attribute18_value => p_rec.attribute18
1991 ,p_attribute19_name => 'ATTRIBUTE19'
1992 ,p_attribute19_value => p_rec.attribute19
1993 ,p_attribute20_name => 'ATTRIBUTE20'
1994 ,p_attribute20_value => p_rec.attribute20
1995 );
1996 End if;
1997 --
1998 hr_utility.set_location(' Leaving:'||l_proc, 30);
1999 --
2000 end chk_df;
2001 --
2002 -- -----------------------------------------------------------------------
2003 -- |------------------------------< chk_ddf >----------------------------|
2004 -- -----------------------------------------------------------------------
2005 --
2006 -- Description:
2007 -- Validates the all Developer Descriptive Flexfield values.
2008 --
2009 -- Pre-conditions:
2010 -- All other columns have been validated. Must be called as the
2011 -- second last step from insert_validate and update_validate.
2012 -- Before any Descriptive Flexfield (chk_df) calls.
2013 --
2014 -- In Arguments:
2015 -- p_rec
2016 --
2017 -- Post Success:
2018 -- If the Developer Descriptive Flexfield structure column and data
2019 -- values are all valid this procedure will end normally and
2020 -- processing will continue.
2021 --
2022 -- Post Failure:
2023 -- If the DDF structure column value or any of the data values
2024 -- are invalid then an application error is raised as
2025 -- a PL/SQL exception.
2026 --
2027 -- Access Status:
2028 -- Internal Row Handler Use Only.
2029 --
2030 -- {End Of Comments}
2031 -- ----------------------------------------------------------------------------
2032 --
2033 procedure chk_ddf
2034 (p_rec in per_pds_shd.g_rec_type) is
2035 --
2036 l_proc varchar2(72) := g_package||'chk_ddf';
2037 l_error exception;
2038 --
2039 Begin
2040 hr_utility.set_location('Entering:'||l_proc, 5);
2041 --
2042 -- Check if the row is being inserted or updated and a
2043 -- value has changed
2044 --
2045 if (p_rec.period_of_service_id is null) or
2046 ((p_rec.period_of_service_id is not null) and
2050 nvl(p_rec.pds_information1, hr_api.g_varchar2) or
2047 nvl(per_pds_shd.g_old_rec.pds_information_category, hr_api.g_varchar2) <>
2048 nvl(p_rec.pds_information_category, hr_api.g_varchar2) or
2049 nvl(per_pds_shd.g_old_rec.pds_information1, hr_api.g_varchar2) <>
2051 nvl(per_pds_shd.g_old_rec.pds_information2, hr_api.g_varchar2) <>
2052 nvl(p_rec.pds_information2, hr_api.g_varchar2) or
2053 nvl(per_pds_shd.g_old_rec.pds_information3, hr_api.g_varchar2) <>
2054 nvl(p_rec.pds_information3, hr_api.g_varchar2) or
2055 nvl(per_pds_shd.g_old_rec.pds_information4, hr_api.g_varchar2) <>
2056 nvl(p_rec.pds_information4, hr_api.g_varchar2) or
2057 nvl(per_pds_shd.g_old_rec.pds_information5, hr_api.g_varchar2) <>
2058 nvl(p_rec.pds_information5, hr_api.g_varchar2) or
2059 nvl(per_pds_shd.g_old_rec.pds_information6, hr_api.g_varchar2) <>
2060 nvl(p_rec.pds_information6, hr_api.g_varchar2) or
2061 nvl(per_pds_shd.g_old_rec.pds_information7, hr_api.g_varchar2) <>
2062 nvl(p_rec.pds_information7, hr_api.g_varchar2) or
2063 nvl(per_pds_shd.g_old_rec.pds_information8, hr_api.g_varchar2) <>
2064 nvl(p_rec.pds_information8, hr_api.g_varchar2) or
2065 nvl(per_pds_shd.g_old_rec.pds_information9, hr_api.g_varchar2) <>
2066 nvl(p_rec.pds_information9, hr_api.g_varchar2) or
2067 nvl(per_pds_shd.g_old_rec.pds_information10, hr_api.g_varchar2) <>
2068 nvl(p_rec.pds_information10, hr_api.g_varchar2) or
2069 nvl(per_pds_shd.g_old_rec.pds_information11, hr_api.g_varchar2) <>
2070 nvl(p_rec.pds_information11, hr_api.g_varchar2) or
2071 nvl(per_pds_shd.g_old_rec.pds_information12, hr_api.g_varchar2) <>
2072 nvl(p_rec.pds_information12, hr_api.g_varchar2) or
2073 nvl(per_pds_shd.g_old_rec.pds_information13, hr_api.g_varchar2) <>
2074 nvl(p_rec.pds_information13, hr_api.g_varchar2) or
2075 nvl(per_pds_shd.g_old_rec.pds_information14, hr_api.g_varchar2) <>
2076 nvl(p_rec.pds_information14, hr_api.g_varchar2) or
2077 nvl(per_pds_shd.g_old_rec.pds_information15, hr_api.g_varchar2) <>
2078 nvl(p_rec.pds_information15, hr_api.g_varchar2) or
2079 nvl(per_pds_shd.g_old_rec.pds_information16, hr_api.g_varchar2) <>
2080 nvl(p_rec.pds_information16, hr_api.g_varchar2) or
2081 nvl(per_pds_shd.g_old_rec.pds_information17, hr_api.g_varchar2) <>
2082 nvl(p_rec.pds_information17, hr_api.g_varchar2) or
2083 nvl(per_pds_shd.g_old_rec.pds_information18, hr_api.g_varchar2) <>
2084 nvl(p_rec.pds_information18, hr_api.g_varchar2) or
2085 nvl(per_pds_shd.g_old_rec.pds_information19, hr_api.g_varchar2) <>
2086 nvl(p_rec.pds_information19, hr_api.g_varchar2) or
2087 nvl(per_pds_shd.g_old_rec.pds_information20, hr_api.g_varchar2) <>
2088 nvl(p_rec.pds_information20, hr_api.g_varchar2) or
2089 nvl(per_pds_shd.g_old_rec.pds_information21, hr_api.g_varchar2) <>
2090 nvl(p_rec.pds_information21, hr_api.g_varchar2) or
2091 nvl(per_pds_shd.g_old_rec.pds_information22, hr_api.g_varchar2) <>
2092 nvl(p_rec.pds_information22, hr_api.g_varchar2) or
2093 nvl(per_pds_shd.g_old_rec.pds_information23, hr_api.g_varchar2) <>
2094 nvl(p_rec.pds_information23, hr_api.g_varchar2) or
2095 nvl(per_pds_shd.g_old_rec.pds_information24, hr_api.g_varchar2) <>
2096 nvl(p_rec.pds_information24, hr_api.g_varchar2) or
2097 nvl(per_pds_shd.g_old_rec.pds_information25, hr_api.g_varchar2) <>
2098 nvl(p_rec.pds_information25, hr_api.g_varchar2) or
2099 nvl(per_pds_shd.g_old_rec.pds_information26, hr_api.g_varchar2) <>
2100 nvl(p_rec.pds_information26, hr_api.g_varchar2) or
2101 nvl(per_pds_shd.g_old_rec.pds_information27, hr_api.g_varchar2) <>
2102 nvl(p_rec.pds_information27, hr_api.g_varchar2) or
2103 nvl(per_pds_shd.g_old_rec.pds_information28, hr_api.g_varchar2) <>
2104 nvl(p_rec.pds_information28, hr_api.g_varchar2) or
2105 nvl(per_pds_shd.g_old_rec.pds_information29, hr_api.g_varchar2) <>
2106 nvl(p_rec.pds_information29, hr_api.g_varchar2) or
2107 nvl(per_pds_shd.g_old_rec.pds_information30, hr_api.g_varchar2) <>
2108 nvl(p_rec.pds_information30, hr_api.g_varchar2)) then
2109 --
2110 hr_dflex_utility.ins_or_upd_descflex_attribs
2111 (p_appl_short_name => 'PER'
2112 ,p_descflex_name => 'PER_PDS_DEVELOPER_DF'
2113 ,p_attribute_category => p_rec.pds_information_category
2114 ,p_attribute1_name => 'PDS_INFORMATION1'
2115 ,p_attribute1_value => p_rec.pds_information1
2116 ,p_attribute2_name => 'PDS_INFORMATION2'
2117 ,p_attribute2_value => p_rec.pds_information2
2118 ,p_attribute3_name => 'PDS_INFORMATION3'
2119 ,p_attribute3_value => p_rec.pds_information3
2120 ,p_attribute4_name => 'PDS_INFORMATION4'
2121 ,p_attribute4_value => p_rec.pds_information4
2122 ,p_attribute5_name => 'PDS_INFORMATION5'
2123 ,p_attribute5_value => p_rec.pds_information5
2124 ,p_attribute6_name => 'PDS_INFORMATION6'
2125 ,p_attribute6_value => p_rec.pds_information6
2126 ,p_attribute7_name => 'PDS_INFORMATION7'
2127 ,p_attribute7_value => p_rec.pds_information7
2128 ,p_attribute8_name => 'PDS_INFORMATION8'
2129 ,p_attribute8_value => p_rec.pds_information8
2130 ,p_attribute9_name => 'PDS_INFORMATION9'
2131 ,p_attribute9_value => p_rec.pds_information9
2132 ,p_attribute10_name => 'PDS_INFORMATION10'
2133 ,p_attribute10_value => p_rec.pds_information10
2137 ,p_attribute12_value => p_rec.pds_information12
2134 ,p_attribute11_name => 'PDS_INFORMATION11'
2135 ,p_attribute11_value => p_rec.pds_information11
2136 ,p_attribute12_name => 'PDS_INFORMATION12'
2138 ,p_attribute13_name => 'PDS_INFORMATION13'
2139 ,p_attribute13_value => p_rec.pds_information13
2140 ,p_attribute14_name => 'PDS_INFORMATION14'
2141 ,p_attribute14_value => p_rec.pds_information14
2142 ,p_attribute15_name => 'PDS_INFORMATION15'
2143 ,p_attribute15_value => p_rec.pds_information15
2144 ,p_attribute16_name => 'PDS_INFORMATION16'
2145 ,p_attribute16_value => p_rec.pds_information16
2146 ,p_attribute17_name => 'PDS_INFORMATION17'
2147 ,p_attribute17_value => p_rec.pds_information17
2148 ,p_attribute18_name => 'PDS_INFORMATION18'
2149 ,p_attribute18_value => p_rec.pds_information18
2150 ,p_attribute19_name => 'PDS_INFORMATION19'
2151 ,p_attribute19_value => p_rec.pds_information19
2152 ,p_attribute20_name => 'PDS_INFORMATION20'
2153 ,p_attribute20_value => p_rec.pds_information20
2154 ,p_attribute21_name => 'PDS_INFORMATION21'
2155 ,p_attribute21_value => p_rec.pds_information21
2156 ,p_attribute22_name => 'PDS_INFORMATION22'
2157 ,p_attribute22_value => p_rec.pds_information22
2158 ,p_attribute23_name => 'PDS_INFORMATION23'
2159 ,p_attribute23_value => p_rec.pds_information23
2160 ,p_attribute24_name => 'PDS_INFORMATION24'
2161 ,p_attribute24_value => p_rec.pds_information24
2162 ,p_attribute25_name => 'PDS_INFORMATION25'
2163 ,p_attribute25_value => p_rec.pds_information25
2164 ,p_attribute26_name => 'PDS_INFORMATION26'
2165 ,p_attribute26_value => p_rec.pds_information26
2166 ,p_attribute27_name => 'PDS_INFORMATION27'
2167 ,p_attribute27_value => p_rec.pds_information27
2168 ,p_attribute28_name => 'PDS_INFORMATION28'
2169 ,p_attribute28_value => p_rec.pds_information28
2170 ,p_attribute29_name => 'PDS_INFORMATION29'
2171 ,p_attribute29_value => p_rec.pds_information29
2172 ,p_attribute30_name => 'PDS_INFORMATION30'
2173 ,p_attribute30_value => p_rec.pds_information30
2174 );
2175 --
2176 end if;
2177 --
2178 hr_utility.set_location(' Leaving:'||l_proc, 10);
2179 end chk_ddf;
2180 --
2181 -- --------------------------------------------------------------------------- -- ----------------------------------------------------------------------------
2182 -- |---------------------------< insert_validate >----------------------------|
2183 -- ----------------------------------------------------------------------------
2184 Procedure insert_validate(p_rec in per_pds_shd.g_rec_type
2185 ,p_effective_date in date
2186 ,p_validate_df_flex in boolean) is
2187 --
2188 l_proc varchar2(72) := g_package||'insert_validate';
2189 --
2190 Begin
2191 hr_utility.set_location('Entering:'||l_proc, 1);
2192 --
2193 -- Call all supporting business operations. Mapping to the appropriate
2194 -- Business Rules in perpds.bru is provided.
2195 --
2196 -- Validate business group id
2197 --
2198 -- Business Rule Mapping
2199 -- =====================
2200 -- CHK_BUSINESS_GROUP_ID / a,c
2201 --
2202 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2203 --
2204 hr_utility.set_location(l_proc, 2);
2205 --
2206 --
2207 -- Validate date start
2208 --
2209 -- Business Rule Mapping
2210 -- =====================
2211 -- CHK_DATE_START / a
2212 --
2213 per_pds_bus.chk_date_start(p_date_start => p_rec.date_start);
2214 --
2215 hr_utility.set_location(l_proc, 3);
2216 --
2217 --
2218 -- Validate person id
2219 --
2220 -- Business Rule Mapping
2221 -- =====================
2222 -- CHK_PERSON_ID / a,c
2223 --
2224 per_pds_bus.chk_person_id
2225 (p_person_id => p_rec.person_id,
2226 p_termination_accepted_person => p_rec.termination_accepted_person_id);
2227 --
2228 hr_utility.set_location(l_proc, 4);
2229 --
2230 --
2231 -- Validate date start
2232 --
2233 -- Business Rule Mapping
2234 -- =====================
2235 -- CHK_DATE_START / a
2236 --
2237 per_pds_bus.chk_date_start(p_date_start => p_rec.date_start);
2238 --
2239 hr_utility.set_location(l_proc, 5);
2240 --
2241 --
2242 -- Validate person id and date start combination
2243 --
2244 -- Business Rule Mapping
2245 -- =====================
2246 -- CHK_PERSON_ID_DATE_START / a,b
2247 --
2248 per_pds_bus.chk_person_id_date_start
2249 (p_period_of_service_id => p_rec.period_of_service_id,
2250 p_object_version_number => p_rec.object_version_number,
2251 p_person_id => p_rec.person_id,
2252 p_date_start => p_rec.date_start);
2253 --
2254 hr_utility.set_location(l_proc, 6);
2255 --
2256 --
2257 -- Validate accepted termination date
2258 --
2259 -- Business Rule Mapping
2260 -- =====================
2261 -- CHK_ACCEPTED_TERMINATION_DATE / a
2262 --
2263 per_pds_bus.chk_accepted_termination_date
2264 (p_accepted_termination_date => p_rec.accepted_termination_date);
2265 --
2269 -- Validate actual termination date
2266 hr_utility.set_location(l_proc, 7);
2267 --
2268 --
2270 --
2271 -- Business Rule Mapping
2272 -- =====================
2273 -- CHK_ACTUAL_TERMINATION_DATE / c
2274 --
2275 -- 70.3 change a start.
2276 --
2277 per_pds_bus.chk_actual_termination_date
2278 (p_actual_termination_date => p_rec.actual_termination_date
2279 ,p_date_start => p_rec.date_start
2280 ,p_last_standard_process_date => p_rec.last_standard_process_date
2281 ,p_object_version_number => p_rec.object_version_number
2282 ,p_period_of_service_id => p_rec.period_of_service_id
2283 ,p_person_id => p_rec.person_id);
2284 --
2285 -- 70.3 change a end.
2286 --
2287 hr_utility.set_location(l_proc, 8);
2288 --
2289 --
2290 -- Validate last standard process date
2291 --
2292 -- Business Rule Mapping
2293 -- =====================
2294 -- CHK_LAST_STANDARD_PROCESS_DATE / e
2295 --
2296 -- 70.3 change c start.
2297 --
2298 per_pds_bus.chk_last_standard_process_date
2299 (p_actual_termination_date => p_rec.actual_termination_date
2300 ,p_business_group_id => p_rec.business_group_id
2301 ,p_date_start => p_rec.date_start
2302 ,p_last_standard_process_date => p_rec.last_standard_process_date
2303 ,p_object_version_number => p_rec.object_version_number
2304 ,p_period_of_service_id => p_rec.period_of_service_id
2305 );
2306 hr_utility.set_location(l_proc, 9);
2307 --
2308 --
2309 -- Validate final process date
2310 --
2311 -- Business Rule Mapping
2312 -- =====================
2313 -- CHK_FINAL_PROCESS_DATE / e
2314 --
2315 per_pds_bus.chk_final_process_date
2316 (p_actual_termination_date => p_rec.actual_termination_date,
2317 p_date_start => p_rec.date_start,
2318 p_final_process_date => p_rec.final_process_date,
2319 p_last_standard_process_date => p_rec.last_standard_process_date,
2320 p_object_version_number => p_rec.object_version_number,
2321 p_period_of_service_id => p_rec.period_of_service_id);
2322 --
2323 --
2324 -- 70.3 change c end.
2325 --
2326 hr_utility.set_location(l_proc, 10);
2327 --
2328 --
2329 -- Validate leaving reason
2330 --
2331 -- Business Rule Mapping
2332 -- =====================
2333 -- CHK_LEAVING_REASON / b
2334 --
2335 per_pds_bus.chk_leaving_reason
2336 (p_leaving_reason => p_rec.leaving_reason,
2337 p_effective_date => p_effective_date,
2338 p_object_version_number => p_rec.object_version_number,
2339 p_period_of_service_id => p_rec.period_of_service_id);
2340 --
2341 hr_utility.set_location(l_proc, 11);
2342 --
2343 --
2344 -- Validate notified termination date
2345 --
2346 -- Business Rule Mapping
2347 -- =====================
2348 -- CHK_NOTIFIED_TERMINATION_DATE / a
2349 --
2350 per_pds_bus.chk_notified_termination_date
2351 (p_date_start => p_rec.date_start,
2352 p_notified_termination_date => p_rec.notified_termination_date,
2353 p_object_version_number => p_rec.object_version_number,
2354 p_period_of_service_id => p_rec.period_of_service_id);
2355 --
2356 hr_utility.set_location(l_proc, 12);
2357 --
2358 --
2359 -- Validate projected termination date
2360 --
2361 -- Business Rule Mapping
2362 -- =====================
2363 -- CHK_PROJECTED_TERMINATION_DATE / a,b
2364 --
2365 per_pds_bus.chk_projected_termination_date
2366 (p_date_start => p_rec.date_start,
2367 p_period_of_service_id => p_rec.period_of_service_id,
2368 p_object_version_number => p_rec.object_version_number,
2369 p_projected_termination_date => p_rec.projected_termination_date);
2370 --
2371 hr_utility.set_location(l_proc, 13);
2372 --
2373 --
2374 -- Validate termination accepted person id
2375 --
2376 -- Business Rule Mapping
2377 -- =====================
2378 -- CHK_TERMINATION_ACCEPTED_PERSON_ID / b,c
2379 --
2380 per_pds_bus.chk_termination_accepted_pers
2381 (p_accepted_termination_date => p_rec.accepted_termination_date,
2382 p_object_version_number => p_rec.object_version_number,
2383 p_period_of_service_id => p_rec.period_of_service_id,
2384 p_person_id => p_rec.person_id,
2385 p_termination_accepted_person => p_rec.termination_accepted_person_id,
2386 p_effective_date => p_effective_date);
2387 --
2388 -- Validate descriptive flex fields.
2389 --
2390 per_pds_bus.chk_ddf(p_rec => p_rec);
2391 --
2392 --
2393 -- Validate flex fields.
2394 --
2395 per_pds_bus.chk_df(p_rec => p_rec
2396 ,p_validate_df_flex => p_validate_df_flex);
2397 --
2398 --
2399 hr_utility.set_location(' Leaving:'||l_proc, 14);
2400 End insert_validate;
2401 --
2402 -- ----------------------------------------------------------------------------
2403 -- |---------------------------< update_validate >----------------------------|
2404 -- ----------------------------------------------------------------------------
2405 Procedure update_validate(p_rec in per_pds_shd.g_rec_type
2406 ,p_effective_date in date) is
2407 --
2408 l_proc varchar2(72) := g_package||'update_validate';
2409 --
2410 Begin
2411 hr_utility.set_location('Entering:'||l_proc, 1);
2412 --
2413 -- Check that the columns which cannot be updated have not changed.
2414 --
2415 -- Business Rule Mapping
2416 -- =====================
2417 -- CHK_BUSINESS_GROUP_ID / b
2418 -- CHK_PERIOD_OF_SERVICE_ID / c
2419 -- CHK_PERSON_ID / c
2420 --
2421 check_non_updateable_args(p_rec => p_rec);
2422 --
2423 hr_utility.set_location(l_proc, 2);
2424 --
2425 --
2426 -- Call all supporting business operations
2427 --
2428 -- Validate business group id
2429 --
2430 -- Business Rule Mapping
2431 -- =====================
2432 -- CHK_BUSINESS_GROUP_ID / a,c
2433 --
2434 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2435 --
2436 hr_utility.set_location(l_proc, 3);
2437 --
2438 --
2439 -- Validate date start
2440 --
2441 -- Business Rule Mapping
2442 -- =====================
2443 -- CHK_DATE_START / a
2444 --
2445 per_pds_bus.chk_date_start(p_date_start => p_rec.date_start);
2446 --
2447 hr_utility.set_location(l_proc, 4);
2448 --
2449 --
2450 -- Validate person id and date start combination
2451 --
2452 -- Business Rule Mapping
2453 -- =====================
2454 -- CHK_PERSON_ID_DATE_START / a,b
2455 --
2456 per_pds_bus.chk_person_id_date_start
2457 (p_period_of_service_id => p_rec.period_of_service_id,
2458 p_object_version_number => p_rec.object_version_number,
2459 p_person_id => p_rec.person_id,
2460 p_date_start => p_rec.date_start);
2461 --
2462 hr_utility.set_location(l_proc, 5);
2463 --
2464 --
2465 -- Validate actual termination date
2466 --
2467 -- Business Rule Mapping
2468 -- =====================
2469 -- CHK_ACTUAL_TERMINATION_DATE / a,b,d,e,f
2470 --
2471 -- 70.3 change a start.
2472 --
2473 per_pds_bus.chk_actual_termination_date
2474 (p_actual_termination_date => p_rec.actual_termination_date
2475 ,p_date_start => p_rec.date_start
2476 ,p_last_standard_process_date => p_rec.last_standard_process_date
2477 ,p_object_version_number => p_rec.object_version_number
2478 ,p_period_of_service_id => p_rec.period_of_service_id
2479 ,p_person_id => p_rec.person_id);
2480 --
2481 -- 70.3 change a end.
2482 --
2483 hr_utility.set_location(l_proc, 6);
2484 --
2485 --
2486 -- Validate last standard process date
2487 --
2488 -- Business Rule Mapping
2489 -- =====================
2490 --
2491 -- 70.3 change c start.
2492 --
2493 -- CHK_LAST_STANDARD_PROCESS_DATE / a,b,c,f,g,i
2494 --
2498 ,p_date_start => p_rec.date_start
2495 per_pds_bus.chk_last_standard_process_date
2496 (p_actual_termination_date => p_rec.actual_termination_date
2497 ,p_business_group_id => p_rec.business_group_id
2499 ,p_last_standard_process_date => p_rec.last_standard_process_date
2500 ,p_object_version_number => p_rec.object_version_number
2501 ,p_period_of_service_id => p_rec.period_of_service_id
2502 );
2503 hr_utility.set_location(l_proc, 7);
2504 --
2505 -- Validate actual termination date/last standard process date
2506 --
2507 -- Business Rule Mapping
2508 -- =====================
2509 -- Commenting out this routine for fix of bug# 2784295
2510 -- CHK_LAST_STANDARD_PROCESS_DATE / h
2511 --
2512 /* per_pds_bus.chk_at_date_lsp_date
2513 (p_actual_termination_date => p_rec.actual_termination_date
2514 ,p_last_standard_process_date => p_rec.last_standard_process_date
2515 ,p_object_version_number => p_rec.object_version_number
2516 ,p_period_of_service_id => p_rec.period_of_service_id
2517 ,p_business_group_id => p_rec.business_group_id
2518 );
2519 hr_utility.set_location(l_proc, 8); */
2520 --
2521 -- Validate final process date
2522 --
2523 -- Business Rule Mapping
2524 -- =====================
2525 --
2526 -- 70.4 change a start.
2527 --
2528 -- CHK_FINAL_PROCESS_DATE / c,d
2529 --
2530 -- 70.4 change a end.
2531 --
2532 per_pds_bus.chk_final_process_date
2533 (p_actual_termination_date => p_rec.actual_termination_date,
2534 p_date_start => p_rec.date_start,
2535 p_final_process_date => p_rec.final_process_date,
2536 p_last_standard_process_date => p_rec.last_standard_process_date,
2537 p_object_version_number => p_rec.object_version_number,
2538 p_period_of_service_id => p_rec.period_of_service_id);
2539 --
2540 --
2541 -- 70.3 change c end.
2542 --
2543 hr_utility.set_location(l_proc, 9);
2544 --
2545 --
2546 -- Validate leaving reason
2547 --
2548 -- Business Rule Mapping
2549 -- =====================
2550 -- CHK_LEAVING_REASON / a
2551 --
2552 per_pds_bus.chk_leaving_reason
2553 (p_leaving_reason => p_rec.leaving_reason,
2554 p_effective_date => p_effective_date,
2555 p_object_version_number => p_rec.object_version_number,
2556 p_period_of_service_id => p_rec.period_of_service_id);
2557 --
2558 hr_utility.set_location(l_proc, 10);
2559 --
2560 --
2561 -- Validate notified termination date
2562 --
2563 -- Business Rule Mapping
2564 -- =====================
2565 -- CHK_NOTIFIED_TERMINATION_DATE / b
2566 --
2567 per_pds_bus.chk_notified_termination_date
2568 (p_date_start => p_rec.date_start,
2569 p_notified_termination_date => p_rec.notified_termination_date,
2570 p_object_version_number => p_rec.object_version_number,
2571 p_period_of_service_id => p_rec.period_of_service_id);
2572 --
2573 hr_utility.set_location(l_proc, 11);
2574 --
2575 --
2576 -- Validate projected termination date
2577 --
2578 -- Business Rule Mapping
2579 -- =====================
2580 -- CHK_PROJECTED_TERMINATION_DATE / b
2581 --
2582 per_pds_bus.chk_projected_termination_date
2583 (p_date_start => p_rec.date_start,
2584 p_period_of_service_id => p_rec.period_of_service_id,
2585 p_object_version_number => p_rec.object_version_number,
2586 p_projected_termination_date => p_rec.projected_termination_date);
2587 --
2588 hr_utility.set_location(l_proc, 12);
2589 --
2590 --
2591 -- Validate termination accepted person id
2592 --
2593 -- Business Rule Mapping
2594 -- =====================
2595 -- CHK_TERMINATION_ACCEPTED_PERSON_ID / a,b
2596 --
2597 per_pds_bus.chk_termination_accepted_pers
2598 (p_accepted_termination_date => p_rec.accepted_termination_date,
2599 p_object_version_number => p_rec.object_version_number,
2600 p_period_of_service_id => p_rec.period_of_service_id,
2601 p_person_id => p_rec.person_id,
2602 p_termination_accepted_person => p_rec.termination_accepted_person_id,
2603 p_effective_date => p_effective_date);
2604 --
2605 --
2606 -- Validate descriptive flex fields.
2607 --
2608 per_pds_bus.chk_ddf(p_rec => p_rec);
2609 --
2610 --
2611 -- Validate flex fields.
2612 --
2613 per_pds_bus.chk_df(p_rec => p_rec);
2614 --
2615 hr_utility.set_location(' Leaving:'||l_proc, 13);
2616 End update_validate;
2617 --
2621 Procedure delete_validate(p_rec in per_pds_shd.g_rec_type) is
2618 -- ----------------------------------------------------------------------------
2619 -- |---------------------------< delete_validate >----------------------------|
2620 -- ----------------------------------------------------------------------------
2622 --
2623 l_proc varchar2(72) := g_package||'delete_validate';
2624 --
2625 Begin
2626 hr_utility.set_location('Entering:'||l_proc, 5);
2627 --
2628 -- Call all supporting business operations
2629 --
2630 hr_utility.set_location(' Leaving:'||l_proc, 10);
2631 End delete_validate;
2632 --
2633 -- ---------------------------------------------------------------------------
2634 -- |---------------------< return_legislation_code >-------------------------|
2635 -- ---------------------------------------------------------------------------
2636 --
2637 function return_legislation_code
2638 (p_period_of_service_id in number
2639 ) return varchar2 is
2640 --
2641 -- Declare cursor
2642 --
2643 cursor csr_leg_code is
2644 select pbg.legislation_code
2645 from per_business_groups pbg
2646 , per_periods_of_service pds
2647 where pds.period_of_service_id = p_period_of_service_id
2648 and pbg.business_group_id = pds.business_group_id;
2649 --
2650 -- Declare local variables
2651 --
2652 l_legislation_code varchar2(150);
2653 l_proc varchar2(72) := g_package||'return_legislation_code';
2654 begin
2655 hr_utility.set_location('Entering:'|| l_proc, 10);
2656 --
2657 -- Ensure that all the mandatory parameter are not null
2658 --
2659 hr_api.mandatory_arg_error(p_api_name => l_proc,
2660 p_argument => 'period_of_service_id',
2661 p_argument_value => p_period_of_service_id);
2662 --
2663 if nvl(g_period_of_service_id, hr_api.g_number) = p_period_of_service_id then
2664 --
2665 -- The legislation code has already been found with a previous
2666 -- call to this function. Just return the value in the global
2667 -- variable.
2668 --
2669 l_legislation_code := g_legislation_code;
2670 hr_utility.set_location(l_proc, 20);
2671 else
2672 --
2673 -- The ID is different to the last call to this function
2674 -- or this is the first call to this function.
2675 --
2676 open csr_leg_code;
2677 fetch csr_leg_code into l_legislation_code;
2678 if csr_leg_code%notfound then
2679 close csr_leg_code;
2680 --
2681 -- The primary key is invalid therefore we must error
2682 --
2683 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
2684 hr_utility.raise_error;
2685 end if;
2686 --
2687 -- Set the global variables so the values are
2688 -- available for the next call to this function
2689 --
2690 close csr_leg_code;
2691 g_period_of_service_id := p_period_of_service_id;
2692 g_legislation_code := l_legislation_code;
2693 end if;
2694 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2695 --
2696 return l_legislation_code;
2697 end return_legislation_code;
2698 --
2699 end per_pds_bus;