[Home] [Help]
20: );
21:
22: g_params r_parameters;
23: glb_debug BOOLEAN := FALSE;
24: g_debug BOOLEAN := hr_utility.debug_enabled;
25: -- Used for conditionally enabling hr_utility calls
26: -- variables for storing the old bld blk information
27: t_old_time_seq DBMS_SQL.number_table;
28: t_old_day_seq DBMS_SQL.number_table;
21:
22: g_params r_parameters;
23: glb_debug BOOLEAN := FALSE;
24: g_debug BOOLEAN := hr_utility.debug_enabled;
25: -- Used for conditionally enabling hr_utility calls
26: -- variables for storing the old bld blk information
27: t_old_time_seq DBMS_SQL.number_table;
28: t_old_day_seq DBMS_SQL.number_table;
29: t_old_detail_seq DBMS_SQL.number_table;
267: BEGIN -- translate_it
268: IF g_debug
269: THEN
270: l_proc := g_package || 'translate_it';
271: hr_utility.set_location ('Entering ' || l_proc, 10);
272: END IF;
273:
274: -- placeholder is delimited by [ ], find the delimited string
275: l_start_position := INSTR (p_text, '[', 1, 1) + 1;
279: l_placeholder := SUBSTR (p_text, l_start_position, l_end_position);
280:
281: IF g_debug
282: THEN
283: hr_utility.set_location ('Processing ' || l_proc, 20);
284: END IF;
285:
286: -- predicate is delimited by {}, find the predicate
287: l_start_position := INSTR (p_text, '{', 1, 1) + 1;
291: l_predicate := SUBSTR (p_text, l_start_position, l_end_position);
292:
293: IF g_debug
294: THEN
295: hr_utility.set_location ('Processing ' || l_proc, 30);
296: END IF;
297:
298: -- now that we have the string ensure it is not a reserved keyword.
299: -- if it is then we can leave it be, otherwise we need to map this
319: g_field_mappings_table.FIRST .. g_field_mappings_table.LAST
320: LOOP
321: IF g_debug
322: THEN
323: hr_utility.set_location ('Processing ' || l_proc, 40);
324: END IF;
325:
326: -- check to see if placeholder matches field name
327: IF (g_field_mappings_table (map_cnt).field_name =
336: END LOOP;
337:
338: IF g_debug
339: THEN
340: hr_utility.set_location ('Processing ' || l_proc, 50);
341: END IF;
342:
343: IF (g_params.p_process = 'Projects Retrieval Process')
344: THEN
379: END IF; -- UPPER( l_placeholder
380:
381: IF g_debug
382: THEN
383: hr_utility.set_location ('Processing ' || l_proc, 60);
384: END IF;
385:
386: -- now replace the placeholder with the table dot column expression and remove the predicate all together
387: l_absolute_start_position := INSTR (p_text, '[', 1, 1);
473: BEGIN -- parse it
474: IF g_debug
475: THEN
476: l_proc := g_package || 'parse_it';
477: hr_utility.set_location ('Entering ' || l_proc, 10);
478: END IF;
479:
480: l_text := p_where_clause_blk;
481:
490: );
491:
492: IF g_debug
493: THEN
494: hr_utility.TRACE ('where blk is ' || p_where_clause_blk);
495: hr_utility.TRACE ('where att is ' || p_where_clause_att);
496: END IF;
497: END LOOP;
498: END IF;
491:
492: IF g_debug
493: THEN
494: hr_utility.TRACE ('where blk is ' || p_where_clause_blk);
495: hr_utility.TRACE ('where att is ' || p_where_clause_att);
496: END IF;
497: END LOOP;
498: END IF;
499:
498: END IF;
499:
500: IF g_debug
501: THEN
502: hr_utility.set_location ('Leaving ' || l_proc, 20);
503: END IF;
504:
505: -- using length since the ASCII new line is left when the WHERE has carriage returns
506: IF (LENGTH (LTRIM (RTRIM (p_where_clause_att))) > 5)
743: BEGIN
744:
745: IF g_debug
746: THEN
747: hr_utility.trace('P_start_date '||p_start_date);
748: hr_utility.trace('P_end_date '||p_end_date);
749: END IF;
750:
751: IF p_start_date IS NULL
744:
745: IF g_debug
746: THEN
747: hr_utility.trace('P_start_date '||p_start_date);
748: hr_utility.trace('P_end_date '||p_end_date);
749: END IF;
750:
751: IF p_start_date IS NULL
752: AND p_end_date IS NULL
752: AND p_end_date IS NULL
753: THEN
754: IF g_debug
755: THEN
756: hr_utility.trace(p_sql);
757: END IF;
758: EXECUTE IMMEDIATE p_sql USING p_since_date;
759: ELSIF p_start_date IS NULL
760: AND p_end_date IS NOT NULL
761: THEN
762: l_sql := p_sql|| ' AND tbb.start_time <= :p_end_date ';
763: IF g_debug
764: THEN
765: hr_utility.trace(l_sql);
766: END IF;
767: EXECUTE IMMEDIATE l_sql USING p_since_date, p_end_date ;
768: ELSIF p_start_date IS NOT NULL
769: AND p_end_date IS NULL
770: THEN
771: l_sql := p_sql|| ' AND tbb.stop_time >= :p_start_date ';
772: IF g_debug
773: THEN
774: hr_utility.trace(l_sql);
775: END IF;
776: EXECUTE IMMEDIATE l_sql USING p_since_date, p_start_date ;
777: ELSIF p_start_date IS NOT NULL
778: AND p_end_date IS NOT NULL
780: l_sql := p_sql|| ' AND tbb.stop_time >= :p_start_date ';
781: l_sql := l_sql|| ' AND tbb.start_time <= :p_end_date ';
782: IF g_debug
783: THEN
784: hr_utility.trace(l_sql);
785: END IF;
786: EXECUTE IMMEDIATE l_sql USING p_since_date, p_start_date, p_end_date ;
787: END IF;
788: COMMIT;
864: BEGIN
865: IF g_debug
866: THEN
867: l_proc := g_package || 'generate_resource_sql';
868: hr_utility.TRACE ('in generate resource sql');
869: END IF;
870:
871: -- Bug 9394444
872: -- Description : This bugfix is actually a performance upgrade thru a data model
975: l_range_caveat := TRUE;
976:
977: IF g_debug
978: THEN
979: hr_utility.TRACE ('Using range caveat');
980: END IF;
981: END IF;
982: ELSE
983: CLOSE csr_get_range;
996: );
997:
998: IF g_debug
999: THEN
1000: hr_utility.TRACE ('after parse ');
1001: hr_utility.TRACE (SUBSTR (l_where_blk, 1, 250));
1002: END IF;
1003:
1004: l_where_blk := replace_timecard_string (l_where_blk);
997:
998: IF g_debug
999: THEN
1000: hr_utility.TRACE ('after parse ');
1001: hr_utility.TRACE (SUBSTR (l_where_blk, 1, 250));
1002: END IF;
1003:
1004: l_where_blk := replace_timecard_string (l_where_blk);
1005:
1004: l_where_blk := replace_timecard_string (l_where_blk);
1005:
1006: IF g_debug
1007: THEN
1008: hr_utility.TRACE ('after replace ');
1009: hr_utility.TRACE (SUBSTR (l_where_blk, 1, 250));
1010: END IF;
1011:
1012: IF (g_params.l_using_dates)
1005:
1006: IF g_debug
1007: THEN
1008: hr_utility.TRACE ('after replace ');
1009: hr_utility.TRACE (SUBSTR (l_where_blk, 1, 250));
1010: END IF;
1011:
1012: IF (g_params.l_using_dates)
1013: THEN
1012: IF (g_params.l_using_dates)
1013: THEN
1014: IF g_debug
1015: THEN
1016: hr_utility.TRACE ('Using Dates');
1017: END IF;
1018:
1019: IF (l_range_caveat)
1020: THEN
1049: END IF;
1050: ELSE
1051: IF g_debug
1052: THEN
1053: hr_utility.TRACE ('Not using dates');
1054: END IF;
1055:
1056: IF (l_range_caveat)
1057: THEN
1087: IF (g_params.l_using_dates)
1088: THEN
1089: IF g_debug
1090: THEN
1091: hr_utility.TRACE ('Using Dates');
1092: END IF;
1093:
1094: IF (l_range_caveat)
1095: THEN
1119: END IF;
1120: ELSE
1121: IF g_debug
1122: THEN
1123: hr_utility.TRACE ('Not using dates');
1124: END IF;
1125:
1126: IF (l_range_caveat)
1127: THEN
1154: END IF;
1155:
1156: IF g_debug
1157: THEN
1158: hr_utility.TRACE ('dynamic sql is ');
1159: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1, 250));
1160: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 251, 250));
1161: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 501, 250));
1162: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 751, 250));
1155:
1156: IF g_debug
1157: THEN
1158: hr_utility.TRACE ('dynamic sql is ');
1159: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1, 250));
1160: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 251, 250));
1161: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 501, 250));
1162: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 751, 250));
1163: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1001, 250));
1156: IF g_debug
1157: THEN
1158: hr_utility.TRACE ('dynamic sql is ');
1159: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1, 250));
1160: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 251, 250));
1161: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 501, 250));
1162: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 751, 250));
1163: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1001, 250));
1164: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1251, 250));
1157: THEN
1158: hr_utility.TRACE ('dynamic sql is ');
1159: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1, 250));
1160: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 251, 250));
1161: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 501, 250));
1162: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 751, 250));
1163: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1001, 250));
1164: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1251, 250));
1165: hr_utility.TRACE ( 'FIRST RESOURCE IS : '
1158: hr_utility.TRACE ('dynamic sql is ');
1159: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1, 250));
1160: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 251, 250));
1161: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 501, 250));
1162: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 751, 250));
1163: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1001, 250));
1164: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1251, 250));
1165: hr_utility.TRACE ( 'FIRST RESOURCE IS : '
1166: || TO_CHAR (l_first_resource_id)
1159: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1, 250));
1160: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 251, 250));
1161: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 501, 250));
1162: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 751, 250));
1163: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1001, 250));
1164: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1251, 250));
1165: hr_utility.TRACE ( 'FIRST RESOURCE IS : '
1166: || TO_CHAR (l_first_resource_id)
1167: );
1160: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 251, 250));
1161: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 501, 250));
1162: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 751, 250));
1163: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1001, 250));
1164: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1251, 250));
1165: hr_utility.TRACE ( 'FIRST RESOURCE IS : '
1166: || TO_CHAR (l_first_resource_id)
1167: );
1168: hr_utility.TRACE ( 'LAST RESOURCE IS : '
1161: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 501, 250));
1162: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 751, 250));
1163: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1001, 250));
1164: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1251, 250));
1165: hr_utility.TRACE ( 'FIRST RESOURCE IS : '
1166: || TO_CHAR (l_first_resource_id)
1167: );
1168: hr_utility.TRACE ( 'LAST RESOURCE IS : '
1169: || TO_CHAR (l_last_resource_id)
1164: hr_utility.TRACE (SUBSTR (l_dynamic_sql, 1251, 250));
1165: hr_utility.TRACE ( 'FIRST RESOURCE IS : '
1166: || TO_CHAR (l_first_resource_id)
1167: );
1168: hr_utility.TRACE ( 'LAST RESOURCE IS : '
1169: || TO_CHAR (l_last_resource_id)
1170: );
1171: END IF;
1172:
1216: BEGIN
1217: IF g_debug
1218: THEN
1219: l_proc := g_package || '.insert_rr_resources';
1220: hr_utility.set_location ('Entering ' || l_proc, 10);
1221: END IF;
1222:
1223: OPEN csr_get_rr_id;
1224:
1243: END LOOP;
1244:
1245: IF g_debug
1246: THEN
1247: hr_utility.set_location ('Leaving ' || l_proc, 10);
1248: END IF;
1249: END insert_rr_resources;
1250:
1251: FUNCTION chk_empty
1303:
1304: RETURN FALSE;
1305: END chk_empty;
1306: BEGIN
1307: g_debug := hr_utility.debug_enabled;
1308:
1309: IF g_debug
1310: THEN
1311: l_proc := g_package || 'maintain_ranges';
1308:
1309: IF g_debug
1310: THEN
1311: l_proc := g_package || 'maintain_ranges';
1312: hr_utility.set_location ('Processing ' || l_proc, 10);
1313: END IF;
1314:
1315: -- if the table is empty for the retrieval process
1316: -- then insert the rows
1320: -- exception. If this occurs then we want to requery the table
1321: -- until no exception
1322: IF g_debug
1323: THEN
1324: hr_utility.TRACE ('Entering maintain_ranges');
1325: END IF;
1326:
1327: WHILE l_not_maintained_ranges
1328: LOOP
1327: WHILE l_not_maintained_ranges
1328: LOOP
1329: IF g_debug
1330: THEN
1331: hr_utility.set_location ('Processing ' || l_proc, 20);
1332: END IF;
1333:
1334: BEGIN
1335: IF g_debug
1333:
1334: BEGIN
1335: IF g_debug
1336: THEN
1337: hr_utility.TRACE ('Locking table');
1338: END IF;
1339:
1340: IF NOT hxc_generic_retrieval_pkg.g_in_loop
1341: THEN
1344: IN EXCLUSIVE MODE NOWAIT;
1345:
1346: IF g_debug
1347: THEN
1348: hr_utility.TRACE ('Before chk empty');
1349: END IF;
1350:
1351: IF (chk_empty)
1352: THEN
1359: l_chunk_size := g_params.transfer_batch_size;
1360:
1361: IF g_debug
1362: THEN
1363: hr_utility.TRACE ( 'Chunk Size Profile Value is '
1364: || TO_CHAR (l_chunk_size)
1365: );
1366: END IF;
1367:
1381: END IF;
1382:
1383: IF g_debug
1384: THEN
1385: hr_utility.TRACE ('Inserting rows');
1386: END IF;
1387:
1388: -- insert rows
1389: IF g_debug
1387:
1388: -- insert rows
1389: IF g_debug
1390: THEN
1391: hr_utility.TRACE ('using dynamic SQL for resource list');
1392: END IF;
1393:
1394: -- use dynamic SQL BULK FETCH to generate resource list
1395: l_rows_fetched := l_chunk_size;
1413: WHILE (l_rows_fetched = l_chunk_size)
1414: LOOP
1415: IF g_debug
1416: THEN
1417: hr_utility.TRACE ('in loop');
1418: END IF;
1419:
1420: l_rows_fetched := DBMS_SQL.fetch_rows (l_csr);
1421:
1420: l_rows_fetched := DBMS_SQL.fetch_rows (l_csr);
1421:
1422: IF g_debug
1423: THEN
1424: hr_utility.TRACE ( 'rows fetched is '
1425: || TO_CHAR (l_rows_fetched)
1426: );
1427: END IF;
1428:
1429: IF (l_rows_fetched = 0 AND t_range_start.COUNT = 0)
1430: THEN
1431: IF g_debug
1432: THEN
1433: hr_utility.TRACE ('No ranges !!!');
1434: END IF;
1435:
1436: DBMS_SQL.close_cursor (l_csr);
1437: RAISE e_no_ranges;
1450: x := x + 1;
1451:
1452: IF g_debug
1453: THEN
1454: hr_utility.TRACE ( 'range_start is '
1455: || TO_CHAR (l_range_start)
1456: );
1457: hr_utility.TRACE ( 'range_stop is '
1458: || TO_CHAR (l_range_stop)
1453: THEN
1454: hr_utility.TRACE ( 'range_start is '
1455: || TO_CHAR (l_range_start)
1456: );
1457: hr_utility.TRACE ( 'range_stop is '
1458: || TO_CHAR (l_range_stop)
1459: );
1460: END IF;
1461:
1467: t_where_clause (x) := g_params.p_where_clause;
1468:
1469: IF g_debug
1470: THEN
1471: hr_utility.TRACE ( 'loop conditions is '
1472: || TO_CHAR (l_rows_fetched)
1473: || ':'
1474: || TO_CHAR (l_chunk_size)
1475: );
1480: END LOOP; -- dynamic fetch of resources
1481:
1482: IF g_debug
1483: THEN
1484: hr_utility.TRACE ('Leaving loop');
1485: END IF;
1486:
1487: DBMS_SQL.close_cursor (l_csr);
1488: -- now insert retrieval ranges
1502: );
1503:
1504: IF g_debug
1505: THEN
1506: hr_utility.TRACE ('After bulk insert');
1507: END IF;
1508:
1509: l_not_maintained_ranges := FALSE;
1510: END IF; -- chk_empty
1510: END IF; -- chk_empty
1511:
1512: IF g_debug
1513: THEN
1514: hr_utility.set_location ('Processing ' || l_proc, 30);
1515: END IF;
1516: END IF; -- NOT hxc_generic_retrieval_pkg.G_IN_LOOP
1517:
1518: IF g_debug
1516: END IF; -- NOT hxc_generic_retrieval_pkg.G_IN_LOOP
1517:
1518: IF g_debug
1519: THEN
1520: hr_utility.set_location ('Processing ' || l_proc, 40);
1521: hr_utility.TRACE ('About to get range value');
1522: END IF;
1523:
1524: -- if the table is not empty then select the next row, lock it
1517:
1518: IF g_debug
1519: THEN
1520: hr_utility.set_location ('Processing ' || l_proc, 40);
1521: hr_utility.TRACE ('About to get range value');
1522: END IF;
1523:
1524: -- if the table is not empty then select the next row, lock it
1525: -- set the range values and update the row
1531: IF (csr_get_next_range%FOUND)
1532: THEN
1533: IF g_debug
1534: THEN
1535: hr_utility.TRACE ('FOUND range value');
1536: END IF;
1537:
1538: -- maintain range
1539: UPDATE hxc_retrieval_ranges rr
1548: l_pkg_retrieval_range_id := r_range.retrieval_range_id;
1549: ELSE
1550: IF g_debug
1551: THEN
1552: hr_utility.TRACE ('NOT FOUND range value');
1553: END IF;
1554:
1555: -- no more ranges
1556:
1563: END IF;
1564:
1565: IF g_debug
1566: THEN
1567: hr_utility.set_location ('Processing ' || l_proc, 50);
1568: END IF;
1569:
1570: CLOSE csr_get_next_range;
1571:
1574: EXCEPTION
1575: WHEN OTHERS
1576: THEN
1577: -- Bug 9394444
1578: hr_utility.trace(dbms_utility.format_error_backtrace);
1579: IF g_debug
1580: THEN
1581: hr_utility.TRACE ('sqlerrm is ' || SQLERRM);
1582: hr_utility.TRACE ('sqlcode is ' || SQLCODE);
1577: -- Bug 9394444
1578: hr_utility.trace(dbms_utility.format_error_backtrace);
1579: IF g_debug
1580: THEN
1581: hr_utility.TRACE ('sqlerrm is ' || SQLERRM);
1582: hr_utility.TRACE ('sqlcode is ' || SQLCODE);
1583: END IF;
1584:
1585: IF (SQLCODE = '-54')
1578: hr_utility.trace(dbms_utility.format_error_backtrace);
1579: IF g_debug
1580: THEN
1581: hr_utility.TRACE ('sqlerrm is ' || SQLERRM);
1582: hr_utility.TRACE ('sqlcode is ' || SQLCODE);
1583: END IF;
1584:
1585: IF (SQLCODE = '-54')
1586: THEN
1585: IF (SQLCODE = '-54')
1586: THEN
1587: IF g_debug
1588: THEN
1589: hr_utility.TRACE ('TABLE LOCKED!!!!');
1590: END IF;
1591:
1592: l_cnt := l_cnt + 1;
1593: -- wait for 30 seconds before attempting to lock again
1598: END; -- anonymous PL/SQL block to trap lock exception
1599:
1600: IF g_debug
1601: THEN
1602: hr_utility.set_location ('Processing ' || l_proc, 60);
1603: END IF;
1604:
1605: IF (l_cnt > 60)
1606: THEN
1612: END IF;
1613:
1614: IF g_debug
1615: THEN
1616: hr_utility.set_location ('Processing ' || l_proc, 70);
1617: END IF;
1618: END LOOP;
1619: END maintain_ranges;
1620:
1641: BEGIN
1642: IF g_debug
1643: THEN
1644: l_proc := g_package || 'maintain_chunks';
1645: hr_utility.set_location ('Entering ' || l_proc, 10);
1646: END IF;
1647:
1648: IF (g_params.p_process IN
1649: ('Projects Retrieval Process',
1654: )
1655: THEN
1656: IF g_debug
1657: THEN
1658: hr_utility.set_location ('Entering ' || l_proc, 20);
1659: END IF;
1660:
1661: maintain_ranges (p_process_id => g_retrieval_process_id,
1662: p_range_start => l_pkg_range_start,
1665: );
1666:
1667: IF g_debug
1668: THEN
1669: hr_utility.set_location ('Entering ' || l_proc, 50);
1670: END IF;
1671: ELSE
1672: IF g_debug
1673: THEN
1670: END IF;
1671: ELSE
1672: IF g_debug
1673: THEN
1674: hr_utility.set_location ('Entering ' || l_proc, 80);
1675: END IF;
1676:
1677: -- not the Project Retrieval Process
1678: -- set range to be full range
1681: END IF; -- IF ( g_params.p_process = 'Projects Retrieval Process')
1682:
1683: IF g_debug
1684: THEN
1685: hr_utility.TRACE ('');
1686: hr_utility.TRACE ('******* GLOBALS AFTER MAINTAIN CHUNKS ********');
1687: END IF;
1688:
1689: IF (g_in_loop)
1682:
1683: IF g_debug
1684: THEN
1685: hr_utility.TRACE ('');
1686: hr_utility.TRACE ('******* GLOBALS AFTER MAINTAIN CHUNKS ********');
1687: END IF;
1688:
1689: IF (g_in_loop)
1690: THEN
1689: IF (g_in_loop)
1690: THEN
1691: IF g_debug
1692: THEN
1693: hr_utility.TRACE ('G_IN_LOOP is TRUE');
1694: END IF;
1695: ELSE
1696: IF g_debug
1697: THEN
1694: END IF;
1695: ELSE
1696: IF g_debug
1697: THEN
1698: hr_utility.TRACE ('G_IN_LOOP is FALSE');
1699: END IF;
1700: END IF;
1701:
1702: IF (g_last_chunk)
1702: IF (g_last_chunk)
1703: THEN
1704: IF g_debug
1705: THEN
1706: hr_utility.TRACE ('G_LAST_CHUNK is TRUE');
1707: END IF;
1708: ELSE
1709: IF g_debug
1710: THEN
1707: END IF;
1708: ELSE
1709: IF g_debug
1710: THEN
1711: hr_utility.TRACE ('G_LAST_CHUNK is FALSE');
1712: END IF;
1713: END IF;
1714:
1715: IF g_debug
1713: END IF;
1714:
1715: IF g_debug
1716: THEN
1717: hr_utility.TRACE ('l_range_start is ' || TO_CHAR (l_pkg_range_start));
1718: hr_utility.TRACE ('l_range_stop is ' || TO_CHAR (l_pkg_range_stop));
1719: hr_utility.TRACE ( 'l retrieval range id is '
1720: || TO_CHAR (l_pkg_retrieval_range_id)
1721: );
1714:
1715: IF g_debug
1716: THEN
1717: hr_utility.TRACE ('l_range_start is ' || TO_CHAR (l_pkg_range_start));
1718: hr_utility.TRACE ('l_range_stop is ' || TO_CHAR (l_pkg_range_stop));
1719: hr_utility.TRACE ( 'l retrieval range id is '
1720: || TO_CHAR (l_pkg_retrieval_range_id)
1721: );
1722: END IF;
1715: IF g_debug
1716: THEN
1717: hr_utility.TRACE ('l_range_start is ' || TO_CHAR (l_pkg_range_start));
1718: hr_utility.TRACE ('l_range_stop is ' || TO_CHAR (l_pkg_range_stop));
1719: hr_utility.TRACE ( 'l retrieval range id is '
1720: || TO_CHAR (l_pkg_retrieval_range_id)
1721: );
1722: END IF;
1723: END maintain_chunks;
1941: END IF;
1942:
1943: IF g_debug
1944: THEN
1945: hr_utility.TRACE ('Entering populate_ret_range_blks');
1946: END IF;
1947:
1948: -- only use temp table for processes which loop
1949: IF (g_params.p_process IN
1955: )
1956: THEN
1957: IF g_debug
1958: THEN
1959: hr_utility.TRACE ('starting to build query');
1960: END IF;
1961:
1962: IF (g_params.l_using_dates)
1963: THEN
1962: IF (g_params.l_using_dates)
1963: THEN
1964: IF g_debug
1965: THEN
1966: hr_utility.TRACE ('Using Dates');
1967: END IF;
1968:
1969: l_start_date :=
1970: NVL (g_params.p_start_date, hr_general.start_of_time);
1972: END IF;
1973:
1974: IF g_debug
1975: THEN
1976: hr_utility.TRACE ('About to delete rows');
1977: END IF;
1978:
1979: DELETE FROM hxc_retrieval_range_blks;
1980:
2024: END IF;
2025:
2026: IF g_debug
2027: THEN
2028: hr_utility.TRACE ('dynamic ret range blk sql is ');
2029: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1, 250));
2030: hr_utility.TRACE (SUBSTR (l_ret_range_query, 251, 250));
2031: hr_utility.TRACE (SUBSTR (l_ret_range_query, 501, 250));
2032: hr_utility.TRACE (SUBSTR (l_ret_range_query, 751, 250));
2025:
2026: IF g_debug
2027: THEN
2028: hr_utility.TRACE ('dynamic ret range blk sql is ');
2029: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1, 250));
2030: hr_utility.TRACE (SUBSTR (l_ret_range_query, 251, 250));
2031: hr_utility.TRACE (SUBSTR (l_ret_range_query, 501, 250));
2032: hr_utility.TRACE (SUBSTR (l_ret_range_query, 751, 250));
2033: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1001, 250));
2026: IF g_debug
2027: THEN
2028: hr_utility.TRACE ('dynamic ret range blk sql is ');
2029: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1, 250));
2030: hr_utility.TRACE (SUBSTR (l_ret_range_query, 251, 250));
2031: hr_utility.TRACE (SUBSTR (l_ret_range_query, 501, 250));
2032: hr_utility.TRACE (SUBSTR (l_ret_range_query, 751, 250));
2033: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1001, 250));
2034: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1251, 250));
2027: THEN
2028: hr_utility.TRACE ('dynamic ret range blk sql is ');
2029: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1, 250));
2030: hr_utility.TRACE (SUBSTR (l_ret_range_query, 251, 250));
2031: hr_utility.TRACE (SUBSTR (l_ret_range_query, 501, 250));
2032: hr_utility.TRACE (SUBSTR (l_ret_range_query, 751, 250));
2033: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1001, 250));
2034: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1251, 250));
2035: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1501, 250));
2028: hr_utility.TRACE ('dynamic ret range blk sql is ');
2029: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1, 250));
2030: hr_utility.TRACE (SUBSTR (l_ret_range_query, 251, 250));
2031: hr_utility.TRACE (SUBSTR (l_ret_range_query, 501, 250));
2032: hr_utility.TRACE (SUBSTR (l_ret_range_query, 751, 250));
2033: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1001, 250));
2034: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1251, 250));
2035: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1501, 250));
2036: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1751, 250));
2029: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1, 250));
2030: hr_utility.TRACE (SUBSTR (l_ret_range_query, 251, 250));
2031: hr_utility.TRACE (SUBSTR (l_ret_range_query, 501, 250));
2032: hr_utility.TRACE (SUBSTR (l_ret_range_query, 751, 250));
2033: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1001, 250));
2034: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1251, 250));
2035: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1501, 250));
2036: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1751, 250));
2037: END IF;
2030: hr_utility.TRACE (SUBSTR (l_ret_range_query, 251, 250));
2031: hr_utility.TRACE (SUBSTR (l_ret_range_query, 501, 250));
2032: hr_utility.TRACE (SUBSTR (l_ret_range_query, 751, 250));
2033: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1001, 250));
2034: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1251, 250));
2035: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1501, 250));
2036: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1751, 250));
2037: END IF;
2038:
2031: hr_utility.TRACE (SUBSTR (l_ret_range_query, 501, 250));
2032: hr_utility.TRACE (SUBSTR (l_ret_range_query, 751, 250));
2033: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1001, 250));
2034: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1251, 250));
2035: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1501, 250));
2036: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1751, 250));
2037: END IF;
2038:
2039: insert_query (l_ret_range_query, 'RET_RANGE_BLKS');
2032: hr_utility.TRACE (SUBSTR (l_ret_range_query, 751, 250));
2033: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1001, 250));
2034: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1251, 250));
2035: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1501, 250));
2036: hr_utility.TRACE (SUBSTR (l_ret_range_query, 1751, 250));
2037: END IF;
2038:
2039: insert_query (l_ret_range_query, 'RET_RANGE_BLKS');
2040: -- now fetch and insert the rows
2146: END IF; -- check retrieval process
2147: /*
2148:
2149: if g_debug then
2150: hr_utility.trace('Here is the table ');
2151: end if;
2152: open csr_get_ret_Range_blks;
2153:
2154: fetch csr_get_ret_range_blks into l_ret_range_rec;
2156: WHILE csr_get_ret_range_blks%FOUND
2157: LOOP
2158:
2159: if g_debug then
2160: hr_utility.trace('ret range blks is '||l_ret_range_rec.tbb_id||':'||l_ret_range_rec.tbb_ovn);
2161: end if;
2162:
2163: fetch csr_get_ret_range_blks into l_ret_range_rec;
2164:
2264: l_end_date DATE;
2265: BEGIN
2266: IF g_debug
2267: THEN
2268: hr_utility.TRACE ('Entering populate_max_ovn');
2269: END IF;
2270:
2271: -- clear out the table
2272: DELETE FROM hxc_max_ovn;
2283:
2284: LOOP
2285: IF g_debug
2286: THEN
2287: hr_utility.TRACE ('in max ovn loop');
2288: END IF;
2289:
2290: FETCH csr_get_max_ovn_rrb
2291: BULK COLLECT INTO t_max_ovn_bb_id, t_max_ovn LIMIT 100;
2291: BULK COLLECT INTO t_max_ovn_bb_id, t_max_ovn LIMIT 100;
2292:
2293: IF g_debug
2294: THEN
2295: hr_utility.TRACE ( 'fetch count is '
2296: || TO_CHAR (t_max_ovn_bb_id.COUNT)
2297: );
2298: END IF;
2299:
2355:
2356: LOOP
2357: IF g_debug
2358: THEN
2359: hr_utility.TRACE ('in max ovn loop');
2360: END IF;
2361:
2362: FETCH csr_get_max_ovn
2363: BULK COLLECT INTO t_max_ovn_bb_id, t_max_ovn LIMIT 100;
2363: BULK COLLECT INTO t_max_ovn_bb_id, t_max_ovn LIMIT 100;
2364:
2365: IF g_debug
2366: THEN
2367: hr_utility.TRACE ( 'fetch count is '
2368: || TO_CHAR (t_max_ovn_bb_id.COUNT)
2369: );
2370: END IF;
2371:
2372: IF (t_max_ovn_bb_id.COUNT <> 0)
2373: THEN
2374: IF g_debug
2375: THEN
2376: hr_utility.TRACE ('inserting');
2377: END IF;
2378:
2379: FORALL x IN t_max_ovn_bb_id.FIRST .. t_max_ovn_bb_id.LAST
2380: INSERT INTO hxc_max_ovn
2399:
2400: fetch csr_get_max_ovn_debug into l_max_ovn;
2401:
2402: if g_debug then
2403: hr_utility.trace(l_max_ovn.ovn);
2404: end if;
2405:
2406: exit when csr_get_max_ovn_debug%NOTFOUND;
2407:
2450: BEGIN -- audit transaction
2451: IF g_debug
2452: THEN
2453: l_proc := g_package || 'audit_transaction';
2454: hr_utility.set_location ('Entering ' || l_proc, 10);
2455: hr_utility.TRACE ('Audit Transaction Params');
2456: hr_utility.TRACE ('p_mode is ' || p_mode);
2457: hr_utility.TRACE ( 'p_transaction_process_id is '
2458: || TO_CHAR (p_transaction_process_id)
2451: IF g_debug
2452: THEN
2453: l_proc := g_package || 'audit_transaction';
2454: hr_utility.set_location ('Entering ' || l_proc, 10);
2455: hr_utility.TRACE ('Audit Transaction Params');
2456: hr_utility.TRACE ('p_mode is ' || p_mode);
2457: hr_utility.TRACE ( 'p_transaction_process_id is '
2458: || TO_CHAR (p_transaction_process_id)
2459: );
2452: THEN
2453: l_proc := g_package || 'audit_transaction';
2454: hr_utility.set_location ('Entering ' || l_proc, 10);
2455: hr_utility.TRACE ('Audit Transaction Params');
2456: hr_utility.TRACE ('p_mode is ' || p_mode);
2457: hr_utility.TRACE ( 'p_transaction_process_id is '
2458: || TO_CHAR (p_transaction_process_id)
2459: );
2460: hr_utility.TRACE ('p_status is ' || p_status);
2453: l_proc := g_package || 'audit_transaction';
2454: hr_utility.set_location ('Entering ' || l_proc, 10);
2455: hr_utility.TRACE ('Audit Transaction Params');
2456: hr_utility.TRACE ('p_mode is ' || p_mode);
2457: hr_utility.TRACE ( 'p_transaction_process_id is '
2458: || TO_CHAR (p_transaction_process_id)
2459: );
2460: hr_utility.TRACE ('p_status is ' || p_status);
2461: hr_utility.TRACE ('p_description is ' || p_description);
2456: hr_utility.TRACE ('p_mode is ' || p_mode);
2457: hr_utility.TRACE ( 'p_transaction_process_id is '
2458: || TO_CHAR (p_transaction_process_id)
2459: );
2460: hr_utility.TRACE ('p_status is ' || p_status);
2461: hr_utility.TRACE ('p_description is ' || p_description);
2462: hr_utility.TRACE ( 'Global Transaction ID is '
2463: || TO_CHAR
2464: (hxc_generic_retrieval_pkg.g_transaction_id)
2457: hr_utility.TRACE ( 'p_transaction_process_id is '
2458: || TO_CHAR (p_transaction_process_id)
2459: );
2460: hr_utility.TRACE ('p_status is ' || p_status);
2461: hr_utility.TRACE ('p_description is ' || p_description);
2462: hr_utility.TRACE ( 'Global Transaction ID is '
2463: || TO_CHAR
2464: (hxc_generic_retrieval_pkg.g_transaction_id)
2465: );
2458: || TO_CHAR (p_transaction_process_id)
2459: );
2460: hr_utility.TRACE ('p_status is ' || p_status);
2461: hr_utility.TRACE ('p_description is ' || p_description);
2462: hr_utility.TRACE ( 'Global Transaction ID is '
2463: || TO_CHAR
2464: (hxc_generic_retrieval_pkg.g_transaction_id)
2465: );
2466: END IF;
2476: IF (hxc_generic_retrieval_pkg.g_transaction_id IS NOT NULL)
2477: THEN
2478: IF g_debug
2479: THEN
2480: hr_utility.set_location ('Processing ' || l_proc, 7);
2481: END IF;
2482:
2483: -- already inserted - lets update it!
2484: UPDATE hxc_transactions
2487: WHERE transaction_id = hxc_generic_retrieval_pkg.g_transaction_id;
2488: ELSE
2489: IF g_debug
2490: THEN
2491: hr_utility.set_location ('Processing ' || l_proc, 20);
2492: END IF;
2493:
2494: OPEN csr_get_tx_id;
2495:
2499: CLOSE csr_get_tx_id;
2500:
2501: IF g_debug
2502: THEN
2503: hr_utility.set_location ('Processing ' || l_proc, 30);
2504: END IF;
2505:
2506: INSERT INTO hxc_transactions
2507: (transaction_id,
2518: );
2519:
2520: IF g_debug
2521: THEN
2522: hr_utility.set_location ('Processing ' || l_proc, 40);
2523: END IF;
2524: END IF; -- transaction id IS NULL
2525:
2526: IF l_error_max <> 0
2526: IF l_error_max <> 0
2527: THEN
2528: IF g_debug
2529: THEN
2530: hr_utility.set_location ('Processing ' || l_proc, 85);
2531: END IF;
2532:
2533: -- now let's bulk fetch all the transaction detail id
2534: OPEN csr_get_tx_detail_id (l_error_max);
2542: l_temp_transaction_id;
2543:
2544: IF g_debug
2545: THEN
2546: hr_utility.set_location ('Processing ' || l_proc, 90);
2547: END IF;
2548:
2549: FORALL tx_error IN hxc_generic_retrieval_pkg.t_tx_error_transaction_id.FIRST .. hxc_generic_retrieval_pkg.t_tx_error_transaction_id.LAST
2550: INSERT INTO hxc_transaction_details
2579: hxc_generic_retrieval_pkg.t_tx_error_exception.DELETE;
2580:
2581: IF g_debug
2582: THEN
2583: hr_utility.set_location ('Processing ' || l_proc, 100);
2584: END IF;
2585: END IF; -- l_error_max <> 0
2586: ELSIF (p_mode = 'U') -- update transactions
2587: THEN
2586: ELSIF (p_mode = 'U') -- update transactions
2587: THEN
2588: IF g_debug
2589: THEN
2590: hr_utility.set_location ('Processing ' || l_proc, 110);
2591: END IF;
2592:
2593: OPEN csr_get_tx_id;
2594:
2624: END IF;
2625:
2626: IF g_debug
2627: THEN
2628: hr_utility.set_location ('Processing ' || l_proc, 130);
2629: END IF;
2630:
2631: -- check to see if any tx time details
2632: IF l_time_max <> 0
2643: l_temp_transaction_id;
2644:
2645: IF g_debug
2646: THEN
2647: hr_utility.set_location ('Processing ' || l_proc, 50);
2648: END IF;
2649:
2650: FORALL tx_detail IN hxc_generic_retrieval_pkg.t_tx_time_transaction_id.FIRST .. hxc_generic_retrieval_pkg.t_tx_time_transaction_id.LAST
2651: INSERT INTO hxc_transaction_details
2685: l_temp_transaction_id;
2686:
2687: IF g_debug
2688: THEN
2689: hr_utility.set_location ('Processing ' || l_proc, 60);
2690: END IF;
2691:
2692: FORALL tx_detail IN hxc_generic_retrieval_pkg.t_tx_day_transaction_id.FIRST .. hxc_generic_retrieval_pkg.t_tx_day_transaction_id.LAST
2693: INSERT INTO hxc_transaction_details
2727: l_temp_transaction_id;
2728:
2729: IF g_debug
2730: THEN
2731: hr_utility.set_location ('Processing ' || l_proc, 70);
2732: END IF;
2733:
2734: FORALL tx_detail IN hxc_generic_retrieval_pkg.t_tx_detail_transaction_id.FIRST .. hxc_generic_retrieval_pkg.t_tx_detail_transaction_id.LAST
2735: INSERT INTO hxc_transaction_details
2754: );
2755:
2756: IF g_debug
2757: THEN
2758: hr_utility.set_location ('Processing ' || l_proc, 75);
2759: END IF;
2760: END IF; -- l_detail_max <> 0
2761:
2762: IF g_debug
2760: END IF; -- l_detail_max <> 0
2761:
2762: IF g_debug
2763: THEN
2764: hr_utility.set_location ('Processing ' || l_proc, 80);
2765: END IF;
2766:
2767: IF l_error_max <> 0
2768: THEN
2767: IF l_error_max <> 0
2768: THEN
2769: IF g_debug
2770: THEN
2771: hr_utility.set_location ('Processing ' || l_proc, 85);
2772: END IF;
2773:
2774: -- now let's bulk fetch all the transaction detail id
2775: OPEN csr_get_tx_detail_id (l_error_max);
2783: l_temp_transaction_id;
2784:
2785: IF g_debug
2786: THEN
2787: hr_utility.set_location ('Processing ' || l_proc, 90);
2788: END IF;
2789:
2790: FORALL tx_error IN hxc_generic_retrieval_pkg.t_tx_error_transaction_id.FIRST .. hxc_generic_retrieval_pkg.t_tx_error_transaction_id.LAST
2791: INSERT INTO hxc_transaction_details
2845: hxc_generic_retrieval_pkg.t_tx_error_exception.DELETE;
2846:
2847: IF g_debug
2848: THEN
2849: hr_utility.set_location ('Processing ' || l_proc, 100);
2850: END IF;
2851: END IF; -- l_error_max <> 0
2852: END IF; -- p_mode
2853:
2852: END IF; -- p_mode
2853:
2854: IF g_debug
2855: THEN
2856: hr_utility.TRACE ('Committing !!!');
2857: END IF;
2858:
2859: COMMIT;
2860: END audit_transaction;
2903: BEGIN -- chk_retrieval_process
2904: IF g_debug
2905: THEN
2906: l_proc := g_package || 'chk_retrieval_process';
2907: hr_utility.set_location ('Entering ' || l_proc, 10);
2908: END IF;
2909:
2910: IF (p_retrieval_process = 'Apply Schedule Rules')
2911: THEN
2945: END IF;
2946:
2947: IF g_debug
2948: THEN
2949: hr_utility.set_location ('Leaving ' || l_proc, 30);
2950: END IF;
2951: --
2952: END chk_retrieval_process;
2953:
4796: BEGIN -- query_it
4797: IF g_debug
4798: THEN
4799: l_proc := g_package || 'query_it';
4800: hr_utility.set_location ('Entering ' || l_proc, 10);
4801: END IF;
4802:
4803: OPEN get_session_number_format;
4804: FETCH get_session_number_format into l_number_format;
4808: l_rows_fetched := l_max_array_size;
4809:
4810: IF g_debug
4811: THEN
4812: hr_utility.set_location ('Processing ' || l_proc, 20);
4813: END IF;
4814:
4815: l_csr := DBMS_SQL.open_cursor;
4816:
4815: l_csr := DBMS_SQL.open_cursor;
4816:
4817: IF g_debug
4818: THEN
4819: hr_utility.set_location ('Processing ' || l_proc, 30);
4820: END IF;
4821:
4822: DBMS_SQL.parse (l_csr, l_query_text, DBMS_SQL.native);
4823:
4845:
4846: -- bind the start and end date parameters
4847: IF g_debug
4848: THEN
4849: hr_utility.set_location ('Processing ' || l_proc, 35);
4850: END IF;
4851:
4852: IF ( ( g_params.p_start_date IS NOT NULL
4853: AND g_params.p_end_date IS NOT NULL
4864: END IF;
4865:
4866: IF g_debug
4867: THEN
4868: hr_utility.set_location ('Processing ' || l_proc, 37);
4869: END IF;
4870:
4871: IF (g_params.p_rerun_flag = 'Y')
4872: THEN
4878:
4879: -- define arrays for each item in the select list
4880: IF g_debug
4881: THEN
4882: hr_utility.set_location ('Processing ' || l_proc, 40);
4883: END IF;
4884:
4885: DBMS_SQL.define_array (c => l_csr,
4886: POSITION => 1,
5262: );
5263:
5264: IF g_debug
5265: THEN
5266: hr_utility.set_location ('Processing ' || l_proc, 50);
5267: END IF;
5268:
5269: l_dummy := DBMS_SQL.EXECUTE (l_csr);
5270:
5269: l_dummy := DBMS_SQL.EXECUTE (l_csr);
5270:
5271: IF g_debug
5272: THEN
5273: hr_utility.set_location ('Processing ' || l_proc, 60);
5274: END IF;
5275:
5276: -- loop to ensure we fetch all the rows
5277: WHILE (l_rows_fetched = l_max_array_size)
5287: put_log(' This is an empty chunk : No records relevant for these resources ');
5288: put_log(' ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
5289: IF g_debug
5290: THEN
5291: hr_utility.trace('This is an empty chunk ');
5292: END IF;
5293: RAISE e_no_timecards;
5294: END IF;
5295:
5551: DBMS_SQL.close_cursor (l_csr);
5552:
5553: IF g_debug
5554: THEN
5555: hr_utility.set_location ('Processing ' || l_proc, 110);
5556: END IF;
5557:
5558: IF (g_params.p_incremental = 'N')
5559: THEN
5583:
5584: -- initialise tables
5585: IF g_debug
5586: THEN
5587: hr_utility.TRACE ('Params to parse_resources......');
5588: hr_utility.TRACE ('Process id is '
5589: || TO_CHAR (g_retrieval_process_id)
5590: );
5591: hr_utility.TRACE ( 'Time Recipient id is '
5584: -- initialise tables
5585: IF g_debug
5586: THEN
5587: hr_utility.TRACE ('Params to parse_resources......');
5588: hr_utility.TRACE ('Process id is '
5589: || TO_CHAR (g_retrieval_process_id)
5590: );
5591: hr_utility.TRACE ( 'Time Recipient id is '
5592: || TO_CHAR (g_retrieval_tr_id)
5587: hr_utility.TRACE ('Params to parse_resources......');
5588: hr_utility.TRACE ('Process id is '
5589: || TO_CHAR (g_retrieval_process_id)
5590: );
5591: hr_utility.TRACE ( 'Time Recipient id is '
5592: || TO_CHAR (g_retrieval_tr_id)
5593: );
5594: END IF;
5595:
5606: );
5607:
5608: IF g_debug
5609: THEN
5610: hr_utility.TRACE (' *********** - GLOBAL TABLE INFO ************ ');
5611: END IF;
5612:
5613: l_count := hxc_generic_retrieval_utils.g_resources.COUNT;
5614:
5613: l_count := hxc_generic_retrieval_utils.g_resources.COUNT;
5614:
5615: IF g_debug
5616: THEN
5617: hr_utility.TRACE ('Resources : ' || TO_CHAR (l_count));
5618: END IF;
5619:
5620: l_count := t_bb.COUNT;
5621:
5620: l_count := t_bb.COUNT;
5621:
5622: IF g_debug
5623: THEN
5624: hr_utility.TRACE ('master bld blks : ' || TO_CHAR (l_count));
5625: END IF;
5626:
5627: l_count := l_prefs.COUNT;
5628:
5627: l_count := l_prefs.COUNT;
5628:
5629: IF g_debug
5630: THEN
5631: hr_utility.TRACE ('Prefs : ' || TO_CHAR (l_count));
5632: END IF;
5633:
5634: l_count := l_ret_rules.COUNT;
5635:
5634: l_count := l_ret_rules.COUNT;
5635:
5636: IF g_debug
5637: THEN
5638: hr_utility.TRACE ('Retrieval Rules : ' || TO_CHAR (l_count));
5639: END IF;
5640:
5641: l_count := l_rtr_outcomes.COUNT;
5642:
5641: l_count := l_rtr_outcomes.COUNT;
5642:
5643: IF g_debug
5644: THEN
5645: hr_utility.TRACE ('Ret Rule Outcomes : ' || TO_CHAR (l_count));
5646: END IF;
5647:
5648: l_count := l_errors.COUNT;
5649:
5648: l_count := l_errors.COUNT;
5649:
5650: IF g_debug
5651: THEN
5652: hr_utility.TRACE ('Errors : ' || TO_CHAR (l_count));
5653: END IF;
5654:
5655: -- now loop through the table and populate the building block table for those bld blks which have
5656: -- changed
5655: -- now loop through the table and populate the building block table for those bld blks which have
5656: -- changed
5657: IF g_debug
5658: THEN
5659: hr_utility.TRACE ('');
5660: hr_utility.TRACE ('****** Populating Global PL/SQL tables *******');
5661: hr_utility.TRACE ('');
5662: END IF;
5663:
5656: -- changed
5657: IF g_debug
5658: THEN
5659: hr_utility.TRACE ('');
5660: hr_utility.TRACE ('****** Populating Global PL/SQL tables *******');
5661: hr_utility.TRACE ('');
5662: END IF;
5663:
5664: FOR cnt IN t_bb.FIRST .. t_bb.LAST
5657: IF g_debug
5658: THEN
5659: hr_utility.TRACE ('');
5660: hr_utility.TRACE ('****** Populating Global PL/SQL tables *******');
5661: hr_utility.TRACE ('');
5662: END IF;
5663:
5664: FOR cnt IN t_bb.FIRST .. t_bb.LAST
5665: LOOP
5663:
5664: FOR cnt IN t_bb.FIRST .. t_bb.LAST
5665: LOOP
5666: --if g_debug then
5667: -- hr_utility.set_location('Processing '||l_proc, 130);
5668: --end if;
5669:
5670: -- Intitialise timecard scope variables
5671: IF (t_bb (cnt).time_bb_id <> l_old_timecard_bb_id)
5672: THEN
5673: -- set old time card id and reset copied flag
5674:
5675: --if g_debug then
5676: -- hr_utility.trace('GAZ t bb is '||to_char(t_bb(cnt).time_bb_id));
5677: --end if;
5678: l_timecard_retrieve := TRUE;
5679: l_day_retrieve := TRUE;
5680: l_old_timecard_bb_id := t_bb (cnt).time_bb_id;
5693: t_time_bld_blk_info.DELETE;
5694: END IF; -- timecard id changed check
5695:
5696: -- if g_debug then
5697: --hr_utility.set_location('Processing '||l_proc, 140);
5698: --end if;
5699:
5700: -- Initialise and maintain DAY scope variables
5701: IF (t_bb (cnt).day_bb_id <> l_old_day_bb_id)
5707: t_day_bld_blk_info.DELETE;
5708: END IF;
5709:
5710: -- if g_debug then
5711: --hr_utility.set_location('Processing '||l_proc, 160);
5712: --end if;
5713:
5714: -- Maintain Detail building blocks
5715: IF (t_bb (cnt).detail_bb_id <> l_old_detail_bb_id)
5716: THEN
5717: -- set count and old time card id
5718:
5719: -- if g_debug then
5720: --hr_utility.trace('GAZ tbb DETAIL is '||to_char(t_bb(cnt).detail_bb_id));
5721: --end if;
5722: l_old_detail_bb_id := t_bb (cnt).detail_bb_id;
5723: l_detail_copied := 'N';
5724: l_already_multiplied := 'N'; --DAYS Vs HOURS
5761: g_retrieval_process_id ) )
5762: THEN
5763: IF g_debug
5764: THEN
5765: hr_utility.trace('Resource '||t_bb (cnt).detail_resource_id||
5766: 'had a different rules evaluation pref and needs adj this time');
5767: END IF;
5768: END IF;
5769:
5797:
5798: -- Absences ends
5799:
5800:
5801: hr_utility.TRACE ('about to call chk retrieve');
5802: hxc_generic_retrieval_utils.chk_retrieve
5803: (p_resource_id => t_bb (cnt).detail_resource_id,
5804: p_bb_status => t_bb (cnt).detail_approval_status,
5805: p_bb_deleted => t_bb (cnt).time_deleted,
5819: p_tc_locked => l_tc_locked,
5820: p_tc_first_lock => l_tc_first_lock,
5821: p_bb_skipped_reason => l_bb_skipped_reason -- Bug 7595581
5822: );
5823: hr_utility.TRACE ('after to call chk retrieve');
5824: END IF; -- chk deleted and not transferred
5825:
5826: -- if this detail block has not already been copied and the object version numbers
5827: -- are different then we need this building block and the day and time bld blks
6023: END IF;
6024:
6025: IF g_debug
6026: THEN
6027: hr_utility.TRACE (' *********** - GLOBAL TABLE INFO ************ ');
6028: END IF;
6029:
6030: l_count := hxc_generic_retrieval_pkg.t_detail_bld_blks.COUNT;
6031:
6030: l_count := hxc_generic_retrieval_pkg.t_detail_bld_blks.COUNT;
6031:
6032: IF g_debug
6033: THEN
6034: hr_utility.TRACE ('DETAIL bld blks : ' || TO_CHAR (l_count));
6035: END IF;
6036:
6037: l_count := hxc_generic_retrieval_pkg.t_detail_attributes.COUNT;
6038:
6037: l_count := hxc_generic_retrieval_pkg.t_detail_attributes.COUNT;
6038:
6039: IF g_debug
6040: THEN
6041: hr_utility.TRACE ('DETAIL attributes : ' || TO_CHAR (l_count));
6042: END IF;
6043:
6044: l_count := hxc_generic_retrieval_pkg.t_tx_detail_bb_id.COUNT;
6045:
6044: l_count := hxc_generic_retrieval_pkg.t_tx_detail_bb_id.COUNT;
6045:
6046: IF g_debug
6047: THEN
6048: hr_utility.TRACE ('DETAIL txd count is ' || TO_CHAR (l_count));
6049: END IF;
6050:
6051: l_count := hxc_generic_retrieval_pkg.t_tx_error_bb_id.COUNT;
6052:
6051: l_count := hxc_generic_retrieval_pkg.t_tx_error_bb_id.COUNT;
6052:
6053: IF g_debug
6054: THEN
6055: hr_utility.TRACE ('ERROR txd count is ' || TO_CHAR (l_count));
6056: END IF;
6057:
6058: -- delete the table
6059: t_bb.DELETE;
6059: t_bb.DELETE;
6060:
6061: IF g_debug
6062: THEN
6063: hr_utility.set_location ('Leaving ' || l_proc, 200);
6064: END IF;
6065: EXCEPTION
6066: WHEN e_no_timecards
6067: THEN
6114: fnd_message.raise_error;
6115: WHEN OTHERS
6116: THEN
6117: -- Bug 9394444
6118: hr_utility.trace(dbms_utility.format_error_backtrace);
6119: audit_transaction
6120: (p_mode => 'I' -- Insert
6121: ,
6122: p_transaction_process_id => g_retrieval_process_id,
6415:
6416: IF g_debug
6417: THEN
6418: l_proc := g_package || 'query_old_timecard';
6419: hr_utility.set_location ('Processing ' || l_proc, 180);
6420: END IF;
6421:
6422: IF (l_index <> 0)
6423: THEN
6438: );
6439:
6440: IF g_debug
6441: THEN
6442: hr_utility.set_location ('Processing ' || l_proc, 190);
6443: END IF;
6444:
6445: OPEN csr_get_old_detail_bld_blks;
6446:
6455: CLOSE csr_get_old_detail_bld_blks;
6456:
6457: IF g_debug
6458: THEN
6459: hr_utility.set_location ('Processing ' || l_proc, 200);
6460: END IF;
6461:
6462: OPEN csr_get_old_attributes;
6463:
6478: CLOSE csr_get_old_attributes;
6479:
6480: IF g_debug
6481: THEN
6482: hr_utility.set_location ( 'gaz - old att table is '
6483: || TO_CHAR (t_old_att_bb_id.COUNT),
6484: 999
6485: );
6486: END IF;
6506:
6507: -- populate old bld blk PL/SQL table
6508: IF g_debug
6509: THEN
6510: hr_utility.set_location ('Processing ' || l_proc, 210);
6511: END IF;
6512: l_detail_index := l_lower_range-1; --DAYS Vs HOURS
6513: l_bb_index := l_lower_range;
6514:
6540: END LOOP;
6541:
6542: IF g_debug
6543: THEN
6544: hr_utility.set_location ('Processing ' || l_proc, 220);
6545: END IF;
6546:
6547: l_index := hxc_generic_retrieval_pkg.t_old_detail_bld_blks.COUNT;
6548:
6547: l_index := hxc_generic_retrieval_pkg.t_old_detail_bld_blks.COUNT;
6548:
6549: IF g_debug
6550: THEN
6551: hr_utility.set_location
6552: ( 'gaz GLOBAL old DETAIL bld blk count is '
6553: || TO_CHAR (l_index),
6554: 999
6555: );
6558: l_index := hxc_generic_retrieval_pkg.t_old_detail_attributes.COUNT;
6559:
6560: IF g_debug
6561: THEN
6562: hr_utility.set_location ( 'gaz time att count is '
6563: || TO_CHAR (l_index),
6564: 999
6565: );
6566: END IF;
6568: IF (t_old_att_bb_id.COUNT <> 0)
6569: THEN
6570: IF g_debug
6571: THEN
6572: hr_utility.set_location ('Processing ' || l_proc, 230);
6573: END IF;
6574:
6575: FOR x IN t_old_att_bb_id.FIRST .. t_old_att_bb_id.LAST
6576: LOOP
6651: l_index := hxc_generic_retrieval_pkg.t_bb.COUNT;
6652:
6653: IF g_debug
6654: THEN
6655: hr_utility.set_location ( 'gaz t bb count is '
6656: || TO_CHAR (l_index),
6657: 999
6658: );
6659: END IF;
6659: END IF;
6660:
6661: IF g_debug
6662: THEN
6663: hr_utility.set_location ('Processing ' || l_proc, 240);
6664: END IF;
6665:
6666: FOR x IN t_bb.FIRST .. t_bb.LAST
6667: LOOP
6676: END IF; -- ( t_old_att_bb_id.COUNT <> 0 )
6677:
6678: IF g_debug
6679: THEN
6680: hr_utility.set_location ('Processing ' || l_proc, 250);
6681: END IF;
6682:
6683: -- truncate temporary tables and delete arrays and table
6684: t_bb.DELETE;
6737: END LOOP; -- while l_lower_range <= t_old_detail_bb_id.LAST
6738:
6739: IF g_debug
6740: THEN
6741: hr_utility.set_location ('Processing ' || l_proc, 260);
6742: END IF;
6743: END IF; -- IF ( l_index <> 0 )
6744: --if g_debug then
6745: -- hr_utility.set_location('Processing '||l_proc, 270);
6741: hr_utility.set_location ('Processing ' || l_proc, 260);
6742: END IF;
6743: END IF; -- IF ( l_index <> 0 )
6744: --if g_debug then
6745: -- hr_utility.set_location('Processing '||l_proc, 270);
6746: --end if;
6747: END query_old_timecard;
6748:
6749: --
7560: l_dummy VARCHAR2 (20);
7561: BEGIN -- check concurrency ok
7562: IF g_debug
7563: THEN
7564: hr_utility.TRACE ( 'in check concurrency - process id is '
7565: || TO_CHAR (p_process_id)
7566: );
7567: hr_utility.TRACE ( 'in check concurrency - retrieval id is '
7568: || p_retrieval_process
7563: THEN
7564: hr_utility.TRACE ( 'in check concurrency - process id is '
7565: || TO_CHAR (p_process_id)
7566: );
7567: hr_utility.TRACE ( 'in check concurrency - retrieval id is '
7568: || p_retrieval_process
7569: );
7570: hr_utility.TRACE ( 'in check concurrency - where clause is '
7571: || SUBSTR (p_where_clause, 1, 300)
7566: );
7567: hr_utility.TRACE ( 'in check concurrency - retrieval id is '
7568: || p_retrieval_process
7569: );
7570: hr_utility.TRACE ( 'in check concurrency - where clause is '
7571: || SUBSTR (p_where_clause, 1, 300)
7572: );
7573: END IF;
7574:
7582: -- if another process is running check to see if the transfer batch size is
7583: -- set otherwise the processes will conflict
7584: IF g_debug
7585: THEN
7586: hr_utility.TRACE ('l running is ' || TO_CHAR (l_transaction_id));
7587: END IF;
7588:
7589: IF (p_retrieval_process = 'Projects Retrieval Process')
7590: THEN
7598: )
7599: THEN
7600: IF g_debug
7601: THEN
7602: hr_utility.TRACE ('Checking BEE');
7603: END IF;
7604:
7605: -- make sure that ranges have been inserted already by the IN PROGRESS
7606: -- process
7607: WHILE l_no_ranges
7608: LOOP
7609: IF g_debug
7610: THEN
7611: hr_utility.TRACE ('looking for ranges');
7612: END IF;
7613:
7614: OPEN csr_chk_range_exists (l_transaction_id);
7615:
7619: IF (csr_chk_range_exists%NOTFOUND)
7620: THEN
7621: IF g_debug
7622: THEN
7623: hr_utility.TRACE ( 'No ranges yet for '
7624: || TO_CHAR (l_transaction_id)
7625: );
7626: END IF;
7627:
7635: END IF;
7636: ELSE
7637: IF g_debug
7638: THEN
7639: hr_utility.TRACE ('found ranges');
7640: END IF;
7641:
7642: l_no_ranges := FALSE;
7643: END IF;
7656: CLOSE csr_chk_where_clause;
7657:
7658: IF g_debug
7659: THEN
7660: hr_utility.TRACE ('l bee ok is ' || l_bee_ok);
7661: END IF;
7662: END IF;
7663:
7664: IF ( ( l_transaction_id IS NOT NULL
7669: )
7670: THEN
7671: IF g_debug
7672: THEN
7673: hr_utility.TRACE ('FALSE');
7674: END IF;
7675:
7676: RETURN FALSE;
7677: ELSE
7676: RETURN FALSE;
7677: ELSE
7678: IF g_debug
7679: THEN
7680: hr_utility.TRACE ('TRUE');
7681: END IF;
7682:
7683: RETURN TRUE;
7684: END IF;
7711: l_end_date DATE := NVL (p_end_date, hr_general.end_of_time);
7712: BEGIN
7713: IF g_debug
7714: THEN
7715: hr_utility.TRACE ('Entering chk_a_and_r_overlap');
7716: END IF;
7717:
7718: OPEN chk_a_and_r_overlap (l_start_date, l_end_date);
7719:
7732: CLOSE chk_a_and_r_overlap;
7733:
7734: IF g_debug
7735: THEN
7736: hr_utility.TRACE ('Leaving chk_a_and_r_overlap');
7737: END IF;
7738: END check_a_and_r_overlap;
7739:
7740: -- private procedure
7777: BEGIN -- get field mappings
7778: IF g_debug
7779: THEN
7780: l_proc := g_package || 'get_field_mappings';
7781: hr_utility.set_location ('Entering ' || l_proc, 10);
7782: END IF;
7783:
7784: OPEN csr_get_mappings;
7785:
7796:
7797: LOOP
7798: IF g_debug
7799: THEN
7800: hr_utility.set_location ('Processing ' || l_proc, 20);
7801: END IF;
7802:
7803: l_table_index := l_table_index + 1;
7804: l_mappings_table (l_table_index) := l_mapping_record;
7812: CLOSE csr_get_mappings;
7813:
7814: IF g_debug
7815: THEN
7816: hr_utility.set_location ('Leaving ' || l_proc, 30);
7817: END IF;
7818:
7819: RETURN l_mappings_table;
7820: END get_field_mappings;
7825: g_conc_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
7826:
7827: IF g_debug
7828: THEN
7829: hr_utility.TRACE ('Conc Req ID is ' || TO_CHAR (g_conc_request_id)
7830: );
7831: END IF;
7832:
7833: -- Bug 9173209
7974: END get_valid_app_sets;
7975: BEGIN -- execute retrieval process
7976: -- this is to handle the case where the OTLR and BEE are processed in
7977: -- the loop
7978: g_debug := hr_utility.debug_enabled;
7979:
7980: IF ( g_params.p_process <> p_process
7981: AND hxc_generic_retrieval_pkg.g_in_loop
7982: )
8075: END IF;
8076:
8077: IF g_debug
8078: THEN
8079: hr_utility.TRACE ( 'final since date is '
8080: || TO_CHAR (g_params.since_date,
8081: 'hh24:mi:ss dd-mon-yy'
8082: )
8083: );
8154: glb_debug := TRUE;
8155:
8156: IF g_debug
8157: THEN
8158: hr_utility.trace_on
8159: (trace_mode => NULL,
8160: session_identifier => NVL
8161: (p_transaction_code,
8162: 'RETRIEVAL'
8166: END IF;
8167:
8168: IF g_debug
8169: THEN
8170: hr_utility.set_location ('Entering ' || l_proc, 10);
8171: END IF;
8172:
8173: IF ( g_params.p_transaction_code LIKE 'GAZ%'
8174: OR g_params.p_transaction_code IS NULL
8181: END IF;
8182:
8183: IF g_debug
8184: THEN
8185: hr_utility.TRACE ('**** Retrieval Params are.... ****');
8186: hr_utility.TRACE ('');
8187: hr_utility.TRACE ('Process is :' || g_params.p_process);
8188: hr_utility.TRACE ( 'Transaction code is :'
8189: || g_params.p_transaction_code
8182:
8183: IF g_debug
8184: THEN
8185: hr_utility.TRACE ('**** Retrieval Params are.... ****');
8186: hr_utility.TRACE ('');
8187: hr_utility.TRACE ('Process is :' || g_params.p_process);
8188: hr_utility.TRACE ( 'Transaction code is :'
8189: || g_params.p_transaction_code
8190: );
8183: IF g_debug
8184: THEN
8185: hr_utility.TRACE ('**** Retrieval Params are.... ****');
8186: hr_utility.TRACE ('');
8187: hr_utility.TRACE ('Process is :' || g_params.p_process);
8188: hr_utility.TRACE ( 'Transaction code is :'
8189: || g_params.p_transaction_code
8190: );
8191: hr_utility.TRACE ('Start Date is :' || g_params.p_start_date);
8184: THEN
8185: hr_utility.TRACE ('**** Retrieval Params are.... ****');
8186: hr_utility.TRACE ('');
8187: hr_utility.TRACE ('Process is :' || g_params.p_process);
8188: hr_utility.TRACE ( 'Transaction code is :'
8189: || g_params.p_transaction_code
8190: );
8191: hr_utility.TRACE ('Start Date is :' || g_params.p_start_date);
8192: hr_utility.TRACE ('End Date is :' || g_params.p_end_date);
8187: hr_utility.TRACE ('Process is :' || g_params.p_process);
8188: hr_utility.TRACE ( 'Transaction code is :'
8189: || g_params.p_transaction_code
8190: );
8191: hr_utility.TRACE ('Start Date is :' || g_params.p_start_date);
8192: hr_utility.TRACE ('End Date is :' || g_params.p_end_date);
8193: hr_utility.TRACE ('Incremental is :'
8194: || g_params.p_incremental
8195: );
8188: hr_utility.TRACE ( 'Transaction code is :'
8189: || g_params.p_transaction_code
8190: );
8191: hr_utility.TRACE ('Start Date is :' || g_params.p_start_date);
8192: hr_utility.TRACE ('End Date is :' || g_params.p_end_date);
8193: hr_utility.TRACE ('Incremental is :'
8194: || g_params.p_incremental
8195: );
8196: hr_utility.TRACE ('Rerun Flag is :' || g_params.p_rerun_flag);
8189: || g_params.p_transaction_code
8190: );
8191: hr_utility.TRACE ('Start Date is :' || g_params.p_start_date);
8192: hr_utility.TRACE ('End Date is :' || g_params.p_end_date);
8193: hr_utility.TRACE ('Incremental is :'
8194: || g_params.p_incremental
8195: );
8196: hr_utility.TRACE ('Rerun Flag is :' || g_params.p_rerun_flag);
8197: hr_utility.TRACE ( 'Where Clause is :'
8192: hr_utility.TRACE ('End Date is :' || g_params.p_end_date);
8193: hr_utility.TRACE ('Incremental is :'
8194: || g_params.p_incremental
8195: );
8196: hr_utility.TRACE ('Rerun Flag is :' || g_params.p_rerun_flag);
8197: hr_utility.TRACE ( 'Where Clause is :'
8198: || SUBSTR (g_params.p_where_clause, 1, 200)
8199: );
8200: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 201, 200));
8193: hr_utility.TRACE ('Incremental is :'
8194: || g_params.p_incremental
8195: );
8196: hr_utility.TRACE ('Rerun Flag is :' || g_params.p_rerun_flag);
8197: hr_utility.TRACE ( 'Where Clause is :'
8198: || SUBSTR (g_params.p_where_clause, 1, 200)
8199: );
8200: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 201, 200));
8201: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 401, 200));
8196: hr_utility.TRACE ('Rerun Flag is :' || g_params.p_rerun_flag);
8197: hr_utility.TRACE ( 'Where Clause is :'
8198: || SUBSTR (g_params.p_where_clause, 1, 200)
8199: );
8200: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 201, 200));
8201: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 401, 200));
8202: hr_utility.TRACE ('Scope is :' || g_params.p_scope);
8203: hr_utility.TRACE ('Clusive is :' || g_params.p_clusive);
8204: hr_utility.TRACE ('');
8197: hr_utility.TRACE ( 'Where Clause is :'
8198: || SUBSTR (g_params.p_where_clause, 1, 200)
8199: );
8200: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 201, 200));
8201: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 401, 200));
8202: hr_utility.TRACE ('Scope is :' || g_params.p_scope);
8203: hr_utility.TRACE ('Clusive is :' || g_params.p_clusive);
8204: hr_utility.TRACE ('');
8205: hr_utility.TRACE ('**** Retrieval LOOPING GLOBALS are.... ****');
8198: || SUBSTR (g_params.p_where_clause, 1, 200)
8199: );
8200: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 201, 200));
8201: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 401, 200));
8202: hr_utility.TRACE ('Scope is :' || g_params.p_scope);
8203: hr_utility.TRACE ('Clusive is :' || g_params.p_clusive);
8204: hr_utility.TRACE ('');
8205: hr_utility.TRACE ('**** Retrieval LOOPING GLOBALS are.... ****');
8206: END IF;
8199: );
8200: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 201, 200));
8201: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 401, 200));
8202: hr_utility.TRACE ('Scope is :' || g_params.p_scope);
8203: hr_utility.TRACE ('Clusive is :' || g_params.p_clusive);
8204: hr_utility.TRACE ('');
8205: hr_utility.TRACE ('**** Retrieval LOOPING GLOBALS are.... ****');
8206: END IF;
8207:
8200: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 201, 200));
8201: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 401, 200));
8202: hr_utility.TRACE ('Scope is :' || g_params.p_scope);
8203: hr_utility.TRACE ('Clusive is :' || g_params.p_clusive);
8204: hr_utility.TRACE ('');
8205: hr_utility.TRACE ('**** Retrieval LOOPING GLOBALS are.... ****');
8206: END IF;
8207:
8208: IF (g_in_loop)
8201: hr_utility.TRACE (SUBSTR (g_params.p_where_clause, 401, 200));
8202: hr_utility.TRACE ('Scope is :' || g_params.p_scope);
8203: hr_utility.TRACE ('Clusive is :' || g_params.p_clusive);
8204: hr_utility.TRACE ('');
8205: hr_utility.TRACE ('**** Retrieval LOOPING GLOBALS are.... ****');
8206: END IF;
8207:
8208: IF (g_in_loop)
8209: THEN
8208: IF (g_in_loop)
8209: THEN
8210: IF g_debug
8211: THEN
8212: hr_utility.TRACE ('G_IN_LOOP is TRUE');
8213: END IF;
8214: ELSE
8215: IF g_debug
8216: THEN
8213: END IF;
8214: ELSE
8215: IF g_debug
8216: THEN
8217: hr_utility.TRACE ('G_IN_LOOP is FALSE');
8218: END IF;
8219: END IF;
8220:
8221: IF (g_last_chunk)
8221: IF (g_last_chunk)
8222: THEN
8223: IF g_debug
8224: THEN
8225: hr_utility.TRACE ('G_LAST_CHUNK is TRUE');
8226: END IF;
8227: ELSE
8228: IF g_debug
8229: THEN
8226: END IF;
8227: ELSE
8228: IF g_debug
8229: THEN
8230: hr_utility.TRACE ('G_LAST_CHUNK is FALSE');
8231: END IF;
8232: END IF;
8233:
8234: IF (g_no_timecards)
8234: IF (g_no_timecards)
8235: THEN
8236: IF g_debug
8237: THEN
8238: hr_utility.TRACE ('G_NO_TIMECARDS is TRUE');
8239: END IF;
8240: ELSE
8241: IF g_debug
8242: THEN
8239: END IF;
8240: ELSE
8241: IF g_debug
8242: THEN
8243: hr_utility.TRACE ('G_NO_TIMECARDS is FALSE');
8244: END IF;
8245: END IF;
8246:
8247: IF (g_overall_no_timecards)
8247: IF (g_overall_no_timecards)
8248: THEN
8249: IF g_debug
8250: THEN
8251: hr_utility.TRACE ('G_OVERALL_NO_TIMECARDS is TRUE');
8252: END IF;
8253: ELSE
8254: IF g_debug
8255: THEN
8252: END IF;
8253: ELSE
8254: IF g_debug
8255: THEN
8256: hr_utility.TRACE ('G_OVERALL_NO_TIMECARDS is FALSE');
8257: END IF;
8258: END IF;
8259:
8260: IF g_debug
8258: END IF;
8259:
8260: IF g_debug
8261: THEN
8262: hr_utility.TRACE ( 'l_range_start is '
8263: || TO_CHAR (l_pkg_range_start)
8264: );
8265: hr_utility.TRACE ('l_range_stop is ' || TO_CHAR (l_pkg_range_stop));
8266: END IF;
8261: THEN
8262: hr_utility.TRACE ( 'l_range_start is '
8263: || TO_CHAR (l_pkg_range_start)
8264: );
8265: hr_utility.TRACE ('l_range_stop is ' || TO_CHAR (l_pkg_range_stop));
8266: END IF;
8267:
8268: IF (NOT hxc_generic_retrieval_pkg.g_in_loop)
8269: THEN
8277: );
8278:
8279: IF g_debug
8280: THEN
8281: hr_utility.set_location ('Processing ' || l_proc, 20);
8282: END IF;
8283:
8284: -- check to see if mapping_id exists and thus retrieval process registered
8285: IF (l_mapping_id IS NULL)
8290: -- now check to see if this retrieval is already running
8291: -- only need to do this if NOT the 'Projects Retrieval Process'
8292: IF g_debug
8293: THEN
8294: hr_utility.set_location ('Processing ' || l_proc, 30);
8295: END IF;
8296:
8297: IF (g_params.p_process = 'Apply Schedule Rules')
8298: THEN
8388: );
8389:
8390: IF g_debug
8391: THEN
8392: hr_utility.set_location ('Processing ' || l_proc, 40);
8393: END IF;
8394:
8395: IF NOT hxc_generic_retrieval_pkg.g_in_loop
8396: THEN
8425: l_where_clause_blk := replace_timecard_string (l_where_clause_blk);
8426:
8427: IF g_debug
8428: THEN
8429: hr_utility.set_location ('Processing ' || l_proc, 60);
8430: END IF;
8431:
8432: l_dynamic_query :=
8433: build_query (p_where_clause_blk => l_where_clause_blk,
8435: );
8436:
8437: IF g_debug
8438: THEN
8439: hr_utility.set_location ('Processing ' || l_proc, 70);
8440: END IF;
8441:
8442: -- lets see the query
8443: insert_query (l_dynamic_query, 'QUERY');
8450: IF (hxc_generic_retrieval_pkg.g_in_loop)
8451: THEN
8452: IF g_debug
8453: THEN
8454: hr_utility.set_location ('Processing ' || l_proc, 71);
8455: END IF;
8456:
8457: WHILE (hxc_generic_retrieval_pkg.g_no_timecards)
8458: LOOP
8457: WHILE (hxc_generic_retrieval_pkg.g_no_timecards)
8458: LOOP
8459: IF g_debug
8460: THEN
8461: hr_utility.set_location ('Processing ' || l_proc, 72);
8462: END IF;
8463:
8464: BEGIN
8465: query_it (p_query => l_dynamic_query);
8466: EXCEPTION
8467: WHEN OTHERS
8468: THEN
8469: -- Bug 9394444
8470: hr_utility.trace(dbms_utility.format_error_backtrace);
8471: IF g_debug
8472: THEN
8473: hr_utility.TRACE ( 'query EXCEPTION is '
8474: || SUBSTR (SQLERRM, 1, 60)
8469: -- Bug 9394444
8470: hr_utility.trace(dbms_utility.format_error_backtrace);
8471: IF g_debug
8472: THEN
8473: hr_utility.TRACE ( 'query EXCEPTION is '
8474: || SUBSTR (SQLERRM, 1, 60)
8475: );
8476: hr_utility.TRACE ( 'query EXCEPTION is '
8477: || SUBSTR (SQLERRM, 61, 120)
8472: THEN
8473: hr_utility.TRACE ( 'query EXCEPTION is '
8474: || SUBSTR (SQLERRM, 1, 60)
8475: );
8476: hr_utility.TRACE ( 'query EXCEPTION is '
8477: || SUBSTR (SQLERRM, 61, 120)
8478: );
8479: hr_utility.TRACE ( 'query EXCEPTION is '
8480: || SUBSTR (SQLERRM, 121, 180)
8475: );
8476: hr_utility.TRACE ( 'query EXCEPTION is '
8477: || SUBSTR (SQLERRM, 61, 120)
8478: );
8479: hr_utility.TRACE ( 'query EXCEPTION is '
8480: || SUBSTR (SQLERRM, 121, 180)
8481: );
8482: hr_utility.TRACE ( 'query EXCEPTION is '
8483: || SUBSTR (SQLERRM, 181, 240)
8478: );
8479: hr_utility.TRACE ( 'query EXCEPTION is '
8480: || SUBSTR (SQLERRM, 121, 180)
8481: );
8482: hr_utility.TRACE ( 'query EXCEPTION is '
8483: || SUBSTR (SQLERRM, 181, 240)
8484: );
8485: END IF;
8486:
8493: )
8494: THEN
8495: IF g_debug
8496: THEN
8497: hr_utility.set_location ('Processing ' || l_proc,
8498: 73
8499: );
8500: END IF;
8501:
8519: )
8520: THEN
8521: IF g_debug
8522: THEN
8523: hr_utility.set_location ('Processing ' || l_proc,
8524: 74
8525: );
8526: END IF;
8527:
8529: )
8530: THEN
8531: IF g_debug
8532: THEN
8533: hr_utility.set_location ('Processing ' || l_proc,
8534: 75
8535: );
8536: END IF;
8537:
8546: END IF;
8547: ELSE
8548: IF g_debug
8549: THEN
8550: hr_utility.set_location ('Processing ' || l_proc,
8551: 76
8552: );
8553: END IF;
8554:
8568: END LOOP;
8569: ELSE
8570: IF g_debug
8571: THEN
8572: hr_utility.set_location ('Processing ' || l_proc, 77);
8573: END IF;
8574:
8575: query_it (p_query => l_dynamic_query);
8576: END IF;
8576: END IF;
8577:
8578: IF g_debug
8579: THEN
8580: hr_utility.set_location ('Processing ' || l_proc, 80);
8581: END IF;
8582:
8583: IF (NOT hxc_generic_retrieval_pkg.g_last_chunk)
8584: THEN
8589: END IF; -- IF ( NOT hxc_generic_retrieval_pkg.G_LAST_CHUNK )
8590:
8591: IF g_debug
8592: THEN
8593: hr_utility.set_location ('Processing ' || l_proc, 100);
8594: END IF;
8595: END IF;
8596:
8597: -- ( hxc_generic_retrieval_pkg.G_IN_LOOP AND hxc_generic_retrieval_pkg.G_LAST_CHUNK );
8850: fnd_message.raise_error;
8851: WHEN OTHERS
8852: THEN
8853: -- Bug 9394444
8854: hr_utility.trace(dbms_utility.format_error_backtrace);
8855: audit_transaction
8856: (p_mode => 'I' -- Insert
8857: ,
8858: p_transaction_process_id => g_retrieval_process_id,
9010:
9011:
9012:
9013: BEGIN
9014: g_debug := hr_utility.debug_enabled;
9015:
9016: IF g_debug
9017: THEN
9018: l_proc := g_package || 'update_transaction_status';
9015:
9016: IF g_debug
9017: THEN
9018: l_proc := g_package || 'update_transaction_status';
9019: hr_utility.TRACE ('In Update Transaction Status');
9020: END IF;
9021:
9022: -- get the process id
9023: chk_retrieval_process (p_retrieval_process => p_process,
9028:
9029: -- now call the audit transaction appropriately
9030: IF g_debug
9031: THEN
9032: hr_utility.set_location ('Processing ' || l_proc, 110);
9033: END IF;
9034:
9035: -- Bug 6914381
9036: -- If any transaction resulted in error, there is no need to
9101: IF NOT p_rollback
9102: THEN
9103: IF g_debug
9104: THEN
9105: hr_utility.set_location ('Processing ' || l_proc, 130);
9106: END IF;
9107:
9108: -- check to see if any tx time details
9109: IF l_time_max <> 0
9120: l_temp_transaction_id;
9121:
9122: IF g_debug
9123: THEN
9124: hr_utility.set_location ('Processing ' || l_proc, 50);
9125: END IF;
9126:
9127: FORALL tx_detail IN hxc_generic_retrieval_pkg.t_tx_time_transaction_id.FIRST .. hxc_generic_retrieval_pkg.t_tx_time_transaction_id.LAST
9128: INSERT INTO hxc_transaction_details
9162: l_temp_transaction_id;
9163:
9164: IF g_debug
9165: THEN
9166: hr_utility.set_location ('Processing ' || l_proc, 60);
9167: END IF;
9168:
9169: FORALL tx_detail IN hxc_generic_retrieval_pkg.t_tx_day_transaction_id.FIRST .. hxc_generic_retrieval_pkg.t_tx_day_transaction_id.LAST
9170: INSERT INTO hxc_transaction_details
9204: l_temp_transaction_id;
9205:
9206: IF g_debug
9207: THEN
9208: hr_utility.set_location ('Processing ' || l_proc, 70);
9209: END IF;
9210:
9211: FORALL tx_detail IN hxc_generic_retrieval_pkg.t_tx_detail_transaction_id.FIRST .. hxc_generic_retrieval_pkg.t_tx_detail_transaction_id.LAST
9212: INSERT INTO hxc_transaction_details
9966:
9967:
9968: IF g_debug
9969: THEN
9970: hr_utility.set_location ('Processing ' || l_proc, 75);
9971: END IF;
9972: END IF; -- l_detail_max <> 0
9973:
9974: IF g_debug
9972: END IF; -- l_detail_max <> 0
9973:
9974: IF g_debug
9975: THEN
9976: hr_utility.set_location ('Processing ' || l_proc, 80);
9977: END IF;
9978:
9979: IF l_error_max <> 0
9980: THEN
9979: IF l_error_max <> 0
9980: THEN
9981: IF g_debug
9982: THEN
9983: hr_utility.set_location ('Processing ' || l_proc, 85);
9984: END IF;
9985:
9986: -- now let's bulk fetch all the transaction detail id
9987: OPEN csr_get_tx_detail_id (l_error_max);
9995: l_temp_transaction_id;
9996:
9997: IF g_debug
9998: THEN
9999: hr_utility.set_location ('Processing ' || l_proc, 90);
10000: END IF;
10001:
10002: FORALL tx_error IN hxc_generic_retrieval_pkg.t_tx_error_transaction_id.FIRST .. hxc_generic_retrieval_pkg.t_tx_error_transaction_id.LAST
10003: INSERT INTO hxc_transaction_details
10029: hxc_generic_retrieval_pkg.t_tx_error_exception.DELETE;
10030:
10031: IF g_debug
10032: THEN
10033: hr_utility.set_location ('Processing ' || l_proc, 100);
10034: END IF;
10035: END IF; -- l_error_max <> 0
10036: END IF; -- p_rollback
10037:
10036: END IF; -- p_rollback
10037:
10038: IF g_debug
10039: THEN
10040: hr_utility.set_location ('Processing ' || l_proc, 170);
10041: END IF;
10042:
10043: -- need to clean up the hxc retrieval ranges table if the process
10044: -- has errored.
10065: -- Added the below construct to process transferred to
10066: -- column.
10067: IF g_debug
10068: THEN
10069: hr_utility.trace(' About to process transferred to ');
10070:
10071: IF g_tc_ovn_array.COUNT > 0
10072: THEN
10073: i := g_tc_ovn_array.FIRST;
10071: IF g_tc_ovn_array.COUNT > 0
10072: THEN
10073: i := g_tc_ovn_array.FIRST;
10074: LOOP
10075: hr_utility.trace(i);
10076: hr_utility.trace(g_tc_ovn_array(i));
10077: hr_utility.trace(l_process_id);
10078: i := g_tc_ovn_array.NEXT(i);
10079: EXIT WHEN NOT g_tc_ovn_array.EXISTS(i);
10072: THEN
10073: i := g_tc_ovn_array.FIRST;
10074: LOOP
10075: hr_utility.trace(i);
10076: hr_utility.trace(g_tc_ovn_array(i));
10077: hr_utility.trace(l_process_id);
10078: i := g_tc_ovn_array.NEXT(i);
10079: EXIT WHEN NOT g_tc_ovn_array.EXISTS(i);
10080: END LOOP;
10073: i := g_tc_ovn_array.FIRST;
10074: LOOP
10075: hr_utility.trace(i);
10076: hr_utility.trace(g_tc_ovn_array(i));
10077: hr_utility.trace(l_process_id);
10078: i := g_tc_ovn_array.NEXT(i);
10079: EXIT WHEN NOT g_tc_ovn_array.EXISTS(i);
10080: END LOOP;
10081: END IF;
10104: -- DO NOT DELETE FOR 'IN PROGRESS' TRANSACTIONS
10105: THEN
10106: IF g_debug
10107: THEN
10108: hr_utility.TRACE ('g_conc_request_id is ' || g_conc_request_id);
10109: END IF;
10110:
10111: delete_retrieval_ranges (hxc_generic_retrieval_pkg.g_transaction_id);
10112:
10111: delete_retrieval_ranges (hxc_generic_retrieval_pkg.g_transaction_id);
10112:
10113: IF g_debug
10114: THEN
10115: hr_utility.set_location ('Processing ' || l_proc, 200);
10116: END IF;
10117: ELSE
10118: IF g_debug
10119: THEN
10116: END IF;
10117: ELSE
10118: IF g_debug
10119: THEN
10120: hr_utility.set_location ('Processing ' || l_proc, 250);
10121: END IF;
10122: END IF;
10123:
10124: IF (hxc_generic_retrieval_utils.chk_terminated
10221: END IF;
10222:
10223: IF g_debug
10224: THEN
10225: hr_utility.TRACE ('Leaving Update Transaction Status');
10226: END IF;
10227: END update_transaction_status;
10228:
10229:
10237: BEGIN
10238: FND_FILE.PUT_LINE(FND_FILE.LOG,p_text);
10239: IF g_debug
10240: THEN
10241: hr_utility.trace(p_text);
10242: END IF;
10243: END put_log;
10244:
10245: -- Bug 9458888