1 package body per_accrual_calc_functions as
2 /* $Header: peaclcal.pkb 120.6.12010000.3 2008/09/25 05:12:15 nshrikha 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 l_inputs(2).value := fnd_date.date_to_canonical(l_p_calculation_date);
322
323 per_formula_functions.run_formula(p_formula_id => p_co_formula_id,
324 p_calculation_date => p_calculation_date,
325 p_inputs => l_inputs,
326 p_outputs => l_outputs);
327 END IF;
328 CLOSE csr_emp_asg_act;
329
330 -- Code Change for the bug6354665 ends here
331
332 if upper(l_outputs(4).value) = 'NO' then
333 --
334 p_max_carry_over := null;
335 --
336 else
337 --
338 -- Bug fix 4004565
339 -- Fast formula output converted to number using
340 -- fnd_number.canonical_to_number function
341
342 p_max_carry_over := fnd_number.canonical_to_number(l_outputs(1).value);
343 --
344 end if;
345
346 p_effective_date := fnd_date.canonical_to_date(l_outputs(2).value);
347 p_expiry_date := fnd_date.canonical_to_date(l_outputs(3).value);
348
349 hr_utility.set_location('Leaving '||l_proc, 10);
350 --
351 end Get_Carry_Over_Values;
352 --
353 /* =====================================================================
354 Name : Get_Absence
355 Purpose :
356 Returns : Total Absence
357 ---------------------------------------------------------------------*/
358 function Get_Absence
359 (P_Assignment_ID IN Number
360 ,P_Plan_ID IN Number
361 ,P_Calculation_Date IN Date
362 ,P_Start_Date IN Date
363 ,p_absence_attendance_type_id IN Number default NULL
364 ,p_pto_input_value_id IN NUMBER default NULL) return Number is
365
366 l_proc varchar2(72) := g_package||'Get_Absence';
367 l_total_absence number;
368
369 /* NOTE: This cursor has been tuned for the CBO. */
370
371 cursor c_get_total_absence is
372 select nvl(sum(nvl(abs.absence_days, abs.absence_hours)), 0)
373 from per_absence_attendances abs,
374 per_absence_attendance_types abt,
375 pay_net_calculation_rules ncr
376 where abs.absence_attendance_type_id =
377 abt.absence_attendance_type_id
378 and abt.input_value_id = ncr.input_value_id
379 and ((ncr.absence_attendance_type_id is not null
380 and ncr.absence_attendance_type_id =
381 abt.absence_attendance_type_id)
382 OR (ncr.absence_attendance_type_id is null
383 and exists
384 (select 'Y' from pay_accrual_plans
385 where accrual_plan_id = ncr.accrual_plan_id
386 and ncr.input_value_id = pto_input_value_id)
387 ))
388 and exists (select 'Y'
389 from per_all_assignments_f paf
390 where paf.assignment_id = p_assignment_id
391 and paf.person_id = abs.person_id)
392 and abs.date_start between p_start_date and p_calculation_date
393 and ncr.accrual_plan_id = p_plan_id;
394 --
395 cursor c_get_abs_per_type is
396 select nvl(sum(nvl(abs.absence_days, abs.absence_hours)), 0)
397 from per_absence_attendances abs,
398 pay_net_calculation_rules ncr
399 where ncr.absence_attendance_type_id = p_absence_attendance_type_id
400 and ncr.absence_attendance_type_id = abs.absence_attendance_type_id
401 and exists (select 'Y'
402 from per_all_assignments_f paf
403 where paf.assignment_id = p_assignment_id
404 and paf.person_id = abs.person_id)
405 and abs.date_start between p_start_date and p_calculation_date
406 and ncr.accrual_plan_id = p_plan_id;
407 --
408 cursor c_get_abs_per_iv is
409 select nvl(sum(nvl(abs.absence_days, abs.absence_hours)), 0)
410 from per_absence_attendances abs,
411 per_absence_attendance_types abt
412 where abs.absence_attendance_type_id = abt.absence_attendance_type_id
413 and abt.input_value_id = p_pto_input_value_id
414 and exists ( select 1
415 from per_all_assignments_f asg
416 where asg.assignment_id = p_assignment_id
417 and abs.person_id = asg.person_id
418 )
419 and abs.date_start between p_start_date and p_calculation_date;
420 --
421 begin
422 --
423 hr_utility.set_location('Entering '||l_proc, 5);
424 --
425 if p_absence_attendance_type_id is not null then
426 hr_utility.set_location(l_proc, 6);
427 open c_get_abs_per_type;
428 fetch c_get_abs_per_type into l_total_absence;
429 close c_get_abs_per_type;
430 elsif p_pto_input_value_id is not null then
431 hr_utility.set_location(l_proc, 7);
432 open c_get_abs_per_iv;
433 fetch c_get_abs_per_iv into l_total_absence;
434 close c_get_abs_per_iv;
435 else
436 hr_utility.set_location(l_proc, 8);
437 open c_get_total_absence;
438 fetch c_get_total_absence into l_total_absence;
439 close c_get_total_absence;
440 end if;
441 hr_utility.set_location('Leaving '||l_proc, 10);
442
443 return nvl(l_total_absence, 0);
444 --
445 end Get_Absence;
446
447 --
448 /* =====================================================================
449 Name : Get_Other_Net_Contribution
450 Purpose :
451 Returns : Total contribution of other elements.
452 ---------------------------------------------------------------------*/
453 function Get_Other_Net_Contribution
454 (P_Assignment_ID IN Number
455 ,P_Plan_ID IN Number
456 ,P_Calculation_Date IN Date
457 ,P_Start_Date IN Date
458 ,P_Input_Value_ID IN Number default null) return Number is
459
460 l_proc varchar2(72) := g_package||'Get_Other_Net_Contribution';
461 l_contribution number := 0;
462 -- Start of fix 3222662
463 l_limit natural := 100; -- Limiting the bulk collect, if not limited then bulk collect
464 -- returns entire rows for the condition, it may affect memory
465 l_prev_collect number := 0; -- Cumulative record count till previous fetch
466 l_curr_collect number := 0; -- Cumulative record count including the current fetch
467 l_diff_collect number := 0; -- To check that, whether the last fetch retrived any new
468 -- records, if not then to exit from the loop
469 g_amount_entries g_entry_value;
470 g_add_sub_entries g_add_subtract;
471 --
472 cursor c_get_contribution is
473 -- index hint applied for bug 4737028 to avoid bitmap conversion and usage of proper index
474 -- index PAY_ELEMENT_ENTRIES_F_N53 is now used in hint to resolve bug 5677610
475 -- index PAY_INPUT_VALUES_F_N50 is now used in hint to resolve bug 6621800
476
477 select /*+ index(pee PAY_ELEMENT_ENTRIES_F_N53,iv PAY_INPUT_VALUES_F_N50 )*/ fnd_number.canonical_to_number(pev.screen_entry_value) amount,
478 -- Bug 4551666, bug6621800
479 ncr.add_or_subtract add_or_subtract
480 from pay_accrual_plans pap,
481 pay_net_calculation_rules ncr,
482 pay_element_entries_f pee,
483 pay_element_entry_values_f pev,
484 pay_input_values_f iv
485 where pap.accrual_plan_id = p_plan_id
486 and pee.assignment_id = p_assignment_id
487 and pee.element_entry_id = pev.element_entry_id
488 and pev.input_value_id = ncr.input_value_id
489 and pap.accrual_plan_id = ncr.accrual_plan_id
490 and ncr.input_value_id not in
491 (pap.co_input_value_id,pap.pto_input_value_id)
492 and pev.screen_entry_value is not null
493 and ((p_input_value_id is not null and p_input_value_id = ncr.input_value_id)
494 or p_input_value_id is null)
495 and pev.effective_start_date = pee.effective_start_date
496 and pev.effective_end_date = pee.effective_end_date
497 and iv.input_value_id = ncr.input_value_id
498 and p_calculation_date between iv.effective_start_date and iv.effective_end_date
499 and pee.element_type_id = iv.element_type_id
500 and exists
501 (select /*+ index(piv2 PAY_INPUT_VALUES_F_N50)*/ null -- bug6621800
502 from pay_element_entry_values_f pev1,
503 pay_input_values_f piv2
504 where pev1.element_entry_id = pev.element_entry_id
505 and pev1.input_value_id = ncr.date_input_value_id
506 and pev1.effective_start_date = pev.effective_start_date
507 and pev1.effective_end_date = pev.effective_end_date
508 and ncr.date_input_value_id = piv2.input_value_id
509 and pee.element_type_id = piv2.element_type_id
510 and p_calculation_date between piv2.effective_start_date
511 and piv2.effective_end_date
512 and fnd_date.canonical_to_date(decode(substr(piv2.uom, 1, 1),'D',
513 pev1.screen_entry_value, Null))
514 between p_start_date and p_calculation_date);
515 --
516 begin
517 --
518 hr_utility.set_location('Entering '||l_proc, 5);
519 --
520 open c_get_contribution;
521 --
522 loop
523 --
524 fetch c_get_contribution bulk collect into
525 g_amount_entries, g_add_sub_entries limit l_limit;
526 l_prev_collect := l_curr_collect;
527 l_curr_collect := c_get_contribution%rowcount;
528 l_diff_collect := l_curr_collect - l_prev_collect;
529 --
530 if l_diff_collect > 0 then
531 --
532 for i in g_amount_entries.first..g_amount_entries.last loop
533 --
534 l_contribution := l_contribution + (g_amount_entries(i) *
535 g_add_sub_entries(i));
536 --
537 end loop;
538 --
539 end if;
540 --
541 -- Exiting, if the present fetch is NOT returning any new rows
542 exit when (l_diff_collect = 0);
543 --
544 --
545 end loop;
546 --
547 close c_get_contribution;
548 -- End of fix 3222662
549 /*
550 -- Bug 1570965. The below is commented out because we are interested
551 -- in displaying the negative value: it appears on the View Accruals form.
552 --
553 -- If we are dealing with a single net calculation rule,
554 -- we return the absolute value, rather than a potentially
555 -- negative one. We are only interested in the negative value
556 -- when summing all elements together, so that we get an
557 -- accurate result
558
559 if p_input_value_id is not null then
560 l_contribution := abs(l_contribution);
561 end if;
562 */
563 --
564 hr_utility.set_location('Leaving '||l_proc, 10);
565 --
566 return nvl(l_contribution, 0);
567 --
568 --
569 end Get_Other_Net_Contribution;
570 --
571 --
572 /* =====================================================================
573 Name : Get_Carry_Over
574 Purpose :
575 Returns : Total Carry Over amount
576 ---------------------------------------------------------------------*/
577 function Get_Carry_Over
578 (P_Assignment_ID IN Number
579 ,P_Plan_ID IN Number
580 ,P_Calculation_Date IN Date
581 ,P_Start_Date IN Date) return Number is
582
583 l_proc varchar2(72) := g_package||'Get_Carry_Over';
584 l_carryover number := 0;
585 l_absence number := 0;
586 l_net_absence number := 0;
587 -- Bug 4245674 Start
588 l_other number := 0;
589 l_net_other number := 0;
590 l_carry_diff number := 0;
591 -- Bug 4245674 End
592 l_expiry_date date;
593 l_start_date date := p_start_date;
594 l_old_date date;
595 -- Start of fix 3222662
596 l_count number := 1;
597 l_limit natural := 100; -- Limiting the bulk collect, if not limited then bulk collect
598 -- returns entire rows for the condition, it may affect memory
599 l_prev_collect number := 0; -- Cumulative record count till previous fetch
600 l_curr_collect number := 0; -- Cumulative record count including the current fetch
601 l_diff_collect number := 0; -- To check that, whether the last fetch retrived any new
602 -- records, if not then to exit from the loop
603 g_carry_over g_entry_value;
604 g_expiry_date g_effective_date;
605 g_carry_over1 g_entry_value;
606 g_expiry_date1 g_effective_date;
607 --
608 cursor c_get_carryover is
609 -- index hint applied for bug 4737028 to avoid bitmap conversion and usage of proper index
610 select /*+ index(pee PAY_ELEMENT_ENTRIES_F_N53)*/ fnd_number.canonical_to_number(nvl(pev.screen_entry_value, 0)) carryover,
611 fnd_date.canonical_to_date(nvl(pev1.screen_entry_value,
612 p_calculation_date)) expiry_date
613 from pay_accrual_plans pap,
614 pay_element_entry_values_f pev,
615 pay_element_entry_values_f pev1,
616 pay_input_values_f piv,
617 pay_input_values_f piv1,
618 pay_element_entries_f pee
619 where pap.accrual_plan_id = p_plan_id
620 and pee.assignment_id = p_assignment_id
621 and pee.element_entry_id = pev.element_entry_id
622 and pee.element_entry_id = pev1.element_entry_id
623 and pev.input_value_id = pap.co_input_value_id
624 and pev1.input_value_id = pap.co_exp_date_input_value_id
625 and pap.co_input_value_id = piv.input_value_id
626 and pap.co_exp_date_input_value_id = piv1.input_value_id
627 and p_calculation_date between piv.effective_start_date and piv.effective_end_date
628 and p_calculation_date between piv1.effective_start_date and piv1.effective_end_date
629 and pee.element_type_id = piv.element_type_id
630 and pee.element_type_id = piv1.element_type_id
631 and exists
632 (select null
633 from pay_element_entry_values_f pev2,
634 pay_input_values_f piv2
635 where pev2.element_entry_id = pev.element_entry_id
636 and pev2.input_value_id = pap.co_date_input_value_id
637 and pev2.input_value_id = piv2.input_value_id
638 and pev2.effective_start_date = pev.effective_start_date
639 and pev2.effective_end_date = pev.effective_end_date
640 and pap.co_date_input_value_id = piv2.input_value_id
641 and pee.element_type_id = piv2.element_type_id
642 and p_calculation_date between piv2.effective_start_date
643 and piv2.effective_end_date
644 and fnd_date.canonical_to_date(decode(substr(piv2.uom, 1, 1),'D',
645 pev2.screen_entry_value, Null)) <=
646 fnd_date.canonical_to_date(nvl(pev1.screen_entry_value,p_calculation_date))
647 and fnd_date.canonical_to_date(decode(substr(piv2.uom, 1, 1),'D',
648 pev2.screen_entry_value, Null))
649 between p_start_date and p_calculation_date)
650 order by expiry_date;
651 --
652 begin
653 --
654 hr_utility.set_location('Entering '||l_proc, 5);
655 --
656 -- Getting entire records into a PL/SQL table
657 --
658 open c_get_carryover;
659 --
660 loop
661 --
662 fetch c_get_carryover bulk collect into g_carry_over1, g_expiry_date1 limit l_limit;
663 --
664 l_prev_collect := l_curr_collect;
665 l_curr_collect := c_get_carryover%rowcount;
666 l_diff_collect := l_curr_collect - l_prev_collect;
667 --
668 if l_diff_collect > 0 then
669 --
670 for i in g_carry_over1.first..g_carry_over1.last loop
671 -- Setting the index
672 l_count := g_carry_over.count + 1;
673 -- Keeping the cumulated records to a seperate PL/SQL tables
674 g_carry_over(l_count) := g_carry_over1(i);
675 g_expiry_date(l_count) := g_expiry_date1(i);
676 end loop;
677 --
678 end if;
679 --
680 -- Exiting, if the present fetch is NOT returning any new rows
681 exit when (l_diff_collect = 0);
682 --
683 end loop;
684 --
685 close c_get_carryover;
686 --
687 --
688 -- Bug 4245674 Start
689 -- Desc : Modified this function to incorporate the Net Calculation rules
690 -- while calculating the net calculation rules.
691 -- Added comments throughout the fuction to explain each and every
692 -- actions.
693 l_old_date := p_start_date - 1;
694 --
695 if g_carry_over.count > 0 then
696 --
697 -- Because although you normally only have one carry over element entry,
698 -- users can manually add additional carry over entries in by hand.
699 for i in g_carry_over.first..g_carry_over.last loop
700 --
701 -- If you have more than one carry over entry and its expiry date is
702 -- later than the previous element entry, you need to sum any additional
703 -- absences and other contribution between the the first expiry period
704 -- and the second and add them to the total.
705 if g_expiry_date(i) > l_old_date then
706 --
707 l_start_date := l_old_date + 1;
708 --
709 l_absence := get_absence(
710 p_assignment_id => p_assignment_id,
711 p_plan_id => p_plan_id,
712 p_start_date => l_start_date,
713 p_calculation_date => g_expiry_date(i));
714
715 l_absence := l_absence + l_net_absence;
716
717 l_other := get_other_net_contribution(
718 p_assignment_id => p_assignment_id,
719 p_plan_id => p_plan_id,
720 p_start_date => l_start_date,
721 p_calculation_date => g_expiry_date(i));
722 l_other := l_other + l_net_other;
723 --
724 else
725 --
726 l_absence := l_net_absence;
727 l_other := l_net_other;
728 --
729 end if;
730 --
731 if p_calculation_date <= g_expiry_date(i) then
732 --
733 l_net_absence := l_absence;
734 l_carryover := l_carryover + g_carry_over(i);
735 l_net_other := l_other;
736 --
737 -- When the carryover has already expired, we showing the carryover amount
738 -- to be the amount of absences and other contribution - ie the amount
739 -- not forfeited.
740 else
741 --
742 -- This is for absence to be calculated.
743 if g_carry_over(i) <= l_absence then
744 --
745 l_net_absence := l_absence - g_carry_over(i);
746 l_carryover := l_carryover + g_carry_over(i);
747 l_carry_diff := 0;
748 --
749 else
750 --
751 l_net_absence := 0;
752 l_carryover := l_carryover + l_absence;
753 l_carry_diff := g_carry_over(i) - l_absence;
754 --
755 end if;
756 --
757 -- This is for Net Calculation rules to be calculated.
758 if l_other > 0 then
759 l_net_other := l_other;
760 else
761
762 if l_carry_diff <= abs(l_other) then
763 -- Remaining other is add into net_other.
764 l_net_other := l_other + l_carry_diff;
765 -- Adding the carryover in the absence and remaining carryover if the
766 -- net carryover calculated after absence deduction is less than the
767 -- net carryover.
768 l_carryover := l_carryover + l_carry_diff;
769
770 else
771 -- Utilized all the other. If some other remains then add to net
772 l_net_other := 0;
773 -- Case 1: when the other element is -ve(Subtract), Adding the carryover
774 -- and -ve of other if the net carryover is greater than other.
775 --
776 -- Case 2: when the other element is +ve (Add), If the carryover due to
777 -- absence is less than the other then what needs to be done.
778 -- If the carryover due to absence is more than the other then net
779 -- carryover is carryover_absence - other
780 l_carryover := l_carryover - l_other;
781 end if;
782
783 end if;
784
785 end if;
786 --
787 l_old_date := g_expiry_date(i);
788 --
789 end loop;
790 --
791 end if;
792 --
793 hr_utility.set_location('Leaving '||l_proc, 10);
794 --
795 return l_carryover;
796 --
797 -- End of fix 3222662
798 -- Bug 4245674 Ends.
799 --
800 end Get_Carry_Over;
801 --
802 --
803 /* =====================================================================
804 Name : Get_Net_Accrual
805 Purpose :
806 Returns : Total Accrued entitlement
807 ---------------------------------------------------------------------*/
808 procedure Get_Net_Accrual
809 (P_Assignment_ID IN Number
810 ,P_Plan_ID IN Number
811 ,P_Payroll_ID IN Number
812 ,P_Business_Group_ID IN Number
813 ,P_Assignment_Action_ID IN Number default -1
814 ,P_Calculation_Date IN Date
815 ,P_Accrual_Start_Date IN Date default null
816 ,P_Accrual_Latest_Balance IN Number default null
817 ,P_Calling_Point IN Varchar2 default 'FRM'
818 ,P_Start_Date OUT NOCOPY Date
819 ,P_End_Date OUT NOCOPY Date
820 ,P_Accrual_End_Date OUT NOCOPY Date
821 ,P_Accrual OUT NOCOPY Number
822 ,P_Net_Entitlement OUT NOCOPY Number) is
823
824 l_proc varchar2(72) := g_package||'Get_Net_Accrual';
825 l_absence number;
826 l_accrual number;
827 l_other number;
828 l_carryover number;
829 l_start_date date;
830 l_end_date date;
831 l_accrual_end_date date;
832 l_defined_balance_id number;
833
834 cursor c_get_balance is
835 select defined_balance_id
836 from pay_accrual_plans
837 where accrual_plan_id = p_plan_id;
838
839 begin
840 --
841 hr_utility.set_location('Entering '||l_proc, 5);
842
843 --
844 -- Pipe the parameters for ease of debugging.
845 --
846 hr_utility.trace(' ');
847 hr_utility.trace(' --------------------------------'||
848 '---------------------------------');
849 hr_utility.trace(' ENTERING '||upper(l_proc));
850 hr_utility.trace(' for assignment '||to_char(p_assignment_id));
851 hr_utility.trace(' --------------------------------'||
852 '+--------------------------------');
853 hr_utility.trace(' p_assignment_id '||
854 to_char(p_assignment_id));
855 hr_utility.trace(' p_plan_id '||
856 to_char(p_plan_id));
857 hr_utility.trace(' p_payroll_id '||
858 to_char(p_payroll_id));
859 hr_utility.trace(' p_business_group_id '||
860 to_char(p_business_group_id));
861 hr_utility.trace(' p_assignment_action_id '||
862 to_char(p_assignment_action_id));
863 hr_utility.trace(' p_calculation_date '||
864 to_char(p_calculation_date));
865 hr_utility.trace(' p_accrual_start_date '||
866 to_char(p_accrual_start_date));
867 hr_utility.trace(' p_accrual_latest_balance '||
868 to_char(p_accrual_latest_balance));
869 hr_utility.trace(' p_calling_point '||
870 p_calling_point);
871 hr_utility.trace(' --------------------------------'||
872 '---------------------------------');
873 hr_utility.trace(' ');
874
875 open c_get_balance;
876 fetch c_get_balance into l_defined_balance_id;
877 close c_get_balance;
878
879 if p_calling_point = 'BP' and
880 l_defined_balance_id is not null and
881 p_assignment_action_id <> -1 then
882 --
883 /* Procedure called from batch process, so
884 get latest balance. */
885
886 p_net_entitlement := pay_balance_pkg.get_value(
887 p_defined_balance_id => l_defined_balance_id
888 ,p_assignment_action_id => p_assignment_action_id
889 );
890 --
891 else
892 --
893
894 get_accrual(p_assignment_id => p_assignment_id,
895 p_plan_id => p_plan_id,
896 p_calculation_date => p_calculation_date,
897 p_business_group_id => p_business_group_id,
898 p_payroll_id => p_payroll_id,
899 p_assignment_action_id => p_assignment_action_id,
900 p_accrual_start_date => p_accrual_start_date,
901 p_accrual_latest_balance => p_accrual_latest_balance,
902 p_start_date => l_start_date,
903 p_end_date => l_end_date,
904 p_accrual_end_date => l_accrual_end_date,
905 p_accrual => l_accrual);
906
907 l_absence := get_absence(p_assignment_id => p_assignment_id,
908 p_plan_id => p_plan_id,
909 p_start_date => l_start_date,
910 p_calculation_date => l_end_date);
911
912 l_other := get_other_net_contribution(
913 p_assignment_id => p_assignment_id,
914 p_plan_id => p_plan_id,
915 p_start_date => l_start_date,
916 p_calculation_date => l_end_date
917 );
918
919 l_carryover := get_carry_over(
920 p_assignment_id => p_assignment_id,
921 p_plan_id => p_plan_id,
922 p_start_date => l_start_date,
923 p_calculation_date => l_end_date);
924
925 --
926 -- Set up values in the return parameters.
927 --
928 p_net_entitlement := l_accrual - l_absence + l_other + l_carryover;
929 p_accrual := l_accrual;
930 p_start_date := l_start_date;
931 p_end_date := l_end_date;
932 p_accrual_end_date := l_accrual_end_date;
933 --
934 end if;
935
936 --
937 -- Pipe the parameters for ease of debugging.
938 --
939 hr_utility.trace(' ');
940 hr_utility.trace(' --------------------------------'||
941 '---------------------------------');
942 hr_utility.trace(' LEAVING '||upper(l_proc));
943 hr_utility.trace(' --------------------------------'||
944 '+--------------------------------');
945 hr_utility.trace(' p_start_date '||
946 to_char(p_start_date));
947 hr_utility.trace(' p_end_date '||
948 to_char(p_end_date));
949 hr_utility.trace(' p_accrual_end_date '||
950 to_char(p_accrual_end_date));
951 hr_utility.trace(' p_accrual '||
952 to_char(p_accrual));
953 hr_utility.trace(' p_net_entitlement '||
954 to_char(p_net_entitlement));
955 hr_utility.trace(' --------------------------------'||
956 '---------------------------------');
957 hr_utility.trace(' ');
958
959 --
960 end Get_Net_Accrual;
961 --
962 /* =====================================================================
963 Name : get_asg_inactive_days
964 Purpose : Gets the number of days in a period where the assignment
965 status is not 'Active'.
966 Returns : Number of inactive days in the period.
967 ---------------------------------------------------------------------*/
968 FUNCTION get_asg_inactive_days
969 (p_assignment_id IN NUMBER,
970 p_period_sd IN DATE,
971 p_period_ed IN DATE) RETURN NUMBER IS
972
973 CURSOR csr_period_asg_status IS
974 SELECT asg.effective_start_date,
975 asg.effective_end_date,
976 ast.per_system_status
977 FROM per_all_assignments_f asg,
978 per_assignment_status_types ast
979 WHERE asg.assignment_id = p_assignment_id
980 AND ((asg.effective_start_date BETWEEN p_period_sd AND p_period_ed
981 OR asg.effective_end_date BETWEEN p_period_sd AND p_period_ed)
982 OR (p_period_sd BETWEEN asg.effective_start_date AND asg.effective_end_date))
983 AND asg.assignment_status_type_id = ast.assignment_status_type_id
984 AND ast.per_system_status <> 'ACTIVE_ASSIGN';
985
986 l_proc VARCHAR2(72) := g_package||'get_loa_days';
987 l_assignment_sd DATE;
988 l_assignment_ed DATE;
989 l_assignment_status per_assignment_status_types.per_system_status%TYPE;
990 l_asg_inactive_days NUMBER := 0;
991
992 BEGIN
993 --
994 hr_utility.set_location('Entering '||l_proc, 10);
995
996 -- Loop each inactive assignment record for the period and count the inactive days
997 OPEN csr_period_asg_status;
998 LOOP
999 FETCH csr_period_asg_status INTO
1000 l_assignment_sd,
1001 l_assignment_ed,
1002 l_assignment_status;
1003 EXIT WHEN csr_period_asg_status%NOTFOUND;
1004 -- Count inactive days
1005 l_asg_inactive_days := l_asg_inactive_days + get_working_days(
1006 GREATEST(l_assignment_sd, p_period_sd),
1007 LEAST(l_assignment_ed, p_period_ed));
1008 END LOOP;
1009 CLOSE csr_period_asg_status;
1010
1011 hr_utility.set_location('Leaving '||l_proc, 20);
1012
1013 RETURN l_asg_inactive_days;
1014
1015 END get_asg_inactive_days;
1016 --
1017 /* =====================================================================
1018 Name : get_working_days
1019 Purpose : Gets the number of working days in a given period.
1020 Returns : Number of working days in the period.
1021 ---------------------------------------------------------------------*/
1022 FUNCTION get_working_days
1023 (p_start_date IN DATE,
1024 p_end_date IN DATE) RETURN NUMBER IS
1025
1026 l_proc VARCHAR2(72) := g_package||'get_working_days';
1027 l_working_days NUMBER := 0;
1028 l_curr_date DATE := NULL;
1029 l_curr_day NUMBER := 0;
1030 l_ref_day NUMBER;
1031 l_adj_day NUMBER;
1032 l_diff_days NUMBER;
1033
1034 BEGIN
1035 --
1036 hr_utility.set_location('Entering '||l_proc, 10);
1037
1038 -- Check for a valid range
1039 IF p_start_date > p_end_date THEN
1040 RETURN l_working_days;
1041 END IF;
1042
1043 -- Select the day of week for a date known to be a Saturday.
1044 -- On an instance with NLS_TERRITORY set to AMERICAN, this will
1045 -- return 7; with NLS_TERRITORY set to POLAND, it will return 6.
1046 -- Start of 3222662
1047 l_ref_day := to_number(to_char(to_date('01/01/2000', 'dd/mm/yyyy'), 'D'));
1048 -- End of 3222662
1049
1050 hr_utility.trace('l_ref_day = '||to_char(l_ref_day));
1051
1052 -- A non-zero difference here indicates the week does not begin
1053 -- on Sunday and provides the adjustment we must consider when
1054 -- determining whether or not a day is a work day.
1055 l_diff_days := 7 - l_ref_day;
1056
1057 hr_utility.trace('l_diff_days = '||to_char(l_diff_days));
1058
1059 -- Loop each day in period and count working days
1060
1061 l_curr_date := p_start_date;
1062
1063 LOOP
1064
1065 l_curr_day := to_number(to_char(l_curr_date, 'D'));
1066
1067 hr_utility.trace('l_curr_day = '||to_char(l_curr_day));
1068
1069 -- Find the adjusted day of week
1070 -- Start of 3222662
1071 if mod(l_curr_day+l_diff_days,7) = 0 then
1072 l_adj_day := 7;
1073 else
1074 l_adj_day := mod(l_curr_day+l_diff_days,7);
1075 end if;
1076 -- End of 3222662
1077
1078 hr_utility.trace('l_adj_day = '||to_char(l_adj_day));
1079
1080 IF l_adj_day > 1 AND l_adj_day < 7 THEN
1081 l_working_days := l_working_days + 1;
1082 END IF;
1083 l_curr_date := l_curr_date + 1;
1084 EXIT WHEN l_curr_date > p_end_date;
1085 END LOOP;
1086
1087 hr_utility.set_location('Leaving '||l_proc, 20);
1088
1089 RETURN l_working_days;
1090
1091 END get_working_days;
1092 --
1093 --
1094 end per_accrual_calc_functions;
[Home] [Help]