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