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