[Home] [Help]
PACKAGE BODY: APPS.HR_AU_HOLIDAYS
Source
1 PACKAGE BODY hr_au_holidays AS
2 -- $Header: hrauhol.pkb 120.19.12020000.4 2012/11/09 05:00:22 mdubasi 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 -- 09-Nov-2012 mdubasi 115.39 Bug# 14789375 - Modified cursor c_get_initialise
15 -- 25-Jun-2012 mdubasi 115.38 Bug# 14094863 - Modified cursor c_get_initialise for considering leave initialization
16 -- 18-Jun-2012 mdubasi 115.37 Bug# 14094863 - Modified cursor c_get_initialise for considering leave initialization
17 -- when the accrual plan's start date is later and in between the seeded element start-end date
18 -- 02-Jun-2011 jmarupil 115.36 Bug# 12586038 - Modified cursor csr_get_ppl_detail for recurring paid parental leave
19 -- 25-jan-2011 jmarupil 115.35 Bug# 12586038 - Modified function get_paid_parental_leave for recurring paid parental leave
20 -- 17-Jan-2011 dduvvuri 115.35 Bug# 9691417 - Modified cursor in function get_leave_initialise to use fnd_number.canonical_to_number instead of to_number
21 -- 09-Dec-2010 avenkatk 115.34 Bug# 10384820 Modified function get_paid_parental_leave for retropay case
22 -- 24-Nov-2010 skshin 115.33 Bug #10212532 - Added get_paid_parental_leave function and made changes for paid parental leave
23 -- 16-Nov-2010 avenkatk 115.30 Bug #9950136 - Added changes for foreign workers accruals
24 -- 12-MAY-2010 skshin 115.29 9507714 - Modifed cursor c_enrollment_startdate to remove a join to cover a new primary assignment change
25 -- 31-Mar-2009 pnethaga 115.28 9444169 - Added cursor c_get_oth_adj_type to get the value for
26 -- 'Other Adjustments Type' in 'Further Accrual Information' DFF
27 -- Added Step 4b.
28 -- 17-Jun-2009 pmatamsr 115.27 8604518 - Cursor c_enrollment_startdate modified in function au_get_enrollment_startdate
29 -- 26 May 2009 dduvvuri 115.26 8482224 - Cursor csr_get_accrual_plan_id modified in Function get_accrual_plan_by_category
30 -- 04-Mar-2009 dduvvuri 115.25 8301730 - function get_accrual_entitlement - removed Step 4a
31 -- 16-Dec-2008 pmatamsr 115.23 Bug 7607177 function au_get_enrollment_startdate added
32 -- to get the correct enrollment start date
33 -- for calculating the PTO Accrual.
34 -- 01-Oct-2007 priupadh 115.22 Bug 6449311 function get_accrual_entitlement added Step 4a
35 -- 04-Apr-2007 priupadh 115.21 Bug 5964317 removed cursor c_asg_periods modified c_periods
36 -- 02-Apr-2007 priupadh 115.20 Bug 5964317 Added cursor c_asg_periods and loop a_periods
37 -- 29 May 2003 apunekar 115.9 Bug2920725 - Corrected base tables to support security model
38 -- 02 Dec 2002 Apunekar 115.18 Bug#2689173-Added Nocopy to out and in out parameters
39 -- 20-MAR-2001 apunekar 115.17 Validated anniversary date for 29th feb input,Bug#2272301
40 -- 10-DEC-2001 srussell 115.16 Put in checkfile syntax.
41 -- 07-DEC-2001 srussell 115.15 Allow get_accrual_entitlement to return a
42 -- negative amount for net_accrual.
43 -- 28-NOV-2001 nnaresh 115.12 Updated for GSCC Standards
44 -- 26-SEP-2001 shoskatt 115.11 Used the get_leave_initialise to get the accrual
45 -- initialise at the entitlement end date. This is
46 -- used to calculate the net entitlements
47 -- 12-SEP-2001 shoskatt 115.10 Used the get_leave_initialise function to get
48 -- the Leave Entitlement Initialise and Leave Accrual
49 -- Initialise value. This is used to calculate the
50 -- Net Entitlement as well as Net Accrual. Bug #1942971
51 -- 16-OCT-2000 rayyadev 115.9 change the code to consider multiple bands with
52 -- different annual rate bug no 1460922
53 -- 25-Jan-2000 sclarke 115.8 Moved term_lsl_eligibility_years to pay_au_terminations
54 -- 29-May-2000 makelly 115.7 Re-added get_net_accrual wrapper
55 -- 26-May-2000 makelly 115.6 Bug 1313971 anniversary date counted twice in
56 -- accrual_daily_basis. (removed exceptions)
57 -- 16-May-2000 makelly 115.5 Bug 1300935 Altered accrual_entitlement to check for
58 -- start date and change to entitlement adjustments
59 -- 03-May-2000 makelly 115.4 Bug 1273677 and added accrual_entitlement fn
60 -- to simplify calls from accrual/absence forms
61 -- 21-Mar-2000 makelly 115.3 fixed bug in call to asg_working_hours
62 -- 15-Mar-2000 sclarke 115.2 Added LSL function
63 -- 21-Jan-2000 makelly 115.1 Initial - Based on hrnzhol.pkb
64 -----------------------------------------------------------------------------------
65 -- private global declarations
66 -----------------------------------------------------------------------------------
67
68 -- Define a record and PL/SQL table to hold accrual band information.
69 -- Used by accrual_period_basis and ann_leave_accrual_daily_basis
70 -- functions.
71
72 type t_accrual_band_rec is record
73 (lower_limit pay_accrual_bands.lower_limit%type
74 ,upper_limit pay_accrual_bands.upper_limit%type
75 ,annual_rate pay_accrual_bands.annual_rate%type) ;
76
77 type t_accrual_band_tab
78 is table of t_accrual_band_rec
79 index by binary_integer ;
80
81 -- Define a record and PL/SQL table to hold assignment work day data.
82 -- Used by accrual_period_basis and ann_leave_accrual_daily_basis
83 -- functions.
84
85 type t_asg_work_day_info_rec is record
86 (effective_start_date per_all_assignments_f.effective_start_date%type
87 ,effective_end_date per_all_assignments_f.effective_end_date%type
88 ,normal_hours per_all_assignments_f.normal_hours%type
89 ,frequency per_all_assignments_f.frequency%type) ;
90
91 type t_asg_work_day_info_tab
92 is table of t_asg_work_day_info_rec
93 index by binary_integer ;
94
95 /* Bug 9950136 - Start of FW Changes for Accruals */
96
97 g_debug BOOLEAN;
98
99 /*
100 --------------------------------------------------------------------
101 Name : set_accrual_ids
102 Type : Procedure
103 Access: Public
104 Description:This public procedure takes the accrual plan id and
105 identifies the type of plan, does an associated FW
106 plan exist and sets the globals.
107 --------------------------------------------------------------------
108 */
109
110
111 PROCEDURE set_accrual_ids
112 (p_accrual_plan_id IN NUMBER)
113 IS
114
115 CURSOR csr_fw_plan_details
116 (c_accrual_plan_id pay_accrual_plans.accrual_plan_id%TYPE)
117 IS
118 SELECT pap.accrual_plan_id plan_id
119 ,pap.accrual_category accrual_category
120 ,pap.information3 information3
121 ,pap.information4 information4
122 ,pap_fw.accrual_plan_id fw_plan_id
123 ,pap_fw.accrual_category fw_accrual_category
124 ,pap_fw.information3 fw_information3
125 ,pap_fw.information3 fw_information4
126 FROM pay_accrual_plans pap
127 ,pay_accrual_plans pap_fw
128 where pap.accrual_plan_id = c_accrual_plan_id
129 AND pap.business_group_id = pap_fw.business_group_id(+)
130 AND pap.accrual_category = pap_fw.accrual_category(+)
131 AND pap_fw.information3(+) = 'Y'
132 AND pap_fw.information4(+) = pap.accrual_plan_id;
133
134 l_csr_details csr_fw_plan_details%ROWTYPE;
135 l_procedure VARCHAR2(100);
136
137 BEGIN
138
139 g_debug := hr_utility.debug_enabled;
140 IF g_debug
141 THEN
142 l_procedure := g_package||'.set_accrual_ids';
143 hr_utility.set_location('Entering procedure '||l_procedure,1000);
144 hr_utility.set_location('p_accrual_plan_id '||p_accrual_plan_id,1000);
145 END IF;
146
147 OPEN csr_fw_plan_details(p_accrual_plan_id);
148 FETCH csr_fw_plan_details INTO l_csr_details;
149 CLOSE csr_fw_plan_details;
150
151 IF l_csr_details.information3 = 'Y'
152 THEN
153 g_plan_id := p_accrual_plan_id;
154 g_fw_plan_id := p_accrual_plan_id;
155 g_is_fw_plan := TRUE;
156 g_fw_exists := TRUE;
157
158 ELSIF (l_csr_details.fw_plan_id IS NOT NULL
159 AND l_csr_details.fw_information3 = 'Y')
160 THEN
161 g_plan_id := p_accrual_plan_id;
162 g_fw_plan_id := l_csr_details.fw_plan_id;
163 g_is_fw_plan := FALSE;
164 g_fw_exists := TRUE;
165
166 ELSE
167 g_plan_id := p_accrual_plan_id;
168 g_fw_plan_id := NULL;
169 g_is_fw_plan := FALSE;
170 g_fw_exists := FALSE;
171
172 END IF;
173
174 IF g_debug
175 THEN
176 hr_utility.set_location('g_plan_id '||g_plan_id,1000);
177 hr_utility.set_location('g_fw_plan_id '||g_fw_plan_id,1000);
178 hr_utility.set_location('Leaving procedure '||l_procedure,1000);
179 END IF;
180
181 END set_accrual_ids;
182
183 /* Bug 10212532 - Added to adjust NE and NA for Paid Parental Leave with Accrued set to 'No' */
184
185
186 function get_paid_parental_leave
187 (p_assignment_id in NUMBER
188 ,p_plan_id in NUMBER
189 ,p_payroll_id IN NUMBER
190 ,p_business_group_id IN NUMBER
191 ,p_start_date in DATE
192 ,p_end_date in DATE)
193 return number is
194
195 /* Bug 10384820 - Added a condition to remove Retro created entires
196 The Accrued Value and the dates should only be checked against the original entry
197 */
198
199 /* Bug 12586038 - Modified the cursor csr_get_ppl_detail to calculate Accrual for
200 paid Parental leave recurring element also */
201
202
203 cursor csr_get_ppl_detail (c_start_date date) is
204 select
205 peev.screen_entry_value accrued,
206 (to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS')) start_date,
207 (to_date(peev2.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS')) end_date
208 from
209 pay_element_entries_f pee,
210 pay_element_entry_values_f peev,
211 pay_element_entry_values_f peev1,
212 pay_element_entry_values_f peev2,
213 pay_element_types_f pet,
214 pay_input_values_f piv,
215 pay_input_values_f piv1,
216 pay_input_values_f piv2
217 where pee.assignment_id = p_assignment_id
218 and pee.element_type_id = pet.element_type_id
219 and pet.element_name IN ('Statutory Paid Parental Leave Payment', 'Rec Statutory PPL Payment')
220 and pet.legislation_code = 'AU'
221 and peev.screen_entry_value = 'N'
222 and pee.element_entry_id = peev.element_entry_id
223 and pee.effective_start_date = (select Max(pe.effective_start_date) from pay_element_entries_f pe , pay_element_entry_values_f peev,
224 pay_input_values_f piv
225 where pe.assignment_id = p_assignment_id
226 and pee.element_entry_id= pe.element_entry_id
227 and peev.screen_entry_value = 'N'
228 and pe.element_entry_id = peev.element_entry_id
229 and pe.effective_start_date = peev.effective_start_date
230 and pe.effective_end_date = peev.effective_end_date
231 and peev.input_value_id = piv.input_value_id
232 and piv.name = 'Accrued'
233 and pe.effective_end_date >= greatest(to_date('01/01/2011','DD/MM/YYYY'), c_start_date)
234 group by pe.element_entry_id)
235 and pee.effective_start_date = peev.effective_start_date
236 and pee.effective_end_date = peev.effective_end_date
237 and peev.input_value_id = piv.input_value_id
238 and piv.name = 'Accrued'
239 and pee.element_entry_id = peev1.element_entry_id
240 and pee.effective_start_date = peev1.effective_start_date
241 and pee.effective_end_date = peev1.effective_end_date
242 and peev1.input_value_id = piv1.input_value_id
243 and piv1.name = 'Start Date'
244 and pee.element_entry_id = peev2.element_entry_id
245 and pee.effective_start_date = peev2.effective_start_date
246 and pee.effective_end_date = peev2.effective_end_date
247 and peev2.input_value_id = piv2.input_value_id
248 and piv2.name = 'End Date'
249 and piv.element_type_id = pet.element_type_id
250 and piv1.element_type_id = pet.element_type_id
251 and piv2.element_type_id = pet.element_type_id
252 and pee.effective_end_date >= greatest(to_date('01/01/2011','DD/MM/YYYY'), c_start_date)
253 and p_end_date between piv.effective_start_date and piv.effective_end_date
254 and p_end_date between piv1.effective_start_date and piv1.effective_end_date
255 and p_end_date between piv2.effective_start_date and piv2.effective_end_date
256 and p_end_date between pet.effective_start_date and pet.effective_end_date
257 and pee.creator_type NOT IN ('EE','RR');
258 --group by pee.element_entry_id; /* Bug 10384820 */
259
260 l_proc varchar2(61) := g_package||'.get_paid_parental_leave' ;
261 l_date_start date;
262 l_ppl_start_date date;
263 l_ppl_end_date date;
264 l_start_date date;
265 l_end_date date;
266 l_accrual_end_date date;
267 l_accrual number;
268 l_not_accrued_before_date number;
269 l_not_accrued_end_date number;
270 l_not_accrued_dates number := 0;
271 l_tot_not_accrued_dates number := 0;
272 l_get_days_ppl number := 0;
273
274 begin
275
276 g_debug := hr_utility.debug_enabled;
277 IF g_debug
278 THEN
279 hr_utility.set_location('Entering function '||l_proc,1);
280 hr_utility.set_location('p_start_date '||p_start_date,1);
281 hr_utility.set_location('p_end_date '||p_end_date,1);
282 END IF;
283
284 l_date_start := nvl(p_start_date, to_date('01/01/2011','DD/MM/YYYY'));
285
286 for rec_ppl in csr_get_ppl_detail(l_date_start) loop
287
288 if (rec_ppl.start_date <= p_end_date and rec_ppl.end_date >= l_date_start) then
289
290 l_ppl_start_date := rec_ppl.start_date;
291
292 per_accrual_calc_functions.get_accrual(
293 p_assignment_id => p_assignment_id
294 ,p_plan_id => p_plan_id
295 ,p_payroll_id => p_payroll_id
296 ,p_business_group_id => p_business_group_id
297 ,p_calculation_date => l_ppl_start_date-1
298 ,p_start_date => l_start_date
299 ,p_end_date => l_end_date
300 ,p_accrual_end_date => l_accrual_end_date
301 ,p_accrual => l_not_accrued_before_date) ;
302
303 l_ppl_end_date := rec_ppl.end_date;
304 if p_end_date < l_ppl_end_date then
305 l_ppl_end_date := p_end_date;
306 end if;
307
308 per_accrual_calc_functions.get_accrual(
309 p_assignment_id => p_assignment_id
310 ,p_plan_id => p_plan_id
311 ,p_payroll_id => p_payroll_id
312 ,p_business_group_id => p_business_group_id
313 ,p_calculation_date => l_ppl_end_date
314 ,p_start_date => l_start_date
315 ,p_end_date => l_end_date
316 ,p_accrual_end_date => l_accrual_end_date
317 ,p_accrual => l_not_accrued_end_date );
318
319 l_not_accrued_dates := l_not_accrued_end_date - l_not_accrued_before_date;
320 l_tot_not_accrued_dates := l_tot_not_accrued_dates + l_not_accrued_dates;
321
322 IF g_debug THEN
323 hr_utility.set_location('l_not_accrued_dates: '||l_not_accrued_dates,10) ;
324 END IF;
325
326 end if;
327
328 end loop;
329
330 IF g_debug THEN
331 hr_utility.set_location('l_tot_not_accrued_dates: '||l_tot_not_accrued_dates,20) ;
332 hr_utility.set_location('Leaving: '||l_proc,20) ;
333 END IF;
334
335 return l_tot_not_accrued_dates;
336
337 end get_paid_parental_leave;
338
339 /*
340 --------------------------------------------------------------------
341 Name : get_fw_dates
342 Type : Procedure
343 Access: Private
344 Description: The function maintains a PL/SQL table with FW Dates
345 for an assignment. Between the given dates, the function
346 returns the FW Dates.
347 --------------------------------------------------------------------
348 */
349
350 PROCEDURE get_fw_dates
351 (p_assignment_id IN NUMBER
352 ,p_start_date IN DATE
353 ,p_end_date IN DATE
354 ,p_fw_f_tab OUT NOCOPY pay_au_foreign_workers.tab_fw_dates
355 ,p_fw_j_tab OUT NOCOPY pay_au_foreign_workers.tab_fw_dates)
356 IS
357
358 l_proc_name VARCHAR2(100);
359 l_start_date DATE;
360 l_end_of_time DATE;
361
362 l_fw_f_tab pay_au_foreign_workers.tab_fw_dates;
363 l_fw_j_tab pay_au_foreign_workers.tab_fw_dates;
364
365 BEGIN
366
367 l_start_date := to_date('01/07/2010','DD/MM/YYYY');
368 l_end_of_time := to_date('31/12/4712','DD/MM/YYYY');
369
370
371 IF g_debug
372 THEN
373 l_proc_name := g_package||'.get_fw_dates';
374 hr_utility.set_location('Entering Procedure '||l_proc_name,1010);
375 hr_utility.set_location('g_assignment_id '||g_assignment_id,1010);
376 hr_utility.set_location('p_assignment_id '||p_assignment_id,1010);
377 END IF;
378
379 IF (g_assignment_id <> p_assignment_id) OR (g_assignment_id IS NULL)
380 THEN
381
382 g_assignment_id := p_assignment_id;
383 pay_au_foreign_workers.get_foreign_worker_dates
384 (p_assignment_id => p_assignment_id
385 ,p_tax_unit_id => NULL
386 ,p_start_date => l_start_date
387 ,p_end_date => l_end_of_time
388 ,p_fw_type => pay_au_foreign_workers.g_fw_f_type
389 ,p_tab_fw_dates => g_fw_f_tab_dates
390 );
391
392 pay_au_foreign_workers.get_foreign_worker_dates
393 (p_assignment_id => p_assignment_id
394 ,p_tax_unit_id => NULL
395 ,p_start_date => l_start_date
396 ,p_end_date => l_end_of_time
397 ,p_fw_type => pay_au_foreign_workers.g_fw_j_type
398 ,p_tab_fw_dates => g_fw_j_tab_dates
399 );
400
401 pay_au_foreign_workers.get_fw_between_dates
402 (p_fw_tab_dates => g_fw_f_tab_dates
403 ,p_start_date => p_start_date
404 ,p_end_date => p_end_date
405 ,p_result_tab_dates => l_fw_f_tab);
406
407 pay_au_foreign_workers.get_fw_between_dates
408 (p_fw_tab_dates => g_fw_j_tab_dates
409 ,p_start_date => p_start_date
410 ,p_end_date => p_end_date
411 ,p_result_tab_dates => l_fw_j_tab);
412
413 ELSE
414
415 pay_au_foreign_workers.get_fw_between_dates
416 (p_fw_tab_dates => g_fw_f_tab_dates
417 ,p_start_date => p_start_date
418 ,p_end_date => p_end_date
419 ,p_result_tab_dates => l_fw_f_tab);
420
421 pay_au_foreign_workers.get_fw_between_dates
422 (p_fw_tab_dates => g_fw_j_tab_dates
423 ,p_start_date => p_start_date
424 ,p_end_date => p_end_date
425 ,p_result_tab_dates => l_fw_j_tab);
426
427 END IF;
428
429 p_fw_f_tab := l_fw_f_tab;
430 p_fw_j_tab := l_fw_j_tab;
431
432 IF g_debug
433 THEN
434 hr_utility.set_location('Leaving Procedure '||l_proc_name,1010);
435 END IF;
436
437 END get_fw_dates;
438
439
440 /*
441 --------------------------------------------------------------------
442 Name : au_adjust_fw_accruals
443 Type : Procedure
444 Access: Private
445 Description: The function returns the total accrual sans Absences
446 and Other Contrib for FW periods between the given dates
447 --------------------------------------------------------------------
448 */
449
450 PROCEDURE au_adjust_fw_accruals
451 ( p_assignment_id IN NUMBER
452 ,p_plan_id IN NUMBER
453 ,p_payroll_id IN NUMBER
454 ,p_business_group_id IN NUMBER
455 ,p_calculation_date IN DATE
456 ,p_start_date IN DATE
457 ,p_net_ent OUT NOCOPY NUMBER
458 )
459 IS
460
461 l_fw_dates pay_au_foreign_workers.tab_fw_dates;
462 l_fw_f_dates pay_au_foreign_workers.tab_fw_dates;
463 l_fw_j_dates pay_au_foreign_workers.tab_fw_dates;
464
465 l_procedure VARCHAR2(100);
466
467 TYPE l_char_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
468
469 l_fw_types l_char_type;
470 l_tot_accrual NUMBER;
471 l_type_accrual NUMBER;
472
473
474 l_start_date DATE;
475 l_end_date DATE;
476 l_acc_end_date DATE;
477 l_accrual NUMBER;
478 l_net_ent_end NUMBER;
479 l_net_ent_start NUMBER;
480
481 BEGIN
482
483 g_debug := hr_utility.debug_enabled;
484
485 IF g_debug
486 THEN
487 l_procedure := g_package||'.au_adjust_fw_accruals';
488 hr_utility.set_location('Entering function '||l_procedure,1000);
489 END IF;
490
491 get_fw_dates
492 (p_assignment_id => p_assignment_id
493 ,p_start_date => p_start_date
494 ,p_end_date => p_calculation_date
495 ,p_fw_f_tab => l_fw_f_dates
496 ,p_fw_j_tab => l_fw_j_dates);
497
498 l_tot_accrual := 0;
499 FOR i IN 1..2
500 LOOP
501 IF i = 1
502 THEN
503 l_fw_dates := l_fw_f_dates;
504 ELSIF i = 2
505 THEN
506 l_fw_dates := l_fw_j_dates;
507 END IF;
508
509 l_type_accrual := 0;
510 IF l_fw_dates.COUNT > 0
511 THEN
512
513 FOR j in l_fw_dates.FIRST..l_fw_dates.LAST
514 LOOP
515 /* Accrual is got by getting accruals as on Accruals(End Date) - Accruals(Start Date - 1) */
516
517 per_accrual_calc_functions.get_accrual(
518 p_assignment_id => p_assignment_id
519 ,p_plan_id => p_plan_id
520 ,p_payroll_id => p_payroll_id
521 ,p_business_group_id => p_business_group_id
522 ,p_calculation_date => l_fw_dates(j).end_date
523 ,p_start_date => l_start_date
524 ,p_end_date => l_end_date
525 ,p_accrual_end_date => l_acc_end_date
526 ,p_accrual => l_net_ent_end);
527
528 per_accrual_calc_functions.get_accrual(
529 p_assignment_id => p_assignment_id
530 ,p_plan_id => p_plan_id
531 ,p_payroll_id => p_payroll_id
532 ,p_business_group_id => p_business_group_id
533 ,p_calculation_date => (l_fw_dates(j).start_date - 1)
534 ,p_start_date => l_start_date
535 ,p_end_date => l_end_date
536 ,p_accrual_end_date => l_acc_end_date
537 ,p_accrual => l_net_ent_start);
538
539 l_type_accrual := l_type_accrual + (l_net_ent_end - l_net_ent_start);
540
541 END LOOP;
542 END IF;
543
544 l_tot_accrual := l_tot_accrual + l_type_accrual;
545
546 END LOOP;
547
548 p_net_ent := l_tot_accrual;
549
550 IF g_debug
551 THEN
552 hr_utility.set_location('p_net_ent '||p_net_ent,1000);
553 hr_utility.set_location('Leaving function '||l_procedure,1000);
554 END IF;
555
556 END au_adjust_fw_accruals;
557
558 /*
559 --------------------------------------------------------------------
560 Name : get_accrual_entitlement_2010
561 Type : Procedure
562 Access: Private
563 Description: The principal function to calculate accruals and entitlement,
564 is called from main function if FW Plan or Standard
565 Plan with associated FW Plan.
566 --------------------------------------------------------------------
567 */
568
569 FUNCTION get_accrual_entitlement_2010
570 (p_assignment_id IN NUMBER
571 ,p_payroll_id IN NUMBER
572 ,p_business_group_id IN NUMBER
573 ,p_plan_id IN NUMBER
574 ,p_calculation_date IN DATE
575 ,p_net_accrual OUT NOCOPY NUMBER
576 ,p_net_entitlement OUT NOCOPY NUMBER
577 ,p_calc_start_date OUT NOCOPY DATE
578 ,p_last_accrual OUT NOCOPY DATE
579 ,p_next_period_end OUT NOCOPY DATE)
580 RETURN NUMBER
581 IS
582
583 l_proc_name VARCHAR2(100);
584 l_assignment_id NUMBER ;
585 l_plan_id NUMBER ;
586 l_payroll_id NUMBER ;
587 l_business_group_id NUMBER ;
588 l_calculation_date DATE ;
589 l_start_date DATE ;
590 l_end_date DATE ;
591
592 l_accrual_end_date DATE ;
593 l_accrual_period_start_date DATE ;
594 l_accrual_period_end_date DATE ;
595 l_entitlement_period_end_date DATE ;
596
597 l_net_accrual NUMBER ;
598 l_net_entitlement NUMBER ;
599 l_co_formula_id NUMBER ;
600 l_max_co NUMBER ;
601
602 l_leave_end_ent NUMBER ;
603 l_leave_calc_date NUMBER ;
604
605 l_other_adj_type VARCHAR2(10);
606
607 l_initialise_type VARCHAR2(100);
608 l_accrual_init NUMBER ;
609 l_accrual_ent NUMBER ;
610 l_entitlement_init NUMBER ;
611
612 l_fw_leave_end_ent NUMBER;
613 l_fw_leave_calc_date NUMBER;
614 l_fw_accrual_absences NUMBER;
615 l_fw_other NUMBER;
616
617 l_enrol_start_date DATE;
618 l_enrol_end_date DATE;
619
620 l_fw_start_date DATE;
621
622 l_accrual_ent_period NUMBER;
623 l_accrual_acc_period NUMBER;
624
625 l_abs_ent_period NUMBER;
626 l_abs_acc_period NUMBER;
627
628 l_other_ent_period NUMBER;
629 l_other_acc_period NUMBER;
630
631 l_int_net_accrual NUMBER;
632 l_int_net_entitlement NUMBER;
633
634 l_ppl_ent NUMBER; -- bug 10212532
635 l_ppl_accrual NUMBER; -- bug 10212532
636
637
638 CURSOR c_get_co_formula (v_accrual_plan_id NUMBER)
639 IS
640 SELECT co_formula_id
641 FROM pay_accrual_plans
642 WHERE accrual_plan_id = v_accrual_plan_id;
643
644 CURSOR c_get_oth_adj_type(v_accrual_plan_id NUMBER) IS
645 SELECT information2
646 FROM pay_accrual_plans
647 WHERE accrual_plan_id = v_accrual_plan_id;
648
649 CURSOR c_get_end_date(v_assignment_id NUMBER)
650 IS
651 SELECT LEAST(NVL(pps.actual_termination_date,p_calculation_date), p_calculation_date)
652 FROM per_periods_of_service pps
653 ,per_assignments_f paf
654 WHERE paf.assignment_id = v_assignment_id
655 AND paf.period_of_service_id = pps.period_of_service_id;
656
657 BEGIN
658
659 /* Calculation logic -
660
661 Accruals:
662 (A) Get the total Accrual for FW periods without absences and Other Contrib on Entitlement End date
663 (B) Get the total Accrual for FW periods without absences and Other Contrib on Calculation date
664 (C) Get the total Accrual for All periods without absences and Other Contrib on Entitlement End date
665 (D) Get the total Accrual for All periods without absences and Other Contrib on Calculation date
666
667 Absences:
668 (E) Get the absences between Start Date and entitlement end date
669 (F) Get the absences between Accrual Start Date and Calculation Date
670
671 Other Contribs:
672 (G) Get the Other Contrib between Start Date and entitlement end date
673 (H) Get the Other Contrib between Accrual Start Date and Calculation Date
674
675 Interim Accrual Values:
676 Get the interim accrual values similar to the original function
677 l_int_net_entitlement - entitlement until the entitlement end date
678 l_int_net_accrual - entitlement until the calculation date
679
680 Entitlement = Total Accrual - Absences + Other Contributions
681
682 If Standard Plan
683 l_int_net_entitlement := [(C) - (A)] - (E) + (G)
684 l_int_net_accrual := [(D) - (B)] - [(E) + (F)] + [(G) + (H)]
685 else
686 l_int_net_entitlement := [(A)] - (E) + (G)
687 l_int_net_accrual := [(B)] - [(E) + (F)] + [(G) + (H)]
688
689 Similar to Original function, calculate the net entitlement and net accrual based on
690 Adjustment type and initialised values
691
692 */
693
694 g_debug := hr_utility.debug_enabled;
695 IF g_debug
696 THEN
697 l_proc_name := g_package||'.get_accrual_entitlement_2010';
698 hr_utility.set_location('Entering: '||l_proc_name,1020) ;
699 END IF;
700
701 l_assignment_id := p_assignment_id ;
702 l_plan_id := p_plan_id ;
703 l_payroll_id := p_payroll_id ;
704 l_business_group_id := p_business_group_id ;
705 l_calculation_date := p_calculation_date ;
706
707
708 /* Step (1) - Call Carryover formula and get anniversary date etc. */
709
710 OPEN c_get_co_formula (l_plan_id);
711 FETCH c_get_co_formula INTO l_co_formula_id;
712 CLOSE c_get_co_formula;
713
714 per_accrual_calc_functions.get_carry_over_values(
715 p_co_formula_id => l_co_formula_id
716 ,p_assignment_id => l_assignment_id
717 ,p_calculation_date => l_calculation_date
718 ,p_accrual_plan_id => l_plan_id
719 ,p_business_group_id => l_business_group_id
720 ,p_payroll_id => l_payroll_id
721 ,p_accrual_term => 'AU_FORM'
722 ,p_effective_date => l_accrual_period_start_date
723 ,p_session_date => l_calculation_date
724 ,p_max_carry_over => l_max_co
725 ,p_expiry_date => l_accrual_period_end_date );
726
727 IF l_max_co = 1
728 THEN
729 l_entitlement_period_end_date := l_accrual_period_start_date;
730 ELSE
731 l_entitlement_period_end_date := (l_accrual_period_start_date - 1);
732 END IF;
733
734 /* Step (2) - Call set_accrual_ids and set global values */
735
736 IF (g_plan_id <> l_plan_id OR g_plan_id IS NULL)
737 THEN
738 set_accrual_ids(l_plan_id);
739 END IF;
740
741 l_enrol_start_date := au_get_enrollment_startdate
742 ( p_accrual_plan_id => l_plan_id
743 ,p_assignment_id => l_assignment_id
744 ,p_calculation_date => l_calculation_date) ;
745
746 OPEN c_get_end_date(l_assignment_id);
747 FETCH c_get_end_date INTO l_enrol_end_date;
748 CLOSE c_get_end_date;
749
750 l_enrol_end_date := LEAST(l_enrol_end_date,check_periods(l_payroll_id));
751
752 hr_utility.set_location('l_enrol_start_date '||l_enrol_start_date,1020);
753 hr_utility.set_location('l_enrol_end_date '||l_enrol_end_date,1020);
754
755 /* Step (3) Get the Initialise element values */
756
757 l_initialise_type := 'Leave Accrual Initialise';
758
759 l_accrual_init := (get_leave_initialise(
760 p_assignment_id => l_assignment_id
761 ,p_accrual_plan_id => l_plan_id
762 ,p_calc_end_date => l_calculation_date
763 ,p_initialise_type => l_initialise_type
764 ,p_start_date => l_enrol_start_date
765 ,p_end_date => l_enrol_end_date)
766 );
767
768 l_accrual_ent := (get_leave_initialise(
769 p_assignment_id => l_assignment_id
770 ,p_accrual_plan_id => l_plan_id
771 ,p_calc_end_date => l_calculation_date
772 ,p_initialise_type => l_initialise_type
773 ,p_start_date => l_enrol_start_date
774 ,p_end_date => l_entitlement_period_end_date) /* Orig says l_end_date -1, wonder why :-/ */
775 );
776
777 l_initialise_type := 'Leave Entitlement Initialise';
778 l_entitlement_init := (get_leave_initialise(
779 p_assignment_id => l_assignment_id
780 ,p_accrual_plan_id => l_plan_id
781 ,p_calc_end_date => l_calculation_date
782 ,p_initialise_type => l_initialise_type
783 ,p_start_date => l_enrol_start_date
784 ,p_end_date => l_enrol_end_date)
785 );
786
787 /* Step (5) - If there is a FW Plan - get the FW plan total accrual for FW periods
788 on entitlement end date and calculation date.
789 This value does not reflect absences or Other Contrib */
790
791 IF (g_fw_exists)
792 THEN
793
794 l_fw_start_date := to_date('01/07/2010','DD/MM/YYYY'); /* Hardcoded date, look for FW periods after this date */
795
796 au_adjust_fw_accruals
797 ( p_assignment_id => l_assignment_id
798 ,p_plan_id => l_plan_id
799 ,p_payroll_id => l_payroll_id
800 ,p_business_group_id => l_business_group_id
801 ,p_calculation_date => l_entitlement_period_end_date
802 ,p_start_date => l_fw_start_date
803 ,p_net_ent => l_fw_leave_end_ent);
804
805
806 au_adjust_fw_accruals
807 ( p_assignment_id => l_assignment_id
808 ,p_plan_id => l_plan_id
809 ,p_payroll_id => l_payroll_id
810 ,p_business_group_id => l_business_group_id
811 ,p_calculation_date => l_calculation_date
812 ,p_start_date => l_fw_start_date
813 ,p_net_ent => l_fw_leave_calc_date);
814
815 ELSE
816 l_fw_leave_end_ent := 0 ;
817 l_fw_leave_calc_date := 0 ;
818
819 END IF;
820
821 /* Step (6) - For the standard Plan - get the total accrual on entitlement end date
822 and calculation date.
823 This value does not reflect absences or Other Contrib */
824
825 /* bug 10212532 - Assumming Paid Parental Leaves are recorded against non-FW accrual plan only */
826
827 IF (g_is_fw_plan = FALSE)
828 THEN
829
830 /* Fetch Net accruals till entitlement end date */
831
832 per_accrual_calc_functions.get_accrual(
833 p_assignment_id => l_assignment_id
834 ,p_calculation_date => l_entitlement_period_end_date
835 ,p_plan_id => l_plan_id
836 ,p_business_group_id => l_business_group_id
837 ,p_payroll_id => l_payroll_id
838 ,p_start_date => l_start_date
839 ,p_end_date => l_end_date
840 ,p_accrual_end_date => l_accrual_end_date
841 ,p_accrual => l_leave_end_ent -- at end of entitlement perod
842 ) ;
843
844 -- bug 10212532
845 l_ppl_ent := get_paid_parental_leave(
846 p_assignment_id => l_assignment_id
847 ,p_plan_id => l_plan_id
848 ,p_payroll_id => l_payroll_id
849 ,p_business_group_id => l_business_group_id
850 ,p_start_date => l_start_date
851 ,p_end_date => l_entitlement_period_end_date);
852
853 /* Fetch Net accruals till entitlement end date */
854
855 per_accrual_calc_functions.get_accrual(
856 p_assignment_id => l_assignment_id
857 ,p_calculation_date => l_calculation_date
858 ,p_plan_id => l_plan_id
859 ,p_business_group_id => l_business_group_id
860 ,p_payroll_id => l_payroll_id
861 ,p_start_date => l_start_date
862 ,p_end_date => l_end_date
863 ,p_accrual_end_date => l_accrual_end_date
864 ,p_accrual => l_leave_calc_date -- at calculation date
865 ) ;
866
867 -- bug 10212532
868 l_ppl_accrual := get_paid_parental_leave(
869 p_assignment_id => l_assignment_id
870 ,p_plan_id => l_plan_id
871 ,p_payroll_id => l_payroll_id
872 ,p_business_group_id => l_business_group_id
873 ,p_start_date => l_accrual_period_start_date
874 ,p_end_date => l_calculation_date);
875
876 ELSE
877
878 l_leave_end_ent := 0;
879 l_leave_calc_date := 0;
880
881 END IF;
882
883 /* Step (7) - Calculate the net entitlement and accrual sans absence and Other contrib.
884 */
885
886 IF g_is_fw_plan
887 THEN
888 l_accrual_ent_period := l_fw_leave_end_ent;
889 l_accrual_acc_period := l_fw_leave_calc_date;
890 l_start_date := GREATEST(l_enrol_start_date,l_fw_start_date);
891 ELSE
892 l_accrual_ent_period := l_leave_end_ent - l_fw_leave_end_ent - l_ppl_ent; -- 10212532 l_ppl_ent
893 l_accrual_acc_period := (l_leave_calc_date - l_fw_leave_calc_date) - l_ppl_ent - l_ppl_accrual ; -- 10212532 l_ppl_ent, l_ppl_accrual
894 l_start_date := l_enrol_start_date;
895 END IF;
896
897
898 /* Step (8) - Get the absences and Other Contrib for the Plan being queried
899 */
900
901 l_abs_ent_period := per_accrual_calc_functions.get_absence
902 (p_assignment_id => l_assignment_id
903 ,p_plan_id => l_plan_id
904 ,p_calculation_date => l_accrual_period_start_date - 1
905 ,p_start_date => l_start_date);
906
907 l_abs_acc_period := per_accrual_calc_functions.get_absence
908 (p_assignment_id => l_assignment_id
909 ,p_plan_id => l_plan_id
910 ,p_calculation_date => l_calculation_date
911 ,p_start_date => l_accrual_period_start_date);
912
913 l_other_ent_period := per_accrual_calc_functions.get_other_net_contribution
914 (p_assignment_id => l_assignment_id
915 ,p_plan_id => l_plan_id
916 ,p_calculation_date => l_accrual_period_start_date - 1
917 ,p_start_date => l_start_date);
918
919 l_other_acc_period := per_accrual_calc_functions.get_other_net_contribution
920 (p_assignment_id => l_assignment_id
921 ,p_plan_id => l_plan_id
922 ,p_calculation_date => l_calculation_date
923 ,p_start_date => l_accrual_period_start_date);
924
925
926 /* Step (9) -
927 Set up Interim values of accruals similar to the original function.
928 Net Entl := Accruals - Absences + Other Contrib
929
930 l_int_net_entitlement = Has Net Entl until Entitlement Date
931 l_int_net_accrual = Has Net Entl until Calculation Date
932
933 */
934
935
936 l_int_net_entitlement := l_accrual_ent_period - l_abs_ent_period + l_other_ent_period;
937 l_int_net_accrual := l_accrual_acc_period - (l_abs_ent_period + l_abs_acc_period)
938 + (l_other_ent_period + l_other_acc_period);
939
940 IF g_debug
941 THEN
942 hr_utility.set_location('l_fw_leave_end_ent '||l_fw_leave_end_ent,1020);
943 hr_utility.set_location('l_fw_leave_calc_date '||l_fw_leave_calc_date,1020);
944 hr_utility.set_location('l_leave_end_ent '||l_leave_end_ent,1020);
945 hr_utility.set_location('l_leave_calc_date '||l_leave_calc_date,1020);
946 hr_utility.set_location('l_accrual_ent_period '||l_accrual_ent_period,1020);
947 hr_utility.set_location('l_accrual_acc_period '||l_accrual_acc_period,1020);
948 hr_utility.set_location('l_abs_ent_period '||l_abs_ent_period,1020);
949 hr_utility.set_location('l_abs_acc_period '||l_abs_acc_period,1020);
950 hr_utility.set_location('l_other_ent_period '||l_other_ent_period,1020);
951 hr_utility.set_location('l_other_acc_period '||l_other_acc_period,1020);
952 hr_utility.set_location('l_int_net_accrual '||l_int_net_accrual,1020);
953 hr_utility.set_location('l_int_net_entitlement '||l_int_net_entitlement,1020);
954 END IF;
955
956
957 /* Step (10) - Based on Other Adjustment Types,
958 Adjust the absences and Other Contrubutions in the Accrual Period.
959 Add Initialise element values
960 */
961
962
963 OPEN c_get_oth_adj_type (l_plan_id);
964 FETCH c_get_oth_adj_type INTO l_other_adj_type;
965 CLOSE c_get_oth_adj_type;
966
967 IF (NVL(l_other_adj_type,'E') = 'A')
968 THEN
969
970 l_net_entitlement := GREATEST(l_int_net_entitlement - l_abs_acc_period
971 + l_entitlement_init
972 + l_accrual_ent,0);
973
974 ELSIF (NVL(l_other_adj_type,'E') = 'E')
975 THEN
976
977 l_net_entitlement := GREATEST(l_int_net_entitlement - l_abs_acc_period
978 + l_entitlement_init
979 + l_accrual_ent
980 + l_other_acc_period,0);
981 END IF;
982
983 l_net_accrual := GREATEST(l_int_net_accrual + l_accrual_init
984 - l_net_entitlement
985 + l_entitlement_init,0);
986
987
988 p_net_accrual := ROUND(NVL(l_net_accrual, 0), 3);
989 p_net_entitlement := ROUND(NVL(l_net_entitlement, 0), 3);
990 p_calc_start_date := l_enrol_start_date;
991 p_last_accrual := l_enrol_end_date;
992 p_next_period_end := l_accrual_period_end_date;
993
994 IF g_debug
995 THEN
996 hr_utility.set_location('p_net_accrual '||p_net_accrual,1020);
997 hr_utility.set_location('p_net_entitlement '||p_net_entitlement,1020);
998 hr_utility.set_location('p_calc_start_date '||p_calc_start_date,1020);
999 hr_utility.set_location('p_last_accrual '||p_last_accrual,1020);
1000 hr_utility.set_location('p_next_period_end '||p_next_period_end,1020);
1001 hr_utility.set_location('Leaving '||l_proc_name,20);
1002 END IF;
1003
1004
1005 RETURN(0);
1006
1007
1008 END get_accrual_entitlement_2010;
1009
1010
1011 /*
1012 --------------------------------------------------------------------
1013 Name : get_net_accrual_2010
1014 Type : Function
1015 Access: Private
1016 Description: The principal function to calculate accruals
1017 is called from main function if FW Plan or Standard
1018 Plan with associated FW Plan.
1019 --------------------------------------------------------------------
1020 */
1021
1022 FUNCTION get_net_accrual_2010
1023 (p_assignment_id IN NUMBER
1024 ,p_payroll_id IN NUMBER
1025 ,p_business_group_id IN NUMBER
1026 ,p_plan_id IN NUMBER
1027 ,p_calculation_date IN DATE)
1028 RETURN NUMBER
1029 IS
1030
1031 l_proc_name VARCHAR2(80) ;
1032 l_assignment_id NUMBER ;
1033 l_plan_id NUMBER ;
1034 l_payroll_id NUMBER ;
1035 l_business_group_id NUMBER ;
1036 l_calculation_date DATE ;
1037 l_start_date DATE ;
1038 l_end_date DATE ;
1039 l_accrual_end_date DATE ;
1040 l_accrual NUMBER ;
1041 l_net_entitlement NUMBER ;
1042 l_fw_net_entitlement NUMBER;
1043 l_fw_start_date DATE;
1044
1045 --------------------------------------
1046 -- Bug No : 2132299 Start
1047 --------------------------------------
1048
1049 l_initialise_type VARCHAR2(100);
1050 l_accrual_init NUMBER ;
1051 l_entitlement_init NUMBER;
1052
1053 l_absence NUMBER;
1054 l_other NUMBER;
1055
1056 l_ppl_accrual number; -- bug 10212532
1057
1058 BEGIN
1059
1060 g_debug := hr_utility.debug_enabled;
1061 IF g_debug
1062 THEN
1063 l_proc_name := g_package||'get_net_accrual_2010';
1064 hr_utility.set_location('Entering function '||l_proc_name,1050);
1065 END IF;
1066
1067 l_assignment_id := p_assignment_id ;
1068 l_plan_id := p_plan_id ;
1069 l_payroll_id := p_payroll_id ;
1070 l_business_group_id := p_business_group_id ;
1071 l_calculation_date := p_calculation_date ;
1072 l_start_date := NULL ;
1073 l_end_date := NULL ;
1074 l_accrual_end_date := NULL ;
1075 l_accrual := NULL ;
1076 l_net_entitlement := 0 ;
1077 l_fw_net_entitlement := 0;
1078 l_fw_start_date := to_date('01/07/2010','DD/MM/YYYY');
1079
1080 IF (g_plan_id <> l_plan_id OR g_plan_id IS NULL)
1081 THEN
1082 set_accrual_ids(l_plan_id);
1083 END IF;
1084
1085 IF g_fw_exists
1086 THEN
1087
1088 au_adjust_fw_accruals
1089 ( p_assignment_id => l_assignment_id
1090 ,p_plan_id => l_plan_id
1091 ,p_payroll_id => l_payroll_id
1092 ,p_business_group_id => l_business_group_id
1093 ,p_calculation_date => l_calculation_date
1094 ,p_start_date => l_fw_start_date
1095 ,p_net_ent => l_fw_net_entitlement);
1096 END IF;
1097
1098
1099 per_accrual_calc_functions.get_net_accrual(
1100 p_assignment_id => l_assignment_id
1101 ,p_plan_id => l_plan_id
1102 ,p_payroll_id => l_payroll_id
1103 ,p_business_group_id => l_business_group_id
1104 ,p_calculation_date => l_calculation_date
1105 ,p_start_date => l_start_date
1106 ,p_end_date => l_end_date
1107 ,p_accrual_end_date => l_accrual_end_date
1108 ,p_accrual => l_net_entitlement
1109 ,p_net_entitlement => l_accrual) ;
1110
1111 -- bug 10212532 paid parental leave adjustment
1112 l_ppl_accrual := get_paid_parental_leave(
1113 p_assignment_id => l_assignment_id
1114 ,p_plan_id => l_plan_id
1115 ,p_payroll_id => l_payroll_id
1116 ,p_business_group_id => l_business_group_id
1117 ,p_start_date => null
1118 ,p_end_date => l_calculation_date);
1119
1120
1121 l_initialise_type := 'Leave Accrual Initialise';
1122 l_accrual_init := (get_leave_initialise(
1123 p_assignment_id => l_assignment_id
1124 ,p_accrual_plan_id => l_plan_id
1125 ,p_calc_end_date => l_calculation_date
1126 ,p_initialise_type => l_initialise_type
1127 ,p_start_date => l_start_date
1128 ,p_end_date => l_end_date)
1129 );
1130
1131 l_initialise_type := 'Leave Entitlement Initialise';
1132 l_entitlement_init := (get_leave_initialise(
1133 p_assignment_id => l_assignment_id
1134 ,p_accrual_plan_id => l_plan_id
1135 ,p_calc_end_date => l_calculation_date
1136 ,p_initialise_type => l_initialise_type
1137 ,p_start_date => l_start_date
1138 ,p_end_date => l_end_date)
1139 );
1140
1141
1142 l_absence := per_accrual_calc_functions.get_absence
1143 (p_assignment_id => l_assignment_id
1144 ,p_plan_id => l_plan_id
1145 ,p_calculation_date => l_calculation_date
1146 ,p_start_date => l_start_date);
1147
1148 l_other := per_accrual_calc_functions.get_other_net_contribution
1149 (p_assignment_id => l_assignment_id
1150 ,p_plan_id => l_plan_id
1151 ,p_calculation_date => l_calculation_date
1152 ,p_start_date => l_start_date);
1153
1154 IF g_is_fw_plan = FALSE
1155 THEN
1156 l_net_entitlement := (l_net_entitlement - l_fw_net_entitlement) + l_entitlement_init + l_accrual_init - l_absence + l_other
1157 - l_ppl_accrual; -- bug 10212532 l_ppl_accrual
1158 ELSE
1159 l_net_entitlement := l_fw_net_entitlement + l_entitlement_init + l_accrual_init - l_absence + l_other;
1160 END IF;
1161
1162 IF g_debug
1163 THEN
1164 hr_utility.set_location('l_net_entitlement '||l_net_entitlement,1050);
1165 hr_utility.set_location('Leaving function '||l_proc_name,1050);
1166 END IF;
1167
1168 RETURN l_net_entitlement;
1169
1170 END get_net_accrual_2010;
1171
1172 /* Bug 9950136 - End Changes */
1173
1174
1175 /*---------------------------------------------------------------------
1176 Name : get_accrual_plan_by_category
1177 Purpose : To retrieve accrual plan id for designated category
1178 Returns : accrual_plan_id if successful, null otherwise
1179 ---------------------------------------------------------------------*/
1180
1181 FUNCTION get_accrual_plan_by_category
1182 (p_assignment_id IN NUMBER
1183 ,p_effective_date IN DATE
1184 ,p_plan_category IN VARCHAR2) RETURN NUMBER IS
1185
1186 l_proc VARCHAR2(72) := g_package||'get_accrual_plan_by_category' ;
1187 l_accrual_plan_id NUMBER ;
1188 l_dummy NUMBER ;
1189
1190 /* Bug 9950136 - Modified cursor to pick the non-FW Standard Plan Accrual ID
1191 */
1192
1193 CURSOR csr_get_accrual_plan_id(p_assignment_id NUMBER
1194 ,p_effective_date DATE
1195 ,p_plan_category VARCHAR2) IS
1196 SELECT pap.accrual_plan_id
1197 FROM pay_accrual_plans pap,
1198 pay_element_entries_f pee,
1199 pay_element_links_f pel,
1200 pay_element_types_f pet
1201 WHERE pee.assignment_id = p_assignment_id
1202 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
1203 AND p_effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date /*Added for 8482224*/
1204 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date /*Added for 8482224*/
1205 AND pel.element_link_id = pee.element_link_id
1206 AND pel.element_type_id = pet.element_type_id
1207 AND pap.accrual_plan_element_type_id = pet.element_type_id
1208 AND pap.accrual_category = p_plan_category
1209 AND NVL(pap.information3,'N') = 'N'; /* Bug 9950136 */
1210
1211 BEGIN
1212 hr_utility.set_location(' Entering::'||l_proc,5);
1213
1214 OPEN csr_get_accrual_plan_id(p_assignment_id, p_effective_date, p_plan_category) ;
1215
1216 FETCH csr_get_accrual_plan_id INTO l_accrual_plan_id;
1217
1218 IF csr_get_accrual_plan_id%NOTFOUND
1219 THEN
1220 CLOSE csr_get_accrual_plan_id;
1221 hr_utility.set_location('Plan Not Found '||l_proc,10);
1222 hr_utility.set_message(801, 'HR_AU_ACCRUAL_PLAN_NOT_FOUND');
1223 hr_utility.raise_error;
1224 end if ;
1225
1226 FETCH csr_get_accrual_plan_id INTO l_dummy ;
1227
1228 IF csr_get_accrual_plan_id%FOUND
1229 THEN
1230 CLOSE csr_get_accrual_plan_id;
1231 hr_utility.set_location('Enrolled in Multiple Plans '||l_proc,15);
1232 hr_utility.set_message(801, 'HR_AU_TOO_MANY_ACCRUAL_PLANS');
1233 hr_utility.raise_error;
1234 END IF;
1235
1236 CLOSE csr_get_accrual_plan_id;
1237 hr_utility.set_location('Leaving:'||l_proc,20);
1238
1239 RETURN l_accrual_plan_id;
1240
1241 -- EXCEPTION
1242 -- WHEN OTHERS THEN
1243 -- hr_utility.set_location('Leaving:'||l_proc,99);
1244 -- RETURN NULL;
1245 END get_accrual_plan_by_category;
1246
1247
1248 --
1249 -- get_net_accrual
1250 --
1251 -- This function is a wrapper for the
1252 -- per_accrual_calc_functions.get_net_accrual procedure. The
1253 -- wrapper is required so that a FastFormula function can be
1254 -- registered for use in formulas.
1255 --
1256
1257 FUNCTION get_net_accrual
1258 (p_assignment_id IN NUMBER
1259 ,p_payroll_id IN NUMBER
1260 ,p_business_group_id IN NUMBER
1261 ,p_plan_id IN NUMBER
1262 ,p_calculation_date IN DATE)
1263 RETURN NUMBER IS
1264
1265 l_proc VARCHAR2(72) := g_package||'get_net_accrual';
1266 l_assignment_id NUMBER ;
1267 l_plan_id NUMBER ;
1268 l_payroll_id NUMBER ;
1269 l_business_group_id NUMBER ;
1270 l_calculation_date DATE ;
1271 l_start_date DATE ;
1272 l_end_date DATE ;
1273 l_accrual_end_date DATE ;
1274 l_accrual NUMBER ;
1275 l_net_entitlement NUMBER ;
1276
1277 --------------------------------------
1278 -- Bug No : 2132299 Start
1279 --------------------------------------
1280
1281 l_initialise_type VARCHAR2(100);
1282 l_accrual_init NUMBER ;
1283 l_entitlement_init NUMBER;
1284
1285 --------------------------------------
1286 -- Bug No : 2132299 End
1287 --------------------------------------
1288
1289 l_ppl_accrual number;
1290
1291 BEGIN
1292 hr_utility.set_location('Entering: '||l_proc,10) ;
1293 l_assignment_id := p_assignment_id ;
1294 l_plan_id := p_plan_id ;
1295 l_payroll_id := p_payroll_id ;
1296 l_business_group_id := p_business_group_id ;
1297 l_calculation_date := p_calculation_date ;
1298 l_start_date := NULL ;
1299 l_end_date := NULL ;
1300 l_accrual_end_date := NULL ;
1301 l_accrual := NULL ;
1302 l_net_entitlement := NULL ;
1303
1304 /* Bug 9950136 - Call set_accrual_ids.
1305 If Plan is a FW Plan or Standard Plan with associated FW Plan,
1306 call new FW function wrapper */
1307
1308 IF (g_plan_id <> l_plan_id OR g_plan_id IS NULL)
1309 THEN
1310 set_accrual_ids(l_plan_id);
1311 END IF;
1312
1313 IF g_fw_exists
1314 THEN
1315 l_net_entitlement := get_net_accrual_2010
1316 (p_assignment_id => l_assignment_id
1317 ,p_payroll_id => l_payroll_id
1318 ,p_business_group_id => l_business_group_id
1319 ,p_plan_id => l_plan_id
1320 ,p_calculation_date => l_calculation_date);
1321
1322 RETURN l_net_entitlement;
1323
1324 END IF;
1325
1326 /* End Bug 9950136 */
1327
1328 per_accrual_calc_functions.get_net_accrual(
1329 p_assignment_id => l_assignment_id
1330 ,p_plan_id => l_plan_id
1331 ,p_payroll_id => l_payroll_id
1332 ,p_business_group_id => l_business_group_id
1333 ,p_calculation_date => l_calculation_date
1334 ,p_start_date => l_start_date
1335 ,p_end_date => l_end_date
1336 ,p_accrual_end_date => l_accrual_end_date
1337 ,p_accrual => l_accrual
1338 ,p_net_entitlement => l_net_entitlement) ;
1339
1340 -- bug 10212532 paid parental leave adjustment
1341 l_ppl_accrual := get_paid_parental_leave(
1342 p_assignment_id => l_assignment_id
1343 ,p_plan_id => l_plan_id
1344 ,p_payroll_id => l_payroll_id
1345 ,p_business_group_id => l_business_group_id
1346 ,p_start_date => null
1347 ,p_end_date => l_calculation_date);
1348
1349 --------------------------------------
1350 -- Bug No : 2132299 Start
1351 --------------------------------------
1352
1353 l_initialise_type := 'Leave Accrual Initialise';
1354 l_accrual_init := (get_leave_initialise(
1355 p_assignment_id => l_assignment_id
1356 ,p_accrual_plan_id => l_plan_id
1357 ,p_calc_end_date => l_calculation_date
1358 ,p_initialise_type => l_initialise_type
1359 ,p_start_date => l_start_date
1360 ,p_end_date => l_end_date)
1361 );
1362
1363 l_initialise_type := 'Leave Entitlement Initialise';
1364 l_entitlement_init := (get_leave_initialise(
1365 p_assignment_id => l_assignment_id
1366 ,p_accrual_plan_id => l_plan_id
1367 ,p_calc_end_date => l_calculation_date
1368 ,p_initialise_type => l_initialise_type
1369 ,p_start_date => l_start_date
1370 ,p_end_date => l_end_date)
1371 );
1372
1373
1374
1375 l_net_entitlement := l_net_entitlement + l_entitlement_init + l_accrual_init - l_ppl_accrual ; -- bug 10212532 l_ppl_accrual
1376
1377 --------------------------------------
1378 -- Bug No : 2132299 End
1379 --------------------------------------
1380
1381 hr_utility.set_location('Leaving '||l_proc,20);
1382 RETURN l_net_entitlement ;
1383
1384 END get_net_accrual ;
1385
1386
1387
1388 --------------------------------------------------------------
1389 --
1390 -- get_accrual_entitlement
1391 --
1392 -- This function is required mainly by the AU local library
1393 -- and will return the net accrual and net entitlement for a
1394 -- given person on a given day.
1395 --
1396 -- These values will be displayed in the forms PAYWSACV and
1397 -- PAYWSEAD.
1398 --
1399 --------------------------------------------------------------
1400
1401 FUNCTION get_accrual_entitlement
1402 (p_assignment_id IN NUMBER
1403 ,p_payroll_id IN NUMBER
1404 ,p_business_group_id IN NUMBER
1405 ,p_plan_id IN NUMBER
1406 ,p_calculation_date IN DATE
1407 ,p_net_accrual OUT NOCOPY NUMBER
1408 ,p_net_entitlement OUT NOCOPY NUMBER
1409 ,p_calc_start_date OUT NOCOPY DATE
1410 ,p_last_accrual OUT NOCOPY DATE
1411 ,p_next_period_end OUT NOCOPY DATE)
1412 RETURN NUMBER IS
1413
1414
1415 -- The stages of the calculation are as follows
1416 --
1417 -- 1: Find the entitlement end date using the get_carryover_values
1418 -- core function - ie the last day of the entitlement period
1419 --
1420 -- 2: Find net leave at entitlement end date using the core
1421 -- get_net_accrual Function.
1422 --
1423 -- 3: Find the total net leave up to the calculation date using
1424 -- the core get_net_accrual function.
1425 --
1426 -- 4: Find the number of hours taken during the accrual period
1427 -- i.e. date from step 1 plus 1 day until calc date using the
1428 -- core get_absence function
1429 --
1430 -- Added Step 4a for Bug 6449311
1431 -- 4a Find the Net Contribution of other elements using
1432 -- per_accrual_calc_functions.get_other_net_contribution
1433 -- Added Step 4b for Bug 9444169
1434 -- 4b If the Other Adjustments type is 'Entitlement', 'Other Net Contributions'
1435 -- are added to Net Entitlement.
1436 --
1437 -- 5: Find Leave Accrual Initialise during period
1438 --
1439 -- 6: Find Leave Entitlement Initialise during period
1440 --
1441 -- 7: Net entitlement = greater ((step 2 - step 4 + step 6 + step 4.1), 0)
1442 --
1443 -- 8: Net accrual = (step 3 + step 5 - step 7 + step 6)
1444 --
1445
1446
1447 l_proc VARCHAR2(72) := g_package||'.get_accrual_entitlement';
1448 l_assignment_id NUMBER ;
1449 l_plan_id NUMBER ;
1450 l_payroll_id NUMBER ;
1451 l_business_group_id NUMBER ;
1452 l_calculation_date DATE ;
1453 l_start_date DATE ;
1454 l_end_date DATE ;
1455 l_accrual_end_date DATE ;
1456 l_accrual_period_start_date DATE ;
1457 l_accrual_period_end_date DATE ;
1458 l_entitlement_period_end_date DATE ;
1459 l_net_accrual NUMBER ;
1460 l_net_entitlement NUMBER ;
1461 l_co_formula_id NUMBER ;
1462 l_max_co NUMBER ;
1463 l_leave_end_ent NUMBER ;
1464 l_leave_calc_date NUMBER ;
1465 l_accrual NUMBER ;
1466 l_accrual_absences NUMBER ;
1467 l_total_ent_adj NUMBER ;
1468 l_other NUMBER ; -- Bug 9444169
1469 l_other_adj_type VARCHAR2(10); -- Bug 9444169
1470
1471 l_dummy NUMBER ; /* Bug 9950136 */
1472 ---------------------------------------------
1473 -- Bug #1942971 -- Start
1474 ---------------------------------------------
1475 l_initialise_type VARCHAR2(100);
1476 l_accrual_init NUMBER ;
1477 l_accrual_ent NUMBER ;
1478 l_entitlement_init NUMBER ;
1479 ---------------------------------------------
1480 -- Bug #1942971 -- End
1481 ---------------------------------------------
1482
1483 cursor c_get_co_formula (v_accrual_plan_id number) is
1484 select co_formula_id
1485 from pay_accrual_plans
1486 where accrual_plan_id = v_accrual_plan_id;
1487
1488 -- Start of Bug 9444169
1489 cursor c_get_oth_adj_type(v_accrual_plan_id number) is
1490 select information2
1491 from pay_accrual_plans
1492 where accrual_plan_id = v_accrual_plan_id;
1493 -- End of Bug 9444169
1494
1495 l_ppl_ent number := 0; -- 10212532
1496 l_ppl_accrual number := 0; -- 10212532
1497
1498 BEGIN
1499
1500 g_debug := hr_utility.debug_enabled;
1501 hr_utility.set_location('Entering: '||l_proc,10) ;
1502 l_assignment_id := p_assignment_id ;
1503 l_plan_id := p_plan_id ;
1504 l_payroll_id := p_payroll_id ;
1505 l_business_group_id := p_business_group_id ;
1506 l_calculation_date := p_calculation_date ;
1507
1508
1509
1510 /* Bug 9950136 - Call set_accrual_ids.
1511 If Plan is a FW Plan or Standard Plan with associated FW Plan,
1512 call new FW function wrapper */
1513
1514 IF (g_plan_id <> l_plan_id OR g_plan_id IS NULL)
1515 THEN
1516 set_accrual_ids(l_plan_id);
1517 END IF;
1518
1519 IF g_fw_exists
1520 THEN
1521
1522 l_dummy := get_accrual_entitlement_2010
1523 (p_assignment_id => l_assignment_id
1524 ,p_payroll_id => l_payroll_id
1525 ,p_business_group_id => l_business_group_id
1526 ,p_plan_id => l_plan_id
1527 ,p_calculation_date => l_calculation_date
1528 ,p_net_accrual => l_net_accrual
1529 ,p_net_entitlement => l_net_entitlement
1530 ,p_calc_start_date => l_start_date
1531 ,p_last_accrual => l_accrual_end_date
1532 ,p_next_period_end => l_accrual_period_end_date);
1533
1534 p_net_accrual := ROUND(NVL(l_net_accrual, 0), 3);
1535 p_net_entitlement := ROUND(NVL(l_net_entitlement, 0), 3);
1536 p_calc_start_date := l_start_date;
1537 p_last_accrual := l_accrual_end_date;
1538 p_next_period_end := l_accrual_period_end_date;
1539
1540 IF g_debug
1541 THEN
1542 hr_utility.set_location('Leaving '||l_proc,20);
1543 END IF;
1544
1545 RETURN(0);
1546 END IF;
1547
1548 /* End Bug 9950136 */
1549
1550 --
1551 -- Step 1 Find entitlement end date
1552 -- first get the carryover formula then call it
1553 -- to get the prev and next anniversary dates.
1554 -- Entitlement end date and accrual end dates are
1555 -- actually the day before the anniversary dates.
1556 --
1557
1558 open c_get_co_formula (l_plan_id);
1559 fetch c_get_co_formula into l_co_formula_id;
1560 close c_get_co_formula;
1561
1562
1563 per_accrual_calc_functions.get_carry_over_values(
1564 p_co_formula_id => l_co_formula_id
1565 ,p_assignment_id => l_assignment_id
1566 ,p_calculation_date => l_calculation_date
1567 ,p_accrual_plan_id => l_plan_id
1568 ,p_business_group_id => l_business_group_id
1569 ,p_payroll_id => l_payroll_id
1570 ,p_accrual_term => 'AU_FORM'
1571 ,p_effective_date => l_accrual_period_start_date
1572 ,p_session_date => l_calculation_date
1573 ,p_max_carry_over => l_max_co
1574 ,p_expiry_date => l_accrual_period_end_date );
1575
1576
1577
1578 --
1579 -- Step two find the Net leave at entitlement end date
1580 --
1581 -- Before first anniversary date accrual_period_start_date = start_date
1582 -- in this case l_max_co will be set to 1
1583 --
1584
1585 if l_max_co = 1 then
1586 l_entitlement_period_end_date := l_accrual_period_start_date;
1587 else
1588 l_entitlement_period_end_date := (l_accrual_period_start_date - 1);
1589 end if;
1590
1591 per_accrual_calc_functions.get_net_accrual(
1592 p_assignment_id => l_assignment_id
1593 ,p_plan_id => l_plan_id
1594 ,p_payroll_id => l_payroll_id
1595 ,p_business_group_id => l_business_group_id
1596 ,p_calculation_date => l_entitlement_period_end_date
1597 ,p_start_date => l_start_date
1598 ,p_end_date => l_end_date
1599 ,p_accrual_end_date => l_accrual_end_date
1600 ,p_accrual => l_accrual
1601 ,p_net_entitlement => l_leave_end_ent) ; -- at end of entitlement perod
1602
1603 l_ppl_ent := get_paid_parental_leave(
1604 p_assignment_id => l_assignment_id
1605 ,p_plan_id => l_plan_id
1606 ,p_payroll_id => l_payroll_id
1607 ,p_business_group_id => l_business_group_id
1608 ,p_start_date => l_start_date
1609 ,p_end_date => l_entitlement_period_end_date);
1610 --
1611 -- Step three find the Net leave at the calculation_date
1612 --
1613
1614 per_accrual_calc_functions.get_net_accrual(
1615 p_assignment_id => l_assignment_id
1616 ,p_plan_id => l_plan_id
1617 ,p_payroll_id => l_payroll_id
1618 ,p_business_group_id => l_business_group_id
1619 ,p_calculation_date => l_calculation_date
1620 ,p_start_date => l_start_date
1621 ,p_end_date => l_end_date
1622 ,p_accrual_end_date => l_accrual_end_date
1623 ,p_accrual => l_accrual
1624 ,p_net_entitlement => l_leave_calc_date) ; -- at calculation date
1625
1626 l_ppl_accrual := get_paid_parental_leave(
1627 p_assignment_id => l_assignment_id
1628 ,p_plan_id => l_plan_id
1629 ,p_payroll_id => l_payroll_id
1630 ,p_business_group_id => l_business_group_id
1631 ,p_start_date => l_accrual_period_start_date
1632 ,p_end_date => l_calculation_date);
1633
1634 --
1635 -- Step four find out the numder of hours taken during the accrual period
1636 --
1637
1638 l_accrual_absences := per_accrual_calc_functions.get_absence(
1639 p_assignment_id => l_assignment_id,
1640 p_plan_id => l_plan_id,
1641 p_start_date => l_accrual_period_start_date,
1642 p_calculation_date => l_calculation_date );
1643
1644 /*Bug 6449311 Begin */
1645 --
1646 -- Step 4a find out the contribution from other elements
1647 --
1648 l_other := per_accrual_calc_functions.get_other_net_contribution(
1649 p_assignment_id => l_assignment_id,
1650 p_plan_id => l_plan_id,
1651 p_start_date => l_accrual_period_start_date,
1652 p_calculation_date => l_calculation_date );
1653
1654 /*Bug 6449311 End */
1655
1656 /* 8301730 - Removed the above call to per_accrual_calc_functions.get_other_net_contribution made
1657 in bug 6449311 */
1658
1659 -------------------------------------------------------------------------------------------
1660 --- Bug #1942971 ----- Start
1661 -------------------------------------------------------------------------------------------
1662 --
1663 -- Step 5 : Find the Leave Accrual Initialise for the period(5a). Also get leave accrual initialise
1664 -- at the end of entitlement date(5b).
1665 --
1666 l_initialise_type := 'Leave Accrual Initialise';
1667 l_accrual_init := (get_leave_initialise(
1668 p_assignment_id => l_assignment_id
1669 ,p_accrual_plan_id => l_plan_id
1670 ,p_calc_end_date => l_calculation_date
1671 ,p_initialise_type => l_initialise_type
1672 ,p_start_date => l_start_date
1673 ,p_end_date => l_end_date)
1674 );
1675
1676 l_accrual_ent := (get_leave_initialise(
1677 p_assignment_id => l_assignment_id
1678 ,p_accrual_plan_id => l_plan_id
1679 ,p_calc_end_date => l_calculation_date
1680 ,p_initialise_type => l_initialise_type
1681 ,p_start_date => l_start_date
1682 ,p_end_date => l_entitlement_period_end_date - 1)
1683 );
1684
1685 --
1686 -- Step 6 : Find the Leave Entitlement Initialise for the period.
1687 --
1688 l_initialise_type := 'Leave Entitlement Initialise';
1689 l_entitlement_init := (get_leave_initialise(
1690 p_assignment_id => l_assignment_id
1691 ,p_accrual_plan_id => l_plan_id
1692 ,p_calc_end_date => l_calculation_date
1693 ,p_initialise_type => l_initialise_type
1694 ,p_start_date => l_start_date
1695 ,p_end_date => l_end_date)
1696 );
1697
1698 /* Bug 9444169 */
1699 open c_get_oth_adj_type (l_plan_id);
1700 fetch c_get_oth_adj_type into l_other_adj_type;
1701 close c_get_oth_adj_type;
1702
1703 /*Bug 6449311 l_other (Step 4a) added for calculating l_net_entitlement */
1704 --
1705 -- Step 7: Net entitlement = greater ((step 2 - step 4 + step 6 + Step 5b +Step 4a), 0)
1706 --
1707 /* Start of Bug 9444169 */
1708 -- Modified Step 7: Net entitlement = greater ((step 2 - step 4 + step 6 + Step 5b), 0)
1709 if (nvl(l_other_adj_type,'E') = 'A') then
1710 l_net_entitlement := greatest( (l_leave_end_ent - l_accrual_absences + l_entitlement_init + l_accrual_ent - l_ppl_ent) , 0); --bug 10212532 l_ppl_ent
1711
1712 elsif (nvl(l_other_adj_type,'E') = 'E') then
1713 l_net_entitlement := greatest( (l_leave_end_ent - l_accrual_absences + l_entitlement_init + l_accrual_ent + l_other - l_ppl_ent) , 0); --bug 10212532 l_ppl_ent
1714 end if;
1715 /* End of Bug 9444169 */
1716 --
1717 -- Step 8: Net accrual = greater((step 3 + step 5 - step 7 + step 6),0)
1718 --
1719 --l_net_accrual := greatest((l_leave_calc_date + l_accrual_init - l_net_entitlement + l_entitlement_init),0);
1720 l_net_accrual := (l_leave_calc_date + l_accrual_init - l_net_entitlement + l_entitlement_init - l_ppl_ent - l_ppl_accrual); --bug 10212532 l_ppl_ent, l_ppl_accrual
1721
1722
1723 --
1724 -- set up return values
1725 --
1726
1727 p_net_accrual := round(nvl(l_net_accrual, 0), 3);
1728 p_net_entitlement := round(nvl(l_net_entitlement, 0), 3);
1729 p_calc_start_date := l_start_date;
1730 p_last_accrual := l_accrual_end_date;
1731 p_next_period_end := l_accrual_period_end_date - 1;
1732
1733 hr_utility.set_location('Leaving '||l_proc,20);
1734 RETURN (0);
1735
1736 -- EXCEPTION
1737 -- WHEN OTHERS
1738 -- THEN
1739 -- hr_utility.set_location('Leaving:'||l_proc,99);
1740 -- RETURN -99;
1741
1742 END get_accrual_entitlement ;
1743
1744
1745
1746 /*---------------------------------------------------------------------
1747 Name : get_annual_leave_plan
1748 Purpose : To get the Annual Leave Plan for an Assignment
1749 Returns : PLAN_ID if successful, NULL otherwise
1750 ---------------------------------------------------------------------*/
1751
1752 FUNCTION get_annual_leave_plan
1753 (p_assignment_id IN NUMBER
1754 ,p_business_group_id IN NUMBER
1755 ,p_calculation_date IN DATE)
1756 RETURN NUMBER IS
1757
1758 l_proc VARCHAR2(72) := g_package||'get_annual_leave_plan';
1759 l_plan_id NUMBER;
1760
1761 CURSOR csr_annual_leave_accrual_plan(c_business_group_id IN NUMBER
1762 ,c_calculation_date IN DATE
1763 ,c_assignment_id IN NUMBER) IS
1764 SELECT pap.accrual_plan_id
1765 FROM pay_accrual_plans pap,
1766 pay_element_entries_f pee,
1767 pay_element_links_f pel,
1768 pay_element_types_f pet
1769 WHERE pel.element_link_id = pee.element_link_id
1770 AND pel.element_type_id = pet.element_type_id
1771 AND pee.assignment_id = c_assignment_id
1772 AND pet.element_type_id = pap.accrual_plan_element_type_id
1773 AND pap.business_group_id = c_business_group_id
1774 AND c_calculation_date BETWEEN pee.effective_start_date AND pee.effective_end_date
1775 AND pap.accrual_category = (
1776 SELECT lookup_code
1777 FROM hr_lookups
1778 WHERE lookup_type = 'ABSENCE_CATEGORY'
1779 AND meaning = 'Annual Leave');
1780
1781 BEGIN
1782 hr_utility.set_location('Entering: '||l_proc,5);
1783 OPEN csr_annual_leave_accrual_plan (p_business_group_id
1784 ,p_calculation_date
1785 ,p_assignment_id);
1786
1787 FETCH csr_annual_leave_accrual_plan INTO l_plan_id;
1788 CLOSE csr_annual_leave_accrual_plan;
1789 hr_utility.set_location('Leaving:'||l_proc,10);
1790 RETURN l_plan_id;
1791
1792 -- EXCEPTION
1793 -- WHEN OTHERS
1794 -- THEN
1795 -- hr_utility.set_location('Leaving:'||l_proc,99);
1796 -- RETURN NULL;
1797 END;
1798
1799
1800
1801
1802 /*---------------------------------------------------------------------
1803 Name : get_continuous_service_date
1804 Purpose : To get the Continuous Service Date for an Annual Leave Plan
1805 Returns : CONTINUOUS_SERVICE_DATE if successful, NULL otherwise
1806 ---------------------------------------------------------------------*/
1807
1808 FUNCTION get_continuous_service_date
1809 (p_assignment_id IN NUMBER
1810 ,p_business_group_id IN NUMBER
1811 ,p_accrual_plan_id IN NUMBER
1812 ,p_calculation_date IN DATE)
1813 RETURN DATE IS
1814
1815 l_proc VARCHAR2(72) := g_package||'get_continuous_service_date';
1816 l_csd DATE;
1817
1818 /*Bug2920725 Corrected base tables to support security model*/
1819
1820 CURSOR csr_continuous_service_date (c_business_group_id NUMBER
1821 ,c_accrual_plan_id NUMBER
1822 ,c_calculation_date DATE
1823 ,c_assignment_id NUMBER) IS
1824 SELECT NVL(TO_DATE(pev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS'),pps.date_start)
1825 FROM pay_element_entries_f pee,
1826 pay_element_entry_values_f pev,
1827 pay_input_values_f piv,
1828 pay_accrual_plans pap,
1829 hr_lookups hrl,
1830 per_assignments_f asg,
1831 per_periods_of_service pps
1832 WHERE pev.element_entry_id = pee.element_entry_id
1833 AND pap.accrual_plan_element_type_id = piv.element_type_id
1834 AND piv.input_value_id = pev.input_value_id
1835 AND pee.entry_type ='E'
1836 AND asg.assignment_id = pee.assignment_id
1837 AND asg.assignment_id = c_assignment_id
1838 AND pap.accrual_plan_id = c_accrual_plan_id
1839 AND asg.business_group_id = c_business_group_id
1840 AND asg.period_of_service_id = pps.period_of_service_id
1841 AND c_calculation_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1842 AND c_calculation_date BETWEEN pee.effective_start_date AND pee.effective_end_date
1843 AND c_calculation_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1844 AND c_calculation_date BETWEEN pev.effective_start_date AND pev.effective_end_date
1845 AND piv.name = hrl.meaning
1846 AND hrl.lookup_type = 'NAME_TRANSLATIONS'
1847 AND hrl.lookup_code = 'PTO_CONTINUOUS_SD';
1848
1849 BEGIN
1850 hr_utility.set_location('Entering:'||l_proc,5);
1851 OPEN csr_continuous_service_date (p_business_group_id
1852 ,p_accrual_plan_id
1853 ,p_calculation_date
1854 ,p_assignment_id);
1855 FETCH csr_continuous_service_date INTO l_csd;
1856 CLOSE csr_continuous_service_date;
1857 hr_utility.set_location('Leaving:'||l_proc,10);
1858 RETURN l_csd;
1859
1860 -- EXCEPTION
1861 -- WHEN OTHERS
1862 -- THEN
1863 -- hr_utility.set_location('Leaving:'||l_proc,99);
1864 -- RETURN NULL;
1865 END;
1866
1867
1868
1869 -----------------------------------------------------------------------------
1870 -- accrual_daily_basis function
1871 --
1872 -- public function called by PTO Accrual Formulae
1873 -- PTO accrual formula.
1874 -----------------------------------------------------------------------------
1875
1876 function accrual_daily_basis
1877 (p_payroll_id in number
1878 ,p_accrual_plan_id in number
1879 ,p_assignment_id in number
1880 ,p_calculation_start_date in date
1881 ,p_calculation_end_date in date
1882 ,p_service_start_date in date
1883 ,p_business_group_hours in number
1884 ,p_business_group_freq in varchar2)
1885 return number is
1886
1887 l_procedure_name varchar2(61) := 'hr_au_holidays.accrual_daily_basis' ;
1888 l_accrual number := 0 ;
1889 l_accrual_band_cache t_accrual_band_tab ;
1890 l_asg_work_day_info_cache t_asg_work_day_info_tab ;
1891 l_counter integer ;
1892 l_years_service number ;
1893 l_annual_accrual number ;
1894 l_special_annual_accrual number ;
1895 l_days_in_year integer ;
1896 l_days_in_part_period integer ;
1897 l_days_suspended integer ;
1898 l_next_anniversary_date date ;
1899 l_mm_dd varchar2(10);
1900 l_start_date date ;
1901 l_end_date date ;
1902 l_period_accrual number ;
1903 l_asg_working_hours per_all_assignments_f.normal_hours%type ;
1904 l_pay_periods_per_year per_time_period_types.number_per_fiscal_year%type ;
1905 e_accrual_function_failure exception ;
1906
1907 -- cursor to get number of periods per year
1908
1909 cursor c_number_of_periods_per_year (p_payroll_id number
1910 ,p_effective_date date) is
1911 select tpt.number_per_fiscal_year
1912 from pay_payrolls_f p
1913 , per_time_period_types tpt
1914 where p.payroll_id = p_payroll_id
1915 and p_effective_date between p.effective_start_date
1916 and p.effective_end_date
1917 and tpt.period_type = p.period_type ;
1918
1919 -- cursor to get assignment work day information
1920
1921 cursor c_asg_work_day_history(p_assignment_id number
1922 ,p_start_date date
1923 ,p_end_date date) is
1924 select a.effective_start_date
1925 , a.effective_end_date
1926 , a.normal_hours
1927 , a.frequency
1928 from per_assignments_f a
1929 where a.assignment_id = p_assignment_id
1930 and a.effective_start_date <= p_end_date
1931 and a.effective_end_date >= p_start_date
1932 order by
1933 a.effective_start_date ;
1934
1935 -- cursor to get accrual band details
1936
1937 cursor c_accrual_bands (p_accrual_plan_id number) is
1938 select ab.lower_limit
1939 , ab.upper_limit
1940 , ab.annual_rate
1941 from pay_accrual_bands ab
1942 where ab.accrual_plan_id = p_accrual_plan_id
1943 order by
1944 ab.lower_limit ;
1945
1946 /*Bug 5964317 Modified cursor c_periods to get time periods for corresponding payrolls */
1947 -- cursor to get time periods to process
1948
1949 cursor c_periods (p_assignment_id number
1950 ,p_start_date date
1951 ,p_end_date date) is
1952 select greatest(tp.start_date,paf.effective_start_date) start_date,least(tp.end_date,paf.effective_end_date) end_date
1953 from per_time_periods tp,per_assignments_f paf
1954 where paf.assignment_id = p_assignment_id
1955 and tp.payroll_id = paf.payroll_id
1956 and tp.start_date <= paf.effective_end_date
1957 and tp.end_date >= paf.effective_start_date
1958 and tp.start_date <= p_end_date
1959 and tp.end_date >= p_start_date
1960 and paf.effective_start_date <= p_end_date
1961 and paf.effective_end_date >= p_start_date
1962 order by tp.start_date ;
1963
1964 -- local function to get accrual annual rate from PL/SQL table
1965
1966 function accrual_annual_rate(p_years_service number) return number is
1967
1968 l_procedure_name varchar2(61) := ' accrual_annual_rate' ;
1969 l_annual_accrual pay_accrual_bands.annual_rate%type ;
1970 l_counter integer := 1 ;
1971 l_band_notfound_flag boolean := true ;
1972
1973 begin
1974
1975 hr_utility.trace(' In: ' || l_procedure_name) ;
1976
1977 -- loop through the PL/SQL table looking for a likely accrual band
1978 while l_accrual_band_cache.count > 0
1979 and l_band_notfound_flag
1980 and l_counter <= l_accrual_band_cache.last
1981 loop
1982
1983 if (p_years_service >= l_accrual_band_cache(l_counter).lower_limit) and
1984 (p_years_service < l_accrual_band_cache(l_counter).upper_limit)
1985 then
1986
1987 l_annual_accrual := l_accrual_band_cache(l_counter).annual_rate ;
1988 l_band_notfound_flag := false ;
1989
1990 end if ;
1991
1992 l_counter := l_counter + 1 ;
1993
1994 end loop ;
1995
1996 -- raise error if no accrual band found
1997 if l_band_notfound_flag
1998 then
1999
2000 raise e_accrual_function_failure ;
2001
2002 end if ;
2003
2004 hr_utility.trace(' Out: ' || l_procedure_name ||' '|| l_annual_accrual) ;
2005 return l_annual_accrual ;
2006
2007 end accrual_annual_rate ;
2008
2009 -- local function to get asg working hours from PL/SQL table
2010
2011 function asg_working_hours(p_effective_date date
2012 ,p_frequency varchar2) return number is
2013
2014 l_procedure_name varchar2(61) := ' asg_working_hours' ;
2015 l_asg_working_hours per_all_assignments_f.normal_hours%type ;
2016 l_counter integer := 1 ;
2017 l_hours_notfound_flag boolean := true ;
2018
2019 begin
2020
2021 hr_utility.trace(' In: ' || l_procedure_name) ;
2022 hr_utility.trace('p_effective_date = '||to_char(p_effective_date, 'DD-MON-YYYY'));
2023
2024 -- loop through the PL/SQL table looking for a likely accrual band
2025 while l_asg_work_day_info_cache.count > 0
2026 and l_hours_notfound_flag
2027 and l_counter <= l_asg_work_day_info_cache.last
2028 loop
2029
2030 if p_effective_date between l_asg_work_day_info_cache(l_counter).effective_start_date
2031 and l_asg_work_day_info_cache(l_counter).effective_end_date
2032 and l_asg_work_day_info_cache(l_counter).frequency = p_frequency
2033 then
2034
2035 l_asg_working_hours := l_asg_work_day_info_cache(l_counter).normal_hours ;
2036 l_hours_notfound_flag := false ;
2037
2038 end if ;
2039
2040 l_counter := l_counter + 1 ;
2041
2042 end loop ;
2043
2044 -- raise error if no working hours found
2045 if l_hours_notfound_flag
2046 then
2047
2048 hr_utility.trace(' Failed_mk: ' || l_procedure_name ) ;
2049 hr_utility.trace(' End Date: ' || to_char(l_asg_work_day_info_cache(l_counter).effective_end_date, 'DD-MON-YYYY'));
2050 raise e_accrual_function_failure ;
2051
2052 end if ;
2053
2054 hr_utility.trace(' Out: ' || l_procedure_name) ;
2055 return l_asg_working_hours ;
2056
2057
2058
2059 end asg_working_hours ;
2060
2061 begin
2062
2063 hr_utility.trace('In: ' || l_procedure_name) ;
2064 hr_utility.trace(' p_payroll_id: ' || to_char(p_payroll_id)) ;
2065 hr_utility.trace(' p_accrual_plan_id: ' || to_char(p_accrual_plan_id)) ;
2066 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
2067 hr_utility.trace(' p_calculation_start_date: ' || to_char(p_calculation_start_date, 'DD-MM-YYYY')) ;
2068 hr_utility.trace(' p_calculation_end_date: ' || to_char(p_calculation_end_date, 'DD-MM-YYYY')) ;
2069 hr_utility.trace(' p_service_start_date: ' || to_char(p_service_start_date, 'DD-MM-YYYY')) ;
2070 hr_utility.trace(' p_business_group_hours: ' || to_char(p_business_group_hours)) ;
2071 hr_utility.trace(' p_business_group_freq: ' || p_business_group_freq) ;
2072
2073 -- cache the assignment's work day history
2074
2075 l_counter := 1 ;
2076
2077 for r_asg_work_day in c_asg_work_day_history(p_assignment_id
2078 ,p_calculation_start_date
2079 ,p_calculation_end_date)
2080 loop
2081
2082 l_asg_work_day_info_cache(l_counter).effective_start_date := r_asg_work_day.effective_start_date ;
2083 l_asg_work_day_info_cache(l_counter).effective_end_date := r_asg_work_day.effective_end_date ;
2084
2085 if r_asg_work_day.normal_hours is not null then
2086 l_asg_work_day_info_cache(l_counter).normal_hours := r_asg_work_day.normal_hours ;
2087 else
2088 l_asg_work_day_info_cache(l_counter).normal_hours := p_business_group_hours ;
2089 end if ;
2090
2091 if r_asg_work_day.frequency is not null then
2092 l_asg_work_day_info_cache(l_counter).frequency := r_asg_work_day.frequency ;
2093 else
2094 l_asg_work_day_info_cache(l_counter).frequency := p_business_group_freq ;
2095 end if ;
2096
2097 l_counter := l_counter + 1 ;
2098
2099 end loop ; -- c_asg_work_day_history
2100
2101 -- cache the accrual bands
2102 l_counter := 1 ;
2103
2104 for r_accrual_band in c_accrual_bands(p_accrual_plan_id)
2105 loop
2106
2107 l_accrual_band_cache(l_counter).lower_limit := r_accrual_band.lower_limit ;
2108 l_accrual_band_cache(l_counter).upper_limit := r_accrual_band.upper_limit ;
2109 l_accrual_band_cache(l_counter).annual_rate := r_accrual_band.annual_rate ;
2110
2111 l_counter := l_counter + 1 ;
2112
2113 end loop ; -- c_accrual_bands
2114
2115 -- get the number of periods per year
2116 open c_number_of_periods_per_year(p_payroll_id, p_calculation_start_date) ;
2117 fetch c_number_of_periods_per_year
2118 into l_pay_periods_per_year ;
2119 close c_number_of_periods_per_year ;
2120
2121 /*Bug 5964317 Passing p_assignment_id in place of p_payroll_id */
2122 -- loop through the payroll periods
2123 for r_period in c_periods(p_assignment_id
2124 ,p_calculation_start_date
2125 ,p_calculation_end_date)
2126 loop
2127
2128 -- how many years of effective service does the assignment have (at the end of each period)
2129 -- i.e. (days since hired - days with susp ass) / avg no of days per year
2130 l_years_service := floor(((r_period.end_date - p_service_start_date)
2131 - hr_au_holidays.days_suspended(p_assignment_id, p_service_start_date, r_period.end_date)) / 365.25) ;
2132
2133 -- get the accrual band
2134 l_annual_accrual := accrual_annual_rate(l_years_service) ;
2135
2136
2137
2138 -- get the assignment's normal working hours (at the end of each period)
2139 -- l_asg_working_hours := asg_working_hours(r_period.end_date, p_business_group_freq) ;
2140
2141 l_asg_working_hours := asg_working_hours(least(r_period.end_date, p_calculation_end_date), p_business_group_freq) ;
2142
2143
2144 -- the accrual rate in the accrual band is for assignments that work the
2145 -- business group's default working hours. Now prorate the accrual rate
2146 -- based on the proporation of the business group hours that the
2147 -- assignment works.
2148 l_annual_accrual := l_annual_accrual * (l_asg_working_hours / p_business_group_hours) ;
2149
2150 -- the algorithm being used here is:
2151 --
2152 -- days to accrue for period
2153 -- = (annual entitlement / days in current holiday year)
2154 -- * days in period
2155 --
2156 -- the number of days in the year varies between leap years and
2157 -- leap years. if the anniversary date falls in the period (or part
2158 -- period) being processed then the calculation needs to treat the
2159 -- bit of the period up to the anniversary date separately from the
2160 -- bit of the period after the anniversary date to allow for different
2161 -- number of days in the holiday year.
2162
2163 -- we may be dealing with a part period here, ie if the calculation
2164 -- start date is part way through the first period or if the
2165 -- calculation end date is part way through the last period.
2166 if p_calculation_start_date between r_period.start_date and r_period.end_date then
2167 l_start_date := p_calculation_start_date ;
2168 else
2169 l_start_date := r_period.start_date ;
2170 end if ;
2171
2172 if p_calculation_end_date between r_period.start_date and r_period.end_date then
2173 l_end_date := p_calculation_end_date ;
2174 else
2175 l_end_date := r_period.end_date ;
2176 end if ;
2177
2178 -- l_start_date and l_end_date now define the time span we're
2179 -- interested in. find the anniversary date and see if it falls
2180 -- between the dates.
2181 l_mm_dd:= to_char(p_service_start_date, 'MMDD');/*for bug2272301*/
2182 if (l_mm_dd = '0229' ) then
2183 l_mm_dd:='0228';
2184 end if;
2185 l_next_anniversary_date := to_date(to_char(l_start_date, 'YYYY') ||l_mm_dd
2186 ,'YYYYMMDD') ;
2187
2188 if l_next_anniversary_date <= l_start_date
2189 then
2190 l_next_anniversary_date := add_months(l_next_anniversary_date, 12) ;
2191 end if ;
2192
2193 if (least((l_next_anniversary_date-1), p_calculation_end_date)) between l_start_date and l_end_date then
2194
2195
2196 -- this is the special case where the anniversary date is in the time
2197 -- span we're dealing with
2198
2199 -- process the start of the time span up to the (but not incl) anniversary date
2200 -- see bug 1313971
2201 -- consideration of multiple bands of different annual rate bug no 1460922
2202
2203 l_years_service := floor((((least((l_next_anniversary_date-1), p_calculation_end_date))- p_service_start_date)
2204 - hr_au_holidays.days_suspended(p_assignment_id, p_service_start_date, (least((l_next_anniversary_date-1), p_calculation_end_date)))) / 365.25) ;
2205
2206
2207 l_special_annual_accrual := accrual_annual_rate(l_years_service) ;
2208
2209
2210
2211 l_asg_working_hours := asg_working_hours((least((l_next_anniversary_date-1), p_calculation_end_date)), p_business_group_freq) ;
2212
2213
2214 -- the accrual rate in the accrual band is for assignments that work the
2215 -- business group's default working hours. Now prorate the accrual rate
2216 -- based on the proporation of the business group hours that the
2217 -- assignment works.
2218 l_special_annual_accrual := l_special_annual_accrual * (l_asg_working_hours / p_business_group_hours) ;
2219
2220
2221
2222 l_days_in_year := (l_next_anniversary_date - add_months(l_next_anniversary_date, -12)) ;
2223 l_days_in_part_period := ((least((l_next_anniversary_date-1), p_calculation_end_date)) - l_start_date) +1 ;
2224 l_days_suspended := hr_au_holidays.days_suspended (p_assignment_id
2225 ,l_start_date
2226 ,(least((l_next_anniversary_date-1), p_calculation_end_date)));
2227 l_period_accrual := (l_special_annual_accrual / l_days_in_year) * (l_days_in_part_period - l_days_suspended) ;
2228
2229 If l_end_date > (l_next_anniversary_date-1) then
2230 -- process the anniversary date to the end of the time span
2231 l_days_in_year := (add_months(l_next_anniversary_date, 12) - l_next_anniversary_date) ;
2232 l_days_in_part_period := (l_end_date - l_next_anniversary_date) + 1 ;
2233 l_days_suspended := hr_au_holidays.days_suspended (p_assignment_id
2234 ,l_next_anniversary_date
2235 ,l_end_date);
2236 l_period_accrual := l_period_accrual + (l_annual_accrual / l_days_in_year) * (l_days_in_part_period - l_days_suspended);
2237 end if;
2238
2239 else
2240
2241 -- this is the most common case where the anniversary date is outside
2242 -- the time span we're dealing with
2243
2244 l_days_in_year := (l_next_anniversary_date - add_months(l_next_anniversary_date, -12)) ;
2245 l_days_in_part_period := (l_end_date - l_start_date) + 1 ;
2246 l_days_suspended := hr_au_holidays.days_suspended (p_assignment_id
2247 ,l_start_date
2248 ,l_end_date);
2249 l_period_accrual := (l_annual_accrual / l_days_in_year) * (l_days_in_part_period - l_days_suspended) ;
2250
2251 end if ;
2252
2253 l_accrual := l_accrual + l_period_accrual ;
2254
2255 end loop ; -- c_periods
2256
2257 hr_utility.trace('Out: ' || l_procedure_name) ;
2258 return l_accrual ;
2259
2260 -- exception
2261 -- when e_accrual_function_failure
2262 -- then
2263 -- hr_utility.set_message(801, 'HR_AU_ACCRUAL_FUNCTION_FAILURE') ;
2264 -- hr_utility.raise_error ;
2265
2266 end accrual_daily_basis ;
2267
2268
2269 /*---------------------------------------------------------------------
2270 Name : days_suspended
2271 Purpose : to get the number of suspended days in the period
2272 Returns : Number of suspended days
2273 Issue - the requirement AU019PTO 1.8 talks about suspending accrual
2274 based on leave types. In Core PTO they suggest using assignment status
2275 so basing on that but including proration.
2276 ---------------------------------------------------------------------*/
2277
2278 FUNCTION days_suspended
2279 (p_assignment_id IN NUMBER
2280 ,p_start_date IN DATE
2281 ,p_end_date IN DATE)
2282 RETURN NUMBER IS
2283
2284 /*Bug2920725 Corrected base tables to support security model*/
2285
2286 CURSOR csr_days_suspended(c_assignment_id NUMBER
2287 ,c_start_date DATE
2288 ,c_end_date DATE) IS
2289 SELECT
2290 NVL(SUM(1+
2291 LEAST(effective_end_date, c_end_date)
2292 - GREATEST(effective_start_date, c_start_date)),0)
2293 FROM
2294 per_assignments_f asg
2295 ,per_assignment_status_types t
2296 WHERE
2297 assignment_id = c_assignment_id
2298 AND t.assignment_status_type_id = asg.assignment_status_type_id
2299 AND effective_start_date <= c_end_date
2300 AND effective_end_date >= c_start_date
2301 AND per_system_status = 'SUSP_ASSIGN';
2302
2303 l_proc VARCHAR2(72) := g_package||'days_suspended';
2304 l_days_suspended NUMBER := 0;
2305
2306 BEGIN
2307
2308 hr_utility.set_location('Entering'||l_proc,5);
2309 -- hr_utility.trace(TO_CHAR(p_start_date,'DD-MM-YYYY')||' and '
2310 -- ||TO_CHAR(p_end_date,'DD-MM-YYYY'));
2311
2312 IF (p_start_date > p_end_date) THEN
2313 hr_utility.set_message(801,'HR_AU_INVALID_DATE_RANGE');
2314 hr_utility.raise_error;
2315 END IF;
2316
2317 OPEN csr_days_suspended(p_assignment_id
2318 ,p_start_date
2319 ,p_end_date);
2320 FETCH csr_days_suspended INTO l_days_suspended;
2321 CLOSE csr_days_suspended;
2322
2323 hr_utility.trace('Days Suspended between '
2324 ||TO_CHAR(p_start_date,'DD-MM-YYYY')||' and '
2325 ||TO_CHAR(p_end_date,'DD-MM-YYYY')||' = '
2326 ||TO_CHAR(l_days_suspended));
2327 hr_utility.set_location('Leaving:'||l_proc,10);
2328
2329 RETURN l_days_suspended;
2330
2331 -- EXCEPTION
2332 -- WHEN others THEN
2333 -- hr_utility.set_location('Leaving:'||l_proc,99);
2334 -- RETURN NULL;
2335
2336 END days_suspended;
2337
2338 -----------------------------------------------------------------------------
2339 -- check_periods function
2340 --
2341 -- public function called by AU_ANNUAL_LEAVE_ACCRUAL_DAILY
2342 -- PTO accrual formula.
2343 -----------------------------------------------------------------------------
2344
2345 function check_periods
2346 (p_payroll_id in number)
2347 return date is
2348
2349 l_proc varchar2(61) := 'hr_au_holidays.check_periods' ;
2350 l_end_date date := to_date('01010001','DDMMYYYY');
2351
2352 -- cursor to check payroll periods exist up to calc_end_date
2353
2354 cursor c_last_period (p_payroll_id number) is
2355 select max(tp.end_date)
2356 from per_time_periods tp
2357 where tp.payroll_id = p_payroll_id;
2358 begin
2359
2360 hr_utility.set_location(' In: ' || l_proc,5) ;
2361
2362 -- check payroll periods exist up to calculation_end_date
2363
2364 open c_last_period ( p_payroll_id );
2365 fetch c_last_period into l_end_date;
2366 close c_last_period;
2367
2368 hr_utility.set_location(' Out: ' || l_proc,10) ;
2369
2370 return(l_end_date);
2371
2372 -- EXCEPTION
2373 -- WHEN others THEN
2374 -- hr_utility.trace('Error - payroll periods not found for payroll_id '||to_char(p_payroll_id));
2375 -- hr_utility.set_location('Leaving:'||l_proc,99);
2376 -- RETURN NULL;
2377
2378 end check_periods ;
2379
2380 -----------------------------------------------------------------------------
2381 -- adjust_for_suspend_assign function
2382 --
2383 -- public function called by Accrual/Entitlement Formula
2384 -- adjusts ineligability end date to take account of any
2385 -- periods when assignment was suspended
2386 -----------------------------------------------------------------------------
2387
2388 function adjust_for_suspend_assign
2389 (p_assignment_id IN NUMBER
2390 ,p_adjust_date IN DATE
2391 ,p_start_date IN DATE
2392 ,p_end_date IN DATE)
2393 return date is
2394
2395 l_proc varchar2(61) := 'hr_au_holidays.adjust_for_suspend_assign' ;
2396 l_days_suspended number := 1;
2397 l_start_date date := p_start_date;
2398 l_adjust_date date := p_adjust_date;
2399
2400 begin
2401
2402 hr_utility.set_location(' In: ' || l_proc,5) ;
2403
2404 -- loop to check each new period added on for suspended assignments
2405
2406 while (l_days_suspended > 0) and (l_adjust_date < p_end_date) loop
2407
2408 l_days_suspended := hr_au_holidays.days_suspended (p_assignment_id
2409 ,l_start_date
2410 ,l_adjust_date);
2411 l_start_date := l_adjust_date;
2412 l_adjust_date := l_adjust_date + l_days_suspended;
2413
2414 end loop;
2415
2416 if l_adjust_date > p_end_date then
2417 l_adjust_date := p_end_date;
2418 end if;
2419
2420 hr_utility.set_location(' Out: ' || l_proc,10) ;
2421
2422 return (l_adjust_date);
2423
2424 -- EXCEPTION
2425 -- WHEN others THEN
2426 -- hr_utility.set_location('Leaving:'||l_proc,99);
2427 -- RETURN NULL;
2428
2429 end adjust_for_suspend_assign ;
2430
2431 -----------------------------------------------------------------------------
2432 --
2433 -- Find Leave Adjustment Intialise or Leave Entitlement Initailise value
2434 -- depending on the parameter(p_initialise_type) passed.
2435 --
2436 -- public function called by Leave Formulae
2437 --
2438 -----------------------------------------------------------------------------
2439
2440 function get_leave_initialise
2441 (p_assignment_id in NUMBER
2442 ,p_accrual_plan_id in NUMBER
2443 ,p_calc_end_date in DATE
2444 ,p_initialise_type in VARCHAR2
2445 ,p_start_date in DATE
2446 ,p_end_date in DATE)
2447 return number is
2448
2449 l_proc varchar2(61) := 'hr_au_holidays.get_leave_initailise' ;
2450 l_initialise number := 0;
2451
2452 -- find Leave Initialise Values
2453
2454 cursor c_get_initialise ( v_assignment_id number
2455 ,v_accrual_plan_id number
2456 ,v_calc_end_date date
2457 ,v_initialise_type varchar2
2458 ,v_start_date date
2459 ,v_end_date date ) is
2460 select
2461 sum(nvl(fnd_number.canonical_to_number(pev1.screen_entry_value),0))
2462 from
2463 pay_accrual_plans pap
2464 ,pay_element_types_f pet
2465 ,pay_element_links_f pel
2466 ,pay_input_values_f piv1
2467 ,pay_input_values_f piv2
2468 ,pay_element_entries_f pee
2469 ,pay_element_entry_values_f pev1
2470 ,pay_element_entry_values_f pev2
2471 where
2472 pee.assignment_id = v_assignment_id
2473 and pet.element_name = v_initialise_type
2474 and pet.element_type_id = pel.element_type_id
2475 and pel.element_link_id = pee.element_link_id
2476 and pee.element_entry_id = pev1.element_entry_id
2477 and pev1.input_value_id = piv1.input_value_id
2478 and piv1.name = 'Hours'
2479 and piv1.element_type_id = pet.element_type_id
2480 and pee.element_entry_id = pev2.element_entry_id
2481 and pev2.input_value_id = piv2.input_value_id
2482 and piv2.name = 'Accrual Plan'
2483 and piv2.element_type_id = pet.element_type_id
2484 and pev2.screen_entry_value = pap.accrual_plan_name
2485 and pap.accrual_plan_id = v_accrual_plan_id
2486 and pee.effective_start_date <= v_calc_end_date
2487 and pee.effective_start_date between pet.effective_start_date and pet.effective_end_date
2488 and pee.effective_start_date between pel.effective_start_date and pel.effective_end_date
2489 and pee.effective_start_date between piv1.effective_start_date and piv1.effective_end_date
2490 and pee.effective_start_date between pev1.effective_start_date and pev1.effective_end_date
2491 and pee.effective_start_date between piv2.effective_start_date and piv2.effective_end_date
2492 and pee.effective_start_date between pev2.effective_start_date and pev2.effective_end_date
2493 and pee.effective_start_date between CASE when v_start_date between pee.effective_start_date and pee.effective_end_date
2494 then pee.effective_start_date
2495 else
2496 v_start_date
2497 end
2498 and v_end_date; /*Bug 14789375 */
2499 /* -- and pee.effective_start_date between v_start_date and v_end_date;
2500 and ( (pee.effective_start_date between v_start_date and v_end_date)
2501 OR (v_start_date between pee.effective_start_date and pee.effective_end_date)
2502 );*/
2503
2504 begin
2505
2506 hr_utility.set_location(' In: ' || l_proc,5) ;
2507
2508 -- find total leave initialise - should return zero if none entered
2509
2510 open c_get_initialise (p_assignment_id
2511 ,p_accrual_plan_id
2512 ,p_calc_end_date
2513 ,p_initialise_type
2514 ,p_start_date
2515 ,p_end_date );
2516 fetch c_get_initialise into l_initialise;
2517 close c_get_initialise;
2518
2519 hr_utility.trace('Initialise : '||to_char(l_initialise));
2520 hr_utility.set_location(' Out: ' || l_proc,10) ;
2521
2522 return(nvl(l_initialise,0));
2523
2524 end get_leave_initialise ;
2525
2526
2527 -----------------------------------------------------------------------------
2528 --
2529 -- Find long service leave entitlement date
2530 --
2531 -- Because LSL has two entitlement periods they cannot be stored
2532 --
2533 -- Find long service leave entitlement date
2534 --
2535 -- Because LSL has two entitlement periods they cannot be stored
2536 -- in the standard PTO model. For LSL we get the periods from the
2537 -- different in the from and to dates in the plan accrual bands.
2538 --
2539 -- public function called by Long Service Leave Formulae
2540 --
2541 -----------------------------------------------------------------------------
2542
2543 function get_lsl_entitlement_date
2544 ( p_accrual_plan_id in NUMBER
2545 ,p_assignment_id in NUMBER
2546 ,p_enrollment_date in DATE
2547 ,p_service_start_date in DATE
2548 ,p_calculation_date in DATE
2549 ,p_next_entitlement_date in out NOCOPY DATE)
2550 return date is
2551
2552 l_proc varchar2(61) := 'hr_au_holidays.get_lsl_entitlement_date' ;
2553 l_first_period number;
2554 l_subsequent_periods number;
2555 l_entitlement_date date;
2556 l_next_entitlement_date date;
2557 l_eot date := to_date('31124712','DDMMYYYY');
2558
2559 -- find lsl entitlement periods
2560 cursor c_accrual_bands (v_accrual_plan_id number) is
2561 select (ab.upper_limit - ab.lower_limit)
2562 from pay_accrual_bands ab
2563 where ab.accrual_plan_id = v_accrual_plan_id
2564 order by
2565 ab.lower_limit ;
2566
2567 begin
2568
2569 hr_utility.set_location(' In: ' || l_proc, 5) ;
2570
2571 open c_accrual_bands (p_accrual_plan_id);
2572 fetch c_accrual_bands into l_first_period;
2573 fetch c_accrual_bands into l_subsequent_periods;
2574 close c_accrual_bands;
2575
2576 hr_utility.trace('First : '||to_char(l_first_period) );
2577 hr_utility.trace('Subsequent : '||to_char(l_subsequent_periods) );
2578
2579 if (l_first_period <= 0) OR (l_subsequent_periods <= 0) then
2580 hr_utility.set_message(801,'HR_AU_INVALID_LSL_PERIODS');
2581 hr_utility.raise_error;
2582 end if;
2583
2584 -- set entitlement date to end of first period plus any suspension
2585 -- adjustment
2586 l_entitlement_date := p_service_start_date;
2587
2588 p_next_entitlement_date := hr_au_holidays.adjust_for_suspend_assign
2589 (p_assignment_id
2590 ,add_months(p_service_start_date, (l_first_period * 12) )
2591 ,p_service_start_date
2592 ,l_eot);
2593
2594 if p_calculation_date < p_next_entitlement_date then
2595 return (l_entitlement_date);
2596 end if;
2597
2598 -- while next date is less that calculation date keep adding
2599 -- subsequent entitlement periods
2600 while p_calculation_date >= p_next_entitlement_date loop
2601
2602 l_entitlement_date := p_next_entitlement_date;
2603
2604 p_next_entitlement_date := hr_au_holidays.adjust_for_suspend_assign
2605 (p_assignment_id
2606 ,add_months(l_entitlement_date, (l_subsequent_periods * 12) )
2607 ,l_entitlement_date
2608 ,l_eot);
2609
2610 end loop;
2611
2612 hr_utility.set_location(' Out: ' || l_proc, 10) ;
2613
2614 return (l_entitlement_date);
2615
2616 -- EXCEPTION
2617 -- WHEN others THEN
2618 -- hr_utility.trace('Error - cursor c_accrual_bands failed - Accrual Plan ID: '||to_char(p_accrual_plan_id) );
2619 -- hr_utility.set_location('Leaving: '||l_proc,99);
2620 -- RETURN (p_service_start_date - 1);
2621
2622 end get_lsl_entitlement_date;
2623
2624
2625 -----------------------------------------------------------------------------
2626 --
2627 -- Validate Accrual Plan Name in Entitlement Adjustment Element Input Value
2628 --
2629 -----------------------------------------------------------------------------
2630
2631 function validate_accrual_plan_name
2632 ( p_business_group_id in NUMBER
2633 ,p_entry_value in VARCHAR2)
2634 return number is
2635
2636 l_proc varchar2(61) := 'hr_au_holidays.validate_accrual_plan_name' ;
2637 l_plan_exists number := 0;
2638
2639 -- find plan name
2640 cursor c_plan_name ( v_business_group_id number
2641 ,v_entry_value varchar2 ) is
2642 select 1
2643 from pay_accrual_plans pap
2644 where pap.business_group_id = v_business_group_id
2645 and pap.accrual_plan_name = v_entry_value;
2646
2647 begin
2648
2649 hr_utility.set_location(' In: ' || l_proc, 5) ;
2650
2651 open c_plan_name ( p_business_group_id
2652 ,p_entry_value);
2653 fetch c_plan_name into l_plan_exists;
2654
2655 if c_plan_name%notfound then
2656 l_plan_exists := 0;
2657 end if;
2658
2659 close c_plan_name;
2660
2661 hr_utility.set_location(' Out: ' || l_proc, 10) ;
2662
2663 return (l_plan_exists);
2664
2665 --EXCEPTION
2666 -- WHEN others THEN
2667 -- hr_utility.set_location('Leaving: '||l_proc,99);
2668 -- RETURN (99);
2669
2670 end validate_accrual_plan_name;
2671
2672 /*Bug# 7607177 --This function is called from AU_ANNUAL_LEAVE_ACCRUAL_DAILY fast formula
2673 to get the correct enrollment start date for calculating the PTO Accruals*/
2674 /*Bug# 8604518 --In the cursor,the date joins on the table 'pay_element_entries_f' are modified
2675 to return rows when Annual Leave and LSL are paid to terminated employees */
2676 function au_get_enrollment_startdate
2677 ( p_accrual_plan_id in number
2678 ,p_assignment_id in number
2679 ,p_calculation_date in date )
2680 return date
2681 is
2682
2683 l_enrollment_startdate date;
2684
2685 cursor c_enrollment_startdate(v_accrual_plan_id number
2686 ,v_assignment_id number
2687 ,v_calculation_date date )
2688 is
2689 select min(PEE.EFFECTIVE_START_DATE)
2690 from pay_accrual_plans pap,
2691 pay_element_types_f pet,
2692 pay_element_links_f pel,
2693 pay_element_entries_f pee,
2694 per_assignments_f paf,
2695 per_periods_of_service pps
2696 where pee.element_link_id = pel.element_link_id
2697 and pel.element_type_id = pet.element_type_id
2698 and pet.element_type_id = pap.accrual_plan_element_type_id
2699 and paf.assignment_id = pee.assignment_id
2700 and paf.period_of_service_id =pps.period_of_service_id
2701 and pee.entry_type ='E'
2702 and pee.assignment_id = v_assignment_id
2703 and pap.accrual_plan_id = v_accrual_plan_id
2704 and pee.effective_end_date >= pps.date_start
2705 and pee.effective_start_date <= nvl(pps.actual_termination_date,to_date('31/12/4712','dd/mm/yyyy'))
2706 /* bug9507714 and pps.date_start between paf.effective_start_date and paf.effective_end_date */
2707 and v_calculation_date between pel.effective_start_date
2708 and pel.effective_end_date
2709 and v_calculation_date between pet.effective_start_date
2710 and pet.effective_end_date;
2711
2712 begin
2713
2714 open c_enrollment_startdate(p_accrual_plan_id,p_assignment_id,p_calculation_date);
2715 fetch c_enrollment_startdate into l_enrollment_startdate;
2716 close c_enrollment_startdate;
2717
2718 return l_enrollment_startdate;
2719
2720 end au_get_enrollment_startdate;
2721 /*End --Bug7607177 */
2722
2723 END hr_au_holidays;