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