[Home] [Help]
231: CHECK_RRVS_FIRST and
232: CHECK_LAT_BALS_FIRST package
233: globals to tune behaviour of
234: trash_latest_balances.
235: 09-JAN-2003 alogue 115.63 2692195 Use of hr_utility.debug_enabled
236: for performance of trace
237: statements.
238: 06-DEC-2002 alogue 115.62 Subtle-paranoid fix to
239: bal_adjust_actions to avoid
636: c_eot constant date := to_date('31/12/4712','DD/MM/YYYY');
637: g_context_cache context_cache_type;
638: contexts_cached boolean := FALSE;
639: g_lat_bal_check_mode pay_action_parameters.parameter_value%TYPE := null;
640: g_debug boolean := hr_utility.debug_enabled;
641: g_dynamic_contexts pay_core_utils.t_contexts_tab;
642: --
643: --
644: --
648: p_rollback_mode in varchar2) is
649: begin
650: if p_rollback_mode = 'RETRY' then
651: if p_pact_rec.action_type in ('V', 'B', 'Z', 'E', 'H') then
652: hr_utility.set_message(801, 'HR_7093_ACTION_CANT_RETPAY');
653: hr_utility.set_message_token('ACTION_NAME',p_pact_rec.action_name);
654: hr_utility.raise_error;
655: end if;
656: else
649: begin
650: if p_rollback_mode = 'RETRY' then
651: if p_pact_rec.action_type in ('V', 'B', 'Z', 'E', 'H') then
652: hr_utility.set_message(801, 'HR_7093_ACTION_CANT_RETPAY');
653: hr_utility.set_message_token('ACTION_NAME',p_pact_rec.action_name);
654: hr_utility.raise_error;
655: end if;
656: else
657: if p_pact_rec.action_type = 'Z' then
650: if p_rollback_mode = 'RETRY' then
651: if p_pact_rec.action_type in ('V', 'B', 'Z', 'E', 'H') then
652: hr_utility.set_message(801, 'HR_7093_ACTION_CANT_RETPAY');
653: hr_utility.set_message_token('ACTION_NAME',p_pact_rec.action_name);
654: hr_utility.raise_error;
655: end if;
656: else
657: if p_pact_rec.action_type = 'Z' then
658: hr_utility.set_message(801, 'HR_7212_ACTION_RBACK_RULE');
654: hr_utility.raise_error;
655: end if;
656: else
657: if p_pact_rec.action_type = 'Z' then
658: hr_utility.set_message(801, 'HR_7212_ACTION_RBACK_RULE');
659: hr_utility.set_message_token('ACTION_NAME',p_pact_rec.action_name);
660: hr_utility.raise_error;
661: end if;
662: end if;
655: end if;
656: else
657: if p_pact_rec.action_type = 'Z' then
658: hr_utility.set_message(801, 'HR_7212_ACTION_RBACK_RULE');
659: hr_utility.set_message_token('ACTION_NAME',p_pact_rec.action_name);
660: hr_utility.raise_error;
661: end if;
662: end if;
663: end val_pact_rr_rules;
656: else
657: if p_pact_rec.action_type = 'Z' then
658: hr_utility.set_message(801, 'HR_7212_ACTION_RBACK_RULE');
659: hr_utility.set_message_token('ACTION_NAME',p_pact_rec.action_name);
660: hr_utility.raise_error;
661: end if;
662: end if;
663: end val_pact_rr_rules;
664: --
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;
694: --
695: procedure validate_rollback_mode(p_rollback_mode in varchar2) is
696: begin
697: if p_rollback_mode not in ('RETRY', 'ROLLBACK', 'BACKPAY') then
698: hr_utility.set_message(801, 'HR_7000_ACTION_BAD_ROLL_MODE');
699: hr_utility.raise_error;
700: end if;
701: end validate_rollback_mode;
702: --
695: procedure validate_rollback_mode(p_rollback_mode in varchar2) is
696: begin
697: if p_rollback_mode not in ('RETRY', 'ROLLBACK', 'BACKPAY') then
698: hr_utility.set_message(801, 'HR_7000_ACTION_BAD_ROLL_MODE');
699: hr_utility.raise_error;
700: end if;
701: end validate_rollback_mode;
702: --
703: --
771: from pay_element_entries_f
772: where updating_action_id = p_assact_id);
773: --
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;
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');
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');
790: hr_utility.set_message_token('ASSACTID',p_assact_id);
791: hr_utility.raise_error;
792: end loop;
793: --
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');
790: hr_utility.set_message_token('ASSACTID',p_assact_id);
791: hr_utility.raise_error;
792: end loop;
793: --
794: else
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');
790: hr_utility.set_message_token('ASSACTID',p_assact_id);
791: hr_utility.raise_error;
792: end loop;
793: --
794: else
795: --
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');
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');
802: hr_utility.set_message_token('ASSACTID',p_assact_id);
803: hr_utility.raise_error;
804: end loop;
805: --
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');
802: hr_utility.set_message_token('ASSACTID',p_assact_id);
803: hr_utility.raise_error;
804: end loop;
805: --
806: 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');
802: hr_utility.set_message_token('ASSACTID',p_assact_id);
803: hr_utility.raise_error;
804: end loop;
805: --
806: end if;
807: --
828: from pay_message_lines
829: where source_type = 'P'
830: and source_id = p_pact_id);
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;
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');
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');
840: hr_utility.raise_error;
841: end loop;
842: return;
843: end ensure_pact_rolled_back;
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');
840: hr_utility.raise_error;
841: end loop;
842: return;
843: end ensure_pact_rolled_back;
844: --
865: l_action_status pay_assignment_actions.action_status%type;
866: l_person_id per_all_people_f.person_id%type;
867: begin
868: --
869: g_debug := hr_utility.debug_enabled;
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
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,
1135: and pay.payroll_id (+) = pac.payroll_id
1136: and pac.effective_date between
1137: pay.effective_start_date (+) and pay.effective_end_date (+);
1138: if g_debug then
1139: hr_utility.trace('action type is ' || p_pact_rec.action_type );
1140: end if;
1141: --
1142: --
1143: -- some types (e.g, purge) of action can't be rolled back full stop.
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
1156: where action_type = p_pact_rec.action_type
1157: and classification_name = 'NONREMOVEABLE');
1158: exception
1159: when no_data_found then
1160: hr_utility.set_message(801, 'HR_6216_ACTION_CANT_PURGE');
1161: hr_utility.set_message_token('PACT_ID',
1162: to_char(p_pact_rec.pact_id));
1163: hr_utility.raise_error;
1164: end;
1157: and classification_name = 'NONREMOVEABLE');
1158: exception
1159: when no_data_found then
1160: hr_utility.set_message(801, 'HR_6216_ACTION_CANT_PURGE');
1161: hr_utility.set_message_token('PACT_ID',
1162: to_char(p_pact_rec.pact_id));
1163: hr_utility.raise_error;
1164: end;
1165: --
1159: when no_data_found then
1160: hr_utility.set_message(801, 'HR_6216_ACTION_CANT_PURGE');
1161: hr_utility.set_message_token('PACT_ID',
1162: to_char(p_pact_rec.pact_id));
1163: hr_utility.raise_error;
1164: end;
1165: --
1166: --
1167: -- get some more info needed to roll back actions
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
1190: from pay_action_classifications CLASS
1191: where CLASS.action_type = p_pact_rec.action_type
1192: and CLASS.classification_name = 'SEQUENCED';
1193: if g_debug then
1194: hr_utility.trace('this action type IS sequenced');
1195: end if;
1196: exception
1197: when no_data_found then
1198: p_pact_rec.sequenced_flag := FALSE;
1196: exception
1197: when no_data_found then
1198: p_pact_rec.sequenced_flag := FALSE;
1199: if g_debug then
1200: hr_utility.trace('this action type NOT sequenced');
1201: end if;
1202: end;
1203: end val_pact_rollback;
1204: --
1209: p_rollback_mode in varchar2
1210: ) is
1211: l_pact_rec pact_details;
1212: begin
1213: g_debug := hr_utility.debug_enabled;
1214: --
1215: -- We simply call the internal validate rollback procedure
1216: -- to give us the information we need.
1217: if g_debug then
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,
1241: l_pact_rec pact_details;
1242: l_assact_rec assact_details;
1243: result boolean;
1244: begin
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);
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;
1383: c_indent constant varchar2(30) := 'pyassact.undo_stop_update';
1384: begin
1385: -- Select some information about the entry we are operating on.
1386: if g_debug then
1387: hr_utility.set_location(c_indent, 10);
1388: end if;
1389: select pee.effective_start_date,
1390: pee.effective_end_date,
1391: pee.original_entry_id,
1405: -- For undo update, we have to get next effective start date.
1406: if(p_mode = 'DELETE_NEXT_CHANGE') then
1407: begin
1408: if g_debug then
1409: hr_utility.set_location(c_indent, 20);
1410: end if;
1411: select min(ee.effective_end_date)
1412: into next_end
1413: from pay_element_entries_f ee
1440: exception
1441: -- Several error messages can be raised from this procedure.
1442: -- We wish to trap a number of them, as they should be ignored
1443: -- for our purposes.
1444: when hr_utility.hr_error then
1445: hr_utility.get_message_details(message,applid);
1446: --
1447: if(message in ('HR_7699_ELE_ENTRY_REC_EXISTS',
1448: 'HR_7700_ELE_ENTRY_REC_EXISTS',
1441: -- Several error messages can be raised from this procedure.
1442: -- We wish to trap a number of them, as they should be ignored
1443: -- for our purposes.
1444: when hr_utility.hr_error then
1445: hr_utility.get_message_details(message,applid);
1446: --
1447: if(message in ('HR_7699_ELE_ENTRY_REC_EXISTS',
1448: 'HR_7700_ELE_ENTRY_REC_EXISTS',
1449: 'HR_6281_ELE_ENTRY_DT_DEL_LINK',
1451: 'HR_6284_ELE_ENTRY_DT_ASG_DEL')
1452: ) then
1453: -- We cannot extend the entry.
1454: if g_debug then
1455: hr_utility.set_location(c_indent, 25);
1456: end if;
1457: return;
1458: else
1459: -- Should fail if it is anything else.
1466: --
1467: declare dummy number;
1468: begin
1469: if g_debug then
1470: hr_utility.set_location(c_indent, 30);
1471: end if;
1472: select null
1473: into dummy
1474: from pay_element_entries_f ee
1479: and (ee.effective_start_date <= val_end and
1480: ee.effective_end_date >= val_start);
1481: --
1482: -- If row returned, we are in trouble.
1483: hr_utility.set_message(801, 'HR_6956_ELE_ENTRY_OVERLAP');
1484: hr_utility.raise_error;
1485: --
1486: exception
1487: when no_data_found then null;
1480: ee.effective_end_date >= val_start);
1481: --
1482: -- If row returned, we are in trouble.
1483: hr_utility.set_message(801, 'HR_6956_ELE_ENTRY_OVERLAP');
1484: hr_utility.raise_error;
1485: --
1486: exception
1487: when no_data_found then null;
1488: end;
1498: --
1499: -- Process the delete of element entries.
1500: if(p_mode = 'DELETE_NEXT_CHANGE') then
1501: if g_debug then
1502: hr_utility.set_location(c_indent, 40);
1503: end if;
1504: delete from pay_element_entries_f ee
1505: where ee.element_entry_id = p_ee_id
1506: and ee.effective_start_date = val_start;
1505: where ee.element_entry_id = p_ee_id
1506: and ee.effective_start_date = val_start;
1507: --
1508: if g_debug then
1509: hr_utility.set_location(c_indent, 50);
1510: end if;
1511: update pay_element_entries_f ee
1512: set ee.effective_end_date = decode(val_end, c_eot, effend, val_end)
1513: where ee.element_entry_id = p_ee_id
1515: --
1516: elsif(p_mode = 'FUTURE_CHANGE') then
1517: --
1518: if g_debug then
1519: hr_utility.set_location(c_indent, 60);
1520: end if;
1521: delete from pay_element_entries_f ee
1522: where ee.element_entry_id = p_ee_id
1523: and ee.effective_start_date > effstart;
1522: where ee.element_entry_id = p_ee_id
1523: and ee.effective_start_date > effstart;
1524: --
1525: if g_debug then
1526: hr_utility.set_location(c_indent, 70);
1527: end if;
1528: update pay_element_entries_f ee
1529: set ee.effective_end_date = effend
1530: where ee.element_entry_id = p_ee_id
1533: --
1534: -- Now, delete the entry values between validation start/end dates..
1535: --
1536: if g_debug then
1537: hr_utility.set_location(c_indent, 80);
1538: end if;
1539: delete from pay_element_entry_values_f eev
1540: where eev.element_entry_id = p_ee_id
1541: and ((eev.effective_end_date between val_start and val_end)
1542: or (eev.effective_start_date between val_start and val_end));
1543: --
1544: -- Update the effective end date as appropriate.
1545: if g_debug then
1546: hr_utility.set_location(c_indent, 90);
1547: end if;
1548: update pay_element_entry_values_f eev
1549: set eev.effective_end_date = val_end
1550: where eev.element_entry_id = p_ee_id
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;
1826: --
1827: declare
1828: mesg_text pay_message_lines.line_text%type;
1829: begin
1830: hr_utility.set_message (801, 'HR_ACTION_ASACT_ROLLOK');
1831: hr_utility.set_message_token
1832: ('ASG_NUMBER',p_assact_rec.assignment_number);
1833: hr_utility.set_message_token
1834: ('FULL_NAME',p_assact_rec.full_name);
1827: declare
1828: mesg_text pay_message_lines.line_text%type;
1829: begin
1830: hr_utility.set_message (801, 'HR_ACTION_ASACT_ROLLOK');
1831: hr_utility.set_message_token
1832: ('ASG_NUMBER',p_assact_rec.assignment_number);
1833: hr_utility.set_message_token
1834: ('FULL_NAME',p_assact_rec.full_name);
1835: hr_utility.set_message_token
1829: begin
1830: hr_utility.set_message (801, 'HR_ACTION_ASACT_ROLLOK');
1831: hr_utility.set_message_token
1832: ('ASG_NUMBER',p_assact_rec.assignment_number);
1833: hr_utility.set_message_token
1834: ('FULL_NAME',p_assact_rec.full_name);
1835: hr_utility.set_message_token
1836: ('SYSDATE',fnd_date.date_to_canonical(p_pact_rec.current_date));
1837: mesg_text := substrb(hr_utility.get_message,1,240);
1831: hr_utility.set_message_token
1832: ('ASG_NUMBER',p_assact_rec.assignment_number);
1833: hr_utility.set_message_token
1834: ('FULL_NAME',p_assact_rec.full_name);
1835: hr_utility.set_message_token
1836: ('SYSDATE',fnd_date.date_to_canonical(p_pact_rec.current_date));
1837: mesg_text := substrb(hr_utility.get_message,1,240);
1838: --
1839: -- now write our message to the payroll_action level. We
1833: hr_utility.set_message_token
1834: ('FULL_NAME',p_assact_rec.full_name);
1835: hr_utility.set_message_token
1836: ('SYSDATE',fnd_date.date_to_canonical(p_pact_rec.current_date));
1837: mesg_text := substrb(hr_utility.get_message,1,240);
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
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;
1947:
1948: -- populate payroll action details and perform high level validation
1949: l_pact_rec.pact_id := p_payroll_action_id;
1950: if g_debug then
1951: hr_utility.set_location(c_indent, 10);
1952: end if;
1953: val_pact_rollback(p_pact_rec => l_pact_rec,
1954: p_rollback_mode => p_rollback_mode);
1955: --
1967: -- We need to roll back assignment actions in reverse order
1968: -- from high action_sequence down (for multiple assignments).
1969: -- Therefore, start by selecting the max action_sequence.
1970: if g_debug then
1971: hr_utility.set_location(c_indent, 20);
1972: end if;
1973: select max(act.action_sequence) + 1
1974: into l_cur_aseq
1975: from pay_assignment_actions act
2010: more_actions := FALSE;
2011: l_counter := 0;
2012: --
2013: if g_debug then
2014: hr_utility.set_location(c_indent,30);
2015: end if;
2016: for c1rec in c1 loop
2017: exit when l_counter = p_chunk_size;
2018: more_actions := TRUE;
2024: --
2025: -- see if OK to roll back or retry this assignment action
2026: l_assact_rec.assact_id := c1rec.assignment_action_id;
2027: if g_debug then
2028: hr_utility.set_location(c_indent,40);
2029: end if;
2030: if val_assact_rollback
2031: (p_pact_rec => l_pact_rec,
2032: p_assact_rec => l_assact_rec,
2033: p_rollback_mode => p_rollback_mode) then
2034: --
2035: -- OK, clean up all child records for the action
2036: if g_debug then
2037: hr_utility.set_location(c_indent,50);
2038: end if;
2039: do_assact_rollback
2040: (p_pact_rec => l_pact_rec,
2041: p_assact_rec => l_assact_rec,
2057: end if;
2058: --
2059: -- decide whether to leap out entirely
2060: if p_all_or_nothing then
2061: hr_utility.set_message(801, 'HR_7008_ACTION_CANT_ROLLBACK');
2062: hr_utility.set_message_token
2063: ('FAILING_ASSACT', to_char(c1rec.assignment_action_id));
2064: hr_utility.raise_error;
2065: end if;
2058: --
2059: -- decide whether to leap out entirely
2060: if p_all_or_nothing then
2061: hr_utility.set_message(801, 'HR_7008_ACTION_CANT_ROLLBACK');
2062: hr_utility.set_message_token
2063: ('FAILING_ASSACT', to_char(c1rec.assignment_action_id));
2064: hr_utility.raise_error;
2065: end if;
2066: end if;
2060: if p_all_or_nothing then
2061: hr_utility.set_message(801, 'HR_7008_ACTION_CANT_ROLLBACK');
2062: hr_utility.set_message_token
2063: ('FAILING_ASSACT', to_char(c1rec.assignment_action_id));
2064: hr_utility.raise_error;
2065: end if;
2066: end if;
2067: --
2068: end loop; -- cursor loop.
2076: end;
2077: --
2078: -- trash messages
2079: if g_debug then
2080: hr_utility.set_location(c_indent,60);
2081: end if;
2082: delete from pay_message_lines ML
2083: where ML.source_type = 'P'
2084: and ML.source_id = p_payroll_action_id;
2085: --
2086: -- now trash the payroll action itself if the user so desires (and if
2087: -- all assignment actions where successfully rolled back).
2088: if g_debug then
2089: hr_utility.set_location(c_indent,70);
2090: end if;
2091: if not p_leave_base_table_row and p_failed_assact is null then
2092: --
2093: --
2099: -- us BACS or NACHA or whatever, before generating
2100: -- the message.
2101: if(l_pact_rec.action_type = 'M') then
2102: if g_debug then
2103: hr_utility.set_location(c_indent,90);
2104: end if;
2105: select ppt_tl.payment_type_name
2106: into l_pact_rec.action_name
2107: from pay_payroll_actions pac,
2119: mesg_text pay_message_lines.line_text%type;
2120: begin
2121: if(l_pact_rec.payroll_name is null) then
2122: -- Set up message for no payroll case.
2123: hr_utility.set_message(801,'HR_ACTION_PACT_ROLLNOPAY');
2124: hr_utility.set_message_token('ACTION_TYPE',l_pact_rec.action_name);
2125: hr_utility.set_message_token('BG_NAME',l_pact_rec.bg_name);
2126: hr_utility.set_message_token('SYSDATE',
2127: fnd_date.date_to_canonical(l_pact_rec.current_date));
2120: begin
2121: if(l_pact_rec.payroll_name is null) then
2122: -- Set up message for no payroll case.
2123: hr_utility.set_message(801,'HR_ACTION_PACT_ROLLNOPAY');
2124: hr_utility.set_message_token('ACTION_TYPE',l_pact_rec.action_name);
2125: hr_utility.set_message_token('BG_NAME',l_pact_rec.bg_name);
2126: hr_utility.set_message_token('SYSDATE',
2127: fnd_date.date_to_canonical(l_pact_rec.current_date));
2128: else
2121: if(l_pact_rec.payroll_name is null) then
2122: -- Set up message for no payroll case.
2123: hr_utility.set_message(801,'HR_ACTION_PACT_ROLLNOPAY');
2124: hr_utility.set_message_token('ACTION_TYPE',l_pact_rec.action_name);
2125: hr_utility.set_message_token('BG_NAME',l_pact_rec.bg_name);
2126: hr_utility.set_message_token('SYSDATE',
2127: fnd_date.date_to_canonical(l_pact_rec.current_date));
2128: else
2129: -- Set up message for payroll case.
2122: -- Set up message for no payroll case.
2123: hr_utility.set_message(801,'HR_ACTION_PACT_ROLLNOPAY');
2124: hr_utility.set_message_token('ACTION_TYPE',l_pact_rec.action_name);
2125: hr_utility.set_message_token('BG_NAME',l_pact_rec.bg_name);
2126: hr_utility.set_message_token('SYSDATE',
2127: fnd_date.date_to_canonical(l_pact_rec.current_date));
2128: else
2129: -- Set up message for payroll case.
2130: hr_utility.set_message(801,'HR_ACTION_PACT_ROLLPAY');
2126: hr_utility.set_message_token('SYSDATE',
2127: fnd_date.date_to_canonical(l_pact_rec.current_date));
2128: else
2129: -- Set up message for payroll case.
2130: hr_utility.set_message(801,'HR_ACTION_PACT_ROLLPAY');
2131: hr_utility.set_message_token('ACTION_TYPE',l_pact_rec.action_name);
2132: hr_utility.set_message_token('PAYROLL_NAME',
2133: l_pact_rec.payroll_name);
2134: hr_utility.set_message_token('BG_NAME',l_pact_rec.bg_name);
2127: fnd_date.date_to_canonical(l_pact_rec.current_date));
2128: else
2129: -- Set up message for payroll case.
2130: hr_utility.set_message(801,'HR_ACTION_PACT_ROLLPAY');
2131: hr_utility.set_message_token('ACTION_TYPE',l_pact_rec.action_name);
2132: hr_utility.set_message_token('PAYROLL_NAME',
2133: l_pact_rec.payroll_name);
2134: hr_utility.set_message_token('BG_NAME',l_pact_rec.bg_name);
2135: hr_utility.set_message_token('SYSDATE',
2128: else
2129: -- Set up message for payroll case.
2130: hr_utility.set_message(801,'HR_ACTION_PACT_ROLLPAY');
2131: hr_utility.set_message_token('ACTION_TYPE',l_pact_rec.action_name);
2132: hr_utility.set_message_token('PAYROLL_NAME',
2133: l_pact_rec.payroll_name);
2134: hr_utility.set_message_token('BG_NAME',l_pact_rec.bg_name);
2135: hr_utility.set_message_token('SYSDATE',
2136: fnd_date.date_to_canonical(l_pact_rec.current_date));
2130: hr_utility.set_message(801,'HR_ACTION_PACT_ROLLPAY');
2131: hr_utility.set_message_token('ACTION_TYPE',l_pact_rec.action_name);
2132: hr_utility.set_message_token('PAYROLL_NAME',
2133: l_pact_rec.payroll_name);
2134: hr_utility.set_message_token('BG_NAME',l_pact_rec.bg_name);
2135: hr_utility.set_message_token('SYSDATE',
2136: fnd_date.date_to_canonical(l_pact_rec.current_date));
2137: end if;
2138: mesg_text := substrb(hr_utility.get_message,1,240);
2131: hr_utility.set_message_token('ACTION_TYPE',l_pact_rec.action_name);
2132: hr_utility.set_message_token('PAYROLL_NAME',
2133: l_pact_rec.payroll_name);
2134: hr_utility.set_message_token('BG_NAME',l_pact_rec.bg_name);
2135: hr_utility.set_message_token('SYSDATE',
2136: fnd_date.date_to_canonical(l_pact_rec.current_date));
2137: end if;
2138: mesg_text := substrb(hr_utility.get_message,1,240);
2139: --
2134: hr_utility.set_message_token('BG_NAME',l_pact_rec.bg_name);
2135: hr_utility.set_message_token('SYSDATE',
2136: fnd_date.date_to_canonical(l_pact_rec.current_date));
2137: end if;
2138: mesg_text := substrb(hr_utility.get_message,1,240);
2139: --
2140: if g_debug then
2141: hr_utility.set_location(c_indent,100);
2142: end if;
2137: end if;
2138: mesg_text := substrb(hr_utility.get_message,1,240);
2139: --
2140: if g_debug then
2141: hr_utility.set_location(c_indent,100);
2142: end if;
2143: insert into pay_message_lines (
2144: line_sequence,
2145: payroll_id,
2158:
2159: end;
2160: --
2161: if g_debug then
2162: hr_utility.set_location(c_indent,80);
2163: end if;
2164: delete from pay_payroll_actions
2165: where payroll_action_id = p_payroll_action_id;
2166: --
2165: where payroll_action_id = p_payroll_action_id;
2166: --
2167: if not p_all_or_nothing then
2168: if g_debug then
2169: hr_utility.set_location(c_indent,110);
2170: end if;
2171: commit;
2172: end if;
2173: --
2173: --
2174: end if;
2175: --
2176: if g_debug then
2177: hr_utility.set_location(c_indent,120);
2178: end if;
2179: return;
2180: --
2181: end do_pact_rollback;
2205: (p_payroll_action_id in number,
2206: p_rollback_mode in varchar2,
2207: p_leave_base_table_row in boolean) is
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;
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,
2223: p_failed_assact in out nocopy number,
2224: p_rollback_mode in varchar2,
2225: p_leave_base_table_row in boolean) is
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;
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,
2242: p_failed_assact in out nocopy number,
2243: p_rollback_mode in varchar2,
2244: p_leave_base_table_row in boolean) is
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;
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,
2277: for update of action_status;
2278: --
2279: --
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;
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,
2313: p_assact_rec => l_assact_rec,
2314: p_rollback_mode => p_rollback_mode,
2315: p_leave_base_table_row => p_leave_base_table_row);
2316: else
2317: hr_utility.set_message(801, 'HR_7008_ACTION_CANT_ROLLBACK');
2318: hr_utility.set_message_token('FAILING_ASSACT',l_assact_rec.assact_id);
2319: hr_utility.raise_error;
2320: end if;
2321: --
2314: p_rollback_mode => p_rollback_mode,
2315: p_leave_base_table_row => p_leave_base_table_row);
2316: else
2317: hr_utility.set_message(801, 'HR_7008_ACTION_CANT_ROLLBACK');
2318: hr_utility.set_message_token('FAILING_ASSACT',l_assact_rec.assact_id);
2319: hr_utility.raise_error;
2320: end if;
2321: --
2322: end loop;
2315: p_leave_base_table_row => p_leave_base_table_row);
2316: else
2317: hr_utility.set_message(801, 'HR_7008_ACTION_CANT_ROLLBACK');
2318: hr_utility.set_message_token('FAILING_ASSACT',l_assact_rec.assact_id);
2319: hr_utility.raise_error;
2320: end if;
2321: --
2322: end loop;
2323: --
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;
2472: Type t_latbal is table of pay_assignment_latest_balances.latest_balance_id%type;
2473: lat_bal_list t_latbal;
2474: --
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;
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
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:
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;
3001: --
3002: l_person_id number;
3003: l_element_type_id number;
3004: begin
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);
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;
3406: upd_aa_id pay_assignment_actions.assignment_action_id%type;
3407: --
3408: begin
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;
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)
3669: obj_type pay_assignment_actions.object_type%type;
3670: --
3671: begin
3672: --
3673: g_debug := hr_utility.debug_enabled;
3674: --
3675: hr_utility.trace('passing pact = '||pactid);
3676: hr_utility.trace('passing asgid = '||asgid);
3677: hr_utility.trace('passing actseq = '||actseq);
3671: begin
3672: --
3673: g_debug := hr_utility.debug_enabled;
3674: --
3675: hr_utility.trace('passing pact = '||pactid);
3676: hr_utility.trace('passing asgid = '||asgid);
3677: hr_utility.trace('passing actseq = '||actseq);
3678: --
3679: if g_debug then
3672: --
3673: g_debug := hr_utility.debug_enabled;
3674: --
3675: hr_utility.trace('passing pact = '||pactid);
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);
3673: g_debug := hr_utility.debug_enabled;
3674: --
3675: hr_utility.trace('passing pact = '||pactid);
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;
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
3699: fetch seqgrp into my_rowid, upd_aa_id, src_aa_id, obj_type;
3700: exit when seqgrp%notfound;
3701: end if;
3702: --
3703: hr_utility.trace('Resequenceing '||upd_aa_id);
3704: resequence_children(upd_aa_id);
3705: --
3706: --
3707: -- Now, update with new action_sequence.
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: --
3763: v_string varchar2(240) := NULL;
3764: v_max_length_reached boolean := false;
3765: --
3766: begin
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);
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;
4286: l_run_type_id number;
4287: aa_exists number;
4288: --
4289: begin
4290: g_debug := hr_utility.debug_enabled;
4291: --
4292: taxunt2 := null;
4293: l_run_type_id := p_run_type_id;
4294: --
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;
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;
4308: --
4309: select pac.effective_date,
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;
4308: --
4309: select pac.effective_date,
4310: pac.action_type,
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
4461: end;
4462: end loop;
4463: end if;
4464: if(aa_exists <> 0) then
4465: hr_utility.set_message(801,'HR_7010_ACTION_INTLOCK_FAIL');
4466: hr_utility.raise_error;
4467: end if;
4468: end if;
4469: -- we need to check that there is not a purge action
4462: end loop;
4463: end if;
4464: if(aa_exists <> 0) then
4465: hr_utility.set_message(801,'HR_7010_ACTION_INTLOCK_FAIL');
4466: hr_utility.raise_error;
4467: end if;
4468: end if;
4469: -- we need to check that there is not a purge action
4470: -- with an effective date later than the effective
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,
4511: and pa2.action_type = 'Z'
4512: and pa2.effective_date > pac.effective_date);
4513: --
4514: if(sql%rowcount = 0) then
4515: hr_utility.set_message(801,'HR_7009_ACTION_FUTURE_PURGE');
4516: hr_utility.raise_error;
4517: end if;
4518: --
4519: -- If we are really getting called from purge, we do not
4512: and pa2.effective_date > pac.effective_date);
4513: --
4514: if(sql%rowcount = 0) then
4515: hr_utility.set_message(801,'HR_7009_ACTION_FUTURE_PURGE');
4516: hr_utility.raise_error;
4517: end if;
4518: --
4519: -- If we are really getting called from purge, we do not
4520: -- want to perform any re-sequencing. Purge mode is actually
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
4578: end if;
4579: end irbaact;
4580: --
4581: begin
4582: g_debug := hr_utility.debug_enabled;
4583: --
4584: -- Start by grabbing the type of the action we are dealing with.
4585: select pac.action_type,
4586: pac.business_group_id,
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,
4773: -- If a row was not inserted, it must mean interlock
4774: -- rule failure for that particular assignment.
4775: -- This condition is reported via an error message.
4776: if(sql%rowcount = 0) then
4777: hr_utility.set_message(801,'HR_7010_ACTION_INTLOCK_FAIL');
4778: hr_utility.raise_error;
4779: end if;
4780: elsif (actyp in ('V','B','N','X','I')) then
4781: --
4774: -- rule failure for that particular assignment.
4775: -- This condition is reported via an error message.
4776: if(sql%rowcount = 0) then
4777: hr_utility.set_message(801,'HR_7010_ACTION_INTLOCK_FAIL');
4778: hr_utility.raise_error;
4779: end if;
4780: elsif (actyp in ('V','B','N','X','I')) then
4781: --
4782: -- The following called to create assignment action for
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;
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;
4841: end if;
4842: --
4843: elsif (actyp = 'U') 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;
4841: end if;
4842: --
4843: elsif (actyp = 'U') then
4844: -- Insert an assignment action for a QuickPay Pre-Payment.
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,
4903: and as3.person_id = pos.person_id);
4904: else
4905: -- If we get here, we are attempting to process
4906: -- an illegal action type.
4907: hr_utility.set_message(801,'HR_7034_ACTION_TYP_INV_VBN');
4908: hr_utility.raise_error;
4909: end if;
4910: --
4911: --
4904: else
4905: -- If we get here, we are attempting to process
4906: -- an illegal action type.
4907: hr_utility.set_message(801,'HR_7034_ACTION_TYP_INV_VBN');
4908: hr_utility.raise_error;
4909: end if;
4910: --
4911: --
4912: -- update the action_population_status to indicate
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
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.
4961: if g_debug then
4962: hr_utility.set_location(c_indent,5);
4958: g_debug := hr_utility.debug_enabled;
4959: --
4960: -- Simply call the inassact procedure.
4961: if g_debug then
4962: hr_utility.set_location(c_indent,5);
4963: end if;
4964: inassact(p_payroll_action_id, p_assignment_id, null, null, null);
4965: --
4966: -- Get the assignment_action_id
4992: l_assignment_id number;
4993: l_locking_action_id number;
4994: l_locked_action_id number;
4995: begin
4996: g_debug := hr_utility.debug_enabled;
4997: --
4998: -- We have to get the assignment_id
4999: -- of the assignment that is going
5000: -- to be prepaid, so it may be passed on.
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.
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
5161: l_st2_needed pay_legislation_rules.rule_mode%type;
5162: l_value pay_action_parameters.parameter_value%type;
5163: l_tax_group hr_organization_information.org_information5%type;
5164: begin
5165: g_debug := hr_utility.debug_enabled;
5166: --
5167: -- To process the reversal, we need to
5168: -- obtain information about the assignment.
5169: -- Note : in addition, the following select
5176: -- required for the reversal assigment action.
5177: -- not part of assignment set reversal so perform normal logic
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,
5177: -- not part of assignment set reversal so perform normal logic
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,
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,
5199: and acl.action_type = pa2.action_type
5200: and pa2.effective_date <= pac.effective_date;
5201: exception
5202: when no_data_found then
5203: hr_utility.set_message(801,'HR_7011_ACTION_ILLEGAL_REV');
5204: hr_utility.raise_error;
5205: end;
5206: --
5207: if not multi then
5200: and pa2.effective_date <= pac.effective_date;
5201: exception
5202: when no_data_found then
5203: hr_utility.set_message(801,'HR_7011_ACTION_ILLEGAL_REV');
5204: hr_utility.raise_error;
5205: end;
5206: --
5207: if not multi then
5208: -- If redo is true, we do not not need to insert
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)
5253: and pa2.payroll_action_id = ac2.payroll_action_id
5254: and pa2.action_type = 'V');
5255: --
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: --
5254: and pa2.action_type = 'V');
5255: --
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
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,
5391: and pac.effective_date between
5392: pet.effective_start_date and pet.effective_end_date;
5393: exception
5394: when no_data_found then
5395: hr_utility.set_message(801,'HR_7012_ACTION_ELE_NOT_EFF');
5396: hr_utility.raise_error;
5397: end;
5398: --
5399: if g_debug then
5392: pet.effective_start_date and pet.effective_end_date;
5393: exception
5394: when no_data_found then
5395: hr_utility.set_message(801,'HR_7012_ACTION_ELE_NOT_EFF');
5396: hr_utility.raise_error;
5397: end;
5398: --
5399: if g_debug then
5400: hr_utility.set_location('hrassact.reversal',30);
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,
5441: -- or SOURCE_NUMBER input value, then multiply by 1, otherwise by
5442: -- -1 as before.
5443: -- Need to get input_value name for SOURCE_ID and SOURCE_NUMBER
5444: --
5445: hr_utility.trace('leg_code: '||leg_code);
5446: pay_core_utils.get_leg_context_iv_name('SOURCE_ID'
5447: ,leg_code
5448: ,l_src_iv
5449: ,l_iv_found);
5450: if (not l_iv_found) then
5451: l_src_iv := null;
5452: else
5453: l_si_needed := 'Y';
5454: hr_utility.trace('l_src_iv: '||l_src_iv);
5455: end if;
5456: --
5457: pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER'
5458: ,leg_code
5461: if (not l_num_found) then
5462: l_src_num := null;
5463: else
5464: l_sn_needed := 'Y';
5465: hr_utility.trace('l_src_num: '||l_src_num);
5466: end if;
5467: --
5468: pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER2'
5469: ,leg_code
5472: if (not l_num_found) then
5473: l_src_num2 := null;
5474: else
5475: l_sn2_needed := 'Y';
5476: hr_utility.trace('l_src_num2: '||l_src_num2);
5477: end if;
5478: --
5479: pay_core_utils.get_leg_context_iv_name('ORGANIZATION_ID'
5480: ,leg_code
5483: if (not l_num_found) then
5484: l_org_id_iv := null;
5485: else
5486: l_org_needed := 'Y';
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);
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,
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 ------------------------------
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
5705: hr_utility.set_location(c_indent, 10);
5706: end if;
5701: begin
5702: g_debug := hr_utility.debug_enabled;
5703: -- Insert un-processed run results and values as appropriate
5704: if g_debug then
5705: hr_utility.set_location(c_indent, 10);
5706: end if;
5707: for c1rec in c1 loop
5708: -- Start with insertion of run result.
5709: insert into pay_run_results (
5782: where paa.ASSIGNMENT_ACTION_ID = p_assignment_action_id
5783: and paa.PAYROLL_ACTION_ID = pa.PAYROLL_ACTION_ID;
5784: --
5785: begin
5786: g_debug := hr_utility.debug_enabled;
5787: --
5788: -- Open the cursor and retrieve the payroll action details
5789: if g_debug then
5790: hr_utility.set_location(c_indent,5);
5786: g_debug := hr_utility.debug_enabled;
5787: --
5788: -- Open the cursor and retrieve the payroll action details
5789: if g_debug then
5790: hr_utility.set_location(c_indent,5);
5791: end if;
5792: open C_CON1;
5793: fetch C_CON1 into l_payroll_action_id,
5794: l_assignment_action_id,
5797: close C_CON1;
5798: --
5799: -- insert the payroll action row
5800: if g_debug then
5801: hr_utility.set_location(c_indent,10);
5802: end if;
5803: insert into PAY_PAYROLL_ACTIONS
5804: (PAYROLL_ACTION_ID,
5805: ACTION_TYPE,
5826: p_reason);
5827: --
5828: -- call procedure to insert assignment action record
5829: if g_debug then
5830: hr_utility.set_location(c_indent,20);
5831: end if;
5832: inassact(pactid => l_payroll_action_id,
5833: asgid => p_assignment_id,
5834: p_ass_action_seq => l_assignment_action_id,
5836: p_serial_number => p_serial_number);
5837: --
5838: -- insert a pay action interlock record.
5839: if g_debug then
5840: hr_utility.set_location(c_indent,30);
5841: end if;
5842: insert into PAY_ACTION_INTERLOCKS
5843: (LOCKING_ACTION_ID,
5844: LOCKED_ACTION_ID)
5848: --
5849: -- Update the payroll action table with
5850: -- the appropriate date earned value.
5851: if g_debug then
5852: hr_utility.set_location(c_indent,40);
5853: end if;
5854: update pay_payroll_actions pac
5855: set pac.date_earned = (
5856: select pa2.date_earned
5890: c_indent constant varchar2(30) := 'hrassact.ext_man_payment2';
5891:
5892: BEGIN
5893:
5894: g_debug := hr_utility.debug_enabled;
5895:
5896: if g_debug then
5897: hr_utility.set_location('Entering '||c_indent,10);
5898: end if;
5893:
5894: g_debug := hr_utility.debug_enabled;
5895:
5896: if g_debug then
5897: hr_utility.set_location('Entering '||c_indent,10);
5898: end if;
5899:
5900: ext_man_payment(p_payroll_id,
5901: TRUNC(FND_DATE.canonical_to_date(P_EFF_DATE)),
5906: p_pre_payment_id,
5907: p_reason);
5908:
5909: if g_debug then
5910: hr_utility.set_location('Leaving '||c_indent,20);
5911: end if;
5912:
5913: END ext_man_payment;
5914: --------------------------- set_action_contexts------------------------
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: --
5972: if g_debug then
5973: hr_utility.set_location(c_indent,10);
5974: end if;
5969: begin
5970: g_debug := hr_utility.debug_enabled;
5971: --
5972: if g_debug then
5973: hr_utility.set_location(c_indent,10);
5974: end if;
5975: udca.sz := 0;
5976: pay_core_utils.get_dynamic_contexts(p_busgrp,
5977: g_dynamic_contexts);
5999: --
6000: begin
6001: --
6002: if g_debug then
6003: hr_utility.set_location(c_indent,20);
6004: end if;
6005: select 1
6006: into dummy
6007: from pay_legislation_rules plr
6009: and plr.rule_type = 'ACTION_CONTEXTS'
6010: and plr.rule_mode = 'Y';
6011: --
6012: if g_debug then
6013: hr_utility.set_location(c_indent,30);
6014: end if;
6015: action_contexts := TRUE;
6016: --
6017: exception
6015: action_contexts := TRUE;
6016: --
6017: exception
6018: when no_data_found then
6019: hr_utility.set_location(c_indent,40);
6020: action_contexts := FALSE;
6021: end;
6022: --
6023: if g_debug then
6020: action_contexts := FALSE;
6021: end;
6022: --
6023: if g_debug then
6024: hr_utility.set_location(c_indent,50);
6025: end if;
6026: --
6027: -- OK get the contexts cached
6028: --
6079: --
6080: for rrvrec in get_rr_values (p_rrid, l_effective_date) loop
6081: for l_cnt in 1..g_dynamic_contexts.count loop
6082: --
6083: hr_utility.set_location(c_indent,90);
6084: --
6085: --Run Result Contexts
6086: if (rrvrec.name = g_dynamic_contexts(l_cnt).input_value_name) then
6087: --
6084: --
6085: --Run Result Contexts
6086: if (rrvrec.name = g_dynamic_contexts(l_cnt).input_value_name) then
6087: --
6088: hr_utility.set_location(c_indent,95);
6089: declare
6090: l_ctx_value pay_run_result_values.result_value%type;
6091: begin
6092: l_ctx_value := null;
6138: end if;
6139: --
6140: end if;
6141:
6142: hr_utility.set_location(c_indent,96);
6143: --
6144: if l_ctx_value is not null then
6145: --
6146: -- Set the jurisdiction on the run_result.
6170: end;
6171: end if;
6172: --
6173: if g_debug then
6174: hr_utility.set_location(c_indent,100);
6175: end if;
6176: get_cache_context(g_dynamic_contexts(l_cnt).context_name, cxt_id);
6177: udca.sz := udca.sz + 1;
6178: udca.assact_id(udca.sz) := p_assact;
6183: udca.valid(udca.sz) := TRUE;
6184: end if;
6185: end;
6186: if g_debug then
6187: hr_utility.set_location(c_indent,105);
6188: end if;
6189: --
6190: end if;
6191: if g_debug then
6188: end if;
6189: --
6190: end if;
6191: if g_debug then
6192: hr_utility.set_location(c_indent,106);
6193: end if;
6194: end loop;
6195: end loop;
6196:
6197: --
6198: -- Payroll ID
6199: --
6200: if g_debug then
6201: hr_utility.set_location(c_indent,130);
6202: end if;
6203: get_cache_context('PAYROLL_ID', cxt_id);
6204: udca.sz := udca.sz + 1;
6205: udca.assact_id(udca.sz) := p_assact;
6213: -- Original Entry ID
6214: --
6215: if p_oentry is not null then
6216: if g_debug then
6217: hr_utility.set_location(c_indent,135);
6218: end if;
6219:
6220: get_cache_context('ORIGINAL_ENTRY_ID', cxt_id);
6221: udca.sz := udca.sz + 1;
6239: where run_result_id = p_rrid;
6240: --
6241: if l_local_unit_id is not null then
6242: if g_debug then
6243: hr_utility.set_location(c_indent,136);
6244: end if;
6245:
6246: get_cache_context('LOCAL_UNIT_ID', cxt_id);
6247: udca.sz := udca.sz + 1;
6257: --
6258: if (p_legcode = 'US' or p_legcode = 'CA') then
6259: --
6260: if g_debug then
6261: hr_utility.set_location(c_indent,140);
6262: end if;
6263: declare
6264: tax_group hr_organization_information.org_information5%type;
6265: asg_id number;
6267: begin
6268: --
6269: if (p_legcode = 'US') then
6270: if g_debug then
6271: hr_utility.set_location(c_indent,150);
6272: end if;
6273: select hoi.org_information5,
6274: paa.assignment_id
6275: into tax_group,
6281: and paa.assignment_action_id = p_assact
6282: and hoi.org_information5 is not null;
6283: else
6284: if g_debug then
6285: hr_utility.set_location(c_indent,153);
6286: end if;
6287: select hoi.org_information4,
6288: paa.assignment_id
6289: into tax_group,
6296: and hoi.org_information4 is not null;
6297: end if;
6298: --
6299: if g_debug then
6300: hr_utility.set_location(c_indent,155);
6301: end if;
6302: get_cache_context('TAX_GROUP', cxt_id);
6303: udca.sz := udca.sz + 1;
6304: udca.assact_id(udca.sz) := p_assact;
6310: --
6311: exception
6312: when no_data_found then
6313: if g_debug then
6314: hr_utility.set_location(c_indent,160);
6315: end if;
6316: null;
6317: end;
6318: --
6318: --
6319: end if;
6320: --
6321: if g_debug then
6322: hr_utility.set_location(c_indent,170);
6323: end if;
6324: -- Now do all the inserts.
6325: if action_contexts = TRUE then
6326: for cnt in 1..udca.sz loop
6334: ,'TIME_DEFINITION_ID'
6335: ,'TAX_GROUP'
6336: ,'ORIGINAL_ENTRY_ID')) then
6337: if g_debug then
6338: hr_utility.set_location(c_indent,180);
6339: end if;
6340: --
6341: -- The row could already be in the table due
6342: -- to batch balance adjustments
6361: --
6362: end loop;
6363: end if;
6364: if g_debug then
6365: hr_utility.set_location(c_indent,190);
6366: end if;
6367: exception
6368: when others then
6369: hr_utility.trace(sqlerrm);
6365: hr_utility.set_location(c_indent,190);
6366: end if;
6367: exception
6368: when others then
6369: hr_utility.trace(sqlerrm);
6370: raise;
6371: --
6372: end set_action_context;
6373: --
6422: and legislation_code = p_leg_code
6423: and rule_mode = 'Y';
6424: --
6425: begin
6426: g_debug := hr_utility.debug_enabled;
6427: --
6428: --
6429: -- Select a number of values, including assignment
6430: -- and payroll action details.
6441: -- attached to the record by the form or entry API before
6442: -- calling this process.
6443: --
6444: if g_debug then
6445: hr_utility.trace('effdate='||to_char(effdate, 'DD-MON-YYYY'));
6446: hr_utility.set_location(c_indent,5);
6447: end if;
6448: select /*+ ordered use_nl(pee asg ptp pbg)
6449: index(pee PAY_ELEMENT_ENTRIES_F_PK)
6442: -- calling this process.
6443: --
6444: if g_debug then
6445: hr_utility.trace('effdate='||to_char(effdate, 'DD-MON-YYYY'));
6446: hr_utility.set_location(c_indent,5);
6447: end if;
6448: select /*+ ordered use_nl(pee asg ptp pbg)
6449: index(pee PAY_ELEMENT_ENTRIES_F_PK)
6450: index(asg PER_ASSIGNMENTS_F_PK)
6502: end if;
6503: --
6504: -- insert payroll action row.
6505: if g_debug then
6506: hr_utility.set_location(c_indent,10);
6507: end if;
6508: insert into pay_payroll_actions (
6509: payroll_action_id,
6510: action_type,
6544: --
6545: -- We now need to get the id of the inserted action,
6546: -- so that it can be used for update purposes.
6547: if g_debug then
6548: hr_utility.set_location(c_indent,15);
6549: end if;
6550: select act.assignment_action_id, act.tax_unit_id
6551: into assactid, tax_unit
6552: from pay_assignment_actions act
6554: --
6555: --
6556: -- we need to insert the run result row
6557: if g_debug then
6558: hr_utility.set_location(c_indent,20);
6559: end if;
6560:
6561:
6562: -- calc jur code name
6626:
6627: --
6628: if(sql%notfound) then
6629: if g_debug then
6630: hr_utility.trace('Update of pay_run_results has failed');
6631: end if;
6632: raise no_data_found;
6633: end if;
6634: --
6635: -- Update the element entry creator_id column
6636: -- with the assignment_action_id of balance
6637: -- adjustment action.
6638: if g_debug then
6639: hr_utility.set_location(c_indent,25);
6640: end if;
6641: update pay_element_entries_f pee
6642: set pee.creator_id = assactid
6643: where pee.element_entry_id = eentryid;
6643: where pee.element_entry_id = eentryid;
6644: --
6645: if(sql%notfound) then
6646: if g_debug then
6647: hr_utility.trace('Update of pay_element_entries_f has failed');
6648: end if;
6649: raise no_data_found;
6650: end if;
6651: --
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
6721: hr_utility.set_location(c_indent,10);
6722: end if;
6717: begin
6718: g_debug := hr_utility.debug_enabled;
6719: --
6720: if g_debug then
6721: hr_utility.set_location(c_indent,10);
6722: end if;
6723: bal_adjust_actions(consetid, eentryid, effdate, l_pay_act_id,
6724: l_asg_act_id, act_type, prepay_flag,tax_unit_id,false,run_type_id);
6725: if g_debug then
6722: end if;
6723: bal_adjust_actions(consetid, eentryid, effdate, l_pay_act_id,
6724: l_asg_act_id, act_type, prepay_flag,tax_unit_id,false,run_type_id);
6725: if g_debug then
6726: hr_utility.set_location(c_indent,20);
6727: end if;
6728: end;
6729: --
6730: --------------------------- maintain_lat_bal ------------------------
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);
6900: end if;
6901: --
6902: l_context_string := '';
6903: for cnt in 1..udca.sz loop
6934: --
6935: begin
6936: --
6937: if g_debug then
6938: hr_utility.set_location (c_indent, 1);
6939: hr_utility.trace ('Feed checking code = ' || p_feed_checking_code);
6940: hr_utility.trace ('context string = ' || p_bal_context_str);
6941: end if;
6942: --
6935: begin
6936: --
6937: if g_debug then
6938: hr_utility.set_location (c_indent, 1);
6939: hr_utility.trace ('Feed checking code = ' || p_feed_checking_code);
6940: hr_utility.trace ('context string = ' || p_bal_context_str);
6941: end if;
6942: --
6943: -- we build up the sql string to call the balance
6936: --
6937: if g_debug then
6938: hr_utility.set_location (c_indent, 1);
6939: hr_utility.trace ('Feed checking code = ' || p_feed_checking_code);
6940: hr_utility.trace ('context string = ' || p_bal_context_str);
6941: end if;
6942: --
6943: -- we build up the sql string to call the balance
6944: -- feed checking pl/sql procedure:
6963: -- 5. Get the variable value (providing there are rows returned)
6964: -- 6. Close the dynamic sql cursor
6965: --
6966: if g_debug then
6967: hr_utility.set_location (c_indent, 20);
6968: end if;
6969: sql_cursor := dbms_sql.open_cursor; -- step 1
6970: --
6971: if g_debug then
6968: end if;
6969: sql_cursor := dbms_sql.open_cursor; -- step 1
6970: --
6971: if g_debug then
6972: hr_utility.set_location (c_indent, 25);
6973: end if;
6974: dbms_sql.parse(sql_cursor, l_feed_chk_str, dbms_sql.v7); -- step 2
6975: --
6976: if g_debug then
6973: end if;
6974: dbms_sql.parse(sql_cursor, l_feed_chk_str, dbms_sql.v7); -- step 2
6975: --
6976: if g_debug then
6977: hr_utility.set_location (c_indent, 30);
6978: end if;
6979: dbms_sql.bind_variable(sql_cursor, 'pactid', pactid); -- step 3:
6980: --
6981: dbms_sql.bind_variable(sql_cursor, 'assactid', assactid);
6990: --
6991: dbms_sql.bind_variable(sql_cursor, 'l_feed_flag', l_feed_flag);
6992: --
6993: if g_debug then
6994: hr_utility.set_location (c_indent, 35);
6995: end if;
6996: l_rows := dbms_sql.execute (sql_cursor); -- step 4
6997: --
6998: if (l_rows = 1) then
6996: l_rows := dbms_sql.execute (sql_cursor); -- step 4
6997: --
6998: if (l_rows = 1) then
6999: if g_debug then
7000: hr_utility.set_location (c_indent, 40);
7001: end if;
7002: dbms_sql.variable_value(sql_cursor, 'l_feed_flag', -- step 5
7003: l_feed_flag);
7004: --
7006: l_feed_balance := TRUE;
7007: end if;
7008: --
7009: if g_debug then
7010: hr_utility.set_location (c_indent, 45);
7011: end if;
7012: dbms_sql.close_cursor(sql_cursor); -- step 6
7013: else
7014: --
7015: -- None or more than 1 row has been returned. We must error as package
7016: -- call can only return 1 row, so this condition should never occur !
7017: --
7018: if g_debug then
7019: hr_utility.set_location (c_indent, 111);
7020: end if;
7021: dbms_sql.close_cursor(sql_cursor);
7022: hr_utility.raise_error;
7023: end if;
7018: if g_debug then
7019: hr_utility.set_location (c_indent, 111);
7020: end if;
7021: dbms_sql.close_cursor(sql_cursor);
7022: hr_utility.raise_error;
7023: end if;
7024: --
7025: return(l_feed_balance);
7026: --
7089: from pay_latest_balances
7090: where p_lat_bal_id = latest_balance_id;
7091:
7092: if g_debug then
7093: hr_utility.set_location (c_indent, 1);
7094: end if;
7095:
7096: l_feed_flag := TRUE;
7097: if udca.sz = 0 then
7357: --
7358: begin
7359: --
7360: if g_debug then
7361: hr_utility.set_location (c_indent, 1);
7362: end if;
7363: --
7364: l_feed_flag := TRUE;
7365:
7407: end loop;
7408:
7409: --
7410: if g_debug then
7411: hr_utility.set_location (c_indent, 20);
7412: end if;
7413: --
7414: -- jurisdiction_code check for 'J' feed checking type whereby
7415: -- jurisdiction_code must be undefined in the udca
7455: --
7456: begin
7457: --
7458: if g_debug then
7459: hr_utility.set_location (c_indent, 10);
7460: end if;
7461: --
7462: -- get balance dimension info for the latest balance
7463: --
7504: --
7505: if feed_checking_type is null then
7506: -- default checking type whereby always feed balance
7507: if g_debug then
7508: hr_utility.set_location (c_indent, 20);
7509: end if;
7510: feed_balance := TRUE;
7511:
7512: elsif feed_checking_type not in ('P', 'E', 'J') then
7515: -- recalculate it
7516: -- YET TO BE DECIDED
7517:
7518: if g_debug then
7519: hr_utility.set_location (c_indent, 30);
7520: end if;
7521:
7522: if lat_bal_type = 'A' then
7523: delete from pay_balance_context_values
7602: --
7603: begin
7604: --
7605: if g_debug then
7606: hr_utility.set_location (c_indent, 5);
7607: end if;
7608: if p_bal_owner_asg_actid = -9999 then
7609: l_expiry_date := to_date('01/01/1900', 'DD/MM/YYYY');
7610: p_bal_owner_eff_date := l_expiry_date;
7612: return(l_expiry_date);
7613: end if;
7614: --
7615: if g_debug then
7616: hr_utility.trace ('Expiry date fetching code = ' || p_expiry_date_code);
7617: hr_utility.set_location (c_indent||p_bal_owner_asg_actid, 7);
7618: end if;
7619: --
7620: -- Get the payroll_action_id and eff date for the latest balance
7613: end if;
7614: --
7615: if g_debug then
7616: hr_utility.trace ('Expiry date fetching code = ' || p_expiry_date_code);
7617: hr_utility.set_location (c_indent||p_bal_owner_asg_actid, 7);
7618: end if;
7619: --
7620: -- Get the payroll_action_id and eff date for the latest balance
7621: -- The expiry date returned is the last date in the period for
7633: -- Get the payroll_action_id and eff date for the adjustment if
7634: -- it is different
7635: --
7636: if g_debug then
7637: hr_utility.set_location (c_indent||p_ass_action_id, 10);
7638: end if;
7639: if p_ass_action_id <> p_bal_owner_asg_actid then
7640: select pay.payroll_action_id,
7641: pay.effective_date
7654: -- of beginning of time
7655: --
7656: if p_expiry_check_level = 'N' then
7657: if g_debug then
7658: hr_utility.set_location (c_indent, 12);
7659: end if;
7660: l_expiry_date := to_date('01/01/1900', 'DD/MM/YYYY');
7661:
7662: return(l_expiry_date);
7664: --
7665: -- expiry date procedure doesn't exist
7666: --
7667: if g_debug then
7668: hr_utility.set_location (c_indent, 14);
7669: end if;
7670: dbms_sql.close_cursor(sql_cursor);
7671: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
7672: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_date_code);
7667: if g_debug then
7668: hr_utility.set_location (c_indent, 14);
7669: end if;
7670: dbms_sql.close_cursor(sql_cursor);
7671: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
7672: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_date_code);
7673: hr_utility.raise_error;
7674: end if;
7675: --
7668: hr_utility.set_location (c_indent, 14);
7669: end if;
7670: dbms_sql.close_cursor(sql_cursor);
7671: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
7672: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_date_code);
7673: hr_utility.raise_error;
7674: end if;
7675: --
7676: -- we build up the sql string to call the balance
7669: end if;
7670: dbms_sql.close_cursor(sql_cursor);
7671: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
7672: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_date_code);
7673: hr_utility.raise_error;
7674: end if;
7675: --
7676: -- we build up the sql string to call the balance
7677: -- feed checking pl/sql procedure:
7702: -- 5. Get the variable value (providing there are rows returned)
7703: -- 6. Close the dynamic sql cursor
7704: --
7705: if g_debug then
7706: hr_utility.set_location (c_indent, 20);
7707: end if;
7708: sql_cursor := dbms_sql.open_cursor; -- step 1
7709: --
7710: if g_debug then
7707: end if;
7708: sql_cursor := dbms_sql.open_cursor; -- step 1
7709: --
7710: if g_debug then
7711: hr_utility.set_location (c_indent, 25);
7712: end if;
7713: dbms_sql.parse(sql_cursor, l_expiry_chk_str, dbms_sql.v7); -- step 2
7714: --
7715: if g_debug then
7712: end if;
7713: dbms_sql.parse(sql_cursor, l_expiry_chk_str, dbms_sql.v7); -- step 2
7714: --
7715: if g_debug then
7716: hr_utility.set_location (c_indent||to_char(p_bal_owner_eff_date, 'DD/MM/YYYY'), 30);
7717: end if;
7718: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_pay_action', l_bal_owner_pay_action); -- step 3:
7719: --
7720: dbms_sql.bind_variable(sql_cursor, 'l_payroll_action', l_payroll_action);
7735: --
7736: dbms_sql.bind_variable(sql_cursor, 'l_expiry_date', l_expiry_date);
7737: --
7738: if g_debug then
7739: hr_utility.set_location (c_indent, 35);
7740: end if;
7741: l_rows := dbms_sql.execute (sql_cursor); -- step 4
7742: --
7743: if (l_rows = 1) then
7741: l_rows := dbms_sql.execute (sql_cursor); -- step 4
7742: --
7743: if (l_rows = 1) then
7744: if g_debug then
7745: hr_utility.set_location (c_indent, 40);
7746: end if;
7747: dbms_sql.variable_value(sql_cursor, 'l_expiry_date', -- step 5
7748: l_expiry_date);
7749: --
7747: dbms_sql.variable_value(sql_cursor, 'l_expiry_date', -- step 5
7748: l_expiry_date);
7749: --
7750: if g_debug then
7751: hr_utility.set_location (c_indent||to_char(l_expiry_date, 'DD/MM/YYYY'), 45);
7752: end if;
7753: dbms_sql.close_cursor(sql_cursor); -- step 6
7754: elsif (l_rows = 0) then
7755: --
7755: --
7756: -- expiry date procedure didn't exist
7757: --
7758: if g_debug then
7759: hr_utility.set_location (c_indent, 50);
7760: end if;
7761: dbms_sql.close_cursor(sql_cursor);
7762: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
7763: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_date_code);
7758: if g_debug then
7759: hr_utility.set_location (c_indent, 50);
7760: end if;
7761: dbms_sql.close_cursor(sql_cursor);
7762: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
7763: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_date_code);
7764: hr_utility.raise_error;
7765: else
7766: --
7759: hr_utility.set_location (c_indent, 50);
7760: end if;
7761: dbms_sql.close_cursor(sql_cursor);
7762: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
7763: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_date_code);
7764: hr_utility.raise_error;
7765: else
7766: --
7767: -- None or more than 1 row has been returned. We must error as package
7760: end if;
7761: dbms_sql.close_cursor(sql_cursor);
7762: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
7763: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_date_code);
7764: hr_utility.raise_error;
7765: else
7766: --
7767: -- None or more than 1 row has been returned. We must error as package
7768: -- call can only return 1 row, so this condition should never occur !
7767: -- None or more than 1 row has been returned. We must error as package
7768: -- call can only return 1 row, so this condition should never occur !
7769: --
7770: if g_debug then
7771: hr_utility.set_location (c_indent, 60);
7772: end if;
7773: dbms_sql.close_cursor(sql_cursor);
7774: hr_utility.raise_error;
7775: end if;
7770: if g_debug then
7771: hr_utility.set_location (c_indent, 60);
7772: end if;
7773: dbms_sql.close_cursor(sql_cursor);
7774: hr_utility.raise_error;
7775: end if;
7776: --
7777: return(l_expiry_date);
7778: --
7817: --
7818: begin
7819: --
7820: if g_debug then
7821: hr_utility.set_location (c_indent, 10);
7822: end if;
7823: --
7824: l_change_flag := TRUE;
7825: --
7829: if assignment_action_id = assactid or
7830: expired_asg_action_id = assactid then
7831:
7832: if g_debug then
7833: hr_utility.set_location (c_indent, 15);
7834: end if;
7835:
7836: if assignment_action_id = assactid then
7837: value := value + (result_value * feed_scale);
7856: p_expiry_check_level, p_bal_context_str,
7857: bal_adj_eff_date);
7858: --
7859: if g_debug then
7860: hr_utility.set_location (c_indent, 20);
7861: end if;
7862: --
7863: -- Get expiry date for latest balance
7864: --
7867: p_expiry_check_level, p_bal_context_str,
7868: l_bal_owner_eff_date);
7869: --
7870: if g_debug then
7871: hr_utility.set_location (c_indent||p_dimension_name, 30);
7872: end if;
7873:
7874: if bal_adj_exp_date > lat_bal_exp_date then
7875: -- balance adjustment expiry date is later than the latest balance
7877: -- to the previous latest balance and expired latest balance, and set
7878: -- the latest balance to be the balance adjustment
7879:
7880: if g_debug then
7881: hr_utility.set_location (c_indent, 40);
7882: end if;
7883:
7884: prev_balance_value := value;
7885: prev_asg_action_id := assignment_action_id;
7896: -- balance adjustment expiry date = latest balance expiry date
7897: -- so feed latest balance and possibly previous latest balance
7898:
7899: if g_debug then
7900: hr_utility.set_location (c_indent, 50);
7901: end if;
7902:
7903: if bal_adj_eff_date >= l_bal_owner_eff_date then
7904: -- balance adjustment effective date is later than the latest balance
7906: -- balance and adjust the latest balance value and its asg action id by
7907: -- the adjustment
7908:
7909: if g_debug then
7910: hr_utility.set_location (c_indent, 60);
7911: end if;
7912:
7913: prev_balance_value := value;
7914: prev_asg_action_id := assignment_action_id;
7924: -- action id), and then see if we need to adjust the previous latest
7925: -- balance
7926:
7927: if g_debug then
7928: hr_utility.set_location (c_indent, 70);
7929: end if;
7930:
7931: value := value + (result_value * feed_scale);
7932: expiry_date := bal_adj_exp_date;
7940: if bal_adj_exp_date = prev_bal_exp_date then
7941: -- need to adjust previous balance as in same period as balance adjustment
7942:
7943: if g_debug then
7944: hr_utility.set_location (c_indent, 80);
7945: end if;
7946:
7947: if bal_adj_eff_date >= p_bal_owner_eff_date then
7948: -- as balance adjustment effective date is later than previous latest
7949: -- balance effective date we also need to amend previous latest
7950: -- balance asg action id
7951:
7952: if g_debug then
7953: hr_utility.set_location (c_indent, 90);
7954: end if;
7955:
7956: prev_balance_value := prev_balance_value + (result_value * feed_scale);
7957: prev_asg_action_id := assactid;
7957: prev_asg_action_id := assactid;
7958: prev_expiry_date := bal_adj_exp_date;
7959: else
7960: if g_debug then
7961: hr_utility.set_location (c_indent, 100);
7962: end if;
7963:
7964: prev_balance_value := prev_balance_value + (result_value * feed_scale);
7965: prev_expiry_date := prev_bal_exp_date;
7970: -- so replace previous latest balance value and its asg action id by
7971: -- the balance adjustment
7972:
7973: if g_debug then
7974: hr_utility.set_location (c_indent, 110);
7975: end if;
7976: prev_balance_value := result_value * feed_scale;
7977: prev_asg_action_id := assactid;
7978: prev_expiry_date := bal_adj_exp_date;
7980: else
7981: -- previous balance value is undefined (ie null or -9999)
7982:
7983: if g_debug then
7984: hr_utility.set_location (c_indent, 115);
7985: end if;
7986:
7987: end if;
7988: end if;
7990: else
7991: -- balance adjusmtent expiry date <> latest balance expiry date
7992: -- so may need to adjust expired and previous latest balances
7993: if g_debug then
7994: hr_utility.set_location (c_indent, 120);
7995: end if;
7996:
7997: -- Get expiry date for expired latest balance
7998: exp_bal_exp_date := get_expiry_date(p_expiry_date_code, expired_asg_action_id,
8004: -- then adjust expired balance and possibly previous latest balance
8005: if bal_adj_exp_date = exp_bal_exp_date then
8006:
8007: if g_debug then
8008: hr_utility.set_location (c_indent, 130);
8009: end if;
8010:
8011: if bal_adj_eff_date >= e_bal_owner_eff_date then
8012: -- balance adjustment effective date is later than the expired balance
8016: -- before doing so we see if the previous latest balance is from the
8017: -- same assignment action id and hence also needs adjusting
8018:
8019: if g_debug then
8020: hr_utility.set_location (c_indent, 140);
8021: end if;
8022:
8023: if expired_asg_action_id = prev_asg_action_id then
8024: -- adjust the previous latest balance
8022:
8023: if expired_asg_action_id = prev_asg_action_id then
8024: -- adjust the previous latest balance
8025: if g_debug then
8026: hr_utility.set_location (c_indent, 150);
8027: end if;
8028:
8029: prev_balance_value := prev_balance_value + (result_value * feed_scale);
8030: prev_asg_action_id := assactid;
8043: -- before doing so we see if the previous latest balance is from the
8044: -- same assignment action id and hence also needs adjusting
8045:
8046: if g_debug then
8047: hr_utility.set_location (c_indent, 160);
8048: end if;
8049:
8050: if expired_asg_action_id = prev_asg_action_id then
8051: -- adjust the previous latest balance
8049:
8050: if expired_asg_action_id = prev_asg_action_id then
8051: -- adjust the previous latest balance
8052: if g_debug then
8053: hr_utility.set_location (c_indent||prev_balance_value, 170);
8054: end if;
8055:
8056: prev_balance_value := prev_balance_value + (result_value * feed_scale);
8057: prev_expiry_date := exp_bal_exp_date;
8055:
8056: prev_balance_value := prev_balance_value + (result_value * feed_scale);
8057: prev_expiry_date := exp_bal_exp_date;
8058: if g_debug then
8059: hr_utility.set_location (c_indent||prev_balance_value, 170);
8060: end if;
8061: end if;
8062:
8063: if g_debug then
8060: end if;
8061: end if;
8062:
8063: if g_debug then
8064: hr_utility.set_location (c_indent||expired_value, 170);
8065: end if;
8066: expired_value := expired_value + (result_value * feed_scale);
8067: expired_date := exp_bal_exp_date;
8068: if g_debug then
8065: end if;
8066: expired_value := expired_value + (result_value * feed_scale);
8067: expired_date := exp_bal_exp_date;
8068: if g_debug then
8069: hr_utility.set_location (c_indent||expired_value, 170);
8070: end if;
8071:
8072: end if;
8073:
8079: -- which case we would want to adjust the expired, or it may be
8080: -- ealier than the expired.
8081: --
8082: if g_debug then
8083: hr_utility.set_location (c_indent, 180);
8084: end if;
8085:
8086: if expired_asg_action_id <> -9999 and
8087: bal_adj_exp_date > exp_bal_exp_date then
8087: bal_adj_exp_date > exp_bal_exp_date then
8088: -- it is newer so we need to replace the expired (and possibly previous)
8089:
8090: if g_debug then
8091: hr_utility.set_location (c_indent, 190);
8092: end if;
8093:
8094: if expired_asg_action_id = prev_asg_action_id then
8095: -- adjust the previous latest balance
8093:
8094: if expired_asg_action_id = prev_asg_action_id then
8095: -- adjust the previous latest balance
8096: if g_debug then
8097: hr_utility.set_location (c_indent, 200);
8098: end if;
8099:
8100: prev_balance_value := result_value * feed_scale;
8101: prev_asg_action_id := assactid;
8110: -- the balance adjustment is in an older period than the expired
8111: -- latest balance and therefore does not feed it
8112:
8113: if g_debug then
8114: hr_utility.set_location (c_indent, 210);
8115: end if;
8116:
8117: l_change_flag := FALSE;
8118:
8125: --
8126: end feed_balance;
8127: --
8128: begin
8129: g_debug := hr_utility.debug_enabled;
8130: --
8131: if g_debug then
8132: hr_utility.set_location(c_indent,10);
8133: end if;
8128: begin
8129: g_debug := hr_utility.debug_enabled;
8130: --
8131: if g_debug then
8132: hr_utility.set_location(c_indent,10);
8133: end if;
8134: --
8135: -- initialise balance dimensions cache
8136: --
8156: and effdate between pera.effective_start_date
8157: and pera.effective_end_date;
8158: --
8159: if g_debug then
8160: hr_utility.set_location(c_indent,20);
8161: end if;
8162: --
8163: if act_type = 'B' then
8164: --
8198: if not_supported = TRUE then
8199: --
8200: -- delete latest balances
8201: if g_debug then
8202: hr_utility.set_location(c_indent,25);
8203: end if;
8204: --
8205: -- Derive element type id from the run result.
8206: --
8217: cache_contexts;
8218: end if;
8219: --
8220: if g_debug then
8221: hr_utility.set_location(c_indent,30);
8222: end if;
8223: --
8224: for rrv in run_result_values loop
8225:
8296: --
8297: else
8298: -- feed check person latest balances
8299: for lb in fed_latest_balances(rrv.input_value_id, l_person_id, asgid) loop
8300: hr_utility.set_location(c_indent,100);
8301: bal_fed := feed_check(lb.latest_balance_id, 'L', lb.balance_dimension_id,
8302: lb.balance_type_id, bal_dim_name,
8303: balance_expiry_code, balance_expiry_level,
8304: bal_context_string, udca);
8382: when no_data_found then
8383: return -9999;
8384:
8385: when too_many_rows then
8386: hr_utility.set_message(801,'HR_34864_INTERLOCK_ERR_REV');
8387: hr_utility.raise_error;
8388:
8389: end;
8390:
8383: return -9999;
8384:
8385: when too_many_rows then
8386: hr_utility.set_message(801,'HR_34864_INTERLOCK_ERR_REV');
8387: hr_utility.raise_error;
8388:
8389: end;
8390:
8391: exception