1 PACKAGE BODY PQP_NL_PGGM_FUNCTIONS AS
2 /* $Header: pqpnlpgg.pkb 120.20 2012/01/19 07:41:17 rpahune ship $ */
3
4 --
5 --Cursor to fetch the max. salary value beyond which
6 --the threshold is constant,from global values
7 --
8 CURSOR c_get_global_value(c_global_name IN VARCHAR2,c_date_earned IN DATE) IS
9 SELECT nvl(fnd_number.canonical_to_number(global_value),0)
10 FROM ff_globals_f
11 WHERE global_name = c_global_name
12 AND legislation_code = 'NL'
13 AND (c_date_earned between effective_start_date and effective_end_date);
14
15
16 CURSOR c_get_num_periods_per_year(c_payroll_action_id IN NUMBER) IS
17 SELECT TPTYPE.number_per_fiscal_year
18 FROM pay_payroll_actions PACTION
19 ,per_time_periods TPERIOD
20 ,per_time_period_types TPTYPE
21 WHERE PACTION.payroll_action_id = c_payroll_action_id
22 AND TPERIOD.payroll_id = PACTION.payroll_id
23 AND (PACTION.date_earned between TPERIOD.start_date
24 and TPERIOD.end_date)
25 AND TPTYPE.period_type = TPERIOD.period_type;
26
27 --9975522
28 CURSOR c_get_global_value_custom(c_global_name IN VARCHAR2, c_date_earned IN DATE, c_business_group_id IN NUMBER) IS
29 SELECT nvl(fnd_number.canonical_to_number(global_value),0)
30 FROM ff_globals_f
31 WHERE global_name = c_global_name
32 AND legislation_code IS NULL
33 AND business_group_id = c_business_group_id
34 AND (c_date_earned between effective_start_date and effective_end_date);
35 --9975522
36
37
38
39 --
40 -- ----------------------------------------------------------------------------
41 -- |------------------------< CHECK_ELIGIBILITY >-----------------------------|
42 -- ----------------------------------------------------------------------------
43 --
44 -- This function is used to check if an employee is eligible to contribute
45 -- towards a particular pension type by verifying that the employee age
46 -- falls between the minimum and maximum ages for the pension type
47
48 FUNCTION CHECK_ELIGIBILITY
49 (p_date_earned IN DATE
50 ,p_business_group_id IN NUMBER
51 ,p_person_age IN NUMBER
52 ,p_pension_type_id IN NUMBER
53 ,p_eligible OUT NOCOPY NUMBER
54 ,p_err_message OUT NOCOPY VARCHAR2)
55
56 RETURN NUMBER IS
57
58 --
59 --Cursor to fetch the min. and max. age specified at the pension type
60 --
61 CURSOR c_get_min_max_ages IS
62 SELECT nvl(minimum_age,0),nvl(maximum_age,100)
63 FROM pqp_pension_types_f
64 WHERE pension_type_id = p_pension_type_id
65 AND p_date_earned BETWEEN effective_start_date
66 AND effective_end_date;
67
68 l_min_age NUMBER := 0;
69 l_max_age NUMBER := 100;
70 l_ret_value NUMBER := 0;
71 l_proc_name VARCHAR2(30) := 'CHECK_ELIGIBILITY';
72
73 BEGIN
74
75 hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
76 --Query up the min. and max. age specified at the pension type
77
78 OPEN c_get_min_max_ages;
79 FETCH c_get_min_max_ages INTO l_min_age,l_max_age;
80
81 IF c_get_min_max_ages%FOUND THEN
82 --the min and max ages have been found,now check for eligibility
83
84 CLOSE c_get_min_max_ages;
85 hr_utility.set_location('Min. and Max. ages are : '||l_min_age||
86 ' and '||l_max_age,20);
87
88 IF p_person_age BETWEEN l_min_age and l_max_age THEN
89 --the person is eligible for the pension type
90
91 hr_utility.set_location('Person is eligible, age is : '||p_person_age,30);
92 p_eligible := 1;
93
94 ELSE
95 --the person is not eligible for this pension type
96 hr_utility.set_location('Person is not eligible, age is : '||p_person_age,30);
97 p_err_message := 'This person is not eligible for the pension type';
98 p_eligible := 0;
99 l_ret_value := 2;
100
101 END IF;
102
103 ELSE
104 --no row could be found for this pension type
105 CLOSE c_get_min_max_ages;
106 hr_utility.set_location('No row could be found for this pension type',40);
107 p_err_message := 'No row could be found for this pension type on this date';
108 p_eligible := 0;
109 l_ret_value := 2;
110
111 END IF;
112
113 hr_utility.set_location('Returning from : '||g_pkg_name||l_proc_name,50);
114
115 RETURN 0;
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,60);
120 p_eligible := 0;
121 p_err_message := 'Error occured when determining eligibility : '||SQLERRM;
122 RETURN 1;
123
124 END CHECK_ELIGIBILITY;
125
126 --
127 -- ----------------------------------------------------------------------------
128 -- |-------------------------< GET_CONTRIBUTION >-----------------------------|
129 -- ----------------------------------------------------------------------------
130 --
131 -- This function is used to get the contribution percentage / flat amount from
132 -- the org hierarchy or from the pension types table if there is no override
133 -- at the org hierarchy level
134
135 FUNCTION GET_CONTRIBUTION
136 (p_assignment_id IN NUMBER
137 ,p_date_earned IN DATE
138 ,p_business_group_id IN NUMBER
139 ,p_ee_or_total IN NUMBER
140 ,p_pension_type_id IN NUMBER
141 ,p_contrib_value OUT NOCOPY NUMBER
142 ,p_err_message OUT NOCOPY VARCHAR2)
143
144 RETURN NUMBER IS
145
146 --
147 -- Cursor to fetch the org id for a given assignment id
148 --
149 CURSOR c_get_org_id IS
150 SELECT organization_id
151 FROM per_all_assignments_f
152 WHERE assignment_id = p_assignment_id
153 AND p_date_earned BETWEEN effective_start_date
154 AND effective_end_date;
155
156 --
157 -- Cursor to get the parent org id for a given organization
158 -- given the version id of the org hierarchy defined
159 --
160 CURSOR c_get_parent_id(c_org_id IN NUMBER
161 ,c_version_id IN NUMBER) IS
162 SELECT organization_id_parent
163 FROM per_org_structure_elements
164 WHERE organization_id_child = c_org_id
165 AND org_structure_version_id = c_version_id
166 AND business_group_id = p_business_group_id;
167
168 --
169 -- Cursor to get the percentage value from the ORG EIT
170 --
171 CURSOR c_get_contrib_frm_org(c_org_id IN NUMBER) IS
172 SELECT decode(p_ee_or_total,0,nvl(fnd_number.canonical_to_number(org_information4),0)
173 ,nvl(fnd_number.canonical_to_number(org_information5),0))
174 FROM hr_organization_information
175 WHERE org_information_context = 'PQP_NL_PGGM_PT'
176 AND organization_id = c_org_id
177 AND p_date_earned BETWEEN fnd_date.canonical_to_date(org_information1)
178 AND nvl(fnd_date.canonical_to_date(org_information2),hr_api.g_eot)
179 AND fnd_number.canonical_to_number(org_information3) = p_pension_type_id;
180
181 --
182 -- Cursor to get the percentage value from the pension types table
183 --
184 CURSOR c_get_contrib_frm_pt IS
185 SELECT decode(p_ee_or_total,0,nvl(ee_contribution_percent,0)
186 ,nvl(er_contribution_percent,0))
187 FROM pqp_pension_types_f
188 WHERE pension_type_id = p_pension_type_id
189 AND p_date_earned BETWEEN effective_start_date
190 AND effective_end_date;
191
192 l_proc_name VARCHAR2(30) := 'GET_CONTRIBUTION';
193 l_loop_again NUMBER := 1;
194 l_emp_or_total VARCHAR2(30) := 'Employee';
195 l_org_contrib_found NUMBER := 0;
196 l_ret_value NUMBER := 0;
197 l_version_id NUMBER;
198 l_org_id NUMBER;
199 l_contrib_value NUMBER := 0;
200
201
202 BEGIN
203
204 hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
205 --First fetch the HR Org Id for the current assignment
206 OPEN c_get_org_id;
207 FETCH c_get_org_id INTO l_org_id;
208 CLOSE c_get_org_id;
209 hr_utility.set_location('Org Id for this assignment is : '||l_org_id,20);
210
211 -- Now fetch the contribution percentages from the ORG EIT
212 -- This will proceed in a loop from the HR Organization
213 -- of this assignment and then up the org the hierarchy until
214 -- we find a EIT row or the topmost parent is reached
215
216 IF p_ee_or_total = 0 THEN
217 l_emp_or_total := 'Employee';
218 ELSE
219 l_emp_or_total := 'Total';
220 END IF;
221 hr_utility.set_location('Now deriving contribution percentage for : '||l_emp_or_total,25);
222
223 --check to see if the version id is already present for this business group
224 IF NOT g_version_info.EXISTS(p_business_group_id) THEN
225 --no cached value of the version id exists for this BG
226 g_version_info(p_business_group_id).version_id
227 := pqp_pension_functions.GET_VERSION_ID(p_business_group_id
228 ,p_date_earned);
229
230 END IF;
231
232 l_version_id := g_version_info(p_business_group_id).version_id;
233 hr_utility.set_location('Org Hierarchy version id : '||l_version_id,27);
234
235 WHILE (l_loop_again = 1)
236 LOOP
237 OPEN c_get_contrib_frm_org(l_org_id);
238 FETCH c_get_contrib_frm_org INTO l_contrib_value;
239
240 IF c_get_contrib_frm_org%FOUND THEN
241 --Contribution percentages have been found at this org
242 hr_utility.set_location('Contribution values found at org : '||l_org_id,30);
243 CLOSE c_get_contrib_frm_org;
244
245 p_contrib_value := fnd_number.canonical_to_number(l_contrib_value);
246
247 --set the flag indicating that contribution percentage has been found at the
248 --ORG level, so we dont need to continue any further
249 l_org_contrib_found := 1;
250
251 hr_utility.set_location('Contribution percentage is : '||p_contrib_value,40);
252 --we no longer need to continue up the hierarchy
253 l_loop_again := 0;
254
255 ELSE
256 --contribution percentages not found at this org, now move up the hierarchy
257 --find the parent org for this current org
258 CLOSE c_get_contrib_frm_org;
259 OPEN c_get_parent_id(c_org_id => l_org_id
260 ,c_version_id => l_version_id);
261 FETCH c_get_parent_id INTO l_org_Id;
262
263 IF c_get_parent_id%FOUND THEN
264 --a parent org has been found,so we can loop again
265 CLOSE c_get_parent_id;
266
267 ELSE
268 --no further parents exist,so exit the loop
269 CLOSE c_get_parent_id;
270 l_loop_again := 0;
271
272 --set the flag indicating that org contributions
273 --have not been found, so derive it from the Pension Type
274 l_org_contrib_found := 0;
275
276 END IF;
277
278 END IF;
279
280 END LOOP;
281
282 --now check to see if the contribution percentage
283 --has been found at the ORG level or do we need to
284 --derive it from the pension type
285 IF l_org_contrib_found = 0 THEN
286 hr_utility.set_location('No contribution values found at the org'||
287 ' ,now deriving it from the pension type',50);
288 --contribution values have not been found at the ORG level
289 --query up the contribution percentages from the pension type
290 OPEN c_get_contrib_frm_pt;
291 FETCH c_get_contrib_frm_pt INTO p_contrib_value;
292
293 IF c_get_contrib_frm_pt%FOUND THEN
294 --contribution percentages have been found from the pension type
295 hr_utility.set_location('Contribution derived from pension type : '
296 ||p_contrib_value,60);
297 CLOSE c_get_contrib_frm_pt;
298
299 ELSE
300 --no data has been found for this pension type, this is an error condition
301 CLOSE c_get_contrib_frm_pt;
302 hr_utility.set_location('No data could be found for the pension type on date earned',70);
303 p_contrib_value := 0;
304 p_err_message := 'No data could be found for the pension type attached to '
305 ||'this scheme on the date earned.';
306 l_ret_value := 2;
307
308 END IF;
309
310 END IF;
311
312 hr_utility.set_location('Returning from : '||g_pkg_name||l_proc_name,80);
313 RETURN l_ret_value;
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,90);
318 p_contrib_value := 0;
319 p_err_message := 'Error occured when determining contribution : '||SQLERRM;
320 RETURN 1;
321
322 END GET_CONTRIBUTION;
323
324 --
325 -- ----------------------------------------------------------------------------
326 -- |-----------------------< GET_AGE_DEPENDANT_THLD>--------------------------|
327 -- ----------------------------------------------------------------------------
328 --
329 -- This function is used to derive the age dependant threshold for an employee
330 -- based on his/her age from a user defined table
331
332 FUNCTION GET_AGE_DEPENDANT_THLD
333 (p_person_age IN NUMBER
334 ,p_business_group_id IN NUMBER
335 ,p_date_earned IN DATE)
336
337 RETURN NUMBER IS
338
339 --
340 --Cursor to fetch the maximum age for which thresholds are defined
341 --all ages above this value have the same threshold
342 --
343 CURSOR c_get_max_age IS
344 SELECT max(fnd_number.canonical_to_number(row_low_range_or_name))
345 FROM pay_user_rows_f
346 WHERE p_date_earned BETWEEN effective_start_date
347 AND effective_end_date
348 AND user_table_id = (SELECT user_table_id
349 FROM pay_user_tables
350 WHERE user_table_name = 'PQP_NL_PGGM_AGE_DEPENDANT_THRESHOLD'
351 AND legislation_code = 'NL'
352 );
353
354 --9975522
355 CURSOR c_get_max_age_custom IS
356 SELECT max(fnd_number.canonical_to_number(row_low_range_or_name))
357 FROM pay_user_rows_f
358 WHERE p_date_earned BETWEEN effective_start_date
359 AND effective_end_date
360 AND user_table_id = (SELECT user_table_id
361 FROM pay_user_tables
362 WHERE user_table_name = 'PQP_NL_PGGM_AGE_DEPENDANT_THRESHOLD_CUSTOM'
363 AND legislation_code = 'NL'
364 );
365 --9975522
366
367
368 l_proc_name VARCHAR2(30) := 'GET_AGE_DEPENDANT_THLD';
369 l_return_value pay_user_column_instances.value%TYPE;
370 l_value_found NUMBER := 0;
371 l_threshold_percentage NUMBER := 0;
372 l_max_age NUMBER;
373
374 BEGIN
375
376 hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
377
378 --9975522
379 --FIRST CHECK INTO THE CUSTOME USER TABLE IF NOT THEN SEEDED
380 --first check to see if the user defined table for age dependant
381 --thresholds has a row for the person's age, then return this value
382 BEGIN
383 hr_utility.set_location('Checking for threshold percentage for age : '||p_person_age,200);
384 l_return_value :=
385 hruserdt.get_table_value
386 (
387 p_bus_group_id => p_business_group_id
388 ,p_table_name => 'PQP_NL_PGGM_AGE_DEPENDANT_THRESHOLD_CUSTOM'
389 ,p_col_name => 'Percentage Of Maximum Threshold'
390 ,p_row_value => p_person_age
391 ,p_effective_date => p_date_earned
392 );
393
394 l_value_found := 1;
395 l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
396 hr_utility.set_location('Threshold percentage derived as : '||l_threshold_percentage,300);
397
398 EXCEPTION
399 WHEN NO_DATA_FOUND THEN
400 l_value_found := 0;
401
402 END;
403
404 --if no value has been found for the person's age, find the maximum age
405 --for which values have been defined, if the person's age exceeds this age
406 --then the value applicable for the person is the same as the value defined
407 --for the maximum age
408 IF l_value_found = 0 THEN
409
410 hr_utility.set_location('No row found for the person''s age,now finding max age',400);
411 --query up the maximum age defined in the rows for this user defined tbl
412 OPEN c_get_max_age_custom;
413 FETCH c_get_max_age_custom INTO l_max_age;
414 IF c_get_max_age_custom%FOUND THEN
415
416 --Found the maximum age upto which thresholds have been defined
417 hr_utility.set_location('Max age in threshold udt : '||l_max_age,500);
418 CLOSE c_get_max_age_custom;
419
420 --if the person's age is > the max age, then the threshold is the same
421 --as the value defined for this max age
422 IF p_person_age > l_max_age THEN
423
424 BEGIN
425 l_return_value :=
426 hruserdt.get_table_value
427 (
428 p_bus_group_id => p_business_group_id
429 ,p_table_name => 'PQP_NL_PGGM_AGE_DEPENDANT_THRESHOLD_CUSTOM'
430 ,p_col_name => 'Percentage Of Maximum Threshold'
431 ,p_row_value => l_max_age
432 ,p_effective_date => p_date_earned
433 );
434 l_value_found := 1;
435 l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
436 hr_utility.set_location('value of max age used : '||l_threshold_percentage,600);
437
438 EXCEPTION
439 WHEN NO_DATA_FOUND THEN
440 l_value_found := 0;
441 l_threshold_percentage := 0;
442 END;
443
444 --else the person's age is lesser than the max age and no row has been defined
445 ELSE
446 l_value_found := 0;
447 l_threshold_percentage := 0;
448
449 END IF;
450
451 --else no rows have been defined for the user defined table
452 --so return the percentage as 0
453 ELSE
454
455 hr_utility.set_location('No rows were defined for the udt',700);
456 CLOSE c_get_max_age_custom;
457 l_value_found := 0;
458 l_threshold_percentage := 0;
459
460 END IF;
461
462 END IF;
463 --9975522
464
465 IF l_value_found = 0 THEN --IF NO VALUE FOUND IN CUSTOM TABLE --9975522
466 --first check to see if the user defined table for age dependant
467 --thresholds has a row for the person's age, then return this value
468 BEGIN
469 hr_utility.set_location('Checking for threshold percentage for age : '||p_person_age,20);
470 l_return_value :=
471 hruserdt.get_table_value
472 (
473 p_bus_group_id => p_business_group_id
474 ,p_table_name => 'PQP_NL_PGGM_AGE_DEPENDANT_THRESHOLD'
475 ,p_col_name => 'Percentage Of Maximum Threshold'
476 ,p_row_value => p_person_age
477 ,p_effective_date => p_date_earned
478 );
479
480 l_value_found := 1;
481 l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
482 hr_utility.set_location('Threshold percentage derived as : '||l_threshold_percentage,30);
483
484 EXCEPTION
485 WHEN NO_DATA_FOUND THEN
486 l_value_found := 0;
487
488 END;
489
490 --if no value has been found for the person's age, find the maximum age
491 --for which values have been defined, if the person's age exceeds this age
492 --then the value applicable for the person is the same as the value defined
493 --for the maximum age
494 IF l_value_found = 0 THEN
495
496 hr_utility.set_location('No row found for the person''s age,now finding max age',40);
497 --query up the maximum age defined in the rows for this user defined tbl
498 OPEN c_get_max_age;
499 FETCH c_get_max_age INTO l_max_age;
500 IF c_get_max_age%FOUND THEN
501
502 --Found the maximum age upto which thresholds have been defined
503 hr_utility.set_location('Max age in threshold udt : '||l_max_age,50);
504 CLOSE c_get_max_age;
505
506 --if the person's age is > the max age, then the threshold is the same
507 --as the value defined for this max age
508 IF p_person_age > l_max_age THEN
509
510 BEGIN
511 l_return_value :=
512 hruserdt.get_table_value
513 (
514 p_bus_group_id => p_business_group_id
515 ,p_table_name => 'PQP_NL_PGGM_AGE_DEPENDANT_THRESHOLD'
516 ,p_col_name => 'Percentage Of Maximum Threshold'
517 ,p_row_value => l_max_age
518 ,p_effective_date => p_date_earned
519 );
520 l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
521 hr_utility.set_location('value of max age used : '||l_threshold_percentage,60);
522
523 EXCEPTION
524 WHEN NO_DATA_FOUND THEN
525 l_threshold_percentage := 0;
526 END;
527
528 --else the person's age is lesser than the max age and no row has been defined
529 ELSE
530
531 l_threshold_percentage := 0;
532
533 END IF;
534
535 --else no rows have been defined for the user defined table
536 --so return the percentage as 0
537 ELSE
538
539 hr_utility.set_location('No rows were defined for the udt',70);
540 CLOSE c_get_max_age;
541 l_threshold_percentage := 0;
542
543 END IF;
544
545 END IF;
546 END IF; -- IF NO VALUE FOUND IN CUSTOM TABLE --9975522
547
548 hr_utility.set_location('Value of threshold percentage is : '||l_threshold_percentage,75);
549 hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,80);
550
551 RETURN l_threshold_percentage;
552
553 EXCEPTION
554 WHEN OTHERS THEN
555 hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,90);
556 RETURN 0;
557
558 END GET_AGE_DEPENDANT_THLD;
559
560 --=============================================================================
561 -- Function to get the age of a person given the effective date
562 --=============================================================================
563 FUNCTION Get_Age
564 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
565 ,p_effective_date IN DATE
566 ,p_begin_of_year_date IN DATE)
567 RETURN NUMBER IS
568
569 CURSOR get_dob IS
570 SELECT trunc(date_of_birth)
571 FROM per_all_people_f per
572 ,per_all_assignments_f paf
573 WHERE per.person_id = paf.person_id
574 AND paf.assignment_id = p_assignment_id
575 AND p_effective_date BETWEEN per.effective_start_date
576 AND per.effective_end_date
577 AND p_effective_date BETWEEN paf.effective_start_date
578 AND paf.effective_end_date;
579
580 l_age NUMBER;
581 l_dob DATE;
582
583 BEGIN
584
585 --
586 --Fetch the date of birth
587 --
588 OPEN get_dob;
589 FETCH get_dob INTO l_dob;
590 CLOSE get_dob;
591
592 l_dob := NVL(l_dob,p_effective_date);
593
594 RETURN (TRUNC(MONTHS_BETWEEN(p_begin_of_year_date,l_dob)/12,2));
595
596 END Get_Age;
597
598
599 --
600 -- ----------------------------------------------------------------------------
601 -- |-------------------------< GET_PENSION_BASIS >-----------------------------|
602 -- ----------------------------------------------------------------------------
603 --
604 -- This function is used to derive the pension basis value for a given pension
605 -- type based on the basis method defined
606
607 FUNCTION GET_PENSION_BASIS
608 (
609 p_payroll_action_id IN NUMBER
610 ,p_date_earned IN DATE
611 ,p_business_group_id IN NUMBER
612 ,p_person_age IN NUMBER
613 ,p_pension_type_id IN NUMBER
614 ,p_pension_salary IN NUMBER
615 ,p_part_time_percentage IN NUMBER
616 ,p_pension_basis OUT NOCOPY NUMBER
617 ,p_err_message OUT NOCOPY VARCHAR2
618 ,p_avlb_thld IN OUT NOCOPY NUMBER
619 ,p_used_thld IN OUT NOCOPY NUMBER)
620
621 RETURN NUMBER IS
622
623 --
624 --Cursor to fetch the basis method from the pension type
625 --
626 CURSOR c_get_basis_method IS
627 SELECT pension_basis_calc_method
628 FROM pqp_pension_types_f
629 WHERE pension_type_id = p_pension_type_id
630 AND p_date_earned BETWEEN effective_start_date
631 AND effective_end_date;
632
633 l_proc_name VARCHAR2(30) := 'GET_PENSION_BASIS';
634 l_ret_value NUMBER := 0;
635 l_threshold_salary NUMBER;
636 l_max_salary_threshold NUMBER;
637 l_max_age_threshold NUMBER;
638 l_threshold NUMBER;
639 l_threshold_percentage NUMBER;
640 l_avlb_thld NUMBER := 0;
641 l_used_thld NUMBER := 0;
642 l_basis_method pqp_pension_types_f.pension_basis_calc_method%TYPE;
643 l_num_periods NUMBER :=12;
644
645 BEGIN
646 --hr_utility.trace_on(null,'SS');
647 hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
648
649 --first fetch the basis method for this pension type
650 OPEN c_get_basis_method;
651 FETCH c_get_basis_method INTO l_basis_method;
652 CLOSE c_get_basis_method;
653
654 --Get number of payroll periods in a year
655 OPEN c_get_num_periods_per_year(p_payroll_action_id);
656 FETCH c_get_num_periods_per_year INTO l_num_periods;
657 CLOSE c_get_num_periods_per_year;
658
659 --now calculate the basis value depending on the basis method
660 --derived above
661 IF l_basis_method = '6' THEN
662
663 --basis method is (Pension Salary - Salary dependant threshold)
664 -- * Part Time Percentage
665
666 hr_utility.set_location('Basis method : '||'(Pension Salary - Salary threshold)'
667 ||' * Part Time %',20);
668 --9975522
669 OPEN c_get_global_value_custom('PQP_NL_PGGM_THRESHOLD_SALARY_CUSTOM', p_date_earned, p_business_group_id);
670 FETCH c_get_global_value_custom INTO l_threshold_salary;
671 --9975522
672 IF c_get_global_value_custom%FOUND THEN
673 CLOSE c_get_global_value_custom;
674 --the value for the threshold salary has been found
675 hr_utility.set_location('PQP_NL_PGGM_THRESHOLD_SALARY_CUSTOM : '||l_threshold_salary,25);
676 ELSE
677 CLOSE c_get_global_value_custom;
678 --9975522
679
680 --first derive the salary dependant threshold
681 --query up the maximum salary
682 OPEN c_get_global_value('PQP_NL_PGGM_THRESHOLD_SALARY',p_date_earned);
683 FETCH c_get_global_value INTO l_threshold_salary;
684
685 IF c_get_global_value%FOUND THEN
686 CLOSE c_get_global_value;
687 --the value for the threshold salary has been found
688 hr_utility.set_location('PQP_NL_PGGM_THRESHOLD_SALARY : '||l_threshold_salary,25);
689
690 ELSE
691 CLOSE c_get_global_value;
692 --the global value was not found,raise a warning
693 hr_utility.set_location('PQP_NL_PGGM_THRESHOLD_SALARY not found',25);
694 p_err_message := 'No value was found for the global PQP_NL_PGGM_THRESHOLD_SALARY';
695 l_ret_value := 2;
696
697 END IF;
698 END IF; --9975522
699
700 --9975522
701 OPEN c_get_global_value_custom('PQP_NL_PGGM_MAX_SALARY_THRESHOLD_CUSTOM', p_date_earned, p_business_group_id);
702 FETCH c_get_global_value_custom INTO l_max_salary_threshold;
703
704 IF c_get_global_value_custom%FOUND THEN
705 CLOSE c_get_global_value_custom;
706 --the value for the salary threshold has been found
707 hr_utility.set_location('PQP_NL_PGGM_MAX_SALARY_THRESHOLD_CUSTOM : '||l_max_salary_threshold,26);
708 ELSE
709 CLOSE c_get_global_value_custom;
710 --9975522
711 --query up the maximum salary dependant threshold
712 OPEN c_get_global_value('PQP_NL_PGGM_MAX_SALARY_THRESHOLD',p_date_earned);
713 FETCH c_get_global_value INTO l_max_salary_threshold;
714
715 IF c_get_global_value%FOUND THEN
716 CLOSE c_get_global_value;
717 --the value for the salary threshold has been found
718 hr_utility.set_location('PQP_NL_PGGM_MAX_SALARY_THRESHOLD : '||l_max_salary_threshold,26);
719
720 ELSE
721 CLOSE c_get_global_value;
722 --the global value was not found,raise a warning
723 hr_utility.set_location('PQP_NL_PGGM_MAX_SALARY_THRESHOLD not found',26);
724 p_err_message := p_err_message || 'No value was found for the global'||
725 'PQP_NL_PGGM_MAX_SALARY_THRESHOLD';
726 l_ret_value := 2;
727
728 END IF;
729
730 END IF; --9975522
731
732 IF (p_date_earned < to_date('01-01-2006','DD-MM-YYYY'))THEN
733 IF p_pension_salary >= l_threshold_salary THEN
734 l_threshold := l_max_salary_threshold;
735 ELSE
736 l_threshold := (p_pension_salary * l_max_salary_threshold)/
737 (l_threshold_salary + 1);
738 END IF;
739 ELSE --Legislative year 2006 Change
740 l_threshold := l_max_salary_threshold; -- 9566 fixed value
741 END IF;
742
743 hr_utility.set_location('Salary Dependant Threshold : '||l_threshold,30);
744
745 p_pension_basis := (p_pension_salary - l_threshold)
746 * p_part_time_percentage/100;
747
748 p_pension_basis := p_pension_basis / l_num_periods;
749
750 ELSIF l_basis_method = '7' THEN
751
752 --basis method is Pension Salary * Part Time Percentage
753 hr_utility.set_location('Basis method : '||'(Pension Salary * Part Time %)',20);
754 p_pension_basis := p_pension_salary * p_part_time_percentage/100;
755
756 p_pension_basis := p_pension_basis / l_num_periods;
757
758 ELSIF l_basis_method = '8' THEN
759
760 --basis method is (Pension Salary * Part Time Percentage) -
761 -- Age Dependant Threshold
762 hr_utility.set_location('Basis method : '||'(Pension Salary * Part Time %)'
763 ||' - Age Threshold',20);
764
765 --9975522
766 OPEN c_get_global_value_custom('PQP_NL_PGGM_MAX_AGE_THRESHOLD_CUSTOM', p_date_earned, p_business_group_id);
767 FETCH c_get_global_value_custom INTO l_max_age_threshold;
768
769 IF c_get_global_value_custom%FOUND THEN
770 CLOSE c_get_global_value_custom;
771 --the value for the age threshold has been found
772 hr_utility.set_location('PQP_NL_PGGM_MAX_AGE_THRESHOLD_CUSTOM : '||l_max_age_threshold,22);
773 ELSE
774 CLOSE c_get_global_value_custom;
775 --9975522
776
777 --query up the maximum age dependant threshold
778 OPEN c_get_global_value('PQP_NL_PGGM_MAX_AGE_THRESHOLD',p_date_earned);
779 FETCH c_get_global_value INTO l_max_age_threshold;
780
781 IF c_get_global_value%FOUND THEN
782 CLOSE c_get_global_value;
783 --the value for the age threshold has been found
784 hr_utility.set_location('PQP_NL_PGGM_MAX_AGE_THRESHOLD : '||l_max_age_threshold,22);
785
786 ELSE
787 CLOSE c_get_global_value;
788 --the global value was not found,raise a warning
789 hr_utility.set_location('PQP_NL_PGGM_MAX_AGE_THRESHOLD not found',22);
790 p_err_message := p_err_message || 'No value was found for the global'||
791 'PQP_NL_PGGM_MAX_AGE_THRESHOLD';
792 l_ret_value := 2;
793
794 END IF;
795
796 END IF; --9975522
797
798 l_threshold_percentage := GET_AGE_DEPENDANT_THLD(p_person_age => p_person_age
799 ,p_business_group_id => p_business_group_id
800 ,p_date_earned => p_date_earned);
801
802 IF l_threshold_percentage = 0 THEN
803 p_err_message := p_err_message ||' The age dependant threshold could not be '
804 ||'derived for this person and 0 will be used';
805 l_ret_value := 2;
806 END IF;
807
808 --Calculate Age dependent Threshold value and drop the decimals
809 l_threshold := trunc(l_max_age_threshold * l_threshold_percentage/100);
810
811 hr_utility.set_location('Age Dependant Threshold : '||l_threshold,30);
812 hr_utility.set_location('Available threshold : '||l_avlb_thld,30);
813 hr_utility.set_location('Used Threshold : '||l_used_thld,30);
814
815 l_avlb_thld := l_threshold/l_num_periods;
816
817 l_threshold := GREATEST((p_avlb_thld + l_threshold/l_num_periods - p_used_thld),0);
818
819 p_pension_basis := (p_pension_salary * p_part_time_percentage/100)/l_num_periods -
820 l_threshold;
821
822 IF p_pension_basis < 0 THEN
823 l_used_thld := (p_pension_salary * p_part_time_percentage/100)/l_num_periods;
824 ELSE
825 l_used_thld := l_threshold;
826 END IF;
827
828 END IF;
829
830 /*
831 Bug 5215600
832 SR : 5461519.993
833 --if the calculated pension basis is < 0 then restrict it to 0
834 IF p_pension_basis < 0 THEN
835 p_pension_basis := 0;
836 END IF;
837 */
838 p_avlb_thld := l_avlb_thld;
839 p_used_thld := l_used_thld;
840
841 hr_utility.set_location('Calculated pension basis value : '||p_pension_basis,40);
842 hr_utility.set_location('Available threshold : '||l_avlb_thld,40);
843 hr_utility.set_location('Used Threshold : '||l_used_thld,40);
844
845 hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,50);
846 --hr_utility.trace_off;
847
848 RETURN l_ret_value;
849
850 EXCEPTION
851 WHEN OTHERS THEN
852 hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,60);
853 p_pension_basis := 0;
854 p_err_message := 'Error occured while deriving the pension basis : '||SQLERRM;
855 RETURN 1;
856
857 END GET_PENSION_BASIS;
858
859 --
860 -- ----------------------------------------------------------------------------
861 -- |---------------------------< DO_PRORATION >-------------------------------|
862 -- ----------------------------------------------------------------------------
863 --
864 -- This function is used to perform a calendar day proration of the deduction
865 -- amount for starter/leavers
866
867 FUNCTION DO_PRORATION
868 (p_assignment_id IN NUMBER
869 ,p_payroll_action_id IN NUMBER
870 ,p_period_start_date IN DATE
871 ,p_period_end_date IN DATE
872 ,p_dedn_amount IN OUT NOCOPY NUMBER
873 ,p_err_message OUT NOCOPY VARCHAR2)
874
875 RETURN NUMBER IS
876
877 --
878 --Cursor to fetch the range of dates for which the assignment
879 --is active in the pay period
880 --
881 CURSOR c_get_asg_dates IS
882 SELECT asg.effective_start_date start_date
883 ,nvl(asg.effective_end_date,p_period_end_date) end_date
884 FROM per_all_assignments_f asg,per_assignment_status_types past
885 WHERE asg.assignment_id = p_assignment_id
886 AND asg.assignment_status_type_id = past.assignment_status_type_id
887 AND past.per_system_status = 'ACTIVE_ASSIGN'
888 AND asg.effective_start_date <= p_period_end_date
889 AND nvl(asg.effective_end_date,p_period_end_date)
890 >= p_period_start_date;
891
892 --
893 --Cursor to fetch period type of payroll attached to assignment
894 --
895 CURSOR c_get_period_type(c_payroll_action_id IN NUMBER)
896 IS
897 SELECT TPERIOD.period_type
898 FROM pay_payroll_actions PACTION
899 ,per_time_periods TPERIOD
900 WHERE PACTION.payroll_action_id = c_payroll_action_id
901 AND TPERIOD.payroll_id = PACTION.payroll_id
902 AND (PACTION.date_earned between TPERIOD.start_date
903 and TPERIOD.end_date);
904
905
906 c_asg_row c_get_asg_dates%ROWTYPE;
907
908 l_proc_name VARCHAR2(30) := 'DO_PRORATION';
909 l_ret_value NUMBER := 0;
910 l_start_date DATE;
911 l_end_date DATE;
912 l_days NUMBER := 0;
913 l_payroll_days NUMBER := 0;
914 l_non_insured_days NUMBER := 0;
915 l_proration_factor NUMBER := 1;
916 l_period_type VARCHAR2(150);
917 l_num_of_days_in_period NUMBER:=30;
918
919 BEGIN
920
921 --first fetch the range of dates when the assignment is
922 --active in the pay period
923 hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
924
925 OPEN c_get_period_type(p_payroll_action_id);
926 FETCH c_get_period_type INTO l_period_type;
927 CLOSE c_get_period_type;
928 IF l_period_type='Calendar Month' THEN
929 l_num_of_days_in_period:= 30;
930 ELSE IF l_period_type = 'Lunar Month' THEN
931 l_num_of_days_in_period:=28;
932 ELSE IF l_period_type = 'Week' THEN
933 l_num_of_days_in_period:=7;
934 ELSE IF l_period_type = 'Quarter' THEN
935 l_num_of_days_in_period:=90;
936 END IF;
937 END IF;
938 END IF;
939 END IF;
940
941 FOR c_asg_row IN c_get_asg_dates
942 LOOP
943 --loop through all assignment rows effective in this pay period
944 --and find the number of days the person is insured for
945 IF c_asg_row.start_date < p_period_start_date THEN
946 l_start_date := p_period_start_date;
947 ELSE
948 l_start_date := c_asg_row.start_date;
949 END IF;
950
951 IF c_asg_row.end_date > p_period_end_date THEN
952 l_end_date := p_period_end_date;
953 ELSE
954 l_end_date := c_asg_row.end_date;
955 END IF;
956
957 l_days := l_days + (l_end_date - l_start_date) + 1;
958
959 END LOOP;
960
961 hr_utility.set_location('Assignment was active for : '||l_days||' days',20);
962
963 --now find the days the person was not insured for in this period
964 --first find the total number of days in this pay period
965
966 l_payroll_days := p_period_end_date - p_period_start_date + 1;
967 hr_utility.set_location('Days in pay period : '||l_payroll_days,30);
968
969 --the non insured days in the difference of the pay period days and
970 --the number of days the assignment has been active
971 --if the assignment has not been active any day in the pay period
972 --then the non insured days is taken as 30 to make the proration
973 --factor 0
974
975 IF l_days > 0 THEN
976 l_non_insured_days := l_payroll_days - l_days;
977 ELSE
978 l_non_insured_days := l_num_of_days_in_period;
979 p_err_message := 'Assignment was not active in this pay period, so '||
980 'deduction amount is prorated as 0';
981 l_ret_value := 2;
982 END IF;
983
984 hr_utility.set_location('Non insured days : '||l_non_insured_days,40);
985
986 --now derive the proration factor, the days in a pay period are
987 --taken as 30 for this purpose, so we need to first find the days the
988 --person has been insured for in the period, ie (30 - days not insured)
989 --the proration factor is then this number divided by 30
990
991 l_proration_factor := (l_num_of_days_in_period - l_non_insured_days) / l_num_of_days_in_period;
992 hr_utility.set_location('Proration factor is : '||l_proration_factor,50);
993
994 --now multiply the deduction amount with the proration factor to
995 --find the prorated amount
996 hr_utility.set_location('Original deduction amount : '||p_dedn_amount,60);
997
998 p_dedn_amount := p_dedn_amount * l_proration_factor;
999
1000 hr_utility.set_location('Prorated deduction amount : '||p_dedn_amount,70);
1001 hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,80);
1002
1003 RETURN l_ret_value;
1004
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,90);
1008 p_dedn_amount := 0;
1009 p_err_message := 'Error occured while prorating : '||SQLERRM;
1010 RETURN 1;
1011
1012 END DO_PRORATION;
1013
1014 --
1015 -- ----------------------------------------------------------------------------
1016 -- |--------------------------< GET_GENERAL_INFO >-----------------------------|
1017 -- ----------------------------------------------------------------------------
1018 --
1019 -- This function is used to derive the general information such as holiday allowance
1020 -- and end of year bonus percentage from the org extra information by traversing
1021 -- the org hierarchy
1022
1023 FUNCTION GET_GENERAL_INFO
1024 (p_assignment_id IN NUMBER
1025 ,p_business_group_id IN NUMBER
1026 ,p_date_earned IN DATE
1027 ,p_code IN NUMBER
1028 ,p_value OUT NOCOPY NUMBER)
1029
1030 RETURN NUMBER IS
1031
1032 --
1033 -- Cursor to fetch the org id for the current assignment
1034 --
1035 CURSOR c_get_org_id IS
1036 SELECT organization_id
1037 FROM per_all_assignments_f
1038 WHERE assignment_id = p_assignment_id
1039 AND p_date_earned BETWEEN effective_start_date
1040 AND effective_end_date;
1041
1042 --
1043 -- Cursor to fetch the parent id for a given organization id
1044 -- for a particular hierarchy version id
1045 --
1046 CURSOR c_get_parent_id(c_org_id IN NUMBER
1047 ,c_version_id IN NUMBER) IS
1048 SELECT organization_id_parent
1049 FROM per_org_structure_elements
1050 WHERE organization_id_child = c_org_id
1051 AND org_structure_version_id = c_version_id
1052 AND business_group_id = p_business_group_id;
1053
1054 --
1055 -- Cursor to fetch the holiday allowance or eoy bonus
1056 -- percentage from the relevant segment based on the
1057 -- code passed in
1058 -- Code 0 => Holiday Allowance Percentage
1059 -- Code 1 => End Of Year Bonus Percentage
1060 --
1061 CURSOR c_get_general_info(c_org_id IN NUMBER) IS
1062 SELECT decode(p_code,0,org_information3,1,org_information4)
1063 FROM hr_organization_information
1064 WHERE organization_id = c_org_id
1065 AND org_information_context = 'PQP_NL_PGGM_INFO'
1066 AND p_date_earned BETWEEN fnd_date.canonical_to_date(org_information1)
1067 AND fnd_date.canonical_to_date(nvl(org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
1068 AND decode(p_code,0,org_information3,1,org_information4) IS NOT NULL;
1069
1070 l_proc_name VARCHAR2(30) := 'GET_GENERAL_INFO';
1071 l_org_id NUMBER;
1072 l_value hr_organization_information.org_information1%TYPE;
1073 l_loop_again NUMBER := 1;
1074 l_version_id NUMBER;
1075
1076 BEGIN
1077 hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
1078
1079 --initialise the percentage return value to 0
1080 p_value := 0;
1081
1082 -- fetch the org id for the organization this assignment is attached to
1083 OPEN c_get_org_id;
1084 FETCH c_get_org_id INTO l_org_id;
1085 IF c_get_org_id%NOTFOUND THEN
1086 --org id for the assignment could not be found
1087 --return 1 and a value of 0
1088 CLOSE c_get_org_id;
1089 p_value := 0;
1090 RETURN 1;
1091 ELSE
1092 --org id was found
1093 CLOSE c_get_org_id;
1094 hr_utility.set_location('Org id for the ASG : '||l_org_id,20);
1095
1096 -- now find the holiday allowance/eoy bonus percentage from the
1097 -- org extra information and by traversing the org hierarchy
1098 --check to see if the version id is already present for this business group
1099
1100 IF NOT g_version_info.EXISTS(p_business_group_id) THEN
1101 --no cached value of the version id exists for this BG
1102 g_version_info(p_business_group_id).version_id
1103 := pqp_pension_functions.GET_VERSION_ID(p_business_group_id
1104 ,p_date_earned);
1105
1106 END IF;
1107
1108 l_version_id := g_version_info(p_business_group_id).version_id;
1109 hr_utility.set_location('Org Hierarchy version id : '||l_version_id,30);
1110
1111 WHILE (l_loop_again = 1)
1112 LOOP
1113 OPEN c_get_general_info(l_org_id);
1114 FETCH c_get_general_info INTO l_value;
1115
1116 IF c_get_general_info%FOUND THEN
1117 --holiday allowance /eoy bonus percentages have been found at this org
1118 hr_utility.set_location('Percentages found at org : '||l_org_id,40);
1119 CLOSE c_get_general_info;
1120
1121 p_value := fnd_number.canonical_to_number(l_value);
1122
1123 hr_utility.set_location('Percentage is : '||p_value,50);
1124 --we no longer need to continue up the hierarchy
1125 l_loop_again := 0;
1126
1127 ELSE
1128 -- percentages not found at this org, now move up the hierarchy
1129 --find the parent org for this current org
1130 CLOSE c_get_general_info;
1131 OPEN c_get_parent_id(c_org_id => l_org_id
1132 ,c_version_id => l_version_id);
1133 FETCH c_get_parent_id INTO l_org_Id;
1134
1135 IF c_get_parent_id%FOUND THEN
1136 --a parent org has been found,so we can loop again
1137 CLOSE c_get_parent_id;
1138
1139 ELSE
1140 --no further parents exist,so exit the loop
1141 CLOSE c_get_parent_id;
1142 l_loop_again := 0;
1143
1144 -- no value has been found , so set it to 0
1145 p_value := 0;
1146
1147 END IF;
1148
1149 END IF;
1150
1151 END LOOP;
1152
1153 END IF;
1154
1155 hr_utility.set_location('Percentage value : '||p_value,55);
1156 hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,60);
1157
1158 RETURN 0;
1159
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,70);
1163 p_value := 0;
1164 RETURN 1;
1165
1166 END GET_GENERAL_INFO;
1167
1168
1169 -- ----------------------------------------------------------------------------
1170 -- |------------------------<GET_PENSION_SALARY >------------------------------|
1171 -- ----------------------------------------------------------------------------
1172 --
1173 FUNCTION GET_PENSION_SALARY
1174 (p_assignment_id IN NUMBER
1175 ,p_date_earned IN DATE
1176 ,p_business_group_id IN NUMBER
1177 ,p_payroll_id IN NUMBER
1178 ,p_period_start_date IN DATE
1179 ,p_period_end_date IN DATE
1180 ,p_scale_salary IN NUMBER
1181 ,p_scale_salary_h IN NUMBER
1182 ,p_scale_salary_e IN NUMBER
1183 ,p_ft_rec_payments IN NUMBER
1184 ,p_ft_rec_payments_h IN NUMBER
1185 ,p_ft_rec_payments_e IN NUMBER
1186 ,p_pt_rec_payments IN NUMBER
1187 ,p_pt_rec_payments_h IN NUMBER
1188 ,p_pt_rec_payments_e IN NUMBER
1189 ,p_salary_balance_value OUT NOCOPY NUMBER
1190 ,p_err_message OUT NOCOPY VARCHAR2
1191 ,p_err_message1 OUT NOCOPY VARCHAR2
1192 ,p_err_message2 OUT NOCOPY VARCHAR2
1193 )
1194 RETURN NUMBER IS
1195
1196 --
1197 -- Cursor to get the hire date of the person
1198 --
1199 CURSOR c_hire_dt_cur(c_asg_id IN NUMBER) IS
1200 SELECT max(date_start)
1201 FROM per_all_assignments_f asg
1202 ,per_periods_of_service pps
1203 WHERE pps.person_id = asg.person_id
1204 AND asg.assignment_id = c_asg_id
1205 AND pps.business_group_id = p_business_group_id
1206 AND date_start <= p_date_earned;
1207
1208 --
1209 --Cursor to fetch the part time percentage from the
1210 --assignment standard conditions for a given effective date
1211 --
1212 CURSOR c_pt_cur (c_effective_date IN DATE) IS
1213 SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) pt_perc
1214 FROM per_assignments_f asg
1215 ,hr_soft_coding_keyflex target
1216 WHERE asg.assignment_id = p_assignment_id
1217 AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1218 AND trunc(c_effective_date) between asg.effective_start_date
1219 AND asg.effective_end_date
1220 AND target.enabled_flag = 'Y';
1221
1222 --
1223 --Cursor to fetch the assignment start date
1224 --
1225 CURSOR c_get_asg_start IS
1226 SELECT min(effective_start_date)
1227 FROM per_all_assignments_f
1228 WHERE assignment_id = p_assignment_id;
1229
1230 --
1231 -- Cursor to get the defined balance id for a given balance and dimension
1232 --
1233 CURSOR csr_defined_bal (c_balance_name IN VARCHAR2
1234 ,c_dimension_name IN VARCHAR2
1235 ,c_business_group_id IN NUMBER) IS
1236 SELECT db.defined_balance_id
1237 FROM pay_balance_types pbt
1238 ,pay_defined_balances db
1239 ,pay_balance_dimensions bd
1240 WHERE pbt.balance_name = c_balance_name
1241 AND pbt.balance_type_id = db.balance_type_id
1242 AND bd.balance_dimension_id = db.balance_dimension_id
1243 AND bd.dimension_name = c_dimension_name
1244 AND (pbt.business_group_id = c_business_group_id OR
1245 pbt.legislation_code = 'NL')
1246 AND (db.business_group_id = pbt.business_group_id OR
1247 db.legislation_code = 'NL');
1248
1249 --
1250 -- Cursor to get the holiday allowance global
1251 --
1252 CURSOR c_global_cur(c_global_name IN VARCHAR2) IS
1253 SELECT fnd_number.canonical_to_number(global_value)
1254 FROM ff_globals_f
1255 WHERE global_name = c_global_name
1256 AND trunc (p_date_earned) BETWEEN effective_start_date
1257 AND effective_end_date;
1258
1259 --
1260 -- Cursor to get the collective agreement name as of 1 Jan/Hire Date
1261 --
1262 CURSOR c_cag_name (c_asg_id IN NUMBER
1263 ,c_eff_date IN DATE) IS
1264 SELECT cola.name
1265 FROM per_collective_agreements cola
1266 ,per_all_assignments_f asg
1267 WHERE asg.assignment_id = c_asg_id
1268 AND asg.collective_agreement_id = cola.collective_agreement_id
1269 AND cola.status = 'A'
1270 AND c_eff_date BETWEEN asg.effective_start_date
1271 AND asg.effective_end_date
1272 AND c_eff_date BETWEEN cola.start_date
1273 AND NVL(cola.end_date,to_date('31/12/4712','DD/MM/YYYY'));
1274
1275 --
1276 -- Cursor to get the number of pay periods per year.
1277 --
1278 CURSOR c_pp_cur IS
1279 SELECT pety.number_per_fiscal_year
1280 FROM pay_payrolls_f ppaf
1281 ,per_time_period_types pety
1282 WHERE ppaf.payroll_id = p_payroll_id
1283 AND ppaf.period_type = pety.period_type;
1284
1285 --
1286 --Cursor to fetch the org id for the organization
1287 --attached to this assignment
1288 --
1289 CURSOR c_get_org_id(c_eff_date IN DATE) IS
1290 SELECT organization_id
1291 FROM per_all_assignments_f
1292 WHERE assignment_id = p_assignment_id
1293 AND c_eff_date BETWEEN effective_start_date
1294 AND effective_end_date;
1295
1296 --
1297 -- Cursor to get the value for EOY Bonus override percent
1298 --
1299 CURSOR c_eoy_per_or (c_asg_id IN NUMBER
1300 ,c_effective_date IN DATE) IS
1301 select min (fffunc.cn(decode(
1302 decode(INPUTV.uom,'M','N','N','N','I','N',null),'N',decode(INPUTV.hot_default_flag,'Y',nvl(EEV.screen_entry_value,
1303 nvl(LIV.default_value,INPUTV.default_value)),'N',EEV.screen_entry_value),null))) eoy_or
1304 FROM pay_element_entry_values_f EEV,
1305 pay_element_entries_f EE,
1306 pay_link_input_values_f LIV,
1307 pay_input_values_f INPUTV,
1308 pay_element_types_f pet
1309 WHERE INPUTV.element_type_id = pet.element_type_id
1310 AND INPUTV.name = 'End of Year Bonus Percentage'
1311 AND c_effective_date BETWEEN INPUTV.effective_start_date
1312 AND INPUTV.effective_end_date
1313 AND INPUTV.element_type_id = pet.element_type_id
1314 AND c_effective_date BETWEEN pet.effective_start_date
1315 AND pet.effective_end_date
1316 AND pet.element_name = 'PGGM Pensions General Information'
1317 AND pet.legislation_code = 'NL'
1318 AND LIV.input_value_id = INPUTV.input_value_id
1319 AND c_effective_date BETWEEN LIV.effective_start_date
1320 AND LIV.effective_end_date
1321 AND EEV.input_value_id + 0 = INPUTV.input_value_id
1322 AND EEV.element_entry_id = EE.element_entry_id
1323 AND EEV.effective_start_date = EE.effective_start_date
1324 AND EEV.effective_end_date = EE.effective_end_date
1325 AND EE.element_link_id = LIV.element_link_id
1326 AND EE.assignment_id = c_asg_id
1327 AND c_effective_date BETWEEN EE.effective_start_date
1328 AND EE.effective_end_date ;
1329
1330 --
1331 -- Cursor to get the value for Holiday Allowance override percent
1332 --
1333 CURSOR c_ha_per_or (c_asg_id IN NUMBER
1334 ,c_effective_date IN DATE) IS
1335 select min (fffunc.cn(decode(
1336 decode(INPUTV.uom,'M','N','N','N','I','N',null),'N',decode(INPUTV.hot_default_flag,'Y',nvl(EEV.screen_entry_value,
1337 nvl(LIV.default_value,INPUTV.default_value)),'N',EEV.screen_entry_value),null))) ha_or
1338 FROM pay_element_entry_values_f EEV,
1339 pay_element_entries_f EE,
1340 pay_link_input_values_f LIV,
1341 pay_input_values_f INPUTV,
1342 pay_element_types_f pet
1343 WHERE INPUTV.element_type_id = pet.element_type_id
1344 AND INPUTV.name = 'Holiday Allowance Percentage'
1345 AND c_effective_date BETWEEN INPUTV.effective_start_date
1346 AND INPUTV.effective_end_date
1347 AND INPUTV.element_type_id = pet.element_type_id
1348 AND c_effective_date BETWEEN pet.effective_start_date
1349 AND pet.effective_end_date
1350 AND pet.element_name = 'PGGM Pensions General Information'
1351 AND pet.legislation_code = 'NL'
1352 AND LIV.input_value_id = INPUTV.input_value_id
1353 AND c_effective_date BETWEEN LIV.effective_start_date
1354 AND LIV.effective_end_date
1355 AND EEV.input_value_id + 0 = INPUTV.input_value_id
1356 AND EEV.element_entry_id = EE.element_entry_id
1357 AND EEV.effective_start_date = EE.effective_start_date
1358 AND EEV.effective_end_date = EE.effective_end_date
1359 AND EE.element_link_id = LIV.element_link_id
1360 AND EE.assignment_id = c_asg_id
1361 AND c_effective_date BETWEEN EE.effective_start_date
1362 AND EE.effective_end_date ;
1363
1364 -- =============================================================================
1365 --Cursor to fetch Start date of a year
1366 -- =============================================================================
1367 CURSOR c_get_period_start_date ( c_year IN VARCHAR2
1368 )
1369 IS
1370 SELECT NVL(min(PTP.start_date),to_date('0101'||c_year,'DDMMYYYY'))
1371 FROM per_time_periods PTP
1372 WHERE
1373 PTP.payroll_id = p_payroll_id
1374 AND (substr(PTP.period_name,4,4)=c_year
1375 OR substr(PTP.period_name,3,4)=c_year);
1376
1377 l_loop_again NUMBER;
1378 l_valid_pt NUMBER;
1379 l_org_id NUMBER;
1380 l_run_year NUMBER;
1381 l_begin_of_year_date DATE;
1382 l_end_of_last_year DATE;
1383 l_effective_date DATE;
1384 l_hire_date DATE;
1385 l_asg_st_date DATE;
1386 l_jan_hire_ptp NUMBER;
1387 l_error_message VARCHAR2(1000);
1388 l_defined_balance_id NUMBER;
1389 l_scale_salary NUMBER := nvl(p_scale_salary,0);
1390 l_scale_salary_h NUMBER := nvl(p_scale_salary_h,0);
1391 l_scale_salary_e NUMBER := nvl(p_scale_salary_e,0);
1392 l_ft_rec_paymnt NUMBER := nvl(p_ft_rec_payments,0);
1393 l_ft_rec_paymnt_h NUMBER := nvl(p_ft_rec_payments_h,0);
1394 l_ft_rec_paymnt_e NUMBER := nvl(p_ft_rec_payments_e,0);
1395 l_pt_rec_paymnt NUMBER := nvl(p_pt_rec_payments,0);
1396 l_pt_rec_paymnt_h NUMBER := nvl(p_pt_rec_payments_h,0);
1397 l_pt_rec_paymnt_e NUMBER := nvl(p_pt_rec_payments_e,0);
1398 l_holiday_allowance NUMBER := 0;
1399 l_holiday_allow_per NUMBER := 0;
1400 l_min_holiday_allow NUMBER := 0;
1401 l_min_holiday_char VARCHAR2(80);
1402 l_eoy_bonus NUMBER := 0;
1403 l_eoy_bonus_per NUMBER := 0;
1404 l_min_eoy_bonus NUMBER := 0;
1405 l_min_eoy_bonus_char VARCHAR2(80);
1406 l_cag_name per_collective_agreements.name%TYPE;
1407 l_max_periods NUMBER;
1408 l_max_ptp NUMBER := 0;
1409 l_prev_max_ptp NUMBER := 0;
1410 l_ret_val NUMBER := 0;
1411 l_error_status CHAR;
1412 l_message_flag CHAR;
1413 UDT_CONTAINS_NO_DATA EXCEPTION;
1414 l_proc_name VARCHAR2(30) := 'GET_PENSION_SALARY';
1415 l_asg_eoy_bonus_per NUMBER;
1416 l_asg_ha_per NUMBER;
1417 l_ignore_eoy_cag NUMBER;
1418 l_ignore_ha_cag NUMBER;
1419 l_min_age_holiday_allow VARCHAR2(50);
1420 l_person_age NUMBER;
1421 l_first_date_of_year DATE;
1422
1423 BEGIN
1424 hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
1425 --hr_utility.trace_on(null,'SS');
1426
1427 --initialise the error message params
1428 p_err_message := ' ';
1429 p_err_message1 := ' ';
1430 p_err_message2 := ' ';
1431
1432 --derive the year in which payroll is being run
1433 l_run_year := to_number(to_char(p_date_earned,'YYYY'));
1434 hr_utility.set_location('Payroll run year : '||l_run_year,20);
1435
1436 OPEN c_get_period_start_date(l_run_year);
1437 FETCH c_get_period_start_date INTO l_first_date_of_year;
1438 CLOSE c_get_period_start_date;
1439
1440 -- Get the first date of the run year
1441 l_begin_of_year_date := l_first_date_of_year;
1442
1443 -- Get the latest start date of the assignment
1444 OPEN c_get_asg_start;
1445 FETCH c_get_asg_start INTO l_asg_st_date;
1446
1447 IF c_get_asg_start%FOUND THEN
1448 CLOSE c_get_asg_start;
1449 ELSE
1450 CLOSE c_get_asg_start;
1451 p_err_message := 'Error: Unable to find the start date of the assignment';
1452 p_salary_balance_value := 0;
1453 RETURN 1;
1454
1455 END IF;
1456
1457 hr_utility.set_location('Asg start date : '||l_asg_st_date,30);
1458
1459 -- Get the hire date
1460 OPEN c_hire_dt_cur (p_assignment_id);
1461 FETCH c_hire_dt_cur INTO l_hire_date;
1462
1463 IF c_hire_dt_cur%FOUND THEN
1464 CLOSE c_hire_dt_cur;
1465 hr_utility.set_location('Hire date is : '||l_hire_date,40);
1466 -- The effective date is now the valid assignemnt
1467 --start date for the assignment
1468 l_effective_date := pqp_nl_abp_functions.get_valid_start_date
1469 (p_assignment_id,
1470 p_date_earned,
1471 l_error_status,
1472 l_error_message);
1473
1474 --if an error occured while fetching the valid start date
1475 IF(l_error_status = trim(to_char(1,'9'))) THEN
1476 hr_utility.set_location('Error occured while fetching valid start date',50);
1477 fnd_message.set_name('PQP',l_error_message);
1478 p_err_message :='Error : '|| fnd_message.get();
1479 p_salary_balance_value :=0;
1480 RETURN 1;
1481 ELSE
1482 hr_utility.set_location('Valid start date fetched : '||l_effective_date,50);
1483 END IF;
1484
1485 ELSE
1486 CLOSE c_hire_dt_cur;
1487 hr_utility.set_location('Hire date could not be found',60);
1488 p_err_message := 'Error: Unable to find the hire date for the person ';
1489 p_salary_balance_value :=0;
1490 RETURN 1;
1491 END IF;
1492
1493 -- Get the maximum number of periods in a year.
1494 OPEN c_pp_cur;
1495 FETCH c_pp_cur INTO l_max_periods;
1496
1497 IF c_pp_cur%NOTFOUND THEN
1498 p_err_message := 'Error: Unable to find the pay periods per year';
1499 p_salary_balance_value :=0;
1500 CLOSE c_pp_cur;
1501 RETURN 1;
1502 ELSE
1503 CLOSE c_pp_cur;
1504 END IF;
1505
1506 hr_utility.set_location('Number of periods in a year : '||l_max_periods,70);
1507 hr_utility.set_location('Deriving the part time percentage',80);
1508
1509 -- Calculate the ptp as of 1 jan or Hire Date
1510 OPEN c_pt_cur (l_effective_date);
1511 FETCH c_pt_cur INTO l_jan_hire_ptp;
1512
1513 IF c_pt_cur%NOTFOUND THEN
1514 CLOSE c_pt_cur;
1515 hr_utility.set_location('No value found for part time percentage',90);
1516 p_err_message := 'Error: Unable to find the part time percentage.';
1517 p_err_message := p_err_message || 'Please enter a value as of 1 January or Hire Date';
1518 p_salary_balance_value :=0;
1519 RETURN 1;
1520 ELSE
1521 CLOSE c_pt_cur;
1522 END IF;
1523
1524 IF l_jan_hire_ptp = 0 THEN
1525 l_jan_hire_ptp := 100;
1526 END IF;
1527
1528 l_jan_hire_ptp := LEAST(l_jan_hire_ptp,125);
1529
1530 hr_utility.set_location('Part Time Percentage used for salary : '||l_jan_hire_ptp,100);
1531 l_jan_hire_ptp := l_jan_hire_ptp/100;
1532
1533 -- Divide all the part time balances by this value
1534
1535 l_pt_rec_paymnt := l_pt_rec_paymnt/l_jan_hire_ptp;
1536 l_pt_rec_paymnt_h := l_pt_rec_paymnt_h/l_jan_hire_ptp;
1537 l_pt_rec_paymnt_e := l_pt_rec_paymnt_e/l_jan_hire_ptp;
1538
1539 --
1540 -- EOY Bonus Calculation
1541 -- This section will calculate the end of year bonus
1542 -- based on the EOY Bonus percentage specified at the
1543 -- org level. These will be derived from the following
1544 -- three balances.
1545 --
1546 -- PGGM Scale Salary For End Of Year Bonus
1547 -- PGGM Full Time Recurring Payments For End Of Year Bonus
1548 -- PGGM Part Time Recurring Payments For End Of Year Bonus
1549 --
1550
1551 --
1552 -- Check to see if the EOY Bonus has been overridden
1553 -- at the assignment input value level. If it has been overridden
1554 -- and is not null. Use this value and do not derive from the org.
1555 -- Otherwise derive it from the org.
1556 -- It is imp to note that if the value is 0, then do not consider
1557 -- the min at the CAG level.
1558 --
1559 OPEN c_eoy_per_or(p_assignment_id
1560 ,l_effective_date);
1561 FETCH c_eoy_per_or INTO l_asg_eoy_bonus_per;
1562 IF c_eoy_per_or%FOUND THEN
1563
1564 l_eoy_bonus_per := l_asg_eoy_bonus_per;
1565 hr_utility.set_location('EOY Bonus Override at the I/V Level',105);
1566
1567 END IF;
1568
1569 CLOSE c_eoy_per_or;
1570
1571 --
1572 -- Call the function to derive EOY Bonus
1573 -- percentage from the org level only if it is null at the asg i/v level.
1574 --
1575 IF NVL(l_eoy_bonus_per,-99) = -99 THEN
1576
1577 l_ret_val := pqp_nl_pggm_functions.get_general_info
1578 (p_assignment_id => p_assignment_id
1579 ,p_business_group_id => p_business_group_id
1580 ,p_date_earned => p_date_earned
1581 ,p_code => 1
1582 ,p_value => l_eoy_bonus_per
1583 );
1584
1585 IF l_ret_val <> 0 THEN
1586 l_eoy_bonus_per := 0;
1587 END IF;
1588 hr_utility.set_location('EOY Bonus Calculated from the org',106);
1589
1590 END IF;
1591
1592 --
1593 -- Check if the final calculated value for EOY Bonus % is 0
1594 -- If it is zero then ignore the min EOY Bonus specified
1595 -- at the CAG ( Collective Agreement) level.
1596 --
1597 IF l_eoy_bonus_per = 0 THEN
1598 l_ignore_eoy_cag := 1;
1599 ELSE
1600 l_ignore_eoy_cag := 0;
1601 END IF;
1602
1603 hr_utility.set_location('EOY Bonus % used for calculation is : '||l_eoy_bonus_per,110);
1604
1605 --
1606 -- Calculate the EOY Bonus that needs to be included for
1607 -- PGGM Pension Salary calculations
1608 --
1609 l_eoy_bonus := (l_scale_salary_e
1610 +l_ft_rec_paymnt_e
1611 +l_pt_rec_paymnt_e
1612 )
1613 * l_eoy_bonus_per/100
1614 * l_max_periods;
1615
1616 hr_utility.set_location('EOY Bonus amount calculated : '||l_eoy_bonus,120);
1617
1618 --
1619 -- Holiday Allowance Calculation
1620 -- This section will calculate the holiday allowance
1621 -- based on the holiday allowance percentage specified
1622 -- at the org level. These will be derived from the
1623 -- following three balances.
1624 --
1625 -- PGGM Scale Salary For Holiday Allowance
1626 -- PGGM Full Time Recurring Payments For Holiday Allowance
1627 -- PGGM Part Time Recurring Payments For Holiday Allowance
1628 --
1629
1630 --
1631 -- Check to see if the Holiday Allowance % has been overridden
1632 -- at the assignment input value level. If it has been overridden
1633 -- and is not null, Use this value and do not derive from the org.
1634 -- Otherwise derive it from the org.
1635 -- It is imp to note that if the value is 0, the do not consider
1636 -- the min at the cag level.
1637 --
1638 OPEN c_ha_per_or(p_assignment_id
1639 ,l_effective_date);
1640 FETCH c_ha_per_or INTO l_asg_ha_per;
1641
1642 IF c_ha_per_or%FOUND THEN
1643
1644 l_holiday_allow_per := l_asg_ha_per;
1645 hr_utility.set_location('Holiday Allowance overridden at the iv level ',125);
1646
1647 END IF;
1648
1649 CLOSE c_ha_per_or;
1650 --
1651 -- Call the function to derive Holiday allowance
1652 -- percentage from the org level only if it is null at the asg i/v level.
1653 --
1654 IF NVL(l_holiday_allow_per,-99) = -99 THEN
1655
1656 l_ret_val := pqp_nl_pggm_functions.get_general_info
1657 (p_assignment_id => p_assignment_id
1658 ,p_business_group_id => p_business_group_id
1659 ,p_date_earned => p_date_earned
1660 ,p_code => 0
1661 ,p_value => l_holiday_allow_per
1662 );
1663
1664 IF l_ret_val <> 0 THEN
1665 l_holiday_allow_per := 0;
1666 END IF;
1667 hr_utility.set_location('Holiday Allowance Calculated from the org',125);
1668
1669 END IF;
1670
1671 hr_utility.set_location('Holiday allow % used for calculation is : '
1672 ||l_holiday_allow_per,130);
1673
1674 --
1675 -- Check if the final calculated value for HA % is 0
1676 -- If it is zero then ignore the min HA specified
1677 -- at the CAG (Collective Agreement) level.
1678 --
1679 IF l_holiday_allow_per = 0 THEN
1680 l_ignore_ha_cag := 1;
1681 ELSE
1682 l_ignore_ha_cag := 0;
1683 END IF;
1684
1685 l_holiday_allowance := (l_scale_salary_h
1686 +l_ft_rec_paymnt_h
1687 +l_pt_rec_paymnt_h
1688 )
1689 * l_holiday_allow_per/100
1690 * l_max_periods;
1691
1692 hr_utility.set_location('Calculated holiday allowance : '||l_holiday_allowance,140);
1693
1694 -- Find the minimum values for EOY Bonus and Holiday
1695 -- allowance that may have been defined for this
1696 -- assignment. The value of holiday allowance and EOY
1697 -- bonus used in pension salary calculation should
1698 -- not be lesser than this defined minimum
1699
1700 -- First get the CAG Name
1701 OPEN c_cag_name (c_asg_id => p_assignment_id
1702 ,c_eff_date => l_effective_date) ;
1703
1704 FETCH c_cag_name INTO l_cag_name;
1705
1706 IF c_cag_name%FOUND THEN
1707
1708 -- We found a CAG at the asg level . Now get the Min End Of
1709 -- Year Bonus for this CAG from the UDT.
1710 hr_utility.set_location('CAG attached to the asg : '||l_cag_name,150);
1711 BEGIN
1712 l_min_eoy_bonus_char := hruserdt.get_table_value
1713 (
1714 p_bus_group_id => p_business_group_id
1715 ,p_table_name => 'PQP_NL_MIN_END_OF_YEAR_BONUS'
1716 ,p_col_name => 'PGGM Minimum End Of Year Bonus'
1717 ,p_row_value => l_cag_name
1718 ,p_effective_date => l_effective_date
1719 );
1720
1721 --
1722 -- Calculate the min EOY bonus only if the override percentage is not 0
1723 --
1724 IF NVL(l_ignore_eoy_cag,0) = 0 THEN
1725 l_min_eoy_bonus := fnd_number.canonical_to_number(NVL(l_min_eoy_bonus_char,'0'));
1726 ELSIF NVL(l_ignore_eoy_cag,0) = 1 THEN
1727 l_min_eoy_bonus := 0;
1728 END IF;
1729
1730 EXCEPTION
1731 WHEN NO_DATA_FOUND THEN
1732 hr_utility.set_location('Exception occured :NO_DATA_FOUND ',160);
1733 l_min_eoy_bonus := 0;
1734 END;
1735
1736 -- We found a CAG at the asg level . Now get the Min holiday
1737 -- allowance for this CAG from the UDT.
1738 BEGIN
1739 l_min_age_holiday_allow:= hruserdt.get_table_value
1740 (
1741 p_bus_group_id => p_business_group_id
1742 ,p_table_name => 'PQP_NL_MIN_HOLIDAY_ALLOWANCE'
1743 ,p_col_name => 'PGGM Min Age for Holiday Allowance'
1744 ,p_row_value => l_cag_name
1745 ,p_effective_date => l_effective_date
1746 );
1747 --Get the age of the person
1748 hr_utility.set_location('l_begin_of_year_date'||l_begin_of_year_date,161);
1749 l_person_age := Get_age(p_assignment_id,l_effective_date,l_begin_of_year_date);
1750 hr_utility.set_location('l_person_age'||l_person_age,162);
1751 hr_utility.set_location('l_min_age_holiday_allow'||l_min_age_holiday_allow,163);
1752
1753 --Comapre it with min age defined at CAG level
1754 IF (l_person_age < to_number(l_min_age_holiday_allow) ) THEN
1755 --Person is not eligible for Min holiday allowance
1756 l_min_holiday_allow :=0;
1757 p_err_message2 :=p_err_message2|| 'Min. holiday allowance is set to 0 as'
1758 ||'person age is less than min age defined at collective agreement ';
1759 l_ret_val := 2;
1760 ELSE
1761 l_min_holiday_char := hruserdt.get_table_value
1762 (
1763 p_bus_group_id => p_business_group_id
1764 ,p_table_name => 'PQP_NL_MIN_HOLIDAY_ALLOWANCE'
1765 ,p_col_name => 'PGGM Minimum Holiday Allowance'
1766 ,p_row_value => l_cag_name
1767 ,p_effective_date => l_effective_date
1768 );
1769 hr_utility.set_location('l_min_holiday_char'||l_min_holiday_char,165);
1770 --
1771 -- Calculate the min HA only if the override percentage is not 0
1772 --
1773 IF NVL(l_ignore_ha_cag,0) = 0 THEN
1774 l_min_holiday_allow := fnd_number.canonical_to_number(NVL(l_min_holiday_char,'0'));
1775 ELSIF NVL(l_ignore_ha_cag,0) = 1 THEN
1776 l_min_holiday_allow := 0;
1777 END IF;
1778 END IF; -- End of person age check
1779
1780 EXCEPTION
1781 WHEN NO_DATA_FOUND THEN
1782 hr_utility.set_location('Exception occured :NO_DATA_FOUND ',170);
1783 l_min_holiday_allow := 0;
1784 END;
1785
1786 --no collective agreement defined for this assignment
1787 ELSE
1788 -- set Min EOY bonus and Min Holiday allowance to 0 .
1789 hr_utility.set_location('No CAG attached',160);
1790 l_min_eoy_bonus := 0;
1791 l_min_holiday_allow := 0;
1792 END IF;
1793
1794 CLOSE c_cag_name;
1795
1796 hr_utility.set_location('Min. EOY bonus : '||l_min_eoy_bonus,170);
1797 hr_utility.set_location('Min. Holiday allowance : '||l_min_holiday_allow,180);
1798
1799 --now compare the calculated eoy bonus and the min. eoy bonus
1800 IF l_eoy_bonus < l_min_eoy_bonus THEN
1801 p_err_message1 :=p_err_message1|| 'Min. end of year bonus for the collective agreement'
1802 ||' was used for Pension Salary calculation ';
1803 l_ret_val := 2;
1804 END IF;
1805
1806 l_eoy_bonus := GREATEST(l_eoy_bonus,nvl(l_min_eoy_bonus,0));
1807
1808 hr_utility.set_location('Final EOY Bonus value : '||l_eoy_bonus,190);
1809
1810 --now compare the calculated holiday allowance and the min. holiday allowance
1811 IF l_holiday_allowance < l_min_holiday_allow THEN
1812 p_err_message2 :=p_err_message2|| 'Min. holiday allowance for the collective agreement'
1813 ||' was used for Pension Salary calculation ';
1814 l_ret_val := 2;
1815 END IF;
1816
1817 l_holiday_allowance := GREATEST(l_holiday_allowance,nvl(l_min_holiday_allow,0));
1818
1819 hr_utility.set_location('Final Holiday Allowance value : '||l_holiday_allowance,200);
1820
1821 p_salary_balance_value := (l_scale_salary
1822 +l_ft_rec_paymnt
1823 +l_pt_rec_paymnt
1824 ) * l_max_periods
1825 +l_holiday_allowance
1826 +l_eoy_bonus;
1827
1828 p_salary_balance_value := CEIL(p_salary_balance_value);
1829
1830 hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,210);
1831 --hr_utility.trace_off;
1832 RETURN l_ret_val;
1833
1834 EXCEPTION
1835 WHEN OTHERS THEN
1836 p_salary_balance_value := 0;
1837 hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,220);
1838 p_err_message := 'Error occured while deriving the PGGM Pension Salary'
1839 ||' value : '||SQLERRM;
1840 RETURN 1;
1841
1842 END GET_PENSION_SALARY;
1843
1844 -- ----------------------------------------------------------------------------
1845 -- |----------------------<GET_PART_TIME_PERCENTAGE >--------------------------|
1846 -- ----------------------------------------------------------------------------
1847 --
1848 FUNCTION GET_PART_TIME_PERCENTAGE
1849 (p_assignment_id IN NUMBER
1850 ,p_payroll_action_id IN NUMBER
1851 ,p_date_earned IN DATE
1852 ,p_business_group_id IN NUMBER
1853 ,p_period_start_date IN DATE
1854 ,p_period_end_date IN DATE
1855 ,p_override_value IN NUMBER
1856 ,p_parental_leave IN VARCHAR2
1857 ,p_extra_hours IN NUMBER
1858 ,p_hours_worked OUT NOCOPY NUMBER
1859 ,p_total_hours OUT NOCOPY NUMBER
1860 ,p_part_time_percentage OUT NOCOPY NUMBER
1861 ,p_err_message OUT NOCOPY VARCHAR2
1862 )
1863 RETURN NUMBER IS
1864
1865 --
1866 -- Cursor to fetch the earliest start date of the assignment
1867 -- for this period
1868 --
1869 CURSOR c_get_st_date IS
1870 SELECT min(effective_start_date)
1871 FROM per_all_assignments_f
1872 WHERE assignment_id = p_assignment_id
1873 AND effective_end_date >= p_period_start_date;
1874 --
1875 --Cursor to fetch the part time percentage from the
1876 --assignment standard conditions for a given effective date
1877 --
1878 CURSOR c_get_ptp(c_effective_date IN DATE) IS
1879 SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'0')) pt_perc
1880 FROM per_assignments_f asg
1881 ,hr_soft_coding_keyflex target
1882 WHERE asg.assignment_id = p_assignment_id
1883 AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1884 AND c_effective_date between asg.effective_start_date
1885 AND asg.effective_end_date
1886 AND target.enabled_flag = 'Y';
1887
1888 --
1889 -- cursor to fetch the date tracked assignment rows
1890 -- for all changes in the pay period
1891 -- the calendar day average value of all these changes
1892 -- is taken as the average part time percentage of this period
1893 --
1894 CURSOR c_get_asg_rows IS
1895 SELECT fnd_number.canonical_to_number(NVL(target.segment28,'0')) hours_worked
1896 ,nvl(asg.normal_hours,0) total_hours
1897 ,nvl(asg.frequency,'W') freq
1898 ,asg.effective_start_date start_date
1899 ,asg.effective_end_date end_date
1900 FROM per_assignments_f asg
1901 ,per_assignment_status_types ast
1902 ,hr_soft_coding_keyflex target
1903 WHERE asg.assignment_id = p_assignment_id
1904 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1905 AND ast.per_system_status = 'ACTIVE_ASSIGN'
1906 AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1907 AND asg.effective_start_date <= p_period_end_date
1908 AND asg.effective_end_date >= p_period_start_date;
1909
1910
1911 --
1912 --Cursor to fetch period type of payroll attached to assignment
1913 --
1914 CURSOR c_get_period_type(c_payroll_action_id IN NUMBER)
1915 IS
1916 SELECT TPERIOD.period_type
1917 FROM pay_payroll_actions PACTION
1918 ,per_time_periods TPERIOD
1919 WHERE PACTION.payroll_action_id = c_payroll_action_id
1920 AND TPERIOD.payroll_id = PACTION.payroll_id
1921 AND (PACTION.date_earned between TPERIOD.start_date
1922 and TPERIOD.end_date);
1923
1924 c_asg_row c_get_asg_rows%ROWTYPE;
1925
1926 l_proc_name VARCHAR2(30) := 'GET_PART_TIME_PERCENTAGE';
1927 l_eff_date DATE;
1928 l_ptp NUMBER := 100;
1929 l_emp_kind NUMBER := 1;
1930 l_start_date DATE;
1931 l_end_date DATE;
1932 l_days NUMBER;
1933 l_pay_days NUMBER;
1934 l_tot_days NUMBER := 0;
1935 hours_worked NUMBER := 0;
1936 total_hours NUMBER := 0;
1937 l_hours_worked NUMBER;
1938 l_total_hours NUMBER;
1939 l_ret_value NUMBER := 0;
1940 l_num_periods NUMBER :=12;
1941 l_period_type VARCHAR2(150):='Calendar Month';
1942 l_num_of_days_in_period NUMBER:=30;
1943
1944 BEGIN
1945 hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
1946
1947 OPEN c_get_period_type(p_payroll_action_id);
1948 FETCH c_get_period_type INTO l_period_type;
1949 CLOSE c_get_period_type;
1950 IF l_period_type='Calendar Month' THEN
1951 l_num_of_days_in_period:= 30;
1952 ELSE IF l_period_type = 'Lunar Month' THEN
1953 l_num_of_days_in_period:=28;
1954 ELSE IF l_period_type = 'Week' THEN
1955 l_num_of_days_in_period:=7;
1956 ELSE IF l_period_type = 'Quarter' THEN
1957 l_num_of_days_in_period:=90;
1958 END IF;
1959 END IF;
1960 END IF;
1961 END IF;
1962
1963 p_err_message := '';
1964 --
1965 -- check if the person is a full timer or part timer
1966 -- if the person is a part timer, then the final part time
1967 -- percentage cannot exceed 100% and it cannot exceed 125%
1968 -- for a full timer
1969 -- If the part time percentage derived from the standard
1970 -- conditions is < 100 then the person is a part time employee
1971 -- if the value is equal to or exceeds 100 then this is a full
1972 -- timer
1973 --
1974
1975 -- fetch the part time percentage on the period start date
1976 -- first fetch the date on which this part time percentage needs
1977 -- to be derived on
1978
1979 OPEN c_get_st_date;
1980 FETCH c_get_st_date INTO l_eff_date;
1981 CLOSE c_get_st_date;
1982
1983 hr_utility.set_location('Start date of asg for this period : '||l_eff_date,20);
1984
1985 -- the date on which ptp needs to be derived is the greater of the asg start date
1986 -- and the period start date
1987
1988 l_eff_date := GREATEST(l_eff_date,p_period_start_date);
1989
1990 hr_utility.set_location('date on which check is performed : '||l_eff_date,30);
1991
1992 --Get number of payroll periods in a year
1993 OPEN c_get_num_periods_per_year(p_payroll_action_id);
1994 FETCH c_get_num_periods_per_year INTO l_num_periods;
1995 CLOSE c_get_num_periods_per_year;
1996
1997
1998 --now derive the ptp from standard conditions on this eff date to check
1999 -- for part/full timers
2000
2001 OPEN c_get_ptp(l_eff_date);
2002 FETCH c_get_ptp INTO l_ptp;
2003 CLOSE c_get_ptp;
2004
2005 hr_utility.set_location('PTP on the start date : '||l_ptp,40);
2006
2007 IF l_ptp < 100 THEN
2008 -- the employee is a part time employee
2009 l_emp_kind := 0;
2010 ELSE
2011 -- the employee is full time
2012 l_emp_kind := 1;
2013 END IF;
2014
2015 hr_utility.set_location('Employee Kind : '||l_emp_kind,50);
2016
2017 --calculate the number of days in this pay period
2018 l_pay_days := TRUNC(p_period_end_date) - TRUNC(p_period_start_date) + 1;
2019
2020 --now loop through the asg changes to find the total average hours worked
2021 FOR c_asg_row IN c_get_asg_rows
2022 LOOP
2023 --if the start date is before period start,use period start
2024 IF c_asg_row.start_date < p_period_start_date THEN
2025 l_start_date := p_period_start_date;
2026 ELSE
2027 l_start_date := c_asg_row.start_date;
2028 END IF;
2029
2030 -- if end date is after period end,use period end
2031 IF c_asg_row.end_date > p_period_end_date THEN
2032 l_end_date := p_period_end_date;
2033 ELSE
2034 l_end_date := c_asg_row.end_date;
2035 END IF;
2036
2037 hr_utility.set_location('Start Date : '||l_start_date,60);
2038 hr_utility.set_location('End Date : '||l_end_date,70);
2039 hr_utility.set_location('Hours worked : '||c_asg_row.hours_worked,80);
2040 hr_utility.set_location('Total Hours : '||c_asg_row.total_hours,90);
2041 hr_utility.set_location('Frequency : '||c_asg_row.freq,100);
2042
2043 --calculate the hours per week based on the frequency
2044 IF c_asg_row.freq = 'D' THEN
2045 l_hours_worked := c_asg_row.hours_worked * 5;
2046 l_total_hours := c_asg_row.total_hours * 5;
2047 ELSIF c_asg_row.freq = 'M' THEN
2048 l_hours_worked := c_asg_row.hours_worked * l_num_periods/52;
2049 l_total_hours := c_asg_row.total_hours * l_num_periods/52;
2050 ELSIF c_asg_row.freq = 'Y' THEN
2051 l_hours_worked := c_asg_row.hours_worked/52;
2052 l_total_hours := c_asg_row.total_hours/52;
2053 ELSE
2054 l_hours_worked := c_asg_row.hours_worked;
2055 l_total_hours := c_asg_row.total_hours;
2056 END IF;
2057
2058 --calculate the days for this asg row
2059 l_days := TRUNC(l_end_date) - TRUNC(l_start_date) + 1;
2060 l_tot_days := l_tot_days + l_days;
2061
2062 --the total days should always be l_num_of_days_in_period,
2063 --adjust the last l_days so that it adds up to l_num_of_days_in_period
2064 IF l_tot_days = l_pay_days THEN
2065 --the number of days is equal to the number of days in the
2066 --pay period,but this has to always be l_num_of_days_in_period
2067 l_days := l_days + (l_num_of_days_in_period - l_tot_days);
2068 END IF;
2069
2070 hr_utility.set_location('l_days : '||l_days,110);
2071
2072 hours_worked := hours_worked +
2073 l_hours_worked * l_days/l_num_of_days_in_period;
2074
2075 total_hours := total_hours +
2076 l_total_hours * l_days/l_num_of_days_in_period;
2077 END LOOP;
2078
2079 --hours worked and total hours for the month is the average calculated
2080 -- above * 52/12
2081 hours_worked := hours_worked * 52/l_num_periods;
2082 total_hours := total_hours * 52/l_num_periods;
2083
2084 hr_utility.set_location('Avg. Hours worked : '||hours_worked,120);
2085 hr_utility.set_location('Avg. Total Hours : '||total_hours,130);
2086
2087 --
2088 -- check if an override has been entered, if so then the part time
2089 -- percentage is the same as the value entered in the override
2090 -- however for the case where the person is on parental leave,
2091 -- the override part time percentage needs to be added with
2092 -- the extra hours entered
2093 --
2094
2095 IF p_override_value <> -99 THEN
2096 --override has been entered
2097 l_ptp := p_override_value;
2098 hr_utility.set_location('Override exists : '||l_ptp,140);
2099 --check if the employee is also on parental leave
2100 IF p_parental_leave = 'Y' THEN
2101 --we also need to add any increase in ptp due to extra hours
2102 hr_utility.set_location('parental leave exists',150);
2103 IF total_hours > 0 THEN
2104 --only if the employee is part time,extra hours can be considered
2105 IF l_emp_kind = 0 THEN
2106 l_ptp := l_ptp + (nvl(p_extra_hours,0)/total_hours) * 100;
2107 END IF;
2108 END IF;
2109 END IF;
2110
2111 ELSE
2112 --no override exists
2113 hr_utility.set_location('No override exists',160);
2114 --final ptp value is calculated value + increase due to extra hours
2115 IF total_hours > 0 THEN
2116 --only if emp is part time, then consider extra hours
2117 IF l_emp_kind = 0 THEN
2118 l_ptp := ((hours_worked + nvl(p_extra_hours,0))/total_hours) * 100;
2119 ELSE
2120 l_ptp := (hours_worked/total_hours) * 100;
2121 END IF;
2122 ELSE
2123 l_ptp := 0;
2124 END IF;
2125 END IF;
2126
2127 l_ptp := nvl(l_ptp,0);
2128
2129 hr_utility.set_location('Calculated PTP : '||l_ptp,170);
2130 /*
2131 PGGM 2006 Legislative change
2132 No max limit for Part time percentage
2133 --
2134 -- Restrict the final value of PTP to 125
2135 --
2136 IF l_ptp > 125 THEN
2137 p_err_message := 'Part time percentage has been restricted to 125%';
2138 l_ret_value := 2;
2139 END IF;
2140
2141 l_ptp := LEAST(l_ptp,125);
2142 */
2143
2144 hr_utility.set_location('Final ptp value : '||l_ptp,180);
2145
2146 p_part_time_percentage := round(nvl(l_ptp,0),2);
2147 p_hours_worked := round(nvl(hours_worked,0),2);
2148 p_total_hours := round(nvl(total_hours,0),2);
2149
2150 hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,190);
2151
2152 RETURN l_ret_value;
2153
2154 EXCEPTION
2155 WHEN OTHERS THEN
2156 hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,200);
2157 p_part_time_percentage := 0;
2158 p_hours_worked := 0;
2159 p_total_hours := 0;
2160 p_err_message := 'Error occured while deriving part time percentage : '||SQLERRM;
2161 RETURN 1;
2162
2163 END GET_PART_TIME_PERCENTAGE;
2164
2165 -- ----------------------------------------------------------------------------
2166 -- |----------------------<GET_INCI_WKR_CODE >--------------------------------|
2167 -- ----------------------------------------------------------------------------
2168 --
2169 FUNCTION GET_INCI_WKR_CODE
2170 (p_assignment_id IN NUMBER
2171 ,p_business_group_id IN NUMBER
2172 ,p_date_earned IN DATE
2173 ,p_result_value OUT NOCOPY VARCHAR2
2174 ,p_err_message OUT NOCOPY VARCHAR2
2175 )
2176 RETURN NUMBER IS
2177 CURSOR csr_get_inci_wkr_code(c_assignment_id NUMBER,c_date_earned DATE) IS
2178 SELECT scl.SEGMENT1
2179 FROM per_all_assignments_f asg
2180 ,hr_soft_coding_keyflex scl
2181 WHERE asg.assignment_id = c_assignment_id
2182 AND c_date_earned BETWEEN asg.effective_start_date
2183 AND asg.effective_end_date
2184 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
2185
2186 l_proc_name VARCHAR2(30) := 'GET_INCI_WKR_CODE';
2187 l_inci_code VARCHAR2(1):='N';
2188 l_ret_value NUMBER:=0;
2189 BEGIN
2190 hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
2191
2192 OPEN csr_get_inci_wkr_code(p_assignment_id,p_date_earned);
2193 FETCH csr_get_inci_wkr_code INTO l_inci_code;
2194
2195 IF csr_get_inci_wkr_code%FOUND THEN
2196 p_result_value:=l_inci_code;
2197 ELSE
2198 p_result_value:='N';
2199 END IF;
2200 CLOSE csr_get_inci_wkr_code;
2201
2202 hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,190);
2203 RETURN l_ret_value;
2204 EXCEPTION
2205 WHEN OTHERS THEN
2206 hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,200);
2207 p_err_message := 'Error occured while deriving incidental worker code : '||SQLERRM;
2208 RETURN 1;
2209 END GET_INCI_WKR_CODE;
2210
2211 END PQP_NL_PGGM_FUNCTIONS;
[Home] [Help]