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