1 PACKAGE BODY pay_no_holiday_pay AS
2 /* $Header: pynoholp.pkb 120.0.12000000.1 2007/05/22 07:53:23 rajesrin noship $ */
3 -- Function to get the G value.
4 FUNCTION get_grate(p_business_group_id IN NUMBER, p_effective_date IN DATE) RETURN NUMBER IS l_g_rate NUMBER;
5 BEGIN
6 -- Get the daily rate value
7 BEGIN
8
9 -- Bug Fix 5566622 : Value of G (National Insurance Base Rate) to be taken from Global and not user table.
10
11 -- l_g_rate := to_number(hruserdt.get_table_value(p_business_group_id, 'NO_GLOBAL_CONSTANTS', 'Value', 'NATIONAL_INSURANCE_BASE_RATE', p_effective_date));
12
13 select to_number(GLOBAL_VALUE)
14 into l_g_rate
15 from ff_globals_f
16 where global_name = 'NO_NATIONAL_INSURANCE_BASE_RATE'
17 and LEGISLATION_CODE = 'NO'
18 and BUSINESS_GROUP_ID IS NULL
19 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE ;
20
21
22 EXCEPTION
23 WHEN no_data_found THEN
24 l_g_rate := NULL;
25 END;
26
27 RETURN l_g_rate;
28 END get_grate;
29
30 --Function to get the age of a person as on 31-AUG of the holiday year.
31 FUNCTION get_age(p_payroll_proc_start_date IN DATE, p_date_of_birth IN DATE) RETURN NUMBER IS l_age NUMBER;
32 l_effective_date DATE;
33 BEGIN
34 l_effective_date := to_date('31/08/' || to_char(p_payroll_proc_start_date, 'YYYY'), 'DD/MM/YYYY');
35 l_age := FLOOR(months_between(l_effective_date, p_date_of_birth) / 12);
36 RETURN l_age;
37 END get_age;
38
39 /* Function to whether the payroll run is the last payroll run of the year in order
40 recalulate the holiday pay over 60*/ FUNCTION get_last_payroll(p_payroll_id IN NUMBER, p_pay_proc_period_end_date IN DATE) RETURN VARCHAR2 IS l_next_period_start_date DATE;
41 l_next_period_end_date DATE;
42 l_flag VARCHAR2(1);
43
44 CURSOR csr_next_pay_proc_end_date(p_start_date DATE) IS
45 SELECT end_date
46 FROM per_time_periods ptp
47 WHERE ptp.payroll_id = p_payroll_id
48 AND ptp.start_date = p_start_date;
49
50 BEGIN
51 l_next_period_start_date := p_pay_proc_period_end_date + 1;
52
53 OPEN csr_next_pay_proc_end_date(l_next_period_start_date);
54 FETCH csr_next_pay_proc_end_date
55 INTO l_next_period_end_date;
56 CLOSE csr_next_pay_proc_end_date;
57
58 IF(to_char(p_pay_proc_period_end_date, 'YYYY') <> to_char(l_next_period_start_date, 'YYYY')) THEN
59 l_flag := 'Y';
60 RETURN l_flag;
61 ELSIF(to_char(l_next_period_start_date, 'YYYY') <> to_char(l_next_period_end_date, 'YYYY')) THEN
62 l_flag := 'Y';
63 RETURN l_flag;
64 ELSE
65 l_flag := 'N';
66 RETURN l_flag;
67 END IF;
68
69 END;
70
71 -- Function to get the assignments status.
72 FUNCTION get_assg_status(p_business_group_id IN NUMBER, p_asg_id IN NUMBER, p_pay_proc_period_start_date IN DATE, p_pay_proc_period_end_date IN DATE) RETURN VARCHAR2 IS
73
74 CURSOR csr_asg IS
75 SELECT MIN(paaf.effective_start_date) effective_start_date
76 FROM per_all_assignments_f paaf
77 WHERE paaf.business_group_id = p_business_group_id
78 AND paaf.assignment_id = p_asg_id
79 AND paaf.assignment_status_type_id = 3;
80
81 l_flag VARCHAR2(1);
82 l_asg_status csr_asg % rowtype;
83
84 BEGIN
85
86 OPEN csr_asg;
87 FETCH csr_asg
88 INTO l_asg_status;
89 CLOSE csr_asg;
90
91 IF l_asg_status.effective_start_date >= p_pay_proc_period_start_date
92 AND l_asg_status.effective_start_date <=(p_pay_proc_period_end_date + 1) THEN
93 l_flag := 'T';
94 ELSE
95 l_flag := 'A';
96 END IF;
97
98 RETURN l_flag;
99
100 END get_assg_status;
101
102 -- Function to get the entitlement days as years last payroll run end date.
103 FUNCTION get_entitlement_days(p_business_group_id IN NUMBER, p_asg_id IN NUMBER, p_tax_unit_id IN NUMBER, p_effective_date IN DATE, p_above_60 IN VARCHAR2, p_entit_days OUT nocopy NUMBER,
104 p_entit_days_over_60 OUT nocopy NUMBER) RETURN NUMBER IS CURSOR csr_assig_details IS
105 SELECT hsck.segment15 holiday_entitlement,
106 hsck.segment16 holiday_pay_calc_basis
107 FROM per_all_assignments_f paaf,
108 hr_soft_coding_keyflex hsck
109 WHERE paaf.business_group_id = p_business_group_id
110 AND paaf.assignment_id = p_asg_id
111 AND p_effective_date BETWEEN paaf.effective_start_date
112 AND paaf.effective_end_date
113 AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;
114
115 CURSOR csr_person_details IS
116 SELECT papf.per_information16 holiday_entitlement,
117 papf.per_information17 holiday_pay_calc_basis
118 FROM per_all_people_f papf,
119 per_all_assignments_f paaf
120 WHERE paaf.business_group_id = p_business_group_id
121 AND paaf.assignment_id = p_asg_id
122 AND papf.person_id = paaf.person_id
123 AND p_effective_date BETWEEN papf.effective_start_date
124 AND papf.effective_end_date;
125
126 --Added Cursor to get the legal employer details
127 CURSOR csr_le_details IS
128 SELECT hoi.org_information1 holiday_entitlement,
129 hoi.org_information2 holiday_pay_calc_basis
130 FROM hr_organization_information hoi
131 WHERE hoi.organization_id = p_tax_unit_id
132 AND hoi.org_information_context = 'NO_HOLIDAY_PAY_DETAILS';
133
134 CURSOR csr_globals(p_global_name VARCHAR2) IS
135 SELECT fgf.global_value
136 FROM ff_globals_f fgf
137 WHERE fgf.global_name = p_global_name
138 AND fgf.legislation_code = 'NO'
139 AND p_effective_date BETWEEN fgf.effective_start_date
140 AND fgf.effective_end_date;
141
142 l_holiday_entitlement VARCHAR2(150);
143 l_holiday_pay_calc_basis VARCHAR2(150);
144 l_entit_days NUMBER;
145 l_entit_days_over_60 NUMBER;
146 l_assig_details csr_assig_details % rowtype;
147 l_person_details csr_person_details % rowtype;
148 l_le_details csr_le_details % rowtype;
149 l_global_name ff_globals_f.global_name%TYPE;
150 BEGIN
151
152 /*Bug 5346834 fix*/
153
154 OPEN csr_assig_details;
155 FETCH csr_assig_details
156 INTO l_assig_details;
157 CLOSE csr_assig_details;
158
159 OPEN csr_person_details;
160 FETCH csr_person_details
161 INTO l_person_details;
162 CLOSE csr_person_details;
163
164 OPEN csr_le_details;
165 FETCH csr_le_details
166 INTO l_le_details;
167 CLOSE csr_le_details;
168
169 IF(l_assig_details.holiday_entitlement IS NOT NULL) THEN
170 l_holiday_entitlement := l_assig_details.holiday_entitlement;
171 ELSIF(l_person_details.holiday_entitlement IS NOT NULL) THEN
172 l_holiday_entitlement := l_person_details.holiday_entitlement;
173 ELSIF(l_person_details.holiday_entitlement IS NOT NULL) THEN
174 l_holiday_entitlement := l_person_details.holiday_entitlement;
175 ELSIF(l_le_details.holiday_entitlement IS NOT NULL) THEN
176 l_holiday_entitlement := l_le_details.holiday_entitlement;
177 ELSE
178 l_holiday_entitlement := 'HA';
179 END IF;
180
181 IF(l_assig_details.holiday_pay_calc_basis IS NOT NULL) THEN
182 l_holiday_pay_calc_basis := l_assig_details.holiday_pay_calc_basis;
183 ELSIF(l_person_details.holiday_pay_calc_basis IS NOT NULL) THEN
184 l_holiday_pay_calc_basis := l_person_details.holiday_pay_calc_basis;
185 ELSIF(l_le_details.holiday_pay_calc_basis IS NOT NULL) THEN
186 l_holiday_pay_calc_basis := l_le_details.holiday_pay_calc_basis;
187 ELSE
188 l_holiday_pay_calc_basis := '5DAY';
189 END IF;
190
191 IF(l_holiday_entitlement = 'HA'
192 AND l_holiday_pay_calc_basis = '5DAY') THEN
193 l_global_name := 'NO_HOLACT_5DAY_ENTITLMENT';
194
195 ELSIF(l_holiday_entitlement = 'HA'
196 AND l_holiday_pay_calc_basis = '6DAY') THEN
197 l_global_name := 'NO_HOLACT_6DAY_ENTITLMENT';
198 ELSIF(l_holiday_entitlement = 'CA'
199 AND l_holiday_pay_calc_basis = '5DAY') THEN
200 l_global_name := 'NO_COLAGR_5DAY_ENTITLMENT';
201 ELSIF(l_holiday_entitlement = 'CA'
202 AND l_holiday_pay_calc_basis = '6DAY') THEN
203 l_global_name := 'NO_COLAGR_6DAY_ENTITLMENT';
204 END IF;
205
206 OPEN csr_globals(l_global_name);
207 FETCH csr_globals
208 INTO p_entit_days;
209 CLOSE csr_globals;
210
211 IF p_above_60 = 'Y' THEN
212
213 IF(l_holiday_pay_calc_basis = '5DAY') THEN
214 l_global_name := 'NO_HOL_5DAY_OVER60_ENTITLEMENT';
215 ELSE
216 l_global_name := 'NO_HOL_6DAY_OVER60_ENTITLEMENT';
217 END IF;
218 OPEN csr_globals(l_global_name);
219 FETCH csr_globals
220 INTO p_entit_days_over_60;
221 CLOSE csr_globals;
222
223 ELSE
224 p_entit_days_over_60 := 0;
225 END IF;
226
227 RETURN 1;
228 END get_entitlement_days;
229
230 -- Function to get the fixed period for a payroll.
231 FUNCTION get_fixed_period(p_payroll_id IN NUMBER, p_start_date IN DATE) RETURN NUMBER IS l_fixed_period NUMBER;
232
233 CURSOR csr_fixed_period IS
234 SELECT period_num
235 FROM per_time_periods
236 WHERE payroll_id = p_payroll_id
237 AND to_char(start_date, 'YYYY') = to_char(p_start_date, 'YYYY')
238 AND prd_information2 = 'Y';
239 BEGIN
240
241 OPEN csr_fixed_period;
242 FETCH csr_fixed_period
243 INTO l_fixed_period;
244 CLOSE csr_fixed_period;
245
246 RETURN l_fixed_period;
247
248 END get_fixed_period;
249
250 -- Function to get the previous employer details.
251 FUNCTION get_prev_employer_days(p_business_group_id IN NUMBER, p_assg_id IN NUMBER, p_emp_hire_date IN DATE, p_asg_start_date IN DATE) RETURN NUMBER IS
252
253 l_person_id per_all_people_f.person_id%TYPE;
254
255 CURSOR csr_person_id IS
256 SELECT paaf.person_id
257 FROM per_all_assignments_f paaf
258 WHERE paaf.business_group_id = p_business_group_id
259 AND paaf.assignment_id = p_assg_id;
260 /*Bug 5344736 fix - getting the previous employer days */
261 /* Bug 5344736 fix - added condition to check the assignment start year*/
262 CURSOR csr_prev_employer_days(p_person_id NUMBER) IS
263 SELECT SUM(to_number(ppe.pem_information2))
264 FROM per_previous_employers ppe
265 WHERE ppe.business_group_id = p_business_group_id
266 AND ppe.person_id = p_person_id
267 AND to_char(ppe.end_date, 'YYYY') = to_char(p_emp_hire_date, 'YYYY')
268 AND to_char(ppe.end_date, 'YYYY') = to_char(p_asg_start_date, 'YYYY');
269
270 l_prev_employer_days per_previous_employers.pem_information2%TYPE;
271 BEGIN
272
273 OPEN csr_person_id;
274 FETCH csr_person_id
275 INTO l_person_id;
276 CLOSE csr_person_id;
277
278 OPEN csr_prev_employer_days(l_person_id);
279 FETCH csr_prev_employer_days
280 INTO l_prev_employer_days;
281 CLOSE csr_prev_employer_days;
282 RETURN nvl(l_prev_employer_days, 0);
283 END get_prev_employer_days;
284
285 -- Function to get the holiday details required for hoiliday pay calculation.
286 FUNCTION get_hol_parameters(p_bus_group_id IN NUMBER, p_assignment_id IN NUMBER, p_date_earned IN DATE, p_tax_unit_id IN NUMBER, p_hourly_salaried_code IN OUT nocopy VARCHAR2,
287 p_holiday_entitlement IN OUT nocopy VARCHAR2, p_holiday_pay_calc_basis IN OUT nocopy VARCHAR2, p_holiday_pay_in_fixed_period IN OUT nocopy VARCHAR2,
288 p_hol_pay_over60_in_fix_period IN OUT nocopy VARCHAR2, p_holiday_pay_to_be_adjusted IN OUT nocopy VARCHAR2, p_res_hol_pay_to_6g_for_over60 IN OUT nocopy VARCHAR2) RETURN NUMBER IS
289
290 CURSOR csr_assg_details IS
291 SELECT paaf.hourly_salaried_code hourly_salaried_code,
292 hsck.segment15 holiday_entitlement,
293 hsck.segment16 holiday_pay_calc_basis,
294 hsck.segment17 holiday_pay_in_fixed_period,
295 hsck.segment18 hol_pay_over60_in_fix_period,
296 hsck.segment19 holiday_pay_to_be_adjusted
297 FROM per_all_assignments_f paaf,
298 hr_soft_coding_keyflex hsck
299 WHERE paaf.business_group_id = p_bus_group_id
300 AND paaf.assignment_id = p_assignment_id
301 AND p_date_earned BETWEEN paaf.effective_start_date
302 AND paaf.effective_end_date
303 AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;
304
305 CURSOR csr_person_details IS
306 SELECT papf.per_information6 hourly_salaried_code,
307 papf.per_information16 holiday_entitlement,
308 papf.per_information17 holiday_pay_calc_basis,
309 papf.per_information18 holiday_pay_in_fixed_period,
313 FROM per_all_people_f papf,
310 papf.per_information19 hol_pay_over60_in_fix_period,
311 papf.per_information20 holiday_pay_to_be_adjusted,
312 papf.per_information21 res_hol_pay_to_6g_for_over60
314 per_all_assignments_f paaf
315 WHERE paaf.business_group_id = p_bus_group_id
316 AND paaf.assignment_id = p_assignment_id
317 AND papf.person_id = paaf.person_id
318 AND p_date_earned BETWEEN papf.effective_start_date
319 AND papf.effective_end_date;
320
321 --Added Cursor to get the legal employer details
322 CURSOR csr_le_details IS
323 SELECT hoi.org_information1 holiday_entitlement,
324 hoi.org_information2 holiday_pay_calc_basis,
325 hoi.org_information3 holiday_pay_in_fixed_period,
326 hoi.org_information4 hol_pay_over60_in_fix_period,
327 hoi.org_information5 holiday_pay_to_be_adjusted,
328 hoi.org_information6 res_hol_pay_to_6g_for_over60
329 FROM hr_organization_information hoi
330 WHERE hoi.organization_id = p_tax_unit_id
331 AND hoi.org_information_context = 'NO_HOLIDAY_PAY_DETAILS';
332
333 CURSOR csr_le_hourly_sal IS
334 SELECT hoi.org_information5 hourly_salaried_code
335 FROM hr_organization_information hoi
336 WHERE hoi.organization_id = p_tax_unit_id
337 AND hoi.org_information_context = 'NO_ABSENCE_PAYMENT_DETAILS';
338
339 l_person_details csr_person_details % rowtype;
340 l_le_details csr_le_details % rowtype;
341 l_le_hourly_sal csr_le_hourly_sal % rowtype;
342
343 BEGIN
344
345 OPEN csr_assg_details;
346 FETCH csr_assg_details
347 INTO p_hourly_salaried_code,
348 p_holiday_entitlement,
349 p_holiday_pay_calc_basis,
350 p_holiday_pay_in_fixed_period,
351 p_hol_pay_over60_in_fix_period,
352 p_holiday_pay_to_be_adjusted;
353 CLOSE csr_assg_details;
354
355 OPEN csr_person_details;
356 FETCH csr_person_details
357 INTO l_person_details;
358 CLOSE csr_person_details;
359
360 OPEN csr_le_details;
361 FETCH csr_le_details
362 INTO l_le_details;
363 CLOSE csr_le_details;
364
365 OPEN csr_le_hourly_sal;
366 FETCH csr_le_hourly_sal
367 INTO l_le_hourly_sal;
368 CLOSE csr_le_hourly_sal;
369
370 IF(p_hourly_salaried_code IS NULL) THEN
371
372 IF(l_person_details.hourly_salaried_code IS NOT NULL) THEN
373 p_hourly_salaried_code := l_person_details.hourly_salaried_code;
374 ELSIF(l_le_hourly_sal.hourly_salaried_code IS NOT NULL) THEN
375 p_hourly_salaried_code := l_le_hourly_sal.hourly_salaried_code;
376 ELSE
377 p_hourly_salaried_code := 'S';
378 END IF;
379
380 END IF;
381
382 IF p_holiday_entitlement IS NULL THEN
383
387 p_holiday_entitlement := l_le_details.holiday_entitlement;
384 IF(l_person_details.holiday_entitlement IS NOT NULL) THEN
385 p_holiday_entitlement := l_person_details.holiday_entitlement;
386 ELSIF(l_le_details.holiday_entitlement IS NOT NULL) THEN
388 ELSE
389 p_holiday_entitlement := 'HA';
390 END IF;
391
392 END IF;
393
394 IF p_holiday_pay_calc_basis IS NULL THEN
395
396 IF(l_person_details.holiday_pay_calc_basis IS NOT NULL) THEN
397 p_holiday_pay_calc_basis := l_person_details.holiday_pay_calc_basis;
398 ELSIF(l_le_details.holiday_pay_calc_basis IS NOT NULL) THEN
399 p_holiday_pay_calc_basis := l_le_details.holiday_pay_calc_basis;
400 ELSE
401 p_holiday_pay_calc_basis := '5DAY';
402 END IF;
403
404 END IF;
405
406 IF p_holiday_pay_in_fixed_period IS NULL THEN
407
408 IF(l_person_details.holiday_pay_in_fixed_period IS NOT NULL) THEN
409 p_holiday_pay_in_fixed_period := l_person_details.holiday_pay_in_fixed_period;
410 p_hol_pay_over60_in_fix_period := l_person_details.hol_pay_over60_in_fix_period;
411 p_holiday_pay_to_be_adjusted := l_person_details.holiday_pay_to_be_adjusted;
412 ELSIF(l_le_details.holiday_pay_in_fixed_period IS NOT NULL) THEN
413 p_holiday_pay_in_fixed_period := l_le_details.holiday_pay_in_fixed_period;
414 p_hol_pay_over60_in_fix_period := l_le_details.hol_pay_over60_in_fix_period;
415 p_holiday_pay_to_be_adjusted := l_le_details.holiday_pay_to_be_adjusted;
416
417 ELSE
418 p_holiday_pay_in_fixed_period := 'N';
419 p_hol_pay_over60_in_fix_period := 'N';
420 p_holiday_pay_to_be_adjusted := 'N';
421 END IF;
422
423 END IF;
424
425 IF(l_person_details.res_hol_pay_to_6g_for_over60 IS NOT NULL) THEN
426 p_res_hol_pay_to_6g_for_over60 := l_person_details.res_hol_pay_to_6g_for_over60;
427 ELSIF(l_le_details.res_hol_pay_to_6g_for_over60 IS NOT NULL) THEN
428 p_res_hol_pay_to_6g_for_over60 := l_le_details.res_hol_pay_to_6g_for_over60;
429 ELSE
430 p_res_hol_pay_to_6g_for_over60 := 'Y';
431 END IF;
432
433 RETURN 1;
434 END get_hol_parameters;
435
436 -- Function to get the assignment start date.
437
438 /*Bug 5334894 fix- Added a new function to get the assignment start date*/
439 FUNCTION get_asg_start_date(p_business_group_id IN NUMBER, p_assignment_id IN NUMBER, p_asg_start_date OUT nocopy DATE) RETURN NUMBER IS CURSOR csr_asg_start_date IS
440 SELECT MIN(paaf.effective_start_date)
441 FROM per_all_assignments_f paaf
442 WHERE paaf.business_group_id = p_business_group_id
443 AND paaf.assignment_id = p_assignment_id
444 AND paaf.assignment_status_type_id = 1;
445
446 BEGIN
447
448 OPEN csr_asg_start_date;
449 FETCH csr_asg_start_date
450 INTO p_asg_start_date;
451 CLOSE csr_asg_start_date;
452 RETURN 1;
453 END get_asg_start_date;
454
455 --Function to get the accrual act information from absence details
456 FUNCTION get_abs_hol_accr_entitl (p_bus_group_id IN NUMBER, p_assignment_id IN NUMBER, p_date_earned IN DATE, p_tax_unit_id IN NUMBER
457 , p_hol_accrual_entit OUT nocopy VARCHAR2) RETURN NUMBER IS
458
459 CURSOR csr_person_details IS
460 SELECT
461 papf.per_information13 hol_accrual_entit
462 FROM per_all_people_f papf,
463 per_all_assignments_f paaf
464 WHERE paaf.business_group_id = p_bus_group_id
465 AND paaf.assignment_id = p_assignment_id
466 AND papf.person_id = paaf.person_id
467 AND p_date_earned BETWEEN papf.effective_start_date
468 AND papf.effective_end_date;
469
470 --Added Cursor to get the legal employer details
471 CURSOR csr_le_details IS
472 SELECT hoi.org_information9 hol_accrual_entit
473 FROM hr_organization_information hoi
474 WHERE hoi.organization_id = p_tax_unit_id
475 AND hoi.org_information_context = 'NO_ABSENCE_PAYMENT_DETAILS';
476
477 l_person_details csr_person_details % rowtype;
478 l_le_details csr_le_details % rowtype;
479
480 BEGIN
481
482
483 OPEN csr_person_details;
484 FETCH csr_person_details
485 INTO l_person_details;
486 CLOSE csr_person_details;
487
488 OPEN csr_le_details;
489 FETCH csr_le_details
490 INTO l_le_details;
491 CLOSE csr_le_details;
492
493 p_hol_accrual_entit := NVL(NVL(l_person_details.hol_accrual_entit,l_le_details.hol_accrual_entit),'HA');
494
495 RETURN 1;
496
497 EXCEPTION WHEN OTHERS THEN
498 RETURN 0 ;
499
500 END get_abs_hol_accr_entitl;
501
502 END;
[Home] [Help]