[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_SICKPAY_PKG
Source
1 PACKAGE BODY pay_dk_sickpay_pkg AS
2 /* $Header: pydksckp.pkb 120.2 2012/01/19 09:09:24 rpahune ship $ */
3
4 function get_worked_hours(
5 p_assignment_id IN number
6 ,p_period_start_date IN date
7 ,p_period_end_date IN date)RETURN NUMBER is
8
9 l_assignment_id NUMBER;
10 l_period_start_date DATE;
11 l_period_end_date DATE;
12 l_period_start_time varchar2(5);
13 l_period_end_time varchar2(5);
14
15 l_input_value varchar2(30);
16 l_screen_value varchar2(30);
17 l_rec_start_date l_type;
18 l_rec_end_date l_type;
19 l_rec_start_time l_type;
20 l_rec_end_time l_type;
21
22 l_records number;
23 l_absent_hours number;
24 l_return number;
25 l_duration number;
26 l_days_or_hours varchar2(2);
27 l_include_event varchar2(2);
28 l_hours_in_period number;
29 l_worked_hours number;
30
31 l_start_date date;
32 l_end_date date;
33 l_start_time varchar2(10);
34 l_end_time varchar2(10);
35
36
37 CURSOR csr_get_details(p_period_start_date DATE,p_period_end_date DATE,p_assignment_id NUMBER,p_input_value VARCHAR2) is
38 SELECT eev.screen_entry_value screen_entry_value
39 FROM per_all_assignments_f asg
40 ,per_all_people_f per
41 ,pay_element_links_f el
42 ,pay_element_types_f et
43 ,pay_input_values_f iv
44 ,pay_element_entries_f ee
45 ,pay_element_entry_values_f eev
46 WHERE asg.assignment_id = p_assignment_id
47 AND per.person_id = asg.person_id
48 AND et.element_name IN ('Absent Sick','Absent Holiday')
49 AND et.legislation_code = 'DK'
50 AND iv.element_type_id = et.element_type_id
51 AND iv.name = p_input_value
52 AND el.business_group_id = per.business_group_id
53 AND el.element_type_id = et.element_type_id
54 AND ee.element_link_id = el.element_link_id
55 and ee.assignment_id = asg.assignment_id
56 AND eev.element_entry_id = ee.element_entry_id
57 AND eev.input_value_id = iv.input_value_id
58 AND ee.effective_start_date BETWEEN per.effective_start_date AND per.effective_end_date --9455098
59 AND ee.effective_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date --9455098
60 AND ((p_period_start_date BETWEEN ee.effective_start_date AND ee.effective_end_date)
61 OR
62 (p_period_end_date BETWEEN ee.effective_start_date AND ee.effective_end_date)
63 OR
64 (p_period_start_date < ee.effective_start_date AND p_period_end_date > ee.effective_end_date))
65 ORDER BY ee.element_entry_id;
66
67
68 begin
69
70 l_period_start_date := p_period_start_date;
71 l_period_end_date := p_period_end_date;
72 l_assignment_id := p_assignment_id;
73
74 l_period_start_time := '00.00';
75 l_period_end_time := '23.59';
76
77 l_hours_in_period :=0;
78 l_worked_hours :=0;
79
80 hr_utility.set_location('get worked Hours function call',10);
81
82 l_input_value := 'Start Date';
83 OPEN csr_get_details(l_period_start_date ,l_period_end_date ,l_assignment_id ,l_input_value );
84 FETCH csr_get_details BULK COLLECT INTO l_rec_start_date;
85 CLOSE csr_get_details;
86
87 l_input_value := 'End Date';
88 OPEN csr_get_details(l_period_start_date ,l_period_end_date ,l_assignment_id ,l_input_value );
89 FETCH csr_get_details BULK COLLECT INTO l_rec_end_date;
90 CLOSE csr_get_details;
91
92 l_input_value := 'Start Time';
93 OPEN csr_get_details(l_period_start_date ,l_period_end_date ,l_assignment_id ,l_input_value );
94 FETCH csr_get_details BULK COLLECT INTO l_rec_start_time;
95 CLOSE csr_get_details;
96
97 l_input_value := 'End Time';
98 OPEN csr_get_details(l_period_start_date ,l_period_end_date ,l_assignment_id ,l_input_value );
99 FETCH csr_get_details BULK COLLECT INTO l_rec_end_time;
100 CLOSE csr_get_details;
101
102 l_records := l_rec_start_date.count;
103 l_absent_hours := 0;
104 l_return := -1;
105 l_duration := -1;
106 l_days_or_hours := 'H';
107 l_include_event := 'Y';
108
109 FOR l_index IN 1 .. l_records LOOP
110 l_start_date := to_date(substr(l_rec_start_date(l_index),1,10),'YYYY/MM/DD');
111 l_end_date := to_date(substr(l_rec_end_date(l_index),1,10),'YYYY/MM/DD');
112 l_start_time := l_rec_start_time(l_index);
113 l_end_time := l_rec_end_time(l_index);
114
115 IF (l_start_date < l_period_start_date) then
116 l_start_date := l_period_start_date;
117 END IF;
118
119 IF (l_end_date > l_period_end_date) then
120 l_end_date := l_period_end_date;
121 END IF;
122
123 l_return := hr_loc_work_schedule.calc_sch_based_dur(
124 p_assignment_id => l_assignment_id,
125 p_days_or_hours => l_days_or_hours,
126 p_include_event => l_include_event,
127 p_date_start => l_start_date,
128 p_date_end => l_end_date,
129 p_time_start => l_start_time,
130 p_time_end => l_end_time,
131 p_duration => l_duration);
132
133 /* Handled availability of work schedule for Sickness Report */
134 IF l_return <> 0 then
135 l_worked_hours := -1;
136 ELSE
137 l_absent_hours := l_absent_hours + l_duration;
138 END if;
139 END LOOP;
140 IF l_worked_hours = -1 then
141 RETURN l_worked_hours;
142 END IF;
143 /* Handled availability of work schedule for Sickness Report */
144
145 l_duration := -1;
146 l_include_event := 'Y';
147
148 l_return := hr_loc_work_schedule.calc_sch_based_dur(
149 p_assignment_id => l_assignment_id,
150 p_days_or_hours => l_days_or_hours,
151 p_include_event => l_include_event,
152 p_date_start => l_period_start_date,
153 p_date_end => l_period_end_date,
154 p_time_start => l_period_start_time,
155 p_time_end => l_period_end_time,
156 p_duration => l_duration);
157
158 l_hours_in_period := l_duration;
159 l_worked_hours := l_hours_in_period - l_absent_hours;
160
161
162 RETURN l_worked_hours;
163 END get_worked_hours;
164
165
166 /*Bug 5047360 fix- Passing p_abs_start_time and p_abs_end_time*/
167 FUNCTION get_sickness_dur_details
168 (p_assignment_id IN NUMBER
169 ,p_effective_date IN DATE
170 ,p_abs_start_date IN DATE
171 ,p_abs_end_date IN DATE
172 ,p_abs_start_time IN VARCHAR2 --Bug 5047360 fix
173 ,p_abs_end_time IN VARCHAR2 --Bug 5047360 fix
174 ,p_start_date OUT NOCOPY DATE
175 ,p_end_date OUT NOCOPY DATE
176 ,p_sick_days OUT NOCOPY NUMBER
177 ,p_sick_hours OUT NOCOPY NUMBER
178 ) RETURN NUMBER IS
179 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE ) IS
180 SELECT ee.element_entry_id element_entry_id
181 , eev1.screen_entry_value screen_entry_value
182 , iv1.name
183 FROM per_all_assignments_f asg1
184 ,per_all_assignments_f asg2
185 ,per_all_people_f per
186 ,pay_element_links_f el
187 ,pay_element_types_f et
188 ,pay_input_values_f iv1
189 ,pay_element_entries_f ee
190 ,pay_element_entry_values_f eev1
191 WHERE asg1.assignment_id = p_assignment_id
192 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
193 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
194 AND per.person_id = asg1.person_id
195 AND asg2.person_id = per.person_id
196 AND et.element_name = 'Override Sickness Duration'
197 AND et.legislation_code = 'DK'
198 AND iv1.element_type_id = et.element_type_id
199 /*Bug 5047360 fix */
200 /* AND iv1.name in ('Start Date', 'End Date', 'Sick Days', 'Sick Hours')*/
201 AND iv1.name in ('Start Date', 'End Date', 'Sick Days','Start Time','End Time', 'Sick Hours')
202 AND el.business_group_id = per.business_group_id
203 AND el.element_type_id = et.element_type_id
204 AND ee.assignment_id = asg2.assignment_id
205 AND ee.element_link_id = el.element_link_id
206 AND eev1.element_entry_id = ee.element_entry_id
207 AND eev1.input_value_id = iv1.input_value_id
208 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
209 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
210 ORDER BY ee.element_entry_id, DECODE(iv1.name, 'Start Date', 1,'End Date', 2, 'Sick Days', 3,'Start Time', 4,'End Time', 5,'Sick Hours', 6) ;
211 --
212 TYPE l_record is record (eeid pay_element_entries_f.element_entry_id%TYPE,
213 eevalue pay_element_entry_values_f.screen_entry_value%TYPE,
214 eename pay_input_values_f.name%TYPE );
215 l_rec l_record;
216 TYPE l_table is table of l_record index by BINARY_INTEGER;
217 l_tab l_table;
218
219 l_start_date date;
220 l_end_date date;
221 l_sick_hours number;
222 l_sick_days number;
223 l_counter number ;
224 l_bool_match boolean;
225 l_num_match number;
226 /*Bug 5047360 fix */
227 l_start_time pay_element_entry_values_f.screen_entry_value%TYPE;
228 l_end_time pay_element_entry_values_f.screen_entry_value%TYPE;
229 --
230 BEGIN
231 --
232 l_counter := 1;
233 l_bool_match := FALSE;
234
235 -- Open cursor to fetch all screen entry values of Sickness Duration element.
236 OPEN get_details(p_assignment_id , p_effective_date );
237 -- Assign the values to a table type
238 FETCH get_details BULK COLLECT INTO l_tab;
239 CLOSE get_details;
240
241 -- Loop through each values for processing.
242 FOR l_cur in 1..l_tab.count LOOP
243 -- Assign values to local variables.
244 IF l_tab(l_cur).eename = 'Start Date' THEN
245 l_start_date := to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss') ;
246 elsif l_tab(l_cur).eename = 'End Date' THEN
247 l_end_date := to_date(l_tab(l_cur).eevalue,'yyyy/mm/dd hh24:mi:ss');
248 elsif l_tab(l_cur).eename = 'Sick Days' THEN
249 l_sick_days := l_tab(l_cur).eevalue;
250 /*Bug 5047360 fix */
251 elsif l_tab(l_cur).eename = 'Start Time' THEN
252 l_start_time := NVL(l_tab(l_cur).eevalue,'0');
253 elsif l_tab(l_cur).eename = 'End Time' THEN
254 l_end_time := NVL(l_tab(l_cur).eevalue,'0');
255 elsif l_tab(l_cur).eename = 'Sick Hours' THEN
256 l_sick_hours := l_tab(l_cur).eevalue;
257 end if;
258 -- Check no. of input values of override element is 4
259 --IF l_counter < 4 then
260 /*Bug 5047360 fix */
261 -- Check no. of input values of override element is 6
262 IF l_counter < 6 then
263 l_counter := l_counter + 1;
264 else
265 -- Check override element's start and end date matches with Absent element.
266 --if l_start_date = p_abs_start_date and l_end_date = p_abs_end_date then
267 /*Bug 5047360 fix-Check override element's start date,end date,start time and end time
268 matches with Absent element */
269 IF l_start_date = p_abs_start_date AND l_end_date = p_abs_end_date
270 AND l_start_time = p_abs_start_time AND l_end_time = p_abs_end_time THEN
271 -- Multiple entry exists with same start and end date
272 IF l_bool_match THEN
273 p_start_date := null;
274 p_end_date := null;
275 p_sick_days := null;
276 p_sick_hours := null;
277 return -1;
278 -- Exact match found
279 ELSE
280 l_bool_match := True;
281 END IF;
282 -- Assign input values to output variables.
283 p_start_date := l_start_date;
284 p_end_date := l_end_date;
285 p_sick_days := l_sick_days;
286 p_sick_hours := l_sick_hours;
287 end if;
288 l_counter := 1;
289 end if;
290 END LOOP;
291
292 -- Match found successfully
293 IF p_start_date is not null then
294 RETURN 1;
295 -- Override element exists but date doesnt match.
296 /*Bug 5047360 fix- commenting the else if part*/
297 /*elsif p_start_date is null and l_tab.count > 0 then
298 RETURN 2;*/
299 -- No override element attached
300 else
301 RETURN 0;
302 end if;
303 --
304 END get_sickness_dur_details;
305
306
307 FUNCTION get_le_sickpay_details
308 (p_effective_date IN DATE,
309 p_org_id IN NUMBER,
310 p_section27 OUT NOCOPY VARCHAR2
311 ) RETURN NUMBER is
312
313 /* Modified cursor */
314 CURSOR csr_get_sickpay_defaults(p_effective_date DATE, p_org_id NUMBER) is
315 SELECT org_information1
316 FROM hr_organization_information
317 WHERE organization_id = p_org_id
318 and org_information_context = 'DK_SICKPAY_DEFAULTS'
319 AND p_effective_date BETWEEN fnd_date.canonical_to_date( org_information2) and fnd_date.canonical_to_date( org_information3)
320 order by org_information2;
321 TYPE l_record is record (sec_27 varchar2(1));
322 l_rec l_record;
323 TYPE l_table is table of l_record index by BINARY_INTEGER;
324 l_tab l_table;
325 begin
326 OPEN csr_get_sickpay_defaults(p_effective_date,p_org_id);
327 FETCH csr_get_sickpay_defaults BULK COLLECT INTO l_tab;
328 CLOSE csr_get_sickpay_defaults;
329 IF l_tab.COUNT = 1 then
330 p_section27 := l_tab(1).sec_27;
331 RETURN 1;
332 Elsif l_tab.COUNT = 0 then
333 RETURN 0;
334 else
335 RETURN -1;
336 END if;
337 END get_le_sickpay_details;
338
339 /*Bug 5020916 fix - Fucntion to get the section 28 value based on the payroll processing start date*/
340 FUNCTION get_section28_details
341 (p_assignment_id IN NUMBER
342 ,p_effective_date IN DATE --payroll processing start date
343 ) RETURN VARCHAR2 IS
344 CURSOR csr_section28 IS
345 SELECT
346 NVL(hsck.segment18,'N')
347 FROM
348 per_all_assignments_f paaf
349 ,hr_soft_coding_keyflex hsck
350 WHERE
351 paaf.assignment_id = p_assignment_id
352 AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
353 AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;
354
355 l_section28_reg hr_soft_coding_keyflex.segment18%TYPE ;
356
357 BEGIN
358 OPEN csr_section28;
359 FETCH csr_section28 INTO l_section28_reg;
360 CLOSE csr_section28;
361 RETURN l_section28_reg;
362 END get_section28_details;
363
364
365 /* Bug fix 5045710, added function get_worked_days */
366 FUNCTION get_worked_days(p_assignment_id IN number
367 ,p_period_start_date IN date
368 ,p_period_end_date IN date)RETURN NUMBER is
369
370 l_assignment_id NUMBER;
371 l_period_start_date DATE;
372 l_period_end_date DATE;
373 l_period_start_time varchar2(5);
374 l_period_end_time varchar2(5);
375
376 l_input_value varchar2(30);
377 l_screen_value varchar2(30);
378 l_rec_start_date l_type;
379 l_rec_end_date l_type;
380 l_rec_start_time l_type;
381 l_rec_end_time l_type;
382
383 l_records number;
384 l_absent_days number;
385 l_return number;
386 l_duration number;
387 l_days_or_hours varchar2(2);
391
388 l_include_event varchar2(2);
389 l_days_in_period number;
390 l_worked_days number;
392 l_start_date date;
393 l_end_date date;
394 l_start_time varchar2(10);
395 l_end_time varchar2(10);
396
397
398 CURSOR csr_get_details(p_period_start_date DATE,p_period_end_date DATE,p_assignment_id NUMBER,p_input_value VARCHAR2) is
399 SELECT eev.screen_entry_value screen_entry_value
400 FROM per_all_assignments_f asg
401 ,per_all_people_f per
402 ,pay_element_links_f el
403 ,pay_element_types_f et
404 ,pay_input_values_f iv
405 ,pay_element_entries_f ee
406 ,pay_element_entry_values_f eev
407 WHERE asg.assignment_id = p_assignment_id
408 AND per.person_id = asg.person_id
409 AND et.element_name IN ('Absent Sick','Absent Holiday')
410 AND et.legislation_code = 'DK'
411 AND iv.element_type_id = et.element_type_id
412 AND iv.name = p_input_value
413 AND el.business_group_id = per.business_group_id
414 AND el.element_type_id = et.element_type_id
415 AND ee.element_link_id = el.element_link_id
416 and ee.assignment_id = asg.assignment_id
417 AND eev.element_entry_id = ee.element_entry_id
418 AND eev.input_value_id = iv.input_value_id
419 AND ee.effective_start_date BETWEEN per.effective_start_date AND per.effective_end_date --9455098
420 AND ee.effective_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date --9455098
421 AND ((p_period_start_date BETWEEN ee.effective_start_date AND ee.effective_end_date)
422 OR
423 (p_period_end_date BETWEEN ee.effective_start_date AND ee.effective_end_date)
424 OR
425 (p_period_start_date < ee.effective_start_date AND p_period_end_date > ee.effective_end_date))
426 ORDER BY ee.element_entry_id;
427
428
429 begin
430
431 l_period_start_date := p_period_start_date;
432 l_period_end_date := p_period_end_date;
433 l_assignment_id := p_assignment_id;
434
435 l_period_start_time := '00.00';
436 l_period_end_time := '23.59';
437
438 l_days_in_period :=0;
439 l_worked_days :=0;
440
441 l_input_value := 'Start Date';
442 OPEN csr_get_details(l_period_start_date ,l_period_end_date ,l_assignment_id ,l_input_value );
443 FETCH csr_get_details BULK COLLECT INTO l_rec_start_date;
444 CLOSE csr_get_details;
445
446 l_input_value := 'End Date';
447 OPEN csr_get_details(l_period_start_date ,l_period_end_date ,l_assignment_id ,l_input_value );
448 FETCH csr_get_details BULK COLLECT INTO l_rec_end_date;
449 CLOSE csr_get_details;
450
451 l_input_value := 'Start Time';
452 OPEN csr_get_details(l_period_start_date ,l_period_end_date ,l_assignment_id ,l_input_value );
453 FETCH csr_get_details BULK COLLECT INTO l_rec_start_time;
454 CLOSE csr_get_details;
455
456 l_input_value := 'End Time';
457 OPEN csr_get_details(l_period_start_date ,l_period_end_date ,l_assignment_id ,l_input_value );
458 FETCH csr_get_details BULK COLLECT INTO l_rec_end_time;
459 CLOSE csr_get_details;
460
461 l_records := l_rec_start_date.count;
462 l_absent_days := 0;
463 l_return := -1;
464 l_duration := -1;
465 l_days_or_hours := 'D';
466 l_include_event := 'N';
467
468 FOR l_index IN 1 .. l_records LOOP
469 l_start_date := to_date(substr(l_rec_start_date(l_index),1,10),'YYYY/MM/DD');
470 l_end_date := to_date(substr(l_rec_end_date(l_index),1,10),'YYYY/MM/DD');
471
472 l_start_time := '00.00';
473 l_end_time := '23.59';
474
475 IF (l_start_date < l_period_start_date) then
476 l_start_date := l_period_start_date;
477 END IF;
478
479 IF (l_end_date > l_period_end_date) then
480 l_end_date := l_period_end_date;
481 END IF;
482
483 l_duration := -1;
484 l_return := hr_loc_work_schedule.calc_sch_based_dur(
485 p_assignment_id => l_assignment_id,
486 p_days_or_hours => l_days_or_hours,
487 p_include_event => l_include_event,
488 p_date_start => l_start_date,
489 p_date_end => l_end_date,
490 p_time_start => l_start_time,
491 p_time_end => l_end_time,
492 p_duration => l_duration);
493
494 /* Handled availability of work schedule for Sickness Report */
495 IF l_return <> 0 then
496 l_worked_days := -1;
497 ELSE
498 l_absent_days := l_absent_days + l_duration;
499 END if;
500 END LOOP;
501 IF l_worked_days = -1 then
502 RETURN l_worked_days;
503 END IF;
504 /* Handled availability of work schedule for Sickness Report */
505
506 l_duration := -1;
507 l_include_event := 'Y';
508 l_days_or_hours := 'D';
509
510 l_return := hr_loc_work_schedule.calc_sch_based_dur(
511 p_assignment_id => l_assignment_id,
512 p_days_or_hours => l_days_or_hours,
513 p_include_event => l_include_event,
514 p_date_start => l_period_start_date,
515 p_date_end => l_period_end_date,
516 p_time_start => l_period_start_time,
517 p_time_end => l_period_end_time,
518 p_duration => l_duration);
519
520 l_days_in_period := l_duration;
521 l_worked_days := l_days_in_period - l_absent_days;
525
522
523 RETURN l_worked_days;
524
526 END get_worked_days;
527
528
529 FUNCTION get_worked_hours_flag(p_assignment_id IN number
530 ,p_worked_days_limit IN number
531 ,p_worked_hours_limit IN number
532 ,p_period_end_date IN date) RETURN varchar2 is
533
534 l_assignment_id NUMBER;
535 l_worked_days_limit NUMBER;
536 l_worked_hours_limit NUMBER;
537 l_period_start_date DATE;
538 l_period_end_date DATE;
539 l_worked_hours number;
540 l_worked_days number;
541 l_total_worked_hours number;
542 l_total_worked_days number;
543 l_return varchar2(2);
544
545 begin
546
547 l_assignment_id := p_assignment_id;
548 l_worked_days_limit := p_worked_days_limit;
549 l_worked_hours_limit := p_worked_hours_limit;
550 l_period_end_date := p_period_end_date - 1;
551 l_period_start_date := p_period_end_date - l_worked_days_limit;
552
553 l_total_worked_days := -1;
554 l_total_worked_hours := -1;
555 l_worked_hours := -1;
556 l_worked_days := -1;
557 l_return := 'N';
558
559 l_total_worked_hours := get_worked_hours(
560 l_assignment_id
561 ,l_period_start_date
562 ,l_period_end_date);
563
564 l_total_worked_days := get_worked_days(
565 l_assignment_id
566 ,l_period_start_date
567 ,l_period_end_date);
568 /* reopned bug fix 5045710, following statement caused problem, which is not required
569 l_total_worked_days := l_total_worked_days - 1;
570 */
571
572 /* reopned bug fix 5045710, following conditions corrected
573 IF l_worked_hours > l_worked_hours_limit THEN
574 l_return := 'Y';
575 RETURN l_return;
576 ELSIF l_total_worked_days >= l_worked_days_limit AND l_worked_hours < l_worked_hours_limit THEN
577 l_return := 'N';
578 RETURN l_return;
579 END IF;
580 */
581
582 IF l_total_worked_hours >= l_worked_hours_limit THEN
583 l_return := 'Y';
584 RETURN l_return;
585 ELSIF l_total_worked_days >= l_worked_days_limit AND l_total_worked_hours < l_worked_hours_limit THEN
586 l_return := 'N';
587 RETURN l_return;
588 END IF;
589
590 l_period_end_date := l_period_start_date - 1;
591 l_period_start_date := l_period_start_date - (l_worked_days_limit - l_total_worked_days);
592
593 WHILE (l_total_worked_hours < l_worked_hours_limit AND l_total_worked_days < l_worked_days_limit)
594 LOOP
595
596 l_worked_hours := get_worked_hours(
597 l_assignment_id
598 ,l_period_start_date
599 ,l_period_end_date);
600 l_total_worked_hours := l_total_worked_hours + l_worked_hours;
601
602 l_worked_days := get_worked_days(
603 l_assignment_id
604 ,l_period_start_date
605 ,l_period_end_date);
606 l_total_worked_days := l_total_worked_days + l_worked_days;
607
608 l_period_end_date := l_period_start_date - 1;
609 l_period_start_date := l_period_start_date - (l_worked_days_limit - l_total_worked_days);
610
611 END LOOP;
612
613 IF l_total_worked_hours > l_worked_hours_limit THEN
614 l_return := 'Y';
615 ELSE
616 l_return := 'N';
617 END IF;
618
619 RETURN l_return;
620
621 END get_worked_hours_flag;
622
623 END pay_dk_sickpay_pkg;