[Home] [Help]
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);
25: AND TPTYPE.period_type = TPERIOD.period_type;
26:
27: --9975522
28: CURSOR c_get_global_value_custom(c_global_name IN VARCHAR2, c_date_earned IN DATE, c_business_group_id IN NUMBER) IS
29: SELECT nvl(fnd_number.canonical_to_number(global_value),0)
30: FROM ff_globals_f
31: WHERE global_name = c_global_name
32: AND legislation_code IS NULL
33: AND business_group_id = c_business_group_id
168: --
169: -- Cursor to get the percentage value from the ORG EIT
170: --
171: CURSOR c_get_contrib_frm_org(c_org_id IN NUMBER) IS
172: SELECT decode(p_ee_or_total,0,nvl(fnd_number.canonical_to_number(org_information4),0)
173: ,nvl(fnd_number.canonical_to_number(org_information5),0))
174: FROM hr_organization_information
175: WHERE org_information_context = 'PQP_NL_PGGM_PT'
176: AND organization_id = c_org_id
169: -- Cursor to get the percentage value from the ORG EIT
170: --
171: CURSOR c_get_contrib_frm_org(c_org_id IN NUMBER) IS
172: SELECT decode(p_ee_or_total,0,nvl(fnd_number.canonical_to_number(org_information4),0)
173: ,nvl(fnd_number.canonical_to_number(org_information5),0))
174: FROM hr_organization_information
175: WHERE org_information_context = 'PQP_NL_PGGM_PT'
176: AND organization_id = c_org_id
177: AND p_date_earned BETWEEN fnd_date.canonical_to_date(org_information1)
175: WHERE org_information_context = 'PQP_NL_PGGM_PT'
176: AND organization_id = c_org_id
177: AND p_date_earned BETWEEN fnd_date.canonical_to_date(org_information1)
178: AND nvl(fnd_date.canonical_to_date(org_information2),hr_api.g_eot)
179: AND fnd_number.canonical_to_number(org_information3) = p_pension_type_id;
180:
181: --
182: -- Cursor to get the percentage value from the pension types table
183: --
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;
340: --Cursor to fetch the maximum age for which thresholds are defined
341: --all ages above this value have the same threshold
342: --
343: CURSOR c_get_max_age IS
344: SELECT max(fnd_number.canonical_to_number(row_low_range_or_name))
345: FROM pay_user_rows_f
346: WHERE p_date_earned BETWEEN effective_start_date
347: AND effective_end_date
348: AND user_table_id = (SELECT user_table_id
352: );
353:
354: --9975522
355: CURSOR c_get_max_age_custom IS
356: SELECT max(fnd_number.canonical_to_number(row_low_range_or_name))
357: FROM pay_user_rows_f
358: WHERE p_date_earned BETWEEN effective_start_date
359: AND effective_end_date
360: AND user_table_id = (SELECT user_table_id
391: ,p_effective_date => p_date_earned
392: );
393:
394: l_value_found := 1;
395: l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
396: hr_utility.set_location('Threshold percentage derived as : '||l_threshold_percentage,300);
397:
398: EXCEPTION
399: WHEN NO_DATA_FOUND THEN
431: ,p_row_value => l_max_age
432: ,p_effective_date => p_date_earned
433: );
434: l_value_found := 1;
435: l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
436: hr_utility.set_location('value of max age used : '||l_threshold_percentage,600);
437:
438: EXCEPTION
439: WHEN NO_DATA_FOUND THEN
477: ,p_effective_date => p_date_earned
478: );
479:
480: l_value_found := 1;
481: l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
482: hr_utility.set_location('Threshold percentage derived as : '||l_threshold_percentage,30);
483:
484: EXCEPTION
485: WHEN NO_DATA_FOUND THEN
516: ,p_col_name => 'Percentage Of Maximum Threshold'
517: ,p_row_value => l_max_age
518: ,p_effective_date => p_date_earned
519: );
520: l_threshold_percentage := nvl(fnd_number.canonical_to_number(l_return_value),0);
521: hr_utility.set_location('value of max age used : '||l_threshold_percentage,60);
522:
523: EXCEPTION
524: WHEN NO_DATA_FOUND THEN
1117: --holiday allowance /eoy bonus percentages have been found at this org
1118: hr_utility.set_location('Percentages found at org : '||l_org_id,40);
1119: CLOSE c_get_general_info;
1120:
1121: p_value := fnd_number.canonical_to_number(l_value);
1122:
1123: hr_utility.set_location('Percentage is : '||p_value,50);
1124: --we no longer need to continue up the hierarchy
1125: l_loop_again := 0;
1209: --Cursor to fetch the part time percentage from the
1210: --assignment standard conditions for a given effective date
1211: --
1212: CURSOR c_pt_cur (c_effective_date IN DATE) IS
1213: SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) pt_perc
1214: FROM per_assignments_f asg
1215: ,hr_soft_coding_keyflex target
1216: WHERE asg.assignment_id = p_assignment_id
1217: AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1249: --
1250: -- Cursor to get the holiday allowance global
1251: --
1252: CURSOR c_global_cur(c_global_name IN VARCHAR2) IS
1253: SELECT fnd_number.canonical_to_number(global_value)
1254: FROM ff_globals_f
1255: WHERE global_name = c_global_name
1256: AND trunc (p_date_earned) BETWEEN effective_start_date
1257: AND effective_end_date;
1721: --
1722: -- Calculate the min EOY bonus only if the override percentage is not 0
1723: --
1724: IF NVL(l_ignore_eoy_cag,0) = 0 THEN
1725: l_min_eoy_bonus := fnd_number.canonical_to_number(NVL(l_min_eoy_bonus_char,'0'));
1726: ELSIF NVL(l_ignore_eoy_cag,0) = 1 THEN
1727: l_min_eoy_bonus := 0;
1728: END IF;
1729:
1770: --
1771: -- Calculate the min HA only if the override percentage is not 0
1772: --
1773: IF NVL(l_ignore_ha_cag,0) = 0 THEN
1774: l_min_holiday_allow := fnd_number.canonical_to_number(NVL(l_min_holiday_char,'0'));
1775: ELSIF NVL(l_ignore_ha_cag,0) = 1 THEN
1776: l_min_holiday_allow := 0;
1777: END IF;
1778: END IF; -- End of person age check
1875: --Cursor to fetch the part time percentage from the
1876: --assignment standard conditions for a given effective date
1877: --
1878: CURSOR c_get_ptp(c_effective_date IN DATE) IS
1879: SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'0')) pt_perc
1880: FROM per_assignments_f asg
1881: ,hr_soft_coding_keyflex target
1882: WHERE asg.assignment_id = p_assignment_id
1883: AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1891: -- the calendar day average value of all these changes
1892: -- is taken as the average part time percentage of this period
1893: --
1894: CURSOR c_get_asg_rows IS
1895: SELECT fnd_number.canonical_to_number(NVL(target.segment28,'0')) hours_worked
1896: ,nvl(asg.normal_hours,0) total_hours
1897: ,nvl(asg.frequency,'W') freq
1898: ,asg.effective_start_date start_date
1899: ,asg.effective_end_date end_date