1 package SSP_PAB_PKG as
2 /*$Header: sppabapi.pkh 120.1.12000000.1 2007/01/17 14:14:45 appldev noship $
3 +==============================================================================+
4 | Copyright (c) 1994 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +==============================================================================+
8 --
9 Name
10 Statutory Paternity Pay (Birth) Business Process
11 --
12 Purpose
13 To perform calculation of entitlement and payment for PAB purposes
14 --
15 History
16 17 Oct 02 A Blinko 2690305 Created from SSP_SMP_PKG
17 10 Jan 03 A Blinko amended latest_ppp_start_date
18 18 Oct 04 KThampan 4670360 Amended latest_ppp_start_date
19 */
20 --------------------------------------------------------------------------------
21 -- ***************************************************************************
22 -- * Performs actions required by the UK legislation for Statutory
23 -- * Adoption Pay. See the High Level Design document for general details
24 -- * of the functionality and use of ADO.
25 -- ***************************************************************************
26 --
27 c_PAB_element_name constant varchar2 (80) := 'Statutory Paternity Pay Birth';
28 c_PAB_corr_element_name constant varchar2 (80) := 'SPP Birth Corrections';
29 c_PAB_creator_type constant varchar2 (3) := 'M';
30 c_PAB_entry_type constant varchar2 (1) := 'E';
31 c_week_commencing_name constant varchar2 (30) := 'Week Commencing';
32 c_amount_name constant varchar2 (30) := 'Amount';
33 c_recoverable_amount_name constant varchar2 (30) := 'Recoverable Amount';
34 c_rate_name constant varchar2 (30) := 'Rate';
35 --
36 -- Get the element details for SMP
37 --
38 cursor CSR_PAB_ELEMENT_DETAILS (
39 --
40 -- Get the legislative parameters for PAB, which are held in the
41 -- Developer descriptive Flexfield of the element.
42 --
43 p_effective_date date default sysdate,
44 --
45 -- p_effective_date restricts us to a single row for the selected
46 -- element
47 --
48 p_element_name varchar2 default c_PAB_element_name
49 --
50 -- p_element_name allows us to select different elements in the same
51 -- cursor
52 --
53 ) is
54 --
55 select ele1.element_type_id,
56 ele1.effective_start_date,
57 ele1.effective_end_date,
58 to_number (ele1.element_information1) *7
59 EARLIEST_START_OF_PPP,
60 to_number (ele1.element_information2) *7
61 QUALIFYING_WEEK,
62 to_number (ele1.element_information3) *7
63 CONTINUOUS_EMPLOYMENT_PERIOD,
64 to_number (ele1.element_information4)
65 MAXIMUM_PPP_WEEKS,
66 to_number (ele1.element_information5)
67 MPP_NOTICE_REQUIREMENT,
68 to_number (ele1.element_information6) /100
69 SPP_RATE,
70 to_number (ele1.element_information7) /100
71 RECOVERY_RATE,
72 to_number (ele1.element_information8) *7
73 STILLBIRTH_THRESHOLD_WEEK,
74 to_number (ele1.element_information9)
75 STANDARD_RATE,
76 to_number (ele1.element_information10) *7
77 LATEST_END_OF_PPP,
78 ssp_smp_support_pkg.element_input_value_id
79 (ele1.element_type_id, c_rate_name)
80 RATE_ID,
81 ssp_smp_support_pkg.element_input_value_id
82 (ele1.element_type_id, c_week_commencing_name)
83 WEEK_COMMENCING_ID,
84 ssp_smp_support_pkg.element_input_value_id
85 (ele1.element_type_id, c_amount_name)
86 AMOUNT_ID,
87 ssp_smp_support_pkg.element_input_value_id
88 (ele1.element_type_id, c_recoverable_amount_name)
89 RECOVERABLE_AMOUNT_ID
90 from pay_element_types_f ele1
91 where ele1.element_name = p_element_name
92 and p_effective_date between ele1.effective_start_date
93 and ele1.effective_end_date;
94 --------------------------------------------------------------------------------
95 --function entitled_to_SAP (
96 --
97 --p_maternity_id in number
98 --
99 --) return boolean;
100 --
101 -- Check whether or not an absence gives rise to an entitlement to
102 -- Statutory Maternity Pay. First check whether there is a prima facia
103 -- entitlement (eg the person must be female), and then check for any
104 -- reasons for withholding payment from an entitled person. If there is
105 -- any such withholding reason, then insert a row in ssp_stoppages for
106 -- the period to which the reason applies.
107 --
108 -- While checking entitlement, bear in mind that stoppages may also be
109 -- created by users, and that the check may be being performed for
110 -- a second time. Therefore, if there is a user-generated stoppage
111 -- for a specific period, do not alter any value on it; should there be
112 -- a need to create a stoppage for the same reason as the user's
113 -- stoppage, which covers a wider period, then create stoppages around
114 -- the user's one. Any system-generated stoppages in existence at the
115 -- start of the check should first be deleted before commencing the
116 -- checks.
117 --
118 -- Another feature of stoppages is that the user may override them.
119 -- This means that the withholding reason is to be ignored for that
120 -- period and so the user can effectively prevent any notice being taken
121 -- of that reason. If the stoppage is only for a temporary period, then
122 -- the system may still generate a stoppage for that reason outside the
123 -- overridden period.
124 --
125 -- The following checks should be performed:
126 --
127 -- ABSENT
128 -- Check that the woman is absent on maternity leave
129 --
130 -- There must be a maternity leave record. If not
131 -- then exit silently (because this may be called from the trigger
132 -- on the absence table).
133 -- Check that notification of absence is given in time
134 --
135 -- CONTINUITY
136 -- Check that the woman has a period of service of the right length
137 -- covering the right period.
138 --
139 -- There must be an unbroken period of service for the woman which
140 -- lasts for the period of time defined, in weeks, in the SMP
141 -- continuous employment DDF segment, and the Qualifying Week. The
142 -- woman must have been employed for at least part of the Qualifying
143 -- Week. If this condition is not met then create a stoppage for the
144 -- whole maternity.
145 --
146 -- PREGNANT
147 -- Check that the woman is still pregnant or has given birth in the
148 -- correct timescales.
149 --
150 -- If there is a stillbirth occurs before the stillbirth threshhold
151 -- week, then create a stoppage for the whole maternity.
152 -- Stillbirth is determined by there being a date of birth and the
153 -- live birth flag being 'N'. The stillbirth threshhold week is
154 -- determined by converting to days the stillbirth threshhold period
155 -- defined on the SMP element DDF, and subtracting that number from the
156 -- EWC start date.
157 --
158 -- EMPLOYER
159 -- Check that the woman has not started work for a new employer after
160 -- the birth of the child.
161 --
162 -- The start date with new employer is on the maternity table. If it is
163 -- after the birth of the child then create a
164 -- stoppage from the week the woman started work for the new employer.
165 -- It is up to the user to override this if the new employer is one
166 -- who employed the woman in the qualifying week.
167 --
168 -- DEATH
169 -- Check that the woman has not died.
170 --
171 -- Create a stoppage from the Sunday following the date of death.
172 --
173 -- SMA
174 -- Check that the woman is not receiving Statutory Maternity Allowance
175 -- from the Department of Social Security.
176 --
177 -- The start date of Maternity Allowance is held on the maternity
178 -- table. If it is entered, then create a stoppage from that week.
179 --
180 -- EARNINGS
181 -- Check that the average earnings of the woman are high enough to
182 -- qualify for SMP.
183 --
184 -- The average earnings calculation is shared with the SSP package and
185 -- is defined in the header file for the SSP_SSP_pkg. The period to
186 -- which average earnings are to be calculated is the QW start date.
187 -- The average earnings period is defined on the SMP element DDF.
188 --
189 -- EVIDENCE
190 -- Check that medical evidence of maternity has been received in time.
191 --
192 -- The earliest SMP evidence, the latest SMP evidence and the
193 -- extended SMP evidence periods are defined in the SMP element DDF.
194 -- Create a stoppage for the whole maternity if the evidence date is
195 -- earlier than the earliest SMP evidence date. Calculate the earliest
196 -- SMP evidence date by converting the earliest SMP evidence period to
197 -- days from weeks, then subtracting that number of days from the
198 -- EWC start date. Create a stoppage for the whole maternity if the
199 -- evidence received date is later than the latest SMP evidence date.
200 -- Calculate the latest SMP evidence date by converting the latest
201 -- SMP evidence period to days from weeks and adding that number
202 -- to the MPP start date. If the latest date is exceeded, then if the
203 -- accept_late_notification flag is 'Y' then check the extended SMP
204 -- evidence date (in the same manner). If this date is exceeded, or if
205 -- the accept_late_notification flag is 'N', then create a stoppage
206 -- for the whole maternity.
207 --
208 -- CONFIRMATION
209 -- Check that confirmation of birth was received in time.
210 --
211 -- The SMP element DDF defines the number of days after the birth
212 -- which is the end of the period in which the employee may notify the
213 -- employer of the birth. If this date is exceeded, then create a
214 -- stoppage for the whole maternity.
215 --
216 function MATERNITY_RECORD_EXISTS
217 (
218 p_person_id in number
219 ) return boolean;
220 --
221 -- Returns TRUE if there is a maternity record for the person
222 --
223 pragma restrict_references (maternity_record_exists, WNPS,WNDS);
224 --
225
226 function QUALIFYING_WEEK
227 (
228 p_due_date in date
229 ) return date;
230 --
231 --
232 pragma restrict_references (QUALIFYING_WEEK, WNPS,WNDS);
233 --
234 function Earliest_PPP_start_date
235 (
236 p_birth_date in date
237 ) return date;
238 --
239 -- Returns the earliest date a person may start their Paternity Pay
240 -- Period, based on the due date and assuming a normal pregnancy.
241 --
242 pragma restrict_references (Earliest_PPP_start_date, WNPS,WNDS);
243 --
244
245 function Latest_PPP_start_date
246 (
247 p_birth_date in date,
248 p_ewc in date,
249 p_due_date in date
250 ) return date;
251 --
252 function expected_week_of_confinement
253 (
254 p_due_date in date
255 ) return date;
256
257 pragma restrict_references (expected_week_of_confinement, WNPS,WNDS);
258
259 function Continuous_employment_date
260 (
261 p_due_date in date
262 ) return date;
263 --
264 -- Returns the start date of the period ending with the Qualifying
265 -- Week, for which the woman must have been continuously employed
266 -- in order to qualify for SMP
267 --
268 pragma restrict_references (Continuous_employment_date, WNPS,WNDS);
269 --
270 procedure PAB_control (p_maternity_id in number,
271 p_deleting in boolean default FALSE);
272 --
273 end ssp_pab_pkg;