[Home] [Help]
1: package body hrassact as
2: /* $Header: pyassact.pkb 120.30.12010000.4 2008/11/24 05:09:29 priupadh ship $ */
3: /*
4: Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5: --
4: Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5: --
6: /*
7: --
8: Name : hrassact
9: Author : $Author: priupadh $
10: Synopsis : Payroll and Assignment action processing.
11: Contents : applied_interlocks
12: bal_adjust
672: --
673: -- Validate the rollback and mark for retry rules for
674: -- assignment actions.
675: if g_debug then
676: hr_utility.set_location('hrassact.val_assact_rr_rules', 10);
677: end if;
678: if p_rollback_mode = 'RETRY' then
679: if p_pact_rec.action_type in ('V', 'B', 'Z', 'E', 'M', 'H', 'T') then
680: return false;
774: begin
775: g_debug := hr_utility.debug_enabled;
776: --
777: if g_debug then
778: hr_utility.set_location('hrassact.ensure_assact_rolled_back',10);
779: end if;
780: validate_rollback_mode(p_rollback_mode);
781: --
782: if (p_rollback_mode = 'BACKPAY') then
781: --
782: if (p_rollback_mode = 'BACKPAY') then
783: --
784: if g_debug then
785: hr_utility.set_location('hrassact.ensure_assact_rolled_back',20);
786: end if;
787: for c2rec in c2 loop
788: -- any record fetched is an error
789: hr_utility.set_message(801, 'HR_7001_ACTION_MUST_ROLL_FIRST');
793: --
794: else
795: --
796: if g_debug then
797: hr_utility.set_location('hrassact.ensure_assact_rolled_back',30);
798: end if;
799: for c1rec in c1 loop
800: -- any record fetched is an error
801: hr_utility.set_message(801, 'HR_7001_ACTION_MUST_ROLL_FIRST');
831: begin
832: g_debug := hr_utility.debug_enabled;
833: --
834: if g_debug then
835: hr_utility.set_location('hrassact.ensure_pact_rolled_back',10);
836: end if;
837: for c1rec in c1 loop
838: -- any record fetched is an error
839: hr_utility.set_message(801, 'HR_7007_ACTION_ROLL_ASSACTS');
870: -- Obtain information about this assignment action which we will
871: -- need later on.
872: -- Some of this is required for messages.
873: if g_debug then
874: hr_utility.set_location('hrassact.val_assact_rollback',10);
875: end if;
876: select ACT.assignment_id,
877: ACT.action_sequence,
878: ACT.action_status,
927: -- to see if it is locked by another action. Peform
928: -- slightly different checks for RETRY and ROLLBACK
929: -- modes. See comments below.
930: if g_debug then
931: hr_utility.set_location('hrassact.val_assact_rollback',20);
932: end if;
933: if(p_rollback_mode = 'RETRY') then
934: -- Case for RETRY mode.
935: -- Check that the assignment action we are attempting to
974: --
975: -- check for other actions on this ASSIGNMENT
976: -- Perform different checks for RETRY or ROLLBACK.
977: if g_debug then
978: hr_utility.set_location('hrassact.val_assact_rollback',30);
979: end if;
980: if(p_rollback_mode = 'RETRY') then
981: -- Case for RETRY mode.
982: -- Disallow mark for retry assignment action
1024: -- check for other actions on this PERSON
1025: -- As above, perform different checks for
1026: -- RETRY and ROLLBACK modes.
1027: if g_debug then
1028: hr_utility.set_location('hrassact.val_assact_rollback',40);
1029: end if;
1030: if(p_rollback_mode = 'RETRY') then
1031: -- Case for RETRY mode.
1032: select null into dummy
1076: --
1077: exception
1078: when no_data_found then
1079: if g_debug then
1080: hr_utility.set_location('hrassact.val_assact_rollback',50);
1081: end if;
1082: return FALSE;
1083: --
1084: end;
1083: --
1084: end;
1085: --
1086: if g_debug then
1087: hr_utility.set_location('hrassact.val_assact_rollback',60);
1088: end if;
1089: return TRUE;
1090: --
1091: end val_assact_rollback;
1107: validate_rollback_mode(p_rollback_mode);
1108: --
1109: -- get payroll action level information
1110: if g_debug then
1111: hr_utility.set_location('hrassact.val_pact_rollback', 10);
1112: end if;
1113: select pac.business_group_id,
1114: pac.effective_date,
1115: hrl.meaning,
1144: declare
1145: dummy number;
1146: begin
1147: if g_debug then
1148: hr_utility.set_location('hrassact.val_pact_rollback', 20);
1149: end if;
1150: select null
1151: into dummy
1152: from dual
1165: --
1166: --
1167: -- get some more info needed to roll back actions
1168: if g_debug then
1169: hr_utility.set_location('hrassact.val_pact_rollback', 30);
1170: end if;
1171: if upper( hr_leg_rule.get_independent_periods(l_business_group_id))
1172: like 'Y%' then
1173: p_pact_rec.independent_periods_flag := TRUE;
1182: begin
1183: p_pact_rec.sequenced_flag := TRUE;
1184: --
1185: if g_debug then
1186: hr_utility.set_location('hrassact.val_pact_rollback', 40);
1187: end if;
1188: select null
1189: into dummy
1190: from pay_action_classifications CLASS
1214: --
1215: -- We simply call the internal validate rollback procedure
1216: -- to give us the information we need.
1217: if g_debug then
1218: hr_utility.set_location('hrassact.validate_pact_rollback', 10);
1219: end if;
1220: l_pact_rec.pact_id := p_payroll_action_id;
1221: --
1222: val_pact_rollback(p_pact_rec => l_pact_rec,
1245: g_debug := hr_utility.debug_enabled;
1246: --
1247: -- Call the validate payroll action routine to get pact details.
1248: if g_debug then
1249: hr_utility.set_location('hrassact.validate_assact_rollback',10);
1250: end if;
1251: l_pact_rec.pact_id := p_payroll_action_id;
1252: val_pact_rollback(p_pact_rec => l_pact_rec,
1253: p_rollback_mode => p_rollback_mode);
1253: p_rollback_mode => p_rollback_mode);
1254: --
1255: -- Call the validate assignment action routine to validate assact.
1256: if g_debug then
1257: hr_utility.set_location('hrassact.validate_assact_rollback',20);
1258: end if;
1259: l_assact_rec.assact_id := p_assignment_action_id;
1260: --
1261: if val_assact_rollback(l_pact_rec, l_assact_rec, p_rollback_mode) and
1290: -- results were trashed in a previous step.
1291: begin
1292: -- We wish to remove the QuickPay inclusions.
1293: if g_debug then
1294: hr_utility.set_location('hrassact.trash_quickpay',10);
1295: end if;
1296: --
1297: -- Enhancement 3368211
1298: -- Delete from both PAY_QUICKPAY_INCLUSIONS and
1307: delete from pay_quickpay_inclusions
1308: where assignment_action_id = p_action_id;
1309: --
1310: if g_debug then
1311: hr_utility.set_location('hrassact.trash_quickpay',20);
1312: end if;
1313: for c1rec in c1 loop
1314: --
1315: -- delete any unprocessed run result attached to the entry.
1313: for c1rec in c1 loop
1314: --
1315: -- delete any unprocessed run result attached to the entry.
1316: if g_debug then
1317: hr_utility.set_location('hrassact.trash_quickpay',30);
1318: end if;
1319: delete from pay_run_result_values RRV
1320: where RRV.run_result_id in
1321: (select RR.run_result_id
1323: where RR.source_type = 'E'
1324: and RR.source_id = c1rec.element_entry_id);
1325: --
1326: if g_debug then
1327: hr_utility.set_location('hrassact.trash_quickpay',40);
1328: end if;
1329: delete from pay_run_results RR
1330: where RR.source_type = 'E'
1331: and RR.source_id = c1rec.element_entry_id;
1331: and RR.source_id = c1rec.element_entry_id;
1332: --
1333: -- delete any element entry values
1334: if g_debug then
1335: hr_utility.set_location('hrassact.trash_quickpay',50);
1336: end if;
1337: delete from pay_element_entry_values_f EEV
1338: where EEV.element_entry_id = c1rec.element_entry_id;
1339: --
1338: where EEV.element_entry_id = c1rec.element_entry_id;
1339: --
1340: -- delete the entry itself
1341: if g_debug then
1342: hr_utility.set_location('hrassact.trash_quickpay',60);
1343: end if;
1344: delete from pay_element_entries_f
1345: where current of c1;
1346: --
1347: end loop;
1348: end;
1349: --
1350: if g_debug then
1351: hr_utility.set_location('hrassact.trash_quickpay',60);
1352: end if;
1353: return;
1354: --
1355: end trash_quickpay;
1581: -- will have no effect for unsequenced actions such as
1582: -- Pre-Payments. For efficiency there should be no cascade
1583: -- trigger on Run Results so we need to trash the values as well.
1584: if g_debug then
1585: hr_utility.set_location('hrassact.do_assact_rollback',10);
1586: end if;
1587: delete from pay_run_result_values RRV
1588: where RRV.run_result_id in
1589: (select RR.run_result_id
1590: from pay_run_results RR
1591: where RR.assignment_action_id = p_assact_rec.assact_id);
1592: --
1593: if g_debug then
1594: hr_utility.set_location('hrassact.do_assact_rollback',20);
1595: end if;
1596: delete from pay_run_results RR
1597: where RR.assignment_action_id = p_assact_rec.assact_id;
1598: --
1600: -- performance requirements on Insert and Update, and also because
1601: -- they need to go for retry as well as rollback.
1602: -- Start with any balance context values.
1603: if g_debug then
1604: hr_utility.set_location('hrassact.do_assact_rollback',25);
1605: end if;
1606: delete from pay_balance_context_values VAL
1607: where exists (
1608: select null
1610: where PLB.assignment_action_id = p_assact_rec.assact_id
1611: and VAL.latest_balance_id = PLB.latest_balance_id);
1612: --
1613: if g_debug then
1614: hr_utility.set_location('hrassact.do_assact_rollback',30);
1615: end if;
1616: delete from pay_person_latest_balances PLB
1617: where PLB.assignment_action_id = p_assact_rec.assact_id;
1618: --
1616: delete from pay_person_latest_balances PLB
1617: where PLB.assignment_action_id = p_assact_rec.assact_id;
1618: --
1619: if g_debug then
1620: hr_utility.set_location('hrassact.do_assact_rollback',35);
1621: end if;
1622: delete from pay_balance_context_values VAL
1623: where exists (
1624: select null
1626: where ALB.assignment_action_id = p_assact_rec.assact_id
1627: and VAL.latest_balance_id = ALB.latest_balance_id);
1628: --
1629: if g_debug then
1630: hr_utility.set_location('hrassact.do_assact_rollback',40);
1631: end if;
1632: delete from pay_assignment_latest_balances ALB
1633: where ALB.assignment_action_id = p_assact_rec.assact_id;
1634: --
1644: -- Start by selecting the element_entry_id
1645: -- that we need to delete.
1646: -- We are joining effective dates only to hit the whole
1647: -- index, because there should only be the one row anyway.
1648: -- hr_utility.set_location('hrassact.do_assact_rollback',50);
1649: select pee.element_entry_id
1650: into v_eeid
1651: from pay_element_entries_f pee
1652: where pee.creator_id = p_assact_rec.assact_id
1654: and p_pact_rec.action_date between
1655: pee.effective_start_date and pee.effective_end_date;
1656: --
1657: -- Now, we attempt to delete the entry values.
1658: -- hr_utility.set_location('hrassact.do_assact_rollback',60);
1659: delete from pay_element_entry_values_f pev
1660: where pev.element_entry_id = v_eeid
1661: and p_pact_rec.action_date between
1662: pev.effective_start_date and pev.effective_end_date;
1665: -- Note, if this procedure is called from the balance
1666: -- adjustment row, the form may be attempting to delete
1667: -- this row. However, this could be called from the
1668: -- actions form, in which case we do need to do the delete.
1669: -- hr_utility.set_location('hrassact.do_assact_rollback',70);
1670: delete from pay_element_entries_f pee
1671: where pee.element_entry_id = v_eeid
1672: and p_pact_rec.action_date between
1673: pee.effective_start_date and pee.effective_end_date;
1710: -- to undo changes in their entirety, since we do not
1711: -- have all the information we need to hand, but we
1712: -- perform delete next change.
1713: if g_debug then
1714: hr_utility.set_location('hrassact.do_assact_rollback',80);
1715: end if;
1716: for c1rec in c1 loop
1717: -- First, look for possible stop rule case.
1718: if(c1rec.effective_end_date = p_pact_rec.action_date) then
1718: if(c1rec.effective_end_date = p_pact_rec.action_date) then
1719: -- We may have a stopped entry, but we need to
1720: -- see if this really is the case.
1721: if g_debug then
1722: hr_utility.set_location('hrassact.do_assact_rollback',90);
1723: end if;
1724: select max(pee.effective_end_date)
1725: into v_max_date
1726: from pay_element_entries_f pee
1750: then
1751: -- Note, in following select, use max to avoid
1752: -- having to deal with no data found error.
1753: if g_debug then
1754: hr_utility.set_location('hrassact.do_assact_rollback',110);
1755: end if;
1756: select max(pee.effective_end_date)
1757: into v_max_date
1758: from pay_element_entries_f pee
1771: -- the update was previously a correction. This
1772: -- means we need to set the updating_action_id
1773: -- to null.
1774: if g_debug then
1775: hr_utility.set_location('hrassact.do_assact_rollback',130);
1776: end if;
1777: update pay_element_entries_f pee
1778: set pee.updating_action_id = null
1779: where pee.element_entry_id = c1rec.element_entry_id
1789: --
1790: --
1791: -- delete any messages associated with the assignment action.
1792: if g_debug then
1793: hr_utility.set_location('hrassact.do_assact_rollback',140);
1794: end if;
1795: delete from pay_message_lines ML
1796: where ML.source_type = 'A'
1797: and ML.source_id = p_assact_rec.assact_id;
1803: -- in retry mode.
1804: -- Note, the delete of a pre-payment row causes
1805: -- a cascade delete from pay_coin_anal_elements.
1806: if g_debug then
1807: hr_utility.set_location('hrassact.do_assact_rollback',150);
1808: end if;
1809: delete from pay_pre_payments ppp
1810: where ppp.assignment_action_id = p_assact_rec.assact_id;
1811: end if;
1838: --
1839: -- now write our message to the payroll_action level. We
1840: -- want to spit out the asssignment's current Payroll ID as well.
1841: if g_debug then
1842: hr_utility.set_location('hrassact.do_assact_rollback',160);
1843: end if;
1844: insert into pay_message_lines
1845: (line_sequence,
1846: payroll_id,
1869: -- if we were being called from a form)
1870: if not p_leave_base_table_row then
1871: if p_rollback_mode = 'RETRY' then
1872: if g_debug then
1873: hr_utility.set_location('hrassact.do_assact_rollback',170);
1874: end if;
1875: update pay_assignment_actions
1876: set action_status = 'M'
1877: where assignment_action_id = p_assact_rec.assact_id;
1877: where assignment_action_id = p_assact_rec.assact_id;
1878: --
1879: elsif p_rollback_mode = 'BACKPAY' then
1880: if g_debug then
1881: hr_utility.set_location('hrassact.do_assact_rollback',180);
1882: end if;
1883: update pay_assignment_actions
1884: set action_status = 'B'
1885: where assignment_action_id = p_assact_rec.assact_id;
1887: elsif p_rollback_mode = 'ROLLBACK' then
1888: -- there may be pay_action_interlock rows.
1889: -- which are locking other assignment actions.
1890: if g_debug then
1891: hr_utility.set_location('hrassact.do_assact_rollback',190);
1892: end if;
1893: delete from pay_action_interlocks lck
1894: where lck.locking_action_id = p_assact_rec.assact_id;
1895: --
1893: delete from pay_action_interlocks lck
1894: where lck.locking_action_id = p_assact_rec.assact_id;
1895: --
1896: if g_debug then
1897: hr_utility.set_location('hrassact.do_assact_rollback',200);
1898: end if;
1899: delete from pay_assignment_actions
1900: where assignment_action_id = p_assact_rec.assact_id;
1901: end if;
1904: -- still need to delete interlock rows. Of course,
1905: -- in this case we do not delete the action.
1906: if(p_rollback_mode = 'ROLLBACK') then
1907: if g_debug then
1908: hr_utility.set_location('hrassact.do_assact_rollback',210);
1909: end if;
1910: delete from pay_action_interlocks lck
1911: where lck.locking_action_id = p_assact_rec.assact_id;
1912: end if;
1912: end if;
1913: end if;
1914: --
1915: if g_debug then
1916: hr_utility.set_location('hrassact.do_assact_rollback',220);
1917: end if;
1918: return;
1919: --
1920: end do_assact_rollback;
2208: begin
2209: g_debug := hr_utility.debug_enabled;
2210: --
2211: if g_debug then
2212: hr_utility.set_location('hrassact.rollback_payroll_action',10);
2213: end if;
2214: py_rollback_pkg.rollback_payroll_action
2215: (p_payroll_action_id => p_payroll_action_id,
2216: p_rollback_mode => p_rollback_mode,
2226: begin
2227: g_debug := hr_utility.debug_enabled;
2228: --
2229: if g_debug then
2230: hr_utility.set_location('hrassact.rollback_payroll_action',20);
2231: end if;
2232: py_rollback_pkg.rollback_payroll_action
2233: (p_payroll_action_id => p_payroll_action_id,
2234: p_rollback_mode => p_rollback_mode,
2245: begin
2246: g_debug := hr_utility.debug_enabled;
2247: --
2248: if g_debug then
2249: hr_utility.set_location('hrassact.rollback_payroll_action',30);
2250: end if;
2251: py_rollback_pkg.rollback_payroll_action
2252: (p_payroll_action_id => p_payroll_action_id,
2253: p_all_or_nothing => FALSE,
2280: begin
2281: g_debug := hr_utility.debug_enabled;
2282: --
2283: if g_debug then
2284: hr_utility.set_location('hrassact.rollback_ass_action',20);
2285: end if;
2286: for c1rec in c1 loop
2287: --
2288: -- populate payroll action details and perform high level validation
2287: --
2288: -- populate payroll action details and perform high level validation
2289: l_pact_rec.pact_id := c1rec.payroll_action_id;
2290: if g_debug then
2291: hr_utility.set_location('hrassact.rollback_ass_action',30);
2292: end if;
2293: val_pact_rollback(p_pact_rec => l_pact_rec,
2294: p_rollback_mode => p_rollback_mode);
2295: --
2296: -- see if OK to roll back or retry this assignment action
2297: l_assact_rec.assact_id := p_assignment_action_id;
2298: --
2299: if g_debug then
2300: hr_utility.set_location('hrassact.rollback_ass_action',40);
2301: end if;
2302: if p_rollback_mode = 'BACKPAY' or
2303: (val_assact_rollback (l_pact_rec, l_assact_rec, p_rollback_mode) and
2304: val_assact_rr_rules (l_pact_rec, p_rollback_mode))
2305: then
2306: --
2307: -- OK, clean up all child records for the action
2308: if g_debug then
2309: hr_utility.set_location('hrassact.rollback_ass_action',50);
2310: end if;
2311: do_assact_rollback
2312: (p_pact_rec => l_pact_rec,
2313: p_assact_rec => l_assact_rec,
2321: --
2322: end loop;
2323: --
2324: if g_debug then
2325: hr_utility.set_location('hrassact.rollback_ass_action',60);
2326: end if;
2327: return;
2328: --
2329: end rollback_ass_action;
2475: begin
2476: g_debug := hr_utility.debug_enabled;
2477: --
2478: if g_debug then
2479: hr_utility.set_location('hrassact.trash_latest_balances',10);
2480: end if;
2481:
2482: if (g_lat_bal_check_mode is null) then
2483: begin
2481:
2482: if (g_lat_bal_check_mode is null) then
2483: begin
2484: if g_debug then
2485: hr_utility.set_location('hrassact.trash_latest_balances',15);
2486: end if;
2487: select parameter_value
2488: into g_lat_bal_check_mode
2489: from pay_action_parameters
2494: g_lat_bal_check_mode := 'N';
2495: end;
2496:
2497: if (g_lat_bal_check_mode = 'B') then
2498: HRASSACT.CHECK_LAT_BALS_FIRST := TRUE;
2499: elsif (g_lat_bal_check_mode = 'R') then
2500: HRASSACT.CHECK_RRVS_FIRST := TRUE;
2501: end if;
2502: end if;
2496:
2497: if (g_lat_bal_check_mode = 'B') then
2498: HRASSACT.CHECK_LAT_BALS_FIRST := TRUE;
2499: elsif (g_lat_bal_check_mode = 'R') then
2500: HRASSACT.CHECK_RRVS_FIRST := TRUE;
2501: end if;
2502: end if;
2503: --
2504: if HRASSACT.CHECK_LATEST_BALANCES = TRUE then
2500: HRASSACT.CHECK_RRVS_FIRST := TRUE;
2501: end if;
2502: end if;
2503: --
2504: if HRASSACT.CHECK_LATEST_BALANCES = TRUE then
2505:
2506: if HRASSACT.CHECK_RRVS_FIRST = TRUE then
2507:
2508: if g_debug then
2502: end if;
2503: --
2504: if HRASSACT.CHECK_LATEST_BALANCES = TRUE then
2505:
2506: if HRASSACT.CHECK_RRVS_FIRST = TRUE then
2507:
2508: if g_debug then
2509: hr_utility.set_location('hrassact.trash_latest_balances',20);
2510: end if;
2505:
2506: if HRASSACT.CHECK_RRVS_FIRST = TRUE then
2507:
2508: if g_debug then
2509: hr_utility.set_location('hrassact.trash_latest_balances',20);
2510: end if;
2511: --
2512: -- Check for existance of run result value for input value
2513: --
2517:
2518: if ivchk%FOUND then
2519: --
2520: if g_debug then
2521: hr_utility.set_location('hrassact.trash_latest_balances',30);
2522: end if;
2523: -- Delete all balance context values and
2524: -- person latest balances.
2525: for plbcrec in platbalc loop
2530: where PLB.latest_balance_id = plbcrec.latest_balance_id;
2531: end loop;
2532:
2533: if g_debug then
2534: hr_utility.set_location('hrassact.trash_latest_balances',40);
2535: end if;
2536: -- Delete all balance context values and
2537: -- assignment latest balances.
2538:
2576: --
2577: end if;
2578: close ivchk;
2579:
2580: elsif HRASSACT.CHECK_LAT_BALS_FIRST = TRUE then
2581:
2582: if g_debug then
2583: hr_utility.set_location('hrassact.trash_latest_balances',50);
2584: end if;
2579:
2580: elsif HRASSACT.CHECK_LAT_BALS_FIRST = TRUE then
2581:
2582: if g_debug then
2583: hr_utility.set_location('hrassact.trash_latest_balances',50);
2584: end if;
2585: --
2586: -- Check for any latest balances before relevant run result value
2587: --
2610: end if;
2611: end loop;
2612: --
2613: if g_debug then
2614: hr_utility.set_location('hrassact.trash_latest_balances',60);
2615: end if;
2616: -- Delete all balance context values and
2617: -- assignment latest balances.
2618: if l_rrv_found <> 0 then
2678: --
2679: -- Original Code
2680: --
2681: if g_debug then
2682: hr_utility.set_location('hrassact.trash_latest_balances',70);
2683: end if;
2684: -- Delete all balance context values and
2685: -- person latest balances.
2686: for plbcrec in plbc loop
2691: where PLB.latest_balance_id = plbcrec.latest_balance_id;
2692: end loop;
2693: --
2694: if g_debug then
2695: hr_utility.set_location('hrassact.trash_latest_balances',80);
2696: end if;
2697: -- Delete all balance context values and
2698: -- assignment latest balances.
2699: open albc;
2717: END IF;
2718:
2719: --
2720: if g_debug then
2721: hr_utility.set_location('hrassact.trash_latest_balances',70);
2722: end if;
2723: --
2724: for lbcrec in lbc loop
2725: delete from pay_latest_balances ALB
2728: --
2729: end if;
2730: --
2731: if g_debug then
2732: hr_utility.set_location('hrassact.trash_latest_balances',90);
2733: end if;
2734: --
2735: for plrec in pl_feed_chk loop
2736: --
2734: --
2735: for plrec in pl_feed_chk loop
2736: --
2737: if g_debug then
2738: hr_utility.set_location('hrassact.trash_latest_balances',100);
2739: end if;
2740:
2741: delete from pay_balance_context_values BCV
2742: where BCV.latest_balance_id = plrec.latest_balance_id;
2785: --
2786: end if;
2787: --
2788: if g_debug then
2789: hr_utility.set_location('hrassact.trash_latest_balances',110);
2790: end if;
2791: --
2792: return;
2793: --
2833: Type t_latbal is table of pay_assignment_latest_balances.latest_balance_id%type;
2834: lat_bal_list t_latbal;
2835:
2836: begin
2837: hr_utility.set_location('hrassact.trash_latest_balances',10);
2838: --
2839: -- Delete all balance context values and
2840: -- person latest balances.
2841: for plbcrec in plbc loop
2845: delete from pay_person_latest_balances PLB
2846: where PLB.latest_balance_id = plbcrec.latest_balance_id;
2847: end loop;
2848: --
2849: hr_utility.set_location('hrassact.trash_latest_balances',20);
2850: -- Delete all balance context values and
2851: -- assignment latest balances.
2852: open albc;
2853: loop
2869: CLOSE albc;
2870: END IF;
2871:
2872: --
2873: hr_utility.set_location('hrassact.trash_latest_balances',30);
2874: for lbcrec in lbc loop
2875: delete from pay_latest_balances ALB
2876: where ALB.latest_balance_id = lbcrec.latest_balance_id;
2877: end loop;
2874: for lbcrec in lbc loop
2875: delete from pay_latest_balances ALB
2876: where ALB.latest_balance_id = lbcrec.latest_balance_id;
2877: end loop;
2878: hr_utility.set_location('hrassact.trash_latest_balances',40);
2879: --
2880: return;
2881: --
2882: end trash_latest_balances;
3005: g_debug := hr_utility.debug_enabled;
3006: --
3007: -- Simply return the person_id for the assignment.
3008: if g_debug then
3009: hr_utility.set_location('hrassact.del_latest_balances',10);
3010: end if;
3011: select asg.person_id
3012: into l_person_id
3013: from per_all_assignments_f asg
3017: --
3018: if (p_element_entry is null) and (p_element_type_id is null) then
3019: -- Delete (person) balance context values.
3020: if g_debug then
3021: hr_utility.set_location('hrassact.del_latest_balances',20);
3022: end if;
3023: for plbcrec in plbc(l_person_id) loop
3024: delete from pay_balance_context_values BCV
3025: where BCV.latest_balance_id = plbcrec.latest_balance_id;
3030: --
3031: -- We need to delete all latest balances for the
3032: -- person's period of service.
3033: if g_debug then
3034: hr_utility.set_location('hrassact.del_latest_balances',40);
3035: end if;
3036: for albcrec in albc(l_person_id) loop
3037: delete from pay_balance_context_values BCV
3038: where BCV.latest_balance_id = albcrec.latest_balance_id;
3041: where ALB.latest_balance_id = albcrec.latest_balance_id;
3042: end loop;
3043: --
3044: if g_debug then
3045: hr_utility.set_location('hrassact.del_latest_balances',45);
3046: end if;
3047: for lbcrec in lbc(l_person_id) loop
3048: delete from pay_latest_balances ALB
3049: where ALB.latest_balance_id = lbcrec.latest_balance_id;
3058: --
3059: -- Derive the element type id from the entry id.
3060: --
3061: if g_debug then
3062: hr_utility.set_location('hrassact.del_latest_balances',60);
3063: end if;
3064: select pel.element_type_id into l_element_type_id
3065: from pay_element_entries_f pee
3066: ,pay_element_links_f pel
3074: ;
3075: end if;
3076: -- Delete (person) balance context values.
3077: if g_debug then
3078: hr_utility.set_location('hrassact.del_latest_balances',70);
3079: end if;
3080: for plbcrec in plbc_selective(l_person_id, l_element_type_id) loop
3081: delete from pay_balance_context_values BCV
3082: where BCV.latest_balance_id = plbcrec.latest_balance_id;
3088: -- Delete contexts and latest balances for assignment.
3089: -- We need to delete all latest balances for the
3090: -- person's period of service.
3091: if g_debug then
3092: hr_utility.set_location('hrassact.del_latest_balances',80);
3093: end if;
3094: for albcrec in albc_selective(l_person_id, l_element_type_id) loop
3095: delete from pay_balance_context_values BCV
3096: where BCV.latest_balance_id = albcrec.latest_balance_id;
3099: where ALB.latest_balance_id = albcrec.latest_balance_id;
3100: end loop;
3101: --
3102: if g_debug then
3103: hr_utility.set_location('hrassact.del_latest_balances',85);
3104: end if;
3105: for lbcrec in lbc_selective(l_person_id, l_element_type_id) loop
3106: delete from pay_latest_balances ALB
3107: where ALB.latest_balance_id = lbcrec.latest_balance_id;
3409: --
3410: g_debug := hr_utility.debug_enabled;
3411: --
3412: if g_debug then
3413: hr_utility.set_location('hrassact.resequence_actions', 10);
3414: end if;
3415: if(rmode = 'Y') then
3416: open seqasg(aaid, chldact);
3417: elsif (rmode = 'N') then
3471: end if;
3472: end if;
3473: --
3474: if g_debug then
3475: hr_utility.set_location('hrassact.resequence_actions', 20);
3476: end if;
3477: end resequence_actions;
3478: --
3479: procedure resequence_children(p_asg_action in number)
3676: hr_utility.trace('passing asgid = '||asgid);
3677: hr_utility.trace('passing actseq = '||actseq);
3678: --
3679: if g_debug then
3680: hr_utility.set_location('hrassact.resequence_actions', 10);
3681: end if;
3682: if(rmode = 'Y') then
3683: open seqasg(asgid,actseq);
3684: elsif(rmode = 'N') then
3737: else
3738: close seqgrp;
3739: end if;
3740: if g_debug then
3741: hr_utility.set_location('hrassact.resequence_actions', 20);
3742: end if;
3743: end resequence_actions;
3744: --
3745: --
3767: g_debug := hr_utility.debug_enabled;
3768: --
3769: -- Find interlocks for this assignment action
3770: if g_debug then
3771: hr_utility.set_location('hrassact.applied_interlocks',10);
3772: end if;
3773: <
3774: for locked IN interlocks(p_locking_action_id) loop
3775: --
3783: -- For the second and subsequent locked action ids, append them to
3784: -- the current string, if there is enough space left for the dash
3785: -- and the whole of the id.
3786: if g_debug then
3787: hr_utility.set_location('hrassact.applied_interlocks',20);
3788: end if;
3789: v_string := v_string || '-' || locked.locked_action_id;
3790: else
3791: --
3793: -- If there is no space left at the end of the string for the
3794: -- current locked action id, add the arrow symbol to the end
3795: -- of the string
3796: if g_debug then
3797: hr_utility.set_location('hrassact.applied_interlocks',30);
3798: end if;
3799: v_string := v_string || '->';
3800: else
3801: -- If the end of the string has already been reached,
3800: else
3801: -- If the end of the string has already been reached,
3802: -- replace the last id with the arrow symbol
3803: if g_debug then
3804: hr_utility.set_location('hrassact.applied_interlocks',40);
3805: end if;
3806: v_string := substr(v_string, 1, instr(v_string, '-', -1, 1)-1)
3807: || '->';
3808: end if;
3819: --
3820: end loop interlocks_loop; -- end for locked IN interlocks
3821: --
3822: if g_debug then
3823: hr_utility.set_location('hrassact.applied_interlocks',40);
3824: end if;
3825: return v_string;
3826: --
3827: end applied_interlocks;
4014: run_type_id in number default null
4015: ) is
4016: --
4017: begin
4018: hrassact.inassact_main(pactid, asgid, p_ass_action_seq,
4019: p_serial_number, p_pre_payment_id,
4020: p_element_entry, p_asg_lock, null,
4021: p_purge_mode,run_type_id);
4022: end inassact;
4034: p_purge_mode in boolean default FALSE --purge mode
4035: ) is
4036: --
4037: begin
4038: hrassact.inassact_main(pactid, asgid, p_ass_action_seq,
4039: p_serial_number, p_pre_payment_id,
4040: p_element_entry, p_asg_lock, taxunt,
4041: p_purge_mode,null);
4042: end inassact_main;
4299: -- Need the effective date of the payroll action.
4300: -- Also want to see the action_type.
4301: -- The legislation and the tax unit is also needed for the US.
4302: if g_debug then
4303: hr_utility.set_location('hrassact.irbaact',10);
4304: --
4305: hr_utility.trace('pactid = '||pactid);
4306: hr_utility.trace('assignment_id = '||asgid);
4307: end if;
4327: taxunt2 := hr_dynsql.get_tax_unit(asgid, effdate);
4328: end if;
4329: --
4330: if g_debug then
4331: hr_utility.set_location('hrassact.irbaact',11);
4332: end if;
4333: end if;
4334: --
4335: if (actyp = 'V') then
4385: end if;
4386: --
4387: -- insert an assigment action.
4388: if g_debug then
4389: hr_utility.set_location('hrassact.irbaact',20);
4390: end if;
4391: if((actype = 'V' or actype = 'I' or actype = 'B')
4392: and p_mode <> 'BACKPAY') then
4393: -- we need to check that there isn't a failed action for this
4473: -- An exception is the 'I' action. It can be inserted
4474: -- before a purge, because a Purge uses balance
4475: -- initialisation to perform balance rollup.
4476: if g_debug then
4477: hr_utility.set_location('hrassact.irbaact',25);
4478: end if;
4479: insert into pay_assignment_actions (
4480: assignment_action_id,
4481: assignment_id,
4531: -- NOTE - the update of sequences for 'X' actions will
4532: -- work correctly because the seqasg cursor does not
4533: -- join to per_all_assignments_f.
4534: if g_debug then
4535: hr_utility.set_location('hrassact.irbaact',30);
4536: end if;
4537: if(rmode = 'Y') then
4538: open seqasg(pactid, asgid);
4539: elsif(rmode = 'N') then
4599: if(actyp <> 'X') then
4600: -- take the opportunity here to lock the assignment
4601: -- and period of service of the person.
4602: if g_debug then
4603: hr_utility.set_location('hrassact.inassact_main',10);
4604: end if;
4605: if (p_asg_lock) then
4606: -- We wish to lock the assignment and period of service.
4607: declare
4636: end;
4637: end if;
4638: --
4639: if g_debug then
4640: hr_utility.set_location('hrassact.inassact_main',15);
4641: end if;
4642: --
4643: -- get the rule_mode
4644: select /*+ ORDERED*/ plr.rule_mode
4668: -- as for the run.
4669: if(rmode = 'Y') then
4670: -- time period independent legislation.
4671: if g_debug then
4672: hr_utility.set_location('hrassact.inassact_main',20);
4673: end if;
4674: insert into pay_assignment_actions (
4675: assignment_action_id,
4676: assignment_id,
4717: and pa2.effective_date <= pac.effective_date)));
4718: else
4719: -- time period dependent legislation.
4720: if g_debug then
4721: hr_utility.set_location('hrassact.inassact_main',30);
4722: end if;
4723: insert into pay_assignment_actions (
4724: assignment_action_id,
4725: assignment_id,
4832:
4833: --
4834: -- Check that a row has been inserted.
4835: if g_debug then
4836: hr_utility.set_location('hrassact.inassact_main',40);
4837: end if;
4838: if(sql%rowcount = 0) then
4839: hr_utility.set_message(801,'HR_7010_ACTION_INTLOCK_FAIL');
4840: hr_utility.raise_error;
4845: -- We perform validation of the interlock rules here.
4846: -- kkawol: Only return rows for master assignment action.
4847: --
4848: if g_debug then
4849: hr_utility.set_location('hrassact.inassact_main',45);
4850: end if;
4851: insert into PAY_ASSIGNMENT_ACTIONS (
4852: ASSIGNMENT_ACTION_ID,
4853: ASSIGNMENT_ID,
4911: --
4912: -- update the action_population_status to indicate
4913: -- an action has been successfully inserted.
4914: if g_debug then
4915: hr_utility.set_location('hrassact.inassact_main',50);
4916: end if;
4917:
4918: begin
4919: select pac.action_population_status
4952: p_assignment_action_id out nocopy number,
4953: p_object_version_number out nocopy number
4954: ) is
4955: assactid number;
4956: c_indent constant varchar2(30) := 'hrassact.qpassact';
4957: begin
4958: g_debug := hr_utility.debug_enabled;
4959: --
4960: -- Simply call the inassact procedure.
5001: -- kkawol: query restricted to only return the assignment id
5002: -- of the master action.
5003: --
5004: if g_debug then
5005: hr_utility.set_location('hrassact.qpppassact',10);
5006: end if;
5007: select act.assignment_id
5008: into l_assignment_id
5009: from pay_assignment_actions act,
5013: and act.source_action_id is null;
5014: --
5015: -- Start by simply inserting an assignment action.
5016: if g_debug then
5017: hr_utility.set_location('hrassact.qpppassact',20);
5018: end if;
5019: hrassact.inassact(p_payroll_action_id,l_assignment_id,null,null,null);
5020: --
5021: -- Get some information for insert to interlocks.
5015: -- Start by simply inserting an assignment action.
5016: if g_debug then
5017: hr_utility.set_location('hrassact.qpppassact',20);
5018: end if;
5019: hrassact.inassact(p_payroll_action_id,l_assignment_id,null,null,null);
5020: --
5021: -- Get some information for insert to interlocks.
5022: -- kkawol: Only returning details for the master action.
5023: --
5021: -- Get some information for insert to interlocks.
5022: -- kkawol: Only returning details for the master action.
5023: --
5024: if g_debug then
5025: hr_utility.set_location('hrassact.qpppassact',30);
5026: end if;
5027: select pay_assignment_actions_s.currval,
5028: act.assignment_action_id
5029: into l_locking_action_id,
5035: and act.source_action_id is null;
5036: --
5037: -- We can now insert interlock row.
5038: if g_debug then
5039: hr_utility.set_location('hrassact.qpppassact',40);
5040: end if;
5041: insert into pay_action_interlocks (
5042: locking_action_id,
5043: locked_action_id)
5046: --
5047: -- Update the payroll actions table with the
5048: -- appropriate date_earned value.
5049: if g_debug then
5050: hr_utility.set_location('hrassact.qpppassact',50);
5051: end if;
5052: update pay_payroll_actions pac
5053: set pac.date_earned = (
5054: select pa2.date_earned
5178: begin
5179: if g_debug then
5180: hr_utility.trace('pactid: '||to_char(pactid));
5181: hr_utility.trace('assactid: '||to_char(assactid));
5182: hr_utility.set_location('hrassact.reversal',10);
5183: end if;
5184: select ac2.assignment_id,
5185: ac2.tax_unit_id,
5186: ac2.run_type_id,
5210: -- the BackPay process needs this.
5211: if(not redo) then
5212: --
5213: -- start by inserting an assignment action row.
5214: hrassact.inassact_main(pactid,asgid,null,null,null,null,TRUE,taxunt,FALSE,run_type_id);
5215: --
5216: hr_utility.set_location('hrassact.reversal',10);
5217: -- Return the Reversal's assignment_action_id
5218: select act.assignment_action_id
5212: --
5213: -- start by inserting an assignment action row.
5214: hrassact.inassact_main(pactid,asgid,null,null,null,null,TRUE,taxunt,FALSE,run_type_id);
5215: --
5216: hr_utility.set_location('hrassact.reversal',10);
5217: -- Return the Reversal's assignment_action_id
5218: select act.assignment_action_id
5219: into rev_assact
5220: from pay_assignment_actions act
5234: -- have not already reversed the assignment action.
5235: -- Do this by checking a reversal does not already lock
5236: -- this row.
5237: if g_debug then
5238: hr_utility.set_location('hrassact.reversal',20);
5239: end if;
5240: insert into pay_action_interlocks (
5241: locking_action_id,
5242: locked_action_id)
5256: if(sql%rowcount = 0) then
5257: hr_utility.set_message(801,'HR_7013_ACTION_IS_REVERSED');
5258: hr_utility.raise_error;
5259: end if;
5260: hr_utility.set_location('hrassact.reversal',10);
5261: --
5262: -- insert lock to master action if this is a sub action
5263:
5264: insert into pay_action_interlocks (
5379: declare
5380: dummy number; -- need this for syntax.
5381: begin
5382: if g_debug then
5383: hr_utility.set_location('hrassact.reversal',25);
5384: end if;
5385: select 1
5386: into dummy
5387: from pay_payroll_actions pac,
5396: hr_utility.raise_error;
5397: end;
5398: --
5399: if g_debug then
5400: hr_utility.set_location('hrassact.reversal',30);
5401: end if;
5402:
5403: insert into pay_run_results (
5404: run_result_id,
5487: hr_utility.trace('l_org_id_iv: '||l_org_id_iv);
5488: end if;
5489: --
5490: if g_debug then
5491: hr_utility.set_location('hrassact.reversal',35);
5492: end if;
5493: insert into pay_run_result_values (
5494: input_value_id,
5495: run_result_id,
5553: -- the Reversal itself, we call a procedure to create
5554: -- "pre-inserted" run results and values to allow the
5555: -- correct re-processing of element entries in a
5556: -- subsequent run.
5557: --hrassact.rev_pre_inserted_rr(pactid);
5558: --
5559: -- To signal that processing has been successfully
5560: -- completed, update the action_status to (C)omplete.
5561: hr_utility.set_location('hrassact.reversal',40);
5557: --hrassact.rev_pre_inserted_rr(pactid);
5558: --
5559: -- To signal that processing has been successfully
5560: -- completed, update the action_status to (C)omplete.
5561: hr_utility.set_location('hrassact.reversal',40);
5562: update pay_payroll_actions pac
5563: set pac.action_status = 'C'
5564: where pac.payroll_action_id = pactid;
5565: end;
5567: --
5568: -- Now do the reversal for pay_run_balances. 1st the asg level balances
5569: --
5570: if g_debug then
5571: hr_utility.set_location('hrassact.reversal',45);
5572: end if;
5573: --
5574: /* currently for multi reversal we are using pactid as the reversal
5575: pactid. If multi then the ass_act_id is the rev_act_id we want to
5604: --
5605: pay_balance_pkg.maintain_balances_for_action(rev_assact);
5606: --
5607: if g_debug then
5608: hr_utility.set_location('Leaving: hrassact.reversal', 100);
5609: end if;
5610: end reversal;
5611: --
5612: ----------------- multi_assignment_reversal ------------------------------
5696: where rr3.source_id = pee.element_entry_id
5697: and rr3.status = 'U')
5698: order by pee.element_entry_id;
5699: --
5700: c_indent constant varchar2(30) := 'hrassact.rev_pre_inserted_rr';
5701: begin
5702: g_debug := hr_utility.debug_enabled;
5703: -- Insert un-processed run results and values as appropriate
5704: if g_debug then
5765: p_serial_number in varchar2,
5766: p_pre_payment_id in number,
5767: p_reason in varchar2 default null
5768: ) is
5769: c_indent constant varchar2(30) := 'hrassact.ext_man_payment';
5770: l_payroll_action_id number;
5771: l_assignment_action_id number;
5772: l_business_group_id number;
5773: l_consolidation_set_id number;
5886: p_pre_payment_id in number, -- pre-payment id
5887: p_reason in varchar2 default null -- Reason
5888: )
5889: IS
5890: c_indent constant varchar2(30) := 'hrassact.ext_man_payment2';
5891:
5892: BEGIN
5893:
5894: g_debug := hr_utility.debug_enabled;
5963: dummy number;
5964: l_found boolean;
5965: l_action_type varchar2(30);
5966: l_effective_date date;
5967: c_indent constant varchar2(32) := 'hrassact.set_action_context';
5968: --
5969: begin
5970: g_debug := hr_utility.debug_enabled;
5971: --
6391: taxunit in number default null, -- tax unit id.
6392: purge_mode in boolean default false, -- are we calling in purge mode?
6393: run_type_id in number default null
6394: ) is
6395: c_indent constant varchar2(32) := 'hrassact.bal_adjust_actions';
6396: pactid number; -- payroll_action_id.
6397: busgrp number; -- business_group_id.
6398: legcode pay_legislation_rules.legislation_code%TYPE; -- leg code
6399: asgid number; -- assignment_id.
6712: ) is
6713: --
6714: l_pay_act_id number;
6715: l_asg_act_id number;
6716: c_indent constant varchar2(22) := 'hrassact.bal_adjust';
6717: begin
6718: g_debug := hr_utility.debug_enabled;
6719: --
6720: if g_debug then
6861: and pplb.person_id = p_person_id;
6862:
6863: bal_dims bal_dims_cache;
6864:
6865: c_indent constant varchar2(30) := 'hrassact.maintain_lat_bal';
6866: pactid pay_payroll_actions.payroll_action_id%TYPE;
6867: asgid per_all_assignments_f.assignment_id%TYPE;
6868: bus_grp_id per_all_assignments_f.business_group_id%TYPE;
6869: l_person_id per_all_assignments_f.person_id%TYPE;
6891: ) return varchar2 is
6892: --
6893: l_context_string varchar2(2000);
6894: --
6895: c_indent constant varchar2(35) := 'hrassact.create_context_string';
6896: begin
6897: --
6898: if g_debug then
6899: hr_utility.set_location(c_indent,10);
6925: p_bal_context_str in varchar2 -- list of context values.
6926: ) return boolean is
6927: l_feed_balance boolean := FALSE;
6928: --
6929: c_indent constant varchar2(35) := 'hrassact.proc_feed_check';
6930: l_feed_chk_str varchar2(2000); -- used with dynamic pl/sql
6931: sql_cursor integer;
6932: l_rows integer;
6933: l_feed_flag integer;
7043: ) return boolean is
7044: l_feed_flag boolean;
7045: l_match boolean;
7046:
7047: c_indent constant varchar2(35) := 'hrassact.lb_context_match_check';
7048: l_jurisdiction_level pay_balance_types.jurisdiction_level%TYPE;
7049: cnt number;
7050: l_tax_unit_id number;
7051: l_jurisdiction_code pay_run_result_values.result_value%type;
7350: value
7351: from pay_balance_context_values
7352: where latest_balance_id = p_lat_bal_id;
7353:
7354: c_indent constant varchar2(35) := 'hrassact.context_match_check';
7355: l_jurisdiction_level pay_balance_types.jurisdiction_level%TYPE;
7356: cnt number;
7357: --
7358: begin
7446: udca in context_details
7447: ) return boolean is
7448: feed_balance boolean;
7449: --
7450: c_indent constant varchar2(30) := 'hrassact.feed_check';
7451: feed_checking_type pay_balance_dimensions.feed_checking_type%TYPE;
7452: feed_checking_code pay_balance_dimensions.feed_checking_code%TYPE;
7453: dim_num number;
7454: found boolean := FALSE;
7591: p_bal_owner_eff_date in out nocopy date -- latest balance date
7592: ) return date is
7593: l_expiry_date date;
7594: --
7595: c_indent constant varchar2(35) := 'hrassact.get_expiry_date';
7596: l_payroll_action pay_payroll_actions.payroll_action_id%type;
7597: l_effective_date pay_payroll_actions.effective_date%type;
7598: l_bal_owner_pay_action pay_payroll_actions.payroll_action_id%type;
7599: l_expiry_chk_str varchar2(2000); -- used with dynamic pl/sql
7804: prev_expiry_date in out nocopy date -- lb prev expiry date
7805: ) return boolean is
7806: l_change_flag boolean;
7807: --
7808: c_indent constant varchar2(35) := 'hrassact.feed_balance';
7809: lat_bal_exp_date date;
7810: l_bal_owner_eff_date pay_payroll_actions.effective_date%type;
7811: bal_adj_exp_date date;
7812: bal_adj_eff_date pay_payroll_actions.effective_date%type;
8393: return l_mst_id;
8394:
8395: end;
8396: --
8397: end hrassact;