1 PACKAGE BODY hr_nz_holidays AS
2 -- $Header: hrnzhol.pkb 120.1 2005/09/01 21:44:11 snekkala noship $
3 --
4 -- Copyright (C) 1999 Oracle Corporation
5 -- All Rights Reserved
6 --
7 -- Script to create NZ HRMS hr_nz_holidays package
8 --
9 -- Change List
10 -- ===========
11 --
12 -- Date Author Reference Descripti_n
13 -- -----------+-----------+---------+---------------------------------------
14 -- 03 Feb 2003 sclarke 3417767 c_get_adjustments in get_adjustment_values
15 -- now returns adjustment entries who have
16 -- effective_start_date outside accrual period
17 -- 22 Dec 2003 sclarke 3064179 Backed out change on 01-NOV, now just
18 -- provided comments.
19 -- 01 Nov 2003 sclarke 3064179 Changed get_net_entitlement to point to
20 -- new package pay_nz_holidays.
21 -- NULLED out redundant procedures.
22 -- 15 May 2003 puchil 2950172 Changed function annual_leave_eoy_adjustment
23 -- to return negative EOY Adjustment value.
24 -- 14 May 2003 puchil 2798048 Changed variable name from l_element_id to
25 -- l_pleave_taken in function get_parental_leaves_taken
26 -- 06 May 2003 puchil 2798048 Changed function annual_leave_entitled_to_pay
27 -- to include parental leave logic
28 -- 13 Mar 2003 vgsriniv 2264070 Added the function check_retro_eoy
29 -- 03 Dec 2002 srrajago 2689221 Included 'nocopy' option for the 'out' and
30 -- 'in out' parameters of all the
31 -- procedures and functions.
32 -- 19 Nov 2002 kaverma 2581490 Modified cursor get_pay_period_start_date
33 -- and retro_start_date function
34 -- 24 Jun 2002 vgsriniv 2366349 Added function get_adjustment_values to
35 -- handle Adjustment Elements for Accrual and
36 -- Entitlement.Also modified the relevant
37 -- functions to calculate Accrual and Entitlement
38 -- 21 Mar 2002 vgsriniv 2264070 Added functions to handle leaves
39 -- retroed after EOY period and modified
40 -- cursor get_pay_period_start_date in the
41 -- function annual_leave_entitled_to_pay
42 -- 05-Mar-2002 vgsriniv 2230110 Added a check for Anniversary date between
43 -- the pay period in function annual_leave
44 -- entitled to pay
45 -- 29 Jan 2002 vgsriniv 2185116 Removed the check for Termination type
46 -- 24-Jan-2002 vgsriniv 2185116 Added two extra parameters to function
47 -- annual_leave_entitled_to_pay
48 -- 17-JAN-2002 vgsriniv 2183135 Added function get_acp_start_date to
49 -- get the enrollment start date
50 -- 05-DEC-2001 vgsriniv 2127114 Added a function get_leap_year_mon and used
51 -- it in accrual_daily_basis function to adjust
52 -- for extra day in leap year
53 -- 19-NOV-2001 vgsriniv 2097319 changed the cursor c_leave_in_advance
54 -- Added OR clause to handle payrolls with offsets
55 -- 19-NOV-2001 hnainani 2115332 Added a round and NVL statement to the
56 -- l_hours_left_to_pay calculation
57 -- 12 NOV 2001 vgsriniv 2097319 Changed the join in the cursor
58 -- c_leave_in_advance
59 -- 06-NOV-2001 shoskatt 2090809 Changed the parameter to calculation date
60 -- for hr_nzbal.calc_asg_hol_ytd_date, which
61 -- is called from annual_leave_entitled_to_pay
62 -- function
63 -- 10-OCT-2001 rbsinha 2077370 modified annual_leave_net entitlement to
64 -- call get_accrual_entitlement
65 -- 31-JUL 2001 rbsinha 1422001 added function average_accrual_rate
66 -- 12 Jul 2001 Apunekar 1872465 Fixed for getting correct value of entitlement
67 -- and accrual in case of absences
68 -- 07 Jun 2000 SClarke 1323998 extra day accrual fixed
69 -- 07 Jun 2000 SClarke 1323990 Changes for display of entitlement +
70 -- accrual on forms
71 -- 21 Mar 2000 JTurner 1243407 Updated calls to asg_working_hours to
72 -- cater for mid period terminations
73 -- 14 Feb 2000 JTurner 1189790 Fixed problem with selection of
74 -- accrual bands
75 -- 27 Jan 2000 JTurner 1098494 Changes num_weeks_for_avg_earnings to
76 -- absence dev desc flex number of
77 -- complete weeks segment instead of
78 -- absence entry input value
79 -- 27 Jan 2000 JTurner 1098494 Changed get_accrual_plan_by_category
80 -- to use code rather than meaning
81 -- 26 Jan 2000 JTurner 1098494 Modified annual_leave_eoy_adjustment
82 -- function to cater for no carryover
83 -- 25 Jan 2000 JTurner 1098494 Modified annual_leave_entitled_to_pay
84 -- function to cater for no carryover
85 -- 17 Jan 2000 JTURNER 1098494 Modified annual_leave_net_entitlement
86 -- fn to cater for no carryover
87 -- 17 Jan 2000 JTURNER 1098494 Added accrual_daily_basis
88 -- function.
89 -- 14 Jan 2000 JTURNER 1098494 Added accrual_period_basis
90 -- function.
91 -- 11 Jan 2000 J Turner Moved Header symbol to 2nd line for
92 -- standards compliance
93 -- 29 Sep 1999 S.Clarke 110.6 Bug fix from QA testing to EOY
94 -- adjustment
95 -- 28 Sep 1999 P.Macdonald 110.5 Bug 1007736 -
96 -- annual_leave_net_entitlement
97 -- 23 Aug 1999 P.Macdonald 110.4 Fix syntax error
98 -- 13 Aug 1999 P.Macdonald 110.3 Add new functions
99 -- 30 Jul 1999 J Turner 110.2 Added get_net_accrual fn
100 -- 30 Jul 1999 J Turner 110.1 Completed development of
101 -- get_accrual_plan_by_category fn
102 -- 25 Jul 1999 P Macdonald 110.0 Created
103 -- 30 Oct 2001 VGSRINIV 2072748 Function accrual_daily_basis modified to
104 -- increment the accrual band on the continous
105 -- service date or the Hire date
106 -- 01 Nov 2001 VGSRINIV 2072748 Modified accrual_daily_basis function
107 -- to validate years of service
108 -- 02 Nov 2001 VGSRINIV 2033033 Modified accrual_daily_basis to get the
109 -- correct annual leave accrual when
110 -- assignment working hours change
111 -- 05 Nov 2001 VGSRINIV 2033033 Modified accrual_daily basis to get
112 -- accruals when assignment working hours
113 -- change more then once
114 -- 10 Oct 2002 PUCHIL 2595888 Changed line 3460 from varchar2(1) to
115 -- pay_payroll_actions.action_type%type
116 -- 01 Aug 2005 SNEKKALA 4259438 Modified Cursor c_get_curr_action_type
117 -- as part of performance fix
118 -----------------------------------------------------------------------------
119 -- private global declarations
120 -----------------------------------------------------------------------------
121
122 -- Define a record and PL/SQL table to hold accrual band information.
123 -- Used by accrual_period_basis and ann_leave_accrual_daily_basis
124 -- functions.
125
126 type t_accrual_band_rec is record
127 (lower_limit pay_accrual_bands.lower_limit%type
128 ,upper_limit pay_accrual_bands.upper_limit%type
129 ,annual_rate pay_accrual_bands.annual_rate%type) ;
130
131 type t_accrual_band_tab
132 is table of t_accrual_band_rec
133 index by binary_integer ;
134
135 -- Define a record and PL/SQL table to hold assignment work day data.
136 -- Used by accrual_period_basis and ann_leave_accrual_daily_basis
137 -- functions.
138
139 type t_asg_work_day_info_rec is record
140 (effective_start_date per_all_assignments_f.effective_start_date%type
141 ,effective_end_date per_all_assignments_f.effective_end_date%type
142 ,normal_hours per_all_assignments_f.normal_hours%type
143 ,frequency per_all_assignments_f.frequency%type) ;
144
145 type t_asg_work_day_info_tab
146 is table of t_asg_work_day_info_rec
147 index by binary_integer ;
148
149 --* Bug# 2183135 Function to get Accrual Plan Enrollment Start date
150
151 FUNCTION get_acp_start_date
152 (p_assignment_id NUMBER
153 ,p_plan_id NUMBER
154 ,p_effective_date DATE) RETURN DATE IS
155
156 l_effective_date DATE;
157
158 CURSOR csr_acp_start_date(p_assignment_id IN NUMBER
159 ,p_plan_id IN NUMBER
160 ,p_effective_date IN DATE) IS
161 SELECT LEAST(PEE.EFFECTIVE_START_DATE)
162 FROM pay_element_entries_f pee,
163 pay_element_links_f pel,
164 pay_element_types_f pet,
165 pay_accrual_plans pap
166 where pee.element_link_id = pel.element_link_id
167 and pel.element_type_id = pet.element_type_id
168 and pet.element_type_id = pap.accrual_plan_element_type_id
169 and pee.entry_type ='E'
170 and pee.assignment_id = p_assignment_id
171 and pap.accrual_plan_id = p_plan_id
172 and p_effective_date between pee.effective_start_date
173 and pee.effective_end_date
174 and p_effective_date between pel.effective_start_date
175 and pel.effective_end_date
176 and p_effective_date between pet.effective_start_date
177 and pet.effective_end_date;
178
179
180 BEGIN
181 OPEN csr_acp_start_date(p_assignment_id, p_plan_id, p_effective_date);
182 FETCH csr_acp_start_date into l_effective_date;
183 CLOSE csr_acp_start_date;
184
185 RETURN l_effective_date;
186
187 END get_acp_start_date;
188
189
190
191
192
193
194
195 /*---------------------------------------------------------------------
196 Name : get_accrual_plan_by_category
197 Purpose : To retrieve accrual plan id for designated category
198 Returns : accrual_plan_id if successful, null otherwise
199 ---------------------------------------------------------------------*/
200
201 FUNCTION get_accrual_plan_by_category
202 (p_assignment_id IN NUMBER
203 ,p_effective_date IN DATE
204 ,p_plan_category IN VARCHAR2) RETURN NUMBER IS
205
206 l_proc VARCHAR2(72) := g_package||'get_accrual_plan_by_category' ;
207 l_accrual_plan_id NUMBER ;
208 l_dummy NUMBER ;
209
210 CURSOR csr_get_accrual_plan_id(p_assignment_id NUMBER
211 ,p_effective_date DATE
212 ,p_plan_category VARCHAR2) IS
213 SELECT pap.accrual_plan_id
214 FROM pay_accrual_plans pap,
215 pay_element_entries_f pee,
216 pay_element_links_f pel,
217 pay_element_types_f pet
218 WHERE pee.assignment_id = p_assignment_id
219 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
220 AND pel.element_link_id = pee.element_link_id
221 AND pel.element_type_id = pet.element_type_id
222 AND pap.accrual_plan_element_type_id = pet.element_type_id
223 AND pap.accrual_category = p_plan_category ;
224
225 BEGIN
226 hr_utility.trace('In: ' || l_proc) ;
227 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
228 hr_utility.trace(' p_effective_date: ' || to_char(p_effective_date,'dd Mon yyyy')) ;
229 hr_utility.trace(' p_plan_category: ' || p_plan_category) ;
230
231 OPEN csr_get_accrual_plan_id(p_assignment_id, p_effective_date, p_plan_category) ;
232
233 FETCH csr_get_accrual_plan_id INTO l_accrual_plan_id;
234
235 IF csr_get_accrual_plan_id%NOTFOUND
236 THEN
237 CLOSE csr_get_accrual_plan_id;
238 hr_utility.trace('Crash Out: ' || l_proc) ;
239 hr_utility.set_message(801, 'HR_NZ_ACCRUAL_PLAN_NOT_FOUND');
240 hr_utility.raise_error;
241 end if ;
242
243 FETCH csr_get_accrual_plan_id INTO l_dummy ;
244
245 IF csr_get_accrual_plan_id%FOUND
246 THEN
247 CLOSE csr_get_accrual_plan_id;
248 hr_utility.trace('Crash Out: ' || l_proc) ;
249 hr_utility.set_message(801, 'HR_NZ_TOO_MANY_ACCRUAL_PLANS');
250 hr_utility.raise_error;
251 END IF;
252
253 CLOSE csr_get_accrual_plan_id;
254
255 hr_utility.trace(' return: ' || to_char(l_accrual_plan_id)) ;
256 hr_utility.trace('Out: ' || l_proc) ;
257 RETURN l_accrual_plan_id;
258
259 END get_accrual_plan_by_category;
260
261 --
262 -- get_net_accrual
263 --
264 -- This function is a wrapper for the
265 -- per_accrual_calc_functions.get_net_accrual procedure. The
266 -- wrapper is required so that a FastFormula function can be
267 -- registered for use in formulas.
268 --
269
270 FUNCTION get_net_accrual
271 (p_assignment_id IN NUMBER
272 ,p_payroll_id IN NUMBER
273 ,p_business_group_id IN NUMBER
274 ,p_plan_id IN NUMBER
275 ,p_calculation_date IN DATE)
276 RETURN NUMBER IS
277
278 l_proc VARCHAR2(72) := g_package||'get_net_accrual';
279 l_assignment_id NUMBER ;
280 l_plan_id NUMBER ;
281 l_payroll_id NUMBER ;
282 l_business_group_id NUMBER ;
283 l_calculation_date DATE ;
284 l_start_date DATE ;
285 l_end_date DATE ;
286 l_accrual_end_date DATE ;
287 l_accrual NUMBER ;
288 l_net_entitlement NUMBER ;
289
290 l_adjustment_element VARCHAR2(100);
291 l_accrual_adj NUMBER;
292 l_entitlement_adj NUMBER;
293
294 BEGIN
295
296 hr_utility.trace('In: ' || l_proc) ;
297 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
298 hr_utility.trace(' p_payroll_id: ' || to_char(p_payroll_id)) ;
299 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
300 hr_utility.trace(' p_plan_id: ' || to_char(p_plan_id)) ;
301 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'dd Mon yyyy')) ;
302
303 l_assignment_id := p_assignment_id ;
304 l_plan_id := p_plan_id ;
305 l_payroll_id := p_payroll_id ;
306 l_business_group_id := p_business_group_id ;
307 l_calculation_date := p_calculation_date ;
308 l_start_date := NULL ;
309 l_end_date := NULL ;
313
310 l_accrual_end_date := NULL ;
311 l_accrual := NULL ;
312 l_net_entitlement := NULL ;
314 per_accrual_calc_functions.get_net_accrual
315 (p_assignment_id => l_assignment_id
316 ,p_plan_id => l_plan_id
317 ,p_payroll_id => l_payroll_id
318 ,p_business_group_id => l_business_group_id
319 ,p_calculation_date => l_calculation_date
320 ,p_start_date => l_start_date
321 ,p_end_date => l_end_date
322 ,p_accrual_end_date => l_accrual_end_date
323 ,p_accrual => l_accrual
324 ,p_net_entitlement => l_net_entitlement) ;
325
326 -- venkat ---
327 /* Bug 2366349 Adjustment values are added to the accruals to display the annual leave
328 balance in the SOE */
329
330 l_adjustment_element:= 'Entitlement Adjustment Element';
331 l_entitlement_adj:= (get_adjustment_values(
332 p_assignment_id => l_assignment_id
333 ,p_accrual_plan_id => l_plan_id
334 ,p_calc_end_date => l_calculation_date
335 ,p_adjustment_element => l_adjustment_element
336 ,p_start_date => l_start_date
337 ,p_end_date => l_end_date));
338
339
340 hr_utility.trace('ven_others_ent= '||to_char(l_entitlement_adj));
341
342 l_adjustment_element := 'Accrual Adjustment Element';
343 l_accrual_adj:= (get_adjustment_values(
344 p_assignment_id => l_assignment_id
345 ,p_accrual_plan_id => l_plan_id
346 ,p_calc_end_date => l_calculation_date
347 ,p_adjustment_element => l_adjustment_element
348 ,p_start_date => l_start_date
349 ,p_end_date => l_end_date));
350
351
352 hr_utility.trace('ven_others_acc= '||to_char(l_accrual_adj));
353
354
355 -- venkat ---
356
357
358 l_net_entitlement := l_net_entitlement + l_entitlement_adj + l_accrual_adj;
359
360 hr_utility.trace(' return: ' || to_char(l_net_entitlement)) ;
361 hr_utility.trace('Out: ' || l_proc) ;
362 RETURN l_net_entitlement ;
363
364 END get_net_accrual ;
365
366 --------------------------------------------------------------
367 -- ====================================
368 -- 3064179
369 -- This function becomes on 01-APR-2004
370 -- ====================================
371
372 -- get_accrual_entitlement
373 --
374 -- This function is required mainly by the NZ local library
375 -- and will return the net accrual and net entitlement for a
376 -- given person on a given day.
377 --
378 -- These values will be displayed in the forms PAYWSACV and
379 -- PAYWSEAD.
380 --
381 --------------------------------------------------------------
382
383 FUNCTION get_accrual_entitlement
384 (p_assignment_id IN NUMBER
385 ,p_payroll_id IN NUMBER
386 ,p_business_group_id IN NUMBER
387 ,p_plan_id IN NUMBER
388 ,p_calculation_date IN DATE
389 ,p_net_accrual OUT NOCOPY NUMBER
390 ,p_net_entitlement OUT NOCOPY NUMBER
391 ,p_calc_start_date OUT NOCOPY DATE
392 ,p_last_accrual OUT NOCOPY DATE
393 ,p_next_period_end OUT NOCOPY DATE)
394 RETURN NUMBER IS
395 --
396 l_proc varchar2(72) := g_package||'.get_accrual_entitlement';
397 l_start_date date ;
398 l_end_date date ;
399 l_accrual_end_date date ;
400 l_entitlement_end_date date ;
401 l_net_accrual number ;
402 l_net_entitlement number ;
403 l_co_formula_id number ;
404 l_max_co number ;
405 l_accrual number ;
406 l_accrual_absences number ;
407 l_leave_accrual_amount number;
408 l_leave_total_amount number;
409 l_leave_entitlement_amount number;
410 l_expiry_date date;
411 l_last_anniversary_date date;
412 l_first_anniversary_date date;
413 l_continuous_service_date date;
414 l_temp number;
415 l_others_entitlement_amount number;
416 l_others_accrual_amount number;
417 l_assignment_id number;
418 l_plan_id number;
419 l_calculation_date date;
420
421 --------------------------------------
422 -- Bug No : 2366349 Start
423 --------------------------------------
424
425 l_adjustment_element VARCHAR2(100);
426 l_accrual_adj NUMBER ;
427 l_entitlement_adj NUMBER;
428 l_accrual_ent NUMBER;
429 --------------------------------------
430 -- Bug No : 2366349 End
431 --------------------------------------
432
433 --
437 where accrual_plan_id = v_accrual_plan_id;
434 cursor c_get_co_formula (v_accrual_plan_id number) is
435 select co_formula_id
436 from pay_accrual_plans
438 --
439 BEGIN
440 --
441 hr_utility.set_location('Entering: '||l_proc,10) ;
442 --
443
444 l_assignment_id := p_assignment_id;
445 l_plan_id := p_plan_id;
446 l_calculation_date := p_calculation_date;
447
448 -- Step 1 Find entitlement end date
449 -- first get the carryover formula then call it
450 -- to get the prev and next anniversary dates.
451 -- Entitlement end date and accrual end dates are
452 -- actually the day before the anniversary dates.
453 --
454 null;
455 open c_get_co_formula (p_plan_id);
456 fetch c_get_co_formula into l_co_formula_id;
457 close c_get_co_formula;
458 --
459 per_accrual_calc_functions.get_carry_over_values
460 (p_co_formula_id => l_co_formula_id
461 ,p_assignment_id => p_assignment_id
462 ,p_accrual_plan_id => p_plan_id
463 ,p_business_group_id => p_business_group_id
464 ,p_payroll_id => p_payroll_id
465 ,p_calculation_date => p_calculation_date
466 ,p_session_date => p_calculation_date
467 ,p_accrual_term => 'NZ_FORM'
468 ,p_effective_date => l_entitlement_end_date
469 ,p_expiry_date => l_expiry_date
470 ,p_max_carry_over => l_max_co
471 );
472 --
473 l_last_anniversary_date := get_last_anniversary
474 (p_assignment_id => p_assignment_id
475 ,p_business_group_id => p_business_group_id
476 ,p_calculation_date => p_calculation_date
477 );
478 --
479
480 l_continuous_service_date := get_continuous_service_date
481 (p_assignment_id => p_assignment_id
482 ,p_business_group_id => p_business_group_id
483 ,p_accrual_plan_id => p_plan_id
484 ,p_calculation_date => p_calculation_date
485 );
486 --
487 hr_utility.set_location(l_proc,30) ;
488 hr_utility.trace('l_entitlement_end_date = '||to_char(l_entitlement_end_date, 'yyyy/mm/dd'));
489 hr_utility.trace('l_expiry_date = '||to_char(l_expiry_date, 'yyyy/mm/dd'));
490 hr_utility.trace('l_max_co = '||to_char(l_max_co));
491 --
492 -- ============================
493 -- ENTITLEMENT PORTION OF LEAVE
494 -- ============================
495 --
496 -- Get the amount of leave which goes toward ENTITLEMENT
497 -- Sum from start of plan until last anniversary
498 --
499 -- l_net_accrual is not used because the net calculation
500 -- of leave is done manually to allow for absences to be taken
501 -- from entitlement before accrual
502 --
503 Begin
504 per_accrual_calc_functions.get_net_accrual
505 (p_assignment_id => p_assignment_id
506 ,p_plan_id => p_plan_id
507 ,p_payroll_id => p_payroll_id
508 ,p_business_group_id => p_business_group_id
509 ,p_calculation_date => l_entitlement_end_date
510 ,p_start_date => l_start_date
511 ,p_end_date => l_end_date
512 ,p_accrual_end_date => l_accrual_end_date
513 ,p_accrual => l_accrual
514 ,p_net_entitlement => l_net_accrual
515 );
516 Exception
517 when Others Then
518 NULL;
519 End;
520 --
521 -- If in the first year then if the entitlement end date
522 -- is equal to the start date then the entitlement amount = 0
523 --
524 if l_continuous_service_date = l_last_anniversary_date
525 then
526 if l_last_anniversary_date <= l_entitlement_end_date
527 then
528 l_leave_entitlement_amount := 0;
529 else
530 l_leave_entitlement_amount := l_accrual;
531 end if;
532 else
533 if l_continuous_service_date > l_entitlement_end_date
534 then
535 l_leave_entitlement_amount := 0;
536 else
537 l_leave_entitlement_amount := l_accrual;
538 end if;
539 end if;
540 --
541 hr_utility.set_location(l_proc,40);
542 hr_utility.trace('l_entitlement_end_date = '||to_char(l_entitlement_end_date, 'yyyy/mm/dd'));
543 hr_utility.trace('l_start_date = '||to_char(l_start_date, 'yyyy/mm/dd'));
544 hr_utility.trace('l_end_date = '||to_char(l_end_date, 'yyyy/mm/dd'));
545 hr_utility.trace('l_accrual_end_date = '||to_char(l_accrual_end_date, 'yyyy/mm/dd'));
546 hr_utility.trace('l_leave_entitlement_amount = '||to_char(l_leave_entitlement_amount));
547
548 --------------------------------------
549 --**Bug No : 2366349 Value of Adjustment element for Entitlement is calculated to add
550 --** to the entitlements
551 --------------------------------------
552
553
554 l_adjustment_element:= 'Entitlement Adjustment Element';
555 l_entitlement_adj:= (get_adjustment_values(
556 p_assignment_id => l_assignment_id
557 ,p_accrual_plan_id => l_plan_id
558 ,p_calc_end_date => l_calculation_date
559 ,p_adjustment_element => l_adjustment_element
560 ,p_start_date => l_start_date
561 ,p_end_date => l_end_date)
562 );
563
564 hr_utility.trace('ven_others_ent= '||to_char(l_entitlement_adj));
565
566 --------------------------------------
567 -- Bug No : 2366349 End
568 --------------------------------------
569
570
571 --
572 l_others_entitlement_amount := per_accrual_calc_functions.get_other_net_contribution (p_assignment_id => p_assignment_id
573 ,p_plan_id => p_plan_id
574 ,p_start_date => l_start_date
575 ,p_calculation_date => l_entitlement_end_date - 1 ) ;
576
577 -- Add other contibutions from adjustment element before anniversary date
578 -- to entitlement
579 -- l_leave_entitlement_amount := l_leave_entitlement_amount +
580 --l_others_entitlement_amount;
581
582 -- Get the amount of Leave which makes up the TOTAL ( ENTITLEMENT + ACCRUAL )
583 --
584 -- =========================
585 -- ACCRUAL PORTION OF LEAVE
586 -- =========================
587 --
588 -- This is calculated by getting the total amount of leave
589 -- accrued from start until the calculation date given.
590 -- The Accrual portion will then be: (total - entitlement portion)
591 -- Absences must then be subtracted to obtain the net figure
592 -- Absences are subtracted from the entitlement portion first
593 --
594 -- l_net_accrual is not used because the net calculation
595 -- of leave is done manually to allow for absences to be taken
596 -- from entitlement before accrual
597 --
598 begin
599 per_accrual_calc_functions.get_net_accrual
600 (p_assignment_id => p_assignment_id
601 ,p_plan_id => p_plan_id
602 ,p_payroll_id => p_payroll_id
603 ,p_business_group_id => p_business_group_id
604 ,p_calculation_date => p_calculation_date
605 ,p_start_date => l_start_date
606 ,p_end_date => l_end_date
607 ,p_accrual_end_date => l_accrual_end_date
608 ,p_accrual => l_accrual
609 ,p_net_entitlement => l_net_accrual
610 );
611 Exception
612 when Others Then
613 NULL;
614 End;
615 --amit
616 --
617 l_leave_total_amount := l_accrual;
618 --
619 hr_utility.set_location(l_proc,50) ;
620 hr_utility.trace('l_leave_total_amount = '||to_char(l_leave_total_amount));
621 hr_utility.trace('l_start_date = '||to_char(l_start_date, 'yyyy/mm/dd'));
622 hr_utility.trace('l_end_date = '||to_char(l_end_date, 'yyyy/mm/dd'));
623 hr_utility.trace('l_accrual_end_date = '||to_char(l_accrual_end_date, 'yyyy/mm/dd'));
624 --
625
626 ---------------------------------------------------------------------------------
627 --** Bug No : 2366349 : Function returns the accrual adjustment value for the first
628 --** year which is added to the accrual. This function returns the accrual value in
629 --** the first year and in the subsequent years it returns 0
630 ---------------------------------------------------------------------------------
631
632
633 l_adjustment_element := 'Accrual Adjustment Element';
634 l_accrual_adj:= (get_adjustment_values(
635 p_assignment_id => l_assignment_id
636 ,p_accrual_plan_id => l_plan_id
637 ,p_calc_end_date => l_calculation_date
638 ,p_adjustment_element => l_adjustment_element
639 ,p_start_date => l_entitlement_end_date
640 ,p_end_date => l_calculation_date)
641 );
642 ----------------------------------------------------------------------------------
643 --** Bug No : 2366349 : Function returns the accrual adjustment value which need to
644 --** be added to the entitlement value. l_accrual_ent returns the adjustment
645 --** value for accrual for all the anniversary years whereas l_accrual_adj returns
646 --** the adjustment value for accrual only for the first anniversary year.
647 --** Reason : The Adjustment value entered for accrual should be added to the
648 --** net accrual in the first year and from the next year onwards i.e., from the
649 --** next anniversary year this has to be added to the net entitlement
650 ----------------------------------------------------------------------------------
651
652 l_accrual_ent:= (get_adjustment_values(
656 ,p_adjustment_element => l_adjustment_element
653 p_assignment_id => l_assignment_id
654 ,p_accrual_plan_id => l_plan_id
655 ,p_calc_end_date => l_calculation_date
657 ,p_start_date => l_start_date
658 ,p_end_date => l_end_date));
659
660
661
662 hr_utility.trace('ven_others_acc= '||to_char(l_accrual_adj));
663 --------------------------------------
664 -- Bug No : 2366349 End
665 --------------------------------------
666 l_others_accrual_amount := per_accrual_calc_functions.get_other_net_contribution (p_assignment_id => p_assignment_id
667 ,p_plan_id => p_plan_id
668 ,p_start_date => l_entitlement_end_date
669 ,p_calculation_date => p_calculation_date ) ;
670
671 -- l_leave_total_amount := l_leave_total_amount + l_others_accrual_amount;
672 --
673 -- Find out the numder of hours taken during the accrual period
674 -- If max_co is 1 then no accrual only entitlement
675 --
676 if l_max_co = 1
677 then
678 l_accrual_absences := per_accrual_calc_functions.get_absence
679 (p_assignment_id => p_assignment_id
680 ,p_plan_id => p_plan_id
681 ,p_start_date => l_entitlement_end_date + 1
682 ,p_calculation_date => p_calculation_date
683 );
684 hr_utility.trace('Absence calculation start date = '||to_char(l_entitlement_end_date + 1,'yyyy/mm/dd'));
685 l_leave_entitlement_amount := l_leave_total_amount - l_accrual_absences;
686 l_leave_accrual_amount := 0;
687 --
688 else
689 l_accrual_absences := per_accrual_calc_functions.get_absence
690 (p_assignment_id => p_assignment_id
691 ,p_plan_id => p_plan_id
692 ,p_start_date => l_start_date
693 ,p_calculation_date => p_calculation_date
694 );
695 hr_utility.trace('Absence calculation start date = '||to_char(l_start_date,'yyyy/mm/dd'));
696 --
697 --Get the net entitlement and accrualamount before checking for absences
698 -- Determine the amount to go towards accrual portion by subtracting
699 -- entitlement portion from total
700 --
701 l_leave_accrual_amount := l_leave_total_amount - l_leave_entitlement_amount;
702 --
703 l_leave_accrual_amount := l_leave_accrual_amount +
704 l_others_accrual_amount + l_accrual_adj;
705
706 --** Bug 2366349 : l_accrual_ent is added to the net_entitlement. As this value
707 --** in the first year is accrual, not entitlement l_accrual_adj is subtracted.
708 --** As l_accrual_adj returns value only in the first anniversary year,
709 --** l_accrual_adj and l_accrual_ent is nullified in the first year and from
710 --** second year onwards adjusted accrual value is added to the entitlement
711
712 l_leave_entitlement_amount := l_leave_entitlement_amount +
713 l_others_entitlement_amount + l_entitlement_adj + l_accrual_ent - l_accrual_adj;
714
715
716 -- have to subtract absences taken to calculate net entitlement
717 -- absences must come off entitlement before accrual
718 --
719 if l_leave_entitlement_amount > l_accrual_absences
720 then
721 l_leave_entitlement_amount := l_leave_entitlement_amount - l_accrual_absences;
722 else
723 --subtract from entitlement and leftovers from accrual
724 l_leave_accrual_amount := l_leave_accrual_amount - (l_accrual_absences-l_leave_entitlement_amount);
725 l_leave_entitlement_amount := 0;
726 end if;
727 end if;
728 --
729 hr_utility.set_location(l_proc,60) ;
730 hr_utility.trace('Net Entitlement Amount = '||to_char(l_leave_entitlement_amount));
731 hr_utility.trace('Net Accrual Amount = '||to_char(l_leave_accrual_amount));
732 hr_utility.trace('Others accrual = '||to_char(l_others_accrual_amount));
733 hr_utility.trace('Others Entitlement = '||to_char(l_others_entitlement_amount));
734
735 --
736 -- set up return values
737 --
738 p_net_accrual := round(nvl(l_leave_accrual_amount,0),3);
739 p_net_entitlement := round(nvl(l_leave_entitlement_amount, 0),3);
740 p_calc_start_date := l_start_date;
741 p_last_accrual := l_accrual_end_date;
742 p_next_period_end := l_expiry_date;
743 --
744 hr_utility.trace('p_calc_start_date = '||to_char(p_calc_start_date, 'yyyy/mm/dd'));
745 hr_utility.trace('p_last_accrual = '||to_char(p_last_accrual, 'yyyy/mm/dd'));
746 hr_utility.trace('p_next_period_end = '||to_char(p_next_period_end, 'yyyy/mm/dd'));
747 --
748 hr_utility.set_location('Leaving '||l_proc,80);
749 RETURN (0);
750 EXCEPTION
751 WHEN OTHERS THEN
752 hr_utility.trace('EXCEPTION-'||sqlerrm);
753
754
755 END get_accrual_entitlement ;
756
757 --
758 -----------------------------------------------------------------------------
759 -- CHECK_PERIODS
760 --
761 -- Uses:
762 --
763 -- Used by:
767
764 -- FastFormula Function NZ_STAT_ANNUAL_LEAVE_CARRYOVER
765 --
766 -----------------------------------------------------------------------------
768 function check_periods
769 (p_payroll_id in number)
770 return date is
771 --
772 l_proc varchar2(61) := 'hr_nz_holidays.check_periods' ;
773 l_end_date date := to_date('01010001','DDMMYYYY');
774 --
775 -- cursor to check payroll periods exist up to calc_end_date
776 --
777 cursor c_last_period (p_payroll_id number) is
778 select max(tp.end_date)
779 from per_time_periods tp
780 where tp.payroll_id = p_payroll_id;
781 --
782 begin
783 hr_utility.set_location(' In: ' || l_proc,5) ;
784 --
785 -- check payroll periods exist up to calculation_end_date
786 --
787 open c_last_period ( p_payroll_id );
788 fetch c_last_period into l_end_date;
789 close c_last_period;
790 --
791 hr_utility.set_location(' Out: ' || l_proc,10) ;
792 return(l_end_date);
793 EXCEPTION
794 WHEN others THEN
795 hr_utility.trace('Error - payroll periods not found for payroll_id '||to_char(p_payroll_id));
796 hr_utility.set_location('Leaving:'||l_proc,99);
797 RETURN NULL;
798 end check_periods ;
799
800
801 /*---------------------------------------------------------------------
802
803 ====================================
804 3064179
805 This function becomes on 01-APR-2004
806 ====================================
807
808 Name : annual_leave_net_entitlement
809 Purpose :
810 Returns : Total accrued entitlement to the last anniversary
811
812 17 Jan 2000, JTurner: modified to cater for no carryover
813 ---------------------------------------------------------------------*/
814
815 PROCEDURE annual_leave_net_entitlement
816 (p_assignment_id IN NUMBER
817 ,p_payroll_id IN NUMBER
818 ,p_business_group_id IN NUMBER
819 ,p_plan_id IN NUMBER
820 ,p_calculation_date IN DATE
821 ,p_start_date OUT NOCOPY DATE
822 ,p_end_date OUT NOCOPY DATE
823 ,p_net_entitlement OUT NOCOPY NUMBER) IS
824
825 --
826 -- Local Variables
827 --
828
829 l_proc VARCHAR2(72) := g_package||'annual_leave_net_entitlement';
830 l_net_accrual NUMBER;
831 l_net_entitlement NUMBER;
832 l_calc_start_date DATE;
833 l_last_accrual DATE;
834 l_next_period_end DATE;
835 l_return_val NUMBER;
836
837 BEGIN
838
839 hr_utility.trace(' In: ' || l_proc) ;
840 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
841 hr_utility.trace(' p_payroll_id: ' || to_char(p_payroll_id)) ;
842 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
843 hr_utility.trace(' p_plan_id: ' || to_char(p_plan_id)) ;
844 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'dd Mon yyyy')) ;
845
846
847 l_return_val :=hr_nz_holidays.get_accrual_entitlement(p_assignment_id
848 ,p_payroll_id
849 ,p_business_group_id
850 ,p_plan_id
851 ,p_calculation_date
852 ,l_net_accrual
853 ,l_net_entitlement
854 ,l_calc_start_date
855 ,l_last_accrual
856 ,l_next_period_end );
857 --amit
858 p_net_entitlement := l_net_entitlement;
859 p_start_date := l_calc_start_date ;
860 p_end_date := p_calculation_date ;
861
862 hr_utility.trace(' p_net_entitlement: ' || to_char(p_net_entitlement)) ;
863 hr_utility.trace(' p_start_date: ' || to_char(p_start_date,'dd Mon yyyy')) ;
864 hr_utility.trace(' p_end_date: ' || to_char(p_end_date,'dd Mon yyyy')) ;
865 hr_utility.trace('Out: ' || l_proc) ;
866
867 END annual_leave_net_entitlement;
868
869 /*---------------------------------------------------------------------
870 Name : get_net_entitlement
871 Purpose : Total accrued entitlement to the last anniversary
872 Returns : 0 if successful, 1 otherwise
873 ---------------------------------------------------------------------*/
874
875 FUNCTION get_net_entitlement
876 (p_assignment_id IN NUMBER
877 ,p_payroll_id IN NUMBER
878 ,p_business_group_id IN NUMBER
879 ,p_calculation_date IN DATE)
880 RETURN NUMBER IS
881
882 l_proc VARCHAR2(72) := g_package||'get_net_entitlement';
883 l_plan_id NUMBER;
884 l_return_code NUMBER;
885 l_start_date DATE;
886 l_end_date DATE;
887 l_accrual_end_date DATE;
888 l_net_entitlement NUMBER;
889 l_acp_start_date DATE;
890 BEGIN
891 hr_utility.trace('In: ' || l_proc) ;
895 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'dd Mon yyyy')) ;
892 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
893 hr_utility.trace(' p_payroll_id: ' || to_char(p_payroll_id)) ;
894 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
896
897 l_plan_id := hr_nz_holidays.get_annual_leave_plan
898 (p_assignment_id => p_assignment_id
899 ,p_business_group_id => p_business_group_id
900 ,p_calculation_date => p_calculation_date);
901
902 IF (l_plan_id IS NULL)
903 THEN
904 --* Bug# 2183135 Added the code to check the case wherein Employee takes
905 --* Absence on the first day of the Accrual Plan Enrollment
906
907 l_plan_id := hr_nz_holidays.get_annual_leave_plan
908 (p_assignment_id => p_assignment_id
909 ,p_business_group_id => p_business_group_id
910 ,p_calculation_date => p_calculation_date+1);
911 l_acp_start_date := get_acp_start_date(p_assignment_id,l_plan_id,p_calculation_date+1);
912 hr_utility.trace('acp_strt_date= '||l_acp_start_date);
913 hr_utility.trace('p_cal_1_date = '||p_calculation_date);
914 if ((p_calculation_date+1)=l_acp_start_date) then
915 l_return_code := per_formula_functions.set_number('NET_ENTITLEMENT',0);
916 RETURN 0;
917 else
918 hr_utility.set_location('Accrual Plan Not Found '||l_proc,10);
919 hr_utility.set_message(801,'HR_NZ_ACCRUAL_PLAN_NOT_FOUND');
920 hr_utility.raise_error;
921 END IF;
922 end if;
923
924 hr_nz_holidays.annual_leave_net_entitlement
925 (p_assignment_id => p_assignment_id
926 ,p_payroll_id => p_payroll_id
927 ,p_business_group_id => p_business_group_id
928 ,p_plan_id => l_plan_id
929 ,p_calculation_date => p_calculation_date
930 ,p_start_date => l_start_date
931 ,p_end_date => l_end_date
932 ,p_net_entitlement => l_net_entitlement);
933
934 hr_utility.trace(' START_DATE: ' || to_char(l_start_date,'dd Mon yyyy')) ;
935 l_return_code := per_formula_functions.set_date('START_DATE',l_start_date);
936 hr_utility.trace(' END_DATE: ' || to_char(l_end_date,'dd Mon yyyy')) ;
937 l_return_code := per_formula_functions.set_date('END_DATE',l_end_date);
938 hr_utility.trace(' NET_ENTITLEMENT: ' || to_char(l_net_entitlement)) ;
939 l_return_code := per_formula_functions.set_number('NET_ENTITLEMENT',l_net_entitlement);
940
941 hr_utility.trace(' return: 0') ;
942 hr_utility.trace('Out: ' || l_proc) ;
943 RETURN 0;
944 EXCEPTION
945 WHEN others
946 THEN
947
948 hr_utility.trace('Crash Out: ' || l_proc) ;
949 RETURN 1;
950 END get_net_entitlement;
951
952 /*---------------------------------------------------------------------
953 Name : call_accrual_formula
954 Purpose : To run a named formula, with no inputs and no outputs
955 Returns : 0 if successful, 1 otherwise
956 ---------------------------------------------------------------------*/
957
958 FUNCTION call_accrual_formula
959 (p_assignment_id IN NUMBER
960 ,p_payroll_id IN NUMBER
961 ,p_business_group_id IN NUMBER
962 ,p_accrual_plan_name IN VARCHAR2
963 ,p_formula_name IN VARCHAR2
964 ,p_calculation_date IN DATE)
965 RETURN NUMBER IS
966
967 l_proc VARCHAR2(72) := g_package||'call_accrual_formula';
968 l_inputs ff_exec.inputs_t;
969 l_get_outputs ff_exec.outputs_t;
970 l_accrual_plan_id NUMBER;
971
972 CURSOR csr_get_accrual_plan_id IS
973 SELECT accrual_plan_id
974 FROM pay_accrual_plans
975 WHERE NVL(business_group_id, p_business_group_id) = p_business_group_id
976 AND accrual_plan_name = p_accrual_plan_name;
977
978
979 BEGIN
980 hr_utility.trace('In: ' || l_proc) ;
981 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
982 hr_utility.trace(' p_payroll_id: ' || to_char(p_payroll_id)) ;
983 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
984 hr_utility.trace(' p_accrual_plan_name: ' || p_accrual_plan_name) ;
985 hr_utility.trace(' p_formula_name: ' || p_formula_name) ;
986 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'dd Mon yyyy')) ;
987
988 OPEN csr_get_accrual_plan_id;
989 FETCH csr_get_accrual_plan_id INTO l_accrual_plan_id;
990 IF (csr_get_accrual_plan_id%NOTFOUND)
991 THEN
992 CLOSE csr_get_accrual_plan_id;
993 hr_utility.trace('Crash Out: ' || l_proc) ;
994 hr_utility.set_message(801, 'Accrual Plan Not Found');
995 hr_utility.raise_error;
996 END IF;
997 CLOSE csr_get_accrual_plan_id;
998
999
1000 -----------------------------
1001 -- Initialise the formula. --
1002 -----------------------------
1003
1004 l_inputs(1).name := 'ASSIGNMENT_ID';
1005 l_inputs(1).value := p_assignment_id;
1006 l_inputs(2).name := 'DATE_EARNED';
1007 l_inputs(2).value := TO_CHAR(p_calculation_date, 'DD-MON-YYYY');
1008 l_inputs(3).name := 'ACCRUAL_PLAN_ID';
1009 l_inputs(3).value := l_accrual_plan_id;
1010 l_inputs(4).name := 'BUSINESS_GROUP_ID';
1011 l_inputs(4).value := p_business_group_id;
1012 l_inputs(5).name := 'PAYROLL_ID';
1013 l_inputs(5).value := p_payroll_id;
1014
1015 l_get_outputs(1).name := 'CONTINUE_PROCESSING_FLAG';
1016
1017 ----------------------
1018 -- Run the formula. --
1019 ----------------------
1020 per_formula_functions.run_formula (p_formula_name => p_formula_name
1021 ,p_business_group_id => p_business_group_id
1022 ,p_calculation_date => p_calculation_date
1023 ,p_inputs => l_inputs
1024 ,p_outputs => l_get_outputs);
1025
1026 hr_utility.trace(' return: 0') ;
1027 hr_utility.trace('Out: ' || l_proc) ;
1028 RETURN 0;
1029 EXCEPTION
1030 WHEN others THEN
1031 hr_utility.trace('Crash Out: ' || l_proc) ;
1032 RETURN 1;
1033 END call_accrual_formula;
1034
1035 /*---------------------------------------------------------------------
1036 Name : get_annual_leave_plan
1037 Purpose : To get the Annual Leave Plan for an Assignment
1038 Returns : PLAN_ID if successful, NULL otherwise
1039 ---------------------------------------------------------------------*/
1040
1041 FUNCTION get_annual_leave_plan
1042 (p_assignment_id IN NUMBER
1043 ,p_business_group_id IN NUMBER
1044 ,p_calculation_date IN DATE)
1045 RETURN NUMBER IS
1046
1047 l_proc VARCHAR2(72) := g_package||'get_annual_leave_plan';
1048 l_plan_id NUMBER;
1049
1050 CURSOR csr_annual_leave_accrual_plan(c_business_group_id IN NUMBER
1051 ,c_calculation_date IN DATE
1052 ,c_assignment_id IN NUMBER) IS
1053 SELECT pap.accrual_plan_id
1054 FROM pay_accrual_plans pap,
1055 pay_element_entries_f pee,
1056 pay_element_links_f pel,
1057 pay_element_types_f pet
1058 WHERE pel.element_link_id = pee.element_link_id
1059 AND pel.element_type_id = pet.element_type_id
1060 AND pee.assignment_id = c_assignment_id
1061 AND pet.element_type_id = pap.accrual_plan_element_type_id
1062 AND pap.business_group_id + 0 = c_business_group_id
1063 AND c_calculation_date BETWEEN pee.effective_start_date AND pee.effective_end_date
1064 AND pap.accrual_category = 'NZAL' ;
1065
1066 BEGIN
1067 hr_utility.trace('In: ' || l_proc) ;
1068 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
1069 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
1070 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'dd Mon yyyy')) ;
1071
1072 OPEN csr_annual_leave_accrual_plan (p_business_group_id
1073 ,p_calculation_date
1074 ,p_assignment_id);
1075
1076 FETCH csr_annual_leave_accrual_plan INTO l_plan_id;
1077 CLOSE csr_annual_leave_accrual_plan;
1078
1079 hr_utility.trace(' return: ' || to_char(l_plan_id)) ;
1080 hr_utility.trace('Out: ' || l_proc) ;
1081 RETURN l_plan_id;
1082
1083 END;
1084
1085 /*---------------------------------------------------------------------
1086 Name : get_continuous_service_date
1087 Purpose : To get the Continuous Service Date for an Annual Leave Plan
1088 Returns : CONTINUOUS_SERVICE_DATE if successful, NULL otherwise
1089 ---------------------------------------------------------------------*/
1090
1091 FUNCTION get_continuous_service_date
1092 (p_assignment_id IN NUMBER
1093 ,p_business_group_id IN NUMBER
1094 ,p_accrual_plan_id IN NUMBER
1095 ,p_calculation_date IN DATE)
1096 RETURN DATE IS
1097
1098 l_proc VARCHAR2(72) := g_package||'get_continuous_service_date';
1099 l_csd DATE;
1100
1101 CURSOR csr_continuous_service_date (c_business_group_id NUMBER
1102 ,c_accrual_plan_id NUMBER
1103 ,c_calculation_date DATE
1104 ,c_assignment_id NUMBER) IS
1105 SELECT NVL(TO_DATE(pev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS'),pps.date_start)
1106 FROM pay_element_entries_f pee,
1107 pay_element_entry_values_f pev,
1108 pay_input_values_f piv,
1109 pay_accrual_plans pap,
1110 per_all_assignments_f asg,
1111 per_periods_of_service pps
1112 WHERE pev.element_entry_id = pee.element_entry_id
1113 AND pap.accrual_plan_element_type_id = piv.element_type_id
1114 AND piv.input_value_id = pev.input_value_id
1115 AND pee.entry_type ='E'
1116 AND asg.assignment_id = pee.assignment_id
1117 AND asg.assignment_id = c_assignment_id
1121 AND c_calculation_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1118 AND pap.accrual_plan_id = c_accrual_plan_id
1119 AND asg.business_group_id = c_business_group_id
1120 AND asg.period_of_service_id = pps.period_of_service_id
1122 AND c_calculation_date BETWEEN pee.effective_start_date AND pee.effective_end_date
1123 AND c_calculation_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1124 AND c_calculation_date BETWEEN pev.effective_start_date AND pev.effective_end_date
1125 AND piv.name = (
1126 SELECT meaning
1127 FROM hr_lookups
1128 WHERE lookup_type = 'NAME_TRANSLATIONS'
1129 AND lookup_code = 'PTO_CONTINUOUS_SD');
1130
1131 BEGIN
1132 hr_utility.trace('In: ' || l_proc) ;
1133 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
1134 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
1135 hr_utility.trace(' p_accrual_plan_id: ' || to_char(p_accrual_plan_id)) ;
1136
1137 OPEN csr_continuous_service_date (p_business_group_id
1138 ,p_accrual_plan_id
1139 ,p_calculation_date
1140 ,p_assignment_id);
1141 FETCH csr_continuous_service_date INTO l_csd;
1142 CLOSE csr_continuous_service_date;
1143 hr_utility.trace(' return: ' || to_char(l_csd)) ;
1144 hr_utility.trace('Out: ' || l_proc) ;
1145 RETURN l_csd;
1146
1147 END;
1148
1149
1150 /*---------------------------------------------------------------------
1151 ====================================
1152 3064179
1153 This function becomes on 01-APR-2004
1154 ====================================
1155
1156 Name : get_anniversary_date
1157 Purpose : To get the Anniversary Date for an Assignment
1158 Returns : Anniversary_Date if successful, NULL otherwise
1159 ---------------------------------------------------------------------*/
1160
1161 FUNCTION get_anniversary_date
1162 (p_assignment_id IN NUMBER
1163 ,p_business_group_id IN NUMBER
1164 ,p_calculation_date IN DATE)
1165 RETURN DATE IS
1166
1167 l_proc VARCHAR2(72) := g_package||'get_anniversary_date';
1168 l_anniversary_date DATE;
1169
1170 CURSOR csr_scl(c_business_group_id NUMBER
1171 ,c_calculation_date DATE
1172 ,c_assignment_id NUMBER) IS
1173 SELECT TO_DATE(scl.segment2,'YYYY/MM/DD HH24:MI:SS')
1174 FROM hr_soft_coding_keyflex scl,
1175 per_assignments_f asg
1176 WHERE asg.assignment_id = c_assignment_id
1177 AND asg.business_group_id + 0 = c_business_group_id
1178 AND scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1179 AND scl.enabled_flag = 'Y'
1180 AND scl.id_flex_num = 18
1181 AND c_calculation_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
1182
1183
1184 BEGIN
1185 hr_utility.trace('In: ' || l_proc) ;
1186 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
1187 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
1188 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'dd Mon yyyy')) ;
1189
1190 OPEN csr_scl(p_business_group_id
1191 ,p_calculation_date
1192 ,p_assignment_id);
1193 FETCH csr_scl INTO l_anniversary_date;
1194 CLOSE csr_scl;
1195 hr_utility.trace(' return: ' || to_char(l_anniversary_date,'dd Mon yyyy')) ;
1196 hr_utility.trace('Out: ' || l_proc) ;
1197 RETURN l_anniversary_date;
1198
1199 END;
1200
1201 /*---------------------------------------------------------------------
1202 ====================================
1203 3064179
1204 This function becomes on 01-APR-2004
1205 ====================================
1206
1207 Name : get_last_anniversary
1208 Purpose : To get the Last Anniversary Date for an Assignment
1209 Returns : Anniversary_Date if successful, NULL otherwise
1210 ---------------------------------------------------------------------*/
1211
1212 FUNCTION get_last_anniversary
1213 (p_assignment_id IN NUMBER
1214 ,p_business_group_id IN NUMBER
1215 ,p_calculation_date IN DATE)
1216 RETURN DATE IS
1217
1218 l_proc VARCHAR2(72) := g_package||'get_last_anniversary';
1219 l_base_anniversary DATE;
1220 l_last_anniversary DATE := NULL;
1221
1222 BEGIN
1223 hr_utility.trace('In: ' || l_proc) ;
1224 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
1225 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
1226 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'dd Mon yyyy')) ;
1227
1228 l_base_anniversary := get_anniversary_date (p_business_group_id => p_business_group_id
1229 ,p_calculation_date => p_calculation_date
1230 ,p_assignment_id => p_assignment_id);
1231 IF (l_base_anniversary IS NULL)
1232 THEN
1236 END IF;
1233 hr_utility.trace('Crash Out: ' || l_proc) ;
1234 hr_utility.set_message(801,'HR_NZ_INVALID_ANNIVERSARY_DATE');
1235 hr_utility.raise_error;
1237
1238 -- Assignment Anniversary Date is after the Calculation Date
1239
1240 IF (l_base_anniversary > p_calculation_date)
1241 THEN
1242 hr_utility.trace('Crash Out: ' || l_proc) ;
1243 hr_utility.set_message(801,'HR_NZ_INVALID_CALC_DATE');
1244 hr_utility.raise_error;
1245 END IF;
1246
1247 l_last_anniversary := TO_DATE(TO_CHAR(l_base_anniversary,'DDMM')||TO_CHAR(p_calculation_date,'YYYY'),'DDMMYYYY');
1248 IF (l_last_anniversary > p_calculation_date) THEN
1249 l_last_anniversary := ADD_MONTHS(l_last_anniversary,-12);
1250 END IF;
1251 hr_utility.trace(' return: ' || to_char(l_last_anniversary,'dd Mon yyyy')) ;
1252 hr_utility.trace('Out: ' || l_proc) ;
1253 RETURN l_last_anniversary;
1254
1255 END;
1256
1257 /*---------------------------------------------------------------------
1258 Name : get_annual_entitlement
1259 Purpose : To get the annual leave entitlement for an accrual plan
1260 Returns : ANNUAL_ENTITLEMENT if successful, NULL otherwise
1261 ---------------------------------------------------------------------*/
1262
1263 FUNCTION get_annual_entitlement
1264 (p_assignment_id IN NUMBER
1265 ,p_business_group_id IN NUMBER
1266 ,p_calculation_date IN DATE)
1267 RETURN NUMBER IS
1268 --
1269 -- Cursors
1270 --
1271 CURSOR csr_get_payroll_end_date(c_assignment_id NUMBER
1272 ,c_calculation_date DATE) IS
1273 SELECT ptp.end_date
1274 FROM per_time_periods ptp,
1275 per_all_assignments_f paa
1276 WHERE ptp.payroll_id = paa.payroll_id
1277 AND paa.assignment_id = c_assignment_id
1278 AND c_calculation_date BETWEEN ptp.start_date AND ptp.end_date;
1279
1280 CURSOR csr_get_accrual_band (c_number_of_years NUMBER
1281 ,c_accrual_plan_id NUMBER) IS
1282 SELECT annual_rate
1283 FROM pay_accrual_bands
1284 WHERE c_number_of_years >= lower_limit
1285 AND c_number_of_years < upper_limit
1286 AND accrual_plan_id = c_accrual_plan_id;
1287
1288 --
1289 -- Local Variables
1290 --
1291 l_proc VARCHAR2(72) := g_package||'get_annual_entitlement';
1292 l_continuous_service DATE;
1293 l_payroll_period_end DATE;
1294 l_accrual_plan_id NUMBER;
1295 l_annual_entitlement NUMBER := 0;
1296 l_years_service NUMBER := 0;
1297
1298 BEGIN
1299 hr_utility.trace('In: ' || l_proc) ;
1300 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
1301 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
1302 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'dd Mon yyyy')) ;
1303
1304
1305 l_accrual_plan_id := hr_nz_holidays.get_annual_leave_plan
1306 (p_business_group_id => p_business_group_id
1307 ,p_calculation_date => p_calculation_date
1308 ,p_assignment_id => p_assignment_id);
1309
1310 IF (l_accrual_plan_id IS NULL)
1311 THEN
1312 hr_utility.trace('Crash Out: ' || l_proc) ;
1313 hr_utility.set_message(801,'HR_NZ_INVALID_ACCRUAL_PLAN');
1314 hr_utility.raise_error;
1315 END IF;
1316
1317 l_continuous_service := hr_nz_holidays.get_continuous_service_date
1318 (p_business_group_id => p_business_group_id
1319 ,p_accrual_plan_id => l_accrual_plan_id
1320 ,p_calculation_date => p_calculation_date
1321 ,p_assignment_id => p_assignment_id);
1322 IF (l_continuous_service IS NULL)
1323 THEN
1324 hr_utility.trace('Crash Out: ' || l_proc) ;
1325 hr_utility.set_message(801,'HR_NZ_INVALID_SERVICE_DATE');
1326 hr_utility.raise_error;
1327 END IF;
1328
1329 -- Get the payroll end date
1330
1331 OPEN csr_get_payroll_end_date(p_assignment_id,p_calculation_date);
1332 FETCH csr_get_payroll_end_date INTO l_payroll_period_end;
1333 IF (csr_get_payroll_end_date%NOTFOUND)
1334 THEN
1335 CLOSE csr_get_payroll_end_date;
1336 hr_utility.trace('Crash Out: ' || l_proc) ;
1337 hr_utility.set_message(801,'HR_NZ_PAYROLL_DATE_NOT_FOUND');
1338 hr_utility.raise_error;
1339 END IF;
1340 CLOSE csr_get_payroll_end_date;
1341
1342 -- Calculate the number of years service
1343
1344 l_years_service := FLOOR(MONTHS_BETWEEN(l_payroll_period_end, l_continuous_service)/12);
1345
1346 -- Get the accrual rate from the accrual band
1347
1348 OPEN csr_get_accrual_band(l_years_service,l_accrual_plan_id);
1349 FETCH csr_get_accrual_band INTO l_annual_entitlement;
1350 IF (csr_get_accrual_band%NOTFOUND)
1351 THEN
1352 CLOSE csr_get_accrual_band;
1353 hr_utility.trace('Crash Out: ' || l_proc) ;
1354 hr_utility.set_message(801,'HR_NZ_ACCRUAL_BAND_NOT_FOUND');
1355 hr_utility.raise_error;
1356 END IF;
1357 CLOSE csr_get_accrual_band;
1358 hr_utility.trace(' return: ' || to_char(l_annual_entitlement)) ;
1359 hr_utility.trace('Out: ' || l_proc) ;
1360 RETURN l_annual_entitlement;
1361
1362 END;
1363
1364 /*---------------------------------------------------------------------
1365 Name : get_annual_leave_taken
1366 Purpose : To get the annual leave taken for an accrual plan
1367 Returns : ANNUAL LEAVE TAKEN if successful, NULL otherwise
1368 ---------------------------------------------------------------------*/
1369
1370 FUNCTION get_annual_leave_taken
1371 (p_assignment_id IN NUMBER
1372 ,p_business_group_id IN NUMBER
1373 ,p_calculation_date IN DATE
1374 ,p_start_date IN DATE
1375 ,p_end_date IN DATE)
1376 RETURN NUMBER IS
1377
1378 l_proc VARCHAR2(72) := g_package||'get_annual_leave_taken';
1379 l_plan_id NUMBER;
1380 l_total_absence NUMBER;
1381
1382 BEGIN
1383 hr_utility.trace('In: ' || l_proc) ;
1384 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
1385 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
1386 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'dd Mon yyyy')) ;
1387 hr_utility.trace(' p_start_date: ' || to_char(p_start_date,'dd Mon yyyy')) ;
1388 hr_utility.trace(' p_end_date: ' || to_char(p_end_date,'dd Mon yyyy')) ;
1389
1390 l_plan_id := hr_nz_holidays.get_annual_leave_plan
1391 (p_assignment_id => p_assignment_id
1392 ,p_business_group_id => p_business_group_id
1393 ,p_calculation_date => p_calculation_date);
1394
1395 IF (l_plan_id IS NULL)
1396 THEN
1397 hr_utility.trace(' ** Accrual Plan Not Found **');
1398 hr_utility.trace('Crash Out: ' || l_proc) ;
1399 hr_utility.set_message(801,'HR_NZ_ACCRUAL_PLAN_NOT_FOUND');
1400 hr_utility.raise_error;
1401 END IF;
1402 hr_utility.set_location(l_proc,15);
1403 l_total_absence := per_accrual_calc_functions.get_absence
1404 (p_assignment_id => p_assignment_id
1405 ,p_plan_id => l_plan_id
1406 ,p_calculation_date => p_end_date
1407 ,p_start_date => p_start_date);
1408
1409 hr_utility.trace(' return: ' || to_char(nvl(l_total_absence, 0))) ;
1410 hr_utility.trace('Out: ' || l_proc) ;
1411 RETURN NVL(l_total_absence, 0);
1412
1413 END get_annual_leave_taken;
1414
1415 -----------------------------------------------------------------------------
1416 -- num_weeks_for_avg_earnings
1417 --
1418 -- This function determines the number of weeks
1419 -- to use when calculating average earnings. Complete
1420 -- weeks of special leave and protected voluntary
1421 -- service leave reduce the number of weeks in the year.
1422 -----------------------------------------------------------------------------
1423
1424 function num_weeks_for_avg_earnings
1425 (p_assignment_id in number
1426 ,p_start_of_year_date in date)
1427 return number is
1428
1429 l_proc varchar2(72) := g_package||'num_weeks_for_avg_earnings';
1430 l_number_of_weeks number;
1431 l_number_of_leave_weeks number;
1432
1433 cursor c_number_of_leave_weeks(p_assignment_id number
1434 ,p_start_of_year date) is
1435 select nvl(sum(ab.abs_information2), 0) number_of_complete_weeks
1436 from per_absence_attendances ab
1437 , per_absence_attendance_types aat
1438 , pay_element_entries_f ee
1439 , pay_run_results rr
1440 , pay_assignment_actions aa
1441 , pay_payroll_actions pa
1442 , per_time_periods tp
1443 where aat.absence_attendance_type_id = ab.absence_attendance_type_id
1444 and aat.absence_category in ('NZSL', 'NZVS')
1445 and ee.creator_type = 'A'
1446 and ee.creator_id = ab.absence_attendance_id
1447 and ee.assignment_id = p_assignment_id
1448 and rr.source_id = ee.element_entry_id
1449 and rr.source_type = 'E'
1450 and aa.assignment_action_id = rr.assignment_action_id
1451 and pa.payroll_action_id = aa.payroll_action_id
1452 and pa.effective_date between ee.effective_start_date
1453 and ee.effective_end_date
1454 and tp.time_period_id = pa.time_period_id
1455 and tp.regular_payment_date >= p_start_of_year
1456 and tp.regular_payment_date < add_months(p_start_of_year, 12) ;
1457
1458 begin
1459
1460 hr_utility.trace('In: ' || l_proc) ;
1461 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
1462 hr_utility.trace(' p_start_of_year_date: ' || to_char(p_start_of_year_date,'dd Mon yyyy')) ;
1463
1464 open c_number_of_leave_weeks(p_assignment_id
1465 ,p_start_of_year_date) ;
1466 fetch c_number_of_leave_weeks
1467 into l_number_of_leave_weeks ;
1468 if (c_number_of_leave_weeks%notfound)
1469 then
1470 l_number_of_leave_weeks := 0 ;
1471 end if ;
1472 close c_number_of_leave_weeks ;
1473 l_number_of_weeks := 52 - l_number_of_leave_weeks ;
1474
1475 hr_utility.trace(' return: ' || to_char(l_number_of_weeks)) ;
1476 hr_utility.trace('Out: ' || l_proc) ;
1477
1478 return l_number_of_weeks ;
1479
1480 end num_weeks_for_avg_earnings ;
1481
1482 /*---------------------------------------------------------------------
1483 Name : get_ar_element_details
1484 Purpose : To get the get_accrual_record for an accrual plan
1485 Returns : 0 if successful, 1 otherwise
1486 ---------------------------------------------------------------------*/
1487
1488 FUNCTION get_ar_element_details
1489 (p_assignment_id IN NUMBER
1490 ,p_business_group_id IN NUMBER
1491 ,p_calculation_date IN DATE
1492 ,p_element_type_id OUT NOCOPY NUMBER
1493 ,p_accual_plan_name_iv_id OUT NOCOPY NUMBER
1494 ,p_holiday_year_end_date_iv_id OUT NOCOPY NUMBER
1495 ,p_hours_accrued_iv_id OUT NOCOPY NUMBER)
1496 RETURN NUMBER IS
1497
1498 CURSOR csr_accrual_record_element(c_effective_date DATE) IS
1499 SELECT pet.element_type_id
1500 FROM pay_element_types_f pet
1501 WHERE pet.element_name = 'Annual Leave Accrual Record'
1502 AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
1503
1504 CURSOR csr_accrual_record_iv(c_element_type_id pay_input_values_f.element_type_id%TYPE
1505 ,c_effective_date DATE) IS
1506 SELECT piv.input_value_id
1507 ,piv.name
1508 FROM pay_input_values_f piv
1509 WHERE piv.element_type_id = c_element_type_id
1510 AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
1511
1512 l_proc VARCHAR2(72) := g_package||'get_ar_element_details';
1513 l_element_type_id NUMBER;
1514 l_accual_plan_name_iv_id NUMBER;
1515 l_holiday_year_end_date_iv_id NUMBER;
1516 l_hours_accrued_iv_id NUMBER;
1517
1518 BEGIN
1519 hr_utility.trace('In: ' || l_proc) ;
1520 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
1521 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
1522 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'dd Mon yyyy')) ;
1523
1524 -- Find the Accrual Record Element
1525 OPEN csr_accrual_record_element(p_calculation_date);
1526 FETCH csr_accrual_record_element INTO l_element_type_id;
1527 IF (csr_accrual_record_element%NOTFOUND)
1528 THEN
1529 CLOSE csr_accrual_record_element;
1530 hr_utility.trace('Crash Out: ' || l_proc) ;
1531 hr_utility.set_message(801,'HR_AU_NZ_ELE_TYP_NOT_FND');
1532 hr_utility.raise_error;
1533 END IF;
1534 CLOSE csr_accrual_record_element;
1535 hr_utility.set_location(l_proc,10);
1536
1537 -- Get the input value id for each input value on the Accrual Record Element
1538 FOR rec_input_values in csr_accrual_record_iv(l_element_type_id, p_calculation_date)
1539 LOOP
1540 IF (rec_input_values.name = 'Accrual Plan Name')
1541 THEN
1542 l_accual_plan_name_iv_id := rec_input_values.input_value_id;
1543 ELSIF (rec_input_values.name = 'Holiday Year End Date')
1544 THEN
1545 l_holiday_year_end_date_iv_id := rec_input_values.input_value_id;
1546 ELSIF (rec_input_values.name = 'Hours Accrued')
1547 THEN
1548 l_hours_accrued_iv_id := rec_input_values.input_value_id;
1549 END IF;
1550 END LOOP;
1551
1552 IF ( l_accual_plan_name_iv_id IS NULL OR
1553 l_holiday_year_end_date_iv_id IS NULL OR
1554 l_hours_accrued_iv_id IS NULL)
1555 THEN
1556 hr_utility.trace('Crash Out: ' || l_proc) ;
1557 hr_utility.set_message(801,'HR_NZ_INPUT_VALUE_NOT_FOUND');
1558 hr_utility.raise_error;
1559 END IF;
1560 hr_utility.set_location(l_proc, 15);
1561
1562 hr_utility.trace(' p_element_type_id: ' || to_char(p_element_type_id)) ;
1563 p_element_type_id := l_element_type_id;
1564 hr_utility.trace(' p_accual_plan_name_iv_id: ' || to_char(p_accual_plan_name_iv_id)) ;
1565 p_accual_plan_name_iv_id := l_accual_plan_name_iv_id;
1566 hr_utility.trace(' p_holiday_year_end_date_iv_id: ' || to_char(p_holiday_year_end_date_iv_id)) ;
1567 p_holiday_year_end_date_iv_id := l_holiday_year_end_date_iv_id;
1568 hr_utility.trace(' p_hours_accrued_iv_id: ' || to_char(p_hours_accrued_iv_id)) ;
1569 p_hours_accrued_iv_id := l_hours_accrued_iv_id;
1570
1571 hr_utility.trace(' return: 0') ;
1572 hr_utility.trace('Out: ' || l_proc) ;
1573 RETURN 0;
1574
1575 EXCEPTION
1576 WHEN others
1577 THEN
1578 hr_utility.trace('Crash Out: ' || l_proc) ;
1579 RETURN 1;
1580 END get_ar_element_details;
1581
1582 /*---------------------------------------------------------------------
1583 Name : annual_leave_entitled_to_pay
1587 /* Bug# 2185116 Added p_ordinary_rate, p_type parameters */
1584 Purpose : To get the annual leave pay for entitled pay
1585 Returns : ANNUAL LEAVE PAY if successful, NULL otherwise
1586 ---------------------------------------------------------------------*/
1588
1589 FUNCTION annual_leave_entitled_to_pay
1590 (p_assignment_id IN NUMBER
1591 ,p_business_group_id IN NUMBER
1592 ,p_payroll_id in number
1593 ,p_calculation_date IN DATE
1594 ,p_entitled_to_hours IN NUMBER
1595 ,p_start_date IN DATE
1596 ,p_anniversary_date IN DATE
1597 ,p_working_hours IN NUMBER
1598 ,p_ordinary_rate IN NUMBER
1599 ,p_type IN VARCHAR2)
1600 RETURN NUMBER IS
1601
1602 l_proc VARCHAR2(72) := g_package||'annual_leave_entitled_to_pay';
1603 l_anniversary_date DATE;
1604 l_total_annual_leave_accrual NUMBER := 0;
1605 l_prev_total_accrual NUMBER := 0;
1606 l_gross_earnings NUMBER := 0;
1607 l_annual_leave_pay NUMBER := 0;
1608 l_hours_to_pay NUMBER := 0;
1609 l_hours_left_to_pay NUMBER := 0;
1610 l_total_annual_leave_taken NUMBER := 0;
1611 l_other_net_contributions NUMBER := 0;
1612 l_num_weeks_in_year NUMBER := 0;
1613 l_rate NUMBER := 0;
1614 l_accrued NUMBER := 0;
1615 l_taken NUMBER := 0;
1616
1617 l_plan_id NUMBER;
1618 l_element_type_id NUMBER;
1619 l_accual_plan_name_iv_id NUMBER;
1620 l_holiday_year_end_date_iv_id NUMBER;
1621 l_hours_accrued_iv_id NUMBER;
1622 l_balance_type_id NUMBER;
1623 l_return_value NUMBER;
1624 l_invalid_exit BOOLEAN := TRUE;
1625
1626 l_pay_period_start_date DATE;
1627 l_num_of_pay_periods_per_year per_time_period_types.number_per_fiscal_year%type;
1628 l_extra_weeks NUMBER;
1629 l_offset_flag BOOLEAN := FALSE;
1630
1631
1632 CURSOR csr_gross_earning_balance IS
1633 SELECT pbt.balance_type_id
1634 FROM pay_balance_types pbt
1635 WHERE pbt.balance_name = 'Gross Earnings for Holiday Pay'
1636 AND legislation_code = 'NZ'
1637 AND business_group_id IS NULL;
1638
1639 /* Bug 2230110 Added the following cursor */
1640 /* Bug 2264070 Added a extra join for payroll_action_id */
1641 cursor get_pay_period_start_date(p_assignment_id in number) is
1642 SELECT TPERIOD.start_date,
1643 TPTYPE.number_per_fiscal_year
1644 FROM pay_payroll_actions PACTION,
1645 per_time_periods TPERIOD,
1646 per_time_period_types TPTYPE
1647 where PACTION.payroll_action_id =
1648 (select max(paa.payroll_action_id)
1649 from pay_assignment_actions paa,
1650 pay_payroll_actions ppa
1651 where paa.assignment_id = p_assignment_id
1652 and ppa.action_type in ('R','Q')
1653 and ppa.payroll_action_id = paa.payroll_action_id)
1654 and PACTION.payroll_id = TPERIOD.payroll_id
1655 and PACTION.date_earned between TPERIOD.start_date and TPERIOD.end_date
1656 and TPTYPE.period_type = TPERIOD.period_type;
1657
1658 /* Bug 2798048-NZ Parental leave, added the following code
1659 for checking whether parental leave is taken in a particular
1660 period.*/
1661 l_parental_leave NUMBER := 0;
1662 l_prev_anniversary_date DATE;
1663
1664 FUNCTION get_parental_leaves_taken
1665 (p_assignment_id IN NUMBER
1666 ,p_business_group_id IN NUMBER
1667 ,p_start_date IN DATE
1668 ,p_end_date IN DATE)
1669 RETURN NUMBER IS
1670
1671 CURSOR csr_parental_leaves_taken(c_assignment_id IN NUMBER
1672 ,c_business_group_id IN NUMBER
1673 ,c_start_date IN DATE
1674 ,c_end_date IN DATE)IS
1675 select 1
1676 from per_absence_attendances paa,
1677 per_absence_attendance_types paat
1678 where paa.person_id = (select distinct person_id
1679 from per_assignments_f paaf
1680 where paaf.assignment_id = c_assignment_id)
1681 and paa.business_group_id = c_business_group_id
1682 and paa.business_group_id = paat.business_group_id
1683 and paa.absence_attendance_type_id = paat.absence_attendance_type_id
1684 and paat.absence_category = 'NZPL'
1685 and (paa.date_start between c_start_date and c_end_date
1686 or paa.date_end between c_start_date and c_end_date );
1687
1688 l_pleave_taken number := 0;
1689 l_proc varchar2(72) := 'get_parental_leaves_taken' ;
1690
1691 BEGIN
1692 hr_utility.trace('In: ' || l_proc);
1693 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
1694 hr_utility.trace(' p_business_group_id: '||to_char(p_business_group_id));
1695 hr_utility.trace(' p_start_date: ' || to_char(p_start_date,'dd Mon yyyy')) ;
1696 hr_utility.trace(' p_end_date: ' || to_char(p_end_date,'dd Mon yyyy')) ;
1697
1698 open csr_parental_leaves_taken(p_assignment_id,
1699 p_business_group_id,
1700 p_start_date,
1701 p_end_date);
1702 fetch csr_parental_leaves_taken into l_pleave_taken;
1703
1704 if csr_parental_leaves_taken%FOUND then
1705 close csr_parental_leaves_taken;
1706 hr_utility.trace(' l_pleave_taken: ' || to_char(l_pleave_taken));
1707 hr_utility.trace('Out: ' || l_proc);
1708 return 1;
1709 end if;
1710 close csr_parental_leaves_taken;
1711 hr_utility.trace(' No parental leave taken');
1712 hr_utility.trace('Out: ' || l_proc);
1713 return 0;
1714 END get_parental_leaves_taken;
1715 /* Bug 2798048-NZ Parental leave, End.*/
1716
1717
1718 FUNCTION get_total_annual_leave_accrued
1719 (p_assignment_id number
1720 ,p_holiday_year_end_date date
1721 ,p_plan_id number
1722 ,p_business_group_id number
1723 ,p_payroll_id number)
1724 RETURN NUMBER IS
1725
1726 l_proc varchar2(72) := 'get_total_annual_leave_accrued' ;
1727 l_accrual number ;
1728 l_other number ;
1729 l_total number ;
1730 l_start_date date ;
1731 l_end_date date ;
1732 l_accrual_end_date date ;
1733
1734 l_adjustment_element VARCHAR2(100);
1735 l_accrual_adj NUMBER;
1736 l_entitlement_adj NUMBER;
1737
1738 BEGIN
1739 hr_utility.trace(' In: ' || l_proc) ;
1740
1741 -- find what the accrual was as at the holiday year end date supplied
1742
1743 per_accrual_calc_functions.get_accrual
1744 (p_assignment_id => p_assignment_id
1745 ,p_calculation_date => p_holiday_year_end_date
1746 ,p_plan_id => p_plan_id
1747 ,p_business_group_id => p_business_group_id
1748 ,p_payroll_id => p_payroll_id
1749 ,p_start_date => l_start_date
1750 ,p_end_date => l_end_date
1751 ,p_accrual_end_date => l_accrual_end_date
1752 ,p_accrual => l_accrual) ;
1753
1754 -- find what other contributions were at holiday year end date
1755
1756 /* Bug 2366349 Adjustment Element values are added to the total accrual */
1757
1758 l_adjustment_element:= 'Entitlement Adjustment Element';
1759 l_entitlement_adj:= (get_adjustment_values(
1760 p_assignment_id => p_assignment_id
1761 ,p_accrual_plan_id => p_plan_id
1762 ,p_calc_end_date => p_holiday_year_end_date
1763 ,p_adjustment_element => l_adjustment_element
1764 ,p_start_date => l_start_date
1765 ,p_end_date => l_end_date));
1766
1767
1768 hr_utility.trace('ven_others_ent= '||to_char(l_entitlement_adj));
1769
1770 l_adjustment_element := 'Accrual Adjustment Element';
1771 l_accrual_adj:= (get_adjustment_values(
1772 p_assignment_id => p_assignment_id
1773 ,p_accrual_plan_id => p_plan_id
1774 ,p_calc_end_date => p_holiday_year_end_date
1775 ,p_adjustment_element => l_adjustment_element
1776 ,p_start_date => l_start_date
1777 ,p_end_date => l_end_date));
1778
1779
1780 hr_utility.trace('ven_others_acc= '||to_char(l_accrual_adj));
1781
1782
1783 l_other := per_accrual_calc_functions.get_other_net_contribution
1784 (p_assignment_id => p_assignment_id
1785 ,p_plan_id => p_plan_id
1786 ,p_start_date => l_start_date
1787 ,p_calculation_date => p_holiday_year_end_date);
1788
1789 l_total := l_accrual + l_other +l_accrual_adj + l_entitlement_adj;
1790
1791 hr_utility.trace(' Out: ' || l_proc) ;
1792 RETURN l_total ;
1793
1794 END get_total_annual_leave_accrued;
1795
1796 BEGIN
1797 hr_utility.trace('In: ' || l_proc) ;
1798 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
1799 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
1800 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'dd Mon yyyy')) ;
1801 hr_utility.trace(' p_entitled_to_hours: ' || to_char(p_entitled_to_hours)) ;
1802 hr_utility.trace(' p_start_date: ' || to_char(p_start_date,'dd Mon yyyy')) ;
1803 hr_utility.trace(' p_anniversary_date: ' || to_char(p_anniversary_date,'dd Mon yyyy')) ;
1804 hr_utility.trace(' p_working_hours: ' || to_char(p_working_hours)) ;
1805
1806 l_anniversary_date := p_anniversary_date ;
1807 l_hours_left_to_pay := p_entitled_to_hours;
1808
1809 l_plan_id := hr_nz_holidays.get_annual_leave_plan
1813
1810 (p_assignment_id => p_assignment_id
1811 ,p_business_group_id => p_business_group_id
1812 ,p_calculation_date => p_calculation_date);
1814 IF (l_plan_id IS NULL)
1815 THEN
1816 hr_utility.trace('Crash Out: ' || l_proc) ;
1817 hr_utility.set_message(801,'HR_NZ_ACCRUAL_PLAN_NOT_FOUND');
1818 hr_utility.raise_error;
1819 END IF;
1820
1821 -- find total leave taken up until just before this leave
1822
1823 l_total_annual_leave_taken := hr_nz_holidays.get_annual_leave_taken
1824 (p_assignment_id => p_assignment_id
1825 ,p_business_group_id => p_business_group_id
1826 ,p_calculation_date => p_calculation_date
1827 ,p_start_date => p_anniversary_date
1828 ,p_end_date => p_start_date - 1) ;
1829
1830 hr_utility.trace(' l_total_annual_leave_taken: ' || to_char(l_total_annual_leave_taken)) ;
1831 hr_utility.trace(' l_anniversary_date: ' || to_char(l_anniversary_date, 'dd Mon yyyy')) ;
1832
1833 WHILE (l_anniversary_date <= p_start_date)
1834 LOOP
1835
1836 l_total_annual_leave_accrual := get_total_annual_leave_accrued
1837 (p_assignment_id => p_assignment_id
1838 ,p_holiday_year_end_date => l_anniversary_date - 1
1839 ,p_plan_id => l_plan_id
1840 ,p_business_group_id => p_business_group_id
1841 ,p_payroll_id => p_payroll_id) ;
1842
1843 hr_utility.trace(' l_total_annual_leave_accrual: ' || to_char(l_total_annual_leave_accrual)) ;
1844
1845 IF (l_total_annual_leave_accrual >= l_total_annual_leave_taken)
1846 THEN
1847 l_invalid_exit := FALSE;
1848 l_total_annual_leave_accrual := l_total_annual_leave_accrual - l_total_annual_leave_taken;
1849 EXIT ;
1850 END IF;
1851
1852 l_anniversary_date := ADD_MONTHS(l_anniversary_date,12);
1853 hr_utility.trace(' l_anniversary_date: ' || to_char(l_anniversary_date, 'dd Mon yyyy')) ;
1854
1855 END LOOP;
1856
1857 IF (l_invalid_exit)
1858 THEN
1859 hr_utility.trace(' ** No entitled annual leave found **');
1860 hr_utility.trace('Crash Out: ' || l_proc) ;
1861 hr_utility.set_message(801,'HR_NZ_ENTITLED_LEAVE_NOT_FOUND');
1862 hr_utility.raise_error;
1863 END IF;
1864
1865
1866 /* Bug 2230110 cursor get_pay_period_start_date is used to get the pay period start date. */
1867
1868 open get_pay_period_start_date(p_assignment_id);
1869 fetch get_pay_period_start_date into l_pay_period_start_date,l_num_of_pay_periods_per_year;
1870 close get_pay_period_start_date;
1871
1872
1873 /* following check is to find whether holiday anniversary date lies in between the pay
1874 period and if so, find the number of weeks in the pay period */
1875 if (to_char(p_anniversary_date,'dd') <> to_char(l_pay_period_start_date,'dd'))
1876 then
1877 l_extra_weeks := 52/l_num_of_pay_periods_per_year;
1878 l_offset_flag := true;
1879 end if;
1880
1881 /* Bug 2798048-NZ Parental leave, calculate the previous holiday
1882 anniversary date.*/
1883 l_prev_anniversary_date := ADD_MONTHS(l_anniversary_date,-12);
1884
1885 LOOP
1886
1887 l_num_weeks_in_year := num_weeks_for_avg_earnings
1888 (p_assignment_id => p_assignment_id
1889 ,p_start_of_year_date => ADD_MONTHS(l_anniversary_date,-12));
1890
1891 /* if holiday anniversary is inbetween the pay period add the extra pay period weeks */
1892 if l_offset_flag then
1893 l_num_weeks_in_year := l_num_weeks_in_year + l_extra_weeks;
1894 end if;
1895
1896 OPEN csr_gross_earning_balance;
1897 FETCH csr_gross_earning_balance INTO l_balance_type_id;
1898 CLOSE csr_gross_earning_balance;
1899
1900 IF (l_balance_type_id IS NULL)
1901 THEN
1902 hr_utility.trace('Crash Out: ' || l_proc) ;
1903 hr_utility.set_message(801,'HR_NZ_BALANCE_NOT_FOUND');
1904 hr_utility.raise_error;
1905 END IF;
1906
1907 -- Changed the parameter to p_calculation_date. Bug #2090809
1908 /* Bug# 2185116 --> Changed the calculation date to anniversary date-1 */
1909
1910 l_gross_earnings := hr_nzbal.calc_asg_hol_ytd_date
1911 (p_assignment_id => p_assignment_id
1912 ,p_balance_type_id => l_balance_type_id
1913 ,p_effective_date => l_anniversary_date-1);
1914 hr_utility.trace(' Year Ending: ' || to_char(l_anniversary_date - 1,'dd Mon yyyy')) ;
1915 hr_utility.trace(' Gross Earnings for Holiday Pay: ' || to_char(l_gross_earnings)) ;
1916
1917 l_rate := l_gross_earnings / l_num_weeks_in_year / p_working_hours;
1918 l_hours_to_pay := LEAST(l_hours_left_to_pay, l_total_annual_leave_accrual - l_prev_total_accrual);
1919 hr_utility.trace(' Rate: ' || to_char(l_rate)) ;
1920 hr_utility.trace(' Hours Left to Pay: ' || to_char(l_hours_left_to_pay)) ;
1921 hr_utility.trace(' Total Annual Leave Accrual: ' || to_char(l_total_annual_leave_accrual)) ;
1922 hr_utility.trace(' Previous Total Accrual: ' || to_char(l_prev_total_accrual)) ;
1923 hr_utility.trace(' Hours to Pay: ' || to_char(l_hours_to_pay)) ;
1924 hr_utility.trace(' Annual Leave Pay: ' || to_char(l_annual_leave_pay)) ;
1925
1926 /* Bug 2798048-NZ Parental leave, calculation changes for annual leave
1927 falling in parental leave period.*/
1928 l_parental_leave := get_parental_leaves_taken
1929 (p_assignment_id => p_assignment_id
1930 ,p_business_group_id => p_business_group_id
1931 ,p_start_date => l_prev_anniversary_date
1932 ,p_end_date => l_anniversary_date-1);
1933
1934 hr_utility.trace(' Parental Leave Taken:' || to_char(l_parental_leave));
1935 if l_parental_leave = 1 then
1936 l_annual_leave_pay := l_annual_leave_pay + (l_hours_to_pay * l_rate);
1937 else
1938 /* Bug# 2185116 Included the check for greatest of Ordinary pay and
1939 Average Pay and the greatest value is returned */
1940 l_annual_leave_pay := l_annual_leave_pay + GREATEST((l_hours_to_pay * l_rate),(l_hours_to_pay*p_ordinary_rate));
1941 end if;
1942
1943 hr_utility.trace(' Annual Leave Pay: ' || to_char(l_annual_leave_pay)) ;
1944 l_hours_left_to_pay := nvl(l_hours_left_to_pay,0) - round(nvl(l_hours_to_pay,0),3);
1945
1946 hr_utility.trace(' Hours Left to Pay: ' || to_char(l_hours_left_to_pay)) ;
1947 EXIT WHEN (l_hours_left_to_pay = 0);
1948
1949 /* Bug 2798048-NZ Parental leave, update the
1950 previous anniversary date*/
1951 l_prev_anniversary_date := l_anniversary_date;
1952 l_anniversary_date := ADD_MONTHS(l_anniversary_date,12);
1953 l_prev_total_accrual := l_total_annual_leave_accrual;
1954
1955 l_taken := hr_nz_holidays.get_annual_leave_taken
1956 (p_assignment_id => p_assignment_id
1957 ,p_business_group_id => p_business_group_id
1958 ,p_calculation_date => p_calculation_date
1959 ,p_start_date => p_anniversary_date
1960 ,p_end_date => l_anniversary_date);
1961
1962 l_accrued := get_total_annual_leave_accrued
1963 (p_assignment_id => p_assignment_id
1964 ,p_holiday_year_end_date => l_anniversary_date - 1
1965 ,p_plan_id => l_plan_id
1966 ,p_business_group_id => p_business_group_id
1967 ,p_payroll_id => p_payroll_id) ;
1968
1969 hr_utility.trace(' Leave Taken: ' || to_char(l_taken)) ;
1970 hr_utility.trace(' Leave Accrued: ' || to_char(l_accrued)) ;
1971 l_total_annual_leave_accrual := l_accrued - l_taken;
1972
1973 END LOOP;
1974
1975 hr_utility.trace(' return: ' || to_char(l_annual_leave_pay)) ;
1976 hr_utility.trace('Out: ' || l_proc) ;
1977 RETURN l_annual_leave_pay;
1978
1979 END annual_leave_entitled_to_pay;
1980
1981 -----------------------------------------------------------------------------
1982 -- ====================================
1983 -- 3064179
1984 -- This function becomes on 01-APR-2004
1985 -- ====================================
1986
1987 -- annual_leave_eoy_adjustment
1988 --
1989 -- calculate annual leave end of year adjustment
1990 -----------------------------------------------------------------------------
1991
1992 function annual_leave_eoy_adjustment
1993 (p_business_group_id in number
1994 ,p_payroll_id in number
1995 ,p_assignment_id in number
1996 ,p_asg_hours in number
1997 ,p_year_end_date in date
1998 ,p_in_advance_pay_carryover in out nocopy number
1999 ,p_in_advance_hours_carryover in out nocopy number)
2000 return number is
2001
2002 l_procedure_name varchar2(61) := 'hr_nz_holidays.annual_leave_eoy_adjustment' ;
2003 l_eoy_adjustment number ;
2004 l_balance_type_id pay_balance_types.balance_type_id%type ;
2005 l_annual_leave_in_advance_hrs number ;
2006 l_annual_leave_in_advance_pay number ;
2007 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%type ;
2008 l_start_date date ;
2009 l_end_date date ;
2010 l_accrual_end_date date ;
2011 l_accrual number ;
2012 l_accrual_tmp number ;
2013 l_in_advance_hours_carryover number ;
2014 l_in_advance_pay_carryover number ;
2015 l_hours_to_adjust number ;
2016 l_pay_to_adjust number ;
2017 l_gross_earnings_for_hol_pay number ;
2018 l_num_weeks number ;
2019 l_recalculated_pay number ;
2020 l_absence_hours number ;
2021 l_absence_pay number ;
2022 l_hours_running_total number ;
2023 l_prev_hours_running_total number ;
2024 l_pay_running_total number ;
2025 l_prev_pay_running_total number ;
2026 l_pay number ;
2027 l_hours number ;
2028
2029 l_pay_period_start_date date;
2030 l_num_of_pay_periods_per_year number;
2031 l_extra_weeks number;
2032 l_offset_flag boolean:= false;
2033
2034 e_missing_balance_type exception ;
2035 e_missing_accrual_plan exception ;
2036 e_missing_leave_in_advance exception ;
2037
2038 /* Bug 2581490 - added join to pay_payroll_actions in subquery */
2039 cursor get_pay_period_start_date(p_assignment_id number) is
2040 SELECT TPERIOD.start_date,
2041 TPTYPE.number_per_fiscal_year
2042 FROM pay_payroll_actions PACTION,
2043 per_time_periods TPERIOD,
2044 per_time_period_types TPTYPE
2045 where PACTION.payroll_action_id =
2046 (select max(paa.payroll_action_id)
2047 from pay_assignment_actions paa,
2048 pay_payroll_actions ppa
2049 where paa.assignment_id = p_assignment_id
2050 and ppa.action_type in ('R','Q')
2051 and ppa.payroll_action_id = paa.payroll_action_id)
2052 and PACTION.payroll_id = TPERIOD.payroll_id
2053 and PACTION.date_earned between TPERIOD.start_date and TPERIOD.end_date
2054 and TPTYPE.period_type = TPERIOD.period_type;
2055
2056 -- cursor to get ID for a balance type
2057 cursor c_balance_type(p_name varchar2) is
2058 select bt.balance_type_id
2059 from pay_balance_types bt
2060 where bt.balance_name = p_name ;
2061
2062 -- cursor to get annual leave accrual plan
2063 cursor c_annual_leave_plan(p_assignment_id number
2064 ,p_effective_date date) is
2065 select pap.accrual_plan_id
2066 from pay_accrual_plans pap
2067 , pay_element_entries_f pee
2068 , pay_element_links_f pel
2069 , pay_element_types_f pet
2070 where pee.assignment_id = p_assignment_id
2071 and p_effective_date between pee.effective_start_date
2072 and pee.effective_end_date
2073 and pel.element_link_id = pee.element_link_id
2074 and p_effective_date between pel.effective_start_date
2075 and pel.effective_end_date
2076 and pel.element_type_id = pet.element_type_id
2077 and p_effective_date between pet.effective_start_date
2078 and pet.effective_end_date
2079 and pap.accrual_plan_element_type_id = pet.element_type_id
2080 and pap.accrual_category = 'NZAL' ;
2081
2082 -- cursor to get annual leave in advance payments made during the year
2083 --
2084 -- This is a bit complicated: the hours taken come from the absence
2085 -- record (per_absence_attendances). A corresponding element entry,
2086 -- "absence element entry", is created for each absence record
2087 -- (pay_element_entries_f). When the absence element entry gets processed
2088 -- an indirect result causes a new entry to be created for the "Annual
2089 -- Leave Pay" element type. The pay value run result for the "Annual
2090 -- Leave Pay" element is the amount paid for the leave.
2091
2092 -- Bug no : 2097319 : added or clause in the query to handle the
2093 -- when payroll is used with offsets
2094
2095 cursor c_leave_in_advance(p_accrual_plan_id number
2096 ,p_assignment_id number
2097 ,p_year_end_date date) is
2098 select ab.absence_hours absence_hours
2099 , to_number(rrv2.result_value) absence_pay
2100 from pay_accrual_plans ap -- annual leave accrualplan
2101 , pay_element_entry_values_f eev -- absence element entry
2102 -- "hours taken" entry value
2103 , pay_element_entries_f ee -- absence element entry
2104 , pay_run_results rr -- run result for absence -- element entry
2105 , per_absence_attendances ab -- absence record
2106 , pay_assignment_actions aa -- assignment action for -- absence element entry
2107 , pay_payroll_actions pa -- payroll action for -- absence element entry
2108 , per_time_periods tp
2109 , pay_run_results rr2 -- run result for Annual -- Leave Pay element type
2110 , pay_run_result_values rrv2 -- run result value for -- Annual Leave Pay element -- pay value ,
2111 , pay_element_types_f et2 -- Annual Leave Pay element
2112 , pay_input_values_f iv2 -- Pay Value input value
2113 where ap.accrual_plan_id = p_accrual_plan_id
2114 and eev.input_value_id = ap.pto_input_value_id
2115 and ee.element_entry_id = eev.element_entry_id
2116 and ee.assignment_id = p_assignment_id
2117 and rr.source_id = ee.element_entry_id
2118 and rr.source_type = 'E'
2119 and ee.creator_type = 'A'
2120 and ab.absence_attendance_id = ee.creator_id
2121 and aa.assignment_action_id = rr.assignment_action_id
2122 and pa.payroll_action_id = aa.payroll_action_id
2123 and (
2124 (tp.regular_payment_date <= p_year_end_date
2125 and pa.effective_date between ee.effective_start_date
2126 and ee.effective_end_date
2127 and pa.effective_date between eev.effective_start_date
2128 and eev.effective_end_date
2129 and pa.time_period_id=tp.time_period_id
2130 )
2131 or
2132 (
2133 pa.payroll_id = tp.payroll_id
2134 and pa.date_earned between tp.start_date and tp.end_date
2135 and p_year_end_date >= tp.start_date
2136 and pa.date_earned between ee.effective_start_date
2137 and ee.effective_end_date
2138 and pa.date_earned between eev.effective_start_date
2139 and eev.effective_end_date
2140 )
2141 )
2142 and et2.element_name = 'Annual Leave Pay'
2143 and pa.effective_date between et2.effective_start_date
2144 and et2.effective_end_date
2145 and rr2.element_type_id = et2.element_type_id
2146 and rr2.source_id = ee.element_entry_id
2147 and rr2.source_type = 'I'
2148 and rr2.assignment_action_id = aa.assignment_action_id
2149 and rrv2.run_result_id = rr2.run_result_id
2150 and iv2.input_value_id = rrv2.input_value_id
2151 and pa.effective_date between iv2.effective_start_date
2152 and iv2.effective_end_date
2153 and iv2.name = 'Pay Value'
2154 order by
2155 aa.action_sequence desc
2156 , ab.date_start desc
2157 , to_date(ab.time_start, 'hh24:mi') ;
2158
2159
2160 begin
2161
2162 -- trace input variables
2163 hr_utility.trace('In: ' || l_procedure_name) ;
2164 hr_utility.trace(' p_business_group_id: ' || to_char(p_business_group_id)) ;
2165 hr_utility.trace(' p_payroll_id: ' || to_char(p_payroll_id)) ;
2166 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
2167 hr_utility.trace(' p_asg_hours: ' || to_char(p_asg_hours)) ;
2168 hr_utility.trace(' p_year_end_date: ' || to_char(p_year_end_date, 'dd Mon yyyy')) ;
2169 hr_utility.trace(' p_in_advance_pay_carryover: ' || to_char(p_in_advance_pay_carryover)) ;
2170 hr_utility.trace(' p_in_advance_hours_carryover: ' || to_char(p_in_advance_hours_carryover)) ;
2171
2172 -- get values for ANNUAL_LEAVE_IN_ADVANCE_HOURS_ASG_HOL_YTD and
2173 -- ANNUAL_LEAVE_IN_ADVANCE_PAY_ASG_HOL_YTD balances as at the end of the
2174 -- holiday year
2175
2176 open c_balance_type('Annual Leave in Advance Hours') ;
2177 fetch c_balance_type
2178 into l_balance_type_id ;
2179 if c_balance_type%notfound
2180 then
2181 close c_balance_type ;
2182 raise e_missing_balance_type ;
2183 end if ;
2184 close c_balance_type ;
2185
2186 l_annual_leave_in_advance_hrs := hr_nzbal.calc_asg_hol_ytd_date
2187 (p_assignment_id
2188 ,l_balance_type_id
2189 ,p_year_end_date) ;
2190
2191 l_annual_leave_in_advance_hrs := l_annual_leave_in_advance_hrs
2192 + p_in_advance_hours_carryover ;
2193
2194 open c_balance_type('Annual Leave in Advance Pay') ;
2195 fetch c_balance_type
2196 into l_balance_type_id ;
2197 if c_balance_type%notfound
2198 then
2199 close c_balance_type ;
2200 raise e_missing_balance_type ;
2201 end if ;
2202 close c_balance_type ;
2203
2204 l_annual_leave_in_advance_pay := hr_nzbal.calc_asg_hol_ytd_date
2205 (p_assignment_id
2206 ,l_balance_type_id
2207 ,p_year_end_date) ;
2208
2209 l_annual_leave_in_advance_pay := l_annual_leave_in_advance_pay
2210 + p_in_advance_pay_carryover ;
2211
2212 hr_utility.trace('Ann_leave_adv_hrs= '|| to_char(l_annual_leave_in_advance_hrs));
2213 hr_utility.trace('Ann_leave_adv_pay= '|| to_char(l_annual_leave_in_advance_pay));
2214 -- if there is no annual leave in advance or annual leave in advance
2215 -- carryover then we can finish now
2216 if l_annual_leave_in_advance_hrs = 0
2217 then
2218
2219 hr_utility.trace(' no in advance leave to process') ;
2220
2221 -- set outputs
2222 p_in_advance_pay_carryover := 0 ;
2223 p_in_advance_hours_carryover := 0 ;
2224 l_eoy_adjustment := 0 ;
2225
2226 -- trace output variables
2227 hr_utility.trace(' p_in_advance_pay_carryover: ' || to_char(p_in_advance_pay_carryover)) ;
2228 hr_utility.trace(' p_in_advance_hours_carryover: ' || to_char(p_in_advance_hours_carryover)) ;
2229 hr_utility.trace(' return: ' || to_char(l_eoy_adjustment)) ;
2230
2231 return l_eoy_adjustment ;
2232
2233 end if ;
2234
2235 hr_utility.trace(' in advance leave to process') ;
2236
2237 -- Now work out what the accrual for the holiday year was. First
2238 -- get the accrual plan ID, then find the accrual up until the end of the
2239 -- holiday year, then the accrual up until the end of the previous holiday
2243 open c_annual_leave_plan(p_assignment_id
2240 -- year and subtract the values
2241
2242 -- get the accrual plan ID
2244 ,p_year_end_date) ;
2245 fetch c_annual_leave_plan
2246 into l_accrual_plan_id ;
2247 if c_annual_leave_plan%notfound
2248 then
2249 close c_annual_leave_plan ;
2250 raise e_missing_accrual_plan ;
2251 end if ;
2252 close c_annual_leave_plan ;
2253
2254 per_accrual_calc_functions.get_accrual
2255 (p_assignment_id => p_assignment_id
2256 ,p_calculation_date => p_year_end_date
2257 ,p_plan_id => l_accrual_plan_id
2258 ,p_business_group_id => p_business_group_id
2259 ,p_payroll_id => p_payroll_id
2260 ,p_start_date => l_start_date
2261 ,p_end_date => l_end_date
2262 ,p_accrual_end_date => l_accrual_end_date
2263 ,p_accrual => l_accrual) ;
2264
2265 per_accrual_calc_functions.get_accrual
2266 (p_assignment_id => p_assignment_id
2267 ,p_calculation_date => add_months(p_year_end_date, -12)
2268 ,p_plan_id => l_accrual_plan_id
2269 ,p_business_group_id => p_business_group_id
2270 ,p_payroll_id => p_payroll_id
2271 ,p_start_date => l_start_date
2272 ,p_end_date => l_end_date
2273 ,p_accrual_end_date => l_accrual_end_date
2274 ,p_accrual => l_accrual_tmp) ;
2275
2276 l_accrual := l_accrual - l_accrual_tmp ;
2277
2278 hr_utility.trace(' l_accrual: ' || to_char(l_accrual)) ;
2279
2280 -- Now we know how many hours have been taken in advance
2281 -- (l_annual_leave_in_advance_hrs) and how many hours were accrued during
2282 -- the year (l_accrual). We also know the value, as paid, of the in
2283 -- advance hours (l_annual_leave_in_advance_pay).
2284
2285 -- Next we need to determine how much of the in advance should be adjusted
2286 -- this year.
2287
2288 -- If l_annual_leave_in_advance_hrs is less then or equal to l_accrual
2289 -- then all the in advance pay should be considered this year. No
2290 -- advance hours or advance pay will need to be carried over for future
2291 -- processing.
2292
2293 -- If l_annual_leave_in_advance_hrs is greater then l_accrual we need to
2294 -- carryover the difference for consideration in a future year. The
2295 -- number of hours to consider this year will be l_accrual. We need to
2296 -- work out the value of those hours. The advance hours and pay not
2297 -- adjusted this year will be carried over for future processing.
2298
2299 if l_annual_leave_in_advance_hrs <= l_accrual
2300 then
2301
2302 hr_utility.trace(' no in advance leave carryover to process') ;
2303
2304 -- all the advance pay will be adjusted this year
2305 l_in_advance_hours_carryover := 0 ;
2306 l_hours_to_adjust := l_annual_leave_in_advance_hrs ;
2307 l_in_advance_pay_carryover := 0 ;
2308 l_pay_to_adjust := l_annual_leave_in_advance_pay ;
2309
2310 else -- if l_annual_leave_in_advance_hrs > l_accrual
2311
2312 hr_utility.trace(' in advance leave carryover to process') ;
2313
2314 -- some of the adjustment will be carried over. Work out what
2315 -- portion of the advance should be dealth with now.
2316
2317 -- work out the hours to adjust and the hours to carryover
2318 l_in_advance_hours_carryover := l_annual_leave_in_advance_hrs
2319 - l_accrual ;
2320 l_hours_to_adjust := l_accrual ;
2321
2322 hr_utility.trace('l_hrs_to_adj= '||l_hours_to_adjust);
2323
2324 -- work out the pay to adjust and the pay to carryover
2325 --
2326 -- to find the pay to adjust we need to loop through the in advance
2327 -- absence records and find those that contribute to the hours to
2328 -- adjust
2329
2330 -- initialise some variables used in the following loop
2331 l_hours_running_total := 0 ;
2332 l_prev_hours_running_total := 0 ;
2333 l_pay_running_total := 0 ;
2334 l_prev_pay_running_total := 0 ;
2335 l_in_advance_pay_carryover := 0 ;
2336 l_pay_to_adjust := 0 ;
2337
2338 -- now loop through the absence records in reverse order
2339 -- the first records will be those contributing to the carryover
2340 -- so they can be ignored. The ones after the those contributing to the
2341 -- carryover will contribute to the leave we need to adjust now (after
2342 -- the leave we need to adjust now will come the leave we have
2343 -- previously adjusted. A single leave record may span the borders
2344 -- between the carryover and the leave to adjust now, and/or the border
2345 -- between the in advance leave and the leave previously adjusted.
2346
2347 open c_leave_in_advance(l_accrual_plan_id
2348 ,p_assignment_id
2349 ,p_year_end_date) ;
2350
2351 loop
2352
2353 fetch c_leave_in_advance
2354 into l_absence_hours
2355 , l_absence_pay ;
2356 if c_leave_in_advance%notfound
2357 then
2358 close c_leave_in_advance ;
2359 raise e_missing_leave_in_advance ;
2360 end if ;
2361
2362 l_hours_running_total := l_hours_running_total + l_absence_hours ;
2366 hr_utility.trace(' l_absence_pay: ' || to_char(l_absence_pay)) ;
2363 l_pay_running_total := l_pay_running_total + l_absence_pay ;
2364
2365 hr_utility.trace(' l_absence_hours: ' || to_char(l_absence_hours)) ;
2367
2368 -- test to see if we've past the records that are in the carryover
2369 if l_hours_running_total > l_in_advance_hours_carryover
2370 then
2371
2372 -- test to see if this is the first record to be included in the
2373 -- adjustment
2374 if l_prev_hours_running_total < l_in_advance_hours_carryover
2375 then
2376
2377 -- test to see if this is also the last record to be included in
2378 -- this adjustment
2379 if l_hours_running_total >= l_annual_leave_in_advance_hrs
2380 then
2381
2382 hr_utility.trace(' processing first and last absence record to adjust') ;
2383
2384 -- first and last record: work out how much of this absence
2385 -- record should be included in this adjustment
2386
2387 l_hours := (l_hours_running_total - l_in_advance_hours_carryover)
2388 - (l_hours_running_total - l_annual_leave_in_advance_hrs) ;
2389
2390 l_pay := (l_hours / l_absence_hours) * l_absence_pay ;
2391
2392 hr_utility.trace(' l_hours: ' || to_char(l_hours)) ;
2393 hr_utility.trace(' l_pay: ' || to_char(l_pay)) ;
2394
2395 l_pay_to_adjust := l_pay_to_adjust + l_pay ;
2396
2397 -- exit from loop
2398 exit ;
2399
2400 else
2401
2402 hr_utility.trace(' processing first record to adjust') ;
2403
2404 -- first record only: work out how much of this absence record
2405 -- should be included in this adjustment
2406
2407 l_hours := l_hours_running_total - l_in_advance_hours_carryover ;
2408 l_pay := (l_hours / l_absence_hours) * l_absence_pay ;
2409
2410 hr_utility.trace(' l_hours: ' || to_char(l_hours)) ;
2411 hr_utility.trace(' l_pay: ' || to_char(l_pay)) ;
2412
2413 l_pay_to_adjust := l_pay_to_adjust + l_pay ;
2414
2415 end if ;
2416
2417 -- test to see if this is the last record to be included in the
2418 -- adjustment
2419 elsif l_hours_running_total >= l_annual_leave_in_advance_hrs
2420 then
2421
2422 hr_utility.trace(' processing last record to adjust') ;
2423
2424 l_hours := l_absence_hours - (l_hours_running_total - l_annual_leave_in_advance_hrs) ;
2425 l_pay := (l_hours / l_absence_hours) * l_absence_pay ;
2426
2427 hr_utility.trace(' l_hours: ' || to_char(l_hours)) ;
2428 hr_utility.trace(' l_pay: ' || to_char(l_pay)) ;
2429
2430 l_pay_to_adjust := l_pay_to_adjust + l_pay ;
2431
2432 -- exit from loop
2433 exit ;
2434
2435 -- otherwise this is a record between the first and last to be
2436 -- included in the adjustment
2437 else
2438
2439 hr_utility.trace(' processing middle in advance absence record to adjust') ;
2440
2441 -- add all of the value of this absence
2442 l_pay_to_adjust := l_pay_to_adjust + l_absence_pay ;
2443
2444 end if ;
2445
2446 end if ;
2447
2448 l_prev_hours_running_total := l_hours_running_total ;
2449 l_prev_pay_running_total := l_pay_running_total ;
2450
2451 end loop ;
2452
2453 close c_leave_in_advance ;
2454
2455 l_in_advance_pay_carryover := l_annual_leave_in_advance_pay - l_pay_to_adjust ;
2456
2457 end if ; -- if l_annual_leave_in_advance_hrs <= l_accrual
2458
2459 -- We now know how many hours (l_hours_to_adjust) and how much pay
2460 -- (l_pay_to_adjust) this year.
2461
2462 hr_utility.trace(' l_hours_to_adjust: ' || to_char(l_hours_to_adjust)) ;
2463 hr_utility.trace(' l_pay_to_adjust: ' || to_char(l_pay_to_adjust)) ;
2464
2465 -- get the ID of the Gross Earnings for Holiday Pay balance
2466 open c_balance_type('Gross Earnings for Holiday Pay') ;
2467 fetch c_balance_type
2468 into l_balance_type_id ;
2469 if c_balance_type%notfound
2470 then
2471 close c_balance_type ;
2472 raise e_missing_balance_type ;
2473 end if ;
2474 close c_balance_type ;
2475
2476 l_gross_earnings_for_hol_pay := hr_nzbal.calc_asg_hol_ytd_date
2477 (p_assignment_id
2478 ,l_balance_type_id
2479 ,p_year_end_date) ;
2480
2481 hr_utility.trace(' l_gross_earnings_for_hol_pay: ' || to_char(l_gross_earnings_for_hol_pay)) ;
2482
2483 -- Get number of eligible weeks in year (complete weeks of special and
2484 -- protected voluntary service leave are subtracted from the number of
2485 -- weeks in the year.
2486 l_num_weeks := num_weeks_for_avg_earnings
2487 (p_assignment_id
2488 ,add_months(p_year_end_date + 1, -12)) ;
2489
2490 hr_utility.trace(' l_num_weeks: ' || to_char(l_num_weeks)) ;
2491
2492 open get_pay_period_start_date(p_assignment_id);
2496 /* following check is to find whether holiday anniversary date lies in betw
2493 fetch get_pay_period_start_date into l_pay_period_start_date,l_num_of_pay_periods_per_year;
2494 close get_pay_period_start_date;
2495
2497 een the pay
2498 period and if so, find the number of weeks in the pay period */
2499 if(to_char((p_year_end_date+1),'dd')<> to_char(l_pay_period_start_date,'
2500 dd'))
2501 then
2502 l_extra_weeks := 52/l_num_of_pay_periods_per_year;
2503 l_offset_flag := true;
2504 end if;
2505
2506 /* if holiday anniversary is inbetween the pay period add the extra pay p
2507 eriod weeks */
2508 if l_offset_flag then
2509 l_num_weeks:= l_num_weeks + l_extra_weeks;
2510 end if;
2511 -- work out the value of the advance leave at the average earnings rate
2512 l_recalculated_pay := ((l_gross_earnings_for_hol_pay / l_num_weeks)
2513 / p_asg_hours)
2514 * l_hours_to_adjust ;
2515
2516 hr_utility.trace(' l_recalculated_pay: ' || to_char(l_recalculated_pay)) ;
2517 hr_utility.trace(' Pay_to_adjust= '||l_pay_to_adjust);
2518 -- work out the adjustment (cannot be negative)
2519 l_eoy_adjustment := l_recalculated_pay - l_pay_to_adjust ;
2520 /*Bug 2950172 - Removed greatest(...) logic so as to return negative value
2521 to the formula. The negative value is handled in the formula.*/
2522
2523 -- set outputs
2524 p_in_advance_pay_carryover := l_in_advance_pay_carryover ;
2525 p_in_advance_hours_carryover := l_in_advance_hours_carryover ;
2526
2527 -- trace output variables
2528 hr_utility.trace(' p_in_advance_pay_carryover: ' || to_char(p_in_advance_pay_carryover)) ;
2529 hr_utility.trace(' p_in_advance_hours_carryover: ' || to_char(p_in_advance_hours_carryover)) ;
2530 hr_utility.trace(' return: ' || to_char(l_eoy_adjustment)) ;
2531
2532 return l_eoy_adjustment ;
2533
2534 exception
2535 when e_missing_balance_type
2536 then
2537 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL') ;
2538 hr_utility.set_message_token('PROCEDURE', l_procedure_name) ;
2539 hr_utility.set_message_token('STEP', 'Missing Balance Type Exception') ;
2540 hr_utility.raise_error ;
2541
2542 when e_missing_accrual_plan
2543 then
2544 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL') ;
2545 hr_utility.set_message_token('PROCEDURE', l_procedure_name) ;
2546 hr_utility.set_message_token('STEP', 'Missing Accrual Plan Exception') ;
2547 hr_utility.raise_error ;
2548
2549 when e_missing_leave_in_advance
2550 then
2551 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL') ;
2552 hr_utility.set_message_token('PROCEDURE', l_procedure_name) ;
2553 hr_utility.set_message_token('STEP', 'Missing Leave in Advance Exception') ;
2554 hr_utility.raise_error ;
2555
2556 end annual_leave_eoy_adjustment ;
2557
2558 /*---------------------------------------------------------------------
2559 Name : get_weekdays_in_period
2560 Purpose : To get the number of weekdays in a date range
2561 Returns : Number of Weekdays if successful, NULL otherwise
2562 ---------------------------------------------------------------------*/
2563
2564 FUNCTION get_weekdays_in_period
2565 (p_start_date IN DATE
2566 ,p_end_date IN DATE)
2567 RETURN NUMBER IS
2568 l_proc VARCHAR2(72) := g_package||'get_weekdays_in_period';
2569 l_day_count NUMBER := 0;
2570 l_day DATE;
2571 BEGIN
2572
2573 hr_utility.trace('In: '||l_proc);
2574 hr_utility.trace(' p_start_date: ' || to_char(p_start_date,'dd Mon yyyy')) ;
2575 hr_utility.trace(' p_end_date: ' || to_char(p_end_date,'dd Mon yyyy')) ;
2576
2577 IF (p_start_date > p_end_date)
2578 THEN
2579 hr_utility.trace('Crash Out: '||l_proc);
2580 hr_utility.set_message(801,'HR_NZ_INVALID_DATE_RANGE');
2581 hr_utility.raise_error;
2582 END IF;
2583
2584 hr_utility.set_location(l_proc,5);
2585 l_day := p_start_date;
2586 WHILE (l_day <= p_end_date)
2587 LOOP
2588 IF (TO_CHAR(l_day,'DY') IN ('MON','TUE','WED','THU','FRI'))
2589 THEN
2590 l_day_count := l_day_count + 1;
2591 END IF;
2592 l_day := l_day + 1;
2593 END LOOP;
2594 hr_utility.trace(' return: ' || to_char(l_day_count)) ;
2595 hr_utility.trace('Out: '||l_proc);
2596 RETURN l_day_count;
2597
2598 END get_weekdays_in_period;
2599
2600 -- Bug# 2127114 Added the following function
2601 --------------------------------------------------------------------
2602 -- get_leap_year_mon function
2603 -- function called by accrual_daily_basis function
2604 -- This function finds whether 29-feb of leap year present between
2605 -- the calculation period and if it is present ignores it
2606 --------------------------------------------------------------------
2607 function get_leap_year_mon
2608 (p_start_date in date
2609 ,p_end_date in date)
2610 return number is
2611
2612 l_date date;
2613 l_curr_year varchar2(4);
2614
2615 begin
2616 l_curr_year := to_char(p_start_date,'YYYY');
2617 if to_number(l_curr_year)/4 = trunc(to_number(l_curr_year)/4)
2618 then
2622 then
2619 l_date := to_date('29-02'||to_char(p_start_date,'YYYY'),'DD-MM-YYYY');
2620
2621 if l_date between p_start_date and p_end_date
2623 return 1;
2624 else
2625 return 0;
2626 end if;
2627 else
2628 return 0;
2629 end if;
2630
2631 end get_leap_year_mon;
2632
2633 /* end of function */
2634
2635
2636 -----------------------------------------------------------------------------
2637 -- ====================================
2638 -- 3064179
2639 -- This function becomes on 01-APR-2004
2640 -- ====================================
2641
2642 -- accrual_period_basis function
2643 --
2644 -- public function called by NZ_STAT_ANNUAL_LEAVE_ACCRUAL_PERIOD_BASIS
2645 -- PTO accrual formula.
2646 -----------------------------------------------------------------------------
2647
2648 function accrual_period_basis
2649 (p_payroll_id in number
2650 ,p_accrual_plan_id in number
2651 ,p_assignment_id in number
2652 ,p_calculation_start_date in date
2653 ,p_calculation_end_date in date
2654 ,p_service_start_date in date
2655 ,p_business_group_hours in number
2656 ,p_business_group_freq in varchar2)
2657 return number is
2658
2659 l_procedure_name varchar2(61) := 'hr_nz_holidays.accrual_period_basis' ;
2660 l_accrual number := 0 ;
2661 l_accrual_band_cache t_accrual_band_tab ;
2662 l_asg_work_day_info_cache t_asg_work_day_info_tab ;
2663 l_counter integer ;
2664 l_years_service number ;
2665 l_annual_accrual number ;
2666 l_days_in_whole_period integer ;
2667 l_days_in_part_period integer ;
2668 l_start_date date ;
2669 l_end_date date ;
2670 l_period_accrual number ;
2671 l_asg_working_hours per_all_assignments_f.normal_hours%type ;
2672 l_pay_periods_per_year per_time_period_types.number_per_fiscal_year%type ;
2673
2674 e_accrual_function_failure exception ;
2675
2676 -- cursor to get number of periods per year
2677
2678 cursor c_number_of_periods_per_year (p_payroll_id number
2679 ,p_effective_date date) is
2680 select tpt.number_per_fiscal_year
2681 from pay_payrolls_f p
2682 , per_time_period_types tpt
2683 where p.payroll_id = p_payroll_id
2684 and p_effective_date between p.effective_start_date
2685 and p.effective_end_date
2686 and tpt.period_type = p.period_type ;
2687
2688 -- cursor to get assignment work day information
2689
2690 cursor c_asg_work_day_history(p_assignment_id number
2691 ,p_start_date date
2692 ,p_end_date date) is
2693 select a.effective_start_date
2694 , a.effective_end_date
2695 , a.normal_hours
2696 , a.frequency
2697 from per_assignments_f a
2698 where a.assignment_id = p_assignment_id
2699 and a.effective_start_date <= p_end_date
2700 and a.effective_end_date >= p_start_date
2701 order by
2702 a.effective_start_date ;
2703
2704 -- cursor to get accrual band details
2705
2706 cursor c_accrual_bands (p_accrual_plan_id number) is
2707 select ab.lower_limit
2708 , ab.upper_limit
2709 , ab.annual_rate
2710 from pay_accrual_bands ab
2711 where ab.accrual_plan_id = p_accrual_plan_id
2712 order by
2713 ab.lower_limit ;
2714
2715 -- cursor to get time periods to process
2716
2717 cursor c_periods (p_payroll_id number
2718 ,p_start_date date
2719 ,p_end_date date) is
2720 select tp.start_date
2721 , tp.end_date
2722 from per_time_periods tp
2723 where tp.payroll_id = p_payroll_id
2724 and tp.start_date <= p_end_date
2725 and tp.end_date >= p_start_date
2726 order by
2727 tp.start_date ;
2728
2729 -- local function to get accrual annual rate from PL/SQL table
2730
2731 function accrual_annual_rate(p_years_service number) return number is
2732
2733 l_procedure_name varchar2(61) := 'accrual_annual_rate' ;
2734 l_annual_accrual pay_accrual_bands.annual_rate%type ;
2735 l_counter integer := 1 ;
2736 l_band_notfound_flag boolean := true ;
2737
2738 begin
2739
2740 -- hr_utility.trace(' In: ' || l_procedure_name) ;
2741
2742 -- loop through the PL/SQL table looking for a likely accrual band
2743 while l_accrual_band_cache.count > 0
2744 and l_band_notfound_flag
2745 and l_counter <= l_accrual_band_cache.last
2746 loop
2747
2748 -- JTurner, 14 Feb 2000, 1189790: changed from using "between"
2749 if p_years_service >= l_accrual_band_cache(l_counter).lower_limit
2750 and p_years_service < l_accrual_band_cache(l_counter).upper_limit
2751 then
2752
2756 end if ;
2753 l_annual_accrual := l_accrual_band_cache(l_counter).annual_rate ;
2754 l_band_notfound_flag := false ;
2755
2757
2758 l_counter := l_counter + 1 ;
2759
2760 end loop ;
2761
2762 -- raise error if no accrual band found
2763 if l_band_notfound_flag
2764 then
2765
2766 raise e_accrual_function_failure ;
2767
2768 end if ;
2769
2770 -- hr_utility.trace(' Out: ' || l_procedure_name) ;
2771 return l_annual_accrual ;
2772
2773 end accrual_annual_rate ;
2774
2775 -- local function to get asg working hours from PL/SQL table
2776
2777 function asg_working_hours(p_effective_date date
2778 ,p_frequency varchar2) return number is
2779
2780 l_procedure_name varchar2(61) := 'asg_working_hours' ;
2781 l_asg_working_hours per_all_assignments_f.normal_hours%type ;
2782 l_counter integer := 1 ;
2783 l_hours_notfound_flag boolean := true ;
2784
2785 begin
2786
2787 -- hr_utility.trace(' In: ' || l_procedure_name) ;
2788
2789 -- loop through the PL/SQL table looking for a likely accrual band
2790 while l_asg_work_day_info_cache.count > 0
2791 and l_hours_notfound_flag
2792 and l_counter <= l_asg_work_day_info_cache.last
2793 loop
2794
2795 if p_effective_date between l_asg_work_day_info_cache(l_counter).effective_start_date
2796 and l_asg_work_day_info_cache(l_counter).effective_end_date
2797 and l_asg_work_day_info_cache(l_counter).frequency = p_frequency
2798 then
2799
2800 l_asg_working_hours := l_asg_work_day_info_cache(l_counter).normal_hours ;
2801 l_hours_notfound_flag := false ;
2802
2803 end if ;
2804
2805 l_counter := l_counter + 1 ;
2806
2807 end loop ;
2808
2809 -- raise error if no working hours found
2810 if l_hours_notfound_flag
2811 then
2812
2813 raise e_accrual_function_failure ;
2814
2815 end if ;
2816
2817 -- hr_utility.trace(' Out: ' || l_procedure_name) ;
2818 return l_asg_working_hours ;
2819
2820 end asg_working_hours ;
2821
2822 begin
2823
2824 hr_utility.trace('In: ' || l_procedure_name) ;
2825 hr_utility.trace(' p_payroll_id: ' || to_char(p_payroll_id)) ;
2826 hr_utility.trace(' p_accrual_plan_id: ' || to_char(p_accrual_plan_id)) ;
2827 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
2828 hr_utility.trace(' p_calculation_start_date: ' || to_char(p_calculation_start_date, 'dd-Mon-yyyy')) ;
2829 hr_utility.trace(' p_calculation_end_date: ' || to_char(p_calculation_end_date, 'dd-Mon-yyyy')) ;
2830 hr_utility.trace(' p_service_start_date: ' || to_char(p_service_start_date, 'dd-Mon-yyyy')) ;
2831 hr_utility.trace(' p_business_group_hours: ' || to_char(p_business_group_hours)) ;
2832 hr_utility.trace(' p_business_group_freq: ' || p_business_group_freq) ;
2833
2834 -- cache the assignment's work day history
2835 l_counter := 1 ;
2836
2837 for r_asg_work_day in c_asg_work_day_history(p_assignment_id
2838 ,p_calculation_start_date
2839 ,p_calculation_end_date)
2840 loop
2841
2842 l_asg_work_day_info_cache(l_counter).effective_start_date := r_asg_work_day.effective_start_date ;
2843 l_asg_work_day_info_cache(l_counter).effective_end_date := r_asg_work_day.effective_end_date ;
2844 l_asg_work_day_info_cache(l_counter).normal_hours := r_asg_work_day.normal_hours ;
2845 l_asg_work_day_info_cache(l_counter).frequency := r_asg_work_day.frequency ;
2846
2847 l_counter := l_counter + 1 ;
2848
2849 end loop ; -- c_asg_work_day_history
2850
2851 -- cache the accrual bands
2852 l_counter := 1 ;
2853
2854 for r_accrual_band in c_accrual_bands(p_accrual_plan_id)
2855 loop
2856
2857 l_accrual_band_cache(l_counter).lower_limit := r_accrual_band.lower_limit ;
2858 l_accrual_band_cache(l_counter).upper_limit := r_accrual_band.upper_limit ;
2859 l_accrual_band_cache(l_counter).annual_rate := r_accrual_band.annual_rate ;
2860
2861 l_counter := l_counter + 1 ;
2862
2863 end loop ; -- c_accrual_bands
2864
2865 -- get the number of periods per year
2866 open c_number_of_periods_per_year(p_payroll_id, p_calculation_start_date) ;
2867 fetch c_number_of_periods_per_year
2868 into l_pay_periods_per_year ;
2869 close c_number_of_periods_per_year ;
2870
2871 -- loop through the payroll periods
2872 for r_period in c_periods(p_payroll_id
2873 ,p_calculation_start_date
2874 ,p_calculation_end_date)
2875 loop
2876
2877 -- how many years of service does the assignment have (as at the end of the period)
2878 l_years_service := floor(months_between(r_period.end_date, p_service_start_date) / 12) ;
2879
2880 -- get the accrual band
2881 l_annual_accrual := accrual_annual_rate(l_years_service) ;
2882
2883 -- get the assignment's normal working hours
2884 -- JTurner, 21 Mar 2000, 1243407: changed to use least of period end
2885 -- and calculation end date instead of just period end date.
2889 -- the accrual rate in the accrual band is for assignments that work the
2886 l_asg_working_hours := asg_working_hours(least(r_period.end_date
2887 ,p_calculation_end_date)
2888 ,p_business_group_freq) ;
2890 -- business group's default working hours. Now prorate the accrual rate
2891 -- based on the proporation of the business group hours that the
2892 -- assignment works.
2893 l_annual_accrual := l_annual_accrual * (l_asg_working_hours / p_business_group_hours) ;
2894
2895 l_period_accrual := l_annual_accrual / l_pay_periods_per_year ;
2896
2897 -- how many days are there in the whole period?
2898 l_days_in_whole_period := (r_period.end_date - r_period.start_date) + 1 ;
2899
2900 -- we may be dealing with a part period here, ie if the calculation
2901 -- start date is part way through the first period or if the
2902 -- calculation end date is part way through the last period.
2903 if p_calculation_start_date between r_period.start_date
2904 and r_period.end_date
2905 then
2906 l_start_date := p_calculation_start_date ;
2907 else
2908 l_start_date := r_period.start_date ;
2909 end if ;
2910
2911 if p_calculation_end_date between r_period.start_date
2912 and r_period.end_date
2913 then
2914 l_end_date := p_calculation_end_date ;
2915 else
2916 l_end_date := r_period.end_date ;
2917 end if ;
2918
2919 -- how many days are there in the part period? (Note it may not
2920 -- actually be a part period).
2921 l_days_in_part_period := (l_end_date - l_start_date) + 1 ;
2922
2923 -- prorate the period accrual
2924 l_period_accrual := l_period_accrual * (l_days_in_part_period / l_days_in_whole_period) ;
2925
2926 l_accrual := l_accrual + l_period_accrual ;
2927
2928 end loop ; -- c_periods
2929
2930 hr_utility.trace(' return: ' || to_char(l_accrual)) ;
2931 hr_utility.trace('Out: ' || l_procedure_name) ;
2932 return l_accrual ;
2933
2934 exception
2935 when e_accrual_function_failure
2936 then
2937 hr_utility.trace('Crash Out: ' || l_procedure_name) ;
2938 hr_utility.set_message(801, 'HR_NZ_ACCRUAL_FUNCTION_FAILURE') ;
2939 hr_utility.raise_error ;
2940
2941 end accrual_period_basis ;
2942
2943 -----------------------------------------------------------------------------
2944 -- ====================================
2945 -- 3064179
2946 -- This function becomes on 01-APR-2004
2947 -- ====================================
2948
2949 -- accrual_daily_basis function
2950 --
2951 -- public function called by NZ_STAT_ANNUAL_LEAVE_ACCRUAL_DAILY_BASIS
2952 -- PTO accrual formula.
2953 -----------------------------------------------------------------------------
2954
2955 function accrual_daily_basis
2956 (p_payroll_id in number
2957 ,p_accrual_plan_id in number
2958 ,p_assignment_id in number
2959 ,p_calculation_start_date in date
2960 ,p_calculation_end_date in date
2961 ,p_service_start_date in date
2962 ,p_anniversary_date in date
2963 ,p_business_group_hours in number
2964 ,p_business_group_freq in varchar2)
2965 return number is
2966
2967 l_procedure_name varchar2(61) := 'hr_nz_holidays.accrual_daily_basis' ;
2968 l_accrual number := 0 ;
2969 l_accrual_band_cache t_accrual_band_tab ;
2970 l_asg_work_day_info_cache t_asg_work_day_info_tab ;
2971 l_counter integer ;
2972 l_years_service number ;
2973 l_annual_accrual number ;
2974 l_days_in_year integer ;
2975 l_days_in_part_period integer ;
2976 l_next_anniversary_date date ;
2977 l_start_date date ;
2978 l_end_date date ;
2979 l_period_accrual number ;
2980 l_asg_working_hours per_all_assignments_f.normal_hours%type ;
2981 l_pay_periods_per_year per_time_period_types.number_per_fiscal_year%type ;
2982 l_calc_service_date date;
2983 l_annual_accrual_1 number;
2984 l_annual_accrual_2 number;
2985 l_asg_working_hours_1 per_all_assignments_f.normal_hours%type;
2986 l_asg_working_hours_2 per_all_assignments_f.normal_hours%type;
2987 l_days_in_part_period_1 integer;
2988 l_days_in_part_period_2 integer;
2989 l_counter_1 integer;
2990 l_counter_2 integer;
2991 l_counter_3 integer;
2992 l_least_date date;
2993 l_check_flag boolean;
2994 e_accrual_function_failure exception ;
2995 -- cursor to get number of periods per year
2996
2997 cursor c_number_of_periods_per_year (p_payroll_id number
2998 ,p_effective_date date) is
2999 select tpt.number_per_fiscal_year
3000 from pay_payrolls_f p
3001 , per_time_period_types tpt
3002 where p.payroll_id = p_payroll_id
3006
3003 and p_effective_date between p.effective_start_date
3004 and p.effective_end_date
3005 and tpt.period_type = p.period_type ;
3007 -- cursor to get assignment work day information
3008
3009 cursor c_asg_work_day_history(p_assignment_id number
3010 ,p_start_date date
3011 ,p_end_date date) is
3012 select a.effective_start_date
3013 , a.effective_end_date
3014 , a.normal_hours
3015 , a.frequency
3016 from per_assignments_f a
3017 where a.assignment_id = p_assignment_id
3018 and a.effective_start_date <= p_end_date
3019 and a.effective_end_date >= p_start_date
3020 order by
3021 a.effective_start_date ;
3022
3023 -- cursor to get accrual band details
3024
3025 cursor c_accrual_bands (p_accrual_plan_id number) is
3026 select ab.lower_limit
3027 , ab.upper_limit
3028 , ab.annual_rate
3029 from pay_accrual_bands ab
3030 where ab.accrual_plan_id = p_accrual_plan_id
3031 order by
3032 ab.lower_limit ;
3033
3034 -- cursor to get time periods to process
3035
3036 cursor c_periods (p_payroll_id number
3037 ,p_start_date date
3038 ,p_end_date date) is
3039 select tp.start_date
3040 , tp.end_date
3041 from per_time_periods tp
3042 where tp.payroll_id = p_payroll_id
3043 and tp.start_date <= p_end_date
3044 and tp.end_date >= p_start_date
3045 order by
3046 tp.start_date ;
3047
3048 -- local function to get accrual annual rate from PL/SQL table
3049
3050 function accrual_annual_rate(p_years_service number) return number is
3051
3052 l_procedure_name varchar2(61) := 'accrual_annual_rate' ;
3053 l_annual_accrual pay_accrual_bands.annual_rate%type ;
3054 l_counter integer := 1 ;
3055 l_band_notfound_flag boolean := true ;
3056
3057 begin
3058
3059 hr_utility.trace('acc_band_cache_ct '||l_accrual_band_cache.count);
3060 hr_utility.trace('l_counter '||l_counter);
3061 hr_utility.trace('l_accrual_band_cache.last '||l_accrual_band_cache.last);
3062
3063 -- hr_utility.trace(' In: ' || l_procedure_name) ;
3064
3065 -- loop through the PL/SQL table looking for a likely accrual band
3066 while l_accrual_band_cache.count > 0
3067 and l_band_notfound_flag
3068 and l_counter <= l_accrual_band_cache.last
3069 loop
3070
3071 -- JTurner, 14 Feb 2000, 1189790: changed from using "between"
3072 if p_years_service >= l_accrual_band_cache(l_counter).lower_limit
3073 and p_years_service < l_accrual_band_cache(l_counter).upper_limit
3074 then
3075
3076 l_annual_accrual := l_accrual_band_cache(l_counter).annual_rate ;
3077 l_band_notfound_flag := false ;
3078
3079 end if ;
3080
3081 l_counter := l_counter + 1 ;
3082
3083 end loop ;
3084
3085 -- raise error if no accrual band found
3086 if l_band_notfound_flag
3087 then
3088
3089 raise e_accrual_function_failure ;
3090
3091 end if ;
3092
3093 -- hr_utility.trace(' Out: ' || l_procedure_name) ;
3094 return l_annual_accrual ;
3095
3096 end accrual_annual_rate ;
3097
3098 -- local function to get asg working hours from PL/SQL table
3099
3100 function asg_working_hours(p_effective_date date
3101 ,p_frequency varchar2) return number is
3102
3103 l_procedure_name varchar2(61) := 'asg_working_hours' ;
3104 l_asg_working_hours per_all_assignments_f.normal_hours%type ;
3105 l_counter integer := 1 ;
3106 l_hours_notfound_flag boolean := true ;
3107
3108 begin
3109
3110 -- hr_utility.trace(' In: ' || l_procedure_name) ;
3111
3112 -- loop through the PL/SQL table looking for a likely accrual band
3113 while l_asg_work_day_info_cache.count > 0
3114 and l_hours_notfound_flag
3115 and l_counter <= l_asg_work_day_info_cache.last
3116 loop
3117
3118 if p_effective_date between l_asg_work_day_info_cache(l_counter).effective_start_date
3119 and l_asg_work_day_info_cache(l_counter).effective_end_date
3120 and l_asg_work_day_info_cache(l_counter).frequency = p_frequency
3121 then
3122
3123 l_asg_working_hours := l_asg_work_day_info_cache(l_counter).normal_hours ;
3124 l_hours_notfound_flag := false ;
3125
3126 end if ;
3127
3128 l_counter := l_counter + 1 ;
3129
3130 end loop ;
3131
3132 -- raise error if no working hours found
3133 if l_hours_notfound_flag
3134 then
3135
3136 raise e_accrual_function_failure ;
3137
3138 end if ;
3139
3140 -- hr_utility.trace(' Out: ' || l_procedure_name) ;
3141 return l_asg_working_hours ;
3142
3143 end asg_working_hours ;
3144
3145 begin
3146 hr_utility.trace('fun_enetered_ven');
3147 hr_utility.trace('In: ' || l_procedure_name) ;
3151 hr_utility.trace(' p_calculation_start_date: ' || to_char(p_calculation_start_date, 'dd-Mon-yyyy')) ;
3148 hr_utility.trace(' p_payroll_id: ' || to_char(p_payroll_id)) ;
3149 hr_utility.trace(' p_accrual_plan_id: ' || to_char(p_accrual_plan_id)) ;
3150 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
3152 hr_utility.trace(' p_calculation_end_date: ' || to_char(p_calculation_end_date, 'dd-Mon-yyyy')) ;
3153 hr_utility.trace(' p_service_start_date: ' || to_char(p_service_start_date, 'dd-Mon-yyyy')) ;
3154 hr_utility.trace(' p_anniversary_date: ' || to_char(p_anniversary_date, 'dd-Mon-yyyy')) ;
3155 hr_utility.trace(' p_business_group_hours: ' || to_char(p_business_group_hours)) ;
3156 hr_utility.trace(' p_business_group_freq: ' || p_business_group_freq) ;
3157
3158 -- cache the assignment's work day history
3159 l_counter := 1 ;
3160 l_check_flag := false;
3161
3162 for r_asg_work_day in c_asg_work_day_history(p_assignment_id
3163 ,p_calculation_start_date
3164 ,p_calculation_end_date)
3165 loop
3166
3167 l_asg_work_day_info_cache(l_counter).effective_start_date := r_asg_work_day.effective_start_date ;
3168 l_asg_work_day_info_cache(l_counter).effective_end_date := r_asg_work_day.effective_end_date ;
3169 l_asg_work_day_info_cache(l_counter).normal_hours := r_asg_work_day.normal_hours ;
3170 l_asg_work_day_info_cache(l_counter).frequency := r_asg_work_day.frequency ;
3171
3172 l_counter := l_counter + 1 ;
3173
3174 end loop ; -- c_asg_work_day_history
3175 -- cache the accrual bands
3176 l_counter := 1 ;
3177
3178 for r_accrual_band in c_accrual_bands(p_accrual_plan_id)
3179 loop
3180
3181 l_accrual_band_cache(l_counter).lower_limit := r_accrual_band.lower_limit ;
3182 l_accrual_band_cache(l_counter).upper_limit := r_accrual_band.upper_limit ;
3183 l_accrual_band_cache(l_counter).annual_rate := r_accrual_band.annual_rate ;
3184
3185 l_counter := l_counter + 1 ;
3186
3187 end loop ; -- c_accrual_bands
3188
3189 -- get the number of periods per year
3190 open c_number_of_periods_per_year(p_payroll_id, p_calculation_start_date) ;
3191 fetch c_number_of_periods_per_year
3192 into l_pay_periods_per_year ;
3193 close c_number_of_periods_per_year ;
3194
3195 -- loop through the payroll periods
3196 for r_period in c_periods(p_payroll_id
3197 ,p_calculation_start_date
3198 ,p_calculation_end_date)
3199 loop
3200
3201 -- how many years of service does the assignment have (as at the end of the period)
3202 hr_utility.trace('---------------------------------------------------------------------');
3203
3204 -- Bug# 2072748 -- added the following code
3205 -- calculation of accruals is upto the calculation end date, hence for each
3206 -- period calculation start date and calculation end date are compared with
3207 -- the period start date and period end date
3208 if p_calculation_start_date between r_period.start_date and r_period.end_date
3209 then
3210 l_start_date := p_calculation_start_date;
3211 else
3212 l_start_date := r_period.start_date;
3213 end if;
3214
3215 if p_calculation_end_date between r_period.start_date and r_period.end_date
3216 then
3217 l_end_date := p_calculation_end_date;
3218 else
3219 l_end_date := r_period.end_date;
3220 end if;
3221
3222 l_asg_working_hours := asg_working_hours(least(r_period.end_date
3223 ,p_calculation_end_date)
3224 ,p_business_group_freq) ;
3225
3226 -- if calcualtion end date is less then the period end date
3227
3228 l_least_date := least(r_period.end_date,p_calculation_end_date);
3229 l_days_in_year := 365;
3230
3231 -- if continous service date falls in the pay period then accrual band should
3232 -- be incremented on that date, if continous service date is not present then
3233 -- service start date is used
3234 l_calc_service_date := to_date(to_char(l_start_date,'YYYY') || to_char(p_service_start_date,'MMDD'),'YYYYMMDD');
3235
3236 if l_calc_service_date between l_start_date and l_end_date
3237 then
3238 if l_calc_service_date=p_service_start_date
3239 then l_years_service:=0;
3240 else
3241 -- accrual before the l_next_service_date
3242 l_years_service := floor(months_between(l_calc_service_date-1,p_service_start_date)/12);
3243 end if;
3244 l_annual_accrual_1 := accrual_annual_rate(l_years_service);
3245 l_days_in_part_period := ((l_calc_service_date)-l_start_date);
3246
3247 -- Bug# 2033033 added the logic to handle assigment working hours change
3248 --* If assignment working hours change then take the part period upto the change as one
3249 --* period and the remaining as another period and calculate accruals seperately with
3250 --* different assignment working hours
3251 --* The same logic is repeated when continious service date happens between the calculation
3252 --* period and accrual band is incremented.
3253
3254 if (l_asg_work_day_info_cache.count>1)
3255 then
3256 for l_counter_2 in 1..l_asg_work_day_info_cache.count
3257 loop
3258 if (l_asg_work_day_info_cache(l_counter_2).effective_end_date between r_period.start_date and l_calc_service_date-1)
3259 then
3260 l_asg_working_hours_1 := asg_working_hours(l_asg_work_day_info_cache(l_counter_2).effective_end_date,p_business_group_freq);
3261
3262 l_asg_working_hours_2 := asg_working_hours(l_calc_service_date-1,p_business_group_freq);
3263
3264 l_days_in_part_period_1 := l_asg_work_day_info_cache(l_counter_2).effective_end_date - r_period.start_date+1;
3265
3266 -- Bug# 2127114
3267 --** This code subtracts the extra day of the leap year
3268
3269 l_days_in_part_period_1 := l_days_in_part_period_1 - get_leap_year_mon(r_period.start_date, l_asg_work_day_info_cache(l_counter_2).effective_end_date);
3270
3271 l_days_in_part_period_2 := l_days_in_part_period - l_days_in_part_period_1;
3272
3273 l_days_in_part_period_2 := l_days_in_part_period_2 - get_leap_year_mon( l_asg_work_day_info_cache(l_counter_2).effective_end_date+1,l_calc_service_date);
3274
3275 l_period_accrual:= l_annual_accrual_1 * ((l_asg_working_hours_1*l_days_in_part_period_1) + (l_asg_working_hours_2*l_days_in_part_period_2))/(l_days_in_year * p_business_group_hours);
3276
3277 l_check_flag:=false;
3278 exit;
3279 else
3280 l_check_flag:=true;
3281 end if;
3282 end loop;
3283 else
3284 l_check_flag:=true;
3285 end if;
3286
3287 if l_check_flag
3288 then
3289 l_annual_accrual_1 := l_annual_accrual_1 * (l_asg_working_hours/p_business_group_hours);
3290
3291 l_days_in_part_period := l_days_in_part_period - get_leap_year_mon(l_start_date,l_calc_service_date);
3292 l_period_accrual := (l_annual_accrual_1/l_days_in_year)*l_days_in_part_period;
3293 end if;
3294
3295
3296
3297 l_years_service := floor(months_between(l_end_date,p_service_start_date)/12);
3298
3299 l_annual_accrual_2 := accrual_annual_rate(l_years_service);
3300
3301 l_days_in_part_period := (l_end_date-l_calc_service_date)+1;
3302
3303 if (l_asg_work_day_info_cache.count>1)
3304 then
3305 for l_counter_3 in 1..l_asg_work_day_info_cache.count
3306 loop
3307 if (l_asg_work_day_info_cache(l_counter_3).effective_end_date between l_calc_service_date and l_least_date)
3308 then
3309 l_asg_working_hours_1 := asg_working_hours(l_asg_work_day_info_cache(l_counter_3).effective_end_date,p_business_group_freq);
3310
3311 l_asg_working_hours_2 := asg_working_hours(l_least_date,p_business_group_freq);
3312
3313 l_days_in_part_period_1 := l_asg_work_day_info_cache(l_counter_3).effective_end_date - l_calc_service_date+1;
3314
3315 l_days_in_part_period_1 := l_days_in_part_period_1 - get_leap_year_mon(l_calc_service_date+1,l_asg_work_day_info_cache(l_counter_3).effective_end_date);
3316
3317
3318 l_days_in_part_period_2 := l_days_in_part_period - l_days_in_part_period_1;
3319
3320 l_days_in_part_period_2 := l_days_in_part_period_2 - get_leap_year_mon(l_asg_work_day_info_cache(l_counter_3).effective_end_date+1,l_end_date);
3321
3322 l_period_accrual := l_period_accrual+(l_annual_accrual_2 * ((l_asg_working_hours_1*l_days_in_part_period_1) + (l_asg_working_hours_2*l_days_in_part_period_2))/(l_days_in_year*p_business_group_hours));
3323
3324 l_check_flag:=false;
3325 exit;
3326 else
3327 l_check_flag:=true;
3328 end if;
3329 end loop;
3330 else
3331 l_check_flag:=true;
3332 end if;
3333 -- accrual after the l_next_service_date
3334 if l_check_flag
3335 then
3336 l_annual_accrual_2 := l_annual_accrual_2 * (l_asg_working_hours/p_business_group_hours);
3337 l_days_in_part_period := l_days_in_part_period - get_leap_year_mon(l_calc_service_date+1,l_end_date);
3338
3339 l_period_accrual := l_period_accrual+(l_annual_accrual_2/l_days_in_year)*l_days_in_part_period;
3340
3341 end if;
3342 -- if the continous service date does not fall between the calculation period
3343 else
3344 l_years_service := floor(months_between(l_end_date, p_service_start_date) / 12) ;
3345
3346 -- get the accrual band
3347 l_annual_accrual := accrual_annual_rate(l_years_service) ;
3348 l_days_in_part_period := (l_end_date-l_start_date)+1;
3349
3350 -- Bug# 2033033 included the logic for assignment working hours change
3351
3352 if (l_asg_work_day_info_cache.count>1)
3353 then
3354 for l_counter_1 in 1..l_asg_work_day_info_cache.count
3355 loop
3356 if (l_asg_work_day_info_cache(l_counter_1).effective_end_date between r_period.start_date and l_least_date)
3357 then
3358 l_asg_working_hours_1 := asg_working_hours(l_asg_work_day_info_cache(l_counter_1).effective_end_date,p_business_group_freq);
3359
3360 l_asg_working_hours_2 := asg_working_hours(l_least_date,p_business_group_freq);
3361
3362 l_days_in_part_period_1 := l_asg_work_day_info_cache(l_counter_1).effective_end_date - r_period.start_date+1;
3363
3364 l_days_in_part_period_1 := l_days_in_part_period_1 - get_leap_year_mon(r_period.start_date,l_asg_work_day_info_cache(l_counter_1).effective_end_date);
3365
3366
3367 l_days_in_part_period_2 := l_days_in_part_period - l_days_in_part_period_1;
3368
3369 l_days_in_part_period_2 := l_days_in_part_period_2 - get_leap_year_mon(l_asg_work_day_info_cache(l_counter_1).effective_end_date+1,l_end_date);
3370
3374 exit;
3371 l_period_accrual := l_annual_accrual * ((l_asg_working_hours_1*l_days_in_part_period_1) + (l_asg_working_hours_2*l_days_in_part_period_2))/ (l_days_in_year * p_business_group_hours);
3372
3373 l_check_flag:= false;
3375 else
3376 l_check_flag:=true;
3377 end if;
3378 end loop;
3379 else
3380 l_check_flag:=true;
3381 end if;
3382
3383 if l_check_flag
3384 then
3385 l_annual_accrual:= l_annual_accrual * (l_asg_working_hours/p_business_group_hours);
3386
3387 l_days_in_part_period := l_days_in_part_period - get_leap_year_mon(l_start_date,l_end_date);
3388
3389 l_period_accrual := (l_annual_accrual/l_days_in_year)*l_days_in_part_period;
3390 end if;
3391 end if;
3392
3393 -- hr_utility.trace('l_annual_accrual = '||to_char(l_annual_accrual));
3394 --
3395 -- the algorithm being used here is:
3396 --
3397 -- days to accrue for period
3398 -- = (annual entitlement / days in current holiday year)
3399 -- * days in period
3400 --
3401
3402 --
3403
3404
3405
3406 l_accrual := l_accrual + l_period_accrual ;
3407 --
3408 end loop ; -- c_periods
3409
3410 hr_utility.trace(' return: ' || to_char(l_accrual)) ;
3411 hr_utility.trace('Out: ' || l_procedure_name) ;
3412 return l_accrual ;
3413
3414 exception
3415 when e_accrual_function_failure
3416 then
3417 hr_utility.trace('Crash Out: ' || l_procedure_name) ;
3418 hr_utility.set_message(801, 'HR_NZ_ACCRUAL_FUNCTION_FAILURE') ;
3419 hr_utility.raise_error ;
3420
3421 end accrual_daily_basis ;
3422
3423
3424 ---------------------------------------------------------------
3425 -- function to calculate average acrual rate (Bug 1422001)
3426 ---------------------------------------------------------------
3427
3428 function average_accrual_rate(
3429 p_assignment_id IN per_all_assignments_f.assignment_id%type
3430 ,p_calculation_date IN date
3431 ,p_anniversary_date IN date
3432 ,p_asg_hours IN number ) return number is
3433
3434 CURSOR get_balance_id
3435 IS
3436 SELECT pbt.balance_type_id
3437 FROM pay_balance_types pbt
3438 WHERE pbt.balance_name = 'Gross Earnings for Holiday Pay'
3439 AND legislation_code = 'NZ'
3440 AND business_group_id IS NULL;
3441
3442
3443 l_gross_earnings NUMBER;
3444 l_avg_rate NUMBER;
3445 l_balance_type_id pay_balance_types.balance_type_id%TYPE;
3446 l_num_of_weeks NUMBER;
3447 l_year_end DATE ;
3448
3449 BEGIN
3450 ---------------------------------------------
3451 -- this function returns the average rate to
3452 -- value accrual hrs . In order to get the avg
3453 -- rate , it uses the year end balance
3454 -- for - 'Gross Earnings for Holiday Pay' and
3455 -- the number of weeks in that year
3456 --
3457 -- used for leave liability process
3458 ---------------------------------------------
3459
3460 l_year_end := to_date(to_char((p_anniversary_date-1),'DD-MM-')||to_char(p_calculation_date,'YYYY'),'DD-MM-YYYY');
3461 OPEN get_balance_id ;
3462
3463 FETCH get_balance_id INTO l_balance_type_id;
3464
3465 IF get_balance_id %NOTFOUND THEN
3466 hr_utility.set_location('balance -Gross Earnings for Holiday Pay- not found ',3);
3467 END IF;
3468
3469 CLOSE get_balance_id ;
3470
3471 l_gross_earnings := hr_nzbal.calc_asg_hol_ytd_date
3472 (p_assignment_id => p_assignment_id
3473 ,p_balance_type_id => l_balance_type_id
3474 ,p_effective_date => l_year_end);
3475 hr_utility.trace('gross earnings :'||l_gross_earnings);
3476 hr_utility.trace('year end date :'||l_year_end);
3477
3478 l_num_of_weeks := hr_nz_holidays.num_weeks_for_avg_earnings
3479 (p_assignment_id
3480 ,add_months(l_year_end + 1, -12)) ;
3481
3482 hr_utility.trace('num of weeks :'||l_num_of_weeks);
3483 l_avg_rate := l_gross_Earnings/l_num_of_weeks/p_asg_hours;
3484 hr_utility.trace('Return :'||l_avg_rate);
3485 RETURN l_avg_rate ;
3486
3487 EXCEPTION
3488 WHEN OTHERS THEN
3489 hr_utility.set_location('Error in function -average_accrual_rate. ',3);
3490 RAISE ;
3491 END average_accrual_rate;
3492
3493 /* Bug 2264070 This function returns the annual leave paid before
3494 retro process is exceuted.The pay value is fetched from run_result_values. */
3495
3496 Function get_act_ann_lev_pay(
3497 p_assignment_id IN number
3498 , p_element_entry_id IN number
3499 , p_assgt_action_id IN number
3500 , p_effective_date IN date)
3501 return NUMBER is
3502
3503 l_ann_lev_pay number;
3504
3505 CURSOR c_act_ann_pay(p_assignment_id number, p_element_entry_id number, p_assgt_action_id number,p_effective_date date) IS
3506 select prv.result_value
3507 from pay_run_result_values prv
3508 , pay_run_results prr
3509 , pay_input_values_f piv
3510 , pay_element_types_f pet
3511 , pay_element_entries_f pee
3512 where pet.element_name = 'Annual Leave Pay'
3513 and pet.legislation_code = 'NZ'
3517 and prr.run_result_id = prv.run_result_id
3514 and pet.element_type_id = piv.element_type_id
3515 and piv.name = 'Pay Value'
3516 and prv.input_value_id = piv.input_value_id
3518 and pee.element_entry_id = p_element_entry_id
3519 AND PRR.RUN_RESULT_ID = PEE.SOURCE_ID
3520 and prr.assignment_action_id = p_assgt_action_id
3521 and p_effective_date between piv.effective_start_date and piv.effective_end_date
3522 and p_effective_date between pet.effective_start_date and pet.effective_end_date
3523 and p_effective_date between pee.effective_start_date and pee.effective_end_date;
3524
3525
3526 begin
3527 open c_act_ann_pay(p_assignment_id,p_element_entry_id,p_assgt_action_id,p_effective_date);
3528 fetch c_act_ann_pay into l_ann_lev_pay;
3529 close c_act_ann_pay;
3530 return l_ann_lev_pay;
3531
3532 end get_act_ann_lev_pay;
3533
3534 /* Bug 2264070 This function returns the number of weeks which is used for
3535 calculation of average rate */
3536
3537 Function num_of_weeks_for_avg_earnings(
3538 p_assignment_id IN number
3539 , p_hol_ann_date IN date)
3540 return NUMBER is
3541
3542 l_num_of_weeks number;
3543 l_pay_period_start_date date;
3544 l_num_of_pay_periods_per_year number;
3545 l_extra_weeks number;
3546
3547 cursor get_pay_period_start_date(p_assignment_id number) is
3548 SELECT TPERIOD.start_date,
3549 TPTYPE.number_per_fiscal_year
3550 FROM pay_payroll_actions PACTION,
3551 per_time_periods TPERIOD,
3552 per_time_period_types TPTYPE
3553 where PACTION.payroll_action_id =
3554 (select max(paa.payroll_action_id)
3555 from pay_assignment_actions paa,
3556 pay_payroll_actions ppa
3557 where paa.assignment_id = p_assignment_id
3558 and ppa.action_type in ('R','Q')
3559 and ppa.payroll_action_id = paa.payroll_action_id)
3560 and PACTION.payroll_id = TPERIOD.payroll_id
3561 and PACTION.date_earned between TPERIOD.start_date and TPERIOD.end_date
3562 and TPTYPE.period_type = TPERIOD.period_type;
3563
3564 BEGIN
3565
3566 open get_pay_period_start_date(p_assignment_id);
3567 fetch get_pay_period_start_date into l_pay_period_start_date,l_num_of_pay_periods_per_year;
3568 close get_pay_period_start_date;
3569
3570 l_num_of_weeks := num_weeks_for_avg_earnings(
3571 p_assignment_id => p_assignment_id
3572 ,p_start_of_year_date => ADD_MONTHS(p_hol_ann_date,-12));
3573
3574 /* If the hol ann date lies in between the pay period then add
3575 number of weeks in one extra pay period to the total number of weeks in a year */
3576 if (to_char(p_hol_ann_date,'dd') <> to_char( l_pay_period_start_date,'dd'))
3577 then
3578 l_extra_weeks := 52/l_num_of_pay_periods_per_year;
3579 l_num_of_weeks := l_num_of_weeks + l_extra_weeks ;
3580
3581 end if;
3582
3583 return l_num_of_weeks;
3584
3585 end num_of_weeks_for_avg_earnings;
3586
3587 /* Bug 2264070 The function returns 1 if action type of the current run is
3588 L (Retro) else 0 */
3589
3590 FUNCTION get_current_action_type(p_payroll_id in number)
3591 RETURN number IS
3592
3593 /* Bug 4259438 : Modified cursor as part of performance */
3594 CURSOR c_get_curr_action_type(p_payroll_id NUMBER)
3595 IS
3596 SELECT action_type
3597 FROM pay_payroll_actions ppa
3598 , pay_payrolls_f ppf
3599 WHERE ppf.payroll_id = p_payroll_id
3600 AND ppa.payroll_id = ppf.payroll_id
3601 AND ppa.business_group_id = ppf.business_group_id
3602 AND (ppa.consolidation_set_id = ppf.consolidation_set_id
3603 OR ppa.consolidation_set_id IS NULL)
3604 AND ppa.action_type LIKE '%'
3605 AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
3606 ORDER BY PAYROLL_ACTION_ID DESC;
3607
3608 -- Bug 2595888: Changed the datatype from varchar2(1) to pay_payroll_actions.action_type%type
3609 l_action_type pay_payroll_actions.action_type%type;
3610
3611 begin
3612
3613 open c_get_curr_action_type(p_payroll_id);
3614 FETCH c_get_curr_action_type INTO l_action_type;
3615 close c_get_curr_action_type;
3616
3617 hr_utility.trace('Action_type= '||l_action_type);
3618 if l_action_type = 'L' then
3619 return 1;
3620 else
3621 return 0;
3622 end if;
3623
3624 end get_current_action_type;
3625
3626 /* Bug 2264070 This function returns the retro period start date ,that is
3627 date earned +1 for the last payroll run executed before the retro process*/
3628
3629 FUNCTION retro_start_date
3630 (p_assignment_id in number)
3631 RETURN date IS
3632
3633 /* Bug No - 2581490 */
3634
3635 -- cursor to give the effective_date of the retro pay process
3636
3637 cursor c_get_values(p_assignment_id number) is
3638 select max(ppa.effective_date)
3639 ,ppa.payroll_id
3640 from pay_payroll_actions ppa
3641 ,pay_assignment_actions pac
3642 where pac.assignment_id = p_assignment_id
3646
3643 and pac.payroll_action_id = ppa.payroll_action_id
3644 and ppa.action_type = 'L'
3645 group by ppa.payroll_id ;
3647 -- cursor to get the period start_date of the retro process pay period
3648
3649 cursor c_retro_start_date(p_effective_date date,
3650 p_payroll_id number) is
3651 select ptp.start_date
3652 from per_time_periods ptp
3653 ,pay_all_payrolls_f pap
3654 where pap.payroll_id = p_payroll_id
3655 and ptp.payroll_id = pap.payroll_id
3656 and (p_effective_date - pap.PAY_DATE_OFFSET) between ptp.start_date and ptp.end_date;
3657
3658 l_retro_start_date date;
3659 l_effective_date date;
3660 l_payroll_id number;
3661
3662 begin
3663 hr_utility.trace('Inside retro_start_date');
3664
3665 open c_get_values(p_assignment_id);
3666 fetch c_get_values into l_effective_date ,l_payroll_id;
3667 close c_get_values;
3668
3669 open c_retro_start_date(l_effective_date,l_payroll_id);
3670 fetch c_retro_start_date into l_retro_start_date ;
3671 close c_retro_start_date ;
3672 hr_utility.trace('Executed_retro_start_cursor value of date'|| to_char(l_retro_start_date,'DD-MON-YYYY'));
3673 return l_retro_start_date ;
3674
3675 end retro_start_date;
3676
3677
3678 /* Bug 2264070 This function returns the gross earnings for the calculation
3679 of Average rate */
3680
3681 FUNCTION gross_earnings_ytd_for_retro
3682 (p_assignment_id in per_all_assignments_f.assignment_id%type
3683 ,p_effective_date in date) RETURN number IS
3684
3685 l_balance_type_id NUMBER;
3686 l_gross_earnings NUMBER;
3687 l_procedure_name varchar2(61) :=
3688 'hr_nz_holidays.gross_earnings_ytd_for_retro';
3689
3690 e_missing_balance_type exception;
3691 CURSOR c_balance_type(p_name varchar2) is
3692 select balance_type_id
3693 from pay_balance_types
3694 where balance_name = p_name;
3695
3696
3697 BEGIN
3698
3699
3700 --** get the balance id
3701 open c_balance_type('Gross Earnings for Holiday Pay');
3702 fetch c_balance_type into l_balance_type_id;
3703 if c_balance_type%notfound
3704 then
3705 close c_balance_type;
3706 raise e_missing_balance_type;
3707 end if;
3708 close c_balance_type;
3709
3710
3711 l_gross_earnings := hr_nzbal.calc_asg_hol_ytd_date (p_assignment_id,l_balance_type_id,p_effective_date);
3712
3713 return l_gross_earnings;
3714
3715 exception
3716 when e_missing_balance_type
3717 then
3718 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3719 hr_utility.set_message_token('PROCEDURE',l_procedure_name);
3720 hr_utility.set_message_token('STEP','Missing Balance Type Exception');
3721 hr_utility.raise_error;
3722
3723 END gross_earnings_ytd_for_retro;
3724
3725
3726 --------------------------------------------------------------------------------------------------
3727 --** Bug 2366349 : Function to get the Adjustments for accrual and entitlement entered through seeded elements
3728 --------------------------------------------------------------------------------------------------
3729 function get_adjustment_values
3730 (p_assignment_id in NUMBER
3731 ,p_accrual_plan_id in NUMBER
3732 ,p_calc_end_date in DATE
3733 ,p_adjustment_element in VARCHAR2
3734 ,p_start_date in DATE
3735 ,p_end_date in DATE)
3736 return number is
3737
3738 l_proc varchar2(61) := 'hr_nz_holidays.get_adjustment_values' ;
3739 l_adjustment number := 0;
3740
3741 -- find Leave Initialise Values
3742
3743 cursor c_get_adjustments( v_assignment_id number
3744 ,v_accrual_plan_id number
3745 ,v_calc_end_date date
3746 ,v_adjustment_element varchar2
3747 ,v_start_date date
3748 ,v_end_date date) is
3749 select
3750 sum(nvl(to_number(pev1.screen_entry_value),0))
3751 from
3752 pay_accrual_plans pap
3753 ,pay_element_types_f pet
3754 ,pay_element_links_f pel
3755 ,pay_input_values_f piv1
3756 ,pay_input_values_f piv2
3757 ,pay_element_entries_f pee
3758 ,pay_element_entry_values_f pev1
3759 ,pay_element_entry_values_f pev2
3760 where
3761 pee.assignment_id = v_assignment_id
3762 and pet.element_name = v_adjustment_element
3763 and pet.element_type_id = pel.element_type_id
3764 and pel.element_link_id = pee.element_link_id
3765 and pee.element_entry_id = pev1.element_entry_id
3766 and pev1.input_value_id = piv1.input_value_id
3767 and piv1.name = 'Hours'
3768 and piv1.element_type_id = pet.element_type_id
3769 and pee.element_entry_id = pev2.element_entry_id
3770 and pev2.input_value_id = piv2.input_value_id
3771 and piv2.name = 'Accrual Plan'
3772 and piv2.element_type_id = pet.element_type_id
3773 and pev2.screen_entry_value = pap.accrual_plan_name
3774 and pap.accrual_plan_id = v_accrual_plan_id
3775 /* Start date of adjustment entry must be before end of accrual */
3776 and pee.effective_start_date <= v_calc_end_date
3777 /* End date of adjustment entry must be after start of accrual */
3778 and pee.effective_end_date >= v_start_date
3779 and pee.effective_start_date between pet.effective_start_date and pet.effective_end_date
3780 and pee.effective_start_date between pel.effective_start_date and pel.effective_end_date
3781 and pee.effective_start_date between piv1.effective_start_date and piv1.effective_end_date
3782 and pee.effective_start_date between pev1.effective_start_date and pev1.effective_end_date
3783 and pee.effective_start_date between piv2.effective_start_date and piv2.effective_end_date
3784 and pee.effective_start_date between pev2.effective_start_date and pev2.effective_end_date;
3785
3786 begin
3787
3788 hr_utility.set_location(' In: ' || l_proc,5) ;
3789 hr_utility.trace('p_adjustment_element = '||p_adjustment_element);
3790 hr_utility.trace('p_calc_end_date = '||to_char(p_calc_end_date,'dd/mm/rrrr'));
3791 hr_utility.trace('p_start_date = '||to_char(p_start_date,'dd/mm/rrrr'));
3792 hr_utility.trace('p_end_date = '||to_char(p_end_date,'dd/mm/rrrr'));
3793 -- find total leave initialise - should return zero if none entered
3794
3795 open c_get_adjustments(p_assignment_id
3796 ,p_accrual_plan_id
3797 ,p_calc_end_date
3798 ,p_adjustment_element
3799 ,p_start_date
3800 ,p_end_date);
3801 fetch c_get_adjustments into l_adjustment;
3802 close c_get_adjustments;
3803
3804 hr_utility.trace('Adjustment: '||to_char(l_adjustment));
3805 hr_utility.set_location(' Out: ' || l_proc,10) ;
3806
3807 return(nvl(l_adjustment,0));
3808
3809 end get_adjustment_values;
3810
3811 /* Bug 2264070. Following function is called from the formula Annual Leave EOY
3812 Adjustment skip. It checks whether the element entry of EOY Adjustment element
3813 is due to Retro process or not.
3814 If it is due to Retro then it returns 1 else 0 */
3815
3816 function check_retro_eoy(p_element_entry_id in number)
3817 Return number is
3818
3819 l_retro varchar2(10);
3820
3821 cursor c_check_retro(p_element_entry_id in number)
3822 is
3823 select 'EXISTS'
3824 from pay_element_entries_f pee
3825 where pee.element_entry_id = p_element_entry_id
3826 and pee.creator_type = 'RR';
3827
3828 begin
3829
3830 open c_check_retro(p_element_entry_id);
3831 fetch c_check_retro into l_retro;
3832 if c_check_retro%notfound then
3833 close c_check_retro;
3834 return 0;
3835 end if;
3836 close c_check_retro;
3837 if l_retro = 'EXISTS' then
3838 return 1;
3839 end if;
3840
3841 end check_retro_eoy;
3842
3843 END hr_nz_holidays;
[Home] [Help]