1 Package Body ssp_ern_bus as
2 /* $Header: spernrhi.pkb 120.5.12010000.2 2008/08/13 13:25:38 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ssp_ern_bus.'; -- Global package name
9 --
10 -- global variable used to pass the number of payment_periods, calculated in
11 -- procedure do_standard_calculation, to the form SSPWSENT.
12 form_variable number := null;
13 --
14 --
15 -- Business Validation Rules
16 --
17 --
18 -----------------------------------------------------------------------------
19 -- |---------------------------------< Check_Person_id >---------------------
20 -- --------------------------------------------------------------------------
21 --
22 -- PUBLIC
23 -- Description:
24 -- Ensure that a valid person id is entered
25 --
26 Procedure check_person_id (p_person_id in number,
27 p_effective_date in date) is
28 l_proc varchar2(72) := g_package||'Check_Person_id';
29 cursor c1 is
30 select p.rowid
31 from per_all_people_f p
32 where p.person_id = p_person_id
33 and p_effective_date between p.effective_start_date and
34 p.effective_end_date;
35 c1_rec c1%ROWTYPE;
36 Begin
37 hr_utility.set_location('Entering:'||l_proc, 1);
38 open c1;
39 fetch c1 into c1_rec;
40 if c1%NOTFOUND then
41 fnd_message.set_name ('SSP' , 'SSP_35049_INV_PERSON_EFF_DATE' );
42 fnd_message.raise_error;
43 end if;
44 close c1;
45 hr_utility.set_location('Leaving :'||l_proc, 100);
46 End check_person_id;
47 --
48 --
49 -- ------------------------------------------------------------------------
50 -- |-----------------------------< Check_Effective_Date >-------------------
51 -- ------------------------------------------------------------------------
52 --
53 -- PUBLIC
54 -- Description:
55 -- Ensure that a valid effective date is entered for a PErson
56 --
57 Procedure check_effective_date (p_person_id in number,
58 p_effective_date in date) is
59
60 l_proc varchar2(72) := g_package||'Check_Effective_Date';
61
62 cursor c2 is
63 select s.person_id
64 from per_periods_of_service s
65 where s.person_id = p_person_id
66 and p_effective_date between s.date_start and
67 nvl(s.actual_termination_date,hr_general.end_of_time);
68
69 c2_rec c2%ROWTYPE;
70
71 BEGIN
72 hr_utility.set_location('Entering:'||l_proc, 1);
73
74 open c2;
75 fetch c2 into c2_rec;
76 if c2%NOTFOUND then
77 fnd_message.set_name ('SSP' , 'SSP_35050_INV_EFFECTIVE_DATE' );
78 fnd_message.raise_error;
79 end if;
80 close c2;
81 hr_utility.set_location('Leaving :'||l_proc, 100);
82 END check_effective_date;
83 --
84 --
85 -- ------------------------------------------------------------------------
86 -- |----------------------< calculate_average_earnings >-------------------
87 -- ------------------------------------------------------------------------
88 --
89 -- PUBLIC
90 -- Description:
91 --
92 -- Calculate the average weekly earnings of a person over an approximate
93 -- 8 week period prior to a specified date. The calculation method is in
94 -- accordance with the requirements laid down for SSP/SMP in DSS document
95 -- CA30 (NI270) from April 1995
96 --
97 --
98 PROCEDURE CALCULATE_AVERAGE_EARNINGS (
99 p_person_id in number,
100 p_effective_date in date,
101 p_average_earnings_amount out nocopy number,
102 p_user_entered in varchar2 default 'Y',
103 p_absence_category in varchar2 --DFoster 1304683
104 ) is
105 --
106 l_proc varchar2(72) := g_package
107 ||'calculate_average_earnings';
108 type date_table is table of date index by binary_integer;
109 l_assignment_average number := 0;
110 l_person_average number := 0;
111 l_period_of_service_id number := null;
112 l_hire_date date := null;
113 l_payroll_frequency varchar2 (30);
114 l_payday date_table;
115 l_start_of_relevant_period date := null;
116 l_end_of_relevant_period date := null;
117 l_start_of_coverage date := null;
118 l_end_of_coverage date := null;
119 l_assignment_id number := null;
120 l_payroll_id number := null;
121 l_new_employee boolean := FALSE;
122 cannot_derive_earnings exception;
123 --
124 -- added variables for checking re-hired employees(abhaduri)
125 l_earlier_term_date date;
126 l_noof_periods_service number :=0;
127 --
128 cursor csr_NIable_earnings (p_balance_name in varchar2) is
129 --
130 -- Calculate the total of all NIable pay in a given period for an
131 -- assignment.
132 --
133 select /*+ ORDERED USE_NL(RUN_VALUE, RUN_RESULT, FEED, BALANCE) */
134 nvl (sum (nvl (run_value.result_value, 0) * feed.scale),0) EARNINGS
135 from pay_assignment_actions ASG_ACTION,
136 pay_payroll_actions PAY_ACTION,
137 per_time_periods PERIOD,
138 pay_balance_types BALANCE,
139 pay_balance_feeds_f FEED,
140 pay_run_results RUN_RESULT,
141 pay_run_result_values RUN_VALUE
142 --
143 -- where the tables join via primary/foreign keys
144 where pay_action.payroll_action_id = asg_action.payroll_action_id
145 and run_result.assignment_action_id=asg_action.assignment_action_id
146 and run_result.run_result_id = run_value.run_result_id
147 and run_value.input_value_id = feed.input_value_id
148 and feed.balance_type_id = balance.balance_type_id
149 and period.time_period_id = pay_action.time_period_id
150 and period.regular_payment_date between feed.effective_start_date and feed.effective_end_date
151 --
152 -- and the earnings are for the specified assignment
153 and asg_action.assignment_id = L_ASSIGNMENT_ID
154 --
155 -- and the run result has been processed
156 and run_result.status in ('P','PA') --like 'P%'
157 --
158 and balance.balance_name = p_balance_name
159 and period.end_date between L_START_OF_RELEVANT_PERIOD and L_END_OF_RELEVANT_PERIOD;
160 --
161 cursor csr_set_of_current_assignments is
162 --
163 -- Get all a person's assignments which fall within a period of service.
164 -- Retrieve a row for each assignment/payroll combination so that we can
165 -- treat payroll transfers effectively as if they were separate
166 -- assignments.
167 --
168 -- Payroll_id not returned as causes do_standard_calculation /
169 -- do_monthly_calculation to be called twice if there has been a change
170 -- of payroll, and the relevant period ends up being calculated
171 -- incorrectly.
172 --
173 select distinct
174 asg.assignment_id
175 from per_all_assignments_f ASG
176 where asg.period_of_service_id = L_PERIOD_OF_SERVICE_ID
177 and asg.payroll_id is not null
178 --6791913 begin - To treat adoption in the same way as Maternity
179 --and (( p_absence_category = 'M'
180 and (( p_absence_category in ('M','GB_ADO')
181 --6791913 end
182 and effective_end_date >= (p_effective_date - 68))
183 or -- p_absence = 'S'
184 effective_end_date >= (p_effective_date - 62)
185 );
186 --
187 -- (abhaduri)added cursor to check whether employee has been re-hired
188 cursor csr_noof_periods_service is
189 -- check the no of rows in per_periods_of_service table
190 -- to calculate the no of times
191 -- the employee has been employed by the employer
192 select count(*) from per_periods_of_service
193 where person_id = p_person_id;
194 --
195 -- (abhaduri) added cursor to get the last termination date
196 -- if employer has been employed more than once by the employer
197 cursor csr_earlier_term is
198 -- get the termination date if it is not null
199 select nvl(max(actual_termination_date),to_date('01/01/01','DD/MM/YY'))
200 from per_periods_of_service
201 where person_id = p_person_id
202 and actual_termination_date is not null;
203 --
204
205 procedure derive_relevant_period is
206 --
207 -- Derive the 8-week period before the latest payday before the
208 -- effective date.
209 --
210 l_proc varchar2 (72) := g_package||'derive_relevant_period';
211 l_temp number;
212 --
213 cursor csr_end_period_m is
214 --
215 -- Get the end date of the last payroll period prior to the
216 -- end of the week of the effective date for Maternities.
217 --
218
219 select max (period.end_date)
220 from per_time_periods PERIOD
221 where period.payroll_id = L_PAYROLL_ID
222 and period.regular_payment_date <= P_EFFECTIVE_DATE +6;
223
224 --
225
226 cursor csr_end_period_m2 is
227 --
228 -- Get the end date of the last payroll period prior to the
229 -- end of the week of the effective date for Maternities.
230
231 -- This cursor will be called when a new payroll has been
232 -- assigned to the employee and there has been no
233 -- payroll runs and the employee is going on a maternity leave.
234 -- The cursor csr_end_period_m would return null, which is incorrect.
235
236
237 select max(ptp.end_date)
238 from per_time_periods ptp
239 where ptp.payroll_id in
240 ( select papf.payroll_id
241 from pay_all_payrolls_f papf,
242 per_all_assignments_f paf,
243 per_all_people_f ppf,
244 per_time_periods ptp
245 where ppf.person_id = paf.person_id
246 and papf.payroll_id = paf.payroll_id
247 and paf.payroll_id = ptp.payroll_id
248 and ptp.regular_payment_date <=
249 P_EFFECTIVE_DATE + 6
250 and paf.assignment_id = l_assignment_id)
251 and ptp.regular_payment_date <= P_EFFECTIVE_DATE + 6 ;
252 --
253 cursor csr_end_period_s is
254 --
255 -- Get the end date of the last payroll period prior to the
256 -- end of the week of the effective date for Sicknesses.
257 --
258 select max (period.end_date)
259 from per_time_periods PERIOD
260 where period.payroll_id = L_PAYROLL_ID
261 and period.regular_payment_date <= P_EFFECTIVE_DATE;
262
263 --
264 cursor csr_end_period_s2 is
265 --
266 -- Get the end date of the last payroll period prior to the
267 -- end of the week of the effective date for Sicknesses.
268 --
269 -- This cursor will be called when a new payroll has been
270 -- assigned to the employee and there has been no
271 -- payroll runs and the employee is going on a leave.
272 -- The cursor csr_end_period_s would return null, which is incorrect.
273
274 select max(ptp.end_date)
275 from per_time_periods ptp
276 where ptp.payroll_id in
277 ( select papf.payroll_id
278 from pay_all_payrolls_f papf,
279 per_all_assignments_f paf,
280 per_all_people_f ppf,
281 per_time_periods ptp
282 where ppf.person_id = paf.person_id
283 and papf.payroll_id = paf.payroll_id
284 and paf.payroll_id = ptp.payroll_id
285 and ptp.regular_payment_date <=
286 P_EFFECTIVE_DATE
287 and paf.assignment_id = l_assignment_id)
288 and ptp.regular_payment_date <= P_EFFECTIVE_DATE ;
289 --
290 cursor csr_start_period is
291 --
292 -- Get the start date of the payroll period which was at least
293 -- 8 weeks prior to the end of the relevant period.
294 --
295 select max (period.end_date) +1
296 from per_time_periods PERIOD
297 where period.payroll_id = L_PAYROLL_ID
298 and period.end_date <= L_END_OF_RELEVANT_PERIOD - 56;
299 --
300 -- Cursor to return the payroll_id, as is no longer returned in
301 -- csr_set_of_current_assignments above, but is required in the
302 -- calculation of the relevant_period_start_date. The cursor returns
303 -- the payroll_id that the assignment is on, 56 days before the
304 -- l_end_of_relevant_period.
305 --
306 cursor csr_get_payroll_id_start is
307 select ppf.payroll_id
308 from pay_all_payrolls_f ppf
309 , per_all_assignments_f paf
310 where ppf.payroll_id = paf.payroll_id
311 and paf.assignment_id = l_assignment_id
312 and l_start_of_relevant_period between
313 paf.effective_start_date and paf.effective_end_date;
314 --
315 -- Get payroll id for this assignment just before the start of the absence so
316 -- we can then find the regular payment date which is used to identify the end
317 -- of the last eight weeks for calculating average earnings.
318 --
319 cursor csr_get_payroll_id_end is
320 select payroll_id
321 from per_all_assignments_f paf
322 where paf.assignment_id = L_ASSIGNMENT_ID
323 and P_EFFECTIVE_DATE between
324 paf.effective_start_date and paf.effective_end_date;
325
326 --
327 --
328 cursor csr_chk_asg is
329 select 1
330 from per_all_assignments_f
331 where assignment_id = L_ASSIGNMENT_ID
332 and l_start_of_relevant_period between effective_start_date and effective_end_date;
333
334 cursor csr_get_start_date is
335 select min(effective_start_date)
336 from per_all_assignments_f
337 where assignment_id = L_ASSIGNMENT_ID;
338 --
339 --
340 begin
341 --
342 hr_utility.set_location('Entering:'||l_proc,1);
343 --
344 open csr_get_payroll_id_end;
345 fetch csr_get_payroll_id_end into l_payroll_id;
346 close csr_get_payroll_id_end;
347
348 --
349 -- Bug 1304683 DFoster
350 -- Get the end date of the last pay period where the regular payment
351 -- date is just before the effective date depending on whether a
352 -- Sickness or a Maternity.
353 --
354 --6791913 Begin
355 --if p_absence_category = 'M' then
356 if p_absence_category in ('M','GB_ADO') then
357 --6791913 End
358 open csr_end_period_m;
359 fetch csr_end_period_m into l_end_of_relevant_period;
360 close csr_end_period_m;
361
362 if l_end_of_relevant_period is null then
363 hr_utility.trace (l_proc||' finding the end of relevant period using the assignment id and not using the payroll id');
364 open csr_end_period_m2;
365 fetch csr_end_period_m2 into l_end_of_relevant_period;
366 close csr_end_period_m2;
367 end if;
368
369 else --if p_absence_category = 'S' then
370 open csr_end_period_s;
371 fetch csr_end_period_s into l_end_of_relevant_period;
372 close csr_end_period_s;
373
374 if l_end_of_relevant_period is null then
375 hr_utility.trace (l_proc||' finding the end of relevant period using the assignment id and not using the payroll id');
376 open csr_end_period_s2;
377 fetch csr_end_period_s2 into l_end_of_relevant_period;
378 close csr_end_period_s2;
379 end if;
380
381 end if;
382 --
383 -- the above csrs can return null when a new employee is hired
384 -- assigned to new payroll and goes on sick leave!
385
386 l_start_of_relevant_period := l_end_of_relevant_period - 56;
387 --
388 --
389 -- the payrll id is reset to null as if the csr_get_payroll_id_start
390 -- returns no rows, the old value of payroll id is retained
391 -- and this causes error as l_start_of_relevant_period is not
392 -- set to l_hire_date;
393 -- the above csr can return null when a new employee is hired
394 -- assigned to new payroll and foes on sick leave!
395
396 l_payroll_id := null;
397
398 open csr_get_payroll_id_start;
399 fetch csr_get_payroll_id_start into l_payroll_id;
400 close csr_get_payroll_id_start;
401 --
402 if l_payroll_id is null then
403 if l_end_of_relevant_period is null then
404 -- -1 because, the end date is the previous months payroll
405 -- end date
406 l_end_of_relevant_period := l_hire_date - 1 ;
407 l_start_of_relevant_period := l_end_of_relevant_period - 56;
408 else
409 l_start_of_relevant_period := l_hire_date;
410 -- if the assignment doesn't exits at the hire date (ie, multiple asg)
411 -- then get the assignment start date
412 open csr_chk_asg;
413 fetch csr_chk_asg into l_temp;
414 if csr_chk_asg%notfound then
415 open csr_get_start_date;
416 fetch csr_get_start_date into l_start_of_relevant_period;
417 close csr_get_start_date;
418 end if;
419 close csr_chk_asg;
420 end if;
421 else
422 open csr_start_period;
423 fetch csr_start_period into l_start_of_relevant_period;
424 close csr_start_period;
425 end if;
426 --
427 hr_utility.trace (l_proc||' start of relevant period = '
428 ||to_char (l_start_of_relevant_period));
429 hr_utility.trace (l_proc||' end of relevant period = '
430 ||to_char (l_end_of_relevant_period));
431 --
432 hr_utility.set_location('Leaving :'||l_proc,100);
433 --
434 end derive_relevant_period;
435 --
436 procedure get_payroll_frequency is
437 --
438 -- Find out what payroll frequency the assignment is using
439 --
440 l_proc varchar2 (72) := g_package||'get_payroll_frequency';
441 --
442 cursor csr_payroll_frequency is
443 --
444 -- Get the payroll frequency for an assignment
445 --
446 -- This now returns all the payrolls that the person is on between the
447 -- start and end of the 'relevant period', rather than just the payroll
448 -- that the person is on as of the p_effective_date which is the
449 -- PIW_start_date(SSP) or QW_start_date(SMP).
450 --
451 select period_type.number_per_fiscal_year fiscal_year
452 from pay_all_payrolls_f PAYROLL,
453 per_all_assignments_f ASSIGNMENT,
454 per_time_period_types PERIOD_TYPE
455 where assignment.assignment_id = l_assignment_id
456 and assignment.effective_start_date <= l_end_of_relevant_period
457 and assignment.effective_end_date >= l_start_of_relevant_period
458 and payroll.payroll_id = assignment.payroll_id
459 and payroll.period_type = period_type.period_type
460 and payroll.effective_start_date <= l_end_of_relevant_period
461 and payroll.effective_end_date >= l_start_of_relevant_period;
462 --
463 periods_per_fiscal_year number := 0;
464 --
465 begin
466 --
467 hr_utility.set_location('Entering:'||l_proc,1);
468 --
469 l_payroll_frequency := 'MONTHLY';
470 --
471 for each_payroll in csr_payroll_frequency loop
472 if each_payroll.fiscal_year <> 12 then
473 l_payroll_frequency := 'NOT MONTHLY';
474 end if;
475 end loop;
476 --
477 hr_utility.trace ('l_payroll_frequency = '||l_payroll_frequency);
478 --
479 hr_utility.set_location('Leaving :'||l_proc,100);
480 --
481 end get_payroll_frequency;
482 --
483 procedure get_period_of_service is
484 --
485 -- Get the current period of service for the person
486 --
487 l_proc varchar2 (72) := g_package||'get_period_of_service';
488 --
489 cursor csr_period_of_service is
490 --
491 -- Get the period of service current as of a specified date
492 --
493 select service.period_of_service_id,
494 service.date_start
495 from per_periods_of_service SERVICE
496 where person_id = p_person_id
497 and p_effective_date between service.date_start
498 and nvl (service.actual_termination_date,
499 hr_general.end_of_time);
500 --
501 begin
502 --
503 hr_utility.set_location('Entering:'||l_proc,1);
504 --
505 -- Get the period of service current as of the effective date
506 --
507 open csr_period_of_service;
508 fetch csr_period_of_service into l_period_of_service_id, l_hire_date;
509 close csr_period_of_service;
510 --
511 hr_utility.trace (l_proc||' l_period_of_service_id = '
512 ||to_char (l_period_of_service_id));
513 hr_utility.trace (l_proc||' l_hire_date = '
514 ||to_char (l_hire_date));
515 --
516 hr_utility.set_location('Leaving :'||l_proc,100);
517 --
518 end get_period_of_service;
519 --
520 procedure check_payroll_installed is
521 --
522 -- Checks that Payroll is installed before calculation of earnings is
523 -- attempted.
524 -- This code was copied and modified from hrapiapi.pkb
525 --
526 l_proc varchar2 (72) := g_package||'check_payroll_installed';
527 l_pa_installed fnd_product_installations.status%TYPE;
528 l_industry fnd_product_installations.industry%TYPE;
529 l_pa_appid fnd_product_installations.application_id%TYPE := 801;
530 payroll_not_found exception;
531 --
532 Begin
533 --
534 hr_utility.set_location('Entering:'||l_proc,1);
535 --
536 -- We need to determine if Payroll is installed.
537 if (fnd_installation.get(
538 --
539 appl_id => l_pa_appid,
540 dep_appl_id => l_pa_appid,
541 status => l_pa_installed,
542 industry => l_industry))
543 then
544 --
545 -- Check to see if the status = 'I'
546 --
547 If (l_pa_installed = 'I') then
548 return; -- Payroll is installed
549 else
550 raise payroll_not_found;
551 end If;
552 --
553 else
554 raise payroll_not_found;
555 end If;
556 --
557 hr_utility.set_location('Leaving :'||l_proc,100);
558 --
559 exception
560 when payroll_not_found then
561 --
562 -- Set warning message:
563 -- "Average Earnings cannot be calculated automatically unless
564 -- you have installed Oracle Payroll. You must enter the figure
565 -- yourself."
566 --
567 ssp_smp_support_pkg.reason_for_no_earnings
568 := 'SSP_35024_NEED_PAYROLL_FOR_ERN';
569 raise cannot_derive_earnings;
570 --
571 end check_payroll_installed;
572 --
573 procedure stop_if_a_director is
574 --
575 cursor csr_director is
576 select 1
577 from per_all_people_f
578 where per_information2 = 'Y' -- Director_flag
579 and person_id = p_person_id
580 and p_effective_date between effective_start_date
581 and effective_end_date;
582 --
583 l_proc varchar2 (72) := g_package||'stop_if_a_director';
584 l_dummy integer (1) := null;
585 l_person_is_director boolean := FALSE;
586 --
587 begin
588 --
589 hr_utility.set_location('Entering:'||l_proc,1);
590 --
591 open csr_director;
592 fetch csr_director into l_dummy;
593 l_person_is_director := csr_director%found;
594 close csr_director;
595 --
596 if l_person_is_director then
597 --
598 -- Set the warning message text to:
599 -- "Oracle Payroll is unable to calculate the earnings of directors
600 -- because it has no way to distinguish between voted fees and fees
601 -- drawn in anticipation of voting. Please enter the average earnings
602 -- figure for directors yourself."
603 --
604 ssp_smp_support_pkg.reason_for_no_earnings
605 := 'SSP_35025_NO_DIRECTOR_EARNINGS';
606 raise cannot_derive_earnings;
607 --
608 end if;
609 --
610 hr_utility.set_location('Leaving :'||l_proc,100);
611 --
612 end stop_if_a_director;
613 --
614 function gross_NIable_pay
615 --
616 -- Calculate the gross NIable pay for an 8 week period
617 --
618 return number is
619 --
620 l_proc varchar2 (72) := g_package||'gross_NIable_pay';
621 l_lel number := 0;
622 l_weekly_pay number(18,8) := 0;
623 l_gross_NIable_pay number := 0;
624 l_gross_NIable_pay_acc number(18,8) := 0;
625 --
626 begin
627 --
628 hr_utility.set_location('Entering:'||l_proc,1);
629 --
630 for csr_Ne in csr_NIable_earnings ('NIable Pay')
631 loop
632 l_gross_NIable_pay_acc := csr_Ne.EARNINGS;
633 l_gross_NIable_pay := l_gross_NIable_pay_acc;
634 end loop;
635 --
636 hr_utility.trace('L_GROSS_NIABLE: '||l_gross_NIable_pay);
637 --
638 l_weekly_pay := l_gross_NIable_pay_acc * 6 / 52;
639 l_lel := SSP_SMP_SUPPORT_PKG.NI_Lower_Earnings_Limit(
640 L_END_OF_RELEVANT_PERIOD);
641 hr_utility.trace('l_lel: '||l_lel);
642 --
643 if l_weekly_pay < l_lel
644 then
645 for csr_Ne in csr_NIable_earnings ('NIable Earnings 1B')
646 loop
647 l_gross_NIable_pay_acc := l_gross_NIable_pay_acc+csr_Ne.EARNINGS;
648 l_gross_NIable_pay := l_gross_NIable_pay_acc;
649 --
650 hr_utility.trace('L_GROSS_NIABLE inc 1B: '||l_gross_NIable_pay);
651 end loop;
652 end if;
653 --
654 hr_utility.set_location('Leaving :'||l_proc,100);
655 --
656 return l_gross_NIable_pay;
657 --
658 end gross_NIable_pay;
659 --
660 procedure do_monthly_calculation is
661 --
662 -- Calculate average earnings for an assignment on a monthly payroll.
663 -- We handle calendar monthly payrolls separately because they have
664 -- unequal numbers of days and so using the normal calculation method
665 -- would give different results depending upon which months were being
666 -- studied.
667 --
668 l_proc varchar2 (72) := g_package||'do_monthly_calculation';
669 --
670 begin
671 --
672 hr_utility.set_location('Entering:'||l_proc,1);
673 --
674 -- Take the gross payments from the last 2 months, multiply by 6 for
675 -- the annual figure and divide by 52 for the weekly average
676 --
677 l_assignment_average :=
678 l_assignment_average + ((gross_NIable_pay * 6) / 52);
679 --
680 hr_utility.trace (l_proc||' gross_NIable_pay = '
681 ||to_char (gross_NIable_pay));
682 --
683 hr_utility.set_location('Leaving :'||l_proc,100);
684 --
685 end do_monthly_calculation;
686 --
687 procedure do_standard_calculation is
688 --
689 -- Calculate average weekly earnings for an assignment with any payroll
690 -- frequency other than monthly, ie those consisting of equal numbers
691 -- of days.
692 --
693 l_proc varchar2 (72) := g_package||'do_standard_calculation';
694 l_days_covered number := (l_end_of_relevant_period
695 - greatest (l_start_of_relevant_period,
696 l_hire_date))
697 +1;
698 --
699 -- Csr_get_number_of_reg_payments is used in the new method of
700 -- calculating weekly average earnings. Users will have to enter a
701 -- value for a new element 'Average Earnings Period' to state the
702 -- number of regular payment periods being processed in the one payroll
703 -- process. If a value is returned then an irregular number of payments
704 -- have been processed within the relevant period. The average amount
705 -- is calculated as the total gross NIable pay / the number of periods
706 -- paid. The value entered in this element is used to calculate the
707 -- actual number of payment periods.
708 --
709 cursor csr_get_number_of_reg_payments is
710 select peev.screen_entry_value
711 from per_all_assignments_f paf
712 , pay_element_entry_values_f peev
713 , pay_element_entries_f pee
714 , pay_element_types_f pet
715 , pay_element_links_f pel
716 where pee.element_entry_id = peev.element_entry_id
717 and pee.assignment_id = paf.assignment_id
718 and pet.element_type_id = pel.element_type_id
719 and pel.element_link_id = pee.element_link_id
720 and paf.assignment_id = l_assignment_id
721 and pet.element_name = 'Average Earnings Period'
722 and peev.effective_start_date between paf.effective_start_date
723 and paf.effective_end_date
724 and peev.effective_start_date between pee.effective_start_date
725 and pee.effective_end_date
726 and peev.effective_start_date between pet.effective_start_date
727 and pet.effective_end_date
728 and peev.effective_start_date between pel.effective_start_date
729 and pel.effective_end_date
730 and peev.effective_start_date
731 between greatest(l_start_of_relevant_period, l_hire_date)
732 and l_end_of_relevant_period
733 and peev.effective_end_date
734 between greatest(l_start_of_relevant_period, l_hire_date)
735 and l_end_of_relevant_period;
736 --
737 -- csr_number_of_days returns the payroll frequency that an assignment
738 -- is on during the relevant period. If a person changes payroll within
739 -- the relevant period, then they are considered to be on an irregular
740 -- payroll and as such are calculated using do_standard_calculation,
741 -- even if the person was on a Monthly payroll as some stage during the
742 -- relevant period. Thus, if do_standard_calculation is being executed
743 -- it is not for a Monthly payroll and so we never want csr_number_of
744 -- _days to return a fiscal_year value of 12 (i.e. Monthly).
745 --
746 cursor csr_number_of_days is
747 select period_type.number_per_fiscal_year fiscal_year
748 from pay_all_payrolls_f PAYROLL,
749 per_all_assignments_f ASSIGNMENT,
750 per_time_period_types PERIOD_TYPE
751 where assignment.assignment_id = l_assignment_id
752 and payroll.payroll_id = assignment.payroll_id
753 and payroll.period_type = period_type.period_type
754 and payroll.effective_start_date <= l_end_of_relevant_period
755 and payroll.effective_end_date >= l_start_of_relevant_period
756 and period_type.number_per_fiscal_year <> 12;
757 --
758 number_of_payments number;
759 total_number_payments number;
760 payroll_freq number;
761 number_of_days number;
762 user_ent_multi_reg_pays boolean;
763 expected_num_of_periods number := null;
764 --
765 begin
766 --
767 hr_utility.set_location('Entering:'||l_proc,1);
768 hr_utility.trace('days covered orig: '||l_days_covered);
769 --
770 -- Take the gross payments from the relevant period, divide by the
771 -- number of days the payments cover, and multiply by 7.
772 --
773 number_of_payments := 0;
774 total_number_payments := 0;
775 --
776 for payments in csr_get_number_of_reg_payments loop
777 exit when csr_get_number_of_reg_payments%NOTFOUND
778 or csr_get_number_of_reg_payments%NOTFOUND is null;
779 if payments.screen_entry_value < 1 then
780 number_of_payments := -1;
781 elsif payments.screen_entry_value >= 1 then
782 number_of_payments := payments.screen_entry_value -1;
783 end if;
784
785 total_number_payments := total_number_payments + number_of_payments;
786 user_ent_multi_reg_pays := TRUE;
787 hr_utility.trace('NUMBER OF PAYMENTS: '||number_of_payments);
788 end loop;
789
790 hr_utility.trace('TOTAL NUM PAYMENTS: '||total_number_payments);
791 --
792 if user_ent_multi_reg_pays then
793 --
794 payroll_freq := 0;
795 number_of_days := 0;
796 --
797 open csr_number_of_days;
798 fetch csr_number_of_days into payroll_freq;
799 close csr_number_of_days;
800 --
801 number_of_days := round(365/payroll_freq);
802 --
803 hr_utility.trace('PAYROLL FREQ: '||payroll_freq);
804 hr_utility.trace('NUMBER OF DAYS: '||number_of_days);
805 --
806 -- Next bit of code is for outputting the number of payments to the ssp entries
807 -- form. The value in form_variable is passed to the form via the function
808 -- number_of_periods. If the total_number_payments is 0 then then number of
809 -- payments in the relevant period is the regular number, so the value is not
810 -- output to the form. The user only wants to see a value on the form if it is
811 -- an irregular number of payment periods.
812 --
813 expected_num_of_periods := l_days_covered/number_of_days;
814 --
815 if total_number_payments = 0 then
816 form_variable := null;
817 else
818 form_variable := expected_num_of_periods + total_number_payments;
819 end if;
820 --
821 hr_utility.trace('expected num of periods '||expected_num_of_periods||
822 ', form variable '||form_variable);
823 --
824 l_days_covered := l_days_covered +
825 (number_of_days * total_number_payments);
826 end if;
827 hr_utility.trace('DAYS COVERED: '||l_days_covered);
828 --
829 if l_days_covered < 1 then
830 l_assignment_average := 0;
831 else
832 l_assignment_average :=
833 l_assignment_average + ((gross_NIable_pay
834 -----------------
835 / l_days_covered)
836 * 7);
837 end if;
838 --
839 hr_utility.trace('GROSS NIABLE PAY: '||gross_NIable_pay);
840 hr_utility.trace ('l_days_covered = '||to_char (l_days_covered));
841 hr_utility.trace ('l_assignment_average = '
842 ||to_char (l_assignment_average));
843 --
844 hr_utility.set_location('Leaving :'||l_proc,100);
845 --
846 end do_standard_calculation;
847 --
848 begin
849 --
850 hr_utility.set_location('Entering:'||l_proc, 1);
851 hr_utility.trace('P_EFFECTIVE_DATE IS: '||p_effective_date);
852 --
853 check_payroll_installed;
854 stop_if_a_director;
855 get_period_of_service;
856 --
857 FOR each_assignment in csr_set_of_current_assignments
858 LOOP
859 -- Initialise assignment variables
860 --
861 l_assignment_id := each_assignment.assignment_id;
862 l_assignment_average := 0;
863 --
864 derive_relevant_period;
865 --
866 l_new_employee := FALSE;
867 --
868 -- If the employee joined within the relevant period then we must note that
869 -- fact for later use.
870 --
871 if l_hire_date >= l_start_of_relevant_period
872 then
873 l_new_employee := TRUE;
874 --
875 hr_utility.trace ('Employee is NEW');
876 end if;
877 --
878 -- The calculation of average earnings is done differently depending upon the
879 -- payroll frequency. For new employees, we always treat them as if they were
880 -- on irregular payroll frequencies so that we can pick up any payments they
881 -- may have received.
882 --
883 get_payroll_frequency;
884 --
885 if l_payroll_frequency = 'MONTHLY' and not l_new_employee
886 then
887 do_monthly_calculation;
888 else -- any other payroll frequency or new employee
889 do_standard_calculation;
890 end if;
891 --
892 -- Increment the person's average earnings by the average earnings for the
893 -- assignment just calculated.
894 --
895 l_person_average := l_person_average + l_assignment_average;
896 end loop;
897 --
898 if l_person_average = 0 and l_new_employee
899 then
900 --
901 -- If by the end of the calculation a new employee has zero average earnings
902 -- it means he received no pay in the period. Therefore, we cannot calculate
903 -- an average so it is determined by contracted pay. Since we cannot derive
904 -- that, set a warning message telling the user why the earnings figure is
905 -- zero: "Oracle Payroll cannot derive the average earnings for new employees
906 -- who have not yet received any pay on which to base a calculation. Please
907 -- enter the average earnings figure yourself, based upon the employee's
908 -- contracted weekly earnings."
909 --
910 -- (abhaduri) 'IF' condition added to check for employees
911 -- re-hired within 8 weeks of previous termination
912 open csr_noof_periods_service;
913 fetch csr_noof_periods_service into l_noof_periods_service;
914 close csr_noof_periods_service;
915 if l_noof_periods_service >1 then
916 -- the employee has been re-hired
917 -- check if the hiring has been within 8 weeks
918 open csr_earlier_term;
919 fetch csr_earlier_term into l_earlier_term_date;
920 close csr_earlier_term;
921 if l_hire_date - l_earlier_term_date <56 then
922 ssp_smp_support_pkg.reason_for_no_earnings:='SSP_36076_EMP_REHIRED';
923 else
924 ssp_smp_support_pkg.reason_for_no_earnings:='SSP_35026_NO_NEW_EMP_EARNINGS';
925 end if;
926 else
927 -- otherwise continue as earlier for a new employee
928 ssp_smp_support_pkg.reason_for_no_earnings:= 'SSP_35026_NO_NEW_EMP_EARNINGS';
929 end if;
930 raise cannot_derive_earnings;
931 end if;
932 --
933 p_average_earnings_amount := nvl (round (l_person_average,2),0);
934 hr_utility.trace ('average earnings is '||to_char(l_person_average));
935 --
936 hr_utility.set_location('Leaving :'||l_proc, 100);
937 --
938 exception
939 when cannot_derive_earnings then
940 hr_utility.set_location ('Leaving :'||l_proc||', exception',999);
941 --
942 p_average_earnings_amount := 0;
943 --
944 fnd_message.set_name ('SSP',ssp_smp_support_pkg.reason_for_no_earnings);
945 --
946 if p_user_entered = 'Y' then
947 --
948 -- We only fail the procedure if the user is entering the amount.
949 -- If the system is calculating it (eg as part of the SSP/SMP process)
950 -- then we must allow the process to continue and handle the error
951 --
952 fnd_message.raise_error;
953 end if;
954 --
955 end calculate_average_earnings;
956
957 -- ----------------------------------------------------------------------------
958 -- |---------------------------< number_of_periods >---------------------------|
959 -- ----------------------------------------------------------------------------
960 -- This function is used to pass the number of payment periods to the entries
961 -- form, SSPWSENT.
962 --
963 function number_of_periods return number is
964 --
965 l_proc varchar2(72) := g_package||'number_of_periods';
966 begin
967 hr_utility.set_location('Entering:'||l_proc, 1);
968 --
969 return form_variable;
970 hr_utility.set_location('Leaving:'||l_proc, 100);
971 end;
972 --
973 -- ----------------------------------------------------------------------------
974 -- |---------------------------< insert_validate >----------------------------|
975 -- ----------------------------------------------------------------------------
976 Procedure insert_validate(p_rec in out nocopy ssp_ern_shd.g_rec_type) is
977 --
978 l_proc varchar2(72) := g_package||'insert_validate';
979 --
980 Begin
981 hr_utility.set_location('Entering:'||l_proc, 1);
982 --
983 -- Call all supporting business operations
984 --
985 -- Following two calls are to ensure that the mandatory columns
986 -- person_id and effective_date have been entered.
987 hr_api.mandatory_arg_error (p_api_name => l_proc,
988 p_argument => 'person_id',
989 p_argument_value => p_rec.person_id);
990
991 hr_api.mandatory_arg_error (p_api_name => l_proc,
992 p_argument => 'effective_date',
993 p_argument_value => p_rec.effective_date);
994 --
995 ssp_ern_bus.check_person_id(p_rec.person_id, p_rec.effective_date);
996 --
997 ssp_ern_bus.check_effective_date (p_rec.person_id, p_rec.effective_date);
998 --
999 if p_rec.average_earnings_amount is null
1000 or p_rec.average_earnings_amount = hr_api.g_number
1001 then
1002 p_rec.user_entered := 'N';
1003 ssp_ern_bus.calculate_average_earnings
1004 (p_rec.person_id,
1005 p_rec.effective_date,
1006 p_rec.average_earnings_amount,
1007 p_rec.user_entered,
1008 p_rec.absence_category --DFoster 1305683
1009 );
1010 end if;
1011 --
1012 hr_utility.set_location('Leaving :'||l_proc, 100);
1013 End insert_validate;
1014 --
1015 -- ----------------------------------------------------------------------------
1016 -- |---------------------------< update_validate >----------------------------|
1017 -- ----------------------------------------------------------------------------
1018 Procedure update_validate(p_rec in out nocopy ssp_ern_shd.g_rec_type) is
1019 --
1020 l_proc varchar2(72) := g_package||'update_validate';
1021 --
1022 Begin
1023 hr_utility.set_location('Entering:'||l_proc, 1);
1024 --
1025 -- Call all supporting business operations
1026 --
1027 -- Following two bits of code used to ensure that the argument values
1028 -- have not been updated.
1029 --
1030 if (ssp_ern_shd.api_updating
1031 (p_earnings_calculations_id => p_rec.earnings_calculations_id,
1032 p_object_version_number => p_rec.object_version_number)
1033 and
1034 p_rec.person_id <> ssp_ern_shd.g_old_rec.person_id)
1035 then
1036 hr_api.argument_changed_error
1037 (p_api_name => l_proc, p_argument => 'Person_id');
1038 end if;
1039
1040 if (ssp_ern_shd.api_updating
1041 (p_earnings_calculations_id => p_rec.earnings_calculations_id,
1042 p_object_version_number => p_rec.object_version_number)
1043 and
1044 p_rec.effective_date <> ssp_ern_shd.g_old_rec.effective_date)
1045 then
1046 hr_api.argument_changed_error
1047 (p_api_name => l_proc, p_argument => 'effective_date');
1048 end if;
1049
1050 if p_rec.average_earnings_amount is null
1051 or p_rec.average_earnings_amount = hr_api.g_number
1052 then
1053 p_rec.user_entered := 'N';
1054 ssp_ern_bus.calculate_average_earnings
1055 (ssp_ern_shd.g_old_rec.person_id,
1056 ssp_ern_shd.g_old_rec.effective_date,
1057 p_rec.average_earnings_amount,
1058 p_rec.user_entered,
1059 p_rec.absence_category --DFoster 1304683
1060 );
1061 end if;
1062 --
1063 hr_utility.set_location('Leaving :'||l_proc, 100);
1064 End update_validate;
1065 --
1066 -- ----------------------------------------------------------------------------
1067 -- |---------------------------< delete_validate >----------------------------|
1068 -- ----------------------------------------------------------------------------
1069 Procedure delete_validate(p_rec in ssp_ern_shd.g_rec_type) is
1070 --
1071 l_proc varchar2(72) := g_package||'delete_validate';
1072 --
1073 Begin
1074 hr_utility.set_location('Entering:'||l_proc, 1);
1075 --
1076 -- Call all supporting business operations - there are none
1077 --
1078 hr_utility.set_location('Leaving :'||l_proc, 100);
1079 End delete_validate;
1080 --
1081 end ssp_ern_bus;
[Home] [Help]