[Home] [Help]
PACKAGE BODY: APPS.PAY_ES_CALC_SS_EARNINGS
Source
1 PACKAGE BODY pay_es_calc_ss_earnings AS
2 /* $Header: pyesssec.pkb 120.11 2006/04/20 00:04:37 kseth noship $ */
3 --
4 START_OF_TIME CONSTANT DATE := TO_DATE('01/01/0001','DD/MM/YYYY');
5 END_OF_TIME CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
6 --
7 --------------------------------------------------------------------------------
8 -- GET_DEFINED_BAL_ID
9 --------------------------------------------------------------------------------
10 FUNCTION get_defined_bal_id(p_bal_name IN VARCHAR2
11 ,p_db_item_suffix IN VARCHAR2) RETURN NUMBER
12 IS
13 --
14 CURSOR get_def_bal_id IS
15 SELECT pdb.defined_balance_id
16 FROM pay_balance_types pbt
17 ,pay_balance_dimensions pbd
18 ,pay_defined_balances pdb
19 WHERE pdb.balance_type_id = pbt.balance_type_id
20 AND pdb.balance_dimension_id = pbd.balance_dimension_id
21 AND pbt.balance_name = p_bal_name
22 AND pbd.database_item_suffix = p_db_item_suffix;
23 --
24 l_def_bal_id NUMBER;
25 --
26 BEGIN
27 --
28 OPEN get_def_bal_id;
29 FETCH get_def_bal_id INTO l_def_bal_id;
30 CLOSE get_def_bal_id;
31 RETURN l_def_bal_id;
32 --
33 END get_defined_bal_id;
34 --
35 --------------------------------------------------------------------------------
36 -- Get_Absence_Details
37 --------------------------------------------------------------------------------
38 FUNCTION Get_Absence_Details(p_absence_attendance_id IN NUMBER
39 ,p_sickness_reason OUT NOCOPY VARCHAR2
40 ,p_sickness_category OUT NOCOPY VARCHAR2
41 ,p_temp_dis_start_date OUT NOCOPY DATE
42 ,p_sickness_end OUT NOCOPY DATE
43 ,p_info_1 OUT NOCOPY VARCHAR2
44 ,p_info_2 OUT NOCOPY VARCHAR2
45 ,p_info_3 OUT NOCOPY VARCHAR2
46 ,p_info_4 OUT NOCOPY VARCHAR2
47 ,p_info_5 OUT NOCOPY VARCHAR2
48 ,p_info_6 OUT NOCOPY VARCHAR2
49 ,p_info_7 OUT NOCOPY VARCHAR2
50 ,p_info_8 OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
51 --
52 CURSOR csr_abs_details(p_absence_attendance_id NUMBER) IS
53 SELECT paa.date_start start_date
54 ,NVL(paa.date_end,to_date('31-12-4712','dd-mm-yyyy')) end_date
55 ,paar.name Reason
56 ,paat.absence_category Reason_Category
57 ,paa.abs_information1
58 ,paa.abs_information2
59 ,paa.abs_information3
60 ,paa.abs_information4
61 ,paa.abs_information5
62 ,paa.abs_information6
63 ,paa.abs_information7
64 ,paa.abs_information8
65 FROM per_abs_attendance_reasons paar
66 ,per_absence_attendances paa
67 ,per_absence_attendance_types paat
68 WHERE paa.absence_attendance_id = p_absence_attendance_id
69 AND paa.absence_attendance_type_id = paat.absence_attendance_type_id
70 AND paa.abs_attendance_reason_id = paar.abs_attendance_reason_id (+);
71 --
72 BEGIN
73 --
74 OPEN csr_abs_details(p_absence_attendance_id);
75 FETCH csr_abs_details INTO p_temp_dis_start_date ,p_sickness_end
76 ,p_sickness_reason ,p_sickness_category
77 ,p_info_1 ,p_info_2 ,p_info_3 ,p_info_4
78 ,p_info_5 ,p_info_6 ,p_info_7 ,p_info_8 ;
79 CLOSE csr_abs_details;
80 RETURN p_Sickness_Reason;
81 --
82 END Get_Absence_Details;
83 --
84 --------------------------------------------------------------------------------
85 -- GET_CONTRIBUTION_DAYS
86 --------------------------------------------------------------------------------
87 FUNCTION Get_Contribution_Days(p_date_earned IN DATE
88 ,p_no_of_months IN NUMBER) RETURN NUMBER IS
89 --
90 l_ctr NUMBER := 0 ;
91 --
92 BEGIN
93 --
94 l_ctr := last_day(Add_months(p_date_earned, -1)) - last_day(Add_months(p_date_earned, -1 -p_no_of_months));
95 --
96 RETURN l_ctr ;
97 --
98 END Get_Contribution_Days;
99 --
100 --------------------------------------------------------------------------------
101 -- GET_PERSON_GENDER
102 --------------------------------------------------------------------------------
103 FUNCTION get_person_gender(p_assignment_id IN NUMBER
104 ,p_date_earned IN DATE) RETURN VARCHAR2 IS
105 --
106 CURSOR csr_get_emp_gender IS
107 SELECT papf.sex
108 FROM per_all_people_f papf
109 ,per_all_assignments_f paaf
110 WHERE paaf.assignment_id = p_assignment_id
111 AND papf.person_id = paaf.person_id
112 AND p_date_earned BETWEEN paaf.effective_start_date
113 AND paaf.effective_end_date
114 AND p_date_earned BETWEEN papf.effective_start_date
115 AND papf.effective_end_date;
116
117 --
118 l_Gender per_all_people_f.sex%TYPE;
119 --
120 BEGIN
121 --
122 OPEN csr_get_emp_gender;
123 FETCH csr_get_emp_gender INTO l_Gender;
124 CLOSE csr_get_emp_gender;
125 --
126 RETURN l_Gender ;
127 --
128 END get_person_gender;
129 --
130 --------------------------------------------------------------------------------
131 -- GET_DAYS_PREV_YEAR
132 --------------------------------------------------------------------------------
133 FUNCTION get_days_prev_year(p_date_earned IN DATE) RETURN NUMBER IS
134 --
135 l_Days NUMBER;
136 BEGIN
137 --
138 SELECT (to_date('01-01-'||to_char(p_date_earned,'yyyy'),'dd-mm-yyyy')
139 -to_date('01-01-'||to_char(to_number(to_char(p_date_earned,'yyyy'))-1),'dd-mm-yyyy'))
140 INTO l_Days
141 FROM dual;
142 --
143 RETURN l_Days;
144 --
145 END get_days_prev_year;
146 --
147 --------------------------------------------------------------------------------
148 -- GET_SS_CONTRIBUTION_DAYS
149 --------------------------------------------------------------------------------
150 FUNCTION get_ss_contribution_days(p_assignment_id IN NUMBER
151 ,p_balance_name IN VARCHAR2
152 ,p_database_item_suffix IN VARCHAR2
153 ,p_virtal_date IN DATE
154 ,p_span_years IN NUMBER)RETURN NUMBER IS
155 --
156 l_Days NUMBER;
157 l_span_days NUMBER;
158 l_def_bal_id NUMBER;
159 l_span_back_date DATE;
160 l_ne_span NUMBER;
161 --
162 BEGIN
163 l_def_bal_id := get_defined_bal_id(p_balance_name, p_database_item_suffix);
164 BEGIN
165 l_Days := pay_balance_pkg.get_value(l_def_bal_id,p_assignment_id,p_virtal_date);
166 EXCEPTION
167 WHEN no_data_found THEN
168 l_Days := 0;
169 END;
170 --
171 l_ne_span := p_span_years * 12;
172 l_span_back_date := ADD_MONTHS(p_virtal_date, -1 * FLOOR(l_ne_span)) - ( l_ne_span - FLOOR(l_ne_span)) * 30 + 1;
173 --
174 BEGIN
175 l_span_days := pay_balance_pkg.get_value(l_def_bal_id,p_assignment_id,l_span_back_date);
176 EXCEPTION
177 WHEN no_data_found THEN
178 l_span_days := 0;
179 END;
180 --
181 RETURN (l_Days - l_span_days);
182 --
183 END get_ss_contribution_days;
184 --
185 --------------------------------------------------------------------------------
186 -- GET_LINKED_ABSENCE_DETAILS
187 --------------------------------------------------------------------------------
188 FUNCTION get_linked_absence_details(p_absence_attendance_id IN NUMBER
189 ,p_disability_start_date IN DATE) RETURN NUMBER IS
190 --
191 CURSOR csr_abs_details(l_absence_attendance_id NUMBER
192 ,p_disability_start_date DATE) IS
193 SELECT paa.date_start start_date
194 ,paa.date_end end_date
195 ,paa.abs_information1 linked_absence
196 FROM per_absence_attendances paa
197 WHERE paa.absence_attendance_id = l_absence_attendance_id
198 AND paa.date_start > ADD_MONTHS(p_disability_start_date,-6);
199 --
200 l_Days NUMBER;
201 l_Start_Date DATE;
202 l_End_Date DATE;
203 l_Linked_Absence per_absence_attendances.abs_information1%TYPE;
204 l_absence_attendance_id per_absence_attendances.absence_attendance_id%TYPE;
205 --
206 BEGIN
207 --
208 l_Days := 0;
209 l_absence_attendance_id := p_absence_attendance_id;
210 --
211 WHILE (l_absence_attendance_id IS NOT NULL) LOOP
212 OPEN csr_abs_details(l_absence_attendance_id, p_disability_start_date);
213 FETCH csr_abs_details INTO l_Start_Date, l_End_Date, l_Linked_Absence;
214 CLOSE csr_abs_details;
215 --
216 IF l_absence_attendance_id = to_number(l_Linked_Absence) OR l_Start_Date IS NULL THEN
217 EXIT;
218 ELSIF l_End_Date IS NULL THEN
219 l_End_Date := p_disability_start_date - 1;
220 END IF;
221 --
222 l_Days := l_Days + (l_End_Date - l_Start_Date) + 1;
223 l_absence_attendance_id := to_number(l_Linked_Absence);
224 END LOOP;
225 --
226 RETURN l_Days;
227 --
228 END get_linked_absence_details;
229 --
230 --------------------------------------------------------------------------------
231 -- GET_NO_CHILDREN
232 --------------------------------------------------------------------------------
233 --
234 FUNCTION get_no_children(passignment_id IN NUMBER
235 ,pbusiness_gr_id IN NUMBER
236 ,peffective_date IN DATE)RETURN NUMBER IS
237 --
238 CURSOR c_contact_info IS
239 SELECT COUNT(pcr.contact_type)
240 FROM per_contact_relationships pcr
241 ,per_all_assignments_f paaf
242 WHERE paaf.assignment_id = passignment_id
243 AND pcr.person_id = paaf.person_id
244 AND pcr.rltd_per_rsds_w_dsgntr_flag = 'Y'
245 AND pcr.cont_information_category = 'ES'
246 AND (pcr.cont_information1 = 'Y'
247 AND pcr.contact_type IN ('C','A'))
248 AND peffective_date BETWEEN paaf.effective_start_date
249 AND paaf.effective_end_date
250 AND peffective_date BETWEEN nvl(pcr.date_start,START_OF_TIME)
251 AND nvl(pcr.date_end,END_OF_TIME);
252 --
253 l_Children_no NUMBER;
254 --
255 BEGIN
256 --
257 l_Children_no := 0;
258 --
259 OPEN c_contact_info;
260 FETCH c_contact_info INTO l_Children_no;
261 CLOSE c_contact_info;
262 --
263 RETURN l_Children_no;
264 --
265 END get_no_children;
266 --
267 --------------------------------------------------------------------------------
268 -- GET_BENEFIT_SLABS
269 --------------------------------------------------------------------------------
270 --
271 FUNCTION get_benefit_slabs(p_assignment_id IN NUMBER
272 ,p_business_group_id IN NUMBER
273 ,p_absence_attendance_id IN NUMBER
274 ,p_disability_start_date IN DATE
275 ,p_Start_Date IN DATE
276 ,p_End_Date IN DATE
277 ,p_Work_Pattern IN VARCHAR2
278 ,p_Slab_1_high IN NUMBER
279 ,p_Slab_2_high IN NUMBER
280 ,p_Slab_SSA_high IN NUMBER
281 ,p_Days_Passed_By IN NUMBER
282 ,p_Disability_in_current IN VARCHAR2
283 ,p_Link_Days OUT NOCOPY NUMBER
284 ,p_Withheld_Days OUT NOCOPY NUMBER
285 ,p_Lower_Days OUT NOCOPY NUMBER
286 ,p_Higher_Days OUT NOCOPY NUMBER
287 ,p_Lower_BR_Days OUT NOCOPY NUMBER
288 ,p_Higher_BR_Days OUT NOCOPY NUMBER ) RETURN NUMBER IS
289 --
290 Link_Days NUMBER := 0 ;
291 l_Link_Days NUMBER;
292 Temp NUMBER;
293 l_Start_Date DATE;
294 l_End_Date DATE;
295 l_working_hrs NUMBER;
296 p_High_Low_Days NUMBER;
297 --
298 BEGIN
299 --
300 p_Link_Days := 0;
301 p_Higher_Days := 0;
302 p_Lower_Days := 0;
303 p_Withheld_Days := 0;
304 p_Lower_BR_Days := 0;
305 p_Higher_BR_Days := 0;
306 --
307 IF p_absence_attendance_id <> -1 THEN
308 Link_Days := pay_es_calc_ss_earnings.get_linked_absence_details(p_absence_attendance_id
309 ,p_disability_start_date);
310 END IF;
311 Link_Days := Link_Days + p_Days_Passed_By;
312 --
313 l_Start_Date := p_Start_Date;
314 --
315 IF Link_Days < p_Slab_1_high THEN
316 --
317 IF p_End_Date > p_Start_Date + p_Slab_1_high - Link_Days - 1 THEN
318 l_End_Date := p_Start_Date + p_Slab_1_high - Link_Days - 1;
319 ELSE
320 l_End_Date := p_End_Date;
321 END IF;
322 --
323 IF p_Work_Pattern = 'Y' THEN
324 Temp := pay_es_ss_calculation.get_working_time(p_assignment_id
325 ,p_business_group_id
326 ,l_Start_Date
327 ,l_End_Date
328 ,p_Withheld_Days
329 ,l_working_hrs);
330 ELSIF p_Work_Pattern = 'N' THEN
331 p_Withheld_Days := l_End_Date - l_Start_Date + 1;
332 END IF;
333 l_Start_Date := l_End_Date + 1;
334 --
335 END IF;
336 --
337 IF p_End_Date >= p_Start_Date + p_Slab_1_high - Link_Days -1 AND Link_Days < p_Slab_2_high THEN
338 --
339 IF p_End_Date > p_Start_Date + p_Slab_2_high - Link_Days -1 THEN
340 l_End_Date := p_Start_Date + p_Slab_2_high - Link_Days - 1;
341 ELSE
342 l_End_Date := p_End_Date;
343 END IF;
344 --
345 IF l_Start_Date <= l_End_Date THEN
346 --
347 IF p_Work_Pattern = 'Y' THEN
348 Temp := pay_es_ss_calculation.get_working_time(p_assignment_id
349 ,p_business_group_id
350 ,l_Start_Date
351 ,l_End_Date
352 ,p_Lower_Days
353 ,l_working_hrs);
354 ELSIF p_Work_Pattern = 'N' THEN
355 p_Lower_Days := l_End_Date - l_Start_Date + 1;
356 END IF;
357 --
358 l_Start_Date := l_End_Date + 1;
359 END IF;
360 --
361 END IF;
362 --
363 IF p_End_Date >= p_Start_Date + p_Slab_2_high - Link_Days -1 OR p_Slab_2_high = -1 THEN
364 l_End_Date := p_End_Date;
365 --
366 IF p_Work_Pattern = 'Y' THEN
367 Temp := pay_es_ss_calculation.get_working_time(p_assignment_id
368 ,p_business_group_id
369 ,l_Start_Date
370 ,l_End_Date
371 ,p_Higher_Days
372 ,l_working_hrs);
373 ELSIF p_Work_Pattern = 'N' THEN
374 p_Higher_Days := l_End_Date - l_Start_Date + 1;
375 END IF;
376 --
377 END IF;
378 --
379 IF Link_Days IS NOT NULL THEN
380 p_Link_Days := Link_Days;
381 END IF;
382 --
383 -- BENEFIT RECLAIM CALC ----------------------------------------------------
384 --
385 l_Start_Date := p_Start_Date;
386 l_Link_Days := Link_Days;
387 --
388 IF Link_Days + p_End_Date - p_Start_Date + 1 >= p_Slab_SSA_high THEN
389 --
390 IF l_Link_Days < p_Slab_SSA_high THEN
391 l_Start_Date := p_Start_Date + p_Slab_SSA_high - l_Link_Days - 1;
392 l_Link_Days := p_Slab_SSA_high - 1;
393 END IF;
394 --
395 WHILE l_Start_Date <= p_End_Date LOOP
396 --
397 IF p_Work_Pattern = 'Y' THEN
398 p_High_Low_Days := 0;
399 Temp := pay_es_ss_calculation.get_working_time(p_assignment_id
400 ,p_business_group_id
401 ,l_Start_Date
402 ,l_Start_Date
403 ,p_High_Low_Days
404 ,l_working_hrs);
405 ELSIF p_Work_Pattern = 'N' THEN
406 p_High_Low_Days := 1;
407 END IF;
408 --
409 IF l_Link_Days < p_Slab_2_high AND p_Slab_2_high <> -1 THEN
410 p_Lower_BR_Days := p_Lower_BR_Days + p_High_Low_Days;
411 ELSE
412 p_Higher_BR_Days := p_Higher_BR_Days + p_High_Low_Days;
413 END IF;
414 --
415 l_Start_Date := l_Start_Date + 1;
416 l_Link_Days := l_Link_Days + 1;
417 --
418 END LOOP;
419 --
420 END IF;
421 --
422 return 0;
423 --
424 END get_benefit_slabs;
425 --
426 --------------------------------------------------------------------------------
427 -- GET_CONTRACT_WORKING_HOURS
428 --------------------------------------------------------------------------------
429 --
430 FUNCTION get_contract_working_hours(p_assignment_id IN NUMBER
431 ,p_business_group_id IN NUMBER
432 ,p_Start_Date IN DATE) RETURN NUMBER
433 IS
434 --
435 l_working_hrs NUMBER := 0;
436 l_End_Date DATE;
437 l_Days NUMBER;
438 Temp NUMBER;
439 --
440 BEGIN
441 l_End_Date := to_date((to_char(p_Start_Date,'dd-mm-')||
442 to_char(to_number(to_char(p_Start_Date,'YYYY'))-1)),'dd-mm-yyyy')-1;
443 Temp := pay_es_ss_calculation.get_working_time(p_assignment_id
444 ,p_business_group_id
445 ,p_Start_Date
446 ,l_End_Date
447 ,l_Days
448 ,l_working_hrs);
449 return l_working_hrs;
450 END get_contract_working_hours;
451 --
452 --------------------------------------------------------------------------------
453 -- MATERNITY_VALIDATIONS
454 --------------------------------------------------------------------------------
455 FUNCTION Maternity_Validations(p_absence_attendance_id IN NUMBER
456 ,p_benefit_days OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
457 --
458 CURSOR csr_abs_details(p_absence_attendance_id NUMBER) IS
459 SELECT NVL(PAA1.date_end,to_date('31-12-4712','dd-mm-yyyy'))-PAA1.date_start+1
460 ,PAAT1.absence_category Reason_Category_prev
461 ,PAAT2.absence_category Reason_Category
462 FROM per_absence_attendances PAA1
463 ,per_absence_attendances PAA2
464 ,per_absence_attendance_types PAAT1
465 ,per_absence_attendance_types PAAT2
466 WHERE PAA2.absence_attendance_id = p_absence_attendance_id
467 AND PAA2.date_start = PAA1.date_end + 1
468 AND PAA1.person_id = PAA2.person_id
469 AND PAA1.absence_attendance_type_id = PAAT1.absence_attendance_type_id
470 AND PAA2.absence_attendance_type_id = PAAT2.absence_attendance_type_id;
471 --
472 l_category per_absence_attendance_types.absence_category%TYPE;
473 l_category_prev per_absence_attendance_types.absence_category%TYPE;
474 --
475 BEGIN
476 --
477 l_category := 'x';
478 l_category_prev := 'x';
479 --
480 BEGIN
481 OPEN csr_abs_details(p_absence_attendance_id);
482 FETCH csr_abs_details into p_benefit_days, l_category_prev ,l_category ;
483 CLOSE csr_abs_details;
484 --
485 EXCEPTION
486 WHEN no_data_found THEN
487 RETURN 'N';
488 END;
489 --
490 IF (l_category_prev = 'M' AND l_category = 'PTM') OR
491 (l_category_prev = 'PAR' AND l_category = 'M') THEN
492 RETURN 'Y';
493 ELSE
494 RETURN 'N';
495 END IF;
496 --
497 END Maternity_Validations;
498 --
499 --------------------------------------------------------------------------------
500 -- GET_WC_ND_SD_PU_INFO
501 --------------------------------------------------------------------------------
502 --
503 FUNCTION get_wc_nd_sd_pu_info(p_work_center IN NUMBER
504 ,p_date_between IN DATE
505 ,p_PU IN VARCHAR2
506 ,p_end_date OUT NOCOPY DATE
507 ,p_part_unemp_perc OUT NOCOPY NUMBER
508 ,p_start_date OUT NOCOPY DATE
509 ,p_Cal_method OUT NOCOPY VARCHAR2
510 ,p_Rate_formula OUT NOCOPY VARCHAR2
511 ,p_Duration_Formula OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
512 --
513 CURSOR csr_wc_eit_nd_sd IS
514 SELECT nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31-12-4712','dd-mm-yyyy')) end_date
515 ,0 PU_perc
516 ,fnd_date.canonical_to_date(hoi.org_information1) start_date
517 FROM hr_organization_information hoi
518 WHERE hoi.organization_id = p_work_center
519 AND hoi.org_information_context IN ('ES_WC_NATURAL_DISASTER','ES_WC_SHUTDOWN')
520 AND p_date_between BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
521 AND nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31-12-4712','dd-mm-yyyy'));
522 --
523 CURSOR csr_wc_eit_pu IS
524 SELECT nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31-12-4712','dd-mm-yyyy')) end_date
525 ,fnd_number.canonical_to_number(hoi.org_information3) PU_perc
526 ,fnd_date.canonical_to_date(hoi.org_information1) start_date
527 ,hoi.org_information5 Cal_metod
528 ,hoi.org_information6 Rate_formula
529 ,hoi.org_information7 Duration_Formula
530 FROM hr_organization_information hoi
531 WHERE hoi.organization_id = p_work_center
532 AND hoi.org_information_context IN ('ES_WC_PARTIAL_UNEMPLOYMENT')
533 AND p_date_between BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
534 AND nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31-12-4712','dd-mm-yyyy'));
535 --
536 BEGIN
537 --
538 IF p_PU = 'Y' THEN
539 OPEN csr_wc_eit_pu;
540 FETCH csr_wc_eit_pu into p_end_date, p_part_unemp_perc, p_start_date,p_Cal_method ,p_Rate_formula,p_Duration_Formula ;
541 IF csr_wc_eit_pu%NOTFOUND THEN
542 CLOSE csr_wc_eit_pu;
543 RETURN 'N';
544 END IF;
545 CLOSE csr_wc_eit_pu;
546 ELSIF p_PU = 'N' THEN
547 OPEN csr_wc_eit_nd_sd;
548 FETCH csr_wc_eit_nd_sd into p_end_date, p_part_unemp_perc, p_start_date;
549 IF csr_wc_eit_nd_sd%NOTFOUND THEN
550 CLOSE csr_wc_eit_nd_sd;
551 RETURN 'N';
552 END IF;
553 CLOSE csr_wc_eit_nd_sd;
554 END IF;
555 --
556 --
557 RETURN 'Y';
558 --
559 END get_wc_nd_sd_pu_info;
560 --
561 --------------------------------------------------------------------------------
562 -- GET_WC_PU_INFO
563 --------------------------------------------------------------------------------
564 --
565 FUNCTION get_wc_pu_info(p_work_center IN NUMBER
566 ,p_period_start_date IN DATE
567 ,p_period_end_date IN DATE
568 ,p_end_date OUT NOCOPY DATE
569 ,p_part_unemp_perc OUT NOCOPY NUMBER
570 ,p_start_date OUT NOCOPY DATE
571 ,p_Cal_method OUT NOCOPY VARCHAR2
572 ,p_Rate_formula OUT NOCOPY VARCHAR2
573 ,p_Duration_Formula OUT NOCOPY VARCHAR2) RETURN VARCHAR2
574 IS
575 --
576 CURSOR csr_wc_pu_details IS
577 SELECT nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31-12-4712','dd-mm-yyyy')) end_date
578 ,fnd_number.canonical_to_number(hoi.org_information3) PU_perc
579 ,fnd_date.canonical_to_date(hoi.org_information1) start_date
580 ,hoi.org_information5 Cal_metod
581 ,hoi.org_information6 Rate_formula
582 ,hoi.org_information7 Duration_Formula
583 FROM hr_organization_information hoi
584 WHERE hoi.organization_id = p_work_center
585 AND hoi.org_information_context IN ('ES_WC_PARTIAL_UNEMPLOYMENT')
586 AND fnd_date.canonical_to_date(hoi.org_information1) BETWEEN p_period_start_date
587 AND p_period_end_date;
588 --
589 BEGIN
590 --
591 OPEN csr_wc_pu_details;
592 FETCH csr_wc_pu_details into p_end_date, p_part_unemp_perc, p_start_date, p_Cal_method ,p_Rate_formula,p_Duration_Formula ;
593 IF csr_wc_pu_details%NOTFOUND THEN
594 CLOSE csr_wc_pu_details;
595 RETURN 'N';
596 END IF;
597 CLOSE csr_wc_pu_details;
598 --
599 Return 'Y';
600 --
601 END get_wc_pu_info;
602 --
603 --------------------------------------------------------------------------------
604 -- GET_BU_INFO
605 --------------------------------------------------------------------------------
606 --
607 FUNCTION get_bu_info(p_assignment_id IN NUMBER
608 ,p_business_gr_id IN NUMBER
609 ,p_date_earned IN DATE
610 ,p_abs_cat IN VARCHAR2
611 ,p_Total_Days IN NUMBER
612 ,p_bu_calc_method_e IN VARCHAR2
613 ,p_bu_daily_rate_e IN VARCHAR2
614 ,p_bu_duration_e IN VARCHAR2
615 ,p_start_date IN DATE
616 ,p_end_date IN DATE
617 ,p_Daily_Value_Base IN NUMBER
618 ,p_Link_Duration_Days IN NUMBER
619 ,p_Days_Passed_By OUT NOCOPY NUMBER
620 ,p_Benefit_Uplift OUT NOCOPY NUMBER
621 ,p_Gross_Pay_Per_Days OUT NOCOPY NUMBER
622 ,p_rate1 OUT NOCOPY NUMBER
623 ,p_value1 OUT NOCOPY NUMBER
624 ,p_rate2 OUT NOCOPY NUMBER
625 ,p_value2 OUT NOCOPY NUMBER
626 ,p_rate3 OUT NOCOPY NUMBER
627 ,p_value3 OUT NOCOPY NUMBER
628 ,p_rate4 OUT NOCOPY NUMBER
629 ,p_value4 OUT NOCOPY NUMBER
630 ,p_rate5 OUT NOCOPY NUMBER
631 ,p_value5 OUT NOCOPY NUMBER
632 ,p_rate6 OUT NOCOPY NUMBER
633 ,p_value6 OUT NOCOPY NUMBER
634 ,p_rate7 OUT NOCOPY NUMBER
635 ,p_value7 OUT NOCOPY NUMBER
636 ,p_rate8 OUT NOCOPY NUMBER
637 ,p_value8 OUT NOCOPY NUMBER
638 ,p_rate9 OUT NOCOPY NUMBER
639 ,p_value9 OUT NOCOPY NUMBER
640 ,p_rate10 OUT NOCOPY NUMBER
641 ,p_value10 OUT NOCOPY NUMBER
642 ,p_work_center IN NUMBER
643 ,p_pattern IN VARCHAR2
644 ,p_percentage IN NUMBER) RETURN VARCHAR2 IS
645 --
646 CURSOR csr_legal_employer_info(l_legal_emp_id IN NUMBER) IS
647 SELECT org_information2 l_bu_calc_method
648 ,org_information3 l_bu_daily_rate_ff
649 ,org_information4 l_bu_duration_ff
650 ,GREATEST(fnd_date.canonical_to_date(org_information5), p_start_date) l_bu_start_date
651 ,LEAST(nvl(fnd_date.canonical_to_date(org_information6),to_date('31-12-4712','dd-mm-yyyy')),p_end_date) l_bu_end_date
652 FROM hr_organization_information
653 WHERE organization_id = l_legal_emp_id
654 AND org_information_context = 'ES_BENEFIT_UPLIFT'
655 AND org_information1 = p_abs_cat
656 AND ((p_start_date BETWEEN fnd_date.canonical_to_date(ORG_INFORMATION5)
657 AND NVL(fnd_date.canonical_to_date(ORG_INFORMATION6),to_date('31-12-4712','DD-MM-YYYY')))
658 OR (fnd_date.canonical_to_date(ORG_INFORMATION5) BETWEEN p_start_date
659 AND p_end_date))
660 ORDER BY org_information5 ;
661 --
662 CURSOR csr_get_le_details (p_wc_organization_id NUMBER) IS
663 SELECT hoi.organization_id le_id
664 FROM hr_organization_information hoi
665 WHERE hoi.org_information1 = p_wc_organization_id
666 AND hoi.org_information_context = 'ES_WORK_CENTER_REF';
667
668
669 l_Benefit_Days NUMBER;
670 l_BU_Calculation hr_organization_information.org_information2%TYPE;
671 l_BU_Rate_Formula hr_organization_information.org_information2%TYPE;
672 l_BU_Duration_Formula hr_organization_information.org_information2%TYPE;
673 l_Day_Amount NUMBER;
674 l_Days_in_Value1 NUMBER := 0;
675 l_Days_in_Value2 NUMBER := 0;
676 l_Days_in_Value3 NUMBER := 0;
677 l_Days_in_Value4 NUMBER := 0;
678 l_Days_in_Value5 NUMBER := 0;
679 l_Days_in_Value6 NUMBER := 0;
680 l_Days_in_Value7 NUMBER := 0;
681 l_Days_in_Value8 NUMBER := 0;
682 l_Days_in_Value9 NUMBER := 0;
683 l_Days_in_Value10 NUMBER := 0;
684 L_BENEFIT_UPLIFT NUMBER := 0;
685 l_Benefit_Days_w NUMBER := 0;
686 l_temp VARCHAR2(10);
687 l_legal_emp_id NUMBER;
688 temp NUMBER := 0;
689 l_working_hrs NUMBER := 0;
690 bu_start_date DATE;
691 bu_end_date DATE;
692 --
693 BEGIN
694 --
695 l_Benefit_Days := 0;
696 l_Day_Amount := 0;
697 p_Gross_Pay_Per_Days := 0;
698 p_Benefit_Uplift := 0;
699 l_temp := 'x';
700 --
701 p_rate1 := 0;
702 p_value1 := 0;
703 p_rate2 := 0;
704 p_value2 := 0;
705 p_rate3 := 0;
706 p_value3 := 0;
707 p_rate4 := 0;
708 p_value4 := 0;
709 p_rate5 := 0;
710 p_value5 := 0;
711 p_rate6 := 0;
712 p_value6 := 0;
713 p_rate7 := 0;
714 p_value7 := 0;
715 p_rate8 := 0;
716 p_value8 := 0;
717 p_rate9 := 0;
718 p_value9 := 0;
719 p_rate10 := 0;
720 p_value10 := 0;
721 p_Days_Passed_By := 0;
722
723 --
724 OPEN csr_get_le_details(p_work_center);
725 FETCH csr_get_le_details INTO l_legal_emp_id;
726 CLOSE csr_get_le_details;
727 --
728 FOR recd_le_info IN csr_legal_employer_info(l_legal_emp_id) LOOP
729 --
730 l_BU_Calculation := p_bu_calc_method_e ;
731 l_BU_Rate_Formula := p_bu_daily_rate_e ;
732 l_BU_Duration_Formula := p_bu_duration_e ;
733 IF l_BU_Calculation IS NULL THEN
734 --
735 IF l_BU_Duration_Formula IS NULL THEN
736 --
737 l_BU_Calculation := recd_le_info.l_bu_calc_method ;
738 l_BU_Rate_Formula := recd_le_info.l_bu_daily_rate_ff ;
739 l_BU_Duration_Formula := recd_le_info.l_bu_duration_ff ;
740 ELSE
741 --
742 l_BU_Calculation := recd_le_info.l_bu_calc_method ;
743 l_BU_Rate_Formula := recd_le_info.l_bu_daily_rate_ff ;
744 END IF;
745 --
746 ELSIF l_BU_Duration_Formula IS NULL THEN
747 --
748 l_BU_Duration_Formula := recd_le_info.l_bu_duration_ff ;
749 END IF;
750 --
751 --
752 IF l_BU_Calculation = 'GROSS_PAY' AND l_BU_Rate_Formula IS NOT NULL AND l_BU_Duration_Formula IS NOT NULL THEN
753 --
754
755 l_Day_Amount := pay_es_benefit_uplift_calc.get_gross_per_day(p_assignment_id
756 ,p_business_gr_id
757 ,p_date_earned
758 ,l_BU_Rate_Formula); -- Gross_Pay_Per_Days
759 --
760 IF l_Day_Amount IS NULL OR l_Day_Amount < 0 THEN
761 l_Day_Amount := 0 ;
762 END IF;
763 --
764 p_Gross_Pay_Per_Days := l_Day_Amount ;
765 l_Day_Amount := l_Day_Amount * p_percentage / 100;
766 --
767 ELSIF l_BU_Calculation = 'STATUTORY_EARNINGS' AND l_BU_Duration_Formula IS NOT NULL THEN
768 --
769 l_Day_Amount := p_Daily_Value_Base ;
770 ELSE
771 --
772 l_Day_Amount := 0 ;
773 --
774 END IF;
775
776 p_value1:= 0;
777 p_rate1 := 0;
778 p_value2:= 0;
779 p_rate2 := 0;
780 p_value3:= 0;
781 p_rate3:= 0;
782 p_value4:= 0;
783 p_rate4:= 0;
784 p_value5:= 0;
785 p_rate5:= 0;
786 p_value6:= 0;
787 p_rate6:= 0;
788 p_value7:= 0;
789 p_rate7:= 0;
790 p_value8:= 0;
791 p_rate8:= 0;
792 p_value9:= 0;
793 p_rate9:= 0;
794 p_value10:= 0;
795 p_rate10:= 0;
796
797 l_temp := pay_es_benefit_uplift_calc.get_duration(p_assignment_id,p_business_gr_id,p_date_earned,l_BU_Duration_Formula,
798 p_rate1, p_value1 ,p_rate2, p_value2, p_rate3, p_value3,p_rate4, p_value4, p_rate5, p_value5,p_rate6, p_value6
799 ,p_rate7, p_value7, p_rate8, p_value8, p_rate9, p_value9, p_rate10, p_value10);
800 p_Days_Passed_By := p_Link_Duration_Days ; --Benefit_Days initialize to 0 at top
801 l_Benefit_Days := recd_le_info.l_bu_end_date - recd_le_info.l_bu_start_date + 1 ; --Benefit_Days should never be initialized after this
802 IF p_pattern = 'P' THEN
803 temp := pay_es_ss_calculation.get_working_time(p_assignment_id
804 ,p_business_gr_id
805 ,recd_le_info.l_bu_start_date
806 ,recd_le_info.l_bu_end_date
807 ,l_Benefit_Days_w
808 ,l_working_hrs);
809 ELSE
810 l_Benefit_Days_w := l_Benefit_Days ;
811 END IF;
812 --
813 bu_start_date := recd_le_info.l_bu_start_date;
814 bu_end_date := recd_le_info.l_bu_end_date;
815 --
816 l_Days_in_Value1 := p_value1 ;
817 l_Days_in_Value2 := p_value2 ;
818 l_Days_in_Value3 := p_value3 ;
819 l_Days_in_Value4 := p_value4 ;
820 l_Days_in_Value5 := p_value5 ;
821 l_Days_in_Value6 := p_value6 ;
822 l_Days_in_Value7 := p_value7 ;
823 l_Days_in_Value8 := p_value8 ;
824 l_Days_in_Value9 := p_value9 ;
825 l_Days_in_Value10 := p_value10 ;
826 --
827 IF p_Days_Passed_By < p_value1 THEN
828 l_Days_in_Value1 := p_value1 - p_Days_Passed_By ;
829 ELSIF p_Days_Passed_By < p_value1 + p_value2 THEN
830 l_Days_in_Value1 := 0 ;
831 l_Days_in_Value2 := p_value1 + p_value2 - p_Days_Passed_By ;
832 ELSIF p_Days_Passed_By < p_value1 + p_value2 + p_value3 THEN
833 l_Days_in_Value1 := 0 ;
834 l_Days_in_Value2 := 0 ;
835 l_Days_in_Value3 := p_value1 + p_value2 + p_value3 - p_Days_Passed_By ;
836 ELSIF p_Days_Passed_By < p_value1 + p_value2 + p_value3 + p_value4 THEN
837 l_Days_in_Value1 := 0 ;
838 l_Days_in_Value2 := 0 ;
839 l_Days_in_Value3 := 0 ;
840 l_Days_in_Value4 := p_value1 + p_value2 + p_value3 + p_value4 - p_Days_Passed_By ;
841 ELSIF p_Days_Passed_By < p_value1 + p_value2 + p_value3 + p_value4 + p_value5 THEN
842 l_Days_in_Value1 := 0 ;
843 l_Days_in_Value2 := 0 ;
844 l_Days_in_Value3 := 0 ;
845 l_Days_in_Value4 := 0 ;
846 l_Days_in_Value5 := p_value1 + p_value2 + p_value3 + p_value4 + p_value5 - p_Days_Passed_By ;
847 ELSIF p_Days_Passed_By < p_value1 + p_value2 + p_value3 + p_value4 + p_value5 + p_value6 THEN
848 l_Days_in_Value1 := 0 ;
849 l_Days_in_Value2 := 0 ;
850 l_Days_in_Value3 := 0 ;
851 l_Days_in_Value4 := 0 ;
852 l_Days_in_Value5 := 0 ;
853 l_Days_in_Value6 := p_value1 + p_value2 + p_value3 + p_value4 + p_value5 + p_value6 - p_Days_Passed_By ;
854 ELSIF p_Days_Passed_By < p_value1 + p_value2 + p_value3 + p_value4 + p_value5 + p_value6 + p_value7 THEN
855 l_Days_in_Value1 := 0 ;
856 l_Days_in_Value2 := 0 ;
857 l_Days_in_Value3 := 0 ;
858 l_Days_in_Value4 := 0 ;
859 l_Days_in_Value5 := 0 ;
860 l_Days_in_Value6 := 0 ;
861 l_Days_in_Value7 := p_value1 + p_value2 + p_value3 + p_value4 + p_value5 + p_value6 + p_value7 - p_Days_Passed_By ;
862 ELSIF p_Days_Passed_By < p_value1 + p_value2 + p_value3 + p_value4 + p_value5 + p_value6 + p_value7 + p_value8 THEN
863 l_Days_in_Value1 := 0 ;
864 l_Days_in_Value2 := 0 ;
865 l_Days_in_Value3 := 0 ;
866 l_Days_in_Value4 := 0 ;
867 l_Days_in_Value5 := 0 ;
868 l_Days_in_Value6 := 0 ;
869 l_Days_in_Value7 := 0 ;
870 l_Days_in_Value8 := p_value1 + p_value2 + p_value3 + p_value4 + p_value5 + p_value6 + p_value7 + p_value8 - p_Days_Passed_By ;
871 ELSIF p_Days_Passed_By < p_value1 + p_value2 + p_value3 + p_value4 + p_value5 + p_value6 + p_value7 + p_value8 + p_value9 THEN
872 l_Days_in_Value1 := 0 ;
873 l_Days_in_Value2 := 0 ;
874 l_Days_in_Value3 := 0 ;
875 l_Days_in_Value4 := 0 ;
876 l_Days_in_Value5 := 0 ;
877 l_Days_in_Value6 := 0 ;
878 l_Days_in_Value7 := 0 ;
879 l_Days_in_Value8 := 0 ;
880 l_Days_in_Value9 := p_value1 + p_value2 + p_value3 + p_value4 + p_value5 + p_value6 + p_value7 + p_value8 + p_value9 - p_Days_Passed_By ;
881 ELSE
882 l_Days_in_Value1 := 0 ;
883 l_Days_in_Value2 := 0 ;
884 l_Days_in_Value3 := 0 ;
885 l_Days_in_Value4 := 0 ;
886 l_Days_in_Value5 := 0 ;
887 l_Days_in_Value6 := 0 ;
888 l_Days_in_Value7 := 0 ;
889 l_Days_in_Value8 := 0 ;
890 l_Days_in_Value9 := 0 ;
891 l_Days_in_Value10 := p_value1 + p_value2 + p_value3 + p_value4 + p_value5 + p_value6 + p_value7 + p_value8 + p_value9 + p_value10 - p_Days_Passed_By ;
892 END IF;
893 --
894 IF p_pattern = 'P' THEN
895 IF l_Days_in_Value1 > 0 THEN
896 bu_end_date := bu_start_date + l_Days_in_Value1 - 1;
897 temp := pay_es_ss_calculation.get_working_time( p_assignment_id
898 ,p_business_gr_id
899 ,bu_start_date
900 ,bu_end_date
901 ,l_Days_in_Value1
902 ,l_working_hrs);
903 bu_start_date := bu_end_date + 1;
904 END IF;
905 IF l_Days_in_Value2 > 0 THEN
906 bu_end_date := bu_start_date + l_Days_in_Value2 - 1;
907 temp := pay_es_ss_calculation.get_working_time( p_assignment_id
908 ,p_business_gr_id
909 ,bu_start_date
910 ,bu_end_date
911 ,l_Days_in_Value2
912 ,l_working_hrs);
913 bu_start_date := bu_end_date + 1;
914 END IF;
915 IF l_Days_in_Value3 > 0 THEN
916 bu_end_date := bu_start_date + l_Days_in_Value3 - 1;
917 temp := pay_es_ss_calculation.get_working_time( p_assignment_id
918 ,p_business_gr_id
919 ,bu_start_date
920 ,bu_end_date
921 ,l_Days_in_Value3
922 ,l_working_hrs);
923 bu_start_date := bu_end_date + 1;
924 END IF;
925 IF l_Days_in_Value4 > 0 THEN
926 bu_end_date := bu_start_date + l_Days_in_Value4 - 1;
927 temp := pay_es_ss_calculation.get_working_time( p_assignment_id
928 ,p_business_gr_id
929 ,bu_start_date
930 ,bu_end_date
931 ,l_Days_in_Value4
932 ,l_working_hrs);
933 bu_start_date := bu_end_date + 1;
934 END IF;
935 IF l_Days_in_Value5 > 0 THEN
936 bu_end_date := bu_start_date + l_Days_in_Value5 - 1;
937 temp := pay_es_ss_calculation.get_working_time( p_assignment_id
938 ,p_business_gr_id
939 ,bu_start_date
940 ,bu_end_date
941 ,l_Days_in_Value5
942 ,l_working_hrs);
943 bu_start_date := bu_end_date + 1;
944 END IF;
945 IF l_Days_in_Value6 > 0 THEN
946 bu_end_date := bu_start_date + l_Days_in_Value6 - 1;
947 temp := pay_es_ss_calculation.get_working_time( p_assignment_id
948 ,p_business_gr_id
949 ,bu_start_date
950 ,bu_end_date
951 ,l_Days_in_Value6
952 ,l_working_hrs);
953 bu_start_date := bu_end_date + 1;
954 END IF;
955 IF l_Days_in_Value7 > 0 THEN
956 bu_end_date := bu_start_date + l_Days_in_Value7 - 1;
957 temp := pay_es_ss_calculation.get_working_time( p_assignment_id
958 ,p_business_gr_id
959 ,bu_start_date
960 ,bu_end_date
961 ,l_Days_in_Value7
962 ,l_working_hrs);
963 bu_start_date := bu_end_date + 1;
964 END IF;
965 IF l_Days_in_Value8 > 0 THEN
966 bu_end_date := bu_start_date + l_Days_in_Value8 - 1;
967 temp := pay_es_ss_calculation.get_working_time( p_assignment_id
968 ,p_business_gr_id
969 ,bu_start_date
970 ,bu_end_date
971 ,l_Days_in_Value8
972 ,l_working_hrs);
973 bu_start_date := bu_end_date + 1;
974 END IF;
975 IF l_Days_in_Value9 > 0 THEN
976 bu_end_date := bu_start_date + l_Days_in_Value9 - 1;
977 temp := pay_es_ss_calculation.get_working_time( p_assignment_id
978 ,p_business_gr_id
979 ,bu_start_date
980 ,bu_end_date
981 ,l_Days_in_Value9
982 ,l_working_hrs);
983 bu_start_date := bu_end_date + 1;
984 END IF;
985 IF l_Days_in_Value10 > 0 THEN
986 bu_end_date := bu_start_date + l_Days_in_Value10 - 1;
987 temp := pay_es_ss_calculation.get_working_time( p_assignment_id
988 ,p_business_gr_id
989 ,bu_start_date
990 ,bu_end_date
991 ,l_Days_in_Value10
992 ,l_working_hrs);
993 bu_start_date := bu_end_date + 1;
994 END IF;
995 END IF;
996 --
997 IF l_Benefit_Days_w < l_Days_in_Value1 THEN
998 l_Benefit_Uplift := (l_Day_Amount * l_Benefit_Days_w * p_rate1 / 100) ;
999 ELSIF l_Benefit_Days_w < l_Days_in_Value1 + l_Days_in_Value2 THEN
1000 l_Benefit_Uplift := (l_Day_Amount * (l_Days_in_Value1 * p_rate1 + (l_Benefit_Days_w - l_Days_in_Value1) * p_rate2) / 100) ;
1001 ELSIF l_Benefit_Days_w < l_Days_in_Value1 + l_Days_in_Value2 + l_Days_in_Value3 THEN
1002 l_Benefit_Uplift := (l_Day_Amount *
1003 (l_Days_in_Value1 * p_rate1 + l_Days_in_Value2 * p_rate2 +
1004 (l_Benefit_Days_w - l_Days_in_Value1 - l_Days_in_Value2) * p_rate3) / 100) ;
1005 ELSIF l_Benefit_Days_w < l_Days_in_Value1 + l_Days_in_Value2 + l_Days_in_Value3 + l_Days_in_Value4 THEN
1006 l_Benefit_Uplift := (l_Day_Amount *
1007 (l_Days_in_Value1 * p_rate1 + l_Days_in_Value2 * p_rate2 + l_Days_in_Value3 * p_rate3 +
1008 (l_Benefit_Days_w - l_Days_in_Value1 - l_Days_in_Value2 - l_Days_in_Value3) * p_rate4) / 100) ;
1009 ELSIF l_Benefit_Days_w < l_Days_in_Value1 + l_Days_in_Value2 + l_Days_in_Value3 + l_Days_in_Value4 + l_Days_in_Value5 THEN
1010 l_Benefit_Uplift := (l_Day_Amount *
1011 (l_Days_in_Value1 * p_rate1 + l_Days_in_Value2 * p_rate2 + l_Days_in_Value3 * p_rate3 + l_Days_in_Value4 * p_rate4 +
1012 (l_Benefit_Days_w - l_Days_in_Value1 - l_Days_in_Value2 - l_Days_in_Value3 - l_Days_in_Value4) * p_rate5) / 100) ;
1013 ELSIF l_Benefit_Days_w < l_Days_in_Value1 + l_Days_in_Value2 + l_Days_in_Value3 + l_Days_in_Value4 + l_Days_in_Value5 + l_Days_in_Value6 THEN
1014 l_Benefit_Uplift := (l_Day_Amount *
1015 (l_Days_in_Value1 * p_rate1 + l_Days_in_Value2 * p_rate2 + l_Days_in_Value3 * p_rate3 + l_Days_in_Value4 * p_rate4 +
1016 l_Days_in_Value5 * p_rate5 +
1017 (l_Benefit_Days_w - l_Days_in_Value1 - l_Days_in_Value2 - l_Days_in_Value3 - l_Days_in_Value4 - l_Days_in_Value5)
1018 * p_rate6) / 100) ;
1019 ELSIF l_Benefit_Days_w < l_Days_in_Value1 + l_Days_in_Value2 + l_Days_in_Value3 + l_Days_in_Value4 + l_Days_in_Value5 + l_Days_in_Value6 + l_Days_in_Value7 THEN
1020 l_Benefit_Uplift := (l_Day_Amount *
1021 (l_Days_in_Value1 * p_rate1 + l_Days_in_Value2 * p_rate2 + l_Days_in_Value3 * p_rate3 + l_Days_in_Value4 * p_rate4 +
1022 l_Days_in_Value5 * p_rate5 + l_Days_in_Value6 * p_rate6 +
1023 (l_Benefit_Days_w - l_Days_in_Value1 - l_Days_in_Value2 - l_Days_in_Value3 - l_Days_in_Value4 - l_Days_in_Value5 - l_Days_in_Value6 )
1024 * p_rate7) / 100) ;
1025 ELSIF l_Benefit_Days_w < l_Days_in_Value1 + l_Days_in_Value2 + l_Days_in_Value3 + l_Days_in_Value4 + l_Days_in_Value5 + l_Days_in_Value6
1026 + l_Days_in_Value7 + l_Days_in_Value8 THEN
1027 l_Benefit_Uplift := (l_Day_Amount *
1028 (l_Days_in_Value1 * p_rate1 + l_Days_in_Value2 * p_rate2 + l_Days_in_Value3 * p_rate3 + l_Days_in_Value4 * p_rate4 +
1029 l_Days_in_Value5 * p_rate5 + l_Days_in_Value6 * p_rate6 + l_Days_in_Value7 * p_rate7 +
1030 (l_Benefit_Days_w - l_Days_in_Value1 - l_Days_in_Value2 - l_Days_in_Value3 - l_Days_in_Value4 - l_Days_in_Value5 - l_Days_in_Value6 -
1031 l_Days_in_Value7 ) * p_rate8) / 100) ;
1032 ELSIF l_Benefit_Days_w < l_Days_in_Value1 + l_Days_in_Value2 + l_Days_in_Value3 + l_Days_in_Value4 + l_Days_in_Value5 + l_Days_in_Value6
1033 + l_Days_in_Value7 + l_Days_in_Value8 + l_Days_in_Value9 THEN
1034 l_Benefit_Uplift := (l_Day_Amount *
1035 (l_Days_in_Value1 * p_rate1 + l_Days_in_Value2 * p_rate2 + l_Days_in_Value3 * p_rate3 + l_Days_in_Value4 * p_rate4 +
1036 l_Days_in_Value5 * p_rate5 + l_Days_in_Value6 * p_rate6 + l_Days_in_Value7 * p_rate7 + l_Days_in_Value8 * p_rate8 +
1037 (l_Benefit_Days_w - l_Days_in_Value1 - l_Days_in_Value2 - l_Days_in_Value3 - l_Days_in_Value4 - l_Days_in_Value5 - l_Days_in_Value6 -
1038 l_Days_in_Value7 - l_Days_in_Value8 ) * p_rate9) / 100) ;
1039 ELSIF l_Benefit_Days_w < l_Days_in_Value1 + l_Days_in_Value2 + l_Days_in_Value3 + l_Days_in_Value4 + l_Days_in_Value5 + l_Days_in_Value6
1040 + l_Days_in_Value7 + l_Days_in_Value8 + l_Days_in_Value9 + l_Days_in_Value10 THEN
1041 l_Benefit_Uplift := (l_Day_Amount *
1042 (l_Days_in_Value1 * p_rate1 + l_Days_in_Value2 * p_rate2 + l_Days_in_Value3 * p_rate3 + l_Days_in_Value4 * p_rate4 +
1043 l_Days_in_Value5 * p_rate5 + l_Days_in_Value6 * p_rate6 + l_Days_in_Value7 * p_rate7 + l_Days_in_Value8 * p_rate8 +
1044 l_Days_in_Value9 * p_rate9 +
1045 (l_Benefit_Days_w - l_Days_in_Value1 - l_Days_in_Value2 - l_Days_in_Value3 - l_Days_in_Value4 - l_Days_in_Value5 - l_Days_in_Value6 -
1046 l_Days_in_Value7 - l_Days_in_Value8 - l_Days_in_Value9 ) * p_rate10) / 100) ;
1047 ELSE
1048 l_Benefit_Uplift := (l_Day_Amount *
1049 (l_Days_in_Value1 * p_rate1 + l_Days_in_Value2 * p_rate2 + l_Days_in_Value3 * p_rate3 + l_Days_in_Value4 * p_rate4 +
1050 l_Days_in_Value5 * p_rate5 + l_Days_in_Value6 * p_rate6 + l_Days_in_Value7 * p_rate7 + l_Days_in_Value8 * p_rate8 +
1051 l_Days_in_Value9 * p_rate9 + l_Days_in_Value10 * p_rate10) / 100) ;
1052 END IF;
1053 --
1054 p_Benefit_Uplift := p_Benefit_Uplift + l_Benefit_Uplift;
1055 l_BU_Calculation := p_bu_calc_method_e ;
1056 l_BU_Rate_Formula := p_bu_daily_rate_e ;
1057 l_BU_Duration_Formula := p_bu_duration_e ;
1058 --
1059
1060 END LOOP;
1061 --
1062 RETURN 'Y';
1063 --
1064 END get_bu_info;
1065 --
1066 --------------------------------------------------------------------------------
1067 -- GET_PU_CONTRIBUTION_VALUE
1068 --------------------------------------------------------------------------------
1069 --
1070 FUNCTION get_pu_contribution_value(p_assignment_id IN NUMBER
1071 ,p_assignment_action_id IN NUMBER
1072 ,p_balance_SS IN VARCHAR2
1073 ,p_database_item_SS IN VARCHAR2
1074 ,p_balance_PU IN VARCHAR2
1075 ,p_database_item_PU IN VARCHAR2
1076 ,p_PU_start_date IN DATE
1077 ,p_span_days IN NUMBER
1078 ,p_ss_days OUT NOCOPY NUMBER)RETURN NUMBER IS
1079 --
1080 l_Contri_Base_PU NUMBER;
1081 l_Contri_Base_180 NUMBER;
1082 l_def_bal_id_SS NUMBER;
1083 l_def_bal_id_PU NUMBER;
1084 l_ctr NUMBER;
1085 l_prev_date DATE;
1086 l_num NUMBER;
1087 l_amt NUMBER;
1088 --
1089 CURSOR get_prev_periods_dates (c_assignment_action_id NUMBER
1090 ,c_period_start_date DATE) IS
1091 SELECT ptp.start_date start_date
1092 ,ptp.end_date end_date
1093 ,ppa.action_type action_type
1094 ,paa2.assignment_action_id assignment_action_id
1095 FROM pay_assignment_actions paa1
1096 ,pay_assignment_actions paa2
1097 ,per_all_assignments_f paaf1
1098 ,per_all_assignments_f paaf2
1099 ,pay_payroll_actions ppa
1100 ,pay_payroll_actions ppa1
1101 ,per_time_period_types ptpt
1102 ,per_time_periods ptp
1103 WHERE paa1.assignment_action_id = c_assignment_action_id
1104 AND ppa1.payroll_action_id = paa1.payroll_action_id
1105 AND ppa1.business_group_id = paaf1.business_group_id
1106 AND paaf1.assignment_id = paa1.assignment_id
1107 AND paaf2.person_id = paaf1.person_id
1108 AND paaf2.business_group_id = paaf1.business_group_id
1109 AND paa2.assignment_id = paaf2.assignment_id
1110 AND paa2.tax_unit_id = paa1.tax_unit_id
1111 AND ppa.payroll_action_id = paa2.payroll_action_id
1112 AND ppa.business_group_id = paaf1.business_group_id
1113 AND paa2.source_action_id IS NULL
1114 AND ptp.period_type = ptpt.period_type
1115 AND ptp.start_date < c_period_start_date
1116 AND ptp.payroll_id = ppa.payroll_id
1117 AND ppa.action_type IN ('R','Q','I','B')
1118 AND ppa.action_status IN('C','U')
1119 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
1120 AND ptp.end_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1121 AND ptp.end_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
1122 ORDER BY ptp.start_date DESC,paa2.assignment_action_id DESC;
1123 --
1124 /* SELECT ptp.start_date start_date
1125 ,ptp.end_date end_date
1126 ,paa2.assignment_action_id assignment_action_id
1127 ,ppa.action_type action_type
1128 FROM pay_assignment_actions paa1
1129 ,pay_assignment_actions paa2
1130 ,per_all_assignments_f paaf1
1131 ,per_all_assignments_f paaf2
1132 ,pay_payroll_actions ppa
1133 ,per_time_periods ptp
1134 WHERE paa1.assignment_action_id = c_assignment_action_id
1135 AND paa1.assignment_id = paaf1.assignment_id
1136 AND paaf1.person_id = paaf2.person_id
1137 AND paaf2.assignment_id = paa2.assignment_id
1138 AND paa1.tax_unit_id = paa2.tax_unit_id
1139 AND paa2.payroll_action_id = ppa.payroll_action_id
1140 AND paa2.source_action_id IS NULL
1141 AND ptp.start_date < c_period_start_date
1142 AND ppa.payroll_id = ptp.payroll_id
1143 AND ppa.time_period_id = ptp.time_period_id
1144 AND ppa.action_type IN ('R','Q','I','B')
1145 AND ppa.action_status IN('C','U')
1146 AND ptp.start_date BETWEEN paaf1.effective_start_date
1147 AND paaf1.effective_end_date
1148 AND ptp.start_date BETWEEN paaf2.effective_start_date
1149 AND paaf2.effective_end_date
1150 ORDER BY ptp.start_date DESC,paa2.assignment_action_id DESC;
1151 */ --
1152 BEGIN
1153 l_def_bal_id_SS := pay_es_calc_ss_earnings.get_defined_bal_id( p_balance_SS, p_database_item_SS);
1154 l_def_bal_id_PU := pay_es_calc_ss_earnings.get_defined_bal_id( p_balance_PU, p_database_item_PU);
1155 p_ss_days := 0;
1156 l_Contri_Base_PU := 0;
1157 l_Contri_Base_180 := 0;
1158 l_ctr := 0;
1159 l_num := 0;
1160 l_prev_date := to_date('01-01-0001','dd-mm-yyyy');
1161 l_amt := 0;
1162 --
1163 BEGIN
1164 l_Contri_Base_PU := pay_balance_pkg.get_value(l_def_bal_id_PU, p_assignment_id, p_PU_start_date);
1165 EXCEPTION
1166 WHEN no_data_found THEN
1167 l_Contri_Base_PU := 0;
1168 END;
1169 --
1170 FOR i IN get_prev_periods_dates( p_assignment_action_id, p_PU_start_date) LOOP
1171 --
1172 IF l_prev_date <> i.start_date THEN
1173 --
1174 l_num := l_num + 1;
1175 IF l_ctr = 0 THEN
1176 p_ss_days := p_ss_days + pay_balance_pkg.get_value (l_def_bal_id_SS, i.assignment_action_id);
1177 END IF;
1178 --
1179 IF p_span_days <= p_ss_days THEN
1180 --
1181 BEGIN
1182 l_Contri_Base_180 := pay_balance_pkg.get_value (l_def_bal_id_PU,i.assignment_action_id);-- p_assignment_id, i.start_date);
1183 EXCEPTION
1184 WHEN no_data_found THEN
1185 l_Contri_Base_180 := 0;
1186 END;
1187 --
1188 IF l_ctr > 0 THEN
1189 l_amt := 1;
1190 EXIT;
1191 END IF;
1192 l_ctr := l_ctr + 1;
1193 --
1194 END IF;
1195 --
1196 l_prev_date := i.start_date;
1197 END IF;
1198 --
1199 END LOOP;
1200 --
1201 IF l_amt = 0 THEN
1202 RETURN (l_Contri_Base_PU);
1203 END IF;
1204 --
1205 IF l_num > 1 THEN
1206 RETURN (l_Contri_Base_PU - l_Contri_Base_180);
1207 ELSIF l_num = 1 THEN
1208 RETURN (l_Contri_Base_PU);
1209 ELSE
1210 RETURN 0;
1211 END IF;
1212 --
1213 END get_pu_contribution_value;
1214 --
1215
1216 --
1217 END pay_es_calc_ss_earnings;