[Home] [Help]
1: package body payplnk as
2: /* $Header: paylink.pkb 120.7.12020000.2 2012/07/06 11:27:34 vmaripal ship $ */
3: --
4: --
5: -- GLOBAL DECLARATIONS
166: and HL.lookup_code = p_lookup_code;
167: --
168: begin
169: --
170: hr_utility.set_location('payplnk.convert_internal_to_display',10);
171: if (p_lookup_type is not null and
172: l_internal_value is not null) then
173: --
174: open csr_valid_lookup(p_lookup_type, l_internal_value);
173: --
174: open csr_valid_lookup(p_lookup_type, l_internal_value);
175: fetch csr_valid_lookup into l_display_value ;
176: close csr_valid_lookup;
177: hr_utility.set_location('payplnk.convert_internal_to_display',15);
178: --
179: elsif (p_value_set_id is not null and
180: l_internal_value is not null) then
181: --
180: l_internal_value is not null) then
181: --
182: l_display_value := pay_input_values_pkg.decode_vset_value(
183: p_value_set_id, l_internal_value);
184: hr_utility.set_location('payplnk.convert_internal_to_display',20);
185: --
186: else
187: --
188: hr_utility.set_location('payplnk.convert_internal_to_display',21);
184: hr_utility.set_location('payplnk.convert_internal_to_display',20);
185: --
186: else
187: --
188: hr_utility.set_location('payplnk.convert_internal_to_display',21);
189: hr_chkfmt.changeformat (
190: l_internal_value, /* the value to be formatted (out - display) */
191: l_display_value, /* the formatted value on output (out - canonical) */
192: p_uom_value, /* the format to check */
191: l_display_value, /* the formatted value on output (out - canonical) */
192: p_uom_value, /* the format to check */
193: p_currency_code );
194: --
195: hr_utility.set_location('payplnk.convert_internal_to_display',25);
196: end if;
197: hr_utility.set_location('payplnk.convert_internal_to_display',30);
198: --
199: return l_display_value;
193: p_currency_code );
194: --
195: hr_utility.set_location('payplnk.convert_internal_to_display',25);
196: end if;
197: hr_utility.set_location('payplnk.convert_internal_to_display',30);
198: --
199: return l_display_value;
200: --
201: exception
229: --
230: --
231: -----------------------------------------------------------------------
232: -- NAME --
233: -- payplnk.run_process --
234: -- --
235: -- DESCRIPTION --
236: -- The main procedure caled from the SRS screen, from which the --
237: -- paylink process is triggered for a particular batch. --
303: --
304: -- Depending on the mode of operation requested the particular batch
305: -- will be processed accordingly.
306: --
307: hr_utility.set_location('payplnk.run_process',5);
308: --
309: if (p_batch_operation = 'VALIDATE' OR p_batch_operation = 'TRANSFER') then
310: --
311: -- validate the batch.
309: if (p_batch_operation = 'VALIDATE' OR p_batch_operation = 'TRANSFER') then
310: --
311: -- validate the batch.
312: --
313: hr_utility.set_location('payplnk.run_process',6);
314: --
315: g_header_processing := TRUE;
316: --
317: payplnk.validate
313: hr_utility.set_location('payplnk.run_process',6);
314: --
315: g_header_processing := TRUE;
316: --
317: payplnk.validate
318: (
319: p_business_group_id,
320: p_batch_operation,
321: p_batch_id
324: set_header_status(p_business_group_id,p_batch_operation,p_batch_id);
325: --
326: g_header_processing := FALSE;
327: --
328: payplnk.g_payplnk_call := TRUE;
329: --
330: hr_utility.set_location('payplnk.run_process',7);
331: --
332: -- elsif p_batch_operation = 'TRANSFER' then
326: g_header_processing := FALSE;
327: --
328: payplnk.g_payplnk_call := TRUE;
329: --
330: hr_utility.set_location('payplnk.run_process',7);
331: --
332: -- elsif p_batch_operation = 'TRANSFER' then
333: -- --
334: -- -- transfer the batch. NB The transfer procedure calls the
335: -- -- validate procedure to ensure the batch is valid before
336: -- -- tansferring the element entries into the appropriate
337: -- -- base tables.
338: -- --
339: -- hr_utility.set_location('payplnk.run_process',8);
340: -- payplnk.transfer
341: -- (
342: -- p_business_group_id,
343: -- p_batch_operation,
336: -- -- tansferring the element entries into the appropriate
337: -- -- base tables.
338: -- --
339: -- hr_utility.set_location('payplnk.run_process',8);
340: -- payplnk.transfer
341: -- (
342: -- p_business_group_id,
343: -- p_batch_operation,
344: -- p_batch_id
342: -- p_business_group_id,
343: -- p_batch_operation,
344: -- p_batch_id
345: -- );
346: -- hr_utility.set_location('payplnk.run_process',9);
347: --
348: elsif p_batch_operation = 'PURGE' then
349: --
350: -- purge the batch. All records associated with the batch i.e.
350: -- purge the batch. All records associated with the batch i.e.
351: -- batch messages, controls, lines and header will be deleted
352: -- from the temporary batch tables.
353: --
354: hr_utility.set_location('payplnk.run_process',10);
355: payplnk.purge
356: (
357: p_batch_id
358: );
351: -- batch messages, controls, lines and header will be deleted
352: -- from the temporary batch tables.
353: --
354: hr_utility.set_location('payplnk.run_process',10);
355: payplnk.purge
356: (
357: p_batch_id
358: );
359: hr_utility.set_location('payplnk.run_process',11);
355: payplnk.purge
356: (
357: p_batch_id
358: );
359: hr_utility.set_location('payplnk.run_process',11);
360: --
361: end if;
362: --
363: hr_utility.set_location('payplnk.run_process',15);
359: hr_utility.set_location('payplnk.run_process',11);
360: --
361: end if;
362: --
363: hr_utility.set_location('payplnk.run_process',15);
364: --
365: -- commit;
366: --
367: errbuf := null;
382: end run_process;
383: --
384: ------------------------------------------------------------------------
385: -- NAME --
386: -- payplnk.validate --
387: -- --
388: -- DESCRIPTION --
389: -- Given a batch id it will validate the batch and insert its --
390: -- element entries into the necessary base tables. Then depending on --
423: not_upper boolean := false;
424: --
425: begin
426: --
427: hr_utility.set_location('payplnk.validate',1);
428: --
429: SAVEPOINT VL;
430: --
431: hr_utility.set_location('payplnk.validate',5);
427: hr_utility.set_location('payplnk.validate',1);
428: --
429: SAVEPOINT VL;
430: --
431: hr_utility.set_location('payplnk.validate',5);
432: --
433: select legislation_code
434: into l_legislation_code
435: from per_business_groups_perf
539: close csr_all_controls;
540: end if;
541: end if;
542: --
543: hr_utility.set_location('payplnk.validate',10);
544: --
545: -- Any updates made to the batch header and control statuses, and
546: -- and messages resulting from the header and control checks will be
547: -- committed.
573: -- raise error_occurred;
574: end if;
575: end if;
576: --
577: hr_utility.set_location('payplnk.validate',15);
578: -- The following is comment out due to multithreading.
579: -- --
580: -- -- Successfully validated line(s) may as a result have created
581: -- -- element entrie(s) in the base tables, therefore the entries must be
598: -- else
599: -- commit;
600: -- end if;
601: -- --
602: -- hr_utility.set_location('payplnk.validate',20);
603: -- --
604: -- -- Any messages accumulated during batch lines validation are now
605: -- -- inserted into the pay_message_lines table.
606: -- --
629: -- when no_data_found then
630: -- null;
631: -- end;
632: -- --
633: -- hr_utility.set_location('payplnk.validate',25);
634: -- --
635: -- -- The status of each batch line is set according to the outcome
636: -- -- of the validation carried out for the particular line.
637: -- --
657: -- when no_data_found then
658: -- null;
659: -- end;
660: -- --
661: -- hr_utility.set_location('payplnk.validate',30);
662: -- --
663: -- if l_line_match = false then
664: -- --
665: -- update pay_batch_lines bal
670: -- end if;
671: -- end loop;
672: -- --
673: -- End of the code blocked for multithreading.
674: hr_utility.set_location('payplnk.validate',35);
675: --
676: for g_line_record in csr_all_lines(p_batch_id) loop
677: --
678: if g_line_record.effective_date is null then
824: where current of csr_all_lines;
825: --
826: end;
827: --
828: hr_utility.set_location('payplnk.validate',40);
829: --
830: elsif (g_line_record.assignment_id is null) and
831: (g_line_record.assignment_number is null) then
832: --
854: set batch_line_status = 'E'
855: where current of csr_all_lines;
856: --
857: --
858: hr_utility.set_location('payplnk.validate',45);
859: --
860: elsif (g_line_record.assignment_id is not null) and
861: (g_line_record.assignment_number is not null) then
862: begin
921: where current of csr_all_lines;
922: --
923: end;
924: --
925: hr_utility.set_location('payplnk.validate',50);
926: --
927: -- If only an assignment id has been entered, ensure that it is valid.
928: --
929: else
966: end;
967: --
968: end if;
969: --
970: hr_utility.set_location('payplnk.validate',55);
971: --
972: end if;
973: --
974: end loop;
972: end if;
973: --
974: end loop;
975: --
976: hr_utility.set_location('payplnk.validate',60);
977: --
978: -- COMMIT;
979: --
980: --
977: --
978: -- COMMIT;
979: --
980: --
981: hr_utility.set_location('payplnk.validate',85);
982: --
983: exception
984: when error_occurred then
985: rollback to VL;
993: end validate;
994: --
995: -----------------------------------------------------------------------
996: -- NAME --
997: -- payplnk.set_status --
998: -- --
999: -- DESCRIPTION --
1000: -- Given a batch id and the mode of operation it will set the status --
1001: -- of the batch and control totals. --
1038: where pbh.batch_id = p_batch_id;
1039: --
1040: begin
1041: --
1042: hr_utility.set_location('payplnk.set_status',5);
1043: --
1044: SAVEPOINT SS;
1045: --
1046: g_line_error := false;
1084: close csr_error_header;
1085: --
1086:
1087: if (g_header_error = true) then
1088: hr_utility.trace('payplnk.set_status g_header_error = TRUE');
1089: else
1090: hr_utility.trace('payplnk.set_status g_header_error = FALSE');
1091: end if;
1092:
1086:
1087: if (g_header_error = true) then
1088: hr_utility.trace('payplnk.set_status g_header_error = TRUE');
1089: else
1090: hr_utility.trace('payplnk.set_status g_header_error = FALSE');
1091: end if;
1092:
1093: if (g_control_error = true) then
1094: hr_utility.trace('payplnk.set_status g_control_error = TRUE');
1090: hr_utility.trace('payplnk.set_status g_header_error = FALSE');
1091: end if;
1092:
1093: if (g_control_error = true) then
1094: hr_utility.trace('payplnk.set_status g_control_error = TRUE');
1095: else
1096: hr_utility.trace('payplnk.set_status g_control_error = FALSE');
1097: end if;
1098:
1092:
1093: if (g_control_error = true) then
1094: hr_utility.trace('payplnk.set_status g_control_error = TRUE');
1095: else
1096: hr_utility.trace('payplnk.set_status g_control_error = FALSE');
1097: end if;
1098:
1099: if (g_line_error = true) then
1100: hr_utility.trace('payplnk.set_status g_line_error = TRUE');
1096: hr_utility.trace('payplnk.set_status g_control_error = FALSE');
1097: end if;
1098:
1099: if (g_line_error = true) then
1100: hr_utility.trace('payplnk.set_status g_line_error = TRUE');
1101: else
1102: hr_utility.trace('payplnk.set_status g_line_error = FALSE');
1103: end if;
1104: --
1098:
1099: if (g_line_error = true) then
1100: hr_utility.trace('payplnk.set_status g_line_error = TRUE');
1101: else
1102: hr_utility.trace('payplnk.set_status g_line_error = FALSE');
1103: end if;
1104: --
1105: if (p_batch_operation = 'VALIDATE' and g_header_error = false) then
1106: --
1115: -- Once the element entries have been committed check if the
1116: -- PURGE_AFTER_TRANSFER flag is 'Y'. If so delete all records associated
1117: -- with the batch from the temporary batch tables.
1118: --
1119: hr_utility.set_location('payplnk.set_status',11);
1120: --
1121: if (g_line_error = false) and
1122: upper(l_purge_after_transfer) = 'Y' then
1123: hr_utility.set_location('payplnk.set_status',30);
1119: hr_utility.set_location('payplnk.set_status',11);
1120: --
1121: if (g_line_error = false) and
1122: upper(l_purge_after_transfer) = 'Y' then
1123: hr_utility.set_location('payplnk.set_status',30);
1124: purge (
1125: p_batch_id
1126: );
1127: --
1126: );
1127: --
1128: else
1129: --
1130: hr_utility.set_location('payplnk.set_status',15);
1131: --
1132: if g_control_error = false then
1133: for g_control_record in csr_all_controls(p_batch_id) loop
1134: update pay_batch_control_totals
1137: --
1138: end loop;
1139: end if;
1140: --
1141: hr_utility.set_location('payplnk.set_status',20);
1142: --
1143: update pay_batch_headers
1144: set batch_status = l_status
1145: where batch_id = p_batch_id;
1143: update pay_batch_headers
1144: set batch_status = l_status
1145: where batch_id = p_batch_id;
1146: --
1147: hr_utility.set_location('payplnk.set_status',25);
1148: --
1149: end if;
1150: --
1151: hr_utility.set_location('payplnk.set_status',30);
1147: hr_utility.set_location('payplnk.set_status',25);
1148: --
1149: end if;
1150: --
1151: hr_utility.set_location('payplnk.set_status',30);
1152: end if;
1153: --
1154: end if;
1155: --
1152: end if;
1153: --
1154: end if;
1155: --
1156: hr_utility.set_location('payplnk.set_status',35);
1157: --
1158: exception
1159: when others then
1160: rollback to SS;
1162: end set_status;
1163: --
1164: -----------------------------------------------------------------------
1165: -- NAME --
1166: -- payplnk.set_line_status --
1167: -- --
1168: -- DESCRIPTION --
1169: -- Given a batch id and the mode of operation it will set the status --
1170: -- of the batch lines. --
1184: --
1185: --
1186: begin
1187: --
1188: hr_utility.set_location('payplnk.set_line_status',5);
1189: --
1190: -- Set the global parameter to disable the triggers.
1191: payplnk.g_payplnk_call := true;
1192: --
1187: --
1188: hr_utility.set_location('payplnk.set_line_status',5);
1189: --
1190: -- Set the global parameter to disable the triggers.
1191: payplnk.g_payplnk_call := true;
1192: --
1193: SAVEPOINT SLS;
1194: --
1195: --
1200: else
1201: l_batch_line_status := 'T';
1202: end if;
1203: --
1204: hr_utility.set_location('payplnk.set_line_status',10);
1205: --
1206: -- Any messages accumulated during batch lines validation are now
1207: -- inserted into the pay_message_lines table.
1208: --
1231: when no_data_found then
1232: null;
1233: end;
1234: --
1235: hr_utility.set_location('payplnk.set_line_status',20);
1236: --
1237: -- The status of each batch line is set according to the outcome
1238: -- of the validation carried out for the particular line.
1239: --
1266: when no_data_found then
1267: null;
1268: end;
1269: --
1270: hr_utility.set_location('payplnk.set_line_status',30);
1271: --
1272: if l_line_match = false then
1273: --
1274: update pay_batch_lines bal
1286: g_status_tbl.delete;
1287: g_message_tbl.delete;
1288: --
1289: -- Set the global parameter to enable the triggers.
1290: payplnk.g_payplnk_call := false;
1291: --
1292: --
1293: hr_utility.set_location('payplnk.set_line_status',35);
1294: --
1289: -- Set the global parameter to enable the triggers.
1290: payplnk.g_payplnk_call := false;
1291: --
1292: --
1293: hr_utility.set_location('payplnk.set_line_status',35);
1294: --
1295: exception
1296: when others then
1297: rollback to SLS;
1299: end set_line_status;
1300: --
1301: -----------------------------------------------------------------------
1302: -- NAME --
1303: -- payplnk.set_header_status --
1304: -- --
1305: -- DESCRIPTION --
1306: -- Given a batch id and the mode of operation it will set the status --
1307: -- of the batch lines. --
1319: --
1320: --
1321: begin
1322: --
1323: hr_utility.set_location('payplnk.set_header_status',5);
1324: --
1325: -- Set the global parameter to disable the triggers.
1326: payplnk.g_payplnk_call := true;
1327: --
1322: --
1323: hr_utility.set_location('payplnk.set_header_status',5);
1324: --
1325: -- Set the global parameter to disable the triggers.
1326: payplnk.g_payplnk_call := true;
1327: --
1328: SAVEPOINT SHS;
1329: --
1330: if g_header_processing then
1352: end if;
1353: end if;
1354: --
1355: --
1356: hr_utility.set_location('payplnk.set_header_status',10);
1357: --
1358: -- Only update the status of the cotnrol totals if the batc his not failed.
1359: if g_head_err_stat <> 'F' or g_head_err_stat is null then
1360: --
1386: when no_data_found then
1387: null;
1388: end;
1389: --
1390: hr_utility.set_location('payplnk.set_header_status',20);
1391: --
1392: -- The status of each batch total is set according to the outcome
1393: -- of the validation carried out for the particular total.
1394: --
1413: when no_data_found then
1414: null;
1415: end;
1416: --
1417: hr_utility.set_location('payplnk.set_header_status',30);
1418: --
1419: if l_line_match = false then
1420: --
1421: update pay_batch_control_totals ctl
1461: g_head_err_msg := null;
1462: g_header_processing := false;
1463: --
1464: -- Set the global parameter to enable the triggers.
1465: payplnk.g_payplnk_call := false;
1466: --
1467: --
1468: hr_utility.set_location('payplnk.set_header_status',35);
1469: --
1464: -- Set the global parameter to enable the triggers.
1465: payplnk.g_payplnk_call := false;
1466: --
1467: --
1468: hr_utility.set_location('payplnk.set_header_status',35);
1469: --
1470: exception
1471: when others then
1472: rollback to SHS;
1475: --
1476: --
1477: -----------------------------------------------------------------------
1478: -- NAME --
1479: -- payplnk.purge --
1480: -- --
1481: -- DESCRIPTION --
1482: -- Given a batch id it will delete all records associated with the --
1483: -- batch from all the temporary batch tables. Any messages --
1499: --
1500: begin
1501: --
1502: SAVEPOINT PU;
1503: hr_utility.set_location('payplnk.purge',5);
1504: --
1505: open csr_ovn;
1506: fetch csr_ovn into l_ovn;
1507: close csr_ovn;
1506: fetch csr_ovn into l_ovn;
1507: close csr_ovn;
1508: --
1509: -- Set the global parameter to enable the triggers.
1510: payplnk.g_payplnk_call := true;
1511: --
1512: --
1513: --
1514: -- Calls the BEE API to delete the batch.
1518: ,p_object_version_number => l_ovn
1519: );
1520: --
1521: -- Set the global parameter to enable the triggers.
1522: payplnk.g_payplnk_call := false;
1523: --
1524: --
1525: hr_utility.set_location('payplnk.purge',8);
1526: --
1521: -- Set the global parameter to enable the triggers.
1522: payplnk.g_payplnk_call := false;
1523: --
1524: --
1525: hr_utility.set_location('payplnk.purge',8);
1526: --
1527: --
1528: /*
1529: purge_messages(p_batch_id,'Y');
1527: --
1528: /*
1529: purge_messages(p_batch_id,'Y');
1530: --
1531: hr_utility.set_location('payplnk.purge',10);
1532: --
1533: delete from pay_batch_control_totals
1534: where batch_id = p_batch_id;
1535: --
1532: --
1533: delete from pay_batch_control_totals
1534: where batch_id = p_batch_id;
1535: --
1536: hr_utility.set_location('payplnk.purge',15);
1537: --
1538: delete from pay_batch_lines
1539: where batch_id = p_batch_id;
1540: --
1537: --
1538: delete from pay_batch_lines
1539: where batch_id = p_batch_id;
1540: --
1541: hr_utility.set_location('payplnk.purge',20);
1542: --
1543: delete from pay_batch_headers
1544: where batch_id = p_batch_id;
1545: --
1542: --
1543: delete from pay_batch_headers
1544: where batch_id = p_batch_id;
1545: --
1546: hr_utility.set_location('payplnk.purge',25);
1547: --
1548: */
1549: --
1550: exception
1555: end purge;
1556: --
1557: ----------------------------------------------------------------------
1558: -- NAME --
1559: -- payplnk.validate_header PRIVATE PROCEDURE --
1560: -- --
1561: -- DESCRIPTION --
1562: -- Validates batch header details. This includes the user defined --
1563: -- checks for the header as well as the core validation checks --
1597: pragma exception_init(nopackbody,-6508);
1598: --
1599: begin
1600: --
1601: hr_utility.set_location('payplnk.validate_header',5);
1602: --
1603: select legislation_code
1604: into l_legislation_code
1605: from per_business_groups_perf
1618: SAVEPOINT BH;
1619: --
1620: -- commit;
1621: --
1622: hr_utility.set_location('payplnk.validate_header',10);
1623: --
1624: -- Check if transferred record(s) exist in the batch, if so, terminate
1625: -- validation with an appropriate message.
1626: --
1643: when no_data_found then
1644: null;
1645: end;
1646: --
1647: hr_utility.set_location('payplnk.validate_header',15);
1648: --
1649: if l_transfer_recs = 'x' then
1650: --
1651: -- Atleast one transferred record has been detected in the batch,
1677: --
1678: raise core_error;
1679: end if;
1680: --
1681: hr_utility.set_location('payplnk.validate_header',20);
1682: --
1683: -- The core validation checks will be carried out first.
1684: -- Check the batch status is not already PROCESSING.
1685: --
1730: -- where bah.batch_id = p_batch_id;
1731: -- commit;
1732: -- end if;
1733: --
1734: hr_utility.set_location('payplnk.validate_header',25);
1735: --
1736: update pay_batch_control_totals bac
1737: set bac.control_status = 'U'
1738: where bac.batch_id = p_batch_id
1737: set bac.control_status = 'U'
1738: where bac.batch_id = p_batch_id
1739: and bac.control_status <> 'T';
1740: --
1741: hr_utility.set_location('payplnk.validate_header',30);
1742: --
1743: update pay_batch_lines bal
1744: set bal.batch_line_status = 'U'
1745: where bal.batch_id = p_batch_id
1746: and bal.batch_line_status <> 'T';
1747: --
1748: -- commit;
1749: --
1750: hr_utility.set_location('payplnk.validate_header',35);
1751: --
1752: if l_process_recs <> 'T' then
1753: --
1754: open csr_header(p_batch_id);
1775: --
1776: --
1777: -- call legislative header check hook if required
1778: --
1779: hr_utility.set_location('payplnk.validate_header',36);
1780: if p_leg_header_check = TRUE then
1781: --
1782: begin
1783: statem := 'BEGIN
1875: --
1876: l_user_status := 'V';
1877: g_user_status := l_user_status;
1878: --
1879: hr_utility.set_location('payplnk.validate_header',40);
1880: --
1881: begin
1882: pay_user_check.validate_header (
1883: p_batch_id,
1883: p_batch_id,
1884: l_user_status,
1885: l_user_message);
1886: --
1887: hr_utility.set_location('payplnk.validate_header',45);
1888: --
1889: exception
1890: when nopackbody then
1891: --
1952: --
1953: raise user_error;
1954: end;
1955: --
1956: hr_utility.set_location('payplnk.validate_header',50);
1957: --
1958: -- If a status has not been returned raise an error.
1959: --
1960: if l_user_status is null then
2119: --
2120: close csr_header;
2121: --
2122: end if;
2123: hr_utility.set_location('payplnk.validate_header',55);
2124: --
2125: exception
2126: when column_is_null then
2127: --
2214: end validate_header;
2215: --
2216: -----------------------------------------------------------------------
2217: -- NAME --
2218: -- payplnk.validate_controls PRIVATE PROCEDURE --
2219: -- --
2220: -- DESCRIPTION --
2221: -- Validate batch control(s) details. This includes the user defined--
2222: -- checks for the control(s) as well as the core validation checks --
2255: g_ctl_id_tbl.delete;
2256: g_ctl_stat_tbl.delete;
2257: g_ctl_mess_tbl.delete;
2258: --
2259: hr_utility.set_location('payplnk.validate_controls',5);
2260: --
2261: -- Check to see if user defined control types exist.
2262: --
2263: begin
2282: raise control_types_not_defined;
2283: --
2284: end;
2285: --
2286: hr_utility.set_location('payplnk.validate_controls',10);
2287: --
2288: -- Otherwise validate the batch control details.
2289: --
2290: for g_control_record in csr_all_controls(p_batch_id) loop
2307: hr_general.end_of_time)
2308: and hlk.enabled_flag = 'Y'
2309: and upper(g_control_record.control_type) in (hlk.lookup_code);
2310: --
2311: hr_utility.set_location('payplnk.validate_controls',15);
2312: --
2313: exception
2314: when no_data_found then
2315: raise invalid_value;
2427: g_control_record.control_total,
2428: l_user_status,
2429: l_user_message);
2430: --
2431: hr_utility.set_location('payplnk.validate_controls',20);
2432: --
2433: exception
2434: --
2435: -- If an unhandled error occurred during the user control validation
2468: --
2469: raise user_control_error;
2470: end;
2471: --
2472: hr_utility.set_location('payplnk.validate_controls',25);
2473: --
2474: -- If a status has not been returned raise an error.
2475: --
2476: if l_user_status is null then
2629: end if;
2630: --
2631: end if;
2632: --
2633: hr_utility.set_location('payplnk.validate_controls',30);
2634:
2635: -- end section dealing with user defined controls as opposed to standard controls
2636: end if;
2637: --
2709: --
2710: end;
2711: end loop;
2712: --
2713: hr_utility.set_location('payplnk.validate_controls',35);
2714: --
2715: exception
2716: --
2717: when control_types_not_defined then
2760: --
2761: --
2762: -----------------------------------------------------------------------
2763: -- NAME --
2764: -- payplnk.validate_lines PRIVATE PROCEDURE --
2765: -- --
2766: -- DESCRIPTION --
2767: -- Validates batch line(s) details. This includes the user defined --
2768: -- checks for the line(s) as well as the core validation checks --
3026: l_bee_iv_upgrade varchar2(1);
3027: --
3028: begin
3029: --
3030: hr_utility.set_location('payplnk.validate_lines',5);
3031: -- Added the following call as part of the fix to the bug#7138224.
3032: fnd_profile.put('PER_ASSIGNMENT_ID',p_asg_id);
3033: --
3034: --Only validate batch lines if there are no eerrors in batch header or control totals.
3063: end if;
3064: */ /* Bug 13814081 */
3065: --
3066: -- Set the global parameter to disable the triggers.
3067: payplnk.g_payplnk_call := true;
3068: --
3069: -- Set the retry duration and maximum wait values if one hasn't assigned.
3070: if g_lock_max_wait is null then
3071: --
3236: l_creator_type := 'H';
3237: l_absence_attendance_id := 0;
3238: absence_entry_already_created := false;
3239: --
3240: hr_utility.set_location('payplnk.validate_lines',10);
3241: -- Moving the following code into the validate procedure.
3242: -- --
3243: -- -- Carry out the core line validation checks.
3244: -- --
3295: --
3296: raise line_error_occurred;
3297: end;
3298: --
3299: hr_utility.set_location('payplnk.validate_lines',15);
3300: --
3301: -- If neither an element type id or element name has been entered,
3302: -- raise error.
3303: --
3343: --
3344: raise line_error_occurred;
3345: end;
3346: --
3347: hr_utility.set_location('payplnk.validate_lines',20);
3348: --
3349: -- If only an element type id has been entered, ensure it is valid.
3350: --
3351: else
3379: raise line_error_occurred;
3380: end;
3381: end if;
3382: --
3383: hr_utility.set_location('payplnk.validate_lines',25);
3384: -- Moving the following code into the validate procedure.
3385: -- --
3386: -- -- Ensure valid assignment details have been entered.
3387: -- --
3440: -- --
3441: -- raise line_error_occurred;
3442: -- end;
3443: -- --
3444: -- hr_utility.set_location('payplnk.validate_lines',30);
3445: -- --
3446: -- -- If both assignment id and assignment number are null, raise error.
3447: -- --
3448: -- elsif (g_line_record.assignment_id is null) and
3505: -- --
3506: -- raise line_error_occurred;
3507: -- end;
3508: -- --
3509: -- hr_utility.set_location('payplnk.validate_lines',35);
3510: -- --
3511: -- -- If only an assignment id has been entered, ensure that it is valid.
3512: -- --
3513: -- else
3537: -- raise line_error_occurred;
3538: -- end;
3539: -- end if;
3540: --
3541: hr_utility.set_location('payplnk.validate_lines',40);
3542: --
3543: -- Validate the entry values, if entered.
3544: --
3545: l_entry_value_tbl(1) := g_line_record.value_1;
3580: --
3581: -- Checks whether the upgrade has been performed.
3582: --
3583: if l_bee_iv_upgrade = 'N' THEN
3584: hr_utility.set_location('payplnk.validate_lines',41);
3585: --
3586: -- BEE now handles input value of date in canonical format.
3587: -- However the EE API expects the data in the DD-MON-YYYY format.
3588: -- The DD-MON-YYYY is the default format of the fnd_date.
3610: end;
3611: else
3612: -- bug no. 3734946
3613: /* l_passed_val_tbl(i) := substrb(l_entry_value_tbl(i),1,60);*/
3614: hr_utility.set_location('payplnk.validate_lines',42);
3615: l_passed_val_tbl(i) := l_entry_value_tbl(i);
3616: end if;
3617: --
3618: else
3622: l_uom,
3623: l_lookup_type,
3624: l_value_set_id,
3625: l_input_curr_code),1,60); */
3626: hr_utility.set_location('payplnk.validate_lines',43);
3627: l_passed_val_tbl(i) := convert_internal_to_display(l_entry_value_tbl(i),
3628: l_uom,
3629: l_lookup_type,
3630: l_value_set_id,
3628: l_uom,
3629: l_lookup_type,
3630: l_value_set_id,
3631: l_input_curr_code);
3632: hr_utility.set_location('payplnk.validate_lines',44);
3633: --
3634: end if;
3635: --
3636: else
3649:
3650: end loop;
3651: close csr_table_inp_ids;
3652: --
3653: hr_utility.set_location('payplnk.validate_lines',45);
3654: --
3655: -- If there are no input value ids for the element type or the
3656: -- entry values exceed the number of input value id(s) , raise an error.
3657: --
3668: raise invalid_entry_value;
3669: end if;
3670: end loop;
3671: --
3672: hr_utility.set_location('payplnk.validate_lines', 50);
3673: --
3674: -- Retrieve the link id for the element type and assignment combination.
3675: --
3676: hr_utility.trace('ASS: '||g_line_record.assignment_id);
3682: l_link_id := hr_entry_api.get_link(g_line_record.assignment_id,
3683: g_line_record.element_type_id,
3684: g_line_record.effective_date);
3685: --
3686: hr_utility.set_location('payplnk.validate_lines',55);
3687: --
3688: -- Raise an error if a link does not exist on the effective date.
3689: --
3690: if l_link_id is null then
3705: or l_inpv_uom_tbl(l) = 'H_HHMMSS' or l_inpv_uom_tbl(l) = 'H_DECIMAL1'
3706: or l_inpv_uom_tbl(l) = 'H_DECIMAL2' or l_inpv_uom_tbl(l) = 'H_DECIMAL3'
3707: or l_inpv_uom_tbl(l) = 'HOURS') then
3708: --
3709: hr_utility.set_location('payplnk.validate_lines',53);
3710: --
3711: l_absence_attendance_type_id := null;
3712: --
3713: -- The hours input value may be associated with an Absence
3737: -- exception
3738: -- when no_data_found then null;
3739: -- end;
3740: --
3741: hr_utility.set_location('payplnk.validate_lines',54);
3742: --
3743: if l_absence_attendance_type_id is not null then
3744: --
3745: l_abs_type_tbl(l) := l_absence_attendance_type_id;
3747: l_creator_type := 'A';
3748: --
3749: end if;
3750: --
3751: hr_utility.set_location('payplnk.validate_lines',56);
3752: --
3753: --
3754: -- WW Bug# 282299
3755: -- The hours input value may also be associated with an Accrual Plan.
3767: p_asg_id => g_line_record.assignment_id,
3768: p_sess_date => g_line_record.effective_date,
3769: p_eligible => l_eligible);
3770: --
3771: hr_utility.set_location('payplnk.validate_lines',57);
3772: --
3773: if l_eligible = 'N' then
3774: hr_utility.set_location('payplnk.validate_lines',58);
3775: hr_utility.trace('Assignment is in ineligible period for this accrual plan. ');
3770: --
3771: hr_utility.set_location('payplnk.validate_lines',57);
3772: --
3773: if l_eligible = 'N' then
3774: hr_utility.set_location('payplnk.validate_lines',58);
3775: hr_utility.trace('Assignment is in ineligible period for this accrual plan. ');
3776: hr_utility.set_message(801,'PAY_7853_PDT_INELIG_ACCRUAL');
3777: hr_utility.raise_error;
3778: end if;
3778: end if;
3779: end if;
3780: end loop;
3781: --
3782: hr_utility.set_location('payplnk.validate_lines',59);
3783: --
3784: -- Validate the costing details entered.
3785: --
3786: -- S.Sinha pseudo bug 493304, for performance fix for BT.
3846: end if; -- } no structure
3847: --
3848: -- Retrieve/generate a cost keyflex id for the costing details.
3849: --
3850: hr_utility.set_location('payplnk.validate_lines',60);
3851: --
3852: g_line_record.cost_allocation_keyflex_id :=
3853: hr_entry.maintain_cost_keyflex(
3854: l_cost_allocation_structure,
3892: end if; -- } costable_type N else
3893: --
3894: end if; -- } costing exists
3895: --
3896: hr_utility.set_location('payplnk.validate_lines',65);
3897: --
3898: -- The batch line core validation checks have been carried out
3899: -- successfully, hence the legislative hook checks (if any) and
3900: -- then the user line validation checks can be called.
3904: -- The legislative hook to line validation should be executed here
3905: --
3906: -- call legislative header check hook if required
3907: --
3908: hr_utility.set_location('payplnk.validate_lines',66);
3909: if l_leg_line_check = TRUE then
3910: --
3911: begin
3912: hr_utility.set_location('payplnk.validate_lines',67);
3908: hr_utility.set_location('payplnk.validate_lines',66);
3909: if l_leg_line_check = TRUE then
3910: --
3911: begin
3912: hr_utility.set_location('payplnk.validate_lines',67);
3913: statem := 'BEGIN
3914: pay_'||lower(l_legislation_code)||'_bee.validate_line(:batch_line_id, :valid, :leg_message, :line_changed); END;';
3915: --
3916: if pay_core_utils.get_sql_cursor(statem,sql_curs) then
3931: end if;
3932: --
3933: exception
3934: when others then
3935: hr_utility.set_location('payplnk.validate_lines',68);
3936: --
3937: g_line_error := true;
3938: g_count := g_count + 1;
3939: --
3949: raise line_error_occurred;
3950: end;
3951: --
3952: if l_valid = 1 then
3953: hr_utility.set_location('payplnk.validate_lines',688);
3954: --
3955: g_line_error := true;
3956: g_count := g_count + 1;
3957: --
4015: --
4016: raise line_error_occurred;
4017: end;
4018: --
4019: hr_utility.set_location('payplnk.validate_lines',70);
4020: --
4021: -- If a status has not been returned raise an error.
4022: --
4023: if l_user_status is null then
4112: end if;
4113: end if;
4114: end if;
4115: --
4116: hr_utility.set_location('payplnk.validate_lines',75);
4117: --
4118: -- Batch line has been validated successfully. Attempt to insert/update
4119: -- an element entry into the base tables.
4120: --
4232: p_error_table => l_error_table);
4233: --
4234:
4235: if l_warning_table.COUNT <> 0 then
4236: hr_utility.set_location('payplnk.validate_lines',76);
4237: for l_warning_num in 1..l_warning_table.COUNT loop
4238: hr_utility.trace(l_warning_table(l_warning_num));
4239: if l_warning_table(l_warning_num) = 'EE_CREATED_BY_ABSENCE_API' then
4240: hr_utility.set_location('payplnk.validate_lines',77);
4236: hr_utility.set_location('payplnk.validate_lines',76);
4237: for l_warning_num in 1..l_warning_table.COUNT loop
4238: hr_utility.trace(l_warning_table(l_warning_num));
4239: if l_warning_table(l_warning_num) = 'EE_CREATED_BY_ABSENCE_API' then
4240: hr_utility.set_location('payplnk.validate_lines',77);
4241: absence_entry_already_created := TRUE;
4242: else
4243: g_count := g_count + 1;
4244: hr_utility.set_message(801,l_warning_table(l_warning_num));
4323: end if;
4324: close csr_future_existence;
4325: end if;
4326: --
4327: hr_utility.set_location('payplnk.validate_lines',80);
4328: --
4329: -- An attempt can be made to insert the element entry as all the
4330: -- prerequisite conditions are in place.
4331: --
4332: -- Bug 488335 - new declaration to avoid invalid parameter
4333: -- mode in Oracle 8
4334: g_line_record1 := g_line_record;
4335: begin
4336: payplnk.insert_element_entry(l_link_id,
4337: g_line_record1,
4338: p_asg_act_id,
4339: l_creator_type,
4340: l_absence_attendance_id,
4378: --
4379: l_action_if_exists_chk := true;
4380: end if;
4381: --
4382: hr_utility.set_location('payplnk.validate_lines',85);
4383: --
4384: if (l_multiple_entries_allowed = 'Y') and
4385: (g_header_record.action_if_exists = 'I') then
4386: --
4385: (g_header_record.action_if_exists = 'I') then
4386: --
4387: g_line_record1 := g_line_record;
4388: begin
4389: payplnk.insert_element_entry (l_link_id,
4390: g_line_record1,
4391: p_asg_Act_id,
4392: l_creator_type,
4393: l_absence_attendance_id,
4418: --
4419: raise multiple_entries_not_allowed;
4420: end if;
4421: --
4422: hr_utility.set_location('payplnk.validate_lines',90);
4423: --
4424: if g_header_record.action_if_exists = 'R' then
4425: --
4426: raise element_entry_exists;
4555: end if;
4556: --
4557: end if;
4558: --
4559: hr_utility.set_location('payplnk.validate_lines',95);
4560: --
4561: exception
4562: when no_data_found then
4563: g_line_error := true;
4772: if g_line_error then
4773: raise no_data_found;
4774: end if;
4775: --
4776: hr_utility.set_location('payplnk.validate_lines',500);
4777: --
4778: -- Close the header cursor if it is open.
4779: if csr_bl_header%ISOPEN then
4780: close csr_bl_header;
4780: close csr_bl_header;
4781: end if;
4782: --
4783: --Set the global parameter to enable the triggers.
4784: payplnk.g_payplnk_call := false;
4785: --
4786: /* Do Not continue to transfer even if one line is errored */
4787: if (l_control_err = true or l_header_err = true or (l_chk_asg_lines = 'Y' and
4788: p_process_mode = 'TRANSFER')) then /* Bug 13814081 */
4799: close csr_bl_header;
4800: end if;
4801: rollback to BL;
4802: --Set the global parameter to enable the triggers.
4803: payplnk.g_payplnk_call := false;
4804: hr_utility.set_message(800, 'HR_289719_BEE_LINE_ERROR');
4805: hr_utility.raise_error;
4806: -- raise;
4807: --
4815: end validate_lines;
4816: --
4817: -----------------------------------------------------------------------
4818: -- NAME --
4819: -- payplnk.purge_messages PRIVATE PROCEDURE --
4820: -- --
4821: -- DESCRIPTION --
4822: -- Deletes all messages associated with the batch from the --
4823: -- PAY_MESSAGE_LINES table. --
4844: and (batch_line_status <> 'T' or p_mode='Y');
4845: --
4846: begin
4847: --
4848: hr_utility.set_location('payplnk.purge_messages',5);
4849: --
4850: for pclrec in pcl(p_batch_id) loop
4851: delete from pay_message_lines
4852: where source_type = 'C'
4852: where source_type = 'C'
4853: and source_id = pclrec.batch_control_id;
4854: end loop;
4855: --
4856: hr_utility.set_location('payplnk.purge_messages',10);
4857: --
4858: for pblrec in pbl(p_batch_id) loop
4859: delete from pay_message_lines
4860: where source_type = 'L'
4860: where source_type = 'L'
4861: and source_id = pblrec.batch_line_id;
4862: end loop;
4863: --
4864: hr_utility.set_location('payplnk.purge_messages',20);
4865: --
4866: delete from pay_message_lines
4867: where source_type = 'H'
4868: and source_id = p_batch_id
4872: where pbl.batch_id = p_batch_id
4873: and pbl.batch_line_status = 'T')
4874: or p_mode='Y');
4875: --
4876: hr_utility.set_location('payplnk.purge_messages',25);
4877: --
4878: end purge_messages;
4879: --
4880: ----------------------------------------------------------------------
4878: end purge_messages;
4879: --
4880: ----------------------------------------------------------------------
4881: -- NAME --
4882: -- payplnk.insert_element_entry PRIVATE PROCEDURE --
4883: -- --
4884: -- DESCRIPTION --
4885: -- Inserts an element entry into the PAY_ELEMENT_ENTRIES_F table --
4886: ----------------------------------------------------------------------
4924: l_time_out boolean := FALSE;
4925: --
4926: begin
4927: --
4928: hr_utility.set_location('payplnk.insert_element_entry',5);
4929: --
4930: -- Bug 423237 - Convert passed in reason to lookup_code for api
4931: --
4932: IF l_line_record.reason IS NOT NULL THEN
5122: hr_utility.set_location('GHR Installed....insert_element_entry .. Post ',6);
5123: ghr_history_api.post_update_process;
5124: end if;
5125: --
5126: hr_utility.set_location('payplnk.insert_element_entry',10);
5127: --
5128: end insert_element_entry;
5129: --
5130: --
5129: --
5130: --
5131: ---------------------------------------------------------------------
5132: -- NAME --
5133: -- payplnk.update_element_entry PRIVATE PROCEDURE --
5134: -- --
5135: -- DESCRIPTION --
5136: -- Updates an existing element entry in the PAY_ELEMENT_ENTRIES_F --
5137: -- table. --
5158: l_time_out boolean := FALSE;
5159: --
5160: begin
5161: --
5162: hr_utility.set_location('payplnk.update_element_entry',5);
5163: --
5164: -- Bug 423237 - Convert passed in reason to lookup_code for api
5165: --
5166: IF l_line_record.reason IS NOT NULL THEN
5305: -- for this element
5306:
5307: if (p_creator_type = 'A') then
5308: --
5309: hr_utility.set_location('payplnk.update_element_entry',10);
5310: --
5311: update per_absence_attendances
5312: set batch_id = null
5313: where absence_attendance_id = p_creator_id;
5312: set batch_id = null
5313: where absence_attendance_id = p_creator_id;
5314: else
5315: --
5316: hr_utility.set_location('payplnk.update_element_entry',15);
5317: --
5318: if p_allow_rollback then
5319: --Recurring element of creator type 'H' or 'F' with update and update insert change.
5320: --
5339: end if;
5340: end if;
5341:
5342: --
5343: hr_utility.set_location('payplnk.update_element_entry',20);
5344: --
5345: end update_element_entry;
5346: --
5347: end payplnk;
5343: hr_utility.set_location('payplnk.update_element_entry',20);
5344: --
5345: end update_element_entry;
5346: --
5347: end payplnk;