[Home] [Help]
1113: --
1114: PROCEDURE write_output_header IS
1115: BEGIN
1116: --
1117: hr_utility.set_location('pay_gb_eoy_archive.write_output_header', 10);
1118: fnd_file.put_line(fnd_file.output, rpad(' ', 41)||
1119: 'End Of Year Process - Errors and Warnings Report'||
1120: rpad(' ', 30)||fnd_date.date_to_displaydate(sysdate));
1121: fnd_file.put_line(fnd_file.output, ' ');
1131: fnd_file.put_line(fnd_file.output, rpad('-', 14, '-') || ' ' ||
1132: rpad('-', 30, '-') || ' ' || rpad('-', 10, '-') || ' ' ||
1133: rpad('-', 75, '-'));
1134: g_output_header := FALSE;
1135: hr_utility.set_location('pay_gb_eoy_archive.write_output_header', 100);
1136: END write_output_header;
1137: --
1138: FUNCTION write_output(p_assignment_number IN VARCHAR2,
1139: p_full_name IN VARCHAR2,
1143: l_err_warn VARCHAR2(10);
1144: l_message VARCHAR2(250);
1145: BEGIN
1146: --
1147: hr_utility.set_location('pay_gb_eoy_archive.write_output', 1);
1148: hr_utility.trace('p_assignment_number='||p_assignment_number);
1149: hr_utility.trace('p_full_name='||p_full_name);
1150: hr_utility.trace('p_message_type='||p_message_type);
1151: hr_utility.trace('p_message='||p_message);
1144: l_message VARCHAR2(250);
1145: BEGIN
1146: --
1147: hr_utility.set_location('pay_gb_eoy_archive.write_output', 1);
1148: hr_utility.trace('p_assignment_number='||p_assignment_number);
1149: hr_utility.trace('p_full_name='||p_full_name);
1150: hr_utility.trace('p_message_type='||p_message_type);
1151: hr_utility.trace('p_message='||p_message);
1152: --
1145: BEGIN
1146: --
1147: hr_utility.set_location('pay_gb_eoy_archive.write_output', 1);
1148: hr_utility.trace('p_assignment_number='||p_assignment_number);
1149: hr_utility.trace('p_full_name='||p_full_name);
1150: hr_utility.trace('p_message_type='||p_message_type);
1151: hr_utility.trace('p_message='||p_message);
1152: --
1153: -- strip ':' from the error message
1146: --
1147: hr_utility.set_location('pay_gb_eoy_archive.write_output', 1);
1148: hr_utility.trace('p_assignment_number='||p_assignment_number);
1149: hr_utility.trace('p_full_name='||p_full_name);
1150: hr_utility.trace('p_message_type='||p_message_type);
1151: hr_utility.trace('p_message='||p_message);
1152: --
1153: -- strip ':' from the error message
1154: l_message := ltrim(p_message, ':');
1147: hr_utility.set_location('pay_gb_eoy_archive.write_output', 1);
1148: hr_utility.trace('p_assignment_number='||p_assignment_number);
1149: hr_utility.trace('p_full_name='||p_full_name);
1150: hr_utility.trace('p_message_type='||p_message_type);
1151: hr_utility.trace('p_message='||p_message);
1152: --
1153: -- strip ':' from the error message
1154: l_message := ltrim(p_message, ':');
1155: --
1155: --
1156: IF g_output_header THEN
1157: write_output_header;
1158: END IF;
1159: hr_utility.set_location('pay_gb_eoy_archive.write_output', 30);
1160: IF p_message_type = 'E' THEN
1161: l_err_warn := 'Error';
1162: fnd_file.put_line(fnd_file.log, 'An error encountered when processing assignment '||p_assignment_number||', please check output file for more details.');
1163: g_err_count := nvl(g_err_count, 0) + 1;
1165: l_err_warn := 'Warning';
1166: g_warn_count := nvl(g_warn_count, 0) + 1;
1167: END IF;
1168: --
1169: hr_utility.set_location('pay_gb_eoy_archive.write_output', 40);
1170: fnd_file.put_line(fnd_file.output, rpad(p_assignment_number, 14)||' '||
1171: rpad(p_full_name, 30)||' '||rpad(l_err_warn,10)||' '||
1172: rpad(l_message,75));
1173: --
1170: fnd_file.put_line(fnd_file.output, rpad(p_assignment_number, 14)||' '||
1171: rpad(p_full_name, 30)||' '||rpad(l_err_warn,10)||' '||
1172: rpad(l_message,75));
1173: --
1174: hr_utility.set_location('pay_gb_eoy_archive.write_output', 50);
1175: IF length(l_message) > 75 THEN
1176: fnd_file.put_line(fnd_file.output, rpad(' ', 57)||
1177: rpad(substr(l_message,76),75));
1178: END IF;
1176: fnd_file.put_line(fnd_file.output, rpad(' ', 57)||
1177: rpad(substr(l_message,76),75));
1178: END IF;
1179: --
1180: hr_utility.set_location('pay_gb_eoy_archive.write_output', 100);
1181: return 0;
1182: END write_output;
1183: --
1184: FUNCTION write_output_footer RETURN NUMBER IS
1182: END write_output;
1183: --
1184: FUNCTION write_output_footer RETURN NUMBER IS
1185: BEGIN
1186: hr_utility.set_location('pay_gb_eoy_archive.write_output_header', 1);
1187: --
1188: IF g_output_header THEN
1189: write_output_header;
1190: END IF;
1188: IF g_output_header THEN
1189: write_output_header;
1190: END IF;
1191: --
1192: hr_utility.set_location('pay_gb_eoy_archive.write_output_header', 10);
1193: fnd_file.put_line(fnd_file.output, ' ');
1194: fnd_file.put_line(fnd_file.output, rpad(' ', 20)||
1195: 'Total Number of Errors = '||nvl(g_err_count,0));
1196: fnd_file.put_line(fnd_file.output, rpad(' ', 20)||
1194: fnd_file.put_line(fnd_file.output, rpad(' ', 20)||
1195: 'Total Number of Errors = '||nvl(g_err_count,0));
1196: fnd_file.put_line(fnd_file.output, rpad(' ', 20)||
1197: 'Total Number of Warnings = '||nvl(g_warn_count,0));
1198: hr_utility.set_location('pay_gb_eoy_archive.write_output_header', 100);
1199: return 0;
1200: END write_output_footer;
1201: --
1202: PROCEDURE range_cursor (pactid IN NUMBER,
1462: END archive_payroll_info;
1463: --
1464: BEGIN
1465: BEGIN
1466: hr_utility.set_location('Entering: '||l_proc,1);
1467: --
1468: setup_entity_ids;
1469: --
1470: -- Find payroll action parameters
1499: EXCEPTION
1500: WHEN value_error THEN
1501: RAISE tax_dist_ref_error;
1502: END;
1503: hr_utility.set_location(l_proc,10);
1504: --
1505: l_payroll_name := NULL;
1506: -- Extract Payroll info
1507: FOR rec_payroll IN csr_payrolls(l_business_group_id,
1506: -- Extract Payroll info
1507: FOR rec_payroll IN csr_payrolls(l_business_group_id,
1508: l_end_year)
1509: LOOP
1510: hr_utility.set_location(l_proc||' '||rec_payroll.payroll_name,20);
1511: l_payroll_name := rec_payroll.payroll_name;
1512: -- find payroll year info
1513: OPEN csr_payroll_year (rec_payroll.payroll_id,l_start_year,l_end_year);
1514: FETCH csr_payroll_year INTO l_payroll_start_year,
1522: l_end_year,
1523: rec_payroll.emp_paye_ref);
1524: FETCH csr_another_paye_ref INTO l_another_paye_ref;
1525: IF csr_another_paye_ref%FOUND THEN
1526: hr_utility.trace('After csr_another_paye_ref, l_another_paye_ref='||
1527: l_another_paye_ref);
1528: CLOSE csr_another_paye_ref;
1529: RAISE inconsis_ref_error;
1530: ELSE
1528: CLOSE csr_another_paye_ref;
1529: RAISE inconsis_ref_error;
1530: ELSE
1531: CLOSE csr_another_paye_ref;
1532: hr_utility.trace('No other paye ref found on the payroll.');
1533: END IF;
1534: --
1535: -- Call API to archive Data via cover procedure
1536: /* Start 4011263
1670: -- return range cursor
1671: --
1672: -- select all people in the BG, filter out non appropriate ones in
1673: -- action_creation procedure.
1674: hr_utility.set_location(l_proc,30);
1675: -- sqlstr must contain one and only one entry of :payroll_action_id
1676: -- it must be ordered by person_id
1677: --
1678: sqlstr := 'SELECT DISTINCT person_id
1680: pay_payroll_actions ppa
1681: WHERE ppa.payroll_action_id = :payroll_action_id
1682: AND ppa.business_group_id +0= ppf.business_group_id
1683: ORDER BY ppf.person_id';
1684: hr_utility.set_location('Leaving: '||l_proc,40);
1685: EXCEPTION
1686: WHEN OTHERS THEN
1687: hr_utility.set_location(' Leaving: '||l_proc,50);
1688: fnd_file.put_line(fnd_file.log,
1683: ORDER BY ppf.person_id';
1684: hr_utility.set_location('Leaving: '||l_proc,40);
1685: EXCEPTION
1686: WHEN OTHERS THEN
1687: hr_utility.set_location(' Leaving: '||l_proc,50);
1688: fnd_file.put_line(fnd_file.log,
1689: substr('Error in rangecode '||sqlerrm(sqlcode),1,80));
1690: -- Return cursor that selects no rows
1691: sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
1955: --
1956: BEGIN
1957: IF chunk = 1 THEN
1958: NULL;
1959: --hr_utility.trace_on(NULL,'EOY_CHUNK1');
1960: END IF;
1961: --hr_utility.trace_on(NULL,'RMEOYAC');
1962: hr_utility.set_location('Entering: '||l_proc,1);
1963: -- Setup info and ids if new session.
1957: IF chunk = 1 THEN
1958: NULL;
1959: --hr_utility.trace_on(NULL,'EOY_CHUNK1');
1960: END IF;
1961: --hr_utility.trace_on(NULL,'RMEOYAC');
1962: hr_utility.set_location('Entering: '||l_proc,1);
1963: -- Setup info and ids if new session.
1964: -- DO NOT set g_payroll_action_id here as archinit() may stop working
1965: IF g_context_id IS NULL THEN
1958: NULL;
1959: --hr_utility.trace_on(NULL,'EOY_CHUNK1');
1960: END IF;
1961: --hr_utility.trace_on(NULL,'RMEOYAC');
1962: hr_utility.set_location('Entering: '||l_proc,1);
1963: -- Setup info and ids if new session.
1964: -- DO NOT set g_payroll_action_id here as archinit() may stop working
1965: IF g_context_id IS NULL THEN
1966: OPEN csr_parameter_info(pactid);
2006: OPEN csr_context_id('PAYROLL_ID');
2007: FETCH csr_context_id INTO g_context_id;
2008: CLOSE csr_context_id;
2009: --
2010: hr_utility.set_location(l_proc,10);
2011: --
2012: cache_archive_value(p_payroll_action_id => pactid,
2013: p_end_yr_ueid => g_payroll_end_year_eid,
2014: p_start_yr_ueid => g_payroll_start_year_eid,
2016: p_tax_dist_ueid => g_tax_dist_ref_eid,
2017: p_permit_ueid => g_permit_number_eid);
2018: --
2019: END IF; -- g_context_id IS NULL
2020: hr_utility.set_location(l_proc,13);
2021: --
2022: -- set asg backup details to unlikely values before loop.
2023: rec_prev_asg.assignment_id := hr_api.g_number;
2024: rec_prev_asg.effective_end_date := hr_api.g_date;
2034: g_payroll_end_year_eid,
2035: g_asg_set_id);
2036: LOOP
2037: FETCH csr_assignments INTO rec_assignment;
2038: hr_utility.trace(l_proc||' Fetched assignment_id='||
2039: rec_assignment.assignment_id||', g_asg_set_id='||g_asg_set_id);
2040: l_process_asg := FALSE;
2041: l_payroll_start_date :=
2042: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
2041: l_payroll_start_date :=
2042: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
2043: (pactid,'X_START_YEAR',
2044: to_char(rec_assignment.payroll_id)));
2045: hr_utility.trace(l_proc||' l_payroll_start_date='||fnd_date.date_to_displaydate(l_payroll_start_date));
2046: l_payroll_end_date :=
2047: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
2048: (pactid,'X_END_YEAR',
2049: to_char(rec_assignment.payroll_id)));
2046: l_payroll_end_date :=
2047: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
2048: (pactid,'X_END_YEAR',
2049: to_char(rec_assignment.payroll_id)));
2050: hr_utility.trace(l_proc||' l_payroll_end_date='||fnd_date.date_to_displaydate(l_payroll_end_date));
2051: hr_utility.trace(l_proc||' tax_ref_xfer='||rec_assignment.tax_ref_xfer);
2052: IF nvl(rec_assignment.tax_ref_xfer,' ') = 'N' AND csr_assignments%FOUND
2053: THEN
2054: hr_utility.trace(l_proc||' opening cursor csr_sub_asg.');
2047: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
2048: (pactid,'X_END_YEAR',
2049: to_char(rec_assignment.payroll_id)));
2050: hr_utility.trace(l_proc||' l_payroll_end_date='||fnd_date.date_to_displaydate(l_payroll_end_date));
2051: hr_utility.trace(l_proc||' tax_ref_xfer='||rec_assignment.tax_ref_xfer);
2052: IF nvl(rec_assignment.tax_ref_xfer,' ') = 'N' AND csr_assignments%FOUND
2053: THEN
2054: hr_utility.trace(l_proc||' opening cursor csr_sub_asg.');
2055: for asgrec in csr_sub_asg( rec_assignment.charrowid,
2050: hr_utility.trace(l_proc||' l_payroll_end_date='||fnd_date.date_to_displaydate(l_payroll_end_date));
2051: hr_utility.trace(l_proc||' tax_ref_xfer='||rec_assignment.tax_ref_xfer);
2052: IF nvl(rec_assignment.tax_ref_xfer,' ') = 'N' AND csr_assignments%FOUND
2053: THEN
2054: hr_utility.trace(l_proc||' opening cursor csr_sub_asg.');
2055: for asgrec in csr_sub_asg( rec_assignment.charrowid,
2056: l_payroll_start_date,
2057: l_payroll_end_date,
2058: pactid,
2058: pactid,
2059: g_payroll_start_year_eid,
2060: g_payroll_end_year_eid
2061: ) loop
2062: hr_utility.trace(l_proc||' In the loop for cursor csr_sub_asg.');
2063: l_process_asg := TRUE;
2064: end loop;
2065: ELSIF nvl(rec_assignment.tax_ref_xfer,' ') = 'Y' AND csr_assignments%FOUND
2066: THEN
2063: l_process_asg := TRUE;
2064: end loop;
2065: ELSIF nvl(rec_assignment.tax_ref_xfer,' ') = 'Y' AND csr_assignments%FOUND
2066: THEN
2067: hr_utility.trace(l_proc||' tax ref xfer=Y and found record by csr_assignments cursor.');
2068: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2069: hr_utility.trace(l_proc||' rec_assignment.effective_start_date='||fnd_date.date_to_displaydate(rec_assignment.effective_start_date));
2070: IF rec_assignment.effective_end_date >= l_payroll_start_date
2071: AND rec_assignment.effective_start_date <= l_payroll_end_date
2064: end loop;
2065: ELSIF nvl(rec_assignment.tax_ref_xfer,' ') = 'Y' AND csr_assignments%FOUND
2066: THEN
2067: hr_utility.trace(l_proc||' tax ref xfer=Y and found record by csr_assignments cursor.');
2068: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2069: hr_utility.trace(l_proc||' rec_assignment.effective_start_date='||fnd_date.date_to_displaydate(rec_assignment.effective_start_date));
2070: IF rec_assignment.effective_end_date >= l_payroll_start_date
2071: AND rec_assignment.effective_start_date <= l_payroll_end_date
2072: THEN
2065: ELSIF nvl(rec_assignment.tax_ref_xfer,' ') = 'Y' AND csr_assignments%FOUND
2066: THEN
2067: hr_utility.trace(l_proc||' tax ref xfer=Y and found record by csr_assignments cursor.');
2068: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2069: hr_utility.trace(l_proc||' rec_assignment.effective_start_date='||fnd_date.date_to_displaydate(rec_assignment.effective_start_date));
2070: IF rec_assignment.effective_end_date >= l_payroll_start_date
2071: AND rec_assignment.effective_start_date <= l_payroll_end_date
2072: THEN
2073: l_process_asg := TRUE;
2075: ELSE
2076: -- will come here if csr_assignments%NOTFOUND
2077: l_process_asg := TRUE;
2078: end if;
2079: hr_utility.trace(l_proc||' rec_assignment.person_id='||rec_assignment.person_id);
2080: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2081: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2082: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2083: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2076: -- will come here if csr_assignments%NOTFOUND
2077: l_process_asg := TRUE;
2078: end if;
2079: hr_utility.trace(l_proc||' rec_assignment.person_id='||rec_assignment.person_id);
2080: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2081: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2082: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2083: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2084: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2077: l_process_asg := TRUE;
2078: end if;
2079: hr_utility.trace(l_proc||' rec_assignment.person_id='||rec_assignment.person_id);
2080: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2081: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2082: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2083: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2084: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2085: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2078: end if;
2079: hr_utility.trace(l_proc||' rec_assignment.person_id='||rec_assignment.person_id);
2080: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2081: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2082: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2083: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2084: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2085: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2086: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2079: hr_utility.trace(l_proc||' rec_assignment.person_id='||rec_assignment.person_id);
2080: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2081: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2082: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2083: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2084: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2085: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2086: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2087: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2080: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2081: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2082: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2083: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2084: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2085: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2086: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2087: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2088: hr_utility.trace(l_proc||' rec_prev_asg.tax_ref='||rec_prev_asg.tax_ref);
2081: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2082: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2083: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2084: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2085: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2086: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2087: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2088: hr_utility.trace(l_proc||' rec_prev_asg.tax_ref='||rec_prev_asg.tax_ref);
2089: IF (csr_assignments%NOTFOUND
2082: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2083: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2084: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2085: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2086: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2087: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2088: hr_utility.trace(l_proc||' rec_prev_asg.tax_ref='||rec_prev_asg.tax_ref);
2089: IF (csr_assignments%NOTFOUND
2090: OR rec_assignment.person_id <> rec_prev_asg.person_id
2083: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2084: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2085: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2086: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2087: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2088: hr_utility.trace(l_proc||' rec_prev_asg.tax_ref='||rec_prev_asg.tax_ref);
2089: IF (csr_assignments%NOTFOUND
2090: OR rec_assignment.person_id <> rec_prev_asg.person_id
2091: OR rec_assignment.period_of_service_id <> rec_prev_asg.period_of_service_id -- Added to fix bug 3784871
2084: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2085: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2086: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2087: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2088: hr_utility.trace(l_proc||' rec_prev_asg.tax_ref='||rec_prev_asg.tax_ref);
2089: IF (csr_assignments%NOTFOUND
2090: OR rec_assignment.person_id <> rec_prev_asg.person_id
2091: OR rec_assignment.period_of_service_id <> rec_prev_asg.period_of_service_id -- Added to fix bug 3784871
2092: OR rec_assignment.agg_active_start <> rec_prev_asg.agg_active_start
2114: p_object_version_number => l_ovn,
2115: p_some_warning => l_some_warning);
2116: END IF;
2117: EXIT WHEN csr_assignments%NOTFOUND;
2118: hr_utility.set_location(l_proc,15);
2119: hr_utility.trace(l_proc||' rec_assignment.assignment_id='||rec_assignment.assignment_id);
2120: hr_utility.trace(l_proc||' rec_prev_asg.assignment_id='||rec_prev_asg.assignment_id);
2121: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2122: hr_utility.trace(l_proc||' rec_prev_asg.effective_end_date='||fnd_date.date_to_displaydate(rec_prev_asg.effective_end_date));
2115: p_some_warning => l_some_warning);
2116: END IF;
2117: EXIT WHEN csr_assignments%NOTFOUND;
2118: hr_utility.set_location(l_proc,15);
2119: hr_utility.trace(l_proc||' rec_assignment.assignment_id='||rec_assignment.assignment_id);
2120: hr_utility.trace(l_proc||' rec_prev_asg.assignment_id='||rec_prev_asg.assignment_id);
2121: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2122: hr_utility.trace(l_proc||' rec_prev_asg.effective_end_date='||fnd_date.date_to_displaydate(rec_prev_asg.effective_end_date));
2123: IF (rec_assignment.assignment_id <> rec_prev_asg.assignment_id
2116: END IF;
2117: EXIT WHEN csr_assignments%NOTFOUND;
2118: hr_utility.set_location(l_proc,15);
2119: hr_utility.trace(l_proc||' rec_assignment.assignment_id='||rec_assignment.assignment_id);
2120: hr_utility.trace(l_proc||' rec_prev_asg.assignment_id='||rec_prev_asg.assignment_id);
2121: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2122: hr_utility.trace(l_proc||' rec_prev_asg.effective_end_date='||fnd_date.date_to_displaydate(rec_prev_asg.effective_end_date));
2123: IF (rec_assignment.assignment_id <> rec_prev_asg.assignment_id
2124: OR rec_assignment.effective_end_date <> rec_prev_asg.effective_end_date)
2117: EXIT WHEN csr_assignments%NOTFOUND;
2118: hr_utility.set_location(l_proc,15);
2119: hr_utility.trace(l_proc||' rec_assignment.assignment_id='||rec_assignment.assignment_id);
2120: hr_utility.trace(l_proc||' rec_prev_asg.assignment_id='||rec_prev_asg.assignment_id);
2121: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2122: hr_utility.trace(l_proc||' rec_prev_asg.effective_end_date='||fnd_date.date_to_displaydate(rec_prev_asg.effective_end_date));
2123: IF (rec_assignment.assignment_id <> rec_prev_asg.assignment_id
2124: OR rec_assignment.effective_end_date <> rec_prev_asg.effective_end_date)
2125: AND l_process_asg
2118: hr_utility.set_location(l_proc,15);
2119: hr_utility.trace(l_proc||' rec_assignment.assignment_id='||rec_assignment.assignment_id);
2120: hr_utility.trace(l_proc||' rec_prev_asg.assignment_id='||rec_prev_asg.assignment_id);
2121: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2122: hr_utility.trace(l_proc||' rec_prev_asg.effective_end_date='||fnd_date.date_to_displaydate(rec_prev_asg.effective_end_date));
2123: IF (rec_assignment.assignment_id <> rec_prev_asg.assignment_id
2124: OR rec_assignment.effective_end_date <> rec_prev_asg.effective_end_date)
2125: AND l_process_asg
2126: -- if the current row is the first row
2127: -- or is not the same as the previous one
2128: -- (ignoring tax_ref_xfer) as the 2nd part of union may bring back
2129: -- duplicates
2130: THEN
2131: hr_utility.set_location(l_proc,20);
2132: -- insert an action
2133: SELECT pay_assignment_actions_s.nextval
2134: INTO l_actid
2135: FROM dual;
2187: rec_prev_asg := rec_assignment;
2188: END IF; -- not duplicate
2189: END LOOP;
2190: CLOSE csr_assignments;
2191: -- hr_utility.trace_off;
2192: hr_utility.set_location('Leaving: '||l_proc,40);
2193: END action_creation;
2194: --
2195: --
2188: END IF; -- not duplicate
2189: END LOOP;
2190: CLOSE csr_assignments;
2191: -- hr_utility.trace_off;
2192: hr_utility.set_location('Leaving: '||l_proc,40);
2193: END action_creation;
2194: --
2195: --
2196: PROCEDURE archinit(p_payroll_action_id IN NUMBER) IS
2310: l_agg_non_retry_err_flag VARCHAR2(1) := 'N';
2311: --
2312: l_dummy NUMBER := 0;
2313: BEGIN
2314: -- hr_utility.trace_on(NULL,'ARCHINIT');
2315: hr_utility.set_location('Entering: '||l_proc,1);
2316: --
2317: IF g_payroll_action_id IS NULL
2318: OR g_payroll_action_id <> p_payroll_action_id THEN
2311: --
2312: l_dummy NUMBER := 0;
2313: BEGIN
2314: -- hr_utility.trace_on(NULL,'ARCHINIT');
2315: hr_utility.set_location('Entering: '||l_proc,1);
2316: --
2317: IF g_payroll_action_id IS NULL
2318: OR g_payroll_action_id <> p_payroll_action_id THEN
2319: g_payroll_action_id := p_payroll_action_id;
2335: --
2336: l_agg_non_retry_err_flag := 'N';
2337: FOR retry_actions_rec IN get_retry_actions LOOP
2338: --
2339: hr_utility.trace(l_proc||': retry action id='||
2340: retry_actions_rec.assignment_action_id);
2341: hr_utility.trace(l_proc||': retry action status='||
2342: retry_actions_rec.action_status);
2343: --
2337: FOR retry_actions_rec IN get_retry_actions LOOP
2338: --
2339: hr_utility.trace(l_proc||': retry action id='||
2340: retry_actions_rec.assignment_action_id);
2341: hr_utility.trace(l_proc||': retry action status='||
2342: retry_actions_rec.action_status);
2343: --
2344: FOR non_retry_act_rec IN get_agg_non_retry_actions(retry_actions_rec.assignment_action_id) LOOP
2345: --
2342: retry_actions_rec.action_status);
2343: --
2344: FOR non_retry_act_rec IN get_agg_non_retry_actions(retry_actions_rec.assignment_action_id) LOOP
2345: --
2346: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_action_id='||
2347: non_retry_act_rec.assignment_action_id);
2348: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_number='||
2349: non_retry_act_rec.assignment_number);
2350: hr_utility.trace(l_proc||': non_retry_act_rec.retry_asg_number='||
2344: FOR non_retry_act_rec IN get_agg_non_retry_actions(retry_actions_rec.assignment_action_id) LOOP
2345: --
2346: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_action_id='||
2347: non_retry_act_rec.assignment_action_id);
2348: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_number='||
2349: non_retry_act_rec.assignment_number);
2350: hr_utility.trace(l_proc||': non_retry_act_rec.retry_asg_number='||
2351: non_retry_act_rec.retry_asg_number);
2352: hr_utility.trace(l_proc||': non_retry_act_rec.full_name='||
2346: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_action_id='||
2347: non_retry_act_rec.assignment_action_id);
2348: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_number='||
2349: non_retry_act_rec.assignment_number);
2350: hr_utility.trace(l_proc||': non_retry_act_rec.retry_asg_number='||
2351: non_retry_act_rec.retry_asg_number);
2352: hr_utility.trace(l_proc||': non_retry_act_rec.full_name='||
2353: non_retry_act_rec.full_name);
2354: l_agg_non_retry_err_flag := 'Y';
2348: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_number='||
2349: non_retry_act_rec.assignment_number);
2350: hr_utility.trace(l_proc||': non_retry_act_rec.retry_asg_number='||
2351: non_retry_act_rec.retry_asg_number);
2352: hr_utility.trace(l_proc||': non_retry_act_rec.full_name='||
2353: non_retry_act_rec.full_name);
2354: l_agg_non_retry_err_flag := 'Y';
2355: l_dummy := write_output(p_assignment_number => non_retry_act_rec.retry_asg_number,
2356: p_full_name => non_retry_act_rec.full_name,
2358: p_message => 'Assignment action '||non_retry_act_rec.assignment_action_id||' for the aggrgated assignment '||non_retry_act_rec.assignment_number||' must be marked for retry.');
2359: END LOOP;
2360: END LOOP;
2361: --
2362: hr_utility.trace(l_proc||' After get_agg_non_retry_actions, l_agg_non_retry_err_flag='||l_agg_non_retry_err_flag);
2363: --
2364: IF l_agg_non_retry_err_flag = 'Y' THEN
2365: -- Raise the error and stop processing the assignments marked for retry
2366: -- because aggregated assignment(s) has(have) not been marked for retry.
2993: --
2994: END LOOP;
2995: --
2996: END IF;
2997: hr_utility.set_location(' Leaving: '||l_proc,100);
2998: END archinit;
2999: --
3000: --
3001: PROCEDURE archive_code(p_assactid IN NUMBER, p_effective_date IN DATE) IS
3770: p_actid NUMBER DEFAULT NULL) IS
3771: l_proc CONSTANT VARCHAR2(40):= g_package||'archive_asg_info';
3772: BEGIN
3773: IF p_value IS NOT NULL THEN
3774: hr_utility.set_location(l_proc||' '||p_user_entity_id,10);
3775: ff_archive_api.create_archive_item
3776: (p_archive_item_id => l_archive_item_id,
3777: p_user_entity_id => p_user_entity_id,
3778: p_archive_value => p_value,
3815: -- p_actid may be given to archive the value against a different assignment
3816: -- action to that currently being processed. This is utilised by the
3817: -- Multiple Assignment Logic Pt. III
3818: BEGIN
3819: hr_utility.trace('Entering pay_gb_eoy_archive.archive_ni_values');
3820: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3821: hr_utility.trace('p_value='||p_value);
3822: hr_utility.trace('p_reportable='||p_reportable);
3823: hr_utility.trace('p_ni_cat='||p_ni_cat);
3816: -- action to that currently being processed. This is utilised by the
3817: -- Multiple Assignment Logic Pt. III
3818: BEGIN
3819: hr_utility.trace('Entering pay_gb_eoy_archive.archive_ni_values');
3820: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3821: hr_utility.trace('p_value='||p_value);
3822: hr_utility.trace('p_reportable='||p_reportable);
3823: hr_utility.trace('p_ni_cat='||p_ni_cat);
3824: hr_utility.trace('p_scon='||p_scon);
3817: -- Multiple Assignment Logic Pt. III
3818: BEGIN
3819: hr_utility.trace('Entering pay_gb_eoy_archive.archive_ni_values');
3820: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3821: hr_utility.trace('p_value='||p_value);
3822: hr_utility.trace('p_reportable='||p_reportable);
3823: hr_utility.trace('p_ni_cat='||p_ni_cat);
3824: hr_utility.trace('p_scon='||p_scon);
3825: hr_utility.trace('p_actid='||p_actid);
3818: BEGIN
3819: hr_utility.trace('Entering pay_gb_eoy_archive.archive_ni_values');
3820: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3821: hr_utility.trace('p_value='||p_value);
3822: hr_utility.trace('p_reportable='||p_reportable);
3823: hr_utility.trace('p_ni_cat='||p_ni_cat);
3824: hr_utility.trace('p_scon='||p_scon);
3825: hr_utility.trace('p_actid='||p_actid);
3826: hr_utility.trace('p_assactid='||p_assactid);
3819: hr_utility.trace('Entering pay_gb_eoy_archive.archive_ni_values');
3820: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3821: hr_utility.trace('p_value='||p_value);
3822: hr_utility.trace('p_reportable='||p_reportable);
3823: hr_utility.trace('p_ni_cat='||p_ni_cat);
3824: hr_utility.trace('p_scon='||p_scon);
3825: hr_utility.trace('p_actid='||p_actid);
3826: hr_utility.trace('p_assactid='||p_assactid);
3827: IF p_value IS NOT NULL THEN
3820: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3821: hr_utility.trace('p_value='||p_value);
3822: hr_utility.trace('p_reportable='||p_reportable);
3823: hr_utility.trace('p_ni_cat='||p_ni_cat);
3824: hr_utility.trace('p_scon='||p_scon);
3825: hr_utility.trace('p_actid='||p_actid);
3826: hr_utility.trace('p_assactid='||p_assactid);
3827: IF p_value IS NOT NULL THEN
3828: IF p_scon IS NOT NULL THEN
3821: hr_utility.trace('p_value='||p_value);
3822: hr_utility.trace('p_reportable='||p_reportable);
3823: hr_utility.trace('p_ni_cat='||p_ni_cat);
3824: hr_utility.trace('p_scon='||p_scon);
3825: hr_utility.trace('p_actid='||p_actid);
3826: hr_utility.trace('p_assactid='||p_assactid);
3827: IF p_value IS NOT NULL THEN
3828: IF p_scon IS NOT NULL THEN
3829: ff_archive_api.create_archive_item
3822: hr_utility.trace('p_reportable='||p_reportable);
3823: hr_utility.trace('p_ni_cat='||p_ni_cat);
3824: hr_utility.trace('p_scon='||p_scon);
3825: hr_utility.trace('p_actid='||p_actid);
3826: hr_utility.trace('p_assactid='||p_assactid);
3827: IF p_value IS NOT NULL THEN
3828: IF p_scon IS NOT NULL THEN
3829: ff_archive_api.create_archive_item
3830: (p_archive_item_id => l_archive_item_id,
3909: l_proc CONSTANT VARCHAR2(40):= g_package||'archive_agg_values';
3910: --
3911: BEGIN
3912: --
3913: hr_utility.set_location(l_proc,10);
3914: BEGIN
3915: g_agg_balance_totals.smp :=
3916: g_agg_balance_totals.smp + nvl(p_smp,0);
3917: g_agg_balance_totals.ssp :=
3937: g_agg_balance_totals.student_ln :=
3938: g_agg_balance_totals.student_ln + nvl(p_student_ln,0);
3939: g_agg_balance_totals.ni_arrears :=
3940: g_agg_balance_totals.ni_arrears + nvl(p_ni_arrears,0);
3941: hr_utility.set_location(l_proc,20);
3942: EXCEPTION WHEN NO_DATA_FOUND THEN
3943: hr_utility.set_location(l_proc,30);
3944: -- The PLSQL table is empty so first row.
3945: g_agg_balance_totals.smp := nvl(p_smp,0);
3939: g_agg_balance_totals.ni_arrears :=
3940: g_agg_balance_totals.ni_arrears + nvl(p_ni_arrears,0);
3941: hr_utility.set_location(l_proc,20);
3942: EXCEPTION WHEN NO_DATA_FOUND THEN
3943: hr_utility.set_location(l_proc,30);
3944: -- The PLSQL table is empty so first row.
3945: g_agg_balance_totals.smp := nvl(p_smp,0);
3946: g_agg_balance_totals.ssp := nvl(p_ssp,0);
3947: g_agg_balance_totals.sap := nvl(p_sap,0);
3956: g_agg_balance_totals.student_ln := nvl(p_student_ln,0);
3957: g_agg_balance_totals.ni_arrears := nvl(p_ni_arrears,0);
3958: END; -- store balance values in global table.
3959: --
3960: hr_utility.set_location(l_proc,40);
3961: --
3962: END store_agg_values;
3963: ------------------------------------------------------------------------
3964: PROCEDURE archive_ni_values(p_ni_cat VARCHAR2,
3989: l_index1 binary_integer;
3990: --
3991: BEGIN
3992: --
3993: hr_utility.set_location('Entering: '||l_proc,1);
3994: --
3995: IF l_multiple_asg_flag = 'Y' THEN
3996: -- Do Multiple Assignment Logic Part II
3997: --
4003: -- Removed previous total contribs check.
4004: -- Add the balances for this assignment to the NI balances
4005: -- (per NI category) for the person in the NI Balance Totals table
4006: --
4007: hr_utility.set_location(l_proc,10);
4008: -- Find appropriate row in NI values table:
4009: BEGIN
4010: l_index1:=0;
4011: LOOP
4014: AND nvl(g_ni_balance_totals(l_index1).scon,'NONE')
4015: = nvl(p_scon,'NONE');
4016: END LOOP;
4017: -- Add balances to those in table:
4018: hr_utility.set_location(l_proc||' '||l_index1,15);
4019: g_ni_balance_totals(l_index1).tot_contribs :=
4020: g_ni_balance_totals(l_index1).tot_contribs + nvl(p_tot_contribs,0);
4021: g_ni_balance_totals(l_index1).earnings :=
4022: g_ni_balance_totals(l_index1).earnings + nvl(p_earnings,0);
4037: g_ni_balance_totals(l_index1).ees_rebate :=
4038: g_ni_balance_totals(l_index1).ees_rebate + nvl(p_ees_rebate,0);
4039: EXCEPTION WHEN no_data_found THEN
4040: -- row not found, insert new one:
4041: hr_utility.set_location(l_proc||' '||l_index1,20);
4042: g_ni_balance_totals(l_index1).ni_cat := p_ni_cat;
4043: g_ni_balance_totals(l_index1).scon := p_scon;
4044: g_ni_balance_totals(l_index1).tot_contribs := nvl(p_tot_contribs,0);
4045: g_ni_balance_totals(l_index1).earnings := nvl(p_earnings,0);
4073: l_refund_flag := '';
4074: END IF;
4075: --
4076: IF p_scon IS NOT NULL THEN
4077: hr_utility.set_location(l_proc,30);
4078: archive_ni_value(g_ni_scon_earnings_eid,p_earnings,
4079: l_report_values,p_ni_cat,p_scon);
4080: archive_ni_value(g_ni_scon_ees_contribution_eid,p_ees_contribs,
4081: l_report_values,p_ni_cat,p_scon);
4097: l_report_values,p_ni_cat,p_scon);
4098: archive_ni_value(g_ni_scon_refund_eid, l_refund_flag,
4099: l_report_values,p_ni_cat,p_scon);
4100: ELSE
4101: hr_utility.set_location(l_proc,40);
4102: archive_ni_value(g_ni_earnings_eid,p_earnings,
4103: l_report_values,p_ni_cat);
4104: archive_ni_value(g_ni_ees_contribution_eid,p_ees_contribs,
4105: l_report_values,p_ni_cat);
4121: l_report_values,p_ni_cat);
4122: archive_ni_value(g_ni_refund_eid, l_refund_flag,
4123: l_report_values,p_ni_cat);
4124: END IF;
4125: hr_utility.set_location(' Leaving: '||l_proc,100);
4126: END archive_ni_values;
4127: --
4128: PROCEDURE remove_null_address_lines(p_address_line1 IN OUT NOCOPY VARCHAR2,
4129: p_address_line2 IN OUT NOCOPY VARCHAR2,
4136: l_dummy VARCHAR2(1000);
4137: l_proc VARCHAR2(100) := 'pay_gb_eoy_archive.remove_null_address_lines';
4138: --
4139: BEGIN
4140: hr_utility.set_location('Entering: '||l_proc,1);
4141: hr_utility.trace('p_address_line1='||p_address_line1);
4142: hr_utility.trace('p_address_line2='||p_address_line2);
4143: hr_utility.trace('p_address_line3='||p_address_line3);
4144: hr_utility.trace('p_address_line4='||p_address_line4);
4137: l_proc VARCHAR2(100) := 'pay_gb_eoy_archive.remove_null_address_lines';
4138: --
4139: BEGIN
4140: hr_utility.set_location('Entering: '||l_proc,1);
4141: hr_utility.trace('p_address_line1='||p_address_line1);
4142: hr_utility.trace('p_address_line2='||p_address_line2);
4143: hr_utility.trace('p_address_line3='||p_address_line3);
4144: hr_utility.trace('p_address_line4='||p_address_line4);
4145: --
4138: --
4139: BEGIN
4140: hr_utility.set_location('Entering: '||l_proc,1);
4141: hr_utility.trace('p_address_line1='||p_address_line1);
4142: hr_utility.trace('p_address_line2='||p_address_line2);
4143: hr_utility.trace('p_address_line3='||p_address_line3);
4144: hr_utility.trace('p_address_line4='||p_address_line4);
4145: --
4146: l_lines(1) := p_address_line1;
4139: BEGIN
4140: hr_utility.set_location('Entering: '||l_proc,1);
4141: hr_utility.trace('p_address_line1='||p_address_line1);
4142: hr_utility.trace('p_address_line2='||p_address_line2);
4143: hr_utility.trace('p_address_line3='||p_address_line3);
4144: hr_utility.trace('p_address_line4='||p_address_line4);
4145: --
4146: l_lines(1) := p_address_line1;
4147: l_lines(2) := p_address_line2;
4140: hr_utility.set_location('Entering: '||l_proc,1);
4141: hr_utility.trace('p_address_line1='||p_address_line1);
4142: hr_utility.trace('p_address_line2='||p_address_line2);
4143: hr_utility.trace('p_address_line3='||p_address_line3);
4144: hr_utility.trace('p_address_line4='||p_address_line4);
4145: --
4146: l_lines(1) := p_address_line1;
4147: l_lines(2) := p_address_line2;
4148: l_lines(3) := p_address_line3;
4156: END IF;
4157: END LOOP;
4158: END LOOP;
4159: --
4160: hr_utility.set_location(l_proc,10);
4161: --
4162: p_address_line1 := l_lines(1);
4163: p_address_line2 := l_lines(2);
4164: p_address_line3 := l_lines(3);
4163: p_address_line2 := l_lines(2);
4164: p_address_line3 := l_lines(3);
4165: p_address_line4 := l_lines(4);
4166: --
4167: hr_utility.set_location('Leaving: '||l_proc,20);
4168: hr_utility.trace('p_address_line1='||p_address_line1);
4169: hr_utility.trace('p_address_line2='||p_address_line2);
4170: hr_utility.trace('p_address_line3='||p_address_line3);
4171: hr_utility.trace('p_address_line4='||p_address_line4);
4164: p_address_line3 := l_lines(3);
4165: p_address_line4 := l_lines(4);
4166: --
4167: hr_utility.set_location('Leaving: '||l_proc,20);
4168: hr_utility.trace('p_address_line1='||p_address_line1);
4169: hr_utility.trace('p_address_line2='||p_address_line2);
4170: hr_utility.trace('p_address_line3='||p_address_line3);
4171: hr_utility.trace('p_address_line4='||p_address_line4);
4172: END;
4165: p_address_line4 := l_lines(4);
4166: --
4167: hr_utility.set_location('Leaving: '||l_proc,20);
4168: hr_utility.trace('p_address_line1='||p_address_line1);
4169: hr_utility.trace('p_address_line2='||p_address_line2);
4170: hr_utility.trace('p_address_line3='||p_address_line3);
4171: hr_utility.trace('p_address_line4='||p_address_line4);
4172: END;
4173: --
4166: --
4167: hr_utility.set_location('Leaving: '||l_proc,20);
4168: hr_utility.trace('p_address_line1='||p_address_line1);
4169: hr_utility.trace('p_address_line2='||p_address_line2);
4170: hr_utility.trace('p_address_line3='||p_address_line3);
4171: hr_utility.trace('p_address_line4='||p_address_line4);
4172: END;
4173: --
4174: BEGIN
4167: hr_utility.set_location('Leaving: '||l_proc,20);
4168: hr_utility.trace('p_address_line1='||p_address_line1);
4169: hr_utility.trace('p_address_line2='||p_address_line2);
4170: hr_utility.trace('p_address_line3='||p_address_line3);
4171: hr_utility.trace('p_address_line4='||p_address_line4);
4172: END;
4173: --
4174: BEGIN
4175: hr_utility.set_location('Entering: '||l_proc,1);
4171: hr_utility.trace('p_address_line4='||p_address_line4);
4172: END;
4173: --
4174: BEGIN
4175: hr_utility.set_location('Entering: '||l_proc,1);
4176: hr_utility.trace('p_assactid='||p_assactid);
4177: -- Get the AAC level info.
4178: OPEN csr_asg_act_info(p_assactid);
4179: FETCH csr_asg_act_info INTO l_assignment_id,
4172: END;
4173: --
4174: BEGIN
4175: hr_utility.set_location('Entering: '||l_proc,1);
4176: hr_utility.trace('p_assactid='||p_assactid);
4177: -- Get the AAC level info.
4178: OPEN csr_asg_act_info(p_assactid);
4179: FETCH csr_asg_act_info INTO l_assignment_id,
4180: l_effective_end_date,
4181: l_tax_ref_transfer,
4182: l_agg_active_start,
4183: l_agg_active_end;
4184: CLOSE csr_asg_act_info;
4185: hr_utility.trace('After csr_asg_act_info, l_assignment_id='||l_assignment_id);
4186: hr_utility.trace('l_effective_end_date='||fnd_date.date_to_displaydate(l_effective_end_date));
4187: hr_utility.trace('l_tax_ref_transfer='||l_tax_ref_transfer);
4188: hr_utility.trace('l_agg_active_start='||fnd_date.date_to_displaydate(l_agg_active_start));
4189: hr_utility.trace('l_agg_active_end='||fnd_date.date_to_displaydate(l_agg_active_end));
4182: l_agg_active_start,
4183: l_agg_active_end;
4184: CLOSE csr_asg_act_info;
4185: hr_utility.trace('After csr_asg_act_info, l_assignment_id='||l_assignment_id);
4186: hr_utility.trace('l_effective_end_date='||fnd_date.date_to_displaydate(l_effective_end_date));
4187: hr_utility.trace('l_tax_ref_transfer='||l_tax_ref_transfer);
4188: hr_utility.trace('l_agg_active_start='||fnd_date.date_to_displaydate(l_agg_active_start));
4189: hr_utility.trace('l_agg_active_end='||fnd_date.date_to_displaydate(l_agg_active_end));
4190: -- Bug fix for 3699865 - get the final process date
4183: l_agg_active_end;
4184: CLOSE csr_asg_act_info;
4185: hr_utility.trace('After csr_asg_act_info, l_assignment_id='||l_assignment_id);
4186: hr_utility.trace('l_effective_end_date='||fnd_date.date_to_displaydate(l_effective_end_date));
4187: hr_utility.trace('l_tax_ref_transfer='||l_tax_ref_transfer);
4188: hr_utility.trace('l_agg_active_start='||fnd_date.date_to_displaydate(l_agg_active_start));
4189: hr_utility.trace('l_agg_active_end='||fnd_date.date_to_displaydate(l_agg_active_end));
4190: -- Bug fix for 3699865 - get the final process date
4191: OPEN csr_asg_last_process_date(l_assignment_id);
4184: CLOSE csr_asg_act_info;
4185: hr_utility.trace('After csr_asg_act_info, l_assignment_id='||l_assignment_id);
4186: hr_utility.trace('l_effective_end_date='||fnd_date.date_to_displaydate(l_effective_end_date));
4187: hr_utility.trace('l_tax_ref_transfer='||l_tax_ref_transfer);
4188: hr_utility.trace('l_agg_active_start='||fnd_date.date_to_displaydate(l_agg_active_start));
4189: hr_utility.trace('l_agg_active_end='||fnd_date.date_to_displaydate(l_agg_active_end));
4190: -- Bug fix for 3699865 - get the final process date
4191: OPEN csr_asg_last_process_date(l_assignment_id);
4192: FETCH csr_asg_last_process_date INTO l_final_process_date;
4185: hr_utility.trace('After csr_asg_act_info, l_assignment_id='||l_assignment_id);
4186: hr_utility.trace('l_effective_end_date='||fnd_date.date_to_displaydate(l_effective_end_date));
4187: hr_utility.trace('l_tax_ref_transfer='||l_tax_ref_transfer);
4188: hr_utility.trace('l_agg_active_start='||fnd_date.date_to_displaydate(l_agg_active_start));
4189: hr_utility.trace('l_agg_active_end='||fnd_date.date_to_displaydate(l_agg_active_end));
4190: -- Bug fix for 3699865 - get the final process date
4191: OPEN csr_asg_last_process_date(l_assignment_id);
4192: FETCH csr_asg_last_process_date INTO l_final_process_date;
4193: CLOSE csr_asg_last_process_date;
4190: -- Bug fix for 3699865 - get the final process date
4191: OPEN csr_asg_last_process_date(l_assignment_id);
4192: FETCH csr_asg_last_process_date INTO l_final_process_date;
4193: CLOSE csr_asg_last_process_date;
4194: hr_utility.trace('After csr_asg_last_process_date, l_final_process_date='||fnd_date.date_to_displaydate(l_final_process_date));
4195: IF l_final_process_date is not null
4196: and nvl(l_tax_ref_transfer, 'N') = 'N'
4197: and l_final_process_date < l_effective_end_date THEN
4198: -- added extra conditions above for 5199746, so that final process date
4213: l_people_group_id,
4214: l_period_of_service_id;
4215: CLOSE csr_basic_asg_info;
4216: --
4217: hr_utility.trace('After csr_basic_asg_info, l_payroll_id='||l_payroll_id);
4218: hr_utility.trace('l_assignment_number='||l_assignment_number);
4219: hr_utility.trace('l_assignment_number='||l_assignment_number);
4220: hr_utility.trace('l_person_id='||l_person_id);
4221: hr_utility.trace('l_person_id='||l_person_id);
4214: l_period_of_service_id;
4215: CLOSE csr_basic_asg_info;
4216: --
4217: hr_utility.trace('After csr_basic_asg_info, l_payroll_id='||l_payroll_id);
4218: hr_utility.trace('l_assignment_number='||l_assignment_number);
4219: hr_utility.trace('l_assignment_number='||l_assignment_number);
4220: hr_utility.trace('l_person_id='||l_person_id);
4221: hr_utility.trace('l_person_id='||l_person_id);
4222: hr_utility.trace('l_organization_id='||l_organization_id);
4215: CLOSE csr_basic_asg_info;
4216: --
4217: hr_utility.trace('After csr_basic_asg_info, l_payroll_id='||l_payroll_id);
4218: hr_utility.trace('l_assignment_number='||l_assignment_number);
4219: hr_utility.trace('l_assignment_number='||l_assignment_number);
4220: hr_utility.trace('l_person_id='||l_person_id);
4221: hr_utility.trace('l_person_id='||l_person_id);
4222: hr_utility.trace('l_organization_id='||l_organization_id);
4223: hr_utility.trace('l_location_id='||l_location_id);
4216: --
4217: hr_utility.trace('After csr_basic_asg_info, l_payroll_id='||l_payroll_id);
4218: hr_utility.trace('l_assignment_number='||l_assignment_number);
4219: hr_utility.trace('l_assignment_number='||l_assignment_number);
4220: hr_utility.trace('l_person_id='||l_person_id);
4221: hr_utility.trace('l_person_id='||l_person_id);
4222: hr_utility.trace('l_organization_id='||l_organization_id);
4223: hr_utility.trace('l_location_id='||l_location_id);
4224: hr_utility.trace('l_people_group_id='||l_people_group_id);
4217: hr_utility.trace('After csr_basic_asg_info, l_payroll_id='||l_payroll_id);
4218: hr_utility.trace('l_assignment_number='||l_assignment_number);
4219: hr_utility.trace('l_assignment_number='||l_assignment_number);
4220: hr_utility.trace('l_person_id='||l_person_id);
4221: hr_utility.trace('l_person_id='||l_person_id);
4222: hr_utility.trace('l_organization_id='||l_organization_id);
4223: hr_utility.trace('l_location_id='||l_location_id);
4224: hr_utility.trace('l_people_group_id='||l_people_group_id);
4225: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4218: hr_utility.trace('l_assignment_number='||l_assignment_number);
4219: hr_utility.trace('l_assignment_number='||l_assignment_number);
4220: hr_utility.trace('l_person_id='||l_person_id);
4221: hr_utility.trace('l_person_id='||l_person_id);
4222: hr_utility.trace('l_organization_id='||l_organization_id);
4223: hr_utility.trace('l_location_id='||l_location_id);
4224: hr_utility.trace('l_people_group_id='||l_people_group_id);
4225: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4226: --
4219: hr_utility.trace('l_assignment_number='||l_assignment_number);
4220: hr_utility.trace('l_person_id='||l_person_id);
4221: hr_utility.trace('l_person_id='||l_person_id);
4222: hr_utility.trace('l_organization_id='||l_organization_id);
4223: hr_utility.trace('l_location_id='||l_location_id);
4224: hr_utility.trace('l_people_group_id='||l_people_group_id);
4225: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4226: --
4227: IF l_assignment_number is null then
4220: hr_utility.trace('l_person_id='||l_person_id);
4221: hr_utility.trace('l_person_id='||l_person_id);
4222: hr_utility.trace('l_organization_id='||l_organization_id);
4223: hr_utility.trace('l_location_id='||l_location_id);
4224: hr_utility.trace('l_people_group_id='||l_people_group_id);
4225: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4226: --
4227: IF l_assignment_number is null then
4228: --
4221: hr_utility.trace('l_person_id='||l_person_id);
4222: hr_utility.trace('l_organization_id='||l_organization_id);
4223: hr_utility.trace('l_location_id='||l_location_id);
4224: hr_utility.trace('l_people_group_id='||l_people_group_id);
4225: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4226: --
4227: IF l_assignment_number is null then
4228: --
4229: hr_utility.trace('Assignment has been updated or terminated');
4225: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4226: --
4227: IF l_assignment_number is null then
4228: --
4229: hr_utility.trace('Assignment has been updated or terminated');
4230: -- 1.The direct match asg and end date has not been found,
4231: -- Check whether there are any asgs current at this date,
4232: -- and use the adjusted end date.
4233: --
4242: l_period_of_service_id;
4243: IF csr_basic_inf_current%FOUND then
4244: -- Set the eff end to the one of the current row
4245: l_effective_end_date := l_adj_eff_end_date;
4246: hr_utility.trace('After csr_basic_inf_current, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4247: hr_utility.trace('l_payroll_id='||l_payroll_id);
4248: hr_utility.trace('l_assignment_number='||l_assignment_number);
4249: hr_utility.trace('l_person_id='||l_person_id);
4250: hr_utility.trace('l_organization_id='||l_organization_id);
4243: IF csr_basic_inf_current%FOUND then
4244: -- Set the eff end to the one of the current row
4245: l_effective_end_date := l_adj_eff_end_date;
4246: hr_utility.trace('After csr_basic_inf_current, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4247: hr_utility.trace('l_payroll_id='||l_payroll_id);
4248: hr_utility.trace('l_assignment_number='||l_assignment_number);
4249: hr_utility.trace('l_person_id='||l_person_id);
4250: hr_utility.trace('l_organization_id='||l_organization_id);
4251: hr_utility.trace('l_location_id='||l_location_id);
4244: -- Set the eff end to the one of the current row
4245: l_effective_end_date := l_adj_eff_end_date;
4246: hr_utility.trace('After csr_basic_inf_current, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4247: hr_utility.trace('l_payroll_id='||l_payroll_id);
4248: hr_utility.trace('l_assignment_number='||l_assignment_number);
4249: hr_utility.trace('l_person_id='||l_person_id);
4250: hr_utility.trace('l_organization_id='||l_organization_id);
4251: hr_utility.trace('l_location_id='||l_location_id);
4252: hr_utility.trace('l_people_group_id='||l_people_group_id);
4245: l_effective_end_date := l_adj_eff_end_date;
4246: hr_utility.trace('After csr_basic_inf_current, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4247: hr_utility.trace('l_payroll_id='||l_payroll_id);
4248: hr_utility.trace('l_assignment_number='||l_assignment_number);
4249: hr_utility.trace('l_person_id='||l_person_id);
4250: hr_utility.trace('l_organization_id='||l_organization_id);
4251: hr_utility.trace('l_location_id='||l_location_id);
4252: hr_utility.trace('l_people_group_id='||l_people_group_id);
4253: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4246: hr_utility.trace('After csr_basic_inf_current, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4247: hr_utility.trace('l_payroll_id='||l_payroll_id);
4248: hr_utility.trace('l_assignment_number='||l_assignment_number);
4249: hr_utility.trace('l_person_id='||l_person_id);
4250: hr_utility.trace('l_organization_id='||l_organization_id);
4251: hr_utility.trace('l_location_id='||l_location_id);
4252: hr_utility.trace('l_people_group_id='||l_people_group_id);
4253: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4254: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4247: hr_utility.trace('l_payroll_id='||l_payroll_id);
4248: hr_utility.trace('l_assignment_number='||l_assignment_number);
4249: hr_utility.trace('l_person_id='||l_person_id);
4250: hr_utility.trace('l_organization_id='||l_organization_id);
4251: hr_utility.trace('l_location_id='||l_location_id);
4252: hr_utility.trace('l_people_group_id='||l_people_group_id);
4253: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4254: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4255: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4248: hr_utility.trace('l_assignment_number='||l_assignment_number);
4249: hr_utility.trace('l_person_id='||l_person_id);
4250: hr_utility.trace('l_organization_id='||l_organization_id);
4251: hr_utility.trace('l_location_id='||l_location_id);
4252: hr_utility.trace('l_people_group_id='||l_people_group_id);
4253: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4254: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4255: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4256: l_assignment_message :=
4249: hr_utility.trace('l_person_id='||l_person_id);
4250: hr_utility.trace('l_organization_id='||l_organization_id);
4251: hr_utility.trace('l_location_id='||l_location_id);
4252: hr_utility.trace('l_people_group_id='||l_people_group_id);
4253: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4254: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4255: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4256: l_assignment_message :=
4257: 'The Assignment has been updated during this process';
4250: hr_utility.trace('l_organization_id='||l_organization_id);
4251: hr_utility.trace('l_location_id='||l_location_id);
4252: hr_utility.trace('l_people_group_id='||l_people_group_id);
4253: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4254: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4255: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4256: l_assignment_message :=
4257: 'The Assignment has been updated during this process';
4258: archive_asg_info(g_assignment_message_eid, l_assignment_message);
4251: hr_utility.trace('l_location_id='||l_location_id);
4252: hr_utility.trace('l_people_group_id='||l_people_group_id);
4253: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4254: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4255: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4256: l_assignment_message :=
4257: 'The Assignment has been updated during this process';
4258: archive_asg_info(g_assignment_message_eid, l_assignment_message);
4259: END IF;
4276: IF csr_basic_inf_term%FOUND then
4277: -- Set the eff end to the adjusted one from the
4278: -- terminated assignment.
4279: l_effective_end_date := l_adj_eff_end_date;
4280: /* hr_utility.trace('After csr_basic_inf_term, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4281: hr_utility.trace('l_payroll_id='||l_payroll_id);
4282: hr_utility.trace('l_assignment_number='||l_assignment_number);
4283: hr_utility.trace('l_person_id='||l_person_id);
4284: hr_utility.trace('l_organization_id='||l_organization_id);
4277: -- Set the eff end to the adjusted one from the
4278: -- terminated assignment.
4279: l_effective_end_date := l_adj_eff_end_date;
4280: /* hr_utility.trace('After csr_basic_inf_term, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4281: hr_utility.trace('l_payroll_id='||l_payroll_id);
4282: hr_utility.trace('l_assignment_number='||l_assignment_number);
4283: hr_utility.trace('l_person_id='||l_person_id);
4284: hr_utility.trace('l_organization_id='||l_organization_id);
4285: hr_utility.trace('l_location_id='||l_location_id);
4278: -- terminated assignment.
4279: l_effective_end_date := l_adj_eff_end_date;
4280: /* hr_utility.trace('After csr_basic_inf_term, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4281: hr_utility.trace('l_payroll_id='||l_payroll_id);
4282: hr_utility.trace('l_assignment_number='||l_assignment_number);
4283: hr_utility.trace('l_person_id='||l_person_id);
4284: hr_utility.trace('l_organization_id='||l_organization_id);
4285: hr_utility.trace('l_location_id='||l_location_id);
4286: hr_utility.trace('l_people_group_id='||l_people_group_id);
4279: l_effective_end_date := l_adj_eff_end_date;
4280: /* hr_utility.trace('After csr_basic_inf_term, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4281: hr_utility.trace('l_payroll_id='||l_payroll_id);
4282: hr_utility.trace('l_assignment_number='||l_assignment_number);
4283: hr_utility.trace('l_person_id='||l_person_id);
4284: hr_utility.trace('l_organization_id='||l_organization_id);
4285: hr_utility.trace('l_location_id='||l_location_id);
4286: hr_utility.trace('l_people_group_id='||l_people_group_id);
4287: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4280: /* hr_utility.trace('After csr_basic_inf_term, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4281: hr_utility.trace('l_payroll_id='||l_payroll_id);
4282: hr_utility.trace('l_assignment_number='||l_assignment_number);
4283: hr_utility.trace('l_person_id='||l_person_id);
4284: hr_utility.trace('l_organization_id='||l_organization_id);
4285: hr_utility.trace('l_location_id='||l_location_id);
4286: hr_utility.trace('l_people_group_id='||l_people_group_id);
4287: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4288: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4281: hr_utility.trace('l_payroll_id='||l_payroll_id);
4282: hr_utility.trace('l_assignment_number='||l_assignment_number);
4283: hr_utility.trace('l_person_id='||l_person_id);
4284: hr_utility.trace('l_organization_id='||l_organization_id);
4285: hr_utility.trace('l_location_id='||l_location_id);
4286: hr_utility.trace('l_people_group_id='||l_people_group_id);
4287: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4288: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4289: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4282: hr_utility.trace('l_assignment_number='||l_assignment_number);
4283: hr_utility.trace('l_person_id='||l_person_id);
4284: hr_utility.trace('l_organization_id='||l_organization_id);
4285: hr_utility.trace('l_location_id='||l_location_id);
4286: hr_utility.trace('l_people_group_id='||l_people_group_id);
4287: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4288: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4289: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4290: l_assignment_message :=
4283: hr_utility.trace('l_person_id='||l_person_id);
4284: hr_utility.trace('l_organization_id='||l_organization_id);
4285: hr_utility.trace('l_location_id='||l_location_id);
4286: hr_utility.trace('l_people_group_id='||l_people_group_id);
4287: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4288: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4289: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4290: l_assignment_message :=
4291: 'The Assignment has been Terminated during this process';
4284: hr_utility.trace('l_organization_id='||l_organization_id);
4285: hr_utility.trace('l_location_id='||l_location_id);
4286: hr_utility.trace('l_people_group_id='||l_people_group_id);
4287: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4288: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4289: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4290: l_assignment_message :=
4291: 'The Assignment has been Terminated during this process';
4292: archive_asg_info(g_assignment_message_eid, l_assignment_message);
4285: hr_utility.trace('l_location_id='||l_location_id);
4286: hr_utility.trace('l_people_group_id='||l_people_group_id);
4287: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4288: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4289: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4290: l_assignment_message :=
4291: 'The Assignment has been Terminated during this process';
4292: archive_asg_info(g_assignment_message_eid, l_assignment_message);
4293: ELSE
4293: ELSE
4294: -- Both cursors not found, so archive a default error msg.
4295: -- This should never be raised but placed to trap error where
4296: -- the asg has been somehow purged.
4297: hr_utility.trace('ASG NOT FOUND: '||to_char(l_assignment_id));
4298: l_assignment_message :=
4299: 'ERROR: Assignment Cannot be found from archived info';
4300: archive_asg_info(g_assignment_message_eid, l_assignment_message);
4301: END IF;
4321: --
4322: END IF;
4323: --
4324: IF l_payroll_id IS NULL THEN
4325: hr_utility.trace('l_assignment_message='||l_assignment_message);
4326: fnd_file.put_line(fnd_file.log, 'Error encountered while processing assignment action '||p_assactid);
4327: fnd_file.put_line(fnd_file.log, l_assignment_message);
4328: l_dummy := write_output(p_assignment_number => l_assignment_number,
4329: p_full_name => l_last_name||', '||l_first_name,
4344: l_payroll_tax_ref := g_pay_tax_ref_tab(l_payroll_id);
4345: l_payroll_tax_dist := g_pay_tax_dist_tab(l_payroll_id);
4346: --
4347: l_number_per_fiscal_yr := g_no_per_fiscal_yr(l_payroll_id);
4348: hr_utility.trace('Payroll Info Cached previously for: '||to_char(l_payroll_id));
4349: --
4350: END;
4351: --
4352: -- Get asg Start date
4349: --
4350: END;
4351: --
4352: -- Get asg Start date
4353: hr_utility.trace('Calling csr_asg_start');
4354: hr_utility.trace('With: '||to_char(l_assignment_id)||','||to_char(l_effective_end_date)||','||to_char(l_payroll_start_year)||','||to_char(l_payroll_end_year));
4355: OPEN csr_asg_start(l_assignment_id, l_effective_end_date,
4356: l_payroll_start_year, l_payroll_end_year);
4357: FETCH csr_asg_start INTO l_effective_start_date;
4350: END;
4351: --
4352: -- Get asg Start date
4353: hr_utility.trace('Calling csr_asg_start');
4354: hr_utility.trace('With: '||to_char(l_assignment_id)||','||to_char(l_effective_end_date)||','||to_char(l_payroll_start_year)||','||to_char(l_payroll_end_year));
4355: OPEN csr_asg_start(l_assignment_id, l_effective_end_date,
4356: l_payroll_start_year, l_payroll_end_year);
4357: FETCH csr_asg_start INTO l_effective_start_date;
4358: CLOSE csr_asg_start;
4355: OPEN csr_asg_start(l_assignment_id, l_effective_end_date,
4356: l_payroll_start_year, l_payroll_end_year);
4357: FETCH csr_asg_start INTO l_effective_start_date;
4358: CLOSE csr_asg_start;
4359: hr_utility.trace('After csr_asg_start, l_effective_start_date='||fnd_date.date_to_displaydate(l_effective_start_date));
4360: --
4361: hr_utility.trace('Action type: '||l_action_type);
4362: --
4363: -- Get basic person info
4357: FETCH csr_asg_start INTO l_effective_start_date;
4358: CLOSE csr_asg_start;
4359: hr_utility.trace('After csr_asg_start, l_effective_start_date='||fnd_date.date_to_displaydate(l_effective_start_date));
4360: --
4361: hr_utility.trace('Action type: '||l_action_type);
4362: --
4363: -- Get basic person info
4364: OPEN csr_person_info(l_person_id);
4365: FETCH csr_person_info INTO l_last_name,
4373: l_pensioner_indicator,
4374: l_aggregated_paye_flag,
4375: l_multiple_asg_flag;
4376: CLOSE csr_person_info;
4377: hr_utility.trace('After csr_person_info, l_last_name='||l_last_name);
4378: hr_utility.trace('l_first_name='||l_first_name);
4379: hr_utility.trace('l_middle_name='||l_middle_name);
4380: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4381: hr_utility.trace('l_title='||l_title);
4374: l_aggregated_paye_flag,
4375: l_multiple_asg_flag;
4376: CLOSE csr_person_info;
4377: hr_utility.trace('After csr_person_info, l_last_name='||l_last_name);
4378: hr_utility.trace('l_first_name='||l_first_name);
4379: hr_utility.trace('l_middle_name='||l_middle_name);
4380: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4381: hr_utility.trace('l_title='||l_title);
4382: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4375: l_multiple_asg_flag;
4376: CLOSE csr_person_info;
4377: hr_utility.trace('After csr_person_info, l_last_name='||l_last_name);
4378: hr_utility.trace('l_first_name='||l_first_name);
4379: hr_utility.trace('l_middle_name='||l_middle_name);
4380: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4381: hr_utility.trace('l_title='||l_title);
4382: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4383: hr_utility.trace('l_ni_number='||l_ni_number);
4376: CLOSE csr_person_info;
4377: hr_utility.trace('After csr_person_info, l_last_name='||l_last_name);
4378: hr_utility.trace('l_first_name='||l_first_name);
4379: hr_utility.trace('l_middle_name='||l_middle_name);
4380: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4381: hr_utility.trace('l_title='||l_title);
4382: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4383: hr_utility.trace('l_ni_number='||l_ni_number);
4384: hr_utility.trace('l_sex='||l_sex);
4377: hr_utility.trace('After csr_person_info, l_last_name='||l_last_name);
4378: hr_utility.trace('l_first_name='||l_first_name);
4379: hr_utility.trace('l_middle_name='||l_middle_name);
4380: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4381: hr_utility.trace('l_title='||l_title);
4382: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4383: hr_utility.trace('l_ni_number='||l_ni_number);
4384: hr_utility.trace('l_sex='||l_sex);
4385: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4378: hr_utility.trace('l_first_name='||l_first_name);
4379: hr_utility.trace('l_middle_name='||l_middle_name);
4380: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4381: hr_utility.trace('l_title='||l_title);
4382: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4383: hr_utility.trace('l_ni_number='||l_ni_number);
4384: hr_utility.trace('l_sex='||l_sex);
4385: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4386: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4379: hr_utility.trace('l_middle_name='||l_middle_name);
4380: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4381: hr_utility.trace('l_title='||l_title);
4382: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4383: hr_utility.trace('l_ni_number='||l_ni_number);
4384: hr_utility.trace('l_sex='||l_sex);
4385: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4386: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4387: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4380: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4381: hr_utility.trace('l_title='||l_title);
4382: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4383: hr_utility.trace('l_ni_number='||l_ni_number);
4384: hr_utility.trace('l_sex='||l_sex);
4385: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4386: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4387: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4388: --
4381: hr_utility.trace('l_title='||l_title);
4382: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4383: hr_utility.trace('l_ni_number='||l_ni_number);
4384: hr_utility.trace('l_sex='||l_sex);
4385: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4386: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4387: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4388: --
4389: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4382: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4383: hr_utility.trace('l_ni_number='||l_ni_number);
4384: hr_utility.trace('l_sex='||l_sex);
4385: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4386: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4387: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4388: --
4389: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4390: hr_utility.trace('l_payroll_tax_dist='||l_payroll_tax_dist);
4383: hr_utility.trace('l_ni_number='||l_ni_number);
4384: hr_utility.trace('l_sex='||l_sex);
4385: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4386: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4387: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4388: --
4389: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4390: hr_utility.trace('l_payroll_tax_dist='||l_payroll_tax_dist);
4391: hr_utility.trace('l_assignment_id='||l_assignment_id);
4385: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4386: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4387: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4388: --
4389: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4390: hr_utility.trace('l_payroll_tax_dist='||l_payroll_tax_dist);
4391: hr_utility.trace('l_assignment_id='||l_assignment_id);
4392: OPEN get_asg_active_range(l_assignment_id, l_payroll_tax_dist||'/'||l_payroll_tax_ref);
4393: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4386: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4387: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4388: --
4389: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4390: hr_utility.trace('l_payroll_tax_dist='||l_payroll_tax_dist);
4391: hr_utility.trace('l_assignment_id='||l_assignment_id);
4392: OPEN get_asg_active_range(l_assignment_id, l_payroll_tax_dist||'/'||l_payroll_tax_ref);
4393: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4394: CLOSE get_asg_active_range;
4387: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4388: --
4389: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4390: hr_utility.trace('l_payroll_tax_dist='||l_payroll_tax_dist);
4391: hr_utility.trace('l_assignment_id='||l_assignment_id);
4392: OPEN get_asg_active_range(l_assignment_id, l_payroll_tax_dist||'/'||l_payroll_tax_ref);
4393: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4394: CLOSE get_asg_active_range;
4395: hr_utility.trace('After get_asg_active_range.');
4391: hr_utility.trace('l_assignment_id='||l_assignment_id);
4392: OPEN get_asg_active_range(l_assignment_id, l_payroll_tax_dist||'/'||l_payroll_tax_ref);
4393: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4394: CLOSE get_asg_active_range;
4395: hr_utility.trace('After get_asg_active_range.');
4396: hr_utility.trace('l_active_start='||fnd_date.date_to_displaydate(l_active_start));
4397: hr_utility.trace('l_active_end='||fnd_date.date_to_displaydate(l_active_end));
4398: --
4399: -- set termination type and date.
4392: OPEN get_asg_active_range(l_assignment_id, l_payroll_tax_dist||'/'||l_payroll_tax_ref);
4393: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4394: CLOSE get_asg_active_range;
4395: hr_utility.trace('After get_asg_active_range.');
4396: hr_utility.trace('l_active_start='||fnd_date.date_to_displaydate(l_active_start));
4397: hr_utility.trace('l_active_end='||fnd_date.date_to_displaydate(l_active_end));
4398: --
4399: -- set termination type and date.
4400: IF l_tax_ref_transfer = 'N' THEN
4393: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4394: CLOSE get_asg_active_range;
4395: hr_utility.trace('After get_asg_active_range.');
4396: hr_utility.trace('l_active_start='||fnd_date.date_to_displaydate(l_active_start));
4397: hr_utility.trace('l_active_end='||fnd_date.date_to_displaydate(l_active_end));
4398: --
4399: -- set termination type and date.
4400: IF l_tax_ref_transfer = 'N' THEN
4401: --
4398: --
4399: -- set termination type and date.
4400: IF l_tax_ref_transfer = 'N' THEN
4401: --
4402: hr_utility.set_location(l_proc,10);
4403: -- not a tax reference transfer.
4404: OPEN csr_termination(l_period_of_service_id,
4405: l_effective_end_date);
4406: FETCH csr_termination INTO l_actual_termination_date,
4406: FETCH csr_termination INTO l_actual_termination_date,
4407: l_last_std_process_date,
4408: l_termination_type;
4409: CLOSE csr_termination;
4410: hr_utility.trace('After csr_termination, l_actual_termination_date='||fnd_date.date_to_displaydate(l_actual_termination_date));
4411: hr_utility.trace('l_last_std_process_date='||fnd_date.date_to_displaydate(l_last_std_process_date));
4412: hr_utility.trace('l_termination_type='||l_termination_type);
4413: IF l_multiple_asg_flag = 'Y' THEN
4414: -- for multiple assignment check if aggregated active end date
4407: l_last_std_process_date,
4408: l_termination_type;
4409: CLOSE csr_termination;
4410: hr_utility.trace('After csr_termination, l_actual_termination_date='||fnd_date.date_to_displaydate(l_actual_termination_date));
4411: hr_utility.trace('l_last_std_process_date='||fnd_date.date_to_displaydate(l_last_std_process_date));
4412: hr_utility.trace('l_termination_type='||l_termination_type);
4413: IF l_multiple_asg_flag = 'Y' THEN
4414: -- for multiple assignment check if aggregated active end date
4415: -- is before the actual termination date and EOY, if yes then set
4408: l_termination_type;
4409: CLOSE csr_termination;
4410: hr_utility.trace('After csr_termination, l_actual_termination_date='||fnd_date.date_to_displaydate(l_actual_termination_date));
4411: hr_utility.trace('l_last_std_process_date='||fnd_date.date_to_displaydate(l_last_std_process_date));
4412: hr_utility.trace('l_termination_type='||l_termination_type);
4413: IF l_multiple_asg_flag = 'Y' THEN
4414: -- for multiple assignment check if aggregated active end date
4415: -- is before the actual termination date and EOY, if yes then set
4416: -- termination date to aggregated active end date
4442: -- the end of current tax year, do nothing here, leave
4443: -- l_termination_date as actual_termination date to report on
4444: -- P14 EDI and to ensure P60 is not generated
4445: NULL;
4446: hr_utility.trace('Employee terminated and last std process before EOY.');
4447: ELSE
4448: -- Employee has been terminated but last_std_process_date is
4449: -- after current tax year hence need to check if P45 has been
4450: -- issued, if yes then leave l_termination_date as actual
4451: -- termination date else ensire l_termination_date is null
4452: -- so that P60 can be generated
4453: l_date_of_manual_p45_issue := NULL;
4454: l_date_of_manual_p45_issue := pay_p45_pkg.get_p45_eit_manual_issue_dt(l_assignment_id);
4455: hr_utility.trace('After get_p45_eit_manual_issue_dt, manual issue date='||fnd_date.date_to_displaydate(l_date_of_manual_p45_issue));
4456: --
4457: IF l_date_of_manual_p45_issue IS NOT NULL
4458: AND l_date_of_manual_p45_issue <= g_end_year THEN
4459: -- manual P45 issued in current tax year hence leave
4471: pay_p45_pkg.get_p45_asg_action_id(l_assignment_id,
4472: l_p45_action_id,
4473: l_p45_issue_date,
4474: l_p45_action_seq);
4475: hr_utility.trace('After get_p45_asg_action_id, l_p45_issue_date='||fnd_date.date_to_displaydate(l_p45_issue_date));
4476: hr_utility.trace('l_p45_action_id='||l_p45_action_id);
4477:
4478: IF l_p45_issue_date IS NULL THEN
4479: pay_p45_pkg.get_p45_agg_asg_action_id(l_assignment_id,
4472: l_p45_action_id,
4473: l_p45_issue_date,
4474: l_p45_action_seq);
4475: hr_utility.trace('After get_p45_asg_action_id, l_p45_issue_date='||fnd_date.date_to_displaydate(l_p45_issue_date));
4476: hr_utility.trace('l_p45_action_id='||l_p45_action_id);
4477:
4478: IF l_p45_issue_date IS NULL THEN
4479: pay_p45_pkg.get_p45_agg_asg_action_id(l_assignment_id,
4480: l_p45_agg_asg_id,
4480: l_p45_agg_asg_id,
4481: l_p45_final_pay_date,
4482: l_p45_issue_date,
4483: l_p45_action_id);
4484: hr_utility.trace('After get_p45_agg_asg_action_id, l_p45_issue_date='||fnd_date.date_to_displaydate(l_p45_issue_date));
4485:
4486: END IF;
4487:
4488: -- Start of BUG 5671777-1
4503: -- p45 has been issued before the end of tax year hence
4504: -- leave l_termination_date as actual_termination_date to
4505: -- report on p14 EDI and to ensure P60 is not generated
4506: NULL;
4507: hr_utility.trace('P45 has been issued before the EOY.');
4508: ELSE
4509: -- p45 has not been issued before EOY hence don't archive
4510: -- actual termination date so that p60 can be generated,
4511: -- set l_termination_date to NULL
4514: END IF; -- l_date_of_manual_p45_issue IS (NOT) NULL
4515: END IF; -- l_last_std_process_date IS (NOT) in the current tax year
4516: END IF; -- Termination date (NOT) NULL
4517: ELSE
4518: hr_utility.set_location(l_proc,20);
4519: -- asg is tax ref transfer
4520: l_termination_type := 'R';
4521: l_termination_date := l_effective_end_date;
4522: END IF;
4522: END IF;
4523: --
4524: -- get last action this year, using stat dates.
4525: --
4526: hr_utility.trace('Calling csr_last_action');
4527: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4528: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4529: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4530: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4523: --
4524: -- get last action this year, using stat dates.
4525: --
4526: hr_utility.trace('Calling csr_last_action');
4527: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4528: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4529: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4530: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4531: hr_utility.trace('End Yr: '||to_char(g_end_year));
4524: -- get last action this year, using stat dates.
4525: --
4526: hr_utility.trace('Calling csr_last_action');
4527: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4528: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4529: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4530: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4531: hr_utility.trace('End Yr: '||to_char(g_end_year));
4532: hr_utility.trace('Tax Ref Xfer: '||l_tax_ref_transfer);
4525: --
4526: hr_utility.trace('Calling csr_last_action');
4527: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4528: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4529: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4530: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4531: hr_utility.trace('End Yr: '||to_char(g_end_year));
4532: hr_utility.trace('Tax Ref Xfer: '||l_tax_ref_transfer);
4533: OPEN csr_last_action(l_assignment_id, l_effective_start_date,
4526: hr_utility.trace('Calling csr_last_action');
4527: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4528: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4529: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4530: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4531: hr_utility.trace('End Yr: '||to_char(g_end_year));
4532: hr_utility.trace('Tax Ref Xfer: '||l_tax_ref_transfer);
4533: OPEN csr_last_action(l_assignment_id, l_effective_start_date,
4534: l_effective_end_date, g_start_year,
4527: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4528: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4529: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4530: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4531: hr_utility.trace('End Yr: '||to_char(g_end_year));
4532: hr_utility.trace('Tax Ref Xfer: '||l_tax_ref_transfer);
4533: OPEN csr_last_action(l_assignment_id, l_effective_start_date,
4534: l_effective_end_date, g_start_year,
4535: g_end_year, l_tax_ref_transfer);
4528: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4529: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4530: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4531: hr_utility.trace('End Yr: '||to_char(g_end_year));
4532: hr_utility.trace('Tax Ref Xfer: '||l_tax_ref_transfer);
4533: OPEN csr_last_action(l_assignment_id, l_effective_start_date,
4534: l_effective_end_date, g_start_year,
4535: g_end_year, l_tax_ref_transfer);
4536: FETCH csr_last_action INTO l_last_asg_action_id,
4535: g_end_year, l_tax_ref_transfer);
4536: FETCH csr_last_action INTO l_last_asg_action_id,
4537: l_last_effective_date;
4538: CLOSE csr_last_action;
4539: hr_utility.trace('After csr_last_action, l_last_asg_action_id='||l_last_asg_action_id);
4540: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4541: --
4542: IF l_last_asg_action_id IS NOT NULL THEN
4543: OPEN csr_action_details(l_last_asg_action_id);
4536: FETCH csr_last_action INTO l_last_asg_action_id,
4537: l_last_effective_date;
4538: CLOSE csr_last_action;
4539: hr_utility.trace('After csr_last_action, l_last_asg_action_id='||l_last_asg_action_id);
4540: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4541: --
4542: IF l_last_asg_action_id IS NOT NULL THEN
4543: OPEN csr_action_details(l_last_asg_action_id);
4544: FETCH csr_action_details into l_action_type;
4542: IF l_last_asg_action_id IS NOT NULL THEN
4543: OPEN csr_action_details(l_last_asg_action_id);
4544: FETCH csr_action_details into l_action_type;
4545: CLOSE csr_action_details;
4546: hr_utility.trace('After csr_action_details, l_action_type='||l_action_type);
4547: END IF;
4548: --
4549: -- Get Non-NI balances and codes used to determine whether to further
4550: -- process the asg.
4594: -- fetch previous_taxable_pay,previous_tax_paid balances -- Bug 6271548
4595: --
4596: IF l_action_type <> 'V' THEN
4597: -- get paye element's run result id from last run
4598: hr_utility.trace('Before csr_tax_last_paye_run, l_last_asg_action_id='||l_last_asg_action_id);
4599: OPEN csr_tax_last_paye_run(l_last_asg_action_id);
4600: FETCH csr_tax_last_paye_run INTO l_tax_paye_run_result_id;
4601: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4602: IF l_tax_paye_run_result_id IS NULL THEN
4597: -- get paye element's run result id from last run
4598: hr_utility.trace('Before csr_tax_last_paye_run, l_last_asg_action_id='||l_last_asg_action_id);
4599: OPEN csr_tax_last_paye_run(l_last_asg_action_id);
4600: FETCH csr_tax_last_paye_run INTO l_tax_paye_run_result_id;
4601: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4602: IF l_tax_paye_run_result_id IS NULL THEN
4603: -- if paye was not calculated in last run then
4604: -- get the latest run in which it was
4605: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
4601: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4602: IF l_tax_paye_run_result_id IS NULL THEN
4603: -- if paye was not calculated in last run then
4604: -- get the latest run in which it was
4605: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
4606: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4607: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4608: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4609: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4602: IF l_tax_paye_run_result_id IS NULL THEN
4603: -- if paye was not calculated in last run then
4604: -- get the latest run in which it was
4605: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
4606: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4607: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4608: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4609: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4610: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4605: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
4606: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4607: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4608: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4609: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4610: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4611: CLOSE csr_tax_latest_paye_run;
4612: ELSE
4613: -- PAYE run result found hence effective date for paye details is
4606: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4607: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4608: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4609: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4610: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4611: CLOSE csr_tax_latest_paye_run;
4612: ELSE
4613: -- PAYE run result found hence effective date for paye details is
4614: -- date of last assignment action
4612: ELSE
4613: -- PAYE run result found hence effective date for paye details is
4614: -- date of last assignment action
4615: l_paye_eff_date := l_last_effective_date;
4616: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4617: END IF;
4618: CLOSE csr_tax_last_paye_run;
4619: -- most people will have had paye calculated on the last run.
4620: -- Pick these up
4617: END IF;
4618: CLOSE csr_tax_last_paye_run;
4619: -- most people will have had paye calculated on the last run.
4620: -- Pick these up
4621: hr_utility.trace('Before csr_tax_last_run, l_last_asg_action_id='||l_last_asg_action_id);
4622: OPEN csr_tax_last_run(l_last_asg_action_id);
4623: FETCH csr_tax_last_run INTO l_tax_run_result_id;
4624: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
4625: IF l_tax_run_result_id IS NULL THEN
4620: -- Pick these up
4621: hr_utility.trace('Before csr_tax_last_run, l_last_asg_action_id='||l_last_asg_action_id);
4622: OPEN csr_tax_last_run(l_last_asg_action_id);
4623: FETCH csr_tax_last_run INTO l_tax_run_result_id;
4624: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
4625: IF l_tax_run_result_id IS NULL THEN
4626: -- find the latest update
4627: hr_utility.trace('calling csr_tax_latest_run, non reversal');
4628: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4623: FETCH csr_tax_last_run INTO l_tax_run_result_id;
4624: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
4625: IF l_tax_run_result_id IS NULL THEN
4626: -- find the latest update
4627: hr_utility.trace('calling csr_tax_latest_run, non reversal');
4628: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4629: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4630: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4631: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4624: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
4625: IF l_tax_run_result_id IS NULL THEN
4626: -- find the latest update
4627: hr_utility.trace('calling csr_tax_latest_run, non reversal');
4628: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4629: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4630: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4631: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4632: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4625: IF l_tax_run_result_id IS NULL THEN
4626: -- find the latest update
4627: hr_utility.trace('calling csr_tax_latest_run, non reversal');
4628: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4629: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4630: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4631: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4632: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4633: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4628: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4629: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4630: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4631: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4632: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4633: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4634: CLOSE csr_tax_latest_run;
4635: ELSE
4636: l_paye_details_eff_date := l_last_effective_date;
4629: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4630: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4631: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4632: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4633: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4634: CLOSE csr_tax_latest_run;
4635: ELSE
4636: l_paye_details_eff_date := l_last_effective_date;
4637: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4633: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4634: CLOSE csr_tax_latest_run;
4635: ELSE
4636: l_paye_details_eff_date := l_last_effective_date;
4637: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4638: END IF;
4639: CLOSE csr_tax_last_run;
4640: ELSE
4641: -- find the latest update, as reversal action
4638: END IF;
4639: CLOSE csr_tax_last_run;
4640: ELSE
4641: -- find the latest update, as reversal action
4642: hr_utility.trace('calling csr_tax_latest_run, reversal');
4643: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4644: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4645: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4646: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4639: CLOSE csr_tax_last_run;
4640: ELSE
4641: -- find the latest update, as reversal action
4642: hr_utility.trace('calling csr_tax_latest_run, reversal');
4643: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4644: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4645: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4646: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4647: CLOSE csr_tax_latest_run;
4640: ELSE
4641: -- find the latest update, as reversal action
4642: hr_utility.trace('calling csr_tax_latest_run, reversal');
4643: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4644: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4645: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4646: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4647: CLOSE csr_tax_latest_run;
4648: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4644: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4645: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4646: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4647: CLOSE csr_tax_latest_run;
4648: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4649: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4650: --
4651: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4652: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4645: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4646: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4647: CLOSE csr_tax_latest_run;
4648: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4649: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4650: --
4651: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4652: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4653: CLOSE csr_tax_latest_paye_run;
4650: --
4651: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4652: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4653: CLOSE csr_tax_latest_paye_run;
4654: hr_utility.trace('After csr_tax_latest_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4655: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4656: END IF;
4657: --
4658: archive_asg_info(g_tax_run_result_id_eid, nvl(l_tax_paye_run_result_id, l_tax_run_result_id));
4651: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4652: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4653: CLOSE csr_tax_latest_paye_run;
4654: hr_utility.trace('After csr_tax_latest_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4655: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4656: END IF;
4657: --
4658: archive_asg_info(g_tax_run_result_id_eid, nvl(l_tax_paye_run_result_id, l_tax_run_result_id));
4659: --
4665: OPEN csr_tax_paye_result(l_tax_paye_run_result_id);
4666: FETCH csr_tax_paye_result INTO l_tax_code, l_w1_m1_indicator,
4667: l_previous_taxable_pay,
4668: l_previous_tax_paid;
4669: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
4670: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
4671: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
4672: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
4673: IF l_tax_code IS NULL THEN
4666: FETCH csr_tax_paye_result INTO l_tax_code, l_w1_m1_indicator,
4667: l_previous_taxable_pay,
4668: l_previous_tax_paid;
4669: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
4670: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
4671: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
4672: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
4673: IF l_tax_code IS NULL THEN
4674: -- Get the details from the element entry on the added criteria that
4667: l_previous_taxable_pay,
4668: l_previous_tax_paid;
4669: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
4670: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
4671: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
4672: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
4673: IF l_tax_code IS NULL THEN
4674: -- Get the details from the element entry on the added criteria that
4675: -- there exists an updating action id on the element_entry. In other
4668: l_previous_tax_paid;
4669: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
4670: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
4671: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
4672: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
4673: IF l_tax_code IS NULL THEN
4674: -- Get the details from the element entry on the added criteria that
4675: -- there exists an updating action id on the element_entry. In other
4676: -- words, an entry achieved using an Update Recurring rule.
4681: l_payroll_end_year, 'Y');
4682: FETCH csr_tax_details_entry INTO l_tax_code, l_w1_m1_indicator,
4683: l_previous_taxable_pay,
4684: l_previous_tax_paid;
4685: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
4686: l_w1_m1_indicator,52);
4687: IF l_tax_code IS NULL THEN
4688: -- no update recurring, so retrieve the details from the run result.
4689: OPEN csr_tax_details_result(l_tax_run_result_id);
4689: OPEN csr_tax_details_result(l_tax_run_result_id);
4690: FETCH csr_tax_details_result INTO l_tax_code, l_w1_m1_indicator,
4691: l_previous_taxable_pay,
4692: l_previous_tax_paid;
4693: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
4694: l_w1_m1_indicator,54);
4695: IF l_tax_code IS NULL THEN
4696: -- If there is still no tax code, use the element entry query
4697: -- without the update recurring criteria.
4700: l_payroll_end_year, 'N');
4701: FETCH csr_tax_details_entry INTO l_tax_code, l_w1_m1_indicator,
4702: l_previous_taxable_pay,
4703: l_previous_tax_paid;
4704: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
4705: l_w1_m1_indicator,56);
4706: IF l_tax_code IS NOT NULL THEN
4707: IF l_aggregated_paye_flag = 'Y' THEN
4708: hr_utility.trace('This is an aggregated assignment.');
4704: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
4705: l_w1_m1_indicator,56);
4706: IF l_tax_code IS NOT NULL THEN
4707: IF l_aggregated_paye_flag = 'Y' THEN
4708: hr_utility.trace('This is an aggregated assignment.');
4709: --
4710: if l_w1_m1_indicator = 'C' then
4711: l_w1_m1_indicator := ' ';
4712: else
4709: --
4710: if l_w1_m1_indicator = 'C' then
4711: l_w1_m1_indicator := ' ';
4712: else
4713: hr_utility.trace('No of periods per year = '||g_no_per_fiscal_yr(l_payroll_id));
4714: if g_no_per_fiscal_yr(l_payroll_id) in (1,2,4,6,12,24) then
4715: l_w1_m1_indicator := 'M';
4716: else
4717: l_w1_m1_indicator := 'W';
4716: else
4717: l_w1_m1_indicator := 'W';
4718: end if;
4719: end if;
4720: hr_utility.trace('Aggregated tax code found so far='||g_agg_balance_totals.tax_code);
4721: hr_utility.trace('g_agg_balance_totals.paye_eff_date = '||fnd_date.date_to_displaydate(g_agg_balance_totals.paye_eff_date));
4722: -- this tax code is from the PAYE Details Entry, since it is an
4723: -- aggregated assignment therefore check if we already have a
4724: -- a tax code from a previously processed assignment of this employee
4717: l_w1_m1_indicator := 'W';
4718: end if;
4719: end if;
4720: hr_utility.trace('Aggregated tax code found so far='||g_agg_balance_totals.tax_code);
4721: hr_utility.trace('g_agg_balance_totals.paye_eff_date = '||fnd_date.date_to_displaydate(g_agg_balance_totals.paye_eff_date));
4722: -- this tax code is from the PAYE Details Entry, since it is an
4723: -- aggregated assignment therefore check if we already have a
4724: -- a tax code from a previously processed assignment of this employee
4725: IF g_agg_balance_totals.tax_code is NULL THEN
4722: -- this tax code is from the PAYE Details Entry, since it is an
4723: -- aggregated assignment therefore check if we already have a
4724: -- a tax code from a previously processed assignment of this employee
4725: IF g_agg_balance_totals.tax_code is NULL THEN
4726: hr_utility.trace('Aggregated tax code found so far is NULL hence store '||l_tax_code||' as the aggregated tax code');
4727: -- No tax code found on other assignments so far therefore store
4728: -- tax code found on element entry of this assignment,
4729: -- this value will be archived against the primary eoy action later
4730: g_agg_balance_totals.tax_code := l_tax_code;
4808:
4809: -- fetch NI x Total/Able balances for checking whether this assignment
4810: -- reported in the P35 report or not. -- Bug 6271548
4811:
4812: hr_utility.trace('fetching NI x Total/Able balances');
4813: -- NI A
4814: l_nia_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nia_tot_id);
4815: l_nia_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
4816: g_nia_able_id);
4845: l_nil_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
4846: g_nil_able_id);
4847:
4848: -- NI F,G,S
4849: hr_utility.trace('fetching NI x Total/Able balances for F,G,S category');
4850: DECLARE
4851: l_sum_ni_tot NUMBER:= 0;
4852: l_sum_ni_able NUMBER:= 0;
4853: l_ni_ers NUMBER:= 0;
4875: l_ni_tot := l_sum_ni_tot;
4876: l_ni_able := l_sum_ni_able;
4877: END;
4878:
4879: hr_utility.trace('l_ni_tot '||l_ni_tot);
4880: hr_utility.trace('l_ni_able '||l_ni_able );
4881: END IF;
4882:
4883: -- added additional conditions to check whether this assignment
4876: l_ni_able := l_sum_ni_able;
4877: END;
4878:
4879: hr_utility.trace('l_ni_tot '||l_ni_tot);
4880: hr_utility.trace('l_ni_able '||l_ni_able );
4881: END IF;
4882:
4883: -- added additional conditions to check whether this assignment
4884: -- reported in the P35 report or not.
4899: l_nie_tot = 0 AND l_nie_able = 0 AND l_nij_tot = 0 AND l_nij_able = 0 AND
4900: l_nil_tot = 0 AND l_nil_able = 0 AND l_ni_tot = 0 AND l_ni_able = 0 ))
4901: -- End of Bug 6271548
4902: THEN
4903: hr_utility.trace('Do Not Process asg any further');
4904: -- Do not process this assignment any further and don't archive
4905: -- info extracted so far
4906: NULL;
4907: ELSE
4904: -- Do not process this assignment any further and don't archive
4905: -- info extracted so far
4906: NULL;
4907: ELSE
4908: hr_utility.set_location(l_proc,30);
4909: -- archive info extracted so far
4910: archive_asg_info(g_termination_type_eid,l_termination_type);
4911: archive_asg_info(g_payroll_id_eid,l_payroll_id);
4912: archive_asg_info(g_assignment_number_eid,l_assignment_number);
4945: g_masg_tax_ref_num := l_payroll_tax_ref;
4946: IF g_permit_number IS NOT NULL THEN
4947: -- a permit was specified through SRS.
4948: --
4949: hr_utility.set_location(l_proc,35);
4950: -- Check that this person does not have assignments in
4951: -- different permits:
4952: hr_utility.trace('Calling csr_get_invalid_multiple_asg');
4953: OPEN csr_get_invalid_multiple_asg(l_person_id, l_payroll_id,
4948: --
4949: hr_utility.set_location(l_proc,35);
4950: -- Check that this person does not have assignments in
4951: -- different permits:
4952: hr_utility.trace('Calling csr_get_invalid_multiple_asg');
4953: OPEN csr_get_invalid_multiple_asg(l_person_id, l_payroll_id,
4954: l_payroll_start_year,
4955: l_payroll_end_year,
4956: l_payroll_tax_ref);
4962: END IF; -- 1st asg
4963: END IF;
4964: IF g_has_non_extracted_masgs THEN
4965: -- error the assignment
4966: hr_utility.set_message(801, 'PAY_78000_MULTIPLE_PERMIT_ASG');
4967: hr_utility.raise_error;
4968: END IF;
4969: END IF; -- End of Multiple Assignment Logic Part I
4970: --
4963: END IF;
4964: IF g_has_non_extracted_masgs THEN
4965: -- error the assignment
4966: hr_utility.set_message(801, 'PAY_78000_MULTIPLE_PERMIT_ASG');
4967: hr_utility.raise_error;
4968: END IF;
4969: END IF; -- End of Multiple Assignment Logic Part I
4970: --
4971: OPEN csr_director(l_person_id);
5005: OPEN csr_country_name(l_country);
5006: FETCH csr_country_name INTO l_country_name;
5007: CLOSE csr_country_name;
5008: --
5009: hr_utility.set_location(l_proc,40);
5010: --
5011: -- Archive more asg info.
5012: hr_utility.trace('Archiving more info');
5013: archive_asg_info(g_last_name_eid,l_last_name);
5008: --
5009: hr_utility.set_location(l_proc,40);
5010: --
5011: -- Archive more asg info.
5012: hr_utility.trace('Archiving more info');
5013: archive_asg_info(g_last_name_eid,l_last_name);
5014: archive_asg_info(g_first_name_eid,l_first_name);
5015: archive_asg_info(g_middle_name_eid,l_middle_name);
5016: archive_asg_info(g_date_of_birth_eid,
5033: archive_asg_info(g_county_eid,l_county);
5034: archive_asg_info(g_country_eid,l_country_name); -- 4011263
5035: archive_asg_info(g_postal_code_eid,l_postal_code);
5036: --
5037: hr_utility.trace('End of archiving person and address info');
5038: -- Get more Non-NI balances and codes
5039: l_paye := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_paye_id);
5040: l_widow := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_widow_id);
5041: hr_utility.set_location(l_proc,50);
5037: hr_utility.trace('End of archiving person and address info');
5038: -- Get more Non-NI balances and codes
5039: l_paye := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_paye_id);
5040: l_widow := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_widow_id);
5041: hr_utility.set_location(l_proc,50);
5042: hr_utility.trace('l_paye='||l_paye||', g_paye_id='||g_paye_id);
5043: IF nvl(l_aggregated_paye_flag,'N') <> 'Y' THEN
5044: -- Not Aggregated PAYE, so archive all values as prior to
5045: -- introduction of agg PAYE.
5038: -- Get more Non-NI balances and codes
5039: l_paye := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_paye_id);
5040: l_widow := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_widow_id);
5041: hr_utility.set_location(l_proc,50);
5042: hr_utility.trace('l_paye='||l_paye||', g_paye_id='||g_paye_id);
5043: IF nvl(l_aggregated_paye_flag,'N') <> 'Y' THEN
5044: -- Not Aggregated PAYE, so archive all values as prior to
5045: -- introduction of agg PAYE.
5046: hr_utility.trace('Not Aggregated PAYE, archive at asg level');
5042: hr_utility.trace('l_paye='||l_paye||', g_paye_id='||g_paye_id);
5043: IF nvl(l_aggregated_paye_flag,'N') <> 'Y' THEN
5044: -- Not Aggregated PAYE, so archive all values as prior to
5045: -- introduction of agg PAYE.
5046: hr_utility.trace('Not Aggregated PAYE, archive at asg level');
5047: /* IF l_sex = 'F' Then rerstored as a part of 2987008
5048: was earlier commented out for 2003,
5049: And now commented out again for 2005, BUG 4011263 */
5050: archive_asg_info(g_smp_eid,l_smp);
5091: OPEN get_week_53_start(l_payroll_id);
5092: FETCH get_week_53_start INTO l_week_53_start;
5093: CLOSE get_week_53_start;
5094: --
5095: hr_utility.trace('After get_week_53_start, l_week_53_start='||
5096: fnd_date.date_to_displaydate(l_week_53_start));
5097: IF nvl(l_week_53_start, hr_general.end_of_time) <= l_active_end THEN
5098: l_week_53_indicator := substr(to_char(l_payroll_max_period_number),2,1);
5099: END IF;
5100: else
5101: l_week_53_indicator := ' ';
5102: end if;
5103: --
5104: hr_utility.trace('Before getting paye details from run results.');
5105: hr_utility.trace('l_action_type='||l_action_type);
5106: --
5107: -- 2166991: Check the Action type, if reversal then the
5108: -- csr_tax_last_run cannot be used, call csr_tax_latest_run.
5101: l_week_53_indicator := ' ';
5102: end if;
5103: --
5104: hr_utility.trace('Before getting paye details from run results.');
5105: hr_utility.trace('l_action_type='||l_action_type);
5106: --
5107: -- 2166991: Check the Action type, if reversal then the
5108: -- csr_tax_last_run cannot be used, call csr_tax_latest_run.
5109: -- Separate clause for V types for performance.
5114: -- Archiving the tax code details depending on the global g_paye_archive
5115: IF g_paye_archive = FALSE THEN
5116: IF l_action_type <> 'V' THEN
5117: -- get paye element's run result id from last run
5118: hr_utility.trace('Before csr_tax_last_paye_run, l_last_asg_action_id='||l_last_asg_action_id);
5119: OPEN csr_tax_last_paye_run(l_last_asg_action_id);
5120: FETCH csr_tax_last_paye_run INTO l_tax_paye_run_result_id;
5121: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5122: IF l_tax_paye_run_result_id IS NULL THEN
5117: -- get paye element's run result id from last run
5118: hr_utility.trace('Before csr_tax_last_paye_run, l_last_asg_action_id='||l_last_asg_action_id);
5119: OPEN csr_tax_last_paye_run(l_last_asg_action_id);
5120: FETCH csr_tax_last_paye_run INTO l_tax_paye_run_result_id;
5121: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5122: IF l_tax_paye_run_result_id IS NULL THEN
5123: -- if paye was not calculated in last run then
5124: -- get the latest run in which it was
5125: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
5121: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5122: IF l_tax_paye_run_result_id IS NULL THEN
5123: -- if paye was not calculated in last run then
5124: -- get the latest run in which it was
5125: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
5126: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5127: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5128: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5129: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5122: IF l_tax_paye_run_result_id IS NULL THEN
5123: -- if paye was not calculated in last run then
5124: -- get the latest run in which it was
5125: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
5126: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5127: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5128: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5129: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5130: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5125: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
5126: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5127: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5128: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5129: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5130: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5131: CLOSE csr_tax_latest_paye_run;
5132: ELSE
5133: -- PAYE run result found hence effective date for paye details is
5126: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5127: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5128: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5129: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5130: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5131: CLOSE csr_tax_latest_paye_run;
5132: ELSE
5133: -- PAYE run result found hence effective date for paye details is
5134: -- date of last assignment action
5132: ELSE
5133: -- PAYE run result found hence effective date for paye details is
5134: -- date of last assignment action
5135: l_paye_eff_date := l_last_effective_date;
5136: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5137: END IF;
5138: CLOSE csr_tax_last_paye_run;
5139: -- most people will have had paye calculated on the last run.
5140: -- Pick these up
5137: END IF;
5138: CLOSE csr_tax_last_paye_run;
5139: -- most people will have had paye calculated on the last run.
5140: -- Pick these up
5141: hr_utility.trace('Before csr_tax_last_run, l_last_asg_action_id='||l_last_asg_action_id);
5142: OPEN csr_tax_last_run(l_last_asg_action_id);
5143: FETCH csr_tax_last_run INTO l_tax_run_result_id;
5144: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
5145: IF l_tax_run_result_id IS NULL THEN
5140: -- Pick these up
5141: hr_utility.trace('Before csr_tax_last_run, l_last_asg_action_id='||l_last_asg_action_id);
5142: OPEN csr_tax_last_run(l_last_asg_action_id);
5143: FETCH csr_tax_last_run INTO l_tax_run_result_id;
5144: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
5145: IF l_tax_run_result_id IS NULL THEN
5146: -- find the latest update
5147: hr_utility.trace('calling csr_tax_latest_run, non reversal');
5148: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5143: FETCH csr_tax_last_run INTO l_tax_run_result_id;
5144: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
5145: IF l_tax_run_result_id IS NULL THEN
5146: -- find the latest update
5147: hr_utility.trace('calling csr_tax_latest_run, non reversal');
5148: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5149: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5150: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5151: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5144: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
5145: IF l_tax_run_result_id IS NULL THEN
5146: -- find the latest update
5147: hr_utility.trace('calling csr_tax_latest_run, non reversal');
5148: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5149: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5150: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5151: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5152: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5145: IF l_tax_run_result_id IS NULL THEN
5146: -- find the latest update
5147: hr_utility.trace('calling csr_tax_latest_run, non reversal');
5148: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5149: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5150: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5151: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5152: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5153: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5148: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5149: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5150: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5151: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5152: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5153: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5154: CLOSE csr_tax_latest_run;
5155: ELSE
5156: l_paye_details_eff_date := l_last_effective_date;
5149: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5150: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5151: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5152: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5153: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5154: CLOSE csr_tax_latest_run;
5155: ELSE
5156: l_paye_details_eff_date := l_last_effective_date;
5157: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5153: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5154: CLOSE csr_tax_latest_run;
5155: ELSE
5156: l_paye_details_eff_date := l_last_effective_date;
5157: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5158: END IF;
5159: CLOSE csr_tax_last_run;
5160: ELSE
5161: -- find the latest update, as reversal action
5158: END IF;
5159: CLOSE csr_tax_last_run;
5160: ELSE
5161: -- find the latest update, as reversal action
5162: hr_utility.trace('calling csr_tax_latest_run, reversal');
5163: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5164: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5165: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5166: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5159: CLOSE csr_tax_last_run;
5160: ELSE
5161: -- find the latest update, as reversal action
5162: hr_utility.trace('calling csr_tax_latest_run, reversal');
5163: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5164: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5165: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5166: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5167: CLOSE csr_tax_latest_run;
5160: ELSE
5161: -- find the latest update, as reversal action
5162: hr_utility.trace('calling csr_tax_latest_run, reversal');
5163: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5164: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5165: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5166: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5167: CLOSE csr_tax_latest_run;
5168: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5164: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5165: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5166: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5167: CLOSE csr_tax_latest_run;
5168: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5169: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5170: --
5171: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5172: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5165: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5166: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5167: CLOSE csr_tax_latest_run;
5168: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5169: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5170: --
5171: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5172: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5173: CLOSE csr_tax_latest_paye_run;
5170: --
5171: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5172: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5173: CLOSE csr_tax_latest_paye_run;
5174: hr_utility.trace('After csr_tax_latest_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5175: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5176: END IF;
5177: --
5178: --
5171: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5172: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5173: CLOSE csr_tax_latest_paye_run;
5174: hr_utility.trace('After csr_tax_latest_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5175: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5176: END IF;
5177: --
5178: --
5179: -- archive_asg_info(g_tax_run_result_id_eid, nvl(l_tax_paye_run_result_id, l_tax_run_result_id));
5186: OPEN csr_tax_paye_result(l_tax_paye_run_result_id);
5187: FETCH csr_tax_paye_result INTO l_tax_code, l_w1_m1_indicator,
5188: l_previous_taxable_pay,
5189: l_previous_tax_paid;
5190: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
5191: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
5192: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
5193: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
5194: IF l_tax_code IS NULL THEN
5187: FETCH csr_tax_paye_result INTO l_tax_code, l_w1_m1_indicator,
5188: l_previous_taxable_pay,
5189: l_previous_tax_paid;
5190: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
5191: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
5192: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
5193: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
5194: IF l_tax_code IS NULL THEN
5195: -- Get the details from the element entry on the added criteria that
5188: l_previous_taxable_pay,
5189: l_previous_tax_paid;
5190: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
5191: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
5192: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
5193: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
5194: IF l_tax_code IS NULL THEN
5195: -- Get the details from the element entry on the added criteria that
5196: -- there exists an updating action id on the element_entry. In other
5189: l_previous_tax_paid;
5190: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
5191: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
5192: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
5193: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
5194: IF l_tax_code IS NULL THEN
5195: -- Get the details from the element entry on the added criteria that
5196: -- there exists an updating action id on the element_entry. In other
5197: -- words, an entry achieved using an Update Recurring rule.
5202: l_payroll_end_year, 'Y');
5203: FETCH csr_tax_details_entry INTO l_tax_code, l_w1_m1_indicator,
5204: l_previous_taxable_pay,
5205: l_previous_tax_paid;
5206: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
5207: l_w1_m1_indicator,52);
5208: IF l_tax_code IS NULL THEN
5209: -- no update recurring, so retrieve the details from the run result.
5210: OPEN csr_tax_details_result(l_tax_run_result_id);
5210: OPEN csr_tax_details_result(l_tax_run_result_id);
5211: FETCH csr_tax_details_result INTO l_tax_code, l_w1_m1_indicator,
5212: l_previous_taxable_pay,
5213: l_previous_tax_paid;
5214: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
5215: l_w1_m1_indicator,54);
5216: IF l_tax_code IS NULL THEN
5217: -- If there is still no tax code, use the element entry query
5218: -- without the update recurring criteria.
5221: l_payroll_end_year, 'N');
5222: FETCH csr_tax_details_entry INTO l_tax_code, l_w1_m1_indicator,
5223: l_previous_taxable_pay,
5224: l_previous_tax_paid;
5225: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
5226: l_w1_m1_indicator,56);
5227: IF l_tax_code IS NOT NULL THEN
5228: IF l_aggregated_paye_flag = 'Y' THEN
5229: hr_utility.trace('This is an aggregated assignment.');
5225: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
5226: l_w1_m1_indicator,56);
5227: IF l_tax_code IS NOT NULL THEN
5228: IF l_aggregated_paye_flag = 'Y' THEN
5229: hr_utility.trace('This is an aggregated assignment.');
5230: --
5231: if l_w1_m1_indicator = 'C' then
5232: l_w1_m1_indicator := ' ';
5233: else
5230: --
5231: if l_w1_m1_indicator = 'C' then
5232: l_w1_m1_indicator := ' ';
5233: else
5234: hr_utility.trace('No of periods per year = '||g_no_per_fiscal_yr(l_payroll_id));
5235: if g_no_per_fiscal_yr(l_payroll_id) in (1,2,4,6,12,24) then
5236: l_w1_m1_indicator := 'M';
5237: else
5238: l_w1_m1_indicator := 'W';
5237: else
5238: l_w1_m1_indicator := 'W';
5239: end if;
5240: end if;
5241: hr_utility.trace('Aggregated tax code found so far='||g_agg_balance_totals.tax_code);
5242: hr_utility.trace('g_agg_balance_totals.paye_eff_date = '||fnd_date.date_to_displaydate(g_agg_balance_totals.paye_eff_date));
5243: -- this tax code is from the PAYE Details Entry, since it is an
5244: -- aggregated assignment therefore check if we already have a
5245: -- a tax code from a previously processed assignment of this employee
5238: l_w1_m1_indicator := 'W';
5239: end if;
5240: end if;
5241: hr_utility.trace('Aggregated tax code found so far='||g_agg_balance_totals.tax_code);
5242: hr_utility.trace('g_agg_balance_totals.paye_eff_date = '||fnd_date.date_to_displaydate(g_agg_balance_totals.paye_eff_date));
5243: -- this tax code is from the PAYE Details Entry, since it is an
5244: -- aggregated assignment therefore check if we already have a
5245: -- a tax code from a previously processed assignment of this employee
5246: IF g_agg_balance_totals.tax_code is NULL THEN
5243: -- this tax code is from the PAYE Details Entry, since it is an
5244: -- aggregated assignment therefore check if we already have a
5245: -- a tax code from a previously processed assignment of this employee
5246: IF g_agg_balance_totals.tax_code is NULL THEN
5247: hr_utility.trace('Aggregated tax code found so far is NULL hence store '||l_tax_code||' as the aggregated tax code');
5248: -- No tax code found on other assignments so far therefore store
5249: -- tax code found on element entry of this assignment,
5250: -- this value will be archived against the primary eoy action later
5251: g_agg_balance_totals.tax_code := l_tax_code;
5329: --
5330: g_paye_archive := TRUE;
5331: END IF;
5332: -- End of Bug 6271548
5333: hr_utility.set_location(l_proc,60);
5334: /**************************************/
5335: /* assigned blank space to l_tax_code */
5336: /**************************************/
5337: if (l_tax_code IS NULL) then
5370: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5371: g_nia_able_id); */
5372: IF nvl(l_nia_tot,0) <> 0 or nvl(l_nia_able,0) <> 0 THEN
5373: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5374: hr_utility.set_location(l_proc,70);
5375:
5376: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nia_id);
5377: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5378: g_nia_lel_id);
5406: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5407: g_nib_able_id);*/
5408: IF nvl(l_nib_tot,0) <> 0 or nvl(l_nib_able,0) <> 0 THEN
5409: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5410: hr_utility.set_location(l_proc,80);
5411: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5412: g_nib_id);
5413: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5414: g_nib_lel_id);
5442: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5443: g_nic_able_id);*/
5444: IF nvl(l_nic_tot,0) <> 0 or nvl(l_nic_able,0) <> 0 THEN
5445: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5446: hr_utility.set_location(l_proc,90);
5447: --archive_ni_values('C', l_ni_tot);
5448: -- Bug Fix 1976152, commented the above stmt, and included the below code
5449: -- to get the balance for NI C Employers Rebate and modified the call to
5450: -- archive_ni_values procedure
5493: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5494: g_nid_able_id);*/
5495: IF nvl(l_nid_tot,0) <> 0 or nvl(l_nid_able,0) <> 0 THEN
5496: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5497: hr_utility.set_location(l_proc,100);
5498: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nid_id);
5499: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5500: g_nid_lel_id);
5501: l_ni_able_uel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5536: /*l_ni_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_tot_id);
5537: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_able_id);*/
5538: IF nvl(l_nie_tot,0) <> 0 or nvl(l_nie_able,0) <> 0 THEN
5539: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5540: hr_utility.set_location(l_proc,110);
5541: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_id);
5542: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_lel_id);
5543: l_ni_able_uel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_uel_id);
5544: --EOY 07/08 Begin
5568: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5569: g_nij_able_id);*/
5570: IF nvl(l_nij_tot,0) <> 0 or nvl(l_nij_able,0) <> 0 THEN
5571: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5572: hr_utility.set_location(l_proc,130);
5573: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nij_id);
5574: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5575: g_nij_lel_id);
5576: l_ni_able_uel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5602: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5603: g_nil_able_id);*/
5604: IF nvl(l_nil_tot,0) <> 0 or nvl(l_nil_able,0) <> 0 THEN
5605: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5606: hr_utility.set_location(l_proc,140);
5607: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nil_id);
5608: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5609: g_nil_lel_id);
5610: l_ni_able_uel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5641: OR HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(l_last_asg_action_id,'S') = 1
5642: THEN
5643: 4221300 */
5644: -- F/G/S Total(s) exist(s)
5645: hr_utility.set_location(l_proc,120);
5646: -- open cursor and populate year end values
5647: DECLARE
5648: l_sum_ni_tot NUMBER:= 0;
5649: l_sum_ni_ees NUMBER:= 0;
5675: OR l_store_scon <> l_scon)
5676: AND csr_get_scon_bal%rowcount > 0 THEN
5677: -- first row will not come here due to null trap with cat and scon
5678: -- write values from store
5679: hr_utility.trace('l_sum_ni_tot='||l_sum_ni_tot);
5680: hr_utility.trace('l_sum_ni_able='||l_sum_ni_able);
5681: -- Add following IF condition for 4221300
5682: IF (nvl(l_sum_ni_tot,0) <> 0 or nvl(l_sum_ni_able,0) <> 0) THEN
5683: archive_ni_values(l_store_cat, l_sum_ni_tot, l_sum_ni_able,
5676: AND csr_get_scon_bal%rowcount > 0 THEN
5677: -- first row will not come here due to null trap with cat and scon
5678: -- write values from store
5679: hr_utility.trace('l_sum_ni_tot='||l_sum_ni_tot);
5680: hr_utility.trace('l_sum_ni_able='||l_sum_ni_able);
5681: -- Add following IF condition for 4221300
5682: IF (nvl(l_sum_ni_tot,0) <> 0 or nvl(l_sum_ni_able,0) <> 0) THEN
5683: archive_ni_values(l_store_cat, l_sum_ni_tot, l_sum_ni_able,
5684: l_sum_ni_ees, l_sum_ni_able_lel,
5740: OPEN csr_current_cat(l_assignment_id, l_effective_end_date,
5741: l_payroll_end_year);
5742: FETCH csr_current_cat INTO l_ni_cat, l_scon;
5743: IF csr_current_cat%found THEN
5744: hr_utility.set_location(l_proc,160);
5745: archive_ni_values(p_ni_cat => l_ni_cat,
5746: p_tot_contribs => 0,
5747: p_scon => l_scon);
5748: ELSE
5756: -- If Aggregated PAYE flag is set, use the first assignment ID
5757: -- in the sequence, this is the rule for AggPAYE. nvl the
5758: -- assignment ID with the max number for the field.
5759: IF nvl(l_aggregated_paye_flag,'N') = 'Y' THEN
5760: hr_utility.set_location(l_proc,162);
5761: IF l_assignment_id < nvl(g_min_assignment_id,999999999) THEN
5762: hr_utility.set_location(l_proc,163);
5763: g_primary_action := p_assactid;
5764: g_min_assignment_id := l_assignment_id;
5758: -- assignment ID with the max number for the field.
5759: IF nvl(l_aggregated_paye_flag,'N') = 'Y' THEN
5760: hr_utility.set_location(l_proc,162);
5761: IF l_assignment_id < nvl(g_min_assignment_id,999999999) THEN
5762: hr_utility.set_location(l_proc,163);
5763: g_primary_action := p_assactid;
5764: g_min_assignment_id := l_assignment_id;
5765: END IF;
5766: ELSE
5774: -- g_max_gross_pay is null, i.e., first assignment of the emp
5775: IF nvl(l_gross,0) >= g_max_gross_pay
5776: OR g_max_gross_pay is null THEN
5777: -- store new max gross pay and asg act id
5778: hr_utility.set_location(l_proc,165);
5779: g_primary_action := p_assactid;
5780: g_max_gross_pay := nvl(l_gross,0);
5781: END IF;
5782: END IF;
5798: g_last_multi_asg_eid) = 'Y'
5799: THEN
5800: -- This is the last asg for the person in this tax ref
5801: -- archive the primary assignment flag against the primary action
5802: hr_utility.set_location(l_proc,170);
5803: ff_archive_api.create_archive_item
5804: (p_archive_item_id => l_archive_item_id,
5805: p_user_entity_id => g_eoy_primary_flag_eid,
5806: p_archive_value => 'Y',
5813: BEGIN
5814: l_index1:=0;
5815: LOOP -- actions
5816: l_index1 := l_index1 + 1;
5817: hr_utility.set_location(l_proc||' '||g_asg_actions(l_index1),180);
5818: IF g_asg_actions(l_index1) = g_primary_action THEN
5819: -- This is the Primary action so Archive Balance values.
5820: -- Firstly the Aggregated PAYE values if necessary.
5821: IF nvl(l_aggregated_paye_flag,'N') = 'Y' then
5819: -- This is the Primary action so Archive Balance values.
5820: -- Firstly the Aggregated PAYE values if necessary.
5821: IF nvl(l_aggregated_paye_flag,'N') = 'Y' then
5822: BEGIN
5823: hr_utility.set_location(l_proc,181);
5824: -- Get all values from the global table.
5825: /* IF l_sex = 'F' THEN rerstored as a part of 2987008
5826: was earlier commented out for 2003,
5827: And Commented out again for EOY 2005, Bug 4011263 */
5870: --
5871: IF g_agg_balance_totals.superann < 0 THEN
5872: archive_asg_info(g_superannuation_refund_eid,'R',g_primary_action);
5873: END IF;
5874: hr_utility.set_location(l_proc,182);
5875: --
5876: EXCEPTION WHEN NO_DATA_FOUND THEN
5877: NULL; -- All values archived
5878: END; -- Agg PAYE Block
5890: else
5891: l_ni_refund_flag := '';
5892: end if;
5893: IF g_ni_balance_totals(l_index2).scon IS NOT NULL THEN
5894: hr_utility.set_location(l_proc||' '||
5895: g_ni_balance_totals(l_index2).ni_cat||' '||
5896: g_ni_balance_totals(l_index2).scon,190);
5897: archive_ni_value(g_ni_scon_earnings_eid,
5898: g_ni_balance_totals(l_index2).earnings,2,
5946: g_ni_balance_totals(l_index2).ni_cat,
5947: g_ni_balance_totals(l_index2).scon,
5948: g_primary_action);
5949: ELSE
5950: hr_utility.set_location(l_proc||' '||
5951: g_ni_balance_totals(l_index2).ni_cat,200);
5952: archive_ni_value(g_ni_earnings_eid,
5953: g_ni_balance_totals(l_index2).earnings,2,
5954: g_ni_balance_totals(l_index2).ni_cat,
5998: -- all NI values archived
5999: NULL;
6000: END; -- NI values block
6001: ELSE
6002: hr_utility.set_location(l_proc,210);
6003: -- not primary action, so archive reportable cat X 'row'.
6004: -- Need to archive the Total Contributions item as 0 as the
6005: -- values view relies on this item being in the archive in order
6006: -- for a row to be returned (it uses this item to obtain the
6014: NULL;
6015: END; -- action block
6016: --
6017: IF l_reportable_ni_archived THEN
6018: hr_utility.trace('Reportable NI values archived against primary action='||g_primary_action);
6019: archive_asg_info(g_reportable_ni_eid, 'Y', g_primary_action);
6020: END IF;
6021: --
6022: empty_masg_cache;
6023: END IF; -- l_last_multi_asg
6024: ELSE -- End of Multiple Assignment Logic Part IV
6025: -- This is not an employee with aggregated multiple assignments
6026: IF l_reportable_ni_archived THEN
6027: hr_utility.trace('Reportable NI values archived against current action='||p_assactid);
6028: archive_asg_info(g_reportable_ni_eid, 'Y');
6029: END IF;
6030: END IF;
6031: --
6028: archive_asg_info(g_reportable_ni_eid, 'Y');
6029: END IF;
6030: END IF;
6031: --
6032: hr_utility.set_location(' Leaving: '||l_proc,220);
6033: EXCEPTION
6034: WHEN ASG_ACTION_ERROR THEN
6035: hr_utility.trace('ASG_ACTION_ERROR exception raised.');
6036: hr_utility.set_location(' Leaving: '||l_proc,230);
6031: --
6032: hr_utility.set_location(' Leaving: '||l_proc,220);
6033: EXCEPTION
6034: WHEN ASG_ACTION_ERROR THEN
6035: hr_utility.trace('ASG_ACTION_ERROR exception raised.');
6036: hr_utility.set_location(' Leaving: '||l_proc,230);
6037: raise;
6038: END archive_code;
6039: --
6032: hr_utility.set_location(' Leaving: '||l_proc,220);
6033: EXCEPTION
6034: WHEN ASG_ACTION_ERROR THEN
6035: hr_utility.trace('ASG_ACTION_ERROR exception raised.');
6036: hr_utility.set_location(' Leaving: '||l_proc,230);
6037: raise;
6038: END archive_code;
6039: --
6040: PROCEDURE extract_item_report_format(p_user_entity_name in varchar2,
6058: invalid_archive_type EXCEPTION;
6059: --
6060: BEGIN
6061: --
6062: hr_utility.trace('Extract Item: '||p_user_entity_name);
6063: -- Retrieve user entity ID, also validates the entity.
6064: --
6065: OPEN csr_get_user_entity_id(p_user_entity_name);
6066: FETCH csr_get_user_entity_id INTO l_user_entity_id;
6138: END;
6139: --
6140: EXCEPTION
6141: WHEN invalid_archive_type THEN
6142: hr_utility.set_message(800, 'FF_34958_INVALID_ARCHIVE_TYPE');
6143: hr_utility.raise_error;
6144: --
6145: END extract_item_report_format;
6146: --
6139: --
6140: EXCEPTION
6141: WHEN invalid_archive_type THEN
6142: hr_utility.set_message(800, 'FF_34958_INVALID_ARCHIVE_TYPE');
6143: hr_utility.raise_error;
6144: --
6145: END extract_item_report_format;
6146: --
6147: END pay_gb_eoy_archive;