1 package SSP_SSP_PKG AUTHID CURRENT_USER as
2 /* $Header: spsspapi.pkh 120.2.12010000.1 2008/07/25 08:16:09 appldev 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 History:
15 -------
16 Version Date Author Bug No Description
17 ------- ---- ------ ------ -----------
18 11 Sep 95 N Simpson Created
19 19 Sep 95 N Simpson New procedure check_sickness_date_change
20 15 Nov 95 N Simpson Added pragma to linked_PIW_end_date
21 09 Apr 96 N Simpson New procedure update_linked_absence_ids
22 18 Jun 98 A Parkes Added input_currency_code to
23 csr_SSP_element_details
24 30.14 19-AUG-98 A.Myers 701750 Added parameter processing level to
25 qualifying_days_in_peiod, which controls
26 the amount of work done in the calendars
27 package.
28 115.4 12-Oct-99 C Carter 1027169 Removed pragma restriction from
29 absence_is_a_piw,linked_piw_start_date
30 and linked_piw_end_date
31 115.9 22-Mar-04 A Blinko 3466672 Added procedure medical_control
32 115.10 24-Aug-06 K Thampan 5482199 Added procedure check_for_other_stoppage
33 and check_employee_too_old
34 115.11 06-Sep-06 K Thampan 5482199 Remove procedure check_for_other_stoppage
35 and check_employee_too_old
36 */
37 --------------------------------------------------------------------------------
38 -- ***************************************************************************
39 -- * Performs actions required by the UK legislation for Statutory
40 -- * Sick Pay. See the High Level Design document for general details
41 -- * of the functionality and use of SSP. For specific rules about SSP, see
42 -- * the Statutory Sick Pay Manual for Employers, CA30 (NI270) issued by the
43 -- * Department of Social Security. The latest version as at the time of
44 -- * writing was dated 6 April 1995.
45 -- ***************************************************************************
46 --
47 c_rate_name constant varchar2 (80) := 'Rate';
48 c_from_name constant varchar2 (80) := 'From';
49 c_to_name constant varchar2 (80) := 'To';
50 c_amount_name constant varchar2 (80) := 'Amount';
51 c_withheld_days_name constant varchar2 (80) := 'Withheld days';
52 c_SSP_weeks_name constant varchar2 (80) := 'SSP weeks';
53 c_SSP_days_due_name constant varchar2 (80) := 'SSP days due';
54 c_qualifying_days_name constant varchar2 (80) := 'Qualifying days';
55 c_SSP_element_name constant varchar2 (80) := 'Statutory Sick Pay';
56 c_SSP_correction_element_name constant varchar2 (80) := 'SSP Corrections';
57 c_SSP_creator_type constant varchar2 (30) := 'S';
58 --
59 cursor csr_SSP_element_details (
60 --
61 -- Gets the SSP legislative parameters held in the element DDF.
62 --
63 p_effective_date in date,
64 p_element_name in varchar2 default c_SSP_element_name
65 ) is
66 --
67 select element_type_id,
68 effective_start_date,
69 effective_end_date,
70 to_number (element_information1) MAXIMUM_SSP_PERIOD,
71 to_number (element_information2) MAXIMUM_AGE,
72 to_number (element_information3)+1 LINKING_PERIOD_DAYS,
73 to_number (element_information4) WAITING_DAYS,
74 to_number (element_information5) PIW_THRESHHOLD,
75 to_number (element_information6) MAXIMUM_LINKED_PIW_YEARS,
76 to_number (element_information7) PERCENTAGE_THRESHHOLD,
77 ssp_smp_support_pkg.element_input_value_id
78 (element_type_id, c_SSP_days_due_name) SSP_DAYS_DUE_ID,
79 ssp_smp_support_pkg.element_input_value_id
80 (element_type_id, c_amount_name) AMOUNT_ID,
81 ssp_smp_support_pkg.element_input_value_id
82 (element_type_id, c_from_name) FROM_ID,
83 ssp_smp_support_pkg.element_input_value_id
84 (element_type_id, c_to_name) TO_ID,
85 ssp_smp_support_pkg.element_input_value_id
86 (element_type_id, c_rate_name) RATE_ID,
87 ssp_smp_support_pkg.element_input_value_id
88 (element_type_id,c_withheld_days_name) WITHHELD_DAYS_ID,
89 ssp_smp_support_pkg.element_input_value_id
90 (element_type_id, c_SSP_weeks_name) SSP_WEEKS_ID,
91 ssp_smp_support_pkg.element_input_value_id
92 (element_type_id, c_qualifying_days_name) QUALIFYING_DAYS_ID,
93 input_currency_code
94 from pay_element_types_f
95 where element_name = p_element_name
96 and p_effective_date between effective_start_date
97 and effective_end_date;
98 --
99 --------------------------------------------------------------------------------
100 procedure update_linked_absence_ids;
101 --
102 -- Called from the recalculate_ssp_and_smp procedure in the
103 -- ssp_smp_support_pkg. This procedure handles the case where an
104 -- absence has been updated such that it is now a PIW, having not
105 -- previously been one. It will update the linked_absence_id of the
106 -- modified absence if it links to a previous PIW, and amend the PIW
107 -- ID held in the temporary store which determines which PIWs have
108 -- their SSP recalculated.
109 --------------------------------------------------------------------------------
110 function linked_absence_ID
111 (
112 p_person_id in number,
113 p_sickness_start_date in date,
114 p_sickness_end_date in date
115 ) return number;
116 --
117 -- Called from absence before row insert trigger. Returns the ID
118 -- of the absence which is the start of a series of linked periods
119 -- of incapacity for work. The linking period is held on the SSP
120 -- element DDF. If any sickness absence starts within the linking
121 -- period from the end of a previous sickness, then those absences
122 -- are said to be linked. We hold the ID of the first of the series
123 -- because that is the one which is used to derive many of the
124 -- details for checking entitlement to SSP.
125 --
126 -- Steps:
127 --
128 -- 1. Get the linking period from the element DDF.
129 -- 2. Calculate the date which is the sickness start date minus the
130 -- linking period.
131 -- 3. Find the details of any sickness absence for this
132 -- person whose sickness end date falls between the new sickness
133 -- start date and the date calculated at step 2.
134 -- 4. If the linked_absence_id found at step 3 is not null, that is the
135 -- linked_absence_id to be returned. If it is null, then return the
136 -- absence id of the row found at step 3. If no row was found,
137 -- return null.
138 --
139 -- Known Restrictions:
140 --
141 -- If the user inserts a sickness absence which is PRIOR to existing
142 -- sickness absences and this absence would become the first in the
143 -- series of linked PIWs, then we cannot update all of the existing
144 -- absences as required because of the 'mutating table restriction'
145 -- on database triggers. This case must be handled by the
146 -- application interface.
147 --
148 procedure check_sickness_date_change
149 (
150 p_person_id in number,-- :old.person_id
151 p_linked_absence_id in number,-- :old.linked_absence_id
152 p_absence_attendance_id in number,-- :old.absence_attendance_id
153 p_new_sickness_start_date in date,-- :new.sickness_start_date
154 p_new_sickness_end_date in date, -- :new.sickness_end_date
155 p_old_sickness_end_date in date -- :old.sickness_end_date
156 );
157 --
158 -- Called from AFTER update of sickness_start_date, sickness_end_date
159 -- trigger. Prevents update if the change would invalidate the
160 -- linked_absence_id of either this row or the next one.
161 --
162 procedure Check_for_break_in_linked_PIW
163 (
164 p_sickness_start_date in date,
165 p_sickness_end_date in date,
166 p_linked_absence_id in number,
167 p_absence_attendance_id in number);
168 --
169 -- Called from absence before delete trigger. Prevent the removal of a
170 -- sickness absence from the start or middle of a linked series if by
171 -- so doing the user would break the link. If the link would remain
172 -- intact even after removal of the deleted absence, then allow the
173 -- deletion to go ahead.
174 --
175 -- The ideal situation would be that if an absence is removed from the
176 -- start or middle of a linked PIW, we would recalculate the linked
177 -- PIW ID of all subsequent sickness absences and update them. However,
178 -- the restriction on 'mutating tables' for database triggers prevents
179 -- us from implementing that logic. Therefore, we must prevent the user
180 -- from destroying the series by removing one of its components.
181 --
182 -- function entitled_to_SSP
183 --
184 -- This is not a public procedure but is documented here because this
185 -- header file acts as the Low Level Design document for the package.
186 --
187 -- Check whether or not an absence gives rise to an entitlement to
188 -- Statutory Sick Pay. First check whether there is a prima facia
189 -- entitlement (eg there must be a PIW formed), and then check for any
190 -- reasons for withholding payment from an entitled person. If there is
191 -- any such withholding reason, then insert a row in ssp_stoppages for
192 -- the period to which the reason applies.
193 --
194 -- While checking entitlement, bear in mind that stoppages may also be
195 -- created by users, and that the check may be being performed for
196 -- a second time. Therefore, if there is a user-generated stoppage
197 -- for a specific period, do not alter any value on it; should there be
198 -- a need to create a stoppage for the same reason as the user's
199 -- stoppage, which covers a wider period, then create stoppages around
200 -- the user's one. Any system-generated stoppages in existence at the
201 -- start of the check should first be deleted before commencing the
202 -- checks.
203 --
204 -- Another feature of stoppages is that the user may override them.
205 -- This means that the withholding reason is to be ignored for that
206 -- period and so the user can effectively prevent any notice being taken
207 -- of that reason. If the stoppage is only for a temporary period, then
208 -- the system may still generate a stoppage for that reason outside the
209 -- overridden period. However, if the stoppage is overridden, it must
210 -- not be deleted by the system.
211 --
212 -- Code each of the following checks as individual, private functions
213 -- or procedures. That will make the code easier to debug, read and
214 -- amend if required.
215 --
216 -- SICK
217 -- Check that the absence is for a sickness absence type category
218 --
219 -- The presence of a value for sickness_start_date may be taken to
220 -- indicate that the absence is sick leave because it is mandatory for
221 -- sickness-type absences.
222 --
223 -- DEATH
224 -- Check that the person is still alive.
225 --
226 -- There is a date of death on the person table. Create a stoppage from
227 -- the start of the following day.
228 --
229 -- AGE
230 -- Check that the person is not too old.
231 --
232 -- There is a date of birth on the person table. Create a stoppage if
233 -- the person was too old for SSP AS AT THE START OF THE LINKED PIW.
234 -- The linked_PIW_start_date is a function defined below. The age
235 -- limit is defined in years and is held on the SSP element as a DDF
236 -- segment. Create a stoppage from the PIW start date if the person
237 -- was too old.
238 --
239 -- PREGNANT
240 -- Check that the person is not within a maternity pay period.
241 --
242 -- The SMP package will have a function to perform this check. Pass the
243 -- linked_absence_start_date, the absence end date, the sickness start
244 -- date of any absence in the series which is pregnancy related.
245 --
246 -- EARNINGS
247 -- Check that the person's average earnings are high enough.
248 --
249 -- Compare the earnings figure against the NI Lower Earnings Limit.
250 --
251 -- ASSIGNMENT
252 -- Check that the person's primary assignment is not excluded from SSP.
253 --
254 -- There is a flag on the assignment DDF.
255 --
256 -- TERMINATION
257 -- Check that the person has a current period of service.
258 --
259 -- Create a stoppage from the end of the period of service which covers
260 -- the absence.
261 --
262 -- EVIDENCE
263 -- If the user requires it, check that medical evidence of
264 -- sickness has been received.
265 --
266 -- There is a DDF segment on the organization table, for the business
267 -- group, which flags the requirement for medical evidence. If it is
268 -- set to 'Y', check the medical evidence table for a child of the
269 -- absence with a status of 'CURRENT'.
270 --
271 -- PIW
272 -- Check that a PIW has been formed
273 --
274 -- The PIW threshhold is a DDF segment on the SSP element. It
275 -- identifies the number of calendar days which form a Period of
276 -- Incapacity for Work. If the difference between the sickness start
277 -- date and the absence end date is less than this, then no PIW is
278 -- formed and no SSP is due. Do not create a stoppage for this
279 -- circumstance, but prevent the creation of payment entries. (NB the
280 -- removal of any existing entries must continue if this check is
281 -- being done as a result of the absence end date being brought
282 -- forward).
283 --
284 -- WAITING
285 -- Check that the waiting days have been served.
286 --
287 -- There is a DDF segment on the SSP element which identifies the
288 -- number of qualifying days which must be served as 'waiting days'
289 -- before SSP is due. If the number of qualifying days between the
290 -- start of the linked series of PIWs and the absence end date is less
291 -- than this figure, then create a stoppage for the absence. If there
292 -- is a stoppage for late notification of sickness (this would be
293 -- created by the user), then the period covered by that stoppage
294 -- will not count towards the waiting days.
295 --
296 -- QUALIFYING
297 -- Check that there are qualifying days within the PIW.
298 --
299 -- Using the function defined below to determine the number of
300 -- qualifying days within a PIW, check that there are qualifying days
301 -- in the period of absence. If there is none, create a stoppage for
302 -- the absence period. If the absence is open ended, then check up to
303 -- the end date of the maximum PIW length.
304 --
305 -- WORK
306 -- Check that no work was done on the qualifying day.
307 --
308 -- Using the absence start and end times, determine if the person was
309 -- present at all on a qualifying day. It is up to the user to enter
310 -- the appropriate start and end times on both their SSP qualifying
311 -- pattern and the absence. If there is a period of the qualifying day
312 -- which is not covered by the absence, then create a stoppage for
313 -- that whole day.
314 --
315 -- WEEKS
316 -- Check that the number of weeks SSP does not exceed the maximum.
317 --
318 -- The maximum number of weeks which may be paid is held as a DDF
319 -- segment on the SSP element. Check that the entries already created
320 -- for the absence do not exceed that figure. Take into account the
321 -- number of weeks paid under a previous employer if the absence links
322 -- to the end of the PIW paid under a previous employer. The previous
323 -- employment values are held on a DDF on the assignment.
324 -- Also take into account any stoppages for the absence, excepting
325 -- stoppages for late notification.
326 --
327 -- YEARS
328 -- Check that the PIW does not exceed the maximum period.
329 --
330 -- Create a stoppage from the start of the third year after the linked
331 -- PIW start date, if the absence covers that date and there has not
332 -- been a stoppage already for maximum payment.
333 --
334 -- procedure generate_payments;
335 --
336 -- When a sickness absence is created, after checking entitlement,
337 -- generate an element entry for the SSP element for each week of the
338 -- Period of Incapacity for Work (PIW). If the absence has an end date
339 -- entered for it, then this is a straightforward matter, but for
340 -- open-ended absences, the entries should be created up for weeks up
341 -- to the lesser of the maximum allowed number of weeks and the
342 -- actual termination date of the period of service. NB there may be
343 -- a linked series of sickness absences, so the period for which
344 -- payments may be generated may be broken into several chunks.
345 --
346 -- If there are stoppages for the absence, created as a result of the
347 -- entitlement check, or by the user, then these will block the
348 -- creation of entries for any weeks which are completely overlapped by
349 -- a stoppage. Weeks which are partially overlapped will still have
350 -- entries created for them, but there will be an effect on the
351 -- entry values (see below). There are two kinds of stoppages; those
352 -- which apply temporarily, and those which apply forever once started.
353 -- Stoppages only apply within a PIW. If there is a stoppage which
354 -- applies forever (ie it has no end date), then there is no need to
355 -- continue creating entries after the start of that stoppage.
356 -- Temporary entries should only affect creation while they apply.
357 -- A further feature of stoppages is that they may be overridden by the
358 -- user; if the override flag is set, then take no account of that
359 -- stoppage when creating entries.
360 --
361 -- The maximum allowed number of weeks is held as a DDF segment on the
362 -- SSP element. If the absence is open-ended, then the check on
363 -- entitlement will create a stoppage starting with the date from
364 -- which no further entries may be created, so there is no need to
365 -- check explicitly for the maximum number of weeks; just keep going
366 -- until the stoppage.
367 --
368 -- Whilst each entry is created to cover a particular period of absence,
369 -- the payroll period in which the entry resides is determined
370 -- separately. The default is that the entry will be created in the
371 -- payroll period which covers the end of the week of absence for which
372 -- the entry is created. If, however, that payroll period is in the
373 -- past, or has already been processed, or is closed, or is after the
374 -- person's last standard process date, then the entry must be placed
375 -- in the next open period for which no main payroll run has been
376 -- performed and which falls around or before the last standard process
377 -- date. If the entry cannot be created in any such period, for whatever
378 -- reason, then an error should be raised and the user required to
379 -- resolve the problem before any entry for the absence can be created.
380 --
381 -- If any detail of the absence is changed, then the entries must be
382 -- recalculated to ensure that the change is reflected in the
383 -- payments. Therefore, we may be performing the entry creation when
384 -- entries already exist for the absence. For each entry which we are
385 -- about to create, we must check that there is not an existing entry
386 -- which covers the same absence period. If there is not, then we
387 -- create the entry as planned; if there is, then we must update the
388 -- existing one rather than create a new one, if a change is required.
389 -- However, if that entry has already been processed in a payroll run,
390 -- then rather than updating it, we must ensure that the
391 -- over/underpayment is corrected at the next opportunity. This is
392 -- done by creating two entries; one which pays the correct amount
393 -- and another, for the SSP Correction element, which reverses the
394 -- incorrect payment by replicating it with a negative sign in front of
395 -- the amount entry value. Before creating the negative entry, it is
396 -- essential to check that there is not already a negative entry for
397 -- the incorrect entry; we do not want to overcorrect.
398 --
399 -- The rate of SSP to be applied to each entry is that current for
400 -- each day that a payment is being made. However, we know that the
401 -- UK rate only changes once a year on 6th April. Therefore, it is only
402 -- necessary to get the rate once unless the absence period crosses
403 -- the 6th April. If this occurs, and there is a change of rate, then
404 -- instead of one entry for that week, create two; one for each rate.
405 -- The rate of SSP is held as the default for the rate input value
406 -- on the SSP element.
407 --
408 -- The 'from' entry value will be the greater of the start date of the
409 -- week for which the entry applies, and the absence start date. If the
410 -- week includes the 6th April, when there is a change of SSP rate,
411 -- then there will be one entry with a 'from' value of the start of
412 -- that week or the start of the absence whichever is greater, and
413 -- another entry with a from date of the 6th April.
414 --
415 -- The 'to' entry value will be the least of the end of the week for
416 -- which the entry applies, and the PIW end date. If the week includes
417 -- the 6th April, when there is a rate change, then there should be
418 -- an entry created for the week up to the 5th April, and another for
419 -- the remainder of the week.
420 --
421 -- The number of qualifying days is calculated by the calendars package
422 -- functionality. Pass the start and end of the week and the ID of the
423 -- person, requesting the SSP qualifying pattern availability. The
424 -- return should be the number of qualifying days within the period.
425 --
426 -- The number of stopped qualifying days is derived by finding the
427 -- period of overlap between the entry and any stoppage. Pass that
428 -- period to the calendars package as for the number of qualifying days
429 -- (see above). The return will be the number of qualifying days
430 -- covered by the entry which are also covered by a stoppage.
431 --
432 -- The amount entry value is derived by subtracting the number of
433 -- stopped qualifying days from the number of qualifying days (both
434 -- figures explained above). The resulting number of days is multiplied
435 -- by the SSP rate.
436 --
437 -- Each entry created by this procedure is to have a creator type
438 -- which identifies it as an SSP entry, and a creator_id which is
439 -- the absence_id of the first absence to which it relates, ie the
440 -- linked_absence_id if it is populated or else the absence_id itself.
441 --
442 --
443 function Linked_PIW_start_date
444 (
445 p_person_id in number,
446 p_sickness_start_date in date,
447 p_sickness_end_date in date
448 --
449 ) return date;
450 --pragma restrict_references (Linked_PIW_start_date, WNDS);
451 --
452 -- Returns the start date of a linked series of Periods of Incapacity
453 -- for Work (PIWs). Each sickness absence may be 'linked' by virtue
454 -- of being separated by less than a certain period. The start of
455 -- the linked PIW is the sickness start date of the first absence
456 -- in a series.
457 --
458 function Linked_PIW_end_date
459 (
460 p_person_id in number,
461 p_sickness_start_date in date,
462 p_sickness_end_date in date
463 ) return date;
464 --pragma restrict_references (Linked_PIW_end_date, WNDS);
465 --
466 -- Returns the end date of a linked series of Periods of Incapacity
467 -- for Work (PIWs).
468 --
469 function qualifying_days_in_period
470 (
471 p_period_from in date,
472 p_period_to in date,
473 p_person_id in number,
474 p_business_group_id in number,
475 p_processing_level in number default 0
476 ) return integer;
477 ----------
478 -- Returns the number of SSP qualifying days in a specific period
479 --
480 function SSP_is_installed return boolean;
481 --
482 -- Returns TRUE if the user has installed the SSP product.
483 --
484 procedure SSP1L_control (
485 --
486 -- If prior employment details are updated then we must recalculate SSP
487 --
488 p_person_id in number,
489 p_date_start in date
490 );
491 --
492 procedure person_control (
493 --
494 -- If the person dies or the date of birth is modified then we need to
495 -- recalculate SSP.
496 --
497 p_person_id in number,
498 p_date_of_death in date,
499 p_date_of_birth in date);
500 --
501 procedure absence_control (
502 --
503 p_absence_attendance_id in number,
504 p_linked_absence_id in number,
505 p_person_id in number,
506 p_sickness_start_date in date,
507 p_deleting in boolean default FALSE);
508 --
509 procedure earnings_control (
510 --
511 -- If average earnings are altered then we must recalculate SSP.
512 --
513 p_person_id in number,
514 p_effective_date in date);
515 --
516 procedure stoppage_control (
517 --
518 -- If stoppage altered we must recalculate SSP.
519 --
520 p_absence_id in number);
521 --
522 procedure medical_control (
523 --
524 -- If medical evidence altered we must recalculate SSP.
525 --
526 p_absence_id in number);
527 --
528 procedure SSP_control (p_absence_attendance_id in number);
529 --
530 function absence_is_a_PIW (
531 --
532 -- Returns TRUE if the sickness dates constitute a PIW for the person,
533 -- either in their own right or as part of a contiguous series of absences.
534 --
535 p_person_id number,
536 p_sickness_start_date date,
537 p_sickness_end_date date) return boolean;
538 --pragma restrict_references (absence_is_a_PIW, WNDS);
539 --
540 procedure get_ssp_element (p_effective_date in date);
541 pragma restrict_references (get_ssp_element, WNDS);
542 --
543 end ssp_SSP_pkg;
[Home] [Help]