[Home] [Help]
493: --
494: l_proc varchar2(72) := g_package||'maintain_tax_percentage';
495: l_element_type_id pay_element_types_f.element_type_id%TYPE := null;
496: t_element_entry_id pay_element_types_f.element_type_id%TYPE := null;
497: l_input_value_id_tbl hr_entry.number_table;
498: l_new_vertex_value_tbl hr_entry.varchar2_table;
499: l_element_rec_tbl l_element_rec;
500: l_asg_tbl location_tbl;
501: l_location_chg_tbl location_tbl;
494: l_proc varchar2(72) := g_package||'maintain_tax_percentage';
495: l_element_type_id pay_element_types_f.element_type_id%TYPE := null;
496: t_element_entry_id pay_element_types_f.element_type_id%TYPE := null;
497: l_input_value_id_tbl hr_entry.number_table;
498: l_new_vertex_value_tbl hr_entry.varchar2_table;
499: l_element_rec_tbl l_element_rec;
500: l_asg_tbl location_tbl;
501: l_location_chg_tbl location_tbl;
502: l_state_code pay_us_emp_city_tax_rules_f.state_code%TYPE;
539: p_assignment_id number,
540: p_jurisdiction varchar2,
541: p_effective_date date,
542: p_element_link_id number,
543: p_input_value_id_tbl hr_entry.number_table
544: )
545: return number is
546: l_element_entry_id number;
547: l_cp varchar2(80);
665: p_assignment_id number,
666: p_jurisdiction varchar2,
667: p_percentage number,
668: p_element_link_id number,
669: p_input_value_id_tbl hr_entry.number_table,
670: p_effective_date date
671: )
672: is
673: l_proc varchar2(72)
671: )
672: is
673: l_proc varchar2(72)
674: := g_package||'set_and_correct_jurisdiction';
675: l_new_vertex_value_tbl hr_entry.varchar2_table;
676: l_element_entry_id number;
677: begin
678: hr_utility.set_location('Entering:'||l_proc, 10);
679: open csr_ele_entry
690: l_new_vertex_value_tbl(2) := p_jurisdiction;
691: if p_percentage is not null then
692: hr_utility.set_location(l_proc, 20);
693: l_new_vertex_value_tbl(3) := fnd_number.canonical_to_number(p_percentage);
694: hr_entry_api.update_element_entry
695: (
696: p_dt_update_mode => 'CORRECTION',
697: p_session_date => p_effective_date,
698: p_element_entry_id => l_element_entry_id,
724: (
725: p_jurisdiction varchar2,
726: p_assignment_id number,
727: p_element_link_id number,
728: p_input_value_id_tbl hr_entry.number_table,
729: p_effective_date date
730: )
731: return number is
732: l_state varchar2(10);
775: p_state_code varchar2,
776: p_county_code varchar2,
777: p_city_code varchar2,
778: p_element_link_id number,
779: p_input_value_id_tbl hr_entry.number_table,
780: p_effective_date date
781: )
782: is
783: l_proc varchar2(72) := g_package||'calculate_db_percentage';
1035: if p_datetrack_mode <> 'ZAP' then
1036: --
1037: -- Get element link id
1038: --
1039: l_element_link_id := hr_entry_api.get_link
1040: (
1041: p_assignment_id => p_assignment_id,
1042: p_element_type_id => l_element_type_id,
1043: p_session_date => l_effective_start_date
1239: -- end if; -- if l_payroll_installed
1240: --
1241: -- Select the corresponding datetrack mode
1242: --
1243: -- a. call hr_entry_api.update_element_entry with a datetrack_mode
1244: -- of 'UPDATE' if the effective end date of the current record
1245: -- is the end of time.
1246: -- b. if the effective end date of the current record is not the
1247: -- end of time then call hr_entry_api.update_element_entry with
1243: -- a. call hr_entry_api.update_element_entry with a datetrack_mode
1244: -- of 'UPDATE' if the effective end date of the current record
1245: -- is the end of time.
1246: -- b. if the effective end date of the current record is not the
1247: -- end of time then call hr_entry_api.update_element_entry with
1248: -- a mode of 'UPDATE_INSERT'.
1249: --
1250: If p_datetrack_mode = 'INSERT_OLD' then
1251: --
1283: or not l_element_rec_tbl.exists(l_pct_inc + 1) then
1284: --
1285: hr_utility.set_location(l_proc, 75);
1286: --
1287: hr_entry_api.update_element_entry
1288: (
1289: p_dt_update_mode => 'UPDATE',
1290: p_session_date => p_effective_date,
1291: p_element_entry_id => l_element_entry_id,
1296: else
1297: --
1298: hr_utility.set_location(l_proc, 80);
1299: --
1300: hr_entry_api.update_element_entry
1301: (
1302: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1303: p_session_date => p_effective_date,
1304: p_element_entry_id => l_element_entry_id,
1310: --
1311: -- a. get the current jurisdiction's percentage record.
1312: -- b. if the current percentage record is the last record with an
1313: -- effective end date of the end of time then call
1314: -- hr_entry_api.update_element_entry with a mode of 'UPDATE'.
1315: -- c. else if call hr_entry_api.update_element_entry with a mode
1316: -- of 'UPDATE_INSERT'.
1317: -- d. remove any percentage records which are before the new effective
1318: -- date from pay_element_entry_values_f and pay_element_entries_f.
1311: -- a. get the current jurisdiction's percentage record.
1312: -- b. if the current percentage record is the last record with an
1313: -- effective end date of the end of time then call
1314: -- hr_entry_api.update_element_entry with a mode of 'UPDATE'.
1315: -- c. else if call hr_entry_api.update_element_entry with a mode
1316: -- of 'UPDATE_INSERT'.
1317: -- d. remove any percentage records which are before the new effective
1318: -- date from pay_element_entry_values_f and pay_element_entries_f.
1319: -- e. if the new effective date is before the old defaulting date then
1338: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1339: --
1340: hr_utility.set_location(l_proc, 90);
1341: --
1342: hr_entry_api.update_element_entry
1343: (
1344: p_dt_update_mode => 'UPDATE',
1345: p_session_date => p_effective_date,
1346: p_element_entry_id => l_element_entry_id,
1350: else
1351: --
1352: hr_utility.set_location(l_proc, 95);
1353: --
1354: hr_entry_api.update_element_entry
1355: (
1356: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1357: p_session_date => p_effective_date,
1358: p_element_entry_id => l_element_entry_id,
1414: /*
1415: a. create a percentage record for the new jurisdiction at
1416: the defaulting date.
1417: b. apply all location changes to the new percentage record, by
1418: calling hr_entry_element.update_element_entry with MODE
1419: = 'UPDATE' at the start date for each location change.
1420: c. correct the percentage for the current jurisdiction record to
1421: 0% for assignment jurisdiction not equal to the current
1422: jurisdiction and 100% where the assignment jurisdiction and
1435: l_new_vertex_value_tbl(3) := '0';
1436: --
1437: hr_utility.set_location(l_proc ,115);
1438: --
1439: hr_entry_api.insert_element_entry
1440: (
1441: p_effective_start_date => l_defaulting_date,
1442: p_effective_end_date => l_effective_end_date,
1443: p_element_entry_id => l_element_entry_id,
1453: hr_utility.set_location(l_proc ,120);
1454: --
1455: -- Find all location changes in the assignment record.
1456: -- Apply all location changes to the new percentage record, by
1457: -- calling hr_entry_element.update_element_entry with MODE = 'UPDATE'.
1458: --
1459: if l_location_chg_tbl.first is not null then
1460: --
1461: hr_utility.set_location(l_proc ,125);
1467: -- The percentage is assigned 0% for location not equal to
1468: -- current jurisdiction and 100% where the jurisdiction and location
1469: -- are equal.
1470: --
1471: hr_entry_api.update_element_entry
1472: (
1473: p_dt_update_mode => 'UPDATE',
1474: p_session_date => l_location_chg_tbl(l_inc).start_date,
1475: p_element_entry_id => l_element_entry_id,
1500: end if; --
1501: --
1502: hr_utility.set_location(l_proc ,130);
1503: --
1504: hr_entry_api.update_element_entry
1505: (
1506: p_dt_update_mode => 'CORRECTION',
1507: p_session_date => p_effective_date,
1508: p_element_entry_id => l_element_entry_id,
1524: c. if the end date of the current assignment location record is less
1525: than the effective end date for the current percentage record and
1526: the percentage record's effective end date = the end of time or the
1527: current percentage record is the last record, then call
1528: hr_entry_api.update_element_entry with a mode of 'UPDATE' and
1529: a session date = the curr ent assignments effective end date + 1.
1530: d. else if the current assignment location record's effective end
1531: date < the currect assignments' element entry record's effective end
1532: date, then call hr_entry_api.update_element_entry with a mode of
1528: hr_entry_api.update_element_entry with a mode of 'UPDATE' and
1529: a session date = the curr ent assignments effective end date + 1.
1530: d. else if the current assignment location record's effective end
1531: date < the currect assignments' element entry record's effective end
1532: date, then call hr_entry_api.update_element_entry with a mode of
1533: 'UPDATE_INSERT' and a session date = the current assignments
1534: effective end date + 1.
1535: e. set the percentage value to the new percentage.
1536: f. if the current percentage record's effective start date < the
1534: effective end date + 1.
1535: e. set the percentage value to the new percentage.
1536: f. if the current percentage record's effective start date < the
1537: current assignment location record's effective start date then call
1538: hr_entry_api.update_element_entry with a mode of 'UPDATE_CHANGE_INSERT'
1539: and at the assignments effective start date + 1.
1540: g. else if not modified then find the current assignment's defaulting
1541: date.
1542: h. check to see if the value of the assignment's location for the next
1540: g. else if not modified then find the current assignment's defaulting
1541: date.
1542: h. check to see if the value of the assignment's location for the next
1543: record. if it is the same as the current assignment record's
1544: location, then call hr_entry_api.delete_element_entry with a mode of
1545: 'DELETE_NEXT_CHANGE' at the effective date.
1546: g. check the value of the location for the assignment's prior record, if
1547: it is the same as the location for the assignment's current record,
1548: call hr_entry_api.delete_element_entry with a mode of
1544: location, then call hr_entry_api.delete_element_entry with a mode of
1545: 'DELETE_NEXT_CHANGE' at the effective date.
1546: g. check the value of the location for the assignment's prior record, if
1547: it is the same as the location for the assignment's current record,
1548: call hr_entry_api.delete_element_entry with a mode of
1549: 'DELETE_NEXT_CHANGE' at the effective end date of the assignment's
1550: next record.
1551: h. if this is a call from the public api correct percentage, calculate
1552: the jurisdiction's new element entry value.
1549: 'DELETE_NEXT_CHANGE' at the effective end date of the assignment's
1550: next record.
1551: h. if this is a call from the public api correct percentage, calculate
1552: the jurisdiction's new element entry value.
1553: i. else call hr_entry_api.update_element_entry with a mode of 'CORRECTION'
1554: at the effective date.
1555: */
1556: elsif p_datetrack_mode = 'CORRECTION' then
1557: --
1594: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1595: --
1596: hr_utility.set_location(l_proc ,145);
1597: --
1598: hr_entry_api.update_element_entry
1599: (
1600: p_dt_update_mode => 'UPDATE',
1601: p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
1602: p_element_entry_id => l_element_entry_id,
1608: l_element_rec_tbl(l_pct_inc).effective_end_date then
1609: --
1610: hr_utility.set_location(l_proc ,150);
1611: --
1612: hr_entry_api.update_element_entry
1613: (
1614: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1615: p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
1616: p_element_entry_id => l_element_entry_id,
1628: --
1629: --
1630: hr_utility.set_location(l_proc ,155);
1631: --
1632: hr_entry_api.update_element_entry
1633: (
1634: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1635: p_session_date => l_location_chg_tbl(l_asg_inc).start_date,
1636: p_element_entry_id => l_element_entry_id,
1654: ) = TRUE and p_calculate_pct = FALSE then
1655: --
1656: hr_utility.set_location(l_proc ,160);
1657: --
1658: hr_entry_api.delete_element_entry
1659: (
1660: p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
1661: p_session_date => p_effective_date,
1662: p_element_entry_id => l_element_entry_id
1670: ) = TRUE and p_calculate_pct = FALSE then
1671: --
1672: hr_utility.set_location(l_proc ,165);
1673: --
1674: hr_entry_api.delete_element_entry
1675: (
1676: p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
1677: p_session_date => l_element_rec_tbl(l_pct_inc).effective_start_date - 1,
1678: p_element_entry_id => l_element_entry_id
1696: else
1697: --
1698: hr_utility.set_location(l_proc ,175);
1699: --
1700: hr_entry_api.update_element_entry
1701: (
1702: p_dt_update_mode => 'CORRECTION',
1703: p_session_date => p_effective_date,
1704: p_element_entry_id => l_element_entry_id,
1713: /*
1714: a. if the current assignment location record's effective end date <
1715: current percentage record's effective end date and current percentage
1716: record's effective end date = hr_api.g_eot or the next percentage
1717: record doesn't exist then call hr_entry_api.update_element_entry with
1718: a mode of 'UPDATE' at the the assignment record's effective end
1719: date + 1.
1720: b. else if the current assignment location record's effective end date
1721: < the current percentage record's effective end date then call
1718: a mode of 'UPDATE' at the the assignment record's effective end
1719: date + 1.
1720: b. else if the current assignment location record's effective end date
1721: < the current percentage record's effective end date then call
1722: hr_entry_api.update_element_entry with a mode of 'UPDATE_CHANGE_INSERT'
1723: at the next assignment record's effective end date.
1724: c. call hr_entry_api.update_element_entry with a mode of
1725: 'UPDATE_CHANGE_INSERT' at the effective date and with the new percentage
1726: value.
1720: b. else if the current assignment location record's effective end date
1721: < the current percentage record's effective end date then call
1722: hr_entry_api.update_element_entry with a mode of 'UPDATE_CHANGE_INSERT'
1723: at the next assignment record's effective end date.
1724: c. call hr_entry_api.update_element_entry with a mode of
1725: 'UPDATE_CHANGE_INSERT' at the effective date and with the new percentage
1726: value.
1727: d. if the assignment's next record's location is the same as the current
1728: assignment record's location then call hr_entry_api.delete_element_entry
1724: c. call hr_entry_api.update_element_entry with a mode of
1725: 'UPDATE_CHANGE_INSERT' at the effective date and with the new percentage
1726: value.
1727: d. if the assignment's next record's location is the same as the current
1728: assignment record's location then call hr_entry_api.delete_element_entry
1729: with a mode of 'DELETE_NEXT_CHANGE' at the effective date.
1730: */
1731: elsif p_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
1732: --
1771: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1772: --
1773: hr_utility.set_location(l_proc ,185);
1774: --
1775: hr_entry_api.update_element_entry
1776: (
1777: p_dt_update_mode => 'UPDATE',
1778: p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
1779: p_element_entry_id => l_element_entry_id,
1785: l_element_rec_tbl(l_pct_inc).effective_end_date then
1786: --
1787: hr_utility.set_location(l_proc ,190);
1788: --
1789: hr_entry_api.update_element_entry
1790: (
1791: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1792: p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
1793: p_element_entry_id => l_element_entry_id,
1796: p_entry_value_tbl => l_new_vertex_value_tbl
1797: );
1798: end if;
1799: --
1800: -- Call hr_entry_api.update_element_entry with MODE =
1801: -- 'UPDATE_CHANGE_INSERT' as of the effective date.
1802: --
1803: l_new_vertex_value_tbl(3) := fnd_number.canonical_to_number(p_percentage);
1804: --
1803: l_new_vertex_value_tbl(3) := fnd_number.canonical_to_number(p_percentage);
1804: --
1805: hr_utility.set_location(l_proc ,195);
1806: --
1807: hr_entry_api.update_element_entry
1808: (
1809: p_dt_update_mode => p_datetrack_mode,
1810: p_session_date => p_effective_date,
1811: p_element_entry_id => l_element_entry_id,
1821: ) then
1822: --
1823: hr_utility.set_location(l_proc ,200);
1824: --
1825: hr_entry_api.delete_element_entry
1826: (
1827: p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
1828: p_session_date => p_effective_date,
1829: p_element_entry_id => l_element_entry_id
1829: p_element_entry_id => l_element_entry_id
1830: );
1831: end if;
1832: /*
1833: a. call hr_entry_api.update_element_entry with a mode of p_datetrack_mode
1834: at the effective date.
1835: */
1836: elsif p_datetrack_mode in ('UPDATE_OVERRIDE', 'UPDATE') then
1837: hr_utility.set_location(l_proc ,205);
1835: */
1836: elsif p_datetrack_mode in ('UPDATE_OVERRIDE', 'UPDATE') then
1837: hr_utility.set_location(l_proc ,205);
1838: --
1839: hr_entry_api.update_element_entry
1840: (
1841: p_dt_update_mode => p_datetrack_mode,
1842: p_session_date => p_effective_date,
1843: p_element_entry_id => l_element_entry_id,
1854: elsif p_datetrack_mode in ('ZAP', 'DELETE', 'FUTURE_CHANGE') then
1855: --
1856: hr_utility.set_location(l_proc ,210);
1857: --
1858: hr_entry_api.delete_element_entry
1859: (
1860: p_dt_delete_mode => p_datetrack_mode,
1861: p_session_date => p_effective_date,
1862: p_element_entry_id => l_element_entry_id
1927: or not l_element_rec_tbl.exists(l_pct_inc + 1)) then
1928: --
1929: hr_utility.set_location(l_proc ,225);
1930: --
1931: hr_entry_api.update_element_entry
1932: (
1933: p_dt_update_mode => 'UPDATE',
1934: p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
1935: p_element_entry_id => l_element_entry_id,
1941: l_element_rec_tbl(l_pct_inc).effective_start_date then
1942: --
1943: hr_utility.set_location(l_proc ,230);
1944: --
1945: hr_entry_api.update_element_entry
1946: (
1947: p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
1948: p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
1949: p_element_entry_id => l_element_entry_id,
1954: end if;
1955: end if;
1956: end if;
1957: --
1958: -- Call hr_entry_api.delete_element_entry with MODE =
1959: -- 'DELETE_NEXT_CHANGE' for any rows found.
1960: --
1961: hr_utility.set_location(l_proc ,235);
1962: --
1959: -- 'DELETE_NEXT_CHANGE' for any rows found.
1960: --
1961: hr_utility.set_location(l_proc ,235);
1962: --
1963: hr_entry_api.delete_element_entry
1964: (
1965: p_dt_delete_mode => p_datetrack_mode,
1966: p_session_date => p_effective_date,
1967: p_element_entry_id => l_element_entry_id
2018: l_temp_char varchar2(2);
2019: lc_workers_comp varchar2(25) := 'Workers Compensation';
2020: l_tmp_date date;
2021:
2022: l_inp_value_id_table hr_entry.number_table;
2023: l_scr_value_table hr_entry.varchar2_table;
2024:
2025: /* Cursor to get details about the federal tax rule */
2026:
2019: lc_workers_comp varchar2(25) := 'Workers Compensation';
2020: l_tmp_date date;
2021:
2022: l_inp_value_id_table hr_entry.number_table;
2023: l_scr_value_table hr_entry.varchar2_table;
2024:
2025: /* Cursor to get details about the federal tax rule */
2026:
2027: cursor csr_fed_detail is
2178: open csr_get_ele_entry_id(l_assignment_id
2179: ,l_tmp_date);
2180: fetch csr_get_ele_entry_id into l_element_entry_id;
2181: if csr_get_ele_entry_id%found then
2182: hr_entry_api.delete_element_entry(
2183: p_dt_delete_mode => p_datetrack_mode,
2184: p_session_date => l_effective_date,
2185: p_element_entry_id => l_element_entry_id);
2186: end if;
2220: end loop;
2221: hr_utility.set_location('Entering:'|| l_proc, 30);
2222:
2223: /* Get element link */
2224: l_element_link_id := hr_entry_api.get_link(
2225: P_assignment_id => l_assignment_id,
2226: P_element_type_id => l_element_type_id,
2227: P_session_date => l_get_old_value_date);
2228:
2245: if p_datetrack_mode = 'INSERT' then
2246: /* Insert the worker's compensation element entry */
2247:
2248: hr_utility.set_location('Entering:'|| l_proc, 50);
2249: hr_entry_api.insert_element_entry(
2250: P_effective_start_date => l_effective_start_date,
2251: P_effective_end_date => l_effective_end_date,
2252: P_element_entry_id => l_element_entry_id,
2253: P_assignment_id => l_assignment_id,
2288: /* All of the tax %age records will be created from the date on which the
2289: default tax rules criteria was met till the end of time. So, we should
2290: get records for the state, county and city for the same effective start
2291: date */
2292: hr_entry_api.delete_element_entry(
2293: p_dt_delete_mode => l_mode,
2294: p_session_date => l_effective_date,
2295: p_element_entry_id => l_element_entry_id);
2296:
2297:
2298: elsif p_datetrack_mode in ('CORRECTION','UPDATE', 'UPDATE_CHANGE_INSERT',
2299: 'UPDATE_OVERRIDE') then
2300:
2301: hr_entry_api.update_element_entry(
2302: p_dt_update_mode => l_mode,
2303: p_session_date => l_effective_date,
2304: p_element_entry_id => l_element_entry_id,
2305: p_num_entry_values => 2,
2334: l_mode := 'UPDATE_CHANGE_INSERT';
2335: end if;
2336: close csr_fed_rule_exists;
2337:
2338: hr_entry_api.update_element_entry(
2339: p_dt_update_mode => l_mode,
2340: p_session_date => l_effective_date,
2341: p_element_entry_id => l_element_entry_id,
2342: p_num_entry_values => 2,
2385: l_mode := 'UPDATE_CHANGE_INSERT';
2386: end if;
2387: close csr_fed_rule_exists;
2388:
2389: hr_entry_api.update_element_entry(
2390: p_dt_update_mode => l_mode,
2391: p_session_date => l_effective_date,
2392: p_element_entry_id => l_element_entry_id,
2393: p_num_entry_values => 2,