1 package body pay_fr_overtime as
2 /* $Header: pyfrovtm.pkb 115.20 2004/03/31 08:34:12 autiwari noship $ */
3 --
4 g_package varchar2(30) := 'pay_fr_overtime';
5 --
6 TYPE scheme_rec is RECORD
7 (Overtime_Payroll_Id number
8 ,Overtime_Scheme_Type varchar2(150)
9 ,Threshold varchar2(150)
10 ,Annual_Quota varchar2(150)
11 ,Weekly_Offset_Threshold varchar2(150)
12 ,Bonification_Method varchar2(150)
13 ,Majoration_Method varchar2(150)
14 ,Weekly_Compensation_Threshold varchar2(150)
15 ,Upper_Compensation_Threshold varchar2(150)
16 ,Lower_Compensation_Factor varchar2(150)
17 ,Higher_Compensation_Factor varchar2(150)
18 ,Overtime_Band_Table varchar2(150)
19 ,Regularisation_Period_Type varchar2(150)
20 ,Regularisation_Period_Weeks varchar2(150)
21 ,Regularisation_Threshold varchar2(150)
22 ,Regularisation_Offset varchar2(150)
23 ,Regularisation_Payment_Basis varchar2(150)
24 ,Regularisation_Start_Date varchar2(150)
25 ,Overtime_formula_ID varchar2(150)
26 ,Regularisation_Formula_ID varchar2(150));
27 --
28 null_scheme scheme_rec;
29 scheme scheme_rec;
30 --
31 TYPE band_rec is RECORD
32 (Label varchar2(30)
33 ,Hours varchar2(30)
34 ,Hours_Percentage varchar2(30)
35 ,Factor varchar2(30));
36 --
37 TYPE band_tab is TABLE of band_rec INDEX by BINARY_INTEGER;
38 --
39 null_band band_tab;
40 band band_tab;
41 --
42 ------------------------------------------------------------------------
43 -- Function GET_UPPER_COMP_THRESHOLD
44 --
45 -- This is a local function that derives the overtime scheme upper compensation
46 -- threshold by prorating the threshold applicable for the employee during the
47 -- year.
48 -- Once the effective date is reached, as long as the employee is assigned
49 -- to the overtime scheme on DATE_EARNED+1 the upper compensation threshold
50 -- effective on DATE_EARNED+1 is assumed to last until the end of the year.
51 -- If the they are not on an overtime scheme on DATE_EARNED+1 (perhaps
52 -- because they are a leaver) then the prorated threshold is only calculated
53 -- upt to DATE_EARNED.
54 ------------------------------------------------------------------------
55 function get_upper_comp_threshold
56 (p_assignment_id number
57 ,p_effective_date date) return number is
58 --
59 start_of_year date;
60 end_of_year date;
61 days_in_year number;
62 proration_factor number;
63 upper_compensation_threshold number;
64 prev_threshold number;
65 total_threshold number;
66 end_date date;
67 l_proc varchar2(72) := g_package||'.get_upper_comp_threshold';
68 --
69 cursor c_overtime_scheme is
70 select nvl(to_number(p.prl_information8),0) upper_compensation_threshold
71 , greatest(start_of_year
72 ,p.effective_start_date
73 ,ee.effective_start_date) start_date
74 , least(end_of_year
75 ,p.effective_end_date
76 ,ee.effective_end_date) end_date
77 from pay_element_entries_f ee
78 , pay_element_links_f el
79 , pay_element_types_f et
80 , pay_payrolls_f p
81 where et.element_name = 'FR_OVERTIME'
82 and p_effective_date
83 between et.effective_start_date and et.effective_end_date
84 and el.element_type_id = et.element_type_id
85 and p_effective_date
86 between el.effective_start_date and el.effective_end_date
87 and el.element_link_id = ee.element_link_id
88 and ee.assignment_id = p_assignment_id
89 and p.effective_start_date <= ee.effective_end_date
90 and p.effective_end_date >= ee.effective_start_date
91 and fnd_number.canonical_to_number(ee.ENTRY_INFORMATION1) = p.payroll_id
92 and p.effective_start_date <= end_of_year
93 and p.effective_end_date >= start_of_year
94 and ee.effective_start_date <= end_of_year
95 and ee.effective_end_date >= start_of_year
96 order by greatest(start_of_year,p.effective_start_date,ee.effective_start_date);
97 --
98 begin
99 hr_utility.set_location(l_proc,10);
100 /* Initialise parameters */
101 start_of_year :=
102 to_date(to_char(p_effective_date,'YYYY')||'0101','YYYYMMDD');
103 end_of_year := to_date(to_char(p_effective_date,'YYYY')||'1231','YYYYMMDD');
104 days_in_year := end_of_year - start_of_year + 1;
105 total_threshold := 0;
106 --
107 /* Loop through the overtime scheme records
108 If the start date is before date earned and the end date is after date
109 earned then move the end date to end of year - we assume employee is on
110 the scheme to the end of the year.
111
112 If the start date is date earned + 1 then this implies that the employee
113 had a scheme change on date earned but continues to receive overtime - hence
114 use the upper compensation threshold applicable on date earned up until the
115 end of the year
116 */
117 for e in c_overtime_scheme loop
118 if e.start_date <= p_effective_date then
119 if e.end_date > p_effective_date then
120 end_date := end_of_year;
121 else
122 end_date := e.end_date;
123 end if;
124 upper_compensation_threshold := e.upper_compensation_threshold;
125 else -- e.start_date = p_effective_date + 1
126 upper_compensation_threshold := prev_threshold;
127 end_date := end_of_year;
128 end if;
129 --
130 /*
131 Calculate the prorated value of upper compensation threshold
132 Keep running total of threshold value.
133 */
134 proration_factor := (end_date - e.start_date + 1) / days_in_year;
135 --
136 total_threshold := total_threshold +
137 round(
138 upper_compensation_threshold * proration_factor,1);
139 prev_threshold := upper_compensation_threshold;
140 --
141 /* Once the end date has reached end_of_year then no need to process further
142 records
143 */
144 if end_date = end_of_year then
145 exit;
146 end if;
147 end loop;
148 hr_utility.set_location(l_proc,20);
149 return total_threshold;
150 end;
151 --
152 ------------------------------------------------------------------------
153 -- Function SET_SCHEME
154 --
155 -- This function retrieves the overtime scheme details of the employee
156 -- as of the p_date. The details are held in a PL/SQL table.
157 -- It also derives the band values.
158 ------------------------------------------------------------------------
159 function set_scheme (p_assignment_id number
160 ,p_date date) return number is
161 cursor get_scheme is
162 Select to_char(SCHEME.PAYROLL_ID)
163 ,PRL_INFORMATION1 -- Overtime_Scheme_Type
164 ,PRL_INFORMATION2 -- Threshold
165 ,PRL_INFORMATION3 -- Annual_Quota
166 ,PRL_INFORMATION4 -- Weekly_Offset_Threshold
167 ,nvl(TARGET.ENTRY_INFORMATION2,PRL_INFORMATION5) -- Bonification_Method
168 ,nvl(TARGET.ENTRY_INFORMATION3,PRL_INFORMATION6) -- Majoration_Method
169 ,PRL_INFORMATION7 -- Weekly_Compensation_Threshold
170 ,'0' -- Upper_Compensation_Threshold
171 ,PRL_INFORMATION9 -- Lower_Compensation_Factor
172 ,PRL_INFORMATION10 -- Higher_Compensation_Factor
173 ,PRL_INFORMATION11 -- Overtime_Band_Table
174 ,PRL_INFORMATION12 -- Regularisation_Period_Type
175 ,PRL_INFORMATION13 -- Regularisation_Period_Weeks
176 ,PRL_INFORMATION14 -- Regularisation_Threshold
177 ,PRL_INFORMATION15 -- Regularisation_Offset
178 ,PRL_INFORMATION16 -- Regularisation_Payment_Basis
179 ,nvl(TARGET.ENTRY_INFORMATION4,PRL_INFORMATION17) -- Regularisation_Start_Date
180 ,nvl(PRL_INFORMATION18,'-1') -- Overtime Formula ID
181 ,nvl(PRL_INFORMATION19,'-1') -- Regularisation Formula ID
182 from pay_element_entries_f TARGET
183 , pay_element_links_f LINK
184 , pay_element_types_f ELEMENT
185 , pay_payrolls_f SCHEME
186 where TARGET.assignment_id = p_assignment_id
187 and p_date BETWEEN TARGET.effective_start_date
188 AND TARGET.effective_end_date
189 and TARGET.element_link_id = LINK.element_link_id
190 and p_date BETWEEN LINK.effective_start_date
191 AND LINK.effective_end_date
192 and LINK.element_type_id = ELEMENT.element_type_id
193 and p_date BETWEEN ELEMENT.effective_start_date
194 AND ELEMENT.effective_end_date
195 and ELEMENT.element_name = 'FR_OVERTIME'
196 and fnd_number.canonical_to_number(TARGET.ENTRY_INFORMATION1)
197 = SCHEME.payroll_id
198 and p_date BETWEEN SCHEME.effective_start_date
199 AND SCHEME.effective_end_date;
200 --
201 cursor get_band_values(p_band_table_id number) is
202 select r.ROW_LOW_RANGE_OR_NAME
203 , decode(scheme.overtime_scheme_type,'F',ci1.VALUE,null) hours_value
204 , decode(scheme.overtime_scheme_type,'P',ci1.VALUE,null) working_hours_value
205 , ci2.VALUE factor_value
206 from pay_user_rows_f r
207 , pay_user_column_instances_f ci1
208 , pay_user_column_instances_f ci2
209 , pay_user_columns c1
210 , pay_user_columns c2
211 where r.user_table_id = p_band_table_id
212 and p_date BETWEEN r.effective_start_date and r.effective_end_date
213 and c1.user_table_id = p_band_table_id
214 and c1.user_column_id = ci1.user_column_id
215 and ci1.user_row_id = r.user_row_id
216 and p_date BETWEEN ci1.effective_start_date and ci1.effective_end_date
217 and c1.user_column_name = decode(scheme.overtime_scheme_type,
218 'F','HOURS',
219 'P','WORKING_HOURS_PERCENTAGE')
220 and c2.user_table_id = p_band_table_id
221 and c2.user_column_id = ci2.user_column_id
222 and ci2.user_row_id = r.user_row_id
223 and p_date BETWEEN ci2.effective_start_date and ci2.effective_end_date
224 and c2.user_column_name = 'FACTOR'
225 order by r.display_sequence;
226 --
227 i number;
228 l_proc varchar2(72) := g_package||'.set_scheme';
229 begin
230 hr_utility.set_location(l_proc,10);
231 scheme := null_scheme;
232 band := null_band;
233 --
234 open get_scheme;
235 fetch get_scheme into scheme;
236 if get_scheme%notfound then
237 close get_scheme;
238 return 1;
239 else
240 scheme.upper_compensation_threshold :=
241 to_char(get_upper_comp_threshold(p_assignment_id,p_date));
242 end if;
243 close get_scheme;
244 --
245 /* Check mandatory data */
246 if (scheme.Overtime_Scheme_Type <> 'P' and
247 scheme.Overtime_Scheme_Type <> 'F') then
248 fnd_message.set_name('PAY','PAY_74966_MANDATORY_OT_TYPE');
249 fnd_message.raise_error;
250 end if;
251 --
252 if scheme.Overtime_Scheme_Type = 'F' then
253 if scheme.threshold is null
254 or scheme.Weekly_Offset_Threshold is null
255 or (scheme.Bonification_Method <> 'P' and
256 scheme.Bonification_Method <> 'T')
257 or (scheme.Majoration_Method <> 'P' and
258 scheme.Majoration_Method <> 'T')
259 or scheme.Upper_Compensation_Threshold is null
260 or scheme.Weekly_Compensation_Threshold is null
261 or scheme.Lower_Compensation_Factor is null
262 or scheme.Higher_Compensation_Factor is null
263 or scheme.Overtime_Band_Table is null
264 or (scheme.Regularisation_Payment_Basis <> 'W' and
265 scheme.Regularisation_Payment_Basis <> 'C') then
266 fnd_message.set_name('PAY','PAY_74967_MANDATORY_FT_SCHEME');
267 fnd_message.raise_error;
268 end if;
269 --
270 if scheme.Regularisation_Period_Type = 'P' then
271 if scheme.Regularisation_Period_Weeks is null then
272 fnd_message.set_name('PAY','PAY_74968_MANDATORY_REG_PERIOD');
273 fnd_message.raise_error;
274 end if;
275 end if;
276 if scheme.Regularisation_Period_Type is not null then
277 if scheme.Regularisation_Threshold is null
278 or scheme.Regularisation_Offset is null
279 or scheme.Regularisation_Start_Date is null
280 or scheme.Regularisation_Formula_ID = -1 then
281 fnd_message.set_name('PAY','PAY_74969_MANDATORY_REG_ATTS');
282 fnd_message.raise_error;
283 end if;
284 end if;
285 end if;
286 --
287 if scheme.Overtime_Scheme_Type = 'P' then
288 if (scheme.Bonification_Method <> 'P' and
289 scheme.Bonification_Method <> 'T')
290 or scheme.Overtime_Band_Table is null then
291 fnd_message.set_name('PAY','PAY_74970_MANDATORY_PT_SCHEME');
292 fnd_message.raise_error;
293 end if;
294 if (scheme.Majoration_Method is null) then
295 /* Set Majoration Method to 'T' for processing purposes */
296 scheme.Majoration_Method := 'T';
297 end if;
298 end if;
299
300
301 /* Load Band information */
302 i := 0;
303 for b in get_band_values(
304 fnd_number.canonical_to_number(scheme.overtime_band_table)) loop
305 i := i + 1;
306 band(i) := b;
307 end loop;
308 --
309 hr_utility.set_location(l_proc,20);
310 return 0;
311 end;
312 --
313 ------------------------------------------------------------------------
314 -- Function GET_SCHEME
315 --
316 -- This function reads the PL/SQL scheme table and returns the value of
317 -- an item in the table, the item returned is specified in a parameter.
318 ------------------------------------------------------------------------
319 function get_scheme(p_scheme_item varchar2) return varchar2 is
320 l_proc varchar2(72) := g_package||'.get_scheme';
321 begin
325 elsif p_scheme_item = 'OVERTIME_SCHEME_TYPE' then
322 hr_utility.set_location(l_proc,10);
323 if p_scheme_item = 'OVERTIME_PAYROLL_ID' then
324 return scheme.OVERTIME_PAYROLL_ID;
326 return scheme.OVERTIME_SCHEME_TYPE;
327 elsif p_scheme_item = 'THRESHOLD' then
328 return scheme.THRESHOLD;
329 elsif p_scheme_item = 'ANNUAL_QUOTA' then
330 return scheme.ANNUAL_QUOTA;
331 elsif p_scheme_item = 'WEEKLY_OFFSET_THRESHOLD' then
332 return scheme.WEEKLY_OFFSET_THRESHOLD;
333 elsif p_scheme_item = 'BONIFICATION_METHOD' then
334 return scheme.BONIFICATION_METHOD;
335 elsif p_scheme_item = 'MAJORATION_METHOD' then
336 return scheme.MAJORATION_METHOD;
337 elsif p_scheme_item = 'WEEKLY_COMPENSATION_THRESHOLD' then
338 return scheme.WEEKLY_COMPENSATION_THRESHOLD;
339 elsif p_scheme_item = 'UPPER_COMPENSATION_THRESHOLD' then
340 return scheme.UPPER_COMPENSATION_THRESHOLD;
341 elsif p_scheme_item = 'LOWER_COMPENSATION_FACTOR' then
342 return scheme.LOWER_COMPENSATION_FACTOR;
343 elsif p_scheme_item = 'HIGHER_COMPENSATION_FACTOR' then
344 return scheme.HIGHER_COMPENSATION_FACTOR;
345 elsif p_scheme_item = 'OVERTIME_BAND_TABLE' then
346 return scheme.OVERTIME_BAND_TABLE;
347 elsif p_scheme_item = 'REGULARISATION_PERIOD_TYPE' then
348 return scheme.REGULARISATION_PERIOD_TYPE;
349 elsif p_scheme_item = 'REGULARISATION_PERIOD_WEEKS' then
350 return scheme.REGULARISATION_PERIOD_WEEKS;
351 elsif p_scheme_item = 'REGULARISATION_THRESHOLD' then
352 return scheme.REGULARISATION_THRESHOLD;
353 elsif p_scheme_item = 'REGULARISATION_OFFSET' then
354 return scheme.REGULARISATION_OFFSET;
355 elsif p_scheme_item = 'REGULARISATION_PAYMENT_BASIS' then
356 return scheme.REGULARISATION_PAYMENT_BASIS;
357 elsif p_scheme_item = 'REGULARISATION_START_DATE' then
358 return scheme.REGULARISATION_START_DATE;
359 elsif p_scheme_item = 'OVERTIME_FORMULA_ID' then
360 return scheme.OVERTIME_FORMULA_ID;
361 elsif p_scheme_item = 'REGULARISATION_FORMULA_ID' then
362 return scheme.REGULARISATION_FORMULA_ID;
363 else
364 return('');
365 end if;
366 --
367 end get_scheme;
368 --
369 ------------------------------------------------------------------------
370 -- Function GET_BAND
371 --
372 -- This function reads the PL/SQL band table and returns the band label,
373 -- hours, working_time_percentage and factor for a specified band.
374 ------------------------------------------------------------------------
375 function get_band(p_band number
376 ,p_label out nocopy varchar2
377 ,p_hours out nocopy number
378 ,p_hours_percentage out nocopy number
379 ,p_factor out nocopy number) return number is
380 l_proc varchar2(72) := g_package||'.get_band';
381 begin
382 hr_utility.set_location(l_proc,10);
383 p_label := band(p_band).Label;
384 p_hours := band(p_band).Hours;
385 p_hours_percentage := band(p_band).Hours_Percentage;
386 p_factor := band(p_band).Factor;
387 hr_utility.trace('Fetching Band '||to_char(p_band));
388 hr_utility.trace('Hours_Percentage '||p_hours_percentage);
389 return 0;
390 exception when others then
391 return 1;
392 end;
393 --
394 ------------------------------------------------------------------------
395 -- Function CALCULATE_BAND
396 --
397 -- This function will determine the number of hours to pay at the prevailing
398 -- full band rate and reduced band rate (if some hours have already been
399 -- included in normal pay due to working hours being greater than the
400 -- overtime threshold).
401 --
402 -- It also takes into account whether the compensation is either being paid
403 -- or taken as time off in lieu. Either the pay and the hourly pay rate
404 -- (increased according to the band factor) or time accrued and the accrual
405 -- rate (band factor) is returned by the function.
406 ------------------------------------------------------------------------
407 function calculate_band
408 (p_low_value number
409 ,p_band_hours number
410 ,p_overtime_hours number
411 ,p_weekly_reference_hours number
412 ,p_band_factor number
413 ,p_pay_rate number
414 ,p_compensation_method varchar2
415 ,p_high_value out nocopy number
416 ,p_band_full_factor out nocopy number
417 ,p_band_full_pay_rate out nocopy number
418 ,p_band_full_hours out nocopy number
419 ,p_band_full_pay out nocopy number
420 ,p_band_full_accrual out nocopy number
421 ,p_band_reduced_actor out nocopy number
422 ,p_band_reduced_pay_rate out nocopy number
423 ,p_band_reduced_hours out nocopy number
424 ,p_band_reduced_pay out nocopy number
425 ,p_band_reduced_accrual out nocopy number) return number is
426 --
427 l_overtime_hours_in_band number;
428 --
429 l_high_value number := 0;
430 l_full_factor number := 0;
431 l_hourly_rate_ff number := 0;
432 l_hourly_pay_rate_ff number := 0;
433 l_hourly_accrual_rate_ff number := 0;
434 l_hours_ff number := 0;
435 l_pay_ff number := 0;
436 l_accrual_ff number := 0;
437 --
438 l_reduced_factor number := 0;
439 l_hourly_rate_rf number := 0;
440 l_hourly_pay_rate_rf number := 0;
441 l_hourly_accrual_rate_rf number := 0;
442 l_hours_rf number := 0;
443 l_pay_rf number := 0;
444 l_accrual_rf number := 0;
445 --
446 l_proc varchar2(72) := g_package||'.calculate_band';
447 begin
448 hr_utility.set_location(l_proc,10);
449 --
450 /* The number of hours is
451 1. 0 if the weekly hours are below the bands low value
452
456 */
453 2. or the difference between the weekly hours and the low value if less than the high value
454
455 3. or the difference between the low and high value if weekly hours is greater than the high value
457 --
458 l_high_value := p_low_value + p_band_hours;
459 --
460 l_overtime_hours_in_band :=
461 least(greatest(p_overtime_hours, p_low_value),l_high_value) - p_low_value;
462 --
463 /* If weekly reference hours is greater than the overtime threshold then the
464 implication is that the time worked up to the weekly reference hours is
465 covered by the salary payment. These hours are therefore only subject to the
466 difference between the band overtime rate and 100%.
467
468 For instance if the weekly reference hours is 37 and a person works 38
469 hours then 2 of these hours are payable at 10% and 1 hour is payable at 110%. */
470 --
471 if p_weekly_reference_hours > p_low_value then
472 /* Calculate the differential rate */
473 l_reduced_factor := p_band_factor - 100;
474 l_hourly_pay_rate_rf := p_pay_rate * l_reduced_factor / 100;
475 l_hourly_accrual_rate_rf := l_reduced_factor / 100;
476 end if;
477 --
478 /* If weekly reference hours is at or above the upper level for the band then all the overtime hours are payable at a differential value */
479 --
480 if p_weekly_reference_hours < l_high_value then
481 l_full_factor := p_band_factor;
482 l_hourly_pay_rate_ff := p_pay_rate * l_full_factor / 100;
483 l_hourly_accrual_rate_ff := l_full_factor / 100;
484 end if;
485 --
486 /* Determine the number of hours overtime payable at a reduced rate */
487 l_hours_rf := least(l_overtime_hours_in_band,
488 (least(greatest(p_weekly_reference_hours, p_low_value)
489 ,l_high_value) - p_low_value));
490 --
491 /* The remaining hours in the band are payable at the full rate */
492 l_hours_ff := l_overtime_hours_in_band - l_hours_rf;
493 --
494 if p_compensation_method = 'P' then
495 if l_hours_rf <> 0 then
496 l_hourly_rate_rf := l_hourly_pay_rate_rf;
497 l_pay_rf := l_hours_rf * l_hourly_rate_rf;
498 end if;
499 --
500 if l_hours_ff <> 0 then
501 l_hourly_rate_ff := l_hourly_pay_rate_ff;
502 l_pay_ff := l_hours_ff * l_hourly_rate_ff;
503 end if;
504 --
505 else /* COMPENSATION_METHOD = 'T' */
506 if l_hours_rf <> 0 then
507 l_hourly_rate_rf := l_hourly_accrual_rate_rf;
508 l_accrual_rf := l_hours_rf * l_hourly_rate_rf;
509 end if;
510 --
511 if l_hours_ff <> 0 then
512 l_hourly_rate_ff := l_hourly_accrual_rate_ff;
513 l_accrual_ff := l_hours_ff * l_hourly_rate_ff;
514 end if;
515 end if;
516 --
517 hr_utility.trace('-------------------------------------');
518 hr_utility.trace('Low Value = '||to_char(p_low_value));
519 hr_utility.trace('High Value = '||to_char(l_high_value));
520 hr_utility.trace('-------------------------------------');
521 hr_utility.trace('Full Factor = '||to_char(l_full_factor));
522 hr_utility.trace('Full Pay Rate = '||to_char(l_hourly_rate_ff));
523 hr_utility.trace('Full Hours = '||to_char(l_hours_ff));
524 hr_utility.trace('Full Pay = '||to_char(l_pay_ff));
525 hr_utility.trace('Full Accrual = '||to_char(l_accrual_ff));
526 hr_utility.trace('-------------------------------------');
527 hr_utility.trace('Reduced Factor = '||to_char(l_reduced_factor));
528 hr_utility.trace('Reduced Pay Rate = '||to_char(l_hourly_rate_rf));
529 hr_utility.trace('Reduced Hours = '||to_char(l_hours_rf));
530 hr_utility.trace('Reduced Pay = '||to_char(l_pay_rf));
531 hr_utility.trace('Reduced Accrual = '||to_char(l_accrual_rf));
532 hr_utility.trace('-------------------------------------');
533 --
534 p_high_value := l_high_value;
535 p_band_full_factor := l_full_factor;
536 p_band_full_pay_rate := l_hourly_rate_ff;
537 p_band_full_hours := l_hours_ff;
538 p_band_full_pay := l_pay_ff;
539 p_band_full_accrual := l_accrual_ff;
540 p_band_reduced_actor := l_reduced_factor;
541 p_band_reduced_pay_rate := l_hourly_rate_rf;
542 p_band_reduced_hours := l_hours_rf;
543 p_band_reduced_pay := l_pay_rf;
544 p_band_reduced_accrual := l_accrual_rf;
545 --
546 hr_utility.set_location(l_proc,10);
547 return 0;
548 end calculate_band;
549 --
550 ------------------------------------------------------------------------
551 -- Function LAST_REGULARISATION
552 --
553 -- This function retrieves the date of the last regularisation if one exists.
554 ------------------------------------------------------------------------
555 function last_regularisation
556 (P_ASSIGNMENT_ID number
557 ,P_DATE_EARNED DATE
558 ,P_RANGE_END_DATE date
559 ,P_RANGE_START_DATE date) return date is
560 --
561 cursor get_elements(p_orig_ele varchar2,
562 p_retr_ele varchar2) is
563 select /*+ORDERED index(pet PAY_ELEMENT_TYPES_F_UK2) */
564 max(decode(pet.element_name,p_orig_ele,pet.element_type_id)) orig_ele_id,
565 max(decode(pet.element_name,p_retr_ele,pet.element_type_id)) retr_ele_id
566 from pay_element_types_f pet
567 where pet.element_name in (p_orig_ele,p_retr_ele)
568 and pet.legislation_code = 'FR'
569 and pet.business_group_id is null
570 and p_date_earned between pet.effective_start_date
571 and pet.effective_end_date;
572 --
573 cursor c_get_regularisation(p_orig_ele_id number,
574 p_retr_ele_id number,
575 p_range_start_chr varchar2,
579 from pay_assignment_actions a
576 p_range_end_chr varchar2) is
577 select /*+ordered use_nl(i i2) */ rr.result_value,
578 sum(fnd_number.canonical_to_number(rr2.result_value))
580 ,pay_run_results r
581 ,pay_input_values_f i
582 ,pay_run_result_values rr
583 ,pay_input_values_f i2
584 ,pay_run_result_values rr2
585 where i.element_type_id = r.element_type_id
586 and i.name = 'End Date'
587 and i.business_group_id is null
588 and i.legislation_code = 'FR'
589 and p_date_earned
590 between i.effective_start_date and i.effective_end_date
591 and i2.element_type_id = r.element_type_id
592 and i2.name = 'Processing Sequence'
593 and i2.business_group_id is null
594 and i2.legislation_code = 'FR'
595 and p_date_earned
596 between i2.effective_start_date and i2.effective_end_date
597 and a.assignment_id = p_assignment_id
598 and a.assignment_action_id = r.assignment_action_id
599 and r.element_type_id in (p_orig_ele_id,p_retr_ele_id)
600 and rr.run_result_id = r.run_result_id
601 and i.input_value_id = rr.input_value_id
602 and rr2.run_result_id = r.run_result_id
603 and i2.input_value_id = rr2.input_value_id
604 and rr.result_value <= p_range_end_chr
605 and rr.result_value >= p_range_start_chr
606 and r.status in ('P','PA')
607 group by rr.result_value
608 having sum(fnd_number.canonical_to_number(rr2.result_value)) >0
609 order by rr.result_value desc;
610 --
611 l_last_regularisation varchar2(30);
612 l_Proc_Seq_sum number;
613 l_orig_ele_id number;
614 l_retr_ele_id number;
615 --
616 l_proc varchar2(72) := g_package||'.last_regularisation';
617 begin
618 hr_utility.set_location(l_proc,10);
619 --
620 open get_elements('FR_REGULARISATION_WEEK','FR_REGULARISATION_WEEK_RETRO');
621 fetch get_elements into l_orig_ele_id,l_retr_ele_id;
622 close get_elements;
623 open c_get_regularisation(l_orig_ele_id,l_retr_ele_id,
624 fnd_date.date_to_canonical(p_range_start_date),
625 fnd_date.date_to_canonical(p_range_end_date));
626 fetch c_get_regularisation into l_last_regularisation,l_Proc_Seq_sum;
627 close c_get_regularisation;
628 --
629 return fnd_date.canonical_to_date(l_last_regularisation);
630 end last_regularisation;
631 --
632 ------------------------------------------------------------------------
633 -- Function DETERMINE_REGULARISATION
634 --
635 -- This function determines whether regularisation is due in the current
636 -- week being processed. It will use the type of regularisation and the
637 -- regularisation start week to determine whether the regularisation is due.
638 ------------------------------------------------------------------------
639 function determine_regularisation
640 (P_ASSIGNMENT_ID number
641 ,P_DATE_EARNED DATE
642 ,P_OVERTIME_PAYROLL_ID NUMBER
643 ,P_PERIOD_TYPE VARCHAR2
644 ,P_NUMBER_OF_WEEKS NUMBER
645 ,P_START_DATE DATE
646 ,P_CURRENT_WEEK_END_DATE DATE
647 ,P_PERIOD_START_DATE OUT NOCOPY DATE
648 ,P_PERIOD_END_DATE OUT NOCOPY DATE) return varchar2 is
649 --
650 l_start_date date;
651 l_end_date date;
652 l_period_start_date date;
653 l_period_end_date date;
654 l_number_of_weeks number;
655 l_proc varchar2(72) := g_package||'.determine_regularisation';
656 --
657 cursor c_week_start_date(p_payroll_id number
658 ,p_date date) is
659 select max(start_date)
660 from per_time_periods
661 where payroll_id = p_payroll_id
662 and p_date >= start_date;
663 --
664 cursor c_week_end_date(p_payroll_id number
665 ,p_date date) is
666 select max(end_date)
667 from per_time_periods
668 where payroll_id = p_payroll_id
669 and p_date >= end_date;
670 --
671 /* Local function to increment the date either with a full year or
672 a multiple of weeks */
673 function increment_date(p_date date) return date is
674 begin
675 if p_period_type = 'Y' then
676 return(add_months(p_date,12));
677 else
678 return(p_date + 7*p_number_of_weeks);
679 end if;
680 end;
681 --
682 begin
683 hr_utility.set_location(l_proc,10);
684 --
685 /* If there is a regularisation after the initial one defined on the scheme
686 or element entry override - then treat it as the start of the repetitions
687 */
688 l_start_date := last_regularisation(p_assignment_id
689 ,p_date_earned
690 ,p_current_week_end_date
691 ,p_start_date);
692 if l_start_date is null then
693 l_start_date := p_start_date;
694 else
695 l_start_date := l_start_date +1;
696 end if;
697 --
698 --
699 while l_start_date < p_current_week_end_date loop
700 --
701 open c_week_start_date(p_overtime_payroll_id
702 ,l_start_date);
703 fetch c_week_start_date into l_period_start_date;
704 if c_week_start_date%notfound then
705 close c_week_start_date;
706 fnd_message.set_name('PAY','PAY_74961_REG_WEEK_NOT_FOUND');
707 fnd_message.raise_error;
708 else
709 close c_week_start_date;
710 l_period_start_date := l_period_start_date;
711 end if;
712
713 l_end_date := increment_date(l_start_date) - 1;
714 --
718 if c_week_end_date%notfound then
715 open c_week_end_date(p_overtime_payroll_id
716 ,l_end_date);
717 fetch c_week_end_date into l_period_end_date;
719 close c_week_end_date;
720 fnd_message.set_name('PAY','PAY_74961_REG_WEEK_NOT_FOUND');
721 fnd_message.raise_error;
722 else
723 close c_week_end_date;
724 end if;
725 --
726 hr_utility.trace('Regularisation Period = ' || to_char(l_period_start_date,'DD-MON-YYYY') ||' - '||to_char(l_period_end_date,'DD-MON-YYYY'));
727 --
728 if l_period_end_date = p_current_week_end_date then
729 l_number_of_weeks := (l_period_end_date - l_period_start_date+1)/7;
730 --
731 hr_utility.trace('Regularisation Due --------------');
732 --
733 /* Set output variables */
734 p_period_start_date := l_period_start_date;
735 p_period_end_date := l_period_end_date;
736 --
737 return('Y');
738 else
739 l_start_date := l_end_date + 1;
740 end if;
741 --
742 end loop;
743 --
744 hr_utility.trace('Regularisation Not Found');
745 return('N');
746 --
747 end determine_regularisation;
748 --
749 /*
750 ------------------------------------------------------------------------
751 -- Function OVERTIME_ENTRY_EXISTS
752 --
753 -- This is a local function that derives the overtime scheme upper compensation
754 ------------------------------------------------------------------------
755 function overtime_entry_exists(p_assignment_id number
756 ,p_week_start_date date
757 ,p_week_end_date date) return varchar2 is
758 --
759 cursor c_overtime_entry is
760 select 'Y'
761 from pay_element_entries_f ee
762 , pay_element_links_f el
763 , pay_element_types_f et
764 where ee.assignment_id = p_assignment_id
765 and ee.element_link_id = el.element_link_id
766 and el.element_type_id = et.element_type_id
767 and et.element_name in ('FR_OVERTIME_WEEK','FR_OVERTIME_EXCEPTION_WEEK')
768 and exists
769 (select null
770 from pay_element_entry_values_f eev
771 , pay_input_values_f iv
772 where eev.input_value_id = iv.input_value_id
773 and eev.element_entry_id = ee.element_entry_id
774 and iv.name = 'Start Date'
775 and eev.screen_entry_value =
776 fnd_date.date_to_canonical(p_week_start_date)
777 )
778 and exists
779 (select null
780 from pay_element_entry_values_f eev
781 , pay_input_values_f iv
782 where eev.input_value_id = iv.input_value_id
783 and eev.element_entry_id = ee.element_entry_id
784 and iv.name = 'End Date'
785 and eev.screen_entry_value =
786 fnd_date.date_to_canonical(p_week_end_date)
787 );
788 --
789 l_entry_exists varchar2(1) := 'N';
790 --
791 l_proc varchar2(72) := g_package||'.overtime_entry_exists';
792 begin
793 hr_utility.set_location(l_proc,10);
794 open c_overtime_entry;
795 fetch c_overtime_entry into l_entry_exists;
796 close c_overtime_entry;
797 --
798 return l_entry_exists;
799 end;
800 */
801 --
802 ------------------------------------------------------------------------
803 -- Function GET_OVERTIME_WEEKS
804 --
805 -- This function will be called from the FR_OVERTIME_WEEK elements
806 -- processing and will determine whether the overtime week corresponding
807 -- to the WEEK_START and WEEK_END Dates is a valid week in the overtime scheme.
808 -- If so it returns the Julian value of the WEEK_END_DATE to be used as
809 -- the processing priority.
810 ------------------------------------------------------------------------
811 function get_overtime_weeks(p_overtime_payroll_id number
812 ,p_week_start_date date
813 ,p_week_end_date date) return number is
814 cursor c_weeks is
815 select 'Y'
816 from per_time_periods o
817 where o.payroll_id = p_overtime_payroll_id
818 and o.start_date = p_week_start_date
819 and o.end_date = p_week_end_date;
820 --
821 l_valid_week varchar2(1) := 'N';
822 l_process_week number;
823 l_proc varchar2(72) := g_package||'.get_overtime_weeks';
824 --
825 begin
826 hr_utility.set_location(l_proc,10);
827 open c_weeks;
828 fetch c_weeks into l_valid_week;
829 close c_weeks;
830 --
831 if l_valid_week = 'N' then
832 fnd_message.set_name('PAY','PAY_74956_NO_SCHEME_WEEK');
833 fnd_message.raise_error;
834 else
835 l_process_week := to_number(to_char(p_week_end_date,'J'));
836 end if;
837 --
838 return l_process_week;
839 --
840 end get_overtime_weeks;
841 --
842 ------------------------------------------------------------------------
843 -- Function GET_OVERTIME_WEEK_DATES
844 --
845 -- This function will be called from the FR_OVERTIME_WEEK1-6 elements
846 -- processing and will determine the whether the overtime week corresponding
847 -- to the WEEK_NUMBER parameter, should be processed in the current payroll
848 -- period. If this is the case then the WEEK START and END dates are returned.
849 ------------------------------------------------------------------------
850 function get_overtime_week_dates(p_overtime_payroll_id number
851 ,p_payroll_start_date date
855 cursor c_weeks is
852 ,p_week_number number
853 ,p_week_start_date out nocopy date
854 ,p_week_end_date out nocopy date) return number is
856 select o.start_date,o.end_date
857 from per_time_periods o
858 , per_time_periods p
859 where o.prd_information2 = p.time_period_id
860 and p.start_date = p_payroll_start_date
861 and o.payroll_id = p_overtime_payroll_id
862 order by o.start_date;
863 --
864 l_weeks number := 0;
865 l_process_week number := 0;
866 --
867 l_proc varchar2(72) := g_package||'.get_overtime_week_dates';
868 begin
869 hr_utility.set_location(l_proc,10);
870 for w in c_weeks loop
871 l_weeks := l_weeks + 1;
872 if l_weeks = p_week_number then
873 --
874 l_process_week := to_number(to_char(w.end_date,'J'));
875 p_week_start_date := w.start_date;
876 p_week_end_date := w.end_date;
877 exit;
878 end if;
879 --
880 end loop;
881 --
882 return l_process_week;
883 end get_overtime_week_dates;
884 --
885 ------------------------------------------------------------------------
886 -- Function GET_OVERTIME_WEEK_DATES
887 --
888 -- This function will be called from the FR_OVERTIME_WEEK1-6 elements
889 -- processing and will determine the whether the overtime week corresponding
890 -- to the WEEK_NUMBER parameter, should be processed in the current payroll
891 -- period. If this is the case then the WEEK START and END dates are returned.
892 -- overloaded function with extra payroll_id context for performance. Overloaded
893 -- to avoid having to ship pdt in 11.5.10, although after that previous version
894 -- becomes obsolete
895 ------------------------------------------------------------------------
896 function get_overtime_week_dates(p_payroll_id number
897 ,p_overtime_payroll_id number
898 ,p_payroll_start_date date
899 ,p_week_number number
900 ,p_week_start_date out nocopy date
901 ,p_week_end_date out nocopy date) return number is
902 cursor c_weeks is
903 select o.start_date,o.end_date
904 from per_time_periods o
905 , per_time_periods p
906 where o.prd_information2 = p.time_period_id
907 and p.payroll_id = p_payroll_id
908 and p.start_date = p_payroll_start_date
909 and o.payroll_id = p_overtime_payroll_id
910 order by o.start_date;
911 --
912 l_weeks number := 0;
913 l_process_week number := 0;
914 --
915 l_proc varchar2(72) := g_package||'.get_overtime_week_dates';
916 begin
917 hr_utility.set_location(l_proc,10);
918 for w in c_weeks loop
919 l_weeks := l_weeks + 1;
920 if l_weeks = p_week_number then
921 --
922 l_process_week := to_number(to_char(w.end_date,'J'));
923 p_week_start_date := w.start_date;
924 p_week_end_date := w.end_date;
925 exit;
926 end if;
927 --
928 end loop;
929 --
930 return l_process_week;
931 end get_overtime_week_dates;
932 --
933 ------------------------------------------------------------------------
934 -- Function GET_WEEK_DETAILS
935 --
936 -- This function will retrieve the number of hours overtime for a given week.
937 -- This information will be acquired by executing a user defined formula
938 -- named FR_USER_OVERTIME_WEEKS, i.e. to retrieve the OVERTIME_HOURS,
939 -- QUOTA_HOURS and COMPENSATION_HOURS.
940 ------------------------------------------------------------------------
941 function get_week_details(p_assignment_id number
942 ,p_effective_date date
943 ,p_business_group_id number
944 ,p_assignment_action_id number
945 ,p_payroll_action_id number
946 ,p_week_start_date date
947 ,p_week_end_date date
948 ,p_formula_id number
949 ,p_overtime_hours out nocopy number
950 ,p_quota_hours out nocopy number
951 ,p_compensation_hours out nocopy number) return number is
952 --
953 l_proc varchar2(72) := g_package||'.get_week_details';
954 l_inputs ff_exec.inputs_t;
955 l_outputs ff_exec.outputs_t;
956 l_formula_id number;
957 l_start_date date;
958 --
959 cursor csr_get_formula is
960 select ff.formula_id,
961 ff.effective_start_date
962 from ff_formulas_f ff
963 where ff.formula_id = p_formula_id
964 and p_effective_date
965 between ff.effective_start_date and ff.effective_end_date
966 and ff.business_group_id = p_business_group_id;
967
968 begin
969 hr_utility.set_location(l_proc,10);
970 --
971 open csr_get_formula;
972 fetch csr_get_formula into l_formula_id, l_start_date;
973 If csr_get_formula%found then
974 hr_utility.set_location(l_proc,20);
975 -- Initialise the formula
976 ff_exec.init_formula (l_formula_id,
977 l_start_date,
978 l_inputs,
979 l_outputs);
980 --
981 -- populate input parameters
985 l_inputs(i).value := p_assignment_id;
982 if (l_inputs.first is not null) and (l_inputs.last is not null) then
983 for i in l_inputs.first..l_inputs.last loop
984 if l_inputs(i).name = 'ASSIGNMENT_ID' then
986 elsif l_inputs(i).name = 'DATE_EARNED' then
987 l_inputs(i).value := fnd_date.date_to_canonical(p_effective_date);
988 elsif l_inputs(i).name = 'BUSINESS_GROUP_ID' then
989 l_inputs(i).value := p_business_group_id;
990 elsif l_inputs(i).name = 'ASSIGNMENT_ACTION_ID' then
991 l_inputs(i).value := p_assignment_action_id;
992 elsif l_inputs(i).name = 'PAYROLL_ACTION_ID' then
993 l_inputs(i).value := p_payroll_action_id;
994 elsif l_inputs(i).name = 'WEEK_START_DATE' then
995 l_inputs(i).value := fnd_date.date_to_canonical(p_week_start_date);
996 elsif l_inputs(i).name = 'WEEK_END_DATE' then
997 l_inputs(i).value := fnd_date.date_to_canonical(p_week_end_date);
998 end if;
999 end loop;
1000 end if;
1001 --
1002 hr_utility.set_location(' Prior to execute the formula',8);
1003 hr_utility.set_location(l_proc,30);
1004 ff_exec.run_formula (l_inputs
1005 ,l_outputs);
1006 --
1007 hr_utility.set_location(l_proc,40);
1008 hr_utility.set_location(' End run formula',9);
1009 --
1010 for l_out_cnt in l_outputs.first..l_outputs.last loop
1011 if l_outputs(l_out_cnt).name = 'OVERTIME_HOURS' then
1012 p_overtime_hours := l_outputs(l_out_cnt).value;
1013 elsif l_outputs(l_out_cnt).name = 'QUOTA_HOURS' then
1014 p_quota_hours := l_outputs(l_out_cnt).value;
1015 elsif l_outputs(l_out_cnt).name = 'COMPENSATION_HOURS' then
1016 p_compensation_hours := l_outputs(l_out_cnt).value;
1017 end if;
1018 end loop;
1019 --
1020 close csr_get_formula;
1021 else
1022 close csr_get_formula;
1023 end if;
1024 return 0;
1025 end get_week_details;
1026 --
1027 ------------------------------------------------------------------------
1028 -- Function REGULARISATION
1029 --
1030 -- This function will call a user formula named in the overtime scheme
1031 -- (passed in as a parameter) to perform the regularisation process.
1032 ------------------------------------------------------------------------
1033 function regularisation(p_assignment_id number
1034 ,p_effective_date date
1035 ,p_business_group_id number
1036 ,p_assignment_action_id number
1037 ,p_payroll_action_id number
1038 ,p_reg_period_start_date date
1039 ,p_reg_period_end_date date
1040 ,p_formula_id number
1041 ,p_b1_pay_ff out nocopy number
1042 ,p_b1_hours_ff out nocopy number
1043 ,p_b1_hourly_rate_ff out nocopy number
1044 ,p_b1_full_factor out nocopy number
1045 ,p_b1_accrual_ff out nocopy number
1046 ,p_b1_label_ff out nocopy varchar2
1047 ,p_b1_pay_rf out nocopy number
1048 ,p_b1_hours_rf out nocopy number
1049 ,p_b1_hourly_rate_rf out nocopy number
1050 ,p_b1_reduced_factor out nocopy number
1051 ,p_b1_accrual_rf out nocopy number
1052 ,p_b1_label_rf out nocopy varchar2
1053 ,p_b2_pay_ff out nocopy number
1054 ,p_b2_hours_ff out nocopy number
1055 ,p_b2_hourly_rate_ff out nocopy number
1056 ,p_b2_full_factor out nocopy number
1057 ,p_b2_accrual_ff out nocopy number
1058 ,p_b2_label_ff out nocopy varchar2
1059 ,p_b2_pay_rf out nocopy number
1060 ,p_b2_hours_rf out nocopy number
1061 ,p_b2_hourly_rate_rf out nocopy number
1062 ,p_b2_reduced_factor out nocopy number
1063 ,p_b2_accrual_rf out nocopy number
1064 ,p_b2_label_rf out nocopy varchar2
1065 ,p_b3_pay_ff out nocopy number
1066 ,p_b3_hours_ff out nocopy number
1067 ,p_b3_hourly_rate_ff out nocopy number
1068 ,p_b3_full_factor out nocopy number
1069 ,p_b3_accrual_ff out nocopy number
1070 ,p_b3_label_ff out nocopy varchar2
1071 ,p_b3_pay_rf out nocopy number
1072 ,p_b3_hours_rf out nocopy number
1073 ,p_b3_hourly_rate_rf out nocopy number
1074 ,p_b3_reduced_factor out nocopy number
1075 ,p_b3_accrual_rf out nocopy number
1076 ,p_b3_label_rf out nocopy varchar2
1077 ,p_b4_pay_ff out nocopy number
1078 ,p_b4_hours_ff out nocopy number
1079 ,p_b4_hourly_rate_ff out nocopy number
1080 ,p_b4_full_factor out nocopy number
1081 ,p_b4_accrual_ff out nocopy number
1082 ,p_b4_label_ff out nocopy varchar2
1083 ,p_b4_pay_rf out nocopy number
1084 ,p_b4_hours_rf out nocopy number
1088 ,p_b4_label_rf out nocopy varchar2
1085 ,p_b4_hourly_rate_rf out nocopy number
1086 ,p_b4_reduced_factor out nocopy number
1087 ,p_b4_accrual_rf out nocopy number
1089 ,p_b5_pay_ff out nocopy number
1090 ,p_b5_hours_ff out nocopy number
1091 ,p_b5_hourly_rate_ff out nocopy number
1092 ,p_b5_full_factor out nocopy number
1093 ,p_b5_accrual_ff out nocopy number
1094 ,p_b5_label_ff out nocopy varchar2
1095 ,p_b5_pay_rf out nocopy number
1096 ,p_b5_hours_rf out nocopy number
1097 ,p_b5_hourly_rate_rf out nocopy number
1098 ,p_b5_reduced_factor out nocopy number
1099 ,p_b5_accrual_rf out nocopy number
1100 ,p_b5_label_rf out nocopy varchar2
1101 ,p_b6_pay_ff out nocopy number
1102 ,p_b6_hours_ff out nocopy number
1103 ,p_b6_hourly_rate_ff out nocopy number
1104 ,p_b6_full_factor out nocopy number
1105 ,p_b6_accrual_ff out nocopy number
1106 ,p_b6_label_ff out nocopy varchar2
1107 ,p_b6_pay_rf out nocopy number
1108 ,p_b6_hours_rf out nocopy number
1109 ,p_b6_hourly_rate_rf out nocopy number
1110 ,p_b6_reduced_factor out nocopy number
1111 ,p_b6_accrual_rf out nocopy number
1112 ,p_b6_label_rf out nocopy varchar2
1113 ) return number is
1114 --
1115 l_proc varchar2(72) := g_package||'.regularisation';
1116 l_inputs ff_exec.inputs_t;
1117 l_outputs ff_exec.outputs_t;
1118 l_formula_id number;
1119 l_start_date date;
1120 --
1121 cursor csr_get_formula is
1122 select ff.formula_id,
1123 ff.effective_start_date
1124 from ff_formulas_f ff
1125 where ff.formula_id = p_formula_id
1126 and p_effective_date
1127 between ff.effective_start_date and ff.effective_end_date;
1128
1129 begin
1130 hr_utility.set_location(l_proc,10);
1131 --
1132 open csr_get_formula;
1133 fetch csr_get_formula into l_formula_id, l_start_date;
1134 If csr_get_formula%found then
1135 hr_utility.set_location(l_proc,20);
1136 -- Initialise the formula
1137 ff_exec.init_formula (l_formula_id,
1138 l_start_date,
1139 l_inputs,
1140 l_outputs);
1141 --
1142 -- populate input parameters
1143 if (l_inputs.first is not null) and (l_inputs.last is not null) then
1144 for i in l_inputs.first..l_inputs.last loop
1145 if l_inputs(i).name = 'ASSIGNMENT_ID' then
1146 l_inputs(i).value := p_assignment_id;
1147 elsif l_inputs(i).name = 'DATE_EARNED' then
1148 l_inputs(i).value := fnd_date.date_to_canonical(p_effective_date);
1149 elsif l_inputs(i).name = 'BUSINESS_GROUP_ID' then
1150 l_inputs(i).value := p_business_group_id;
1151 elsif l_inputs(i).name = 'ASSIGNMENT_ACTION_ID' then
1152 l_inputs(i).value := p_assignment_action_id;
1153 elsif l_inputs(i).name = 'PAYROLL_ACTION_ID' then
1154 l_inputs(i).value := p_payroll_action_id;
1155 elsif l_inputs(i).name = 'REG_PERIOD_START_DATE' then
1156 l_inputs(i).value := fnd_date.date_to_canonical(p_reg_period_start_date);
1157 elsif l_inputs(i).name = 'REG_PERIOD_END_DATE' then
1158 l_inputs(i).value := fnd_date.date_to_canonical(p_reg_period_end_date);
1159 end if;
1160 end loop;
1161 end if;
1162 --
1163 hr_utility.set_location(' Prior to execute the formula',8);
1164 hr_utility.set_location(l_proc,30);
1165 ff_exec.run_formula (l_inputs
1166 ,l_outputs);
1167 --
1168 hr_utility.set_location(l_proc,40);
1169 hr_utility.set_location(' End run formula',9);
1170 --
1171 for l_out_cnt in l_outputs.first..l_outputs.last loop
1172
1173 if l_outputs(l_out_cnt).name = 'B1_PAY_FF'
1174 then p_b1_pay_ff := l_outputs(l_out_cnt).value;
1175 elsif l_outputs(l_out_cnt).name = 'B1_BASE_FF'
1176 then p_b1_hours_ff := l_outputs(l_out_cnt).value;
1177 elsif l_outputs(l_out_cnt).name = 'B1_HOURLY_RATE_FF'
1178 then p_b1_hourly_rate_ff := l_outputs(l_out_cnt).value;
1179 elsif l_outputs(l_out_cnt).name = 'B1_FULL_FACTOR'
1180 then p_b1_full_factor := l_outputs(l_out_cnt).value;
1181 elsif l_outputs(l_out_cnt).name = 'B1_ACCRUAL_FF'
1182 then p_b1_accrual_ff := l_outputs(l_out_cnt).value;
1183 elsif l_outputs(l_out_cnt).name = 'B1_PAY_RF'
1184 then p_b1_pay_rf := l_outputs(l_out_cnt).value;
1185 elsif l_outputs(l_out_cnt).name = 'B1_BASE_RF'
1186 then p_b1_hours_rf := l_outputs(l_out_cnt).value;
1187 elsif l_outputs(l_out_cnt).name = 'B1_HOURLY_RATE_RF'
1188 then p_b1_hourly_rate_rf := l_outputs(l_out_cnt).value;
1189 elsif l_outputs(l_out_cnt).name = 'B1_REDUCED_FACTOR'
1190 then p_b1_reduced_factor := l_outputs(l_out_cnt).value;
1191 elsif l_outputs(l_out_cnt).name = 'B1_ACCRUAL_RF'
1192 then p_b1_accrual_rf := l_outputs(l_out_cnt).value;
1193 elsif l_outputs(l_out_cnt).name = 'B2_PAY_FF'
1194 then p_b2_pay_ff := l_outputs(l_out_cnt).value;
1195 elsif l_outputs(l_out_cnt).name = 'B2_BASE_FF'
1199 elsif l_outputs(l_out_cnt).name = 'B2_FULL_FACTOR'
1196 then p_b2_hours_ff := l_outputs(l_out_cnt).value;
1197 elsif l_outputs(l_out_cnt).name = 'B2_HOURLY_RATE_FF'
1198 then p_b2_hourly_rate_ff := l_outputs(l_out_cnt).value;
1200 then p_b2_full_factor := l_outputs(l_out_cnt).value;
1201 elsif l_outputs(l_out_cnt).name = 'B2_ACCRUAL_FF'
1202 then p_b2_accrual_ff := l_outputs(l_out_cnt).value;
1203 elsif l_outputs(l_out_cnt).name = 'B2_PAY_RF'
1204 then p_b2_pay_rf := l_outputs(l_out_cnt).value;
1205 elsif l_outputs(l_out_cnt).name = 'B2_BASE_RF'
1206 then p_b2_hours_rf := l_outputs(l_out_cnt).value;
1207 elsif l_outputs(l_out_cnt).name = 'B2_HOURLY_RATE_RF'
1208 then p_b2_hourly_rate_rf := l_outputs(l_out_cnt).value;
1209 elsif l_outputs(l_out_cnt).name = 'B2_REDUCED_FACTOR'
1210 then p_b2_reduced_factor := l_outputs(l_out_cnt).value;
1211 elsif l_outputs(l_out_cnt).name = 'B2_ACCRUAL_RF'
1212 then p_b2_accrual_rf := l_outputs(l_out_cnt).value;
1213 elsif l_outputs(l_out_cnt).name = 'B3_PAY_FF'
1214 then p_b3_pay_ff := l_outputs(l_out_cnt).value;
1215 elsif l_outputs(l_out_cnt).name = 'B3_BASE_FF'
1216 then p_b3_hours_ff := l_outputs(l_out_cnt).value;
1217 elsif l_outputs(l_out_cnt).name = 'B3_HOURLY_RATE_FF'
1218 then p_b3_hourly_rate_ff := l_outputs(l_out_cnt).value;
1219 elsif l_outputs(l_out_cnt).name = 'B3_FULL_FACTOR'
1220 then p_b3_full_factor := l_outputs(l_out_cnt).value;
1221 elsif l_outputs(l_out_cnt).name = 'B3_ACCRUAL_FF'
1222 then p_b3_accrual_ff := l_outputs(l_out_cnt).value;
1223 elsif l_outputs(l_out_cnt).name = 'B3_PAY_RF'
1224 then p_b3_pay_rf := l_outputs(l_out_cnt).value;
1225 elsif l_outputs(l_out_cnt).name = 'B3_BASE_RF'
1226 then p_b3_hours_rf := l_outputs(l_out_cnt).value;
1227 elsif l_outputs(l_out_cnt).name = 'B3_HOURLY_RATE_RF'
1228 then p_b3_hourly_rate_rf := l_outputs(l_out_cnt).value;
1229 elsif l_outputs(l_out_cnt).name = 'B3_REDUCED_FACTOR'
1230 then p_b3_reduced_factor := l_outputs(l_out_cnt).value;
1231 elsif l_outputs(l_out_cnt).name = 'B3_ACCRUAL_RF'
1232 then p_b3_accrual_rf := l_outputs(l_out_cnt).value;
1233 elsif l_outputs(l_out_cnt).name = 'B4_PAY_FF'
1234 then p_b4_pay_ff := l_outputs(l_out_cnt).value;
1235 elsif l_outputs(l_out_cnt).name = 'B4_BASE_FF'
1236 then p_b4_hours_ff := l_outputs(l_out_cnt).value;
1237 elsif l_outputs(l_out_cnt).name = 'B4_HOURLY_RATE_FF'
1238 then p_b4_hourly_rate_ff := l_outputs(l_out_cnt).value;
1239 elsif l_outputs(l_out_cnt).name = 'B4_FULL_FACTOR'
1240 then p_b4_full_factor := l_outputs(l_out_cnt).value;
1241 elsif l_outputs(l_out_cnt).name = 'B4_ACCRUAL_FF'
1242 then p_b4_accrual_ff := l_outputs(l_out_cnt).value;
1243 elsif l_outputs(l_out_cnt).name = 'B4_PAY_RF'
1244 then p_b4_pay_rf := l_outputs(l_out_cnt).value;
1245 elsif l_outputs(l_out_cnt).name = 'B4_BASE_RF'
1246 then p_b4_hours_rf := l_outputs(l_out_cnt).value;
1247 elsif l_outputs(l_out_cnt).name = 'B4_HOURLY_RATE_RF'
1248 then p_b4_hourly_rate_rf := l_outputs(l_out_cnt).value;
1249 elsif l_outputs(l_out_cnt).name = 'B4_REDUCED_FACTOR'
1250 then p_b4_reduced_factor := l_outputs(l_out_cnt).value;
1251 elsif l_outputs(l_out_cnt).name = 'B4_ACCRUAL_RF'
1252 then p_b4_accrual_rf := l_outputs(l_out_cnt).value;
1253 elsif l_outputs(l_out_cnt).name = 'B5_PAY_FF'
1254 then p_b5_pay_ff := l_outputs(l_out_cnt).value;
1255 elsif l_outputs(l_out_cnt).name = 'B5_BASE_FF'
1256 then p_b5_hours_ff := l_outputs(l_out_cnt).value;
1257 elsif l_outputs(l_out_cnt).name = 'B5_HOURLY_RATE_FF'
1258 then p_b5_hourly_rate_ff := l_outputs(l_out_cnt).value;
1259 elsif l_outputs(l_out_cnt).name = 'B5_FULL_FACTOR'
1260 then p_b5_full_factor := l_outputs(l_out_cnt).value;
1261 elsif l_outputs(l_out_cnt).name = 'B5_ACCRUAL_FF'
1262 then p_b5_accrual_ff := l_outputs(l_out_cnt).value;
1263 elsif l_outputs(l_out_cnt).name = 'B5_PAY_RF'
1264 then p_b5_pay_rf := l_outputs(l_out_cnt).value;
1265 elsif l_outputs(l_out_cnt).name = 'B5_BASE_RF'
1266 then p_b5_hours_rf := l_outputs(l_out_cnt).value;
1267 elsif l_outputs(l_out_cnt).name = 'B5_HOURLY_RATE_RF'
1268 then p_b5_hourly_rate_rf := l_outputs(l_out_cnt).value;
1269 elsif l_outputs(l_out_cnt).name = 'B5_REDUCED_FACTOR'
1270 then p_b5_reduced_factor := l_outputs(l_out_cnt).value;
1271 elsif l_outputs(l_out_cnt).name = 'B5_ACCRUAL_RF'
1272 then p_b5_accrual_rf := l_outputs(l_out_cnt).value;
1273 elsif l_outputs(l_out_cnt).name = 'B6_PAY_FF'
1274 then p_b6_pay_ff := l_outputs(l_out_cnt).value;
1275 elsif l_outputs(l_out_cnt).name = 'B6_BASE_FF'
1276 then p_b6_hours_ff := l_outputs(l_out_cnt).value;
1277 elsif l_outputs(l_out_cnt).name = 'B6_HOURLY_RATE_FF'
1278 then p_b6_hourly_rate_ff := l_outputs(l_out_cnt).value;
1279 elsif l_outputs(l_out_cnt).name = 'B6_FULL_FACTOR'
1280 then p_b6_full_factor := l_outputs(l_out_cnt).value;
1281 elsif l_outputs(l_out_cnt).name = 'B6_ACCRUAL_FF'
1282 then p_b6_accrual_ff := l_outputs(l_out_cnt).value;
1283 elsif l_outputs(l_out_cnt).name = 'B6_PAY_RF'
1284 then p_b6_pay_rf := l_outputs(l_out_cnt).value;
1285 elsif l_outputs(l_out_cnt).name = 'B6_BASE_RF'
1286 then p_b6_hours_rf := l_outputs(l_out_cnt).value;
1287 elsif l_outputs(l_out_cnt).name = 'B6_HOURLY_RATE_RF'
1288 then p_b6_hourly_rate_rf := l_outputs(l_out_cnt).value;
1289 elsif l_outputs(l_out_cnt).name = 'B6_REDUCED_FACTOR'
1290 then p_b6_reduced_factor := l_outputs(l_out_cnt).value;
1291 elsif l_outputs(l_out_cnt).name = 'B6_ACCRUAL_RF'
1292 then p_b6_accrual_rf := l_outputs(l_out_cnt).value;
1293 elsif l_outputs(l_out_cnt).name = 'B1_LABEL_FF'
1294 then p_b1_label_ff := l_outputs(l_out_cnt).value;
1295 elsif l_outputs(l_out_cnt).name = 'B2_LABEL_FF'
1296 then p_b2_label_ff := l_outputs(l_out_cnt).value;
1297 elsif l_outputs(l_out_cnt).name = 'B3_LABEL_FF'
1298 then p_b3_label_ff := l_outputs(l_out_cnt).value;
1302 then p_b5_label_ff := l_outputs(l_out_cnt).value;
1299 elsif l_outputs(l_out_cnt).name = 'B4_LABEL_FF'
1300 then p_b4_label_ff := l_outputs(l_out_cnt).value;
1301 elsif l_outputs(l_out_cnt).name = 'B5_LABEL_FF'
1303 elsif l_outputs(l_out_cnt).name = 'B6_LABEL_FF'
1304 then p_b6_label_ff := l_outputs(l_out_cnt).value;
1305 elsif l_outputs(l_out_cnt).name = 'B1_LABEL_RF'
1306 then p_b1_label_rf := l_outputs(l_out_cnt).value;
1307 elsif l_outputs(l_out_cnt).name = 'B2_LABEL_RF'
1308 then p_b2_label_rf := l_outputs(l_out_cnt).value;
1309 elsif l_outputs(l_out_cnt).name = 'B3_LABEL_RF'
1310 then p_b3_label_rf := l_outputs(l_out_cnt).value;
1311 elsif l_outputs(l_out_cnt).name = 'B4_LABEL_RF'
1312 then p_b4_label_rf := l_outputs(l_out_cnt).value;
1313 elsif l_outputs(l_out_cnt).name = 'B5_LABEL_RF'
1314 then p_b5_label_rf := l_outputs(l_out_cnt).value;
1315 elsif l_outputs(l_out_cnt).name = 'B6_LABEL_RF'
1316 then p_b6_label_rf := l_outputs(l_out_cnt).value;
1317 end if;
1318 end loop;
1319 --
1320 hr_utility.set_location(' After Loop',1);
1321 close csr_get_formula;
1322 else
1323 close csr_get_formula;
1324 end if;
1325 hr_utility.set_location(' Leaving Regularisation',10);
1326 return 0;
1327 --
1328 end regularisation;
1329 --
1330 ------------------------------------------------------------------------
1331 -- Function CHECK_EXISTING_OVERTIME_WEEK
1332 --
1333 -- This function will be called from the FR_OVERTIME_WEEK_PROCESS formula
1334 -- and will check whether there are any run results of type
1335 -- FR_OVERTIME_WEEK_PROCESS the same START and END Dates as those passed
1336 -- into the function. If such a record is found then the a warning is issued.
1337 ------------------------------------------------------------------------
1338 function check_existing_overtime_week
1339 (p_assignment_id number
1340 ,p_element_type_id number
1341 ,p_date_earned date
1342 ,p_week_end_date date) return varchar2 is
1343 --
1344 cursor get_elements(p_orig_ele varchar2,
1345 p_retr_ele varchar2) is
1346 select /*+ORDERED index(pet PAY_ELEMENT_TYPES_F_UK2) */
1347 max(decode(pet.element_name,p_orig_ele,pet.element_type_id)) orig_ele_id,
1348 max(decode(pet.element_name,p_retr_ele,pet.element_type_id)) retr_ele_id
1349 from pay_element_types_f pet
1350 where pet.element_name in (p_orig_ele,p_retr_ele)
1351 and pet.legislation_code = 'FR'
1352 and pet.business_group_id is null
1353 and p_date_earned between pet.effective_start_date
1354 and pet.effective_end_date;
1355 --
1356 cursor get_existing_week(p_orig_ele_id number,
1357 p_retr_ele_id number,
1358 p_week_end in varchar2) is
1359 select /*+ordered use_nl(i i2) */
1360 decode(sum(rr2.result_value),null,'N',0,'N','Y')
1361 from pay_assignment_actions a
1362 ,pay_run_results r
1363 ,pay_input_values_f i
1364 ,pay_run_result_values rr
1365 ,pay_input_values_f i2
1366 ,pay_run_result_values rr2
1367 where i.element_type_id = r.element_type_id
1368 and i.name = 'End Date'
1369 and i.business_group_id is null
1370 and i.legislation_code = 'FR'
1371 and p_date_earned
1372 between i.effective_start_date and i.effective_end_date
1373 and i2.element_type_id = r.element_type_id
1374 and i2.name = 'Processing Sequence'
1375 and i2.business_group_id is null
1376 and i2.legislation_code = 'FR'
1377 and p_date_earned
1378 between i2.effective_start_date and i2.effective_end_date
1379 and a.assignment_id = p_assignment_id
1380 and a.assignment_action_id = r.assignment_action_id
1381 and r.element_type_id in (p_orig_ele_id,p_retr_ele_id)
1382 and rr.run_result_id = r.run_result_id
1383 and i.input_value_id = rr.input_value_id
1384 and rr2.run_result_id = r.run_result_id
1385 and i2.input_value_id = rr2.input_value_id
1386 and rr.result_value = p_week_end
1387 and r.status in ('P','PA');
1388 --
1389 l_exists varchar2(1) := 'N';
1390 l_orig_ele_id number;
1391 l_retr_ele_id number;
1392 --
1393 l_proc varchar2(72) := g_package||'.check_existing_overtime_week';
1394 begin
1395 hr_utility.set_location(l_proc,10);
1396 --
1397 open get_elements('FR_OVERTIME_WEEK_PROCESS','FR_OVERTIME_WEEK_PROCESS_RETRO');
1398 fetch get_elements into l_orig_ele_id,l_retr_ele_id;
1399 close get_elements;
1400 open get_existing_week(l_orig_ele_id,l_retr_ele_id,
1401 fnd_date.date_to_canonical(p_week_end_date));
1402 fetch get_existing_week into l_exists;
1403 close get_existing_week;
1404 --
1405 return l_exists;
1406 end check_existing_overtime_week;
1407 --
1408 ------------------------------------------------------------------------
1409 -- Function GET_PERIOD_BALANCE
1410 --
1411 -- This function will sum the run results of a specified element and
1412 -- input value of a given period of time specified by input parameters.
1413 -- In order that this can be done the element in question must have at
1414 -- least one date input value in addition to the input value to be summed.
1415 ------------------------------------------------------------------------
1416 function get_period_balance
1417 (p_assignment_id number
1418 ,p_date_earned date
1419 ,p_business_group_id number
1420 ,p_element varchar2
1421 ,p_start_input varchar2
1422 ,p_start_date date
1423 ,p_end_input varchar2
1424 ,p_end_date date
1425 ,p_value_input varchar2) return number is
1426 --
1430 max(decode(piv.name,p_start_input,piv.input_value_id)) start_iv,
1427 cursor get_element is
1428 select /*+ORDERED index(pet PAY_ELEMENT_TYPES_F_UK2) */ pet.element_type_id,
1429 max(decode(piv.name,p_value_input,piv.input_value_id)) value_iv,
1431 max(decode(piv.name,p_end_input, piv.input_value_id)) end_iv
1432 from pay_element_types_f pet,
1433 pay_input_values_f piv
1434 where pet.element_name = p_element
1435 and (pet.legislation_code = 'FR' or
1436 pet.business_group_id = p_business_group_id)
1437 and p_date_earned between pet.effective_start_date
1438 and pet.effective_end_date
1439 and piv.element_type_id = pet.element_type_id
1440 and p_date_earned between piv.effective_start_date
1441 and piv.effective_end_date
1442 and piv.name in (p_value_input,p_start_input,p_end_input)
1443 group by pet.element_type_id;
1444 --
1445 cursor get_balance(p_element_type_id number,
1446 p_value_iv number, p_start_iv number,
1447 p_end_iv number, p_start_date_chr varchar2,
1448 p_end_date_chr varchar2) is
1449 select /*+ORDERED */
1450 sum(to_number(rr.result_value))
1451 from pay_assignment_actions a
1452 , pay_run_results r
1453 , pay_run_result_values rrsd
1454 , pay_run_result_values rred
1455 , pay_run_result_values rr
1456 where a.assignment_id = p_assignment_id
1457 and a.assignment_action_id = r.assignment_action_id
1458 and r.element_type_id = p_element_type_id
1459 and rr.run_result_id = r.run_result_id
1460 and rr.input_value_id = p_value_iv
1461 and rrsd.run_result_id = r.run_result_id
1462 and rrsd.input_value_id = p_start_iv
1463 and rred.run_result_id = r.run_result_id
1464 and rred.input_value_id = p_end_iv
1465 and rred.result_value <= p_end_date_chr
1466 and rrsd.result_value >= p_start_date_chr
1467 and r.status in ('P','PA');
1468 --
1469 l_ele get_element%ROWTYPE;
1470 l_balance number;
1471 --
1472 l_proc varchar2(72) := g_package||'.get_period_balance';
1473 begin
1474 hr_utility.set_location(l_proc,10);
1475 --
1476 open get_element;
1477 fetch get_element into l_ele;
1478 if get_element%FOUND then
1479 open get_balance(l_ele.element_type_id,l_ele.value_iv,
1480 l_ele.start_iv, l_ele.end_iv,
1481 fnd_date.date_to_canonical(p_start_date),
1482 fnd_date.date_to_canonical(p_end_date));
1483 fetch get_balance into l_balance;
1484 close get_balance;
1485 end if;
1486 close get_element;
1487 --
1488 if l_balance is null then
1489 l_balance := 0;
1490 end if;
1491 --
1492 return l_balance;
1493 end get_period_balance;
1494 --
1495 --
1496 ------------------------------------------------------------------------
1497 -- Function GET_NORMAL_WEEK_HOURS
1498 --
1499 -- This function will retrieve the normal working hours as of the overtime
1500 -- week end date and convert them into a weekly frequency
1501 ------------------------------------------------------------------------
1502 function get_normal_week_hours
1503 (p_business_group_id number
1504 ,p_assignment_id number
1505 ,p_effective_date date) return number is
1506 --
1507 cursor get_hours is
1508 select normal_hours,frequency
1509 from per_all_assignments_f
1510 where assignment_id = p_assignment_id
1511 and p_effective_date
1512 between effective_start_date and effective_end_date;
1513 --
1514 l_normal_hours number;
1515 l_frequency varchar2(30);
1516 l_hours number;
1517 --
1518 begin
1519 hr_utility.trace('Business Group = '||to_char(p_business_group_id));
1520 hr_utility.trace('Assignment ID = '||to_char(p_assignment_id));
1521 hr_utility.trace('Effective Date = '||to_char(p_effective_date,'YYYYMMDD'));
1522 open get_hours;
1523 fetch get_hours into l_normal_hours,l_frequency;
1524 if get_hours%notfound then
1525 close get_hours;
1526 fnd_message.set_name('PAY','PAY_74980_MISSING_HOURS');
1527 fnd_message.raise_error;
1528 end if;
1529 close get_hours;
1530 --
1531 hr_utility.trace('Found Assignment');
1532 if l_normal_hours is null or l_frequency is null then
1533 fnd_message.set_name('PAY','PAY_74980_MISSING_HOURS');
1534 fnd_message.raise_error;
1535 end if;
1536 --
1537 l_hours := pay_fr_general.convert_hours(p_effective_date
1538 ,p_business_group_id
1539 ,p_assignment_id
1540 ,l_normal_hours
1541 ,l_frequency
1542 ,'W');
1543 --
1544 return l_hours;
1545 --
1546 end get_normal_week_hours;
1547 --
1548 end pay_fr_overtime;
[Home] [Help]