[Home] [Help]
1: package body payplnk as
2: /* $Header: paylink.pkb 120.0.12010000.3 2008/10/03 08:42:10 ankagarw ship $ */
3: --
4: --
5: -- GLOBAL DECLARATIONS
227: --
228: --
229: -----------------------------------------------------------------------
230: -- NAME --
231: -- payplnk.run_process --
232: -- --
233: -- DESCRIPTION --
234: -- The main procedure caled from the SRS screen, from which the --
235: -- paylink process is triggered for a particular batch. --
301: --
302: -- Depending on the mode of operation requested the particular batch
303: -- will be processed accordingly.
304: --
305: hr_utility.set_location('payplnk.run_process',5);
306: --
307: if (p_batch_operation = 'VALIDATE' OR p_batch_operation = 'TRANSFER') then
308: --
309: -- validate the batch.
307: if (p_batch_operation = 'VALIDATE' OR p_batch_operation = 'TRANSFER') then
308: --
309: -- validate the batch.
310: --
311: hr_utility.set_location('payplnk.run_process',6);
312: --
313: g_header_processing := TRUE;
314: --
315: payplnk.validate
311: hr_utility.set_location('payplnk.run_process',6);
312: --
313: g_header_processing := TRUE;
314: --
315: payplnk.validate
316: (
317: p_business_group_id,
318: p_batch_operation,
319: p_batch_id
322: set_header_status(p_business_group_id,p_batch_operation,p_batch_id);
323: --
324: g_header_processing := FALSE;
325: --
326: payplnk.g_payplnk_call := TRUE;
327: --
328: hr_utility.set_location('payplnk.run_process',7);
329: --
330: -- elsif p_batch_operation = 'TRANSFER' then
324: g_header_processing := FALSE;
325: --
326: payplnk.g_payplnk_call := TRUE;
327: --
328: hr_utility.set_location('payplnk.run_process',7);
329: --
330: -- elsif p_batch_operation = 'TRANSFER' then
331: -- --
332: -- -- transfer the batch. NB The transfer procedure calls the
333: -- -- validate procedure to ensure the batch is valid before
334: -- -- tansferring the element entries into the appropriate
335: -- -- base tables.
336: -- --
337: -- hr_utility.set_location('payplnk.run_process',8);
338: -- payplnk.transfer
339: -- (
340: -- p_business_group_id,
341: -- p_batch_operation,
334: -- -- tansferring the element entries into the appropriate
335: -- -- base tables.
336: -- --
337: -- hr_utility.set_location('payplnk.run_process',8);
338: -- payplnk.transfer
339: -- (
340: -- p_business_group_id,
341: -- p_batch_operation,
342: -- p_batch_id
340: -- p_business_group_id,
341: -- p_batch_operation,
342: -- p_batch_id
343: -- );
344: -- hr_utility.set_location('payplnk.run_process',9);
345: --
346: elsif p_batch_operation = 'PURGE' then
347: --
348: -- purge the batch. All records associated with the batch i.e.
348: -- purge the batch. All records associated with the batch i.e.
349: -- batch messages, controls, lines and header will be deleted
350: -- from the temporary batch tables.
351: --
352: hr_utility.set_location('payplnk.run_process',10);
353: payplnk.purge
354: (
355: p_batch_id
356: );
349: -- batch messages, controls, lines and header will be deleted
350: -- from the temporary batch tables.
351: --
352: hr_utility.set_location('payplnk.run_process',10);
353: payplnk.purge
354: (
355: p_batch_id
356: );
357: hr_utility.set_location('payplnk.run_process',11);
353: payplnk.purge
354: (
355: p_batch_id
356: );
357: hr_utility.set_location('payplnk.run_process',11);
358: --
359: end if;
360: --
361: hr_utility.set_location('payplnk.run_process',15);
357: hr_utility.set_location('payplnk.run_process',11);
358: --
359: end if;
360: --
361: hr_utility.set_location('payplnk.run_process',15);
362: --
363: -- commit;
364: --
365: errbuf := null;
380: end run_process;
381: --
382: ------------------------------------------------------------------------
383: -- NAME --
384: -- payplnk.validate --
385: -- --
386: -- DESCRIPTION --
387: -- Given a batch id it will validate the batch and insert its --
388: -- element entries into the necessary base tables. Then depending on --
421: not_upper boolean := false;
422: --
423: begin
424: --
425: hr_utility.set_location('payplnk.validate',1);
426: --
427: SAVEPOINT VL;
428: --
429: hr_utility.set_location('payplnk.validate',5);
425: hr_utility.set_location('payplnk.validate',1);
426: --
427: SAVEPOINT VL;
428: --
429: hr_utility.set_location('payplnk.validate',5);
430: --
431: select legislation_code
432: into l_legislation_code
433: from per_business_groups_perf
537: close csr_all_controls;
538: end if;
539: end if;
540: --
541: hr_utility.set_location('payplnk.validate',10);
542: --
543: -- Any updates made to the batch header and control statuses, and
544: -- and messages resulting from the header and control checks will be
545: -- committed.
571: -- raise error_occurred;
572: end if;
573: end if;
574: --
575: hr_utility.set_location('payplnk.validate',15);
576: -- The following is comment out due to multithreading.
577: -- --
578: -- -- Successfully validated line(s) may as a result have created
579: -- -- element entrie(s) in the base tables, therefore the entries must be
596: -- else
597: -- commit;
598: -- end if;
599: -- --
600: -- hr_utility.set_location('payplnk.validate',20);
601: -- --
602: -- -- Any messages accumulated during batch lines validation are now
603: -- -- inserted into the pay_message_lines table.
604: -- --
627: -- when no_data_found then
628: -- null;
629: -- end;
630: -- --
631: -- hr_utility.set_location('payplnk.validate',25);
632: -- --
633: -- -- The status of each batch line is set according to the outcome
634: -- -- of the validation carried out for the particular line.
635: -- --
655: -- when no_data_found then
656: -- null;
657: -- end;
658: -- --
659: -- hr_utility.set_location('payplnk.validate',30);
660: -- --
661: -- if l_line_match = false then
662: -- --
663: -- update pay_batch_lines bal
668: -- end if;
669: -- end loop;
670: -- --
671: -- End of the code blocked for multithreading.
672: hr_utility.set_location('payplnk.validate',35);
673: --
674: for g_line_record in csr_all_lines(p_batch_id) loop
675: --
676: if g_line_record.effective_date is null then
822: where current of csr_all_lines;
823: --
824: end;
825: --
826: hr_utility.set_location('payplnk.validate',40);
827: --
828: elsif (g_line_record.assignment_id is null) and
829: (g_line_record.assignment_number is null) then
830: --
852: set batch_line_status = 'E'
853: where current of csr_all_lines;
854: --
855: --
856: hr_utility.set_location('payplnk.validate',45);
857: --
858: elsif (g_line_record.assignment_id is not null) and
859: (g_line_record.assignment_number is not null) then
860: begin
919: where current of csr_all_lines;
920: --
921: end;
922: --
923: hr_utility.set_location('payplnk.validate',50);
924: --
925: -- If only an assignment id has been entered, ensure that it is valid.
926: --
927: else
964: end;
965: --
966: end if;
967: --
968: hr_utility.set_location('payplnk.validate',55);
969: --
970: end if;
971: --
972: end loop;
970: end if;
971: --
972: end loop;
973: --
974: hr_utility.set_location('payplnk.validate',60);
975: --
976: -- COMMIT;
977: --
978: --
975: --
976: -- COMMIT;
977: --
978: --
979: hr_utility.set_location('payplnk.validate',85);
980: --
981: exception
982: when error_occurred then
983: rollback to VL;
991: end validate;
992: --
993: -----------------------------------------------------------------------
994: -- NAME --
995: -- payplnk.set_status --
996: -- --
997: -- DESCRIPTION --
998: -- Given a batch id and the mode of operation it will set the status --
999: -- of the batch and control totals. --
1036: where pbh.batch_id = p_batch_id;
1037: --
1038: begin
1039: --
1040: hr_utility.set_location('payplnk.set_status',5);
1041: --
1042: SAVEPOINT SS;
1043: --
1044: g_line_error := false;
1082: close csr_error_header;
1083: --
1084:
1085: if (g_header_error = true) then
1086: hr_utility.trace('payplnk.set_status g_header_error = TRUE');
1087: else
1088: hr_utility.trace('payplnk.set_status g_header_error = FALSE');
1089: end if;
1090:
1084:
1085: if (g_header_error = true) then
1086: hr_utility.trace('payplnk.set_status g_header_error = TRUE');
1087: else
1088: hr_utility.trace('payplnk.set_status g_header_error = FALSE');
1089: end if;
1090:
1091: if (g_control_error = true) then
1092: hr_utility.trace('payplnk.set_status g_control_error = TRUE');
1088: hr_utility.trace('payplnk.set_status g_header_error = FALSE');
1089: end if;
1090:
1091: if (g_control_error = true) then
1092: hr_utility.trace('payplnk.set_status g_control_error = TRUE');
1093: else
1094: hr_utility.trace('payplnk.set_status g_control_error = FALSE');
1095: end if;
1096:
1090:
1091: if (g_control_error = true) then
1092: hr_utility.trace('payplnk.set_status g_control_error = TRUE');
1093: else
1094: hr_utility.trace('payplnk.set_status g_control_error = FALSE');
1095: end if;
1096:
1097: if (g_line_error = true) then
1098: hr_utility.trace('payplnk.set_status g_line_error = TRUE');
1094: hr_utility.trace('payplnk.set_status g_control_error = FALSE');
1095: end if;
1096:
1097: if (g_line_error = true) then
1098: hr_utility.trace('payplnk.set_status g_line_error = TRUE');
1099: else
1100: hr_utility.trace('payplnk.set_status g_line_error = FALSE');
1101: end if;
1102: --
1096:
1097: if (g_line_error = true) then
1098: hr_utility.trace('payplnk.set_status g_line_error = TRUE');
1099: else
1100: hr_utility.trace('payplnk.set_status g_line_error = FALSE');
1101: end if;
1102: --
1103: if (p_batch_operation = 'VALIDATE' and g_header_error = false) then
1104: --
1113: -- Once the element entries have been committed check if the
1114: -- PURGE_AFTER_TRANSFER flag is 'Y'. If so delete all records associated
1115: -- with the batch from the temporary batch tables.
1116: --
1117: hr_utility.set_location('payplnk.set_status',11);
1118: --
1119: if (g_line_error = false) and
1120: upper(l_purge_after_transfer) = 'Y' then
1121: hr_utility.set_location('payplnk.set_status',30);
1117: hr_utility.set_location('payplnk.set_status',11);
1118: --
1119: if (g_line_error = false) and
1120: upper(l_purge_after_transfer) = 'Y' then
1121: hr_utility.set_location('payplnk.set_status',30);
1122: purge (
1123: p_batch_id
1124: );
1125: --
1124: );
1125: --
1126: else
1127: --
1128: hr_utility.set_location('payplnk.set_status',15);
1129: --
1130: if g_control_error = false then
1131: for g_control_record in csr_all_controls(p_batch_id) loop
1132: update pay_batch_control_totals
1135: --
1136: end loop;
1137: end if;
1138: --
1139: hr_utility.set_location('payplnk.set_status',20);
1140: --
1141: update pay_batch_headers
1142: set batch_status = l_status
1143: where batch_id = p_batch_id;
1141: update pay_batch_headers
1142: set batch_status = l_status
1143: where batch_id = p_batch_id;
1144: --
1145: hr_utility.set_location('payplnk.set_status',25);
1146: --
1147: end if;
1148: --
1149: hr_utility.set_location('payplnk.set_status',30);
1145: hr_utility.set_location('payplnk.set_status',25);
1146: --
1147: end if;
1148: --
1149: hr_utility.set_location('payplnk.set_status',30);
1150: end if;
1151: --
1152: end if;
1153: --
1150: end if;
1151: --
1152: end if;
1153: --
1154: hr_utility.set_location('payplnk.set_status',35);
1155: --
1156: exception
1157: when others then
1158: rollback to SS;
1160: end set_status;
1161: --
1162: -----------------------------------------------------------------------
1163: -- NAME --
1164: -- payplnk.set_line_status --
1165: -- --
1166: -- DESCRIPTION --
1167: -- Given a batch id and the mode of operation it will set the status --
1168: -- of the batch lines. --
1182: --
1183: --
1184: begin
1185: --
1186: hr_utility.set_location('payplnk.set_line_status',5);
1187: --
1188: -- Set the global parameter to disable the triggers.
1189: payplnk.g_payplnk_call := true;
1190: --
1185: --
1186: hr_utility.set_location('payplnk.set_line_status',5);
1187: --
1188: -- Set the global parameter to disable the triggers.
1189: payplnk.g_payplnk_call := true;
1190: --
1191: SAVEPOINT SLS;
1192: --
1193: --
1198: else
1199: l_batch_line_status := 'T';
1200: end if;
1201: --
1202: hr_utility.set_location('payplnk.set_line_status',10);
1203: --
1204: -- Any messages accumulated during batch lines validation are now
1205: -- inserted into the pay_message_lines table.
1206: --
1229: when no_data_found then
1230: null;
1231: end;
1232: --
1233: hr_utility.set_location('payplnk.set_line_status',20);
1234: --
1235: -- The status of each batch line is set according to the outcome
1236: -- of the validation carried out for the particular line.
1237: --
1264: when no_data_found then
1265: null;
1266: end;
1267: --
1268: hr_utility.set_location('payplnk.set_line_status',30);
1269: --
1270: if l_line_match = false then
1271: --
1272: update pay_batch_lines bal
1284: g_status_tbl.delete;
1285: g_message_tbl.delete;
1286: --
1287: -- Set the global parameter to enable the triggers.
1288: payplnk.g_payplnk_call := false;
1289: --
1290: --
1291: hr_utility.set_location('payplnk.set_line_status',35);
1292: --
1287: -- Set the global parameter to enable the triggers.
1288: payplnk.g_payplnk_call := false;
1289: --
1290: --
1291: hr_utility.set_location('payplnk.set_line_status',35);
1292: --
1293: exception
1294: when others then
1295: rollback to SLS;
1297: end set_line_status;
1298: --
1299: -----------------------------------------------------------------------
1300: -- NAME --
1301: -- payplnk.set_header_status --
1302: -- --
1303: -- DESCRIPTION --
1304: -- Given a batch id and the mode of operation it will set the status --
1305: -- of the batch lines. --
1317: --
1318: --
1319: begin
1320: --
1321: hr_utility.set_location('payplnk.set_header_status',5);
1322: --
1323: -- Set the global parameter to disable the triggers.
1324: payplnk.g_payplnk_call := true;
1325: --
1320: --
1321: hr_utility.set_location('payplnk.set_header_status',5);
1322: --
1323: -- Set the global parameter to disable the triggers.
1324: payplnk.g_payplnk_call := true;
1325: --
1326: SAVEPOINT SHS;
1327: --
1328: if g_header_processing then
1350: end if;
1351: end if;
1352: --
1353: --
1354: hr_utility.set_location('payplnk.set_header_status',10);
1355: --
1356: -- Only update the status of the cotnrol totals if the batc his not failed.
1357: if g_head_err_stat <> 'F' or g_head_err_stat is null then
1358: --
1384: when no_data_found then
1385: null;
1386: end;
1387: --
1388: hr_utility.set_location('payplnk.set_header_status',20);
1389: --
1390: -- The status of each batch total is set according to the outcome
1391: -- of the validation carried out for the particular total.
1392: --
1411: when no_data_found then
1412: null;
1413: end;
1414: --
1415: hr_utility.set_location('payplnk.set_header_status',30);
1416: --
1417: if l_line_match = false then
1418: --
1419: update pay_batch_control_totals ctl
1459: g_head_err_msg := null;
1460: g_header_processing := false;
1461: --
1462: -- Set the global parameter to enable the triggers.
1463: payplnk.g_payplnk_call := false;
1464: --
1465: --
1466: hr_utility.set_location('payplnk.set_header_status',35);
1467: --
1462: -- Set the global parameter to enable the triggers.
1463: payplnk.g_payplnk_call := false;
1464: --
1465: --
1466: hr_utility.set_location('payplnk.set_header_status',35);
1467: --
1468: exception
1469: when others then
1470: rollback to SHS;
1473: --
1474: --
1475: -----------------------------------------------------------------------
1476: -- NAME --
1477: -- payplnk.purge --
1478: -- --
1479: -- DESCRIPTION --
1480: -- Given a batch id it will delete all records associated with the --
1481: -- batch from all the temporary batch tables. Any messages --
1497: --
1498: begin
1499: --
1500: SAVEPOINT PU;
1501: hr_utility.set_location('payplnk.purge',5);
1502: --
1503: open csr_ovn;
1504: fetch csr_ovn into l_ovn;
1505: close csr_ovn;
1504: fetch csr_ovn into l_ovn;
1505: close csr_ovn;
1506: --
1507: -- Set the global parameter to enable the triggers.
1508: payplnk.g_payplnk_call := true;
1509: --
1510: --
1511: --
1512: -- Calls the BEE API to delete the batch.
1516: ,p_object_version_number => l_ovn
1517: );
1518: --
1519: -- Set the global parameter to enable the triggers.
1520: payplnk.g_payplnk_call := false;
1521: --
1522: --
1523: hr_utility.set_location('payplnk.purge',8);
1524: --
1519: -- Set the global parameter to enable the triggers.
1520: payplnk.g_payplnk_call := false;
1521: --
1522: --
1523: hr_utility.set_location('payplnk.purge',8);
1524: --
1525: --
1526: /*
1527: purge_messages(p_batch_id,'Y');
1525: --
1526: /*
1527: purge_messages(p_batch_id,'Y');
1528: --
1529: hr_utility.set_location('payplnk.purge',10);
1530: --
1531: delete from pay_batch_control_totals
1532: where batch_id = p_batch_id;
1533: --
1530: --
1531: delete from pay_batch_control_totals
1532: where batch_id = p_batch_id;
1533: --
1534: hr_utility.set_location('payplnk.purge',15);
1535: --
1536: delete from pay_batch_lines
1537: where batch_id = p_batch_id;
1538: --
1535: --
1536: delete from pay_batch_lines
1537: where batch_id = p_batch_id;
1538: --
1539: hr_utility.set_location('payplnk.purge',20);
1540: --
1541: delete from pay_batch_headers
1542: where batch_id = p_batch_id;
1543: --
1540: --
1541: delete from pay_batch_headers
1542: where batch_id = p_batch_id;
1543: --
1544: hr_utility.set_location('payplnk.purge',25);
1545: --
1546: */
1547: --
1548: exception
1553: end purge;
1554: --
1555: ----------------------------------------------------------------------
1556: -- NAME --
1557: -- payplnk.validate_header PRIVATE PROCEDURE --
1558: -- --
1559: -- DESCRIPTION --
1560: -- Validates batch header details. This includes the user defined --
1561: -- checks for the header as well as the core validation checks --
1595: pragma exception_init(nopackbody,-6508);
1596: --
1597: begin
1598: --
1599: hr_utility.set_location('payplnk.validate_header',5);
1600: --
1601: select legislation_code
1602: into l_legislation_code
1603: from per_business_groups_perf
1616: SAVEPOINT BH;
1617: --
1618: -- commit;
1619: --
1620: hr_utility.set_location('payplnk.validate_header',10);
1621: --
1622: -- Check if transferred record(s) exist in the batch, if so, terminate
1623: -- validation with an appropriate message.
1624: --
1641: when no_data_found then
1642: null;
1643: end;
1644: --
1645: hr_utility.set_location('payplnk.validate_header',15);
1646: --
1647: if l_transfer_recs = 'x' then
1648: --
1649: -- Atleast one transferred record has been detected in the batch,
1675: --
1676: raise core_error;
1677: end if;
1678: --
1679: hr_utility.set_location('payplnk.validate_header',20);
1680: --
1681: -- The core validation checks will be carried out first.
1682: -- Check the batch status is not already PROCESSING.
1683: --
1728: -- where bah.batch_id = p_batch_id;
1729: -- commit;
1730: -- end if;
1731: --
1732: hr_utility.set_location('payplnk.validate_header',25);
1733: --
1734: update pay_batch_control_totals bac
1735: set bac.control_status = 'U'
1736: where bac.batch_id = p_batch_id
1735: set bac.control_status = 'U'
1736: where bac.batch_id = p_batch_id
1737: and bac.control_status <> 'T';
1738: --
1739: hr_utility.set_location('payplnk.validate_header',30);
1740: --
1741: update pay_batch_lines bal
1742: set bal.batch_line_status = 'U'
1743: where bal.batch_id = p_batch_id
1744: and bal.batch_line_status <> 'T';
1745: --
1746: -- commit;
1747: --
1748: hr_utility.set_location('payplnk.validate_header',35);
1749: --
1750: if l_process_recs <> 'T' then
1751: --
1752: open csr_header(p_batch_id);
1773: --
1774: --
1775: -- call legislative header check hook if required
1776: --
1777: hr_utility.set_location('payplnk.validate_header',36);
1778: if p_leg_header_check = TRUE then
1779: --
1780: begin
1781: statem := 'BEGIN
1873: --
1874: l_user_status := 'V';
1875: g_user_status := l_user_status;
1876: --
1877: hr_utility.set_location('payplnk.validate_header',40);
1878: --
1879: begin
1880: pay_user_check.validate_header (
1881: p_batch_id,
1881: p_batch_id,
1882: l_user_status,
1883: l_user_message);
1884: --
1885: hr_utility.set_location('payplnk.validate_header',45);
1886: --
1887: exception
1888: when nopackbody then
1889: --
1950: --
1951: raise user_error;
1952: end;
1953: --
1954: hr_utility.set_location('payplnk.validate_header',50);
1955: --
1956: -- If a status has not been returned raise an error.
1957: --
1958: if l_user_status is null then
2117: --
2118: close csr_header;
2119: --
2120: end if;
2121: hr_utility.set_location('payplnk.validate_header',55);
2122: --
2123: exception
2124: when column_is_null then
2125: --
2212: end validate_header;
2213: --
2214: -----------------------------------------------------------------------
2215: -- NAME --
2216: -- payplnk.validate_controls PRIVATE PROCEDURE --
2217: -- --
2218: -- DESCRIPTION --
2219: -- Validate batch control(s) details. This includes the user defined--
2220: -- checks for the control(s) as well as the core validation checks --
2253: g_ctl_id_tbl.delete;
2254: g_ctl_stat_tbl.delete;
2255: g_ctl_mess_tbl.delete;
2256: --
2257: hr_utility.set_location('payplnk.validate_controls',5);
2258: --
2259: -- Check to see if user defined control types exist.
2260: --
2261: begin
2280: raise control_types_not_defined;
2281: --
2282: end;
2283: --
2284: hr_utility.set_location('payplnk.validate_controls',10);
2285: --
2286: -- Otherwise validate the batch control details.
2287: --
2288: for g_control_record in csr_all_controls(p_batch_id) loop
2305: hr_general.end_of_time)
2306: and hlk.enabled_flag = 'Y'
2307: and upper(g_control_record.control_type) in (hlk.lookup_code);
2308: --
2309: hr_utility.set_location('payplnk.validate_controls',15);
2310: --
2311: exception
2312: when no_data_found then
2313: raise invalid_value;
2425: g_control_record.control_total,
2426: l_user_status,
2427: l_user_message);
2428: --
2429: hr_utility.set_location('payplnk.validate_controls',20);
2430: --
2431: exception
2432: --
2433: -- If an unhandled error occurred during the user control validation
2466: --
2467: raise user_control_error;
2468: end;
2469: --
2470: hr_utility.set_location('payplnk.validate_controls',25);
2471: --
2472: -- If a status has not been returned raise an error.
2473: --
2474: if l_user_status is null then
2627: end if;
2628: --
2629: end if;
2630: --
2631: hr_utility.set_location('payplnk.validate_controls',30);
2632:
2633: -- end section dealing with user defined controls as opposed to standard controls
2634: end if;
2635: --
2707: --
2708: end;
2709: end loop;
2710: --
2711: hr_utility.set_location('payplnk.validate_controls',35);
2712: --
2713: exception
2714: --
2715: when control_types_not_defined then
2758: --
2759: --
2760: -----------------------------------------------------------------------
2761: -- NAME --
2762: -- payplnk.validate_lines PRIVATE PROCEDURE --
2763: -- --
2764: -- DESCRIPTION --
2765: -- Validates batch line(s) details. This includes the user defined --
2766: -- checks for the line(s) as well as the core validation checks --
3018: l_bee_iv_upgrade varchar2(1);
3019: --
3020: begin
3021: --
3022: hr_utility.set_location('payplnk.validate_lines',5);
3023: -- Added the following call as part of the fix to the bug#7138224.
3024: fnd_profile.put('PER_ASSIGNMENT_ID',p_asg_id);
3025: --
3026: --Only validate batch lines if there are no eerrors in batch header or control totals.
3054: return;
3055: end if;
3056: --
3057: -- Set the global parameter to disable the triggers.
3058: payplnk.g_payplnk_call := true;
3059: --
3060: -- Set the retry duration and maximum wait values if one hasn't assigned.
3061: if g_lock_max_wait is null then
3062: --
3227: l_creator_type := 'H';
3228: l_absence_attendance_id := 0;
3229: absence_entry_already_created := false;
3230: --
3231: hr_utility.set_location('payplnk.validate_lines',10);
3232: -- Moving the following code into the validate procedure.
3233: -- --
3234: -- -- Carry out the core line validation checks.
3235: -- --
3286: --
3287: raise line_error_occurred;
3288: end;
3289: --
3290: hr_utility.set_location('payplnk.validate_lines',15);
3291: --
3292: -- If neither an element type id or element name has been entered,
3293: -- raise error.
3294: --
3334: --
3335: raise line_error_occurred;
3336: end;
3337: --
3338: hr_utility.set_location('payplnk.validate_lines',20);
3339: --
3340: -- If only an element type id has been entered, ensure it is valid.
3341: --
3342: else
3370: raise line_error_occurred;
3371: end;
3372: end if;
3373: --
3374: hr_utility.set_location('payplnk.validate_lines',25);
3375: -- Moving the following code into the validate procedure.
3376: -- --
3377: -- -- Ensure valid assignment details have been entered.
3378: -- --
3431: -- --
3432: -- raise line_error_occurred;
3433: -- end;
3434: -- --
3435: -- hr_utility.set_location('payplnk.validate_lines',30);
3436: -- --
3437: -- -- If both assignment id and assignment number are null, raise error.
3438: -- --
3439: -- elsif (g_line_record.assignment_id is null) and
3496: -- --
3497: -- raise line_error_occurred;
3498: -- end;
3499: -- --
3500: -- hr_utility.set_location('payplnk.validate_lines',35);
3501: -- --
3502: -- -- If only an assignment id has been entered, ensure that it is valid.
3503: -- --
3504: -- else
3528: -- raise line_error_occurred;
3529: -- end;
3530: -- end if;
3531: --
3532: hr_utility.set_location('payplnk.validate_lines',40);
3533: --
3534: -- Validate the entry values, if entered.
3535: --
3536: l_entry_value_tbl(1) := g_line_record.value_1;
3631:
3632: end loop;
3633: close csr_table_inp_ids;
3634: --
3635: hr_utility.set_location('payplnk.validate_lines',45);
3636: --
3637: -- If there are no input value ids for the element type or the
3638: -- entry values exceed the number of input value id(s) , raise an error.
3639: --
3650: raise invalid_entry_value;
3651: end if;
3652: end loop;
3653: --
3654: hr_utility.set_location('payplnk.validate_lines', 50);
3655: --
3656: -- Retrieve the link id for the element type and assignment combination.
3657: --
3658: hr_utility.trace('ASS: '||g_line_record.assignment_id);
3664: l_link_id := hr_entry_api.get_link(g_line_record.assignment_id,
3665: g_line_record.element_type_id,
3666: g_line_record.effective_date);
3667: --
3668: hr_utility.set_location('payplnk.validate_lines',55);
3669: --
3670: -- Raise an error if a link does not exist on the effective date.
3671: --
3672: if l_link_id is null then
3687: or l_inpv_uom_tbl(l) = 'H_HHMMSS' or l_inpv_uom_tbl(l) = 'H_DECIMAL1'
3688: or l_inpv_uom_tbl(l) = 'H_DECIMAL2' or l_inpv_uom_tbl(l) = 'H_DECIMAL3'
3689: or l_inpv_uom_tbl(l) = 'HOURS') then
3690: --
3691: hr_utility.set_location('payplnk.validate_lines',53);
3692: --
3693: l_absence_attendance_type_id := null;
3694: --
3695: -- The hours input value may be associated with an Absence
3719: -- exception
3720: -- when no_data_found then null;
3721: -- end;
3722: --
3723: hr_utility.set_location('payplnk.validate_lines',54);
3724: --
3725: if l_absence_attendance_type_id is not null then
3726: --
3727: l_abs_type_tbl(l) := l_absence_attendance_type_id;
3729: l_creator_type := 'A';
3730: --
3731: end if;
3732: --
3733: hr_utility.set_location('payplnk.validate_lines',56);
3734: --
3735: --
3736: -- WW Bug# 282299
3737: -- The hours input value may also be associated with an Accrual Plan.
3749: p_asg_id => g_line_record.assignment_id,
3750: p_sess_date => g_line_record.effective_date,
3751: p_eligible => l_eligible);
3752: --
3753: hr_utility.set_location('payplnk.validate_lines',57);
3754: --
3755: if l_eligible = 'N' then
3756: hr_utility.set_location('payplnk.validate_lines',58);
3757: hr_utility.trace('Assignment is in ineligible period for this accrual plan. ');
3752: --
3753: hr_utility.set_location('payplnk.validate_lines',57);
3754: --
3755: if l_eligible = 'N' then
3756: hr_utility.set_location('payplnk.validate_lines',58);
3757: hr_utility.trace('Assignment is in ineligible period for this accrual plan. ');
3758: hr_utility.set_message(801,'PAY_7853_PDT_INELIG_ACCRUAL');
3759: hr_utility.raise_error;
3760: end if;
3760: end if;
3761: end if;
3762: end loop;
3763: --
3764: hr_utility.set_location('payplnk.validate_lines',59);
3765: --
3766: -- Validate the costing details entered.
3767: --
3768: -- S.Sinha pseudo bug 493304, for performance fix for BT.
3828: end if; -- } no structure
3829: --
3830: -- Retrieve/generate a cost keyflex id for the costing details.
3831: --
3832: hr_utility.set_location('payplnk.validate_lines',60);
3833: --
3834: g_line_record.cost_allocation_keyflex_id :=
3835: hr_entry.maintain_cost_keyflex(
3836: l_cost_allocation_structure,
3874: end if; -- } costable_type N else
3875: --
3876: end if; -- } costing exists
3877: --
3878: hr_utility.set_location('payplnk.validate_lines',65);
3879: --
3880: -- The batch line core validation checks have been carried out
3881: -- successfully, hence the legislative hook checks (if any) and
3882: -- then the user line validation checks can be called.
3886: -- The legislative hook to line validation should be executed here
3887: --
3888: -- call legislative header check hook if required
3889: --
3890: hr_utility.set_location('payplnk.validate_lines',66);
3891: if l_leg_line_check = TRUE then
3892: --
3893: begin
3894: hr_utility.set_location('payplnk.validate_lines',67);
3890: hr_utility.set_location('payplnk.validate_lines',66);
3891: if l_leg_line_check = TRUE then
3892: --
3893: begin
3894: hr_utility.set_location('payplnk.validate_lines',67);
3895: statem := 'BEGIN
3896: pay_'||lower(l_legislation_code)||'_bee.validate_line(:batch_line_id, :valid, :leg_message, :line_changed); END;';
3897: --
3898: if pay_core_utils.get_sql_cursor(statem,sql_curs) then
3913: end if;
3914: --
3915: exception
3916: when others then
3917: hr_utility.set_location('payplnk.validate_lines',68);
3918: --
3919: g_line_error := true;
3920: g_count := g_count + 1;
3921: --
3931: raise line_error_occurred;
3932: end;
3933: --
3934: if l_valid = 1 then
3935: hr_utility.set_location('payplnk.validate_lines',688);
3936: --
3937: g_line_error := true;
3938: g_count := g_count + 1;
3939: --
3997: --
3998: raise line_error_occurred;
3999: end;
4000: --
4001: hr_utility.set_location('payplnk.validate_lines',70);
4002: --
4003: -- If a status has not been returned raise an error.
4004: --
4005: if l_user_status is null then
4094: end if;
4095: end if;
4096: end if;
4097: --
4098: hr_utility.set_location('payplnk.validate_lines',75);
4099: --
4100: -- Batch line has been validated successfully. Attempt to insert/update
4101: -- an element entry into the base tables.
4102: --
4214: p_error_table => l_error_table);
4215: --
4216:
4217: if l_warning_table.COUNT <> 0 then
4218: hr_utility.set_location('payplnk.validate_lines',76);
4219: for l_warning_num in 1..l_warning_table.COUNT loop
4220: hr_utility.trace(l_warning_table(l_warning_num));
4221: if l_warning_table(l_warning_num) = 'EE_CREATED_BY_ABSENCE_API' then
4222: hr_utility.set_location('payplnk.validate_lines',77);
4218: hr_utility.set_location('payplnk.validate_lines',76);
4219: for l_warning_num in 1..l_warning_table.COUNT loop
4220: hr_utility.trace(l_warning_table(l_warning_num));
4221: if l_warning_table(l_warning_num) = 'EE_CREATED_BY_ABSENCE_API' then
4222: hr_utility.set_location('payplnk.validate_lines',77);
4223: absence_entry_already_created := TRUE;
4224: else
4225: g_count := g_count + 1;
4226: hr_utility.set_message(801,l_warning_table(l_warning_num));
4304: end if;
4305: close csr_future_existence;
4306: end if;
4307: --
4308: hr_utility.set_location('payplnk.validate_lines',80);
4309: --
4310: -- An attempt can be made to insert the element entry as all the
4311: -- prerequisite conditions are in place.
4312: --
4313: -- Bug 488335 - new declaration to avoid invalid parameter
4314: -- mode in Oracle 8
4315: g_line_record1 := g_line_record;
4316: begin
4317: payplnk.insert_element_entry(l_link_id,
4318: g_line_record1,
4319: p_asg_act_id,
4320: l_creator_type,
4321: l_absence_attendance_id,
4359: --
4360: l_action_if_exists_chk := true;
4361: end if;
4362: --
4363: hr_utility.set_location('payplnk.validate_lines',85);
4364: --
4365: if (l_multiple_entries_allowed = 'Y') and
4366: (g_header_record.action_if_exists = 'I') then
4367: --
4366: (g_header_record.action_if_exists = 'I') then
4367: --
4368: g_line_record1 := g_line_record;
4369: begin
4370: payplnk.insert_element_entry (l_link_id,
4371: g_line_record1,
4372: p_asg_Act_id,
4373: l_creator_type,
4374: l_absence_attendance_id,
4399: --
4400: raise multiple_entries_not_allowed;
4401: end if;
4402: --
4403: hr_utility.set_location('payplnk.validate_lines',90);
4404: --
4405: if g_header_record.action_if_exists = 'R' then
4406: --
4407: raise element_entry_exists;
4536: end if;
4537: --
4538: end if;
4539: --
4540: hr_utility.set_location('payplnk.validate_lines',95);
4541: --
4542: exception
4543: when no_data_found then
4544: g_line_error := true;
4753: if g_line_error then
4754: raise no_data_found;
4755: end if;
4756: --
4757: hr_utility.set_location('payplnk.validate_lines',500);
4758: --
4759: -- Close the header cursor if it is open.
4760: if csr_bl_header%ISOPEN then
4761: close csr_bl_header;
4761: close csr_bl_header;
4762: end if;
4763: --
4764: --Set the global parameter to enable the triggers.
4765: payplnk.g_payplnk_call := false;
4766: --
4767: --
4768: exception
4769: when others then
4774: close csr_bl_header;
4775: end if;
4776: rollback to BL;
4777: --Set the global parameter to enable the triggers.
4778: payplnk.g_payplnk_call := false;
4779: hr_utility.set_message(800, 'HR_289719_BEE_LINE_ERROR');
4780: hr_utility.raise_error;
4781: -- raise;
4782: --
4783: end validate_lines;
4784: --
4785: -----------------------------------------------------------------------
4786: -- NAME --
4787: -- payplnk.purge_messages PRIVATE PROCEDURE --
4788: -- --
4789: -- DESCRIPTION --
4790: -- Deletes all messages associated with the batch from the --
4791: -- PAY_MESSAGE_LINES table. --
4812: and (batch_line_status <> 'T' or p_mode='Y');
4813: --
4814: begin
4815: --
4816: hr_utility.set_location('payplnk.purge_messages',5);
4817: --
4818: for pclrec in pcl(p_batch_id) loop
4819: delete from pay_message_lines
4820: where source_type = 'C'
4820: where source_type = 'C'
4821: and source_id = pclrec.batch_control_id;
4822: end loop;
4823: --
4824: hr_utility.set_location('payplnk.purge_messages',10);
4825: --
4826: for pblrec in pbl(p_batch_id) loop
4827: delete from pay_message_lines
4828: where source_type = 'L'
4828: where source_type = 'L'
4829: and source_id = pblrec.batch_line_id;
4830: end loop;
4831: --
4832: hr_utility.set_location('payplnk.purge_messages',20);
4833: --
4834: delete from pay_message_lines
4835: where source_type = 'H'
4836: and source_id = p_batch_id
4840: where pbl.batch_id = p_batch_id
4841: and pbl.batch_line_status = 'T')
4842: or p_mode='Y');
4843: --
4844: hr_utility.set_location('payplnk.purge_messages',25);
4845: --
4846: end purge_messages;
4847: --
4848: ----------------------------------------------------------------------
4846: end purge_messages;
4847: --
4848: ----------------------------------------------------------------------
4849: -- NAME --
4850: -- payplnk.insert_element_entry PRIVATE PROCEDURE --
4851: -- --
4852: -- DESCRIPTION --
4853: -- Inserts an element entry into the PAY_ELEMENT_ENTRIES_F table --
4854: ----------------------------------------------------------------------
4892: l_time_out boolean := FALSE;
4893: --
4894: begin
4895: --
4896: hr_utility.set_location('payplnk.insert_element_entry',5);
4897: --
4898: -- Bug 423237 - Convert passed in reason to lookup_code for api
4899: --
4900: IF l_line_record.reason IS NOT NULL THEN
5090: hr_utility.set_location('GHR Installed....insert_element_entry .. Post ',6);
5091: ghr_history_api.post_update_process;
5092: end if;
5093: --
5094: hr_utility.set_location('payplnk.insert_element_entry',10);
5095: --
5096: end insert_element_entry;
5097: --
5098: --
5097: --
5098: --
5099: ---------------------------------------------------------------------
5100: -- NAME --
5101: -- payplnk.update_element_entry PRIVATE PROCEDURE --
5102: -- --
5103: -- DESCRIPTION --
5104: -- Updates an existing element entry in the PAY_ELEMENT_ENTRIES_F --
5105: -- table. --
5126: l_time_out boolean := FALSE;
5127: --
5128: begin
5129: --
5130: hr_utility.set_location('payplnk.update_element_entry',5);
5131: --
5132: -- Bug 423237 - Convert passed in reason to lookup_code for api
5133: --
5134: IF l_line_record.reason IS NOT NULL THEN
5273: -- for this element
5274:
5275: if (p_creator_type = 'A') then
5276: --
5277: hr_utility.set_location('payplnk.update_element_entry',10);
5278: --
5279: update per_absence_attendances
5280: set batch_id = null
5281: where absence_attendance_id = p_creator_id;
5280: set batch_id = null
5281: where absence_attendance_id = p_creator_id;
5282: else
5283: --
5284: hr_utility.set_location('payplnk.update_element_entry',15);
5285: --
5286: if p_allow_rollback then
5287: --Recurring element of creator type 'H' or 'F' with update and update insert change.
5288: --
5307: end if;
5308: end if;
5309:
5310: --
5311: hr_utility.set_location('payplnk.update_element_entry',20);
5312: --
5313: end update_element_entry;
5314: --
5315: end payplnk;
5311: hr_utility.set_location('payplnk.update_element_entry',20);
5312: --
5313: end update_element_entry;
5314: --
5315: end payplnk;