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