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