1 package body hr_us_accrual_plans as
2 /* $Header: pyusaccr.pkb 120.3 2006/07/31 07:39:13 risgupta noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1994 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : hr_us_accrual_plans
11 Filename : pyusudfs.sql
12 Change List
13 -----------
14 Date Name Vers Bug No Description
15 ---- ---- ---- ------ -----------
16 26-JAN-95 hparicha 40.0 G1565 Vacation/Sick correlation
17 to Regular Pay - changes to
18 Calc Period Earns.
19 Also need separate fn to calc
20 Vac/Sick Pay as well as a fn
21 to check for entry of Vac/Sick
22 Hours against an accrual plan.
23 09-JUL-95 hparicha 40.1 282299 Check accrual ineligibility.
24 Currently only used from
25 PayMIX.
26 04-OCT-95 ramurthy 40.3 312537 Added NO_DATA_FOUND
27 exception in procedure
28 get_accrual_ineligibility.
29 05-Jan-96 rfine 40.4 323214 Prevented TOO_MANY_ROWS error
30 when > 1 Plan has the same
31 absence element.
32 11-JAN-96 ramurthy 40.5 326766 Changed procedure
33 get_accrual_ineligibility
34 to handle accrual start rules
35 'Hire Date' and 'Beginning
36 of Year' when they do not
37 have any period of
38 ineligibility. That is,
39 the eligible dates are now
40 set accordingly.
41 04-NOV-96 hparicha 40.6 408507 Changed accrual_time_taken
42 function to sum entry values
43 for vacation or sick time taken.
44 16-MAR-99 alogue 115.1 Support of new accrual functionality
45 by use of ineligibility_formula_id.
46 07-APR-99 alogue 115.2 Canonical date support in ff
47 pl sql engine call within
48 get_accrual_ineligibility.
49 08-APR-99 djoshi 115.3 Verfied and converted for Canonical
50 Complience of Date
51 15-APR-99 VMehta 115.4 764244 Accessing the
52 per_periods_of_service through a
53 date effective join
54
55
56 21-Apr-99 scgrant 115.5 Multi-radix changes.
57 --
58 26-FEB-02 Rmonge 115.6 Fix for bug 2006907
59
60 03-SEP-03 rmonge 115.10 Removed NOCOPY from
61 in arguments.
62 20-SEP-05 ghshanka 115.11 bug 4123194 deleted the functions
63 calc_accrual_pay and accrual_time_taken .
64 29-Nov-05 irgonzal 115.12 Bug fix 4762608. Altered
65 get_accrual_ineligibility procedure.
66 Handled scenario when accrual plan
67 does not have a "start date" rule.
68 31-AUG-06 risgupta 115.13 5405255 obsoleted functions being re-added on request
69 of US payroll
70 Description: User-Defined Functions required for US implementations.
71 */
72 --
73 -- **********************************************************************
74
75 --
76 -- Procedure
77 -- get_accrual_ineligibility
78 --
79 -- Purpose
80 -- Check for accrual plan ineligibility period and indicate if the current
81 -- assignment is within the ineligible period - ie. the batch line entry
82 -- for time taken against the accrual should be invalidated.
83 --
84 -- Arguments
85 -- p_iv_id
86 -- p_bg_id
87 -- p_asg_id
88 -- p_sess_date
89 --
90 -- History
91 -- 8th July 1995 Hankins Parichabutr Created.
92 -- 4th Oct 1995 Ranjana Murthy Added NO_DATA_FOUND exception
93 -- 05-Jan-96 rfine 323214 Prevented TOO_MANY_ROWS error when
94 -- > 1 Plan has the same absence element
95 -- 11-JAN-96 ramurthy 326766 Set eligible dates properly for
96 -- start rules 'Hire Date' and
97 -- 'Beginning of Year'.
98 --
99 PROCEDURE get_accrual_ineligibility( p_iv_id IN NUMBER,
100 p_bg_id IN NUMBER,
101 p_asg_id IN NUMBER,
102 p_sess_date IN DATE,
103 p_eligible OUT NOCOPY VARCHAR2
104 ) IS
105
106 v_inel_length NUMBER(2);
107 v_inel_period_type VARCHAR2(30);
108 v_start_rule VARCHAR2(30);
109 v_service_start DATE;
110 v_inel_days NUMBER(9);
111 v_semi_days NUMBER(9);
112 v_eligible_date DATE;
113 v_plan_id NUMBER(9);
114 v_formula_id NUMBER(9);
115 l_inputs ff_exec.inputs_t;
116 l_outputs ff_exec.outputs_t;
117
118 CURSOR plan_for_input_value IS
119 SELECT nvl(ineligible_period_length, 0),
120 ineligible_period_type,
121 accrual_start,
122 accrual_plan_id,
123 ineligibility_formula_id
124 FROM PAY_ACCRUAL_PLANS
125 WHERE pto_input_value_id = p_iv_id
126 AND business_group_id = p_bg_id;
127
128 BEGIN
129
130 --
131 -- Check that this input value is used for accrual pto recording.
132 -- #323214 Redefined as an explicit cursor with an unlooped fetch.
133 -- This prevents a TOO_MANY_ROWS error when > 1 Plan has the same
134 -- absence element.
135 --
136 -- However, if this is the case, note that you cannot guarantee which
137 -- plan for the absence element the details are being retrieved from. There
138 -- may come a subsequent Enhancement Request to prevent users from setting
139 -- up more than one plan with the same absence element. RMF 05-Jan-96.
140 --
141 hr_utility.set_location('get_accrual_ineligibility', 1);
142
143 open plan_for_input_value;
144 fetch plan_for_input_value into v_inel_length,
145 v_inel_period_type,
146 v_start_rule,
147 v_plan_id,
148 v_formula_id;
149 --
150 -- If there is no associated plan, there is no further work to be
151 -- done in this procedure.
152 --
153 if plan_for_input_value%notfound then
154 close plan_for_input_value;
155 return;
156 end if;
157 --
158 hr_utility.set_location('get_accrual_ineligibility', 2);
159
160 close plan_for_input_value;
161
162 -- Now check for the assignment's enrollment into the plan
163 -- and the assignment's length of service relative to ineligible period.
164
165 SELECT pps.date_start
166 INTO v_service_start
167 FROM per_periods_of_service pps,
168 per_assignments_f paf
169 WHERE paf.assignment_id = p_asg_id
170 AND p_sess_date BETWEEN paf.effective_start_date
171 AND paf.effective_end_date
172 AND pps.person_id = paf.person_id
173 AND paf.business_group_id = p_bg_id
174 AND p_sess_date BETWEEN pps.date_start AND pps.final_process_date;
175
176 if v_formula_id is null then
177
178 --
179 -- As no eligibility formula, we use the info in the
180 -- accrual plan table to check for ineligibility
181 --
182
183 -- First check ineligible period, then check accrual start...
184
185 hr_utility.set_location('get_accrual_ineligibility', 3);
186 hr_utility.trace('Service Start is: ' || v_service_start);
187 hr_utility.trace('Ineligible length is:' || v_inel_length);
188
189 IF v_inel_length <> 0 THEN
190
191 hr_utility.set_location('get_accrual_ineligibility', 4);
192
193 -- Calculate how many days are ineligible...
194 IF v_inel_period_type = 'CM' THEN
195
196 v_eligible_date := ADD_MONTHS(v_service_start, v_inel_length);
197
198 ELSIF v_inel_period_type = 'W' THEN
199
200 v_eligible_date := v_service_start + (v_inel_length * 7);
201
202 ELSIF v_inel_period_type = 'F' THEN
203
204 v_eligible_date := v_service_start + (v_inel_length * 14);
205
206 ELSIF v_inel_period_type = 'SM' THEN
207
208 v_semi_days := MOD(v_inel_length, 2);
209
210 IF v_semi_days <> 0 THEN
211
212 v_semi_days := 15; -- ie. an odd number of semi-months.
213
214 END IF;
215
216 v_eligible_date := ADD_MONTHS(v_service_start, (v_inel_length / 2));
217 v_eligible_date := v_eligible_date + v_semi_days;
218
219 ELSIF v_inel_period_type = 'Q' THEN
220
221 v_eligible_date := ADD_MONTHS(v_service_start, (v_inel_length * 3));
222
223 ELSIF v_inel_period_type = 'Y' THEN
224
225 v_eligible_date := ADD_MONTHS(v_service_start, (v_inel_length * 12));
226
227 ELSIF v_inel_period_type = 'SY' THEN
228
229 v_eligible_date := ADD_MONTHS(v_service_start, (v_inel_length * 6));
230
231 ELSIF v_inel_period_type = 'LM' THEN
232
233 v_eligible_date := v_service_start + (v_inel_length * 26);
234
235 ELSIF v_inel_period_type = 'BM' THEN
236
237 v_eligible_date := ADD_MONTHS(v_service_start, (v_inel_length * 2));
238
239 END IF; -- Inel Period Types
240
241 ELSIF v_start_rule = 'PLUS_SIX_MONTHS' THEN
242
243 -- Inel length = 0, check Accrual Start for 6 month inel.
244 -- Ie. you can't take time against an accrual for which you haven't
245 -- started accruing!
246
247 v_eligible_date := ADD_MONTHS(v_service_start, 6);
248
249 ELSIF v_start_rule = 'HD' then
250
251 v_eligible_date := v_service_start;
252
253 ELSIF v_start_rule = 'BOY' then
254
255 v_eligible_date := TRUNC(ADD_MONTHS(v_service_start, 12), 'YEAR');
256
257 END IF;
258
259 hr_utility.set_location('get_accrual_ineligibility', 5);
260 hr_utility.trace('Eligible Date is: ' || v_eligible_date);
261 hr_utility.trace('Session Date is: ' || p_sess_date);
262
263 -- Eligible or what?
264
265 IF p_sess_date >= nvl(v_eligible_date,p_sess_date) THEN -- #4762608
266 hr_utility.set_location('get_accrual_ineligibility', 6);
267
268 p_eligible := 'Y';
269
270 ELSE
271 hr_utility.set_location('get_accrual_ineligibility', 7);
272
273 p_eligible := 'N';
274
275 END IF;
276
277 else
278 --
279 -- Use ineligibilty accrual plan formula to calculate
280 -- ineligibility
281 --
282
283 -- Initialise the Inputs and Outputs tables
284 ff_exec.init_formula
285 ( v_formula_id
286 , p_sess_date
287 , l_inputs
288 , l_outputs );
289 --
290 -- Set up context values for the formula
291 for i in l_inputs.first..l_inputs.last loop
292
293 if l_inputs(i).name = 'DATE_EARNED' then
294 l_inputs(i).value := p_sess_date;
295
296 elsif l_inputs(i).name = 'ASSIGNMENT_ID' then
297 l_inputs(i).value := p_asg_id;
298
299 elsif l_inputs(i).name = 'ACCRUAL_PLAN_ID' then
300 l_inputs(i).value := v_plan_id;
301
302 end if;
303 end loop;
304
305 -- Run the formula
306 ff_exec.run_formula( l_inputs, l_outputs );
307
308 -- Get the result
309 p_eligible := l_outputs(l_outputs.first).value;
310
311 end if;
312
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN NULL;
315
316 END get_accrual_ineligibility;
317
318 --
319 -- **********************************************************************
320
321 FUNCTION calc_accrual_pay ( p_bg_id IN NUMBER,
322 p_asg_id IN NUMBER,
323 p_eff_date IN DATE,
324 p_hours_taken IN NUMBER,
325 p_curr_rate IN NUMBER,
326 p_mode IN VARCHAR2) RETURN NUMBER IS
327
328 l_vac_pay NUMBER(27,7) := 0;
329 l_vac_hours NUMBER(10,7) := 0;
330 l_vac_tot_hrs NUMBER(10,7) := 0;
331
332 l_sick_pay NUMBER(27,7) := 0;
333 l_sick_hours NUMBER(10,7) := 0;
334 l_sick_tot_hrs NUMBER(10,7) := 0;
335
336 CURSOR get_vac_hours IS
337 select fnd_number.canonical_to_number(pev.screen_entry_value)
338 from pay_accrual_plans pap,
339 pay_element_entries_f pee,
340 pay_element_entry_values_f pev
341 where pap.accrual_category = 'V'
342 and pap.business_group_id = p_bg_id
343 and pev.input_value_id = pap.pto_input_value_id
344 and p_eff_date between pev.effective_start_date
345 and pev.effective_end_date
346 and pee.element_entry_id = pev.element_entry_id
347 and pee.assignment_id = p_asg_id
348 and p_eff_date between pee.effective_start_date
349 and pee.effective_end_date;
350
351 -- The "vacation_pay" function looks for hours entered against Vacation plans
352 -- in the current period. The number of hours are summed and multiplied by
353 -- the current rate of Regular Pay..
354 -- Return immediately when no vacation time has been taken.
355 -- Need to loop thru all "Vacation Plans" and check for entries in the current
356 -- period for this assignment.
357
358 CURSOR get_sick_hours IS
359 select fnd_number.canonical_to_number(pev.screen_entry_value)
360 from pay_accrual_plans pap,
361 pay_element_entries_f pee,
362 pay_element_entry_values_f pev
363 where pap.accrual_category = 'S'
364 and pap.business_group_id = p_bg_id
365 and pev.input_value_id = pap.pto_input_value_id
366 and p_eff_date between pev.effective_start_date
367 and pev.effective_end_date
368 and pee.element_entry_id = pev.element_entry_id
369 and pee.assignment_id = p_asg_id
370 and p_eff_date between pee.effective_start_date
371 and pee.effective_end_date;
372
373 -- The "sick_pay" function looks for hours entered against Sick plans in the
374 -- current period. The number of hours are summed and multiplied by the
375 -- current rate of Regular Pay.
376 -- Return immediately when no sick time has been taken.
377
378 BEGIN
379
380 hr_utility.set_location('calc_accrual_pay', 11);
381 IF p_mode = 'V' THEN
382
383 hr_utility.set_location('calc_accrual_pay', 12);
384 OPEN get_vac_hours;
385 LOOP
386
387 hr_utility.set_location('calc_accrual_pay', 13);
388 FETCH get_vac_hours
389 INTO l_vac_hours;
390 EXIT WHEN get_vac_hours%NOTFOUND;
391
392 hr_utility.set_location('calc_accrual_pay', 14);
393 hr_utility.set_location('l_vac_hours =', l_vac_hours);
394 l_vac_tot_hrs := l_vac_tot_hrs + l_vac_hours;
395 hr_utility.set_location('l_vac_tot_hrs =', l_vac_tot_hrs);
396
397 END LOOP;
398 CLOSE get_vac_hours;
399 hr_utility.set_location('calc_accrual_pay', 15);
400
401 IF l_vac_tot_hrs <> 0 THEN
402
403 hr_utility.set_location('calc_accrual_pay', 16);
404 l_vac_pay := p_hours_taken * p_curr_rate;
405
406 ELSE
407
408 l_vac_pay := -777.77;
409
410 END IF;
411
412 RETURN l_vac_pay;
413
414 ELSIF p_mode = 'S' THEN
415
416 hr_utility.set_location('calc_accrual_pay', 17);
417 OPEN get_sick_hours;
418 LOOP
419
420 hr_utility.set_location('calc_accrual_pay', 18);
421 FETCH get_sick_hours
422 INTO l_sick_hours;
423 EXIT WHEN get_sick_hours%NOTFOUND;
424
425 hr_utility.set_location('calc_accrual_pay', 19);
426 l_sick_tot_hrs := l_sick_tot_hrs + l_sick_hours;
427
428 END LOOP;
429 CLOSE get_sick_hours;
430 hr_utility.set_location('calc_accrual_pay', 20);
431 --
432 -- Rmonge 02/26/2002
433 -- Fix for bug 2006907
434 -- Changing sick_tot_hrs > 0 to sick_tot_hrs <> 0
435 --
436 IF l_sick_tot_hrs <> 0 THEN
437
438 hr_utility.set_location('calc_accrual_pay', 21);
439 l_sick_pay := p_hours_taken * p_curr_rate;
440
441 ELSE
442
443 l_sick_pay := -999.99;
444
445 END IF;
446
447 RETURN l_sick_pay;
448
449 ELSE
450
451 hr_utility.set_location('Accrual Pay mode not set to V or S', 99);
452 l_vac_pay := 0;
453 return l_vac_pay;
454
455 END IF;
456
457 END calc_accrual_pay;
458 --
459 FUNCTION accrual_time_taken ( p_bg_id IN NUMBER,
460 p_asg_id IN NUMBER,
461 p_eff_date IN DATE,
462 p_mode IN VARCHAR2) RETURN NUMBER IS
463
464 l_hours_taken NUMBER(7,3) := 0;
465
466 BEGIN
467
468 hr_utility.set_location('accrual_time_taken', 1);
469 select sum(fnd_number.canonical_to_number(pev.screen_entry_value))
470 into l_hours_taken
471 from pay_accrual_plans pap,
472 pay_element_entries_f pee,
473 pay_element_entry_values_f pev
474 where pap.accrual_category = p_mode
475 and pap.business_group_id = p_bg_id
476 and pev.input_value_id = pap.pto_input_value_id
477 and p_eff_date between pev.effective_start_date
478 and pev.effective_end_date
479 and pee.element_entry_id = pev.element_entry_id
480 and pee.assignment_id = p_asg_id
481 and p_eff_date between pee.effective_start_date
482 and pee.effective_end_date;
483
484 hr_utility.set_location('accrual_time_taken', 3);
485 return l_hours_taken;
486
487 EXCEPTION
488
489 WHEN NO_DATA_FOUND THEN
490
491 hr_utility.set_location('accrual_time_taken', 5);
492 return l_hours_taken;
493
494 END accrual_time_taken;
495
496 END hr_us_accrual_plans;
[Home] [Help]