1 PACKAGE BODY pay_hk_mpf AS
5 p_date_from in DATE,
2 /* $Header: pyhkudfs.pkb 120.15 2011/08/15 00:28:37 jalin noship $ */
3 FUNCTION get_retro_mpf(p_bus_grp_id in NUMBER,
4 p_assignment_id in NUMBER,
6 p_date_to in DATE,
7 p_pay_basis in VARCHAR2,
8 p_percentage in NUMBER,
9 p_calc_method in VARCHAR2,
10 p_hire_date in DATE,
11 p_min_birthday in DATE,
12 p_ER_Liability_Start_Date in DATE,
13 p_EE_Deductions_Start_Date in DATE,
14 p_Contributions_End_Date in DATE,
15 p_value1 out nocopy NUMBER,
16 p_value2 out nocopy NUMBER,
17 p_start_date1 out nocopy DATE,
18 p_start_date2 out nocopy DATE,
19 p_end_date1 out nocopy DATE,
20 p_end_date2 out nocopy DATE) RETURN NUMBER IS
21 --
22
23 TYPE get_retro_rec IS RECORD (l_mon_value NUMBER,
24 l_start_date DATE,
25 l_end_date DATE);
26
27 TYPE get_retro_value IS TABLE OF get_retro_rec INDEX BY BINARY_INTEGER;
28
29 CURSOR get_prev_periods (p_assignment_id NUMBER,
30 p_date_from DATE,
31 p_date_to DATE) is
32 select ptp.time_period_id,
33 bal.action_sequence,
34 bal.balance_name||bal.database_item_suffix,
35 ptp.start_date,
36 ptp.end_date,
37 bal.value,
38 to_number(to_char(bal.effective_date,'MM'))
39 from pay_hk_mpf_ri_balances_v bal,
40 per_time_periods ptp
41 where bal.assignment_id = p_assignment_id
42 and bal.balance_name = 'MPF_RI'
43 and bal.payroll_id = ptp.payroll_id
44 and bal.expiry_date between ptp.start_date and ptp.end_date
45 and ptp.end_date between p_date_from and p_date_to
46 order by ptp.start_date, bal.action_sequence desc;
47 --
48 --
49
50 l_action_sequence NUMBER;
51 l_period_id NUMBER;
52 l_period_start_date DATE;
53 l_period_end_date DATE;
54 l_old_period_id NUMBER ;
55 l_prorator NUMBER ;
56 l_value NUMBER;
57 l_balance VARCHAR2(160);
58 l_cum_mpf_arrs NUMBER;
59 l_cum_mpf_ri NUMBER;
60 l_ri1 NUMBER;
61 l_ri2 NUMBER;
62 l_min_ri NUMBER;
63 l_max_ri NUMBER;
64 l_capped_ri_ptd NUMBER;
65 l_er_prorator NUMBER;
66
67 l_max_birthday DATE;
68 l_ee_eligibility DATE;
69 l_get_retro_value get_retro_value;
70 l_month VARCHAR2(3);
71 l_prev_month VARCHAR2(3):= '0';
72 i number:=0;
73 --
74 BEGIN
75 --
76 hr_utility.set_location('pyhkudfs get_retro_mpf', 01);
77 --
78 l_cum_mpf_arrs := 0;
79 l_cum_mpf_ri := 0;
80 l_old_period_id := 0;
81 l_prorator := 1;
82 l_max_birthday := add_months(p_min_birthday,564) ; /* Bug 3623970 - Get the 65th Birthday date */
83
84 /* Bug 6924031 employee who commences employment after 1-February-2003 and reaches the age of 18 on or
85 after 18th January 2008. In this case Employee contribution start date will be 31st day from 18th Bday. */
86
87 IF p_hire_date >= to_date('2003/02/01','YYYY/MM/DD') and
88 p_min_birthday >= to_date('2008/01/18','YYYY/MM/DD') and
89 p_hire_date < p_min_birthday THEN
90 l_ee_eligibility := p_min_birthday+30;
91 ELSE
92 l_ee_eligibility := p_hire_date+30;
93 END IF;
94
95 OPEN get_prev_periods(p_assignment_id, p_date_from, p_date_to);
96 LOOP
97 /*
98 ** Get period RI balance, and approriate period dates
99 */
100 FETCH get_prev_periods
101 INTO l_period_id, l_action_sequence, l_balance,
102 l_period_start_date, l_period_end_date, l_value,l_month;
103 EXIT WHEN get_prev_periods%NOTFOUND;
104
105
106 IF l_period_id <> l_old_period_id THEN -- dealing with new period
107 /*
108 ** Get the MPF RI LIMITS that were in force at the period end date
109 ** Bug 12800852, use l_period_start_date for get_table_value function
110 */
111 hr_utility.set_location('pyhkudfs get_retro_mpf', 02);
112 IF p_pay_basis <> 'Year' and
113 p_pay_basis <> 'Semi-Year' and
114 p_pay_basis <> 'Semi-Month' and /* Bug 7171659, based on Day rate times days*/
115 p_pay_basis <> 'Lunar Month' THEN
116 l_min_ri := to_number(hruserdt.get_table_value(p_bus_grp_id,'MPF_RI_LIMITS'
117 ,p_pay_basis,'Lower',l_period_start_date));
118 l_max_ri := to_number(hruserdt.get_table_value(p_bus_grp_id,'MPF_RI_LIMITS'
119 ,p_pay_basis,'Upper',l_period_start_date));
120 ELSIF p_pay_basis = 'Semi-Month' THEN /* Bug 7171659 */
121 l_min_ri := to_number(hruserdt.get_table_value(p_bus_grp_id,'MPF_RI_LIMITS'
122 ,'Day','Lower',l_period_start_date))*(l_period_end_date-l_period_start_date+1);
123 l_max_ri := to_number(hruserdt.get_table_value(p_bus_grp_id,'MPF_RI_LIMITS'
124 ,'Day','Upper',l_period_start_date))*(l_period_end_date-l_period_start_date+1);
128 l_max_ri := to_number(hruserdt.get_table_value(p_bus_grp_id,'MPF_RI_LIMITS'
125 ELSIF p_pay_basis = 'Year' THEN
126 l_min_ri := to_number(hruserdt.get_table_value(p_bus_grp_id,'MPF_RI_LIMITS'
127 ,'Calendar Month','Lower',l_period_start_date))*12;
129 ,'Calendar Month','Upper',l_period_start_date))*12;
130 ELSIF p_pay_basis = 'Semi-Year' THEN
131 l_min_ri := to_number(hruserdt.get_table_value(p_bus_grp_id,'MPF_RI_LIMITS'
132 ,'Calendar Month','Lower',l_period_start_date))*6;
133 l_max_ri := to_number(hruserdt.get_table_value(p_bus_grp_id,'MPF_RI_LIMITS'
134 ,'Calendar Month','Upper',l_period_start_date))*6;
135 ELSIF p_pay_basis = 'Lunar Month' THEN
136 l_min_ri := to_number(hruserdt.get_table_value(p_bus_grp_id,'MPF_RI_LIMITS'
137 ,'Week','Lower',l_period_start_date))*4;
138 l_max_ri := to_number(hruserdt.get_table_value(p_bus_grp_id,'MPF_RI_LIMITS'
139 ,'Week','Upper',l_period_start_date))*4;
140 END IF;
141 /*
142 ** Calculate Return Values based on p_calc_method and
143 ** apply the capping to the periodic RI based on p_calc_method
144 */
145
146
147 hr_utility.trace('l_cum_mpf_arrs' || to_char(l_cum_mpf_arrs));
148 hr_utility.trace('l_value' || to_char(l_value));
149 hr_utility.trace('l_min_ri' || to_char(l_min_ri));
150 hr_utility.trace('l_max_ri' || to_char(l_max_ri));
151 hr_utility.trace('p_percentage' || to_char(p_percentage));
152 hr_utility.trace('p_calc_method' ||p_calc_method);
153
154 IF p_calc_method = 'RI' THEN
155 hr_utility.set_location('pyhkudfs get_retro_mpf', 10);
156 /* Bug 7535161, to separate pay period */
157 IF l_month <> l_prev_month THEN
158 i:=i+1;
159 l_get_retro_value(i).l_mon_value:=0;
160 END IF;
161 IF p_Contributions_End_Date >= l_period_start_date THEN
162 l_get_retro_value(i).l_mon_value := l_get_retro_value(i).l_mon_value + l_value;
163 l_cum_mpf_ri := l_cum_mpf_ri + l_value;
164 END IF;
165 ELSIF p_calc_method = 'ER' THEN
166 hr_utility.set_location('pyhkudfs get_retro_mpf', 15);
167 /* Bug 2798013. Added the following proration logic to calculate
168 Retro MPF Liability */
169 IF p_Contributions_End_Date < l_period_start_date THEN
170 l_er_prorator := 0;
171 ELSIF p_date_from <= l_period_start_date and
172 p_date_to >= l_period_end_date and
173 p_Contributions_End_Date > l_period_end_date THEN
174 l_er_prorator := 1;
175 /* Bug 2824603 added p_min_birthday check, p_contributions_end_date check */
176 /* Bug 2824660 Added proration logic when p_ER_Liability_Start_Date is
177 entered and is mid period. */
178 /* Bug 3623970 - Excluded BirthDate from 65 Years check */
179 /* Bug 9827385 - Included the last day of period checking */
180 ELSIF p_Contributions_End_Date > l_period_start_date and
181 p_Contributions_End_Date <= l_period_end_date THEN
182 IF p_Contributions_End_Date = l_max_birthday THEN
183 l_er_prorator := (p_Contributions_End_Date- greatest(p_date_from,l_period_start_date)) /
184 (l_period_end_date-l_period_start_date+1);
185 ELSE
186 l_er_prorator := (p_Contributions_End_Date- greatest(p_date_from,l_period_start_date)+1) /
187 (l_period_end_date-l_period_start_date+1);
188 END IF;
189 /* Bug 3976900 Removed prorated calculation for join after 18thDOB at same mth*/
190 /* Bug 4175965 Included prorated calculation for the 18th DOB is the last day of the first mth */
191 ELSIF ((p_min_birthday > l_period_start_date and
192 p_min_birthday <= l_period_end_date) or
193 (p_ER_Liability_Start_Date > l_period_start_date and
194 p_ER_Liability_Start_Date <= l_period_end_date)) THEN
195 IF p_date_from > l_period_start_date and
196 p_date_to >= l_period_end_date THEN
197 l_er_prorator := (l_period_end_date-greatest(p_date_from,p_ER_Liability_Start_Date)+1) /
198 (l_period_end_date-l_period_start_date+1);
199 ELSIF p_date_from >= l_period_start_date and
200 p_date_to < l_period_end_date THEN
201 l_er_prorator := (p_date_to-greatest(p_date_from,p_ER_Liability_Start_Date)+1) /
202 (l_period_end_date-l_period_start_date+1);
203 END IF;
204
205 ELSE
206 l_er_prorator := 1;
207 END IF;
208 hr_utility.trace('Anu ER p_date_from' || to_char(p_date_from,'DD/MM/YYYY'));
209 hr_utility.trace('p_date_to' || to_char(p_date_to,'DD/MM/YYYY'));
210 hr_utility.trace('l_period_start_date' || to_char(l_period_start_date,'DD/MM/YYYY'));
211 hr_utility.trace('l_period_end_date' || to_char(l_period_end_date,'DD/MM/YYYY'));
212 hr_utility.trace('p_ER_Liability_Start_Date' || to_char(p_ER_Liability_Start_Date,'DD/MM/YYYY'));
213 hr_utility.trace('l_er_prorator' || to_char(l_er_prorator));
214
215 /* Bug 2753292. Removed the check for min RI threshold */
216 l_cum_mpf_arrs := l_cum_mpf_arrs +
217 least(((l_value * l_er_prorator) * p_percentage / 100)
218 ,(l_max_ri * p_percentage / 100)) ;
222 l_get_retro_value(i).l_mon_value:=0;
219 /* Bug 7535161, calculate for separate pay period*/
220 IF l_month <> l_prev_month THEN
221 i:=i+1;
223 END IF;
224 IF l_er_prorator <>0 THEN
225 l_get_retro_value(i).l_mon_value := l_get_retro_value(i).l_mon_value+least(((l_value * l_er_prorator) * p_percentage / 100)
226 ,(l_max_ri * p_percentage / 100));
227 l_get_retro_value(i).l_start_date:= greatest(l_period_start_date,p_ER_Liability_Start_Date);
228 l_get_retro_value(i).l_end_date := least(l_period_end_date,p_Contributions_End_Date);
229 END IF;
230
231 ELSE
232 /*
233 ** Establish if its a full period RI,
234 ** if not, derive prorator for qualifying days
235 */
236 hr_utility.set_location('pyhkudfs get_retro_mpf', 20);
237
238 /* Bug 2753272. Added p_hire_date condition */
239 /* Bug# 4314140 Modified the partial period waiver logic so that partial period waiver should be applicable only for the month
240 in which 30th day of the employment falls*/
241 /* Bug 6270465, Modified the partial period waiver logic so that if ee eligibility date is the same as the period start date,
242 the prorator should not be the zero */
243 IF p_calc_method = 'EE' THEN
244 IF l_month <> l_prev_month THEN
245 i:=i+1;
246 l_get_retro_value(i).l_mon_value:=0;
247 END IF;
248 IF (l_ee_eligibility > l_period_start_date and /* Bug 6270465 */
249 p_hire_date>=to_date('01/02/2003','DD/MM/YYYY') ) then
250 l_prorator := 0; /* Bug 2660969 */
251 ELSIF p_date_from <= l_period_start_date and
252 p_date_to >= l_period_end_date and
253 p_Contributions_End_Date > l_period_end_date THEN
254 l_prorator := 1;
255 /* Bug 2824660 Added proration logic when p_EE_Deductions_Start_Date is
259 ELSIF p_Contributions_End_Date > l_period_start_date and
256 entered and is mid period and hire date is before 01-feb-2003
257 Bug 2824603 Added proration logic whn employee turns 65 or contribution end date is entered */
258 /* Bug 3623970 - Excluded BirthDate from 65 Years check */
260 p_Contributions_End_Date <= l_period_end_date then
261 IF p_Contributions_End_Date = l_max_birthday THEN
262 l_prorator := (p_Contributions_End_Date-greatest(p_date_from,l_period_start_date)) /
263 (l_period_end_date-l_period_start_date+1);
264 ELSE
265 l_prorator := (p_Contributions_End_Date-greatest(p_date_from,l_period_start_date) + 1) /
266 (l_period_end_date-l_period_start_date+1);
267 END IF;
268 /* Bug# 4314140 Proration calculation is included for the employee whose 18th birthday is end of the month*/
269 ELSIF (p_min_birthday > l_period_start_date and
270 p_min_birthday <= l_period_end_date) then
271 IF p_date_from > l_period_start_date and
272 p_date_to >= l_period_end_date THEN
273 l_prorator := (l_period_end_date-p_date_from+1) /
274 (l_period_end_date-l_period_start_date+1);
275 ELSIF p_date_from >= l_period_start_date and
276 p_date_to < l_period_end_date THEN
277 l_prorator := (p_date_to-l_period_start_date+1) /
278 (l_period_end_date-l_period_start_date+1);
279 END IF;
280 ELSIF (p_EE_Deductions_Start_Date > l_period_start_date AND
281 p_EE_Deductions_Start_Date < l_period_end_date) THEN
282 IF p_hire_date < to_date('01/02/2003','DD/MM/YYYY') THEN
283 IF p_date_from > l_period_start_date AND
284 p_date_to >= l_period_end_date THEN
285 l_prorator := (l_period_end_date-p_date_from+1) /
286 (l_period_end_date-l_period_start_date+1);
287 ELSIF p_date_from >= l_period_start_date and
288 p_date_to < l_period_end_date THEN
289 l_prorator := (p_date_to-l_period_start_date+1) /
290 (l_period_end_date-l_period_start_date+1);
291 END IF;
292 ELSE
293 l_prorator := 0;
294 END IF;
295 END IF;
296
300 hr_utility.trace('l_period_end_date' || to_char(l_period_end_date,'DD/MM/YYYY'));
297 hr_utility.trace('p_date_from'||to_char(p_date_from,'DD/MM/YYYY'));
298 hr_utility.trace('p_date_to' || to_char(p_date_to,'DD/MM/YYYY'));
299 hr_utility.trace('l_period_start_date' || to_char(l_period_start_date,'DD/MM/YYYY'));
301 hr_utility.trace('l_prorator' || to_char(l_prorator));
302
303 /*
304 ** Bug #2270318 - Check the RI with prorated value of Minimum Threshold
305 ** Also prorate the Maximum Threshold Value
306 ** Bug #4494597 - Removed Maximum Threshold Value Proration
307 */
308
309 IF (l_value >= l_min_ri * l_prorator) THEN
310 l_cum_mpf_arrs := l_cum_mpf_arrs +
311 least(((l_value * l_prorator) * p_percentage / 100)
312 ,(l_max_ri * p_percentage / 100));
313 l_get_retro_value(i).l_mon_value := l_get_retro_value(i).l_mon_value + least(((l_value * l_prorator) * p_percentage / 100)
314 ,(l_max_ri * p_percentage / 100));
315 l_get_retro_value(i).l_start_date:= l_period_start_date;
316 l_get_retro_value(i).l_end_date := l_period_end_date;
317 END IF;
318 END IF;
319 END IF;
320 l_old_period_id := l_period_id;
321 END IF;
322 l_prev_month:=l_month;
323 END LOOP;
324 CLOSE get_prev_periods;
325
326 IF i in (1,2) THEN
327 p_value1 := l_get_retro_value(1).l_mon_value;
328 p_start_date1 := greatest(p_date_from, l_get_retro_value(1).l_start_date);
329 p_end_date1 := l_get_retro_value(1).l_end_date;
330 END IF;
331
332 IF i = 2 THEN
333 p_value2 := l_get_retro_value(2).l_mon_value;
334 p_start_date2 := l_get_retro_value(2).l_start_date;
335 p_end_date2 := least(p_date_to, l_get_retro_value(2).l_end_date);
336 END IF;
337
338 IF p_value1 IS NULL THEN
339 p_value1 := 0;
340 END IF;
341
342 IF p_value2 IS NULL THEN
343 p_value2 := 0;
344 END IF;
345
346 IF p_calc_method = 'RI' THEN
347 hr_utility.set_location('pyhkudfs get_retro_mpf', 50);
348 hr_utility.trace('l_cur_mpf_ri:'||l_cum_mpf_ri);
349 RETURN l_cum_mpf_ri;
350 ELSE
351 hr_utility.set_location('pyhkudfs get_retro_mpf', 55);
352 RETURN l_cum_mpf_arrs;
353 END IF;
354 END get_retro_mpf;
355 --
356 FUNCTION hk_scheme_val(p_bus_grp_id in NUMBER,
357 p_assignment_id in NUMBER,
358 p_entry_value in VARCHAR2) RETURN VARCHAR2 IS
359 --
360 v_valid_scheme varchar2(1);
361 --
362 CURSOR check_source (p_bus_grp_id NUMBER,
363 p_entry_value VARCHAR2) is
364 select 'S'
365 from hr_organization_information d
366 ,hr_all_organization_units b
367 where to_number(d.org_information20) = to_number(p_entry_value)
368 and b.business_group_id = p_bus_grp_id
369 and b.organization_id = d.organization_id
370 and d.org_information_context = 'HK_MPF_SCHEMES';
371 --
372 BEGIN
373 --
374 hr_utility.set_location('pyhkudfs hk_scheme_val', 01);
375 --
376 v_valid_scheme := 'E';
377 open check_source (p_bus_grp_id, p_entry_value);
378 fetch check_source into v_valid_scheme;
379 close check_source;
380 --
381 hr_utility.set_location('pyhkudfs hk_scheme_val', 03);
382 --
383 RETURN v_valid_scheme;
384 --
385 END hk_scheme_val;
386 --
387 FUNCTION hk_quarters_val(p_bus_grp_id in NUMBER,
388 p_assignment_id in NUMBER,
389 p_entry_value in VARCHAR2) RETURN VARCHAR2 IS
390 --
391 v_valid_quarters varchar2(1);
392 --
393 CURSOR check_source (p_assignment_id NUMBER,
394 p_entry_value VARCHAR2) is
395 select 'S'
396 from per_assignment_extra_info d
397 where to_number(d.aei_information20) = to_number(p_entry_value)
398 and d.assignment_id = p_assignment_id
399 and d.aei_information_category = 'HR_QUARTERS_INFO_HK';
400 --
401 BEGIN
402 --
403 hr_utility.set_location('pyhkudfs hk_quarters_val', 01);
404 --
405 v_valid_quarters := 'E';
406 open check_source (p_assignment_id, p_entry_value);
407 fetch check_source into v_valid_quarters;
408 close check_source;
409 --
410 hr_utility.set_location('pyhkudfs hk_quarters_val', 03);
411 --
412 RETURN v_valid_quarters;
413 --
414 END hk_quarters_val;
415
416 /* Bug:3333006. Added the following function */
417 FUNCTION get_act_termination_date
418 (p_assignment_id in per_all_assignments_f.assignment_id%type,
419 p_date in date) RETURN date IS
420
421 l_act_term_date date;
422
423 CURSOR get_act_term_date(p_assignment_id in per_all_assignments_f.assignment_id%type,
424 p_date in date) IS
425 SELECT target.ACTUAL_TERMINATION_DATE
426 FROM
427 per_periods_of_service target,
428 per_assignments_f ASSIGN
429 WHERE p_date BETWEEN ASSIGN.effective_start_date
430 AND ASSIGN.effective_end_date
434
431 AND ASSIGN.assignment_id = p_assignment_id
432 AND ASSIGN.assignment_type = 'E'
433 AND target.period_of_service_id = ASSIGN.period_of_service_id;
435 BEGIN
436
437 Open get_act_term_date(p_assignment_id,p_date);
438 Fetch get_act_term_date into l_act_term_date;
439 If get_act_term_date%notfound then
440 l_act_term_date := to_date('31/12/4712','dd/mm/yyyy');
441 end if;
442 Close get_act_term_date;
443
444 return l_act_term_date;
445
446 END get_act_termination_date;
447 --
448 END pay_hk_mpf;
[Home] [Help]