[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);
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
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)
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: --
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;
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
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
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
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;
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
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;
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:
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
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
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