1 package body SSP_SAP_PKG as
2 /*$Header: spsapapi.pkb 120.1.12000000.3 2007/08/02 06:44:58 pbalu noship $
3 +==============================================================================+
4 | Copyright (c) 1994 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +==============================================================================+
8 --
9 Name
10 Statutory Paternity Pay Business Process
11 --
12 Purpose
13 To perform calculation of entitlement and payment for SAP purposes
14 --
15 History
16 History
17 03 Oct 02 V Khandelwal 2690305 Created from SSP_SMP_PKG
18 27 Jan 03 G Butler nocopy fix to average_earnings -
19 added 2nd dummy variable to act as
20 placeholder variable for OUT param
21 from ssp_ern_ins
22 01 Feb 03 A Blinko 2774865 Added code for disrupted placement
23 03 Feb 03 A Blinko disrupted placement stop only created
24 if before end of APP Period
25 06 Feb 03 A Blinko 2779883 Fixed Employee died stoppage
26 24 Oct 03 A Blinko 3208325 Removed hardcoded SATURDAY and
27 SUNDAY references
28 13 Jul 04 A Blinko 3682122 Changes for recalculating lump sum
29 updates
30 09 Feb 06 K Thampan 4891953 Fixed performance bug.
31 23-AUG-06 K Thampan 5482199 Statutory changes for 2007
32 19-SEP-06 K Thampan 5547703 Amend sap_control to call generate_payments
33 with insert-mode if absence is > 0 and
34 also change csr_check_if_existing_entries
35 not to reference from per_absence_attendances
36 table
37 19-OCT-06 K Thampan 5604330 Amended function entitled_to_sap to
38 check for employee date of death before creating
39 'Some work was done' stoppage.
40 Also update check_death to create stoppage based
41 on the 'rolling 7-day week' not the next Sunday.
42 02-AUG-07 P Balu 6271407 Changed the To_Date function to fnd_date.canonical_to_date
43 function in the cursor csr_existing_entries.
44 */
45 --------------------------------------------------------------------------------
46 g_package varchar2(33) := ' ssp_sap_pkg.'; -- Global package name
47 --
48
49 cursor csr_absence_details (p_maternity_id in number) is
50 --
51 -- Get details of maternity leave for a maternity, in chronological
52 -- order of start date
53 --
54 select absence.absence_attendance_id,
55 absence.date_start,
56 nvl (absence.date_end, hr_general.end_of_time) date_end,
57 absence.date_notification,
58 absence.accept_late_notification_flag
59 from
60 per_absence_attendances ABSENCE
61 where absence.maternity_id = p_maternity_id
62 order by absence.date_start;
63 --
64 --------------------------------------------------------------------------------
65 cursor csr_personal_details (p_maternity_id in number) is
66 --
67 -- Get details of the maternal woman
68 --
69 select maternity.person_id,
70 maternity.due_date,
71 ssp_sap_pkg.MATCHING_WEEK_OF_ADOPTION (MATCHING_DATE) MW,
72 MATCHING_DATE MATCHING_DATE,
73 PLACEMENT_DATE PLACEMENT_DATE,
74 DISRUPTED_PLACEMENT_DATE DISRUPTED_PLACEMENT_DATE,
75 maternity.maternity_id,
76 maternity.actual_birth_date,
77 maternity.live_birth_flag,
78 maternity.start_date_with_new_employer,
79 maternity.MPP_start_date APP_start_date,
80 maternity.notification_of_birth_date,
81 maternity.start_date_maternity_allowance,
82 maternity.pay_SMP_as_lump_sum pay_SAP_as_lump_sum,
83 person.date_of_death,
84 service.date_start,
85 nvl (service.final_process_date, hr_general.end_of_time) FINAL_PROCESS_DATE
86 from
87 ssp_maternities MATERNITY,
88 per_all_people_f PERSON,
89 per_periods_of_service SERVICE
90 where
91 person.person_id = maternity.person_id
92 and person.person_id = service.person_id
93 and maternity.maternity_id = p_maternity_id
94 and nvl(service.actual_termination_date+1,service.date_start)
95 between person.effective_start_date
96 and person.effective_end_date
97 and service.date_start = (select max(serv.date_start)
98 from per_periods_of_service serv
99 where serv.person_id = person.person_id);
100 --
101 --------------------------------------------------------------------------------
102 person csr_personal_details%rowtype;
103 g_SAP_element csr_SAP_element_details%rowtype;
104 g_SAP_Correction_element csr_SAP_element_details%rowtype;
105 --------------------------------------------------------------------------------
106 l_saturday varchar2(100) := to_char(to_date('06/01/2001','DD/MM/YYYY'),'DAY');
107 l_sunday varchar2(100) := to_char(to_date('07/01/2001','DD/MM/YYYY'),'DAY');
108
109 function MATCHING_WEEK_OF_ADOPTION
110 --
111 -- Returns the date on which the MW starts
112 --
113 (p_matching_date in date)
114 --
115 -- p_matching_date comes from the person's maternity record
116 --
117 return date is
118 --
119 -- MW is the Sunday prior to MATCHING_WEEK DATE
120 --
121 l_MW date := (next_day (p_matching_date,l_sunday) -7);
122 --
123 begin
124 --
125 return l_MW;
126 --
127 end MATCHING_WEEK_OF_ADOPTION;
128 --
129 --------------------------------------------------------------------------------
130 Function CONTINUOUS_EMPLOYMENT_DATE (p_matching_date in date)
131 return date is
132 --
133 -- The continuous employment start date is the date on which the woman must
134 -- have been employed (and continuously from then to the MW) in order to
135 -- qualify for SAP. It is the MW minus the continuous employment period
136 -- specified on the SAP element.
137 -- A woman must have started work on or before the last day of the
138 -- week which starts 182 days (26 weeks) before the last day of the MW. In
139 -- SAP weeks start on Sunday and end on Saturday.
140 --
141 l_SAP_element csr_SAP_element_details%rowtype;
142 l_Continuously_employed_since date;
143 --
144 begin
145 --
146 open csr_SAP_element_details (p_matching_date,c_SAP_element_name);
147 fetch csr_SAP_element_details into l_SAP_element;
148 close csr_SAP_element_details;
149 --
150 l_Continuously_employed_since :=
151 next_day(next_day(
152 MATCHING_WEEK_OF_ADOPTION (p_matching_date) ,l_saturday)
153 - (l_SAP_element.continuous_employment_period),l_saturday);
154 --
155 return l_Continuously_employed_since;
156 --
157 end continuous_employment_date;
158 --
159 --------------------------------------------------------------------------------
160
161
162 procedure get_SAP_correction_element (p_effective_date in date) is
163 --
164 l_proc varchar2(72) := g_package||'get_SAP_correction_element';
165 --
166 procedure check_parameters is
167 begin
168 --
169 hr_utility.trace (l_proc||' p_effective_date = '
170 ||to_char (p_effective_date));
171 --
172 hr_api.mandatory_arg_error (
173 p_api_name => l_proc,
174 p_argument => 'effective_date',
175 p_argument_value=> p_effective_date);
176 end check_parameters;
177 --
178 begin
179 --
180 hr_utility.set_location (l_proc,1);
181 --
182 check_parameters;
183 --
184 open csr_SAP_element_details (p_effective_date,c_SAP_Corr_element_name);
185 fetch csr_SAP_element_details into g_SAP_Correction_element;
186 close csr_SAP_element_details;
187 --
188 hr_utility.set_location (l_proc,100);
189 --
190 end get_SAP_correction_element;
191 --
192 --------------------------------------------------------------------------------
193 procedure get_SAP_element (p_effective_date in date) is
194 --
195 l_proc varchar2(72) := g_package||'get_SAP_element';
196 --
197 procedure check_parameters is
198 begin
199 --
200 hr_utility.trace (l_proc||' p_effective_date = '
201 ||to_char (p_effective_date));
202 --
203 hr_api.mandatory_arg_error (
204 p_api_name => l_proc,
205 p_argument => 'effective_date',
206 p_argument_value=> p_effective_date);
207 end check_parameters;
208 --
209 begin
210 --
211 hr_utility.set_location (l_proc,1);
212 --
213 check_parameters;
214 --
215 open csr_SAP_element_details (p_effective_date,c_SAP_element_name);
216 fetch csr_SAP_element_details into g_SAP_element;
217 close csr_SAP_element_details;
218 --
219 hr_utility.set_location (l_proc,100);
220 --
221 end get_SAP_element;
222 --
223 --------------------------------------------------------------------------------
224 function EARLIEST_APP_START_DATE (p_due_date in date) return date is
225 --
226 -- The earliest APP start date, under normal circumstances is the
227 -- child expected date minus
228 -- the number of weeks specified on the SAP element for earliest SAP start
229 --
230 l_earliest_APP_start date;
231 l_SAP_element csr_SAP_element_details%rowtype;
232 --
233 begin
234 --
235 open csr_SAP_element_details (p_due_date,c_SAP_element_name);
236 fetch csr_SAP_element_details into l_SAP_element;
237 close csr_SAP_element_details;
238 --
239 l_earliest_APP_start := p_due_date
240 - (l_SAP_element.earliest_start_of_APP);
241 return l_earliest_APP_start;
242 --
243 end earliest_APP_start_date;
244 --
245 --------------------------------------------------------------------------------
246 function MATERNITY_RECORD_EXISTS (p_person_id in number) return boolean is
247 --
248 cursor maternity_record is
249 select 1
250 from ssp_maternities
251 where person_id = p_person_id;
252 --
253 l_dummy number (1);
254 l_maternity_record_exists boolean;
255 --
256 begin
257 --
258 open maternity_record;
259 fetch maternity_record into l_dummy;
260 l_maternity_record_exists := maternity_record%found;
261 close maternity_record;
262 --
263 return l_maternity_record_exists;
264 --
265 end maternity_record_exists;
266 --
267 --------------------------------------------------------------------------------
268 function AVERAGE_EARNINGS return number is
269 --
270 l_average_earnings number := null;
271 l_effective_date date := null;
272 l_dummy number;
273 l_dummy2 number; -- nocopy fix, placeholder variable
274 l_user_entered varchar2(30) := 'N'; -- DFoster 1304683
275 l_absence_category varchar2(30) := 'GB_ADO'; --DFoster 1304683
276 l_payment_periods number := null; --DFoster 1304683
277 l_proc varchar2(72) := g_package||'average_earnings';
278 --
279 cursor csr_average_earnings is
280 select average_earnings_amount
281 from ssp_earnings_calculations
282 where person_id = person.person_id
283 and effective_date = l_effective_date;
284 --
285 begin
286 --
287 hr_utility.set_location ('Entering '||l_proc,1);
288 --
289 l_effective_date := greatest(person.MW, person.date_start);
290 --
291 open csr_average_earnings;
292 fetch csr_average_earnings into l_average_earnings;
293 if csr_average_earnings%notfound
294 then
295 ssp_ern_ins.ins (p_earnings_calculations_id => l_dummy,
296 p_object_version_number => l_dummy2,
297 p_person_id => person.person_id,
298 p_effective_date => l_effective_date,
299 p_average_earnings_amount => l_average_earnings,
300 p_user_entered => l_user_entered, --DFoster 1304683
301 p_absence_category => l_absence_category, --DFoster 1304683
302 p_payment_periods => l_payment_periods); --DFoster 1304683
303 end if;
304 --
305 close csr_average_earnings;
306 --
307 hr_utility.set_location ('Leaving '||l_proc,10);
308 --
309 return l_average_earnings;
310 --
311 end average_earnings;
312 --------------------------------------------------------------------------------
313 function entitled_to_SAP (p_maternity_id in number) return boolean is
314 --
315 -- See header for description of this procedure.
316 --
317 no_prima_facia_entitlement exception;
318 invalid_absence_date exception;
319 l_work_start_date date := hr_general.end_of_time;
320 stoppage_end_date date := null;
321 no_of_absence_periods integer := 0;
322 l_proc varchar2(72) := g_package||'entitled_to_SAP';
323 l_keep_stoppages boolean default FALSE;
324 --
325 cursor csr_no_of_absences is
326 --
327 -- Get the number of distinct absences within a maternity pay period
328 --
329 select count (*)
330 from per_absence_attendances
331 where person_id = person.person_id
332 and maternity_id = p_maternity_id;
333 --
334 -- returns entries associated with a maternity_id.
335 cursor csr_check_if_existing_entries is
336 --
337 select /*+ ORDERED use_nl(paa,paaf,etype,entry) */
338 entry.element_entry_id,
339 entry.effective_start_date
340 from per_all_assignments_f PAAF,
341 pay_element_types_f ETYPE,
342 pay_element_entries_f ENTRY
343 where PAAF.person_id = person.person_id
344 and ETYPE.element_name = c_SAP_element_name
345 and ETYPE.legislation_code = 'GB'
346 and ENTRY.element_type_id = ETYPE.element_type_id
347 and entry.creator_type = c_SAP_creator_type
348 and entry.creator_id = p_maternity_id
349 and entry.assignment_id = PAAF.assignment_id
350 and not exists (
351 --
352 -- Do not select entries which have already had reversal action
353 -- taken against them because they are effectively cancelled out.
354 --
355 select 1
356 from pay_element_entries_f ENTRY2
357 where entry.element_entry_id=entry2.target_entry_id
358 and entry.assignment_id = entry2.assignment_id)
359 --
360 and not exists (
361 --
362 -- Do not select reversal entries
363 --
364 select 1
365 from pay_element_links_f LINK,
366 pay_element_types_f TYPE
367 where link.element_link_id = entry.element_link_id
368 and entry.effective_start_date between link.effective_start_date and link.effective_end_date
369 and link.element_type_id = type.element_type_id
370 and link.effective_start_date between type.effective_start_date and type.effective_end_date
371 and type.element_name = c_SAP_Corr_element_name);
372 --
373 l_existing_entries csr_check_if_existing_entries%rowtype;
374 --
375 procedure create_stoppage (
376 --
377 -- Create a stoppage of payment for SAP
378 --
379 p_reason in varchar2,
380 p_withhold_from in date,
381 p_withhold_to in date default null
382 ) is
383 --
384 l_proc varchar2(72) := g_package||'create_stoppage';
385 l_dummy number;
386 l_reason_id number;
387 --
388 procedure check_parameters is
389 --
390 begin
391 --
392 hr_utility.trace (l_proc||' p_reason = '||p_reason);
393 hr_utility.trace (l_proc||' withhold from '||to_char (p_withhold_from));
394 hr_utility.trace (l_proc||' withhold to '||to_char (p_withhold_to));
395 --
396 hr_api.mandatory_arg_error (
397 p_api_name => l_proc,
398 p_argument => 'reason',
399 p_argument_value=> p_reason);
400 --
401 hr_api.mandatory_arg_error (
402 p_api_name => l_proc,
403 p_argument => 'withhold_from',
404 p_argument_value=> p_withhold_from);
405 --
406 end check_parameters;
407 --
408 begin
409 --
410 hr_utility.set_location (l_proc,1);
411 --
412 check_parameters;
413 --
414 l_reason_id := ssp_smp_support_pkg.withholding_reason_id (
415 g_SAP_element.element_type_id,
416 p_reason);
417 --
418 if not ssp_smp_support_pkg.stoppage_overridden (
419 p_maternity_id => p_maternity_id,
420 p_reason_id => l_reason_id)
421 then
422 --
423 -- Only create the stoppage if there is not already a stoppage marked
424 -- as overridden. Thus, overriding a stoppage effectively blocks that
425 -- reason being used to withhold payment for this person.
426 --
427 ssp_stp_ins.ins (p_withhold_from => p_withhold_from,
428 p_withhold_to => p_withhold_to,
429 p_stoppage_id => l_dummy,
430 p_object_version_number => l_dummy,
431 p_maternity_id => p_maternity_id,
432 p_user_entered => 'N',
433 p_reason_id => l_reason_id);
434 else
435 hr_utility.trace (l_proc||' Stoppage is overridden');
436 end if;
437 --
438 hr_utility.set_location (l_proc,100);
439 --
440 end create_stoppage;
441 --
442 procedure remove_stoppages is
443 --
444 -- Remove old system, non-overridden stoppages
445 --
446 cursor csr_stoppages is
447 select stoppage_id
448 from ssp_stoppages
449 where user_entered <>'Y'
450 and override_stoppage <> 'Y'
451 and maternity_id = p_maternity_id;
452 --
453 l_dummy number;
454 l_proc varchar2 (72) := g_package||'remove_stoppages';
455 --
456 begin
457 --
458 hr_utility.set_location (l_proc,1);
459 --
460 for each_stoppage in csr_stoppages LOOP
461 ssp_stp_del.del (p_stoppage_id => each_stoppage.stoppage_id,
462 p_object_version_number => l_dummy);
463 end loop;
464 --
465 hr_utility.set_location (l_proc,100);
466 --
467 end remove_stoppages;
468 --
469 procedure check_continuity_rule is
470 --
471 -- Check that the person has the right amount of continuous service to
472 -- qualify for SAP
473 --
474 cursor period_of_service is
475 --
476 -- Check the period of service length up to the MW start date
477 --
478 select 1
479 from per_periods_of_service
480 where person_id = person.person_id
481 and date_start <= ssp_sap_pkg.continuous_employment_date
482 (person.matching_date)
483 and nvl (actual_termination_date, hr_general.end_of_time)
484 >= person.MW;
485 --
486 l_dummy number (1);
487 l_proc varchar2 (72) := g_package||'check_continuity_rule';
488 --
489 begin
490 --
491 hr_utility.set_location (l_proc,1);
492 --
493 open period_of_service;
494 fetch period_of_service into l_dummy;
495 --
496 if period_of_service%notfound then
497 --
498 -- Stop all SAP payment for the Adoption because the person has not
499 -- been continuously employed for long enough.
500 --
501 create_stoppage (p_withhold_from => person.APP_start_date,
502 p_reason => 'Insufficient employment');
503 end if;
504 --
505 close period_of_service;
506 --
507 hr_utility.set_location (l_proc,100);
508 --
509 end check_continuity_rule;
510 --
511 /*
512 procedure check_stillbirth is
513 --
514 -- Check the pregnancy condition for qualification for SMP
515 --
516 l_proc varchar2 (72) := g_package||'check_stillbirth';
517 --
518 begin
519 --
520 hr_utility.set_location (l_proc,1);
521 --
522 -- Woman must be still pregnant, have had a live birth, or have had a
523 -- stillbirth after the threshhold week to be eligible for SMP
524 --
525 if NOT (woman.actual_birth_date is null
526 or woman.live_birth_flag = 'Y'
527 or woman.actual_birth_date > woman.EWC
528 - g_SMP_element.stillbirth_threshhold_week)
529 then
530 --
531 -- Stop SMP payment from the start of the week in which the absence
532 -- started.
533 --
534 create_stoppage (p_withhold_from => woman.MPP_start_date,
535 p_reason => 'Stillbirth');
536 end if;
537 --
538 hr_utility.set_location (l_proc,100);
539 --
540 end check_stillbirth;
541 --
542 */
543 procedure check_new_employer is
544 --
545 -- Check the person has not been employed by a new employer after the
546 -- child placement
547 --
548 l_proc varchar2 (72) := g_package||'check_new_employer';
549 --
550 begin
551 --
552 hr_utility.set_location (l_proc,1);
553 --
554 if person.start_date_with_new_employer >= person.placement_date then
555 --
556 -- Stop SAP payment from the start of the week in which the person
557 -- started work for a new employer after the placement of her child.
558 --
559 create_stoppage (p_withhold_from => ssp_smp_support_pkg.start_of_week
560 (person.start_date_with_new_employer),
561 p_reason => 'Worked for another employer');
562 end if;
563 --
564 hr_utility.set_location (l_proc,100);
565 --
566 end check_new_employer;
567 --
568 procedure check_maternity_allowance is
569 --
570 -- SAP ceases when SMA starts.
571 --
572 l_proc varchar2 (72) := g_package||'check_maternity_allowance';
573 --
574 begin
575 --
576 hr_utility.set_location (l_proc,1);
577 --
578 if person.start_date_maternity_allowance is not null then
579 --
580 -- Stop SAP payment from the start of the week in which SMA was first
581 -- paid.
582 --
583 create_stoppage (p_withhold_from => ssp_smp_support_pkg.start_of_week
584 (person.start_date_maternity_allowance),
585 p_reason => 'Employee is receiving SMA');
586 end if;
587 --
588 hr_utility.set_location (l_proc,100);
589 --
590 end check_maternity_allowance;
591 --
592 procedure check_death is
593 --
594 -- SAP ceases after the death of the woman.
595 --
596 l_proc varchar2 (72) := g_package||'check_death';
597 --
598 cursor csr_check_death is
599 select ppf.date_of_death
600 from per_all_people_f ppf
601 where ppf.person_id = person.person_id
602 and ppf.date_of_death is not null;
603 --
604 cursor csr_get_week_date is
605 select to_char(person.app_start_date, 'DAY')
606 from dual;
607 --
608 l_date_of_the_week varchar2(20);
609 l_death_date date;
610
611 begin
612 --
613 hr_utility.set_location (l_proc,1);
614 --
615 open csr_check_death;
616 fetch csr_check_death into l_death_date;
617 close csr_check_death;
618 --
619 if l_death_date is not null then
620 open csr_get_week_date;
621 fetch csr_get_week_date into l_date_of_the_week;
622 close csr_get_week_date;
623 --
624 -- SAP ceases on the Saturday following death
625 --
626 create_stoppage (p_withhold_from => next_day (person.date_of_death,
627 l_date_of_the_week), --l_sunday),
628 p_reason => 'Employee died');
629 end if;
630 --
631 hr_utility.set_location (l_proc,100);
632 --
633 end check_death;
634 --
635 procedure check_disrupted_placement is
636
637 begin
638
639 if person.disrupted_placement_date is not null
640 and (person.disrupted_placement_date + (g_SAP_element.disrupted_placement_weeks)) <
641 ((g_SAP_element.maximum_APP * 7) + person.APP_start_date)
642 then
643
644 create_stoppage (p_withhold_from => person.disrupted_placement_date +
645 (g_SAP_element.disrupted_placement_weeks),
646 p_reason => 'Placement disrupted');
647
648 end if;
649
650 end check_disrupted_placement;
651
652 procedure check_average_earnings is
653 --
654 -- The woman must earn enough to qualify for SAP
655 --
656 l_proc varchar2 (72) := g_package||'check_average_earnings';
657 l_average_earnings number := average_earnings;
658 l_reason_for_no_earnings varchar2 (80) := null;
659 earnings_not_derived exception;
660 --
661 begin
662 --
663 hr_utility.set_location (l_proc,1);
664 --
665 if l_average_earnings = 0
666 then
667 --
668 -- If the average earnings figure returned is zero then check that
669 -- no error message was set. Error messages will be set for system-
670 -- generated average earnings when the earnings could not be derived
671 -- for some reason, but to allow this procedure to continue, no error
672 -- will be raised.
673 --
674 l_reason_for_no_earnings:=ssp_smp_support_pkg.average_earnings_error;
675 --
676 if l_reason_for_no_earnings is not null then
677 create_stoppage (p_withhold_from => person.aPP_start_date,
678 p_reason => l_reason_for_no_earnings);
679 --
680 raise earnings_not_derived;
681 end if;
682 end if;
683 --
684 if l_average_earnings
685 < ssp_smp_support_pkg.NI_Lower_Earnings_Limit (person.MW)
686 then
687 --
688 -- Stop SAP payment from the APP start date
689 --
690 create_stoppage (p_withhold_from => person.APP_start_date,
691 p_reason => 'Earnings too low');
692 end if;
693 --
694 hr_utility.set_location (l_proc,100);
695 --
696 exception
697 --
698 when earnings_not_derived then
699 --
700 -- Exit silently from this procedure
701 --
702 hr_utility.trace (l_proc||' Earnings not derived');
703 null;
704 --
705 end check_average_earnings;
706 --
707 /*
708 procedure check_medical_evidence is
709 --
710 -- Check the acceptability of the maternity evidence
711 --
712 cursor medical is
713 select *
714 from ssp_medicals
715 where maternity_id = woman.maternity_id
716 and evidence_status = 'CURRENT';
717 --
718 l_proc varchar2 (72) := g_package||'check_medical_evidence';
719 l_medical medical%rowtype;
720 --
721 begin
722 --
723 hr_utility.set_location (l_proc,1);
724 --
725 open medical;
726 fetch medical into l_medical;
727 --
728 if medical%notfound -- no medical evidence recorded
729 or (medical%found and
730 --
731 -- evidence is dated too early
732 --
733 (l_medical.evidence_date < person.EWC
734 - g_SMP_element.earliest_SMP_evidence)
735 --
736 -- evidence was received late for no good reason
737 --
738 or (l_medical.evidence_received_date > woman.MPP_start_date
739 + g_SMP_element.latest_SMP_evidence
740 and l_medical.accept_late_evidence_flag = 'N')
741 --
742 -- evidence was received late, even after extension allowed
743 --
744 or (l_medical.evidence_received_date > woman.MPP_start_date
745 + g_SMP_element.extended_SMP_evidence))
746 then
747 --
748 -- Stop SMP payment from the start of the week in which the MPP
749 -- started.
750 --
751 create_stoppage (p_withhold_from => woman.MPP_start_date,
752 p_reason => 'Late/unacceptable evidence');
753 end if;
754 --
755 close medical;
756 --
757 hr_utility.set_location (l_proc,100);
758 --
759 end check_medical_evidence;
760 --
761 */
762 /*
763 procedure check_birth_confirmation is
764 --
765 -- Check that confirmation of birth was received in good time.
766 --
767 l_proc varchar2 (72) := g_package||'check_birth_confirmation';
768 --
769 begin
770 --
771 hr_utility.set_location (l_proc,1);
772 --
773 --
774 -- This should not create a stoppage. A quick fix for bug 1021179
775 -- is to comment out the stoppage process.
776 --
777 null;
778
779 -- if (woman.actual_birth_date is not null
780 -- and (nvl (woman.notification_of_birth_date, sysdate)
781 -- > woman.actual_birth_date
782 -- + g_SMP_element.notice_of_birth_requirement))
783 -- then
784 --
785 -- Stop SMP payment from the start of the week in which the MPP
786 -- started.
787 --
788 -- create_stoppage (p_withhold_from => woman.MPP_start_date,
789 -- p_reason => 'Late notification of birth');
790 -- end if;
791 --
792 hr_utility.set_location (l_proc,100);
793 --
794 end check_birth_confirmation;
795 --
796 */
797 procedure check_parameters is
798 --
799 begin
800 --
801 hr_utility.trace (l_proc||' p_maternity_id = '
802 ||to_char (p_maternity_id));
803 --
804 hr_api.mandatory_arg_error (
805 p_api_name => l_proc,
806 p_argument => 'maternity_id',
807 p_argument_value=> p_maternity_id);
808 --
809 end check_parameters;
810 --
811 begin
812 --
813 hr_utility.set_location (l_proc,1);
814 --
815 check_parameters;
816 --
817 -- Get the details of the woman and her maternity.
818 --
819 open csr_personal_details (p_maternity_id);
820 fetch csr_personal_details into person;
821 --
822 if csr_personal_details%notfound
823 then
824 --
825 -- If no maternity record exists then there can be no entitlement to SAP
826 --
827 close csr_personal_details;
828 --
829 hr_utility.trace (l_proc||' Person has no maternity record - exiting');
830 --
831 raise no_prima_facia_entitlement;
832 end if;
833 --
834 close csr_personal_details;
835 --
836 if person.APP_start_date is null then
837 --
838 -- If the APP has not started then there is no entitlement to SAP.
839 --
840 hr_utility.trace (l_proc||' Person has no APP start date - exiting');
841 --
842 raise no_prima_facia_entitlement;
843 end if;
844 --
845 -- Count how many absences there are for the maternity.
846 --
847 open csr_no_of_absences;
848 fetch csr_no_of_absences into no_of_absence_periods;
849 close csr_no_of_absences;
850 --
851 if no_of_absence_periods = 0
852 then
853 --
854 -- check if entries exist despite there being no absence
855 --
856 open csr_check_if_existing_entries;
857 fetch csr_check_if_existing_entries into l_existing_entries;
858 --
859 if csr_check_if_existing_entries%NOTFOUND
860 then
861 hr_utility.trace (l_proc||' Person has not stopped work - exiting');
862 raise no_prima_facia_entitlement;
863 end if;
864 --
865 -- if entries are found then the absence has been deleted and entries remain
866 -- that must be dealt with
867 --
868 while csr_check_if_existing_entries%FOUND LOOP
869 fetch csr_check_if_existing_entries into l_existing_entries;
870 end loop;
871 --
872 close csr_check_if_existing_entries;
873 l_keep_stoppages := TRUE;
874 end if;
875 --
876 -- Having established a prima facia entitlement to SAP, perform checks which
877 -- may lead to creation of stoppages for particular periods.
878 --
879 hr_utility.set_location ('ssp_smp_pkg.entitled_to_SAP',2);
880 --
881 -- Get the SAP legislative parameters.
882 --
883 get_SAP_element (person.due_date);
884 --
885 -- Clear stoppages created by previous calculations of SAP but if an absence
886 -- is being deleted, then must keep stoppages so that when later comparison of
887 -- old_entry and hypothetical_entry is done then stoppages are still there.
888 --
889 if not l_keep_stoppages then
890 remove_stoppages;
891 end if;
892 --
893 for absence in csr_absence_details (p_maternity_id) LOOP
894 --
895 -- Check that sufficient notification of absence was given
896 --
897 if
898 -- notification of absence was later than the allowed date
899 (absence.date_notification > absence.date_start
900 - g_SAP_element.APP_notice_requirement
901 -- and there was no acceptable reason for the delay
902 and absence.accept_late_notification_flag = 'N')
903 --
904 -- or notification of absence was later than the extended allowable date
905 --
906 or (absence.date_notification > person.placement_date
907 + g_SAP_element.APP_notice_requirement)
908 then
909 --
910 -- Stop SAP payment from the start of the week in which the absence
911 -- starts, to the end of the notice period
912 --
913 stoppage_end_date := g_SAP_element.APP_notice_requirement
914 + absence.date_start - 1;
915 --
916 /*
917 Bug 2772479 - Late absence notification is no longer required for SAP. This may be reintroduced
918 so all code has been left in, with only the creation of the stoppage commented out.
919
920 create_stoppage (
921 p_withhold_from => ssp_smp_support_pkg.start_of_week
922 (absence.date_start),
923 p_withhold_to => ssp_smp_support_pkg.end_of_week (stoppage_end_date),
924 p_reason => 'Late absence notification');
925 */
926 end if;
927 --
928 hr_utility.set_location ('ssp_sap_pkg.entitled_to_SaP',3);
929 --
930 -- Check for any work done during the APP.
931 --
932 if
933 -- this is the first absence period in the APP
934 csr_absence_details%rowcount = 1
935 --
936 -- and the absence starts after the APP start date
937 and absence.date_start > person.APP_start_date
938 then
939 create_stoppage (p_reason => 'Some work was done',
940 p_withhold_from => person.APP_start_date,
941 p_withhold_to => ssp_smp_support_pkg.end_of_week
942 (absence.date_start -1));
943 end if;
944 --
945 if
946 -- this is the last absence period in the MPP
947 csr_absence_details%rowcount = no_of_absence_periods
948 --
949 -- and the absence period ends before the end of the MPP
950 and absence.date_end < (g_SAP_element.maximum_APP * 7)
951 + person.APP_start_date
952 and person.date_of_death is null
953 then
954 create_stoppage (p_reason => 'Some work was done',
955 p_withhold_from => ssp_smp_support_pkg.start_of_week
956 (absence.date_end+1));
957 elsif
958 -- there is a period of work between two absences
959 l_work_start_date < absence.date_start
960 and l_work_start_date < (g_SAP_element.maximum_APP * 7)
961 + person.APP_start_date
962 then
963 create_stoppage (p_reason => 'Some work was done',
964 p_withhold_from => ssp_smp_support_pkg.start_of_week
965 (l_work_start_date),
966 p_withhold_to => ssp_smp_support_pkg.end_of_week
967 (absence.date_start -1));
968 --
969 if absence.date_end <> hr_general.end_of_time
970 then
971 l_work_start_date := absence.date_end + 1;
972 else
973 --
974 -- This is not the last absence in the maternity but it has no end date.
975 --
976 hr_utility.trace (l_proc||' ERROR: Invalid null absence end date');
977 --
978 raise invalid_absence_date;
979 end if;
980 end if;
981 end loop;
982 --
983 check_continuity_rule;
984 --check_stillbirth;
985 check_new_employer;
986 check_maternity_allowance;
987 check_death;
988 check_disrupted_placement;
989 --check_medical_evidence;
990 --check_birth_confirmation;
991 check_average_earnings;
992 --
993 -- If we get this far the person is entitled to SAP (though stoppages may apply)
994 --
995 return TRUE;
996 --
997 exception
998 --
999 when invalid_absence_date then
1000 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1001 fnd_message.set_token ('PROCEDURE','ssp_smp_pkg.entitled_to_SAP');
1002 fnd_message.set_token ('STEP','3');
1003 --
1004 when no_prima_facia_entitlement then
1005 --
1006 -- Exit silently; this will allow us to call this procedure with impunity
1007 -- from absences which are not maternity absences (e.g. via a row trigger)
1008 --
1009 return FALSE;
1010 --
1011 end entitled_to_SAP;
1012 --------------------------------------------------------------------------------
1013 procedure generate_payments
1014 --
1015 --
1016 -- Starting with the start of the Adoption Pay Period (APP), create
1017 -- a nonrecurring entry for each week of maternity absence up to the
1018 -- end of the maternity absence or to the maximum number of weeks
1019 -- specified by the SAP rules. Note that there may be more than one
1020 -- absence for a maternity, and the absences should be put together;
1021 -- however, if the woman works for any part of a week (Sun-Sat), then
1022 -- that week does not count towards maternity pay.
1023 --
1024 -- If there is a stoppage which overlaps either partially or completely
1025 -- with the period covered by an entry then skip the creation of that
1026 -- entry and move on to the next week. There are two kinds of stoppages;
1027 -- those which apply temporarily, and those which apply forever once
1028 -- started. Stoppages only apply within a maternity. If there is a
1029 -- stoppage which applies forever (ie it has no end date), then there
1030 -- is no need to continue creating entries after the start of that
1031 -- stoppage. Temporary entries should only affect creation while they
1032 -- apply. A further feature of stoppages is that they may be overridden
1033 -- by the user; if the override flag is set, then take no acount of
1034 -- that stoppage when creating entries.
1035 --
1036 -- The maximum allowed number of weeks is held as a DDF segment on the
1037 -- SAP element.
1038 --
1039 -- Whilst each entry is created to cover a particular period of absence,
1040 -- the payroll period in which the entry resides is determined
1041 -- separately. The default is that the entry will be created in the
1042 -- payroll period which covers the end of the week of absence for which
1043 -- the entry is created. If, however, that payroll period is in the
1044 -- past, or has already been processed, or is closed, or is after the
1045 -- person's last standard process date, then the entry must be placed
1046 -- in the next open period for which no main payroll run has been
1047 -- performed and which falls around or before the last standard process
1048 -- date. If the entry cannot be created in any such period, for whatever
1049 -- reason, then an error should be raised and the user required to
1050 -- resolve the problem before any entry for the absence can be created.
1051 -- The user may choose to pay SAP as a lump sum, in which case all
1052 -- entries are to be placed in the next possible payroll period after
1053 -- the start of the Maternity Pay Period.
1054 --
1055 -- If any detail of the absence is changed, then the entries must be
1056 -- recalculated to ensure that the change is reflected in the
1057 -- payments. Therefore, we may be performing the entry creation when
1058 -- entries already exist for the absence. For each entry which we are
1059 -- about to create, we must check that there is not an existing entry
1060 -- which covers the same absence period. If there is not, then we
1061 -- create the entry as planned; if there is, then we must update the
1062 -- existing one rather than create a new one, if a change is required.
1063 -- However, if that entry has already been processed in a payroll run,
1064 -- then rather than updating it, we must ensure that the
1065 -- over/underpayment is corrected at the next opportunity. This is
1066 -- done by creating two entries; one which pays the correct amount
1067 -- and another, for the SAP Correction element, which reverses the
1068 -- incorrect payment by replicating it with a negative sign in front of
1069 -- the amount entry value. Before creating the negative entry, it is
1070 -- essential to check that there is not already a negative entry for
1071 -- the incorrect entry; we do not want to overcorrect.
1072 --
1073 -- The week commencing entry value is the date of the Sunday of the
1074 -- week for which this entry is being created.
1075 --
1076 -- The Rate entry value is simply 'Higher' or 'Lower'. The number of
1077 -- entries to be created at the higher rate is determined by the
1078 -- DDF segment on the SAP element. When creating entries for SAP,
1079 -- that number of entries is to be created at the higher rate before
1080 -- any are created at the lower rate, and so stoppages are always
1081 -- affecting the lower rate first.
1082 --
1083 -- The amount entry value is determined by the rate; if it is the
1084 -- higher rate, then the higer rate DDF segment of the SAP element
1085 -- will identify a percentage of average earnings. The average
1086 -- earnings should have been calculated by the entitlement check, for
1087 -- payroll users, or entered independently by HR users. If it is the
1088 -- lower rate, then the amount to be paid is held directly in the
1089 -- lower rate DDF segment on the SAP element.
1090 --
1091 -- Each entry created by this procedure is to have a creator type
1092 -- which identifies it as an SAP entry, and a creator_id which is
1093 -- the maternity_id.
1094 --
1095 -- p_deleting parameter has been added to carry out logic for
1096 -- dealing with deleted absences
1097 --
1098 (p_maternity_id in number,
1099 p_deleting in boolean default FALSE) is
1100 --
1101 type date_table is table of date index by binary_integer;
1102 type number_table is table of number index by binary_integer;
1103 type varchar_table is table of varchar2 (80) index by binary_integer;
1104 l_proc varchar2(72) := g_package||'generate_payments';
1105 --
1106 type SAP_entry is record (
1107 --
1108 element_entry_id number_table,
1109 element_link_id number_table,
1110 assignment_id number_table,
1111 effective_start_date date_table,
1112 effective_end_date date_table,
1113 amount number_table,
1114 rate varchar_table,
1115 week_commencing date_table,
1116 recoverable_amount number_table,
1117 stopped varchar_table,
1118 dealt_with varchar_table);
1119 --
1120 --
1121 -- A store for all the SAP entries that potentially may be
1122 -- granted to the person.
1123 --
1124 hypothetical_entry SAP_entry;
1125 --
1126 -- A tally of the number of weeks of the APP which are subject to stoppages.
1127 --
1128 l_stopped_weeks number := 0;
1129 --
1130 l_high_rate varchar2 (80) := null;
1131 l_low_rate varchar2 (80) := null;
1132 -- RT entries prob
1133 l_no_of_absence_periods integer := 0;
1134 --
1135 -- p_deleting passed into save_hypothetical_entries, so that logic can be
1136 -- dealt with for deleted absences.
1137 --
1138 procedure save_hypothetical_entries (p_deleting in boolean default false) is
1139 --
1140 -- Having generated the potential SAP entries, reconcile them with any
1141 -- previously granted entries for the same maternity.
1142 --
1143 cursor csr_existing_entries is
1144 --
1145 -- Get all entries and entry values for the Adoption
1146 --
1147 -- Decode the rate code to give the meaning using local variables
1148 -- populated earlier by Calculate_correct_SAP_rate
1149 -- these can then be passed directly into the hr_entry_api's and
1150 -- simplifies comparison with hypo entries
1151 --
1152 select entry.element_entry_id,
1153 entry.element_link_id,
1154 entry.assignment_id,
1155 entry.effective_start_date,
1156 entry.effective_end_date,
1157 -- if in future we get two different rates then a decode can be added here
1158 l_high_rate RATE,
1159 --BUG 6271407 begin
1160 /* to_date (ssp_smp_support_pkg.value
1161 (entry.element_entry_id,
1162 ssp_sap_pkg.c_week_commencing_name),
1163 'DD-MON-YYYY') WEEK_COMMENCING,*/
1164 fnd_date.canonical_to_date(ssp_smp_support_pkg.value
1165 (entry.element_entry_id,
1166 ssp_sap_pkg.c_week_commencing_name)) WEEK_COMMENCING,
1167 --BUG 6271407 end
1168 to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
1169 ssp_sap_pkg.c_amount_name)) AMOUNT,
1170 to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
1171 ssp_sap_pkg.c_recoverable_amount_name)) RECOVERABLE_AMOUNT
1172 from pay_element_entries_f ENTRY,
1173 per_all_assignments_f asg
1174 where creator_type = c_SAP_creator_type
1175 and creator_id = p_maternity_id
1176 and asg.person_id = person.person_id
1177 and asg.assignment_id = entry.assignment_id
1178 and entry.effective_start_date between asg.effective_start_date
1179 and asg.effective_end_date
1180 and not exists (
1181 --
1182 -- Do not select entries which have already had reversal action taken
1183 -- against them because they are effectively cancelled out.
1184 --
1185 select 1
1186 from pay_element_entries_f ENTRY2
1187 where entry.element_entry_id= entry2.target_entry_id
1188 and entry.assignment_id = entry2.assignment_id)
1189 --
1190 and not exists (
1191 --
1192 -- Do not select reversal entries
1193 --
1194 select 1
1195 from pay_element_links_f LINK,
1196 pay_element_types_f TYPE
1197 where link.element_link_id = entry.element_link_id
1198 and entry.effective_start_date between link.effective_start_date
1199 and link.effective_end_date
1200 and link.element_type_id = type.element_type_id
1201 and link.effective_start_date between type.effective_start_date
1202 and type.effective_end_date
1203 and type.element_name = c_SAP_Corr_element_name);
1204 --
1205 cursor csr_no_of_absences is
1206 --
1207 -- Get the number of distinct absences within a maternity pay period
1208 --
1209 select count (*)
1210 from per_absence_attendances
1211 where person_id = person.person_id
1212 and maternity_id = p_maternity_id;
1213 --
1214 l_ins_corr_ele boolean;
1215 l_dummy number;
1216 Entry_number integer;
1217 l_ern_calc_id number;
1218 l_ob_v_no number;
1219 l_new_ob_v_no number;
1220 l_proc varchar2 (72) := g_package||'save_hypothetical_entries';
1221 --
1222 -- This procedure was a private procedure in the function entitled_to_sap. I
1223 -- wanted to call it within this procedure (generate_payments) aswell, so
1224 -- instead of making it a public procedure I have copied the procedure to here.
1225 --
1226 procedure remove_stoppages is
1227 --
1228 -- Remove old system, non-overridden stoppages
1229 --
1230 cursor csr_stoppages is
1231 --
1232 select stoppage_id
1233 from ssp_stoppages
1234 where user_entered <>'Y'
1235 and override_stoppage <> 'Y'
1236 and maternity_id = p_maternity_id;
1237 --
1238 l_dummy number;
1239 l_proc varchar2 (72) := g_package||'.remove_stoppages';
1240 --
1241 begin
1242 --
1243 hr_utility.set_location (l_proc,1);
1244 --
1245 for each_stoppage in csr_stoppages LOOP
1246 ssp_stp_del.del (p_stoppage_id => each_stoppage.stoppage_id,
1247 p_object_version_number => l_dummy);
1248 end loop;
1249 --
1250 hr_utility.set_location (l_proc,100);
1251 --
1252 end remove_stoppages;
1253 --
1254 begin
1255 --
1256 hr_utility.set_location('Entering: '||l_proc,10);
1257 --
1258 get_SAP_correction_element (person.due_date);
1259 --
1260 -- Check each existing SaP entry in turn against all the potential new ones.
1261 --
1262 <<OLD_ENTRIES>>
1263 for old_entry in csr_existing_entries
1264 LOOP
1265 --First loop through the hypothetical entries to see if there is one
1266 --which covers the same week as the old entry and is not subject to
1267 --a stoppage. If there isn't one, invalidate the old entry.
1268 --Assume we don't need to correct the entry until we discover otherwise:
1269 --
1270 l_ins_corr_ele := FALSE;
1271 begin
1272 entry_number := 0;
1273 if p_deleting then
1274 raise no_data_found; -- enter exception handler
1275 end if;
1276 LOOP
1277 entry_number := entry_number +1;
1278 -- Exit the loop when we find a hypothetical entry covering the
1279 -- same week as the old entry, which is not subject to a stoppage.
1280 -- If no such match is found, then we will reach the end of the
1281 -- pl/sql table and attempt to read beyond the existing rows; this
1282 -- will cause us to enter the exception handler and indicate that
1283 -- no match was found.
1284 exit when ((old_entry.week_commencing
1285 = hypothetical_entry.week_commencing (entry_number)
1286 and not hypothetical_entry.stopped (entry_number) = 'TRUE'
1287 and ssp_smp_pkg.g_smp_update = 'N')
1288 or (old_entry.effective_start_date
1289 = hypothetical_entry.effective_start_date (entry_number)
1290 and old_entry.week_commencing
1291 = hypothetical_entry.week_commencing (entry_number)
1292 and not hypothetical_entry.stopped (entry_number) = 'TRUE'
1293 and ssp_smp_pkg.g_smp_update = 'Y'));
1294 end loop;
1295 hr_utility.trace (l_proc||' Old entry / Hypo entry time Match with values:');
1296 hr_utility.trace (l_proc||' Rate: ' ||old_entry.rate||' / '
1297 ||hypothetical_entry.rate (Entry_number));
1298 hr_utility.trace (l_proc||' Amount: '
1299 ||old_entry.amount||' / '
1300 ||hypothetical_entry.amount (entry_number));
1301 hr_utility.trace (l_proc||' Recoverable: '
1302 ||old_entry.recoverable_amount||' / '
1303 ||hypothetical_entry.recoverable_amount (entry_number));
1304 hr_utility.trace (l_proc||' Week Comm: '
1305 ||hypothetical_entry.week_commencing (entry_number) );
1306 --A hypo entry covers the same week as the old one
1307 if old_entry.rate = hypothetical_entry.rate (entry_number)
1308 and old_entry.amount = hypothetical_entry.amount(entry_number)
1309 and old_entry.recoverable_amount
1310 = hypothetical_entry.recoverable_amount (entry_number)
1311 then
1312 -- the hypo entry has the same values as the old one
1313 -- don't create a correction element.
1314 -- don't create a new entry
1315 hypothetical_entry.dealt_with (entry_number) := 'TRUE';
1316 hr_utility.trace (l_proc||' leave unchanged');
1317 else
1318 if ssp_smp_support_pkg.entry_already_processed
1319 (old_entry.element_entry_id)
1320 then l_ins_corr_ele := TRUE;
1321 hr_utility.trace (l_proc||' processed - correct it');
1322 else
1323 -- update old entry
1324 hr_utility.trace (l_proc||' unprocessed - update it');
1325 hr_entry_api.update_element_entry (
1326 p_dt_update_mode => 'CORRECTION',
1327 p_session_date => old_entry.effective_start_date,
1328 p_element_entry_id => old_entry.element_entry_id,
1329 p_input_value_id1 => g_SAP_element.rate_id,
1330 p_input_value_id2 => g_SAP_element.amount_id,
1331 p_input_value_id3 => g_SAP_element.recoverable_amount_id,
1332 p_entry_value1=> hypothetical_entry.rate (entry_number),
1333 p_entry_value2=> hypothetical_entry.amount(entry_number),
1334 p_entry_value3=>
1335 hypothetical_entry.recoverable_amount (entry_number));
1336 --
1337 --prevent insertion of new entry
1338 --
1339 hypothetical_entry.dealt_with (entry_number) := 'TRUE';
1340 end if;
1341 end if;
1342 exception
1343 when no_data_found then
1344 -- There was no new entry which exactly matched the old entry.
1345 -- or we are deleting.
1346 entry_number := null;
1347 hr_utility.trace (l_proc||' No Old entry - Hypo entry time Match');
1348 hr_utility.trace (l_proc||' or p_deleting is true');
1349 hr_utility.trace (l_proc||' Old entry values:');
1350 hr_utility.trace (l_proc||' Rate: '||old_entry.rate);
1351 hr_utility.trace (l_proc||' Amount: '||old_entry.amount);
1352 hr_utility.trace (l_proc||' Recoverable: '
1353 ||old_entry.recoverable_amount);
1354 if ssp_smp_support_pkg.entry_already_processed
1355 (old_entry.element_entry_id)
1356 then l_ins_corr_ele := TRUE;
1357 hr_utility.trace (l_proc||' Old entry already processed');
1358 else
1359 hr_utility.trace (l_proc||' Old entry NOT already processed');
1360 --Old entry not already processed so delete it
1361 hr_entry_api.delete_element_entry (
1362 p_dt_delete_mode => 'ZAP',
1363 p_session_date => old_entry.effective_start_date,
1364 p_element_entry_id => old_entry.element_entry_id);
1365 end if;
1366 end;
1367 if l_ins_corr_ele
1368 then
1369 -- Create a correction element to reverse the old entry. Then create a
1370 -- brand new entry with the correct values.
1371 --
1372 hr_utility.trace (l_proc ||
1373 ' Inserting CORRECTION entry for week commencing ' ||
1374 to_char (old_entry.week_commencing));
1375 hr_utility.trace (l_proc||' Old value / New value:');
1376 if entry_number is null then
1377 hr_utility.trace (l_proc||' Rate: '
1378 ||old_entry.rate||' / NA');
1379 hr_utility.trace (l_proc||' Amount: '
1380 ||old_entry.amount||' / NA');
1381 hr_utility.trace (l_proc||' Recoverable: '
1382 ||old_entry.recoverable_amount||' / NA');
1383 else
1384 hr_utility.trace (l_proc||' Rate: '
1385 ||old_entry.rate||' / '
1386 ||hypothetical_entry.rate (Entry_number));
1387 hr_utility.trace (l_proc||' Amount: '
1388 ||old_entry.amount||' / '
1389 ||hypothetical_entry.amount (entry_number));
1390 hr_utility.trace (l_proc||' Recoverable: '
1391 ||old_entry.recoverable_amount||' /'
1392 ||hypothetical_entry.recoverable_amount (entry_number));
1393 end if;
1394 --
1395 -- Determine the next available period in which to place the
1396 -- correction entry
1397 --
1398 ssp_smp_support_pkg.get_entry_details (
1399 p_date_earned => old_entry.week_commencing,
1400 p_last_process_date => person.final_process_date,
1401 p_person_id => person.person_id,
1402 p_element_type_id => g_SAP_Correction_element.element_type_id,
1403 p_element_link_id => old_entry.element_link_id,
1404 p_assignment_id => old_entry.assignment_id,
1405 p_effective_start_date => old_entry.effective_start_date,
1406 p_effective_end_date => old_entry.effective_end_date,
1407 p_pay_as_lump_sum => person.pay_SAP_as_lump_sum);
1408 --
1409 -- hr_entry_api's take the lookup meanings not the lookup codes.
1410 -- converted rate codes to meanings before calling the
1411 -- api. Later fix made old_entry (csr_existing_entries) return
1412 -- the meaning, so rate passed directly.
1413 --
1414 hr_entry_api.insert_element_entry (
1415 p_effective_start_date=> old_entry.effective_start_date,
1416 p_effective_end_date => old_entry.effective_end_date,
1417 p_element_entry_id => l_dummy,
1418 p_target_entry_id => old_entry.element_entry_id,
1419 p_assignment_id => old_entry.assignment_id,
1420 p_element_link_id => old_entry.element_link_id,
1421 p_creator_type => c_SAP_creator_type,
1422 p_creator_id => p_maternity_id,
1423 p_entry_type => c_SAP_entry_type,
1424 p_input_value_id1=> g_SAP_correction_element.rate_id,
1425 p_input_value_id2=> g_SAP_correction_element.week_commencing_id,
1426 p_input_value_id3=> g_SAP_correction_element.amount_id,
1427 p_input_value_id4=> g_SAP_correction_element.recoverable_amount_id,
1428 p_entry_value1=> old_entry.rate,
1429 -- p_entry_value2=> old_entry.week_commencing,
1430 p_entry_value2 => to_char(old_entry.week_commencing,'DD-MON-YYYY'),
1431 p_entry_value3=> old_entry.amount * -1,
1432 p_entry_value4=> old_entry.recoverable_amount * -1);
1433 --
1434 --New entry will be created by brand_new_entries loop if not p_deleting
1435 end if;
1436 --
1437 end loop old_entries;
1438 --
1439 -- Having been through all the existing entries, we now check that we
1440 -- have dealt with all the newly derived entries by inserting any which
1441 -- were not flagged as dealt with during the above actions.
1442 --
1443 hr_utility.set_location (l_proc,20);
1444 --
1445 <<BRAND_NEW_ENTRIES>>
1446 begin
1447 if p_deleting then
1448 hr_utility.trace('Deleting an absence so don''t insert entries');
1449 else
1450 for new_entry in 1..g_SAP_element.maximum_APP LOOP
1451 if (not hypothetical_entry.dealt_with (new_entry) = 'TRUE')
1452 and (not hypothetical_entry.stopped (new_entry) = 'TRUE')
1453 then
1454 hr_entry_api.insert_element_entry (
1455 p_effective_start_date =>
1456 hypothetical_entry.effective_start_date (new_entry),
1457 p_effective_end_date =>
1458 hypothetical_entry.effective_end_date (new_entry),
1459 p_element_entry_id => l_dummy,
1460 p_assignment_id => hypothetical_entry.assignment_id (new_entry),
1461 p_element_link_id => hypothetical_entry.element_link_id (new_entry),
1462 p_creator_type => c_SAP_creator_type,
1463 p_creator_id => p_maternity_id,
1464 p_entry_type => c_SAP_entry_type,
1465 p_input_value_id1 => g_SAP_element.rate_id,
1466 p_input_value_id2 => g_SAP_element.week_commencing_id,
1467 p_input_value_id3 => g_SAP_element.amount_id,
1468 p_input_value_id4 => g_SAP_element.recoverable_amount_id,
1469 p_entry_value1 => hypothetical_entry.rate (new_entry),
1470 -- p_entry_value2 => hypothetical_entry.week_commencing (new_entry),
1471 p_entry_value2 => to_char(hypothetical_entry.week_commencing(new_entry),'DD-MON-YYYY'),
1472 p_entry_value3 => hypothetical_entry.amount (new_entry),
1473 p_entry_value4 =>
1474 hypothetical_entry.recoverable_amount (new_entry));
1475 end if;
1476 end loop brand_new_entries;
1477 end if;
1478 exception
1479 when no_data_found then
1480 --
1481 -- We have run out of hypothetical entries to insert
1482 --
1483 null;
1484 --
1485 end;
1486 --
1487 -- Orphaned stoppages, associated with deleted absence can now be deleted
1488 -- This replaces cross product constraints that are no longer allowed.
1489 --
1490 open csr_no_of_absences;
1491 fetch csr_no_of_absences into l_no_of_absence_periods;
1492 close csr_no_of_absences;
1493 --
1494 if l_no_of_absence_periods = 0 then
1495 remove_stoppages;
1496 end if;
1497 --
1498 hr_utility.set_location(' Leaving: '||l_proc,100);
1499 --
1500 end save_hypothetical_entries;
1501 --
1502 procedure derive_SAP_week (p_week_number in integer) is
1503 --
1504 -- Derive the start and end dates of the week covered by the SAP
1505 -- payment. This is done by finding out how many weeks into the APP
1506 -- we are and finding the offset from the start date.
1507 --
1508 begin
1509 --
1510 hr_utility.set_location ('Entering: ssp_sap_pkg.derive_SAP_week',1);
1511 hr_utility.trace ('Entry number = '||to_char (p_week_number));
1512 --
1513 hypothetical_entry.week_commencing (p_week_number)
1514 := (person.APP_start_date + ((p_week_number -1) * 7));
1515 --
1516 hypothetical_entry.dealt_with (p_week_number) := 'FALSE';
1517 hypothetical_entry.stopped (p_week_number) := 'FALSE';
1518 hypothetical_entry.element_link_id (p_week_number) := null;
1519 hypothetical_entry.assignment_id (p_week_number) := null;
1520 --
1521 hr_utility.trace ('week_commencing = '
1522 ||to_char (hypothetical_entry.week_commencing (p_week_number)));
1523 --
1524 hr_utility.set_location ('Leaving : ssp_sap_pkg.derive_SAP_week',100);
1525 --
1526 end derive_SAP_week;
1527 --
1528 procedure Check_SAP_stoppages (p_week_number in integer) is
1529 --
1530 -- Find any SAP stoppage for the Adoption which overlaps a date range
1531 --
1532 employee_died varchar2 (30) := 'Employee died';
1533 --
1534 cursor csr_stoppages (p_start_date in date, p_end_date in date) is
1535 --
1536 -- Find any non-overridden stoppages
1537 --
1538 select 1
1539 from ssp_stoppages STP,
1540 ssp_withholding_reasons WRE
1541 where stp.override_stoppage <> 'Y'
1542 --
1543 -- and the stoppage ovelaps the period or the stoppage is for
1544 -- death and is prior to the period
1545 --
1546 and ((wre.reason <> employee_died
1547 and stp.withhold_from <= p_end_date
1548 and nvl (stp.withhold_to, hr_general.end_of_time)
1549 >= p_start_date)
1550 --
1551 or (wre.reason = employee_died
1552 and stp.withhold_from <= p_end_date))
1553 --
1554 and stp.maternity_id = p_maternity_id
1555 and stp.reason_id = wre.reason_id;
1556 --
1557 l_dummy integer (1);
1558 --
1559 begin
1560 --
1561 hr_utility.set_location ('ssp_sap_pkg.Check_SAP_stoppages',1);
1562 --
1563 hypothetical_entry.stopped (p_week_number) := 'FALSE';
1564 --
1565 open csr_stoppages (
1566 hypothetical_entry.week_commencing (p_week_number),
1567 ssp_smp_support_pkg.end_of_week
1568 (hypothetical_entry.week_commencing (p_week_number)));
1569 --
1570 fetch csr_stoppages into l_dummy;
1571 --
1572 if csr_stoppages%found
1573 then
1574 --
1575 -- There is an overlap between the SAP week and a stoppage so no SAP
1576 -- is payable.
1577 --
1578 hypothetical_entry.stopped (p_week_number) := 'TRUE';
1579 --
1580 hr_utility.trace ('Entry is STOPPED');
1581 --
1582 -- Keep a tally of the number of stopped weeks
1583 --
1584 l_stopped_weeks := l_stopped_weeks +1;
1585 end if;
1586 --
1587 close csr_stoppages;
1588 --
1589 hr_utility.set_location ('ssp_sap_pkg.Check_SAP_stoppages',10);
1590 --
1591 end Check_SAP_stoppages;
1592 --
1593 procedure Calculate_correct_SAP_rate (p_week_number in number) is
1594 --
1595 -- The entry API takes the lookup meanings so we must find
1596 -- the meanings rather than the codes for SAP rates.
1597 --
1598 cursor csr_rate_meaning (p_rate_band varchar2) is
1599 --
1600 select meaning
1601 from hr_lookups
1602 where lookup_type = 'SAP_RATES'
1603 and lookup_code = p_rate_band;
1604 --
1605 begin
1606 --
1607 hr_utility.set_location ('ssp_sap_pkg.Calculate_correct_SAP_rate',1);
1608 --
1609 if l_high_rate is null then
1610 --
1611 -- Get the meanings for the rate bands
1612 --
1613 -- Get the higher rate band
1614 --
1615 open csr_rate_meaning ('STD');
1616 fetch csr_rate_meaning into l_high_rate;
1617 close csr_rate_meaning;
1618
1619 end if;
1620 --
1621 /*
1622 if (p_week_number - l_stopped_weeks)
1623 <= g_SMP_element.period_at_higher_rate
1624 then
1625 hr_utility.set_location ('ssp_smp_pkg.Calculate_correct_SMP_rate',1);
1626 --
1627 -- We have not yet given the employee all their higher rate weeks
1628 --
1629 */
1630 hypothetical_entry.rate (p_week_number) := l_high_rate;
1631 /*
1632 else
1633 hypothetical_entry.rate (p_week_number) := l_low_rate;
1634 end if;
1635 */
1636 --
1637 hr_utility.trace ('SAP Rate = '
1638 ||hypothetical_entry.rate (p_week_number));
1639 --
1640 hr_utility.set_location ('ssp_sap_pkg.Calculate_correct_SAP_rate',10);
1641 --
1642 end Calculate_correct_SaP_rate;
1643 --
1644 procedure Calculate_SAP_amounts (p_week_number in integer, p_APP_start_date in date) is
1645 --
1646 begin
1647 --
1648 hr_utility.set_location('Entering: ssp_sAp_pkg.Calculate_SaP_amounts',1);
1649 --
1650 -- Get the SAP element for each week in case the SAP rate has changed
1651 --
1652 get_SAP_element (hypothetical_entry.week_commencing (p_week_number));
1653 --
1654 hypothetical_entry.amount (p_week_number)
1655 := least (round (
1656 (average_earnings * g_SAP_element.SAP_rate)
1657 + 0.0049,2),
1658 g_SAP_element.STANDARD_SAP_RATE);
1659 --
1660 hypothetical_entry.recoverable_amount (p_week_number)
1661 := round (hypothetical_entry.amount (p_week_number)
1662 * g_SAP_element.recovery_rate,2);
1663 --
1664 hr_utility.trace ('SAP amount = '
1665 ||to_char (hypothetical_entry.amount (p_week_number)));
1666 hr_utility.trace ('Recoverable amount = '
1667 ||to_char (hypothetical_entry.recoverable_amount (p_week_number)));
1668 --
1669 hr_utility.set_location('Leaving : ssp_sap_pkg.Calculate_SAP_amounts',100);
1670 --
1671 end calculate_SAP_amounts;
1672 --
1673 procedure check_parameters is
1674 begin
1675 hr_api.mandatory_arg_error (
1676 p_api_name => l_proc,
1677 p_argument => 'maternity_id',
1678 p_argument_value=> p_maternity_id);
1679 --
1680 end check_parameters;
1681 --
1682 begin
1683 --
1684 hr_utility.set_location ('ssp_sap_pkg.generate_payments',1);
1685 --
1686 check_parameters;
1687 --
1688 <<SAP_WEEKS>>
1689 --
1690 if person.APP_start_date is not null then
1691 for week_number in 1..g_SAP_element.maximum_APP
1692 LOOP
1693 --
1694 -- Derive hypothetical entries ie those entries which would be applied for a
1695 -- completely new maternity. Store them internally because we must check
1696 -- previously created entries before applying the hypothetical entries to the
1697 -- database.
1698 --
1699 Derive_SAP_week (week_number);
1700 Check_SAP_stoppages (week_number);
1701 Calculate_correct_SAP_rate (week_number);
1702 Calculate_SAP_amounts (week_number, person.APP_start_date);
1703 --
1704 if (hypothetical_entry.stopped (week_number) = 'FALSE') then
1705 --
1706 -- Get the entry details unless the entry has been stopped (in which case
1707 -- we do not need the entry details and errors may occur if we call the
1708 -- procedure; eg the woman's assignment ends)
1709 --
1710 ssp_smp_support_pkg.get_entry_details (
1711 p_date_earned => hypothetical_entry.week_commencing
1712 (week_number),
1713 p_pay_as_lump_sum => person.pay_SAP_as_lump_sum,
1714 p_last_process_date => person.final_process_date,
1715 p_person_id => person.person_id,
1716 p_element_type_id => g_SAP_element.element_type_id,
1717 p_element_link_id => hypothetical_entry.element_link_id
1718 (week_number),
1719 p_assignment_id => hypothetical_entry.assignment_id
1720 (week_number),
1721 p_effective_start_date => hypothetical_entry.effective_start_date
1722 (week_number),
1723 p_effective_end_date => hypothetical_entry.effective_end_date
1724 (week_number));
1725 end if;
1726 end loop SAP_weeks;
1727 end if;
1728 --
1729 Save_hypothetical_entries(p_deleting);
1730 --
1731 end generate_payments;
1732 --
1733 --------------------------------------------------------------------------------
1734 procedure SAP_control (p_maternity_id in number,
1735 p_deleting in boolean default FALSE) is
1736 --
1737 -- p_deleting parameter added to deal with absences being deleted, without
1738 -- maternity being deleted.
1739 --
1740 cursor csr_maternity is
1741 --
1742 -- Find out if the maternity exists
1743 --
1744 select 1
1745 from ssp_maternities
1746 where maternity_id = p_maternity_id;
1747 --
1748 cursor csr_entries is
1749 --
1750 -- Get all element entries associated with a maternity
1751 --
1752 select /*+ ORDERED use_nl(paa,paaf,etype,entry) */
1753 entry.element_entry_id,
1754 entry.effective_start_date
1755 from per_absence_attendances PAA,
1756 per_all_assignments_f PAAF,
1757 pay_element_entries_f entry
1758 where PAA.maternity_id = p_maternity_id
1759 and PAAF.person_id = PAA.person_id
1760 and entry.creator_type = 'M'
1761 and entry.creator_id = p_maternity_id
1762 and entry.assignment_id = paaf.assignment_id;
1763 --
1764 cursor csr_count_absences is
1765 select count(*)
1766 from ssp_maternities mat,
1767 per_absence_attendances ab
1768 where mat.maternity_id = p_maternity_id
1769 and ab.person_id = mat.person_id
1770 and ab.maternity_id = mat.maternity_id;
1771 --
1772 l_count number;
1773 l_dummy number;
1774 l_proc varchar2 (72) := g_package||'SAP_control';
1775 --
1776 begin
1777 --
1778 hr_utility.set_location (l_proc,1);
1779 --
1780 open csr_maternity;
1781 fetch csr_maternity into l_dummy;
1782 --
1783 if csr_maternity%found then
1784 --
1785 -- Recalculate SAP
1786 --
1787 if entitled_to_SAP (p_maternity_id) then
1788 open csr_count_absences;
1789 fetch csr_count_absences into l_count;
1790 close csr_count_absences;
1791 if l_count > 0 then
1792 generate_payments (p_maternity_id, false);
1793 else
1794 generate_payments (p_maternity_id, p_deleting);
1795 end if;
1796 elsif p_deleting then
1797 -- not entitled but deleting absence then
1798 generate_payments (p_maternity_id, p_deleting);
1799 end if;
1800 else
1801 --
1802 -- The maternity may have been deleted. Remove any element entries associated
1803 -- with it (the absences, stoppages and medicals are handled by constraints).
1804 --
1805 for obsolete in csr_entries LOOP
1806 hr_utility.trace (l_proc||' Deleting element entry_id '||
1807 to_char (obsolete.element_entry_id));
1808 hr_utility.trace (l_proc||'-------------------------------------------');
1809 --
1810 hr_entry_api.delete_element_entry (
1811 p_dt_delete_mode => 'ZAP',
1812 p_session_date => obsolete.effective_start_date,
1813 p_element_entry_id => obsolete.element_entry_id);
1814 end loop;
1815 end if;
1816 --
1817 hr_utility.set_location (l_proc,100);
1818 --
1819 end SAP_control;
1820 --
1821 --------------------------------------------------------------------------------
1822 -- Return the maximum APP date
1823 --
1824 function get_max_SAP_date(p_maternity_id in number) return date is
1825 l_due_date date;
1826 l_mpp_date date;
1827 l_max_mpp number;
1828
1829 cursor get_person_details is
1830 select mpp_start_date, due_date
1831 from ssp_maternities
1832 where maternity_id = p_maternity_id;
1833
1834 cursor get_maximum_mpp is
1835 select to_number(element_information3)
1836 from pay_element_types_f
1837 where element_name = c_SAP_element_name
1838 and l_due_date between effective_start_date and effective_end_date;
1839 begin
1840 open get_person_details;
1841 fetch get_person_details into l_mpp_date, l_due_date;
1842 close get_person_details;
1843
1844 open get_maximum_mpp;
1845 fetch get_maximum_mpp into l_max_mpp;
1846 close get_maximum_mpp;
1847
1848 if l_mpp_date is not null then
1849 return trunc(l_mpp_date + (l_max_mpp * 7));
1850 else
1851 return l_due_date;
1852 end if;
1853 end;
1854 --------------------------------------------------------------------------------
1855 end ssp_SAP_pkg;
[Home] [Help]