1 PACKAGE BODY PAY_SE_ABSENCE AS
2 /* $Header: pysesick.pkb 120.5 2007/08/20 15:18:16 rravi noship $ */
3 FUNCTION GET_HOURLY_RATE(
4 p_assignment_id IN NUMBER
5 ,p_effective_date IN DATE
6 ,p_abs_start_date IN DATE
7 ,p_abs_end_date IN DATE
8 ,p_Monthly_Pay IN NUMBER
9 ,p_hourly_rate_option1 OUT NOCOPY VARCHAR
10 ,p_hourly_rate_option2 OUT NOCOPY VARCHAR
11 ,p_hourly_rate_option3 OUT NOCOPY VARCHAR
12 ,p_hourly_rate_option4 OUT NOCOPY VARCHAR
13 ,p_hourly_rate_option5 OUT NOCOPY VARCHAR
14 ,p_hourly_rate_option6 OUT NOCOPY VARCHAR
15 ,p_hourly_rate_option7 OUT NOCOPY VARCHAR
16 ,p_hourly_rate_option8 OUT NOCOPY VARCHAR
17 ,p_hourly_rate_option9 OUT NOCOPY VARCHAR
18 ,p_hourly_rate_option10 OUT NOCOPY VARCHAR
19 ,p_hourly_rate OUT NOCOPY NUMBER
20 ,p_normal_hours OUT NOCOPY NUMBER
21 ,p_working_perc OUT NOCOPY NUMBER
22 ,p_salary_rate_option1 OUT NOCOPY VARCHAR
23 ,p_salary_rate_option2 OUT NOCOPY VARCHAR
24 ,p_salary_rate_option3 OUT NOCOPY VARCHAR
25 ,p_salary_rate_option4 OUT NOCOPY VARCHAR
26 ,p_salary_rate_option5 OUT NOCOPY VARCHAR
27 ,p_salary_rate_option6 OUT NOCOPY VARCHAR
28 ,p_salary_rate_option7 OUT NOCOPY VARCHAR
29 ,p_salary_rate_option8 OUT NOCOPY VARCHAR
30 ,p_salary_rate_option9 OUT NOCOPY VARCHAR
31 ,p_salary_rate_option10 OUT NOCOPY VARCHAR
32 ,p_salary_rate OUT NOCOPY NUMBER
33 ,p_hour_sal OUT NOCOPY VARCHAR
34 )
35 RETURN NUMBER IS
36
37 l_le_hourly_rate varchar2(20);
38 l_pe_hourly_rate varchar2(20);
39 l_ab_hourly_rate varchar2(20);
40 l_gen_hourly_rate varchar2(20);
41 l_le_salary_rate varchar2(20);
42 l_pe_salary_rate varchar2(20);
43 l_ab_salary_rate varchar2(20);
44 l_gen_salary_rate varchar2(20);
45 l_person_id NUMBER;
46 l_working_percentage NUMBER;
47 l_abs_attendance_id NUMBER;
48 l_business_group_id NUMBER;
49 l_normal_hours NUMBER;
50 l_frequency CHAR(1);
51 l_hour_sal CHAR(1);
52 l_employee_category CHAR(2);
53 --To Fetch the hourly rate from EIT for Legal Employer
54 CURSOR csr_Legal_Employer_Hourly_Rate IS
55 SELECT hoi4.ORG_INFORMATION1
56
57 FROM HR_ORGANIZATION_UNITS o1
58 ,HR_ORGANIZATION_INFORMATION hoi1
59 ,HR_ORGANIZATION_INFORMATION hoi2
60 ,HR_ORGANIZATION_INFORMATION hoi3
61 ,HR_ORGANIZATION_INFORMATION hoi4
62 ,( SELECT TRIM(SCL.SEGMENT2) AS ORG_ID
63 FROM PER_ALL_ASSIGNMENTS_F ASG
64 ,HR_SOFT_CODING_KEYFLEX SCL
65 WHERE ASG.ASSIGNMENT_ID = p_assignment_id
66 AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
67 AND p_effective_date BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE ) X
68 WHERE o1.business_group_id = l_business_group_id
69 AND hoi1.organization_id = o1.organization_id
70 AND hoi1.organization_id = X.ORG_ID
71 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
72 AND hoi1.org_information_context = 'CLASS'
73 AND o1.organization_id = hoi2.org_information1
74 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
75 AND hoi2.organization_id = hoi3.organization_id
76 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
77 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
78 AND hoi3.organization_id = hoi4.organization_id
79 AND hoi4.ORG_INFORMATION_CONTEXT='SE_HOURLY_RATE_DETAILS'
80 AND HOI4.org_information1 IS NOT NULL;
81
82 CURSOR csr_Legal_Employer_Salary_Rate IS
83 SELECT hoi4.ORG_INFORMATION1
84
85 FROM HR_ORGANIZATION_UNITS o1
86 ,HR_ORGANIZATION_INFORMATION hoi1
87 ,HR_ORGANIZATION_INFORMATION hoi2
88 ,HR_ORGANIZATION_INFORMATION hoi3
89 ,HR_ORGANIZATION_INFORMATION hoi4
90 ,( SELECT TRIM(SCL.SEGMENT2) AS ORG_ID
91 FROM PER_ALL_ASSIGNMENTS_F ASG
92 ,HR_SOFT_CODING_KEYFLEX SCL
93 WHERE ASG.ASSIGNMENT_ID = p_assignment_id
94 AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
95 AND p_effective_date BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE ) X
96 WHERE o1.business_group_id = l_business_group_id
97 AND hoi1.organization_id = o1.organization_id
98 AND hoi1.organization_id = X.ORG_ID
99 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
100 AND hoi1.org_information_context = 'CLASS'
101 AND o1.organization_id = hoi2.org_information1
102 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
103 AND hoi2.organization_id = hoi3.organization_id
104 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
105 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
106 AND hoi3.organization_id = hoi4.organization_id
107 AND hoi4.ORG_INFORMATION_CONTEXT='SE_HOURLY_RATE_DETAILS'
108 AND HOI4.org_information2 IS NOT NULL;
109
110 --To fetch the hourly rate from person form
111
112 CURSOR csr_Person_Hourly_Rate IS
113 SELECT PER_INFORMATION6 AS Hourly_Rate --Hourly/Salaried
114 FROM PER_ALL_PEOPLE_F PER
115 WHERE PER.PERSON_ID = l_person_id
116 AND P_EFFECTIVE_DATE BETWEEN PER.EFFECTIVE_START_DATE
117 AND PER.EFFECTIVE_END_DATE;
118
119 CURSOR csr_Person_Salary_Rate IS
120 SELECT PER_INFORMATION7 AS Salary_Rate --Hourly/Salaried
121 FROM PER_ALL_PEOPLE_F PER
122 WHERE PER.PERSON_ID = l_person_id
123 AND P_EFFECTIVE_DATE BETWEEN PER.EFFECTIVE_START_DATE
124 AND PER.EFFECTIVE_END_DATE;
125
126 CURSOR csr_Absence_Hourly_Rate IS
127 SELECT PAA.ABS_INFORMATION1 AS Hourly_Rate --Daily Rate Calculation
128 FROM PER_ABSENCE_ATTENDANCES PAA
129 WHERE PAA.ABSENCE_ATTENDANCE_ID =l_abs_attendance_id;
130
131
132 CURSOR csr_Absence_Salary_Rate IS
133 SELECT PAA.ABS_INFORMATION2 AS Salary_Rate --Daily Rate Calculation
134 FROM PER_ABSENCE_ATTENDANCES PAA
135 WHERE PAA.ABSENCE_ATTENDANCE_ID =l_abs_attendance_id;
136
137
138 CURSOR csr_abs_attendance_id (l_asg_id number, l_eff_dt date, l_inp_val_name varchar2, l_start_dt date, l_end_dt date) IS
139 SELECT eev1.screen_entry_value screen_entry_value
140 FROM per_all_assignments_f asg1
141 ,per_all_assignments_f asg2
142 ,per_all_people_f per
143 ,pay_element_links_f el
144 ,pay_element_types_f et
145 ,pay_input_values_f iv1
146 ,pay_element_entries_f ee
147 ,pay_element_entry_values_f eev1
148 WHERE asg1.assignment_id = l_asg_id
149 AND l_eff_dt BETWEEN asg1.effective_start_date AND asg1.effective_end_date
150 AND l_eff_dt BETWEEN asg2.effective_start_date AND asg2.effective_end_date
151 AND per.person_id = asg1.person_id
152 AND asg2.person_id = per.person_id
153 --AND asg2.primary_flag = 'Y'
154 AND asg1.assignment_id=asg2.assignment_id
155 AND et.element_name = 'Sickness Details'
156 AND et.legislation_code = 'SE'
157 --OR et.business_group_id=3261 ) --checking for the business group, it should be removed
158 AND iv1.element_type_id = et.element_type_id
159 AND iv1.name = l_inp_val_name
160 AND el.business_group_id = per.business_group_id
161 AND el.element_type_id = et.element_type_id
162 AND ee.assignment_id = asg2.assignment_id
163 AND ee.element_link_id = el.element_link_id
164 AND eev1.element_entry_id = ee.element_entry_id
165 AND eev1.input_value_id = iv1.input_value_id
166 AND ee.effective_start_date >= l_start_dt
167 AND ee.effective_end_date <= l_end_dt
168 AND eev1.effective_start_date <= l_start_dt
169 AND eev1.effective_end_date >= l_end_dt
170 AND ROWNUM < 2 ;
171
172
173
174 BEGIN
175
176 BEGIN
177 SELECT papf.person_id
178 ,papf.business_group_id
179 ,segment9
180 ,normal_hours
181 ,frequency
182 ,hourly_salaried_code
183 ,employee_category
184 INTO l_person_id
185 ,l_business_group_id
186 ,l_working_percentage
187 ,l_normal_hours
188 ,l_frequency
189 ,l_hour_sal
190 ,l_employee_category
191 FROM per_all_assignments_f paaf,
192 per_all_people_f papf,
193 hr_soft_coding_keyflex hsck
194 WHERE paaf.assignment_id = p_assignment_id
195 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
196 AND papf.person_id=paaf.person_id
197 AND p_effective_date BETWEEN paaf.effective_start_date
198 AND paaf.effective_end_date
199 AND p_effective_date BETWEEN papf.effective_start_date
200 AND papf.effective_end_date;
201
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 RETURN 0;
206 END;
207
208 OPEN csr_abs_attendance_id (p_assignment_id,p_effective_date,'CREATOR_ID',p_abs_start_date, p_abs_end_date);
209 FETCH csr_abs_attendance_id INTO l_abs_attendance_id;
210 CLOSE csr_abs_attendance_id;
211
212 IF l_hour_sal='H' THEN
213 OPEN csr_Absence_Hourly_Rate;
214 FETCH csr_Absence_Hourly_Rate INTO l_ab_hourly_rate;
215 CLOSE csr_Absence_Hourly_Rate;
216
217 OPEN csr_Person_Hourly_Rate;
218 FETCH csr_Person_Hourly_Rate INTO l_pe_hourly_rate;
219 CLOSE csr_Person_Hourly_Rate;
220
221 OPEN csr_Legal_Employer_Hourly_Rate;
222 FETCH csr_Legal_Employer_Hourly_Rate INTO l_le_hourly_rate;
223 CLOSE csr_Legal_Employer_Hourly_Rate;
224
225
226 l_gen_hourly_rate:=nvl(nvl(l_ab_hourly_rate,l_pe_hourly_rate),l_le_hourly_rate);
227
228 IF l_gen_hourly_rate='HRATE_OPTION1' THEN
229 p_hourly_rate_option1:='Y';
230 ELSIF l_gen_hourly_rate='HRATE_OPTION2' THEN
231 p_hourly_rate_option2:='Y';
232 ELSIF l_gen_hourly_rate='HRATE_OPTION3' THEN
233 p_hourly_rate_option3:='Y';
234 ELSIF l_gen_hourly_rate='HRATE_OPTION4' THEN
235 p_hourly_rate_option4:='Y';
236 ELSIF l_gen_hourly_rate='HRATE_OPTION5' THEN
237 p_hourly_rate_option5:='Y';
238 ELSIF l_gen_hourly_rate='HRATE_OPTION6' THEN
239 p_hourly_rate_option6:='Y';
240 ELSIF l_gen_hourly_rate='HRATE_OPTION7' THEN
241 p_hourly_rate_option7:='Y';
242 ELSIF l_gen_hourly_rate='HRATE_OPTION8' THEN
243 p_hourly_rate_option8:='Y';
244 ELSIF l_gen_hourly_rate='HRATE_OPTION9' THEN
245 p_hourly_rate_option9:='Y';
246 ELSIF l_gen_hourly_rate='HRATE_OPTION10' THEN
247 p_hourly_rate_option10:='Y';
248 ELSE
249 p_hourly_rate_option1 := null;
250 p_hourly_rate_option2 := null;
251 p_hourly_rate_option3 := null;
252 p_hourly_rate_option4 := null;
253 p_hourly_rate_option5 := null;
254 p_hourly_rate_option6 := null;
255 p_hourly_rate_option7 := null;
256 p_hourly_rate_option8 := null;
257 p_hourly_rate_option9 := null;
258 p_hourly_rate_option10 := null;
259 /*IF l_hour_sal IS NULL THEN
260 p_hourly_rate:=0;
261 ELSIF (l_hour_sal='S' AND l_frequency='W') OR (l_employee_category='WC' AND l_frequency='W') THEN
262 p_hourly_rate:=round(((p_Monthly_Pay*12)/(52*l_normal_hours)*l_working_percentage/100),2);
263 ELSE
264 p_hourly_rate:=0;*/
265 END IF;
266 ELSIF l_hour_sal='S' THEN
267
268 OPEN csr_Absence_Salary_Rate;
269 FETCH csr_Absence_Salary_Rate INTO l_ab_salary_rate;
270 CLOSE csr_Absence_Salary_Rate;
271
272 OPEN csr_Person_Salary_Rate;
273 FETCH csr_Person_Salary_Rate INTO l_pe_salary_rate;
274 CLOSE csr_Person_Salary_Rate;
275
276 OPEN csr_Legal_Employer_Salary_Rate;
277 FETCH csr_Legal_Employer_Salary_Rate INTO l_le_salary_rate;
278 CLOSE csr_Legal_Employer_Salary_Rate;
279
280
281 l_gen_salary_rate:=nvl(nvl(l_ab_salary_rate,l_pe_salary_rate),l_le_salary_rate);
282
283 IF l_gen_salary_rate='OPTION1' THEN
284 p_salary_rate_option1:='Y';
285 ELSIF l_gen_salary_rate='OPTION2' THEN
286 p_salary_rate_option2:='Y';
287 ELSIF l_gen_salary_rate='OPTION3' THEN
288 p_salary_rate_option3:='Y';
289 ELSIF l_gen_salary_rate='OPTION4' THEN
290 p_salary_rate_option4:='Y';
291 ELSIF l_gen_salary_rate='OPTION5' THEN
292 p_salary_rate_option5:='Y';
293 ELSIF l_gen_salary_rate='OPTION6' THEN
294 p_salary_rate_option6:='Y';
295 ELSIF l_gen_salary_rate='OPTION7' THEN
296 p_salary_rate_option7:='Y';
297 ELSIF l_gen_salary_rate='OPTION8' THEN
298 p_salary_rate_option8:='Y';
299 ELSIF l_gen_salary_rate='OPTION9' THEN
300 p_salary_rate_option9:='Y';
301 ELSIF l_gen_salary_rate='OPTION10' THEN
302 p_salary_rate_option10:='Y';
303 ELSE
304 p_salary_rate_option1 := null;
305 p_salary_rate_option2 := null;
306 p_salary_rate_option3 := null;
307 p_salary_rate_option4 := null;
308 p_salary_rate_option5 := null;
309 p_salary_rate_option6 := null;
310 p_salary_rate_option7 := null;
311 p_salary_rate_option8 := null;
312 p_salary_rate_option9 := null;
313 p_salary_rate_option10 := null;
314 IF (l_hour_sal='S' AND l_frequency='W') OR (l_employee_category='WC' AND l_frequency='W') THEN
315 p_salary_rate:=round(((p_Monthly_Pay*12)/(52*l_normal_hours)*l_working_percentage/100),2);
316 END IF;
317 END IF;
318
319 ELSE
320 p_hourly_rate_option1 := null;
321 p_hourly_rate_option2 := null;
322 p_hourly_rate_option3 := null;
323 p_hourly_rate_option4 := null;
324 p_hourly_rate_option5 := null;
325 p_hourly_rate_option6 := null;
326 p_hourly_rate_option7 := null;
327 p_hourly_rate_option8 := null;
328 p_hourly_rate_option9 := null;
329 p_hourly_rate_option10 := null;
330 p_salary_rate_option1 := null;
331 p_salary_rate_option2 := null;
332 p_salary_rate_option3 := null;
333 p_salary_rate_option4 := null;
334 p_salary_rate_option5 := null;
335 p_salary_rate_option6 := null;
336 p_salary_rate_option7 := null;
337 p_salary_rate_option8 := null;
338 p_salary_rate_option9 := null;
339 p_salary_rate_option10 := null;
340 p_hourly_rate:=0;
341 p_salary_rate:=0;
342 END IF;
343 p_normal_hours:=nvl(l_normal_hours,0);
344 p_working_perc:=nvl(l_working_percentage,0);
345 p_hour_sal:=l_hour_sal;
346 RETURN 1;
347 EXCEPTION
348 WHEN OTHERS THEN
349 RETURN 0;
350 END GET_HOURLY_RATE;
351
352 FUNCTION GET_GROUP(
353 p_assignment_id IN NUMBER
354 ,p_effective_date IN DATE
355 ,p_abs_start_date IN DATE
356 ,p_abs_end_date IN DATE
357 ,p_group_start_date1 OUT NOCOPY DATE
358 ,p_group_start_date2 OUT NOCOPY DATE
359 ,p_group_start_date3 OUT NOCOPY DATE
360 ,p_group_start_date4 OUT NOCOPY DATE
361 ,p_group_start_date5 OUT NOCOPY DATE
362 ,p_group_start_date6 OUT NOCOPY DATE
363 ,p_group_start_date7 OUT NOCOPY DATE
364 ,p_group_start_date8 OUT NOCOPY DATE
365 ,p_group_start_date9 OUT NOCOPY DATE
366 ,p_group_start_date10 OUT NOCOPY DATE
367 ,p_group_start_date11 OUT NOCOPY DATE
368 ,p_group_end_date1 OUT NOCOPY DATE
369 ,p_group_end_date2 OUT NOCOPY DATE
370 ,p_group_end_date3 OUT NOCOPY DATE
371 ,p_group_end_date4 OUT NOCOPY DATE
372 ,p_group_end_date5 OUT NOCOPY DATE
373 ,p_group_end_date6 OUT NOCOPY DATE
374 ,p_group_end_date7 OUT NOCOPY DATE
375 ,p_group_end_date8 OUT NOCOPY DATE
376 ,p_group_end_date9 OUT NOCOPY DATE
377 ,p_group_end_date10 OUT NOCOPY DATE
378 ,p_group_end_date11 OUT NOCOPY DATE
379 ,p_group_option1 OUT NOCOPY VARCHAR2
380 ,p_group_option2 OUT NOCOPY VARCHAR2
381 ,p_group_option3 OUT NOCOPY VARCHAR2
382 ,p_group_option4 OUT NOCOPY VARCHAR2
383 ,p_group_option5 OUT NOCOPY VARCHAR2
384 ,p_group_option6 OUT NOCOPY VARCHAR2
385 ,p_group_option7 OUT NOCOPY VARCHAR2
386 ,p_group_option8 OUT NOCOPY VARCHAR2
387 ,p_group_option9 OUT NOCOPY VARCHAR2
388 ,p_group_option10 OUT NOCOPY VARCHAR2
389 ,p_group_option11 OUT NOCOPY VARCHAR2
390 ,p_asg_hour_sal OUT NOCOPY VARCHAR2
391 )
392 RETURN NUMBER IS
393
394 l_st_date date;
395
396 CURSOR csr_absence(l_person_id Number) is
397 /*SELECT paa.absence_attendance_id
398 ,paa.date_start
399 ,nvl(paa.date_end,p_abs_end_date) date_end
400 ,paa.time_start
401 ,paa.time_end
402 ,DECODE(paa.date_start, paa.date_end, 1, (paa.date_end-paa.date_start)+1) AS days_diff
403 FROM per_absence_attendances paa,
404 per_absence_attendance_types pat
405 WHERE paa.person_id = l_person_id
406 AND ((paa.date_start between l_st_date and p_abs_end_date)
407 OR (paa.date_end between l_st_date and p_abs_end_date))
408 AND paa.date_start IS NOT NULL /*AND paa.date_end IS NOT NULL*/
409 /*AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
410 AND pat.absence_category IN ('S')
411 ORDER BY paa.date_end desc;*/
412
413 --Fix for bug no. 5355937
414
415 SELECT paa.absence_attendance_id
416 ,paa.date_start
417 --,nvl(paa.date_end,'31-jan-2000') date_end
418 ,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)) date_end
419 ,paa.time_start
420 ,paa.time_end
421 ,DECODE(paa.date_start,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)), 1,
422 (least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date))-paa.date_start)+1) AS days_diff
423 FROM per_absence_attendances paa,
424 per_absence_attendance_types pat
425 WHERE paa.person_id = l_person_id
426 /*AND paa.date_start >=p_abs_start_date
427 AND nvl(paa.date_end,p_abs_end_date)<=p_abs_end_date*/
428 AND ((paa.date_start between l_st_date and p_abs_end_date)
429 OR (paa.date_end between l_st_date and p_abs_end_date))
430 /*AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL*/
431 AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
432 AND pat.absence_category IN ('S')
433 AND paa.absence_attendance_id IN(
434 -- ORDER BY paa.date_end ;
435
436
437 SELECT eev1.screen_entry_value screen_entry_value
438 FROM per_all_assignments_f asg1
439 ,per_all_assignments_f asg2
440 ,per_all_people_f per
441 ,pay_element_links_f el
442 ,pay_element_types_f et
443 ,pay_input_values_f iv1
444 ,pay_element_entries_f ee
445 ,pay_element_entry_values_f eev1
446 WHERE asg1.assignment_id = p_assignment_id --34040 --l_asg_id
447 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
448 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
449 AND per.person_id = asg1.person_id
450 AND asg2.person_id = per.person_id
451 --AND asg2.primary_flag = 'Y'
452 AND asg1.assignment_id=asg2.assignment_id
453 AND et.element_name = 'Sickness Details'
454 AND et.legislation_code = 'SE'
455 --OR et.business_group_id=3261 ) --checking for the business group, it should be removed
456 AND iv1.element_type_id = et.element_type_id
457 AND iv1.name = 'CREATOR_ID'
458 AND el.business_group_id = per.business_group_id
459 AND el.element_type_id = et.element_type_id
460 AND ee.assignment_id = asg2.assignment_id
461 AND ee.element_link_id = el.element_link_id
462 AND eev1.element_entry_id = ee.element_entry_id
463 AND eev1.input_value_id = iv1.input_value_id
464 /*AND ee.effective_start_date >= p_abs_start_date
465 AND ee.effective_end_date <= p_abs_end_date
466 AND eev1.effective_start_date >= p_abs_start_date
467 AND eev1.effective_end_date <= p_abs_end_date*/
468 ) ORDER BY paa.date_end desc ;
469
470 TYPE l_date IS
471 TABLE OF DATE
472 INDEX BY BINARY_INTEGER;
473
474 TYPE l_char IS
475 TABLE OF char
476 INDEX BY BINARY_INTEGER;
477
478 l_start_date l_date;
479 l_end_date l_date;
480 l_gp_char l_char;
481 l_prev_start_date DATE :=NULL;
482 l_group_start_date DATE;
483 l_group_end_date DATE;
484 l_person_id NUMBER;
485 l_counter NUMBER :=1;
486
487 BEGIN
488 l_st_date :=to_date('01010001','DDMMYYYY');
489
490 FOR l_counter IN 1..10 LOOP
491 l_start_date(l_counter):=NULL;
492 l_end_date(l_counter):=NULL;
493 l_gp_char(l_counter):=NULL;
494 END LOOP;
495
496 BEGIN
497 SELECT papf.person_id ,
498 hourly_salaried_code
499 INTO l_person_id ,
500 p_asg_hour_sal
501 FROM per_all_assignments_f paaf,
502 per_all_people_f papf,
503 hr_soft_coding_keyflex hsck
504 WHERE paaf.assignment_id = p_assignment_id
505 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
506 AND papf.person_id=paaf.person_id
507 AND p_effective_date BETWEEN paaf.effective_start_date
508 AND paaf.effective_end_date
509 AND p_effective_date BETWEEN papf.effective_start_date
510 AND papf.effective_end_date;
511
512
513 EXCEPTION
514 WHEN OTHERS THEN
515 RETURN 0;
516 END;
517
518
519
520 FOR c_abs IN csr_absence(l_person_id) LOOP
521 IF (l_prev_start_date-c_abs.date_end) <= 5 OR (l_prev_start_date-c_abs.date_end) IS NULL THEN
522 IF (l_prev_start_date-c_abs.date_end) IS NULL THEN
523 l_group_end_date:=c_abs.date_end;
524 END IF;
525 ELSE
526
527 IF ((l_prev_start_date-c_abs.date_end) > 5) and (c_abs.date_end<p_abs_start_date) THEN
528 exit;
529 END IF;
530 l_group_start_date:=l_prev_start_date;
531 l_start_date(l_counter):=l_group_start_date;
532 l_end_date(l_counter):=l_group_end_date;
533 l_gp_char(l_counter):='Y';
534 l_group_end_date:=c_abs.date_end;
535 l_counter:=l_counter+1;
536 END IF;
537 l_prev_start_date:=c_abs.date_start;
538 END LOOP;
539 l_group_start_date:=l_prev_start_date;
540 l_start_date(l_counter):=l_group_start_date;
541 l_end_date(l_counter):=l_group_end_date;
542 l_gp_char(l_counter):='Y';
543 /* Fix for bug 5985088 */
544 IF l_group_end_date>=p_abs_start_date THEN
545 if l_counter >=1 then
546 p_group_start_date1:=l_start_date(1);
547 p_group_end_date1:=l_end_date(1);
548 p_group_option1:=l_gp_char(1);
549
550 end if;
551 if l_counter>=2 then
552 p_group_start_date2:=l_start_date(2);
553 p_group_end_date2:=l_end_date(2);
554 p_group_option2:=l_gp_char(2);
555 end if;
556 if l_counter>=3 then
557 p_group_start_date3:=l_start_date(3);
558 p_group_end_date3:=l_end_date(3);
559 p_group_option3:=l_gp_char(3);
560 end if;
561 if l_counter>=4 then
562 p_group_start_date4:=l_start_date(4);
563 p_group_end_date4:=l_end_date(4);
564 p_group_option4:=l_gp_char(4);
565 end if;
566 if l_counter>=5 then
567 p_group_start_date5:=l_start_date(5);
568 p_group_end_date5:=l_end_date(5);
569 p_group_option5:=l_gp_char(5);
570 end if;
571 IF l_counter>=6 then
572 p_group_start_date6:=l_start_date(6);
573 p_group_end_date6:=l_end_date(6);
574 p_group_option6:=l_gp_char(6);
575 end if;
576 if l_counter>=7 then
577 p_group_start_date7:=l_start_date(7);
578 p_group_end_date7:=l_end_date(7);
579 p_group_option7:=l_gp_char(7);
580 end if;
581 if l_counter>=8 then
582 p_group_start_date8:=l_start_date(8);
583 p_group_end_date8:=l_end_date(8);
584 p_group_option8:=l_gp_char(8);
585 end if;
586 if l_counter>=9 then
587 p_group_start_date1:=l_start_date(9);
588 p_group_end_date1:=l_end_date(9);
589 p_group_option9:=l_gp_char(9);
590 end if;
591 if l_counter>=10 then
592 p_group_start_date1:=l_start_date(10);
593 p_group_end_date1:=l_end_date(10);
594 p_group_option10:=l_gp_char(10);
595 end if;
596 END IF;
597 RETURN 1;
598 EXCEPTION
599 WHEN OTHERS THEN
600 RETURN 0;
601
602 END GET_GROUP;
603
604
605 FUNCTION GET_WAITING_HOURS(p_abs_hours IN VARCHAR2,
606 p_normal_hours IN VARCHAR2
607 )
608 RETURN NUMBER
609 IS
610 l_abs_hours number;
611 l_abs_mins number;
612 l_nor_hours number;
613 l_nor_mins number;
614 l_hours number;
615 l_mins number;
616 BEGIN
617
618 l_abs_hours:=substr(p_abs_hours,1,2);
619
620 l_abs_mins:=substr(p_abs_hours,4,2);
621 l_nor_hours:=substr(p_normal_hours,1,2);
622 l_nor_mins:=substr(p_normal_hours,4,2);
623
624 IF (l_abs_mins>l_nor_hours) THEN
625
626 l_mins:=(60-l_abs_mins);
627 l_hours:=(l_nor_hours-(l_abs_hours+1));
628 ELSE
629 l_mins:=(l_nor_mins-l_abs_mins);
630 l_hours:=(l_nor_hours-l_abs_hours);
631 END IF;
632
633 RETURN l_hours+(l_mins * .0167);
634 END GET_WAITING_HOURS;
635
636
637 FUNCTION CALCULATE_PAYMENT(
638 p_assignment_id IN NUMBER
639 ,p_effective_date IN DATE
640 ,p_assignment_action_id IN NUMBER
641 ,p_pay_start_date IN DATE
642 ,p_pay_end_date IN DATE
643 ,p_abs_start_date IN DATE
644 ,p_abs_end_date IN DATE
645 ,p_monthly_pay IN NUMBER
646 ,p_hourly_rate IN OUT NOCOPY NUMBER
647 ,p_tot_waiting_day_hours OUT NOCOPY NUMBER
648 ,p_tot_waiting_day OUT NOCOPY NUMBER
649 ,p_total_sickness_deduction OUT NOCOPY NUMBER
650 ,p_tot_sickness_ded_14_above OUT NOCOPY NUMBER
651 ,p_total_sick_pay OUT NOCOPY NUMBER
652 ,p_total_sick_pay_14_above OUT NOCOPY NUMBER
653 ,p_tot_waiting_day_ded OUT NOCOPY NUMBER
654 ,p_sickness_14_below_days OUT NOCOPY NUMBER
655 ,p_sickness_above_14_days OUT NOCOPY NUMBER
656 ,p_sickness_pay_hours_14_below OUT NOCOPY NUMBER
657 ,p_sickness_pay_hours_above_14 OUT NOCOPY NUMBER
658 ,p_sex OUT NOCOPY VARCHAR2
659 ,p_tot_sick_pay_days OUT NOCOPY NUMBER
660 ,p_asg_hour_sal OUT NOCOPY VARCHAR2
661 ,p_waiting_date OUT NOCOPY DATE
662 ,p_salary_rate IN OUT NOCOPY NUMBER
663 ,p_fourteenth_date OUT NOCOPY DATE
664 ,p_full_days OUT NOCOPY NUMBER
665 ,p_override_monthly_basic OUT NOCOPY NUMBER
666 ,p_override_monthly_basic_day OUT NOCOPY NUMBER
667 ,p_exceeds_14_days OUT NOCOPY VARCHAR2
668 ,p_sickness_after_14_days_month OUT NOCOPY NUMBER
669 ,p_group_calendar_days OUT NOCOPY NUMBER
670 ,p_group_working_days OUT NOCOPY NUMBER
671 ,p_group_working_hours OUT NOCOPY NUMBER
672
673
674 )
675 RETURN NUMBER IS
676
677 CURSOR csr_absence(l_person_id Number) is
678 /*SELECT paa.absence_attendance_id
679 ,paa.date_start
680 ,nvl(paa.date_end,p_abs_end_date) date_end
681 ,paa.time_start
682 ,paa.time_end
683 ,DECODE(paa.date_start, paa.date_end, 1, (paa.date_end-paa.date_start)+1) AS days_diff
684 FROM per_absence_attendances paa,
685 per_absence_attendance_types pat
686 WHERE paa.person_id = l_person_id
687 AND paa.date_start >=p_abs_start_date
688 AND nvl(paa.date_end,p_abs_end_date)<=p_abs_end_date
689 AND paa.date_start IS NOT NULL /*AND paa.date_end IS NOT NULL*/
690 /*AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
691 AND pat.absence_category IN ('S')
692 ORDER BY paa.date_end ;*/
693
694 SELECT paa.absence_attendance_id
695 ,paa.date_start
696 --,nvl(paa.date_end,'31-jan-2000') date_end
697 ,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)) date_end
698 ,paa.time_start
699 ,paa.time_end
700 ,DECODE(paa.date_start,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)), 1,
701 (least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date))-paa.date_start)+1) AS days_diff
702 FROM per_absence_attendances paa,
703 per_absence_attendance_types pat
704 WHERE paa.person_id = l_person_id
705 AND paa.date_start >=p_abs_start_date
706 AND paa.date_start<=p_abs_end_date
707 AND least(nvl(paa.date_end,p_abs_end_date),p_abs_end_date)<=p_abs_end_date
708 /*AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL*/
709 AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
710 AND pat.absence_category IN ('S')
711 /* Fix for bug 5383707 */
712 AND paa.absence_attendance_id IN(
713 -- ORDER BY paa.date_end ;
714
715
716 SELECT eev1.screen_entry_value screen_entry_value
717 FROM per_all_assignments_f asg1
718 ,per_all_assignments_f asg2
719 ,per_all_people_f per
720 ,pay_element_links_f el
721 ,pay_element_types_f et
722 ,pay_input_values_f iv1
723 ,pay_element_entries_f ee
724 ,pay_element_entry_values_f eev1
725 WHERE asg1.assignment_id = p_assignment_id --34040 --l_asg_id
726 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
727 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
728 AND per.person_id = asg1.person_id
729 AND asg2.person_id = per.person_id
730 --AND asg2.primary_flag = 'Y'
731 AND asg1.assignment_id=asg2.assignment_id
732 AND et.element_name = 'Sickness Details'
733 AND et.legislation_code = 'SE'
734 --OR et.business_group_id=3261 ) --checking for the business group, it should be removed
735 AND iv1.element_type_id = et.element_type_id
736 AND iv1.name = 'CREATOR_ID'
737 AND el.business_group_id = per.business_group_id
738 AND el.element_type_id = et.element_type_id
739 AND ee.assignment_id = asg2.assignment_id
740 AND ee.element_link_id = el.element_link_id
741 AND eev1.element_entry_id = ee.element_entry_id
742 AND eev1.input_value_id = iv1.input_value_id
743 /*AND ee.effective_start_date >= p_abs_start_date
744 AND ee.effective_end_date <= p_abs_end_date
745 AND eev1.effective_start_date >= p_abs_start_date
746 AND eev1.effective_end_date <= p_abs_end_date*/
747 ) ORDER BY paa.date_end /*desc*/ ; /*Bug Fix 5649509*/
748 --ORDER BY paa.date_end ;
749
750 CURSOR csr_check_override (l_asg_id number, l_eff_dt date, l_inp_val_name varchar2, l_start_dt date, l_end_dt date) IS
751 SELECT eev1.screen_entry_value screen_entry_value
752 FROM per_all_assignments_f asg1
753 ,per_all_assignments_f asg2
754 ,per_all_people_f per
755 ,pay_element_links_f el
756 ,pay_element_types_f et
757 ,pay_input_values_f iv1
758 ,pay_element_entries_f ee
759 ,pay_element_entry_values_f eev1
760 WHERE asg1.assignment_id = l_asg_id
761 AND l_eff_dt BETWEEN asg1.effective_start_date AND asg1.effective_end_date
762 AND l_eff_dt BETWEEN asg2.effective_start_date AND asg2.effective_end_date
763 AND per.person_id = asg1.person_id
764 AND asg2.person_id = per.person_id
765 AND asg2.primary_flag = 'Y'
766 AND et.element_name = 'Sickness Details'
767 AND et.legislation_code = 'SE'
768 --OR et.business_group_id=3261 ) --checking for the business group, it should be removed
769 AND iv1.element_type_id = et.element_type_id
770 AND iv1.name = l_inp_val_name
771 AND el.business_group_id = per.business_group_id
772 AND el.element_type_id = et.element_type_id
773 AND ee.assignment_id = asg2.assignment_id
774 AND ee.element_link_id = el.element_link_id
775 AND eev1.element_entry_id = ee.element_entry_id
776 AND eev1.input_value_id = iv1.input_value_id
777 AND ee.effective_start_date = l_start_dt
778 AND ee.effective_end_date = l_end_dt
779 AND eev1.effective_start_date = l_start_dt
780 AND eev1.effective_end_date = l_end_dt ;
781
782 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
783 IS
784 SELECT ue.creator_id
785 FROM ff_user_entities ue,
786 ff_database_items di
787 WHERE di.user_name = csr_v_Balance_Name
788 AND ue.user_entity_id = di.user_entity_id
789 AND ue.legislation_code = 'SE'
790 AND ue.business_group_id is NULL
791 AND ue.creator_type = 'B';
792
793 CURSOR csr_group_start_end_date
794 ( p_assignment_id NUMBER,
795 p_element_type_id NUMBER,
796 p_start_date_iv NUMBER,
797 p_waiting_day_iv NUMBER,
798 p_start_date DATE,
799 p_end_date DATE
800 ) IS
801 /* (p_element_name varchar2,
802 p_input_name varchar2,
803 p_group_start_date DATE,
804 p_group_end_date DATE,
805 p_assignment_id NUMBER) IS
806
807 SELECT SUM(RESULT_VALUE)--prrv1.* ,paa.assignment_id
808 /* prrv2.result_value fourteenth_date,
809 prrv3.result_value end_date*/
810 /*FROM pay_assignment_actions paa,
811 pay_payroll_actions ppa,
812 pay_run_results prr,
813 pay_run_result_values prrv1,
814 pay_input_values_f pivf,
815 pay_element_types_f petf
816 /* pay_run_result_values prrv2,
817 pay_run_result_values prrv3*/
818 /*WHERE ppa.effective_date BETWEEN p_group_start_date --'01-jun-1999' --p_report_start_date
819 /* AND p_group_end_date /*'01-jun-2000' */--p_report_end_date
820 /* AND ppa.payroll_action_id = paa.payroll_action_id
821 AND paa.assignment_id =p_assignment_id --21035 --p_assignment_id
822 AND paa.assignment_action_id = prr.assignment_action_id
823 AND prr.element_type_id = petf.element_type_id --62358 -- p_element_type_id
824 AND petf.element_name=p_element_name --'Sick Pay 1 to 14 days'
825 AND petf.element_type_id=pivf.element_type_id
826 AND pivf.element_type_id=prr.element_type_id
827 AND prr.run_result_id = prrv1.run_result_id
828 AND prrv1.input_value_id =pivf.input_value_id --139722 --p_input_value_id;
829 AND pivf.NAME=p_input_name; --'Waiting Day'*/
830
831 SELECT SUM(prrv2.RESULT_VALUE) /*fnd_date.canonical_to_date(prrv1.RESULT_VALUE),prrv2.RESULT_VALUE,
832 ADD_MONTHS('15-nov-2000',-12), '15-nov-2000' --*, prrv1.result_value*/
833 FROM pay_assignment_actions paa,
834 pay_payroll_actions ppa,
835 pay_run_results prr,
836 pay_run_result_values prrv1,
837 pay_run_result_values prrv2--,
838 -- pay_run_result_values prrv3
839 WHERE ppa.effective_date BETWEEN p_start_date --ADD_MONTHS('15-nov-2000',-12)
840 AND p_end_date --'15-nov-2000'
841 AND ppa.payroll_action_id = paa.payroll_action_id
842 AND paa.assignment_id = p_assignment_id --22145 -- p_assignment_id
843 AND paa.assignment_action_id = prr.assignment_action_id
844 AND prr.element_type_id = p_element_type_id--62358 --p_element_type_id
845 AND prr.run_result_id = prrv1.run_result_id
846 AND prrv1.input_value_id = p_start_date_iv --139718 --p_start_date_iv
847 AND prr.run_result_id = prrv2.run_result_id
848 AND prrv2.input_value_id = p_waiting_day_iv --139722 --p_full_day_iv
849 AND fnd_date.canonical_to_date(prrv1.result_value) BETWEEN p_start_date --ADD_MONTHS('15-nov-2000',-12)
850 AND p_end_date ; --'15-nov-2000'
851
852
853 CURSOR csr_element(p_element_name VARCHAR2)
854 IS
855 SELECT element_type_id FROM
856 pay_element_types_f
857 WHERE element_name=p_element_name
858 AND legislation_code='SE';
859
860 CURSOR csr_input(p_element_type_id number,p_input_value varchar2)
861 IS
862 SELECT input_value_id FROM
863 pay_input_values_f
864 WHERE name=p_input_value
865 AND element_type_id=p_element_type_id;
866
867 CURSOR csr_run_result_value(csr_v_assignment_id NUMBER,
868 csr_v_element_type_id NUMBER,
869 csr_v_input_type_id1 NUMBER,
870 --csr_v_input_type_id2 NUMBER,
871 csr_v_start_date DATE,
872 csr_v_end_date DATE)
873 IS
874 SELECT nvl(SUM(prrv1.RESULT_VALUE),0)
875 FROM pay_assignment_actions paa,
876 pay_payroll_actions ppa,
877 pay_run_results prr,
878 pay_run_result_values prrv1--,
879 --pay_run_result_values prrv2
880 WHERE ppa.effective_date BETWEEN csr_v_start_date --ADD_MONTHS('15-nov-2000',-12)
881 AND csr_v_end_date --'15-nov-2000'
882 AND ppa.payroll_action_id = paa.payroll_action_id
883 AND paa.assignment_id = csr_v_assignment_id --22145 -- p_assignment_id
884 AND paa.assignment_action_id = prr.assignment_action_id
885 AND prr.element_type_id = csr_v_element_type_id--62358 --p_element_type_id
886 AND prr.run_result_id = prrv1.run_result_id
887 AND prrv1.input_value_id = csr_v_input_type_id1; --139718 --p_start_date_iv
888 /*AND prr.run_result_id = prrv2.run_result_id
889 AND prrv2.input_value_id = csr_v_input_type_id2; --139718 --p_start_date_iv*/
890
891 CURSOR csr_global_value(csr_v_global_name VARCHAR2,csr_v_effective_date DATE )
892 IS
893 SELECT nvl(global_value,0) FROM ff_globals_f WHERE --ROWNUM<3
894 legislation_code='SE'
895 AND GLOBAL_NAME=csr_v_global_name --'SE_BASIC_AMOUNT'
896 AND csr_v_effective_date /*'28-feb-2007'*/ BETWEEN effective_start_date
897 AND effective_end_date;
898
899 CURSOR csr_absence_period(csr_v_assignment_id NUMBER,
900 csr_v_start_date DATE, csr_v_end_date DATE )
901 IS
902 SELECT greatest(fnd_date.canonical_to_date(peevf1.SCREEN_ENTRY_VALUE),csr_v_start_date) date_start,
903 least(fnd_date.canonical_to_date(peevf2.SCREEN_ENTRY_VALUE),csr_v_end_date) date_end
904 FROM per_all_assignments_f paaf
905 ,pay_element_types_f et
906 ,pay_element_entries_f ee
907 ,pay_element_entry_values_f peevf1
908 ,pay_element_entry_values_f peevf2
909 ,pay_input_values_f pivf1
910 ,pay_input_values_f pivf2
911 WHERE paaf.assignment_id = csr_v_assignment_id --38399 --p_assignment_id
912 AND csr_v_end_date /*p_effective_date*/ BETWEEN paaf.effective_start_date AND paaf.effective_end_date
913 AND et.element_name = 'Sickness Details'
914 AND et.legislation_code = 'SE'
915 AND ee.assignment_id = paaf.assignment_id
916 AND ee.ELEMENT_TYPE_ID = et.ELEMENT_TYPE_ID
917 AND ee.effective_start_date <= csr_v_end_date--p_payroll_start_date
918 AND ee.effective_end_date >= csr_v_start_date--p_payroll_end_date
919 and ee.ELEMENT_ENTRY_ID =peevf1.ELEMENT_ENTRY_ID
920 AND pivf1.element_type_id = et.element_type_id
921 AND pivf1.name = 'Start Date'
922 AND peevf1.input_value_id = pivf1.input_value_id
923 -- AND peevf1.SCREEN_ENTRY_VALUE='V'
924 and ee.ELEMENT_ENTRY_ID =peevf2.ELEMENT_ENTRY_ID
925 AND pivf2.element_type_id = et.element_type_id
926 AND pivf2.name = 'End Date'
927 AND peevf2.input_value_id = pivf2.input_value_id
928 AND csr_v_end_date /*p_payroll_start_date*/ BETWEEN et.effective_start_date AND et.effective_end_date
929 AND csr_v_end_date /*p_payroll_end_date*/ BETWEEN et.effective_start_date AND et.effective_end_date;
930
931 l_person_id number;
932 l_business_group_id number;
933 l_asg_hour_sal varchar2(10);
934 l_prev_start_date DATE :=NULL;
935 l_prev_end_date DATE:=NULL;
936 l_p_end_date DATE:=NULL;
937 l_prev_duration number:=0;
938 l_working_percentage NUMBER;
939 l_normal_start_date DATE;
940 l_normal_end_date DATE;
941 l_sickness_days NUMBER:=0;
942 l_total_sickness_days NUMBER:=0;
943 l_sickness_pay_days NUMBER:=0;
944 l_wrk_schd_return NUMBER;
945 l_start_time_char Varchar2(10) := '0';
946 l_end_time_char Varchar2(10) := '23.59';
947 l_duration NUMBER;
948 l_wrk_duration NUMBER;
949 l_include_event CHAR :='Y';
950 l_date_start DATE;
951 l_date_end DATE;
952 l_waiting_day NUMBER :=0;
953 l_employee_category varchar2(10);
954 l_normal_hours NUMBER;
955 l_sickness_deduction NUMBER :=0;
956 l_sick_pay NUMBER :=0;
957 l_waiting_day_deduction NUMBER :=0;
958 l_total_sickness_deduction NUMBER :=0;
959 l_total_sick_pay NUMBER :=0;
960 l_total_waiting_day_deduction NUMBER :=0;
961 l_normal_time_start varchar2(10);
962 l_normal_time_end varchar2(10);
963 l_time_start char(5);
964 l_prev_time_start char(5);
965 l_prev_time_end char(5);
966 l_prev_sicknness_days number;
967 l_time_end char(5);
968 l_return number;
969 l_waiting_date date;
970 l_waiting_day_hours number;
971 l_total_waiting_day NUMBER:=0;
972 l_total_waiting_day_hours number:=0;
973 l_loop_entered number:=0;
974 l_override_sickness_pay_hours NUMBER:=-1;
975 l_override_sickness_days NUMBER:=-1;
976 l_override_waiting_hours NUMBER:=-1;
977 l_sickness_deduction_14_above NUMBER:=0;
978 l_sickness_deduction_14_less NUMBER:=0;
979 l_sick_pay_14_above NUMBER:=0;
980 l_sick_pay_14_less NUMBER:=0;
981 l_tot_waiting_day_hours NUMBER:=0;
982 l_total_sickness_ded_14_above NUMBER:=0;
983 l_total_sick_pay_14_above NUMBER:=0;
984 l_prev_sickness_days NUMBER:=0;
985 l_curr_sickness_days NUMBER:=0;
986 l_sickness_14_below_days NUMBER:=0;
987 l_sick_pay_days_14_below_days NUMBER:=0;
988 l_sickness_above_14_days NUMBER :=0;
989 l_sickness_pay_hours_14_below NUMBER:=0;
990 l_sickness_pay_hours_above_14 NUMBER:=0;
991 l_sickness_pay_hours NUMBER:=0;
992 l_waiting_day_ded NUMBER:=0;
993 l_fourteenth_date DATE;
994 l_fourteenth_difference NUMBER;
995 l_sickness_last_date DATE;
996 l_group_start_date DATE;
997 lr_Get_Defined_Balance_Id NUMBER(10);
998 l_value NUMBER(10);
999 l_total_waiting_days NUMBER(10);
1000 l_start_date_iv NUMBER;
1001 l_waiting_day_iv NUMBER;
1002 l_element_type_id NUMBER;
1003 l_input_type_id NUMBER;
1004 --l_input_type_id2 NUMBER;
1005 l_days Char(1):='D';
1006 l_hours Char(1):='H';
1007 l_global_value NUMBER;
1008 l_sick_after_14_year_limit NUMBER;
1009 l_sick_after_14_year_taken NUMBER;
1010 l_total_sick_after_14 NUMBER;
1011 l_absence_period NUMBER:=0;
1012 BEGIN
1013
1014 BEGIN
1015 SELECT papf.person_id
1016 ,papf.business_group_id
1017 ,hourly_salaried_code
1018 ,segment9
1019 ,employee_category
1020 ,normal_hours
1021 ,time_normal_start
1022 ,time_normal_finish
1023 ,sex
1024 INTO l_person_id
1025 ,l_business_group_id
1026 ,l_asg_hour_sal
1027 ,l_working_percentage
1028 ,l_employee_category
1029 ,l_normal_hours
1030 ,l_normal_time_start
1031 ,l_normal_time_end
1032 ,p_sex
1033 FROM per_all_assignments_f paaf,
1034 per_all_people_f papf,
1035 hr_soft_coding_keyflex hsck
1036 WHERE paaf.assignment_id = p_assignment_id
1037 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
1038 AND papf.person_id=paaf.person_id
1039 AND p_effective_date BETWEEN paaf.effective_start_date
1040 AND paaf.effective_end_date
1041 AND p_effective_date BETWEEN papf.effective_start_date
1042 AND papf.effective_end_date;
1043 p_asg_hour_sal:=l_asg_hour_sal;
1044
1045 EXCEPTION
1046 WHEN OTHERS THEN
1047 l_person_id := null;
1048 l_business_group_id := null;
1049 l_asg_hour_sal := null;
1050 END;
1051
1052 IF l_asg_hour_sal='S' THEN
1053
1054 FOR c_abs IN csr_absence(l_person_id) LOOP
1055
1056 --if the period falls in previous payroll period
1057 IF c_abs.date_end<p_pay_start_date THEN
1058 --first entry need to check for waiting day
1059 IF l_loop_entered=0 THEN
1060 l_waiting_date:=GET_WAITING_DAY(p_assignment_id,c_abs.date_start,c_abs.date_end);
1061 l_group_start_date:=l_waiting_date;
1062 --Sickness day calculation
1063 l_duration:=c_abs.date_end-l_waiting_date+1;
1064 --if the sickness days is less than 14 days then
1065 IF (l_sickness_days+l_duration)<14 THEN
1066 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
1067 l_sickness_days:=l_sickness_days+l_duration;
1068
1069 l_sickness_last_date:=c_abs.date_end;
1070
1071 --if the period falls across 14 days
1072 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>=14) THEN
1073 --l_fourteenth_difference:=13-l_sickness_14_below_days;
1074 l_fourteenth_date:=l_waiting_date+13;
1075
1076
1077 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
1078 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration)-14);
1079 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
1080 --sickness days greater than 14 days
1081 ELSE
1082 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
1083 l_sickness_days:=l_sickness_days+l_duration;
1084 END IF;
1085 l_waiting_day_hours:=0;
1086 ELSE
1087
1088 l_duration:=least(c_abs.date_end,p_pay_end_date)-c_abs.date_start+1;
1089 --if the sickness days is less than 14 days then
1090 IF (l_sickness_days+l_duration)<=14 THEN
1091 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
1092 l_sickness_days:=l_sickness_days+l_duration;
1093
1094 l_sickness_last_date:=c_abs.date_end;
1095
1096 --if the period falls across 14 days
1097 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>14) THEN
1098 l_fourteenth_difference:=13-l_sickness_14_below_days;
1099 l_fourteenth_date:=c_abs.date_start + l_fourteenth_difference;
1100
1101
1102 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
1103 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration)-14);
1104 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
1105
1106 --sickness days greater than 14 days
1107 ELSE
1108 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
1109 l_sickness_days:=l_sickness_days+l_duration;
1110
1111 END IF;
1112 END IF;
1113 /*l_sickness_days:=l_sickness_days+(cbs.date_end-l_waiting_date);
1114 l_waiting_day_hours:=0; */
1115 --if the absence start date falls in previous payroll period
1116 --and absence end date fall is current payroll period
1117 ELSIF c_abs.date_start<p_pay_start_date AND c_abs.date_end<p_pay_end_date THEN
1118
1119 --first entry need to check for waiting day
1120 IF l_loop_entered=0 THEN
1121
1122 l_waiting_date:=GET_WAITING_DAY(p_assignment_id,c_abs.date_start,c_abs.date_end);
1123 l_group_start_date:=l_waiting_date;
1124 --Sickness days calculation
1125 --l_duration:=c_abs.date_end-l_waiting_date;
1126 l_duration:=least(c_abs.date_end,p_abs_end_date) -l_waiting_date+1;
1127 --if the sickness days is less than 14 days then
1128 IF (l_sickness_days+l_duration)<14 THEN
1129
1130 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
1131 l_sickness_days:=l_sickness_days+l_duration;
1132
1133 l_sickness_last_date:=c_abs.date_end;
1134
1135 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1136 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1137 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1138 CLOSE csr_check_override;
1139
1140 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
1141
1142 --sickness pay hours calculation
1143 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1144 ( p_assignment_id, l_hours, l_include_event,
1145 greatest(p_pay_start_date,l_waiting_date+1), c_abs.date_end, replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1146 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
1147 l_end_time_char)),':','.'), l_wrk_duration
1148 );
1149
1150 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
1151
1152 ELSE
1153
1154 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
1155 l_override_sickness_pay_hours:=-1;
1156 END IF;
1157 /* Calculating the Sick Pay days less than 14 days*/
1158 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1159 ( p_assignment_id, l_days, l_include_event,
1160 greatest(p_pay_start_date,l_waiting_date+1), c_abs.date_end, replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1161 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
1162 l_end_time_char)),':','.'), l_wrk_duration
1163 );
1164 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
1165 --if the period falls across 14 days
1166 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>=14) THEN
1167
1168 --l_fourteenth_difference:=14-l_sickness_14_below_days;
1169 --l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
1170 l_fourteenth_date:=l_waiting_date+13;
1171
1172 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
1173 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration)-14);
1174
1175 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1176 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1177 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1178 CLOSE csr_check_override;
1179
1180 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
1181
1182 --check whether the end period for 14 days fall within payroll period
1183 IF (c_abs.date_start + 14-l_sickness_days) > p_pay_start_date THEN
1184
1185 --sickness pay hours calculation
1186 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1187 ( p_assignment_id, l_hours, l_include_event,
1188 greatest(p_pay_start_date,l_waiting_date+1), (c_abs.date_start + 14-(l_sickness_days)+1),
1189 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1190 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
1191 );
1192
1193 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
1194 END IF;
1195 ELSE
1196
1197 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
1198 l_override_sickness_pay_hours:=-1;
1199 END IF;
1200
1201 /* Calculating the Sick Pay days less than 14 days*/
1202 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1203 ( p_assignment_id, l_days, l_include_event,
1204 greatest(p_pay_start_date,l_waiting_date+1), (c_abs.date_start + 14-(l_sickness_days)+1),
1205 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1206 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
1207 );
1208 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
1209
1210 --Checking Override input value for Sick Pay Days above 14 days in Hours
1211 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
1212 FETCH csr_check_override INTO l_override_sickness_days;
1213 CLOSE csr_check_override;
1214
1215 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
1216 --sickness pay hours calculation
1217
1218 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1219 ( p_assignment_id, l_hours, l_include_event,
1220 greatest((c_abs.date_start + 14-l_sickness_days+1),p_pay_start_date) ,
1221 c_abs.date_end,replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
1222 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
1223 );
1224 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
1225
1226
1227 ELSE
1228 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
1229 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
1230 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
1231 l_override_sickness_pay_hours:=-1;
1232 END IF;
1233
1234 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
1235 --sickness days greater than 14 days
1236 ELSE
1237
1238 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
1239 l_sickness_days:=l_sickness_days+l_duration;
1240
1241 --Checking Override input value for Sick Pay Days above 14 days in Hours
1242 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
1243 FETCH csr_check_override INTO l_override_sickness_days;
1244 CLOSE csr_check_override;
1245
1246 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
1247
1248 --sickness pay hours calculation
1249 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1250 ( p_assignment_id, l_hours, l_include_event,
1251 greatest(p_pay_start_date,l_waiting_date+1), c_abs.date_start,
1252 replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
1253 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
1254 );
1255 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
1256
1257
1258 ELSE
1259 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
1260 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
1261 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
1262 l_override_sickness_pay_hours:=-1;
1263 END IF;
1264
1265 END IF;
1266 IF l_waiting_date>=p_pay_start_date THEN
1267 --Checking Override input value for waiting day
1268 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override Waiting Day in Hours',c_abs.date_start, c_abs.date_end);
1269 FETCH csr_check_override INTO l_override_waiting_hours;
1270 CLOSE csr_check_override;
1271
1272 IF l_override_waiting_hours =-1 OR l_override_waiting_hours IS NULL THEN
1273 IF (l_waiting_date<>c_abs.date_end) THEN
1274 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1275 ( p_assignment_id, l_hours, l_include_event,
1276 l_waiting_date, l_waiting_date, replace(nvl(c_abs.time_start,
1277 nvl(l_normal_time_start,l_start_time_char)),':','.'),
1278 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
1279 );
1280 ELSE
1281 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1282 ( p_assignment_id, l_hours, l_include_event,
1283 l_waiting_date, l_waiting_date, replace(nvl(c_abs.time_start,
1284 nvl(l_normal_time_start,l_start_time_char)),':','.'),
1285 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,l_end_time_char)),':','.'), l_wrk_duration
1286 );
1287 END IF;
1288 l_waiting_day_hours:=l_wrk_duration;
1289
1290 ELSE
1291
1292 l_waiting_day_hours:=l_override_waiting_hours;
1293 l_override_waiting_hours:=-1;
1294 END IF;
1295 ELSE
1296 l_waiting_day_hours:=0;
1297 END IF;
1298
1299 ELSE
1300 l_duration:=least(c_abs.date_end,p_pay_end_date)-c_abs.date_start+1;
1301 --if the sickness days is less than 14 days then
1302 IF (l_sickness_days+l_duration)<=14 THEN
1303
1304 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
1305 l_sickness_days:=l_sickness_days+l_duration;
1306
1307 l_sickness_last_date:=c_abs.date_end;
1308
1309 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1310 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1311 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1312 CLOSE csr_check_override;
1313
1314 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
1315
1316 --sickness pay hours calculation
1317 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1318 ( p_assignment_id, l_hours, l_include_event,
1319 greatest(p_pay_start_date,c_abs.date_start), c_abs.date_end, replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1320 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
1321 l_end_time_char)),':','.'), l_wrk_duration
1322 );
1323
1324 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
1325
1326 ELSE
1327
1328 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
1329 l_override_sickness_pay_hours:=-1;
1330 END IF;
1331 /* Calculating the Sick Pay days less than 14 days*/
1332 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1333 ( p_assignment_id, l_days, l_include_event,
1334 greatest(p_pay_start_date,c_abs.date_start), c_abs.date_end, replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1335 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
1336 l_end_time_char)),':','.'), l_wrk_duration
1337 );
1338 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
1339
1340 --if the period falls across 14 days
1341 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>14) THEN
1342
1343 l_fourteenth_difference:=13-l_sickness_14_below_days;
1344 l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
1345 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
1346 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration)-14);
1347
1348 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1349 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1350 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1351 CLOSE csr_check_override;
1352
1353 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
1354
1355 --check whether the end period for 14 days fall within payroll period
1356 IF (c_abs.date_start + 14-l_sickness_days) > p_pay_start_date THEN
1357
1358 --sickness pay hours calculation
1359 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1360 ( p_assignment_id, l_hours, l_include_event,
1361 greatest(p_pay_start_date,c_abs.date_start), (c_abs.date_start + 14-(l_sickness_days+1)),
1362 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1363 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_duration
1364 );
1365 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_duration;
1366 END IF;
1367 ELSE
1368
1369 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
1370 l_override_sickness_pay_hours:=-1;
1371 END IF;
1372 /* Calculating the Sick Pay days less than 14 days*/
1373 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1374 ( p_assignment_id, l_days, l_include_event,
1375 greatest(p_pay_start_date,c_abs.date_start), (c_abs.date_start + 14-(l_sickness_days+1)),
1376 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1377 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_duration
1378 );
1379 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
1380 --Checking Override input value for Sick Pay Days above 14 days in Hours
1381 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
1382 FETCH csr_check_override INTO l_override_sickness_days;
1383 CLOSE csr_check_override;
1384
1385 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
1386 --sickness pay hours calculation
1387
1388 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1389 ( p_assignment_id, l_hours, l_include_event,
1390 greatest((c_abs.date_start + 14-l_sickness_days+1),p_pay_start_date) ,
1391 c_abs.date_end,replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
1392 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
1393 );
1394 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
1395
1396
1397 ELSE
1398 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
1399 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
1400 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
1401 l_override_sickness_pay_hours:=-1;
1402 END IF;
1403
1404 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
1405 --sickness days greater than 14 days
1406 ELSE
1407
1408 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
1409 l_sickness_days:=l_sickness_days+l_duration;
1410
1411 --Checking Override input value for Sick Pay Days above 14 days in Hours
1412 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
1413 FETCH csr_check_override INTO l_override_sickness_days;
1414 CLOSE csr_check_override;
1415
1416 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
1417
1418 --sickness pay hours calculation
1419 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1420 ( p_assignment_id, l_hours, l_include_event,
1421 greatest(p_pay_start_date,c_abs.date_start), c_abs.date_start,
1422 replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
1423 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
1424 );
1425 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
1426
1427 ELSE
1428 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
1429 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
1430 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
1431 l_override_sickness_pay_hours:=-1;
1432 END IF;
1433
1434 END IF;
1435 END IF;
1436 --if the absence start date falls in previous period and absence end date falls in
1437 --next payroll period
1438 ELSIF c_abs.date_start<p_pay_start_date AND c_abs.date_end>p_pay_end_date THEN
1439
1440 --first entry need to check for waiting day
1441 IF l_loop_entered=0 THEN
1442
1443 l_waiting_date:=GET_WAITING_DAY(p_assignment_id,c_abs.date_start,c_abs.date_end);
1444 l_group_start_date:=l_waiting_date;
1445 --Sickness days calculation
1446 l_duration:=c_abs.date_end-l_waiting_date+1;
1447 --if the sickness days is less than 14 days then
1448 IF (l_sickness_days+l_duration)<14 THEN
1449
1450 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
1451 l_sickness_days:=l_sickness_days+l_duration;
1452
1453 l_sickness_last_date:=c_abs.date_end;
1454
1455 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1456 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1457 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1458 CLOSE csr_check_override;
1459
1460 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
1461
1462 --sickness pay hours calculation
1463 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1464 ( p_assignment_id, l_hours, l_include_event,
1465 greatest(p_pay_start_date,l_waiting_date+1), p_pay_end_date, replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1466 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
1467 l_end_time_char)),':','.'), l_wrk_duration
1468 );
1469 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
1470
1471 ELSE
1472
1473 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
1474 l_override_sickness_pay_hours:=-1;
1475 END IF;
1476 /* Calculating the Sick Pay days less than 14 days*/
1477 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1478 ( p_assignment_id, l_days, l_include_event,
1479 greatest(p_pay_start_date,l_waiting_date+1), p_pay_end_date, replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1480 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
1481 l_end_time_char)),':','.'), l_wrk_duration
1482 );
1483 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
1484 --if the period falls across 14 days
1485 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>=14) THEN
1486
1487 --l_fourteenth_difference:=14-l_sickness_14_below_days;
1488 --l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
1489 l_fourteenth_date:=l_waiting_date+13;
1490
1491 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
1492 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration)-14);
1493 --l_sickness_14_below_days:=14;
1494 --l_sickness_above_14_days:=(l_duration-14);
1495
1496 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1497 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1498 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1499 CLOSE csr_check_override;
1500
1501 IF l_override_sickness_pay_hours = -1 or l_override_sickness_pay_hours is null THEN
1502 --check whether the end period for 14 days fall within payroll period
1503 IF (c_abs.date_start + 14-l_sickness_days) > p_pay_start_date THEN
1504
1505 --sickness pay hours calculation
1506 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1507 ( p_assignment_id, l_hours, l_include_event,
1508 greatest(p_pay_start_date,l_waiting_date+1), (c_abs.date_start + 14-(l_sickness_days+1+(c_abs.date_end-p_pay_end_date))),
1509 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1510 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
1511 );
1512
1513 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
1514 END IF;
1515 ELSE
1516 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
1517 l_override_sickness_pay_hours:=-1;
1518 END IF;
1519 /* Calculating the Sick Pay days less than 14 days*/
1520 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1521 ( p_assignment_id, l_days, l_include_event,
1522 greatest(p_pay_start_date,l_waiting_date+1), (c_abs.date_start + 14-(l_sickness_days+1+(c_abs.date_end-p_pay_end_date))),
1523 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1524 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
1525 );
1526 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
1527 --Checking Override input value for Sick Pay Days above 14 days in Hours
1528 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
1529 FETCH csr_check_override INTO l_override_sickness_days;
1530 CLOSE csr_check_override;
1531
1532 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
1533
1534 --sickness pay hours calculation
1535 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1536 ( p_assignment_id, l_hours, l_include_event,
1537 greatest((l_waiting_date + 14-(l_sickness_days)),p_pay_start_date) ,
1538 p_pay_end_date,replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
1539 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
1540 );
1541 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
1542
1543 ELSE
1544 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
1545 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
1546 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
1547 l_override_sickness_pay_hours:=-1;
1548 END IF;
1549
1550 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
1551
1552 --sickness days greater than 14 days
1553 ELSE
1554
1555 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
1556 l_sickness_days:=l_sickness_days+l_duration;
1557
1558 --Checking Override input value for Sick Pay Days above 14 days in Hours
1559 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
1560 FETCH csr_check_override INTO l_override_sickness_days;
1561 CLOSE csr_check_override;
1562
1563 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
1564
1565 --sickness pay hours calculation
1566 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1567 ( p_assignment_id, l_hours, l_include_event,
1568 greatest(p_pay_start_date,l_waiting_date+1), p_pay_end_date,
1569 replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
1570 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
1571 );
1572 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
1573
1574
1575 ELSE
1576 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
1577 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
1578 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
1579 l_override_sickness_pay_hours:=-1;
1580 END IF;
1581
1582 END IF;
1583 IF l_waiting_date>=p_pay_start_date THEN
1584 --Checking Override input value for waiting day
1585 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override Waiting Day in Hours',c_abs.date_start, c_abs.date_end);
1586 FETCH csr_check_override INTO l_override_waiting_hours;
1587 CLOSE csr_check_override;
1588
1589 IF l_override_waiting_hours =-1 OR l_override_waiting_hours IS NULL THEN
1590 IF (l_waiting_date<>c_abs.date_end) THEN
1591 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1592 ( p_assignment_id, l_hours, l_include_event,
1593 l_waiting_date, l_waiting_date, replace(nvl(c_abs.time_start,
1594 nvl(l_normal_time_start,l_start_time_char)),':','.'),
1595 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_duration
1596 );
1597 ELSE
1598 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1599 ( p_assignment_id, l_hours, l_include_event,
1600 l_waiting_date, l_waiting_date, replace(nvl(c_abs.time_start,
1601 nvl(l_normal_time_start,l_start_time_char)),':','.'),
1602 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,l_end_time_char)),':','.'), l_wrk_duration
1603 );
1604 END IF;
1605 l_waiting_day_hours:=l_wrk_duration;
1606
1607 ELSE
1608
1609 l_waiting_day_hours:=l_override_waiting_hours;
1610 l_override_waiting_hours:=-1;
1611 END IF;
1612 ELSE
1613 l_waiting_day_hours:=0;
1614 END IF;
1615
1616 ELSE
1617 l_duration:=least(c_abs.date_end,p_pay_end_date)-c_abs.date_start+1;
1618 --if the sickness days is less than 14 days then
1619 IF (l_sickness_days+l_duration)<=14 THEN
1620
1621 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
1622 l_sickness_days:=l_sickness_days+l_duration;
1623
1624 l_sickness_last_date:=c_abs.date_end;
1625
1626 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1627 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1628 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1629 CLOSE csr_check_override;
1630
1631 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
1632 --sickness pay hours calculation
1633 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1634 ( p_assignment_id, l_hours, l_include_event,
1635 greatest(p_pay_start_date,c_abs.date_start), p_pay_end_date, replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1636 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
1637 l_end_time_char)),':','.'), l_wrk_duration
1638 );
1639 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
1640
1641 ELSE
1642
1643 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
1644 l_override_sickness_pay_hours:=-1;
1645 END IF;
1646 /* Calculating the Sick Pay days less than 14 days*/
1647 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1648 ( p_assignment_id, l_days, l_include_event,
1649 greatest(p_pay_start_date,c_abs.date_start), p_pay_end_date, replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1650 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
1651 l_end_time_char)),':','.'), l_wrk_duration
1652 );
1653 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
1654 --if the period falls across 14 days
1655 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>14) THEN
1656
1657 l_fourteenth_difference:=13-l_sickness_14_below_days;
1658 l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
1659 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
1660 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration)-14);
1661
1662 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1663 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1664 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1665 CLOSE csr_check_override;
1666
1667 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
1668 --check whether the end period for 14 days fall within payroll period
1669 IF (c_abs.date_start + 14-l_sickness_days) > p_pay_start_date THEN
1670
1671 --sickness pay hours calculation
1672 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1673 ( p_assignment_id, l_hours, l_include_event,
1674 greatest(p_pay_start_date,c_abs.date_start), (c_abs.date_start + 14-(l_sickness_days+1)),
1675 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1676 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
1677 );
1678
1679 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
1680 END IF;
1681 ELSE
1682 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
1683 l_override_sickness_pay_hours:=-1;
1684 END IF;
1685 /* Calculating the Sick Pay days less than 14 days*/
1686 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1687 ( p_assignment_id, l_days, l_include_event,
1688 greatest(p_pay_start_date,c_abs.date_start), (c_abs.date_start + 14-(l_sickness_days+1)),
1689 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1690 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
1691 );
1692 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
1693
1694 --Checking Override input value for Sick Pay Days above 14 days in Hours
1695 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
1696 FETCH csr_check_override INTO l_override_sickness_days;
1697 CLOSE csr_check_override;
1698
1699 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
1700
1701 --sickness pay hours calculation
1702 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1703 ( p_assignment_id, l_hours, l_include_event,
1704 greatest((c_abs.date_start + 14-l_sickness_days+1),p_pay_start_date) ,
1705 p_pay_end_date,replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
1706 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
1707 );
1708 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
1709
1710 ELSE
1711 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
1712 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
1713 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
1714 l_override_sickness_pay_hours:=-1;
1715 END IF;
1716
1717 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
1718
1719 --sickness days greater than 14 days
1720 ELSE
1721
1722 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
1723 l_sickness_days:=l_sickness_days+l_duration;
1724
1725 --Checking Override input value for Sick Pay Days above 14 days in Hours
1726 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
1727 FETCH csr_check_override INTO l_override_sickness_days;
1728 CLOSE csr_check_override;
1729
1730 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
1731
1732 --sickness pay hours calculation
1733 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1734 ( p_assignment_id, l_hours, l_include_event,
1735 greatest(p_pay_start_date,c_abs.date_start), p_pay_end_date,
1736 replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
1737 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
1738 );
1739 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
1740
1741
1742 ELSE
1743 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
1744 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
1745 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
1746 l_override_sickness_pay_hours:=-1;
1747 END IF;
1748
1749 END IF;
1750 END IF;
1751 --if absence start date falls in the current payroll period and the absence end date
1752 --falls in next payroll period
1753 ELSIF c_abs.date_start > p_pay_start_date AND c_abs.date_end > p_pay_end_date THEN
1754
1755 --first entry need to check for waiting day
1756
1757 IF l_loop_entered=0 THEN
1758
1759 l_waiting_date:=GET_WAITING_DAY(p_assignment_id,c_abs.date_start,c_abs.date_end);
1760 l_group_start_date:=l_waiting_date;
1761 --Sickness days calculation
1762 --l_duration:=c_abs.date_end-l_waiting_date;
1763 l_duration:=least(c_abs.date_end,p_pay_end_date)-l_waiting_date+1;
1764 --if the sickness days is less than 14 days then
1765 IF (l_sickness_days+l_duration)<14 THEN
1766
1767 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
1768 l_sickness_days:=l_sickness_days+l_duration;
1769
1770 l_sickness_last_date:=c_abs.date_end;
1771
1772 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1773 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1774 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1775 CLOSE csr_check_override;
1776
1777 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
1778 IF l_waiting_date<p_pay_end_date THEN
1779 --sickness pay hours calculation
1780
1781 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1782 ( p_assignment_id, l_hours, l_include_event,
1783 (l_waiting_date+1),p_pay_end_date, replace(nvl(l_normal_time_start,
1784 l_start_time_char),':','.'),replace(nvl(l_normal_time_end,
1785 l_end_time_char),':','.'), l_wrk_duration
1786 );
1787 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
1788
1789 END IF;
1790 ELSE
1791
1792 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
1793 l_override_sickness_pay_hours:=-1;
1794 END IF;
1795 /* Calculating the Sick Pay days less than 14 days*/
1796 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1797 ( p_assignment_id, l_days, l_include_event,
1798 (l_waiting_date+1),p_pay_end_date, replace(nvl(l_normal_time_start,
1799 l_start_time_char),':','.'),replace(nvl(l_normal_time_end,
1800 l_end_time_char),':','.'), l_wrk_duration
1801 );
1802
1803 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
1804 --if the period falls across 14 days
1805 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>=14) THEN
1806
1807 --l_fourteenth_difference:=14-l_sickness_14_below_days;
1808 --l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
1809 l_fourteenth_date:=l_waiting_date+13;
1810
1811 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
1812 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration)-14);
1813
1814 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1815 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1816 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1817 CLOSE csr_check_override;
1818
1819 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
1820
1821 --check whether the end period for 14 days fall within payroll period
1822 /*IF (c_abs.date_start + 14-l_sickness_days) > p_pay_start_date then*/
1823 --sickness pay hours calculation
1824 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1825 ( p_assignment_id, l_hours, l_include_event,
1826 l_waiting_date+1, least((l_waiting_date + 14-(l_sickness_days+1)),p_pay_end_date),
1827 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1828 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
1829 );
1830 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
1831
1832 /*END IF;*/
1833 ELSE
1834
1835 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
1836 l_override_sickness_pay_hours:=-1;
1837 END IF;
1838 /* Calculating the Sick Pay days less than 14 days*/
1839 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1840 ( p_assignment_id, l_days, l_include_event,
1841 l_waiting_date+1, least((l_waiting_date + 14-(l_sickness_days+1)),p_pay_end_date),
1842 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1843 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
1844 );
1845 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
1846 --Checking Override input value for Sick Pay Days above 14 days in Hours
1847 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
1848 FETCH csr_check_override INTO l_override_sickness_days;
1849 CLOSE csr_check_override;
1850
1851 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
1852 IF (l_waiting_date + 14-l_sickness_days+1)<=p_pay_end_date THEN
1853
1854 --sickness pay hours calculation
1855 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1856 ( p_assignment_id, l_hours, l_include_event,
1857 (l_waiting_date + 14-l_sickness_days+1),
1858 p_pay_end_date,replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
1859 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
1860 );
1861 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
1862
1863 END IF;
1864 ELSE
1865 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
1866 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
1867
1868 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
1869 l_override_sickness_pay_hours:=-1;
1870 END IF;
1871
1872 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
1873
1874 --sickness days greater than 14 days
1875 ELSE
1876
1877 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
1878 l_sickness_days:=l_sickness_days+l_duration;
1879
1880 --Checking Override input value for Sick Pay Days above 14 days in Hours
1881 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
1882 FETCH csr_check_override INTO l_override_sickness_days;
1883 CLOSE csr_check_override;
1884
1885 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
1886 IF l_waiting_date < p_pay_end_date THEN
1887
1888 --sickness pay hours calculation
1889 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1890 ( p_assignment_id, l_hours, l_include_event,
1891 least(p_pay_start_date,l_waiting_date+1), p_pay_end_date,
1892 replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
1893 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
1894 );
1895 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
1896
1897 END IF;
1898 ELSE
1899 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
1900 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
1901 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
1902 l_override_sickness_pay_hours:=-1;
1903 END IF;
1904
1905 END IF;
1906 --Checking Override input value for waiting day
1907 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override Waiting Day in Hours',c_abs.date_start, c_abs.date_end);
1908 FETCH csr_check_override INTO l_override_waiting_hours;
1909 CLOSE csr_check_override;
1910
1911 IF (l_override_waiting_hours =-1 OR l_override_waiting_hours IS NULL ) AND (l_waiting_date<=p_pay_end_date) THEN
1912
1913 if (l_waiting_date<>c_abs.date_end) then
1914 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1915 ( p_assignment_id, l_hours, l_include_event,
1916 l_waiting_date, l_waiting_date, replace(nvl(c_abs.time_start,
1917 nvl(l_normal_time_start,l_start_time_char)),':','.'),
1918 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
1919 );
1920 ELSE
1921 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1922 ( p_assignment_id, l_hours, l_include_event,
1923 l_waiting_date, l_waiting_date, replace(nvl(c_abs.time_start,
1924 nvl(l_normal_time_start,l_start_time_char)),':','.'),
1925 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,l_end_time_char)),':','.'), l_wrk_duration
1926 );
1927 END IF;
1928 l_waiting_day_hours:=l_wrk_duration;
1929
1930 ELSE
1931 l_waiting_day_hours:=l_override_waiting_hours;
1932 l_override_waiting_hours:=-1;
1933 END IF;
1934
1935 ELSE
1936 l_duration:=least(c_abs.date_end,p_pay_end_date)-c_abs.date_start+1;
1937 --if the sickness days is less than 14 days then
1938 IF (l_sickness_days+l_duration)<=14 THEN
1939
1940 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
1941 l_sickness_days:=l_sickness_days+l_duration;
1942
1943 l_sickness_last_date:=c_abs.date_end;
1944
1945 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1946 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1947 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1948 CLOSE csr_check_override;
1949
1950 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
1951 IF l_waiting_date<p_pay_end_date THEN
1952 --sickness pay hours calculation
1953
1954 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1955 ( p_assignment_id, l_hours, l_include_event,
1956 (c_abs.date_start),p_pay_end_date, replace(nvl(l_normal_time_start,
1957 l_start_time_char),':','.'),replace(nvl(l_normal_time_end,
1958 l_end_time_char),':','.'), l_wrk_duration
1959 );
1960 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
1961
1962 END IF;
1963 ELSE
1964
1965 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
1966 l_override_sickness_pay_hours:=-1;
1967 END IF;
1968 /* Calculating the Sick Pay days less than 14 days*/
1969 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1970 ( p_assignment_id, l_days, l_include_event,
1971 (c_abs.date_start),p_pay_end_date, replace(nvl(l_normal_time_start,
1972 l_start_time_char),':','.'),replace(nvl(l_normal_time_end,
1973 l_end_time_char),':','.'), l_wrk_duration
1974 );
1975 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
1976 --if the period falls across 14 days
1977 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>14) THEN
1978
1979 l_fourteenth_difference:=13-l_sickness_14_below_days;
1980 l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
1981 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
1982 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration)-14);
1983
1984 --Checking Override input value for Sick Pay Days 14 days and below in Hours
1985 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
1986 FETCH csr_check_override INTO l_override_sickness_pay_hours;
1987 CLOSE csr_check_override;
1988
1989 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
1990
1991 --check whether the end period for 14 days fall within payroll period
1992 /*IF (c_abs.date_start + 14-l_sickness_days) > p_pay_start_date then*/
1993 --sickness pay hours calculation
1994 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1995 ( p_assignment_id, l_hours, l_include_event,
1996 c_abs.date_start, least((c_abs.date_start + 14-(l_sickness_days+1)),p_pay_end_date),
1997 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
1998 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
1999 );
2000 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2001
2002 /*END IF;*/
2003 ELSE
2004
2005 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2006 l_override_sickness_pay_hours:=-1;
2007 END IF;
2008 /* Calculating the Sick Pay days less than 14 days*/
2009 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2010 ( p_assignment_id, l_days, l_include_event,
2011 c_abs.date_start, least((c_abs.date_start + 14-(l_sickness_days+1)),p_pay_end_date),
2012 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2013 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2014 );
2015 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2016 --Checking Override input value for Sick Pay Days above 14 days in Hours
2017 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2018 FETCH csr_check_override INTO l_override_sickness_days;
2019 CLOSE csr_check_override;
2020
2021 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2022 IF (l_waiting_date + 14-l_sickness_days+1)<=p_pay_end_date THEN
2023
2024 --sickness pay hours calculation
2025 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2026 ( p_assignment_id, l_hours, l_include_event,
2027 (c_abs.date_start + 14-l_sickness_days+1),
2028 p_pay_end_date,replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
2029 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
2030 );
2031 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2032
2033
2034 END IF;
2035 ELSE
2036 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
2037 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2038 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2039 l_override_sickness_pay_hours:=-1;
2040 END IF;
2041
2042 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
2043
2044 --sickness days greater than 14 days
2045 ELSE
2046
2047 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
2048 l_sickness_days:=l_sickness_days+l_duration;
2049
2050 --Checking Override input value for Sick Pay Days above 14 days in Hours
2051 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2052 FETCH csr_check_override INTO l_override_sickness_days;
2053 CLOSE csr_check_override;
2054
2055 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2056 IF l_waiting_date < p_pay_end_date THEN
2057
2058 --sickness pay hours calculation
2059 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2060 ( p_assignment_id, l_hours, l_include_event,
2061 least(p_pay_start_date,c_abs.date_start), p_pay_end_date,
2062 replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
2063 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
2064 );
2065 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2066
2067
2068 END IF;
2069 ELSE
2070 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
2071 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2072 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2073 l_override_sickness_pay_hours:=-1;
2074 END IF;
2075
2076 END IF;
2077 END IF;
2078 --if the absence falls within the payroll period
2079 ELSE
2080
2081 --first entry need to check for waiting day
2082 IF l_loop_entered=0 THEN
2083
2084 l_waiting_date:=GET_WAITING_DAY(p_assignment_id,c_abs.date_start,c_abs.date_end);
2085 l_group_start_date:=l_waiting_date;
2086 --Sickness days calculation
2087 l_duration:=c_abs.date_end-l_waiting_date+1;
2088 --if the sickness days is less than 14 days then
2089 IF (l_sickness_days+l_duration)<14 THEN
2090
2091 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
2092 l_sickness_days:=l_sickness_days+l_duration;
2093
2094 l_sickness_last_date:=c_abs.date_end;
2095
2096 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2097 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2098 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2099 CLOSE csr_check_override;
2100
2101 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2102 /*IF l_waiting_day<p_pay_end_date THEN */
2103 --sickness pay hours calculation
2104
2105 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2106 ( p_assignment_id, l_hours, l_include_event,
2107 (l_waiting_date+1),c_abs.date_end, replace(nvl(l_normal_time_start,
2108 l_start_time_char),':','.'),replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
2109 l_end_time_char)),':','.'), l_wrk_duration
2110 );
2111 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2112
2113 /*END IF;*/
2114 ELSE
2115
2116 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2117 l_override_sickness_pay_hours:=-1;
2118 END IF;
2119 /* Calculating the Sick Pay days less than 14 days*/
2120 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2121 ( p_assignment_id, l_days, l_include_event,
2122 (l_waiting_date+1),c_abs.date_end, replace(nvl(l_normal_time_start,
2123 l_start_time_char),':','.'),replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
2124 l_end_time_char)),':','.'), l_wrk_duration
2125 );
2126 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2127 --if the period falls across 14 days
2128 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>=14) THEN
2129
2130 --l_fourteenth_difference:=14-l_sickness_14_below_days;
2131 --l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
2132 l_fourteenth_date:=l_waiting_date+13;
2133
2134 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
2135 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration)-14);
2136
2137 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2138 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2139 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2140 CLOSE csr_check_override;
2141
2142 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2143
2144 --check whether the end period for 14 days fall within payroll period
2145 /*IF (c_abs.date_start + 14-l_sickness_days) > p_pay_start_date then*/
2146 --sickness pay hours calculation
2147 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2148 ( p_assignment_id, l_hours, l_include_event,
2149 l_waiting_date+1, least(l_fourteenth_date/*(l_waiting_date + 14-(l_sickness_days+1))*/,p_pay_end_date),
2150 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2151 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2152 );
2153 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2154
2155 /*END IF;*/
2156 ELSE
2157
2158 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2159 l_override_sickness_pay_hours:=-1;
2160 END IF;
2161 /* Calculating the Sick Pay days less than 14 days*/
2162 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2163 ( p_assignment_id, l_days, l_include_event,
2164 l_waiting_date+1, least(l_fourteenth_date/*(l_waiting_date + 14-(l_sickness_days+1))*/,p_pay_end_date),
2165 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2166 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2167 );
2168 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2169 --Checking Override input value for Sick Pay Days above 14 days in Hours
2170 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2171 FETCH csr_check_override INTO l_override_sickness_days;
2172 CLOSE csr_check_override;
2173
2174 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2175
2176 /*IF (l_waiting_day + 14-l_sickness_days+1)<=p_pay_end_date THEN */
2177 --sickness pay hours calculation
2178 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2179 ( p_assignment_id, l_hours, l_include_event,
2180 (l_fourteenth_date+1/*l_waiting_date + 14-l_sickness_days+1*/),
2181 c_abs.date_end,replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
2182 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
2183 );
2184 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2185
2186 /*END IF;*/
2187 ELSE
2188 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
2189 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2190 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2191 l_override_sickness_pay_hours:=-1;
2192 END IF;
2193
2194 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
2195 --sickness days greater than 14 days
2196 ELSE
2197
2198 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
2199 l_sickness_days:=l_sickness_days+l_duration;
2200
2201 --Checking Override input value for Sick Pay Days above 14 days in Hours
2202 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2203 FETCH csr_check_override INTO l_override_sickness_days;
2204
2205
2206 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2207 IF l_waiting_date < p_pay_end_date THEN
2208
2209 --sickness pay hours calculation
2210 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2211 ( p_assignment_id, l_hours, l_include_event,
2212 (l_waiting_date+1), c_abs.date_end,
2213 replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
2214 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
2215 );
2216 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2217
2218 END IF;
2219 ELSE
2220 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
2221 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2222 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2223 l_override_sickness_pay_hours:=-1;
2224 END IF;
2225
2226 END IF;
2227 --Checking Override input value for waiting day
2228 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override Waiting Day in Hours',c_abs.date_start, c_abs.date_end);
2229 FETCH csr_check_override INTO l_override_waiting_hours;
2230 CLOSE csr_check_override;
2231
2232 IF (l_override_waiting_hours =-1 OR l_override_waiting_hours IS NULL) AND (l_waiting_date<=p_pay_end_date) THEN
2233 IF (l_waiting_date<>c_abs.date_end) THEN
2234 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2235 ( p_assignment_id, l_hours, l_include_event,
2236 l_waiting_date, l_waiting_date, replace(nvl(c_abs.time_start,
2237 nvl(l_normal_time_start,l_start_time_char)),':','.'),
2238 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2239 );
2240 ELSE
2241
2242 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2243 ( p_assignment_id, l_hours, l_include_event,
2244 l_waiting_date, l_waiting_date, replace(nvl(c_abs.time_start,
2245 nvl(l_normal_time_start,l_start_time_char)),':','.'),
2246 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,l_end_time_char)),':','.'), l_wrk_duration
2247 );
2248 END IF;
2249
2250 l_waiting_day_hours:=l_wrk_duration;
2251
2252 ELSE
2253
2254 l_waiting_day_hours:=l_override_waiting_hours;
2255 l_override_waiting_hours:=-1;
2256 END IF;
2257
2258 ELSE
2259 l_duration:=least(c_abs.date_end,p_pay_end_date)-c_abs.date_start+1;
2260 --if the sickness days is less than 14 days then
2261 IF (l_sickness_days+l_duration)<=14 THEN
2262
2263 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
2264 l_sickness_days:=l_sickness_days+l_duration;
2265
2266 l_sickness_last_date:=c_abs.date_end;
2267
2268 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2269 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2270 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2271 CLOSE csr_check_override;
2272
2273 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2274 /*IF l_waiting_day<p_pay_end_date THEN */
2275 --sickness pay hours calculation
2276
2277 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2278 ( p_assignment_id, l_hours, l_include_event,
2279 (c_abs.date_start),c_abs.date_end, replace(nvl(l_normal_time_start,
2280 l_start_time_char),':','.'),replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
2281 l_end_time_char)),':','.'), l_wrk_duration
2282 );
2283 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2284
2285 /*END IF;*/
2286 ELSE
2287
2288 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2289 l_override_sickness_pay_hours:=-1;
2290 END IF;
2291 /* Calculating the Sick Pay days less than 14 days*/
2292 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2293 ( p_assignment_id, l_days, l_include_event,
2294 (c_abs.date_start),c_abs.date_end, replace(nvl(l_normal_time_start,
2295 l_start_time_char),':','.'),replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
2296 l_end_time_char)),':','.'), l_wrk_duration
2297 );
2298 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2299 --if the period falls across 14 days
2300 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>14) THEN
2301
2302 l_fourteenth_difference:=13-l_sickness_14_below_days;
2303 l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
2304 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
2305 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration)-14);
2306
2307 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2308 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2309 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2310 CLOSE csr_check_override;
2311
2312 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2313
2314 --check whether the end period for 14 days fall within payroll period
2315 /*IF (c_abs.date_start + 14-l_sickness_days) > p_pay_start_date then*/
2316 --sickness pay hours calculation
2317 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2318 ( p_assignment_id, l_hours, l_include_event,
2319 c_abs.date_start, least(l_fourteenth_date/*(c_abs.date_start + 14-(l_sickness_days+2))*/,p_pay_end_date),
2320 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2321 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2322 );
2323 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2324
2325 /*END IF;*/
2326 ELSE
2327
2328 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2329 l_override_sickness_pay_hours:=-1;
2330 END IF;
2331 /* Calculating the Sick Pay days less than 14 days*/
2332 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2333 ( p_assignment_id, l_days, l_include_event,
2334 c_abs.date_start, least(l_fourteenth_date/*(c_abs.date_start + 14-(l_sickness_days+2))*/,p_pay_end_date),
2335 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2336 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2337 );
2338 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2339 --Checking Override input value for Sick Pay Days above 14 days in Hours
2340 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2341 FETCH csr_check_override INTO l_override_sickness_days;
2342 CLOSE csr_check_override;
2343
2344 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2345
2346 /*IF (l_waiting_day + 14-l_sickness_days+1)<=p_pay_end_date THEN */
2347 --sickness pay hours calculation
2348 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2349 ( p_assignment_id, l_hours, l_include_event,
2350 (l_fourteenth_date/*c_abs.date_start + 14-l_sickness_days+2*/),
2351 c_abs.date_end,replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
2352 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
2353 );
2354 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2355
2356 /*END IF;*/
2357 ELSE
2358 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
2359 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2360 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2361 l_override_sickness_pay_hours:=-1;
2362 END IF;
2363
2364 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
2365 --sickness days greater than 14 days
2366 ELSE
2367
2368 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
2369 l_sickness_days:=l_sickness_days+l_duration;
2370
2371 --Checking Override input value for Sick Pay Days above 14 days in Hours
2372 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2373 FETCH csr_check_override INTO l_override_sickness_days;
2374 CLOSE csr_check_override;
2375
2376 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2377 IF l_waiting_date < p_pay_end_date THEN
2378
2379 --sickness pay hours calculation
2380 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2381 ( p_assignment_id, l_hours, l_include_event,
2382 (c_abs.date_start), c_abs.date_end,
2383 replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
2384 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
2385 );
2386 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2387
2388 END IF;
2389 ELSE
2390 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
2391 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2392 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2393 l_override_sickness_pay_hours:=-1;
2394 END IF;
2395
2396 END IF;
2397 END IF;
2398 END IF;
2399 l_loop_entered:=1;
2400 END LOOP;
2401 ELSE
2402 FOR c_abs IN csr_absence(l_person_id) LOOP
2403 IF l_loop_entered=0 THEN
2404 l_group_start_date:=c_abs.date_start;
2405 END IF;
2406 --if the absence ends before the payroll start date
2407 IF c_abs.date_end <p_pay_start_date THEN
2408
2409 --Sickness day calculation
2410 l_duration:=least(c_abs.date_end,p_pay_end_date)-c_abs.date_start+1;
2411
2412 --if the sickness days is less than 14 days then
2413 IF (l_sickness_days+l_duration)<=14 THEN
2414
2415 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
2416 l_sickness_days:=l_sickness_days+l_duration;
2417
2418 l_sickness_last_date:=c_abs.date_end;
2419
2420 --if the period falls across 14 days
2421 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>14) THEN
2422
2423 l_fourteenth_difference:=13-l_sickness_14_below_days;
2424 l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
2425 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
2426 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration+1)-14);
2427 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
2428
2429 --sickness days greater than 14 days
2430 ELSE
2431 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
2432 l_sickness_days:=l_sickness_days+l_duration;
2433 END IF;
2434
2435 --if the absence start date falls in previous payroll period
2436 --and absence end date fall is current payroll period
2437 ELSIF c_abs.date_start<p_pay_start_date AND c_abs.date_end<p_pay_end_date THEN
2438
2439 --Sickness days calculation
2440 l_duration:=least(c_abs.date_end,p_pay_end_date)-c_abs.date_start+1;
2441
2442 --if the sickness days is less than 14 days then
2443 IF (l_sickness_days+l_duration)<=14 THEN
2444
2445 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
2446 l_sickness_days:=l_sickness_days+l_duration;
2447
2448 l_sickness_last_date:=c_abs.date_end;
2449
2450 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2451 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2452 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2453 CLOSE csr_check_override;
2454
2455 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2456 --sickness pay hours calculation
2457 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2458 ( p_assignment_id, l_hours, l_include_event,
2459 p_pay_start_date, c_abs.date_end, l_start_time_char,
2460 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
2461 l_end_time_char)),':','.'), l_wrk_duration
2462 );
2463 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2464 ELSE
2465 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2466 l_override_sickness_pay_hours:=-1;
2467 END IF;
2468 /* Calculating the Sick Pay days less than 14 days*/
2469 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2470 ( p_assignment_id, l_days, l_include_event,
2471 p_pay_start_date, c_abs.date_end, l_start_time_char,
2472 replace(nvl(c_abs.time_end,nvl(l_normal_time_end,
2473 l_end_time_char)),':','.'), l_wrk_duration
2474 );
2475 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2476 --if the period falls across 14 days
2477 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>14) THEN
2478
2479 l_fourteenth_difference:=13-l_sickness_14_below_days;
2480 l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
2481 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
2482 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration+1)-14);
2483
2484 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2485 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2486 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2487 CLOSE csr_check_override;
2488
2489 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2490
2491 --check whether the end period for 14 days fall within payroll period
2492 IF (c_abs.date_start + 14-l_sickness_days) > p_pay_start_date THEN
2493
2494 --sickness pay hours calculation
2495 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2496 ( p_assignment_id, l_hours, l_include_event,
2497 p_pay_start_date, (c_abs.date_start + 14-l_sickness_days),
2498 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2499 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2500 );
2501 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2502 END IF;
2503 ELSE
2504 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2505 l_override_sickness_pay_hours:=-1;
2506 END IF;
2507 /* Calculating the Sick Pay days less than 14 days*/
2508 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2509 ( p_assignment_id, l_days, l_include_event,
2510 p_pay_start_date, (c_abs.date_start + 14-l_sickness_days),
2511 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2512 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2513 );
2514 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2515 --Checking Override input value for Sick Pay Days above 14 days in Hours
2516 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2517 FETCH csr_check_override INTO l_override_sickness_days;
2518 CLOSE csr_check_override;
2519
2520 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2521
2522 --sickness pay hours calculation
2523 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2524 ( p_assignment_id, l_hours, l_include_event,
2525 greatest((c_abs.date_start + 14-l_sickness_days+1),p_pay_start_date) ,
2526 c_abs.date_end,replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
2527 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
2528 );
2529 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2530
2531 ELSE
2532 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
2533 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2534 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2535 l_override_sickness_pay_hours:=-1;
2536 END IF;
2537 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
2538
2539 --sickness days greater than 14 days
2540 ELSE
2541
2542 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
2543 l_sickness_days:=l_sickness_days+l_duration;
2544
2545 --Checking Override input value for Sick Pay Days above 14 days in Hours
2546 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2547 FETCH csr_check_override INTO l_override_sickness_days;
2548 CLOSE csr_check_override;
2549
2550 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2551
2552 --sickness pay hours calculation
2553 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2554 ( p_assignment_id, l_hours, l_include_event,
2555 p_pay_start_date, c_abs.date_start,
2556 replace(nvl(l_normal_time_start,l_start_time_char),':','.'), replace(nvl(c_abs.time_end,
2557 nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
2558 );
2559 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2560
2561 ELSE
2562 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
2563 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2564 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2565 l_override_sickness_pay_hours:=-1;
2566 END IF;
2567
2568 END IF;
2569
2570 --if the absence start date falls in previous period and absence end date falls in
2571 --next payroll period
2572 ELSIF c_abs.date_start<p_pay_start_date AND c_abs.date_end>p_pay_end_date THEN
2573
2574 --Sickness days calculation
2575 l_duration:=least(c_abs.date_end,p_pay_end_date)-c_abs.date_start+1;
2576
2577 --if the sickness days is less than 14 days then
2578 IF (l_sickness_days+l_duration)<=14 THEN
2579
2580 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
2581 l_sickness_days:=l_sickness_days+l_duration;
2582
2583 l_sickness_last_date:=c_abs.date_end;
2584
2585 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2586 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2587 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2588 CLOSE csr_check_override;
2589
2590 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2591
2592 --sickness pay hours calculation
2593 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2594 ( p_assignment_id, l_hours, l_include_event,
2595 p_pay_start_date, p_pay_end_date, replace(nvl(l_normal_time_end,
2596 l_start_time_char),':','.'),replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2597 );
2598 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2599 ELSE
2600 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2601 l_override_sickness_pay_hours:=-1;
2602 END IF;
2603 /* Calculating the Sick Pay days less than 14 days*/
2604 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2605 ( p_assignment_id, l_days, l_include_event,
2606 p_pay_start_date, p_pay_end_date, replace(nvl(l_normal_time_end,
2607 l_start_time_char),':','.'),replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2608 );
2609 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2610 --if the period falls across 14 days
2611 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>14) THEN
2612
2613 l_fourteenth_difference:=13-l_sickness_14_below_days;
2614 l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
2615 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
2616 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration+1)-14);
2617
2618 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2619 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2620 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2621 CLOSE csr_check_override;
2622
2623 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2624
2625 --check whether the end period for 14 days fall within payroll period
2626 IF (c_abs.date_start + 14-l_sickness_days) > p_pay_start_date THEN
2627
2628 --sickness pay hours calculation
2629 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2630 ( p_assignment_id, l_hours, l_include_event,
2631 p_pay_start_date, (c_abs.date_start + 14-l_sickness_days),
2632 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2633 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2634 );
2635 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2636 END IF;
2637 ELSE
2638 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2639 l_override_sickness_pay_hours:=-1;
2640 END IF;
2641 /* Calculating the Sick Pay days less than 14 days*/
2642 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2643 ( p_assignment_id, l_days, l_include_event,
2644 p_pay_start_date, (c_abs.date_start + 14-l_sickness_days),
2645 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2646 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2647 );
2648 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2649 --Checking Override input value for Sick Pay Days above 14 days in Hours
2650 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2651 FETCH csr_check_override INTO l_override_sickness_days;
2652 CLOSE csr_check_override;
2653
2654 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2655
2656 --sickness pay hours calculation
2657 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2658 ( p_assignment_id, l_hours, l_include_event,
2659 greatest((c_abs.date_start + 14-l_sickness_days+1),p_pay_start_date) ,
2660 c_abs.date_end,replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2661 replace(nvl(l_normal_time_end, l_end_time_char),':','.'), l_wrk_duration
2662 );
2663 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2664
2665 ELSE
2666 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
2667 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2668 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2669 l_override_sickness_pay_hours:=-1;
2670 END IF;
2671 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
2672
2673 --sickness days greater than 14 days
2674 ELSE
2675
2676 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
2677 l_sickness_days:=l_sickness_days+l_duration;
2678
2679 --Checking Override input value for Sick Pay Days above 14 days in Hours
2680 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2681 FETCH csr_check_override INTO l_override_sickness_days;
2682 CLOSE csr_check_override;
2683
2684 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2685
2686 --sickness pay hours calculation
2687 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2688 ( p_assignment_id, l_hours, l_include_event,
2689 p_pay_start_date, p_pay_end_date,
2690 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2691 replace(nvl(l_normal_time_end, l_end_time_char),':','.'), l_wrk_duration
2692 );
2693 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2694
2695 ELSE
2696 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
2697 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2698 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2699 l_override_sickness_pay_hours:=-1;
2700 END IF;
2701
2702 END IF;
2703
2704
2705 --if absence start date falls in current payroll period and the absence end date
2706 --falls in next payroll period
2707 ELSIF c_abs.date_start > p_pay_start_date AND c_abs.date_end > p_pay_end_date THEN
2708
2709 --Sickness days calculation
2710 l_duration:=least(c_abs.date_end,p_pay_end_date)-c_abs.date_start+1;
2711
2712 --if the sickness days is less than 14 days then
2713 IF (l_sickness_days+l_duration)<=14 THEN
2714
2715 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
2716 l_sickness_days:=l_sickness_days+l_duration;
2717
2718 l_sickness_last_date:=c_abs.date_end;
2719
2720 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2721 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2722 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2723 CLOSE csr_check_override;
2724
2725 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2726
2727 --sickness pay hours calculation
2728 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2729 ( p_assignment_id, l_hours, l_include_event,
2730 c_abs.date_start, p_pay_end_date, replace(nvl(l_normal_time_end,
2731 l_start_time_char),':','.'),replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2732 );
2733 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2734 ELSE
2735 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2736 l_override_sickness_pay_hours:=-1;
2737 END IF;
2738 /* Calculating the Sick Pay days less than 14 days*/
2739 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2740 ( p_assignment_id, l_days, l_include_event,
2741 c_abs.date_start, p_pay_end_date, replace(nvl(l_normal_time_end,
2742 l_start_time_char),':','.'),replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2743 );
2744 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2745 --if the period falls across 14 days
2746 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>14) THEN
2747
2748 l_fourteenth_difference:=13-l_sickness_14_below_days;
2749 l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
2750 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
2751 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration+1)-14);
2752
2753 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2754 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2755 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2756 CLOSE csr_check_override;
2757
2758 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2759
2760 --check whether the end period for 14 days fall within payroll period
2761 /*IF (c_abs.date_start + 14-l_sickness_days) < p_pay_end_date then*/
2762 --sickness pay hours calculation
2763 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2764 ( p_assignment_id, l_hours, l_include_event,
2765 c_abs.date_start, least((c_abs.date_start + 14-l_sickness_days),
2766 p_pay_end_date),replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2767 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2768 );
2769 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2770 /*END IF;*/
2771 ELSE
2772 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2773 l_override_sickness_pay_hours:=-1;
2774 END IF;
2775 /* Calculating the Sick Pay days less than 14 days*/
2776 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2777 ( p_assignment_id, l_days, l_include_event,
2778 c_abs.date_start, least((c_abs.date_start + 14-l_sickness_days),
2779 p_pay_end_date),replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2780 replace(nvl(l_normal_time_end,l_end_time_char),':','.'), l_wrk_duration
2781 );
2782 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2783 --Checking Override input value for Sick Pay Days above 14 days in Hours
2784 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2785 FETCH csr_check_override INTO l_override_sickness_days;
2786 CLOSE csr_check_override;
2787
2788 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2789
2790 IF (c_abs.date_start + 14-l_sickness_days+1) < p_pay_end_date THEN
2791 --sickness pay hours calculation
2792 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2793 ( p_assignment_id, l_hours, l_include_event,
2794 c_abs.date_start + 14-l_sickness_days+1 ,
2795 p_pay_end_date,replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2796 replace(nvl(l_normal_time_end, l_end_time_char),':','.'), l_wrk_duration
2797 );
2798 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2799
2800 END IF;
2801 ELSE
2802 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
2803 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2804 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2805 l_override_sickness_pay_hours:=-1;
2806 END IF;
2807 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
2808
2809 --sickness days greater than 14 days
2810 ELSE
2811
2812 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
2813 l_sickness_days:=l_sickness_days+l_duration;
2814
2815 --Checking Override input value for Sick Pay Days above 14 days in Hours
2816 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2817 FETCH csr_check_override INTO l_override_sickness_days;
2818 CLOSE csr_check_override;
2819
2820 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2821
2822 --sickness pay hours calculation
2823 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2824 ( p_assignment_id, l_hours, l_include_event,
2825 c_abs.date_start, p_pay_end_date,
2826 replace(nvl(l_normal_time_start,l_start_time_char),':','.'),
2827 replace(nvl(l_normal_time_end, l_end_time_char),':','.'), l_wrk_duration
2828 );
2829 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2830
2831 ELSE
2832 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
2833 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2834 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2835 l_override_sickness_pay_hours:=-1;
2836 END IF;
2837
2838 END IF;
2839
2840
2841 --if the absence falls within the payroll period
2842 ELSE
2843
2844 --Sickness days calculation
2845 l_duration:=c_abs.date_end-c_abs.date_start+1;
2846
2847 --if the sickness days is less than 14 days then
2848 IF (l_sickness_days+l_duration)<=14 THEN
2849
2850 l_sickness_14_below_days:=l_sickness_14_below_days+l_duration;
2851 l_sickness_days:=l_sickness_days+l_duration;
2852
2853 l_sickness_last_date:=c_abs.date_end;
2854
2855 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2856 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2857 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2858 CLOSE csr_check_override;
2859
2860 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2861
2862 --sickness pay hours calculation
2863 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2864 ( p_assignment_id, l_hours, l_include_event,
2865 c_abs.date_start, c_abs.date_end, replace(nvl(c_abs.time_start,nvl(l_normal_time_end,
2866 l_start_time_char)),':','.'),replace(nvl(c_abs.time_end,nvl(l_normal_time_end,l_end_time_char)),':','.'), l_wrk_duration
2867 );
2868 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2869 ELSE
2870 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2871 l_override_sickness_pay_hours:=-1;
2872 END IF;
2873 /* Calculating the Sick Pay days less than 14 days*/
2874 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2875 ( p_assignment_id, l_days, l_include_event,
2876 c_abs.date_start, c_abs.date_end, replace(nvl(c_abs.time_start,nvl(l_normal_time_end,
2877 l_start_time_char)),':','.'),replace(nvl(c_abs.time_end,nvl(l_normal_time_end,l_end_time_char)),':','.'), l_wrk_duration
2878 );
2879 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2880 --if the period falls across 14 days
2881 ELSIF (l_sickness_days<=14) AND (l_sickness_days+l_duration>14) THEN
2882
2883 l_fourteenth_difference:=13-l_sickness_14_below_days;
2884 l_fourteenth_date:=c_abs.date_start+l_fourteenth_difference;
2885 l_sickness_14_below_days:=l_sickness_14_below_days+(14-l_sickness_days);
2886 l_sickness_above_14_days:=l_sickness_above_14_days+(l_sickness_days+(l_duration+1)-14);
2887
2888 --Checking Override input value for Sick Pay Days 14 days and below in Hours
2889 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override below 14 day in Hour',c_abs.date_start, c_abs.date_end);
2890 FETCH csr_check_override INTO l_override_sickness_pay_hours;
2891 CLOSE csr_check_override;
2892
2893 IF l_override_sickness_pay_hours = -1 OR l_override_sickness_pay_hours IS NULL THEN
2894
2895 --check whether the end period for 14 days fall within payroll period
2896 /*IF (c_abs.date_start + 14-l_sickness_days) < p_pay_end_date then*/
2897 --sickness pay hours calculation
2898 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2899 ( p_assignment_id, l_hours, l_include_event,
2900 c_abs.date_start, (c_abs.date_start + 14-l_sickness_days),
2901 replace(nvl(c_abs.time_start,nvl(l_normal_time_start,l_start_time_char)),':','.'),
2902 replace(nvl(c_abs.time_start,nvl(l_normal_time_end,l_end_time_char)),':','.'), l_wrk_duration
2903 );
2904 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below + l_wrk_duration;
2905 /*END IF;*/
2906 ELSE
2907 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_override_sickness_pay_hours;
2908 l_override_sickness_pay_hours:=-1;
2909 END IF;
2910 /* Calculating the Sick Pay days less than 14 days*/
2911 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2912 ( p_assignment_id, l_days, l_include_event,
2913 c_abs.date_start, (c_abs.date_start + 14-l_sickness_days),
2914 replace(nvl(c_abs.time_start,nvl(l_normal_time_start,l_start_time_char)),':','.'),
2915 replace(nvl(c_abs.time_start,nvl(l_normal_time_end,l_end_time_char)),':','.'), l_wrk_duration
2916 );
2917 l_sick_pay_days_14_below_days:=l_sick_pay_days_14_below_days+ l_wrk_duration;
2918 --Checking Override input value for Sick Pay Days above 14 days in Hours
2919 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2920 FETCH csr_check_override INTO l_override_sickness_days;
2921 CLOSE csr_check_override;
2922
2923 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2924
2925 /*IF (c_abs.date_start + 14-l_sickness_days+1) < p_pay_end_date THEN */
2926 --sickness pay hours calculation
2927 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2928 ( p_assignment_id, l_hours, l_include_event,
2929 c_abs.date_start + 14-l_sickness_days+1 ,c_abs.date_end,
2930 replace(nvl(c_abs.time_start,nvl(l_normal_time_start,l_start_time_char)),':','.'),
2931 replace(nvl(c_abs.time_end,nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
2932 );
2933 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2934
2935 /*END IF;*/
2936 ELSE
2937 l_sickness_above_14_days:=l_sickness_above_14_days-(l_sickness_days+(l_duration+1)-14);
2938 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2939 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2940 l_override_sickness_pay_hours:=-1;
2941 END IF;
2942 l_sickness_days:=l_sickness_days+l_sickness_14_below_days+l_sickness_above_14_days;
2943
2944 --sickness days greater than 14 days
2945 ELSE
2946
2947 l_sickness_above_14_days:=l_sickness_above_14_days+l_duration;
2948 l_sickness_days:=l_sickness_days+l_duration;
2949
2950 --Checking Override input value for Sick Pay Days above 14 days in Hours
2951 OPEN csr_check_override (p_assignment_id,p_effective_date,'Override beyond 14 day in Day',c_abs.date_start, c_abs.date_end);
2952 FETCH csr_check_override INTO l_override_sickness_days;
2953 CLOSE csr_check_override;
2954
2955 IF l_override_sickness_days = -1 OR l_override_sickness_days IS NULL THEN
2956 --sickness pay hours calculation
2957 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2958 ( p_assignment_id, l_hours, l_include_event,
2959 c_abs.date_start, c_abs.date_end,
2960 replace(nvl(c_abs.time_start,nvl(l_normal_time_start,l_start_time_char)),':','.'),
2961 replace(nvl(c_abs.time_end,nvl(l_normal_time_end, l_end_time_char)),':','.'), l_wrk_duration
2962 );
2963 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_wrk_duration;
2964
2965 ELSE
2966 l_sickness_above_14_days:=l_sickness_above_14_days-l_duration;
2967 l_sickness_above_14_days:=l_sickness_above_14_days+l_override_sickness_days;
2968 l_sickness_pay_hours_above_14:=l_sickness_pay_hours_above_14 + l_override_sickness_pay_hours;
2969 l_override_sickness_pay_hours:=-1;
2970 END IF;
2971
2972 END IF;
2973
2974 END IF;
2975 END LOOP;
2976 END IF;
2977
2978 /* Logic to change the Sick Pay and Sickness Deduction, if the waiting day has crossed for bug 5718434 */
2979 /* Get the value from the balance */
2980 /*If balance value is less than 10 then waiting day can be one */
2981
2982 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id/*189398*/);
2983 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_WAITING_DAY_ASG_13MONTH');
2984 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2985
2986 CLOSE csr_Get_Defined_Balance_Id;
2987 l_value:=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2988 P_ASSIGNMENT_ID => p_assignment_id, --22145,
2989 P_VIRTUAL_DATE=> p_pay_end_date/*TO_DATE('28-feb-2000')*/);
2990
2991 /*If the balance value is greater than 10 then check from the run results,
2992 whether it has actually crossed 10 waiting days by run results*/
2993 IF l_value>=10 THEN
2994
2995 OPEN csr_element('Sick Pay 1 to 14 days');
2996 FETCH csr_element INTO l_element_type_id;
2997 CLOSE csr_element;
2998
2999 OPEN csr_input(l_element_type_id,'Waiting Day');
3000 FETCH csr_input INTO l_waiting_day_iv;
3001 CLOSE csr_input;
3002
3003 OPEN csr_input(l_element_type_id,'Start Date');
3004 FETCH csr_input INTO l_start_date_iv;
3005 CLOSE csr_input;
3006
3007 OPEN csr_group_start_end_date(p_assignment_id,l_element_type_id,l_start_date_iv,l_waiting_day_iv,add_months(p_abs_start_date,-12),p_abs_start_date);
3008 FETCH csr_group_start_end_date INTO l_total_waiting_days;
3009 CLOSE csr_group_start_end_date;
3010
3011 --ELSE
3012
3013 END IF;
3014 /*checking whether the total waiting days before one year is greater than 10 */
3015 IF l_total_waiting_days>=10 THEN
3016 /* checking whether the absence has crossed 10 days */
3017 IF l_sickness_above_14_days >0 THEN
3018 /* Increasing the sickness days above 14 by one */
3019 --l_sickness_above_14_days:=l_sickness_above_14_days+1;
3020 /* check whether it is one day absence, only waiting day */
3021 IF l_sickness_pay_hours_14_below<>0 THEN
3022 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_waiting_day_hours;
3023 END IF;
3024 l_waiting_day_hours:=0;
3025 ELSE
3026 /*absence has not crossed 10 days */
3027 /*Increasing the sick pay 1 to 14 day hours by waiting day hours*/
3028 /* check whether it is one day absence, only waiting day */
3029 IF l_sickness_pay_hours_14_below<>0 THEN
3030 l_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below+l_waiting_day_hours;
3031 END IF;
3032 l_waiting_day_hours:=0;
3033 END IF;
3034 END IF;
3035
3036 OPEN csr_element('Sick Pay after 14 days');
3037 FETCH csr_element INTO l_element_type_id;
3038 CLOSE csr_element;
3039 OPEN csr_input(l_element_type_id,'Sick Days in Period');
3040 FETCH csr_input INTO l_input_type_id;
3041 CLOSE csr_input;
3042
3043 /*OPEN csr_input(l_element_type_id,'Start Date');
3044 FETCH csr_input INTO l_input_type_id2;
3045 CLOSE csr_input;
3046 hr_utility.set_location('l_input_type_id2'||l_input_type_id2,10);*/
3047 OPEN csr_global_value('SE_SICK_PAY_AFTER_14_DAYS_YEAR_LIMIT',p_effective_date);
3048 FETCH csr_global_value INTO l_global_value;
3049 CLOSE csr_global_value;
3050
3051 l_sick_after_14_year_limit:=l_global_value;
3052
3053 /* Get the values from run results one year before the processing date */
3054 OPEN csr_run_result_value(p_assignment_id,l_element_type_id,l_input_type_id,add_months(p_effective_date,-12)+1,p_abs_start_date-1);
3055 FETCH csr_run_result_value INTO l_sick_after_14_year_taken;
3056 CLOSE csr_run_result_value;
3057 /* FOR csr_run IN csr_run_result_value(p_assignment_id,l_element_type_id,l_input_type_id1,l_input_type_id2,add_months(p_effective_date,-12)+1,p_abs_start_date-1) LOOP
3058
3059 END LOOP;*/
3060
3061
3062 l_total_sick_after_14:=(l_sick_after_14_year_taken+l_sickness_above_14_days);
3063
3064 IF l_total_sick_after_14 > l_sick_after_14_year_limit THEN
3065 --l_sickness_above_14_days:=l_total_sick_after_14-l_sick_after_14_year_limit;
3066 /* Bug 5985088 fix */
3067 FOR csr_abs in csr_absence_period(p_assignment_id,p_pay_start_date,p_abs_end_date) LOOP
3068 l_absence_period:=l_absence_period+(csr_abs.date_end-csr_abs.date_start +1);
3069 END LOOP;
3070
3071 IF l_sick_after_14_year_taken>=l_sick_after_14_year_limit THEN
3072 p_sickness_after_14_days_month:=0;
3073 ELSIF (l_total_sick_after_14-l_absence_period>= l_sick_after_14_year_limit) THEN
3074 p_sickness_after_14_days_month:=0;
3075 l_sickness_above_14_days:=0;
3076 ELSE
3077 /*p_sickness_after_14_days_month:=(p_pay_end_date-p_pay_start_date+1)-(l_sickness_above_14_days-l_sick_after_14_year_limit);
3078 l_sickness_above_14_days:=l_sick_after_14_year_limit-l_sick_after_14_year_taken; */
3079 /*Fix for bug No. 5985088 */
3080 p_sickness_after_14_days_month:=(p_pay_end_date-p_pay_start_date+1)- (l_total_sick_after_14-l_sick_after_14_year_limit); --l_sick_after_14_year_taken; -- (l_sickness_above_14_days-l_sick_after_14_year_limit);
3081 /*If there is no absence before the current absence within the year*/
3082 IF l_sick_after_14_year_taken <>0 then
3083 l_sickness_above_14_days:=l_sick_after_14_year_limit-(l_total_sick_after_14-l_sick_after_14_year_limit)+1; --l_sick_after_14_year_taken;
3084 /*If there is absence before the current absence within the year*/
3085 ELSE
3086 l_sickness_above_14_days:=l_sick_after_14_year_limit; --l_sick_after_14_year_taken;
3087 END IF;
3088
3089 end if;
3090 p_exceeds_14_days:='Y';
3091 END IF;
3092 /* Check whether Sickness above 14 days exist, because when sickness is less than
3093 14 days also we have fourteenth date */
3094 --IF l_sickness_above_14_days > 0 THEN
3095 /* check whether the absence days has exceeded the limit*/
3096 /*If exceeded then same as l_sickness_above_14_days */
3097 IF p_exceeds_14_days IS NULL THEN
3098 IF l_fourteenth_date IS NULL THEN
3099
3100
3101 p_sickness_after_14_days_month:=GET_SICKNESS_AFTER_14_PERIOD(l_person_id,p_assignment_id,
3102 p_pay_start_date,p_pay_end_date,p_pay_end_date);
3103
3104 --p_fourteenth_date:=l_sickness_last_date;
3105 ELSE
3106
3107 p_sickness_after_14_days_month:=GET_SICKNESS_AFTER_14_PERIOD(l_person_id,p_assignment_id,
3108 p_pay_start_date,p_pay_end_date,l_fourteenth_date);
3109
3110
3111 -- p_sickness_after_14_days_month:=GET_SICKNESS_AFTER_14_PERIOD(l_person_id,p_pay_start_date,
3112 -- p_pay_end_date,p_fourteenth_date);
3113 -- hr_utility.set_location('p_sickness_after_14_days_month'||p_sickness_after_14_days_month,10);
3114 END IF;
3115 /* Checking if l_sickness_above_14_days less than the days in period then
3116 assign it to days in the period*/
3117 /* ELSIF l_sickness_above_14_days <=(p_pay_start_date-P_pay_end_date) THEN
3118 p_sickness_after_14_days_month:=l_sickness_above_14_days;*/
3119 END IF;
3120 -- END IF;
3121 l_return:=GET_SICKPAY_DETAILS(p_assignment_id,p_abs_start_date,p_abs_end_date,l_sickness_14_below_days,
3122 /*l_sickness_above_14_days*/p_sickness_after_14_days_month,l_sickness_pay_hours_14_below,l_sickness_pay_hours_above_14,
3123 p_monthly_pay,l_asg_hour_sal,l_working_percentage,l_normal_hours,p_hourly_rate,
3124 l_waiting_day_hours,l_waiting_day_deduction,l_waiting_day,l_sickness_deduction_14_above,
3125 l_sickness_deduction_14_less,l_sick_pay_14_above,l_sick_pay_14_less,p_salary_rate,p_effective_date,
3126 p_assignment_action_id,p_override_monthly_basic,p_override_monthly_basic_day);
3127
3128
3129
3130 p_sickness_14_below_days:=l_sick_pay_days_14_below_days; /*l_sickness_14_below_days;*/
3131 p_sickness_above_14_days:=l_sickness_above_14_days;
3132 p_sickness_pay_hours_14_below:=l_sickness_pay_hours_14_below;
3133 p_sickness_pay_hours_above_14:=nvl(l_sickness_pay_hours_above_14,0);
3134 p_tot_waiting_day_hours:=l_waiting_day_hours;
3135 p_tot_waiting_day:=l_waiting_day;
3136 p_total_sick_pay:=l_sick_pay_14_less;
3137 p_total_sick_pay_14_above:=l_sick_pay_14_above;
3138 p_tot_waiting_day_ded:=l_waiting_day_deduction;
3139 p_tot_sick_pay_days:=1;
3140 p_total_sickness_deduction:=l_sickness_deduction_14_less;
3141 p_tot_sickness_ded_14_above:=l_sickness_deduction_14_above;
3142 p_waiting_date:=l_waiting_date;
3143 p_fourteenth_date:=l_fourteenth_date;
3144 p_override_monthly_basic:=nvl(p_override_monthly_basic,0);
3145 p_override_monthly_basic_day:=nvl(p_override_monthly_basic_day,0);
3146
3147 IF l_total_waiting_days>=10 THEN
3148 /*waiting day is 0 */
3149 p_tot_waiting_day:=0;
3150 p_tot_waiting_day_hours:=0;
3151 /* making waiting date to be null*/
3152 p_waiting_date:=null;
3153 END IF;
3154
3155 IF l_fourteenth_date IS NULL then
3156 p_fourteenth_date:=l_sickness_last_date;
3157 /* To get the sickness days after 14th day in the month*/
3158 /*ELSE
3159 p_sickness_after_14_days_month:=GET_SICKNESS_AFTER_14_PERIOD(l_person_id,p_pay_start_date,
3160 p_pay_end_date,p_fourteenth_date);
3161 hr_utility.set_location('p_sickness_after_14_days_month'||p_sickness_after_14_days_month,10); */
3162 END IF;
3163 /* Bug Fix for 5981860 */
3164 IF p_fourteenth_date <p_pay_start_date THEN
3165 p_tot_waiting_day_ded:=0;
3166 p_total_sickness_deduction:=0;
3167 END IF;
3168 l_asg_hour_sal:='D';
3169 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
3170 ( p_assignment_id, l_asg_hour_sal, l_include_event,
3171 l_group_start_date, p_fourteenth_date,
3172 l_start_time_char,l_end_time_char, l_wrk_duration
3173 );
3174 p_full_days:=l_wrk_duration;
3175 /*Call the function to get the Sickness Group level values */
3176 l_return:=Get_Sickness_Group_Details(l_person_id,p_assignment_id,p_pay_start_date,
3177 p_pay_end_date,p_abs_start_date,p_abs_end_date,p_group_calendar_days,
3178 p_group_working_days,p_group_working_hours);
3179
3180
3181
3182 RETURN 1;
3183 EXCEPTION
3184 WHEN OTHERS THEN
3185 l_person_id := null;
3186 l_business_group_id := null;
3187 l_asg_hour_sal := null;
3188
3189 END CALCULATE_PAYMENT;
3190
3191
3192
3193 FUNCTION GET_SICKPAY_DETAILS(p_assignment_id IN NUMBER,
3194 p_abs_start_date IN DATE,
3195 p_abs_end_date IN DATE,
3196 p_sickness_14_below_days IN NUMBER,
3197 --p_sickness_above_14_days IN NUMBER,
3198 p_sickness_after_14_days_month IN NUMBER,
3199 p_sickness_pay_hours_14_below IN NUMBER,
3200 p_sickness_pay_hours_above_14 IN NUMBER,
3201 p_monthly_pay IN NUMBER,
3202 p_asg_hour_sal IN varchar2,
3203 p_working_percentage IN NUMBER,
3204 p_normal_hours IN NUMBER,
3205 p_hourly_rate IN NUMBER,
3206 p_waiting_day_hours IN OUT NOCOPY NUMBER,
3207 p_waiting_day_deduction OUT NOCOPY NUMBER,
3208 p_waiting_day OUT NOCOPY NUMBER,
3209 p_sickness_deduction_14_above OUT NOCOPY NUMBER,
3210 p_sickness_deduction_14_less OUT NOCOPY NUMBER,
3211 p_sick_pay_14_above OUT NOCOPY NUMBER,
3212 p_sick_pay_14_less OUT NOCOPY NUMBER,
3213 p_salary_rate IN NUMBER,
3214 p_effective_date IN DATE,
3215 p_assignment_action_id IN NUMBER,
3216 p_override_monthly_basic OUT NOCOPY NUMBER,
3217 p_override_monthly_basic_day OUT NOCOPY NUMBER
3218 )
3219 RETURN NUMBER IS
3220
3221 CURSOR csr_get_schedule_id(
3222 b_schdl_cat VARCHAR2,
3223 b_object_type VARCHAR2,
3224 b_object_id NUMBER,
3225 b_start_dt DATE,
3226 b_end_dt DATE
3227 )
3228 IS
3229 SELECT CSSB.SCHEDULE_ID
3230 from
3231 CAC_SR_SCHDL_OBJECTS CSSO,
3232 CAC_SR_SCHEDULES_B CSSB
3233 where
3234 CSSO.OBJECT_TYPE = b_object_type
3235 AND CSSO.OBJECT_ID = b_object_id
3236 AND CSSO.START_DATE_ACTIVE <= b_end_dt
3237 AND CSSO.END_DATE_ACTIVE >= b_start_dt
3238 AND CSSO.SCHEDULE_ID = CSSB.SCHEDULE_ID
3239 AND CSSB.DELETED_DATE IS NULL
3240 AND (CSSB.SCHEDULE_CATEGORY = b_schdl_cat
3241 OR CSSB.SCHEDULE_ID IN (SELECT SCHEDULE_ID
3242 FROM CAC_SR_PUBLISH_SCHEDULES
3243 WHERE OBJECT_TYPE = b_object_type
3244 AND OBJECT_ID = b_object_id
3245 AND b_schdl_cat IS NULL
3246 ));
3247 CURSOR csr_get_template_details(b_schedule_id number)
3248 IS
3249 select CSTB.Template_Id,CSTB.TEMPLATE_LENGTH_DAYS from CAC_SR_SCHEDULES_B CSSB,
3250 CAC_SR_TEMPLATES_B CSTB
3251 where
3252 CSSB.Template_Id=CSTB.Template_Id
3253 and CSSB.Schedule_id=b_schedule_id; --10206
3254
3255 CURSOR csr_get_shift_duration(b_template_id number)
3256 IS
3257 select CSRB.DURATION from
3258 CAC_SR_TEMPLATES_B CSTB,
3259 CAC_SR_TMPL_DETAILS CSTD,
3260 CAC_SR_PERIODS_B CSRB
3261 where
3262 CSTB.Template_id =CSTD.Template_Id
3263 and CSTD.Child_Period_Id=CSRB.Period_ID
3264 and CSTB.Template_Id=b_template_id; --10284
3265
3266 CURSOR csr_global_value(csr_v_global_name VARCHAR2,csr_v_effective_date DATE )
3267 IS
3268 SELECT nvl(global_value,0) FROM ff_globals_f WHERE --ROWNUM<3
3269 legislation_code='SE'
3270 AND GLOBAL_NAME=csr_v_global_name --'SE_BASIC_AMOUNT'
3271 AND csr_v_effective_date /*'28-feb-2007'*/ BETWEEN effective_start_date
3272 AND effective_end_date;
3273
3274 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
3275 IS
3276 SELECT ue.creator_id
3277 FROM ff_user_entities ue,
3278 ff_database_items di
3279 WHERE di.user_name = csr_v_Balance_Name
3280 AND ue.user_entity_id = di.user_entity_id
3281 AND ue.legislation_code = 'SE'
3282 AND ue.business_group_id is NULL
3283 AND ue.creator_type = 'B';
3284
3285 l_weekly_working_hours NUMBER;
3286 l_hourly_rate NUMBER;
3287 l_hours_day NUMBER;
3288 l_schedule_id NUMBER;
3289 l_template_id NUMBER;
3290 l_pattern_length NUMBER;
3291 l_shift_duration NUMBER;
3292 l_waiting_day_hours NUMBER;
3293 l_curr_sickness_14_less NUMBER;
3294 l_curr_sickness_14_above NUMBER;
3295 l_global_value NUMBER;
3296 l_basic_amount_month NUMBER;
3297 l_monthly_salary NUMBER;
3298 l_override_monthly_basic NUMBER;
3299 l_override_monthly_basic_day NUMBER;
3300 lr_Get_Defined_Balance_Id number(10);
3301 l_value number(10);
3302
3303 BEGIN
3304 --getting the hours per day
3305
3306 OPEN csr_get_schedule_id(NULL, 'PERSON_ASSIGNMENT', p_assignment_id,add_months(p_abs_start_date,-1),p_abs_end_date);
3307 FETCH csr_get_schedule_id INTO l_schedule_id;
3308 CLOSE csr_get_schedule_id;
3309
3310 OPEN csr_get_template_details(l_schedule_id);
3311 FETCH csr_get_template_details --.Template_Id,csr_get_template_details.TEMPLATE_LENGTH_DAYS
3312 INTO l_template_id,l_pattern_length;
3313 CLOSE csr_get_template_details;
3314
3315 OPEN csr_get_shift_duration(l_template_id);
3316 FETCH csr_get_shift_duration INTO l_hours_day;
3317 CLOSE csr_get_shift_duration;
3318
3319 OPEN csr_global_value('SE_BASIC_AMOUNT',p_effective_date);
3320 FETCH csr_global_value INTO l_global_value;
3321 CLOSE csr_global_value;
3322
3323 l_basic_amount_month:=(7.5*l_global_value/12);
3324
3325 /* Get the monthly Salary value from balance */
3326 /*pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',p_assignment_action_id/*1305752*/--);
3327 /*pay_balance_pkg.set_context('DATE_EARNED',p_effective_date/*'28-feb-2000'*/--);
3328
3329 /*OPEN csr_Get_Defined_Balance_Id( 'SICK_PAY_AFTER_14_MONTHLY_PAY_ASG_MONTH');
3330 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
3331 CLOSE csr_Get_Defined_Balance_Id;
3332
3333 l_value :=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
3334 P_ASSIGNMENT_ACTION_ID =>p_assignment_action_id/*1305752*/--),0);
3335 /*l_monthly_salary:=l_value;*/
3336 l_monthly_salary:=p_monthly_pay;
3337 --checking salaried person
3338 IF p_asg_hour_sal='S' THEN
3339 /*l_hourly_rate:=round(nvl(((p_Monthly_Pay*12)/(52*p_normal_hours)*p_working_percentage/100),0),2);*/
3340 IF p_waiting_day_hours <> 0 then
3341 p_waiting_day:=1;
3342 ELSE
3343 p_waiting_day:=0;
3344 END IF;
3345 p_waiting_day_deduction:=round((p_waiting_day_hours*p_salary_rate),2);
3346
3347 p_sick_pay_14_less:=round((0.8*p_sickness_pay_hours_14_below*p_salary_rate),2);
3348
3349 IF l_monthly_salary>=l_basic_amount_month THEN
3350 l_override_monthly_basic_day:=round((((l_monthly_salary-l_basic_amount_month)*12)/365),2);
3351 l_override_monthly_basic:=round((0.8*l_override_monthly_basic_day*/*p_sickness_above_14_days*/p_sickness_after_14_days_month),2);
3352
3353 p_override_monthly_basic:=l_override_monthly_basic;
3354 p_override_monthly_basic_day:=l_override_monthly_basic_day;
3355 p_sick_pay_14_above:=round((0.1*((p_monthly_pay*12)/365)*(/*p_sickness_above_14_days*/p_sickness_after_14_days_month)),2);
3356 ELSE
3357 p_sick_pay_14_above:=round((0.1*((p_monthly_pay*12)/365)*(/*p_sickness_above_14_days*/p_sickness_after_14_days_month)),2);
3358 END IF;
3359
3360 p_sickness_deduction_14_less:=round((p_sickness_pay_hours_14_below * p_salary_rate),2);
3361
3362
3363 p_sickness_deduction_14_above:=round(((p_monthly_pay*12)/365)*(/*p_sickness_above_14_days*/p_sickness_after_14_days_month),2);
3364
3365 ELSE
3366 p_sick_pay_14_less:=round((0.8*(p_sickness_pay_hours_14_below)*p_hourly_rate),2);
3367
3368 END IF;
3369 return 1;
3370
3371 END GET_SICKPAY_DETAILS;
3372
3373 FUNCTION GET_WAITING_DAY(p_assignment_id NUMBER, p_abs_start_date DATE, p_abs_end_date date)
3374 RETURN DATE IS
3375
3376 l_starting_date DATE;
3377 l_found BOOLEAN :=false;
3378 l_asg_hour_sal CHAR :='D';
3379 l_include_event CHAR :='Y';
3380 l_start_time_char Varchar2(10) := '0';
3381 l_end_time_char Varchar2(10) := '23.59';
3382 l_duration Number;
3383 l_wrk_schd_return NUMBER;
3384 BEGIN
3385 l_starting_date:=p_abs_start_date;
3386
3387 WHILE (l_starting_date <= p_abs_end_date) AND l_found = false LOOP
3388 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
3389 ( p_assignment_id, l_asg_hour_sal, l_include_event,
3390 l_starting_date,l_starting_date, l_start_time_char,
3391 l_end_time_char, l_duration
3392 );
3393
3394 IF l_duration=1 THEN
3395 l_found:=true;
3396 ELSE
3397 l_starting_date:=l_starting_date +1;
3398 END IF;
3399 IF l_starting_date>p_abs_end_date THEN
3400 l_starting_date:=TO_DATE('01/01/0001','dd/mm/yyyy');
3401 l_found:=true;
3402 END IF;
3403 END LOOP;
3404 RETURN l_starting_date;
3405 END GET_WAITING_DAY;
3406
3407 FUNCTION Get_Entitlement_Days(
3408 p_assignment_id in NUMBER,
3409 p_effective_date IN DATE,
3410 p_absence_start_date IN DATE,
3411 p_absence_end_date IN DATE,
3412 p_entitlement_days OUT NOCOPY NUMBER,
3413 p_sickness_days OUT NOCOPY NUMBER
3414 )
3415 RETURN NUMBER IS
3416 l_wrk_schd_return NUMBER;
3417 l_start_time_char Varchar2(10) := '0';
3418 l_end_time_char Varchar2(10) := '23.59';
3419 l_include_event CHAR;
3420 l_wrk_duration NUMBER;
3421 l_asg_hour_sal CHAR:='D';
3422
3423 BEGIN
3424
3425 /*SELECT hourly_salaried_code
3426 INTO
3427 l_asg_hour_sal
3428 FROM per_all_assignments_f paaf,
3429 per_all_people_f papf,
3430 hr_soft_coding_keyflex hsck
3431 WHERE paaf.assignment_id = p_assignment_id
3432 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
3433 AND papf.person_id=paaf.person_id
3434 AND p_effective_date BETWEEN paaf.effective_start_date
3435 AND paaf.effective_end_date
3436 AND p_effective_date BETWEEN papf.effective_start_date
3437 AND papf.effective_end_date;*/
3438 /*get the entitlement days with public holidays and weekends*/
3439 --l_include_event:='Y';
3440 /*l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
3441 ( p_assignment_id, l_asg_hour_sal, l_include_event,
3442 p_absence_start_date, p_absence_end_date,l_start_time_char,
3443 l_end_time_char, l_wrk_duration
3444 ); */
3445
3446 p_entitlement_days:=(p_absence_end_date-p_absence_start_date)+1;
3447 /*get the sickness days without public holidays and weekends*/
3448 l_include_event:='Y';
3449 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
3450 ( p_assignment_id, l_asg_hour_sal, l_include_event,
3451 p_absence_start_date, p_absence_end_date,l_start_time_char,
3452 l_end_time_char, l_wrk_duration
3453 );
3454
3455 p_sickness_days:=l_wrk_duration;
3456 RETURN 0;
3457 END Get_entitlement_days;
3458
3459 FUNCTION GET_SICKNESS_AFTER_14_PERIOD(p_person_id IN NUMBER,
3460 p_assignment_id IN NUMBER,
3461 p_payroll_start IN DATE,
3462 p_payroll_end IN DATE,
3463 p_fourteenth_date IN DATE)
3464 RETURN NUMBER IS
3465 l_14_days_month NUMBER:=0;
3466 CURSOR csr_sickness_after_14_period(csr_v_person_id NUMBER,
3467 csr_v_assignment_id number,
3468 csr_v_payroll_start_date DATE,
3469 csr_v_payroll_end_date DATE,
3470 csr_v_fourteenth_date DATE)
3471 IS
3472 SELECT paa.absence_attendance_id
3473 ,greatest(paa.date_start,csr_v_fourteenth_date,csr_v_payroll_start_date) date_start
3474 ,least(nvl(paa.date_end,csr_v_payroll_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_payroll_end_date)) date_end
3475 ,paa.time_start
3476 ,paa.time_end
3477 --,(date_end-date_start) date_diff
3478 FROM per_absence_attendances paa,
3479 per_absence_attendance_types pat
3480 WHERE paa.person_id = csr_v_person_id --37732
3481 AND paa.date_start <= csr_v_payroll_end_date--'31-mar-2000' --p_abs_end_date
3482 AND least(nvl(paa.date_end,csr_v_payroll_end_date),csr_v_payroll_end_date)>=greatest(paa.date_start,csr_v_fourteenth_date,csr_v_payroll_start_date)--'01-mar-2000' --p_abs_end_date
3483 /*AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL*/
3484 AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
3485 AND pat.absence_category IN ('S')
3486 /*Fix for Bug No. 5383707*/
3487 AND paa.absence_attendance_id IN(
3488 -- ORDER BY paa.date_end ;
3489
3490
3491 SELECT eev1.screen_entry_value screen_entry_value
3492 FROM per_all_assignments_f asg1
3493 ,per_all_assignments_f asg2
3494 ,per_all_people_f per
3495 ,pay_element_links_f el
3496 ,pay_element_types_f et
3497 ,pay_input_values_f iv1
3498 ,pay_element_entries_f ee
3499 ,pay_element_entry_values_f eev1
3500 WHERE asg1.assignment_id = csr_v_assignment_id --34040 --l_asg_id
3501 AND p_payroll_end BETWEEN asg1.effective_start_date AND asg1.effective_end_date
3502 AND p_payroll_end BETWEEN asg2.effective_start_date AND asg2.effective_end_date
3503 AND per.person_id = asg1.person_id
3504 AND asg2.person_id = per.person_id
3505 --AND asg2.primary_flag = 'Y'
3506 AND asg1.assignment_id=asg2.assignment_id
3507 AND et.element_name = 'Sickness Details'
3508 AND et.legislation_code = 'SE'
3509 --OR et.business_group_id=3261 ) --checking for the business group, it should be removed
3510 AND iv1.element_type_id = et.element_type_id
3511 AND iv1.name = 'CREATOR_ID'
3512 AND el.business_group_id = per.business_group_id
3513 AND el.element_type_id = et.element_type_id
3514 AND ee.assignment_id = asg2.assignment_id
3515 AND ee.element_link_id = el.element_link_id
3516 AND eev1.element_entry_id = ee.element_entry_id
3517 AND eev1.input_value_id = iv1.input_value_id
3518 /*AND ee.effective_start_date >= p_abs_start_date
3519 AND ee.effective_end_date <= p_abs_end_date
3520 AND eev1.effective_start_date >= p_abs_start_date
3521 AND eev1.effective_end_date <= p_abs_end_date*/
3522 ) ORDER BY paa.date_end desc ;
3523
3524 -- ORDER BY paa.date_end ;
3525
3526 BEGIN
3527
3528 --OPEN csr_sickness_after_14_payroll_period(p_person_id,p_payroll_start,p_payroll_end,p_fourteenth_date);
3529 FOR csr_sick IN csr_sickness_after_14_period(p_person_id,p_assignment_id,p_payroll_start,p_payroll_end,p_fourteenth_date) LOOP
3530 /*check for whether the start_date is same as fourteenth date*/
3531 IF csr_sick.date_start=p_fourteenth_date THEN
3532 l_14_days_month:=l_14_days_month+(csr_sick.date_end-csr_sick.date_start);
3533 /*IF start_date is not equal to fourteenth date */
3534 ELSE
3535 l_14_days_month:=l_14_days_month+(csr_sick.date_end-csr_sick.date_start)+1;
3536 END IF;
3537 END LOOP csr_sickness_after_14_period;
3538 -- FETCH csr_sickness_after_14_payroll_period.date_diff INTO l_14_days_month
3539 --CLOSE csr_sickness_after_14_payroll_period;
3540 RETURN l_14_days_month;
3541 END GET_SICKNESS_AFTER_14_PERIOD;
3542 FUNCTION Get_Sickness_Group_Details(p_person_id IN NUMBER,
3543 p_assignment_id IN NUMBER,
3544 p_pay_start_date IN DATE,
3545 p_pay_end_date IN DATE,
3546 p_abs_group_start_date IN DATE,
3547 p_abs_group_end_date IN DATE,
3548 p_group_calendar_days OUT NOCOPY NUMBER,
3549 p_group_working_days OUT NOCOPY NUMBER,
3550 p_group_working_hours OUT NOCOPY NUMBER )
3551 RETURN NUMBER IS
3552
3553 l_working_days NUMBER:=0;
3554 l_calendar_days NUMBER:=0;
3555 l_working_hours NUMBER:=0;
3556 l_days Char(1):='D';
3557 l_hours Char(1):='H';
3558 l_include_event CHAR :='Y';
3559 l_start_time_char Varchar2(10) := '0';
3560 l_end_time_char Varchar2(10) := '23.59';
3561 l_wrk_schd_return NUMBER;
3562 l_hour_duration NUMBER;
3563 l_day_duration NUMBER;
3564
3565 CURSOR csr_absence_period(csr_v_person_id NUMBER,
3566 csr_v_start_date DATE, csr_v_end_date DATE )
3567 IS
3568 SELECT paa.absence_attendance_id
3569 ,greatest(paa.date_start,csr_v_start_date) date_start
3570 ,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)) date_end
3571 ,paa.time_start
3572 ,paa.time_end
3573 --,(date_end-date_start) date_diff
3574 FROM per_absence_attendances paa,
3575 per_absence_attendance_types pat
3576 WHERE paa.person_id = csr_v_person_id --37732
3577 AND paa.date_start <= csr_v_end_date--'31-mar-2000' --p_abs_end_date
3578 AND least(nvl(paa.date_end,csr_v_end_date),csr_v_end_date)>=greatest(paa.date_start,csr_v_start_date)--'01-mar-2000' --p_abs_end_date
3579 /*AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL*/
3580 AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
3581 AND pat.absence_category IN ('S')
3582 /*Fix for Bug No. 5383707*/
3583 AND paa.absence_attendance_id IN(
3584 -- ORDER BY paa.date_end ;
3585
3586
3587 SELECT eev1.screen_entry_value screen_entry_value
3588 FROM per_all_assignments_f asg1
3589 ,per_all_assignments_f asg2
3590 ,per_all_people_f per
3591 ,pay_element_links_f el
3592 ,pay_element_types_f et
3593 ,pay_input_values_f iv1
3594 ,pay_element_entries_f ee
3595 ,pay_element_entry_values_f eev1
3596 WHERE asg1.assignment_id = p_assignment_id --34040 --l_asg_id
3597 AND p_pay_end_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
3598 AND p_pay_end_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
3599 AND per.person_id = asg1.person_id
3600 AND asg2.person_id = per.person_id
3601 --AND asg2.primary_flag = 'Y'
3602 AND asg1.assignment_id=asg2.assignment_id
3603 AND et.element_name = 'Sickness Details'
3604 AND et.legislation_code = 'SE'
3605 --OR et.business_group_id=3261 ) --checking for the business group, it should be removed
3606 AND iv1.element_type_id = et.element_type_id
3607 AND iv1.name = 'CREATOR_ID'
3608 AND el.business_group_id = per.business_group_id
3609 AND el.element_type_id = et.element_type_id
3610 AND ee.assignment_id = asg2.assignment_id
3611 AND ee.element_link_id = el.element_link_id
3612 AND eev1.element_entry_id = ee.element_entry_id
3613 AND eev1.input_value_id = iv1.input_value_id
3614 /*AND ee.effective_start_date >= p_abs_start_date
3615 AND ee.effective_end_date <= p_abs_end_date
3616 AND eev1.effective_start_date >= p_abs_start_date
3617 AND eev1.effective_end_date <= p_abs_end_date*/
3618 ) ORDER BY paa.date_end desc ;
3619
3620 -- ORDER BY paa.date_end ;
3621
3622 /*SELECT paa.absence_attendance_id
3623 ,paa.date_start
3624 --,nvl(paa.date_end,'31-jan-2000') date_end
3625 ,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)) date_end
3626 ,paa.time_start
3627 ,paa.time_end
3628 ,DECODE(paa.date_start,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)), 1,
3629 (least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date))-paa.date_start)+1) AS days_diff
3630 FROM per_absence_attendances paa,
3631 per_absence_attendance_types pat
3632 WHERE paa.person_id = csr_v_person_id
3633 AND paa.date_start >=csr_v_start_date
3634 AND paa.date_start<=csr_v_end_date
3635 AND least(nvl(paa.date_end,csr_v_end_date),csr_v_end_date)<=csr_v_end_date
3636 /*AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL*/
3637 /*AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
3638 AND pat.absence_category IN ('S')
3639 ORDER BY paa.date_end ;*/
3640
3641
3642 BEGIN
3643 FOR csr_abs IN csr_absence_period(p_person_id,greatest(p_pay_start_date,p_abs_group_start_date),least(p_pay_end_date,p_abs_group_end_date)) LOOP
3644
3645 l_calendar_days:=l_calendar_days+(csr_abs.date_end-csr_abs.date_start+1);
3646
3647 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
3648 ( p_assignment_id, l_days, l_include_event,
3649 csr_abs.date_start, csr_abs.date_end, replace(nvl(csr_abs.time_start,l_start_time_char),':','.'),
3650 replace(nvl(csr_abs.time_end,l_end_time_char),':','.'), l_day_duration
3651 );
3652
3653 l_working_days:=l_working_days+l_day_duration;
3654
3655 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
3656 ( p_assignment_id, l_hours, l_include_event,
3657 csr_abs.date_start, csr_abs.date_end, replace(l_start_time_char,':','.'),
3658 replace(l_end_time_char,':','.'), l_hour_duration
3659 );
3660
3661 l_working_hours:=l_working_hours+l_hour_duration;
3662
3663
3664 END LOOP csr_absence_period;
3665
3666 p_group_calendar_days:=l_calendar_days;
3667 p_group_working_days:=l_working_days;
3668 p_group_working_hours:=l_working_hours;
3669
3670 RETURN 1;
3671 EXCEPTION
3672 WHEN OTHERS THEN
3673 RETURN 0;
3674
3675 END Get_Sickness_Group_Details;
3676 END PAY_SE_ABSENCE;
[Home] [Help]