1 Package Body ssp_ern_bus as
2 /* $Header: spernrhi.pkb 120.8 2010/07/07 13:26:55 pbalu 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 /* Bug 9411096
340 Added cursor to identify TERM_ASSIGN, and handle the relavant dates accordingly. */
341 cursor get_asg_status is
342 select past.per_system_status
343 from per_all_assignments_f paaf, per_assignment_status_types past
344 where paaf.assignment_id = L_ASSIGNMENT_ID
345 and paaf.effective_start_date <= P_EFFECTIVE_DATE
346 and paaf.assignment_status_type_id = past.assignment_status_type_id
347 order by paaf.effective_start_date desc;
348
349 v_asg_status varchar2(100); -- bug 9411096
350 --
351 begin
352 --
353 hr_utility.set_location('Entering:'||l_proc,1);
354 --
355 l_payroll_id := null; -- bug 9411096
356 v_asg_status := null;
357
358 open csr_get_payroll_id_end;
359 fetch csr_get_payroll_id_end into l_payroll_id;
360 close csr_get_payroll_id_end;
361
362 --
363 -- Bug 1304683 DFoster
364 -- Get the end date of the last pay period where the regular payment
365 -- date is just before the effective date depending on whether a
366 -- Sickness or a Maternity.
367 --
368 --6791913 Begin
369 --if p_absence_category = 'M' then
370 if p_absence_category in ('M','GB_ADO') then
371 --6791913 End
372 open csr_end_period_m;
373 fetch csr_end_period_m into l_end_of_relevant_period;
374 close csr_end_period_m;
375
376 if l_end_of_relevant_period is null then
377 hr_utility.trace (l_proc||' finding the end of relevant period using the assignment id and not using the payroll id');
378 open csr_end_period_m2;
379 fetch csr_end_period_m2 into l_end_of_relevant_period;
380 close csr_end_period_m2;
381 end if;
382
383 else --if p_absence_category = 'S' then
384 open csr_end_period_s;
385 fetch csr_end_period_s into l_end_of_relevant_period;
386 close csr_end_period_s;
387
388 if l_end_of_relevant_period is null then
389 hr_utility.trace (l_proc||' finding the end of relevant period using the assignment id and not using the payroll id');
390 open csr_end_period_s2;
391 fetch csr_end_period_s2 into l_end_of_relevant_period;
392 close csr_end_period_s2;
393 end if;
394
395 end if;
396 --
397 -- the above csrs can return null when a new employee is hired
398 -- assigned to new payroll and goes on sick leave!
399
400 l_start_of_relevant_period := l_end_of_relevant_period - 56;
401
402 /* Bug 9411096 start */
403 open get_asg_status;
404 fetch get_asg_status into v_asg_status;
405 hr_utility.trace('v_asg_status IS: '||v_asg_status);
406 close get_asg_status;
407 /* Bug 9411096 end */
408 --
409 --
410 -- the payrll id is reset to null as if the csr_get_payroll_id_start
411 -- returns no rows, the old value of payroll id is retained
412 -- and this causes error as l_start_of_relevant_period is not
413 -- set to l_hire_date;
414 -- the above csr can return null when a new employee is hired
415 -- assigned to new payroll and foes on sick leave!
416
417 -- l_payroll_id := null; bug 9411096
418
419 open csr_get_payroll_id_start;
420 fetch csr_get_payroll_id_start into l_payroll_id;
421 /* Bug 9411096 start */
422 if csr_get_payroll_id_start%notfound then
423 l_payroll_id := null;
424 end if;
425 /* Bug 9411096 end */
426 close csr_get_payroll_id_start;
427 --
428 -- if l_payroll_id is null then bug 9411096
429 if l_payroll_id is null and (nvl(v_asg_status,'x') = 'ACTIVE_ASSIGN') then
430 if l_end_of_relevant_period is null then
431 -- -1 because, the end date is the previous months payroll
432 -- end date
433 l_end_of_relevant_period := l_hire_date - 1 ;
434 l_start_of_relevant_period := l_end_of_relevant_period - 56;
435 else
436 l_start_of_relevant_period := l_hire_date;
437 -- if the assignment doesn't exits at the hire date (ie, multiple asg)
438 -- then get the assignment start date
439 open csr_chk_asg;
440 fetch csr_chk_asg into l_temp;
441 if csr_chk_asg%notfound then
442 open csr_get_start_date;
443 fetch csr_get_start_date into l_start_of_relevant_period;
444 close csr_get_start_date;
445 end if;
446 close csr_chk_asg;
447 end if;
448 else
449 open csr_start_period;
450 fetch csr_start_period into l_start_of_relevant_period;
451 close csr_start_period;
452 end if;
453 --
454 hr_utility.trace (l_proc||' start of relevant period = '
455 ||to_char (l_start_of_relevant_period));
456 hr_utility.trace (l_proc||' end of relevant period = '
457 ||to_char (l_end_of_relevant_period));
458 --
459 hr_utility.set_location('Leaving :'||l_proc,100);
460 --
461 end derive_relevant_period;
462 --
463 procedure get_payroll_frequency is
464 --
465 -- Find out what payroll frequency the assignment is using
466 --
467 l_proc varchar2 (72) := g_package||'get_payroll_frequency';
468 --
469 cursor csr_payroll_frequency is
470 --
471 -- Get the payroll frequency for an assignment
472 --
473 -- This now returns all the payrolls that the person is on between the
474 -- start and end of the 'relevant period', rather than just the payroll
475 -- that the person is on as of the p_effective_date which is the
476 -- PIW_start_date(SSP) or QW_start_date(SMP).
477 --
478 select period_type.number_per_fiscal_year fiscal_year
479 from pay_all_payrolls_f PAYROLL,
480 per_all_assignments_f ASSIGNMENT,
481 per_time_period_types PERIOD_TYPE
482 where assignment.assignment_id = l_assignment_id
483 and assignment.effective_start_date <= l_end_of_relevant_period
484 and assignment.effective_end_date >= l_start_of_relevant_period
485 and payroll.payroll_id = assignment.payroll_id
486 and payroll.period_type = period_type.period_type
487 and payroll.effective_start_date <= l_end_of_relevant_period
488 and payroll.effective_end_date >= l_start_of_relevant_period;
489 --
490 periods_per_fiscal_year number := 0;
491 --
492 begin
493 --
494 hr_utility.set_location('Entering:'||l_proc,1);
495 --
496 l_payroll_frequency := 'MONTHLY';
497 --
498 for each_payroll in csr_payroll_frequency loop
499 if each_payroll.fiscal_year <> 12 then
500 l_payroll_frequency := 'NOT MONTHLY';
501 end if;
502 end loop;
503 --
504 hr_utility.trace ('l_payroll_frequency = '||l_payroll_frequency);
505 --
506 hr_utility.set_location('Leaving :'||l_proc,100);
507 --
508 end get_payroll_frequency;
509 --
510 procedure get_period_of_service is
511 --
512 -- Get the current period of service for the person
513 --
514 l_proc varchar2 (72) := g_package||'get_period_of_service';
515 --
516 cursor csr_period_of_service is
517 --
518 -- Get the period of service current as of a specified date
519 --
520 select service.period_of_service_id,
521 service.date_start
522 from per_periods_of_service SERVICE
523 where person_id = p_person_id
524 and p_effective_date between service.date_start
525 and nvl (service.actual_termination_date,
526 hr_general.end_of_time);
527 --
528 begin
529 --
530 hr_utility.set_location('Entering:'||l_proc,1);
531 --
532 -- Get the period of service current as of the effective date
533 --
534 open csr_period_of_service;
535 fetch csr_period_of_service into l_period_of_service_id, l_hire_date;
536 close csr_period_of_service;
537 --
538 hr_utility.trace (l_proc||' l_period_of_service_id = '
539 ||to_char (l_period_of_service_id));
540 hr_utility.trace (l_proc||' l_hire_date = '
541 ||to_char (l_hire_date));
542 --
543 hr_utility.set_location('Leaving :'||l_proc,100);
544 --
545 end get_period_of_service;
546 --
547 procedure check_payroll_installed is
548 --
549 -- Checks that Payroll is installed before calculation of earnings is
550 -- attempted.
551 -- This code was copied and modified from hrapiapi.pkb
552 --
553 l_proc varchar2 (72) := g_package||'check_payroll_installed';
554 l_pa_installed fnd_product_installations.status%TYPE;
555 l_industry fnd_product_installations.industry%TYPE;
556 l_pa_appid fnd_product_installations.application_id%TYPE := 801;
557 payroll_not_found exception;
558 --
559 Begin
560 --
561 hr_utility.set_location('Entering:'||l_proc,1);
562 --
563 -- We need to determine if Payroll is installed.
564 if (fnd_installation.get(
565 --
566 appl_id => l_pa_appid,
567 dep_appl_id => l_pa_appid,
568 status => l_pa_installed,
569 industry => l_industry))
570 then
571 --
572 -- Check to see if the status = 'I'
573 --
574 If (l_pa_installed = 'I') then
575 return; -- Payroll is installed
576 else
577 raise payroll_not_found;
578 end If;
579 --
580 else
581 raise payroll_not_found;
582 end If;
583 --
584 hr_utility.set_location('Leaving :'||l_proc,100);
585 --
586 exception
587 when payroll_not_found then
588 --
589 -- Set warning message:
590 -- "Average Earnings cannot be calculated automatically unless
591 -- you have installed Oracle Payroll. You must enter the figure
592 -- yourself."
593 --
594 ssp_smp_support_pkg.reason_for_no_earnings
595 := 'SSP_35024_NEED_PAYROLL_FOR_ERN';
596 raise cannot_derive_earnings;
597 --
598 end check_payroll_installed;
599 --
600 procedure stop_if_a_director is
601 --
602 cursor csr_director is
603 select 1
604 from per_all_people_f
605 where per_information2 = 'Y' -- Director_flag
606 and person_id = p_person_id
607 and p_effective_date between effective_start_date
608 and effective_end_date;
609 --
610 l_proc varchar2 (72) := g_package||'stop_if_a_director';
611 l_dummy integer (1) := null;
612 l_person_is_director boolean := FALSE;
613 --
614 begin
615 --
616 hr_utility.set_location('Entering:'||l_proc,1);
617 --
618 open csr_director;
619 fetch csr_director into l_dummy;
620 l_person_is_director := csr_director%found;
621 close csr_director;
622 --
623 if l_person_is_director then
624 --
625 -- Set the warning message text to:
626 -- "Oracle Payroll is unable to calculate the earnings of directors
627 -- because it has no way to distinguish between voted fees and fees
628 -- drawn in anticipation of voting. Please enter the average earnings
629 -- figure for directors yourself."
630 --
631 ssp_smp_support_pkg.reason_for_no_earnings
632 := 'SSP_35025_NO_DIRECTOR_EARNINGS';
633 raise cannot_derive_earnings;
634 --
635 end if;
636 --
637 hr_utility.set_location('Leaving :'||l_proc,100);
638 --
639 end stop_if_a_director;
640 --
641 function gross_NIable_pay
642 --
643 -- Calculate the gross NIable pay for an 8 week period
644 --
645 return number is
646 --
647 l_proc varchar2 (72) := g_package||'gross_NIable_pay';
648 l_lel number := 0;
649 l_weekly_pay number(18,8) := 0;
650 l_gross_NIable_pay number := 0;
651 l_gross_NIable_pay_acc number(18,8) := 0;
652 --
653 begin
654 --
655 hr_utility.set_location('Entering:'||l_proc,1);
656 --
657 for csr_Ne in csr_NIable_earnings ('NIable Pay')
658 loop
659 l_gross_NIable_pay_acc := csr_Ne.EARNINGS;
660 l_gross_NIable_pay := l_gross_NIable_pay_acc;
661 end loop;
662 --
663 hr_utility.trace('L_GROSS_NIABLE: '||l_gross_NIable_pay);
664 --
665 l_weekly_pay := l_gross_NIable_pay_acc * 6 / 52;
666 l_lel := SSP_SMP_SUPPORT_PKG.NI_Lower_Earnings_Limit(
667 L_END_OF_RELEVANT_PERIOD);
668 hr_utility.trace('l_lel: '||l_lel);
669 --
670 if l_weekly_pay < l_lel
671 then
672 for csr_Ne in csr_NIable_earnings ('NIable Earnings 1B')
673 loop
674 l_gross_NIable_pay_acc := l_gross_NIable_pay_acc+csr_Ne.EARNINGS;
675 l_gross_NIable_pay := l_gross_NIable_pay_acc;
676 --
677 hr_utility.trace('L_GROSS_NIABLE inc 1B: '||l_gross_NIable_pay);
678 end loop;
679 end if;
680 --
681 hr_utility.set_location('Leaving :'||l_proc,100);
682 --
683 return l_gross_NIable_pay;
684 --
685 end gross_NIable_pay;
686 --
687 procedure do_monthly_calculation is
688 --
689 -- Calculate average earnings for an assignment on a monthly payroll.
690 -- We handle calendar monthly payrolls separately because they have
691 -- unequal numbers of days and so using the normal calculation method
692 -- would give different results depending upon which months were being
693 -- studied.
694 --
695 l_proc varchar2 (72) := g_package||'do_monthly_calculation';
696 --
697 begin
698 --
699 hr_utility.set_location('Entering:'||l_proc,1);
700 --
701 -- Take the gross payments from the last 2 months, multiply by 6 for
702 -- the annual figure and divide by 52 for the weekly average
703 --
704 l_assignment_average :=
705 l_assignment_average + ((gross_NIable_pay * 6) / 52);
706 --
707 hr_utility.trace (l_proc||' gross_NIable_pay = '
708 ||to_char (gross_NIable_pay));
709 --
710 hr_utility.set_location('Leaving :'||l_proc,100);
711 --
712 end do_monthly_calculation;
713 --
714 procedure do_standard_calculation is
715 --
716 -- Calculate average weekly earnings for an assignment with any payroll
717 -- frequency other than monthly, ie those consisting of equal numbers
718 -- of days.
719 --
720 l_proc varchar2 (72) := g_package||'do_standard_calculation';
721 l_days_covered number := (l_end_of_relevant_period
722 - greatest (l_start_of_relevant_period,
723 l_hire_date))
724 +1;
725 --
726 -- Csr_get_number_of_reg_payments is used in the new method of
727 -- calculating weekly average earnings. Users will have to enter a
728 -- value for a new element 'Average Earnings Period' to state the
729 -- number of regular payment periods being processed in the one payroll
730 -- process. If a value is returned then an irregular number of payments
731 -- have been processed within the relevant period. The average amount
732 -- is calculated as the total gross NIable pay / the number of periods
733 -- paid. The value entered in this element is used to calculate the
734 -- actual number of payment periods.
735 --
736 cursor csr_get_number_of_reg_payments is
737 select peev.screen_entry_value
738 from per_all_assignments_f paf
739 , pay_element_entry_values_f peev
740 , pay_element_entries_f pee
741 , pay_element_types_f pet
742 , pay_element_links_f pel
743 where pee.element_entry_id = peev.element_entry_id
744 and pee.assignment_id = paf.assignment_id
745 and pet.element_type_id = pel.element_type_id
746 and pel.element_link_id = pee.element_link_id
747 and paf.assignment_id = l_assignment_id
748 and pet.element_name = 'Average Earnings Period'
749 and peev.effective_start_date between paf.effective_start_date
750 and paf.effective_end_date
751 and peev.effective_start_date between pee.effective_start_date
752 and pee.effective_end_date
753 and peev.effective_start_date between pet.effective_start_date
754 and pet.effective_end_date
755 and peev.effective_start_date between pel.effective_start_date
756 and pel.effective_end_date
757 and peev.effective_start_date
758 between greatest(l_start_of_relevant_period, l_hire_date)
759 and l_end_of_relevant_period
760 and peev.effective_end_date
761 between greatest(l_start_of_relevant_period, l_hire_date)
762 and l_end_of_relevant_period;
763 --
764 -- csr_number_of_days returns the payroll frequency that an assignment
765 -- is on during the relevant period. If a person changes payroll within
766 -- the relevant period, then they are considered to be on an irregular
767 -- payroll and as such are calculated using do_standard_calculation,
768 -- even if the person was on a Monthly payroll as some stage during the
769 -- relevant period. Thus, if do_standard_calculation is being executed
770 -- it is not for a Monthly payroll and so we never want csr_number_of
771 -- _days to return a fiscal_year value of 12 (i.e. Monthly).
772 --
773 cursor csr_number_of_days is
774 select period_type.number_per_fiscal_year fiscal_year
775 from pay_all_payrolls_f PAYROLL,
776 per_all_assignments_f ASSIGNMENT,
777 per_time_period_types PERIOD_TYPE
778 where assignment.assignment_id = l_assignment_id
779 and payroll.payroll_id = assignment.payroll_id
780 and payroll.period_type = period_type.period_type
781 and payroll.effective_start_date <= l_end_of_relevant_period
782 and payroll.effective_end_date >= l_start_of_relevant_period
783 and period_type.number_per_fiscal_year <> 12;
784 --
785 number_of_payments number;
786 total_number_payments number;
787 payroll_freq number;
788 number_of_days number;
789 user_ent_multi_reg_pays boolean;
790 expected_num_of_periods number := null;
791 --
792 begin
793 --
794 hr_utility.set_location('Entering:'||l_proc,1);
795 hr_utility.trace('days covered orig: '||l_days_covered);
796 --
797 -- Take the gross payments from the relevant period, divide by the
798 -- number of days the payments cover, and multiply by 7.
799 --
800 number_of_payments := 0;
801 total_number_payments := 0;
802 --
803 for payments in csr_get_number_of_reg_payments loop
804 exit when csr_get_number_of_reg_payments%NOTFOUND
805 or csr_get_number_of_reg_payments%NOTFOUND is null;
806 if payments.screen_entry_value < 1 then
807 number_of_payments := -1;
808 elsif payments.screen_entry_value >= 1 then
809 number_of_payments := payments.screen_entry_value -1;
810 end if;
811
812 total_number_payments := total_number_payments + number_of_payments;
813 user_ent_multi_reg_pays := TRUE;
814 hr_utility.trace('NUMBER OF PAYMENTS: '||number_of_payments);
815 end loop;
816
817 hr_utility.trace('TOTAL NUM PAYMENTS: '||total_number_payments);
818 --
819 if user_ent_multi_reg_pays then
820 --
821 payroll_freq := 0;
822 number_of_days := 0;
823 --
824 open csr_number_of_days;
825 fetch csr_number_of_days into payroll_freq;
826 close csr_number_of_days;
827 --
828 number_of_days := round(365/payroll_freq);
829 --
830 hr_utility.trace('PAYROLL FREQ: '||payroll_freq);
831 hr_utility.trace('NUMBER OF DAYS: '||number_of_days);
832 --
833 -- Next bit of code is for outputting the number of payments to the ssp entries
834 -- form. The value in form_variable is passed to the form via the function
835 -- number_of_periods. If the total_number_payments is 0 then then number of
836 -- payments in the relevant period is the regular number, so the value is not
837 -- output to the form. The user only wants to see a value on the form if it is
838 -- an irregular number of payment periods.
839 --
840 expected_num_of_periods := l_days_covered/number_of_days;
841 --
842 if total_number_payments = 0 then
843 form_variable := null;
844 else
845 form_variable := expected_num_of_periods + total_number_payments;
846 end if;
847 --
848 hr_utility.trace('expected num of periods '||expected_num_of_periods||
849 ', form variable '||form_variable);
850 --
851 l_days_covered := l_days_covered +
852 (number_of_days * total_number_payments);
853 --9819351 begin
854 else
855
856 if l_hire_date < l_end_of_relevant_period - 56 then
857 --if the person is hired before the 56 days period then
858 --the average should be calculated for the whole 56 days.
859 hr_utility.trace('DAYS COVERED is eight weeks');
860 l_days_covered := 56 ;
861 else
862 --if the person is hired within the 56 days period then
863 --the average should be calculated from hire date to end of relevant period,
864 hr_utility.trace('DAYS COVERED will be less than eight weeks');
865 l_days_covered := l_end_of_relevant_period - l_hire_date;
866 end if;
867 --9819351 end
868 end if;
869 hr_utility.trace('DAYS COVERED: '||l_days_covered);
870 --
871 if l_days_covered < 1 then
872 l_assignment_average := 0;
873 else
874 l_assignment_average :=
875 l_assignment_average + ((gross_NIable_pay
876 -----------------
877 / l_days_covered)
878 * 7);
879 end if;
880 --
881 hr_utility.trace('GROSS NIABLE PAY: '||gross_NIable_pay);
882 hr_utility.trace ('l_days_covered = '||to_char (l_days_covered));
883 hr_utility.trace ('l_assignment_average = '
884 ||to_char (l_assignment_average));
885 --
886 hr_utility.set_location('Leaving :'||l_proc,100);
887 --
888 end do_standard_calculation;
889 --
890 begin
891 --
892 hr_utility.set_location('Entering:'||l_proc, 1);
893 hr_utility.trace('P_EFFECTIVE_DATE IS: '||p_effective_date);
894 --
895 check_payroll_installed;
896 stop_if_a_director;
897 get_period_of_service;
898 --
899 FOR each_assignment in csr_set_of_current_assignments
900 LOOP
901 -- Initialise assignment variables
902 --
903 l_assignment_id := each_assignment.assignment_id;
904 l_assignment_average := 0;
905 --
906 derive_relevant_period;
907 --
908 l_new_employee := FALSE;
909 --
910 -- If the employee joined within the relevant period then we must note that
911 -- fact for later use.
912 --
913 if l_hire_date >= l_start_of_relevant_period
914 then
915 l_new_employee := TRUE;
916 --
917 hr_utility.trace ('Employee is NEW');
918 end if;
919 --
920 -- The calculation of average earnings is done differently depending upon the
921 -- payroll frequency. For new employees, we always treat them as if they were
922 -- on irregular payroll frequencies so that we can pick up any payments they
923 -- may have received.
924 --
925 get_payroll_frequency;
926 --
927 if l_payroll_frequency = 'MONTHLY' and not l_new_employee
928 then
929 do_monthly_calculation;
930 else -- any other payroll frequency or new employee
931 do_standard_calculation;
932 end if;
933 --
934 -- Increment the person's average earnings by the average earnings for the
935 -- assignment just calculated.
936 --
937 l_person_average := l_person_average + l_assignment_average;
938 end loop;
939 --
940 if l_person_average = 0 and l_new_employee
941 then
942 --
943 -- If by the end of the calculation a new employee has zero average earnings
944 -- it means he received no pay in the period. Therefore, we cannot calculate
945 -- an average so it is determined by contracted pay. Since we cannot derive
946 -- that, set a warning message telling the user why the earnings figure is
947 -- zero: "Oracle Payroll cannot derive the average earnings for new employees
948 -- who have not yet received any pay on which to base a calculation. Please
949 -- enter the average earnings figure yourself, based upon the employee's
950 -- contracted weekly earnings."
951 --
952 -- (abhaduri) 'IF' condition added to check for employees
953 -- re-hired within 8 weeks of previous termination
954 open csr_noof_periods_service;
955 fetch csr_noof_periods_service into l_noof_periods_service;
956 close csr_noof_periods_service;
957 if l_noof_periods_service >1 then
958 -- the employee has been re-hired
959 -- check if the hiring has been within 8 weeks
960 open csr_earlier_term;
961 fetch csr_earlier_term into l_earlier_term_date;
962 close csr_earlier_term;
963 if l_hire_date - l_earlier_term_date <56 then
964 ssp_smp_support_pkg.reason_for_no_earnings:='SSP_36076_EMP_REHIRED';
965 else
966 ssp_smp_support_pkg.reason_for_no_earnings:='SSP_35026_NO_NEW_EMP_EARNINGS';
967 end if;
968 else
969 -- otherwise continue as earlier for a new employee
970 ssp_smp_support_pkg.reason_for_no_earnings:= 'SSP_35026_NO_NEW_EMP_EARNINGS';
971 end if;
972 raise cannot_derive_earnings;
973 end if;
974 --
975 p_average_earnings_amount := nvl (round (l_person_average,2),0);
976 hr_utility.trace ('average earnings is '||to_char(l_person_average));
977 --
978 hr_utility.set_location('Leaving :'||l_proc, 100);
979 --
980 exception
981 when cannot_derive_earnings then
982 hr_utility.set_location ('Leaving :'||l_proc||', exception',999);
983 --
984 p_average_earnings_amount := 0;
985 --
986 fnd_message.set_name ('SSP',ssp_smp_support_pkg.reason_for_no_earnings);
987 --
988 if p_user_entered = 'Y' then
989 --
990 -- We only fail the procedure if the user is entering the amount.
991 -- If the system is calculating it (eg as part of the SSP/SMP process)
992 -- then we must allow the process to continue and handle the error
993 --
994 fnd_message.raise_error;
995 end if;
996 --
997 end calculate_average_earnings;
998
999 -- ----------------------------------------------------------------------------
1000 -- |---------------------------< number_of_periods >---------------------------|
1001 -- ----------------------------------------------------------------------------
1002 -- This function is used to pass the number of payment periods to the entries
1003 -- form, SSPWSENT.
1004 --
1005 function number_of_periods return number is
1006 --
1007 l_proc varchar2(72) := g_package||'number_of_periods';
1008 begin
1009 hr_utility.set_location('Entering:'||l_proc, 1);
1010 --
1011 return form_variable;
1012 hr_utility.set_location('Leaving:'||l_proc, 100);
1013 end;
1014 --
1015 -- ----------------------------------------------------------------------------
1016 -- |---------------------------< insert_validate >----------------------------|
1017 -- ----------------------------------------------------------------------------
1018 Procedure insert_validate(p_rec in out nocopy ssp_ern_shd.g_rec_type) is
1019 --
1020 l_proc varchar2(72) := g_package||'insert_validate';
1021 --
1022 Begin
1023 hr_utility.set_location('Entering:'||l_proc, 1);
1024 --
1025 -- Call all supporting business operations
1026 --
1027 -- Following two calls are to ensure that the mandatory columns
1028 -- person_id and effective_date have been entered.
1029 hr_api.mandatory_arg_error (p_api_name => l_proc,
1030 p_argument => 'person_id',
1031 p_argument_value => p_rec.person_id);
1032
1033 hr_api.mandatory_arg_error (p_api_name => l_proc,
1034 p_argument => 'effective_date',
1035 p_argument_value => p_rec.effective_date);
1036 --
1037 ssp_ern_bus.check_person_id(p_rec.person_id, p_rec.effective_date);
1038 --
1039 ssp_ern_bus.check_effective_date (p_rec.person_id, p_rec.effective_date);
1040 --
1041 if p_rec.average_earnings_amount is null
1042 or p_rec.average_earnings_amount = hr_api.g_number
1043 then
1044 p_rec.user_entered := 'N';
1045 ssp_ern_bus.calculate_average_earnings
1046 (p_rec.person_id,
1047 p_rec.effective_date,
1048 p_rec.average_earnings_amount,
1049 p_rec.user_entered,
1050 p_rec.absence_category --DFoster 1305683
1051 );
1052 end if;
1053 --
1054 hr_utility.set_location('Leaving :'||l_proc, 100);
1055 End insert_validate;
1056 --
1057 -- ----------------------------------------------------------------------------
1058 -- |---------------------------< update_validate >----------------------------|
1059 -- ----------------------------------------------------------------------------
1060 Procedure update_validate(p_rec in out nocopy ssp_ern_shd.g_rec_type) is
1061 --
1062 l_proc varchar2(72) := g_package||'update_validate';
1063 --
1064 Begin
1065 hr_utility.set_location('Entering:'||l_proc, 1);
1066 --
1067 -- Call all supporting business operations
1068 --
1069 -- Following two bits of code used to ensure that the argument values
1070 -- have not been updated.
1071 --
1072 if (ssp_ern_shd.api_updating
1073 (p_earnings_calculations_id => p_rec.earnings_calculations_id,
1074 p_object_version_number => p_rec.object_version_number)
1075 and
1076 p_rec.person_id <> ssp_ern_shd.g_old_rec.person_id)
1077 then
1078 hr_api.argument_changed_error
1079 (p_api_name => l_proc, p_argument => 'Person_id');
1080 end if;
1081
1082 if (ssp_ern_shd.api_updating
1083 (p_earnings_calculations_id => p_rec.earnings_calculations_id,
1084 p_object_version_number => p_rec.object_version_number)
1085 and
1086 p_rec.effective_date <> ssp_ern_shd.g_old_rec.effective_date)
1087 then
1088 hr_api.argument_changed_error
1089 (p_api_name => l_proc, p_argument => 'effective_date');
1090 end if;
1091
1092 if p_rec.average_earnings_amount is null
1093 or p_rec.average_earnings_amount = hr_api.g_number
1094 then
1095 p_rec.user_entered := 'N';
1096 ssp_ern_bus.calculate_average_earnings
1097 (ssp_ern_shd.g_old_rec.person_id,
1098 ssp_ern_shd.g_old_rec.effective_date,
1099 p_rec.average_earnings_amount,
1100 p_rec.user_entered,
1101 p_rec.absence_category --DFoster 1304683
1102 );
1103 end if;
1104 --
1105 hr_utility.set_location('Leaving :'||l_proc, 100);
1106 End update_validate;
1107 --
1108 -- ----------------------------------------------------------------------------
1109 -- |---------------------------< delete_validate >----------------------------|
1110 -- ----------------------------------------------------------------------------
1111 Procedure delete_validate(p_rec in ssp_ern_shd.g_rec_type) is
1112 --
1113 l_proc varchar2(72) := g_package||'delete_validate';
1114 --
1115 Begin
1116 hr_utility.set_location('Entering:'||l_proc, 1);
1117 --
1118 -- Call all supporting business operations - there are none
1119 --
1120 hr_utility.set_location('Leaving :'||l_proc, 100);
1121 End delete_validate;
1122 --
1123 end ssp_ern_bus;
[Home] [Help]