[Home] [Help]
PACKAGE BODY: APPS.PAY_ES_SS_CALCULATION
Source
1 PACKAGE BODY pay_es_ss_calculation as
2 /* $Header: pyesssdc.pkb 120.14 2006/04/28 02:04:23 grchandr noship $ */
3 --
4 TYPE cac_epigraph_change_rec is RECORD
5 (cac VARCHAR2(15),
6 epigraph VARCHAR2(5),
7 epigraph_114 VARCHAR2(1),
8 epigraph_126 VARCHAR2(1),
9 days NUMBER,
10 start_date DATE,
11 end_date DATE,
12 no_ptm_days NUMBER,
13 no_ptm_hours NUMBER,
14 no_partial_strike_days NUMBER,
15 no_partial_strike_hours NUMBER,
16 active_without_pay_days NUMBER,
17 active_without_pay_hours NUMBER,
18 days_worked NUMBER,
19 no_td_days NUMBER,
20 Tot_Days NUMBER,
21 PU_Days NUMBER);
22 --
23 TYPE cac_epigraph_change_tab is TABLE of cac_epigraph_change_rec INDEX by BINARY_INTEGER;
24 cac_epigraph_change cac_epigraph_change_tab;
25 --
26 GIndex NUMBER;
27 --------------------------------------------------------------------------------
28 -- GET_ASSIGNMENT_INFO
29 --------------------------------------------------------------------------------
30 FUNCTION get_assignment_info(p_assignment_id IN NUMBER
31 ,p_effective_date IN DATE
32 ,p_contribution_grp OUT NOCOPY VARCHAR2
33 ,p_work_center OUT NOCOPY NUMBER
34 ,p_35_yrs_ss OUT NOCOPY VARCHAR2
35 ,p_seniority_yrs OUT NOCOPY NUMBER
36 ,p_date IN DATE) RETURN NUMBER
37 IS
38 --
39 CURSOR csr_get_per_info(c_assignment_id NUMBER
40 ,c_effective_date DATE) IS
41 SELECT pap.per_information5
42 ,pps.adjusted_svc_date
43 FROM per_all_people_f pap
44 ,per_all_assignments_f paaf
45 ,per_periods_of_service pps
46 WHERE paaf.assignment_id = c_assignment_id
47 AND paaf.person_id = pap.person_id
48 AND pap.person_id = pps.person_id
49 AND paaf.period_of_service_id = pps.period_of_service_id
50 AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date
51 AND c_effective_date between pap.effective_start_date and pap.effective_end_date;
52 --
53 CURSOR csr_get_assign_info(c_assignment_id NUMBER
54 ,c_effective_date DATE) IS
55 SELECT segment5
56 ,segment2
57 FROM per_all_assignments_f paaf
58 ,hr_soft_coding_keyflex scl
59 WHERE paaf.assignment_id = c_assignment_id
60 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
61 AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date;
62 --
63 l_date DATE;
64 --
65 BEGIN
66 --
67 hr_utility.trace('get_assignment_info ');
68 OPEN csr_get_per_info(p_assignment_id,p_date);
69 FETCH csr_get_per_info into p_35_yrs_ss, l_date;
70 CLOSE csr_get_per_info;
71 --
72 hr_utility.trace('p_35_yrs_ss '||p_35_yrs_ss);
73 --
74 p_seniority_yrs := FLOOR(MONTHS_BETWEEN(p_date,l_date)/12);
75 hr_utility.trace('p_seniority_yrs '||p_seniority_yrs);
76 --
77 OPEN csr_get_assign_info(p_assignment_id,p_date);
78 FETCH csr_get_assign_info into p_Contribution_grp,p_work_center;
79 CLOSE csr_get_assign_info;
80 --
81 hr_utility.trace('leaving get_assignment_info ');
82 --
83 return 0;
84 END get_assignment_info;
85 --
86 --------------------------------------------------------------------------------
87 -- GET_ABSENCE_DAYS
88 --------------------------------------------------------------------------------
89 FUNCTION get_absence_days(p_assignment_id IN NUMBER
90 ,p_business_group_id IN NUMBER
91 ,p_effective_date IN DATE
92 ,p_period_start_date IN DATE
93 ,p_period_end_date IN DATE
94 ,p_leave_type IN VARCHAR2
95 ,p_work_pattern IN VARCHAR2) RETURN NUMBER
96 IS
97 --
98 CURSOR csr_get_no_absence(c_assignment_id NUMBER
99 ,c_business_group_id NUMBER
100 ,c_effective_date DATE
101 ,c_period_start_date DATE
102 ,c_period_end_date DATE
103 ,c_leave_type VARCHAR2) IS
104 SELECT GREATEST (paa.DATE_START,c_period_start_date) start_date
105 ,LEAST(c_period_end_date,nvl(paa.date_end,to_date('31/12/4712','dd/mm/yyyy'))) end_date
106 ,paa.abs_information3 ptm_perc
107 FROM per_absence_attendances paa
108 ,per_absence_attendance_types paat
109 ,per_all_people_f pap
110 ,per_all_assignments_f paaf
111 WHERE paaf.assignment_id = c_assignment_id
112 AND paaf.business_group_id = c_business_group_id
113 AND paaf.person_id = pap.person_id
114 AND pap.person_id = paa.person_id
115 AND paat.absence_category = c_leave_type
116 AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
117 AND NVL(paa.date_end,c_period_end_date) >= c_period_start_date
118 AND paa.date_start <= c_period_end_date
119 AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
120 AND c_effective_date BETWEEN pap.effective_start_date AND Pap.effective_end_date;
121 --
122 l_no_days NUMBER;
123 l_days NUMBER;
124 l_date DATE;
125 l_is_wrking_day VARCHAR2(1);
126 l_error_code NUMBER;
127 l_error_msg fnd_new_messages.message_text%TYPE;
128 l_ptm_perc NUMBER;
129 --
130 BEGIN
131 --
132 l_no_days := 0;
133 l_ptm_perc := 0;
134 --
135 hr_utility.trace('~~ Type '||p_leave_type);
136 FOR i IN csr_get_no_absence(p_assignment_id
137 ,p_business_group_id
138 ,p_effective_date
139 ,p_period_start_date
140 ,p_period_end_date
141 ,p_leave_type) LOOP
142 l_date := i.start_date;
143 IF p_leave_type = 'PTM' THEN
144 l_ptm_perc := i.ptm_perc;
145 END IF;
146 l_days := 0;
147 IF p_work_pattern = 'Y' THEN
148 LOOP
149 EXIT WHEN l_date > i.end_date;
150 l_is_wrking_day := pqp_schedule_calculation_pkg.is_working_day
151 (p_assignment_id => p_assignment_id
152 ,p_business_group_id => p_business_group_id
153 ,p_date => l_date
154 ,p_error_code => l_error_code
155 ,p_error_message => l_error_msg
156 ,p_default_wp => null
157 );
158 IF l_is_wrking_day = 'Y' THEN
159 l_days := l_days + 1;
160 END IF;
161 l_date := l_date +1;
162 END LOOP;
163 IF p_leave_type = 'PTM' THEN
164 l_no_days := l_no_days + l_days*(100-l_ptm_perc)/100;
165 ELSE
166 l_no_days := l_no_days + l_days;
167 END IF;
168 ELSE
169 IF p_leave_type = 'PTM' THEN
170 l_no_days := l_no_days + ((i.end_date - i.start_date) + 1)*(100-l_ptm_perc)/100;
171 ELSE
172 l_no_days := l_no_days + (i.end_date - i.start_date) + 1;
173 END IF;
174 END IF;
175 END LOOP;
176 hr_utility.trace('~~ l_no_days '||l_no_days);
177 --
178 RETURN nvl(l_no_days,0);
179 --
180 END get_absence_days;
181 --
182 --------------------------------------------------------------------------------
183 -- GET_ABSENCE_HOURS
184 --------------------------------------------------------------------------------
185 FUNCTION get_absence_hours(p_assignment_id IN NUMBER
186 ,p_business_group_id IN NUMBER
187 ,p_effective_date IN DATE
188 ,p_period_start_date IN DATE
189 ,p_period_end_date IN DATE
190 ,p_leave_type IN VARCHAR2) RETURN NUMBER
191 IS
192 --
193 CURSOR csr_get_no_absence(c_assignment_id NUMBER
194 ,c_business_group_id NUMBER
195 ,c_effective_date DATE
196 ,c_period_start_date DATE
197 ,c_period_end_date DATE
198 ,c_leave_type VARCHAR2) IS
199 SELECT GREATEST(paa.date_start,c_period_start_date) start_date
200 ,LEAST(c_period_end_date,nvl(paa.date_end,to_date('31/12/4712','dd/mm/yyyy'))) end_date
201 ,time_start
202 ,time_end
203 FROM per_absence_attendances paa
204 ,per_absence_attendance_types paat
205 ,per_all_people_f pap
206 ,per_all_assignments_f paaf
207 WHERE paaf.assignment_id = c_assignment_id
208 AND paaf.business_group_id = c_business_group_id
209 AND paaf.person_id = pap.person_id
210 AND pap.person_id = paa.person_id
211 AND paat.absence_category = c_leave_type
212 AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
213 AND NVL(paa.date_end,c_period_end_date) >= c_period_start_date
214 AND paa.DATE_start <= c_period_end_date
215 AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date
216 AND c_effective_date between pap.effective_start_date and pap.effective_end_date;
217 --
218 l_no_hours NUMBER;
219 l_date DATE;
220 l_is_wrking_day VARCHAR2(1);
221 l_error_code NUMBER;
222 l_error_msg fnd_new_messages.message_text%TYPE;
223 l_hrs_wrked NUMBER;
224 --
225 BEGIN
226 --
227 l_no_hours := 0;
228 FOR i in csr_get_no_absence(p_assignment_id
229 ,p_business_group_id
230 ,p_effective_date
231 ,p_period_start_date
232 ,p_period_end_date
233 ,p_leave_type) LOOP
234 l_date := i.start_date;
235 IF i.start_date = i.end_date THEN
236 l_is_wrking_day := PQP_SCHEDULE_CALCULATION_PKG.is_working_day
237 (p_assignment_id => p_assignment_id
238 ,p_business_group_id => p_business_group_id
239 ,p_date => l_date
240 ,p_error_code => l_error_code
241 ,p_error_message => l_error_msg
242 ,p_default_wp => null
243 );
244 IF l_is_wrking_day = 'Y' THEN
245 l_hrs_wrked:=pqp_schedule_calculation_pkg.get_hours_worked
246 (p_assignment_id => p_assignment_id
247 ,p_business_group_id => p_business_group_id
248 ,p_date_start => l_date
249 ,p_date_end => l_date
250 ,p_error_code => l_error_code
251 ,p_error_message => l_error_msg
252 ,p_default_wp => NULL
253 );
254 IF i.time_start IS NOT NULL AND
255 i.time_end IS NOT NULL THEN
256 l_no_hours := l_no_hours + greatest((to_date('0001/01/01 '||i.time_end,'yyyy/mm/dd hh24:mi') - to_date('0001/01/01 '||i.time_start,'yyyy/mm/dd hh24:mi'))*24,l_hrs_wrked);
257 ELSE
258 l_no_hours := l_no_hours + l_hrs_wrked;
259 END IF;
260 END IF;
261 ELSE
262 l_hrs_wrked:=pqp_schedule_calculation_pkg.get_hours_worked
263 (p_assignment_id => p_assignment_id
264 ,p_business_group_id => p_business_group_id
265 ,p_date_start => i.start_date
266 ,p_date_end => i.end_date
267 ,p_error_code => l_error_code
268 ,p_error_message => l_error_msg
269 ,p_default_wp => NULL
270 );
271 l_no_hours := l_no_hours + l_hrs_wrked;
272 END IF;
273 END LOOP;
274 --
275 RETURN l_no_hours;
276 --
277 END get_absence_hours;
278 --
279 --------------------------------------------------------------------------------
280 -- GET_WORKING_TIME
281 --------------------------------------------------------------------------------
282 FUNCTION get_working_time(p_assignment_id IN NUMBER
283 ,p_business_group_id IN NUMBER
284 ,p_period_start_date IN DATE
285 ,p_period_end_date IN DATE
286 ,p_working_days OUT NOCOPY NUMBER
287 ,p_working_hours OUT NOCOPY NUMBER) RETURN NUMBER
288 IS
289 l_date DATE;
290 l_no_days NUMBER;
291 l_is_wrking_day VARCHAR2(1);
292 l_error_code NUMBER;
293 l_error_msg fnd_new_messages.message_text%TYPE;
294 --
295 BEGIN
296 --
297 l_date := p_period_start_date;
298 l_no_days := 0;
299 LOOP
300 EXIT WHEN l_date > p_period_end_date;
301 l_is_wrking_day := PQP_SCHEDULE_CALCULATION_PKG.is_working_day
302 (p_assignment_id => p_assignment_id
303 ,p_business_group_id => p_business_group_id
304 ,p_date => l_date
305 ,p_error_code => l_error_code
306 ,p_error_message => l_error_msg
307 ,p_default_wp => null
308 );
309 IF l_is_wrking_day = 'Y' THEN
310 l_no_days := l_no_days + 1;
311 END IF;
312 l_date := l_date + 1;
313 END LOOP;
314 p_working_hours := pqp_schedule_calculation_pkg.get_hours_worked
315 (p_assignment_id => p_assignment_id
316 ,p_business_group_id => p_business_group_id
317 ,p_date_start => p_period_start_date
318 ,p_date_end => p_period_end_date
319 ,p_error_code => l_error_code
320 ,p_error_message => l_error_msg
321 ,p_default_wp => NULL
322 );
323 p_working_days := l_no_days;
324 hr_utility.trace('**************** p_working_days '||p_working_days);
325 hr_utility.trace('**************** p_working_hours '||p_working_hours);
326 RETURN l_no_days;
327 END;
328 --
329 --------------------------------------------------------------------------------
330 -- GET_WORK_CENTER_INFO
331 --------------------------------------------------------------------------------
332 FUNCTION get_work_center_info(p_business_gr_id IN NUMBER
333 ,p_work_center IN NUMBER
334 ,p_info1 OUT NOCOPY VARCHAR2
335 ,p_info2 OUT NOCOPY VARCHAR2
336 ,p_info3 OUT NOCOPY VARCHAR2
337 ,p_info4 OUT NOCOPY VARCHAR2
338 ,p_info5 OUT NOCOPY VARCHAR2
339 ,p_info6 OUT NOCOPY VARCHAR2
340 ,p_info7 OUT NOCOPY VARCHAR2
341 ,p_info8 OUT NOCOPY VARCHAR2
342 ,p_info9 OUT NOCOPY VARCHAR2
343 ,p_info10 OUT NOCOPY VARCHAR2) RETURN NUMBER
344 IS
345 --
346 CURSOR csr_work_center(c_business_group_id NUMBER, c_work_center NUMBER)IS
347 SELECT hoi.org_information3
348 ,hoi.org_information4
349 ,hoi.org_information5
350 ,hoi.org_information6
351 ,hoi.org_information7
352 ,hoi.org_information8
353 ,hoi.org_information9
354 ,hoi.org_information10
355 ,hoi.org_information11
356 ,hoi.org_information12
357 FROM hr_organization_information hoi
358 WHERE hoi.organization_id = c_work_center
359 AND hoi.org_information_context = 'ES_WORK_CENTER_DETAILS';
360 --
361 BEGIN
362 --
363 OPEN csr_work_center(p_business_gr_id,p_work_center);
364 FETCH csr_work_center INTO p_info1
365 ,p_info2
366 ,p_info3
367 ,p_info4
368 ,p_info5
369 ,p_info6
370 ,p_info7
371 ,p_info8
372 ,p_info9
373 ,p_info10;
374 CLOSE csr_work_center;
375 --
376 RETURN 0;
377 END get_work_center_info;
378 --
379 --------------------------------------------------------------------------------
380 -- GET_LEGAL_EMPLOYER_INFO
381 --------------------------------------------------------------------------------
382 FUNCTION get_legal_employer_info(p_business_gr_id IN NUMBER
383 ,p_effective_date IN DATE
384 ,p_assignment_id IN NUMBER
385 ,p_work_center IN NUMBER
386 ,p_period_start_date IN DATE
387 ,p_period_end_date IN DATE
388 ,p_ss_type IN VARCHAR2
389 ,p_td_flag OUT NOCOPY VARCHAR2
390 ,p_td_rebate_days OUT NOCOPY NUMBER
391 ,p_le_td_perc OUT NOCOPY NUMBER
392 ,p_ss_td_perc OUT NOCOPY NUMBER
393 ,p_exempt_flag OUT NOCOPY VARCHAR2
394 ,p_exempt_days OUT NOCOPY NUMBER
395 ,p_le_exempt_perc OUT NOCOPY NUMBER
396 ,p_emp_exempt_perc OUT NOCOPY NUMBER
397 ,p_tot_days IN NUMBER
398 ,p_contract_type IN VARCHAR2) RETURN NUMBER
399 IS
400 --
401 CURSOR csr_legal_employer_info(c_business_group_id NUMBER
402 ,c_work_center NUMBER
403 ,c_type VARCHAR2
404 ,c_period_start_date DATE
405 ,c_period_end_date DATE)IS
406 SELECT hoi2.org_information1 situation
407 ,fnd_date.canonical_to_date(hoi2.org_information2) start_date
408 ,nvl(fnd_date.canonical_to_date(hoi2.org_information3),c_period_end_date) end_date
409 FROM hr_organization_information hoi
410 ,hr_organization_information hoi1
411 ,hr_all_organization_units hou
412 ,hr_organization_information hoi2
413 WHERE hou.business_group_id = p_business_gr_id
414 AND hoi.org_information1 = c_work_center
415 AND hoi.org_information_context = 'ES_WORK_CENTER_REF'
416 AND hoi1.organization_id = hou.organization_id
417 AND hoi2.organization_id = hou.organization_id
418 AND hou.organization_id = hoi.organization_id
419 AND hoi1.org_information_context = 'CLASS'
420 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
421 AND hoi2.org_information_context = c_type
422 AND fnd_date.canonical_to_date(hoi2.org_information2) <= c_period_end_date
423 and nvl(fnd_date.canonical_to_date(hoi2.org_information3),c_period_end_date) >= c_period_start_date
424 ORDER BY hoi1.organization_id ;
425 --
426 l_ret pay_user_column_instances_f.value%type;
427 l_tot_days NUMBER;
428 l_days NUMBER;
429 l_act_days NUMBER;
430 l_tmp_days NUMBER;
431 l_tmp_hours NUMBER;
432 --
433 BEGIN
434 --
435 p_td_flag := 'N';
436 p_le_td_perc := 0;
437 p_ss_td_perc := 0;
438 p_exempt_flag := 'N';
439 p_le_exempt_perc := 0;
440 p_emp_exempt_perc := 0;
441 p_td_rebate_days := 0;
442 p_exempt_days := 0;
443 --
444 l_tot_days := p_tot_days;
445 l_days := 0;
446 l_act_days := 0;
447 l_tmp_days := 0;
448 l_tmp_hours := 0;
449 --
450 FOR I IN csr_legal_employer_info(p_business_gr_id
451 ,p_work_center
452 ,'ES_TEMP_DISABILITY_MGT'
453 ,p_period_start_date
454 ,p_period_end_date) LOOP
455 l_act_days := p_period_end_date - p_period_start_date + 1;
456 l_days := (LEAST(p_period_end_date,i.end_date) - GREATEST(p_period_start_date,i.start_date) + 1);
457 IF p_contract_type = 'PART_TIME' THEN
458 l_days := get_working_time(p_assignment_id
459 ,p_business_gr_id
460 ,p_period_start_date
461 ,p_period_end_date
462 ,l_tmp_days
463 ,l_tmp_hours);
464 END IF;
465 -- If calculated days are more then the no of days employee is supposed to work
466 -- to handle 31 days
467 IF l_days > p_tot_days THEN
468 l_days := p_tot_days;
469 END IF;
470 -- If calculated days are for entire period
471 -- to handle 28 days
472 IF l_days = l_act_days THEN
473 l_days := p_tot_days;
474 END IF;
475 hr_utility.trace('~~~~~~~~ Exempt days '||l_days||p_ss_type|| i.situation);
476 --
477 IF p_ss_type = 'NON_IA_ID' AND i.situation IN ('EXP_LE_NON_IA_ID','EXP_SS') THEN
478 p_td_flag := 'Y';
479 p_td_rebate_days := p_td_rebate_days + l_days;
480 l_ret:= get_table_value(p_business_gr_id
481 ,'ES_CONTRIBUTION_RATES_FOR_NON_IA/ID_TEMPORARY_DISABILITY_MANAGEMENT'
482 ,'TEMPORARY_DISABILITY'
483 ,i.situation
484 ,p_effective_date);
485 IF i.situation = 'EXP_LE_NON_IA_ID' THEN
486 p_le_td_perc := p_le_td_perc + (to_number(l_ret) * (l_days/l_tot_days));
487 ELSIF i.situation = 'EXP_SS' THEN
488 p_ss_td_perc := p_ss_td_perc + (to_number(l_ret) * (l_days/l_tot_days));
489 END IF;
490 ELSIF p_ss_type = 'IA_ID' AND i.situation = 'EXP_LE_IA_ID' THEN
491 p_td_flag := 'Y';
492 p_td_rebate_days := p_td_rebate_days + l_days;
493 l_ret:= get_table_value(p_business_gr_id
494 ,'ES_CONTRIBUTION_RATES_FOR_IA/ID_TEMPORARY_DISABILITY_MANAGEMENT'
495 ,'TEMPORARY_DISABILITY'
496 ,i.situation
497 ,p_effective_date);
498 p_le_td_perc := p_le_td_perc + (to_number(l_ret) * (l_days/l_tot_days));
499 END IF;
500 END LOOP;
501 --
502 IF p_ss_type = 'NON_IA_ID' THEN
503 FOR I IN csr_legal_employer_info(p_business_gr_id
504 ,p_work_center
505 ,'ES_CONTRIB_EXEMPT'
506 ,p_period_start_date
507 ,p_period_end_date) LOOP
508 p_exempt_flag := 'Y';
509 l_act_days := p_period_end_date - p_period_start_date + 1;
510 l_days := (LEAST(p_period_end_date,i.end_date) - GREATEST(p_period_start_date,i.start_date) + 1);
511 IF p_contract_type = 'PART_TIME' THEN
512 l_days := get_working_time(p_assignment_id
513 ,p_business_gr_id
514 ,p_period_start_date
515 ,p_period_end_date
516 ,l_tmp_days
517 ,l_tmp_hours);
518 END IF;
519 -- If calculated days are more then the no of days employee is supposed to work
520 -- to handle 31 days
521 IF l_days > p_tot_days THEN
522 l_days := p_tot_days;
523 END IF;
524 -- If calculated days are for entire period
525 -- to handle 28 days
526 IF l_days = l_act_days THEN
527 l_days := p_tot_days;
528 END IF;
529 hr_utility.trace('l_days :'||l_days ||' l_tot_days : '||l_tot_days);
530 p_exempt_days := p_exempt_days + l_days;
531 l_ret:= get_table_value(p_business_gr_id
532 ,'ES_CONTRIBUTION_RATES_FOR_EXEMPT_SITUATIONS'
533 ,'EMPLOYERS_PERC'
534 ,i.situation
535 ,p_effective_date);
536
537 p_le_exempt_perc := p_le_exempt_perc + (to_number(l_ret) * (l_days/l_tot_days));
538 hr_utility.trace('p_le_exempt_perc :'||p_le_exempt_perc ||' l_ret : '||l_ret);
539 --
540 l_ret:= get_table_value(p_business_gr_id
541 ,'ES_CONTRIBUTION_RATES_FOR_EXEMPT_SITUATIONS'
542 ,'EMPLOYEES_PERC'
543 ,i.situation
544 ,p_effective_date);
545 p_emp_exempt_perc := p_emp_exempt_perc + (to_number(l_ret) * (l_days/l_tot_days));
546 hr_utility.trace('p_emp_exempt_perc :'||p_emp_exempt_perc ||' l_ret : '||l_ret);
547 END LOOP;
548 END IF;
549 p_td_rebate_days := LEAST(p_td_rebate_days,30);
550 p_exempt_days := LEAST(p_exempt_days,30);
551 --
552 hr_utility.trace('~~Rebate- LE - CE - TD ');
553 hr_utility.trace('~~--p_td_flag ' || p_td_flag );
554 hr_utility.trace('~~--p_td_rebate_days ' || p_td_rebate_days );
555 hr_utility.trace('~~--p_le_td_perc ' || p_le_td_perc );
556 hr_utility.trace('~~--p_ss_td_perc ' || p_ss_td_perc );
557 hr_utility.trace('~~--p_exempt_flag ' || p_exempt_flag );
558 hr_utility.trace('~~--p_exempt_days ' || p_exempt_days );
559 hr_utility.trace('~~--p_le_exempt_perc ' || p_le_exempt_perc );
560 hr_utility.trace('~~--p_emp_exempt_perc ' || p_emp_exempt_perc );
561 --
562 RETURN 0;
563 END get_legal_employer_info;
564 --
565 --------------------------------------------------------------------------------
566 -- GET_TRNG_HOURS
567 --------------------------------------------------------------------------------
568 FUNCTION get_trng_hours(p_business_gr_id IN NUMBER
569 ,p_assignment_id IN NUMBER
570 ,p_effective_date IN DATE
571 ,p_in_class_trng_hours OUT NOCOPY NUMBER
572 ,p_remote_trng_hours OUT NOCOPY NUMBER) RETURN NUMBER
573 IS
574 CURSOR csr_get_trng_hours(c_business_gr_id NUMBER
575 ,c_assignment_id NUMBER
576 ,c_effective_date DATE) IS
577 SELECT sum(CTR_INFORMATION2) In_Class_trng_hours
578 ,sum(CTR_INFORMATION3) Remote_trng_hours
579 FROM PER_CONTRACTS_f pcf
580 ,per_all_assignments_f paaf
581 WHERE paaf.assignment_id = c_assignment_id
582 AND paaf.business_group_id = c_business_gr_id
583 AND paaf.contract_id = pcf.contract_id
584 AND pcf.ctr_information_category = 'ES'
585 AND pcf.ctr_information1 = 'ES_TRAINING'
586 AND c_effective_date BETWEEN paaf.effective_start_date
587 AND paaf.effective_end_date
588 AND c_effective_date BETWEEN pcf.effective_start_date
589 AND pcf.effective_end_date ;
590 --
591 BEGIN
592 --
593 OPEN csr_get_trng_hours(p_business_gr_id
594 ,p_assignment_id
595 ,p_effective_date);
596 FETCH csr_get_trng_hours INTO p_in_class_trng_hours,p_remote_trng_hours;
597 CLOSE csr_get_trng_hours;
598 --
599 p_in_class_trng_hours := nvl(p_in_class_trng_hours,0);
600 p_remote_trng_hours := nvl(p_remote_trng_hours,0);
601
602 RETURN 0;
603 END get_trng_hours;
604 --
605 --------------------------------------------------------------------------------
606 -- GET_DEFINED_BAL_ID
607 --------------------------------------------------------------------------------
608 FUNCTION get_defined_bal_id(p_bal_name IN VARCHAR2
609 ,p_db_item_suffix IN VARCHAR2) RETURN NUMBER
610 IS
611 --
612 CURSOR get_def_bal_id is
613 SELECT pdb.defined_balance_id
614 FROM pay_balance_types pbt
615 ,pay_balance_dimensions pbd
616 ,pay_defined_balances pdb
617 WHERE pdb.balance_type_id = pbt.balance_type_id
618 AND pdb.balance_dimension_id = pbd.balance_dimension_id
619 AND pbt.balance_name = p_bal_name
620 AND pbd.database_item_suffix = p_db_item_suffix;
621 --
622 l_def_bal_id NUMBER;
623 --
624 BEGIN
625 --
626 OPEN get_def_bal_id;
627 FETCH get_def_bal_id into l_def_bal_id;
628 CLOSE get_def_bal_id;
629 RETURN l_def_bal_id;
630 --
631 END get_defined_bal_id;
632 --
633 --------------------------------------------------------------------------------
634 -- GET_PREV_SALARY
635 --------------------------------------------------------------------------------
636 FUNCTION get_prev_salary(p_assignment_action_id IN NUMBER
637 ,p_balance_name IN VARCHAR2
638 ,p_database_item_suffix IN VARCHAR2
639 ,p_period_start_date IN DATE
640 ,p_no_month IN NUMBER
641 ,p_flag IN VARCHAR2
642 ,p_context IN VARCHAR2
643 ,p_context_val IN VARCHAR2
644 ,p_days IN OUT NOCOPY NUMBER) RETURN NUMBER
645 IS
646 --
647 CURSOR get_prev_periods_dates (c_assignment_action_id NUMBER
648 ,c_period_start_date DATE) IS
649 SELECT ptp.start_date start_date
650 ,ptp.end_date end_date
651 ,ppa.action_type
652 ,MAX(paa2.assignment_action_id) assignment_action_id
653 FROM pay_assignment_actions paa1
654 ,pay_assignment_actions paa2
655 ,per_all_assignments_f paaf1
656 ,per_all_assignments_f paaf2
657 ,pay_payroll_actions ppa
658 ,pay_payroll_actions ppa1
659 ,per_time_periods ptp
660 ,per_time_period_types ptpt
661 WHERE paa1.assignment_action_id = c_assignment_action_id
662 AND ppa1.payroll_action_id = paa1.payroll_action_id
663 AND ppa1.business_group_id = paaf1.business_group_id
664 AND paa1.assignment_id = paaf1.assignment_id
665 AND paaf1.person_id = paaf2.person_id
666 AND paaf2.business_group_id = paaf1.business_group_id
667 AND paaf2.assignment_id = paa2.assignment_id
668 AND paa1.tax_unit_id = paa2.tax_unit_id
669 AND paa2.payroll_action_id = ppa.payroll_action_id
670 AND paa2.source_action_id IS NULL
671 AND ptp.start_date < c_period_start_date
672 AND ppa.payroll_id = ptp.payroll_id
673 AND ppa.business_group_id = paaf2.business_group_id
674 AND ptp.period_type = ptpt.period_type
675 AND ppa.action_type IN ('R','Q','I','B')
676 AND ppa.action_status IN('C','U')
677 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
678 AND ptp.end_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
679 AND ptp.end_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
680 GROUP BY ptp.start_date, ptp.end_date, ppa.action_type
681 ORDER BY 1 desc;
682
683 /* SELECT ptp.start_date start_date
684 ,ptp.end_date end_date
685 ,ppa.action_type
686 ,MAX(paa2.assignment_action_id) assignment_action_id
687 FROM pay_assignment_actions paa1
688 ,pay_assignment_actions paa2
689 ,per_all_assignments_f paaf1
690 ,per_all_assignments_f paaf2
691 ,pay_payroll_actions ppa
692 ,per_time_periods ptp
693 WHERE paa1.assignment_action_id = c_assignment_action_id
694 AND paa1.assignment_id = paaf1.assignment_id
695 AND paaf1.person_id = paaf2.person_id
696 AND paaf2.assignment_id = paa2.assignment_id
697 AND paa1.tax_unit_id = paa2.tax_unit_id
698 AND paa2.payroll_action_id = ppa.payroll_action_id
699 AND paa2.source_action_id IS NULL
700 AND ptp.start_date < c_period_start_date
701 AND ppa.payroll_id = ptp.payroll_id
702 AND ppa.time_period_id = ptp.time_period_id
703 AND ppa.action_type IN ('R','Q','I','B')
704 AND ppa.action_status IN('C','U')
705 AND ptp.end_date BETWEEN paaf1.effective_start_date
706 AND paaf1.effective_end_date
707 AND ptp.end_date BETWEEN paaf2.effective_start_date
708 AND paaf2.effective_end_date
709 GROUP BY ptp.start_date, ptp.end_date, ppa.action_type
710 ORDER BY 1 desc;
711 */
712 --
713 CURSOR get_legal_employer_id(c_work_center_id NUMBER) IS
714 SELECT hoi.organization_id
715 FROM hr_organization_information hoi
716 WHERE hoi.org_information1 = c_work_center_id
717 AND hoi.org_information_context = 'ES_WORK_CENTER_REF';
718 --
719 l_def_bal_id NUMBER;
720 l_amount NUMBER;
721 l_amt NUMBER;
722 l_ctr NUMBER;
723 l_cnt NUMBER;
724 l_start_date DATE;
725 l_date DATE;
726 l_legal_employer_id hr_All_organization_units.organization_id%TYPE;
727 --
728 BEGIN
729 --
730 hr_utility.trace('~~Entering pay_es_ss_calculation.get_prev_salary');
731 l_def_bal_id := get_defined_bal_id(p_balance_name, p_database_item_suffix);
732 hr_utility.trace('~~~~ p_balance_name'||p_balance_name);
733 hr_utility.trace('~~~~ p_database_item_suffix'||p_database_item_suffix);
734 hr_utility.trace('~~~~ l_def_bal_id'||l_def_bal_id);
735 l_amount := 0;
736 l_amt := 0;
737 l_ctr := 0;
738 l_cnt := 0;
739 p_days := 0;
740 l_date := to_date('01-01-0001','dd-mm-yyyy');
741 --
742 IF p_context = 'TAX_UNIT_ID' THEN
743 OPEN get_legal_employer_id(to_number(p_context_val));
744 FETCH get_legal_employer_id INTO l_legal_employer_id;
745 CLOSE get_legal_employer_id;
746 pay_balance_pkg.set_context('TAX_UNIT_ID', l_legal_employer_id);
747 hr_utility.trace('~~~~ Setting TAX_UNIT_ID Context '||l_legal_employer_id);
748 END IF;
749 --
750 hr_utility.trace('~~~~ Start loop p_period_start_date '||p_period_start_date);
751 hr_utility.trace('~~~~ p_assignment_action_id '||p_assignment_action_id);
752 FOR i IN get_prev_periods_dates( p_assignment_action_id, p_period_start_date) LOOP
753 --
754 IF l_date = i.start_date AND l_amt <> 0 THEN
755 NULL;
756 ELSE
757 l_amt := 0;
758 IF l_date <> i.start_date THEN
759 l_ctr := l_ctr + 1;
760 END IF;
761 l_amt := pay_balance_pkg.get_value (l_def_bal_id, i.assignment_action_id);
762 IF l_amt <> 0 THEN
763 l_cnt := l_cnt + 1;
764 END IF;
765 l_amount := l_amount + l_amt;
766 hr_utility.trace('~~~~ Inside loop start_date '||i.start_date);
767 hr_utility.trace('~~~~ assignment_action_id '||i.assignment_action_id);
768 hr_utility.trace('~~~~ l_ctr '||l_ctr);
769 hr_utility.trace('~~~~ l_cnt '||l_cnt);
770 hr_utility.trace('~~~~ l_amt '||l_amt);
771 hr_utility.trace('~~~~ l_amount '||l_amount);
772 hr_utility.trace('~~~~ p_no_month '||p_no_month);
773 IF l_amt <> 0 OR p_flag = 'N' THEN
774 p_days := p_days + last_day(i.start_date) - last_day(add_months(i.start_date,-1));
775 END IF;
776 IF l_ctr >= p_no_month THEN
777 IF l_cnt = p_no_month OR p_flag = 'N' THEN
778 RETURN l_amount;
779 /*ELSE
780 l_ctr := p_no_month - 1;*/
781 END IF;
782 END IF;
783 l_date := i.start_date;
784 END IF;
785 END LOOP;
786 hr_utility.trace('~~Exiting pay_es_ss_calculation.get_prev_salary');
787 RETURN l_amount;
788 --
789 END get_prev_salary;
790 --
791 --------------------------------------------------------------------------------
792 -- GET_ROW_VALUE
793 --------------------------------------------------------------------------------
794 FUNCTION get_row_value(p_effective_date IN DATE
795 ,p_reduction_id IN VARCHAR2
796 ,p_duration IN NUMBER) RETURN VARCHAR2
797 IS
798 --
799 CURSOR csr_get_row_value(c_reduction_id VARCHAR2
800 ,c_efective_date DATE ) IS
801 SELECT pur.row_low_range_or_name row_val
802 ,puci2.value Offset
803 ,puci3.value Duration
804 FROM pay_user_columns puc1
805 ,pay_user_columns puc2
806 ,pay_user_columns puc3
807 ,pay_user_rows_f pur
808 ,pay_user_tables put
809 ,pay_user_column_instances_f puci1
810 ,pay_user_column_instances_f puci2
811 ,pay_user_column_instances_f puci3
812 WHERE put.legislation_code = 'ES'
813 AND pur.user_table_id = put.user_table_id
814 AND puc1.user_table_id = put.user_table_id
815 AND puc1.user_column_name='REBATE_REDUCTION_ID'
816 AND puc2.user_table_id = put.user_table_id
817 AND puc2.user_column_name='OFFSET'
818 AND puc3.user_table_id = put.user_table_id
819 AND puc3.user_column_name='DURATION'
820 AND puci1.user_row_id = pur.user_row_id
821 AND puci1.user_column_id = puc1.user_column_id
822 AND puci1.value = c_reduction_id
823 AND puci2.user_row_id = pur.user_row_id
824 AND puci2.user_column_id = puc2.user_column_id
825 AND puci3.user_row_id = pur.user_row_id
826 AND puci3.user_column_id = puc3.user_column_id
827 AND put.user_table_name like 'ES_REBATE_OR_REDUCTION_RATES'
828 AND c_efective_date BETWEEN puci1.effective_start_date AND puci1.effective_end_date
829 AND c_efective_date BETWEEN puci2.effective_start_date AND puci2.effective_end_date
830 AND c_efective_date BETWEEN puci3.effective_start_date AND puci3.effective_end_date
831 AND c_efective_date BETWEEN pur.effective_start_date AND pur.effective_end_date
832 ORDER BY 1;
833 --
834 l_row VARCHAR2(4);
835 --
836 BEGIN
837 --
838 l_row := 0;
839 FOR i in csr_get_row_value(p_reduction_id, p_effective_date) LOOP
840 IF p_duration >= i.Offset AND p_duration <= (i.Offset + i.Duration) THEN
841 l_row := i.row_val;
842 END IF;
843 END LOOP;
844 --
845 RETURN l_row;
846 END get_row_value;
847 --
848 --------------------------------------------------------------------------------
849 -- GET_INPUT_VALUE
850 --------------------------------------------------------------------------------
851 FUNCTION get_input_value(p_assignment_id IN NUMBER
852 ,p_effective_date IN DATE
853 ,p_no_ptm_days OUT NOCOPY NUMBER
854 ,p_no_ptm_hours OUT NOCOPY NUMBER
855 ,p_no_partial_strike_days OUT NOCOPY NUMBER
856 ,p_no_partial_strike_hours OUT NOCOPY NUMBER
857 ,p_active_without_pay_days OUT NOCOPY NUMBER
858 ,p_active_without_pay_hours OUT NOCOPY NUMBER
859 ,p_rec_start_date IN DATE
860 ,p_rec_end_date IN DATE
861 ,p_cac IN VARCHAR2
862 ,p_epigraph_code IN VARCHAR2
863 ,p_period_end_date IN DATE) RETURN NUMBER
864 IS
865 CURSOR csr_get_value(c_assignment_id NUMBER
866 ,c_effective_date DATE
867 ,c_element_name VARCHAR2
868 ,c_input_value_name VARCHAR2
869 ,c_type VARCHAR2
870 ,c_rec_start_date DATE
871 ,c_rec_end_date DATE
872 ,c_period_end_date DATE) IS
873 SELECT Sum(decode(piv2.name, c_input_value_name, nvl(peev2.screen_entry_value,0), null)) adjusted_period
874 ,min(decode(piv2.name, 'Epigraph Code', nvl(peev2.screen_entry_value,'x'), null)) epigraph_code
875 ,min(decode(piv2.name, 'Secondary CAC', nvl(peev2.screen_entry_value,'x'), null)) Secondary_CAC
876 FROM pay_element_entries_f peef1
877 ,pay_element_entry_values_f peev1
878 ,pay_element_entry_values_f peev2
879 ,pay_input_values_f piv1
880 ,pay_input_values_f piv2
881 ,pay_element_types_f pet
882 WHERE pet.element_name = c_element_name
883 AND piv1.element_type_id = pet.element_type_id
884 AND piv2.element_type_id = pet.element_type_id
885 AND pet.legislation_code = 'ES'
886 AND piv1.name ='Reason'
887 AND peev1.screen_entry_value = c_type
888 AND peef1.element_type_id = pet.element_type_id
889 AND peef1.assignment_id = c_assignment_id
890 AND peev1.element_entry_id = peef1.element_entry_id
891 AND peev2.element_entry_id = peef1.element_entry_id
892 AND peev1.input_value_id = piv1.input_value_id
893 AND peev2.input_value_id = piv2.input_value_id
894 AND NVL(peef1.date_earned, c_period_end_date) BETWEEN c_rec_start_date
895 AND c_rec_end_date
896 AND c_effective_date BETWEEN pet.effective_start_date
897 AND pet.effective_end_date
898 AND c_effective_date BETWEEN peef1.effective_start_date
899 AND peef1.effective_end_date
900 AND c_effective_date BETWEEN peev1.effective_start_date
901 AND peev1.effective_end_date
902 AND c_effective_date BETWEEN piv1.effective_start_date
903 AND piv1.effective_end_date
904 AND c_effective_date BETWEEN peev2.effective_start_date
905 AND peev2.effective_end_date
906 AND c_effective_date BETWEEN piv2.effective_start_date
907 AND piv2.effective_end_date;
908 --
909 l_period NUMBER;
910 l_cac VARCHAR2(15);
911 l_epigraph VARCHAR2(5);
912 --
913 BEGIN
914 --
915 p_no_ptm_days := 0;
916 p_no_partial_strike_days := 0;
917 p_active_without_pay_days := 0;
918 --
919 OPEN csr_get_value(p_assignment_id
920 ,p_effective_date
921 ,'Social Security Days Adjustment'
922 ,'Days Adjustment'
923 ,'PTM'
924 ,p_rec_start_date
925 ,p_rec_end_date
926 ,p_period_end_date);
927 FETCH csr_get_value INTO l_period,l_epigraph,l_cac;
928 hr_utility.trace(' l_epigraph '||l_epigraph||' '||p_epigraph_code||' l_cac '||l_cac||' '||p_cac);
929 IF csr_get_value%NOTFOUND THEN
930 p_no_ptm_days := 0;
931 ELSE
932 IF l_epigraph = p_epigraph_code AND l_cac = p_cac THEN
933 p_no_ptm_days := l_period;
934 END IF;
935 END IF;
936 CLOSE csr_get_value;
937 --
938 OPEN csr_get_value(p_assignment_id
939 ,p_effective_date
940 ,'Social Security Hours Adjustment'
941 ,'Hours Adjustment'
942 ,'PTM'
943 ,p_rec_start_date
944 ,p_rec_end_date
945 ,p_period_end_date);
946 FETCH csr_get_value INTO l_period,l_epigraph,l_cac;
947 IF csr_get_value%NOTFOUND THEN
948 p_no_ptm_days := 0;
949 ELSE
950 IF l_epigraph = p_epigraph_code AND l_cac = p_cac THEN
951 p_no_ptm_days := l_period;
952 END IF;
953 END IF;
954 CLOSE csr_get_value;
955 --
956 OPEN csr_get_value(p_assignment_id
957 ,p_effective_date
958 ,'Social Security Days Adjustment'
959 ,'Days Adjustment'
960 ,'PS'
961 ,p_rec_start_date
962 ,p_rec_end_date
963 ,p_period_end_date);
964 FETCH csr_get_value INTO l_period,l_epigraph,l_cac;
965 IF csr_get_value%NOTFOUND THEN
966 p_no_partial_strike_days := 0;
967 ELSE
968 IF l_epigraph = p_epigraph_code AND l_cac = p_cac THEN
969 p_no_partial_strike_days := l_period;
970 END IF;
971 END IF;
972 CLOSE csr_get_value;
973 --
974 OPEN csr_get_value(p_assignment_id
975 ,p_effective_date
976 ,'Social Security Hours Adjustment'
977 ,'Hours Adjustment'
978 ,'PS'
979 ,p_rec_start_date
980 ,p_rec_end_date
981 ,p_period_end_date);
982 FETCH csr_get_value INTO l_period,l_epigraph,l_cac;
983 IF csr_get_value%NOTFOUND THEN
984 p_no_partial_strike_hours := 0;
985 ELSE
986 IF l_epigraph = p_epigraph_code AND l_cac = p_cac THEN
987 p_no_partial_strike_hours := l_period;
988 END IF;
989 END IF;
990 CLOSE csr_get_value;
991 --
992 OPEN csr_get_value(p_assignment_id
993 ,p_effective_date
994 ,'Social Security Days Adjustment'
995 ,'Days Adjustment'
996 ,'AWP'
997 ,p_rec_start_date
998 ,p_rec_end_date
999 ,p_period_end_date);
1000 FETCH csr_get_value INTO l_period,l_epigraph,l_cac;
1001 IF csr_get_value%NOTFOUND THEN
1002 p_active_without_pay_days := 0;
1003 ELSE
1004 IF l_epigraph = p_epigraph_code AND l_cac = p_cac THEN
1005 p_active_without_pay_days := l_period;
1006 END IF;
1007 END IF;
1008 CLOSE csr_get_value;
1009 --
1010 OPEN csr_get_value(p_assignment_id
1011 ,p_effective_date
1012 ,'Social Security Hours Adjustment'
1013 ,'Hours Adjustment'
1014 ,'AWP'
1015 ,p_rec_start_date
1016 ,p_rec_end_date
1017 ,p_period_end_date);
1018 FETCH csr_get_value INTO l_period,l_epigraph,l_cac;
1019 IF csr_get_value%NOTFOUND THEN
1020 p_active_without_pay_hours := 0;
1021 ELSE
1022 IF l_epigraph = p_epigraph_code AND l_cac = p_cac THEN
1023 p_active_without_pay_hours := l_period;
1024 END IF;
1025 END IF;
1026 CLOSE csr_get_value;
1027 --
1028 p_no_ptm_days := nvl(p_no_ptm_days,0);
1029 p_no_partial_strike_days := nvl(p_no_partial_strike_days,0);
1030 p_active_without_pay_days := nvl(p_active_without_pay_days,0);
1031 hr_utility.trace(' p_no_ptm_days '||p_no_ptm_days);
1032 hr_utility.trace(' p_no_partial_strike_days '||p_no_partial_strike_days);
1033 hr_utility.trace(' p_active_without_pay_days '||p_active_without_pay_days);
1034 hr_utility.trace(' p_no_ptm_hours '||p_no_ptm_hours);
1035 hr_utility.trace(' p_no_partial_strike_hours '||p_no_partial_strike_hours);
1036 hr_utility.trace(' p_active_without_pay_hours '||p_active_without_pay_hours);
1037 --
1038 RETURN 0;
1039 --
1040 END get_input_value;
1041 --
1042 --------------------------------------------------------------------------------
1043 -- GET_TABLE_VALUE
1044 --------------------------------------------------------------------------------
1045 FUNCTION get_table_value(bus_group_id IN NUMBER
1046 ,ptab_name IN VARCHAR2
1047 ,pcol_name IN VARCHAR2
1048 ,prow_value IN VARCHAR2
1049 ,peffective_date IN DATE )RETURN NUMBER IS
1050 --
1051 l_ret pay_user_column_instances_f.value%type;
1052 --
1053 BEGIN
1054 --
1055 BEGIN
1056 --
1057 l_ret:= hruserdt.get_table_value(bus_group_id
1058 ,ptab_name
1059 ,pcol_name
1060 ,prow_value
1061 ,peffective_date);
1062 --
1063 EXCEPTION
1064 WHEN NO_DATA_FOUND THEN
1065 l_ret:='0';
1066 END;
1067 --
1068 hr_utility.trace('l_ret '||l_ret);
1069 RETURN to_number(l_ret);
1070 --
1071 END get_table_value;
1072 --
1073 --------------------------------------------------------------------------------
1074 -- GET_ORG_CONTEXT_INFO
1075 --------------------------------------------------------------------------------
1076 FUNCTION get_org_context_info(p_assignment_id IN NUMBER
1077 ,p_business_group_id IN NUMBER
1078 ,p_work_center IN NUMBER
1079 ,p_context IN VARCHAR2
1080 ,p_period_start_date IN DATE
1081 ,p_period_end_date IN DATE
1082 ,p_tot_days IN NUMBER
1083 ,p_contract_type IN VARCHAR2) RETURN NUMBER
1084 IS
1085 CURSOR csr_get_context_info(c_work_center NUMBER
1086 ,c_context VARCHAR2
1087 ,c_period_start_date DATE
1088 ,c_period_end_date DATE) IS
1089 SELECT fnd_date.canonical_to_date(hoi.org_information1) Information_1
1090 ,nvl(fnd_date.canonical_to_date(hoi.org_information2),c_period_end_date) Information_2
1091 ,hoi.org_information3 Information_3
1092 FROM hr_organization_information hoi
1093 WHERE hoi.organization_id = c_work_center
1094 AND hoi.org_information_context = c_context --'ES_WC_PARTIAL_UNEMPLOYMENT'~~'ES_WC_NATURAL_DISASTER'
1095 ORDER BY 1;
1096 --
1097 l_days NUMBER;
1098 l_total_days NUMBER;
1099 l_end_date DATE;
1100 l_tmp_days NUMBER;
1101 l_tmp_hours NUMBER;
1102 l_act_days NUMBER;
1103 --
1104 BEGIN
1105 --
1106 hr_utility.trace('~~WC - '|| p_context);
1107 l_total_days := 0;
1108 l_act_days := 0;
1109 l_tmp_days := 0;
1110 l_tmp_hours := 0;
1111 FOR i IN csr_get_context_info(p_work_center,p_context,p_period_start_date,p_period_end_date) LOOP
1112 l_days := 0;
1113 hr_utility.trace('~~--Start Loop '|| i.Information_1);
1114 hr_utility.trace('~~----Start Date '|| i.Information_1);
1115 hr_utility.trace('~~----End Date'|| i.Information_2);
1116 IF i.Information_1 > p_period_end_date THEN
1117 hr_utility.trace('~~Total Days '|| l_total_days);
1118 RETURN ROUND(l_total_days);
1119 END IF;
1120 IF i.Information_2 >= p_period_start_date THEN
1121 --l_act_days := LEAST(p_period_end_date,i.Information_2)-GREATEST(p_period_start_date,i.Information_1) + 1;
1122 l_act_days := p_period_end_date - p_period_start_date + 1;
1123 l_days := LEAST(p_period_end_date,i.Information_2)-GREATEST(p_period_start_date,i.Information_1) + 1;
1124 IF p_contract_type = 'PART_TIME' THEN
1125 l_days := get_working_time(p_assignment_id
1126 ,p_business_group_id
1127 ,p_period_start_date
1128 ,p_period_end_date
1129 ,l_tmp_days
1130 ,l_tmp_hours);
1131 END IF;
1132 -- If calculated days are more then the no of days employee is supposed to work
1133 -- to handle 31 days
1134 IF l_days > p_tot_days THEN
1135 l_days := p_tot_days;
1136 END IF;
1137 -- If calculated days are for entire period
1138 -- to handle 28 days
1139 IF l_days = l_act_days THEN
1140 l_days := p_tot_days;
1141 END IF;
1142 hr_utility.trace('~~----l_days '|| l_days);
1143 IF p_context = 'ES_WC_PARTIAL_UNEMPLOYMENT' THEN
1144 hr_utility.trace('~~----PU Percentage '|| i.Information_3);
1145 l_days := l_days * fnd_number.canonical_to_number(i.Information_3)/100;
1146 END IF;
1147 END IF;
1148 l_total_days := l_total_days + l_days;
1149 END LOOP;
1150 hr_utility.trace('~~Total Days '|| l_total_days);
1151 RETURN l_total_days;
1152 --
1153 END get_org_context_info;
1154 --
1155 --------------------------------------------------------------------------------
1156 -- WRITE_CAC_EPIGRAPH_CHANGE_TABLE
1157 --------------------------------------------------------------------------------
1158 FUNCTION write_cac_epigraph_chg_table(p_assignment_id NUMBER
1159 ,p_effective_date DATE
1160 ,p_business_group_id NUMBER
1161 ,p_period_start_date DATE
1162 ,p_period_end_date DATE
1163 ,p_contract_type VARCHAR2
1164 ,p_hire_date DATE
1165 ,p_end_date DATE) RETURN NUMBER IS
1166 --
1167 CURSOR c_get_element_entries(c_assignment_id NUMBER
1168 ,c_start_date DATE
1169 ,c_end_date DATE) IS
1170 SELECT pee.element_entry_id
1171 ,GREATEST(pee.effective_start_date, c_start_date) start_date
1172 ,LEAST(pee.effective_end_date, c_end_date) end_date
1173 ,min(decode(piv.name, 'SS Epigraph 126', nvl(peev.screen_entry_value,'x'), null)) epigraph_126
1174 ,min(decode(piv.name, 'SS Epigraph 114', nvl(peev.screen_entry_value,'x'), null)) epigraph_114
1175 ,min(decode(piv.name, 'SS Epigraph Code', nvl(peev.screen_entry_value,'x'), null)) epigraph_code
1176 ,min(decode(piv2.name, 'Work Center CAC', nvl(peev2.screen_entry_value,0), null)) work_center_cac
1177 FROM pay_element_entries_f pee
1178 ,pay_element_entries_f pee2
1179 ,pay_element_types_f pet
1180 ,pay_element_types_f pet2
1181 ,pay_input_values_f piv
1182 ,pay_input_values_f piv2
1183 ,pay_element_entry_values_f peev
1184 ,pay_element_entry_values_f peev2
1185 WHERE pee.assignment_id = c_assignment_id
1186 AND pee2.assignment_id = pee.assignment_id
1187 AND pet.element_name = 'Social Security Details'
1188 AND pet2.element_name = 'Multiple Employment Details'
1189 AND pet.legislation_code = 'ES'
1190 AND pet2.legislation_code = 'ES'
1191 AND piv.legislation_code = 'ES'
1192 AND piv2.legislation_code = 'ES'
1193 AND pee.element_type_id = pet.element_type_id
1194 AND pee2.element_type_id = pet2.element_type_id
1195 AND piv.element_type_id = pet.element_type_id
1196 AND piv2.element_type_id = pet2.element_type_id
1197 AND peev.input_value_id = piv.input_value_id
1198 AND peev2.input_value_id = piv2.input_value_id
1199 AND peev.element_entry_id = pee.element_entry_id
1200 AND peev2.element_entry_id = pee2.element_entry_id
1201 AND pee.effective_start_date = peev.effective_start_date
1202 AND pee2.effective_start_date = peev2.effective_start_date
1203 AND pee.effective_end_date = peev.effective_end_date
1204 AND pee2.effective_end_date = peev2.effective_end_date
1205 AND pee2.effective_start_date = pee.effective_start_date
1206 AND pee2.effective_end_date = pee.effective_end_date
1207 AND (pee.effective_start_date <= c_end_date
1208 AND pee.effective_end_date >= c_start_date)
1209 AND c_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1210 AND c_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1211 AND c_start_date BETWEEN pet2.effective_start_date AND pet2.effective_end_date
1212 AND c_start_date BETWEEN piv2.effective_start_date AND piv2.effective_end_date
1213 GROUP BY pee.element_entry_id
1214 ,pee.effective_start_date
1215 ,pee.effective_end_date;
1216 --
1217 lctr NUMBER;
1218 l_no_ptm_days NUMBER;
1219 l_no_ptm_hours NUMBER;
1220 l_no_partial_strike_days NUMBER;
1221 l_no_partial_strike_hours NUMBER;
1222 l_active_without_pay_days NUMBER;
1223 l_active_without_pay_hours NUMBER;
1224 l_td_days NUMBER;
1225 l_tmp NUMBER;
1226 l_chk_work_pattern VARCHAR2(1);
1227 l_m_days NUMBER;
1228 l_tot_rec_days NUMBER;
1229 l_par_days NUMBER;
1230 l_ptm_days NUMBER;
1231 l_adoption_days NUMBER;
1232 l_tot_days NUMBER;
1233 l_rec_days NUMBER;
1234 l_tot_hours NUMBER;
1235 l_days_worked NUMBER;
1236 l_rec_hours NUMBER;
1237 p_Contribution_group VARCHAR2(10);
1238 p_work_center NUMBER;
1239 l_pu_days NUMBER;
1240 l_act_days NUMBER;
1241 l_tot_leave NUMBER;
1242 --
1243 CURSOR csr_get_assign_info(c_assignment_id NUMBER
1244 ,c_effective_date DATE) IS
1245 SELECT segment5
1246 ,segment2
1247 FROM per_all_assignments_f paaf
1248 ,hr_soft_coding_keyflex scl
1249 WHERE paaf.assignment_id = c_assignment_id
1250 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1251 AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date;
1252 --
1253 BEGIN
1254 --
1255 cac_epigraph_change.DELETE;
1256 lctr := 0;
1257 GIndex := 1;
1258 l_tot_rec_days := 0;
1259 l_act_days := p_period_end_date - p_period_start_date +1;
1260 --
1261 FOR i IN c_get_element_entries(p_assignment_id,p_period_start_date,p_period_end_date) LOOP
1262 --
1263 l_tmp := get_input_value(p_assignment_id
1264 ,p_effective_date
1265 ,l_no_ptm_days
1266 ,l_no_ptm_hours
1267 ,l_no_partial_strike_days
1268 ,l_no_partial_strike_hours
1269 ,l_active_without_pay_days
1270 ,l_active_without_pay_hours
1271 ,i.start_date
1272 ,i.end_date
1273 ,i.work_center_cac
1274 ,i.epigraph_code
1275 ,p_period_end_date);
1276 --
1277 OPEN csr_get_assign_info(p_assignment_id,i.end_date);
1278 FETCH csr_get_assign_info into p_Contribution_group,p_work_center;
1279 CLOSE csr_get_assign_info;
1280 --
1281 l_rec_days := i.end_date - i.start_date + 1;
1282 IF p_contract_type = 'FULL_TIME' THEN
1283 l_tot_rec_days := i.end_date - p_period_start_date + 1;
1284 IF TO_NUMBER(p_Contribution_group) >= 1 AND TO_NUMBER(p_Contribution_group) <= 7 THEN
1285 l_tot_days := 30;
1286 l_tot_rec_days := i.end_date - p_period_start_date + 1;
1287 IF (p_hire_date > p_period_start_date AND p_hire_date <= p_period_end_date)
1288 OR (p_end_date >= p_period_start_date AND p_end_date < p_period_end_date) THEN
1289 l_tot_rec_days := LEAST(i.end_date,p_end_date) - GREATEST(p_period_start_date,p_hire_date) + 1;
1290 l_tot_days := LEAST(p_period_end_date,p_end_date) - GREATEST(p_period_start_date,p_hire_date) + 1;
1291 END IF;
1292 ELSIF TO_NUMBER(p_Contribution_group) >= 8 AND TO_NUMBER(p_Contribution_group) <= 11 THEN
1293 l_tot_days := p_period_end_date - p_period_start_date + 1;
1294 END IF;
1295 l_chk_work_pattern := 'N';
1296 IF i.end_date = p_period_end_date THEN
1297 l_rec_days := l_rec_days - (l_tot_rec_days - l_tot_days);
1298 END IF;
1299 ELSE
1300 /* Calculate the total working days using Work Pattern */
1301 l_tmp := get_working_time(p_assignment_id
1302 ,p_business_group_id
1303 ,p_period_start_date
1304 ,p_period_end_date
1305 ,l_tot_days
1306 ,l_tot_hours);
1307 l_tmp := get_working_time(p_assignment_id
1308 ,p_business_group_id
1309 ,i.start_date
1310 ,i.end_date
1311 ,l_rec_days
1312 ,l_rec_hours);
1313 l_chk_work_pattern := 'Y';
1314 END IF;
1315 --
1316 l_pu_days := get_org_context_info(p_assignment_id,p_business_group_id,p_work_center,'ES_WC_PARTIAL_UNEMPLOYMENT',i.start_date,i.end_date,l_tot_days,p_contract_type);
1317 l_td_days := get_absence_days(p_assignment_id,p_business_group_id,i.end_date,i.start_date,i.end_date,'TD',l_chk_work_pattern);
1318 l_m_days := get_absence_days(p_assignment_id,p_business_group_id,i.end_date,i.start_date,i.end_date,'M',l_chk_work_pattern);
1319 l_par_days := get_absence_days(p_assignment_id,p_business_group_id,i.end_date,i.start_date,i.end_date,'PAR',l_chk_work_pattern);
1320 l_ptm_days := get_absence_days(p_assignment_id,p_business_group_id,i.end_date,i.start_date,i.end_date,'PTM',l_chk_work_pattern);
1321 l_adoption_days := get_absence_days(p_assignment_id,p_business_group_id,i.end_date,i.start_date,i.end_date,'IE_AL',l_chk_work_pattern);
1322 --
1323 l_m_days := l_m_days + l_adoption_days;
1324 --
1325 l_no_ptm_days := l_no_ptm_days + l_m_days + l_par_days + l_ptm_days ;
1326 l_tot_leave := nvl(l_td_days,0) + nvl(l_no_ptm_days,0) + nvl(l_no_partial_strike_days,0) + nvl(l_active_without_pay_days,0) + nvl(l_pu_days,0);
1327 If l_tot_leave = l_act_days THEN
1328 l_tot_leave := l_rec_days;
1329 END IF;
1330 l_days_worked := GREATEST(l_rec_days - l_tot_leave,0);
1331 --
1332 --
1333 IF lctr = 0 THEN
1334 lctr := lctr + 1;
1335 cac_epigraph_change(lctr).cac := i.work_center_cac;
1336 cac_epigraph_change(lctr).epigraph := i.epigraph_code;
1337 cac_epigraph_change(lctr).epigraph_114 := i.epigraph_114;
1338 cac_epigraph_change(lctr).epigraph_126 := i.epigraph_126;
1339 cac_epigraph_change(lctr).days := l_rec_days;
1340 cac_epigraph_change(lctr).start_date := i.start_date;
1341 cac_epigraph_change(lctr).end_date := i.end_date;
1342 cac_epigraph_change(lctr).no_ptm_days := l_no_ptm_days;
1343 cac_epigraph_change(lctr).no_ptm_hours := l_no_ptm_hours;
1344 cac_epigraph_change(lctr).no_partial_strike_days := l_no_partial_strike_days;
1345 cac_epigraph_change(lctr).no_partial_strike_hours := l_no_partial_strike_hours;
1346 cac_epigraph_change(lctr).active_without_pay_days := l_active_without_pay_days;
1347 cac_epigraph_change(lctr).active_without_pay_hours := l_active_without_pay_hours;
1348 cac_epigraph_change(lctr).days_worked := l_days_worked;
1349 cac_epigraph_change(lctr).no_td_days := l_td_days;
1350 cac_epigraph_change(lctr).Tot_Days := l_tot_days;
1351 cac_epigraph_change(lctr).PU_Days := l_pu_days;
1352
1353
1354 IF i.epigraph_114 = 'Y' THEN
1355 cac_epigraph_change(lctr).epigraph := '114';
1356 END IF;
1357 FND_FILE.NEW_LINE(fnd_file.log, 1);
1358 FND_FILE.PUT(fnd_file.log,rpad(i.work_center_cac,10));
1359 FND_FILE.PUT(fnd_file.log,rpad(i.epigraph_code,10));
1360 FND_FILE.PUT(fnd_file.log,rpad(i.epigraph_114,10));
1361 FND_FILE.PUT(fnd_file.log,rpad(i.epigraph_126,10));
1362 FND_FILE.PUT(fnd_file.log,rpad(to_char(l_rec_days),10));
1363 FND_FILE.PUT(fnd_file.log,rpad(to_char(i.start_date,'dd-mm-yyyy'),10));
1364 FND_FILE.PUT(fnd_file.log,rpad(to_char(i.end_date,'dd-mm-yyyy'),10));
1365 FND_FILE.PUT(fnd_file.log,rpad(to_char(l_days_worked),5));
1366 FND_FILE.PUT(fnd_file.log,rpad(to_char(l_td_days),5));
1367 FND_FILE.PUT(fnd_file.log,rpad(to_char(l_tot_days),5));
1368 FND_FILE.PUT(fnd_file.log,rpad(to_char(l_pu_days),5));
1369 ELSE
1370 IF NOT cac_epigraph_change.exists(lctr) THEN
1371 RETURN (-1);
1372 END IF;
1373 IF cac_epigraph_change(lctr).cac <> i.work_center_cac
1374 OR cac_epigraph_change(lctr).epigraph <> i.epigraph_code
1375 OR cac_epigraph_change(lctr).epigraph_114 <> i.epigraph_114
1376 OR cac_epigraph_change(lctr).epigraph_126 <> i.epigraph_126 THEN
1377 lctr := lctr + 1;
1378 cac_epigraph_change(lctr).cac := i.work_center_cac;
1379 cac_epigraph_change(lctr).epigraph := i.epigraph_code;
1380 cac_epigraph_change(lctr).epigraph_114 := i.epigraph_114;
1381 IF i.epigraph_114 = 'Y' THEN
1382 cac_epigraph_change(lctr).epigraph := '114';
1383 END IF;
1384 cac_epigraph_change(lctr).epigraph_126 := i.epigraph_126;
1385 cac_epigraph_change(lctr).days := i.end_date - i.start_date + 1;
1386 cac_epigraph_change(lctr).start_date := i.start_date;
1387 cac_epigraph_change(lctr).end_date := i.end_date;
1388 cac_epigraph_change(lctr).no_ptm_days := l_no_ptm_days;
1389 cac_epigraph_change(lctr).no_ptm_hours := l_no_ptm_hours;
1390 cac_epigraph_change(lctr).no_partial_strike_days := l_no_partial_strike_days;
1391 cac_epigraph_change(lctr).no_partial_strike_hours := l_no_partial_strike_hours;
1392 cac_epigraph_change(lctr).active_without_pay_days := l_active_without_pay_days;
1393 cac_epigraph_change(lctr).active_without_pay_hours := l_active_without_pay_hours;
1394 cac_epigraph_change(lctr).days_worked := l_days_worked;
1395 cac_epigraph_change(lctr).no_td_days := l_td_days;
1396 cac_epigraph_change(lctr).Tot_Days := l_tot_days;
1397 cac_epigraph_change(lctr).PU_Days := l_pu_days;
1398 ELSE
1399 cac_epigraph_change(lctr).days := i.end_date - cac_epigraph_change(lctr).start_date + 1;
1400 cac_epigraph_change(lctr).end_date := i.end_date;
1401 cac_epigraph_change(lctr).no_ptm_days := cac_epigraph_change(lctr).no_ptm_days + l_no_ptm_days;
1402 cac_epigraph_change(lctr).no_ptm_hours := cac_epigraph_change(lctr).no_ptm_hours + l_no_ptm_hours;
1403 cac_epigraph_change(lctr).no_partial_strike_days := cac_epigraph_change(lctr).no_partial_strike_days + l_no_partial_strike_days;
1404 cac_epigraph_change(lctr).no_partial_strike_hours := cac_epigraph_change(lctr).no_partial_strike_hours + l_no_partial_strike_hours;
1405 cac_epigraph_change(lctr).active_without_pay_days := cac_epigraph_change(lctr).active_without_pay_days + l_active_without_pay_days;
1406 cac_epigraph_change(lctr).active_without_pay_hours :=cac_epigraph_change(lctr).active_without_pay_hours +l_active_without_pay_hours;
1407 cac_epigraph_change(lctr).days_worked := l_days_worked;
1408 cac_epigraph_change(lctr).no_td_days := l_td_days;
1409 cac_epigraph_change(lctr).Tot_Days := l_tot_days;
1410 cac_epigraph_change(lctr).PU_Days := l_pu_days;
1411 END IF;
1412 FND_FILE.NEW_LINE(fnd_file.log, 1);
1413 FND_FILE.PUT(fnd_file.log,rpad(i.work_center_cac,10));
1414 FND_FILE.PUT(fnd_file.log,rpad(i.epigraph_code,10));
1415 FND_FILE.PUT(fnd_file.log,rpad(i.epigraph_114,10));
1416 FND_FILE.PUT(fnd_file.log,rpad(i.epigraph_126,10));
1417 FND_FILE.PUT(fnd_file.log,rpad(to_char(l_rec_days),10));
1418 FND_FILE.PUT(fnd_file.log,rpad(to_char(i.start_date,'dd-mm-yyyy'),10));
1419 FND_FILE.PUT(fnd_file.log,rpad(to_char(i.end_date,'dd-mm-yyyy'),10));
1420 FND_FILE.PUT(fnd_file.log,rpad(to_char(l_days_worked),5));
1421 FND_FILE.PUT(fnd_file.log,rpad(to_char(l_td_days),5));
1422 FND_FILE.PUT(fnd_file.log,rpad(to_char(l_tot_days),5));
1423 FND_FILE.PUT(fnd_file.log,rpad(to_char(l_pu_days),5));
1424 END IF;
1425 END LOOP;
1426 RETURN lctr;
1427 --
1428 END write_cac_epigraph_chg_table;
1429 --
1430 --------------------------------------------------------------------------------
1431 -- READ_CAC_EPIGRAPH_CHG_TABLE
1432 --------------------------------------------------------------------------------
1433 FUNCTION read_cac_epigraph_chg_table(p_assignment_id IN NUMBER
1434 ,p_cac IN OUT NOCOPY VARCHAR2
1435 ,p_epigraph IN OUT NOCOPY VARCHAR2
1436 ,p_epigraph_114 IN OUT NOCOPY VARCHAR2
1437 ,p_epigraph_126 IN OUT NOCOPY VARCHAR2
1438 ,p_days IN OUT NOCOPY NUMBER
1439 ,p_start_date IN OUT NOCOPY DATE
1440 ,p_end_date IN OUT NOCOPY DATE
1441 ,p_no_ptm_days IN OUT NOCOPY NUMBER
1442 ,p_no_ptm_hours IN OUT NOCOPY NUMBER
1443 ,p_no_partial_strike_days IN OUT NOCOPY NUMBER
1444 ,p_no_partial_strike_hours IN OUT NOCOPY NUMBER
1445 ,p_active_without_pay_days IN OUT NOCOPY NUMBER
1446 ,p_active_without_pay_hours IN OUT NOCOPY NUMBER
1447 ,p_curr_index IN OUT NOCOPY NUMBER
1448 ,p_next_epigraph IN OUT NOCOPY VARCHAR2
1449 ,p_next_cac IN OUT NOCOPY VARCHAR2
1450 ,p_days_worked IN OUT NOCOPY NUMBER
1451 ,p_td_days IN OUT NOCOPY NUMBER
1452 ,p_tot_days IN OUT NOCOPY NUMBER
1453 ,p_pu_days IN OUT NOCOPY NUMBER) RETURN NUMBER
1454 IS
1455 --
1456 BEGIN
1457 --
1458 IF GIndex = 0 and cac_epigraph_change.LAST <> 0 THEN
1459 GIndex := 1;
1460 END IF;
1461 IF NOT cac_epigraph_change.exists(GIndex) THEN
1462 hr_utility.trace('~~RETRUN GIndex'||GIndex);
1463 RETURN -1;
1464 END IF;
1465
1466 p_epigraph := cac_epigraph_change(GIndex).epigraph;
1467 p_epigraph_114 := cac_epigraph_change(GIndex).epigraph_114;
1468 p_epigraph_126 := cac_epigraph_change(GIndex).epigraph_126;
1469 p_days := cac_epigraph_change(GIndex).days;
1470 p_start_date := cac_epigraph_change(GIndex).start_date;
1471 p_end_date := cac_epigraph_change(GIndex).end_date;
1472 p_no_ptm_days := nvl(cac_epigraph_change(GIndex).no_ptm_days,0);
1473 p_no_ptm_hours := nvl(cac_epigraph_change(GIndex).no_ptm_hours,0);
1474 p_no_partial_strike_days := nvl(cac_epigraph_change(GIndex).no_partial_strike_days,0);
1475 p_no_partial_strike_hours := nvl(cac_epigraph_change(GIndex).no_partial_strike_hours,0);
1476 p_active_without_pay_days := nvl(cac_epigraph_change(GIndex).active_without_pay_days,0);
1477 p_active_without_pay_hours := nvl(cac_epigraph_change(GIndex).active_without_pay_hours,0);
1478 p_days_worked := cac_epigraph_change(GIndex).days_worked;
1479 p_td_days := cac_epigraph_change(GIndex).no_td_days;
1480 p_tot_days := cac_epigraph_change(GIndex).Tot_Days;
1481 p_pu_days := cac_epigraph_change(GIndex).PU_Days;
1482 p_curr_index := GIndex;
1483
1484 hr_utility.trace('~~Read PL/SQl Tablep_epigraph '||p_epigraph);
1485 hr_utility.trace('~~--p_epigraph '||p_epigraph);
1486 hr_utility.trace('~~--p_epigraph_114 '||p_epigraph_114);
1487 hr_utility.trace('~~--p_epigraph_126 '||p_epigraph_126);
1488 hr_utility.trace('~~--p_start_date '||p_start_date);
1489 hr_utility.trace('~~--p_end_date '||p_end_date);
1490 hr_utility.trace('~~--p_no_ptm_days '||p_no_ptm_days);
1491 IF cac_epigraph_change(GIndex).cac = 0 THEN
1492 p_cac := '';
1493 ELSE
1494 p_cac := cac_epigraph_change(GIndex).cac;
1495 END IF;
1496 --
1497 IF GIndex = cac_epigraph_change.LAST THEN
1498 GIndex := 0;
1499 hr_utility.trace('~~RETURN GIndex'||GIndex);
1500 p_next_epigraph := 'x';
1501 p_next_cac := 'x';
1502 RETURN GIndex;
1503 END IF;
1504 --
1505 hr_utility.trace('~~RETURN GIndex'||GIndex);
1506 GIndex := GIndex + 1;
1507 p_next_epigraph := cac_epigraph_change(GIndex).epigraph;
1508 p_next_cac := cac_epigraph_change(GIndex).cac;
1509 Return GIndex;
1510 --
1511 END read_cac_epigraph_chg_table;
1512 --------------------------------------------------------------------------------
1513 -- READ_TABLE_INDEX
1514 --------------------------------------------------------------------------------
1515 FUNCTION read_table_index(p_next_epigraph IN OUT NOCOPY VARCHAR2
1516 ,p_next_cac IN OUT NOCOPY VARCHAR2) RETURN NUMBER IS
1517 BEGIN
1518 --
1519 IF GIndex <> 0 THEN
1520 p_next_epigraph := cac_epigraph_change(GIndex).epigraph;
1521 p_next_cac := cac_epigraph_change(GIndex).cac;
1522 ELSE
1523 p_next_epigraph := 'x';
1524 p_next_cac := 'x';
1525 END IF;
1526 RETURN (GIndex);
1527 --
1528 END read_table_index;
1529 --------------------------------------------------------------------------------
1530 -- READ_TABLE_INDEX_VALUES
1531 --------------------------------------------------------------------------------
1532 FUNCTION read_table_index_values(p_assignment_id IN NUMBER
1533 ,p_index IN NUMBER
1534 ,p_cac IN OUT NOCOPY VARCHAR2
1535 ,p_epigraph IN OUT NOCOPY VARCHAR2
1536 ,p_epigraph_114 IN OUT NOCOPY VARCHAR2
1537 ,p_epigraph_126 IN OUT NOCOPY VARCHAR2
1538 ,p_days IN OUT NOCOPY NUMBER
1539 ,p_start_date IN OUT NOCOPY DATE
1540 ,p_end_date IN OUT NOCOPY DATE
1541 ,p_no_ptm_days IN OUT NOCOPY NUMBER
1542 ,p_no_ptm_hours IN OUT NOCOPY NUMBER
1543 ,p_no_partial_strike_days IN OUT NOCOPY NUMBER
1544 ,p_no_partial_strike_hours IN OUT NOCOPY NUMBER
1545 ,p_active_without_pay_days IN OUT NOCOPY NUMBER
1546 ,p_active_without_pay_hours IN OUT NOCOPY NUMBER
1547 ,p_days_worked IN OUT NOCOPY NUMBER
1548 ,p_td_days IN OUT NOCOPY NUMBER
1549 ,p_tot_days IN OUT NOCOPY NUMBER
1550 ,p_pu_days IN OUT NOCOPY NUMBER) RETURN NUMBER
1551 IS
1552 --
1553 BEGIN
1554 --
1555 IF NOT cac_epigraph_change.exists(p_index) THEN
1556 hr_utility.trace('~~RETRUN GIndex'||GIndex);
1557 RETURN -1;
1558 END IF;
1559 --
1560 p_epigraph := cac_epigraph_change(p_index).epigraph;
1561 p_epigraph_114 := cac_epigraph_change(p_index).epigraph_114;
1562 p_epigraph_126 := cac_epigraph_change(p_index).epigraph_126;
1563 p_days := cac_epigraph_change(p_index).days;
1564 p_start_date := cac_epigraph_change(p_index).start_date;
1565 p_end_date := cac_epigraph_change(p_index).end_date;
1566 p_no_ptm_days := cac_epigraph_change(p_index).no_ptm_days;
1567 p_no_ptm_hours := cac_epigraph_change(p_index).no_ptm_hours;
1568 p_no_partial_strike_days := cac_epigraph_change(p_index).no_partial_strike_days;
1569 p_no_partial_strike_hours := cac_epigraph_change(p_index).no_partial_strike_hours;
1570 p_active_without_pay_days := cac_epigraph_change(p_index).active_without_pay_days;
1571 p_active_without_pay_hours := cac_epigraph_change(p_index).active_without_pay_hours;
1572 p_days_worked := cac_epigraph_change(p_index).days_worked;
1573 p_td_days := cac_epigraph_change(p_index).no_td_days;
1574 p_tot_days := cac_epigraph_change(p_index).Tot_Days;
1575 p_pu_days := cac_epigraph_change(p_index).PU_Days;
1576 IF cac_epigraph_change(p_index).cac = 0 THEN
1577 p_cac := '';
1578 ELSE
1579 p_cac := cac_epigraph_change(p_index).cac;
1580 END IF;
1581 --
1582 RETURN 0;
1583 --
1584 END read_table_index_values;
1585 --
1586 --------------------------------------------------------------------------------
1587 -- GET_PREV_BASE
1588 --------------------------------------------------------------------------------
1589 FUNCTION get_prev_base(p_assignment_action_id IN NUMBER
1590 ,p_balance_name IN VARCHAR2
1591 ,p_database_item_suffix IN VARCHAR2
1592 ,p_period_start_date IN DATE
1593 ,p_no_month IN NUMBER
1594 ,p_flag IN VARCHAR2
1595 ,p_context IN VARCHAR2
1596 ,p_context_val IN VARCHAR2
1597 ,p_ss_days IN OUT NOCOPY NUMBER
1598 ,p_days IN OUT NOCOPY NUMBER) RETURN NUMBER
1599 IS
1600 --
1601 CURSOR get_prev_periods_dates (c_assignment_action_id NUMBER
1602 ,c_period_start_date DATE) IS
1603 SELECT ptp.start_date start_date
1604 ,ptp.end_date end_date
1605 ,ppa.action_type
1606 ,max(paa2.assignment_action_id) assignment_action_id
1607 FROM pay_assignment_actions paa1
1608 ,per_all_assignments_f paaf1
1609 ,per_all_assignments_f paaf2
1610 ,pay_assignment_actions paa2
1611 ,pay_payroll_actions ppa
1612 ,pay_payroll_actions ppa1
1613 ,per_time_periods ptp
1614 ,per_time_period_types ptpt
1615 WHERE paa1.assignment_action_id = c_assignment_action_id
1616 AND ppa1.payroll_action_id = paa1.payroll_action_id
1617 AND ppa1.business_group_id = paaf1.business_group_id
1618 AND paaf1.assignment_id = paa1.assignment_id
1619 AND paaf2.person_id = paaf1.person_id
1620 AND paaf2.business_group_id = paaf1.business_group_id
1621 AND paa2.assignment_id = paaf2.assignment_id
1622 AND paa2.tax_unit_id = paa1.tax_unit_id
1623 AND paa2.source_action_id IS NULL
1624 AND ppa.payroll_action_id = paa2.payroll_action_id
1625 AND ppa.action_type IN ('R','Q','I','B')
1626 AND ppa.action_status IN ('C','U')
1627 AND ppa.business_group_id = paaf2.business_group_id
1628 AND ptp.payroll_id = ppa.payroll_id
1629 AND ptp.period_type = ptpt.period_type
1630 AND ptp.start_date < c_period_start_date
1631 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
1632 AND ptp.end_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
1633 AND ptp.end_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
1634 GROUP BY ptp.start_date, ptp.end_date, ppa.action_type
1635 ORDER BY 1 desc;
1636 /* SELECT ptp.start_date start_date
1637 ,ptp.end_date end_date
1638 ,ppa.action_type
1639 ,MAX(paa2.assignment_action_id) assignment_action_id
1640 FROM pay_assignment_actions paa1
1641 ,pay_assignment_actions paa2
1642 ,per_all_assignments_f paaf1
1643 ,per_all_assignments_f paaf2
1644 ,pay_payroll_actions ppa
1645 ,per_time_periods ptp
1646 WHERE paa1.assignment_action_id = c_assignment_action_id
1647 AND paa1.assignment_id = paaf1.assignment_id
1648 AND paaf1.person_id = paaf2.person_id
1649 AND paaf2.assignment_id = paa2.assignment_id
1650 AND paa1.tax_unit_id = paa2.tax_unit_id
1651 AND paa2.payroll_action_id = ppa.payroll_action_id
1652 AND paa2.source_action_id IS NULL
1653 AND ptp.start_date < c_period_start_date
1654 AND ppa.payroll_id = ptp.payroll_id
1655 AND ppa.time_period_id = ptp.time_period_id
1656 AND ppa.action_type IN ('R','Q','I','B')
1657 AND ppa.action_status IN('C','U')
1658 AND ptp.end_date BETWEEN paaf1.effective_start_date
1659 AND paaf1.effective_end_date
1660 AND ptp.end_date BETWEEN paaf2.effective_start_date
1661 AND paaf2.effective_end_date
1662 GROUP BY ptp.start_date, ptp.end_date, ppa.action_type
1663 ORDER BY 1 desc;
1664 */
1665 --
1666 CURSOR get_legal_employer_id(c_work_center_id NUMBER) IS
1667 SELECT hoi.organization_id
1668 FROM hr_organization_information hoi
1669 WHERE hoi.org_information1 = c_work_center_id
1670 AND hoi.org_information_context = 'ES_WORK_CENTER_REF';
1671 --
1672 l_def_bal_id NUMBER;
1673 l_days_def_bal_id NUMBER;
1674 l_amount NUMBER;
1675 l_ctr NUMBER;
1676 l_cnt NUMBER;
1677 l_start_date DATE;
1678 l_legal_employer_id hr_All_organization_units.organization_id%TYPE;
1679 l_amt NUMBER;
1680 l_date DATE;
1681 l_days NUMBER;
1682 --
1683 BEGIN
1684 --
1685 hr_utility.trace('~~Entering pay_es_ss_calculation.get_prev_salary');
1686 l_def_bal_id := get_defined_bal_id(p_balance_name, p_database_item_suffix);
1687 l_days_def_bal_id := get_defined_bal_id('Social Security Days', p_database_item_suffix);
1688 hr_utility.trace('~~~~ p_balance_name'||p_balance_name);
1689 hr_utility.trace('~~~~ p_database_item_suffix'||p_database_item_suffix);
1690 hr_utility.trace('~~~~ l_def_bal_id'||l_def_bal_id);
1691 l_amount := 0;
1692 l_amt := 0;
1693 l_days := 0;
1694 l_ctr := 0;
1695 l_cnt := 0;
1696 p_days := 0;
1697 p_ss_days := 0;
1698 l_date := to_date('01-01-0001','dd-mm-yyyy');
1699 --
1700 IF p_context = 'TAX_UNIT_ID' THEN
1701 OPEN get_legal_employer_id(to_number(p_context_val));
1702 FETCH get_legal_employer_id INTO l_legal_employer_id;
1703 CLOSE get_legal_employer_id;
1704 pay_balance_pkg.set_context('TAX_UNIT_ID', l_legal_employer_id);
1705 hr_utility.trace('~~~~ Setting TAX_UNIT_ID Context '||l_legal_employer_id);
1706 END IF;
1707 --
1708 hr_utility.trace('~~~~ Start loop p_period_start_date '||p_period_start_date);
1709 hr_utility.trace('~~~~ p_assignment_action_id '||p_assignment_action_id);
1710 FOR i IN get_prev_periods_dates( p_assignment_action_id, p_period_start_date) LOOP
1711 --
1712 IF l_date = i.start_date AND l_days <> 0 AND l_amt <> 0 THEN
1713 NULL;
1714 ELSE
1715 IF l_date <> i.start_date THEN
1716 l_ctr := l_ctr + 1;
1717 l_days := 0;
1718 l_amt := 0;
1719 END IF;
1720 hr_utility.trace('~~~~ Inside loop p_period_start_date '||p_period_start_date);
1721 hr_utility.trace('~~~~ p_assignment_action_id '||p_assignment_action_id);
1722 IF l_days = 0 THEN
1723 l_days := pay_balance_pkg.get_value(l_days_def_bal_id, i.assignment_action_id);
1724 END IF;
1725 IF l_days <> 0 THEN
1726 p_ss_days := p_ss_days + l_days;
1727 l_amt := pay_balance_pkg.get_value(l_def_bal_id, i.assignment_action_id);
1728 l_amount := l_amount + l_amt;
1729 IF l_amt <> 0 THEN
1730 l_cnt := l_cnt + 1;
1731 END IF;
1732 END IF;
1733 hr_utility.trace('~~~~ Inside loop start_date '||i.start_date);
1734 hr_utility.trace('~~~~ assignment_action_id '||i.assignment_action_id);
1735 hr_utility.trace('~~~~ l_ctr '||l_ctr);
1736 hr_utility.trace('~~~~ l_cnt '||l_cnt);
1737 hr_utility.trace('~~~~ l_amt '||l_amt);
1738 hr_utility.trace('~~~~ l_days '||l_days);
1739 hr_utility.trace('~~~~ l_amount '||l_amount);
1740 hr_utility.trace('~~~~ p_ss_days '||p_ss_days);
1741 hr_utility.trace('~~~~ p_no_month '||p_no_month);
1742 IF (l_days <> 0)OR p_flag = 'N' THEN
1743 p_days := p_days + last_day(i.start_date) - last_day(add_months(i.start_date,-1));
1744 END IF;
1745 IF l_ctr >= p_no_month THEN
1746 IF (l_cnt = p_no_month)OR p_flag = 'N' THEN
1747 RETURN l_amount;
1748 /*ELSE
1749 l_ctr := l_ctr - 1;*/
1750 END IF;
1751 END IF;
1752 END IF;
1753 END LOOP;
1754 hr_utility.trace('~~Exiting pay_es_ss_calculation.get_prev_salary');
1755 RETURN l_amount;
1756 --
1757 END get_prev_base;
1758 --
1759 END pay_es_ss_calculation;