1 package body per_accrual_calc_functions as
2 /* $Header: peaclcal.pkb 120.10.12020000.5 2012/11/16 10:14:32 lbodired ship $ */
3 --
4 -- Start of fix 3222662
5 Type g_entry_value is table of
6 pay_element_entry_values_f.screen_entry_value%Type
7 index by binary_integer;
8 Type g_add_subtract is table of pay_net_calculation_rules.add_or_subtract%Type
9 index by binary_integer;
10 Type g_effective_date is table of date index by binary_integer;
11 -- End of 3222662
12 --
13 g_package varchar2(50) := ' per_accrual_calc_functions.'; -- Global package name
14
15 /* =====================================================================
16 Name : Calculate_Accrual
17 Purpose : Determines whether an assignment is enrolled in a plan, and
18 if so, executes the formula to calculate the accrual for
19 the plan(s).
20 ---------------------------------------------------------------------*/
21 procedure Calculate_Accrual
22 (P_Assignment_ID IN Number
23 ,P_Plan_ID IN Number
24 ,P_Payroll_ID IN Number
25 ,P_Business_Group_ID IN Number
26 ,P_Accrual_formula_ID IN Number
27 ,P_Assignment_Action_ID IN Number default null
28 ,P_Calculation_Date IN Date
29 ,P_Accrual_Start_Date IN Date default null
30 ,P_Accrual_Latest_Balance IN Number default null
31 ,P_Total_Accrued_PTO OUT NOCOPY Number
32 ,P_Effective_Start_Date OUT NOCOPY Date
33 ,P_Effective_End_Date OUT NOCOPY Date
34 ,P_Accrual_End_date OUT NOCOPY Date) is
35 --
36 /* CHECK VALIDITY OF ABOVE PARAMS */
37
38 l_proc varchar2(72) := g_package||'Calculate_Accrual';
39 l_inputs ff_exec.inputs_t;
40 l_outputs ff_exec.outputs_t;
41
42 begin
43 --
44 hr_utility.set_location('Entering '||l_proc, 5);
45 --
46 l_inputs(1).name := 'ASSIGNMENT_ID';
47 l_inputs(1).value := p_assignment_id;
48 l_inputs(2).name := 'DATE_EARNED';
49 l_inputs(2).value := fnd_date.date_to_canonical(p_calculation_date);
50 l_inputs(3).name := 'ACCRUAL_PLAN_ID';
51 l_inputs(3).value := p_plan_id;
52 l_inputs(4).name := 'BUSINESS_GROUP_ID';
53 l_inputs(4).value := p_business_group_id;
54 l_inputs(5).name := 'PAYROLL_ID';
55 l_inputs(5).value := p_payroll_id;
56 l_inputs(6).name := 'CALCULATION_DATE';
57 l_inputs(6).value := fnd_date.date_to_canonical(p_calculation_date);
58 l_inputs(7).name := 'ACCRUAL_START_DATE';
59 l_inputs(7).value := fnd_date.date_to_canonical(p_accrual_start_date);
60 l_inputs(8).name := 'ASSIGNMENT_ACTION_ID';
61 l_inputs(8).value := p_assignment_action_id;
62 l_inputs(9).name := 'ACCRUAL_LATEST_BALANCE';
63 l_inputs(9).value := p_accrual_latest_balance;
64
65 l_outputs(1).name := 'TOTAL_ACCRUED_PTO';
66 l_outputs(2).name := 'EFFECTIVE_START_DATE';
67 l_outputs(3).name := 'EFFECTIVE_END_DATE';
68 l_outputs(4).name := 'ACCRUAL_END_DATE';
69
70 per_formula_functions.run_formula(p_formula_id => p_accrual_formula_id,
71 p_calculation_date => p_calculation_date,
72 p_inputs => l_inputs,
73 p_outputs => l_outputs);
74
75 -- Bug fix 4004565
76 -- Fast formula output p_total_accrued_pto converted to number using
77 -- fnd_number.canonical_to_number function
78
79 p_total_accrued_pto := fnd_number.canonical_to_number(l_outputs(1).value);
80 p_effective_start_date := fnd_date.canonical_to_date(l_outputs(2).value);
81 p_effective_end_date := fnd_date.canonical_to_date(l_outputs(3).value);
82 p_accrual_end_date := fnd_date.canonical_to_date(l_outputs(4).value);
83 --
84 hr_utility.set_location('Leaving '||l_proc, 10);
85 --
86 end Calculate_Accrual;
87
88 --
89 /* =====================================================================
90 Name : Get_Accrual
91 Purpose :
92 Returns : Total Accrual
93 ---------------------------------------------------------------------*/
94 procedure Get_Accrual
95 (P_Assignment_ID IN Number
96 ,P_Calculation_Date IN Date
97 ,P_Plan_ID IN Number
98 ,P_Business_Group_ID IN Number
99 ,P_Payroll_ID IN Number
100 ,P_Assignment_Action_ID IN Number default null
101 ,P_Accrual_Start_Date IN Date default null
102 ,P_Accrual_Latest_Balance IN Number default null
103 ,P_Start_Date OUT NOCOPY Date
104 ,P_End_Date OUT NOCOPY Date
105 ,P_Accrual_End_Date OUT NOCOPY Date
106 ,P_Accrual OUT NOCOPY number) is
107
108 l_proc varchar2(72) := g_package||'Get_Accrual';
109 l_accrual_plan_rec g_accrual_plan_rec_type;
110 l_accrual_for_plan number := 0;
111 l_effective_start_date date;
112 l_effective_end_date date;
113 l_accrual_end_date date;
114 l_enrolled_in_plan boolean;
115
116 begin
117 --
118 hr_utility.set_location('Entering '||l_proc, 5);
119
120 l_accrual_plan_rec := get_accrual_plan(p_plan_id);
121
122 l_Enrolled_In_Plan := check_assignment_enrollment(
123 p_assignment_id
124 ,l_accrual_plan_rec.accrual_plan_element_type_id
125 ,p_calculation_date);
126
127 if l_enrolled_in_plan then
128 --
129 calculate_accrual(p_assignment_id => p_assignment_id,
130 p_plan_id => p_plan_id,
131 p_payroll_id => p_payroll_id,
132 p_business_group_id => p_business_group_id,
133 p_accrual_formula_id => l_accrual_plan_rec.accrual_formula_id,
134 p_assignment_action_id => p_assignment_action_id,
135 p_calculation_date => p_calculation_date,
136 p_accrual_start_date => p_accrual_start_date,
137 p_accrual_latest_balance => p_accrual_latest_balance,
138 p_total_accrued_pto => l_accrual_for_plan,
139 p_effective_start_date => l_effective_start_date,
140 p_effective_end_date => l_effective_end_date,
141 p_accrual_end_date => l_accrual_end_date
142 );
143
144 --
145 -- Set the return values of the out parameters
146 --
147
148 p_start_date := l_effective_start_date;
149 p_end_date := l_effective_end_date;
150 p_accrual_end_date := l_accrual_end_date;
151 p_accrual := l_accrual_for_plan;
152
153 --
154 else
155 --
156 p_start_date := null;
157 p_end_date := null;
158 p_accrual_end_date := null;
159 p_accrual := 0;
160 --
161 end if;
162 --
163 hr_utility.set_location('Leaving '||l_proc, 10);
164 --
165 end Get_Accrual;
166
167 --
168 /* =====================================================================
169 Name : Get_Accrual_Plan
170 Purpose :
171 Returns : Table of Accrual Plan Details
172 ---------------------------------------------------------------------*/
173 function Get_Accrual_Plan
174 (P_Plan_ID IN Number) RETURN g_accrual_plan_rec_type is
175 --
176 cursor c_accrual_plan_details is
177 select accrual_plan_element_type_id,
178 accrual_formula_id
179 from pay_accrual_plans
180 where accrual_plan_id = p_plan_id;
181
182 l_proc varchar2(72) := g_package||'Get_Accrual_Plan';
183 l_accrual_plan_rec g_accrual_plan_rec_type;
184
185 begin
186 --
187 hr_utility.set_location('Entering '||l_proc, 5);
188
189 hr_utility.trace('plan_id = '||to_char(p_plan_id));
190 open c_accrual_plan_details;
191 fetch c_accrual_plan_details into l_accrual_plan_rec.accrual_plan_element_type_id,
192 l_accrual_plan_rec.accrual_formula_id;
193 close c_accrual_plan_details;
194
195 hr_utility.set_location('Leaving '||l_proc, 10);
196
197 return l_accrual_plan_rec;
198 --
199 end Get_Accrual_Plan;
200
201 --
202
203 /* =====================================================================
204 Name : Check_Assignment_Enrollment
205 Purpose :
206 Returns : True if assignment is enrolled, otherwise false.
207 ---------------------------------------------------------------------*/
208 function Check_Assignment_Enrollment
209 (P_Assignment_ID IN Number
210 ,P_Accrual_Plan_Element_Type_ID IN Number
211 ,P_Calculation_Date IN Date) return Boolean is
212
213 cursor c_enrolled is
214 select 1
215 from pay_element_entries_f pee,
216 pay_element_links_f pel,
217 pay_element_types_f pet
218 where pel.element_link_id = pee.element_link_id
219 and pel.element_type_id = pet.element_type_id
220 and pee.assignment_id = p_assignment_id
221 and pet.element_type_id = p_accrual_plan_element_type_id
222 and p_calculation_date between pee.effective_start_date
223 and pee.effective_end_date;
224
225 l_proc varchar2(72) := g_package||'Check_Assignment_Enrollment';
226 l_enrolled boolean;
227 l_dummy c_enrolled%rowtype;
228
229 begin
230 --
231 hr_utility.set_location('Entering '||l_proc, 5);
232
233 open c_enrolled;
234 fetch c_enrolled into l_dummy;
235
236 l_enrolled := c_enrolled%found;
237
238 close c_enrolled;
239
240 hr_utility.set_location('Leaving '||l_proc, 10);
241
242 return l_enrolled;
243 --
244 end Check_Assignment_Enrollment;
245 --
246 /* =====================================================================
247 Name : Get_Carry_Over_Values
248 Purpose :
249 Returns : Max Carry over and effective date of carry over. Used by
250 carry over process.
251 ---------------------------------------------------------------------*/
252 procedure Get_Carry_Over_Values
253 (P_CO_Formula_ID IN Number
254 ,P_Assignment_ID IN Number
255 ,P_Accrual_Plan_ID IN Number
256 ,P_Business_Group_ID IN Number
257 ,P_Payroll_ID IN Number
258 ,P_Calculation_Date IN Date
259 ,P_Session_Date IN Date
260 ,P_Accrual_Term IN Varchar2
261 ,P_Effective_Date OUT NOCOPY Date
262 ,P_Expiry_Date OUT NOCOPY Date
263 ,P_Max_Carry_Over OUT NOCOPY Number) is
264
265 l_proc varchar2(72) := g_package||'Get_Carry_Over_Values';
266 l_inputs ff_exec.inputs_t;
267 l_outputs ff_exec.outputs_t;
268 l_p_calculation_date date; -- Added for the bug6354665
269 l_accrual_start varchar2(1); -- Added for the bug 6354665
270
271 -- Code change for the bug 6354665 starts here
272
273 CURSOR csr_emp_asg_act is
274 select null from
275 per_all_assignments_f asg,
276 per_periods_of_service pps
277 where asg.assignment_id = P_Assignment_ID
278 and P_calculation_date between asg.effective_start_date
279 and asg.effective_end_date
280 and asg.period_of_service_id = pps.period_of_service_id;
281
282 -- Code change for the bug 6354665 ends here
283
284 begin
285 --
286 hr_utility.set_location('Entering '||l_proc, 5);
287
288
289
290 l_inputs(1).name := 'ASSIGNMENT_ID';
291 l_inputs(1).value := p_assignment_id;
292 l_inputs(2).name := 'DATE_EARNED';
293 l_inputs(2).value := fnd_date.date_to_canonical(P_calculation_date);
294 l_inputs(3).name := 'ACCRUAL_PLAN_ID';
295 l_inputs(3).value := p_accrual_plan_id;
296 l_inputs(4).name := 'PAYROLL_ID';
297 l_inputs(4).value := p_payroll_id;
298 l_inputs(5).name := 'BUSINESS_GROUP_ID';
299 l_inputs(5).value := p_business_group_id;
300 l_inputs(6).name := 'CALCULATION_DATE';
301 l_inputs(6).value := fnd_date.date_to_canonical(p_calculation_date);
302 l_inputs(7).name := 'ACCRUAL_TERM';
303 l_inputs(7).value := p_accrual_term;
304
305 l_outputs(1).name := 'MAX_CARRYOVER';
306 l_outputs(2).name := 'EFFECTIVE_DATE';
307 l_outputs(3).name := 'EXPIRY_DATE';
308 l_outputs(4).name := 'PROCESS';
309
310 per_formula_functions.run_formula(p_formula_id => p_co_formula_id,
311 p_calculation_date => p_calculation_date,
312 p_inputs => l_inputs,
313 p_outputs => l_outputs);
314
315 -- Code Change for the bug6354665 starts here
316
317 OPEN csr_emp_asg_act;
318 FETCH csr_emp_asg_act INTO l_accrual_start;
319 IF csr_emp_asg_act%NOTFOUND THEN
320 l_p_calculation_date := FFFUNC.ADD_DAYS(fnd_date.canonical_to_date(l_outputs(2).value),1);
321 -- nvl condition added for the bug 7371746
322 l_inputs(2).value := fnd_date.date_to_canonical(nvl(l_p_calculation_date,p_calculation_date));
323
324 per_formula_functions.run_formula(p_formula_id => p_co_formula_id,
325 p_calculation_date => p_calculation_date,
326 p_inputs => l_inputs,
327 p_outputs => l_outputs);
328 END IF;
329 CLOSE csr_emp_asg_act;
330
331 -- Code Change for the bug6354665 ends here
332
333 if upper(l_outputs(4).value) = 'NO' then
334 --
335 p_max_carry_over := null;
336 --
337 else
338 --
339 -- Bug fix 4004565
340 -- Fast formula output converted to number using
341 -- fnd_number.canonical_to_number function
342
343 p_max_carry_over := fnd_number.canonical_to_number(l_outputs(1).value);
344 --
345 end if;
346
347 p_effective_date := fnd_date.canonical_to_date(l_outputs(2).value);
348 p_expiry_date := fnd_date.canonical_to_date(l_outputs(3).value);
349
350 hr_utility.set_location('Leaving '||l_proc, 10);
351 --
352 end Get_Carry_Over_Values;
353 --
354 /* =====================================================================
355 Name : Get_Absence
356 Purpose :
357 Returns : Total Absence
358 ---------------------------------------------------------------------*/
359 function Get_Absence
360 (P_Assignment_ID IN Number
361 ,P_Plan_ID IN Number
362 ,P_Calculation_Date IN Date
363 ,P_Start_Date IN Date
364 ,p_absence_attendance_type_id IN Number default NULL
365 ,p_pto_input_value_id IN NUMBER default NULL) return Number is
366
367 l_proc varchar2(72) := g_package||'Get_Absence';
368 l_total_absence number;
369
370 /* NOTE: This cursor has been tuned for the CBO. */
371
372 cursor c_get_total_absence is
373 select nvl(sum(nvl(abs.absence_days, abs.absence_hours)), 0)
374 from per_absence_attendances abs,
375 per_absence_attendance_types abt,
376 pay_net_calculation_rules ncr
377 where abs.absence_attendance_type_id =
378 abt.absence_attendance_type_id
379 and abt.input_value_id = ncr.input_value_id
380 and ((ncr.absence_attendance_type_id is not null
381 and ncr.absence_attendance_type_id =
382 abt.absence_attendance_type_id)
383 OR (ncr.absence_attendance_type_id is null
384 and exists
385 (select 'Y' from pay_accrual_plans
386 where accrual_plan_id = ncr.accrual_plan_id
387 and ncr.input_value_id = pto_input_value_id)
388 ))
389 and exists (select 'Y'
390 from per_all_assignments_f paf
391 where paf.assignment_id = p_assignment_id
392 and paf.person_id = abs.person_id)
393 and abs.date_start between p_start_date and p_calculation_date
394 and ncr.accrual_plan_id = p_plan_id;
395 --
396 cursor c_get_abs_per_type is
397 select nvl(sum(nvl(abs.absence_days, abs.absence_hours)), 0)
398 from per_absence_attendances abs,
399 pay_net_calculation_rules ncr
400 where ncr.absence_attendance_type_id = p_absence_attendance_type_id
401 and ncr.absence_attendance_type_id = abs.absence_attendance_type_id
402 and exists (select 'Y'
403 from per_all_assignments_f paf
404 where paf.assignment_id = p_assignment_id
405 and paf.person_id = abs.person_id)
406 and abs.date_start between p_start_date and p_calculation_date
407 and ncr.accrual_plan_id = p_plan_id;
408 --
409 cursor c_get_abs_per_iv is
410 select nvl(sum(nvl(abs.absence_days, abs.absence_hours)), 0)
411 from per_absence_attendances abs,
412 per_absence_attendance_types abt
413 where abs.absence_attendance_type_id = abt.absence_attendance_type_id
414 and abt.input_value_id = p_pto_input_value_id
415 and exists ( select 1
416 from per_all_assignments_f asg
417 where asg.assignment_id = p_assignment_id
418 and abs.person_id = asg.person_id
419 )
420 and abs.date_start between p_start_date and p_calculation_date;
421 --
422 begin
423 --
424 hr_utility.set_location('Entering '||l_proc, 5);
425 --
426 if p_absence_attendance_type_id is not null then
427 hr_utility.set_location(l_proc, 6);
428 open c_get_abs_per_type;
429 fetch c_get_abs_per_type into l_total_absence;
430 close c_get_abs_per_type;
431 elsif p_pto_input_value_id is not null then
432 hr_utility.set_location(l_proc, 7);
433 open c_get_abs_per_iv;
434 fetch c_get_abs_per_iv into l_total_absence;
435 close c_get_abs_per_iv;
436 else
437 hr_utility.set_location(l_proc, 8);
438 open c_get_total_absence;
439 fetch c_get_total_absence into l_total_absence;
440 close c_get_total_absence;
441 end if;
442 hr_utility.set_location('Leaving '||l_proc, 10);
443
444 return nvl(l_total_absence, 0);
445 --
446 end Get_Absence;
447
448 --
449 /* =====================================================================
450 Name : Get_Other_Net_Contribution
451 Purpose :
452 Returns : Total contribution of other elements.
453 ---------------------------------------------------------------------*/
454 function Get_Other_Net_Contribution
455 (P_Assignment_ID IN Number
456 ,P_Plan_ID IN Number
457 ,P_Calculation_Date IN Date
458 ,P_Start_Date IN Date
459 ,P_Input_Value_ID IN Number default null) return Number is
460
461 l_proc varchar2(72) := g_package||'Get_Other_Net_Contribution';
462 l_contribution number := 0;
463 -- Start of fix 3222662
464 l_limit natural := 100; -- Limiting the bulk collect, if not limited then bulk collect
465 -- returns entire rows for the condition, it may affect memory
466 l_prev_collect number := 0; -- Cumulative record count till previous fetch
467 l_curr_collect number := 0; -- Cumulative record count including the current fetch
468 l_diff_collect number := 0; -- To check that, whether the last fetch retrived any new
469 -- records, if not then to exit from the loop
470 g_amount_entries g_entry_value;
471 g_add_sub_entries g_add_subtract;
472 --
473 cursor c_get_contribution is
474 -- index hint applied for bug 4737028 to avoid bitmap conversion and usage of proper index
475 -- index PAY_ELEMENT_ENTRIES_F_N53 is now used in hint to resolve bug 5677610
476 -- index PAY_INPUT_VALUES_F_N50 is now used in hint to resolve bug 6621800
477
478 select /*+ index(pee PAY_ELEMENT_ENTRIES_F_N53,iv PAY_INPUT_VALUES_F_N50 )*/ fnd_number.canonical_to_number(pev.screen_entry_value) amount,
479 -- Bug 4551666, bug6621800
480 ncr.add_or_subtract add_or_subtract
481 from pay_accrual_plans pap,
482 pay_net_calculation_rules ncr,
483 pay_element_entries_f pee,
484 pay_element_entry_values_f pev,
485 pay_input_values_f iv
486 where pap.accrual_plan_id = p_plan_id
487 and pee.assignment_id = p_assignment_id
488 and pee.element_entry_id = pev.element_entry_id
489 and pev.input_value_id = ncr.input_value_id
490 and pap.accrual_plan_id = ncr.accrual_plan_id
491 and ncr.input_value_id not in
492 (pap.co_input_value_id,pap.pto_input_value_id)
493 and pev.screen_entry_value is not null
494 and ((p_input_value_id is not null and p_input_value_id = ncr.input_value_id)
495 or p_input_value_id is null)
496 and pev.effective_start_date = pee.effective_start_date
497 and pev.effective_end_date = pee.effective_end_date
498 and iv.input_value_id = ncr.input_value_id
499 and p_calculation_date between iv.effective_start_date and iv.effective_end_date
500 and pee.element_type_id = iv.element_type_id
501 and exists
502 (select /*+ index(piv2 PAY_INPUT_VALUES_F_N50)*/ null -- bug6621800
503 from pay_element_entry_values_f pev1,
504 pay_input_values_f piv2
505 where pev1.element_entry_id = pev.element_entry_id
506 and pev1.input_value_id = ncr.date_input_value_id
507 and pev1.effective_start_date = pev.effective_start_date
508 and pev1.effective_end_date = pev.effective_end_date
509 and ncr.date_input_value_id = piv2.input_value_id
510 and pee.element_type_id = piv2.element_type_id
511 and p_calculation_date between piv2.effective_start_date
512 and piv2.effective_end_date
513 and fnd_date.canonical_to_date(decode(substr(piv2.uom, 1, 1),'D',
514 pev1.screen_entry_value, Null))
515 between p_start_date and p_calculation_date);
516 --
517 begin
518 --
519 hr_utility.set_location('Entering '||l_proc, 5);
520 --
521 open c_get_contribution;
522 --
523 loop
524 --
525 fetch c_get_contribution bulk collect into
526 g_amount_entries, g_add_sub_entries limit l_limit;
527 l_prev_collect := l_curr_collect;
528 l_curr_collect := c_get_contribution%rowcount;
529 l_diff_collect := l_curr_collect - l_prev_collect;
530 --
531 if l_diff_collect > 0 then
532 --
533 for i in g_amount_entries.first..g_amount_entries.last loop
534 --
535 l_contribution := l_contribution + (g_amount_entries(i) *
536 g_add_sub_entries(i));
537 --
538 end loop;
539 --
540 end if;
541 --
542 -- Exiting, if the present fetch is NOT returning any new rows
543 exit when (l_diff_collect = 0);
544 --
545 --
546 end loop;
547 --
548 close c_get_contribution;
549 -- End of fix 3222662
550 /*
551 -- Bug 1570965. The below is commented out because we are interested
552 -- in displaying the negative value: it appears on the View Accruals form.
553 --
554 -- If we are dealing with a single net calculation rule,
555 -- we return the absolute value, rather than a potentially
556 -- negative one. We are only interested in the negative value
557 -- when summing all elements together, so that we get an
558 -- accurate result
559
560 if p_input_value_id is not null then
561 l_contribution := abs(l_contribution);
562 end if;
563 */
564 --
565 hr_utility.set_location('Leaving '||l_proc, 10);
566 --
567 return nvl(l_contribution, 0);
568 --
569 --
570 end Get_Other_Net_Contribution;
571 --
572 --
573 /* =====================================================================
574 Name : Get_Carry_Over
575 Purpose :
576 Returns : Total Carry Over amount
577 ---------------------------------------------------------------------*/
578 function Get_Carry_Over
579 (P_Assignment_ID IN Number
580 ,P_Plan_ID IN Number
581 ,P_Calculation_Date IN Date
582 ,P_Start_Date IN Date) return Number is
583
584 l_proc varchar2(72) := g_package||'Get_Carry_Over';
585 l_carryover number := 0;
586 l_absence number := 0;
587 l_net_absence number := 0;
588 -- Bug 4245674 Start
589 l_other number := 0;
590 l_net_other number := 0;
591 l_carry_diff number := 0;
592 -- Bug 4245674 End
593 l_expiry_date date;
594 l_start_date date := p_start_date;
595 l_old_date date;
596 -- Start of fix 3222662
597 l_count number := 1;
598 l_limit natural := 100; -- Limiting the bulk collect, if not limited then bulk collect
599 -- returns entire rows for the condition, it may affect memory
600 l_prev_collect number := 0; -- Cumulative record count till previous fetch
601 l_curr_collect number := 0; -- Cumulative record count including the current fetch
602 l_diff_collect number := 0; -- To check that, whether the last fetch retrived any new
603 -- records, if not then to exit from the loop
604 g_carry_over g_entry_value;
605 g_expiry_date g_effective_date;
606 g_carry_over1 g_entry_value;
607 g_expiry_date1 g_effective_date;
608
609 -- Fix for the bug 13935707
610 l_st_date date;
611 l_leap_year varchar2(4);
612
613 --
614 cursor c_get_carryover is
615 -- index hint applied for bug 4737028 to avoid bitmap conversion and usage of proper index
616 select /*+ index(pee PAY_ELEMENT_ENTRIES_F_N53)*/ fnd_number.canonical_to_number(nvl(pev.screen_entry_value, 0)) carryover,
617 fnd_date.canonical_to_date(nvl(pev1.screen_entry_value,
618 p_calculation_date)) expiry_date
619 from pay_accrual_plans pap,
620 pay_element_entry_values_f pev,
621 pay_element_entry_values_f pev1,
622 pay_input_values_f piv,
623 pay_input_values_f piv1,
624 pay_element_entries_f pee
625 where pap.accrual_plan_id = p_plan_id
626 and pee.assignment_id = p_assignment_id
627 and pee.element_entry_id = pev.element_entry_id
628 and pee.element_entry_id = pev1.element_entry_id
629 and pev.input_value_id = pap.co_input_value_id
630 and pev1.input_value_id = pap.co_exp_date_input_value_id
631 and pap.co_input_value_id = piv.input_value_id
632 and pap.co_exp_date_input_value_id = piv1.input_value_id
633 and p_calculation_date between piv.effective_start_date and piv.effective_end_date
634 and p_calculation_date between piv1.effective_start_date and piv1.effective_end_date
635 and pee.element_type_id = piv.element_type_id
636 and pee.element_type_id = piv1.element_type_id
637 and exists
638 (select null
639 from pay_element_entry_values_f pev2,
640 pay_input_values_f piv2
641 where pev2.element_entry_id = pev.element_entry_id
642 and pev2.input_value_id = pap.co_date_input_value_id
643 and pev2.input_value_id = piv2.input_value_id
644 and pev2.effective_start_date = pev.effective_start_date
645 and pev2.effective_end_date = pev.effective_end_date
646 and pap.co_date_input_value_id = piv2.input_value_id
647 and pee.element_type_id = piv2.element_type_id
648 and p_calculation_date between piv2.effective_start_date
649 and piv2.effective_end_date
650 and fnd_date.canonical_to_date(decode(substr(piv2.uom, 1, 1),'D',
651 pev2.screen_entry_value, Null)) <=
652 fnd_date.canonical_to_date(nvl(pev1.screen_entry_value,p_calculation_date))
653 and fnd_date.canonical_to_date(decode(substr(piv2.uom, 1, 1),'D',
654 pev2.screen_entry_value, Null))
655 between l_st_date and p_calculation_date)
656 order by expiry_date;
657
658 --
659 begin
660 --
661 hr_utility.set_location('Entering '||l_proc, 5);
662 --
663 -- Getting entire records into a PL/SQL table
664
665 l_leap_year := to_char(p_start_date,'ddmm');
666
667 if l_leap_year ='2902' then
668 l_st_date :=p_start_date -1;
669 else
670 l_st_date :=p_start_date;
671 end if;
672
673 hr_utility.set_location('l_st_date '|| l_st_date , 5);
674 --
675 open c_get_carryover;
676 --
677 loop
678 --
679 fetch c_get_carryover bulk collect into g_carry_over1, g_expiry_date1 limit l_limit;
680 --
681 l_prev_collect := l_curr_collect;
682 l_curr_collect := c_get_carryover%rowcount;
683 l_diff_collect := l_curr_collect - l_prev_collect;
684 --
685 if l_diff_collect > 0 then
686 --
687 for i in g_carry_over1.first..g_carry_over1.last loop
688 -- Setting the index
689 l_count := g_carry_over.count + 1;
690 -- Keeping the cumulated records to a seperate PL/SQL tables
691 g_carry_over(l_count) := g_carry_over1(i);
692 g_expiry_date(l_count) := g_expiry_date1(i);
693 end loop;
694 --
695 end if;
696 --
697 -- Exiting, if the present fetch is NOT returning any new rows
698 exit when (l_diff_collect = 0);
699 --
700 end loop;
701 --
702 close c_get_carryover;
703 --
704 --
705 -- Bug 4245674 Start
706 -- Desc : Modified this function to incorporate the Net Calculation rules
707 -- while calculating the net calculation rules.
708 -- Added comments throughout the fuction to explain each and every
709 -- actions.
710 l_old_date := p_start_date - 1;
711 --
712 if g_carry_over.count > 0 then
713 --
714 -- Because although you normally only have one carry over element entry,
715 -- users can manually add additional carry over entries in by hand.
716 for i in g_carry_over.first..g_carry_over.last loop
717 --
718 -- If you have more than one carry over entry and its expiry date is
719 -- later than the previous element entry, you need to sum any additional
720 -- absences and other contribution between the the first expiry period
721 -- and the second and add them to the total.
722 if g_expiry_date(i) > l_old_date then
723 --
724 l_start_date := l_old_date + 1;
725 --
726 l_absence := get_absence(
727 p_assignment_id => p_assignment_id,
728 p_plan_id => p_plan_id,
729 p_start_date => l_start_date,
730 p_calculation_date => g_expiry_date(i));
731
732 l_absence := l_absence + l_net_absence;
733
734 l_other := get_other_net_contribution(
735 p_assignment_id => p_assignment_id,
736 p_plan_id => p_plan_id,
737 p_start_date => l_start_date,
738 p_calculation_date => g_expiry_date(i));
739 l_other := l_other + l_net_other;
740 --
741 else
742 --
743 l_absence := l_net_absence;
744 l_other := l_net_other;
745 --
746 end if;
747 --
748 if p_calculation_date <= g_expiry_date(i) then
749 --
750 l_net_absence := l_absence;
751 l_carryover := l_carryover + g_carry_over(i);
752 l_net_other := l_other;
753 --
754 -- When the carryover has already expired, we showing the carryover amount
755 -- to be the amount of absences and other contribution - ie the amount
756 -- not forfeited.
757 else
758 --
759 -- This is for absence to be calculated.
760 if g_carry_over(i) <= l_absence then
761 --
762 l_net_absence := l_absence - g_carry_over(i);
763 l_carryover := l_carryover + g_carry_over(i);
764 l_carry_diff := 0;
765 --
766 else
767 --
768 l_net_absence := 0;
769 l_carryover := l_carryover + l_absence;
770 l_carry_diff := g_carry_over(i) - l_absence;
771 --
772 end if;
773 --
774 -- This is for Net Calculation rules to be calculated.
775 if l_other > 0 then
776 l_net_other := l_other;
777 else
778
779 if l_carry_diff <= abs(l_other) then
780 -- Remaining other is add into net_other.
781 l_net_other := l_other + l_carry_diff;
782 -- Adding the carryover in the absence and remaining carryover if the
783 -- net carryover calculated after absence deduction is less than the
784 -- net carryover.
785 l_carryover := l_carryover + l_carry_diff;
786
787 else
788 -- Utilized all the other. If some other remains then add to net
789 l_net_other := 0;
790 -- Case 1: when the other element is -ve(Subtract), Adding the carryover
791 -- and -ve of other if the net carryover is greater than other.
792 --
793 -- Case 2: when the other element is +ve (Add), If the carryover due to
794 -- absence is less than the other then what needs to be done.
795 -- If the carryover due to absence is more than the other then net
796 -- carryover is carryover_absence - other
797 l_carryover := l_carryover - l_other;
798 end if;
799
800 end if;
801
802 end if;
803 --
804 l_old_date := g_expiry_date(i);
805 --
806 end loop;
807 --
808 end if;
809 --
810 hr_utility.set_location('Leaving '||l_proc, 10);
811 --
812 return l_carryover;
813 --
814 -- End of fix 3222662
815 -- Bug 4245674 Ends.
816 --
817 end Get_Carry_Over;
818 --
819 --
820 /* =====================================================================
821 Name : Get_Net_Accrual
822 Purpose :
823 Returns : Total Accrued entitlement
824 ---------------------------------------------------------------------*/
825 procedure Get_Net_Accrual
826 (P_Assignment_ID IN Number
827 ,P_Plan_ID IN Number
828 ,P_Payroll_ID IN Number
829 ,P_Business_Group_ID IN Number
830 ,P_Assignment_Action_ID IN Number default -1
831 ,P_Calculation_Date IN Date
832 ,P_Accrual_Start_Date IN Date default null
833 ,P_Accrual_Latest_Balance IN Number default null
834 ,P_Calling_Point IN Varchar2 default 'FRM'
835 ,P_Start_Date OUT NOCOPY Date
836 ,P_End_Date OUT NOCOPY Date
837 ,P_Accrual_End_Date OUT NOCOPY Date
838 ,P_Accrual OUT NOCOPY Number
839 ,P_Net_Entitlement OUT NOCOPY Number) is
840
841 l_proc varchar2(72) := g_package||'Get_Net_Accrual';
842 l_absence number := 0; --changed for bug 6914353
843 l_accrual number;
844 l_other number := 0; --changed for bug 6914353
845 l_carryover number;
846 l_start_date date;
847 l_end_date date;
848 l_accrual_end_date date;
849 l_defined_balance_id number;
850
851 l_atd date; --added for bug 6418568
852
853 cursor c_get_balance is
854 select defined_balance_id
855 from pay_accrual_plans
856 where accrual_plan_id = p_plan_id;
857
858 --added for bug 6418568
859 cursor c_get_atd is
860 select nvl(pps.ACTUAL_TERMINATION_DATE,to_date('31/12/4712','dd/mm/yyyy'))
861 from per_periods_of_service pps, per_all_assignments_f paaf
862 where paaf.person_id = pps.person_id
863 and paaf.period_of_service_id = pps.period_of_service_id
864 and paaf.Assignment_ID = P_Assignment_ID
865 and P_Calculation_Date between paaf.effective_start_date and paaf.effective_end_date;
866
867
868 begin
869 --
870 hr_utility.set_location('Entering '||l_proc, 5);
871
872 --
873 -- Pipe the parameters for ease of debugging.
874 --
875 hr_utility.trace(' ');
876 hr_utility.trace(' --------------------------------'||
877 '---------------------------------');
878 hr_utility.trace(' ENTERING '||upper(l_proc));
879 hr_utility.trace(' for assignment '||to_char(p_assignment_id));
880 hr_utility.trace(' --------------------------------'||
881 '+--------------------------------');
882 hr_utility.trace(' p_assignment_id '||
883 to_char(p_assignment_id));
884 hr_utility.trace(' p_plan_id '||
885 to_char(p_plan_id));
886 hr_utility.trace(' p_payroll_id '||
887 to_char(p_payroll_id));
888 hr_utility.trace(' p_business_group_id '||
889 to_char(p_business_group_id));
890 hr_utility.trace(' p_assignment_action_id '||
891 to_char(p_assignment_action_id));
892 hr_utility.trace(' p_calculation_date '||
893 to_char(p_calculation_date));
894 hr_utility.trace(' p_accrual_start_date '||
895 to_char(p_accrual_start_date));
896 hr_utility.trace(' p_accrual_latest_balance '||
897 to_char(p_accrual_latest_balance));
898 hr_utility.trace(' p_calling_point '||
899 p_calling_point);
900 hr_utility.trace(' --------------------------------'||
901 '---------------------------------');
902 hr_utility.trace(' ');
903
904 open c_get_balance;
905 fetch c_get_balance into l_defined_balance_id;
906 close c_get_balance;
907
908 if p_calling_point = 'BP' and
909 l_defined_balance_id is not null and
910 p_assignment_action_id <> -1 then
911 --
912 /* Procedure called from batch process, so
913 get latest balance. */
914
915 p_net_entitlement := pay_balance_pkg.get_value(
916 p_defined_balance_id => l_defined_balance_id
917 ,p_assignment_action_id => p_assignment_action_id
918 );
919 --
920 else
921 --
922
923 get_accrual(p_assignment_id => p_assignment_id,
924 p_plan_id => p_plan_id,
925 p_calculation_date => p_calculation_date,
926 p_business_group_id => p_business_group_id,
927 p_payroll_id => p_payroll_id,
928 p_assignment_action_id => p_assignment_action_id,
929 p_accrual_start_date => p_accrual_start_date,
930 p_accrual_latest_balance => p_accrual_latest_balance,
931 p_start_date => l_start_date,
932 p_end_date => l_end_date,
933 p_accrual_end_date => l_accrual_end_date,
934 p_accrual => l_accrual);
935
936 --start changes for bug 6418568
937 open c_get_atd;
938 fetch c_get_atd into l_atd;
939 close c_get_atd;
940
941 if l_accrual_end_date is not null then
942 --
943 l_absence := get_absence(p_assignment_id => p_assignment_id,
944 p_plan_id => p_plan_id,
945 p_start_date => l_start_date,
946 p_calculation_date => l_end_date);
947
948 l_other := get_other_net_contribution(
949 p_assignment_id => p_assignment_id,
950 p_plan_id => p_plan_id,
951 p_start_date => l_start_date,
952 p_calculation_date => l_end_date
953 );
954 else
955 --
956 if l_atd >= P_Calculation_Date then
957 --
958 l_absence := get_absence(p_assignment_id => p_assignment_id,
959 p_plan_id => p_plan_id,
960 p_start_date => l_start_date,
961 p_calculation_date => l_end_date);
962
963 l_other := get_other_net_contribution(
964 p_assignment_id => p_assignment_id,
965 p_plan_id => p_plan_id,
966 p_start_date => l_start_date,
967 p_calculation_date => l_end_date
968 );
969 --
970 end if;
971 --
972 end if;
973 --end changes for bug 6418568
974
975 l_carryover :=0; -- 12880652
976 l_carryover := get_carry_over(
977 p_assignment_id => p_assignment_id,
978 p_plan_id => p_plan_id,
979 p_start_date => l_start_date,
980 p_calculation_date => l_end_date);
981 -- 12880652
982
983 if P_Calculation_Date > l_atd and l_carryover <> 0 then
984 l_other := get_other_net_contribution(
985 p_assignment_id => p_assignment_id,
986 p_plan_id => p_plan_id,
987 p_start_date => l_start_date,
988 p_calculation_date => l_end_date
989 );
990
991 l_absence := get_absence(p_assignment_id => p_assignment_id,
992 p_plan_id => p_plan_id,
993 p_start_date => l_start_date,
994 p_calculation_date => l_end_date); -- added new 12880652
995
996
997 end if;
998 -- 12880652
999
1000 --
1001 -- Set up values in the return parameters.
1002 --
1003 p_net_entitlement := l_accrual - l_absence + l_other + l_carryover;
1004 p_accrual := l_accrual;
1005 p_start_date := l_start_date;
1006 p_end_date := l_end_date;
1007 p_accrual_end_date := l_accrual_end_date;
1008 --
1009 end if;
1010
1011 --
1012 -- Pipe the parameters for ease of debugging.
1013 --
1014 hr_utility.trace(' ');
1015 hr_utility.trace(' --------------------------------'||
1016 '---------------------------------');
1017 hr_utility.trace(' LEAVING '||upper(l_proc));
1018 hr_utility.trace(' --------------------------------'||
1019 '+--------------------------------');
1020 hr_utility.trace(' p_start_date '||
1021 to_char(p_start_date));
1022 hr_utility.trace(' p_end_date '||
1023 to_char(p_end_date));
1024 hr_utility.trace(' p_accrual_end_date '||
1025 to_char(p_accrual_end_date));
1026 hr_utility.trace(' p_accrual '||
1027 to_char(p_accrual));
1028 hr_utility.trace(' p_net_entitlement '||
1029 to_char(p_net_entitlement));
1030 hr_utility.trace(' --------------------------------'||
1031 '---------------------------------');
1032 hr_utility.trace(' ');
1033
1034 --
1035 end Get_Net_Accrual;
1036 --
1037 /* =====================================================================
1038 Name : get_asg_inactive_days
1039 Purpose : Gets the number of days in a period where the assignment
1040 status is not 'Active'.
1041 Returns : Number of inactive days in the period.
1042 ---------------------------------------------------------------------*/
1043 FUNCTION get_asg_inactive_days
1044 (p_assignment_id IN NUMBER,
1045 p_period_sd IN DATE,
1046 p_period_ed IN DATE) RETURN NUMBER IS
1047
1048 CURSOR csr_period_asg_status IS
1049 SELECT asg.effective_start_date,
1050 asg.effective_end_date,
1051 ast.per_system_status
1052 FROM per_all_assignments_f asg,
1053 per_assignment_status_types ast
1054 WHERE asg.assignment_id = p_assignment_id
1055 AND ((asg.effective_start_date BETWEEN p_period_sd AND p_period_ed
1056 OR asg.effective_end_date BETWEEN p_period_sd AND p_period_ed)
1057 OR (p_period_sd BETWEEN asg.effective_start_date AND asg.effective_end_date))
1058 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1059 AND ast.per_system_status <> 'ACTIVE_ASSIGN';
1060
1061 l_proc VARCHAR2(72) := g_package||'get_loa_days';
1062 l_assignment_sd DATE;
1063 l_assignment_ed DATE;
1064 l_assignment_status per_assignment_status_types.per_system_status%TYPE;
1065 l_asg_inactive_days NUMBER := 0;
1066
1067 BEGIN
1068 --
1069 hr_utility.set_location('Entering '||l_proc, 10);
1070
1071 -- Loop each inactive assignment record for the period and count the inactive days
1072 OPEN csr_period_asg_status;
1073 LOOP
1074 FETCH csr_period_asg_status INTO
1075 l_assignment_sd,
1076 l_assignment_ed,
1077 l_assignment_status;
1078 EXIT WHEN csr_period_asg_status%NOTFOUND;
1079 -- Count inactive days
1080 l_asg_inactive_days := l_asg_inactive_days + get_working_days(
1081 GREATEST(l_assignment_sd, p_period_sd),
1082 LEAST(l_assignment_ed, p_period_ed));
1083 END LOOP;
1084 CLOSE csr_period_asg_status;
1085
1086 hr_utility.set_location('Leaving '||l_proc, 20);
1087
1088 RETURN l_asg_inactive_days;
1089
1090 END get_asg_inactive_days;
1091 --
1092 /* =====================================================================
1093 Name : get_working_days
1094 Purpose : Gets the number of working days in a given period.
1095 Returns : Number of working days in the period.
1096 ---------------------------------------------------------------------*/
1097 FUNCTION get_working_days
1098 (p_start_date IN DATE,
1099 p_end_date IN DATE) RETURN NUMBER IS
1100
1101 l_proc VARCHAR2(72) := g_package||'get_working_days';
1102 l_working_days NUMBER := 0;
1103 l_curr_date DATE := NULL;
1104 l_curr_day NUMBER := 0;
1105 l_ref_day NUMBER;
1106 l_adj_day NUMBER;
1107 l_diff_days NUMBER;
1108
1109 BEGIN
1110 --
1111 hr_utility.set_location('Entering '||l_proc, 10);
1112
1113 -- Check for a valid range
1114 IF p_start_date > p_end_date THEN
1115 RETURN l_working_days;
1116 END IF;
1117
1118 -- Select the day of week for a date known to be a Saturday.
1119 -- On an instance with NLS_TERRITORY set to AMERICAN, this will
1120 -- return 7; with NLS_TERRITORY set to POLAND, it will return 6.
1121 -- Start of 3222662
1122 l_ref_day := to_number(to_char(to_date('01/01/2000', 'dd/mm/yyyy'), 'D'));
1123 -- End of 3222662
1124
1125 hr_utility.trace('l_ref_day = '||to_char(l_ref_day));
1126
1127 -- A non-zero difference here indicates the week does not begin
1128 -- on Sunday and provides the adjustment we must consider when
1129 -- determining whether or not a day is a work day.
1130 l_diff_days := 7 - l_ref_day;
1131
1132 hr_utility.trace('l_diff_days = '||to_char(l_diff_days));
1133
1134 -- Loop each day in period and count working days
1135
1136 l_curr_date := p_start_date;
1137
1138 LOOP
1139
1140 l_curr_day := to_number(to_char(l_curr_date, 'D'));
1141
1142 hr_utility.trace('l_curr_day = '||to_char(l_curr_day));
1143
1144 -- Find the adjusted day of week
1145 -- Start of 3222662
1146 if mod(l_curr_day+l_diff_days,7) = 0 then
1147 l_adj_day := 7;
1148 else
1149 l_adj_day := mod(l_curr_day+l_diff_days,7);
1150 end if;
1151 -- End of 3222662
1152
1153 hr_utility.trace('l_adj_day = '||to_char(l_adj_day));
1154
1155 IF l_adj_day > 1 AND l_adj_day < 7 THEN
1156 l_working_days := l_working_days + 1;
1157 END IF;
1158 l_curr_date := l_curr_date + 1;
1159 EXIT WHEN l_curr_date > p_end_date;
1160 END LOOP;
1161
1162 hr_utility.set_location('Leaving '||l_proc, 20);
1163
1164 RETURN l_working_days;
1165
1166 END get_working_days;
1167 --
1168 --
1169 end per_accrual_calc_functions;
[Home] [Help]