[Home] [Help]
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:
1103:
1104: IF ( l_return )
1105: THEN
1106: IF ( g_debug ) THEN
1107: hr_utility.trace('Continue evaluation is TRUE');
1108: END IF;
1109: ELSE
1110: IF ( g_debug ) THEN
1111: hr_utility.trace('Continue evaluation is FALSE');
1107: hr_utility.trace('Continue evaluation is TRUE');
1108: END IF;
1109: ELSE
1110: IF ( g_debug ) THEN
1111: hr_utility.trace('Continue evaluation is FALSE');
1112: END IF;
1113: END IF;
1114:
1115: RETURN l_return;
1138:
1139:
1140: IF ( g_debug ) THEN
1141: l_proc := g_package||'get_dyn_sql';
1142: hr_utility.trace('get dyn sql params are ....');
1143: hr_utility.trace('dyn sql is '||p_time_sql);
1144: END IF;
1145:
1146: -- we want the dynamic sql string
1139:
1140: IF ( g_debug ) THEN
1141: l_proc := g_package||'get_dyn_sql';
1142: hr_utility.trace('get dyn sql params are ....');
1143: hr_utility.trace('dyn sql is '||p_time_sql);
1144: END IF;
1145:
1146: -- we want the dynamic sql string
1147:
1193: ELSIF ( ( l_value_string = '
1194: THEN
1195:
1196: IF ( g_debug ) THEN
1197: hr_utility.trace('GAZ - INVALID COMBO');
1198: END IF;
1199:
1200: fnd_message.set_name('HXC', 'HXC_TC_INV_EQUAL_IS_NULL_COMBO');
1201: fnd_message.raise_error;
1277: -- EAch Time Category SQL to be evaluated seperately from
1278: -- now on so combining of TIME_SQL not necessary
1279:
1280: IF ( g_debug ) THEN
1281: hr_utility.trace('GAZ - another TC !!!!');
1282: END IF;
1283:
1284: END IF;
1285:
1285:
1286: ELSE
1287:
1288: IF ( g_debug ) THEN
1289: hr_utility.trace('not first time round');
1290: hr_utility.trace('sql is '||l_dyn_sql);
1291: END IF;
1292:
1293: -- set l_string_start for the case when generating SQL for alernate name
1286: ELSE
1287:
1288: IF ( g_debug ) THEN
1289: hr_utility.trace('not first time round');
1290: hr_utility.trace('sql is '||l_dyn_sql);
1291: END IF;
1292:
1293: -- set l_string_start for the case when generating SQL for alernate name
1294:
1314: ELSIF ( ( l_value_string = '
1315: THEN
1316:
1317: IF ( g_debug ) THEN
1318: hr_utility.trace('GAZ - INVALID COMBO');
1319: END IF;
1320:
1321: fnd_message.set_name('HXC', 'HXC_TC_INV_EQUAL_IS_NULL_COMBO');
1322: fnd_message.raise_error;
1388: -- EAch Time Category SQL to be evaluated seperately from
1389: -- now on so combining of TIME_SQL not necessary
1390:
1391: IF ( g_debug ) THEN
1392: hr_utility.trace('GAZ - another TC !!!!');
1393: END IF;
1394:
1395: END IF;
1396: END IF;
1395: END IF;
1396: END IF;
1397:
1398: IF ( g_debug ) THEN
1399: hr_utility.trace('dyn sql is '||l_dyn_sql);
1400: END IF;
1401:
1402: p_time_sql := l_dyn_sql;
1403:
1550:
1551:
1552: BEGIN -- mapping_component_string
1553:
1554: g_debug := hr_utility.debug_enabled;
1555:
1556: l_first_time_round := TRUE;
1557:
1558: -- ***************************************
1560: -- ***************************************
1561:
1562: IF ( g_debug ) THEN
1563: l_proc := g_package||'mapping_component_string';
1564: hr_utility.set_location('Processing '||l_proc, 10);
1565:
1566: hr_utility.trace('Time Category ID is '||to_char(p_time_category_id));
1567: END IF;
1568:
1562: IF ( g_debug ) THEN
1563: l_proc := g_package||'mapping_component_string';
1564: hr_utility.set_location('Processing '||l_proc, 10);
1565:
1566: hr_utility.trace('Time Category ID is '||to_char(p_time_category_id));
1567: END IF;
1568:
1569: -- get the time category operator
1570:
1579: OPEN csr_get_category_comps ( p_time_category_id );
1580: FETCH csr_get_category_comps INTO l_comps_r;
1581:
1582: IF ( g_debug ) THEN
1583: hr_utility.set_location('Processing '||l_proc, 20);
1584: END IF;
1585:
1586: WHILE csr_get_category_comps%FOUND
1587: LOOP
1586: WHILE csr_get_category_comps%FOUND
1587: LOOP
1588:
1589: IF ( g_debug ) THEN
1590: hr_utility.set_location('Processing '||l_proc, 30);
1591: END IF;
1592:
1593: get_dyn_sql ( p_time_sql => l_dynamic_sql
1594: , p_comps_r => l_comps_r
1594: , p_comps_r => l_comps_r
1595: , p_operator => l_operator );
1596:
1597: IF ( g_debug ) THEN
1598: hr_utility.set_location('Processing '||l_proc, 60);
1599: END IF;
1600:
1601: FETCH csr_get_category_comps INTO l_comps_r;
1602:
1604:
1605: END LOOP;
1606:
1607: IF ( g_debug ) THEN
1608: hr_utility.set_location('Processing '||l_proc, 70);
1609: END IF;
1610:
1611: CLOSE csr_get_category_comps;
1612:
1610:
1611: CLOSE csr_get_category_comps;
1612:
1613: IF ( g_debug ) THEN
1614: hr_utility.set_location('Processing '||l_proc, 80);
1615: END IF;
1616:
1617: IF ( l_dynamic_sql IS NOT NULL )
1618: THEN
1624:
1625: p_time_sql := l_dynamic_sql;
1626:
1627: IF ( g_debug ) THEN
1628: hr_utility.trace('Final dyn sql is '||NVL(p_time_sql,'Empty'));
1629: END IF;
1630:
1631: END mapping_component_string;
1632:
1654: l_first_context hxc_bld_blk_info_types.bld_blk_info_type%TYPE;
1655:
1656: BEGIN -- alternate_name_string
1657:
1658: g_debug := hr_utility.debug_enabled;
1659:
1660: IF ( g_debug ) THEN
1661: l_proc := g_package||'alternate_name_string';
1662: hr_utility.set_location('Entering '||l_proc, 10);
1658: g_debug := hr_utility.debug_enabled;
1659:
1660: IF ( g_debug ) THEN
1661: l_proc := g_package||'alternate_name_string';
1662: hr_utility.set_location('Entering '||l_proc, 10);
1663: END IF;
1664:
1665: l_comps_t.DELETE;
1666:
1670: -- ALTERNATE_NAME_STRING
1671: -- ***************************************
1672:
1673: IF ( g_debug ) THEN
1674: hr_utility.set_location('Processing '||l_proc, 20);
1675: END IF;
1676:
1677: OPEN csr_get_alternate_name_comps ( p_alias_value_id );
1678: FETCH csr_get_alternate_name_comps INTO l_alternate_name_comps_r;
1679:
1680: l_first_context := l_alternate_name_comps_r.context;
1681:
1682: IF ( g_debug ) THEN
1683: hr_utility.set_location('Processing '||l_proc, 25);
1684: END IF;
1685:
1686: WHILE csr_get_alternate_name_comps%FOUND
1687: LOOP
1686: WHILE csr_get_alternate_name_comps%FOUND
1687: LOOP
1688:
1689: IF ( g_debug ) THEN
1690: hr_utility.set_location('Processing '||l_proc, 30);
1691: END IF;
1692:
1693: l_value := get_alternate_name_value ( p_alternate_name_comp_r => l_alternate_name_comps_r );
1694:
1707: l_comps_t(l_ind).flex_value_set_id := -1; -- dummy value not used
1708: l_comps_t(l_ind).equal_to := p_equal_to;
1709:
1710: IF ( g_debug ) THEN
1711: hr_utility.set_location('Processing '||l_proc, 60);
1712: END IF;
1713:
1714: FETCH csr_get_alternate_name_comps INTO l_alternate_name_comps_r;
1715:
1731:
1732: END LOOP;
1733:
1734: IF ( g_debug ) THEN
1735: hr_utility.set_location('Processing '||l_proc, 70);
1736: END IF;
1737:
1738: CLOSE csr_get_alternate_name_comps;
1739:
1760:
1761: END LOOP;
1762:
1763: IF ( g_debug ) THEN
1764: hr_utility.set_location('Processing '||l_proc, 80);
1765: END IF;
1766:
1767: IF ( l_dynamic_sql IS NOT NULL )
1768: THEN
1771:
1772: p_time_sql := l_dynamic_sql;
1773:
1774: IF ( g_debug ) THEN
1775: hr_utility.trace('alternate name sql is '||p_time_sql);
1776: END IF;
1777:
1778: END alternate_name_string;
1779:
2055:
2056:
2057:
2058: IF ( g_debug ) THEN
2059: hr_utility.trace('Here are the build blocks....');
2060: END IF;
2061:
2062: OPEN gaz_blk;
2063: FETCH gaz_blk into l_blk;
2065: WHILE gaz_blk%FOUND
2066: LOOP
2067:
2068: IF ( g_debug ) THEN
2069: hr_utility.trace('bb id is : '||to_char(l_blk.bb_id));
2070: hr_utility.trace('measure is : '||to_char(l_blk.measure));
2071: hr_utility.trace('scope is : '||l_blk.scope);
2072: hr_utility.trace('start time : '||to_char(l_blk.start_time,'hh24:mi:ss dd-mon-yy'));
2073: hr_utility.trace('stop time : '||to_char(l_blk.stop_time,'hh24:mi:ss dd-mon-yy'));
2066: LOOP
2067:
2068: IF ( g_debug ) THEN
2069: hr_utility.trace('bb id is : '||to_char(l_blk.bb_id));
2070: hr_utility.trace('measure is : '||to_char(l_blk.measure));
2071: hr_utility.trace('scope is : '||l_blk.scope);
2072: hr_utility.trace('start time : '||to_char(l_blk.start_time,'hh24:mi:ss dd-mon-yy'));
2073: hr_utility.trace('stop time : '||to_char(l_blk.stop_time,'hh24:mi:ss dd-mon-yy'));
2074: END IF;
2067:
2068: IF ( g_debug ) THEN
2069: hr_utility.trace('bb id is : '||to_char(l_blk.bb_id));
2070: hr_utility.trace('measure is : '||to_char(l_blk.measure));
2071: hr_utility.trace('scope is : '||l_blk.scope);
2072: hr_utility.trace('start time : '||to_char(l_blk.start_time,'hh24:mi:ss dd-mon-yy'));
2073: hr_utility.trace('stop time : '||to_char(l_blk.stop_time,'hh24:mi:ss dd-mon-yy'));
2074: END IF;
2075:
2068: IF ( g_debug ) THEN
2069: hr_utility.trace('bb id is : '||to_char(l_blk.bb_id));
2070: hr_utility.trace('measure is : '||to_char(l_blk.measure));
2071: hr_utility.trace('scope is : '||l_blk.scope);
2072: hr_utility.trace('start time : '||to_char(l_blk.start_time,'hh24:mi:ss dd-mon-yy'));
2073: hr_utility.trace('stop time : '||to_char(l_blk.stop_time,'hh24:mi:ss dd-mon-yy'));
2074: END IF;
2075:
2076: FETCH gaz_blk INTO l_blk;
2069: hr_utility.trace('bb id is : '||to_char(l_blk.bb_id));
2070: hr_utility.trace('measure is : '||to_char(l_blk.measure));
2071: hr_utility.trace('scope is : '||l_blk.scope);
2072: hr_utility.trace('start time : '||to_char(l_blk.start_time,'hh24:mi:ss dd-mon-yy'));
2073: hr_utility.trace('stop time : '||to_char(l_blk.stop_time,'hh24:mi:ss dd-mon-yy'));
2074: END IF;
2075:
2076: FETCH gaz_blk INTO l_blk;
2077:
2079:
2080: CLOSE gaz_blk;
2081:
2082: IF ( g_debug ) THEN
2083: hr_utility.trace('Here are the attributes ....');
2084: END IF;
2085:
2086: OPEN gaz_att;
2087: FETCH gaz_att into l_att;
2089: WHILE gaz_att%FOUND
2090: LOOP
2091:
2092: IF ( g_debug ) THEN
2093: hr_utility.trace('ta id is : '||to_char(l_att.ta_id));
2094: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2095: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2096: hr_utility.trace('att cat is : '||l_att.attribute_category);
2097: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2090: LOOP
2091:
2092: IF ( g_debug ) THEN
2093: hr_utility.trace('ta id is : '||to_char(l_att.ta_id));
2094: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2095: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2096: hr_utility.trace('att cat is : '||l_att.attribute_category);
2097: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2098: hr_utility.trace('att 1 is : '||l_att.attribute1);
2091:
2092: IF ( g_debug ) THEN
2093: hr_utility.trace('ta id is : '||to_char(l_att.ta_id));
2094: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2095: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2096: hr_utility.trace('att cat is : '||l_att.attribute_category);
2097: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2098: hr_utility.trace('att 1 is : '||l_att.attribute1);
2099: hr_utility.trace('att 2 is : '||l_att.attribute2);
2092: IF ( g_debug ) THEN
2093: hr_utility.trace('ta id is : '||to_char(l_att.ta_id));
2094: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2095: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2096: hr_utility.trace('att cat is : '||l_att.attribute_category);
2097: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2098: hr_utility.trace('att 1 is : '||l_att.attribute1);
2099: hr_utility.trace('att 2 is : '||l_att.attribute2);
2100: hr_utility.trace('att 3 is : '||l_att.attribute3);
2093: hr_utility.trace('ta id is : '||to_char(l_att.ta_id));
2094: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2095: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2096: hr_utility.trace('att cat is : '||l_att.attribute_category);
2097: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2098: hr_utility.trace('att 1 is : '||l_att.attribute1);
2099: hr_utility.trace('att 2 is : '||l_att.attribute2);
2100: hr_utility.trace('att 3 is : '||l_att.attribute3);
2101: hr_utility.trace('att 4 is : '||l_att.attribute4);
2094: hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2095: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2096: hr_utility.trace('att cat is : '||l_att.attribute_category);
2097: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2098: hr_utility.trace('att 1 is : '||l_att.attribute1);
2099: hr_utility.trace('att 2 is : '||l_att.attribute2);
2100: hr_utility.trace('att 3 is : '||l_att.attribute3);
2101: hr_utility.trace('att 4 is : '||l_att.attribute4);
2102: hr_utility.trace('att 5 is : '||l_att.attribute5);
2095: hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2096: hr_utility.trace('att cat is : '||l_att.attribute_category);
2097: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2098: hr_utility.trace('att 1 is : '||l_att.attribute1);
2099: hr_utility.trace('att 2 is : '||l_att.attribute2);
2100: hr_utility.trace('att 3 is : '||l_att.attribute3);
2101: hr_utility.trace('att 4 is : '||l_att.attribute4);
2102: hr_utility.trace('att 5 is : '||l_att.attribute5);
2103: END IF;
2096: hr_utility.trace('att cat is : '||l_att.attribute_category);
2097: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2098: hr_utility.trace('att 1 is : '||l_att.attribute1);
2099: hr_utility.trace('att 2 is : '||l_att.attribute2);
2100: hr_utility.trace('att 3 is : '||l_att.attribute3);
2101: hr_utility.trace('att 4 is : '||l_att.attribute4);
2102: hr_utility.trace('att 5 is : '||l_att.attribute5);
2103: END IF;
2104:
2097: hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2098: hr_utility.trace('att 1 is : '||l_att.attribute1);
2099: hr_utility.trace('att 2 is : '||l_att.attribute2);
2100: hr_utility.trace('att 3 is : '||l_att.attribute3);
2101: hr_utility.trace('att 4 is : '||l_att.attribute4);
2102: hr_utility.trace('att 5 is : '||l_att.attribute5);
2103: END IF;
2104:
2105: FETCH gaz_att INTO l_att;
2098: hr_utility.trace('att 1 is : '||l_att.attribute1);
2099: hr_utility.trace('att 2 is : '||l_att.attribute2);
2100: hr_utility.trace('att 3 is : '||l_att.attribute3);
2101: hr_utility.trace('att 4 is : '||l_att.attribute4);
2102: hr_utility.trace('att 5 is : '||l_att.attribute5);
2103: END IF;
2104:
2105: FETCH gaz_att INTO l_att;
2106:
2148: l_trunc_atts VARCHAR2(30) := 'delete from hxc_tmp_atts';
2149:
2150: BEGIN
2151:
2152: g_debug := hr_utility.debug_enabled;
2153:
2154: IF ( g_debug ) THEN
2155: l_proc := g_package||'push_timecard';
2156: hr_utility.set_location('Entering '||l_proc, 10);
2152: g_debug := hr_utility.debug_enabled;
2153:
2154: IF ( g_debug ) THEN
2155: l_proc := g_package||'push_timecard';
2156: hr_utility.set_location('Entering '||l_proc, 10);
2157: END IF;
2158:
2159: execute immediate l_trunc_blks;
2160: execute immediate l_trunc_atts;
2168: WHILE l_ind is not null
2169: loop
2170:
2171: IF ( g_debug ) THEN
2172: hr_utility.trace('scope is '||p_blocks(l_ind).scope);
2173: hr_utility.trace('start time is '||p_blocks(l_ind).start_time);
2174: hr_utility.trace('stop time is '||p_blocks(l_ind).stop_time);
2175: END IF;
2176:
2169: loop
2170:
2171: IF ( g_debug ) THEN
2172: hr_utility.trace('scope is '||p_blocks(l_ind).scope);
2173: hr_utility.trace('start time is '||p_blocks(l_ind).start_time);
2174: hr_utility.trace('stop time is '||p_blocks(l_ind).stop_time);
2175: END IF;
2176:
2177: l_ind := p_blocks.NEXT(l_ind);
2170:
2171: IF ( g_debug ) THEN
2172: hr_utility.trace('scope is '||p_blocks(l_ind).scope);
2173: hr_utility.trace('start time is '||p_blocks(l_ind).start_time);
2174: hr_utility.trace('stop time is '||p_blocks(l_ind).stop_time);
2175: END IF;
2176:
2177: l_ind := p_blocks.NEXT(l_ind);
2178:
2195: IF ( FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).date_to) = hr_general.end_of_time )
2196: THEN
2197:
2198: IF ( g_debug ) THEN
2199: hr_utility.trace('Scope : start time '||p_blocks(l_ind).scope||' : '||p_blocks(l_ind).start_time);
2200: END IF;
2201:
2202: IF ( NOT p_detail_blocks_only )
2203: THEN
2328:
2329: -- gaz_debug_push_timecard;
2330:
2331: IF ( g_debug ) THEN
2332: hr_utility.set_location('Leaving '||l_proc, 110);
2333: END IF;
2334:
2335: END push_timecard;
2336:
2433:
2434:
2435: IF ( g_debug ) THEN
2436: l_proc := g_package||'evaluate_time_sql';
2437: hr_utility.trace('Params for Evaluate Time SQL are ....');
2438:
2439: hr_utility.trace('p_time_sql is : '|| l_time_sql );
2440: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2441: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2435: IF ( g_debug ) THEN
2436: l_proc := g_package||'evaluate_time_sql';
2437: hr_utility.trace('Params for Evaluate Time SQL are ....');
2438:
2439: hr_utility.trace('p_time_sql is : '|| l_time_sql );
2440: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2441: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2442: hr_utility.trace('p_operator is : '||p_operator);
2443:
2436: l_proc := g_package||'evaluate_time_sql';
2437: hr_utility.trace('Params for Evaluate Time SQL are ....');
2438:
2439: hr_utility.trace('p_time_sql is : '|| l_time_sql );
2440: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2441: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2442: hr_utility.trace('p_operator is : '||p_operator);
2443:
2444: hr_utility.set_location('Entering '||l_proc, 10);
2437: hr_utility.trace('Params for Evaluate Time SQL are ....');
2438:
2439: hr_utility.trace('p_time_sql is : '|| l_time_sql );
2440: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2441: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2442: hr_utility.trace('p_operator is : '||p_operator);
2443:
2444: hr_utility.set_location('Entering '||l_proc, 10);
2445: END IF;
2438:
2439: hr_utility.trace('p_time_sql is : '|| l_time_sql );
2440: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2441: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2442: hr_utility.trace('p_operator is : '||p_operator);
2443:
2444: hr_utility.set_location('Entering '||l_proc, 10);
2445: END IF;
2446:
2440: hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2441: hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2442: hr_utility.trace('p_operator is : '||p_operator);
2443:
2444: hr_utility.set_location('Entering '||l_proc, 10);
2445: END IF;
2446:
2447:
2448: l_bb_not_ok_string := p_tc_bb_not_ok_string;
2452:
2453: IF g_debug
2454: THEN
2455:
2456: hr_utility.trace('l_parse_time_sql is ');
2457: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2458: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2459: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2460: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2453: IF g_debug
2454: THEN
2455:
2456: hr_utility.trace('l_parse_time_sql is ');
2457: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2458: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2459: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2460: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2461: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2454: THEN
2455:
2456: hr_utility.trace('l_parse_time_sql is ');
2457: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2458: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2459: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2460: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2461: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2462: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2455:
2456: hr_utility.trace('l_parse_time_sql is ');
2457: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2458: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2459: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2460: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2461: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2462: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2463: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2456: hr_utility.trace('l_parse_time_sql is ');
2457: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2458: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2459: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2460: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2461: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2462: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2463: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2464: hr_utility.trace( substr(l_parse_time_sql,1751,250) );
2457: hr_utility.trace( substr(l_parse_time_sql,1,250) );
2458: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2459: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2460: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2461: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2462: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2463: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2464: hr_utility.trace( substr(l_parse_time_sql,1751,250) );
2465:
2458: hr_utility.trace( substr(l_parse_time_sql,251,250) );
2459: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2460: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2461: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2462: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2463: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2464: hr_utility.trace( substr(l_parse_time_sql,1751,250) );
2465:
2466: END IF;
2459: hr_utility.trace( substr(l_parse_time_sql,501,250) );
2460: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2461: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2462: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2463: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2464: hr_utility.trace( substr(l_parse_time_sql,1751,250) );
2465:
2466: END IF;
2467:
2460: hr_utility.trace( substr(l_parse_time_sql,751,250) );
2461: hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2462: hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2463: hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2464: hr_utility.trace( substr(l_parse_time_sql,1751,250) );
2465:
2466: END IF;
2467:
2468: IF ( g_params.p_use_temp_table )
2469: THEN
2470:
2471: IF g_debug
2472: THEN
2473: hr_utility.trace('g_params.p_use_temp_table is TRUE ');
2474: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2475: END IF;
2476:
2477: IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )THEN
2470:
2471: IF g_debug
2472: THEN
2473: hr_utility.trace('g_params.p_use_temp_table is TRUE ');
2474: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2475: END IF;
2476:
2477: IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )THEN
2478:
2492:
2493:
2494: IF g_debug
2495: THEN
2496: hr_utility.trace(' g_params.p_scope is DETAIL ');
2497: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2498: END IF;
2499:
2500: IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )
2493:
2494: IF g_debug
2495: THEN
2496: hr_utility.trace(' g_params.p_scope is DETAIL ');
2497: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2498: END IF;
2499:
2500: IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )
2501: THEN
2519: THEN
2520:
2521: IF g_debug
2522: THEN
2523: hr_utility.trace(' g_params.p_scope is TIME ');
2524: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2525: END IF;
2526:
2527:
2520:
2521: IF g_debug
2522: THEN
2523: hr_utility.trace(' g_params.p_scope is TIME ');
2524: hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2525: END IF;
2526:
2527:
2528: -- sum for timecard
2552:
2553: END IF; -- p_use_temp_table
2554:
2555: IF ( g_debug ) THEN
2556: hr_utility.trace( 'dynamic time sql is ');
2557: hr_utility.trace( substr(l_sql,1,250) );
2558: hr_utility.trace( substr(l_sql,251,250) );
2559: hr_utility.trace( substr(l_sql,501,250) );
2560: hr_utility.trace( substr(l_sql,751,250) );
2553: END IF; -- p_use_temp_table
2554:
2555: IF ( g_debug ) THEN
2556: hr_utility.trace( 'dynamic time sql is ');
2557: hr_utility.trace( substr(l_sql,1,250) );
2558: hr_utility.trace( substr(l_sql,251,250) );
2559: hr_utility.trace( substr(l_sql,501,250) );
2560: hr_utility.trace( substr(l_sql,751,250) );
2561: hr_utility.trace( substr(l_sql,1001,250) );
2554:
2555: IF ( g_debug ) THEN
2556: hr_utility.trace( 'dynamic time sql is ');
2557: hr_utility.trace( substr(l_sql,1,250) );
2558: hr_utility.trace( substr(l_sql,251,250) );
2559: hr_utility.trace( substr(l_sql,501,250) );
2560: hr_utility.trace( substr(l_sql,751,250) );
2561: hr_utility.trace( substr(l_sql,1001,250) );
2562: hr_utility.trace( substr(l_sql,1251,250) );
2555: IF ( g_debug ) THEN
2556: hr_utility.trace( 'dynamic time sql is ');
2557: hr_utility.trace( substr(l_sql,1,250) );
2558: hr_utility.trace( substr(l_sql,251,250) );
2559: hr_utility.trace( substr(l_sql,501,250) );
2560: hr_utility.trace( substr(l_sql,751,250) );
2561: hr_utility.trace( substr(l_sql,1001,250) );
2562: hr_utility.trace( substr(l_sql,1251,250) );
2563: hr_utility.trace( substr(l_sql,1501,250) );
2556: hr_utility.trace( 'dynamic time sql is ');
2557: hr_utility.trace( substr(l_sql,1,250) );
2558: hr_utility.trace( substr(l_sql,251,250) );
2559: hr_utility.trace( substr(l_sql,501,250) );
2560: hr_utility.trace( substr(l_sql,751,250) );
2561: hr_utility.trace( substr(l_sql,1001,250) );
2562: hr_utility.trace( substr(l_sql,1251,250) );
2563: hr_utility.trace( substr(l_sql,1501,250) );
2564: hr_utility.trace( substr(l_sql,1751,250) );
2557: hr_utility.trace( substr(l_sql,1,250) );
2558: hr_utility.trace( substr(l_sql,251,250) );
2559: hr_utility.trace( substr(l_sql,501,250) );
2560: hr_utility.trace( substr(l_sql,751,250) );
2561: hr_utility.trace( substr(l_sql,1001,250) );
2562: hr_utility.trace( substr(l_sql,1251,250) );
2563: hr_utility.trace( substr(l_sql,1501,250) );
2564: hr_utility.trace( substr(l_sql,1751,250) );
2565: END IF;
2558: hr_utility.trace( substr(l_sql,251,250) );
2559: hr_utility.trace( substr(l_sql,501,250) );
2560: hr_utility.trace( substr(l_sql,751,250) );
2561: hr_utility.trace( substr(l_sql,1001,250) );
2562: hr_utility.trace( substr(l_sql,1251,250) );
2563: hr_utility.trace( substr(l_sql,1501,250) );
2564: hr_utility.trace( substr(l_sql,1751,250) );
2565: END IF;
2566:
2559: hr_utility.trace( substr(l_sql,501,250) );
2560: hr_utility.trace( substr(l_sql,751,250) );
2561: hr_utility.trace( substr(l_sql,1001,250) );
2562: hr_utility.trace( substr(l_sql,1251,250) );
2563: hr_utility.trace( substr(l_sql,1501,250) );
2564: hr_utility.trace( substr(l_sql,1751,250) );
2565: END IF;
2566:
2567: -- for the AND operator need to reset p_tc_bb_ok_string
2560: hr_utility.trace( substr(l_sql,751,250) );
2561: hr_utility.trace( substr(l_sql,1001,250) );
2562: hr_utility.trace( substr(l_sql,1251,250) );
2563: hr_utility.trace( substr(l_sql,1501,250) );
2564: hr_utility.trace( substr(l_sql,1751,250) );
2565: END IF;
2566:
2567: -- for the AND operator need to reset p_tc_bb_ok_string
2568: -- since each AND evaluation should start from scratch
2680:
2681: l_rows_fetched := dbms_sql.fetch_rows ( l_csr );
2682:
2683: IF ( g_debug ) THEN
2684: hr_utility.trace('l rows fetched is '||to_char(l_rows_fetched));
2685: END IF;
2686:
2687: IF ( l_rows_fetched > 0 )
2688: THEN
2699:
2700: p_tc_bb_ok_tab(t_bb_id(x)).bb_id_ok := 'Y';
2701:
2702: IF ( g_debug ) THEN
2703: hr_utility.trace('bb ok id is '||to_char(t_bb_id(x)));
2704: END IF;
2705:
2706: -- maintain bb ok string for OR operator
2707:
2712: l_bb_ok_string := l_bb_ok_string || ', ' || to_char(t_bb_id(x));
2713: END IF;
2714:
2715: IF ( g_debug ) THEN
2716: hr_utility.trace('bb ok string is '||l_bb_ok_string);
2717: hr_utility.trace('bb NOT ok string is '||l_bb_not_ok_string);
2718: END IF;
2719:
2720: -- maintain bb not ok string i.e. remove the building block from the string
2713: END IF;
2714:
2715: IF ( g_debug ) THEN
2716: hr_utility.trace('bb ok string is '||l_bb_ok_string);
2717: hr_utility.trace('bb NOT ok string is '||l_bb_not_ok_string);
2718: END IF;
2719:
2720: -- maintain bb not ok string i.e. remove the building block from the string
2721:
2742:
2743: END IF;
2744:
2745: IF ( g_debug ) THEN
2746: hr_utility.trace('bb NOT ok string AFTER is '||l_bb_not_ok_string);
2747: END IF;
2748:
2749: END LOOP;
2750:
2756:
2757: dbms_sql.close_cursor ( l_csr );
2758:
2759: IF ( g_debug ) THEN
2760: hr_utility.trace('GAZ - BB OK string is '||l_bb_ok_string);
2761: hr_utility.trace('GAZ - BB NOT OK string is '||l_bb_not_ok_string);
2762: END IF;
2763:
2764: p_tc_bb_ok_string := l_bb_ok_string;
2757: dbms_sql.close_cursor ( l_csr );
2758:
2759: IF ( g_debug ) THEN
2760: hr_utility.trace('GAZ - BB OK string is '||l_bb_ok_string);
2761: hr_utility.trace('GAZ - BB NOT OK string is '||l_bb_not_ok_string);
2762: END IF;
2763:
2764: p_tc_bb_ok_string := l_bb_ok_string;
2765: p_tc_bb_not_ok_string := l_bb_not_ok_string;
2764: p_tc_bb_ok_string := l_bb_ok_string;
2765: p_tc_bb_not_ok_string := l_bb_not_ok_string;
2766:
2767: IF ( g_debug ) THEN
2768: hr_utility.set_location('Leaving '||l_proc, 170);
2769: END IF;
2770:
2771: exception when others then
2772:
2770:
2771: exception when others then
2772:
2773: IF ( g_debug ) THEN
2774: hr_utility.trace('in exception');
2775: END IF;
2776:
2777: raise;
2778:
2882: ,VALUESET => r_valueset
2883: ,FORMAT => r_format );
2884:
2885: IF ( g_debug ) THEN
2886: hr_utility.trace('where is '||r_valueset.table_info.where_clause);
2887: END IF;
2888:
2889:
2890:
2915: r_valueset.table_info.table_name || ' ' ||
2916: l_where_clause || ' ) ';
2917:
2918: IF ( g_debug ) THEN
2919: hr_utility.trace('Value Set SQL is ');
2920:
2921: hr_utility.trace( substr(l_vs_sql,1,250) );
2922: hr_utility.trace( substr(l_vs_sql,251,250) );
2923: hr_utility.trace( substr(l_vs_sql,501,250) );
2917:
2918: IF ( g_debug ) THEN
2919: hr_utility.trace('Value Set SQL is ');
2920:
2921: hr_utility.trace( substr(l_vs_sql,1,250) );
2922: hr_utility.trace( substr(l_vs_sql,251,250) );
2923: hr_utility.trace( substr(l_vs_sql,501,250) );
2924: hr_utility.trace( substr(l_vs_sql,751,250) );
2925: hr_utility.trace( substr(l_vs_sql,1001,250) );
2918: IF ( g_debug ) THEN
2919: hr_utility.trace('Value Set SQL is ');
2920:
2921: hr_utility.trace( substr(l_vs_sql,1,250) );
2922: hr_utility.trace( substr(l_vs_sql,251,250) );
2923: hr_utility.trace( substr(l_vs_sql,501,250) );
2924: hr_utility.trace( substr(l_vs_sql,751,250) );
2925: hr_utility.trace( substr(l_vs_sql,1001,250) );
2926: hr_utility.trace( substr(l_vs_sql,1251,250) );
2919: hr_utility.trace('Value Set SQL is ');
2920:
2921: hr_utility.trace( substr(l_vs_sql,1,250) );
2922: hr_utility.trace( substr(l_vs_sql,251,250) );
2923: hr_utility.trace( substr(l_vs_sql,501,250) );
2924: hr_utility.trace( substr(l_vs_sql,751,250) );
2925: hr_utility.trace( substr(l_vs_sql,1001,250) );
2926: hr_utility.trace( substr(l_vs_sql,1251,250) );
2927: hr_utility.trace( substr(l_vs_sql,1501,250) );
2920:
2921: hr_utility.trace( substr(l_vs_sql,1,250) );
2922: hr_utility.trace( substr(l_vs_sql,251,250) );
2923: hr_utility.trace( substr(l_vs_sql,501,250) );
2924: hr_utility.trace( substr(l_vs_sql,751,250) );
2925: hr_utility.trace( substr(l_vs_sql,1001,250) );
2926: hr_utility.trace( substr(l_vs_sql,1251,250) );
2927: hr_utility.trace( substr(l_vs_sql,1501,250) );
2928: hr_utility.trace( substr(l_vs_sql,1751,250) );
2921: hr_utility.trace( substr(l_vs_sql,1,250) );
2922: hr_utility.trace( substr(l_vs_sql,251,250) );
2923: hr_utility.trace( substr(l_vs_sql,501,250) );
2924: hr_utility.trace( substr(l_vs_sql,751,250) );
2925: hr_utility.trace( substr(l_vs_sql,1001,250) );
2926: hr_utility.trace( substr(l_vs_sql,1251,250) );
2927: hr_utility.trace( substr(l_vs_sql,1501,250) );
2928: hr_utility.trace( substr(l_vs_sql,1751,250) );
2929: END IF;
2922: hr_utility.trace( substr(l_vs_sql,251,250) );
2923: hr_utility.trace( substr(l_vs_sql,501,250) );
2924: hr_utility.trace( substr(l_vs_sql,751,250) );
2925: hr_utility.trace( substr(l_vs_sql,1001,250) );
2926: hr_utility.trace( substr(l_vs_sql,1251,250) );
2927: hr_utility.trace( substr(l_vs_sql,1501,250) );
2928: hr_utility.trace( substr(l_vs_sql,1751,250) );
2929: END IF;
2930:
2923: hr_utility.trace( substr(l_vs_sql,501,250) );
2924: hr_utility.trace( substr(l_vs_sql,751,250) );
2925: hr_utility.trace( substr(l_vs_sql,1001,250) );
2926: hr_utility.trace( substr(l_vs_sql,1251,250) );
2927: hr_utility.trace( substr(l_vs_sql,1501,250) );
2928: hr_utility.trace( substr(l_vs_sql,1751,250) );
2929: END IF;
2930:
2931: l_vs_sql := REPLACE ( l_sql, '
2924: hr_utility.trace( substr(l_vs_sql,751,250) );
2925: hr_utility.trace( substr(l_vs_sql,1001,250) );
2926: hr_utility.trace( substr(l_vs_sql,1251,250) );
2927: hr_utility.trace( substr(l_vs_sql,1501,250) );
2928: hr_utility.trace( substr(l_vs_sql,1751,250) );
2929: END IF;
2930:
2931: l_vs_sql := REPLACE ( l_sql, '
2932:
2930:
2931: l_vs_sql := REPLACE ( l_sql, '
2932:
2933: IF ( g_debug ) THEN
2934: hr_utility.trace('Final Value Set String ...');
2935: hr_utility.trace( substr(l_vs_sql,1,250) );
2936: hr_utility.trace( substr(l_vs_sql,251,250) );
2937: hr_utility.trace( substr(l_vs_sql,501,250) );
2938: hr_utility.trace( substr(l_vs_sql,751,250) );
2931: l_vs_sql := REPLACE ( l_sql, '
2932:
2933: IF ( g_debug ) THEN
2934: hr_utility.trace('Final Value Set String ...');
2935: hr_utility.trace( substr(l_vs_sql,1,250) );
2936: hr_utility.trace( substr(l_vs_sql,251,250) );
2937: hr_utility.trace( substr(l_vs_sql,501,250) );
2938: hr_utility.trace( substr(l_vs_sql,751,250) );
2939: hr_utility.trace( substr(l_vs_sql,1001,250) );
2932:
2933: IF ( g_debug ) THEN
2934: hr_utility.trace('Final Value Set String ...');
2935: hr_utility.trace( substr(l_vs_sql,1,250) );
2936: hr_utility.trace( substr(l_vs_sql,251,250) );
2937: hr_utility.trace( substr(l_vs_sql,501,250) );
2938: hr_utility.trace( substr(l_vs_sql,751,250) );
2939: hr_utility.trace( substr(l_vs_sql,1001,250) );
2940: hr_utility.trace( substr(l_vs_sql,1251,250) );
2933: IF ( g_debug ) THEN
2934: hr_utility.trace('Final Value Set String ...');
2935: hr_utility.trace( substr(l_vs_sql,1,250) );
2936: hr_utility.trace( substr(l_vs_sql,251,250) );
2937: hr_utility.trace( substr(l_vs_sql,501,250) );
2938: hr_utility.trace( substr(l_vs_sql,751,250) );
2939: hr_utility.trace( substr(l_vs_sql,1001,250) );
2940: hr_utility.trace( substr(l_vs_sql,1251,250) );
2941: hr_utility.trace( substr(l_vs_sql,1501,250) );
2934: hr_utility.trace('Final Value Set String ...');
2935: hr_utility.trace( substr(l_vs_sql,1,250) );
2936: hr_utility.trace( substr(l_vs_sql,251,250) );
2937: hr_utility.trace( substr(l_vs_sql,501,250) );
2938: hr_utility.trace( substr(l_vs_sql,751,250) );
2939: hr_utility.trace( substr(l_vs_sql,1001,250) );
2940: hr_utility.trace( substr(l_vs_sql,1251,250) );
2941: hr_utility.trace( substr(l_vs_sql,1501,250) );
2942: hr_utility.trace( substr(l_vs_sql,1751,250) );
2935: hr_utility.trace( substr(l_vs_sql,1,250) );
2936: hr_utility.trace( substr(l_vs_sql,251,250) );
2937: hr_utility.trace( substr(l_vs_sql,501,250) );
2938: hr_utility.trace( substr(l_vs_sql,751,250) );
2939: hr_utility.trace( substr(l_vs_sql,1001,250) );
2940: hr_utility.trace( substr(l_vs_sql,1251,250) );
2941: hr_utility.trace( substr(l_vs_sql,1501,250) );
2942: hr_utility.trace( substr(l_vs_sql,1751,250) );
2943: END IF;
2936: hr_utility.trace( substr(l_vs_sql,251,250) );
2937: hr_utility.trace( substr(l_vs_sql,501,250) );
2938: hr_utility.trace( substr(l_vs_sql,751,250) );
2939: hr_utility.trace( substr(l_vs_sql,1001,250) );
2940: hr_utility.trace( substr(l_vs_sql,1251,250) );
2941: hr_utility.trace( substr(l_vs_sql,1501,250) );
2942: hr_utility.trace( substr(l_vs_sql,1751,250) );
2943: END IF;
2944:
2937: hr_utility.trace( substr(l_vs_sql,501,250) );
2938: hr_utility.trace( substr(l_vs_sql,751,250) );
2939: hr_utility.trace( substr(l_vs_sql,1001,250) );
2940: hr_utility.trace( substr(l_vs_sql,1251,250) );
2941: hr_utility.trace( substr(l_vs_sql,1501,250) );
2942: hr_utility.trace( substr(l_vs_sql,1751,250) );
2943: END IF;
2944:
2945: p_vs_sql := l_vs_sql;
2938: hr_utility.trace( substr(l_vs_sql,751,250) );
2939: hr_utility.trace( substr(l_vs_sql,1001,250) );
2940: hr_utility.trace( substr(l_vs_sql,1251,250) );
2941: hr_utility.trace( substr(l_vs_sql,1501,250) );
2942: hr_utility.trace( substr(l_vs_sql,1751,250) );
2943: END IF;
2944:
2945: p_vs_sql := l_vs_sql;
2946:
2992: l_index number;
2993:
2994: BEGIN
2995:
2996: g_debug := hr_utility.debug_enabled;
2997:
2998:
2999: IF ( g_debug ) THEN
3000: l_proc := g_package||'sum_bb_ok_hrs';
2997:
2998:
2999: IF ( g_debug ) THEN
3000: l_proc := g_package||'sum_bb_ok_hrs';
3001: hr_utility.trace('Params are ');
3002: hr_utility.trace('p_tc_bb_ok_string is '||p_tc_bb_ok_string);
3003: hr_utility.trace('p_hrs are '||to_char(p_hrs));
3004: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
3005: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
2998:
2999: IF ( g_debug ) THEN
3000: l_proc := g_package||'sum_bb_ok_hrs';
3001: hr_utility.trace('Params are ');
3002: hr_utility.trace('p_tc_bb_ok_string is '||p_tc_bb_ok_string);
3003: hr_utility.trace('p_hrs are '||to_char(p_hrs));
3004: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
3005: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
3006:
2999: IF ( g_debug ) THEN
3000: l_proc := g_package||'sum_bb_ok_hrs';
3001: hr_utility.trace('Params are ');
3002: hr_utility.trace('p_tc_bb_ok_string is '||p_tc_bb_ok_string);
3003: hr_utility.trace('p_hrs are '||to_char(p_hrs));
3004: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
3005: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
3006:
3007:
3000: l_proc := g_package||'sum_bb_ok_hrs';
3001: hr_utility.trace('Params are ');
3002: hr_utility.trace('p_tc_bb_ok_string is '||p_tc_bb_ok_string);
3003: hr_utility.trace('p_hrs are '||to_char(p_hrs));
3004: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
3005: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
3006:
3007:
3008: hr_utility.set_location('Entering '||l_proc, 10);
3001: hr_utility.trace('Params are ');
3002: hr_utility.trace('p_tc_bb_ok_string is '||p_tc_bb_ok_string);
3003: hr_utility.trace('p_hrs are '||to_char(p_hrs));
3004: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
3005: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
3006:
3007:
3008: hr_utility.set_location('Entering '||l_proc, 10);
3009: END IF;
3004: hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
3005: hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
3006:
3007:
3008: hr_utility.set_location('Entering '||l_proc, 10);
3009: END IF;
3010:
3011:
3012: -- Added for Bug 13528722
3076: END IF;
3077:
3078:
3079: IF ( g_debug ) THEN
3080: hr_utility.trace( 'p_tc_bb_ok_string ::'||p_tc_bb_ok_string);
3081: hr_utility.trace( 'dynamic hrs l_select is ::'||l_select);
3082: hr_utility.trace( 'dynamic hrs l_select_null is ::'||l_select_null);
3083: END IF;
3084:
3077:
3078:
3079: IF ( g_debug ) THEN
3080: hr_utility.trace( 'p_tc_bb_ok_string ::'||p_tc_bb_ok_string);
3081: hr_utility.trace( 'dynamic hrs l_select is ::'||l_select);
3082: hr_utility.trace( 'dynamic hrs l_select_null is ::'||l_select_null);
3083: END IF;
3084:
3085: -- End of Bug 13528722
3078:
3079: IF ( g_debug ) THEN
3080: hr_utility.trace( 'p_tc_bb_ok_string ::'||p_tc_bb_ok_string);
3081: hr_utility.trace( 'dynamic hrs l_select is ::'||l_select);
3082: hr_utility.trace( 'dynamic hrs l_select_null is ::'||l_select_null);
3083: END IF;
3084:
3085: -- End of Bug 13528722
3086:
3090: --l_sql := l_select || p_tc_bb_ok_string || ' ) ';
3091: l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
3092:
3093: IF ( g_debug ) THEN
3094: hr_utility.trace('p_tc_bb_ok_string 1');
3095: hr_utility.trace( 'dynamic hrs sql is ');
3096: hr_utility.trace( substr(l_sql,1,250) );
3097: hr_utility.trace( substr(l_sql,251,250) );
3098: hr_utility.trace( substr(l_sql,501,250) );
3091: l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
3092:
3093: IF ( g_debug ) THEN
3094: hr_utility.trace('p_tc_bb_ok_string 1');
3095: hr_utility.trace( 'dynamic hrs sql is ');
3096: hr_utility.trace( substr(l_sql,1,250) );
3097: hr_utility.trace( substr(l_sql,251,250) );
3098: hr_utility.trace( substr(l_sql,501,250) );
3099: hr_utility.trace( substr(l_sql,751,250) );
3092:
3093: IF ( g_debug ) THEN
3094: hr_utility.trace('p_tc_bb_ok_string 1');
3095: hr_utility.trace( 'dynamic hrs sql is ');
3096: hr_utility.trace( substr(l_sql,1,250) );
3097: hr_utility.trace( substr(l_sql,251,250) );
3098: hr_utility.trace( substr(l_sql,501,250) );
3099: hr_utility.trace( substr(l_sql,751,250) );
3100: hr_utility.trace( substr(l_sql,1001,250) );
3093: IF ( g_debug ) THEN
3094: hr_utility.trace('p_tc_bb_ok_string 1');
3095: hr_utility.trace( 'dynamic hrs sql is ');
3096: hr_utility.trace( substr(l_sql,1,250) );
3097: hr_utility.trace( substr(l_sql,251,250) );
3098: hr_utility.trace( substr(l_sql,501,250) );
3099: hr_utility.trace( substr(l_sql,751,250) );
3100: hr_utility.trace( substr(l_sql,1001,250) );
3101: hr_utility.trace( substr(l_sql,1251,250) );
3094: hr_utility.trace('p_tc_bb_ok_string 1');
3095: hr_utility.trace( 'dynamic hrs sql is ');
3096: hr_utility.trace( substr(l_sql,1,250) );
3097: hr_utility.trace( substr(l_sql,251,250) );
3098: hr_utility.trace( substr(l_sql,501,250) );
3099: hr_utility.trace( substr(l_sql,751,250) );
3100: hr_utility.trace( substr(l_sql,1001,250) );
3101: hr_utility.trace( substr(l_sql,1251,250) );
3102: hr_utility.trace( substr(l_sql,1501,250) );
3095: hr_utility.trace( 'dynamic hrs sql is ');
3096: hr_utility.trace( substr(l_sql,1,250) );
3097: hr_utility.trace( substr(l_sql,251,250) );
3098: hr_utility.trace( substr(l_sql,501,250) );
3099: hr_utility.trace( substr(l_sql,751,250) );
3100: hr_utility.trace( substr(l_sql,1001,250) );
3101: hr_utility.trace( substr(l_sql,1251,250) );
3102: hr_utility.trace( substr(l_sql,1501,250) );
3103: hr_utility.trace( substr(l_sql,1751,250) );
3096: hr_utility.trace( substr(l_sql,1,250) );
3097: hr_utility.trace( substr(l_sql,251,250) );
3098: hr_utility.trace( substr(l_sql,501,250) );
3099: hr_utility.trace( substr(l_sql,751,250) );
3100: hr_utility.trace( substr(l_sql,1001,250) );
3101: hr_utility.trace( substr(l_sql,1251,250) );
3102: hr_utility.trace( substr(l_sql,1501,250) );
3103: hr_utility.trace( substr(l_sql,1751,250) );
3104: END IF;
3097: hr_utility.trace( substr(l_sql,251,250) );
3098: hr_utility.trace( substr(l_sql,501,250) );
3099: hr_utility.trace( substr(l_sql,751,250) );
3100: hr_utility.trace( substr(l_sql,1001,250) );
3101: hr_utility.trace( substr(l_sql,1251,250) );
3102: hr_utility.trace( substr(l_sql,1501,250) );
3103: hr_utility.trace( substr(l_sql,1751,250) );
3104: END IF;
3105:
3098: hr_utility.trace( substr(l_sql,501,250) );
3099: hr_utility.trace( substr(l_sql,751,250) );
3100: hr_utility.trace( substr(l_sql,1001,250) );
3101: hr_utility.trace( substr(l_sql,1251,250) );
3102: hr_utility.trace( substr(l_sql,1501,250) );
3103: hr_utility.trace( substr(l_sql,1751,250) );
3104: END IF;
3105:
3106: l_csr := dbms_sql.open_cursor;
3099: hr_utility.trace( substr(l_sql,751,250) );
3100: hr_utility.trace( substr(l_sql,1001,250) );
3101: hr_utility.trace( substr(l_sql,1251,250) );
3102: hr_utility.trace( substr(l_sql,1501,250) );
3103: hr_utility.trace( substr(l_sql,1751,250) );
3104: END IF;
3105:
3106: l_csr := dbms_sql.open_cursor;
3107:
3128:
3129: ELSIF ( hxc_time_category_utils_pkg.g_time_category_id IS NULL )
3130: THEN
3131: IF ( g_debug ) THEN
3132: hr_utility.trace('KRISH p_tc_bb_ok_string 2');
3133: END IF;
3134:
3135: l_sql := l_select_null;
3136:
3137: execute immediate l_sql INTO l_hrs USING p_period_end, p_period_start;
3138:
3139: ELSE
3140: IF ( g_debug ) THEN
3141: hr_utility.trace('KRISH p_tc_bb_ok_string 3');
3142: END IF;
3143:
3144: l_hrs := 0;
3145:
3146:
3147: END IF; -- IF ( p_tc_bb_ok_string IS NOT NULL )
3148:
3149: IF ( g_debug ) THEN
3150: hr_utility.trace('GAZ - HOURS ARE : '||to_char(NVL(l_hrs,0)));
3151: END IF;
3152:
3153: p_hrs := NVL(l_hrs,0);
3154:
3152:
3153: p_hrs := NVL(l_hrs,0);
3154:
3155: IF ( g_debug ) THEN
3156: hr_utility.set_location('Leaving '||l_proc, 170);
3157: END IF;
3158:
3159: END sum_tc_bb_ok_hrs;
3160:
3195: l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
3196:
3197: IF ( g_debug ) THEN
3198: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3199: hr_utility.trace( 'dynamic hrs sql is ');
3200: hr_utility.trace( substr(l_sql,1,250) );
3201: hr_utility.trace( substr(l_sql,251,250) );
3202: hr_utility.trace( substr(l_sql,501,250) );
3203: hr_utility.trace( substr(l_sql,751,250) );
3196:
3197: IF ( g_debug ) THEN
3198: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3199: hr_utility.trace( 'dynamic hrs sql is ');
3200: hr_utility.trace( substr(l_sql,1,250) );
3201: hr_utility.trace( substr(l_sql,251,250) );
3202: hr_utility.trace( substr(l_sql,501,250) );
3203: hr_utility.trace( substr(l_sql,751,250) );
3204: hr_utility.trace( substr(l_sql,1001,250) );
3197: IF ( g_debug ) THEN
3198: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3199: hr_utility.trace( 'dynamic hrs sql is ');
3200: hr_utility.trace( substr(l_sql,1,250) );
3201: hr_utility.trace( substr(l_sql,251,250) );
3202: hr_utility.trace( substr(l_sql,501,250) );
3203: hr_utility.trace( substr(l_sql,751,250) );
3204: hr_utility.trace( substr(l_sql,1001,250) );
3205: hr_utility.trace( substr(l_sql,1251,250) );
3198: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3199: hr_utility.trace( 'dynamic hrs sql is ');
3200: hr_utility.trace( substr(l_sql,1,250) );
3201: hr_utility.trace( substr(l_sql,251,250) );
3202: hr_utility.trace( substr(l_sql,501,250) );
3203: hr_utility.trace( substr(l_sql,751,250) );
3204: hr_utility.trace( substr(l_sql,1001,250) );
3205: hr_utility.trace( substr(l_sql,1251,250) );
3206: hr_utility.trace( substr(l_sql,1501,250) );
3199: hr_utility.trace( 'dynamic hrs sql is ');
3200: hr_utility.trace( substr(l_sql,1,250) );
3201: hr_utility.trace( substr(l_sql,251,250) );
3202: hr_utility.trace( substr(l_sql,501,250) );
3203: hr_utility.trace( substr(l_sql,751,250) );
3204: hr_utility.trace( substr(l_sql,1001,250) );
3205: hr_utility.trace( substr(l_sql,1251,250) );
3206: hr_utility.trace( substr(l_sql,1501,250) );
3207: hr_utility.trace( substr(l_sql,1751,250) );
3200: hr_utility.trace( substr(l_sql,1,250) );
3201: hr_utility.trace( substr(l_sql,251,250) );
3202: hr_utility.trace( substr(l_sql,501,250) );
3203: hr_utility.trace( substr(l_sql,751,250) );
3204: hr_utility.trace( substr(l_sql,1001,250) );
3205: hr_utility.trace( substr(l_sql,1251,250) );
3206: hr_utility.trace( substr(l_sql,1501,250) );
3207: hr_utility.trace( substr(l_sql,1751,250) );
3208: END IF;
3201: hr_utility.trace( substr(l_sql,251,250) );
3202: hr_utility.trace( substr(l_sql,501,250) );
3203: hr_utility.trace( substr(l_sql,751,250) );
3204: hr_utility.trace( substr(l_sql,1001,250) );
3205: hr_utility.trace( substr(l_sql,1251,250) );
3206: hr_utility.trace( substr(l_sql,1501,250) );
3207: hr_utility.trace( substr(l_sql,1751,250) );
3208: END IF;
3209:
3202: hr_utility.trace( substr(l_sql,501,250) );
3203: hr_utility.trace( substr(l_sql,751,250) );
3204: hr_utility.trace( substr(l_sql,1001,250) );
3205: hr_utility.trace( substr(l_sql,1251,250) );
3206: hr_utility.trace( substr(l_sql,1501,250) );
3207: hr_utility.trace( substr(l_sql,1751,250) );
3208: END IF;
3209:
3210: l_csr := dbms_sql.open_cursor;
3203: hr_utility.trace( substr(l_sql,751,250) );
3204: hr_utility.trace( substr(l_sql,1001,250) );
3205: hr_utility.trace( substr(l_sql,1251,250) );
3206: hr_utility.trace( substr(l_sql,1501,250) );
3207: hr_utility.trace( substr(l_sql,1751,250) );
3208: END IF;
3209:
3210: l_csr := dbms_sql.open_cursor;
3211:
3234:
3235: END IF; -- IF ( p_tc_bb_ok_string IS NOT NULL )
3236:
3237: IF ( g_debug ) THEN
3238: hr_utility.trace('GAZ - HOURS ARE : '||to_char(NVL(l_hrs,0)));
3239: END IF;
3240:
3241: p_hrs := NVL( l_hrs, 0 );
3242:
3240:
3241: p_hrs := NVL( l_hrs, 0 );
3242:
3243: IF ( g_debug ) THEN
3244: hr_utility.set_location('Leaving '||l_proc, 170);
3245: END IF;
3246:
3247: END sum_live_tc_bb_ok_hrs;
3248:
3288: l_sql := l_select || p_tc_bb_ok_string || ' ) ';
3289:
3290: IF ( g_debug ) THEN
3291: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3292: hr_utility.trace( 'dynamic hrs sql is ');
3293: hr_utility.trace( substr(l_sql,1,250) );
3294: hr_utility.trace( substr(l_sql,251,250) );
3295: hr_utility.trace( substr(l_sql,501,250) );
3296: hr_utility.trace( substr(l_sql,751,250) );
3289:
3290: IF ( g_debug ) THEN
3291: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3292: hr_utility.trace( 'dynamic hrs sql is ');
3293: hr_utility.trace( substr(l_sql,1,250) );
3294: hr_utility.trace( substr(l_sql,251,250) );
3295: hr_utility.trace( substr(l_sql,501,250) );
3296: hr_utility.trace( substr(l_sql,751,250) );
3297: hr_utility.trace( substr(l_sql,1001,250) );
3290: IF ( g_debug ) THEN
3291: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3292: hr_utility.trace( 'dynamic hrs sql is ');
3293: hr_utility.trace( substr(l_sql,1,250) );
3294: hr_utility.trace( substr(l_sql,251,250) );
3295: hr_utility.trace( substr(l_sql,501,250) );
3296: hr_utility.trace( substr(l_sql,751,250) );
3297: hr_utility.trace( substr(l_sql,1001,250) );
3298: hr_utility.trace( substr(l_sql,1251,250) );
3291: l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3292: hr_utility.trace( 'dynamic hrs sql is ');
3293: hr_utility.trace( substr(l_sql,1,250) );
3294: hr_utility.trace( substr(l_sql,251,250) );
3295: hr_utility.trace( substr(l_sql,501,250) );
3296: hr_utility.trace( substr(l_sql,751,250) );
3297: hr_utility.trace( substr(l_sql,1001,250) );
3298: hr_utility.trace( substr(l_sql,1251,250) );
3299: hr_utility.trace( substr(l_sql,1501,250) );
3292: hr_utility.trace( 'dynamic hrs sql is ');
3293: hr_utility.trace( substr(l_sql,1,250) );
3294: hr_utility.trace( substr(l_sql,251,250) );
3295: hr_utility.trace( substr(l_sql,501,250) );
3296: hr_utility.trace( substr(l_sql,751,250) );
3297: hr_utility.trace( substr(l_sql,1001,250) );
3298: hr_utility.trace( substr(l_sql,1251,250) );
3299: hr_utility.trace( substr(l_sql,1501,250) );
3300: hr_utility.trace( substr(l_sql,1751,250) );
3293: hr_utility.trace( substr(l_sql,1,250) );
3294: hr_utility.trace( substr(l_sql,251,250) );
3295: hr_utility.trace( substr(l_sql,501,250) );
3296: hr_utility.trace( substr(l_sql,751,250) );
3297: hr_utility.trace( substr(l_sql,1001,250) );
3298: hr_utility.trace( substr(l_sql,1251,250) );
3299: hr_utility.trace( substr(l_sql,1501,250) );
3300: hr_utility.trace( substr(l_sql,1751,250) );
3301: END IF;
3294: hr_utility.trace( substr(l_sql,251,250) );
3295: hr_utility.trace( substr(l_sql,501,250) );
3296: hr_utility.trace( substr(l_sql,751,250) );
3297: hr_utility.trace( substr(l_sql,1001,250) );
3298: hr_utility.trace( substr(l_sql,1251,250) );
3299: hr_utility.trace( substr(l_sql,1501,250) );
3300: hr_utility.trace( substr(l_sql,1751,250) );
3301: END IF;
3302:
3295: hr_utility.trace( substr(l_sql,501,250) );
3296: hr_utility.trace( substr(l_sql,751,250) );
3297: hr_utility.trace( substr(l_sql,1001,250) );
3298: hr_utility.trace( substr(l_sql,1251,250) );
3299: hr_utility.trace( substr(l_sql,1501,250) );
3300: hr_utility.trace( substr(l_sql,1751,250) );
3301: END IF;
3302:
3303: execute immediate l_sql INTO l_hrs;
3296: hr_utility.trace( substr(l_sql,751,250) );
3297: hr_utility.trace( substr(l_sql,1001,250) );
3298: hr_utility.trace( substr(l_sql,1251,250) );
3299: hr_utility.trace( substr(l_sql,1501,250) );
3300: hr_utility.trace( substr(l_sql,1751,250) );
3301: END IF;
3302:
3303: execute immediate l_sql INTO l_hrs;
3304:
3308:
3309: END IF; -- IF ( p_tc_bb_ok_string IS NOT NULL )
3310:
3311: IF ( g_debug ) THEN
3312: hr_utility.trace('GAZ - HOURS ARE : '||to_char(NVL(l_hrs,0)));
3313: END IF;
3314:
3315: p_hrs := NVL( l_hrs, 0 );
3316:
3314:
3315: p_hrs := NVL( l_hrs, 0 );
3316:
3317: IF ( g_debug ) THEN
3318: hr_utility.set_location('Leaving '||l_proc, 170);
3319: END IF;
3320:
3321: END sum_live_tc_bb_ok_hrs;
3322:
3416: l_tc_ind PLS_INTEGER := 1;
3417:
3418:
3419: BEGIN -- evaluate_time_category
3420: g_debug := hr_utility.debug_enabled;
3421:
3422: g_params.p_time_category_id := p_time_category_id;
3423: g_params.p_use_tc_cache := p_use_tc_cache;
3424: g_params.p_use_tc_bb_cache := p_use_tc_bb_cache;
3430: -- gaz_debug_push_timecard;
3431:
3432: IF ( g_debug ) THEN
3433: l_proc := g_package||'evaluate_time_category';
3434: hr_utility.trace('*****************************************************');
3435: hr_utility.trace('Params are :');
3436: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3437: hr_utility.trace('scope is : '||g_params.p_scope);
3438: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3431:
3432: IF ( g_debug ) THEN
3433: l_proc := g_package||'evaluate_time_category';
3434: hr_utility.trace('*****************************************************');
3435: hr_utility.trace('Params are :');
3436: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3437: hr_utility.trace('scope is : '||g_params.p_scope);
3438: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3439: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3432: IF ( g_debug ) THEN
3433: l_proc := g_package||'evaluate_time_category';
3434: hr_utility.trace('*****************************************************');
3435: hr_utility.trace('Params are :');
3436: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3437: hr_utility.trace('scope is : '||g_params.p_scope);
3438: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3439: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3440: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3433: l_proc := g_package||'evaluate_time_category';
3434: hr_utility.trace('*****************************************************');
3435: hr_utility.trace('Params are :');
3436: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3437: hr_utility.trace('scope is : '||g_params.p_scope);
3438: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3439: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3440: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3441: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3434: hr_utility.trace('*****************************************************');
3435: hr_utility.trace('Params are :');
3436: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3437: hr_utility.trace('scope is : '||g_params.p_scope);
3438: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3439: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3440: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3441: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3442: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3435: hr_utility.trace('Params are :');
3436: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3437: hr_utility.trace('scope is : '||g_params.p_scope);
3438: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3439: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3440: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3441: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3442: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3443:
3436: hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3437: hr_utility.trace('scope is : '||g_params.p_scope);
3438: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3439: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3440: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3441: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3442: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3443:
3444: IF ( g_params.p_use_tc_cache )
3437: hr_utility.trace('scope is : '||g_params.p_scope);
3438: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3439: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3440: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3441: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3442: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3443:
3444: IF ( g_params.p_use_tc_cache )
3445: THEN
3438: hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3439: hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3440: hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3441: hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3442: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3443:
3444: IF ( g_params.p_use_tc_cache )
3445: THEN
3446: hr_utility.trace('p_use_tc_cache is TRUE');
3442: hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3443:
3444: IF ( g_params.p_use_tc_cache )
3445: THEN
3446: hr_utility.trace('p_use_tc_cache is TRUE');
3447: ELSE
3448: hr_utility.trace('p_use_tc_cache is FALSE');
3449: END IF;
3450:
3444: IF ( g_params.p_use_tc_cache )
3445: THEN
3446: hr_utility.trace('p_use_tc_cache is TRUE');
3447: ELSE
3448: hr_utility.trace('p_use_tc_cache is FALSE');
3449: END IF;
3450:
3451: IF ( g_params.p_use_tc_bb_cache )
3452: THEN
3449: END IF;
3450:
3451: IF ( g_params.p_use_tc_bb_cache )
3452: THEN
3453: hr_utility.trace('p_use_tc_bb_cache is TRUE');
3454: ELSE
3455: hr_utility.trace('p_use_tc_bb_cache is FALSE');
3456: END IF;
3457: IF ( g_params.p_use_temp_table )
3451: IF ( g_params.p_use_tc_bb_cache )
3452: THEN
3453: hr_utility.trace('p_use_tc_bb_cache is TRUE');
3454: ELSE
3455: hr_utility.trace('p_use_tc_bb_cache is FALSE');
3456: END IF;
3457: IF ( g_params.p_use_temp_table )
3458: THEN
3459: hr_utility.trace('p_use_temp_table is TRUE');
3455: hr_utility.trace('p_use_tc_bb_cache is FALSE');
3456: END IF;
3457: IF ( g_params.p_use_temp_table )
3458: THEN
3459: hr_utility.trace('p_use_temp_table is TRUE');
3460: ELSE
3461: hr_utility.trace('p_use_temp_table is FALSE');
3462: END IF;
3463:
3457: IF ( g_params.p_use_temp_table )
3458: THEN
3459: hr_utility.trace('p_use_temp_table is TRUE');
3460: ELSE
3461: hr_utility.trace('p_use_temp_table is FALSE');
3462: END IF;
3463:
3464: hr_utility.trace('tc bb ok string '||p_tc_bb_ok_string);
3465:
3460: ELSE
3461: hr_utility.trace('p_use_temp_table is FALSE');
3462: END IF;
3463:
3464: hr_utility.trace('tc bb ok string '||p_tc_bb_ok_string);
3465:
3466: END IF; -- l debug
3467:
3468:
3471: IF ( p_use_tc_cache AND tc_cache_exists ( p_time_category_id ) )
3472: THEN
3473:
3474: IF ( g_debug ) THEN
3475: hr_utility.trace('Using time category cache');
3476: END IF;
3477:
3478: l_tc_cache_exists := TRUE;
3479:
3482:
3483: ELSE
3484:
3485: IF ( g_debug ) THEN
3486: hr_utility.trace('NOT using time category cache');
3487: END IF;
3488:
3489: -- get time category TIME_SQL and OPERATOR
3490:
3495: THEN
3496: -- empty time category
3497:
3498: IF ( g_debug ) THEN
3499: hr_utility.trace('Time Category EMPTY anyway !!!');
3500: END IF;
3501:
3502: l_empty_time_category := TRUE;
3503: /* bug fix for 5076837 */
3528: IF ( hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id IS NULL )
3529: THEN
3530:
3531: IF ( g_debug ) THEN
3532: hr_utility.trace('Master Time Category !!!!!!');
3533: END IF;
3534:
3535: -- set master TC info
3536:
3546: IF ( g_tc_bb_ok_cache.exists( p_time_category_id ) AND p_use_tc_bb_cache )
3547: THEN
3548:
3549: IF ( g_debug ) THEN
3550: hr_utility.trace('tc bb ok cache exists');
3551: END IF;
3552:
3553: IF ( ( g_tc_bb_ok_cache(p_time_category_id).timecard_id =
3554: hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id ) AND
3556: hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count ) )
3557: THEN
3558:
3559: IF ( g_debug ) THEN
3560: hr_utility.trace('Using tc bb ok cache');
3561: END IF;
3562:
3563: l_tc_bb_cache_exists := TRUE;
3564:
3565: ELSE
3566: -- different timecard therefore cannot use cache
3567:
3568: IF ( g_debug ) THEN
3569: hr_utility.trace('NOT using tc bb ok cache - diff TC ID');
3570: END IF;
3571:
3572: l_tc_bb_cache_exists := FALSE;
3573: g_tc_bb_ok_cache(p_time_category_id).bb_ok_string := NULL;
3576:
3577: ELSE
3578:
3579: IF ( g_debug ) THEN
3580: hr_utility.trace('tc bb ok cache NOT EXISTS');
3581: END IF;
3582:
3583: l_tc_bb_cache_exists := FALSE;
3584:
3597:
3598: IF ( p_use_temp_table )
3599: THEN
3600: IF ( g_debug ) THEN
3601: hr_utility.trace('Using temporary table to generate not ok bbs');
3602: END IF;
3603:
3604: IF ( hxc_time_category_utils_pkg.g_tc_bb_not_ok_string IS NULL )
3605: THEN
3628:
3629: END LOOP;
3630:
3631: IF ( g_debug ) THEN
3632: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3633: END IF;
3634:
3635: ELSE -- hxc_time_category_utils_pkg.g_tc_bb_not_ok_string IS NOT NULL
3636:
3636:
3637: p_tc_bb_not_ok_string := hxc_time_category_utils_pkg.g_tc_bb_not_ok_string;
3638:
3639: IF ( g_debug ) THEN
3640: hr_utility.trace('Using cache : bb not ok string is '||p_tc_bb_not_ok_string);
3641: END IF;
3642:
3643: END IF; -- hxc_time_category_utils_pkg.g_tc_bb_not_ok_string IS NULL
3644:
3644:
3645: ELSE -- using live table
3646:
3647: IF ( g_debug ) THEN
3648: hr_utility.trace('Using the live table');
3649: END IF;
3650:
3651: IF ( p_scope = 'TIME' )
3652: THEN
3651: IF ( p_scope = 'TIME' )
3652: THEN
3653:
3654: IF ( g_debug ) THEN
3655: hr_utility.trace('Scope is TIME');
3656: END IF;
3657:
3658: -- populate p tc bb not ok string from timecard
3659:
3671:
3672: END IF;
3673:
3674: IF ( g_debug ) THEN
3675: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3676: END IF;
3677:
3678: END IF;
3679:
3691: ELSIF ( p_scope = 'DETAIL' )
3692: THEN
3693:
3694: IF ( g_debug ) THEN
3695: hr_utility.trace('Scope is DETAIL');
3696: END IF;
3697:
3698: p_tc_bb_not_ok_string := g_params.p_tbb_id;
3699:
3697:
3698: p_tc_bb_not_ok_string := g_params.p_tbb_id;
3699:
3700: IF ( g_debug ) THEN
3701: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3702: END IF;
3703:
3704: END IF;
3705:
3723: IF ( NOT l_tc_bb_cache_exists OR NOT p_use_tc_bb_cache )
3724: THEN
3725:
3726: IF ( g_debug ) THEN
3727: hr_utility.trace('NOT using tc bb cache');
3728: END IF;
3729:
3730: -- get time category components
3731:
3745:
3746: ELSE
3747:
3748: IF ( g_debug ) THEN
3749: hr_utility.trace('about to get tc comps');
3750: END IF;
3751:
3752: OPEN csr_get_category_comps( p_time_category_id );
3753: FETCH csr_get_category_comps INTO l_time_category_comps;
3803:
3804: END LOOP;
3805:
3806: IF ( g_debug ) THEN
3807: hr_utility.trace('Category Component Table Counts are .......');
3808: hr_utility.trace('MC_VS count is '||to_char(l_vs_comp_tab.count));
3809: hr_utility.trace('AN count is '||to_char(l_an_comp_tab.count));
3810: hr_utility.trace('TC count is '||to_char(l_tc_comp_tab.count));
3811: END IF;
3804: END LOOP;
3805:
3806: IF ( g_debug ) THEN
3807: hr_utility.trace('Category Component Table Counts are .......');
3808: hr_utility.trace('MC_VS count is '||to_char(l_vs_comp_tab.count));
3809: hr_utility.trace('AN count is '||to_char(l_an_comp_tab.count));
3810: hr_utility.trace('TC count is '||to_char(l_tc_comp_tab.count));
3811: END IF;
3812:
3805:
3806: IF ( g_debug ) THEN
3807: hr_utility.trace('Category Component Table Counts are .......');
3808: hr_utility.trace('MC_VS count is '||to_char(l_vs_comp_tab.count));
3809: hr_utility.trace('AN count is '||to_char(l_an_comp_tab.count));
3810: hr_utility.trace('TC count is '||to_char(l_tc_comp_tab.count));
3811: END IF;
3812:
3813: -- maintain TC CACHE
3806: IF ( g_debug ) THEN
3807: hr_utility.trace('Category Component Table Counts are .......');
3808: hr_utility.trace('MC_VS count is '||to_char(l_vs_comp_tab.count));
3809: hr_utility.trace('AN count is '||to_char(l_an_comp_tab.count));
3810: hr_utility.trace('TC count is '||to_char(l_tc_comp_tab.count));
3811: END IF;
3812:
3813: -- maintain TC CACHE
3814:
3836:
3837: -- TYPE = MC
3838:
3839: IF ( g_debug ) THEN
3840: hr_utility.trace('Evaluating MC');
3841: END IF;
3842:
3843: evaluate_time_sql ( l_time_category_info.time_sql
3844: , NULL
3871: -- if so - then call value_set_string and
3872: -- maintain tccs again
3873:
3874: IF ( g_debug ) THEN
3875: hr_utility.trace('Evaluating MC_VS Loop');
3876: END IF;
3877:
3878: evaluate_time_sql ( NULL
3879: , l_vs_comp_tab(l_vs_ind).sql_string
3905: WHILE ( l_an_ind IS NOT NULL AND l_continue_evaluation )
3906: LOOP
3907:
3908: IF ( g_debug ) THEN
3909: hr_utility.trace('Evaluating AN Loop');
3910: END IF;
3911:
3912: evaluate_time_sql ( NULL
3913: , l_an_comp_tab(l_an_ind).sql_string
4007:
4008: /*
4009:
4010: IF ( g_debug ) THEN
4011: hr_utility.trace('Setting tc bb ok cache for master tc !!!! '||to_char(p_time_category_id));
4012: END IF;
4013:
4014: g_tc_bb_ok_cache(p_time_category_id).bb_ok_string := p_tc_bb_ok_string;
4015:
4029:
4030: -- using the cache
4031:
4032: IF ( g_debug ) THEN
4033: hr_utility.trace('Using bb outcome cache');
4034: END IF;
4035:
4036: hxc_time_category_utils_pkg.g_tc_in_bb_ok := p_time_category_id;
4037:
4046:
4047: END IF; -- IF ( NOT l_empty_time_category )
4048:
4049: IF ( g_debug ) THEN
4050: hr_utility.trace('***************************************************');
4051: hr_utility.trace('Return values from evaluate time category for : '||to_char(p_time_category_id));
4052: hr_utility.trace('bb ok string is '||p_tc_bb_ok_string);
4053: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
4054: hr_utility.trace('bb ok tab is ....');
4047: END IF; -- IF ( NOT l_empty_time_category )
4048:
4049: IF ( g_debug ) THEN
4050: hr_utility.trace('***************************************************');
4051: hr_utility.trace('Return values from evaluate time category for : '||to_char(p_time_category_id));
4052: hr_utility.trace('bb ok string is '||p_tc_bb_ok_string);
4053: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
4054: hr_utility.trace('bb ok tab is ....');
4055:
4048:
4049: IF ( g_debug ) THEN
4050: hr_utility.trace('***************************************************');
4051: hr_utility.trace('Return values from evaluate time category for : '||to_char(p_time_category_id));
4052: hr_utility.trace('bb ok string is '||p_tc_bb_ok_string);
4053: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
4054: hr_utility.trace('bb ok tab is ....');
4055:
4056: l_tc_ind := p_tc_bb_ok_tab.FIRST;
4049: IF ( g_debug ) THEN
4050: hr_utility.trace('***************************************************');
4051: hr_utility.trace('Return values from evaluate time category for : '||to_char(p_time_category_id));
4052: hr_utility.trace('bb ok string is '||p_tc_bb_ok_string);
4053: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
4054: hr_utility.trace('bb ok tab is ....');
4055:
4056: l_tc_ind := p_tc_bb_ok_tab.FIRST;
4057:
4050: hr_utility.trace('***************************************************');
4051: hr_utility.trace('Return values from evaluate time category for : '||to_char(p_time_category_id));
4052: hr_utility.trace('bb ok string is '||p_tc_bb_ok_string);
4053: hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
4054: hr_utility.trace('bb ok tab is ....');
4055:
4056: l_tc_ind := p_tc_bb_ok_tab.FIRST;
4057:
4058: WHILE l_tc_ind IS NOT NULL
4057:
4058: WHILE l_tc_ind IS NOT NULL
4059: LOOP
4060:
4061: hr_utility.trace('bb id is : '||to_char(l_tc_ind));
4062:
4063: l_tc_ind := p_tc_bb_ok_tab.NEXT(l_tc_ind);
4064:
4065: END LOOP;
4062:
4063: l_tc_ind := p_tc_bb_ok_tab.NEXT(l_tc_ind);
4064:
4065: END LOOP;
4066: hr_utility.trace('***************************************************');
4067:
4068: END IF; -- l debug
4069:
4070: -- Reset Variables for the Master TC
4082:
4083: exception when others then
4084:
4085: IF ( g_debug ) THEN
4086: hr_utility.trace('In exception error is '||SQLERRM);
4087: END IF;
4088: raise;
4089:
4090: END evaluate_time_category;
4104: l_proc VARCHAR2(72);
4105:
4106: BEGIN
4107:
4108: g_debug := hr_utility.debug_enabled;
4109:
4110: IF ( g_debug ) THEN
4111: l_proc := g_package||'chk_tc_bb_ok';
4112: hr_utility.set_location('Entering '||l_proc, 10);
4108: g_debug := hr_utility.debug_enabled;
4109:
4110: IF ( g_debug ) THEN
4111: l_proc := g_package||'chk_tc_bb_ok';
4112: hr_utility.set_location('Entering '||l_proc, 10);
4113: END IF;
4114:
4115: IF ( ( g_tc_in_bb_ok <> hxc_time_category_utils_pkg.g_time_category_id ) OR
4116: ( g_tc_in_bb_ok IS NULL ) )
4147: l_operator VARCHAR2(3);
4148:
4149: BEGIN
4150:
4151: g_debug := hr_utility.debug_enabled;
4152:
4153: IF ( g_debug ) THEN
4154: l_proc := g_package||'insert_time_category_comp_sql';
4155: hr_utility.set_location('Entering '||l_proc, 10);
4151: g_debug := hr_utility.debug_enabled;
4152:
4153: IF ( g_debug ) THEN
4154: l_proc := g_package||'insert_time_category_comp_sql';
4155: hr_utility.set_location('Entering '||l_proc, 10);
4156:
4157: hr_utility.trace('Inserting tcc SQL for type '||p_rec.type);
4158: END IF;
4159:
4153: IF ( g_debug ) THEN
4154: l_proc := g_package||'insert_time_category_comp_sql';
4155: hr_utility.set_location('Entering '||l_proc, 10);
4156:
4157: hr_utility.trace('Inserting tcc SQL for type '||p_rec.type);
4158: END IF;
4159:
4160: OPEN csr_get_operator ( p_rec.time_category_id );
4161: FETCH csr_get_operator INTO l_operator;
4196: , p_rec.time_category_comp_id
4197: , l_sql );
4198:
4199: IF ( g_debug ) THEN
4200: hr_utility.set_location('Entering '||l_proc, 20);
4201: END IF;
4202:
4203: END insert_time_category_comp_sql;
4204:
4213: l_tcc_sql csr_chk_tcc_sql_exists%ROWTYPE;
4214:
4215: BEGIN
4216:
4217: g_debug := hr_utility.debug_enabled;
4218:
4219: IF ( g_debug ) THEN
4220: l_proc := g_package||'update_time_category_comp_sql';
4221: hr_utility.trace('Updating tcc SQL for type '||p_rec.type);
4217: g_debug := hr_utility.debug_enabled;
4218:
4219: IF ( g_debug ) THEN
4220: l_proc := g_package||'update_time_category_comp_sql';
4221: hr_utility.trace('Updating tcc SQL for type '||p_rec.type);
4222: END IF;
4223:
4224: -- First check to see row exists
4225: -- user may have changed the TYPE of tcc record
4235:
4236: -- row exists and TCC row still of correct type
4237:
4238: IF ( g_debug ) THEN
4239: hr_utility.trace('is nul is '||p_rec.is_null);
4240: hr_utility.trace('equal to is '||p_Rec.equal_to);
4241: END IF;
4242:
4243: OPEN csr_get_operator ( p_rec.time_category_id );
4236: -- row exists and TCC row still of correct type
4237:
4238: IF ( g_debug ) THEN
4239: hr_utility.trace('is nul is '||p_rec.is_null);
4240: hr_utility.trace('equal to is '||p_Rec.equal_to);
4241: END IF;
4242:
4243: OPEN csr_get_operator ( p_rec.time_category_id );
4244: FETCH csr_get_operator INTO l_operator;
4306: l_proc VARCHAR2(72);
4307:
4308: BEGIN
4309:
4310: g_debug := hr_utility.debug_enabled;
4311:
4312: IF ( g_debug ) THEN
4313: l_proc := g_package||'delete_time_category_comp_sql';
4314: hr_utility.trace('Deleting tcc SQL for type '||p_rec.type);
4310: g_debug := hr_utility.debug_enabled;
4311:
4312: IF ( g_debug ) THEN
4313: l_proc := g_package||'delete_time_category_comp_sql';
4314: hr_utility.trace('Deleting tcc SQL for type '||p_rec.type);
4315: END IF;
4316:
4317: -- First check to see row exists
4318: -- user may have changed the TYPE of tcc record
4360: l_dep_parent_column_name fnd_columns.column_name%TYPE;
4361: --
4362: begin -- get_value_set_sql
4363:
4364: g_debug := hr_utility.debug_enabled;
4365:
4366: IF ( g_debug ) THEN
4367: l_proc := g_package||'get_value_set_sql';
4368: hr_utility.set_location('Entering:'|| l_proc, 5);
4364: g_debug := hr_utility.debug_enabled;
4365:
4366: IF ( g_debug ) THEN
4367: l_proc := g_package||'get_value_set_sql';
4368: hr_utility.set_location('Entering:'|| l_proc, 5);
4369: END IF;
4370:
4371:
4372: l_value_set_id := p_flex_value_set_id;
4379: l_sql_text := '';
4380: l_sql_text_id := '';
4381: --
4382: IF ( g_debug ) THEN
4383: hr_utility.set_location(l_proc, 10);
4384: END IF;
4385:
4386: -- Ok next build the SQL text that can be used to build a pop-list
4387: -- for this segment, if this is a table validated or independant
4395: THEN
4396: -- TABLE validated
4397:
4398: IF ( g_debug ) THEN
4399: hr_utility.set_location(l_proc, 20);
4400: END IF;
4401:
4402: select 'SELECT ' ||
4403: l_valueset_r.table_info.value_column_name ||
4411: into l_sql_text
4412: from dual;
4413:
4414: IF ( g_debug ) THEN
4415: hr_utility.set_location(l_proc, 30);
4416: END IF;
4417:
4418: l_order_by_start := INSTR(upper(l_sql_text),'ORDER BY');
4419: l_from_start := INSTR(upper(l_sql_text),'FROM');
4418: l_order_by_start := INSTR(upper(l_sql_text),'ORDER BY');
4419: l_from_start := INSTR(upper(l_sql_text),'FROM');
4420:
4421: IF ( g_debug ) THEN
4422: hr_utility.set_location(l_proc, 60);
4423: END IF;
4424:
4425: -- Build the SQL for the FROM clause
4426:
4431: l_from_where := substr(l_sql_text,l_from_start);
4432: end if;
4433: --
4434: IF ( g_debug ) THEN
4435: hr_utility.set_location(l_proc, 90);
4436: END IF;
4437: --
4438: if(l_valueset_r.table_info.meaning_column_name is not null) then
4439: l_select_clause := 'SELECT '||l_valueset_r.table_info.
4459:
4460: elsif l_valueset_r.validation_type = 'I' then
4461:
4462: IF ( g_debug ) THEN
4463: hr_utility.set_location(l_proc, 120);
4464: END IF;
4465: --
4466: -- We can hard code the DESC SQL this time, since we know explicitly
4467: -- how independant value sets are built. This should be changed once
4486:
4487: end if; -- validation type
4488:
4489: IF ( g_debug ) THEN
4490: hr_utility.set_location(' Leaving:'||l_proc, 150);
4491: END IF;
4492:
4493: RETURN l_sql_text_id;
4494:
4527:
4528:
4529: IF ( g_debug ) THEN
4530: l_proc := g_package||'parse_sql';
4531: hr_utility.set_location('Processing:'||l_proc, 5);
4532: END IF;
4533:
4534: l_sql_text :=
4535: REPLACE(UPPER(
4537: SUBSTR(p_sql ,INSTR(p_sql,',',1,1)+1, ( (INSTR(p_sql,',',1,2)) - (INSTR(p_sql,',',1,1)+1) ))||' B,TO_CHAR('||
4538: SUBSTR(p_sql ,INSTR(p_sql,',',1,2)+1)), 'FROM', ') C FROM');
4539:
4540: IF ( g_debug ) THEN
4541: hr_utility.set_location('Processing:'||l_proc, 10);
4542: END IF;
4543:
4544: RETURN l_sql_text;
4545:
4546: END parse_sql;
4547:
4548: BEGIN -- get_flex_info
4549:
4550: g_debug := hr_utility.debug_enabled;
4551:
4552: IF ( g_debug ) THEN
4553: l_proc := g_package||'get_flex_info';
4554: hr_utility.set_location('Processing:'||l_proc, 5);
4550: g_debug := hr_utility.debug_enabled;
4551:
4552: IF ( g_debug ) THEN
4553: l_proc := g_package||'get_flex_info';
4554: hr_utility.set_location('Processing:'||l_proc, 5);
4555: END IF;
4556:
4557: hr_flexfield_info.initialize;
4558:
4556:
4557: hr_flexfield_info.initialize;
4558:
4559: IF ( g_debug ) THEN
4560: hr_utility.set_location('Processing:'||l_proc, 10);
4561: END IF;
4562:
4563: hr_flexfield_info.get_segments (
4564: p_appl_short_name => 'HXC'
4568: , p_segments => r_segments_t
4569: , p_session_date => p_session_date );
4570:
4571: IF ( g_debug ) THEN
4572: hr_utility.set_location('Processing:'||l_proc, 20);
4573: END IF;
4574:
4575: -- reduce r_segments_t to l_r_seg_info_t
4576:
4576:
4577: FOR x IN r_segments_t.sequence.FIRST .. r_segments_t.sequence.LAST
4578: LOOP
4579: IF ( g_debug ) THEN
4580: hr_utility.set_location('Processing:'||l_proc, 30);
4581: END IF;
4582:
4583: l_t_seg_info(x).application_column_name := r_segments_t.application_column_name(x);
4584: l_t_seg_info(x).segment_name := r_segments_t.segment_name(x);
4593: -- if value set not table or list or SQL has any $FLEX$ or $PROFILE$ references
4594: -- then we cannot use this to set the record group in the form
4595:
4596: IF ( g_debug ) THEN
4597: hr_utility.trace('SQL is '||l_t_seg_info(x).sql_text);
4598: END IF;
4599:
4600: IF ( l_t_seg_info(x).validation_type = 'NONE' OR l_t_seg_info(x).value_set IS NULL )
4601: THEN
4599:
4600: IF ( l_t_seg_info(x).validation_type = 'NONE' OR l_t_seg_info(x).value_set IS NULL )
4601: THEN
4602: IF ( g_debug ) THEN
4603: hr_utility.trace('validation type NONE');
4604: END IF;
4605:
4606: l_t_seg_info(x).sql_ok := FALSE;
4607: l_t_seg_info(x).no_sql := TRUE;
4608:
4609: ELSIF ( l_t_seg_info(x).validation_type = 'INDEPENDENT')
4610: THEN
4611: IF ( g_debug ) THEN
4612: hr_utility.trace('validation type INDEPENDENT');
4613: END IF;
4614:
4615: l_t_seg_info(x).sql_text := parse_sql ( l_t_seg_info(x).sql_text );
4616: l_t_seg_info(x).sql_ok := TRUE;
4615: l_t_seg_info(x).sql_text := parse_sql ( l_t_seg_info(x).sql_text );
4616: l_t_seg_info(x).sql_ok := TRUE;
4617: ELSE
4618: IF ( g_debug ) THEN
4619: hr_utility.trace('validation type '||l_t_seg_info(x).validation_type);
4620: END IF;
4621:
4622: l_t_seg_info(x).sql_text := parse_sql ( l_t_seg_info(x).sql_text );
4623:
4638:
4639: END LOOP;
4640:
4641: IF ( g_debug ) THEN
4642: hr_utility.set_location('Processing:'||l_proc, 40);
4643: END IF;
4644:
4645: p_seg_info := l_t_seg_info;
4646:
4672: l_csr INTEGER;
4673:
4674: BEGIN
4675:
4676: g_debug := hr_utility.debug_enabled;
4677:
4678: IF ( p_flex_value_set_id = -1 )
4679: THEN
4680:
4693:
4694: ELSE
4695:
4696: IF ( g_debug ) THEN
4697: hr_utility.trace('gaz - before');
4698: END IF;
4699:
4700: l_sql := get_value_set_sql (
4701: p_flex_value_set_id => p_flex_value_set_id
4701: p_flex_value_set_id => p_flex_value_set_id
4702: , p_session_date => sysdate );
4703:
4704: IF ( g_debug ) THEN
4705: hr_utility.trace('gaz - before');
4706: hr_utility.trace('gaz - l sql is '||l_sql);
4707: hr_utility.trace('gaz - p_id is '||p_id);
4708: END IF;
4709:
4702: , p_session_date => sysdate );
4703:
4704: IF ( g_debug ) THEN
4705: hr_utility.trace('gaz - before');
4706: hr_utility.trace('gaz - l sql is '||l_sql);
4707: hr_utility.trace('gaz - p_id is '||p_id);
4708: END IF;
4709:
4710: BEGIN
4703:
4704: IF ( g_debug ) THEN
4705: hr_utility.trace('gaz - before');
4706: hr_utility.trace('gaz - l sql is '||l_sql);
4707: hr_utility.trace('gaz - p_id is '||p_id);
4708: END IF;
4709:
4710: BEGIN
4711:
4773:
4774:
4775: BEGIN
4776:
4777: g_debug := hr_utility.debug_enabled;
4778:
4779: g_tc_bb_ok_tab.delete;
4780:
4781:
4794:
4795:
4796: IF ( g_debug ) THEN
4797: l_proc := g_package||'initialise_time_category';
4798: hr_utility.set_location('Entering '||l_proc, 10);
4799: hr_utility.trace('Initialise_time_category for time_category_id :'||p_time_category_id);
4800: hr_utility.trace('Assigned NULL to the master tc id record ');
4801: END IF;
4802:
4795:
4796: IF ( g_debug ) THEN
4797: l_proc := g_package||'initialise_time_category';
4798: hr_utility.set_location('Entering '||l_proc, 10);
4799: hr_utility.trace('Initialise_time_category for time_category_id :'||p_time_category_id);
4800: hr_utility.trace('Assigned NULL to the master tc id record ');
4801: END IF;
4802:
4803: hxc_time_category_utils_pkg.evaluate_time_category (
4796: IF ( g_debug ) THEN
4797: l_proc := g_package||'initialise_time_category';
4798: hr_utility.set_location('Entering '||l_proc, 10);
4799: hr_utility.trace('Initialise_time_category for time_category_id :'||p_time_category_id);
4800: hr_utility.trace('Assigned NULL to the master tc id record ');
4801: END IF;
4802:
4803: hxc_time_category_utils_pkg.evaluate_time_category (
4804: p_time_category_id => p_time_category_id
4806: , p_tc_bb_ok_string => l_tc_bb_ok_string
4807: , p_tc_bb_not_ok_string => l_tc_bb_not_ok_string );
4808:
4809: IF ( g_debug ) THEN
4810: hr_utility.set_location('Leaving '||l_proc, 20);
4811: END IF;
4812:
4813: END initialise_time_category;
4814:
4871: l_proc varchar2(72);
4872:
4873: BEGIN
4874:
4875: g_debug := hr_utility.debug_enabled;
4876:
4877: IF ( g_debug ) THEN
4878: l_proc := g_package||'category_timecard_hrs';
4879: hr_utility.set_location('Entering '||l_proc, 10);
4875: g_debug := hr_utility.debug_enabled;
4876:
4877: IF ( g_debug ) THEN
4878: l_proc := g_package||'category_timecard_hrs';
4879: hr_utility.set_location('Entering '||l_proc, 10);
4880:
4881: hr_utility.trace('gaz - time cat id is '||p_time_category_name);
4882: END IF;
4883:
4877: IF ( g_debug ) THEN
4878: l_proc := g_package||'category_timecard_hrs';
4879: hr_utility.set_location('Entering '||l_proc, 10);
4880:
4881: hr_utility.trace('gaz - time cat id is '||p_time_category_name);
4882: END IF;
4883:
4884: IF ( p_time_category_name is not null )
4885: THEN
4886:
4887: l_time_category_id := get_time_category_id ( p_time_category_name => p_time_category_name );
4888:
4889: IF ( g_debug ) THEN
4890: hr_utility.trace('gaz - time cat id is '||to_char(l_time_category_id));
4891: hr_utility.trace('gaz - time bb id is '||to_char(p_tbb_id));
4892: hr_utility.trace('gaz - time ovn id is '||to_char(p_tbb_ovn));
4893:
4894: hr_utility.set_location('Processing '||l_proc, 20);
4887: l_time_category_id := get_time_category_id ( p_time_category_name => p_time_category_name );
4888:
4889: IF ( g_debug ) THEN
4890: hr_utility.trace('gaz - time cat id is '||to_char(l_time_category_id));
4891: hr_utility.trace('gaz - time bb id is '||to_char(p_tbb_id));
4892: hr_utility.trace('gaz - time ovn id is '||to_char(p_tbb_ovn));
4893:
4894: hr_utility.set_location('Processing '||l_proc, 20);
4895: END IF;
4888:
4889: IF ( g_debug ) THEN
4890: hr_utility.trace('gaz - time cat id is '||to_char(l_time_category_id));
4891: hr_utility.trace('gaz - time bb id is '||to_char(p_tbb_id));
4892: hr_utility.trace('gaz - time ovn id is '||to_char(p_tbb_ovn));
4893:
4894: hr_utility.set_location('Processing '||l_proc, 20);
4895: END IF;
4896:
4890: hr_utility.trace('gaz - time cat id is '||to_char(l_time_category_id));
4891: hr_utility.trace('gaz - time bb id is '||to_char(p_tbb_id));
4892: hr_utility.trace('gaz - time ovn id is '||to_char(p_tbb_ovn));
4893:
4894: hr_utility.set_location('Processing '||l_proc, 20);
4895: END IF;
4896:
4897: -- call evaluate time category with p_scope = 'DETAIL'
4898:
4917:
4918: END IF;
4919:
4920: IF ( g_debug ) THEN
4921: hr_utility.trace('l timecard hrs are '||to_char(NVL( l_timecard_hrs, 0 ) ));
4922: hr_utility.set_location('Leaving '||l_proc, 30);
4923: END IF;
4924:
4925: RETURN NVL(l_timecard_hrs, 0);
4918: END IF;
4919:
4920: IF ( g_debug ) THEN
4921: hr_utility.trace('l timecard hrs are '||to_char(NVL( l_timecard_hrs, 0 ) ));
4922: hr_utility.set_location('Leaving '||l_proc, 30);
4923: END IF;
4924:
4925: RETURN NVL(l_timecard_hrs, 0);
4926:
4998: l_proc varchar2(72);
4999:
5000: BEGIN
5001:
5002: g_debug := hr_utility.debug_enabled;
5003:
5004: open c_tc_resource_id(p_tbb_id, p_tbb_ovn);
5005: fetch c_tc_resource_id into l_resource_id;
5006: close c_tc_resource_id;
5177: tbb.object_version_number = p_tbb_ovn;
5178:
5179:
5180: BEGIN
5181: g_debug := hr_utility.debug_enabled;
5182:
5183: IF ( g_debug ) THEN
5184: l_proc := g_package||'category_detail_hrs';
5185: hr_utility.set_location('Entering '||l_proc, 10);
5181: g_debug := hr_utility.debug_enabled;
5182:
5183: IF ( g_debug ) THEN
5184: l_proc := g_package||'category_detail_hrs';
5185: hr_utility.set_location('Entering '||l_proc, 10);
5186: END IF;
5187:
5188: IF ( p_time_category_id IS NULL )
5189: THEN
5187:
5188: IF ( p_time_category_id IS NULL )
5189: THEN
5190: IF ( g_debug ) THEN
5191: hr_utility.set_location('Processing '||l_proc, 20);
5192: END IF;
5193:
5194: OPEN csr_calc_all;
5195: FETCH csr_calc_all INTO l_hrs;
5197:
5198: ELSE
5199:
5200: IF ( g_debug ) THEN
5201: hr_utility.set_location('Processing '||l_proc, 30);
5202: END IF;
5203:
5204: -- call evaluate time category with p_scope = 'DETAIL'
5205:
5219:
5220: END IF;
5221:
5222: IF ( g_debug ) THEN
5223: hr_utility.set_location('Leaving '||l_proc, 60);
5224: END IF;
5225:
5226: RETURN NVL(l_hrs,0);
5227:
5260: AND application_period_id = p_application_period_id;
5261:
5262: BEGIN
5263:
5264: g_debug := hr_utility.debug_enabled;
5265:
5266: IF ( g_debug ) THEN
5267: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5268: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5263:
5264: g_debug := hr_utility.debug_enabled;
5265:
5266: IF ( g_debug ) THEN
5267: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5268: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5269: hr_utility.trace('resource id is '||to_char(p_resource_id));
5270: hr_utility.trace('app period id is '||to_char(p_application_period_id));
5271: hr_utility.trace('time category name is '||p_time_category_name);
5264: g_debug := hr_utility.debug_enabled;
5265:
5266: IF ( g_debug ) THEN
5267: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5268: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5269: hr_utility.trace('resource id is '||to_char(p_resource_id));
5270: hr_utility.trace('app period id is '||to_char(p_application_period_id));
5271: hr_utility.trace('time category name is '||p_time_category_name);
5272: END IF;
5265:
5266: IF ( g_debug ) THEN
5267: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5268: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5269: hr_utility.trace('resource id is '||to_char(p_resource_id));
5270: hr_utility.trace('app period id is '||to_char(p_application_period_id));
5271: hr_utility.trace('time category name is '||p_time_category_name);
5272: END IF;
5273:
5266: IF ( g_debug ) THEN
5267: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5268: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5269: hr_utility.trace('resource id is '||to_char(p_resource_id));
5270: hr_utility.trace('app period id is '||to_char(p_application_period_id));
5271: hr_utility.trace('time category name is '||p_time_category_name);
5272: END IF;
5273:
5274: l_precision := hxc_preference_evaluation.resource_preferences
5267: hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5268: hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5269: hr_utility.trace('resource id is '||to_char(p_resource_id));
5270: hr_utility.trace('app period id is '||to_char(p_application_period_id));
5271: hr_utility.trace('time category name is '||p_time_category_name);
5272: END IF;
5273:
5274: l_precision := hxc_preference_evaluation.resource_preferences
5275: (p_resource_id,
5536: l_rec hxc_tcc_shd.g_rec_type;
5537:
5538: BEGIN
5539:
5540: g_debug := hr_utility.debug_enabled;
5541:
5542: IF ( g_debug ) THEN
5543: hr_utility.trace('In ref chk');
5544: END IF;
5539:
5540: g_debug := hr_utility.debug_enabled;
5541:
5542: IF ( g_debug ) THEN
5543: hr_utility.trace('In ref chk');
5544: END IF;
5545:
5546: FOR tc IN csr_get_time_category_comps
5547: LOOP
5557: l_rec.type := tc.type;
5558: l_rec.object_version_number := tc.object_version_number;
5559:
5560: IF ( g_debug ) THEN
5561: hr_utility.trace('about to call update');
5562: END IF;
5563:
5564: update_time_category_comp_sql ( l_rec );
5565: