1 PACKAGE BODY hr_au_holidays AS
2 -- $Header: hrauhol.pkb 120.3 2007/10/03 06:48:41 priupadh ship $
3 --
4 -- Copyright (C) 2000 Oracle Corporation
5 -- All Rights Reserved
6 --
7 -- Script to create AU HRMS hr_au_holidays package
8 --
9 -- Change List
10 -- ===========
11 --
12 -- Date Author Ver Description
13 -- -----------+--------+-------+-----------------------------------------------
14 -- 01-Oct-2007 priupadh 115.22 Bug 6449311 function get_accrual_entitlement added Step 4a
15 -- 04-Apr-2007 priupadh 115.21 Bug 5964317 removed cursor c_asg_periods modified c_periods
16 -- 02-Apr-2007 priupadh 115.20 Bug 5964317 Added cursor c_asg_periods and loop a_periods
17 -- 29 May 2003 apunekar 115.9 Bug2920725 - Corrected base tables to support security model
18 -- 02 Dec 2002 Apunekar 115.18 Bug#2689173-Added Nocopy to out and in out parameters
19 -- 20-MAR-2001 apunekar 115.17 Validated anniversary date for 29th feb input,Bug#2272301
20 -- 10-DEC-2001 srussell 115.16 Put in checkfile syntax.
21 -- 07-DEC-2001 srussell 115.15 Allow get_accrual_entitlement to return a
22 -- negative amount for net_accrual.
23 -- 28-NOV-2001 nnaresh 115.12 Updated for GSCC Standards
24 -- 26-SEP-2001 shoskatt 115.11 Used the get_leave_initialise to get the accrual
25 -- initialise at the entitlement end date. This is
26 -- used to calculate the net entitlements
27 -- 12-SEP-2001 shoskatt 115.10 Used the get_leave_initialise function to get
28 -- the Leave Entitlement Initialise and Leave Accrual
29 -- Initialise value. This is used to calculate the
30 -- Net Entitlement as well as Net Accrual. Bug #1942971
31 -- 16-OCT-2000 rayyadev 115.9 change the code to consider multiple bands with
32 -- different annual rate bug no 1460922
33 -- 25-Jan-2000 sclarke 115.8 Moved term_lsl_eligibility_years to pay_au_terminations
34 -- 29-May-2000 makelly 115.7 Re-added get_net_accrual wrapper
35 -- 26-May-2000 makelly 115.6 Bug 1313971 anniversary date counted twice in
36 -- accrual_daily_basis. (removed exceptions)
37 -- 16-May-2000 makelly 115.5 Bug 1300935 Altered accrual_entitlement to check for
38 -- start date and change to entitlement adjustments
39 -- 03-May-2000 makelly 115.4 Bug 1273677 and added accrual_entitlement fn
40 -- to simplify calls from accrual/absence forms
41 -- 21-Mar-2000 makelly 115.3 fixed bug in call to asg_working_hours
42 -- 15-Mar-2000 sclarke 115.2 Added LSL function
43 -- 21-Jan-2000 makelly 115.1 Initial - Based on hrnzhol.pkb
44 -----------------------------------------------------------------------------------
45 -- private global declarations
46 -----------------------------------------------------------------------------------
47
48 -- Define a record and PL/SQL table to hold accrual band information.
49 -- Used by accrual_period_basis and ann_leave_accrual_daily_basis
50 -- functions.
51
52 type t_accrual_band_rec is record
53 (lower_limit pay_accrual_bands.lower_limit%type
54 ,upper_limit pay_accrual_bands.upper_limit%type
55 ,annual_rate pay_accrual_bands.annual_rate%type) ;
56
57 type t_accrual_band_tab
58 is table of t_accrual_band_rec
59 index by binary_integer ;
60
61 -- Define a record and PL/SQL table to hold assignment work day data.
62 -- Used by accrual_period_basis and ann_leave_accrual_daily_basis
63 -- functions.
64
65 type t_asg_work_day_info_rec is record
66 (effective_start_date per_all_assignments_f.effective_start_date%type
67 ,effective_end_date per_all_assignments_f.effective_end_date%type
68 ,normal_hours per_all_assignments_f.normal_hours%type
69 ,frequency per_all_assignments_f.frequency%type) ;
70
71 type t_asg_work_day_info_tab
72 is table of t_asg_work_day_info_rec
73 index by binary_integer ;
74
75 /*---------------------------------------------------------------------
76 Name : get_accrual_plan_by_category
77 Purpose : To retrieve accrual plan id for designated category
78 Returns : accrual_plan_id if successful, null otherwise
79 ---------------------------------------------------------------------*/
80
81 FUNCTION get_accrual_plan_by_category
82 (p_assignment_id IN NUMBER
83 ,p_effective_date IN DATE
84 ,p_plan_category IN VARCHAR2) RETURN NUMBER IS
85
86 l_proc VARCHAR2(72) := g_package||'get_accrual_plan_by_category' ;
87 l_accrual_plan_id NUMBER ;
88 l_dummy NUMBER ;
89
90 CURSOR csr_get_accrual_plan_id(p_assignment_id NUMBER
91 ,p_effective_date DATE
92 ,p_plan_category VARCHAR2) IS
93 SELECT pap.accrual_plan_id
94 FROM pay_accrual_plans pap,
95 pay_element_entries_f pee,
96 pay_element_links_f pel,
97 pay_element_types_f pet
98 WHERE pee.assignment_id = p_assignment_id
99 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
100 AND pel.element_link_id = pee.element_link_id
101 AND pel.element_type_id = pet.element_type_id
102 AND pap.accrual_plan_element_type_id = pet.element_type_id
103 AND pap.accrual_category = p_plan_category ;
104
105 BEGIN
106 hr_utility.set_location(' Entering::'||l_proc,5);
107
108 OPEN csr_get_accrual_plan_id(p_assignment_id, p_effective_date, p_plan_category) ;
109
110 FETCH csr_get_accrual_plan_id INTO l_accrual_plan_id;
111
112 IF csr_get_accrual_plan_id%NOTFOUND
113 THEN
114 CLOSE csr_get_accrual_plan_id;
115 hr_utility.set_location('Plan Not Found '||l_proc,10);
116 hr_utility.set_message(801, 'HR_AU_ACCRUAL_PLAN_NOT_FOUND');
117 hr_utility.raise_error;
118 end if ;
119
120 FETCH csr_get_accrual_plan_id INTO l_dummy ;
121
122 IF csr_get_accrual_plan_id%FOUND
123 THEN
124 CLOSE csr_get_accrual_plan_id;
125 hr_utility.set_location('Enrolled in Multiple Plans '||l_proc,15);
126 hr_utility.set_message(801, 'HR_AU_TOO_MANY_ACCRUAL_PLANS');
127 hr_utility.raise_error;
128 END IF;
129
130 CLOSE csr_get_accrual_plan_id;
131 hr_utility.set_location('Leaving:'||l_proc,20);
132
133 RETURN l_accrual_plan_id;
134
135 -- EXCEPTION
136 -- WHEN OTHERS THEN
137 -- hr_utility.set_location('Leaving:'||l_proc,99);
138 -- RETURN NULL;
139 END get_accrual_plan_by_category;
140
141
142 --
143 -- get_net_accrual
144 --
145 -- This function is a wrapper for the
146 -- per_accrual_calc_functions.get_net_accrual procedure. The
147 -- wrapper is required so that a FastFormula function can be
148 -- registered for use in formulas.
149 --
150
151 FUNCTION get_net_accrual
152 (p_assignment_id IN NUMBER
153 ,p_payroll_id IN NUMBER
154 ,p_business_group_id IN NUMBER
155 ,p_plan_id IN NUMBER
156 ,p_calculation_date IN DATE)
157 RETURN NUMBER IS
158
159 l_proc VARCHAR2(72) := g_package||'get_net_accrual';
160 l_assignment_id NUMBER ;
161 l_plan_id NUMBER ;
162 l_payroll_id NUMBER ;
163 l_business_group_id NUMBER ;
164 l_calculation_date DATE ;
165 l_start_date DATE ;
166 l_end_date DATE ;
167 l_accrual_end_date DATE ;
168 l_accrual NUMBER ;
169 l_net_entitlement NUMBER ;
170
171 --------------------------------------
172 -- Bug No : 2132299 Start
173 --------------------------------------
174
175 l_initialise_type VARCHAR2(100);
176 l_accrual_init NUMBER ;
177 l_entitlement_init NUMBER;
178
179 --------------------------------------
180 -- Bug No : 2132299 End
181 --------------------------------------
182
183 BEGIN
184 hr_utility.set_location('Entering: '||l_proc,10) ;
185 l_assignment_id := p_assignment_id ;
186 l_plan_id := p_plan_id ;
187 l_payroll_id := p_payroll_id ;
188 l_business_group_id := p_business_group_id ;
189 l_calculation_date := p_calculation_date ;
190 l_start_date := NULL ;
191 l_end_date := NULL ;
192 l_accrual_end_date := NULL ;
193 l_accrual := NULL ;
194 l_net_entitlement := NULL ;
195 per_accrual_calc_functions.get_net_accrual(
196 p_assignment_id => l_assignment_id
197 ,p_plan_id => l_plan_id
198 ,p_payroll_id => l_payroll_id
199 ,p_business_group_id => l_business_group_id
200 ,p_calculation_date => l_calculation_date
201 ,p_start_date => l_start_date
202 ,p_end_date => l_end_date
203 ,p_accrual_end_date => l_accrual_end_date
204 ,p_accrual => l_accrual
205 ,p_net_entitlement => l_net_entitlement) ;
206
207 --------------------------------------
208 -- Bug No : 2132299 Start
209 --------------------------------------
210
211 l_initialise_type := 'Leave Accrual Initialise';
212 l_accrual_init := (get_leave_initialise(
213 p_assignment_id => l_assignment_id
214 ,p_accrual_plan_id => l_plan_id
215 ,p_calc_end_date => l_calculation_date
216 ,p_initialise_type => l_initialise_type
217 ,p_start_date => l_start_date
218 ,p_end_date => l_end_date)
219 );
220
221 l_initialise_type := 'Leave Entitlement Initialise';
222 l_entitlement_init := (get_leave_initialise(
223 p_assignment_id => l_assignment_id
224 ,p_accrual_plan_id => l_plan_id
225 ,p_calc_end_date => l_calculation_date
226 ,p_initialise_type => l_initialise_type
227 ,p_start_date => l_start_date
228 ,p_end_date => l_end_date)
229 );
230
231
232
233 l_net_entitlement := l_net_entitlement + l_entitlement_init + l_accrual_init;
234
235 --------------------------------------
236 -- Bug No : 2132299 End
237 --------------------------------------
238
239 hr_utility.set_location('Leaving '||l_proc,20);
240 RETURN l_net_entitlement ;
241
242 END get_net_accrual ;
243
244
245
246 --------------------------------------------------------------
247 --
248 -- get_accrual_entitlement
249 --
250 -- This function is required mainly by the AU local library
251 -- and will return the net accrual and net entitlement for a
252 -- given person on a given day.
253 --
254 -- These values will be displayed in the forms PAYWSACV and
255 -- PAYWSEAD.
256 --
257 --------------------------------------------------------------
258
259 FUNCTION get_accrual_entitlement
260 (p_assignment_id IN NUMBER
261 ,p_payroll_id IN NUMBER
262 ,p_business_group_id IN NUMBER
263 ,p_plan_id IN NUMBER
264 ,p_calculation_date IN DATE
265 ,p_net_accrual OUT NOCOPY NUMBER
266 ,p_net_entitlement OUT NOCOPY NUMBER
267 ,p_calc_start_date OUT NOCOPY DATE
268 ,p_last_accrual OUT NOCOPY DATE
269 ,p_next_period_end OUT NOCOPY DATE)
270 RETURN NUMBER IS
271
272
273 -- The stages of the calculation are as follows
274 --
275 -- 1: Find the entitlement end date using the get_carryover_values
276 -- core function - ie the last day of the entitlement period
277 --
278 -- 2: Find net leave at entitlement end date using the core
279 -- get_net_accrual Function.
280 --
281 -- 3: Find the total net leave up to the calculation date using
282 -- the core get_net_accrual function.
283 --
284 -- 4: Find the number of hours taken during the accrual period
285 -- i.e. date from step 1 plus 1 day until calc date using the
286 -- core get_absence function
287 --
288 -- Added Step 4a for Bug 6449311
289 -- 4a Find the Net Contribution of other elements using
290 -- per_accrual_calc_functions.get_other_net_contribution
291 --
292 -- 5: Find Leave Accrual Initialise during period
293 --
294 -- 6: Find Leave Entitlement Initialise during period
295 --
296 -- 7: Net entitlement = greater ((step 2 - step 4 + step 6 + step 4.1), 0)
297 --
298 -- 8: Net accrual = (step 3 + step 5 - step 7 + step 6)
299 --
300
301
302 l_proc VARCHAR2(72) := g_package||'.get_accrual_entitlement';
303 l_assignment_id NUMBER ;
304 l_plan_id NUMBER ;
305 l_payroll_id NUMBER ;
306 l_business_group_id NUMBER ;
307 l_calculation_date DATE ;
308 l_start_date DATE ;
309 l_end_date DATE ;
310 l_accrual_end_date DATE ;
311 l_accrual_period_start_date DATE ;
312 l_accrual_period_end_date DATE ;
313 l_entitlement_period_end_date DATE ;
314 l_net_accrual NUMBER ;
315 l_net_entitlement NUMBER ;
319 l_leave_calc_date NUMBER ;
316 l_co_formula_id NUMBER ;
317 l_max_co NUMBER ;
318 l_leave_end_ent NUMBER ;
320 l_accrual NUMBER ;
321 l_accrual_absences NUMBER ;
322 l_other NUMBER ;
323 l_total_ent_adj NUMBER ;
324 ---------------------------------------------
325 -- Bug #1942971 -- Start
326 ---------------------------------------------
327 l_initialise_type VARCHAR2(100);
328 l_accrual_init NUMBER ;
329 l_accrual_ent NUMBER ;
330 l_entitlement_init NUMBER ;
331 ---------------------------------------------
332 -- Bug #1942971 -- End
333 ---------------------------------------------
334
335 cursor c_get_co_formula (v_accrual_plan_id number) is
336 select co_formula_id
337 from pay_accrual_plans
338 where accrual_plan_id = v_accrual_plan_id;
339
340
341
342 BEGIN
343
344 hr_utility.set_location('Entering: '||l_proc,10) ;
345 l_assignment_id := p_assignment_id ;
346 l_plan_id := p_plan_id ;
347 l_payroll_id := p_payroll_id ;
348 l_business_group_id := p_business_group_id ;
349 l_calculation_date := p_calculation_date ;
350
351
352 --
353 -- Step 1 Find entitlement end date
354 -- first get the carryover formula then call it
355 -- to get the prev and next anniversary dates.
356 -- Entitlement end date and accrual end dates are
357 -- actually the day before the anniversary dates.
358 --
359
360 open c_get_co_formula (l_plan_id);
361 fetch c_get_co_formula into l_co_formula_id;
362 close c_get_co_formula;
363
364
365 per_accrual_calc_functions.get_carry_over_values(
366 p_co_formula_id => l_co_formula_id
367 ,p_assignment_id => l_assignment_id
368 ,p_calculation_date => l_calculation_date
369 ,p_accrual_plan_id => l_plan_id
370 ,p_business_group_id => l_business_group_id
371 ,p_payroll_id => l_payroll_id
372 ,p_accrual_term => 'AU_FORM'
373 ,p_effective_date => l_accrual_period_start_date
374 ,p_session_date => l_calculation_date
375 ,p_max_carry_over => l_max_co
376 ,p_expiry_date => l_accrual_period_end_date );
377
378
379
380 --
381 -- Step two find the Net leave at entitlement end date
382 --
383 -- Before first anniversary date accrual_period_start_date = start_date
384 -- in this case l_max_co will be set to 1
385 --
386
387 if l_max_co = 1 then
388 l_entitlement_period_end_date := l_accrual_period_start_date;
389 else
390 l_entitlement_period_end_date := (l_accrual_period_start_date - 1);
391 end if;
392
393 per_accrual_calc_functions.get_net_accrual(
394 p_assignment_id => l_assignment_id
395 ,p_plan_id => l_plan_id
396 ,p_payroll_id => l_payroll_id
397 ,p_business_group_id => l_business_group_id
398 ,p_calculation_date => l_entitlement_period_end_date
399 ,p_start_date => l_start_date
400 ,p_end_date => l_end_date
401 ,p_accrual_end_date => l_accrual_end_date
402 ,p_accrual => l_accrual
403 ,p_net_entitlement => l_leave_end_ent) ; -- at end of entitlement perod
404
405
406 --
407 -- Step three find the Net leave at the calculation_date
408 --
409
410 per_accrual_calc_functions.get_net_accrual(
411 p_assignment_id => l_assignment_id
412 ,p_plan_id => l_plan_id
413 ,p_payroll_id => l_payroll_id
414 ,p_business_group_id => l_business_group_id
415 ,p_calculation_date => l_calculation_date
416 ,p_start_date => l_start_date
417 ,p_end_date => l_end_date
418 ,p_accrual_end_date => l_accrual_end_date
419 ,p_accrual => l_accrual
420 ,p_net_entitlement => l_leave_calc_date) ; -- at calculation date
421
422
423
424 --
425 -- Step four find out the numder of hours taken during the accrual period
426 --
427
428 l_accrual_absences := per_accrual_calc_functions.get_absence(
429 p_assignment_id => l_assignment_id,
430 p_plan_id => l_plan_id,
431 p_start_date => l_accrual_period_start_date,
432 p_calculation_date => l_calculation_date );
433
434 /*Bug 6449311 Begin */
435 --
436 -- Step 4a find out the contribution from other elements
437 --
438
439 l_other := per_accrual_calc_functions.get_other_net_contribution(
440 p_assignment_id => l_assignment_id,
444
441 p_plan_id => l_plan_id,
442 p_start_date => l_accrual_period_start_date,
443 p_calculation_date => l_calculation_date );
445 /*Bug 6449311 End */
446
447 -------------------------------------------------------------------------------------------
448 --- Bug #1942971 ----- Start
449 -------------------------------------------------------------------------------------------
450 --
451 -- Step 5 : Find the Leave Accrual Initialise for the period(5a). Also get leave accrual initialise
452 -- at the end of entitlement date(5b).
453 --
454 l_initialise_type := 'Leave Accrual Initialise';
455 l_accrual_init := (get_leave_initialise(
456 p_assignment_id => l_assignment_id
457 ,p_accrual_plan_id => l_plan_id
458 ,p_calc_end_date => l_calculation_date
459 ,p_initialise_type => l_initialise_type
460 ,p_start_date => l_start_date
461 ,p_end_date => l_end_date)
462 );
463
464 l_accrual_ent := (get_leave_initialise(
465 p_assignment_id => l_assignment_id
466 ,p_accrual_plan_id => l_plan_id
467 ,p_calc_end_date => l_calculation_date
468 ,p_initialise_type => l_initialise_type
469 ,p_start_date => l_start_date
470 ,p_end_date => l_entitlement_period_end_date - 1)
471 );
472
473 --
474 -- Step 6 : Find the Leave Entitlement Initialise for the period.
475 --
476 l_initialise_type := 'Leave Entitlement Initialise';
477 l_entitlement_init := (get_leave_initialise(
478 p_assignment_id => l_assignment_id
479 ,p_accrual_plan_id => l_plan_id
480 ,p_calc_end_date => l_calculation_date
481 ,p_initialise_type => l_initialise_type
482 ,p_start_date => l_start_date
483 ,p_end_date => l_end_date)
484 );
485
486
487 /*Bug 6449311 l_other (Step 4a) added for calculating l_net_entitlement */
488 --
489 -- Step 7: Net entitlement = greater ((step 2 - step 4 + step 6 + Step 5b +Step 4a), 0)
490 --
491
492 l_net_entitlement := greatest( (l_leave_end_ent - l_accrual_absences + l_entitlement_init + l_accrual_ent + l_other ) , 0);
493
494 --
495 -- Step 8: Net accrual = greater((step 3 + step 5 - step 7 + step 6),0)
496 --
497 --l_net_accrual := greatest((l_leave_calc_date + l_accrual_init - l_net_entitlement + l_entitlement_init),0);
498 l_net_accrual := (l_leave_calc_date + l_accrual_init - l_net_entitlement + l_entitlement_init);
499
500
501 --
502 -- set up return values
503 --
504
505 p_net_accrual := round(nvl(l_net_accrual, 0), 3);
506 p_net_entitlement := round(nvl(l_net_entitlement, 0), 3);
507 p_calc_start_date := l_start_date;
508 p_last_accrual := l_accrual_end_date;
509 p_next_period_end := l_accrual_period_end_date - 1;
510
511 hr_utility.set_location('Leaving '||l_proc,20);
512 RETURN (0);
513
514 -- EXCEPTION
515 -- WHEN OTHERS
516 -- THEN
517 -- hr_utility.set_location('Leaving:'||l_proc,99);
518 -- RETURN -99;
519
520 END get_accrual_entitlement ;
521
522
523
524 /*---------------------------------------------------------------------
525 Name : get_annual_leave_plan
526 Purpose : To get the Annual Leave Plan for an Assignment
527 Returns : PLAN_ID if successful, NULL otherwise
528 ---------------------------------------------------------------------*/
529
530 FUNCTION get_annual_leave_plan
531 (p_assignment_id IN NUMBER
532 ,p_business_group_id IN NUMBER
533 ,p_calculation_date IN DATE)
534 RETURN NUMBER IS
535
536 l_proc VARCHAR2(72) := g_package||'get_annual_leave_plan';
537 l_plan_id NUMBER;
538
539 CURSOR csr_annual_leave_accrual_plan(c_business_group_id IN NUMBER
540 ,c_calculation_date IN DATE
541 ,c_assignment_id IN NUMBER) IS
542 SELECT pap.accrual_plan_id
543 FROM pay_accrual_plans pap,
544 pay_element_entries_f pee,
545 pay_element_links_f pel,
546 pay_element_types_f pet
547 WHERE pel.element_link_id = pee.element_link_id
548 AND pel.element_type_id = pet.element_type_id
549 AND pee.assignment_id = c_assignment_id
550 AND pet.element_type_id = pap.accrual_plan_element_type_id
551 AND pap.business_group_id = c_business_group_id
552 AND c_calculation_date BETWEEN pee.effective_start_date AND pee.effective_end_date
553 AND pap.accrual_category = (
554 SELECT lookup_code
555 FROM hr_lookups
559 BEGIN
556 WHERE lookup_type = 'ABSENCE_CATEGORY'
557 AND meaning = 'Annual Leave');
558
560 hr_utility.set_location('Entering: '||l_proc,5);
561 OPEN csr_annual_leave_accrual_plan (p_business_group_id
562 ,p_calculation_date
563 ,p_assignment_id);
564
565 FETCH csr_annual_leave_accrual_plan INTO l_plan_id;
566 CLOSE csr_annual_leave_accrual_plan;
567 hr_utility.set_location('Leaving:'||l_proc,10);
568 RETURN l_plan_id;
569
570 -- EXCEPTION
571 -- WHEN OTHERS
572 -- THEN
573 -- hr_utility.set_location('Leaving:'||l_proc,99);
574 -- RETURN NULL;
575 END;
576
577
578
579
580 /*---------------------------------------------------------------------
581 Name : get_continuous_service_date
582 Purpose : To get the Continuous Service Date for an Annual Leave Plan
583 Returns : CONTINUOUS_SERVICE_DATE if successful, NULL otherwise
584 ---------------------------------------------------------------------*/
585
586 FUNCTION get_continuous_service_date
587 (p_assignment_id IN NUMBER
588 ,p_business_group_id IN NUMBER
589 ,p_accrual_plan_id IN NUMBER
590 ,p_calculation_date IN DATE)
591 RETURN DATE IS
592
593 l_proc VARCHAR2(72) := g_package||'get_continuous_service_date';
594 l_csd DATE;
595
596 /*Bug2920725 Corrected base tables to support security model*/
597
598 CURSOR csr_continuous_service_date (c_business_group_id NUMBER
599 ,c_accrual_plan_id NUMBER
600 ,c_calculation_date DATE
601 ,c_assignment_id NUMBER) IS
602 SELECT NVL(TO_DATE(pev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS'),pps.date_start)
603 FROM pay_element_entries_f pee,
604 pay_element_entry_values_f pev,
605 pay_input_values_f piv,
606 pay_accrual_plans pap,
607 hr_lookups hrl,
608 per_assignments_f asg,
609 per_periods_of_service pps
610 WHERE pev.element_entry_id = pee.element_entry_id
611 AND pap.accrual_plan_element_type_id = piv.element_type_id
612 AND piv.input_value_id = pev.input_value_id
613 AND pee.entry_type ='E'
614 AND asg.assignment_id = pee.assignment_id
615 AND asg.assignment_id = c_assignment_id
616 AND pap.accrual_plan_id = c_accrual_plan_id
617 AND asg.business_group_id = c_business_group_id
618 AND asg.period_of_service_id = pps.period_of_service_id
619 AND c_calculation_date BETWEEN asg.effective_start_date AND asg.effective_end_date
620 AND c_calculation_date BETWEEN pee.effective_start_date AND pee.effective_end_date
621 AND c_calculation_date BETWEEN piv.effective_start_date AND piv.effective_end_date
622 AND c_calculation_date BETWEEN pev.effective_start_date AND pev.effective_end_date
623 AND piv.name = hrl.meaning
624 AND hrl.lookup_type = 'NAME_TRANSLATIONS'
625 AND hrl.lookup_code = 'PTO_CONTINUOUS_SD';
626
627 BEGIN
628 hr_utility.set_location('Entering:'||l_proc,5);
629 OPEN csr_continuous_service_date (p_business_group_id
630 ,p_accrual_plan_id
631 ,p_calculation_date
632 ,p_assignment_id);
633 FETCH csr_continuous_service_date INTO l_csd;
634 CLOSE csr_continuous_service_date;
635 hr_utility.set_location('Leaving:'||l_proc,10);
636 RETURN l_csd;
637
638 -- EXCEPTION
639 -- WHEN OTHERS
640 -- THEN
641 -- hr_utility.set_location('Leaving:'||l_proc,99);
642 -- RETURN NULL;
643 END;
644
645
646
647 -----------------------------------------------------------------------------
648 -- accrual_daily_basis function
649 --
650 -- public function called by PTO Accrual Formulae
651 -- PTO accrual formula.
652 -----------------------------------------------------------------------------
653
654 function accrual_daily_basis
655 (p_payroll_id in number
656 ,p_accrual_plan_id in number
657 ,p_assignment_id in number
658 ,p_calculation_start_date in date
659 ,p_calculation_end_date in date
660 ,p_service_start_date in date
661 ,p_business_group_hours in number
662 ,p_business_group_freq in varchar2)
663 return number is
664
665 l_procedure_name varchar2(61) := 'hr_au_holidays.accrual_daily_basis' ;
666 l_accrual number := 0 ;
667 l_accrual_band_cache t_accrual_band_tab ;
668 l_asg_work_day_info_cache t_asg_work_day_info_tab ;
669 l_counter integer ;
670 l_years_service number ;
671 l_annual_accrual number ;
672 l_special_annual_accrual number ;
673 l_days_in_year integer ;
674 l_days_in_part_period integer ;
678 l_start_date date ;
675 l_days_suspended integer ;
676 l_next_anniversary_date date ;
677 l_mm_dd varchar2(10);
679 l_end_date date ;
680 l_period_accrual number ;
681 l_asg_working_hours per_all_assignments_f.normal_hours%type ;
682 l_pay_periods_per_year per_time_period_types.number_per_fiscal_year%type ;
683 e_accrual_function_failure exception ;
684
685 -- cursor to get number of periods per year
686
687 cursor c_number_of_periods_per_year (p_payroll_id number
688 ,p_effective_date date) is
689 select tpt.number_per_fiscal_year
690 from pay_payrolls_f p
691 , per_time_period_types tpt
692 where p.payroll_id = p_payroll_id
693 and p_effective_date between p.effective_start_date
694 and p.effective_end_date
695 and tpt.period_type = p.period_type ;
696
697 -- cursor to get assignment work day information
698
699 cursor c_asg_work_day_history(p_assignment_id number
700 ,p_start_date date
701 ,p_end_date date) is
702 select a.effective_start_date
703 , a.effective_end_date
704 , a.normal_hours
705 , a.frequency
706 from per_assignments_f a
707 where a.assignment_id = p_assignment_id
708 and a.effective_start_date <= p_end_date
709 and a.effective_end_date >= p_start_date
710 order by
711 a.effective_start_date ;
712
713 -- cursor to get accrual band details
714
715 cursor c_accrual_bands (p_accrual_plan_id number) is
716 select ab.lower_limit
717 , ab.upper_limit
718 , ab.annual_rate
719 from pay_accrual_bands ab
720 where ab.accrual_plan_id = p_accrual_plan_id
721 order by
722 ab.lower_limit ;
723
724 /*Bug 5964317 Modified cursor c_periods to get time periods for corresponding payrolls */
725 -- cursor to get time periods to process
726
727 cursor c_periods (p_assignment_id number
728 ,p_start_date date
729 ,p_end_date date) is
730 select greatest(tp.start_date,paf.effective_start_date) start_date,least(tp.end_date,paf.effective_end_date) end_date
731 from per_time_periods tp,per_assignments_f paf
732 where paf.assignment_id = p_assignment_id
733 and tp.payroll_id = paf.payroll_id
734 and tp.start_date <= paf.effective_end_date
735 and tp.end_date >= paf.effective_start_date
736 and tp.start_date <= p_end_date
737 and tp.end_date >= p_start_date
738 and paf.effective_start_date <= p_end_date
739 and paf.effective_end_date >= p_start_date
740 order by tp.start_date ;
741
742 -- local function to get accrual annual rate from PL/SQL table
743
744 function accrual_annual_rate(p_years_service number) return number is
745
746 l_procedure_name varchar2(61) := ' accrual_annual_rate' ;
747 l_annual_accrual pay_accrual_bands.annual_rate%type ;
748 l_counter integer := 1 ;
749 l_band_notfound_flag boolean := true ;
750
751 begin
752
753 hr_utility.trace(' In: ' || l_procedure_name) ;
754
755 -- loop through the PL/SQL table looking for a likely accrual band
756 while l_accrual_band_cache.count > 0
757 and l_band_notfound_flag
758 and l_counter <= l_accrual_band_cache.last
759 loop
760
761 if (p_years_service >= l_accrual_band_cache(l_counter).lower_limit) and
762 (p_years_service < l_accrual_band_cache(l_counter).upper_limit)
763 then
764
765 l_annual_accrual := l_accrual_band_cache(l_counter).annual_rate ;
766 l_band_notfound_flag := false ;
767
768 end if ;
769
770 l_counter := l_counter + 1 ;
771
772 end loop ;
773
774 -- raise error if no accrual band found
775 if l_band_notfound_flag
776 then
777
778 raise e_accrual_function_failure ;
779
780 end if ;
781
782 hr_utility.trace(' Out: ' || l_procedure_name ||' '|| l_annual_accrual) ;
783 return l_annual_accrual ;
784
785 end accrual_annual_rate ;
786
787 -- local function to get asg working hours from PL/SQL table
788
789 function asg_working_hours(p_effective_date date
790 ,p_frequency varchar2) return number is
791
792 l_procedure_name varchar2(61) := ' asg_working_hours' ;
793 l_asg_working_hours per_all_assignments_f.normal_hours%type ;
794 l_counter integer := 1 ;
795 l_hours_notfound_flag boolean := true ;
796
797 begin
798
799 hr_utility.trace(' In: ' || l_procedure_name) ;
800 hr_utility.trace('p_effective_date = '||to_char(p_effective_date, 'DD-MON-YYYY'));
801
802 -- loop through the PL/SQL table looking for a likely accrual band
806 loop
803 while l_asg_work_day_info_cache.count > 0
804 and l_hours_notfound_flag
805 and l_counter <= l_asg_work_day_info_cache.last
807
808 if p_effective_date between l_asg_work_day_info_cache(l_counter).effective_start_date
809 and l_asg_work_day_info_cache(l_counter).effective_end_date
810 and l_asg_work_day_info_cache(l_counter).frequency = p_frequency
811 then
812
813 l_asg_working_hours := l_asg_work_day_info_cache(l_counter).normal_hours ;
814 l_hours_notfound_flag := false ;
815
816 end if ;
817
818 l_counter := l_counter + 1 ;
819
820 end loop ;
821
822 -- raise error if no working hours found
823 if l_hours_notfound_flag
824 then
825
826 hr_utility.trace(' Failed_mk: ' || l_procedure_name ) ;
827 hr_utility.trace(' End Date: ' || to_char(l_asg_work_day_info_cache(l_counter).effective_end_date, 'DD-MON-YYYY'));
828 raise e_accrual_function_failure ;
829
830 end if ;
831
832 hr_utility.trace(' Out: ' || l_procedure_name) ;
833 return l_asg_working_hours ;
834
835
836
837 end asg_working_hours ;
838
839 begin
840
841 hr_utility.trace('In: ' || l_procedure_name) ;
842 hr_utility.trace(' p_payroll_id: ' || to_char(p_payroll_id)) ;
843 hr_utility.trace(' p_accrual_plan_id: ' || to_char(p_accrual_plan_id)) ;
844 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
845 hr_utility.trace(' p_calculation_start_date: ' || to_char(p_calculation_start_date, 'DD-MM-YYYY')) ;
846 hr_utility.trace(' p_calculation_end_date: ' || to_char(p_calculation_end_date, 'DD-MM-YYYY')) ;
847 hr_utility.trace(' p_service_start_date: ' || to_char(p_service_start_date, 'DD-MM-YYYY')) ;
848 hr_utility.trace(' p_business_group_hours: ' || to_char(p_business_group_hours)) ;
849 hr_utility.trace(' p_business_group_freq: ' || p_business_group_freq) ;
850
851 -- cache the assignment's work day history
852
853 l_counter := 1 ;
854
855 for r_asg_work_day in c_asg_work_day_history(p_assignment_id
856 ,p_calculation_start_date
857 ,p_calculation_end_date)
858 loop
859
860 l_asg_work_day_info_cache(l_counter).effective_start_date := r_asg_work_day.effective_start_date ;
861 l_asg_work_day_info_cache(l_counter).effective_end_date := r_asg_work_day.effective_end_date ;
862
863 if r_asg_work_day.normal_hours is not null then
864 l_asg_work_day_info_cache(l_counter).normal_hours := r_asg_work_day.normal_hours ;
865 else
866 l_asg_work_day_info_cache(l_counter).normal_hours := p_business_group_hours ;
867 end if ;
868
869 if r_asg_work_day.frequency is not null then
870 l_asg_work_day_info_cache(l_counter).frequency := r_asg_work_day.frequency ;
871 else
872 l_asg_work_day_info_cache(l_counter).frequency := p_business_group_freq ;
873 end if ;
874
875 l_counter := l_counter + 1 ;
876
877 end loop ; -- c_asg_work_day_history
878
879 -- cache the accrual bands
880 l_counter := 1 ;
881
882 for r_accrual_band in c_accrual_bands(p_accrual_plan_id)
883 loop
884
885 l_accrual_band_cache(l_counter).lower_limit := r_accrual_band.lower_limit ;
886 l_accrual_band_cache(l_counter).upper_limit := r_accrual_band.upper_limit ;
887 l_accrual_band_cache(l_counter).annual_rate := r_accrual_band.annual_rate ;
888
889 l_counter := l_counter + 1 ;
890
891 end loop ; -- c_accrual_bands
892
893 -- get the number of periods per year
894 open c_number_of_periods_per_year(p_payroll_id, p_calculation_start_date) ;
895 fetch c_number_of_periods_per_year
896 into l_pay_periods_per_year ;
897 close c_number_of_periods_per_year ;
898
899 /*Bug 5964317 Passing p_assignment_id in place of p_payroll_id */
900 -- loop through the payroll periods
901 for r_period in c_periods(p_assignment_id
902 ,p_calculation_start_date
903 ,p_calculation_end_date)
904 loop
905
906 -- how many years of effective service does the assignment have (at the end of each period)
907 -- i.e. (days since hired - days with susp ass) / avg no of days per year
908 l_years_service := floor(((r_period.end_date - p_service_start_date)
909 - hr_au_holidays.days_suspended(p_assignment_id, p_service_start_date, r_period.end_date)) / 365.25) ;
910
911 -- get the accrual band
912 l_annual_accrual := accrual_annual_rate(l_years_service) ;
913
914
915
916 -- get the assignment's normal working hours (at the end of each period)
917 -- l_asg_working_hours := asg_working_hours(r_period.end_date, p_business_group_freq) ;
918
919 l_asg_working_hours := asg_working_hours(least(r_period.end_date, p_calculation_end_date), p_business_group_freq) ;
920
921
922 -- the accrual rate in the accrual band is for assignments that work the
923 -- business group's default working hours. Now prorate the accrual rate
924 -- based on the proporation of the business group hours that the
928 -- the algorithm being used here is:
925 -- assignment works.
926 l_annual_accrual := l_annual_accrual * (l_asg_working_hours / p_business_group_hours) ;
927
929 --
930 -- days to accrue for period
931 -- = (annual entitlement / days in current holiday year)
932 -- * days in period
933 --
934 -- the number of days in the year varies between leap years and
935 -- leap years. if the anniversary date falls in the period (or part
936 -- period) being processed then the calculation needs to treat the
937 -- bit of the period up to the anniversary date separately from the
938 -- bit of the period after the anniversary date to allow for different
939 -- number of days in the holiday year.
940
941 -- we may be dealing with a part period here, ie if the calculation
942 -- start date is part way through the first period or if the
943 -- calculation end date is part way through the last period.
944 if p_calculation_start_date between r_period.start_date and r_period.end_date then
945 l_start_date := p_calculation_start_date ;
946 else
947 l_start_date := r_period.start_date ;
948 end if ;
949
950 if p_calculation_end_date between r_period.start_date and r_period.end_date then
951 l_end_date := p_calculation_end_date ;
952 else
953 l_end_date := r_period.end_date ;
954 end if ;
955
956 -- l_start_date and l_end_date now define the time span we're
957 -- interested in. find the anniversary date and see if it falls
958 -- between the dates.
959 l_mm_dd:= to_char(p_service_start_date, 'MMDD');/*for bug2272301*/
960 if (l_mm_dd = '0229' ) then
961 l_mm_dd:='0228';
962 end if;
963 l_next_anniversary_date := to_date(to_char(l_start_date, 'YYYY') ||l_mm_dd
964 ,'YYYYMMDD') ;
965
966 if l_next_anniversary_date <= l_start_date
967 then
968 l_next_anniversary_date := add_months(l_next_anniversary_date, 12) ;
969 end if ;
970
971 if (least((l_next_anniversary_date-1), p_calculation_end_date)) between l_start_date and l_end_date then
972
973
974 -- this is the special case where the anniversary date is in the time
975 -- span we're dealing with
976
977 -- process the start of the time span up to the (but not incl) anniversary date
978 -- see bug 1313971
979 -- consideration of multiple bands of different annual rate bug no 1460922
980
981 l_years_service := floor((((least((l_next_anniversary_date-1), p_calculation_end_date))- p_service_start_date)
982 - hr_au_holidays.days_suspended(p_assignment_id, p_service_start_date, (least((l_next_anniversary_date-1), p_calculation_end_date)))) / 365.25) ;
983
984
985 l_special_annual_accrual := accrual_annual_rate(l_years_service) ;
986
987
988
989 l_asg_working_hours := asg_working_hours((least((l_next_anniversary_date-1), p_calculation_end_date)), p_business_group_freq) ;
990
991
992 -- the accrual rate in the accrual band is for assignments that work the
993 -- business group's default working hours. Now prorate the accrual rate
994 -- based on the proporation of the business group hours that the
995 -- assignment works.
996 l_special_annual_accrual := l_special_annual_accrual * (l_asg_working_hours / p_business_group_hours) ;
997
998
999
1000 l_days_in_year := (l_next_anniversary_date - add_months(l_next_anniversary_date, -12)) ;
1001 l_days_in_part_period := ((least((l_next_anniversary_date-1), p_calculation_end_date)) - l_start_date) +1 ;
1002 l_days_suspended := hr_au_holidays.days_suspended (p_assignment_id
1003 ,l_start_date
1004 ,(least((l_next_anniversary_date-1), p_calculation_end_date)));
1005 l_period_accrual := (l_special_annual_accrual / l_days_in_year) * (l_days_in_part_period - l_days_suspended) ;
1006
1007 If l_end_date > (l_next_anniversary_date-1) then
1008 -- process the anniversary date to the end of the time span
1009 l_days_in_year := (add_months(l_next_anniversary_date, 12) - l_next_anniversary_date) ;
1010 l_days_in_part_period := (l_end_date - l_next_anniversary_date) + 1 ;
1011 l_days_suspended := hr_au_holidays.days_suspended (p_assignment_id
1012 ,l_next_anniversary_date
1013 ,l_end_date);
1014 l_period_accrual := l_period_accrual + (l_annual_accrual / l_days_in_year) * (l_days_in_part_period - l_days_suspended);
1015 end if;
1016
1017 else
1018
1019 -- this is the most common case where the anniversary date is outside
1020 -- the time span we're dealing with
1021
1022 l_days_in_year := (l_next_anniversary_date - add_months(l_next_anniversary_date, -12)) ;
1023 l_days_in_part_period := (l_end_date - l_start_date) + 1 ;
1024 l_days_suspended := hr_au_holidays.days_suspended (p_assignment_id
1025 ,l_start_date
1026 ,l_end_date);
1027 l_period_accrual := (l_annual_accrual / l_days_in_year) * (l_days_in_part_period - l_days_suspended) ;
1028
1029 end if ;
1030
1034
1031 l_accrual := l_accrual + l_period_accrual ;
1032
1033 end loop ; -- c_periods
1035 hr_utility.trace('Out: ' || l_procedure_name) ;
1036 return l_accrual ;
1037
1038 -- exception
1039 -- when e_accrual_function_failure
1040 -- then
1041 -- hr_utility.set_message(801, 'HR_AU_ACCRUAL_FUNCTION_FAILURE') ;
1042 -- hr_utility.raise_error ;
1043
1044 end accrual_daily_basis ;
1045
1046
1047 /*---------------------------------------------------------------------
1048 Name : days_suspended
1049 Purpose : to get the number of suspended days in the period
1050 Returns : Number of suspended days
1051 Issue - the requirement AU019PTO 1.8 talks about suspending accrual
1052 based on leave types. In Core PTO they suggest using assignment status
1053 so basing on that but including proration.
1054 ---------------------------------------------------------------------*/
1055
1056 FUNCTION days_suspended
1057 (p_assignment_id IN NUMBER
1058 ,p_start_date IN DATE
1059 ,p_end_date IN DATE)
1060 RETURN NUMBER IS
1061
1062 /*Bug2920725 Corrected base tables to support security model*/
1063
1064 CURSOR csr_days_suspended(c_assignment_id NUMBER
1065 ,c_start_date DATE
1066 ,c_end_date DATE) IS
1067 SELECT
1068 NVL(SUM(1+
1069 LEAST(effective_end_date, c_end_date)
1070 - GREATEST(effective_start_date, c_start_date)),0)
1071 FROM
1072 per_assignments_f asg
1073 ,per_assignment_status_types t
1074 WHERE
1075 assignment_id = c_assignment_id
1076 AND t.assignment_status_type_id = asg.assignment_status_type_id
1077 AND effective_start_date <= c_end_date
1078 AND effective_end_date >= c_start_date
1079 AND per_system_status = 'SUSP_ASSIGN';
1080
1081 l_proc VARCHAR2(72) := g_package||'days_suspended';
1082 l_days_suspended NUMBER := 0;
1083
1084 BEGIN
1085
1086 hr_utility.set_location('Entering'||l_proc,5);
1087 -- hr_utility.trace(TO_CHAR(p_start_date,'DD-MM-YYYY')||' and '
1088 -- ||TO_CHAR(p_end_date,'DD-MM-YYYY'));
1089
1090 IF (p_start_date > p_end_date) THEN
1091 hr_utility.set_message(801,'HR_AU_INVALID_DATE_RANGE');
1092 hr_utility.raise_error;
1093 END IF;
1094
1095 OPEN csr_days_suspended(p_assignment_id
1096 ,p_start_date
1097 ,p_end_date);
1098 FETCH csr_days_suspended INTO l_days_suspended;
1099 CLOSE csr_days_suspended;
1100
1101 hr_utility.trace('Days Suspended between '
1102 ||TO_CHAR(p_start_date,'DD-MM-YYYY')||' and '
1103 ||TO_CHAR(p_end_date,'DD-MM-YYYY')||' = '
1104 ||TO_CHAR(l_days_suspended));
1105 hr_utility.set_location('Leaving:'||l_proc,10);
1106
1107 RETURN l_days_suspended;
1108
1109 -- EXCEPTION
1110 -- WHEN others THEN
1111 -- hr_utility.set_location('Leaving:'||l_proc,99);
1112 -- RETURN NULL;
1113
1114 END days_suspended;
1115
1116 -----------------------------------------------------------------------------
1117 -- check_periods function
1118 --
1119 -- public function called by AU_ANNUAL_LEAVE_ACCRUAL_DAILY
1120 -- PTO accrual formula.
1121 -----------------------------------------------------------------------------
1122
1123 function check_periods
1124 (p_payroll_id in number)
1125 return date is
1126
1127 l_proc varchar2(61) := 'hr_au_holidays.check_periods' ;
1128 l_end_date date := to_date('01010001','DDMMYYYY');
1129
1130 -- cursor to check payroll periods exist up to calc_end_date
1131
1132 cursor c_last_period (p_payroll_id number) is
1133 select max(tp.end_date)
1134 from per_time_periods tp
1135 where tp.payroll_id = p_payroll_id;
1136 begin
1137
1138 hr_utility.set_location(' In: ' || l_proc,5) ;
1139
1140 -- check payroll periods exist up to calculation_end_date
1141
1142 open c_last_period ( p_payroll_id );
1143 fetch c_last_period into l_end_date;
1144 close c_last_period;
1145
1146 hr_utility.set_location(' Out: ' || l_proc,10) ;
1147
1148 return(l_end_date);
1149
1150 -- EXCEPTION
1151 -- WHEN others THEN
1152 -- hr_utility.trace('Error - payroll periods not found for payroll_id '||to_char(p_payroll_id));
1153 -- hr_utility.set_location('Leaving:'||l_proc,99);
1154 -- RETURN NULL;
1155
1156 end check_periods ;
1157
1158 -----------------------------------------------------------------------------
1159 -- adjust_for_suspend_assign function
1160 --
1161 -- public function called by Accrual/Entitlement Formula
1162 -- adjusts ineligability end date to take account of any
1163 -- periods when assignment was suspended
1167 (p_assignment_id IN NUMBER
1164 -----------------------------------------------------------------------------
1165
1166 function adjust_for_suspend_assign
1168 ,p_adjust_date IN DATE
1169 ,p_start_date IN DATE
1170 ,p_end_date IN DATE)
1171 return date is
1172
1173 l_proc varchar2(61) := 'hr_au_holidays.adjust_for_suspend_assign' ;
1174 l_days_suspended number := 1;
1175 l_start_date date := p_start_date;
1176 l_adjust_date date := p_adjust_date;
1177
1178 begin
1179
1180 hr_utility.set_location(' In: ' || l_proc,5) ;
1181
1182 -- loop to check each new period added on for suspended assignments
1183
1184 while (l_days_suspended > 0) and (l_adjust_date < p_end_date) loop
1185
1186 l_days_suspended := hr_au_holidays.days_suspended (p_assignment_id
1187 ,l_start_date
1188 ,l_adjust_date);
1189 l_start_date := l_adjust_date;
1190 l_adjust_date := l_adjust_date + l_days_suspended;
1191
1192 end loop;
1193
1194 if l_adjust_date > p_end_date then
1195 l_adjust_date := p_end_date;
1196 end if;
1197
1198 hr_utility.set_location(' Out: ' || l_proc,10) ;
1199
1200 return (l_adjust_date);
1201
1202 -- EXCEPTION
1203 -- WHEN others THEN
1204 -- hr_utility.set_location('Leaving:'||l_proc,99);
1205 -- RETURN NULL;
1206
1207 end adjust_for_suspend_assign ;
1208
1209 -----------------------------------------------------------------------------
1210 --
1211 -- Find Leave Adjustment Intialise or Leave Entitlement Initailise value
1212 -- depending on the parameter(p_initialise_type) passed.
1213 --
1214 -- public function called by Leave Formulae
1215 --
1216 -----------------------------------------------------------------------------
1217
1218 function get_leave_initialise
1219 (p_assignment_id in NUMBER
1220 ,p_accrual_plan_id in NUMBER
1221 ,p_calc_end_date in DATE
1222 ,p_initialise_type in VARCHAR2
1223 ,p_start_date in DATE
1224 ,p_end_date in DATE)
1225 return number is
1226
1227 l_proc varchar2(61) := 'hr_au_holidays.get_leave_initailise' ;
1228 l_initialise number := 0;
1229
1230 -- find Leave Initialise Values
1231
1232 cursor c_get_initialise ( v_assignment_id number
1233 ,v_accrual_plan_id number
1234 ,v_calc_end_date date
1235 ,v_initialise_type varchar2
1236 ,v_start_date date
1237 ,v_end_date date ) is
1238 select
1239 sum(nvl(to_number(pev1.screen_entry_value),0))
1240 from
1241 pay_accrual_plans pap
1242 ,pay_element_types_f pet
1243 ,pay_element_links_f pel
1244 ,pay_input_values_f piv1
1245 ,pay_input_values_f piv2
1246 ,pay_element_entries_f pee
1247 ,pay_element_entry_values_f pev1
1248 ,pay_element_entry_values_f pev2
1249 where
1250 pee.assignment_id = v_assignment_id
1251 and pet.element_name = v_initialise_type
1252 and pet.element_type_id = pel.element_type_id
1253 and pel.element_link_id = pee.element_link_id
1254 and pee.element_entry_id = pev1.element_entry_id
1255 and pev1.input_value_id = piv1.input_value_id
1256 and piv1.name = 'Hours'
1257 and piv1.element_type_id = pet.element_type_id
1258 and pee.element_entry_id = pev2.element_entry_id
1259 and pev2.input_value_id = piv2.input_value_id
1260 and piv2.name = 'Accrual Plan'
1261 and piv2.element_type_id = pet.element_type_id
1262 and pev2.screen_entry_value = pap.accrual_plan_name
1263 and pap.accrual_plan_id = v_accrual_plan_id
1264 and pee.effective_start_date <= v_calc_end_date
1265 and pee.effective_start_date between pet.effective_start_date and pet.effective_end_date
1266 and pee.effective_start_date between pel.effective_start_date and pel.effective_end_date
1267 and pee.effective_start_date between piv1.effective_start_date and piv1.effective_end_date
1268 and pee.effective_start_date between pev1.effective_start_date and pev1.effective_end_date
1269 and pee.effective_start_date between piv2.effective_start_date and piv2.effective_end_date
1270 and pee.effective_start_date between pev2.effective_start_date and pev2.effective_end_date
1271 and pee.effective_start_date between v_start_date and v_end_date;
1272
1273 begin
1274
1275 hr_utility.set_location(' In: ' || l_proc,5) ;
1276
1280 ,p_accrual_plan_id
1277 -- find total leave initialise - should return zero if none entered
1278
1279 open c_get_initialise (p_assignment_id
1281 ,p_calc_end_date
1282 ,p_initialise_type
1283 ,p_start_date
1284 ,p_end_date );
1285 fetch c_get_initialise into l_initialise;
1286 close c_get_initialise;
1287
1288 hr_utility.trace('Initialise : '||to_char(l_initialise));
1289 hr_utility.set_location(' Out: ' || l_proc,10) ;
1290
1291 return(nvl(l_initialise,0));
1292
1293 end get_leave_initialise ;
1294
1295
1296 -----------------------------------------------------------------------------
1297 --
1298 -- Find long service leave entitlement date
1299 --
1300 -- Because LSL has two entitlement periods they cannot be stored
1301 --
1302 -- Find long service leave entitlement date
1303 --
1304 -- Because LSL has two entitlement periods they cannot be stored
1305 -- in the standard PTO model. For LSL we get the periods from the
1306 -- different in the from and to dates in the plan accrual bands.
1307 --
1308 -- public function called by Long Service Leave Formulae
1309 --
1310 -----------------------------------------------------------------------------
1311
1312 function get_lsl_entitlement_date
1313 ( p_accrual_plan_id in NUMBER
1314 ,p_assignment_id in NUMBER
1315 ,p_enrollment_date in DATE
1316 ,p_service_start_date in DATE
1317 ,p_calculation_date in DATE
1318 ,p_next_entitlement_date in out NOCOPY DATE)
1319 return date is
1320
1321 l_proc varchar2(61) := 'hr_au_holidays.get_lsl_entitlement_date' ;
1322 l_first_period number;
1323 l_subsequent_periods number;
1324 l_entitlement_date date;
1325 l_next_entitlement_date date;
1326 l_eot date := to_date('31124712','DDMMYYYY');
1327
1328 -- find lsl entitlement periods
1329 cursor c_accrual_bands (v_accrual_plan_id number) is
1330 select (ab.upper_limit - ab.lower_limit)
1331 from pay_accrual_bands ab
1332 where ab.accrual_plan_id = v_accrual_plan_id
1333 order by
1334 ab.lower_limit ;
1335
1336 begin
1337
1338 hr_utility.set_location(' In: ' || l_proc, 5) ;
1339
1340 open c_accrual_bands (p_accrual_plan_id);
1341 fetch c_accrual_bands into l_first_period;
1342 fetch c_accrual_bands into l_subsequent_periods;
1343 close c_accrual_bands;
1344
1345 hr_utility.trace('First : '||to_char(l_first_period) );
1346 hr_utility.trace('Subsequent : '||to_char(l_subsequent_periods) );
1347
1348 if (l_first_period <= 0) OR (l_subsequent_periods <= 0) then
1349 hr_utility.set_message(801,'HR_AU_INVALID_LSL_PERIODS');
1350 hr_utility.raise_error;
1351 end if;
1352
1353 -- set entitlement date to end of first period plus any suspension
1354 -- adjustment
1355 l_entitlement_date := p_service_start_date;
1356
1357 p_next_entitlement_date := hr_au_holidays.adjust_for_suspend_assign
1358 (p_assignment_id
1359 ,add_months(p_service_start_date, (l_first_period * 12) )
1360 ,p_service_start_date
1361 ,l_eot);
1362
1363 if p_calculation_date < p_next_entitlement_date then
1364 return (l_entitlement_date);
1365 end if;
1366
1367 -- while next date is less that calculation date keep adding
1368 -- subsequent entitlement periods
1369 while p_calculation_date >= p_next_entitlement_date loop
1370
1371 l_entitlement_date := p_next_entitlement_date;
1372
1373 p_next_entitlement_date := hr_au_holidays.adjust_for_suspend_assign
1374 (p_assignment_id
1375 ,add_months(l_entitlement_date, (l_subsequent_periods * 12) )
1376 ,l_entitlement_date
1377 ,l_eot);
1378
1379 end loop;
1380
1381 hr_utility.set_location(' Out: ' || l_proc, 10) ;
1382
1383 return (l_entitlement_date);
1384
1385 -- EXCEPTION
1386 -- WHEN others THEN
1387 -- hr_utility.trace('Error - cursor c_accrual_bands failed - Accrual Plan ID: '||to_char(p_accrual_plan_id) );
1388 -- hr_utility.set_location('Leaving: '||l_proc,99);
1389 -- RETURN (p_service_start_date - 1);
1390
1391 end get_lsl_entitlement_date;
1392
1393
1394 -----------------------------------------------------------------------------
1395 --
1396 -- Validate Accrual Plan Name in Entitlement Adjustment Element Input Value
1397 --
1398 -----------------------------------------------------------------------------
1399
1400 function validate_accrual_plan_name
1401 ( p_business_group_id in NUMBER
1402 ,p_entry_value in VARCHAR2)
1403 return number is
1404
1405 l_proc varchar2(61) := 'hr_au_holidays.validate_accrual_plan_name' ;
1406 l_plan_exists number := 0;
1407
1408 -- find plan name
1409 cursor c_plan_name ( v_business_group_id number
1410 ,v_entry_value varchar2 ) is
1411 select 1
1412 from pay_accrual_plans pap
1413 where pap.business_group_id = v_business_group_id
1414 and pap.accrual_plan_name = v_entry_value;
1415
1416 begin
1417
1418 hr_utility.set_location(' In: ' || l_proc, 5) ;
1419
1420 open c_plan_name ( p_business_group_id
1421 ,p_entry_value);
1422 fetch c_plan_name into l_plan_exists;
1423
1424 if c_plan_name%notfound then
1425 l_plan_exists := 0;
1426 end if;
1427
1428 close c_plan_name;
1429
1430 hr_utility.set_location(' Out: ' || l_proc, 10) ;
1431
1432 return (l_plan_exists);
1433
1434 --EXCEPTION
1435 -- WHEN others THEN
1436 -- hr_utility.set_location('Leaving: '||l_proc,99);
1437 -- RETURN (99);
1438
1439 end validate_accrual_plan_name;
1440
1441 END hr_au_holidays;
[Home] [Help]