1 package body pay_nz_holidays_2003 as
2 -- $Header: pynzhl2003.pkb 120.2.12010000.3 2008/09/12 12:20:38 lnagaraj ship $
3 --
4 -- Copyright (C) 1999 Oracle Corporation
5 -- All Rights Reserved
6 --
7 -- Change List
8 -- ===========
9 --
10 -- Date Author Reference Description
11 -- -----------+-----------+---------+--------------------------------------------
12 -- 03 FEB 2003 sclarke 3417767 Adjustments now taken off entitlement before accrual
13 -- 19 NOV 2003 sclarke 3064179 Created
14 -- 04 DEC 2003 sclarke Updates after testing
15 -- 30 DEC 2003 sclarke Changed parameters to annual_leave_calc_1
16 -- 08 JAN 2004 sclarke Removed tables of records
17 -- 28 JAN 2004 sclarke 3392071 changed get_accrual_entitlement logic regarding
18 -- adjustment elements
19 -- 11 FEB 2004 sclarke 3435633 get_working_days_balance cursor changed
20 -- so that the max ass action fetches
21 -- the currently processing action
22 -- 12 FEB 2004 sclarke 3435633 Need to handle when viewing accruals
23 -- for actual time worked and there are no runs
24 -- in period. Better error handling required.
25 -- 12 Mar 2004 sclarke 3547116 Recurring absences
26 -- 02 APR 2004 sclarke 3541500 Anniversary Date is not moved the initial 7 days
27 -- 15 APR 2004 sclarke 3541500 Do not accrue leave on the initial 7 days
28 -- 11 MAY 2004 puchil 3592923 Performance fix for cursor csr_ass_action.
29 -- 3620398 1) Changed function get_working_days_balance
30 -- 2) Added return statement to function get_previous_rate
31 -- 13 MAY 2004 statkar 3620398 Reverted back the changes to
32 -- csr_ass_action done in the previous version
33 -- 21 MAY 2004 sclarke 3632528 added extra action_status to get_working_days_balance
34 -- 11 JUN 2004 puchil 3654766 Changed the cursor csr_get_rate to return the value
35 -- based on the assignment_action_id.
36 -- 07 JUL 2004 puchil 3654766 Changed the cursor csr_get_rate to consider
37 -- leaves spanning multiple pay periods.
38 -- 23 JUL 2004 bramajey 3608752 Added functions is_parental_leave_taken,get_entitled_amount
39 -- ,get_recur_abs_prev_period and get_leave_taken
40 -- 17 AUG 2004 bramajey 3608752 Modified cursor csr_parental_leave_taken
41 -- and csr_get_count_leave.
42 -- 05 AUG 2005 rpalli 4536217 As part of bug 4536217(performance issue):
43 -- Added overloaded function determine_work_week.
44 -- Added is_leap_year function.
45 -- Removed eligible_for_accrual function.
46 -- Modified calculate_daily_accrual function.
47 -- Modified daily_accrual_loop function.
48 -- 02 SEP 2005 snekkala 4259438 Modified cursor csr_hire_date as Part of Performance
49 -- Modified csr_ass_action in get_working_days_balance
50 -- for performance
51 -- 21 JUL 2008 vamittal 7254820 Modified function annual_leave_rate_calc_2 and get_annual_leave_percentage.
52 -- 30-Jul-2008 avenkatk 7260523 Modified function annual_leave_rate_calc_1 - condition for months_between
53 -- ------------------------------------------------------------------------------
54 --
55 g_package constant varchar2(60) := 'pay_nz_holidays_2003.';
56 g_debug boolean;
57 g_legislation_code constant varchar2(3) := 'NZ';
58 g_unpaid_absence_category constant varchar2(4) := 'NZUL';
59 --
60 -- An absence is taken given a start and end date with the two days the given
61 -- dates fall on being included in the absence taken. Unpaid absences greater
62 -- than 1 week move the anniversary date. To determine the 1 weeks duration
63 -- we are subtracting the start_date from the end_date of the absence and moving
64 -- the anniversary when the difference is > than 6 days (Note, not 7 days).
65 --
66 g_unpaid_absence_days constant number := 6;
67 --
68 type t_person_rec2 is record
69 (person_id number
70 ,calculation_date date
71 ,anniversary_start_date date
72 ,anniversary_end_date date
73 ,years_of_service number
74 );
75 type t_person_tab2 is table of t_person_rec2 index by binary_integer;
76
77 p_anniversary_table2 t_person_tab2;
78 --
79 -- Cursor to retrieve days where the assignment
80 -- is not active
81 --
82 cursor csr_inactive_days
83 (p_assignment_id number
84 ,p_period_sd date
85 ,p_period_ed date
86 ) is
87 -- need to add 1 as to include the finale day
88 -- e.g. 01-JAN-2000 minus 01-JAN-2000 is actually 1 day but the arithmetic returns 0
89 select sum(asg.effective_end_date - asg.effective_start_date + 1) days_inactive
90 from per_assignments_f asg
91 , per_assignment_status_types ast
92 where asg.assignment_id = p_assignment_id
93 and ((asg.effective_start_date between p_period_sd and p_period_ed
94 or asg.effective_end_date between p_period_sd and p_period_ed)
95 or (p_period_sd between asg.effective_start_date and asg.effective_end_date))
96 and asg.assignment_status_type_id = ast.assignment_status_type_id
97 and ast.per_system_status <> 'ACTIVE_ASSIGN';
98 --
99 cursor csr_person_id
100 (p_assignment_id number
101 ,p_calculation_date date
102 ) is
103 select person_id
104 from per_assignments_f
105 where assignment_id = p_assignment_id
106 and p_calculation_date between effective_start_date and effective_end_date;
107 --
108 -- Cursor to retrieve the input value of
109 -- element entry for LEAVE_INFORMATION -> STANDARD WORK WEEK
110 --
111 cursor csr_work_week
112 (p_assignment_id number
113 ,p_effective_date date
114 ) is
115 select nvl(to_number(val.screen_entry_value),0)
116 from pay_element_entries_f ent
117 , pay_element_types_f el
118 , pay_input_values_f piv
119 , pay_element_entry_values_f val
120 , pay_element_links_f link
121 where ent.assignment_id = p_assignment_id
122 and ent.element_entry_id = val.element_entry_id
123 and p_effective_date between ent.effective_start_date and ent.effective_end_date
124 and ent.element_link_id = link.element_link_id
125 and link.element_type_id = el.element_type_id
126 and el.element_name = 'Leave Information'
127 and el.legislation_code = g_legislation_code
128 and p_effective_date between el.effective_start_date and el.effective_end_date
129 and el.element_type_id = piv.element_type_id
130 and piv.name = 'Standard Work Week'
131 and p_effective_date between piv.effective_start_date and piv.effective_end_date
132 and val.input_value_id = piv.input_value_id
133 and p_effective_date between val.effective_start_date and val.effective_end_date;
134 --
135 -- Cursor to retrieve the input value of
136 -- element entry for Leave Information -> USE ASSIGNMENT WORK HOURS
137 --
138 cursor csr_use_asg_hours
139 (p_assignment_id number
140 ,p_effective_date date
141 ) is
142 select nvl(val.screen_entry_value,'Y')
143 from pay_element_entries_f ent
144 , pay_element_types_f el
145 , pay_input_values_f piv
146 , pay_element_entry_values_f val
147 , pay_element_links_f link
148 where ent.assignment_id = p_assignment_id
149 and ent.element_entry_id = val.element_entry_id
150 and p_effective_date between ent.effective_start_date and ent.effective_end_date
151 and ent.element_link_id = link.element_link_id
152 and link.element_type_id = el.element_type_id
153 and el.element_name = 'Leave Information'
154 and el.legislation_code = g_legislation_code
155 and p_effective_date between el.effective_start_date and el.effective_end_date
156 and el.element_type_id = piv.element_type_id
157 and piv.name = 'Use Assignment Working Hours'
158 and p_effective_date between piv.effective_start_date and piv.effective_end_date
159 and val.input_value_id = piv.input_value_id
160 and p_effective_date between val.effective_start_date and val.effective_end_date;
161
162 --
163 -- Comparison on dates uses the > 6
164 -- as an alternative to => 7
165 --
166 cursor csr_get_unpaid_absences
167 (p_person_id per_all_people_f.person_id%type
168 ,p_start_date date
169 ,p_end_date date
170 ) is
171 select ab.absence_attendance_id
172 , ab.person_id
173 , ab.absence_days
174 , ab.absence_hours
175 , abt.hours_or_days
176 , ab.date_start
177 , ab.date_end
178 , ((ab.date_end - ab.date_start) - g_unpaid_absence_days) add_days
179 -- however, do not move the initial qualifying period
180 from per_absence_attendances ab
181 , per_absence_attendance_types abt
182 where ab.absence_attendance_type_id = abt.absence_attendance_type_id
183 and ab.person_id = p_person_id
184 and abt.absence_category = g_unpaid_absence_category
185 and ab.date_start between p_start_date and p_end_date
186 and ((ab.date_end - ab.date_start) > g_unpaid_absence_days);
187 --
188 -----------------------------
189 -- GET_ACCRUAL_PLAN_UOM
190 -----------------------------
191 function get_accrual_plan_uom
192 (p_accrual_plan_id number
193 ) return varchar2 is
194 --
195 l_uom varchar2(60);
196 --
197 cursor csr_get_uom
198 (p_accrual_plan_id number
199 ) is
200 select accrual_units_of_measure
201 from pay_accrual_plans
202 where accrual_plan_id = p_accrual_plan_id;
203 --
204 begin
205 open csr_get_uom(p_accrual_plan_id);
206 fetch csr_get_uom into l_uom;
207 close csr_get_uom;
208 --
209 return l_uom;
210 end get_accrual_plan_uom;
211
212 ---------------------------
213 -- GET_WORKING_DAYS_BALANCE
214 ---------------------------
215 --
216 function get_working_days_balance
217 (p_assignment_id in number
218 ,p_effective_date in date
219 ) return number is
220
221 l_balance_name constant varchar2(100) := 'Days or Hours Worked';
222 l_dimension_name constant varchar2(100) := '_ASG_PTD';
223 l_value number;
224 l_defined_balance_id number;
225 l_procedure constant varchar2(100) := g_package||'get_working_days_balance';
226
227 cursor csr_def_bal
228 (p_balance_name varchar2
229 ,p_dimension_name varchar2
230 )is
231 select defined_balance_id
232 from pay_defined_balances pdb
233 , pay_balance_types pbt
234 , pay_balance_dimensions dim
235 where pdb.balance_type_id = pbt.balance_type_id
236 and pdb.balance_dimension_id = dim.balance_dimension_id
237 and pbt.balance_name = p_balance_name
238 and dim.dimension_name = p_dimension_name
239 and dim.legislation_code = g_legislation_code
240 and pbt.legislation_code = g_legislation_code;
241
242 /* Bug 4259438 : Modified cursor as part of performance */
243 CURSOR csr_ass_action
244 (p_assignment_id NUMBER
245 ,p_effective_date DATE
246 ) IS
247 SELECT MAX(paa.assignment_action_id)
248 FROM pay_assignment_actions paa
249 , per_assignments_f paf
250 , pay_payrolls_f ppf
251 , pay_payroll_actions ppa
252 , per_time_periods ptp
253 WHERE paf.assignment_id = p_assignment_id
254 AND ppa.action_type in ('R','Q')
255 AND p_effective_date BETWEEN paf.effective_start_date
256 AND paf.effective_end_date
257 AND ppa.payroll_action_id = paa.payroll_action_id
258 AND ppf.payroll_id = paf.payroll_id
259 AND ppa.time_period_id = ptp.time_period_id
260 AND ppf.payroll_id = ppa.payroll_id
261 AND ppa.effective_date BETWEEN ptp.start_date
262 AND ptp.end_date
263 AND p_effective_date BETWEEN ptp.start_date
264 AND ptp.end_date
265 AND ppf.payroll_id = ppa.payroll_id
266 AND ppf.payroll_id = ptp.payroll_id
267 AND paf.assignment_id = paa.assignment_id
268 AND paa.action_status IN ('C','P','U')
269 AND ppa.action_status IN ('C','P','U')
270 GROUP BY paa.assignment_action_id
271 HAVING paa.assignment_action_id = MAX(paa.assignment_action_id);
272
273 --
274 l_assignment_action_id number;
275 begin
276 --
277 g_debug := hr_utility.debug_enabled;
278 --
279 if g_debug then
280 hr_utility.set_location(l_procedure, 0);
281 end if;
282 --
283 open csr_ass_action(p_assignment_id, p_effective_date);
284 fetch csr_ass_action into l_assignment_action_id;
285 close csr_ass_action;
286 --
287 if g_debug then
288 hr_utility.set_location(l_procedure, 10);
289 hr_utility.trace('l_assignment_action_id = *'||to_char(nvl(l_assignment_action_id,99))||'*');
290 end if;
291 --
292 if l_assignment_action_id is null then
293 hr_utility.set_message(801,'HR_NZ_WORKING_HRS_MISSING');
294 hr_utility.raise_error;
295 end if;
296 --
297 open csr_def_bal(l_balance_name, l_dimension_name);
298 fetch csr_def_bal into l_defined_balance_id;
299 if csr_def_bal%notfound then
300 if g_debug then
301 hr_utility.set_location(l_procedure, 7);
302 end if;
303 --
304 l_value := 0;
305 else
306 --
307 l_value := pay_balance_pkg.get_value
308 (p_defined_balance_id =>l_defined_balance_id
309 ,p_assignment_action_id => l_assignment_action_id
310 );
311 end if;
312 close csr_def_bal;
313 --
314 --
315 ----
316 --l_value := pay_balance_pkg.get_value
317 --(p_defined_balance_id => l_defined_balance_id
318 --,p_assignment_id => p_assignment_id
319 --,p_virtual_date => p_effective_date
320 --);
321 --
322 if g_debug then
323 hr_utility.set_location(l_procedure,999);
324 end if;
325 --
326 return l_value;
327 end get_working_days_balance;
328 ---------------------------
329 -- GET_BALANCE
330 ---------------------------
331 --
332 function get_balance
333 (p_assignment_id in number
334 ,p_effective_date in date
335 ,p_balance_name varchar2
336 ,p_dimension_name varchar2
337 ) return number is
338
339 l_value number;
340 l_defined_balance_id number;
341 l_procedure constant varchar2(100) := g_package||'get_balance';
342
343 cursor csr_def_bal
344 (p_balance_name varchar2
345 ,p_dimension_name varchar2
346 )is
347 select defined_balance_id
348 from pay_defined_balances pdb
349 , pay_balance_types pbt
350 , pay_balance_dimensions dim
351 where pdb.balance_type_id = pbt.balance_type_id
352 and pdb.balance_dimension_id = dim.balance_dimension_id
353 and pbt.balance_name = p_balance_name
354 and dim.dimension_name = p_dimension_name
355 and dim.legislation_code = g_legislation_code
356 and pbt.legislation_code = g_legislation_code;
360 fetch csr_def_bal into l_defined_balance_id;
357 begin
358 --
359 open csr_def_bal(p_balance_name, p_dimension_name);
361 close csr_def_bal;
362 --
363 l_value := nvl(pay_balance_pkg.get_value
364 (p_defined_balance_id => l_defined_balance_id
365 ,p_assignment_id => p_assignment_id
366 ,p_virtual_date => p_effective_date
367 ),0);
368 --
369 return l_value;
370 end get_balance;
371 --
372 --------------------
373 -- GET_STANDARD_WORK_WEEK
374 --------------------
375 --
376 function get_standard_work_week
377 (p_assignment_id in number
378 ,p_effective_date in date
379 ) return number is
380 l_procedure constant varchar2(100) := g_package||'get_standard_week';
381 l_standard_days_per_week number;
382 begin
383 --
384 open csr_work_week(p_assignment_id, p_effective_date);
385 fetch csr_work_week
386 into l_standard_days_per_week;
387 if csr_work_week%notfound then
388 l_standard_days_per_week := 0;
389 end if;
390 close csr_work_week;
391 --
392 return l_standard_days_per_week;
393 end get_standard_work_week;
394 --
395 -------------------
396 -- MOVE_ANNIVERSARY
397 -------------------
398 --
399 function move_anniversary
400 (p_person_id number
401 ,p_calculation_date date
402 ,p_start_date in out nocopy date
403 ,p_end_date in out nocopy date
404 ) return date is
405 --
406 g_absences_found boolean;
407 l_tmp_anniversary_end_date date;
408 l_procedure constant varchar2(60) := g_package||'move anniversary';
409 l_date_moved boolean;
410 --
411 begin
412 --
413 g_debug := hr_utility.debug_enabled;
414 --
415 l_tmp_anniversary_end_date := p_end_date;
416 if g_debug then
417 hr_utility.set_location(l_procedure, 0);
418 end if;
419 --
420 if g_debug then
421 hr_utility.set_location('Entering '||l_procedure,1);
422 hr_utility.set_location('p_start_date '||to_char(p_start_date,'DD-MON-YYYY'),1);
423 hr_utility.set_location('p_end_date '||to_char(p_end_date,'DD-MON-YYYY'),1);
424 end if;
425 --
426 -- Check for more absence unless we have gone beyond the calculation date
427 while not l_tmp_anniversary_end_date > p_end_date
428 loop
429 if g_debug then
430 hr_utility.set_location(l_procedure,2);
431 end if;
432 -- initialise variable to false before we actually check absences
433 g_absences_found := false;
434 --
435 -- now check for absences
436 for abs_rec in csr_get_unpaid_absences(p_person_id, p_start_date, p_end_date)
437 loop
438 -- add the number of days the absence was taken over
439 g_absences_found := true;
440 l_tmp_anniversary_end_date := l_tmp_anniversary_end_date + abs_rec.add_days;
441 end loop;
442
446 if g_debug then
443 if g_absences_found then
444 l_tmp_anniversary_end_date := move_anniversary(p_person_id, p_calculation_date, p_end_date, l_tmp_anniversary_end_date);
445 else
447 hr_utility.set_location('no absences',1);
448 end if;
449 --
450 -- exit when absence not found
451 exit;
452 end if;
453 end loop;
454 --
455 if g_debug then
456 hr_utility.set_location(l_procedure,999);
457 end if;
458 --
459 return l_tmp_anniversary_end_date;
460 end move_anniversary;
461 --
462 ---------------------------
463 -- INITIALISE_ANNIVERSARIES
464 ---------------------------
465 -- Sets the first anniversary start date for an employee
466 -- If p_service_date is not entered then the first anniversary start date
467 -- will be set to the hire date of the employee...
468 -- p_service_date is provided so continuous_service_date can be considered
469 -- it is expected that this parameter has already been validated.
470 --
471 procedure initialise_dates
472 (p_assignment_id in number
473 ,p_service_start_date in out nocopy date
474 ,p_anniversary_start_date out nocopy date
475 ) is
476 l_procedure constant varchar2(60) := g_package||'initialise_anniversaries';
477 l_anniversary_start_date date;
478 --
479 -- Bug 4259438 : Modified the Select clause of the cursor as part of Performance
480 --
481 CURSOR csr_hire_date
482 IS
483 SELECT date_start
484 FROM per_periods_of_service pps
485 , per_assignments_f paf
486 WHERE pps.period_of_service_id = paf.period_of_service_id
487 AND pps.person_id = paf.person_id
488 AND paf.assignment_id = p_assignment_id;
489 --
490 -- End Bug 4259438
491 --
492 begin
493 g_debug := hr_utility.debug_enabled;
494 if g_debug then
495 hr_utility.set_location(l_procedure, 0);
496 hr_utility.trace('..p_assignment_id = '||to_char(nvl(p_assignment_id,0)));
497 hr_utility.trace('..p_service_start_date = '||to_char(p_service_start_date,'DD/MM/RRRR'));
498 end if;
499 --
500 -- If the service start date is entered
501 -- then we assume validation of dates has
502 -- already been done.
503 --
504 if p_service_start_date is null then
505 --
506 -- get hire_date and termination_date for future use
507 open csr_hire_date;
508 fetch csr_hire_date into l_anniversary_start_date;
509 close csr_hire_date;
510 --
511 p_service_start_date := l_anniversary_start_date;
512 else
513 l_anniversary_start_date := p_service_start_date;
514 end if;
515 --
516 p_anniversary_start_date := l_anniversary_start_date;
517 --
518 if g_debug then
519 hr_utility.trace('..l_anniversary_start_date = '||to_char(l_anniversary_start_date,'DD/MM/RRRR'));
520 hr_utility.set_location(l_procedure, 999);
521 end if;
522 end initialise_dates;
523 --
524 --------------------------
525 -- CACE_ANNIVERSARY_DETAILS
526 --------------------------
527 --
528 -- This function fetches anniversary dates
529 -- for the lifetime of the employee up to the calculation date
530 -- Since this function is for the accrual formula it is assumed
531 -- the data passed in has already been validated so we do no
532 -- validate again.
533 --
534 function cache_anniversary_details
535 (p_payroll_id in number
536 ,p_assignment_id in number
540 ,p_anniversary_start_date out nocopy date
537 ,p_accrual_plan_id in number
538 ,p_calculation_date in date
539 ,p_service_start_date in date
541 ,p_anniversary_end_date out nocopy date
542 ,p_years_of_service out nocopy number
543 ) return number is
544 l_procedure constant varchar2(60) := g_package||'cache_anniversary_details';
545 l_person_counter integer;
546 l_counter integer;
547 l_anniversary_start_date date;
548 l_anniversary_end_date date;
549 l_years_of_service number;
550 l_person_id number;
551 l_date_start date;
552 l_cached boolean;
553 l_anniversary_not_found boolean;
554 l_actual_termination_date date;
555 l_calculation_date date;
556 l_service_start_date date;
557 begin
558 l_person_counter := 1;
559 l_counter := 1;
560 l_years_of_service := 0;
561 g_debug := hr_utility.debug_enabled;
562 if g_debug then
563 hr_utility.set_location(l_procedure, 0);
564 end if;
565 --
566 open csr_person_id(p_assignment_id, p_calculation_date);
567 fetch csr_person_id into l_person_id;
568 close csr_person_id;
569 --
570 if g_debug then
571 hr_utility.trace('l_person_id.=.'||to_char(l_person_id));
572 end if;
573 --
574 -- Has the information been cached?
575 -- Note: Anniversaries apply to the person... not each assignment
576 --
577 if (p_anniversary_table2.count > 0) then
578 for x in 1..p_anniversary_table2.count loop
579 if (p_anniversary_table2(x).person_id = l_person_id)
580 and
581 (p_anniversary_table2(x).calculation_date = p_calculation_date) then
582 l_cached := true;
583 else
584 l_cached := false;
585 end if;
586 end loop;
587 else
588 l_cached := false;
589 end if;
590 --
591 if not l_cached then
592 --
593 -- Anniversaries have not yet been cached
594 --
595 if g_debug then
596 hr_utility.set_location(l_procedure, 30);
597 end if;
598 --
599 -- Initialise the first anniversary
600 -- and start from there.
601 --
602 initialise_dates
603 (p_assignment_id => p_assignment_id
604 ,p_service_start_date => l_service_start_date
605 ,p_anniversary_start_date => l_anniversary_start_date
606 );
607 l_anniversary_end_date := l_anniversary_start_date;
608 --
609 if g_debug then
610 hr_utility.set_location(l_procedure, 40);
611 hr_utility.trace('l_anniversary_start_date..=.'||to_char(l_anniversary_start_date));
612 hr_utility.trace('p_calculation_date........=.'||to_char(p_calculation_date));
613 end if;
614 --
615 -- Initialise Person Table
616 --
617 if p_anniversary_table2.exists(1) then
618 --
619 -- Some records exist... we have already confirmed
620 -- that one does not exist for this person above
621 -- so set the person counter index after the last
622 --
623 l_person_counter := p_anniversary_table2.last + 1;
624 --
625 if g_debug then
626 hr_utility.set_location(l_procedure, 45);
627 end if;
628 end if;
629 --
630 --
631 -- Start at l_anniversary_start and loop thru until the next anniversary is after calculation date
632 --
633 while (not l_anniversary_start_date > p_calculation_date) and (l_anniversary_end_date <= p_calculation_date)
634 loop
635 --
636 -- Initialise Anniversary Table
637 --
638 -- Note, the anniversary dates cannot be on the same day
639 -- Assuming no time off without pay, the anniversary end date
640 -- will actually be 12mths - 1 day from the anniversary start date
641 --
642 p_anniversary_table2(l_person_counter).person_id := l_person_id;
643 p_anniversary_table2(l_person_counter).calculation_date := p_calculation_date;
644 --
645 if l_service_start_date = l_anniversary_end_date then
646 p_anniversary_table2(l_person_counter).anniversary_start_date := l_anniversary_end_date;
647 p_anniversary_table2(l_person_counter).anniversary_end_date := add_months(l_anniversary_end_date,12) - 1;
648 else
649 p_anniversary_table2(l_person_counter).anniversary_start_date := l_anniversary_end_date + 1;
650 p_anniversary_table2(l_person_counter).anniversary_end_date := add_months(l_anniversary_end_date,12);
651 end if;
652 p_anniversary_table2(l_person_counter).years_of_service := l_years_of_service;
653 --
654 p_anniversary_table2(l_person_counter).anniversary_end_date :=
655 move_anniversary
656 (l_person_id
657 , p_calculation_date
658 , p_anniversary_table2(l_person_counter).anniversary_start_date
659 , p_anniversary_table2(l_person_counter).anniversary_end_date
660 );
661 if g_debug then
662 hr_utility.set_location(l_procedure, 50);
666 hr_utility.trace('years_of_service........=.'||to_char(p_anniversary_table2(l_person_counter).years_of_service));
663 hr_utility.trace('........................');
664 hr_utility.trace('anniversary_start_date..=.'||to_char(p_anniversary_table2(l_person_counter).anniversary_start_date));
665 hr_utility.trace('anniversary_end_date....=.'||to_char(p_anniversary_table2(l_person_counter).anniversary_end_date));
667 hr_utility.trace('........................');
668 end if;
669 --
670 -- Has the next anniversary been moved after the calculation date
671 if l_anniversary_end_date > p_calculation_date then
672 if g_debug then
673 hr_utility.set_location(l_procedure, 60);
674 end if;
675 exit;
676 end if;
677 l_anniversary_start_date := p_anniversary_table2(l_person_counter).anniversary_start_date;
678 l_anniversary_end_date := p_anniversary_table2(l_person_counter).anniversary_end_date;
679 --
680 -- Years of service cannot equal to l_counter
681 -- because it can be zero, whereas the index for a plsql table
682 -- starts at 1
683 --
684 l_years_of_service := l_years_of_service + 1;
685 --
686 -- Increment table counter and initialise next anniversaries
687 --
688 l_counter := l_counter + 1;
689 l_person_counter := l_person_counter + 1;
690 --
691 if g_debug then
692 hr_utility.set_location(l_procedure, 70);
693 end if;
694 --
695 end loop;
696 p_anniversary_start_date := l_anniversary_start_date;
697 p_anniversary_end_date := l_anniversary_end_date;
698 --
699 -- Years of service is zero until 1 complete year of employment is reached
700 -- This being the case then because the table counter is initialised at 1
701 -- we need to subtract 1 from the count to get the correct result...
702 -- unless of course the counter is less than 1 (which can happen when the
703 -- calculation date is on the service_start_date
704 --
705 if l_counter > 0 then
706 p_years_of_service := l_counter - 1;
707 else
708 p_years_of_service := 0;
709 end if;
710 --
711 l_cached := true;
712 end if;
713 --
714 if l_cached then
715 for x in 1..p_anniversary_table2.count loop
716 if (p_anniversary_table2(x).person_id = l_person_id)
717 then
718 --
719 -- Information has been cached
720 -- so we retrieve it from the cache
721 --
722 if g_debug then
723 hr_utility.set_location(l_procedure||' CACHE', 10);
724 end if;
725 --
726 -- looking for where the calculation_date is between ann_start and ann_end
727 -- for the given person
728 --
729 if ((p_calculation_date > p_anniversary_table2(x).anniversary_start_date)
730 or(p_calculation_date = p_anniversary_table2(x).anniversary_start_date))
731 and
732 ((p_calculation_date < p_anniversary_table2(x).anniversary_end_date)
733 or (p_calculation_date = p_anniversary_table2(x).anniversary_end_date))
734 then
735 if g_debug then
736 hr_utility.set_location(l_procedure||' CACHE', 20);
737 end if;
738 p_anniversary_start_date := p_anniversary_table2(x).anniversary_start_date;
739 p_anniversary_end_date := p_anniversary_table2(x).anniversary_end_date;
740 p_years_of_service := p_anniversary_table2(x).years_of_service;
741 if g_debug then
742 hr_utility.set_location('p_anniversary_start_date = '||to_char(p_anniversary_start_date,'DD-MON-YYYY'), 20);
743 hr_utility.set_location('p_anniversary_end_date.. = '||to_char(p_anniversary_end_date,'DD-MON-YYYY'), 20);
744 hr_utility.set_location('p_years_of_service...... = '||to_char(p_years_of_service), 20);
745 end if;
746 --
747 -- Exit loop so that the counter is not incremented
748 --
749 exit;
750 end if; -- p_calculation_date
751 end if;
752 end loop;
753 --
754 end if;
755 --
756 if g_debug then
757 hr_utility.set_location(l_procedure,999);
758 end if;
759 --
760 return 0;
761 --
762 end cache_anniversary_details;
763 --
764 --------------------------
765 -- CALCULATE_DAILY_ACCRUAL
766 --------------------------
767 --
768 function calculate_daily_accrual
769 (p_person_id in number
770 ,p_accrual_plan_id in number
771 ,p_start_date in date
772 ,p_end_date in date
773 ,p_annual_accrual in number
774 ,p_work_week in number
775 )
776 return number is
777 --
778 l_days number;
779 l_daily_accrual number;
780 l_days_inactive number;
781 l_days_total number;
782 l_days_unpaid number;
783 l_procedure constant varchar2(60) := g_package||'calculate_daily_accrual';
784 --
785 begin
786 g_debug := hr_utility.debug_enabled;
787 if g_debug then
791 end if;
788 hr_utility.set_location(l_procedure, 0);
789 hr_utility.trace('p_start_date = '||to_char(p_start_date,'DD-MON-YYYY'));
790 hr_utility.trace('p_end_date...= '||to_char(p_end_date,'DD-MON-YYYY'));
792 --
793 -- 1.
794 -- Get the number of days between start and end
795 -- we include the end day which is why we add 1
796 l_days := (p_end_date + 1) - p_start_date;
797 --
798 --
799 -- 2.
800 -- Get the number of days of unpaid absence where the total each seperate absence was > 1 week
801 -- between these 2 dates
802 l_days_unpaid := 0;
803 --
804 for abs_rec in csr_get_unpaid_absences(p_person_id, p_start_date, p_end_date)
805 loop
806 l_days_unpaid := l_days_unpaid + abs_rec.add_days;
807 end loop;
808 --
809 -- total = 1-2
810 l_days_total := l_days - l_days_unpaid;
811 --
812 -- The Annual Accrual is held in 'work weeks' therefore
813 -- we need the multiplication by work week
814 l_daily_accrual := (p_annual_accrual * p_work_week)/l_days_total;
815
816 if g_debug then
817 hr_utility.trace('l_days...........= '||to_char(l_days));
818 hr_utility.trace('l_days_unpaid....= '||to_char(l_days_unpaid));
819 hr_utility.trace('l_days_total.....= '||to_char(l_days_total));
820 hr_utility.trace('l_daily_accrual..= '||to_char(l_daily_accrual));
821 end if;
822 --
823 if g_debug then
824 hr_utility.set_location(l_procedure, 999);
825 end if;
826 --
827 return l_daily_accrual;
828 --
829 end calculate_daily_accrual;
830 --
831 ----------------------
832 -- DETERMINE_WORK_WEEK
833 ----------------------
834 --
835 function determine_work_week
836 (p_assignment_id in number
837 ,p_current_day in date
838 ,p_uom in varchar2
839 ,p_annual_accrual in number
840 ,p_chg_asg_hours IN boolean
841 ,p_asg_hours IN number
842 ,p_freq IN varchar2
843 ) return number is
844 l_procedure constant varchar2(60) := g_package||'determine_work_week';
845 l_work_week number;
846 l_work_frequency varchar2(30);
847 l_use_asg_hours varchar2(3);
848 l_weeks_in_period number;
849 l_days_in_period number;
850 l_working_time number;
851 --
852 cursor csr_asg_hours(p_effective_date date) is
853 select normal_hours
854 , frequency
855 from per_assignments_f
856 where assignment_id = p_assignment_id
857 and p_effective_date between effective_start_date and effective_end_date;
858 --
859 cursor csr_get_days_in_period is
860 select ptp.end_date - ptp.start_date days_in_period
861 from per_time_periods ptp
862 , per_assignments_f paf
863 where paf.assignment_id = p_assignment_id
864 and p_current_day between paf.effective_start_date and paf.effective_end_date
865 and paf.payroll_id = ptp.payroll_id
866 and p_current_day between ptp.start_date and ptp.end_date;
867 --
868 begin
869 l_use_asg_hours := 'N';
870 g_debug := hr_utility.debug_enabled;
871 if g_debug then
872 hr_utility.set_location(l_procedure, 0);
873 hr_utility.trace('p_assignment_id = '||to_char(p_assignment_id));
874 hr_utility.trace('p_current_day.. = '||to_char(p_current_day,'DD-MON-YYYY'));
875 end if;
876 --
877 -- Check how to calculate accrual
878 --
879 open csr_use_asg_hours(p_assignment_id, p_current_day);
880 fetch csr_use_asg_hours into l_use_asg_hours;
881 close csr_use_asg_hours;
882 --
883 if g_debug then
884 hr_utility.trace('l_use_asg_hours = '||l_use_asg_hours);
885 end if;
886
887 if l_use_asg_hours = 'Y' then
888 --
889 -- Get work week from ASG_HOURS on this current day
890 -- because ASG_HOURS can be datetracked we need to check it
891 -- each day
892 --
893 IF p_chg_asg_hours THEN
894 l_work_week:=p_asg_hours;
895 l_work_frequency:=p_freq;
896 ELSE
897 open csr_asg_hours(p_current_day);
898 fetch csr_asg_hours into l_work_week, l_work_frequency;
899 if csr_asg_hours%notfound then
900 hr_utility.set_message(801,'HR_NZ_WORKING_HRS_MISSING');
901 hr_utility.raise_error;
902 end if;
903 close csr_asg_hours;
904 END IF;
905 --
906 -- ASG_FREQ must be Week
907 --
908 if l_work_frequency <> 'W' then
909 hr_utility.set_message(801,'HR_NZ_BAD_WORKING_FREQ');
910 hr_utility.raise_error;
911 end if;
912 --
913 -- Validate date to use work week from ASG_HOURS
914 -- Note: this can only be used to accrue in HOURS
915 -- so we also check the UOM of the accrual plan
916 -- matches this.
917 --
918 if ((l_work_week is not null) or (l_work_week = 0)) then
919 if g_debug then
920 hr_utility.set_location(l_procedure,20);
921 hr_utility.trace('l_work_week comes from ASG_HOURS');
922 hr_utility.trace('l_work_week = '||to_char(l_work_week));
923 hr_utility.trace('p_uom.......= '||p_uom);
924 end if;
928 hr_utility.raise_error;
925 --
926 else
927 hr_utility.set_message(801,'HR_NZ_WORKING_HRS_MISSING');
929 end if;
930 --
931 else
932 --
933 --------------------------------------------
934 -- Work week is not determined from ASG_HOURS
935 --------------------------------------------
936 --
937 if g_debug then
938 hr_utility.set_location(l_procedure,35);
939 end if;
940 --
941 l_work_week := get_standard_work_week(p_assignment_id => p_assignment_id, p_effective_date => p_current_day);
942 --
943 -- If the 'Standard Work Week' is not available
944 -- then we use the balance DAYS_OR_HOURS_WORKED
945 --
946 if (l_work_week is null) or (l_work_week = 0) then
947 --
948 open csr_get_days_in_period;
949 fetch csr_get_days_in_period into l_days_in_period;
950 close csr_get_days_in_period;
951 --
952 l_weeks_in_period := l_days_in_period / 7;
953
954 l_working_time := get_working_days_balance
955 (p_assignment_id => p_assignment_id
956 ,p_effective_date => p_current_day
957 );
958
959 l_work_week := l_working_time / l_weeks_in_period;
960
961 if g_debug then
962 hr_utility.set_location(l_procedure,40);
963 hr_utility.trace('..l_work_week comes from DAYS_OR_HOURS_WORKED');
964 hr_utility.trace('..l_work_week = '||to_char(l_work_week));
965 hr_utility.trace('..l_working_time = '||to_char(l_working_time));
966 hr_utility.trace('..l_weeks_in_period = '||to_char(l_weeks_in_period));
967 hr_utility.trace('..p_annual_accrual = '||to_char(p_annual_accrual));
968 hr_utility.trace('..l_days_in_period = '||to_char(l_days_in_period));
969 end if;
970
971 end if;
972 --
973 end if;
974 --
975 --
976 if g_debug then
977 hr_utility.set_location(l_procedure, 999);
978 end if;
979 --
980 return l_work_week;
981 end determine_work_week;
982 ---------------------------------------------------------------------
983 -- is_leap_year function --
984 -- function called in daily_accrual_loop function --
985 -- This function finds whether 29-feb of leap year present between --
986 -- the dates given --
987 ---------------------------------------------------------------------
988 function is_leap_year
989 (p_start_date in date
990 ,p_end_date in date)
991 return number
992 is
993
994 l_date date;
995 l_year_date date;
996 l_curr_year varchar2(4);
997 l_mon number;
998
999 begin
1000
1001 l_mon :=to_char(p_start_date,'MM');
1002
1003 IF l_mon<=2 THEN
1004 l_curr_year := to_char(p_start_date,'YYYY');
1005 l_year_date := p_start_date;
1006 ELSE
1007 l_curr_year := to_char(p_end_date,'YYYY');
1008 l_year_date := p_end_date;
1009 END IF;
1010
1011 if ((to_number(l_curr_year)/4 = trunc( to_number(l_curr_year)/4))
1012 and not((to_number(l_curr_year)/100 = trunc(to_number(l_curr_year)/100))))
1013 or (to_number(l_curr_year)/400 = trunc(to_number(l_curr_year)/400) )
1014 then
1015 l_date := to_date('29-02'||to_char(l_year_date,'YYYY'),'DD-MM-YYYY');
1016 if l_date between p_start_date and p_end_date then
1017 return 1;
1018 else
1019 return 0;
1020 end if;
1021 else
1022 return 0;
1023 end if;
1024
1025 end is_leap_year;
1026 --
1027 --------------------------
1028 -- DETERMINE_WORK_WEEK_OLD
1029 --------------------------
1030 --
1031 function determine_work_week
1032 (p_assignment_id in number
1033 ,p_current_day in date
1034 ,p_uom in varchar2
1035 ,p_annual_accrual in number
1036 ) return number is
1037 l_procedure constant varchar2(60) := g_package||'determine_work_week';
1038 l_work_week number;
1039 l_work_frequency varchar2(30);
1040 l_use_asg_hours varchar2(3);
1041 l_weeks_in_period number;
1042 l_days_in_period number;
1043 l_working_time number;
1044 --
1045 cursor csr_asg_hours(p_effective_date date) is
1046 select normal_hours
1047 , frequency
1048 from per_assignments_f
1049 where assignment_id = p_assignment_id
1050 and p_effective_date between effective_start_date and effective_end_date;
1051 --
1052 cursor csr_get_days_in_period is
1053 select ptp.end_date - ptp.start_date days_in_period
1054 from per_time_periods ptp
1055 , per_assignments_f paf
1056 where paf.assignment_id = p_assignment_id
1057 and p_current_day between paf.effective_start_date and paf.effective_end_date
1058 and paf.payroll_id = ptp.payroll_id
1059 and p_current_day between ptp.start_date and ptp.end_date;
1060 --
1061 begin
1062 l_use_asg_hours := 'N';
1063 g_debug := hr_utility.debug_enabled;
1064 if g_debug then
1068 end if;
1065 hr_utility.set_location(l_procedure, 0);
1066 hr_utility.trace('p_assignment_id = '||to_char(p_assignment_id));
1067 hr_utility.trace('p_current_day.. = '||to_char(p_current_day,'DD-MON-YYYY'));
1069 --
1070 -- Check how to calculate accrual
1071 --
1072 open csr_use_asg_hours(p_assignment_id, p_current_day);
1073 fetch csr_use_asg_hours into l_use_asg_hours;
1074 close csr_use_asg_hours;
1075 --
1076 if g_debug then
1077 hr_utility.trace('l_use_asg_hours = '||l_use_asg_hours);
1078 end if;
1079
1080 if l_use_asg_hours = 'Y' then
1081 --
1082 -- Get work week from ASG_HOURS on this current day
1083 -- because ASG_HOURS can be datetracked we need to check it
1084 -- each day
1085 --
1086 open csr_asg_hours(p_current_day);
1087 fetch csr_asg_hours into l_work_week, l_work_frequency;
1088 if csr_asg_hours%notfound then
1089 hr_utility.set_message(801,'HR_NZ_WORKING_HRS_MISSING');
1090 hr_utility.raise_error;
1091 end if;
1092 close csr_asg_hours;
1093 --
1094 -- ASG_FREQ must be Week
1095 --
1096 if l_work_frequency <> 'W' then
1097 hr_utility.set_message(801,'HR_NZ_BAD_WORKING_FREQ');
1098 hr_utility.raise_error;
1099 end if;
1100 --
1101 -- Validate date to use work week from ASG_HOURS
1102 -- Note: this can only be used to accrue in HOURS
1103 -- so we also check the UOM of the accrual plan
1104 -- matches this.
1105 --
1106 if ((l_work_week is not null) or (l_work_week = 0)) then
1107 if g_debug then
1108 hr_utility.set_location(l_procedure,20);
1109 hr_utility.trace('l_work_week comes from ASG_HOURS');
1110 hr_utility.trace('l_work_week = '||to_char(l_work_week));
1111 hr_utility.trace('p_uom.......= '||p_uom);
1112 end if;
1113 --
1114 else
1115 hr_utility.set_message(801,'HR_NZ_WORKING_HRS_MISSING');
1116 hr_utility.raise_error;
1117 end if;
1118 --
1119 else
1120 --
1121 --------------------------------------------
1122 -- Work week is not determined from ASG_HOURS
1123 --------------------------------------------
1124 --
1125 if g_debug then
1126 hr_utility.set_location(l_procedure,35);
1127 end if;
1128 --
1129 l_work_week := get_standard_work_week(p_assignment_id => p_assignment_id, p_effective_date => p_current_day);
1130 --
1131 -- If the 'Standard Work Week' is not available
1132 -- then we use the balance DAYS_OR_HOURS_WORKED
1133 --
1134 if (l_work_week is null) or (l_work_week = 0) then
1135 --
1136 open csr_get_days_in_period;
1137 fetch csr_get_days_in_period into l_days_in_period;
1138 close csr_get_days_in_period;
1139 --
1140 l_weeks_in_period := l_days_in_period / 7;
1141
1142 l_working_time := get_working_days_balance
1143 (p_assignment_id => p_assignment_id
1144 ,p_effective_date => p_current_day
1145 );
1146
1147 l_work_week := l_working_time / l_weeks_in_period;
1148
1149 if g_debug then
1150 hr_utility.set_location(l_procedure,40);
1151 hr_utility.trace('..l_work_week comes from DAYS_OR_HOURS_WORKED');
1152 hr_utility.trace('..l_work_week = '||to_char(l_work_week));
1153 hr_utility.trace('..l_working_time = '||to_char(l_working_time));
1154 hr_utility.trace('..l_weeks_in_period = '||to_char(l_weeks_in_period));
1155 hr_utility.trace('..p_annual_accrual = '||to_char(p_annual_accrual));
1156 hr_utility.trace('..l_days_in_period = '||to_char(l_days_in_period));
1157 end if;
1158
1159 end if;
1160 --
1161 end if;
1162 --
1163 --
1164 if g_debug then
1165 hr_utility.set_location(l_procedure, 999);
1166 end if;
1167 --
1168 return l_work_week;
1169 end determine_work_week;
1170 --
1171 ---------------------
1172 -- DAILY_ACCRUAL_LOOP
1173 ---------------------
1174 --
1175 function daily_accrual_loop
1176 (p_payroll_id in number
1177 ,p_assignment_id in number
1178 ,p_accrual_plan_id in number
1179 ,p_service_start_date in date
1180 ,p_start_date in date
1181 ,p_end_date in date
1182 )
1183 return number is
1184 l_procedure constant varchar2(60) := g_package||'daily_accrual_loop';
1185 l_current_day date;
1186 l_anniversary_start_date date;
1187 l_anniversary_end_date date;
1188
1189 l_person_id number;
1190 l_years_of_service number;
1191 l_prev_years_of_service number;
1192 l_tmp_number number;
1193 l_period_accrual number;
1194 l_asg_hours number;
1195 l_annual_accrual number;
1196 l_daily_accrual number;
1197 l_work_week number;
1198 l_temp_week number;
1199 l_temp_accrual number;
1200 l_uom varchar2(2);
1201 l_eligible_inactive boolean;
1205 l_tmp_freq varchar2(1);
1202 l_eligible_abs boolean;
1203 l_chg_asg_hours boolean;
1204 l_tmp_asg_hours number;
1206 l_is_leap_year number;
1207 l_temp_leap_year number;
1208
1209
1210 type t_system_status is table of varchar2(30) index by binary_integer ;
1211 type t_eff_start_date is table of date index by binary_integer ;
1212 type t_eff_end_date is table of date index by binary_integer ;
1213
1214 l_system_status t_system_status;
1215 l_eff_start_date t_eff_start_date;
1216 l_eff_end_date t_eff_end_date;
1217
1218 type t_abs_start_date is table of date index by binary_integer ;
1219 type t_abs_end_date is table of date index by binary_integer ;
1220
1221 l_abs_start_date t_abs_start_date;
1222 l_abs_end_date t_abs_end_date;
1223
1224 type t_normal_hours is table of number index by binary_integer ;
1225 type t_frequency is table of varchar2(1) index by binary_integer ;
1226 type t_wrk_eff_start_date is table of date index by binary_integer ;
1227 type t_wrk_eff_end_date is table of date index by binary_integer ;
1228
1229 l_normal_hours t_normal_hours;
1230 l_frequency t_frequency;
1231 l_wrk_eff_start_date t_wrk_eff_start_date;
1232 l_wrk_eff_end_date t_wrk_eff_end_date;
1233
1234 --
1235 cursor csr_get_accrual_band
1236 (p_number_of_years number
1237 ,p_accrual_plan_id number
1238 ) is
1239 select bnd.annual_rate annual_rate
1240 , acc.accrual_units_of_measure uom
1241 from pay_accrual_bands bnd
1242 , pay_accrual_plans acc
1243 where p_number_of_years >= bnd.lower_limit
1244 and p_number_of_years < bnd.upper_limit
1245 and bnd.accrual_plan_id = p_accrual_plan_id
1246 and bnd.accrual_plan_id = acc.accrual_plan_id;
1247
1248 begin
1249
1250 open csr_person_id(p_assignment_id, p_start_date);
1251 fetch csr_person_id into l_person_id;
1252 close csr_person_id;
1253
1254 select ast.per_system_status,asg.EFFECTIVE_START_DATE,asg.EFFECTIVE_END_DATE
1255 bulk collect into l_system_status, l_eff_start_date, l_eff_end_date
1256 from per_assignments_f asg
1257 ,per_assignment_status_types ast
1258 where asg.assignment_id = p_assignment_id
1259 and asg.assignment_status_type_id = ast.assignment_status_type_id
1260 and ast.per_system_status <> 'ACTIVE_ASSIGN';
1261
1262 select (ab.date_start + g_unpaid_absence_days + 1), ab.date_end
1263 bulk collect into l_abs_start_date, l_abs_end_date
1264 from per_absence_attendances ab
1265 , per_absence_attendance_types abt
1266 where ab.absence_attendance_type_id = abt.absence_attendance_type_id
1267 and ab.person_id = l_person_id
1268 and abt.absence_category = g_unpaid_absence_category
1269 and ((ab.date_end - ab.date_start) > g_unpaid_absence_days);
1270
1271 select normal_hours,frequency,effective_start_date,effective_end_date
1272 bulk collect into l_normal_hours,l_frequency,l_wrk_eff_start_date,l_wrk_eff_end_date
1273 from per_assignments_f
1274 where assignment_id = p_assignment_id;
1275
1276 l_eligible_inactive:= true;
1277 l_eligible_abs:= true;
1278 l_chg_asg_hours:= false;
1279
1280 l_years_of_service := 0;
1281 l_prev_years_of_service := -1;
1282
1283 l_annual_accrual := 0;
1284 l_work_week := 0;
1285 l_period_accrual := 0;
1286 l_is_leap_year:=0;
1287
1288 g_debug := hr_utility.debug_enabled;
1289 if g_debug then
1290 hr_utility.set_location(l_procedure, 0);
1291 hr_utility.set_location('p_payroll_id......= '||to_char(p_payroll_id),1);
1292 hr_utility.set_location('p_assignment_id...= '||to_char(p_assignment_id),1);
1293 hr_utility.set_location('p_accrual_plan_id.= '||to_char(p_accrual_plan_id),1);
1294 hr_utility.set_location('p_start_date......= '||to_char(p_start_date,'DD-MON-YYYY'),1);
1295 hr_utility.set_location('p_end_date........= '||to_char(p_end_date,'DD-MON-YYYY'),1);
1296 end if;
1297 --l_period_accrual := 0;
1298 --
1299 -- Loop thru each day of the period given
1300 -- from first to last day of period
1301 --
1302 l_current_day := p_start_date;
1303 --
1304 loop
1305 exit when l_current_day > p_end_date;
1306 if g_debug then
1307 hr_utility.set_location(l_procedure,5);
1308 hr_utility.trace('l_current_day = '||to_char(l_current_day,'DD-MON-YYYY'));
1309 end if;
1310 --
1311 -- Is this day eligible to accrue Annual Leave
1312 --
1313 --
1314 -- Check if the present day is present in the inactive days periods
1315 if l_system_status.count>0 then
1316 for i in 1..l_system_status.count loop
1317 if l_current_day between l_eff_start_date(i) and l_eff_end_date(i)
1318 then
1319 l_eligible_inactive:= false;
1320 exit;
1321 else
1322 l_eligible_inactive:= true;
1323 end if;
1324 end loop;
1325 end if;
1326
1327 -- Check if the present day is present in the absences
1328 if l_abs_start_date.count>0 then
1332 l_eligible_abs:= false;
1329 for i in 1..l_abs_start_date.count loop
1330 if l_current_day between l_abs_start_date(i) and l_abs_end_date(i)
1331 then
1333 exit;
1334 else
1335 l_eligible_abs:= true;
1336 end if;
1337 end loop;
1338 end if;
1339
1340 -- Check if assignment hours has changed
1341 if l_frequency.count>0 then
1342 for i in 1..l_frequency.count loop
1343 if l_current_day between l_wrk_eff_start_date(i) and l_wrk_eff_end_date(i)
1344 then
1345 l_chg_asg_hours := true;
1346 l_tmp_asg_hours := l_normal_hours(i);
1347 l_tmp_freq := l_frequency(i);
1348 exit;
1349 end if;
1350 end loop;
1351 end if;
1352
1353 if (l_eligible_inactive) and (l_eligible_abs)
1354 then
1355 --
1356 -- Set temporary holder for years of service
1357 -- so that we can check to see if it has changed
1358 -- from day to day... if it has not changed, this
1359 -- means that the accrual band cannot change and
1360 -- this can save us checking the accrual band
1361 -- for every day.
1362 --
1363 if l_prev_years_of_service <> -1 then
1364 l_prev_years_of_service := l_years_of_service;
1365 end if;
1366 --
1367 -- We only need to get the anniversary details
1368 -- once for each anniversary period
1369 -- So we only execute this code if anniversary has
1370 -- not been initialised or if the current processing
1371 -- day has advanced into the next anniversary period
1372 --
1373 if ((l_anniversary_start_date is null) or (l_current_day > l_anniversary_end_date)) then
1374 --
1375 -- Get the anniversary details for this day
1376 --
1377 l_tmp_number := cache_anniversary_details
1378 (p_payroll_id => p_payroll_id
1379 ,p_assignment_id => p_assignment_id
1380 ,p_accrual_plan_id => p_accrual_plan_id
1381 ,p_calculation_date => l_current_day
1382 ,p_service_start_date => p_service_start_date
1383 ,p_anniversary_start_date => l_anniversary_start_date
1384 ,p_anniversary_end_date => l_anniversary_end_date
1385 ,p_years_of_service => l_years_of_service
1386 );
1387 --
1388 if g_debug then
1389 hr_utility.set_location(l_procedure,10);
1390 hr_utility.trace('l_anniversary_start_date = '||to_char(l_anniversary_start_date,'DD-MON-YYYY'));
1391 hr_utility.trace('l_anniversary_end_date...= '||to_char(l_anniversary_end_date,'DD-MON-YYYY'));
1392 end if;
1393 --
1394 end if;
1395 --
1396
1397 l_temp_accrual:=l_annual_accrual;
1398
1399 if l_years_of_service <> l_prev_years_of_service then
1400 --
1401 -- Now get the accrual band for this day
1402 --
1403 if g_debug then
1404 hr_utility.set_location(l_procedure,23);
1405 hr_utility.trace('l_uom...... ='||l_uom);
1406 end if;
1407 --
1408 open csr_get_accrual_band(l_years_of_service, p_accrual_plan_id);
1409 fetch csr_get_accrual_band into l_annual_accrual, l_uom;
1410 close csr_get_accrual_band;
1411 --
1412 end if;
1413 --
1414 -- Get the work week
1415 --
1416 l_temp_week :=l_work_week;
1417
1418 l_work_week := nvl(determine_work_week
1419 (p_assignment_id => p_assignment_id
1420 ,p_current_day => l_current_day
1421 ,p_uom => l_uom
1422 ,p_annual_accrual => l_annual_accrual
1423 ,p_chg_asg_hours => l_chg_asg_hours
1424 ,p_asg_hours => l_tmp_asg_hours
1425 ,p_freq => l_tmp_freq
1426 ),0);
1427 --
1428 if g_debug then
1429 hr_utility.set_location(l_procedure,30);
1430 hr_utility.trace('l_work_week ='||to_char(l_work_week));
1431 end if;
1432 --
1433 l_temp_leap_year:=l_is_leap_year;
1434 l_is_leap_year:=is_leap_year(l_anniversary_start_date,l_anniversary_end_date);
1435
1436 IF (l_temp_week <> l_work_week) OR (l_temp_accrual<>l_annual_accrual)
1437 OR (l_temp_leap_year <> l_is_leap_year)
1438 THEN
1439 l_daily_accrual := nvl(calculate_daily_accrual
1440 (p_person_id => l_person_id
1441 ,p_accrual_plan_id => p_accrual_plan_id
1442 ,p_start_date => l_anniversary_start_date
1443 ,p_end_date => l_anniversary_end_date
1444 ,p_annual_accrual => l_annual_accrual
1445 ,p_work_week => l_work_week
1446 ),0);
1447 END IF;
1448 --
1452 hr_utility.trace('...l_period_accrual = '||to_char(l_period_accrual));
1449 l_period_accrual := l_period_accrual + l_daily_accrual;
1450 if g_debug then
1451 hr_utility.set_location(l_procedure,40);
1453 end if;
1454 end if;
1455 --
1456 l_current_day := l_current_day + 1;
1457 end loop;
1458 --
1459 if g_debug then
1460 hr_utility.set_location(l_procedure, 999);
1461 end if;
1462 --
1463 return nvl(l_period_accrual,0);
1464 --
1465 end daily_accrual_loop;
1466 --
1467 ------------------------------
1468 -- GET_ANNUAL_LEAVE_PERCENTAGE
1469 ------------------------------
1470 --
1471 function get_annual_leave_percentage(p_accrual_plan_id number)
1472 return number is
1473 --
1474 cursor csr_percentage(p_accrual_plan_id number) is
1475 select fnd_number.canonical_to_number(information1)
1476 from pay_accrual_plans pap
1477 where information_category = 'NZ_NZAL'
1478 and pap.accrual_plan_id = p_accrual_plan_id;
1479 --
1480 l_percentage number;
1481 l_procedure constant varchar2(60) := g_package||'get_annual_leave_percentage';
1482 --
1483 begin
1484 g_debug := hr_utility.debug_enabled;
1485 if g_debug then
1486 hr_utility.set_location(l_procedure, 0);
1487 end if;
1488 --
1489 open csr_percentage(p_accrual_plan_id);
1490 fetch csr_percentage into l_percentage;
1491 if csr_percentage%notfound then
1492 l_percentage := 0;
1493 end if;
1494 close csr_percentage;
1495 --
1496 if g_debug then
1497 hr_utility.set_location(l_procedure, 999);
1498 end if;
1499 --
1500 return l_percentage;
1501 --
1502 end get_annual_leave_percentage;
1503 --
1504 --
1505 -- Function used by formula function
1506 -- only required to save on duplication of code
1507 -- due to multiple calls.
1508 --
1509 --
1510 -- Ordinary rate has already been converted to appropriate UOM
1511 --
1512 function annual_leave_rate_calc_1
1513 (p_ordinary_rate in number
1514 ,p_earnings_prev_12mths in number
1515 ,p_earnings_td in number
1516 ,p_time_worked_prev_12mths in number
1517 ,p_time_worked_td in number
1518 ,p_work_week in number
1519 ,p_hire_date in date
1520 ,p_period_start_date in date
1521 ,p_period_end_date in date
1522 )
1523 return number is
1524 l_rate number;
1525 l_procedure constant varchar2(60) := g_package||'annual_leave_rate_calc_1';
1526 l_average_weekly_rate number;
1527 --
1528
1529 begin
1530 g_debug := hr_utility.debug_enabled;
1531 --
1532 if g_debug then
1533 hr_utility.set_location(l_procedure, 0);
1534 hr_utility.trace('...p_ordinary_rate = '||to_char(p_ordinary_rate));
1535 hr_utility.trace('...p_earnings_prev_12mths = '||to_char(p_earnings_prev_12mths));
1536 hr_utility.trace('...p_time_worked_prev_12mths = '||to_char(p_time_worked_prev_12mths));
1537 hr_utility.trace('...p_work_week = '||to_char(p_work_week));
1538 hr_utility.trace('...p_period_start_date = '||to_char(p_period_start_date,'DD/MM/RRRR'));
1539 hr_utility.trace('...p_period_end_date = '||to_char(p_period_end_date,'DD/MM/RRRR'));
1540 hr_utility.trace('...p_hire_date = '||to_char(p_hire_date,'DD/MM/RRRR'));
1541 end if;
1542 --
1543 -- Check to see if the Employees work period is more than 12 months
1544 --
1545 /* Bug 7260523 - Changed dates order for months_between condition */
1546 if months_between(p_period_end_date,p_hire_date) < 12 then
1547 if (p_hire_date = p_period_start_date or p_hire_date > p_period_start_date)
1548 and (p_hire_date = p_period_end_date or p_hire_date < p_period_end_date)
1549 then
1550 if g_debug then
1551 hr_utility.set_location(l_procedure, 20);
1552 end if;
1553 l_rate := greatest(p_ordinary_rate, p_earnings_td/p_time_worked_td);
1554 else
1555 if g_debug then
1556 hr_utility.set_location(l_procedure, 30);
1557 end if;
1558 l_rate := greatest(p_ordinary_rate, p_earnings_prev_12mths/p_time_worked_prev_12mths);
1559 end if;
1560 else
1561 --
1562 -- Calculate the rate per week
1563 --
1564
1565 --
1566 -- Check to ensure we do not get divide by zero error
1567 --
1568 if p_time_worked_prev_12mths = 0 then
1569 l_rate := p_ordinary_rate;
1570 else
1571 l_average_weekly_rate := p_earnings_prev_12mths/52;
1572 l_rate := greatest(p_ordinary_rate, (l_average_weekly_rate / p_work_week));
1573 if g_debug then
1574 hr_utility.set_location(l_procedure, 40);
1575 end if;
1576 end if;
1577 end if;
1578 --
1579 if g_debug then
1580 hr_utility.set_location(l_procedure, 999);
1581 end if;
1582 --
1583 return l_rate;
1584 end annual_leave_rate_calc_1;
1585 --
1586 -- Function used by formula function
1587 -- only required to save on duplication of code
1588 -- due to multiple calls.
1589 --
1590 function annual_leave_rate_calc_2
1594 ) return number is
1591 (p_percentage in number
1592 ,p_gross_earnings in number
1593 ,p_advance_leave_earnings in number
1595 l_rate number;
1596 l_procedure constant varchar2(60) := g_package||'annual_leave_rate_calc_2';
1597 begin
1598 l_rate := p_percentage * p_gross_earnings - p_advance_leave_earnings; /*Bug 7254820 */
1599 return l_rate;
1600 end annual_leave_rate_calc_2;
1601 --
1602 -- PREVIOUS_PERIOD_END_DATE
1603 --
1604 function previous_period_end_date
1605 (p_payroll_id in number
1606 ,p_time_period_id in number
1607 ) return date is
1608 l_date date;
1609 l_number number;
1610 --
1611 cursor csr_get_payment_date is
1612 --select pptp.end_date
1613 select pptp.regular_payment_date
1614 from per_time_periods bptp
1615 , per_time_periods pptp
1616 where bptp.payroll_id = p_payroll_id -- identify driving period
1617 and bptp.time_period_id = p_time_period_id -- identify driving period
1618 and pptp.payroll_id = p_payroll_id -- match payroll
1619 and bptp.start_date = pptp.end_date + 1; -- idenfity previous period
1620 --
1621 begin
1622 open csr_get_payment_date;
1623 fetch csr_get_payment_date into l_date;
1624 close csr_get_payment_date;
1625 --
1626 return l_date;
1627 end previous_period_end_date;
1628 --
1629 --
1630 --------------------------------------------------------------
1631 --
1632 -- get_accrual_entitlement
1633 --
1634 -- This function is required mainly by the NZ local library
1635 -- and will return the net accrual and net entitlement for a
1636 -- given person on a given day.
1637 --
1638 -- These values will be displayed in the forms PAYWSACV and
1639 -- PAYWSEAD.
1640 --
1641 --------------------------------------------------------------
1642 --
1643 FUNCTION get_accrual_entitlement
1644 (p_assignment_id IN NUMBER
1645 ,p_payroll_id IN NUMBER
1646 ,p_business_group_id IN NUMBER
1647 ,p_plan_id IN NUMBER
1648 ,p_calculation_date IN DATE
1649 ,p_net_accrual OUT NOCOPY NUMBER
1650 ,p_net_entitlement OUT NOCOPY NUMBER
1651 ,p_calc_start_date OUT NOCOPY DATE
1652 ,p_last_accrual OUT NOCOPY DATE
1653 ,p_next_period_end OUT NOCOPY DATE
1654 ) RETURN NUMBER IS
1655 -- The stages of the calculation are as follows
1656 --
1657 -- 1. Find the anniversary dates for the given calculation date
1658 -- using the get_carryover_values...this will allow us to
1659 -- obtain entitlement end date
1660 --
1661 -- 2: Find net leave at entitlement end date using the core
1662 -- get_net_accrual Function.
1663 --
1664 -- 3: Find the total net leave up to the calculation date using
1665 -- the core get_net_accrual function.
1666 --
1667 -- 4: Find Leave Accrual Initialise during period
1668 --
1669 -- 5: Find Leave Entitlement Initialise during period
1670 --
1671 -- 6: Net entitlement = step 2 + step 5
1672 --
1673 -- 7: Net accrual = (step 3 + step 5 - step 6)
1674 --
1675 l_procedure constant varchar2(72) := g_package||'.get_accrual_entitlement';
1676 l_adjustment_element varchar2(100);
1677 l_initialise_element varchar2(100);
1678 --
1679 l_anniversary_start_date date;
1680 l_anniversary_end_date date;
1681 l_start_date date;
1682 l_end_date date;
1683 l_accrual_end_date date;
1684 l_entitlement_period_end_date date;
1685 l_accrual_period_start_date date;
1686 l_continuous_service_date date;
1687
1688 --
1689 l_co_formula_id number;
1690 l_max_co number;
1691 l_accrual_adj number;
1692 l_accrual_ent number;
1693 l_entitlement_adj number;
1694 l_net_accrual number;
1695 l_net_entitlement number;
1696 l_accrual number;
1697 l_leave_entitlement number;
1698 l_leave_total number;
1699 l_others_entitlement number;
1700 l_others_accrual number;
1701 l_accrual_absences number;
1702 l_leave_accrual number;
1703 --
1704 l_accrual_period_end_date date;
1705 l_calculation_date date;
1706 --
1707 cursor c_get_co_formula
1708 (p_accrual_plan_id number) is
1709 select co_formula_id
1710 from pay_accrual_plans
1711 where accrual_plan_id = p_accrual_plan_id;
1712 --
1713 BEGIN
1714 --
1715 g_debug := hr_utility.debug_enabled;
1716 if g_debug then
1717 hr_utility.set_location(l_procedure, 0);
1718 hr_utility.trace('p_calculation_date = '||to_char(p_calculation_date,'DD-MON-YYYY'));
1719 end if;
1720 --
1721 l_calculation_date := p_calculation_date;
1722 --
1723 -- Step 1 Find entitlement end date
1724 -- first get the carryover formula then call it
1728 --
1725 -- to get the prev and next anniversary dates.
1726 -- Entitlement end date and accrual end dates are
1727 -- actually the day before the anniversary dates.
1729 open c_get_co_formula (p_plan_id);
1730 fetch c_get_co_formula into l_co_formula_id;
1731 close c_get_co_formula;
1732 --
1733 if g_debug then
1734 hr_utility.set_location(l_procedure, 10);
1735 end if;
1736 --
1737 --------------------------------------------------------
1738 --
1739 -- GET ENTITLEMENT PERIOD DETAILS
1740 --
1741 --------------------------------------------------------
1742 -- The carryover formula's have been written to
1743 -- get the anniversary start and anniversary end dates
1744 -- for the anniversary period as of the calculation date
1745 -- when called in the mode NZ_FORM
1746 --------------------------------------------------------
1747 --
1748
1749 per_accrual_calc_functions.get_carry_over_values
1750 (p_co_formula_id => l_co_formula_id
1751 ,p_assignment_id => p_assignment_id
1752 ,p_calculation_date => l_calculation_date
1753 ,p_accrual_plan_id => p_plan_id
1754 ,p_business_group_id => p_business_group_id
1755 ,p_payroll_id => p_payroll_id
1756 ,p_accrual_term => 'NZ_FORM'
1757 ,p_effective_date => l_anniversary_start_date --l_accrual_period_start_date
1758 ,p_session_date => l_calculation_date
1759 ,p_max_carry_over => l_max_co
1760 ,p_expiry_date => l_anniversary_end_date --l_accrual_period_end_date
1761 );
1762 --
1763 -- The entitlement end date is the day before the start
1764 -- of the current anniversary period... unless the current anniversary
1765 -- period is the first anniversary period, in which case there will be
1766 -- no entitlement only accrual.
1767 --
1768 -- Before first anniversary date accrual_period_start_date = start_date
1769 -- in this case l_max_co will be set to 1 (for sick leave only)
1770 --
1771 l_accrual_period_start_date := l_anniversary_start_date;
1772 l_accrual_period_end_date := l_anniversary_end_date;
1773 --
1774 if l_max_co = 1 then
1775 l_entitlement_period_end_date := l_accrual_period_start_date;
1776 else
1777 l_entitlement_period_end_date := (l_accrual_period_start_date - 1);
1778 end if;
1779 --
1780 if g_debug then
1781 hr_utility.set_location(l_procedure,5);
1782 hr_utility.trace('l_entitlement_period_end_date = '||to_char(l_entitlement_period_end_date,'DD-MON-YYYY'));
1783 hr_utility.trace('l_accrual_period_start_date.. = '||to_char(l_accrual_period_start_date,'DD-MON-YYYY'));
1784 hr_utility.trace('l_accrual_period_endt_date... = '||to_char(l_accrual_period_end_date,'DD-MON-YYYY'));
1785 hr_utility.trace('l_continuous_service_date.... = '||to_char(l_continuous_service_date,'DD-MON-YYYY'));
1786 end if;
1787 --
1788 -------------------------------------------------------
1789 --
1790 -- GET THE NET LEAVE AMOUNT FOR THE ENTITLEMENT PERIOD
1791 --
1792 -------------------------------------------------------
1793 -- Step two find the Net leave at entitlement end date
1794
1795 -- Get the amount of leave which goes toward ENTITLEMENT
1796 -- Sum from start of plan until day before start of
1797 -- current anniversary period
1798 --
1799 -------------------------------------------------------
1800 --
1801
1802 per_accrual_calc_functions.get_net_accrual
1803 (p_assignment_id => p_assignment_id
1804 ,p_plan_id => p_plan_id
1805 ,p_payroll_id => p_payroll_id
1806 ,p_business_group_id => p_business_group_id
1807 ,p_calculation_date => l_entitlement_period_end_date
1808 ,p_start_date => l_start_date
1809 ,p_end_date => l_end_date
1810 ,p_accrual_end_date => l_accrual_end_date
1811 ,p_accrual => l_accrual
1812 ,p_net_entitlement => l_leave_entitlement
1813 );
1814 --
1815 -- Net Entitlement is not used because the net calculation
1816 -- of leave is done manually to allow for absences to be taken
1817 -- from entitlement before accrual
1818 --
1819 l_leave_entitlement := l_accrual;
1820 --
1821 l_others_entitlement := per_accrual_calc_functions.get_other_net_contribution
1822 (p_assignment_id => p_assignment_id
1823 ,p_plan_id => p_plan_id
1824 ,p_start_date => l_start_date
1825 ,p_calculation_date => l_entitlement_period_end_date
1826 ) ;
1827
1828 --
1829 -------------------------------------------------------
1830 --
1831 -- GET THE NET LEAVE TOTAL
1832 --
1833 -------------------------------------------------------
1834 -- Find the Net leave at the calculation_date
1835 -------------------------------------------------------
1836 --
1837 per_accrual_calc_functions.get_net_accrual
1838 (p_assignment_id => p_assignment_id
1839 ,p_plan_id => p_plan_id
1840 ,p_payroll_id => p_payroll_id
1841 ,p_business_group_id => p_business_group_id
1842 ,p_calculation_date => l_calculation_date
1846 ,p_accrual => l_accrual
1843 ,p_start_date => l_start_date
1844 ,p_end_date => l_end_date
1845 ,p_accrual_end_date => l_accrual_end_date
1847 ,p_net_entitlement => l_leave_total
1848 );
1849 --
1850 -- Net Entitlement is not used because the net calculation
1851 -- of leave is done manually to allow for absences to be taken
1852 -- from entitlement before accrual
1853 --
1854 l_leave_total := l_accrual;
1855 --
1856 ------------------------------------------------
1857 --
1858 -- GET THE ADJUSTMENT VALUES FOR ENTITLEMENT
1859 -- We search the entrie accrual period
1860 -- for entitlement adjustments since entitlement
1861 -- is always entitlement.
1862 -- Note: also if the accruals are being run
1863 -- before the entitlement period is reached
1864 -- then the function get_net_accrual does not
1865 -- return a start and end date.
1866 --
1867 ------------------------------------------------
1868 --
1869 -- We search the entire date range since entitlement
1870 -- adjustments are always entitlement
1871 --
1872 l_adjustment_element:= 'Entitlement Adjustment Element';
1873 l_entitlement_adj:= hr_nz_holidays.get_adjustment_values
1874 (p_assignment_id => p_assignment_id
1875 ,p_accrual_plan_id => p_plan_id
1876 ,p_calc_end_date => l_calculation_date
1877 ,p_adjustment_element => l_adjustment_element
1878 ,p_start_date => l_start_date
1879 ,p_end_date => l_end_date
1880 );
1881 if g_debug then
1882 hr_utility.set_location(l_procedure, 40);
1883 hr_utility.trace('l_start_date = '||to_char(l_start_date));
1884 hr_utility.trace('l_end_date = '||to_char(l_end_date));
1885 hr_utility.trace('l_calculation_date = '||to_char(l_calculation_date));
1886 hr_utility.trace('l_entitlement_adj = '||to_char(l_entitlement_adj));
1887 end if;
1888 --
1889
1890 ------------------------------------------------
1891 --
1892 -- GET THE ADJUSTMENT VALUES FOR ACCRUAL
1893 --
1894 ------------------------------------------------
1895 --
1896 -- We need to check for adjustments within
1897 -- the accrual period (ie accrual_period_start_date until calculation_date
1898 -- and the entitlement period (ie start_date until entitlement_period_end_date)
1899 --
1900 l_adjustment_element := 'Accrual Adjustment Element';
1901 l_accrual_adj:= hr_nz_holidays.get_adjustment_values
1902 (p_assignment_id => p_assignment_id
1903 ,p_accrual_plan_id => p_plan_id
1904 ,p_calc_end_date => l_calculation_date
1905 ,p_adjustment_element => l_adjustment_element
1906 ,p_start_date => l_entitlement_period_end_date
1907 ,p_end_date => l_calculation_date
1908 );
1909 if g_debug then
1910 hr_utility.set_location(l_procedure, 50);
1911 hr_utility.trace('l_accrual_adj = '||to_char(l_accrual_adj));
1912 end if;
1913
1914 --
1915 -- Accrual Adjustments which occured during the
1916 -- entitlement period become entitlement
1917 -- Add to the existing entitlement adjustments
1918 --
1919 l_accrual_ent := hr_nz_holidays.get_adjustment_values
1920 (p_assignment_id => p_assignment_id
1921 ,p_accrual_plan_id => p_plan_id
1922 ,p_calc_end_date => l_calculation_date
1923 ,p_adjustment_element => l_adjustment_element
1924 ,p_start_date => l_start_date
1925 ,p_end_date => l_end_date
1926 );
1927 --
1928 if g_debug then
1929 hr_utility.trace('l_entitlement_adj = '||to_char(l_entitlement_adj));
1930 hr_utility.trace('l_accrual_adj.... = '||to_char(l_accrual_adj));
1931 end if;
1932 l_others_accrual := per_accrual_calc_functions.get_other_net_contribution
1933 (p_assignment_id => p_assignment_id
1934 ,p_plan_id => p_plan_id
1935 ,p_start_date => l_accrual_period_start_date
1936 ,p_calculation_date => p_calculation_date
1937 ) ;
1938 --
1939 -- Find out the numder of hours taken during the accrual period
1940 -- If max_co is 1 then no accrual only entitlement
1941 --
1942 if l_max_co = 1
1943 then
1944 l_accrual_absences := per_accrual_calc_functions.get_absence
1945 (p_assignment_id => p_assignment_id
1946 ,p_plan_id => p_plan_id
1947 ,p_start_date => l_accrual_period_start_date
1948 ,p_calculation_date => p_calculation_date
1949 );
1950 if g_debug then
1951 hr_utility.set_location(l_procedure, 60);
1952 hr_utility.trace('..l_accrual_absences = '||to_char(l_accrual_absences));
1953 hr_utility.trace('..l_start_date = '||to_char(l_start_date,'dd/mm/rrrr'));
1957 l_leave_accrual := 0;
1954 hr_utility.trace('..l_calculation_date = '||to_char(l_calculation_date,'dd/mm/rrrr'));
1955 end if;
1956 l_leave_entitlement := l_leave_total - l_accrual_absences;
1958 --
1959 else
1960 l_accrual_absences := per_accrual_calc_functions.get_absence
1961 (p_assignment_id => p_assignment_id
1962 ,p_plan_id => p_plan_id
1963 ,p_start_date => l_start_date
1964 ,p_calculation_date => p_calculation_date
1965 );
1966 if g_debug then
1967 hr_utility.set_location(l_procedure, 70);
1968 hr_utility.trace('..l_start_date = '||to_char(l_start_date,'dd/mm/rrrr'));
1969 hr_utility.trace('..l_calculation_date = '||to_char(l_calculation_date,'dd/mm/rrrr'));
1970 hr_utility.trace('..l_accrual_absences = '||to_char(l_accrual_absences));
1971 end if;
1972 --
1973 -- Get the net entitlement and accrual before checking for absences
1974 -- Determine the amount to go towards accrual portion by subtracting
1975 -- entitlement portion from total
1976 --
1977 if g_debug then
1978 hr_utility.set_location(l_procedure, 75);
1979 hr_utility.trace('l_leave_total = '||to_char(l_leave_total));
1980 hr_utility.trace('l_leave_entitlement = '||to_char(l_leave_entitlement));
1981 end if;
1982
1983 l_leave_accrual := l_leave_total - l_leave_entitlement;
1984 --
1985 l_leave_accrual := l_leave_accrual + l_others_accrual + l_accrual_adj;
1986
1987 -- First year is accrual, not entitlement l_accrual_adj is subtracted.
1988 -- As l_accrual_adj returns value only in the first anniversary year,
1989 -- l_accrual_adj and l_accrual_ent is nullified in the first year and from
1990 -- second year onwards adjusted accrual value is added to the entitlement
1991
1992 l_leave_entitlement := l_leave_entitlement + l_others_entitlement + l_entitlement_adj + l_accrual_ent - l_accrual_adj;
1993 if g_debug then
1994 hr_utility.set_location(l_procedure, 80);
1995 hr_utility.trace('..l_leave_entitlement = '||to_char(l_leave_entitlement));
1996 hr_utility.trace('..l_others_entitlement = '||to_char(l_others_entitlement));
1997 hr_utility.trace('..l_entitlement_adj = '||to_char(l_entitlement_adj));
1998 hr_utility.trace('..l_accrual_ent = '||to_char(l_accrual_ent));
1999 hr_utility.trace('..l_accrual_adj = '||to_char(l_accrual_adj));
2000 hr_utility.trace('..l_leave_accrual = '||to_char(l_leave_accrual));
2001 end if;
2002
2003 -- have to subtract absences taken to calculate net entitlement
2004 -- absences must come off entitlement before accrual
2005 --
2006 if l_leave_entitlement > l_accrual_absences
2007 then
2008 l_leave_entitlement := l_leave_entitlement - l_accrual_absences;
2009 else
2010 -- Subtract from entitlement and leftovers from accrual
2011 l_leave_accrual := l_leave_accrual - (l_accrual_absences - l_leave_entitlement);
2012 l_leave_entitlement := 0;
2013 end if;
2014
2015 end if;
2016
2017 --
2018 -- set up return values
2019 --
2020 p_net_accrual := round(nvl(l_leave_accrual, 0), 3);
2021 p_net_entitlement := round(nvl(l_leave_entitlement, 0), 3);
2022 p_calc_start_date := l_start_date;
2023 p_last_accrual := l_accrual_end_date;
2024 p_next_period_end := l_accrual_period_end_date;
2025 --
2026 if g_debug then
2027 hr_utility.set_location(l_procedure,999);
2028 end if;
2029 --
2030 RETURN (0);
2031 --
2032 END get_accrual_entitlement;
2033 --
2034 ---------------------------------------------------------------------
2035 --
2036 -- ANNUAL_LEAVE_NET_ENTITLEMENT
2037 --
2038 -- Purpose : Wraps get_accrual_entitlement with parameters
2039 -- to match the Leave liability process.
2040 -- Returns : Total entitlement
2041 --
2042 ---------------------------------------------------------------------
2043
2044 PROCEDURE annual_leave_net_entitlement
2045 (p_assignment_id IN NUMBER
2046 ,p_payroll_id IN NUMBER
2047 ,p_business_group_id IN NUMBER
2048 ,p_plan_id IN NUMBER
2049 ,p_calculation_date IN DATE
2050 ,p_start_date OUT NOCOPY DATE
2051 ,p_end_date OUT NOCOPY DATE
2052 ,p_net_entitlement OUT NOCOPY NUMBER
2053 ) IS
2054 --
2055 -- Local Variables
2056 --
2057 l_proc CONSTANT VARCHAR2(72) := g_package||'annual_leave_net_entitlement';
2058 l_net_accrual NUMBER;
2059 l_net_entitlement NUMBER;
2060 l_calc_start_date DATE;
2061 l_last_accrual DATE;
2062 l_next_period_end DATE;
2063 l_dummy_number NUMBER;
2064 --
2065 BEGIN
2066 g_debug := hr_utility.debug_enabled;
2067 --
2068 if g_debug then
2069 hr_utility.trace(' In: ' || l_proc) ;
2070 end if;
2071 --
2072 l_dummy_number := get_accrual_entitlement
2073 (p_assignment_id
2074 ,p_payroll_id
2075 ,p_business_group_id
2079 ,l_net_entitlement
2076 ,p_plan_id
2077 ,p_calculation_date
2078 ,l_net_accrual
2080 ,l_calc_start_date
2081 ,l_last_accrual
2082 ,l_next_period_end
2083 );
2084 --
2085 p_net_entitlement := l_net_entitlement;
2086 p_start_date := l_calc_start_date;
2087 p_end_date := p_calculation_date;
2088 if g_debug then
2089 hr_utility.trace('Out: ' || l_proc);
2090 end if;
2091
2092 END annual_leave_net_entitlement;
2093 --
2094
2095 function get_previous_rate
2096 (p_element_type_id number
2097 ,p_assignment_action_id number
2098 ,p_rate_name varchar2
2099 ) return number is
2100 --
2101 l_procedure constant varchar2(72) := g_package||'get_previous_rate';
2102 --
2103 /*Bug 3654766 - Changed the cursor to improve performance and also
2104 to consider the assignment action id into account.*/
2105 cursor csr_get_rate is
2106 select to_number(result.result_value)
2107 from pay_run_results runs
2108 , pay_input_values_f input
2109 , pay_run_result_values result
2110 , pay_assignment_actions paa
2111 , pay_assignment_actions cur_paa
2112 , pay_payroll_actions cur_ppa
2113 , pay_element_entries_f entry
2114 , per_time_periods ptp
2115 where runs.element_type_id = p_element_type_id
2116 and input.element_type_id = runs.element_type_id
2117 and input.name = p_rate_name
2118 and result.run_result_id = runs.run_result_id
2119 and result.input_value_id = input.input_value_id
2120 and cur_paa.assignment_action_id = p_assignment_action_id
2121 and cur_ppa.payroll_action_id = cur_paa.payroll_action_id
2122 and paa.assignment_action_id = runs.assignment_action_id
2123 and paa.assignment_id = cur_paa.assignment_id
2124 and entry.assignment_id = cur_paa.assignment_id
2125 and entry.element_entry_id = runs.source_id
2126 and ptp.time_period_id = cur_ppa.time_period_id
2127 and cur_ppa.effective_date between input.effective_start_date
2128 and input.effective_end_date
2129 and (ptp.start_date between entry.effective_start_date
2130 and entry.effective_end_date
2131 or ptp.end_date between entry.effective_start_date
2132 and entry.effective_end_date);
2133
2134 l_previous_rate number;
2135 --
2136 begin
2137 --
2138 g_debug := hr_utility.debug_enabled;
2139 --
2140 if g_debug then
2141 hr_utility.set_location(l_procedure, 0) ;
2142 end if;
2143 --
2144 open csr_get_rate;
2145 fetch csr_get_rate into l_previous_rate;
2146 if csr_get_rate%notfound then
2147 l_previous_rate := 0;
2148 end if;
2149 close csr_get_rate;
2150 --
2151 if g_debug then
2152 hr_utility.trace('l_previous_rate = '||to_char(l_previous_rate));
2153 hr_utility.set_location(l_procedure, 999);
2154 end if;
2155 --
2156 --Bug 3620398: Changed the function to return l_previous_rate.
2157 --
2158 return l_previous_rate;
2159 --
2160 end get_previous_rate;
2161 --
2162
2163 -- Bug 3608752
2164 -- Parental leave changes
2165 FUNCTION is_parental_leave_taken(
2166 p_assignment_id IN NUMBER
2167 ,p_business_group_id IN NUMBER
2168 ,p_start_date IN DATE
2169 ,p_end_date IN DATE
2170 )
2171 RETURN NUMBER
2172 IS
2173 --
2174 CURSOR csr_parental_leave_taken(c_assignment_id IN NUMBER
2175 ,c_business_group_id IN NUMBER
2176 ,c_start_date IN DATE
2177 ,c_end_date IN DATE)
2178 IS
2179 --
2180 SELECT 1
2181 FROM per_absence_attendances paa
2182 ,per_absence_attendance_types paat
2183 ,per_assignments_f paf
2184 WHERE paa.person_id = paf.person_id
2185 AND paf.assignment_id = p_assignment_id
2186 AND paa.business_group_id = c_business_group_id
2187 AND paa.business_group_id = paat.business_group_id
2188 AND paa.absence_attendance_type_id = paat.absence_attendance_type_id
2189 AND paat.absence_category = 'NZPL'
2190 AND (paa.date_start BETWEEN c_start_date
2191 AND c_end_date
2192 OR paa.date_end BETWEEN c_start_date
2193 AND c_end_date )
2194 AND c_end_date BETWEEN paf.effective_start_date
2195 AND paf.effective_end_date;
2196 --
2197 l_pleave_taken NUMBER;
2198 l_procedure CONSTANT VARCHAR2(100) := g_package||'.is_parental_leave_taken';
2199 --
2200 BEGIN
2201 --
2202 l_pleave_taken := 0;
2206 hr_utility.set_location('NZ p_start_date : ' || to_char(p_start_date,'dd Mon yyyy'),10) ;
2203 hr_utility.set_location('NZ Entering : ' || l_procedure,10);
2204 hr_utility.set_location('NZ p_assignment_id : ' || to_char(p_assignment_id),10) ;
2205 hr_utility.set_location('NZ p_business_group_id : ' || to_char(p_business_group_id),10);
2207 hr_utility.set_location('NZ p_end_date : ' || to_char(p_end_date,'dd Mon yyyy'),10) ;
2208
2209 OPEN csr_parental_leave_taken(p_assignment_id
2210 ,p_business_group_id
2211 ,p_start_date
2212 ,p_end_date);
2213 FETCH csr_parental_leave_taken
2214 INTO l_pleave_taken;
2215
2216 IF csr_parental_leave_taken%FOUND THEN
2217 --
2218 CLOSE csr_parental_leave_taken;
2219 hr_utility.set_location('NZ l_pleave_taken : ' || to_char(l_pleave_taken),20);
2220 hr_utility.set_location('NZ Leaving : ' || l_procedure,20);
2221 RETURN 1;
2222 --
2223 END IF;
2224 --
2225 CLOSE csr_parental_leave_taken;
2226 hr_utility.set_location('NZ No parental leave taken',30);
2227 hr_utility.set_location('NZ Leaving : ' || l_procedure,30);
2228 RETURN 0;
2229 --
2230 EXCEPTION
2231 --
2232 WHEN others THEN
2233 --
2234 IF csr_parental_leave_taken%ISOPEN THEN
2235 --
2236 CLOSE csr_parental_leave_taken;
2237 --
2238 END IF;
2239 hr_utility.set_location('NZ Error in : ' || l_procedure,40);
2240 RAISE;
2241 --
2242 --
2243 END is_parental_leave_taken;
2244
2245 FUNCTION get_recur_abs_prev_period(
2246 p_assignment_id IN NUMBER
2247 ,p_payroll_id IN NUMBER
2248 ,p_absence_start_date IN DATE
2249 ,p_curr_aniv_start IN DATE
2250 ,p_prev_period_end_date IN DATE
2251 ,p_plan_id IN NUMBER
2252 )
2253 RETURN NUMBER
2254 IS
2255 --
2256 CURSOR get_pay_period_details(c_eff_date IN DATE) IS
2257 --
2258 SELECT ptp.time_period_id
2259 ,ptp.end_date
2260 FROM per_time_periods ptp
2261 WHERE ptp.payroll_id = p_payroll_id
2262 AND c_eff_date BETWEEN ptp.start_date
2263 AND ptp.end_date;
2264 --
2265
2266 CURSOR get_period_assg_act_id(c_time_period_id IN NUMBER)
2267 IS
2268 --
2269 SELECT max(paa.assignment_action_id)
2270 FROM pay_payroll_actions ppa
2271 ,pay_assignment_actions paa
2272 WHERE ppa.payroll_id = p_payroll_id
2273 AND ppa.time_period_id = c_time_period_id
2274 AND ppa.action_type IN ('R','Q')
2275 AND ppa.action_status = 'C'
2276 AND ppa.payroll_action_id = paa.payroll_action_id
2277 AND paa.assignment_id = p_assignment_id
2278 AND paa.action_status = 'C';
2279 --
2280
2281 CURSOR csr_get_recurr_absence(c_assignment_action_id IN NUMBER
2282 ,c_prev_period_end_date IN DATE)
2283 IS
2284 --
2285 SELECT to_number(nvl(prrv.result_value,0))
2286 FROM pay_run_result_values prrv
2287 ,pay_run_results prr
2288 ,pay_element_types_f alp_pet
2289 ,pay_input_values_f alp_piv
2290 ,pay_element_entries_f pee
2291 ,pay_element_links_f pel
2292 ,pay_element_types_f abs_pet
2293 ,pay_input_values_f abs_piv
2294 ,pay_accrual_plans pap
2295 WHERE prr.run_result_id = prrv.run_result_id
2296 AND prr.assignment_action_id = c_assignment_action_id
2297 AND prr.element_type_id = alp_pet.element_type_id
2298 AND alp_pet.element_name = 'Annual Leave Payment'
2299 AND alp_pet.element_type_id = alp_piv.element_type_id
2300 AND alp_piv.name = 'Leave Taken'
2301 AND alp_piv.input_value_id = prrv.input_value_id
2302 AND prr.source_id = pee.element_entry_id
2303 AND pee.creator_type = 'A'
2304 AND pee.effective_end_date > c_prev_period_end_date
2305 AND pee.element_link_id = pel.element_link_id
2306 AND pel.element_type_id = abs_pet.element_type_id
2307 AND abs_pet.processing_type = 'R'
2308 AND abs_pet.element_type_id = abs_piv.element_type_id
2309 AND abs_piv.input_value_id = pap.pto_input_value_id
2310 AND pap.accrual_plan_id = p_plan_id
2311 AND c_prev_period_end_date BETWEEN alp_pet.effective_start_date
2312 AND alp_pet.effective_end_date
2313 AND c_prev_period_end_date BETWEEN alp_piv.effective_start_date
2314 AND alp_piv.effective_end_date
2315 AND c_prev_period_end_date BETWEEN pel.effective_start_date
2316 AND pel.effective_end_date
2320 AND abs_piv.effective_end_date;
2317 AND c_prev_period_end_date BETWEEN abs_pet.effective_start_date
2318 AND abs_pet.effective_end_date
2319 AND c_prev_period_end_date BETWEEN abs_piv.effective_start_date
2321 --
2322 l_eff_date DATE;
2323 l_recurr_leave_taken NUMBER;
2324 l_leave_taken NUMBER;
2325 l_period_assg_act_id NUMBER;
2326 l_time_period_id NUMBER;
2327 l_period_end_date DATE;
2328 l_procedure CONSTANT VARCHAR2(100) := g_package||'.get_recur_abs_prev_period';
2329 --
2330 BEGIN
2331 --
2332 hr_utility.set_location('NZ Entering : ' ||l_procedure,10 );
2333 hr_utility.set_location('NZ p_absence_start_date : ' ||p_absence_start_date,10 );
2334 hr_utility.set_location('NZ p_curr_aniv_start : ' ||p_curr_aniv_start ,10 );
2335 hr_utility.set_location('NZ p_prev_period_end_date: ' ||p_prev_period_end_date ,10);
2336 hr_utility.set_location('NZ p_plan_id : ' ||p_plan_id ,10);
2337 l_recurr_leave_taken := 0;
2338 IF (p_curr_aniv_start <= p_absence_start_date ) THEN
2339 --
2340 -- Loop through starting from the absence start date
2341 -- Till Previous period end date to find the recurring
2342 -- absence processed in the previous runs
2343 l_eff_date := p_absence_start_date;
2344 WHILE (l_eff_date <= p_prev_period_end_date)
2345 LOOP
2346 --
2347 OPEN get_pay_period_details(l_eff_date);
2348 FETCH get_pay_period_details
2349 INTO l_time_period_id,l_period_end_date;
2350 CLOSE get_pay_period_details;
2351
2352 hr_utility.set_location('NZ l_time_period_id : ' || l_time_period_id,20);
2353 hr_utility.set_location('NZ l_prev_period_end_date : ' || l_period_end_date,20);
2354
2355 OPEN get_period_assg_act_id(l_time_period_id);
2356 FETCH get_period_assg_act_id
2357 INTO l_period_assg_act_id;
2358 CLOSE get_period_assg_act_id;
2359
2360 hr_utility.set_location('NZ l_prev_period_assg_act_id : ' || l_period_assg_act_id,30);
2361
2362 OPEN csr_get_recurr_absence(l_period_assg_act_id
2363 ,p_prev_period_end_date);
2364 FETCH csr_get_recurr_absence
2365 INTO l_leave_taken;
2366
2367 IF csr_get_recurr_absence%FOUND THEN
2368 --
2369 hr_utility.set_location('NZ l_leave_taken : ' || l_leave_taken,40);
2370
2371 l_recurr_leave_taken := l_recurr_leave_taken + l_leave_taken;
2372 --
2373 ELSE
2374 --
2375 hr_utility.set_location('NZ No recurring absence in previous periods ',50);
2376 --
2377 END IF;
2378
2379 CLOSE csr_get_recurr_absence;
2380
2381 -- Move to next period
2382 l_eff_date := l_period_end_date +1;
2383 --
2384 END LOOP;
2385 --
2386 END IF;
2387 hr_utility.set_location('NZ Leaving : ' ||l_procedure,60 );
2388 RETURN l_recurr_leave_taken;
2389 --
2390 EXCEPTION
2391 --
2392 WHEN others THEN
2393 --
2394 IF get_pay_period_details%ISOPEN THEN
2395 --
2396 CLOSE get_pay_period_details;
2397 --
2398 END IF;
2399 IF get_period_assg_act_id%ISOPEN THEN
2400 --
2401 CLOSE get_period_assg_act_id;
2402 --
2403 END IF;
2404 IF csr_get_recurr_absence%ISOPEN THEN
2405 --
2406 CLOSE csr_get_recurr_absence;
2407 --
2408 END IF;
2409
2410 hr_utility.set_location('NZ Error in : ' ||l_procedure,70 );
2411 RAISE;
2412 --
2413 END get_recur_abs_prev_period;
2414
2415 FUNCTION get_leave_taken (
2416 p_assignment_id IN NUMBER
2417 ,p_payroll_id IN NUMBER
2418 ,p_business_group_id IN NUMBER
2419 ,p_start_date IN DATE
2420 ,p_end_date IN DATE
2421 ,p_curr_aniv_start IN DATE
2422 ,p_plan_id IN NUMBER
2423 ,p_absence_start_date IN DATE
2424 )
2425 RETURN NUMBER
2426 IS
2427 --
2428 CURSOR csr_get_count_leave(c_assignment_id IN NUMBER
2429 ,c_business_group_id IN NUMBER
2430 ,c_start_date IN DATE
2431 ,c_end_date IN DATE)
2432 IS
2433 --
2434 SELECT nvl(nvl(sum(absence_days),sum(absence_hours)),0) cnt_abs
2435 FROM per_absence_attendances paa
2436 ,per_absence_attendance_types paat
2437 ,pay_accrual_plans pap
2438 ,per_assignments_f paf
2439 WHERE paa.person_id = paf.person_id
2440 AND paf.assignment_id = c_assignment_id
2441 AND paa.business_group_id = c_business_group_id
2442 AND paa.absence_attendance_type_id = paat.absence_attendance_type_id
2443 AND pap.accrual_plan_id = p_plan_id
2444 AND pap.accrual_category = 'NZAL'
2448 AND c_end_date
2445 AND paa.date_start BETWEEN c_start_date
2446 AND c_end_date
2447 AND paa.date_end BETWEEN c_start_date
2449 AND c_end_date BETWEEN paf.effective_start_date
2450 AND paf.effective_end_date;
2451 --
2452
2453
2454 l_days NUMBER;
2455 l_hours NUMBER;
2456 l_leave_taken NUMBER;
2457 l_recurr_leave_prev_period NUMBER;
2458 l_time_period_id NUMBER;
2459 l_period_end_date DATE;
2460 l_period_assg_act_id NUMBER;
2461 l_recurr_leave_taken NUMBER;
2462 l_eff_date DATE;
2463 l_procedure CONSTANT VARCHAR2(100) := g_package||'.get_leave_taken';
2464 --
2465 BEGIN
2466 --
2467 hr_utility.set_location('NZ Entering : ' || l_procedure,10);
2468 hr_utility.set_location('NZ p_assignment_id : ' || to_char(p_assignment_id),10);
2469 hr_utility.set_location('NZ p_payroll_id : ' || p_payroll_id,10);
2470 hr_utility.set_location('NZ p_business_group_id : ' ||to_char(p_business_group_id),10);
2471 hr_utility.set_location('NZ p_start_date : ' || to_char(p_start_date,'dd Mon yyyy'),10);
2472 hr_utility.set_location('NZ p_end_date : ' || to_char(p_end_date,'dd Mon yyyy'),10);
2473
2474 OPEN csr_get_count_leave(p_assignment_id
2475 ,p_business_group_id
2476 ,p_start_date
2477 ,p_end_date);
2478 FETCH csr_get_count_leave
2479 INTO l_leave_taken;
2480 CLOSE csr_get_count_leave;
2481
2482 -- Now find out whether there is any recurring absence that
2483 -- got processed in previous period
2484 -- If the recurring absence started in the previous Anniversary then
2485 -- it will get included in the Net Accrual of the previous Year.
2486 -- So the following calculation need not be done
2487
2488 l_recurr_leave_taken := pay_nz_holidays_2003.get_recur_abs_prev_period
2489 (
2490 p_assignment_id => p_assignment_id
2491 ,p_payroll_id => p_payroll_id
2492 ,p_absence_start_date => p_absence_start_date
2493 ,p_curr_aniv_start => p_curr_aniv_start
2494 ,p_prev_period_end_date => p_end_date
2495 ,p_plan_id => p_plan_id
2496 );
2497 l_leave_taken := l_leave_taken + l_recurr_leave_taken;
2498 hr_utility.set_location('NZ l_leave_taken : ' || l_leave_taken,20);
2499 hr_utility.set_location('NZ Leaving : ' || l_procedure,30);
2500
2501 RETURN l_leave_taken;
2502
2503 --
2504 EXCEPTION
2505 --
2506 WHEN others THEN
2507 --
2508 IF csr_get_count_leave%ISOPEN THEN
2509 --
2510 CLOSE csr_get_count_leave;
2511 --
2512 END IF;
2513
2514 hr_utility.set_location('NZ Erroring in : ' || l_procedure,40);
2515 RAISE;
2516 --
2517 --
2518 END get_leave_taken;
2519
2520
2521
2522 FUNCTION get_entitled_amount(
2523 p_payroll_id NUMBER
2524 ,p_payroll_action_id NUMBER
2525 ,p_assignment_id NUMBER
2526 ,p_business_group_id NUMBER
2527 ,p_accrual_plan_id NUMBER
2528 ,p_absence_start_date DATE
2529 ,p_period_start_date DATE
2530 ,p_period_end_date DATE
2531 ,p_entitled_leave_taken NUMBER
2532 ,p_curr_rate NUMBER
2533 ,p_hire_date DATE
2534 ,p_average_rate_p12mths NUMBER
2535 )
2536 RETURN NUMBER
2537 IS
2538 --
2539
2540 CURSOR get_curr_period_start
2541 IS
2542 --
2543 SELECT ptp.start_date
2544 FROM per_time_periods ptp
2545 ,pay_payroll_actions ppa
2546 WHERE ppa.payroll_action_id = p_payroll_action_id
2547 AND ppa.time_period_id = ptp.time_period_id;
2548 --
2549 l_eff_date DATE;
2550 l_amt NUMBER;
2551 l_dummy NUMBER;
2552 l_anniversary_start_date DATE;
2553 l_anniversary_end_date DATE;
2554 l_years_of_service NUMBER;
2555 l_net_accrual NUMBER;
2556 l_net_entitlement NUMBER;
2557 l_calc_start_date DATE;
2558 l_last_accrual DATE;
2559 l_next_period_end DATE;
2560 l_has_taken_pl NUMBER;
2561 l_rate NUMBER;
2562 l_period_rate NUMBER;
2563 l_def_bal_id NUMBER;
2564 l_gross_earnings_ytd NUMBER;
2565 l_time_worked_ytd NUMBER;
2566 l_std_work_hours NUMBER;
2567 l_entitled_leave_taken NUMBER;
2568 l_leave_taken NUMBER;
2572 --
2569 l_curr_start_date DATE;
2570 l_leave_available NUMBER;
2571 l_procedure CONSTANT VARCHAR2(100) := g_package||'get_entitled_amount';
2573 BEGIN
2574 --
2575 hr_utility.set_location('NZ Entering : ' || l_procedure ,10);
2576 hr_utility.set_location('NZ p_payroll_id : ' || p_payroll_id ,10);
2577 hr_utility.set_location('NZ p_assignment_id : ' || p_assignment_id ,10);
2578 hr_utility.set_location('NZ p_business_group_id : ' || p_business_group_id ,10);
2579 hr_utility.set_location('NZ p_accrual_plan_id : ' || p_accrual_plan_id,10);
2580 hr_utility.set_location('NZ p_period_start_date : ' || p_period_start_date ,10);
2581 hr_utility.set_location('NZ p_period_end_date : ' || p_period_end_date ,10);
2582 hr_utility.set_location('NZ p_entitled_leave_taken : ' || p_entitled_leave_taken ,10);
2583 hr_utility.set_location('NZ p_curr_rate : ' || p_curr_rate,10);
2584 hr_utility.set_location('NZ p_hire_date : ' || p_hire_date,10);
2585 hr_utility.set_location('NZ p_average_rate_p12mths : ' || p_average_rate_p12mths,10);
2586
2587 -- Initialize Variables
2588 l_eff_date := p_hire_date;
2589 l_amt := 0;
2590 l_entitled_leave_taken := p_entitled_leave_taken;
2591
2592 -- This loop when be executed for each anniversary starting from
2593 -- First anniversary to the anniversary before the current anniversary
2594 --
2595 WHILE ( l_eff_date < p_period_start_date)
2596 LOOP
2597 --
2598 hr_utility.set_location('NZ l_eff_Date : ' || l_eff_Date,20);
2599
2600 -- Get Anniversary details for l_eff_date
2601 l_dummy := cache_anniversary_details
2602 (p_payroll_id => p_payroll_id
2603 ,p_assignment_id => p_assignment_id
2604 ,p_accrual_plan_id => p_accrual_plan_id
2605 ,p_calculation_date => l_eff_date
2606 ,p_service_start_date => p_hire_date
2607 ,p_anniversary_start_date => l_anniversary_start_date
2608 ,p_anniversary_end_date => l_anniversary_end_date
2609 ,p_years_of_service => l_years_of_service
2610 );
2611 hr_utility.set_location('NZ l_anniversary_start_date : ' || l_anniversary_start_date,30);
2612 hr_utility.set_location('NZ l_anniversary_end_date : ' || l_anniversary_end_date,30);
2613 hr_utility.set_location('NZ l_years_of_service : ' || l_years_of_service,30);
2614
2615 -- Get the Net Accrual for this anniversary
2616 l_dummy := get_accrual_entitlement
2617 (
2618 p_assignment_id => p_assignment_id
2619 ,p_payroll_id => p_payroll_id
2620 ,p_business_group_id => p_business_group_id
2621 ,p_plan_id => p_accrual_plan_id
2622 ,p_calculation_date => l_anniversary_end_date
2623 ,p_net_accrual => l_net_accrual
2624 ,p_net_entitlement => l_net_entitlement
2625 ,p_calc_start_date => l_calc_start_date
2626 ,p_last_accrual => l_last_accrual
2627 ,p_next_period_end => l_next_period_end
2628 );
2629 hr_utility.set_location('NZ l_net_accrual : ' || l_net_accrual,40);
2630
2631 IF (l_net_accrual > 0) THEN
2632 --
2633 hr_utility.set_location('NZ l_net_accrual is greater than 0',50);
2634 OPEN get_curr_period_start;
2635 FETCH get_curr_period_start
2636 INTO l_curr_start_date;
2637 CLOSE get_curr_period_start;
2638 hr_utility.set_location('NZ after cursor get_curr_period_start',50);
2639
2640 -- Net accrual in this period has to accommodate absences that are
2641 -- taken after this anniversary and before the current payroll period.
2642
2643 -- Leave taken needs to be found only once when the control reaches
2644 -- this point of code for the first time.
2645 IF (l_leave_taken is NULL) THEN -- If it is first anniversary
2646 --
2647 IF (l_anniversary_end_date < l_curr_start_date) THEN
2648 --
2649 hr_utility.set_location('NZ l_leave taken is null',40);
2650 l_leave_taken := get_leave_taken
2651 (
2652 p_assignment_id => p_assignment_id
2653 ,p_payroll_id => p_payroll_id
2654 ,p_business_group_id => p_business_group_id
2655 ,p_start_date => (l_anniversary_end_date + 1)
2656 ,p_end_date => (l_curr_start_date -1)
2657 ,p_curr_aniv_start => p_period_start_date
2658 ,p_plan_id => p_accrual_plan_id
2659 ,p_absence_start_date => p_absence_start_date
2660 );
2661 --
2662 ELSE
2663 --
2664 -- Anniversary has ended in current period
2665 -- Leave taken should be made 0
2666 l_leave_taken := 0;
2667 --
2668 END IF;
2669 --
2670 END IF;
2671 hr_utility.set_location('NZ l_leave_taken : ' || l_leave_taken,50);
2672
2673 -- If 'Net Accrual in this Anniversary is less than 'Leave Taken'
2677 -- Else
2674 -- Then
2675 -- There is no leave available in this anniversary.
2676 -- Leave Taken = Leave Taken - Net Accrual
2678 -- There is leave available in this anniversary.
2679 -- Leave Available = Net Accrual - Leave Taken
2680 -- Leave Taken = 0 as all 'Leave Taken' has been accommodated now.
2681 IF (l_net_accrual <= l_leave_taken ) THEN
2682 --
2683 hr_utility.set_location('NZ Leave not Available in this year',60);
2684 l_leave_taken := l_leave_taken - l_net_accrual;
2685 --
2686 ELSE
2687 --
2688 hr_utility.set_location('NZ Leave Available in this year',70);
2689
2690 l_leave_available := l_net_accrual - l_leave_taken;
2691 l_leave_taken := 0;
2692
2693 hr_utility.set_location('NZ Leave Available : '||l_leave_available,80);
2694
2695 -- Check whether Assignment has taken any parental leave in this
2696 -- anniversary
2697 l_has_taken_pl := is_parental_leave_taken
2698 (
2699 p_assignment_id
2700 ,p_business_group_id
2701 ,l_anniversary_start_date
2702 ,l_anniversary_end_date
2703 );
2704 hr_utility.set_location('NZ l_has_taken_pl : ' || l_has_taken_pl,90);
2705
2706 -- If Assignment has taken parental leave
2707 -- Then
2708 -- Rate as of this anniversary should be computed
2709 -- Else
2710 -- Current Rate should be used
2711 IF (l_has_taken_pl = 1) THEN
2712 --
2713 hr_utility.set_location('NZ Parental leave taken',100);
2714 l_rate := p_average_rate_p12mths;
2715 --
2716 ELSE
2717 --
2718 l_rate := p_curr_rate;
2719 --
2720 END IF;
2721 --
2722 hr_utility.set_location('NZ l_rate : '|| l_rate,130);
2723 hr_utility.set_location('NZ l_entitle_leave_Taken : '|| l_entitled_leave_Taken,130);
2724 hr_utility.set_location('NZ l_net_accrual : '|| l_net_accrual,130);
2725 hr_utility.set_location('NZ l_leave_available : '|| l_leave_available,130);
2726
2727 -- If Entitled Leave taken is less than Leave Available
2728 -- Then
2729 -- Compute the Amount and return
2730 -- Else
2731 -- Need to compute the amount for this anniversary and
2732 -- added it to l_amt
2733 -- Entitled Leave Taken = Entitled Leave Taken - Leave Available
2734 IF ( l_entitled_leave_taken <= l_leave_available ) THEN
2735 --
2736 l_amt := l_amt + l_entitled_leave_taken * l_rate;
2737 hr_utility.set_location('NZ l_amt : ' || l_amt,140);
2738 hr_utility.set_location('NZ l_entitled_leave_taken : ' || l_entitled_leave_taken,140);
2739 hr_utility.set_location('NZ Leaving',140);
2740 RETURN l_amt;
2741 --
2742 ELSE
2743 --
2744 hr_utility.set_location('NZ leave available less than entitled',150);
2745 l_entitled_leave_taken := l_entitled_leave_taken - l_leave_available;
2746 l_amt := l_amt + l_rate * l_leave_available;
2747 hr_utility.set_location('NZ l_amt : ' || l_amt,150);
2751 --
2748 hr_utility.set_location('NZ l_entitled_leave_taken : ' || l_entitled_leave_taken,150);
2749 --
2750 END IF;
2752 END IF; -- 'If leaves available in this year' block
2753 --
2754 END IF; -- 'If NET Accrual <0 ' Block
2755 -- Move to Next Anniversary
2756 l_eff_date := l_anniversary_end_date +1;
2757 --
2758 END LOOP;
2759 hr_utility.set_location('NZ Leaving : ' || l_procedure ,160);
2760 RETURN l_amt;
2761 --
2762 EXCEPTION
2763 --
2764 WHEN others THEN
2765 --
2766 IF get_curr_period_start%ISOPEN THEN
2767 --
2768 CLOSE get_curr_period_start ;
2769 --
2770 END IF;
2771 hr_utility.set_location('NZ Error in : ' || l_procedure ,170);
2772 RAISE;
2773 --
2774 --
2775 END get_entitled_amount;
2776
2777
2778 --
2779 end pay_nz_holidays_2003;
[Home] [Help]