1 Package Body ssp_mat_bus as
2 /* $Header: spmatrhi.pkb 120.5.12010000.3 2008/08/13 13:27:41 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ssp_mat_bus.'; -- Global package name
9 --
10 -- Business Validation Rules
11 --
12 -- -----------------------------------------------------------------------------
13 -- |---------------------------------< Validate_Female_Sex >-------------------
14 -- -----------------------------------------------------------------------------
15 --
16 -- PUBLIC
17 -- Description:
18 -- Maternity details may only be entered for Females
19 --
20 Procedure validate_female_sex (p_person_id in number) is
21 --
22 p_sex varchar2(30);
23 l_proc varchar2(72) := g_package||'validate_female_sex';
24 --
25 cursor csr_person is
26 --
27 -- Get the sex of the person
28 --
29 select p.sex
30 from per_all_people_f p
31 where p.person_id = p_person_id;
32 --
33 procedure check_parameters is
34 --
35 begin
36 --
37 hr_api.mandatory_arg_error
38 (p_api_name => l_proc,
39 p_argument => 'person_id',
40 p_argument_value => p_person_id);
41 --
42 end check_parameters;
43 --
44 BEGIN
45 --
46 hr_utility.set_location ('Entering '||l_proc,1);
47 --
48 check_parameters;
49 --
50 open csr_person;
51 fetch csr_person into p_sex;
52 --
53 if csr_person%notfound then
54 --
55 -- The person id is not valid
56 --
57 close csr_person;
58 fnd_message.set_name ('SSP' , 'SSP_35057_BAD_PERSON_ID' ) ;
59 fnd_message.raise_error;
60 --
61 elsif p_sex <> 'F' then
62 --
63 -- The person is not female (and therefore may not have maternity)
64 --
65 close csr_person;
66 fnd_message.set_name ('SSP', 'SSP_35002_PER_NOT_FEMALE');
67 fnd_message.raise_error;
68 --
69 else
70 close csr_person;
71 end if;
72 --
73 hr_utility.set_location ('Leaving '||l_proc,10);
74 --
75 END validate_female_sex;
76
77 --
78 -- -----------------------------------------------------------------------------
79 -- ---------------------< unique_person_due_date >------------------------------
80 -- -----------------------------------------------------------------------------
81 --
82 -- PUBLIC
83 -- Description:
84 -- Ensure that no existing baby due_dates have been previously entered
85 -- for this person
86 --
87 Procedure unique_person_due_date (
88 --
89 p_due_date in date,
90 p_person_id in number,
91 p_leave_type in varchar2,
92 p_maternity_id in number) is
93 --
94 l_proc varchar2 (72) := g_package||'unique_person_due_date';
95 --
96 cursor csr_duplicate_due_date is
97 --
98 -- Get that the due date is unique for the person
99 --
100 select 1
101 from ssp_maternities h
102 where due_date = p_due_date
103 and person_id = p_person_id
104 and nvl(leave_type,'MA') = nvl(p_leave_type,'MA')
105 and maternity_id <> nvl(p_maternity_id,0);
106 --
107 duplicate_due_date_rec csr_duplicate_due_date%ROWTYPE;
108 --
109 BEGIN
110 --
111 hr_utility.set_location (l_proc,1);
112 --
113 open csr_duplicate_due_date;
114 fetch csr_duplicate_due_date into duplicate_due_date_rec;
115 --
116 if csr_duplicate_due_date%FOUND then
117 --
118 close csr_duplicate_due_date;
119 fnd_message.set_name ('SSP', 'SSP_35009_DUP_DUE_DATE');
120 fnd_message.raise_error;
121 --
122 end if;
123 --
124 close csr_duplicate_due_date;
125 --
126 hr_utility.set_location (l_proc,100);
127 --
128 END unique_person_due_date;
129 --
130 -- -----------------------------------------------------------------------------
131 -- -------------------------< check_actual_birth >-----------------------------
132 -- ---------------------------------------------------------------------------
133 --
134 -- PUBLIC
135 -- Description:
136 -- Ensure the birth date is not defined in the future
137 --
138
139 Procedure check_actual_birth (p_actual_birth_date in date) is
140 BEGIN
141 if p_actual_birth_date > SYSDATE then
142 fnd_message.set_name('SSP','SSP_35003_INV_FUTURE_DATE');
143 fnd_message.raise_error;
144 end if;
145 END check_actual_birth;
146 --
147 -- ----------------------------------------------------------------------------
148 -- |-------------------------< check_live_birth >------------------------------
149 -- ----------------------------------------------------------------------------
150 --
151 -- PUBLIC
152 -- Description:
153 -- Check that the a birth date is entered if live_birth_flag is No
154 --
155
156 Procedure check_live_birth (p_live_birth_flag in out nocopy varchar2, p_actual_birth_date in date) is
157 BEGIN
158 if p_live_birth_flag = 'N' and p_actual_birth_date is null then
159 fnd_message.set_name ('SSP', 'SSP_35004_LIVE_BIRTH_FLAG');
160 fnd_message.raise_error;
161 p_live_birth_flag := 'Y';
162 end if;
163 END check_live_birth;
164 --
165 -- ---------------------------------------------------------------------------
166 -- |------------------------------< check_date_notified >--------------------
167 -- ---------------------------------------------------------------------------
168 --
169 -- PUBLIC
170 -- Description:
171 -- Ensure the birth notification date is not before the actual birth date
172 --
173
174 Procedure check_date_notified (p_notification_of_birth_date in date,
175 p_actual_birth_date in date) is
176 BEGIN
177 if p_notification_of_birth_date < p_actual_birth_date then
178 fnd_message.set_name ('SSP', 'SSP_35012_EARLY_BIRTH_NOTIFIC');
179 fnd_message.raise_error;
180 elsif p_notification_of_birth_date > SYSDATE then
181 fnd_message.set_name ('SSP', 'SSP_35019_INV_NOTIF_DATE');
182 fnd_message.raise_error;
183 end if;
184 END check_date_notified;
185 --
186 -- -----------------------------------------------------------------------------
187 -- ---------------------< check_stated_ret_date >------------------------------
188 -- ----------------------------------------------------------------------------
189 --
190 -- PUBLIC
191 -- Description:
192 -- 1. Cannot enter a stated reurn date if return status is No
193 -- 2. Stated return date cannot be before the maternity pay period
194 -- start date
195 -- 3. Stated return date cannot be before the earliest mpp start date
196 --
197
198 Procedure check_stated_ret_date (p_stated_return_date in date,
199 p_intend_to_return in varchar2,
200 p_mpp_start_date in date,
201 p_earliest_mpp_start in date) is
202 BEGIN
203 if p_intend_to_return = 'N' and p_stated_return_date is not null then
204 fnd_message.set_name('SSP', 'SSP_35013_INV_EVNT_RET_DATE');
205 fnd_message.raise_error;
206 elsif p_stated_return_date < nvl(p_mpp_start_date, p_stated_return_date) then
207 fnd_message.set_name ('SSP', 'SSP_35006_RET_DATE_LT_MPP');
208 fnd_message.raise_error;
209 elsif p_stated_return_date < nvl(p_earliest_mpp_start, p_stated_return_date) then
210 fnd_message.set_name ('SSP','SSP_35007_RET_DTE_LT_EMPSD');
211 fnd_message.raise_error;
212 end if;
213 END check_stated_ret_date;
214 --
215 --------------------------------------------------------------------------------
216 -- ----------------------------< check_MPP_start_date >-------------------------
217 -- -----------------------------------------------------------------------------
218 --
219 -- PUBLIC
220 -- Description:
221 -- If no actual_birth_date is entered, the mpp_start_date cannot be before
222 -- the earliest_mpp_start_date. Otherwise the actual_birth_date is the next
223 -- sunday after the actual_birth_date.
224 --
225
226 Procedure check_MPP_start_date (p_mpp_start_date in out nocopy date,
227 p_earliest_mpp_start_date in date,
228 p_actual_birth_date in date) is
229 BEGIN
230 if p_mpp_start_date < p_earliest_mpp_start_date and p_actual_birth_date IS NULL then
231 fnd_message.set_name ('PER', 'SSP_35114_SMCAL_EMPP_TOO_EARLY');
232 fnd_message.raise_error;
233 end if;
234
235 if NOT(p_actual_birth_date IS NULL) then
236 if (p_mpp_start_date IS NULL) OR (p_mpp_start_date < p_earliest_mpp_start_date) THEN
237 ssp_mat_bus.default_mpp_date(p_actual_birth_date, p_mpp_start_date);
238 end if;
239 end if;
240
241 END check_MPP_start_date;
242
243 Procedure check_MPP_start_date_2 (p_mpp_start_date in date,
244 p_person_id in number,
245 p_ewc in date,
246 p_earliest_mpp_start_date in date,
247 p_due_date in date,
248 p_prev_mpp_start_date in date,
249 p_actual_birth_date in date) is
250
251 cursor pregnancy_related_absences is
252 select paa.sickness_start_date,
253 paa.sickness_end_date
254 from per_absence_attendances paa
255 where paa.person_id = p_person_id
256 and paa.pregnancy_related_illness = 'Y'
257 and (p_ewc - 28) <= nvl(paa.sickness_end_date, fnd_date.canonical_to_date('4712/12/31 00:00:00'))
258 and p_ewc >= paa.sickness_start_date;
259 --
260 l_pregnancy_related_illness pregnancy_related_absences%ROWTYPE;
261 -- bug 2649315, translation fix
262 l_sunday varchar2(100) := to_char(to_date('07/01/2001','DD/MM/YYYY'),'fmDAY');
263
264 BEGIN
265 --
266 -- Find out if there are any pregnancy related absences
267 --
268 OPEN pregnancy_related_absences;
269 FETCH pregnancy_related_absences into l_pregnancy_related_illness;
270 --
271 -- Do not apply Sunday only validation to those maternities
272 -- where the person has a pregnancy related absence
273 -- within four weeks of the EWC
274 --
275 -- this conditions checks if the baby is born earlier then the mpp start date
276 -- if yes then it allows the mpp start date to be ABD + 1 else raises the error.
277 if p_due_date >= fnd_date.canonical_to_date('2003/04/06 00:00:00') and
278 (p_prev_mpp_start_date is null or p_actual_birth_date < p_prev_mpp_start_date) and
279 p_actual_birth_date is not null then
280 if p_mpp_start_date = p_actual_birth_date + 1 then
281 CLOSE pregnancy_related_absences;
282 null;
283 elsif p_due_date >= fnd_date.canonical_to_date('2003/04/06 00:00:00') and
284 p_actual_birth_date > p_prev_mpp_start_date and
285 p_actual_birth_date is not null then
286 -- user cannot reset the value of MPP
287 if p_prev_mpp_start_date <> p_mpp_start_date then
288 fnd_message.set_name('SSP','SSP_36078_MPP_RESET_MPP');
289 fnd_message.raise_error;
290 end if;
291 elsif ( p_due_date >= fnd_date.canonical_to_date('2003/04/06 00:00:00')
292 and p_mpp_start_date = p_actual_birth_date + 1 ) or
293 ( pregnancy_related_absences%FOUND and
294 p_mpp_start_date = l_pregnancy_related_illness.sickness_start_date ) then
295 CLOSE pregnancy_related_absences;
296 null;
297 -- Bug 3944196 - additional check
298 elsif ( p_due_date >= fnd_date.canonical_to_date('2003/04/06 00:00:00') and
299 p_actual_birth_date + 1 = p_prev_mpp_start_date and
300 p_actual_birth_date is not null) then
301 CLOSE pregnancy_related_absences;
302 null;
303 else
304 CLOSE pregnancy_related_absences;
305 fnd_message.set_name('SSP','SSP_36077_MPP_PREMATURE_DAY');
306 fnd_message.raise_error;
307 end if;
308 -- This condition checks if the baby was born during the MPP, if yes then
309 -- the user cannot set the MPP start date here
310 /*
311 elsif pregnancy_related_absences%FOUND and
312 p_mpp_start_date <> l_pregnancy_related_illness.sickness_start_date then
313 CLOSE pregnancy_related_absences;
314 fnd_message.set_name('SSP','SSP_36079_MPP_SICKNESS_DAY');
315 fnd_message.raise_error;
316 */
317 -- begin bug fix 5331152
318 elsif pregnancy_related_absences%FOUND and
319 p_mpp_start_date = l_pregnancy_related_illness.sickness_start_date then
320 CLOSE pregnancy_related_absences;
321 fnd_message.set_name('SSP','SSP_35051_MPP_ILLNESS_DATE');
322 fnd_message.raise_error;
323 elsif pregnancy_related_absences%FOUND and
324 p_mpp_start_date = l_pregnancy_related_illness.sickness_start_date + 1 then
325 CLOSE pregnancy_related_absences;
326 null;
327 -- end bug fix 5331152
328 elsif p_due_date >= fnd_date.canonical_to_date('2003/04/06 00:00:00') and
329 p_actual_birth_date is not null and
330 p_mpp_start_date <> p_actual_birth_date + 1 then
331 CLOSE pregnancy_related_absences;
332 fnd_message.set_name('SSP','SSP_36077_MPP_PREMATURE_DAY');
333 fnd_message.raise_error;
334 elsif to_char(to_date(to_char(p_mpp_start_date,'DD/MM/YYYY'),'DD/MM/YYYY'),'fmDAY') <> l_sunday
335 and
336 p_due_date < fnd_date.canonical_to_date('2007/04/01 00:00:00') then
337 CLOSE pregnancy_related_absences;
338 fnd_message.set_name('SSP','SSP_35054_MPP_NOT_SUNDAY');
339 fnd_message.raise_error;
340 end if;
341
342 END check_MPP_start_date_2;
343
344 --------------------------------------------------------------------------------
345 -- ----------------------------< check_forward_MPP_date >-----------------------
346 -- -----------------------------------------------------------------------------
347 --
348 -- PUBLIC
349 -- Description:
350 -- The MPP start date cannot be before the start date of maternity allowance
351 --
352
353 Procedure check_forward_MPP_date (p_start_date_mat_allow in date,
354 p_mpp_start_date in out nocopy date) is
355 BEGIN
356 if p_mpp_start_date is null and p_start_date_mat_allow is not null
357 then
358 p_mpp_start_date := p_start_date_mat_allow;
359 elsif p_mpp_start_date > nvl(p_start_date_mat_allow, p_mpp_start_date)
360 then
361 fnd_message.set_name ('SSP', 'SSP_35014_FWD_MPP_START');
362 fnd_message.raise_error;
363 end if;
364 END check_forward_MPP_date;
365 --
366 -- -----------------------------------------------------------------------------
367 -- -----------------------< get_SMP_element_details >---------------------------
368 -- -----------------------------------------------------------------------------
369 --
370 -- PRIVATE
371 -- Description: Retrieve statutory SMP details
372 -->>>>
373
374 PROCEDURE get_SMP_element_details
375 (
376 p_effective_date in date,
377 p_element_name in varchar2,
378 p_element_details out nocopy ssp_SMP_pkg.csr_SMP_element_details%ROWTYPE
379 ) is
380
381 l_proc varchar2(72) := g_package||'get_SMP_element_details';
382
383 begin
384
385 open ssp_SMP_pkg.csr_SMP_element_details(p_effective_date, p_element_name);
386 fetch ssp_SMP_pkg.csr_SMP_element_details into p_element_details;
387 close ssp_SMP_pkg.csr_SMP_element_details;
388
389 end get_SMP_element_details;
390 --
391 -- -----------------------------------------------------------------------------
392 -- ----------------------------< late_evidence >--------------------------------
393 -- -----------------------------------------------------------------------------
394 --
395 -- PUBLIC
396 -- Description: Check whether maternity evidence was late
397 --
398
399 FUNCTION late_evidence
400 (p_maternity_id in number,
401 p_evidence_rcvd in date,
402 p_effective_date in date,
403 p_element_name in varchar2)
404 return boolean is
405 --
406 l_element_details ssp_SMP_pkg.csr_SMP_element_details%ROWTYPE;
407 l_late_evidence BOOLEAN;
408 l_chk_date DATE;
409 cursor c1 is
410 select m.mpp_start_date
411 from ssp_maternities m
412 where m.maternity_id = p_maternity_id;
413 c1_rec c1%ROWTYPE;
414
415 BEGIN
416 get_SMP_element_details(p_effective_date, p_element_name, l_element_details);
417 --
418 open c1;
419 fetch c1 into c1_rec;
420 close c1;
421 --
422 l_chk_date := c1_rec.mpp_start_date + l_element_details.latest_smp_evidence;
423
424 if p_evidence_rcvd > l_chk_date then
425 return(TRUE);
426 else
427 return(FALSE);
428 end if;
429 END late_evidence;
430 --
431 -- -----------------------------------------------------------------------------
432 -- ----------------------< evd_before_ewc_due_date_change >---------------------
433 -- -----------------------------------------------------------------------------
434 --
435 -- PUBLIC
436 -- Description: Check the evidence date is valid if the due date is being
437 -- changed, in case this invalidates evidence being input.
438 --
439
440 PROCEDURE evd_before_ewc_due_date_change
441 (
442 p_qualifying_week in date,
443 p_ewc in date,
444 p_maternity_id in number
445 ) is
446 l_element_details ssp_SMP_pkg.csr_SMP_element_details%ROWTYPE;
447 l_chk_date date;
448 l_proc varchar2(72) := g_package||'evd_before_ewc_due_date_change';
449
450 cursor csr_evidence is
451 select evidence_date from ssp_medicals
452 where maternity_id = p_maternity_id;
453
454 BEGIN
455 get_SMP_element_details(p_qualifying_week,
456 'Statutory Maternity Pay', l_element_details);
457 --
458 l_chk_date := p_ewc - l_element_details.earliest_SMP_evidence;
459
460 for csr_ev in csr_evidence loop
461 if csr_ev.evidence_date < l_chk_date then
462 fnd_message.set_name ('SSP', 'SSP_36075_EVIDENCE_NOW_INVALID');
463 fnd_message.raise_error;
464 end if;
465 end loop;
466 --
467 END evd_before_ewc_due_date_change;
468
469 -- -----------------------------------------------------------------------------
470 -- -----------------------------------< evd_before_ewc >------------------------
471 -- -----------------------------------------------------------------------------
472 --
473 -- PUBLIC
474 -- Description: Check the evidence date is valid
475 --
476
477 PROCEDURE evd_before_ewc
478 (p_ewc in date,
479 p_evidence_date in date,
480 p_effective_date in date,
481 p_element_name in varchar2) is
482 l_element_details ssp_SMP_pkg.csr_SMP_element_details%ROWTYPE;
483 l_chk_date DATE;
484 BEGIN
485 get_SMP_element_details(p_effective_date, p_element_name, l_element_details);
486 --
487 l_chk_date := p_ewc - l_element_details.earliest_SMP_evidence;
488 if p_evidence_date < l_chk_date then
489 fnd_message.set_name ('SSP', 'SSP_35028_LATE_EVID_DATE');
490 fnd_message.raise_error;
491 end if;
492 END evd_before_ewc;
493
494 -- -----------------------------------------------------------------------------
495 -- --------------------------------< default_mpp_date >-------------------------
496 -- -----------------------------------------------------------------------------
497 --
498 -- PUBLIC
499 -- Description:
500 -- Default the MPP start date if the birth date has been specified
501 -- and the mpp_date is null
502 --
503 PROCEDURE default_mpp_date(p_actual_birth_date in date,
504 p_mpp_start_date in out nocopy date)
505 is
506 l_day_number number;
507 BEGIN
508 --
509 -- Amended the logic of the procedure by re-introducing the check
510 --for mpp_date is null. which was removed without a reason.
511 --
512 if p_actual_birth_date is not null and
513 p_mpp_start_date is null then
514 -- if p_actual_birth_date is not null then
515 begin
516 l_day_number := to_number(to_char(p_actual_birth_date,'D'));
517 if l_day_number <> 1 then
518 p_mpp_start_date := p_actual_birth_date + (8 - l_day_number);
519 elsif l_day_number = 1 then
520 p_mpp_start_date := p_actual_birth_date;
521 end if;
522 end;
523 end if;
524 END default_mpp_date;
525
526 -- -----------------------------------------------------------------------------
527 -- ----------------------------< default_date_notification >--------------------
528 -- -----------------------------------------------------------------------------
529 --
530 -- PUBLIC
531 -- Description:
532 -- Default the date notification when an actual birth date is entered
533 -- to sysdate
534 --
535 PROCEDURE default_date_notification(p_actual_birth_date in date,
536 p_notif_of_birth_date in out nocopy date
537 ) is
538 BEGIN
539 if p_actual_birth_date is not null and
540 p_notif_of_birth_date is null then
541 p_notif_of_birth_date := SYSDATE;
542 end if;
543 END default_date_notification;
544
545 PROCEDURE default_date_notification(p_actual_birth_date in date,
546 p_effective_date in date,
547 p_notif_of_birth_date in out nocopy date
548 ) is
549 BEGIN
550 if p_actual_birth_date is not null and
551 p_notif_of_birth_date is null then
552 p_notif_of_birth_date := p_effective_date;
553 end if;
554 END default_date_notification;
555
556 PROCEDURE CHECK_CHILD_EXPECTED_DATE(p_due_date in date,
557 p_matching_date in date) is
558 Begin
559 If p_due_date < p_matching_date then
560 fnd_message.set_name('SSP','SSP_36094_CHILD_EXPECTED_DATE');
561 fnd_message.raise_error;
562 End if;
563 End;
564
565 PROCEDURE CHECK_APP_START_DATE(p_mpp_start_date in date,
566 p_placement_date in date,
567 p_due_date in date) is
568 Begin
569
570 If p_placement_date is not null and p_mpp_start_date > p_placement_date then
571 fnd_message.set_name('SSP','SSP_36085_APP_DATE_PLACEMENT');
572 fnd_message.raise_error;
573 end if;
574
575 If p_mpp_start_date < ssp_sap_pkg.EARLIEST_APP_START_DATE(p_due_date) then
576 fnd_message.set_name('SSP','SSP_36083_APP_DATE_EARLY');
577 fnd_message.raise_error;
578 End if;
579
580 If p_placement_date is null and p_mpp_start_date > p_due_date then
581 fnd_message.set_name('SSP','SSP_36098_APP_DATE_LATE');
582 fnd_message.raise_error;
583 End if;
584
585 End ;
586
587 PROCEDURE CHECK_PPPA_START_DATE(p_ppp_start_date in date,
588 p_placement_date in date,
589 p_due_date in date) is
590 begin
591 if p_placement_date is null and p_ppp_start_date is not null then
592 fnd_message.set_name('SSP','SSP_36090_PPP_DATE_PLACE_NULL');
593 fnd_message.raise_error;
594 end if;
595
596 if p_ppp_start_date < ssp_pad_pkg.EARLIEST_PPP_START_DATE(p_placement_date)
597 then
598 fnd_message.set_name('SSP','SSP_36088_PPP_DATE_PLACE_EARLY');
599 fnd_message.raise_error;
600 end if;
601
602 if p_ppp_start_date > ssp_pad_pkg.LATEST_PPP_START_DATE(p_placement_date)
603 then
604 fnd_message.set_name('SSP','SSP_36087_PPP_DATE_PLACE_LATE');
605 fnd_message.raise_error;
606 end if;
607 end;
608
609 PROCEDURE CHECK_PPP_START_DATE(p_ppp_start_date in date,
610 p_birth_date in date,
611 p_ewc in date,
612 p_due_date in date) is
613 begin
614 if p_birth_date is null and p_ppp_start_date is not null then
615 fnd_message.set_name('SSP','SSP_36092_PPP_DATE_BIRTH_NULL');
616 fnd_message.raise_error;
617 end if;
618
619 if p_ppp_start_date > ssp_pab_pkg.LATEST_PPP_START_DATE(p_birth_date,
620 p_ewc,
621 p_due_date)
622 then
623 fnd_message.set_name('SSP','SSP_36097_PPP_DATE_BIRTH_LATE');
624 fnd_message.raise_error;
625 end if;
626
627 if p_ppp_start_date < ssp_pab_pkg.EARLIEST_PPP_START_DATE(p_birth_date)
628 then
629 fnd_message.set_name('SSP','SSP_36096_PPP_DATE_BIRTH');
630 fnd_message.raise_error;
631 end if;
632
633 end;
634
635 PROCEDURE CHECK_PLACEMENT_DATE( p_placement_date in date,
636 P_MATCHING_DATE IN DATE) is
637 begin
638 If p_placement_date < P_MATCHING_DATE then
639 fnd_message.set_name('SSP','SSP_36091_PLACE_DATE_MATCHING');
640 fnd_message.raise_error;
641 End if;
642 End;
643
644 PROCEDURE CHECK_DISRUPTED_PLACEMENT_DATE (p_disrupted_placement_date in date,
645 p_mpp_start_date in date) is
646 Begin
647 If p_disrupted_placement_date < p_mpp_start_date then
648 fnd_message.set_name('SSP','SSP_36095_APP_DATE_DISRUPTED');
649 fnd_message.raise_error;
650 End if;
651 End ;
652
653
654 Procedure check_adopt_child_birth_dt(p_actual_birth_date in date,
655 p_due_date in date) is
656 BEGIN
657 NULL;
658 /* Removed 18 year check due to legislation change
659 if months_between(p_due_date,p_actual_birth_date)/12 > 18 then
660 fnd_message.set_name('SSP','SSP_36093_ACTUALBIRTH_DATE_18');
661 fnd_message.raise_error;
662 end if;
663 */
664 END;
665 --
666 -- ----------------------------------------------------------------------------
667 -- |--------------------------< check_kit_days >-------------------------------
668 -- ----------------------------------------------------------------------------
669 --
670 -- PUBLIC
671 -- Description:
672 -- Check that the a
673 --date entered is within the MPP/APP periods
674 -- and if MA type, also check that it is 2 weeks after Birth Date
675 --
676 Procedure check_kit_days(p_rec in ssp_mat_shd.g_rec_type) is
677
678 cursor c_get_ma_end_date is /* Bug 6003570 */
679 select max(date_end) end_date
680 from per_absence_attendances
681 where maternity_id=p_rec.maternity_id;
682
683 l_2_weeks constant number := 14;
684 l_2_weeks_after_birth date;
685 l_start_date date := p_rec.mpp_start_date;
686 l_end_date date;
687 l_get_ma_end_date date;
688
689 procedure validate_kit(p_start in date,
690 p_end in date,
691 p_date in date) is
692 begin
693 if p_date is not null then
694 if p_start is null then
695 fnd_message.set_name ('SSP', 'SSP_35083_KIT_DAY_NO_MPP_APP');
696 fnd_message.raise_error;
697 end if;
698 if p_date < p_start or
699 p_date > p_end then
700 fnd_message.set_name ('SSP', 'SSP_35081_KIT_DAY_PERIOD');
701 fnd_message.set_token('DATE1', p_start);
702 fnd_message.set_token('DATE2',p_end);
703 fnd_message.raise_error;
704 else
705 /*Bug 7025371 if p_date <= l_2_weeks_after_birth then */
706 if p_date between p_rec.actual_birth_date+1 and p_rec.actual_birth_date + 15 then
707 fnd_message.set_name ('SSP', 'SSP_35082_KIT_DAY_NOT_ALLOWS');
708 fnd_message.raise_error;
709 end if;
710 end if;
711 end if;
712 end;
713
714
715 BEGIN
716
717 l_2_weeks_after_birth := to_date('01/01/0001','DD/MM/YYYY');
718 --
719 if p_rec.mat_information_category is not null then
720 if p_rec.leave_type = 'MA' then
721 if p_rec.actual_birth_date is not null then
722 l_2_weeks_after_birth := p_rec.actual_birth_date + 14;
723 end if;
724
725 -- begin
726 open c_get_ma_end_date;
727 fetch c_get_ma_end_date into l_get_ma_end_date;
728 close c_get_ma_end_date;
729 -- end;
730
731 l_end_date := least(ssp_smp_pkg.get_max_SMP_date(p_rec.maternity_id), nvl(l_get_ma_end_date,to_date('31-12-4712','DD-MM-YYYY')));
732
733 else -- type = 'AD'
734 l_end_date := ssp_sap_pkg.get_max_SAP_date(p_rec.maternity_id);
735 end if;
736 --
737
738
739 validate_kit(l_start_date,l_end_date,fnd_date.canonical_to_date(p_rec.mat_information1));
740 validate_kit(l_start_date,l_end_date,fnd_date.canonical_to_date(p_rec.mat_information2));
741 validate_kit(l_start_date,l_end_date,fnd_date.canonical_to_date(p_rec.mat_information3));
742 validate_kit(l_start_date,l_end_date,fnd_date.canonical_to_date(p_rec.mat_information4));
743 validate_kit(l_start_date,l_end_date,fnd_date.canonical_to_date(p_rec.mat_information5));
744 validate_kit(l_start_date,l_end_date,fnd_date.canonical_to_date(p_rec.mat_information6));
745 validate_kit(l_start_date,l_end_date,fnd_date.canonical_to_date(p_rec.mat_information7));
746 validate_kit(l_start_date,l_end_date,fnd_date.canonical_to_date(p_rec.mat_information8));
747 validate_kit(l_start_date,l_end_date,fnd_date.canonical_to_date(p_rec.mat_information9));
748 validate_kit(l_start_date,l_end_date,fnd_date.canonical_to_date(p_rec.mat_information10));
749 end if;
750 END check_kit_days;
751 --------------------------------------------------------------------------------
752 -- ----------------------------------------------------------------------------
753 -- |---------------------------< insert_validate >----------------------------|
754 -- ----------------------------------------------------------------------------
755 Procedure insert_validate(p_rec in out nocopy ssp_mat_shd.g_rec_type) is
756 --
757 l_proc varchar2(72) := g_package||'insert_validate';
758 l_earliest_mpp_start date;
759 --
760 Begin
761 hr_utility.set_location('Entering:'||l_proc, 5);
762 --
763 -- Call all supporting business operations
764 --
765 unique_person_due_date (p_rec.due_date,
766 p_rec.person_id,
767 p_rec.leave_type,
768 p_rec.maternity_id);
769
770 if nvl(p_rec.leave_type,'MA') = 'MA' then
771 validate_female_sex (p_rec.person_id);
772 --
773 -- unique_person_due_date (p_rec.due_date,
774 -- p_rec.person_id,
775 -- p_rec.maternity_id);
776 --
777 check_live_birth (p_rec.live_birth_flag, p_rec.actual_birth_date);
778 --
779 default_mpp_date (p_rec.actual_birth_date, p_rec.mpp_start_date);
780 --
781 check_date_notified (p_rec.notification_of_birth_date,
782 p_rec.actual_birth_date);
783 --
784 l_earliest_mpp_start := ssp_SMP_pkg.earliest_mpp_start_date (p_rec.due_date);
785 --
786 check_stated_ret_date (p_rec.stated_return_date,
787 p_rec.intend_to_return_flag,
788 p_rec.mpp_start_date,
789 l_earliest_mpp_start);
790 --
791 if p_rec.start_date_maternity_allowance is not null then
792 check_forward_MPP_date (p_rec.start_date_maternity_allowance,
793 p_rec.mpp_start_date) ;
794 end if;
795
796 --
797 check_kit_days(p_rec);
798 --
799 elsif p_rec.leave_type ='AD' then
800 l_earliest_mpp_start := ssp_SAP_pkg.earliest_App_start_date (p_rec.due_date);
801 --
802 check_stated_ret_date (p_rec.stated_return_date,
803 p_rec.intend_to_return_flag,
804 p_rec.mpp_start_date,
805 l_earliest_mpp_start);
806 --
807 check_kit_days(p_rec);
808 --
809 end if;
810 --
811 hr_utility.set_location(' Leaving:'||l_proc, 10);
812 End insert_validate;
813 --
814 -- ----------------------------------------------------------------------------
815 -- |---------------------------< update_validate >----------------------------|
816 -- ----------------------------------------------------------------------------
817 Procedure update_validate(p_rec in out nocopy ssp_mat_shd.g_rec_type) is
818 --
819 l_proc varchar2(72) := g_package||'update_validate';
820 l_earliest_mpp_start date;
821 --
822 Begin
823 hr_utility.set_location('Entering:'||l_proc, 5);
824 --
825 -- Call all supporting business operations
826 --
827 unique_person_due_date (p_rec.due_date,
828 p_rec.person_id,
829 p_rec.leave_type,
830 p_rec.maternity_id);
831 --
832 --
833 if nvl(p_rec.leave_type,'MA') = 'MA' then
834
835 check_live_birth (p_rec.live_birth_flag, p_rec.actual_birth_date);
836 --
837 default_mpp_date (p_rec.actual_birth_date, p_rec.mpp_start_date);
838 --
839 check_date_notified (p_rec.notification_of_birth_date, p_rec.actual_birth_date);
840 --
841 l_earliest_mpp_start := ssp_SMP_pkg.earliest_mpp_start_date (p_rec.due_date);
842 check_stated_ret_date (p_rec.stated_return_date, p_rec.intend_to_return_flag,
843 p_rec.mpp_start_date, l_earliest_mpp_start);
844 --
845 if p_rec.start_date_maternity_allowance is not null then
846 check_forward_MPP_date (p_rec.start_date_maternity_allowance, p_rec.mpp_start_date) ;
847 end if;
848 --
849 check_kit_days(p_rec);
850 --
851 elsif p_rec.leave_type ='AD' then
852 l_earliest_mpp_start := ssp_SAP_pkg.earliest_App_start_date (p_rec.due_date);
853
854 check_stated_ret_date (p_rec.stated_return_date,
855 p_rec.intend_to_return_flag,
856 p_rec.mpp_start_date,
857 l_earliest_mpp_start);
858 --
859 check_kit_days(p_rec);
860 --
861 end if;
862
863 hr_utility.set_location(' Leaving:'||l_proc, 10);
864 End update_validate;
865 --
866 -- ----------------------------------------------------------------------------
867 -- |---------------------------< delete_validate >----------------------------|
868 -- ----------------------------------------------------------------------------
869 Procedure delete_validate(p_rec in ssp_mat_shd.g_rec_type) is
870 --
871 l_proc varchar2(72) := g_package||'delete_validate';
872 --
873 Begin
874 hr_utility.set_location('Entering:'||l_proc, 5);
875 --
876 -- Call all supporting business operations
877 --
878 null;
879 --
880 hr_utility.set_location(' Leaving:'||l_proc, 10);
881 End delete_validate;
882 --
883 end ssp_mat_bus;
[Home] [Help]