1 package body SSP_SMP_PKG as
2 /*$Header: spsmpapi.pkb 120.8 2006/12/09 16:49:27 kthampan ship $
3 +==============================================================================+
4 | Copyright (c) 1994 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +==============================================================================+
8 --
9 Name
10 Statutory Maternity Pay Business Process
11 --
12 Purpose
13 To perform calculation of entitlement and payment for SMP purposes
14 --
15 History
16 31 Aug 95 N Simpson Created
17 8 Sep 95 N Simpson Modified procedure
18 check_entitlement_to_SMP and turned it
19 into a function entitled_to_SMP.
20 15 Sep 95 N Simpson Various modifications because of
21 change in ssp_stoppages table.
22 19 Sep 95 N Simpson Modified medical_control parameters.
23 27 Oct 95 N Simpson Renamed all HR_ prefixed objects to
24 SSP_ prefix if they were SSP-specific.
25 2 Nov 95 N Simpson Replaced hrstpapi calls with new style
26 table handler names.
27 Amended derive_SMP_week to initialise
28 hypothetical_entry.element_link_id and
29 hypothetical_entry.assignment_id which
30 were being read by get_entry_details and
31 raising a no_data_found error.
32 Added a few check_parameter procedures.
33 9 Nov 95 N Simpson Removed some obsolete code.
34 16 Nov 95 N Simpson Changed last_standard_process_date
35 to final_process_date throughout as
36 it should have been in the first place.
37 27 Nov 95 N Simpson Converted the SMP rate set for each
38 entry to use the lookup meaning instead
39 of the lookup code.
40 30 Nov 95 N Simpson Changed code creating stoppages for late
41 absence notification. This should ensure
42 that the first absence day is included
43 in the stoppage period calculation
44 5 Dec 95 N Simpson Added handling of average earnings
45 errors.
46 22 Aug 96 C Barbieri Deleted function maternity_leave_exists.
47 With Oracle 7.3.2 it is not possible to
48 reference a function that returns a
49 BOOLEAN inside a SELECT statement.
50
51 DATE AUTHOR VERSION BUG NO DESCRIPTION
52 ---- ------ ------- ------ -----------
53 06 Jan 97 M Fender 434233 Fully qualified call to function within
54 cursor period_of_service with package
55 name. This is a workaround to a bug in
56 PL/SQL which is fixed in version 2.3.3.
57 See related bug 410159 for details.
58
59 04-Feb-97 RThirlby 30.34 447690 Altered function continous_employment-
60 _date to return correct date.
61
62 18-Apr-97 RThirlby 30.35 479378 Cursor csr_personal_details altered to
63 allow for rehired employees - added
64 subquery with max function on date_start
65 12-Dec-97 RThirlby 30.36 590966 Fix.
66 08-Jan-98 RThirlby 30.37 SMP Entries problem solved - on absence
67 delete the entries were not deleted, or
68 correction entries were not created if
69 the entries had been through a payroll
70 run.
71 06-Apr-98 AParkes 30.38 648313 Prevented raising of no_data_found in
72 new_entries block. Changed hr_trace
73 outputs to prevent errors.
74 08-Apr-98 AParkes 30.39 653276 Performance fix to csr_existing_entries
75 cursor; drove from per_assignments using
76 woman.person_id
77 20-Apr-98 AParkes 30.40 647543 Corrected sub-query in cursor
78 csr_existing_entries which prevents
79 selection of correction entries; used
80 base tables not dt views. Made cursor
81 csr_existing_entries return the rate
82 meaning, not code, to simplify api calls
83 and comparisons between old and hypo
84 entry rates. save_hypothetical_entries
85 altered to update existing unprocessed
86 entries covering the same week, but
87 having incorrect amounts.
88 30-JUL-98 A.Myers 30.41 705553 Added date formatting around week-
89 commencing passed in attribute 2 to
90 procedure insert_element_entry (2 calls)
91 31-JUL-98 A.Myers 30.42 701750 Only inserting temp_affected row if it
92 does not already exist (new procedure).
93 01-NOV-99 M.Vilrokx 110.8 960689 Added service.date_start to the cursor
94 csr_personal_details. This date will be
95 passed to ssp_ern_ins.ins in stead of the
96 QW date if the latter is smaller than the
97 service.date_start. When this is not done
98 the ssp_ern_ins.ins procedure will try to
99 validate the QW and raise error 35049
100 because it is before the effective start
101 date of the person.
102 06-JAN-2000 ILeath 110.9 1021179 Remove the stoppage for
103 check_birth_confirmation. A stoppage
104 should not be created if the woman
105 notifies her employer of the date the
106 baby was born.
107 11-AUG-2000 DFoster 110.10 1304683 Amended the average_earnings function so
108 that it calls the nwe version of the
109 calculate_average_earnings which can
110 treat Sicknesses and Maternities
111 differently.
112 30-MAY-2002 SMRobins 115.9 Changes to add changes for APR 2003
113 legislation. Added woman.MPP_start_date
114 to call to Calculate_SMP_amounts, and
115 so extra p_MPP_start_date parameter
116 to Calculate_SMP_amounts procedure.
117 Then code splits within
118 Calculate_SMP_amounts dependent on
119 whether MPP start date before or
120 after 06-APR-2003. The code for prior
121 to 06-APR-2003 can be removed once
122 this date has elapsed.
123 28-OCT-2002 SHVEERAB 115.10 2620611 Do not create stoppage for pre-matured
124 birth of baby.
125 06-NOV-2002 GButler 115.12 2649315 Changes to EXPECTED_WEEK_OF_CONFINEMENT,
126 CONTINUOUS_EMPLOYMENT_DATE
127 functions and CHECK_DEATH procedure to resolve
128 translation issues
129 13-NOV-2002 GButler 115.13 2620413 Change to entitled_to_smp, check on
130 creation of late notification stoppages
131 15-NOV-2002 Bhaskar 115.14 2663735 New procedure check_employment_qw is
132 created to check for employee
133 terminated in qualifying week.
134 15-NOV-2002 Bhaskar 115.14 2663899 Check for employee death in the
135 MPP pay period.
136 22-NOV-2002 BTHAMMIN 115.16 2663899 SMP was paid one week more than
137 required
138 27-JAN-2003 GButler 115.17 nocopy fix to average_earnings - 2nd
139 dummy variable created as placeholder
140 to OUT param from ssp_ern_ins
141 24-FEB-2003 ABlinko 115.18 2811430 Amended csr_maternity in earnings_control
142 for SAP/SPP
143 17-APR-2003 MMAhmad 115.19 2801805 Amended code for Late Absence Notification
144 14-MAY-2003 GButler 115.20 2939058 Changes to csr_period_of_service_qw in
145 check_employment_qw to resolve bug
146 25-AUG-2003 asengar 115.21 3111736 Added to_number to make it compatible for 10g
147 25-FEB-2004 asengar 115.22 3436510 Cursor period_of_service and Procedure
148 3429978 entitled_to_SMP have been modified.
149 29-MAR-2004 skhandwa 115.23 3510141 Added condition to generate Late evidence stoppage
150
151 15-JUN-2004 ssekhar 115.24 3693735 Added to_number to make it compatible for 10g
152 12-JUL-2004 ablinko 115.25 3682122 Changes for recalculating lump sum updates
153 11-OCT-2005 npershad 115.26 4621910 Fixed two problems with date conversion,
154 one in the cursor csr_existing_entries
155 and twice when calling insert_element_entry
156 09-FEB-2006 kthampan 115.27 4891953 Fixed performance bug.
157 21-MAR-2006 kthampan 115.28 5105039 Added function to calculate max SMP paid date.
158 16-JUN-2006 ajeyam 115.29 5210118 In check_continuity_rule procedure,
159 period_of_service cursor changed to fetch the
160 latest start date for rehired persons.
161 23-AUG-2006 kthampan 115.30 5482199 Change from per_people_f and per_assignments_f
162 to per_all_people_f and per_all_assignments_f
163 19-SEP-2006 kthampan 115.31 5547703 Amend smp_control to call generate_payments
164 with insert-mode if absence is > 0 and
165 also change csr_check_if_existing_entries
166 not to reference from per_absence_attendances
167 table
168 19-OCT-2006 kthampan 115.32 5604330 Amend check_death to create the stoppage based
169 on the '7 day rolling weeks', currently it
170 will always created using the next Sunday after
171 the date of death.
172 09-DEC-2006 kthampan 115.33 5706912 Amend procedure ins_ssp_temp_affected_rows_mat
173 and person_control
174 */
175 --------------------------------------------------------------------------------
176 g_package varchar2(33) := ' ssp_smp_pkg.'; -- Global package name
177 --
178 cursor csr_absence_details (p_maternity_id in number) is
179 --
180 -- Get details of maternity leave for a maternity, in chronological
181 -- order of start date
182 --
183 select absence.absence_attendance_id,
184 absence.date_start,
185 nvl (absence.date_end, hr_general.end_of_time) date_end,
186 absence.date_notification,
187 absence.accept_late_notification_flag
188 from per_absence_attendances ABSENCE
189 where absence.maternity_id = p_maternity_id
190 order by absence.date_start;
191 --
192 --------------------------------------------------------------------------------
193 cursor csr_personal_details (p_maternity_id in number) is
194 --
195 -- Get details of the maternal woman
196 --
197 select maternity.person_id,
198 maternity.due_date,
199 ssp_smp_pkg.qualifying_week (due_date) QW,
200 ssp_smp_pkg.expected_week_of_confinement (due_date) EWC,
201 maternity.maternity_id,
202 maternity.actual_birth_date,
203 maternity.live_birth_flag,
204 maternity.start_date_with_new_employer,
205 maternity.MPP_start_date,
206 maternity.notification_of_birth_date,
207 maternity.start_date_maternity_allowance,
208 maternity.pay_SMP_as_lump_sum,
209 person.date_of_death,
210 service.date_start,
211 nvl (service.final_process_date, hr_general.end_of_time)
212 FINAL_PROCESS_DATE
213 from ssp_maternities MATERNITY,
214 per_all_people_f PERSON,
215 per_periods_of_service SERVICE
216 where person.person_id = maternity.person_id
217 and person.person_id = service.person_id
218 and maternity.maternity_id = p_maternity_id
219 --
220 -- Bug 2663899
221 -- When employee is terminated, the person.date_of_death
222 -- is null in the old record. Check the dates with
223 -- actual termination date+1
224 --
225 and nvl(service.actual_termination_date+1,service.date_start)
226 between person.effective_start_date
227 and person.effective_end_date
228 and service.date_start = (select max(serv.date_start)
229 from per_periods_of_service serv
230 where serv.person_id = person.person_id);
231 --
232 --------------------------------------------------------------------------------
233 woman csr_personal_details%rowtype;
234 g_SMP_element csr_SMP_element_details%rowtype;
235 g_SMP_Correction_element csr_SMP_element_details%rowtype;
236 --------------------------------------------------------------------------------
237 procedure get_SMP_correction_element (p_effective_date in date) is
238 --
239 l_proc varchar2(72) := g_package||'get_SMP_correction_element';
240 --
241 procedure check_parameters is
242 begin
243 --
244 hr_utility.trace (l_proc||' p_effective_date = '
245 ||to_char (p_effective_date));
246 --
247 hr_api.mandatory_arg_error (
248 p_api_name => l_proc,
249 p_argument => 'effective_date',
250 p_argument_value=> p_effective_date);
251 end check_parameters;
252 --
253 begin
254 --
255 hr_utility.set_location (l_proc,1);
256 --
257 check_parameters;
258 --
259 open csr_SMP_element_details (p_effective_date,c_SMP_Corr_element_name);
260 fetch csr_SMP_element_details into g_SMP_Correction_element;
261
262 --hr_utility.trace('g_SMP_Correction_element'||g_SMP_Correction_element);
263
264 close csr_SMP_element_details;
265 --
266 hr_utility.set_location (l_proc,100);
267 --
268 end get_SMP_correction_element;
269 --
270 --------------------------------------------------------------------------------
271 procedure get_SMP_element (p_effective_date in date) is
272 --
273 l_proc varchar2(72) := g_package||'get_SMP_element';
274 --
275 procedure check_parameters is
276 begin
277 --
278 hr_utility.trace (l_proc||' p_effective_date = '
279 ||to_char (p_effective_date));
280 --
281 hr_api.mandatory_arg_error (
282 p_api_name => l_proc,
283 p_argument => 'effective_date',
284 p_argument_value=> p_effective_date);
285 end check_parameters;
286 --
287 begin
288 --
289 hr_utility.set_location (l_proc,1);
290 --
291 check_parameters;
292 --
293 open csr_SMP_element_details (p_effective_date,c_SMP_element_name);
294 fetch csr_SMP_element_details into g_SMP_element;
295 close csr_SMP_element_details;
296 --
297 hr_utility.set_location (l_proc,100);
298 --
299 end get_SMP_element;
300 --
301 --------------------------------------------------------------------------------
302 function EXPECTED_WEEK_OF_CONFINEMENT
303 --
304 -- Returns the date on which the EWC starts
305 --
306 (p_due_date in date)
307 --
308 -- Due date comes from the woman's maternity record
309 --
310 return date is
311 --
312 -- EWC is the Sunday prior to the due date
313 --
314 -- l_EWC date := (next_day (p_due_date,'SUNDAY') -7);
315 --
316 l_EWC date;
317 l_sunday varchar2(100) := to_char(to_date('07/01/2001','DD/MM/YYYY'),'DAY');
318
319
320 begin
321 --
322 hr_utility.set_location(g_package||'EXPECTED_WEEK_OF_CONFINEMENT',1);
323
324 l_EWC := (next_day (p_due_date,l_sunday) -7);
325
326 hr_utility.trace('l_EWC: '||l_EWC);
327
328 hr_utility.set_location(g_package||'EXPECTED_WEEK_OF_CONFINEMENT',99);
329
330 return l_EWC;
331 --
332 end expected_week_of_confinement;
333 --
334 --------------------------------------------------------------------------------
335 function QUALIFYING_WEEK
336 --
337 -- Returns the start date of the QW
338 --
339 (p_due_date in date)
340 --
341 return date is
342 --
343 -- QW is the EWC minus the QW weeks from the SMP element
344 --
345 l_QW date;
346 l_SMP_element csr_SMP_element_details%rowtype;
347 --
348 begin
349 --
350 open csr_SMP_element_details (p_due_date, c_SMP_element_name);
351 fetch csr_SMP_element_details into l_SMP_element;
352 close csr_SMP_element_details;
353 --
354 l_QW := expected_week_of_confinement (p_due_date)
355 - (l_SMP_element.qualifying_week);
356 return l_QW;
357 --
358 end qualifying_week;
359 --
360 --------------------------------------------------------------------------------
361 function EARLIEST_MPP_START_DATE (p_due_date in date) return date is
362 --
363 -- The earliest MPP start date, under normal circumstances is the EWC minus
364 -- the number of weeks specified on the SMP element for earliest SMP start
365 --
366 l_earliest_MPP_start date;
367 l_SMP_element csr_SMP_element_details%rowtype;
368 --
369 begin
370 --
371 open csr_SMP_element_details (p_due_date,c_SMP_element_name);
372 fetch csr_SMP_element_details into l_SMP_element;
373 close csr_SMP_element_details;
374 --
375 l_earliest_MPP_start := Expected_Week_of_Confinement (p_due_date)
376 - (l_SMP_element.earliest_start_of_MPP);
377 return l_earliest_MPP_start;
378 --
379 end earliest_MPP_start_date;
380 --
381 --------------------------------------------------------------------------------
382 function CONTINUOUS_EMPLOYMENT_DATE (p_due_date in date) return date is
383 --
384 -- The continuous employment start date is the date on which the woman must
385 -- have been employed (and continuously from then to the QW) in order to
386 -- qualify for SMP. It is the QW minus the continuous employment period
387 -- specified on the SMP element.
388 -- Bug 447690 a woman must have started work on or before the last day of the
389 -- week which starts 182 days (26 weeks) before the last day of the QW. In
390 -- SMP weeks start on Sunday and end on Saturday.
391 --
392 l_SMP_element csr_SMP_element_details%rowtype;
393 l_Continuously_employed_since date;
394
395 l_saturday varchar2(100) := to_char(to_date('06/01/2001','DD/MM/YYYY'),'DAY');
396
397 --
398 begin
399 --
400 hr_utility.set_location(g_package||'CONTINUOUS_EMPLOYMENT_DATE',1);
401
402 open csr_SMP_element_details (p_due_date,c_SMP_element_name);
403 fetch csr_SMP_element_details into l_SMP_element;
404 close csr_SMP_element_details;
405
406 hr_utility.set_location(g_package||'CONTINUOUS_EMPLOYMENT_DATE',2);
407 --
408
409 l_Continuously_employed_since :=
410 next_day(next_day(Qualifying_Week (p_due_date),l_saturday)
411 - (l_SMP_element.continuous_employment_period),l_saturday);
412
413 hr_utility.trace('l_Continuously_employed_since: '||l_Continuously_employed_since);
414
415 hr_utility.set_location(g_package||'CONTINUOUS_EMPLOYMENT_DATE',99);
416 --
417 return l_Continuously_employed_since;
418 --
419 end continuous_employment_date;
420 --
421 --------------------------------------------------------------------------------
422 function MATERNITY_RECORD_EXISTS (p_person_id in number) return boolean is
423 --
424 cursor maternity_record is
425 select 1
426 from ssp_maternities
427 where person_id = p_person_id;
428 --
429 l_dummy number (1);
430 l_maternity_record_exists boolean;
431 --
432 begin
433 --
434 open maternity_record;
435 fetch maternity_record into l_dummy;
436 l_maternity_record_exists := maternity_record%found;
437 close maternity_record;
438 --
439 return l_maternity_record_exists;
440 --
441 end maternity_record_exists;
442 --
443 --------------------------------------------------------------------------------
444 function AVERAGE_EARNINGS return number is
445 --
446 l_average_earnings number := null;
447 l_effective_date date := null;
448 l_dummy number;
449 l_dummy2 number; -- nocopy fix, placeholder variable
450 l_user_entered varchar2(30) := 'N'; -- DFoster 1304683
451 l_absence_category varchar2(30) := 'M'; --DFoster 1304683
452 l_payment_periods number := null; --DFoster 1304683
453 l_proc varchar2(72) := g_package||'average_earnings';
454 --
455 cursor csr_average_earnings is
456 select average_earnings_amount
457 from ssp_earnings_calculations
458 where person_id = woman.person_id
459 and effective_date = l_effective_date;
460 --
461 begin
462 --
463 hr_utility.set_location ('Entering '||l_proc,1);
464 --
465 if woman.actual_birth_date is not null then
466 l_effective_date := least (greatest(woman.QW, woman.date_start), ssp_smp_support_pkg.start_of_week
467 (woman.actual_birth_date));
468 else
469 l_effective_date := greatest(woman.QW, woman.date_start);
470 end if;
471 --
472 open csr_average_earnings;
473 fetch csr_average_earnings into l_average_earnings;
474 if csr_average_earnings%notfound
475 then
476 ssp_ern_ins.ins (p_earnings_calculations_id => l_dummy,
477 p_object_version_number => l_dummy2,
478 p_person_id => woman.person_id,
479 p_effective_date => l_effective_date,
480 p_average_earnings_amount => l_average_earnings,
481 p_user_entered => l_user_entered, --DFoster 1304683
482 p_absence_category => l_absence_category, --DFoster 1304683
483 p_payment_periods => l_payment_periods); --DFoster 1304683
484 end if;
485 --
486 close csr_average_earnings;
487 --
488 hr_utility.set_location ('Leaving '||l_proc,10);
489 --
490 return l_average_earnings;
491 --
492 end average_earnings;
493 --------------------------------------------------------------------------------
494 function entitled_to_SMP (p_maternity_id in number) return boolean is
495 --
496 -- See header for description of this procedure.
497 --
498 no_prima_facia_entitlement exception;
499 invalid_absence_date exception;
500 l_work_start_date date := hr_general.end_of_time;
501 stoppage_end_date date := null;
502 no_of_absence_periods integer := 0;
503 l_proc varchar2(72) := g_package||'entitled_to_SMP';
504 l_keep_stoppages boolean default FALSE;
505 --
506 cursor csr_no_of_absences is
507 --
508 -- Get the number of distinct absences within a maternity pay period
509 --
510 select count (*)
511 from per_absence_attendances
512 where person_id = woman.person_id
513 and maternity_id = p_maternity_id;
514 --
515 -- returns entries associated with a maternity_id.
516 cursor csr_check_if_existing_entries is
517 --
518 select /*+ ORDERED use_nl(paa,paaf,etype,entry) */
519 entry.element_entry_id,
520 entry.effective_start_date
521 from per_all_assignments_f PAAF,
522 pay_element_types_f ETYPE,
523 pay_element_entries_f ENTRY
524 where PAAF.person_id = woman.person_id
525 and ETYPE.element_name = c_SMP_element_name
526 and ETYPE.legislation_code = 'GB'
527 and ENTRY.element_type_id = ETYPE.element_type_id
528 and entry.creator_type = c_SMP_creator_type
529 and entry.creator_id = p_maternity_id
530 and entry.assignment_id = PAAF.assignment_id
531 and not exists (
532 --
533 -- Do not select entries which have already had reversal action
534 -- taken against them because they are effectively cancelled out.
535 --
536 select 1
537 from pay_element_entries_f ENTRY2
538 where entry.element_entry_id=entry2.target_entry_id
539 and entry.assignment_id = entry2.assignment_id)
540 --
541 and not exists (
542 --
543 -- Do not select reversal entries
544 --
545 select 1
546 from pay_element_links_f LINK,
547 pay_element_types_f TYPE
548 where link.element_link_id = entry.element_link_id
549 and entry.effective_start_date between link.effective_start_date and link.effective_end_date
550 and link.element_type_id = type.element_type_id
551 and link.effective_start_date between type.effective_start_date and type.effective_end_date
552 and type.element_name = c_SMP_Corr_element_name);
553 --
554 l_existing_entries csr_check_if_existing_entries%rowtype;
555 --
556 procedure create_stoppage (
557 --
558 -- Create a stoppage of payment for SMP
559 --
560 p_reason in varchar2,
561 p_withhold_from in date,
562 p_withhold_to in date default null
563 ) is
564 --
565 l_proc varchar2(72) := g_package||'create_stoppage';
566 l_dummy number;
567 l_reason_id number;
568 --
569 procedure check_parameters is
570 --
571 begin
572 --
573 hr_utility.trace (l_proc||' p_reason = '||p_reason);
574 hr_utility.trace (l_proc||' withhold from '
575 ||to_char (p_withhold_from));
576 hr_utility.trace (l_proc||' withhold to '
577 ||to_char (p_withhold_to));
578 --
579 hr_api.mandatory_arg_error (
580 p_api_name => l_proc,
581 p_argument => 'reason',
582 p_argument_value=> p_reason);
583 --
584 hr_api.mandatory_arg_error (
585 p_api_name => l_proc,
586 p_argument => 'withhold_from',
587 p_argument_value=> p_withhold_from);
588 --
589 end check_parameters;
590 --
591 begin
592 --
593 hr_utility.set_location (l_proc,1);
594 --
595 check_parameters;
596 --
597 l_reason_id := ssp_smp_support_pkg.withholding_reason_id (
598 g_SMP_element.element_type_id,
599 p_reason);
600 --
601 if not ssp_smp_support_pkg.stoppage_overridden (
602 p_maternity_id => p_maternity_id,
603 p_reason_id => l_reason_id)
604 then
605 --
606 -- Only create the stoppage if there is not already a stoppage marked
607 -- as overridden. Thus, overriding a stoppage effectively blocks that
608 -- reason being used to withhold payment for this person.
609 --
610 ssp_stp_ins.ins (p_withhold_from => p_withhold_from,
611 p_withhold_to => p_withhold_to,
612 p_stoppage_id => l_dummy,
613 p_object_version_number => l_dummy,
614 p_maternity_id => p_maternity_id,
615 p_user_entered => 'N',
616 p_reason_id => l_reason_id);
617 else
618 hr_utility.trace (l_proc||' Stoppage is overridden');
619 end if;
620 --
621 hr_utility.set_location (l_proc,100);
622 --
623 end create_stoppage;
624 --
625 procedure remove_stoppages is
626 --
627 -- Remove old system, non-overridden stoppages
628 --
629 cursor csr_stoppages is
630 select stoppage_id
631 from ssp_stoppages
632 where user_entered <>'Y'
633 and override_stoppage <> 'Y'
634 and maternity_id = p_maternity_id;
635 --
636 l_dummy number;
637 l_proc varchar2 (72) := g_package||'remove_stoppages';
638 --
639 begin
640 --
641 hr_utility.set_location (l_proc,1);
642 --
643 for each_stoppage in csr_stoppages LOOP
644 ssp_stp_del.del (p_stoppage_id => each_stoppage.stoppage_id,
645 p_object_version_number => l_dummy);
646 end loop;
647 --
648 hr_utility.set_location (l_proc,100);
649 --
650 end remove_stoppages;
651 --
652 procedure check_continuity_rule is
653 --
654 -- Check that the woman has the right amount of continuous service to
655 -- qualify for SMP
656 --
657 cursor period_of_service is
658 --
659 -- Check the period of service length up to the QW start date
660 --
661 -- 5210118 starts
662 select 1
663 from per_periods_of_service
664 where person_id = woman.person_id
665 and ssp_smp_pkg.continuous_employment_date(woman.due_date) >=
666 (select max(date_start)
667 from per_periods_of_service
668 where person_id = woman.person_id
669 );
670 -- 5210118 ends
671 --and nvl (actual_termination_date, hr_general.end_of_time) -- BUG 3436510
672 -- >= woman.QW;
673 --
674 l_dummy number (1);
675 l_proc varchar2 (72) := g_package||'check_continuity_rule';
676 --
677 begin
678 --
679 hr_utility.set_location (l_proc,1);
680 --
681 open period_of_service;
682 fetch period_of_service into l_dummy;
683 --
684 if period_of_service%notfound then
685 --
686 -- Stop all SMP payment for the maternity because the woman has not
687 -- been continuously employed for long enough.
688 --
689 create_stoppage (p_withhold_from => woman.MPP_start_date,
690 p_reason => 'Insufficient employment');
691 end if;
692 --
693 close period_of_service;
694 --
695 hr_utility.set_location (l_proc,100);
696 --
697 end check_continuity_rule;
698 --
699 --
700 -- New procedure for bug 2663735
701 --
702 procedure check_employment_qw is
703 --
704 -- retrieve period of service details relating to this maternity
705 --
706 cursor csr_period_of_service_qw is
707 select nvl(ser.actual_termination_date, hr_general.end_of_time) termination_date
708 ,leaving_reason leaving_reason
709 from per_periods_of_service ser
710 where ser.person_id = woman.person_id
711 and ssp_smp_pkg.continuous_employment_date(woman.due_date)
712 between ser.date_start and nvl(ser.actual_termination_date, hr_general.end_of_time);
713 --
714 l_proc varchar2(72) := g_package||'check_employment_qw';
715 l_termination_date per_periods_of_service.actual_termination_date%type;
716 l_leaving_reason hr_lookups.meaning%type;
717 --
718 -- Local Function
719 function get_leaving_reason(p_leaving_reason in varchar2)
720 return varchar2 is
721 cursor csr_leaving_reason is
722 select upper(meaning)
723 from hr_lookups
724 where lookup_type = 'LEAV_REAS'
725 and lookup_code = p_leaving_reason
726 and enabled_flag = 'Y' ;
727 --
728 l_leaving_reason hr_lookups.meaning%type;
729 begin
730 open csr_leaving_reason;
731 fetch csr_leaving_reason into l_leaving_reason;
732 close csr_leaving_reason;
733 --
734 return nvl(l_leaving_reason,'-1');
735 end get_leaving_reason;
736 --
737 begin
738 hr_utility.set_location('Entering : '||l_proc,1);
739 --
740 open csr_period_of_service_qw;
741 fetch csr_period_of_service_qw into l_termination_date
742 ,l_leaving_reason ;
743 close csr_period_of_service_qw;
744 --
745
746 l_leaving_reason := get_leaving_reason(l_leaving_reason);
747 --
748 hr_utility.set_location(l_proc,2);
749 --
750 --
751 -- Bug 2663735
752 -- For current regulations (due date less than 06-APR-2003)
753 -- If an employee is terminated in between QW and Due Date
754 -- she is not entitled to SMP.
755 -- But the employee gets complete SMP according to the
756 -- new regulations (due date after 06-APR-2003)
757 --
758 if woman.due_date >= fnd_date.canonical_to_date('2003/04/06 00:00:00') then
759 hr_utility.set_location(l_proc,3);
760 if l_termination_date < woman.qw then
761 hr_utility.set_location(l_proc,4);
762 create_stoppage(p_withhold_from => l_termination_date + 1
763 ,p_reason => 'Not employed in QW' );
764 end if;
765 else
766 hr_utility.set_location(l_proc,5);
767 if (l_termination_date
768 between woman.qw
769 and nvl(woman.MPP_start_date
770 ,ssp_smp_pkg.earliest_mpp_start_date(woman.due_date)) )
771 and
772 (l_leaving_reason <> 'MATERNITY')
773 then
774 hr_utility.set_location(l_proc,6);
775 create_stoppage(p_withhold_from => l_termination_date + 1
776 ,p_reason => 'Insufficient employment' );
777 elsif l_termination_date < woman.qw then
778 hr_utility.set_location(l_proc,7);
779 create_stoppage(p_withhold_from => l_termination_date + 1
780 ,p_reason => 'Not employed in QW' );
781 end if;
782 hr_utility.set_location(l_proc,8);
783 end if;
784 --
785 hr_utility.set_location('Leaving : '||l_proc,9);
786 --
787 end check_employment_qw;
788 --
789 procedure check_stillbirth is
790 --
791 -- Check the pregnancy condition for qualification for SMP
792 --
793 l_proc varchar2 (72) := g_package||'check_stillbirth';
794 --
795 begin
796 --
797 hr_utility.set_location (l_proc,1);
798 --
799 -- Woman must be still pregnant, have had a live birth, or have had a
800 -- stillbirth after the threshhold week to be eligible for SMP
801 --
802 if NOT (woman.actual_birth_date is null
803 or woman.live_birth_flag = 'Y'
804 or woman.actual_birth_date > woman.EWC
805 - g_SMP_element.stillbirth_threshhold_week)
806 then
807 --
808 -- Stop SMP payment from the start of the week in which the absence
809 -- started.
810 --
811 create_stoppage (p_withhold_from => woman.MPP_start_date,
812 p_reason => 'Stillbirth');
813 end if;
814 --
815 hr_utility.set_location (l_proc,100);
816 --
817 end check_stillbirth;
818 --
819 procedure check_new_employer is
820 --
821 -- Check the woman has not been employed by a new employer after the
822 -- birth of her child
823 --
824 l_proc varchar2 (72) := g_package||'check_new_employer';
825 --
826 begin
827 --
828 hr_utility.set_location (l_proc,1);
829 --
830 if woman.start_date_with_new_employer >= woman.actual_birth_date then
831 --
832 -- Stop SMP payment from the start of the week in which the woman
833 -- started work for a new employer after the birth of her child.
834 --
835 create_stoppage (p_withhold_from => ssp_smp_support_pkg.start_of_week
836 (woman.start_date_with_new_employer),
837 p_reason => 'Worked for another employer');
838 end if;
839 --
840 hr_utility.set_location (l_proc,100);
841 --
842 end check_new_employer;
843 --
844 procedure check_maternity_allowance is
845 --
846 -- SMP ceases when SMA starts.
847 --
848 l_proc varchar2 (72) := g_package||'check_maternity_allowance';
849 --
850 begin
851 --
852 hr_utility.set_location (l_proc,1);
853 --
854 if woman.start_date_maternity_allowance is not null then
855 --
856 -- Stop SMP payment from the start of the week in which SMA was first
857 -- paid.
858 --
859 create_stoppage (p_withhold_from => ssp_smp_support_pkg.start_of_week
860 (woman.start_date_maternity_allowance),
861 p_reason => 'Employee is receiving SMA');
862 end if;
863 --
864 hr_utility.set_location (l_proc,100);
865 --
866 end check_maternity_allowance;
867 --
868 procedure check_death is
869 --
870 -- SMP ceases after the death of the woman.
871 --
872 l_proc varchar2 (72) := g_package||'check_death';
873 l_sunday varchar2(100)
874 := to_char(to_date('07/01/2001','DD/MM/YYYY'),'DAY');
875 --
876 -- Bug 2663899 Start
877 --
878 mpp_pay_period_end date;
879 current_mpp_period number := 126;
880 new_mpp_period number := 182;
881 mpp_start_date date := nvl(woman.MPP_start_date
882 ,ssp_smp_pkg.earliest_mpp_start_date
883 (woman.due_date));
884 -- Bug 2663899 End
885 cursor csr_get_week_day is
886 select to_char(mpp_start_date,'DAY')
887 from dual;
888 --
889 l_day_of_the_week varchar2(20);
890 --
891 begin
892 --
893 hr_utility.set_location (l_proc,1);
894 --
895 if woman.date_of_death is not null then
896 --
897 -- SMP ceases on the Saturday following death
898 --
899 -- Bug 2663899 start
900 /*
901 if woman.due_date >= fnd_date.canonical_to_date('2003/04/06 00:00:00')
902 then
903 mpp_pay_period_end := mpp_start_date + new_mpp_period;
904 else
905 mpp_pay_period_end := mpp_start_date + current_mpp_period;
906 end if;
907 */
908 mpp_pay_period_end := mpp_start_date + (g_SMP_element.maximum_mpp * 7);
909 --
910 if woman.date_of_death between mpp_start_date
911 and mpp_pay_period_end then
912
913 open csr_get_week_day;
914 fetch csr_get_week_day into l_day_of_the_week;
915 close csr_get_week_day;
916 -- Instead of using the next sunday, the code will use the '7-day rolling week'
917 -- method where if the MPP start on Wednesday, the week will end on Tuesday
918 -- We can assume that the MPP start date is correct as the validation is done
919 -- when the user entered the absence/maternity record.
920 create_stoppage (p_withhold_from => next_day (woman.date_of_death,
921 l_day_of_the_week) --l_sunday)
922 ,p_reason => 'Employee died');
923 end if;
924 -- Bug 2663899 end
925 end if;
926 --
927 hr_utility.set_location (l_proc,100);
928 --
929 end check_death;
930 --
931 procedure check_average_earnings is
932 --
933 -- The woman must earn enough to qualify for SMP
934 --
935 l_proc varchar2 (72) := g_package||'check_average_earnings';
936 l_average_earnings number := average_earnings;
937 l_reason_for_no_earnings varchar2 (80) := null;
938 earnings_not_derived exception;
939 --
940 begin
941 --
942 hr_utility.set_location (l_proc,1);
943 --
944 if l_average_earnings = 0
945 then
946 --
947 -- If the average earnings figure returned is zero then check that
948 -- no error message was set. Error messages will be set for system-
949 -- generated average earnings when the earnings could not be derived
950 -- for some reason, but to allow this procedure to continue, no error
951 -- will be raised.
952 --
953 l_reason_for_no_earnings:=ssp_smp_support_pkg.average_earnings_error;
954 --
955 if l_reason_for_no_earnings is not null then
956 create_stoppage (p_withhold_from => woman.MPP_start_date,
957 p_reason => l_reason_for_no_earnings);
958 --
959 raise earnings_not_derived;
960 end if;
961 end if;
962 --
963 if l_average_earnings
964 < ssp_smp_support_pkg.NI_Lower_Earnings_Limit (woman.QW)
965 then
966 --
967 -- Stop SMP payment from the MPP start date
968 --
969 create_stoppage (p_withhold_from => woman.MPP_start_date,
970 p_reason => 'Earnings too low');
971 end if;
972 --
973 hr_utility.set_location (l_proc,100);
974 --
975 exception
976 --
977 when earnings_not_derived then
978 --
979 -- Exit silently from this procedure
980 --
981 hr_utility.trace (l_proc||' Earnings not derived');
982 null;
983 --
984 end check_average_earnings;
985 --
986 procedure check_medical_evidence is
987 --
988 -- Check the acceptability of the maternity evidence
989 --
990 cursor medical is
991 select *
992 from ssp_medicals
993 where maternity_id = woman.maternity_id
994 and evidence_status = 'CURRENT';
995 --
996 l_proc varchar2 (72) := g_package||'check_medical_evidence';
997 l_medical medical%rowtype;
998 --
999 begin
1000 --
1001 hr_utility.set_location (l_proc,1);
1002 --
1003 open medical;
1004 fetch medical into l_medical;
1005 --
1006 if medical%notfound -- no medical evidence recorded
1007 or (medical%found and
1008 --
1009 -- evidence is dated too early
1010 --
1011 (l_medical.evidence_date < woman.EWC
1012 - g_SMP_element.earliest_SMP_evidence)
1013 --
1014 -- evidence was received late for no good reason
1015 --
1016 or (l_medical.evidence_received_date > woman.MPP_start_date
1017 + g_SMP_element.latest_SMP_evidence
1018 and l_medical.accept_late_evidence_flag = 'N')
1019 --
1020 -- evidence was received late, even after extension allowed
1021 --
1022 or (l_medical.evidence_received_date > woman.MPP_start_date
1023 + g_SMP_element.extended_SMP_evidence)
1024 --
1025 -- Added condition for bug 3510141
1026 -- evidence was received late, after mpp_notice_requirement_period
1027 -- Bug 3693735 Added to_number to make the code compatible with 10g
1028 or (l_medical.evidence_received_date > woman.MPP_start_date
1029 - to_number(g_SMP_element.mpp_notice_requirement)))
1030 then
1031 --
1032 -- Stop SMP payment from the start of the week in which the MPP
1033 -- started.
1034 --
1035 create_stoppage (p_withhold_from => woman.MPP_start_date,
1036 p_reason => 'Late/unacceptable evidence');
1037 end if;
1038 --
1039 close medical;
1040 --
1041 hr_utility.set_location (l_proc,100);
1042 --
1043 end check_medical_evidence;
1044 --
1045 procedure check_birth_confirmation is
1046 --
1047 -- Check that confirmation of birth was received in good time.
1048 --
1049 l_proc varchar2 (72) := g_package||'check_birth_confirmation';
1050 --
1051 begin
1052 --
1053 hr_utility.set_location (l_proc,1);
1054 --
1055 --
1056 -- This should not create a stoppage. A quick fix for bug 1021179
1057 -- is to comment out the stoppage process.
1058 --
1059 null;
1060
1061 -- if (woman.actual_birth_date is not null
1062 -- and (nvl (woman.notification_of_birth_date, sysdate)
1063 -- > woman.actual_birth_date
1064 -- + g_SMP_element.notice_of_birth_requirement))
1065 -- then
1066 --
1067 -- Stop SMP payment from the start of the week in which the MPP
1068 -- started.
1069 --
1070 -- create_stoppage (p_withhold_from => woman.MPP_start_date,
1071 -- p_reason => 'Late notification of birth');
1072 -- end if;
1073 --
1074 hr_utility.set_location (l_proc,100);
1075 --
1076 end check_birth_confirmation;
1077 --
1078 procedure check_parameters is
1079 --
1080 begin
1081 --
1082 hr_utility.trace (l_proc||' p_maternity_id = '
1083 ||to_char (p_maternity_id));
1084 --
1085 hr_api.mandatory_arg_error (
1086 p_api_name => l_proc,
1087 p_argument => 'maternity_id',
1088 p_argument_value=> p_maternity_id);
1089 --
1090 end check_parameters;
1091 --
1092 begin
1093 --
1094 hr_utility.set_location (l_proc,1);
1095 --
1096 check_parameters;
1097 --
1098 -- Get the details of the woman and her maternity.
1099 --
1100 open csr_personal_details (p_maternity_id);
1101 fetch csr_personal_details into woman;
1102 --
1103 if csr_personal_details%notfound
1104 then
1105 --
1106 -- If no maternity record exists then there can be no entitlement to SMP
1107 --
1108 close csr_personal_details;
1109 --
1110 hr_utility.trace (l_proc||' Woman has no maternity record - exiting');
1111 --
1112 raise no_prima_facia_entitlement;
1113 end if;
1114 --
1115 close csr_personal_details;
1116 --
1117 if woman.MPP_start_date is null then
1118 --
1119 -- If the MPP has not started then there is no entitlement to SMP.
1120 --
1121 hr_utility.trace (l_proc||' Woman has no MPP start date - exiting');
1122 --
1123 raise no_prima_facia_entitlement;
1124 end if;
1125 --
1126 -- Count how many absences there are for the maternity.
1127 --
1128 open csr_no_of_absences;
1129 fetch csr_no_of_absences into no_of_absence_periods;
1130 close csr_no_of_absences;
1131 --
1132 if no_of_absence_periods = 0
1133 then
1134 --
1135 -- check if entries exist despite there being no absence
1136 --
1137 open csr_check_if_existing_entries;
1138 fetch csr_check_if_existing_entries into l_existing_entries;
1139 --
1140 if csr_check_if_existing_entries%NOTFOUND
1141 then
1142 hr_utility.trace (l_proc||' Woman has not stopped work - exiting');
1143 raise no_prima_facia_entitlement;
1144 end if;
1145 --
1146 -- if entries are found then the absence has been deleted and entries remain
1147 -- that must be dealt with
1148 --
1149 while csr_check_if_existing_entries%FOUND LOOP
1150 fetch csr_check_if_existing_entries into l_existing_entries;
1151 end loop;
1152 --
1153 close csr_check_if_existing_entries;
1154 l_keep_stoppages := TRUE;
1155 end if;
1156 --
1157 -- Having established a prima facia entitlement to SMP, perform checks which
1158 -- may lead to creation of stoppages for particular periods.
1159 --
1160 hr_utility.set_location ('ssp_smp_pkg.entitled_to_SMP',2);
1161 --
1162 -- Get the SMP legislative parameters.
1163 --
1164 get_SMP_element (woman.due_date);
1165 --
1166 -- Clear stoppages created by previous calculations of SMP but if an absence
1167 -- is being deleted, then must keep stoppages so that when later comparison of
1168 -- old_entry and hypothetical_entry is done then stoppages are still there.
1169 --
1170 if not l_keep_stoppages then
1171 remove_stoppages;
1172 end if;
1173 --
1174 for absence in csr_absence_details (p_maternity_id) LOOP
1175 --
1176 -- Check that sufficient notification of absence was given
1177 --
1178
1179 if
1180 -- for pre-matured birth of baby, Bug 2620611
1181 -- Bug 2620413, check notified date of absence is less than
1182 -- or equal to the actual birth date + 21/28 days for cases
1183 -- of premature birth (extended allowable date for notifications rule)
1184 --
1185 (woman.actual_birth_date < woman.due_date
1186 and absence.date_notification > woman.actual_birth_date
1187 + g_SMP_element.MPP_notice_requirement)
1188 --
1189 or (absence.date_notification > absence.date_start
1190 - to_number(g_SMP_element.MPP_notice_requirement)
1191 -- BUG 3111736 Added to_number to make it compatible for 10g
1192 -- and there was no acceptable reason for the delay
1193 and absence.accept_late_notification_flag = 'N'
1194 -- and baby was not born prematurely
1195 and nvl(woman.actual_birth_date,hr_general.end_of_time) >= woman.due_date )
1196 then
1197 --
1198 -- Stop SMP payment from the start of the week in which the absence
1199 -- starts, to the end of the notice period
1200 --
1201 stoppage_end_date := g_SMP_element.MPP_notice_requirement
1202 + absence.date_start - 1;
1203 --
1204 create_stoppage (
1205 --p_withhold_from => ssp_smp_support_pkg.start_of_week
1206 -- (absence.date_start),
1207 -- p_withhold_to => ssp_smp_support_pkg.end_of_week (stoppage_end_date),
1208 p_withhold_from => woman.MPP_start_date,
1209 p_reason => 'Late absence notification');
1210 end if;
1211 --
1212 hr_utility.set_location ('ssp_smp_pkg.entitled_to_SMP',3);
1213 --
1214 -- Check for any work done during the MPP.
1215 --
1216 if
1217 -- this is the first absence period in the MPP
1218 csr_absence_details%rowcount = 1
1219 --
1220 -- and the absence starts after the MPP start date
1221 and absence.date_start > woman.MPP_start_date
1222 then
1223 create_stoppage (p_reason => 'Some work was done',
1224 p_withhold_from => woman.MPP_start_date,
1225 p_withhold_to => ssp_smp_support_pkg.end_of_week
1226 (absence.date_start -1));
1227 end if;
1228 --
1229 if
1230 -- this is the last absence period in the MPP
1231 csr_absence_details%rowcount = no_of_absence_periods
1232 --
1233 -- and the absence period ends before the end of the MPP
1234 and absence.date_end < (g_SMP_element.maximum_MPP * 7)
1235 + woman.MPP_start_date
1236 and woman.date_of_death is null -- Added the condition 3429978
1237 then
1238 create_stoppage (p_reason => 'Some work was done',
1239 p_withhold_from => ssp_smp_support_pkg.start_of_week
1240 (absence.date_end+1));
1241 elsif
1242 -- there is a period of work between two absences
1243 l_work_start_date < absence.date_start
1244 and l_work_start_date < (g_SMP_element.maximum_MPP * 7)
1245 + woman.MPP_start_date
1246 then
1247 create_stoppage (p_reason => 'Some work was done',
1248 p_withhold_from => ssp_smp_support_pkg.start_of_week
1249 (l_work_start_date),
1250 p_withhold_to => ssp_smp_support_pkg.end_of_week
1251 (absence.date_start -1));
1252 --
1253 if absence.date_end <> hr_general.end_of_time
1254 then
1255 l_work_start_date := absence.date_end + 1;
1256 else
1257 --
1258 -- This is not the last absence in the maternity but it has no end date.
1259 --
1260 hr_utility.trace (l_proc||' ERROR: Invalid null absence end date');
1261 --
1262 raise invalid_absence_date;
1263 end if;
1264 end if;
1265 end loop;
1266 --
1267 check_continuity_rule;
1268 check_stillbirth;
1269 check_new_employer;
1270 check_maternity_allowance;
1271 check_death;
1272 check_medical_evidence;
1273 check_birth_confirmation;
1274 -- bug 2663735
1275 check_employment_qw;
1276 check_average_earnings;
1277 --
1278 -- If we get this far the person is entitled to SMP (though stoppages may apply)
1279 --
1280 return TRUE;
1281 --
1282 exception
1283 --
1284 when invalid_absence_date then
1285 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1286 fnd_message.set_token ('PROCEDURE','ssp_smp_pkg.entitled_to_SMP');
1287 fnd_message.set_token ('STEP','3');
1288 --
1289 when no_prima_facia_entitlement then
1290 --
1291 -- Exit silently; this will allow us to call this procedure with impunity
1292 -- from absences which are not maternity absences (e.g. via a row trigger)
1293 --
1294 return FALSE;
1295 --
1296 end entitled_to_SMP;
1297 --------------------------------------------------------------------------------
1298 procedure generate_payments
1299 --
1300 --
1301 -- Starting with the start of the Maternity Pay Period (MPP), create
1302 -- a nonrecurring entry for each week of maternity absence up to the
1303 -- end of the maternity absence or to the maximum number of weeks
1304 -- specified by the SMP rules. Note that there may be more than one
1305 -- absence for a maternity, and the absences should be put together;
1306 -- however, if the woman works for any part of a week (Sun-Sat), then
1307 -- that week does not count towards maternity pay.
1308 --
1309 -- If there is a stoppage which overlaps either partially or completely
1310 -- with the period covered by an entry then skip the creation of that
1311 -- entry and move on to the next week. There are two kinds of stoppages;
1312 -- those which apply temporarily, and those which apply forever once
1313 -- started. Stoppages only apply within a maternity. If there is a
1314 -- stoppage which applies forever (ie it has no end date), then there
1315 -- is no need to continue creating entries after the start of that
1316 -- stoppage. Temporary entries should only affect creation while they
1317 -- apply. A further feature of stoppages is that they may be overridden
1318 -- by the user; if the override flag is set, then take no acount of
1319 -- that stoppage when creating entries.
1320 --
1321 -- The maximum allowed number of weeks is held as a DDF segment on the
1322 -- SMP element.
1323 --
1324 -- Whilst each entry is created to cover a particular period of absence,
1325 -- the payroll period in which the entry resides is determined
1326 -- separately. The default is that the entry will be created in the
1327 -- payroll period which covers the end of the week of absence for which
1328 -- the entry is created. If, however, that payroll period is in the
1329 -- past, or has already been processed, or is closed, or is after the
1330 -- person's last standard process date, then the entry must be placed
1331 -- in the next open period for which no main payroll run has been
1332 -- performed and which falls around or before the last standard process
1333 -- date. If the entry cannot be created in any such period, for whatever
1334 -- reason, then an error should be raised and the user required to
1335 -- resolve the problem before any entry for the absence can be created.
1336 -- The user may choose to pay SMP as a lump sum, in which case all
1337 -- entries are to be placed in the next possible payroll period after
1338 -- the start of the Maternity Pay Period.
1339 --
1340 -- If any detail of the absence is changed, then the entries must be
1341 -- recalculated to ensure that the change is reflected in the
1342 -- payments. Therefore, we may be performing the entry creation when
1343 -- entries already exist for the absence. For each entry which we are
1344 -- about to create, we must check that there is not an existing entry
1345 -- which covers the same absence period. If there is not, then we
1346 -- create the entry as planned; if there is, then we must update the
1347 -- existing one rather than create a new one, if a change is required.
1348 -- However, if that entry has already been processed in a payroll run,
1349 -- then rather than updating it, we must ensure that the
1350 -- over/underpayment is corrected at the next opportunity. This is
1351 -- done by creating two entries; one which pays the correct amount
1352 -- and another, for the SMP Correction element, which reverses the
1353 -- incorrect payment by replicating it with a negative sign in front of
1354 -- the amount entry value. Before creating the negative entry, it is
1355 -- essential to check that there is not already a negative entry for
1356 -- the incorrect entry; we do not want to overcorrect.
1357 --
1358 -- The week commencing entry value is the date of the Sunday of the
1359 -- week for which this entry is being created.
1360 --
1361 -- The Rate entry value is simply 'Higher' or 'Lower'. The number of
1362 -- entries to be created at the higher rate is determined by the
1363 -- DDF segment on the SMP element. When creating entries for SMP,
1364 -- that number of entries is to be created at the higher rate before
1365 -- any are created at the lower rate, and so stoppages are always
1366 -- affecting the lower rate first.
1367 --
1368 -- The amount entry value is determined by the rate; if it is the
1369 -- higher rate, then the higer rate DDF segment of the SMP element
1370 -- will identify a percentage of average earnings. The average
1371 -- earnings should have been calculated by the entitlement check, for
1372 -- payroll users, or entered independently by HR users. If it is the
1373 -- lower rate, then the amount to be paid is held directly in the
1374 -- lower rate DDF segment on the SMP element.
1375 --
1376 -- Each entry created by this procedure is to have a creator type
1377 -- which identifies it as an SMP entry, and a creator_id which is
1378 -- the maternity_id.
1379 --
1380 -- p_deleting parameter has been added to carry out logic for
1381 -- dealing with deleted absences
1382 --
1383 (p_maternity_id in number,
1384 p_deleting in boolean ) is
1385 --
1386 type date_table is table of date index by binary_integer;
1387 type number_table is table of number index by binary_integer;
1388 type varchar_table is table of varchar2 (80) index by binary_integer;
1389 l_proc varchar2(72) := g_package||'generate_payments';
1390 --
1391 type SMP_entry is record (
1392 --
1393 element_entry_id number_table,
1394 element_link_id number_table,
1395 assignment_id number_table,
1396 effective_start_date date_table,
1397 effective_end_date date_table,
1398 amount number_table,
1399 rate varchar_table,
1400 week_commencing date_table,
1401 recoverable_amount number_table,
1402 stopped varchar_table,
1403 dealt_with varchar_table);
1404 --
1405 --
1406 -- A store for all the SMP entries that potentially may be granted to the woman.
1407 --
1408 hypothetical_entry SMP_entry;
1409 --
1410 -- A tally of the number of weeks of the MPP which are subject to stoppages.
1411 --
1412 l_stopped_weeks number := 0;
1413 --
1414 l_high_rate varchar2 (80) := null;
1415 l_low_rate varchar2 (80) := null;
1416 -- RT entries prob
1417 l_no_of_absence_periods integer := 0;
1418 --
1419 -- p_deleting passed into save_hypothetical_entries, so that logic can be
1420 -- dealt with for deleted absences.
1421 --
1422 procedure save_hypothetical_entries (p_deleting in boolean ) is
1423 --
1424 -- Having generated the potential SMP entries, reconcile them with any
1425 -- previously granted entries for the same maternity.
1426 --
1427 cursor csr_existing_entries is
1428 --
1429 -- Get all entries and entry values for the maternity
1430 --
1431 -- Decode the rate code to give the meaning using local variables
1432 -- populated earlier by Calculate_correct_SMP_rate
1433 -- these can then be passed directly into the hr_entry_api's and
1434 -- simplifies comparison with hypo entries
1435 --
1436 select entry.element_entry_id,
1437 entry.element_link_id,
1438 entry.assignment_id,
1439 entry.effective_start_date,
1440 entry.effective_end_date,
1441 decode(ssp_smp_support_pkg.value(entry.element_entry_id,
1442 ssp_smp_pkg.c_rate_name),'HIGH',l_high_rate,l_low_rate) RATE,
1443 /*to_date (ssp_smp_support_pkg.value
1444 (entry.element_entry_id,
1445 ssp_smp_pkg.c_week_commencing_name),
1446 'DD-MON-YYYY') WEEK_COMMENCING,*/
1447 fnd_date.chardate_to_date(ssp_smp_support_pkg.value
1448 (entry.element_entry_id,ssp_smp_pkg.c_week_commencing_name)) WEEK_COMMENCING,
1449 to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
1450 ssp_smp_pkg.c_amount_name)) AMOUNT,
1451 to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
1452 ssp_smp_pkg.c_recoverable_amount_name)) RECOVERABLE_AMOUNT
1453 from pay_element_entries_f ENTRY,
1454 per_all_assignments_f asg
1455 where creator_type = c_SMP_creator_type
1456 and creator_id = p_maternity_id
1457 and asg.person_id = woman.person_id
1458 and asg.assignment_id = entry.assignment_id
1459 and entry.effective_start_date between asg.effective_start_date
1460 and asg.effective_end_date
1461 and not exists (
1462 --
1463 -- Do not select entries which have already had reversal action taken
1464 -- against them because they are effectively cancelled out.
1465 --
1466 select 1
1467 from pay_element_entries_f ENTRY2
1468 where entry.element_entry_id= entry2.target_entry_id
1469 and entry.assignment_id = entry2.assignment_id)
1470 --
1471 and not exists (
1472 --
1473 -- Do not select reversal entries
1474 --
1475 select 1
1476 from pay_element_links_f LINK,
1477 pay_element_types_f TYPE
1478 where link.element_link_id = entry.element_link_id
1479 and entry.effective_start_date between link.effective_start_date
1480 and link.effective_end_date
1481 and link.element_type_id = type.element_type_id
1482 and link.effective_start_date between type.effective_start_date
1483 and type.effective_end_date
1484 and type.element_name = c_SMP_Corr_element_name);
1485 --
1486 cursor csr_no_of_absences is
1487 --
1488 -- Get the number of distinct absences within a maternity pay period
1489 --
1490 select count (*)
1491 from per_absence_attendances
1492 where person_id = woman.person_id
1493 and maternity_id = p_maternity_id;
1494 --
1495 l_ins_corr_ele boolean;
1496 l_dummy number;
1497 Entry_number integer;
1498 l_ern_calc_id number;
1499 l_ob_v_no number;
1500 l_new_ob_v_no number;
1501 l_proc varchar2 (72) := g_package||'save_hypothetical_entries';
1502 --
1503 -- This procedure was a private procedure in the function entitled_to_smp. I
1504 -- wanted to call it within this procedure (generate_payments) aswell, so
1505 -- instead of making it a public procedure I have copied the procedure to here.
1506 --
1507 procedure remove_stoppages is
1508 --
1509 -- Remove old system, non-overridden stoppages
1510 --
1511 cursor csr_stoppages is
1512 --
1513 select stoppage_id
1514 from ssp_stoppages
1515 where user_entered <>'Y'
1516 and override_stoppage <> 'Y'
1517 and maternity_id = p_maternity_id;
1518 --
1519 l_dummy number;
1520 l_proc varchar2 (72) := g_package||'.remove_stoppages';
1521 --
1522 begin
1523 --
1524 hr_utility.set_location (l_proc,1);
1525 --
1526 for each_stoppage in csr_stoppages LOOP
1527 ssp_stp_del.del (p_stoppage_id => each_stoppage.stoppage_id,
1528 p_object_version_number => l_dummy);
1529 end loop;
1530 --
1531 hr_utility.set_location (l_proc,100);
1532 --
1533 end remove_stoppages;
1534 --
1535 begin
1536 --
1537 --hr_utility.trace_on(null,'ssp');
1538 hr_utility.set_location('Entering: '||l_proc,10);
1539
1540 hr_utility.trace('woman.due_date='||woman.due_date);
1541 --
1542 get_SMP_correction_element (woman.due_date);
1543 --
1544 -- Check each existing SMP entry in turn against all the potential new ones.
1545 --
1546 <<OLD_ENTRIES>>
1547
1548 hr_utility.trace('Old entries');
1549
1550
1551 for old_entry in csr_existing_entries
1552 LOOP
1553 --First loop through the hypothetical entries to see if there is one
1554 --which covers the same week as the old entry and is not subject to
1555 --a stoppage. If there isn't one, invalidate the old entry.
1556 --Assume we don't need to correct the entry until we discover otherwise:
1557 --
1558
1559 hr_utility.trace('Entered csr_existing_entries');
1560
1561 l_ins_corr_ele := FALSE;
1562 begin
1563 hr_utility.trace('Entering begin');
1564 entry_number := 0;
1565 if p_deleting then
1566 hr_utility.trace('Raising error');
1567 raise no_data_found; -- enter exception handler
1568 end if;
1569 LOOP
1570
1571 hr_utility.trace('First loop');
1572 entry_number := entry_number +1;
1573
1574 -- Exit the loop when we find a hypothetical entry covering the
1575 -- same week as the old entry, which is not subject to a stoppage.
1576 -- If no such match is found, then we will reach the end of the
1577 -- pl/sql table and attempt to read beyond the existing rows; this
1578 -- will cause us to enter the exception handler and indicate that
1579 -- no match was found.
1580
1581 exit when ((old_entry.week_commencing
1582 = hypothetical_entry.week_commencing (entry_number)
1583 and not hypothetical_entry.stopped (entry_number) = 'TRUE'
1584 and g_smp_update = 'N')
1585 or (old_entry.effective_start_date
1586 = hypothetical_entry.effective_start_date (entry_number)
1587 and old_entry.week_commencing
1588 = hypothetical_entry.week_commencing (entry_number)
1589 and not hypothetical_entry.stopped (entry_number) = 'TRUE'
1590 and g_smp_update = 'Y'));
1591
1592 hr_utility.trace('Exiting');
1593 end loop;
1594
1595 hr_utility.trace (l_proc||' Old entry / Hypo entry time Match with values:');
1596 hr_utility.trace (l_proc||' Rate: '
1597 ||old_entry.rate||' / '
1598 ||hypothetical_entry.rate (Entry_number));
1599 hr_utility.trace (l_proc||' Amount: '
1600 ||old_entry.amount||' / '
1601 ||hypothetical_entry.amount (entry_number));
1602 hr_utility.trace (l_proc||' Recoverable: '
1603 ||old_entry.recoverable_amount||' / '
1604 ||hypothetical_entry.recoverable_amount (entry_number));
1605 hr_utility.trace (l_proc||' Week Comm: '
1606 ||hypothetical_entry.week_commencing (entry_number) );
1607 --A hypo entry covers the same week as the old one
1608 if old_entry.rate = hypothetical_entry.rate (entry_number)
1609 and old_entry.amount = hypothetical_entry.amount(entry_number)
1610 and old_entry.recoverable_amount
1611 = hypothetical_entry.recoverable_amount (entry_number)
1612 then
1613 -- the hypo entry has the same values as the old one
1614 -- don't create a correction element.
1615 -- don't create a new entry
1616 hypothetical_entry.dealt_with (entry_number) := 'TRUE';
1617 hr_utility.trace (l_proc||' leave unchanged');
1618 else
1619 if ssp_smp_support_pkg.entry_already_processed
1620 (old_entry.element_entry_id)
1621 then l_ins_corr_ele := TRUE;
1622 hr_utility.trace (l_proc||' processed - correct it');
1623 else
1624 -- update old entry
1625 hr_utility.trace (l_proc||' unprocessed - update it');
1626 hr_entry_api.update_element_entry (
1627 p_dt_update_mode => 'CORRECTION',
1628 p_session_date => old_entry.effective_start_date,
1629 p_element_entry_id => old_entry.element_entry_id,
1630 p_input_value_id1 => g_SMP_element.rate_id,
1631 p_input_value_id2 => g_SMP_element.amount_id,
1632 p_input_value_id3 => g_SMP_element.recoverable_amount_id,
1633 p_entry_value1=> hypothetical_entry.rate (entry_number),
1634 p_entry_value2=> hypothetical_entry.amount(entry_number),
1635 p_entry_value3=>
1636 hypothetical_entry.recoverable_amount (entry_number));
1637 --
1638 --prevent insertion of new entry
1639 --
1640 hypothetical_entry.dealt_with (entry_number) := 'TRUE';
1641 end if;
1642 end if;
1643 exception
1644
1645 when no_data_found then
1646 -- There was no new entry which exactly matched the old entry.
1647 -- or we are deleting.
1648 entry_number := null;
1649 hr_utility.trace (l_proc||' No Old entry - Hypo entry time Match');
1650 hr_utility.trace (l_proc||' or p_deleting is true');
1651 hr_utility.trace (l_proc||' Old entry values:');
1652 hr_utility.trace (l_proc||' Rate: '||old_entry.rate);
1653 hr_utility.trace (l_proc||' Amount: '||old_entry.amount);
1654 hr_utility.trace (l_proc||' Recoverable: '
1655 ||old_entry.recoverable_amount);
1656 if ssp_smp_support_pkg.entry_already_processed
1657 (old_entry.element_entry_id)
1658 then l_ins_corr_ele := TRUE;
1659 hr_utility.trace (l_proc||' Old entry already processed');
1660 else
1661 hr_utility.trace (l_proc||' Old entry NOT already processed');
1662 --Old entry not already processed so delete it
1663 hr_entry_api.delete_element_entry (
1664 p_dt_delete_mode => 'ZAP',
1665 p_session_date => old_entry.effective_start_date,
1666 p_element_entry_id => old_entry.element_entry_id);
1667 end if;
1668 when others then
1669 hr_utility.trace('Entered error');
1670 hr_utility.trace('SQL ERROR:='||SQLERRM);
1671 end;
1672 if l_ins_corr_ele
1673 then
1674 -- Create a correction element to reverse the old entry. Then create a
1675 -- brand new entry with the correct values.
1676 --
1677 hr_utility.trace (l_proc ||
1678 ' Inserting CORRECTION entry for week commencing ' ||
1679 to_char (old_entry.week_commencing));
1680 hr_utility.trace (l_proc||' Old value / New value:');
1681 if entry_number is null then
1682 hr_utility.trace (l_proc||' Rate: '
1683 ||old_entry.rate||' / NA');
1684 hr_utility.trace (l_proc||' Amount: '
1685 ||old_entry.amount||' / NA');
1686 hr_utility.trace (l_proc||' Recoverable: '
1687 ||old_entry.recoverable_amount||' / NA');
1688 else
1689 hr_utility.trace (l_proc||' Rate: '
1690 ||old_entry.rate||' / '
1691 ||hypothetical_entry.rate (Entry_number));
1692 hr_utility.trace (l_proc||' Amount: '
1693 ||old_entry.amount||' / '
1694 ||hypothetical_entry.amount (entry_number));
1695 hr_utility.trace (l_proc||' Recoverable: '
1696 ||old_entry.recoverable_amount||' /'
1697 ||hypothetical_entry.recoverable_amount (entry_number));
1698 end if;
1699 --
1700 -- Determine the next available period in which to place the
1701 -- correction entry
1702 --
1703
1704 hr_utility.trace('Correction Entry');
1705
1706 ssp_smp_support_pkg.get_entry_details (
1707 p_date_earned => old_entry.week_commencing,
1708 p_last_process_date => woman.final_process_date,
1709 p_person_id => woman.person_id,
1710 p_element_type_id => g_SMP_Correction_element.element_type_id,
1711 p_element_link_id => old_entry.element_link_id,
1712 p_assignment_id => old_entry.assignment_id,
1713 p_effective_start_date => old_entry.effective_start_date,
1714 p_effective_end_date => old_entry.effective_end_date,
1715 p_pay_as_lump_sum => woman.pay_SMP_as_lump_sum);
1716 --
1717 -- hr_entry_api's take the lookup meanings not the lookup codes.
1718 -- Fix to Bug 590966 converted rate codes to meanings before calling the
1719 -- api. Later fix 647543 made old_entry (csr_existing_entries) return
1720 -- the meaning, so rate passed directly.
1721 --
1722
1723 hr_utility.trace('Insert element entry');
1724
1725 hr_entry_api.insert_element_entry (
1726 p_effective_start_date=> old_entry.effective_start_date,
1727 p_effective_end_date => old_entry.effective_end_date,
1728 p_element_entry_id => l_dummy,
1729 p_target_entry_id => old_entry.element_entry_id,
1730 p_assignment_id => old_entry.assignment_id,
1731 p_element_link_id => old_entry.element_link_id,
1732 p_creator_type => c_SMP_creator_type,
1733 p_creator_id => p_maternity_id,
1734 p_entry_type => c_SMP_entry_type,
1735 p_input_value_id1=> g_SMP_correction_element.rate_id,
1736 p_input_value_id2=> g_SMP_correction_element.week_commencing_id,
1737 p_input_value_id3=> g_SMP_correction_element.amount_id,
1738 p_input_value_id4=> g_SMP_correction_element.recoverable_amount_id,
1739 p_entry_value1=> old_entry.rate,
1740 --p_entry_value2 => to_char(old_entry.week_commencing,'DD-MON-YYYY'),
1741 p_entry_value2 => fnd_date.date_to_chardate(old_entry.week_commencing),
1742 p_entry_value3=> old_entry.amount * -1,
1743 p_entry_value4=> old_entry.recoverable_amount * -1);
1744 --
1745 --New entry will be created by brand_new_entries loop if not p_deleting
1746 end if;
1747 --
1748 end loop old_entries;
1749 --
1750 -- Having been through all the existing entries, we now check that we
1751 -- have dealt with all the newly derived entries by inserting any which
1752 -- were not flagged as dealt with during the above actions.
1753 --
1754 hr_utility.set_location (l_proc,20);
1755 --
1756 <<BRAND_NEW_ENTRIES>>
1757 begin
1758 if p_deleting then
1759 hr_utility.trace('Deleting an absence so don''t insert entries');
1760 else
1761 for new_entry in 1..g_SMP_element.maximum_MPP LOOP
1762 if (not hypothetical_entry.dealt_with (new_entry) = 'TRUE')
1763 and (not hypothetical_entry.stopped (new_entry) = 'TRUE')
1764 then
1765 hr_entry_api.insert_element_entry (
1766 p_effective_start_date =>
1767 hypothetical_entry.effective_start_date (new_entry),
1768 p_effective_end_date =>
1769 hypothetical_entry.effective_end_date (new_entry),
1770 p_element_entry_id => l_dummy,
1771 p_assignment_id => hypothetical_entry.assignment_id (new_entry),
1772 p_element_link_id => hypothetical_entry.element_link_id (new_entry),
1773 p_creator_type => c_SMP_creator_type,
1774 p_creator_id => p_maternity_id,
1775 p_entry_type => c_SMP_entry_type,
1776 p_input_value_id1 => g_SMP_element.rate_id,
1777 p_input_value_id2 => g_SMP_element.week_commencing_id,
1778 p_input_value_id3 => g_SMP_element.amount_id,
1779 p_input_value_id4 => g_SMP_element.recoverable_amount_id,
1780 p_entry_value1 => hypothetical_entry.rate (new_entry),
1781 -- p_entry_value2 => hypothetical_entry.week_commencing (new_entry),
1782 p_entry_value2 => to_char(hypothetical_entry.week_commencing(new_entry),'DD-MON-YYYY'),
1783 p_entry_value3 => hypothetical_entry.amount (new_entry),
1784 p_entry_value4 =>
1785 hypothetical_entry.recoverable_amount (new_entry));
1786 end if;
1787 end loop brand_new_entries;
1788 end if;
1789 exception
1790 when no_data_found then
1791 --
1792 -- We have run out of hypothetical entries to insert
1793 --
1794 null;
1795 --
1796 end;
1797 --
1798 -- Orphaned stoppages, associated with deleted absence can now be deleted
1799 -- This replaces cross product constraints that are no longer allowed.
1800 --
1801 open csr_no_of_absences;
1802 fetch csr_no_of_absences into l_no_of_absence_periods;
1803 close csr_no_of_absences;
1804 --
1805 if l_no_of_absence_periods = 0 then
1806 remove_stoppages;
1807 end if;
1808 --
1809 hr_utility.set_location(' Leaving: '||l_proc,100);
1810 --
1811 end save_hypothetical_entries;
1812 --
1813 procedure derive_SMP_week (p_week_number in integer) is
1814 --
1815 -- Derive the start and end dates of the week covered by the SMP
1816 -- payment. This is done by finding out how many weeks into the MPP
1817 -- we are and finding the offset from the start date.
1818 --
1819 begin
1820 --
1821 hr_utility.set_location ('Entering: ssp_smp_pkg.derive_SMP_week',1);
1822 hr_utility.trace ('Entry number = '||to_char (p_week_number));
1823 --
1824 hypothetical_entry.week_commencing (p_week_number)
1825 := (woman.MPP_start_date + ((p_week_number -1) * 7));
1826 --
1827 hypothetical_entry.dealt_with (p_week_number) := 'FALSE';
1828 hypothetical_entry.stopped (p_week_number) := 'FALSE';
1829 hypothetical_entry.element_link_id (p_week_number) := null;
1830 hypothetical_entry.assignment_id (p_week_number) := null;
1831 --
1832 hr_utility.trace ('week_commencing = '
1833 ||to_char (hypothetical_entry.week_commencing (p_week_number)));
1834 --
1835 hr_utility.set_location ('Leaving : ssp_smp_pkg.derive_SMP_week',100);
1836 --
1837 end derive_SMP_week;
1838 --
1839 procedure Check_SMP_stoppages (p_week_number in integer) is
1840 --
1841 -- Find any SMP stoppage for the maternity which overlaps a date range
1842 --
1843 employee_died varchar2 (30) := 'Employee died';
1844 --
1845 cursor csr_stoppages (p_start_date in date, p_end_date in date) is
1846 --
1847 -- Find any non-overridden stoppages
1848 --
1849 select 1
1850 from ssp_stoppages STP,
1851 ssp_withholding_reasons WRE
1852 where stp.override_stoppage <> 'Y'
1853 --
1854 -- and the stoppage ovelaps the period or the stoppage is for
1855 -- death and is prior to the period
1856 --
1857 and ((wre.reason <> employee_died
1858 and stp.withhold_from <= p_end_date
1859 and nvl (stp.withhold_to, hr_general.end_of_time)
1860 >= p_start_date)
1861 --
1862 or (wre.reason = employee_died
1863 -- Bug 2663899
1864 and stp.withhold_from <= p_end_date))
1865 --
1866 and stp.maternity_id = p_maternity_id
1867 and stp.reason_id = wre.reason_id;
1868 --
1869 l_dummy integer (1);
1870 --
1871 begin
1872 --
1873 hr_utility.set_location ('ssp_smp_pkg.Check_SMP_stoppages',1);
1874 --
1875 hypothetical_entry.stopped (p_week_number) := 'FALSE';
1876 --
1877 open csr_stoppages (
1878 hypothetical_entry.week_commencing (p_week_number),
1879 ssp_smp_support_pkg.end_of_week
1880 (hypothetical_entry.week_commencing (p_week_number)));
1881 --
1882 fetch csr_stoppages into l_dummy;
1883 --
1884 if csr_stoppages%found
1885 then
1886 --
1887 -- There is an overlap between the SMP week and a stoppage so no SMP
1888 -- is payable.
1889 --
1890 hypothetical_entry.stopped (p_week_number) := 'TRUE';
1891 --
1892 hr_utility.trace ('Entry is STOPPED');
1893 --
1894 -- Keep a tally of the number of stopped weeks
1895 --
1896 l_stopped_weeks := l_stopped_weeks +1;
1897 end if;
1898 --
1899 close csr_stoppages;
1900 --
1901 hr_utility.set_location ('ssp_smp_pkg.Check_SMP_stoppages',10);
1902 --
1903 end Check_SMP_stoppages;
1904 --
1905 procedure Calculate_correct_SMP_rate (p_week_number in number) is
1906 --
1907 -- The entry API takes the lookup meanings so we must find
1908 -- the meanings rather than the codes for SMP rates.
1909 --
1910 cursor csr_rate_meaning (p_rate_band varchar2) is
1911 --
1912 select meaning
1913 from hr_lookups
1914 where lookup_type = 'SMP_RATES'
1915 and lookup_code = p_rate_band;
1916 --
1917 begin
1918 --
1919 hr_utility.set_location ('ssp_smp_pkg.Calculate_correct_SMP_rate',1);
1920 --
1921 if l_high_rate is null then
1922 --
1923 -- Get the meanings for the rate bands
1924 --
1925 -- Get the higher rate band
1926 --
1927 open csr_rate_meaning ('HIGH');
1928 fetch csr_rate_meaning into l_high_rate;
1929 close csr_rate_meaning;
1930 --
1931 -- Get the lower rate band
1932 --
1933 open csr_rate_meaning ('LOW');
1934 fetch csr_rate_meaning into l_low_rate;
1935 close csr_rate_meaning;
1936 end if;
1937 --
1938 if (p_week_number - l_stopped_weeks)
1939 <= g_SMP_element.period_at_higher_rate
1940 then
1941 hr_utility.set_location ('ssp_smp_pkg.Calculate_correct_SMP_rate',1);
1942 --
1943 -- We have not yet given the employee all their higher rate weeks
1944 --
1945 hypothetical_entry.rate (p_week_number) := l_high_rate;
1946 else
1947 hypothetical_entry.rate (p_week_number) := l_low_rate;
1948 end if;
1949 --
1950 hr_utility.trace ('SMP Rate = '
1951 ||hypothetical_entry.rate (p_week_number));
1952 --
1953 hr_utility.set_location ('ssp_smp_pkg.Calculate_correct_SMP_rate',10);
1954 --
1955 end Calculate_correct_SMP_rate;
1956 --
1957 procedure Calculate_SMP_amounts (p_week_number in integer, p_MPP_start_date in date) is
1958 --
1959 begin
1960 --
1961 hr_utility.set_location('Entering: ssp_smp_pkg.Calculate_SMP_amounts',1);
1962 --
1963 -- Get the SMP element for each week in case the SMP rate has changed
1964 --
1965 get_SMP_element (hypothetical_entry.week_commencing (p_week_number));
1966 --
1967 if hypothetical_entry.rate (p_week_number) = l_high_rate then
1968 --
1969 -- The higher rate is the greater of the low SMP flat rate amount and
1970 -- the legislation-specified proportion of average earnings (rounded
1971 -- UP to the nearest penny)
1972 --
1973 hr_utility.trace('Calculate at high rate');
1974 hr_utility.trace('p_MPP_start_date : '||fnd_date.date_to_canonical(p_MPP_start_date));
1975 if p_MPP_start_date >= fnd_date.canonical_to_date('2003/04/06 00:00:00') then
1976 hr_utility.trace('MPP start date after 06-APR-2003, therefore 90 percent of average earnings');
1977 hypothetical_entry.amount (p_week_number)
1978 := round((average_earnings * g_SMP_element.higher_SMP_rate)
1979 + 0.0049,2);
1980 else
1981 hr_utility.trace('Due date before 06-APR-2003, therfore under higher rate with lower rate, else 90 percent of average earnings');
1982 hypothetical_entry.amount (p_week_number)
1983 := greatest (round (
1984 (average_earnings * g_SMP_element.higher_SMP_rate)
1985 + 0.0049,2),
1986 g_SMP_element.lower_SMP_rate);
1987 end if;
1988 else
1989 hr_utility.trace('Calculating for lower, therfore we need to find out if week commencing date is before or after 06-APR-2003, week_commencing : '||fnd_date.date_to_canonical(hypothetical_entry.week_commencing(p_week_number)));
1990 if hypothetical_entry.week_commencing(p_week_number) <
1991 fnd_date.canonical_to_date('2003/04/06 00:00:00') then
1992 hr_utility.trace('Decided week commencing before 06-APR-2003');
1993 -- Any SMP weeks paid before 06-APR-2003 to be paid
1994 -- at floored lower amount
1995 hypothetical_entry.amount (p_week_number)
1996 := g_SMP_element.lower_SMP_rate;
1997 elsif (hypothetical_entry.week_commencing(p_week_number) >=
1998 fnd_date.canonical_to_date('2003/04/06 00:00:00') and
1999 p_MPP_start_date < fnd_date.canonical_to_date('2003/04/06 00:00:00')) then
2000 -- For payments after 06-APR-2003 for maternities with MPP start date
2001 -- prior to 06-APR-2003, the payment must be underpinned by SMP
2002 -- lower rate
2003 hr_utility.trace('Week commenicng is after 06-APR-2003, but MPP start date is before 06-APR-2003, therfore amount must be underpinned');
2004 if g_SMP_element.lower_SMP_rate > round(
2005 (average_earnings * g_SMP_element.higher_SMP_rate)
2006 + 0.0049,2) then
2007 -- Payment to be underpinned
2008 hr_utility.trace('Average earnings * 90 percent less than lower rate, therfore underpin');
2009 hypothetical_entry.amount (p_week_number)
2010 := g_SMP_element.lower_SMP_rate;
2011 else
2012 -- Calculated amount is higher than lower SMP rate, so
2013 -- assign calculated amount
2014 hr_utility.trace('average earnings * 90 percent greater than lower rate, no need to underpin');
2015 hypothetical_entry.amount (p_week_number)
2016 := least(round(
2017 (average_earnings * g_SMP_element.higher_SMP_rate)
2018 + 0.0049,2), g_SMP_element.standard_SMP_rate);
2019 end if;
2020 else
2021 hr_utility.trace('Due date and week Commencing both after 06-APr-2003, therfore lowest of average earnings and standard rate');
2022 hypothetical_entry.amount (p_week_number)
2023 := least(round(
2024 (average_earnings * g_SMP_element.higher_SMP_rate)
2025 + 0.0049,2), g_SMP_element.standard_SMP_rate);
2026 end if;
2027 end if;
2028 --
2029 hypothetical_entry.recoverable_amount (p_week_number)
2030 := round (hypothetical_entry.amount (p_week_number)
2031 * g_SMP_element.recovery_rate,2);
2032 --
2033 hr_utility.trace ('SMP amount = '
2034 ||to_char (hypothetical_entry.amount (p_week_number)));
2035 hr_utility.trace ('Recoverable amount = '
2036 ||to_char (hypothetical_entry.recoverable_amount (p_week_number)));
2037 --
2038 hr_utility.set_location('Leaving : ssp_smp_pkg.Calculate_SMP_amounts',100);
2039 --
2040 end calculate_SMP_amounts;
2041 --
2042 procedure check_parameters is
2043 begin
2044 hr_api.mandatory_arg_error (
2045 p_api_name => l_proc,
2046 p_argument => 'maternity_id',
2047 p_argument_value=> p_maternity_id);
2048 --
2049 end check_parameters;
2050 --
2051 begin
2052 --
2053 hr_utility.set_location ('ssp_smp_pkg.generate_payments',1);
2054 --
2055 check_parameters;
2056 --
2057 <<SMP_WEEKS>>
2058 --
2059 if woman.MPP_start_date is not null then
2060 for week_number in 1..g_SMP_element.maximum_MPP
2061 LOOP
2062 --
2063 -- Derive hypothetical entries ie those entries which would be applied for a
2064 -- completely new maternity. Store them internally because we must check
2065 -- previously created entries before applying the hypothetical entries to the
2066 -- database.
2067 --
2068 Derive_SMP_week (week_number);
2069 Check_SMP_stoppages (week_number);
2070 Calculate_correct_SMP_rate (week_number);
2071 Calculate_SMP_amounts (week_number, woman.MPP_start_date);
2072 --
2073 if (hypothetical_entry.stopped (week_number) = 'FALSE') then
2074 --
2075 -- Get the entry details unless the entry has been stopped (in which case
2076 -- we do not need the entry details and errors may occur if we call the
2077 -- procedure; eg the woman's assignment ends)
2078 --
2079 ssp_smp_support_pkg.get_entry_details (
2080 p_date_earned => hypothetical_entry.week_commencing
2081 (week_number),
2082 p_pay_as_lump_sum => woman.pay_SMP_as_lump_sum,
2083 p_last_process_date => woman.final_process_date,
2084 p_person_id => woman.person_id,
2085 p_element_type_id => g_SMP_element.element_type_id,
2086 p_element_link_id => hypothetical_entry.element_link_id
2087 (week_number),
2088 p_assignment_id => hypothetical_entry.assignment_id
2089 (week_number),
2090 p_effective_start_date => hypothetical_entry.effective_start_date
2091 (week_number),
2092 p_effective_end_date => hypothetical_entry.effective_end_date
2093 (week_number));
2094 end if;
2095 end loop SMP_weeks;
2096 end if;
2097 --
2098 Save_hypothetical_entries(p_deleting);
2099 --
2100 end generate_payments;
2101 --
2102 --------------------------------------------------------------------------------
2103 procedure SMP_control (p_maternity_id in number,
2104 p_deleting in boolean ) is
2105 --
2106 -- p_deleting parameter added to deal with absences being deleted, without
2107 -- maternity being deleted.
2108 --
2109 cursor csr_maternity is
2110 --
2111 -- Find out if the maternity exists
2112 --
2113 select 1
2114 from ssp_maternities
2115 where maternity_id = p_maternity_id;
2116 --
2117 cursor csr_entries is
2118 --
2119 -- Get all element entries associated with a maternity
2120 --
2121 select /*+ ORDERED use_nl(paa,paaf,etype,entry) */
2122 entry.element_entry_id,
2123 entry.effective_start_date
2124 from per_absence_attendances PAA,
2125 per_all_assignments_f PAAF,
2126 pay_element_entries_f entry
2127 where PAA.maternity_id = p_maternity_id
2128 and PAAF.person_id = PAA.person_id
2129 and entry.creator_type = 'M'
2130 and entry.creator_id = p_maternity_id
2131 and entry.assignment_id = paaf.assignment_id;
2132 --
2133 cursor csr_count_absences is
2134 select count(*)
2135 from ssp_maternities mat,
2136 per_absence_attendances ab
2137 where mat.maternity_id = p_maternity_id
2138 and ab.person_id = mat.person_id
2139 and ab.maternity_id = mat.maternity_id;
2140 --
2141 l_count number;
2142 l_dummy number;
2143 l_proc varchar2 (72) := g_package||'SMP_control';
2144 --
2145 begin
2146 --
2147 hr_utility.set_location (l_proc,1);
2148 --
2149 open csr_maternity;
2150 fetch csr_maternity into l_dummy;
2151 --
2152 if csr_maternity%found then
2153 --
2154 -- Recalculate SMP
2155 --
2156 if entitled_to_SMP (p_maternity_id) then
2157 open csr_count_absences;
2158 fetch csr_count_absences into l_count;
2159 close csr_count_absences;
2160 if l_count > 0 then
2161 generate_payments (p_maternity_id, false);
2162 else
2163 generate_payments (p_maternity_id, p_deleting);
2164 end if;
2165 elsif p_deleting then
2166 -- not entitled but deleting absence then
2167 generate_payments (p_maternity_id, p_deleting);
2168 end if;
2169 else
2170 --
2171 -- The maternity may have been deleted. Remove any element entries associated
2172 -- with it (the absences, stoppages and medicals are handled by constraints).
2173 --
2174 for obsolete in csr_entries LOOP
2175 hr_utility.trace (l_proc||' Deleting element entry_id '||
2176 to_char (obsolete.element_entry_id));
2177 hr_utility.trace (l_proc||'-------------------------------------------');
2178 --
2179 hr_entry_api.delete_element_entry (
2180 p_dt_delete_mode => 'ZAP',
2181 p_session_date => obsolete.effective_start_date,
2182 p_element_entry_id => obsolete.element_entry_id);
2183 end loop;
2184 end if;
2185 --
2186 g_smp_update := 'N';
2187 --
2188 hr_utility.set_location (l_proc,100);
2189 --
2190 end SMP_control;
2191 --
2192 --------------------------------------------------------------------------------
2193 procedure ins_ssp_temp_affected_rows_mat(p_maternity_id in number,
2194 p_deleting in boolean ) is
2195 --
2196 -- Inserts a row in ssp_temp_affected_rows for the maternity, if not there
2197 --
2198 l_proc varchar2 (72) := g_package||'ins_ssp_temp_affected_rows_mat';
2199 l_deleting_ch varchar2(1);
2200 --
2201 begin
2202 --
2203 hr_utility.set_location('Entering:'||l_proc,1);
2204 if p_deleting then
2205 l_deleting_ch := 'Y';
2206 else
2207 l_deleting_ch := 'N';
2208 end if;
2209 --
2210 if p_maternity_id is not null then
2211 hr_utility.trace (l_proc||' Saving maternity_id #' || p_maternity_id ||
2212 ' for recalculation (p_del = '||l_deleting_ch||').');
2213 --
2214 insert into ssp_temp_affected_rows (MATERNITY_ID, p_deleting, locked)
2215 select p_maternity_id, l_deleting_ch, userenv('sessionid')
2216 from sys.dual
2217 where not exists
2218 (select null
2219 from ssp_temp_affected_rows t2
2220 where t2.maternity_id = p_maternity_id);
2221 end if;
2222 --
2223 hr_utility.set_location('Leaving :'||l_proc,100);
2224 --
2225 end ins_ssp_temp_affected_rows_mat;
2226 --
2227 --------------------------------------------------------------------------------
2228 procedure absence_control (p_maternity_id in number,
2229 p_deleting in boolean ) is
2230 --
2231 -- Handle the event of DML on per_absence_attendances
2232 --
2233 l_proc varchar2 (72) := g_package||'absence_control';
2234 --
2235 begin
2236 --
2237 hr_utility.set_location (l_proc,1);
2238 --
2239 g_smp_update := 'N';
2240 --
2241 if p_maternity_id is not null then
2242 ins_ssp_temp_affected_rows_mat(p_maternity_id, p_deleting);
2243 end if;
2244 --
2245 hr_utility.set_location (l_proc,100);
2246 --
2247 end absence_control;
2248 --------------------------------------------------------------------------------
2249 procedure maternity_control (p_maternity_id in number) is
2250 --
2251 l_proc varchar2 (72) := g_package||'maternity_control';
2252 --
2253 begin
2254 --
2255 hr_utility.set_location (l_proc,1);
2256 --
2257 g_smp_update := 'Y';
2258 --
2259 ins_ssp_temp_affected_rows_mat(p_maternity_id, p_deleting => FALSE);
2260 --
2261 hr_utility.set_location (l_proc,100);
2262 --
2263 end maternity_control;
2264 --------------------------------------------------------------------------------
2265 procedure medical_control (p_maternity_id in number) is
2266 --
2267 l_proc varchar2 (72) := g_package||'medical_control';
2268 --
2269 begin
2270 --
2271 hr_utility.set_location (l_proc,1);
2272 --
2273 if p_maternity_id is not null -- the medical is for a maternity
2274 then
2275 ins_ssp_temp_affected_rows_mat(p_maternity_id, p_deleting => FALSE);
2276 end if;
2277 --
2278 hr_utility.set_location (l_proc,100);
2279 --
2280 end medical_control;
2281 --
2282 --------------------------------------------------------------------------------
2283 procedure earnings_control (
2284 p_person_id in number,
2285 p_effective_date in date) is
2286 --
2287 cursor csr_maternity is
2288 --
2289 -- Find any maternity whose SMP figures rely on the earnings
2290 -- calculation.
2291 --
2292 select mat.maternity_id
2293 from ssp_maternities mat
2294 where mat.person_id = p_person_id
2295 and p_effective_date = decode(mat.leave_type,
2296 'AD',ssp_sap_pkg.MATCHING_WEEK_OF_ADOPTION(mat.matching_date),
2297 'PA',ssp_pad_pkg.MATCHING_WEEK_OF_ADOPTION(mat.matching_date),
2298 'PB',ssp_pab_pkg.QUALIFYING_WEEK(mat.due_date),
2299 ssp_smp_pkg.QUALIFYING_WEEK(mat.due_date))
2300 and exists (select 1
2301 from per_absence_attendances abs
2302 where abs.person_id = p_person_id
2303 and abs.maternity_id = mat.maternity_id);
2304 --
2305 l_maternity_id number := null;
2306 l_proc varchar2(72) := g_package||'earnings_control';
2307 --
2308 begin
2309 --
2310 hr_utility.set_location (l_proc,1);
2311 --
2312 open csr_maternity;
2313 fetch csr_maternity into l_maternity_id;
2314 --
2315 if csr_maternity%found
2316 then
2317 ins_ssp_temp_affected_rows_mat(l_maternity_id, p_deleting => FALSE);
2318 else
2319 hr_utility.trace(l_proc||' No maternities affected by change in earnings');
2320 end if;
2321 --
2322 close csr_maternity;
2323 --
2324 hr_utility.set_location (l_proc,100);
2325 --
2326 end earnings_control;
2327 --
2328 --------------------------------------------------------------------------------
2329 procedure person_control (
2330 p_person_id in number,
2331 p_date_of_death in date) is
2332 --
2333 cursor csr_maternity is
2334 --
2335 -- Get any maternity which may be affected by the death of the person,
2336 -- where the date of death falls within the MPP and leave is recorded.
2337 --
2338 select maternity.maternity_id
2339 from ssp_maternities MATERNITY,
2340 pay_element_types_f ELEMENT
2341 where maternity.person_id = p_person_id
2342 and element.legislation_code = 'GB'
2343 and element.element_name = c_SMP_element_name
2344 and maternity.due_date between element.effective_start_date
2345 and element.effective_end_date
2346 and p_date_of_death <= maternity.MPP_start_date
2347 + (element.element_information6 * 7)
2348 and EXISTS (
2349 select 1
2350 from per_absence_attendances ABSENCE
2351 where ABSENCE.maternity_id = MATERNITY.maternity_id
2352 );
2353 --
2354 l_maternity_id number := null;
2355 l_proc varchar2(72) := g_package||'person_control';
2356 --
2357 begin
2358 --
2359 hr_utility.set_location (l_proc,1);
2360 --
2361 open csr_maternity;
2362 fetch csr_maternity into l_maternity_id;
2363 --
2364 if csr_maternity%found
2365 then
2366 ins_ssp_temp_affected_rows_mat(l_maternity_id, p_deleting => FALSE);
2367 end if;
2368 --
2369 close csr_maternity;
2370 --
2371 ssp_smp_support_pkg.recalculate_ssp_and_smp(p_deleting => FALSE);
2372 --
2373 hr_utility.set_location (l_proc,100);
2374 --
2375 end person_control;
2376 --
2377 --------------------------------------------------------------------------------
2378 procedure stoppage_control (p_maternity_id in number) is
2379 --
2380 -- Because stoppages are the result of the SMP entitlement check, if the user
2381 -- modifies these stoppages, then there is no point in rerunning the check on
2382 -- entitlement. However, the alteration of stoppage information will almost
2383 -- certainly have an effect on the SMP payments and so we must regenerate them.
2384 --
2385 l_proc varchar2 (72) := g_package||'stoppage_control';
2386 --
2387 begin
2388 --
2389 hr_utility.set_location (l_proc,1);
2390 --
2391 if p_maternity_id is not null
2392 then
2393 ins_ssp_temp_affected_rows_mat(p_maternity_id, p_deleting => FALSE);
2394 end if;
2395 --
2396 hr_utility.set_location (l_proc,100);
2397 --
2398 end stoppage_control;
2399 --
2400 --------------------------------------------------------------------------------
2401 function get_max_SMP_date(p_maternity_id in number) return date is
2402 l_due_date date;
2403 l_mpp_date date;
2404 l_max_mpp number;
2405
2406 cursor get_person_details is
2407 select mpp_start_date, due_date
2408 from ssp_maternities
2409 where maternity_id = p_maternity_id;
2410
2411 cursor get_maximum_mpp is
2412 select to_number(element_information4)
2413 from pay_element_types_f
2414 where element_name = c_SMP_element_name
2415 and l_due_date between effective_start_date and effective_end_date;
2416 begin
2417 open get_person_details;
2418 fetch get_person_details into l_mpp_date, l_due_date;
2419 close get_person_details;
2420
2421 open get_maximum_mpp;
2422 fetch get_maximum_mpp into l_max_mpp;
2423 close get_maximum_mpp;
2424
2425 if l_mpp_date is not null then
2426 return trunc(l_mpp_date + (l_max_mpp * 7));
2427 else
2428 return l_due_date;
2429 end if;
2430 end;
2431 --------------------------------------------------------------------------------
2432 function get_max_SMP_date(p_maternity_id in number,
2433 p_due_date in date,
2434 p_mpp_date in date) return date is
2435 l_max_mpp number;
2436
2437 cursor get_maximum_mpp is
2438 select to_number(element_information4)
2439 from pay_element_types_f
2440 where element_name = c_SMP_element_name
2441 and p_due_date between effective_start_date and effective_end_date;
2442 begin
2443 open get_maximum_mpp;
2444 fetch get_maximum_mpp into l_max_mpp;
2445 close get_maximum_mpp;
2446
2447 if p_mpp_date is not null then
2448 return trunc(p_mpp_date + (l_max_mpp * 7));
2449 else
2450 return p_due_date;
2451 end if;
2452 end;
2453 --------------------------------------------------------------------------------
2454 end ssp_SMP_pkg;
[Home] [Help]