[Home] [Help]
96: min(end_date)
97: into
98: l_first_end_date
99: from
100: per_time_periods
101: where
102: payroll_id = l_payroll_id;
103:
104: -- Obtain the chronologically last end date in the Payroll
106: max(end_date)
107: into
108: l_last_end_date
109: from
110: per_time_periods
111: where
112: payroll_id = l_payroll_id;
113:
114: -- Determine the base period type and the multiple of the base
411: cursor get_periods is
412: select
413: end_date
414: from
415: per_time_periods
416: where
417: payroll_id = p_payroll_id
418: and
419: end_date between p_first_end_date and p_last_end_date
455: -- Update all the periods between l_store_end_date and the previous
456: -- period, so that their Payroll Month End is equal to the Period End Date
457: -- of the previous period
458: update
459: per_time_periods
460: set
461: pay_advice_date = l_previous_end_date
462: where
463: payroll_id = p_payroll_id
498:
499: -- Update all the periods between l_store_end_date and the last period,
500: -- so that their Payroll Month End is equal to l_old_end_date
501: update
502: per_time_periods
503: set
504: pay_advice_date = l_old_end_date
505: where
506: payroll_id = p_payroll_id
863: cursor get_periods is
864: select
865: end_date, pay_advice_date
866: from
867: per_time_periods
868: where
869: payroll_id = p_payroll_id
870: and
871: end_date between p_first_end_date and p_last_end_date
876: cursor get_previous_periods is
877: select
878: end_date, pay_advice_date
879: from
880: per_time_periods
881: where
882: payroll_id = p_payroll_id
883: and
884: end_date < p_first_end_date
899: start_date
900: into
901: l_start_date
902: from
903: per_time_periods
904: where
905: payroll_id = p_payroll_id
906: and
907: end_date = p_first_end_date;
945: max(end_date)
946: into
947: l_store_end_date
948: from
949: per_time_periods
950: where
951: payroll_id = p_payroll_id
952: and
953: pay_advice_date = l_old_pay_me;
984: max(end_date)
985: into
986: l_store_end_date
987: from
988: per_time_periods
989: where
990: payroll_id = p_payroll_id
991: and
992: pay_advice_date = l_pay_me;
1004: end if;
1005:
1006: -- Update the first periods to the first tax year
1007: update
1008: per_time_periods
1009: set
1010: prd_information1 = to_char(l_tax_year, 'YYYY'),
1011: prd_information_category = 'ZA'
1012: where
1084: -- period in that Payroll Month End
1085: if get_periods%notfound then
1086: -- Update the periods up to the last period end date
1087: update
1088: per_time_periods
1089: set
1090: prd_information1 = to_char(l_tax_year, 'YYYY'),
1091: prd_information_category = 'ZA'
1092: where
1105:
1106: if get_periods%notfound then
1107: -- Update the periods up to the last period end date
1108: update
1109: per_time_periods
1110: set
1111: prd_information1 = to_char(l_tax_year, 'YYYY'),
1112: prd_information_category = 'ZA'
1113: where
1116: end_date between l_old_end_date and p_last_end_date;
1117: else
1118: -- Update the periods up to the previous period
1119: update
1120: per_time_periods
1121: set
1122: prd_information1 = to_char(l_tax_year, 'YYYY'),
1123: prd_information_category = 'ZA'
1124: where
1198: cursor get_periods is
1199: select
1200: end_date, pay_advice_date
1201: from
1202: per_time_periods
1203: where
1204: payroll_id = p_payroll_id
1205: and
1206: end_date between p_first_end_date and p_last_end_date
1221: start_date
1222: into
1223: l_start_date
1224: from
1225: per_time_periods
1226: where
1227: payroll_id = p_payroll_id
1228: and
1229: end_date = p_first_end_date;
1267: max(end_date)
1268: into
1269: l_store_end_date
1270: from
1271: per_time_periods
1272: where
1273: payroll_id = p_payroll_id
1274: and
1275: pay_advice_date = l_old_pay_me;
1304: max(end_date)
1305: into
1306: l_store_end_date
1307: from
1308: per_time_periods
1309: where
1310: payroll_id = p_payroll_id
1311: and
1312: pay_advice_date = l_pay_me;
1324: end if;
1325:
1326: -- Update the first periods to the first calendar year
1327: update
1328: per_time_periods
1329: set
1330: prd_information3 = to_char(l_cal_year, 'YYYY'),
1331: prd_information_category = 'ZA'
1332: where
1401: -- period in that Payroll Month End
1402: if get_periods%notfound then
1403: -- Update the periods up to the last period end date
1404: update
1405: per_time_periods
1406: set
1407: prd_information3 = to_char(l_cal_year, 'YYYY'),
1408: prd_information_category = 'ZA'
1409: where
1422:
1423: if get_periods%notfound then
1424: -- Update the periods up to the last period end date
1425: update
1426: per_time_periods
1427: set
1428: prd_information3 = to_char(l_cal_year, 'YYYY'),
1429: prd_information_category = 'ZA'
1430: where
1433: end_date between l_old_end_date and p_last_end_date;
1434: else
1435: -- Update the periods up to the previous period
1436: update
1437: per_time_periods
1438: set
1439: prd_information3 = to_char(l_cal_year, 'YYYY'),
1440: prd_information_category = 'ZA'
1441: where
1479: )
1480: is
1481:
1482: -- Temporary variable that holds the tax year of the first period
1483: l_tax_year per_time_periods.prd_information1%TYPE;
1484: -- Variable used to store the end date of the period
1485: l_old_end_date date;
1486: -- Variable used to hold the end date of the period
1487: l_end_date date;
1498: -- Function name used in trace package
1499: func_name CONSTANT varchar2(50) := 'za_pay_calendars.create_za_tax_quarters';
1500:
1501: -- The periods after the first tax year
1502: cursor get_periods(c_tax_year per_time_periods.prd_information1%TYPE) is
1503: select
1504: end_date, pay_advice_date
1505: from
1506: per_time_periods
1502: cursor get_periods(c_tax_year per_time_periods.prd_information1%TYPE) is
1503: select
1504: end_date, pay_advice_date
1505: from
1506: per_time_periods
1507: where
1508: payroll_id = p_payroll_id
1509: and
1510: end_date between p_first_end_date and p_last_end_date
1513: order by
1514: end_date;
1515:
1516: -- The periods in the first tax year, in reverse order
1517: cursor get_periods2(c_tax_year per_time_periods.prd_information1%TYPE) is
1518: select
1519: end_date, pay_advice_date
1520: from
1521: per_time_periods
1517: cursor get_periods2(c_tax_year per_time_periods.prd_information1%TYPE) is
1518: select
1519: end_date, pay_advice_date
1520: from
1521: per_time_periods
1522: where
1523: payroll_id = p_payroll_id
1524: and
1525: prd_information1 = c_tax_year
1535: prd_information1
1536: into
1537: l_tax_year
1538: from
1539: per_time_periods
1540: where
1541: payroll_id = p_payroll_id
1542: and
1543: end_date = p_first_end_date;
1593: end if;
1594:
1595: -- Update the periods with the quarter
1596: update
1597: per_time_periods
1598: set
1599: prd_information2 = to_char(l_quarter),
1600: prd_information_category = 'ZA'
1601: where
1620: end if;
1621:
1622: -- Update the periods with the quarter
1623: update
1624: per_time_periods
1625: set
1626: prd_information2 = to_char(l_quarter),
1627: prd_information_category = 'ZA'
1628: where
1675: end if;
1676:
1677: -- Update the periods with the quarter
1678: update
1679: per_time_periods
1680: set
1681: prd_information2 = to_char(l_quarter),
1682: prd_information_category = 'ZA'
1683: where
1702: end if;
1703:
1704: -- Update the periods with the quarter
1705: update
1706: per_time_periods
1707: set
1708: prd_information2 = to_char(l_quarter),
1709: prd_information_category = 'ZA'
1710: where
1722: -- This procedure creates the period numbers for the ZA calendars. This procedure
1723: -- should be called every time a change is made to the Payroll Tax Year field on the
1724: -- Additional Period Information Flexfield. It uses the following steps:
1725: -- 1. Store the first period number of the first tax year in l_period, by selecting
1726: -- it from per_time_periods.
1727: -- 2. Loop through the tax years.
1728: -- 3. Loop through the periods in the current tax year.
1729: -- 4. Update the period number of the current period to l_period.
1730: -- 5. Increment l_period.
1746: )
1747: is
1748:
1749: -- Variable used to hold the current tax year
1750: l_tax_year per_time_periods.prd_information1%TYPE;
1751: -- The rowid of the period to be updated
1752: l_rowid rowid;
1753: -- The current period's number
1754: l_period number;
1761: cursor tax_years is
1762: select distinct
1763: prd_information1
1764: from
1765: per_time_periods
1766: where
1767: payroll_id = p_payroll_id
1768: and
1769: end_date between p_first_end_date and p_last_end_date
1770: order by
1771: prd_information1;
1772:
1773: -- The periods in the current tax year.
1774: cursor get_periods(c_tax_year per_time_periods.prd_information1%TYPE) is
1775: select
1776: rowid, end_date
1777: from
1778: per_time_periods
1774: cursor get_periods(c_tax_year per_time_periods.prd_information1%TYPE) is
1775: select
1776: rowid, end_date
1777: from
1778: per_time_periods
1779: where
1780: payroll_id = p_payroll_id
1781: and
1782: end_date between p_first_end_date and p_last_end_date
1801: period_num
1802: into
1803: l_period
1804: from
1805: per_time_periods
1806: where
1807: payroll_id = p_payroll_id
1808: and
1809: end_date = p_first_end_date;
1827: exit when get_periods%notfound;
1828:
1829: -- Update the period number of the current period
1830: update
1831: per_time_periods
1832: set
1833: period_num = l_period
1834: where
1835: rowid = l_rowid;
1835: rowid = l_rowid;
1836:
1837: -- Update the period name of the current period
1838: update
1839: per_time_periods
1840: set
1841: period_name = to_char(l_period) || ' ' || to_char(l_end_date, 'YYYY')
1842: || ' ' || p_proc_period_type
1843: where