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