5: -- | Private Global Definitions |
6: -- ----------------------------------------------------------------------------
7: --
8:
9: g_debug boolean := hr_utility.debug_enabled;
10:
11: -- caching structures
12:
13: TYPE rec_time_category_cache IS RECORD ( operator hxc_time_categories.operator%TYPE
245:
246:
247: IF ( g_debug ) THEN
248: l_proc := g_package||'value_set_changed';
249: hr_utility.set_location('Leaving '||l_proc, 10);
250: END IF;
251:
252: OPEN get_value_set_last_update_date ( p_vs_comp_rec.flex_value_set_id );
253: FETCH get_value_set_last_update_date INTO l_vs_last_update_date;
253: FETCH get_value_set_last_update_date INTO l_vs_last_update_date;
254: CLOSE get_value_set_last_update_date;
255:
256: IF ( g_debug ) THEN
257: hr_utility.trace('Value set is '||to_char(p_vs_comp_rec.flex_value_set_id));
258: hr_utility.trace('Value set last update date is '||to_char(l_vs_last_update_date));
259: hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
260: END IF;
261:
254: CLOSE get_value_set_last_update_date;
255:
256: IF ( g_debug ) THEN
257: hr_utility.trace('Value set is '||to_char(p_vs_comp_rec.flex_value_set_id));
258: hr_utility.trace('Value set last update date is '||to_char(l_vs_last_update_date));
259: hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
260: END IF;
261:
262: IF ( l_vs_last_update_date > p_vs_comp_rec.last_update_date )
255:
256: IF ( g_debug ) THEN
257: hr_utility.trace('Value set is '||to_char(p_vs_comp_rec.flex_value_set_id));
258: hr_utility.trace('Value set last update date is '||to_char(l_vs_last_update_date));
259: hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
260: END IF;
261:
262: IF ( l_vs_last_update_date > p_vs_comp_rec.last_update_date )
263: THEN
286: FETCH get_vset_tab_last_update_date INTO l_vs_last_update_date;
287: CLOSE get_vset_tab_last_update_date;
288:
289: IF ( g_debug ) THEN
290: hr_utility.trace('Value set is '||to_char(p_vs_comp_rec.flex_value_set_id));
291: hr_utility.trace('Value set table last update date is '||to_char(l_vs_last_update_date));
292: hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
293: END IF;
294:
287: CLOSE get_vset_tab_last_update_date;
288:
289: IF ( g_debug ) THEN
290: hr_utility.trace('Value set is '||to_char(p_vs_comp_rec.flex_value_set_id));
291: hr_utility.trace('Value set table last update date is '||to_char(l_vs_last_update_date));
292: hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
293: END IF;
294:
295: IF ( l_vs_last_update_date > p_vs_comp_rec.last_update_date )
288:
289: IF ( g_debug ) THEN
290: hr_utility.trace('Value set is '||to_char(p_vs_comp_rec.flex_value_set_id));
291: hr_utility.trace('Value set table last update date is '||to_char(l_vs_last_update_date));
292: hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
293: END IF;
294:
295: IF ( l_vs_last_update_date > p_vs_comp_rec.last_update_date )
296: THEN
319:
320: END IF;
321:
322: IF ( g_debug ) THEN
323: hr_utility.set_location('Leaving '||l_proc, 10);
324: END IF;
325:
326: END value_set_changed;
327:
331:
332:
333: IF ( g_debug ) THEN
334: l_proc := g_package||'add_tc_to_cache';
335: hr_utility.set_location('Entering '||l_proc, 10);
336: END IF;
337:
338: l_start_index := NVL( g_tc_component_cache.LAST, 0 )+1;
339: g_tc_cache( p_time_category_id ).time_sql := p_time_category_info.time_sql;
359:
360: -- re-evaluate
361:
362: IF ( g_debug ) THEN
363: hr_utility.trace('Value Set Changed!!!');
364: END IF;
365:
366: update_time_category_comp_sql ( p_rec => l_rec );
367:
379:
380: ELSE
381:
382: IF ( g_debug ) THEN
383: hr_utility.trace('Value Set Not Changed');
384: END IF;
385:
386: g_tc_component_cache(l_tc_comp_ind).sql_string := p_vs_comp_tab(x).sql_string;
387:
440:
441: END IF;
442:
443: IF ( g_debug ) THEN
444: hr_utility.set_location('Leaving '||l_proc, 100);
445:
446: hr_utility.trace('TC Cache for '||to_char(p_time_category_id));
447:
448: hr_utility.trace('g_tc_cache operator is '||g_tc_cache(p_time_category_id).operator);
442:
443: IF ( g_debug ) THEN
444: hr_utility.set_location('Leaving '||l_proc, 100);
445:
446: hr_utility.trace('TC Cache for '||to_char(p_time_category_id));
447:
448: hr_utility.trace('g_tc_cache operator is '||g_tc_cache(p_time_category_id).operator);
449: hr_utility.trace('g_tc_cache start is '||to_char(g_tc_cache(p_time_category_id).start_index));
450: hr_utility.trace('g_tc_cache stop is '||to_char(g_tc_cache(p_time_category_id).stop_index));
444: hr_utility.set_location('Leaving '||l_proc, 100);
445:
446: hr_utility.trace('TC Cache for '||to_char(p_time_category_id));
447:
448: hr_utility.trace('g_tc_cache operator is '||g_tc_cache(p_time_category_id).operator);
449: hr_utility.trace('g_tc_cache start is '||to_char(g_tc_cache(p_time_category_id).start_index));
450: hr_utility.trace('g_tc_cache stop is '||to_char(g_tc_cache(p_time_category_id).stop_index));
451: hr_utility.trace(SUBSTR( 'g_tc_cache time sql is '||g_tc_cache(p_time_category_id).time_sql,1,250));
452:
445:
446: hr_utility.trace('TC Cache for '||to_char(p_time_category_id));
447:
448: hr_utility.trace('g_tc_cache operator is '||g_tc_cache(p_time_category_id).operator);
449: hr_utility.trace('g_tc_cache start is '||to_char(g_tc_cache(p_time_category_id).start_index));
450: hr_utility.trace('g_tc_cache stop is '||to_char(g_tc_cache(p_time_category_id).stop_index));
451: hr_utility.trace(SUBSTR( 'g_tc_cache time sql is '||g_tc_cache(p_time_category_id).time_sql,1,250));
452:
453:
446: hr_utility.trace('TC Cache for '||to_char(p_time_category_id));
447:
448: hr_utility.trace('g_tc_cache operator is '||g_tc_cache(p_time_category_id).operator);
449: hr_utility.trace('g_tc_cache start is '||to_char(g_tc_cache(p_time_category_id).start_index));
450: hr_utility.trace('g_tc_cache stop is '||to_char(g_tc_cache(p_time_category_id).stop_index));
451: hr_utility.trace(SUBSTR( 'g_tc_cache time sql is '||g_tc_cache(p_time_category_id).time_sql,1,250));
452:
453:
454: IF ( g_tc_cache(p_time_category_id).start_index IS NOT NULL )
447:
448: hr_utility.trace('g_tc_cache operator is '||g_tc_cache(p_time_category_id).operator);
449: hr_utility.trace('g_tc_cache start is '||to_char(g_tc_cache(p_time_category_id).start_index));
450: hr_utility.trace('g_tc_cache stop is '||to_char(g_tc_cache(p_time_category_id).stop_index));
451: hr_utility.trace(SUBSTR( 'g_tc_cache time sql is '||g_tc_cache(p_time_category_id).time_sql,1,250));
452:
453:
454: IF ( g_tc_cache(p_time_category_id).start_index IS NOT NULL )
455: THEN
453:
454: IF ( g_tc_cache(p_time_category_id).start_index IS NOT NULL )
455: THEN
456:
457: hr_utility.trace('TC Cache components are ');
458:
459: FOR x IN g_tc_cache(p_time_category_id).start_index ..
460: g_tc_cache(p_time_category_id).stop_index
461: LOOP
459: FOR x IN g_tc_cache(p_time_category_id).start_index ..
460: g_tc_cache(p_time_category_id).stop_index
461: LOOP
462:
463: hr_utility.trace('Type is '||g_tc_component_cache(x).type);
464: hr_utility.trace('ref tc id is '||to_char(g_tc_component_cache(x).ref_tc_id));
465:
466: END LOOP;
467:
460: g_tc_cache(p_time_category_id).stop_index
461: LOOP
462:
463: hr_utility.trace('Type is '||g_tc_component_cache(x).type);
464: hr_utility.trace('ref tc id is '||to_char(g_tc_component_cache(x).ref_tc_id));
465:
466: END LOOP;
467:
468: ELSE
466: END LOOP;
467:
468: ELSE
469:
470: hr_utility.trace('No components');
471:
472: END IF;
473: END IF; -- g_debug
474:
495:
496:
497: IF ( g_debug ) THEN
498: l_proc := g_package||'get_tc_from_cache';
499: hr_utility.set_location('Entering '||l_proc, 10);
500:
501: hr_utility.trace('Getting info from cache for TC '||to_char(p_time_category_id));
502:
503: END IF;
497: IF ( g_debug ) THEN
498: l_proc := g_package||'get_tc_from_cache';
499: hr_utility.set_location('Entering '||l_proc, 10);
500:
501: hr_utility.trace('Getting info from cache for TC '||to_char(p_time_category_id));
502:
503: END IF;
504:
505: l_tc_cache_start := g_tc_cache(p_time_category_id).start_index;
508: IF ( l_tc_cache_start IS NOT NULL )
509: THEN
510:
511: IF ( g_debug ) THEN
512: hr_utility.trace('Cache components exist');
513: END IF;
514:
515: FOR x IN l_tc_cache_start .. l_tc_cache_stop
516: LOOP
544:
545: ELSE
546:
547: IF ( g_debug ) THEN
548: hr_utility.trace('No Cache Components');
549: END IF;
550:
551: END IF;
552:
550:
551: END IF;
552:
553: IF ( g_debug ) THEN
554: hr_utility.set_location('Leaving '||l_proc, 20);
555: END IF;
556:
557: END get_tc_from_cache;
558:
566: IF ( g_tc_cache.EXISTS ( p_time_category_id ) )
567: THEN
568:
569: IF ( g_debug ) THEN
570: hr_utility.trace('TC cache exists');
571: END IF;
572:
573: -- is cache more than 30 mins old?
574:
575: -- 10 mins IF ( ( sysdate - g_tc_cache(p_time_category_id).cache_date ) <= .007 )
576: IF ( ( sysdate - g_tc_cache(p_time_category_id).cache_date ) <= .02 )
577: THEN
578: IF ( g_debug ) THEN
579: hr_utility.trace('TC Cache Current');
580: END IF;
581:
582: RETURN TRUE;
583: ELSE
581:
582: RETURN TRUE;
583: ELSE
584: IF ( g_debug ) THEN
585: hr_utility.trace('TC Cache Expired');
586: END IF;
587:
588: g_tc_cache.DELETE;
589: g_tc_bb_ok_cache.DELETE;
592: END IF;
593: ELSE
594:
595: IF ( g_debug ) THEN
596: hr_utility.trace('TC Cache does not exist');
597: END IF;
598:
599: RETURN FALSE;
600: END IF;
617:
618:
619: IF ( g_debug ) THEN
620: l_proc := g_package||'get_bb_ok_tab_from_string';
621: hr_utility.set_location('Entering '||l_proc, 10);
622: END IF;
623:
624: l_bb_ok_string := g_tc_bb_ok_cache(p_time_category_id).bb_ok_string;
625:
647:
648: END LOOP;
649:
650: IF ( g_debug ) THEN
651: hr_utility.set_location('Leaving '||l_proc, 20);
652: END IF;
653:
654: RETURN l_bb_ok_tab;
655:
1082:
1083: IF ( l_return )
1084: THEN
1085: IF ( g_debug ) THEN
1086: hr_utility.trace('Continue evaluation is TRUE');
1087: END IF;
1088: ELSE
1089: IF ( g_debug ) THEN
1090: hr_utility.trace('Continue evaluation is FALSE');
1086: hr_utility.trace('Continue evaluation is TRUE');
1087: END IF;
1088: ELSE
1089: IF ( g_debug ) THEN
1090: hr_utility.trace('Continue evaluation is FALSE');
1091: END IF;
1092: END IF;
1093:
1094: RETURN l_return;
1117:
1118:
1119: IF ( g_debug ) THEN
1120: l_proc := g_package||'get_dyn_sql';
1121: hr_utility.trace('get dyn sql params are ....');
1122: hr_utility.trace('dyn sql is '||p_time_sql);
1123: END IF;
1124:
1125: -- we want the dynamic sql string
1118:
1119: IF ( g_debug ) THEN
1120: l_proc := g_package||'get_dyn_sql';
1121: hr_utility.trace('get dyn sql params are ....');
1122: hr_utility.trace('dyn sql is '||p_time_sql);
1123: END IF;
1124:
1125: -- we want the dynamic sql string
1126:
1172: ELSIF ( ( l_value_string = '
1173: THEN
1174:
1175: IF ( g_debug ) THEN
1176: hr_utility.trace('GAZ - INVALID COMBO');
1177: END IF;
1178:
1179: fnd_message.set_name('HXC', 'HXC_TC_INV_EQUAL_IS_NULL_COMBO');
1180: fnd_message.raise_error;
1256: -- EAch Time Category SQL to be evaluated seperately from
1257: -- now on so combining of TIME_SQL not necessary
1258:
1259: IF ( g_debug ) THEN
1260: hr_utility.trace('GAZ - another TC !!!!');
1261: END IF;
1262:
1263: END IF;
1264:
1264:
1265: ELSE
1266:
1267: IF ( g_debug ) THEN
1268: hr_utility.trace('not first time round');
1269: hr_utility.trace('sql is '||l_dyn_sql);
1270: END IF;
1271:
1272: -- set l_string_start for the case when generating SQL for alernate name
1265: ELSE
1266:
1267: IF ( g_debug ) THEN
1268: hr_utility.trace('not first time round');
1269: hr_utility.trace('sql is '||l_dyn_sql);
1270: END IF;
1271:
1272: -- set l_string_start for the case when generating SQL for alernate name
1273:
1293: ELSIF ( ( l_value_string = '
1294: THEN
1295:
1296: IF ( g_debug ) THEN
1297: hr_utility.trace('GAZ - INVALID COMBO');
1298: END IF;
1299:
1300: fnd_message.set_name('HXC', 'HXC_TC_INV_EQUAL_IS_NULL_COMBO');
1301: fnd_message.raise_error;
1367: -- EAch Time Category SQL to be evaluated seperately from
1368: -- now on so combining of TIME_SQL not necessary
1369:
1370: IF ( g_debug ) THEN
1371: hr_utility.trace('GAZ - another TC !!!!');
1372: END IF;
1373:
1374: END IF;
1375: END IF;
1374: END IF;
1375: END IF;
1376:
1377: IF ( g_debug ) THEN
1378: hr_utility.trace('dyn sql is '||l_dyn_sql);
1379: END IF;
1380:
1381: p_time_sql := l_dyn_sql;
1382:
1529:
1530:
1531: BEGIN -- mapping_component_string
1532:
1533: g_debug := hr_utility.debug_enabled;
1534:
1535: l_first_time_round := TRUE;
1536:
1537: -- ***************************************
1539: -- ***************************************
1540:
1541: IF ( g_debug ) THEN
1542: l_proc := g_package||'mapping_component_string';
1543: hr_utility.set_location('Processing '||l_proc, 10);
1544:
1545: hr_utility.trace('Time Category ID is '||to_char(p_time_category_id));
1546: END IF;
1547:
1541: IF ( g_debug ) THEN
1542: l_proc := g_package||'mapping_component_string';
1543: hr_utility.set_location('Processing '||l_proc, 10);
1544:
1545: hr_utility.trace('Time Category ID is '||to_char(p_time_category_id));
1546: END IF;
1547:
1548: -- get the time category operator
1549:
1558: OPEN csr_get_category_comps ( p_time_category_id );
1559: FETCH csr_get_category_comps INTO l_comps_r;
1560:
1561: IF ( g_debug ) THEN
1562: hr_utility.set_location('Processing '||l_proc, 20);
1563: END IF;
1564:
1565: WHILE csr_get_category_comps%FOUND
1566: LOOP
1565: WHILE csr_get_category_comps%FOUND
1566: LOOP
1567:
1568: IF ( g_debug ) THEN
1569: hr_utility.set_location('Processing '||l_proc, 30);
1570: END IF;
1571:
1572: get_dyn_sql ( p_time_sql => l_dynamic_sql
1573: , p_comps_r => l_comps_r
1573: , p_comps_r => l_comps_r
1574: , p_operator => l_operator );
1575:
1576: IF ( g_debug ) THEN
1577: hr_utility.set_location('Processing '||l_proc, 60);
1578: END IF;
1579:
1580: FETCH csr_get_category_comps INTO l_comps_r;
1581:
1583:
1584: END LOOP;
1585:
1586: IF ( g_debug ) THEN
1587: hr_utility.set_location('Processing '||l_proc, 70);
1588: END IF;
1589:
1590: CLOSE csr_get_category_comps;
1591:
1589:
1590: CLOSE csr_get_category_comps;
1591:
1592: IF ( g_debug ) THEN
1593: hr_utility.set_location('Processing '||l_proc, 80);
1594: END IF;
1595:
1596: IF ( l_dynamic_sql IS NOT NULL )
1597: THEN
1603:
1604: p_time_sql := l_dynamic_sql;
1605:
1606: IF ( g_debug ) THEN
1607: hr_utility.trace('Final dyn sql is '||NVL(p_time_sql,'Empty'));
1608: END IF;
1609:
1610: END mapping_component_string;
1611:
1633: l_first_context hxc_bld_blk_info_types.bld_blk_info_type%TYPE;
1634:
1635: BEGIN -- alternate_name_string
1636:
1637: g_debug := hr_utility.debug_enabled;
1638:
1639: IF ( g_debug ) THEN
1640: l_proc := g_package||'alternate_name_string';
1641: hr_utility.set_location('Entering '||l_proc, 10);
1637: g_debug := hr_utility.debug_enabled;
1638:
1639: IF ( g_debug ) THEN
1640: l_proc := g_package||'alternate_name_string';
1641: hr_utility.set_location('Entering '||l_proc, 10);
1642: END IF;
1643:
1644: l_comps_t.DELETE;
1645:
1649: -- ALTERNATE_NAME_STRING
1650: -- ***************************************
1651:
1652: IF ( g_debug ) THEN
1653: hr_utility.set_location('Processing '||l_proc, 20);
1654: END IF;
1655:
1656: OPEN csr_get_alternate_name_comps ( p_alias_value_id );
1657: FETCH csr_get_alternate_name_comps INTO l_alternate_name_comps_r;
1658:
1659: l_first_context := l_alternate_name_comps_r.context;
1660:
1661: IF ( g_debug ) THEN
1662: hr_utility.set_location('Processing '||l_proc, 25);
1663: END IF;
1664:
1665: WHILE csr_get_alternate_name_comps%FOUND
1666: LOOP
1665: WHILE csr_get_alternate_name_comps%FOUND
1666: LOOP
1667:
1668: IF ( g_debug ) THEN
1669: hr_utility.set_location('Processing '||l_proc, 30);
1670: END IF;
1671:
1672: l_value := get_alternate_name_value ( p_alternate_name_comp_r => l_alternate_name_comps_r );
1673:
1686: l_comps_t(l_ind).flex_value_set_id := -1; -- dummy value not used
1687: l_comps_t(l_ind).equal_to := p_equal_to;
1688:
1689: IF ( g_debug ) THEN
1690: hr_utility.set_location('Processing '||l_proc, 60);
1691: END IF;
1692:
1693: FETCH csr_get_alternate_name_comps INTO l_alternate_name_comps_r;
1694:
1710:
1711: END LOOP;
1712:
1713: IF ( g_debug ) THEN
1714: hr_utility.set_location('Processing '||l_proc, 70);
1715: END IF;
1716:
1717: CLOSE csr_get_alternate_name_comps;
1718:
1739:
1740: END LOOP;
1741:
1742: IF ( g_debug ) THEN
1743: hr_utility.set_location('Processing '||l_proc, 80);
1744: END IF;
1745:
1746: IF ( l_dynamic_sql IS NOT NULL )
1747: THEN
1750:
1751: p_time_sql := l_dynamic_sql;
1752:
1753: IF ( g_debug ) THEN
1754: hr_utility.trace('alternate name sql is '||p_time_sql);
1755: END IF;
1756:
1757: END alternate_name_string;
1758:
2034:
2035:
2036:
2037: IF ( g_debug ) THEN
2038: hr_utility.trace('Here are the build blocks....');
2039: END IF;
2040:
2041: OPEN gaz_blk;
2042: FETCH gaz_blk into l_blk;
2044: WHILE gaz_blk%FOUND
2045: LOOP
2046:
2047: IF ( g_debug ) THEN
2048: hr_utility.trace('bb id is : '||to_char(l_blk.bb_id));
2049: hr_utility.trace('measure is : '||to_char(l_blk.measure));
2050: hr_utility.trace('scope is : '||l_blk.scope);
2051: hr_utility.trace('start time : '||to_char(l_blk.start_time,'hh24:mi:ss dd-mon-yy'));
2052: hr_utility.trace('stop time : '||to_char(l_blk.stop_time,'hh24:mi:ss dd-mon-yy'));
2045: LOOP
2046:
2047: IF ( g_debug ) THEN
2048: hr_utility.trace('bb id is : '||to_char(l_blk.bb_id));
2049: hr_utility.trace('measure is : '||to_char(l_blk.measure));
2050: hr_utility.trace('scope is : '||l_blk.scope);
2051: hr_utility.trace('start time : '||to_char(l_blk.start_time,'hh24:mi:ss dd-mon-yy'));
2052: hr_utility.trace('stop time : '||to_char(l_blk.stop_time,'hh24:mi:ss dd-mon-yy'));
2053: END IF;
2046:
2047: IF ( g_debug ) THEN
2048: hr_utility.trace('bb id is : '||to_char(l_blk.bb_id));
2049: hr_utility.trace('measure is : '||to_char(l_blk.measure));
2050: hr_utility.trace('scope is : '||l_blk.scope);
2051: hr_utility.trace('start time : '||to_char(l_blk.start_time,'hh24:mi:ss dd-mon-yy'));
2052: hr_utility.trace('stop time : '||to_char(l_blk.stop_time,'hh24:mi:ss dd-mon-yy'));
2053: END IF;
2054:
2047: IF ( g_debug ) THEN
2048: hr_utility.trace('bb id is : '||to_char(l_blk.bb_id));
2049: hr_utility.trace('measure is : '||to_char(l_blk.measure));
2050: hr_utility.trace('scope is : '||l_blk.scope);
2051: hr_utility.trace('start time : '||to_char(l_blk.start_time,'hh24:mi:ss dd-mon-yy'));
2052: hr_utility.trace('stop time : '||to_char(l_blk.stop_time,'hh24:mi:ss dd-mon-yy'));
2053: END IF;
2054:
2055: FETCH gaz_blk INTO l_blk;
2048: hr_utility.trace('bb id is : '||to_char(l_blk.bb_id));
2049: hr_utility.trace('measure is : '||to_char(l_blk.measure));
2050: hr_utility.trace('scope is : '||l_blk.scope);
2051: hr_utility.trace('start time : '||to_char(l_blk.start_time,'hh24:mi:ss dd-mon-yy'));
2052: hr_utility.trace('stop time : '||to_char(l_blk.stop_time,'hh24:mi:ss dd-mon-yy'));
2053: END IF;
2054:
2055: FETCH gaz_blk INTO l_blk;
2056:
2058:
2059: CLOSE gaz_blk;
2060:
2061: IF ( g_debug ) THEN
2062: hr_utility.trace('Here are the attributes ....');
2063: END IF;
2064:
2065: OPEN gaz_att;
2066: FETCH gaz_att into l_att;
2068: WHILE gaz_att%FOUND
2069: LOOP
2070:
2071: IF ( g_debug ) THEN
2072: hr_utility.trace('ta id is : '||to_char(l_att.ta_id));
2073: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2074: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2075: hr_utility.trace('att cat is : '||l_att.attribute_category);
2076: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2069: LOOP
2070:
2071: IF ( g_debug ) THEN
2072: hr_utility.trace('ta id is : '||to_char(l_att.ta_id));
2073: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2074: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2075: hr_utility.trace('att cat is : '||l_att.attribute_category);
2076: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2077: hr_utility.trace('att 1 is : '||l_att.attribute1);
2070:
2071: IF ( g_debug ) THEN
2072: hr_utility.trace('ta id is : '||to_char(l_att.ta_id));
2073: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2074: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2075: hr_utility.trace('att cat is : '||l_att.attribute_category);
2076: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2077: hr_utility.trace('att 1 is : '||l_att.attribute1);
2078: hr_utility.trace('att 2 is : '||l_att.attribute2);
2071: IF ( g_debug ) THEN
2072: hr_utility.trace('ta id is : '||to_char(l_att.ta_id));
2073: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2074: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2075: hr_utility.trace('att cat is : '||l_att.attribute_category);
2076: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2077: hr_utility.trace('att 1 is : '||l_att.attribute1);
2078: hr_utility.trace('att 2 is : '||l_att.attribute2);
2079: hr_utility.trace('att 3 is : '||l_att.attribute3);
2072: hr_utility.trace('ta id is : '||to_char(l_att.ta_id));
2073: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2074: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2075: hr_utility.trace('att cat is : '||l_att.attribute_category);
2076: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2077: hr_utility.trace('att 1 is : '||l_att.attribute1);
2078: hr_utility.trace('att 2 is : '||l_att.attribute2);
2079: hr_utility.trace('att 3 is : '||l_att.attribute3);
2080: hr_utility.trace('att 4 is : '||l_att.attribute4);
2073: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2074: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2075: hr_utility.trace('att cat is : '||l_att.attribute_category);
2076: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2077: hr_utility.trace('att 1 is : '||l_att.attribute1);
2078: hr_utility.trace('att 2 is : '||l_att.attribute2);
2079: hr_utility.trace('att 3 is : '||l_att.attribute3);
2080: hr_utility.trace('att 4 is : '||l_att.attribute4);
2081: hr_utility.trace('att 5 is : '||l_att.attribute5);
2074: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2075: hr_utility.trace('att cat is : '||l_att.attribute_category);
2076: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2077: hr_utility.trace('att 1 is : '||l_att.attribute1);
2078: hr_utility.trace('att 2 is : '||l_att.attribute2);
2079: hr_utility.trace('att 3 is : '||l_att.attribute3);
2080: hr_utility.trace('att 4 is : '||l_att.attribute4);
2081: hr_utility.trace('att 5 is : '||l_att.attribute5);
2082: END IF;
2075: hr_utility.trace('att cat is : '||l_att.attribute_category);
2076: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2077: hr_utility.trace('att 1 is : '||l_att.attribute1);
2078: hr_utility.trace('att 2 is : '||l_att.attribute2);
2079: hr_utility.trace('att 3 is : '||l_att.attribute3);
2080: hr_utility.trace('att 4 is : '||l_att.attribute4);
2081: hr_utility.trace('att 5 is : '||l_att.attribute5);
2082: END IF;
2083:
2076: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2077: hr_utility.trace('att 1 is : '||l_att.attribute1);
2078: hr_utility.trace('att 2 is : '||l_att.attribute2);
2079: hr_utility.trace('att 3 is : '||l_att.attribute3);
2080: hr_utility.trace('att 4 is : '||l_att.attribute4);
2081: hr_utility.trace('att 5 is : '||l_att.attribute5);
2082: END IF;
2083:
2084: FETCH gaz_att INTO l_att;
2077: hr_utility.trace('att 1 is : '||l_att.attribute1);
2078: hr_utility.trace('att 2 is : '||l_att.attribute2);
2079: hr_utility.trace('att 3 is : '||l_att.attribute3);
2080: hr_utility.trace('att 4 is : '||l_att.attribute4);
2081: hr_utility.trace('att 5 is : '||l_att.attribute5);
2082: END IF;
2083:
2084: FETCH gaz_att INTO l_att;
2085:
2127: l_trunc_atts VARCHAR2(30) := 'delete from hxc_tmp_atts';
2128:
2129: BEGIN
2130:
2131: g_debug := hr_utility.debug_enabled;
2132:
2133: IF ( g_debug ) THEN
2134: l_proc := g_package||'push_timecard';
2135: hr_utility.set_location('Entering '||l_proc, 10);
2131: g_debug := hr_utility.debug_enabled;
2132:
2133: IF ( g_debug ) THEN
2134: l_proc := g_package||'push_timecard';
2135: hr_utility.set_location('Entering '||l_proc, 10);
2136: END IF;
2137:
2138: execute immediate l_trunc_blks;
2139: execute immediate l_trunc_atts;
2147: WHILE l_ind is not null
2148: loop
2149:
2150: IF ( g_debug ) THEN
2151: hr_utility.trace('scope is '||p_blocks(l_ind).scope);
2152: hr_utility.trace('start time is '||p_blocks(l_ind).start_time);
2153: hr_utility.trace('stop time is '||p_blocks(l_ind).stop_time);
2154: END IF;
2155:
2148: loop
2149:
2150: IF ( g_debug ) THEN
2151: hr_utility.trace('scope is '||p_blocks(l_ind).scope);
2152: hr_utility.trace('start time is '||p_blocks(l_ind).start_time);
2153: hr_utility.trace('stop time is '||p_blocks(l_ind).stop_time);
2154: END IF;
2155:
2156: l_ind := p_blocks.NEXT(l_ind);
2149:
2150: IF ( g_debug ) THEN
2151: hr_utility.trace('scope is '||p_blocks(l_ind).scope);
2152: hr_utility.trace('start time is '||p_blocks(l_ind).start_time);
2153: hr_utility.trace('stop time is '||p_blocks(l_ind).stop_time);
2154: END IF;
2155:
2156: l_ind := p_blocks.NEXT(l_ind);
2157:
2174: IF ( FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).date_to) = hr_general.end_of_time )
2175: THEN
2176:
2177: IF ( g_debug ) THEN
2178: hr_utility.trace('Scope : start time '||p_blocks(l_ind).scope||' : '||p_blocks(l_ind).start_time);
2179: END IF;
2180:
2181: IF ( NOT p_detail_blocks_only )
2182: THEN
2307:
2308: -- gaz_debug_push_timecard;
2309:
2310: IF ( g_debug ) THEN
2311: hr_utility.set_location('Leaving '||l_proc, 110);
2312: END IF;
2313:
2314: END push_timecard;
2315:
2411:
2412:
2413: IF ( g_debug ) THEN
2414: l_proc := g_package||'evaluate_time_sql';
2415: hr_utility.trace('Params for Evaluate Time SQL are ....');
2416:
2417: hr_utility.trace('p_time_sql is : '|| l_time_sql );
2418: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2419: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2413: IF ( g_debug ) THEN
2414: l_proc := g_package||'evaluate_time_sql';
2415: hr_utility.trace('Params for Evaluate Time SQL are ....');
2416:
2417: hr_utility.trace('p_time_sql is : '|| l_time_sql );
2418: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2419: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2420: hr_utility.trace('p_operator is : '||p_operator);
2421:
2414: l_proc := g_package||'evaluate_time_sql';
2415: hr_utility.trace('Params for Evaluate Time SQL are ....');
2416:
2417: hr_utility.trace('p_time_sql is : '|| l_time_sql );
2418: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2419: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2420: hr_utility.trace('p_operator is : '||p_operator);
2421:
2422: hr_utility.set_location('Entering '||l_proc, 10);
2415: hr_utility.trace('Params for Evaluate Time SQL are ....');
2416:
2417: hr_utility.trace('p_time_sql is : '|| l_time_sql );
2418: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2419: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2420: hr_utility.trace('p_operator is : '||p_operator);
2421:
2422: hr_utility.set_location('Entering '||l_proc, 10);
2423: END IF;
2416:
2417: hr_utility.trace('p_time_sql is : '|| l_time_sql );
2418: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2419: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2420: hr_utility.trace('p_operator is : '||p_operator);
2421:
2422: hr_utility.set_location('Entering '||l_proc, 10);
2423: END IF;
2424:
2418: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2419: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2420: hr_utility.trace('p_operator is : '||p_operator);
2421:
2422: hr_utility.set_location('Entering '||l_proc, 10);
2423: END IF;
2424:
2425:
2426: l_bb_not_ok_string := p_tc_bb_not_ok_string;
2430:
2431: IF g_debug
2432: THEN
2433:
2434: hr_utility.trace('l_parse_time_sql is ');
2435: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2436: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2437: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2438: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2431: IF g_debug
2432: THEN
2433:
2434: hr_utility.trace('l_parse_time_sql is ');
2435: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2436: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2437: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2438: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2439: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2432: THEN
2433:
2434: hr_utility.trace('l_parse_time_sql is ');
2435: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2436: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2437: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2438: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2439: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2440: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2433:
2434: hr_utility.trace('l_parse_time_sql is ');
2435: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2436: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2437: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2438: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2439: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2440: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2441: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2434: hr_utility.trace('l_parse_time_sql is ');
2435: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2436: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2437: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2438: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2439: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2440: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2441: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2442: hr_utility.trace( substr(l_parse_time_sql,1751,250) );
2435: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2436: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2437: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2438: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2439: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2440: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2441: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2442: hr_utility.trace( substr(l_parse_time_sql,1751,250) );
2443:
2436: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2437: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2438: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2439: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2440: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2441: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2442: hr_utility.trace( substr(l_parse_time_sql,1751,250) );
2443:
2444: END IF;
2437: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2438: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2439: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2440: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2441: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2442: hr_utility.trace( substr(l_parse_time_sql,1751,250) );
2443:
2444: END IF;
2445:
2438: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2439: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2440: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2441: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2442: hr_utility.trace( substr(l_parse_time_sql,1751,250) );
2443:
2444: END IF;
2445:
2446: IF ( g_params.p_use_temp_table )
2447: THEN
2448:
2449: IF g_debug
2450: THEN
2451: hr_utility.trace('g_params.p_use_temp_table is TRUE ');
2452: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2453: END IF;
2454:
2455: IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )THEN
2448:
2449: IF g_debug
2450: THEN
2451: hr_utility.trace('g_params.p_use_temp_table is TRUE ');
2452: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2453: END IF;
2454:
2455: IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )THEN
2456:
2470:
2471:
2472: IF g_debug
2473: THEN
2474: hr_utility.trace(' g_params.p_scope is DETAIL ');
2475: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2476: END IF;
2477:
2478: IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )
2471:
2472: IF g_debug
2473: THEN
2474: hr_utility.trace(' g_params.p_scope is DETAIL ');
2475: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2476: END IF;
2477:
2478: IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )
2479: THEN
2497: THEN
2498:
2499: IF g_debug
2500: THEN
2501: hr_utility.trace(' g_params.p_scope is TIME ');
2502: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2503: END IF;
2504:
2505:
2498:
2499: IF g_debug
2500: THEN
2501: hr_utility.trace(' g_params.p_scope is TIME ');
2502: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2503: END IF;
2504:
2505:
2506: -- sum for timecard
2530:
2531: END IF; -- p_use_temp_table
2532:
2533: IF ( g_debug ) THEN
2534: hr_utility.trace( 'dynamic time sql is ');
2535: hr_utility.trace( substr(l_sql,1,250) );
2536: hr_utility.trace( substr(l_sql,251,250) );
2537: hr_utility.trace( substr(l_sql,501,250) );
2538: hr_utility.trace( substr(l_sql,751,250) );
2531: END IF; -- p_use_temp_table
2532:
2533: IF ( g_debug ) THEN
2534: hr_utility.trace( 'dynamic time sql is ');
2535: hr_utility.trace( substr(l_sql,1,250) );
2536: hr_utility.trace( substr(l_sql,251,250) );
2537: hr_utility.trace( substr(l_sql,501,250) );
2538: hr_utility.trace( substr(l_sql,751,250) );
2539: hr_utility.trace( substr(l_sql,1001,250) );
2532:
2533: IF ( g_debug ) THEN
2534: hr_utility.trace( 'dynamic time sql is ');
2535: hr_utility.trace( substr(l_sql,1,250) );
2536: hr_utility.trace( substr(l_sql,251,250) );
2537: hr_utility.trace( substr(l_sql,501,250) );
2538: hr_utility.trace( substr(l_sql,751,250) );
2539: hr_utility.trace( substr(l_sql,1001,250) );
2540: hr_utility.trace( substr(l_sql,1251,250) );
2533: IF ( g_debug ) THEN
2534: hr_utility.trace( 'dynamic time sql is ');
2535: hr_utility.trace( substr(l_sql,1,250) );
2536: hr_utility.trace( substr(l_sql,251,250) );
2537: hr_utility.trace( substr(l_sql,501,250) );
2538: hr_utility.trace( substr(l_sql,751,250) );
2539: hr_utility.trace( substr(l_sql,1001,250) );
2540: hr_utility.trace( substr(l_sql,1251,250) );
2541: hr_utility.trace( substr(l_sql,1501,250) );
2534: hr_utility.trace( 'dynamic time sql is ');
2535: hr_utility.trace( substr(l_sql,1,250) );
2536: hr_utility.trace( substr(l_sql,251,250) );
2537: hr_utility.trace( substr(l_sql,501,250) );
2538: hr_utility.trace( substr(l_sql,751,250) );
2539: hr_utility.trace( substr(l_sql,1001,250) );
2540: hr_utility.trace( substr(l_sql,1251,250) );
2541: hr_utility.trace( substr(l_sql,1501,250) );
2542: hr_utility.trace( substr(l_sql,1751,250) );
2535: hr_utility.trace( substr(l_sql,1,250) );
2536: hr_utility.trace( substr(l_sql,251,250) );
2537: hr_utility.trace( substr(l_sql,501,250) );
2538: hr_utility.trace( substr(l_sql,751,250) );
2539: hr_utility.trace( substr(l_sql,1001,250) );
2540: hr_utility.trace( substr(l_sql,1251,250) );
2541: hr_utility.trace( substr(l_sql,1501,250) );
2542: hr_utility.trace( substr(l_sql,1751,250) );
2543: END IF;
2536: hr_utility.trace( substr(l_sql,251,250) );
2537: hr_utility.trace( substr(l_sql,501,250) );
2538: hr_utility.trace( substr(l_sql,751,250) );
2539: hr_utility.trace( substr(l_sql,1001,250) );
2540: hr_utility.trace( substr(l_sql,1251,250) );
2541: hr_utility.trace( substr(l_sql,1501,250) );
2542: hr_utility.trace( substr(l_sql,1751,250) );
2543: END IF;
2544:
2537: hr_utility.trace( substr(l_sql,501,250) );
2538: hr_utility.trace( substr(l_sql,751,250) );
2539: hr_utility.trace( substr(l_sql,1001,250) );
2540: hr_utility.trace( substr(l_sql,1251,250) );
2541: hr_utility.trace( substr(l_sql,1501,250) );
2542: hr_utility.trace( substr(l_sql,1751,250) );
2543: END IF;
2544:
2545: -- for the AND operator need to reset p_tc_bb_ok_string
2538: hr_utility.trace( substr(l_sql,751,250) );
2539: hr_utility.trace( substr(l_sql,1001,250) );
2540: hr_utility.trace( substr(l_sql,1251,250) );
2541: hr_utility.trace( substr(l_sql,1501,250) );
2542: hr_utility.trace( substr(l_sql,1751,250) );
2543: END IF;
2544:
2545: -- for the AND operator need to reset p_tc_bb_ok_string
2546: -- since each AND evaluation should start from scratch
2655:
2656: l_rows_fetched := dbms_sql.fetch_rows ( l_csr );
2657:
2658: IF ( g_debug ) THEN
2659: hr_utility.trace('l rows fetched is '||to_char(l_rows_fetched));
2660: END IF;
2661:
2662: IF ( l_rows_fetched > 0 )
2663: THEN
2674:
2675: p_tc_bb_ok_tab(t_bb_id(x)).bb_id_ok := 'Y';
2676:
2677: IF ( g_debug ) THEN
2678: hr_utility.trace('bb ok id is '||to_char(t_bb_id(x)));
2679: END IF;
2680:
2681: -- maintain bb ok string for OR operator
2682:
2687: l_bb_ok_string := l_bb_ok_string || ', ' || to_char(t_bb_id(x));
2688: END IF;
2689:
2690: IF ( g_debug ) THEN
2691: hr_utility.trace('bb ok string is '||l_bb_ok_string);
2692: hr_utility.trace('bb NOT ok string is '||l_bb_not_ok_string);
2693: END IF;
2694:
2695: -- maintain bb not ok string i.e. remove the building block from the string
2688: END IF;
2689:
2690: IF ( g_debug ) THEN
2691: hr_utility.trace('bb ok string is '||l_bb_ok_string);
2692: hr_utility.trace('bb NOT ok string is '||l_bb_not_ok_string);
2693: END IF;
2694:
2695: -- maintain bb not ok string i.e. remove the building block from the string
2696:
2717:
2718: END IF;
2719:
2720: IF ( g_debug ) THEN
2721: hr_utility.trace('bb NOT ok string AFTER is '||l_bb_not_ok_string);
2722: END IF;
2723:
2724: END LOOP;
2725:
2731:
2732: dbms_sql.close_cursor ( l_csr );
2733:
2734: IF ( g_debug ) THEN
2735: hr_utility.trace('GAZ - BB OK string is '||l_bb_ok_string);
2736: hr_utility.trace('GAZ - BB NOT OK string is '||l_bb_not_ok_string);
2737: END IF;
2738:
2739: p_tc_bb_ok_string := l_bb_ok_string;
2732: dbms_sql.close_cursor ( l_csr );
2733:
2734: IF ( g_debug ) THEN
2735: hr_utility.trace('GAZ - BB OK string is '||l_bb_ok_string);
2736: hr_utility.trace('GAZ - BB NOT OK string is '||l_bb_not_ok_string);
2737: END IF;
2738:
2739: p_tc_bb_ok_string := l_bb_ok_string;
2740: p_tc_bb_not_ok_string := l_bb_not_ok_string;
2739: p_tc_bb_ok_string := l_bb_ok_string;
2740: p_tc_bb_not_ok_string := l_bb_not_ok_string;
2741:
2742: IF ( g_debug ) THEN
2743: hr_utility.set_location('Leaving '||l_proc, 170);
2744: END IF;
2745:
2746: exception when others then
2747:
2745:
2746: exception when others then
2747:
2748: IF ( g_debug ) THEN
2749: hr_utility.trace('in exception');
2750: END IF;
2751:
2752: raise;
2753:
2857: ,VALUESET => r_valueset
2858: ,FORMAT => r_format );
2859:
2860: IF ( g_debug ) THEN
2861: hr_utility.trace('where is '||r_valueset.table_info.where_clause);
2862: END IF;
2863:
2864:
2865:
2890: r_valueset.table_info.table_name || ' ' ||
2891: l_where_clause || ' ) ';
2892:
2893: IF ( g_debug ) THEN
2894: hr_utility.trace('Value Set SQL is ');
2895:
2896: hr_utility.trace( substr(l_vs_sql,1,250) );
2897: hr_utility.trace( substr(l_vs_sql,251,250) );
2898: hr_utility.trace( substr(l_vs_sql,501,250) );
2892:
2893: IF ( g_debug ) THEN
2894: hr_utility.trace('Value Set SQL is ');
2895:
2896: hr_utility.trace( substr(l_vs_sql,1,250) );
2897: hr_utility.trace( substr(l_vs_sql,251,250) );
2898: hr_utility.trace( substr(l_vs_sql,501,250) );
2899: hr_utility.trace( substr(l_vs_sql,751,250) );
2900: hr_utility.trace( substr(l_vs_sql,1001,250) );
2893: IF ( g_debug ) THEN
2894: hr_utility.trace('Value Set SQL is ');
2895:
2896: hr_utility.trace( substr(l_vs_sql,1,250) );
2897: hr_utility.trace( substr(l_vs_sql,251,250) );
2898: hr_utility.trace( substr(l_vs_sql,501,250) );
2899: hr_utility.trace( substr(l_vs_sql,751,250) );
2900: hr_utility.trace( substr(l_vs_sql,1001,250) );
2901: hr_utility.trace( substr(l_vs_sql,1251,250) );
2894: hr_utility.trace('Value Set SQL is ');
2895:
2896: hr_utility.trace( substr(l_vs_sql,1,250) );
2897: hr_utility.trace( substr(l_vs_sql,251,250) );
2898: hr_utility.trace( substr(l_vs_sql,501,250) );
2899: hr_utility.trace( substr(l_vs_sql,751,250) );
2900: hr_utility.trace( substr(l_vs_sql,1001,250) );
2901: hr_utility.trace( substr(l_vs_sql,1251,250) );
2902: hr_utility.trace( substr(l_vs_sql,1501,250) );
2895:
2896: hr_utility.trace( substr(l_vs_sql,1,250) );
2897: hr_utility.trace( substr(l_vs_sql,251,250) );
2898: hr_utility.trace( substr(l_vs_sql,501,250) );
2899: hr_utility.trace( substr(l_vs_sql,751,250) );
2900: hr_utility.trace( substr(l_vs_sql,1001,250) );
2901: hr_utility.trace( substr(l_vs_sql,1251,250) );
2902: hr_utility.trace( substr(l_vs_sql,1501,250) );
2903: hr_utility.trace( substr(l_vs_sql,1751,250) );
2896: hr_utility.trace( substr(l_vs_sql,1,250) );
2897: hr_utility.trace( substr(l_vs_sql,251,250) );
2898: hr_utility.trace( substr(l_vs_sql,501,250) );
2899: hr_utility.trace( substr(l_vs_sql,751,250) );
2900: hr_utility.trace( substr(l_vs_sql,1001,250) );
2901: hr_utility.trace( substr(l_vs_sql,1251,250) );
2902: hr_utility.trace( substr(l_vs_sql,1501,250) );
2903: hr_utility.trace( substr(l_vs_sql,1751,250) );
2904: END IF;
2897: hr_utility.trace( substr(l_vs_sql,251,250) );
2898: hr_utility.trace( substr(l_vs_sql,501,250) );
2899: hr_utility.trace( substr(l_vs_sql,751,250) );
2900: hr_utility.trace( substr(l_vs_sql,1001,250) );
2901: hr_utility.trace( substr(l_vs_sql,1251,250) );
2902: hr_utility.trace( substr(l_vs_sql,1501,250) );
2903: hr_utility.trace( substr(l_vs_sql,1751,250) );
2904: END IF;
2905:
2898: hr_utility.trace( substr(l_vs_sql,501,250) );
2899: hr_utility.trace( substr(l_vs_sql,751,250) );
2900: hr_utility.trace( substr(l_vs_sql,1001,250) );
2901: hr_utility.trace( substr(l_vs_sql,1251,250) );
2902: hr_utility.trace( substr(l_vs_sql,1501,250) );
2903: hr_utility.trace( substr(l_vs_sql,1751,250) );
2904: END IF;
2905:
2906: l_vs_sql := REPLACE ( l_sql, '
2899: hr_utility.trace( substr(l_vs_sql,751,250) );
2900: hr_utility.trace( substr(l_vs_sql,1001,250) );
2901: hr_utility.trace( substr(l_vs_sql,1251,250) );
2902: hr_utility.trace( substr(l_vs_sql,1501,250) );
2903: hr_utility.trace( substr(l_vs_sql,1751,250) );
2904: END IF;
2905:
2906: l_vs_sql := REPLACE ( l_sql, '
2907:
2905:
2906: l_vs_sql := REPLACE ( l_sql, '
2907:
2908: IF ( g_debug ) THEN
2909: hr_utility.trace('Final Value Set String ...');
2910: hr_utility.trace( substr(l_vs_sql,1,250) );
2911: hr_utility.trace( substr(l_vs_sql,251,250) );
2912: hr_utility.trace( substr(l_vs_sql,501,250) );
2913: hr_utility.trace( substr(l_vs_sql,751,250) );
2906: l_vs_sql := REPLACE ( l_sql, '
2907:
2908: IF ( g_debug ) THEN
2909: hr_utility.trace('Final Value Set String ...');
2910: hr_utility.trace( substr(l_vs_sql,1,250) );
2911: hr_utility.trace( substr(l_vs_sql,251,250) );
2912: hr_utility.trace( substr(l_vs_sql,501,250) );
2913: hr_utility.trace( substr(l_vs_sql,751,250) );
2914: hr_utility.trace( substr(l_vs_sql,1001,250) );
2907:
2908: IF ( g_debug ) THEN
2909: hr_utility.trace('Final Value Set String ...');
2910: hr_utility.trace( substr(l_vs_sql,1,250) );
2911: hr_utility.trace( substr(l_vs_sql,251,250) );
2912: hr_utility.trace( substr(l_vs_sql,501,250) );
2913: hr_utility.trace( substr(l_vs_sql,751,250) );
2914: hr_utility.trace( substr(l_vs_sql,1001,250) );
2915: hr_utility.trace( substr(l_vs_sql,1251,250) );
2908: IF ( g_debug ) THEN
2909: hr_utility.trace('Final Value Set String ...');
2910: hr_utility.trace( substr(l_vs_sql,1,250) );
2911: hr_utility.trace( substr(l_vs_sql,251,250) );
2912: hr_utility.trace( substr(l_vs_sql,501,250) );
2913: hr_utility.trace( substr(l_vs_sql,751,250) );
2914: hr_utility.trace( substr(l_vs_sql,1001,250) );
2915: hr_utility.trace( substr(l_vs_sql,1251,250) );
2916: hr_utility.trace( substr(l_vs_sql,1501,250) );
2909: hr_utility.trace('Final Value Set String ...');
2910: hr_utility.trace( substr(l_vs_sql,1,250) );
2911: hr_utility.trace( substr(l_vs_sql,251,250) );
2912: hr_utility.trace( substr(l_vs_sql,501,250) );
2913: hr_utility.trace( substr(l_vs_sql,751,250) );
2914: hr_utility.trace( substr(l_vs_sql,1001,250) );
2915: hr_utility.trace( substr(l_vs_sql,1251,250) );
2916: hr_utility.trace( substr(l_vs_sql,1501,250) );
2917: hr_utility.trace( substr(l_vs_sql,1751,250) );
2910: hr_utility.trace( substr(l_vs_sql,1,250) );
2911: hr_utility.trace( substr(l_vs_sql,251,250) );
2912: hr_utility.trace( substr(l_vs_sql,501,250) );
2913: hr_utility.trace( substr(l_vs_sql,751,250) );
2914: hr_utility.trace( substr(l_vs_sql,1001,250) );
2915: hr_utility.trace( substr(l_vs_sql,1251,250) );
2916: hr_utility.trace( substr(l_vs_sql,1501,250) );
2917: hr_utility.trace( substr(l_vs_sql,1751,250) );
2918: END IF;
2911: hr_utility.trace( substr(l_vs_sql,251,250) );
2912: hr_utility.trace( substr(l_vs_sql,501,250) );
2913: hr_utility.trace( substr(l_vs_sql,751,250) );
2914: hr_utility.trace( substr(l_vs_sql,1001,250) );
2915: hr_utility.trace( substr(l_vs_sql,1251,250) );
2916: hr_utility.trace( substr(l_vs_sql,1501,250) );
2917: hr_utility.trace( substr(l_vs_sql,1751,250) );
2918: END IF;
2919:
2912: hr_utility.trace( substr(l_vs_sql,501,250) );
2913: hr_utility.trace( substr(l_vs_sql,751,250) );
2914: hr_utility.trace( substr(l_vs_sql,1001,250) );
2915: hr_utility.trace( substr(l_vs_sql,1251,250) );
2916: hr_utility.trace( substr(l_vs_sql,1501,250) );
2917: hr_utility.trace( substr(l_vs_sql,1751,250) );
2918: END IF;
2919:
2920: p_vs_sql := l_vs_sql;
2913: hr_utility.trace( substr(l_vs_sql,751,250) );
2914: hr_utility.trace( substr(l_vs_sql,1001,250) );
2915: hr_utility.trace( substr(l_vs_sql,1251,250) );
2916: hr_utility.trace( substr(l_vs_sql,1501,250) );
2917: hr_utility.trace( substr(l_vs_sql,1751,250) );
2918: END IF;
2919:
2920: p_vs_sql := l_vs_sql;
2921:
2961: l_rows_processed NUMBER := 0;
2962:
2963: BEGIN
2964:
2965: g_debug := hr_utility.debug_enabled;
2966:
2967: IF ( g_debug ) THEN
2968: l_proc := g_package||'sum_bb_ok_hrs';
2969: hr_utility.trace('Params are ');
2965: g_debug := hr_utility.debug_enabled;
2966:
2967: IF ( g_debug ) THEN
2968: l_proc := g_package||'sum_bb_ok_hrs';
2969: hr_utility.trace('Params are ');
2970: hr_utility.trace('p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2971: hr_utility.trace('p_hrs are '||to_char(p_hrs));
2972: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
2973: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
2966:
2967: IF ( g_debug ) THEN
2968: l_proc := g_package||'sum_bb_ok_hrs';
2969: hr_utility.trace('Params are ');
2970: hr_utility.trace('p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2971: hr_utility.trace('p_hrs are '||to_char(p_hrs));
2972: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
2973: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
2974:
2967: IF ( g_debug ) THEN
2968: l_proc := g_package||'sum_bb_ok_hrs';
2969: hr_utility.trace('Params are ');
2970: hr_utility.trace('p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2971: hr_utility.trace('p_hrs are '||to_char(p_hrs));
2972: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
2973: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
2974:
2975:
2968: l_proc := g_package||'sum_bb_ok_hrs';
2969: hr_utility.trace('Params are ');
2970: hr_utility.trace('p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2971: hr_utility.trace('p_hrs are '||to_char(p_hrs));
2972: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
2973: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
2974:
2975:
2976: hr_utility.set_location('Entering '||l_proc, 10);
2969: hr_utility.trace('Params are ');
2970: hr_utility.trace('p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2971: hr_utility.trace('p_hrs are '||to_char(p_hrs));
2972: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
2973: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
2974:
2975:
2976: hr_utility.set_location('Entering '||l_proc, 10);
2977: END IF;
2972: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
2973: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
2974:
2975:
2976: hr_utility.set_location('Entering '||l_proc, 10);
2977: END IF;
2978:
2979: IF ( p_tc_bb_ok_string IS NOT NULL )
2980: THEN
2981: --l_sql := l_select || p_tc_bb_ok_string || ' ) ';
2982: l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
2983:
2984: IF ( g_debug ) THEN
2985: hr_utility.trace( 'dynamic hrs sql is ');
2986: hr_utility.trace( substr(l_sql,1,250) );
2987: hr_utility.trace( substr(l_sql,251,250) );
2988: hr_utility.trace( substr(l_sql,501,250) );
2989: hr_utility.trace( substr(l_sql,751,250) );
2982: l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
2983:
2984: IF ( g_debug ) THEN
2985: hr_utility.trace( 'dynamic hrs sql is ');
2986: hr_utility.trace( substr(l_sql,1,250) );
2987: hr_utility.trace( substr(l_sql,251,250) );
2988: hr_utility.trace( substr(l_sql,501,250) );
2989: hr_utility.trace( substr(l_sql,751,250) );
2990: hr_utility.trace( substr(l_sql,1001,250) );
2983:
2984: IF ( g_debug ) THEN
2985: hr_utility.trace( 'dynamic hrs sql is ');
2986: hr_utility.trace( substr(l_sql,1,250) );
2987: hr_utility.trace( substr(l_sql,251,250) );
2988: hr_utility.trace( substr(l_sql,501,250) );
2989: hr_utility.trace( substr(l_sql,751,250) );
2990: hr_utility.trace( substr(l_sql,1001,250) );
2991: hr_utility.trace( substr(l_sql,1251,250) );
2984: IF ( g_debug ) THEN
2985: hr_utility.trace( 'dynamic hrs sql is ');
2986: hr_utility.trace( substr(l_sql,1,250) );
2987: hr_utility.trace( substr(l_sql,251,250) );
2988: hr_utility.trace( substr(l_sql,501,250) );
2989: hr_utility.trace( substr(l_sql,751,250) );
2990: hr_utility.trace( substr(l_sql,1001,250) );
2991: hr_utility.trace( substr(l_sql,1251,250) );
2992: hr_utility.trace( substr(l_sql,1501,250) );
2985: hr_utility.trace( 'dynamic hrs sql is ');
2986: hr_utility.trace( substr(l_sql,1,250) );
2987: hr_utility.trace( substr(l_sql,251,250) );
2988: hr_utility.trace( substr(l_sql,501,250) );
2989: hr_utility.trace( substr(l_sql,751,250) );
2990: hr_utility.trace( substr(l_sql,1001,250) );
2991: hr_utility.trace( substr(l_sql,1251,250) );
2992: hr_utility.trace( substr(l_sql,1501,250) );
2993: hr_utility.trace( substr(l_sql,1751,250) );
2986: hr_utility.trace( substr(l_sql,1,250) );
2987: hr_utility.trace( substr(l_sql,251,250) );
2988: hr_utility.trace( substr(l_sql,501,250) );
2989: hr_utility.trace( substr(l_sql,751,250) );
2990: hr_utility.trace( substr(l_sql,1001,250) );
2991: hr_utility.trace( substr(l_sql,1251,250) );
2992: hr_utility.trace( substr(l_sql,1501,250) );
2993: hr_utility.trace( substr(l_sql,1751,250) );
2994: END IF;
2987: hr_utility.trace( substr(l_sql,251,250) );
2988: hr_utility.trace( substr(l_sql,501,250) );
2989: hr_utility.trace( substr(l_sql,751,250) );
2990: hr_utility.trace( substr(l_sql,1001,250) );
2991: hr_utility.trace( substr(l_sql,1251,250) );
2992: hr_utility.trace( substr(l_sql,1501,250) );
2993: hr_utility.trace( substr(l_sql,1751,250) );
2994: END IF;
2995:
2988: hr_utility.trace( substr(l_sql,501,250) );
2989: hr_utility.trace( substr(l_sql,751,250) );
2990: hr_utility.trace( substr(l_sql,1001,250) );
2991: hr_utility.trace( substr(l_sql,1251,250) );
2992: hr_utility.trace( substr(l_sql,1501,250) );
2993: hr_utility.trace( substr(l_sql,1751,250) );
2994: END IF;
2995:
2996: l_csr := dbms_sql.open_cursor;
2989: hr_utility.trace( substr(l_sql,751,250) );
2990: hr_utility.trace( substr(l_sql,1001,250) );
2991: hr_utility.trace( substr(l_sql,1251,250) );
2992: hr_utility.trace( substr(l_sql,1501,250) );
2993: hr_utility.trace( substr(l_sql,1751,250) );
2994: END IF;
2995:
2996: l_csr := dbms_sql.open_cursor;
2997:
3030:
3031: END IF; -- IF ( p_tc_bb_ok_string IS NOT NULL )
3032:
3033: IF ( g_debug ) THEN
3034: hr_utility.trace('GAZ - HOURS ARE : '||to_char(NVL(l_hrs,0)));
3035: END IF;
3036:
3037: p_hrs := NVL(l_hrs,0);
3038:
3036:
3037: p_hrs := NVL(l_hrs,0);
3038:
3039: IF ( g_debug ) THEN
3040: hr_utility.set_location('Leaving '||l_proc, 170);
3041: END IF;
3042:
3043: END sum_tc_bb_ok_hrs;
3044:
3078: l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
3079:
3080: IF ( g_debug ) THEN
3081: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3082: hr_utility.trace( 'dynamic hrs sql is ');
3083: hr_utility.trace( substr(l_sql,1,250) );
3084: hr_utility.trace( substr(l_sql,251,250) );
3085: hr_utility.trace( substr(l_sql,501,250) );
3086: hr_utility.trace( substr(l_sql,751,250) );
3079:
3080: IF ( g_debug ) THEN
3081: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3082: hr_utility.trace( 'dynamic hrs sql is ');
3083: hr_utility.trace( substr(l_sql,1,250) );
3084: hr_utility.trace( substr(l_sql,251,250) );
3085: hr_utility.trace( substr(l_sql,501,250) );
3086: hr_utility.trace( substr(l_sql,751,250) );
3087: hr_utility.trace( substr(l_sql,1001,250) );
3080: IF ( g_debug ) THEN
3081: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3082: hr_utility.trace( 'dynamic hrs sql is ');
3083: hr_utility.trace( substr(l_sql,1,250) );
3084: hr_utility.trace( substr(l_sql,251,250) );
3085: hr_utility.trace( substr(l_sql,501,250) );
3086: hr_utility.trace( substr(l_sql,751,250) );
3087: hr_utility.trace( substr(l_sql,1001,250) );
3088: hr_utility.trace( substr(l_sql,1251,250) );
3081: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3082: hr_utility.trace( 'dynamic hrs sql is ');
3083: hr_utility.trace( substr(l_sql,1,250) );
3084: hr_utility.trace( substr(l_sql,251,250) );
3085: hr_utility.trace( substr(l_sql,501,250) );
3086: hr_utility.trace( substr(l_sql,751,250) );
3087: hr_utility.trace( substr(l_sql,1001,250) );
3088: hr_utility.trace( substr(l_sql,1251,250) );
3089: hr_utility.trace( substr(l_sql,1501,250) );
3082: hr_utility.trace( 'dynamic hrs sql is ');
3083: hr_utility.trace( substr(l_sql,1,250) );
3084: hr_utility.trace( substr(l_sql,251,250) );
3085: hr_utility.trace( substr(l_sql,501,250) );
3086: hr_utility.trace( substr(l_sql,751,250) );
3087: hr_utility.trace( substr(l_sql,1001,250) );
3088: hr_utility.trace( substr(l_sql,1251,250) );
3089: hr_utility.trace( substr(l_sql,1501,250) );
3090: hr_utility.trace( substr(l_sql,1751,250) );
3083: hr_utility.trace( substr(l_sql,1,250) );
3084: hr_utility.trace( substr(l_sql,251,250) );
3085: hr_utility.trace( substr(l_sql,501,250) );
3086: hr_utility.trace( substr(l_sql,751,250) );
3087: hr_utility.trace( substr(l_sql,1001,250) );
3088: hr_utility.trace( substr(l_sql,1251,250) );
3089: hr_utility.trace( substr(l_sql,1501,250) );
3090: hr_utility.trace( substr(l_sql,1751,250) );
3091: END IF;
3084: hr_utility.trace( substr(l_sql,251,250) );
3085: hr_utility.trace( substr(l_sql,501,250) );
3086: hr_utility.trace( substr(l_sql,751,250) );
3087: hr_utility.trace( substr(l_sql,1001,250) );
3088: hr_utility.trace( substr(l_sql,1251,250) );
3089: hr_utility.trace( substr(l_sql,1501,250) );
3090: hr_utility.trace( substr(l_sql,1751,250) );
3091: END IF;
3092:
3085: hr_utility.trace( substr(l_sql,501,250) );
3086: hr_utility.trace( substr(l_sql,751,250) );
3087: hr_utility.trace( substr(l_sql,1001,250) );
3088: hr_utility.trace( substr(l_sql,1251,250) );
3089: hr_utility.trace( substr(l_sql,1501,250) );
3090: hr_utility.trace( substr(l_sql,1751,250) );
3091: END IF;
3092:
3093: l_csr := dbms_sql.open_cursor;
3086: hr_utility.trace( substr(l_sql,751,250) );
3087: hr_utility.trace( substr(l_sql,1001,250) );
3088: hr_utility.trace( substr(l_sql,1251,250) );
3089: hr_utility.trace( substr(l_sql,1501,250) );
3090: hr_utility.trace( substr(l_sql,1751,250) );
3091: END IF;
3092:
3093: l_csr := dbms_sql.open_cursor;
3094:
3117:
3118: END IF; -- IF ( p_tc_bb_ok_string IS NOT NULL )
3119:
3120: IF ( g_debug ) THEN
3121: hr_utility.trace('GAZ - HOURS ARE : '||to_char(NVL(l_hrs,0)));
3122: END IF;
3123:
3124: p_hrs := NVL( l_hrs, 0 );
3125:
3123:
3124: p_hrs := NVL( l_hrs, 0 );
3125:
3126: IF ( g_debug ) THEN
3127: hr_utility.set_location('Leaving '||l_proc, 170);
3128: END IF;
3129:
3130: END sum_live_tc_bb_ok_hrs;
3131:
3171: l_sql := l_select || p_tc_bb_ok_string || ' ) ';
3172:
3173: IF ( g_debug ) THEN
3174: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3175: hr_utility.trace( 'dynamic hrs sql is ');
3176: hr_utility.trace( substr(l_sql,1,250) );
3177: hr_utility.trace( substr(l_sql,251,250) );
3178: hr_utility.trace( substr(l_sql,501,250) );
3179: hr_utility.trace( substr(l_sql,751,250) );
3172:
3173: IF ( g_debug ) THEN
3174: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3175: hr_utility.trace( 'dynamic hrs sql is ');
3176: hr_utility.trace( substr(l_sql,1,250) );
3177: hr_utility.trace( substr(l_sql,251,250) );
3178: hr_utility.trace( substr(l_sql,501,250) );
3179: hr_utility.trace( substr(l_sql,751,250) );
3180: hr_utility.trace( substr(l_sql,1001,250) );
3173: IF ( g_debug ) THEN
3174: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3175: hr_utility.trace( 'dynamic hrs sql is ');
3176: hr_utility.trace( substr(l_sql,1,250) );
3177: hr_utility.trace( substr(l_sql,251,250) );
3178: hr_utility.trace( substr(l_sql,501,250) );
3179: hr_utility.trace( substr(l_sql,751,250) );
3180: hr_utility.trace( substr(l_sql,1001,250) );
3181: hr_utility.trace( substr(l_sql,1251,250) );
3174: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3175: hr_utility.trace( 'dynamic hrs sql is ');
3176: hr_utility.trace( substr(l_sql,1,250) );
3177: hr_utility.trace( substr(l_sql,251,250) );
3178: hr_utility.trace( substr(l_sql,501,250) );
3179: hr_utility.trace( substr(l_sql,751,250) );
3180: hr_utility.trace( substr(l_sql,1001,250) );
3181: hr_utility.trace( substr(l_sql,1251,250) );
3182: hr_utility.trace( substr(l_sql,1501,250) );
3175: hr_utility.trace( 'dynamic hrs sql is ');
3176: hr_utility.trace( substr(l_sql,1,250) );
3177: hr_utility.trace( substr(l_sql,251,250) );
3178: hr_utility.trace( substr(l_sql,501,250) );
3179: hr_utility.trace( substr(l_sql,751,250) );
3180: hr_utility.trace( substr(l_sql,1001,250) );
3181: hr_utility.trace( substr(l_sql,1251,250) );
3182: hr_utility.trace( substr(l_sql,1501,250) );
3183: hr_utility.trace( substr(l_sql,1751,250) );
3176: hr_utility.trace( substr(l_sql,1,250) );
3177: hr_utility.trace( substr(l_sql,251,250) );
3178: hr_utility.trace( substr(l_sql,501,250) );
3179: hr_utility.trace( substr(l_sql,751,250) );
3180: hr_utility.trace( substr(l_sql,1001,250) );
3181: hr_utility.trace( substr(l_sql,1251,250) );
3182: hr_utility.trace( substr(l_sql,1501,250) );
3183: hr_utility.trace( substr(l_sql,1751,250) );
3184: END IF;
3177: hr_utility.trace( substr(l_sql,251,250) );
3178: hr_utility.trace( substr(l_sql,501,250) );
3179: hr_utility.trace( substr(l_sql,751,250) );
3180: hr_utility.trace( substr(l_sql,1001,250) );
3181: hr_utility.trace( substr(l_sql,1251,250) );
3182: hr_utility.trace( substr(l_sql,1501,250) );
3183: hr_utility.trace( substr(l_sql,1751,250) );
3184: END IF;
3185:
3178: hr_utility.trace( substr(l_sql,501,250) );
3179: hr_utility.trace( substr(l_sql,751,250) );
3180: hr_utility.trace( substr(l_sql,1001,250) );
3181: hr_utility.trace( substr(l_sql,1251,250) );
3182: hr_utility.trace( substr(l_sql,1501,250) );
3183: hr_utility.trace( substr(l_sql,1751,250) );
3184: END IF;
3185:
3186: execute immediate l_sql INTO l_hrs;
3179: hr_utility.trace( substr(l_sql,751,250) );
3180: hr_utility.trace( substr(l_sql,1001,250) );
3181: hr_utility.trace( substr(l_sql,1251,250) );
3182: hr_utility.trace( substr(l_sql,1501,250) );
3183: hr_utility.trace( substr(l_sql,1751,250) );
3184: END IF;
3185:
3186: execute immediate l_sql INTO l_hrs;
3187:
3191:
3192: END IF; -- IF ( p_tc_bb_ok_string IS NOT NULL )
3193:
3194: IF ( g_debug ) THEN
3195: hr_utility.trace('GAZ - HOURS ARE : '||to_char(NVL(l_hrs,0)));
3196: END IF;
3197:
3198: p_hrs := NVL( l_hrs, 0 );
3199:
3197:
3198: p_hrs := NVL( l_hrs, 0 );
3199:
3200: IF ( g_debug ) THEN
3201: hr_utility.set_location('Leaving '||l_proc, 170);
3202: END IF;
3203:
3204: END sum_live_tc_bb_ok_hrs;
3205:
3299: l_tc_ind PLS_INTEGER := 1;
3300:
3301:
3302: BEGIN -- evaluate_time_category
3303: g_debug := hr_utility.debug_enabled;
3304:
3305: g_params.p_time_category_id := p_time_category_id;
3306: g_params.p_use_tc_cache := p_use_tc_cache;
3307: g_params.p_use_tc_bb_cache := p_use_tc_bb_cache;
3313: -- gaz_debug_push_timecard;
3314:
3315: IF ( g_debug ) THEN
3316: l_proc := g_package||'evaluate_time_category';
3317: hr_utility.trace('*****************************************************');
3318: hr_utility.trace('Params are :');
3319: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3320: hr_utility.trace('scope is : '||g_params.p_scope);
3321: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3314:
3315: IF ( g_debug ) THEN
3316: l_proc := g_package||'evaluate_time_category';
3317: hr_utility.trace('*****************************************************');
3318: hr_utility.trace('Params are :');
3319: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3320: hr_utility.trace('scope is : '||g_params.p_scope);
3321: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3322: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3315: IF ( g_debug ) THEN
3316: l_proc := g_package||'evaluate_time_category';
3317: hr_utility.trace('*****************************************************');
3318: hr_utility.trace('Params are :');
3319: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3320: hr_utility.trace('scope is : '||g_params.p_scope);
3321: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3322: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3323: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3316: l_proc := g_package||'evaluate_time_category';
3317: hr_utility.trace('*****************************************************');
3318: hr_utility.trace('Params are :');
3319: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3320: hr_utility.trace('scope is : '||g_params.p_scope);
3321: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3322: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3323: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3324: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3317: hr_utility.trace('*****************************************************');
3318: hr_utility.trace('Params are :');
3319: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3320: hr_utility.trace('scope is : '||g_params.p_scope);
3321: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3322: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3323: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3324: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3325: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3318: hr_utility.trace('Params are :');
3319: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3320: hr_utility.trace('scope is : '||g_params.p_scope);
3321: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3322: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3323: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3324: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3325: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3326:
3319: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3320: hr_utility.trace('scope is : '||g_params.p_scope);
3321: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3322: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3323: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3324: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3325: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3326:
3327: IF ( g_params.p_use_tc_cache )
3320: hr_utility.trace('scope is : '||g_params.p_scope);
3321: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3322: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3323: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3324: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3325: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3326:
3327: IF ( g_params.p_use_tc_cache )
3328: THEN
3321: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3322: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3323: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3324: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3325: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3326:
3327: IF ( g_params.p_use_tc_cache )
3328: THEN
3329: hr_utility.trace('p_use_tc_cache is TRUE');
3325: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3326:
3327: IF ( g_params.p_use_tc_cache )
3328: THEN
3329: hr_utility.trace('p_use_tc_cache is TRUE');
3330: ELSE
3331: hr_utility.trace('p_use_tc_cache is FALSE');
3332: END IF;
3333:
3327: IF ( g_params.p_use_tc_cache )
3328: THEN
3329: hr_utility.trace('p_use_tc_cache is TRUE');
3330: ELSE
3331: hr_utility.trace('p_use_tc_cache is FALSE');
3332: END IF;
3333:
3334: IF ( g_params.p_use_tc_bb_cache )
3335: THEN
3332: END IF;
3333:
3334: IF ( g_params.p_use_tc_bb_cache )
3335: THEN
3336: hr_utility.trace('p_use_tc_bb_cache is TRUE');
3337: ELSE
3338: hr_utility.trace('p_use_tc_bb_cache is FALSE');
3339: END IF;
3340: IF ( g_params.p_use_temp_table )
3334: IF ( g_params.p_use_tc_bb_cache )
3335: THEN
3336: hr_utility.trace('p_use_tc_bb_cache is TRUE');
3337: ELSE
3338: hr_utility.trace('p_use_tc_bb_cache is FALSE');
3339: END IF;
3340: IF ( g_params.p_use_temp_table )
3341: THEN
3342: hr_utility.trace('p_use_temp_table is TRUE');
3338: hr_utility.trace('p_use_tc_bb_cache is FALSE');
3339: END IF;
3340: IF ( g_params.p_use_temp_table )
3341: THEN
3342: hr_utility.trace('p_use_temp_table is TRUE');
3343: ELSE
3344: hr_utility.trace('p_use_temp_table is FALSE');
3345: END IF;
3346:
3340: IF ( g_params.p_use_temp_table )
3341: THEN
3342: hr_utility.trace('p_use_temp_table is TRUE');
3343: ELSE
3344: hr_utility.trace('p_use_temp_table is FALSE');
3345: END IF;
3346:
3347: hr_utility.trace('tc bb ok string '||p_tc_bb_ok_string);
3348:
3343: ELSE
3344: hr_utility.trace('p_use_temp_table is FALSE');
3345: END IF;
3346:
3347: hr_utility.trace('tc bb ok string '||p_tc_bb_ok_string);
3348:
3349: END IF; -- l debug
3350:
3351:
3354: IF ( p_use_tc_cache AND tc_cache_exists ( p_time_category_id ) )
3355: THEN
3356:
3357: IF ( g_debug ) THEN
3358: hr_utility.trace('Using time category cache');
3359: END IF;
3360:
3361: l_tc_cache_exists := TRUE;
3362:
3365:
3366: ELSE
3367:
3368: IF ( g_debug ) THEN
3369: hr_utility.trace('NOT using time category cache');
3370: END IF;
3371:
3372: -- get time category TIME_SQL and OPERATOR
3373:
3378: THEN
3379: -- empty time category
3380:
3381: IF ( g_debug ) THEN
3382: hr_utility.trace('Time Category EMPTY anyway !!!');
3383: END IF;
3384:
3385: l_empty_time_category := TRUE;
3386: /* bug fix for 5076837 */
3411: IF ( hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id IS NULL )
3412: THEN
3413:
3414: IF ( g_debug ) THEN
3415: hr_utility.trace('Master Time Category !!!!!!');
3416: END IF;
3417:
3418: -- set master TC info
3419:
3429: IF ( g_tc_bb_ok_cache.exists( p_time_category_id ) AND p_use_tc_bb_cache )
3430: THEN
3431:
3432: IF ( g_debug ) THEN
3433: hr_utility.trace('tc bb ok cache exists');
3434: END IF;
3435:
3436: IF ( ( g_tc_bb_ok_cache(p_time_category_id).timecard_id =
3437: hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id ) AND
3439: hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count ) )
3440: THEN
3441:
3442: IF ( g_debug ) THEN
3443: hr_utility.trace('Using tc bb ok cache');
3444: END IF;
3445:
3446: l_tc_bb_cache_exists := TRUE;
3447:
3448: ELSE
3449: -- different timecard therefore cannot use cache
3450:
3451: IF ( g_debug ) THEN
3452: hr_utility.trace('NOT using tc bb ok cache - diff TC ID');
3453: END IF;
3454:
3455: l_tc_bb_cache_exists := FALSE;
3456: g_tc_bb_ok_cache(p_time_category_id).bb_ok_string := NULL;
3459:
3460: ELSE
3461:
3462: IF ( g_debug ) THEN
3463: hr_utility.trace('tc bb ok cache NOT EXISTS');
3464: END IF;
3465:
3466: l_tc_bb_cache_exists := FALSE;
3467:
3480:
3481: IF ( p_use_temp_table )
3482: THEN
3483: IF ( g_debug ) THEN
3484: hr_utility.trace('Using temporary table to generate not ok bbs');
3485: END IF;
3486:
3487: IF ( hxc_time_category_utils_pkg.g_tc_bb_not_ok_string IS NULL )
3488: THEN
3511:
3512: END LOOP;
3513:
3514: IF ( g_debug ) THEN
3515: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3516: END IF;
3517:
3518: ELSE -- hxc_time_category_utils_pkg.g_tc_bb_not_ok_string IS NOT NULL
3519:
3519:
3520: p_tc_bb_not_ok_string := hxc_time_category_utils_pkg.g_tc_bb_not_ok_string;
3521:
3522: IF ( g_debug ) THEN
3523: hr_utility.trace('Using cache : bb not ok string is '||p_tc_bb_not_ok_string);
3524: END IF;
3525:
3526: END IF; -- hxc_time_category_utils_pkg.g_tc_bb_not_ok_string IS NULL
3527:
3527:
3528: ELSE -- using live table
3529:
3530: IF ( g_debug ) THEN
3531: hr_utility.trace('Using the live table');
3532: END IF;
3533:
3534: IF ( p_scope = 'TIME' )
3535: THEN
3534: IF ( p_scope = 'TIME' )
3535: THEN
3536:
3537: IF ( g_debug ) THEN
3538: hr_utility.trace('Scope is TIME');
3539: END IF;
3540:
3541: -- populate p tc bb not ok string from timecard
3542:
3554:
3555: END IF;
3556:
3557: IF ( g_debug ) THEN
3558: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3559: END IF;
3560:
3561: END IF;
3562:
3574: ELSIF ( p_scope = 'DETAIL' )
3575: THEN
3576:
3577: IF ( g_debug ) THEN
3578: hr_utility.trace('Scope is DETAIL');
3579: END IF;
3580:
3581: p_tc_bb_not_ok_string := g_params.p_tbb_id;
3582:
3580:
3581: p_tc_bb_not_ok_string := g_params.p_tbb_id;
3582:
3583: IF ( g_debug ) THEN
3584: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3585: END IF;
3586:
3587: END IF;
3588:
3606: IF ( NOT l_tc_bb_cache_exists OR NOT p_use_tc_bb_cache )
3607: THEN
3608:
3609: IF ( g_debug ) THEN
3610: hr_utility.trace('NOT using tc bb cache');
3611: END IF;
3612:
3613: -- get time category components
3614:
3628:
3629: ELSE
3630:
3631: IF ( g_debug ) THEN
3632: hr_utility.trace('about to get tc comps');
3633: END IF;
3634:
3635: OPEN csr_get_category_comps( p_time_category_id );
3636: FETCH csr_get_category_comps INTO l_time_category_comps;
3686:
3687: END LOOP;
3688:
3689: IF ( g_debug ) THEN
3690: hr_utility.trace('Category Component Table Counts are .......');
3691: hr_utility.trace('MC_VS count is '||to_char(l_vs_comp_tab.count));
3692: hr_utility.trace('AN count is '||to_char(l_an_comp_tab.count));
3693: hr_utility.trace('TC count is '||to_char(l_tc_comp_tab.count));
3694: END IF;
3687: END LOOP;
3688:
3689: IF ( g_debug ) THEN
3690: hr_utility.trace('Category Component Table Counts are .......');
3691: hr_utility.trace('MC_VS count is '||to_char(l_vs_comp_tab.count));
3692: hr_utility.trace('AN count is '||to_char(l_an_comp_tab.count));
3693: hr_utility.trace('TC count is '||to_char(l_tc_comp_tab.count));
3694: END IF;
3695:
3688:
3689: IF ( g_debug ) THEN
3690: hr_utility.trace('Category Component Table Counts are .......');
3691: hr_utility.trace('MC_VS count is '||to_char(l_vs_comp_tab.count));
3692: hr_utility.trace('AN count is '||to_char(l_an_comp_tab.count));
3693: hr_utility.trace('TC count is '||to_char(l_tc_comp_tab.count));
3694: END IF;
3695:
3696: -- maintain TC CACHE
3689: IF ( g_debug ) THEN
3690: hr_utility.trace('Category Component Table Counts are .......');
3691: hr_utility.trace('MC_VS count is '||to_char(l_vs_comp_tab.count));
3692: hr_utility.trace('AN count is '||to_char(l_an_comp_tab.count));
3693: hr_utility.trace('TC count is '||to_char(l_tc_comp_tab.count));
3694: END IF;
3695:
3696: -- maintain TC CACHE
3697:
3719:
3720: -- TYPE = MC
3721:
3722: IF ( g_debug ) THEN
3723: hr_utility.trace('Evaluating MC');
3724: END IF;
3725:
3726: evaluate_time_sql ( l_time_category_info.time_sql
3727: , NULL
3754: -- if so - then call value_set_string and
3755: -- maintain tccs again
3756:
3757: IF ( g_debug ) THEN
3758: hr_utility.trace('Evaluating MC_VS Loop');
3759: END IF;
3760:
3761: evaluate_time_sql ( NULL
3762: , l_vs_comp_tab(l_vs_ind).sql_string
3788: WHILE ( l_an_ind IS NOT NULL AND l_continue_evaluation )
3789: LOOP
3790:
3791: IF ( g_debug ) THEN
3792: hr_utility.trace('Evaluating AN Loop');
3793: END IF;
3794:
3795: evaluate_time_sql ( NULL
3796: , l_an_comp_tab(l_an_ind).sql_string
3890:
3891: /*
3892:
3893: IF ( g_debug ) THEN
3894: hr_utility.trace('Setting tc bb ok cache for master tc !!!! '||to_char(p_time_category_id));
3895: END IF;
3896:
3897: g_tc_bb_ok_cache(p_time_category_id).bb_ok_string := p_tc_bb_ok_string;
3898:
3912:
3913: -- using the cache
3914:
3915: IF ( g_debug ) THEN
3916: hr_utility.trace('Using bb outcome cache');
3917: END IF;
3918:
3919: hxc_time_category_utils_pkg.g_tc_in_bb_ok := p_time_category_id;
3920:
3929:
3930: END IF; -- IF ( NOT l_empty_time_category )
3931:
3932: IF ( g_debug ) THEN
3933: hr_utility.trace('***************************************************');
3934: hr_utility.trace('Return values from evaluate time category for : '||to_char(p_time_category_id));
3935: hr_utility.trace('bb ok string is '||p_tc_bb_ok_string);
3936: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3937: hr_utility.trace('bb ok tab is ....');
3930: END IF; -- IF ( NOT l_empty_time_category )
3931:
3932: IF ( g_debug ) THEN
3933: hr_utility.trace('***************************************************');
3934: hr_utility.trace('Return values from evaluate time category for : '||to_char(p_time_category_id));
3935: hr_utility.trace('bb ok string is '||p_tc_bb_ok_string);
3936: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3937: hr_utility.trace('bb ok tab is ....');
3938:
3931:
3932: IF ( g_debug ) THEN
3933: hr_utility.trace('***************************************************');
3934: hr_utility.trace('Return values from evaluate time category for : '||to_char(p_time_category_id));
3935: hr_utility.trace('bb ok string is '||p_tc_bb_ok_string);
3936: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3937: hr_utility.trace('bb ok tab is ....');
3938:
3939: l_tc_ind := p_tc_bb_ok_tab.FIRST;
3932: IF ( g_debug ) THEN
3933: hr_utility.trace('***************************************************');
3934: hr_utility.trace('Return values from evaluate time category for : '||to_char(p_time_category_id));
3935: hr_utility.trace('bb ok string is '||p_tc_bb_ok_string);
3936: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3937: hr_utility.trace('bb ok tab is ....');
3938:
3939: l_tc_ind := p_tc_bb_ok_tab.FIRST;
3940:
3933: hr_utility.trace('***************************************************');
3934: hr_utility.trace('Return values from evaluate time category for : '||to_char(p_time_category_id));
3935: hr_utility.trace('bb ok string is '||p_tc_bb_ok_string);
3936: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3937: hr_utility.trace('bb ok tab is ....');
3938:
3939: l_tc_ind := p_tc_bb_ok_tab.FIRST;
3940:
3941: WHILE l_tc_ind IS NOT NULL
3940:
3941: WHILE l_tc_ind IS NOT NULL
3942: LOOP
3943:
3944: hr_utility.trace('bb id is : '||to_char(l_tc_ind));
3945:
3946: l_tc_ind := p_tc_bb_ok_tab.NEXT(l_tc_ind);
3947:
3948: END LOOP;
3945:
3946: l_tc_ind := p_tc_bb_ok_tab.NEXT(l_tc_ind);
3947:
3948: END LOOP;
3949: hr_utility.trace('***************************************************');
3950:
3951: END IF; -- l debug
3952:
3953: -- Reset Variables for the Master TC
3965:
3966: exception when others then
3967:
3968: IF ( g_debug ) THEN
3969: hr_utility.trace('In exception error is '||SQLERRM);
3970: END IF;
3971: raise;
3972:
3973: END evaluate_time_category;
3987: l_proc VARCHAR2(72);
3988:
3989: BEGIN
3990:
3991: g_debug := hr_utility.debug_enabled;
3992:
3993: IF ( g_debug ) THEN
3994: l_proc := g_package||'chk_tc_bb_ok';
3995: hr_utility.set_location('Entering '||l_proc, 10);
3991: g_debug := hr_utility.debug_enabled;
3992:
3993: IF ( g_debug ) THEN
3994: l_proc := g_package||'chk_tc_bb_ok';
3995: hr_utility.set_location('Entering '||l_proc, 10);
3996: END IF;
3997:
3998: IF ( ( g_tc_in_bb_ok <> hxc_time_category_utils_pkg.g_time_category_id ) OR
3999: ( g_tc_in_bb_ok IS NULL ) )
4030: l_operator VARCHAR2(3);
4031:
4032: BEGIN
4033:
4034: g_debug := hr_utility.debug_enabled;
4035:
4036: IF ( g_debug ) THEN
4037: l_proc := g_package||'insert_time_category_comp_sql';
4038: hr_utility.set_location('Entering '||l_proc, 10);
4034: g_debug := hr_utility.debug_enabled;
4035:
4036: IF ( g_debug ) THEN
4037: l_proc := g_package||'insert_time_category_comp_sql';
4038: hr_utility.set_location('Entering '||l_proc, 10);
4039:
4040: hr_utility.trace('Inserting tcc SQL for type '||p_rec.type);
4041: END IF;
4042:
4036: IF ( g_debug ) THEN
4037: l_proc := g_package||'insert_time_category_comp_sql';
4038: hr_utility.set_location('Entering '||l_proc, 10);
4039:
4040: hr_utility.trace('Inserting tcc SQL for type '||p_rec.type);
4041: END IF;
4042:
4043: OPEN csr_get_operator ( p_rec.time_category_id );
4044: FETCH csr_get_operator INTO l_operator;
4079: , p_rec.time_category_comp_id
4080: , l_sql );
4081:
4082: IF ( g_debug ) THEN
4083: hr_utility.set_location('Entering '||l_proc, 20);
4084: END IF;
4085:
4086: END insert_time_category_comp_sql;
4087:
4096: l_tcc_sql csr_chk_tcc_sql_exists%ROWTYPE;
4097:
4098: BEGIN
4099:
4100: g_debug := hr_utility.debug_enabled;
4101:
4102: IF ( g_debug ) THEN
4103: l_proc := g_package||'update_time_category_comp_sql';
4104: hr_utility.trace('Updating tcc SQL for type '||p_rec.type);
4100: g_debug := hr_utility.debug_enabled;
4101:
4102: IF ( g_debug ) THEN
4103: l_proc := g_package||'update_time_category_comp_sql';
4104: hr_utility.trace('Updating tcc SQL for type '||p_rec.type);
4105: END IF;
4106:
4107: -- First check to see row exists
4108: -- user may have changed the TYPE of tcc record
4118:
4119: -- row exists and TCC row still of correct type
4120:
4121: IF ( g_debug ) THEN
4122: hr_utility.trace('is nul is '||p_rec.is_null);
4123: hr_utility.trace('equal to is '||p_Rec.equal_to);
4124: END IF;
4125:
4126: OPEN csr_get_operator ( p_rec.time_category_id );
4119: -- row exists and TCC row still of correct type
4120:
4121: IF ( g_debug ) THEN
4122: hr_utility.trace('is nul is '||p_rec.is_null);
4123: hr_utility.trace('equal to is '||p_Rec.equal_to);
4124: END IF;
4125:
4126: OPEN csr_get_operator ( p_rec.time_category_id );
4127: FETCH csr_get_operator INTO l_operator;
4189: l_proc VARCHAR2(72);
4190:
4191: BEGIN
4192:
4193: g_debug := hr_utility.debug_enabled;
4194:
4195: IF ( g_debug ) THEN
4196: l_proc := g_package||'delete_time_category_comp_sql';
4197: hr_utility.trace('Deleting tcc SQL for type '||p_rec.type);
4193: g_debug := hr_utility.debug_enabled;
4194:
4195: IF ( g_debug ) THEN
4196: l_proc := g_package||'delete_time_category_comp_sql';
4197: hr_utility.trace('Deleting tcc SQL for type '||p_rec.type);
4198: END IF;
4199:
4200: -- First check to see row exists
4201: -- user may have changed the TYPE of tcc record
4243: l_dep_parent_column_name fnd_columns.column_name%TYPE;
4244: --
4245: begin -- get_value_set_sql
4246:
4247: g_debug := hr_utility.debug_enabled;
4248:
4249: IF ( g_debug ) THEN
4250: l_proc := g_package||'get_value_set_sql';
4251: hr_utility.set_location('Entering:'|| l_proc, 5);
4247: g_debug := hr_utility.debug_enabled;
4248:
4249: IF ( g_debug ) THEN
4250: l_proc := g_package||'get_value_set_sql';
4251: hr_utility.set_location('Entering:'|| l_proc, 5);
4252: END IF;
4253:
4254:
4255: l_value_set_id := p_flex_value_set_id;
4262: l_sql_text := '';
4263: l_sql_text_id := '';
4264: --
4265: IF ( g_debug ) THEN
4266: hr_utility.set_location(l_proc, 10);
4267: END IF;
4268:
4269: -- Ok next build the SQL text that can be used to build a pop-list
4270: -- for this segment, if this is a table validated or independant
4278: THEN
4279: -- TABLE validated
4280:
4281: IF ( g_debug ) THEN
4282: hr_utility.set_location(l_proc, 20);
4283: END IF;
4284:
4285: select 'SELECT ' ||
4286: l_valueset_r.table_info.value_column_name ||
4294: into l_sql_text
4295: from dual;
4296:
4297: IF ( g_debug ) THEN
4298: hr_utility.set_location(l_proc, 30);
4299: END IF;
4300:
4301: l_order_by_start := INSTR(upper(l_sql_text),'ORDER BY');
4302: l_from_start := INSTR(upper(l_sql_text),'FROM');
4301: l_order_by_start := INSTR(upper(l_sql_text),'ORDER BY');
4302: l_from_start := INSTR(upper(l_sql_text),'FROM');
4303:
4304: IF ( g_debug ) THEN
4305: hr_utility.set_location(l_proc, 60);
4306: END IF;
4307:
4308: -- Build the SQL for the FROM clause
4309:
4314: l_from_where := substr(l_sql_text,l_from_start);
4315: end if;
4316: --
4317: IF ( g_debug ) THEN
4318: hr_utility.set_location(l_proc, 90);
4319: END IF;
4320: --
4321: if(l_valueset_r.table_info.meaning_column_name is not null) then
4322: l_select_clause := 'SELECT '||l_valueset_r.table_info.
4342:
4343: elsif l_valueset_r.validation_type = 'I' then
4344:
4345: IF ( g_debug ) THEN
4346: hr_utility.set_location(l_proc, 120);
4347: END IF;
4348: --
4349: -- We can hard code the DESC SQL this time, since we know explicitly
4350: -- how independant value sets are built. This should be changed once
4369:
4370: end if; -- validation type
4371:
4372: IF ( g_debug ) THEN
4373: hr_utility.set_location(' Leaving:'||l_proc, 150);
4374: END IF;
4375:
4376: RETURN l_sql_text_id;
4377:
4410:
4411:
4412: IF ( g_debug ) THEN
4413: l_proc := g_package||'parse_sql';
4414: hr_utility.set_location('Processing:'||l_proc, 5);
4415: END IF;
4416:
4417: l_sql_text :=
4418: REPLACE(UPPER(
4420: SUBSTR(p_sql ,INSTR(p_sql,',',1,1)+1, ( (INSTR(p_sql,',',1,2)) - (INSTR(p_sql,',',1,1)+1) ))||' B,TO_CHAR('||
4421: SUBSTR(p_sql ,INSTR(p_sql,',',1,2)+1)), 'FROM', ') C FROM');
4422:
4423: IF ( g_debug ) THEN
4424: hr_utility.set_location('Processing:'||l_proc, 10);
4425: END IF;
4426:
4427: RETURN l_sql_text;
4428:
4429: END parse_sql;
4430:
4431: BEGIN -- get_flex_info
4432:
4433: g_debug := hr_utility.debug_enabled;
4434:
4435: IF ( g_debug ) THEN
4436: l_proc := g_package||'get_flex_info';
4437: hr_utility.set_location('Processing:'||l_proc, 5);
4433: g_debug := hr_utility.debug_enabled;
4434:
4435: IF ( g_debug ) THEN
4436: l_proc := g_package||'get_flex_info';
4437: hr_utility.set_location('Processing:'||l_proc, 5);
4438: END IF;
4439:
4440: hr_flexfield_info.initialize;
4441:
4439:
4440: hr_flexfield_info.initialize;
4441:
4442: IF ( g_debug ) THEN
4443: hr_utility.set_location('Processing:'||l_proc, 10);
4444: END IF;
4445:
4446: hr_flexfield_info.get_segments (
4447: p_appl_short_name => 'HXC'
4451: , p_segments => r_segments_t
4452: , p_session_date => p_session_date );
4453:
4454: IF ( g_debug ) THEN
4455: hr_utility.set_location('Processing:'||l_proc, 20);
4456: END IF;
4457:
4458: -- reduce r_segments_t to l_r_seg_info_t
4459:
4459:
4460: FOR x IN r_segments_t.sequence.FIRST .. r_segments_t.sequence.LAST
4461: LOOP
4462: IF ( g_debug ) THEN
4463: hr_utility.set_location('Processing:'||l_proc, 30);
4464: END IF;
4465:
4466: l_t_seg_info(x).application_column_name := r_segments_t.application_column_name(x);
4467: l_t_seg_info(x).segment_name := r_segments_t.segment_name(x);
4476: -- if value set not table or list or SQL has any $FLEX$ or $PROFILE$ references
4477: -- then we cannot use this to set the record group in the form
4478:
4479: IF ( g_debug ) THEN
4480: hr_utility.trace('SQL is '||l_t_seg_info(x).sql_text);
4481: END IF;
4482:
4483: IF ( l_t_seg_info(x).validation_type = 'NONE' OR l_t_seg_info(x).value_set IS NULL )
4484: THEN
4482:
4483: IF ( l_t_seg_info(x).validation_type = 'NONE' OR l_t_seg_info(x).value_set IS NULL )
4484: THEN
4485: IF ( g_debug ) THEN
4486: hr_utility.trace('validation type NONE');
4487: END IF;
4488:
4489: l_t_seg_info(x).sql_ok := FALSE;
4490: l_t_seg_info(x).no_sql := TRUE;
4491:
4492: ELSIF ( l_t_seg_info(x).validation_type = 'INDEPENDENT')
4493: THEN
4494: IF ( g_debug ) THEN
4495: hr_utility.trace('validation type INDEPENDENT');
4496: END IF;
4497:
4498: l_t_seg_info(x).sql_text := parse_sql ( l_t_seg_info(x).sql_text );
4499: l_t_seg_info(x).sql_ok := TRUE;
4498: l_t_seg_info(x).sql_text := parse_sql ( l_t_seg_info(x).sql_text );
4499: l_t_seg_info(x).sql_ok := TRUE;
4500: ELSE
4501: IF ( g_debug ) THEN
4502: hr_utility.trace('validation type '||l_t_seg_info(x).validation_type);
4503: END IF;
4504:
4505: l_t_seg_info(x).sql_text := parse_sql ( l_t_seg_info(x).sql_text );
4506:
4521:
4522: END LOOP;
4523:
4524: IF ( g_debug ) THEN
4525: hr_utility.set_location('Processing:'||l_proc, 40);
4526: END IF;
4527:
4528: p_seg_info := l_t_seg_info;
4529:
4555: l_csr INTEGER;
4556:
4557: BEGIN
4558:
4559: g_debug := hr_utility.debug_enabled;
4560:
4561: IF ( p_flex_value_set_id = -1 )
4562: THEN
4563:
4576:
4577: ELSE
4578:
4579: IF ( g_debug ) THEN
4580: hr_utility.trace('gaz - before');
4581: END IF;
4582:
4583: l_sql := get_value_set_sql (
4584: p_flex_value_set_id => p_flex_value_set_id
4584: p_flex_value_set_id => p_flex_value_set_id
4585: , p_session_date => sysdate );
4586:
4587: IF ( g_debug ) THEN
4588: hr_utility.trace('gaz - before');
4589: hr_utility.trace('gaz - l sql is '||l_sql);
4590: hr_utility.trace('gaz - p_id is '||p_id);
4591: END IF;
4592:
4585: , p_session_date => sysdate );
4586:
4587: IF ( g_debug ) THEN
4588: hr_utility.trace('gaz - before');
4589: hr_utility.trace('gaz - l sql is '||l_sql);
4590: hr_utility.trace('gaz - p_id is '||p_id);
4591: END IF;
4592:
4593: BEGIN
4586:
4587: IF ( g_debug ) THEN
4588: hr_utility.trace('gaz - before');
4589: hr_utility.trace('gaz - l sql is '||l_sql);
4590: hr_utility.trace('gaz - p_id is '||p_id);
4591: END IF;
4592:
4593: BEGIN
4594:
4656:
4657:
4658: BEGIN
4659:
4660: g_debug := hr_utility.debug_enabled;
4661:
4662: g_tc_bb_ok_tab.delete;
4663:
4664:
4677:
4678:
4679: IF ( g_debug ) THEN
4680: l_proc := g_package||'initialise_time_category';
4681: hr_utility.set_location('Entering '||l_proc, 10);
4682: hr_utility.trace('Initialise_time_category for time_category_id :'||p_time_category_id);
4683: hr_utility.trace('Assigned NULL to the master tc id record ');
4684: END IF;
4685:
4678:
4679: IF ( g_debug ) THEN
4680: l_proc := g_package||'initialise_time_category';
4681: hr_utility.set_location('Entering '||l_proc, 10);
4682: hr_utility.trace('Initialise_time_category for time_category_id :'||p_time_category_id);
4683: hr_utility.trace('Assigned NULL to the master tc id record ');
4684: END IF;
4685:
4686: hxc_time_category_utils_pkg.evaluate_time_category (
4679: IF ( g_debug ) THEN
4680: l_proc := g_package||'initialise_time_category';
4681: hr_utility.set_location('Entering '||l_proc, 10);
4682: hr_utility.trace('Initialise_time_category for time_category_id :'||p_time_category_id);
4683: hr_utility.trace('Assigned NULL to the master tc id record ');
4684: END IF;
4685:
4686: hxc_time_category_utils_pkg.evaluate_time_category (
4687: p_time_category_id => p_time_category_id
4689: , p_tc_bb_ok_string => l_tc_bb_ok_string
4690: , p_tc_bb_not_ok_string => l_tc_bb_not_ok_string );
4691:
4692: IF ( g_debug ) THEN
4693: hr_utility.set_location('Leaving '||l_proc, 20);
4694: END IF;
4695:
4696: END initialise_time_category;
4697:
4754: l_proc varchar2(72);
4755:
4756: BEGIN
4757:
4758: g_debug := hr_utility.debug_enabled;
4759:
4760: IF ( g_debug ) THEN
4761: l_proc := g_package||'category_timecard_hrs';
4762: hr_utility.set_location('Entering '||l_proc, 10);
4758: g_debug := hr_utility.debug_enabled;
4759:
4760: IF ( g_debug ) THEN
4761: l_proc := g_package||'category_timecard_hrs';
4762: hr_utility.set_location('Entering '||l_proc, 10);
4763:
4764: hr_utility.trace('gaz - time cat id is '||p_time_category_name);
4765: END IF;
4766:
4760: IF ( g_debug ) THEN
4761: l_proc := g_package||'category_timecard_hrs';
4762: hr_utility.set_location('Entering '||l_proc, 10);
4763:
4764: hr_utility.trace('gaz - time cat id is '||p_time_category_name);
4765: END IF;
4766:
4767: IF ( p_time_category_name is not null )
4768: THEN
4769:
4770: l_time_category_id := get_time_category_id ( p_time_category_name => p_time_category_name );
4771:
4772: IF ( g_debug ) THEN
4773: hr_utility.trace('gaz - time cat id is '||to_char(l_time_category_id));
4774: hr_utility.trace('gaz - time bb id is '||to_char(p_tbb_id));
4775: hr_utility.trace('gaz - time ovn id is '||to_char(p_tbb_ovn));
4776:
4777: hr_utility.set_location('Processing '||l_proc, 20);
4770: l_time_category_id := get_time_category_id ( p_time_category_name => p_time_category_name );
4771:
4772: IF ( g_debug ) THEN
4773: hr_utility.trace('gaz - time cat id is '||to_char(l_time_category_id));
4774: hr_utility.trace('gaz - time bb id is '||to_char(p_tbb_id));
4775: hr_utility.trace('gaz - time ovn id is '||to_char(p_tbb_ovn));
4776:
4777: hr_utility.set_location('Processing '||l_proc, 20);
4778: END IF;
4771:
4772: IF ( g_debug ) THEN
4773: hr_utility.trace('gaz - time cat id is '||to_char(l_time_category_id));
4774: hr_utility.trace('gaz - time bb id is '||to_char(p_tbb_id));
4775: hr_utility.trace('gaz - time ovn id is '||to_char(p_tbb_ovn));
4776:
4777: hr_utility.set_location('Processing '||l_proc, 20);
4778: END IF;
4779:
4773: hr_utility.trace('gaz - time cat id is '||to_char(l_time_category_id));
4774: hr_utility.trace('gaz - time bb id is '||to_char(p_tbb_id));
4775: hr_utility.trace('gaz - time ovn id is '||to_char(p_tbb_ovn));
4776:
4777: hr_utility.set_location('Processing '||l_proc, 20);
4778: END IF;
4779:
4780: -- call evaluate time category with p_scope = 'DETAIL'
4781:
4800:
4801: END IF;
4802:
4803: IF ( g_debug ) THEN
4804: hr_utility.trace('l timecard hrs are '||to_char(NVL( l_timecard_hrs, 0 ) ));
4805: hr_utility.set_location('Leaving '||l_proc, 30);
4806: END IF;
4807:
4808: RETURN NVL(l_timecard_hrs, 0);
4801: END IF;
4802:
4803: IF ( g_debug ) THEN
4804: hr_utility.trace('l timecard hrs are '||to_char(NVL( l_timecard_hrs, 0 ) ));
4805: hr_utility.set_location('Leaving '||l_proc, 30);
4806: END IF;
4807:
4808: RETURN NVL(l_timecard_hrs, 0);
4809:
4881: l_proc varchar2(72);
4882:
4883: BEGIN
4884:
4885: g_debug := hr_utility.debug_enabled;
4886:
4887: open c_tc_resource_id(p_tbb_id, p_tbb_ovn);
4888: fetch c_tc_resource_id into l_resource_id;
4889: close c_tc_resource_id;
5060: tbb.object_version_number = p_tbb_ovn;
5061:
5062:
5063: BEGIN
5064: g_debug := hr_utility.debug_enabled;
5065:
5066: IF ( g_debug ) THEN
5067: l_proc := g_package||'category_detail_hrs';
5068: hr_utility.set_location('Entering '||l_proc, 10);
5064: g_debug := hr_utility.debug_enabled;
5065:
5066: IF ( g_debug ) THEN
5067: l_proc := g_package||'category_detail_hrs';
5068: hr_utility.set_location('Entering '||l_proc, 10);
5069: END IF;
5070:
5071: IF ( p_time_category_id IS NULL )
5072: THEN
5070:
5071: IF ( p_time_category_id IS NULL )
5072: THEN
5073: IF ( g_debug ) THEN
5074: hr_utility.set_location('Processing '||l_proc, 20);
5075: END IF;
5076:
5077: OPEN csr_calc_all;
5078: FETCH csr_calc_all INTO l_hrs;
5080:
5081: ELSE
5082:
5083: IF ( g_debug ) THEN
5084: hr_utility.set_location('Processing '||l_proc, 30);
5085: END IF;
5086:
5087: -- call evaluate time category with p_scope = 'DETAIL'
5088:
5102:
5103: END IF;
5104:
5105: IF ( g_debug ) THEN
5106: hr_utility.set_location('Leaving '||l_proc, 60);
5107: END IF;
5108:
5109: RETURN NVL(l_hrs,0);
5110:
5143: AND application_period_id = p_application_period_id;
5144:
5145: BEGIN
5146:
5147: g_debug := hr_utility.debug_enabled;
5148:
5149: IF ( g_debug ) THEN
5150: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5151: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5146:
5147: g_debug := hr_utility.debug_enabled;
5148:
5149: IF ( g_debug ) THEN
5150: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5151: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5152: hr_utility.trace('resource id is '||to_char(p_resource_id));
5153: hr_utility.trace('app period id is '||to_char(p_application_period_id));
5154: hr_utility.trace('time category name is '||p_time_category_name);
5147: g_debug := hr_utility.debug_enabled;
5148:
5149: IF ( g_debug ) THEN
5150: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5151: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5152: hr_utility.trace('resource id is '||to_char(p_resource_id));
5153: hr_utility.trace('app period id is '||to_char(p_application_period_id));
5154: hr_utility.trace('time category name is '||p_time_category_name);
5155: END IF;
5148:
5149: IF ( g_debug ) THEN
5150: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5151: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5152: hr_utility.trace('resource id is '||to_char(p_resource_id));
5153: hr_utility.trace('app period id is '||to_char(p_application_period_id));
5154: hr_utility.trace('time category name is '||p_time_category_name);
5155: END IF;
5156:
5149: IF ( g_debug ) THEN
5150: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5151: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5152: hr_utility.trace('resource id is '||to_char(p_resource_id));
5153: hr_utility.trace('app period id is '||to_char(p_application_period_id));
5154: hr_utility.trace('time category name is '||p_time_category_name);
5155: END IF;
5156:
5157: l_precision := hxc_preference_evaluation.resource_preferences
5150: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5151: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5152: hr_utility.trace('resource id is '||to_char(p_resource_id));
5153: hr_utility.trace('app period id is '||to_char(p_application_period_id));
5154: hr_utility.trace('time category name is '||p_time_category_name);
5155: END IF;
5156:
5157: l_precision := hxc_preference_evaluation.resource_preferences
5158: (p_resource_id,
5419: l_rec hxc_tcc_shd.g_rec_type;
5420:
5421: BEGIN
5422:
5423: g_debug := hr_utility.debug_enabled;
5424:
5425: IF ( g_debug ) THEN
5426: hr_utility.trace('In ref chk');
5427: END IF;
5422:
5423: g_debug := hr_utility.debug_enabled;
5424:
5425: IF ( g_debug ) THEN
5426: hr_utility.trace('In ref chk');
5427: END IF;
5428:
5429: FOR tc IN csr_get_time_category_comps
5430: LOOP
5440: l_rec.type := tc.type;
5441: l_rec.object_version_number := tc.object_version_number;
5442:
5443: IF ( g_debug ) THEN
5444: hr_utility.trace('about to call update');
5445: END IF;
5446:
5447: update_time_category_comp_sql ( l_rec );
5448: