[Home] [Help]
1: PACKAGE BODY hxt_time_summary AS
2: /* $Header: hxttsum.pkb 120.4.12000000.2 2007/03/13 11:42:02 nissharm noship $ */
3:
4: --Global variables for package
5: --Used for parameters received that are not changed
239:
240: BEGIN
241:
242: IF g_debug THEN
243: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',10);
244: END IF;
245:
246: l_hours := p_hours ; -- SIR092
247:
311: ,l_sqlerrm);
312:
313: IF l_error_status = 0 THEN
314: IF g_debug then
315: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs',20);
316: END IF;
317:
318: HXT_DML.INSERT_HXT_DET_HOURS_WORKED (
319: p_rowid => l_rowid,
363: p_ZIP_CODE => g_ZIP_CODE);
364:
365: ELSE /* l_error_status <> 0 */
366: IF g_debug then
367: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 30);
368: END IF;
369: -- Insert record in error table.
370: FND_MESSAGE.SET_NAME('HXT','HXT_39421_GET_RETRO_ERR');
371: END IF;
372:
373: EXCEPTION
374: WHEN OTHERS THEN
375: IF g_debug THEN
376: hr_utility.set_location('hxt_time_summary.insert_non_explodable_hrs', 40);
377: END IF;
378: -- Insert record in error table.
379: FND_MESSAGE.SET_NAME('HXT','HXT_39313_OR_ERR_INS_REC');
380:
410:
411: BEGIN
412: g_debug :=hr_utility.debug_enabled;
413: if g_debug then
414: hr_utility.set_location('hxt_time_summary.generate_details',10);
415: end if;
416:
417: -- Set the flags for checking which shift diff premium gets paid for a
418: -- segment chunk.
496: -- Context of the Preference is TC_W_RULES_EVALUATION
497: -- Segment is attribute 3 OT_RECURRING_PERIOD
498: --
499: if g_debug then
500: hr_utility.set_location('hxt_time_summary.generate_details',20);
501: end if;
502: otl_recurring_period := hxc_preference_evaluation.resource_preferences
503: (g_person_id
504: ,'TC_W_RULES_EVALUATION'
504: ,'TC_W_RULES_EVALUATION'
505: ,3
506: ,p_date_worked);
507: if g_debug then
508: hr_utility.set_location('hxt_time_summary.generate_details',30);
509: hr_utility.trace('otl_recurring_period :'||otl_recurring_period);
510: end if;
511: --
512: -- Now calculate the start day as per the otl recurring period
511: --
512: -- Now calculate the start day as per the otl recurring period
513: --
514: if g_debug then
515: hr_utility.set_location('hxt_time_summary.generate_details',40);
516: end if;
517: IF otl_recurring_period is NOT NULL THEN
518: --
519: -- Get the period start_date, period_type for the recurring_period
518: --
519: -- Get the period start_date, period_type for the recurring_period
520: --
521: if g_debug then
522: hr_utility.set_location('hxt_time_summary.generate_details',50);
523: end if;
524: SELECT start_date,period_type
525: INTO l_period_start_date,l_period_type
526: FROM hxc_recurring_periods
531: || to_char(l_period_start_date,'DD-MON-YYYY HH24:MI:SS'));
532: hr_utility.trace('l_period_type :'|| l_period_type);
533: hr_utility.trace('g_date_worked :'
534: || to_char(g_date_worked,'DD-MON-YYYY HH24:MI:SS'));
535: hr_utility.set_location('hxt_time_summary.generate_details',60);
536: end if;
537: hxc_period_evaluation.period_start_stop
538: (g_date_worked
539: ,l_period_start_date
541: ,l_period_end
542: ,l_period_type);
543:
544: if g_debug then
545: hr_utility.set_location('hxt_time_summary.generate_details',70);
546: hr_utility.trace('l_period_start :'
547: || to_char(l_period_start,'DD-MON-YYYY HH24:MI:SS'));
548: hr_utility.trace('l_period_end :'
549: || to_char(l_period_end,'DD-MON-YYYY HH24:MI:SS'));
550: end if;
551: g_start_day_of_week := TO_CHAR(l_period_start,'DAY');
552: if g_debug then
553: hr_utility.trace('g_start_day_of_week :'|| g_start_day_of_week);
554: hr_utility.set_location('hxt_time_summary.generate_details',80);
555: end if;
556: ELSE
557: if g_debug then
558: hr_utility.set_location('hxt_time_summary.generate_details',90);
554: hr_utility.set_location('hxt_time_summary.generate_details',80);
555: end if;
556: ELSE
557: if g_debug then
558: hr_utility.set_location('hxt_time_summary.generate_details',90);
559: hr_utility.trace('p_PERIOD_START_DATE :'
560: || to_char(p_PERIOD_START_DATE,'DD-MON-YYYY HH24:MI:SS'));
561: end if;
562: g_start_day_of_week := TO_CHAR(p_PERIOD_START_DATE,'DAY');
566: l_location := p_location||':GD';
567: END IF;
568:
569: if g_debug then
570: hr_utility.set_location('hxt_time_summary.generate_details',100);
571: end if;
572: IF valid_data(l_location) NOT IN (0,1) THEN
573: -- check for time in/out and hour values
574: if g_debug then
571: end if;
572: IF valid_data(l_location) NOT IN (0,1) THEN
573: -- check for time in/out and hour values
574: if g_debug then
575: hr_utility.set_location('hxt_time_summary.generate_details',110);
576: end if;
577: RETURN(2);
578: ELSIF valid_data(l_location) = 1 THEN
579: if g_debug then
576: end if;
577: RETURN(2);
578: ELSIF valid_data(l_location) = 1 THEN
579: if g_debug then
580: hr_utility.set_location('hxt_time_summary.generate_details',120);
581: end if;
582: RETURN(11);
583: END IF;
584:
583: END IF;
584:
585: IF g_element_type_id is not null THEN
586: if g_debug then
587: hr_utility.set_location('hxt_time_summary.generate_details',130);
588: end if;
589: -- Determine if earning is in assigned INCLUDE earning group
590: g_include_yn := Get_Include(l_location
591: ,g_egt_id
593: ,g_date_worked);
594:
595: IF g_include_yn = 'E' THEN
596: if g_debug then
597: hr_utility.set_location('hxt_time_summary.generate_details',140);
598: end if;
599: RETURN 2;
600: END IF;
601:
602: END IF; --element_type_id is not null
603:
604: IF l_error_code < 2 THEN
605: if g_debug then
606: hr_utility.set_location('hxt_time_summary.generate_details',150);
607: end if;
608:
609: /* Bug: 4489952 changes starts here */
610:
609: /* Bug: 4489952 changes starts here */
610:
611: IF check_non_explosion_entry (g_element_type_id, g_date_worked) THEN
612: IF g_debug then
613: hr_utility.set_location('hxt_time_summary.generate_details',151);
614: END IF;
615:
616: insert_non_explodable_hrs;
617:
617:
618: ELSE /* check_non_explosion_entry = FALSE */
619:
620: IF g_debug then
621: hr_utility.set_location('hxt_time_summary.generate_details',155);
622: END IF;
623:
624: rebuild_details( l_location, l_rebuild_code);
625: END IF;
629: END IF;
630:
631: IF l_rebuild_code <> 0 THEN
632: if g_debug then
633: hr_utility.set_location('hxt_time_summary.generate_details',160);
634: end if;
635: RETURN l_rebuild_code;
636: ELSE
637: if g_debug then
634: end if;
635: RETURN l_rebuild_code;
636: ELSE
637: if g_debug then
638: hr_utility.set_location('hxt_time_summary.generate_details',170);
639: end if;
640: RETURN l_error_code;
641: END IF;
642:
643: EXCEPTION
644:
645: WHEN OTHERS THEN
646: if g_debug then
647: hr_utility.set_location('hxt_time_summary.generate_details',180);
648: end if;
649: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
650: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm);
651: --2278400 RETURN call_gen_error(l_location, '', sqlerrm);
662:
663: BEGIN
664: g_debug :=hr_utility.debug_enabled;
665: if g_debug then
666: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
667: hr_utility.trace('ln_start :'||ln_start);
668: hr_utility.trace('ln_stop :'||ln_stop);
669: hr_utility.trace('ln_carryover :'||ln_carryover);
670: end if;
677: IF ln_start < ln_stop OR
678: (ln_start = 0 AND
679: ln_stop = 0) THEN
680: if g_debug then
681: hr_utility.set_location('hxt_time_summary.time_in_dates',20);
682: end if;
683: time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
684: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
685: if g_debug then
687: || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
688: end if;
689: ELSE --IF ln_start >= ln_stop
690: if g_debug then
691: hr_utility.set_location('hxt_time_summary.time_in_dates',30);
692: end if;
693: time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
694: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
695: if g_debug then
699: END IF;
700:
701: IF ln_carryover is NOT NULL THEN
702: if g_debug then
703: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
704: end if;
705: IF ln_start < ln_carryover THEN
706: if g_debug then
707: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
703: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
704: end if;
705: IF ln_start < ln_carryover THEN
706: if g_debug then
707: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
708: end if;
709: carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
710: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
711:
714: || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
715: end if;
716: ELSE --IF ln_start >= ln_carryover
717: if g_debug then
718: hr_utility.set_location('hxt_time_summary.time_in_dates',50);
719: end if;
720: carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
721: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
722:
741:
742: BEGIN
743:
744: if g_debug then
745: hr_utility.set_location('hxt_time_summary.call_gen_error',10);
746: end if;
747: --Checking of the hours worked table for the current id is done so that
748: --validation is not done on a record being deleted.
749:
757: ,g_EFFECTIVE_END_DATE
758: ,'ERR');
759:
760: if g_debug then
761: hr_utility.set_location('hxt_time_summary.call_gen_error',20);
762: end if;
763: RETURN 2;
764:
765: EXCEPTION
765: EXCEPTION
766:
767: WHEN NO_DATA_FOUND THEN
768: if g_debug then
769: hr_utility.set_location('hxt_time_summary.call_gen_error',30);
770: end if;
771: RETURN 0;
772:
773: WHEN OTHERS THEN
771: RETURN 0;
772:
773: WHEN OTHERS THEN
774: if g_debug then
775: hr_utility.set_location('hxt_time_summary.call_gen_error',40);
776: end if;
777: hxt_util.gen_error(g_tim_id
778: ,g_id
779: ,NULL
783: ,g_EFFECTIVE_START_DATE
784: ,g_EFFECTIVE_END_DATE
785: ,'ERR');
786: if g_debug then
787: hr_utility.set_location('hxt_time_summary.call_gen_error',50);
788: end if;
789: RETURN 2;
790: END;
791:
803:
804: BEGIN
805:
806: if g_debug then
807: hr_utility.set_location('hxt_time_summary.call_gen_error',10);
808: end if;
809: --Checking of the hours worked table for the current id is done so that
810: --validation is not done on a record being deleted.
811:
832: p_time_attribute_ovn=> NULL
833: );
834: if g_debug then
835: hr_utility.trace('Adding to g_otm_messages'||p_msg_name);
836: hr_utility.set_location('hxt_time_summary.call_gen_error',20);
837: end if;
838: RETURN 2;
839:
840: EXCEPTION
840: EXCEPTION
841:
842: WHEN NO_DATA_FOUND THEN
843: if g_debug then
844: hr_utility.set_location('hxt_time_summary.call_gen_error',30);
845: end if;
846: RETURN 0;
847:
848: WHEN OTHERS THEN
846: RETURN 0;
847:
848: WHEN OTHERS THEN
849: if g_debug then
850: hr_utility.set_location('hxt_time_summary.call_gen_error',40);
851: end if;
852: hxt_util.gen_error(g_tim_id
853: ,g_id
854: ,NULL
872: p_time_attribute_ovn=> NULL
873: );
874:
875: if g_debug then
876: hr_utility.set_location('hxt_time_summary.call_gen_error',50);
877: end if;
878: RETURN 2;
879: END;
880:
890:
891: BEGIN
892:
893: if g_debug then
894: hr_utility.set_location('hxt_time_summary.valid_data',10);
895: end if;
896: IF g_time_in IS NULL AND g_time_out IS NOT NULL THEN
897:
898: if g_debug then
895: end if;
896: IF g_time_in IS NULL AND g_time_out IS NOT NULL THEN
897:
898: if g_debug then
899: hr_utility.set_location('hxt_time_summary.valid_data',20);
900: end if;
901: FND_MESSAGE.SET_NAME('HXT','HXT_39327_TIME_IN_OT_REQ');
902: RETURN call_hxthxc_gen_error('HXT','HXT_39327_TIME_IN_OT_REQ',NULL,location, '');
903:
905:
906: IF g_time_out IS NULL AND g_time_in IS NOT NULL THEN
907:
908: if g_debug then
909: hr_utility.set_location('hxt_time_summary.valid_data',30);
910: end if;
911: FND_MESSAGE.SET_NAME('HXT','HXT_39327_TIME_IN_OT_REQ');
912: RETURN call_hxthxc_gen_error('HXT','HXT_39327_TIME_IN_OT_REQ',NULL,location, '');
913:
915:
916: IF g_time_in IS NULL AND g_hours IS NULL THEN
917:
918: if g_debug then
919: hr_utility.set_location('hxt_time_summary.valid_data',40);
920: end if;
921: FND_MESSAGE.SET_NAME('HXT','HXT_39328_TIME_OR_TOT_HRS_REQ');
922: RETURN call_hxthxc_gen_error('HXT','HXT_39328_TIME_OR_TOT_HRS_REQ',NULL,location, '');
923:
925:
926: IF g_element_type_id IS NOT NULL THEN
927:
928: if g_debug then
929: hr_utility.set_location('hxt_time_summary.valid_data',50);
930: end if;
931: l_cat := hxt_util.element_cat(g_element_type_id
932: ,g_date_worked);
933:
937:
938: BEGIN
939: g_debug :=hr_utility.debug_enabled;
940: if g_debug then
941: hr_utility.set_location('hxt_time_summary.valid_data',60);
942: end if;
943:
944: /* Bug: 4489952 changes starts here */
945:
1005:
1006: EXCEPTION
1007: WHEN NO_DATA_FOUND THEN
1008: if g_debug then
1009: hr_utility.set_location('hxt_time_summary.valid_data',90);
1010: end if;
1011: NULL;
1012: END;
1013:
1012: END;
1013:
1014: IF error_code = 1 THEN
1015: if g_debug then
1016: hr_utility.set_location('hxt_time_summary.valid_data',100);
1017: end if;
1018: FND_MESSAGE.SET_NAME('HXT','HXT_39329_INC_TIM_HR_ENTRIES');
1019:
1020: END IF;
1024: EXCEPTION
1025:
1026: WHEN OTHERS THEN
1027: if g_debug then
1028: hr_utility.set_location('hxt_time_summary.valid_data',110);
1029: end if;
1030: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1031: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm);
1032:
1046:
1047: BEGIN
1048:
1049: if g_debug then
1050: hr_utility.set_location('hxt_time_summary.Delete_Details',10);
1051: end if;
1052: p_error_code := 0;
1053:
1054: --Add local code to location variable
1055: l_location := p_location||':DD';
1056:
1057: IF nvl(g_DT_UPDATE_MODE, 'CORRECTION') = 'CORRECTION' THEN
1058: if g_debug then
1059: hr_utility.set_location('hxt_time_summary.Delete_Details',20);
1060: end if;
1061: DELETE from hxt_det_hours_worked_f
1062: WHERE parent_id = g_id;
1063:
1064: DELETE from hxt_errors_f where --SPR C153
1065: hrw_id = g_id; --SPR C153
1066: ELSE
1067: if g_debug then
1068: hr_utility.set_location('hxt_time_summary.Delete_Details',30);
1069: end if;
1070: -- Delete details for this entry and all others that follow
1071: -- on this day for this person that are non-absence or are in
1072: -- the person's include group
1089: EXCEPTION
1090:
1091: WHEN OTHERS THEN
1092: if g_debug then
1093: hr_utility.set_location('hxt_time_summary.Delete_Details',40);
1094: end if;
1095: -- Write to error table - do not generate details
1096: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
1097: p_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm); -- HXT11
1104:
1105: BEGIN
1106:
1107: if g_debug then
1108: hr_utility.set_location('hxt_time_summary.shift_adjust_times',10);
1109: end if;
1110: p_shift_adjusted_time_in := g_TIME_IN;
1111: p_shift_adjusted_time_out := g_TIME_OUT;
1112:
1111: p_shift_adjusted_time_out := g_TIME_OUT;
1112:
1113: IF (g_EARLY_START IS NOT NULL AND g_STANDARD_START IS NOT NULL) THEN
1114: if g_debug then
1115: hr_utility.set_location('hxt_time_summary.shift_adjust_times',20);
1116: end if;
1117: IF g_EARLY_START > g_STANDARD_START THEN -- spans midnight
1118: if g_debug then
1119: hr_utility.set_location('hxt_time_summary.shift_adjust_times',30);
1115: hr_utility.set_location('hxt_time_summary.shift_adjust_times',20);
1116: end if;
1117: IF g_EARLY_START > g_STANDARD_START THEN -- spans midnight
1118: if g_debug then
1119: hr_utility.set_location('hxt_time_summary.shift_adjust_times',30);
1120: end if;
1121: IF to_number(to_char(g_TIME_IN, 'HH24MI')) < g_STANDARD_START THEN
1122: if g_debug then
1123: hr_utility.set_location('hxt_time_summary.shift_adjust_times',40);
1119: hr_utility.set_location('hxt_time_summary.shift_adjust_times',30);
1120: end if;
1121: IF to_number(to_char(g_TIME_IN, 'HH24MI')) < g_STANDARD_START THEN
1122: if g_debug then
1123: hr_utility.set_location('hxt_time_summary.shift_adjust_times',40);
1124: end if;
1125: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
1126: - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
1127: ELSIF to_number(to_char(g_TIME_IN, 'HH24MI')) > g_EARLY_START THEN
1125: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
1126: - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
1127: ELSIF to_number(to_char(g_TIME_IN, 'HH24MI')) > g_EARLY_START THEN
1128: if g_debug then
1129: hr_utility.set_location('hxt_time_summary.shift_adjust_times',50);
1130: end if;
1131: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
1132: + (hxt_util.time_to_hours(2400) -
1133: hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI'))))) / 24);
1133: hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI'))))) / 24);
1134: END IF;
1135: ELSE -- no midnight span
1136: if g_debug then
1137: hr_utility.set_location('hxt_time_summary.shift_adjust_times',60);
1138: end if;
1139: IF to_number(to_char(g_TIME_IN, 'HH24MI')) BETWEEN g_EARLY_START
1140: AND g_STANDARD_START THEN
1141: if g_debug then
1138: end if;
1139: IF to_number(to_char(g_TIME_IN, 'HH24MI')) BETWEEN g_EARLY_START
1140: AND g_STANDARD_START THEN
1141: if g_debug then
1142: hr_utility.set_location('hxt_time_summary.shift_adjust_times',70);
1143: end if;
1144: p_shift_adjusted_time_in := g_TIME_IN + ((hxt_util.time_to_hours(g_STANDARD_START)
1145: - hxt_util.time_to_hours(to_number(to_char(g_TIME_IN, 'HH24MI')))) / 24);
1146: END IF;
1148: END IF;
1149:
1150: IF (g_LATE_STOP IS NOT NULL AND g_STANDARD_STOP IS NOT NULL) THEN
1151: if g_debug then
1152: hr_utility.set_location('hxt_time_summary.shift_adjust_times',80);
1153: end if;
1154: IF g_LATE_STOP < g_STANDARD_STOP THEN -- spans midnight
1155: if g_debug then
1156: hr_utility.set_location('hxt_time_summary.shift_adjust_times',90);
1152: hr_utility.set_location('hxt_time_summary.shift_adjust_times',80);
1153: end if;
1154: IF g_LATE_STOP < g_STANDARD_STOP THEN -- spans midnight
1155: if g_debug then
1156: hr_utility.set_location('hxt_time_summary.shift_adjust_times',90);
1157: end if;
1158: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) > g_STANDARD_STOP THEN
1159: if g_debug then
1160: hr_utility.set_location('hxt_time_summary.shift_adjust_times',100);
1156: hr_utility.set_location('hxt_time_summary.shift_adjust_times',90);
1157: end if;
1158: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) > g_STANDARD_STOP THEN
1159: if g_debug then
1160: hr_utility.set_location('hxt_time_summary.shift_adjust_times',100);
1161: end if;
1162: p_shift_adjusted_time_out := g_TIME_OUT -
1163: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
1164: - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
1163: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
1164: - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
1165: ELSIF to_number(to_char(g_TIME_OUT, 'HH24MI')) < g_LATE_STOP THEN
1166: if g_debug then
1167: hr_utility.set_location('hxt_time_summary.shift_adjust_times',110);
1168: end if;
1169: p_shift_adjusted_time_out := g_TIME_OUT -
1170: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
1171: + (hxt_util.time_to_hours(2400) - (hxt_util.time_to_hours(g_STANDARD_STOP)))) / 24);
1171: + (hxt_util.time_to_hours(2400) - (hxt_util.time_to_hours(g_STANDARD_STOP)))) / 24);
1172: END IF;
1173: ELSE -- no midnight span
1174: if g_debug then
1175: hr_utility.set_location('hxt_time_summary.shift_adjust_times',120);
1176: end if;
1177: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) BETWEEN g_STANDARD_STOP
1178: AND g_LATE_STOP THEN
1179: if g_debug then
1176: end if;
1177: IF to_number(to_char(g_TIME_OUT, 'HH24MI')) BETWEEN g_STANDARD_STOP
1178: AND g_LATE_STOP THEN
1179: if g_debug then
1180: hr_utility.set_location('hxt_time_summary.shift_adjust_times',130);
1181: end if;
1182: p_shift_adjusted_time_out := g_TIME_OUT -
1183: (( hxt_util.time_to_hours(to_number(to_char(g_TIME_OUT, 'HH24MI')))
1184: - (hxt_util.time_to_hours(g_STANDARD_STOP))) / 24);
1206:
1207: BEGIN
1208:
1209: if g_debug then
1210: hr_utility.set_location('hxt_time_summary.Rebuild_Details',10);
1211: end if;
1212: --Add local code to location variable
1213: l_location := p_location||':RB';
1214:
1221: ,l_hdy_id
1222: ,l_hours
1223: ,l_retcode);
1224: if g_debug then
1225: hr_utility.set_location('hxt_time_summary.Rebuild_Details',20);
1226: end if;
1227: IF l_retcode = 1 THEN
1228: if g_debug then
1229: hr_utility.set_location('hxt_time_summary.Rebuild_Details',30);
1225: hr_utility.set_location('hxt_time_summary.Rebuild_Details',20);
1226: end if;
1227: IF l_retcode = 1 THEN
1228: if g_debug then
1229: hr_utility.set_location('hxt_time_summary.Rebuild_Details',30);
1230: end if;
1231: g_hol_yn := 'Y';
1232: ELSE
1233: if g_debug then
1230: end if;
1231: g_hol_yn := 'Y';
1232: ELSE
1233: if g_debug then
1234: hr_utility.set_location('hxt_time_summary.Rebuild_Details',40);
1235: end if;
1236: g_hol_yn := 'N';
1237: END IF;
1238:
1239: l_error_code := Gen_Details(l_location, g_time_in, g_time_out);
1240:
1241: IF l_error_code <> 0 THEN
1242: if g_debug then
1243: hr_utility.set_location('hxt_time_summary.Rebuild_Details',50);
1244: end if;
1245: p_error_code := l_error_code;
1246: ELSE
1247: if g_debug then
1244: end if;
1245: p_error_code := l_error_code;
1246: ELSE
1247: if g_debug then
1248: hr_utility.set_location('hxt_time_summary.Rebuild_Details',60);
1249: end if;
1250: p_error_code := change_warning_flag;
1251: END IF;
1252:
1253: EXCEPTION
1254:
1255: WHEN OTHERS THEN
1256: if g_debug then
1257: hr_utility.set_location('hxt_time_summary.Rebuild_Details',70);
1258: end if;
1259: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
1260: p_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location, '', sqlerrm); -- HXT11
1261: --2278400 p_error_code := call_gen_error(l_location, '', sqlerrm); -- HXT11
1396:
1397: BEGIN
1398: g_debug :=hr_utility.debug_enabled;
1399: if g_debug then
1400: hr_utility.set_location('hxt_time_summary.time_in_dates',10);
1401: hr_utility.trace('ln_start :'||ln_start);
1402: hr_utility.trace('ln_stop :'||ln_stop);
1403: hr_utility.trace('ln_carryover :'||ln_carryover);
1404: end if;
1411: IF ln_start < ln_stop OR
1412: (ln_start = 0 AND
1413: ln_stop = 0) THEN
1414: if g_debug then
1415: hr_utility.set_location('hxt_time_summary.time_in_dates',20);
1416: end if;
1417: time_out := to_date((to_char(l_date_worked,'DDMMYYYY')
1418: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1419: if g_debug then
1421: || to_char(time_out,'DD-MON-YYYY HH24:MI:SS'));
1422: end if;
1423: ELSE --IF ln_start >= ln_stop
1424: if g_debug then
1425: hr_utility.set_location('hxt_time_summary.time_in_dates',30);
1426: end if;
1427: time_out := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1428: ||lpad(to_char(ln_stop),4,'0')),'DDMMYYYYHH24MI');
1429: if g_debug then
1433: END IF;
1434:
1435: IF ln_carryover is NOT NULL THEN
1436: if g_debug then
1437: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
1438: end if;
1439: IF ln_start < ln_carryover THEN
1440: if g_debug then
1441: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
1437: hr_utility.set_location('hxt_time_summary.time_in_dates',35);
1438: end if;
1439: IF ln_start < ln_carryover THEN
1440: if g_debug then
1441: hr_utility.set_location('hxt_time_summary.time_in_dates',40);
1442: end if;
1443: carryover_time := to_date((to_char(l_date_worked,'DDMMYYYY')
1444: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1445:
1448: || to_char(carryover_time,'DD-MON-YYYY HH24:MI:SS'));
1449: end if;
1450: ELSE --IF ln_start >= ln_carryover
1451: if g_debug then
1452: hr_utility.set_location('hxt_time_summary.time_in_dates',50);
1453: end if;
1454: carryover_time := to_date((to_char(l_date_worked + 1,'DDMMYYYY')
1455: ||lpad(to_char(ln_carryover),4,'0')),'DDMMYYYYHH24MI');
1456:
1473:
1474: BEGIN
1475:
1476: if g_debug then
1477: hr_utility.set_location('hxt_time_summary.sort',10);
1478: end if;
1479: if segment_chunks.count > 0 then
1480: For i in segment_chunks.first .. segment_chunks.LAST
1481: Loop
1479: if segment_chunks.count > 0 then
1480: For i in segment_chunks.first .. segment_chunks.LAST
1481: Loop
1482: if g_debug then
1483: hr_utility.set_location('hxt_time_summary.sort',20);
1484: end if;
1485: sorted_chunks(i):= segment_chunks(i);
1486: End loop;
1487: end if;
1490: hr_utility.trace('FYI');
1491: end if;
1492: if sorted_chunks.count <> 0 then
1493: if g_debug then
1494: hr_utility.set_location('hxt_time_summary.sort',30);
1495: end if;
1496: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
1497: if g_debug then
1498: hr_utility.trace('sorted_chunks is:'||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
1504: end if;
1505: For i in sorted_chunks.First+1 .. sorted_chunks.LAST
1506: Loop
1507: if g_debug then
1508: hr_utility.set_location('hxt_time_summary.sort',40);
1509: end if;
1510: v_temp:= sorted_chunks(i);
1511: if g_debug then
1512: hr_utility.trace('v_temp :'||to_char(v_temp,'DD-MON-YYYY HH24:MI:SS'));
1512: hr_utility.trace('v_temp :'||to_char(v_temp,'DD-MON-YYYY HH24:MI:SS'));
1513: end if;
1514: <
1515: if g_debug then
1516: hr_utility.set_location('hxt_time_summary.sort',50);
1517: end if;
1518: For j in REVERSE sorted_chunks.First .. (i-1)
1519: Loop
1520: if g_debug then
1517: end if;
1518: For j in REVERSE sorted_chunks.First .. (i-1)
1519: Loop
1520: if g_debug then
1521: hr_utility.set_location('hxt_time_summary.sort',60);
1522: hr_utility.trace('sorted_chunks(j) :'||to_char(sorted_chunks(j),'DD-MON-YYYY HH24:MI:SS'));
1523: end if;
1524: If sorted_chunks(j) >= v_temp then
1525: if g_debug then
1522: hr_utility.trace('sorted_chunks(j) :'||to_char(sorted_chunks(j),'DD-MON-YYYY HH24:MI:SS'));
1523: end if;
1524: If sorted_chunks(j) >= v_temp then
1525: if g_debug then
1526: hr_utility.set_location('hxt_time_summary.sort',70);
1527: end if;
1528: sorted_chunks(j+1) := sorted_chunks(j);
1529: sorted_chunks(j) := v_temp;
1530: end if;
1528: sorted_chunks(j+1) := sorted_chunks(j);
1529: sorted_chunks(j) := v_temp;
1530: end if;
1531: if g_debug then
1532: hr_utility.set_location('hxt_time_summary.sort',80);
1533: end if;
1534: end loop inner_loop;
1535: if g_debug then
1536: hr_utility.set_location('hxt_time_summary.sort',90);
1532: hr_utility.set_location('hxt_time_summary.sort',80);
1533: end if;
1534: end loop inner_loop;
1535: if g_debug then
1536: hr_utility.set_location('hxt_time_summary.sort',90);
1537: end if;
1538: end loop;
1539: IF p_order ='ASC' then
1540: if g_debug then
1537: end if;
1538: end loop;
1539: IF p_order ='ASC' then
1540: if g_debug then
1541: hr_utility.set_location('hxt_time_summary.sort',100);
1542: end if;
1543: RETURN sorted_chunks;
1544: END IF;
1545: if g_debug then
1542: end if;
1543: RETURN sorted_chunks;
1544: END IF;
1545: if g_debug then
1546: hr_utility.set_location('hxt_time_summary.sort',110);
1547: end if;
1548: END;
1549:
1550: FUNCTION segment_earning (segment_start in date
1610:
1611:
1612: if g_debug then
1613:
1614: hr_utility.set_location('hxt_time_summary.check_eligibility',10);
1615: end if;
1616:
1617: IF (
1618: (lv_time_in <= lv_segment_start
1638: -- lv_time_in lv_segment_start | lv_segment_stop lv_time_out
1639: )
1640: THEN
1641: if g_debug then
1642: hr_utility.set_location('hxt_time_summary.check_eligibility',20);
1643: end if;
1644: RETURN TRUE;
1645: ELSE
1646: if g_debug then
1643: end if;
1644: RETURN TRUE;
1645: ELSE
1646: if g_debug then
1647: hr_utility.set_location('hxt_time_summary.check_eligibility',30);
1648: end if;
1649: RETURN FALSE;
1650: END IF;
1651: if g_debug then
1648: end if;
1649: RETURN FALSE;
1650: END IF;
1651: if g_debug then
1652: hr_utility.set_location('hxt_time_summary.check_eligibility',40);
1653: end if;
1654: END;
1655:
1656: ----------------------------segment_earning main module-------------------------
1657:
1658: BEGIN
1659:
1660: if g_debug then
1661: l_proc := 'hxt_time_summary.segment_earning';
1662: hr_utility.set_location(l_proc,10);
1663:
1664: -- hr_utility.trace('worked_time_in :'||worked_time_in);
1665: -- hr_utility.trace('worked_time_out :'||worked_time_out);
2091:
2092: BEGIN
2093:
2094: if g_debug then
2095: hr_utility.set_location('hxt_time_summary.segment_start_in_rule',10);
2096: end if;
2097: OPEN sd_rules;
2098: FETCH sd_rules INTO p_rule_earning_type, p_carryover;
2099: if g_debug then
2101: hr_utility.trace('p_carryover :'||p_carryover);
2102: end if;
2103: IF sd_rules%NOTFOUND THEN
2104: if g_debug then
2105: hr_utility.set_location('hxt_time_summary.segment_start_in_rule',20);
2106: end if;
2107: CLOSE sd_rules;
2108: RETURN FALSE;
2109: END IF;
2107: CLOSE sd_rules;
2108: RETURN FALSE;
2109: END IF;
2110: if g_debug then
2111: hr_utility.set_location('hxt_time_summary.segment_start_in_rule',30);
2112: end if;
2113: CLOSE sd_rules;
2114: RETURN TRUE;
2115:
2142:
2143: BEGIN
2144:
2145: if g_debug then
2146: hr_utility.set_location('hxt_time_summary.rule_start_in_segment',10);
2147: end if;
2148: OPEN sd_rules;
2149: FETCH sd_rules INTO p_rule_start;
2150: if g_debug then
2151: hr_utility.trace('p_rule_start :'||p_rule_start);
2152: end if;
2153: IF sd_rules%NOTFOUND THEN
2154: if g_debug then
2155: hr_utility.set_location('hxt_time_summary.rule_start_in_segment',20);
2156: end if;
2157: CLOSE sd_rules;
2158: RETURN FALSE;
2159: END IF;
2178:
2179: BEGIN
2180:
2181: if g_debug then
2182: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',10);
2183: end if;
2184: p_segment_stop_time := NULL;
2185: if g_debug then
2186: hr_utility.trace('sd_rule_carryover :'||sd_rule_carryover);
2190: hr_utility.trace('segment_start_time :'||segment_start_time);
2191: end if;
2192: IF sd_rule_carryover >= time_out_hours THEN
2193: if g_debug then
2194: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',20);
2195: end if;
2196: IF ((time_out_hours > time_in_hours) OR (time_in_hours > sd_rule_carryover)) THEN
2197: if g_debug then
2198: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',30);
2194: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',20);
2195: end if;
2196: IF ((time_out_hours > time_in_hours) OR (time_in_hours > sd_rule_carryover)) THEN
2197: if g_debug then
2198: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',30);
2199: end if;
2200: -- | S ST C S | ST C
2201: p_segment_stop_time := p_shift_adjusted_time_out;
2202: if g_debug then
2203: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2204: end if;
2205: ELSIF time_in_hours >= time_out_hours THEN
2206: if g_debug then
2207: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',40);
2208: end if;
2209: -- S C | ST
2210: p_segment_stop_time := segment_start_time +
2211: ((hxt_util.time_to_hours(sd_rule_carryover) -
2216: END IF;
2217: ELSE -- carryover < time_out_hours
2218: IF time_in_hours < sd_rule_carryover THEN
2219: if g_debug then
2220: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',50);
2221: end if;
2222: -- S C ST |
2223: p_segment_stop_time := segment_start_time +
2224: ((hxt_util.time_to_hours(sd_rule_carryover) -
2228: end if;
2229:
2230: ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours > time_out_hours)) THEN
2231: if g_debug then
2232: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',60);
2233: end if;
2234: -- S | C ST
2235: p_segment_stop_time := segment_start_time +
2236: ((hxt_util.time_to_hours(sd_rule_carryover) +
2239: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2240: end if;
2241: ELSIF ((time_in_hours > sd_rule_carryover) AND (time_in_hours < time_out_hours)) THEN
2242: if g_debug then
2243: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',70);
2244: end if;
2245: -- S ST | C
2246: p_segment_stop_time := p_shift_adjusted_time_out;
2247: if g_debug then
2250: ELSIF ((time_in_hours > sd_rule_carryover)
2251: AND (time_in_hours = time_out_hours)) THEN --SIR523
2252: -- S ST | C
2253: if g_debug then
2254: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',80);
2255: end if;
2256: p_segment_stop_time := p_shift_adjusted_time_out;
2257: if g_debug then
2258: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2258: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2259: end if;
2260: ELSIF time_in_hours = sd_rule_carryover THEN
2261: if g_debug then
2262: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',90);
2263: end if;
2264: p_segment_stop_time := p_shift_adjusted_time_out;
2265: if g_debug then
2266: hr_utility.trace('p_segment_stop_time :'||p_segment_stop_time);
2274: end if;
2275:
2276: IF p_segment_stop_time IS NULL THEN
2277: if g_debug then
2278: hr_utility.set_location('hxt_time_summary.set_stop_and_hours',100);
2279: end if;
2280: FND_MESSAGE.SET_NAME('HXT','HXT_39314_SEG_STOP_TIME_NF'); -- HXT11
2281: RETURN call_hxthxc_gen_error('HXT','HXT_39314_SEG_STOP_TIME_NF',NULL,location, ''); -- HXT11
2282: --2278400 RETURN call_gen_error(location, ''); -- HXT11
2298:
2299: BEGIN
2300:
2301: if g_debug then
2302: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',10);
2303: end if;
2304: l_stop_time := NULL;
2305:
2306: if g_debug then
2308: hr_utility.trace('time_in_hours :'||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2309: end if;
2310: IF sd_rule_start > time_in_hours THEN
2311: if g_debug then
2312: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',20);
2313: hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2314: end if;
2315: l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start)
2316: - hxt_util.time_to_hours(time_in_hours)) / 24);
2319: hr_utility.trace('l_stop_time :'||to_char(l_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2320: end if;
2321: ELSE
2322: if g_debug then
2323: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',30);
2324: hr_utility.trace('segment_start_time:'||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2325: end if;
2326: l_stop_time := segment_start_time + ((hxt_util.time_to_hours(sd_rule_start) +
2327: (hxt_util.time_to_hours(2400) - hxt_util.time_to_hours(time_in_hours))) / 24);
2342: hr_utility.trace('p_segment_stop_time :'||to_char(p_segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2343: end if;
2344: IF l_stop_time IS NULL THEN
2345: if g_debug then
2346: hr_utility.set_location('hxt_time_summary.set_stop_at_rule',40);
2347: end if;
2348: FND_MESSAGE.SET_NAME('HXT','HXT_39314_SEG_STOP_TIME_NF'); -- HXT11
2349: RETURN call_hxthxc_gen_error('HXT','HXT_39314_SEG_STOP_TIME_NF',NULL,location, ''); -- HXT11
2350: --2278400 RETURN call_gen_error(location, ''); -- HXT11
2356: ----------------------------GEN DETAILS MAIN MODULE----------------------------
2357: BEGIN
2358: g_debug :=hr_utility.debug_enabled;
2359: if g_debug then
2360: hr_utility.set_location('hxt_time_summary.gen_details',10);
2361: end if;
2362: IF segment_chunks.count > 0 THEN
2363: if g_debug then
2364: hr_utility.set_location('hxt_time_summary.gen_details',11);
2360: hr_utility.set_location('hxt_time_summary.gen_details',10);
2361: end if;
2362: IF segment_chunks.count > 0 THEN
2363: if g_debug then
2364: hr_utility.set_location('hxt_time_summary.gen_details',11);
2365: end if;
2366: FOR l in segment_chunks.first .. segment_chunks.last LOOP
2367: if g_debug then
2368: hr_utility.set_location('hxt_time_summary.gen_details',12);
2364: hr_utility.set_location('hxt_time_summary.gen_details',11);
2365: end if;
2366: FOR l in segment_chunks.first .. segment_chunks.last LOOP
2367: if g_debug then
2368: hr_utility.set_location('hxt_time_summary.gen_details',12);
2369: end if;
2370: segment_chunks(l) := null;
2371: END LOOP;
2372: segment_chunks.delete;
2370: segment_chunks(l) := null;
2371: END LOOP;
2372: segment_chunks.delete;
2373: if g_debug then
2374: hr_utility.set_location('hxt_time_summary.gen_details',13);
2375: end if;
2376: END IF;
2377:
2378: IF sorted_chunks.count > 0 THEN
2376: END IF;
2377:
2378: IF sorted_chunks.count > 0 THEN
2379: if g_debug then
2380: hr_utility.set_location('hxt_time_summary.gen_details',14);
2381: end if;
2382: FOR l in sorted_chunks.first .. sorted_chunks.last LOOP
2383: if g_debug then
2384: hr_utility.set_location('hxt_time_summary.gen_details',15);
2380: hr_utility.set_location('hxt_time_summary.gen_details',14);
2381: end if;
2382: FOR l in sorted_chunks.first .. sorted_chunks.last LOOP
2383: if g_debug then
2384: hr_utility.set_location('hxt_time_summary.gen_details',15);
2385: end if;
2386: sorted_chunks(l) := null;
2387: END LOOP;
2388: sorted_chunks.delete;
2386: sorted_chunks(l) := null;
2387: END LOOP;
2388: sorted_chunks.delete;
2389: if g_debug then
2390: hr_utility.set_location('hxt_time_summary.gen_details',16);
2391: end if;
2392: END IF;
2393:
2394: -- Takes the incoming time in and out from the summary record and splits into
2408: end if;
2409: IF g_sdp_id IS NULL OR g_TIME_IN IS NULL
2410: OR (g_element_type_id IS NOT NULL AND g_CALL_ADJUST_ABS = 'Y') THEN
2411: if g_debug then
2412: hr_utility.set_location('hxt_time_summary.gen_details',20);
2413: end if;
2414: -- If there is no shift diff policy or only hours entered or override hours
2415: -- type entered
2416: segment_start_time := p_shift_adjusted_time_in;
2424: ,NULL);
2425: ELSE
2426:
2427: if g_debug then
2428: hr_utility.set_location('hxt_time_summary.gen_details',21);
2429: end if;
2430:
2431: segment_start_time := p_shift_adjusted_time_in;
2432: segment_stop_time := p_shift_adjusted_time_out;
2444: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2445: hr_utility.trace('segment_stop_time :'
2446: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2447:
2448: hr_utility.set_location('hxt_time_summary.gen_details',22);
2449: end if;
2450: -- Insert these rows for hours worked start stop into the
2451: -- pl sql table segment_chunks.
2452: -- These start and stop times are entered into a single column
2491: populate_plsql_table(wp_start_time);
2492: populate_plsql_table(wp_stop_time);
2493:
2494: if g_debug then
2495: hr_utility.set_location('hxt_time_summary.gen_details',23);
2496: end if;
2497: wplan_date_worked := TRUNC(wplan_date_worked + 1, 'DD');
2498: END LOOP;
2499:
2497: wplan_date_worked := TRUNC(wplan_date_worked + 1, 'DD');
2498: END LOOP;
2499:
2500: if g_debug then
2501: hr_utility.set_location('hxt_time_summary.gen_details',23.4);
2502: end if;
2503: open get_sd_rules;
2504: LOOP
2505: -- fetch get_sd_rules into lv_sd_start,lv_sd_stop;
2516: sd_date_worked := TRUNC(p_shift_adjusted_time_in - 1, 'DD');
2517:
2518: FOR i in 1 .. 3 LOOP
2519: if g_debug then
2520: hr_utility.set_location('hxt_time_summary.gen_details',23.5);
2521: hr_utility.trace('sd_date_worked :'
2522: || to_char(sd_date_worked,'DD-MON-YYYY HH24:MI:SS'));
2523: end if;
2524: time_in_dates(ln_sd_start
2538: populate_plsql_table(sd_start_time);
2539: populate_plsql_table(sd_carryover_time);
2540:
2541: if g_debug then
2542: hr_utility.set_location('hxt_time_summary.gen_details',23.6);
2543: end if;
2544: sd_date_worked := TRUNC(sd_date_worked + 1, 'DD');
2545:
2546: END LOOP;
2545:
2546: END LOOP;
2547:
2548: if g_debug then
2549: hr_utility.set_location('hxt_time_summary.gen_details',23.8);
2550: end if;
2551: END LOOP;
2552: close get_sd_rules;
2553:
2551: END LOOP;
2552: close get_sd_rules;
2553:
2554: if g_debug then
2555: hr_utility.set_location('hxt_time_summary.gen_details',24);
2556:
2557: hr_utility.trace('FYI');
2558: end if;
2559: if segment_chunks.count <> 0 then
2557: hr_utility.trace('FYI');
2558: end if;
2559: if segment_chunks.count <> 0 then
2560: if g_debug then
2561: hr_utility.set_location('hxt_time_summary.gen_details',26);
2562: end if;
2563: for l_cnt in segment_chunks.first .. segment_chunks.last loop
2564: if g_debug then
2565: hr_utility.trace('segment_chunks is:'
2568: end loop;
2569: end if;
2570: if g_debug then
2571: hr_utility.trace('END FYI');
2572: hr_utility.set_location('hxt_time_summary.gen_details',27);
2573: end if;
2574: -- Get the sorted pl sql table
2575: sorted_chunks := sort( segment_chunks , 'ASC');
2576:
2578: hr_utility.trace('FYI');
2579: end if;
2580: if sorted_chunks.count <> 0 then
2581: if g_debug then
2582: hr_utility.set_location('hxt_time_summary.gen_details',28);
2583: end if;
2584: for l_cnt in sorted_chunks.first .. sorted_chunks.last loop
2585: if g_debug then
2586: hr_utility.trace('sorted_chunks is:'
2587: ||to_char(sorted_chunks(l_cnt),'DD-MON-YYYY HH24:MI:SS'));
2588: end if;
2589: end loop;
2590: if g_debug then
2591: hr_utility.set_location('hxt_time_summary.gen_details',29);
2592: end if;
2593: end if;
2594: if g_debug then
2595: hr_utility.trace('END FYI');
2597:
2598:
2599: if sorted_chunks.count > 0 then
2600: if g_debug then
2601: hr_utility.set_location('hxt_time_summary.gen_details',30);
2602: end if;
2603: for i in sorted_chunks.first .. sorted_chunks.last-1 loop
2604: if g_debug then
2605: hr_utility.set_location('hxt_time_summary.gen_details',31);
2601: hr_utility.set_location('hxt_time_summary.gen_details',30);
2602: end if;
2603: for i in sorted_chunks.first .. sorted_chunks.last-1 loop
2604: if g_debug then
2605: hr_utility.set_location('hxt_time_summary.gen_details',31);
2606: end if;
2607: chunk_start := sorted_chunks(i);
2608:
2609: if g_debug then
2624: ||to_char(chunk_stop,'DD-MON-YYYY HH24:MI:SS'));
2625: end if;
2626: -- end if;
2627: if g_debug then
2628: hr_utility.set_location('hxt_time_summary.gen_details',32);
2629: end if;
2630: IF segment_earning(chunk_start
2631: ,chunk_stop
2632: ,p_sdp_earning_type
2632: ,p_sdp_earning_type
2633: ,p_sdovr_earning_type
2634: ) THEN
2635: if g_debug then
2636: hr_utility.set_location('hxt_time_summary.gen_details',33);
2637: end if;
2638: -- hours_worked:= ((chunk_stop - chunk_start) * 24);
2639: hours_worked:= ROUND((chunk_stop - chunk_start) * 24,3);
2640:
2639: hours_worked:= ROUND((chunk_stop - chunk_start) * 24,3);
2640:
2641: if g_debug then
2642: hr_utility.trace('hours_worked :'|| hours_worked);
2643: hr_utility.set_location('hxt_time_summary.gen_details',34);
2644: end if;
2645: return_code := gen_special(location
2646: ,chunk_start
2647: ,chunk_stop
2648: ,hours_worked
2649: ,p_sdp_earning_type
2650: ,p_sdovr_earning_type);
2651: if g_debug then
2652: hr_utility.set_location('hxt_time_summary.gen_details',35);
2653: end if;
2654: END IF; -- segment_earning
2655:
2656: end loop;
2655:
2656: end loop;
2657:
2658: if g_debug then
2659: hr_utility.set_location('hxt_time_summary.gen_details',36);
2660: end if;
2661:
2662: end if;
2663:
2665:
2666: /*
2667:
2668: if g_debug then
2669: hr_utility.set_location('hxt_time_summary.gen_details',30);
2670: end if;
2671: segment_stop_time := p_shift_adjusted_time_in;
2672: if g_debug then
2673: hr_utility.trace('p_shift_adjusted_time_in :'
2681: end if;
2682: WHILE (segment_stop_time < p_shift_adjusted_time_out) AND return_code = 0
2683: LOOP
2684: if g_debug then
2685: hr_utility.set_location('hxt_time_summary.gen_details',40);
2686: end if;
2687: --while the end of the last segment is not end of time
2688: segment_start_time := segment_stop_time; -- start each segment with
2689: -- stop time of last
2692: ||to_char(segment_start_time,'DD-MON-YYYY HH24:MI:SS'));
2693: end if;
2694: IF segment_start_in_rule(sd_rule_earning,sd_rule_carryover) THEN
2695: if g_debug then
2696: hr_utility.set_location('hxt_time_summary.gen_details',50);
2697: end if;
2698: -- if the start is in a rule
2699: if g_debug then
2700: hr_utility.trace('segment_stop_time :'
2709: hr_utility.trace('return_code :'||return_code);
2710: end if;
2711: ELSIF rule_start_in_segment(sd_rule_start) THEN
2712: if g_debug then
2713: hr_utility.set_location('hxt_time_summary.gen_details',60);
2714: end if;
2715: -- if a rule starts in the segment
2716: return_code := set_stop_at_rule(segment_stop_time,hours_worked);
2717: if g_debug then
2737: -- from 1200 to 1700 (shift diff defined from 0800 to 1200) and
2738: -- shift diff Override from 1700 to 2300
2739: IF to_number(to_char(segment_stop_time,'HH24MI')) < lv_standard_stop THEN
2740: if g_debug then
2741: hr_utility.set_location('hxt_time_summary.gen_details',75);
2742: end if;
2743: -- segment_stop_time := lv_standard_stop;
2744: segment_stop_time := to_date((to_char(g_date_worked, 'DDMMYYYY')||lv_standard_stop), 'DDMMYYYYHH24MI');
2745: if g_debug then
2754: end if;
2755: sd_rule_earning := NULL;
2756: ELSE -- segment_stop_time >= lv_standard_stop
2757: if g_debug then
2758: hr_utility.set_location('hxt_time_summary.gen_details',76);
2759: end if;
2760: segment_stop_time := p_shift_adjusted_time_out;
2761: if g_debug then
2762: hr_utility.trace('segment_stop_time :'
2774: -- logic gets called to insert the data into hxt_det_hours_worked
2775: sd_rule_earning := g_sdovr_id;
2776: END IF;
2777: if g_debug then
2778: hr_utility.set_location('hxt_time_summary.gen_details',77);
2779: end if;
2780: -- END g_sdovr_id is NOT NULL
2781:
2782: ELSE
2787: hr_utility.trace('segment_stop_time :'
2788: ||to_char(segment_stop_time,'DD-MON-YYYY HH24:MI:SS'));
2789: hr_utility.trace('p_shift_adjusted_time_out :'
2790: ||to_char(p_shift_adjusted_time_out,'DD-MON-YYYY HH24:MI:SS'));
2791: hr_utility.set_location('hxt_time_summary.gen_details',78);
2792: end if;
2793: segment_stop_time := p_shift_adjusted_time_out;
2794: if g_debug then
2795: hr_utility.trace('segment_stop_time :'
2815: hr_utility.trace('return_code :'||return_code);
2816: end if;
2817: IF return_code = 0 THEN
2818: if g_debug then
2819: hr_utility.set_location('hxt_time_summary.gen_details',80);
2820: end if;
2821: loop_count := loop_count + 1;
2822: if g_debug then
2823: hr_utility.trace('loop_count :'||loop_count);
2829: ,sd_rule_earning);
2830: END IF;
2831: IF loop_count > 50 THEN
2832: if g_debug then
2833: hr_utility.set_location('hxt_time_summary.gen_details',90);
2834: end if;
2835: FND_MESSAGE.SET_NAME('HXT','HXT_39300_GEN_DTAIL_EXC_LOOP'); -- HXT11
2836: return_code := call_gen_error(location, ''); -- HXT11
2837: END IF;
2836: return_code := call_gen_error(location, ''); -- HXT11
2837: END IF;
2838: IF return_code > 0 THEN
2839: if g_debug then
2840: hr_utility.set_location('hxt_time_summary.gen_details',100);
2841: end if;
2842: EXIT;
2843: END IF;
2844: END LOOP;
2845: */
2846:
2847:
2848: if g_debug then
2849: hr_utility.set_location('hxt_time_summary.gen_details',40);
2850: end if;
2851: END IF; -- there is a shift diff policy
2852:
2853: if g_debug then
2850: end if;
2851: END IF; -- there is a shift diff policy
2852:
2853: if g_debug then
2854: hr_utility.set_location('hxt_time_summary.gen_details',45);
2855: end if;
2856: RETURN return_code;
2857:
2858: EXCEPTION
2858: EXCEPTION
2859:
2860: WHEN OTHERS THEN
2861: if g_debug then
2862: hr_utility.set_location('hxt_time_summary.gen_details',110);
2863: end if;
2864: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR'); -- HXT11
2865: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm); -- HXT11
2866: --2278400 RETURN call_gen_error(location, '', sqlerrm); -- HXT11
2887:
2888: BEGIN
2889:
2890: if g_debug then
2891: hr_utility.set_location('hxt_time_summary.Get_Include',10);
2892: end if;
2893: -- Check if no earn group passed
2894: IF p_egt_id is null THEN
2895: if g_debug then
2892: end if;
2893: -- Check if no earn group passed
2894: IF p_egt_id is null THEN
2895: if g_debug then
2896: hr_utility.set_location('hxt_time_summary.Get_Include',20);
2897: end if;
2898: RETURN l_retcode;
2899: ELSE
2900: BEGIN
2898: RETURN l_retcode;
2899: ELSE
2900: BEGIN
2901: if g_debug then
2902: hr_utility.set_location('hxt_time_summary.Get_Include',30);
2903: end if;
2904: -- Check if element_type exists in earning group
2905: SELECT 'Y'
2906: INTO l_retcode
2918:
2919: EXCEPTION
2920: WHEN no_data_found THEN
2921: if g_debug then
2922: hr_utility.set_location('hxt_time_summary.Get_Include',40);
2923: end if;
2924: RETURN l_retcode;
2925: END;
2926: END IF;
2928: EXCEPTION
2929: -- Return Oracle error number
2930: WHEN others THEN
2931: if g_debug then
2932: hr_utility.set_location('hxt_time_summary.Get_Include',50);
2933: end if;
2934: FND_MESSAGE.SET_NAME('HXT','HXT_39270_OR_ERR_G_GROUP'); -- HXT11
2935: l_error_code := call_hxthxc_gen_error('HXT','HXT_39270_OR_ERR_G_GROUP',l_location,'', sqlerrm); -- HXT11
2936: --2278400 l_error_code := call_gen_error(l_location,'', sqlerrm); -- HXT11
2948:
2949: BEGIN
2950:
2951: if g_debug then
2952: hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',10);
2953: hr_utility.trace('p_time_in :'||to_char(p_time_in,'DD-MON-YYYY HH24:MI:SS'));
2954: hr_utility.trace('p_time_out :'||to_char(p_time_out,'DD-MON-YYYY HH24:MI:SS'));
2955: hr_utility.trace('p_hours_worked :'||p_hours_worked);
2956: hr_utility.trace('p_shift_diff_earning_id :'||p_shift_diff_earning_id);
3015: --,g_GROUP_ID
3016: );
3017:
3018: if g_debug then
3019: hr_utility.set_location('hxt_time_summary.GEN_SPECIAL',20);
3020: end if;
3021: END; -- gen special
3022:
3023: -- begin