[Home] [Help]
2: /* $Header: hxttsum.pkb 120.15.12020000.2 2013/02/12 12:08:42 asrajago ship $ */
3:
4: --Global variables for package
5: --Used for parameters received that are not changed
6: g_debug boolean := hr_utility.debug_enabled;
7: g_ep_id NUMBER;
8: g_ep_type HXT_EARNING_POLICIES.FCL_EARN_TYPE%TYPE;
9: g_egt_id NUMBER;
10: g_sdp_id NUMBER;
330: BEGIN
331:
332: IF g_debug
333: THEN
334: hr_utility.trace('g_hol_id '||g_hol_id);
335: hr_utility.trace('g_hol_element_type_id '||g_hol_element_type_id);
336: hr_utility.trace('g_hol_seqno '||g_hol_seqno);
337: hr_utility.trace('g_hol_tim_id '||g_hol_tim_id);
338: hr_utility.trace('g_sum_session_date '||g_sum_session_date);
331:
332: IF g_debug
333: THEN
334: hr_utility.trace('g_hol_id '||g_hol_id);
335: hr_utility.trace('g_hol_element_type_id '||g_hol_element_type_id);
336: hr_utility.trace('g_hol_seqno '||g_hol_seqno);
337: hr_utility.trace('g_hol_tim_id '||g_hol_tim_id);
338: hr_utility.trace('g_sum_session_date '||g_sum_session_date);
339: hr_utility.trace('g_hol_time_in '||to_char(g_hol_time_in,'dd-mon-yyyy HH24:MI'));
332: IF g_debug
333: THEN
334: hr_utility.trace('g_hol_id '||g_hol_id);
335: hr_utility.trace('g_hol_element_type_id '||g_hol_element_type_id);
336: hr_utility.trace('g_hol_seqno '||g_hol_seqno);
337: hr_utility.trace('g_hol_tim_id '||g_hol_tim_id);
338: hr_utility.trace('g_sum_session_date '||g_sum_session_date);
339: hr_utility.trace('g_hol_time_in '||to_char(g_hol_time_in,'dd-mon-yyyy HH24:MI'));
340: END IF;
333: THEN
334: hr_utility.trace('g_hol_id '||g_hol_id);
335: hr_utility.trace('g_hol_element_type_id '||g_hol_element_type_id);
336: hr_utility.trace('g_hol_seqno '||g_hol_seqno);
337: hr_utility.trace('g_hol_tim_id '||g_hol_tim_id);
338: hr_utility.trace('g_sum_session_date '||g_sum_session_date);
339: hr_utility.trace('g_hol_time_in '||to_char(g_hol_time_in,'dd-mon-yyyy HH24:MI'));
340: END IF;
341:
334: hr_utility.trace('g_hol_id '||g_hol_id);
335: hr_utility.trace('g_hol_element_type_id '||g_hol_element_type_id);
336: hr_utility.trace('g_hol_seqno '||g_hol_seqno);
337: hr_utility.trace('g_hol_tim_id '||g_hol_tim_id);
338: hr_utility.trace('g_sum_session_date '||g_sum_session_date);
339: hr_utility.trace('g_hol_time_in '||to_char(g_hol_time_in,'dd-mon-yyyy HH24:MI'));
340: END IF;
341:
342:
335: hr_utility.trace('g_hol_element_type_id '||g_hol_element_type_id);
336: hr_utility.trace('g_hol_seqno '||g_hol_seqno);
337: hr_utility.trace('g_hol_tim_id '||g_hol_tim_id);
338: hr_utility.trace('g_sum_session_date '||g_sum_session_date);
339: hr_utility.trace('g_hol_time_in '||to_char(g_hol_time_in,'dd-mon-yyyy HH24:MI'));
340: END IF;
341:
342:
343:
351: THEN
352:
353: IF g_debug
354: THEN
355: hr_utility.trace('There are no more details to explode ');
356: hr_utility.trace('All the duplicate details can be deleted ');
357: END IF;
358:
359: -- Delete from Details table.
352:
353: IF g_debug
354: THEN
355: hr_utility.trace('There are no more details to explode ');
356: hr_utility.trace('All the duplicate details can be deleted ');
357: END IF;
358:
359: -- Delete from Details table.
360: DELETE FROM hxt_det_hours_worked_f
376:
377:
378: IF g_debug
379: THEN
380: hr_utility.trace('Clearing globals ');
381: END IF;
382: g_hol_ep_id := NULL;
383: g_hol_ep_type := NULL;
384: g_hol_egt_id := NULL;
486: END IF;
487:
488: IF g_debug
489: THEN
490: hr_utility.trace('Hol: g_date_worked '||g_date_worked);
491: hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
492: hr_utility.trace('Hol: l_Retcode'||l_retcode);
493: hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
494: hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
487:
488: IF g_debug
489: THEN
490: hr_utility.trace('Hol: g_date_worked '||g_date_worked);
491: hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
492: hr_utility.trace('Hol: l_Retcode'||l_retcode);
493: hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
494: hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
495: END IF;
488: IF g_debug
489: THEN
490: hr_utility.trace('Hol: g_date_worked '||g_date_worked);
491: hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
492: hr_utility.trace('Hol: l_Retcode'||l_retcode);
493: hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
494: hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
495: END IF;
496:
489: THEN
490: hr_utility.trace('Hol: g_date_worked '||g_date_worked);
491: hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
492: hr_utility.trace('Hol: l_Retcode'||l_retcode);
493: hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
494: hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
495: END IF;
496:
497: IF l_retcode = 1
490: hr_utility.trace('Hol: g_date_worked '||g_date_worked);
491: hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
492: hr_utility.trace('Hol: l_Retcode'||l_retcode);
493: hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
494: hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
495: END IF;
496:
497: IF l_retcode = 1
498: AND g_hol_element_type_id IS NOT NULL
501: END IF;
502:
503: IF g_debug
504: THEN
505: hr_utility.trace('Hol: After correction ');
506: hr_utility.trace('Hol: g_date_worked '||g_date_worked);
507: hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
508: hr_utility.trace('Hol: l_Retcode'||l_retcode);
509: hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
502:
503: IF g_debug
504: THEN
505: hr_utility.trace('Hol: After correction ');
506: hr_utility.trace('Hol: g_date_worked '||g_date_worked);
507: hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
508: hr_utility.trace('Hol: l_Retcode'||l_retcode);
509: hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
510: hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
503: IF g_debug
504: THEN
505: hr_utility.trace('Hol: After correction ');
506: hr_utility.trace('Hol: g_date_worked '||g_date_worked);
507: hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
508: hr_utility.trace('Hol: l_Retcode'||l_retcode);
509: hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
510: hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
511: END IF;
504: THEN
505: hr_utility.trace('Hol: After correction ');
506: hr_utility.trace('Hol: g_date_worked '||g_date_worked);
507: hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
508: hr_utility.trace('Hol: l_Retcode'||l_retcode);
509: hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
510: hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
511: END IF;
512:
505: hr_utility.trace('Hol: After correction ');
506: hr_utility.trace('Hol: g_date_worked '||g_date_worked);
507: hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
508: hr_utility.trace('Hol: l_Retcode'||l_retcode);
509: hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
510: hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
511: END IF;
512:
513:
506: hr_utility.trace('Hol: g_date_worked '||g_date_worked);
507: hr_utility.trace('Hol: g_hol_hours '||g_hol_hours);
508: hr_utility.trace('Hol: l_Retcode'||l_retcode);
509: hr_utility.trace('Hol: g_hol_element_type_id '||g_hol_element_type_id);
510: hr_utility.trace('Hol: g_hol_egt_id '||g_hol_egt_id);
511: END IF;
512:
513:
514: END check_holiday_rule_behavior;
711:
712:
713: IF g_debug
714: THEN
715: hr_utility.trace('ADJUST_HOLIDAY_RULE call here '||g_hol_hol_id||'-'||g_hol_date_worked);
716: END IF;
717:
718: -- Bug 12844923 (Bug 10123886)
719: -- Added the exception block below.
726:
727: EXCEPTION
728: WHEN NO_DATA_FOUND
729: THEN
730: hr_utility.trace('Not a holiday '||g_hol_date_worked);
731: clear_globals;
732: RETURN 0;
733: END;
734:
734:
735:
736: If g_debug
737: THEN
738: hr_utility.trace('l_hol_true '||l_hol_true);
739: END IF;
740:
741: -- Bug 12844923 (Bug 10123886)
742: /*
744: IF l_hol_true IS NULL
745: THEN
746: IF g_debug
747: THEN
748: hr_utility.trace('Not a holiday '||g_hol_date_worked);
749: END IF;
750: clear_globals;
751: RETURN 0;
752: END IF;
766: clear_globals;
767: CLOSE get_holiday_values;
768: IF g_debug
769: THEN
770: hr_utility.trace('No HOLIDAY RULE, NO ADJUSTMENT');
771: END IF;
772: RETURN 0;
773: END IF;
774:
795: END IF;
796:
797: IF g_debug
798: THEN
799: hr_utility.trace('Ash Hol G_hol_time_in '||to_char(g_hol_time_in,'dd-mon-yyyy hh24:mi'));
800: hr_utility.trace('Ash Hol G_hol_time_out '||to_char(g_hol_time_out,'dd-mon-yyyy hh24:mi'));
801: END IF;
802: */
803:
796:
797: IF g_debug
798: THEN
799: hr_utility.trace('Ash Hol G_hol_time_in '||to_char(g_hol_time_in,'dd-mon-yyyy hh24:mi'));
800: hr_utility.trace('Ash Hol G_hol_time_out '||to_char(g_hol_time_out,'dd-mon-yyyy hh24:mi'));
801: END IF;
802: */
803:
804: l_other_summary := NULL;
811: -- a new table.
812: IF l_other_summary IS NULL
813: THEN
814:
815: hr_utility.trace('Selecting Regular Elements ');
816:
817: OPEN get_reg_elements(g_hol_ep_id,g_date_worked);
818: LOOP
819: FETCH get_reg_elements INTO l_reg;
840: IF l_det_tab.COUNT > 0
841: THEN
842: IF g_debug
843: THEN
844: hr_utility.trace(' Regular details exists for element'||l_reg);
845: END IF;
846:
847: FOR i IN l_det_tab.FIRST..l_det_tab.LAST
848: LOOP
894:
895:
896: IF g_debug
897: THEN
898: hr_utility.trace('l_sql is '||l_sql);
899: END IF;
900:
901: OPEN l_refcursor FOR l_sql USING g_hol_tim_id,
902: FND_DATE.DATE_TO_CANONICAL(g_hol_date_worked),
935: IF FND_PROFILE.VALUE('HXT_HOLIDAY_EXPLOSION') = 'OO'
936: THEN
937: IF g_debug
938: THEN
939: hr_utility.trace('Holiday profile set to apply only on Reg elements ');
940: END IF;
941: OPEN get_holiday_overtime;
942:
943: FETCH get_holiday_overtime INTO l_hours;
944:
945: CLOSE get_holiday_overtime;
946: IF g_debug
947: THEN
948: hr_utility.trace('Overtime already paid = '||l_hours);
949: hr_utility.trace('Holiday hours now = '||g_hol_hours);
950: hr_utility.trace('Holiday time_in now = '||TO_CHAR(g_hol_time_in,'HH24:MI'));
951: hr_utility.trace('Holiday time_out now = '||TO_CHAR(g_hol_time_out,'HH24:MI'));
952: END IF;
945: CLOSE get_holiday_overtime;
946: IF g_debug
947: THEN
948: hr_utility.trace('Overtime already paid = '||l_hours);
949: hr_utility.trace('Holiday hours now = '||g_hol_hours);
950: hr_utility.trace('Holiday time_in now = '||TO_CHAR(g_hol_time_in,'HH24:MI'));
951: hr_utility.trace('Holiday time_out now = '||TO_CHAR(g_hol_time_out,'HH24:MI'));
952: END IF;
953:
946: IF g_debug
947: THEN
948: hr_utility.trace('Overtime already paid = '||l_hours);
949: hr_utility.trace('Holiday hours now = '||g_hol_hours);
950: hr_utility.trace('Holiday time_in now = '||TO_CHAR(g_hol_time_in,'HH24:MI'));
951: hr_utility.trace('Holiday time_out now = '||TO_CHAR(g_hol_time_out,'HH24:MI'));
952: END IF;
953:
954: IF l_hours >= g_hol_hours
947: THEN
948: hr_utility.trace('Overtime already paid = '||l_hours);
949: hr_utility.trace('Holiday hours now = '||g_hol_hours);
950: hr_utility.trace('Holiday time_in now = '||TO_CHAR(g_hol_time_in,'HH24:MI'));
951: hr_utility.trace('Holiday time_out now = '||TO_CHAR(g_hol_time_out,'HH24:MI'));
952: END IF;
953:
954: IF l_hours >= g_hol_hours
955: THEN
963: g_hol_time_out := g_hol_time_out - (l_hours/24);
964:
965: IF g_debug
966: THEN
967: hr_utility.trace('After adjustment of Overtime ='||l_hours);
968: hr_utility.trace('Holiday hours = '||g_hol_hours);
969: hr_utility.trace('Holiday time_in = '||TO_CHAR(g_hol_time_in,'HH24:MI'));
970: hr_utility.trace('Holiday time_out = '||TO_CHAR(g_hol_time_out,'HH24:MI'));
971: END IF;
964:
965: IF g_debug
966: THEN
967: hr_utility.trace('After adjustment of Overtime ='||l_hours);
968: hr_utility.trace('Holiday hours = '||g_hol_hours);
969: hr_utility.trace('Holiday time_in = '||TO_CHAR(g_hol_time_in,'HH24:MI'));
970: hr_utility.trace('Holiday time_out = '||TO_CHAR(g_hol_time_out,'HH24:MI'));
971: END IF;
972: END IF;
965: IF g_debug
966: THEN
967: hr_utility.trace('After adjustment of Overtime ='||l_hours);
968: hr_utility.trace('Holiday hours = '||g_hol_hours);
969: hr_utility.trace('Holiday time_in = '||TO_CHAR(g_hol_time_in,'HH24:MI'));
970: hr_utility.trace('Holiday time_out = '||TO_CHAR(g_hol_time_out,'HH24:MI'));
971: END IF;
972: END IF;
973:
966: THEN
967: hr_utility.trace('After adjustment of Overtime ='||l_hours);
968: hr_utility.trace('Holiday hours = '||g_hol_hours);
969: hr_utility.trace('Holiday time_in = '||TO_CHAR(g_hol_time_in,'HH24:MI'));
970: hr_utility.trace('Holiday time_out = '||TO_CHAR(g_hol_time_out,'HH24:MI'));
971: END IF;
972: END IF;
973:
974: -- Adjust the Hours and Time IN/OUT values.
1038: -- is paid correctly.
1039: IF FND_PROFILE.VALUE('HXT_HOLIDAY_EXPLOSION') IN( 'OO','NO')
1040: THEN
1041: hxt_time_detail.g_holiday_sdp_paid := TRUE;
1042: hr_utility.trace('yes '||g_sdp_id);
1043: g_sdf_rule_completed := 'Y';
1044: g_sdf_carryover := null;
1045: ELSE
1046: hxt_time_detail.g_holiday_sdp_paid := FALSE;
1043: g_sdf_rule_completed := 'Y';
1044: g_sdf_carryover := null;
1045: ELSE
1046: hxt_time_detail.g_holiday_sdp_paid := FALSE;
1047: hr_utility.trace('No '||g_sdp_id);
1048: END IF;
1049: g_ep_id := g_hol_ep_id;
1050: g_ep_type := g_hol_ep_type;
1051: g_egt_id := g_hol_egt_id;
1106: -- g_GROUP_ID := g_hol_GROUP_ID;
1107:
1108: IF g_debug
1109: THEN
1110: hr_utility.trace('Hol: Calling rebuild details ');
1111: END IF;
1112:
1113: rebuild_details(g_hol_location,l_rebuild_code);
1114: IF l_rebuild_code <> 0
1112:
1113: rebuild_details(g_hol_location,l_rebuild_code);
1114: IF l_rebuild_code <> 0
1115: THEN
1116: hr_utility.trace('There is a problem while readjusting holiday ');
1117: RETURN l_rebuild_code;
1118: END IF;
1119:
1120:
1119:
1120:
1121: IF g_debug
1122: THEN
1123: hr_utility.trace('Hol: Done Calling rebuild details ');
1124: END IF;
1125:
1126:
1127: e_ind := l_hol_tab.NEXT(e_ind);
1136: THEN
1137:
1138: IF g_debug
1139: THEN
1140: hr_utility.trace('Hol: Checking for duplicate SDPs ');
1141: END IF;
1142:
1143: OPEN get_duplicate_sdp(g_id);
1144: FETCH get_duplicate_sdp BULK COLLECT INTO l_id_tab,
1154: THEN
1155:
1156: IF g_debug
1157: THEN
1158: hr_utility.trace(' Iteration 1 to find those to be updated ');
1159: END IF;
1160:
1161: FOR i IN l_id_tab.FIRST..l_id_tab.LAST
1162: LOOP
1161: FOR i IN l_id_tab.FIRST..l_id_tab.LAST
1162: LOOP
1163: IF g_debug
1164: THEN
1165: hr_utility.trace(l_id_tab(i)||'-'||l_elem_tab(i)
1166: ||'-'||TO_CHAR(l_time_in_tab(i),'HH24:MI')
1167: ||'-'||TO_CHAR(l_time_out_tab(i),'HH24:MI') );
1168: END IF;
1169:
1190: END LOOP;
1191:
1192: IF g_debug
1193: THEN
1194: hr_utility.trace(' Iteration 2 to find those to be deleted ');
1195: END IF;
1196:
1197:
1198: FOR i IN l_id_tab.FIRST..l_id_tab.LAST
1198: FOR i IN l_id_tab.FIRST..l_id_tab.LAST
1199: LOOP
1200: IF g_debug
1201: THEN
1202: hr_utility.trace(l_id_tab(i)||'-'||l_elem_tab(i)
1203: ||'-'||TO_CHAR(l_time_in_tab(i),'HH24:MI')
1204: ||'-'||TO_CHAR(l_time_out_tab(i),'HH24:MI') );
1205: END IF;
1206: IF l_id_tab.EXISTS(i+1)
1407:
1408: BEGIN
1409:
1410: IF g_debug THEN
1411: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',10);
1412: END IF;
1413:
1414: l_hours := p_hours ; -- SIR092
1415:
1416: OPEN next_id_cur;
1417: FETCH next_id_cur INTO l_id;
1418:
1419: IF g_debug THEN
1420: hr_utility.trace('l_id :'||l_id);
1421: END IF;
1422:
1423: CLOSE next_id_cur;
1424:
1426: FETCH get_ovt_rates_cur
1427: INTO l_premium_type, l_premium_amount, l_processing_order;
1428:
1429: IF g_debug THEN
1430: hr_utility.trace('premium_type :'||l_premium_type);
1431: hr_utility.trace('premium_amount :'||l_premium_amount);
1432: hr_utility.trace('processing_order :'||l_processing_order);
1433: END IF;
1434:
1427: INTO l_premium_type, l_premium_amount, l_processing_order;
1428:
1429: IF g_debug THEN
1430: hr_utility.trace('premium_type :'||l_premium_type);
1431: hr_utility.trace('premium_amount :'||l_premium_amount);
1432: hr_utility.trace('processing_order :'||l_processing_order);
1433: END IF;
1434:
1435: CLOSE get_ovt_rates_cur;
1428:
1429: IF g_debug THEN
1430: hr_utility.trace('premium_type :'||l_premium_type);
1431: hr_utility.trace('premium_amount :'||l_premium_amount);
1432: hr_utility.trace('processing_order :'||l_processing_order);
1433: END IF;
1434:
1435: CLOSE get_ovt_rates_cur;
1436:
1459: l_costable_type := HXT_UTIL.get_costable_type(g_element_type_id,
1460: g_date_worked,
1461: g_assignment_id);
1462: IF g_debug THEN
1463: hr_utility.trace('l_costable_type :'||l_costable_type);
1464: END IF;
1465:
1466: IF l_costable_type in ('C','F') THEN
1467: l_ffv_cost_center_id := g_ffv_cost_center_id;
1479: ,l_sqlerrm);
1480:
1481: IF l_error_status = 0 THEN
1482: IF g_debug then
1483: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',20);
1484: END IF;
1485:
1486: HXT_DML.INSERT_HXT_DET_HOURS_WORKED (
1487: p_rowid => l_rowid,
1531: p_ZIP_CODE => g_ZIP_CODE);
1532:
1533: ELSE /* l_error_status <> 0 */
1534: IF g_debug then
1535: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 30);
1536: END IF;
1537: -- Insert record in error table.
1538: FND_MESSAGE.SET_NAME('HXT','HXT_39421_GET_RETRO_ERR');
1539: END IF;
1540:
1541: EXCEPTION
1542: WHEN OTHERS THEN
1543: IF g_debug THEN
1544: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 40);
1545: END IF;
1546: hr_utility.trace(dbms_utility.format_error_backtrace);
1547: -- Insert record in error table.
1548: FND_MESSAGE.SET_NAME('HXT','HXT_39313_OR_ERR_INS_REC');
1542: WHEN OTHERS THEN
1543: IF g_debug THEN
1544: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 40);
1545: END IF;
1546: hr_utility.trace(dbms_utility.format_error_backtrace);
1547: -- Insert record in error table.
1548: FND_MESSAGE.SET_NAME('HXT','HXT_39313_OR_ERR_INS_REC');
1549:
1550: END insert_non_explodable_hrs;
1580:
1581: /* Bug: 4489952 changes ends here */
1582:
1583: BEGIN
1584: g_debug :=hr_utility.debug_enabled;
1585: if g_debug then
1586: hr_utility.set_location('hxt_time_summary.generate_details',10);
1587: end if;
1588:
1582:
1583: BEGIN
1584: g_debug :=hr_utility.debug_enabled;
1585: if g_debug then
1586: hr_utility.set_location('hxt_time_summary.generate_details',10);
1587: end if;
1588:
1589: -- Set the flags for checking which shift diff premium gets paid for a
1590: -- segment chunk.
1684: -- Context of the Preference is TC_W_RULES_EVALUATION
1685: -- Segment is attribute 3 OT_RECURRING_PERIOD
1686: --
1687: if g_debug then
1688: hr_utility.set_location('hxt_time_summary.generate_details',20);
1689: end if;
1690:
1691: -- Changed the following call for Bug 12689562
1692: IF NOT g_rec_period_id.EXISTS(to_char(p_tim_id))
1706:
1707: END IF;
1708:
1709: if g_debug then
1710: hr_utility.set_location('hxt_time_summary.generate_details',30);
1711: hr_utility.trace('otl_recurring_period :'||otl_recurring_period);
1712: end if;
1713: --
1714: -- Now calculate the start day as per the otl recurring period
1707: END IF;
1708:
1709: if g_debug then
1710: hr_utility.set_location('hxt_time_summary.generate_details',30);
1711: hr_utility.trace('otl_recurring_period :'||otl_recurring_period);
1712: end if;
1713: --
1714: -- Now calculate the start day as per the otl recurring period
1715: --
1713: --
1714: -- Now calculate the start day as per the otl recurring period
1715: --
1716: if g_debug then
1717: hr_utility.set_location('hxt_time_summary.generate_details',40);
1718: end if;
1719: IF otl_recurring_period is NOT NULL THEN
1720: --
1721: -- Get the period start_date, period_type for the recurring_period
1720: --
1721: -- Get the period start_date, period_type for the recurring_period
1722: --
1723: if g_debug then
1724: hr_utility.set_location('hxt_time_summary.generate_details',50);
1725: end if;
1726: SELECT start_date,period_type
1727: INTO l_period_start_date,l_period_type
1728: FROM hxc_recurring_periods
1728: FROM hxc_recurring_periods
1729: WHERE recurring_period_id = otl_recurring_period;
1730:
1731: if g_debug then
1732: hr_utility.trace('l_period_start_date :'
1733: || to_char(l_period_start_date,'DD-MON-YYYY HH24:MI:SS'));
1734: hr_utility.trace('l_period_type :'|| l_period_type);
1735: hr_utility.trace('g_date_worked :'
1736: || to_char(g_date_worked,'DD-MON-YYYY HH24:MI:SS'));
1730:
1731: if g_debug then
1732: hr_utility.trace('l_period_start_date :'
1733: || to_char(l_period_start_date,'DD-MON-YYYY HH24:MI:SS'));
1734: hr_utility.trace('l_period_type :'|| l_period_type);
1735: hr_utility.trace('g_date_worked :'
1736: || to_char(g_date_worked,'DD-MON-YYYY HH24:MI:SS'));
1737: hr_utility.set_location('hxt_time_summary.generate_details',60);
1738: end if;
1731: if g_debug then
1732: hr_utility.trace('l_period_start_date :'
1733: || to_char(l_period_start_date,'DD-MON-YYYY HH24:MI:SS'));
1734: hr_utility.trace('l_period_type :'|| l_period_type);
1735: hr_utility.trace('g_date_worked :'
1736: || to_char(g_date_worked,'DD-MON-YYYY HH24:MI:SS'));
1737: hr_utility.set_location('hxt_time_summary.generate_details',60);
1738: end if;
1739: hxc_period_evaluation.period_start_stop
1733: || to_char(l_period_start_date,'DD-MON-YYYY HH24:MI:SS'));
1734: hr_utility.trace('l_period_type :'|| l_period_type);
1735: hr_utility.trace('g_date_worked :'
1736: || to_char(g_date_worked,'DD-MON-YYYY HH24:MI:SS'));
1737: hr_utility.set_location('hxt_time_summary.generate_details',60);
1738: end if;
1739: hxc_period_evaluation.period_start_stop
1740: (g_date_worked
1741: ,l_period_start_date
1743: ,l_period_end
1744: ,l_period_type);
1745:
1746: if g_debug then
1747: hr_utility.set_location('hxt_time_summary.generate_details',70);
1748: hr_utility.trace('l_period_start :'
1749: || to_char(l_period_start,'DD-MON-YYYY HH24:MI:SS'));
1750: hr_utility.trace('l_period_end :'
1751: || to_char(l_period_end,'DD-MON-YYYY HH24:MI:SS'));
1744: ,l_period_type);
1745:
1746: if g_debug then
1747: hr_utility.set_location('hxt_time_summary.generate_details',70);
1748: hr_utility.trace('l_period_start :'
1749: || to_char(l_period_start,'DD-MON-YYYY HH24:MI:SS'));
1750: hr_utility.trace('l_period_end :'
1751: || to_char(l_period_end,'DD-MON-YYYY HH24:MI:SS'));
1752: end if;
1746: if g_debug then
1747: hr_utility.set_location('hxt_time_summary.generate_details',70);
1748: hr_utility.trace('l_period_start :'
1749: || to_char(l_period_start,'DD-MON-YYYY HH24:MI:SS'));
1750: hr_utility.trace('l_period_end :'
1751: || to_char(l_period_end,'DD-MON-YYYY HH24:MI:SS'));
1752: end if;
1753: g_start_day_of_week := TO_CHAR(l_period_start,'DAY');
1754: if g_debug then
1751: || to_char(l_period_end,'DD-MON-YYYY HH24:MI:SS'));
1752: end if;
1753: g_start_day_of_week := TO_CHAR(l_period_start,'DAY');
1754: if g_debug then
1755: hr_utility.trace('g_start_day_of_week :'|| g_start_day_of_week);
1756: hr_utility.set_location('hxt_time_summary.generate_details',80);
1757: end if;
1758:
1759: -- Bug 11837942
1752: end if;
1753: g_start_day_of_week := TO_CHAR(l_period_start,'DAY');
1754: if g_debug then
1755: hr_utility.trace('g_start_day_of_week :'|| g_start_day_of_week);
1756: hr_utility.set_location('hxt_time_summary.generate_details',80);
1757: end if;
1758:
1759: -- Bug 11837942
1760: -- Added this construct here to store the previous id of the timecard
1774: g_sum_prev_ids(to_char(p_tim_id)) := l_prev_id;
1775:
1776: IF g_debug
1777: THEN
1778: hr_utility.trace('OTL : l_prev_id '||l_prev_id);
1779: END IF;
1780:
1781: END LOOP;
1782: CLOSE get_prev_ids;
1784:
1785:
1786: ELSE
1787: if g_debug then
1788: hr_utility.set_location('hxt_time_summary.generate_details',90);
1789: hr_utility.trace('p_PERIOD_START_DATE :'
1790: || to_char(p_PERIOD_START_DATE,'DD-MON-YYYY HH24:MI:SS'));
1791: end if;
1792: g_start_day_of_week := TO_CHAR(p_PERIOD_START_DATE,'DAY');
1785:
1786: ELSE
1787: if g_debug then
1788: hr_utility.set_location('hxt_time_summary.generate_details',90);
1789: hr_utility.trace('p_PERIOD_START_DATE :'
1790: || to_char(p_PERIOD_START_DATE,'DD-MON-YYYY HH24:MI:SS'));
1791: end if;
1792: g_start_day_of_week := TO_CHAR(p_PERIOD_START_DATE,'DAY');
1793: if g_debug then
1790: || to_char(p_PERIOD_START_DATE,'DD-MON-YYYY HH24:MI:SS'));
1791: end if;
1792: g_start_day_of_week := TO_CHAR(p_PERIOD_START_DATE,'DAY');
1793: if g_debug then
1794: hr_utility.trace('g_start_day_of_week :'|| g_start_day_of_week);
1795: end if;
1796: l_location := p_location||':GD';
1797: g_hol_location := l_location;
1798:
1798:
1799: END IF;
1800:
1801: if g_debug then
1802: hr_utility.set_location('hxt_time_summary.generate_details',100);
1803: end if;
1804:
1805: -- Bug 7359347
1806: -- Execute valid data and capture that.
1808:
1809: IF l_vd_retcode NOT IN (0,1) THEN
1810: -- check for time in/out and hour values
1811: if g_debug then
1812: hr_utility.set_location('hxt_time_summary.generate_details',110);
1813: end if;
1814: RETURN(2);
1815: ELSIF l_vd_retcode = 1 THEN
1816: if g_debug then
1813: end if;
1814: RETURN(2);
1815: ELSIF l_vd_retcode = 1 THEN
1816: if g_debug then
1817: hr_utility.set_location('hxt_time_summary.generate_details',120);
1818: end if;
1819: RETURN(11);
1820: END IF;
1821:
1820: END IF;
1821:
1822: IF g_element_type_id is not null THEN
1823: if g_debug then
1824: hr_utility.set_location('hxt_time_summary.generate_details',130);
1825: end if;
1826: -- Determine if earning is in assigned INCLUDE earning group
1827: g_include_yn := Get_Include(l_location
1828: ,g_egt_id
1830: ,g_date_worked);
1831:
1832: IF g_include_yn = 'E' THEN
1833: if g_debug then
1834: hr_utility.set_location('hxt_time_summary.generate_details',140);
1835: end if;
1836: RETURN 2;
1837:
1838: END IF;
1861: END IF;
1862:
1863: IF l_error_code < 2 THEN
1864: if g_debug then
1865: hr_utility.set_location('hxt_time_summary.generate_details',150);
1866: end if;
1867:
1868: /* Bug: 4489952 changes starts here */
1869:
1868: /* Bug: 4489952 changes starts here */
1869:
1870: IF check_non_explosion_entry (g_element_type_id, g_date_worked) THEN
1871: IF g_debug then
1872: hr_utility.set_location('hxt_time_summary.generate_details',151);
1873: END IF;
1874:
1875: insert_non_explodable_hrs;
1876:
1876:
1877: ELSE /* check_non_explosion_entry = FALSE */
1878:
1879: IF g_debug then
1880: hr_utility.set_location('hxt_time_summary.generate_details',155);
1881: END IF;
1882:
1883: rebuild_details( l_location, l_rebuild_code);
1884: END IF;
1888: END IF;
1889:
1890: IF l_rebuild_code <> 0 THEN
1891: if g_debug then
1892: hr_utility.set_location('hxt_time_summary.generate_details',160);
1893: end if;
1894: RETURN l_rebuild_code;
1895: ELSE
1896: if g_debug then
1893: end if;
1894: RETURN l_rebuild_code;
1895: ELSE
1896: if g_debug then
1897: hr_utility.set_location('hxt_time_summary.generate_details',170);
1898: end if;
1899: -- Bug 8600894
1900: IF NVL(FND_PROFILE.VALUE('HXT_HOLIDAY_EXPLOSION'),'EX') <> 'EX'
1901: THEN
1907: EXCEPTION
1908:
1909: WHEN OTHERS THEN
1910: if g_debug then
1911: hr_utility.set_location('hxt_time_summary.generate_details',180);
1912: end if;
1913: hr_utility.trace(dbms_utility.format_error_backtrace);
1914: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1915: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm);
1909: WHEN OTHERS THEN
1910: if g_debug then
1911: hr_utility.set_location('hxt_time_summary.generate_details',180);
1912: end if;
1913: hr_utility.trace(dbms_utility.format_error_backtrace);
1914: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1915: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm);
1916: --2278400 RETURN call_gen_error(l_location, '', sqlerrm);
1917: END; -- generate_details
1925: ,l_date_worked in date)
1926: IS
1927:
1928: BEGIN
1929: g_debug :=hr_utility.debug_enabled;
1930: if g_debug then
1931: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
1932: hr_utility.trace('ln_start :'||ln_start);
1933: hr_utility.trace('ln_stop :'||ln_stop);
1927:
1928: BEGIN
1929: g_debug :=hr_utility.debug_enabled;
1930: if g_debug then
1931: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
1932: hr_utility.trace('ln_start :'||ln_start);
1933: hr_utility.trace('ln_stop :'||ln_stop);
1934: hr_utility.trace('ln_carryover :'||ln_carryover);
1935: end if;
1928: BEGIN
1929: g_debug :=hr_utility.debug_enabled;
1930: if g_debug then
1931: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
1932: hr_utility.trace('ln_start :'||ln_start);
1933: hr_utility.trace('ln_stop :'||ln_stop);
1934: hr_utility.trace('ln_carryover :'||ln_carryover);
1935: end if;
1936: time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
1929: g_debug :=hr_utility.debug_enabled;
1930: if g_debug then
1931: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
1932: hr_utility.trace('ln_start :'||ln_start);
1933: hr_utility.trace('ln_stop :'||ln_stop);
1934: hr_utility.trace('ln_carryover :'||ln_carryover);
1935: end if;
1936: time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
1937: ||lpad(to_char(ln_start),4,'0')),'DDMMYYYYHH24MI');
1930: if g_debug then
1931: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
1932: hr_utility.trace('ln_start :'||ln_start);
1933: hr_utility.trace('ln_stop :'||ln_stop);
1934: hr_utility.trace('ln_carryover :'||ln_carryover);
1935: end if;
1936: time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
1937: ||lpad(to_char(ln_start),4,'0')),'DDMMYYYYHH24MI');
1938: if g_debug then
1935: end if;
1936: time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
1937: ||lpad(to_char(ln_start),4,'0')),'DDMMYYYYHH24MI');
1938: if g_debug then
1939: hr_utility.trace('time_in :'
1940: || to_char(time_in,'DD-MON-YYYY HH24:MI:SS'));
1941: end if;
1942: IF ln_start < ln_stop OR
1943: (ln_start = 0 AND
1942: IF ln_start < ln_stop OR
1943: (ln_start = 0 AND
1944: ln_stop = 0) THEN
1945: if g_debug then
1946: hr_utility.set_location('hxt_time_summary.time_in_dates',20);
1947: end if;
1948: time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
1949: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1950: if g_debug then
1947: end if;
1948: time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
1949: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1950: if g_debug then
1951: hr_utility.trace('time_out :'
1952: || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
1953: end if;
1954: ELSE --IF ln_start >= ln_stop
1955: if g_debug then
1952: || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
1953: end if;
1954: ELSE --IF ln_start >= ln_stop
1955: if g_debug then
1956: hr_utility.set_location('hxt_time_summary.time_in_dates',30);
1957: end if;
1958: time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1959: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1960: if g_debug then
1957: end if;
1958: time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1959: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1960: if g_debug then
1961: hr_utility.trace('time_out :'
1962: || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
1963: end if;
1964: END IF;
1965:
1964: END IF;
1965:
1966: IF ln_carryover is NOT NULL THEN
1967: if g_debug then
1968: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
1969: end if;
1970: IF ln_start < ln_carryover THEN
1971: if g_debug then
1972: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
1968: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
1969: end if;
1970: IF ln_start < ln_carryover THEN
1971: if g_debug then
1972: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
1973: end if;
1974: carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
1975: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1976:
1974: carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
1975: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1976:
1977: if g_debug then
1978: hr_utility.trace('carryover_time :'
1979: || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
1980: end if;
1981: ELSE --IF ln_start >= ln_carryover
1982: if g_debug then
1979: || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
1980: end if;
1981: ELSE --IF ln_start >= ln_carryover
1982: if g_debug then
1983: hr_utility.set_location('hxt_time_summary.time_in_dates',50);
1984: end if;
1985: carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1986: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1987:
1985: carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1986: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1987:
1988: if g_debug then
1989: hr_utility.trace('carryover_time :'
1990: || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
1991: end if;
1992: END IF;
1993:
2006:
2007: BEGIN
2008:
2009: if g_debug then
2010: hr_utility.set_location('hxt_time_summary.call_gen_error',10);
2011: end if;
2012: --Checking of the hours worked table for the current id is done so that
2013: --validation is not done on a record being deleted.
2014:
2022: ,g_EFFECTIVE_END_DATE
2023: ,'ERR');
2024:
2025: if g_debug then
2026: hr_utility.set_location('hxt_time_summary.call_gen_error',20);
2027: end if;
2028: RETURN 2;
2029:
2030: EXCEPTION
2030: EXCEPTION
2031:
2032: WHEN NO_DATA_FOUND THEN
2033: if g_debug then
2034: hr_utility.set_location('hxt_time_summary.call_gen_error',30);
2035: end if;
2036: RETURN 0;
2037:
2038: WHEN OTHERS THEN
2036: RETURN 0;
2037:
2038: WHEN OTHERS THEN
2039: if g_debug then
2040: hr_utility.set_location('hxt_time_summary.call_gen_error',40);
2041: end if;
2042: hxt_util.gen_error(g_tim_id
2043: ,g_id
2044: ,NULL
2048: ,g_EFFECTIVE_START_DATE
2049: ,g_EFFECTIVE_END_DATE
2050: ,'ERR');
2051: if g_debug then
2052: hr_utility.set_location('hxt_time_summary.call_gen_error',50);
2053: end if;
2054: hr_utility.trace(dbms_utility.format_error_backtrace);
2055: RETURN 2;
2056: END;
2050: ,'ERR');
2051: if g_debug then
2052: hr_utility.set_location('hxt_time_summary.call_gen_error',50);
2053: end if;
2054: hr_utility.trace(dbms_utility.format_error_backtrace);
2055: RETURN 2;
2056: END;
2057:
2058: FUNCTION call_hxthxc_gen_error
2069:
2070: BEGIN
2071:
2072: if g_debug then
2073: hr_utility.set_location('hxt_time_summary.call_gen_error',10);
2074: end if;
2075: --Checking of the hours worked table for the current id is done so that
2076: --validation is not done on a record being deleted.
2077:
2097: p_timecard_bb_ovn=> NULL,
2098: p_time_attribute_ovn=> NULL
2099: );
2100: if g_debug then
2101: hr_utility.trace('Adding to g_otm_messages'||p_msg_name);
2102: hr_utility.set_location('hxt_time_summary.call_gen_error',20);
2103: end if;
2104: RETURN 2;
2105:
2098: p_time_attribute_ovn=> NULL
2099: );
2100: if g_debug then
2101: hr_utility.trace('Adding to g_otm_messages'||p_msg_name);
2102: hr_utility.set_location('hxt_time_summary.call_gen_error',20);
2103: end if;
2104: RETURN 2;
2105:
2106: EXCEPTION
2106: EXCEPTION
2107:
2108: WHEN NO_DATA_FOUND THEN
2109: if g_debug then
2110: hr_utility.set_location('hxt_time_summary.call_gen_error',30);
2111: end if;
2112: RETURN 0;
2113:
2114: WHEN OTHERS THEN
2112: RETURN 0;
2113:
2114: WHEN OTHERS THEN
2115: if g_debug then
2116: hr_utility.set_location('hxt_time_summary.call_gen_error',40);
2117: end if;
2118: hxt_util.gen_error(g_tim_id
2119: ,g_id
2120: ,NULL
2138: p_time_attribute_ovn=> NULL
2139: );
2140:
2141: if g_debug then
2142: hr_utility.set_location('hxt_time_summary.call_gen_error',50);
2143: end if;
2144: hr_utility.trace(dbms_utility.format_error_backtrace);
2145: RETURN 2;
2146: END;
2140:
2141: if g_debug then
2142: hr_utility.set_location('hxt_time_summary.call_gen_error',50);
2143: end if;
2144: hr_utility.trace(dbms_utility.format_error_backtrace);
2145: RETURN 2;
2146: END;
2147:
2148: FUNCTION valid_data(p_location IN VARCHAR2) RETURN NUMBER IS
2166: error_code := hxt_tim_col_util.get_session_date(g_sum_session_date);
2167: END IF;
2168:
2169: if g_debug then
2170: hr_utility.set_location('hxt_time_summary.valid_data',10);
2171: end if;
2172: IF g_time_in IS NULL AND g_time_out IS NOT NULL THEN
2173:
2174: if g_debug then
2171: end if;
2172: IF g_time_in IS NULL AND g_time_out IS NOT NULL THEN
2173:
2174: if g_debug then
2175: hr_utility.set_location('hxt_time_summary.valid_data',20);
2176: end if;
2177: FND_MESSAGE.SET_NAME('HXT','HXT_39327_TIME_IN_OT_REQ');
2178: RETURN call_hxthxc_gen_error('HXT','HXT_39327_TIME_IN_OT_REQ',NULL,location, '');
2179:
2181:
2182: IF g_time_out IS NULL AND g_time_in IS NOT NULL THEN
2183:
2184: if g_debug then
2185: hr_utility.set_location('hxt_time_summary.valid_data',30);
2186: end if;
2187: FND_MESSAGE.SET_NAME('HXT','HXT_39327_TIME_IN_OT_REQ');
2188: RETURN call_hxthxc_gen_error('HXT','HXT_39327_TIME_IN_OT_REQ',NULL,location, '');
2189:
2191:
2192: IF g_time_in IS NULL AND g_hours IS NULL THEN
2193:
2194: if g_debug then
2195: hr_utility.set_location('hxt_time_summary.valid_data',40);
2196: end if;
2197: FND_MESSAGE.SET_NAME('HXT','HXT_39328_TIME_OR_TOT_HRS_REQ');
2198: RETURN call_hxthxc_gen_error('HXT','HXT_39328_TIME_OR_TOT_HRS_REQ',NULL,location, '');
2199:
2201:
2202: IF g_element_type_id IS NOT NULL THEN
2203:
2204: if g_debug then
2205: hr_utility.set_location('hxt_time_summary.valid_data',50);
2206: end if;
2207: l_cat := hxt_util.element_cat(g_element_type_id
2208: ,g_date_worked);
2209:
2211:
2212: -- Hours or times must be consistent across all summary records in a day
2213:
2214: BEGIN
2215: g_debug :=hr_utility.debug_enabled;
2216: if g_debug then
2217: hr_utility.set_location('hxt_time_summary.valid_data',60);
2218: end if;
2219:
2213:
2214: BEGIN
2215: g_debug :=hr_utility.debug_enabled;
2216: if g_debug then
2217: hr_utility.set_location('hxt_time_summary.valid_data',60);
2218: end if;
2219:
2220: /* Bug: 4489952 changes starts here */
2221:
2281:
2282: EXCEPTION
2283: WHEN NO_DATA_FOUND THEN
2284: if g_debug then
2285: hr_utility.set_location('hxt_time_summary.valid_data',90);
2286: end if;
2287: NULL;
2288: END;
2289:
2288: END;
2289:
2290: IF error_code = 1 THEN
2291: if g_debug then
2292: hr_utility.set_location('hxt_time_summary.valid_data',100);
2293: end if;
2294: FND_MESSAGE.SET_NAME('HXT','HXT_39329_INC_TIM_HR_ENTRIES');
2295:
2296: END IF;
2300: EXCEPTION
2301:
2302: WHEN OTHERS THEN
2303: if g_debug then
2304: hr_utility.set_location('hxt_time_summary.valid_data',110);
2305: end if;
2306: hr_utility.trace(dbms_utility.format_error_backtrace);
2307: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
2308: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm);
2302: WHEN OTHERS THEN
2303: if g_debug then
2304: hr_utility.set_location('hxt_time_summary.valid_data',110);
2305: end if;
2306: hr_utility.trace(dbms_utility.format_error_backtrace);
2307: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
2308: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm);
2309:
2310: END; -- valid_data
2323:
2324: BEGIN
2325:
2326: if g_debug then
2327: hr_utility.set_location('hxt_time_summary.Delete_Details',10);
2328: end if;
2329: p_error_code := 0;
2330:
2331: --Add local code to location variable
2332: l_location := p_location||':DD';
2333:
2334: IF nvl(g_DT_UPDATE_MODE, 'CORRECTION') = 'CORRECTION' THEN
2335: if g_debug then
2336: hr_utility.set_location('hxt_time_summary.Delete_Details',20);
2337: end if;
2338: DELETE from hxt_det_hours_worked_f
2339: WHERE parent_id = g_id;
2340:
2341: DELETE from hxt_errors_f where --SPR C153
2342: hrw_id = g_id; --SPR C153
2343: ELSE
2344: if g_debug then
2345: hr_utility.set_location('hxt_time_summary.Delete_Details',30);
2346: end if;
2347: -- Delete details for this entry and all others that follow
2348: -- on this day for this person that are non-absence or are in
2349: -- the person's include group
2379: EXCEPTION
2380:
2381: WHEN OTHERS THEN
2382: if g_debug then
2383: hr_utility.set_location('hxt_time_summary.Delete_Details',40);
2384: end if;
2385: hr_utility.trace(dbms_utility.format_error_backtrace);
2386: -- Write to error table - do not generate details
2387: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
2381: WHEN OTHERS THEN
2382: if g_debug then
2383: hr_utility.set_location('hxt_time_summary.Delete_Details',40);
2384: end if;
2385: hr_utility.trace(dbms_utility.format_error_backtrace);
2386: -- Write to error table - do not generate details
2387: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
2388: p_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm); -- HXT11
2389: --p_error_code := call_gen_error(l_location, '', sqlerrm); -- HXT11
2395:
2396: BEGIN
2397:
2398: if g_debug then
2399: hr_utility.set_location('hxt_time_summary.shift_adjust_times',10);
2400: end if;
2401: p_shift_adjusted_time_in := g_TIME_IN;
2402: p_shift_adjusted_time_out := g_TIME_OUT;
2403:
2402: p_shift_adjusted_time_out := g_TIME_OUT;
2403:
2404: IF (g_EARLY_START IS NOT NULL AND g_STANDARD_START IS NOT NULL) THEN
2405: if g_debug then
2406: hr_utility.set_location('hxt_time_summary.shift_adjust_times',20);
2407: end if;
2408: IF g_EARLY_START > g_STANDARD_START THEN -- spans midnight
2409: if g_debug then
2410: hr_utility.set_location('hxt_time_summary.shift_adjust_times',30);
2406: hr_utility.set_location('hxt_time_summary.shift_adjust_times',20);
2407: end if;
2408: IF g_EARLY_START > g_STANDARD_START THEN -- spans midnight
2409: if g_debug then
2410: hr_utility.set_location('hxt_time_summary.shift_adjust_times',30);
2411: end if;
2412: IF to_number(to_char(g_TIME_IN, 'HH24MI')) < g_STANDARD_START THEN
2413: if g_debug then
2414: hr_utility.set_location('hxt_time_summary.shift_adjust_times',40);
2410: hr_utility.set_location('hxt_time_summary.shift_adjust_times',30);
2411: end if;
2412: IF to_number(to_char(g_TIME_IN, 'HH24MI')) < g_STANDARD_START THEN
2413: if g_debug then
2414: hr_utility.set_location('hxt_time_summary.shift_adjust_times',40);
2415: end if;
2416: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
2417: - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
2418: ELSIF to_number(to_char(g_TIME_IN, 'HH24MI')) > g_EARLY_START THEN
2416: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
2417: - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
2418: ELSIF to_number(to_char(g_TIME_IN, 'HH24MI')) > g_EARLY_START THEN
2419: if g_debug then
2420: hr_utility.set_location('hxt_time_summary.shift_adjust_times',50);
2421: end if;
2422: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
2423: + (hxt_util.time_to_hours(2400) -
2424: hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI'))))) / 24);
2424: hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI'))))) / 24);
2425: END IF;
2426: ELSE -- no midnight span
2427: if g_debug then
2428: hr_utility.set_location('hxt_time_summary.shift_adjust_times',60);
2429: end if;
2430: IF to_number(to_char(g_TIME_IN, 'HH24MI')) BETWEEN g_EARLY_START
2431: AND g_STANDARD_START THEN
2432: if g_debug then
2429: end if;
2430: IF to_number(to_char(g_TIME_IN, 'HH24MI')) BETWEEN g_EARLY_START
2431: AND g_STANDARD_START THEN
2432: if g_debug then
2433: hr_utility.set_location('hxt_time_summary.shift_adjust_times',70);
2434: end if;
2435: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
2436: - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
2437: END IF;
2439: END IF;
2440:
2441: IF (g_LATE_STOP IS NOT NULL AND g_STANDARD_STOP IS NOT NULL) THEN
2442: if g_debug then
2443: hr_utility.set_location('hxt_time_summary.shift_adjust_times',80);
2444: end if;
2445: IF g_LATE_STOP < g_STANDARD_STOP THEN -- spans midnight
2446: if g_debug then
2447: hr_utility.set_location('hxt_time_summary.shift_adjust_times',90);
2443: hr_utility.set_location('hxt_time_summary.shift_adjust_times',80);
2444: end if;
2445: IF g_LATE_STOP < g_STANDARD_STOP THEN -- spans midnight
2446: if g_debug then
2447: hr_utility.set_location('hxt_time_summary.shift_adjust_times',90);
2448: end if;
2449: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) > g_STANDARD_STOP THEN
2450: if g_debug then
2451: hr_utility.set_location('hxt_time_summary.shift_adjust_times',100);
2447: hr_utility.set_location('hxt_time_summary.shift_adjust_times',90);
2448: end if;
2449: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) > g_STANDARD_STOP THEN
2450: if g_debug then
2451: hr_utility.set_location('hxt_time_summary.shift_adjust_times',100);
2452: end if;
2453: p_shift_adjusted_time_out := g_TIME_OUT -
2454: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
2455: - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
2454: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
2455: - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
2456: ELSIF to_number(to_char(g_TIME_OUT, 'HH24MI')) < g_LATE_STOP THEN
2457: if g_debug then
2458: hr_utility.set_location('hxt_time_summary.shift_adjust_times',110);
2459: end if;
2460: p_shift_adjusted_time_out := g_TIME_OUT -
2461: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
2462: + (hxt_util.time_to_hours(2400) - (hxt_util.time_to_hours(g_STANDARD_STOP)))) / 24);
2462: + (hxt_util.time_to_hours(2400) - (hxt_util.time_to_hours(g_STANDARD_STOP)))) / 24);
2463: END IF;
2464: ELSE -- no midnight span
2465: if g_debug then
2466: hr_utility.set_location('hxt_time_summary.shift_adjust_times',120);
2467: end if;
2468: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) BETWEEN g_STANDARD_STOP
2469: AND g_LATE_STOP THEN
2470: if g_debug then
2467: end if;
2468: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) BETWEEN g_STANDARD_STOP
2469: AND g_LATE_STOP THEN
2470: if g_debug then
2471: hr_utility.set_location('hxt_time_summary.shift_adjust_times',130);
2472: end if;
2473: p_shift_adjusted_time_out := g_TIME_OUT -
2474: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
2475: - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
2497:
2498: BEGIN
2499:
2500: if g_debug then
2501: hr_utility.set_location('hxt_time_summary.Rebuild_Details',10);
2502: end if;
2503: --Add local code to location variable
2504: l_location := p_location||':RB';
2505:
2512: ,l_hdy_id
2513: ,l_hours
2514: ,l_retcode);
2515: if g_debug then
2516: hr_utility.set_location('hxt_time_summary.Rebuild_Details',20);
2517: end if;
2518: IF l_retcode = 1 THEN
2519: if g_debug then
2520: hr_utility.set_location('hxt_time_summary.Rebuild_Details',30);
2516: hr_utility.set_location('hxt_time_summary.Rebuild_Details',20);
2517: end if;
2518: IF l_retcode = 1 THEN
2519: if g_debug then
2520: hr_utility.set_location('hxt_time_summary.Rebuild_Details',30);
2521: end if;
2522: g_hol_yn := 'Y';
2523: ELSE
2524: if g_debug then
2521: end if;
2522: g_hol_yn := 'Y';
2523: ELSE
2524: if g_debug then
2525: hr_utility.set_location('hxt_time_summary.Rebuild_Details',40);
2526: end if;
2527: g_hol_yn := 'N';
2528: END IF;
2529:
2530: l_error_code := Gen_Details(l_location, g_time_in, g_time_out);
2531:
2532: IF l_error_code <> 0 THEN
2533: if g_debug then
2534: hr_utility.set_location('hxt_time_summary.Rebuild_Details',50);
2535: end if;
2536: p_error_code := l_error_code;
2537: ELSE
2538: if g_debug then
2535: end if;
2536: p_error_code := l_error_code;
2537: ELSE
2538: if g_debug then
2539: hr_utility.set_location('hxt_time_summary.Rebuild_Details',60);
2540: end if;
2541: p_error_code := change_warning_flag;
2542: END IF;
2543:
2544: EXCEPTION
2545:
2546: WHEN OTHERS THEN
2547: if g_debug then
2548: hr_utility.set_location('hxt_time_summary.Rebuild_Details',70);
2549: end if;
2550: hr_utility.trace(dbms_utility.format_error_backtrace);
2551: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
2552: p_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm); -- HXT11
2546: WHEN OTHERS THEN
2547: if g_debug then
2548: hr_utility.set_location('hxt_time_summary.Rebuild_Details',70);
2549: end if;
2550: hr_utility.trace(dbms_utility.format_error_backtrace);
2551: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
2552: p_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm); -- HXT11
2553: --2278400 p_error_code := call_gen_error(l_location, '', sqlerrm); -- HXT11
2554: END; -- rebuild details
2686: ,l_date_worked in date)
2687: IS
2688:
2689: BEGIN
2690: g_debug :=hr_utility.debug_enabled;
2691: if g_debug then
2692: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
2693: hr_utility.trace('ln_start :'||ln_start);
2694: hr_utility.trace('ln_stop :'||ln_stop);
2688:
2689: BEGIN
2690: g_debug :=hr_utility.debug_enabled;
2691: if g_debug then
2692: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
2693: hr_utility.trace('ln_start :'||ln_start);
2694: hr_utility.trace('ln_stop :'||ln_stop);
2695: hr_utility.trace('ln_carryover :'||ln_carryover);
2696: end if;
2689: BEGIN
2690: g_debug :=hr_utility.debug_enabled;
2691: if g_debug then
2692: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
2693: hr_utility.trace('ln_start :'||ln_start);
2694: hr_utility.trace('ln_stop :'||ln_stop);
2695: hr_utility.trace('ln_carryover :'||ln_carryover);
2696: end if;
2697: time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
2690: g_debug :=hr_utility.debug_enabled;
2691: if g_debug then
2692: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
2693: hr_utility.trace('ln_start :'||ln_start);
2694: hr_utility.trace('ln_stop :'||ln_stop);
2695: hr_utility.trace('ln_carryover :'||ln_carryover);
2696: end if;
2697: time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
2698: ||lpad(to_char(ln_start),4,'0')),'DDMMYYYYHH24MI');
2691: if g_debug then
2692: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
2693: hr_utility.trace('ln_start :'||ln_start);
2694: hr_utility.trace('ln_stop :'||ln_stop);
2695: hr_utility.trace('ln_carryover :'||ln_carryover);
2696: end if;
2697: time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
2698: ||lpad(to_char(ln_start),4,'0')),'DDMMYYYYHH24MI');
2699: if g_debug then
2696: end if;
2697: time_in := to_date((to_char(l_date_worked,'DDMMYYYY')
2698: ||lpad(to_char(ln_start),4,'0')),'DDMMYYYYHH24MI');
2699: if g_debug then
2700: hr_utility.trace('time_in :'
2701: || to_char(time_in,'DD-MON-YYYY HH24:MI:SS'));
2702: end if;
2703: IF ln_start < ln_stop OR
2704: (ln_start = 0 AND
2703: IF ln_start < ln_stop OR
2704: (ln_start = 0 AND
2705: ln_stop = 0) THEN
2706: if g_debug then
2707: hr_utility.set_location('hxt_time_summary.time_in_dates',20);
2708: end if;
2709: time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
2710: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
2711: if g_debug then
2708: end if;
2709: time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
2710: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
2711: if g_debug then
2712: hr_utility.trace('time_out :'
2713: || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
2714: end if;
2715: ELSE --IF ln_start >= ln_stop
2716: if g_debug then
2713: || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
2714: end if;
2715: ELSE --IF ln_start >= ln_stop
2716: if g_debug then
2717: hr_utility.set_location('hxt_time_summary.time_in_dates',30);
2718: end if;
2719: time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
2720: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
2721: if g_debug then
2718: end if;
2719: time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
2720: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
2721: if g_debug then
2722: hr_utility.trace('time_out :'
2723: || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
2724: end if;
2725: END IF;
2726:
2725: END IF;
2726:
2727: IF ln_carryover is NOT NULL THEN
2728: if g_debug then
2729: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
2730: end if;
2731: IF ln_start < ln_carryover THEN
2732: if g_debug then
2733: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
2729: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
2730: end if;
2731: IF ln_start < ln_carryover THEN
2732: if g_debug then
2733: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
2734: end if;
2735: carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
2736: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
2737:
2735: carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
2736: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
2737:
2738: if g_debug then
2739: hr_utility.trace('carryover_time :'
2740: || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
2741: end if;
2742: ELSE --IF ln_start >= ln_carryover
2743: if g_debug then
2740: || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
2741: end if;
2742: ELSE --IF ln_start >= ln_carryover
2743: if g_debug then
2744: hr_utility.set_location('hxt_time_summary.time_in_dates',50);
2745: end if;
2746: carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
2747: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
2748:
2746: carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
2747: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
2748:
2749: if g_debug then
2750: hr_utility.trace('carryover_time :'
2751: || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
2752: end if;
2753: END IF;
2754:
2765:
2766: BEGIN
2767:
2768: if g_debug then
2769: hr_utility.set_location('hxt_time_summary.sort',10);
2770: end if;
2771: if segment_chunks.count > 0 then
2772: -- We do not need to copy each member of the array one by one.
2773: /*
2773: /*
2774: For i in segment_chunks.first .. segment_chunks.LAST
2775: Loop
2776: if g_debug then
2777: hr_utility.set_location('hxt_time_summary.sort',20);
2778: end if;
2779: sorted_chunks(i):= segment_chunks(i);
2780: End loop;
2781: */
2779: sorted_chunks(i):= segment_chunks(i);
2780: End loop;
2781: */
2782: if g_debug then
2783: hr_utility.set_location('hxt_time_summary.sort',20);
2784: end if;
2785: sorted_chunks := segment_chunks;
2786: end if;
2787: /*
2786: end if;
2787: /*
2788:
2789: if g_debug then
2790: hr_utility.trace('FYI');
2791: end if;
2792: if sorted_chunks.count <> 0 then
2793: if g_debug then
2794: hr_utility.set_location('hxt_time_summary.sort',30);
2790: hr_utility.trace('FYI');
2791: end if;
2792: if sorted_chunks.count <> 0 then
2793: if g_debug then
2794: hr_utility.set_location('hxt_time_summary.sort',30);
2795: end if;
2796: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2797: if g_debug then
2798: hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2794: hr_utility.set_location('hxt_time_summary.sort',30);
2795: end if;
2796: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2797: if g_debug then
2798: hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2799: end if;
2800: end loop;
2801: end if;
2802: if g_debug then
2799: end if;
2800: end loop;
2801: end if;
2802: if g_debug then
2803: hr_utility.trace('END FYI');
2804: end if;
2805:
2806: */
2807:
2805:
2806: */
2807:
2808: if g_debug then
2809: hr_utility.trace('FYI');
2810: if sorted_chunks.count <> 0 then
2811: hr_utility.set_location('hxt_time_summary.sort',30);
2812: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2813: hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2807:
2808: if g_debug then
2809: hr_utility.trace('FYI');
2810: if sorted_chunks.count <> 0 then
2811: hr_utility.set_location('hxt_time_summary.sort',30);
2812: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2813: hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2814: end loop;
2815: end if;
2809: hr_utility.trace('FYI');
2810: if sorted_chunks.count <> 0 then
2811: hr_utility.set_location('hxt_time_summary.sort',30);
2812: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2813: hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2814: end loop;
2815: end if;
2816: hr_utility.trace('END FYI');
2817: end if;
2812: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2813: hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2814: end loop;
2815: end if;
2816: hr_utility.trace('END FYI');
2817: end if;
2818:
2819:
2820:
2820:
2821: For i in sorted_chunks.First+1 .. sorted_chunks.LAST
2822: Loop
2823: if g_debug then
2824: hr_utility.set_location('hxt_time_summary.sort',40);
2825: end if;
2826: v_temp:= sorted_chunks(i);
2827: if g_debug then
2828: hr_utility.trace('v_temp :'||to_char(v_temp,'DD-MON-YYYY HH24:MI:SS'));
2824: hr_utility.set_location('hxt_time_summary.sort',40);
2825: end if;
2826: v_temp:= sorted_chunks(i);
2827: if g_debug then
2828: hr_utility.trace('v_temp :'||to_char(v_temp,'DD-MON-YYYY HH24:MI:SS'));
2829: end if;
2830: <
2831: if g_debug then
2832: hr_utility.set_location('hxt_time_summary.sort',50);
2828: hr_utility.trace('v_temp :'||to_char(v_temp,'DD-MON-YYYY HH24:MI:SS'));
2829: end if;
2830: <
2831: if g_debug then
2832: hr_utility.set_location('hxt_time_summary.sort',50);
2833: end if;
2834: For j in REVERSE sorted_chunks.First .. (i-1)
2835: Loop
2836: if g_debug then
2833: end if;
2834: For j in REVERSE sorted_chunks.First .. (i-1)
2835: Loop
2836: if g_debug then
2837: hr_utility.set_location('hxt_time_summary.sort',60);
2838: hr_utility.trace('sorted_chunks(j) :'||to_char(sorted_chunks(j),'DD-MON-YYYY HH24:MI:SS'));
2839: end if;
2840: If sorted_chunks(j) >= v_temp then
2841: if g_debug then
2834: For j in REVERSE sorted_chunks.First .. (i-1)
2835: Loop
2836: if g_debug then
2837: hr_utility.set_location('hxt_time_summary.sort',60);
2838: hr_utility.trace('sorted_chunks(j) :'||to_char(sorted_chunks(j),'DD-MON-YYYY HH24:MI:SS'));
2839: end if;
2840: If sorted_chunks(j) >= v_temp then
2841: if g_debug then
2842: hr_utility.set_location('hxt_time_summary.sort',70);
2838: hr_utility.trace('sorted_chunks(j) :'||to_char(sorted_chunks(j),'DD-MON-YYYY HH24:MI:SS'));
2839: end if;
2840: If sorted_chunks(j) >= v_temp then
2841: if g_debug then
2842: hr_utility.set_location('hxt_time_summary.sort',70);
2843: end if;
2844: sorted_chunks(j+1) := sorted_chunks(j);
2845: sorted_chunks(j) := v_temp;
2846: end if;
2844: sorted_chunks(j+1) := sorted_chunks(j);
2845: sorted_chunks(j) := v_temp;
2846: end if;
2847: if g_debug then
2848: hr_utility.set_location('hxt_time_summary.sort',80);
2849: end if;
2850: end loop inner_loop;
2851: if g_debug then
2852: hr_utility.set_location('hxt_time_summary.sort',90);
2848: hr_utility.set_location('hxt_time_summary.sort',80);
2849: end if;
2850: end loop inner_loop;
2851: if g_debug then
2852: hr_utility.set_location('hxt_time_summary.sort',90);
2853: end if;
2854: end loop;
2855: IF p_order ='ASC' then
2856: if g_debug then
2853: end if;
2854: end loop;
2855: IF p_order ='ASC' then
2856: if g_debug then
2857: hr_utility.set_location('hxt_time_summary.sort',100);
2858: end if;
2859: RETURN sorted_chunks;
2860: END IF;
2861: if g_debug then
2858: end if;
2859: RETURN sorted_chunks;
2860: END IF;
2861: if g_debug then
2862: hr_utility.set_location('hxt_time_summary.sort',110);
2863: end if;
2864: END;
2865:
2866: FUNCTION segment_earning (segment_start in date
2926:
2927:
2928: if g_debug then
2929:
2930: hr_utility.set_location('hxt_time_summary.check_eligibility',10);
2931: end if;
2932:
2933: IF (
2934: (lv_time_in <= lv_segment_start
2954: -- lv_time_in lv_segment_start | lv_segment_stop lv_time_out
2955: )
2956: THEN
2957: if g_debug then
2958: hr_utility.set_location('hxt_time_summary.check_eligibility',20);
2959: end if;
2960: RETURN TRUE;
2961: ELSE
2962: if g_debug then
2959: end if;
2960: RETURN TRUE;
2961: ELSE
2962: if g_debug then
2963: hr_utility.set_location('hxt_time_summary.check_eligibility',30);
2964: end if;
2965: RETURN FALSE;
2966: END IF;
2967: if g_debug then
2964: end if;
2965: RETURN FALSE;
2966: END IF;
2967: if g_debug then
2968: hr_utility.set_location('hxt_time_summary.check_eligibility',40);
2969: end if;
2970: END;
2971:
2972: ----------------------------segment_earning main module-------------------------
2974: BEGIN
2975:
2976: if g_debug then
2977: l_proc := 'hxt_time_summary.segment_earning';
2978: hr_utility.set_location(l_proc,10);
2979:
2980: -- hr_utility.trace('worked_time_in :'||worked_time_in);
2981: -- hr_utility.trace('worked_time_out :'||worked_time_out);
2982: -- hr_utility.trace('segment_start :'||segment_start);
2976: if g_debug then
2977: l_proc := 'hxt_time_summary.segment_earning';
2978: hr_utility.set_location(l_proc,10);
2979:
2980: -- hr_utility.trace('worked_time_in :'||worked_time_in);
2981: -- hr_utility.trace('worked_time_out :'||worked_time_out);
2982: -- hr_utility.trace('segment_start :'||segment_start);
2983: -- hr_utility.trace('segment_stop :'||segment_stop);
2984:
2977: l_proc := 'hxt_time_summary.segment_earning';
2978: hr_utility.set_location(l_proc,10);
2979:
2980: -- hr_utility.trace('worked_time_in :'||worked_time_in);
2981: -- hr_utility.trace('worked_time_out :'||worked_time_out);
2982: -- hr_utility.trace('segment_start :'||segment_start);
2983: -- hr_utility.trace('segment_stop :'||segment_stop);
2984:
2985: hr_utility.trace('worked_time_in :'
2978: hr_utility.set_location(l_proc,10);
2979:
2980: -- hr_utility.trace('worked_time_in :'||worked_time_in);
2981: -- hr_utility.trace('worked_time_out :'||worked_time_out);
2982: -- hr_utility.trace('segment_start :'||segment_start);
2983: -- hr_utility.trace('segment_stop :'||segment_stop);
2984:
2985: hr_utility.trace('worked_time_in :'
2986: ||to_char(worked_time_in,'DD-MON-YYYY HH24:MI:SS'));
2979:
2980: -- hr_utility.trace('worked_time_in :'||worked_time_in);
2981: -- hr_utility.trace('worked_time_out :'||worked_time_out);
2982: -- hr_utility.trace('segment_start :'||segment_start);
2983: -- hr_utility.trace('segment_stop :'||segment_stop);
2984:
2985: hr_utility.trace('worked_time_in :'
2986: ||to_char(worked_time_in,'DD-MON-YYYY HH24:MI:SS'));
2987: hr_utility.trace('worked_time_out :'
2981: -- hr_utility.trace('worked_time_out :'||worked_time_out);
2982: -- hr_utility.trace('segment_start :'||segment_start);
2983: -- hr_utility.trace('segment_stop :'||segment_stop);
2984:
2985: hr_utility.trace('worked_time_in :'
2986: ||to_char(worked_time_in,'DD-MON-YYYY HH24:MI:SS'));
2987: hr_utility.trace('worked_time_out :'
2988: ||to_char(worked_time_out,'DD-MON-YYYY HH24:MI:SS'));
2989: hr_utility.trace('segment_start :'
2983: -- hr_utility.trace('segment_stop :'||segment_stop);
2984:
2985: hr_utility.trace('worked_time_in :'
2986: ||to_char(worked_time_in,'DD-MON-YYYY HH24:MI:SS'));
2987: hr_utility.trace('worked_time_out :'
2988: ||to_char(worked_time_out,'DD-MON-YYYY HH24:MI:SS'));
2989: hr_utility.trace('segment_start :'
2990: ||to_char(segment_start,'DD-MON-YYYY HH24:MI:SS'));
2991: hr_utility.trace('segment_stop :'
2985: hr_utility.trace('worked_time_in :'
2986: ||to_char(worked_time_in,'DD-MON-YYYY HH24:MI:SS'));
2987: hr_utility.trace('worked_time_out :'
2988: ||to_char(worked_time_out,'DD-MON-YYYY HH24:MI:SS'));
2989: hr_utility.trace('segment_start :'
2990: ||to_char(segment_start,'DD-MON-YYYY HH24:MI:SS'));
2991: hr_utility.trace('segment_stop :'
2992: ||to_char(segment_stop,'DD-MON-YYYY HH24:MI:SS'));
2993: end if;
2987: hr_utility.trace('worked_time_out :'
2988: ||to_char(worked_time_out,'DD-MON-YYYY HH24:MI:SS'));
2989: hr_utility.trace('segment_start :'
2990: ||to_char(segment_start,'DD-MON-YYYY HH24:MI:SS'));
2991: hr_utility.trace('segment_stop :'
2992: ||to_char(segment_stop,'DD-MON-YYYY HH24:MI:SS'));
2993: end if;
2994: --First check whether employee worked for this chunk or not ,i.e., whether
2995: --this chunk is between the worked_time_in and worked_time_out.
3000: ,segment_start
3001: ,segment_stop) THEN
3002:
3003: if g_debug then
3004: hr_utility.set_location(l_proc,20);
3005: end if;
3006: -- Check whether eligible for shift override
3007: if g_debug then
3008: hr_utility.set_location(l_proc,30);
3004: hr_utility.set_location(l_proc,20);
3005: end if;
3006: -- Check whether eligible for shift override
3007: if g_debug then
3008: hr_utility.set_location(l_proc,30);
3009: end if;
3010: open get_work_plan;
3011: fetch get_work_plan into wp_start,wp_stop;
3012: if g_debug then
3009: end if;
3010: open get_work_plan;
3011: fetch get_work_plan into wp_start,wp_stop;
3012: if g_debug then
3013: hr_utility.trace('wp_start :'||wp_start);
3014: hr_utility.trace('wp_stop :'||wp_stop);
3015: end if;
3016: close get_work_plan;
3017:
3010: open get_work_plan;
3011: fetch get_work_plan into wp_start,wp_stop;
3012: if g_debug then
3013: hr_utility.trace('wp_start :'||wp_start);
3014: hr_utility.trace('wp_stop :'||wp_stop);
3015: end if;
3016: close get_work_plan;
3017:
3018: wp_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
3019:
3020: FOR i in 1 .. 3 LOOP
3021:
3022: if g_debug then
3023: hr_utility.set_location(l_proc,40);
3024: hr_utility.trace('wp_date_worked :'
3025: || to_char(wp_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3026: end if;
3027: time_in_dates(wp_start
3020: FOR i in 1 .. 3 LOOP
3021:
3022: if g_debug then
3023: hr_utility.set_location(l_proc,40);
3024: hr_utility.trace('wp_date_worked :'
3025: || to_char(wp_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3026: end if;
3027: time_in_dates(wp_start
3028: ,wp_stop
3032: ,ld_carryover1
3033: ,wp_date_worked);
3034:
3035: if g_debug then
3036: hr_utility.set_location(l_proc,50);
3037: hr_utility.trace('ld_wp_start :'
3038: || to_char(ld_wp_start,'DD-MON-YYYY HH24:MI:SS'));
3039: hr_utility.trace('ld_wp_stop :'
3040: || to_char(ld_wp_stop,'DD-MON-YYYY HH24:MI:SS'));
3033: ,wp_date_worked);
3034:
3035: if g_debug then
3036: hr_utility.set_location(l_proc,50);
3037: hr_utility.trace('ld_wp_start :'
3038: || to_char(ld_wp_start,'DD-MON-YYYY HH24:MI:SS'));
3039: hr_utility.trace('ld_wp_stop :'
3040: || to_char(ld_wp_stop,'DD-MON-YYYY HH24:MI:SS'));
3041: hr_utility.trace('ld_carryover1:'
3035: if g_debug then
3036: hr_utility.set_location(l_proc,50);
3037: hr_utility.trace('ld_wp_start :'
3038: || to_char(ld_wp_start,'DD-MON-YYYY HH24:MI:SS'));
3039: hr_utility.trace('ld_wp_stop :'
3040: || to_char(ld_wp_stop,'DD-MON-YYYY HH24:MI:SS'));
3041: hr_utility.trace('ld_carryover1:'
3042: || to_char(ld_carryover1,'DD-MON-YYYY HH24:MI:SS'));
3043: end if;
3037: hr_utility.trace('ld_wp_start :'
3038: || to_char(ld_wp_start,'DD-MON-YYYY HH24:MI:SS'));
3039: hr_utility.trace('ld_wp_stop :'
3040: || to_char(ld_wp_stop,'DD-MON-YYYY HH24:MI:SS'));
3041: hr_utility.trace('ld_carryover1:'
3042: || to_char(ld_carryover1,'DD-MON-YYYY HH24:MI:SS'));
3043: end if;
3044: IF g_sdovr_id is NOT NULL THEN
3045:
3043: end if;
3044: IF g_sdovr_id is NOT NULL THEN
3045:
3046: if g_debug then
3047: hr_utility.set_location(l_proc,60);
3048:
3049: hr_utility.trace('segment_start :'
3050: ||to_char(segment_start,'DD-MON-YYYY HH24:MI:SS'));
3051: hr_utility.trace('segment_stop :'
3045:
3046: if g_debug then
3047: hr_utility.set_location(l_proc,60);
3048:
3049: hr_utility.trace('segment_start :'
3050: ||to_char(segment_start,'DD-MON-YYYY HH24:MI:SS'));
3051: hr_utility.trace('segment_stop :'
3052: ||to_char(segment_stop,'DD-MON-YYYY HH24:MI:SS'));
3053: end if;
3047: hr_utility.set_location(l_proc,60);
3048:
3049: hr_utility.trace('segment_start :'
3050: ||to_char(segment_start,'DD-MON-YYYY HH24:MI:SS'));
3051: hr_utility.trace('segment_stop :'
3052: ||to_char(segment_stop,'DD-MON-YYYY HH24:MI:SS'));
3053: end if;
3054: IF check_eligibility(ld_wp_start
3055: ,ld_wp_stop
3055: ,ld_wp_stop
3056: ,segment_start
3057: ,segment_stop) THEN
3058: if g_debug then
3059: hr_utility.set_location(l_proc,70);
3060: end if;
3061: -- the chunk falls within the regular shift.
3062: -- So , not eligible for shift override.
3063: -- sdovr_earning_type := null;
3062: -- So , not eligible for shift override.
3063: -- sdovr_earning_type := null;
3064: elig_for_sdovr := 'N';
3065: if g_debug then
3066: hr_utility.trace('sdovr_earning_type :'||sdovr_earning_type);
3067: end if;
3068: ELSE
3069: if g_debug then
3070: hr_utility.set_location(l_proc,80);
3066: hr_utility.trace('sdovr_earning_type :'||sdovr_earning_type);
3067: end if;
3068: ELSE
3069: if g_debug then
3070: hr_utility.set_location(l_proc,80);
3071: end if;
3072: -- sdovr_earning_type := g_sdovr_id;
3073: --if g_debug then
3074: -- hr_utility.trace('sdovr_earning_type :'||sdovr_earning_type);
3070: hr_utility.set_location(l_proc,80);
3071: end if;
3072: -- sdovr_earning_type := g_sdovr_id;
3073: --if g_debug then
3074: -- hr_utility.trace('sdovr_earning_type :'||sdovr_earning_type);
3075: --end if;
3076: elig_for_sdovr := 'Y';
3077: END IF;
3078:
3076: elig_for_sdovr := 'Y';
3077: END IF;
3078:
3079: if g_debug then
3080: hr_utility.set_location(l_proc,90);
3081: end if;
3082:
3083: -- Check if eligible before midnight
3084: IF i = 1 THEN
3082:
3083: -- Check if eligible before midnight
3084: IF i = 1 THEN
3085: if g_debug then
3086: hr_utility.set_location(l_proc,100);
3087: end if;
3088: IF elig_for_sdovr = 'Y' THEN
3089: if g_debug then
3090: hr_utility.set_location(l_proc,110);
3086: hr_utility.set_location(l_proc,100);
3087: end if;
3088: IF elig_for_sdovr = 'Y' THEN
3089: if g_debug then
3090: hr_utility.set_location(l_proc,110);
3091: end if;
3092: elig_for_sdovr1 := 'Y';
3093: END IF;
3094: if g_debug then
3091: end if;
3092: elig_for_sdovr1 := 'Y';
3093: END IF;
3094: if g_debug then
3095: hr_utility.set_location(l_proc,120);
3096: end if;
3097: END IF;
3098:
3099: -- Now Check if eligible on current day
3098:
3099: -- Now Check if eligible on current day
3100: IF i = 2 THEN
3101: if g_debug then
3102: hr_utility.set_location(l_proc,130);
3103: end if;
3104: IF elig_for_sdovr = 'Y' THEN
3105: if g_debug then
3106: hr_utility.set_location(l_proc,140);
3102: hr_utility.set_location(l_proc,130);
3103: end if;
3104: IF elig_for_sdovr = 'Y' THEN
3105: if g_debug then
3106: hr_utility.set_location(l_proc,140);
3107: end if;
3108: elig_for_sdovr2 := 'Y';
3109: END IF;
3110: if g_debug then
3107: end if;
3108: elig_for_sdovr2 := 'Y';
3109: END IF;
3110: if g_debug then
3111: hr_utility.set_location(l_proc,145);
3112: end if;
3113: END IF;
3114:
3115: -- Now Check if eligible after midnight
3114:
3115: -- Now Check if eligible after midnight
3116: IF i = 3 THEN
3117: if g_debug then
3118: hr_utility.set_location(l_proc,150);
3119: end if;
3120: IF elig_for_sdovr = 'Y' THEN
3121: if g_debug then
3122: hr_utility.set_location(l_proc,160);
3118: hr_utility.set_location(l_proc,150);
3119: end if;
3120: IF elig_for_sdovr = 'Y' THEN
3121: if g_debug then
3122: hr_utility.set_location(l_proc,160);
3123: end if;
3124: elig_for_sdovr3 := 'Y';
3125: END IF;
3126: if g_debug then
3123: end if;
3124: elig_for_sdovr3 := 'Y';
3125: END IF;
3126: if g_debug then
3127: hr_utility.set_location(l_proc,170);
3128: end if;
3129: END IF;
3130:
3131: -- If eligible for both before,current day and after the midnight then
3132: -- pay the shift diff override
3133: IF elig_for_sdovr1= 'Y' and elig_for_sdovr2 = 'Y' and
3134: elig_for_sdovr3 = 'Y' THEN
3135: if g_debug then
3136: hr_utility.set_location(l_proc,180);
3137: end if;
3138: sdovr_earning_type := g_sdovr_id;
3139: ELSE
3140: if g_debug then
3137: end if;
3138: sdovr_earning_type := g_sdovr_id;
3139: ELSE
3140: if g_debug then
3141: hr_utility.set_location(l_proc,190);
3142: end if;
3143: sdovr_earning_type := null;
3144: END IF;
3145:
3143: sdovr_earning_type := null;
3144: END IF;
3145:
3146: if g_debug then
3147: hr_utility.set_location(l_proc,200);
3148: end if;
3149: wp_date_worked := TRUNC(wp_date_worked + 1, 'DD');
3150:
3151: END IF;
3150:
3151: END IF;
3152:
3153: if g_debug then
3154: hr_utility.trace('sdovr_earning_type :'|| sdovr_earning_type);
3155: hr_utility.set_location(l_proc,210);
3156: end if;
3157: END LOOP;
3158:
3151: END IF;
3152:
3153: if g_debug then
3154: hr_utility.trace('sdovr_earning_type :'|| sdovr_earning_type);
3155: hr_utility.set_location(l_proc,210);
3156: end if;
3157: END LOOP;
3158:
3159: if g_debug then
3156: end if;
3157: END LOOP;
3158:
3159: if g_debug then
3160: hr_utility.set_location(l_proc,215);
3161: hr_utility.trace('sdovr_earning_type :'|| sdovr_earning_type);
3162: hr_utility.trace('sdp_earning_type :'|| sdp_earning_type);
3163: end if;
3164: -- BUG 2721493
3157: END LOOP;
3158:
3159: if g_debug then
3160: hr_utility.set_location(l_proc,215);
3161: hr_utility.trace('sdovr_earning_type :'|| sdovr_earning_type);
3162: hr_utility.trace('sdp_earning_type :'|| sdp_earning_type);
3163: end if;
3164: -- BUG 2721493
3165: -- Now before proceeding to calculate the shift diff premium
3158:
3159: if g_debug then
3160: hr_utility.set_location(l_proc,215);
3161: hr_utility.trace('sdovr_earning_type :'|| sdovr_earning_type);
3162: hr_utility.trace('sdp_earning_type :'|| sdp_earning_type);
3163: end if;
3164: -- BUG 2721493
3165: -- Now before proceeding to calculate the shift diff premium
3166: -- check that the employee is not eligible for any shift diff override.
3170:
3171: IF sdovr_earning_type IS NULL THEN
3172:
3173: if g_debug then
3174: hr_utility.set_location(l_proc,230);
3175: end if;
3176: -- Check whether eligible for shift diff premium
3177: open sd_earning;
3178:
3177: open sd_earning;
3178:
3179: LOOP
3180: if g_debug then
3181: hr_utility.set_location(l_proc,240);
3182: end if;
3183: fetch sd_earning into sdp_start,sdp_stop,sdp_carryover,sdp_earning;
3184:
3185: Exit when sd_earning%NOTFOUND;
3184:
3185: Exit when sd_earning%NOTFOUND;
3186:
3187: if g_debug then
3188: hr_utility.trace('sdp_start :'||sdp_start);
3189: hr_utility.trace('sdp_stop :'||sdp_stop);
3190: hr_utility.trace('sdp_carryover :'||sdp_carryover);
3191: hr_utility.trace('sdp_earning:'||sdp_earning);
3192: hr_utility.trace('sdp_earning_type:'||sdp_earning_type);
3185: Exit when sd_earning%NOTFOUND;
3186:
3187: if g_debug then
3188: hr_utility.trace('sdp_start :'||sdp_start);
3189: hr_utility.trace('sdp_stop :'||sdp_stop);
3190: hr_utility.trace('sdp_carryover :'||sdp_carryover);
3191: hr_utility.trace('sdp_earning:'||sdp_earning);
3192: hr_utility.trace('sdp_earning_type:'||sdp_earning_type);
3193:
3186:
3187: if g_debug then
3188: hr_utility.trace('sdp_start :'||sdp_start);
3189: hr_utility.trace('sdp_stop :'||sdp_stop);
3190: hr_utility.trace('sdp_carryover :'||sdp_carryover);
3191: hr_utility.trace('sdp_earning:'||sdp_earning);
3192: hr_utility.trace('sdp_earning_type:'||sdp_earning_type);
3193:
3194: hr_utility.trace('p_shift_adjusted_time_in :'
3187: if g_debug then
3188: hr_utility.trace('sdp_start :'||sdp_start);
3189: hr_utility.trace('sdp_stop :'||sdp_stop);
3190: hr_utility.trace('sdp_carryover :'||sdp_carryover);
3191: hr_utility.trace('sdp_earning:'||sdp_earning);
3192: hr_utility.trace('sdp_earning_type:'||sdp_earning_type);
3193:
3194: hr_utility.trace('p_shift_adjusted_time_in :'
3195: ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
3188: hr_utility.trace('sdp_start :'||sdp_start);
3189: hr_utility.trace('sdp_stop :'||sdp_stop);
3190: hr_utility.trace('sdp_carryover :'||sdp_carryover);
3191: hr_utility.trace('sdp_earning:'||sdp_earning);
3192: hr_utility.trace('sdp_earning_type:'||sdp_earning_type);
3193:
3194: hr_utility.trace('p_shift_adjusted_time_in :'
3195: ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
3196: hr_utility.trace('p_shift_adjusted_time_out:'
3190: hr_utility.trace('sdp_carryover :'||sdp_carryover);
3191: hr_utility.trace('sdp_earning:'||sdp_earning);
3192: hr_utility.trace('sdp_earning_type:'||sdp_earning_type);
3193:
3194: hr_utility.trace('p_shift_adjusted_time_in :'
3195: ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
3196: hr_utility.trace('p_shift_adjusted_time_out:'
3197: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
3198: end if;
3192: hr_utility.trace('sdp_earning_type:'||sdp_earning_type);
3193:
3194: hr_utility.trace('p_shift_adjusted_time_in :'
3195: ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
3196: hr_utility.trace('p_shift_adjusted_time_out:'
3197: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
3198: end if;
3199: lv_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
3200:
3200:
3201: -- Loop through for the day before, the current day and the day after
3202: FOR i in 1 .. 3 LOOP
3203: if g_debug then
3204: hr_utility.set_location(l_proc,250);
3205: hr_utility.trace('lv_date_worked :'
3206: ||to_char(lv_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3207: end if;
3208: time_in_dates(sdp_start
3201: -- Loop through for the day before, the current day and the day after
3202: FOR i in 1 .. 3 LOOP
3203: if g_debug then
3204: hr_utility.set_location(l_proc,250);
3205: hr_utility.trace('lv_date_worked :'
3206: ||to_char(lv_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3207: end if;
3208: time_in_dates(sdp_start
3209: ,sdp_stop
3214: ,lv_date_worked
3215: );
3216:
3217: if g_debug then
3218: hr_utility.trace('sdp_start_time :'
3219: || to_char(sdp_start_time,'DD-MON-YYYY HH24:MI:SS'));
3220: hr_utility.trace('sdp_stop_time :'
3221: || to_char(sdp_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3222: hr_utility.trace('sdp_carryover_time :'
3216:
3217: if g_debug then
3218: hr_utility.trace('sdp_start_time :'
3219: || to_char(sdp_start_time,'DD-MON-YYYY HH24:MI:SS'));
3220: hr_utility.trace('sdp_stop_time :'
3221: || to_char(sdp_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3222: hr_utility.trace('sdp_carryover_time :'
3223: || to_char(sdp_carryover_time,'DD-MON-YYYY HH24:MI:SS'));
3224: hr_utility.set_location(l_proc,260);
3218: hr_utility.trace('sdp_start_time :'
3219: || to_char(sdp_start_time,'DD-MON-YYYY HH24:MI:SS'));
3220: hr_utility.trace('sdp_stop_time :'
3221: || to_char(sdp_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3222: hr_utility.trace('sdp_carryover_time :'
3223: || to_char(sdp_carryover_time,'DD-MON-YYYY HH24:MI:SS'));
3224: hr_utility.set_location(l_proc,260);
3225:
3226: hr_utility.trace('g_sdf_rule_completed :'
3220: hr_utility.trace('sdp_stop_time :'
3221: || to_char(sdp_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3222: hr_utility.trace('sdp_carryover_time :'
3223: || to_char(sdp_carryover_time,'DD-MON-YYYY HH24:MI:SS'));
3224: hr_utility.set_location(l_proc,260);
3225:
3226: hr_utility.trace('g_sdf_rule_completed :'
3227: || g_sdf_rule_completed);
3228: hr_utility.trace('g_sdf_carryover :'
3222: hr_utility.trace('sdp_carryover_time :'
3223: || to_char(sdp_carryover_time,'DD-MON-YYYY HH24:MI:SS'));
3224: hr_utility.set_location(l_proc,260);
3225:
3226: hr_utility.trace('g_sdf_rule_completed :'
3227: || g_sdf_rule_completed);
3228: hr_utility.trace('g_sdf_carryover :'
3229: || to_char(g_sdf_carryover,'DD-MON-YYYY HH24:MI:SS'));
3230: end if;
3224: hr_utility.set_location(l_proc,260);
3225:
3226: hr_utility.trace('g_sdf_rule_completed :'
3227: || g_sdf_rule_completed);
3228: hr_utility.trace('g_sdf_carryover :'
3229: || to_char(g_sdf_carryover,'DD-MON-YYYY HH24:MI:SS'));
3230: end if;
3231: IF g_sdf_rule_completed = 'Y' THEN
3232: if g_debug then
3229: || to_char(g_sdf_carryover,'DD-MON-YYYY HH24:MI:SS'));
3230: end if;
3231: IF g_sdf_rule_completed = 'Y' THEN
3232: if g_debug then
3233: hr_utility.set_location(l_proc,270);
3234: end if;
3235: if segment_start >= sdp_start_time and
3236: segment_start < sdp_stop_time then
3237: if g_debug then
3234: end if;
3235: if segment_start >= sdp_start_time and
3236: segment_start < sdp_stop_time then
3237: if g_debug then
3238: hr_utility.set_location(l_proc,280);
3239: end if;
3240: sdp_earning_type := sdp_earning;
3241: sdp_earning_found := 'Y';
3242:
3241: sdp_earning_found := 'Y';
3242:
3243: if segment_stop = sdp_carryover_time then
3244: if g_debug then
3245: hr_utility.set_location(l_proc,290);
3246: end if;
3247: g_sdf_rule_completed := 'Y';
3248: g_sdf_carryover := null;
3249: if g_debug then
3246: end if;
3247: g_sdf_rule_completed := 'Y';
3248: g_sdf_carryover := null;
3249: if g_debug then
3250: hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3251: end if;
3252: else
3253: if g_debug then
3254: hr_utility.set_location(l_proc,300);
3250: hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3251: end if;
3252: else
3253: if g_debug then
3254: hr_utility.set_location(l_proc,300);
3255: end if;
3256: g_sdf_rule_completed := 'N';
3257: g_sdf_carryover := sdp_carryover_time;
3258: if g_debug then
3255: end if;
3256: g_sdf_rule_completed := 'N';
3257: g_sdf_carryover := sdp_carryover_time;
3258: if g_debug then
3259: hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3260: hr_utility.set_location(l_proc,310);
3261: end if;
3262: end if;
3263:
3256: g_sdf_rule_completed := 'N';
3257: g_sdf_carryover := sdp_carryover_time;
3258: if g_debug then
3259: hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3260: hr_utility.set_location(l_proc,310);
3261: end if;
3262: end if;
3263:
3264: if g_debug then
3261: end if;
3262: end if;
3263:
3264: if g_debug then
3265: hr_utility.set_location(l_proc,320);
3266: end if;
3267: exit;
3268: else
3269: if g_debug then
3266: end if;
3267: exit;
3268: else
3269: if g_debug then
3270: hr_utility.set_location(l_proc,330);
3271: end if;
3272: sdp_earning_type := null;
3273: end if;
3274:
3272: sdp_earning_type := null;
3273: end if;
3274:
3275: if g_debug then
3276: hr_utility.set_location(l_proc,340);
3277: end if;
3278: ELSIF g_sdf_rule_completed = 'N' THEN
3279:
3280: if g_debug then
3277: end if;
3278: ELSIF g_sdf_rule_completed = 'N' THEN
3279:
3280: if g_debug then
3281: hr_utility.set_location(l_proc,350);
3282: end if;
3283: if g_sdf_carryover = sdp_carryover_time then
3284:
3285: if g_debug then
3282: end if;
3283: if g_sdf_carryover = sdp_carryover_time then
3284:
3285: if g_debug then
3286: hr_utility.set_location(l_proc,360);
3287: end if;
3288: sdp_earning_type := sdp_earning;
3289: sdp_earning_found := 'Y';
3290:
3289: sdp_earning_found := 'Y';
3290:
3291: if segment_stop = sdp_carryover_time then
3292: if g_debug then
3293: hr_utility.set_location(l_proc,370);
3294: end if;
3295: g_sdf_rule_completed := 'Y';
3296: g_sdf_carryover := null;
3297: if g_debug then
3294: end if;
3295: g_sdf_rule_completed := 'Y';
3296: g_sdf_carryover := null;
3297: if g_debug then
3298: hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3299: end if;
3300: else
3301: if g_debug then
3302: hr_utility.set_location(l_proc,380);
3298: hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3299: end if;
3300: else
3301: if g_debug then
3302: hr_utility.set_location(l_proc,380);
3303: end if;
3304: g_sdf_rule_completed := 'N';
3305: if g_debug then
3306: hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3302: hr_utility.set_location(l_proc,380);
3303: end if;
3304: g_sdf_rule_completed := 'N';
3305: if g_debug then
3306: hr_utility.trace('g_sdf_carryover :'||g_sdf_carryover);
3307: end if;
3308: end if;
3309:
3310: if g_debug then
3307: end if;
3308: end if;
3309:
3310: if g_debug then
3311: hr_utility.set_location(l_proc,390);
3312: end if;
3313: exit;
3314: else
3315: if g_debug then
3312: end if;
3313: exit;
3314: else
3315: if g_debug then
3316: hr_utility.set_location(l_proc,400);
3317: end if;
3318: sdp_earning_type := null;
3319: end if;
3320:
3318: sdp_earning_type := null;
3319: end if;
3320:
3321: if g_debug then
3322: hr_utility.set_location(l_proc,410);
3323: end if;
3324: END IF;
3325:
3326: lv_date_worked := TRUNC(lv_date_worked + 1, 'DD');
3325:
3326: lv_date_worked := TRUNC(lv_date_worked + 1, 'DD');
3327:
3328: if g_debug then
3329: hr_utility.set_location(l_proc,420);
3330: end if;
3331: END LOOP;
3332:
3333: if g_debug then
3330: end if;
3331: END LOOP;
3332:
3333: if g_debug then
3334: hr_utility.set_location(l_proc,430);
3335: end if;
3336: IF sdp_earning_found = 'Y' THEN
3337: if g_debug then
3338: hr_utility.set_location(l_proc,440);
3334: hr_utility.set_location(l_proc,430);
3335: end if;
3336: IF sdp_earning_found = 'Y' THEN
3337: if g_debug then
3338: hr_utility.set_location(l_proc,440);
3339: end if;
3340: EXIT;
3341: END IF;
3342: if g_debug then
3339: end if;
3340: EXIT;
3341: END IF;
3342: if g_debug then
3343: hr_utility.set_location(l_proc,450);
3344: end if;
3345: END LOOP;
3346:
3347: if g_debug then
3344: end if;
3345: END LOOP;
3346:
3347: if g_debug then
3348: hr_utility.set_location(l_proc,460);
3349: end if;
3350: close sd_earning;
3351:
3352: ELSE
3351:
3352: ELSE
3353:
3354: if g_debug then
3355: hr_utility.set_location(l_proc,465);
3356: end if;
3357: sdp_earning_type := null;
3358:
3359: END IF;
3358:
3359: END IF;
3360:
3361: if g_debug then
3362: hr_utility.set_location(l_proc,470);
3363: end if;
3364: RETURN TRUE;
3365:
3366: ELSE
3364: RETURN TRUE;
3365:
3366: ELSE
3367: if g_debug then
3368: hr_utility.set_location(l_proc,480);
3369: end if;
3370: RETURN FALSE;
3371: END IF; -- check_eligibility
3372:
3371: END IF; -- check_eligibility
3372:
3373: if g_debug then
3374:
3375: hr_utility.set_location(l_proc,490);
3376: end if;
3377: END; -- segment_earning
3378:
3379: FUNCTION segment_start_in_rule
3408:
3409: BEGIN
3410:
3411: if g_debug then
3412: hr_utility.set_location('hxt_time_summary.segment_start_in_rule',10);
3413: end if;
3414: OPEN sd_rules;
3415: FETCH sd_rules INTO p_rule_earning_type, p_carryover;
3416: if g_debug then
3413: end if;
3414: OPEN sd_rules;
3415: FETCH sd_rules INTO p_rule_earning_type, p_carryover;
3416: if g_debug then
3417: hr_utility.trace('p_rule_earning_type :'||p_rule_earning_type);
3418: hr_utility.trace('p_carryover :'||p_carryover);
3419: end if;
3420: IF sd_rules%NOTFOUND THEN
3421: if g_debug then
3414: OPEN sd_rules;
3415: FETCH sd_rules INTO p_rule_earning_type, p_carryover;
3416: if g_debug then
3417: hr_utility.trace('p_rule_earning_type :'||p_rule_earning_type);
3418: hr_utility.trace('p_carryover :'||p_carryover);
3419: end if;
3420: IF sd_rules%NOTFOUND THEN
3421: if g_debug then
3422: hr_utility.set_location('hxt_time_summary.segment_start_in_rule',20);
3418: hr_utility.trace('p_carryover :'||p_carryover);
3419: end if;
3420: IF sd_rules%NOTFOUND THEN
3421: if g_debug then
3422: hr_utility.set_location('hxt_time_summary.segment_start_in_rule',20);
3423: end if;
3424: CLOSE sd_rules;
3425: RETURN FALSE;
3426: END IF;
3424: CLOSE sd_rules;
3425: RETURN FALSE;
3426: END IF;
3427: if g_debug then
3428: hr_utility.set_location('hxt_time_summary.segment_start_in_rule',30);
3429: end if;
3430: CLOSE sd_rules;
3431: RETURN TRUE;
3432:
3459:
3460: BEGIN
3461:
3462: if g_debug then
3463: hr_utility.set_location('hxt_time_summary.rule_start_in_segment',10);
3464: end if;
3465: OPEN sd_rules;
3466: FETCH sd_rules INTO p_rule_start;
3467: if g_debug then
3464: end if;
3465: OPEN sd_rules;
3466: FETCH sd_rules INTO p_rule_start;
3467: if g_debug then
3468: hr_utility.trace('p_rule_start :'||p_rule_start);
3469: end if;
3470: IF sd_rules%NOTFOUND THEN
3471: if g_debug then
3472: hr_utility.set_location('hxt_time_summary.rule_start_in_segment',20);
3468: hr_utility.trace('p_rule_start :'||p_rule_start);
3469: end if;
3470: IF sd_rules%NOTFOUND THEN
3471: if g_debug then
3472: hr_utility.set_location('hxt_time_summary.rule_start_in_segment',20);
3473: end if;
3474: CLOSE sd_rules;
3475: RETURN FALSE;
3476: END IF;
3495:
3496: BEGIN
3497:
3498: if g_debug then
3499: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',10);
3500: end if;
3501: p_segment_stop_time := NULL;
3502: if g_debug then
3503: hr_utility.trace('sd_rule_carryover :'||sd_rule_carryover);
3499: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',10);
3500: end if;
3501: p_segment_stop_time := NULL;
3502: if g_debug then
3503: hr_utility.trace('sd_rule_carryover :'||sd_rule_carryover);
3504: hr_utility.trace('time_out_hours :'||time_out_hours);
3505: hr_utility.trace('time_in_hours :'||time_in_hours);
3506: hr_utility.trace('p_shift_adjusted_time_out :'||p_shift_adjusted_time_out);
3507: hr_utility.trace('segment_start_time :'||segment_start_time);
3500: end if;
3501: p_segment_stop_time := NULL;
3502: if g_debug then
3503: hr_utility.trace('sd_rule_carryover :'||sd_rule_carryover);
3504: hr_utility.trace('time_out_hours :'||time_out_hours);
3505: hr_utility.trace('time_in_hours :'||time_in_hours);
3506: hr_utility.trace('p_shift_adjusted_time_out :'||p_shift_adjusted_time_out);
3507: hr_utility.trace('segment_start_time :'||segment_start_time);
3508: end if;
3501: p_segment_stop_time := NULL;
3502: if g_debug then
3503: hr_utility.trace('sd_rule_carryover :'||sd_rule_carryover);
3504: hr_utility.trace('time_out_hours :'||time_out_hours);
3505: hr_utility.trace('time_in_hours :'||time_in_hours);
3506: hr_utility.trace('p_shift_adjusted_time_out :'||p_shift_adjusted_time_out);
3507: hr_utility.trace('segment_start_time :'||segment_start_time);
3508: end if;
3509: IF sd_rule_carryover >= time_out_hours THEN
3502: if g_debug then
3503: hr_utility.trace('sd_rule_carryover :'||sd_rule_carryover);
3504: hr_utility.trace('time_out_hours :'||time_out_hours);
3505: hr_utility.trace('time_in_hours :'||time_in_hours);
3506: hr_utility.trace('p_shift_adjusted_time_out :'||p_shift_adjusted_time_out);
3507: hr_utility.trace('segment_start_time :'||segment_start_time);
3508: end if;
3509: IF sd_rule_carryover >= time_out_hours THEN
3510: if g_debug then
3503: hr_utility.trace('sd_rule_carryover :'||sd_rule_carryover);
3504: hr_utility.trace('time_out_hours :'||time_out_hours);
3505: hr_utility.trace('time_in_hours :'||time_in_hours);
3506: hr_utility.trace('p_shift_adjusted_time_out :'||p_shift_adjusted_time_out);
3507: hr_utility.trace('segment_start_time :'||segment_start_time);
3508: end if;
3509: IF sd_rule_carryover >= time_out_hours THEN
3510: if g_debug then
3511: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',20);
3507: hr_utility.trace('segment_start_time :'||segment_start_time);
3508: end if;
3509: IF sd_rule_carryover >= time_out_hours THEN
3510: if g_debug then
3511: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',20);
3512: end if;
3513: IF ((time_out_hours > time_in_hours) OR (time_in_hours > sd_rule_carryover)) THEN
3514: if g_debug then
3515: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',30);
3511: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',20);
3512: end if;
3513: IF ((time_out_hours > time_in_hours) OR (time_in_hours > sd_rule_carryover)) THEN
3514: if g_debug then
3515: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',30);
3516: end if;
3517: -- | S ST C S | ST C
3518: p_segment_stop_time := p_shift_adjusted_time_out;
3519: if g_debug then
3516: end if;
3517: -- | S ST C S | ST C
3518: p_segment_stop_time := p_shift_adjusted_time_out;
3519: if g_debug then
3520: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3521: end if;
3522: ELSIF time_in_hours >= time_out_hours THEN
3523: if g_debug then
3524: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',40);
3520: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3521: end if;
3522: ELSIF time_in_hours >= time_out_hours THEN
3523: if g_debug then
3524: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',40);
3525: end if;
3526: -- S C | ST
3527: p_segment_stop_time := segment_start_time +
3528: ((hxt_util.time_to_hours(sd_rule_carryover) -
3527: p_segment_stop_time := segment_start_time +
3528: ((hxt_util.time_to_hours(sd_rule_carryover) -
3529: hxt_util.time_to_hours(time_in_hours)) / 24);
3530: if g_debug then
3531: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3532: end if;
3533: END IF;
3534: ELSE -- carryover < time_out_hours
3535: IF time_in_hours < sd_rule_carryover THEN
3533: END IF;
3534: ELSE -- carryover < time_out_hours
3535: IF time_in_hours < sd_rule_carryover THEN
3536: if g_debug then
3537: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',50);
3538: end if;
3539: -- S C ST |
3540: p_segment_stop_time := segment_start_time +
3541: ((hxt_util.time_to_hours(sd_rule_carryover) -
3540: p_segment_stop_time := segment_start_time +
3541: ((hxt_util.time_to_hours(sd_rule_carryover) -
3542: hxt_util.time_to_hours(time_in_hours)) / 24);
3543: if g_debug then
3544: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3545: end if;
3546:
3547: ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours > time_out_hours)) THEN
3548: if g_debug then
3545: end if;
3546:
3547: ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours > time_out_hours)) THEN
3548: if g_debug then
3549: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',60);
3550: end if;
3551: -- S | C ST
3552: p_segment_stop_time := segment_start_time +
3553: ((hxt_util.time_to_hours(sd_rule_carryover) +
3552: p_segment_stop_time := segment_start_time +
3553: ((hxt_util.time_to_hours(sd_rule_carryover) +
3554: (hxt_util.time_to_hours(2400) - hxt_util.time_to_hours(time_in_hours))) / 24);
3555: if g_debug then
3556: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3557: end if;
3558: ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours < time_out_hours)) THEN
3559: if g_debug then
3560: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',70);
3556: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3557: end if;
3558: ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours < time_out_hours)) THEN
3559: if g_debug then
3560: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',70);
3561: end if;
3562: -- S ST | C
3563: p_segment_stop_time := p_shift_adjusted_time_out;
3564: if g_debug then
3561: end if;
3562: -- S ST | C
3563: p_segment_stop_time := p_shift_adjusted_time_out;
3564: if g_debug then
3565: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3566: end if;
3567: ELSIF ((time_in_hours > sd_rule_carryover)
3568: AND (time_in_hours = time_out_hours)) THEN --SIR523
3569: -- S ST | C
3567: ELSIF ((time_in_hours > sd_rule_carryover)
3568: AND (time_in_hours = time_out_hours)) THEN --SIR523
3569: -- S ST | C
3570: if g_debug then
3571: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',80);
3572: end if;
3573: p_segment_stop_time := p_shift_adjusted_time_out;
3574: if g_debug then
3575: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3571: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',80);
3572: end if;
3573: p_segment_stop_time := p_shift_adjusted_time_out;
3574: if g_debug then
3575: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3576: end if;
3577: ELSIF time_in_hours = sd_rule_carryover THEN
3578: if g_debug then
3579: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',90);
3575: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3576: end if;
3577: ELSIF time_in_hours = sd_rule_carryover THEN
3578: if g_debug then
3579: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',90);
3580: end if;
3581: p_segment_stop_time := p_shift_adjusted_time_out;
3582: if g_debug then
3583: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3579: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',90);
3580: end if;
3581: p_segment_stop_time := p_shift_adjusted_time_out;
3582: if g_debug then
3583: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
3584: end if;
3585: END IF;
3586: END IF;
3587:
3586: END IF;
3587:
3588: p_hours_worked := ((p_segment_stop_time - segment_start_time) * 24);
3589: if g_debug then
3590: hr_utility.trace('p_hours_worked :'||p_hours_worked);
3591: end if;
3592:
3593: IF p_segment_stop_time IS NULL THEN
3594: if g_debug then
3591: end if;
3592:
3593: IF p_segment_stop_time IS NULL THEN
3594: if g_debug then
3595: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',100);
3596: end if;
3597: FND_MESSAGE.SET_NAME('HXT','HXT_39314_SEG_STOP_TIME_NF'); -- HXT11
3598: RETURN call_hxthxc_gen_error('HXT','HXT_39314_SEG_STOP_TIME_NF',NULL,location, ''); -- HXT11
3599: --2278400 RETURN call_gen_error(location, ''); -- HXT11
3615:
3616: BEGIN
3617:
3618: if g_debug then
3619: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',10);
3620: end if;
3621: l_stop_time := NULL;
3622:
3623: if g_debug then
3620: end if;
3621: l_stop_time := NULL;
3622:
3623: if g_debug then
3624: hr_utility.trace('sd_rule_start :'||sd_rule_start);
3625: hr_utility.trace('time_in_hours :'||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3626: end if;
3627: IF sd_rule_start > time_in_hours THEN
3628: if g_debug then
3621: l_stop_time := NULL;
3622:
3623: if g_debug then
3624: hr_utility.trace('sd_rule_start :'||sd_rule_start);
3625: hr_utility.trace('time_in_hours :'||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3626: end if;
3627: IF sd_rule_start > time_in_hours THEN
3628: if g_debug then
3629: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',20);
3625: hr_utility.trace('time_in_hours :'||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3626: end if;
3627: IF sd_rule_start > time_in_hours THEN
3628: if g_debug then
3629: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',20);
3630: hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3631: end if;
3632: l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start)
3633: - hxt_util.time_to_hours(time_in_hours)) / 24);
3626: end if;
3627: IF sd_rule_start > time_in_hours THEN
3628: if g_debug then
3629: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',20);
3630: hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3631: end if;
3632: l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start)
3633: - hxt_util.time_to_hours(time_in_hours)) / 24);
3634: -- | Segment.start Rule.start or Segment.start Rule.start |
3632: l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start)
3633: - hxt_util.time_to_hours(time_in_hours)) / 24);
3634: -- | Segment.start Rule.start or Segment.start Rule.start |
3635: if g_debug then
3636: hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3637: end if;
3638: ELSE
3639: if g_debug then
3640: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',30);
3636: hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3637: end if;
3638: ELSE
3639: if g_debug then
3640: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',30);
3641: hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3642: end if;
3643: l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start) +
3644: (hxt_util.time_to_hours(2400) - hxt_util.time_to_hours(time_in_hours))) / 24);
3637: end if;
3638: ELSE
3639: if g_debug then
3640: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',30);
3641: hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3642: end if;
3643: l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start) +
3644: (hxt_util.time_to_hours(2400) - hxt_util.time_to_hours(time_in_hours))) / 24);
3645: -- Segment.start | Rule.start
3643: l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start) +
3644: (hxt_util.time_to_hours(2400) - hxt_util.time_to_hours(time_in_hours))) / 24);
3645: -- Segment.start | Rule.start
3646: if g_debug then
3647: hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3648: end if;
3649: END IF;
3650:
3651: if g_debug then
3648: end if;
3649: END IF;
3650:
3651: if g_debug then
3652: hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3653: hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3654: end if;
3655: p_hours_worked := ((l_stop_time - segment_start_time) * 24);
3656: p_segment_stop_time := l_stop_time;
3649: END IF;
3650:
3651: if g_debug then
3652: hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3653: hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3654: end if;
3655: p_hours_worked := ((l_stop_time - segment_start_time) * 24);
3656: p_segment_stop_time := l_stop_time;
3657: if g_debug then
3654: end if;
3655: p_hours_worked := ((l_stop_time - segment_start_time) * 24);
3656: p_segment_stop_time := l_stop_time;
3657: if g_debug then
3658: hr_utility.trace('p_hours_worked :'||to_char(p_hours_worked,'DD-MON-YYYY HH24:MI:SS'));
3659: hr_utility.trace('p_segment_stop_time :'||to_char(p_segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3660: end if;
3661: IF l_stop_time IS NULL THEN
3662: if g_debug then
3655: p_hours_worked := ((l_stop_time - segment_start_time) * 24);
3656: p_segment_stop_time := l_stop_time;
3657: if g_debug then
3658: hr_utility.trace('p_hours_worked :'||to_char(p_hours_worked,'DD-MON-YYYY HH24:MI:SS'));
3659: hr_utility.trace('p_segment_stop_time :'||to_char(p_segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3660: end if;
3661: IF l_stop_time IS NULL THEN
3662: if g_debug then
3663: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',40);
3659: hr_utility.trace('p_segment_stop_time :'||to_char(p_segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3660: end if;
3661: IF l_stop_time IS NULL THEN
3662: if g_debug then
3663: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',40);
3664: end if;
3665: FND_MESSAGE.SET_NAME('HXT','HXT_39314_SEG_STOP_TIME_NF'); -- HXT11
3666: RETURN call_hxthxc_gen_error('HXT','HXT_39314_SEG_STOP_TIME_NF',NULL,location, ''); -- HXT11
3667: --2278400 RETURN call_gen_error(location, ''); -- HXT11
3671:
3672: END;
3673: ----------------------------GEN DETAILS MAIN MODULE----------------------------
3674: BEGIN
3675: g_debug :=hr_utility.debug_enabled;
3676: if g_debug then
3677: hr_utility.set_location('hxt_time_summary.gen_details',10);
3678: end if;
3679: IF segment_chunks.count > 0 THEN
3673: ----------------------------GEN DETAILS MAIN MODULE----------------------------
3674: BEGIN
3675: g_debug :=hr_utility.debug_enabled;
3676: if g_debug then
3677: hr_utility.set_location('hxt_time_summary.gen_details',10);
3678: end if;
3679: IF segment_chunks.count > 0 THEN
3680: if g_debug then
3681: hr_utility.set_location('hxt_time_summary.gen_details',11);
3677: hr_utility.set_location('hxt_time_summary.gen_details',10);
3678: end if;
3679: IF segment_chunks.count > 0 THEN
3680: if g_debug then
3681: hr_utility.set_location('hxt_time_summary.gen_details',11);
3682: end if;
3683: -- Bug 10123886
3684: -- No need to NULL out every member before we delete the array
3685: /*
3685: /*
3686:
3687: FOR l in segment_chunks.first .. segment_chunks.last LOOP
3688: if g_debug then
3689: hr_utility.set_location('hxt_time_summary.gen_details',12);
3690: end if;
3691: segment_chunks(l) := null;
3692: END LOOP;
3693: */
3692: END LOOP;
3693: */
3694: segment_chunks.delete;
3695: if g_debug then
3696: hr_utility.set_location('hxt_time_summary.gen_details',13);
3697: end if;
3698: END IF;
3699:
3700: IF sorted_chunks.count > 0 THEN
3698: END IF;
3699:
3700: IF sorted_chunks.count > 0 THEN
3701: if g_debug then
3702: hr_utility.set_location('hxt_time_summary.gen_details',14);
3703: end if;
3704: -- Bug 10123886
3705: -- No need to NULL out every member before we delete the array
3706: /*
3705: -- No need to NULL out every member before we delete the array
3706: /*
3707: FOR l in sorted_chunks.first .. sorted_chunks.last LOOP
3708: if g_debug then
3709: hr_utility.set_location('hxt_time_summary.gen_details',15);
3710: end if;
3711: sorted_chunks(l) := null;
3712: END LOOP;
3713: */
3713: */
3714:
3715: sorted_chunks.delete;
3716: if g_debug then
3717: hr_utility.set_location('hxt_time_summary.gen_details',16);
3718: end if;
3719: END IF;
3720:
3721: -- Takes the incoming time in and out from the summary record and splits into
3727: -- IF g_sdp_id IS NULL OR g_TIME_IN IS NULL THEN
3728: -- If there is no shift diff policy or only hours entered
3729:
3730: if g_debug then
3731: hr_utility.trace('g_sdp_id :'||g_sdp_id);
3732: hr_utility.trace('g_TIME_IN :'
3733: ||to_char(g_time_in,'DD-MON-YYYY HH24:MI:SS'));
3734: hr_utility.trace('g_element_type_id :'||g_element_type_id);
3735: end if;
3728: -- If there is no shift diff policy or only hours entered
3729:
3730: if g_debug then
3731: hr_utility.trace('g_sdp_id :'||g_sdp_id);
3732: hr_utility.trace('g_TIME_IN :'
3733: ||to_char(g_time_in,'DD-MON-YYYY HH24:MI:SS'));
3734: hr_utility.trace('g_element_type_id :'||g_element_type_id);
3735: end if;
3736: IF g_sdp_id IS NULL OR g_TIME_IN IS NULL
3730: if g_debug then
3731: hr_utility.trace('g_sdp_id :'||g_sdp_id);
3732: hr_utility.trace('g_TIME_IN :'
3733: ||to_char(g_time_in,'DD-MON-YYYY HH24:MI:SS'));
3734: hr_utility.trace('g_element_type_id :'||g_element_type_id);
3735: end if;
3736: IF g_sdp_id IS NULL OR g_TIME_IN IS NULL
3737: OR (
3738: g_element_type_id IS NOT NULL
3739: AND g_CALL_ADJUST_ABS = 'Y'
3740: -- Bug 10123886
3741: AND hxt_time_detail.g_holiday_sdp_paid = FALSE ) THEN
3742: if g_debug then
3743: hr_utility.set_location('hxt_time_summary.gen_details',20);
3744: end if;
3745: -- If there is no shift diff policy or only hours entered or override hours
3746: -- type entered
3747: segment_start_time := p_shift_adjusted_time_in;
3755: ,NULL);
3756: ELSE
3757:
3758: if g_debug then
3759: hr_utility.set_location('hxt_time_summary.gen_details',21);
3760: end if;
3761:
3762: segment_start_time := p_shift_adjusted_time_in;
3763: segment_stop_time := p_shift_adjusted_time_out;
3765: -- segment_start := to_number(to_char(p_shift_adjusted_time_in,'HH24MI'));
3766: -- segment_stop := to_number(to_char(p_shift_adjusted_time_out,'HH24MI'));
3767:
3768: if g_debug then
3769: hr_utility.trace('p_shift_adjusted_time_in :'
3770: ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
3771: hr_utility.trace('p_shift_adjusted_time_out:'
3772: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
3773:
3767:
3768: if g_debug then
3769: hr_utility.trace('p_shift_adjusted_time_in :'
3770: ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
3771: hr_utility.trace('p_shift_adjusted_time_out:'
3772: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
3773:
3774: hr_utility.trace('segment_start_time :'
3775: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3770: ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
3771: hr_utility.trace('p_shift_adjusted_time_out:'
3772: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
3773:
3774: hr_utility.trace('segment_start_time :'
3775: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3776: hr_utility.trace('segment_stop_time :'
3777: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3778:
3772: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
3773:
3774: hr_utility.trace('segment_start_time :'
3775: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3776: hr_utility.trace('segment_stop_time :'
3777: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3778:
3779: hr_utility.set_location('hxt_time_summary.gen_details',22);
3780: end if;
3775: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
3776: hr_utility.trace('segment_stop_time :'
3777: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3778:
3779: hr_utility.set_location('hxt_time_summary.gen_details',22);
3780: end if;
3781: -- Insert these rows for hours worked start stop into the
3782: -- pl sql table segment_chunks.
3783: -- These start and stop times are entered into a single column
3791: -- after.
3792: open get_work_plan;
3793: fetch get_work_plan into ln_standard_start ,ln_standard_stop;
3794: if g_debug then
3795: hr_utility.trace('ln_standard_start :'|| ln_standard_start);
3796: hr_utility.trace('ln_standard_stop :'|| ln_standard_stop);
3797: end if;
3798: close get_work_plan;
3799:
3792: open get_work_plan;
3793: fetch get_work_plan into ln_standard_start ,ln_standard_stop;
3794: if g_debug then
3795: hr_utility.trace('ln_standard_start :'|| ln_standard_start);
3796: hr_utility.trace('ln_standard_stop :'|| ln_standard_stop);
3797: end if;
3798: close get_work_plan;
3799:
3800: wplan_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
3799:
3800: wplan_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
3801: FOR i in 1 .. 3 LOOP
3802: if g_debug then
3803: hr_utility.trace('wplan_date_worked :'
3804: || to_char(wplan_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3805: end if;
3806: time_in_dates(ln_standard_start
3807: ,ln_standard_stop
3811: ,ld_carryover2
3812: ,wplan_date_worked);
3813:
3814: if g_debug then
3815: hr_utility.trace('wp_start_time :'
3816: || to_char(wp_start_time,'DD-MON-YYYY HH24:MI:SS'));
3817: hr_utility.trace('wp_stop_time :'
3818: || to_char(wp_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3819: hr_utility.trace('ld_carryover2 :'
3813:
3814: if g_debug then
3815: hr_utility.trace('wp_start_time :'
3816: || to_char(wp_start_time,'DD-MON-YYYY HH24:MI:SS'));
3817: hr_utility.trace('wp_stop_time :'
3818: || to_char(wp_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3819: hr_utility.trace('ld_carryover2 :'
3820: || to_char(ld_carryover2,'DD-MON-YYYY HH24:MI:SS'));
3821: end if;
3815: hr_utility.trace('wp_start_time :'
3816: || to_char(wp_start_time,'DD-MON-YYYY HH24:MI:SS'));
3817: hr_utility.trace('wp_stop_time :'
3818: || to_char(wp_stop_time,'DD-MON-YYYY HH24:MI:SS'));
3819: hr_utility.trace('ld_carryover2 :'
3820: || to_char(ld_carryover2,'DD-MON-YYYY HH24:MI:SS'));
3821: end if;
3822: populate_plsql_table(wp_start_time);
3823: populate_plsql_table(wp_stop_time);
3822: populate_plsql_table(wp_start_time);
3823: populate_plsql_table(wp_stop_time);
3824:
3825: if g_debug then
3826: hr_utility.set_location('hxt_time_summary.gen_details',23);
3827: end if;
3828: wplan_date_worked := TRUNC(wplan_date_worked + 1, 'DD');
3829: END LOOP;
3830:
3828: wplan_date_worked := TRUNC(wplan_date_worked + 1, 'DD');
3829: END LOOP;
3830:
3831: if g_debug then
3832: hr_utility.set_location('hxt_time_summary.gen_details',23.4);
3833: end if;
3834: open get_sd_rules;
3835: LOOP
3836: -- fetch get_sd_rules into lv_sd_start,lv_sd_stop;
3837: fetch get_sd_rules into ln_sd_start,ln_sd_stop,ln_sd_carryover;
3838: Exit when get_sd_rules%NOTFOUND;
3839:
3840: if g_debug then
3841: hr_utility.trace('ln_sd_start :'||ln_sd_start);
3842: hr_utility.trace('ln_sd_stop :'||ln_sd_stop);
3843: hr_utility.trace('ln_sd_carryover :'||ln_sd_carryover);
3844: end if;
3845: -- Populate the plsql table with the shift policy chunks appended
3838: Exit when get_sd_rules%NOTFOUND;
3839:
3840: if g_debug then
3841: hr_utility.trace('ln_sd_start :'||ln_sd_start);
3842: hr_utility.trace('ln_sd_stop :'||ln_sd_stop);
3843: hr_utility.trace('ln_sd_carryover :'||ln_sd_carryover);
3844: end if;
3845: -- Populate the plsql table with the shift policy chunks appended
3846: -- with the dates for day before , current day and day after
3839:
3840: if g_debug then
3841: hr_utility.trace('ln_sd_start :'||ln_sd_start);
3842: hr_utility.trace('ln_sd_stop :'||ln_sd_stop);
3843: hr_utility.trace('ln_sd_carryover :'||ln_sd_carryover);
3844: end if;
3845: -- Populate the plsql table with the shift policy chunks appended
3846: -- with the dates for day before , current day and day after
3847: sd_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
3847: sd_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
3848:
3849: FOR i in 1 .. 3 LOOP
3850: if g_debug then
3851: hr_utility.set_location('hxt_time_summary.gen_details',23.5);
3852: hr_utility.trace('sd_date_worked :'
3853: || to_char(sd_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3854: end if;
3855: time_in_dates(ln_sd_start
3848:
3849: FOR i in 1 .. 3 LOOP
3850: if g_debug then
3851: hr_utility.set_location('hxt_time_summary.gen_details',23.5);
3852: hr_utility.trace('sd_date_worked :'
3853: || to_char(sd_date_worked,'DD-MON-YYYY HH24:MI:SS'));
3854: end if;
3855: time_in_dates(ln_sd_start
3856: ,ln_sd_stop
3860: ,sd_carryover_time
3861: ,sd_date_worked);
3862:
3863: if g_debug then
3864: hr_utility.trace('sd_start_time :'
3865: || to_char(sd_start_time,'DD-MON-YYYY HH24:MI:SS'));
3866: hr_utility.trace('sd_carryover_time :'
3867: || to_char(sd_carryover_time,'DD-MON-YYYY HH24:MI:SS'));
3868: end if;
3862:
3863: if g_debug then
3864: hr_utility.trace('sd_start_time :'
3865: || to_char(sd_start_time,'DD-MON-YYYY HH24:MI:SS'));
3866: hr_utility.trace('sd_carryover_time :'
3867: || to_char(sd_carryover_time,'DD-MON-YYYY HH24:MI:SS'));
3868: end if;
3869: populate_plsql_table(sd_start_time);
3870: populate_plsql_table(sd_carryover_time);
3869: populate_plsql_table(sd_start_time);
3870: populate_plsql_table(sd_carryover_time);
3871:
3872: if g_debug then
3873: hr_utility.set_location('hxt_time_summary.gen_details',23.6);
3874: end if;
3875: sd_date_worked := TRUNC(sd_date_worked + 1, 'DD');
3876:
3877: END LOOP;
3876:
3877: END LOOP;
3878:
3879: if g_debug then
3880: hr_utility.set_location('hxt_time_summary.gen_details',23.8);
3881: end if;
3882: END LOOP;
3883: close get_sd_rules;
3884:
3882: END LOOP;
3883: close get_sd_rules;
3884:
3885: if g_debug then
3886: hr_utility.set_location('hxt_time_summary.gen_details',24);
3887:
3888: hr_utility.trace('FYI');
3889: end if;
3890: if segment_chunks.count <> 0 then
3884:
3885: if g_debug then
3886: hr_utility.set_location('hxt_time_summary.gen_details',24);
3887:
3888: hr_utility.trace('FYI');
3889: end if;
3890: if segment_chunks.count <> 0 then
3891: if g_debug then
3892: hr_utility.set_location('hxt_time_summary.gen_details',26);
3888: hr_utility.trace('FYI');
3889: end if;
3890: if segment_chunks.count <> 0 then
3891: if g_debug then
3892: hr_utility.set_location('hxt_time_summary.gen_details',26);
3893: end if;
3894: for l_cnt in segment_chunks.first .. segment_chunks.last loop
3895: if g_debug then
3896: hr_utility.trace('segment_chunks is:'
3892: hr_utility.set_location('hxt_time_summary.gen_details',26);
3893: end if;
3894: for l_cnt in segment_chunks.first .. segment_chunks.last loop
3895: if g_debug then
3896: hr_utility.trace('segment_chunks is:'
3897: ||to_char(segment_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
3898: end if;
3899: end loop;
3900: end if;
3898: end if;
3899: end loop;
3900: end if;
3901: if g_debug then
3902: hr_utility.trace('END FYI');
3903: hr_utility.set_location('hxt_time_summary.gen_details',27);
3904: end if;
3905: -- Get the sorted pl sql table
3906: sorted_chunks := sort( segment_chunks , 'ASC');
3899: end loop;
3900: end if;
3901: if g_debug then
3902: hr_utility.trace('END FYI');
3903: hr_utility.set_location('hxt_time_summary.gen_details',27);
3904: end if;
3905: -- Get the sorted pl sql table
3906: sorted_chunks := sort( segment_chunks , 'ASC');
3907:
3905: -- Get the sorted pl sql table
3906: sorted_chunks := sort( segment_chunks , 'ASC');
3907:
3908: if g_debug then
3909: hr_utility.trace('FYI');
3910: end if;
3911: if sorted_chunks.count <> 0 then
3912: if g_debug then
3913: hr_utility.set_location('hxt_time_summary.gen_details',28);
3909: hr_utility.trace('FYI');
3910: end if;
3911: if sorted_chunks.count <> 0 then
3912: if g_debug then
3913: hr_utility.set_location('hxt_time_summary.gen_details',28);
3914: end if;
3915: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
3916: if g_debug then
3917: hr_utility.trace('sorted_chunks is:'
3913: hr_utility.set_location('hxt_time_summary.gen_details',28);
3914: end if;
3915: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
3916: if g_debug then
3917: hr_utility.trace('sorted_chunks is:'
3918: ||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
3919: end if;
3920: end loop;
3921: if g_debug then
3918: ||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
3919: end if;
3920: end loop;
3921: if g_debug then
3922: hr_utility.set_location('hxt_time_summary.gen_details',29);
3923: end if;
3924: end if;
3925: if g_debug then
3926: hr_utility.trace('END FYI');
3922: hr_utility.set_location('hxt_time_summary.gen_details',29);
3923: end if;
3924: end if;
3925: if g_debug then
3926: hr_utility.trace('END FYI');
3927: end if;
3928:
3929:
3930: if sorted_chunks.count > 0 then
3928:
3929:
3930: if sorted_chunks.count > 0 then
3931: if g_debug then
3932: hr_utility.set_location('hxt_time_summary.gen_details',30);
3933: end if;
3934: for i in sorted_chunks.first .. sorted_chunks.last-1 loop
3935: if g_debug then
3936: hr_utility.set_location('hxt_time_summary.gen_details',31);
3932: hr_utility.set_location('hxt_time_summary.gen_details',30);
3933: end if;
3934: for i in sorted_chunks.first .. sorted_chunks.last-1 loop
3935: if g_debug then
3936: hr_utility.set_location('hxt_time_summary.gen_details',31);
3937: end if;
3938: chunk_start := sorted_chunks(i);
3939:
3940: if g_debug then
3937: end if;
3938: chunk_start := sorted_chunks(i);
3939:
3940: if g_debug then
3941: hr_utility.trace('chunk_start:'
3942: ||to_char(chunk_start,'DD-MON-YYYY HH24:MI:SS'));
3943: end if;
3944: /* if i = sorted_chunks.last then
3945: chunk_stop := sorted_chunks(sorted_chunks.first);
3943: end if;
3944: /* if i = sorted_chunks.last then
3945: chunk_stop := sorted_chunks(sorted_chunks.first);
3946: if g_debug then
3947: hr_utility.trace('chunk_stop :'
3948: ||to_char(chunk_stop,'DD-MON-YYYY HH24:MI:SS'));
3949: end if;
3950: else
3951: */
3950: else
3951: */
3952: chunk_stop := sorted_chunks(i+1);
3953: if g_debug then
3954: hr_utility.trace('chunk_stop :'
3955: ||to_char(chunk_stop,'DD-MON-YYYY HH24:MI:SS'));
3956: end if;
3957: -- end if;
3958: if g_debug then
3955: ||to_char(chunk_stop,'DD-MON-YYYY HH24:MI:SS'));
3956: end if;
3957: -- end if;
3958: if g_debug then
3959: hr_utility.set_location('hxt_time_summary.gen_details',32);
3960: end if;
3961: IF segment_earning(chunk_start
3962: ,chunk_stop
3963: ,p_sdp_earning_type
3963: ,p_sdp_earning_type
3964: ,p_sdovr_earning_type
3965: ) THEN
3966: if g_debug then
3967: hr_utility.set_location('hxt_time_summary.gen_details',33);
3968: end if;
3969: -- hours_worked:= ((chunk_stop - chunk_start) * 24);
3970: hours_worked:= ROUND((chunk_stop - chunk_start) * 24,3);
3971:
3969: -- hours_worked:= ((chunk_stop - chunk_start) * 24);
3970: hours_worked:= ROUND((chunk_stop - chunk_start) * 24,3);
3971:
3972: if g_debug then
3973: hr_utility.trace('hours_worked :'|| hours_worked);
3974: hr_utility.set_location('hxt_time_summary.gen_details',34);
3975: end if;
3976: return_code := gen_special(location
3977: ,chunk_start
3970: hours_worked:= ROUND((chunk_stop - chunk_start) * 24,3);
3971:
3972: if g_debug then
3973: hr_utility.trace('hours_worked :'|| hours_worked);
3974: hr_utility.set_location('hxt_time_summary.gen_details',34);
3975: end if;
3976: return_code := gen_special(location
3977: ,chunk_start
3978: ,chunk_stop
3979: ,hours_worked
3980: ,p_sdp_earning_type
3981: ,p_sdovr_earning_type);
3982: if g_debug then
3983: hr_utility.set_location('hxt_time_summary.gen_details',35);
3984: end if;
3985: END IF; -- segment_earning
3986:
3987: end loop;
3986:
3987: end loop;
3988:
3989: if g_debug then
3990: hr_utility.set_location('hxt_time_summary.gen_details',36);
3991: end if;
3992:
3993: end if;
3994:
3996:
3997: /*
3998:
3999: if g_debug then
4000: hr_utility.set_location('hxt_time_summary.gen_details',30);
4001: end if;
4002: segment_stop_time := p_shift_adjusted_time_in;
4003: if g_debug then
4004: hr_utility.trace('p_shift_adjusted_time_in :'
4000: hr_utility.set_location('hxt_time_summary.gen_details',30);
4001: end if;
4002: segment_stop_time := p_shift_adjusted_time_in;
4003: if g_debug then
4004: hr_utility.trace('p_shift_adjusted_time_in :'
4005: ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
4006: hr_utility.trace('p_shift_adjusted_time_out:'
4007: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
4008: hr_utility.trace('return_code :'
4002: segment_stop_time := p_shift_adjusted_time_in;
4003: if g_debug then
4004: hr_utility.trace('p_shift_adjusted_time_in :'
4005: ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
4006: hr_utility.trace('p_shift_adjusted_time_out:'
4007: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
4008: hr_utility.trace('return_code :'
4009: ||return_code);
4010: hr_utility.trace('segment_stop_time :'
4004: hr_utility.trace('p_shift_adjusted_time_in :'
4005: ||to_char(p_shift_adjusted_time_in,'DD-MON-YYYY HH24:MI:SS'));
4006: hr_utility.trace('p_shift_adjusted_time_out:'
4007: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
4008: hr_utility.trace('return_code :'
4009: ||return_code);
4010: hr_utility.trace('segment_stop_time :'
4011: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4012: end if;
4006: hr_utility.trace('p_shift_adjusted_time_out:'
4007: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
4008: hr_utility.trace('return_code :'
4009: ||return_code);
4010: hr_utility.trace('segment_stop_time :'
4011: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4012: end if;
4013: WHILE (segment_stop_time < p_shift_adjusted_time_out) AND return_code = 0
4014: LOOP
4012: end if;
4013: WHILE (segment_stop_time < p_shift_adjusted_time_out) AND return_code = 0
4014: LOOP
4015: if g_debug then
4016: hr_utility.set_location('hxt_time_summary.gen_details',40);
4017: end if;
4018: --while the end of the last segment is not end of time
4019: segment_start_time := segment_stop_time; -- start each segment with
4020: -- stop time of last
4018: --while the end of the last segment is not end of time
4019: segment_start_time := segment_stop_time; -- start each segment with
4020: -- stop time of last
4021: if g_debug then
4022: hr_utility.trace('segment_start_time :'
4023: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4024: end if;
4025: IF segment_start_in_rule(sd_rule_earning,sd_rule_carryover) THEN
4026: if g_debug then
4023: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4024: end if;
4025: IF segment_start_in_rule(sd_rule_earning,sd_rule_carryover) THEN
4026: if g_debug then
4027: hr_utility.set_location('hxt_time_summary.gen_details',50);
4028: end if;
4029: -- if the start is in a rule
4030: if g_debug then
4031: hr_utility.trace('segment_stop_time :'
4027: hr_utility.set_location('hxt_time_summary.gen_details',50);
4028: end if;
4029: -- if the start is in a rule
4030: if g_debug then
4031: hr_utility.trace('segment_stop_time :'
4032: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4033: hr_utility.trace('hours_worked :'||hours_worked);
4034: end if;
4035: return_code := set_stop_and_hours(segment_stop_time,hours_worked);
4029: -- if the start is in a rule
4030: if g_debug then
4031: hr_utility.trace('segment_stop_time :'
4032: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4033: hr_utility.trace('hours_worked :'||hours_worked);
4034: end if;
4035: return_code := set_stop_and_hours(segment_stop_time,hours_worked);
4036: if g_debug then
4037: hr_utility.trace('segment_stop_time :'
4033: hr_utility.trace('hours_worked :'||hours_worked);
4034: end if;
4035: return_code := set_stop_and_hours(segment_stop_time,hours_worked);
4036: if g_debug then
4037: hr_utility.trace('segment_stop_time :'
4038: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4039: hr_utility.trace('hours_worked :'||hours_worked);
4040: hr_utility.trace('return_code :'||return_code);
4041: end if;
4035: return_code := set_stop_and_hours(segment_stop_time,hours_worked);
4036: if g_debug then
4037: hr_utility.trace('segment_stop_time :'
4038: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4039: hr_utility.trace('hours_worked :'||hours_worked);
4040: hr_utility.trace('return_code :'||return_code);
4041: end if;
4042: ELSIF rule_start_in_segment(sd_rule_start) THEN
4043: if g_debug then
4036: if g_debug then
4037: hr_utility.trace('segment_stop_time :'
4038: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4039: hr_utility.trace('hours_worked :'||hours_worked);
4040: hr_utility.trace('return_code :'||return_code);
4041: end if;
4042: ELSIF rule_start_in_segment(sd_rule_start) THEN
4043: if g_debug then
4044: hr_utility.set_location('hxt_time_summary.gen_details',60);
4040: hr_utility.trace('return_code :'||return_code);
4041: end if;
4042: ELSIF rule_start_in_segment(sd_rule_start) THEN
4043: if g_debug then
4044: hr_utility.set_location('hxt_time_summary.gen_details',60);
4045: end if;
4046: -- if a rule starts in the segment
4047: return_code := set_stop_at_rule(segment_stop_time,hours_worked);
4048: if g_debug then
4045: end if;
4046: -- if a rule starts in the segment
4047: return_code := set_stop_at_rule(segment_stop_time,hours_worked);
4048: if g_debug then
4049: hr_utility.trace('segment_stop_time :'
4050: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4051: hr_utility.trace('hours_worked :'||hours_worked);
4052: hr_utility.trace('return_code :'||return_code);
4053: end if;
4047: return_code := set_stop_at_rule(segment_stop_time,hours_worked);
4048: if g_debug then
4049: hr_utility.trace('segment_stop_time :'
4050: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4051: hr_utility.trace('hours_worked :'||hours_worked);
4052: hr_utility.trace('return_code :'||return_code);
4053: end if;
4054: sd_rule_earning := NULL;
4055:
4048: if g_debug then
4049: hr_utility.trace('segment_stop_time :'
4050: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4051: hr_utility.trace('hours_worked :'||hours_worked);
4052: hr_utility.trace('return_code :'||return_code);
4053: end if;
4054: sd_rule_earning := NULL;
4055:
4056:
4059:
4060: open Get_Shift_stop_time( g_ASSIGNMENT_ID,g_DATE_WORKED );
4061: fetch Get_Shift_stop_time into lv_standard_stop;
4062: if g_debug then
4063: hr_utility.trace('lv_standard_stop :'||lv_standard_stop);
4064: end if;
4065: close Get_Shift_stop_time;
4066:
4067: -- IF working from 0800 to 2300 then pay only regular hours
4068: -- from 1200 to 1700 (shift diff defined from 0800 to 1200) and
4069: -- shift diff Override from 1700 to 2300
4070: IF to_number(to_char(segment_stop_time,'HH24MI')) < lv_standard_stop THEN
4071: if g_debug then
4072: hr_utility.set_location('hxt_time_summary.gen_details',75);
4073: end if;
4074: -- segment_stop_time := lv_standard_stop;
4075: segment_stop_time := to_date((to_char(g_date_worked, 'DDMMYYYY')||lv_standard_stop), 'DDMMYYYYHH24MI');
4076: if g_debug then
4073: end if;
4074: -- segment_stop_time := lv_standard_stop;
4075: segment_stop_time := to_date((to_char(g_date_worked, 'DDMMYYYY')||lv_standard_stop), 'DDMMYYYYHH24MI');
4076: if g_debug then
4077: hr_utility.trace('segment_stop_time :'
4078: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4079: hr_utility.trace('segment_start_time :'
4080: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4081: end if;
4075: segment_stop_time := to_date((to_char(g_date_worked, 'DDMMYYYY')||lv_standard_stop), 'DDMMYYYYHH24MI');
4076: if g_debug then
4077: hr_utility.trace('segment_stop_time :'
4078: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4079: hr_utility.trace('segment_start_time :'
4080: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4081: end if;
4082: hours_worked:=((segment_stop_time - segment_start_time) * 24);
4083: if g_debug then
4080: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4081: end if;
4082: hours_worked:=((segment_stop_time - segment_start_time) * 24);
4083: if g_debug then
4084: hr_utility.trace('hours_worked :'||hours_worked);
4085: end if;
4086: sd_rule_earning := NULL;
4087: ELSE -- segment_stop_time >= lv_standard_stop
4088: if g_debug then
4085: end if;
4086: sd_rule_earning := NULL;
4087: ELSE -- segment_stop_time >= lv_standard_stop
4088: if g_debug then
4089: hr_utility.set_location('hxt_time_summary.gen_details',76);
4090: end if;
4091: segment_stop_time := p_shift_adjusted_time_out;
4092: if g_debug then
4093: hr_utility.trace('segment_stop_time :'
4089: hr_utility.set_location('hxt_time_summary.gen_details',76);
4090: end if;
4091: segment_stop_time := p_shift_adjusted_time_out;
4092: if g_debug then
4093: hr_utility.trace('segment_stop_time :'
4094: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4095: hr_utility.trace('segment_start_time :'
4096: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4097: end if;
4091: segment_stop_time := p_shift_adjusted_time_out;
4092: if g_debug then
4093: hr_utility.trace('segment_stop_time :'
4094: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4095: hr_utility.trace('segment_start_time :'
4096: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4097: end if;
4098: hours_worked:=((segment_stop_time - segment_start_time) * 24);
4099: if g_debug then
4096: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4097: end if;
4098: hours_worked:=((segment_stop_time - segment_start_time) * 24);
4099: if g_debug then
4100: hr_utility.trace('hours_worked :'||hours_worked);
4101: end if;
4102: -- IF the shift differential override is applicable to the time_in and
4103: -- time_outs ,then set p_sdf_id to g_sdovr_id so that the cursor
4104: -- cur_elig_prem in hxt_time_pay.gen_premiums returns a row and the
4105: -- logic gets called to insert the data into hxt_det_hours_worked
4106: sd_rule_earning := g_sdovr_id;
4107: END IF;
4108: if g_debug then
4109: hr_utility.set_location('hxt_time_summary.gen_details',77);
4110: end if;
4111: -- END g_sdovr_id is NOT NULL
4112:
4113: ELSE
4112:
4113: ELSE
4114: -- NO SHIFT DIFF RULES APPLY
4115: if g_debug then
4116: hr_utility.trace('segment_start_time :'
4117: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4118: hr_utility.trace('segment_stop_time :'
4119: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4120: hr_utility.trace('p_shift_adjusted_time_out :'
4114: -- NO SHIFT DIFF RULES APPLY
4115: if g_debug then
4116: hr_utility.trace('segment_start_time :'
4117: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4118: hr_utility.trace('segment_stop_time :'
4119: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4120: hr_utility.trace('p_shift_adjusted_time_out :'
4121: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
4122: hr_utility.set_location('hxt_time_summary.gen_details',78);
4116: hr_utility.trace('segment_start_time :'
4117: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4118: hr_utility.trace('segment_stop_time :'
4119: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4120: hr_utility.trace('p_shift_adjusted_time_out :'
4121: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
4122: hr_utility.set_location('hxt_time_summary.gen_details',78);
4123: end if;
4124: segment_stop_time := p_shift_adjusted_time_out;
4118: hr_utility.trace('segment_stop_time :'
4119: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4120: hr_utility.trace('p_shift_adjusted_time_out :'
4121: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
4122: hr_utility.set_location('hxt_time_summary.gen_details',78);
4123: end if;
4124: segment_stop_time := p_shift_adjusted_time_out;
4125: if g_debug then
4126: hr_utility.trace('segment_stop_time :'
4122: hr_utility.set_location('hxt_time_summary.gen_details',78);
4123: end if;
4124: segment_stop_time := p_shift_adjusted_time_out;
4125: if g_debug then
4126: hr_utility.trace('segment_stop_time :'
4127: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4128: end if;
4129: hours_worked :=((segment_stop_time - segment_start_time) * 24);
4130: if g_debug then
4127: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4128: end if;
4129: hours_worked :=((segment_stop_time - segment_start_time) * 24);
4130: if g_debug then
4131: hr_utility.trace('segment_stop_time :'
4132: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4133: hr_utility.trace('hours_worked :'||hours_worked);
4134: end if;
4135: sd_rule_earning := NULL;
4129: hours_worked :=((segment_stop_time - segment_start_time) * 24);
4130: if g_debug then
4131: hr_utility.trace('segment_stop_time :'
4132: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4133: hr_utility.trace('hours_worked :'||hours_worked);
4134: end if;
4135: sd_rule_earning := NULL;
4136:
4137: END IF;
4136:
4137: END IF;
4138:
4139: if g_debug then
4140: hr_utility.trace('location :'||location);
4141: hr_utility.trace('segment_start_time :'
4142: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4143: hr_utility.trace('segment_stop_time :'
4144: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4137: END IF;
4138:
4139: if g_debug then
4140: hr_utility.trace('location :'||location);
4141: hr_utility.trace('segment_start_time :'
4142: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4143: hr_utility.trace('segment_stop_time :'
4144: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4145: hr_utility.trace('sd_rule_earning :'||sd_rule_earning);
4139: if g_debug then
4140: hr_utility.trace('location :'||location);
4141: hr_utility.trace('segment_start_time :'
4142: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4143: hr_utility.trace('segment_stop_time :'
4144: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4145: hr_utility.trace('sd_rule_earning :'||sd_rule_earning);
4146: hr_utility.trace('return_code :'||return_code);
4147: end if;
4141: hr_utility.trace('segment_start_time :'
4142: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4143: hr_utility.trace('segment_stop_time :'
4144: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4145: hr_utility.trace('sd_rule_earning :'||sd_rule_earning);
4146: hr_utility.trace('return_code :'||return_code);
4147: end if;
4148: IF return_code = 0 THEN
4149: if g_debug then
4142: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
4143: hr_utility.trace('segment_stop_time :'
4144: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
4145: hr_utility.trace('sd_rule_earning :'||sd_rule_earning);
4146: hr_utility.trace('return_code :'||return_code);
4147: end if;
4148: IF return_code = 0 THEN
4149: if g_debug then
4150: hr_utility.set_location('hxt_time_summary.gen_details',80);
4146: hr_utility.trace('return_code :'||return_code);
4147: end if;
4148: IF return_code = 0 THEN
4149: if g_debug then
4150: hr_utility.set_location('hxt_time_summary.gen_details',80);
4151: end if;
4152: loop_count := loop_count + 1;
4153: if g_debug then
4154: hr_utility.trace('loop_count :'||loop_count);
4150: hr_utility.set_location('hxt_time_summary.gen_details',80);
4151: end if;
4152: loop_count := loop_count + 1;
4153: if g_debug then
4154: hr_utility.trace('loop_count :'||loop_count);
4155: end if;
4156: return_code := gen_special(location
4157: ,segment_start_time
4158: ,segment_stop_time
4160: ,sd_rule_earning);
4161: END IF;
4162: IF loop_count > 50 THEN
4163: if g_debug then
4164: hr_utility.set_location('hxt_time_summary.gen_details',90);
4165: end if;
4166: FND_MESSAGE.SET_NAME('HXT','HXT_39300_GEN_DTAIL_EXC_LOOP'); -- HXT11
4167: return_code := call_gen_error(location, ''); -- HXT11
4168: END IF;
4167: return_code := call_gen_error(location, ''); -- HXT11
4168: END IF;
4169: IF return_code > 0 THEN
4170: if g_debug then
4171: hr_utility.set_location('hxt_time_summary.gen_details',100);
4172: end if;
4173: EXIT;
4174: END IF;
4175: END LOOP;
4176: */
4177:
4178:
4179: if g_debug then
4180: hr_utility.set_location('hxt_time_summary.gen_details',40);
4181: end if;
4182: END IF; -- there is a shift diff policy
4183:
4184: if g_debug then
4181: end if;
4182: END IF; -- there is a shift diff policy
4183:
4184: if g_debug then
4185: hr_utility.set_location('hxt_time_summary.gen_details',45);
4186: end if;
4187: RETURN return_code;
4188:
4189: EXCEPTION
4189: EXCEPTION
4190:
4191: WHEN OTHERS THEN
4192: if g_debug then
4193: hr_utility.set_location('hxt_time_summary.gen_details',110);
4194: end if;
4195: hr_utility.trace(dbms_utility.format_error_backtrace);
4196: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
4197: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm); -- HXT11
4191: WHEN OTHERS THEN
4192: if g_debug then
4193: hr_utility.set_location('hxt_time_summary.gen_details',110);
4194: end if;
4195: hr_utility.trace(dbms_utility.format_error_backtrace);
4196: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
4197: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm); -- HXT11
4198: --2278400 RETURN call_gen_error(location, '', sqlerrm); -- HXT11
4199:
4219:
4220: BEGIN
4221:
4222: if g_debug then
4223: hr_utility.set_location('hxt_time_summary.Get_Include',10);
4224: end if;
4225: -- Check if no earn group passed
4226: IF p_egt_id is null THEN
4227: if g_debug then
4224: end if;
4225: -- Check if no earn group passed
4226: IF p_egt_id is null THEN
4227: if g_debug then
4228: hr_utility.set_location('hxt_time_summary.Get_Include',20);
4229: end if;
4230: RETURN l_retcode;
4231: ELSE
4232: BEGIN
4230: RETURN l_retcode;
4231: ELSE
4232: BEGIN
4233: if g_debug then
4234: hr_utility.set_location('hxt_time_summary.Get_Include',30);
4235: end if;
4236: -- Check if element_type exists in earning group
4237: SELECT 'Y'
4238: INTO l_retcode
4250:
4251: EXCEPTION
4252: WHEN no_data_found THEN
4253: if g_debug then
4254: hr_utility.set_location('hxt_time_summary.Get_Include',40);
4255: end if;
4256: RETURN l_retcode;
4257: END;
4258: END IF;
4260: EXCEPTION
4261: -- Return Oracle error number
4262: WHEN others THEN
4263: if g_debug then
4264: hr_utility.set_location('hxt_time_summary.Get_Include',50);
4265: end if;
4266: hr_utility.trace(dbms_utility.format_error_backtrace);
4267: FND_MESSAGE.SET_NAME('HXT','HXT_39270_OR_ERR_G_GROUP'); -- HXT11
4268: l_error_code := call_hxthxc_gen_error('HXT','HXT_39270_OR_ERR_G_GROUP',l_location,'', sqlerrm); -- HXT11
4262: WHEN others THEN
4263: if g_debug then
4264: hr_utility.set_location('hxt_time_summary.Get_Include',50);
4265: end if;
4266: hr_utility.trace(dbms_utility.format_error_backtrace);
4267: FND_MESSAGE.SET_NAME('HXT','HXT_39270_OR_ERR_G_GROUP'); -- HXT11
4268: l_error_code := call_hxthxc_gen_error('HXT','HXT_39270_OR_ERR_G_GROUP',l_location,'', sqlerrm); -- HXT11
4269: --2278400 l_error_code := call_gen_error(l_location,'', sqlerrm); -- HXT11
4270: RETURN 'E';
4322:
4323: BEGIN
4324:
4325: if g_debug then
4326: hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',10);
4327: hr_utility.trace('p_time_in :'||to_char(p_time_in,'DD-MON-YYYY HH24:MI:SS'));
4328: hr_utility.trace('p_time_out :'||to_char(p_time_out,'DD-MON-YYYY HH24:MI:SS'));
4329: hr_utility.trace('p_hours_worked :'||p_hours_worked);
4330: hr_utility.trace('p_shift_diff_earning_id :'||p_shift_diff_earning_id);
4323: BEGIN
4324:
4325: if g_debug then
4326: hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',10);
4327: hr_utility.trace('p_time_in :'||to_char(p_time_in,'DD-MON-YYYY HH24:MI:SS'));
4328: hr_utility.trace('p_time_out :'||to_char(p_time_out,'DD-MON-YYYY HH24:MI:SS'));
4329: hr_utility.trace('p_hours_worked :'||p_hours_worked);
4330: hr_utility.trace('p_shift_diff_earning_id :'||p_shift_diff_earning_id);
4331: hr_utility.trace('p_sdovr_earning_id :'||p_sdovr_earning_id);
4324:
4325: if g_debug then
4326: hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',10);
4327: hr_utility.trace('p_time_in :'||to_char(p_time_in,'DD-MON-YYYY HH24:MI:SS'));
4328: hr_utility.trace('p_time_out :'||to_char(p_time_out,'DD-MON-YYYY HH24:MI:SS'));
4329: hr_utility.trace('p_hours_worked :'||p_hours_worked);
4330: hr_utility.trace('p_shift_diff_earning_id :'||p_shift_diff_earning_id);
4331: hr_utility.trace('p_sdovr_earning_id :'||p_sdovr_earning_id);
4332: end if;
4325: if g_debug then
4326: hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',10);
4327: hr_utility.trace('p_time_in :'||to_char(p_time_in,'DD-MON-YYYY HH24:MI:SS'));
4328: hr_utility.trace('p_time_out :'||to_char(p_time_out,'DD-MON-YYYY HH24:MI:SS'));
4329: hr_utility.trace('p_hours_worked :'||p_hours_worked);
4330: hr_utility.trace('p_shift_diff_earning_id :'||p_shift_diff_earning_id);
4331: hr_utility.trace('p_sdovr_earning_id :'||p_sdovr_earning_id);
4332: end if;
4333:
4326: hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',10);
4327: hr_utility.trace('p_time_in :'||to_char(p_time_in,'DD-MON-YYYY HH24:MI:SS'));
4328: hr_utility.trace('p_time_out :'||to_char(p_time_out,'DD-MON-YYYY HH24:MI:SS'));
4329: hr_utility.trace('p_hours_worked :'||p_hours_worked);
4330: hr_utility.trace('p_shift_diff_earning_id :'||p_shift_diff_earning_id);
4331: hr_utility.trace('p_sdovr_earning_id :'||p_sdovr_earning_id);
4332: end if;
4333:
4334: -- Bug 9218428
4327: hr_utility.trace('p_time_in :'||to_char(p_time_in,'DD-MON-YYYY HH24:MI:SS'));
4328: hr_utility.trace('p_time_out :'||to_char(p_time_out,'DD-MON-YYYY HH24:MI:SS'));
4329: hr_utility.trace('p_hours_worked :'||p_hours_worked);
4330: hr_utility.trace('p_shift_diff_earning_id :'||p_shift_diff_earning_id);
4331: hr_utility.trace('p_sdovr_earning_id :'||p_sdovr_earning_id);
4332: end if;
4333:
4334: -- Bug 9218428
4335: -- The below code checks for the Max number of hours which can be entered on
4349: CLOSE get_max_hours;
4350:
4351: IF g_debug
4352: THEN
4353: hr_utility.trace('Daily Cap is '||l_max_hours);
4354: END IF;
4355:
4356: IF l_max_hours IS NULL
4357: OR l_max_hours = 0
4375: CLOSE get_other_hours;
4376:
4377: IF g_debug
4378: THEN
4379: hr_utility.trace('Total Hours is '||l_total_hours);
4380: END IF;
4381:
4382: l_total_hours := NVL(l_total_hours,0) + g_hours;
4383:
4458: --,g_GROUP_ID
4459: );
4460:
4461: if g_debug then
4462: hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',20);
4463: end if;
4464: END; -- gen special
4465:
4466: -- begin