[Home] [Help]
225:
226: g_ptu_process BOOLEAN; -- Bug:9753792
227:
228: /****************************************************************************
229: Name : HR_UTILITY_TRACE
230: Description : This function prints debug messages during diagnostics mode.
231: *****************************************************************************/
232:
233: PROCEDURE HR_UTILITY_TRACE(trc_data varchar2) IS
229: Name : HR_UTILITY_TRACE
230: Description : This function prints debug messages during diagnostics mode.
231: *****************************************************************************/
232:
233: PROCEDURE HR_UTILITY_TRACE(trc_data varchar2) IS
234: BEGIN
235: IF g_debug THEN
236: hr_utility.trace(trc_data);
237: END IF;
232:
233: PROCEDURE HR_UTILITY_TRACE(trc_data varchar2) IS
234: BEGIN
235: IF g_debug THEN
236: hr_utility.trace(trc_data);
237: END IF;
238: END HR_UTILITY_TRACE;
239:
240:
234: BEGIN
235: IF g_debug THEN
236: hr_utility.trace(trc_data);
237: END IF;
238: END HR_UTILITY_TRACE;
239:
240:
241: /****************************************************************************
242: Name : GET_DEF_BAL_ID
250: l_defined_balance_id NUMBER;
251: l_proc_name VARCHAR2(100);
252: BEGIN
253: l_proc_name := g_proc_name ||'GET_DEF_BAL_ID';
254: hr_utility_trace('Entering '||l_proc_name);
255: SELECT creator_id
256: INTO l_defined_balance_id
257: FROM ff_user_entities
258: WHERE user_entity_name = p_entity_name
347: l_proc_name VARCHAR2(100);
348:
349: BEGIN
350: l_proc_name := g_proc_name ||'GET_RANGE_BASIS_VALUE';
351: hr_utility_trace('Entering '||l_proc_name);
352:
353: IF p_range_basis = 'SAL' THEN
354:
355: RETURN (p_daily_salary);
383: END IF;
384:
385: l_gre_id := hr_mx_utility.get_gre_from_scl (l_scl_id);
386:
387: hr_utility_trace('GRE obtained from SCL = "'||l_gre_id||'"');
388:
389: IF l_gre_id is null THEN
390: l_gre_id := hr_mx_utility.get_gre_from_location(
391: l_location_id,
392: l_business_group_id, -- Bug 4129001
393: l_effective_date,
394: l_is_ambiguous,
395: l_missing_gre);
396: hr_utility_trace('GRE obtained from location = "'||l_gre_id||'"');
397: END IF;
398: /* Obtain (General) Minimum Wage */
399: OPEN csr_get_min_wage (l_gre_id, l_effective_date);
400: FETCH csr_get_min_wage INTO l_min_wage;
398: /* Obtain (General) Minimum Wage */
399: OPEN csr_get_min_wage (l_gre_id, l_effective_date);
400: FETCH csr_get_min_wage INTO l_min_wage;
401: CLOSE csr_get_min_wage;
402: hr_utility_trace('(General) Minimum wage = '||l_min_wage);
403:
404: hr_utility_trace('Leaving '||l_proc_name);
405: RETURN (l_min_wage);
406:
400: FETCH csr_get_min_wage INTO l_min_wage;
401: CLOSE csr_get_min_wage;
402: hr_utility_trace('(General) Minimum wage = '||l_min_wage);
403:
404: hr_utility_trace('Leaving '||l_proc_name);
405: RETURN (l_min_wage);
406:
407: END GET_RANGE_BASIS_VALUE;
408:
480:
481: BEGIN
482:
483: l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
484: hr_utility_trace('Entering '||l_proc_name);
485:
486: l_counter := 0;
487: l_return_value := 0;
488:
495: FETCH get_ele_class INTO l_classification_id,
496: l_classification_name;
497: EXIT WHEN get_ele_class%NOTFOUND;
498:
499: hr_utility_trace('Element classification name is '
500: ||l_classification_name);
501:
502: IF l_classification_name LIKE '%:Subject to ISR' THEN
503: RETURN (P_EARNINGS_AMT);
645:
646: BEGIN
647:
648: l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
649: hr_utility_trace('Entering '||l_proc_name);
650:
651: l_counter := 0;
652: l_return_value := 0;
653:
660: FETCH get_ele_class INTO l_classification_id,
661: l_classification_name;
662: EXIT WHEN get_ele_class%NOTFOUND;
663:
664: hr_utility_trace('Element classification name is '
665: ||l_classification_name);
666:
667: IF l_classification_name LIKE '%:Subject to ISR' THEN
668: RETURN (P_EARNINGS_AMT);
810:
811: BEGIN
812:
813: l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
814: hr_utility_trace('Entering '||l_proc_name);
815:
816: l_counter := 0;
817: l_return_value := 0;
818:
825: FETCH get_ele_class INTO l_classification_id,
826: l_classification_name;
827: EXIT WHEN get_ele_class%NOTFOUND;
828:
829: hr_utility_trace('Element classification name is '
830: ||l_classification_name);
831:
832: IF l_classification_name LIKE '%:Subject to ISR' THEN
833: RETURN (P_EARNINGS_AMT);
1043:
1044: BEGIN
1045:
1046: l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
1047: hr_utility_trace('Entering '||l_proc_name);
1048:
1049: hr_utility_trace('Parameters ....');
1050: hr_utility_trace('P_CTX_EFFECTIVE_DATE = '|| P_CTX_EFFECTIVE_DATE);
1051: hr_utility_trace('P_CTX_ASSIGNMENT_ACTION_ID = '|| P_CTX_ASSIGNMENT_ACTION_ID);
1045:
1046: l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
1047: hr_utility_trace('Entering '||l_proc_name);
1048:
1049: hr_utility_trace('Parameters ....');
1050: hr_utility_trace('P_CTX_EFFECTIVE_DATE = '|| P_CTX_EFFECTIVE_DATE);
1051: hr_utility_trace('P_CTX_ASSIGNMENT_ACTION_ID = '|| P_CTX_ASSIGNMENT_ACTION_ID);
1052: hr_utility_trace('P_CTX_BUSINESS_GROUP_ID = '|| P_CTX_BUSINESS_GROUP_ID);
1053: hr_utility_trace('P_CTX_JURISDICTION_CODE = '|| P_CTX_JURISDICTION_CODE);
1046: l_proc_name := g_proc_name ||'GET_PARTIAL_SUBJ_EARNINGS';
1047: hr_utility_trace('Entering '||l_proc_name);
1048:
1049: hr_utility_trace('Parameters ....');
1050: hr_utility_trace('P_CTX_EFFECTIVE_DATE = '|| P_CTX_EFFECTIVE_DATE);
1051: hr_utility_trace('P_CTX_ASSIGNMENT_ACTION_ID = '|| P_CTX_ASSIGNMENT_ACTION_ID);
1052: hr_utility_trace('P_CTX_BUSINESS_GROUP_ID = '|| P_CTX_BUSINESS_GROUP_ID);
1053: hr_utility_trace('P_CTX_JURISDICTION_CODE = '|| P_CTX_JURISDICTION_CODE);
1054: hr_utility_trace('P_CTX_ELEMENT_TYPE_ID = '|| P_CTX_ELEMENT_TYPE_ID);
1047: hr_utility_trace('Entering '||l_proc_name);
1048:
1049: hr_utility_trace('Parameters ....');
1050: hr_utility_trace('P_CTX_EFFECTIVE_DATE = '|| P_CTX_EFFECTIVE_DATE);
1051: hr_utility_trace('P_CTX_ASSIGNMENT_ACTION_ID = '|| P_CTX_ASSIGNMENT_ACTION_ID);
1052: hr_utility_trace('P_CTX_BUSINESS_GROUP_ID = '|| P_CTX_BUSINESS_GROUP_ID);
1053: hr_utility_trace('P_CTX_JURISDICTION_CODE = '|| P_CTX_JURISDICTION_CODE);
1054: hr_utility_trace('P_CTX_ELEMENT_TYPE_ID = '|| P_CTX_ELEMENT_TYPE_ID);
1055: hr_utility_trace('P_TAX_TYPE = '|| P_TAX_TYPE);
1048:
1049: hr_utility_trace('Parameters ....');
1050: hr_utility_trace('P_CTX_EFFECTIVE_DATE = '|| P_CTX_EFFECTIVE_DATE);
1051: hr_utility_trace('P_CTX_ASSIGNMENT_ACTION_ID = '|| P_CTX_ASSIGNMENT_ACTION_ID);
1052: hr_utility_trace('P_CTX_BUSINESS_GROUP_ID = '|| P_CTX_BUSINESS_GROUP_ID);
1053: hr_utility_trace('P_CTX_JURISDICTION_CODE = '|| P_CTX_JURISDICTION_CODE);
1054: hr_utility_trace('P_CTX_ELEMENT_TYPE_ID = '|| P_CTX_ELEMENT_TYPE_ID);
1055: hr_utility_trace('P_TAX_TYPE = '|| P_TAX_TYPE);
1056: hr_utility_trace('P_EARNINGS_AMT = '|| P_EARNINGS_AMT);
1049: hr_utility_trace('Parameters ....');
1050: hr_utility_trace('P_CTX_EFFECTIVE_DATE = '|| P_CTX_EFFECTIVE_DATE);
1051: hr_utility_trace('P_CTX_ASSIGNMENT_ACTION_ID = '|| P_CTX_ASSIGNMENT_ACTION_ID);
1052: hr_utility_trace('P_CTX_BUSINESS_GROUP_ID = '|| P_CTX_BUSINESS_GROUP_ID);
1053: hr_utility_trace('P_CTX_JURISDICTION_CODE = '|| P_CTX_JURISDICTION_CODE);
1054: hr_utility_trace('P_CTX_ELEMENT_TYPE_ID = '|| P_CTX_ELEMENT_TYPE_ID);
1055: hr_utility_trace('P_TAX_TYPE = '|| P_TAX_TYPE);
1056: hr_utility_trace('P_EARNINGS_AMT = '|| P_EARNINGS_AMT);
1057: hr_utility_trace('P_YTD_EARNINGS_AMT = '|| P_YTD_EARNINGS_AMT);
1050: hr_utility_trace('P_CTX_EFFECTIVE_DATE = '|| P_CTX_EFFECTIVE_DATE);
1051: hr_utility_trace('P_CTX_ASSIGNMENT_ACTION_ID = '|| P_CTX_ASSIGNMENT_ACTION_ID);
1052: hr_utility_trace('P_CTX_BUSINESS_GROUP_ID = '|| P_CTX_BUSINESS_GROUP_ID);
1053: hr_utility_trace('P_CTX_JURISDICTION_CODE = '|| P_CTX_JURISDICTION_CODE);
1054: hr_utility_trace('P_CTX_ELEMENT_TYPE_ID = '|| P_CTX_ELEMENT_TYPE_ID);
1055: hr_utility_trace('P_TAX_TYPE = '|| P_TAX_TYPE);
1056: hr_utility_trace('P_EARNINGS_AMT = '|| P_EARNINGS_AMT);
1057: hr_utility_trace('P_YTD_EARNINGS_AMT = '|| P_YTD_EARNINGS_AMT);
1058: hr_utility_trace('P_PTD_EARNINGS_AMT = '|| P_PTD_EARNINGS_AMT);
1051: hr_utility_trace('P_CTX_ASSIGNMENT_ACTION_ID = '|| P_CTX_ASSIGNMENT_ACTION_ID);
1052: hr_utility_trace('P_CTX_BUSINESS_GROUP_ID = '|| P_CTX_BUSINESS_GROUP_ID);
1053: hr_utility_trace('P_CTX_JURISDICTION_CODE = '|| P_CTX_JURISDICTION_CODE);
1054: hr_utility_trace('P_CTX_ELEMENT_TYPE_ID = '|| P_CTX_ELEMENT_TYPE_ID);
1055: hr_utility_trace('P_TAX_TYPE = '|| P_TAX_TYPE);
1056: hr_utility_trace('P_EARNINGS_AMT = '|| P_EARNINGS_AMT);
1057: hr_utility_trace('P_YTD_EARNINGS_AMT = '|| P_YTD_EARNINGS_AMT);
1058: hr_utility_trace('P_PTD_EARNINGS_AMT = '|| P_PTD_EARNINGS_AMT);
1059: hr_utility_trace('P_GROSS_EARNINGS = '|| P_GROSS_EARNINGS);
1052: hr_utility_trace('P_CTX_BUSINESS_GROUP_ID = '|| P_CTX_BUSINESS_GROUP_ID);
1053: hr_utility_trace('P_CTX_JURISDICTION_CODE = '|| P_CTX_JURISDICTION_CODE);
1054: hr_utility_trace('P_CTX_ELEMENT_TYPE_ID = '|| P_CTX_ELEMENT_TYPE_ID);
1055: hr_utility_trace('P_TAX_TYPE = '|| P_TAX_TYPE);
1056: hr_utility_trace('P_EARNINGS_AMT = '|| P_EARNINGS_AMT);
1057: hr_utility_trace('P_YTD_EARNINGS_AMT = '|| P_YTD_EARNINGS_AMT);
1058: hr_utility_trace('P_PTD_EARNINGS_AMT = '|| P_PTD_EARNINGS_AMT);
1059: hr_utility_trace('P_GROSS_EARNINGS = '|| P_GROSS_EARNINGS);
1060: hr_utility_trace('P_YTD_GROSS_EARNINGS = '|| P_YTD_GROSS_EARNINGS);
1053: hr_utility_trace('P_CTX_JURISDICTION_CODE = '|| P_CTX_JURISDICTION_CODE);
1054: hr_utility_trace('P_CTX_ELEMENT_TYPE_ID = '|| P_CTX_ELEMENT_TYPE_ID);
1055: hr_utility_trace('P_TAX_TYPE = '|| P_TAX_TYPE);
1056: hr_utility_trace('P_EARNINGS_AMT = '|| P_EARNINGS_AMT);
1057: hr_utility_trace('P_YTD_EARNINGS_AMT = '|| P_YTD_EARNINGS_AMT);
1058: hr_utility_trace('P_PTD_EARNINGS_AMT = '|| P_PTD_EARNINGS_AMT);
1059: hr_utility_trace('P_GROSS_EARNINGS = '|| P_GROSS_EARNINGS);
1060: hr_utility_trace('P_YTD_GROSS_EARNINGS = '|| P_YTD_GROSS_EARNINGS);
1061: hr_utility_trace('P_DAILY_SALARY = '|| P_DAILY_SALARY);
1054: hr_utility_trace('P_CTX_ELEMENT_TYPE_ID = '|| P_CTX_ELEMENT_TYPE_ID);
1055: hr_utility_trace('P_TAX_TYPE = '|| P_TAX_TYPE);
1056: hr_utility_trace('P_EARNINGS_AMT = '|| P_EARNINGS_AMT);
1057: hr_utility_trace('P_YTD_EARNINGS_AMT = '|| P_YTD_EARNINGS_AMT);
1058: hr_utility_trace('P_PTD_EARNINGS_AMT = '|| P_PTD_EARNINGS_AMT);
1059: hr_utility_trace('P_GROSS_EARNINGS = '|| P_GROSS_EARNINGS);
1060: hr_utility_trace('P_YTD_GROSS_EARNINGS = '|| P_YTD_GROSS_EARNINGS);
1061: hr_utility_trace('P_DAILY_SALARY = '|| P_DAILY_SALARY);
1062: hr_utility_trace('P_CLASSIFICATION_NAME = '|| P_CLASSIFICATION_NAME);
1055: hr_utility_trace('P_TAX_TYPE = '|| P_TAX_TYPE);
1056: hr_utility_trace('P_EARNINGS_AMT = '|| P_EARNINGS_AMT);
1057: hr_utility_trace('P_YTD_EARNINGS_AMT = '|| P_YTD_EARNINGS_AMT);
1058: hr_utility_trace('P_PTD_EARNINGS_AMT = '|| P_PTD_EARNINGS_AMT);
1059: hr_utility_trace('P_GROSS_EARNINGS = '|| P_GROSS_EARNINGS);
1060: hr_utility_trace('P_YTD_GROSS_EARNINGS = '|| P_YTD_GROSS_EARNINGS);
1061: hr_utility_trace('P_DAILY_SALARY = '|| P_DAILY_SALARY);
1062: hr_utility_trace('P_CLASSIFICATION_NAME = '|| P_CLASSIFICATION_NAME);
1063:
1056: hr_utility_trace('P_EARNINGS_AMT = '|| P_EARNINGS_AMT);
1057: hr_utility_trace('P_YTD_EARNINGS_AMT = '|| P_YTD_EARNINGS_AMT);
1058: hr_utility_trace('P_PTD_EARNINGS_AMT = '|| P_PTD_EARNINGS_AMT);
1059: hr_utility_trace('P_GROSS_EARNINGS = '|| P_GROSS_EARNINGS);
1060: hr_utility_trace('P_YTD_GROSS_EARNINGS = '|| P_YTD_GROSS_EARNINGS);
1061: hr_utility_trace('P_DAILY_SALARY = '|| P_DAILY_SALARY);
1062: hr_utility_trace('P_CLASSIFICATION_NAME = '|| P_CLASSIFICATION_NAME);
1063:
1064:
1057: hr_utility_trace('P_YTD_EARNINGS_AMT = '|| P_YTD_EARNINGS_AMT);
1058: hr_utility_trace('P_PTD_EARNINGS_AMT = '|| P_PTD_EARNINGS_AMT);
1059: hr_utility_trace('P_GROSS_EARNINGS = '|| P_GROSS_EARNINGS);
1060: hr_utility_trace('P_YTD_GROSS_EARNINGS = '|| P_YTD_GROSS_EARNINGS);
1061: hr_utility_trace('P_DAILY_SALARY = '|| P_DAILY_SALARY);
1062: hr_utility_trace('P_CLASSIFICATION_NAME = '|| P_CLASSIFICATION_NAME);
1063:
1064:
1065: l_exempt_amount := 0;
1058: hr_utility_trace('P_PTD_EARNINGS_AMT = '|| P_PTD_EARNINGS_AMT);
1059: hr_utility_trace('P_GROSS_EARNINGS = '|| P_GROSS_EARNINGS);
1060: hr_utility_trace('P_YTD_GROSS_EARNINGS = '|| P_YTD_GROSS_EARNINGS);
1061: hr_utility_trace('P_DAILY_SALARY = '|| P_DAILY_SALARY);
1062: hr_utility_trace('P_CLASSIFICATION_NAME = '|| P_CLASSIFICATION_NAME);
1063:
1064:
1065: l_exempt_amount := 0;
1066: l_counter := 0;
1089: l_high_range_basis;
1090: EXIT WHEN get_exempt_info%NOTFOUND;
1091: l_counter := l_counter + 1;
1092: IF l_counter > 1 THEN
1093: hr_utility.set_message(801, 'PAY_MX_MULTI_TAX_SEC_CLASS');
1094: hr_utility.set_message_token(801,
1095: 'ELEMENT_TYPE_ID',
1096: p_ctx_element_type_id);
1097: hr_utility.raise_error;
1090: EXIT WHEN get_exempt_info%NOTFOUND;
1091: l_counter := l_counter + 1;
1092: IF l_counter > 1 THEN
1093: hr_utility.set_message(801, 'PAY_MX_MULTI_TAX_SEC_CLASS');
1094: hr_utility.set_message_token(801,
1095: 'ELEMENT_TYPE_ID',
1096: p_ctx_element_type_id);
1097: hr_utility.raise_error;
1098: END IF;
1093: hr_utility.set_message(801, 'PAY_MX_MULTI_TAX_SEC_CLASS');
1094: hr_utility.set_message_token(801,
1095: 'ELEMENT_TYPE_ID',
1096: p_ctx_element_type_id);
1097: hr_utility.raise_error;
1098: END IF;
1099:
1100: hr_utility_trace('l_calc_rule = '|| l_calc_rule);
1101: hr_utility_trace('l_low_exempt_factor = '|| l_low_exempt_factor);
1096: p_ctx_element_type_id);
1097: hr_utility.raise_error;
1098: END IF;
1099:
1100: hr_utility_trace('l_calc_rule = '|| l_calc_rule);
1101: hr_utility_trace('l_low_exempt_factor = '|| l_low_exempt_factor);
1102: hr_utility_trace('l_low_range_factor = '|| l_low_range_factor);
1103: hr_utility_trace('l_low_range_basis = '|| l_low_range_basis);
1104: hr_utility_trace('l_high_exempt_factor = '|| l_high_exempt_factor);
1097: hr_utility.raise_error;
1098: END IF;
1099:
1100: hr_utility_trace('l_calc_rule = '|| l_calc_rule);
1101: hr_utility_trace('l_low_exempt_factor = '|| l_low_exempt_factor);
1102: hr_utility_trace('l_low_range_factor = '|| l_low_range_factor);
1103: hr_utility_trace('l_low_range_basis = '|| l_low_range_basis);
1104: hr_utility_trace('l_high_exempt_factor = '|| l_high_exempt_factor);
1105: hr_utility_trace('l_high_range_factor = '|| l_high_range_factor);
1098: END IF;
1099:
1100: hr_utility_trace('l_calc_rule = '|| l_calc_rule);
1101: hr_utility_trace('l_low_exempt_factor = '|| l_low_exempt_factor);
1102: hr_utility_trace('l_low_range_factor = '|| l_low_range_factor);
1103: hr_utility_trace('l_low_range_basis = '|| l_low_range_basis);
1104: hr_utility_trace('l_high_exempt_factor = '|| l_high_exempt_factor);
1105: hr_utility_trace('l_high_range_factor = '|| l_high_range_factor);
1106: hr_utility_trace('l_high_range_basis = '|| l_high_range_basis);
1099:
1100: hr_utility_trace('l_calc_rule = '|| l_calc_rule);
1101: hr_utility_trace('l_low_exempt_factor = '|| l_low_exempt_factor);
1102: hr_utility_trace('l_low_range_factor = '|| l_low_range_factor);
1103: hr_utility_trace('l_low_range_basis = '|| l_low_range_basis);
1104: hr_utility_trace('l_high_exempt_factor = '|| l_high_exempt_factor);
1105: hr_utility_trace('l_high_range_factor = '|| l_high_range_factor);
1106: hr_utility_trace('l_high_range_basis = '|| l_high_range_basis);
1107:
1100: hr_utility_trace('l_calc_rule = '|| l_calc_rule);
1101: hr_utility_trace('l_low_exempt_factor = '|| l_low_exempt_factor);
1102: hr_utility_trace('l_low_range_factor = '|| l_low_range_factor);
1103: hr_utility_trace('l_low_range_basis = '|| l_low_range_basis);
1104: hr_utility_trace('l_high_exempt_factor = '|| l_high_exempt_factor);
1105: hr_utility_trace('l_high_range_factor = '|| l_high_range_factor);
1106: hr_utility_trace('l_high_range_basis = '|| l_high_range_basis);
1107:
1108: l_index := l_classification_id;
1101: hr_utility_trace('l_low_exempt_factor = '|| l_low_exempt_factor);
1102: hr_utility_trace('l_low_range_factor = '|| l_low_range_factor);
1103: hr_utility_trace('l_low_range_basis = '|| l_low_range_basis);
1104: hr_utility_trace('l_high_exempt_factor = '|| l_high_exempt_factor);
1105: hr_utility_trace('l_high_range_factor = '|| l_high_range_factor);
1106: hr_utility_trace('l_high_range_basis = '|| l_high_range_basis);
1107:
1108: l_index := l_classification_id;
1109:
1102: hr_utility_trace('l_low_range_factor = '|| l_low_range_factor);
1103: hr_utility_trace('l_low_range_basis = '|| l_low_range_basis);
1104: hr_utility_trace('l_high_exempt_factor = '|| l_high_exempt_factor);
1105: hr_utility_trace('l_high_range_factor = '|| l_high_range_factor);
1106: hr_utility_trace('l_high_range_basis = '|| l_high_range_basis);
1107:
1108: l_index := l_classification_id;
1109:
1110: IF p_classification_name IN
1168: CLOSE get_days_per_period;
1169:
1170: END IF;
1171:
1172: hr_utility_trace('Days per period of payroll = '||l_days);
1173:
1174: l_earnings_amt := g_isr_balances(l_index).earnings_amt;
1175:
1176: OPEN get_person_id( ln_assignment_id, p_ctx_effective_date);
1378:
1379: g_isr_balances(l_index).exempt_amt :=
1380: nvl(g_isr_balances(l_index).exempt_amt, 0) + l_exempt_amount;
1381:
1382: hr_utility_trace('Subject amount for ' ||
1383: p_classification_name || ' = ' ||
1384: to_char(l_sf_earnings - l_exempt_amount)
1385: );
1386:
1383: p_classification_name || ' = ' ||
1384: to_char(l_sf_earnings - l_exempt_amount)
1385: );
1386:
1387: hr_utility_trace('Leaving '||l_proc_name);
1388:
1389: -- Return the subject amount.
1390: RETURN (l_sf_earnings - l_exempt_amount);
1391:
1403: NVL(g_isr_balances(l_index).exempt_amt, 0) + l_exempt_amount;
1404:
1405: END IF;
1406: --
1407: hr_utility_trace('Subject amount for ' ||
1408: p_classification_name || ' = ' ||
1409: TO_CHAR(p_earnings_amt - l_exempt_amount)
1410: );
1411:
1408: p_classification_name || ' = ' ||
1409: TO_CHAR(p_earnings_amt - l_exempt_amount)
1410: );
1411:
1412: hr_utility_trace('Leaving '||l_proc_name);
1413: --
1414: IF p_earnings_amt >= l_exempt_amount THEN
1415:
1416: RETURN (p_earnings_amt - l_exempt_amount);
1474:
1475: BEGIN
1476:
1477: l_proc_name := g_proc_name ||'GET_PREVIOUS_PERIOD_BAL';
1478: hr_utility_trace('Entering '||l_proc_name);
1479:
1480: l_balance_value := 0;
1481: OPEN get_previous_assact;
1482: FETCH get_previous_assact INTO l_prev_assact;
1481: OPEN get_previous_assact;
1482: FETCH get_previous_assact INTO l_prev_assact;
1483: CLOSE get_previous_assact;
1484:
1485: hr_utility_trace('Previous assignment action id = '||l_prev_assact);
1486:
1487: IF l_prev_assact IS NOT NULL THEN /* Bug 4283490 */
1488: l_counter := 1;
1489:
1496: p_get_rb_route => FALSE);
1497: l_balance_value := l_balance_value_tab(1).balance_value;
1498: END IF;
1499:
1500: hr_utility_trace('Return value = '||l_balance_value);
1501: hr_utility_trace('Leaving '||l_proc_name);
1502:
1503: RETURN (l_balance_value);
1504:
1497: l_balance_value := l_balance_value_tab(1).balance_value;
1498: END IF;
1499:
1500: hr_utility_trace('Return value = '||l_balance_value);
1501: hr_utility_trace('Leaving '||l_proc_name);
1502:
1503: RETURN (l_balance_value);
1504:
1505: END GET_PREVIOUS_PERIOD_BAL;
1555: lv_jurisdiction pay_mx_legislation_info_f.jurisdiction_code%type;
1556: lv_legislation_info_type pay_mx_legislation_info_f.legislation_info_type%type;
1557: BEGIN
1558: l_proc_name := g_proc_name ||'GET_MX_TAX_INFO';
1559: hr_utility_trace('Entering '||l_proc_name);
1560: l_exists := FALSE;
1561: cntr := g_pay_mx_legislation_info_f.count();
1562: hr_utility_trace('Number of cached legislative tax info records = '||cntr);
1563:
1558: l_proc_name := g_proc_name ||'GET_MX_TAX_INFO';
1559: hr_utility_trace('Entering '||l_proc_name);
1560: l_exists := FALSE;
1561: cntr := g_pay_mx_legislation_info_f.count();
1562: hr_utility_trace('Number of cached legislative tax info records = '||cntr);
1563:
1564: IF cntr > 0 THEN /* Check if legislation info exists in cache. */
1565: FOR cntr IN g_pay_mx_legislation_info_f.first()..g_pay_mx_legislation_info_f.last()
1566: LOOP
1577:
1578: THEN
1579:
1580: l_exists := TRUE;
1581: hr_utility_trace ('Retrieving legislative tax info from cache.');
1582: /* Copy cache onto out parameters */
1583: p_legislation_info1 := g_pay_mx_legislation_info_f(cntr).legislation_info1;
1584: p_legislation_info2 := g_pay_mx_legislation_info_f(cntr).legislation_info2;
1585: p_legislation_info3 := g_pay_mx_legislation_info_f(cntr).legislation_info3;
1597: cntr := g_pay_mx_legislation_info_f.last() + 1;
1598: ELSE
1599: cntr := 1;
1600: END IF;
1601: hr_utility_trace ('Legislative tax info not found in cache. Hitting database now.');
1602:
1603: OPEN csr_get_ss_info;
1604: FETCH csr_get_ss_info
1605: INTO g_pay_mx_legislation_info_f(cntr).effective_start_date,
1635:
1636: -- Bug 4656174
1637: IF p_legislation_info_type = 'MX Social Security Information' THEN
1638: IF p_legislation_info1 = 'WRI' THEN
1639: hr_utility_trace('Hitting database for WRIP');
1640: OPEN csr_get_ss_info;
1641: FETCH csr_get_ss_info
1642: INTO ld_start_date,
1643: ld_end_date,
1656: p_tax_unit_id => p_ctx_tax_unit_id);
1657: END IF;
1658: END IF;
1659:
1660: hr_utility_trace('p_legislation_info_type = '||p_legislation_info_type);
1661: hr_utility_trace('p_legislation_info1 = ' ||p_legislation_info1);
1662: hr_utility_trace('p_legislation_info2 = ' ||p_legislation_info2);
1663: hr_utility_trace('p_legislation_info3 = ' ||p_legislation_info3);
1664: hr_utility_trace('p_legislation_info4 = ' ||p_legislation_info4);
1657: END IF;
1658: END IF;
1659:
1660: hr_utility_trace('p_legislation_info_type = '||p_legislation_info_type);
1661: hr_utility_trace('p_legislation_info1 = ' ||p_legislation_info1);
1662: hr_utility_trace('p_legislation_info2 = ' ||p_legislation_info2);
1663: hr_utility_trace('p_legislation_info3 = ' ||p_legislation_info3);
1664: hr_utility_trace('p_legislation_info4 = ' ||p_legislation_info4);
1665: hr_utility_trace('p_legislation_info5 = ' ||p_legislation_info5);
1658: END IF;
1659:
1660: hr_utility_trace('p_legislation_info_type = '||p_legislation_info_type);
1661: hr_utility_trace('p_legislation_info1 = ' ||p_legislation_info1);
1662: hr_utility_trace('p_legislation_info2 = ' ||p_legislation_info2);
1663: hr_utility_trace('p_legislation_info3 = ' ||p_legislation_info3);
1664: hr_utility_trace('p_legislation_info4 = ' ||p_legislation_info4);
1665: hr_utility_trace('p_legislation_info5 = ' ||p_legislation_info5);
1666: hr_utility_trace('p_legislation_info6 = ' ||p_legislation_info6);
1659:
1660: hr_utility_trace('p_legislation_info_type = '||p_legislation_info_type);
1661: hr_utility_trace('p_legislation_info1 = ' ||p_legislation_info1);
1662: hr_utility_trace('p_legislation_info2 = ' ||p_legislation_info2);
1663: hr_utility_trace('p_legislation_info3 = ' ||p_legislation_info3);
1664: hr_utility_trace('p_legislation_info4 = ' ||p_legislation_info4);
1665: hr_utility_trace('p_legislation_info5 = ' ||p_legislation_info5);
1666: hr_utility_trace('p_legislation_info6 = ' ||p_legislation_info6);
1667:
1660: hr_utility_trace('p_legislation_info_type = '||p_legislation_info_type);
1661: hr_utility_trace('p_legislation_info1 = ' ||p_legislation_info1);
1662: hr_utility_trace('p_legislation_info2 = ' ||p_legislation_info2);
1663: hr_utility_trace('p_legislation_info3 = ' ||p_legislation_info3);
1664: hr_utility_trace('p_legislation_info4 = ' ||p_legislation_info4);
1665: hr_utility_trace('p_legislation_info5 = ' ||p_legislation_info5);
1666: hr_utility_trace('p_legislation_info6 = ' ||p_legislation_info6);
1667:
1668: hr_utility_trace('Leaving '||l_proc_name);
1661: hr_utility_trace('p_legislation_info1 = ' ||p_legislation_info1);
1662: hr_utility_trace('p_legislation_info2 = ' ||p_legislation_info2);
1663: hr_utility_trace('p_legislation_info3 = ' ||p_legislation_info3);
1664: hr_utility_trace('p_legislation_info4 = ' ||p_legislation_info4);
1665: hr_utility_trace('p_legislation_info5 = ' ||p_legislation_info5);
1666: hr_utility_trace('p_legislation_info6 = ' ||p_legislation_info6);
1667:
1668: hr_utility_trace('Leaving '||l_proc_name);
1669: RETURN(0);
1662: hr_utility_trace('p_legislation_info2 = ' ||p_legislation_info2);
1663: hr_utility_trace('p_legislation_info3 = ' ||p_legislation_info3);
1664: hr_utility_trace('p_legislation_info4 = ' ||p_legislation_info4);
1665: hr_utility_trace('p_legislation_info5 = ' ||p_legislation_info5);
1666: hr_utility_trace('p_legislation_info6 = ' ||p_legislation_info6);
1667:
1668: hr_utility_trace('Leaving '||l_proc_name);
1669: RETURN(0);
1670: EXCEPTION WHEN OTHERS THEN
1664: hr_utility_trace('p_legislation_info4 = ' ||p_legislation_info4);
1665: hr_utility_trace('p_legislation_info5 = ' ||p_legislation_info5);
1666: hr_utility_trace('p_legislation_info6 = ' ||p_legislation_info6);
1667:
1668: hr_utility_trace('Leaving '||l_proc_name);
1669: RETURN(0);
1670: EXCEPTION WHEN OTHERS THEN
1671: g_pay_mx_legislation_info_f.DELETE();
1672: RAISE;
1692: BEGIN
1693:
1694: l_proc_name := g_proc_name || 'GET_MX_EE_HEAD_COUNT';
1695:
1696: hr_utility_trace('Entering ' || l_proc_name);
1697:
1698: l_legal_employer_name := hr_general.decode_organization(
1699: hr_mx_utility.get_legal_employer(
1700: p_ctx_business_group_id,
1700: p_ctx_business_group_id,
1701: p_ctx_tax_unit_id)
1702: );
1703:
1704: hr_utility_trace('Legal Employer Name: ' || l_legal_employer_name);
1705:
1706: l_row_name := l_legal_employer_name || ' (' ||
1707: p_ctx_jurisdiction_code || ')';
1708:
1755: BEGIN
1756:
1757: l_proc_name := g_proc_name || 'GET_MX_STATE_TAX_RULES';
1758:
1759: hr_utility_trace('Entering ' || l_proc_name);
1760:
1761: l_legal_er_id := hr_mx_utility.get_legal_employer(p_ctx_business_group_id,
1762: p_ctx_tax_unit_id);
1763:
1764: OPEN c_get_rate_data(l_legal_er_id);
1765: FETCH c_get_rate_data INTO l_return_value;
1766: CLOSE c_get_rate_data;
1767:
1768: hr_utility_trace('Leaving ' || l_proc_name);
1769:
1770: RETURN (l_return_value);
1771:
1772: END GET_MX_STATE_TAX_RULES;
1815: l_proc_name VARCHAR2(100);
1816: ln_isr NUMBER;
1817: BEGIN
1818: l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX-2';
1819: hr_utility_trace('Entering '||l_proc_name);
1820:
1821: ln_isr := pay_mx_tax_functions.calculate_isr_tax(
1822: p_payroll_action_id => P_PAYROLL_ACTION_ID,
1823: p_assignment_action_id => P_ASSIGNMENT_ACTION_ID,
1835: p_isr_non_creditable_subsidy => p_isr_non_creditable_subsidy,
1836: p_credit_to_salary => p_credit_to_salary,
1837: p_credit_to_salary_paid => p_credit_to_salary_paid);
1838:
1839: hr_utility_trace('ISR = ' || ln_isr);
1840: hr_utility_trace('Leaving '||l_proc_name);
1841: RETURN (ln_isr);
1842: END CALCULATE_ISR_TAX;
1843:
1836: p_credit_to_salary => p_credit_to_salary,
1837: p_credit_to_salary_paid => p_credit_to_salary_paid);
1838:
1839: hr_utility_trace('ISR = ' || ln_isr);
1840: hr_utility_trace('Leaving '||l_proc_name);
1841: RETURN (ln_isr);
1842: END CALCULATE_ISR_TAX;
1843:
1844:
2032: AND paf.effective_end_date;
2033:
2034: BEGIN
2035: l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX';
2036: hr_utility_trace('Entering '||l_proc_name);
2037: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2038: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2039: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2040: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2033:
2034: BEGIN
2035: l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX';
2036: hr_utility_trace('Entering '||l_proc_name);
2037: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2038: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2039: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2040: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2041: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2034: BEGIN
2035: l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX';
2036: hr_utility_trace('Entering '||l_proc_name);
2037: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2038: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2039: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2040: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2041: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2042: hr_utility_trace('p_date_earned: '||
2035: l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX';
2036: hr_utility_trace('Entering '||l_proc_name);
2037: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2038: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2039: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2040: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2041: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2042: hr_utility_trace('p_date_earned: '||
2043: fnd_date.date_to_canonical(p_date_earned));
2036: hr_utility_trace('Entering '||l_proc_name);
2037: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2038: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2039: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2040: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2041: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2042: hr_utility_trace('p_date_earned: '||
2043: fnd_date.date_to_canonical(p_date_earned));
2044: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2037: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2038: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2039: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2040: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2041: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2042: hr_utility_trace('p_date_earned: '||
2043: fnd_date.date_to_canonical(p_date_earned));
2044: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2045: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2038: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2039: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2040: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2041: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2042: hr_utility_trace('p_date_earned: '||
2043: fnd_date.date_to_canonical(p_date_earned));
2044: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2045: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2046: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2040: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2041: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2042: hr_utility_trace('p_date_earned: '||
2043: fnd_date.date_to_canonical(p_date_earned));
2044: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2045: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2046: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2047: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2048: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2041: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2042: hr_utility_trace('p_date_earned: '||
2043: fnd_date.date_to_canonical(p_date_earned));
2044: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2045: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2046: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2047: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2048: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2049: hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2042: hr_utility_trace('p_date_earned: '||
2043: fnd_date.date_to_canonical(p_date_earned));
2044: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2045: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2046: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2047: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2048: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2049: hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2050:
2043: fnd_date.date_to_canonical(p_date_earned));
2044: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2045: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2046: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2047: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2048: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2049: hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2050:
2051: ln_ISR_proj_subject := p_subject_amount;
2044: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2045: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2046: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2047: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2048: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2049: hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2050:
2051: ln_ISR_proj_subject := p_subject_amount;
2052:
2045: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2046: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2047: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2048: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2049: hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2050:
2051: ln_ISR_proj_subject := p_subject_amount;
2052:
2053: OPEN csr_date_earned;
2053: OPEN csr_date_earned;
2054: FETCH csr_date_earned INTO l_date_paid, l_date_earned;
2055: CLOSE csr_date_earned;
2056:
2057: hr_utility_trace('l_date_earned: '||l_date_earned);
2058: hr_utility_trace('l_date_paid: '||l_date_paid);
2059:
2060: --Start of Bug Fix 6852627
2061: /*Calculation for Proration*/
2054: FETCH csr_date_earned INTO l_date_paid, l_date_earned;
2055: CLOSE csr_date_earned;
2056:
2057: hr_utility_trace('l_date_earned: '||l_date_earned);
2058: hr_utility_trace('l_date_paid: '||l_date_paid);
2059:
2060: --Start of Bug Fix 6852627
2061: /*Calculation for Proration*/
2062:
2075: OPEN csr_get_hire_date;
2076: FETCH csr_get_hire_date INTO ld_hire_date;
2077: CLOSE csr_get_hire_date;
2078:
2079: hr_utility_trace('payroll_id '||ln_payroll_id);
2080: hr_utility_trace('ld_hire_date '||ld_hire_date);
2081: hr_utility_trace('Getting the actual number of days in pay period ...');
2082:
2083: OPEN csr_get_no_of_days_in_period(ln_payroll_id);
2076: FETCH csr_get_hire_date INTO ld_hire_date;
2077: CLOSE csr_get_hire_date;
2078:
2079: hr_utility_trace('payroll_id '||ln_payroll_id);
2080: hr_utility_trace('ld_hire_date '||ld_hire_date);
2081: hr_utility_trace('Getting the actual number of days in pay period ...');
2082:
2083: OPEN csr_get_no_of_days_in_period(ln_payroll_id);
2084: FETCH csr_get_no_of_days_in_period INTO ln_days_in_a_period;
2077: CLOSE csr_get_hire_date;
2078:
2079: hr_utility_trace('payroll_id '||ln_payroll_id);
2080: hr_utility_trace('ld_hire_date '||ld_hire_date);
2081: hr_utility_trace('Getting the actual number of days in pay period ...');
2082:
2083: OPEN csr_get_no_of_days_in_period(ln_payroll_id);
2084: FETCH csr_get_no_of_days_in_period INTO ln_days_in_a_period;
2085: CLOSE csr_get_no_of_days_in_period;
2083: OPEN csr_get_no_of_days_in_period(ln_payroll_id);
2084: FETCH csr_get_no_of_days_in_period INTO ln_days_in_a_period;
2085: CLOSE csr_get_no_of_days_in_period;
2086:
2087: hr_utility_trace('Number of days in the pay period : '||ln_days_in_a_period);
2088:
2089: hr_utility_trace('Getting the total number of days in pay period as deifned in GRE/LE...');
2090:
2091: ln_le_id := hr_mx_utility.get_legal_employer(
2085: CLOSE csr_get_no_of_days_in_period;
2086:
2087: hr_utility_trace('Number of days in the pay period : '||ln_days_in_a_period);
2088:
2089: hr_utility_trace('Getting the total number of days in pay period as deifned in GRE/LE...');
2090:
2091: ln_le_id := hr_mx_utility.get_legal_employer(
2092: p_business_group_id => p_business_group_id
2093: ,p_tax_unit_id => p_tax_unit_id);
2104:
2105: /*ln_pay_period_days := pay_mx_utility.get_days_in_pay_period( p_business_group_id
2106: ,p_tax_unit_id
2107: ,ln_payroll_id);*/
2108: hr_utility_trace('Average days in the month at LE :'||to_char(ln_le_days_month));
2109:
2110: OPEN csr_def_bal_id ('ISR Non Working Days','_ASG_GRE_RUN');
2111: FETCH csr_def_bal_id INTO ln_def_bal_id;
2112: CLOSE csr_def_bal_id;
2110: OPEN csr_def_bal_id ('ISR Non Working Days','_ASG_GRE_RUN');
2111: FETCH csr_def_bal_id INTO ln_def_bal_id;
2112: CLOSE csr_def_bal_id;
2113:
2114: hr_utility_trace('ISR Non Working Days def bal id '||to_char(ln_def_bal_id));
2115: ln_bal_ISR_non_wrkd_days := pay_balance_pkg.get_value(ln_def_bal_id,
2116: p_assignment_action_id,
2117: p_tax_unit_id,
2118: NULL,
2121: NULL,
2122: NULL,
2123: NULL,
2124: 'TRUE');
2125: hr_utility_trace('ISR Non Working Days Bal Val '||to_char(ln_bal_ISR_non_wrkd_days));
2126:
2127: hr_utility_trace('Get the factor for proration ln_le_days_month/ln_days_in_a_period ..');
2128: ln_isr_prop_fact := ln_le_days_month/ln_days_in_a_period;
2129: hr_utility_trace('ln_isr_prop_fact :' || TO_CHAR(ln_isr_prop_fact));
2123: NULL,
2124: 'TRUE');
2125: hr_utility_trace('ISR Non Working Days Bal Val '||to_char(ln_bal_ISR_non_wrkd_days));
2126:
2127: hr_utility_trace('Get the factor for proration ln_le_days_month/ln_days_in_a_period ..');
2128: ln_isr_prop_fact := ln_le_days_month/ln_days_in_a_period;
2129: hr_utility_trace('ln_isr_prop_fact :' || TO_CHAR(ln_isr_prop_fact));
2130:
2131: hr_utility_trace('getting worked days.. ');
2125: hr_utility_trace('ISR Non Working Days Bal Val '||to_char(ln_bal_ISR_non_wrkd_days));
2126:
2127: hr_utility_trace('Get the factor for proration ln_le_days_month/ln_days_in_a_period ..');
2128: ln_isr_prop_fact := ln_le_days_month/ln_days_in_a_period;
2129: hr_utility_trace('ln_isr_prop_fact :' || TO_CHAR(ln_isr_prop_fact));
2130:
2131: hr_utility_trace('getting worked days.. ');
2132: ln_worked_days := ln_days_in_a_period - nvl(ln_bal_ISR_non_wrkd_days,0);
2133:
2127: hr_utility_trace('Get the factor for proration ln_le_days_month/ln_days_in_a_period ..');
2128: ln_isr_prop_fact := ln_le_days_month/ln_days_in_a_period;
2129: hr_utility_trace('ln_isr_prop_fact :' || TO_CHAR(ln_isr_prop_fact));
2130:
2131: hr_utility_trace('getting worked days.. ');
2132: ln_worked_days := ln_days_in_a_period - nvl(ln_bal_ISR_non_wrkd_days,0);
2133:
2134: ln_le_worked_days := (ln_le_days_month - (nvl(ln_bal_ISR_non_wrkd_days,0) * ln_isr_prop_fact)) ;
2135:
2132: ln_worked_days := ln_days_in_a_period - nvl(ln_bal_ISR_non_wrkd_days,0);
2133:
2134: ln_le_worked_days := (ln_le_days_month - (nvl(ln_bal_ISR_non_wrkd_days,0) * ln_isr_prop_fact)) ;
2135:
2136: hr_utility_trace('Actual worked days in the period for ISR Tax '||to_char(ln_worked_days));
2137: hr_utility_trace('worked days for proration in the period for ISR Tax '||to_char(ln_le_worked_days));
2138: --End of Bug Fix 6852627
2139: IF to_char(l_date_paid,'yyyymmdd')
2140: >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
2133:
2134: ln_le_worked_days := (ln_le_days_month - (nvl(ln_bal_ISR_non_wrkd_days,0) * ln_isr_prop_fact)) ;
2135:
2136: hr_utility_trace('Actual worked days in the period for ISR Tax '||to_char(ln_worked_days));
2137: hr_utility_trace('worked days for proration in the period for ISR Tax '||to_char(ln_le_worked_days));
2138: --End of Bug Fix 6852627
2139: IF to_char(l_date_paid,'yyyymmdd')
2140: >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
2141: hr_utility_trace('ISr Proration in 2008');
2137: hr_utility_trace('worked days for proration in the period for ISR Tax '||to_char(ln_le_worked_days));
2138: --End of Bug Fix 6852627
2139: IF to_char(l_date_paid,'yyyymmdd')
2140: >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
2141: hr_utility_trace('ISr Proration in 2008');
2142: IF nvl(ln_bal_ISR_non_wrkd_days,0) > 0 AND nvl(ln_bal_ISR_non_wrkd_days,0) < ln_days_in_a_period THEN
2143:
2144: ln_ISR_proj_subject := ROUND(((ln_ISR_proj_subject * ln_le_days_month) / ln_le_worked_days),2);
2145:
2142: IF nvl(ln_bal_ISR_non_wrkd_days,0) > 0 AND nvl(ln_bal_ISR_non_wrkd_days,0) < ln_days_in_a_period THEN
2143:
2144: ln_ISR_proj_subject := ROUND(((ln_ISR_proj_subject * ln_le_days_month) / ln_le_worked_days),2);
2145:
2146: hr_utility_trace('ISR Projected value in the pay period '||to_char(ln_ISR_proj_subject));
2147: END IF;
2148:
2149: END IF;
2150:
2147: END IF;
2148:
2149: END IF;
2150:
2151: hr_utility_trace('Final Subject amount after proration: '||ln_ISR_proj_subject);
2152: /* Article 113, 114 and 115 */
2153:
2154: ln_fixed_rate := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2155: p_business_group_id
2156: ,p_isr_rates_table
2157: ,'Fixed Rate'
2158: ,TO_CHAR(ln_ISR_proj_subject)));
2159:
2160: hr_utility_trace('ln_fixed_rate: '||ln_fixed_rate);
2161:
2162: ln_marginal_rate := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2163: p_business_group_id
2164: ,p_isr_rates_table
2164: ,p_isr_rates_table
2165: ,'Marginal Rate'
2166: ,TO_CHAR(ln_ISR_proj_subject)));
2167:
2168: hr_utility_trace('ln_marginal_rate: '||ln_marginal_rate);
2169:
2170: ln_lower_limit := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2171: p_business_group_id
2172: ,p_isr_rates_table
2172: ,p_isr_rates_table
2173: ,'Lower Bound'
2174: ,TO_CHAR(ln_ISR_proj_subject)));
2175:
2176: hr_utility_trace('ln_lower_limit: '||ln_lower_limit);
2177:
2178: ln_marginal_tax := (ln_marginal_rate/100) * (ln_ISR_proj_subject -
2179: ln_lower_limit);
2180:
2177:
2178: ln_marginal_tax := (ln_marginal_rate/100) * (ln_ISR_proj_subject -
2179: ln_lower_limit);
2180:
2181: hr_utility_trace('ln_marginal_tax: '||ln_marginal_tax);
2182:
2183: p_isr_calculated := ln_fixed_rate + ln_marginal_tax;
2184:
2185: hr_utility_trace('p_isr_calculated: '||p_isr_calculated);
2181: hr_utility_trace('ln_marginal_tax: '||ln_marginal_tax);
2182:
2183: p_isr_calculated := ln_fixed_rate + ln_marginal_tax;
2184:
2185: hr_utility_trace('p_isr_calculated: '||p_isr_calculated);
2186:
2187: IF TO_CHAR(l_date_paid,'yyyymmdd')
2188: < TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
2189:
2193: ,p_subsidy_table
2194: ,'Fixed Rate'
2195: ,TO_CHAR(ln_ISR_proj_subject)));
2196:
2197: hr_utility_trace('ln_fixed_subsidy: '||ln_fixed_subsidy);
2198:
2199: ln_marginal_subsidy := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2200: p_business_group_id
2201: ,p_subsidy_table
2201: ,p_subsidy_table
2202: ,'Marginal Rate'
2203: ,TO_CHAR(ln_ISR_proj_subject)));
2204:
2205: hr_utility_trace('ln_marginal_subsidy: '||ln_marginal_subsidy);
2206:
2207: ln_subsidy_lower_limit := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2208: p_business_group_id
2209: ,p_subsidy_table
2210: ,'Lower Bound'
2211: ,TO_CHAR(ln_ISR_proj_subject)));
2212:
2213:
2214: hr_utility_trace('ln_subsidy_lower_limit: '||ln_subsidy_lower_limit);
2215:
2216: ln_tax_subsidy_percentage := hr_mx_utility.get_tax_subsidy_percent(
2217: p_business_group_id
2218: ,p_tax_unit_id);
2216: ln_tax_subsidy_percentage := hr_mx_utility.get_tax_subsidy_percent(
2217: p_business_group_id
2218: ,p_tax_unit_id);
2219:
2220: hr_utility_trace('ln_tax_subsidy_percentage: '||ln_tax_subsidy_percentage);
2221:
2222: /*OPEN csr_get_process_type;
2223: FETCH csr_get_process_type INTO lv_action_type,
2224: lv_calc_mode,
2226: CLOSE csr_get_process_type;*/
2227:
2228: IF (p_calc_mode = 'BEST' OR p_calc_mode = 'ARTICLE141') THEN
2229: -- Calculate subsidy for Article 141
2230: hr_utility_trace('Calculating subsidy for Article 141 ...');
2231: ln_total_subsidy := ln_fixed_rate * (ln_fixed_subsidy/100) +
2232: (ln_marginal_subsidy/100) *
2233: (ln_marginal_rate/100) *
2234: (ln_ISR_proj_subject - ln_subsidy_lower_limit);
2237: (ln_marginal_subsidy/100) *
2238: (ln_marginal_rate/100) *
2239: (ln_ISR_proj_subject - ln_subsidy_lower_limit);
2240: END IF;
2241: hr_utility_trace('ln_total_subsidy: '||ln_total_subsidy);
2242:
2243: p_isr_creditable_subsidy := (ln_tax_subsidy_percentage/100) *
2244: ln_total_subsidy;
2245:
2242:
2243: p_isr_creditable_subsidy := (ln_tax_subsidy_percentage/100) *
2244: ln_total_subsidy;
2245:
2246: hr_utility_trace('p_isr_creditable_subsidy: '||p_isr_creditable_subsidy);
2247:
2248: p_isr_non_creditable_subsidy := ln_total_subsidy - p_isr_creditable_subsidy;
2249:
2250: hr_utility_trace('p_isr_non_creditable_subsidy: '||
2246: hr_utility_trace('p_isr_creditable_subsidy: '||p_isr_creditable_subsidy);
2247:
2248: p_isr_non_creditable_subsidy := ln_total_subsidy - p_isr_creditable_subsidy;
2249:
2250: hr_utility_trace('p_isr_non_creditable_subsidy: '||
2251: p_isr_non_creditable_subsidy);
2252:
2253: ELSE
2254: p_isr_creditable_subsidy := 0;
2264: OPEN csr_get_compute_subsidy_flag;
2265: FETCH csr_get_compute_subsidy_flag into l_compute_subsidy_flag;
2266: CLOSE csr_get_compute_subsidy_flag;
2267:
2268: hr_utility_trace('l_compute_subsidy_flag '||l_compute_subsidy_flag);
2269: /*bug7445486 bug#8438155*/
2270: IF ( p_credit_to_salary_table <> 'NONE' and P_CALC_MODE <>'ARTICLE142' AND l_compute_subsidy_flag <> 'N') THEN
2271:
2272: hr_utility_trace('inside subsidy '||p_credit_to_salary_table|| ' --- '||TO_CHAR(ln_ISR_proj_subject));
2268: hr_utility_trace('l_compute_subsidy_flag '||l_compute_subsidy_flag);
2269: /*bug7445486 bug#8438155*/
2270: IF ( p_credit_to_salary_table <> 'NONE' and P_CALC_MODE <>'ARTICLE142' AND l_compute_subsidy_flag <> 'N') THEN
2271:
2272: hr_utility_trace('inside subsidy '||p_credit_to_salary_table|| ' --- '||TO_CHAR(ln_ISR_proj_subject));
2273: p_credit_to_salary := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2274: p_business_group_id
2275: ,p_credit_to_salary_table
2276: ,'Amount'
2297: OPEN csr_get_hire_date;
2298: FETCH csr_get_hire_date INTO ld_hire_date;
2299: CLOSE csr_get_hire_date;
2300:
2301: hr_utility_trace('payroll_id 2'||ln_payroll_id);
2302: hr_utility_trace('ld_hire_date 2'||ld_hire_date);
2303:
2304: -- get ISR Subject balance for month
2305:
2298: FETCH csr_get_hire_date INTO ld_hire_date;
2299: CLOSE csr_get_hire_date;
2300:
2301: hr_utility_trace('payroll_id 2'||ln_payroll_id);
2302: hr_utility_trace('ld_hire_date 2'||ld_hire_date);
2303:
2304: -- get ISR Subject balance for month
2305:
2306: OPEN csr_def_bal_id ('ISR Subject','_ASG_MTD');
2306: OPEN csr_def_bal_id ('ISR Subject','_ASG_MTD');
2307: FETCH csr_def_bal_id INTO ln_def_bal_id;
2308: CLOSE csr_def_bal_id;
2309:
2310: hr_utility_trace('ISR Subject def bal id '||ln_def_bal_id);
2311:
2312: ln_bal_amt := pay_balance_pkg.get_value(ln_def_bal_id,
2313: P_ASSIGNMENT_ACTION_ID,
2314: p_tax_unit_id,
2319: NULL,
2320: NULL,
2321: 'TRUE');
2322:
2323: hr_utility_trace('MTD Subject Amt - ln_bal_amt '||to_char(ln_bal_amt));
2324: /*Get the previous ISR Subject adjusted amount due to proration
2325: this will have impact only in semi-monthly and weekely payroll*/
2326: OPEN csr_def_bal_id ('ISR Subject Adjusted','_ASG_MTD');
2327: FETCH csr_def_bal_id INTO ln_def_bal_id;
2326: OPEN csr_def_bal_id ('ISR Subject Adjusted','_ASG_MTD');
2327: FETCH csr_def_bal_id INTO ln_def_bal_id;
2328: CLOSE csr_def_bal_id;
2329:
2330: hr_utility_trace('ISR Subject Adjusted def bal id '||ln_def_bal_id);
2331: ln_ISR_subj_adj := pay_balance_pkg.get_value(ln_def_bal_id,
2332: P_ASSIGNMENT_ACTION_ID,
2333: p_tax_unit_id,
2334: null,
2337: null,
2338: null,
2339: null,
2340: 'TRUE');
2341: hr_utility_trace('ISR Subject Adjusted Amount MTD - ln_ISR_subj_adj'||ln_ISR_subj_adj);
2342: -- ln_bal_amt := ln_bal_amt + ln_ISR_subj_adj;
2343: -- hr_utility_trace('Actual subject value is ln_bal_amt + ln_ISR_subj_adj '||ln_bal_amt);
2344: -- get ISR Subsidy for Employement balance for month
2345: ln_ISR_subj_adj := ln_ISR_subj_adj + ln_ISR_proj_subject;
2339: null,
2340: 'TRUE');
2341: hr_utility_trace('ISR Subject Adjusted Amount MTD - ln_ISR_subj_adj'||ln_ISR_subj_adj);
2342: -- ln_bal_amt := ln_bal_amt + ln_ISR_subj_adj;
2343: -- hr_utility_trace('Actual subject value is ln_bal_amt + ln_ISR_subj_adj '||ln_bal_amt);
2344: -- get ISR Subsidy for Employement balance for month
2345: ln_ISR_subj_adj := ln_ISR_subj_adj + ln_ISR_proj_subject;
2346: hr_utility_trace('Actual subject value is ln_ISR_subj_adj + ln_ISR_proj_subject '||ln_ISR_subj_adj);
2347:
2342: -- ln_bal_amt := ln_bal_amt + ln_ISR_subj_adj;
2343: -- hr_utility_trace('Actual subject value is ln_bal_amt + ln_ISR_subj_adj '||ln_bal_amt);
2344: -- get ISR Subsidy for Employement balance for month
2345: ln_ISR_subj_adj := ln_ISR_subj_adj + ln_ISR_proj_subject;
2346: hr_utility_trace('Actual subject value is ln_ISR_subj_adj + ln_ISR_proj_subject '||ln_ISR_subj_adj);
2347:
2348: OPEN csr_def_bal_id ('ISR Subsidy for Employment','_ASG_MTD');
2349: FETCH csr_def_bal_id INTO ln_def_bal_id;
2350: CLOSE csr_def_bal_id;
2348: OPEN csr_def_bal_id ('ISR Subsidy for Employment','_ASG_MTD');
2349: FETCH csr_def_bal_id INTO ln_def_bal_id;
2350: CLOSE csr_def_bal_id;
2351:
2352: hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
2353:
2354: ln_bal_sub := pay_balance_pkg.get_value(ln_def_bal_id,
2355: P_ASSIGNMENT_ACTION_ID,
2356: p_tax_unit_id,
2361: NULL,
2362: NULL,
2363: 'TRUE');
2364:
2365: hr_utility_trace('ISR Sub for Empl Bal Sub '||to_char(ln_bal_sub));
2366:
2367: OPEN csr_def_bal_id ('ISR Subsidy for Employment Paid','_ASG_MTD');
2368: FETCH csr_def_bal_id INTO ln_def_bal_id;
2369: CLOSE csr_def_bal_id;
2367: OPEN csr_def_bal_id ('ISR Subsidy for Employment Paid','_ASG_MTD');
2368: FETCH csr_def_bal_id INTO ln_def_bal_id;
2369: CLOSE csr_def_bal_id;
2370:
2371: hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
2372:
2373: ln_bal_sub_paid := pay_balance_pkg.get_value(ln_def_bal_id,
2374: P_ASSIGNMENT_ACTION_ID,
2375: p_tax_unit_id,
2380: NULL,
2381: NULL,
2382: 'TRUE');
2383:
2384: hr_utility_trace('ISR Sub for Empl Bal Sub Paid '||to_char(ln_bal_sub_paid));
2385:
2386: if ln_payroll_id is not null then
2387: select min(period_type)
2388: into lv_period_type
2403: ,ld_first_pay_date
2404: ,ln_period_number)
2405: + (ln_ISR_subj_adj - ln_ISR_proj_subject);
2406:
2407: hr_utility_trace('ln_isr_subject_mtd : '||ln_isr_subject_mtd);
2408: hr_utility_trace('ln_days_in_a_period : '||ln_days_in_a_period);
2409: hr_utility_trace('ln_period_number : '||ln_period_number);
2410:
2411: open csr_get_period_count_in_month;
2404: ,ln_period_number)
2405: + (ln_ISR_subj_adj - ln_ISR_proj_subject);
2406:
2407: hr_utility_trace('ln_isr_subject_mtd : '||ln_isr_subject_mtd);
2408: hr_utility_trace('ln_days_in_a_period : '||ln_days_in_a_period);
2409: hr_utility_trace('ln_period_number : '||ln_period_number);
2410:
2411: open csr_get_period_count_in_month;
2412: loop
2405: + (ln_ISR_subj_adj - ln_ISR_proj_subject);
2406:
2407: hr_utility_trace('ln_isr_subject_mtd : '||ln_isr_subject_mtd);
2408: hr_utility_trace('ln_days_in_a_period : '||ln_days_in_a_period);
2409: hr_utility_trace('ln_period_number : '||ln_period_number);
2410:
2411: open csr_get_period_count_in_month;
2412: loop
2413: fetch csr_get_period_count_in_month into ln_mult_num,
2427: and to_char(ptp.end_date,'yyyymm') = to_char(l_date_paid,'yyyymm')
2428: and to_char(ppa.effective_date,'yyyymm') < to_char(l_date_paid,'yyyymm')
2429: AND ld_hire_date <= ptp.end_date;
2430:
2431: hr_utility_trace('l_date_paid '||to_char(l_date_paid,'yyyymm'));
2432: hr_utility_trace('ln_pre_date_paid '||to_char(ln_pre_date_paid));
2433:
2434: IF l_date_paid >= ln_period_start_mtd and
2435: l_date_paid <= ln_period_end_mtd then
2428: and to_char(ppa.effective_date,'yyyymm') < to_char(l_date_paid,'yyyymm')
2429: AND ld_hire_date <= ptp.end_date;
2430:
2431: hr_utility_trace('l_date_paid '||to_char(l_date_paid,'yyyymm'));
2432: hr_utility_trace('ln_pre_date_paid '||to_char(ln_pre_date_paid));
2433:
2434: IF l_date_paid >= ln_period_start_mtd and
2435: l_date_paid <= ln_period_end_mtd then
2436: ln_period_number := ln_period_number - ln_pre_date_paid;
2433:
2434: IF l_date_paid >= ln_period_start_mtd and
2435: l_date_paid <= ln_period_end_mtd then
2436: ln_period_number := ln_period_number - ln_pre_date_paid;
2437: hr_utility_trace('Actual ln_period_number '||to_char(ln_period_number));
2438: end if;
2439:
2440: end loop;
2441: close csr_get_period_count_in_month;
2457: ,ln_days_in_a_period
2458: ,ln_period_number
2459: ,lv_period_type));
2460:
2461: hr_utility_trace('Total Subsidy for Empl '||to_char(p_credit_to_salary));
2462:
2463: ln_credit_to_salary_max := p_credit_to_salary;
2464:
2465: P_CREDIT_TO_SALARY := (ln_credit_to_salary_max/30.4) * ln_days_in_period_sub_empl;
2465: P_CREDIT_TO_SALARY := (ln_credit_to_salary_max/30.4) * ln_days_in_period_sub_empl;
2466:
2467: ln_credit_to_salary_curr := P_CREDIT_TO_SALARY;
2468:
2469: hr_utility_trace('Current Period Subsidy for Empl '||to_char(p_credit_to_salary));
2470:
2471: select count(*) max_row
2472: into ln_max_row
2473: from PER_TIME_PERIODS ptp1
2487: to_char(ln_period_end_mtd,'yyyymmdd') >= to_char(last_day(l_date_paid),'yyyymmdd') then
2488:
2489: P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
2490: P_CREDIT_TO_SALARY * ln_mult_num );
2491: hr_utility_trace('outside/last day of month '||to_char(p_credit_to_salary));
2492: else
2493: P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
2494: P_CREDIT_TO_SALARY * (ln_mult_num - ln_pre_date_paid) );
2495: hr_utility_trace('With in month '||to_char(p_credit_to_salary));
2491: hr_utility_trace('outside/last day of month '||to_char(p_credit_to_salary));
2492: else
2493: P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
2494: P_CREDIT_TO_SALARY * (ln_mult_num - ln_pre_date_paid) );
2495: hr_utility_trace('With in month '||to_char(p_credit_to_salary));
2496: end if;
2497:
2498: hr_utility_trace('max allowd Subsidy for Empl '||to_char(p_credit_to_salary));
2499: IF l_date_paid >= ln_period_start_mtd AND
2494: P_CREDIT_TO_SALARY * (ln_mult_num - ln_pre_date_paid) );
2495: hr_utility_trace('With in month '||to_char(p_credit_to_salary));
2496: end if;
2497:
2498: hr_utility_trace('max allowd Subsidy for Empl '||to_char(p_credit_to_salary));
2499: IF l_date_paid >= ln_period_start_mtd AND
2500: l_date_paid <= ln_period_end_mtd AND
2501: (lv_period_type = 'Calendar Month' OR (lv_period_type = 'Semi-Month' AND ln_period_number = 2)
2502: OR (lv_period_type = 'Ten Days' AND ln_period_number = 3))
2501: (lv_period_type = 'Calendar Month' OR (lv_period_type = 'Semi-Month' AND ln_period_number = 2)
2502: OR (lv_period_type = 'Ten Days' AND ln_period_number = 3))
2503: THEN
2504: P_CREDIT_TO_SALARY := ln_credit_to_salary_max;
2505: hr_utility_trace('Final period Subsidy for Empl '||to_char(p_credit_to_salary));
2506: end if;
2507: P_CREDIT_TO_SALARY := P_CREDIT_TO_SALARY - ln_bal_sub;
2508: hr_utility_trace('Subsidy for Empl '||to_char(p_credit_to_salary));
2509: end if;
2504: P_CREDIT_TO_SALARY := ln_credit_to_salary_max;
2505: hr_utility_trace('Final period Subsidy for Empl '||to_char(p_credit_to_salary));
2506: end if;
2507: P_CREDIT_TO_SALARY := P_CREDIT_TO_SALARY - ln_bal_sub;
2508: hr_utility_trace('Subsidy for Empl '||to_char(p_credit_to_salary));
2509: end if;
2510: end loop;
2511: close csr_get_period_count_in_month;
2512: END IF;
2514: p_credit_to_salary := 0;
2515: END IF;
2516:
2517: P_CREDIT_TO_SALARY := nvl(P_CREDIT_TO_SALARY,0);
2518: hr_utility_trace('p_credit_to_salary: '||p_credit_to_salary);
2519:
2520: /* ISR Withheld Calculation */
2521:
2522: ln_isr_withheld := p_isr_calculated -
2523: p_isr_creditable_subsidy -
2524: p_credit_to_salary;
2525:
2526:
2527: hr_utility_trace('ln_isr_withheld B4: '||ln_isr_withheld);
2528:
2529: /*Incuded the proration logic based on balance ISR Non working days*/
2530: --Start of Bug fix 6852627
2531: IF to_char(l_date_paid,'yyyymmdd')
2531: IF to_char(l_date_paid,'yyyymmdd')
2532: >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
2533: IF nvl(ln_bal_ISR_non_wrkd_days,0) > 0 AND nvl(ln_bal_ISR_non_wrkd_days,0) < ln_days_in_a_period THEN
2534:
2535: hr_utility_trace('-- Non workings days are feeded, need to proproate ISR Tax --');
2536:
2537: hr_utility_trace('Getting the table factor value for ISR Tax calculation');
2538: ln_ISR_table_factor := ROUND(((ln_days_in_a_period * 30.4)/ln_le_days_month),2);
2539:
2533: IF nvl(ln_bal_ISR_non_wrkd_days,0) > 0 AND nvl(ln_bal_ISR_non_wrkd_days,0) < ln_days_in_a_period THEN
2534:
2535: hr_utility_trace('-- Non workings days are feeded, need to proproate ISR Tax --');
2536:
2537: hr_utility_trace('Getting the table factor value for ISR Tax calculation');
2538: ln_ISR_table_factor := ROUND(((ln_days_in_a_period * 30.4)/ln_le_days_month),2);
2539:
2540: hr_utility_trace('Factor value for '||lv_period_type||' payroll is '||TO_CHAR(ln_ISR_table_factor));
2541:
2536:
2537: hr_utility_trace('Getting the table factor value for ISR Tax calculation');
2538: ln_ISR_table_factor := ROUND(((ln_days_in_a_period * 30.4)/ln_le_days_month),2);
2539:
2540: hr_utility_trace('Factor value for '||lv_period_type||' payroll is '||TO_CHAR(ln_ISR_table_factor));
2541:
2542: hr_utility_trace('Proration logic on ISR Tax for '||TO_CHAR(ln_worked_days)||' working days');
2543:
2544: ln_isr_withheld := (ln_isr_withheld / ln_ISR_table_factor) * ln_worked_days ;
2538: ln_ISR_table_factor := ROUND(((ln_days_in_a_period * 30.4)/ln_le_days_month),2);
2539:
2540: hr_utility_trace('Factor value for '||lv_period_type||' payroll is '||TO_CHAR(ln_ISR_table_factor));
2541:
2542: hr_utility_trace('Proration logic on ISR Tax for '||TO_CHAR(ln_worked_days)||' working days');
2543:
2544: ln_isr_withheld := (ln_isr_withheld / ln_ISR_table_factor) * ln_worked_days ;
2545:
2546: hr_utility_trace('Prorated ISR with held '||TO_CHAR(ln_isr_withheld));
2542: hr_utility_trace('Proration logic on ISR Tax for '||TO_CHAR(ln_worked_days)||' working days');
2543:
2544: ln_isr_withheld := (ln_isr_withheld / ln_ISR_table_factor) * ln_worked_days ;
2545:
2546: hr_utility_trace('Prorated ISR with held '||TO_CHAR(ln_isr_withheld));
2547: hr_utility_trace('Start of ISR Subject proration ');
2548:
2549: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) < 0 OR nvl(ln_bal_ISR_non_wrkd_days,0) > ln_days_in_a_period THEN
2550: hr_utility_trace('ISR Non Worked Days value is invalid ');
2543:
2544: ln_isr_withheld := (ln_isr_withheld / ln_ISR_table_factor) * ln_worked_days ;
2545:
2546: hr_utility_trace('Prorated ISR with held '||TO_CHAR(ln_isr_withheld));
2547: hr_utility_trace('Start of ISR Subject proration ');
2548:
2549: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) < 0 OR nvl(ln_bal_ISR_non_wrkd_days,0) > ln_days_in_a_period THEN
2550: hr_utility_trace('ISR Non Worked Days value is invalid ');
2551: hr_utility.set_message(801, 'PAY_MX_INVALID_ISR_NON_WRK_DAY');
2546: hr_utility_trace('Prorated ISR with held '||TO_CHAR(ln_isr_withheld));
2547: hr_utility_trace('Start of ISR Subject proration ');
2548:
2549: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) < 0 OR nvl(ln_bal_ISR_non_wrkd_days,0) > ln_days_in_a_period THEN
2550: hr_utility_trace('ISR Non Worked Days value is invalid ');
2551: hr_utility.set_message(801, 'PAY_MX_INVALID_ISR_NON_WRK_DAY');
2552: hr_utility.raise_error;
2553:
2554: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) = ln_days_in_a_period THEN
2547: hr_utility_trace('Start of ISR Subject proration ');
2548:
2549: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) < 0 OR nvl(ln_bal_ISR_non_wrkd_days,0) > ln_days_in_a_period THEN
2550: hr_utility_trace('ISR Non Worked Days value is invalid ');
2551: hr_utility.set_message(801, 'PAY_MX_INVALID_ISR_NON_WRK_DAY');
2552: hr_utility.raise_error;
2553:
2554: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) = ln_days_in_a_period THEN
2555: ln_isr_withheld := 0;
2548:
2549: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) < 0 OR nvl(ln_bal_ISR_non_wrkd_days,0) > ln_days_in_a_period THEN
2550: hr_utility_trace('ISR Non Worked Days value is invalid ');
2551: hr_utility.set_message(801, 'PAY_MX_INVALID_ISR_NON_WRK_DAY');
2552: hr_utility.raise_error;
2553:
2554: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) = ln_days_in_a_period THEN
2555: ln_isr_withheld := 0;
2556: p_credit_to_salary_paid := 0;
2570: /*To feed ISR Subject Adjusted Balance*/
2571: IF TO_CHAR(l_date_paid,'yyyymmdd')
2572: >= TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
2573: p_isr_creditable_subsidy := ln_ISR_proj_subject;
2574: hr_utility_trace(' Feeding ISR Subject Adjusted Balance value in 2008 = p_isr_creditable_subsidy'||p_isr_creditable_subsidy);
2575: END IF;
2576: hr_utility_trace('p_credit_to_salary_paid: '||p_credit_to_salary_paid);
2577: hr_utility_trace('ln_isr_withheld Final: '||ln_isr_withheld);
2578: hr_utility_trace('Leaving '||l_proc_name);
2572: >= TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
2573: p_isr_creditable_subsidy := ln_ISR_proj_subject;
2574: hr_utility_trace(' Feeding ISR Subject Adjusted Balance value in 2008 = p_isr_creditable_subsidy'||p_isr_creditable_subsidy);
2575: END IF;
2576: hr_utility_trace('p_credit_to_salary_paid: '||p_credit_to_salary_paid);
2577: hr_utility_trace('ln_isr_withheld Final: '||ln_isr_withheld);
2578: hr_utility_trace('Leaving '||l_proc_name);
2579:
2580: RETURN ln_isr_withheld;
2573: p_isr_creditable_subsidy := ln_ISR_proj_subject;
2574: hr_utility_trace(' Feeding ISR Subject Adjusted Balance value in 2008 = p_isr_creditable_subsidy'||p_isr_creditable_subsidy);
2575: END IF;
2576: hr_utility_trace('p_credit_to_salary_paid: '||p_credit_to_salary_paid);
2577: hr_utility_trace('ln_isr_withheld Final: '||ln_isr_withheld);
2578: hr_utility_trace('Leaving '||l_proc_name);
2579:
2580: RETURN ln_isr_withheld;
2581:
2574: hr_utility_trace(' Feeding ISR Subject Adjusted Balance value in 2008 = p_isr_creditable_subsidy'||p_isr_creditable_subsidy);
2575: END IF;
2576: hr_utility_trace('p_credit_to_salary_paid: '||p_credit_to_salary_paid);
2577: hr_utility_trace('ln_isr_withheld Final: '||ln_isr_withheld);
2578: hr_utility_trace('Leaving '||l_proc_name);
2579:
2580: RETURN ln_isr_withheld;
2581:
2582: EXCEPTION
2580: RETURN ln_isr_withheld;
2581:
2582: EXCEPTION
2583: WHEN OTHERS THEN
2584: hr_utility_trace('Exception in '||l_proc_name||': '||SQLERRM);
2585: RAISE;
2586: END CALCULATE_ISR_TAX;
2587:
2588:
2871: AND paf.effective_end_date;
2872:
2873: BEGIN
2874: l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX';
2875: hr_utility_trace('Entering '||l_proc_name);
2876: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2877: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2878: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2879: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2872:
2873: BEGIN
2874: l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX';
2875: hr_utility_trace('Entering '||l_proc_name);
2876: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2877: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2878: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2879: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2880: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2873: BEGIN
2874: l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX';
2875: hr_utility_trace('Entering '||l_proc_name);
2876: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2877: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2878: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2879: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2880: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2881: hr_utility_trace('p_date_earned: '||
2874: l_proc_name := g_proc_name ||'CALCULATE_ISR_TAX';
2875: hr_utility_trace('Entering '||l_proc_name);
2876: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2877: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2878: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2879: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2880: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2881: hr_utility_trace('p_date_earned: '||
2882: fnd_date.date_to_canonical(p_date_earned));
2875: hr_utility_trace('Entering '||l_proc_name);
2876: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2877: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2878: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2879: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2880: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2881: hr_utility_trace('p_date_earned: '||
2882: fnd_date.date_to_canonical(p_date_earned));
2883: hr_utility_trace('p_run_type: '||p_run_type);
2876: hr_utility_trace('p_payroll_action_id: '||p_payroll_action_id);
2877: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2878: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2879: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2880: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2881: hr_utility_trace('p_date_earned: '||
2882: fnd_date.date_to_canonical(p_date_earned));
2883: hr_utility_trace('p_run_type: '||p_run_type);
2884: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2877: hr_utility_trace('p_assignment_action_id: '||p_assignment_action_id);
2878: hr_utility_trace('p_business_group_id: '||p_business_group_id);
2879: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2880: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2881: hr_utility_trace('p_date_earned: '||
2882: fnd_date.date_to_canonical(p_date_earned));
2883: hr_utility_trace('p_run_type: '||p_run_type);
2884: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2885: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2879: hr_utility_trace('p_assignment_id: '||p_assignment_id);
2880: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2881: hr_utility_trace('p_date_earned: '||
2882: fnd_date.date_to_canonical(p_date_earned));
2883: hr_utility_trace('p_run_type: '||p_run_type);
2884: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2885: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2886: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2887: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2880: hr_utility_trace('p_tax_unit_id: '||p_tax_unit_id);
2881: hr_utility_trace('p_date_earned: '||
2882: fnd_date.date_to_canonical(p_date_earned));
2883: hr_utility_trace('p_run_type: '||p_run_type);
2884: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2885: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2886: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2887: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2888: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2881: hr_utility_trace('p_date_earned: '||
2882: fnd_date.date_to_canonical(p_date_earned));
2883: hr_utility_trace('p_run_type: '||p_run_type);
2884: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2885: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2886: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2887: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2888: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2889: hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2882: fnd_date.date_to_canonical(p_date_earned));
2883: hr_utility_trace('p_run_type: '||p_run_type);
2884: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2885: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2886: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2887: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2888: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2889: hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2890:
2883: hr_utility_trace('p_run_type: '||p_run_type);
2884: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2885: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2886: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2887: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2888: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2889: hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2890:
2891: ln_ISR_proj_subject := p_subject_amount;
2884: hr_utility_trace('p_calc_mode: '||p_calc_mode);
2885: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2886: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2887: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2888: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2889: hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2890:
2891: ln_ISR_proj_subject := p_subject_amount;
2892:
2885: hr_utility_trace('p_subject_amount: '||p_subject_amount);
2886: hr_utility_trace('p_isr_rates_table: '||p_isr_rates_table);
2887: hr_utility_trace('p_subsidy_table: '||p_subsidy_table);
2888: hr_utility_trace('p_credit_to_salary_table: '||p_credit_to_salary_table);
2889: hr_utility_trace('l_credit_to_salary_table: '||l_credit_to_salary_table);
2890:
2891: ln_ISR_proj_subject := p_subject_amount;
2892:
2893: OPEN csr_date_earned;
2893: OPEN csr_date_earned;
2894: FETCH csr_date_earned INTO l_date_paid, l_date_earned;
2895: CLOSE csr_date_earned;
2896:
2897: hr_utility_trace('l_date_earned: '||l_date_earned);
2898: hr_utility_trace('l_date_paid: '||l_date_paid);
2899:
2900: --Start of Bug Fix 6852627
2901: /*Calculation for Proration*/
2894: FETCH csr_date_earned INTO l_date_paid, l_date_earned;
2895: CLOSE csr_date_earned;
2896:
2897: hr_utility_trace('l_date_earned: '||l_date_earned);
2898: hr_utility_trace('l_date_paid: '||l_date_paid);
2899:
2900: --Start of Bug Fix 6852627
2901: /*Calculation for Proration*/
2902: OPEN csr_get_payroll_id; /*bug#8932102 */
2906: OPEN csr_get_hire_date;
2907: FETCH csr_get_hire_date INTO ld_hire_date;
2908: CLOSE csr_get_hire_date;
2909:
2910: hr_utility_trace('payroll_id '||ln_payroll_id);
2911: hr_utility_trace('ld_hire_date '||ld_hire_date);
2912:
2913: hr_utility_trace('Getting the actual number of days in pay period ...');
2914:
2907: FETCH csr_get_hire_date INTO ld_hire_date;
2908: CLOSE csr_get_hire_date;
2909:
2910: hr_utility_trace('payroll_id '||ln_payroll_id);
2911: hr_utility_trace('ld_hire_date '||ld_hire_date);
2912:
2913: hr_utility_trace('Getting the actual number of days in pay period ...');
2914:
2915: OPEN csr_get_no_of_days_in_period(ln_payroll_id);
2909:
2910: hr_utility_trace('payroll_id '||ln_payroll_id);
2911: hr_utility_trace('ld_hire_date '||ld_hire_date);
2912:
2913: hr_utility_trace('Getting the actual number of days in pay period ...');
2914:
2915: OPEN csr_get_no_of_days_in_period(ln_payroll_id);
2916: FETCH csr_get_no_of_days_in_period INTO ln_days_in_a_period;
2917: CLOSE csr_get_no_of_days_in_period;
2915: OPEN csr_get_no_of_days_in_period(ln_payroll_id);
2916: FETCH csr_get_no_of_days_in_period INTO ln_days_in_a_period;
2917: CLOSE csr_get_no_of_days_in_period;
2918:
2919: hr_utility_trace('Number of days in the pay period : '||ln_days_in_a_period);
2920:
2921: hr_utility_trace('Getting the total number of days in pay period as deifned in GRE/LE...');
2922:
2923: ln_le_id := hr_mx_utility.get_legal_employer(
2917: CLOSE csr_get_no_of_days_in_period;
2918:
2919: hr_utility_trace('Number of days in the pay period : '||ln_days_in_a_period);
2920:
2921: hr_utility_trace('Getting the total number of days in pay period as deifned in GRE/LE...');
2922:
2923: ln_le_id := hr_mx_utility.get_legal_employer(
2924: p_business_group_id => p_business_group_id
2925: ,p_tax_unit_id => p_tax_unit_id);
2936:
2937: /*ln_pay_period_days := pay_mx_utility.get_days_in_pay_period( p_business_group_id
2938: ,p_tax_unit_id
2939: ,ln_payroll_id);*/
2940: hr_utility_trace('Average days in the month at LE :'||to_char(ln_le_days_month));
2941:
2942: OPEN csr_def_bal_id ('ISR Non Working Days','_ASG_GRE_RUN');
2943: FETCH csr_def_bal_id INTO ln_def_bal_id;
2944: CLOSE csr_def_bal_id;
2942: OPEN csr_def_bal_id ('ISR Non Working Days','_ASG_GRE_RUN');
2943: FETCH csr_def_bal_id INTO ln_def_bal_id;
2944: CLOSE csr_def_bal_id;
2945:
2946: hr_utility_trace('ISR Non Working Days def bal id '||to_char(ln_def_bal_id));
2947: ln_bal_ISR_non_wrkd_days := pay_balance_pkg.get_value(ln_def_bal_id,
2948: p_assignment_action_id,
2949: p_tax_unit_id,
2950: NULL,
2953: NULL,
2954: NULL,
2955: NULL,
2956: 'TRUE');
2957: hr_utility_trace('ISR Non Working Days Bal Val '||to_char(ln_bal_ISR_non_wrkd_days));
2958:
2959: hr_utility_trace('Get the factor for proration ln_le_days_month/ln_days_in_a_period ..');
2960: ln_isr_prop_fact := ln_le_days_month/ln_days_in_a_period;
2961: hr_utility_trace('ln_isr_prop_fact :' || TO_CHAR(ln_isr_prop_fact));
2955: NULL,
2956: 'TRUE');
2957: hr_utility_trace('ISR Non Working Days Bal Val '||to_char(ln_bal_ISR_non_wrkd_days));
2958:
2959: hr_utility_trace('Get the factor for proration ln_le_days_month/ln_days_in_a_period ..');
2960: ln_isr_prop_fact := ln_le_days_month/ln_days_in_a_period;
2961: hr_utility_trace('ln_isr_prop_fact :' || TO_CHAR(ln_isr_prop_fact));
2962:
2963: hr_utility_trace('getting worked days.. ');
2957: hr_utility_trace('ISR Non Working Days Bal Val '||to_char(ln_bal_ISR_non_wrkd_days));
2958:
2959: hr_utility_trace('Get the factor for proration ln_le_days_month/ln_days_in_a_period ..');
2960: ln_isr_prop_fact := ln_le_days_month/ln_days_in_a_period;
2961: hr_utility_trace('ln_isr_prop_fact :' || TO_CHAR(ln_isr_prop_fact));
2962:
2963: hr_utility_trace('getting worked days.. ');
2964: ln_worked_days := ln_days_in_a_period - nvl(ln_bal_ISR_non_wrkd_days,0);
2965:
2959: hr_utility_trace('Get the factor for proration ln_le_days_month/ln_days_in_a_period ..');
2960: ln_isr_prop_fact := ln_le_days_month/ln_days_in_a_period;
2961: hr_utility_trace('ln_isr_prop_fact :' || TO_CHAR(ln_isr_prop_fact));
2962:
2963: hr_utility_trace('getting worked days.. ');
2964: ln_worked_days := ln_days_in_a_period - nvl(ln_bal_ISR_non_wrkd_days,0);
2965:
2966: ln_le_worked_days := (ln_le_days_month - (nvl(ln_bal_ISR_non_wrkd_days,0) * ln_isr_prop_fact)) ;
2967:
2964: ln_worked_days := ln_days_in_a_period - nvl(ln_bal_ISR_non_wrkd_days,0);
2965:
2966: ln_le_worked_days := (ln_le_days_month - (nvl(ln_bal_ISR_non_wrkd_days,0) * ln_isr_prop_fact)) ;
2967:
2968: hr_utility_trace('Actual worked days in the period for ISR Tax '||to_char(ln_worked_days));
2969: hr_utility_trace('worked days for proration in the period for ISR Tax '||to_char(ln_le_worked_days));
2970: --End of Bug Fix 6852627
2971: IF to_char(l_date_paid,'yyyymmdd')
2972: >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
2965:
2966: ln_le_worked_days := (ln_le_days_month - (nvl(ln_bal_ISR_non_wrkd_days,0) * ln_isr_prop_fact)) ;
2967:
2968: hr_utility_trace('Actual worked days in the period for ISR Tax '||to_char(ln_worked_days));
2969: hr_utility_trace('worked days for proration in the period for ISR Tax '||to_char(ln_le_worked_days));
2970: --End of Bug Fix 6852627
2971: IF to_char(l_date_paid,'yyyymmdd')
2972: >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
2973: hr_utility_trace('ISr Proration in 2008');
2969: hr_utility_trace('worked days for proration in the period for ISR Tax '||to_char(ln_le_worked_days));
2970: --End of Bug Fix 6852627
2971: IF to_char(l_date_paid,'yyyymmdd')
2972: >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
2973: hr_utility_trace('ISr Proration in 2008');
2974: IF nvl(ln_bal_ISR_non_wrkd_days,0) > 0 AND nvl(ln_bal_ISR_non_wrkd_days,0) < ln_days_in_a_period THEN
2975:
2976: ln_ISR_proj_subject := ROUND(((ln_ISR_proj_subject * ln_le_days_month) / ln_le_worked_days),2);
2977:
2974: IF nvl(ln_bal_ISR_non_wrkd_days,0) > 0 AND nvl(ln_bal_ISR_non_wrkd_days,0) < ln_days_in_a_period THEN
2975:
2976: ln_ISR_proj_subject := ROUND(((ln_ISR_proj_subject * ln_le_days_month) / ln_le_worked_days),2);
2977:
2978: hr_utility_trace('ISR Projected value in the pay period '||to_char(ln_ISR_proj_subject));
2979: END IF;
2980:
2981: END IF;
2982:
2979: END IF;
2980:
2981: END IF;
2982:
2983: hr_utility_trace('Final Subject amount after proration: '||ln_ISR_proj_subject);
2984: /* Article 113, 114 and 115 */
2985:
2986: ln_fixed_rate := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2987: p_business_group_id
2988: ,p_isr_rates_table
2989: ,'Fixed Rate'
2990: ,TO_CHAR(ln_ISR_proj_subject)));
2991:
2992: hr_utility_trace('ln_fixed_rate: '||ln_fixed_rate);
2993:
2994: ln_marginal_rate := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
2995: p_business_group_id
2996: ,p_isr_rates_table
2996: ,p_isr_rates_table
2997: ,'Marginal Rate'
2998: ,TO_CHAR(ln_ISR_proj_subject)));
2999:
3000: hr_utility_trace('ln_marginal_rate: '||ln_marginal_rate);
3001:
3002: ln_lower_limit := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
3003: p_business_group_id
3004: ,p_isr_rates_table
3004: ,p_isr_rates_table
3005: ,'Lower Bound'
3006: ,TO_CHAR(ln_ISR_proj_subject)));
3007:
3008: hr_utility_trace('ln_lower_limit: '||ln_lower_limit);
3009:
3010: ln_marginal_tax := (ln_marginal_rate/100) * (ln_ISR_proj_subject -
3011: ln_lower_limit);
3012:
3009:
3010: ln_marginal_tax := (ln_marginal_rate/100) * (ln_ISR_proj_subject -
3011: ln_lower_limit);
3012:
3013: hr_utility_trace('ln_marginal_tax: '||ln_marginal_tax);
3014:
3015: p_isr_calculated := ln_fixed_rate + ln_marginal_tax;
3016:
3017: hr_utility_trace('p_isr_calculated: '||p_isr_calculated);
3013: hr_utility_trace('ln_marginal_tax: '||ln_marginal_tax);
3014:
3015: p_isr_calculated := ln_fixed_rate + ln_marginal_tax;
3016:
3017: hr_utility_trace('p_isr_calculated: '||p_isr_calculated);
3018:
3019: IF TO_CHAR(l_date_paid,'yyyymmdd')
3020: < TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
3021:
3025: ,p_subsidy_table
3026: ,'Fixed Rate'
3027: ,TO_CHAR(ln_ISR_proj_subject)));
3028:
3029: hr_utility_trace('ln_fixed_subsidy: '||ln_fixed_subsidy);
3030:
3031: ln_marginal_subsidy := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
3032: p_business_group_id
3033: ,p_subsidy_table
3033: ,p_subsidy_table
3034: ,'Marginal Rate'
3035: ,TO_CHAR(ln_ISR_proj_subject)));
3036:
3037: hr_utility_trace('ln_marginal_subsidy: '||ln_marginal_subsidy);
3038:
3039: ln_subsidy_lower_limit := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
3040: p_business_group_id
3041: ,p_subsidy_table
3042: ,'Lower Bound'
3043: ,TO_CHAR(ln_ISR_proj_subject)));
3044:
3045:
3046: hr_utility_trace('ln_subsidy_lower_limit: '||ln_subsidy_lower_limit);
3047:
3048: ln_tax_subsidy_percentage := hr_mx_utility.get_tax_subsidy_percent(
3049: p_business_group_id
3050: ,p_tax_unit_id);
3048: ln_tax_subsidy_percentage := hr_mx_utility.get_tax_subsidy_percent(
3049: p_business_group_id
3050: ,p_tax_unit_id);
3051:
3052: hr_utility_trace('ln_tax_subsidy_percentage: '||ln_tax_subsidy_percentage);
3053:
3054: /*OPEN csr_get_process_type;
3055: FETCH csr_get_process_type INTO lv_action_type,
3056: lv_calc_mode,
3058: CLOSE csr_get_process_type;*/
3059:
3060: IF (p_calc_mode = 'BEST' OR p_calc_mode = 'ARTICLE141') THEN
3061: -- Calculate subsidy for Article 141
3062: hr_utility_trace('Calculating subsidy for Article 141 ...');
3063: ln_total_subsidy := ln_fixed_rate * (ln_fixed_subsidy/100) +
3064: (ln_marginal_subsidy/100) *
3065: (ln_marginal_rate/100) *
3066: (ln_ISR_proj_subject - ln_subsidy_lower_limit);
3069: (ln_marginal_subsidy/100) *
3070: (ln_marginal_rate/100) *
3071: (ln_ISR_proj_subject - ln_subsidy_lower_limit);
3072: END IF;
3073: hr_utility_trace('ln_total_subsidy: '||ln_total_subsidy);
3074:
3075: p_isr_creditable_subsidy := (ln_tax_subsidy_percentage/100) *
3076: ln_total_subsidy;
3077:
3074:
3075: p_isr_creditable_subsidy := (ln_tax_subsidy_percentage/100) *
3076: ln_total_subsidy;
3077:
3078: hr_utility_trace('p_isr_creditable_subsidy: '||p_isr_creditable_subsidy);
3079:
3080: p_isr_non_creditable_subsidy := ln_total_subsidy - p_isr_creditable_subsidy;
3081:
3082: hr_utility_trace('p_isr_non_creditable_subsidy: '||
3078: hr_utility_trace('p_isr_creditable_subsidy: '||p_isr_creditable_subsidy);
3079:
3080: p_isr_non_creditable_subsidy := ln_total_subsidy - p_isr_creditable_subsidy;
3081:
3082: hr_utility_trace('p_isr_non_creditable_subsidy: '||
3083: p_isr_non_creditable_subsidy);
3084:
3085: ELSE
3086: p_isr_creditable_subsidy := 0;
3096: OPEN csr_get_compute_subsidy_flag;
3097: FETCH csr_get_compute_subsidy_flag into l_compute_subsidy_flag;
3098: CLOSE csr_get_compute_subsidy_flag;
3099:
3100: hr_utility_trace('l_compute_subsidy_flag '||l_compute_subsidy_flag);
3101: /*bug7445486 bug#8438155*/
3102: IF ( p_credit_to_salary_table <> 'NONE' and P_CALC_MODE <>'ARTICLE142' AND l_compute_subsidy_flag <> 'N') THEN
3103:
3104: hr_utility_trace('inside subsidy '||p_credit_to_salary_table|| ' --- '||TO_CHAR(ln_ISR_proj_subject));
3100: hr_utility_trace('l_compute_subsidy_flag '||l_compute_subsidy_flag);
3101: /*bug7445486 bug#8438155*/
3102: IF ( p_credit_to_salary_table <> 'NONE' and P_CALC_MODE <>'ARTICLE142' AND l_compute_subsidy_flag <> 'N') THEN
3103:
3104: hr_utility_trace('inside subsidy '||p_credit_to_salary_table|| ' --- '||TO_CHAR(ln_ISR_proj_subject));
3105: p_credit_to_salary := FND_NUMBER.canonical_to_number(hruserdt.get_table_value (
3106: p_business_group_id
3107: ,p_credit_to_salary_table
3108: ,'Amount'
3120: OPEN csr_get_hire_date;
3121: FETCH csr_get_hire_date INTO ld_hire_date;
3122: CLOSE csr_get_hire_date;
3123:
3124: hr_utility_trace('payroll_id '||ln_payroll_id);
3125: hr_utility_trace('ld_hire_date '||ld_hire_date);
3126:
3127: -- get ISR Subject balance for month
3128:
3121: FETCH csr_get_hire_date INTO ld_hire_date;
3122: CLOSE csr_get_hire_date;
3123:
3124: hr_utility_trace('payroll_id '||ln_payroll_id);
3125: hr_utility_trace('ld_hire_date '||ld_hire_date);
3126:
3127: -- get ISR Subject balance for month
3128:
3129: OPEN csr_def_bal_id ('ISR Subject','_ASG_MTD');
3129: OPEN csr_def_bal_id ('ISR Subject','_ASG_MTD');
3130: FETCH csr_def_bal_id INTO ln_def_bal_id;
3131: CLOSE csr_def_bal_id;
3132:
3133: hr_utility_trace('ISR Subject def bal id '||ln_def_bal_id);
3134:
3135: ln_bal_amt := pay_balance_pkg.get_value(ln_def_bal_id,
3136: P_ASSIGNMENT_ACTION_ID,
3137: p_tax_unit_id,
3142: NULL,
3143: NULL,
3144: 'TRUE');
3145:
3146: hr_utility_trace('MTD Subject Amt - ln_bal_amt '||to_char(ln_bal_amt));
3147: /*Get the previous ISR Subject adjusted amount due to proration
3148: this will have impact only in semi-monthly and weekely payroll*/
3149: OPEN csr_def_bal_id ('ISR Subject Adjusted','_ASG_MTD');
3150: FETCH csr_def_bal_id INTO ln_def_bal_id;
3149: OPEN csr_def_bal_id ('ISR Subject Adjusted','_ASG_MTD');
3150: FETCH csr_def_bal_id INTO ln_def_bal_id;
3151: CLOSE csr_def_bal_id;
3152:
3153: hr_utility_trace('ISR Subject Adjusted def bal id '||ln_def_bal_id);
3154: ln_ISR_subj_adj := pay_balance_pkg.get_value(ln_def_bal_id,
3155: P_ASSIGNMENT_ACTION_ID,
3156: p_tax_unit_id,
3157: null,
3160: null,
3161: null,
3162: null,
3163: 'TRUE');
3164: hr_utility_trace('ISR Subject Adjusted Amount MTD - ln_ISR_subj_adj'||ln_ISR_subj_adj);
3165: -- ln_bal_amt := ln_bal_amt + ln_ISR_subj_adj;
3166: -- hr_utility_trace('Actual subject value is ln_bal_amt + ln_ISR_subj_adj '||ln_bal_amt);
3167: -- get ISR Subsidy for Employement balance for month
3168: ln_ISR_subj_adj := ln_ISR_subj_adj + ln_ISR_proj_subject;
3162: null,
3163: 'TRUE');
3164: hr_utility_trace('ISR Subject Adjusted Amount MTD - ln_ISR_subj_adj'||ln_ISR_subj_adj);
3165: -- ln_bal_amt := ln_bal_amt + ln_ISR_subj_adj;
3166: -- hr_utility_trace('Actual subject value is ln_bal_amt + ln_ISR_subj_adj '||ln_bal_amt);
3167: -- get ISR Subsidy for Employement balance for month
3168: ln_ISR_subj_adj := ln_ISR_subj_adj + ln_ISR_proj_subject;
3169: hr_utility_trace('Actual subject value is ln_ISR_subj_adj + ln_ISR_proj_subject '||ln_ISR_subj_adj);
3170:
3165: -- ln_bal_amt := ln_bal_amt + ln_ISR_subj_adj;
3166: -- hr_utility_trace('Actual subject value is ln_bal_amt + ln_ISR_subj_adj '||ln_bal_amt);
3167: -- get ISR Subsidy for Employement balance for month
3168: ln_ISR_subj_adj := ln_ISR_subj_adj + ln_ISR_proj_subject;
3169: hr_utility_trace('Actual subject value is ln_ISR_subj_adj + ln_ISR_proj_subject '||ln_ISR_subj_adj);
3170:
3171: OPEN csr_def_bal_id ('ISR Subsidy for Employment','_ASG_MTD');
3172: FETCH csr_def_bal_id INTO ln_def_bal_id;
3173: CLOSE csr_def_bal_id;
3171: OPEN csr_def_bal_id ('ISR Subsidy for Employment','_ASG_MTD');
3172: FETCH csr_def_bal_id INTO ln_def_bal_id;
3173: CLOSE csr_def_bal_id;
3174:
3175: hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
3176:
3177: ln_bal_sub := pay_balance_pkg.get_value(ln_def_bal_id,
3178: P_ASSIGNMENT_ACTION_ID,
3179: p_tax_unit_id,
3184: NULL,
3185: NULL,
3186: 'TRUE');
3187:
3188: hr_utility_trace('ISR Sub for Empl Bal Sub '||to_char(ln_bal_sub));
3189:
3190: OPEN csr_def_bal_id ('ISR Subsidy for Employment Paid','_ASG_MTD');
3191: FETCH csr_def_bal_id INTO ln_def_bal_id;
3192: CLOSE csr_def_bal_id;
3190: OPEN csr_def_bal_id ('ISR Subsidy for Employment Paid','_ASG_MTD');
3191: FETCH csr_def_bal_id INTO ln_def_bal_id;
3192: CLOSE csr_def_bal_id;
3193:
3194: hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
3195:
3196: ln_bal_sub_paid := pay_balance_pkg.get_value(ln_def_bal_id,
3197: P_ASSIGNMENT_ACTION_ID,
3198: p_tax_unit_id,
3203: NULL,
3204: NULL,
3205: 'TRUE');
3206:
3207: hr_utility_trace('ISR Sub for Empl Bal Sub Paid '||to_char(ln_bal_sub_paid));
3208:
3209: if ln_payroll_id is not null then
3210: select min(period_type)
3211: into lv_period_type
3226: ,ld_first_pay_date
3227: ,ln_period_number)
3228: + (ln_ISR_subj_adj - ln_ISR_proj_subject);
3229:
3230: hr_utility_trace('ln_isr_subject_mtd : '||ln_isr_subject_mtd);
3231: hr_utility_trace('ln_days_in_a_period : '||ln_days_in_a_period);
3232: hr_utility_trace('ln_period_number : '||ln_period_number);
3233:
3234: open csr_get_period_count_in_month;
3227: ,ln_period_number)
3228: + (ln_ISR_subj_adj - ln_ISR_proj_subject);
3229:
3230: hr_utility_trace('ln_isr_subject_mtd : '||ln_isr_subject_mtd);
3231: hr_utility_trace('ln_days_in_a_period : '||ln_days_in_a_period);
3232: hr_utility_trace('ln_period_number : '||ln_period_number);
3233:
3234: open csr_get_period_count_in_month;
3235: loop
3228: + (ln_ISR_subj_adj - ln_ISR_proj_subject);
3229:
3230: hr_utility_trace('ln_isr_subject_mtd : '||ln_isr_subject_mtd);
3231: hr_utility_trace('ln_days_in_a_period : '||ln_days_in_a_period);
3232: hr_utility_trace('ln_period_number : '||ln_period_number);
3233:
3234: open csr_get_period_count_in_month;
3235: loop
3236: fetch csr_get_period_count_in_month into ln_mult_num,
3250: and to_char(ptp.end_date,'yyyymm') = to_char(l_date_paid,'yyyymm')
3251: and to_char(ppa.effective_date,'yyyymm') < to_char(l_date_paid,'yyyymm')
3252: AND ld_hire_date <= ptp.end_date;
3253:
3254: hr_utility_trace('l_date_paid '||to_char(l_date_paid,'yyyymm'));
3255: hr_utility_trace('ln_pre_date_paid '||to_char(ln_pre_date_paid));
3256:
3257: IF l_date_paid >= ln_period_start_mtd and
3258: l_date_paid <= ln_period_end_mtd then
3251: and to_char(ppa.effective_date,'yyyymm') < to_char(l_date_paid,'yyyymm')
3252: AND ld_hire_date <= ptp.end_date;
3253:
3254: hr_utility_trace('l_date_paid '||to_char(l_date_paid,'yyyymm'));
3255: hr_utility_trace('ln_pre_date_paid '||to_char(ln_pre_date_paid));
3256:
3257: IF l_date_paid >= ln_period_start_mtd and
3258: l_date_paid <= ln_period_end_mtd then
3259: ln_period_number := ln_period_number - ln_pre_date_paid;
3256:
3257: IF l_date_paid >= ln_period_start_mtd and
3258: l_date_paid <= ln_period_end_mtd then
3259: ln_period_number := ln_period_number - ln_pre_date_paid;
3260: hr_utility_trace('Actual ln_period_number '||to_char(ln_period_number));
3261: end if;
3262:
3263: end loop;
3264: close csr_get_period_count_in_month;
3280: ,ln_days_in_a_period
3281: ,ln_period_number
3282: ,lv_period_type));
3283:
3284: hr_utility_trace('Total Subsidy for Empl '||to_char(p_credit_to_salary));
3285:
3286: ln_credit_to_salary_max := p_credit_to_salary;
3287:
3288: P_CREDIT_TO_SALARY := (ln_credit_to_salary_max/30.4) * ln_days_in_period_sub_empl;
3288: P_CREDIT_TO_SALARY := (ln_credit_to_salary_max/30.4) * ln_days_in_period_sub_empl;
3289:
3290: ln_credit_to_salary_curr := P_CREDIT_TO_SALARY;
3291:
3292: hr_utility_trace('Current Period Subsidy for Empl '||to_char(p_credit_to_salary));
3293:
3294: select count(*) max_row
3295: into ln_max_row
3296: from PER_TIME_PERIODS ptp1
3310: to_char(ln_period_end_mtd,'yyyymmdd') >= to_char(last_day(l_date_paid),'yyyymmdd') then
3311:
3312: P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
3313: P_CREDIT_TO_SALARY * ln_mult_num );
3314: hr_utility_trace('outside/last day of month '||to_char(p_credit_to_salary));
3315: else
3316: P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
3317: P_CREDIT_TO_SALARY * (ln_mult_num - ln_pre_date_paid) );
3318: hr_utility_trace('With in month '||to_char(p_credit_to_salary));
3314: hr_utility_trace('outside/last day of month '||to_char(p_credit_to_salary));
3315: else
3316: P_CREDIT_TO_SALARY := least(ln_credit_to_salary_max,
3317: P_CREDIT_TO_SALARY * (ln_mult_num - ln_pre_date_paid) );
3318: hr_utility_trace('With in month '||to_char(p_credit_to_salary));
3319: end if;
3320:
3321: hr_utility_trace('max allowd Subsidy for Empl '||to_char(p_credit_to_salary));
3322: IF l_date_paid >= ln_period_start_mtd AND
3317: P_CREDIT_TO_SALARY * (ln_mult_num - ln_pre_date_paid) );
3318: hr_utility_trace('With in month '||to_char(p_credit_to_salary));
3319: end if;
3320:
3321: hr_utility_trace('max allowd Subsidy for Empl '||to_char(p_credit_to_salary));
3322: IF l_date_paid >= ln_period_start_mtd AND
3323: l_date_paid <= ln_period_end_mtd AND
3324: (lv_period_type = 'Calendar Month' OR (lv_period_type = 'Semi-Month' AND ln_period_number = 2)
3325: OR (lv_period_type = 'Ten Days' AND ln_period_number = 3))
3324: (lv_period_type = 'Calendar Month' OR (lv_period_type = 'Semi-Month' AND ln_period_number = 2)
3325: OR (lv_period_type = 'Ten Days' AND ln_period_number = 3))
3326: THEN
3327: P_CREDIT_TO_SALARY := ln_credit_to_salary_max;
3328: hr_utility_trace('Final period Subsidy for Empl '||to_char(p_credit_to_salary));
3329: end if;
3330: P_CREDIT_TO_SALARY := P_CREDIT_TO_SALARY - ln_bal_sub;
3331: hr_utility_trace('Subsidy for Empl '||to_char(p_credit_to_salary));
3332: end if;
3327: P_CREDIT_TO_SALARY := ln_credit_to_salary_max;
3328: hr_utility_trace('Final period Subsidy for Empl '||to_char(p_credit_to_salary));
3329: end if;
3330: P_CREDIT_TO_SALARY := P_CREDIT_TO_SALARY - ln_bal_sub;
3331: hr_utility_trace('Subsidy for Empl '||to_char(p_credit_to_salary));
3332: end if;
3333: end loop;
3334: close csr_get_period_count_in_month;
3335: END IF;
3337: p_credit_to_salary := 0;
3338: END IF;
3339:
3340: P_CREDIT_TO_SALARY := nvl(P_CREDIT_TO_SALARY,0);
3341: hr_utility_trace('p_credit_to_salary: '||p_credit_to_salary);
3342:
3343: /* ISR Withheld Calculation */
3344:
3345: ln_isr_withheld := p_isr_calculated -
3346: p_isr_creditable_subsidy -
3347: p_credit_to_salary;
3348:
3349:
3350: hr_utility_trace('ln_isr_withheld B4: '||ln_isr_withheld);
3351:
3352: /*Incuded the proration logic based on balance ISR Non working days*/
3353: --Start of Bug fix 6852627
3354: IF to_char(l_date_paid,'yyyymmdd')
3354: IF to_char(l_date_paid,'yyyymmdd')
3355: >= to_char(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') AND P_CALC_MODE = 'ARTICLE113' THEN
3356: IF nvl(ln_bal_ISR_non_wrkd_days,0) > 0 AND nvl(ln_bal_ISR_non_wrkd_days,0) < ln_days_in_a_period THEN
3357:
3358: hr_utility_trace('-- Non workings days are feeded, need to proproate ISR Tax --');
3359:
3360: hr_utility_trace('Getting the table factor value for ISR Tax calculation');
3361: ln_ISR_table_factor := ROUND(((ln_days_in_a_period * 30.4)/ln_le_days_month),2);
3362:
3356: IF nvl(ln_bal_ISR_non_wrkd_days,0) > 0 AND nvl(ln_bal_ISR_non_wrkd_days,0) < ln_days_in_a_period THEN
3357:
3358: hr_utility_trace('-- Non workings days are feeded, need to proproate ISR Tax --');
3359:
3360: hr_utility_trace('Getting the table factor value for ISR Tax calculation');
3361: ln_ISR_table_factor := ROUND(((ln_days_in_a_period * 30.4)/ln_le_days_month),2);
3362:
3363: hr_utility_trace('Factor value for '||lv_period_type||' payroll is '||TO_CHAR(ln_ISR_table_factor));
3364:
3359:
3360: hr_utility_trace('Getting the table factor value for ISR Tax calculation');
3361: ln_ISR_table_factor := ROUND(((ln_days_in_a_period * 30.4)/ln_le_days_month),2);
3362:
3363: hr_utility_trace('Factor value for '||lv_period_type||' payroll is '||TO_CHAR(ln_ISR_table_factor));
3364:
3365: hr_utility_trace('Proration logic on ISR Tax for '||TO_CHAR(ln_worked_days)||' working days');
3366:
3367: /*ln_pro_sub_emp := (p_credit_to_salary/ln_ISR_table_factor)* ln_worked_days ;
3361: ln_ISR_table_factor := ROUND(((ln_days_in_a_period * 30.4)/ln_le_days_month),2);
3362:
3363: hr_utility_trace('Factor value for '||lv_period_type||' payroll is '||TO_CHAR(ln_ISR_table_factor));
3364:
3365: hr_utility_trace('Proration logic on ISR Tax for '||TO_CHAR(ln_worked_days)||' working days');
3366:
3367: /*ln_pro_sub_emp := (p_credit_to_salary/ln_ISR_table_factor)* ln_worked_days ;
3368:
3369: hr_utility_trace('Prorated Subsidy For Employment is '||TO_CHAR(ln_pro_sub_emp));*/
3365: hr_utility_trace('Proration logic on ISR Tax for '||TO_CHAR(ln_worked_days)||' working days');
3366:
3367: /*ln_pro_sub_emp := (p_credit_to_salary/ln_ISR_table_factor)* ln_worked_days ;
3368:
3369: hr_utility_trace('Prorated Subsidy For Employment is '||TO_CHAR(ln_pro_sub_emp));*/
3370:
3371: ln_isr_withheld := (ln_isr_withheld / ln_ISR_table_factor) * ln_worked_days ;
3372:
3373: hr_utility_trace('Prorated ISR with held '||TO_CHAR(ln_isr_withheld));
3369: hr_utility_trace('Prorated Subsidy For Employment is '||TO_CHAR(ln_pro_sub_emp));*/
3370:
3371: ln_isr_withheld := (ln_isr_withheld / ln_ISR_table_factor) * ln_worked_days ;
3372:
3373: hr_utility_trace('Prorated ISR with held '||TO_CHAR(ln_isr_withheld));
3374: hr_utility_trace('Start of ISR Subject proration ');
3375:
3376: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) < 0 OR nvl(ln_bal_ISR_non_wrkd_days,0) > ln_days_in_a_period THEN
3377: hr_utility_trace('ISR Non Worked Days value is invalid ');
3370:
3371: ln_isr_withheld := (ln_isr_withheld / ln_ISR_table_factor) * ln_worked_days ;
3372:
3373: hr_utility_trace('Prorated ISR with held '||TO_CHAR(ln_isr_withheld));
3374: hr_utility_trace('Start of ISR Subject proration ');
3375:
3376: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) < 0 OR nvl(ln_bal_ISR_non_wrkd_days,0) > ln_days_in_a_period THEN
3377: hr_utility_trace('ISR Non Worked Days value is invalid ');
3378: hr_utility.set_message(801, 'PAY_MX_INVALID_ISR_NON_WRK_DAY');
3373: hr_utility_trace('Prorated ISR with held '||TO_CHAR(ln_isr_withheld));
3374: hr_utility_trace('Start of ISR Subject proration ');
3375:
3376: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) < 0 OR nvl(ln_bal_ISR_non_wrkd_days,0) > ln_days_in_a_period THEN
3377: hr_utility_trace('ISR Non Worked Days value is invalid ');
3378: hr_utility.set_message(801, 'PAY_MX_INVALID_ISR_NON_WRK_DAY');
3379: hr_utility.raise_error;
3380:
3381: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) = ln_days_in_a_period THEN
3374: hr_utility_trace('Start of ISR Subject proration ');
3375:
3376: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) < 0 OR nvl(ln_bal_ISR_non_wrkd_days,0) > ln_days_in_a_period THEN
3377: hr_utility_trace('ISR Non Worked Days value is invalid ');
3378: hr_utility.set_message(801, 'PAY_MX_INVALID_ISR_NON_WRK_DAY');
3379: hr_utility.raise_error;
3380:
3381: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) = ln_days_in_a_period THEN
3382: ln_isr_withheld := 0;
3375:
3376: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) < 0 OR nvl(ln_bal_ISR_non_wrkd_days,0) > ln_days_in_a_period THEN
3377: hr_utility_trace('ISR Non Worked Days value is invalid ');
3378: hr_utility.set_message(801, 'PAY_MX_INVALID_ISR_NON_WRK_DAY');
3379: hr_utility.raise_error;
3380:
3381: ELSIF nvl(ln_bal_ISR_non_wrkd_days,0) = ln_days_in_a_period THEN
3382: ln_isr_withheld := 0;
3383: p_credit_to_salary_paid := 0;
3397: /*To feed ISR Subject Adjusted Balance*/
3398: IF TO_CHAR(l_date_paid,'yyyymmdd')
3399: >= TO_CHAR(fnd_date.canonical_to_date('2008/01/01'),'yyyymmdd') THEN
3400: p_isr_creditable_subsidy := ln_ISR_proj_subject;
3401: hr_utility_trace(' Feeding ISR Subject Adjusted Balance value in 2008 = p_isr_creditable_subsidy'||p_isr_creditable_subsidy);
3402: END IF;
3403: IF p_run_type = 'ADJTAX' OR p_run_type = 'MTDTAXADJ' THEN
3404:
3405: IF p_run_type = 'ADJTAX' THEN
3404:
3405: IF p_run_type = 'ADJTAX' THEN
3406: lv_dimension_type := '_ASG_YTD';
3407: lv_proration_table := 'isr rates_annual';
3408: hr_utility_trace('The Tax Adjustment type is Periodic Tax Adjustment ');
3409: ELSIF p_run_type = 'MTDTAXADJ' THEN
3410: lv_dimension_type := '_ASG_MTD';
3411: lv_proration_table := 'isr rates_month';
3412: hr_utility_trace('The Tax Adjustment type is Monthly Tax Adjustment ');
3408: hr_utility_trace('The Tax Adjustment type is Periodic Tax Adjustment ');
3409: ELSIF p_run_type = 'MTDTAXADJ' THEN
3410: lv_dimension_type := '_ASG_MTD';
3411: lv_proration_table := 'isr rates_month';
3412: hr_utility_trace('The Tax Adjustment type is Monthly Tax Adjustment ');
3413: END IF;
3414:
3415: OPEN csr_def_bal_id ('ISR Subject',lv_dimension_type);
3416: FETCH csr_def_bal_id INTO ln_def_bal_id;
3415: OPEN csr_def_bal_id ('ISR Subject',lv_dimension_type);
3416: FETCH csr_def_bal_id INTO ln_def_bal_id;
3417: CLOSE csr_def_bal_id;
3418:
3419: hr_utility_trace('ISR Subject def bal id '||ln_def_bal_id);
3420: ln_ISR_subj_adj_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3421: P_ASSIGNMENT_ACTION_ID,
3422: p_tax_unit_id,
3423: null,
3426: null,
3427: null,
3428: null,
3429: 'TRUE');
3430: hr_utility_trace('YTD ISR Subject def bal is '||ln_ISR_subj_adj_ytd);
3431:
3432: tot_sub_basis := ln_ISR_subj_adj_ytd;
3433:
3434: hr_utility_trace('Total Subject Basis for tax adjustment is '||tot_sub_basis);
3430: hr_utility_trace('YTD ISR Subject def bal is '||ln_ISR_subj_adj_ytd);
3431:
3432: tot_sub_basis := ln_ISR_subj_adj_ytd;
3433:
3434: hr_utility_trace('Total Subject Basis for tax adjustment is '||tot_sub_basis);
3435:
3436: OPEN csr_def_bal_id ('ISR Calculated',lv_dimension_type);
3437: FETCH csr_def_bal_id INTO ln_def_bal_id;
3438: CLOSE csr_def_bal_id;
3436: OPEN csr_def_bal_id ('ISR Calculated',lv_dimension_type);
3437: FETCH csr_def_bal_id INTO ln_def_bal_id;
3438: CLOSE csr_def_bal_id;
3439:
3440: hr_utility_trace('ISR Calculated def bal id '||ln_def_bal_id);
3441: ln_ISR_calculated_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3442: P_ASSIGNMENT_ACTION_ID,
3443: p_tax_unit_id,
3444: null,
3447: null,
3448: null,
3449: null,
3450: 'TRUE');
3451: hr_utility_trace('YTD ISR Calculated Value is '||ln_ISR_calculated_ytd);
3452:
3453:
3454: OPEN csr_def_bal_id ('ISR Withheld',lv_dimension_type);
3455: FETCH csr_def_bal_id INTO ln_def_bal_id;
3454: OPEN csr_def_bal_id ('ISR Withheld',lv_dimension_type);
3455: FETCH csr_def_bal_id INTO ln_def_bal_id;
3456: CLOSE csr_def_bal_id;
3457:
3458: hr_utility_trace('ISR Withheld def bal id '||to_char(ln_def_bal_id));
3459:
3460: ln_ISR_withheld_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3461: P_ASSIGNMENT_ACTION_ID,
3462: p_tax_unit_id,
3467: NULL,
3468: NULL,
3469: 'TRUE');
3470:
3471: hr_utility_trace('ISR withheld is '||ln_ISR_withheld_ytd);
3472:
3473: OPEN csr_def_bal_id ('ISR Subsidy for Employment Paid',lv_dimension_type);
3474: FETCH csr_def_bal_id INTO ln_def_bal_id;
3475: CLOSE csr_def_bal_id;
3473: OPEN csr_def_bal_id ('ISR Subsidy for Employment Paid',lv_dimension_type);
3474: FETCH csr_def_bal_id INTO ln_def_bal_id;
3475: CLOSE csr_def_bal_id;
3476:
3477: hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
3478:
3479: ln_ISR_subsidy_paid_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3480: P_ASSIGNMENT_ACTION_ID,
3481: p_tax_unit_id,
3486: NULL,
3487: NULL,
3488: 'TRUE');
3489:
3490: hr_utility_trace('ISR subsidy for employment paid is '||ln_ISR_subsidy_paid_ytd);
3491:
3492: ln_ISR_tax_to_charge_ytd := ln_ISR_withheld_ytd - ln_ISR_subsidy_paid_ytd;
3493: hr_utility_trace('YTD ISR Sub for Empl Bal Sub Paid '||to_char(ln_ISR_tax_to_charge_ytd));
3494: OPEN csr_def_bal_id ('ISR Subsidy for Employment',lv_dimension_type);
3489:
3490: hr_utility_trace('ISR subsidy for employment paid is '||ln_ISR_subsidy_paid_ytd);
3491:
3492: ln_ISR_tax_to_charge_ytd := ln_ISR_withheld_ytd - ln_ISR_subsidy_paid_ytd;
3493: hr_utility_trace('YTD ISR Sub for Empl Bal Sub Paid '||to_char(ln_ISR_tax_to_charge_ytd));
3494: OPEN csr_def_bal_id ('ISR Subsidy for Employment',lv_dimension_type);
3495: FETCH csr_def_bal_id INTO ln_def_bal_id;
3496: CLOSE csr_def_bal_id;
3497:
3494: OPEN csr_def_bal_id ('ISR Subsidy for Employment',lv_dimension_type);
3495: FETCH csr_def_bal_id INTO ln_def_bal_id;
3496: CLOSE csr_def_bal_id;
3497:
3498: hr_utility_trace('ISR Sub for Empl def bal id '||to_char(ln_def_bal_id));
3499:
3500: ln_ISR_subsidy_empl_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3501: P_ASSIGNMENT_ACTION_ID,
3502: p_tax_unit_id,
3506: NULL,
3507: NULL,
3508: NULL,
3509: 'TRUE');
3510: hr_utility_trace('YTD ISR Sub for Empl Bal Sub '||to_char(ln_ISR_subsidy_empl_ytd));
3511: /*IF nvl(ln_bal_ISR_non_wrkd_days,0) > 0 AND nvl(ln_bal_ISR_non_wrkd_days,0) < ln_days_in_a_period THEN
3512: tot_subsidy_empl := ln_ISR_subsidy_empl_ytd + ln_pro_sub_emp;
3513: ELSE*/
3514: tot_subsidy_empl := ln_ISR_subsidy_empl_ytd + p_credit_to_salary;
3512: tot_subsidy_empl := ln_ISR_subsidy_empl_ytd + ln_pro_sub_emp;
3513: ELSE*/
3514: tot_subsidy_empl := ln_ISR_subsidy_empl_ytd + p_credit_to_salary;
3515: --END IF;
3516: hr_utility_trace('Total Subsidy for Employment '||to_char(tot_subsidy_empl));
3517: OPEN csr_def_bal_id ('ISR Non Working Days',lv_dimension_type);
3518: FETCH csr_def_bal_id INTO ln_def_bal_id;
3519: CLOSE csr_def_bal_id;
3520:
3517: OPEN csr_def_bal_id ('ISR Non Working Days',lv_dimension_type);
3518: FETCH csr_def_bal_id INTO ln_def_bal_id;
3519: CLOSE csr_def_bal_id;
3520:
3521: hr_utility_trace('ISR Non Working Days def bal id '||to_char(ln_def_bal_id));
3522: ln_bal_ISR_non_wrkd_days_ytd := pay_balance_pkg.get_value(ln_def_bal_id,
3523: p_assignment_action_id,
3524: p_tax_unit_id,
3525: NULL,
3528: NULL,
3529: NULL,
3530: NULL,
3531: 'TRUE');
3532: hr_utility_trace('YTD ISR Non Working Days Bal '||to_char(ln_bal_ISR_non_wrkd_days_ytd));
3533: tot_ISR_non_wrkd_days := ln_bal_ISR_non_wrkd_days_ytd;
3534: hr_utility_trace('Total Non Worked days '||to_char(tot_ISR_non_wrkd_days));
3535:
3536: IF p_run_type = 'ADJTAX' THEN
3530: NULL,
3531: 'TRUE');
3532: hr_utility_trace('YTD ISR Non Working Days Bal '||to_char(ln_bal_ISR_non_wrkd_days_ytd));
3533: tot_ISR_non_wrkd_days := ln_bal_ISR_non_wrkd_days_ytd;
3534: hr_utility_trace('Total Non Worked days '||to_char(tot_ISR_non_wrkd_days));
3535:
3536: IF p_run_type = 'ADJTAX' THEN
3537: OPEN csr_get_no_of_days_in_ytd(ln_payroll_id);
3538: FETCH csr_get_no_of_days_in_ytd INTO ln_days_in_ytd, ld_period_start_date, ld_period_end_date;
3536: IF p_run_type = 'ADJTAX' THEN
3537: OPEN csr_get_no_of_days_in_ytd(ln_payroll_id);
3538: FETCH csr_get_no_of_days_in_ytd INTO ln_days_in_ytd, ld_period_start_date, ld_period_end_date;
3539: CLOSE csr_get_no_of_days_in_ytd;
3540: hr_utility_trace('Period Start date is'||to_char(ld_period_start_date));
3541: hr_utility_trace('Period End date is'||to_char(ld_period_end_date));
3542:
3543: OPEN csr_get_act_hire_date;
3544: FETCH csr_get_act_hire_date INTO ld_act_hire_date;
3537: OPEN csr_get_no_of_days_in_ytd(ln_payroll_id);
3538: FETCH csr_get_no_of_days_in_ytd INTO ln_days_in_ytd, ld_period_start_date, ld_period_end_date;
3539: CLOSE csr_get_no_of_days_in_ytd;
3540: hr_utility_trace('Period Start date is'||to_char(ld_period_start_date));
3541: hr_utility_trace('Period End date is'||to_char(ld_period_end_date));
3542:
3543: OPEN csr_get_act_hire_date;
3544: FETCH csr_get_act_hire_date INTO ld_act_hire_date;
3545: CLOSE csr_get_act_hire_date;
3543: OPEN csr_get_act_hire_date;
3544: FETCH csr_get_act_hire_date INTO ld_act_hire_date;
3545: CLOSE csr_get_act_hire_date;
3546:
3547: hr_utility_trace('The actual Hire Date is'||to_char(ld_act_hire_date));
3548:
3549: IF ld_act_hire_date >ld_period_start_date THEN
3550: ln_days_in_ytd := ld_period_end_date - ld_act_hire_date +1;
3551: hr_utility_trace('Total days in YTD'||to_char(ln_days_in_ytd));
3547: hr_utility_trace('The actual Hire Date is'||to_char(ld_act_hire_date));
3548:
3549: IF ld_act_hire_date >ld_period_start_date THEN
3550: ln_days_in_ytd := ld_period_end_date - ld_act_hire_date +1;
3551: hr_utility_trace('Total days in YTD'||to_char(ln_days_in_ytd));
3552: END IF;
3553:
3554: IF ln_days_in_ytd > 365 THEN
3555: ln_days_in_ytd := 365;
3558: ELSIF p_run_type = 'MTDTAXADJ' THEN
3559: OPEN csr_get_no_of_days_in_mtd (ln_payroll_id);
3560: FETCH csr_get_no_of_days_in_mtd INTO ln_days_in_ytd , ld_period_end_date, ld_period_start_date;
3561: CLOSE csr_get_no_of_days_in_mtd;
3562: hr_utility_trace('Period Start date is'||to_char(ld_period_start_date));
3563: hr_utility_trace('Period End date is'||to_char(ld_period_end_date));
3564:
3565: OPEN csr_get_act_hire_date;
3566: FETCH csr_get_act_hire_date INTO ld_act_hire_date;
3559: OPEN csr_get_no_of_days_in_mtd (ln_payroll_id);
3560: FETCH csr_get_no_of_days_in_mtd INTO ln_days_in_ytd , ld_period_end_date, ld_period_start_date;
3561: CLOSE csr_get_no_of_days_in_mtd;
3562: hr_utility_trace('Period Start date is'||to_char(ld_period_start_date));
3563: hr_utility_trace('Period End date is'||to_char(ld_period_end_date));
3564:
3565: OPEN csr_get_act_hire_date;
3566: FETCH csr_get_act_hire_date INTO ld_act_hire_date;
3567: CLOSE csr_get_act_hire_date;
3565: OPEN csr_get_act_hire_date;
3566: FETCH csr_get_act_hire_date INTO ld_act_hire_date;
3567: CLOSE csr_get_act_hire_date;
3568:
3569: hr_utility_trace('The actual Hire Date is'||to_char(ld_act_hire_date));
3570:
3571: IF ld_act_hire_date > ld_period_start_date THEN
3572: ln_days_in_ytd := ld_period_end_date - ld_act_hire_date +1;
3573: hr_utility_trace('Total days in YTD'||to_char(ln_days_in_ytd));
3569: hr_utility_trace('The actual Hire Date is'||to_char(ld_act_hire_date));
3570:
3571: IF ld_act_hire_date > ld_period_start_date THEN
3572: ln_days_in_ytd := ld_period_end_date - ld_act_hire_date +1;
3573: hr_utility_trace('Total days in YTD'||to_char(ln_days_in_ytd));
3574: END IF;
3575: END IF;
3576:
3577: ln_wrkd_days_in_adj_period := ln_days_in_ytd - tot_ISR_non_wrkd_days;
3574: END IF;
3575: END IF;
3576:
3577: ln_wrkd_days_in_adj_period := ln_days_in_ytd - tot_ISR_non_wrkd_days;
3578: hr_utility_trace('Total Worked days YTD '||to_char(ln_wrkd_days_in_adj_period));
3579:
3580: IF p_run_type = 'ADJTAX' THEN
3581: ln_proration_fac := ln_wrkd_days_in_adj_period/365;
3582: hr_utility_trace('Proration factor for YTD adj'||to_char(ln_proration_fac));
3578: hr_utility_trace('Total Worked days YTD '||to_char(ln_wrkd_days_in_adj_period));
3579:
3580: IF p_run_type = 'ADJTAX' THEN
3581: ln_proration_fac := ln_wrkd_days_in_adj_period/365;
3582: hr_utility_trace('Proration factor for YTD adj'||to_char(ln_proration_fac));
3583:
3584: ELSIF p_run_type = 'MTDTAXADJ' THEN
3585: ln_no_days_in_month := LAST_DAY(ld_period_end_date) - TRUNC(ld_period_end_date, 'MONTH') +1;
3586: hr_utility_trace('The total number of days in the adjustment month'||to_char(ln_no_days_in_month));
3582: hr_utility_trace('Proration factor for YTD adj'||to_char(ln_proration_fac));
3583:
3584: ELSIF p_run_type = 'MTDTAXADJ' THEN
3585: ln_no_days_in_month := LAST_DAY(ld_period_end_date) - TRUNC(ld_period_end_date, 'MONTH') +1;
3586: hr_utility_trace('The total number of days in the adjustment month'||to_char(ln_no_days_in_month));
3587: ln_proration_fac := (ln_wrkd_days_in_adj_period * (30.4/ln_no_days_in_month))/30.4;
3588: hr_utility_trace('Proration factor for MTD adj'||to_char(ln_proration_fac));
3589: END IF;
3590:
3584: ELSIF p_run_type = 'MTDTAXADJ' THEN
3585: ln_no_days_in_month := LAST_DAY(ld_period_end_date) - TRUNC(ld_period_end_date, 'MONTH') +1;
3586: hr_utility_trace('The total number of days in the adjustment month'||to_char(ln_no_days_in_month));
3587: ln_proration_fac := (ln_wrkd_days_in_adj_period * (30.4/ln_no_days_in_month))/30.4;
3588: hr_utility_trace('Proration factor for MTD adj'||to_char(ln_proration_fac));
3589: END IF;
3590:
3591: OPEN csr_get_table_id (lv_proration_table);
3592: FETCH csr_get_table_id INTO ln_table_id;
3591: OPEN csr_get_table_id (lv_proration_table);
3592: FETCH csr_get_table_id INTO ln_table_id;
3593: CLOSE csr_get_table_id;
3594:
3595: hr_utility_trace('Table Id is'||to_char(ln_table_id));
3596:
3597: OPEN csr_get_table_value (ln_table_id, ln_proration_fac, 'FIXED RATE', tot_sub_basis);
3598: FETCH csr_get_table_value INTO ln_fixed_rate_ytd;
3599: CLOSE csr_get_table_value;
3596:
3597: OPEN csr_get_table_value (ln_table_id, ln_proration_fac, 'FIXED RATE', tot_sub_basis);
3598: FETCH csr_get_table_value INTO ln_fixed_rate_ytd;
3599: CLOSE csr_get_table_value;
3600: hr_utility_trace('Fixed Rate is '||to_char(ln_fixed_rate_ytd));
3601:
3602: OPEN csr_get_table_value (ln_table_id, ln_proration_fac, 'LOWER BOUND', tot_sub_basis);
3603: FETCH csr_get_table_value INTO ln_lower_bound_ytd;
3604: CLOSE csr_get_table_value;
3601:
3602: OPEN csr_get_table_value (ln_table_id, ln_proration_fac, 'LOWER BOUND', tot_sub_basis);
3603: FETCH csr_get_table_value INTO ln_lower_bound_ytd;
3604: CLOSE csr_get_table_value;
3605: hr_utility_trace('Lower Bound value is '||to_char(ln_lower_bound_ytd));
3606:
3607: OPEN csr_get_table_value (ln_table_id, ln_proration_fac, 'MARGINAL RATE', tot_sub_basis);
3608: FETCH csr_get_table_value INTO ln_marginal_rate_ytd;
3609: CLOSE csr_get_table_value;
3608: FETCH csr_get_table_value INTO ln_marginal_rate_ytd;
3609: CLOSE csr_get_table_value;
3610:
3611: ln_marginal_rate_ytd := ln_marginal_rate_ytd/ln_proration_fac;
3612: hr_utility_trace('Marginal Rate is '||to_char(ln_marginal_rate_ytd));
3613:
3614: ln_taxable_subject_ytd := tot_sub_basis-ln_lower_bound_ytd;
3615: hr_utility_trace('The Difference amount is '||to_char(ln_taxable_subject_ytd));
3616:
3611: ln_marginal_rate_ytd := ln_marginal_rate_ytd/ln_proration_fac;
3612: hr_utility_trace('Marginal Rate is '||to_char(ln_marginal_rate_ytd));
3613:
3614: ln_taxable_subject_ytd := tot_sub_basis-ln_lower_bound_ytd;
3615: hr_utility_trace('The Difference amount is '||to_char(ln_taxable_subject_ytd));
3616:
3617: ln_marginal_tax_ytd := ln_taxable_subject_ytd * (ln_marginal_rate_ytd/100);
3618: hr_utility_trace('The Marginal Tax '||to_char(ln_marginal_tax_ytd));
3619:
3614: ln_taxable_subject_ytd := tot_sub_basis-ln_lower_bound_ytd;
3615: hr_utility_trace('The Difference amount is '||to_char(ln_taxable_subject_ytd));
3616:
3617: ln_marginal_tax_ytd := ln_taxable_subject_ytd * (ln_marginal_rate_ytd/100);
3618: hr_utility_trace('The Marginal Tax '||to_char(ln_marginal_tax_ytd));
3619:
3620: ln_total_tax_ytd := ln_marginal_tax_ytd + ln_fixed_rate_ytd;
3621: hr_utility_trace('The Total Tax is '||to_char(ln_total_tax_ytd));
3622:
3617: ln_marginal_tax_ytd := ln_taxable_subject_ytd * (ln_marginal_rate_ytd/100);
3618: hr_utility_trace('The Marginal Tax '||to_char(ln_marginal_tax_ytd));
3619:
3620: ln_total_tax_ytd := ln_marginal_tax_ytd + ln_fixed_rate_ytd;
3621: hr_utility_trace('The Total Tax is '||to_char(ln_total_tax_ytd));
3622:
3623: p_isr_calculated := ln_total_tax_ytd - ln_ISR_calculated_ytd;
3624:
3625: ln_net_tax_ytd := ln_total_tax_ytd - tot_subsidy_empl;
3622:
3623: p_isr_calculated := ln_total_tax_ytd - ln_ISR_calculated_ytd;
3624:
3625: ln_net_tax_ytd := ln_total_tax_ytd - tot_subsidy_empl;
3626: hr_utility_trace('The Net Tax is '||to_char(ln_net_tax_ytd));
3627:
3628: ln_adjusted_tax_ytd := ln_net_tax_ytd - ln_ISR_tax_to_charge_ytd;
3629: hr_utility_trace('The Adjusted Tax is '||to_char(ln_adjusted_tax_ytd));
3630:
3625: ln_net_tax_ytd := ln_total_tax_ytd - tot_subsidy_empl;
3626: hr_utility_trace('The Net Tax is '||to_char(ln_net_tax_ytd));
3627:
3628: ln_adjusted_tax_ytd := ln_net_tax_ytd - ln_ISR_tax_to_charge_ytd;
3629: hr_utility_trace('The Adjusted Tax is '||to_char(ln_adjusted_tax_ytd));
3630:
3631: --ln_isr_withheld := ROUND(ln_adjusted_tax_ytd,2);
3632:
3633:
3674: -- End Fix for Bug 13905014
3675:
3676: END IF;
3677:
3678: hr_utility_trace('p_credit_to_salary_paid: '||p_credit_to_salary_paid);
3679: hr_utility_trace('ln_isr_withheld Final: '||ln_isr_withheld);
3680: hr_utility_trace('Leaving '||l_proc_name);
3681:
3682: RETURN ln_isr_withheld;
3675:
3676: END IF;
3677:
3678: hr_utility_trace('p_credit_to_salary_paid: '||p_credit_to_salary_paid);
3679: hr_utility_trace('ln_isr_withheld Final: '||ln_isr_withheld);
3680: hr_utility_trace('Leaving '||l_proc_name);
3681:
3682: RETURN ln_isr_withheld;
3683:
3676: END IF;
3677:
3678: hr_utility_trace('p_credit_to_salary_paid: '||p_credit_to_salary_paid);
3679: hr_utility_trace('ln_isr_withheld Final: '||ln_isr_withheld);
3680: hr_utility_trace('Leaving '||l_proc_name);
3681:
3682: RETURN ln_isr_withheld;
3683:
3684: EXCEPTION
3682: RETURN ln_isr_withheld;
3683:
3684: EXCEPTION
3685: WHEN OTHERS THEN
3686: hr_utility_trace('Exception in '||l_proc_name||': '||SQLERRM);
3687: RAISE;
3688: END CALCULATE_ISR_TAX;
3689:
3690: /****************************************************************************
3715: and to_char(p_date_earned,'yyyymmdd') = to_char(end_date,'yyyymmdd');
3716:
3717: BEGIN
3718:
3719: hr_utility_trace('Entering ..CONVERT_MONTHLY_TO_PERIOD');
3720: hr_utility_trace('p_periodic_value: ' ||p_periodic_value);
3721:
3722: open csr_get_period_count_in_month;
3723: fetch csr_get_period_count_in_month into ln_days_in_a_period;
3716:
3717: BEGIN
3718:
3719: hr_utility_trace('Entering ..CONVERT_MONTHLY_TO_PERIOD');
3720: hr_utility_trace('p_periodic_value: ' ||p_periodic_value);
3721:
3722: open csr_get_period_count_in_month;
3723: fetch csr_get_period_count_in_month into ln_days_in_a_period;
3724: close csr_get_period_count_in_month;
3724: close csr_get_period_count_in_month;
3725:
3726: ln_periodic_value := (p_periodic_value/30.4) * ln_days_in_a_period;
3727:
3728: hr_utility_trace('ln_periodic_value: ' ||ln_periodic_value);
3729: hr_utility_trace('Leaving ..CONVERT_MONTHLY_TO_PERIOD');
3730:
3731: RETURN ln_periodic_value;
3732:
3725:
3726: ln_periodic_value := (p_periodic_value/30.4) * ln_days_in_a_period;
3727:
3728: hr_utility_trace('ln_periodic_value: ' ||ln_periodic_value);
3729: hr_utility_trace('Leaving ..CONVERT_MONTHLY_TO_PERIOD');
3730:
3731: RETURN ln_periodic_value;
3732:
3733: END CONVERT_MONTHLY_TO_PERIOD; /*
3754: lv_period_type pay_all_payrolls_f.period_type%TYPE;
3755:
3756: BEGIN
3757:
3758: hr_utility_trace('Entering ..CONVERT_INTO_MONTHLY_SALARY');
3759: hr_utility_trace('p_periodic_earnings: ' ||p_periodic_earnings);
3760:
3761: SELECT period_type
3762: INTO lv_period_type
3755:
3756: BEGIN
3757:
3758: hr_utility_trace('Entering ..CONVERT_INTO_MONTHLY_SALARY');
3759: hr_utility_trace('p_periodic_earnings: ' ||p_periodic_earnings);
3760:
3761: SELECT period_type
3762: INTO lv_period_type
3763: FROM pay_all_payrolls_f ppf,
3795:
3796:
3797: END IF;
3798:
3799: hr_utility_trace('ln_periodic_earnings: ' ||ln_periodic_earnings);
3800: hr_utility_trace('Leaving ..CONVERT_INTO_MONTHLY_SALARY');
3801:
3802: RETURN ln_periodic_earnings;
3803:
3796:
3797: END IF;
3798:
3799: hr_utility_trace('ln_periodic_earnings: ' ||ln_periodic_earnings);
3800: hr_utility_trace('Leaving ..CONVERT_INTO_MONTHLY_SALARY');
3801:
3802: RETURN ln_periodic_earnings;
3803:
3804: END CONVERT_INTO_MONTHLY_SALARY;
3854: l_gross_earning:=0;
3855: l_capping_value:=0;
3856: l_capping_value:='$Sys_Def$';
3857:
3858: hr_utility_trace('Entering ..CHECK_EE_SAL_CRITERIA');
3859: OPEN c_defined_balance_id('Gross Earnings','_PER_YTD');
3860: FETCH c_defined_balance_id INTO l_bal_defined_id;
3861: CLOSE c_defined_balance_id;
3862:
3864: FETCH c_assignment_action_id INTO l_assignment_action_id;
3865: CLOSE c_assignment_action_id;
3866:
3867: IF(l_assignment_action_id IS NULL) THEN
3868: hr_utility_trace('Leaving ..CHECK_EE_SAL_CRITERIA');
3869: RETURN 'N';
3870: END IF;
3871:
3872: l_pkg_value:=pay_mx_tax_functions.get_mx_tax_info
3896: l_flag :='N';
3897: ELSIF (l_gross_earning <> 0) THEN
3898: l_flag :='Y';
3899: END IF;
3900: hr_utility_trace('l_flag: ' ||l_flag);
3901: hr_utility_trace('Leaving ..CHECK_EE_SAL_CRITERIA');
3902: RETURN l_flag;
3903:
3904: END CHECK_EE_SAL_CRITERIA;
3897: ELSIF (l_gross_earning <> 0) THEN
3898: l_flag :='Y';
3899: END IF;
3900: hr_utility_trace('l_flag: ' ||l_flag);
3901: hr_utility_trace('Leaving ..CHECK_EE_SAL_CRITERIA');
3902: RETURN l_flag;
3903:
3904: END CHECK_EE_SAL_CRITERIA;
3905:
3974: l_prev_term_date DATE;
3975:
3976: BEGIN
3977: l_proc_name := g_proc_name ||'CHECK_EE_EMPLOYMENT_CRITERIA';
3978: hr_utility_trace('Entering '||l_proc_name);
3979: hr_utility_trace('P_CTX_ASSIGNMENT_ID = ' || p_ctx_assignment_id);
3980:
3981: l_flag :='N';
3982: l_start_date := NULL;
3975:
3976: BEGIN
3977: l_proc_name := g_proc_name ||'CHECK_EE_EMPLOYMENT_CRITERIA';
3978: hr_utility_trace('Entering '||l_proc_name);
3979: hr_utility_trace('P_CTX_ASSIGNMENT_ID = ' || p_ctx_assignment_id);
3980:
3981: l_flag :='N';
3982: l_start_date := NULL;
3983:
3987: ln_person_id;
3988: CLOSE csr_get_dates;
3989:
3990:
3991: HR_UTILITY.trace('l_start_date: '||l_start_date);
3992: HR_UTILITY.trace('l_end_date: '||l_end_date);
3993:
3994: OPEN c_get_hire_date(ln_person_id,P_CTX_DATE_EARNED);
3995: FETCH c_get_hire_date INTO l_hire_date, l_termination_date;
3988: CLOSE csr_get_dates;
3989:
3990:
3991: HR_UTILITY.trace('l_start_date: '||l_start_date);
3992: HR_UTILITY.trace('l_end_date: '||l_end_date);
3993:
3994: OPEN c_get_hire_date(ln_person_id,P_CTX_DATE_EARNED);
3995: FETCH c_get_hire_date INTO l_hire_date, l_termination_date;
3996: CLOSE c_get_hire_date;
3994: OPEN c_get_hire_date(ln_person_id,P_CTX_DATE_EARNED);
3995: FETCH c_get_hire_date INTO l_hire_date, l_termination_date;
3996: CLOSE c_get_hire_date;
3997:
3998: HR_UTILITY.trace('l_hire_date: '||l_hire_date);
3999: HR_UTILITY.trace('l_termination_date: '||l_termination_date);
4000:
4001: IF l_hire_date > l_start_date AND l_termination_date >= l_end_date THEN
4002: /*Added for bug 7604298 to pick up the re-hire employee when re-hire happens in next day*/
3995: FETCH c_get_hire_date INTO l_hire_date, l_termination_date;
3996: CLOSE c_get_hire_date;
3997:
3998: HR_UTILITY.trace('l_hire_date: '||l_hire_date);
3999: HR_UTILITY.trace('l_termination_date: '||l_termination_date);
4000:
4001: IF l_hire_date > l_start_date AND l_termination_date >= l_end_date THEN
4002: /*Added for bug 7604298 to pick up the re-hire employee when re-hire happens in next day*/
4003: HR_UTILITY.trace('About to check the re-hire condition ');
3999: HR_UTILITY.trace('l_termination_date: '||l_termination_date);
4000:
4001: IF l_hire_date > l_start_date AND l_termination_date >= l_end_date THEN
4002: /*Added for bug 7604298 to pick up the re-hire employee when re-hire happens in next day*/
4003: HR_UTILITY.trace('About to check the re-hire condition ');
4004: OPEN c_get_prev_term_date(ln_person_id,P_CTX_DATE_EARNED);
4005: FETCH c_get_prev_term_date INTO l_prev_term_date;
4006: CLOSE c_get_prev_term_date;
4007: HR_UTILITY.trace('l_prev_term_date: '||l_prev_term_date);
4003: HR_UTILITY.trace('About to check the re-hire condition ');
4004: OPEN c_get_prev_term_date(ln_person_id,P_CTX_DATE_EARNED);
4005: FETCH c_get_prev_term_date INTO l_prev_term_date;
4006: CLOSE c_get_prev_term_date;
4007: HR_UTILITY.trace('l_prev_term_date: '||l_prev_term_date);
4008: IF (l_prev_term_date IS NOT NULL) AND ((l_prev_term_date +1) = l_hire_date) THEN
4009: hr_utility_trace('Person re-hired in the next day ');
4010: RETURN ('Y');
4011: END IF;
4005: FETCH c_get_prev_term_date INTO l_prev_term_date;
4006: CLOSE c_get_prev_term_date;
4007: HR_UTILITY.trace('l_prev_term_date: '||l_prev_term_date);
4008: IF (l_prev_term_date IS NOT NULL) AND ((l_prev_term_date +1) = l_hire_date) THEN
4009: hr_utility_trace('Person re-hired in the next day ');
4010: RETURN ('Y');
4011: END IF;
4012:
4013: hr_utility_trace('Person hired after ' || l_start_date);
4009: hr_utility_trace('Person re-hired in the next day ');
4010: RETURN ('Y');
4011: END IF;
4012:
4013: hr_utility_trace('Person hired after ' || l_start_date);
4014: hr_utility_trace('Leaving '||l_proc_name);
4015: RETURN ('N');
4016: END IF;
4017:
4010: RETURN ('Y');
4011: END IF;
4012:
4013: hr_utility_trace('Person hired after ' || l_start_date);
4014: hr_utility_trace('Leaving '||l_proc_name);
4015: RETURN ('N');
4016: END IF;
4017:
4018: IF l_termination_date < l_end_date THEN
4015: RETURN ('N');
4016: END IF;
4017:
4018: IF l_termination_date < l_end_date THEN
4019: hr_utility_trace('Person terminated before ' || l_end_date );
4020: hr_utility_trace('Leaving '||l_proc_name);
4021: RETURN ('N');
4022: END IF;
4023:
4016: END IF;
4017:
4018: IF l_termination_date < l_end_date THEN
4019: hr_utility_trace('Person terminated before ' || l_end_date );
4020: hr_utility_trace('Leaving '||l_proc_name);
4021: RETURN ('N');
4022: END IF;
4023:
4024: IF l_start_date IS NOT NULL THEN
4025: OPEN csr_get_form37_status(l_start_date,l_end_date);
4026: FETCH csr_get_form37_status INTO l_flag;
4027: IF csr_get_form37_status%NOTFOUND THEN
4028: CLOSE csr_get_form37_status;
4029: hr_utility_trace('Leaving '||l_proc_name);
4030: RETURN 'Y';
4031: END IF;
4032: CLOSE csr_get_form37_status;
4033: END IF;
4031: END IF;
4032: CLOSE csr_get_form37_status;
4033: END IF;
4034:
4035: hr_utility_trace('l_flag: ' ||l_flag);
4036: hr_utility_trace('Leaving '||l_proc_name);
4037:
4038: RETURN (l_flag);
4039:
4032: CLOSE csr_get_form37_status;
4033: END IF;
4034:
4035: hr_utility_trace('l_flag: ' ||l_flag);
4036: hr_utility_trace('Leaving '||l_proc_name);
4037:
4038: RETURN (l_flag);
4039:
4040: END CHECK_EE_EMPLOYMENT_CRITERIA;
4077:
4078: BEGIN
4079:
4080: l_flag :='N';
4081: hr_utility_trace('Entering ..IS_ASG_EXEMPT_FROM_ISR');
4082: OPEN get_isr_entry;
4083: FETCH get_isr_entry INTO l_flag;
4084: IF get_isr_entry%NOTFOUND THEN
4085: CLOSE get_isr_entry;
4082: OPEN get_isr_entry;
4083: FETCH get_isr_entry INTO l_flag;
4084: IF get_isr_entry%NOTFOUND THEN
4085: CLOSE get_isr_entry;
4086: hr_utility_trace('Leaving ..IS_ASG_EXEMPT_FROM_ISR');
4087: RETURN 'N';
4088: END IF;
4089: CLOSE get_isr_entry;
4090:
4087: RETURN 'N';
4088: END IF;
4089: CLOSE get_isr_entry;
4090:
4091: hr_utility_trace('l_flag: ' ||l_flag);
4092: hr_utility_trace('Leaving ..IS_ASG_EXEMPT_FROM_ISR');
4093:
4094: RETURN (l_flag);
4095:
4088: END IF;
4089: CLOSE get_isr_entry;
4090:
4091: hr_utility_trace('l_flag: ' ||l_flag);
4092: hr_utility_trace('Leaving ..IS_ASG_EXEMPT_FROM_ISR');
4093:
4094: RETURN (l_flag);
4095:
4096: END IS_ASG_EXEMPT_FROM_ISR;
4130:
4131: BEGIN
4132:
4133: l_flag:='N';
4134: hr_utility_trace('Entering ..IS_PER_EXEMPT_FROM_ADJ');
4135: OPEN get_person_id;
4136: FETCH get_person_id INTO l_person_id;
4137: CLOSE get_person_id;
4138:
4139: OPEN get_exempt_adjustment(l_person_id);
4140: FETCH get_exempt_adjustment INTO l_exempt_adj;
4141: IF get_exempt_adjustment%NOTFOUND THEN
4142: CLOSE get_exempt_adjustment;
4143: hr_utility_trace('Leaving ..IS_PER_EXEMPT_FROM_ADJ');
4144: RETURN 'N';
4145: END IF;
4146: CLOSE get_exempt_adjustment;
4147:
4150: ELSE
4151: l_flag:='N';
4152: END IF;
4153:
4154: hr_utility_trace('l_flag: ' ||l_flag);
4155: hr_utility_trace('Leaving ..IS_PER_EXEMPT_FROM_ADJ');
4156:
4157: RETURN (l_flag);
4158:
4151: l_flag:='N';
4152: END IF;
4153:
4154: hr_utility_trace('l_flag: ' ||l_flag);
4155: hr_utility_trace('Leaving ..IS_PER_EXEMPT_FROM_ADJ');
4156:
4157: RETURN (l_flag);
4158:
4159: END IS_PER_EXEMPT_FROM_ADJ;
4217: l_min_wage number;
4218:
4219: BEGIN
4220:
4221: hr_utility_trace('Economy Zone '||P_ECON_ZONE);
4222: OPEN get_min_wage;
4223: FETCH get_min_wage INTO l_min_wage;
4224: CLOSE get_min_wage;
4225:
4274: order by end_date;
4275:
4276: BEGIN
4277:
4278: hr_utility_trace('Entering ..CONVERT_INTO_MONTHLY_AVG_SAL');
4279: hr_utility_trace('p_subject_earnings: ' ||p_subject_earnings);
4280: hr_utility_trace('p_first_pay_date: ' ||to_char(p_first_pay_date));
4281: hr_utility_trace('p_hire_date: ' ||to_char(p_hire_date));
4282: hr_utility_trace('p_date_earned: ' ||to_char(P_DATE_EARNED));
4275:
4276: BEGIN
4277:
4278: hr_utility_trace('Entering ..CONVERT_INTO_MONTHLY_AVG_SAL');
4279: hr_utility_trace('p_subject_earnings: ' ||p_subject_earnings);
4280: hr_utility_trace('p_first_pay_date: ' ||to_char(p_first_pay_date));
4281: hr_utility_trace('p_hire_date: ' ||to_char(p_hire_date));
4282: hr_utility_trace('p_date_earned: ' ||to_char(P_DATE_EARNED));
4283: hr_utility_trace('p_payroll_id: ' ||p_payroll_id);
4276: BEGIN
4277:
4278: hr_utility_trace('Entering ..CONVERT_INTO_MONTHLY_AVG_SAL');
4279: hr_utility_trace('p_subject_earnings: ' ||p_subject_earnings);
4280: hr_utility_trace('p_first_pay_date: ' ||to_char(p_first_pay_date));
4281: hr_utility_trace('p_hire_date: ' ||to_char(p_hire_date));
4282: hr_utility_trace('p_date_earned: ' ||to_char(P_DATE_EARNED));
4283: hr_utility_trace('p_payroll_id: ' ||p_payroll_id);
4284: open csr_get_period_count_in_month;
4277:
4278: hr_utility_trace('Entering ..CONVERT_INTO_MONTHLY_AVG_SAL');
4279: hr_utility_trace('p_subject_earnings: ' ||p_subject_earnings);
4280: hr_utility_trace('p_first_pay_date: ' ||to_char(p_first_pay_date));
4281: hr_utility_trace('p_hire_date: ' ||to_char(p_hire_date));
4282: hr_utility_trace('p_date_earned: ' ||to_char(P_DATE_EARNED));
4283: hr_utility_trace('p_payroll_id: ' ||p_payroll_id);
4284: open csr_get_period_count_in_month;
4285: loop
4278: hr_utility_trace('Entering ..CONVERT_INTO_MONTHLY_AVG_SAL');
4279: hr_utility_trace('p_subject_earnings: ' ||p_subject_earnings);
4280: hr_utility_trace('p_first_pay_date: ' ||to_char(p_first_pay_date));
4281: hr_utility_trace('p_hire_date: ' ||to_char(p_hire_date));
4282: hr_utility_trace('p_date_earned: ' ||to_char(P_DATE_EARNED));
4283: hr_utility_trace('p_payroll_id: ' ||p_payroll_id);
4284: open csr_get_period_count_in_month;
4285: loop
4286: fetch csr_get_period_count_in_month into ln_row_count,
4279: hr_utility_trace('p_subject_earnings: ' ||p_subject_earnings);
4280: hr_utility_trace('p_first_pay_date: ' ||to_char(p_first_pay_date));
4281: hr_utility_trace('p_hire_date: ' ||to_char(p_hire_date));
4282: hr_utility_trace('p_date_earned: ' ||to_char(P_DATE_EARNED));
4283: hr_utility_trace('p_payroll_id: ' ||p_payroll_id);
4284: open csr_get_period_count_in_month;
4285: loop
4286: fetch csr_get_period_count_in_month into ln_row_count,
4287: lv_end_date,
4288: lv_start_date,
4289: ln_time_period_id;
4290: exit when csr_get_period_count_in_month%NOTFOUND;
4291:
4292: hr_utility_trace('ln_row_count: ld_end_date' ||
4293: to_char(ln_row_count) ||' -- '||lv_end_date);
4294:
4295: if to_char(P_DATE_EARNED,'yyyymmdd') <= lv_end_date and
4296: to_char(P_DATE_EARNED,'yyyymmdd') >= lv_start_date then
4295: if to_char(P_DATE_EARNED,'yyyymmdd') <= lv_end_date and
4296: to_char(P_DATE_EARNED,'yyyymmdd') >= lv_start_date then
4297: ln_subject_earnings := p_subject_earnings * ln_row_count;
4298: p_period_days := ln_row_count;
4299: hr_utility_trace('p_period_days: ' ||ln_row_count);
4300: end if;
4301:
4302: end loop;
4303: close csr_get_period_count_in_month;
4301:
4302: end loop;
4303: close csr_get_period_count_in_month;
4304:
4305: hr_utility_trace('ln_subject_earnings: ' ||ln_subject_earnings);
4306: hr_utility_trace('Leaving ..CONVERT_INTO_MONTHLY_AVG_SAL');
4307:
4308: RETURN ln_subject_earnings;
4309:
4302: end loop;
4303: close csr_get_period_count_in_month;
4304:
4305: hr_utility_trace('ln_subject_earnings: ' ||ln_subject_earnings);
4306: hr_utility_trace('Leaving ..CONVERT_INTO_MONTHLY_AVG_SAL');
4307:
4308: RETURN ln_subject_earnings;
4309:
4310: END CONVERT_INTO_MONTHLY_AVG_SAL;
4381:
4382: t_pro_table_values pro_table_val; -- Bug 14094610
4383:
4384: BEGIN
4385: hr_utility_trace('p_bus_grp_id: ' ||p_bus_grp_id);
4386: hr_utility_trace('p_table_id: ' ||to_char(p_table_id));
4387: hr_utility_trace('p_column_name: ' ||p_column_name);
4388: hr_utility_trace('p_eff_date: ' ||p_eff_date);
4389: hr_utility_trace('p_row_value: ' ||to_char(p_row_value));
4382: t_pro_table_values pro_table_val; -- Bug 14094610
4383:
4384: BEGIN
4385: hr_utility_trace('p_bus_grp_id: ' ||p_bus_grp_id);
4386: hr_utility_trace('p_table_id: ' ||to_char(p_table_id));
4387: hr_utility_trace('p_column_name: ' ||p_column_name);
4388: hr_utility_trace('p_eff_date: ' ||p_eff_date);
4389: hr_utility_trace('p_row_value: ' ||to_char(p_row_value));
4390: hr_utility_trace('p_period_days: ' ||to_char(p_period_days));
4383:
4384: BEGIN
4385: hr_utility_trace('p_bus_grp_id: ' ||p_bus_grp_id);
4386: hr_utility_trace('p_table_id: ' ||to_char(p_table_id));
4387: hr_utility_trace('p_column_name: ' ||p_column_name);
4388: hr_utility_trace('p_eff_date: ' ||p_eff_date);
4389: hr_utility_trace('p_row_value: ' ||to_char(p_row_value));
4390: hr_utility_trace('p_period_days: ' ||to_char(p_period_days));
4391:
4384: BEGIN
4385: hr_utility_trace('p_bus_grp_id: ' ||p_bus_grp_id);
4386: hr_utility_trace('p_table_id: ' ||to_char(p_table_id));
4387: hr_utility_trace('p_column_name: ' ||p_column_name);
4388: hr_utility_trace('p_eff_date: ' ||p_eff_date);
4389: hr_utility_trace('p_row_value: ' ||to_char(p_row_value));
4390: hr_utility_trace('p_period_days: ' ||to_char(p_period_days));
4391:
4392: t_pro_table_values.delete;
4385: hr_utility_trace('p_bus_grp_id: ' ||p_bus_grp_id);
4386: hr_utility_trace('p_table_id: ' ||to_char(p_table_id));
4387: hr_utility_trace('p_column_name: ' ||p_column_name);
4388: hr_utility_trace('p_eff_date: ' ||p_eff_date);
4389: hr_utility_trace('p_row_value: ' ||to_char(p_row_value));
4390: hr_utility_trace('p_period_days: ' ||to_char(p_period_days));
4391:
4392: t_pro_table_values.delete;
4393: i := 0;
4386: hr_utility_trace('p_table_id: ' ||to_char(p_table_id));
4387: hr_utility_trace('p_column_name: ' ||p_column_name);
4388: hr_utility_trace('p_eff_date: ' ||p_eff_date);
4389: hr_utility_trace('p_row_value: ' ||to_char(p_row_value));
4390: hr_utility_trace('p_period_days: ' ||to_char(p_period_days));
4391:
4392: t_pro_table_values.delete;
4393: i := 0;
4394:
4391:
4392: t_pro_table_values.delete;
4393: i := 0;
4394:
4395: hr_utility.trace('The code is here in the function - 10');
4396:
4397: /*The following cursor will fetch the prorated user table
4398: values along with the table rate pertaining to each of
4399: the ranges. This information will be stored in the
4413:
4414: /*If the count of the number of records in the table is 0 then raise
4415: the error saying that the Subsidy cannot be calculated correctly.*/
4416: IF table_count = 0 THEN
4417: hr_utility.trace ('The table returned no rows. Please set the: '||upper(p_table_name) ||' user table correctly.');
4418: hr_utility.set_message(800, 'HR_MX_NO_SUBSIDY');
4419: hr_utility.raise_error;
4420: END IF;
4421: hr_utility.trace('The code is here in the function - 20');
4414: /*If the count of the number of records in the table is 0 then raise
4415: the error saying that the Subsidy cannot be calculated correctly.*/
4416: IF table_count = 0 THEN
4417: hr_utility.trace ('The table returned no rows. Please set the: '||upper(p_table_name) ||' user table correctly.');
4418: hr_utility.set_message(800, 'HR_MX_NO_SUBSIDY');
4419: hr_utility.raise_error;
4420: END IF;
4421: hr_utility.trace('The code is here in the function - 20');
4422:
4415: the error saying that the Subsidy cannot be calculated correctly.*/
4416: IF table_count = 0 THEN
4417: hr_utility.trace ('The table returned no rows. Please set the: '||upper(p_table_name) ||' user table correctly.');
4418: hr_utility.set_message(800, 'HR_MX_NO_SUBSIDY');
4419: hr_utility.raise_error;
4420: END IF;
4421: hr_utility.trace('The code is here in the function - 20');
4422:
4423: /*The following code will set the prorated low range
4417: hr_utility.trace ('The table returned no rows. Please set the: '||upper(p_table_name) ||' user table correctly.');
4418: hr_utility.set_message(800, 'HR_MX_NO_SUBSIDY');
4419: hr_utility.raise_error;
4420: END IF;
4421: hr_utility.trace('The code is here in the function - 20');
4422:
4423: /*The following code will set the prorated low range
4424: values as prorated high range value of previous range
4425: + 0.01. Also it checks in which range does p_row_value
4432: ELSIF i = table_count-1 THEN
4433: t_pro_table_values(i).high_range := 9999999999.99;
4434: END IF;
4435:
4436: hr_utility.trace('The code is here in the function - 30');
4437:
4438: IF t_pro_table_values.exists(i+1) THEN
4439: t_pro_table_values(i+1).low_range := t_pro_table_values(i).high_range + 0.01;
4440: END IF;
4438: IF t_pro_table_values.exists(i+1) THEN
4439: t_pro_table_values(i+1).low_range := t_pro_table_values(i).high_range + 0.01;
4440: END IF;
4441:
4442: hr_utility.trace('The code is here in the function - 40');
4443:
4444: hr_utility.trace('Low Range is: '||to_char(t_pro_table_values(i).low_range));
4445: hr_utility.trace('High Range is: '||to_char(t_pro_table_values(i).high_range));
4446:
4440: END IF;
4441:
4442: hr_utility.trace('The code is here in the function - 40');
4443:
4444: hr_utility.trace('Low Range is: '||to_char(t_pro_table_values(i).low_range));
4445: hr_utility.trace('High Range is: '||to_char(t_pro_table_values(i).high_range));
4446:
4447: IF p_row_value BETWEEN t_pro_table_values(i).low_range AND t_pro_table_values(i).high_range THEN
4448: l_table_value := t_pro_table_values(i).row_value;
4441:
4442: hr_utility.trace('The code is here in the function - 40');
4443:
4444: hr_utility.trace('Low Range is: '||to_char(t_pro_table_values(i).low_range));
4445: hr_utility.trace('High Range is: '||to_char(t_pro_table_values(i).high_range));
4446:
4447: IF p_row_value BETWEEN t_pro_table_values(i).low_range AND t_pro_table_values(i).high_range THEN
4448: l_table_value := t_pro_table_values(i).row_value;
4449: hr_utility.trace('The table value is: '||to_char(l_table_value));
4445: hr_utility.trace('High Range is: '||to_char(t_pro_table_values(i).high_range));
4446:
4447: IF p_row_value BETWEEN t_pro_table_values(i).low_range AND t_pro_table_values(i).high_range THEN
4448: l_table_value := t_pro_table_values(i).row_value;
4449: hr_utility.trace('The table value is: '||to_char(l_table_value));
4450: EXIT;
4451: END IF;
4452:
4453: END IF;
4473: l_table_id pay_user_tables.user_table_id%type;
4474: l_value pay_user_column_instances_f.value%type;
4475: ln_period_days number;
4476: begin
4477: hr_utility_trace('p_bus_group_id: ' ||p_bus_group_id);
4478: hr_utility_trace('p_table_name: ' ||p_table_name);
4479: hr_utility_trace('p_col_name: ' ||p_col_name);
4480: hr_utility_trace('p_row_value: ' ||p_row_value);
4481: hr_utility_trace('p_effective_date: ' ||p_effective_date);
4474: l_value pay_user_column_instances_f.value%type;
4475: ln_period_days number;
4476: begin
4477: hr_utility_trace('p_bus_group_id: ' ||p_bus_group_id);
4478: hr_utility_trace('p_table_name: ' ||p_table_name);
4479: hr_utility_trace('p_col_name: ' ||p_col_name);
4480: hr_utility_trace('p_row_value: ' ||p_row_value);
4481: hr_utility_trace('p_effective_date: ' ||p_effective_date);
4482: hr_utility_trace('p_period_days: ' ||p_period_days);
4475: ln_period_days number;
4476: begin
4477: hr_utility_trace('p_bus_group_id: ' ||p_bus_group_id);
4478: hr_utility_trace('p_table_name: ' ||p_table_name);
4479: hr_utility_trace('p_col_name: ' ||p_col_name);
4480: hr_utility_trace('p_row_value: ' ||p_row_value);
4481: hr_utility_trace('p_effective_date: ' ||p_effective_date);
4482: hr_utility_trace('p_period_days: ' ||p_period_days);
4483: hr_utility_trace('p_period_number: ' ||p_period_number);
4476: begin
4477: hr_utility_trace('p_bus_group_id: ' ||p_bus_group_id);
4478: hr_utility_trace('p_table_name: ' ||p_table_name);
4479: hr_utility_trace('p_col_name: ' ||p_col_name);
4480: hr_utility_trace('p_row_value: ' ||p_row_value);
4481: hr_utility_trace('p_effective_date: ' ||p_effective_date);
4482: hr_utility_trace('p_period_days: ' ||p_period_days);
4483: hr_utility_trace('p_period_number: ' ||p_period_number);
4484: hr_utility_trace('p_period_type: ' ||p_period_type);
4477: hr_utility_trace('p_bus_group_id: ' ||p_bus_group_id);
4478: hr_utility_trace('p_table_name: ' ||p_table_name);
4479: hr_utility_trace('p_col_name: ' ||p_col_name);
4480: hr_utility_trace('p_row_value: ' ||p_row_value);
4481: hr_utility_trace('p_effective_date: ' ||p_effective_date);
4482: hr_utility_trace('p_period_days: ' ||p_period_days);
4483: hr_utility_trace('p_period_number: ' ||p_period_number);
4484: hr_utility_trace('p_period_type: ' ||p_period_type);
4485: --
4478: hr_utility_trace('p_table_name: ' ||p_table_name);
4479: hr_utility_trace('p_col_name: ' ||p_col_name);
4480: hr_utility_trace('p_row_value: ' ||p_row_value);
4481: hr_utility_trace('p_effective_date: ' ||p_effective_date);
4482: hr_utility_trace('p_period_days: ' ||p_period_days);
4483: hr_utility_trace('p_period_number: ' ||p_period_number);
4484: hr_utility_trace('p_period_type: ' ||p_period_type);
4485: --
4486: -- Use either the supplied date, or the date from fnd_sessions
4479: hr_utility_trace('p_col_name: ' ||p_col_name);
4480: hr_utility_trace('p_row_value: ' ||p_row_value);
4481: hr_utility_trace('p_effective_date: ' ||p_effective_date);
4482: hr_utility_trace('p_period_days: ' ||p_period_days);
4483: hr_utility_trace('p_period_number: ' ||p_period_number);
4484: hr_utility_trace('p_period_type: ' ||p_period_type);
4485: --
4486: -- Use either the supplied date, or the date from fnd_sessions
4487: --
4480: hr_utility_trace('p_row_value: ' ||p_row_value);
4481: hr_utility_trace('p_effective_date: ' ||p_effective_date);
4482: hr_utility_trace('p_period_days: ' ||p_period_days);
4483: hr_utility_trace('p_period_number: ' ||p_period_number);
4484: hr_utility_trace('p_period_type: ' ||p_period_type);
4485: --
4486: -- Use either the supplied date, or the date from fnd_sessions
4487: --
4488: if (p_effective_date is not null) then
4498: and nvl (business_group_id,
4499: p_bus_group_id) = p_bus_group_id
4500: and nvl(legislation_code, 'MX') = 'MX';
4501: --
4502: hr_utility_trace('l_range_or_match: ' ||l_range_or_match);
4503: hr_utility_trace('l_table_id: ' ||l_table_id);
4504: --
4505: if p_period_type in ('Calendar Month') or
4506: (p_period_type in ('Semi-Month') and p_period_number = 2) or
4499: p_bus_group_id) = p_bus_group_id
4500: and nvl(legislation_code, 'MX') = 'MX';
4501: --
4502: hr_utility_trace('l_range_or_match: ' ||l_range_or_match);
4503: hr_utility_trace('l_table_id: ' ||l_table_id);
4504: --
4505: if p_period_type in ('Calendar Month') or
4506: (p_period_type in ('Semi-Month') and p_period_number = 2) or
4507: (p_period_type = 'Ten Days' AND p_period_number = 3) then
4508: ln_period_days := 30.4;
4509: else
4510: ln_period_days := least(p_period_days * p_period_number,30.4);
4511: end if; --
4512: hr_utility_trace('ln_period_days: ' ||ln_period_days);
4513:
4514: /*Bug 14094610 - Begin
4515: Calling the following function to correctly identify which table range
4516: does the value p_row_value falls in, and to fetch the corresponding table
4527: fnd_number.canonical_to_number(p_row_value),
4528: ln_period_days,
4529: p_table_name);
4530:
4531: hr_utility_trace('l_table_value: ' ||l_value);
4532: return l_value;
4533:
4534: /*Bug 14094610 - End*/
4535:
4570: p_bus_group_id) = p_bus_group_id
4571: and nvl (CINST.legislation_code,
4572: 'MX') = 'MX';
4573: --
4574: hr_utility_trace('l_value: ' ||l_value);
4575: return l_value;
4576:
4577: end if;
4578:
4578:
4579: end get_table_value;
4580:
4581: BEGIN
4582: --hr_utility.trace_on (null, 'MX_IDC');
4583: g_proc_name := 'PAY_MX_TAX_FUNCTIONS.';
4584: g_debug := hr_utility.debug_enabled;
4585:
4586: END PAY_MX_TAX_FUNCTIONS;
4580:
4581: BEGIN
4582: --hr_utility.trace_on (null, 'MX_IDC');
4583: g_proc_name := 'PAY_MX_TAX_FUNCTIONS.';
4584: g_debug := hr_utility.debug_enabled;
4585:
4586: END PAY_MX_TAX_FUNCTIONS;