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