1 package SSP_SMP_PKG as
2 /*$Header: spsmpapi.pkh 120.1.12000000.1 2007/01/17 14:14:52 appldev noship $
3 +==============================================================================+
4 | Copyright (c) 1994 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +==============================================================================+
8 --
9 Name
10 Statutory Maternity Pay Business Process
11 --
12 Purpose
13 To perform calculation of entitlement and payment for SMP purposes
14 --
15 History
16 31 Aug 95 N Simpson Created
17 1 Sep 95 N Simpson Added restriction WNPS to pragmas.
18 4 Sep 95 N Simpson Removed legislation restriction from
19 CSR_SMP_ELEMENT_DETAILS.
20 8 Sep 95 N Simpson Converted public procedure
21 check_entitlement_to_SMP into private
22 function entitled_to_SMP. NB The header
23 remains here because this file acts as
24 the LLD for the package.
25 19 Sep 95 N Simpson Modified medical_control parameters.
26 8 Dec 95 N Simpson Modified c_recoverable_amount_name
27 22 Aug 96 C Barbieri Deleted function maternity_leave_exists.
28 With Oracle 7.3.2 it is not possible to
29 reference a function that returns a
30 BOOLEAN inside a SELECT statement.
31 08-Jan-98 RThirlby 608724 30.16 Parameter p_deleting added - part of
32 solution to SMP element entries prob.
33 24-JUL-98 A.Myers 701750 30.17 Parameter p_deleting added to procedure
34 absence_control; part of solution to
35 performance fix 563202 and bug 701750.
36 06-NOV-02 G.Butler 2649135 115.6 Fix to CBO related issue on csr_SMP_
37 element_details cursor - removed
38 to_number calls so that SQL execution
39 path changed and ORA-1722 does not occur.
40 Also commented out pragma restrict_
41 references statements on functions
42 12-JUL-04 A.Blinko 3682122 115.7 Added g_smp_update for lump sun recalc
43 21-MAR-06 K.Thampan 5105039 115.8 Added get_max_SMP_date
44 */
45 --------------------------------------------------------------------------------
46 -- ***************************************************************************
47 -- * Performs actions required by the UK legislation for Statutory
48 -- * Maternity Pay. See the High Level Design document for general details
49 -- * of the functionality and use of SMP.
50 -- ***************************************************************************
51 --
52 c_SMP_element_name constant
53 varchar2 (80) := 'Statutory Maternity Pay';
54 c_SMP_Corr_element_name constant
55 varchar2 (80) := 'SMP Corrections';
56 c_SMP_creator_type constant varchar2 (3) := 'M';
57 c_SMP_entry_type constant varchar2 (1) := 'E';
58 c_week_commencing_name constant varchar2 (30) := 'Week commencing';
59 c_amount_name constant varchar2 (30) := 'Amount';
60 c_recoverable_amount_name constant varchar2 (30) := 'Recoverable amount';
61 c_rate_name constant varchar2 (30) := 'Rate';
62 --
63 g_smp_update varchar2 (1) := 'N';
64 --
65 -- Get the element details for SMP
66 --
67 cursor CSR_SMP_ELEMENT_DETAILS (
68 --
69 -- Get the legislative parameters for SMP, which are held in the
70 -- Developer descriptive Flexfield of the element.
71 --
72 p_effective_date date default sysdate,
73 --
74 -- p_effective_date restricts us to a single row for the selected
75 -- element
76 --
77 p_element_name varchar2 default c_SMP_element_name
78 --
79 -- p_element_name allows us to select different elements in the same
80 -- cursor
81 --
82 ) is
83 --
84 select ele1.element_type_id,
85 ele1.effective_start_date,
86 ele1.effective_end_date,
87 ele1.element_information1 *7
88 EARLIEST_START_OF_MPP,
89 ele1.element_information2 *7
90 QUALIFYING_WEEK,
91 ele1.element_information3 *7
92 CONTINUOUS_EMPLOYMENT_PERIOD,
93 ele1.element_information4
94 MAXIMUM_MPP,
95 ele1.element_information5 *7
96 COMPULSORY_LEAVE_PERIOD,
97 ele1.element_information6
98 MPP_NOTICE_REQUIREMENT,
99 ele1.element_information7
100 NOTICE_OF_BIRTH_REQUIREMENT,
101 ele1.element_information8 *7
102 EARLIEST_SMP_EVIDENCE,
103 ele1.element_information9 /100
104 HIGHER_SMP_RATE,
105 ele1.element_information10
106 LOWER_SMP_RATE,
107 ele1.element_information11 /100
108 RECOVERY_RATE,
109 ele1.element_information12 *7
110 LATEST_SMP_EVIDENCE,
111 ele1.element_information13 *7
112 EXTENDED_SMP_EVIDENCE,
113 ele1.element_information14
114 PERIOD_AT_HIGHER_RATE,
115 ele1.element_information15 *7
116 STILLBIRTH_THRESHHOLD_WEEK,
117 ele1.element_information16
118 STANDARD_SMP_RATE,
119 ssp_smp_support_pkg.element_input_value_id
120 (ele1.element_type_id, c_rate_name) RATE_ID,
121 ssp_smp_support_pkg.element_input_value_id
122 (ele1.element_type_id, c_week_commencing_name)
123 WEEK_COMMENCING_ID,
124 ssp_smp_support_pkg.element_input_value_id
125 (ele1.element_type_id, c_amount_name) AMOUNT_ID,
126 ssp_smp_support_pkg.element_input_value_id
127 (ele1.element_type_id, c_recoverable_amount_name)
128 RECOVERABLE_AMOUNT_ID
129 from pay_element_types_f ele1
130 where ele1.element_name = p_element_name
131 and p_effective_date between ele1.effective_start_date
132 and ele1.effective_end_date;
133 --
134 --------------------------------------------------------------------------------
135 --function entitled_to_SMP (
136 --
137 --p_maternity_id in number
138 --
139 --) return boolean;
140 --
141 -- Check whether or not an absence gives rise to an entitlement to
142 -- Statutory Maternity Pay. First check whether there is a prima facia
143 -- entitlement (eg the person must be female), and then check for any
144 -- reasons for withholding payment from an entitled person. If there is
145 -- any such withholding reason, then insert a row in ssp_stoppages for
146 -- the period to which the reason applies.
147 --
148 -- While checking entitlement, bear in mind that stoppages may also be
149 -- created by users, and that the check may be being performed for
150 -- a second time. Therefore, if there is a user-generated stoppage
151 -- for a specific period, do not alter any value on it; should there be
152 -- a need to create a stoppage for the same reason as the user's
153 -- stoppage, which covers a wider period, then create stoppages around
154 -- the user's one. Any system-generated stoppages in existence at the
155 -- start of the check should first be deleted before commencing the
156 -- checks.
157 --
158 -- Another feature of stoppages is that the user may override them.
159 -- This means that the withholding reason is to be ignored for that
160 -- period and so the user can effectively prevent any notice being taken
161 -- of that reason. If the stoppage is only for a temporary period, then
162 -- the system may still generate a stoppage for that reason outside the
163 -- overridden period.
164 --
165 -- The following checks should be performed:
166 --
167 -- ABSENT
168 -- Check that the woman is absent on maternity leave
169 --
170 -- There must be a maternity leave record. If not
171 -- then exit silently (because this may be called from the trigger
172 -- on the absence table).
173 -- Check that notification of absence is given in time
174 --
175 -- CONTINUITY
176 -- Check that the woman has a period of service of the right length
177 -- covering the right period.
178 --
179 -- There must be an unbroken period of service for the woman which
180 -- lasts for the period of time defined, in weeks, in the SMP
181 -- continuous employment DDF segment, and the Qualifying Week. The
182 -- woman must have been employed for at least part of the Qualifying
183 -- Week. If this condition is not met then create a stoppage for the
184 -- whole maternity.
185 --
186 -- PREGNANT
187 -- Check that the woman is still pregnant or has given birth in the
188 -- correct timescales.
189 --
190 -- If there is a stillbirth occurs before the stillbirth threshhold
191 -- week, then create a stoppage for the whole maternity.
192 -- Stillbirth is determined by there being a date of birth and the
193 -- live birth flag being 'N'. The stillbirth threshhold week is
194 -- determined by converting to days the stillbirth threshhold period
195 -- defined on the SMP element DDF, and subtracting that number from the
196 -- EWC start date.
197 --
198 -- EMPLOYER
199 -- Check that the woman has not started work for a new employer after
200 -- the birth of the child.
201 --
202 -- The start date with new employer is on the maternity table. If it is
203 -- after the birth of the child then create a
204 -- stoppage from the week the woman started work for the new employer.
205 -- It is up to the user to override this if the new employer is one
206 -- who employed the woman in the qualifying week.
207 --
208 -- DEATH
209 -- Check that the woman has not died.
210 --
211 -- Create a stoppage from the Sunday following the date of death.
212 --
213 -- SMA
214 -- Check that the woman is not receiving Statutory Maternity Allowance
215 -- from the Department of Social Security.
216 --
217 -- The start date of Maternity Allowance is held on the maternity
218 -- table. If it is entered, then create a stoppage from that week.
219 --
220 -- EARNINGS
221 -- Check that the average earnings of the woman are high enough to
222 -- qualify for SMP.
223 --
224 -- The average earnings calculation is shared with the SSP package and
225 -- is defined in the header file for the SSP_SSP_pkg. The period to
226 -- which average earnings are to be calculated is the QW start date.
227 -- The average earnings period is defined on the SMP element DDF.
228 --
229 -- EVIDENCE
230 -- Check that medical evidence of maternity has been received in time.
231 --
232 -- The earliest SMP evidence, the latest SMP evidence and the
233 -- extended SMP evidence periods are defined in the SMP element DDF.
234 -- Create a stoppage for the whole maternity if the evidence date is
235 -- earlier than the earliest SMP evidence date. Calculate the earliest
236 -- SMP evidence date by converting the earliest SMP evidence period to
237 -- days from weeks, then subtracting that number of days from the
238 -- EWC start date. Create a stoppage for the whole maternity if the
239 -- evidence received date is later than the latest SMP evidence date.
240 -- Calculate the latest SMP evidence date by converting the latest
241 -- SMP evidence period to days from weeks and adding that number
242 -- to the MPP start date. If the latest date is exceeded, then if the
243 -- accept_late_notification flag is 'Y' then check the extended SMP
244 -- evidence date (in the same manner). If this date is exceeded, or if
245 -- the accept_late_notification flag is 'N', then create a stoppage
246 -- for the whole maternity.
247 --
248 -- CONFIRMATION
249 -- Check that confirmation of birth was received in time.
250 --
251 -- The SMP element DDF defines the number of days after the birth
252 -- which is the end of the period in which the employee may notify the
253 -- employer of the birth. If this date is exceeded, then create a
254 -- stoppage for the whole maternity.
255 --
256 function MATERNITY_RECORD_EXISTS
257 (
258 p_person_id in number
259 ) return boolean;
260 --
261 -- Returns TRUE if there is a maternity record for the person
262 --
263 -- pragma restrict_references (maternity_record_exists, WNPS,WNDS);
264 --
265 function Qualifying_Week
266 (
267 p_due_date in date
268 ) return date;
269 --
270 -- Returns the start date of the Qualifying Week for SMP, based
271 -- on the woman's due date.
272 --
273 -- pragma restrict_references (qualifying_week, WNPS,WNDS);
274 --
275 function Expected_week_of_confinement
276 (
277 p_due_date in date
278 ) return date;
279 --
280 -- Returns the start date of the Expected Week of Confinement
281 -- for SMP, based on the woman's due date.
282 --
283 -- pragma restrict_references (Expected_week_of_confinement, WNPS,WNDS);
284 --
285 function Earliest_MPP_start_date
286 (
287 p_due_date in date
288 ) return date;
289 --
290 -- Returns the earliest date a woman may start her Maternity Pay
291 -- Period, based on the due date and assuming a normal pregnancy.
292 --
293 -- pragma restrict_references (Earliest_MPP_start_date, WNPS,WNDS);
294 --
295 function Continuous_employment_date
296 (
297 p_due_date in date
298 ) return date;
299 --
300 -- Returns the start date of the period ending with the Qualifying
301 -- Week, for which the woman must have been continuously employed
302 -- in order to qualify for SMP
303 --
304 -- pragma restrict_references (Continuous_employment_date, WNPS,WNDS);
305 --
306 procedure absence_control
307 (
308 p_maternity_id in number,
309 p_deleting in boolean default FALSE
310 );
311 --
312 -- Performs the necessary actions to trigger recalculation of SMP if
313 -- a maternity leave absence is inserted, updated or deleted. This is
314 -- the primary mechanism for controlling SMP and should be called from
315 -- row level database triggers on per_absence_attendances, for each DML
316 -- action.
317 --
318 --
319 procedure maternity_control (p_maternity_id in number);
320 --
321 -- Performs the necessary actions to trigger recalculation of SMP if
322 -- a maternity record is updated and SMP has already been calculated
323 -- for it. Must be called from the after-update trigger on
324 -- ssp_maternities for each row.
325 --
326 --
327 procedure medical_control (p_maternity_id in number);
328 --
329 -- Performs the necessary actions to trigger recalculation of SMP if a
330 -- medical report for the maternity is updated. As the initial
331 -- calculation of SMP is triggered by insertion of maternity leave, we
332 -- only need to recalculate if maternity leave already exists. Also,
333 -- as medical evidence can be superceded, we only need to recalculate
334 -- if the updated medical record is current, or is being updated to
335 -- be current. Call from row-level database triggers for each DML action
336 -- on ssp_medicals.
337 --
338 procedure earnings_control (p_person_id in number, p_effective_date in date);
339 --
340 -- Performs the necessary actions to trigger recalculation of SMP if a
341 -- calculation of average earnings is inserted or updated (delete is
342 -- not allowed). As the initial calculation of SMP is triggered by the
343 -- insertion of maternity leave, we only need recalculate if such leave
344 -- is recorded. Also, we only need to recalculate if the earnings
345 -- calculation is used by the SMP calculation (decided by its
346 -- effective date). This procedure should be called from row-level
347 -- database triggers on ssp_earnings_calculations after insert and
348 -- update. Those triggers should also call a similar procedure for SSP
349 -- recalculation and so a shared requirement of both these procedures is
350 -- for the triggers to filter out any updates which do not move the
351 -- earnings figure to the other side of the Lower Earnings Limit; if
352 -- the limit is not crossed by the change, then no recalculation is
353 -- necessary.
354 --
355 procedure person_control (p_person_id in number, p_date_of_death in date);
356 --
357 -- Performs the necessary actions to trigger recalculation of SMP if a
358 -- person dies. Only triggers recalculation if death occurs within an
359 -- MPP for which leave exists. Call from row-level after update
360 -- database trigger on per_people_f.
361 --
362 --
363 procedure stoppage_control (p_maternity_id in number);
364 --
365 -- Performs the necessary actions to trigger recalculation of SMP if a
366 -- stoppage is modified. We must NOT call the check on entitlement to
367 -- SMP from this procedure because that check will cause modification
368 -- of stoppages and we would enter an infinite loop. To protect
369 -- ourselves from this, we filter calls to this procedure in the
370 -- database trigger on ssp_stoppages by only calling it for stoppages
371 -- changed by the user, rather than the system (ie user_entered = 'Y').
372 -- Also, we call the generate_payments procedure directly from this
373 -- procedure and miss out the check_entitlement_to_SMP procedure.
374 --
375 --
376 procedure SMP_control (p_maternity_id in number,
377 p_deleting in boolean default FALSE);
378
379 --
380 function get_max_SMP_date(p_maternity_id in number) return date;
381 function get_max_SMP_date(p_maternity_id in number,
382 p_due_date in date,
383 p_mpp_date in date) return date;
384 --
385 end ssp_smp_pkg;
[Home] [Help]