1 PACKAGE BODY PAY_DK_HOLIDAY_PAY_PKG AS
2 /* $Header: pydkholp.pkb 120.17 2012/01/19 09:02:59 rpahune ship $ */
3 --
4 --
5
6 FUNCTION get_allowance_perc(p_payroll_id NUMBER
7 ,p_date_earned DATE ) RETURN NUMBER IS
8
9
10
11 l_value PER_TIME_PERIODS.PRD_INFORMATION1%TYPE;
12 CURSOR get_perc_from_ddf(p_payroll_id NUMBER , p_date_earned DATE) IS
13 SELECT nvl(PRD_INFORMATION1 ,0)
14 FROM PER_TIME_PERIODS ptp
15 WHERE PAYROLL_ID = p_payroll_id
16 AND p_date_earned BETWEEN ptp.START_DATE AND ptp.END_DATE;
17
18
19 BEGIN
20 OPEN get_perc_from_ddf(p_payroll_id, p_date_earned);
21 FETCH get_perc_from_ddf INTO l_value;
22 CLOSE get_perc_from_ddf;
23 /* Change the to_number to fnd_number.canonical_to_number */
24 RETURN fnd_number.canonical_to_number(l_value);
25
26 END get_allowance_perc;
27
28 /* 10092419 start */
29 FUNCTION get_hol_Unpaid_details
30
31 (p_org_id IN NUMBER,
32 p_unpaid_def_Y_N OUT NOCOPY VARCHAR2,
33 p_def_5day_days OUT NOCOPY NUMBER,
34 p_def_6day_days OUT NOCOPY NUMBER
35 ) RETURN NUMBER is
36
37 CURSOR csr_get_hol_rates(p_org_id NUMBER) is
38 SELECT org_information10, fnd_number.canonical_to_number(org_information12),fnd_number.canonical_to_number(org_information13)
39 FROM hr_organization_information
40 WHERE organization_id = p_org_id
41 and org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
42
43 BEGIN
44
45 OPEN csr_get_hol_rates(p_org_id);
46 FETCH csr_get_hol_rates INTO p_unpaid_def_Y_N,p_def_5day_days,p_def_6day_days;
47 CLOSE csr_get_hol_rates;
48
49 RETURN 1;
50
51
52 END get_hol_Unpaid_details;
53 /* 10092419 end */
54 FUNCTION get_prev_bal(p_assignment_id NUMBER
55 , p_balance_name VARCHAR2
56 , p_balance_dim VARCHAR2
57 , p_virtual_date DATE) RETURN NUMBER IS
58
59 l_context1 PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
60 l_value NUMBER;
61
62
63 CURSOR get_dbal_id(p_balance_name VARCHAR2 , p_balance_dim VARCHAR2) IS
64 SELECT pdb.defined_balance_id
65 FROM pay_defined_balances pdb
66 ,pay_balance_types pbt
67 ,pay_balance_dimensions pbd
68 WHERE pbt.legislation_code='DK'
69 AND pbt.balance_name = p_balance_name
70 AND pbd.legislation_code = 'DK'
71 AND pbd.database_item_suffix = p_balance_dim
72 AND pdb.balance_type_id = pbt.balance_type_id
73 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
74
75
76 BEGIN
77 OPEN get_dbal_id(p_balance_name, p_balance_dim);
78 FETCH get_dbal_id INTO l_context1;
79 CLOSE get_dbal_id;
80
81 --12398485
82 BEGIN
83 l_value := pay_balance_pkg.get_value(l_context1,p_assignment_id,p_virtual_date);
84 EXCEPTION When others THEN
85 RETURN 0;
86 END;
87
88 IF l_value is null THEN
89 l_value := 0;
90 END IF;
91 --12398485
92
93 RETURN l_value;
94
95 END get_prev_bal;
96
97
98 /* Bug fix 4950983 added parameter p_work_pattern */
99 /* Bug#8293282 fix - Added out parameter p_hol_all_reduction */
100 FUNCTION get_le_holiday_details
101
102 (p_org_id IN NUMBER,
103 p_sal_accrual_rate OUT NOCOPY NUMBER,
104 p_hourly_accrual_rate OUT NOCOPY NUMBER,
105 p_use_holiday_card OUT NOCOPY VARCHAR2,
106 p_work_pattern OUT NOCOPY VARCHAR2,
107 p_hol_all_reduction OUT NOCOPY VARCHAR2) RETURN NUMBER is
108
109 /* Bug fix 4950983 added org_information1 in select clause */
110 /* Change the to_number to fnd_number.canonical_to_number */
111 CURSOR csr_get_hol_rates(p_org_id NUMBER) is
112 SELECT fnd_number.canonical_to_number(org_information4), fnd_number.canonical_to_number(org_information3), org_information5, org_information1, org_information8 --9559824 abraghun - swapped first two columns.
113 FROM hr_organization_information
114 WHERE organization_id = p_org_id
115 and org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
116
117 BEGIN
118
119 /* Bug fix 4950983 added p_work_pattern in fetch statement */
120 OPEN csr_get_hol_rates(p_org_id);
121 FETCH csr_get_hol_rates INTO p_sal_accrual_rate,p_hourly_accrual_rate,p_use_holiday_card,p_work_pattern,p_hol_all_reduction;
122 CLOSE csr_get_hol_rates;
123
124 RETURN 1;
125
126
127 END get_le_holiday_details;
128
129
130 FUNCTION get_le_employment_details
131 (p_org_id IN NUMBER
132 ,p_le_work_hours OUT NOCOPY NUMBER
133 ,p_freq OUT NOCOPY VARCHAR2
134 )RETURN NUMBER IS
135 --
136 CURSOR get_details(p_org_id VARCHAR2) IS
137 SELECT hoi.org_information3
138 , hoi.org_information4
139 FROM hr_organization_information hoi
140 WHERE hoi.org_information_context='DK_EMPLOYMENT_DEFAULTS'
141 AND hoi.organization_id = p_org_id ;
142
143 --
144 BEGIN
145 --
146 OPEN get_details(p_org_id);
147 FETCH get_details INTO p_le_work_hours,p_freq;
148 CLOSE get_details;
149
150
151 RETURN 1;
152 --
153 END get_le_employment_details;
154
155
156 FUNCTION get_eligible_days(p_assignment_id IN NUMBER
157 ,p_org_id IN NUMBER
158 ,p_period_start_date IN DATE
159 ,p_period_end_date IN DATE
160 ,p_5days OUT NOCOPY NUMBER
161 ,p_6days OUT NOCOPY NUMBER) RETURN NUMBER is
162
163 /* return 5day days and 6day days as out parameter, in the formula calculate accrual*/
164 l_assignment_id NUMBER;
165 l_org_id NUMBER;
166 l_elig_start_date DATE;
167 l_elig_end_date DATE;
168 l_wrkp_start_date DATE;
169 l_wrkp_end_date DATE;
170
171 l_input_value VARCHAR2(80);
172
173 l_elig_record l_rec;
174 l_wrkp_record l_rec;
175
176 l_elig_records NUMBER;
177 l_wrkp_records NUMBER;
178 l_el_start_date DATE;
179 l_el_end_date DATE;
180 l_eligible VARCHAR2(5);
181
182 l_wp_start_date DATE;
183 l_wp_end_date DATE;
184 l_work_pattern VARCHAR2(5);
185
186 l_le_work_pattern VARCHAR2(5);
187
188 l_5days NUMBER;
189 l_6days NUMBER;
190 l_eligibility_change VARCHAR2(1);
191
192 CURSOR csr_get_accrual_elig(p_assignment_id NUMBER,p_period_start_date DATE,p_period_end_date DATE,p_input_value VARCHAR2) IS
193 SELECT distinct eev.effective_start_date,eev.effective_end_date,eev.screen_entry_value
194 FROM per_all_assignments_f asg
195 ,per_all_people_f per
196 ,pay_element_links_f el
197 ,pay_element_types_f et
198 ,pay_input_values_f iv
199 ,pay_element_entries_f ee
200 ,pay_element_entry_values_f eev
201 WHERE asg.assignment_id = p_assignment_id
202 AND per.person_id = asg.person_id
203 AND et.element_name = 'Holiday Accrual'
204 AND et.legislation_code = 'DK'
205 AND iv.element_type_id = et.element_type_id
206 AND iv.name = p_input_value
207 AND el.business_group_id = per.business_group_id
208 AND el.element_type_id = et.element_type_id
209 AND ee.assignment_id = asg.assignment_id
210 AND ee.element_link_id = el.element_link_id
211 AND eev.element_entry_id = ee.element_entry_id
212 AND eev.input_value_id = iv.input_value_id
213 AND eev.effective_end_date >= p_period_start_date /* knadhan */
214 AND eev.effective_start_date <= p_period_end_date;
215 /*
216 AND eev.effective_start_date >= p_period_start_date
217 AND eev.effective_start_date <= p_period_end_date;
218 */
219 CURSOR csr_get_asg_work_pattern(p_assignment_id NUMBER,p_period_start_date DATE,p_period_end_date DATE) IS
220 SELECT effective_start_date, effective_end_date, segment10
221 FROM per_all_assignments_f paa,
222 hr_soft_coding_keyflex kf
223 WHERE assignment_id = p_assignment_id
224 AND paa.SOFT_CODING_KEYFLEX_ID = kf.SOFT_CODING_KEYFLEX_ID
225 AND ((p_period_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date)
226 OR
227 (p_period_end_date BETWEEN paa.effective_start_date AND paa.effective_end_date)
228 OR
229 (p_period_start_date < paa.effective_start_date AND p_period_end_date > paa.effective_end_date));
230
231
232 CURSOR csr_get_le_work_pattern(p_org_id NUMBER) IS
233 SELECT org_information1
234 FROM hr_organization_information
235 WHERE organization_id = p_org_id
236 and org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
237
238 BEGIN
239
240 l_elig_start_date := p_period_start_date;
241 l_elig_end_date := p_period_end_date;
242 l_assignment_id := p_assignment_id;
243 l_org_id := p_org_id;
244
245 l_le_work_pattern := ' ';
246 l_eligibility_change := 'Y';
247
248 l_5days := 0;
249 l_6days := 0;
250
251 l_input_value := 'Holiday Accrual Eligibility';
252 OPEN csr_get_accrual_elig(l_assignment_id,l_elig_start_date ,l_elig_end_date,l_input_value);
253 FETCH csr_get_accrual_elig BULK COLLECT INTO l_elig_record;
254 CLOSE csr_get_accrual_elig;
255
256 IF l_elig_record.count = 0 THEN
257 l_wrkp_start_date := l_elig_start_date;
258 l_wrkp_end_date := l_elig_end_date;
259
260 OPEN csr_get_asg_work_pattern(l_assignment_id,l_el_start_date ,l_el_end_date);
261 FETCH csr_get_asg_work_pattern BULK COLLECT INTO l_wrkp_record;
262 CLOSE csr_get_asg_work_pattern;
263
264 l_wrkp_records := l_wrkp_record.count;
265
266 IF l_wrkp_records = 0 THEN
267 IF l_le_work_pattern = '6DAY' THEN
268 l_6days := (l_wrkp_end_date - l_wrkp_start_date) + 1;
269 ELSE
270 l_5days := (l_wrkp_end_date - l_wrkp_start_date)+ 1;
271 END IF;
272 p_5days := l_5days;
273 p_6days := l_6days;
274 RETURN -1;
275 ELSE
276 FOR l_wrkp_index IN 1 .. l_wrkp_records LOOP
277 l_wp_start_date := l_wrkp_record(l_wrkp_index).date1;
278 l_wp_end_date := l_wrkp_record(l_wrkp_index).date2;
279 l_work_pattern := l_wrkp_record(l_wrkp_index).value;
280
281 IF (l_wp_start_date < l_wrkp_start_date) THEN
282 l_wp_start_date := l_wrkp_start_date;
283 END IF;
284
285 IF (l_wp_end_date > l_wrkp_end_date) THEN
286 l_wp_end_date := l_wrkp_end_date;
287 END IF;
288
289 IF l_work_pattern = '5DAY' THEN
290 l_5days := l_5days + (l_wp_end_date - l_wp_start_date) + 1;
291 ELSIF l_work_pattern = '6DAY' THEN
292 l_6days := l_6days + (l_wp_end_date - l_wp_start_date) + 1;
293 ELSIF l_le_work_pattern = '6DAY' THEN
294 l_6days := l_6days + (l_wp_end_date - l_wp_start_date) + 1;
295 ELSE
296 l_5days := l_5days + (l_wp_end_date - l_wp_start_date) + 1;
297 END IF;
298
299 END LOOP;
300 END IF;
301
302 p_5days := l_5days;
303 p_6days := l_6days;
304 RETURN 1;
305
306 ELSE
307 OPEN csr_get_le_work_pattern(l_org_id);
308 FETCH csr_get_le_work_pattern INTO l_le_work_pattern;
309 CLOSE csr_get_le_work_pattern;
310
311 l_elig_records := l_elig_record.count;
312
313 FOR l_elig_index IN 1 .. l_elig_records LOOP
314 l_el_start_date := l_elig_record(l_elig_index).date1;
315 l_el_end_date := l_elig_record(l_elig_index).date2;
316 l_eligible := l_elig_record(l_elig_index).value;
317
318 IF l_eligible = 'N' THEN
319 null;
320 ELSE
321
322 IF (l_el_start_date < l_elig_start_date) THEN
323 l_el_start_date := l_elig_start_date;
324 END IF;
325
326 IF (l_el_end_date > l_elig_end_date) THEN
327 l_el_end_date := l_elig_end_date;
328 END IF;
329
330 /* work pattern record*/
331 l_wrkp_start_date := l_el_start_date;
332 l_wrkp_end_date := l_el_end_date;
333
334 OPEN csr_get_asg_work_pattern(l_assignment_id,l_el_start_date ,l_el_end_date);
335 FETCH csr_get_asg_work_pattern BULK COLLECT INTO l_wrkp_record;
336 CLOSE csr_get_asg_work_pattern;
337
338 l_wrkp_records := l_wrkp_record.count;
339
340
341 FOR l_wrkp_index IN 1 .. l_wrkp_records LOOP
342 l_wp_start_date := l_wrkp_record(l_wrkp_index).date1;
343 l_wp_end_date := l_wrkp_record(l_wrkp_index).date2;
344 l_work_pattern := l_wrkp_record(l_wrkp_index).value;
345
346 IF (l_wp_start_date < l_wrkp_start_date) THEN
347 l_wp_start_date := l_wrkp_start_date;
348 END IF;
349
350 IF (l_wp_end_date > l_wrkp_end_date) THEN
351 l_wp_end_date := l_wrkp_end_date;
352 END IF;
353
354 IF l_work_pattern = '5DAY' THEN
355 l_5days := l_5days + (l_wp_end_date - l_wp_start_date) + 1;
356 ELSIF l_work_pattern = '6DAY' THEN
357 l_6days := l_6days + (l_wp_end_date - l_wp_start_date) + 1;
358 ELSIF l_le_work_pattern = '6DAY' THEN
359 l_6days := l_6days + (l_wp_end_date - l_wp_start_date) + 1;
360 ELSE
361 l_5days := l_5days + (l_wp_end_date - l_wp_start_date) + 1;
362 END IF;
363
364 END LOOP;
365 END IF;
366
367 END LOOP;
368 p_5days := l_5days;
369 p_6days := l_6days;
370 RETURN 1;
371 END IF;
372
373 END get_eligible_days;
374
375 /* Bug Fix 4947637, Added function get_weekdays */
376 FUNCTION get_weekdays(p_period_start_date IN DATE
377 ,p_period_end_date IN DATE
378 ,p_work_pattern IN VARCHAR) RETURN NUMBER IS
379
380 /* Version 115.8 Bug fix 5185910 */
381 /* Commented to add new logic
382
383 l_abs_start_date date;
384 l_abs_end_date date;
385 l_loop_start_date date;
386 l_days number;
390 l_index number;
387 l_start_d number;
388 l_end_d number;
389 l_work_pattern varchar2(6);
391 l_weekdays number;
392 l_curr_date date;
393 l_d number;
394 */
395
396
397 /* Bug fix 5185910 , added cursor and variable */
398 /* Version 115.8 Bug fix 5185910 */
399 /* Commented to add new logic
400 CURSOR csr_get_territory IS
401 SELECT value FROM nls_database_parameters
402 WHERE parameter = 'NLS_TERRITORY';
403 l_territory varchar2(80);
404
405 begin
406 l_abs_start_date := p_period_start_date;
407 l_abs_end_date := p_period_end_date;
408 l_days := (l_abs_end_date - l_abs_start_date) + 1;
409 l_days := (l_abs_end_date - l_abs_start_date) + 1;
410 l_weekdays := 0;
411 l_curr_date := l_abs_start_date;
412 l_work_pattern := p_work_pattern;
413 */
414
415 /* Bug fix 5185910 , added fetch statement */
416 /* Version 115.8 Bug fix 5185910 */
417 /* Commented to add new logic
418 OPEN csr_get_territory;
419 FETCH csr_get_territory INTO l_territory;
420 CLOSE csr_get_territory;
421
422 IF l_work_pattern = '5DAY' then
423 FOR l_index IN 1..l_days
424 loop
425 l_curr_date := l_abs_start_date + (l_index - 1);
426 l_d := to_number(to_char(l_curr_date,'d'));
427 */
428 /* Bug fix 5084425 , Danish Weekends to be considered instead of American weekends
429 In American territory case Sunday is considered as day 1, where as in Denmark monday is
430 to be considered as day 1 of the week */
431 /*IF l_d NOT IN (7,1) then*/
432
433 /* Bug fix 5185910 commented following if statement
434 IF l_d NOT IN (6,7) then
435 l_weekdays := l_weekdays +1;
436 END IF;*/
437
438 /* Bug fix 5185910 , added following logic */
439 /* Version 115.8 Bug fix 5185910 */
440 /* Commented to add new logic
441
442 IF l_territory = 'DENMARK' THEN
443 IF l_d NOT IN (6,7) then
444 l_weekdays := l_weekdays +1;
445 END IF;
446 ELSE
447 IF l_d NOT IN (7,1) THEN
448 l_weekdays := l_weekdays +1;
449 END IF;
450 END IF;
451
452
453 END loop;
454 END if;
455 */
456
457 /* Version 115.8 Bug fix 5185910 */
458 /* Commented to add new logic
459 IF l_work_pattern = '6DAY' then
460 FOR l_index IN 1..l_days
461 loop
462 l_curr_date := l_abs_start_date + (l_index - 1);
463 l_d := to_number(to_char(l_curr_date,'d'));*/
464 /* Bug fix 5084425 , Danish Weekends to be considered instead of American weekends
465 In American territory case Sunday is considered as day 1, where as in Denmark monday is
466 to be considered as day 1 of the week */
467 /*IF l_d <> 1 then*/
468 /* Bug fix 5185910 commented following if statement
469 IF l_d <> 7 then
470 l_weekdays := l_weekdays +1;
471 END IF;*/
472
473 /* Bug fix 5185910 , added following logic */
474 /* Version 115.8 Bug fix 5185910 */
475 /* Commented to add new logic
476 IF l_territory = 'DENMARK' THEN
477 IF l_d <> 7 then
478 l_weekdays := l_weekdays +1;
479 END IF;
480 ELSE
481 IF l_d <> 1 THEN
482 l_weekdays := l_weekdays +1;
483 END IF;
484 END IF;
485
486 END loop;
487 END if;
488 */
489
490
491 /* Version 115.8 Bug fix 5185910 */
492 /* Following logic is added to determine weekdays */
493 v_st_date date;
494 v_en_date date;
495 v_beg_of_week date;
496 v_end_of_week date;
497 l_weekdays number;
498 v_work_pattern varchar2(20);
499 begin
500 v_st_date :=p_period_start_date;
501 v_en_date :=p_period_end_date;
502 l_weekdays := 0;
503 v_work_pattern := p_work_pattern;
504 if p_period_start_date > p_period_end_date then
505 return l_weekdays;
506 end if;
507 --Determine the Beginning of Week Date for Start Date
508 --and End of Week Date for End Date
509 v_beg_of_week := v_st_date - (get_day_of_week(v_st_date)-1);
510 v_end_of_week := v_en_date;
511 if get_day_of_week(v_en_date) NOT IN('1') then
512 v_end_of_week := v_en_date + (7- get_day_of_week(v_en_date)+1);
513 end if;
514 IF v_work_pattern = '5DAY' THEN
515 --Calculate the Total Week Days @ of 5 per week
516 l_weekdays := ((v_end_of_week-v_beg_of_week)/7)*5;
517 --Adjust the Total Week Days by subtracting
518 --No of Days before the Start Date
519 if (v_st_date > (v_beg_of_week+1)) then
520 l_weekdays := l_weekdays - (v_st_date - (v_beg_of_week+1)) ;
521 end if;
522 if v_end_of_week <> v_en_date then
523 v_end_of_week := v_end_of_week -2;
524 else
525 if v_st_date = v_en_date then
526 l_weekdays := 0;
527 end if;
528 end if;
529 --Adjust the Total Week Days by subtracting
530 --No of Days After the End Date
531 if (v_end_of_week - v_en_date) >= 0 then
532 l_weekdays := l_weekdays - (v_end_of_week - v_en_date) ;
533 end if;
534
535 ELSE
536 --Calculate the Total Week Days @ of 6 per week
537 l_weekdays := ((v_end_of_week-v_beg_of_week)/7)*6;
538 --Adjust the Total Week Days by subtracting
539 --No of Days before the Start Date
540 if (v_st_date > (v_beg_of_week+1)) then
541 l_weekdays := l_weekdays - (v_st_date - (v_beg_of_week+1)) ;
542 end if;
543 if v_end_of_week <> v_en_date then
544 v_end_of_week := v_end_of_week -1;
545 else
546 if v_st_date = v_en_date then
547 l_weekdays := 0;
548 end if;
549 end if;
550 --Adjust the Total Week Days by subtracting
551 --No of Days After the End Date
552 if (v_end_of_week - v_en_date) >= 0 then
553 l_weekdays := l_weekdays - (v_end_of_week - v_en_date) ;
554 end if;
555 END IF;
556
557 return (l_weekdays);
558
559
560 END get_weekdays;
561
562 /* Bug fix 5185910 , added function get_day_of_week
563 This Function returns the day of the week.
564 Sunday is considered to be the first day of the week*/
565 FUNCTION get_day_of_week(p_date DATE) RETURN NUMBER IS
566 l_reference_date date:=to_date('01/01/1984','DD/MM/YYYY');
567 v_index number;
568
569 BEGIN
570 v_index := abs(p_date - l_reference_date);
571 v_index := mod(v_index,7);
572 v_index := v_index + 1;
573 RETURN v_index;
574
575 END get_day_of_week;
576
577 /* Added for Public Holiday Pay */
578 FUNCTION get_pub_hol_pay_details(p_assignment_id IN NUMBER
579 ,p_organization_id IN NUMBER
580 ,p_effective_date IN DATE
581 ,p_sh_payment_rate OUT NOCOPY NUMBER)
582
583 RETURN NUMBER IS
584
585 l_sh_payment_rate NUMBER;
586 l_sh_payment_percentage NUMBER;
587
588 CURSOR csr_le_holidaypay(csr_organization_id IN NUMBER) IS
589 SELECT hoi.org_information6,
590 hoi.org_information7
591 FROM hr_organization_information hoi
592 WHERE organization_id = csr_organization_id
593 AND org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
594
595 CURSOR csr_asg_holidaypay IS
596 SELECT scl.segment19
597 ,scl.segment20
598 FROM hr_soft_coding_keyflex scl,
599 per_all_assignments_f paaf
600 WHERE scl.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
601 AND paaf.assignment_id = p_assignment_id
602 AND p_effective_date between paaf.effective_start_date and effective_end_date;
603
604 BEGIN
605
606 FOR csr_asg_val IN csr_asg_holidaypay
607 LOOP
608 l_sh_payment_rate := csr_asg_val.segment19;
609 l_sh_payment_percentage := csr_asg_val.segment20;
610 END LOOP;
611
612 IF l_sh_payment_rate IS NULL OR l_sh_payment_percentage IS NULL THEN
613
614 FOR csr_le_val IN csr_le_holidaypay(p_organization_id)
615 LOOP
616 IF l_sh_payment_rate IS NULL THEN
617 l_sh_payment_rate := csr_le_val.org_information6;
618 END IF;
619 IF l_sh_payment_percentage IS NULL THEN
620 l_sh_payment_percentage := csr_le_val.org_information7;
621 END IF;
622
623 END LOOP;
624 END IF;
625
626 p_sh_payment_rate := l_sh_payment_rate;
627
628 RETURN l_sh_payment_percentage;
629
630 END get_pub_hol_pay_details;
631
632 /*9495504 abraghun*/
633 FUNCTION get_max_carryover_days(p_assignment_id IN NUMBER
634 ,p_organization_id IN NUMBER
635 ,p_effective_date IN DATE
636 ,p_carryover_limit IN NUMBER) RETURN NUMBER is
637
638 ----Assignment setting----
639 CURSOR csr_assignment_carryover(c_assignment_id number) IS
640 SELECT
641 aei_information1 asg_max_carryover
642 FROM
643 per_assignment_extra_info
644 WHERE assignment_id = c_assignment_id
645 AND aei_information_category = 'DK_HOLIDAY_ENTITLEMENT';
646
647 ----Legal Employer setting----
648 CURSOR csr_organization_carryover(c_organization_id number) IS
649 SELECT
650 org_information9 org_max_carryover
651 FROM
652 hr_organization_information
653 WHERE organization_id = c_organization_id
654 AND org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
655
656 ----Global Setting----
657 /*CURSOR csr_global_carryover(c_effective_date DATE) IS
658 SELECT
659 global_value gbl_max_carryover
660 FROM
661 ff_globals_f
662 WHERE global_name = 'DK_HOLIDAY_MAX_CARRYOVER_DAYS'
663 AND legislation_code = 'DK'
664 AND c_effective_date BETWEEN effective_start_date AND effective_end_date;
665
666 l_gbl_max_carryover NUMBER;*/
667 l_org_max_carryover NUMBER;
668 l_asg_max_carryover NUMBER;
669 l_max_carryover NUMBER;
670
671 BEGIN
672
673 OPEN csr_assignment_carryover(p_assignment_id);
674 FETCH csr_assignment_carryover INTO l_asg_max_carryover;
675 CLOSE csr_assignment_carryover;
676
677 OPEN csr_organization_carryover(p_organization_id);
678 FETCH csr_organization_carryover INTO l_org_max_carryover;
679 CLOSE csr_organization_carryover;
680
681 /*OPEN csr_global_carryover(p_effective_date);
682 FETCH csr_global_carryover INTO l_gbl_max_carryover;
683 CLOSE csr_global_carryover;*/
684
685 /* Set Global Value to Zero if not set */
686 --l_gbl_max_carryover := NVL(l_gbl_max_carryover,0);
687
688 /* 9578958 fix Modified the logic not to use global value */
689 IF l_asg_max_carryover IS NOT NULL THEN
690 l_max_carryover := LEAST(l_asg_max_carryover,p_carryover_limit);
691 ELSIF l_org_max_carryover IS NOT NULL THEN
692 l_max_carryover := LEAST(l_org_max_carryover,p_carryover_limit);
693 ELSE
694 l_max_carryover := 0; -- Default carryover is zero
695 END IF;
696
697 RETURN l_max_carryover;
698
699 END get_max_carryover_days;
700
701 END PAY_DK_HOLIDAY_PAY_PKG;
[Home] [Help]