[Home] [Help]
71: l_proc_name VARCHAR2(30) := 'CHECK_ELIGIBILITY';
72:
73: BEGIN
74:
75: hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
76: --Query up the min. and max. age specified at the pension type
77:
78: OPEN c_get_min_max_ages;
79: FETCH c_get_min_max_ages INTO l_min_age,l_max_age;
81: IF c_get_min_max_ages%FOUND THEN
82: --the min and max ages have been found,now check for eligibility
83:
84: CLOSE c_get_min_max_ages;
85: hr_utility.set_location('Min. and Max. ages are : '||l_min_age||
86: ' and '||l_max_age,20);
87:
88: IF p_person_age BETWEEN l_min_age and l_max_age THEN
89: --the person is eligible for the pension type
87:
88: IF p_person_age BETWEEN l_min_age and l_max_age THEN
89: --the person is eligible for the pension type
90:
91: hr_utility.set_location('Person is eligible, age is : '||p_person_age,30);
92: p_eligible := 1;
93:
94: ELSE
95: --the person is not eligible for this pension type
92: p_eligible := 1;
93:
94: ELSE
95: --the person is not eligible for this pension type
96: hr_utility.set_location('Person is not eligible, age is : '||p_person_age,30);
97: p_err_message := 'This person is not eligible for the pension type';
98: p_eligible := 0;
99: l_ret_value := 2;
100:
102:
103: ELSE
104: --no row could be found for this pension type
105: CLOSE c_get_min_max_ages;
106: hr_utility.set_location('No row could be found for this pension type',40);
107: p_err_message := 'No row could be found for this pension type on this date';
108: p_eligible := 0;
109: l_ret_value := 2;
110:
109: l_ret_value := 2;
110:
111: END IF;
112:
113: hr_utility.set_location('Returning from : '||g_pkg_name||l_proc_name,50);
114:
115: RETURN 0;
116:
117: EXCEPTION
115: RETURN 0;
116:
117: EXCEPTION
118: WHEN OTHERS THEN
119: hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,60);
120: p_eligible := 0;
121: p_err_message := 'Error occured when determining eligibility : '||SQLERRM;
122: RETURN 1;
123:
200:
201:
202: BEGIN
203:
204: hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
205: --First fetch the HR Org Id for the current assignment
206: OPEN c_get_org_id;
207: FETCH c_get_org_id INTO l_org_id;
208: CLOSE c_get_org_id;
205: --First fetch the HR Org Id for the current assignment
206: OPEN c_get_org_id;
207: FETCH c_get_org_id INTO l_org_id;
208: CLOSE c_get_org_id;
209: hr_utility.set_location('Org Id for this assignment is : '||l_org_id,20);
210:
211: -- Now fetch the contribution percentages from the ORG EIT
212: -- This will proceed in a loop from the HR Organization
213: -- of this assignment and then up the org the hierarchy until
217: l_emp_or_total := 'Employee';
218: ELSE
219: l_emp_or_total := 'Total';
220: END IF;
221: hr_utility.set_location('Now deriving contribution percentage for : '||l_emp_or_total,25);
222:
223: --check to see if the version id is already present for this business group
224: IF NOT g_version_info.EXISTS(p_business_group_id) THEN
225: --no cached value of the version id exists for this BG
229:
230: END IF;
231:
232: l_version_id := g_version_info(p_business_group_id).version_id;
233: hr_utility.set_location('Org Hierarchy version id : '||l_version_id,27);
234:
235: WHILE (l_loop_again = 1)
236: LOOP
237: OPEN c_get_contrib_frm_org(l_org_id);
238: FETCH c_get_contrib_frm_org INTO l_contrib_value;
239:
240: IF c_get_contrib_frm_org%FOUND THEN
241: --Contribution percentages have been found at this org
242: hr_utility.set_location('Contribution values found at org : '||l_org_id,30);
243: CLOSE c_get_contrib_frm_org;
244:
245: p_contrib_value := fnd_number.canonical_to_number(l_contrib_value);
246:
247: --set the flag indicating that contribution percentage has been found at the
248: --ORG level, so we dont need to continue any further
249: l_org_contrib_found := 1;
250:
251: hr_utility.set_location('Contribution percentage is : '||p_contrib_value,40);
252: --we no longer need to continue up the hierarchy
253: l_loop_again := 0;
254:
255: ELSE
282: --now check to see if the contribution percentage
283: --has been found at the ORG level or do we need to
284: --derive it from the pension type
285: IF l_org_contrib_found = 0 THEN
286: hr_utility.set_location('No contribution values found at the org'||
287: ' ,now deriving it from the pension type',50);
288: --contribution values have not been found at the ORG level
289: --query up the contribution percentages from the pension type
290: OPEN c_get_contrib_frm_pt;
291: FETCH c_get_contrib_frm_pt INTO p_contrib_value;
292:
293: IF c_get_contrib_frm_pt%FOUND THEN
294: --contribution percentages have been found from the pension type
295: hr_utility.set_location('Contribution derived from pension type : '
296: ||p_contrib_value,60);
297: CLOSE c_get_contrib_frm_pt;
298:
299: ELSE
298:
299: ELSE
300: --no data has been found for this pension type, this is an error condition
301: CLOSE c_get_contrib_frm_pt;
302: hr_utility.set_location('No data could be found for the pension type on date earned',70);
303: p_contrib_value := 0;
304: p_err_message := 'No data could be found for the pension type attached to '
305: ||'this scheme on the date earned.';
306: l_ret_value := 2;
308: END IF;
309:
310: END IF;
311:
312: hr_utility.set_location('Returning from : '||g_pkg_name||l_proc_name,80);
313: RETURN l_ret_value;
314:
315: EXCEPTION
316: WHEN OTHERS THEN
313: RETURN l_ret_value;
314:
315: EXCEPTION
316: WHEN OTHERS THEN
317: hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,90);
318: p_contrib_value := 0;
319: p_err_message := 'Error occured when determining contribution : '||SQLERRM;
320: RETURN 1;
321:
372: l_max_age NUMBER;
373:
374: BEGIN
375:
376: hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
377:
378: --9975522
379: --FIRST CHECK INTO THE CUSTOME USER TABLE IF NOT THEN SEEDED
380: --first check to see if the user defined table for age dependant
379: --FIRST CHECK INTO THE CUSTOME USER TABLE IF NOT THEN SEEDED
380: --first check to see if the user defined table for age dependant
381: --thresholds has a row for the person's age, then return this value
382: BEGIN
383: hr_utility.set_location('Checking for threshold percentage for age : '||p_person_age,200);
384: l_return_value :=
385: hruserdt.get_table_value
386: (
387: p_bus_group_id => p_business_group_id
392: );
393:
394: l_value_found := 1;
395: l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
396: hr_utility.set_location('Threshold percentage derived as : '||l_threshold_percentage,300);
397:
398: EXCEPTION
399: WHEN NO_DATA_FOUND THEN
400: l_value_found := 0;
406: --then the value applicable for the person is the same as the value defined
407: --for the maximum age
408: IF l_value_found = 0 THEN
409:
410: hr_utility.set_location('No row found for the person''s age,now finding max age',400);
411: --query up the maximum age defined in the rows for this user defined tbl
412: OPEN c_get_max_age_custom;
413: FETCH c_get_max_age_custom INTO l_max_age;
414: IF c_get_max_age_custom%FOUND THEN
413: FETCH c_get_max_age_custom INTO l_max_age;
414: IF c_get_max_age_custom%FOUND THEN
415:
416: --Found the maximum age upto which thresholds have been defined
417: hr_utility.set_location('Max age in threshold udt : '||l_max_age,500);
418: CLOSE c_get_max_age_custom;
419:
420: --if the person's age is > the max age, then the threshold is the same
421: --as the value defined for this max age
432: ,p_effective_date => p_date_earned
433: );
434: l_value_found := 1;
435: l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
436: hr_utility.set_location('value of max age used : '||l_threshold_percentage,600);
437:
438: EXCEPTION
439: WHEN NO_DATA_FOUND THEN
440: l_value_found := 0;
451: --else no rows have been defined for the user defined table
452: --so return the percentage as 0
453: ELSE
454:
455: hr_utility.set_location('No rows were defined for the udt',700);
456: CLOSE c_get_max_age_custom;
457: l_value_found := 0;
458: l_threshold_percentage := 0;
459:
465: IF l_value_found = 0 THEN --IF NO VALUE FOUND IN CUSTOM TABLE --9975522
466: --first check to see if the user defined table for age dependant
467: --thresholds has a row for the person's age, then return this value
468: BEGIN
469: hr_utility.set_location('Checking for threshold percentage for age : '||p_person_age,20);
470: l_return_value :=
471: hruserdt.get_table_value
472: (
473: p_bus_group_id => p_business_group_id
478: );
479:
480: l_value_found := 1;
481: l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
482: hr_utility.set_location('Threshold percentage derived as : '||l_threshold_percentage,30);
483:
484: EXCEPTION
485: WHEN NO_DATA_FOUND THEN
486: l_value_found := 0;
492: --then the value applicable for the person is the same as the value defined
493: --for the maximum age
494: IF l_value_found = 0 THEN
495:
496: hr_utility.set_location('No row found for the person''s age,now finding max age',40);
497: --query up the maximum age defined in the rows for this user defined tbl
498: OPEN c_get_max_age;
499: FETCH c_get_max_age INTO l_max_age;
500: IF c_get_max_age%FOUND THEN
499: FETCH c_get_max_age INTO l_max_age;
500: IF c_get_max_age%FOUND THEN
501:
502: --Found the maximum age upto which thresholds have been defined
503: hr_utility.set_location('Max age in threshold udt : '||l_max_age,50);
504: CLOSE c_get_max_age;
505:
506: --if the person's age is > the max age, then the threshold is the same
507: --as the value defined for this max age
517: ,p_row_value => l_max_age
518: ,p_effective_date => p_date_earned
519: );
520: l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
521: hr_utility.set_location('value of max age used : '||l_threshold_percentage,60);
522:
523: EXCEPTION
524: WHEN NO_DATA_FOUND THEN
525: l_threshold_percentage := 0;
535: --else no rows have been defined for the user defined table
536: --so return the percentage as 0
537: ELSE
538:
539: hr_utility.set_location('No rows were defined for the udt',70);
540: CLOSE c_get_max_age;
541: l_threshold_percentage := 0;
542:
543: END IF;
544:
545: END IF;
546: END IF; -- IF NO VALUE FOUND IN CUSTOM TABLE --9975522
547:
548: hr_utility.set_location('Value of threshold percentage is : '||l_threshold_percentage,75);
549: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,80);
550:
551: RETURN l_threshold_percentage;
552:
545: END IF;
546: END IF; -- IF NO VALUE FOUND IN CUSTOM TABLE --9975522
547:
548: hr_utility.set_location('Value of threshold percentage is : '||l_threshold_percentage,75);
549: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,80);
550:
551: RETURN l_threshold_percentage;
552:
553: EXCEPTION
551: RETURN l_threshold_percentage;
552:
553: EXCEPTION
554: WHEN OTHERS THEN
555: hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,90);
556: RETURN 0;
557:
558: END GET_AGE_DEPENDANT_THLD;
559:
642: l_basis_method pqp_pension_types_f.pension_basis_calc_method%TYPE;
643: l_num_periods NUMBER :=12;
644:
645: BEGIN
646: --hr_utility.trace_on(null,'SS');
647: hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
648:
649: --first fetch the basis method for this pension type
650: OPEN c_get_basis_method;
643: l_num_periods NUMBER :=12;
644:
645: BEGIN
646: --hr_utility.trace_on(null,'SS');
647: hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
648:
649: --first fetch the basis method for this pension type
650: OPEN c_get_basis_method;
651: FETCH c_get_basis_method INTO l_basis_method;
662:
663: --basis method is (Pension Salary - Salary dependant threshold)
664: -- * Part Time Percentage
665:
666: hr_utility.set_location('Basis method : '||'(Pension Salary - Salary threshold)'
667: ||' * Part Time %',20);
668: --9975522
669: OPEN c_get_global_value_custom('PQP_NL_PGGM_THRESHOLD_SALARY_CUSTOM', p_date_earned, p_business_group_id);
670: FETCH c_get_global_value_custom INTO l_threshold_salary;
671: --9975522
672: IF c_get_global_value_custom%FOUND THEN
673: CLOSE c_get_global_value_custom;
674: --the value for the threshold salary has been found
675: hr_utility.set_location('PQP_NL_PGGM_THRESHOLD_SALARY_CUSTOM : '||l_threshold_salary,25);
676: ELSE
677: CLOSE c_get_global_value_custom;
678: --9975522
679:
684:
685: IF c_get_global_value%FOUND THEN
686: CLOSE c_get_global_value;
687: --the value for the threshold salary has been found
688: hr_utility.set_location('PQP_NL_PGGM_THRESHOLD_SALARY : '||l_threshold_salary,25);
689:
690: ELSE
691: CLOSE c_get_global_value;
692: --the global value was not found,raise a warning
689:
690: ELSE
691: CLOSE c_get_global_value;
692: --the global value was not found,raise a warning
693: hr_utility.set_location('PQP_NL_PGGM_THRESHOLD_SALARY not found',25);
694: p_err_message := 'No value was found for the global PQP_NL_PGGM_THRESHOLD_SALARY';
695: l_ret_value := 2;
696:
697: END IF;
703:
704: IF c_get_global_value_custom%FOUND THEN
705: CLOSE c_get_global_value_custom;
706: --the value for the salary threshold has been found
707: hr_utility.set_location('PQP_NL_PGGM_MAX_SALARY_THRESHOLD_CUSTOM : '||l_max_salary_threshold,26);
708: ELSE
709: CLOSE c_get_global_value_custom;
710: --9975522
711: --query up the maximum salary dependant threshold
714:
715: IF c_get_global_value%FOUND THEN
716: CLOSE c_get_global_value;
717: --the value for the salary threshold has been found
718: hr_utility.set_location('PQP_NL_PGGM_MAX_SALARY_THRESHOLD : '||l_max_salary_threshold,26);
719:
720: ELSE
721: CLOSE c_get_global_value;
722: --the global value was not found,raise a warning
719:
720: ELSE
721: CLOSE c_get_global_value;
722: --the global value was not found,raise a warning
723: hr_utility.set_location('PQP_NL_PGGM_MAX_SALARY_THRESHOLD not found',26);
724: p_err_message := p_err_message || 'No value was found for the global'||
725: 'PQP_NL_PGGM_MAX_SALARY_THRESHOLD';
726: l_ret_value := 2;
727:
739: ELSE --Legislative year 2006 Change
740: l_threshold := l_max_salary_threshold; -- 9566 fixed value
741: END IF;
742:
743: hr_utility.set_location('Salary Dependant Threshold : '||l_threshold,30);
744:
745: p_pension_basis := (p_pension_salary - l_threshold)
746: * p_part_time_percentage/100;
747:
749:
750: ELSIF l_basis_method = '7' THEN
751:
752: --basis method is Pension Salary * Part Time Percentage
753: hr_utility.set_location('Basis method : '||'(Pension Salary * Part Time %)',20);
754: p_pension_basis := p_pension_salary * p_part_time_percentage/100;
755:
756: p_pension_basis := p_pension_basis / l_num_periods;
757:
758: ELSIF l_basis_method = '8' THEN
759:
760: --basis method is (Pension Salary * Part Time Percentage) -
761: -- Age Dependant Threshold
762: hr_utility.set_location('Basis method : '||'(Pension Salary * Part Time %)'
763: ||' - Age Threshold',20);
764:
765: --9975522
766: OPEN c_get_global_value_custom('PQP_NL_PGGM_MAX_AGE_THRESHOLD_CUSTOM', p_date_earned, p_business_group_id);
768:
769: IF c_get_global_value_custom%FOUND THEN
770: CLOSE c_get_global_value_custom;
771: --the value for the age threshold has been found
772: hr_utility.set_location('PQP_NL_PGGM_MAX_AGE_THRESHOLD_CUSTOM : '||l_max_age_threshold,22);
773: ELSE
774: CLOSE c_get_global_value_custom;
775: --9975522
776:
780:
781: IF c_get_global_value%FOUND THEN
782: CLOSE c_get_global_value;
783: --the value for the age threshold has been found
784: hr_utility.set_location('PQP_NL_PGGM_MAX_AGE_THRESHOLD : '||l_max_age_threshold,22);
785:
786: ELSE
787: CLOSE c_get_global_value;
788: --the global value was not found,raise a warning
785:
786: ELSE
787: CLOSE c_get_global_value;
788: --the global value was not found,raise a warning
789: hr_utility.set_location('PQP_NL_PGGM_MAX_AGE_THRESHOLD not found',22);
790: p_err_message := p_err_message || 'No value was found for the global'||
791: 'PQP_NL_PGGM_MAX_AGE_THRESHOLD';
792: l_ret_value := 2;
793:
807:
808: --Calculate Age dependent Threshold value and drop the decimals
809: l_threshold := trunc(l_max_age_threshold * l_threshold_percentage/100);
810:
811: hr_utility.set_location('Age Dependant Threshold : '||l_threshold,30);
812: hr_utility.set_location('Available threshold : '||l_avlb_thld,30);
813: hr_utility.set_location('Used Threshold : '||l_used_thld,30);
814:
815: l_avlb_thld := l_threshold/l_num_periods;
808: --Calculate Age dependent Threshold value and drop the decimals
809: l_threshold := trunc(l_max_age_threshold * l_threshold_percentage/100);
810:
811: hr_utility.set_location('Age Dependant Threshold : '||l_threshold,30);
812: hr_utility.set_location('Available threshold : '||l_avlb_thld,30);
813: hr_utility.set_location('Used Threshold : '||l_used_thld,30);
814:
815: l_avlb_thld := l_threshold/l_num_periods;
816:
809: l_threshold := trunc(l_max_age_threshold * l_threshold_percentage/100);
810:
811: hr_utility.set_location('Age Dependant Threshold : '||l_threshold,30);
812: hr_utility.set_location('Available threshold : '||l_avlb_thld,30);
813: hr_utility.set_location('Used Threshold : '||l_used_thld,30);
814:
815: l_avlb_thld := l_threshold/l_num_periods;
816:
817: l_threshold := GREATEST((p_avlb_thld + l_threshold/l_num_periods - p_used_thld),0);
837: */
838: p_avlb_thld := l_avlb_thld;
839: p_used_thld := l_used_thld;
840:
841: hr_utility.set_location('Calculated pension basis value : '||p_pension_basis,40);
842: hr_utility.set_location('Available threshold : '||l_avlb_thld,40);
843: hr_utility.set_location('Used Threshold : '||l_used_thld,40);
844:
845: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,50);
838: p_avlb_thld := l_avlb_thld;
839: p_used_thld := l_used_thld;
840:
841: hr_utility.set_location('Calculated pension basis value : '||p_pension_basis,40);
842: hr_utility.set_location('Available threshold : '||l_avlb_thld,40);
843: hr_utility.set_location('Used Threshold : '||l_used_thld,40);
844:
845: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,50);
846: --hr_utility.trace_off;
839: p_used_thld := l_used_thld;
840:
841: hr_utility.set_location('Calculated pension basis value : '||p_pension_basis,40);
842: hr_utility.set_location('Available threshold : '||l_avlb_thld,40);
843: hr_utility.set_location('Used Threshold : '||l_used_thld,40);
844:
845: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,50);
846: --hr_utility.trace_off;
847:
841: hr_utility.set_location('Calculated pension basis value : '||p_pension_basis,40);
842: hr_utility.set_location('Available threshold : '||l_avlb_thld,40);
843: hr_utility.set_location('Used Threshold : '||l_used_thld,40);
844:
845: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,50);
846: --hr_utility.trace_off;
847:
848: RETURN l_ret_value;
849:
842: hr_utility.set_location('Available threshold : '||l_avlb_thld,40);
843: hr_utility.set_location('Used Threshold : '||l_used_thld,40);
844:
845: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,50);
846: --hr_utility.trace_off;
847:
848: RETURN l_ret_value;
849:
850: EXCEPTION
848: RETURN l_ret_value;
849:
850: EXCEPTION
851: WHEN OTHERS THEN
852: hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,60);
853: p_pension_basis := 0;
854: p_err_message := 'Error occured while deriving the pension basis : '||SQLERRM;
855: RETURN 1;
856:
919: BEGIN
920:
921: --first fetch the range of dates when the assignment is
922: --active in the pay period
923: hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
924:
925: OPEN c_get_period_type(p_payroll_action_id);
926: FETCH c_get_period_type INTO l_period_type;
927: CLOSE c_get_period_type;
957: l_days := l_days + (l_end_date - l_start_date) + 1;
958:
959: END LOOP;
960:
961: hr_utility.set_location('Assignment was active for : '||l_days||' days',20);
962:
963: --now find the days the person was not insured for in this period
964: --first find the total number of days in this pay period
965:
963: --now find the days the person was not insured for in this period
964: --first find the total number of days in this pay period
965:
966: l_payroll_days := p_period_end_date - p_period_start_date + 1;
967: hr_utility.set_location('Days in pay period : '||l_payroll_days,30);
968:
969: --the non insured days in the difference of the pay period days and
970: --the number of days the assignment has been active
971: --if the assignment has not been active any day in the pay period
980: 'deduction amount is prorated as 0';
981: l_ret_value := 2;
982: END IF;
983:
984: hr_utility.set_location('Non insured days : '||l_non_insured_days,40);
985:
986: --now derive the proration factor, the days in a pay period are
987: --taken as 30 for this purpose, so we need to first find the days the
988: --person has been insured for in the period, ie (30 - days not insured)
988: --person has been insured for in the period, ie (30 - days not insured)
989: --the proration factor is then this number divided by 30
990:
991: l_proration_factor := (l_num_of_days_in_period - l_non_insured_days) / l_num_of_days_in_period;
992: hr_utility.set_location('Proration factor is : '||l_proration_factor,50);
993:
994: --now multiply the deduction amount with the proration factor to
995: --find the prorated amount
996: hr_utility.set_location('Original deduction amount : '||p_dedn_amount,60);
992: hr_utility.set_location('Proration factor is : '||l_proration_factor,50);
993:
994: --now multiply the deduction amount with the proration factor to
995: --find the prorated amount
996: hr_utility.set_location('Original deduction amount : '||p_dedn_amount,60);
997:
998: p_dedn_amount := p_dedn_amount * l_proration_factor;
999:
1000: hr_utility.set_location('Prorated deduction amount : '||p_dedn_amount,70);
996: hr_utility.set_location('Original deduction amount : '||p_dedn_amount,60);
997:
998: p_dedn_amount := p_dedn_amount * l_proration_factor;
999:
1000: hr_utility.set_location('Prorated deduction amount : '||p_dedn_amount,70);
1001: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,80);
1002:
1003: RETURN l_ret_value;
1004:
997:
998: p_dedn_amount := p_dedn_amount * l_proration_factor;
999:
1000: hr_utility.set_location('Prorated deduction amount : '||p_dedn_amount,70);
1001: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,80);
1002:
1003: RETURN l_ret_value;
1004:
1005: EXCEPTION
1003: RETURN l_ret_value;
1004:
1005: EXCEPTION
1006: WHEN OTHERS THEN
1007: hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,90);
1008: p_dedn_amount := 0;
1009: p_err_message := 'Error occured while prorating : '||SQLERRM;
1010: RETURN 1;
1011:
1073: l_loop_again NUMBER := 1;
1074: l_version_id NUMBER;
1075:
1076: BEGIN
1077: hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
1078:
1079: --initialise the percentage return value to 0
1080: p_value := 0;
1081:
1090: RETURN 1;
1091: ELSE
1092: --org id was found
1093: CLOSE c_get_org_id;
1094: hr_utility.set_location('Org id for the ASG : '||l_org_id,20);
1095:
1096: -- now find the holiday allowance/eoy bonus percentage from the
1097: -- org extra information and by traversing the org hierarchy
1098: --check to see if the version id is already present for this business group
1105:
1106: END IF;
1107:
1108: l_version_id := g_version_info(p_business_group_id).version_id;
1109: hr_utility.set_location('Org Hierarchy version id : '||l_version_id,30);
1110:
1111: WHILE (l_loop_again = 1)
1112: LOOP
1113: OPEN c_get_general_info(l_org_id);
1114: FETCH c_get_general_info INTO l_value;
1115:
1116: IF c_get_general_info%FOUND THEN
1117: --holiday allowance /eoy bonus percentages have been found at this org
1118: hr_utility.set_location('Percentages found at org : '||l_org_id,40);
1119: CLOSE c_get_general_info;
1120:
1121: p_value := fnd_number.canonical_to_number(l_value);
1122:
1119: CLOSE c_get_general_info;
1120:
1121: p_value := fnd_number.canonical_to_number(l_value);
1122:
1123: hr_utility.set_location('Percentage is : '||p_value,50);
1124: --we no longer need to continue up the hierarchy
1125: l_loop_again := 0;
1126:
1127: ELSE
1151: END LOOP;
1152:
1153: END IF;
1154:
1155: hr_utility.set_location('Percentage value : '||p_value,55);
1156: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,60);
1157:
1158: RETURN 0;
1159:
1152:
1153: END IF;
1154:
1155: hr_utility.set_location('Percentage value : '||p_value,55);
1156: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,60);
1157:
1158: RETURN 0;
1159:
1160: EXCEPTION
1158: RETURN 0;
1159:
1160: EXCEPTION
1161: WHEN OTHERS THEN
1162: hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,70);
1163: p_value := 0;
1164: RETURN 1;
1165:
1166: END GET_GENERAL_INFO;
1420: l_person_age NUMBER;
1421: l_first_date_of_year DATE;
1422:
1423: BEGIN
1424: hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
1425: --hr_utility.trace_on(null,'SS');
1426:
1427: --initialise the error message params
1428: p_err_message := ' ';
1421: l_first_date_of_year DATE;
1422:
1423: BEGIN
1424: hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
1425: --hr_utility.trace_on(null,'SS');
1426:
1427: --initialise the error message params
1428: p_err_message := ' ';
1429: p_err_message1 := ' ';
1430: p_err_message2 := ' ';
1431:
1432: --derive the year in which payroll is being run
1433: l_run_year := to_number(to_char(p_date_earned,'YYYY'));
1434: hr_utility.set_location('Payroll run year : '||l_run_year,20);
1435:
1436: OPEN c_get_period_start_date(l_run_year);
1437: FETCH c_get_period_start_date INTO l_first_date_of_year;
1438: CLOSE c_get_period_start_date;
1453: RETURN 1;
1454:
1455: END IF;
1456:
1457: hr_utility.set_location('Asg start date : '||l_asg_st_date,30);
1458:
1459: -- Get the hire date
1460: OPEN c_hire_dt_cur (p_assignment_id);
1461: FETCH c_hire_dt_cur INTO l_hire_date;
1461: FETCH c_hire_dt_cur INTO l_hire_date;
1462:
1463: IF c_hire_dt_cur%FOUND THEN
1464: CLOSE c_hire_dt_cur;
1465: hr_utility.set_location('Hire date is : '||l_hire_date,40);
1466: -- The effective date is now the valid assignemnt
1467: --start date for the assignment
1468: l_effective_date := pqp_nl_abp_functions.get_valid_start_date
1469: (p_assignment_id,
1472: l_error_message);
1473:
1474: --if an error occured while fetching the valid start date
1475: IF(l_error_status = trim(to_char(1,'9'))) THEN
1476: hr_utility.set_location('Error occured while fetching valid start date',50);
1477: fnd_message.set_name('PQP',l_error_message);
1478: p_err_message :='Error : '|| fnd_message.get();
1479: p_salary_balance_value :=0;
1480: RETURN 1;
1478: p_err_message :='Error : '|| fnd_message.get();
1479: p_salary_balance_value :=0;
1480: RETURN 1;
1481: ELSE
1482: hr_utility.set_location('Valid start date fetched : '||l_effective_date,50);
1483: END IF;
1484:
1485: ELSE
1486: CLOSE c_hire_dt_cur;
1483: END IF;
1484:
1485: ELSE
1486: CLOSE c_hire_dt_cur;
1487: hr_utility.set_location('Hire date could not be found',60);
1488: p_err_message := 'Error: Unable to find the hire date for the person ';
1489: p_salary_balance_value :=0;
1490: RETURN 1;
1491: END IF;
1502: ELSE
1503: CLOSE c_pp_cur;
1504: END IF;
1505:
1506: hr_utility.set_location('Number of periods in a year : '||l_max_periods,70);
1507: hr_utility.set_location('Deriving the part time percentage',80);
1508:
1509: -- Calculate the ptp as of 1 jan or Hire Date
1510: OPEN c_pt_cur (l_effective_date);
1503: CLOSE c_pp_cur;
1504: END IF;
1505:
1506: hr_utility.set_location('Number of periods in a year : '||l_max_periods,70);
1507: hr_utility.set_location('Deriving the part time percentage',80);
1508:
1509: -- Calculate the ptp as of 1 jan or Hire Date
1510: OPEN c_pt_cur (l_effective_date);
1511: FETCH c_pt_cur INTO l_jan_hire_ptp;
1511: FETCH c_pt_cur INTO l_jan_hire_ptp;
1512:
1513: IF c_pt_cur%NOTFOUND THEN
1514: CLOSE c_pt_cur;
1515: hr_utility.set_location('No value found for part time percentage',90);
1516: p_err_message := 'Error: Unable to find the part time percentage.';
1517: p_err_message := p_err_message || 'Please enter a value as of 1 January or Hire Date';
1518: p_salary_balance_value :=0;
1519: RETURN 1;
1526: END IF;
1527:
1528: l_jan_hire_ptp := LEAST(l_jan_hire_ptp,125);
1529:
1530: hr_utility.set_location('Part Time Percentage used for salary : '||l_jan_hire_ptp,100);
1531: l_jan_hire_ptp := l_jan_hire_ptp/100;
1532:
1533: -- Divide all the part time balances by this value
1534:
1561: FETCH c_eoy_per_or INTO l_asg_eoy_bonus_per;
1562: IF c_eoy_per_or%FOUND THEN
1563:
1564: l_eoy_bonus_per := l_asg_eoy_bonus_per;
1565: hr_utility.set_location('EOY Bonus Override at the I/V Level',105);
1566:
1567: END IF;
1568:
1569: CLOSE c_eoy_per_or;
1584:
1585: IF l_ret_val <> 0 THEN
1586: l_eoy_bonus_per := 0;
1587: END IF;
1588: hr_utility.set_location('EOY Bonus Calculated from the org',106);
1589:
1590: END IF;
1591:
1592: --
1599: ELSE
1600: l_ignore_eoy_cag := 0;
1601: END IF;
1602:
1603: hr_utility.set_location('EOY Bonus % used for calculation is : '||l_eoy_bonus_per,110);
1604:
1605: --
1606: -- Calculate the EOY Bonus that needs to be included for
1607: -- PGGM Pension Salary calculations
1612: )
1613: * l_eoy_bonus_per/100
1614: * l_max_periods;
1615:
1616: hr_utility.set_location('EOY Bonus amount calculated : '||l_eoy_bonus,120);
1617:
1618: --
1619: -- Holiday Allowance Calculation
1620: -- This section will calculate the holiday allowance
1641:
1642: IF c_ha_per_or%FOUND THEN
1643:
1644: l_holiday_allow_per := l_asg_ha_per;
1645: hr_utility.set_location('Holiday Allowance overridden at the iv level ',125);
1646:
1647: END IF;
1648:
1649: CLOSE c_ha_per_or;
1663:
1664: IF l_ret_val <> 0 THEN
1665: l_holiday_allow_per := 0;
1666: END IF;
1667: hr_utility.set_location('Holiday Allowance Calculated from the org',125);
1668:
1669: END IF;
1670:
1671: hr_utility.set_location('Holiday allow % used for calculation is : '
1667: hr_utility.set_location('Holiday Allowance Calculated from the org',125);
1668:
1669: END IF;
1670:
1671: hr_utility.set_location('Holiday allow % used for calculation is : '
1672: ||l_holiday_allow_per,130);
1673:
1674: --
1675: -- Check if the final calculated value for HA % is 0
1688: )
1689: * l_holiday_allow_per/100
1690: * l_max_periods;
1691:
1692: hr_utility.set_location('Calculated holiday allowance : '||l_holiday_allowance,140);
1693:
1694: -- Find the minimum values for EOY Bonus and Holiday
1695: -- allowance that may have been defined for this
1696: -- assignment. The value of holiday allowance and EOY
1706: IF c_cag_name%FOUND THEN
1707:
1708: -- We found a CAG at the asg level . Now get the Min End Of
1709: -- Year Bonus for this CAG from the UDT.
1710: hr_utility.set_location('CAG attached to the asg : '||l_cag_name,150);
1711: BEGIN
1712: l_min_eoy_bonus_char := hruserdt.get_table_value
1713: (
1714: p_bus_group_id => p_business_group_id
1728: END IF;
1729:
1730: EXCEPTION
1731: WHEN NO_DATA_FOUND THEN
1732: hr_utility.set_location('Exception occured :NO_DATA_FOUND ',160);
1733: l_min_eoy_bonus := 0;
1734: END;
1735:
1736: -- We found a CAG at the asg level . Now get the Min holiday
1744: ,p_row_value => l_cag_name
1745: ,p_effective_date => l_effective_date
1746: );
1747: --Get the age of the person
1748: hr_utility.set_location('l_begin_of_year_date'||l_begin_of_year_date,161);
1749: l_person_age := Get_age(p_assignment_id,l_effective_date,l_begin_of_year_date);
1750: hr_utility.set_location('l_person_age'||l_person_age,162);
1751: hr_utility.set_location('l_min_age_holiday_allow'||l_min_age_holiday_allow,163);
1752:
1746: );
1747: --Get the age of the person
1748: hr_utility.set_location('l_begin_of_year_date'||l_begin_of_year_date,161);
1749: l_person_age := Get_age(p_assignment_id,l_effective_date,l_begin_of_year_date);
1750: hr_utility.set_location('l_person_age'||l_person_age,162);
1751: hr_utility.set_location('l_min_age_holiday_allow'||l_min_age_holiday_allow,163);
1752:
1753: --Comapre it with min age defined at CAG level
1754: IF (l_person_age < to_number(l_min_age_holiday_allow) ) THEN
1747: --Get the age of the person
1748: hr_utility.set_location('l_begin_of_year_date'||l_begin_of_year_date,161);
1749: l_person_age := Get_age(p_assignment_id,l_effective_date,l_begin_of_year_date);
1750: hr_utility.set_location('l_person_age'||l_person_age,162);
1751: hr_utility.set_location('l_min_age_holiday_allow'||l_min_age_holiday_allow,163);
1752:
1753: --Comapre it with min age defined at CAG level
1754: IF (l_person_age < to_number(l_min_age_holiday_allow) ) THEN
1755: --Person is not eligible for Min holiday allowance
1765: ,p_col_name => 'PGGM Minimum Holiday Allowance'
1766: ,p_row_value => l_cag_name
1767: ,p_effective_date => l_effective_date
1768: );
1769: hr_utility.set_location('l_min_holiday_char'||l_min_holiday_char,165);
1770: --
1771: -- Calculate the min HA only if the override percentage is not 0
1772: --
1773: IF NVL(l_ignore_ha_cag,0) = 0 THEN
1778: END IF; -- End of person age check
1779:
1780: EXCEPTION
1781: WHEN NO_DATA_FOUND THEN
1782: hr_utility.set_location('Exception occured :NO_DATA_FOUND ',170);
1783: l_min_holiday_allow := 0;
1784: END;
1785:
1786: --no collective agreement defined for this assignment
1785:
1786: --no collective agreement defined for this assignment
1787: ELSE
1788: -- set Min EOY bonus and Min Holiday allowance to 0 .
1789: hr_utility.set_location('No CAG attached',160);
1790: l_min_eoy_bonus := 0;
1791: l_min_holiday_allow := 0;
1792: END IF;
1793:
1792: END IF;
1793:
1794: CLOSE c_cag_name;
1795:
1796: hr_utility.set_location('Min. EOY bonus : '||l_min_eoy_bonus,170);
1797: hr_utility.set_location('Min. Holiday allowance : '||l_min_holiday_allow,180);
1798:
1799: --now compare the calculated eoy bonus and the min. eoy bonus
1800: IF l_eoy_bonus < l_min_eoy_bonus THEN
1793:
1794: CLOSE c_cag_name;
1795:
1796: hr_utility.set_location('Min. EOY bonus : '||l_min_eoy_bonus,170);
1797: hr_utility.set_location('Min. Holiday allowance : '||l_min_holiday_allow,180);
1798:
1799: --now compare the calculated eoy bonus and the min. eoy bonus
1800: IF l_eoy_bonus < l_min_eoy_bonus THEN
1801: p_err_message1 :=p_err_message1|| 'Min. end of year bonus for the collective agreement'
1804: END IF;
1805:
1806: l_eoy_bonus := GREATEST(l_eoy_bonus,nvl(l_min_eoy_bonus,0));
1807:
1808: hr_utility.set_location('Final EOY Bonus value : '||l_eoy_bonus,190);
1809:
1810: --now compare the calculated holiday allowance and the min. holiday allowance
1811: IF l_holiday_allowance < l_min_holiday_allow THEN
1812: p_err_message2 :=p_err_message2|| 'Min. holiday allowance for the collective agreement'
1815: END IF;
1816:
1817: l_holiday_allowance := GREATEST(l_holiday_allowance,nvl(l_min_holiday_allow,0));
1818:
1819: hr_utility.set_location('Final Holiday Allowance value : '||l_holiday_allowance,200);
1820:
1821: p_salary_balance_value := (l_scale_salary
1822: +l_ft_rec_paymnt
1823: +l_pt_rec_paymnt
1826: +l_eoy_bonus;
1827:
1828: p_salary_balance_value := CEIL(p_salary_balance_value);
1829:
1830: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,210);
1831: --hr_utility.trace_off;
1832: RETURN l_ret_val;
1833:
1834: EXCEPTION
1827:
1828: p_salary_balance_value := CEIL(p_salary_balance_value);
1829:
1830: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,210);
1831: --hr_utility.trace_off;
1832: RETURN l_ret_val;
1833:
1834: EXCEPTION
1835: WHEN OTHERS THEN
1833:
1834: EXCEPTION
1835: WHEN OTHERS THEN
1836: p_salary_balance_value := 0;
1837: hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,220);
1838: p_err_message := 'Error occured while deriving the PGGM Pension Salary'
1839: ||' value : '||SQLERRM;
1840: RETURN 1;
1841:
1941: l_period_type VARCHAR2(150):='Calendar Month';
1942: l_num_of_days_in_period NUMBER:=30;
1943:
1944: BEGIN
1945: hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
1946:
1947: OPEN c_get_period_type(p_payroll_action_id);
1948: FETCH c_get_period_type INTO l_period_type;
1949: CLOSE c_get_period_type;
1979: OPEN c_get_st_date;
1980: FETCH c_get_st_date INTO l_eff_date;
1981: CLOSE c_get_st_date;
1982:
1983: hr_utility.set_location('Start date of asg for this period : '||l_eff_date,20);
1984:
1985: -- the date on which ptp needs to be derived is the greater of the asg start date
1986: -- and the period start date
1987:
1986: -- and the period start date
1987:
1988: l_eff_date := GREATEST(l_eff_date,p_period_start_date);
1989:
1990: hr_utility.set_location('date on which check is performed : '||l_eff_date,30);
1991:
1992: --Get number of payroll periods in a year
1993: OPEN c_get_num_periods_per_year(p_payroll_action_id);
1994: FETCH c_get_num_periods_per_year INTO l_num_periods;
2001: OPEN c_get_ptp(l_eff_date);
2002: FETCH c_get_ptp INTO l_ptp;
2003: CLOSE c_get_ptp;
2004:
2005: hr_utility.set_location('PTP on the start date : '||l_ptp,40);
2006:
2007: IF l_ptp < 100 THEN
2008: -- the employee is a part time employee
2009: l_emp_kind := 0;
2011: -- the employee is full time
2012: l_emp_kind := 1;
2013: END IF;
2014:
2015: hr_utility.set_location('Employee Kind : '||l_emp_kind,50);
2016:
2017: --calculate the number of days in this pay period
2018: l_pay_days := TRUNC(p_period_end_date) - TRUNC(p_period_start_date) + 1;
2019:
2033: ELSE
2034: l_end_date := c_asg_row.end_date;
2035: END IF;
2036:
2037: hr_utility.set_location('Start Date : '||l_start_date,60);
2038: hr_utility.set_location('End Date : '||l_end_date,70);
2039: hr_utility.set_location('Hours worked : '||c_asg_row.hours_worked,80);
2040: hr_utility.set_location('Total Hours : '||c_asg_row.total_hours,90);
2041: hr_utility.set_location('Frequency : '||c_asg_row.freq,100);
2034: l_end_date := c_asg_row.end_date;
2035: END IF;
2036:
2037: hr_utility.set_location('Start Date : '||l_start_date,60);
2038: hr_utility.set_location('End Date : '||l_end_date,70);
2039: hr_utility.set_location('Hours worked : '||c_asg_row.hours_worked,80);
2040: hr_utility.set_location('Total Hours : '||c_asg_row.total_hours,90);
2041: hr_utility.set_location('Frequency : '||c_asg_row.freq,100);
2042:
2035: END IF;
2036:
2037: hr_utility.set_location('Start Date : '||l_start_date,60);
2038: hr_utility.set_location('End Date : '||l_end_date,70);
2039: hr_utility.set_location('Hours worked : '||c_asg_row.hours_worked,80);
2040: hr_utility.set_location('Total Hours : '||c_asg_row.total_hours,90);
2041: hr_utility.set_location('Frequency : '||c_asg_row.freq,100);
2042:
2043: --calculate the hours per week based on the frequency
2036:
2037: hr_utility.set_location('Start Date : '||l_start_date,60);
2038: hr_utility.set_location('End Date : '||l_end_date,70);
2039: hr_utility.set_location('Hours worked : '||c_asg_row.hours_worked,80);
2040: hr_utility.set_location('Total Hours : '||c_asg_row.total_hours,90);
2041: hr_utility.set_location('Frequency : '||c_asg_row.freq,100);
2042:
2043: --calculate the hours per week based on the frequency
2044: IF c_asg_row.freq = 'D' THEN
2037: hr_utility.set_location('Start Date : '||l_start_date,60);
2038: hr_utility.set_location('End Date : '||l_end_date,70);
2039: hr_utility.set_location('Hours worked : '||c_asg_row.hours_worked,80);
2040: hr_utility.set_location('Total Hours : '||c_asg_row.total_hours,90);
2041: hr_utility.set_location('Frequency : '||c_asg_row.freq,100);
2042:
2043: --calculate the hours per week based on the frequency
2044: IF c_asg_row.freq = 'D' THEN
2045: l_hours_worked := c_asg_row.hours_worked * 5;
2066: --pay period,but this has to always be l_num_of_days_in_period
2067: l_days := l_days + (l_num_of_days_in_period - l_tot_days);
2068: END IF;
2069:
2070: hr_utility.set_location('l_days : '||l_days,110);
2071:
2072: hours_worked := hours_worked +
2073: l_hours_worked * l_days/l_num_of_days_in_period;
2074:
2080: -- above * 52/12
2081: hours_worked := hours_worked * 52/l_num_periods;
2082: total_hours := total_hours * 52/l_num_periods;
2083:
2084: hr_utility.set_location('Avg. Hours worked : '||hours_worked,120);
2085: hr_utility.set_location('Avg. Total Hours : '||total_hours,130);
2086:
2087: --
2088: -- check if an override has been entered, if so then the part time
2081: hours_worked := hours_worked * 52/l_num_periods;
2082: total_hours := total_hours * 52/l_num_periods;
2083:
2084: hr_utility.set_location('Avg. Hours worked : '||hours_worked,120);
2085: hr_utility.set_location('Avg. Total Hours : '||total_hours,130);
2086:
2087: --
2088: -- check if an override has been entered, if so then the part time
2089: -- percentage is the same as the value entered in the override
2094:
2095: IF p_override_value <> -99 THEN
2096: --override has been entered
2097: l_ptp := p_override_value;
2098: hr_utility.set_location('Override exists : '||l_ptp,140);
2099: --check if the employee is also on parental leave
2100: IF p_parental_leave = 'Y' THEN
2101: --we also need to add any increase in ptp due to extra hours
2102: hr_utility.set_location('parental leave exists',150);
2098: hr_utility.set_location('Override exists : '||l_ptp,140);
2099: --check if the employee is also on parental leave
2100: IF p_parental_leave = 'Y' THEN
2101: --we also need to add any increase in ptp due to extra hours
2102: hr_utility.set_location('parental leave exists',150);
2103: IF total_hours > 0 THEN
2104: --only if the employee is part time,extra hours can be considered
2105: IF l_emp_kind = 0 THEN
2106: l_ptp := l_ptp + (nvl(p_extra_hours,0)/total_hours) * 100;
2109: END IF;
2110:
2111: ELSE
2112: --no override exists
2113: hr_utility.set_location('No override exists',160);
2114: --final ptp value is calculated value + increase due to extra hours
2115: IF total_hours > 0 THEN
2116: --only if emp is part time, then consider extra hours
2117: IF l_emp_kind = 0 THEN
2125: END IF;
2126:
2127: l_ptp := nvl(l_ptp,0);
2128:
2129: hr_utility.set_location('Calculated PTP : '||l_ptp,170);
2130: /*
2131: PGGM 2006 Legislative change
2132: No max limit for Part time percentage
2133: --
2140:
2141: l_ptp := LEAST(l_ptp,125);
2142: */
2143:
2144: hr_utility.set_location('Final ptp value : '||l_ptp,180);
2145:
2146: p_part_time_percentage := round(nvl(l_ptp,0),2);
2147: p_hours_worked := round(nvl(hours_worked,0),2);
2148: p_total_hours := round(nvl(total_hours,0),2);
2146: p_part_time_percentage := round(nvl(l_ptp,0),2);
2147: p_hours_worked := round(nvl(hours_worked,0),2);
2148: p_total_hours := round(nvl(total_hours,0),2);
2149:
2150: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,190);
2151:
2152: RETURN l_ret_value;
2153:
2154: EXCEPTION
2152: RETURN l_ret_value;
2153:
2154: EXCEPTION
2155: WHEN OTHERS THEN
2156: hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,200);
2157: p_part_time_percentage := 0;
2158: p_hours_worked := 0;
2159: p_total_hours := 0;
2160: p_err_message := 'Error occured while deriving part time percentage : '||SQLERRM;
2186: l_proc_name VARCHAR2(30) := 'GET_INCI_WKR_CODE';
2187: l_inci_code VARCHAR2(1):='N';
2188: l_ret_value NUMBER:=0;
2189: BEGIN
2190: hr_utility.set_location('Entering : '||g_pkg_name||l_proc_name,10);
2191:
2192: OPEN csr_get_inci_wkr_code(p_assignment_id,p_date_earned);
2193: FETCH csr_get_inci_wkr_code INTO l_inci_code;
2194:
2198: p_result_value:='N';
2199: END IF;
2200: CLOSE csr_get_inci_wkr_code;
2201:
2202: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,190);
2203: RETURN l_ret_value;
2204: EXCEPTION
2205: WHEN OTHERS THEN
2206: hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,200);
2202: hr_utility.set_location('Leaving : '||g_pkg_name||l_proc_name,190);
2203: RETURN l_ret_value;
2204: EXCEPTION
2205: WHEN OTHERS THEN
2206: hr_utility.set_location('Exception occured in : '||g_pkg_name||l_proc_name,200);
2207: p_err_message := 'Error occured while deriving incidental worker code : '||SQLERRM;
2208: RETURN 1;
2209: END GET_INCI_WKR_CODE;
2210: