1 PACKAGE BODY PAY_NO_ABS_VALIDATION AS
2 /* $Header: pynoabsval.pkb 120.3.12020000.8 2012/08/28 09:02:18 nkjaladi ship $ */
3 l_glb_overlap_chk_done VARCHAR2(1); --#8510300
4 l_glb_absence_attendance_id NUMBER := NULL; --#8510300/14542232
5 --14073252 Starts
6 -- Function used to populate default value in NO_SC_OVERRIDE Org Developer DFF.
7 FUNCTION get_default_rule_value(p_rule IN VARCHAR2
8 ,p_effective_date IN DATE)
9 RETURN NUMBER IS
10
11 CURSOR csr_default_rule_value(c_rule IN VARCHAR2
12 ,c_date IN DATE) IS
13 SELECT to_number(NVL(ucf.value,0)) DefaultValue
14 FROM pay_user_column_instances_f ucf
15 ,pay_user_columns uc
16 ,pay_user_rows_f ur
17 ,pay_user_tables ut
18 WHERE ut.legislation_code = 'NO'
19 AND ut.user_table_name = 'Self Certification Rules'
20 AND ur.row_low_range_or_name = c_rule
21 AND ur.legislation_code = 'NO'
22 AND ur.user_table_id = ut.user_table_id
23 AND c_date BETWEEN ur.effective_start_date
24 AND ur.effective_end_date
25 AND uc.user_column_name='Default'
26 AND uc.legislation_code = 'NO'
27 AND uc.user_table_id = ut.user_table_id
28 AND ur.row_low_range_or_name = p_rule
29 AND ucf.legislation_code = 'NO'
30 AND ucf.user_row_id = ur.user_row_id
31 AND ucf.user_column_id = uc.user_column_id
32 AND c_date BETWEEN ucf.effective_start_date
33 AND ucf.effective_end_date;
34
35 r_default_rule_value csr_default_rule_value%ROWTYPE;
36
37 BEGIN
38 OPEN csr_default_rule_value(P_RULE,P_EFFECTIVE_DATE);
39 FETCH csr_default_rule_value INTO r_default_rule_value;
40 CLOSE csr_default_rule_value;
41
42 RETURN r_default_rule_value.DefaultValue;
43
44 END get_default_rule_value;
45 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --+
46 FUNCTION get_rule_value(p_rule IN VARCHAR2
47 ,p_person_id IN NUMBER
48 ,p_effective_date IN DATE)
49 RETURN NUMBER IS
50
51 CURSOR csr_default_rule_values(c_rule IN VARCHAR2
52 ,c_date IN DATE) IS
53 SELECT max (decode (uc.user_column_name
54 ,'Default'
55 ,ucf.value
56 ,0)) DefaultValue
57 ,max (decode (uc.user_column_name
58 ,'Minimum'
59 ,ucf.value
60 ,0)) MinValue
61 ,max (decode (uc.user_column_name
62 ,'Maximum'
63 ,ucf.value
64 ,0)) MaxValue
65 FROM pay_user_column_instances_f ucf
66 ,pay_user_columns uc
67 ,pay_user_rows_f ur
68 ,pay_user_tables ut
69 WHERE ut.legislation_code = 'NO'
70 AND ut.user_table_name = 'Self Certification Rules'
71 AND ur.row_low_range_or_name = c_rule
72 AND ur.legislation_code = 'NO'
73 AND ur.user_table_id = ut.user_table_id
74 AND c_date BETWEEN ur.effective_start_date
75 AND ur.effective_end_date
76 AND uc.legislation_code = 'NO'
77 AND uc.user_table_id = ut.user_table_id
78 AND ucf.legislation_code = 'NO'
79 AND ucf.user_row_id = ur.user_row_id
80 AND ucf.user_column_id = uc.user_column_id
81 AND c_date BETWEEN ucf.effective_start_date
82 AND ucf.effective_end_date
83 GROUP BY ur.row_low_range_or_name;
84 ----+
85
86 CURSOR csr_get_org(c_person_id IN NUMBER) IS
87 SELECT hoi.organization_id org_id
88 FROM per_all_assignments_f paaf
89 ,per_all_people_f papf
90 ,hr_soft_coding_keyflex hsc
91 ,hr_organization_information hoi
92 WHERE papf.person_id = c_person_id
93 AND paaf.person_id = papf.person_id
94 AND paaf.primary_flag = 'Y'
95 AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
96 AND hoi.org_information1 = hsc.segment2
97 AND hoi.org_information_context = 'NO_LOCAL_UNITS';
98 ----+
99 CURSOR csr_override_rule_values(c_org_id IN NUMBER,
100 c_rule IN VARCHAR2) IS
101 SELECT decode (c_rule
102 ,'CONSECUTIVE_DAYS' ,org_information1
103 ,'YEARLY_DAYS' ,org_information2
104 ,'YEARLY_COUNT' ,org_information3
105 ,'EMPLOYER_PERIOD_DAYS' ,org_information4
106 ,0) override
107 FROM hr_organization_information
108 WHERE organization_id = c_org_id
109 AND org_information_context = 'NO_SC_OVERRIDE';
110 ----+
111 l_override csr_override_rule_values%ROWTYPE;
112 l_rule csr_default_rule_values%ROWTYPE;
113 l_org csr_get_org%ROWTYPE;
114 l_rule_value NUMBER:=0;
115 BEGIN
116
117 OPEN csr_get_org(P_PERSON_ID);
118 FETCH csr_get_org INTO l_org;
119 CLOSE csr_get_org;
120
121 OPEN csr_override_rule_values(l_org.org_id,P_RULE);
122 FETCH csr_override_rule_values INTO l_override;
123 CLOSE csr_override_rule_values;
124
125 OPEN csr_default_rule_values(P_RULE,P_EFFECTIVE_DATE);
126 FETCH csr_default_rule_values INTO l_rule;
127 CLOSE csr_default_rule_values;
128
129 l_rule_value:=NVL(l_override.override,l_rule.DefaultValue);
130 l_rule_value:=LEAST(l_rule_value,l_rule.MaxValue);
131 l_rule_value:=GREATEST(l_rule_value,l_rule.MinValue);
132
133 RETURN l_rule_value;
134 END get_rule_value;
135 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --+
136 FUNCTION is_reimbursement_day(p_person_id IN NUMBER
137 ,p_abs_link_period IN NUMBER
138 ,p_start_date IN DATE
139 ,p_end_date IN DATE)
140 RETURN NUMBER IS
141 J BINARY_INTEGER;
142 TYPE date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;
143 sick_dates date_table;
144 sick_day DATE;
145
146 FUNCTION get_connected_absence(p_conn_start_date IN DATE
147 ,p_dates IN date_table)
148 RETURN NUMBER IS
149 CURSOR csr_connected_absence IS
150 SELECT p_conn_start_date - prev_abs.date_end duration
151 ,prev_abs.date_start start_date
152 ,prev_abs.date_end end_date
153 FROM per_absence_attendances prev_abs
154 ,per_absence_attendance_types pat
155 WHERE prev_abs.absence_attendance_type_id = pat.absence_attendance_type_id
156 AND prev_abs.date_end < p_conn_start_date
157 AND pat.absence_category IN ('S','PTS')
158 AND (((p_conn_start_date - prev_abs.date_end) <= p_abs_link_period)
159 OR (decode (trim (to_char (prev_abs.date_end,'DAY'))
160 ,'FRIDAY',next_day (prev_abs.date_end,'SUNDAY')
161 ,'SATURDAY',next_day (prev_abs.date_end,'SUNDAY')
162 ,prev_abs.date_end) BETWEEN decode (trim (to_char (p_conn_start_date,'DAY'))
163 ,'MONDAY',p_conn_start_date - 2
164 ,'SUNDAY',p_conn_start_date - 1
165 ,p_conn_start_date) - (p_abs_link_period+1)
166 AND decode (trim (to_char (p_conn_start_date,'DAY'))
167 ,'MONDAY',p_conn_start_date - 2
168 ,'SUNDAY',p_conn_start_date - 1
169 ,p_conn_start_date)-1))
170 AND prev_abs.date_start IS NOT NULL
171 AND prev_abs.date_end IS NOT NULL
172 AND prev_abs.person_id = p_person_id
173 ORDER BY duration;
174
175
176 r_connected_absence csr_connected_absence%ROWTYPE;
177 l_dates date_table;
178 l_day DATE;
179 l_days NUMBER;
180
181
182 BEGIN
183 OPEN csr_connected_absence;
184 FETCH csr_connected_absence INTO r_connected_absence;
185
186 IF csr_connected_absence%FOUND THEN
187 CLOSE csr_connected_absence;
188 l_dates := p_dates;
189 l_day := r_connected_absence.end_date;
190 WHILE l_day >= r_connected_absence.start_date LOOP
191 l_dates(NVL(l_dates.LAST,0)+1) := l_day;
192 l_day := l_day-1;
193 END LOOP;
194
195 RETURN get_connected_absence(p_conn_start_date => r_connected_absence.start_date
196 ,p_dates => l_dates);
197
198 ELSIF csr_connected_absence%NOTFOUND THEN
199 CLOSE csr_connected_absence;
200
201 l_dates := p_dates;
202 l_days :=0;
203
204 FOR I IN REVERSE l_dates.FIRST..l_dates.LAST LOOP
205 l_days:=l_days+1;
206 IF l_days > p_abs_link_period
207 AND (trim(to_char (l_dates(I),'DAY')) NOT IN ('SATURDAY','SUNDAY'))
208 AND l_dates(I) BETWEEN p_start_date AND p_end_date THEN
209 RETURN 1;
210 END IF;
211 EXIT WHEN l_dates(I)>p_end_date;
212 END LOOP;
213 RETURN 0;
214 END IF;
215 END get_connected_absence;
216
217
218 BEGIN
219 sick_day := p_end_date;
220 WHILE sick_day >= p_start_date LOOP
221 sick_dates(NVL(sick_dates.LAST,0)+1) := sick_day;
222 sick_day := sick_day-1;
223 END LOOP;
224
225
226
227 RETURN get_connected_absence(p_start_date
228 ,sick_dates);
229
230 END is_reimbursement_day;
231 --14073252 Ends
232 --#8510300 Start
233 -- Procedure checks for the Overlap of the absence for a person
234 -- during a particular period. If exists then procedure raises
235 -- an error
236 --
237 PROCEDURE chk_absence_overlap ( p_absence_attendance_id IN NUMBER
238 ,p_person_id IN NUMBER
239 ,p_date_start IN DATE
240 ,p_date_end IN DATE
241 ) IS
242
243 CURSOR csr_abs_overlap_another IS
244 SELECT 'Y'
245 FROM per_absence_attendances abs
246 WHERE abs.person_id = p_person_id
247 AND abs.date_start is not null
248 AND p_date_start is not null
249 AND (p_absence_attendance_id is null or
250 p_absence_attendance_id <> abs.absence_attendance_id)
251 AND (( abs.date_start
252 BETWEEN p_date_start AND NVL(p_date_end,hr_api.g_eot)
253 )
254 OR
255 ( p_date_start
256 BETWEEN abs.date_start AND NVL(abs.date_end,hr_api.g_eot)
257 )
258 );
259 l_exists VARCHAR2(1);
260 l_proc VARCHAR2(100) := 'PAY_NO_ABS_VALIDATION.CHK_ABSENCE_OVERLAP';
261 BEGIN
262 --
263 hr_utility.set_location (l_proc,5);
264 --
265 -- Cursor to return the absence for the period
266 --
267 OPEN csr_abs_overlap_another;
268 FETCH csr_abs_overlap_another INTO l_exists;
269
270 hr_utility.set_location (l_proc,10);
271 IF csr_abs_overlap_another%FOUND THEN
272 --
273 -- Raise the error message
274 --
275 hr_utility.set_location (l_proc,15);
276 Fnd_message.set_name('PAY','PAY_376933_NO_ABS_OVERLAP');
277 CLOSE csr_abs_overlap_another;
278 Fnd_message.raise_error;
279 END IF;
280
281 hr_utility.set_location (l_proc,20);
282 CLOSE csr_abs_overlap_another;
283 hr_utility.set_location (l_proc,25);
284 --
285 END chk_absence_overlap;
286 --
287 -- Function returns the number of working days for a given period as per
288 -- the Norway Legislation.
289 --
290 FUNCTION get_no_work_days( p_start_date IN DATE
291 ,p_end_date IN DATE
292 )
293 RETURN NUMBER IS
294 l_date DATE;
295 l_working_days NUMBER;
296 l_proc VARCHAR2(100) := 'PAY_NO_ABS_VALIDATION.GET_NO_WORK_DAYS';
297 BEGIN
298
299 hr_utility.set_location (l_proc,5);
300 --
301 -- Function would return 0 if start date is greater than end date
302 --
303 IF (p_start_date > p_end_date) THEN
304 hr_utility.set_location (l_proc,10);
305 RETURN 0;
306 END IF;
307
308 l_date := p_start_date;
309 l_working_days := 0;
310
311 hr_utility.set_location (l_proc,15);
312
313 --
314 -- Loop through all days from start date to the end date
315 --
316 WHILE (l_date <= p_end_date)
317 LOOP
318 hr_utility.set_location (l_proc,20);
319 --
320 -- Check if the day of the date is Saturday or Sunday. If not add
321 -- 1 to number of working days
322 --
323 IF ((to_char(l_date,'DY','nls_date_language=ENGLISH') <> 'SUN')
324 AND (to_char(l_date,'DY','nls_date_language=ENGLISH') <> 'SAT')
325 ) THEN
326 hr_utility.set_location (l_proc,25);
327 l_working_days := l_working_days + 1;
328 END IF;
329 l_date := l_date + 1;
330 hr_utility.set_location (l_proc,30);
331 END LOOP;
332
333 hr_utility.set_location (l_proc,35);
334
335 RETURN l_working_days;
336
337 END get_no_work_days;
338 --
339 -- This function will be created for calculating the number of Child minder
340 -- sickness leave a person is eligible for as on effective date.
341 --
342 FUNCTION get_cms_entitlement( p_person_id IN NUMBER
343 ,p_effective_date IN DATE)
344 RETURN NUMBER IS
345
346 CURSOR csr_child_contact ( p_person_id NUMBER, p_contact_type VARCHAR2, p_eff_date DATE) IS
347 SELECT pap.date_of_birth
348 ,ROUND(MONTHS_BETWEEN( p_eff_date, pap.date_of_birth ) / 12, 2) AS AGE
349 ,pcr.contact_type
350 ,pcr.cont_information1
351 ,pcr.cont_information2
352 ,nvl(pcr.date_end,to_date('31-12-4712','DD-MM-YYYY')) date_end
353 FROM per_all_people_f pap
354 ,per_contact_relationships pcr
355 WHERE pap.person_id = pcr.contact_person_id
356 AND pcr.person_id = p_person_id
357 AND pcr.contact_type = p_contact_type
358 AND (pcr.date_start is null or pcr.date_start <= p_eff_date)
359 AND (pcr.date_end is null or pcr.date_end >= p_eff_date );
360
361 l_total_child NUMBER;
362 l_dep_child NUMBER;
363 l_dis_child NUMBER;
364 l_entitlement NUMBER;
365 l_sole_count NUMBER;
366 l_proc VARCHAR2(100) := 'PAY_NO_ABS_VALIDATION.GET_CMS_ENTITLEMENT';
367 BEGIN
368 hr_utility.set_location (l_proc,5);
369 l_dep_child := 0;
370 l_dis_child := 0;
371 l_total_child := 0;
372 l_sole_count := 0;
373 l_entitlement := 0;
374
375 -- Fetch the contact information for the person as of an effective date
376 -- with relation as 'Dependent Child(DC)'
377 FOR i IN csr_child_contact( p_person_id, 'DC', p_effective_date )
378 LOOP
379 hr_utility.set_location (l_proc,10);
380
381 -- Count the Total number of contact with relation as 'Dependent Child(DC)'
382 l_total_child := l_total_child + 1;
383 i.cont_information2 := NVL(i.cont_information2,'N');
384 i.cont_information1 := NVL(i.cont_information1,'N');
385 -- Count the child as dependent only if Age is less than 13 years
386 -- or Age is less than 19 years with chronic flag as Yes
387 IF (i.age < 13) THEN
388 hr_utility.set_location (l_proc,15);
389 l_dep_child := l_dep_child + 1;
390 IF (i.cont_information2 = 'Y') THEN
391 hr_utility.set_location (l_proc,20);
392 l_dis_child := l_dis_child + 1;
393 END IF;
394 IF (i.cont_information1 = 'Y') THEN
395 hr_utility.set_location (l_proc,25);
396 l_sole_count := l_sole_count + 1;
397 END IF;
398 ELSE
399 hr_utility.set_location (l_proc,30);
400 IF ( i.age < 19 AND i.cont_information2 = 'Y') THEN
401 hr_utility.set_location (l_proc,35);
402 l_dep_child := l_dep_child + 1;
403 l_dis_child := l_dis_child + 1;
404 IF (i.cont_information1 = 'Y') THEN
405 hr_utility.set_location (l_proc,40);
406 l_sole_count := l_sole_count + 1;
407 END IF;
408 END IF;
409 END IF;
410 hr_utility.set_location (l_proc,45);
411 END LOOP;
412
413 hr_utility.set_location (l_proc,50);
414
415 -- If dependent child is more than 0 and less than 2
416 -- then entitlement is 10 . If it is more than 2 then
417 -- entitlement is 15.
418 IF ((l_dep_child > 0) AND (l_dep_child <= 2)) THEN
419 hr_utility.set_location (l_proc,55);
420 l_entitlement := 10;
421 ELSIF ((l_dep_child > 2)) THEN
422 hr_utility.set_location (l_proc,60);
423 l_entitlement := 15;
424 END IF;
425
426 -- If all the dependent child have sole guardian marked as yes
427 -- then entitlement is doubled.
428 hr_utility.set_location (l_proc,65);
429 IF (l_sole_count = l_dep_child) THEN
430 hr_utility.set_location (l_proc,70);
431 l_entitlement := l_entitlement * 2;
432 END IF;
433
434 -- For every disabled dependent child 10 days of entitlement is added
435 hr_utility.set_location (l_proc,75);
436 IF (l_dis_child > 0) THEN
437 hr_utility.set_location (l_proc,80);
438 l_entitlement := l_entitlement + (l_dis_child * 10);
439 END IF;
440
441 hr_utility.set_location (l_proc,85);
442 RETURN l_entitlement;
443
444 END get_cms_entitlement;
445
446 -- This function will be created for calculating the number of Child minder
447 -- sickness leave taken by the person from the year start of effective date
448 -- and effective date.
449 FUNCTION get_cms_leave_taken( p_person_id IN NUMBER
450 ,p_effective_date IN DATE)
451 RETURN NUMBER IS
452
453 CURSOR csr_cms_leave_taken ( p_personid NUMBER, p_start_date DATE, p_end_date DATE) IS
454 SELECT abs.date_start
455 ,abs.date_end
456 ,abs.absence_days
457 FROM per_absence_attendances abs
458 ,per_absence_attendance_types paat
459 WHERE abs.person_id = p_person_id
460 AND abs.absence_attendance_type_id = paat.absence_attendance_type_id
461 AND paat.absence_category = 'CMS'
462 AND abs.absence_attendance_id <> NVL(l_glb_absence_attendance_id,-1)
463 AND abs.date_start IS NOT NULL
464 AND (
465 (abs.date_start BETWEEN p_start_date AND p_end_date)
466 OR
467 (abs.date_end BETWEEN p_start_date AND p_end_date)
468 );
469
470 l_leave_taken NUMBER;
471 l_year_st_date DATE;
472 l_start_date DATE;
473 l_end_date DATE;
474 l_proc VARCHAR2(100) := 'PAY_NO_ABS_VALIDATION.GET_CMS_LEAVE_TAKEN';
475
476 BEGIN
477 hr_utility.set_location (l_proc,5);
478 l_leave_taken := 0;
479
480 l_year_st_date := trunc(p_effective_date,'YYYY');
481 hr_utility.set_location (l_proc,10);
482
483 -- Fetch all the leaves of the person between year start of the effective date
484 -- and effective date
485 FOR l_csr_leave IN csr_cms_leave_taken(p_person_id,l_year_st_date,p_effective_date)
486 LOOP
487
488 hr_utility.set_location (l_proc,15);
489 -- If the leave start and end dates are between the year start of the
490 -- effective date and effective date then added the absence days to the leave
491 -- taken and if days are not provided calculate number of days using func
492 -- get_no_work_days
493 IF ((l_csr_leave.date_start >= l_year_st_date ) AND (l_csr_leave.date_end <= p_effective_date)) THEN
494 hr_utility.set_location (l_proc,20);
495 IF NVL(l_csr_leave.absence_days,0) <> 0 THEN
496 l_leave_taken := l_leave_taken + l_csr_leave.absence_days;
497 ELSE
498 l_leave_taken := l_leave_taken + get_no_work_days(l_csr_leave.date_start,l_csr_leave.date_end);
499 END IF;
500 ELSE
501 hr_utility.set_location (l_proc,25);
502 -- if leave start date is less than year start date of the effective date
503 -- then calculate the leave from the year start date of the effective date
504 IF (l_csr_leave.date_start < l_year_st_date) THEN
505 hr_utility.set_location (l_proc,30);
506 l_start_date := l_year_st_date;
507 ELSE
508 hr_utility.set_location (l_proc,35);
509 l_start_date := l_csr_leave.date_start;
510 END IF;
511 hr_utility.set_location (l_proc,40);
512 -- if leave end date is greaten than effective date then calculate the
513 -- leave upto effective date
514 IF (NVL(l_csr_leave.date_end, TO_DATE('31-12-4712','DD-MM-YYYY')) > p_effective_date) THEN
515 hr_utility.set_location (l_proc,45);
516 l_end_date := p_effective_date;
517 ELSE
518 hr_utility.set_location (l_proc,50);
519 l_end_date := l_csr_leave.date_end;
520 END IF;
521 hr_utility.set_location (l_proc,60);
522 l_leave_taken := l_leave_taken + get_no_work_days (l_start_date,l_end_date);
523 hr_utility.set_location (l_proc,65);
524 END IF;
525 END LOOP;
526
527 hr_utility.set_location (l_proc,70);
528 RETURN l_leave_taken;
529
530 END get_cms_leave_taken;
531 --
532 --
533 FUNCTION cms_validation ( p_person_id IN NUMBER
534 ,p_date_start IN DATE
535 ,p_date_end IN DATE
536 ,p_cert_type IN VARCHAR2
537 ,p_dis_child_care IN VARCHAR2
538 ,p_absence_days IN VARCHAR2
539 ) RETURN VARCHAR2 IS
540 CURSOR child_contact ( personid NUMBER, contacttype VARCHAR2, abs_stdt DATE) IS
541 SELECT pap.date_of_birth
542 ,ROUND(MONTHS_BETWEEN( abs_stdt, pap.date_of_birth ) / 12, 2) AS AGE
543 ,pcr.contact_type
544 ,pcr.cont_information1
545 ,pcr.cont_information2
546 ,NVL(pcr.date_end,TO_DATE('31-12-4712','DD-MM-YYYY')) date_end
547 ,pcr.contact_relationship_id
548 FROM per_all_people_f pap
549 ,per_contact_relationships pcr
550 WHERE pap.person_id = pcr.contact_person_id
551 AND pcr.person_id = personid
552 AND pcr.contact_type = contacttype
553 AND (pcr.date_start IS NULL OR pcr.date_start <= abs_stdt)
554 AND (pcr.date_end IS NULL OR pcr.date_end >= abs_stdt );
555
556 CURSOR csr_contact_extra_info (p_con_relationship_id NUMBER) IS
557 SELECT pceif.effective_start_date
558 ,pceif.effective_end_date
559 ,fnd_date.canonical_to_date(pceif.cei_information1) start_date
560 ,fnd_date.canonical_to_date(pceif.cei_information2) end_date
561 FROM per_contact_extra_info_f pceif
562 WHERE pceif.contact_relationship_id = p_con_relationship_id
563 AND pceif.information_type = 'NO_CMS_DIS_DATE';
564
565 CURSOR csr_get_glb_value(p_global_name VARCHAR2, p_effective_date DATE) IS
566 SELECT fnd_number.canonical_to_number(global_value)
567 FROM ff_globals_f
568 WHERE global_name = p_global_name
569 AND legislation_code = 'NO'
570 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
571
572 l_cms_eligible_flag VARCHAR2(3);
573 l_cms_eligible_count NUMBER;
574 l_cms_contact_end_date DATE;
575 l_cms_child_count NUMBER;
576 l_cms_balance NUMBER;
577 l_warnings VARCHAR2(50);
578 l_absence_days NUMBER;
579 l_dis_child NUMBER;
580 l_nav_no_warn VARCHAR2(1);
581 l_cms_max_sc_elig NUMBER;
582 l_proc VARCHAR2(100) := 'PAY_NO_ABS_VALIDATION.CMS_VALIDATION';
583 BEGIN
584 hr_utility.set_location (l_proc,5);
585 l_cms_eligible_flag := 'N' ;
586 l_cms_eligible_count := 0 ;
587 l_cms_contact_end_date := null ;
588 l_cms_child_count := 0;
589 l_warnings := '';
590 l_dis_child := 0;
591 l_nav_no_warn := 'N';
592
593 hr_utility.set_location (l_proc,10);
594 -- Fetch the contact information for the person as of an effective date
595 -- with relation as 'Dependent Child(DC)'
596 FOR i IN child_contact ( p_person_id, 'DC', p_date_start )
597 LOOP
598 hr_utility.set_location (l_proc,15);
599 l_cms_child_count := l_cms_child_count + 1;
600 i.cont_information2 := nvl(i.cont_information2,'N');
601 -- Age is less than 13 years or Age is less than 19 years with chronic flag as Yes
602 IF (i.age < 13) OR ( i.age < 19 AND i.cont_information2 = 'Y') THEN
603 hr_utility.set_location (l_proc,20);
604 l_cms_eligible_count := l_cms_eligible_count + 1 ;
605 l_cms_eligible_flag := 'Y' ;
606 l_cms_contact_end_date := i.date_end;
607 END IF;
608 hr_utility.set_location (l_proc,25);
609 IF (i.cont_information2 = 'Y') THEN
610 hr_utility.set_location (l_proc,30);
611 l_dis_child := l_dis_child + 1;
612 IF l_nav_no_warn <> 'Y' THEN
613 hr_utility.set_location (l_proc,35);
614 FOR j in csr_contact_extra_info (i.contact_relationship_id)
615 LOOP
616 hr_utility.set_location (l_proc,40);
617 IF ((j.start_date <= p_date_start) AND (j.end_date >= p_date_end)) THEN
618 hr_utility.set_location (l_proc,45);
619 l_nav_no_warn := 'Y';
620 EXIT;
621 END IF;
622 END LOOP;
623 END IF;
624 END IF;
625 END LOOP;
626
627 IF l_cms_child_count = 0 THEN
628 hr_utility.set_location (l_proc,50);
629 l_warnings := l_warnings || '1'; --No Registered Children
630 END IF;
631
632 IF l_cms_eligible_flag = 'N' THEN
633 hr_utility.set_location (l_proc,55);
634 IF l_cms_child_count > 0 THEN
635 hr_utility.set_location (l_proc,60);
636 l_warnings := l_warnings || ',2'; --No Eligible Childen
637 END IF;
638 ELSIF l_cms_eligible_count = 1 AND p_date_end > l_cms_contact_end_date THEN
639 hr_utility.set_location (l_proc,65);
640 l_warnings := l_warnings || ',3'; --Contact Relationship has been end date
641 END IF;
642
643 IF p_date_start IS NOT NULL THEN
644 hr_utility.set_location (l_proc,70);
645 l_cms_balance := (pay_no_abs_validation.get_cms_entitlement(p_person_id,p_date_start) - pay_no_abs_validation.get_cms_leave_taken(p_person_id,p_date_start));
646 IF ((NVL(p_absence_days,0) = 0) AND (p_date_end IS NOT NULL)) THEN
647 hr_utility.set_location (l_proc,75);
648 l_absence_days := get_no_work_days(p_date_start,p_date_end);
649 ELSE
650 hr_utility.set_location (l_proc,80);
651 l_absence_days := p_absence_days;
652 END IF;
653
654 hr_utility.set_location (l_proc,85);
655 OPEN csr_get_glb_value('NO_MAX_CMS_SC_ELIGIBLE',p_date_start);
656 FETCH csr_get_glb_value INTO l_cms_max_sc_elig;
657 CLOSE csr_get_glb_value;
658
659 hr_utility.set_location (l_proc,90);
660 IF ((l_absence_days > l_cms_max_sc_elig) AND (p_cert_type = 'SC')) THEN
661 hr_utility.set_location (l_proc,95);
662 l_warnings := l_warnings || ',6'; -- Self Certification more than 3 days
663 END IF;
664 IF (l_absence_days > l_cms_balance) THEN
665 hr_utility.set_location (l_proc,100);
666 l_warnings := l_warnings || ',4'; --Entitlement is less than leave days
667 END IF;
668 END IF;
669
670
671 IF NVL(p_dis_child_care,'N') = 'Y' THEN
672 hr_utility.set_location (l_proc,105);
673 IF l_dis_child = 0 THEN
674 hr_utility.set_location (l_proc,110);
675 l_warnings := l_warnings || ',5'; -- No Disabled Children Registered
676 ELSE
677 hr_utility.set_location (l_proc,115);
678 IF l_nav_no_warn = 'N' THEN
679 hr_utility.set_location (l_proc,120);
680 l_warnings := l_warnings || ',5'; --NAV details doesn't exist
681 END IF;
682 END IF;
683 END IF;
684
685 hr_utility.set_location (l_proc,125);
686 RETURN l_warnings;
687
688 END cms_validation;
689 --
690 -- #14542232 This overloaded procedure was added to pass the
691 -- p_absence_attendance_id while updating the absence
692 -- record so that duration of current absence is considered
693 -- while calculating the entitlement
694 FUNCTION cms_validation ( p_person_id IN NUMBER
695 ,p_date_start IN DATE
696 ,p_date_end IN DATE
697 ,p_cert_type IN VARCHAR2
698 ,p_dis_child_care IN VARCHAR2
699 ,p_absence_days IN VARCHAR2
700 ,p_absence_attendance_id IN NUMBER
701 )
702 RETURN VARCHAR2 IS
703 l_warnings VARCHAR2(50);
704 BEGIN
705 l_warnings := NULL;
706 l_glb_absence_attendance_id := p_absence_attendance_id;
707
708 l_warnings := cms_validation ( p_person_id => p_person_id
709 ,p_date_start => p_date_start
710 ,p_date_end => p_date_end
711 ,p_cert_type => p_cert_type
712 ,p_dis_child_care => p_dis_child_care
713 ,p_absence_days => p_absence_days
714 );
715
716 l_glb_absence_attendance_id := NULL;
717 RETURN l_warnings;
718
719 END cms_validation;
720 --
721 --#8510300 End
722 PROCEDURE CREATE_ABS_VALIDATION ( P_ABS_INFORMATION_CATEGORY varchar2
723 ,P_PERSON_ID in NUMBER
724 ,P_EFFECTIVE_DATE in DATE
725 ,P_ABS_INFORMATION1 in VARCHAR2
726 ,P_ABS_INFORMATION2 in VARCHAR2
727 ,P_ABS_INFORMATION3 in VARCHAR2
728 ,P_ABS_INFORMATION5 in VARCHAR2
729 ,P_ABS_INFORMATION6 in VARCHAR2
730 ,P_ABS_INFORMATION9 in VARCHAR2 --#8510300
731 ,P_ABS_INFORMATION15 in VARCHAR2
732 ,P_ABS_INFORMATION16 in VARCHAR2
733 ,P_DATE_START in DATE
734 ,P_DATE_END in DATE
735 ,P_DATE_PROJECTED_START in DATE
736 ,P_DATE_PROJECTED_END in DATE
737 ,P_ABSENCE_DAYS in NUMBER --#8510300
738 ,P_ABS_ATTENDANCE_REASON_ID in NUMBER) is
739
740 CURSOR csr_get_gender(l_person_id NUMBER, l_date DATE) IS
741 SELECT sex FROM per_all_people_f
742 WHERE person_id = l_person_id
743 AND l_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
744
745 -- Cursor to fetch global values
746 CURSOR csr_get_glb_value(p_global_name VARCHAR2, p_effective_date DATE) IS
747 SELECT fnd_number.canonical_to_number(global_value)
748 FROM ff_globals_f
749 WHERE global_name = p_global_name
750 AND legislation_code = 'NO'
751 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
752
753 CURSOR csr_get_person_hire_date(p_person_id number,p_effective_date date) IS
754 SELECT start_date,PER_INFORMATION7 FROM per_all_people_f where
755 person_id = p_person_id
756 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE
757 AND EFFECTIVE_END_DATE;
758
759 CURSOR CSR_SICKNESS_ELIG_CHECK (personid NUMBER) IS
760 SELECT (PAA.DATE_END - PAA.DATE_START) + 1 AS DAYS,PAA.DATE_START, PAA.DATE_END
761 FROM PER_ABSENCE_ATTENDANCES PAA, PER_ABSENCE_ATTENDANCE_TYPES PAT
762 WHERE PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAT.ABSENCE_ATTENDANCE_TYPE_ID
763 AND PAT.ABSENCE_CATEGORY = 'UN'
764 AND PAA.DATE_START IS NOT NULL
765 AND PAA.DATE_END IS NOT NULL
766 AND PAA.PERSON_ID = personid
767 ORDER BY DATE_START ;
768
769 CURSOR CSR_REASON_CODE ( attn_reason_id NUMBER) IS
770 select name from PER_ABS_ATTENDANCE_REASONS par
771 WHERE PAR.ABS_ATTENDANCE_REASON_ID = attn_reason_id ;
772
773 CURSOR CSR_3SC_SICKNESS_CHECK(personid NUMBER, abs_start_date DATE, abs_link_period NUMBER) IS
774 SELECT DATE_START
775 ,DATE_END
776 FROM PER_ABSENCE_ATTENDANCES
777 WHERE PERSON_ID = personid
778 AND DATE_END BETWEEN (abs_start_date - abs_link_period) AND (abs_start_date -1)
779 AND ABS_INFORMATION1 = 'SC'
780 AND DATE_START IS NOT NULL
781 AND DATE_END IS NOT NULL;
782
783 -- Bug# 14073252 Starts
784 CURSOR csr_absences_in_12months(c_person_id IN NUMBER
785 ,c_start_date IN DATE) IS
786 SELECT count (paa.absence_attendance_id) counts
787 ,sum ((paa.date_end - paa.date_start) + 1) days
788 FROM per_absence_attendances paa
789 WHERE paa.person_id = c_person_id
790 AND paa.date_end BETWEEN add_months (c_start_date,- 12)
791 AND (c_start_date - 1)
792 AND paa.abs_information1 = 'SC'
793 AND paa.date_start IS NOT NULL
794 AND paa.date_end IS NOT NULL;
795
796 r_absences_in_12months csr_absences_in_12months%ROWTYPE;
797 -- Bug# 14073252 Ends
798
799
800 l_gender varchar2(5);
801 l_date_of_birth date;
802 l_elig_start_date DATE;
803 l_reason_code varchar2(30);
804 l_abs_min_gap number;
805 l_person_id number;
806 l_abs_start_date date;
807 l_abs_end_date date;
808 l_person_hire_date date;
809 l_entitled_sc Varchar2(10);
810 l_abs_count Number(5);
811 l_months_employed_prev number;
812 l_months_employed_curr number;
813 l_eligible varchar2(4);
814 l_min_worked_months number;
815 l_abs_link_period number;
816 l_within_n_months number;
817 l_months_employed number;
818 l_check_start_date date;
819 l_tot_abs Number; -- Bug#8905705 fix
820 l_cms_warnings VARCHAR2(50); --Bug#8510300
821
822 BEGIN
823
824 OPEN csr_get_gender(P_PERSON_ID,P_EFFECTIVE_DATE);
825 FETCH csr_get_gender INTO l_gender;
826 CLOSE csr_get_gender;
827 --Error If gender is Male and apply leave for Maternity or Part Time Maternity
828 IF P_ABS_INFORMATION_CATEGORY IN ('NO_M','NO_PTM') THEN
829 IF l_gender = 'M' then
830 fnd_message.set_name('PAY','PAY_376876_NO_MATERNITY_LEAVE');
831 fnd_message.raise_error;
832 END IF;
833 END IF;
834
835 --Error If gender is Female and apply leave for Paternity or Part Time Paternity
836 IF P_ABS_INFORMATION_CATEGORY IN ('NO_PA','NO_PTP') THEN
837 IF l_gender = 'F' then
838 fnd_message.set_name('PAY','PAY_376877_NO_PATERNITY_LEAVE');
839 fnd_message.raise_error;
840 END IF;
841 END IF;
842
843 -- Error If initial absence is set to Yes and also a linking absence attached to the same absence
844 IF (P_ABS_INFORMATION15 = 'Y' AND P_ABS_INFORMATION16 is NOT NULL) THEN
845 Fnd_message.set_name('PAY','PAY_376916_NO_ABS_LINKING_INI');
846 fnd_message.raise_error;
847 END IF;
848 -- Error - If initial absence is set to No and no linking absence is provided
849 IF (P_ABS_INFORMATION15 = 'N' AND P_ABS_INFORMATION16 is NULL) THEN
850 Fnd_message.set_name('PAY','PAY_376917_NO_ABS_LINKING_DTL');
851 fnd_message.raise_error;
852 END IF;
853 IF P_ABS_INFORMATION_CATEGORY IN ('NO_S','NO_PTS') AND
854 P_ABS_INFORMATION15 is null AND P_ABS_INFORMATION16 is NOT NULL THEN
855 Fnd_message.set_name('PAY','PAY_376916_NO_ABS_LINKING_INI');
856 fnd_message.raise_error;
857 END IF;
858
859 l_person_id := P_PERSON_ID;
860 l_abs_start_date := NVL(P_DATE_START,P_DATE_PROJECTED_START);
861 l_abs_end_date := NVL(P_DATE_END,P_DATE_PROJECTED_END);
862
863 OPEN csr_get_person_hire_date(l_person_id, l_abs_start_date);
864 FETCH csr_get_person_hire_date INTO l_person_hire_date,l_entitled_sc;
865 CLOSE csr_get_person_hire_date;
866
867 OPEN csr_get_glb_value('NO_ABS_MIN_GAP',l_abs_start_date);
868 FETCH csr_get_glb_value INTO l_abs_min_gap;
869 CLOSE csr_get_glb_value;
870
871 OPEN csr_get_glb_value('NO_ABS_LINK_PERIOD',l_abs_start_date);
872 FETCH csr_get_glb_value INTO l_abs_link_period;
873 CLOSE csr_get_glb_value;
874
875
876 IF P_ABS_INFORMATION_CATEGORY IN ('NO_S','NO_PTS','NO_CMS') THEN
877 -- Error - if the certificate end date is earlier than the certificate start date
878 IF P_ABS_INFORMATION3 < P_ABS_INFORMATION2 THEN
879 Fnd_message.set_name('PAY','PAY_376908_NO_ST_END_DATE_VAL');
880 fnd_message.raise_error;
881 END IF;
882
883 --Find the eligiblity date
884 l_elig_start_date := l_person_hire_date + l_abs_min_gap ;
885 FOR i in CSR_SICKNESS_ELIG_CHECK (l_person_id) LOOP
886 IF i.date_start < l_elig_start_date THEN
887 l_elig_start_date := l_elig_start_date + i.days ;
888 ELSE
889 EXIT;
890 END IF;
891 END LOOP;
892
893 OPEN csr_reason_code (P_ABS_ATTENDANCE_REASON_ID);
894 FETCH csr_reason_code INTO l_reason_code;
895 CLOSE csr_reason_code ;
896
897 -- Error - When an absence is recorded before eligibility 28 days
898 IF (l_abs_start_date < l_elig_start_date) AND (l_reason_code is NULL or l_reason_code <> 'ABS_WA') THEN
899 Fnd_message.set_name('PAY','PAY_376910_NO_EMP_NOT_ELIGIBLE');
900 Fnd_message.raise_error;
901 END IF;
902
903 -- Error - if Self-Certificate is selected and the employee has had Self-Certified Sickness absences
904 -- totaling more than 3 days in the previous 14 days
905 IF P_ABS_INFORMATION_CATEGORY IN ('NO_S','NO_PTS') AND P_ABS_INFORMATION1 = 'SC' THEN
906 l_abs_count := 0;
907 l_tot_abs := (l_abs_end_date - l_abs_start_date) + 1; -- Bug#8905705 fix
908 /* --SC Warning 1 -- Bug# 14073252
909 IF l_tot_abs > get_rule_value(p_rule => 'CONSECUTIVE_DAYS'
910 ,p_person_id => P_PERSON_ID
911 ,p_effective_date => P_EFFECTIVE_DATE) THEN
912 Fnd_message.set_name('PAY','PAY_376929_ABS_SC_CONSECUTIVE');
913 Fnd_message.raise_error;
914 END IF;
915 */
916 FOR I IN CSR_3SC_SICKNESS_CHECK (l_person_id,l_abs_start_date,l_abs_link_period )
917 LOOP
918 IF i.DATE_START < (l_abs_start_date - l_abs_link_period) THEN
919 l_abs_count := ( i.DATE_END - (l_abs_start_date - l_abs_link_period) ) +1;
920 ELSIF i.DATE_END > (l_abs_start_date-1) THEN
921 l_abs_count := ((l_abs_start_date-1) - i.DATE_START )+1;
922 ELSIF i.DATE_END = i.DATE_START THEN
923 l_abs_count := 1;
924 ELSE
925 l_abs_count := (i.DATE_END - i.DATE_START)+1 ;
926 END IF;
927 l_tot_abs := l_tot_abs + l_abs_count; -- Bug#8905705 fix
928 END LOOP;
929
930 /* --SC Warning 2 -- Bug# 14073252
931 IF l_tot_abs > get_rule_value(p_rule => 'EMPLOYER_PERIOD_DAYS'
932 ,p_person_id => P_PERSON_ID
933 ,p_effective_date => P_EFFECTIVE_DATE) THEN
934 Fnd_message.set_name('PAY','PAY_376932_ABS_SC_EMPLR_PERIOD');
935 Fnd_message.raise_error;
936 END IF;
937
938
939 IF l_tot_abs > 3 THEN -- Bug#8905705 fix
940 Fnd_message.set_name('PAY','PAY_376869_NO_ABS_SELF_CERT');
941 Fnd_message.raise_error;
942 END IF;
943
944 --Error - Only 4 self certificate absences are allowed for an year.
945 BEGIN
946 SELECT count(1)
947 INTO l_abs_count
948 FROM PER_ABSENCE_ATTENDANCES PAA
949 WHERE PAA.PERSON_ID = l_person_id
950 AND PAA.DATE_END BETWEEN add_months(l_abs_start_date, -12) AND (l_abs_start_date-1)
951 AND PAA.ABS_INFORMATION1 = 'SC'
952 AND PAA.DATE_START IS NOT NULL
953 AND PAA.DATE_END IS NOT NULL;
954 EXCEPTION
955 WHEN OTHERS THEN
956 l_abs_count := 0;
957 END;
958 --Moved above query to cursor csr_absences_in_12months for Bug# 14073252
959
960 OPEN csr_absences_in_12months(P_PERSON_ID,l_abs_start_date);
961 FETCH csr_absences_in_12months INTO r_absences_in_12months;
962 CLOSE csr_absences_in_12months;
963
964 --SC Warning 3 --Bug# 14073252
965 IF r_absences_in_12months.counts > get_rule_value(p_rule => 'YEARLY_COUNT'
966 ,p_person_id => P_PERSON_ID
967 ,p_effective_date => P_EFFECTIVE_DATE) THEN
968 Fnd_message.set_name('PAY','PAY_376931_ABS_SC_YEARLY_COUNT');
969 Fnd_message.raise_error;
970 END IF;
971
972 --SC Warning 4 --Bug# 14073252
973 IF r_absences_in_12months.days > get_rule_value(p_rule => 'YEARLY_DAYS'
974 ,p_person_id => P_PERSON_ID
975 ,p_effective_date => P_EFFECTIVE_DATE) THEN
976 Fnd_message.set_name('PAY','PAY_376930_ABS_SC_YEARLY_DAYS');
977 Fnd_message.raise_error;
978 END IF;
979
980
981
982 IF l_abs_count >= 4 THEN
983 Fnd_message.set_name('PAY','PAY_376907_NO_SC_NOT_ELIGIBLE');
984 Fnd_message.raise_error;
985 END IF;
986 */ -- All SC Warnings not applicable in Self Service. Only below Error appears.
987
988 --SC Error 1 --Bug# 14073252
989 IF is_reimbursement_day (p_person_id => P_PERSON_ID
990 ,p_abs_link_period => l_abs_link_period
991 ,p_start_date => l_abs_start_date
992 ,p_end_date => NVL(l_abs_end_date,l_abs_start_date)) =1 THEN
993 Fnd_message.set_name('PAY','PAY_376888_ABS_SC_REIMB_DAY');
994 Fnd_message.raise_error;
995 END IF;
996
997 END IF;
998
999 IF P_ABS_INFORMATION_CATEGORY IN ('NO_S','NO_PTS') THEN
1000 -- Error - if the Entitltment for self certificate is No and type is selected as Self certification
1001 IF l_entitled_sc = 'N' AND P_ABS_INFORMATION1 = 'SC' THEN
1002 Fnd_message.set_name('PAY','PAY_376907_NO_SC_NOT_ELIGIBLE');
1003 Fnd_message.raise_error;
1004 END IF;
1005
1006 -- Error - if the Self certification is used and the service is less than 2 months
1007 l_months_employed_curr := trunc(months_between(l_abs_start_date,l_person_hire_date),2);
1008 IF l_months_employed_curr < 2 AND P_ABS_INFORMATION1 = 'SC' THEN
1009 Fnd_message.set_name('PAY','PAY_376907_NO_SC_NOT_ELIGIBLE');
1010 Fnd_message.raise_error; -- This is warning not error
1011 END IF;
1012 END IF;
1013
1014 -- Bug#8510300 start
1015 IF p_abs_information_category IN ('NO_CMS') THEN
1016 --
1017 -- Check if this absence overlaps another absence for the same person.
1018 --
1019 IF (NVL(l_glb_overlap_chk_done,'N') = 'N') THEN
1020 chk_absence_overlap (p_absence_attendance_id => NULL
1021 ,p_person_id => p_person_id
1022 ,p_date_start => p_date_start
1023 ,p_date_end => p_date_end
1024 );
1025 END IF;
1026
1027 l_cms_warnings := cms_validation( p_person_id => p_person_id
1028 ,p_date_start => p_date_start
1029 ,p_date_end => p_date_end
1030 ,p_cert_type => p_abs_information1
1031 ,p_dis_child_care => p_abs_information9
1032 ,p_absence_days => p_absence_days
1033 );
1034 IF (INSTR(l_cms_warnings,'6') > 0) THEN
1035 hr_utility.set_message(801, 'PAY_376907_NO_SC_NOT_ELIGIBLE');
1036 hr_multi_message.add
1037 (p_message_type => hr_multi_message.g_warning_msg
1038 );
1039 END IF;
1040 IF (INSTR(l_cms_warnings,'1') > 0) THEN
1041 hr_utility.set_message(801, 'PAY_376934_NO_CHILD_REG');
1042 hr_multi_message.add
1043 (p_message_type => hr_multi_message.g_warning_msg
1044 );
1045 END IF;
1046 IF (INSTR(l_cms_warnings,'2') > 0) THEN
1047 hr_utility.set_message(801, 'PAY_376935_NO_CHILD_MIN_AGE');
1048 hr_multi_message.add
1049 (p_message_type => hr_multi_message.g_warning_msg
1050 );
1051 END IF;
1052 IF (INSTR(l_cms_warnings,'3') > 0) THEN
1053 hr_utility.set_message(801, 'PAY_376914_NO_CMS_CONT_CHECK');
1054 hr_multi_message.add
1055 (p_message_type => hr_multi_message.g_warning_msg
1056 );
1057 END IF;
1058 IF (INSTR(l_cms_warnings,'4') > 0) THEN
1059 hr_utility.set_message(801, 'PAY_376936_NO_LIMIT_EXCEEED');
1060 hr_multi_message.add
1061 (p_message_type => hr_multi_message.g_warning_msg
1062 );
1063 END IF;
1064 IF (INSTR(l_cms_warnings,'5') > 0) THEN
1065 hr_utility.set_message(801, 'PAY_376937_NO_NAV_REQ');
1066 hr_multi_message.add
1067 (p_message_type => hr_multi_message.g_warning_msg
1068 );
1069 END IF;
1070 END IF;
1071 -- Bug#8510300 end
1072 END IF;
1073
1074
1075 IF P_ABS_INFORMATION_CATEGORY IN ('NO_PA','NO_PTP','NO_M','NO_PTM','NO_IE_AL','NO_PTA') THEN
1076
1077 l_months_employed_prev :=0;
1078 l_months_employed_curr :=0;
1079 l_months_employed := 0;
1080 l_eligible := 'N';
1081
1082 l_months_employed_curr := trunc(months_between(l_abs_start_date,l_person_hire_date),2);
1083
1084 OPEN csr_get_glb_value('NO_ABSENCE_MIN_MONTHS_SERVICE_REQUIRED',l_abs_start_date);
1085 FETCH csr_get_glb_value INTO l_min_worked_months;
1086 CLOSE csr_get_glb_value;
1087
1088 IF l_months_employed_curr >= l_min_worked_months THEN --changed to global
1089 l_eligible := 'Y';
1090 ELSE
1091 OPEN csr_get_glb_value('NO_ABSENCE_SERVICE_REQUIRED_WITHIN_MONTHS',l_abs_start_date);
1092 FETCH csr_get_glb_value INTO l_within_n_months;
1093 CLOSE csr_get_glb_value;
1094
1095 l_within_n_months := -1 * l_within_n_months;
1096 l_check_start_date := add_months(l_abs_start_date - 1,l_within_n_months); --changed to global
1097 l_months_employed_prev := PAY_NO_ABSENCE.get_months_employed(l_person_id,l_check_start_date,l_person_hire_date);
1098 l_months_employed := l_months_employed_curr + l_months_employed_prev;
1099 IF l_months_employed >= l_min_worked_months THEN --changed to global
1100 l_eligible := 'Y';
1101 END IF;
1102 l_within_n_months := -1 * l_within_n_months;
1103 END IF;
1104
1105 IF l_eligible = 'N' THEN
1106 --fnd_message.debug ('Person is not eligible to avail this absence as he/she is not employed for 6/10 months'); -- put proper warning message
1107 fnd_message.set_name('PAY','PAY_376875_NO_PARENTAL_ELIGIBL');
1108 fnd_message.set_token('MIN',to_char(l_min_worked_months));
1109 fnd_message.set_token('LIMIT',to_char(l_within_n_months));
1110 Fnd_message.raise_error;
1111
1112 END IF;
1113 IF P_ABS_INFORMATION_CATEGORY IN ('NO_PA','NO_PTP') THEN
1114 -- Error - if the absence start date is earlier than the date of birth
1115 l_date_of_birth := fnd_date.canonical_to_date(P_ABS_INFORMATION1) ;
1116 IF l_date_of_birth > P_DATE_START THEN
1117 Fnd_message.set_name('PAY','PAY_376905_NO_DOB_ST_DT_CHECK');
1118 Fnd_message.raise_error;
1119 END IF;
1120 END IF;
1121 IF P_ABS_INFORMATION_CATEGORY ='NO_M' THEN
1122 IF NVL(P_ABS_INFORMATION5,'N') = 'N' AND P_ABS_INFORMATION6 IS NOT NULL THEN
1123 fnd_message.set_name('PER','HR_376901_NO_ABS_NO_SPOUSE');
1124 fnd_message.raise_error;
1125 END IF;
1126 END IF ;
1127 END IF;
1128 END CREATE_ABS_VALIDATION ;
1129
1130 --Update Mode validations
1131 procedure UPDATE_ABS_VALIDATION (P_ABS_INFORMATION_CATEGORY in varchar2
1132 ,P_ABSENCE_ATTENDANCE_ID in NUMBER
1133 ,P_EFFECTIVE_DATE in DATE
1134 ,P_ABS_INFORMATION1 in VARCHAR2
1135 ,P_ABS_INFORMATION2 in VARCHAR2
1136 ,P_ABS_INFORMATION3 in VARCHAR2
1137 ,P_ABS_INFORMATION5 in VARCHAR2
1138 ,P_ABS_INFORMATION6 in VARCHAR2
1139 ,P_ABS_INFORMATION9 in VARCHAR2 --#8510300
1140 ,P_ABS_INFORMATION15 in VARCHAR2
1141 ,P_ABS_INFORMATION16 in VARCHAR2
1142 ,P_DATE_START in DATE
1143 ,P_DATE_END in DATE
1144 ,P_DATE_PROJECTED_START in DATE
1145 ,P_DATE_PROJECTED_END in DATE
1146 ,P_ABSENCE_DAYS in NUMBER --#8510300
1147 ,P_ABS_ATTENDANCE_REASON_ID in NUMBER) is
1148
1149 -- Get the person Id
1150 CURSOR get_person_id (p_abs_attendance_id in NUMBER) is
1151 SELECT person_id
1152 FROM PER_ABSENCE_ATTENDANCES
1153 WHERE ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
1154
1155 l_person_id NUMBER;
1156 BEGIN
1157 OPEN get_person_id(P_ABSENCE_ATTENDANCE_ID);
1158 FETCH get_person_id INTO l_person_id;
1159 CLOSE get_person_id;
1160
1161 l_glb_absence_attendance_id := P_ABSENCE_ATTENDANCE_ID; --#8510300/14542232
1162 -- Bug#8510300 start
1163 IF p_abs_information_category IN ('NO_CMS') THEN
1164 --
1165 -- Check if this absence overlaps another absence for the same person.
1166 --
1167 chk_absence_overlap (p_absence_attendance_id => p_absence_attendance_id
1168 ,p_person_id => l_person_id
1169 ,p_date_start => p_date_start
1170 ,p_date_end => p_date_end
1171 );
1172 l_glb_overlap_chk_done := 'Y';
1173 END IF;
1174 -- Bug#8510300 end
1175 -- Get the Person ID and pass it to Absence Create level validation package
1176 CREATE_ABS_VALIDATION (P_ABS_INFORMATION_CATEGORY
1177 ,l_person_id
1178 ,P_EFFECTIVE_DATE
1179 ,P_ABS_INFORMATION1
1180 ,P_ABS_INFORMATION2
1181 ,P_ABS_INFORMATION3
1182 ,P_ABS_INFORMATION5
1183 ,P_ABS_INFORMATION6
1184 ,P_ABS_INFORMATION9 --#8510300
1185 ,P_ABS_INFORMATION15
1186 ,P_ABS_INFORMATION16
1187 ,P_DATE_START
1188 ,P_DATE_END
1189 ,P_DATE_PROJECTED_START
1190 ,P_DATE_PROJECTED_END
1191 ,P_ABSENCE_DAYS --#8510300
1192 ,P_ABS_ATTENDANCE_REASON_ID ) ;
1193
1194 l_glb_overlap_chk_done := 'N'; -- Bug#8510300
1195 l_glb_absence_attendance_id := NULL; --#8510300/14542232
1196 END UPDATE_ABS_VALIDATION;
1197
1198 END PAY_NO_ABS_VALIDATION;
[Home] [Help]