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