1 package body pay_us_pto_accrual as
2 /* $Header: pyusptoa.pkb 120.1 2005/10/04 03:38:12 schauhan noship $
3 ******************************************************************
4 * *
5 * Copyright (C) 1993 Oracle Corporation. *
6 * All rights reserved. *
7 * *
8 * This material has been provided pursuant to an agreement *
9 * containing restrictions on its use. The material is also *
10 * protected by copyright law. No part of this material may *
11 * be copied or distributed, transmitted or transcribed, in *
12 * any form or by any means, electronic, mechanical, magnetic, *
13 * manual, or otherwise, or disclosed to third parties without *
14 * the express written permission of Oracle Corporation, *
15 * 500 Oracle Parkway, Redwood City, CA, 94065. *
16 * *
17 ******************************************************************
18 Name : pay_us_pto_accrual
19 Description : This package holds building blocks used in PTO accrual
20 calculation.
21 Uses : hr_utility
22 Change List
23 -----------
24 Date Name Vers Bug No Description
25 ---- ---- ---- ------ -----------
26 FEB-16-1994 RMAMGAIN 1.0 Created with following proc.
27 . get_accrual
28 . get_accrual_for_plan
29 . get_first_accrual_period
30 . ceiling_calc
31
32 24-NOV-1994 RFINE Suppressed index on
33 business_group_id
34
35 14-JUN-1995 HPARICHA 40.6 287032 Corrected length of service
36 287076 calculation and accrual
37 entitlement logic.
38 10-OCT-1995 JTHURING 40.9 Added missing '/', exit
39 11-OCT-1995 JTHURING 40.10 Removed spurious IF clause:
40 "if P_net_accrual > P_current_ceiling then
41 P_net_accrual := P_current_ceiling;"
42 06-DEC-1995 AMILLS 40.11 Changed date format to DDMMYYYY
43 (on one check of end date)
44 for translation.
45 19-Jan-96 rfine 40.12 305751 Changed cursor csr_get_time_periods in
46 proc get_accrual_for_plan so it gets
47 the correct time periods for the
48 accrual period. Also allowed a period
49 after six months eligiblity to count if
50 its start date matches the six month
51 anniversary. (i.e. if you join on 1 Jan
52 and periods start on the first of the
53 month, you start accruing on 1 Jul, not
54 1 Aug.
55 04-Nov-96 khabibul 40.13 367438 added close cursor XXX at various places
56 as the cursors were not closed at the right
57 time especially when an error was raised. This
58 left the open cursor's open and during the next
59 call to this package (same session) the db was
60 in a confusion state and gave spurious messages.
61 Also included a new message which is raised if the
62 effective date is not within the range of payroll
63 time periods.
64 13-Nov-96 lwthomps 40.14 Added a performance fix to csr_get_plan_details.
65 15-NOV-96 gpaytonm 40.15 Added close cursor csr_get_period
66
67 25-Mar-98 lwthomps 40.16(110.1) Truncated date coming in from
68 check writer for bug: 464550
69 21-May-98 Djeng 110.2 fixed bug 672443
70 23-Mar-99 Sdoshi 115.2 Flexible Dates Conversion
71 08-APR-99 djoshi Verfied and converted for Canonical
72 Complience of Date
73 21-May-01 dcasemor 115.10 Removed assignment_action_id check when deciding
74 whether to use hard-coded or Fast Formula-based
75 PTO solution.
76 22-May-01 dcasemor 115.11 Convert assignment_action_id to -1 if
77 a null is passed or defaulted. This
78 prevents an error running the formulae.
79 16-Oct-02 dcasemor 115.12 2628433 Added delete_plan_from_cache and
80 use_fast_formula. These remove the dependency
81 on the "Use FF-based PTO Accruals" profile
82 option.
83
84 */
85 --
86 -- Private PL/SQL table to cache a list of accrual plans.
87 --
88 TYPE per_plans IS TABLE OF BOOLEAN INDEX BY binary_integer;
89 g_plan_list per_plans;
90 g_package VARCHAR2(30) := 'pay_us_pto_accrual.';
91
92 --
93 ------------------------- delete_plan_from_cache ----------------------------
94 --
95 PROCEDURE delete_plan_from_cache (p_plan_id IN NUMBER)
96 IS
97
98 BEGIN
99
100 IF g_plan_list.exists(p_plan_id) THEN
101 --
102 -- Delete the plan from the cache.
103 --
104 g_plan_list.DELETE(p_plan_id);
105
106 END IF;
107
108 END delete_plan_from_cache;
109 --
110 ------------------------- use_fast_formula ----------------------------
111 --
112 FUNCTION use_fast_formula
113 (p_effective_date IN DATE
114 ,p_plan_id IN NUMBER) RETURN BOOLEAN
115 IS
116
117 --
118 -- Fetches FALSE if the old 10.7 hard-coded PTO rules can be used
119 -- instead of the Fast Formula. The sole reason for doing this is
120 -- because its faster to execute PL/SQL than Fast Formula so improves
121 -- the performance of batch processes such as Checkwriter.
122 --
123 CURSOR csr_use_ff IS
124 SELECT NULL
125 FROM pay_accrual_plans pap
126 ,ff_formulas_f ff
127 WHERE pap.accrual_plan_id = p_plan_id
128 AND pap.accrual_formula_id = ff.formula_id
129 AND p_effective_date BETWEEN
130 ff.effective_start_date and ff.effective_end_date
131 AND (ff.formula_name = 'PTO_PAYROLL_CALCULATION'
132 OR (ff.formula_name = 'PTO_PAYROLL_BALANCE_CALCULATION' AND
133 pap.defined_balance_id IS NULL));
134
135 l_return BOOLEAN := TRUE;
136 l_dummy NUMBER;
137
138 BEGIN
139
140 --
141 -- Check to see if this plan has already been cached.
142 --
143 IF g_plan_list.exists(p_plan_id) THEN
144
145 l_return := g_plan_list(p_plan_id);
146
147 ELSE
148
149 --
150 -- The plan has not been cached. Calculate if the Fast Formula
151 -- must be used and cache the value.
152 --
153 OPEN csr_use_ff;
154 FETCH csr_use_ff INTO l_dummy;
155 --
156 -- If the cursor returns no rows, l_return will default to its
157 -- declared value of TRUE.
158 --
159 IF csr_use_ff%FOUND THEN
160 l_return := FALSE;
161 END IF;
162
163 CLOSE csr_use_ff;
164
165 g_plan_list(p_plan_id) := l_return;
166
167 END IF;
168
169 RETURN l_return;
170
171 END use_fast_formula;
172 --
173 ------------------------- get_accrual ----------------------------
174 --
175 FUNCTION get_accrual
176 ( P_assignment_id number,
177 P_calculation_date date,
178 P_plan_id number DEFAULT NULL,
179 P_plan_category varchar2 DEFAULT NULL)
180 RETURN Number is
181 --
182 -- Function calls the actual proc. which will calc. accrual and pass back all
183 -- the details in formula we will call functions so this will be the cover
184 -- function to call the proc.
185 --
186 l_accrual number := 0;
187 --
188 c_date date := P_calculation_date;
189 n1 number;
190 n2 number;
191 n3 number;
192 d1 date;
193 d2 date;
194 d3 date;
195 d4 date;
196 d5 date;
197 d6 date;
198 d7 date;
199 p_mod varchar2(1) := 'N';
200 --
201 BEGIN
202 --
203
204 pay_us_pto_accrual.accrual_calc_detail(
205 P_assignment_id => P_assignment_id,
206 P_calculation_date => c_date,
207 P_plan_id => P_plan_id,
208 P_plan_category => P_plan_category,
209 P_accrual => l_accrual,
210 P_payroll_id => n1,
211 P_first_period_start => d1,
212 P_first_period_end => d2,
213 P_last_period_start => d3,
214 P_last_period_end => d4,
215 P_cont_service_date => d5,
216 P_start_date => d6,
217 P_end_date => d7,
218 P_current_ceiling => n2,
219 P_current_carry_over => n3);
220 --
221 IF l_accrual is null
222 THEN
223 l_accrual := 0;
224 END IF;
225 --
226 RETURN(l_accrual);
227 --
228 END get_accrual;
229 --
230 ------------------------- accrual_calc_detail ------------------------------
231 --
232 -- This procedure can be called directly this procedure will return start
233 -- date, end dates etc. which can be used by CO or net calc routines.
234 --
235 PROCEDURE accrual_calc_detail
236 (P_assignment_id IN number,
237 P_calculation_date IN OUT nocopy date,
238 P_plan_id IN number DEFAULT NULL,
239 P_plan_category IN varchar2 DEFAULT NULL,
240 P_mode IN varchar2 DEFAULT 'N',
241 P_accrual OUT nocopy number,
242 P_payroll_id IN OUT nocopy number,
243 P_first_period_start IN OUT nocopy date,
244 P_first_period_end IN OUT nocopy date,
245 P_last_period_start IN OUT nocopy date,
246 P_last_period_end IN OUT nocopy date,
247 P_cont_service_date OUT nocopy date,
248 P_start_date OUT nocopy date,
249 P_end_date OUT nocopy date,
250 P_current_ceiling OUT nocopy number,
251 P_current_carry_over OUT nocopy number) IS
252 -- Get Plan details
253 -- lwthomps disabled an index on pev, 13-NOV-1996
254 CURSOR csr_get_plan_details ( P_business_group Number) is
255 select pap.accrual_plan_id,
256 pap.accrual_plan_element_type_id,
257 pap.accrual_units_of_measure,
258 pap.ineligible_period_type,
259 pap.ineligible_period_length,
260 pap.accrual_start,
261 pev.SCREEN_ENTRY_VALUE,
262 pee.element_entry_id
263 from pay_accrual_plans pap,
264 pay_element_entry_values_f pev,
265 pay_element_entries_f pee,
266 pay_element_links_f pel,
267 pay_element_types_f pet,
268 pay_input_values_f piv
269 where ( pap.accrual_plan_id = p_plan_id OR
270 pap.accrual_category = P_plan_category )
271 and pap.business_group_id + 0 = P_business_group
272 and pap.accrual_plan_element_type_id = pet.element_type_id
273 and P_calculation_date between pet.effective_start_date and
274 pet.effective_end_date
275 and pet.element_type_id = pel.element_type_id
276 and P_calculation_date between pel.effective_start_date and
277 pel.effective_end_date
278 and pel.element_link_id = pee.element_link_id
279 and pee.assignment_id = P_assignment_id
280 and P_calculation_date between pee.effective_start_date and
281 pee.effective_end_date
282 and piv.element_type_id =
283 pap.accrual_plan_element_type_id
284 and piv.name = 'Continuous Service Date'
285 and P_calculation_date between piv.effective_start_date and
286 piv.effective_end_date
287 and pev.element_entry_id = pee.element_entry_id
288 and pev.input_value_id + 0 = piv.input_value_id
289 and P_calculation_date between pev.effective_start_date and
290 pev.effective_end_date;
291 --
292 --
293 -- Local Variable
294 --
295 l_asg_eff_start_date date := null;
296 l_asg_eff_end_date date := null;
297 l_business_group_id number := null;
298 l_service_start_date date := null;
299 l_termination_date date := null;
300 --
301 l_calc_period_num number := 0;
302 l_calc_start_date date := null;
303 l_calc_end_date date := null;
304 --
305 l_number_of_period number := 0;
306 --
307 l_acc_plan_type_id number := 0;
308 l_acc_plan_ele_type number := 0;
309 l_acc_uom varchar2(30) := null;
310 l_inelig_period varchar2(30) := null;
311 l_inelig_p_length number := 0;
312 l_accrual_start varchar2(30) := null;
313 l_cont_service_date date := null;
314 l_csd_screen_value varchar2(30) := null;
315 l_element_entry_id number := 0;
316 --
317 l_plan_start_date date := null;
318 --
319 l_total_accrual number := 0;
320 l_plan_accrual number := 0;
321 --
322 l_temp varchar2(30) := null;
323 l_temp_date date := null;
324 --
325 p_param_first_pstdt date := null;
326 p_param_first_pendt date := null;
327 p_param_first_pnum number := 0;
328 p_param_acc_calc_edt date := null;
329 p_param_acc_calc_pno number := 0;
330 --
331 -- Main process
332 --
333 BEGIN
334 --
335 P_payroll_id := 0;
336 P_first_period_start := null;
337 P_first_period_end := null;
338 P_last_period_start := null;
339 P_last_period_end := null;
340 --
341 hr_utility.set_location('get_accrual',5);
342 ---
343 --- If both param null. RETURN
344 --
345 IF P_plan_id is null AND P_plan_category is null
346 THEN
347 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
348 hr_utility.set_message_token('PROCEDURE','get_accrual');
349 hr_utility.set_message_token('STEP','1');
350 hr_utility.raise_error;
351 END IF;
352
353 OPEN csr_get_payroll(P_assignment_id, P_calculation_date);
354 FETCH csr_get_payroll INTO P_payroll_id,
355 l_asg_eff_start_date,
356 l_asg_eff_end_date,
357 l_business_group_id,
358 l_service_start_date,
359 l_termination_date;
360 IF csr_get_payroll%NOTFOUND
361 THEN
362 CLOSE csr_get_payroll;
363 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
364 hr_utility.set_message_token('PROCEDURE','get_accrual');
365 hr_utility.set_message_token('STEP','2');
366 hr_utility.raise_error;
367 END IF;
368 CLOSE csr_get_payroll;
369 hr_utility.set_location('get_accrual',10);
370 --
371 -- Get start and end date for the Calculation date
372 --
373 hr_utility.set_location('get_accrual',15);
374
375 OPEN csr_get_period(P_payroll_id, P_calculation_date);
376 FETCH csr_get_period INTO l_calc_period_num,
377 l_calc_start_date,
378 l_calc_end_date;
379 IF csr_get_period%NOTFOUND
380 THEN
381 CLOSE csr_get_period;
385 -- hr_utility.set_message_token('STEP','3');
382 hr_utility.set_message(801,'HR_51731_PTO_DATE_OUT_TIMEPRD');
383 -- hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
384 -- hr_utility.set_message_token('PROCEDURE','get_accrual');
386 hr_utility.raise_error;
387 END IF;
388 CLOSE csr_get_period;
389 hr_utility.set_location('get_accrual',20);
390 --
391 -- Partial first period if start
392 --
393 -- Set return dates for the net process if nothing to accrue in this period
394 --
395 P_start_date := l_calc_start_date;
396 P_end_date := P_calculation_date;
397 --
398 --
399 /*
400 -- 14 JUN 1995: HPARICHA removed this logic until it can be explained why it's
401 -- required. "Partial first period is start"..?..
402
403 IF l_calc_period_num = 1 AND P_calculation_date < l_calc_end_date
404 THEN
405 P_accrual := 0;
406 ELSE
407 */
408
409 --
410 -- Get total number of periods for the year of calculation
411 --
412
413 OPEN csr_get_total_periods(P_payroll_id, l_calc_end_date);
414 FETCH csr_get_total_periods INTO P_first_period_start,
415 P_first_period_end,
416 P_last_period_start,
417 P_last_period_end,
418 l_number_of_period;
419 IF csr_get_total_periods%NOTFOUND
420 THEN
421 CLOSE csr_get_total_periods;
422 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
423 hr_utility.set_message_token('PROCEDURE','get_accrual');
424 hr_utility.set_message_token('STEP','4');
425 hr_utility.raise_error;
426 END IF;
427 CLOSE csr_get_total_periods;
428 -- Set l_number_of_period such that it is based on NUMBER_PER_FISCAL_YEAR
429 -- for period type of payroll. Ie. The number returned from
430 -- csr_get_total_periods is the number of periods defined for this payroll
431 -- in the given calendar year - so payrolls defined mid-year accrue at a
432 -- different rate than if it had a full year of payroll periods.
433 --
434 SELECT number_per_fiscal_year
435 INTO l_number_of_period
436 FROM per_time_period_types TPT,
437 pay_payrolls_f PPF
438 WHERE TPT.period_type = PPF.period_type
439 AND PPF.payroll_id = P_payroll_id
440 AND l_calc_end_date BETWEEN PPF.effective_start_date
441 AND PPF.effective_end_date;
442 --
443 hr_utility.set_location('get_accrual',25);
444 --
445 -- In case of carry over a dummy date of 31-JUL-YYYY is passed in order to get
446 -- the no. of periods first and last period od that year etc. Check if P_mode
447 -- is 'C' then set the calculation date to the end date of last period and
448 -- get period number for that period again.
449 --
450 hr_utility.set_location('get_accrual',27);
451 IF P_mode = 'C'
452 THEN
453 l_calc_period_num := l_number_of_period;
454 l_calc_start_date := P_last_period_start;
455 l_calc_end_date := P_last_period_end;
456 P_calculation_date:= nvl(l_termination_date,P_last_period_end);
457 END IF;
458 --
459 --
460 /* Replacing these 3 lines w/call to csr_get_period for 1st period start date.
461 Remember the first period number is NOT NECESSARILY "1".
462 "p_param_first..." become the beginning of accrual time, need to be
463 set according to accrual plans' "Accrual Start Rule" - ie.
464 Accrual Start Rule Accrual Begins
465 Beginning of Year Beginning of year FOLLOWING year of hire.
466 Hire Date As of beginning of month of hire.
467 6 Months After Hire As of beginning of the first full pay period
468 following the 6 month anniversary of hire date.
469
470 Note: "Hire Date" above refers to the actual period of service hire date
471 OR the "Continuous Service Date" element entry value on the accrual
472 plan element entry. This "Continuous Service Date" entry value
473 overrides the employee's period of service start (Hire) date.
474
475 ALSO: Does "Beginning of Year" need to deal with case of
476 Hire Date = '01-JAN-...." of a calendar year?
477
478 p_param_first_pnum := 1;
479 p_param_first_pstdt := P_first_period_start;
480 p_param_first_pendt := P_first_period_end;
481
482 */
483
484 hr_utility.set_location('get_accrual',30);
485 OPEN csr_get_period (P_payroll_id, P_first_period_start);
486 FETCH csr_get_period INTO p_param_first_pnum,
487 p_param_first_pstdt,
488 p_param_first_pendt;
489 IF csr_get_period%NOTFOUND
490 THEN
491 CLOSE csr_get_period;
492 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
493 hr_utility.set_message_token('PROCEDURE','get_accrual');
494 hr_utility.set_message_token('STEP','5');
495 hr_utility.raise_error;
496 END IF;
497 CLOSE csr_get_period;
498 --
499 -- Check termination date and adjust end date of the last calc Period
500 --
501 OPEN csr_get_period (P_payroll_id,
502 nvl(l_termination_date,P_calculation_date));
503 FETCH csr_get_period INTO p_param_acc_calc_pno,
504 l_temp_date,
505 p_param_acc_calc_edt;
506 IF csr_get_period%NOTFOUND
507 THEN
511 hr_utility.set_message_token('STEP','6');
508 CLOSE csr_get_period;
509 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
510 hr_utility.set_message_token('PROCEDURE','get_accrual');
512 hr_utility.raise_error;
513 END IF;
514 CLOSE csr_get_period;
515 --
516 hr_utility.set_location('get_accrual',35);
517 --
518 -- No accruals for the partial periods
519 --
520 IF nvl(l_termination_date,P_calculation_date) < p_param_acc_calc_edt
521 THEN
522 hr_utility.set_location('get_accrual',36);
523 p_param_acc_calc_pno := p_param_acc_calc_pno - 1;
524 p_param_acc_calc_edt := l_temp_date - 1;
525
526 END IF;
527 --
528 -- Open plan cursor and check at least one plan should be there
529 --
530 hr_utility.set_location('get_accrual',40);
531 OPEN csr_get_plan_details(l_business_group_id);
532 FETCH csr_get_plan_details INTO l_acc_plan_type_id,
533 l_acc_plan_ele_type,
534 l_acc_uom,
535 l_inelig_period,
536 l_inelig_p_length,
537 l_accrual_start,
538 l_csd_screen_value,
539 l_element_entry_id;
540 IF csr_get_plan_details%NOTFOUND
541 THEN
542 CLOSE csr_get_plan_details;
543 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
544 hr_utility.set_message_token('PROCEDURE','get_accrual');
545 hr_utility.set_message_token('STEP','7');
546 hr_utility.raise_error;
547 END IF;
548 --
549 -- Loop thru all the plans and call function to calc. accruals for a plan
550 --
551 hr_utility.set_location('get_accrual',45);
552 LOOP
553 l_temp_date := null;
554 --
555 hr_utility.set_location('get_accrual',50);
556 --
557 -- "Continous Service Date" is ALWAYS determined by:
558 -- 1. "Continuous Service Date" entry value on accrual plan.
559 -- 2. Hire Date of current period of service (ie. in absence of 1.)
560 --
561 IF l_csd_screen_value is null
562 THEN
563 hr_utility.set_location('get_accrual',51);
564 l_cont_service_date := l_service_start_date;
565 ELSE
566 hr_utility.set_location('get_accrual',52);
567 l_cont_service_date := fnd_date.canonical_to_date(l_csd_screen_value);
568 END IF;
569 --
570 -- The "p_param_first..." variables determine when accrual begins for this
571 -- plan and assignment. Accrual begins according to "Accrual Start Rule" and
572 -- hire date as follows:
573 -- Accrual Start Rule Begin Accrual on...
574 -- ================== ==================================================
575 -- Beginning of Year First period of new calendar year FOLLOWING hire date.
576 -- Hire Date First period following hire date.
577 -- 6 Months After Hire First period following 6 month anniversary of hire date.
578 -- NOTE: "Hire Date" is the "Continuous Service Date" as determined above.
579 --
580 IF l_accrual_start = 'BOY'
581 THEN
582 l_temp_date := TRUNC(ADD_MONTHS(l_cont_service_date,12),'YEAR');
583 OPEN csr_get_period (P_payroll_id, l_temp_date);
584 FETCH csr_get_period INTO p_param_first_pnum,
585 p_param_first_pstdt,
586 p_param_first_pendt;
587 IF csr_get_period%NOTFOUND
588 THEN
589 CLOSE csr_get_period;
590 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
591 hr_utility.set_message_token('PROCEDURE','get_accrual');
592 hr_utility.set_message_token('STEP','8');
593 hr_utility.raise_error;
594 END IF;
595 CLOSE csr_get_period;
596 l_temp_date := null;
597 ELSIF l_accrual_start = 'HD'
598 THEN
599 NULL;
600 -- p_param_first... vars have been set above (location get_accrual.30)
601 ELSIF l_accrual_start = 'PLUS_SIX_MONTHS'
602 THEN
603 --
604 -- Actually get the period in force the day before the six months is up.
605 -- This is because we subsequently get the following period as the one
606 -- in which accruals should start. If a period starts on the six
607 -- month anniversary, the asg should qualify from that period, and
608 -- not have to wait for the next one. Example:
609 --
610 -- Assume monthly periods.
611 --
612 -- l_cont_service_date = 02-Jan-95
613 -- six month anniversary = 02-Jul-95
614 -- accruals start on 01-Aug-95
615 --
616 -- l_cont_service_date = 01-Jan-95
617 -- six month anniversary = 01-Jul-95
618 -- accruals should start on 01-Jul-95, not 01-Aug-95
619 --
620 -- RMF 19-Jan-96.
621 --
622 OPEN csr_get_period (P_payroll_id,
623 ADD_MONTHS(l_cont_service_date,6) -1 );
624 FETCH csr_get_period INTO p_param_first_pnum,
625 p_param_first_pstdt,
626 l_temp_date;
627 IF csr_get_period%NOTFOUND
628 THEN
629 CLOSE csr_get_period;
630 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
631 hr_utility.set_message_token('PROCEDURE','get_accrual');
635 CLOSE csr_get_period;
632 hr_utility.set_message_token('STEP','10');
633 hr_utility.raise_error;
634 END IF;
636 --
637 OPEN csr_get_period (P_payroll_id, l_temp_date + 1);
638 FETCH csr_get_period INTO p_param_first_pnum,
639 p_param_first_pstdt,
640 p_param_first_pendt;
641 IF csr_get_period%NOTFOUND
642 THEN
643 CLOSE csr_get_period;
644 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
645 hr_utility.set_message_token('PROCEDURE','get_accrual');
646 hr_utility.set_message_token('STEP','11');
647 hr_utility.raise_error;
648 END IF;
649 CLOSE csr_get_period;
650 l_temp_date := null;
651 END IF;
652 hr_utility.set_location('get_accrual',55);
653 --
654 -- Add period of ineligibility
655 --
656 IF l_accrual_start <> 'PLUS_SIX_MONTHS' AND
657 l_inelig_p_length > 0
658 THEN
659 hr_utility.set_location('get_accrual',60);
660 IF l_inelig_period = 'BM'
661 THEN
662 l_temp_date := ADD_MONTHS(l_cont_service_date,
663 (l_inelig_p_length * 2));
664 ELSIF l_inelig_period = 'F'
665 THEN
666 l_temp_date := fnd_date.canonical_to_date(to_char(l_cont_service_date +
667 (l_inelig_p_length * 14),'YYYY/MM/DD'));
668 ELSIF l_inelig_period = 'CM'
669 THEN
670 l_temp_date := ADD_MONTHS(l_cont_service_date,
671 l_inelig_p_length);
672 ELSIF l_inelig_period = 'LM'
673 THEN
674 l_temp_date := fnd_date.canonical_to_date(to_char(l_cont_service_date +
675 (l_inelig_p_length * 28),'YYYY/MM/DD'));
676 ELSIF l_inelig_period = 'Q'
677 THEN
678 l_temp_date := ADD_MONTHS(l_cont_service_date,
679 (l_inelig_p_length * 3));
680 ELSIF l_inelig_period = 'SM'
681 THEN
682 l_temp_date := ADD_MONTHS(l_cont_service_date,
683 (l_inelig_p_length/2));
684 ELSIF l_inelig_period = 'SY'
685 THEN
686 l_temp_date := ADD_MONTHS(l_cont_service_date,
687 (l_inelig_p_length * 6));
688 ELSIF l_inelig_period = 'W'
689 THEN
690 l_temp_date := fnd_date.canonical_to_date(to_char(l_cont_service_date +
691 (l_inelig_p_length * 7),'YYYY/MM/DD'));
692 ELSIF l_inelig_period = 'Y'
693 THEN
694 l_temp_date := ADD_MONTHS(l_cont_service_date,
695 (l_inelig_p_length * 12));
696 END IF;
697 END IF;
698
699 --
700 -- Determine start and end date and setup return parmas.
701 -- check Period of Service start date, plan element entry start date
702 -- if later then first period start. Accrual period start date accordingly.
703 --
704 hr_utility.set_location('get_accrual',65);
705 select min(effective_start_date)
706 into l_plan_start_date
707 from pay_element_entries_f
708 where element_entry_id = l_element_entry_id;
709 hr_utility.set_location('get_accrual',67);
710 ---
711
712 --- Set the return params
713 --
714 P_cont_service_date := l_cont_service_date;
715 P_start_date := GREATEST(l_service_start_date,l_cont_service_date,
716 l_plan_start_date,P_first_period_start);
717 P_end_date := LEAST(NVL(L_termination_date,P_calculation_date)
718 ,P_calculation_date);
719
720 --
721 hr_utility.set_location('get_accrual',68);
722 IF ( l_temp_date is not null AND
723 l_temp_date >= p_param_acc_calc_edt ) OR
724 l_cont_service_date >= p_param_acc_calc_edt OR
725
726 p_param_first_pstdt >= p_param_acc_calc_edt
727
728 THEN
729 hr_utility.set_location('get_accrual',70);
730 l_plan_accrual := 0;
731 ELSE
732 --
733 -- Set the Start Date appropriately.
734 -- #305751. Don't understand why this code is here at all, seeing as these
735 -- parameters have already been set up above. However, I'll leave the code
736 -- alone, except to prevent it from resetting a later start date to earlier,
737 -- which sometimes happened on 6 Month plans. Added a test to prevent the
738 -- date being reset if it's already been set, to later than l_temp_date
739 -- below. RMF 18-Jan-96.
740 --
741 l_temp_date := GREATEST(l_service_start_date,l_cont_service_date,
742 l_plan_start_date);
743 --
744 IF l_temp_date > P_first_period_start
745 AND l_temp_date > nvl(p_param_first_pstdt, l_temp_date - 1)
746 THEN
747 hr_utility.set_location('get_accrual',71);
748 OPEN csr_get_period (P_payroll_id, l_temp_date);
749 FETCH csr_get_period INTO p_param_first_pnum,
750 p_param_first_pstdt,
751 p_param_first_pendt;
752 IF csr_get_period%NOTFOUND
753 THEN
754 CLOSE csr_get_period;
758 hr_utility.raise_error;
755 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
756 hr_utility.set_message_token('PROCEDURE','get_accrual');
757 hr_utility.set_message_token('STEP','12');
759 END IF;
760 CLOSE csr_get_period;
761 hr_utility.set_location('get_accrual',80);
762 --
763 -- No Accruals fro the partial periods. First period to start the
764 -- accrual will be next one.
765 --
766 IF l_temp_date > p_param_first_pstdt
767 THEN
768 hr_utility.set_location('get_accrual',85);
769 p_param_first_pendt := p_param_first_pendt +1;
770 OPEN csr_get_period (P_payroll_id, p_param_first_pendt);
771 FETCH csr_get_period INTO p_param_first_pnum,
772 p_param_first_pstdt,
773 p_param_first_pendt;
774 IF csr_get_period%NOTFOUND
775 THEN
776 CLOSE csr_get_period;
777 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
778 hr_utility.set_message_token('PROCEDURE','get_accrual');
779 hr_utility.set_message_token('STEP','13');
780 hr_utility.raise_error;
781 END IF;
782 CLOSE csr_get_period;
783 END IF;
784 END IF;
785 --
786 -- Call Function to Calculate accruals for a plan
787 --
788 IF p_param_acc_calc_edt < P_first_period_end
789 THEN
790 l_plan_accrual := 0;
791 ELSE
792 --
793 hr_utility.set_location('get_accrual_for_plan',90);
794 pay_us_pto_accrual.get_accrual_for_plan
795 ( p_plan_id => l_acc_plan_type_id,
796 p_first_p_start_date => p_param_first_pstdt,
797 p_first_p_end_date => p_param_first_pendt,
798 p_first_calc_P_number => p_param_first_pnum,
799 p_accrual_calc_p_end_date => p_param_acc_calc_edt,
800 P_accrual_calc_P_number => p_param_acc_calc_pno,
801 P_number_of_periods => l_number_of_period,
802 P_payroll_id => P_payroll_id,
803 P_assignment_id => P_assignment_id,
804 P_plan_ele_type_id => l_acc_plan_ele_type,
805 P_continuous_service_date => l_cont_service_date,
806 P_Plan_accrual => l_plan_accrual,
807 P_current_ceiling => P_current_ceiling,
808 P_current_carry_over => P_current_carry_over);
809 END IF;
810 --
811 END IF;
812 --
813 -- Add accrual to the total and Fetch next set of plan
814 --
815 hr_utility.set_location('get_accrual',95);
816 l_total_accrual := l_total_accrual + l_plan_accrual;
817 l_plan_accrual := 0;
818
819 FETCH csr_get_plan_details INTO l_acc_plan_type_id,
820 l_acc_plan_ele_type,
821 l_acc_uom,
822 l_inelig_period,
823 l_inelig_p_length,
824 l_accrual_start,
825 l_csd_screen_value,
826 l_element_entry_id;
827 --
828
829 EXIT WHEN csr_get_plan_details%NOTFOUND;
830 hr_utility.set_location('get_accrual',100);
831 --
832 END LOOP;
833 --
834 CLOSE csr_get_plan_details;
835 --
836 IF l_total_accrual is null
837 THEN
838 hr_utility.set_location('get_accrual',105);
839 l_total_accrual := 0;
840 END IF;
841 hr_utility.set_location('get_accrual',110);
842 l_total_accrual := round(l_total_accrual,3);
843 P_accrual := l_total_accrual;
844 --
845 -- Partial first period if end
846 --
847 /*
848 END IF; -- Start Date...partial eh?
849 */
850
851 --
852 END accrual_calc_detail;
853 --
854 ---------------- get_accrual_for_plan -------------------------------------
855 --
856 PROCEDURE get_accrual_for_plan
857 ( p_plan_id Number,
858 p_first_p_start_date date,
859 p_first_p_end_date date,
860 p_first_calc_P_number number,
861 p_accrual_calc_p_end_date date,
862 P_accrual_calc_P_number number,
863 P_number_of_periods number,
864 P_payroll_id number,
865 P_assignment_id number,
866 P_plan_ele_type_id number,
867 P_continuous_service_date date,
868 P_Plan_accrual OUT nocopy number,
869 P_current_ceiling OUT nocopy number,
870 P_current_carry_over OUT nocopy number) IS
871 --
872 --
873 CURSOR csr_all_asg_status is
874 select a.effective_start_date,
875 a.effective_end_date,
876 b.PER_SYSTEM_STATUS
877 from per_assignments_f a,
878 per_assignment_status_types b
882 and a.ASSIGNMENT_STATUS_TYPE_ID =
879 where a.assignment_id = P_assignment_id
880 and a.effective_end_date between p_first_p_start_date and
881 to_date('31-12-4712','DD-MM-YYYY')
883 b.ASSIGNMENT_STATUS_TYPE_ID;
884 --
885 --
886 CURSOR csr_get_bands (P_time_worked number ) is
887 select annual_rate,
888 ceiling,
889 lower_limit,
890 upper_limit,
891 max_carry_over
892 from pay_accrual_bands
893 where accrual_plan_id = P_plan_id
894 and P_time_worked >= lower_limit
895 and P_time_worked < upper_limit;
896 --
897 -- #305751 I think this cursor is intended to get all the time periods over
898 -- which the accrual should be calculated. However, it looks as if the select
899 -- only gets the numbered periods for which the asg qualified in its first year.
900 -- So, if they qualified from Aug in year 1, this cursor only ever returns
901 -- the periods from Aug onwards. Perhaps this was put in to make the first
902 -- year work correctly, but it works too widely.
903 --
904 -- Revised the cursor so it picks up all the time periods from the start of
905 -- year to the current point unless the asg only qualified for the plan at
906 -- some point during the year, in which case start from then. The old version
907 -- of the cursor is retained here, commented out.
908 --
909 -- The decode in the cursor means: "If the year for which we're doing the
910 -- calculation is also the year in which the asg qualified for the plan, just
911 -- take it from the first qualifying period; otherwise, take it from the
912 -- first period of the year. RMF 18-Jan96.
913 --
914 -- CURSOR csr_get_time_periods is
915 -- select start_date,
916 -- end_date,
917 -- period_num
918 -- from per_time_periods
919 -- where to_char(end_date,'YYYY') =
920 -- to_char(p_first_p_end_date,'YYYY')
921 -- and end_date <= p_accrual_calc_p_end_date
922 -- and period_num >= p_first_calc_P_number
923 -- and payroll_id = p_payroll_id
924 --ORDER by period_num;
925 --
926 CURSOR csr_get_time_periods is
927 select start_date,
928 end_date,
929 period_num
930 from per_time_periods
931 where to_char(end_date,'YYYY') =
932 to_char(p_accrual_calc_p_end_date,'YYYY')
933 and end_date <= p_accrual_calc_p_end_date
934 and period_num >=
935 decode (to_char(p_first_p_start_date,'YYYY'),
936 to_char(p_accrual_calc_p_end_date,'YYYY'),
937 p_first_calc_P_number, 1)
938 and payroll_id = p_payroll_id
939 ORDER by period_num;
940 --
941 --
942 --Local varaiables
943 l_start_Date date :=null;
944 l_end_date date :=null;
945 l_period_num number := 0;
946 l_asg_eff_start_date date := null;
947 l_asg_eff_end_date date := null;
948 l_asg_status varchar2(30) := null;
949 l_acc_rate_pp_1 number := 0;
950 l_acc_rate_pp_2 number := 0;
951 l_acc_deds number := 0;
952 l_annual_rate number := 0;
953 l_ceiling_1 number := 0;
954 l_ceiling_2 number := 0;
955 l_carry_over_1 number := 0;
956 l_carry_over_2 number := 0;
957 l_lower_limit number := 0;
958 l_upper_limit number := 0;
959 l_year_1 number := 0;
960 l_year_2 number := 0;
961 l_accrual number := 0;
962 l_temp number := 0;
963 l_temp2 varchar2(30) := null;
964 l_band_change_date date := null;
965 l_ceiling_flag varchar2(1) := 'N';
966 l_curr_p_stdt date := null;
967 l_curr_p_endt date := null;
968 l_curr_p_num number := 0;
969 l_mult_factor number := 0;
970 l_unpaid_day number := 0;
971 l_vac_taken number := 0;
972 l_prev_end_date date := null;
973 l_running_total number := 0;
974 l_curr_p_acc number := 0;
975 l_working_day number := 0;
976 l_curr_ceiling number := 0;
977 --
978 --
979 BEGIN
980 --
981 hr_utility.set_location('get_accrual_for_plan',1);
982 l_year_1 := TRUNC(ABS(months_between(P_continuous_service_date,
983 P_first_p_end_date)/12));
984 l_year_2 := TRUNC(ABS(months_between(P_continuous_service_date,
985 p_accrual_calc_p_end_date)/12));
986
987 --
988 -- Get the band details using the years of service.
989 --
990 OPEN csr_get_bands (l_year_1);
991 FETCH csr_get_bands INTO l_annual_rate,l_ceiling_1,
992 l_lower_limit,l_upper_limit,
993 l_carry_over_1;
994 hr_utility.set_location('get_accrual_for_plan',5);
995
996 IF csr_get_bands%NOTFOUND THEN
997 l_acc_rate_pp_1 := 0;
998 ELSE
999 l_acc_rate_pp_1 := l_annual_rate/P_number_of_periods;
1000 IF l_ceiling_1 is not null THEN
1001 l_ceiling_flag := 'Y';
1002 END IF;
1003 END IF;
1004 CLOSE csr_get_bands;
1005 hr_utility.set_location('get_accrual_for_plan',10);
1006 --
1010 hr_utility.set_location('get_accrual_for_plan',15);
1007 IF l_year_2 < l_upper_limit and l_acc_rate_pp_1 > 0 THEN
1008 l_acc_rate_pp_2 := 0;
1009 ELSE
1011 OPEN csr_get_bands (l_year_2);
1012 FETCH csr_get_bands INTO l_annual_rate,l_ceiling_2,
1013 l_lower_limit,l_upper_limit,
1014 l_carry_over_2;
1015
1016 IF csr_get_bands%NOTFOUND THEN
1017 -- CLOSE csr_get_bands; -- bug 672443
1018 l_accrual := 0;
1019 P_current_ceiling := 0;
1020 P_current_carry_over := 0;
1021 CLOSE csr_get_bands;
1022 GOTO exit_out;
1023 ELSE
1024 l_acc_rate_pp_2 := l_annual_rate/P_number_of_periods;
1025 IF l_ceiling_1 is not null THEN
1026 l_ceiling_flag := 'Y';
1027 END IF;
1028 CLOSE csr_get_bands;
1029 END IF;
1030 END IF;
1031 hr_utility.set_location('get_accrual_for_plan',20);
1032 --
1033 --
1034 IF ((l_acc_rate_pp_1 <> l_acc_rate_pp_2) AND
1035 l_acc_rate_pp_2 <> 0 ) THEN
1036 l_temp := trunc(ABS(months_between(P_continuous_service_date,
1037 p_accrual_calc_p_end_date))/12) * 12 ;
1038
1039 l_band_change_date := ADD_MONTHS(P_continuous_service_date,l_temp);
1040
1041 ELSE
1042 l_band_change_date := (p_accrual_calc_p_end_date + 2);
1043
1044 END IF;
1045 --
1046 -- Set output params.
1047 --
1048 IF l_ceiling_2 = 0 OR l_ceiling_2 is null
1049 THEN
1050 P_current_ceiling := l_ceiling_1;
1051 ELSE
1052 P_current_ceiling := l_ceiling_2;
1053 END IF;
1054 --
1055 IF l_carry_over_2 = 0 OR l_carry_over_2 is null
1056 THEN
1057 P_current_carry_over := l_carry_over_1;
1058 ELSE
1059 P_current_carry_over := l_carry_over_2;
1060 END IF;
1061 --
1062 hr_utility.set_location('get_accrual_for_plan',25);
1063 OPEN csr_all_asg_status;
1064 FETCH csr_all_asg_status into l_asg_eff_start_date,
1065 l_asg_eff_end_date,
1066 l_asg_status;
1067 hr_utility.set_location('get_accrual_for_plan',30);
1068 --
1069 -- Check if calc method should use ceiling calculation or Non-ceiling
1070 -- calculation. For simplicity if there is any asg. status change then
1071 -- ceiling calculation method is used.
1072 --
1073 IF l_ceiling_flag = 'N'
1074 and (p_first_p_end_date >= l_asg_eff_start_date
1075 and p_accrual_calc_p_end_date <= l_asg_eff_end_date
1076 and l_asg_status = 'ACTIVE_ASSIGN') THEN
1077 --
1078 -- Non Ceiling Calc
1079 --
1080 OPEN csr_get_period(P_Payroll_id, l_band_change_date);
1081 FETCH csr_get_period INTO l_curr_p_num,l_curr_p_stdt,l_curr_p_endt;
1082 hr_utility.set_location('get_accrual_for_plan',35);
1083 IF csr_get_period%NOTFOUND THEN
1084 CLOSE csr_get_period;
1085 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1086 hr_utility.set_message_token('PROCEDURE','get_accrual_for_plan');
1087 hr_utility.set_message_token('STEP','14');
1088 hr_utility.raise_error;
1089 END IF;
1090 CLOSE csr_get_period;
1091 --
1092 -- gpaytonm 15-nov mod - added close csr_get_period
1093 --
1094 --
1095 hr_utility.set_location('get_accrual_for_plan',40);
1096 if l_curr_p_num = 1 AND
1097 p_accrual_calc_p_end_date < l_band_change_date
1098 then
1099 l_curr_p_num := P_number_of_periods;
1100 elsif p_accrual_calc_p_end_date >= l_band_change_date then
1101 l_curr_p_num := l_curr_p_num - 1;
1102 else
1103 l_curr_p_num := P_accrual_calc_P_number;
1104 end if;
1105 --
1106 -- Entitlement from first period to Band change date.
1107 --
1108 l_accrual := l_acc_rate_pp_1 * (l_curr_p_num - (p_first_calc_P_number - 1));
1109 hr_utility.set_location('get_accrual_for_plan',45);
1110 --
1111 -- Entitlement from Band change date to Calc. date
1112 --
1113 IF p_accrual_calc_p_end_date >= l_band_change_date THEN
1114 l_accrual := l_accrual + l_acc_rate_pp_2 * (P_accrual_calc_P_number - l_curr_p_num);
1115 END IF;
1116 ELSE
1117 --
1118 -- Ceiling Calc
1119 --
1120 hr_utility.set_location('get_accrual_for_plan',50);
1121 OPEN csr_get_time_periods;
1122 l_running_total := 0;
1123 l_curr_p_acc := 0;
1124 LOOP
1125 hr_utility.set_location('get_accrual_for_plan',55);
1126 FETCH csr_get_time_periods into l_start_Date,
1127 l_end_date,
1128 l_period_num;
1129 EXIT WHEN csr_get_time_periods%NOTFOUND;
1130 IF l_period_num > P_accrual_calc_P_number then
1131 EXIT;
1132 END IF;
1133 --
1134 -- #305751 Remove the following IF statement. The csr_get_time_periods cursor
1135 -- already restricts which period numbers we get.
1136 --
1137 -- IF l_period_num >= p_first_calc_P_number then
1138 -- Check for Any assignment status change in the current period
1139 --
1140 l_mult_factor := 1;
1141 l_working_day := 0;
1142 l_unpaid_day := 0;
1143 l_vac_taken := 0;
1147 IF l_asg_eff_end_date between l_start_Date and l_end_date
1144 l_prev_end_date := l_asg_eff_end_date;
1145 hr_utility.set_location('get_accrual_for_plan',60);
1146 --
1148 THEN
1149 IF l_asg_status <> 'ACTIVE_ASSIGN' THEN
1150 l_unpaid_day := get_working_days(l_start_Date,
1151 l_asg_eff_end_date);
1152 END IF;
1153 --
1154 --
1155 hr_utility.set_location('get_accrual_for_plan',65);
1156 LOOP
1157 hr_utility.set_location('get_accrual_for_plan',70);
1158 l_prev_end_date := l_asg_eff_end_date;
1159 FETCH csr_all_asg_status into l_asg_eff_start_date,
1160 l_asg_eff_end_date,
1161 l_asg_status;
1162 IF csr_all_asg_status%NOTFOUND THEN
1163 CLOSE csr_all_asg_status;
1164 EXIT;
1165 ELSIF l_asg_status <> 'ACTIVE_ASSIGN' and
1166 l_asg_eff_start_date <= l_end_date
1167 THEN
1168 l_unpaid_day := l_unpaid_day +
1169 get_working_days(l_asg_eff_start_date,
1170 least(l_end_date,l_asg_eff_end_date));
1171 END IF;
1172 EXIT WHEN l_asg_eff_end_date > l_end_date;
1173 END LOOP;
1174 --
1175 --
1176 ELSIF csr_all_asg_status%ISOPEN and l_asg_status <> 'ACTIVE_ASSIGN' THEN
1177 l_mult_factor := 0;
1178 hr_utility.set_location('get_accrual_for_plan',75);
1179 ELSIF NOT (csr_all_asg_status%ISOPEN ) THEN
1180 hr_utility.set_location('get_accrual_for_plan',80);
1181 l_mult_factor := 0;
1182 ELSE
1183 hr_utility.set_location('get_accrual_for_plan',85);
1184 l_mult_factor := 1;
1185 END IF;
1186 --
1187 --
1188 IF l_unpaid_day <> 0 THEN
1189 hr_utility.set_location('get_accrual_for_plan',90);
1190 l_working_day := get_working_days(l_start_Date,l_end_date);
1191 IF l_working_day = l_unpaid_day THEN
1192 l_mult_factor := 0;
1193 ELSE
1194 l_mult_factor := (1 - (l_unpaid_day/l_working_day));
1195 END IF;
1196 END IF;
1197 --
1198 -- Find out vacation and carry over if the method is ceiling
1199 --
1200 IF l_ceiling_flag = 'Y' THEN
1201 hr_utility.set_location('get_accrual_for_plan',95);
1202 OPEN csr_calc_accrual(l_start_Date, l_end_date,
1203 P_assignment_id, P_plan_id);
1204 FETCH csr_calc_accrual INTO l_vac_taken;
1205 IF csr_calc_accrual%NOTFOUND or l_vac_taken is null THEN
1206 l_vac_taken := 0;
1207 END IF;
1208 CLOSE csr_calc_accrual;
1209 END IF;
1210 --
1211 -- Multiply the Accrual rate for the current band and Multiplication
1212 -- Factor to get current period accrual.
1213 --
1214 hr_utility.set_location('get_accrual_for_plan',100);
1215 IF (l_band_change_date between l_start_Date and l_end_date)
1216 OR ( l_band_change_date < l_end_date)
1217 THEN
1218 l_curr_p_acc := l_acc_rate_pp_2 * l_mult_factor;
1219 l_curr_ceiling := l_ceiling_2;
1220 ELSE
1221 l_curr_p_acc := l_acc_rate_pp_1 * l_mult_factor;
1222 l_curr_ceiling := l_ceiling_1;
1223 END IF;
1224 --
1225 --
1226 -- Check for ceiling limits
1227 --
1228 hr_utility.set_location('get_accrual_for_plan',105);
1229 IF l_ceiling_flag = 'Y' THEN
1230 l_running_total := l_running_total + l_vac_taken + l_curr_p_acc;
1231 IF l_running_total > l_curr_ceiling THEN
1232 IF (l_running_total - l_curr_ceiling) < l_curr_p_acc
1233 THEN
1234 l_temp := (l_curr_p_acc -
1235 (l_running_total - l_curr_ceiling));
1236 l_accrual := l_accrual + l_temp;
1237 l_running_total := l_running_total + l_temp;
1238 END IF;
1239 l_running_total := l_running_total - l_curr_p_acc;
1240 ELSE
1241 l_accrual := l_accrual + l_curr_p_acc;
1242 END IF;
1243 ELSE
1244 l_accrual := l_accrual + l_curr_p_acc;
1245 END IF;
1246 hr_utility.set_location('get_accrual_for_plan',110);
1247 --
1248 --
1249 -- #305751 Remove the END IF matching the removed IF above.
1250 --
1251 -- END IF;
1252 --
1253 END LOOP;
1254 --
1255 CLOSE csr_get_time_periods;
1256 --
1257 END IF;
1258 --
1259 --
1260 IF l_accrual is null THEN
1261 l_accrual := 0;
1262 END IF;
1263 --
1264 <<exit_out>>
1265 P_Plan_accrual := l_accrual;
1266 --
1267 --
1268 END get_accrual_for_plan;
1269 --
1270 --------------------------- get_working_days ------------------------
1271 --
1272 FUNCTION get_working_days (P_start_date date,
1273 P_end_date date )
1274 RETURN NUMBER is
1275 l_total_days NUMBER := 0;
1276 l_curr_date DATE := NULL;
1277 l_curr_day VARCHAR2(3) := NULL;
1278 --
1279 BEGIN
1280 --
1281 -- Check for valid range
1282 hr_utility.set_location('get_working_days', 5);
1283 IF p_start_date > P_end_date THEN
1287 --
1284 hr_utility.set_location('get_working_days', 8);
1285 RETURN l_total_days;
1286 END IF;
1288 l_curr_date := P_start_date;
1289 hr_utility.set_location('get_working_days', 10);
1290 LOOP
1291 l_curr_day := TO_CHAR(l_curr_date, 'DY');
1292 hr_utility.set_location('get_working_days', 15);
1293 IF UPPER(l_curr_day) in ('MON', 'TUE', 'WED', 'THU', 'FRI') THEN
1294 l_total_days := l_total_days + 1;
1295 hr_utility.set_location('get_working_days', 20);
1296 END IF;
1297 l_curr_date := l_curr_date + 1;
1298 EXIT WHEN l_curr_date > P_end_date;
1299 END LOOP;
1300 --
1301 RETURN l_total_days;
1302 --
1303 END get_working_days;
1304 --
1305 --
1306 ----------------------- get_net_accrual --------------------------------------
1307 --
1308 FUNCTION get_net_accrual
1309 ( P_assignment_id number,
1310 P_calculation_date date,
1311 P_plan_id number default null,
1312 P_plan_category Varchar2 default null,
1313 P_assignment_action_id number default null)
1314 RETURN NUMBER is
1315 --
1316 --
1317 -- Function calls the actual proc. which will calc. net accrual and pass back
1318 -- the details.In formula we will call functions so this will be the cover
1319 -- function to call the proc.
1320 --
1321
1322 cursor c_asg_details is
1323 select business_group_id,
1324 payroll_id
1325 from per_all_assignments_f
1326 where assignment_id = p_assignment_id
1327 and p_calculation_date between effective_start_date
1328 and effective_end_date;
1329
1330 l_proc varchar2(80) := g_package||'get_net_accrual';
1331 l_entitlement number := 0;
1332 l_payroll_id number;
1333 l_business_group_id number;
1334 l_assignment_action_id number;
1335
1336 --
1337 c_date date := P_calculation_date;
1338 n1 number;
1339 n2 number;
1340 n3 number;
1341 n4 number;
1342 d1 date;
1343 d2 date;
1344 d3 date;
1345 d4 date;
1346 d5 date;
1347 d6 date;
1348 d7 date;
1349 --
1350 BEGIN
1351 --
1352 hr_utility.set_location('Entering: '||l_proc, 10);
1353
1354 IF NOT use_fast_formula(p_effective_date => p_calculation_date
1355 ,p_plan_id => p_plan_id) THEN
1356 --
1357 -- It has been determined that:
1358 -- a) the Fast Formula used by this accrual plan contain
1359 -- the same logic as the old 10.7 PL/SQL code and
1360 -- b) this accrual plan does not store accruals in a
1361 -- payroll balance.
1362 -- For this reason, the old 10.7 code is called because it
1363 -- is significantly faster than executing Fast Formula.
1364 --
1365 hr_utility.set_location(l_proc, 20);
1366
1367 pay_us_pto_accrual.net_accruals(
1368 P_assignment_id => P_assignment_id,
1369 P_calculation_date => c_date,
1370 P_plan_id => P_plan_id,
1371 P_plan_category => P_plan_category,
1372 P_mode => 'N',
1373 P_accrual => n4,
1374 P_net_accrual => l_entitlement,
1375 P_payroll_id => n1,
1376 P_first_period_start => d1,
1377 P_first_period_end => d2,
1378 P_last_period_start => d3,
1379 P_last_period_end => d4,
1380 P_cont_service_date => d5,
1381 P_start_date => d6,
1382 P_end_date => d7,
1383 P_current_ceiling => n2,
1384 P_current_carry_over => n3);
1385
1386 if l_entitlement is null then
1387 l_entitlement := 0;
1388 end if;
1389 --
1390 ELSE
1391 --
1392 -- It has been determined that:
1393 -- a) the Fast Formula used by this accrual plan differ
1394 -- from the logic used in the old 10.7 PL/SQL code or
1395 -- b) this accrual plan stores the accruals in a payroll
1396 -- balance.
1397 -- For either of these reasons, the newer call to
1398 -- get_net_accrual (in per_accrual_calc_functions) is used.
1399 --
1400 -- If the assignment_action_id is passed the accruals are
1401 -- simply retrieved from a payroll balance, if the
1402 -- assignment_action_id is not passed, the Fast Formula
1403 -- must instead be executed.
1404 --
1405 open c_asg_details;
1406 fetch c_asg_details into l_business_group_id,
1407 l_payroll_id;
1408 close c_asg_details;
1409
1410 -- Here we set a null assignment_action_id to -1 to prevent
1411 -- an error running the Accrual formula later.
1412
1413 if p_assignment_action_id is null then
1414 l_assignment_action_id := -1;
1415 else
1416 l_assignment_action_id := p_assignment_action_id;
1417 end if;
1418
1419 hr_utility.set_location(l_proc, 30);
1420
1421 per_accrual_calc_functions.get_net_accrual(
1422 P_assignment_id => p_assignment_id,
1423 P_plan_id => p_plan_id,
1424 P_payroll_id => l_payroll_id,
1425 p_business_group_id => l_business_group_id,
1426 p_assignment_action_id => l_assignment_action_id,
1427 P_calculation_date => p_calculation_date,
1428 p_accrual_start_date => null,
1429 p_accrual_latest_balance => null,
1430 p_calling_point => 'BP',
1431 P_start_date => d1,
1432 P_End_Date => d2,
1433 P_Accrual_End_Date => d3,
1434 P_accrual => n1,
1435 P_net_entitlement => l_entitlement
1436 );
1437 --
1438 end if;
1439
1440 hr_utility.trace('l_entitlement: '||to_char(l_entitlement));
1441
1442 hr_utility.set_location('Leaving: '||l_proc, 90);
1443
1444 RETURN(l_entitlement);
1445 --
1446 END get_net_accrual;
1447 ---
1448 ---
1449 --------------------------- net_accruals -----------------------------------
1450 --
1451 --
1452 -- This procedure can be called directly this procedure will return start
1453 -- date, end dates etc. which can be used by CO.
1454 --
1455 PROCEDURE net_accruals
1456 (P_assignment_id IN number,
1457 P_calculation_date IN OUT nocopy date,
1458 P_plan_id IN number DEFAULT NULL,
1459 P_plan_category IN varchar2 DEFAULT NULL,
1460 P_mode IN varchar2 DEFAULT 'N',
1461 P_accrual IN OUT nocopy number,
1462 P_net_accrual OUT nocopy number,
1463 P_payroll_id IN OUT nocopy number,
1464 P_first_period_start IN OUT nocopy date,
1465 P_first_period_end IN OUT nocopy date,
1466 P_last_period_start IN OUT nocopy date,
1467 P_last_period_end IN OUT nocopy date,
1468 P_cont_service_date OUT nocopy date,
1469 P_start_date IN OUT nocopy date,
1470 P_end_date IN OUT nocopy date,
1471 P_current_ceiling OUT nocopy number,
1472 P_current_carry_over OUT nocopy number) IS
1473 --
1474 --
1475 l_taken number := 0;
1476 --
1477 l_temp number := 0;
1478 --
1479 BEGIN
1480 --
1481 -- Get vaction accrued
1482 --
1483 hr_utility.set_location('get_net_accrual',5);
1484 pay_us_pto_accrual.accrual_calc_detail(
1485 P_assignment_id => P_assignment_id,
1486 P_calculation_date => P_calculation_date,
1487 P_plan_id => P_plan_id,
1488 P_plan_category => P_plan_category,
1489 P_mode => P_mode,
1490 P_accrual => P_accrual,
1491 P_payroll_id => P_payroll_id,
1492 P_first_period_start => P_first_period_start,
1493 P_first_period_end => P_first_period_end,
1494 P_last_period_start => P_last_period_start,
1495 P_last_period_end => P_last_period_end,
1496 P_cont_service_date => P_cont_service_date,
1497 P_start_date => P_start_date,
1498 P_end_date => P_end_date,
1499 P_current_ceiling => P_current_ceiling,
1500 P_current_carry_over => P_current_carry_over);
1501 --
1502 -- Get vac taken purchase etc using net Calc rules.
1503 --
1504
1505 OPEN csr_calc_accrual(P_start_Date, P_end_date,
1506 P_assignment_id, P_plan_id);
1507 FETCH csr_calc_accrual INTO l_taken;
1508 IF csr_calc_accrual%NOTFOUND or
1509 l_taken is null
1510 THEN
1511 l_taken := 0;
1512 END IF;
1513 CLOSE csr_calc_accrual;
1514 hr_utility.set_location('get_net_accrual',20);
1515 --
1516 --
1517 P_net_accrual := ROUND((P_accrual + l_taken),3);
1518
1519 --
1520 -- if mode is carry over then return next years first period start
1521 -- and end dates in P_start_date nad P_end_date params.
1522 --
1523 IF P_mode = 'C'
1524 THEN
1525 OPEN csr_get_period(p_payroll_id,(P_last_period_end +1));
1526 hr_utility.set_location('get_net_accrual',21);
1527 FETCH csr_get_period into l_temp,P_start_date,P_end_date;
1528 IF csr_get_period%NOTFOUND THEN
1529 CLOSE csr_get_period;
1530 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1531 hr_utility.set_message_token('PROCEDURE','net_accruals');
1532 hr_utility.set_message_token('STEP','15');
1533 hr_utility.raise_error;
1534 END IF;
1535 CLOSE csr_get_period;
1536 hr_utility.set_location('get_net_accrual',22);
1537 END IF;
1538 --
1539 --
1540 END net_accruals;
1541 --
1542 END pay_us_pto_accrual;
[Home] [Help]