1 PACKAGE BODY pqp_pension_functions AS
2 /* $Header: pqppenff.pkb 120.8.12010000.3 2008/10/14 12:55:19 rsahai ship $ */
3
4 g_ptp_formula_exists BOOLEAN := TRUE;
5 g_ptp_formula_cached BOOLEAN := FALSE;
6 g_ptp_formula_id ff_formulas_f.formula_id%TYPE;
7 g_ptp_formula_name ff_formulas_f.formula_name%TYPE;
8
9 -- =============================================================================
10 -- Cursor to get the defined balance id for a given balance and dimension
11 -- =============================================================================
12 CURSOR csr_defined_bal (c_balance_name IN Varchar2
13 ,c_dimension_name IN Varchar2
14 ,c_business_group_id IN Number) IS
15 SELECT db.defined_balance_id
16 FROM pay_balance_types pbt
17 ,pay_defined_balances db
18 ,pay_balance_dimensions bd
19 WHERE pbt.balance_name = c_balance_name
20 AND pbt.balance_type_id = db.balance_type_id
21 AND bd.balance_dimension_id = db.balance_dimension_id
22 AND bd.dimension_name = c_dimension_name
23 AND (pbt.business_group_id = c_business_group_id OR
24 pbt.legislation_code = 'NL')
25 AND (db.business_group_id = pbt.business_group_id OR
26 db.legislation_code = 'NL');
27
28 --
29 -- ----------------------------------------------------------------------------
30 -- |---------------------< get_pension_type_details >--------------------------|
31 -- ----------------------------------------------------------------------------
32 --
33 FUNCTION get_pension_type_details
34 (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
35 ,p_date_earned IN DATE
36 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
37 ,p_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE
38 ,p_legislation_code IN pqp_pension_types_f.legislation_code%TYPE
39 ,p_column_name IN VARCHAR2
40 ,p_column_value OUT NOCOPY VARCHAR2
41 ,p_error_message OUT NOCOPY VARCHAR2
42 ) RETURN NUMBER IS
43
44 CURSOR c_pty_cur (c_business_group_id IN NUMBER
45 ,c_pension_type_id IN NUMBER
46 ,c_date_earned IN DATE) IS
47 SELECT *
48 FROM pqp_pension_types_f
49 WHERE c_date_earned BETWEEN effective_start_date
50 AND effective_end_date
51 AND business_group_id = c_business_group_id
52 AND pension_type_id = c_pension_type_id;
53
54 CURSOR c_get_subcat (c_sub_cat IN VARCHAR2) IS
55 SELECT meaning
56 FROM fnd_lookup_values
57 WHERE lookup_type = 'PQP_PENSION_SUB_CATEGORY'
58 AND lookup_code = c_sub_cat
59 AND language = 'US';
60
61 CURSOR c_get_person_age IS
62 SELECT to_char(per.date_of_birth,'RRRR')
63 FROM per_all_people_f per,per_all_assignments_f paa
64 WHERE per.person_id = paa.person_id
65 AND p_date_earned between paa.effective_start_date and paa.effective_end_date
66 AND p_date_earned between per.effective_start_date and per.effective_end_date
67 AND paa.assignment_id = p_assignment_id;
68
69 CURSOR c_get_ee_age_threshold(c_pension_type_id IN NUMBER) IS
70 SELECT NVL(EE_AGE_THRESHOLD,'N')
71 FROM pqp_pension_types_f
72 WHERE pension_type_id = c_pension_type_id
73 AND p_date_earned between effective_start_date and effective_end_date;
74
75 CURSOR c_get_er_age_threshold(c_pension_type_id IN NUMBER) IS
76 SELECT NVL(ER_AGE_THRESHOLD,'N')
77 FROM pqp_pension_types_f
78 WHERE pension_type_id = c_pension_type_id
79 AND p_date_earned between effective_start_date and effective_end_date;
80
81
82
83 l_proc_name VARCHAR2(150) := g_proc_name || 'get_pension_type_details';
84 l_pension_id pqp_pension_types_f.pension_type_id%TYPE;
85 l_subcat VARCHAR2(80);
86 l_pension_rec c_pty_cur%ROWTYPE;
87 l_ee_age_threshold pqp_pension_types_f.ee_age_threshold%TYPE;
88 l_person_year_of_birth VARCHAR2(10);
89 l_er_age_threshold pqp_pension_types_f.er_age_threshold%TYPE;
90
91 BEGIN
92
93 hr_utility.set_location('Entering : '||l_proc_name, 10);
94
95 l_pension_id := p_pension_type_id;
96
97 --
98 -- Check if the pension_type_id is already in cache
99 --
100 IF NOT g_pension_rec.EXISTS(l_pension_id) THEN
101 hr_utility.set_location('..Pension Id :'||l_pension_id
102 ||' does not exists',15);
103 g_pension_rec.DELETE;
104 OPEN c_pty_cur (c_business_group_id => p_business_group_id
105 ,c_pension_type_id => p_pension_type_id
106 ,c_date_earned => p_date_earned);
107 FETCH c_pty_cur INTO g_pension_rec(l_pension_id);
108 CLOSE c_pty_cur;
109
110 --
111 -- Check if the pension id in the PL/SQL table is valid for
112 -- the passed date-earned
113 --
114 ELSIF NOT(p_date_earned
115 BETWEEN g_pension_rec(l_pension_id).effective_start_date
116 AND g_pension_rec(l_pension_id).effective_end_date
117 AND g_pension_rec(l_pension_id).pension_type_id = l_pension_id
118 AND g_pension_rec(l_pension_id).business_group_id = p_business_group_id
119 ) THEN
120 hr_utility.set_location('..Pension Id :'||l_pension_id
121 ||' does exists in pl/sql table',20);
122 hr_utility.set_location('..Pension Id is not valid for given date'
123 ||p_date_earned,25);
124 g_pension_rec.DELETE(l_pension_id);
125
126 OPEN c_pty_cur (c_business_group_id => p_business_group_id
127 ,c_pension_type_id => p_pension_type_id
128 ,c_date_earned => p_date_earned);
129 FETCH c_pty_cur
130 INTO g_pension_rec(l_pension_id);
131 CLOSE c_pty_cur;
132 END IF;
133
134 --
135 -- Get the column value from the PL/SQL table based
136 -- on the column name provided
137 --
138 IF p_column_name = 'SALARY_CALCULATION_METHOD' THEN
139 p_column_value
140 := g_pension_rec(l_pension_id).salary_calculation_method;
141 IF p_column_value IS NULL
142 AND g_pension_rec(l_pension_id).pension_category = 'S' THEN
143 p_column_value := '2';
144 END IF;
145
146 ELSIF p_column_name = 'THRESHOLD_CONVERSION_RULE' THEN
147 p_column_value
148 := g_pension_rec(l_pension_id).threshold_conversion_rule;
149 ELSIF p_column_name = 'CONTRIBUTION_CONVERSION_RULE' THEN
150 p_column_value
151 := g_pension_rec(l_pension_id).contribution_conversion_rule;
152 ELSIF p_column_name = 'ER_ANNUAL_LIMIT' THEN
153 p_column_value
154 := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).er_annual_limit),trim(to_char(0,'9')));
155 ELSIF p_column_name = 'EE_ANNUAL_LIMIT' THEN
156 p_column_value
157 := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).ee_annual_limit),trim(to_char(0,'9')));
158 ELSIF p_column_name = 'MINIMUM_AGE' THEN
159 p_column_value
160 := NVL(to_char(g_pension_rec(l_pension_id).minimum_age),trim(to_char(0,'9')));
161 ELSIF p_column_name = 'MAXIMUM_AGE' THEN
162 p_column_value
163 := NVL(to_char(g_pension_rec(l_pension_id).maximum_age),trim(to_char(999,'999')));
164 ELSIF p_column_name = 'EE_ANNUAL_CONTRIBUTION' THEN
165 p_column_value
166 := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).ee_annual_contribution)
167 ,trim(to_char(0,'9')));
168 ELSIF p_column_name = 'ER_ANNUAL_CONTRIBUTION' THEN
169 p_column_value
170 := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).er_annual_contribution)
171 ,trim(to_char(0,'9')));
172 ELSIF p_column_name = 'EE_CONTRIBUTION_PERCENT' THEN
173 p_column_value
174 := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).ee_contribution_percent)
175 ,trim(to_char(0,'9')));
176 ELSIF p_column_name = 'ER_CONTRIBUTION_PERCENT' THEN
177 p_column_value
178 := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).er_contribution_percent)
179 ,trim(to_char(0,'9')));
180 ELSIF p_column_name = 'EE_ANNUAL_SALARY_THRESHOLD' THEN
181
182 OPEN c_get_ee_age_threshold(g_pension_rec(l_pension_id).pension_type_id);
183 FETCH c_get_ee_age_threshold into l_ee_age_threshold;
184 IF (c_get_ee_age_threshold%FOUND
185 and l_ee_age_threshold = 'Y'
186 ) THEN
187
188 hr_utility.set_location(' l_ee_age_threshold is '||l_ee_age_threshold,30);
189
190 OPEN c_get_person_age;
191 FETCH c_get_person_age into l_person_year_of_birth;
192 IF (c_get_person_age%FOUND and l_person_year_of_birth IS NOT NULL) THEN
193
194 hr_utility.set_location(' l_person_year_of_birth is '||l_person_year_of_birth,35);
195
196 OPEN c_get_subcat(NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9'))));
197 FETCH c_get_subcat INTO l_subcat;
198 CLOSE c_get_subcat;
199
200 hr_utility.set_location(' l_subcat is '||l_subcat,40);
201
202 IF l_subcat IS NOT NULL THEN
203
204 BEGIN
205 p_column_value :=
206 hruserdt.get_table_value
207 (
208 p_bus_group_id => p_business_group_id
209 ,p_table_name => 'PQP_NL_ABP_EE_ANNUAL_SALARY_THRESHOLD'
210 ,p_col_name => l_subcat
211 ,p_row_value => l_person_year_of_birth
212 ,p_effective_date => p_date_earned
213 );
214
215 p_column_value := NVL(p_column_value,trim(to_char(0,'9')));
216
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 hr_utility.set_location('NO_DATA_FOUND for UDT : ', 90);
220
221 p_column_value := trim(to_char(0,'9'));
222
223 p_error_message := 'Pension Type '
224 || g_pension_rec(l_pension_id).pension_type_name
225 ||' has age dependent thresholds. '
226 ||' Please verify that this data exists'
227 ||' in the UDT.';
228
229 RETURN 3;
230
231 WHEN OTHERS THEN
232
233 IF (SQLCODE = -1422) THEN
234 hr_utility.set_location('MORE THAN ONE ROW FETCHED for UDT :', 90);
235 p_column_value := trim(to_char(0,'9'));
236 p_error_message := 'The table PQP_NL_ABP_EE_ANNUAL_SALARY_THRESHOLD has '
237 ||'overlapping rows for the age of the employee.';
238
239 END IF;
240 RETURN 3;
241
242 END;
243
244 hr_utility.set_location(' p_column_value is '||p_column_value,40);
245
246 END IF;-- subcat check
247
248 END IF;--c_get_person_age%FOUND
249 CLOSE c_get_person_age;
250 ELSE
251 p_column_value
252 := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).ee_annual_salary_threshold)
253 ,trim(to_char(0,'9')));
254
255
256 END IF; -- c_get_ee_age_threshold%FOUND
257
258 CLOSE c_get_ee_age_threshold;
259
260 hr_utility.set_location(' p_error_message is '||p_error_message,45);
261
262 ELSIF p_column_name = 'ER_ANNUAL_SALARY_THRESHOLD' THEN
263 -- p_column_value
264 -- := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).er_annual_salary_threshold)
265 -- ,trim(to_char(0,'9')));
266
267
268 OPEN c_get_er_age_threshold(g_pension_rec(l_pension_id).pension_type_id);
269 FETCH c_get_er_age_threshold into l_er_age_threshold;
270 IF (c_get_er_age_threshold%FOUND
271 and l_er_age_threshold = 'Y'
272 ) THEN
273
274 hr_utility.set_location(' l_er_age_threshold is '||l_er_age_threshold,30);
275
276 OPEN c_get_person_age;
277 FETCH c_get_person_age into l_person_year_of_birth;
278 IF (c_get_person_age%FOUND and l_person_year_of_birth IS NOT NULL) THEN
279
280 hr_utility.set_location(' l_person_year_of_birth is '||l_person_year_of_birth,35);
281
282 OPEN c_get_subcat(NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9'))));
283 FETCH c_get_subcat INTO l_subcat;
284 CLOSE c_get_subcat;
285
286 hr_utility.set_location(' l_subcat is '||l_subcat,40);
287
288 IF l_subcat IS NOT NULL THEN
289
290 BEGIN
291 p_column_value :=
292 hruserdt.get_table_value
293 (
294 p_bus_group_id => p_business_group_id
295 ,p_table_name => 'PQP_NL_ABP_ER_ANNUAL_SALARY_THRESHOLD'
296 ,p_col_name => l_subcat
297 ,p_row_value => l_person_year_of_birth
298 ,p_effective_date => p_date_earned
299 );
300
301 p_column_value := NVL(p_column_value,trim(to_char(0,'9')));
302
303 EXCEPTION
304 WHEN NO_DATA_FOUND THEN
305 hr_utility.set_location('NO_DATA_FOUND for UDT : ', 90);
306
307 p_column_value := trim(to_char(0,'9'));
308
309 -- fnd_message.set_name('PQP','PQP_230129_PEN_AGE_ANN_SAL_THR');
310 -- fnd_message.set_token('PT Name',g_pension_rec(l_pension_id).pension_type_name);
311 -- p_error_message := fnd_message.get();
312
313 p_error_message := 'Pension Type: '
314 || g_pension_rec(l_pension_id).pension_type_name
315 ||' has age dependent thresholds. '
316 ||' Please verify that this data exists '
317 ||' in the UDT.';
318
319
320 RETURN 3;
321
322 WHEN OTHERS THEN
323
324 IF (SQLCODE = -1422) THEN
325 hr_utility.set_location('MORE THAN ONE ROW FETCHED for UDT :', 90);
326 p_column_value := trim(to_char(0,'9'));
327 p_error_message := 'The table PQP_NL_ABP_ER_ANNUAL_SALARY_THRESHOLD has '
328 ||'overlapping rows for the age of the employee.';
329
330 END IF;
331 RETURN 3;
332
333 END;
334
335 hr_utility.set_location(' p_column_value is '||p_column_value,40);
336
337 END IF;-- subcat check
338
339 END IF;--c_get_person_age%FOUND
340 CLOSE c_get_person_age;
341 ELSE
342 p_column_value
343 := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).er_annual_salary_threshold)
344 ,trim(to_char(0,'9')));
345
346
347 END IF; -- c_get_er_age_threshold%FOUND
348
349 CLOSE c_get_er_age_threshold;
350
351 hr_utility.set_location(' p_error_message is '||p_error_message,45);
352
353 ELSIF p_column_name = 'ANNUAL_PREMIUM_AMOUNT' THEN
354 p_column_value
355 := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).annual_premium_amount)
356 ,trim(to_char(0,'9')));
357 ELSIF p_column_name = 'SPECIAL_PENSION_TYPE_CODE' THEN
358 p_column_value
359 := NVL(g_pension_rec(l_pension_id).special_pension_type_code
360 ,trim(to_char(0,'9')));
361 ELSIF p_column_name = 'PENSION_SUB_CATEGORY' THEN
362 p_column_value
363 := NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9')));
364 ELSIF p_column_name = 'PENSION_SUB_CAT_MEANING' THEN
365 OPEN c_get_subcat(NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9'))));
366 FETCH c_get_subcat INTO l_subcat;
367 IF c_get_subcat%FOUND THEN
368 CLOSE c_get_subcat;
369 p_column_value := l_subcat;
370 ELSE
371 CLOSE c_get_subcat;
372 p_column_value := trim(to_char(0,'9'));
373 END IF;
374 ELSIF p_column_name = 'PENSION_BASIS_CALC_MTHD' THEN
375 p_column_value
376 := NVL(g_pension_rec(l_pension_id).pension_basis_calc_method
377 ,trim(to_char(0,'9')));
378 ELSIF p_column_name = 'PENSION_SALARY_BALANCE' THEN
379 p_column_value
380 := NVL(to_char(g_pension_rec(l_pension_id).pension_salary_balance)
381 ,trim(to_char(0,'9')));
382 ELSIF p_column_name = 'RECURRING_BONUS_PERCENT' THEN
383 p_column_value
384 := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).recurring_bonus_percent)
385 ,trim(to_char(0,'9')));
386 ELSIF p_column_name = 'NON_RECURRING_BONUS_PERCENT' THEN
387 p_column_value
388 := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).non_recurring_bonus_percent)
389 ,trim(to_char(0,'9')));
390 ELSIF p_column_name = 'RECURRING_BONUS_BALANCE' THEN
391 p_column_value
392 := NVL(to_char(g_pension_rec(l_pension_id).recurring_bonus_balance)
393 ,trim(to_char(0,'9')));
394 ELSIF p_column_name = 'NON_RECURRING_BONUS_BALANCE' THEN
395 p_column_value
396 := NVL(to_char(g_pension_rec(l_pension_id).non_recurring_bonus_balance)
397 ,trim(to_char(0,'9')));
398 ELSIF p_column_name = 'PREV_YR_BONUS_INCLUDE' THEN
399 p_column_value
400 := NVL(g_pension_rec(l_pension_id).previous_year_bonus_included
401 ,trim(to_char(0,'9')));
402 ELSIF p_column_name = 'RECURRING_BONUS_PERIOD' THEN
403 p_column_value
404 := NVL(g_pension_rec(l_pension_id).recurring_bonus_period,trim(to_char(0,'9')));
405 ELSIF p_column_name = 'NON_RECURRING_BONUS_PERIOD' THEN
406 p_column_value
407 := NVL(g_pension_rec(l_pension_id).non_recurring_bonus_period
408 ,trim(to_char(0,'9')));
409 ELSE
410 p_error_message := 'Error occured while fetching values for '
411 ||'Pension Type : '||g_pension_rec(l_pension_id).pension_type_name
412 ||' Column : '||p_column_name||' is invalid.';
413 RETURN 1;
414 END IF;
415
416 hr_utility.set_location('..Column Name :'||p_column_name , 30);
417 hr_utility.set_location('..Column Value :'||p_column_value, 35);
418 hr_utility.set_location('..p_error_message :'||p_error_message, 40);
419 hr_utility.set_location('Leaving : '||l_proc_name, 80);
420
421 RETURN 0;
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 hr_utility.set_location('Error when others : '||l_proc_name, 90);
426 hr_utility.set_location('Leaving : '||l_proc_name, 95);
427 p_error_message := 'Error occured while fetching values for Pension Type';
428 RETURN 1;
429
430 END get_pension_type_details;
431
432 -- ----------------------------------------------------------------------------
433 -- |-------------------------< prorate_amount >-------------------------------|
434 -- ----------------------------------------------------------------------------
435 --
436 function prorate_amount
437 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
438 ,p_date_earned in date
439 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
440 ,p_amount in number
441 ,p_payroll_period in varchar2
442 ,p_work_pattern in varchar2
443 ,p_conversion_rule in varchar2
444 ,p_prorated_amount out nocopy number
445 ,p_error_message out nocopy varchar2
446 ,p_payroll_period_prorate in varchar2
447 ,p_override_pension_days in number default -9999
448 ) return NUMBER IS
449
450 -- In the first phase of Dutch pensions , only average working days
451 -- calculation will be supported. Once Actual working days ( equal to
452 -- SI days ) is enabled, the same function will be called here .
453
454 CURSOR c_get_global ( c_global_name IN VARCHAR2
455 ,c_effective_date IN DATE ) IS
456 SELECT fnd_number.canonical_to_number(global_value)
457 FROM ff_globals_f
458 WHERE global_name = c_global_name
459 AND trunc(c_effective_date) BETWEEN effective_start_date AND effective_end_date
460 AND legislation_code = 'NL';
461
462 CURSOR c_get_work_pattern(c_assignment_id
463 IN per_all_assignments_f.assignment_id%TYPE,
464 c_effective_date IN DATE) IS
465 SELECT work_pattern
466 FROM pqp_assignment_attributes_f
467 WHERE assignment_id = c_assignment_id
468 AND trunc(c_effective_date) BETWEEN effective_start_date AND effective_end_date;
469
470 CURSOR c_get_start_end_date(c_assignment_id in NUMBER,
471 c_effective_date IN DATE
472 ) IS
473 SELECT ptp.start_date,ptp.end_date
474 FROM per_all_assignments_f pasf,per_time_periods ptp
475 WHERE pasf.payroll_id = ptp.payroll_id
476 AND pasf.assignment_id = c_assignment_id
477 AND trunc(c_effective_date) BETWEEN ptp.start_date AND ptp.end_date;
478
479 --bug 3115132
480 CURSOR c_get_assign_start_date(c_assign_id in NUMBER,
481 c_period_start_dt IN DATE,
482 c_period_end_dt IN DATE
483 ) IS
484 SELECT min(asg.effective_start_date) , max(asg.effective_end_date)
485 FROM per_assignments_f asg,per_assignment_status_types past
486 WHERE asg.assignment_status_type_id = past.assignment_status_type_id
487 AND past.per_system_status = 'ACTIVE_ASSIGN'
488 AND asg.effective_start_date <= c_period_end_dt
489 AND nvl(asg.effective_end_date, c_period_end_dt) >= c_period_start_dt
490 AND asg.assignment_id = c_assign_id;
491
492 CURSOR c_get_asg_end_date(c_assign_id in NUMBER,
493 c_period_start_dt IN DATE
494 ) IS
495 SELECT
496 decode(asg.EFFECTIVE_END_DATE,to_date('31-12-4712','dd-mm-yyyy'),null,asg.EFFECTIVE_END_DATE)
497 FROM per_assignments_f asg,per_assignment_status_types past
498 WHERE asg.assignment_status_type_id = past.assignment_status_type_id
499 AND past.per_system_status = 'ACTIVE_ASSIGN'
500 AND trunc(c_period_start_dt) = asg.effective_start_date
501 AND asg.assignment_id = c_assign_id;
502
503
504 CURSOR c_term_date_decode(c_term_date IN DATE) IS
505 SELECT decode(c_term_date,to_date('31-12-4712','dd-mm-yyyy'),null,c_term_date)
506 FROM DUAL;
507
508
509 CURSOR
510 c_no_of_days(c_end_date IN DATE, c_start_date IN DATE)
511 IS
512 SELECT (c_end_date - c_start_date + 1)
513 FROM DUAL;
514
515 CURSOR
516 c_get_average_days_per_month(c_assignment_id IN NUMBER,
517 c_effective_date IN DATE
518 ) IS
519 SELECT hoi.org_information5
520 FROM per_all_assignments_f paa,hr_organization_information hoi
521 WHERE paa.organization_id = hoi.organization_id
522 AND hoi.org_information_context='NL_ORG_INFORMATION'
523 AND paa.assignment_id = c_assignment_id
524 AND trunc(c_effective_date) between effective_start_date and effective_end_date;
525
526 CURSOR
527 c_get_max_si_values(c_assignment_id IN NUMBER,c_effective_date IN DATE) IS
528 SELECT SEGMENT26,segment27
529 FROM hr_soft_coding_keyflex hr_keyflex,per_assignments_f ASSIGN
530 WHERE ASSIGN.assignment_id = c_assignment_id
531 AND hr_keyflex.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
532 AND hr_keyflex.enabled_flag = 'Y'
533 AND trunc(c_effective_date) BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date;
534
535 CURSOR
536 c_get_avg_si_assignment(c_assignment_id IN NUMBER) IS
537 SELECT aei_information1,aei_information2
538 FROM per_assignment_extra_info
539 WHERE information_type= 'NL_PAI'
540 AND assignment_id = c_assignment_id;
541
542 Cursor c_get_periods_per_yr(c_period_type in VARCHAR2) IS
543 Select number_per_fiscal_year
544 from per_time_period_types
545 where period_type = c_period_type;
546
547
548 l_average_days_divisor NUMBER;
549 l_average_days_multiplier NUMBER;
550 l_prorated_amount NUMBER;
551 l_avg_days_in_year NUMBER;
552 l_ret_val NUMBER;
553 l_payroll_period per_time_periods.period_name%TYPE;
554 l_work_pattern VARCHAR2(200);
555 l_pay_start_dt DATE;
556 l_pay_end_dt DATE;
557 l_term_date DATE;
558 l_average_si_days NUMBER;
559 l_payroll_days NUMBER; -- average days in a payroll run.
560 l_ass_start_dt DATE;
561 l_si_factor NUMBER;
562 l_non_si_days NUMBER;
563 l_average_ws_si_days NUMBER;
564 l_working_work_pattern_days NUMBER;
565 l_total_days NUMBER; -- total days in a payroll run.
566 l_actual_work_days NUMBER := -99;
567 l_total_work_pattern_days NUMBER;
568 l_days NUMBER;
569 l_average_period_days NUMBER;
570 l_debug BOOLEAN;
571 l_real_si_days NUMBER;
572 l_max_si_days NUMBER;
573 l_error_code VARCHAR2(100);
574 l_error_message VARCHAR2(500);
575 l_max_si_method VARCHAR2(100);
576 l_overridden_realsi_assignment NUMBER;
577 l_overridden_realsi_element NUMBER;
578 bFlagAvgDays boolean;
579 l_override_method VARCHAR2(100);
580 l_overridden_avgsi_assignment VARCHAR2(100);
581 l_pay_prorate_period VARCHAR2(100);
582 l_pay_period VARCHAR2(100);
583 l_tmp_ret_val NUMBER := 0;
584 l_next_term_date DATE;
585 l_periods_per_yr NUMBER := 1;
586 l_prorate_periods_per_yr NUMBER := 1;
587
588 BEGIN
589
590 OPEN c_get_global ('NL_SI_AVERAGE_DAYS_YEARLY'
591 ,p_date_earned);
592 FETCH c_get_global INTO l_avg_days_in_year;
593
594 CLOSE c_get_global;
595
596 IF p_payroll_period = 'NOT ENTERED' THEN
597 l_payroll_period := p_payroll_period_prorate;
598 ELSE
599 l_payroll_period := p_payroll_period;
600 END IF;
601
602 l_debug := hr_utility.debug_enabled;
603
604
605 IF l_debug THEN
606 pqp_utilities.debug(' p_business_group_id is ' || p_business_group_id );
607 pqp_utilities.debug(' p_date_earned is ' || p_date_earned );
608 pqp_utilities.debug(' p_assignment_id is ' || p_assignment_id );
609 pqp_utilities.debug(' p_amount is ' || p_amount );
610 pqp_utilities.debug(' p_payroll_period is ' || p_payroll_period );
611 pqp_utilities.debug(' l_payroll_period is ' || l_payroll_period );
612 pqp_utilities.debug(' p_work_pattern is ' || p_work_pattern );
613 pqp_utilities.debug(' p_conversion_rule is ' || p_conversion_rule );
614 pqp_utilities.debug(' p_prorated_amount is ' || p_prorated_amount );
615 pqp_utilities.debug(' p_error_message is ' || p_error_message );
616 pqp_utilities.debug(' p_payroll_period_prorate is ' || p_payroll_period_prorate);
617 END IF;
618
619
620
621
622 IF ( l_payroll_period LIKE '%Calendar Month'
623 OR l_payroll_period = 'CM') THEN
624
625 OPEN c_get_global ('NL_SI_AVERAGE_DAYS_MONTHLY'
626 ,p_date_earned);
627
628 FETCH c_get_global INTO l_average_days_divisor;
629
630 CLOSE c_get_global;
631
632 OPEN c_get_periods_per_yr('Calendar Month');
633
634 FETCH c_get_periods_per_yr INTO l_periods_per_yr;
635
636 CLOSE c_get_periods_per_yr;
637
638 l_ret_val := 0;
639
640 ELSIF (l_payroll_period LIKE '%Lunar Month'
641 OR l_payroll_period = 'LM') THEN
642
643 OPEN c_get_global ('NL_SI_AVERAGE_DAYS_4WEEKLY'
644 ,p_date_earned);
645
646 FETCH c_get_global INTO l_average_days_divisor;
647
648 CLOSE c_get_global;
649
650 OPEN c_get_periods_per_yr('Lunar Month');
651
652 FETCH c_get_periods_per_yr INTO l_periods_per_yr;
653
654 CLOSE c_get_periods_per_yr;
655
656 l_ret_val := 0;
657
658 ELSIF (l_payroll_period LIKE '%Quarter'
659 OR l_payroll_period = 'Q') THEN
660
661 OPEN c_get_global ('NL_SI_AVERAGE_DAYS_QUARTERLY'
662 ,p_date_earned);
663
664 FETCH c_get_global INTO l_average_days_divisor;
665
666 CLOSE c_get_global;
667
668 OPEN c_get_periods_per_yr('Quarter');
669
670 FETCH c_get_periods_per_yr INTO l_periods_per_yr;
671
672 CLOSE c_get_periods_per_yr;
673
674 l_ret_val := 0;
675
676 ELSIF ( l_payroll_period LIKE '%Week'
677 OR l_payroll_period = 'W') THEN
678
679 OPEN c_get_global ('NL_SI_AVERAGE_DAYS_WEEKLY'
680 ,p_date_earned);
681
682 FETCH c_get_global INTO l_average_days_divisor;
683
684 CLOSE c_get_global;
685
686 OPEN c_get_periods_per_yr('Week');
687
688 FETCH c_get_periods_per_yr INTO l_periods_per_yr;
689
690 CLOSE c_get_periods_per_yr;
691
692 l_ret_val := 0;
693
694 ELSIF ( l_payroll_period LIKE '%Year'
695 OR l_payroll_period = 'Y') THEN
696
697 l_average_days_divisor := l_avg_days_in_year;
698
699 OPEN c_get_periods_per_yr('Year');
700
701 FETCH c_get_periods_per_yr INTO l_periods_per_yr;
702
703 CLOSE c_get_periods_per_yr;
704
705 l_ret_val := 0;
706
707 ELSE
708 l_ret_val := 1;
709 p_error_message := 'Error : Invalid value for Payroll Period';
710
711 END IF;
712
713 IF l_ret_val = 0 THEN
714 IF (p_payroll_period_prorate LIKE '%Calendar Month'
715 OR p_payroll_period_prorate = 'CM') THEN
716
717 OPEN c_get_global ('NL_SI_AVERAGE_DAYS_MONTHLY'
718 ,p_date_earned);
719
720 FETCH c_get_global INTO l_average_days_multiplier;
721
722 CLOSE c_get_global;
723
724 OPEN c_get_periods_per_yr('Calendar Month');
725
726 FETCH c_get_periods_per_yr INTO l_prorate_periods_per_yr;
727
728 CLOSE c_get_periods_per_yr;
729
730 l_ret_val := 0;
731
732 ELSIF (p_payroll_period_prorate LIKE '%Lunar Month'
733 OR p_payroll_period_prorate = 'LM') THEN
734
735 OPEN c_get_global ('NL_SI_AVERAGE_DAYS_4WEEKLY'
736 ,p_date_earned);
737
738 FETCH c_get_global INTO l_average_days_multiplier;
739
740 CLOSE c_get_global;
741
742 OPEN c_get_periods_per_yr('Lunar Month');
743
744 FETCH c_get_periods_per_yr INTO l_prorate_periods_per_yr;
745
746 CLOSE c_get_periods_per_yr;
747
748 l_ret_val := 0;
749
750 ELSIF (p_payroll_period_prorate LIKE '%Quarter'
751 OR p_payroll_period_prorate = 'Q') THEN
752
753 OPEN c_get_global ('NL_SI_AVERAGE_DAYS_QUARTERLY'
754 ,p_date_earned);
755
756 FETCH c_get_global INTO l_average_days_multiplier;
757
758 CLOSE c_get_global;
759
760 OPEN c_get_periods_per_yr('Quarter');
761
762 FETCH c_get_periods_per_yr INTO l_prorate_periods_per_yr;
763
764 CLOSE c_get_periods_per_yr;
765
766 l_ret_val := 0;
767
768 ELSIF ( p_payroll_period_prorate LIKE '%Week'
769 OR p_payroll_period_prorate = 'W') THEN
770
771 OPEN c_get_global ('NL_SI_AVERAGE_DAYS_WEEKLY'
772 ,p_date_earned);
773
774 FETCH c_get_global INTO l_average_days_multiplier;
775
776 CLOSE c_get_global;
777
778 OPEN c_get_periods_per_yr('Week');
779
780 FETCH c_get_periods_per_yr INTO l_prorate_periods_per_yr;
781
782 CLOSE c_get_periods_per_yr;
783
784 l_ret_val := 0;
785
786 ELSIF ( p_payroll_period_prorate LIKE '%Year'
787 OR p_payroll_period_prorate = 'Y') THEN
788
789 l_average_days_multiplier := l_avg_days_in_year;
790
791 OPEN c_get_periods_per_yr('Year');
792
793 FETCH c_get_periods_per_yr INTO l_prorate_periods_per_yr;
794
795 CLOSE c_get_periods_per_yr;
796
797 l_ret_val := 0;
798
799 ELSE
800 l_ret_val := 1;
801 p_error_message := 'Error : Invalid value for Payroll Period Prorate';
802
803 END IF;
804 END IF;
805
806 --check if the pension days values have been overriden at element entry level
807 IF (p_override_pension_days <> -9999) THEN
808
809 --real si days value should be a whole number
810 IF p_conversion_rule = '2' THEN
811 l_average_days_multiplier := ROUND(p_override_pension_days,0);
812
813 IF (l_average_days_multiplier <> p_override_pension_days) THEN
814 l_tmp_ret_val := 1;
815 END IF;
816
817 ELSIF p_conversion_rule <> '3' THEN
818 l_average_days_multiplier := p_override_pension_days;
819 ELSE
820 l_average_days_multiplier := l_periods_per_yr;
821 l_average_days_divisor := l_prorate_periods_per_yr;
822 END IF;
823
824 ELSE
825
826 IF (l_payroll_period LIKE '%Calendar Month') THEN
827 l_pay_period := 'CM' ;
828
829 ELSIF (l_payroll_period LIKE '%Lunar Month') THEN
830 l_pay_period := 'LM';
831
832 ELSIF (l_payroll_period LIKE '%Quarter') THEN
833 l_pay_period := 'Q';
834
835 ELSIF (l_payroll_period LIKE '%Week') THEN
836 l_pay_period := 'W';
837
838 ELSIF (l_payroll_period LIKE '%Year') THEN
839 l_pay_period := 'Y';
840
841 ELSE
842 l_pay_period := l_payroll_period;
843
844 END IF;
845
846
847 IF (p_payroll_period_prorate LIKE '%Calendar Month') THEN
848 l_pay_prorate_period := 'CM';
849
850 ELSIF (p_payroll_period_prorate LIKE '%Lunar Month') THEN
851 l_pay_prorate_period := 'LM';
852
853 ELSIF (p_payroll_period_prorate LIKE '%Quarter') THEN
854 l_pay_prorate_period := 'Q';
855
856 ELSIF (p_payroll_period_prorate LIKE '%Week') THEN
857 l_pay_prorate_period := 'W';
858
859 ELSIF (p_payroll_period_prorate LIKE '%Year') THEN
860 l_pay_prorate_period := 'Y';
861
862 ELSE
863 l_pay_prorate_period := p_payroll_period_prorate;
864
865 END IF;
866
867 IF l_debug THEN
868 pqp_utilities.debug(' l_pay_prorate_period '|| l_pay_prorate_period, 4);
869 pqp_utilities.debug(' l_pay_period ' || l_pay_period, 5);
870 pqp_utilities.debug(' l_ret_val ' || l_ret_val, 5);
871 END IF;
872
873 -- IF the pay period is not the same as the proration period
874 -- then no calculation to be done.
875 -- IF ( l_pay_period <> l_pay_prorate_period) THEN
876
877
878 -- get payroll start date and payroll end date.
879 OPEN c_get_start_end_date(p_assignment_id,p_date_earned);
880 FETCH c_get_start_end_date into l_pay_start_dt,l_pay_end_dt;
881 IF (c_get_start_end_date%FOUND
882 AND l_pay_start_dt IS NOT NULL
883 AND l_pay_end_dt IS NOT NULL)THEN
884 CLOSE c_get_start_end_date;
885
886 IF l_debug THEN
887 pqp_utilities.debug(' l_pay_start_dt is '|| l_pay_start_dt , 60);
888 pqp_utilities.debug(' l_pay_end_dt is '|| l_pay_end_dt , 70);
889 END IF;
890 END IF;
891 -- bug 3122357
892 -- get average days in the payroll
893 OPEN c_no_of_days(l_pay_end_dt,l_pay_start_dt);
894 FETCH c_no_of_days into l_days;
895 IF (c_no_of_days%FOUND
896 AND l_days IS NOT NULL)THEN
897 CLOSE c_no_of_days;
898 l_payroll_days := l_days;
899 ELSE
900 CLOSE c_no_of_days;
901 END IF;
902
903 IF l_debug THEN
904 pqp_utilities.debug(' l_payroll_days is '|| l_payroll_days , 80);
905 END IF;
906
907 -- 0 corresponds to Average working days
908 IF p_conversion_rule = '0' THEN
909
910 IF (l_ret_val = 0) THEN
911
912 -- check IF the assignment has started in this payroll period.
913 OPEN c_get_assign_start_date(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
914 FETCH c_get_assign_start_date into l_ass_start_dt,l_term_date;
915 IF (c_get_assign_start_date%FOUND
916 AND l_ass_start_dt IS NOT NULL)THEN
917 CLOSE c_get_assign_start_date;
918 IF l_debug THEN
919 pqp_utilities.debug(' l_ass_start_dt is '|| l_ass_start_dt , 91);
920 pqp_utilities.debug(' l_term_date is '|| l_term_date , 91);
921 END IF;
922 ELSE
923 CLOSE c_get_assign_start_date;
924 p_prorated_amount := 0;
925 RETURN 0;
926 --p_error_message := 'Error: Unable to find the assignment start date for the person ';
927 END IF; -- assignment start date found
928
929 OPEN c_term_date_decode(l_term_date);
930 FETCH c_term_date_decode into l_term_date;
931 CLOSE c_term_date_decode;
932
933
934
935 IF (l_term_date IS NOT NULL) THEN
936 -- check if there is a date tracked row with active assignment from the start of the
937 -- next pay run.
938
939 OPEN c_get_asg_end_date(p_assignment_id,l_term_date+1);
940 FETCH c_get_asg_end_date into l_next_term_date;
941 IF (c_get_asg_end_date%FOUND)THEN
942 CLOSE c_get_asg_end_date;
943 l_term_date := l_next_term_date;
944 IF l_debug THEN
945 pqp_utilities.debug(' l_next_term_date is '|| l_next_term_date , 92);
946 END IF;
947 ELSE
948 CLOSE c_get_asg_end_date;
949 END IF; -- assignment end date found
950 END IF;-- term date is not null
951
952 IF l_debug THEN
953 pqp_utilities.debug(' l_term_date is '|| l_term_date , 91);
954 END IF;
955
956 /* -- get termination date
957 OPEN c_get_ass_term_date(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
958 FETCH c_get_ass_term_date into l_term_date;
959 IF (c_get_ass_term_date%FOUND AND l_term_date IS NOT NULL)THEN
960 CLOSE c_get_ass_term_date;
961 IF l_debug THEN
962 pqp_utilities.debug(' l_term_date is '|| l_term_date , 101);
963 END IF;
964 ELSE
965 CLOSE c_get_ass_term_date;
966 END IF;-- get termination dt
967 */
968
969 IF(l_ass_start_dt is not null AND ((l_ass_start_dt > l_pay_start_dt) OR (l_ass_start_dt = l_pay_start_dt))) THEN
970 IF (l_term_date is not null AND ((l_term_date < l_pay_end_dt) OR (l_term_date = l_pay_start_dt)))THEN
971 OPEN c_no_of_days(l_term_date,l_ass_start_dt);
972 FETCH c_no_of_days into l_days;
973 IF (c_no_of_days%FOUND AND l_days IS NOT NULL)THEN
974 CLOSE c_no_of_days;
975 l_actual_work_days := l_days;
976 pqp_utilities.debug(' l_actual_work_days is '|| l_actual_work_days , 105);
977 ELSE
978 CLOSE c_no_of_days;
979 END IF;
980 ELSE
981 OPEN c_no_of_days(l_pay_end_dt,l_ass_start_dt);
982 FETCH c_no_of_days into l_days;
983 IF (c_no_of_days%FOUND AND l_days IS NOT NULL)THEN
984 CLOSE c_no_of_days;
985 l_actual_work_days := l_days;
986 pqp_utilities.debug(' l_actual_work_days is '|| l_actual_work_days , 106);
987 pqp_utilities.debug(' l_days is '|| l_days , 107);
988 ELSE
989 CLOSE c_no_of_days;
990 END IF;
991 END IF;
992
993 ELSE
994 IF (l_term_date is not null AND ((l_term_date < l_pay_end_dt) OR (l_term_date = l_pay_end_dt))) THEN
995 OPEN c_no_of_days(l_term_date,l_pay_start_dt);
996 FETCH c_no_of_days into l_days;
997 IF (c_no_of_days%FOUND AND l_days IS NOT NULL)THEN
998 CLOSE c_no_of_days;
999 l_actual_work_days := l_days;
1000 ELSE
1001 CLOSE c_no_of_days;
1002 END IF;
1003 END IF; --term date check
1004
1005 END IF;-- assgn_start_dt check
1006
1007
1008 IF(l_ass_start_dt IS NOT NULL AND l_ass_start_dt > l_pay_start_dt) THEN
1009 l_pay_start_dt := l_ass_start_dt;
1010 END IF;
1011
1012 IF(l_term_date IS NOT NULL AND l_term_date < l_pay_end_dt) THEN
1013 l_pay_end_dt := l_term_date;
1014 END IF;
1015
1016 IF l_debug THEN
1017 pqp_utilities.debug(' l_average_days_divisor is '|| l_average_days_divisor , 151);
1018 pqp_utilities.debug(' l_payroll_days is '|| l_payroll_days , 161);
1019 pqp_utilities.debug(' l_actual_work_days is '|| l_actual_work_days , 171);
1020 pqp_utilities.debug(' l_pay_start_dt is '|| l_pay_start_dt , 173);
1021 pqp_utilities.debug(' l_pay_end_dt is '|| l_pay_end_dt , 176);
1022 END IF;
1023
1024 -- assignment level overrides
1025 OPEN c_get_avg_si_assignment(p_assignment_id);
1026 FETCH c_get_avg_si_assignment into l_override_method,l_overridden_avgsi_assignment;
1027 IF (c_get_avg_si_assignment%FOUND
1028 AND l_overridden_avgsi_assignment IS NOT NULL
1029 AND l_override_method IS NOT NULL)THEN
1030 CLOSE c_get_avg_si_assignment;
1031
1032 IF l_debug THEN
1033 pqp_utilities.debug(' c_get_avg_si_assignment found ', 4);
1034 pqp_utilities.debug(' l_override_method ' || l_override_method, 5);
1035 pqp_utilities.debug(' l_overridden_avgsi_assignment ' || l_overridden_avgsi_assignment ,6);
1036 END IF;
1037
1038 IF (l_override_method = 0) THEN --'Manual Entry'
1039 l_average_days_multiplier := l_overridden_avgsi_assignment;
1040 ELSIF (l_override_method = 1 ) THEN --'Percentage of Average Days'
1041 l_average_days_multiplier :=
1042 l_average_days_multiplier * l_overridden_avgsi_assignment/100;
1043 -- bug 3122357.
1044 --prorate the average_si_days value for actual days worked
1045 IF (l_payroll_days <> 0 and l_actual_work_days <> -99) THEN
1046 l_average_days_multiplier := (l_average_days_multiplier/l_payroll_days * l_actual_work_days);
1047 END IF;
1048
1049 ELSIF (l_override_method = 2 ) THEN -- 'Percentage of Maximum SI Days'
1050
1051 --bug 3115132
1052 --get the method for maximum si days from social insurance tab
1053 OPEN c_get_max_si_values(p_assignment_id,p_date_earned);
1054 FETCH c_get_max_si_values into l_max_si_method,l_overridden_realsi_assignment;
1055 IF (c_get_max_si_values%FOUND AND l_max_si_method IS NOT NULL )THEN
1056 CLOSE c_get_max_si_values;
1057
1058 IF (l_max_si_method = 0 ) THEN -- 1 indicates 'Payroll Period' 0 indicates Weeks worked.
1059 --first check if a work pattern is attached to the ASG, if not
1060 -- return a warning condition and calculate the deduction amount to be 0
1061 OPEN c_get_work_pattern(p_assignment_id,p_date_earned);
1062 FETCH c_get_work_pattern INTO l_work_pattern;
1063 IF c_get_work_pattern%FOUND AND l_work_pattern IS NOT NULL THEN
1064 CLOSE c_get_work_pattern;
1065 ELSE
1066 CLOSE c_get_work_pattern;
1067 l_ret_val := 3;
1068 END IF;
1069 l_max_si_days :=
1070 pay_nl_si_pkg.Get_Max_SI_Days(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1071 ELSE
1072 l_max_si_days :=
1073 pay_nl_si_pkg.Get_Week_Days(l_pay_start_dt,l_pay_end_dt);
1074 END IF;
1075 l_average_days_multiplier :=
1076 l_max_si_days * l_overridden_avgsi_assignment/100;
1077
1078 IF l_debug THEN
1079 pqp_utilities.debug(' l_max_si_method ' || l_max_si_method, 5);
1080 pqp_utilities.debug(' l_max_si_days ' || l_max_si_days ,6);
1081 END IF;
1082 END IF;
1083
1084 END IF;
1085
1086 IF l_debug THEN
1087 pqp_utilities.debug(' l_average_days_multiplier found '|| l_average_days_multiplier,7);
1088 END IF;
1089
1090 ELSIF (c_get_avg_si_assignment%FOUND
1091 AND
1092 (l_overridden_avgsi_assignment IS NULL AND l_override_method IS NOT NULL))
1093 THEN
1094
1095 l_ret_val := 1;
1096 p_error_message := 'Error : Overriding value has not been entered in Average Days Extra Information.';
1097
1098 ELSIF (c_get_avg_si_assignment%FOUND
1099 AND
1100 (l_overridden_avgsi_assignment IS NOT NULL AND l_override_method IS NULL))
1101 THEN
1102
1103 l_ret_val := 1;
1104 p_error_message := 'Error : Overriding Method has not been entered in Average Days Extra Information.';
1105 ELSE
1106 CLOSE c_get_avg_si_assignment;
1107
1108 -- bug 3122357.
1109 --prorate average days to the days worked in the pay period.
1110 IF (l_payroll_days <> 0 and l_actual_work_days <> -99) THEN
1111 l_average_days_multiplier := (l_average_days_multiplier/l_payroll_days * l_actual_work_days);
1112 END IF;
1113
1114 IF l_debug THEN
1115 pqp_utilities.debug(' c_get_avg_si_assignment not found ', 4);
1116 END IF;
1117 END IF;-- check for override at assignment level.
1118
1119
1120 END IF;-- ret val
1121
1122 ELSIF p_conversion_rule = '1' THEN
1123
1124 IF (l_ret_val = 0) THEN
1125
1126 -- get payroll start date and payroll end date.
1127 OPEN c_get_start_end_date(p_assignment_id,p_date_earned);
1128 FETCH c_get_start_end_date into l_pay_start_dt,l_pay_end_dt;
1129 IF (c_get_start_end_date%FOUND
1130 AND l_pay_start_dt IS NOT NULL
1131 AND l_pay_end_dt IS NOT NULL)THEN
1132 CLOSE c_get_start_end_date;
1133
1134 IF l_debug THEN
1135 pqp_utilities.debug(' l_pay_start_dt is '|| l_pay_start_dt , 60);
1136 pqp_utilities.debug(' l_pay_end_dt is '|| l_pay_end_dt , 70);
1137 END IF;
1138
1139
1140 -- get average days in the payroll
1141 OPEN c_no_of_days(l_pay_end_dt,l_pay_start_dt);
1142 FETCH c_no_of_days into l_days;
1143 IF (c_no_of_days%FOUND
1144 AND l_days IS NOT NULL)THEN
1145 CLOSE c_no_of_days;
1146 l_payroll_days := l_days;
1147 ELSE
1148 CLOSE c_no_of_days;
1149 END IF;
1150
1151 IF l_debug THEN
1152 pqp_utilities.debug(' l_payroll_days is '|| l_payroll_days , 80);
1153 END IF;
1154
1155 -- check if the assignment has started in this payroll period.
1156 OPEN c_get_assign_start_date(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1157 FETCH c_get_assign_start_date into l_ass_start_dt,l_term_date;
1158 IF (c_get_assign_start_date%FOUND
1159 AND l_ass_start_dt IS NOT NULL)THEN
1160 CLOSE c_get_assign_start_date;
1161 IF l_debug THEN
1162 pqp_utilities.debug(' l_ass_start_dt is '|| l_ass_start_dt , 92);
1163 END IF;
1164 ELSE
1165 CLOSE c_get_assign_start_date;
1166 p_prorated_amount := 0;
1167 RETURN 0;
1168 --p_error_message := 'Error: Unable to find the assignment start date for the person ';
1169 END IF; -- assignment start date found
1170
1171 OPEN c_term_date_decode(l_term_date);
1172 FETCH c_term_date_decode into l_term_date;
1173 CLOSE c_term_date_decode;
1174
1175 IF (l_term_date IS NOT NULL) THEN
1176 -- check if there is a date tracked row with active assignment from the start of the
1177 -- next pay run.
1178
1179 OPEN c_get_asg_end_date(p_assignment_id,l_term_date+1);
1180 FETCH c_get_asg_end_date into l_next_term_date;
1181 IF (c_get_asg_end_date%FOUND)THEN
1182 CLOSE c_get_asg_end_date;
1183 l_term_date := l_next_term_date;
1184 IF l_debug THEN
1185 pqp_utilities.debug(' l_next_term_date is '|| l_next_term_date , 92);
1186 END IF;
1187 ELSE
1188 CLOSE c_get_asg_end_date;
1189 END IF; -- assignment end date found
1190 END IF;-- term date is not null
1191
1192 IF l_debug THEN
1193 pqp_utilities.debug(' l_term_date is '|| l_term_date , 91);
1194 END IF;
1195
1196 /* -- get termination date
1197 OPEN c_get_ass_term_date(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1198 FETCH c_get_ass_term_date into l_term_date;
1199 IF (c_get_ass_term_date%FOUND AND l_term_date IS NOT NULL)THEN
1200 CLOSE c_get_ass_term_date;
1201 IF l_debug THEN
1202 pqp_utilities.debug(' l_term_date is '|| l_term_date , 102);
1203 END IF;
1204 ELSE
1205 CLOSE c_get_ass_term_date;
1206 END IF;-- get termination dt
1207 */
1208
1209
1210
1211
1212 IF(l_ass_start_dt is not null AND ((l_ass_start_dt > l_pay_start_dt) OR (l_ass_start_dt = l_pay_start_dt))) THEN
1213 IF (l_term_date is not null AND ((l_term_date < l_pay_end_dt) OR (l_term_date = l_pay_start_dt)))THEN
1214 OPEN c_no_of_days(l_term_date,l_ass_start_dt);
1215 FETCH c_no_of_days into l_days;
1216 IF (c_no_of_days%FOUND AND l_days IS NOT NULL)THEN
1217 CLOSE c_no_of_days;
1218 l_actual_work_days := l_days;
1219 pqp_utilities.debug(' l_actual_work_days 1 is '|| l_actual_work_days , 105);
1220 ELSE
1221 CLOSE c_no_of_days;
1222 END IF;
1223 ELSE
1224 OPEN c_no_of_days(l_pay_end_dt,l_ass_start_dt);
1225 FETCH c_no_of_days into l_days;
1226 IF (c_no_of_days%FOUND AND l_days IS NOT NULL)THEN
1227 CLOSE c_no_of_days;
1228 l_actual_work_days := l_days;
1229 pqp_utilities.debug(' l_actual_work_days 0 is '|| l_actual_work_days , 105);
1230 pqp_utilities.debug(' l_days 0 is '|| l_days , 105);
1231 ELSE
1232 CLOSE c_no_of_days;
1233 END IF;
1234 END IF;
1235
1236 ELSE
1237 IF (l_term_date is not null AND ((l_term_date < l_pay_end_dt) OR (l_term_date = l_pay_end_dt))) THEN
1238 OPEN c_no_of_days(l_term_date,l_pay_start_dt);
1239 FETCH c_no_of_days into l_days;
1240 IF (c_no_of_days%FOUND AND l_days IS NOT NULL)THEN
1241 CLOSE c_no_of_days;
1242 l_actual_work_days := l_days;
1243 ELSE
1244 CLOSE c_no_of_days;
1245 END IF;
1246 END IF; --term date check
1247
1248 END IF;-- assgn_start_dt check
1249
1250
1251 IF(l_ass_start_dt IS NOT NULL AND l_ass_start_dt > l_pay_start_dt) THEN
1252 l_pay_start_dt := l_ass_start_dt;
1253 END IF;
1254
1255 IF(l_term_date IS NOT NULL AND l_term_date < l_pay_end_dt) THEN
1256 l_pay_end_dt := l_term_date;
1257 END IF;
1258
1259 IF l_debug THEN
1260 pqp_utilities.debug(' l_average_days_divisor is '|| l_average_days_divisor , 150);
1261 pqp_utilities.debug(' l_payroll_days is '|| l_payroll_days , 160);
1262 pqp_utilities.debug(' l_actual_work_days is '|| l_actual_work_days , 170);
1263 pqp_utilities.debug(' l_pay_start_dt is '|| l_pay_start_dt , 172);
1264 pqp_utilities.debug(' l_pay_end_dt is '|| l_pay_end_dt , 175);
1265 END IF;
1266
1267 -- assignment level overrides
1268 OPEN c_get_avg_si_assignment(p_assignment_id);
1269 FETCH c_get_avg_si_assignment into l_override_method,l_overridden_avgsi_assignment;
1270 IF (c_get_avg_si_assignment%FOUND
1271 AND l_overridden_avgsi_assignment IS NOT NULL
1272 AND l_override_method IS NOT NULL)THEN
1273 CLOSE c_get_avg_si_assignment;
1274
1275 IF l_debug THEN
1276 pqp_utilities.debug(' c_get_avg_si_assignment found ', 4);
1277 pqp_utilities.debug(' l_override_method ' || l_override_method, 5);
1278 pqp_utilities.debug(' l_overridden_avgsi_assignment ' || l_overridden_avgsi_assignment ,6);
1279 END IF;
1280
1281 IF (l_override_method = 0) THEN --'Manual Entry'
1282 l_average_days_multiplier := l_overridden_avgsi_assignment;
1283 ELSIF (l_override_method = 1 ) THEN --'Percentage of Average Days'
1284 l_average_days_multiplier :=
1285 l_average_days_multiplier * l_overridden_avgsi_assignment/100;
1286 -- Bug 3122357
1287 --prorate the average_si_days value for actual days worked
1288 IF (l_payroll_days <> 0 and l_actual_work_days <> -99) THEN
1289 l_average_days_multiplier := (l_average_days_multiplier/l_payroll_days * l_actual_work_days);
1290 END IF;
1291
1292 ELSIF (l_override_method = 2 ) THEN -- 'Percentage of Maximum Days'
1293 -- l_max_si_days := pay_nl_si_pkg.Get_Max_SI_Days(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1294 -- l_average_days_multiplier := l_max_si_days * l_overridden_avgsi_assignment /100;
1295 --bug 3115132
1296 --get the method for maximum si days from social insurance tab
1297 OPEN c_get_max_si_values(p_assignment_id,p_date_earned);
1298 FETCH c_get_max_si_values into l_max_si_method,l_overridden_realsi_assignment;
1299 IF (c_get_max_si_values%FOUND AND l_max_si_method IS NOT NULL )THEN
1300 CLOSE c_get_max_si_values;
1301
1302 IF (l_max_si_method = 0 ) THEN -- 1 indicates 'Payroll Period' 0 indicates Weeks worked.
1303 --first check if a work pattern is attached to the ASG, if not
1304 -- return a warning condition and calculate the deduction amount to be 0
1305 OPEN c_get_work_pattern(p_assignment_id,p_date_earned);
1306 FETCH c_get_work_pattern INTO l_work_pattern;
1307 IF c_get_work_pattern%FOUND AND l_work_pattern IS NOT NULL THEN
1308 CLOSE c_get_work_pattern;
1309 ELSE
1310 CLOSE c_get_work_pattern;
1311 l_ret_val := 3;
1312 END IF;
1313 l_max_si_days :=
1314 pay_nl_si_pkg.Get_Max_SI_Days(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1315 ELSE
1316 l_max_si_days :=
1317 pay_nl_si_pkg.Get_Week_Days(l_pay_start_dt,l_pay_end_dt);
1318 END IF;
1319 l_average_days_multiplier :=
1320 l_max_si_days * l_overridden_avgsi_assignment/100;
1321
1322
1323 IF l_debug THEN
1324 pqp_utilities.debug(' l_max_si_method ' || l_max_si_method, 5);
1325 pqp_utilities.debug(' l_max_si_days ' || l_max_si_days ,6);
1326 END IF;
1327
1328 END IF;
1329 END IF;
1330
1331 IF l_debug THEN
1332 pqp_utilities.debug(' l_average_days_multiplier found '|| l_average_days_multiplier,7);
1333 END IF;
1334
1335 ELSIF (c_get_avg_si_assignment%FOUND
1336 AND
1337 (l_overridden_avgsi_assignment IS NULL AND l_override_method IS NOT NULL))
1338 THEN
1339
1340 l_ret_val := 1;
1341 p_error_message := 'Error : Overriding value has not been entered in Average Days Extra Information';
1342
1343 ELSIF (c_get_avg_si_assignment%FOUND
1344 AND
1345 (l_overridden_avgsi_assignment IS NOT NULL AND l_override_method IS NULL))
1346 THEN
1347
1348 l_ret_val := 1;
1349 p_error_message := 'Error : Overriding Method has not been entered in Average Days Extra Information';
1350
1351 ELSE
1352 bFlagAvgDays := true; -- to indicate that there is no override.
1353 CLOSE c_get_avg_si_assignment;
1354 END IF;-- check for override at assignment level.
1355
1356 IF l_debug THEN
1357 pqp_utilities.debug(' l_average_days_multiplier found '|| l_average_days_multiplier,7);
1358 END IF;
1359
1360 IF(bFlagAvgDays= true) THEN
1361
1362 -- check if employee is attached to a work pattern.
1363 OPEN c_get_work_pattern(p_assignment_id,p_date_earned);
1364 FETCH c_get_work_pattern INTO l_work_pattern;
1365 IF c_get_work_pattern%FOUND AND l_work_pattern IS NOT NULL THEN
1366 CLOSE c_get_work_pattern;
1367
1368 IF l_debug THEN
1369 pqp_utilities.debug(' l_work_pattern is '|| l_work_pattern , 190);
1370 END IF;
1371
1372 l_working_work_pattern_days := pay_nl_si_pkg.Get_Working_Work_Pattern_days(p_assignment_id) ;
1373 l_total_work_pattern_days := pay_nl_si_pkg.Get_Total_Work_Pattern_days(p_assignment_id) ;
1374
1375 IF l_debug THEN
1376 pqp_utilities.debug(' l_working_work_pattern_days is '|| l_working_work_pattern_days , 200);
1377 pqp_utilities.debug(' l_total_work_pattern_days is '|| l_total_work_pattern_days , 210);
1378 END IF;
1379
1380 -- si factor = working pattern days/total work days * average period days.
1381
1382 -- get the average no. of days in the pay period
1383 IF (p_payroll_period_prorate LIKE '%Calendar Month'
1384 OR p_payroll_period_prorate = 'CM') THEN
1385 -- use query for DBI : ORG_DF_NL_ORG_INFORMATION_AVERAGE_DAYS_PER_MONTH
1386 l_average_period_days := HR_NL_ORG_INFO.Get_Avg_Days_Per_Month(p_assignment_id);
1387
1388
1389
1390 ELSIF (p_payroll_period_prorate LIKE '%Lunar Month'
1391 OR p_payroll_period_prorate = 'LM') THEN
1392 l_average_period_days := 28;
1393
1394 ELSIF (p_payroll_period_prorate LIKE '%Quarter'
1395 OR p_payroll_period_prorate = 'Q') THEN
1396 -- use query for DBI : ORG_DF_NL_ORG_INFORMATION_AVERAGE_DAYS_PER_MONTH * 3
1397 l_average_period_days := HR_NL_ORG_INFO.Get_Avg_Days_Per_Month(p_assignment_id) * 3;
1398
1399 ELSIF ( p_payroll_period_prorate LIKE '%Week'
1400 OR p_payroll_period_prorate = 'W') THEN
1401 l_average_period_days := 7;
1402
1403 ELSE
1404 l_ret_val := 1;
1405 p_error_message := 'Error : Invalid value for Payroll Period';
1406
1407 END IF;
1408
1409 IF l_debug THEN
1410 pqp_utilities.debug(' p_error_message is '|| p_error_message , 230);
1411 END IF;
1412
1413 IF (l_ret_val = 0 ) THEN
1414 IF(l_total_work_pattern_days <> 0) THEN
1415
1416 IF l_debug THEN
1417 pqp_utilities.debug(' l_average_period_days is '|| l_average_period_days , 215);
1418 END IF;
1419 l_average_ws_si_days := l_working_work_pattern_days/l_total_work_pattern_days * l_average_period_days;
1420
1421 IF l_debug THEN
1422 pqp_utilities.debug(' l_average_ws_si_days is '|| l_average_ws_si_days , 220);
1423 END IF;
1424
1425 --prorate the average_ws_si_days value for actual days worked
1426 IF (l_payroll_days <> 0 and l_actual_work_days <> -99) THEN
1427 l_average_ws_si_days := (l_average_ws_si_days/l_payroll_days * l_actual_work_days);
1428 END IF;
1429
1430 IF l_debug THEN
1431 pqp_utilities.debug(' l_average_ws_si_days is '|| l_average_ws_si_days , 225);
1432 END IF;
1433
1434 -- get non si days
1435 l_non_si_days := pay_nl_si_pkg.Get_Non_SI_Days(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1436
1437 IF l_debug THEN
1438 pqp_utilities.debug(' l_non_si_days is '|| l_non_si_days , 230);
1439 END IF;
1440
1441 -- avg. work schedule si days = avg. work schedule si days - non si days
1442 l_average_ws_si_days := l_average_ws_si_days - l_non_si_days;
1443
1444
1445 -- l_prorate_amount = p_amount * l_average_ws_si_days/l_average_si_days;
1446 IF(l_average_ws_si_days > 0) THEN
1447 l_average_days_multiplier := l_average_ws_si_days;
1448 ELSE
1449 l_average_days_multiplier := 0;
1450 END IF;
1451 ELSE
1452 l_ret_val := 1;
1453 p_error_message := 'Error : Total Work Pattern days is 0. ';
1454 p_error_message := p_error_message||'Please verify that the workpattern';
1455 p_error_message := p_error_message||' attached to the assignment is defined correctly. ';
1456 END IF;
1457 END IF;
1458
1459 ELSE
1460 -- use average days
1461 -- with respect to actual days worked,no proration is to be done.
1462 -- l_average_days_multiplier := l_average_si_days;
1463 --prorate average days to the days worked in the pay period.
1464 IF (l_payroll_days <> 0 and l_actual_work_days <> -99) THEN
1465 l_average_days_multiplier := (l_average_days_multiplier/l_payroll_days * l_actual_work_days);
1466 END IF;
1467 l_tmp_ret_val := 2;
1468 CLOSE c_get_work_pattern;
1469 END IF; -- no w.p.
1470 END IF;-- check for bFlagAvgDays
1471
1472 ELSE
1473 CLOSE c_get_start_end_date;
1474 l_ret_val := 1;
1475 p_error_message := 'Payroll period start and end dates could not be ';
1476 p_error_message := 'determined for the current payroll run.';
1477 END IF; -- check for payroll id
1478
1479 END IF;-- check for ret val
1480 -- Real Working Days
1481
1482 ELSIF p_conversion_rule = '2' THEN
1483
1484 IF (l_ret_val = 0) THEN
1485
1486 --check if the real si values have been overriden at assignment level
1487 OPEN c_get_max_si_values(p_assignment_id,p_date_earned);
1488 FETCH c_get_max_si_values into l_max_si_method,l_overridden_realsi_assignment;
1489 IF (c_get_max_si_values%NOTFOUND OR
1490 (c_get_max_si_values%FOUND AND l_overridden_realsi_assignment IS NULL ))THEN
1491 CLOSE c_get_max_si_values;
1492
1493 IF l_debug THEN
1494 pqp_utilities.debug(' c_get_max_si_values not found ', 4);
1495 END IF;
1496
1497 OPEN c_get_start_end_date(p_assignment_id,p_date_earned);
1498 FETCH c_get_start_end_date into l_pay_start_dt,l_pay_end_dt;
1499 IF c_get_start_end_date%FOUND THEN
1500 CLOSE c_get_start_end_date;
1501
1502 IF l_debug THEN
1503 pqp_utilities.debug(' l_pay_start_dt is '|| l_pay_start_dt , 10);
1504 pqp_utilities.debug(' l_pay_end_dt is '|| l_pay_end_dt , 15);
1505 END IF;
1506
1507 -- check if the assignment has started in this payroll period.
1508 OPEN c_get_assign_start_date(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1509 FETCH c_get_assign_start_date into l_ass_start_dt,l_term_date;
1510 IF (c_get_assign_start_date%FOUND AND l_ass_start_dt IS NOT NULL)THEN
1511 CLOSE c_get_assign_start_date;
1512 l_ret_val := 0;
1513 IF l_debug THEN
1514 pqp_utilities.debug(' l_ass_start_dt is '|| l_ass_start_dt , 20);
1515 END IF;
1516 ELSE
1517 CLOSE c_get_assign_start_date;
1518 --p_error_message := 'Error: Unable to find the assignment start date for the person ';
1519 p_prorated_amount := 0;
1520 RETURN 0;
1521 END IF;-- assignment date check
1522
1523 OPEN c_term_date_decode(l_term_date);
1524 FETCH c_term_date_decode into l_term_date;
1525 CLOSE c_term_date_decode;
1526
1527 IF (l_term_date IS NOT NULL) THEN
1528 -- check if there is a date tracked row with active assignment from the start of the
1529 -- next pay run.
1530
1531 OPEN c_get_asg_end_date(p_assignment_id,l_term_date+1);
1532 FETCH c_get_asg_end_date into l_next_term_date;
1533 IF (c_get_asg_end_date%FOUND)THEN
1534 CLOSE c_get_asg_end_date;
1535 l_term_date := l_next_term_date;
1536 IF l_debug THEN
1537 pqp_utilities.debug(' l_next_term_date is '|| l_next_term_date , 92);
1538 END IF;
1539 ELSE
1540 CLOSE c_get_asg_end_date;
1541 END IF; -- assignment end date found
1542 END IF;-- term date is not null
1543
1544 IF l_debug THEN
1545 pqp_utilities.debug(' l_term_date is '|| l_term_date , 91);
1546 END IF;
1547 /* -- get termination date
1548 OPEN c_get_ass_term_date(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1549 FETCH c_get_ass_term_date into l_term_date;
1550 IF (c_get_ass_term_date%FOUND AND l_term_date IS NOT NULL)THEN
1551 CLOSE c_get_ass_term_date;
1552
1553 IF l_debug THEN
1554 pqp_utilities.debug(' l_term_date is '|| l_term_date , 30);
1555 END IF;
1556 ELSE
1557 CLOSE c_get_ass_term_date;
1558 END IF;-- term date check
1559 */
1560
1561
1562
1563 IF l_debug THEN
1564 pqp_utilities.debug(' l_ret_val is '|| l_ret_val , 31);
1565 END IF;
1566
1567 IF (l_ret_val = 0 )THEN
1568 IF(l_ass_start_dt IS NOT NULL AND l_ass_start_dt > l_pay_start_dt) THEN
1569 l_pay_start_dt := l_ass_start_dt;
1570 END IF;
1571
1572 IF(l_term_date IS NOT NULL AND l_term_date < l_pay_end_dt) THEN
1573 l_pay_end_dt := l_term_date;
1574 END IF;
1575
1576 IF l_debug THEN
1577 pqp_utilities.debug(' l_pay_start_dt is '|| l_pay_start_dt , 32);
1578 pqp_utilities.debug(' l_pay_end_dt is '|| l_pay_end_dt , 33);
1579 END IF;
1580
1581 -- check if employee is attached to a work pattern.
1582 OPEN c_get_work_pattern(p_assignment_id,p_date_earned);
1583 FETCH c_get_work_pattern INTO l_work_pattern;
1584 IF (c_get_work_pattern%FOUND AND l_work_pattern IS NOT NULL)THEN
1585 CLOSE c_get_work_pattern;
1586
1587 IF l_debug THEN
1588 pqp_utilities.debug(' l_work_pattern is '|| l_work_pattern , 40);
1589 END IF;
1590
1591 l_working_work_pattern_days := pqp_schedule_calculation_pkg.get_days_worked
1592 ( p_assignment_id => p_assignment_id
1593 ,p_business_group_id => p_business_group_id
1594 ,p_date_start => l_pay_start_dt
1595 ,p_date_end =>l_pay_end_dt
1596 ,p_error_code => l_error_code
1597 ,p_error_message => l_error_message
1598 ,p_override_wp => l_work_pattern
1599 );
1600
1601 IF l_debug THEN
1602 pqp_utilities.debug(' l_working_work_pattern_days is '|| l_working_work_pattern_days , 50);
1603 END IF;
1604
1605 IF (l_max_si_method = 0 ) THEN -- 1 indicates 'Payroll Period' 0 indicates Weeks worked.
1606 l_max_si_days := pay_nl_si_pkg.Get_Max_SI_Days(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1607 ELSE
1608 l_max_si_days := pay_nl_si_pkg.Get_Week_Days(l_pay_start_dt,l_pay_end_dt);
1609 END IF;
1610
1611 IF l_debug THEN
1612 pqp_utilities.debug(' l_max_si_days is '|| l_max_si_days , 60);
1613 END IF;
1614
1615 l_non_si_days := pay_nl_si_pkg.Get_Non_SI_Days(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1616
1617 IF l_debug THEN
1618 pqp_utilities.debug(' l_non_si_days is '|| l_non_si_days , 70);
1619 END IF;
1620
1621 IF (l_working_work_pattern_days > l_max_si_days ) THEN
1622 l_real_si_days := l_max_si_days;
1623 ELSE
1624 l_real_si_days := l_working_work_pattern_days;
1625 END IF;
1626
1627 IF l_debug THEN
1628 pqp_utilities.debug(' l_real_si_days is '|| l_real_si_days , 80);
1629 END IF;
1630
1631 l_real_si_days := l_real_si_days - l_non_si_days;
1632
1633 -- l_average_days_divisor := l_max_si_days ;
1634
1635 IF(l_real_si_days > 0) THEN
1636 l_average_days_multiplier := l_real_si_days ;
1637 ELSE
1638 l_average_days_multiplier := 0;
1639 END IF;
1640
1641 ELSE
1642 l_ret_val := 3;
1643 CLOSE c_get_work_pattern;
1644 END IF; -- check for w.p.
1645 END IF; -- ret val is zero
1646 ELSE
1647 CLOSE c_get_start_end_date;
1648 l_ret_val := 1;
1649 p_error_message := 'Payroll period start and end dates could not ';
1650 p_error_message := p_error_message||'be determined for the current payroll run.';
1651 END IF; --check for payroll id
1652 ELSE
1653 CLOSE c_get_max_si_values;
1654 l_average_days_multiplier := l_overridden_realsi_assignment;
1655 IF l_debug THEN
1656 pqp_utilities.debug(' l_overridden_realsi_assignment ' || l_overridden_realsi_assignment, 90);
1657 END IF;
1658 END IF;-- check for max si days
1659 END IF; -- ret val is zero.
1660
1661 -- if the conversion rule is Prorate to pay period, divide by the number
1662 -- of pay periods in a year
1663 ELSIF p_conversion_rule = '3' then
1664
1665 IF l_ret_val = 0 then
1666
1667 -- check IF the assignment has started in this payroll period.
1668 OPEN c_get_assign_start_date(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1669 FETCH c_get_assign_start_date into l_ass_start_dt,l_term_date;
1670 IF (c_get_assign_start_date%FOUND
1671 AND l_ass_start_dt IS NOT NULL)THEN
1672 CLOSE c_get_assign_start_date;
1673 IF l_debug THEN
1674 pqp_utilities.debug(' l_ass_start_dt is '|| l_ass_start_dt , 91);
1675 pqp_utilities.debug(' l_term_date is '|| l_term_date , 91);
1676 END IF;
1677 ELSE
1678 CLOSE c_get_assign_start_date;
1679 p_prorated_amount := 0;
1680 RETURN 0;
1681 --p_error_message := 'Error: Unable to find the assignment start date for the person ';
1682 END IF; -- assignment start date found
1683
1684 OPEN c_term_date_decode(l_term_date);
1685 FETCH c_term_date_decode into l_term_date;
1686 CLOSE c_term_date_decode;
1687
1688
1689
1690 IF (l_term_date IS NOT NULL) THEN
1691 -- check if there is a date tracked row with active assignment from the start of the
1692 -- next pay run.
1693
1694 OPEN c_get_asg_end_date(p_assignment_id,l_term_date+1);
1695 FETCH c_get_asg_end_date into l_next_term_date;
1696 IF (c_get_asg_end_date%FOUND)THEN
1697 CLOSE c_get_asg_end_date;
1698 l_term_date := l_next_term_date;
1699 IF l_debug THEN
1700 pqp_utilities.debug(' l_next_term_date is '|| l_next_term_date , 92);
1701 END IF;
1702 ELSE
1703 CLOSE c_get_asg_end_date;
1704 END IF; -- assignment end date found
1705 END IF;-- term date is not null
1706
1707 IF l_debug THEN
1708 pqp_utilities.debug(' l_term_date is '|| l_term_date , 91);
1709 END IF;
1710
1711 /* -- get termination date
1712 OPEN c_get_ass_term_date(p_assignment_id,l_pay_start_dt,l_pay_end_dt);
1713 FETCH c_get_ass_term_date into l_term_date;
1714 IF (c_get_ass_term_date%FOUND AND l_term_date IS NOT NULL)THEN
1715 CLOSE c_get_ass_term_date;
1716 IF l_debug THEN
1717 pqp_utilities.debug(' l_term_date is '|| l_term_date , 101);
1718 END IF;
1719 ELSE
1720 CLOSE c_get_ass_term_date;
1721 END IF;-- get termination dt
1722 */
1723
1724 IF(l_ass_start_dt is not null AND ((l_ass_start_dt > l_pay_start_dt) OR (l_ass_start_dt = l_pay_start_dt))) THEN
1725 IF (l_term_date is not null AND ((l_term_date < l_pay_end_dt) OR (l_term_date = l_pay_start_dt)))THEN
1726 OPEN c_no_of_days(l_term_date,l_ass_start_dt);
1727 FETCH c_no_of_days into l_days;
1728 IF (c_no_of_days%FOUND AND l_days IS NOT NULL)THEN
1729 CLOSE c_no_of_days;
1730 l_actual_work_days := l_days;
1731 pqp_utilities.debug(' l_actual_work_days is '|| l_actual_work_days , 105);
1732 ELSE
1733 CLOSE c_no_of_days;
1734 END IF;
1735 ELSE
1736 OPEN c_no_of_days(l_pay_end_dt,l_ass_start_dt);
1737 FETCH c_no_of_days into l_days;
1738 IF (c_no_of_days%FOUND AND l_days IS NOT NULL)THEN
1739 CLOSE c_no_of_days;
1740 l_actual_work_days := l_days;
1741 pqp_utilities.debug(' l_actual_work_days is '|| l_actual_work_days , 106);
1742 pqp_utilities.debug(' l_days is '|| l_days , 107);
1743 ELSE
1744 CLOSE c_no_of_days;
1745 END IF;
1746 END IF;
1747
1748 ELSE
1749 IF (l_term_date is not null AND ((l_term_date < l_pay_end_dt) OR (l_term_date = l_pay_end_dt))) THEN
1750 OPEN c_no_of_days(l_term_date,l_pay_start_dt);
1751 FETCH c_no_of_days into l_days;
1752 IF (c_no_of_days%FOUND AND l_days IS NOT NULL)THEN
1753 CLOSE c_no_of_days;
1754 l_actual_work_days := l_days;
1755 ELSE
1756 CLOSE c_no_of_days;
1757 END IF;
1758 END IF; --term date check
1759
1760 END IF;-- assgn_start_dt check
1761
1762
1763 IF(l_ass_start_dt IS NOT NULL AND l_ass_start_dt > l_pay_start_dt) THEN
1764 l_pay_start_dt := l_ass_start_dt;
1765 END IF;
1766
1767 IF(l_term_date IS NOT NULL AND l_term_date < l_pay_end_dt) THEN
1768 l_pay_end_dt := l_term_date;
1769 END IF;
1770
1771 IF l_debug THEN
1772 pqp_utilities.debug(' l_average_days_divisor is '|| l_average_days_divisor , 151);
1773 pqp_utilities.debug(' l_payroll_days is '|| l_payroll_days , 161);
1774 pqp_utilities.debug(' l_actual_work_days is '|| l_actual_work_days , 171);
1775 pqp_utilities.debug(' l_pay_start_dt is '|| l_pay_start_dt , 173);
1776 pqp_utilities.debug(' l_pay_end_dt is '|| l_pay_end_dt , 176);
1777 END IF;
1778
1779 l_average_days_multiplier := l_periods_per_yr;
1780
1781 IF(l_payroll_days <> 0 and l_actual_work_days <> -99) THEN
1782 l_average_days_multiplier := (l_average_days_multiplier/l_payroll_days * l_actual_work_days);
1783 END IF;
1784 l_average_days_divisor := l_prorate_periods_per_yr;
1785
1786 End If;
1787
1788 ELSE
1789
1790 l_ret_val := 1;
1791 p_error_message := 'Error : Invalid Conversion Rule for prorating amount';
1792
1793 END IF; -- end of check for conversion rule.
1794
1795 --END IF;-- periods are different.
1796
1797 END IF;--an override of pension days has been entered
1798
1799
1800 IF l_ret_val = 0 THEN
1801
1802 IF l_debug THEN
1803 pqp_utilities.debug(' l_average_days_multiplier is '|| l_average_days_multiplier , 250);
1804 pqp_utilities.debug(' l_average_days_divisor is '|| l_average_days_divisor , 260);
1805 END IF;
1806
1807 p_prorated_amount := (p_amount/l_average_days_divisor) * l_average_days_multiplier;
1808 p_prorated_amount := ROUND(p_prorated_amount,2);
1809
1810 IF l_debug THEN
1811 pqp_utilities.debug(' p_prorated_amount is '|| p_prorated_amount , 300);
1812 END IF;
1813
1814 END IF;
1815
1816 IF (l_tmp_ret_val = 1 AND l_ret_val =0 )THEN
1817 RETURN 2;
1818 ELSIF (l_tmp_ret_val = 2 AND l_ret_val =0) THEN
1819 RETURN 4;
1820 ELSE
1821 RETURN l_ret_val;
1822 END IF;
1823
1824 EXCEPTION
1825 WHEN NO_DATA_FOUND THEN
1826
1827 l_ret_val := 1;
1828 p_error_message := 'Error occured while prorating the annual amount. Global value ';
1829 p_error_message := p_error_message||'for Average Days could not be found';
1830
1831
1832 END prorate_amount;
1833
1834 -- ----------------------------------------------------------------------------
1835 -- |--------------------------< get_run_year >--------------------------------|
1836 -- ----------------------------------------------------------------------------
1837 --
1838 function get_run_year
1839 (p_date_earned IN DATE
1840 ,p_error_message OUT NOCOPY VARCHAR2
1841 )
1842 RETURN NUMBER IS
1843
1844 l_date_earned DATE;
1845
1846 BEGIN
1847
1848 l_date_earned := TRUNC(p_date_earned);
1849
1850 RETURN TO_NUMBER(TO_CHAR(l_date_earned,'YYYY'));
1851
1852 END get_run_year;
1853
1854 -- ----------------------------------------------------------------------------
1855 -- |---------------------------< get_bal_val_de >------------------------------|
1856 -- ----------------------------------------------------------------------------
1857 -- This function derives the value of a balance using the _ABP_ASG_YTD route.
1858 -- This is used very sparingly and not for all EE assignments.
1859 -- This is only used in the case of a late hire and when the late hire
1860 -- crosses years for e.g. hired in Dec 2006 but the first payroll
1861 -- is processed eff jan 2007. Do not use this function in any other
1862 -- situation other than ABP late hires.
1863 --
1864 FUNCTION get_bal_val_de
1865 (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
1866 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1867 ,p_date_earned IN DATE
1868 ,p_start_date IN DATE
1869 ,p_end_date IN DATE
1870 ,p_payroll_id IN NUMBER
1871 ,p_balance_name IN VARCHAR2
1872 ,p_dimension_name IN VARCHAR2)
1873 RETURN NUMBER IS
1874
1875 l_defined_balance_id NUMBER;
1876 l_val NUMBER :=0;
1877 l_ass_act_id NUMBER;
1878
1879 CURSOR c_ass_act IS
1880 SELECT max(paa.assignment_action_id)
1881 FROM pay_assignment_actions paa
1882 ,pay_payroll_actions ppa
1883 WHERE paa.assignment_id = p_assignment_id
1884 AND ppa.action_status = 'C'
1885 AND ppa.action_type IN ('Q','R')
1886 AND paa.action_status = 'C'
1887 AND paa.payroll_action_id = ppa.payroll_action_id
1888 AND ppa.payroll_id = Nvl(p_payroll_id,ppa.payroll_id)
1889 AND ppa.consolidation_set_id = ppa.consolidation_set_id
1890 AND source_action_id IS NOT NULL
1891 AND ppa.date_earned BETWEEN p_start_date AND p_end_date
1892 AND ppa.effective_date <= p_date_earned;
1893
1894 BEGIN
1895 --
1896 -- Get the defined balance id
1897 --
1898 OPEN csr_defined_bal
1899 (c_balance_name => p_balance_name
1900 ,c_dimension_name => p_dimension_name
1901 ,c_business_group_id => p_business_group_id);
1902
1903 FETCH csr_defined_bal INTO l_defined_balance_id;
1904
1905 IF csr_defined_bal%NOTFOUND THEN
1906
1907 l_val := 0;
1908
1909 ELSE
1910
1911 l_ass_act_id := NULL;
1912
1913 OPEN c_ass_act;
1914 FETCH c_ass_act INTO l_ass_act_id;
1915
1916 IF c_ass_act%FOUND THEN
1917 --
1918 -- Call get_balance_pkg
1919 --
1920 l_val := pay_balance_pkg.get_value
1921 (p_defined_balance_id => l_defined_balance_id
1922 ,p_assignment_action_id => l_ass_act_id);
1923 ELSE
1924 l_val := 0;
1925 END IF;
1926
1927 CLOSE c_ass_act;
1928
1929 END IF;
1930
1931 CLOSE csr_defined_bal;
1932
1933 RETURN l_val;
1934
1935 EXCEPTION WHEN OTHERS THEN
1936 CLOSE csr_defined_bal;
1937 l_val :=0;
1938 RETURN l_val;
1939
1940 END get_bal_val_de;
1941
1942 -- ----------------------------------------------------------------------------
1943 -- |-----------------------------< get_bal_val >-------------------------------|
1944 -- ----------------------------------------------------------------------------
1945 FUNCTION get_bal_val
1946 (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
1947 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1948 ,p_effective_date IN DATE
1949 ,p_balance_name IN VARCHAR2
1950 ,p_dimension_name IN VARCHAR2)
1951 RETURN NUMBER IS
1952
1953 l_defined_balance_id NUMBER;
1954 l_val NUMBER :=0;
1955
1956 BEGIN
1957 --
1958 -- Get the defined balance id
1959 --
1960 OPEN csr_defined_bal
1961 (c_balance_name => p_balance_name
1962 ,c_dimension_name => p_dimension_name
1963 ,c_business_group_id => p_business_group_id);
1964
1965 FETCH csr_defined_bal INTO l_defined_balance_id;
1966
1967 IF csr_defined_bal%NOTFOUND THEN
1968 l_val := 0;
1969 ELSE
1970 --
1971 -- Call get_balance_pkg
1972 --
1973 l_val := pay_balance_pkg.get_value
1974 (p_defined_balance_id => l_defined_balance_id
1975 ,p_assignment_id => p_assignment_id
1976 ,p_virtual_date => p_effective_date);
1977 END IF;
1978
1979 CLOSE csr_defined_bal;
1980
1981 RETURN l_val;
1982
1983 EXCEPTION WHEN OTHERS THEN
1984 CLOSE csr_defined_bal;
1985 l_val :=0;
1986 RETURN l_val;
1987
1988 END get_bal_val;
1989
1990 --
1991 -- ----------------------------------------------------------------------------
1992 -- |-----------------------< get_abp_pension_salary >-----------------------------|
1993 -- ----------------------------------------------------------------------------
1994 function get_abp_pension_salary
1995 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
1996 ,p_date_earned in date
1997 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1998 ,p_payroll_id in pay_payroll_actions.payroll_id%TYPE
1999 ,p_period_start_date in date
2000 ,p_period_end_date in date
2001 ,p_scale_salary in number
2002 ,p_scale_salary_h in number
2003 ,p_ft_rec_bonus in number
2004 ,p_ft_rec_bonus_h in number
2005 ,p_pt_rec_bonus in number
2006 ,p_pt_rec_bonus_h in number
2007 ,p_ft_eoy_bonus in number
2008 ,p_ft_eoy_bonus_h in number
2009 ,p_pt_eoy_bonus in number
2010 ,p_pt_eoy_bonus_h in number
2011 ,p_salary_balance_value out nocopy number
2012 ,p_error_message out nocopy varchar2
2013 ,p_oht_correction out nocopy varchar2
2014 ,p_scale_salary_eoy_bonus in number
2015 ,p_ft_rec_bonus_eoy_bonus in number
2016 ,p_pt_rec_bonus_eoy_bonus in number
2017 ,p_error_message1 out nocopy varchar2
2018 ,p_error_message2 out nocopy varchar2
2019 ,p_late_hire_indicator in number
2020 ) return number IS
2021
2022 -- Cursor to get the hire date of the person
2023 CURSOR c_hire_dt_cur(c_asg_id IN NUMBER) IS
2024 SELECT max(date_start)
2025 FROM per_all_assignments_f asg
2026 ,per_periods_of_service pps
2027 WHERE pps.person_id = asg.person_id
2028 AND asg.assignment_id = c_asg_id
2029 AND pps.business_group_id = p_business_group_id
2030 AND date_start <= p_date_earned;
2031
2032 CURSOR c_pt_cur (c_effective_date IN DATE) IS
2033 SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) pt_perc
2034 FROM per_assignments_f asg
2035 ,hr_soft_coding_keyflex target
2036 WHERE asg.assignment_id = p_assignment_id
2037 AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
2038 AND trunc(c_effective_date) between asg.effective_start_date
2039 AND asg.effective_end_date
2040 AND target.enabled_flag = 'Y';
2041
2042 --cursor to fetch the assignment start date
2043 CURSOR c_get_asg_start IS
2044 SELECT min(effective_start_date)
2045 FROM per_all_assignments_f
2046 WHERE assignment_id = p_assignment_id;
2047
2048 -- =============================================================================
2049 -- Cursor to get the defined balance id for a given balance and dimension
2050 -- =============================================================================
2051 CURSOR csr_defined_bal (c_balance_name IN Varchar2
2052 ,c_dimension_name IN Varchar2
2053 ,c_business_group_id IN Number) IS
2054 SELECT db.defined_balance_id
2055 FROM pay_balance_types pbt
2056 ,pay_defined_balances db
2057 ,pay_balance_dimensions bd
2058 WHERE pbt.balance_name = c_balance_name
2059 AND pbt.balance_type_id = db.balance_type_id
2060 AND bd.balance_dimension_id = db.balance_dimension_id
2061 AND bd.dimension_name = c_dimension_name
2062 AND (pbt.business_group_id = c_business_group_id OR
2063 pbt.legislation_code = 'NL')
2064 AND (db.business_group_id = pbt.business_group_id OR
2065 db.legislation_code = 'NL');
2066
2067 -- =======================================================================
2068 -- Cursor to get the holiday allowance global
2069 -- =======================================================================
2070 CURSOR c_global_cur(c_global_name IN VARCHAR2) IS
2071 SELECT fnd_number.canonical_to_number(global_value)
2072 FROM ff_globals_f
2073 WHERE global_name = c_global_name
2074 AND trunc (p_date_earned) BETWEEN effective_start_date
2075 AND effective_end_date;
2076
2077 -- =======================================================================
2078 -- Cursor to get the collective agreement name as of 1 Jan/Hire Date
2079 -- =======================================================================
2080 CURSOR c_cag_name (c_asg_id IN NUMBER
2081 ,c_eff_date IN DATE) IS
2082 SELECT cola.name
2083 FROM per_collective_agreements cola
2084 ,per_all_assignments_f asg
2085 WHERE asg.assignment_id = c_asg_id
2086 AND asg.collective_agreement_id = cola.collective_agreement_id
2087 AND cola.status = 'A'
2088 AND c_eff_date BETWEEN asg.effective_start_date
2089 AND asg.effective_end_date
2090 AND c_eff_date BETWEEN cola.start_date
2091 AND NVL(cola.end_date,to_date('31/12/4712','DD/MM/YYYY'));
2092
2093 -- =======================================================================
2094 -- Cursor to get the number of pay periods per year.
2095 -- =======================================================================
2096 CURSOR c_pp_cur IS
2097 SELECT pety.number_per_fiscal_year
2098 FROM pay_payrolls_f ppaf
2099 ,per_time_period_types pety
2100 WHERE ppaf.payroll_id = p_payroll_id
2101 AND ppaf.period_type = pety.period_type;
2102
2103 -- =======================================================================
2104 -- Cursor to get the choice for average PTP calculation from ASG EIT
2105 -- =======================================================================
2106 CURSOR c_asg_ptp_choice(c_eff_date IN DATE) IS
2107 SELECT aei_information10
2108 FROM per_assignment_extra_info
2109 WHERE aei_information_category = 'NL_ABP_PAR_INFO'
2110 AND information_type = 'NL_ABP_PAR_INFO'
2111 AND assignment_id = p_assignment_id
2112 AND aei_information10 IS NOT NULL
2113 AND c_eff_date BETWEEN fnd_date.canonical_to_date(aei_information1)
2114 AND fnd_date.canonical_to_date(nvl(aei_information2
2115 ,fnd_date.date_to_canonical(hr_api.g_eot)));
2116
2117 -- =======================================================================
2118 -- Cursor to get the choice for average PTP calculation from ORG EIT
2119 -- =======================================================================
2120 CURSOR c_org_ptp_choice(c_org_id IN NUMBER) IS
2121 SELECT hoi.org_information1
2122 FROM hr_organization_information hoi
2123 WHERE hoi.organization_id = c_org_id
2124 AND hoi.org_information_context = 'PQP_NL_ABP_PTP_METHOD'
2125 AND hoi.org_information1 IS NOT NULL;
2126
2127 -- =======================================================================
2128 -- Cursor to get the choice for OHT Correction from ASG EIT
2129 -- =======================================================================
2130 CURSOR c_asg_oht_choice(c_eff_date IN DATE) IS
2131 SELECT aei_information11
2132 FROM per_assignment_extra_info
2133 WHERE aei_information_category = 'NL_ABP_PAR_INFO'
2134 AND information_type = 'NL_ABP_PAR_INFO'
2135 AND assignment_id = p_assignment_id
2136 AND aei_information11 IS NOT NULL
2137 AND c_eff_date BETWEEN fnd_date.canonical_to_date(aei_information1)
2138 AND fnd_date.canonical_to_date(nvl(aei_information2
2139 ,fnd_date.date_to_canonical(hr_api.g_eot)));
2140
2141 -- =======================================================================
2142 -- Cursor to get the choice for OHT from ORG EIT
2143 -- =======================================================================
2144 CURSOR c_org_oht_choice(c_org_id IN NUMBER) IS
2145 SELECT hoi.org_information2
2146 FROM hr_organization_information hoi
2147 WHERE hoi.organization_id = c_org_id
2148 AND hoi.org_information_context = 'PQP_NL_ABP_PTP_METHOD'
2149 AND hoi.org_information2 IS NOT NULL;
2150
2151 --Cursor to find the named hierarchy associated with the BG
2152 CURSOR c_find_named_hierarchy Is
2153 select org_information1
2154 from hr_organization_information
2155 where organization_id = p_business_group_id
2156 and org_information_context = 'NL_BG_INFO';
2157
2158 --Cursor to find the valid version id for the particular named hierarchy
2159 CURSOR c_find_ver_frm_hierarchy(c_hierarchy_id in Number
2160 ,c_eff_date in Date) Is
2161 select ORG_STRUCTURE_VERSION_ID
2162 from per_org_structure_versions_v
2163 where organization_structure_id = c_hierarchy_id
2164 and c_eff_date between date_from
2165 and nvl(date_to,hr_api.g_eot);
2166
2167 --Cursor to find the valid version id for a particular business group
2168 CURSOR c_find_ver_frm_bg(c_eff_date in date) Is
2169 select ORG_STRUCTURE_VERSION_ID
2170 from per_org_structure_versions_v
2171 where business_group_id = p_business_group_id
2172 and c_eff_date between date_from
2173 and nvl( date_to,hr_api.g_eot);
2174
2175 --cursor to fetch the org id for the organization
2176 --attached to this assignment
2177 CURSOR c_get_org_id(c_eff_date IN DATE) IS
2178 SELECT organization_id
2179 FROM per_all_assignments_f
2180 WHERE assignment_id = p_assignment_id
2181 AND c_eff_date BETWEEN effective_start_date
2182 AND effective_end_date;
2183
2184 --Cursor to find the parent id from the org id
2185 CURSOR c_find_parent_id(c_org_id in number
2186 ,c_version_id in number) IS
2187 select organization_id_parent
2188 from per_org_structure_elements
2189 where organization_id_child = c_org_id
2190 AND org_structure_version_id = c_version_id
2191 AND business_group_id = p_business_group_id;
2192
2193 --7344670
2194 --Cursor to check if the assignment is terminated or not.
2195 Cursor chk_term_asg IS
2196 SELECT 1
2197 FROM per_all_assignments_f asg
2198 WHERE assignment_id = p_assignment_id
2199 AND effective_start_date <= p_date_earned
2200 AND assignment_status_type_id IN (SELECT assignment_status_type_id
2201 FROM per_assignment_status_types
2202 WHERE per_system_status = 'TERM_ASSIGN'
2203 AND active_flag = 'Y');
2204
2205 l_asg_term_indicator VARCHAR2(1);
2206 --7344670
2207
2208 l_loop_again NUMBER;
2209 l_valid_pt NUMBER;
2210 l_named_hierarchy NUMBER;
2211 l_version_id NUMBER default null;
2212 l_org_id NUMBER;
2213 l_run_year number;
2214 l_begin_of_year_date date;
2215 l_end_of_last_year date;
2216 l_beg_of_last_year date;
2217 l_effective_date date;
2218 l_hire_date date;
2219 l_asg_st_date date;
2220 l_jan_hire_ptp number;
2221 l_error_message varchar2(1000);
2222 l_defined_balance_id NUMBER;
2223 l_scale_salary NUMBER;
2224 l_scale_salary_h NUMBER;
2225 l_ft_rec_bonus NUMBER;
2226 l_ft_rec_bonus_h NUMBER;
2227 l_pt_rec_bonus NUMBER;
2228 l_pt_rec_bonus_h NUMBER;
2229 l_ft_non_rec_bonus NUMBER;
2230 l_ft_non_rec_bonus_h NUMBER;
2231 l_pt_non_rec_bonus NUMBER;
2232 l_pt_non_rec_bonus_h NUMBER;
2233 l_ft_eoy_bonus NUMBER;
2234 l_ft_eoy_bonus_h NUMBER;
2235 l_pt_eoy_bonus NUMBER;
2236 l_pt_eoy_bonus_h NUMBER;
2237 l_scale_eoy_bonus number;
2238 l_ft_rec_bonus_eoy_bonus number;
2239 l_pt_rec_bonus_eoy_bonus number;
2240 l_avg_prev_year_ptp NUMBER;
2241 l_holiday_allowance NUMBER;
2242 l_pension_sal_prev_yr NUMBER;
2243 l_holiday_allow_per NUMBER;
2244 l_inflation_per NUMBER;
2245 l_per_age NUMBER;
2246 l_max_inflated_sal NUMBER;
2247 l_min_holiday_allow NUMBER;
2248 l_cag_name per_collective_agreements.name%TYPE;
2249 l_min_holiday_char VARCHAR2(80);
2250 l_max_periods NUMBER;
2251 l_ptp_choice VARCHAR2(1);
2252 l_max_ptp NUMBER := 0;
2253 l_prev_max_ptp NUMBER := 0;
2254 l_ret_val NUMBER := 0;
2255 l_error_status CHAR;
2256 l_eoy_bonus_org NUMBER := 0;
2257 l_eoy_bonus_perc NUMBER := 0;
2258 l_min_eoy_bonus_char VARCHAR2(80):='';
2259 l_min_eoy_bonus NUMBER:=0;
2260 l_eoy_bonus_ca NUMBER:=0;
2261 l_py_late_hire_ind NUMBER;
2262 l_message_flag CHAR;
2263 UDT_CONTAINS_NO_DATA EXCEPTION;
2264 l_retro_avg_ptp NUMBER:=0;
2265
2266 BEGIN
2267
2268 l_scale_salary := NVL(p_scale_salary,0);
2269 l_scale_salary_h := NVL(p_scale_salary_h,0);
2270 l_ft_rec_bonus := NVL(p_ft_rec_bonus,0);
2271 l_ft_rec_bonus_h := NVL(p_ft_rec_bonus_h,0);
2272 l_pt_rec_bonus := NVL(p_pt_rec_bonus,0);
2273 l_pt_rec_bonus_h := NVL(p_pt_rec_bonus_h,0);
2274 l_ft_non_rec_bonus := 0;
2275 l_ft_non_rec_bonus_h := 0;
2276 l_pt_non_rec_bonus := 0;
2277 l_pt_non_rec_bonus_h := 0;
2278 l_ft_eoy_bonus := NVL(p_ft_eoy_bonus,0);
2279 l_ft_eoy_bonus_h := NVL(p_ft_eoy_bonus_h,0);
2280 l_pt_eoy_bonus := NVL(p_pt_eoy_bonus,0);
2281 l_pt_eoy_bonus_h := NVL(p_pt_eoy_bonus_h,0);
2282 l_holiday_allowance := 0;
2283 l_pension_sal_prev_yr := 0;
2284 l_holiday_allow_per := 0;
2285 l_inflation_per := 0;
2286 l_per_age := 0;
2287 l_loop_again := 1;
2288 p_oht_correction :='N';
2289 l_scale_eoy_bonus := NVL(p_scale_salary_eoy_bonus,0);
2290 l_ft_rec_bonus_eoy_bonus := NVL(p_ft_rec_bonus_eoy_bonus,0);
2291 l_pt_rec_bonus_eoy_bonus := NVL(p_pt_rec_bonus_eoy_bonus,0);
2292 l_eoy_bonus_org := 0;
2293 l_eoy_bonus_perc := 0;
2294 l_min_eoy_bonus := 0;
2295 l_eoy_bonus_ca := 0;
2296 l_py_late_hire_ind := 0;
2297 p_error_message :='';
2298 p_error_message1 :='';
2299 p_error_message2 :='';
2300 l_message_flag :='';
2301
2302 l_run_year := get_run_year (p_date_earned
2303 ,l_error_message );
2304
2305 --
2306 -- Get the date for 1 JAN of the run year
2307 --
2308 l_begin_of_year_date := TO_DATE('01/01/'||to_char(l_run_year),'DD/MM/YYYY');
2309 --
2310 -- Get the date for 31 DEC of the prev year
2311 --
2312 l_end_of_last_year := TO_DATE('31/12/'||to_char(l_run_year - 1),'DD/MM/YYYY');
2313 --
2314 -- Get the date for 1 JAN of the prev year
2315 --
2316 l_beg_of_last_year := TO_DATE('01/01/'||to_char(l_run_year - 1),'DD/MM/YYYY');
2317
2318 --7344670
2319 OPEN chk_term_asg;
2320 FETCH chk_term_asg INTO l_asg_term_indicator;
2321 IF chk_term_asg%FOUND THEN
2322 p_error_message := 'The pension salary is zero as the assignment is not active.';
2323 p_salary_balance_value := 0;
2324 CLOSE chk_term_asg;
2325 Return 2;
2326 END IF;
2327 CLOSE chk_term_asg;
2328 --7344670
2329
2330 --
2331 -- Get the latest start date of the assignment
2332 --
2333 OPEN c_get_asg_start;
2334 FETCH c_get_asg_start INTO l_asg_st_date;
2335 IF c_get_asg_start%FOUND THEN
2336 CLOSE c_get_asg_start;
2337 ELSE
2338 CLOSE c_get_asg_start;
2339 p_error_message := 'Error: Unable to find the start date of the assignment';
2340 p_salary_balance_value := 0;
2341 RETURN 1;
2342 END IF;
2343
2344 --
2345 -- Get the hire date
2346 --
2347 OPEN c_hire_dt_cur (p_assignment_id);
2348
2349 FETCH c_hire_dt_cur INTO l_hire_date;
2350 IF c_hire_dt_cur%FOUND THEN
2351 -- The effective date is now the valid assignemnt
2352 --start date for the assignment
2353 l_effective_date := PQP_NL_ABP_FUNCTIONS.GET_VALID_START_DATE(p_assignment_id,p_date_earned,l_error_status,l_error_message);
2354 IF(l_error_status = trim(to_char(1,'9'))) Then
2355 fnd_message.set_name('PQP',l_error_message);
2356 p_error_message :='Error : '|| fnd_message.get();
2357 p_salary_balance_value :=0;
2358 RETURN 1;
2359 End IF;
2360 ELSE
2361 p_error_message := 'Error: Unable to find the hire date for the person ';
2362 p_salary_balance_value :=0;
2363 RETURN 1;
2364 END IF; -- Hire date found
2365
2366 CLOSE c_hire_dt_cur;
2367
2368 --
2369 -- Get the maximum number of periods in a year.
2370 --
2371 OPEN c_pp_cur;
2372
2373 FETCH c_pp_cur INTO l_max_periods;
2374 IF c_pp_cur%NOTFOUND THEN
2375 p_error_message := 'Error: Unable to find the pay periods per year';
2376 p_salary_balance_value :=0;
2377 CLOSE c_pp_cur;
2378 RETURN 1;
2379 ELSE
2380 CLOSE c_pp_cur;
2381 END IF;
2382
2383 --
2384 -- Calculate the ptp as of 1 jan or Hire Date
2385 --
2386 OPEN c_pt_cur (l_effective_date);
2387
2388 FETCH c_pt_cur INTO l_jan_hire_ptp;
2389
2390 IF c_pt_cur%NOTFOUND THEN
2391 CLOSE c_pt_cur;
2392 p_error_message := 'Error: Unable to find the part time percentage.';
2393 p_error_message := p_error_message || 'Please enter a value as of 1 January or Hire Date';
2394 p_salary_balance_value :=0;
2395 RETURN 1;
2396 ELSE
2397 CLOSE c_pt_cur;
2398 END IF;
2399
2400 IF l_jan_hire_ptp = 0 THEN
2401 l_jan_hire_ptp := 100;
2402 END IF;
2403
2404 --restrict the part time percentage to a max of 125
2405 IF l_jan_hire_ptp > 125 THEN
2406 l_max_ptp := 1;
2407 END IF;
2408
2409 l_jan_hire_ptp := LEAST(l_jan_hire_ptp,125);
2410
2411 l_jan_hire_ptp := l_jan_hire_ptp/100;
2412
2413 -- Divide all the pt balances by this value
2414
2415 l_pt_rec_bonus := l_pt_rec_bonus/l_jan_hire_ptp;
2416 l_pt_rec_bonus_h := l_pt_rec_bonus_h/l_jan_hire_ptp;
2417 l_pt_eoy_bonus := l_pt_eoy_bonus/l_jan_hire_ptp;
2418 l_pt_eoy_bonus_h := l_pt_eoy_bonus_h/l_jan_hire_ptp;
2419 l_pt_rec_bonus_eoy_bonus := l_pt_rec_bonus_eoy_bonus/l_jan_hire_ptp;
2420
2421 --
2422 -- Check if the EE asg was a late hire in the prev year
2423 --
2424 l_py_late_hire_ind := pqp_pension_functions.get_bal_val
2425 (p_business_group_id => p_business_group_id
2426 ,p_assignment_id => p_assignment_id
2427 ,p_effective_date => l_end_of_last_year
2428 ,p_balance_name => 'ABP Late Hire'
2429 ,p_dimension_name => 'Assignment Year To Date' );
2430
2431 --
2432 -- Get the average ptp of the prev year
2433 --
2434 IF ( NVL(p_late_hire_indicator,0) = 1 OR l_py_late_hire_ind > 0) THEN
2435 l_avg_prev_year_ptp := get_bal_val_de
2436 (p_business_group_id => p_business_group_id
2437 ,p_assignment_id => p_assignment_id
2438 ,p_date_earned => p_date_earned
2439 ,p_start_date => l_beg_of_last_year
2440 ,p_end_date => l_end_of_last_year
2441 ,p_payroll_id => p_payroll_id
2442 ,p_balance_name => 'ABP Average Part Time Percentage'
2443 ,p_dimension_name => 'NL Assignment ABP Year To Date Dimension');
2444 ELSE
2445 l_avg_prev_year_ptp := pqp_pension_functions.get_bal_val
2446 (p_business_group_id => p_business_group_id
2447 ,p_assignment_id => p_assignment_id
2448 ,p_effective_date => l_end_of_last_year
2449 ,p_balance_name => 'ABP Average Part Time Percentage'
2450 ,p_dimension_name => 'Assignment Year To Date' );
2451 END IF;
2452
2453 --
2454 -- Get the retro ptp of the prev year
2455 --
2456 IF ( NVL(p_late_hire_indicator,0) = 1 OR l_py_late_hire_ind > 0) THEN
2457 l_retro_avg_ptp := get_bal_val_de
2458 (p_business_group_id => p_business_group_id
2459 ,p_assignment_id => p_assignment_id
2460 ,p_date_earned => p_date_earned
2461 ,p_start_date => l_beg_of_last_year
2462 ,p_end_date => l_end_of_last_year
2463 ,p_payroll_id => p_payroll_id
2464 ,p_balance_name => 'Retro ABP Part Time Percentage'
2465 ,p_dimension_name => 'NL Assignment ABP Year To Date Dimension');
2466 ELSE
2467 l_retro_avg_ptp := pqp_pension_functions.get_bal_val
2468 (p_business_group_id => p_business_group_id
2469 ,p_assignment_id => p_assignment_id
2470 ,p_effective_date => l_end_of_last_year
2471 ,p_balance_name => 'Retro ABP Part Time Percentage'
2472 ,p_dimension_name => 'Assignment Year To Date' );
2473 END IF;
2474
2475 l_avg_prev_year_ptp := l_avg_prev_year_ptp/l_max_periods;
2476
2477 l_avg_prev_year_ptp := l_avg_prev_year_ptp + NVL(l_retro_avg_ptp,0);
2478
2479 IF l_avg_prev_year_ptp > 125 THEN
2480 l_prev_max_ptp := 1;
2481 END IF;
2482
2483 l_avg_prev_year_ptp := LEAST(l_avg_prev_year_ptp,125);
2484
2485 l_avg_prev_year_ptp := l_avg_prev_year_ptp/100;
2486
2487 --
2488 --Find the valid org hierarchy version on a particular date
2489 --
2490
2491 --first chk to see if a named hierarchy exists for the BG
2492 OPEN c_find_named_hierarchy;
2493 FETCH c_find_named_hierarchy INTO l_named_hierarchy;
2494 -- if a named hiearchy is found , find the valid version on that date
2495 IF c_find_named_hierarchy%FOUND THEN
2496 CLOSE c_find_named_hierarchy;
2497 -- now find the valid version on that date
2498 OPEN c_find_ver_frm_hierarchy(l_named_hierarchy
2499 ,l_effective_date);
2500 FETCH c_find_ver_frm_hierarchy INTO l_version_id;
2501 --if no valid version is found, try to get it frm the BG
2502 IF c_find_ver_frm_hierarchy%NOTFOUND THEN
2503 CLOSE c_find_ver_frm_hierarchy;
2504 -- find the valid version id from the BG
2505 OPEN c_find_ver_frm_bg(l_effective_date);
2506 FETCH c_find_ver_frm_bg INTO l_version_id;
2507 CLOSE c_find_ver_frm_bg;
2508 -- else a valid version has been found for the named hierarchy
2509 ELSE
2510 CLOSE c_find_ver_frm_hierarchy;
2511 END IF;
2512 -- else find the valid version from BG
2513 ELSE
2514 CLOSE c_find_named_hierarchy;
2515 --now find the version number from the BG
2516 OPEN c_find_ver_frm_bg(l_effective_date);
2517 FETCH c_find_ver_frm_bg INTO l_version_id;
2518 CLOSE c_find_ver_frm_bg;
2519 END IF;
2520 --
2521 -- Check if OHT is to be applied to pension salary.
2522 -- Pass this flag back to the function.
2523 --
2524 OPEN c_asg_oht_choice(l_effective_date);
2525 FETCH c_asg_oht_choice INTO p_oht_correction;
2526 IF c_asg_oht_choice%FOUND THEN
2527 CLOSE c_asg_oht_choice;
2528 ELSIF c_asg_oht_choice%NOTFOUND THEN
2529 -- Check the rule at the org level.
2530 CLOSE c_asg_oht_choice;
2531 OPEN c_get_org_id(l_effective_date);
2532 FETCH c_get_org_id INTO l_org_id;
2533 CLOSE c_get_org_id;
2534 WHILE (l_loop_again = 1)
2535 LOOP
2536 OPEN c_org_oht_choice(l_org_id);
2537 FETCH c_org_oht_choice INTO p_oht_correction;
2538 IF c_org_oht_choice%FOUND THEN
2539 CLOSE c_org_oht_choice;
2540 l_loop_again := 0;
2541 ELSE
2542 CLOSE c_org_oht_choice;
2543 IF l_version_id IS NOT NULL THEN
2544 OPEN c_find_parent_id(c_org_id => l_org_id
2545 ,c_version_id => l_version_id
2546 );
2547 FETCH c_find_parent_id INTO l_org_id;
2548 IF c_find_parent_id%FOUND THEN
2549 CLOSE c_find_parent_id;
2550 ELSE
2551 p_oht_correction := 'N';
2552 CLOSE c_find_parent_id;
2553 l_loop_again := 0;
2554 END IF;
2555 ELSE
2556 p_oht_correction := 'N';
2557 l_loop_again := 0;
2558 END IF;
2559 END IF;
2560 END LOOP;
2561 END IF;
2562 l_loop_again := 1;
2563 --
2564 -- Check the ptp method at the assignment level as of 1 Jan or Hire Date
2565 --
2566 OPEN c_asg_ptp_choice(l_effective_date);
2567 FETCH c_asg_ptp_choice INTO l_ptp_choice;
2568 IF c_asg_ptp_choice%FOUND THEN
2569 CLOSE c_asg_ptp_choice;
2570 IF l_ptp_choice = '0' THEN
2571 l_avg_prev_year_ptp := l_jan_hire_ptp;
2572 IF l_max_ptp = 1 THEN
2573 p_error_message := 'The part time percentage is restricted to a'
2574 ||' maximum of 125.';
2575 l_ret_val := 2;
2576 END IF;
2577 ELSE
2578 IF l_prev_max_ptp = 1 THEN
2579 p_error_message := 'The part time percentage is restricted to a'
2580 ||' maximum of 125.';
2581 l_ret_val := 2;
2582 END IF;
2583 END IF;
2584 ELSE
2585 --
2586 -- Check the method defined at the HR Org
2587 --
2588 CLOSE c_asg_ptp_choice;
2589 OPEN c_get_org_id(l_effective_date);
2590 FETCH c_get_org_id INTO l_org_id;
2591 CLOSE c_get_org_id;
2592 WHILE (l_loop_again = 1)
2593 LOOP
2594 OPEN c_org_ptp_choice(l_org_id);
2595 FETCH c_org_ptp_choice INTO l_ptp_choice;
2596 IF c_org_ptp_choice%FOUND THEN
2597 CLOSE c_org_ptp_choice;
2598 l_loop_again := 0;
2599 IF l_ptp_choice = '0' THEN
2600 l_avg_prev_year_ptp := l_jan_hire_ptp;
2601 IF l_max_ptp = 1 THEN
2602 p_error_message := 'The part time percentage is restricted to a'
2603 ||' maximum of 125.';
2604 l_ret_val := 2;
2605 END IF;
2606 ELSE
2607 IF l_prev_max_ptp = 1 THEN
2608 p_error_message := 'The part time percentage is restricted to a'
2609 ||' maximum of 125.';
2610 l_ret_val := 2;
2611 END IF;
2612 END IF;
2613 ELSE
2614 CLOSE c_org_ptp_choice;
2615 IF l_version_id IS NOT NULL THEN
2616 OPEN c_find_parent_id(c_org_id => l_org_id
2617 ,c_version_id => l_version_id
2618 );
2619 FETCH c_find_parent_id INTO l_org_id;
2620 IF c_find_parent_id%FOUND THEN
2621 CLOSE c_find_parent_id;
2622 ELSE
2623 CLOSE c_find_parent_id;
2624 l_loop_again := 0;
2625 IF l_prev_max_ptp = 1 THEN
2626 p_error_message := 'The part time percentage is restricted to a'
2627 ||' maximum of 125.';
2628 l_ret_val := 2;
2629 END IF;
2630 END IF;
2631 ELSE
2632 l_loop_again := 0;
2633 IF l_prev_max_ptp = 1 THEN
2634 p_error_message := 'The part time percentage is restricted to a'
2635 ||' maximum of 125.';
2636 l_ret_val := 2;
2637 END IF;
2638 END IF;
2639 END IF;
2640 END LOOP;
2641 END IF;
2642
2643 -- If nothing has been defined at the org level or
2644 -- at the assignment level, use average of the previous
2645 -- year.
2646
2647 --
2648 -- Get the FT Non Rec Bonus of the prev Year
2649 --
2650 IF ( NVL(p_late_hire_indicator,0) = 1 OR l_py_late_hire_ind > 0) THEN
2651 l_ft_non_rec_bonus := get_bal_val_de
2652 (p_business_group_id => p_business_group_id
2653 ,p_assignment_id => p_assignment_id
2654 ,p_date_earned => p_date_earned
2655 ,p_start_date => l_beg_of_last_year
2656 ,p_end_date => l_end_of_last_year
2657 ,p_payroll_id => p_payroll_id
2658 ,p_balance_name => 'ABP Full Time Non Recurring Bonus'
2659 ,p_dimension_name => 'NL Assignment ABP Year To Date Dimension');
2660 ELSE
2661 l_ft_non_rec_bonus := pqp_pension_functions.get_bal_val
2662 (p_business_group_id => p_business_group_id
2663 ,p_assignment_id => p_assignment_id
2664 ,p_effective_date => l_end_of_last_year
2665 ,p_balance_name => 'ABP Full Time Non Recurring Bonus'
2666 ,p_dimension_name => 'Assignment Year To Date' );
2667 END IF;
2668 --
2669 -- Get the FT Non Rec Bonus of the prev Year for holiday allowance
2670 --
2671 IF ( NVL(p_late_hire_indicator,0) = 1 OR l_py_late_hire_ind > 0) THEN
2672 l_ft_non_rec_bonus_h := get_bal_val_de
2673 (p_business_group_id => p_business_group_id
2674 ,p_assignment_id => p_assignment_id
2675 ,p_date_earned => p_date_earned
2676 ,p_start_date => l_beg_of_last_year
2677 ,p_end_date => l_end_of_last_year
2678 ,p_payroll_id => p_payroll_id
2679 ,p_balance_name => 'ABP Full Time Non Recurring Bonus For Holiday Allowance'
2680 ,p_dimension_name => 'NL Assignment ABP Year To Date Dimension');
2681 ELSE
2682 l_ft_non_rec_bonus_h := pqp_pension_functions.get_bal_val
2683 (p_business_group_id => p_business_group_id
2684 ,p_assignment_id => p_assignment_id
2685 ,p_effective_date => l_end_of_last_year
2686 ,p_balance_name => 'ABP Full Time Non Recurring Bonus For Holiday Allowance'
2687 ,p_dimension_name => 'Assignment Year To Date' );
2688 END IF;
2689 --
2690 -- Get the PT Non Rec Bonus of the prev Year
2691 --
2692 IF ( NVL(p_late_hire_indicator,0) = 1 OR l_py_late_hire_ind > 0) THEN
2693 l_pt_non_rec_bonus := get_bal_val_de
2694 (p_business_group_id => p_business_group_id
2695 ,p_assignment_id => p_assignment_id
2696 ,p_date_earned => p_date_earned
2697 ,p_start_date => l_beg_of_last_year
2698 ,p_end_date => l_end_of_last_year
2699 ,p_payroll_id => p_payroll_id
2700 ,p_balance_name => 'ABP Part Time Non Recurring Bonus'
2701 ,p_dimension_name => 'NL Assignment ABP Year To Date Dimension');
2702 ELSE
2703 l_pt_non_rec_bonus := pqp_pension_functions.get_bal_val
2704 (p_business_group_id => p_business_group_id
2705 ,p_assignment_id => p_assignment_id
2706 ,p_effective_date => l_end_of_last_year
2707 ,p_balance_name => 'ABP Part Time Non Recurring Bonus'
2708 ,p_dimension_name => 'Assignment Year To Date' );
2709 END IF;
2710
2711 IF l_avg_prev_year_ptp = 0 THEN
2712 l_pt_non_rec_bonus := 0;
2713 ELSE
2714 l_pt_non_rec_bonus := l_pt_non_rec_bonus/l_avg_prev_year_ptp;
2715 END IF;
2716
2717 --
2718 -- Get the PT Non Rec Bonus of the prev Year for holiday allowance
2719 --
2720 IF ( NVL(p_late_hire_indicator,0) = 1 OR l_py_late_hire_ind > 0) THEN
2721 l_pt_non_rec_bonus_h := get_bal_val_de
2722 (p_business_group_id => p_business_group_id
2723 ,p_assignment_id => p_assignment_id
2724 ,p_date_earned => p_date_earned
2725 ,p_start_date => l_beg_of_last_year
2726 ,p_end_date => l_end_of_last_year
2727 ,p_payroll_id => p_payroll_id
2728 ,p_balance_name => 'ABP Part Time Non Recurring Bonus For Holiday Allowance'
2729 ,p_dimension_name => 'NL Assignment ABP Year To Date Dimension');
2730 ELSE
2731 l_pt_non_rec_bonus_h := pqp_pension_functions.get_bal_val
2732 (p_business_group_id => p_business_group_id
2733 ,p_assignment_id => p_assignment_id
2734 ,p_effective_date => l_end_of_last_year
2735 ,p_balance_name => 'ABP Part Time Non Recurring Bonus For Holiday Allowance'
2736 ,p_dimension_name => 'Assignment Year To Date' );
2737 END IF;
2738
2739 IF l_avg_prev_year_ptp = 0 THEN
2740 l_pt_non_rec_bonus_h := 0;
2741 p_error_message := p_error_message || 'The previous year non-recurring '
2742 ||'bonus is 0'
2743 ||' since the average part time percentage of the '
2744 ||'previous year is 0.';
2745 l_ret_val := 2;
2746 ELSE
2747 l_pt_non_rec_bonus_h := l_pt_non_rec_bonus_h/l_avg_prev_year_ptp;
2748 END IF;
2749
2750 --
2751 -- EOY Bonus Calculation
2752 -- This section will calculate the end of year bonus
2753 -- based on the EOY Bonus percentage specified at the
2754 -- org level. These will be derived from the following
2755 -- three balances.
2756 --
2757 -- ABP Scale Salary For End Of Year Bonus
2758 -- ABP Full Time Recurring Bonus For End Of Year Bonus
2759 -- ABP Part Time Recurring Bonus For End Of Year Bonus
2760 --
2761
2762 --
2763 -- Call the function to derive EOY Bonus
2764 -- percentage from the org level
2765 --
2766
2767 l_ret_val := pqp_nl_abp_functions.get_eoy_bonus_percentage
2768 (p_date_earned => p_date_earned
2769 ,p_business_group_id => p_business_group_id
2770 ,p_assignment_id => p_assignment_id
2771 ,p_eoy_bonus_percentage => l_eoy_bonus_perc
2772 );
2773
2774 IF l_ret_val <> 0 THEN
2775 l_eoy_bonus_perc := 0;
2776 END IF;
2777
2778 --
2779 -- Calculate the EOY Bonus that needs to be included for
2780 -- ABP Pension Salary calculations
2781 --
2782 l_eoy_bonus_org := (l_scale_eoy_bonus
2783 + l_ft_rec_bonus_eoy_bonus
2784 + l_pt_rec_bonus_eoy_bonus)
2785 * l_eoy_bonus_perc/100;
2786
2787 -- Check to make sure that the end of year bonus
2788 -- calculated is at least the min of what is defined for
2789 -- the collective agreement
2790
2791 -- Get the CAG Name
2792 OPEN c_cag_name (c_asg_id => p_assignment_id
2793 ,c_eff_date => l_effective_date) ;
2794
2795 FETCH c_cag_name INTO l_cag_name;
2796
2797 IF c_cag_name%FOUND THEN
2798
2799 -- We found a CAG at the asg level . Now get the Min End Of
2800 -- Year Bonus for this CAG from the UDT.
2801 Begin
2802 l_min_eoy_bonus_char := hruserdt.get_table_value
2803 (
2804 p_bus_group_id => p_business_group_id
2805 ,p_table_name => 'PQP_NL_MIN_END_OF_YEAR_BONUS'
2806 ,p_col_name => 'Minimum End Of Year Bonus'
2807 ,p_row_value => l_cag_name
2808 ,p_effective_date => l_effective_date
2809 );
2810
2811 l_min_eoy_bonus := fnd_number.canonical_to_number(NVL(l_min_eoy_bonus_char,'0'));
2812
2813 EXCEPTION
2814 WHEN NO_DATA_FOUND THEN
2815 --l_message_flag :='e';
2816 l_min_eoy_bonus :=0;
2817 End;
2818
2819 ELSE
2820 -- Collective agreement has not been defined at the ASG level
2821 -- set EOY bonus to 0 .
2822 l_min_eoy_bonus := 0;
2823 END IF;
2824
2825 CLOSE c_cag_name;
2826
2827 -- Calculate total EOY Bonus
2828 l_eoy_bonus_ca := ( nvl(l_ft_eoy_bonus,0) + nvl(l_pt_eoy_bonus,0) + l_eoy_bonus_org)*l_max_periods;
2829
2830 IF l_eoy_bonus_ca < l_min_eoy_bonus THEN
2831 p_error_message1 :=p_error_message1|| 'Min. end of year bonus for the collective agreement'
2832 ||' was used for Pension Salary calculation ';
2833 l_ret_val := 2;
2834 END IF;
2835
2836
2837 l_eoy_bonus_ca := GREATEST(l_eoy_bonus_ca,nvl(l_min_eoy_bonus,0));
2838
2839
2840
2841 --
2842 -- Holiday Allowance Calculation
2843 --
2844 OPEN c_global_cur('PQP_NL_HOLIDAY_ALLOWANCE_PERCENT');
2845
2846 FETCH c_global_cur INTO l_holiday_allow_per;
2847 IF c_global_cur%NOTFOUND THEN
2848 l_holiday_allow_per := 0;
2849 END IF;
2850
2851 CLOSE c_global_cur;
2852
2853
2854 l_holiday_allowance := (nvl(l_scale_salary_h,0) +
2855 nvl(l_ft_rec_bonus_h,0) +
2856 nvl(l_pt_rec_bonus_h,0) +
2857 nvl(l_ft_eoy_bonus_h,0) +
2858 nvl(l_pt_eoy_bonus_h,0)) * l_max_periods * nvl(l_holiday_allow_per,0)/100 ;
2859
2860 -- Check to make sure that the holiday allowance
2861 -- calculated is at least the min of what is defined for
2862 -- the collective agreement
2863
2864 -- Get the CAG Name
2865 OPEN c_cag_name (c_asg_id => p_assignment_id
2866 ,c_eff_date => l_effective_date) ;
2867
2868 FETCH c_cag_name INTO l_cag_name;
2869
2870 IF c_cag_name%FOUND THEN
2871
2872 -- We found a CAG at the asg level . Now get the Min holiday
2873 -- allowance for this CAG from the UDT.
2874 Begin
2875 l_min_holiday_char := hruserdt.get_table_value
2876 (
2877 p_bus_group_id => p_business_group_id
2878 ,p_table_name => 'PQP_NL_MIN_HOLIDAY_ALLOWANCE'
2879 ,p_col_name => 'Minimum Holiday Allowance'
2880 ,p_row_value => l_cag_name
2881 ,p_effective_date => l_effective_date
2882 );
2883
2884 l_min_holiday_allow := fnd_number.canonical_to_number(NVL(l_min_holiday_char,'0'));
2885
2886 EXCEPTION
2887 WHEN NO_DATA_FOUND THEN
2888 l_min_holiday_allow := 0;
2889
2890 /* If(l_message_flag = 'e') THEN
2891 l_message_flag :='b';
2892 ELSE
2893 l_message_flag :='h';
2894 END IF;
2895 RAISE UDT_CONTAINS_NO_DATA; */
2896 -- Code commented out by vjhanak
2897 END;
2898
2899 /* IF(l_message_flag = 'e') THEN
2900 RAISE UDT_CONTAINS_NO_DATA;
2901 END IF;
2902 */
2903
2904 ELSE
2905 -- Collective agreement has not been defined at the ASG level
2906 -- set holiday allowance to 0
2907 l_min_holiday_allow := 0;
2908 END IF;
2909
2910 CLOSE c_cag_name;
2911
2912 l_holiday_allowance := nvl(l_holiday_allowance,0) +
2913 (nvl(l_ft_non_rec_bonus_h,0) +
2914 nvl(l_pt_non_rec_bonus_h,0) ) * nvl(l_holiday_allow_per,0)/100;
2915
2916
2917 IF l_holiday_allowance < l_min_holiday_allow THEN
2918 p_error_message2 :=p_error_message2||
2919 'Min. holiday allowance for the collective agreement'
2920 ||'was used for Pension Salary calculation.';
2921 l_ret_val := 2;
2922 END IF;
2923
2924
2925 l_holiday_allowance := GREATEST(l_holiday_allowance,nvl(l_min_holiday_allow,0));
2926
2927 p_salary_balance_value := (NVL(l_scale_salary,0) +
2928 NVL(l_ft_rec_bonus,0) +
2929 NVL(l_pt_rec_bonus,0)
2930 ) * l_max_periods +
2931 NVL(l_holiday_allowance,0) +
2932 NVL(l_eoy_bonus_ca ,0)+
2933 NVL(l_ft_non_rec_bonus,0) +
2934 NVL(l_pt_non_rec_bonus,0);
2935
2936 --
2937 -- Get the age of the EE
2938 --
2939 l_per_age := pay_nl_tax_pkg.get_age_system_date(p_assignment_id,l_begin_of_year_date);
2940
2941 -- Check the change in pension salary compared to the previous year.
2942 -- a max change of 2% plus the inflation percentage should be
2943 -- applied. This is for EE equal to or older than 58 as of
2944 -- 1 January
2945 /*
2946 IF l_per_age >= 58 THEN
2947
2948 --
2949 -- Get the Pension Salary of last year to compare it with this years
2950 --
2951 IF ( NVL(p_late_hire_indicator,0) = 1 OR l_py_late_hire_ind > 0) THEN
2952 l_pension_sal_prev_yr := get_bal_val_de
2953 (p_business_group_id => p_business_group_id
2954 ,p_assignment_id => p_assignment_id
2955 ,p_date_earned => p_date_earned
2956 ,p_start_date => l_beg_of_last_year
2957 ,p_end_date => l_end_of_last_year
2958 ,p_payroll_id => p_payroll_id
2959 ,p_balance_name => 'ABP Pension Salary'
2960 ,p_dimension_name => 'NL Assignment ABP Year To Date Dimension');
2961 ELSE
2962 l_pension_sal_prev_yr := pqp_pension_functions.get_bal_val
2963 (p_business_group_id => p_business_group_id
2964 ,p_assignment_id => p_assignment_id
2965 ,p_effective_date => l_end_of_last_year
2966 ,p_balance_name => 'ABP Pension Salary'
2967 ,p_dimension_name => 'Assignment Year To Date' );
2968 END IF;
2969 --
2970 -- Get the inflation percentage
2971 --
2972 OPEN c_global_cur('PQP_NL_INFLATION_PERCENT');
2973
2974 FETCH c_global_cur INTO l_inflation_per;
2975 IF c_global_cur%NOTFOUND THEN
2976 l_inflation_per := 0;
2977 END IF;
2978
2979 l_inflation_per := nvl(l_inflation_per,0) + 2;
2980
2981 --
2982 -- Add this percentage to the derived ABP Year income
2983 --
2984 IF nvl(l_pension_sal_prev_yr,0) <> 0 THEN
2985 l_max_inflated_sal := l_pension_sal_prev_yr +
2986 l_pension_sal_prev_yr * l_inflation_per/100;
2987
2988 IF p_salary_balance_value > l_max_inflated_sal THEN
2989 p_error_message2 := p_error_message2
2990 ||'The inflation percentage has been used to recalculate the'
2991 ||' pension salary.';
2992 l_ret_val := 2;
2993 p_salary_balance_value := l_max_inflated_sal;
2994 END IF;
2995 END IF;
2996
2997 END IF;
2998 */
2999 RETURN l_ret_val;
3000
3001 EXCEPTION
3002
3003 WHEN UDT_CONTAINS_NO_DATA THEN
3004 p_salary_balance_value := 0;
3005
3006 IF(l_message_flag = 'h') THEN
3007 p_error_message := 'Min. holiday allowance has not been defined'
3008 ||' for the collective agreement at the assignment';
3009 ELSIF (l_message_flag = 'e') THEN
3010 p_error_message := 'Min. end of year bonus has not been defined'
3011 ||' for the collective agreement at the assignment';
3012 ELSIF (l_message_flag = 'b') THEN
3013 p_error_message := 'Min. holiday allowance and end of year bonus'
3014 ||' have not been defined for the collective agreement at the assignment';
3015 END IF;
3016
3017 RETURN 1;
3018
3019 WHEN OTHERS THEN
3020
3021 p_salary_balance_value := 0;
3022 p_error_message := 'Error occured while deriving the ABP Pension Salary'
3023 ||' value : '||SQLERRM;
3024 RETURN 1;
3025
3026 END get_abp_pension_salary;
3027
3028 -- ----------------------------------------------------------------------------
3029 -- |-----------------------< get_pension_salary >-----------------------------|
3030 -- ----------------------------------------------------------------------------
3031 --
3032 function get_pension_salary
3033 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
3034 ,p_date_earned in date
3035 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3036 ,p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
3037 ,p_salary_balance_name in varchar2
3038 ,p_payroll_period in varchar2
3039 ,p_salary_balance_value out nocopy number
3040 ,p_error_message out nocopy varchar2
3041 ,p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE default -99
3042 ) return number is
3043
3044 l_proc_name varchar2(150) := g_proc_name || 'get_pension_salary';
3045 l_hire_date date;
3046 l_date_earned date;
3047 l_run_year number;
3048 l_begin_of_year_date date;
3049 l_effective_date date;
3050 l_error_message varchar2(100);
3051 l_defined_bal_id number;
3052 l_payroll_id pay_payrolls_f.payroll_id%type;
3053 l_ass_act_id pay_assignment_actions.assignment_action_id%type := -1;
3054 l_cur_payroll_id pay_payrolls_f.payroll_id%type;
3055 l_period_start_dt per_time_periods.start_date%type;
3056 l_period_end_dt per_time_periods.end_date%type;
3057 l_time_period_id number;
3058 l_is_abp_pt number := 0;
3059 l_participation_st_dt date;
3060 l_ret_val number;
3061 l_dimension_name pay_balance_dimensions.dimension_name%TYPE := 'Assignment Period To Date';
3062 l_error_status CHAR:='0';
3063 -- Cursor to get the hire date of the person
3064 CURSOR c_hire_dt_cur(c_asg_id IN NUMBER) IS
3065 SELECT max(date_start)
3066 FROM per_all_assignments_f asg
3067 ,per_periods_of_service pps
3068 WHERE pps.person_id = asg.person_id
3069 AND asg.assignment_id = c_asg_id
3070 AND pps.business_group_id = p_business_group_id
3071 AND date_start <= p_date_earned;
3072
3073 -- Cursor to get the defined_balance_id
3074 CURSOR c_def_bal_cur(c_dim_name IN pay_balance_dimensions.dimension_name%TYPE) IS
3075 select pdb.defined_balance_id
3076 from pay_balance_types pbt
3077 ,pay_balance_dimensions pbd
3078 ,pay_defined_balances pdb
3079 where balance_name = p_salary_balance_name
3080 and pbd.dimension_name = c_dim_name
3081 and pdb.balance_type_id = pbt.balance_type_id
3082 and pbd.balance_dimension_id = pdb.balance_dimension_id
3083 and pbd.legislation_code = 'NL';
3084
3085 -- Cursor to get the time_period
3086 CURSOR c_per_time_cur (c_payroll_id IN NUMBER
3087 ,c_effective_date IN DATE) IS
3088 SELECT time_period_id
3089 ,start_date
3090 ,end_date
3091 FROM per_time_periods
3092 WHERE payroll_id = c_payroll_id
3093 AND trunc(start_date) >= c_effective_date
3094 ORDER BY start_date;
3095
3096 -- Cursor to get the payroll_id as of a particular date
3097 CURSOR c_payroll_cur (c_effective_date IN DATE ) IS
3098 SELECT payroll_id
3099 FROM per_all_assignments_f
3100 WHERE assignment_id = p_assignment_id
3101 AND trunc(c_effective_date) BETWEEN effective_start_date
3102 AND effective_end_date;
3103
3104 -- Cursor to check if a payroll has already been run
3105 CURSOR c_get_asg_act_cur (c_time_period_id IN NUMBER
3106 --,c_payroll_id IN NUMBER
3107 ,c_period_start IN DATE
3108 ,c_period_end IN DATE) IS
3109 SELECT paa.assignment_action_id
3110 FROM pay_payroll_actions ppa
3111 ,pay_assignment_actions paa
3112 where ppa.payroll_action_id = paa.payroll_action_id
3113 and ppa.time_period_id = c_time_period_id
3114 --and payroll_id = c_payroll_id
3115 and paa.assignment_id = p_assignment_id
3116 and ppa.action_type in ('R','Q')
3117 and ppa.action_status = 'C'
3118 and paa.action_status = 'C'
3119 and ppa.date_earned between c_period_start and c_period_end
3120 and rownum = 1;
3121
3122 --Cursor to check if the pension type is an ABP Pension Type
3123 CURSOR c_is_abp_pt IS
3124 SELECT 1
3125 FROM pqp_pension_types_f
3126 WHERE pension_type_id = p_pension_type_id
3127 AND special_pension_type_code = 'ABP';
3128
3129 BEGIN
3130
3131 hr_utility.set_location('Entering : '||l_proc_name, 10);
3132
3133 l_date_earned := p_date_earned;
3134
3135 --check if the pension type is an ABP Pension Type
3136 -- if so, find the participation start date
3137 IF p_pension_type_id <> -99 THEN
3138 OPEN c_is_abp_pt;
3139 FETCH c_is_abp_pt INTO l_is_abp_pt;
3140 IF c_is_abp_pt%FOUND THEN
3141 CLOSE c_is_abp_pt;
3142 ELSE
3143 l_is_abp_pt := 0;
3144 CLOSE c_is_abp_pt;
3145 END IF;
3146 END IF;
3147 -- if an invalid PT ID has been passed down, error out
3148 IF p_pension_type_id = -1 THEN
3149 p_error_message := p_error_message||'Error occurred while fetching the pension salary';
3150 p_error_message := p_error_message||'cannot find a value for the pension type id';
3151 RETURN 1;
3152 END IF;
3153
3154 -- if the pension type is an ABP PT, find the participation start date
3155 -- from the ASG/ORG EITs
3156 IF l_is_abp_pt = 1 THEN
3157 l_dimension_name := 'Assignment Year To Date';
3158 l_ret_val := PQP_NL_ABP_FUNCTIONS.get_participation_date
3159 (p_assignment_id => p_assignment_id
3160 ,p_date_earned => p_date_earned
3161 ,p_business_group_id => p_business_group_id
3162 ,p_pension_type_id => p_pension_type_id
3163 ,p_start_date => l_participation_st_dt
3164 );
3165 IF l_ret_val = 1 THEN
3166 p_error_message := p_error_message||'Error occurred while trying to find the participation';
3167 p_error_message := p_error_message||' start date for the assignment';
3168 RETURN 1;
3169 END IF;
3170 END IF;
3171
3172 l_run_year := get_run_year (l_date_earned
3173 ,l_error_message );
3174
3175 --
3176 -- Get the date for 1 JAN of the run year
3177 --
3178 l_begin_of_year_date := TO_DATE('01/01/'||to_char(l_run_year),'DD/MM/YYYY');
3179
3180 --
3181 -- Get the current payroll_id for the person
3182 --
3183 SELECT payroll_id
3184 INTO l_payroll_id
3185 FROM per_all_assignments_f
3186 WHERE assignment_id = p_assignment_id
3187 AND trunc(p_date_earned) BETWEEN effective_start_date
3188 AND effective_end_date;
3189
3190 hr_utility.set_location('Fetched l_cur_payroll_id : '||to_char(l_cur_payroll_id),20);
3191
3192 --
3193 -- Get the hire date
3194 --
3195 OPEN c_hire_dt_cur (p_assignment_id);
3196 FETCH c_hire_dt_cur INTO l_hire_date;
3197 IF c_hire_dt_cur%FOUND THEN
3198 CLOSE c_hire_dt_cur;
3199
3200 IF l_is_abp_pt = 0 THEN
3201 -- NON-ABP Pension Types
3202 -- The effective date is now the valid assignment start date
3203 l_effective_date := PQP_NL_ABP_FUNCTIONS.GET_VALID_START_DATE(p_assignment_id,p_date_earned,l_error_status,l_error_message);
3204 IF (l_error_status = trim(to_char(1,'9'))) Then
3205 fnd_message.set_name('PQP',l_error_message);
3206 p_error_message :='Error' || fnd_message.get();
3207 RETURN 0;
3208 End IF;
3209 ELSE
3210 -- ABP Pension Types
3211 -- The effective date is now the greatest of 1 Jan of the year
3212 -- the hire date and the participation start date
3213 l_effective_date := GREATEST(l_begin_of_year_date,l_hire_date,l_participation_st_dt);
3214 END IF;
3215
3216 hr_utility.set_location('Fetched l_effective_date : '
3217 ||to_char(l_effective_date),30);
3218
3219 -- For the payroll id derived from above, get the time_period_id,
3220 -- start and end dates for the period which has the start_date
3221 -- greater than or equal to l_effective_date
3222 ELSE
3223 CLOSE c_hire_dt_cur;
3224 p_error_message := 'Error: Unable to find the hire date for the person ';
3225 RETURN 0;
3226 END IF; -- Hire date found
3227
3228 FOR temp_rec IN c_per_time_cur (l_payroll_id,l_effective_date)
3229 LOOP
3230 hr_utility.set_location('Fetched l_time_period_id : '
3231 ||to_char(l_time_period_id),50);
3232
3233 -- Check if there is a completed payroll run for the time
3234 -- period, payroll and assignment combination.
3235 -- If a payroll run is complete then get the balance using that
3236 -- assignment_action_id
3237 OPEN c_get_asg_act_cur (temp_rec.time_period_id
3238 ,temp_rec.start_date
3239 ,temp_rec.end_date );
3240 FETCH c_get_asg_act_cur INTO l_ass_act_id;
3241
3242 IF c_get_asg_act_cur%FOUND THEN
3243 CLOSE c_get_asg_act_cur;
3244 EXIT;
3245 END IF;
3246
3247 CLOSE c_get_asg_act_cur;
3248
3249 END LOOP;
3250
3251 --
3252 -- If a valid assignment action id was found then call the get_balance_pkg
3253 -- else return 2 so that the formula will get the current value of the
3254 -- balance.
3255 --
3256 IF l_ass_act_id = -1 THEN
3257 RETURN 2;
3258 ELSE
3259 OPEN c_def_bal_cur(l_dimension_name);
3260 FETCH c_def_bal_cur INTO l_defined_bal_id;
3261 IF c_def_bal_cur%FOUND THEN
3262 -- Get the value of the balance as of the date calculated above.
3263 p_salary_balance_value :=
3264 pay_balance_pkg.get_value(p_defined_balance_id => l_defined_bal_id
3265 ,p_assignment_action_id => l_ass_act_id);
3266 CLOSE c_def_bal_cur;
3267 RETURN 0;
3268 ELSE
3269 p_error_message := 'Error: Unable to find the defined balance';
3270 p_error_message := p_error_message||' Balance Name: '||p_salary_balance_name ;
3271 p_error_message := p_error_message||' Dimension Name: Assignment Period To Date';
3272 p_error_message := p_error_message||' Please make sure that balance and dimension exist';
3273 CLOSE c_def_bal_cur;
3274 RETURN 1;
3275 END IF;
3276 END IF;
3277
3278 END get_pension_salary;
3279
3280 -- ----------------------------------------------------------------------------
3281 -- |-----------------------< get_pension_type_eligibility >-------------------|
3282 -- ----------------------------------------------------------------------------
3283 --
3284
3285 function get_pension_type_eligibility
3286 (p_business_group_id IN NUMBER
3287 ,p_date_earned IN DATE
3288 ,p_assignment_id IN NUMBER
3289 ,p_pension_type_id IN NUMBER
3290 ,p_eligibility_flag OUT NOCOPY VARCHAR2
3291 ,p_error_message OUT NOCOPY VARCHAR2
3292 ) return NUMBER IS
3293
3294 --Cursor to find the org id from the assignment id
3295 CURSOR c_find_org_id IS
3296 SELECT organization_id
3297 FROM per_all_assignments_f
3298 WHERE assignment_id = p_assignment_id
3299 AND trunc(p_date_earned) between effective_start_date and effective_end_date
3300 AND business_group_id = p_business_group_id;
3301
3302 --Cursor to chk if the organization has any overridden pension types attached
3303 CURSOR c_is_any_pt_assigned(c_org_id in number) IS
3304 select 1 from dual
3305 where exists (select 1 from hr_organization_information
3306 where org_information_context = 'PQP_NL_ER_PENSION_TYPES'
3307 AND organization_id = c_org_id
3308 AND p_date_earned between fnd_date.canonical_to_date(org_information4)
3309 and fnd_date.canonical_to_date(nvl(org_information5,
3310 fnd_date.date_to_canonical(hr_api.g_eot)))
3311 );
3312
3313 --Cursor to find the parent id from the org id
3314 CURSOR c_find_parent_id(c_org_id in number
3315 ,c_version_id in number) IS
3316 select organization_id_parent
3317 from per_org_structure_elements
3318 where organization_id_child = c_org_id
3319 AND org_structure_version_id = c_version_id
3320 AND business_group_id = p_business_group_id;
3321
3322 --Cursor to find if the pension type is assigned to this org
3323 CURSOR c_is_pen_type_assigned(c_org_id in number) Is
3324 SELECT 1
3325 FROM hr_organization_information hoi,
3326 pqp_pension_types_f pty
3327 WHERE hoi.org_information_context = 'PQP_NL_ER_PENSION_TYPES'
3328 AND hoi.org_information2 = TO_CHAR(p_pension_type_id)
3329 AND hoi.organization_id = c_org_id
3330 AND pty.pension_type_id = p_pension_type_id
3331 AND p_date_earned between pty.effective_start_date
3332 and pty.effective_end_date
3333 AND p_date_earned between fnd_date.canonical_to_date(hoi.org_information4)
3334 and fnd_date.canonical_to_date(nvl(hoi.org_information5,
3335 fnd_date.date_to_canonical(hr_api.g_eot))
3336 );
3337 --Cursor to find the pension type name from the pension type id
3338 CURSOR c_find_pen_type_name Is
3339 SELECT pension_type_name
3340 FROM pqp_pension_types_f
3341 WHERE pension_type_id = p_pension_type_id
3342 AND rownum = 1;
3343
3344 --Cursor to find if the pension type is valid as of the date earned
3345 CURSOR c_find_pen_type_valid Is
3346 SELECT 1
3347 FROM pqp_pension_types_f
3348 WHERE pension_type_id = p_pension_type_id
3349 AND trunc(p_date_earned) between effective_start_date and effective_end_date
3350 AND business_group_id = p_business_group_id;
3351
3352 --Cursor to find the named hierarchy associated with the BG
3353 CURSOR c_find_named_hierarchy Is
3354 select org_information1
3355 from hr_organization_information
3356 where organization_id = p_business_group_id
3357 and org_information_context = 'NL_BG_INFO';
3358
3359 --Cursor to find the valid version id for the particular named hierarchy
3360 CURSOR c_find_ver_frm_hierarchy(c_hierarchy_id in Number) Is
3361 select ORG_STRUCTURE_VERSION_ID
3362 from per_org_structure_versions_v
3363 where organization_structure_id = c_hierarchy_id
3364 and p_date_earned between date_from
3365 and nvl(date_to,hr_api.g_eot);
3366
3367 --Cursor to find the valid version id for a particular business group
3368 CURSOR c_find_ver_frm_bg Is
3369 select ORG_STRUCTURE_VERSION_ID
3370 from per_org_structure_versions_v
3371 where business_group_id = p_business_group_id
3372 and p_date_earned between date_from
3373 and nvl( date_to,hr_api.g_eot);
3374
3375
3376 l_org_id NUMBER;
3377 l_ret_value NUMBER;
3378 l_is_pen_type_assigned NUMBER;
3379 l_loop_again NUMBER;
3380 l_valid_pt NUMBER;
3381 l_named_hierarchy NUMBER;
3382 l_version_id NUMBER default null;
3383 l_pen_type_name VARCHAR2(240);
3384
3385 BEGIN
3386 --Find the pension type name from the pension type id
3387 OPEN c_find_pen_type_name;
3388 FETCH c_find_pen_type_name INTO l_pen_type_name;
3389 IF c_find_pen_type_name%NOTFOUND THEN
3390 p_error_message := 'Unable to find the details for the Pension Type';
3391 p_error_message := p_error_message||'. Pension Type Id = '||to_char(p_pension_type_id);
3392 CLOSE c_find_pen_type_name;
3393 return 1;
3394 END IF;
3395 CLOSE c_find_pen_type_name;
3396
3397 --
3398 -- Make sure that the pension Type is valid as of the date earned
3399 --
3400 OPEN c_find_pen_type_valid;
3401 FETCH c_find_pen_type_valid INTO l_valid_pt;
3402 IF c_find_pen_type_valid%NOTFOUND THEN
3403 p_error_message := 'Pension Type : '||l_pen_type_name;
3404 p_error_message:= p_error_message||' is not valid as of '||to_char(p_date_earned);
3405 p_error_message := p_error_message||' Please check the validity of the Pension Type.';
3406 CLOSE c_find_pen_type_valid;
3407 return 1;
3408 END IF;
3409 CLOSE c_find_pen_type_valid;
3410
3411 --
3412 --Find the valid org hierarchy version on a particular date
3413 --
3414
3415 --first chk to see if a named hierarchy exists for the BG
3416 OPEN c_find_named_hierarchy;
3417 FETCH c_find_named_hierarchy INTO l_named_hierarchy;
3418 -- if a named hiearchy is found , find the valid version on that date
3419 IF c_find_named_hierarchy%FOUND THEN
3420 CLOSE c_find_named_hierarchy;
3421 -- now find the valid version on that date
3422 OPEN c_find_ver_frm_hierarchy(l_named_hierarchy);
3423 FETCH c_find_ver_frm_hierarchy INTO l_version_id;
3424 --if no valid version is found, try to get it frm the BG
3425 IF c_find_ver_frm_hierarchy%NOTFOUND THEN
3426 CLOSE c_find_ver_frm_hierarchy;
3427 -- find the valid version id from the BG
3428 OPEN c_find_ver_frm_bg;
3429 FETCH c_find_ver_frm_bg INTO l_version_id;
3430 CLOSE c_find_ver_frm_bg;
3431 -- else a valid version has been found for the named hierarchy
3432 ELSE
3433 CLOSE c_find_ver_frm_hierarchy;
3434 END IF;
3435 -- else find the valid version from BG
3436 ELSE
3437 CLOSE c_find_named_hierarchy;
3438 --now find the version number from the BG
3439 OPEN c_find_ver_frm_bg;
3440 FETCH c_find_ver_frm_bg INTO l_version_id;
3441 CLOSE c_find_ver_frm_bg;
3442 END IF;
3443 OPEN c_find_org_id;
3444 FETCH c_find_org_id INTO l_org_id;
3445 IF c_find_org_id%FOUND THEN -- if the org id to which this person is in is found
3446
3447 CLOSE c_find_org_id;
3448 l_loop_again := 1;
3449 -- while the topmost org is not reached or while the pension type is not attached to any parent org
3450 while (l_loop_again = 1) loop
3451
3452 -- first chk to see if there exist any overridden rows effective the date earned
3453 OPEN c_is_any_pt_assigned(l_org_id);
3454 FETCH c_is_any_pt_assigned INTO l_is_pen_type_assigned;
3455 -- if overridden rows exist
3456 IF c_is_any_pt_assigned%FOUND THEN
3457
3458 CLOSE c_is_any_pt_assigned;
3459 -- chk to see if the pension type is attached to this org and the pension type is valid on this date
3460 OPEN c_is_pen_type_assigned(l_org_id);
3461 FETCH c_is_pen_type_assigned INTO l_is_pen_type_assigned;
3462
3463 -- if the pension type is assigned to this org
3464 IF c_is_pen_type_assigned%FOUND THEN
3465
3466 CLOSE c_is_pen_type_assigned;
3467 l_ret_value := 0;
3468 p_eligibility_flag := 'Y';
3469 l_loop_again := 0;
3470
3471 ELSE -- The pension type entry is not found or not valid for thir org
3472
3473 CLOSE c_is_pen_type_assigned;
3474 l_ret_value := 1;
3475 p_eligibility_flag := 'N';
3476 p_error_message := 'This person is not eligible for the pension type : '||l_pen_type_name;
3477 l_loop_again := 0;
3478
3479 END IF;
3480
3481 -- no overridden rows exist
3482 -- PTYPES havent been overridden so chk the parent org
3483 --this is done only if a valid org hierarchy version exists on this date
3484 ELSIF l_version_id IS NOT NULL THEN
3485
3486 CLOSE c_is_any_pt_assigned;
3487 OPEN c_find_parent_id(l_org_id,l_version_id);
3488 FETCH c_find_parent_id INTO l_org_id;
3489
3490 -- if the parent id is found
3491 IF c_find_parent_id%FOUND THEN
3492
3493 CLOSE c_find_parent_id;
3494
3495 -- if the org has no further parent
3496 ELSE
3497 CLOSE c_find_parent_id;
3498 l_ret_value := 1;
3499 p_eligibility_flag := 'N';
3500 p_error_message := 'This person is not eligible for this pension type : '||l_pen_type_name;
3501 l_loop_again := 0;
3502
3503 END IF;
3504
3505 ELSE
3506 -- no hierarchy has been found
3507 l_ret_value := 1;
3508 p_eligibility_flag := 'N';
3509 p_error_message := 'This person is not eligible for this pension type : '||l_pen_type_name;
3510 l_loop_again := 0;
3511
3512
3513 END IF;
3514
3515 end loop;
3516
3517 ELSE -- if the org is not found again raise an error
3518 CLOSE c_find_org_id;
3519 l_ret_value := 1;
3520 p_eligibility_flag := 'N';
3521 p_error_message := 'This person is not eligible for this pension type : '||l_pen_type_name;
3522
3523 END IF;
3524
3525 --return the value of 1 or 0
3526 RETURN l_ret_value;
3527
3528 END get_pension_type_eligibility;
3529 --
3530
3531
3532 -- ----------------------------------------------------------------------------
3533 -- |-----------------------< get_pension_threshold_ratio >-------------------|
3534 -- ----------------------------------------------------------------------------
3535 --
3536
3537 function get_pension_threshold_ratio
3538 (p_date_earned IN DATE
3539 ,p_assignment_id IN NUMBER
3540 ,p_business_group_id IN NUMBER
3541 ,p_assignment_action_id IN NUMBER
3542 ) return NUMBER IS
3543
3544 --cursor to find the person id for the current assignment id
3545 cursor c_get_person_id IS
3546 select person_id from per_all_assignments_f
3547 where assignment_id = p_assignment_id
3548 and p_date_earned between effective_start_date and effective_end_date
3549 and business_group_id = p_business_group_id;
3550
3551 --cursor to find all the assigment ids for a particular person id
3552 cursor c_get_all_assignmentid(c_person_id in number) IS
3553 select assignment_id
3554 from per_all_assignments_f asg
3555 where asg.person_id = c_person_id
3556 and asg.assignment_status_type_id in (select assignment_status_type_id from PER_ASS_STATUS_TYPE_AMENDS
3557 where business_group_id = p_business_group_id and pay_system_status = 'P' and active_flag = 'Y'
3558 union
3559 select assignment_status_type_id from per_assignment_status_types typ
3560 where typ.pay_system_status = 'P'
3561 and typ.active_flag = 'Y'
3562 and ( (typ.legislation_code is null and typ.business_group_id is null)
3563 OR (typ.legislation_code is null and
3564 (typ.business_group_id is not null and typ.business_group_id = p_business_group_id))
3565 OR (typ.legislation_code ='NL')
3566 ))
3567 and p_date_earned between asg.effective_start_date and asg.effective_end_date;
3568
3569 --Cursor to derive the part time percetage value
3570 CURSOR c_get_fte(c_assignment_id IN per_all_assignments_f.assignment_id%TYPE) IS
3571 SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100'))
3572 FROM hr_soft_coding_keyflex target,
3573 per_all_assignments_f ASSIGN
3574 WHERE p_date_earned BETWEEN ASSIGN.effective_start_date AND
3575 ASSIGN.effective_end_date
3576 AND ASSIGN.assignment_id = c_assignment_id
3577 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
3578 AND target.enabled_flag = 'Y';
3579
3580 l_assignment_id NUMBER;
3581 l_fte NUMBER;
3582 l_sum_fte NUMBER := 0;
3583 l_ratio NUMBER := 1;
3584 l_person_id NUMBER;
3585
3586 begin
3587
3588 hr_utility.set_location('Entering the function get_pension_threshold_ratio',10);
3589 open c_get_person_id;
3590
3591 fetch c_get_person_id into l_person_id;
3592
3593 hr_utility.set_location('got person id '||l_person_id,20);
3594
3595 if c_get_person_id%NOTFOUND THEN
3596
3597 close c_get_person_id;
3598 fnd_message.set_name('PQP','PQP_NO_PERSON');
3599
3600
3601 fnd_message.raise_error;
3602
3603 else
3604
3605 hr_utility.set_location('get all assignment ids ',30);
3606
3607 close c_get_person_id;
3608 open c_get_all_assignmentid(c_person_id => l_person_id);
3609
3610 -- loop through all the assignments of the person
3611 loop
3612
3613 fetch c_get_all_assignmentid into l_assignment_id;
3614 exit when c_get_all_assignmentid%NOTFOUND;
3615
3616 --
3617 -- Call function to check if the user has overridden the part
3618 -- time percentage . If a value is found , use this to derive the
3619 -- FTE
3620 --
3621 l_fte := pay_nl_si_pkg.get_part_time_perc( l_assignment_id
3622 ,p_date_earned
3623 ,p_business_group_id
3624 ,p_assignment_action_id);
3625
3626 IF l_fte IS NULL THEN
3627 -- Derive the value normally from the SCL Flex
3628 OPEN c_get_fte(c_assignment_id => l_assignment_id);
3629 FETCH c_get_fte INTO l_fte;
3630 CLOSE c_get_fte;
3631 END IF;
3632
3633 hr_utility.set_location('got fte value '||l_fte||'for assignment'||l_assignment_id,40);
3634
3635 -- sum the FTEs of each of the assignments
3636 l_sum_fte := l_sum_fte + nvl(l_fte,100);
3637
3638 end loop;
3639
3640 close c_get_all_assignmentid;
3641
3642 hr_utility.set_location('got sum of ftes'||l_sum_fte,50);
3643
3644 --
3645 -- Call function to check if the user has overridden the part
3646 -- time percentage . If a value is found , use this to derive the
3647 -- FTE
3648 --
3649 l_fte := pay_nl_si_pkg.get_part_time_perc( p_assignment_id
3650 ,p_date_earned
3651 ,p_business_group_id
3652 ,p_assignment_action_id);
3653
3654 IF l_fte IS NULL THEN
3655 -- Derive the value normally from the SCL Flex
3656 OPEN c_get_fte(c_assignment_id => p_assignment_id);
3657 FETCH c_get_fte INTO l_fte;
3658 CLOSE c_get_fte;
3659 END IF;
3660
3661 l_fte := nvl(l_fte,100);
3662
3663 if not l_sum_fte = 0 THEN
3664
3665 l_ratio := l_fte/l_sum_fte;
3666
3667 hr_utility.set_location('got ratio'||l_ratio,60);
3668
3669 end if;
3670 end if;
3671
3672 hr_utility.set_location('leaving the function get_pension_threshold_ratio',70);
3673
3674 return l_ratio;
3675
3676 end get_pension_threshold_ratio;
3677
3678 -- ----------------------------------------------------------------------------
3679 -- |---------------------------< sort_table >---------------------------------|
3680 -- ----------------------------------------------------------------------------
3681 PROCEDURE sort_table ( p_table_to_sort IN OUT NOCOPY t_tax_si_tbl
3682 ) IS
3683
3684 l_temp_tbl t_tax_si_tbl;
3685 i NUMBER ;
3686 j NUMBER ;
3687 l_add_row NUMBER;
3688 l_counter NUMBER := 0;
3689
3690 BEGIN
3691
3692 FOR i IN 1..p_table_to_sort.count - 1
3693 LOOP
3694 FOR j IN i+1..p_table_to_sort.count
3695 LOOP
3696 IF p_table_to_sort(i).reduction_order >=
3697 p_table_to_sort(j).reduction_order THEN
3698 l_temp_tbl(i) := p_table_to_sort(i);
3699 p_table_to_sort(i):= p_table_to_sort(j);
3700 p_table_to_sort(j):= l_temp_tbl(i);
3701 END IF;
3702 END LOOP;
3703 END LOOP;
3704
3705 END sort_table;
3706
3707 -- ----------------------------------------------------------------------------
3708 -- |----------------------< gen_dynamic_formula >-----------------------------|
3709 -- ----------------------------------------------------------------------------
3710 PROCEDURE gen_dynamic_formula ( p_pension_type_id IN NUMBER
3711 ,p_effective_date IN DATE
3712 ,p_formula_string OUT NOCOPY varchar2
3713 ) IS
3714
3715 l_tax_bal_tbl t_tax_si_tbl;
3716 l_si_incm_bal_tbl t_tax_si_tbl;
3717 l_si_gross_sal_tbl t_tax_si_tbl;
3718 l_formula_string varchar2(32000) := ' ';
3719 l_tax_si_red_str varchar2(10);
3720 l_counter number := 1;
3721 l_tax_tbl_indx number := 1;
3722 l_sig_tbl_indx number := 1;
3723 l_sii_tbl_indx number := 1;
3724
3725 -- Single Balance
3726 l_single_bal_string varchar2(1000) :=
3727 '
3728
3729 IF dedn_amt <= l_bal_one THEN
3730 l_feed_one = dedn_amt
3731 ELSE IF dedn_amt > l_bal_one THEN
3732 (
3733 l_feed_one = l_bal_one
3734 )
3735
3736 ';
3737
3738 -- Two Balances
3739 l_two_bal_string varchar2(1000) :=
3740 '
3741
3742 IF dedn_amt <= l_bal_one THEN
3743 l_feed_one = dedn_amt
3744 ELSE IF dedn_amt > l_bal_one THEN
3745 (
3746 l_feed_one = l_bal_one
3747 dedn_amt_temp = dedn_amt - l_feed_one
3748 l_feed_two = LEAST(dedn_amt_temp,l_bal_two)
3749 )
3750
3751 ';
3752
3753 -- Three Balances
3754 l_three_bal_string varchar2(1000) :=
3755 '
3756
3757 IF dedn_amt <= l_bal_one THEN
3758 (
3759 l_feed_one = dedn_amt
3760 )
3761 ELSE IF dedn_amt > l_bal_one THEN
3762 (
3763 l_feed_one = l_bal_one
3764 dedn_amt_temp = dedn_amt - l_feed_one
3765 IF dedn_amt_temp <= l_bal_two THEN
3766 (
3767 l_feed_two = dedn_amt_temp
3768 )
3769 ELSE IF dedn_amt_temp > l_bal_two THEN
3770 (
3771 l_feed_two = l_bal_two
3772 dedn_amt_temp = dedn_amt_temp - l_feed_two
3773 l_feed_three = LEAST(dedn_amt_temp,l_bal_three)
3774 )
3775 )
3776
3777 ';
3778
3779 CURSOR c_tax_si_cur IS
3780 SELECT nvl(STD_TAX_REDUCTION,'0')||
3781 nvl(SPL_TAX_REDUCTION,'0')||
3782 nvl(SIG_SAL_SPL_TAX_REDUCTION,'0')||
3783 nvl(SIG_SAL_NON_TAX_REDUCTION,'0')||
3784 nvl(SIG_SAL_STD_TAX_REDUCTION,'0')||
3785 nvl(SII_STD_TAX_REDUCTION,'0')||
3786 nvl(SII_SPL_TAX_REDUCTION,'0')||
3787 nvl(SII_NON_TAX_REDUCTION,'0') redstr
3788 FROM pqp_pension_types_f
3789 WHERE pension_type_id = p_pension_type_id
3790 AND trunc(p_effective_date) BETWEEN
3791 trunc(effective_start_date)
3792 AND trunc(effective_end_date);
3793
3794 BEGIN
3795 hr_utility.set_location('Entering gen_dynamic_formula',10);
3796 FOR temp_rec IN c_tax_si_cur
3797 LOOP
3798 l_tax_si_red_str := temp_rec.redstr;
3799 END LOOP;
3800 IF substr(l_tax_si_red_str,1,1) <> '0' THEN
3801 l_tax_bal_tbl(l_tax_tbl_indx).tax_si_code := 'STD_TAX';
3802 l_tax_bal_tbl(l_tax_tbl_indx).reduction_order :=
3803 to_number(substr(l_tax_si_red_str,1,1));
3804 l_tax_tbl_indx := l_tax_tbl_indx+1;
3805 END IF;
3806
3807 IF substr(l_tax_si_red_str,2,1) <> '0' THEN
3808 l_tax_bal_tbl(l_tax_tbl_indx).tax_si_code := 'SPL_TAX';
3809 l_tax_bal_tbl(l_tax_tbl_indx).reduction_order :=
3810 to_number(substr(l_tax_si_red_str,2,1));
3811 l_tax_tbl_indx := l_tax_tbl_indx+1;
3812 END IF;
3813
3814 IF substr(l_tax_si_red_str,3,1) <> '0' THEN
3815 l_si_gross_sal_tbl(l_sig_tbl_indx).tax_si_code := 'SIG_SPL';
3816 l_si_gross_sal_tbl(l_sig_tbl_indx).reduction_order :=
3817 to_number(substr(l_tax_si_red_str,3,1));
3818 l_sig_tbl_indx := l_sig_tbl_indx+1;
3819 END IF;
3820
3821 IF substr(l_tax_si_red_str,4,1) <> '0' THEN
3822 l_si_gross_sal_tbl(l_sig_tbl_indx).tax_si_code := 'SIG_NON';
3823 l_si_gross_sal_tbl(l_sig_tbl_indx).reduction_order :=
3824 to_number(substr(l_tax_si_red_str,4,1));
3825 l_sig_tbl_indx := l_sig_tbl_indx+1;
3826 END IF;
3827
3828 IF substr(l_tax_si_red_str,5,1) <> '0' THEN
3829 l_si_gross_sal_tbl(l_sig_tbl_indx).tax_si_code := 'SIG_STD';
3830 l_si_gross_sal_tbl(l_sig_tbl_indx).reduction_order :=
3831 to_number(substr(l_tax_si_red_str,5,1));
3832 l_sig_tbl_indx := l_sig_tbl_indx+1;
3833 END IF;
3834
3835 IF substr(l_tax_si_red_str,6,1) <> '0' THEN
3836 l_si_incm_bal_tbl(l_sii_tbl_indx).tax_si_code := 'SII_STD';
3837 l_si_incm_bal_tbl(l_sii_tbl_indx).reduction_order :=
3838 to_number(substr(l_tax_si_red_str,6,1));
3839 l_sii_tbl_indx := l_sii_tbl_indx+1;
3840 END IF;
3841
3842 IF substr(l_tax_si_red_str,7,1) <> '0' THEN
3843 l_si_incm_bal_tbl(l_sii_tbl_indx).tax_si_code := 'SII_SPL';
3844 l_si_incm_bal_tbl(l_sii_tbl_indx).reduction_order :=
3845 to_number(substr(l_tax_si_red_str,7,1));
3846 l_sii_tbl_indx := l_sii_tbl_indx+1;
3847 END IF;
3848
3849 IF substr(l_tax_si_red_str,8,1) <> '0' THEN
3850 l_si_incm_bal_tbl(l_sii_tbl_indx).tax_si_code := 'SII_NON';
3851 l_si_incm_bal_tbl(l_sii_tbl_indx).reduction_order :=
3852 to_number(substr(l_tax_si_red_str,8,1));
3853 l_sii_tbl_indx := l_sii_tbl_indx+1;
3854 END IF;
3855
3856 hr_utility.set_location('Calling sort on all 3 tables',20);
3857 sort_table(l_tax_bal_tbl);
3858 sort_table(l_si_gross_sal_tbl);
3859 sort_table(l_si_incm_bal_tbl);
3860
3861
3862 IF l_tax_bal_tbl.count > 0 THEN
3863
3864 hr_utility.set_location('Taxation balances exist in the reduction',30);
3865 IF l_tax_bal_tbl.count = 1 THEN
3866 l_formula_string := l_single_bal_string;
3867 ELSIF l_tax_bal_tbl.count = 2 THEN
3868 l_formula_string := l_two_bal_string;
3869 END IF;
3870 WHILE l_counter <= 2
3871 LOOP
3872 IF l_tax_bal_tbl.EXISTS(l_counter) THEN
3873 IF l_tax_bal_tbl(l_counter).tax_si_code = 'STD_TAX' THEN
3874 IF l_counter = 1 THEN
3875 l_formula_string := replace(l_formula_string,'l_bal_one',
3876 'l_std_tax_bal');
3877 l_formula_string := replace(l_formula_string,'l_feed_one',
3878 'feed_to_std_tax');
3879 ELSIF l_counter = 2 THEN
3880 l_formula_string := replace(l_formula_string,'l_bal_two',
3881 'l_std_tax_bal');
3882 l_formula_string := replace(l_formula_string,'l_feed_two',
3883 'feed_to_std_tax');
3884 END IF;
3885
3886 ELSIF l_tax_bal_tbl(l_counter).tax_si_code = 'SPL_TAX' THEN
3887 IF l_counter = 1 THEN
3888 l_formula_string := replace(l_formula_string,'l_bal_one',
3889 'l_spl_tax_bal');
3890 l_formula_string := replace(l_formula_string,'l_feed_one',
3891 'feed_to_spl_tax');
3892 ELSIF l_counter = 2 THEN
3893 l_formula_string := replace(l_formula_string,'l_bal_two',
3894 'l_spl_tax_bal');
3895 l_formula_string := replace(l_formula_string,'l_feed_two',
3896 'feed_to_spl_tax');
3897 END IF;
3898
3899 END IF;
3900 END IF;
3901 l_counter := l_counter + 1;
3902 END LOOP;
3903 END IF;
3904 l_counter := 1;
3905
3906 IF l_si_incm_bal_tbl.count > 0 THEN
3907
3908 hr_utility.set_location('SI Income balances exist in the reduction',40);
3909 IF l_si_incm_bal_tbl.count = 1 THEN
3910 l_formula_string := l_formula_string||l_single_bal_string;
3911 ELSIF l_si_incm_bal_tbl.count = 2 THEN
3912 l_formula_string := l_formula_string||l_two_bal_string;
3913 ELSIF l_si_incm_bal_tbl.count = 3 THEN
3914 l_formula_string := l_formula_string||l_three_bal_string;
3915 END IF;
3916
3917 WHILE l_counter <= 3
3918 LOOP
3919 IF l_si_incm_bal_tbl.EXISTS(l_counter) THEN
3920 IF l_si_incm_bal_tbl(l_counter).tax_si_code = 'SII_STD' THEN
3921 IF l_counter = 1 THEN
3922 l_formula_string := replace(l_formula_string,'l_bal_one',
3923 'l_sii_std_tax_bal');
3924 l_formula_string := replace(l_formula_string,'l_feed_one',
3925 'feed_to_si_incm_std_tax');
3926 ELSIF l_counter = 2 THEN
3927 l_formula_string := replace(l_formula_string,'l_bal_two',
3928 'l_sii_std_tax_bal');
3929 l_formula_string := replace(l_formula_string,'l_feed_two',
3930 'feed_to_si_incm_std_tax');
3931 ELSIF l_counter = 3 THEN
3932 l_formula_string := replace(l_formula_string,'l_bal_three',
3933 'l_sii_std_tax_bal');
3934 l_formula_string := replace(l_formula_string,'l_feed_three',
3935 'feed_to_si_incm_std_tax');
3936 END IF;
3937
3938 ELSIF l_si_incm_bal_tbl(l_counter).tax_si_code = 'SII_SPL' THEN
3939 IF l_counter = 1 THEN
3940 l_formula_string := replace(l_formula_string,'l_bal_one',
3941 'l_sii_spl_tax_bal');
3942 l_formula_string := replace(l_formula_string,'l_feed_one',
3943 'feed_to_si_incm_spl_tax');
3944 ELSIF l_counter = 2 THEN
3945 l_formula_string := replace(l_formula_string,'l_bal_two',
3946 'l_sii_spl_tax_bal');
3947 l_formula_string := replace(l_formula_string,'l_feed_two',
3948 'feed_to_si_incm_spl_tax');
3949 ELSIF l_counter = 3 THEN
3950 l_formula_string := replace(l_formula_string,'l_bal_three',
3951 'l_sii_spl_tax_bal');
3952 l_formula_string := replace(l_formula_string,'l_feed_three',
3953 'feed_to_si_incm_spl_tax');
3954 END IF;
3955 ELSIF l_si_incm_bal_tbl(l_counter).tax_si_code = 'SII_NON' THEN
3956 IF l_counter = 1 THEN
3957 l_formula_string := replace(l_formula_string,'l_bal_one',
3958 'l_sii_non_tax_bal');
3959 l_formula_string := replace(l_formula_string,'l_feed_one',
3960 'feed_to_si_incm_non_tax');
3961 ELSIF l_counter = 2 THEN
3962 l_formula_string := replace(l_formula_string,'l_bal_two',
3963 'l_sii_non_tax_bal');
3964 l_formula_string := replace(l_formula_string,'l_feed_two',
3965 'feed_to_si_incm_non_tax');
3966 ELSIF l_counter = 3 THEN
3967 l_formula_string := replace(l_formula_string,'l_bal_three',
3968 'l_sii_non_tax_bal');
3969 l_formula_string := replace(l_formula_string,'l_feed_three',
3970 'feed_to_si_incm_non_tax');
3971 END IF;
3972
3973 END IF;
3974 END IF;
3975 l_counter := l_counter + 1;
3976 END LOOP;
3977
3978 END IF;
3979
3980 l_counter := 1;
3981
3982 IF l_si_gross_sal_tbl.count > 0 THEN
3983
3984 hr_utility.set_location('SI Gross Salary balances exist in the reduction',50);
3985 IF l_si_gross_sal_tbl.count = 1 THEN
3986 l_formula_string := l_formula_string||l_single_bal_string;
3987 ELSIF l_si_gross_sal_tbl.count = 2 THEN
3988 l_formula_string := l_formula_string||l_two_bal_string;
3989 ELSIF l_si_gross_sal_tbl.count = 3 THEN
3990 l_formula_string := l_formula_string||l_three_bal_string;
3991 END IF;
3992
3993 WHILE l_counter <= 3
3994 LOOP
3995 IF l_si_gross_sal_tbl.EXISTS(l_counter) THEN
3996 IF l_si_gross_sal_tbl(l_counter).tax_si_code = 'SIG_STD' THEN
3997 IF l_counter = 1 THEN
3998 l_formula_string := replace(l_formula_string,'l_bal_one',
3999 'l_sig_std_tax_bal');
4000 l_formula_string := replace(l_formula_string,'l_feed_one',
4001 'feed_to_si_gr_sal_std_tax');
4002 ELSIF l_counter = 2 THEN
4003 l_formula_string := replace(l_formula_string,'l_bal_two',
4004 'l_sig_std_tax_bal');
4005 l_formula_string := replace(l_formula_string,'l_feed_two',
4006 'feed_to_si_gr_sal_std_tax');
4007 ELSIF l_counter = 3 THEN
4008 l_formula_string := replace(l_formula_string,'l_bal_three',
4009 'l_sig_std_tax_bal');
4010 l_formula_string := replace(l_formula_string,'l_feed_three',
4011 'feed_to_si_gr_sal_std_tax');
4012 END IF;
4013
4014 ELSIF l_si_gross_sal_tbl(l_counter).tax_si_code = 'SIG_SPL' THEN
4015 IF l_counter = 1 THEN
4016 l_formula_string := replace(l_formula_string,'l_bal_one',
4017 'l_sig_spl_tax_bal');
4018 l_formula_string := replace(l_formula_string,'l_feed_one',
4019 'feed_to_si_gr_sal_spl_tax');
4020 ELSIF l_counter = 2 THEN
4021 l_formula_string := replace(l_formula_string,'l_bal_two',
4022 'l_sig_spl_tax_bal');
4023 l_formula_string := replace(l_formula_string,'l_feed_two',
4024 'feed_to_si_gr_sal_spl_tax');
4025 ELSIF l_counter = 3 THEN
4026 l_formula_string := replace(l_formula_string,'l_bal_three',
4027 'l_sig_spl_tax_bal');
4028 l_formula_string := replace(l_formula_string,'l_feed_three',
4029 'feed_to_si_gr_sal_spl_tax');
4030 END IF;
4031 ELSIF l_si_gross_sal_tbl(l_counter).tax_si_code = 'SIG_NON' THEN
4032 IF l_counter = 1 THEN
4033 l_formula_string := replace(l_formula_string,'l_bal_one',
4034 'l_sig_non_tax_bal');
4035 l_formula_string := replace(l_formula_string,'l_feed_one',
4036 'feed_to_si_gr_sal_non_tax');
4037 ELSIF l_counter = 2 THEN
4038 l_formula_string := replace(l_formula_string,'l_bal_two',
4039 'l_sig_non_tax_bal');
4040 l_formula_string := replace(l_formula_string,'l_feed_two',
4041 'feed_to_si_gr_sal_non_tax');
4042 ELSIF l_counter = 3 THEN
4043 l_formula_string := replace(l_formula_string,'l_bal_three',
4044 'l_sig_non_tax_bal');
4045 l_formula_string := replace(l_formula_string,'l_feed_three',
4046 'feed_to_si_gr_sal_non_tax');
4047 END IF;
4048
4049 END IF;
4050 END IF;
4051 l_counter := l_counter + 1;
4052 END LOOP;
4053
4054 END IF;
4055 p_formula_string := l_formula_string;
4056 hr_utility.set_location('Leaving gen_dynamic_formula',50);
4057
4058 END gen_dynamic_formula;
4059
4060 -- ----------------------------------------------------------------------------
4061 -- |--------------------< gen_dynamic_sav_formula >----------------------------|
4062 -- ----------------------------------------------------------------------------
4063 PROCEDURE gen_dynamic_sav_formula ( p_pension_type_id IN NUMBER
4064 ,p_effective_date IN DATE
4065 ,p_formula_string OUT NOCOPY varchar2
4066 ) IS
4067
4068 l_tax_bal_tbl t_tax_si_tbl;
4069 l_si_incm_bal_tbl t_tax_si_tbl;
4070 l_si_gross_sal_tbl t_tax_si_tbl;
4071 l_formula_string varchar2(32000) := ' ';
4072 l_tax_si_red_str varchar2(10);
4073 l_counter number := 1;
4074 l_tax_tbl_indx number := 1;
4075 l_sig_tbl_indx number := 1;
4076 l_sii_tbl_indx number := 1;
4077
4078 -- Single Balance
4079 l_single_bal_string varchar2(1000) :=
4080 '
4081
4082 IF dedn_amt <= (l_bal_one - l_feed_one) THEN
4083 l_feed_one = l_feed_one + dedn_amt
4084 ELSE IF dedn_amt > (l_bal_one - l_feed_one) THEN
4085 (
4086 l_feed_one = l_feed_one + (l_bal_one - l_feed_one)
4087 )
4088
4089 ';
4090
4091 -- Two Balances
4092 l_two_bal_string varchar2(1000) :=
4093 '
4094
4095 IF dedn_amt <= (l_bal_one - l_feed_one) THEN
4096 l_feed_one = l_feed_one + dedn_amt
4097 ELSE IF dedn_amt > (l_bal_one - l_feed_one) THEN
4098 (
4099 dedn_amt_temp = dedn_amt - (l_bal_one - l_feed_one)
4100 l_feed_one = l_feed_one + (l_bal_one - l_feed_one)
4101 l_feed_two = l_feed_two + LEAST(dedn_amt_temp,(l_bal_two - l_feed_two))
4102 )
4103
4104 ';
4105
4106 -- Three Balances
4107 l_three_bal_string varchar2(1000) :=
4108 '
4109
4110 IF dedn_amt <= (l_bal_one - l_feed_one) THEN
4111 (
4112 l_feed_one = l_feed_one + dedn_amt
4113 )
4114 ELSE IF dedn_amt > (l_bal_one - l_feed_one) THEN
4115 (
4116 dedn_amt_temp = dedn_amt - (l_bal_one - l_feed_one)
4117 l_feed_one = l_feed_one + (l_bal_one - l_feed_one)
4118 IF dedn_amt_temp <= (l_bal_two - l_feed_two) THEN
4119 (
4120 l_feed_two = l_feed_two + dedn_amt_temp
4121 )
4122 ELSE IF dedn_amt_temp > (l_bal_two - l_feed_two) THEN
4123 (
4124 dedn_amt_temp = dedn_amt_temp - (l_bal_two - l_feed_two)
4125 l_feed_two = l_feed_two + (l_bal_two - l_feed_two)
4126 l_feed_three = l_feed_three + LEAST(dedn_amt_temp,(l_bal_three - l_feed_three))
4127 )
4128 )
4129
4130 ';
4131
4132 CURSOR c_tax_si_cur IS
4133 SELECT nvl(STD_TAX_REDUCTION,'0')||
4134 nvl(SPL_TAX_REDUCTION,'0')||
4135 nvl(SIG_SAL_SPL_TAX_REDUCTION,'0')||
4136 nvl(SIG_SAL_NON_TAX_REDUCTION,'0')||
4137 nvl(SIG_SAL_STD_TAX_REDUCTION,'0')||
4138 nvl(SII_STD_TAX_REDUCTION,'0')||
4139 nvl(SII_SPL_TAX_REDUCTION,'0')||
4140 nvl(SII_NON_TAX_REDUCTION,'0') redstr
4141 FROM pqp_pension_types_f
4142 WHERE pension_type_id = p_pension_type_id
4143 AND trunc(p_effective_date) BETWEEN
4144 trunc(effective_start_date)
4145 AND trunc(effective_end_date);
4146
4147 BEGIN
4148 hr_utility.set_location('Entering gen_dynamic_formula',10);
4149 FOR temp_rec IN c_tax_si_cur
4150 LOOP
4151 l_tax_si_red_str := temp_rec.redstr;
4152 END LOOP;
4153 IF substr(l_tax_si_red_str,1,1) <> '0' THEN
4154 l_tax_bal_tbl(l_tax_tbl_indx).tax_si_code := 'STD_TAX';
4155 l_tax_bal_tbl(l_tax_tbl_indx).reduction_order :=
4156 to_number(substr(l_tax_si_red_str,1,1));
4157 l_tax_tbl_indx := l_tax_tbl_indx+1;
4158 END IF;
4159
4160 IF substr(l_tax_si_red_str,2,1) <> '0' THEN
4161 l_tax_bal_tbl(l_tax_tbl_indx).tax_si_code := 'SPL_TAX';
4162 l_tax_bal_tbl(l_tax_tbl_indx).reduction_order :=
4163 to_number(substr(l_tax_si_red_str,2,1));
4164 l_tax_tbl_indx := l_tax_tbl_indx+1;
4165 END IF;
4166
4167 IF substr(l_tax_si_red_str,3,1) <> '0' THEN
4168 l_si_gross_sal_tbl(l_sig_tbl_indx).tax_si_code := 'SIG_SPL';
4169 l_si_gross_sal_tbl(l_sig_tbl_indx).reduction_order :=
4170 to_number(substr(l_tax_si_red_str,3,1));
4171 l_sig_tbl_indx := l_sig_tbl_indx+1;
4172 END IF;
4173
4174 IF substr(l_tax_si_red_str,4,1) <> '0' THEN
4175 l_si_gross_sal_tbl(l_sig_tbl_indx).tax_si_code := 'SIG_NON';
4176 l_si_gross_sal_tbl(l_sig_tbl_indx).reduction_order :=
4177 to_number(substr(l_tax_si_red_str,4,1));
4178 l_sig_tbl_indx := l_sig_tbl_indx+1;
4179 END IF;
4180
4181 IF substr(l_tax_si_red_str,5,1) <> '0' THEN
4182 l_si_gross_sal_tbl(l_sig_tbl_indx).tax_si_code := 'SIG_STD';
4183 l_si_gross_sal_tbl(l_sig_tbl_indx).reduction_order :=
4184 to_number(substr(l_tax_si_red_str,5,1));
4185 l_sig_tbl_indx := l_sig_tbl_indx+1;
4186 END IF;
4187
4188 IF substr(l_tax_si_red_str,6,1) <> '0' THEN
4189 l_si_incm_bal_tbl(l_sii_tbl_indx).tax_si_code := 'SII_STD';
4190 l_si_incm_bal_tbl(l_sii_tbl_indx).reduction_order :=
4191 to_number(substr(l_tax_si_red_str,6,1));
4192 l_sii_tbl_indx := l_sii_tbl_indx+1;
4193 END IF;
4194
4195 IF substr(l_tax_si_red_str,7,1) <> '0' THEN
4196 l_si_incm_bal_tbl(l_sii_tbl_indx).tax_si_code := 'SII_SPL';
4197 l_si_incm_bal_tbl(l_sii_tbl_indx).reduction_order :=
4198 to_number(substr(l_tax_si_red_str,7,1));
4199 l_sii_tbl_indx := l_sii_tbl_indx+1;
4200 END IF;
4201
4202 IF substr(l_tax_si_red_str,8,1) <> '0' THEN
4203 l_si_incm_bal_tbl(l_sii_tbl_indx).tax_si_code := 'SII_NON';
4204 l_si_incm_bal_tbl(l_sii_tbl_indx).reduction_order :=
4205 to_number(substr(l_tax_si_red_str,8,1));
4206 l_sii_tbl_indx := l_sii_tbl_indx+1;
4207 END IF;
4208
4209 hr_utility.set_location('Calling sort on all 3 tables',20);
4210 sort_table(l_tax_bal_tbl);
4211 sort_table(l_si_gross_sal_tbl);
4212 sort_table(l_si_incm_bal_tbl);
4213
4214
4215 IF l_tax_bal_tbl.count > 0 THEN
4216
4217 hr_utility.set_location('Taxation balances exist in the reduction',30);
4218 IF l_tax_bal_tbl.count = 1 THEN
4219 l_formula_string := l_single_bal_string;
4220 ELSIF l_tax_bal_tbl.count = 2 THEN
4221 l_formula_string := l_two_bal_string;
4222 END IF;
4223 WHILE l_counter <= 2
4224 LOOP
4225 IF l_tax_bal_tbl.EXISTS(l_counter) THEN
4226 IF l_tax_bal_tbl(l_counter).tax_si_code = 'STD_TAX' THEN
4227 IF l_counter = 1 THEN
4228 l_formula_string := replace(l_formula_string,'l_bal_one',
4229 'l_std_tax_bal');
4230 l_formula_string := replace(l_formula_string,'l_feed_one',
4231 'feed_to_std_tax');
4232 ELSIF l_counter = 2 THEN
4233 l_formula_string := replace(l_formula_string,'l_bal_two',
4234 'l_std_tax_bal');
4235 l_formula_string := replace(l_formula_string,'l_feed_two',
4236 'feed_to_std_tax');
4237 END IF;
4238
4239 ELSIF l_tax_bal_tbl(l_counter).tax_si_code = 'SPL_TAX' THEN
4240 IF l_counter = 1 THEN
4241 l_formula_string := replace(l_formula_string,'l_bal_one',
4242 'l_spl_tax_bal');
4243 l_formula_string := replace(l_formula_string,'l_feed_one',
4244 'feed_to_spl_tax');
4245 ELSIF l_counter = 2 THEN
4246 l_formula_string := replace(l_formula_string,'l_bal_two',
4247 'l_spl_tax_bal');
4248 l_formula_string := replace(l_formula_string,'l_feed_two',
4249 'feed_to_spl_tax');
4250 END IF;
4251
4252 END IF;
4253 END IF;
4254 l_counter := l_counter + 1;
4255 END LOOP;
4256 END IF;
4257 l_counter := 1;
4258
4259 IF l_si_incm_bal_tbl.count > 0 THEN
4260
4261 hr_utility.set_location('SI Income balances exist in the reduction',40);
4262 IF l_si_incm_bal_tbl.count = 1 THEN
4263 l_formula_string := l_formula_string||l_single_bal_string;
4264 ELSIF l_si_incm_bal_tbl.count = 2 THEN
4265 l_formula_string := l_formula_string||l_two_bal_string;
4266 ELSIF l_si_incm_bal_tbl.count = 3 THEN
4267 l_formula_string := l_formula_string||l_three_bal_string;
4268 END IF;
4269
4270 WHILE l_counter <= 3
4271 LOOP
4272 IF l_si_incm_bal_tbl.EXISTS(l_counter) THEN
4273 IF l_si_incm_bal_tbl(l_counter).tax_si_code = 'SII_STD' THEN
4274 IF l_counter = 1 THEN
4275 l_formula_string := replace(l_formula_string,'l_bal_one',
4276 'l_sii_std_tax_bal');
4277 l_formula_string := replace(l_formula_string,'l_feed_one',
4278 'feed_to_si_incm_std_tax');
4279 ELSIF l_counter = 2 THEN
4280 l_formula_string := replace(l_formula_string,'l_bal_two',
4281 'l_sii_std_tax_bal');
4282 l_formula_string := replace(l_formula_string,'l_feed_two',
4283 'feed_to_si_incm_std_tax');
4284 ELSIF l_counter = 3 THEN
4285 l_formula_string := replace(l_formula_string,'l_bal_three',
4286 'l_sii_std_tax_bal');
4287 l_formula_string := replace(l_formula_string,'l_feed_three',
4288 'feed_to_si_incm_std_tax');
4289 END IF;
4290
4291 ELSIF l_si_incm_bal_tbl(l_counter).tax_si_code = 'SII_SPL' THEN
4292 IF l_counter = 1 THEN
4293 l_formula_string := replace(l_formula_string,'l_bal_one',
4294 'l_sii_spl_tax_bal');
4295 l_formula_string := replace(l_formula_string,'l_feed_one',
4296 'feed_to_si_incm_spl_tax');
4297 ELSIF l_counter = 2 THEN
4298 l_formula_string := replace(l_formula_string,'l_bal_two',
4299 'l_sii_spl_tax_bal');
4300 l_formula_string := replace(l_formula_string,'l_feed_two',
4301 'feed_to_si_incm_spl_tax');
4302 ELSIF l_counter = 3 THEN
4303 l_formula_string := replace(l_formula_string,'l_bal_three',
4304 'l_sii_spl_tax_bal');
4305 l_formula_string := replace(l_formula_string,'l_feed_three',
4306 'feed_to_si_incm_spl_tax');
4307 END IF;
4308 ELSIF l_si_incm_bal_tbl(l_counter).tax_si_code = 'SII_NON' THEN
4309 IF l_counter = 1 THEN
4310 l_formula_string := replace(l_formula_string,'l_bal_one',
4311 'l_sii_non_tax_bal');
4312 l_formula_string := replace(l_formula_string,'l_feed_one',
4313 'feed_to_si_incm_non_tax');
4314 ELSIF l_counter = 2 THEN
4315 l_formula_string := replace(l_formula_string,'l_bal_two',
4316 'l_sii_non_tax_bal');
4317 l_formula_string := replace(l_formula_string,'l_feed_two',
4318 'feed_to_si_incm_non_tax');
4319 ELSIF l_counter = 3 THEN
4320 l_formula_string := replace(l_formula_string,'l_bal_three',
4321 'l_sii_non_tax_bal');
4322 l_formula_string := replace(l_formula_string,'l_feed_three',
4323 'feed_to_si_incm_non_tax');
4324 END IF;
4325
4326 END IF;
4327 END IF;
4328 l_counter := l_counter + 1;
4329 END LOOP;
4330
4331 END IF;
4332
4333 l_counter := 1;
4334
4335 IF l_si_gross_sal_tbl.count > 0 THEN
4336
4337 hr_utility.set_location('SI Gross Salary balances exist in the reduction',50);
4338 IF l_si_gross_sal_tbl.count = 1 THEN
4339 l_formula_string := l_formula_string||l_single_bal_string;
4340 ELSIF l_si_gross_sal_tbl.count = 2 THEN
4341 l_formula_string := l_formula_string||l_two_bal_string;
4342 ELSIF l_si_gross_sal_tbl.count = 3 THEN
4343 l_formula_string := l_formula_string||l_three_bal_string;
4344 END IF;
4345
4346 WHILE l_counter <= 3
4347 LOOP
4348 IF l_si_gross_sal_tbl.EXISTS(l_counter) THEN
4349 IF l_si_gross_sal_tbl(l_counter).tax_si_code = 'SIG_STD' THEN
4350 IF l_counter = 1 THEN
4351 l_formula_string := replace(l_formula_string,'l_bal_one',
4352 'l_sig_std_tax_bal');
4353 l_formula_string := replace(l_formula_string,'l_feed_one',
4354 'feed_to_si_gr_sal_std_tax');
4355 ELSIF l_counter = 2 THEN
4356 l_formula_string := replace(l_formula_string,'l_bal_two',
4357 'l_sig_std_tax_bal');
4358 l_formula_string := replace(l_formula_string,'l_feed_two',
4359 'feed_to_si_gr_sal_std_tax');
4360 ELSIF l_counter = 3 THEN
4361 l_formula_string := replace(l_formula_string,'l_bal_three',
4362 'l_sig_std_tax_bal');
4363 l_formula_string := replace(l_formula_string,'l_feed_three',
4364 'feed_to_si_gr_sal_std_tax');
4365 END IF;
4366
4367 ELSIF l_si_gross_sal_tbl(l_counter).tax_si_code = 'SIG_SPL' THEN
4368 IF l_counter = 1 THEN
4369 l_formula_string := replace(l_formula_string,'l_bal_one',
4370 'l_sig_spl_tax_bal');
4371 l_formula_string := replace(l_formula_string,'l_feed_one',
4372 'feed_to_si_gr_sal_spl_tax');
4373 ELSIF l_counter = 2 THEN
4374 l_formula_string := replace(l_formula_string,'l_bal_two',
4375 'l_sig_spl_tax_bal');
4376 l_formula_string := replace(l_formula_string,'l_feed_two',
4377 'feed_to_si_gr_sal_spl_tax');
4378 ELSIF l_counter = 3 THEN
4379 l_formula_string := replace(l_formula_string,'l_bal_three',
4380 'l_sig_spl_tax_bal');
4381 l_formula_string := replace(l_formula_string,'l_feed_three',
4382 'feed_to_si_gr_sal_spl_tax');
4383 END IF;
4384 ELSIF l_si_gross_sal_tbl(l_counter).tax_si_code = 'SIG_NON' THEN
4385 IF l_counter = 1 THEN
4386 l_formula_string := replace(l_formula_string,'l_bal_one',
4387 'l_sig_non_tax_bal');
4388 l_formula_string := replace(l_formula_string,'l_feed_one',
4389 'feed_to_si_gr_sal_non_tax');
4390 ELSIF l_counter = 2 THEN
4391 l_formula_string := replace(l_formula_string,'l_bal_two',
4392 'l_sig_non_tax_bal');
4393 l_formula_string := replace(l_formula_string,'l_feed_two',
4394 'feed_to_si_gr_sal_non_tax');
4395 ELSIF l_counter = 3 THEN
4396 l_formula_string := replace(l_formula_string,'l_bal_three',
4397 'l_sig_non_tax_bal');
4398 l_formula_string := replace(l_formula_string,'l_feed_three',
4399 'feed_to_si_gr_sal_non_tax');
4400 END IF;
4401
4402 END IF;
4403 END IF;
4404 l_counter := l_counter + 1;
4405 END LOOP;
4406
4407 END IF;
4408 p_formula_string := l_formula_string;
4409 hr_utility.set_location('Leaving gen_dynamic_formula',50);
4410
4411 END gen_dynamic_sav_formula;
4412
4413
4414 -- ------------------------------------------------------------------
4415 -- |----------------------< get_bonus >-----------------------------|
4416 -- ------------------------------------------------------------------
4417 FUNCTION get_bonus
4418 ( p_date_earned in date
4419 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4420 ,p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
4421 ,p_pension_type_id in pqp_pension_types_f.pension_type_id%TYPE
4422 ,p_pay_period_salary in number
4423 ,p_pay_period in varchar2
4424 ,p_work_pattern in varchar2
4425 ,p_conversion_rule in varchar2
4426 ,p_bonus_amount out NOCOPY number
4427 ,p_error_message out NOCOPY varchar2
4428 )
4429
4430 RETURN number IS
4431
4432
4433 l_pension_salary NUMBER;
4434 l_pension_salary_yr NUMBER;
4435 l_recur_bonus_percent NUMBER;
4436 l_non_recur_bonus_percent NUMBER;
4437 l_recur_bonus_balance pay_balance_types.balance_type_id%type;
4438 l_non_recur_bonus_balance pay_balance_types.balance_type_id%type;
4439 l_prev_bonus_include varchar2(1);
4440 l_recur_bonus number := 0;
4441 l_non_recur_bonus number := 0;
4442 l_prev_recur_bonus number := 0;
4443 l_prev_non_recur_bonus number := 0;
4444 l_recur_bonus_period pqp_pension_types_f.recurring_bonus_period%type;
4445 l_non_recur_bonus_period pqp_pension_types_f.non_recurring_bonus_period%type;
4446 l_end_of_prev_yr date;
4447 l_prev_run_year number;
4448 l_payroll_id pay_payrolls_f.payroll_id%type;
4449 l_asg_action_id pay_assignment_actions.assignment_action_id%type;
4450 l_time_period_id per_time_periods.time_period_id%type;
4451 l_defined_bal_id pay_defined_balances.defined_balance_id%type;
4452 l_start_date per_time_periods.start_date%type;
4453 l_end_date per_time_periods.end_date%type;
4454 l_periods_per_yr number := 1;
4455 l_ret_val number := 0;
4456
4457 CURSOR c_get_pension_type_details IS
4458 SELECT NVL(recurring_bonus_percent,0)
4459 ,NVL(non_recurring_bonus_percent,0)
4460 ,recurring_bonus_balance
4461 ,non_recurring_bonus_balance
4462 ,previous_year_bonus_included
4463 ,recurring_bonus_period
4464 ,non_recurring_bonus_period
4465 FROM pqp_pension_types_f
4466 WHERE pension_type_id = p_pension_type_id
4467 AND TRUNC(p_date_earned) BETWEEN
4468 effective_start_date AND effective_end_date
4469 AND business_group_id = p_business_group_id;
4470
4471 CURSOR c_get_defined_bal_id
4472 (c_balance_type_id IN pay_balance_types.balance_type_id%TYPE) IS
4473 SELECT pdb.defined_balance_id
4474 FROM pay_balance_dimensions pbd
4475 ,pay_defined_balances pdb
4476 WHERE pbd.dimension_name = 'Assignment Year To Date'
4477 AND pdb.balance_type_id = c_balance_type_id
4478 AND pbd.balance_dimension_id = pdb.balance_dimension_id
4479 AND pbd.legislation_code = 'NL';
4480
4481 CURSOR c_get_prev_run_year(c_date_earned IN DATE) IS
4482 SELECT TO_NUMBER(TO_CHAR(c_date_earned,'YYYY')) -1
4483 FROM dual;
4484
4485 CURSOR c_get_payroll_id(c_effective_date IN DATE) IS
4486 SELECT payroll_id
4487 FROM per_all_assignments_f
4488 WHERE assignment_id = p_assignment_id
4489 AND trunc(c_effective_date) BETWEEN
4490 effective_start_date AND effective_end_date;
4491
4492 CURSOR c_get_time_period_id (c_payroll_id IN NUMBER
4493 ,c_effective_date IN DATE) IS
4494 SELECT time_period_id
4495 ,start_date
4496 ,end_date
4497 FROM per_time_periods
4498 WHERE payroll_id = c_payroll_id
4499 AND trunc(c_effective_date) BETWEEN start_date AND end_date;
4500
4501 CURSOR c_get_asg_act_id (c_time_period_id IN NUMBER
4502 ,c_period_start IN DATE
4503 ,c_period_end IN DATE) IS
4504 SELECT paa.assignment_action_id
4505 FROM pay_payroll_actions ppa
4506 ,pay_assignment_actions paa
4507 WHERE ppa.payroll_action_id = paa.payroll_action_id
4508 AND ppa.time_period_id = c_time_period_id
4509 AND paa.assignment_id = p_assignment_id
4510 AND ppa.action_type in ('R','Q')
4511 AND ppa.action_status = 'C'
4512 AND paa.action_status = 'C'
4513 AND ppa.date_earned BETWEEN c_period_start AND c_period_end
4514 AND rownum = 1;
4515
4516 Cursor c_get_periods_per_yr(c_period_type in VARCHAR2) IS
4517 Select number_per_fiscal_year
4518 from per_time_period_types
4519 where period_type = c_period_type;
4520
4521 BEGIN
4522
4523 IF ( p_pay_period LIKE '%Calendar Month'
4524 OR p_pay_period = 'CM') THEN
4525
4526 OPEN c_get_periods_per_yr('Calendar Month');
4527
4528 FETCH c_get_periods_per_yr INTO l_periods_per_yr;
4529
4530 CLOSE c_get_periods_per_yr;
4531
4532 ELSIF (p_pay_period LIKE '%Lunar Month'
4533 OR p_pay_period = 'LM') THEN
4534
4535 OPEN c_get_periods_per_yr('Lunar Month');
4536
4537 FETCH c_get_periods_per_yr INTO l_periods_per_yr;
4538
4539 CLOSE c_get_periods_per_yr;
4540
4541 ELSIF (p_pay_period LIKE '%Quarter'
4542 OR p_pay_period = 'Q') THEN
4543
4544 OPEN c_get_periods_per_yr('Quarter');
4545
4546 FETCH c_get_periods_per_yr INTO l_periods_per_yr;
4547
4548 CLOSE c_get_periods_per_yr;
4549
4550 ELSIF ( p_pay_period LIKE '%Week'
4551 OR p_pay_period = 'W') THEN
4552
4553 OPEN c_get_periods_per_yr('Week');
4554
4555 FETCH c_get_periods_per_yr INTO l_periods_per_yr;
4556
4557 CLOSE c_get_periods_per_yr;
4558
4559 ELSIF ( p_pay_period LIKE '%Year'
4560 OR p_pay_period = 'Y') THEN
4561
4562 OPEN c_get_periods_per_yr('Year');
4563
4564 FETCH c_get_periods_per_yr INTO l_periods_per_yr;
4565
4566 CLOSE c_get_periods_per_yr;
4567
4568 ELSE
4569 p_error_message := 'Error : Invalid value for Payroll Period';
4570 l_ret_val := 1;
4571
4572 END IF;
4573
4574 IF l_ret_val <> 0 THEN
4575
4576 RETURN l_ret_val;
4577
4578 END IF;
4579
4580 -- Get details of the pension type.
4581 OPEN c_get_pension_type_details;
4582
4583 FETCH c_get_pension_type_details INTO
4584 l_recur_bonus_percent
4585 ,l_non_recur_bonus_percent
4586 ,l_recur_bonus_balance
4587 ,l_non_recur_bonus_balance
4588 ,l_prev_bonus_include
4589 ,l_recur_bonus_period
4590 ,l_non_recur_bonus_period;
4591
4592 CLOSE c_get_pension_type_details;
4593
4594 l_pension_salary := p_pay_period_salary;
4595
4596 l_pension_salary_yr := l_pension_salary * l_periods_per_yr;
4597
4598 OPEN c_get_prev_run_year(TRUNC(p_date_earned));
4599 FETCH c_get_prev_run_year INTO l_prev_run_year;
4600 CLOSE c_get_prev_run_year;
4601
4602 -- Assuming that the last pay period of the previous year ended on 31 Dec
4603 l_end_of_prev_yr := TO_DATE('31/12/'||TO_CHAR(l_prev_run_year),'DD/MM/YYYY');
4604
4605 -- If the % is not null, then multiply it with the
4606 -- salary value for the pay period
4607 IF (l_recur_bonus_period IS NOT NULL AND
4608 l_recur_bonus_percent > 0) THEN
4609 l_recur_bonus := l_recur_bonus +
4610 (l_recur_bonus_percent/100) * l_pension_salary_yr;
4611 END IF;
4612
4613 IF NVL(l_non_recur_bonus_period,'XX') = 'M' then
4614 l_non_recur_bonus := l_non_recur_bonus +
4615 (l_non_recur_bonus_percent/100) * l_pension_salary;
4616 ELSIF NVL(l_non_recur_bonus_period,'XX') = 'Y' then
4617 l_non_recur_bonus := l_non_recur_bonus +
4618 (l_non_recur_bonus_percent/100) * l_pension_salary_yr;
4619 END IF;
4620
4621 IF NVL(l_prev_bonus_include,'XX') = 'Y' THEN
4622
4623 -- Fetch the payroll id on the last day of
4624 -- the previous year for this asg
4625
4626 -- RK what happens here if the person was on a
4627 -- different assignment in the previous year?.
4628 -- There is also a possibility that the person has
4629 -- had multiple jobs ( Multiple ASG's) during that time.
4630 -- We should fetch the PER_YTD Balance for previous years
4631 -- recurring and Non Recurring Balances.
4632
4633 OPEN c_get_payroll_id(l_end_of_prev_yr);
4634 FETCH c_get_payroll_id INTO l_payroll_id;
4635 IF c_get_payroll_id%FOUND THEN
4636 Close c_get_payroll_id;
4637 Open c_get_time_period_id
4638 (c_payroll_id => l_payroll_id
4639 ,c_effective_date => l_end_of_prev_yr);
4640 Fetch c_get_time_period_id into l_time_period_id,l_start_date,l_end_date;
4641 If c_get_time_period_id%FOUND then
4642 Close c_get_time_period_id;
4643 Open c_get_asg_act_id(c_time_period_id => l_time_period_id
4644 ,c_period_start => l_start_date
4645 ,c_period_end => l_end_date);
4646 Fetch c_get_asg_act_id into l_asg_action_id;
4647 If c_get_asg_act_id%FOUND then
4648 Close c_get_asg_act_id;
4649 If l_recur_bonus_balance is not null then
4650 Open c_get_defined_bal_id(c_balance_type_id => l_recur_bonus_balance);
4651 Fetch c_get_defined_bal_id into l_defined_bal_id;
4652 If c_get_defined_bal_id%FOUND then
4653 Close c_get_defined_bal_id;
4654 l_prev_recur_bonus :=
4655 pay_balance_pkg.get_value(p_defined_balance_id => l_defined_bal_id
4656 ,p_assignment_action_id => l_asg_action_id);
4657 l_prev_recur_bonus := nvl(l_prev_recur_bonus,0);
4658 Else
4659 Close c_get_defined_bal_id;
4660 End If;
4661 End If;
4662 If l_non_recur_bonus_balance is not null then
4663 Open c_get_defined_bal_id(c_balance_type_id => l_non_recur_bonus_balance);
4664 Fetch c_get_defined_bal_id into l_defined_bal_id;
4665 If c_get_defined_bal_id%FOUND then
4666 Close c_get_defined_bal_id;
4667 l_prev_non_recur_bonus :=
4668 pay_balance_pkg.get_value(p_defined_balance_id => l_defined_bal_id
4669 ,p_assignment_action_id => l_asg_action_id);
4670 l_prev_non_recur_bonus := nvl(l_prev_non_recur_bonus,0);
4671 Else
4672 Close c_get_defined_bal_id;
4673 End If;
4674 End If;
4675 Else
4676 Close c_get_asg_act_id;
4677 End if;
4678 Else
4679 Close c_get_time_period_id;
4680 End If;
4681 Else
4682 Close c_get_payroll_id;
4683 End If;
4684 End If;
4685
4686 p_bonus_amount := l_recur_bonus +
4687 l_non_recur_bonus +
4688 l_prev_recur_bonus +
4689 l_prev_non_recur_bonus;
4690
4691 /*find the bonus amount for the pay period*/
4692 p_bonus_amount := p_bonus_amount / l_periods_per_yr;
4693
4694 RETURN l_ret_val;
4695
4696 END get_bonus;
4697
4698 -- ------------------------------------------------------------------
4699 -- |----------------------< is_number >-----------------------------|
4700 -- ------------------------------------------------------------------
4701
4702 FUNCTION is_number
4703 (p_data_value IN OUT NOCOPY varchar2)
4704 RETURN NUMBER IS
4705 l_data_value Number;
4706 BEGIN
4707 l_data_value := Fnd_Number.Canonical_To_Number(Nvl(p_data_value,'0'));
4708 IF l_data_value >= 0 THEN
4709 RETURN 0;
4710 ELSE
4711 RETURN 1;
4712 END IF;
4713
4714 EXCEPTION
4715 WHEN Value_Error THEN
4716 RETURN 1;
4717 END is_number;
4718
4719 -- ------------------------------------------------------------------
4720 -- |-----------------< get_addnl_savings_amt >-----------------------|
4721 -- ------------------------------------------------------------------
4722
4723 FUNCTION get_addnl_savings_amt
4724 (p_assignment_id IN NUMBER
4725 ,p_date_earned IN DATE
4726 ,p_business_group_id IN NUMBER
4727 ,p_payroll_id IN NUMBER
4728 ,p_pension_type_id IN NUMBER
4729 ,p_payroll_period_number IN NUMBER
4730 ,p_additional_amount OUT NOCOPY NUMBER
4731 ,p_error_message OUT NOCOPY VARCHAR2
4732 )
4733 RETURN NUMBER IS
4734
4735 --cursor to fetch the amount from the assignment EIT
4736 --if a row exists for this savings type and the same period number
4737
4738 CURSOR c_get_addnl_amt IS
4739 SELECT fnd_number.canonical_to_number(aei_information3)
4740 FROM per_assignment_extra_info
4741 WHERE assignment_id = p_assignment_id
4742 AND aei_information1 = to_char(p_pension_type_id)
4743 AND aei_information2 = to_char(p_payroll_period_number)
4744 AND p_date_earned BETWEEN fnd_date.canonical_to_date(nvl(aei_information4,fnd_date.date_to_canonical(to_date('01-01-1951','dd-mm-yyyy'))))
4745 AND fnd_date.canonical_to_date(nvl(aei_information5,fnd_date.date_to_canonical(to_date('31-12-4712','dd-mm-yyyy'))))
4746 AND aei_information_category = 'NL_SAV_INFO'
4747 AND information_type = 'NL_SAV_INFO';
4748
4749 l_ret_val NUMBER;
4750 l_addnl_amt NUMBER;
4751
4752 BEGIN
4753
4754 --fetch the additional amount from the ASG EIT
4755 OPEN c_get_addnl_amt;
4756 FETCH c_get_addnl_amt INTO l_addnl_amt;
4757 IF c_get_addnl_amt%FOUND THEN
4758 CLOSE c_get_addnl_amt;
4759 p_additional_amount := l_addnl_amt;
4760 l_ret_val := 0;
4761 ELSE
4762 CLOSE c_get_addnl_amt;
4763 p_additional_amount := 0;
4764 l_ret_val := 0;
4765 END IF;
4766
4767 RETURN l_ret_val;
4768
4769 EXCEPTION
4770
4771 WHEN OTHERS THEN
4772
4773 p_error_message := 'Error occured while fetching the value for the additional'
4774 ||' contribution amount.';
4775 p_additional_amount := 0;
4776 l_ret_val := 1;
4777 RETURN l_ret_val;
4778
4779 END get_addnl_savings_amt;
4780
4781 -- -----------------------------------------------------------------------
4782 -- |---------------------< get_abp_entry_value >--------------------------|
4783 -- -----------------------------------------------------------------------
4784 function get_abp_entry_value
4785 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
4786 ,p_date_earned in date
4787 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4788 ,p_element_type_id in number
4789 ,p_input_value_name in varchar2
4790 ) return NUMBER IS
4791
4792 -- Cursor to get the hire date of the person
4793 CURSOR c_hire_dt_cur(c_asg_id IN NUMBER) IS
4794 SELECT max(date_start)
4795 FROM per_all_assignments_f asg
4796 ,per_periods_of_service pps
4797 WHERE pps.person_id = asg.person_id
4798 AND asg.assignment_id = c_asg_id
4799 AND pps.business_group_id = p_business_group_id
4800 AND date_start <= p_date_earned;
4801
4802 --cursor to get the start date for the assignment
4803 CURSOR c_get_asg_start IS
4804 SELECT min(effective_start_date)
4805 FROM per_all_assignments_f
4806 WHERE assignment_id = p_assignment_id;
4807
4808 -- Cursor to get the entry value for the input name
4809 CURSOR c_entry_val_cur
4810 ( c_effective_date IN DATE
4811 ,c_ipv_id IN NUMBER) IS
4812
4813 SELECT min (fffunc.cn(decode(decode(INPUTV.uom,'M','N','N','N','I','N',NULL),'N'
4814 ,decode(INPUTV.hot_default_flag,'Y',nvl(EEV.screen_entry_value
4815 ,nvl(LIV.default_value,INPUTV.default_value)),'N',EEV.screen_entry_value),NULL)))
4816 FROM pay_element_entry_values_f EEV,
4817 pay_element_entries_f EE,
4818 pay_link_input_values_f LIV,
4819 pay_input_values_f INPUTV
4820 WHERE INPUTV.input_value_id = c_ipv_id
4821 AND c_effective_date BETWEEN INPUTV.effective_start_date
4822 AND INPUTV.effective_end_date
4823 AND INPUTV.element_type_id + 0 = p_element_type_id
4824 AND LIV.input_value_id = INPUTV.input_value_id
4825 AND c_effective_date BETWEEN LIV.effective_start_date
4826 AND LIV.effective_end_date
4827 AND EEV.input_value_id + 0 = INPUTV.input_value_id
4828 AND EEV.element_entry_id = EE.element_entry_id
4829 AND EEV.effective_start_date = EE.effective_start_date
4830 AND EEV.effective_end_date = EE.effective_end_date
4831 AND EE.element_link_id = LIV.element_link_id
4832 AND EE.assignment_id = p_assignment_id
4833 AND c_effective_date BETWEEN EE.effective_start_date
4834 AND EE.effective_end_date
4835 AND nvl(EE.ENTRY_TYPE, 'E') = 'E';
4836
4837 CURSOR c_iv_id_cur IS
4838 select iv.input_value_id
4839 from pay_input_values_f_tl iv_tl,
4840 pay_input_values_f iv
4841 where iv.element_type_id = p_element_type_id
4842 and iv_tl.input_value_id = iv.input_value_id
4843 and iv_tl.language = userenv('LANG')
4844 and upper(iv_tl.name) = upper(p_input_value_name)
4845 AND p_date_earned BETWEEN iv.effective_start_date
4846 AND iv.effective_end_date;
4847
4848 ---
4849
4850 l_run_year NUMBER;
4851 l_hire_date DATE;
4852 l_asg_st_date DATE;
4853 l_effective_date DATE;
4854 l_error_message VARCHAR2(100);
4855 l_input_value_id NUMBER;
4856 l_return_value NUMBER;
4857 l_error_status CHAR :='0';
4858 BEGIN
4859
4860 -- Determine the hire date
4861 OPEN c_hire_dt_cur(p_assignment_id);
4862 FETCH c_hire_dt_cur INTO l_hire_date;
4863 CLOSE c_hire_dt_cur;
4864
4865 --determine the start date of the assignment
4866 OPEN c_get_asg_start;
4867 FETCH c_get_asg_start INTO l_asg_st_date;
4868 CLOSE c_get_asg_start;
4869
4870 -- Determine the year of payroll run
4871 l_run_year := get_run_year (p_date_earned
4872 ,l_error_message );
4873
4874 --
4875 -- Get the date for 1 JAN of the run year
4876 --
4877 --l_effective_date := TO_DATE('01/01/'||to_char(l_run_year),'DD/MM/YYYY');
4878 -- Get valid assignment start date
4879
4880 l_effective_date := PQP_NL_ABP_FUNCTIONS.GET_VALID_START_DATE(p_assignment_id,p_date_earned,l_error_status,l_error_message);
4881 IF (l_error_status = trim(to_char(1,'9'))) Then
4882 RETURN 0;
4883 End IF;
4884
4885 --
4886 -- Get the input value id for the Input Value Name,
4887 -- Element and Language combination.
4888 --
4889 OPEN c_iv_id_cur;
4890 FETCH c_iv_id_cur INTO l_input_value_id;
4891
4892 -- Get the id for the input
4893 IF c_iv_id_cur%NOTFOUND THEN
4894 -- Could not find the input value id .
4895 -- Return Control
4896 CLOSE c_iv_id_cur;
4897 RETURN 0;
4898 ELSE
4899 CLOSE c_iv_id_cur;
4900 END IF;
4901
4902 -- Get the input value as of the first Jan or Hire Date for the
4903 -- input obtained above.
4904
4905 OPEN c_entry_val_cur
4906 ( c_effective_date => l_effective_date
4907 ,c_ipv_id => l_input_value_id);
4908 FETCH c_entry_val_cur INTO l_return_value;
4909
4910 IF c_entry_val_cur%NOTFOUND THEN
4911 l_return_value := 0;
4912 END IF ;
4913
4914 CLOSE c_entry_val_cur;
4915
4916 RETURN NVL(l_return_value,0);
4917
4918 END get_abp_entry_value;
4919
4920 --
4921 -- Function to calculate the hook part time percentage
4922 --
4923
4924 FUNCTION get_hook_part_time_perc (p_assignment_id IN NUMBER
4925 ,p_date_earned IN DATE
4926 ,p_business_group_id IN NUMBER
4927 ,p_assignment_action_id IN NUMBER) RETURN number IS
4928
4929 --
4930 CURSOR csr_get_pay_action_id(c_assignment_action_id NUMBER) IS
4931 SELECT payroll_action_id
4932 FROM pay_assignment_actions
4933 WHERE assignment_action_id = c_assignment_action_id;
4934 --
4935 l_payroll_action_id number;
4936 l_part_time_perc varchar2(35);
4937 l_inputs ff_exec.inputs_t;
4938 l_outputs ff_exec.outputs_t;
4939 l_formula_exists BOOLEAN := TRUE;
4940 l_formula_cached BOOLEAN := FALSE;
4941 l_formula_id ff_formulas_f.formula_id%TYPE;
4942
4943 -- This is the exact replica of the SI Hook
4944 -- The code has been replicated here
4945
4946 BEGIN
4947
4948 g_ptp_formula_name := 'NL_ABP_PART_TIME_PERCENTAGE';
4949 OPEN csr_get_pay_action_id(p_assignment_action_id);
4950 FETCH csr_get_pay_action_id INTO l_payroll_action_id;
4951 CLOSE csr_get_pay_action_id;
4952
4953 IF g_ptp_formula_exists = TRUE THEN
4954 IF g_ptp_formula_cached = FALSE THEN
4955 pay_nl_general.cache_formula('NL_ABP_PART_TIME_PERCENTAGE'
4956 ,p_business_group_id,p_date_earned
4957 ,l_formula_id,l_formula_exists
4958 ,l_formula_cached);
4959 g_ptp_formula_exists:=l_formula_exists;
4960 g_ptp_formula_cached:=l_formula_cached;
4961 g_ptp_formula_id:=l_formula_id;
4962 END IF;
4963
4964 --
4965 IF g_ptp_formula_exists = TRUE THEN
4966 -- hr_utility.trace('FORMULA EXISTS');
4967 --
4968 l_inputs(1).name := 'ASSIGNMENT_ID';
4969 l_inputs(1).value := p_assignment_id;
4970 l_inputs(2).name := 'DATE_EARNED';
4971 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
4972 l_inputs(3).name := 'BUSINESS_GROUP_ID';
4973 l_inputs(3).value := p_business_group_id;
4974 l_inputs(4).name := 'ASSIGNMENT_ACTION_ID';
4975 l_inputs(4).value := p_assignment_action_id;
4976 l_inputs(5).name := 'PAYROLL_ACTION_ID';
4977 l_inputs(5).value := l_payroll_action_id;
4978 l_inputs(6).name := 'BALANCE_DATE';
4979 l_inputs(6).value := fnd_date.date_to_canonical(p_date_earned);
4980 --
4981 l_outputs(1).name := 'PART_TIME_PERCENTAGE';
4982 --
4983 pay_nl_general.run_formula(p_formula_id => g_ptp_formula_id,
4984 p_effective_date => p_date_earned,
4985 p_formula_name => g_ptp_formula_name,
4986 p_inputs => l_inputs,
4987 p_outputs => l_outputs);
4988 --
4989 l_part_time_perc := l_outputs(1).value;
4990
4991 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
4992 ELSE
4993 -- hr_utility.trace('FORMULA DOESNT EXISTS');
4994 l_part_time_perc := NULL;
4995
4996 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
4997
4998 END IF;
4999 ELSIF g_ptp_formula_exists = FALSE THEN
5000 l_part_time_perc := NULL;
5001 END IF;
5002
5003 RETURN fnd_number.canonical_to_number(l_part_time_perc);
5004
5005 END get_hook_part_time_perc;
5006
5007
5008 FUNCTION get_reporting_part_time_perc (p_assignment_id IN NUMBER
5009 ,p_date_earned IN DATE
5010 ,p_business_group_id IN NUMBER
5011 ,p_assignment_action_id IN NUMBER) RETURN number IS
5012
5013 --
5014 CURSOR csr_get_pay_action_id(c_assignment_action_id NUMBER) IS
5015 SELECT payroll_action_id
5016 FROM pay_assignment_actions
5017 WHERE assignment_action_id = c_assignment_action_id;
5018 --
5019 l_payroll_action_id number;
5020 l_part_time_perc varchar2(35);
5021 l_inputs ff_exec.inputs_t;
5022 l_outputs ff_exec.outputs_t;
5023 l_formula_exists BOOLEAN := TRUE;
5024 l_formula_cached BOOLEAN := FALSE;
5025 l_formula_id ff_formulas_f.formula_id%TYPE;
5026
5027 -- This is the exact replica of the SI Hook
5028 -- The code has been replicated here
5029
5030 BEGIN
5031
5032 g_ptp_formula_name := 'NL_ABP_REPORTING_PART_TIME_PERCENTAGE';
5033 --
5034 OPEN csr_get_pay_action_id(p_assignment_action_id);
5035 FETCH csr_get_pay_action_id INTO l_payroll_action_id;
5036 CLOSE csr_get_pay_action_id;
5037 --
5038
5039 IF g_ptp_formula_exists = TRUE THEN
5040 IF g_ptp_formula_cached = FALSE THEN
5041 pay_nl_general.cache_formula('NL_ABP_REPORTING_PART_TIME_PERCENTAGE'
5042 ,p_business_group_id
5043 ,p_date_earned
5044 ,l_formula_id,l_formula_exists
5045 ,l_formula_cached);
5046 g_ptp_formula_exists:=l_formula_exists;
5047 g_ptp_formula_cached:=l_formula_cached;
5048 g_ptp_formula_id:=l_formula_id;
5049 END IF;
5050
5051 --
5052 IF g_ptp_formula_exists = TRUE THEN
5053 -- hr_utility.trace('FORMULA EXISTS');
5054 --
5055 l_inputs(1).name := 'ASSIGNMENT_ID';
5056 l_inputs(1).value := p_assignment_id;
5057 l_inputs(2).name := 'DATE_EARNED';
5058 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
5059 l_inputs(3).name := 'BUSINESS_GROUP_ID';
5060 l_inputs(3).value := p_business_group_id;
5061 l_inputs(4).name := 'ASSIGNMENT_ACTION_ID';
5062 l_inputs(4).value := p_assignment_action_id;
5063 l_inputs(5).name := 'PAYROLL_ACTION_ID';
5064 l_inputs(5).value := l_payroll_action_id;
5065 l_inputs(6).name := 'BALANCE_DATE';
5066 l_inputs(6).value := fnd_date.date_to_canonical(p_date_earned);
5067 --
5068 l_outputs(1).name := 'PART_TIME_PERCENTAGE';
5069 --
5070 pay_nl_general.run_formula(p_formula_id => g_ptp_formula_id,
5071 p_effective_date => p_date_earned,
5072 p_formula_name => g_ptp_formula_name,
5073 p_inputs => l_inputs,
5074 p_outputs => l_outputs);
5075 --
5076 l_part_time_perc := l_outputs(1).value;
5077
5078 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5079 ELSE
5080 -- hr_utility.trace('FORMULA DOESNT EXISTS');
5081 l_part_time_perc := NULL;
5082
5083 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5084
5085 END IF;
5086 ELSIF g_ptp_formula_exists = FALSE THEN
5087 l_part_time_perc := NULL;
5088 END IF;
5089
5090 RETURN fnd_number.canonical_to_number(l_part_time_perc);
5091
5092 END get_reporting_part_time_perc;
5093
5094 -- ----------------------------------------------------------------------------
5095 -- |-----------------------< get_avg_part_time_perc >-------------------------|
5096 -- ----------------------------------------------------------------------------
5097 -- This function is to get the average part time percentage of the
5098 -- employee for calculations in pension basis.
5099
5100 function get_avg_part_time_perc
5101 (p_business_group_id in pqp_pension_types_f.business_group_id%TYPE
5102 ,p_date_earned in date
5103 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5104 ,p_assignment_action_id IN NUMBER
5105 ,p_period_start_date in DATE
5106 ,p_period_end_date in DATE
5107 ,p_avg_part_time_perc out NOCOPY number
5108 ,p_error_message out NOCOPY varchar2)
5109
5110 return NUMBER IS
5111
5112 --
5113 -- Cursor to get the start date for the active asg
5114 --
5115 CURSOR c_get_assign_start_date IS
5116 SELECT min(asg.effective_start_date)
5117 FROM per_assignments_f asg
5118 ,per_assignment_status_types past
5119 WHERE asg.assignment_status_type_id = past.assignment_status_type_id
5120 AND past.per_system_status = 'ACTIVE_ASSIGN'
5121 AND asg.effective_start_date <= trunc(p_period_end_date)
5122 AND nvl(asg.effective_end_date, trunc(p_period_end_date)) >= trunc(p_period_start_date)
5123 AND asg.assignment_id = p_assignment_id
5124 group by asg.assignment_id;
5125
5126 --
5127 -- Cursor to get the effective start and end dates for various changes
5128 -- that have been made to the part time percent.
5129 --
5130 CURSOR c_pt_cur (c_effective_date IN DATE) IS
5131 SELECT asg.effective_start_date Start_Dt,
5132 decode(asg.effective_end_date,
5133 to_date ('31/12/4712','DD/MM/YYYY'),
5134 trunc(p_period_end_date),
5135 asg.effective_end_date) End_Dt
5136 ,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) pt_perc
5137 FROM per_assignments_f asg
5138 ,per_assignment_status_types past
5139 ,hr_soft_coding_keyflex target
5140 WHERE asg.assignment_status_type_id = past.assignment_status_type_id
5141 AND past.per_system_status = 'ACTIVE_ASSIGN'
5142 AND asg.effective_end_date >= c_effective_date
5143 AND asg.assignment_id = p_assignment_id
5144 AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
5145 AND target.enabled_flag = 'Y';
5146
5147 l_effective_date DATE;
5148 l_completed VARCHAR2(1);
5149 l_days_in_pp NUMBER;
5150 l_pt_perc NUMBER;
5151 l_end_date DATE;
5152 l_start_date DATE;
5153 l_hook_ptp NUMBER;
5154 l_min_start_date DATE;
5155 l_max_end_date DATE;
5156 l_count NUMBER := 0;
5157 l_ret_val NUMBER := 0;
5158
5159 BEGIN
5160
5161 --
5162 -- Check if there is an override in the HOOK for the ABP Part time percent
5163 --
5164 l_hook_ptp := pqp_pension_functions.get_hook_part_time_perc (p_assignment_id
5165 ,p_date_earned
5166 ,p_business_group_id
5167 ,p_assignment_action_id);
5168
5169 IF NVL(l_hook_ptp,0) <> 0 THEN
5170
5171 --
5172 -- For ABP The max percent allowed is 125. Enforce that rule here
5173 --
5174 l_hook_ptp := LEAST(l_hook_ptp,125);
5175 p_error_message := 'The part time percentage has been overridden by a formula.'
5176 ||' The maximum value used is 125.';
5177
5178 p_avg_part_time_perc := round(l_hook_ptp,4);
5179 RETURN 2;
5180
5181 ELSE
5182
5183 -- Do regular calculation
5184
5185 OPEN c_get_assign_start_date;
5186 FETCH c_get_assign_start_date INTO l_effective_date;
5187 CLOSE c_get_assign_start_date;
5188
5189 l_completed := 'N';
5190
5191 l_pt_perc := 0;
5192
5193 l_effective_date := GREATEST(l_effective_date,trunc(p_period_start_date));
5194
5195 l_days_in_pp := (trunc(p_period_end_date)
5196 - trunc(p_period_start_date)) + 1;
5197
5198 FOR temp_rec in c_pt_cur ( trunc(l_effective_date) )
5199
5200 LOOP
5201
5202 IF l_completed = 'N' THEN
5203
5204 IF temp_rec.End_Dt >= trunc(p_period_end_date) THEN
5205 l_end_date := trunc(p_period_end_date);
5206 l_completed := 'Y';
5207 ELSE
5208 l_end_date := temp_rec.End_Dt;
5209 END IF;
5210
5211 IF temp_rec.Start_Dt < trunc(p_period_start_date) THEN
5212 l_start_date := trunc(p_period_start_date);
5213 ELSE
5214 l_start_date := temp_rec.Start_Dt;
5215 END IF;
5216
5217 IF l_count = 0 THEN
5218 l_min_start_date := l_start_date;
5219 l_max_end_date := l_end_date;
5220 ELSE
5221 IF l_start_date < l_min_start_date THEN
5222 l_min_start_date := l_start_date;
5223 END IF;
5224 IF l_end_date > l_max_end_date THEN
5225 l_max_end_date := l_end_date;
5226 END IF;
5227 END IF;
5228
5229 l_count := l_count + 1;
5230
5231 l_pt_perc := l_pt_perc + temp_rec.pt_perc * ((trunc(l_end_date) -
5232 trunc(l_start_date)) + 1);
5233
5234 END IF;
5235
5236 END LOOP;
5237
5238 --find the number of days the assignments has been effective in the
5239 --current period
5240 l_days_in_pp := nvl(l_max_end_date,trunc(p_period_end_date))
5241 - nvl(l_min_start_date,trunc(p_period_start_date))
5242 + 1;
5243
5244 --find the average part time percentage value
5245 l_pt_perc := l_pt_perc/l_days_in_pp;
5246
5247 --
5248 -- For ABP The max percent allowed is 125. Enforce that rule here
5249 --
5250 IF l_pt_perc > 125 THEN
5251 p_error_message := 'The part time percentage is restricted to a '
5252 ||'maximum of 125.';
5253 l_ret_val := 2;
5254 END IF;
5255 l_pt_perc := LEAST(l_pt_perc,125);
5256
5257 p_avg_part_time_perc := round(l_pt_perc,4);
5258 RETURN l_ret_val;
5259
5260 END IF;
5261
5262 EXCEPTION WHEN OTHERS THEN
5263 p_error_message := 'Error occured while deriving the part time '
5264 ||'percentage : '||SQLERRM;
5265 p_avg_part_time_perc := 0;
5266 RETURN 1;
5267
5268 END get_avg_part_time_perc;
5269
5270 --Function to derive the version id of the current hierarchy
5271 --defined for any given business group
5272
5273 FUNCTION get_version_id
5274 (p_business_group_id IN NUMBER
5275 ,p_date_earned IN DATE)
5276
5277 RETURN NUMBER IS
5278
5279 --Cursor to find the named hierarchy associated with the BG
5280 CURSOR c_find_named_hierarchy Is
5281 select org_information1
5282 from hr_organization_information
5283 where organization_id = p_business_group_id
5284 and org_information_context = 'NL_BG_INFO';
5285
5286 --Cursor to find the valid version id for the particular named hierarchy
5287 CURSOR c_find_ver_frm_hierarchy(c_hierarchy_id in Number) Is
5288 select ORG_STRUCTURE_VERSION_ID
5289 from per_org_structure_versions_v
5290 where organization_structure_id = c_hierarchy_id
5291 and p_date_earned between date_from
5292 and nvl(date_to,hr_api.g_eot);
5293
5294 --Cursor to find the valid version id for a particular business group
5295 CURSOR c_find_ver_frm_bg Is
5296 select ORG_STRUCTURE_VERSION_ID
5297 from per_org_structure_versions_v
5298 where business_group_id = p_business_group_id
5299 and p_date_earned between date_from
5300 and nvl( date_to,hr_api.g_eot);
5301
5302 l_named_hierarchy number;
5303 l_version_id per_org_structure_versions_v.org_structure_version_id%type
5304 default -99;
5305 l_proc_name varchar2(30) := 'Get_Version_id';
5306
5307 BEGIN
5308 hr_utility.set_location('Entering : '||l_proc_name,10);
5309 --first chk to see if a named hierarchy exists for the BG
5310 OPEN c_find_named_hierarchy;
5311 FETCH c_find_named_hierarchy INTO l_named_hierarchy;
5312 -- if a named hiearchy is found , find the valid version on that date
5313
5314 IF c_find_named_hierarchy%FOUND THEN
5315
5316 CLOSE c_find_named_hierarchy;
5317 hr_utility.set_location('Found named hierarchy : '||l_named_hierarchy,20);
5318 -- now find the valid version on that date
5319 OPEN c_find_ver_frm_hierarchy(l_named_hierarchy);
5320 FETCH c_find_ver_frm_hierarchy INTO l_version_id;
5321
5322 --if no valid version is found, try to get it frm the BG
5323 IF c_find_ver_frm_hierarchy%NOTFOUND THEN
5324
5325 CLOSE c_find_ver_frm_hierarchy;
5326 hr_utility.set_location('No valid version was found for the named hierarchy',30);
5327 -- find the valid version id from the BG
5328 OPEN c_find_ver_frm_bg;
5329 FETCH c_find_ver_frm_bg INTO l_version_id;
5330 CLOSE c_find_ver_frm_bg;
5331 hr_utility.set_location('Found the version from the BG : '||l_version_id,40);
5332
5333 -- else a valid version has been found for the named hierarchy
5334 ELSE
5335
5336 CLOSE c_find_ver_frm_hierarchy;
5337 hr_utility.set_location('Found the version for named hierarchy : '||l_version_id,50);
5338
5339 END IF; --end of if no valid version found
5340
5341 -- else find the valid version from BG
5342 ELSE
5343
5344 CLOSE c_find_named_hierarchy;
5345 hr_utility.set_location('No named hierarchy exists',60);
5346 --now find the version number from the BG
5347 OPEN c_find_ver_frm_bg;
5348 FETCH c_find_ver_frm_bg INTO l_version_id;
5349 CLOSE c_find_ver_frm_bg;
5350 hr_utility.set_location('Found the version from the BG : '||l_version_id,60);
5351
5352 END IF; -- end of if named hierarchy found
5353
5354 RETURN nvl(l_version_id,-99);
5355
5356 EXCEPTION
5357 WHEN OTHERS THEN
5358 l_version_id := -99;
5359 RETURN l_version_id;
5360
5361 END get_version_id;
5362
5363 --
5364 -- ----------------------------------------------------------------------------
5365 -- |---------------------< get_pay_period_age >-------------------------------|
5366 -- ----------------------------------------------------------------------------
5367 --
5368 FUNCTION get_pay_period_age
5369 (p_business_group_id IN pqp_pension_types_f.business_group_id%TYPE
5370 ,p_date_earned IN DATE
5371 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
5372 ,p_period_start_date IN DATE
5373 ) RETURN NUMBER IS
5374
5375 --
5376 -- Local variables
5377 --
5378 l_dob DATE;
5379 l_eff_dt DATE;
5380 l_asg_st_dt DATE;
5381 l_age NUMBER;
5382 l_proc_name VARCHAR2(150) := g_proc_name || 'get_pay_period_age';
5383
5384
5385 --
5386 -- Cursor to get the date of birth
5387 --
5388 CURSOR get_dob IS
5389 SELECT TRUNC(date_of_birth)
5390 FROM per_all_people_f per
5391 ,per_all_assignments_f paf
5392 WHERE per.person_id = paf.person_id
5393 AND paf.assignment_id = p_assignment_id
5394 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
5395 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
5396
5397 --
5398 -- Cursor to get the start date for the active asg
5399 --
5400 CURSOR c_get_assign_start_date IS
5401 SELECT min(asg.effective_start_date)
5402 FROM per_all_assignments_f asg
5403 ,per_assignment_status_types past
5404 WHERE asg.assignment_status_type_id = past.assignment_status_type_id
5405 AND past.per_system_status = 'ACTIVE_ASSIGN'
5406 --AND asg.effective_start_date <= trunc(p_period_start_date)
5407 AND asg.assignment_id = p_assignment_id;
5408
5409 BEGIN
5410
5411 hr_utility.set_location('Entering : '||l_proc_name, 10);
5412
5413 --
5414 -- Derive the assignment start date
5415 --
5416 OPEN c_get_assign_start_date;
5417 FETCH c_get_assign_start_date INTO l_asg_st_dt;
5418 CLOSE c_get_assign_start_date;
5419
5420 hr_utility.set_location('.....Assignment Id : '||p_assignment_id, 13);
5421 hr_utility.set_location('.....Assignment Start : '||l_asg_st_dt, 15);
5422
5423 --
5424 -- Derive the greater of effective start date and assignment start date
5425 --
5426 l_eff_dt := GREATEST(NVL(l_asg_st_dt,p_period_start_date),p_period_start_date);
5427
5428 hr_utility.set_location('.....Pay Period Start : '||p_period_start_date, 20);
5429 hr_utility.set_location('.....Effective Date : '||l_eff_dt, 25);
5430
5431 OPEN get_dob;
5432 FETCH get_dob INTO l_dob;
5433 CLOSE get_dob;
5434 --
5435
5436 --
5437 l_dob := NVL(l_dob,p_date_earned);
5438
5439 hr_utility.set_location('.....Birth Date : '||l_dob, 30);
5440 --
5441 l_age := TRUNC(MONTHS_BETWEEN(l_eff_dt,l_dob)/12,6);
5442
5443 hr_utility.set_location('.....Age is : '||l_age, 35);
5444
5445 hr_utility.set_location('Leaving : '||l_proc_name, 40);
5446
5447 RETURN(l_age);
5448 --
5449
5450 EXCEPTION
5451 WHEN OTHERS THEN
5452
5453 hr_utility.set_location('Leaving with errors: '||l_proc_name, 50);
5454 RETURN 0;
5455
5456 END get_pay_period_age;
5457
5458 end pqp_pension_functions;
[Home] [Help]