1 package body SSP_SSP_PKG as
2 /* $Header: spsspapi.pkb 120.22 2011/09/23 05:57:05 pbalu ship $
3 +==============================================================================+
4 | Copyright (c) 1994 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +==============================================================================+
8 --
9 Name
10 Statutory Sick Pay Business Process
11 --
12 Purpose
13 To perform calculation of entitlement and payment for SSP purposes.
14 --
15 History
16 11 Sep 95 N Simpson Created
17 19 Sep 95 N Simpson Removed LEL constant and replaced it
18 with ssp_SMP_pkg.c_LEL. Added procedure
19 check_sickness_date_change.
20 27 Oct 95 N Simpson Renamed all ssp objects to begin with
21 ssp_ instead of hr_
22 31 Oct 95 N Simpson Added workaround for 10.6 to check
23 pay_legislation_rules for indication
24 of SSP installation. This fix should
25 be removed from the code after 10.7.
26 See ssp_is_installed function.
27 3 Nov 95 N Simpson Modified function linked_PIW_end_date.
28 The check on whether the cursor
29 returned a row or not was not valid
30 because MAX ensures a row is always
31 returned. Changed the test to look for
32 a value in the return variable.
33 15 Nov 95 N Simpson Removed hr_utility.trace from
34 linked_PIW_end_date because of pragma
35 added to header.
36 16 Nov 95 N Simpson Renamed parameter call to
37 get_entry_details
38 29 Nov 95 N Simpson Added error trapping to
39 get_SSP...element procedures.
40 29 Nov 95 N Simpson Added to_char to convert date pl/sql
41 variables prior to insert into entry
42 values in insert_element_entry calls.
43 29 Nov 95 N Simpson Changed value placed in qualifying days
44 entry value from qualifying days in the
45 calendar week to qualifying days in the
46 absence period.
47 5 Dec 95 N Simpson Removed test for length of absence from
48 cursor PIW because it was restricting
49 unnecessarily to absences longer than
50 the PIW threshhold. If an absence has
51 a linked_absence_id then it must be a
52 PIW anyway, even if it is too short to
53 be a PIW in its own right.
54 9 Feb 96 N Simpson When calculating amount of SSP, add
55 0.004 before rounding instead of 0.0049
56 so that decimal places after the third
57 one are ignored.
58 4 Apr 96 N Simpson Modified absence_is_a_PIW to take
59 account of contiguous absences AFTER
60 the one being checked as well as
61 those prior to it.
62 Modified linked_absence_id to cascade
63 a linked_absence_id to contiguous
64 absences which become part of a PIW
65 by virtue of the insertion of a new
66 absence which takes the total unbroken
67 absence period over the threshhold.
68 9 Apr 96 N Simpson New procedure update_linked_absence_IDs
69 24 Aug 96 C Barbieri Changed the select stmt in the
70 csr_missing_links cursor definition in
71 order to use index per_absence_attendan
72 ces_fk4.
73 13 Nov 96 C Barbieri Bug. 418895: changed the check about the
74 SSP entitlement. Now when an absence is
75 entered that is not in a PIW, we delete
76 all the old entries and stoppages.
77 21 Jan 97 C Barbieri Bg 441738: Changed this_week.date_earned
78 to pick up the absence start_date,
79 instead of the previous Sunday.
80 Change List
81 ===========
82 Version Date BugNo Author Comment
83 -------+----------+----------+----------+-------------------------------------
84 30.42 02-May-97 467870 Khabibul Changes in check_for_break_in_linked_PIW
85 csr_previous_absence. Removed NVL around
86 the indexed column and replaced it with
87 'OR'. The sql is then more efficient,
88 with no full table scan.
89 ***Same fix was applied to version 30.41
90 which was a backport of Prod14 (taken
91 from 30.12) ****
92 This is the latest version of package
93 for any latest Prod releases. ****
94 Also added new 'Change List' header to
95 comply with PKM standards.
96 30.43 09-jun-97 502108 RThirlby Forced SSP_rate_figure to be based
97 on the sickness_start_date instead
98 of the PIW_start_date.
99 30.44 18-jul-97 513292 RThirlby Altered qualifying_days_in_period to
100 return default BG qualifying pattern
101 if a person has a default as well as
102 personal pattern at any time during
103 their absence (linked or not).
104 30.45 12-Sep-97 550269 RThirlby Explicitly open csr_qualifying_pattern
105 to prevent invalid_cursor error.
106 30.46 29-Sep-97 504386 RThirlby Call qualifying_days_in_period for the
107 absence as a whole so that the pl/sql
108 table in hr_calendar_pkg is populated
109 once for the whole sickness absence.
110 30.47 03-Dec-97 589806 RThirlby Made allowance for open ended absence
111 in call to qualifying_days_in_period.
112 30.48 05-Dec-97 593097 RThirlby Forces end_date to be the next
113 Saturday, after the Sunday before
114 the sickness_start_date.
115 30.49 11-dec-97 555505 RThirlby Added <= rather than < so that one
116 day absences are accounted for.
117 30.50 24-mar-98 563202 RThirlby Performance fix on cursor PIW and
118 csr_qualifying_pattern.
119 30.51 04-Jun-98 668368 AMyers Change so that previous stoppages are
120 retrieved if the id is either the PIW
121 OR the absence id and actioned
122 30.52 19-Jun-98 563202 AParkes Re-wrote absence_is_a_PIW to avoid
123 calling unnecessary sql.
124 Changed csr_existing_entries to avoid
125 using dt views and stop calling
126 ssp_smp_support_pkg.value
127 Added history for 30.51
128 30.53 19-AUG-98 563202 A.Myers Loads of changes to improve efficiency,
129 /701750 such as using pl/sql tables, creating
130 calendar for the whole PIW, not just
131 for each absence. Also added standard
132 trace.
133 110.13 16-AUG-99 886707 MVilrokx The initialisation of l_stopage_found
134 parameter was not correct. Moved it
135 inside the loop so it is reset every
136 time. This assurs that a wating days
137 are still generated after a user
138 created stoppage.
139 115.9 08-NOV-99 1020757 MVilrokx Fixed two problems with date conversion,
140 one in the cursor csr_existing_entries
141 and twice when calling insert_element_entry
142 115.10 04-AUG-00 1320737 DFoster Amended the logic of the
143 check_disqualifying_period which was
144 causing the update of the MPP start
145 date to be calculated incorrectly where
146 a period of sickness went over the sixth
147 week prior to the EWC
148 115.11 14-AUG-00 1304683 DFoster Amended the check_average_earnings
149 procedure so that it calls the new
150 version of the procedure to calculate
151 average earnings so that Sicknesses
152 and Maternities can be dealt with
153 differently.
154 115.14 31-JUL-01 1754802 GButler Changed ssp_is_installed procedure.
155 Procedure was checking PAY_LEGISLATION_
156 RULES table for evidence that ssp was
157 installed. This check was made obsolete
158 in 10.7 and was causing incorrect results
159 to be returned when checking for SSP
160 installation
161 115.16 22-FEB-02 1310170 ABhaduri Added cursor csr_previous_reason to populate
162 the ssp_smp_support_pkg.reason_for_no_earnings
163 variable when the employee is rehired and the
164 absence is getting saved for the second time.
165 115.21 05-NOV-02 2620894 srjadhav Changed the code so that MPP start date is
166 the next day after first working day
167 of (EWC - sickness_trigger_weeks) week.
168 115.22 12-DEC-02 AMills nocopy addition.
169 115.23 27-JAN-03 GButler further nocopy change to check_average
170 _earnings to include 2nd dummy variable
171 to act as placeholder for OUT param from
172 ssp_ern_ins
173 115.24 03-FEB-03 ABlinko added further restriction to maternity_details
174 cursor. Only returns records with a leave_type
175 of MA or null
176 115.25 12-MAY-03 GButler Changes for SSP Legislative Requirements 2003.
177 Commented out stoppage code for Fixed Contract Too
178 Short stoppage and changed check_service procedure
179 accordingly. Also added new check_link_letter
180 procedure
181 115.26 13-JUN-03 GButler Bug 2984845 - changes to do_PIW_calculations to
182 fix issue where PIW lasted max time stoppage not
183 being raised correctly
184 115.27 21-JUL-03 ASENGAR BUG 2984577- Added exception evidence_not_available
185 to the procedure ENTITLED_TO_SSP to raise
186 exception when sickness end date is not mentioned.
187
188 115.28 07-AUG-03 SSEKHAR BUG 2984458- Added another column to csr_person
189 cursor(leaving_reason) to give a stoppage as
190 'Employee Died' when the employee has deceased.
191
192 115.29 08-AUG-03 SSEKHAR BUG 2984458- The check_sql utility was not run
193 last time when the file was arcsed in.
194 115.32 21-OCT-03 ASENGAR BUG 2984577- Added code to handle linked absences
195 when evidence is not present.
196 115.33 24-OCT-03 ABLINKO BUG 3208325- Removed hardcoded SATURDAY and
197 SUNDAY references
198 115.34 03-DEC-03 SSEKHAR BUG 2984458 - Added condition to check if the
199 Employee is deceased before raising the exception
200 piw_max_length_reached
201 115.35 02-MAR-04 ABLINKO BUG 3456918 - Added rtrim when deriving
202 l_saturday and l_sunday
203 115.36 22-MAR-04 3466672 ABLINKO Added procedure medical_control
204 115.37 08-JUL-04 3750125 ABLINKO Added g_absence_del to limit use of
205 medical_control during absence deletion
206 115.38 12-JUL-04 ABLINKO check_evidence now resets last_entitled_day to
207 stoppage start date - 1
208 115.39 21-JUL-04 3769536 ABLINKO Amended person_control to work with open-ended
209 absences
210 115.40 12-APR-06 5126163 KTHAMPAN Amended do_PIW_calculation to check for total_SSP_weeks
211 115.41 04-AUG-06 5444012 KTHAMPAN Amended do_PIW_calculation to reset days_remaining
212 115.42 23-AUG-06 5482199 KTHAMPAN Statutory Changes for 2007.
213 115.45 25-AUG-06 5482199 KTHAMPAN Amend function check_average_earnings to
214 update the effective_date of the earnings calculations
215 for employee over 65.
216 Also change function check_employee_too_old to
217 check for stoppage on the main absence.
218 115.46 31-AUG-06 5504380 KTHAMPAN Added overload function linked_piw_start_date
219 and linked_piw_end_date.
220 115.49 06-SEP-06 5510601 KTHAMPAN Amend linked_PIW_start_date/end_date functions.
221 Added overload function linked_PIW_start_date,
222 linked_PIW_end_date and absence_is_a_PIW.
223 5517272 KTHAMPAN Amend check_age to pass the least of PIW_end_date
224 and 1 0ctober 2006 to the stoppage end date.
225 115.51 08-SEP-06 5517272 KTHAMPAN Amend the check_age to use the absence end date
226 instead of 1 October 2006.
227 115.52 09-SEP-06 5517272 HWINSOR Changed nocopy declaration
228 115.53 19-SEP-06 5550795 KTHAMPAN Amend the check_age
229 115.54 12-OCT-06 5583730 NPERSHAD Modified the function absence_is_a_PIW with 4 parameters.
230 115.55 09-DEC-06 5706912 KTHAMPAN Amend procedure ins_ssp_temp_affected_rows_PIW,
231 update_linked_absence_ids and person_control
232 115.56 19-MAR-07 5932995 HWINSOR Arcs in KTHAMPAN changes. Employees starting
233 post 1-Oct-06 have correct PIW start dates.
234 115.57 23-DEC-07 6658285 PBALU Changed a For loop in qualifying_days_in_period to use
235 PL/sql table instead of cursor as the cursor is opened/closed
236 inside the for loop.
237 115.58 19-Sep-08 6860926 EMUNISEK Changed the less than zero condition on weeks_remaining to
238 account for the rounding error of decimal values
239 115.60 03-Feb-09 7688727 NPANNAMP When creating SSP correction element previously LSP was passed
240 now it is changed to Final Process date similar to SMP/SAP corrections
241 115.61 09-Apr-09 8356706 NPANNAMP variables l_saturday, l_sunday moved from package body level to
242 ENTITLED_TO_SSP function variables, to avoid issues of package state
243 not resetting when used in OA Pages.
244 115.62 23-Sep-11 12552547 PBALU Sickness absence should not be linked with absences taken in different period of service.
245 ===============================================================================
246 */
247 g_package constant varchar2 (31) := 'SSP_SSP_pkg.';
248 g_SSP_is_installed boolean;
249 g_SSP_legislation ssp_SSP_pkg.csr_SSP_element_details%rowtype;
250 g_SSP_correction ssp_SSP_pkg.csr_SSP_element_details%rowtype;
251 g_PIW_id number := null;
252 g_absence_del varchar2(1) := 'N';
253 PIW_start_date date;
254 PIW_end_date date;
255 all_pat_calendar_days number;
256 perf_start_date date;
257 perf_end_date date;
258 /* Bug Fix 8356706 Begin
259 l_saturday varchar2(100) := rtrim(to_char(to_date('06/01/2001','DD/MM/YYYY'),'DAY'));
260 l_sunday varchar2(100) := rtrim(to_char(to_date('07/01/2001','DD/MM/YYYY'),'DAY'));
261 -- These variables moved inside of ENTITLED_TO_SSP function
262 Bug Fix 8356706 End */
263 --
264 attempt_to_add_to_end_of_time exception;
265 pragma exception_init (attempt_to_add_to_end_of_time,-01841);
266 --
267 -- Bug 2984458 Column leaving_reason has been included in this cursor
268 cursor csr_person (
269 --
270 -- Personal details of absentee, including their current period of
271 -- service, their age etc.
272 --
273 p_date_start in date,
274 p_person_id in number) is
275 --
276 select person.date_of_birth,
277 person.date_of_death,
278 person.person_id,
279 person.sex,
280 person.full_name,
281 service.date_start,
282 service.prior_employment_SSP_weeks,
283 service.prior_employment_SSP_paid_to,
284 nvl (service.projected_termination_date,
285 hr_general.end_of_time) PROJECTED_TERMINATION_DATE,
286 nvl (service.actual_termination_date,
287 hr_general.end_of_time) ACTUAL_TERMINATION_DATE,
288 nvl (service.last_standard_process_date,
289 hr_general.end_of_time) LAST_STANDARD_PROCESS_DATE,
290 /* 7688727 begin */
291 nvl (service.final_process_date,
292 hr_general.end_of_time) FINAL_PROCESS_DATE,
293 /* 7688727 End */
294 service.leaving_reason
295 from per_all_people_f PERSON,
296 per_periods_of_service SERVICE
297 where person.person_id = p_person_id
298 and service.person_id = person.person_id
299 and p_date_start between service.date_start
300 and nvl (service.actual_termination_date,
301 hr_general.end_of_time);
302 --
303 absentee csr_person%rowtype;
304 --
305 cursor csr_absence_details (p_absence_attendance_id in number) is
306 --
307 -- Get the details of an absence, given its ID.
308 --
309 select *
310 from per_absence_attendances
311 where absence_attendance_id = p_absence_attendance_id;
312 --
313 g_absence per_absence_attendances%rowtype;
314 --
315 cursor PIW is
316 --
317 -- Get all the PIWs which link to this one
318 --
319 -- 563202 nvl removed from the where clause as was implicitly causing the
320 -- index to be removed.
321 select sickness_start_date,
322 nvl(sickness_end_date,hr_general.end_of_time) sickness_end_date,
323 business_group_id,
324 absence_attendance_id,
325 pregnancy_related_illness
326 from per_absence_attendances
327 where (g_PIW_id = absence_attendance_id and linked_absence_id is null)
328 or g_PIW_ID = linked_absence_id
329 order by sickness_start_date;
330 --
331 type number_table is table of number index by binary_integer;
332 type date_table is table of date index by binary_integer;
333 type varchar_table is table of varchar2 (255) index by binary_integer;
334 --
335 type SSP_info is record (
336 --
337 element_entry_id number_table,
338 effective_start_date date_table,
339 effective_end_date date_table,
340 element_link_id number_table,
341 assignment_id number_table,
342 date_from date_table,
343 date_to date_table,
344 amount number_table,
345 rate number_table,
346 qualifying_days number_table,
347 SSP_days_due number_table,
348 withheld_days number_table,
349 SSP_weeks number_table,
350 dealt_with varchar_table);
351 --
352 -- A store for all the SSP entries that may be granted to the absentee.
353 --
354 new_entry SSP_info;
355 empty_record SSP_info; -- Used to initialise SSP_info records
356 --------------------------------------------------------------------------------
357 procedure get_absence_details (p_absence_attendance_id in number) is
358 --
359 -- Get the details of the absence from the absence id passed in.
360 --
361 l_proc varchar2 (72) := g_package||'get_absence_details';
362 --
363 procedure check_parameters is
364 --
365 begin
366 --
367 hr_utility.trace (l_proc||' p_absence_attendance_id = ' ||
368 to_char (p_absence_attendance_id));
369 --
370 hr_api.mandatory_arg_error (
371 p_api_name => l_proc,
372 p_argument => 'absence_attendance_id',
373 p_argument_value=> p_absence_attendance_id);
374 --
375 end check_parameters;
376 --
377 begin
378 --
379 hr_utility.set_location('Entering:'||l_proc,1);
380 --
381 check_parameters;
382 --
383 open csr_absence_details (p_absence_attendance_id);
384 fetch csr_absence_details into g_absence;
385 --
386 if csr_absence_details%notfound
387 then
388 --
389 -- The cursor unexpectedly returned no rows.
390 --
391 close csr_absence_details;
392 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
393 fnd_message.set_token ('PROCEDURE','ssp_ssp_pkg.get_absence_details');
394 fnd_message.set_token ('STEP','1');
395 fnd_message.raise_error;
396 else
397 close csr_absence_details;
398 end if;
399 --
400 hr_utility.set_location('Leaving :'||l_proc,100);
401 --
402 end get_absence_details;
403 --------------------------------------------------------------------------------
404 function Standard_week_fraction (
405 --
406 -- Returns the legislative standard fraction to be used for a specified number
407 -- of qualifying days in a week.
408 --
409 p_qualifying_days in integer)
410 --
411 return number is
412 --
413 standard_fraction number;
414 l_proc varchar2 (72) := g_package||'Standard_week_fraction';
415 --
416 procedure check_parameters is
417 --
418 all_parameters_valid constant boolean := (p_qualifying_days is not null
419 and p_qualifying_days in (1,2,3,4,5,6,7));
420 --
421 begin
422 --
423 hr_utility.trace (l_proc||' p_qualifying_days = ' ||
424 to_char (p_qualifying_days));
425 --
426 hr_general.assert_condition (all_parameters_valid);
427 --
428 end check_parameters;
429 --
430 begin
431 --
432 hr_utility.set_location('Entering:'||l_proc,1);
433 --
434 check_parameters;
435 --
436 if p_qualifying_days = 1 then standard_fraction := 1;
437 elsif p_qualifying_days = 2 then standard_fraction := 0.5;
438 elsif p_qualifying_days = 3 then standard_fraction := 0.334;
439 elsif p_qualifying_days = 4 then standard_fraction := 0.25;
440 elsif p_qualifying_days = 5 then standard_fraction := 0.2;
441 elsif p_qualifying_days = 6 then standard_fraction := 0.167;
442 elsif p_qualifying_days = 7 then standard_fraction := 0.143;
443 end if;
444 --
445 hr_utility.trace (l_proc||' standard_fraction = '||to_char(standard_fraction));
446 hr_utility.set_location('Leaving :'||l_proc,100);
447 --
448 return standard_fraction;
449 --
450 end standard_week_fraction;
451 --------------------------------------------------------------------------------
452 procedure get_SSP_element (p_effective_date in date) is
453 --
454 -- Get the SSP element legislative parameters
455 --
456 begin
457 --
458 if g_SSP_legislation.element_type_id is null or
459 p_effective_date not between g_SSP_legislation.effective_start_date
460 and g_SSP_legislation.effective_end_date
461 then
462 --
463 -- If we have not already done so, do the following:
464 --
465 open ssp_SSP_pkg.csr_SSP_element_details (p_effective_date);
466 fetch ssp_SSP_pkg.csr_SSP_element_details into g_SSP_legislation;
467 close ssp_SSP_pkg.csr_SSP_element_details;
468 end if;
469 --
470 /* Commented out to comply with pragma
471 -- Check element returned expected values (this is belt and braces checking
472 -- because if the check fails then it was seed data which has been incorrectly
473 -- set up. However, failing here may prevent more inexplicable errors
474 -- elsewhere).
475 --
476 if g_SSP_legislation.element_type_id is null
477 then
478 fnd_message.set_name ('SSP', 'SSP_35077_NO_SSP_ELEMENT');
479 fnd_message.set_token ('EFFECTIVE_DATE',p_effective_date);
480 fnd_message.raise_error;
481 else
482 --
483 -- The element type exists
484 --
485 if (g_SSP_legislation.SSP_DAYS_DUE_ID is null
486 or g_SSP_legislation.AMOUNT_ID is null
487 or g_SSP_legislation.FROM_ID is null
488 or g_SSP_legislation.TO_ID is null
489 or g_SSP_legislation.RATE_ID is null
490 or g_SSP_legislation.WITHHELD_DAYS_ID is null
491 or g_SSP_legislation.SSP_WEEKS_ID is null
492 or g_SSP_legislation.QUALIFYING_DAYS_ID is null)
493 then
494 --
495 -- One of the input values is missing.
496 --
497 fnd_message.set_name ('SSP','SSP_36071_INCORRECT_INPUT_VALS');
498 fnd_message.set_token ('ELEMENT_NAME',c_SSP_element_name);
499 fnd_message.raise_error;
500 end if;
501 --
502 if (g_SSP_legislation.MAXIMUM_SSP_PERIOD is null
503 or g_SSP_legislation.MAXIMUM_AGE is null
504 or g_SSP_legislation.LINKING_PERIOD_DAYS is null
505 or g_SSP_legislation.WAITING_DAYS is null
506 or g_SSP_legislation.PIW_THRESHHOLD is null
507 or g_SSP_legislation.MAXIMUM_LINKED_PIW_YEARS is null
508 or g_SSP_legislation.PERCENTAGE_THRESHHOLD is null)
509 then
510 --
511 -- One of the DDF segments has not been entered.
512 --
513 fnd_message.set_name ('SSP','SSP_36070_MISSING_DDF_SEGMENT');
514 fnd_message.set_token ('ELEMENT_NAME',c_SSP_element_name);
515 fnd_message.raise_error;
516 end if;
517 end if;
518 --
519 */
520 end get_SSP_element;
521 --------------------------------------------------------------------------------
522 procedure get_SSP_correction_element (p_effective_date in date) is
523 --
524 -- Get the SSP element legislative parameters
525 --
526 l_proc varchar2 (72) := g_package||'get_SSP_correction_element';
527 --
528 procedure check_parameters is
529 --
530 begin
531 --
532 hr_utility.trace (l_proc||' p_effective_date = ' ||
533 to_char (p_effective_date));
534 --
535 hr_api.mandatory_arg_error (
536 p_api_name => l_proc,
537 p_argument => 'effective_date',
538 p_argument_value=> p_effective_date);
539 --
540 end check_parameters;
541 --
542 begin
543 --
544 hr_utility.set_location('Entering:'||l_proc,1);
545 --
546 check_parameters;
547 --
548 hr_utility.set_location(l_proc,2);
549 if g_SSP_correction.element_type_id is null or
550 p_effective_date not between g_SSP_correction.effective_start_date
551 and g_SSP_correction.effective_end_date
552 then
553 --
554 -- If we have not already done so, do the following:
555 --
556 hr_utility.set_location(l_proc,3);
557 open ssp_SSP_pkg.csr_SSP_element_details (p_effective_date,
558 c_SSP_correction_element_name);
559 fetch ssp_SSP_pkg.csr_SSP_element_details into g_SSP_correction;
560 close ssp_SSP_pkg.csr_SSP_element_details;
561 end if;
562 hr_utility.trace(l_proc||' g_SSP_correction.element_type_id :'||g_SSP_correction.element_type_id);
563 hr_utility.trace(l_proc||' g_SSP_correction.effective_start_date :'||g_SSP_correction.effective_start_date);
564 hr_utility.trace(l_proc||' g_SSP_correction.effective_end_date :'||g_SSP_correction.effective_end_date);
565 hr_utility.trace(l_proc||' g_SSP_correction.MAXIMUM_SSP_PERIOD :'||g_SSP_correction.MAXIMUM_SSP_PERIOD);
566 hr_utility.trace(l_proc||' g_SSP_correction.MAXIMUM_AGE :'||g_SSP_correction.MAXIMUM_AGE);
567 hr_utility.trace(l_proc||' g_SSP_correction.LINKING_PERIOD_DAYS :'||g_SSP_correction.LINKING_PERIOD_DAYS);
568 hr_utility.trace(l_proc||' g_SSP_correction.WAITING_DAYS :'||g_SSP_correction.WAITING_DAYS);
569 hr_utility.trace(l_proc||' g_SSP_correction.PIW_THRESHHOLD :'||g_SSP_correction.PIW_THRESHHOLD);
570 hr_utility.trace(l_proc||' g_SSP_correction.MAXIMUM_LINKED_PIW_YEARS :'||g_SSP_correction.MAXIMUM_LINKED_PIW_YEARS);
571 hr_utility.trace(l_proc||' g_SSP_correction.PERCENTAGE_THRESHHOLD :'||g_SSP_correction.PERCENTAGE_THRESHHOLD);
572 hr_utility.trace(l_proc||' g_SSP_correction.SSP_DAYS_DUE_ID :'||g_SSP_correction.SSP_DAYS_DUE_ID);
573 hr_utility.trace(l_proc||' g_SSP_correction.AMOUNT_ID :'||g_SSP_correction.AMOUNT_ID);
574 hr_utility.trace(l_proc||' g_SSP_correction.FROM_ID :'||g_SSP_correction.FROM_ID);
575 hr_utility.trace(l_proc||' g_SSP_correction.TO_ID :'||g_SSP_correction.TO_ID);
576 hr_utility.trace(l_proc||' g_SSP_correction.RATE_ID :'||g_SSP_correction.RATE_ID);
577 hr_utility.trace(l_proc||' g_SSP_correction.WITHHELD_DAYS_ID :'||g_SSP_correction.WITHHELD_DAYS_ID);
578 hr_utility.trace(l_proc||' g_SSP_correction.SSP_WEEKS_ID :'||g_SSP_correction.SSP_WEEKS_ID);
579 hr_utility.trace(l_proc||' g_SSP_correction.QUALIFYING_DAYS_ID :'||g_SSP_correction.QUALIFYING_DAYS_ID);
580 hr_utility.trace(l_proc||' g_SSP_correction.input_currency_code :'||g_SSP_correction.input_currency_code);
581
582 --
583 -- Check element returned expected values (this is belt and braces checking,
584 -- if the check fails then it was seed data which has been incorrectly set up.
585 -- However, failing here may prevent more inexplicable errors elsewhere.
586 --
587 if g_SSP_correction.element_type_id is null
588 then
589 fnd_message.set_name ('SSP', 'SSP_35078_NO_SSP_CORR_ELEMENT');
590 fnd_message.set_token ('EFFECTIVE_DATE',p_effective_date);
591 fnd_message.raise_error;
592 else
593 --
594 -- The element type exists
595 --
596 if (g_SSP_correction.SSP_DAYS_DUE_ID is null
597 or g_SSP_correction.AMOUNT_ID is null
598 or g_SSP_correction.FROM_ID is null
599 or g_SSP_correction.TO_ID is null
600 or g_SSP_correction.RATE_ID is null
601 or g_SSP_correction.WITHHELD_DAYS_ID is null
602 or g_SSP_correction.SSP_WEEKS_ID is null
603 or g_SSP_correction.QUALIFYING_DAYS_ID is null)
604 then
605 --
606 -- One of the input values is missing.
607 --
608 fnd_message.set_name ('SSP','SSP_36071_INCORRECT_INPUT_VALS');
609 fnd_message.set_token ('ELEMENT_NAME',c_SSP_element_name);
610 fnd_message.raise_error;
611 end if;
612 --
613 if (g_SSP_correction.MAXIMUM_SSP_PERIOD is null
614 or g_SSP_correction.MAXIMUM_AGE is null
615 or g_SSP_correction.LINKING_PERIOD_DAYS is null
616 or g_SSP_correction.WAITING_DAYS is null
617 or g_SSP_correction.PIW_THRESHHOLD is null
618 or g_SSP_correction.MAXIMUM_LINKED_PIW_YEARS is null
619 or g_SSP_correction.PERCENTAGE_THRESHHOLD is null)
620 then
621 --
622 -- One of the DDF segments has not been entered.
623 --
624 fnd_message.set_name ('SSP','SSP_36070_MISSING_DDF_SEGMENT');
625 fnd_message.set_token ('ELEMENT_NAME',c_SSP_element_name);
626 fnd_message.raise_error;
627 end if;
628 end if;
629 --
630 hr_utility.set_location('Leaving :'||l_proc,100);
631 --
632 end get_SSP_correction_element;
633 --------------------------------------------------------------------------------
634 procedure update_linked_absence_ids is
635 --
636 -- Updates linked_absence_IDs where the absence has either become part of a
637 -- series or dropped out of a series due to date changes on the absence.
638 --
639 l_proc constant varchar2 (72) := g_package||'update_linked_absence_ids';
640 l_new_linked_absence_id number (15) := null;
641 absence_links_but_shouldnt boolean := FALSE;
642 absence_doesnt_link_but_should boolean := FALSE;
643 --
644 cursor csr_missing_links is
645 --
646 -- Get all the absences affected by the current transaction.
647 --
648 select absence.*
649 from per_absence_attendances ABSENCE,
650 ssp_temp_affected_rows TRANSACTION
651 where
652 ( (transaction.PIW_ID = absence.absence_attendance_id and
653 absence.linked_absence_id IS NULL)
654 or
655 transaction.piw_id = absence.linked_absence_id)
656 and transaction.locked = to_char(userenv('sessionid'));
657 --
658 begin
659 --
660 hr_utility.set_location('Entering:'||l_proc,1);
661 --
662 for new_link in csr_missing_links
663 LOOP
664 --
665 -- For each absence affected by the current transaction, check that it has
666 -- the correct linked_absence_id.
667 --
668 -- Get the linked_absence_id the absence SHOULD have.
669 l_new_linked_absence_id := ssp_ssp_pkg.linked_absence_id (
670 new_link.person_id,
671 new_link.sickness_start_date,
672 new_link.sickness_end_date);
673 --
674 absence_links_but_shouldnt := (l_new_linked_absence_id is null and
675 new_link.linked_absence_id is not null);
676 --
677 absence_doesnt_link_but_should := (l_new_linked_absence_id is not null and
678 new_link.linked_absence_id is null);
679 --
680 if (absence_links_but_shouldnt or absence_doesnt_link_but_should)
681 then
682 --
683 -- update the absence's linked absence ID to the calculated one
684 --
685 update per_absence_attendances
686 set linked_absence_id = l_new_linked_absence_id
687 where absence_attendance_id = new_link.absence_attendance_id;
688 --
689 if l_new_linked_absence_id is not null
690 --
691 -- if the absence has been updated from being a whole PIW series in
692 -- its own right to being just one of another series.
693 then
694 --
695 -- Update the list of PIWs affected by the current transaction to
696 -- reflect the PIW series actually affected.
697 --
698 update ssp_temp_affected_rows
699 set PIW_ID = l_new_linked_absence_id
700 where PIW_ID = new_link.absence_attendance_id;
701 end if;
702 end if;
703 end loop;
704 --
705 hr_utility.set_location('Leaving :'||l_proc, 100);
706 --
707 end update_linked_absence_ids;
708 --------------------------------------------------------------------------------
709 --
710 -- Return the absence_attendance_id of the first absence in a series of
711 -- sickness absences which are separated by less than a defined period.
712 --
713 function linked_absence_id(p_person_id in number,
714 p_sickness_start_date in date,
715 p_sickness_end_date in date) return number is
716 l_proc varchar2 (72) := g_package||'linked_absence_id';
717 --
718 -- Get all sickness absences prior to the current one, that do not have
719 -- a linked_absence_id and are not the start absence of the PIW.
720 cursor csr_contiguous_absences is
721 select absence_attendance_id,
722 sickness_start_date,
723 sickness_end_date
724 from per_absence_attendances
725 where person_id = p_person_id
726 and sickness_start_date is not null
727 and sickness_start_date < p_sickness_start_date
728 and linked_absence_id is null
729 order by sickness_start_date desc
730 for update;
731 --
732 cursor csr_linked_absence (x_sickness_start_date date) is
733 select absence_attendance_id
734 from per_absence_attendances
735 where sickness_start_date = x_sickness_start_date
736 and person_id = p_person_id;
737 --
738 l_PIW_id number := null;
739 l_PIW_start date := null;
740 l_current_sickness_start date := p_sickness_start_date;
741 --
742 procedure check_parameters is
743 all_parameters_valid constant boolean :=
744 (p_person_id is not null and
745 p_sickness_start_date <= p_sickness_end_date and
746 p_sickness_start_date = trunc (p_sickness_start_date) and
747 p_sickness_end_date = trunc (p_sickness_end_date));
748 begin
749 hr_utility.trace(l_proc||' p_person_id = '||to_char (p_person_id));
750 hr_utility.trace(l_proc||' p_sickness_start_date = '||to_char (p_sickness_start_date));
751 hr_utility.trace(l_proc||' p_sickness_end_date = '||to_char (p_sickness_end_date));
752 hr_general.assert_condition (all_parameters_valid);
753 end check_parameters;
754 begin
755 --
756 hr_utility.set_location('Entering:'||l_proc,1);
757 --
758 check_parameters;
759 --
760 -- If the sickness start date is null then this cannot be a sickness absence.
761 -- If SSP is not installed then this functionality is not available.
762 if p_sickness_start_date is null or (not SSP_is_installed) then
763 -- Return null as no linked absence is possible.
764 l_PIW_id := null;
765 else
766 -- Get the absence_attendance_id of the first PIW in the series
767 l_PIW_start := (ssp_ssp_pkg.linked_PIW_start_date(p_person_id => p_person_id,
768 p_sickness_start_date => p_sickness_start_date,
769 p_sickness_end_date => p_sickness_end_date));
770 -- if there is a PIW start date
771 -- and the PIW start date is not the start date for the current absence
772 if l_PIW_start is not null and l_PIW_start <> p_sickness_start_date then
773 open csr_linked_absence (l_PIW_start);
774 fetch csr_linked_absence into l_PIW_id;
775 close csr_linked_absence;
776 --
777 -- It is possible to enter separate but contiguous sickness absences. That
778 -- means that a series of absences could be entered consisting of four,
779 -- one-day absences. Each individual day would not itself comprise a PIW,
780 -- but taken together, a PIW is formed. Since absences must be entered in
781 -- chronological order, this would be detected on entry of the fourth one.
782 -- However, if the PIW thus formed links to a previous absence, then the
783 -- PIW ID must be applied to all four of the absences, not just the fourth
784 -- one as it is inserted.
785 for previous in csr_contiguous_absences loop
786 -- previous absence is not in a contiguous series with the current one
787 -- or the previous absence is the first in the current PIW
788 exit when(previous.sickness_end_date <> l_current_sickness_start - 1 or
789 previous.absence_attendance_id = l_PIW_ID);
790 --
791 -- Set the PIW ID of the previous absence the same as the current one
792 update per_absence_attendances
793 set linked_absence_id = l_PIW_ID
794 where current of csr_contiguous_absences;
795 --
796 l_current_sickness_start := previous.sickness_start_date;
797 end loop;
798 end if;
799 end if;
800 --
801 hr_utility.set_location('Leaving :'||l_proc||', l_PIW_id = '||to_char (l_PIW_id),100);
802 --
803 return l_PIW_id;
804 --
805 end linked_absence_id;
806 --------------------------------------------------------------------------------
807 --
808 -- Returns TRUE if the parameter absence constitutes a PIW in its own right
809 -- or if it forms part of a contiguous set of rows which together form a PIW
810 --
811 -- NB Do not add hr_utility calls in here because this function is used by
812 -- other functions which must maintain purity level WNDS, WNPS.
813 --
814 function absence_is_a_PIW(p_person_id number,
815 p_sickness_start_date date,
816 p_sickness_end_date date) return boolean is
817 --
818 --select the number of days, within the period specified,
819 --for which the person has absences
820 cursor csr_abs_days(p_start date, p_end date) is
821 select nvl(sum(nvl(least(sickness_end_date,p_end),p_end) -
822 greatest(sickness_start_date,p_start) + 1),0)
823 from per_absence_attendances
824 where person_id = p_person_id
825 and sickness_start_date <= p_end
826 and nvl(sickness_end_date,hr_general.end_of_time) >= p_start;
827 --
828 cursor csr_contig_abs(p_start date, p_end date,
829 p_start2 date, p_end2 date) is
830 -- select the dates for the continuous absence formed by the parameter
831 -- absence and any absences adjacent to it.
832 select least(min(sickness_start_date),p_start),
833 greatest(max(nvl(sickness_end_date,hr_general.end_of_time)),p_end)
834 from per_absence_attendances
835 where person_id = p_person_id
836 and (sickness_end_date = p_start2 or sickness_start_date = p_end2);
837 --
838 l_days_absent number := 0;
839 l_prev_sickness_start date := null;
840 l_prev_sickness_end date := null;
841 l_sickness_start_date date := p_sickness_start_date;
842 l_sickness_end_date date := nvl (p_sickness_end_date,hr_general.end_of_time);
843 l_absence_is_a_PIW boolean := FALSE;
844 --
845 begin
846 get_SSP_element (p_sickness_start_date);
847 if (l_sickness_end_date - p_sickness_start_date) + 1 >= g_SSP_legislation.PIW_threshhold then
848 l_absence_is_a_PIW := TRUE; -- The absence is a PIW in its own right
849 else
850 -- Check if the absence has contiguous absences which effectively
851 -- extend the sickness start and end dates (and thus may cause a PIW to
852 -- be formed).
853 <<loop_form_PIW>>
854 loop
855 l_prev_sickness_start := l_sickness_start_date;
856 l_prev_sickness_end := l_sickness_end_date;
857
858 -- Find contiguous absences which may contribute to a PIW
859 open csr_contig_abs(l_prev_sickness_start, l_prev_sickness_end,
860 l_prev_sickness_start - 1, l_prev_sickness_end + 1);
861 fetch csr_contig_abs into l_sickness_start_date,
862 l_sickness_end_date;
863
864 --No contiguous absences (null returned by max function)
865 if l_sickness_start_date is null then
866 exit loop_form_PIW;
867 elsif (l_sickness_end_date - l_sickness_start_date) +1
868 >= g_SSP_legislation.PIW_threshhold then
869 -- The absence formed is a PIW
870 l_absence_is_a_PIW := TRUE; -- The absence formed is a PIW
871 exit loop_form_PIW;
872 elsif l_prev_sickness_end = l_sickness_end_date then
873 -- There is no contiguous future absence so look for number of days
874 -- absent within threshold period before PIW formed so far
875 open csr_abs_days(l_sickness_end_date - g_SSP_legislation.PIW_threshhold +1,
876 l_sickness_start_date -1);
877 fetch csr_abs_days into l_days_absent;
878 close csr_abs_days;
879 --
880 if (l_sickness_end_date - l_sickness_start_date) +1 +
881 l_days_absent >= g_SSP_legislation.PIW_threshhold then
882 l_absence_is_a_PIW := TRUE; -- The absence formed is a PIW
883 end if;
884 exit loop_form_PIW;
885 elsif l_prev_sickness_start = l_sickness_start_date then
886 -- There is no contiguous past absence so look for number of days
887 -- absent within threshold period after PIW formed so far
888 open csr_abs_days(l_sickness_end_date +1,l_sickness_start_date -1
889 + g_SSP_legislation.PIW_threshhold);
890 fetch csr_abs_days into l_days_absent;
891 close csr_abs_days;
892 if (l_sickness_end_date - l_sickness_start_date) +1 + l_days_absent
893 >= g_SSP_legislation.PIW_threshhold then
894 l_absence_is_a_PIW := TRUE; -- The absence formed is a PIW
895 end if;
896 exit loop_form_PIW;
897 end if;
898 --else two contiguous abs, so search for past and future
899 --contiguous abs again
900 close csr_contig_abs;
901 end loop loop_form_PIW;
902 close csr_contig_abs;
903 end if;
904 --
905 return l_absence_is_a_PIW;
906 end absence_is_a_PIW;
907 --------------------------------------------------------------------------------
908 --
909 -- Overload function for the absence_is_a_PIW
910 function absence_is_a_PIW(p_person_id number,
911 p_sickness_start_date date,
912 p_sickness_end_date date,
913 p_absence_attendance_id number) return boolean is
914 --
915 --select the number of days, within the period specified,
916 --for which the person has absences
917 cursor csr_abs_days(p_start date, p_end date) is
918 select nvl(sum(nvl(least(sickness_end_date,p_end),p_end) -
919 greatest(sickness_start_date,p_start) + 1),0)
920 from per_absence_attendances
921 where person_id = p_person_id
922 and sickness_start_date <= p_end
923 and nvl(sickness_end_date,hr_general.end_of_time) >= p_start;
924 --
925 cursor csr_contig_abs(p_start date, p_end date,
926 p_start2 date, p_end2 date) is
927 -- select the dates for the continuous absence formed by the parameter
928 -- absence and any absences adjacent to it.
929 select least(min(sickness_start_date),p_start),
930 greatest(max(nvl(sickness_end_date,hr_general.end_of_time)),p_end)
931 from per_absence_attendances
932 where person_id = p_person_id
933 and (sickness_end_date = p_start2 or sickness_start_date = p_end2);
934 --
935 cursor csr_linked_id is
936 select linked_absence_id
937 from per_absence_attendances
938 where absence_attendance_id = p_absence_attendance_id;
939 --
940 l_days_absent number := 0;
941 l_linked number := null;
942 l_prev_sickness_start date := null;
943 l_prev_sickness_end date := null;
944 l_sickness_start_date date := p_sickness_start_date;
945 l_sickness_end_date date := nvl (p_sickness_end_date,hr_general.end_of_time);
946 l_absence_is_a_PIW boolean := FALSE;
947 --
948 begin
949 get_SSP_element (p_sickness_start_date);
950 if (l_sickness_end_date - p_sickness_start_date) + 1 >= g_SSP_legislation.PIW_threshhold then
951 l_absence_is_a_PIW := TRUE; -- The absence is a PIW in its own right
952 else
953 open csr_linked_id;
954 fetch csr_linked_id into l_linked;
955 --if csr_linked_id%found then
956 if l_linked is not null then --for bug fix 5583730
957 l_absence_is_a_PIW := TRUE;
958 else
959 -- Check if the absence has contiguous absences which effectively
960 -- extend the sickness start and end dates (and thus may cause a PIW to
961 -- be formed).
962 <<loop_form_PIW>>
963 loop
964 l_prev_sickness_start := l_sickness_start_date;
965 l_prev_sickness_end := l_sickness_end_date;
966
967 -- Find contiguous absences which may contribute to a PIW
968 open csr_contig_abs(l_prev_sickness_start, l_prev_sickness_end,
969 l_prev_sickness_start - 1, l_prev_sickness_end + 1);
970 fetch csr_contig_abs into l_sickness_start_date,
971 l_sickness_end_date;
972
973 --No contiguous absences (null returned by max function)
974 if l_sickness_start_date is null then
975 exit loop_form_PIW;
976 elsif (l_sickness_end_date - l_sickness_start_date) +1
977 >= g_SSP_legislation.PIW_threshhold then
978 -- The absence formed is a PIW
979 l_absence_is_a_PIW := TRUE; -- The absence formed is a PIW
980 exit loop_form_PIW;
981 elsif l_prev_sickness_end = l_sickness_end_date then
982 -- There is no contiguous future absence so look for number of days
983 -- absent within threshold period before PIW formed so far
984 open csr_abs_days(l_sickness_end_date - g_SSP_legislation.PIW_threshhold +1,
985 l_sickness_start_date -1);
986 fetch csr_abs_days into l_days_absent;
987 close csr_abs_days;
988 --
989 if (l_sickness_end_date - l_sickness_start_date) +1 +
990 l_days_absent >= g_SSP_legislation.PIW_threshhold then
991 l_absence_is_a_PIW := TRUE; -- The absence formed is a PIW
992 end if;
993 exit loop_form_PIW;
994 elsif l_prev_sickness_start = l_sickness_start_date then
995 -- There is no contiguous past absence so look for number of days
996 -- absent within threshold period after PIW formed so far
997 open csr_abs_days(l_sickness_end_date +1,l_sickness_start_date -1
998 + g_SSP_legislation.PIW_threshhold);
999 fetch csr_abs_days into l_days_absent;
1000 close csr_abs_days;
1001 if (l_sickness_end_date - l_sickness_start_date) +1 + l_days_absent
1002 >= g_SSP_legislation.PIW_threshhold then
1003 l_absence_is_a_PIW := TRUE; -- The absence formed is a PIW
1004 end if;
1005 exit loop_form_PIW;
1006 end if;
1007 --else two contiguous abs, so search for past and future
1008 --contiguous abs again
1009 close csr_contig_abs;
1010 end loop loop_form_PIW;
1011 close csr_contig_abs;
1012 end if;
1013 end if;
1014 --
1015 return l_absence_is_a_PIW;
1016 end absence_is_a_PIW;
1017 --------------------------------------------------------------------------------
1018 procedure check_sickness_date_change (
1019 --
1020 -- Called from AFTER update of sickness_start_date, sickness_end_date trigger.
1021 -- Prevents update if the change would invalidate the linked_absence_id of
1022 -- either this row or the next one.
1023 --
1024 p_person_id in number,-- :old.person_id
1025 p_linked_absence_id in number,-- :old.linked_absence_id
1026 p_absence_attendance_id in number,-- :old.absence_attendance_id
1027 p_new_sickness_start_date in date,-- :new.sickness_start_date
1028 p_new_sickness_end_date in date, -- :new.sickness_end_date
1029 p_old_sickness_end_date in date -- :old.sickness_end_date
1030 ) is
1031 --
1032 cursor csr_next_absence is
1033 --
1034 -- Get the chronologically next sickness absence
1035 --
1036 select abs.linked_absence_id,
1037 abs.sickness_start_date,
1038 abs.sickness_end_date
1039 from per_absence_attendances ABS
1040 where abs.sickness_start_date is not null
1041 and person_id = p_person_id
1042 and abs.sickness_start_date = (
1043 --
1044 select min (abs2.sickness_start_date)
1045 from per_absence_attendances ABS2
1046 where abs2.sickness_start_date >= p_old_sickness_end_date
1047 and person_id = p_person_id);
1048 --
1049 l_proc varchar2 (72) := g_package||'check_sickness_date_change';
1050 l_next_absence csr_next_absence%rowtype;
1051 PIW_broken exception;
1052 --
1053 procedure check_parameters is
1054 --
1055 all_parameters_valid constant boolean := (p_person_id is not null and
1056 p_absence_attendance_id is not null);
1057 --
1058 begin
1059 --
1060 hr_utility.trace (' p_person_id = '||to_char (p_person_id));
1061 hr_utility.trace (' p_linked_absence_id = '
1062 ||to_char (p_linked_absence_id));
1063 hr_utility.trace (' p_absence_attendance_id = '
1064 ||to_char (p_absence_attendance_id));
1065 hr_utility.trace (' p_new_sickness_start_date = '
1066 ||to_char (p_new_sickness_start_date));
1067 hr_utility.trace (' p_new_sickness_end_date = '
1068 ||to_char (p_new_sickness_end_date));
1069 hr_utility.trace (' p_old_sickness_end_date = '
1070 ||to_char (p_old_sickness_end_date));
1071 --
1072 hr_general.assert_condition (all_parameters_valid);
1073 --
1074 end check_parameters;
1075 --
1076 begin
1077 --
1078 hr_utility.set_location('Entering:'||l_proc,1);
1079 --
1080 check_parameters;
1081 --
1082 open csr_next_absence;
1083 fetch csr_next_absence into l_next_absence;
1084 --
1085 -- Compare the current values of the linked absence ids with the values they
1086 -- are recalculated to have after the update has gone through
1087 --
1088 if
1089 -- if there is a future sickness absence
1090 csr_next_absence%found
1091 --
1092 -- and if the PIW series to which the updated absence belongs will change
1093 and (nvl (p_linked_absence_id,-1) <> nvl (linked_absence_id (p_person_id,
1094 p_new_sickness_start_date,
1095 p_new_sickness_end_date),
1096 nvl (p_linked_absence_id,-1))
1097 -- or the PIW series to which the following absence belongs will change
1098 or (csr_next_absence%found
1099 and nvl (l_next_absence.linked_absence_id, -1)
1100 <> nvl (linked_absence_id (p_person_id,
1101 l_next_absence.sickness_start_date,
1102 l_next_absence.sickness_end_date), -1)))
1103 then
1104 --
1105 -- The change in sickness dates will change the PIW of the current or next
1106 -- sickness absence and so the update must be prevented.
1107 --
1108 close csr_next_absence;
1109 raise PIW_broken;
1110 else
1111 close csr_next_absence;
1112 end if;
1113 --
1114 hr_utility.set_location('Leaving :'||l_proc,100);
1115 --
1116 exception
1117 --
1118 when PIW_broken then
1119 --
1120 fnd_message.set_name ('SSP','SSP_35037_PIW_BROKEN');
1121 fnd_message.raise_error;
1122 --
1123 end check_sickness_date_change;
1124 --------------------------------------------------------------------------------
1125 procedure CHECK_FOR_BREAK_IN_LINKED_PIW
1126 --
1127 -- Check the effect of removal of a sickness absence from a linked PIW.
1128 -- See header for full description of this procedure.
1129 --
1130 (
1131 p_sickness_start_date in date,
1132 p_sickness_end_date in date,
1133 p_linked_absence_id in number,
1134 p_absence_attendance_id in number
1135 )
1136 is
1137 --
1138 l_proc varchar2 (72) := g_package||'check_for_break_in_linked_PIW';
1139 l_PIW_id number := nvl (p_linked_absence_id, p_absence_attendance_id);
1140 l_next_start_date date := null;
1141 l_previous_end_date date := null;
1142 l_dummy integer (1) := null;
1143 break_in_PIW exception;
1144 --
1145 cursor csr_PIW is
1146 --
1147 -- Return a row if there are any sickness absences in a series of
1148 -- which this absence is first.
1149 --
1150 select 1
1151 from per_absence_attendances
1152 where linked_absence_id = p_absence_attendance_id;
1153 --
1154 cursor csr_next_absence is
1155 --
1156 -- Get the absence which is next in the linked series of which this
1157 -- absence is a part.
1158 --
1159 select max (sickness_end_date)
1160 from per_absence_attendances
1161 where p_linked_absence_id = linked_absence_id
1162 and sickness_end_date <= p_sickness_start_date;
1163 --
1164 cursor csr_previous_absence is
1165 --
1166 -- Get the absence which is the previous one in the linked series of
1167 -- which this absence is a part.
1168 --
1169 select min (sickness_start_date)
1170 from per_absence_attendances
1171 where (l_PIW_id = linked_absence_id and absence_attendance_id is null)
1172 or (l_PIW_id = absence_attendance_id and linked_absence_id is null)
1173 and sickness_start_date >= p_sickness_end_date;
1174 --
1175 procedure check_parameters is
1176 --
1177 all_parameters_valid constant boolean := (p_absence_attendance_id is not null
1178 and p_sickness_start_date <= p_sickness_end_date
1179 and p_sickness_start_date = trunc (p_sickness_start_date)
1180 and p_sickness_end_date = trunc (p_sickness_end_date)
1181 and p_linked_absence_id <> p_absence_attendance_id);
1182 --
1183 begin
1184 --
1185 hr_utility.trace (l_proc||' p_sickness_start_date = '
1186 ||to_char (p_sickness_start_date));
1187 hr_utility.trace (l_proc||' p_sickness_end_date = '
1188 ||to_char (p_sickness_end_date));
1189 hr_utility.trace (l_proc||' p_linked_absence_id = '
1190 ||to_char (p_linked_absence_id));
1191 hr_utility.trace (l_proc||' p_absence_attendance_id = '
1192 ||to_char (p_absence_attendance_id));
1193 --
1194 hr_general.assert_condition (all_parameters_valid);
1195 --
1196 end check_parameters;
1197 --
1198 begin
1199 --
1200 hr_utility.set_location('Entering:'||l_proc,1);
1201 --
1202 check_parameters;
1203 --
1204 if SSP_is_installed and p_sickness_start_date is not null
1205 then
1206 --
1207 -- The SSP functionality is available and the absence is a sickness absence.
1208 --
1209 -- Is the absence the first in a linked series of absences?
1210 --
1211 open csr_PIW;
1212 fetch csr_PIW into l_dummy;
1213 --
1214 if csr_PIW%found then
1215 --
1216 -- This is the first absence in a linked series and may not be removed
1217 --
1218 close csr_PIW;
1219 raise break_in_PIW;
1220 else
1221 --
1222 -- This is not the first absence in a linked series
1223 --
1224 close csr_PIW;
1225 --
1226 -- Is the absence the last in a linked series?
1227 --
1228 open csr_next_absence;
1229 fetch csr_next_absence into l_next_start_date;
1230 --
1231 if csr_next_absence%found then
1232 --
1233 -- The absence is between other absences in a linked series.
1234 --
1235 close csr_next_absence;
1236 --
1237 -- Find the previous absence
1238 --
1239 open csr_previous_absence;
1240 fetch csr_previous_absence into l_previous_end_date;
1241 --
1242 -- Get the legislative parameters for SSP
1243 --
1244 get_SSP_element (p_sickness_start_date);
1245 --
1246 -- Can the next absence link directly to the previous absence without
1247 -- the current absence in the middle?
1248 --
1249 if l_previous_end_date
1250 <= (l_next_start_date - g_SSP_legislation.linking_period_days)
1251 then
1252 --
1253 -- The linked series will be broken by removal of this absence
1254 --
1255 raise break_in_PIW;
1256 end if;
1257 end if;
1258 end if;
1259 end if;
1260 --
1261 hr_utility.set_location('Leaving :'||l_proc,100);
1262 --
1263 exception
1264 when break_in_PIW then
1265 --
1266 -- Stop, with the message:
1267 -- "You may not delete this sickness absence record because to do so would
1268 -- result in a linked Period of Incapacity for Work (PIW) being broken. To
1269 -- get round this, you must first remove all the later sickness absences in
1270 -- the same PIW."
1271 --
1272 hr_utility.set_location('Leaving :'||l_proc||', Error',999);
1273 fnd_message.set_name ('SSP','SSP_35037_PIW_BROKEN');
1274 fnd_message.raise_error;
1275 --
1276 end check_for_break_in_linked_PIW;
1277 --------------------------------------------------------------------------------
1278 function get_age_at_PIW(p_person_id in number,
1279 p_date in date,
1280 p_date_of_birth in date default null) return number is
1281 l_age number;
1282
1283 cursor get_age is
1284 select months_between(p_date,date_of_birth)/12
1285 from per_all_people_f
1286 where person_id = p_person_id
1287 and effective_end_date = (select max(effective_end_date)
1288 from per_all_people_f
1289 where person_id = p_person_id);
1290
1291 cursor get_age2 is
1292 select months_between(p_date,p_date_of_birth)/12
1293 from dual;
1294
1295 begin
1296 if p_date_of_birth is null then
1297 open get_age;
1298 fetch get_age into l_age;
1299 close get_age;
1300 else
1301 open get_age2;
1302 fetch get_age2 into l_age;
1303 close get_age2;
1304 end if;
1305
1306 return l_age;
1307 end get_age_at_PIW;
1308 --------------------------------------------------------------------------------
1309 function check_linked_absence(p_absence_attendance_id in number,
1310 p_start_date out nocopy date,
1311 p_end_date out nocopy date) return boolean is
1312 --
1313 l_linked_id number;
1314 l_start_date date;
1315 l_end_date date;
1316 l_found boolean;
1317 --
1318 -- Get the linked ID from the absence
1319 cursor csr_linked_id(p_absence_id number) is
1320 select linked_absence_id
1321 from per_absence_attendances
1322 where absence_attendance_id = p_absence_id;
1323 --
1324 cursor csr_start_date(p_absence_id number) is
1325 select sickness_start_date
1326 from per_absence_attendances
1327 where absence_attendance_id = p_absence_id;
1328 --
1329 cursor csr_end_date(p_absence_id number) is
1330 select sickness_end_date
1331 from per_absence_attendances
1332 where (absence_attendance_id = p_absence_id
1333 or
1334 linked_absence_id = p_absence_id)
1335 order by sickness_end_date desc;
1336 --
1337 begin
1338 l_found := false;
1339 open csr_linked_id(p_absence_attendance_id);
1340 fetch csr_linked_id into l_linked_id;
1341 if csr_linked_id%found and l_linked_id is not null then
1342 l_found := true;
1343 end if;
1344 close csr_linked_id;
1345
1346 if l_found then
1347 open csr_start_date(l_linked_id);
1348 fetch csr_start_date into l_start_date;
1349 close csr_start_date;
1350 --
1351 open csr_end_date(l_linked_id);
1352 fetch csr_end_date into l_end_date;
1353 close csr_end_date;
1354 --
1355 p_start_date := l_start_date;
1356 p_end_date := l_end_date;
1357 end if;
1358
1359 return l_found;
1360 end check_linked_absence;
1361 --------------------------------------------------------------------------------
1362 function linked_PIW_start_date(p_person_id in number,
1363 p_sickness_start_date in date,
1364 p_sickness_end_date in date,
1365 p_date_of_birth in date) return date is
1366 --
1367 PIW_start date := null;
1368 l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1369 l_proc varchar2(72) := g_package||'linked_PIW_start_date';
1370 l_sickness_end_date date := nvl(p_sickness_end_date,hr_general.end_of_time);
1371 l_age number;
1372 l_s_date date;
1373 l_e_date date;
1374 --
1375 -- Get the linked series of sickness absences
1376 cursor PIW_series(p_start_date date) is
1377 select sickness_start_date,
1378 sickness_end_date,
1379 absence_attendance_id
1380 from per_absence_attendances
1381 where person_id = p_person_id
1382 and sickness_start_date is not null
1383 and sickness_start_date < p_sickness_start_date
1384 and (p_start_date is null or
1385 sickness_start_date >= p_start_date)
1386 order by sickness_start_date desc;
1387 --
1388 begin
1389 if p_sickness_start_date is not null then
1390 --
1391 -- This is a sickness absence
1392 --
1393 if ssp_ssp_pkg.absence_is_a_PIW(p_person_id => p_person_id,
1394 p_sickness_start_date => p_sickness_start_date,
1395 p_sickness_end_date => p_sickness_end_date) then
1396 -- The current absence is a PIW
1397 --
1398 PIW_start := p_sickness_start_date;
1399 --
1400 get_SSP_element (p_sickness_start_date);
1401 --
1402 l_age := get_age_at_PIW(p_person_id, l_01_October_06, p_date_of_birth);
1403 -----------------------------------------
1404 -- Age < 16 and sick after 01-Oct-2006 --
1405 -----------------------------------------
1406 if l_age < 16 and p_sickness_start_date >= l_01_October_06 then
1407 for preceeding in PIW_series(l_01_October_06) LOOP
1408 if (PIW_start - preceeding.sickness_end_date) <= g_SSP_legislation.linking_period_days and
1409 ssp_ssp_pkg.absence_is_a_PIW(p_sickness_start_date => preceeding.sickness_start_date,
1410 p_sickness_end_date => preceeding.sickness_end_date,
1411 p_person_id => p_person_id) then
1412 --
1413 -- The previous absence links to the current one
1414 -- Decrement the PIW start to the start of the previous absence
1415 --
1416 if check_linked_absence(preceeding.absence_attendance_id,
1417 l_s_date, l_e_date) then
1418 PIW_start := l_s_date;
1419 exit;
1420 end if;
1421 PIW_start := preceeding.sickness_start_date;
1422 end if;
1423 end loop;
1424 end if;
1425
1426 ---------------
1427 -- Other Age --
1428 ---------------
1429 if (l_age >= 16) or (p_sickness_start_date < l_01_October_06) then
1430 for preceeding in PIW_series(null) LOOP
1431 if (PIW_start - preceeding.sickness_end_date) <= g_SSP_legislation.linking_period_days and
1432 ssp_ssp_pkg.absence_is_a_PIW(p_sickness_start_date => preceeding.sickness_start_date,
1433 p_sickness_end_date => preceeding.sickness_end_date,
1434 p_person_id => p_person_id) then
1435 --
1436 -- The previous absence links to the current one
1437 -- Decrement the PIW start to the start of the previous absence
1438 --
1439 if check_linked_absence(preceeding.absence_attendance_id,
1440 l_s_date, l_e_date) then
1441 PIW_start := l_s_date;
1442 exit;
1443 end if;
1444 PIW_start := preceeding.sickness_start_date;
1445 end if;
1446 end loop;
1447 end if;
1448 --
1449 end if;
1450 --
1451 end if;
1452 --
1453 return PIW_start;
1454 --
1455 end linked_PIW_start_date;
1456 --------------------------------------------------------------------------------
1457 function linked_PIW_start_date(p_person_id in number,
1458 p_sickness_start_date in date,
1459 p_sickness_end_date in date) return date is
1460 --
1461 PIW_start date := null;
1462 l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1463 l_proc varchar2(72) := g_package||'linked_PIW_start_date';
1464 l_sickness_end_date date := nvl(p_sickness_end_date,hr_general.end_of_time);
1465 l_age number;
1466 l_linked_id number;
1467 l_s_date date;
1468 l_e_date date;
1469 --
1470 -- Get the linked series of sickness absences
1471 /* 12552547 Begin
1472 - Absences in different periods of service should not be linked.
1473 cursor PIW_series(p_start_date date) is
1474 select sickness_start_date,
1475 sickness_end_date,
1476 absence_attendance_id
1477 from per_absence_attendances
1478 where person_id = p_person_id
1479 and sickness_start_date is not null
1480 and sickness_start_date < p_sickness_start_date
1481 and (p_start_date is null or
1482 sickness_start_date >= p_start_date)
1483 order by sickness_start_date desc;
1484 */
1485
1486 l_PERIOD_OF_SERVICE_ID PER_PERIODS_OF_SERVICE.PERIOD_OF_SERVICE_ID%type;
1487
1488 cursor PIW_series(p_start_date date) is
1489 select sickness_start_date,
1490 sickness_end_date,
1491 absence_attendance_id
1492 from per_absence_attendances per,
1493 per_periods_of_service service
1494 where per.person_id = p_person_id
1495 and per.sickness_start_date is not null
1496 and per.sickness_start_date < p_sickness_start_date
1497 and (p_start_date is null or
1498 per.sickness_start_date >= p_start_date)
1499 and per.sickness_start_date between service.DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time)
1500 and service.PERIOD_OF_SERVICE_ID = l_PERIOD_OF_SERVICE_ID
1501 order by sickness_start_date desc;
1502
1503 Cursor POS_series(p_start_date date) is
1504 select PERIOD_OF_SERVICE_ID from per_periods_of_service service
1505 where PERSON_ID = p_person_id
1506 and p_start_date between DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time);
1507
1508 --12552547 End
1509
1510 --
1511 begin
1512 if p_sickness_start_date is not null then
1513 --
1514 -- This is a sickness absence
1515 --
1516 if ssp_ssp_pkg.absence_is_a_PIW(p_person_id => p_person_id,
1517 p_sickness_start_date => p_sickness_start_date,
1518 p_sickness_end_date => p_sickness_end_date) then
1519 -- The current absence is a PIW
1520 --
1521 PIW_start := p_sickness_start_date;
1522 --
1523 -- 12552547 Begin
1524 open POS_series(PIW_start);
1525 fetch POS_series into l_PERIOD_OF_SERVICE_ID; -- The period of service id corresponding absence start date
1526 close POS_series;
1527 -- 12552547 End
1528 --
1529 get_SSP_element (p_sickness_start_date);
1530 --
1531 l_age := get_age_at_PIW(p_person_id, l_01_October_06);
1532 -----------------------------------------
1533 -- Age < 16 and sick after 01-Oct-2006 --
1534 -----------------------------------------
1535 if l_age < 16 and p_sickness_start_date >= l_01_October_06 then
1536 for preceeding in PIW_series(l_01_October_06) LOOP
1537 if (PIW_start - preceeding.sickness_end_date) <= g_SSP_legislation.linking_period_days and
1538 ssp_ssp_pkg.absence_is_a_PIW(p_sickness_start_date => preceeding.sickness_start_date,
1539 p_sickness_end_date => preceeding.sickness_end_date,
1540 p_person_id => p_person_id) then
1541 --
1542 -- The previous absence links to the current one
1543 -- Decrement the PIW start to the start of the previous absence
1544 --
1545 if check_linked_absence(preceeding.absence_attendance_id,
1546 l_s_date, l_e_date) then
1547 PIW_start := l_s_date;
1548 exit;
1549 end if;
1550 PIW_start := preceeding.sickness_start_date;
1551 end if;
1552 end loop;
1553 end if;
1554
1555 ---------------
1556 -- Other Age --
1557 ---------------
1558 if (l_age >= 16) or (p_sickness_start_date < l_01_October_06) then
1559 for preceeding in PIW_series(null) LOOP
1560 if (PIW_start - preceeding.sickness_end_date) <= g_SSP_legislation.linking_period_days and
1561 ssp_ssp_pkg.absence_is_a_PIW(p_sickness_start_date => preceeding.sickness_start_date,
1562 p_sickness_end_date => preceeding.sickness_end_date,
1563 p_person_id => p_person_id) then
1564 --
1565 -- The previous absence links to the current one
1566 -- Decrement the PIW start to the start of the previous absence
1567 --
1568 if check_linked_absence(preceeding.absence_attendance_id,
1569 l_s_date, l_e_date) then
1570 PIW_start := l_s_date;
1571 exit;
1572 end if;
1573 PIW_start := preceeding.sickness_start_date;
1574 end if;
1575 end loop;
1576 end if;
1577 --
1578 end if;
1579 --
1580 end if;
1581 --
1582 return PIW_start;
1583 --
1584 end linked_PIW_start_date;
1585 --------------------------------------------------------------------------------
1586 --
1587 -- Overload function with absence_attendance_id
1588 function linked_PIW_start_date(p_person_id in number,
1589 p_sickness_start_date in date,
1590 p_sickness_end_date in date,
1591 p_absence_attendance_id number) return date is
1592 --
1593 PIW_start date := null;
1594 l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1595 l_proc varchar2(72) := g_package||'linked_PIW_start_date';
1596 l_sickness_end_date date := nvl(p_sickness_end_date,hr_general.end_of_time);
1597 l_age number;
1598 l_s_date date;
1599 l_e_date date;
1600 --
1601 -- Get the linked series of sickness absences
1602 /* 12552547 - begin
1603 --Absences with differenct period of service id should not be linked
1604 cursor PIW_series(p_start_date date) is
1605 select sickness_start_date,
1606 sickness_end_date,
1607 absence_attendance_id
1608 from per_absence_attendances
1609 where person_id = p_person_id
1610 and sickness_start_date is not null
1611 and sickness_start_date < p_sickness_start_date
1612 and (p_start_date is null or
1613 sickness_start_date >= p_start_date)
1614 order by sickness_start_date desc;
1615 */
1616
1617 l_PERIOD_OF_SERVICE_ID PER_PERIODS_OF_SERVICE.PERIOD_OF_SERVICE_ID%type;
1618
1619 cursor PIW_series(p_start_date date) is
1620 select sickness_start_date,
1621 sickness_end_date,
1622 absence_attendance_id
1623 from per_absence_attendances per,
1624 per_periods_of_service service
1625 where per.person_id = p_person_id
1626 and per.sickness_start_date is not null
1627 and per.sickness_start_date < p_sickness_start_date
1628 and (p_start_date is null or
1629 per.sickness_start_date >= p_start_date)
1630 and per.person_id = service.person_id
1631 and per.sickness_start_date between service.DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time)
1632 and service.PERIOD_OF_SERVICE_ID = l_PERIOD_OF_SERVICE_ID
1633 order by sickness_start_date desc;
1634
1635 --
1636
1637 Cursor POS_series(p_start_date date) is
1638 select PERIOD_OF_SERVICE_ID
1639 from per_periods_of_service service
1640 where PERSON_ID = p_person_id
1641 and p_start_date between DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time);
1642
1643 --12552547 End
1644
1645 --
1646 begin
1647 if p_sickness_start_date is not null then
1648 --
1649 -- This is a sickness absence
1650 --
1651 if ssp_ssp_pkg.absence_is_a_PIW(p_person_id => p_person_id,
1652 p_sickness_start_date => p_sickness_start_date,
1653 p_sickness_end_date => p_sickness_end_date,
1654 p_absence_attendance_id => p_absence_attendance_id) then
1655 -- The current absence is a PIW
1656 --
1657 PIW_start := p_sickness_start_date;
1658 --
1659 -- 12552547 Begin
1660 open POS_series(PIW_start);
1661 fetch POS_series into l_PERIOD_OF_SERVICE_ID; -- The period of service id corresponding absence start date
1662 close POS_series;
1663 -- 12552547 End
1664 get_SSP_element (p_sickness_start_date);
1665 --
1666 l_age := get_age_at_PIW(p_person_id, l_01_October_06);
1667 -----------------------------------------
1668 -- Age < 16 and sick after 01-Oct-2006 --
1669 -----------------------------------------
1670 if l_age < 16 and p_sickness_start_date >= l_01_October_06 then
1671 for preceeding in PIW_series(l_01_October_06) LOOP
1672 if (PIW_start - preceeding.sickness_end_date) <= g_SSP_legislation.linking_period_days and
1673 ssp_ssp_pkg.absence_is_a_PIW(p_sickness_start_date => preceeding.sickness_start_date,
1674 p_sickness_end_date => preceeding.sickness_end_date,
1675 p_person_id => p_person_id,
1676 p_absence_attendance_id => preceeding.absence_attendance_id) then
1677 --
1678 -- The previous absence links to the current one
1679 -- Decrement the PIW start to the start of the previous absence
1680 --
1681 if check_linked_absence(preceeding.absence_attendance_id,
1682 l_s_date, l_e_date) then
1683 PIW_start := l_s_date;
1684 exit;
1685 end if;
1686 PIW_start := preceeding.sickness_start_date;
1687 end if;
1688 end loop;
1689 end if;
1690
1691 ---------------
1692 -- Other Age --
1693 ---------------
1694 if (l_age >= 16) or (p_sickness_start_date < l_01_October_06) then
1695 for preceeding in PIW_series(null) LOOP
1696 if (PIW_start - preceeding.sickness_end_date) <= g_SSP_legislation.linking_period_days and
1697 ssp_ssp_pkg.absence_is_a_PIW(p_sickness_start_date => preceeding.sickness_start_date,
1698 p_sickness_end_date => preceeding.sickness_end_date,
1699 p_person_id => p_person_id,
1700 p_absence_attendance_id => preceeding.absence_attendance_id) then
1701 --
1702 -- The previous absence links to the current one
1703 -- Decrement the PIW start to the start of the previous absence
1704 --
1705 if check_linked_absence(preceeding.absence_attendance_id,
1706 l_s_date, l_e_date) then
1707 PIW_start := l_s_date;
1708 exit;
1709 end if;
1710 PIW_start := preceeding.sickness_start_date;
1711 end if;
1712 end loop;
1713 end if;
1714 --
1715 end if;
1716 --
1717 end if;
1718 --
1719 return PIW_start;
1720 --
1721 end linked_PIW_start_date;
1722 --------------------------------------------------------------------------------
1723 function linked_PIW_end_date(p_person_id in number,
1724 p_sickness_start_date in date,
1725 p_sickness_end_date in date,
1726 p_date_of_birth in date) return date is
1727 PIW_end date := null;
1728 l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1729 l_limit_date date := null;
1730 l_age number;
1731 l_s_date date;
1732 l_e_date date;
1733 --
1734 -- Get all sicknesses for the person which follow the parameter dates
1735 cursor PIW_series(p_start_date date) is
1736 select sickness_start_date,
1737 sickness_end_date,
1738 absence_attendance_id
1739 from per_absence_attendances
1740 where person_id = p_person_id
1741 and sickness_start_date is not null
1742 and sickness_start_date > p_sickness_start_date
1743 and (p_start_date is null or
1744 sickness_start_date < p_start_date)
1745 order by sickness_start_date;
1746 begin
1747 if (p_sickness_start_date is null or p_sickness_end_date is null) then
1748 PIW_end := null;
1749 else
1750 if ssp_ssp_pkg.absence_is_a_PIW(p_person_id => p_person_id,
1751 p_sickness_start_date => p_sickness_start_date,
1752 p_sickness_end_date => p_sickness_end_date) then
1753 PIW_end := p_sickness_end_date;
1754 --
1755 get_SSP_element (p_sickness_start_date);
1756 --
1757 l_age := get_age_at_PIW(p_person_id, l_01_October_06,p_date_of_birth);
1758 --
1759 if (l_age < 16) and p_sickness_start_date < l_01_October_06 then
1760 l_limit_date := l_01_October_06;
1761 end if;
1762 --
1763 --
1764 for next in PIW_series(l_limit_date) LOOP
1765 -- if the next sickness period links to the current one
1766 -- and the next sickness period is a PIW
1767 if (next.sickness_start_date - PIW_end) <= g_SSP_legislation.linking_period_days and
1768 ssp_ssp_pkg.absence_is_a_PIW (p_person_id => p_person_id,
1769 p_sickness_start_date => next.sickness_start_date,
1770 p_sickness_end_date => next.sickness_end_date)then
1771 --
1772 -- Increment the end date to that of the next sickness period
1773 --
1774 if check_linked_absence(next.absence_attendance_id,
1775 l_s_date, l_e_date) then
1776 PIW_end := l_e_date;
1777 exit;
1778 end if;
1779 PIW_end := next.sickness_end_date;
1780 end if;
1781 end loop;
1782 end if;
1783 end if;
1784 --
1785 return PIW_end;
1786 --
1787 end linked_PIW_end_date;
1788 --------------------------------------------------------------------------------
1789 function linked_PIW_end_date(p_person_id in number,
1790 p_sickness_start_date in date,
1791 p_sickness_end_date in date) return date is
1792 PIW_end date := null;
1793 l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1794 l_limit_date date := null;
1795 l_age number;
1796 l_s_date date;
1797 l_e_date date;
1798 --
1799 -- Get all sicknesses for the person which follow the parameter dates
1800 cursor PIW_series(p_start_date date) is
1801 select sickness_start_date,
1802 sickness_end_date,
1803 absence_attendance_id
1804 from per_absence_attendances
1805 where person_id = p_person_id
1806 and sickness_start_date is not null
1807 and sickness_start_date > p_sickness_start_date
1808 and (p_start_date is null or
1809 sickness_start_date < p_start_date)
1810 order by sickness_start_date;
1811 begin
1812 if (p_sickness_start_date is null or p_sickness_end_date is null) then
1813 PIW_end := null;
1814 else
1815 if ssp_ssp_pkg.absence_is_a_PIW(p_person_id => p_person_id,
1816 p_sickness_start_date => p_sickness_start_date,
1817 p_sickness_end_date => p_sickness_end_date) then
1818 PIW_end := p_sickness_end_date;
1819 --
1820 get_SSP_element (p_sickness_start_date);
1821 --
1822 l_age := get_age_at_PIW(p_person_id, l_01_October_06);
1823 --
1824 if (l_age < 16) and
1825 p_sickness_start_date < l_01_October_06 then
1826 l_limit_date := l_01_October_06;
1827 end if;
1828 --
1829 --
1830 for next in PIW_series(l_limit_date) LOOP
1831 -- if the next sickness period links to the current one
1832 -- and the next sickness period is a PIW
1833 if (next.sickness_start_date - PIW_end) <= g_SSP_legislation.linking_period_days and
1834 ssp_ssp_pkg.absence_is_a_PIW (p_person_id => p_person_id,
1835 p_sickness_start_date => next.sickness_start_date,
1836 p_sickness_end_date => next.sickness_end_date)then
1837 --
1838 -- Increment the end date to that of the next sickness period
1839 --
1840 if check_linked_absence(next.absence_attendance_id,
1841 l_s_date, l_e_date) then
1842 PIW_end := l_e_date;
1843 exit;
1844 end if;
1845 PIW_end := next.sickness_end_date;
1846 end if;
1847 end loop;
1848 end if;
1849 end if;
1850 --
1851 return PIW_end;
1852 --
1853 end linked_PIW_end_date;
1854 --------------------------------------------------------------------------------
1855 --
1856 -- Overload function with absence_attendance_id
1857 function linked_PIW_end_date(p_person_id in number,
1858 p_sickness_start_date in date,
1859 p_sickness_end_date in date,
1860 p_absence_attendance_id number) return date is
1861 PIW_end date := null;
1862 l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
1863 l_limit_date date := null;
1864 l_age number;
1865 l_s_date date;
1866 l_e_date date;
1867 --
1868 -- Get all sicknesses for the person which follow the parameter dates
1869 cursor PIW_series(p_start_date date) is
1870 select sickness_start_date,
1871 sickness_end_date,
1872 absence_attendance_id
1873 from per_absence_attendances
1874 where person_id = p_person_id
1875 and sickness_start_date is not null
1876 and sickness_start_date > p_sickness_start_date
1877 and (p_start_date is null or
1878 sickness_start_date < p_start_date)
1879 order by sickness_start_date;
1880 begin
1881 if (p_sickness_start_date is null or p_sickness_end_date is null) then
1882 PIW_end := null;
1883 else
1884 if ssp_ssp_pkg.absence_is_a_PIW(p_person_id => p_person_id,
1885 p_sickness_start_date => p_sickness_start_date,
1886 p_sickness_end_date => p_sickness_end_date,
1887 p_absence_attendance_id => p_absence_attendance_id) then
1888 PIW_end := p_sickness_end_date;
1889 --
1890 get_SSP_element (p_sickness_start_date);
1891 --
1892 l_age := get_age_at_PIW(p_person_id, l_01_October_06);
1893 --
1894 if (l_age < 16) and
1895 p_sickness_start_date < l_01_October_06 then
1896 l_limit_date := l_01_October_06;
1897 end if;
1898 --
1899 --
1900 for next in PIW_series(l_limit_date) LOOP
1901 -- if the next sickness period links to the current one
1902 -- and the next sickness period is a PIW
1903 if (next.sickness_start_date - PIW_end) <= g_SSP_legislation.linking_period_days and
1904 ssp_ssp_pkg.absence_is_a_PIW (p_person_id => p_person_id,
1905 p_sickness_start_date => next.sickness_start_date,
1906 p_sickness_end_date => next.sickness_end_date,
1907 p_absence_attendance_id => next.absence_attendance_id)then
1908 --
1909 -- Increment the end date to that of the next sickness period
1910 --
1911 if check_linked_absence(next.absence_attendance_id,
1912 l_s_date, l_e_date) then
1913 PIW_end := l_e_date;
1914 exit;
1915 end if;
1916 PIW_end := next.sickness_end_date;
1917 end if;
1918 end loop;
1919 end if;
1920 end if;
1921 --
1922 return PIW_end;
1923 --
1924 end linked_PIW_end_date;
1925 --------------------------------------------------------------------------------
1926 function qualifying_days_in_period
1927 --
1928 -- Returns the number of SSP qualifying days in a specified period.
1929 --
1930 (
1931 p_period_from in date,
1932 p_period_to in date,
1933 p_person_id in number,
1934 p_business_group_id in number,
1935 p_processing_level in number default 0
1936 )
1937 return integer is
1938 --
1939 l_person_purpose_usage_id number := NULL;
1940 l_bg_purpose_usage_id number := NULL;
1941 l_purpose_usage_id number;
1942 l_qualifying_days integer := 0;
1943 l_person_primary_key_value number := NULL;
1944 l_bg_primary_key_value number := NULL;
1945 l_primary_key_value number;
1946 l_dummy_start_date date;
1947 l_dummy_end_date date;
1948 l_proc varchar2 (72) := g_package||'qualifying_days_in_period';
1949 l_first_absence boolean;
1950 l_save_end_date date;
1951 --
1952 no_SSP_pattern exception;
1953 invalid_SSP_pattern exception;
1954 --
1955 cursor csr_qualifying_pattern is
1956 --
1957 -- 563202 - by concatanating null to purpose_usage_id in the where clause the
1958 -- use of a better index is forced.
1959 -- Returns those patterns that exist at some stage within the absence
1960 -- It will be person or BG pattern depending on the variables passed in
1961 --
1962 select start_date
1963 , end_date
1964 from hr_calendar_usages
1965 where purpose_usage_id||null = l_purpose_usage_id
1966 and primary_key_value = l_primary_key_value
1967 and end_date >= p_period_from
1968 and start_date <= p_period_to;
1969 --
1970 --6658285 begin
1971 Type csr_qua_pattern is record( start_date date,end_date date);
1972 Type csr_qua_pat_tab is table of csr_qua_pattern index by binary_integer;
1973 csr_qualifying_pattern_PLtable csr_qua_pat_tab;
1974 cont number:=0;
1975 --6658285 end
1976 procedure check_parameters is
1977 --
1978 all_parameters_valid constant boolean := (p_period_from is not null
1979 and p_period_to is not null
1980 and p_period_from <= p_period_to
1981 and p_period_from = trunc(p_period_from)
1982 and p_period_to = trunc (p_period_to)
1983 and p_person_id is not null
1984 and p_business_group_id is not null);
1985 --
1986 begin
1987 --
1988 hr_utility.trace(' p_period_from: '||to_char(p_period_from));
1989 hr_utility.trace(' p_period_to: '||to_char(p_period_to));
1990 hr_utility.trace(' p_person_id: '||to_char(p_person_id));
1991 hr_utility.trace(' p_business_group_id: '||to_char(p_business_group_id));
1992 --
1993 hr_general.assert_condition (all_parameters_valid);
1994 --
1995 end check_parameters;
1996 --
1997 begin
1998 --
1999 hr_utility.set_location('Entering:'||l_proc,1);
2000 --
2001 check_parameters;
2002 --
2003 -- Look for a personal SSP qualifying pattern.
2004 --
2005 l_primary_key_value := p_person_id;
2006 l_person_primary_key_value := l_primary_key_value;
2007 l_purpose_usage_id := hr_calendar_pkg.purpose_usage_id ('PERSON',
2008 'QUALIFYING PATTERN');
2009 l_person_purpose_usage_id := l_purpose_usage_id;
2010 --
2011 l_first_absence := TRUE;
2012 --
2013 --6658285 begin
2014 for I in csr_qualifying_pattern loop
2015 cont := cont+1;
2016 csr_qualifying_pattern_PLtable(cont).start_date := I.start_date;
2017 csr_qualifying_pattern_PLtable(cont).end_date := I.end_date;
2018 end loop;
2019 --for pattern in csr_qualifying_pattern loop
2020 for pattern in 1..csr_qualifying_pattern_PLtable.count loop
2021 --6658285 end
2022 if l_first_absence
2023 then
2024 /*6658285 Begin
2025 if pattern.start_date > p_period_from
2026 then
2027 close csr_qualifying_pattern; */
2028 if csr_qualifying_pattern_PLtable(pattern).start_date > p_period_from
2029 then
2030 --6658285 end
2031 l_primary_key_value := p_business_group_id;
2032 l_bg_primary_key_value := l_primary_key_value;
2033 l_purpose_usage_id := hr_calendar_pkg.purpose_usage_id(
2034 'ORGANIZATION',
2035 'QUALIFYING PATTERN');
2036 l_bg_purpose_usage_id := l_purpose_usage_id;
2037 --
2038 open csr_qualifying_pattern;
2039 fetch csr_qualifying_pattern into l_dummy_start_date, l_dummy_end_date;
2040 --
2041 if csr_qualifying_pattern%notfound
2042 then
2043 -- there was no qualifying pattern for the bg
2044 raise no_SSP_pattern;
2045 end if;
2046 --
2047 close csr_qualifying_pattern;
2048 --
2049 /*6658285 Begin
2050 elsif pattern.end_date < p_period_to
2051 then
2052 l_save_end_date := pattern.end_date;*/
2053 elsif csr_qualifying_pattern_PLtable(pattern).end_date < p_period_to
2054 then
2055 l_save_end_date := csr_qualifying_pattern_PLtable(pattern).end_date;
2056 --6658285 end
2057 l_first_absence := FALSE;
2058 end if;
2059 else -- not l_first_absence
2060 /*6658285 Begin
2061 if pattern.start_date > l_save_end_date + 1
2062 then
2063 close csr_qualifying_pattern;
2064 */
2065 if csr_qualifying_pattern_PLtable(pattern).start_date > l_save_end_date + 1
2066 then
2067 --6658285 end
2068 l_primary_key_value := p_business_group_id;
2069 l_bg_primary_key_value := l_primary_key_value;
2070 l_purpose_usage_id :=hr_calendar_pkg.purpose_usage_id(
2071 'ORGANIZATION',
2072 'QUALIFYING PATTERN');
2073 l_bg_purpose_usage_id := l_purpose_usage_id;
2074 --
2075 open csr_qualifying_pattern;
2076 fetch csr_qualifying_pattern into l_dummy_start_date, l_dummy_end_date;
2077 if csr_qualifying_pattern%notfound
2078 then
2079 -- there was no qualifying pattern for the bg
2080 raise no_SSP_pattern;
2081 end if;
2082 close csr_qualifying_pattern;
2083 --
2084 /*6658285 Begin
2085 elsif pattern.end_date < p_period_to
2086 then
2087 l_save_end_date := pattern.end_date; */
2088 elsif csr_qualifying_pattern_PLtable(pattern).end_date < p_period_to
2089 then
2090 l_save_end_date := csr_qualifying_pattern_PLtable(pattern).end_date;
2091 --6658285 end
2092 end if;
2093 --
2094 l_first_absence := FALSE;
2095 end if;
2096 --
2097 l_first_absence := FALSE;
2098 --
2099 /*6658285 Begin commenting the fix done for bug 550269
2100 -- Fix for Bug 550269
2101 if not(csr_qualifying_pattern%ISOPEN)
2102 then
2103 open csr_qualifying_pattern;
2104 end if;
2105 --
2106 6658285 end*/
2107 end loop;
2108 --
2109 if l_first_absence
2110 then
2111 l_person_primary_key_value := null;
2112 l_person_purpose_usage_id := null;
2113 l_primary_key_value:= p_business_group_id;
2114 l_bg_primary_key_value := l_primary_key_value;
2115 l_purpose_usage_id := hr_calendar_pkg.purpose_usage_id(
2116 'ORGANIZATION',
2117 'QUALIFYING PATTERN');
2118 l_bg_purpose_usage_id := l_purpose_usage_id;
2119 --
2120 open csr_qualifying_pattern;
2121 fetch csr_qualifying_pattern into l_dummy_start_date, l_dummy_end_date;
2122 --
2123 if csr_qualifying_pattern%notfound
2124 then
2125 raise no_SSP_pattern;
2126 end if;
2127 close csr_qualifying_pattern;
2128 end if;
2129 --
2130 -- Having found the purpose usage, determine the number of qualifying days
2131 -- in the period according to the appropriate calendar
2132 --
2133 hr_utility.trace('PERSON PATTERN USAGE ID: '||l_person_purpose_usage_id);
2134 hr_utility.trace('PERSON PRIMARY KEY VAL: '||l_person_primary_key_value);
2135 hr_utility.trace('BG PATTERN USAGE ID: '||l_bg_purpose_usage_id);
2136 hr_utility.trace('BG PRIMARY KEY VALUE: '||l_bg_primary_key_value);
2137 hr_utility.trace('PERIOD FROM/TO: '||p_period_from||' -> '||p_period_to);
2138 --
2139 -- call new overloaded version of hr_calendar_pkg.total_availability
2140 --
2141 l_qualifying_days := hr_calendar_pkg.total_availability (
2142 p_availability => 'QUALIFYING',
2143 p_person_purpose_usage_id => l_person_purpose_usage_id,
2144 p_person_primary_key_value => l_person_primary_key_value,
2145 p_bg_purpose_usage_id => l_bg_purpose_usage_id,
2146 p_bg_primary_key_value => l_bg_primary_key_value,
2147 p_period_from => p_period_from,
2148 p_period_to => p_period_to + 1,
2149 p_processing_level => p_processing_level);
2150 --
2151 hr_utility.set_location('Leaving :'||l_proc||
2152 '. Qual days: '||to_char(l_qualifying_days),100);
2153 --
2154 return l_qualifying_days;
2155 --
2156 exception
2157 --
2158 when no_SSP_pattern then
2159 hr_utility.set_location('Leaving :'||l_proc||', Error',999);
2160 fnd_message.set_name ('SSP','SSP_35048_NO_SSP_QUAL_PATTERN');
2161 fnd_message.raise_error;
2162 --
2163 end qualifying_days_in_period;
2164 --
2165 --------------------------------------------------------------------------------
2166 function SSP_is_installed return boolean is
2167 --
2168 -- Checks that SSP is installed before SSP functionality is performed.
2169 -- This code was copied and modified from hrapiapi.pkb
2170 --
2171 l_pa_installed fnd_product_installations.status%TYPE;
2172 l_industry fnd_product_installations.industry%TYPE;
2173 l_pa_appid fnd_product_installations.application_id%TYPE;
2174 --
2175 l_SSP_installed boolean := FALSE;
2176 l_proc varchar2 (72) := g_package||'SSP_is_installed';
2177 --
2178 begin
2179 --
2180 hr_utility.set_location('Entering:'||l_proc,1);
2181 --
2182 if g_SSP_is_installed is null
2183 then
2184 -- We need to determine if SSP is installed.
2185 --
2186 begin
2187
2188 select application_id
2189 into l_pa_appid
2190 from fnd_application
2191 where upper(application_short_name) = upper('SSP');
2192
2193 if (fnd_installation.get(appl_id => l_pa_appid,
2194 dep_appl_id => l_pa_appid,
2195 status => l_pa_installed,
2196 industry => l_industry))
2197 then
2198 --
2199 -- Check to see if status = 'I'
2200 --
2201 if (l_pa_installed = 'I') then
2202 l_SSP_installed := TRUE;
2203 else
2204 l_SSP_installed := FALSE;
2205 end if;
2206 else
2207 l_SSP_installed := FALSE;
2208
2209 end if;
2210 --
2211 g_SSP_is_installed := l_SSP_installed;
2212
2213 exception
2214
2215 when too_many_rows then
2216 hr_utility.trace('Error: more than 1 row on FND_APPLICATION table for SSP');
2217
2218 when others then
2219 raise;
2220
2221 end;
2222
2223 end if;
2224 --
2225 if g_SSP_is_installed
2226 then
2227 hr_utility.trace('SSP is installed');
2228 else
2229 hr_utility.trace('SSP is NOT installed');
2230 end if;
2231 --
2232 hr_utility.set_location('Leaving :'||l_proc,100);
2233 --
2234 return g_SSP_is_installed;
2235 --
2236 end SSP_is_installed;
2237 --------------------------------------------------------------------------------
2238 --------------------------------------------------------------------------------
2239 function ENTITLED_TO_SSP(p_absence per_absence_attendances%rowtype)
2240 --
2241 -- See header for description of this function.
2242 --
2243 return boolean is
2244 --
2245 l_proc varchar2(72) := g_package||'entitled_to_ssp';
2246 last_entitled_day date := hr_general.end_of_time;
2247 max_PIW_end_date date := null;
2248 l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
2249 --
2250 no_prima_facia_entitlement exception;
2251 employee_too_old exception;
2252 earnings_too_low exception;
2253 /*BUG 2984577 Added exception*/
2254 evidence_not_available exception;
2255
2256 --Bug Fix 8356706 Begin
2257 l_saturday varchar2(100) := rtrim(to_char(to_date('06/01/2001','DD/MM/YYYY'),'DAY'));
2258 l_sunday varchar2(100) := rtrim(to_char(to_date('07/01/2001','DD/MM/YYYY'),'DAY'));
2259 --Bug Fix 8356706 End
2260
2261 -- System-created stoppages for the PIW which have not been overridden
2262 cursor csr_old_stoppages is
2263 select stp.*
2264 from ssp_stoppages STP
2265 where stp.absence_attendance_id = g_PIW_id
2266 and ((stp.override_stoppage <> 'Y' and stp.user_entered <> 'Y')
2267 or
2268 (stp.withhold_from > PIW_end_date));
2269 --
2270 /*--------------------------------------*/
2271 /* Create a stoppage of payment for SSP */
2272 /*--------------------------------------*/
2273 procedure create_stoppage(p_withhold_from in date,
2274 p_withhold_to in date default null,
2275 p_reason in varchar2,
2276 p_overridden out nocopy boolean) is
2277 l_dummy number;
2278 l_reason_id number;
2279 l_proc varchar2 (72) := g_package||'create_stoppage';
2280
2281 procedure check_parameters is
2282 all_parameters_valid constant boolean :=
2283 (p_withhold_from is not null
2284 and p_withhold_from = trunc (p_withhold_from)
2285 and p_withhold_to = trunc (p_withhold_to)
2286 and p_withhold_from <= p_withhold_to
2287 and p_reason is not null);
2288 begin
2289 hr_utility.trace(' p_reason: ' || p_reason);
2290 hr_utility.trace(' withhold_from/to: ' ||
2291 to_char(p_withhold_from)||', '||to_char(p_withhold_to));
2292 hr_general.assert_condition (all_parameters_valid);
2293 end check_parameters;
2294 --
2295 begin
2296 hr_utility.set_location('Entering:'||l_proc,1);
2297 --
2298 check_parameters;
2299 --
2300 l_reason_id := ssp_smp_support_pkg.withholding_reason_id(
2301 g_SSP_legislation.element_type_id,p_reason);
2302 --
2303 if not ssp_smp_support_pkg.stoppage_overridden(p_absence_attendance_id => g_PIW_id,
2304 p_reason_id => l_reason_id) then
2305 -- the withholding reason is not overridden for this PIW
2306 --
2307 -- Create a stoppage for the PIW
2308 --
2309 ssp_stp_ins.ins (p_withhold_from => p_withhold_from,
2310 p_withhold_to => p_withhold_to,
2311 p_stoppage_id => l_dummy,
2312 p_object_version_number => l_dummy,
2313 p_absence_attendance_id => g_PIW_id,
2314 p_user_entered => 'N',
2315 p_reason_id => l_reason_id);
2316 --
2317 p_overridden := FALSE;
2318 else
2319 --
2320 -- Inform the calling procedure that the stoppage could not be
2321 -- created because it has been overridden by the user.
2322 --
2323 p_overridden := TRUE;
2324 end if;
2325 --
2326 hr_utility.set_location('Leaving :'||l_proc,100);
2327 --
2328 end create_stoppage;
2329 --
2330
2331 /*---------------------------------------------------*/
2332 /* Check that the absentee is not too old to qualify */
2333 /* for SSP. The date on which age is measured is */
2334 /* the start of the linked PIW. */
2335 /*---------------------------------------------------*/
2336 procedure check_age is
2337 l_proc varchar2(72) := g_package||'check_age';
2338 l_stoppage_overridden boolean := FALSE;
2339 age_at_PIW_start_date number := floor (months_between (PIW_start_date,
2340 absentee.date_of_birth) / 12);
2341 l_end_date date;
2342
2343 cursor get_end_date is
2344 select nvl(sickness_end_date, l_01_October_06 - 1)
2345 from per_absence_attendances
2346 where ((sickness_end_date is null and sickness_start_date < l_01_October_06)
2347 or
2348 sickness_end_date < l_01_October_06)
2349 and person_id = p_absence.person_id
2350 and sickness_start_date is not null
2351 order by sickness_end_date DESC;
2352 begin
2353 hr_utility.set_location('Entering:'||l_proc,1);
2354 --
2355 hr_utility.trace('Age at PIW start date: '||to_char(Age_at_PIW_start_date));
2356 hr_utility.trace('g_SSP_legislation.maximum_age: '||to_char(g_SSP_legislation.maximum_age));
2357 --
2358 -- Calculate the absentee's age as at the PIW start date
2359 --
2360 if age_at_PIW_start_date >= g_SSP_legislation.maximum_age then
2361 --
2362 -- Stop SSP payment because absentee is too old
2363 --
2364 open get_end_date;
2365 fetch get_end_date into l_end_date;
2366 close get_end_date;
2367 hr_utility.trace(l_proc||'l_end_date: '||l_end_date);
2368 hr_utility.trace(l_proc||'PIW_end_date: '||PIW_end_date);
2369
2370 create_stoppage(p_withhold_from => PIW_start_date,
2371 p_withhold_to => least(PIW_end_date,l_end_date),
2372 p_reason => 'Employee too old',
2373 p_overridden => l_stoppage_overridden);
2374 --
2375 if not l_stoppage_overridden and PIW_end_date < l_01_October_06 then
2376 raise employee_too_old;
2377 end if;
2378 end if;
2379 --
2380 hr_utility.set_location('Leaving :'||l_proc,100);
2381 --
2382 end check_age;
2383 --
2384 /*--------------------------------------------------*/
2385 /* Create a stoppage of SSP from the date following */
2386 /* the date of death. */
2387 /*--------------------------------------------------*/
2388 procedure check_death is
2389 l_proc varchar2(72) := g_package||'check_death';
2390 l_stoppage_overridden boolean := FALSE;
2391 begin
2392 --
2393 hr_utility.set_location('Entering:'||l_proc,1);
2394 --
2395 -- Bug 2984458 to display 'Employee Died' when the employee is
2396 -- deceased
2397 if absentee.actual_termination_date is not null and
2398 absentee.leaving_reason='D' then
2399 create_stoppage (p_withhold_from => absentee.actual_termination_date +1,
2400 p_reason => 'Employee died',
2401 p_overridden => l_stoppage_overridden);
2402 end if;
2403 --
2404 hr_utility.set_location('Leaving :'||l_proc,100);
2405 --
2406 end check_death;
2407 --
2408 /*------------------------------------------------*/
2409 /* Return the last day before the first permanent */
2410 /* stoppage date to affect the PIW */
2411 /*------------------------------------------------*/
2412 procedure get_last_entitled_day is
2413 l_proc varchar2 (72) := g_package||'get_last_entitled_day';
2414 --
2415 -- Get the first permanent stoppage date to affect the
2416 -- PIW. Return the day before the stoppage is
2417 -- effective because that is the last day of payment.
2418 --
2419 cursor csr_last_entitled_day is
2420 select nvl (min (withhold_from),hr_general.end_of_time) -1
2421 from ssp_stoppages
2422 where absence_attendance_id = g_PIW_id
2423 and override_stoppage <> 'Y'
2424 and withhold_to is null;
2425 --
2426 begin
2427 --
2428 hr_utility.set_location('Entering:'||l_proc,1);
2429 --
2430 open csr_last_entitled_day;
2431 fetch csr_last_entitled_day into last_entitled_day;
2432 close csr_last_entitled_day;
2433 --
2434 -- The SSP stops with either the first stoppage, or the end of
2435 -- the employment, whichever is sooner.
2436 --
2437 last_entitled_day := least (last_entitled_day,max_PIW_end_date -1,
2438 absentee.actual_termination_date);
2439 --
2440 hr_utility.set_location('Leaving :'||l_proc,100);
2441 --
2442 end get_last_entitled_day;
2443 --
2444 /*----------------------------------------------------------------*/
2445 /* Check that the number of waiting days specified by legislation */
2446 /* have been served within the PIW. Also check that the number of */
2447 /* weeks of SSP paid is within the legislative limit and that the */
2448 /* PIW is within its legislative maximum length. */
2449 /*----------------------------------------------------------------*/
2450 procedure do_PIW_calculations is
2451 l_proc varchar2(72) := g_package||'do_PIW_calculations';
2452 l_pregnancy_related_illness varchar2(30);
2453 weeks_remaining number := g_SSP_legislation.maximum_SSP_period;
2454 waiting_days_left number := g_SSP_legislation.waiting_days;
2455 start_disqualifying_period date;
2456 end_disqualifying_period date;
2457 start_pregnancy_illness date;
2458 end_pregnancy_illness date;
2459 first_sunday_of_sickness date;
2460 cal_start_date date;
2461 cal_end_date date;
2462 rate_start_date date;
2463 rate_end_date date;
2464 l_max_absence_start_date date;
2465 l_max_absence_end_date date;
2466 l_sickness_start_date date;
2467 l_sickness_end_date date;
2468 l_min_withheld_from date;
2469 l_max_withheld_to date;
2470 l_PIW_end_date date := null;
2471 stoppage_date date := hr_general.end_of_time;
2472 min_cal_start_date date := hr_general.end_of_time;
2473 max_cal_end_date date := hr_general.start_of_time;
2474 l_nos_absences number;
2475 l_business_group_id number;
2476 l_absence_attendance_id number;
2477 l_nos_stoppages number;
2478 SSP_rate_figure number;
2479 l_age number;
2480 waiting_days_this_period number := 0;
2481 total_SSP_weeks number := 0;
2482 days_remaining number := weeks_remaining * 7;
2483 daily_rate number(9,4) := null;
2484 N integer := 0;
2485 maximum_SSP_paid exception;
2486 PIW_max_length_reached exception;
2487 stoppage_invoked exception;
2488 l_stoppage_overridden boolean := FALSE;
2489 l_SMP_element ssp_SMP_pkg.csr_SMP_element_details%rowtype;
2490 --
2491 type stoppage_table_type is record (t_withheld_from date_table,
2492 t_withheld_to date_table);
2493 --
2494 stoppage_table stoppage_table_type;
2495 empty_stoppage_table stoppage_table_type;
2496 --
2497 type absence_table_type is record (
2498 t_sickness_start_date date_table,
2499 t_sickness_end_date date_table,
2500 t_business_group_id number_table,
2501 t_absence_attendance_id number_table,
2502 t_pregnancy_related_illness varchar_table,
2503 t_linked_absence_id number_table);
2504 --
2505 absence_table absence_table_type;
2506 empty_absence_table absence_table_type;
2507 --
2508 -- Return rows if the period in question is covered by stoppages
2509 cursor csr_stoppages(p_effective_start_date in date,
2510 p_effective_end_date in date) is
2511 select withhold_from, withhold_to
2512 from ssp_stoppages
2513 where absence_attendance_id = g_PIW_id
2514 and override_stoppage = 'N'
2515 and withhold_to is not null
2516 and ((p_effective_start_date <= withhold_from and
2517 p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
2518 )
2519 or
2520 (p_effective_end_date >= withhold_from and
2521 p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
2522 ))
2523 union all
2524 select withhold_from, withhold_to
2525 from ssp_stoppages ss,
2526 per_absence_attendances paa
2527 where paa.linked_absence_id = g_PIW_id
2528 and paa.absence_attendance_id = ss.absence_attendance_id
2529 and override_stoppage = 'N'
2530 and withhold_to is not null
2531 and ((p_effective_start_date <= withhold_from and
2532 p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
2533 )
2534 or
2535 (p_effective_end_date >= withhold_from and
2536 p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
2537 ));
2538 --
2539 function SSP_rate (p_effective_date date) return number is
2540 --
2541 -- Get the SSP rate as at a given effective date.
2542 cursor csr_rate is
2543 select piv.default_value,
2544 piv.effective_start_date,
2545 piv.effective_end_date
2546 from pay_input_values_f PIV,
2547 pay_element_types_f ELT
2548 where elt.element_type_id = piv.element_type_id
2549 and elt.element_type_id = g_SSP_legislation.element_type_id
2550 and p_effective_date between piv.effective_start_date and piv.effective_end_date
2551 and p_effective_date between elt.effective_start_date and elt.effective_end_date
2552 and piv.input_value_id = g_SSP_legislation.rate_id;
2553 --
2554 l_proc varchar2 (72) := g_package||'SSP_rate';
2555 l_rate number;
2556
2557 cannot_get_SSP_rate exception;
2558 --
2559 procedure check_parameters is
2560 all_parameters_valid constant boolean
2561 :=(p_effective_date is not null and
2562 p_effective_date = trunc (p_effective_date));
2563 begin
2564 hr_utility.trace (l_proc||' p_effective_date = '||to_char (p_effective_date));
2565 --
2566 hr_general.assert_condition (all_parameters_valid);
2567 --
2568 end check_parameters;
2569 --
2570 begin
2571 hr_utility.set_location('Entering:'||l_proc,1);
2572 check_parameters;
2573 if rate_start_date is null or
2574 p_effective_date NOT between rate_start_date and rate_end_date then
2575 --
2576 -- The rate already retrieved is not the one we want so
2577 -- get the right one from the database.
2578 --
2579 open csr_rate;
2580 fetch csr_rate into SSP_rate_figure,
2581 rate_start_date, rate_end_date;
2582 close csr_rate;
2583 end if;
2584 --
2585 l_rate := SSP_rate_figure;
2586 --
2587 if l_rate is null then
2588 raise cannot_get_SSP_rate;
2589 end if;
2590 --
2591 hr_utility.set_location('Leaving :'||l_proc,100);
2592 --
2593 return l_rate;
2594 --
2595 exception
2596 when cannot_get_SSP_rate then
2597 hr_utility.set_location('Leaving :'||l_proc||', Error',999);
2598 fnd_message.set_name ('SSP','SSP_35079_NO_SSP_RATE');
2599 fnd_message.set_token ('EFFECTIVE_DATE',p_effective_date);
2600 fnd_message.raise_error;
2601 end SSP_rate;
2602 --
2603 /*--------------------------------------------------------------*/
2604 /* Calculate number of temporary stoppage days within a period. */
2605 /*--------------------------------------------------------------*/
2606 function stopped_days(p_start_date in date,
2607 p_end_date in date,
2608 p_PIW_id in number) return number is
2609 l_proc varchar2 (72) := g_package||'stopped_days';
2610 l_stopped_days_in_period number := 0;
2611 l_this_day date := p_start_date;
2612 l_stoppage_overridden boolean := FALSE;
2613 l_stoppage_found boolean;
2614 --
2615 procedure check_parameters is
2616 all_parameters_valid constant boolean :=
2617 (p_start_date is not null and
2618 p_end_date is not null and
2619 p_PIW_id is not null and
2620 p_start_date = trunc(p_start_date) and
2621 p_end_date = trunc(p_end_date) and
2622 p_start_date <= p_end_date);
2623 begin
2624 hr_utility.trace (' p_start_date = '||to_char (p_start_date));
2625 hr_utility.trace (' p_end_date = '||to_char (p_end_date));
2626 hr_utility.trace (' p_PIW_id = '||to_char (p_PIW_id));
2627 hr_general.assert_condition (all_parameters_valid);
2628 end check_parameters;
2629 begin
2630 hr_utility.set_location('Entering:'||l_proc,1);
2631 --
2632 check_parameters;
2633 --
2634 -- moved inside loop for bug 886707
2635 -- l_stoppage_found := FALSE;
2636 --
2637 -- Cycle through the days of the period passed in, checking
2638 -- each day for a temporary stoppage covering a qualifying day.
2639 --
2640 while l_this_day <= p_end_date loop
2641 -- 886707: Added initialisation of l_stoppage_found here so
2642 -- so that it is reset at every loop.
2643 l_stoppage_found := FALSE;
2644 --
2645 if 1 = qualifying_days_in_period(l_this_day, l_this_day,
2646 p_absence.person_id,
2647 p_absence.business_group_id,
2648 p_processing_level => 3)then
2649 --
2650 -- The current day is a qualifying day, so
2651 -- check for a stoppage covering this day.
2652 --
2653 for each_stoppage in 1..l_nos_stoppages loop
2654 if l_this_day
2655 between stoppage_table.t_withheld_from(each_stoppage)
2656 and stoppage_table.t_withheld_to(each_stoppage) then
2657 l_stoppage_found := TRUE;
2658 l_stopped_days_in_period :=l_stopped_days_in_period + 1;
2659 end if;
2660 end loop;
2661 --
2662 if not l_stoppage_found and waiting_days_left > 0 then
2663 --
2664 -- This day is not covered by a stoppage and there are
2665 -- waiting days to create.
2666 --
2667 create_stoppage(p_withhold_from => l_this_day,
2668 p_withhold_to => l_this_day,
2669 p_reason => 'Waiting day',
2670 p_overridden => l_stoppage_overridden);
2671 --
2672 if not l_stoppage_overridden then
2673 -- the waiting day stoppage is not user-overridden
2674 --
2675 waiting_days_left := waiting_days_left - 1;
2676 waiting_days_this_period :=waiting_days_this_period+1;
2677 else
2678 waiting_days_left := 0;
2679 end if;
2680 end if;
2681 end if;
2682 --
2683 l_this_day := l_this_day + 1;
2684 end loop;
2685 --
2686 hr_utility.trace('Stopped Days in Period:'||
2687 to_char(l_stopped_days_in_period));
2688 hr_utility.set_location('Leaving :'||l_proc,100);
2689 --
2690 return l_stopped_days_in_period;
2691 --
2692 end stopped_days;
2693 --
2694 procedure check_disqualifying_period(p_sickness_start_date date,
2695 p_sickness_end_date date,
2696 p_pregnancy_related_illness varchar2) is
2697 -- Select the maternity records for the person.
2698 --
2699 cursor maternity_details is
2700 select maternity_id,
2701 object_version_number,
2702 MPP_start_date,
2703 due_date, -- Need due_date to decide on value of sickness_trigger_weeks
2704 ssp_smp_pkg.expected_week_of_confinement(due_date) EWC
2705 from ssp_maternities
2706 where person_id = p_absence.person_id
2707 and nvl(leave_type,'MA') = 'MA';
2708
2709 l_proc varchar2 (72) := g_package||'check_disqualifying_period';
2710 l_stoppage_overridden boolean := FALSE;
2711 sickness_trigger_weeks integer;
2712 --
2713 procedure check_parameters is
2714 all_parameters_valid constant boolean :=
2715 (p_sickness_start_date is not null and
2716 p_sickness_start_date <= p_sickness_end_date and
2717 p_sickness_start_date = trunc(p_sickness_start_date) and
2718 p_sickness_end_date = trunc(p_sickness_end_date) and
2719 p_pregnancy_related_illness is not null and
2720 p_pregnancy_related_illness in ('Y','N'));
2721 begin
2722 hr_utility.trace(l_proc||' p_sickness_start_date = '
2723 ||to_char (p_sickness_start_date));
2724 hr_utility.trace(l_proc||' p_sickness_end_date = '
2725 ||to_char (p_sickness_end_date));
2726 hr_utility.trace(l_proc||' p_pregnancy_related_illness = '
2727 ||p_pregnancy_related_illness);
2728 hr_general.assert_condition (all_parameters_valid);
2729 end check_parameters;
2730 begin
2731 hr_utility.set_location('Entering:'||l_proc,1);
2732 --
2733 check_parameters;
2734 --
2735 for maternity in maternity_details loop
2736 if maternity.due_date >=
2737 fnd_date.canonical_to_date('2003/04/06 00:00:00') then
2738 sickness_trigger_weeks := 28;
2739 else
2740 sickness_trigger_weeks := 42;
2741 end if;
2742 --
2743 -- the illness is pregnancy-related
2744 -- and the sickness ends sickness trigger weeks
2745 -- (six weeks before 06-APR-2003, four weeks after)
2746 -- before the EWC or later
2747 -- and the sickness starts before the EWC
2748 if p_pregnancy_related_illness = 'Y' and
2749 (maternity.EWC - sickness_trigger_weeks) <= p_sickness_end_date and
2750 maternity.EWC >= p_sickness_start_date then
2751 --
2752 hr_utility.trace(l_proc||'Condition 1 true');
2753 --
2754 -- the illness starts before sickness trigger weeks
2755 -- (6 weeks before 06-APR-2003, four weeks after)
2756 -- prior to the EWC
2757 if (maternity.EWC - sickness_trigger_weeks) >=
2758 p_sickness_start_date then
2759 --
2760 hr_utility.trace(l_proc||'Condition 2 true');
2761 --
2762 -- there is no recored start_date OR
2763 -- the start date is later than
2764 -- the fourth or sixth week prior to the EWC
2765 -- depending on whether the due date of the
2766 -- maternity is on or after 06-APR-2003
2767 if maternity.MPP_start_date is null or
2768 maternity.MPP_start_date >
2769 (maternity.EWC - sickness_trigger_weeks) then
2770 --
2771 -- force the MPP to start on the Sunday of the
2772 -- sixth week prior to the EWC
2773 if maternity.due_date <
2774 fnd_date.canonical_to_date('2003/04/06 00:00:00') then
2775 --
2776 hr_utility.trace(l_proc||'Condition 3 true');
2777 --
2778 if rtrim (to_char (maternity.EWC, 'DAY')) = l_sunday then
2779 hr_utility.trace(l_proc||'Condition 4 true');
2780 maternity.MPP_start_date:=
2781 (maternity.EWC - sickness_trigger_weeks);
2782 else
2783 --
2784 hr_utility.trace(l_proc||'Condition 4 untrue');
2785 --
2786 maternity.MPP_start_date :=
2787 next_day((maternity.EWC - sickness_trigger_weeks), l_sunday);
2788 end if;
2789 --
2790 else
2791 --
2792 -- Maternity due date is on or after 06-APR-2003,
2793 -- therefore start MPP on the date 4 weeks before
2794 -- EWC regardless of whether it is a Sunday
2795 --
2796 -- Bug No. 2620894: Changed the code so that MPP start date
2797 -- is the next day after first working day
2798 -- of (EWC - sickness_trigger_weeks) week.
2799 if rtrim(to_char((maternity.EWC - sickness_trigger_weeks),'DAY')) = l_sunday then
2800 maternity.MPP_start_date := (maternity.EWC - sickness_trigger_weeks) + 2;
2801 elsif rtrim(to_char ((maternity.EWC - sickness_trigger_weeks),'DAY')) = l_saturday
2802 then
2803 maternity.MPP_start_date := (maternity.EWC - sickness_trigger_weeks) + 3;
2804 else
2805 maternity.MPP_start_date := (maternity.EWC - sickness_trigger_weeks) + 1;
2806 end if;
2807 end if;
2808 --
2809 hr_utility.trace(l_proc||'About to update MPP Start Date');
2810 ssp_mat_upd.upd(p_maternity_id => maternity.maternity_id,
2811 p_MPP_start_date => maternity.MPP_start_date,
2812 p_object_version_number => maternity.object_version_number);
2813 hr_utility.trace(l_proc||'Back from updating MPP Start Date');
2814 end if;
2815 else
2816 if maternity.due_date < fnd_date.canonical_to_date('2003/04/06 00:00:00') then
2817 --
2818 -- Start the MPP on the first Sunday of the sickness
2819 -- as due date before 06-APR-2003
2820 --
2821 hr_utility.trace(l_proc||'Condition 2 untrue');
2822 if rtrim(to_char (p_sickness_start_date,'DAY')) = l_sunday then
2823 hr_utility.trace(l_proc||'Condition 5 true');
2824 first_sunday_of_sickness := p_sickness_start_date;
2825 else
2826 hr_utility.trace(l_proc||'Condition 5 untrue');
2827 first_sunday_of_sickness := next_day(p_sickness_start_date,l_sunday);
2828 end if;
2829 --
2830 -- there is no recorded MPP start date or
2831 -- the MPP start date is later than the first Sunday
2832 if (maternity.MPP_start_date is null or
2833 maternity.MPP_start_date > first_sunday_of_sickness) then
2834 --
2835 hr_utility.trace(l_proc||'Condition 6 true');
2836 --
2837 -- Update the recorded MPP start date.
2838 --
2839 maternity.MPP_start_date := first_sunday_of_sickness;
2840 --
2841 hr_utility.trace(l_proc||'About to update MPP Start Date');
2842 ssp_mat_upd.upd (p_maternity_id => maternity.maternity_id,
2843 p_MPP_start_date => maternity.MPP_start_date,
2844 p_object_version_number=> maternity.object_version_number);
2845 hr_utility.trace(l_proc||'Back from updating MPP Start Date');
2846 end if;
2847 else
2848 --
2849 -- Due date after 06-APR-2003, therefore MPP starts
2850 -- on sickness start date, regardless of whether
2851 -- it is a Sunday
2852 --
2853 -- there is no recorded MPP start date
2854 -- or the MPP_start_date is later than the
2855 -- sickness start date
2856 if (maternity.MPP_start_date is null or
2857 maternity.MPP_start_date > p_sickness_start_date) then
2858 --
2859 -- Bug No. 2620894: Changed the code so that MPP start date
2860 -- is the next day after first working day of p_sickness_start_date.
2861 if rtrim(to_char (p_sickness_start_date,'DAY')) = l_sunday then
2862 maternity.MPP_start_date := p_sickness_start_date + 2;
2863 elsif rtrim(to_char (p_sickness_start_date,'DAY')) = l_saturday then
2864 maternity.MPP_start_date := p_sickness_start_date + 3;
2865 else
2866 maternity.MPP_start_date:= p_sickness_start_date + 1;
2867 end if;
2868 --
2869 hr_utility.trace(l_proc||'About to update MPP Start Date');
2870 ssp_mat_upd.upd (p_maternity_id => maternity.maternity_id,
2871 p_MPP_start_date => maternity.MPP_start_date,
2872 p_object_version_number => maternity.object_version_number);
2873 hr_utility.trace(l_proc||'Back from updating MPP Start Date');
2874 end if;
2875 end if;
2876 end if;
2877 end if;
2878 --
2879 start_disqualifying_period := maternity.MPP_start_date;
2880 --
2881 if start_disqualifying_period is not null then
2882 hr_utility.trace(l_proc||'Condition 7 true');
2883 begin
2884 --
2885 -- The disqualifying period lasts for the period of time
2886 -- allowed by legislation for the Maternity Pay Period
2887 -- (the figure is expressed in weeks so must be converted
2888 -- to days before deriving end date).
2889 --
2890 end_disqualifying_period := start_disqualifying_period
2891 + (l_SMP_element.Maximum_MPP * 7) -1;
2892 hr_utility.trace('start_disqualifying_period : '||
2893 fnd_date.date_to_canonical(start_disqualifying_period));
2894 hr_utility.trace('end_disqualifying_period : '||
2895 fnd_date.date_to_canonical(end_disqualifying_period));
2896 --
2897 exception
2898 when attempt_to_add_to_end_of_time then
2899 --
2900 -- Handle the case where the end date would exceed the
2901 -- maximum date that Oracle can deal with.
2902 --
2903 end_disqualifying_period := hr_general.end_of_time;
2904 --
2905 end;
2906 --
2907 -- the previously calculated last entitlement day is
2908 -- not already the day before the start of the
2909 -- disqualifying period (because we have already been
2910 -- round this loop and created a stoppage before)
2911 -- and the disqualifying period overlaps with the PIW
2912 if (last_entitled_day <> start_disqualifying_period - 1 and
2913 start_disqualifying_period <= PIW_end_date and
2914 end_disqualifying_period >= PIW_start_date) then
2915 create_stoppage(p_withhold_from => start_disqualifying_period,
2916 p_reason => 'Woman in disqualifying period',
2917 p_overridden => l_stoppage_overridden);
2918 --
2919 -- the previously calculated last entitlement day is
2920 -- later than the day before the start of the
2921 -- disqualifying period
2922 -- and the withholding reason is not overridden
2923 if (last_entitled_day > start_disqualifying_period -1 and
2924 not l_stoppage_overridden) then
2925 last_entitled_day := start_disqualifying_period -1;
2926 end if;
2927 end if;
2928 end if;
2929 end loop;
2930 --
2931 hr_utility.set_location('Leaving :'||l_proc,100);
2932 --
2933 end check_disqualifying_period;
2934 --
2935 procedure check_SSP1L is
2936 l_proc varchar2 (72) := g_package||'check_SSP1L';
2937 begin
2938 --
2939 hr_utility.set_location('Entering:'||l_proc,1);
2940 --
2941 -- take prior employment SSP into account in total liability.
2942 if (PIW_start_date - absentee.prior_employment_SSP_paid_to)
2943 <= g_SSP_legislation.linking_period_days then
2944 --
2945 hr_utility.trace('There is prior employment SSP');
2946 --
2947 total_SSP_weeks := absentee.prior_employment_SSP_weeks;
2948 end if;
2949 --
2950 hr_utility.set_location('Leaving :'||l_proc,100);
2951 --
2952 end check_SSP1L;
2953 --
2954 function check_emp_too_old(p_start date,
2955 p_end date) return boolean is
2956 l_temp number;
2957 l_found boolean;
2958
2959 cursor csr_old_stp is
2960 select 1
2961 from ssp_stoppages stp,
2962 ssp_withholding_reasons rea
2963 where stp.absence_attendance_id = g_PIW_id
2964 and stp.withhold_from <= p_start
2965 and stp.withhold_to >= p_end
2966 and stp.reason_id = rea.reason_id
2967 and rea.reason = 'Employee too old';
2968 begin
2969 l_found := false;
2970 if l_age >= 65 then
2971 open csr_old_stp;
2972 fetch csr_old_stp into l_temp;
2973 if csr_old_stp%found then
2974 l_found := true;
2975 end if;
2976 close csr_old_stp;
2977 end if;
2978 return l_found;
2979 end check_emp_too_old;
2980 --
2981 begin
2982 -- Start of do_PIW_calculations
2983 hr_utility.set_location ('Entering:'||l_proc,1);
2984 --
2985 open ssp_SMP_pkg.csr_SMP_element_details(p_effective_date => PIW_start_date);
2986 fetch ssp_SMP_pkg.csr_SMP_element_details into l_SMP_element;
2987 close ssp_SMP_pkg.csr_SMP_element_details;
2988 --
2989 start_disqualifying_period := null;
2990 end_disqualifying_period := null;
2991 --
2992 check_SSP1L;
2993 --
2994 l_age := get_age_at_PIW(p_absence.person_id,l_01_October_06);
2995 --
2996 SSP_rate_figure := SSP_rate (PIW_start_date);
2997 --
2998 l_nos_absences := 0;
2999 absence_table := empty_absence_table;
3000 l_max_absence_end_date := hr_general.start_of_time;
3001 --
3002 for each_abs_tab in PIW loop
3003 l_max_absence_start_date := each_abs_tab.sickness_start_date;
3004 l_max_absence_end_date := each_abs_tab.sickness_end_date;
3005 l_nos_absences := l_nos_absences + 1;
3006 --
3007 hr_utility.trace('PIWl: dates: ' || to_char(l_max_absence_start_date)
3008 || ' -> ' || to_char(l_max_absence_end_date));
3009 --
3010 absence_table.t_sickness_start_date(l_nos_absences) :=
3011 each_abs_tab.sickness_start_date;
3012 absence_table.t_sickness_end_date(l_nos_absences) :=
3013 each_abs_tab.sickness_end_date;
3014 absence_table.t_business_group_id(l_nos_absences) :=
3015 each_abs_tab.business_group_id;
3016 absence_table.t_absence_attendance_id(l_nos_absences) :=
3017 each_abs_tab.absence_attendance_id;
3018 absence_table.t_pregnancy_related_illness(l_nos_absences) :=
3019 each_abs_tab.pregnancy_related_illness;
3020 end loop;
3021 --
3022 for each_absence in 1..l_nos_absences loop
3023 l_sickness_start_date :=
3024 absence_table.t_sickness_start_date(each_absence);
3025 l_sickness_end_date :=
3026 absence_table.t_sickness_end_date(each_absence);
3027 l_business_group_id :=
3028 absence_table.t_business_group_id(each_absence);
3029 l_absence_attendance_id :=
3030 absence_table.t_absence_attendance_id(each_absence);
3031 l_pregnancy_related_illness :=
3032 absence_table.t_pregnancy_related_illness(each_absence);
3033 --
3034 hr_utility.trace('In PIW Loop');
3035 hr_utility.trace(' for sickness dated: ' ||
3036 to_char(l_sickness_start_date) || ' -> ' ||
3037 to_char(l_sickness_end_date));
3038 --
3039 if ssp_ssp_pkg.absence_is_a_PIW (p_person_id => p_absence.person_id,
3040 p_sickness_start_date => l_sickness_start_date,
3041 p_sickness_end_date => l_sickness_end_date,
3042 p_absence_attendance_id => l_absence_attendance_id) then
3043 --
3044 -- Check for a disqualifying period from SSP because of maternity
3045 --
3046 check_disqualifying_period(p_sickness_start_date => l_sickness_start_date,
3047 p_sickness_end_date => l_sickness_end_date,
3048 p_pregnancy_related_illness => l_pregnancy_related_illness);
3049 --
3050 <<THIS_WEEK>>
3051 --
3052 -- Calculate weekly figures for SSP, accumulating the totals as we
3053 -- go so that we can find the amounts of liability both within each
3054 -- week and over all.
3055 --
3056 declare
3057 -- Bug 441738
3058 -- start_date date := next_day (each_absence.sickness_start_date,
3059 -- l_sunday) - 7;
3060 --
3061 -- Bug 502108 - start_date/end_date initialisation moved after begin.
3062 --
3063 start_date date;
3064 end_date date;
3065 SSP_rate_figure number;
3066 qualifying_days number;
3067 gross_SSP_days_due number;
3068 SSP_days_due number;
3069 withheld_days number;
3070 SSP_week_fraction number;
3071 begin
3072 start_date := l_sickness_start_date;
3073 SSP_rate_figure := SSP_rate(start_date);
3074 --
3075 -- Bug 593097 - force the end_date to be the next Saturday,
3076 -- after the Sunday before the sickness_start_date, rather
3077 -- than the Saturday after the sickness_start_date, which
3078 -- causes an error if the sickness_start_date is itself a
3079 -- Saturday.
3080 end_date := least(next_day(next_day(l_sickness_start_date,l_sunday) -7, l_saturday),
3081 nvl (rate_end_date, hr_general.end_of_time),last_entitled_day);
3082 --
3083 if start_date > last_entitled_day then
3084 -- There is a stoppage which prevents the entry creation
3085 --
3086 if start_date > max_PIW_end_date then
3087 raise PIW_max_length_reached;
3088 else
3089 raise stoppage_invoked;
3090 end if;
3091 end if;
3092 --
3093 -- Bug 504386 - Call qualifying_days_in_period for the whole sickness absence,
3094 -- so that the pl/sql table normal_pattern in hr_calendar_pkg only need be
3095 -- populated once.
3096 --
3097 -- Bug 655707 - Calling qualifying_days_in_period with PIW start/end dates, thus
3098 -- if there is more than absence then the calendar doesn't have to be rederived.
3099 --
3100 -- Bug 589806 - If an open ended absence is entered then
3101 -- each_absence.sickness_end_date is defaulted to the 'end_of_time'. Hence
3102 -- performance is terrible. Now, if the is sickness_end_date is the end_of_time
3103 -- then 203 days are added to the sickness_start_date. 203 days will allow the
3104 -- pl/sql table normal_pattern in hr_calendar_pkg to be populated with the
3105 -- dates to cover the whole open ended absence.
3106 --
3107 -- Bug 655707 - Changed 203 to be Max SSP period, unless this is null, so that
3108 -- this is not legislation specific.
3109 --
3110 cal_end_date := l_max_absence_end_date;
3111 --
3112 if cal_end_date is null or cal_end_date = hr_general.end_of_time then
3113 cal_end_date := l_max_absence_start_date +
3114 ((nvl(g_SSP_legislation.MAXIMUM_SSP_PERIOD,28) + 1) * 7);
3115 else
3116 cal_end_date := ssp_smp_support_pkg.end_of_week(l_max_absence_end_date);
3117 end if;
3118 --
3119 hr_utility.trace('Calendar end date: '||to_char(cal_end_date));
3120 cal_start_date := ssp_smp_support_pkg.start_of_week(nvl(PIW_start_date, start_date));
3121 --
3122 if cal_end_date > max_cal_end_date or
3123 cal_start_date < min_cal_start_date then
3124 all_pat_calendar_days := qualifying_days_in_period(
3125 cal_start_date, cal_end_date,
3126 p_absence.person_id,
3127 p_absence.business_group_id,
3128 p_processing_level => 1);
3129 --
3130 max_cal_end_date := cal_end_date;
3131 min_cal_start_date := cal_start_date;
3132 end if;
3133 --
3134 -- Bug 655707 - build a PL/SQL table of stoppages only once for
3135 -- each absence, so we don't have to keep calling
3136 -- csr_stoppages cursor for every single day of each
3137 -- absence. Also, if no stoppages are found for the
3138 -- whole absence then we do not have to keep calling
3139 -- qualifying_days_in_period so many times.
3140 --
3141 stoppage_table := empty_stoppage_table;
3142 l_nos_stoppages := 0;
3143 l_min_withheld_from := hr_general.end_of_time;
3144 l_max_withheld_to := hr_general.start_of_time;
3145 --
3146 for each_stoppage in csr_stoppages(PIW_start_date, cal_end_date)
3147 loop
3148 l_nos_stoppages := l_nos_stoppages + 1;
3149 stoppage_table.t_withheld_from(l_nos_stoppages):=
3150 each_stoppage.withhold_from;
3151 stoppage_table.t_withheld_to(l_nos_stoppages):=
3152 each_stoppage.withhold_to;
3153 --
3154 if each_stoppage.withhold_from < l_min_withheld_from
3155 then
3156 l_min_withheld_from := each_stoppage.withhold_from;
3157 end if;
3158 --
3159 if each_stoppage.withhold_to > l_max_withheld_to
3160 then
3161 l_max_withheld_to := each_stoppage.withhold_to;
3162 end if;
3163 end loop;
3164 --
3165 hr_utility.trace('No of stoppages between ' ||
3166 to_char(l_min_withheld_from) ||
3167 ' and ' || to_char(l_max_withheld_to) ||
3168 ': ' || to_char(l_nos_stoppages));
3169 --
3170 -- Cycle through each week of the absence until the end of the
3171 -- sickness.
3172 --
3173 -- Bug 555505 - made start_date <= rather than < each_ab.. so that
3174 -- a one day absence will be accounted for.
3175 --
3176 -- Bug 5444012 - check for total day left
3177 days_remaining := g_SSP_legislation.maximum_SSP_period * 7;
3178 days_remaining := greatest(0, days_remaining - (total_SSP_weeks * 7));
3179 --
3180 while start_date <= l_sickness_end_date loop
3181 N := N + 1;
3182 --
3183 -- Find number of qualifying days in the current calendar week.
3184 --
3185 hr_utility.trace('Finding qualifying days in current week...');
3186 qualifying_days := greatest (1,qualifying_days_in_period(
3187 ssp_smp_support_pkg.start_of_week(start_date),
3188 ssp_smp_support_pkg.end_of_week(start_date),
3189 p_absence.person_id,
3190 p_absence.business_group_id,
3191 p_processing_level => 2));
3192 --
3193 if weeks_remaining <= 1 then
3194 --
3195 -- We are in the final week of the employer's SSP liability.
3196 -- We must work out how many days of liability remain.
3197 --
3198 days_remaining := ceil(weeks_remaining /
3199 ---------------------------------------
3200 standard_week_fraction (qualifying_days));
3201 --
3202 -- Now drag the end date back to the date on which
3203 -- SSP ceases to be payable.
3204 --
3205 while qualifying_days_in_period(start_date,
3206 end_date,
3207 p_absence.person_id,
3208 p_absence.business_group_id,
3209 p_processing_level => 2) > days_remaining loop
3210 end_date := end_date -1;
3211 end loop;
3212 --
3213 end if;
3214 --
3215 -- Calculate the number of qualifying days the person was sick.
3216 --
3217 -- Calculate the absent qualifying days.
3218 --
3219 -- The gross number of SSP days due is the number of qualifying
3220 -- days in the period of absence being examined.
3221 --
3222 gross_SSP_days_due :=
3223 qualifying_days_in_period(
3224 greatest (start_date,
3225 l_sickness_start_date),
3226 least (end_date,
3227 l_sickness_end_date),
3228 p_absence.person_id,
3229 p_absence.business_group_id,
3230 p_processing_level => 2);
3231 --
3232 -- Calculate how many of the qualifying days were covered by a
3233 -- waiting day or temporary stoppage. NB withheld_days do not
3234 -- include waiting days but the calculation of waiting days is
3235 -- done within the stopped_days function and stored in a
3236 -- variable.
3237 --
3238 perf_start_date := greatest(start_date, l_sickness_start_date);
3239 perf_end_date := least(end_date, l_sickness_end_date);
3240 waiting_days_this_period := 0;
3241 --
3242 if (l_nos_stoppages > 0 and
3243 perf_start_date <= l_max_withheld_to and
3244 perf_end_date >= l_min_withheld_from)
3245 or waiting_days_left > 0
3246 then
3247 withheld_days := stopped_days(perf_start_date,
3248 perf_end_date,
3249 p_absence.person_id);
3250 else
3251 withheld_days := 0;
3252 end if;
3253 --
3254 SSP_days_due := gross_SSP_days_due - withheld_days;
3255 --
3256 -- Calculate the fraction of an SSP week which this week's absence
3257 -- constitutes
3258 --
3259 if SSP_days_due >= days_remaining then
3260 --
3261 -- We must have done the final week calculations and found that
3262 -- the absentee was sick for as long or longer than the period
3263 -- for which SSP is payable. Make this week's fraction equal
3264 -- the weeks remaining so that we come to exactly the correct
3265 -- total_SSP_weeks figure.
3266 --
3267 SSP_week_fraction := g_SSP_legislation.maximum_SSP_period - total_SSP_weeks;
3268 else
3269 SSP_week_fraction := (SSP_days_due - waiting_days_this_period) /
3270 -----------------------------------------
3271 qualifying_days;
3272 end if;
3273 --
3274 -- Find the daily rate by dividing the weekly SSP rate by the
3275 -- number of qualifying days in the calendar week.
3276 --
3277 daily_rate := SSP_rate (this_week.start_date)/
3278 --------------------------------
3279 qualifying_days;
3280 --
3281 -- Determine which payroll period to put the new entry in
3282 --
3283 -- Initialise in/out parameters to get_entry_details procedure
3284 --
3285 new_entry.assignment_id (N) := null;
3286 new_entry.element_link_id (N) := null;
3287 --
3288 ssp_smp_support_pkg.get_entry_details(p_date_earned => this_week.start_date,
3289 /*p_date_earned => this_week.end_date,*/
3290 p_last_process_date => absentee.last_standard_process_date,
3291 p_person_id => p_absence.person_id,
3292 p_element_type_id => g_SSP_legislation.element_type_id,
3293 p_assignment_id => new_entry.assignment_id (N),
3294 p_element_link_id => new_entry.element_link_id (N),
3295 p_effective_start_date => new_entry.effective_start_date (N),
3296 p_effective_end_date => new_entry.effective_end_date (N));
3297 --
3298 -- Store this week's figures in the hypothetical entry
3299 --
3300 new_entry.date_from(N) := greatest (this_week.start_date,l_sickness_start_date);
3301 new_entry.date_to(N) := least (this_week.end_date,l_sickness_end_date);
3302 new_entry.qualifying_days(N) := this_week.gross_SSP_days_due;
3303 new_entry.SSP_weeks(N) := round (this_week.SSP_week_fraction,3);
3304 new_entry.SSP_days_due(N) := this_week.gross_SSP_days_due - waiting_days_this_period;
3305 new_entry.withheld_days(N) := this_week.withheld_days;
3306 new_entry.rate(N) := SSP_rate (this_week.start_date);
3307 new_entry.amount(N) := round (((SSP_days_due - waiting_days_this_period)
3308 * daily_rate)+0.004,2);
3309 new_entry.dealt_with(N) := 'FALSE';
3310 --
3311 if check_emp_too_old(l_sickness_start_date, l_sickness_end_date) then
3312 total_SSP_weeks := total_SSP_weeks + (this_week.withheld_days/qualifying_days);
3313 end if;
3314 --
3315 -- Increment the total of SSP weeks by this week's figures.
3316 --
3317 total_SSP_weeks := total_SSP_weeks + SSP_week_fraction;
3318 weeks_remaining := g_SSP_legislation.maximum_SSP_period - total_SSP_weeks;
3319 --
3320 -------------------------------------------------
3321 -- Trace the calculations for debugging purposes
3322 begin
3323 hr_utility.trace ('----------------------------');
3324 hr_utility.trace (l_proc||'Entry number ' ||to_char (N));
3325 hr_utility.trace (l_proc||' Payroll period '||to_char (new_entry.effective_start_date (N)) ||' to '
3326 ||to_char (new_entry.effective_end_date (N)));
3327 hr_utility.trace (l_proc||' from = '||to_char (new_entry.date_from (N))||' to = ' ||to_char ( new_entry.date_to (N)));
3328 hr_utility.trace (l_proc||' qualifying_days = '||to_char (new_entry.qualifying_days (N)));
3329 hr_utility.trace (l_proc||' SSP_weeks = '||to_char (new_entry.SSP_weeks (N)));
3330 hr_utility.trace (l_proc||' SSP_days_due = '||to_char (new_entry.SSP_days_due (N)));
3331 hr_utility.trace (l_proc||' withheld_days = '||to_char (new_entry.withheld_days (N)));
3332 hr_utility.trace (l_proc||' rate = '||to_char (new_entry.rate (N)));
3333 hr_utility.trace (l_proc||' amount ' || to_char (daily_rate)
3334 ||' ( ' || to_char (new_entry.SSP_days_due (N))
3335 ||' - ' || to_char (new_entry.withheld_days (N))
3336 ||') = '|| to_char (new_entry.amount (N)));
3337 hr_utility.trace (l_proc||' Total SSP weeks = '||to_char (total_ssp_weeks));
3338 hr_utility.trace (l_proc||' weeks remaining = '||to_char (weeks_remaining));
3339 hr_utility.trace (l_proc||' waiting_days_left = '||to_char (waiting_days_left));
3340 hr_utility.trace (l_proc||' waiting_days_this_period = '||to_char (waiting_days_this_period));
3341 hr_utility.trace (l_proc||' Element_link_id = '||to_char (new_entry.element_link_id (N)));
3342 hr_utility.trace (l_proc||' Assignment_id = '||to_char (new_entry.assignment_id (N)));
3343 hr_utility.trace ('----------------------------');
3344 exception
3345 when not_logged_on or program_error or storage_error then
3346 -- a serious error must be indicated.
3347 raise;
3348 when others then
3349 -- Do not let minor errors in debugging code stop the process
3350 null;
3351 end;
3352 hr_utility.trace ('Weeks remaining:'||to_char(weeks_remaining));
3353 hr_utility.trace (' end date :'||to_char(end_date));
3354 hr_utility.trace (' last ent day :'||to_char(last_entitled_day));
3355 --
3356 --if weeks_remaining <= 0 then --old line
3357 if weeks_remaining <= 0.00000000000000000000000000000000000001 then --line added for bug 6860926
3358 raise maximum_SSP_paid;
3359 end if;
3360 --
3361 if end_date = last_entitled_day then
3362 -- Stop processing SSP if we have reached the date when a
3363 -- stoppage precludes any further payment.
3364 --
3365 -- bug 2984845 - check if entitlement has ended due to
3366 -- max PIW length being reached
3367 -- the original check will never work as
3368 -- end_date will never equal max_piw_end_date
3369 -- so check if max PIW end date between start and end
3370 -- dates of current sickness
3371 -- IF end_date = max_piw_end_date
3372 --
3373 -- Bug 2984458 Added condition to check if max_piw_end_date
3374 -- is less than Actual Termination Date to raise the exception
3375 if ((max_piw_end_date between l_sickness_start_date and l_sickness_end_date) and
3376 (max_piw_end_date < absentee.actual_termination_date)) then
3377 raise piw_max_length_reached;
3378 else
3379 raise stoppage_invoked;
3380 end if;
3381 end if;
3382 --
3383 -- Move on to the next week of absence.
3384 --
3385 if rate_end_date between start_date and end_date then
3386 --
3387 -- The SSP rate changes during this week so we move on to
3388 -- the latter part of the week for the new rate.
3389 --
3390 start_date := rate_end_date + 1;
3391 --
3392 -- The end of the next period to be examined is the lesser of
3393 -- the end of the week and the first permanent stoppage date.
3394 --
3395 end_date := least (ssp_smp_support_pkg.end_of_week (start_date),
3396 last_entitled_day);
3397 --
3398 else
3399 --
3400 -- There is no rate change during the current week, but there
3401 -- may be one next week so move the dates to the beginning
3402 -- of next week and the greater of the rate end date and the
3403 -- calendar week end date and the withholding date.
3404 --
3405 start_date := next_day (start_date, l_sunday);
3406 end_date := least (next_day (start_date,l_saturday),
3407 nvl (rate_end_date,hr_general.end_of_time),
3408 nvl (last_entitled_day,hr_general.end_of_time));
3409 end if;
3410 --
3411 end loop;
3412 end;
3413 end if;
3414 end loop;
3415 --
3416 hr_utility.set_location('Leaving :'||l_proc,100);
3417 --
3418 exception
3419 when stoppage_invoked then
3420 --
3421 -- Stop processing SSP as we have reached the end of the entitlement.
3422 --
3423 null;
3424 --
3425 when maximum_SSP_paid then
3426 create_stoppage(p_withhold_from => new_entry.date_to (N) + 1,
3427 p_reason => 'Maximum weeks SSP paid',
3428 p_overridden => l_stoppage_overridden);
3429 --
3430 when PIW_max_length_reached then
3431 create_stoppage(p_withhold_from => max_PIW_end_date,
3432 p_reason => 'PIW lasted maximum time',
3433 p_overridden => l_stoppage_overridden);
3434 --
3435 when zero_divide then
3436 --
3437 -- One of the calculations must have gone wrong
3438 --
3439 fnd_message.set_name ('PAY','HR_6153_ALL_PROCEDURE_FAIL');
3440 fnd_message.set_token ('PROCEDURE',l_proc);
3441 fnd_message.set_token ('STEP','1');
3442 fnd_message.raise_error;
3443 --
3444 end do_PIW_calculations;
3445 --
3446 /*-------------------------------------------------------*/
3447 /* Check that the person earns enough to qualify for SSP */
3448 /*-------------------------------------------------------*/
3449 procedure check_average_earnings is
3450 --
3451 -- Get the defined average earnings
3452 --
3453 cursor csr_average_earnings is
3454 select average_earnings_amount
3455 from ssp_earnings_calculations
3456 where person_id = p_absence.person_id
3457 and effective_date = PIW_start_date;
3458 --
3459 l_proc varchar2(72):= g_package||'check_average_earnings';
3460 l_average_earnings number := null;
3461 l_dummy number;
3462 l_dummy2 number; -- nocopy fix, placeholder variable
3463 l_payment_periods number := null; --DFoster 1304683
3464 l_absence_category varchar2(30) := 'S'; --DFoster
3465 l_user_entered varchar2(30); --DFoster 1304683
3466 l_reason_for_no_earnings varchar2 (80);
3467 l_stoppage_overridden boolean := FALSE;
3468 l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
3469 l_PIW_date date;
3470 begin
3471 hr_utility.set_location('Entering:'||l_proc,1);
3472 --
3473 -- Look on the table for previously-calculated earnings figure
3474 --
3475 open csr_average_earnings;
3476 fetch csr_average_earnings into l_average_earnings;
3477 --
3478 -- No average earnings are recorded
3479 if csr_average_earnings%notfound then
3480 --
3481 -- Calculate and save the average earnings.
3482 --
3483 ssp_ern_ins.ins(p_earnings_calculations_id => l_dummy,
3484 p_object_version_number => l_dummy2,
3485 p_person_id => p_absence.person_id,
3486 p_effective_date => PIW_start_date,
3487 p_average_earnings_amount => l_average_earnings,
3488 p_user_entered => l_user_entered, --1304683
3489 p_absence_category => l_absence_category, --1304683
3490 p_payment_periods => l_payment_periods); --1304683
3491 --
3492 end if;
3493 --
3494 close csr_average_earnings;
3495 --
3496 if l_average_earnings = 0 then
3497 --
3498 -- If the average earnings figure returned is zero then we must
3499 -- check that no error message was set. Error messages will be set
3500 -- for system-generated average earnings when the earnings could not
3501 -- be derived for some reason, but to allow this procedure to
3502 -- continue, no error will be raised.
3503 --
3504 l_reason_for_no_earnings :=ssp_smp_support_pkg.average_earnings_error;
3505 --
3506 if l_reason_for_no_earnings is not null then
3507 create_stoppage (p_withhold_from => PIW_start_date,
3508 p_reason => l_reason_for_no_earnings,
3509 p_overridden => l_stoppage_overridden);
3510 --
3511 if not l_stoppage_overridden then
3512 raise earnings_too_low;
3513 end if;
3514 end if;
3515 end if;
3516 --
3517 -- the average earnings are lower than the NI Lower Earnings Limit
3518 if l_average_earnings < ssp_smp_support_pkg.NI_Lower_Earnings_Limit(PIW_start_date) then
3519 --
3520 -- The person does not earn enough to qualify for SSP
3521 --
3522 create_stoppage (p_withhold_from => PIW_start_date,
3523 p_reason => 'Earnings too low',
3524 p_overridden => l_stoppage_overridden);
3525 --
3526 if not l_stoppage_overridden then
3527 raise earnings_too_low;
3528 end if;
3529 end if;
3530 --
3531 hr_utility.set_location('Leaving :'||l_proc,100);
3532 --
3533 end check_average_earnings;
3534 --
3535 /*----------------------------------------*/
3536 /* Check for medical evidence of sickness */
3537 /*----------------------------------------*/
3538 procedure check_evidence is
3539
3540 --
3541 -- Return a row if evidence exists.
3542 cursor csr_evidence (c_absence_id number) is
3543 select 1
3544 from ssp_medicals
3545 where absence_attendance_id = c_absence_id
3546 and evidence_status = 'CURRENT';
3547 --
3548 -- Returns the setting of the flag which notes the user's
3549 -- requirement for medical evidence
3550 cursor csr_user_requirement (c_business_id number) is
3551 select org_information1 EVIDENCE_REQUIRED
3552 from hr_organization_information
3553 where organization_id = c_business_id
3554 and org_information_context = 'Sickness Control Rules';
3555 --
3556 l_proc varchar2(72):= g_package||'check_evidence';
3557 l_evidence_required varchar2 (30) := 'N';
3558 l_dummy integer (1);
3559 l_stoppage_overridden boolean := FALSE;
3560 --
3561 begin
3562 hr_utility.set_location('Entering:'||l_proc,1);
3563 --
3564 /*BUG 2984577- Added code to handle linked absences.*/
3565 for each_absence in PIW loop
3566 -- Determine whether or not the user requires medical evidence of
3567 -- incapacity to be presented by employees
3568 --
3569 open csr_user_requirement(each_absence.business_group_id);
3570 fetch csr_user_requirement into l_evidence_required;
3571 close csr_user_requirement;
3572 --
3573 -- the user requires medical evidence of incapacity
3574 if l_evidence_required = 'Y' then
3575 --
3576 -- Look for a sick note.
3577 open csr_evidence(each_absence.absence_attendance_id);
3578 fetch csr_evidence into l_dummy;
3579 hr_utility.set_location('Entering:'||l_dummy,5555);
3580 --
3581 -- no medical evidence was found for this absence
3582 if csr_evidence%notfound then
3583 /*BUG 2984577 Added exception */
3584 if each_absence.sickness_end_date = hr_general.end_of_time then
3585 create_stoppage (p_withhold_from => each_absence.sickness_start_date,
3586 p_reason => 'No acceptable evidence in time',
3587 p_overridden => l_stoppage_overridden);
3588 last_entitled_day := least(last_entitled_day,each_absence.sickness_start_date - 1);
3589 else
3590 create_stoppage (p_withhold_from => each_absence.sickness_start_date,
3591 p_withhold_to => each_absence.sickness_end_date,
3592 p_reason => 'No acceptable evidence in time',
3593 p_overridden => l_stoppage_overridden);
3594 end if;
3595 --
3596 end if;
3597 close csr_evidence;
3598 end if;
3599 end loop;
3600 --
3601 hr_utility.set_location('Leaving :'||l_proc,100);
3602 --
3603 end check_evidence;
3604 --
3605 /*--------------------------------------------------------------*/
3606 /* Check that the person's period of service is long enough to */
3607 /* qualify for SSP */
3608 /*--------------------------------------------------------------*/
3609 procedure check_service is
3610 l_stoppage_overridden boolean := FALSE;
3611 l_proc varchar2 (72) := g_package||'check_service';
3612 begin
3613 --
3614 hr_utility.set_location('Entering:'||l_proc,1);
3615 -- the employment is scheduled to end
3616 -- bug 2984458 to avoid displaying 'Contract ends' when
3617 -- employee is deceased
3618 if (absentee.actual_termination_date < hr_general.end_of_time) and
3619 nvl(absentee.leaving_reason,'No Reason') <>'D' then -- Only If termination reason is 'Death' then this stoppage should be not generated.
3620 create_stoppage(p_withhold_from => absentee.actual_termination_date +1,
3621 p_reason => 'Contract ends',
3622 p_overridden => l_stoppage_overridden);
3623 end if;
3624 --
3625 hr_utility.set_location('Leaving :'||l_proc,100);
3626 --
3627 end check_service;
3628 --
3629 /*-----------------------------------------------------------*/
3630 /* Check that if the person has a DSS linking letter and if */
3631 /* so, is the end date after the start date of current PIW ? */
3632 /*-----------------------------------------------------------*/
3633 procedure check_linking_letter is
3634 l_stoppage_overridden boolean := FALSE;
3635 l_proc varchar2(80) := g_package||'check_linking_letter';
3636 --
3637 -- Get current linking letter end date off the person record
3638 cursor csr_link_letter is
3639 select fnd_date.canonical_to_date(ppf.per_information11)
3640 from per_all_people_f ppf
3641 where ppf.person_id = p_absence.person_id
3642 and ppf.per_information_category = 'GB'
3643 and sysdate between ppf.effective_start_date and ppf.effective_end_date;
3644
3645 l_link_letter_end_date date;
3646 --
3647 begin
3648 --
3649 hr_utility.set_location('Entering:'||l_proc,1);
3650 --
3651 open csr_link_letter;
3652 fetch csr_link_letter into l_link_letter_end_date;
3653 close csr_link_letter;
3654
3655 hr_utility.trace('Linking letter end date: '||l_link_letter_end_date);
3656 --
3657 -- check if link letter end date is after start date of current PIW
3658 if l_link_letter_end_date is not null and
3659 l_link_letter_end_date > PIW_start_date then
3660 -- create stoppage from the start of the current PIW to the end
3661 create_stoppage(p_withhold_from => PIW_start_date,
3662 p_withhold_to => PIW_end_date,
3663 p_reason => 'Linking letter',
3664 p_overridden => l_stoppage_overridden);
3665 end if;
3666 --
3667 hr_utility.set_location('Leaving :'||l_proc,100);
3668 --
3669 end check_linking_letter;
3670 --
3671 --
3672 begin
3673 --
3674 -- Start of Entitled_to_SSP
3675 --
3676 hr_utility.set_location('Entering:'||l_proc,1);
3677 --
3678 new_entry := empty_record;
3679 --
3680 -- SSP not installed or the absence is not sick leave
3681 if ((not SSP_is_installed) or p_absence.sickness_start_date is null) then
3682 raise no_prima_facia_entitlement;
3683 else
3684 --
3685 -- Get details of the absentee
3686 --
3687 open csr_person (p_absence.sickness_start_date, p_absence.person_id);
3688 fetch csr_person into absentee;
3689 --
3690 -- the person does not exist or has no current period of service
3691 if csr_person%notfound then
3692 close csr_person;
3693 raise no_prima_facia_entitlement;
3694 end if;
3695 --
3696 close csr_person;
3697 --
3698 -- Calculate the start and end of the linked series of PIWs.
3699 --
3700 PIW_start_date := linked_PIW_start_date (p_absence.person_id,
3701 p_absence.sickness_start_date,
3702 p_absence.sickness_end_date,
3703 p_absence.absence_attendance_id);
3704 --
3705 hr_utility.trace('Piw_start_date ' || to_char(PIW_start_date,'DD-MON-YYYY'));
3706 --
3707 -- The absence passed in is not a PIW
3708 if PIW_start_date is null then
3709 -- Start Bug 418895 fixing
3710 --
3711 -- Set paramenters to delete all the associated entries
3712 --
3713 g_PIW_id := p_absence.absence_attendance_id;
3714 new_entry := empty_record;
3715 PIW_start_date := p_absence.sickness_start_date;
3716 PIW_end_date := hr_general.end_of_time;
3717 --
3718 -- Delete stoppages
3719 --
3720 for obsolete_stoppage in csr_old_stoppages loop
3721 ssp_stp_del.del(p_stoppage_id => obsolete_stoppage.stoppage_id,
3722 p_object_version_number => obsolete_stoppage.object_version_number);
3723 end loop;
3724 -- End Bug 418895 fixing
3725 raise no_prima_facia_entitlement;
3726 end if;
3727 --
3728 PIW_end_date := nvl (linked_PIW_end_date (p_absence.person_id,
3729 p_absence.sickness_start_date,
3730 p_absence.sickness_end_date,
3731 p_absence.absence_attendance_id),
3732 hr_general.end_of_time);
3733 --
3734 hr_utility.trace('Piw_end_date ' || to_char(PIW_end_date,'DD-MON-YYYY'));
3735 --
3736 g_PIW_id := nvl(p_absence.linked_absence_id,p_absence.absence_attendance_id);
3737 --
3738 hr_utility.trace('Piw_id ' || to_char(g_PIW_id));
3739 --
3740 -- Get the legislative parameters for SSP
3741 --
3742 get_SSP_element (p_absence.sickness_start_date);
3743 --
3744 max_PIW_end_date := add_months (PIW_start_date,(12 * g_SSP_legislation.maximum_linked_PIW_years));
3745 --
3746 -- Remove any previously created stoppages (excluding user-entered ones and
3747 -- overridden ones)
3748 --
3749 for obsolete_stoppage in csr_old_stoppages loop
3750 --
3751 -- Delete stoppage
3752 --
3753 ssp_stp_del.del(p_stoppage_id => obsolete_stoppage.stoppage_id,
3754 p_object_version_number => obsolete_stoppage.object_version_number);
3755 end loop;
3756 --
3757 get_last_entitled_day;
3758 --
3759 if last_entitled_day < p_absence.sickness_start_date then
3760 --
3761 -- The PIW is after a permanent stoppage
3762 --
3763 raise no_prima_facia_entitlement;
3764 end if;
3765 --
3766 -- Check entitlement to SSP
3767 --
3768 check_service;
3769 check_age;
3770 check_death;
3771 check_average_earnings;
3772 check_evidence;
3773 check_linking_letter;
3774 --
3775 -- Work out the amounts of SSP due in entries covering approx. one week each.
3776 --
3777 do_PIW_calculations;
3778 --
3779 hr_utility.set_location('Leaving :'||l_proc,100);
3780 --
3781 -- If we have got this far then the person is entitled to SSP
3782 -- (though stoppages may apply).
3783 --
3784 return TRUE;
3785 end if;
3786 exception
3787 when no_prima_facia_entitlement or employee_too_old or
3788 earnings_too_low or evidence_not_available then
3789 hr_utility.set_location('Leaving :'||l_proc||', Exception',999);
3790 --
3791 return FALSE; -- employee not entitled to SSP
3792 --
3793 end entitled_to_SSP;
3794 --------------------------------------------------------------------------------
3795 procedure generate_payments (p_entitled_to_SSP in boolean) is
3796 --
3797 -- Turn SSP entries in the internal data structure into actual element entries.
3798 -- Take account of entries which have already been created for the PIW.
3799 --
3800 cursor csr_existing_entries is
3801 --
3802 -- The entries previously created for this PIW
3803 --
3804 select entry.element_entry_id,
3805 entry.element_link_id,
3806 entry.assignment_id,
3807 entry.effective_start_date,
3808 entry.effective_end_date,
3809 max(decode(inp.name,c_rate_name,
3810 to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
3811 g_SSP_correction.input_currency_code)),null)) RATE,
3812 /* Use the SSP_correction's input_currency_code as *
3813 * the SSP_legislation global may not be populated */
3814 -------------------------------------------------------------------------
3815 -- Changes for bug 1020757: --
3816 -- --
3817 -- The changeformat function will return the screen_entry_value in the --
3818 -- external display format. This is not always 'DD-MON-YYYY' so that --
3819 -- should not be hardcoded. Better to use fnd_date.chardate_to_date --
3820 -- which will convert the display format to a date. --
3821 -- This will fix bug 1020757. --
3822 -------------------------------------------------------------------------
3823 /* max(decode(inp.name,c_from_name, */
3824 /* to_date(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom, */
3825 /* g_SSP_correction.input_currency_code),'DD-MON-YYYY'),null)) DATE_FROM, */
3826 /* max(decode(inp.name,c_to_name, */
3827 /* to_date(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom, */
3828 /* g_SSP_correction.input_currency_code),'DD-MON-YYYY'),null)) DATE_TO, */
3829 max(decode(inp.name,c_from_name,
3830 fnd_date.chardate_to_date(hr_chkfmt.changeformat(eev.screen_entry_value,
3831 inp.uom,g_SSP_correction.input_currency_code)),null)) DATE_FROM,
3832 max(decode(inp.name,c_to_name,
3833 fnd_date.chardate_to_date(hr_chkfmt.changeformat(eev.screen_entry_value,
3834 inp.uom,g_SSP_correction.input_currency_code)),null)) DATE_TO,
3835 -- End of Changes for bug 1020757
3836 ------------------------------------------------------------------------
3837 max(decode(inp.name,c_amount_name,
3838 to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
3839 g_SSP_correction.input_currency_code)),null)) AMOUNT,
3840 max(decode(inp.name,c_withheld_days_name,
3841 to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
3842 g_SSP_correction.input_currency_code)),null)) WITHHELD_DAYS,
3843 max(decode(inp.name,c_SSP_weeks_name,
3844 to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
3845 g_SSP_correction.input_currency_code)),null)) SSP_WEEKS,
3846 max(decode(inp.name,c_SSP_days_due_name,
3847 to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
3848 g_SSP_correction.input_currency_code)),null)) SSP_DAYS_DUE,
3849 max(decode(inp.name,c_qualifying_days_name,
3850 to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
3851 g_SSP_correction.input_currency_code)),null)) QUALIFYING_DAYS
3852 from pay_input_values_f inp,
3853 pay_element_entry_values_f eev,
3854 pay_element_entries_f ENTRY,
3855 per_all_assignments_f ASGT /* adding this join speeds up the entry query */
3856 where creator_type = c_SSP_creator_type
3857 and creator_id = g_PIW_id
3858 and asgt.person_id = absentee.person_id
3859 and asgt.assignment_id = entry.assignment_id
3860 and entry.effective_start_date between asgt.effective_start_date
3861 and asgt.effective_end_date
3862 and eev.element_entry_id = entry.element_entry_id
3863 and eev.effective_start_date between entry.effective_start_date
3864 and entry.effective_end_date
3865 and eev.input_value_id +0 = inp.input_value_id
3866 and inp.name in (c_rate_name,c_from_name,c_to_name,c_amount_name,
3867 c_withheld_days_name,c_SSP_weeks_name,
3868 c_SSP_days_due_name,c_qualifying_days_name)
3869 and inp.effective_start_date <= eev.effective_end_date
3870 and inp.effective_end_date >= eev.effective_start_date
3871 and not exists (
3872 /* Do not select entries which have already had reversal action *
3873 * taken against them because they are effectively cancelled out */
3874 select 1
3875 from pay_element_entries_f ENTRY2
3876 where entry.element_entry_id = entry2.target_entry_id
3877 and entry.assignment_id = entry2.assignment_id)
3878 and inp.element_type_id <> g_SSP_correction.element_type_id
3879 /* Do not select reversal entries */
3880 group by entry.element_entry_id, entry.element_link_id, entry.assignment_id,
3881 entry.effective_start_date, entry.effective_end_date;
3882 --
3883 l_proc varchar2 (72) := g_package||'generate_payments';
3884 l_invalid_entry boolean;
3885 l_dummy number;
3886 Y integer := 0;
3887 --
3888 begin
3889 --
3890 hr_utility.set_location('Entering:'||l_proc,1);
3891 hr_utility.trace(l_proc||' PIW_START_DATE:'||PIW_start_date);
3892 --
3893 if PIW_start_date is not null
3894 then
3895 -- begin
3896 -- Y:=0;
3897 -- loop
3898 -- Y:=Y+1;
3899 -- hr_utility.trace('NEW ENTRY '||to_char(Y));
3900 -- hr_utility.trace(' Date From: '||to_char(new_entry.date_from(Y)));
3901 -- hr_utility.trace(' Date To : '||to_char(new_entry.date_to(Y)));
3902 -- hr_utility.trace(' Amount : '||to_char(new_entry.amount(Y)));
3903 -- hr_utility.trace(' Days Due : '||to_char(new_entry.ssp_days_due(Y)));
3904 -- hr_utility.trace(' Weeks : '||to_char(new_entry.ssp_weeks(Y)));
3905 -- hr_utility.trace(' Withheld : '||to_char(new_entry.withheld_days(Y)));
3906 -- hr_utility.trace(' Rate : '||to_char(new_entry.rate(Y)));
3907 -- end loop;
3908 -- --
3909 -- exception
3910 -- when no_data_found then
3911 -- null;
3912 -- end;
3913 --
3914 hr_utility.set_location(l_proc,2);
3915 get_SSP_correction_element (PIW_start_date);
3916 hr_utility.set_location(l_proc,3);
3917 --
3918 -- Check each existing SSP entry in turn against all the potential new ones.
3919 --
3920 <<OLD_ENTRIES>>
3921 for old_entry in csr_existing_entries
3922 LOOP
3923 hr_utility.trace(l_proc||'OLD ENTRY');
3924 hr_utility.trace(l_proc||'Ele_entryid: '||to_char(old_entry.element_entry_id));
3925 hr_utility.trace(l_proc||'Ele_link_id: '||to_char(old_entry.element_link_id));
3926 hr_utility.trace(l_proc||'Assgment_id: '||to_char(old_entry.assignment_id));
3927 hr_utility.trace(l_proc||'Eff_star_dt: '||to_char(old_entry.effective_start_date));
3928 hr_utility.trace(l_proc||' Eff_end_dt: '||to_char(old_entry.effective_end_date));
3929 hr_utility.trace(l_proc||' Date From: '||to_char(old_entry.date_from));
3930 hr_utility.trace(l_proc||' Date To : '||to_char(old_entry.date_to));
3931 hr_utility.trace(l_proc||' Amount : '||to_char(old_entry.amount));
3932 hr_utility.trace(l_proc||' Days Due : '||to_char(old_entry.ssp_days_due));
3933 hr_utility.trace(l_proc||' Weeks : '||to_char(old_entry.ssp_weeks));
3934 hr_utility.trace(l_proc||' Withheld : '||to_char(old_entry.withheld_days));
3935 hr_utility.trace(l_proc||' Rate : '||to_char(old_entry.rate));
3936 --
3937 -- See if the existing entry exactly matches one we are going to insert.
3938 -- If it does, then we do not need to change it or insert the new one.
3939 -- If there is no exact match, then we must reverse the existing entry.
3940 --
3941 l_invalid_entry := FALSE;
3942 --
3943 <<FIND_MATCH>>
3944 begin
3945 Y := 0;
3946 --
3947 LOOP
3948 Y := Y + 1;
3949 --
3950 exit when (old_entry.date_from = new_entry.date_from (Y) and
3951 old_entry.date_to = new_entry.date_to (Y) and
3952 old_entry.amount = new_entry.amount (Y) and
3953 old_entry.ssp_days_due = new_entry.ssp_days_due (Y) and
3954 old_entry.ssp_weeks = new_entry.ssp_weeks (Y) and
3955 old_entry.withheld_days = new_entry.withheld_days (Y) and
3956 old_entry.rate = new_entry.rate (Y));
3957 end loop;
3958 --
3959 exception
3960 when no_data_found then
3961 --
3962 -- There was no new entry which exactly matched the old entry.
3963 --
3964 l_invalid_entry := TRUE;
3965 --
3966 end;
3967 --
3968 if not l_invalid_entry then
3969 --
3970 -- The existing entry is OK so leave it alone. Simply mark the new one
3971 -- so that we do not attempt to insert it.
3972 --
3973 new_entry.dealt_with (Y) := 'TRUE';
3974 hr_utility.trace(l_proc||' Inside not l_invalid_entry');
3975 else
3976 --
3977 -- The existing entry has been superseded by a new one.
3978 --
3979 hr_utility.set_location(l_proc,5);
3980 if (ssp_smp_support_pkg.entry_already_processed(old_entry.element_entry_id))
3981 then
3982 --
3983 -- The processed entry must be reversed, its values are superseded.
3984 --
3985 -- Insert an entry for SSP correction element in the next payroll
3986 -- period, with the old entry's amounts reversed in sign so that it
3987 -- will cancel out the old entry.
3988 --
3989 --Changed the last_process_date to FINAL_PROCESS_DATE for debug purpose
3990 hr_utility.set_location(l_proc,6);
3991 ssp_smp_support_pkg.get_entry_details (
3992 p_date_earned => old_entry.date_from,
3993 p_person_id => absentee.person_id,
3994 p_last_process_date => absentee.final_process_date, /*7688727 changed from LSP date to FPD */
3995 p_element_type_id => g_SSP_correction.element_type_id,
3996 p_element_link_id => old_entry.element_link_id,
3997 p_assignment_id => old_entry.assignment_id,
3998 p_effective_start_date => old_entry.effective_start_date,
3999 p_effective_end_date => old_entry.effective_end_date);
4000 --
4001 hr_utility.set_location(l_proc,20);
4002 hr_utility.trace(l_proc||' OLD: Before calling hr_entry_api.insert_element_entry');
4003 hr_utility.trace(l_proc||' old_entry.effective_start_date'||old_entry.effective_start_date);
4004 hr_utility.trace(l_proc||' old_entry.effective_end_date'||old_entry.effective_end_date);
4005 --
4006 hr_entry_api.insert_element_entry (
4007 p_effective_start_date => old_entry.effective_start_date,
4008 p_effective_end_date => old_entry.effective_end_date,
4009 p_element_entry_id => l_dummy,
4010 p_target_entry_id => old_entry.element_entry_id,
4011 p_assignment_id => old_entry.assignment_id,
4012 p_element_link_id => old_entry.element_link_id,
4013 p_creator_type => c_SSP_creator_type,
4014 p_creator_id => g_PIW_id,
4015 p_entry_type => 'E',
4016 p_input_value_id1 => g_SSP_correction.rate_id,
4017 p_input_value_id2 => g_SSP_correction.from_id,
4018 p_input_value_id3 => g_SSP_correction.to_id,
4019 p_input_value_id4 => g_SSP_correction.amount_id,
4020 p_input_value_id5 => g_SSP_correction.withheld_days_id,
4021 p_input_value_id6 => g_SSP_correction.SSP_weeks_id,
4022 p_input_value_id7 => g_SSP_correction.SSP_days_due_id,
4023 p_input_value_id8 => g_SSP_correction.qualifying_days_id,
4024 p_entry_value1 => old_entry.rate,
4025 -- The following two lines will implicitly convert the dates
4026 -- to varchar values. This will use the default format (NLS
4027 -- setting) for a date which is what the date_from and
4028 -- data_to UoM is set to in 11i (=Date).
4029 p_entry_value2 => old_entry.date_from,
4030 p_entry_value3 => old_entry.date_to,
4031 p_entry_value4 => old_entry.amount * -1,
4032 p_entry_value5 => old_entry.withheld_days * -1,
4033 p_entry_value6 => old_entry.SSP_weeks * -1,
4034 p_entry_value7 => old_entry.SSP_days_due * -1,
4035 p_entry_value8 => old_entry.qualifying_days * -1);
4036 --
4037 hr_utility.set_location(l_proc,25);
4038 else
4039 --
4040 -- Delete the unprocessed, invalid entry
4041 --
4042 hr_utility.set_location (l_proc,30);
4043 --
4044 hr_entry_api.delete_element_entry (
4045 p_dt_delete_mode => 'ZAP',
4046 p_session_date => old_entry.effective_start_date,
4047 p_element_entry_id => old_entry.element_entry_id);
4048 --
4049 hr_utility.set_location (l_proc,35);
4050 end if;
4051 end if;
4052 end loop;
4053 --
4054 if p_entitled_to_SSP then
4055 --
4056 -- The person is entitled to SSP so we can create new entries for him.
4057 --
4058 <<INSERT_NEW_ENTRIES>>
4059 --
4060 -- Now go through the new entries, inserting any which are not marked as
4061 -- having been dealt with already.
4062 --
4063 hr_utility.set_location (l_proc,4);
4064 --
4065 begin
4066 Y := 0;
4067 --
4068 LOOP
4069 Y := Y + 1;
4070 --
4071 if not new_entry.dealt_with (Y) = 'TRUE' and
4072 new_entry.ssp_weeks (Y) > 0 and
4073 (new_entry.withheld_days (Y) < new_entry.SSP_days_due (Y)
4074 or new_entry.SSP_days_due (Y) = 0)
4075 then
4076 --
4077 -- Insert an entry for each week where there is not already a
4078 -- correct, processed entry and where the stoppage days are less
4079 -- than the due days or the due days are zero (because of waiting
4080 -- days) and where the entry is for part or all of an SSP week.
4081 --
4082 hr_utility.set_location(l_proc,40);
4083 hr_utility.trace(l_proc||' NEW: Before calling hr_entry_api.insert_element_entry');
4084 hr_utility.trace(l_proc||' new_entry.effective_start_date('||Y||') :'||new_entry.effective_start_date(Y));
4085 hr_utility.trace(l_proc||' new_entry.effective_end_date('||Y||') :'||new_entry.effective_end_date(Y));
4086 --
4087 hr_entry_api.insert_element_entry (
4088 p_effective_start_date => new_entry.effective_start_date (Y),
4089 p_effective_end_date => new_entry.effective_end_date (Y),
4090 p_element_entry_id => l_dummy,
4091 p_assignment_id => new_entry.assignment_id (Y),
4092 p_element_link_id => new_entry.element_link_id (Y),
4093 p_creator_type => c_SSP_creator_type,
4094 p_creator_id => g_PIW_id,
4095 p_entry_type => 'E',
4096 p_input_value_id1 => g_SSP_legislation.rate_id,
4097 p_input_value_id2 => g_SSP_legislation.from_id,
4098 p_input_value_id3 => g_SSP_legislation.to_id,
4099 p_input_value_id4 => g_SSP_legislation.amount_id,
4100 p_input_value_id5 => g_SSP_legislation.withheld_days_id,
4101 p_input_value_id6 => g_SSP_legislation.SSP_weeks_id,
4102 p_input_value_id7 => g_SSP_legislation.SSP_days_due_id,
4103 p_input_value_id8 => g_SSP_legislation.qualifying_days_id,
4104 p_entry_value1 => new_entry.rate (Y),
4105 -- Fix for bug 1020757:
4106 -- This explicit conversion is not possible anymore since we don't know
4107 -- what the format is going to be. We need to use fnd_date,date_to_chardate
4108 -- p_entry_value2 => to_char(new_entry.date_from(Y),'DD-MON-YYYY'),
4109 -- p_entry_value3 => to_char(new_entry.date_to(Y),'DD-MON-YYYY'),
4110 p_entry_value2 => fnd_date.date_to_chardate(new_entry.date_from(Y)),
4111 p_entry_value3 => fnd_date.date_to_chardate(new_entry.date_to(Y)),
4112 p_entry_value4 => new_entry.amount (Y),
4113 p_entry_value5 => new_entry.withheld_days (Y),
4114 p_entry_value6 => new_entry.SSP_weeks (Y),
4115 p_entry_value7 => new_entry.SSP_days_due (Y),
4116 p_entry_value8 => new_entry.qualifying_days (Y));
4117 --
4118 hr_utility.set_location(l_proc,45);
4119 end if;
4120 end loop;
4121 --
4122 exception
4123 when no_data_found then
4124 --
4125 -- There are no more new entries to be inserted.
4126 --
4127 null;
4128 --
4129 end;
4130 end if;
4131 end if;
4132 --
4133 hr_utility.set_location('Leaving :'||l_proc,100);
4134 --
4135 end generate_payments;
4136 --------------------------------------------------------------------------------
4137 procedure ins_ssp_temp_affected_rows_PIW(p_absence_id in number,
4138 p_deleting in boolean default false) is
4139 --
4140 -- Inserts a row in ssp_temp_affected_rows for the PIW id, if not already there
4141 --
4142 l_proc varchar2 (72) := g_package||'ins_ssp_temp_affected_rows_PIW';
4143 l_deleting_ch varchar2(1);
4144 --
4145 begin
4146 --
4147 hr_utility.set_location('Entering:'||l_proc,1);
4148 if p_deleting then
4149 l_deleting_ch := 'Y';
4150 else
4151 l_deleting_ch := 'N';
4152 end if;
4153 --
4154 if p_absence_id is not null then
4155 hr_utility.trace('inserting record for absence '||to_char(p_absence_id)||
4156 'deleting is '||l_deleting_ch);
4157 --
4158 insert into ssp_temp_affected_rows (PIW_id, p_deleting, locked)
4159 select p_absence_id, l_deleting_ch, userenv('sessionid')
4160 from sys.dual
4161 where not exists
4162 (select null
4163 from ssp_temp_affected_rows t2
4164 where t2.PIW_id = p_absence_id);
4165 end if;
4166 --
4167 hr_utility.set_location('Leaving :'||l_proc,100);
4168 --
4169 end ins_ssp_temp_affected_rows_PIW;
4170 --------------------------------------------------------------------------------
4171 procedure SSP1L_control (
4172 --
4173 -- If prior employment details are updated then we must recalculate SSP
4174 -- This procedure is called from the after update row-level trigger on
4175 -- PER_PERIODS_OF_SERVICE.
4176 --
4177 p_person_id in number,
4178 p_date_start in date
4179 ) is
4180 --
4181 cursor affected_PIW is
4182 --
4183 -- Get any PIW which may have been linked to the prior employment SSP.
4184 --
4185 select nvl (linked_absence_id, absence_attendance_id) PIW_ID
4186 from per_absence_attendances
4187 where sickness_start_date
4188 <= (p_date_start + g_SSP_legislation.linking_period_days)
4189 and person_id = p_person_id;
4190 --
4191 l_proc varchar2 (72) := g_package||'SSP1L_control';
4192 l_PIW_ID number := null;
4193 --
4194 procedure check_parameters is
4195 --
4196 all_parameters_valid constant boolean := (p_person_id is not null
4197 and p_date_start = trunc (p_date_start)
4198 and p_date_start is not null);
4199 --
4200 begin
4201 --
4202 hr_utility.trace (l_proc||' p_person_id: '||to_char(p_person_id));
4203 hr_utility.trace (l_proc||' p_date_start: ' ||to_char(p_date_start));
4204 --
4205 hr_general.assert_condition (all_parameters_valid);
4206 --
4207 end check_parameters;
4208 --
4209 begin
4210 --
4211 hr_utility.set_location('Entering:'||l_proc,1);
4212 --
4213 check_parameters;
4214 --
4215 get_SSP_element (p_date_start);
4216 --
4217 -- Get details of PIW which may be affected by changes in SSP1L details
4218 --
4219 open affected_PIW;
4220 fetch affected_PIW into l_PIW_ID;
4221 close affected_PIW;
4222 --
4223 if l_PIW_ID is not null then
4224 --
4225 -- Store the PIW ID for later use in the recalculate_SSP_and_SMP procedure
4226 --
4227 ins_ssp_temp_affected_rows_PIW (l_PIW_ID, p_deleting => FALSE);
4228 end if;
4229 --
4230 hr_utility.set_location('Leaving :'||l_proc,100);
4231 --
4232 end SSP1L_control;
4233 --------------------------------------------------------------------------------
4234 --
4235 -- If the person dies or the date of birth is modified then we need to
4236 -- recalculate SSP. This procedure is called from the after row-level update
4237 -- trigger of PER_PEOPLE_F.
4238 procedure person_control(p_person_id in number,
4239 p_date_of_death in date,
4240 p_date_of_birth in date) is
4241 --
4242 -- Get any sickness absences which may be affected by the change in age
4243 -- or the death.
4244 cursor affected_PIWs is
4245 select linked_absence_id,
4246 absence_attendance_id,
4247 sickness_start_date,
4248 sickness_end_date
4249 from per_absence_attendances
4250 where person_id = p_person_id
4251 and sickness_start_date is not null;
4252 --
4253 l_proc varchar2(72) := g_package||'person_control';
4254 l_65th_birthday date := add_months(p_date_of_birth, (65 *12));
4255 l_16th_birthday date := add_months(p_date_of_birth, (16 *12));
4256 l_01_October_06 date := to_date('01/10/2006','DD/MM/YYYY');
4257 l_employee_age number:= months_between(l_01_October_06,p_date_of_birth)/12;
4258 --
4259 procedure check_parameters is
4260 all_parameters_valid constant boolean :=
4261 (p_person_id is not null and
4262 p_date_of_death >= p_date_of_birth);
4263 begin
4264 hr_utility.trace (l_proc||' p_person_id = '||to_char (p_person_id));
4265 hr_utility.trace (l_proc||' p_date_of_death = '||to_char (p_date_of_death));
4266 hr_utility.trace (l_proc||' p_date_of_birth = '||to_char (p_date_of_birth));
4267 --
4268 hr_general.assert_condition (all_parameters_valid);
4269 --
4270 end check_parameters;
4271 --
4272 begin
4273 hr_utility.set_location('Entering:'||l_proc,1);
4274 --
4275 check_parameters;
4276 --
4277 for l_absence in affected_PIWs LOOP
4278 if(-- the PIW starts on or after the person's 65th birthday
4279 l_65th_birthday <= linked_PIW_start_date (p_person_id,
4280 l_absence.sickness_start_date,
4281 l_absence.sickness_end_date,
4282 p_date_of_birth)
4283 -- or the PIW start on or before the person's 16th birthday
4284 or
4285 (l_16th_birthday > linked_PIW_start_date (p_person_id,
4286 l_absence.sickness_start_date,
4287 l_absence.sickness_end_date,
4288 p_date_of_birth) and
4289 l_employee_age < 16)
4290 -- or the PIW ends on or after the person's death
4291 or p_date_of_death <= nvl(linked_PIW_end_date(p_person_id,
4292 l_absence.sickness_start_date,
4293 l_absence.sickness_end_date,
4294 p_date_of_birth),
4295 p_date_of_death))
4296 then
4297 if l_employee_age < 16 then
4298 ins_ssp_temp_affected_rows_PIW (l_absence.absence_attendance_id,
4299 p_deleting => FALSE);
4300 else
4301 ins_ssp_temp_affected_rows_PIW (nvl(l_absence.linked_absence_id,
4302 l_absence.absence_attendance_id),
4303 p_deleting => FALSE);
4304 end if;
4305 end if;
4306 end loop;
4307 --
4308 -- Call the ssp_smp_support_pkg.recalculate_ssp_and_smp directly.
4309 ssp_smp_support_pkg.recalculate_ssp_and_smp(p_deleting => FALSE);
4310 --
4311 hr_utility.set_location('Leaving :'||l_proc,100);
4312 --
4313 end person_control;
4314 --------------------------------------------------------------------------------
4315 -- If absence details are updated then we must recalculate SSP.
4316 procedure absence_control(p_absence_attendance_id in number,
4317 p_linked_absence_id in number,
4318 p_person_id in number,
4319 p_sickness_start_date in date,
4320 p_deleting in boolean default FALSE) is
4321 l_proc varchar2 (72) := g_package||'absence_control';
4322 --
4323 procedure check_parameters is
4324 all_parameters_valid constant boolean :=
4325 (p_person_id is not null and
4326 p_absence_attendance_id is not null and
4327 p_deleting is not null and
4328 p_sickness_start_date = trunc (p_sickness_start_date) and
4329 p_absence_attendance_id <> p_linked_absence_id);
4330 begin
4331 --
4332 hr_utility.trace (l_proc||' p_absence_attendance_id = '||to_char (p_absence_attendance_id));
4333 hr_utility.trace (l_proc||' p_linked_absence_id'||to_char (p_linked_absence_id));
4334 hr_utility.trace ('p_person_id'||to_char (p_person_id));
4335 hr_utility.trace ('p_sickness_start_date' ||to_char (p_sickness_start_date));
4336 if p_deleting then
4337 hr_utility.trace (l_proc||' p_deleting is TRUE');
4338 else
4339 hr_utility.trace (l_proc||' p_deleting is FALSE');
4340 end if;
4341 --
4342 hr_general.assert_condition (all_parameters_valid);
4343 --
4344 end check_parameters;
4345 begin
4346 hr_utility.set_location('Entering:'||l_proc,1);
4347 --
4348 check_parameters;
4349 --
4350 if p_deleting then
4351 g_absence_del := 'Y';
4352 else
4353 g_absence_del := 'N';
4354 end if;
4355 --
4356 -- The parent PIW is being deleted. Remove any associated entries.
4357 if p_deleting and p_linked_absence_id is null then
4358 g_PIW_id := p_absence_attendance_id;
4359 new_entry := empty_record;
4360 PIW_start_date := p_sickness_start_date;
4361 --
4362 hr_utility.trace('Deleting parent absence, id '||to_char(g_PIW_id));
4363 --
4364 open csr_person (p_sickness_start_date, p_person_id);
4365 fetch csr_person into absentee;
4366 close csr_person;
4367 --
4368 generate_payments (p_entitled_to_SSP => FALSE);
4369 --
4370 delete ssp_temp_affected_rows
4371 where PIW_id = g_PIW_id;
4372 else
4373 --
4374 -- The PIW series must have been updated or inserted. Recalculate SSP.
4375 ins_ssp_temp_affected_rows_PIW (nvl(p_linked_absence_id,p_absence_attendance_id)
4376 ,p_deleting);
4377 end if;
4378 --
4379 hr_utility.set_location('Leaving :'||l_proc,100);
4380 --
4381 end absence_control;
4382 --------------------------------------------------------------------------------
4383 procedure earnings_control (
4384 --
4385 -- If average earnings are altered then we must recalculate SSP.
4386 --
4387 p_person_id in number,
4388 p_effective_date in date
4389 ) is
4390 --
4391 cursor affected_PIW is
4392 --
4393 -- Find the PIW whose start date is the same as the date of the
4394 -- average earnings calculation.
4395 --
4396 select *
4397 from per_absence_attendances
4398 where sickness_start_date = p_effective_date
4399 and person_id = p_person_id;
4400 --
4401 l_proc varchar2 (72) := g_package||'earnings_control';
4402 --
4403 procedure check_parameters is
4404 --
4405 all_parameters_valid constant boolean := (p_person_id is not null
4406 and p_effective_date is not null
4407 and p_effective_date = trunc (p_effective_date));
4408 --
4409 begin
4410 --
4411 hr_utility.trace (l_proc||' p_person_id = '||to_char (p_person_id));
4412 hr_utility.trace (l_proc||' p_effective_date = '
4413 ||to_char (p_effective_date));
4414 --
4415 hr_general.assert_condition (all_parameters_valid);
4416 --
4417 end check_parameters;
4418 --
4419 begin
4420 --
4421 hr_utility.set_location('Entering:'||l_proc,1);
4422 --
4423 check_parameters;
4424 --
4425 open affected_PIW;
4426 fetch affected_PIW into g_absence;
4427 close affected_PIW;
4428 --
4429 if g_absence.absence_attendance_id is not null
4430 then
4431 hr_utility.set_location (l_proc,10);
4432 --
4433 ins_ssp_temp_affected_rows_PIW (g_absence.absence_attendance_id,
4434 p_deleting => FALSE);
4435 end if;
4436 --
4437 hr_utility.set_location('Leaving :'||l_proc,100);
4438 --
4439 end earnings_control;
4440 --------------------------------------------------------------------------------
4441 procedure stoppage_control (p_absence_id in number) is
4442 --
4443 -- If the user modifies stoppages then we must recalculate the SSP entitlement.
4444 --
4445 l_proc varchar2 (72) := g_package||'stoppage_control';
4446 --
4447 begin
4448 --
4449 hr_utility.set_location('Entering:'||l_proc,1);
4450 --
4451 if p_absence_id is not null
4452 then
4453 ins_ssp_temp_affected_rows_PIW (p_absence_id, p_deleting => FALSE);
4454 end if;
4455 --
4456 hr_utility.set_location('Leaving :'||l_proc,100);
4457 --
4458 end stoppage_control;
4459
4460 --------------------------------------------------------------------------------
4461 procedure medical_control (p_absence_id in number) is
4462 --
4463 -- If the user modifies medical evidence then recalculate the SSP entitlement.
4464 --
4465 l_proc varchar2 (72) := g_package||'medical_control';
4466 --
4467 begin
4468 --
4469 hr_utility.set_location('Entering:'||l_proc,1);
4470 --
4471 if p_absence_id is not null and g_absence_del = 'N'
4472 then
4473 ins_ssp_temp_affected_rows_PIW (p_absence_id, p_deleting => FALSE);
4474 end if;
4475
4476 g_absence_del := 'N';
4477 --
4478 hr_utility.set_location('Leaving :'||l_proc,100);
4479 --
4480 end medical_control;
4481
4482 --------------------------------------------------------------------------------
4483 procedure SSP_control(p_absence_attendance_id in number) is
4484 l_proc varchar2 (72) := g_package||'SSP_control';
4485 --
4486 l_rows_reason number:=0;
4487 --
4488 cursor csr_previous_reason is
4489 select count(*)
4490 from ssp_withholding_reasons swr,
4491 ssp_stoppages stp
4492 where stp.absence_attendance_id = p_absence_attendance_id
4493 and stp.reason_id = swr.reason_id
4494 and upper(swr.reason) = 'RE-HIRED EMPLOYEE,PLEASE CHECK';
4495 --
4496 procedure check_parameters is
4497 all_parameters_valid constant boolean := (p_absence_attendance_id is not null);
4498 begin
4499 hr_utility.trace (l_proc||' p_absence_attendance_id = '||to_char (p_absence_attendance_id));
4500 --
4501 hr_general.assert_condition (all_parameters_valid);
4502 --
4503 end check_parameters;
4504 begin
4505 hr_utility.set_location('Entering:'||l_proc,1);
4506 hr_utility.trace(l_proc||'---------PARAMS------------');
4507 hr_utility.trace(l_proc||'p_absence_attendance_id:'||p_absence_attendance_id);
4508 hr_utility.trace(l_proc||'---------------------------');
4509 --
4510 check_parameters;
4511 hr_utility.set_location(l_proc,2);
4512 --
4513 get_absence_details (p_absence_attendance_id);
4514 --
4515 open csr_previous_reason;
4516 fetch csr_previous_reason into l_rows_reason;
4517 if l_rows_reason > 0 then
4518 hr_utility.set_location(l_proc,3);
4519 ssp_smp_support_pkg.reason_for_no_earnings:='SSP_36076_EMP_REHIRED';
4520 hr_utility.set_location(l_proc,4);
4521 end if;
4522 close csr_previous_reason;
4523 --
4524 hr_utility.set_location(l_proc,5);
4525 generate_payments (entitled_to_SSP (g_absence));
4526 --
4527 hr_utility.set_location('Leaving :'||l_proc,100);
4528 --
4529 end SSP_control;
4530 --------------------------------------------------------------------------------
4531 end ssp_SSP_pkg;
[Home] [Help]