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