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