[Home] [Help]
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;
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
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
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:
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:
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
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:
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;
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
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
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
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
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;
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
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:
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
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
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;
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
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
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;
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;
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:
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
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:
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;
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;
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
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
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;
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
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:
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:
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);
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
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:
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;
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:
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);
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);
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;
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:
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:
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
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:
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;
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:
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
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)
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);
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);
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:
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
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:
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:
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
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:
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
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:
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
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;
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 := ' ';
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;
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;
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,
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;
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;
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;
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);
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;
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;
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:
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;
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: --
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
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
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;
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 : '
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
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
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
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
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:
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
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
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
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
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:
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
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'
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'
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
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
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
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:
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;
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:
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;
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;
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:
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);
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:
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
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
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;
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:
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
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
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);
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;
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
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: --
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);
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
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;
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:
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);
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: