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