[Home] [Help]
1137: --
1138: PROCEDURE write_output_header IS
1139: BEGIN
1140: --
1141: hr_utility.set_location('pay_gb_eoy_archive.write_output_header', 10);
1142: fnd_file.put_line(fnd_file.output, rpad(' ', 41)||
1143: 'End Of Year Process - Errors and Warnings Report'||
1144: rpad(' ', 30)||fnd_date.date_to_displaydate(sysdate));
1145: fnd_file.put_line(fnd_file.output, ' ');
1155: fnd_file.put_line(fnd_file.output, rpad('-', 14, '-') || ' ' ||
1156: rpad('-', 30, '-') || ' ' || rpad('-', 10, '-') || ' ' ||
1157: rpad('-', 75, '-'));
1158: g_output_header := FALSE;
1159: hr_utility.set_location('pay_gb_eoy_archive.write_output_header', 100);
1160: END write_output_header;
1161: --
1162: FUNCTION write_output(p_assignment_number IN VARCHAR2,
1163: p_full_name IN VARCHAR2,
1167: l_err_warn VARCHAR2(10);
1168: l_message VARCHAR2(250);
1169: BEGIN
1170: --
1171: hr_utility.set_location('pay_gb_eoy_archive.write_output', 1);
1172: hr_utility.trace('p_assignment_number='||p_assignment_number);
1173: hr_utility.trace('p_full_name='||p_full_name);
1174: hr_utility.trace('p_message_type='||p_message_type);
1175: hr_utility.trace('p_message='||p_message);
1168: l_message VARCHAR2(250);
1169: BEGIN
1170: --
1171: hr_utility.set_location('pay_gb_eoy_archive.write_output', 1);
1172: hr_utility.trace('p_assignment_number='||p_assignment_number);
1173: hr_utility.trace('p_full_name='||p_full_name);
1174: hr_utility.trace('p_message_type='||p_message_type);
1175: hr_utility.trace('p_message='||p_message);
1176: --
1169: BEGIN
1170: --
1171: hr_utility.set_location('pay_gb_eoy_archive.write_output', 1);
1172: hr_utility.trace('p_assignment_number='||p_assignment_number);
1173: hr_utility.trace('p_full_name='||p_full_name);
1174: hr_utility.trace('p_message_type='||p_message_type);
1175: hr_utility.trace('p_message='||p_message);
1176: --
1177: -- strip ':' from the error message
1170: --
1171: hr_utility.set_location('pay_gb_eoy_archive.write_output', 1);
1172: hr_utility.trace('p_assignment_number='||p_assignment_number);
1173: hr_utility.trace('p_full_name='||p_full_name);
1174: hr_utility.trace('p_message_type='||p_message_type);
1175: hr_utility.trace('p_message='||p_message);
1176: --
1177: -- strip ':' from the error message
1178: l_message := ltrim(p_message, ':');
1171: hr_utility.set_location('pay_gb_eoy_archive.write_output', 1);
1172: hr_utility.trace('p_assignment_number='||p_assignment_number);
1173: hr_utility.trace('p_full_name='||p_full_name);
1174: hr_utility.trace('p_message_type='||p_message_type);
1175: hr_utility.trace('p_message='||p_message);
1176: --
1177: -- strip ':' from the error message
1178: l_message := ltrim(p_message, ':');
1179: --
1179: --
1180: IF g_output_header THEN
1181: write_output_header;
1182: END IF;
1183: hr_utility.set_location('pay_gb_eoy_archive.write_output', 30);
1184: IF p_message_type = 'E' THEN
1185: l_err_warn := 'Error';
1186: fnd_file.put_line(fnd_file.log, 'An error encountered when processing assignment '||p_assignment_number||', please check output file for more details.');
1187: g_err_count := nvl(g_err_count, 0) + 1;
1189: l_err_warn := 'Warning';
1190: g_warn_count := nvl(g_warn_count, 0) + 1;
1191: END IF;
1192: --
1193: hr_utility.set_location('pay_gb_eoy_archive.write_output', 40);
1194: fnd_file.put_line(fnd_file.output, rpad(p_assignment_number, 14)||' '||
1195: rpad(p_full_name, 30)||' '||rpad(l_err_warn,10)||' '||
1196: rpad(l_message,75));
1197: --
1194: fnd_file.put_line(fnd_file.output, rpad(p_assignment_number, 14)||' '||
1195: rpad(p_full_name, 30)||' '||rpad(l_err_warn,10)||' '||
1196: rpad(l_message,75));
1197: --
1198: hr_utility.set_location('pay_gb_eoy_archive.write_output', 50);
1199: IF length(l_message) > 75 THEN
1200: fnd_file.put_line(fnd_file.output, rpad(' ', 57)||
1201: rpad(substr(l_message,76),75));
1202: END IF;
1200: fnd_file.put_line(fnd_file.output, rpad(' ', 57)||
1201: rpad(substr(l_message,76),75));
1202: END IF;
1203: --
1204: hr_utility.set_location('pay_gb_eoy_archive.write_output', 100);
1205: return 0;
1206: END write_output;
1207: --
1208: FUNCTION write_output_footer RETURN NUMBER IS
1206: END write_output;
1207: --
1208: FUNCTION write_output_footer RETURN NUMBER IS
1209: BEGIN
1210: hr_utility.set_location('pay_gb_eoy_archive.write_output_header', 1);
1211: --
1212: IF g_output_header THEN
1213: write_output_header;
1214: END IF;
1212: IF g_output_header THEN
1213: write_output_header;
1214: END IF;
1215: --
1216: hr_utility.set_location('pay_gb_eoy_archive.write_output_header', 10);
1217: fnd_file.put_line(fnd_file.output, ' ');
1218: fnd_file.put_line(fnd_file.output, rpad(' ', 20)||
1219: 'Total Number of Errors = '||nvl(g_err_count,0));
1220: fnd_file.put_line(fnd_file.output, rpad(' ', 20)||
1218: fnd_file.put_line(fnd_file.output, rpad(' ', 20)||
1219: 'Total Number of Errors = '||nvl(g_err_count,0));
1220: fnd_file.put_line(fnd_file.output, rpad(' ', 20)||
1221: 'Total Number of Warnings = '||nvl(g_warn_count,0));
1222: hr_utility.set_location('pay_gb_eoy_archive.write_output_header', 100);
1223: return 0;
1224: END write_output_footer;
1225: --
1226: PROCEDURE range_cursor (pactid IN NUMBER,
1488: END archive_payroll_info;
1489: --
1490: BEGIN
1491: BEGIN
1492: hr_utility.set_location('Entering: '||l_proc,1);
1493: --
1494: setup_entity_ids;
1495: --
1496: -- Find payroll action parameters
1529: EXCEPTION
1530: WHEN value_error THEN
1531: RAISE tax_dist_ref_error;
1532: END;
1533: hr_utility.set_location(l_proc,10);
1534: --
1535: l_payroll_name := NULL;
1536: -- Extract Payroll info
1537: FOR rec_payroll IN csr_payrolls(l_business_group_id,
1536: -- Extract Payroll info
1537: FOR rec_payroll IN csr_payrolls(l_business_group_id,
1538: l_end_year)
1539: LOOP
1540: hr_utility.set_location(l_proc||' '||rec_payroll.payroll_name,20);
1541: l_payroll_name := rec_payroll.payroll_name;
1542: -- find payroll year info
1543: OPEN csr_payroll_year (rec_payroll.payroll_id,l_start_year,l_end_year);
1544: FETCH csr_payroll_year INTO l_payroll_start_year,
1552: l_end_year,
1553: rec_payroll.emp_paye_ref);
1554: FETCH csr_another_paye_ref INTO l_another_paye_ref;
1555: IF csr_another_paye_ref%FOUND THEN
1556: hr_utility.trace('After csr_another_paye_ref, l_another_paye_ref='||
1557: l_another_paye_ref);
1558: CLOSE csr_another_paye_ref;
1559: RAISE inconsis_ref_error;
1560: ELSE
1558: CLOSE csr_another_paye_ref;
1559: RAISE inconsis_ref_error;
1560: ELSE
1561: CLOSE csr_another_paye_ref;
1562: hr_utility.trace('No other paye ref found on the payroll.');
1563: END IF;
1564: --
1565: -- Call API to archive Data via cover procedure
1566: /* Start 4011263
1717: -- return range cursor
1718: --
1719: -- select all people in the BG, filter out non appropriate ones in
1720: -- action_creation procedure.
1721: hr_utility.set_location(l_proc,30);
1722: -- sqlstr must contain one and only one entry of :payroll_action_id
1723: -- it must be ordered by person_id
1724: --
1725: sqlstr := 'SELECT DISTINCT person_id
1727: pay_payroll_actions ppa
1728: WHERE ppa.payroll_action_id = :payroll_action_id
1729: AND ppa.business_group_id +0= ppf.business_group_id
1730: ORDER BY ppf.person_id';
1731: hr_utility.set_location('Leaving: '||l_proc,40);
1732: EXCEPTION
1733: WHEN OTHERS THEN
1734: hr_utility.set_location(' Leaving: '||l_proc,50);
1735: fnd_file.put_line(fnd_file.log,
1730: ORDER BY ppf.person_id';
1731: hr_utility.set_location('Leaving: '||l_proc,40);
1732: EXCEPTION
1733: WHEN OTHERS THEN
1734: hr_utility.set_location(' Leaving: '||l_proc,50);
1735: fnd_file.put_line(fnd_file.log,
1736: substr('Error in rangecode '||sqlerrm(sqlcode),1,80));
1737: -- Return cursor that selects no rows
1738: sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
2004: --
2005: BEGIN
2006: IF chunk = 1 THEN
2007: NULL;
2008: --hr_utility.trace_on(NULL,'EOY_CHUNK1');
2009: END IF;
2010: --hr_utility.trace_on(NULL,'RMEOYAC');
2011: hr_utility.set_location('Entering: '||l_proc,1);
2012: -- Setup info and ids if new session.
2006: IF chunk = 1 THEN
2007: NULL;
2008: --hr_utility.trace_on(NULL,'EOY_CHUNK1');
2009: END IF;
2010: --hr_utility.trace_on(NULL,'RMEOYAC');
2011: hr_utility.set_location('Entering: '||l_proc,1);
2012: -- Setup info and ids if new session.
2013: -- DO NOT set g_payroll_action_id here as archinit() may stop working
2014: IF g_context_id IS NULL THEN
2007: NULL;
2008: --hr_utility.trace_on(NULL,'EOY_CHUNK1');
2009: END IF;
2010: --hr_utility.trace_on(NULL,'RMEOYAC');
2011: hr_utility.set_location('Entering: '||l_proc,1);
2012: -- Setup info and ids if new session.
2013: -- DO NOT set g_payroll_action_id here as archinit() may stop working
2014: IF g_context_id IS NULL THEN
2015: OPEN csr_parameter_info(pactid);
2055: OPEN csr_context_id('PAYROLL_ID');
2056: FETCH csr_context_id INTO g_context_id;
2057: CLOSE csr_context_id;
2058: --
2059: hr_utility.set_location(l_proc,10);
2060: --
2061: cache_archive_value(p_payroll_action_id => pactid,
2062: p_end_yr_ueid => g_payroll_end_year_eid,
2063: p_start_yr_ueid => g_payroll_start_year_eid,
2065: p_tax_dist_ueid => g_tax_dist_ref_eid,
2066: p_permit_ueid => g_permit_number_eid);
2067: --
2068: END IF; -- g_context_id IS NULL
2069: hr_utility.set_location(l_proc,13);
2070: --
2071: -- set asg backup details to unlikely values before loop.
2072: rec_prev_asg.assignment_id := hr_api.g_number;
2073: rec_prev_asg.effective_end_date := hr_api.g_date;
2083: g_payroll_end_year_eid,
2084: g_asg_set_id);
2085: LOOP
2086: FETCH csr_assignments INTO rec_assignment;
2087: hr_utility.trace(l_proc||' Fetched assignment_id='||
2088: rec_assignment.assignment_id||', g_asg_set_id='||g_asg_set_id);
2089: l_process_asg := FALSE;
2090: l_payroll_start_date :=
2091: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
2090: l_payroll_start_date :=
2091: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
2092: (pactid,'X_START_YEAR',
2093: to_char(rec_assignment.payroll_id)));
2094: hr_utility.trace(l_proc||' l_payroll_start_date='||fnd_date.date_to_displaydate(l_payroll_start_date));
2095: l_payroll_end_date :=
2096: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
2097: (pactid,'X_END_YEAR',
2098: to_char(rec_assignment.payroll_id)));
2095: l_payroll_end_date :=
2096: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
2097: (pactid,'X_END_YEAR',
2098: to_char(rec_assignment.payroll_id)));
2099: hr_utility.trace(l_proc||' l_payroll_end_date='||fnd_date.date_to_displaydate(l_payroll_end_date));
2100: hr_utility.trace(l_proc||' tax_ref_xfer='||rec_assignment.tax_ref_xfer);
2101: IF nvl(rec_assignment.tax_ref_xfer,' ') = 'N' AND csr_assignments%FOUND
2102: THEN
2103: hr_utility.trace(l_proc||' opening cursor csr_sub_asg.');
2096: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
2097: (pactid,'X_END_YEAR',
2098: to_char(rec_assignment.payroll_id)));
2099: hr_utility.trace(l_proc||' l_payroll_end_date='||fnd_date.date_to_displaydate(l_payroll_end_date));
2100: hr_utility.trace(l_proc||' tax_ref_xfer='||rec_assignment.tax_ref_xfer);
2101: IF nvl(rec_assignment.tax_ref_xfer,' ') = 'N' AND csr_assignments%FOUND
2102: THEN
2103: hr_utility.trace(l_proc||' opening cursor csr_sub_asg.');
2104: for asgrec in csr_sub_asg( rec_assignment.charrowid,
2099: hr_utility.trace(l_proc||' l_payroll_end_date='||fnd_date.date_to_displaydate(l_payroll_end_date));
2100: hr_utility.trace(l_proc||' tax_ref_xfer='||rec_assignment.tax_ref_xfer);
2101: IF nvl(rec_assignment.tax_ref_xfer,' ') = 'N' AND csr_assignments%FOUND
2102: THEN
2103: hr_utility.trace(l_proc||' opening cursor csr_sub_asg.');
2104: for asgrec in csr_sub_asg( rec_assignment.charrowid,
2105: l_payroll_start_date,
2106: l_payroll_end_date,
2107: pactid,
2107: pactid,
2108: g_payroll_start_year_eid,
2109: g_payroll_end_year_eid
2110: ) loop
2111: hr_utility.trace(l_proc||' In the loop for cursor csr_sub_asg.');
2112: l_process_asg := TRUE;
2113: end loop;
2114: ELSIF nvl(rec_assignment.tax_ref_xfer,' ') = 'Y' AND csr_assignments%FOUND
2115: THEN
2112: l_process_asg := TRUE;
2113: end loop;
2114: ELSIF nvl(rec_assignment.tax_ref_xfer,' ') = 'Y' AND csr_assignments%FOUND
2115: THEN
2116: hr_utility.trace(l_proc||' tax ref xfer=Y and found record by csr_assignments cursor.');
2117: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2118: hr_utility.trace(l_proc||' rec_assignment.effective_start_date='||fnd_date.date_to_displaydate(rec_assignment.effective_start_date));
2119: IF rec_assignment.effective_end_date >= l_payroll_start_date
2120: AND rec_assignment.effective_start_date <= l_payroll_end_date
2113: end loop;
2114: ELSIF nvl(rec_assignment.tax_ref_xfer,' ') = 'Y' AND csr_assignments%FOUND
2115: THEN
2116: hr_utility.trace(l_proc||' tax ref xfer=Y and found record by csr_assignments cursor.');
2117: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2118: hr_utility.trace(l_proc||' rec_assignment.effective_start_date='||fnd_date.date_to_displaydate(rec_assignment.effective_start_date));
2119: IF rec_assignment.effective_end_date >= l_payroll_start_date
2120: AND rec_assignment.effective_start_date <= l_payroll_end_date
2121: THEN
2114: ELSIF nvl(rec_assignment.tax_ref_xfer,' ') = 'Y' AND csr_assignments%FOUND
2115: THEN
2116: hr_utility.trace(l_proc||' tax ref xfer=Y and found record by csr_assignments cursor.');
2117: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2118: hr_utility.trace(l_proc||' rec_assignment.effective_start_date='||fnd_date.date_to_displaydate(rec_assignment.effective_start_date));
2119: IF rec_assignment.effective_end_date >= l_payroll_start_date
2120: AND rec_assignment.effective_start_date <= l_payroll_end_date
2121: THEN
2122: l_process_asg := TRUE;
2124: ELSE
2125: -- will come here if csr_assignments%NOTFOUND
2126: l_process_asg := TRUE;
2127: end if;
2128: hr_utility.trace(l_proc||' rec_assignment.person_id='||rec_assignment.person_id);
2129: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2130: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2131: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2132: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2125: -- will come here if csr_assignments%NOTFOUND
2126: l_process_asg := TRUE;
2127: end if;
2128: hr_utility.trace(l_proc||' rec_assignment.person_id='||rec_assignment.person_id);
2129: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2130: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2131: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2132: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2133: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2126: l_process_asg := TRUE;
2127: end if;
2128: hr_utility.trace(l_proc||' rec_assignment.person_id='||rec_assignment.person_id);
2129: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2130: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2131: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2132: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2133: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2134: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2127: end if;
2128: hr_utility.trace(l_proc||' rec_assignment.person_id='||rec_assignment.person_id);
2129: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2130: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2131: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2132: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2133: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2134: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2135: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2128: hr_utility.trace(l_proc||' rec_assignment.person_id='||rec_assignment.person_id);
2129: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2130: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2131: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2132: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2133: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2134: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2135: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2136: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2129: hr_utility.trace(l_proc||' rec_prev_asg.person_id='||rec_prev_asg.person_id);
2130: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2131: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2132: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2133: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2134: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2135: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2136: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2137: hr_utility.trace(l_proc||' rec_prev_asg.tax_ref='||rec_prev_asg.tax_ref);
2130: hr_utility.trace(l_proc||' rec_assignment.period_of_service_id='||rec_assignment.period_of_service_id);
2131: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2132: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2133: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2134: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2135: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2136: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2137: hr_utility.trace(l_proc||' rec_prev_asg.tax_ref='||rec_prev_asg.tax_ref);
2138: IF (csr_assignments%NOTFOUND
2131: hr_utility.trace(l_proc||' rec_prev_asg.period_of_service_id='||rec_prev_asg.period_of_service_id);
2132: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2133: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2134: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2135: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2136: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2137: hr_utility.trace(l_proc||' rec_prev_asg.tax_ref='||rec_prev_asg.tax_ref);
2138: IF (csr_assignments%NOTFOUND
2139: OR rec_assignment.person_id <> rec_prev_asg.person_id
2132: hr_utility.trace(l_proc||' rec_assignment.agg_active_start='||fnd_date.date_to_displaydate(rec_assignment.agg_active_start));
2133: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2134: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2135: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2136: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2137: hr_utility.trace(l_proc||' rec_prev_asg.tax_ref='||rec_prev_asg.tax_ref);
2138: IF (csr_assignments%NOTFOUND
2139: OR rec_assignment.person_id <> rec_prev_asg.person_id
2140: OR rec_assignment.period_of_service_id <> rec_prev_asg.period_of_service_id -- Added to fix bug 3784871
2133: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_start='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_start));
2134: hr_utility.trace(l_proc||' rec_assignment.agg_active_end='||fnd_date.date_to_displaydate(rec_assignment.agg_active_end));
2135: hr_utility.trace(l_proc||' rec_prev_asg.agg_active_end='||fnd_date.date_to_displaydate(rec_prev_asg.agg_active_end));
2136: hr_utility.trace(l_proc||' rec_assignment.tax_ref='||rec_assignment.tax_ref);
2137: hr_utility.trace(l_proc||' rec_prev_asg.tax_ref='||rec_prev_asg.tax_ref);
2138: IF (csr_assignments%NOTFOUND
2139: OR rec_assignment.person_id <> rec_prev_asg.person_id
2140: OR rec_assignment.period_of_service_id <> rec_prev_asg.period_of_service_id -- Added to fix bug 3784871
2141: OR rec_assignment.agg_active_start <> rec_prev_asg.agg_active_start
2163: p_object_version_number => l_ovn,
2164: p_some_warning => l_some_warning);
2165: END IF;
2166: EXIT WHEN csr_assignments%NOTFOUND;
2167: hr_utility.set_location(l_proc,15);
2168: hr_utility.trace(l_proc||' rec_assignment.assignment_id='||rec_assignment.assignment_id);
2169: hr_utility.trace(l_proc||' rec_prev_asg.assignment_id='||rec_prev_asg.assignment_id);
2170: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2171: hr_utility.trace(l_proc||' rec_prev_asg.effective_end_date='||fnd_date.date_to_displaydate(rec_prev_asg.effective_end_date));
2164: p_some_warning => l_some_warning);
2165: END IF;
2166: EXIT WHEN csr_assignments%NOTFOUND;
2167: hr_utility.set_location(l_proc,15);
2168: hr_utility.trace(l_proc||' rec_assignment.assignment_id='||rec_assignment.assignment_id);
2169: hr_utility.trace(l_proc||' rec_prev_asg.assignment_id='||rec_prev_asg.assignment_id);
2170: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2171: hr_utility.trace(l_proc||' rec_prev_asg.effective_end_date='||fnd_date.date_to_displaydate(rec_prev_asg.effective_end_date));
2172: IF (rec_assignment.assignment_id <> rec_prev_asg.assignment_id
2165: END IF;
2166: EXIT WHEN csr_assignments%NOTFOUND;
2167: hr_utility.set_location(l_proc,15);
2168: hr_utility.trace(l_proc||' rec_assignment.assignment_id='||rec_assignment.assignment_id);
2169: hr_utility.trace(l_proc||' rec_prev_asg.assignment_id='||rec_prev_asg.assignment_id);
2170: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2171: hr_utility.trace(l_proc||' rec_prev_asg.effective_end_date='||fnd_date.date_to_displaydate(rec_prev_asg.effective_end_date));
2172: IF (rec_assignment.assignment_id <> rec_prev_asg.assignment_id
2173: OR rec_assignment.effective_end_date <> rec_prev_asg.effective_end_date)
2166: EXIT WHEN csr_assignments%NOTFOUND;
2167: hr_utility.set_location(l_proc,15);
2168: hr_utility.trace(l_proc||' rec_assignment.assignment_id='||rec_assignment.assignment_id);
2169: hr_utility.trace(l_proc||' rec_prev_asg.assignment_id='||rec_prev_asg.assignment_id);
2170: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2171: hr_utility.trace(l_proc||' rec_prev_asg.effective_end_date='||fnd_date.date_to_displaydate(rec_prev_asg.effective_end_date));
2172: IF (rec_assignment.assignment_id <> rec_prev_asg.assignment_id
2173: OR rec_assignment.effective_end_date <> rec_prev_asg.effective_end_date)
2174: AND l_process_asg
2167: hr_utility.set_location(l_proc,15);
2168: hr_utility.trace(l_proc||' rec_assignment.assignment_id='||rec_assignment.assignment_id);
2169: hr_utility.trace(l_proc||' rec_prev_asg.assignment_id='||rec_prev_asg.assignment_id);
2170: hr_utility.trace(l_proc||' rec_assignment.effective_end_date='||fnd_date.date_to_displaydate(rec_assignment.effective_end_date));
2171: hr_utility.trace(l_proc||' rec_prev_asg.effective_end_date='||fnd_date.date_to_displaydate(rec_prev_asg.effective_end_date));
2172: IF (rec_assignment.assignment_id <> rec_prev_asg.assignment_id
2173: OR rec_assignment.effective_end_date <> rec_prev_asg.effective_end_date)
2174: AND l_process_asg
2175: -- if the current row is the first row
2176: -- or is not the same as the previous one
2177: -- (ignoring tax_ref_xfer) as the 2nd part of union may bring back
2178: -- duplicates
2179: THEN
2180: hr_utility.set_location(l_proc,20);
2181: -- insert an action
2182: SELECT pay_assignment_actions_s.nextval
2183: INTO l_actid
2184: FROM dual;
2236: rec_prev_asg := rec_assignment;
2237: END IF; -- not duplicate
2238: END LOOP;
2239: CLOSE csr_assignments;
2240: -- hr_utility.trace_off;
2241: hr_utility.set_location('Leaving: '||l_proc,40);
2242: END action_creation;
2243: --
2244: --
2237: END IF; -- not duplicate
2238: END LOOP;
2239: CLOSE csr_assignments;
2240: -- hr_utility.trace_off;
2241: hr_utility.set_location('Leaving: '||l_proc,40);
2242: END action_creation;
2243: --
2244: --
2245: PROCEDURE archinit(p_payroll_action_id IN NUMBER) IS
2359: l_agg_non_retry_err_flag VARCHAR2(1) := 'N';
2360: --
2361: l_dummy NUMBER := 0;
2362: BEGIN
2363: -- hr_utility.trace_on(NULL,'ARCHINIT');
2364: hr_utility.set_location('Entering: '||l_proc,1);
2365: --
2366: IF g_payroll_action_id IS NULL
2367: OR g_payroll_action_id <> p_payroll_action_id THEN
2360: --
2361: l_dummy NUMBER := 0;
2362: BEGIN
2363: -- hr_utility.trace_on(NULL,'ARCHINIT');
2364: hr_utility.set_location('Entering: '||l_proc,1);
2365: --
2366: IF g_payroll_action_id IS NULL
2367: OR g_payroll_action_id <> p_payroll_action_id THEN
2368: g_payroll_action_id := p_payroll_action_id;
2384: --
2385: l_agg_non_retry_err_flag := 'N';
2386: FOR retry_actions_rec IN get_retry_actions LOOP
2387: --
2388: hr_utility.trace(l_proc||': retry action id='||
2389: retry_actions_rec.assignment_action_id);
2390: hr_utility.trace(l_proc||': retry action status='||
2391: retry_actions_rec.action_status);
2392: --
2386: FOR retry_actions_rec IN get_retry_actions LOOP
2387: --
2388: hr_utility.trace(l_proc||': retry action id='||
2389: retry_actions_rec.assignment_action_id);
2390: hr_utility.trace(l_proc||': retry action status='||
2391: retry_actions_rec.action_status);
2392: --
2393: FOR non_retry_act_rec IN get_agg_non_retry_actions(retry_actions_rec.assignment_action_id) LOOP
2394: --
2391: retry_actions_rec.action_status);
2392: --
2393: FOR non_retry_act_rec IN get_agg_non_retry_actions(retry_actions_rec.assignment_action_id) LOOP
2394: --
2395: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_action_id='||
2396: non_retry_act_rec.assignment_action_id);
2397: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_number='||
2398: non_retry_act_rec.assignment_number);
2399: hr_utility.trace(l_proc||': non_retry_act_rec.retry_asg_number='||
2393: FOR non_retry_act_rec IN get_agg_non_retry_actions(retry_actions_rec.assignment_action_id) LOOP
2394: --
2395: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_action_id='||
2396: non_retry_act_rec.assignment_action_id);
2397: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_number='||
2398: non_retry_act_rec.assignment_number);
2399: hr_utility.trace(l_proc||': non_retry_act_rec.retry_asg_number='||
2400: non_retry_act_rec.retry_asg_number);
2401: hr_utility.trace(l_proc||': non_retry_act_rec.full_name='||
2395: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_action_id='||
2396: non_retry_act_rec.assignment_action_id);
2397: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_number='||
2398: non_retry_act_rec.assignment_number);
2399: hr_utility.trace(l_proc||': non_retry_act_rec.retry_asg_number='||
2400: non_retry_act_rec.retry_asg_number);
2401: hr_utility.trace(l_proc||': non_retry_act_rec.full_name='||
2402: non_retry_act_rec.full_name);
2403: l_agg_non_retry_err_flag := 'Y';
2397: hr_utility.trace(l_proc||': non_retry_act_rec.assignment_number='||
2398: non_retry_act_rec.assignment_number);
2399: hr_utility.trace(l_proc||': non_retry_act_rec.retry_asg_number='||
2400: non_retry_act_rec.retry_asg_number);
2401: hr_utility.trace(l_proc||': non_retry_act_rec.full_name='||
2402: non_retry_act_rec.full_name);
2403: l_agg_non_retry_err_flag := 'Y';
2404: l_dummy := write_output(p_assignment_number => non_retry_act_rec.retry_asg_number,
2405: p_full_name => non_retry_act_rec.full_name,
2407: 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.');
2408: END LOOP;
2409: END LOOP;
2410: --
2411: hr_utility.trace(l_proc||' After get_agg_non_retry_actions, l_agg_non_retry_err_flag='||l_agg_non_retry_err_flag);
2412: --
2413: IF l_agg_non_retry_err_flag = 'Y' THEN
2414: -- Raise the error and stop processing the assignments marked for retry
2415: -- because aggregated assignment(s) has(have) not been marked for retry.
3108: --
3109: END LOOP;
3110: --
3111: END IF;
3112: hr_utility.set_location(' Leaving: '||l_proc,100);
3113: END archinit;
3114: --
3115: --
3116: PROCEDURE archive_code(p_assactid IN NUMBER, p_effective_date IN DATE) IS
3932: p_actid NUMBER DEFAULT NULL) IS
3933: l_proc CONSTANT VARCHAR2(40):= g_package||'archive_asg_info';
3934: BEGIN
3935: IF p_value IS NOT NULL THEN
3936: hr_utility.set_location(l_proc||' '||p_user_entity_id,10);
3937: ff_archive_api.create_archive_item
3938: (p_archive_item_id => l_archive_item_id,
3939: p_user_entity_id => p_user_entity_id,
3940: p_archive_value => p_value,
3977: -- p_actid may be given to archive the value against a different assignment
3978: -- action to that currently being processed. This is utilised by the
3979: -- Multiple Assignment Logic Pt. III
3980: BEGIN
3981: hr_utility.trace('Entering pay_gb_eoy_archive.archive_ni_values');
3982: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3983: hr_utility.trace('p_value='||p_value);
3984: hr_utility.trace('p_reportable='||p_reportable);
3985: hr_utility.trace('p_ni_cat='||p_ni_cat);
3978: -- action to that currently being processed. This is utilised by the
3979: -- Multiple Assignment Logic Pt. III
3980: BEGIN
3981: hr_utility.trace('Entering pay_gb_eoy_archive.archive_ni_values');
3982: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3983: hr_utility.trace('p_value='||p_value);
3984: hr_utility.trace('p_reportable='||p_reportable);
3985: hr_utility.trace('p_ni_cat='||p_ni_cat);
3986: hr_utility.trace('p_scon='||p_scon);
3979: -- Multiple Assignment Logic Pt. III
3980: BEGIN
3981: hr_utility.trace('Entering pay_gb_eoy_archive.archive_ni_values');
3982: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3983: hr_utility.trace('p_value='||p_value);
3984: hr_utility.trace('p_reportable='||p_reportable);
3985: hr_utility.trace('p_ni_cat='||p_ni_cat);
3986: hr_utility.trace('p_scon='||p_scon);
3987: hr_utility.trace('p_actid='||p_actid);
3980: BEGIN
3981: hr_utility.trace('Entering pay_gb_eoy_archive.archive_ni_values');
3982: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3983: hr_utility.trace('p_value='||p_value);
3984: hr_utility.trace('p_reportable='||p_reportable);
3985: hr_utility.trace('p_ni_cat='||p_ni_cat);
3986: hr_utility.trace('p_scon='||p_scon);
3987: hr_utility.trace('p_actid='||p_actid);
3988: hr_utility.trace('p_assactid='||p_assactid);
3981: hr_utility.trace('Entering pay_gb_eoy_archive.archive_ni_values');
3982: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3983: hr_utility.trace('p_value='||p_value);
3984: hr_utility.trace('p_reportable='||p_reportable);
3985: hr_utility.trace('p_ni_cat='||p_ni_cat);
3986: hr_utility.trace('p_scon='||p_scon);
3987: hr_utility.trace('p_actid='||p_actid);
3988: hr_utility.trace('p_assactid='||p_assactid);
3989: IF p_value IS NOT NULL THEN
3982: hr_utility.trace('p_user_entity_id='||p_user_entity_id);
3983: hr_utility.trace('p_value='||p_value);
3984: hr_utility.trace('p_reportable='||p_reportable);
3985: hr_utility.trace('p_ni_cat='||p_ni_cat);
3986: hr_utility.trace('p_scon='||p_scon);
3987: hr_utility.trace('p_actid='||p_actid);
3988: hr_utility.trace('p_assactid='||p_assactid);
3989: IF p_value IS NOT NULL THEN
3990: IF p_scon IS NOT NULL THEN
3983: hr_utility.trace('p_value='||p_value);
3984: hr_utility.trace('p_reportable='||p_reportable);
3985: hr_utility.trace('p_ni_cat='||p_ni_cat);
3986: hr_utility.trace('p_scon='||p_scon);
3987: hr_utility.trace('p_actid='||p_actid);
3988: hr_utility.trace('p_assactid='||p_assactid);
3989: IF p_value IS NOT NULL THEN
3990: IF p_scon IS NOT NULL THEN
3991: ff_archive_api.create_archive_item
3984: hr_utility.trace('p_reportable='||p_reportable);
3985: hr_utility.trace('p_ni_cat='||p_ni_cat);
3986: hr_utility.trace('p_scon='||p_scon);
3987: hr_utility.trace('p_actid='||p_actid);
3988: hr_utility.trace('p_assactid='||p_assactid);
3989: IF p_value IS NOT NULL THEN
3990: IF p_scon IS NOT NULL THEN
3991: ff_archive_api.create_archive_item
3992: (p_archive_item_id => l_archive_item_id,
4074: l_proc CONSTANT VARCHAR2(40):= g_package||'archive_agg_values';
4075: --
4076: BEGIN
4077: --
4078: hr_utility.set_location(l_proc,10);
4079: BEGIN
4080: g_agg_balance_totals.smp :=
4081: g_agg_balance_totals.smp + nvl(p_smp,0);
4082: g_agg_balance_totals.ssp :=
4106: g_agg_balance_totals.student_ln :=
4107: g_agg_balance_totals.student_ln + nvl(p_student_ln,0);
4108: g_agg_balance_totals.ni_arrears :=
4109: g_agg_balance_totals.ni_arrears + nvl(p_ni_arrears,0);
4110: hr_utility.set_location(l_proc,20);
4111: EXCEPTION WHEN NO_DATA_FOUND THEN
4112: hr_utility.set_location(l_proc,30);
4113: -- The PLSQL table is empty so first row.
4114: g_agg_balance_totals.smp := nvl(p_smp,0);
4108: g_agg_balance_totals.ni_arrears :=
4109: g_agg_balance_totals.ni_arrears + nvl(p_ni_arrears,0);
4110: hr_utility.set_location(l_proc,20);
4111: EXCEPTION WHEN NO_DATA_FOUND THEN
4112: hr_utility.set_location(l_proc,30);
4113: -- The PLSQL table is empty so first row.
4114: g_agg_balance_totals.smp := nvl(p_smp,0);
4115: g_agg_balance_totals.ssp := nvl(p_ssp,0);
4116: g_agg_balance_totals.sap := nvl(p_sap,0);
4125: g_agg_balance_totals.student_ln := nvl(p_student_ln,0);
4126: g_agg_balance_totals.ni_arrears := nvl(p_ni_arrears,0);
4127: END; -- store balance values in global table.
4128: --
4129: hr_utility.set_location(l_proc,40);
4130: --
4131: END store_agg_values;
4132: ------------------------------------------------------------------------
4133: PROCEDURE archive_ni_values(p_ni_cat VARCHAR2,
4159: l_index1 binary_integer;
4160: --
4161: BEGIN
4162: --
4163: hr_utility.set_location('Entering: '||l_proc,1);
4164: --
4165: IF l_multiple_asg_flag = 'Y' THEN
4166: -- Do Multiple Assignment Logic Part II
4167: --
4173: -- Removed previous total contribs check.
4174: -- Add the balances for this assignment to the NI balances
4175: -- (per NI category) for the person in the NI Balance Totals table
4176: --
4177: hr_utility.set_location(l_proc,10);
4178: -- Find appropriate row in NI values table:
4179: BEGIN
4180: l_index1:=0;
4181: LOOP
4184: AND nvl(g_ni_balance_totals(l_index1).scon,'NONE')
4185: = nvl(p_scon,'NONE');
4186: END LOOP;
4187: -- Add balances to those in table:
4188: hr_utility.set_location(l_proc||' '||l_index1,15);
4189: g_ni_balance_totals(l_index1).tot_contribs :=
4190: g_ni_balance_totals(l_index1).tot_contribs + nvl(p_tot_contribs,0);
4191: g_ni_balance_totals(l_index1).earnings :=
4192: g_ni_balance_totals(l_index1).earnings + nvl(p_earnings,0);
4211: g_ni_balance_totals(l_index1).ees_rebate :=
4212: g_ni_balance_totals(l_index1).ees_rebate + nvl(p_ees_rebate,0);
4213: EXCEPTION WHEN no_data_found THEN
4214: -- row not found, insert new one:
4215: hr_utility.set_location(l_proc||' '||l_index1,20);
4216: g_ni_balance_totals(l_index1).ni_cat := p_ni_cat;
4217: g_ni_balance_totals(l_index1).scon := p_scon;
4218: g_ni_balance_totals(l_index1).tot_contribs := nvl(p_tot_contribs,0);
4219: g_ni_balance_totals(l_index1).earnings := nvl(p_earnings,0);
4249: END IF;
4250: --
4251: /* EOY 2012/2013 begin block*/
4252: /*IF p_scon IS NOT NULL THEN
4253: hr_utility.set_location(l_proc,30);
4254: archive_ni_value(g_ni_scon_earnings_eid,p_earnings,
4255: l_report_values,p_ni_cat,p_scon);
4256: archive_ni_value(g_ni_scon_ees_contribution_eid,p_ees_contribs,
4257: l_report_values,p_ni_cat,p_scon);
4278: archive_ni_value(g_ni_scon_refund_eid, l_refund_flag,
4279: l_report_values,p_ni_cat,p_scon);
4280: ELSE */
4281: /*EOY 2012/2013 end block*/
4282: hr_utility.set_location(l_proc,40);
4283: archive_ni_value(g_ni_earnings_eid,p_earnings,
4284: l_report_values,p_ni_cat);
4285: archive_ni_value(g_ni_ees_contribution_eid,p_ees_contribs,
4286: l_report_values,p_ni_cat);
4306: l_report_values,p_ni_cat);
4307: archive_ni_value(g_ni_refund_eid, l_refund_flag,
4308: l_report_values,p_ni_cat);
4309: -- END IF; --EOY 2012/2013
4310: hr_utility.set_location(' Leaving: '||l_proc,100);
4311: END archive_ni_values;
4312: --
4313: PROCEDURE remove_null_address_lines(p_address_line1 IN OUT NOCOPY VARCHAR2,
4314: p_address_line2 IN OUT NOCOPY VARCHAR2,
4321: l_dummy VARCHAR2(1000);
4322: l_proc VARCHAR2(100) := 'pay_gb_eoy_archive.remove_null_address_lines';
4323: --
4324: BEGIN
4325: hr_utility.set_location('Entering: '||l_proc,1);
4326: hr_utility.trace('p_address_line1='||p_address_line1);
4327: hr_utility.trace('p_address_line2='||p_address_line2);
4328: hr_utility.trace('p_address_line3='||p_address_line3);
4329: hr_utility.trace('p_address_line4='||p_address_line4);
4322: l_proc VARCHAR2(100) := 'pay_gb_eoy_archive.remove_null_address_lines';
4323: --
4324: BEGIN
4325: hr_utility.set_location('Entering: '||l_proc,1);
4326: hr_utility.trace('p_address_line1='||p_address_line1);
4327: hr_utility.trace('p_address_line2='||p_address_line2);
4328: hr_utility.trace('p_address_line3='||p_address_line3);
4329: hr_utility.trace('p_address_line4='||p_address_line4);
4330: --
4323: --
4324: BEGIN
4325: hr_utility.set_location('Entering: '||l_proc,1);
4326: hr_utility.trace('p_address_line1='||p_address_line1);
4327: hr_utility.trace('p_address_line2='||p_address_line2);
4328: hr_utility.trace('p_address_line3='||p_address_line3);
4329: hr_utility.trace('p_address_line4='||p_address_line4);
4330: --
4331: l_lines(1) := p_address_line1;
4324: BEGIN
4325: hr_utility.set_location('Entering: '||l_proc,1);
4326: hr_utility.trace('p_address_line1='||p_address_line1);
4327: hr_utility.trace('p_address_line2='||p_address_line2);
4328: hr_utility.trace('p_address_line3='||p_address_line3);
4329: hr_utility.trace('p_address_line4='||p_address_line4);
4330: --
4331: l_lines(1) := p_address_line1;
4332: l_lines(2) := p_address_line2;
4325: hr_utility.set_location('Entering: '||l_proc,1);
4326: hr_utility.trace('p_address_line1='||p_address_line1);
4327: hr_utility.trace('p_address_line2='||p_address_line2);
4328: hr_utility.trace('p_address_line3='||p_address_line3);
4329: hr_utility.trace('p_address_line4='||p_address_line4);
4330: --
4331: l_lines(1) := p_address_line1;
4332: l_lines(2) := p_address_line2;
4333: l_lines(3) := p_address_line3;
4341: END IF;
4342: END LOOP;
4343: END LOOP;
4344: --
4345: hr_utility.set_location(l_proc,10);
4346: --
4347: p_address_line1 := l_lines(1);
4348: p_address_line2 := l_lines(2);
4349: p_address_line3 := l_lines(3);
4348: p_address_line2 := l_lines(2);
4349: p_address_line3 := l_lines(3);
4350: p_address_line4 := l_lines(4);
4351: --
4352: hr_utility.set_location('Leaving: '||l_proc,20);
4353: hr_utility.trace('p_address_line1='||p_address_line1);
4354: hr_utility.trace('p_address_line2='||p_address_line2);
4355: hr_utility.trace('p_address_line3='||p_address_line3);
4356: hr_utility.trace('p_address_line4='||p_address_line4);
4349: p_address_line3 := l_lines(3);
4350: p_address_line4 := l_lines(4);
4351: --
4352: hr_utility.set_location('Leaving: '||l_proc,20);
4353: hr_utility.trace('p_address_line1='||p_address_line1);
4354: hr_utility.trace('p_address_line2='||p_address_line2);
4355: hr_utility.trace('p_address_line3='||p_address_line3);
4356: hr_utility.trace('p_address_line4='||p_address_line4);
4357: END;
4350: p_address_line4 := l_lines(4);
4351: --
4352: hr_utility.set_location('Leaving: '||l_proc,20);
4353: hr_utility.trace('p_address_line1='||p_address_line1);
4354: hr_utility.trace('p_address_line2='||p_address_line2);
4355: hr_utility.trace('p_address_line3='||p_address_line3);
4356: hr_utility.trace('p_address_line4='||p_address_line4);
4357: END;
4358: --
4351: --
4352: hr_utility.set_location('Leaving: '||l_proc,20);
4353: hr_utility.trace('p_address_line1='||p_address_line1);
4354: hr_utility.trace('p_address_line2='||p_address_line2);
4355: hr_utility.trace('p_address_line3='||p_address_line3);
4356: hr_utility.trace('p_address_line4='||p_address_line4);
4357: END;
4358: --
4359: BEGIN
4352: hr_utility.set_location('Leaving: '||l_proc,20);
4353: hr_utility.trace('p_address_line1='||p_address_line1);
4354: hr_utility.trace('p_address_line2='||p_address_line2);
4355: hr_utility.trace('p_address_line3='||p_address_line3);
4356: hr_utility.trace('p_address_line4='||p_address_line4);
4357: END;
4358: --
4359: BEGIN
4360: hr_utility.set_location('Entering: '||l_proc,1);
4356: hr_utility.trace('p_address_line4='||p_address_line4);
4357: END;
4358: --
4359: BEGIN
4360: hr_utility.set_location('Entering: '||l_proc,1);
4361: hr_utility.trace('p_assactid='||p_assactid);
4362: -- Get the AAC level info.
4363: OPEN csr_asg_act_info(p_assactid);
4364: FETCH csr_asg_act_info INTO l_assignment_id,
4357: END;
4358: --
4359: BEGIN
4360: hr_utility.set_location('Entering: '||l_proc,1);
4361: hr_utility.trace('p_assactid='||p_assactid);
4362: -- Get the AAC level info.
4363: OPEN csr_asg_act_info(p_assactid);
4364: FETCH csr_asg_act_info INTO l_assignment_id,
4365: l_effective_end_date,
4366: l_tax_ref_transfer,
4367: l_agg_active_start,
4368: l_agg_active_end;
4369: CLOSE csr_asg_act_info;
4370: hr_utility.trace('After csr_asg_act_info, l_assignment_id='||l_assignment_id);
4371: hr_utility.trace('l_effective_end_date='||fnd_date.date_to_displaydate(l_effective_end_date));
4372: hr_utility.trace('l_tax_ref_transfer='||l_tax_ref_transfer);
4373: hr_utility.trace('l_agg_active_start='||fnd_date.date_to_displaydate(l_agg_active_start));
4374: hr_utility.trace('l_agg_active_end='||fnd_date.date_to_displaydate(l_agg_active_end));
4367: l_agg_active_start,
4368: l_agg_active_end;
4369: CLOSE csr_asg_act_info;
4370: hr_utility.trace('After csr_asg_act_info, l_assignment_id='||l_assignment_id);
4371: hr_utility.trace('l_effective_end_date='||fnd_date.date_to_displaydate(l_effective_end_date));
4372: hr_utility.trace('l_tax_ref_transfer='||l_tax_ref_transfer);
4373: hr_utility.trace('l_agg_active_start='||fnd_date.date_to_displaydate(l_agg_active_start));
4374: hr_utility.trace('l_agg_active_end='||fnd_date.date_to_displaydate(l_agg_active_end));
4375: -- Bug fix for 3699865 - get the final process date
4368: l_agg_active_end;
4369: CLOSE csr_asg_act_info;
4370: hr_utility.trace('After csr_asg_act_info, l_assignment_id='||l_assignment_id);
4371: hr_utility.trace('l_effective_end_date='||fnd_date.date_to_displaydate(l_effective_end_date));
4372: hr_utility.trace('l_tax_ref_transfer='||l_tax_ref_transfer);
4373: hr_utility.trace('l_agg_active_start='||fnd_date.date_to_displaydate(l_agg_active_start));
4374: hr_utility.trace('l_agg_active_end='||fnd_date.date_to_displaydate(l_agg_active_end));
4375: -- Bug fix for 3699865 - get the final process date
4376: OPEN csr_asg_last_process_date(l_assignment_id);
4369: CLOSE csr_asg_act_info;
4370: hr_utility.trace('After csr_asg_act_info, l_assignment_id='||l_assignment_id);
4371: hr_utility.trace('l_effective_end_date='||fnd_date.date_to_displaydate(l_effective_end_date));
4372: hr_utility.trace('l_tax_ref_transfer='||l_tax_ref_transfer);
4373: hr_utility.trace('l_agg_active_start='||fnd_date.date_to_displaydate(l_agg_active_start));
4374: hr_utility.trace('l_agg_active_end='||fnd_date.date_to_displaydate(l_agg_active_end));
4375: -- Bug fix for 3699865 - get the final process date
4376: OPEN csr_asg_last_process_date(l_assignment_id);
4377: FETCH csr_asg_last_process_date INTO l_final_process_date;
4370: hr_utility.trace('After csr_asg_act_info, l_assignment_id='||l_assignment_id);
4371: hr_utility.trace('l_effective_end_date='||fnd_date.date_to_displaydate(l_effective_end_date));
4372: hr_utility.trace('l_tax_ref_transfer='||l_tax_ref_transfer);
4373: hr_utility.trace('l_agg_active_start='||fnd_date.date_to_displaydate(l_agg_active_start));
4374: hr_utility.trace('l_agg_active_end='||fnd_date.date_to_displaydate(l_agg_active_end));
4375: -- Bug fix for 3699865 - get the final process date
4376: OPEN csr_asg_last_process_date(l_assignment_id);
4377: FETCH csr_asg_last_process_date INTO l_final_process_date;
4378: CLOSE csr_asg_last_process_date;
4375: -- Bug fix for 3699865 - get the final process date
4376: OPEN csr_asg_last_process_date(l_assignment_id);
4377: FETCH csr_asg_last_process_date INTO l_final_process_date;
4378: CLOSE csr_asg_last_process_date;
4379: hr_utility.trace('After csr_asg_last_process_date, l_final_process_date='||fnd_date.date_to_displaydate(l_final_process_date));
4380: IF l_final_process_date is not null
4381: and nvl(l_tax_ref_transfer, 'N') = 'N'
4382: and l_final_process_date < l_effective_end_date THEN
4383: -- added extra conditions above for 5199746, so that final process date
4398: l_people_group_id,
4399: l_period_of_service_id;
4400: CLOSE csr_basic_asg_info;
4401: --
4402: hr_utility.trace('After csr_basic_asg_info, l_payroll_id='||l_payroll_id);
4403: hr_utility.trace('l_assignment_number='||l_assignment_number);
4404: hr_utility.trace('l_assignment_number='||l_assignment_number);
4405: hr_utility.trace('l_person_id='||l_person_id);
4406: hr_utility.trace('l_person_id='||l_person_id);
4399: l_period_of_service_id;
4400: CLOSE csr_basic_asg_info;
4401: --
4402: hr_utility.trace('After csr_basic_asg_info, l_payroll_id='||l_payroll_id);
4403: hr_utility.trace('l_assignment_number='||l_assignment_number);
4404: hr_utility.trace('l_assignment_number='||l_assignment_number);
4405: hr_utility.trace('l_person_id='||l_person_id);
4406: hr_utility.trace('l_person_id='||l_person_id);
4407: hr_utility.trace('l_organization_id='||l_organization_id);
4400: CLOSE csr_basic_asg_info;
4401: --
4402: hr_utility.trace('After csr_basic_asg_info, l_payroll_id='||l_payroll_id);
4403: hr_utility.trace('l_assignment_number='||l_assignment_number);
4404: hr_utility.trace('l_assignment_number='||l_assignment_number);
4405: hr_utility.trace('l_person_id='||l_person_id);
4406: hr_utility.trace('l_person_id='||l_person_id);
4407: hr_utility.trace('l_organization_id='||l_organization_id);
4408: hr_utility.trace('l_location_id='||l_location_id);
4401: --
4402: hr_utility.trace('After csr_basic_asg_info, l_payroll_id='||l_payroll_id);
4403: hr_utility.trace('l_assignment_number='||l_assignment_number);
4404: hr_utility.trace('l_assignment_number='||l_assignment_number);
4405: hr_utility.trace('l_person_id='||l_person_id);
4406: hr_utility.trace('l_person_id='||l_person_id);
4407: hr_utility.trace('l_organization_id='||l_organization_id);
4408: hr_utility.trace('l_location_id='||l_location_id);
4409: hr_utility.trace('l_people_group_id='||l_people_group_id);
4402: hr_utility.trace('After csr_basic_asg_info, l_payroll_id='||l_payroll_id);
4403: hr_utility.trace('l_assignment_number='||l_assignment_number);
4404: hr_utility.trace('l_assignment_number='||l_assignment_number);
4405: hr_utility.trace('l_person_id='||l_person_id);
4406: hr_utility.trace('l_person_id='||l_person_id);
4407: hr_utility.trace('l_organization_id='||l_organization_id);
4408: hr_utility.trace('l_location_id='||l_location_id);
4409: hr_utility.trace('l_people_group_id='||l_people_group_id);
4410: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4403: hr_utility.trace('l_assignment_number='||l_assignment_number);
4404: hr_utility.trace('l_assignment_number='||l_assignment_number);
4405: hr_utility.trace('l_person_id='||l_person_id);
4406: hr_utility.trace('l_person_id='||l_person_id);
4407: hr_utility.trace('l_organization_id='||l_organization_id);
4408: hr_utility.trace('l_location_id='||l_location_id);
4409: hr_utility.trace('l_people_group_id='||l_people_group_id);
4410: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4411: --
4404: hr_utility.trace('l_assignment_number='||l_assignment_number);
4405: hr_utility.trace('l_person_id='||l_person_id);
4406: hr_utility.trace('l_person_id='||l_person_id);
4407: hr_utility.trace('l_organization_id='||l_organization_id);
4408: hr_utility.trace('l_location_id='||l_location_id);
4409: hr_utility.trace('l_people_group_id='||l_people_group_id);
4410: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4411: --
4412: IF l_assignment_number is null then
4405: hr_utility.trace('l_person_id='||l_person_id);
4406: hr_utility.trace('l_person_id='||l_person_id);
4407: hr_utility.trace('l_organization_id='||l_organization_id);
4408: hr_utility.trace('l_location_id='||l_location_id);
4409: hr_utility.trace('l_people_group_id='||l_people_group_id);
4410: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4411: --
4412: IF l_assignment_number is null then
4413: --
4406: hr_utility.trace('l_person_id='||l_person_id);
4407: hr_utility.trace('l_organization_id='||l_organization_id);
4408: hr_utility.trace('l_location_id='||l_location_id);
4409: hr_utility.trace('l_people_group_id='||l_people_group_id);
4410: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4411: --
4412: IF l_assignment_number is null then
4413: --
4414: hr_utility.trace('Assignment has been updated or terminated');
4410: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4411: --
4412: IF l_assignment_number is null then
4413: --
4414: hr_utility.trace('Assignment has been updated or terminated');
4415: -- 1.The direct match asg and end date has not been found,
4416: -- Check whether there are any asgs current at this date,
4417: -- and use the adjusted end date.
4418: --
4427: l_period_of_service_id;
4428: IF csr_basic_inf_current%FOUND then
4429: -- Set the eff end to the one of the current row
4430: l_effective_end_date := l_adj_eff_end_date;
4431: hr_utility.trace('After csr_basic_inf_current, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4432: hr_utility.trace('l_payroll_id='||l_payroll_id);
4433: hr_utility.trace('l_assignment_number='||l_assignment_number);
4434: hr_utility.trace('l_person_id='||l_person_id);
4435: hr_utility.trace('l_organization_id='||l_organization_id);
4428: IF csr_basic_inf_current%FOUND then
4429: -- Set the eff end to the one of the current row
4430: l_effective_end_date := l_adj_eff_end_date;
4431: hr_utility.trace('After csr_basic_inf_current, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4432: hr_utility.trace('l_payroll_id='||l_payroll_id);
4433: hr_utility.trace('l_assignment_number='||l_assignment_number);
4434: hr_utility.trace('l_person_id='||l_person_id);
4435: hr_utility.trace('l_organization_id='||l_organization_id);
4436: hr_utility.trace('l_location_id='||l_location_id);
4429: -- Set the eff end to the one of the current row
4430: l_effective_end_date := l_adj_eff_end_date;
4431: hr_utility.trace('After csr_basic_inf_current, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4432: hr_utility.trace('l_payroll_id='||l_payroll_id);
4433: hr_utility.trace('l_assignment_number='||l_assignment_number);
4434: hr_utility.trace('l_person_id='||l_person_id);
4435: hr_utility.trace('l_organization_id='||l_organization_id);
4436: hr_utility.trace('l_location_id='||l_location_id);
4437: hr_utility.trace('l_people_group_id='||l_people_group_id);
4430: l_effective_end_date := l_adj_eff_end_date;
4431: hr_utility.trace('After csr_basic_inf_current, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4432: hr_utility.trace('l_payroll_id='||l_payroll_id);
4433: hr_utility.trace('l_assignment_number='||l_assignment_number);
4434: hr_utility.trace('l_person_id='||l_person_id);
4435: hr_utility.trace('l_organization_id='||l_organization_id);
4436: hr_utility.trace('l_location_id='||l_location_id);
4437: hr_utility.trace('l_people_group_id='||l_people_group_id);
4438: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4431: hr_utility.trace('After csr_basic_inf_current, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4432: hr_utility.trace('l_payroll_id='||l_payroll_id);
4433: hr_utility.trace('l_assignment_number='||l_assignment_number);
4434: hr_utility.trace('l_person_id='||l_person_id);
4435: hr_utility.trace('l_organization_id='||l_organization_id);
4436: hr_utility.trace('l_location_id='||l_location_id);
4437: hr_utility.trace('l_people_group_id='||l_people_group_id);
4438: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4439: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4432: hr_utility.trace('l_payroll_id='||l_payroll_id);
4433: hr_utility.trace('l_assignment_number='||l_assignment_number);
4434: hr_utility.trace('l_person_id='||l_person_id);
4435: hr_utility.trace('l_organization_id='||l_organization_id);
4436: hr_utility.trace('l_location_id='||l_location_id);
4437: hr_utility.trace('l_people_group_id='||l_people_group_id);
4438: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4439: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4440: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4433: hr_utility.trace('l_assignment_number='||l_assignment_number);
4434: hr_utility.trace('l_person_id='||l_person_id);
4435: hr_utility.trace('l_organization_id='||l_organization_id);
4436: hr_utility.trace('l_location_id='||l_location_id);
4437: hr_utility.trace('l_people_group_id='||l_people_group_id);
4438: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4439: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4440: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4441: l_assignment_message :=
4434: hr_utility.trace('l_person_id='||l_person_id);
4435: hr_utility.trace('l_organization_id='||l_organization_id);
4436: hr_utility.trace('l_location_id='||l_location_id);
4437: hr_utility.trace('l_people_group_id='||l_people_group_id);
4438: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4439: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4440: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4441: l_assignment_message :=
4442: 'The Assignment has been updated during this process';
4435: hr_utility.trace('l_organization_id='||l_organization_id);
4436: hr_utility.trace('l_location_id='||l_location_id);
4437: hr_utility.trace('l_people_group_id='||l_people_group_id);
4438: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4439: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4440: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4441: l_assignment_message :=
4442: 'The Assignment has been updated during this process';
4443: archive_asg_info(g_assignment_message_eid, l_assignment_message);
4436: hr_utility.trace('l_location_id='||l_location_id);
4437: hr_utility.trace('l_people_group_id='||l_people_group_id);
4438: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id);
4439: hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
4440: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4441: l_assignment_message :=
4442: 'The Assignment has been updated during this process';
4443: archive_asg_info(g_assignment_message_eid, l_assignment_message);
4444: END IF;
4461: IF csr_basic_inf_term%FOUND then
4462: -- Set the eff end to the adjusted one from the
4463: -- terminated assignment.
4464: l_effective_end_date := l_adj_eff_end_date;
4465: /* hr_utility.trace('After csr_basic_inf_term, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4466: hr_utility.trace('l_payroll_id='||l_payroll_id);
4467: hr_utility.trace('l_assignment_number='||l_assignment_number);
4468: hr_utility.trace('l_person_id='||l_person_id);
4469: hr_utility.trace('l_organization_id='||l_organization_id);
4462: -- Set the eff end to the adjusted one from the
4463: -- terminated assignment.
4464: l_effective_end_date := l_adj_eff_end_date;
4465: /* hr_utility.trace('After csr_basic_inf_term, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4466: hr_utility.trace('l_payroll_id='||l_payroll_id);
4467: hr_utility.trace('l_assignment_number='||l_assignment_number);
4468: hr_utility.trace('l_person_id='||l_person_id);
4469: hr_utility.trace('l_organization_id='||l_organization_id);
4470: hr_utility.trace('l_location_id='||l_location_id);
4463: -- terminated assignment.
4464: l_effective_end_date := l_adj_eff_end_date;
4465: /* hr_utility.trace('After csr_basic_inf_term, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4466: hr_utility.trace('l_payroll_id='||l_payroll_id);
4467: hr_utility.trace('l_assignment_number='||l_assignment_number);
4468: hr_utility.trace('l_person_id='||l_person_id);
4469: hr_utility.trace('l_organization_id='||l_organization_id);
4470: hr_utility.trace('l_location_id='||l_location_id);
4471: hr_utility.trace('l_people_group_id='||l_people_group_id);
4464: l_effective_end_date := l_adj_eff_end_date;
4465: /* hr_utility.trace('After csr_basic_inf_term, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4466: hr_utility.trace('l_payroll_id='||l_payroll_id);
4467: hr_utility.trace('l_assignment_number='||l_assignment_number);
4468: hr_utility.trace('l_person_id='||l_person_id);
4469: hr_utility.trace('l_organization_id='||l_organization_id);
4470: hr_utility.trace('l_location_id='||l_location_id);
4471: hr_utility.trace('l_people_group_id='||l_people_group_id);
4472: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4465: /* hr_utility.trace('After csr_basic_inf_term, l_adj_eff_end_date='||fnd_date.date_to_displaydate(l_adj_eff_end_date));
4466: hr_utility.trace('l_payroll_id='||l_payroll_id);
4467: hr_utility.trace('l_assignment_number='||l_assignment_number);
4468: hr_utility.trace('l_person_id='||l_person_id);
4469: hr_utility.trace('l_organization_id='||l_organization_id);
4470: hr_utility.trace('l_location_id='||l_location_id);
4471: hr_utility.trace('l_people_group_id='||l_people_group_id);
4472: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4473: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4466: hr_utility.trace('l_payroll_id='||l_payroll_id);
4467: hr_utility.trace('l_assignment_number='||l_assignment_number);
4468: hr_utility.trace('l_person_id='||l_person_id);
4469: hr_utility.trace('l_organization_id='||l_organization_id);
4470: hr_utility.trace('l_location_id='||l_location_id);
4471: hr_utility.trace('l_people_group_id='||l_people_group_id);
4472: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4473: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4474: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4467: hr_utility.trace('l_assignment_number='||l_assignment_number);
4468: hr_utility.trace('l_person_id='||l_person_id);
4469: hr_utility.trace('l_organization_id='||l_organization_id);
4470: hr_utility.trace('l_location_id='||l_location_id);
4471: hr_utility.trace('l_people_group_id='||l_people_group_id);
4472: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4473: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4474: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4475: l_assignment_message :=
4468: hr_utility.trace('l_person_id='||l_person_id);
4469: hr_utility.trace('l_organization_id='||l_organization_id);
4470: hr_utility.trace('l_location_id='||l_location_id);
4471: hr_utility.trace('l_people_group_id='||l_people_group_id);
4472: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4473: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4474: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4475: l_assignment_message :=
4476: 'The Assignment has been Terminated during this process';
4469: hr_utility.trace('l_organization_id='||l_organization_id);
4470: hr_utility.trace('l_location_id='||l_location_id);
4471: hr_utility.trace('l_people_group_id='||l_people_group_id);
4472: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4473: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4474: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4475: l_assignment_message :=
4476: 'The Assignment has been Terminated during this process';
4477: archive_asg_info(g_assignment_message_eid, l_assignment_message);
4470: hr_utility.trace('l_location_id='||l_location_id);
4471: hr_utility.trace('l_people_group_id='||l_people_group_id);
4472: hr_utility.trace('l_period_of_service_id='||l_period_of_service_id); */
4473: hr_utility.trace('TERMINATION: '||to_char(l_assignment_id));
4474: hr_utility.trace('End date used: '||to_char(l_adj_eff_end_date));
4475: l_assignment_message :=
4476: 'The Assignment has been Terminated during this process';
4477: archive_asg_info(g_assignment_message_eid, l_assignment_message);
4478: ELSE
4478: ELSE
4479: -- Both cursors not found, so archive a default error msg.
4480: -- This should never be raised but placed to trap error where
4481: -- the asg has been somehow purged.
4482: hr_utility.trace('ASG NOT FOUND: '||to_char(l_assignment_id));
4483: l_assignment_message :=
4484: 'ERROR: Assignment Cannot be found from archived info';
4485: archive_asg_info(g_assignment_message_eid, l_assignment_message);
4486: END IF;
4506: --
4507: END IF;
4508: --
4509: IF l_payroll_id IS NULL THEN
4510: hr_utility.trace('l_assignment_message='||l_assignment_message);
4511: fnd_file.put_line(fnd_file.log, 'Error encountered while processing assignment action '||p_assactid);
4512: fnd_file.put_line(fnd_file.log, l_assignment_message);
4513: l_dummy := write_output(p_assignment_number => l_assignment_number,
4514: p_full_name => l_last_name||', '||l_first_name,
4529: l_payroll_tax_ref := g_pay_tax_ref_tab(l_payroll_id);
4530: l_payroll_tax_dist := g_pay_tax_dist_tab(l_payroll_id);
4531: --
4532: l_number_per_fiscal_yr := g_no_per_fiscal_yr(l_payroll_id);
4533: hr_utility.trace('Payroll Info Cached previously for: '||to_char(l_payroll_id));
4534: --
4535: END;
4536: --
4537: -- Get asg Start date
4534: --
4535: END;
4536: --
4537: -- Get asg Start date
4538: hr_utility.trace('Calling csr_asg_start');
4539: 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));
4540: OPEN csr_asg_start(l_assignment_id, l_effective_end_date,
4541: l_payroll_start_year, l_payroll_end_year);
4542: FETCH csr_asg_start INTO l_effective_start_date;
4535: END;
4536: --
4537: -- Get asg Start date
4538: hr_utility.trace('Calling csr_asg_start');
4539: 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));
4540: OPEN csr_asg_start(l_assignment_id, l_effective_end_date,
4541: l_payroll_start_year, l_payroll_end_year);
4542: FETCH csr_asg_start INTO l_effective_start_date;
4543: CLOSE csr_asg_start;
4540: OPEN csr_asg_start(l_assignment_id, l_effective_end_date,
4541: l_payroll_start_year, l_payroll_end_year);
4542: FETCH csr_asg_start INTO l_effective_start_date;
4543: CLOSE csr_asg_start;
4544: hr_utility.trace('After csr_asg_start, l_effective_start_date='||fnd_date.date_to_displaydate(l_effective_start_date));
4545: --
4546: hr_utility.trace('Action type: '||l_action_type);
4547: --
4548: -- Get basic person info
4542: FETCH csr_asg_start INTO l_effective_start_date;
4543: CLOSE csr_asg_start;
4544: hr_utility.trace('After csr_asg_start, l_effective_start_date='||fnd_date.date_to_displaydate(l_effective_start_date));
4545: --
4546: hr_utility.trace('Action type: '||l_action_type);
4547: --
4548: -- Get basic person info
4549: OPEN csr_person_info(l_person_id);
4550: FETCH csr_person_info INTO l_last_name,
4558: l_pensioner_indicator,
4559: l_aggregated_paye_flag,
4560: l_multiple_asg_flag;
4561: CLOSE csr_person_info;
4562: hr_utility.trace('After csr_person_info, l_last_name='||l_last_name);
4563: hr_utility.trace('l_first_name='||l_first_name);
4564: hr_utility.trace('l_middle_name='||l_middle_name);
4565: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4566: hr_utility.trace('l_title='||l_title);
4559: l_aggregated_paye_flag,
4560: l_multiple_asg_flag;
4561: CLOSE csr_person_info;
4562: hr_utility.trace('After csr_person_info, l_last_name='||l_last_name);
4563: hr_utility.trace('l_first_name='||l_first_name);
4564: hr_utility.trace('l_middle_name='||l_middle_name);
4565: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4566: hr_utility.trace('l_title='||l_title);
4567: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4560: l_multiple_asg_flag;
4561: CLOSE csr_person_info;
4562: hr_utility.trace('After csr_person_info, l_last_name='||l_last_name);
4563: hr_utility.trace('l_first_name='||l_first_name);
4564: hr_utility.trace('l_middle_name='||l_middle_name);
4565: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4566: hr_utility.trace('l_title='||l_title);
4567: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4568: hr_utility.trace('l_ni_number='||l_ni_number);
4561: CLOSE csr_person_info;
4562: hr_utility.trace('After csr_person_info, l_last_name='||l_last_name);
4563: hr_utility.trace('l_first_name='||l_first_name);
4564: hr_utility.trace('l_middle_name='||l_middle_name);
4565: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4566: hr_utility.trace('l_title='||l_title);
4567: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4568: hr_utility.trace('l_ni_number='||l_ni_number);
4569: hr_utility.trace('l_sex='||l_sex);
4562: hr_utility.trace('After csr_person_info, l_last_name='||l_last_name);
4563: hr_utility.trace('l_first_name='||l_first_name);
4564: hr_utility.trace('l_middle_name='||l_middle_name);
4565: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4566: hr_utility.trace('l_title='||l_title);
4567: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4568: hr_utility.trace('l_ni_number='||l_ni_number);
4569: hr_utility.trace('l_sex='||l_sex);
4570: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4563: hr_utility.trace('l_first_name='||l_first_name);
4564: hr_utility.trace('l_middle_name='||l_middle_name);
4565: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4566: hr_utility.trace('l_title='||l_title);
4567: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4568: hr_utility.trace('l_ni_number='||l_ni_number);
4569: hr_utility.trace('l_sex='||l_sex);
4570: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4571: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4564: hr_utility.trace('l_middle_name='||l_middle_name);
4565: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4566: hr_utility.trace('l_title='||l_title);
4567: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4568: hr_utility.trace('l_ni_number='||l_ni_number);
4569: hr_utility.trace('l_sex='||l_sex);
4570: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4571: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4572: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4565: hr_utility.trace('l_date_of_birth='||fnd_date.date_to_displaydate(l_date_of_birth));
4566: hr_utility.trace('l_title='||l_title);
4567: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4568: hr_utility.trace('l_ni_number='||l_ni_number);
4569: hr_utility.trace('l_sex='||l_sex);
4570: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4571: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4572: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4573: --
4566: hr_utility.trace('l_title='||l_title);
4567: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4568: hr_utility.trace('l_ni_number='||l_ni_number);
4569: hr_utility.trace('l_sex='||l_sex);
4570: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4571: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4572: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4573: --
4574: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4567: hr_utility.trace('l_expense_check_to_address='||l_expense_check_to_address);
4568: hr_utility.trace('l_ni_number='||l_ni_number);
4569: hr_utility.trace('l_sex='||l_sex);
4570: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4571: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4572: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4573: --
4574: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4575: hr_utility.trace('l_payroll_tax_dist='||l_payroll_tax_dist);
4568: hr_utility.trace('l_ni_number='||l_ni_number);
4569: hr_utility.trace('l_sex='||l_sex);
4570: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4571: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4572: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4573: --
4574: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4575: hr_utility.trace('l_payroll_tax_dist='||l_payroll_tax_dist);
4576: hr_utility.trace('l_assignment_id='||l_assignment_id);
4570: hr_utility.trace('l_pensioner_indicator='||l_pensioner_indicator);
4571: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4572: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4573: --
4574: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4575: hr_utility.trace('l_payroll_tax_dist='||l_payroll_tax_dist);
4576: hr_utility.trace('l_assignment_id='||l_assignment_id);
4577: OPEN get_asg_active_range(l_assignment_id, l_payroll_tax_dist||'/'||l_payroll_tax_ref);
4578: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4571: hr_utility.trace('l_aggregated_paye_flag='||l_aggregated_paye_flag);
4572: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4573: --
4574: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4575: hr_utility.trace('l_payroll_tax_dist='||l_payroll_tax_dist);
4576: hr_utility.trace('l_assignment_id='||l_assignment_id);
4577: OPEN get_asg_active_range(l_assignment_id, l_payroll_tax_dist||'/'||l_payroll_tax_ref);
4578: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4579: CLOSE get_asg_active_range;
4572: hr_utility.trace('l_multiple_asg_flag='||l_multiple_asg_flag);
4573: --
4574: hr_utility.trace('Before get_asg_active_range, l_payroll_tax_ref='||l_payroll_tax_ref);
4575: hr_utility.trace('l_payroll_tax_dist='||l_payroll_tax_dist);
4576: hr_utility.trace('l_assignment_id='||l_assignment_id);
4577: OPEN get_asg_active_range(l_assignment_id, l_payroll_tax_dist||'/'||l_payroll_tax_ref);
4578: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4579: CLOSE get_asg_active_range;
4580: hr_utility.trace('After get_asg_active_range.');
4576: hr_utility.trace('l_assignment_id='||l_assignment_id);
4577: OPEN get_asg_active_range(l_assignment_id, l_payroll_tax_dist||'/'||l_payroll_tax_ref);
4578: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4579: CLOSE get_asg_active_range;
4580: hr_utility.trace('After get_asg_active_range.');
4581: hr_utility.trace('l_active_start='||fnd_date.date_to_displaydate(l_active_start));
4582: hr_utility.trace('l_active_end='||fnd_date.date_to_displaydate(l_active_end));
4583: --
4584: -- set termination type and date.
4577: OPEN get_asg_active_range(l_assignment_id, l_payroll_tax_dist||'/'||l_payroll_tax_ref);
4578: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4579: CLOSE get_asg_active_range;
4580: hr_utility.trace('After get_asg_active_range.');
4581: hr_utility.trace('l_active_start='||fnd_date.date_to_displaydate(l_active_start));
4582: hr_utility.trace('l_active_end='||fnd_date.date_to_displaydate(l_active_end));
4583: --
4584: -- set termination type and date.
4585: IF l_tax_ref_transfer = 'N' THEN
4578: FETCH get_asg_active_range INTO l_active_start, l_active_end;
4579: CLOSE get_asg_active_range;
4580: hr_utility.trace('After get_asg_active_range.');
4581: hr_utility.trace('l_active_start='||fnd_date.date_to_displaydate(l_active_start));
4582: hr_utility.trace('l_active_end='||fnd_date.date_to_displaydate(l_active_end));
4583: --
4584: -- set termination type and date.
4585: IF l_tax_ref_transfer = 'N' THEN
4586: --
4583: --
4584: -- set termination type and date.
4585: IF l_tax_ref_transfer = 'N' THEN
4586: --
4587: hr_utility.set_location(l_proc,10);
4588: -- not a tax reference transfer.
4589: OPEN csr_termination(l_period_of_service_id,
4590: l_effective_end_date);
4591: FETCH csr_termination INTO l_actual_termination_date,
4591: FETCH csr_termination INTO l_actual_termination_date,
4592: l_last_std_process_date,
4593: l_termination_type;
4594: CLOSE csr_termination;
4595: hr_utility.trace('After csr_termination, l_actual_termination_date='||fnd_date.date_to_displaydate(l_actual_termination_date));
4596: hr_utility.trace('l_last_std_process_date='||fnd_date.date_to_displaydate(l_last_std_process_date));
4597: hr_utility.trace('l_termination_type='||l_termination_type);
4598: IF l_multiple_asg_flag = 'Y' THEN
4599: -- for multiple assignment check if aggregated active end date
4592: l_last_std_process_date,
4593: l_termination_type;
4594: CLOSE csr_termination;
4595: hr_utility.trace('After csr_termination, l_actual_termination_date='||fnd_date.date_to_displaydate(l_actual_termination_date));
4596: hr_utility.trace('l_last_std_process_date='||fnd_date.date_to_displaydate(l_last_std_process_date));
4597: hr_utility.trace('l_termination_type='||l_termination_type);
4598: IF l_multiple_asg_flag = 'Y' THEN
4599: -- for multiple assignment check if aggregated active end date
4600: -- is before the actual termination date and EOY, if yes then set
4593: l_termination_type;
4594: CLOSE csr_termination;
4595: hr_utility.trace('After csr_termination, l_actual_termination_date='||fnd_date.date_to_displaydate(l_actual_termination_date));
4596: hr_utility.trace('l_last_std_process_date='||fnd_date.date_to_displaydate(l_last_std_process_date));
4597: hr_utility.trace('l_termination_type='||l_termination_type);
4598: IF l_multiple_asg_flag = 'Y' THEN
4599: -- for multiple assignment check if aggregated active end date
4600: -- is before the actual termination date and EOY, if yes then set
4601: -- termination date to aggregated active end date
4627: -- the end of current tax year, do nothing here, leave
4628: -- l_termination_date as actual_termination date to report on
4629: -- P14 EDI and to ensure P60 is not generated
4630: NULL;
4631: hr_utility.trace('Employee terminated and last std process before EOY.');
4632: ELSE
4633: -- Employee has been terminated but last_std_process_date is
4634: -- after current tax year hence need to check if P45 has been
4635: -- issued, if yes then leave l_termination_date as actual
4636: -- termination date else ensire l_termination_date is null
4637: -- so that P60 can be generated
4638: l_date_of_manual_p45_issue := NULL;
4639: l_date_of_manual_p45_issue := pay_p45_pkg.get_p45_eit_manual_issue_dt(l_assignment_id);
4640: hr_utility.trace('After get_p45_eit_manual_issue_dt, manual issue date='||fnd_date.date_to_displaydate(l_date_of_manual_p45_issue));
4641: --
4642: IF l_date_of_manual_p45_issue IS NOT NULL
4643: AND l_date_of_manual_p45_issue <= g_end_year THEN
4644: -- manual P45 issued in current tax year hence leave
4656: pay_p45_pkg.get_p45_asg_action_id(l_assignment_id,
4657: l_p45_action_id,
4658: l_p45_issue_date,
4659: l_p45_action_seq);
4660: hr_utility.trace('After get_p45_asg_action_id, l_p45_issue_date='||fnd_date.date_to_displaydate(l_p45_issue_date));
4661: hr_utility.trace('l_p45_action_id='||l_p45_action_id);
4662:
4663: IF l_p45_issue_date IS NULL THEN
4664: pay_p45_pkg.get_p45_agg_asg_action_id(l_assignment_id,
4657: l_p45_action_id,
4658: l_p45_issue_date,
4659: l_p45_action_seq);
4660: hr_utility.trace('After get_p45_asg_action_id, l_p45_issue_date='||fnd_date.date_to_displaydate(l_p45_issue_date));
4661: hr_utility.trace('l_p45_action_id='||l_p45_action_id);
4662:
4663: IF l_p45_issue_date IS NULL THEN
4664: pay_p45_pkg.get_p45_agg_asg_action_id(l_assignment_id,
4665: l_p45_agg_asg_id,
4665: l_p45_agg_asg_id,
4666: l_p45_final_pay_date,
4667: l_p45_issue_date,
4668: l_p45_action_id);
4669: hr_utility.trace('After get_p45_agg_asg_action_id, l_p45_issue_date='||fnd_date.date_to_displaydate(l_p45_issue_date));
4670:
4671: END IF;
4672:
4673: -- Start of BUG 5671777-1
4688: -- p45 has been issued before the end of tax year hence
4689: -- leave l_termination_date as actual_termination_date to
4690: -- report on p14 EDI and to ensure P60 is not generated
4691: NULL;
4692: hr_utility.trace('P45 has been issued before the EOY.');
4693: ELSE
4694: -- p45 has not been issued before EOY hence don't archive
4695: -- actual termination date so that p60 can be generated,
4696: -- set l_termination_date to NULL
4699: END IF; -- l_date_of_manual_p45_issue IS (NOT) NULL
4700: END IF; -- l_last_std_process_date IS (NOT) in the current tax year
4701: END IF; -- Termination date (NOT) NULL
4702: ELSE
4703: hr_utility.set_location(l_proc,20);
4704: -- asg is tax ref transfer
4705: l_termination_type := 'R';
4706: l_termination_date := l_effective_end_date;
4707: END IF;
4707: END IF;
4708: --
4709: -- get last action this year, using stat dates.
4710: --
4711: hr_utility.trace('Calling csr_last_action');
4712: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4713: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4714: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4715: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4708: --
4709: -- get last action this year, using stat dates.
4710: --
4711: hr_utility.trace('Calling csr_last_action');
4712: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4713: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4714: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4715: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4716: hr_utility.trace('End Yr: '||to_char(g_end_year));
4709: -- get last action this year, using stat dates.
4710: --
4711: hr_utility.trace('Calling csr_last_action');
4712: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4713: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4714: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4715: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4716: hr_utility.trace('End Yr: '||to_char(g_end_year));
4717: hr_utility.trace('Tax Ref Xfer: '||l_tax_ref_transfer);
4710: --
4711: hr_utility.trace('Calling csr_last_action');
4712: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4713: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4714: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4715: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4716: hr_utility.trace('End Yr: '||to_char(g_end_year));
4717: hr_utility.trace('Tax Ref Xfer: '||l_tax_ref_transfer);
4718: OPEN csr_last_action(l_assignment_id, l_effective_start_date,
4711: hr_utility.trace('Calling csr_last_action');
4712: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4713: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4714: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4715: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4716: hr_utility.trace('End Yr: '||to_char(g_end_year));
4717: hr_utility.trace('Tax Ref Xfer: '||l_tax_ref_transfer);
4718: OPEN csr_last_action(l_assignment_id, l_effective_start_date,
4719: l_effective_end_date, g_start_year,
4712: hr_utility.trace('With asg: '|| to_char(l_assignment_id));
4713: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4714: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4715: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4716: hr_utility.trace('End Yr: '||to_char(g_end_year));
4717: hr_utility.trace('Tax Ref Xfer: '||l_tax_ref_transfer);
4718: OPEN csr_last_action(l_assignment_id, l_effective_start_date,
4719: l_effective_end_date, g_start_year,
4720: g_end_year, l_tax_ref_transfer);
4713: hr_utility.trace('Eff start: '|| to_char(l_effective_start_date));
4714: hr_utility.trace('Eff end: '|| to_char(l_effective_end_date));
4715: hr_utility.trace('Start Yr: '||to_char(g_start_year));
4716: hr_utility.trace('End Yr: '||to_char(g_end_year));
4717: hr_utility.trace('Tax Ref Xfer: '||l_tax_ref_transfer);
4718: OPEN csr_last_action(l_assignment_id, l_effective_start_date,
4719: l_effective_end_date, g_start_year,
4720: g_end_year, l_tax_ref_transfer);
4721: FETCH csr_last_action INTO l_last_asg_action_id,
4720: g_end_year, l_tax_ref_transfer);
4721: FETCH csr_last_action INTO l_last_asg_action_id,
4722: l_last_effective_date;
4723: CLOSE csr_last_action;
4724: hr_utility.trace('After csr_last_action, l_last_asg_action_id='||l_last_asg_action_id);
4725: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4726: --
4727: IF l_last_asg_action_id IS NOT NULL THEN
4728: OPEN csr_action_details(l_last_asg_action_id);
4721: FETCH csr_last_action INTO l_last_asg_action_id,
4722: l_last_effective_date;
4723: CLOSE csr_last_action;
4724: hr_utility.trace('After csr_last_action, l_last_asg_action_id='||l_last_asg_action_id);
4725: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4726: --
4727: IF l_last_asg_action_id IS NOT NULL THEN
4728: OPEN csr_action_details(l_last_asg_action_id);
4729: FETCH csr_action_details into l_action_type;
4727: IF l_last_asg_action_id IS NOT NULL THEN
4728: OPEN csr_action_details(l_last_asg_action_id);
4729: FETCH csr_action_details into l_action_type;
4730: CLOSE csr_action_details;
4731: hr_utility.trace('After csr_action_details, l_action_type='||l_action_type);
4732: END IF;
4733: --
4734: -- Get Non-NI balances and codes used to determine whether to further
4735: -- process the asg.
4790: -- fetch previous_taxable_pay,previous_tax_paid balances -- Bug 6271548
4791: --
4792: IF l_action_type <> 'V' THEN
4793: -- get paye element's run result id from last run
4794: hr_utility.trace('Before csr_tax_last_paye_run, l_last_asg_action_id='||l_last_asg_action_id);
4795: OPEN csr_tax_last_paye_run(l_last_asg_action_id);
4796: FETCH csr_tax_last_paye_run INTO l_tax_paye_run_result_id;
4797: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4798: IF l_tax_paye_run_result_id IS NULL THEN
4793: -- get paye element's run result id from last run
4794: hr_utility.trace('Before csr_tax_last_paye_run, l_last_asg_action_id='||l_last_asg_action_id);
4795: OPEN csr_tax_last_paye_run(l_last_asg_action_id);
4796: FETCH csr_tax_last_paye_run INTO l_tax_paye_run_result_id;
4797: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4798: IF l_tax_paye_run_result_id IS NULL THEN
4799: -- if paye was not calculated in last run then
4800: -- get the latest run in which it was
4801: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
4797: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4798: IF l_tax_paye_run_result_id IS NULL THEN
4799: -- if paye was not calculated in last run then
4800: -- get the latest run in which it was
4801: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
4802: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4803: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4804: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4805: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4798: IF l_tax_paye_run_result_id IS NULL THEN
4799: -- if paye was not calculated in last run then
4800: -- get the latest run in which it was
4801: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
4802: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4803: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4804: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4805: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4806: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4801: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
4802: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4803: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4804: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4805: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4806: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4807: CLOSE csr_tax_latest_paye_run;
4808: ELSE
4809: -- PAYE run result found hence effective date for paye details is
4802: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4803: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4804: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4805: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4806: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4807: CLOSE csr_tax_latest_paye_run;
4808: ELSE
4809: -- PAYE run result found hence effective date for paye details is
4810: -- date of last assignment action
4808: ELSE
4809: -- PAYE run result found hence effective date for paye details is
4810: -- date of last assignment action
4811: l_paye_eff_date := l_last_effective_date;
4812: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4813: END IF;
4814: CLOSE csr_tax_last_paye_run;
4815: -- most people will have had paye calculated on the last run.
4816: -- Pick these up
4813: END IF;
4814: CLOSE csr_tax_last_paye_run;
4815: -- most people will have had paye calculated on the last run.
4816: -- Pick these up
4817: hr_utility.trace('Before csr_tax_last_run, l_last_asg_action_id='||l_last_asg_action_id);
4818: OPEN csr_tax_last_run(l_last_asg_action_id);
4819: FETCH csr_tax_last_run INTO l_tax_run_result_id;
4820: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
4821: IF l_tax_run_result_id IS NULL THEN
4816: -- Pick these up
4817: hr_utility.trace('Before csr_tax_last_run, l_last_asg_action_id='||l_last_asg_action_id);
4818: OPEN csr_tax_last_run(l_last_asg_action_id);
4819: FETCH csr_tax_last_run INTO l_tax_run_result_id;
4820: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
4821: IF l_tax_run_result_id IS NULL THEN
4822: -- find the latest update
4823: hr_utility.trace('calling csr_tax_latest_run, non reversal');
4824: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4819: FETCH csr_tax_last_run INTO l_tax_run_result_id;
4820: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
4821: IF l_tax_run_result_id IS NULL THEN
4822: -- find the latest update
4823: hr_utility.trace('calling csr_tax_latest_run, non reversal');
4824: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4825: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4826: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4827: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4820: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
4821: IF l_tax_run_result_id IS NULL THEN
4822: -- find the latest update
4823: hr_utility.trace('calling csr_tax_latest_run, non reversal');
4824: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4825: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4826: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4827: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4828: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4821: IF l_tax_run_result_id IS NULL THEN
4822: -- find the latest update
4823: hr_utility.trace('calling csr_tax_latest_run, non reversal');
4824: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4825: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4826: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4827: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4828: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4829: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4824: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4825: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4826: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4827: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4828: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4829: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4830: CLOSE csr_tax_latest_run;
4831: ELSE
4832: l_paye_details_eff_date := l_last_effective_date;
4825: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4826: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4827: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4828: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4829: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4830: CLOSE csr_tax_latest_run;
4831: ELSE
4832: l_paye_details_eff_date := l_last_effective_date;
4833: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4829: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4830: CLOSE csr_tax_latest_run;
4831: ELSE
4832: l_paye_details_eff_date := l_last_effective_date;
4833: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4834: END IF;
4835: CLOSE csr_tax_last_run;
4836: ELSE
4837: -- find the latest update, as reversal action
4834: END IF;
4835: CLOSE csr_tax_last_run;
4836: ELSE
4837: -- find the latest update, as reversal action
4838: hr_utility.trace('calling csr_tax_latest_run, reversal');
4839: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4840: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4841: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4842: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4835: CLOSE csr_tax_last_run;
4836: ELSE
4837: -- find the latest update, as reversal action
4838: hr_utility.trace('calling csr_tax_latest_run, reversal');
4839: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4840: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4841: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4842: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4843: CLOSE csr_tax_latest_run;
4836: ELSE
4837: -- find the latest update, as reversal action
4838: hr_utility.trace('calling csr_tax_latest_run, reversal');
4839: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
4840: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4841: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4842: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4843: CLOSE csr_tax_latest_run;
4844: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4840: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
4841: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4842: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4843: CLOSE csr_tax_latest_run;
4844: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4845: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4846: --
4847: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4848: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4841: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
4842: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
4843: CLOSE csr_tax_latest_run;
4844: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
4845: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
4846: --
4847: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4848: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4849: CLOSE csr_tax_latest_paye_run;
4846: --
4847: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4848: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4849: CLOSE csr_tax_latest_paye_run;
4850: hr_utility.trace('After csr_tax_latest_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4851: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4852: END IF;
4853: --
4854: archive_asg_info(g_tax_run_result_id_eid, nvl(l_tax_paye_run_result_id, l_tax_run_result_id));
4847: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
4848: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
4849: CLOSE csr_tax_latest_paye_run;
4850: hr_utility.trace('After csr_tax_latest_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
4851: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
4852: END IF;
4853: --
4854: archive_asg_info(g_tax_run_result_id_eid, nvl(l_tax_paye_run_result_id, l_tax_run_result_id));
4855: --
4861: OPEN csr_tax_paye_result(l_tax_paye_run_result_id);
4862: FETCH csr_tax_paye_result INTO l_tax_code, l_w1_m1_indicator,
4863: l_previous_taxable_pay,
4864: l_previous_tax_paid;
4865: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
4866: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
4867: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
4868: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
4869: IF l_tax_code IS NULL THEN
4862: FETCH csr_tax_paye_result INTO l_tax_code, l_w1_m1_indicator,
4863: l_previous_taxable_pay,
4864: l_previous_tax_paid;
4865: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
4866: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
4867: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
4868: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
4869: IF l_tax_code IS NULL THEN
4870: -- Get the details from the element entry on the added criteria that
4863: l_previous_taxable_pay,
4864: l_previous_tax_paid;
4865: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
4866: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
4867: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
4868: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
4869: IF l_tax_code IS NULL THEN
4870: -- Get the details from the element entry on the added criteria that
4871: -- there exists an updating action id on the element_entry. In other
4864: l_previous_tax_paid;
4865: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
4866: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
4867: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
4868: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
4869: IF l_tax_code IS NULL THEN
4870: -- Get the details from the element entry on the added criteria that
4871: -- there exists an updating action id on the element_entry. In other
4872: -- words, an entry achieved using an Update Recurring rule.
4877: l_payroll_end_year, 'Y');
4878: FETCH csr_tax_details_entry INTO l_tax_code, l_w1_m1_indicator,
4879: l_previous_taxable_pay,
4880: l_previous_tax_paid;
4881: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
4882: l_w1_m1_indicator,52);
4883: IF l_tax_code IS NULL THEN
4884: -- no update recurring, so retrieve the details from the run result.
4885: OPEN csr_tax_details_result(l_tax_run_result_id);
4885: OPEN csr_tax_details_result(l_tax_run_result_id);
4886: FETCH csr_tax_details_result INTO l_tax_code, l_w1_m1_indicator,
4887: l_previous_taxable_pay,
4888: l_previous_tax_paid;
4889: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
4890: l_w1_m1_indicator,54);
4891: IF l_tax_code IS NULL THEN
4892: -- If there is still no tax code, use the element entry query
4893: -- without the update recurring criteria.
4896: l_payroll_end_year, 'N');
4897: FETCH csr_tax_details_entry INTO l_tax_code, l_w1_m1_indicator,
4898: l_previous_taxable_pay,
4899: l_previous_tax_paid;
4900: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
4901: l_w1_m1_indicator,56);
4902: IF l_tax_code IS NOT NULL THEN
4903: IF l_aggregated_paye_flag = 'Y' THEN
4904: hr_utility.trace('This is an aggregated assignment.');
4900: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
4901: l_w1_m1_indicator,56);
4902: IF l_tax_code IS NOT NULL THEN
4903: IF l_aggregated_paye_flag = 'Y' THEN
4904: hr_utility.trace('This is an aggregated assignment.');
4905: --
4906: if l_w1_m1_indicator = 'C' then
4907: l_w1_m1_indicator := ' ';
4908: else
4905: --
4906: if l_w1_m1_indicator = 'C' then
4907: l_w1_m1_indicator := ' ';
4908: else
4909: hr_utility.trace('No of periods per year = '||g_no_per_fiscal_yr(l_payroll_id));
4910: if g_no_per_fiscal_yr(l_payroll_id) in (1,2,4,6,12,24) then
4911: l_w1_m1_indicator := 'M';
4912: else
4913: l_w1_m1_indicator := 'W';
4912: else
4913: l_w1_m1_indicator := 'W';
4914: end if;
4915: end if;
4916: hr_utility.trace('Aggregated tax code found so far='||g_agg_balance_totals.tax_code);
4917: hr_utility.trace('g_agg_balance_totals.paye_eff_date = '||fnd_date.date_to_displaydate(g_agg_balance_totals.paye_eff_date));
4918: -- this tax code is from the PAYE Details Entry, since it is an
4919: -- aggregated assignment therefore check if we already have a
4920: -- a tax code from a previously processed assignment of this employee
4913: l_w1_m1_indicator := 'W';
4914: end if;
4915: end if;
4916: hr_utility.trace('Aggregated tax code found so far='||g_agg_balance_totals.tax_code);
4917: hr_utility.trace('g_agg_balance_totals.paye_eff_date = '||fnd_date.date_to_displaydate(g_agg_balance_totals.paye_eff_date));
4918: -- this tax code is from the PAYE Details Entry, since it is an
4919: -- aggregated assignment therefore check if we already have a
4920: -- a tax code from a previously processed assignment of this employee
4921: IF g_agg_balance_totals.tax_code is NULL THEN
4918: -- this tax code is from the PAYE Details Entry, since it is an
4919: -- aggregated assignment therefore check if we already have a
4920: -- a tax code from a previously processed assignment of this employee
4921: IF g_agg_balance_totals.tax_code is NULL THEN
4922: hr_utility.trace('Aggregated tax code found so far is NULL hence store '||l_tax_code||' as the aggregated tax code');
4923: -- No tax code found on other assignments so far therefore store
4924: -- tax code found on element entry of this assignment,
4925: -- this value will be archived against the primary eoy action later
4926: g_agg_balance_totals.tax_code := l_tax_code;
5004:
5005: -- fetch NI x Total/Able balances for checking whether this assignment
5006: -- reported in the P35 report or not. -- Bug 6271548
5007:
5008: hr_utility.trace('fetching NI x Total/Able balances');
5009: -- NI A
5010: l_nia_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nia_tot_id);
5011: l_nia_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5012: g_nia_able_id);
5041: l_nil_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5042: g_nil_able_id);
5043:
5044: -- NI F,G,S --EOY 2012/2013 begin block
5045: /* hr_utility.trace('fetching NI x Total/Able balances for F,G,S category');
5046: DECLARE
5047: l_sum_ni_tot NUMBER:= 0;
5048: l_sum_ni_able NUMBER:= 0;
5049: l_ni_ers NUMBER:= 0;
5072: l_ni_tot := l_sum_ni_tot;
5073: l_ni_able := l_sum_ni_able;
5074: END; */ --EOY 2012/2013 end block
5075:
5076: hr_utility.trace('l_ni_tot '||l_ni_tot);
5077: hr_utility.trace('l_ni_able '||l_ni_able );
5078: END IF;
5079:
5080: -- added additional conditions to check whether this assignment
5073: l_ni_able := l_sum_ni_able;
5074: END; */ --EOY 2012/2013 end block
5075:
5076: hr_utility.trace('l_ni_tot '||l_ni_tot);
5077: hr_utility.trace('l_ni_able '||l_ni_able );
5078: END IF;
5079:
5080: -- added additional conditions to check whether this assignment
5081: -- reported in the P35 report or not.
5097: l_nie_tot = 0 AND l_nie_able = 0 AND l_nij_tot = 0 AND l_nij_able = 0 AND
5098: l_nil_tot = 0 AND l_nil_able = 0 AND l_ni_tot = 0 AND l_ni_able = 0 ))
5099: -- End of Bug 6271548
5100: THEN
5101: hr_utility.trace('Do Not Process asg any further');
5102: -- Do not process this assignment any further and don't archive
5103: -- info extracted so far
5104: NULL;
5105: ELSE
5102: -- Do not process this assignment any further and don't archive
5103: -- info extracted so far
5104: NULL;
5105: ELSE
5106: hr_utility.set_location(l_proc,30);
5107: -- archive info extracted so far
5108: archive_asg_info(g_termination_type_eid,l_termination_type);
5109: archive_asg_info(g_payroll_id_eid,l_payroll_id);
5110: archive_asg_info(g_assignment_number_eid,l_assignment_number);
5143: g_masg_tax_ref_num := l_payroll_tax_ref;
5144: IF g_permit_number IS NOT NULL THEN
5145: -- a permit was specified through SRS.
5146: --
5147: hr_utility.set_location(l_proc,35);
5148: -- Check that this person does not have assignments in
5149: -- different permits:
5150: hr_utility.trace('Calling csr_get_invalid_multiple_asg');
5151: OPEN csr_get_invalid_multiple_asg(l_person_id, l_payroll_id,
5146: --
5147: hr_utility.set_location(l_proc,35);
5148: -- Check that this person does not have assignments in
5149: -- different permits:
5150: hr_utility.trace('Calling csr_get_invalid_multiple_asg');
5151: OPEN csr_get_invalid_multiple_asg(l_person_id, l_payroll_id,
5152: l_payroll_start_year,
5153: l_payroll_end_year,
5154: l_payroll_tax_ref);
5160: END IF; -- 1st asg
5161: END IF;
5162: IF g_has_non_extracted_masgs THEN
5163: -- error the assignment
5164: hr_utility.set_message(801, 'PAY_78000_MULTIPLE_PERMIT_ASG');
5165: hr_utility.raise_error;
5166: END IF;
5167: END IF; -- End of Multiple Assignment Logic Part I
5168: --
5161: END IF;
5162: IF g_has_non_extracted_masgs THEN
5163: -- error the assignment
5164: hr_utility.set_message(801, 'PAY_78000_MULTIPLE_PERMIT_ASG');
5165: hr_utility.raise_error;
5166: END IF;
5167: END IF; -- End of Multiple Assignment Logic Part I
5168: --
5169: OPEN csr_director(l_person_id);
5216: OPEN csr_country_name(l_country);
5217: FETCH csr_country_name INTO l_country_name;
5218: CLOSE csr_country_name;
5219: --
5220: hr_utility.set_location(l_proc,40);
5221: --
5222: -- Archive more asg info.
5223: hr_utility.trace('Archiving more info');
5224: archive_asg_info(g_last_name_eid,l_last_name);
5219: --
5220: hr_utility.set_location(l_proc,40);
5221: --
5222: -- Archive more asg info.
5223: hr_utility.trace('Archiving more info');
5224: archive_asg_info(g_last_name_eid,l_last_name);
5225: archive_asg_info(g_first_name_eid,l_first_name);
5226: archive_asg_info(g_middle_name_eid,l_middle_name);
5227: archive_asg_info(g_date_of_birth_eid,
5244: archive_asg_info(g_county_eid,l_county);
5245: archive_asg_info(g_country_eid,l_country_name); -- 4011263
5246: archive_asg_info(g_postal_code_eid,l_postal_code);
5247: --
5248: hr_utility.trace('End of archiving person and address info');
5249: -- Get more Non-NI balances and codes
5250: l_paye := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_paye_id);
5251: l_widow := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_widow_id);
5252: hr_utility.set_location(l_proc,50);
5248: hr_utility.trace('End of archiving person and address info');
5249: -- Get more Non-NI balances and codes
5250: l_paye := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_paye_id);
5251: l_widow := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_widow_id);
5252: hr_utility.set_location(l_proc,50);
5253: hr_utility.trace('l_paye='||l_paye||', g_paye_id='||g_paye_id);
5254: IF nvl(l_aggregated_paye_flag,'N') <> 'Y' THEN
5255: -- Not Aggregated PAYE, so archive all values as prior to
5256: -- introduction of agg PAYE.
5249: -- Get more Non-NI balances and codes
5250: l_paye := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_paye_id);
5251: l_widow := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_widow_id);
5252: hr_utility.set_location(l_proc,50);
5253: hr_utility.trace('l_paye='||l_paye||', g_paye_id='||g_paye_id);
5254: IF nvl(l_aggregated_paye_flag,'N') <> 'Y' THEN
5255: -- Not Aggregated PAYE, so archive all values as prior to
5256: -- introduction of agg PAYE.
5257: hr_utility.trace('Not Aggregated PAYE, archive at asg level');
5253: hr_utility.trace('l_paye='||l_paye||', g_paye_id='||g_paye_id);
5254: IF nvl(l_aggregated_paye_flag,'N') <> 'Y' THEN
5255: -- Not Aggregated PAYE, so archive all values as prior to
5256: -- introduction of agg PAYE.
5257: hr_utility.trace('Not Aggregated PAYE, archive at asg level');
5258: /* IF l_sex = 'F' Then rerstored as a part of 2987008
5259: was earlier commented out for 2003,
5260: And now commented out again for 2005, BUG 4011263 */
5261: archive_asg_info(g_smp_eid,l_smp);
5308: OPEN get_week_53_start(l_payroll_id);
5309: FETCH get_week_53_start INTO l_week_53_start;
5310: CLOSE get_week_53_start;
5311: --
5312: hr_utility.trace('After get_week_53_start, l_week_53_start='||
5313: fnd_date.date_to_displaydate(l_week_53_start));
5314: IF nvl(l_week_53_start, hr_general.end_of_time) <= l_active_end THEN
5315: l_week_53_indicator := substr(to_char(l_payroll_max_period_number),2,1);
5316: END IF;
5317: else
5318: l_week_53_indicator := ' ';
5319: end if;
5320: --
5321: hr_utility.trace('Before getting paye details from run results.');
5322: hr_utility.trace('l_action_type='||l_action_type);
5323: --
5324: -- 2166991: Check the Action type, if reversal then the
5325: -- csr_tax_last_run cannot be used, call csr_tax_latest_run.
5318: l_week_53_indicator := ' ';
5319: end if;
5320: --
5321: hr_utility.trace('Before getting paye details from run results.');
5322: hr_utility.trace('l_action_type='||l_action_type);
5323: --
5324: -- 2166991: Check the Action type, if reversal then the
5325: -- csr_tax_last_run cannot be used, call csr_tax_latest_run.
5326: -- Separate clause for V types for performance.
5331: -- Archiving the tax code details depending on the global g_paye_archive
5332: IF g_paye_archive = FALSE THEN
5333: IF l_action_type <> 'V' THEN
5334: -- get paye element's run result id from last run
5335: hr_utility.trace('Before csr_tax_last_paye_run, l_last_asg_action_id='||l_last_asg_action_id);
5336: OPEN csr_tax_last_paye_run(l_last_asg_action_id);
5337: FETCH csr_tax_last_paye_run INTO l_tax_paye_run_result_id;
5338: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5339: IF l_tax_paye_run_result_id IS NULL THEN
5334: -- get paye element's run result id from last run
5335: hr_utility.trace('Before csr_tax_last_paye_run, l_last_asg_action_id='||l_last_asg_action_id);
5336: OPEN csr_tax_last_paye_run(l_last_asg_action_id);
5337: FETCH csr_tax_last_paye_run INTO l_tax_paye_run_result_id;
5338: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5339: IF l_tax_paye_run_result_id IS NULL THEN
5340: -- if paye was not calculated in last run then
5341: -- get the latest run in which it was
5342: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
5338: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5339: IF l_tax_paye_run_result_id IS NULL THEN
5340: -- if paye was not calculated in last run then
5341: -- get the latest run in which it was
5342: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
5343: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5344: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5345: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5346: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5339: IF l_tax_paye_run_result_id IS NULL THEN
5340: -- if paye was not calculated in last run then
5341: -- get the latest run in which it was
5342: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
5343: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5344: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5345: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5346: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5347: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5342: hr_utility.trace('Before csr_tax_last_paye_run, l_assignment_id='||l_assignment_id);
5343: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5344: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5345: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5346: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5347: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5348: CLOSE csr_tax_latest_paye_run;
5349: ELSE
5350: -- PAYE run result found hence effective date for paye details is
5343: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5344: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5345: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5346: hr_utility.trace('After csr_tax_last_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5347: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5348: CLOSE csr_tax_latest_paye_run;
5349: ELSE
5350: -- PAYE run result found hence effective date for paye details is
5351: -- date of last assignment action
5349: ELSE
5350: -- PAYE run result found hence effective date for paye details is
5351: -- date of last assignment action
5352: l_paye_eff_date := l_last_effective_date;
5353: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5354: END IF;
5355: CLOSE csr_tax_last_paye_run;
5356: -- most people will have had paye calculated on the last run.
5357: -- Pick these up
5354: END IF;
5355: CLOSE csr_tax_last_paye_run;
5356: -- most people will have had paye calculated on the last run.
5357: -- Pick these up
5358: hr_utility.trace('Before csr_tax_last_run, l_last_asg_action_id='||l_last_asg_action_id);
5359: OPEN csr_tax_last_run(l_last_asg_action_id);
5360: FETCH csr_tax_last_run INTO l_tax_run_result_id;
5361: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
5362: IF l_tax_run_result_id IS NULL THEN
5357: -- Pick these up
5358: hr_utility.trace('Before csr_tax_last_run, l_last_asg_action_id='||l_last_asg_action_id);
5359: OPEN csr_tax_last_run(l_last_asg_action_id);
5360: FETCH csr_tax_last_run INTO l_tax_run_result_id;
5361: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
5362: IF l_tax_run_result_id IS NULL THEN
5363: -- find the latest update
5364: hr_utility.trace('calling csr_tax_latest_run, non reversal');
5365: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5360: FETCH csr_tax_last_run INTO l_tax_run_result_id;
5361: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
5362: IF l_tax_run_result_id IS NULL THEN
5363: -- find the latest update
5364: hr_utility.trace('calling csr_tax_latest_run, non reversal');
5365: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5366: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5367: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5368: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5361: hr_utility.trace('After csr_tax_last_run, l_tax_run_result_id='||l_tax_run_result_id);
5362: IF l_tax_run_result_id IS NULL THEN
5363: -- find the latest update
5364: hr_utility.trace('calling csr_tax_latest_run, non reversal');
5365: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5366: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5367: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5368: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5369: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5362: IF l_tax_run_result_id IS NULL THEN
5363: -- find the latest update
5364: hr_utility.trace('calling csr_tax_latest_run, non reversal');
5365: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5366: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5367: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5368: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5369: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5370: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5365: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5366: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5367: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5368: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5369: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5370: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5371: CLOSE csr_tax_latest_run;
5372: ELSE
5373: l_paye_details_eff_date := l_last_effective_date;
5366: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5367: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5368: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5369: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5370: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5371: CLOSE csr_tax_latest_run;
5372: ELSE
5373: l_paye_details_eff_date := l_last_effective_date;
5374: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5370: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5371: CLOSE csr_tax_latest_run;
5372: ELSE
5373: l_paye_details_eff_date := l_last_effective_date;
5374: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5375: END IF;
5376: CLOSE csr_tax_last_run;
5377: ELSE
5378: -- find the latest update, as reversal action
5375: END IF;
5376: CLOSE csr_tax_last_run;
5377: ELSE
5378: -- find the latest update, as reversal action
5379: hr_utility.trace('calling csr_tax_latest_run, reversal');
5380: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5381: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5382: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5383: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5376: CLOSE csr_tax_last_run;
5377: ELSE
5378: -- find the latest update, as reversal action
5379: hr_utility.trace('calling csr_tax_latest_run, reversal');
5380: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5381: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5382: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5383: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5384: CLOSE csr_tax_latest_run;
5377: ELSE
5378: -- find the latest update, as reversal action
5379: hr_utility.trace('calling csr_tax_latest_run, reversal');
5380: hr_utility.trace('Before csr_tax_latest_run, l_assignment_id='||l_assignment_id);
5381: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5382: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5383: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5384: CLOSE csr_tax_latest_run;
5385: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5381: hr_utility.trace('l_last_effective_date='||fnd_date.date_to_displaydate(l_last_effective_date));
5382: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5383: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5384: CLOSE csr_tax_latest_run;
5385: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5386: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5387: --
5388: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5389: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5382: OPEN csr_tax_latest_run(l_assignment_id, l_last_effective_date);
5383: FETCH csr_tax_latest_run INTO l_tax_run_result_id, l_paye_details_eff_date;
5384: CLOSE csr_tax_latest_run;
5385: hr_utility.trace('After csr_tax_latest_run, l_tax_run_result_id='||l_tax_run_result_id);
5386: hr_utility.trace('l_paye_details_eff_date='||fnd_date.date_to_displaydate(l_paye_details_eff_date));
5387: --
5388: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5389: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5390: CLOSE csr_tax_latest_paye_run;
5387: --
5388: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5389: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5390: CLOSE csr_tax_latest_paye_run;
5391: hr_utility.trace('After csr_tax_latest_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5392: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5393: END IF;
5394: --
5395: --
5388: OPEN csr_tax_latest_paye_run(l_assignment_id, l_last_effective_date);
5389: FETCH csr_tax_latest_paye_run INTO l_tax_paye_run_result_id, l_paye_eff_date;
5390: CLOSE csr_tax_latest_paye_run;
5391: hr_utility.trace('After csr_tax_latest_paye_run, l_tax_paye_run_result_id='||l_tax_paye_run_result_id);
5392: hr_utility.trace('l_paye_eff_date='||fnd_date.date_to_displaydate(l_paye_eff_date));
5393: END IF;
5394: --
5395: --
5396: -- archive_asg_info(g_tax_run_result_id_eid, nvl(l_tax_paye_run_result_id, l_tax_run_result_id));
5403: OPEN csr_tax_paye_result(l_tax_paye_run_result_id);
5404: FETCH csr_tax_paye_result INTO l_tax_code, l_w1_m1_indicator,
5405: l_previous_taxable_pay,
5406: l_previous_tax_paid;
5407: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
5408: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
5409: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
5410: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
5411: IF l_tax_code IS NULL THEN
5404: FETCH csr_tax_paye_result INTO l_tax_code, l_w1_m1_indicator,
5405: l_previous_taxable_pay,
5406: l_previous_tax_paid;
5407: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
5408: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
5409: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
5410: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
5411: IF l_tax_code IS NULL THEN
5412: -- Get the details from the element entry on the added criteria that
5405: l_previous_taxable_pay,
5406: l_previous_tax_paid;
5407: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
5408: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
5409: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
5410: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
5411: IF l_tax_code IS NULL THEN
5412: -- Get the details from the element entry on the added criteria that
5413: -- there exists an updating action id on the element_entry. In other
5406: l_previous_tax_paid;
5407: hr_utility.trace('After csr_tax_paye_result, l_tax_code='||l_tax_code);
5408: hr_utility.trace('l_w1_m1_indicator='||l_w1_m1_indicator);
5409: hr_utility.trace('l_previous_taxable_pay='||to_char(l_previous_taxable_pay));
5410: hr_utility.trace('l_previous_tax_paid='||to_char(l_previous_tax_paid));
5411: IF l_tax_code IS NULL THEN
5412: -- Get the details from the element entry on the added criteria that
5413: -- there exists an updating action id on the element_entry. In other
5414: -- words, an entry achieved using an Update Recurring rule.
5419: l_payroll_end_year, 'Y');
5420: FETCH csr_tax_details_entry INTO l_tax_code, l_w1_m1_indicator,
5421: l_previous_taxable_pay,
5422: l_previous_tax_paid;
5423: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
5424: l_w1_m1_indicator,52);
5425: IF l_tax_code IS NULL THEN
5426: -- no update recurring, so retrieve the details from the run result.
5427: OPEN csr_tax_details_result(l_tax_run_result_id);
5427: OPEN csr_tax_details_result(l_tax_run_result_id);
5428: FETCH csr_tax_details_result INTO l_tax_code, l_w1_m1_indicator,
5429: l_previous_taxable_pay,
5430: l_previous_tax_paid;
5431: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
5432: l_w1_m1_indicator,54);
5433: IF l_tax_code IS NULL THEN
5434: -- If there is still no tax code, use the element entry query
5435: -- without the update recurring criteria.
5438: l_payroll_end_year, 'N');
5439: FETCH csr_tax_details_entry INTO l_tax_code, l_w1_m1_indicator,
5440: l_previous_taxable_pay,
5441: l_previous_tax_paid;
5442: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
5443: l_w1_m1_indicator,56);
5444: IF l_tax_code IS NOT NULL THEN
5445: IF l_aggregated_paye_flag = 'Y' THEN
5446: hr_utility.trace('This is an aggregated assignment.');
5442: hr_utility.set_location(l_proc||' '||l_tax_code||' '||
5443: l_w1_m1_indicator,56);
5444: IF l_tax_code IS NOT NULL THEN
5445: IF l_aggregated_paye_flag = 'Y' THEN
5446: hr_utility.trace('This is an aggregated assignment.');
5447: --
5448: if l_w1_m1_indicator = 'C' then
5449: l_w1_m1_indicator := ' ';
5450: else
5447: --
5448: if l_w1_m1_indicator = 'C' then
5449: l_w1_m1_indicator := ' ';
5450: else
5451: hr_utility.trace('No of periods per year = '||g_no_per_fiscal_yr(l_payroll_id));
5452: if g_no_per_fiscal_yr(l_payroll_id) in (1,2,4,6,12,24) then
5453: l_w1_m1_indicator := 'M';
5454: else
5455: l_w1_m1_indicator := 'W';
5454: else
5455: l_w1_m1_indicator := 'W';
5456: end if;
5457: end if;
5458: hr_utility.trace('Aggregated tax code found so far='||g_agg_balance_totals.tax_code);
5459: hr_utility.trace('g_agg_balance_totals.paye_eff_date = '||fnd_date.date_to_displaydate(g_agg_balance_totals.paye_eff_date));
5460: -- this tax code is from the PAYE Details Entry, since it is an
5461: -- aggregated assignment therefore check if we already have a
5462: -- a tax code from a previously processed assignment of this employee
5455: l_w1_m1_indicator := 'W';
5456: end if;
5457: end if;
5458: hr_utility.trace('Aggregated tax code found so far='||g_agg_balance_totals.tax_code);
5459: hr_utility.trace('g_agg_balance_totals.paye_eff_date = '||fnd_date.date_to_displaydate(g_agg_balance_totals.paye_eff_date));
5460: -- this tax code is from the PAYE Details Entry, since it is an
5461: -- aggregated assignment therefore check if we already have a
5462: -- a tax code from a previously processed assignment of this employee
5463: IF g_agg_balance_totals.tax_code is NULL THEN
5460: -- this tax code is from the PAYE Details Entry, since it is an
5461: -- aggregated assignment therefore check if we already have a
5462: -- a tax code from a previously processed assignment of this employee
5463: IF g_agg_balance_totals.tax_code is NULL THEN
5464: hr_utility.trace('Aggregated tax code found so far is NULL hence store '||l_tax_code||' as the aggregated tax code');
5465: -- No tax code found on other assignments so far therefore store
5466: -- tax code found on element entry of this assignment,
5467: -- this value will be archived against the primary eoy action later
5468: g_agg_balance_totals.tax_code := l_tax_code;
5546: --
5547: g_paye_archive := TRUE;
5548: END IF;
5549: -- End of Bug 6271548
5550: hr_utility.set_location(l_proc,60);
5551: /**************************************/
5552: /* assigned blank space to l_tax_code */
5553: /**************************************/
5554: if (l_tax_code IS NULL) then
5587: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5588: g_nia_able_id); */
5589: IF nvl(l_nia_tot,0) <> 0 or nvl(l_nia_able,0) <> 0 THEN
5590: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5591: hr_utility.set_location(l_proc,70);
5592:
5593: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nia_id);
5594: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5595: g_nia_lel_id);
5628: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5629: g_nib_able_id);*/
5630: IF nvl(l_nib_tot,0) <> 0 or nvl(l_nib_able,0) <> 0 THEN
5631: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5632: hr_utility.set_location(l_proc,80);
5633: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5634: g_nib_id);
5635: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5636: g_nib_lel_id);
5669: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5670: g_nic_able_id);*/
5671: IF nvl(l_nic_tot,0) <> 0 or nvl(l_nic_able,0) <> 0 THEN
5672: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5673: hr_utility.set_location(l_proc,90);
5674: --archive_ni_values('C', l_ni_tot);
5675: -- Bug Fix 1976152, commented the above stmt, and included the below code
5676: -- to get the balance for NI C Employers Rebate and modified the call to
5677: -- archive_ni_values procedure
5726: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5727: g_nid_able_id);*/
5728: IF nvl(l_nid_tot,0) <> 0 or nvl(l_nid_able,0) <> 0 THEN
5729: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5730: hr_utility.set_location(l_proc,100);
5731: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nid_id);
5732: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5733: g_nid_lel_id);
5734: l_ni_able_uel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5774: /*l_ni_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_tot_id);
5775: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_able_id);*/
5776: IF nvl(l_nie_tot,0) <> 0 or nvl(l_nie_able,0) <> 0 THEN
5777: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5778: hr_utility.set_location(l_proc,110);
5779: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_id);
5780: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_lel_id);
5781: l_ni_able_uel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_uel_id);
5782: l_ni_able_uap := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nie_uap_id); -- 8357870
5808: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5809: g_nij_able_id);*/
5810: IF nvl(l_nij_tot,0) <> 0 or nvl(l_nij_able,0) <> 0 THEN
5811: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5812: hr_utility.set_location(l_proc,130);
5813: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nij_id);
5814: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5815: g_nij_lel_id);
5816: l_ni_able_uel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5845: l_ni_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5846: g_nil_able_id);*/
5847: IF nvl(l_nil_tot,0) <> 0 or nvl(l_nil_able,0) <> 0 THEN
5848: -- IF nvl(l_ni_tot,0) <> 0 or nvl(l_ni_able,0) <> 0 THEN
5849: hr_utility.set_location(l_proc,140);
5850: l_ni_ees := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nil_id);
5851: l_ni_able_lel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5852: g_nil_lel_id);
5853: l_ni_able_uel := 100 * hr_dirbal.get_balance(l_last_asg_action_id,
5888: THEN
5889: 4221300 */
5890: -- F/G/S Total(s) exist(s)
5891: /*
5892: hr_utility.set_location(l_proc,120);
5893: -- open cursor and populate year end values
5894: DECLARE
5895: l_sum_ni_tot NUMBER:= 0;
5896: l_sum_ni_ees NUMBER:= 0;
5924: OR l_store_scon <> l_scon)
5925: AND csr_get_scon_bal%rowcount > 0 THEN
5926: -- first row will not come here due to null trap with cat and scon
5927: -- write values from store
5928: hr_utility.trace('l_sum_ni_tot='||l_sum_ni_tot);
5929: hr_utility.trace('l_sum_ni_able='||l_sum_ni_able);
5930: -- Add following IF condition for 4221300
5931: IF (nvl(l_sum_ni_tot,0) <> 0 or nvl(l_sum_ni_able,0) <> 0) THEN
5932: archive_ni_values(l_store_cat, l_sum_ni_tot, l_sum_ni_able,
5925: AND csr_get_scon_bal%rowcount > 0 THEN
5926: -- first row will not come here due to null trap with cat and scon
5927: -- write values from store
5928: hr_utility.trace('l_sum_ni_tot='||l_sum_ni_tot);
5929: hr_utility.trace('l_sum_ni_able='||l_sum_ni_able);
5930: -- Add following IF condition for 4221300
5931: IF (nvl(l_sum_ni_tot,0) <> 0 or nvl(l_sum_ni_able,0) <> 0) THEN
5932: archive_ni_values(l_store_cat, l_sum_ni_tot, l_sum_ni_able,
5933: l_sum_ni_ees, l_sum_ni_able_lel,
5996: OPEN csr_current_cat(l_assignment_id, l_effective_end_date,
5997: l_payroll_end_year);
5998: FETCH csr_current_cat INTO l_ni_cat, l_scon;
5999: IF csr_current_cat%found THEN
6000: hr_utility.set_location(l_proc,160);
6001: archive_ni_values(p_ni_cat => l_ni_cat,
6002: p_tot_contribs => 0,
6003: p_scon => l_scon);
6004: ELSE
6013: -- If Aggregated PAYE flag is set, use the first assignment ID
6014: -- in the sequence, this is the rule for AggPAYE. nvl the
6015: -- assignment ID with the max number for the field.
6016: IF nvl(l_aggregated_paye_flag,'N') = 'Y' THEN
6017: hr_utility.set_location(l_proc,162);
6018: IF l_assignment_id < nvl(g_min_assignment_id,999999999) THEN
6019: hr_utility.set_location(l_proc,163);
6020: g_primary_action := p_assactid;
6021: g_min_assignment_id := l_assignment_id;
6015: -- assignment ID with the max number for the field.
6016: IF nvl(l_aggregated_paye_flag,'N') = 'Y' THEN
6017: hr_utility.set_location(l_proc,162);
6018: IF l_assignment_id < nvl(g_min_assignment_id,999999999) THEN
6019: hr_utility.set_location(l_proc,163);
6020: g_primary_action := p_assactid;
6021: g_min_assignment_id := l_assignment_id;
6022: END IF;
6023: ELSE
6031: -- g_max_gross_pay is null, i.e., first assignment of the emp
6032: IF nvl(l_gross,0) >= g_max_gross_pay
6033: OR g_max_gross_pay is null THEN
6034: -- store new max gross pay and asg act id
6035: hr_utility.set_location(l_proc,165);
6036: g_primary_action := p_assactid;
6037: g_max_gross_pay := nvl(l_gross,0);
6038: END IF;
6039: END IF;
6055: g_last_multi_asg_eid) = 'Y'
6056: THEN
6057: -- This is the last asg for the person in this tax ref
6058: -- archive the primary assignment flag against the primary action
6059: hr_utility.set_location(l_proc,170);
6060: ff_archive_api.create_archive_item
6061: (p_archive_item_id => l_archive_item_id,
6062: p_user_entity_id => g_eoy_primary_flag_eid,
6063: p_archive_value => 'Y',
6070: BEGIN
6071: l_index1:=0;
6072: LOOP -- actions
6073: l_index1 := l_index1 + 1;
6074: hr_utility.set_location(l_proc||' '||g_asg_actions(l_index1),180);
6075: IF g_asg_actions(l_index1) = g_primary_action THEN
6076: -- This is the Primary action so Archive Balance values.
6077: -- Firstly the Aggregated PAYE values if necessary.
6078: IF nvl(l_aggregated_paye_flag,'N') = 'Y' then
6076: -- This is the Primary action so Archive Balance values.
6077: -- Firstly the Aggregated PAYE values if necessary.
6078: IF nvl(l_aggregated_paye_flag,'N') = 'Y' then
6079: BEGIN
6080: hr_utility.set_location(l_proc,181);
6081: -- Get all values from the global table.
6082: /* IF l_sex = 'F' THEN rerstored as a part of 2987008
6083: was earlier commented out for 2003,
6084: And Commented out again for EOY 2005, Bug 4011263 */
6133: --
6134: IF g_agg_balance_totals.superann < 0 THEN
6135: archive_asg_info(g_superannuation_refund_eid,'R',g_primary_action);
6136: END IF;
6137: hr_utility.set_location(l_proc,182);
6138: --
6139: EXCEPTION WHEN NO_DATA_FOUND THEN
6140: NULL; -- All values archived
6141: END; -- Agg PAYE Block
6154: l_ni_refund_flag := '';
6155: end if;
6156: /* EOY 2012/2013 begin block*/
6157: /* IF g_ni_balance_totals(l_index2).scon IS NOT NULL THEN
6158: hr_utility.set_location(l_proc||' '||
6159: g_ni_balance_totals(l_index2).ni_cat||' '||
6160: g_ni_balance_totals(l_index2).scon,190);
6161: archive_ni_value(g_ni_scon_earnings_eid,
6162: g_ni_balance_totals(l_index2).earnings,2,
6218: g_ni_balance_totals(l_index2).scon,
6219: g_primary_action);
6220: ELSE */
6221: /* EOY 2012/2013 end block*/
6222: hr_utility.set_location(l_proc||' '||
6223: g_ni_balance_totals(l_index2).ni_cat,200);
6224: archive_ni_value(g_ni_earnings_eid,
6225: g_ni_balance_totals(l_index2).earnings,2,
6226: g_ni_balance_totals(l_index2).ni_cat,
6276: -- all NI values archived
6277: NULL;
6278: END; -- NI values block
6279: ELSE
6280: hr_utility.set_location(l_proc,210);
6281: -- not primary action, so archive reportable cat X 'row'.
6282: -- Need to archive the Total Contributions item as 0 as the
6283: -- values view relies on this item being in the archive in order
6284: -- for a row to be returned (it uses this item to obtain the
6292: NULL;
6293: END; -- action block
6294: --
6295: IF l_reportable_ni_archived THEN
6296: hr_utility.trace('Reportable NI values archived against primary action='||g_primary_action);
6297: archive_asg_info(g_reportable_ni_eid, 'Y', g_primary_action);
6298: END IF;
6299: --
6300: empty_masg_cache;
6301: END IF; -- l_last_multi_asg
6302: ELSE -- End of Multiple Assignment Logic Part IV
6303: -- This is not an employee with aggregated multiple assignments
6304: IF l_reportable_ni_archived THEN
6305: hr_utility.trace('Reportable NI values archived against current action='||p_assactid);
6306: archive_asg_info(g_reportable_ni_eid, 'Y');
6307: END IF;
6308: END IF;
6309: --
6306: archive_asg_info(g_reportable_ni_eid, 'Y');
6307: END IF;
6308: END IF;
6309: --
6310: hr_utility.set_location(' Leaving: '||l_proc,220);
6311: EXCEPTION
6312: WHEN ASG_ACTION_ERROR THEN
6313: hr_utility.trace('ASG_ACTION_ERROR exception raised.');
6314: hr_utility.set_location(' Leaving: '||l_proc,230);
6309: --
6310: hr_utility.set_location(' Leaving: '||l_proc,220);
6311: EXCEPTION
6312: WHEN ASG_ACTION_ERROR THEN
6313: hr_utility.trace('ASG_ACTION_ERROR exception raised.');
6314: hr_utility.set_location(' Leaving: '||l_proc,230);
6315: raise;
6316: WHEN others THEN --Added for bug 7326591
6317: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Erroneous Assignment ID ' || l_assignment_id);
6310: hr_utility.set_location(' Leaving: '||l_proc,220);
6311: EXCEPTION
6312: WHEN ASG_ACTION_ERROR THEN
6313: hr_utility.trace('ASG_ACTION_ERROR exception raised.');
6314: hr_utility.set_location(' Leaving: '||l_proc,230);
6315: raise;
6316: WHEN others THEN --Added for bug 7326591
6317: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Erroneous Assignment ID ' || l_assignment_id);
6318: hr_utility.set_location(' Exception at : '||l_proc,235);
6314: hr_utility.set_location(' Leaving: '||l_proc,230);
6315: raise;
6316: WHEN others THEN --Added for bug 7326591
6317: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Erroneous Assignment ID ' || l_assignment_id);
6318: hr_utility.set_location(' Exception at : '||l_proc,235);
6319: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception at : ' || l_proc);
6320: raise;
6321:
6322: END archive_code;
6342: invalid_archive_type EXCEPTION;
6343: --
6344: BEGIN
6345: --
6346: hr_utility.trace('Extract Item: '||p_user_entity_name);
6347: -- Retrieve user entity ID, also validates the entity.
6348: --
6349: OPEN csr_get_user_entity_id(p_user_entity_name);
6350: FETCH csr_get_user_entity_id INTO l_user_entity_id;
6422: END;
6423: --
6424: EXCEPTION
6425: WHEN invalid_archive_type THEN
6426: hr_utility.set_message(800, 'FF_34958_INVALID_ARCHIVE_TYPE');
6427: hr_utility.raise_error;
6428: --
6429: END extract_item_report_format;
6430: --
6423: --
6424: EXCEPTION
6425: WHEN invalid_archive_type THEN
6426: hr_utility.set_message(800, 'FF_34958_INVALID_ARCHIVE_TYPE');
6427: hr_utility.raise_error;
6428: --
6429: END extract_item_report_format;
6430: --
6431: END pay_gb_eoy_archive;