[Home] [Help]
3:
4: g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_TRIPS_PUB';
5: g_debug VARCHAR2(1);
6: g_debug_level NUMBER;
7: g_level_cp_output CONSTANT NUMBER := fnd_log.level_unexpected + 1;
8:
9: g_hours_in_day CONSTANT NUMBER := 24;
10: g_mins_in_day CONSTANT NUMBER := 24 * 60;
11: g_secs_in_day CONSTANT NUMBER := 24 * 60 * 60;
99: ELSE
100: fnd_file.put_line(fnd_file.log, rpad(p_module, 20) || ': ' || p_message);
101: END IF;
102: END IF;
103: IF ( p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
104: THEN
105: fnd_log.string(p_level, 'csf.plsql.CSF_TRIPS_PUB.' || p_module, p_message);
106: END IF;
107: END IF;
101: END IF;
102: END IF;
103: IF ( p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
104: THEN
105: fnd_log.string(p_level, 'csf.plsql.CSF_TRIPS_PUB.' || p_module, p_message);
106: END IF;
107: END IF;
108: --dbms_output.put_line(rpad(p_module, 20) || ': ' || p_message);
109: END debug;
146: l_api_name CONSTANT VARCHAR2(30) := 'CHECK_TIME_OVERLAPS';
147: l_debug CONSTANT BOOLEAN := g_debug = 'Y';
148: BEGIN
149: IF l_debug THEN
150: debug( ' Inside procedure CHECK TIME OVERLAP', l_api_name, fnd_log.level_statement);
151: debug( ' Overtime value # '||g_overtime, l_api_name, fnd_log.level_statement);
152: debug( ' Checking for overlap in trip start#' || format_date(p_trip.start_date_time) ||
153: ' is <= shift endtime + overtime #'|| format_date(p_shift.end_datetime + g_overtime) ||
154: ' and overlap in trip end # '||format_date(p_trip.end_date_time + g_overtime)||
147: l_debug CONSTANT BOOLEAN := g_debug = 'Y';
148: BEGIN
149: IF l_debug THEN
150: debug( ' Inside procedure CHECK TIME OVERLAP', l_api_name, fnd_log.level_statement);
151: debug( ' Overtime value # '||g_overtime, l_api_name, fnd_log.level_statement);
152: debug( ' Checking for overlap in trip start#' || format_date(p_trip.start_date_time) ||
153: ' is <= shift endtime + overtime #'|| format_date(p_shift.end_datetime + g_overtime) ||
154: ' and overlap in trip end # '||format_date(p_trip.end_date_time + g_overtime)||
155: ' is >= starttime + overtime # '||format_date(p_shift.start_datetime), l_api_name, fnd_log.level_statement);
151: debug( ' Overtime value # '||g_overtime, l_api_name, fnd_log.level_statement);
152: debug( ' Checking for overlap in trip start#' || format_date(p_trip.start_date_time) ||
153: ' is <= shift endtime + overtime #'|| format_date(p_shift.end_datetime + g_overtime) ||
154: ' and overlap in trip end # '||format_date(p_trip.end_date_time + g_overtime)||
155: ' is >= starttime + overtime # '||format_date(p_shift.start_datetime), l_api_name, fnd_log.level_statement);
156: debug( ' Outside procedure CHECK TIME OVERLAP', l_api_name, fnd_log.level_statement);
157: END IF;
158: RETURN p_trip.start_date_time <= (p_shift.end_datetime + g_overtime)
159: AND (p_trip.end_date_time + g_overtime) >= p_shift.start_datetime;
152: debug( ' Checking for overlap in trip start#' || format_date(p_trip.start_date_time) ||
153: ' is <= shift endtime + overtime #'|| format_date(p_shift.end_datetime + g_overtime) ||
154: ' and overlap in trip end # '||format_date(p_trip.end_date_time + g_overtime)||
155: ' is >= starttime + overtime # '||format_date(p_shift.start_datetime), l_api_name, fnd_log.level_statement);
156: debug( ' Outside procedure CHECK TIME OVERLAP', l_api_name, fnd_log.level_statement);
157: END IF;
158: RETURN p_trip.start_date_time <= (p_shift.end_datetime + g_overtime)
159: AND (p_trip.end_date_time + g_overtime) >= p_shift.start_datetime;
160: END time_overlaps;
277: || ' between ' || format_date(g_messages(i).start_datetime)
278: || ' and ' || format_date(g_messages(i).end_datetime)
279: || ' processed successfully'
280: , 'PROCESS_ACTION'
281: , fnd_log.level_event
282: );
283: END IF;
284: l_success := l_success + 1;
285: END IF;
325: g_overtime := NVL(CSR_SCHEDULER_PUB.GET_SCH_PARAMETER_VALUE('spMaxOvertime'), 0) / g_mins_in_day;
326:
327:
328: g_debug := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
329: g_debug_level := NVL(fnd_profile.value_specific('AFLOG_LEVEL'), fnd_log.level_event);
330: g_datetime_fmt_mask := NVL(fnd_profile.value('ICX_DATE_FORMAT_MASK'), 'DD-MON-YYYY') || ' HH24:MI';
331: g_tz_enabled := fnd_timezones.timezones_enabled;
332: g_server_tz_code := fnd_timezones.get_server_timezone_code;
333: g_client_tz_code := fnd_timezones.get_client_timezone_code;
348: --EXECUTE IMMEDIATE 'alter session set statistics_level=all';
349: --EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
350: EXCEPTION
351: WHEN OTHERS THEN
352: debug('Unable to initialize the Package - SQLCODE = ' || SQLCODE || ' : SQLERRM = ' || SQLERRM, 'INIT', fnd_log.level_unexpected);
353: END;
354:
355: FUNCTION trip_has_active_tasks(p_trip_id NUMBER)
356: RETURN BOOLEAN IS
508: , p_res_shift_add => g_res_add_prof
509: );
510:
511: IF l_debug THEN
512: debug(' Got the Party Site ID ' || l_address.party_site_id || ' for the resource on ' || p_start_date_time, l_api_name, fnd_log.level_statement);
513: END IF;
514:
515: IF l_address.party_site_id IS NULL THEN
516: IF l_debug THEN
514:
515: IF l_address.party_site_id IS NULL THEN
516: IF l_debug THEN
517: x_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
518: debug(' CSF_RESOURCE_ADDRESS_PVT failed to give Party Site ID' || x_msg_data, l_api_name, fnd_log.level_error);
519: END IF;
520: fnd_message.set_name('CSF', 'CSF_RESOURCE_NO_ACTIVE_PARTY');
521: fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
522: fnd_message.set_token('DATE', format_date(p_start_date_time));
577: RAISE fnd_api.g_exc_error;
578: END IF;
579:
580: IF l_debug THEN
581: debug(' Created Departure Task - Task ID = ' || x_dep_task_id, l_api_name, fnd_log.level_statement);
582: END IF;
583: END IF; -- end if for l_row_obj_cap
584: END IF;
585:
629: RAISE fnd_api.g_exc_error;
630: END IF;
631:
632: IF l_debug THEN
633: debug(' Created Arrival Task - Task ID = ' || x_arr_task_id, l_api_name, fnd_log.level_statement);
634: END IF;
635: END IF;-- end if for l_row_obj_cap
636: END IF;
637: EXCEPTION
737:
738: x_return_status := fnd_api.g_ret_sts_success;
739:
740: IF l_debug THEN
741: debug(' Creating Trip between ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
742: END IF;
743:
744: -- Trip Available Hours
745: l_available_hours := (p_end_date_time - p_start_date_time) * g_hours_in_day;
748: IF l_available_hours > g_hours_in_day THEN
749: IF check_dst(p_resource_id,p_start_date_time,p_end_date_time) = 'FALSE'
750: THEN
751: IF l_debug THEN
752: debug(' The specified Trip Length is greater than one day', l_api_name, fnd_log.level_error);
753: END IF;
754: fnd_message.set_name('CSF', 'CSF_TRIP_LENGTH_MORE_THAN_DAY');
755: fnd_msg_pub.ADD;
756: RAISE fnd_api.g_exc_error;
779: );
780:
781: IF x_return_status <> fnd_api.g_ret_sts_success THEN
782: IF l_debug THEN
783: debug(' Unable to Create Shift Tasks: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
784: END IF;
785: IF x_return_status = fnd_api.g_ret_sts_error THEN
786: RAISE fnd_api.g_exc_error;
787: ELSE
788: RAISE fnd_api.g_exc_unexpected_error;
789: END IF;
790: END IF;
791: IF l_debug THEN
792: debug(' Created new Shift Tasks - Dep#' || l_dep_task_id || ' : Arr#' || l_arr_task_id, l_api_name, fnd_log.level_statement);
793: END IF;
794: l_dep_task_id := NVL(p_dep_task_id, l_dep_task_id);
795: l_arr_task_id := NVL(p_arr_task_id, l_arr_task_id);
796: ELSE
797: -- Use the existing ones.
798: l_dep_task_id := p_dep_task_id;
799: l_arr_task_id := p_arr_task_id;
800: IF l_debug THEN
801: debug(' Using existing Shift Tasks - Dep#' || l_dep_task_id || ' : Arr#' || l_arr_task_id, l_api_name, fnd_log.level_statement);
802: END IF;
803: END IF;
804: IF l_dep_task_id IS NOT NULL AND l_arr_task_id IS NOT NULL
805: THEN
810: --l_time_occupied := l_time_occupied + NVL(v_task.travel_time, 0) / g_mins_in_day; -- Scheduled Travel Duration
811: --l_available_hours := l_available_hours - l_time_occupied * g_hours_in_day;
812:
813: IF l_debug THEN
814: debug(' Linking TaskID #' || v_task.task_id || ' : Time Used = ' || l_time_occupied * g_hours_in_day, l_api_name, fnd_log.level_statement);
815: END IF;
816:
817: l_object_tasks_tbl(i).task_assignment_id := v_task.task_assignment_id;
818: l_object_tasks_tbl(i).task_assignment_ovn := v_task.object_version_number;
838: l_object_capacity_tbl(1).schedule_detail_id := p_schedule_detail_id;
839: l_object_capacity_tbl(1).availability_type := g_shift_type;
840:
841: IF l_debug THEN
842: debug(' Trip Available Hours = ' || l_available_hours, l_api_name, fnd_log.level_statement);
843: END IF;
844: IF l_debug THEN
845: debug(' No departure Arrival for dates ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
846: debug(' No departure Arrival for Resource ' || p_resource_id || ' and Resource Type' ||p_resource_type , l_api_name, fnd_log.level_statement);
841: IF l_debug THEN
842: debug(' Trip Available Hours = ' || l_available_hours, l_api_name, fnd_log.level_statement);
843: END IF;
844: IF l_debug THEN
845: debug(' No departure Arrival for dates ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
846: debug(' No departure Arrival for Resource ' || p_resource_id || ' and Resource Type' ||p_resource_type , l_api_name, fnd_log.level_statement);
847: END IF;
848:
849: -- Create the Trip by calling Object Capacity Table Handlers
842: debug(' Trip Available Hours = ' || l_available_hours, l_api_name, fnd_log.level_statement);
843: END IF;
844: IF l_debug THEN
845: debug(' No departure Arrival for dates ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
846: debug(' No departure Arrival for Resource ' || p_resource_id || ' and Resource Type' ||p_resource_type , l_api_name, fnd_log.level_statement);
847: END IF;
848:
849: -- Create the Trip by calling Object Capacity Table Handlers
850: cac_sr_object_capacity_pub.insert_object_capacity(
860:
861: IF x_return_status <> fnd_api.g_ret_sts_success THEN
862: IF l_debug THEN
863: x_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
864: debug(' Unable to Create the Object Capacity: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
865: END IF;
866: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
867: RAISE fnd_api.g_exc_unexpected_error;
868: END IF;
879: x_trip.status := p_status;
880: x_trip.schedule_detail_id := p_schedule_detail_id;
881:
882: IF l_debug THEN
883: debug(' Created Trip - TripID#' || x_trip.trip_id, l_api_name, fnd_log.level_statement);
884: END IF;
885: ELSE
886: IF l_debug THEN
887: debug(' No departure Arrival for dates ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
883: debug(' Created Trip - TripID#' || x_trip.trip_id, l_api_name, fnd_log.level_statement);
884: END IF;
885: ELSE
886: IF l_debug THEN
887: debug(' No departure Arrival for dates ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
888: debug(' No departure Arrival for Resource ' || p_resource_id || ' and Resource Type' ||p_resource_type , l_api_name, fnd_log.level_statement);
889: END IF;
890:
891: END IF; --END IF FOR l_dep_task_id is null
884: END IF;
885: ELSE
886: IF l_debug THEN
887: debug(' No departure Arrival for dates ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
888: debug(' No departure Arrival for Resource ' || p_resource_id || ' and Resource Type' ||p_resource_type , l_api_name, fnd_log.level_statement);
889: END IF;
890:
891: END IF; --END IF FOR l_dep_task_id is null
892:
895: ROLLBACK TO csf_new_trip;
896: x_return_status := fnd_api.g_ret_sts_error;
897: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
898: WHEN fnd_api.g_exc_unexpected_error THEN
899: debug('Unepected error occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
900: ROLLBACK TO csf_new_trip;
901: if SQLCODE =1 then
902: x_return_status := fnd_api.g_ret_sts_error;
903: else
904: x_return_status := fnd_api.g_ret_sts_unexp_error;
905: end if;
906: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
907: WHEN OTHERS THEN
908: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
909: x_return_status := fnd_api.g_ret_sts_unexp_error;
910: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
911: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
912: END IF;
1002: OR (l_available_hours_after <> fnd_api.g_miss_num AND l_available_hours_after > l_shift_length)
1003: THEN
1004: -- Trip Availability is more than the Shift Length
1005: IF l_debug THEN
1006: debug(' Trip Availability is more than Shift Length', l_api_name, fnd_log.level_error);
1007: END IF;
1008:
1009: fnd_message.set_name('CSF', 'CSF_TRIP_WRONG_AVAILABILITY');
1010: fnd_message.set_token('AVAILABLE', l_available_hours);
1020: )
1021: THEN
1022: -- Trip Availability is equal to the Shift Length and Before and Afters are not NULL
1023: IF l_debug THEN
1024: debug(' Available Hours Before and After must be NULL when Availability is Trip Length', l_api_name, fnd_log.level_error);
1025: END IF;
1026:
1027: fnd_message.set_name('CSF', 'CSF_TRIP_WRONG_AVL_BEFOREAFTER');
1028: fnd_message.set_token('AVLBEFORE', l_available_hours_before);
1049: );
1050:
1051: IF x_return_status <> fnd_api.g_ret_sts_success THEN
1052: IF l_debug THEN
1053: debug(' Unable to Update the Object Capacity', l_api_name, fnd_log.level_error);
1054: END IF;
1055: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1056: RAISE fnd_api.g_exc_unexpected_error;
1057: END IF;
1079: FOR v_task IN c_tasks(l_task_type) LOOP
1080: l_new_task_status := get_new_task_status(p_task_action, v_task.assignment_status_id);
1081: IF l_new_task_status IS NOT NULL THEN
1082: IF l_debug THEN
1083: debug(' Updating the Task - TaskID# ' || v_task.task_id, l_api_name, fnd_log.level_statement);
1084: END IF;
1085: csf_task_assignments_pub.update_assignment_status(
1086: p_api_version => 1.0
1087: , p_init_msg_list => fnd_api.g_false
1101: -- Somehow direct population of the Token using fnd_msg_pub is not working
1102: -- Therefore populating it in x_msg_data and using it to populate the Token REASON.
1103: x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false);
1104: IF l_debug THEN
1105: debug(' Unable to update the Assignment: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
1106: END IF;
1107: fnd_message.set_name('CSF', 'CSF_ASSIGNMENT_UPDATE_FAIL');
1108: fnd_message.set_token('TASK', v_task.task_number);
1109: fnd_message.set_token('REASON', x_msg_data);
1124: ROLLBACK TO csf_change_trip;
1125: x_return_status := fnd_api.g_ret_sts_unexp_error;
1126: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1127: WHEN OTHERS THEN
1128: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1129: x_return_status := fnd_api.g_ret_sts_unexp_error;
1130: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1131: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1132: END IF;
1166: IF l_debug THEN
1167: debug( ' Deleting the Trip #' || p_trip.trip_id
1168: || ' between ' || format_date(p_trip.start_date_time)
1169: || ' and ' || format_date(p_trip.end_date_time)
1170: , l_api_name, fnd_log.level_procedure
1171: );
1172: END IF;
1173:
1174: -- Check whether the Trip is blocked
1173:
1174: -- Check whether the Trip is blocked
1175: IF p_trip.status = g_trip_unavailable THEN
1176: IF l_debug THEN
1177: debug(' The Trip is unavailable and so cant be deleted', l_api_name, fnd_log.level_error);
1178: END IF;
1179:
1180: fnd_message.set_name('CSF', 'CSF_TRIP_IS_BLOCKED');
1181: fnd_msg_pub.ADD;
1186: IF NVL(p_check_active_tasks, fnd_api.g_true) = fnd_api.g_true THEN
1187: IF trip_has_active_tasks(p_trip.trip_id) THEN
1188: -- There are Active Task Assignments for the Trip.
1189: IF l_debug THEN
1190: debug(' Trip has active Tasks and so cant be deleted', l_api_name, fnd_log.level_error);
1191: END IF;
1192:
1193: fnd_message.set_name('CSF', 'CSF_TRIP_HAS_ACTIVE_TASKS');
1194: fnd_msg_pub.ADD;
1198:
1199: -- Delete the Shift Tasks
1200: FOR v_shift_task IN c_shift_tasks LOOP
1201: IF l_debug THEN
1202: debug(' Deleting the Shift Task #' || v_shift_task.task_id ||' object version number ' || v_shift_task.object_version_number , l_api_name, fnd_log.level_statement);
1203: END IF;
1204: jtf_tasks_pub.delete_task(
1205: p_api_version => 1.0
1206: , x_return_status => x_return_status
1210: , p_object_version_number => v_shift_task.object_version_number
1211: );
1212: IF x_return_status <> fnd_api.g_ret_sts_success THEN
1213: IF l_debug THEN
1214: debug(' Unable to Delete the Shift Task id - ' || v_shift_task.task_id , l_api_name, fnd_log.level_error);
1215: END IF;
1216:
1217: fnd_message.set_name('CSF', 'CSF_TASK_DELETE_FAIL');
1218: fnd_message.set_token('TASK', v_shift_task.task_number);
1237: );
1238:
1239: IF x_return_status <> fnd_api.g_ret_sts_success THEN
1240: IF l_debug THEN
1241: debug(' Unable to Delete the Object Capacity', l_api_name, fnd_log.level_error);
1242: END IF;
1243: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1244: RAISE fnd_api.g_exc_unexpected_error;
1245: END IF;
1246: RAISE fnd_api.g_exc_error;
1247: END IF;
1248:
1249: IF l_debug THEN
1250: debug(' Deleted the Trip', l_api_name, fnd_log.level_statement);
1251: END IF;
1252: EXCEPTION
1253: WHEN fnd_api.g_exc_error THEN
1254: ROLLBACK TO csf_remove_trip;
1256: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1257: WHEN fnd_api.g_exc_unexpected_error THEN
1258: ROLLBACK TO csf_remove_trip;
1259: IF l_debug THEN
1260: debug('Unable to Delete the Object Capacity: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1261: END IF;
1262: if SQLCODE =1 then
1263: x_return_status := fnd_api.g_ret_sts_error;
1264: else
1265: x_return_status := fnd_api.g_ret_sts_unexp_error;
1266: end if;
1267: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1268: WHEN OTHERS THEN
1269: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1270: x_return_status := fnd_api.g_ret_sts_unexp_error;
1271: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1272: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1273: END IF;
1385:
1386: x_return_status := fnd_api.g_ret_sts_success;
1387:
1388: IF l_debug THEN
1389: debug(' Checking Shift Tasks', l_api_name, fnd_log.level_statement);
1390: END IF;
1391:
1392: -- Clean up the Shift Tasks for the Trip.
1393: l_dep_task_exists := FALSE;
1394: l_arr_task_exists := FALSE;
1395: FOR v_shift_task IN c_shift_tasks LOOP
1396: IF v_shift_task.duplicate IS NOT NULL THEN
1397: IF l_debug THEN
1398: debug(' Deleting the Duplicate Shift Task #' || v_shift_task.task_id, l_api_name, fnd_log.level_statement);
1399: END IF;
1400: -- Departure Task already exists... Delete this Duplicate.
1401: jtf_tasks_pub.delete_task(
1402: p_api_version => 1.0
1408: );
1409: END IF;
1410: IF x_return_status <> fnd_api.g_ret_sts_success THEN
1411: IF l_debug THEN
1412: debug(' Unable to Delete the Task', l_api_name, fnd_log.level_error);
1413: END IF;
1414:
1415: fnd_message.set_name('CSF', 'CSF_TASK_DELETE_FAIL');
1416: fnd_message.set_token('TASK', v_shift_task.task_number);
1430: END LOOP;
1431:
1432: IF NOT(l_dep_task_exists) OR NOT(l_arr_task_exists) THEN
1433: IF l_debug THEN
1434: debug(' Either Departure or Arrival Task is absent. Creating them', l_api_name, fnd_log.level_statement);
1435: END IF;
1436:
1437: create_shift_tasks(
1438: p_api_version => 1.0
1450: );
1451:
1452: IF x_return_status <> fnd_api.g_ret_sts_success THEN
1453: IF l_debug THEN
1454: debug(' Creation of Shift Tasks failed', l_api_name, fnd_log.level_error);
1455: END IF;
1456: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1457: RAISE fnd_api.g_exc_unexpected_error;
1458: END IF;
1461: END IF;
1462:
1463: FOR v_task IN c_tasks LOOP
1464: IF l_debug THEN
1465: debug(' TaskID#' || v_task.task_id || ' is part of Trip#' || v_task.wrong_trip_id || '. But should be in Trip#' || v_task.correct_trip_id || '. Fixing the Task', l_api_name, fnd_log.level_statement);
1466: END IF;
1467:
1468: jtf_task_assignments_pub.update_task_assignment(
1469: p_api_version => 1.0
1522: ROLLBACK TO csf_correct_trip;
1523: x_return_status := fnd_api.g_ret_sts_unexp_error;
1524: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1525: WHEN OTHERS THEN
1526: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1527: x_return_status := fnd_api.g_ret_sts_unexp_error;
1528: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1529: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1530: END IF;
1576: BEGIN
1577: x_return_status := fnd_api.g_ret_sts_success;
1578:
1579: IF l_debug THEN
1580: debug('Deleting the given Trips', l_api_name, fnd_log.level_procedure);
1581: END IF;
1582:
1583: FOR i IN 1..p_trips.COUNT LOOP
1584: IF p_trips(i).availability_type = g_shift_type OR g_shift_type is null
1613: WHEN fnd_api.g_exc_unexpected_error THEN
1614: x_return_status := fnd_api.g_ret_sts_unexp_error;
1615: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1616: WHEN OTHERS THEN
1617: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1618: x_return_status := fnd_api.g_ret_sts_unexp_error;
1619: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1620: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1621: END IF;
1633: BEGIN
1634: x_return_status := fnd_api.g_ret_sts_success;
1635:
1636: IF l_debug THEN
1637: debug('Fixing the given Trips', l_api_name, fnd_log.level_procedure);
1638: END IF;
1639:
1640: FOR i IN 1..p_trips.COUNT LOOP
1641: correct_trip(
1667: WHEN fnd_api.g_exc_unexpected_error THEN
1668: x_return_status := fnd_api.g_ret_sts_unexp_error;
1669: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1670: WHEN OTHERS THEN
1671: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1672: x_return_status := fnd_api.g_ret_sts_unexp_error;
1673: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1674: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1675: END IF;
1795: l_res_type := p_resource_tbl(1).resource_type;
1796:
1797: IF l_debug THEN
1798: IF p_delete_trips THEN
1799: debug('Replacing Trips for Resource#' || l_res_id || ' between ' || p_start_date || ' and ' || p_end_date, l_api_name, fnd_log.level_procedure);
1800: ELSE
1801: debug('Creating Trips for Resource#' || l_res_id || ' between ' || p_start_date || ' and ' || p_end_date, l_api_name, fnd_log.level_procedure);
1802: END IF;
1803: END IF;
1797: IF l_debug THEN
1798: IF p_delete_trips THEN
1799: debug('Replacing Trips for Resource#' || l_res_id || ' between ' || p_start_date || ' and ' || p_end_date, l_api_name, fnd_log.level_procedure);
1800: ELSE
1801: debug('Creating Trips for Resource#' || l_res_id || ' between ' || p_start_date || ' and ' || p_end_date, l_api_name, fnd_log.level_procedure);
1802: END IF;
1803: END IF;
1804:
1805: -- Get the Resource's Shifts
1817: );
1818:
1819: IF x_return_status <> fnd_api.g_ret_sts_success THEN
1820: IF l_debug THEN
1821: debug(' No Shifts were found for the resource between the timeframe', l_api_name, fnd_log.level_error);
1822: END IF;
1823: add_message(
1824: p_res_id => l_res_id
1825: , p_res_type => l_res_type
1838: -- Check whether Shift Tasks are already there between the given Time Frame.
1839: l_start := l_shifts(l_shifts.FIRST).start_datetime;
1840: l_end := l_shifts(l_shifts.LAST).end_datetime;
1841:
1842: debug('Shift Start time : '|| to_char(l_start,'dd/mm/yyyy hh24:mi') || 'Shift End time : '|| to_char(l_end,'dd/mm/yyyy hh24:mi'), l_api_name, fnd_log.level_procedure);
1843: OPEN c_shift_tasks_exist(l_res_id, l_res_type, l_start, l_end);
1844: FETCH c_shift_tasks_exist INTO l_check_failed;
1845: IF c_shift_tasks_exist%NOTFOUND THEN
1846: l_check_failed := 'N';
1849:
1850: IF l_check_failed = 'Y' THEN
1851: -- Shift Tasks exists. Should use "Upgrade to Trips" API rather than "Create Trip".
1852: IF l_debug THEN
1853: debug(' Shift tasks are present between the timeframe', l_api_name, fnd_log.level_error);
1854: END IF;
1855: add_message(
1856: p_res_id => l_res_id
1857: , p_res_type => l_res_type
1871: l_old_trips := find_trips(p_resource_tbl, l_start, l_end);
1872: -- Bug 13072931
1873: l_old_trips_temp := find_trips(p_resource_tbl, l_start, l_end);
1874: IF l_debug THEN
1875: debug(' Current Trips existing: Count = ' || l_old_trips.COUNT, l_api_name, fnd_log.level_statement);
1876: FOR i IN 1..l_old_trips.COUNT LOOP
1877: debug( ' Trip ID = ' || l_old_trips(i).trip_id
1878: || ' Start Time = ' || format_date(l_old_trips(i).start_date_time)
1879: || ' End Time = ' || format_date(l_old_trips(i).end_date_time)
1876: FOR i IN 1..l_old_trips.COUNT LOOP
1877: debug( ' Trip ID = ' || l_old_trips(i).trip_id
1878: || ' Start Time = ' || format_date(l_old_trips(i).start_date_time)
1879: || ' End Time = ' || format_date(l_old_trips(i).end_date_time)
1880: , l_api_name, fnd_log.level_statement);
1881: END LOOP;
1882: END IF;
1883:
1884: l_del_trip_tbl := jtf_number_table();
1889: IF l_debug THEN
1890: debug( ' Trying to create trip for shift between '
1891: || format_date(l_shifts(l_shift_idx).start_datetime) || ' and '
1892: || format_date(l_shifts(l_shift_idx).end_datetime)
1893: , l_api_name, fnd_log.level_statement
1894: );
1895: END IF;
1896: IF l_shifts(l_shift_idx).start_datetime > p_start_date
1897: THEN
1908: debug( ' Trying to create trip for shift between '
1909: || format_date(l_start) || ' and '
1910: || format_date(l_end) || ' and shift type :'
1911: || g_shift_type
1912: , l_api_name, fnd_log.level_statement
1913: );
1914: END IF;
1915: -- Loop through each trip and check for overlap with any of the current trips
1916: l_trip_idx := l_old_trips.FIRST;
1916: l_trip_idx := l_old_trips.FIRST;
1917: WHILE l_trip_idx IS NOT NULL LOOP
1918: IF l_debug THEN
1919: debug(' Checking for overlap with old trip ' || l_old_trips(l_trip_idx).trip_id ||
1920: ' Object version number :'|| l_old_trips(l_trip_idx).object_version_number , l_api_name, fnd_log.level_statement);
1921: END IF;
1922:
1923:
1924:
1928: IF NOT p_delete_trips
1929: OR NOT time_overlaps(l_old_trips(l_trip_idx), p_start_date, p_end_date)
1930: THEN
1931: IF l_debug THEN
1932: debug(' Error : CSF_TRIP_CREATE_FAIL_DUP ' , l_api_name, fnd_log.level_error);
1933: END IF;
1934: l_msg_name := 'CSF_TRIP_CREATE_FAIL_DUP';
1935: RAISE fnd_api.g_exc_error;
1936: END IF;
1934: l_msg_name := 'CSF_TRIP_CREATE_FAIL_DUP';
1935: RAISE fnd_api.g_exc_error;
1936: END IF;
1937: IF l_debug THEN
1938: debug(' Time Overlaps so calling remove trip ' , l_api_name, fnd_log.level_statement);
1939: END IF;
1940: remove_trip(
1941: x_return_status => x_return_status
1942: , x_msg_data => x_msg_data
1947: );
1948:
1949: IF x_return_status <> fnd_api.g_ret_sts_success THEN
1950: IF l_debug THEN
1951: debug(' remove_trip Error : CSF_TRIP_DELETE_FAIL_OTHER ' , l_api_name, fnd_log.level_error);
1952: END IF;
1953: l_msg_name := 'CSF_TRIP_DELETE_FAIL_OTHER';
1954: RAISE fnd_api.g_exc_error;
1955: END IF;
1971: l_trip_idx := l_new_trips.LAST;
1972: WHILE l_trip_idx IS NOT NULL LOOP
1973: IF l_debug THEN
1974: debug(' Checking for overlap with new trip ' || l_new_trips(l_trip_idx).trip_id
1975: , l_api_name, fnd_log.level_statement);
1976: END IF;
1977: IF time_overlaps(l_new_trips(l_trip_idx), l_shifts(l_shift_idx)) THEN
1978: l_msg_name := 'CSF_TRIP_CREATE_FAIL_DUP';
1979: RAISE fnd_api.g_exc_error;
2046: IF l_debug THEN
2047: IF l_msg_name = 'CSF_TRIP_CREATE_FAIL_DUP' THEN
2048: IF NOT p_delete_trips THEN
2049: debug( ' Since delete trips not allowed.. we have overlap with existing trip'
2050: , l_api_name, fnd_log.level_error
2051: );
2052: ELSE
2053: debug( ' Delete trips allowed.. but we have conflict with new trip'
2054: , l_api_name, fnd_log.level_error
2050: , l_api_name, fnd_log.level_error
2051: );
2052: ELSE
2053: debug( ' Delete trips allowed.. but we have conflict with new trip'
2054: , l_api_name, fnd_log.level_error
2055: );
2056: END IF;
2057: ELSIF l_msg_name = 'CSF_TRIP_CREATE_FAIL_OTHER' THEN
2058: debug( ' Error occurred while creating the trip between '
2057: ELSIF l_msg_name = 'CSF_TRIP_CREATE_FAIL_OTHER' THEN
2058: debug( ' Error occurred while creating the trip between '
2059: || format_date(l_start) || ' and ' || format_date(l_end)
2060: || ' : Error = ' || l_reason
2061: , l_api_name, fnd_log.level_error
2062: );
2063: ELSIF l_msg_name = 'CSF_TRIP_DELETE_FAIL_OTHER' THEN
2064: debug( ' Error occurred while deleting the trip between '
2065: || format_date(l_start) || ' and ' || format_date(l_end)
2063: ELSIF l_msg_name = 'CSF_TRIP_DELETE_FAIL_OTHER' THEN
2064: debug( ' Error occurred while deleting the trip between '
2065: || format_date(l_start) || ' and ' || format_date(l_end)
2066: || ' : Error = ' || l_reason
2067: , l_api_name, fnd_log.level_error
2068: );
2069: END IF;
2070: END IF;
2071:
2088:
2089: IF p_delete_trips THEN
2090: -- Link all the Unlinked Task Assignments to the corresponding shifts
2091: IF l_debug THEN
2092: debug(' Linking unlinked Task Assignments if any of old trips to new trips', l_api_name, fnd_log.level_statement);
2093: END IF;
2094:
2095: l_trip_length := 0;
2096: FOR v_task IN c_unlinked_tasks LOOP
2100: IF l_prev_trip_id <> l_new_trips(l_trip_idx).trip_id THEN
2101: IF l_debug THEN
2102: debug( ' Decreasing Trip#' || l_prev_trip_id
2103: || ' Capacity to be lesser by ' || l_trip_length*g_hours_in_day
2104: , l_api_name, fnd_log.level_statement
2105: );
2106: END IF;
2107:
2108: OPEN c_trip_info(l_prev_trip_id);
2123: );
2124:
2125: IF x_return_status <> fnd_api.g_ret_sts_success THEN
2126: IF l_debug THEN
2127: debug( ' Error updating Trip. ' || fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false), l_api_name, fnd_log.level_error);
2128: END IF;
2129: END IF;
2130:
2131: l_trip_length := 0;
2134: IF l_debug THEN
2135: debug( ' Linking Task ' || v_task.task_id
2136: || ' : Old Trip = ' || v_task.object_capacity_id
2137: || ' : New Trip = ' || l_new_trips(l_trip_idx).trip_id
2138: , l_api_name, fnd_log.level_statement
2139: );
2140: END IF;
2141:
2142: l_trip_length := l_trip_length
2156: );
2157:
2158: IF x_return_status <> fnd_api.g_ret_sts_success THEN
2159: IF l_debug THEN
2160: debug( ' Error updating Task Assignment', l_api_name, fnd_log.level_error);
2161: END IF;
2162: add_message(
2163: p_res_id => l_res_id
2164: , p_res_type => l_res_type
2197: IF l_debug THEN
2198: debug( ' Deleting the non-overlapping Trip ' || l_old_trips_temp(l_trip_idx).trip_id
2199: || ' between ' || format_date(l_old_trips_temp(l_trip_idx).start_date_Time)
2200: || ' and ' || format_date(l_old_trips_temp(l_trip_idx).end_date_Time)
2201: , l_api_name, fnd_log.level_statement
2202: );
2203: END IF;
2204:
2205:
2208: debug( ' Cant delete trip' || l_old_trips_temp(l_trip_idx).trip_id
2209: || ' between ' || format_date(l_old_trips_temp(l_trip_idx).start_date_Time)
2210: || ' and ' || format_date(l_old_trips_temp(l_trip_idx).end_date_Time)
2211: || ' as there active tasks present'
2212: , l_api_name, fnd_log.level_error
2213: );
2214: END IF;
2215:
2216: add_message(l_old_trips_temp(l_trip_idx), NULL, 'CSF_TRIP_REPLACE_FAIL_ACTIVE', g_error_message);
2272: IF l_debug THEN
2273: debug( ' Deleting the Trip ' || l_old_trips(l_trip_idx).trip_id
2274: || ' between ' || format_date(l_old_trips(l_trip_idx).start_date_Time)
2275: || ' and ' || format_date(l_old_trips(l_trip_idx).end_date_Time)
2276: , l_api_name, fnd_log.level_statement
2277: );
2278: END IF;
2279:
2280: IF trip_has_active_tasks(l_old_trips(l_trip_idx).trip_id) THEN
2282: debug( ' Cant delete trip' || l_old_trips(l_trip_idx).trip_id
2283: || ' between ' || format_date(l_old_trips(l_trip_idx).start_date_Time)
2284: || ' and ' || format_date(l_old_trips(l_trip_idx).end_date_Time)
2285: || ' as there active tasks present'
2286: , l_api_name, fnd_log.level_error
2287: );
2288: END IF;
2289:
2290: add_message(l_old_trips(l_trip_idx), NULL, 'CSF_TRIP_REPLACE_FAIL_ACTIVE', g_error_message);
2333: ROLLBACK TO csf_create_trips;
2334: x_return_status := fnd_api.g_ret_sts_unexp_error;
2335: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2336: WHEN OTHERS THEN
2337: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2338: x_return_status := fnd_api.g_ret_sts_unexp_error;
2339: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2340: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2341: END IF;
2428: BEGIN
2429: x_return_status := fnd_api.g_ret_sts_success;
2430:
2431: IF l_debug THEN
2432: debug('Upgrading to Trips for Resource#' || p_resource_tbl(1).resource_id || ' between ' || p_start_date || ' and ' || p_end_date, l_api_name, fnd_log.level_procedure);
2433: END IF;
2434:
2435: -- Bulk Collecting all information about Shift Tasks.
2436: OPEN c_shift_tasks;
2438: CLOSE c_shift_tasks;
2439:
2440: FOR i IN 1..l_dep_task_tbl.COUNT LOOP
2441: IF l_debug THEN
2442: debug(' Found Shift Tasks - Dep #' || l_dep_task_tbl(i) || ' : Arr # ' || l_arr_task_tbl(i), l_api_name, fnd_log.level_procedure);
2443: END IF;
2444: -- Create a Trip between the Shift Tasks.
2445: new_trip(
2446: x_return_status => x_return_status
2484: WHEN fnd_api.g_exc_unexpected_error THEN
2485: x_return_status := fnd_api.g_ret_sts_unexp_error;
2486: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2487: WHEN OTHERS THEN
2488: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2489: x_return_status := fnd_api.g_ret_sts_unexp_error;
2490: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2491: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2492: END IF;
2508: BEGIN
2509: x_return_status := fnd_api.g_ret_sts_success;
2510:
2511: IF l_debug THEN
2512: debug('Updating the status of the given trips', l_api_name, fnd_log.level_procedure);
2513: END IF;
2514:
2515: IF p_trip_action IN (g_action_block_trip, g_action_close_trip) THEN
2516: l_new_trip_status := g_trip_unavailable;
2519: END IF;
2520:
2521: FOR i IN 1..p_trips.COUNT LOOP
2522: IF l_debug THEN
2523: debug('Updating Trip# ' || p_trips(i).trip_id, l_api_name, fnd_log.level_statement);
2524: END IF;
2525:
2526: IF l_new_trip_status = p_trips(i).status THEN
2527: IF l_debug THEN
2524: END IF;
2525:
2526: IF l_new_trip_status = p_trips(i).status THEN
2527: IF l_debug THEN
2528: debug(' Trip is already in correct status ' || p_trips(i).status, l_api_name, fnd_log.level_statement);
2529: END IF;
2530: GOTO NEXT_TRIP;
2531: END IF;
2532:
2531: END IF;
2532:
2533: IF p_trip_action = g_action_close_trip AND (p_trips(i).end_date_time + g_overtime) > SYSDATE THEN
2534: IF l_debug THEN
2535: debug(' Trip is present or future dated. Cant close', l_api_name, fnd_log.level_error);
2536: END IF;
2537: add_message(
2538: p_trip => p_trips(i)
2539: , p_msg_name => 'CSF_TRIP_CLOSE_FAIL_ACTIVE'
2543: END IF;
2544:
2545: IF p_trip_action = g_action_block_trip AND (p_trips(i).end_date_time + g_overtime) < SYSDATE THEN
2546: IF l_debug THEN
2547: debug(' Trip is past dated. Close it rather than blocking', l_api_name, fnd_log.level_statement);
2548: END IF;
2549: l_trip_action := g_action_close_trip;
2550: ELSE
2551: l_trip_action := p_trip_action;
2586: WHEN fnd_api.g_exc_unexpected_error THEN
2587: x_return_status := fnd_api.g_ret_sts_unexp_error;
2588: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2589: WHEN OTHERS THEN
2590: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2591: x_return_status := fnd_api.g_ret_sts_unexp_error;
2592: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2593: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2594: END IF;
2655:
2656: IF l_debug THEN
2657: debug('Creating a Trip for Resource#' || p_resource_id || ' between '
2658: || to_char(p_start_date_time, 'DD-MON-YYYY HH24:MI:SS') || ' and '
2659: || to_char(p_end_date_time, 'DD-MON-YYYY HH24:MI:SS'), l_api_name, fnd_log.level_procedure);
2660: END IF;
2661:
2662: l_resource := csf_resource_pub.resource_tbl_type();
2663: l_resource.extend();
2667:
2668: -- Check#1 - No Trips should be found for the given criteria
2669: IF l_trips.COUNT > 0 THEN
2670: IF l_debug THEN
2671: debug(' Trips already exists for the Resource in the specified interval', l_api_name, fnd_log.level_error);
2672: END IF;
2673: fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_DUP');
2674: fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
2675: fnd_message.set_token('START_TIME', format_date(p_start_date_time));
2679: END IF;
2680:
2681: -- Check#2 - No Shift Tasks in the Interval where the Trip is going to be created.
2682: IF l_debug THEN
2683: debug(' Searching for existence of any Shift Tasks in that interval', l_api_name, fnd_log.level_statement);
2684: END IF;
2685:
2686: OPEN c_st_exist;
2687: FETCH c_st_exist INTO l_shift_tasks_exist;
2691: CLOSE c_st_exist;
2692:
2693: IF l_shift_tasks_exist = 'Y' THEN
2694: IF l_debug THEN
2695: debug(' Shift Tasks exist for the Resource in the specified interval', l_api_name, fnd_log.level_error);
2696: END IF;
2697: fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_ST_EXIST');
2698: fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
2699: fnd_message.set_token('START_TIME', format_date(p_start_date_time));
2743: WHEN fnd_api.g_exc_unexpected_error THEN
2744: x_return_status := fnd_api.g_ret_sts_unexp_error;
2745: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2746: WHEN OTHERS THEN
2747: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2748: x_return_status := fnd_api.g_ret_sts_unexp_error;
2749: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2750: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2751: END IF;
2861: WHEN fnd_api.g_exc_unexpected_error THEN
2862: x_return_status := fnd_api.g_ret_sts_unexp_error;
2863: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2864: WHEN OTHERS THEN
2865: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2866: x_return_status := fnd_api.g_ret_sts_unexp_error;
2867: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2868: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2869: END IF;
2903: -- Initialize Return Status
2904: x_return_status := fnd_api.g_ret_sts_success;
2905:
2906: IF l_debug THEN
2907: debug('Deleting the Trip #' || p_trip_id, l_api_name, fnd_log.level_procedure);
2908: END IF;
2909:
2910: IF p_trip_id IS NULL OR p_trip_id = fnd_api.g_miss_num THEN
2911: -- Invalid Trip ID passed.
2945: IF x_return_status <> fnd_api.g_ret_sts_success THEN
2946: IF l_debug THEN
2947: debug( ' Unable to delete the Trip: Error = '
2948: || fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2949: , l_api_name, fnd_log.level_error);
2950: END IF;
2951: fnd_message.set_name('CSF', 'CSF_TRIP_DELETE_FAIL_OTHER');
2952: fnd_message.set_token('RESOURCE', get_resource_info(l_trip.resource_id, l_trip.resource_type));
2953: fnd_message.set_token('START_TIME', format_date(l_trip.start_date_time));
2973: x_return_status := fnd_api.g_ret_sts_unexp_error;
2974: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2975: WHEN OTHERS THEN
2976: ROLLBACK TO delete_trip;
2977: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2978: x_return_status := fnd_api.g_ret_sts_unexp_error;
2979: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2980: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2981: END IF;
3014: -- Initialize Return Status
3015: x_return_status := fnd_api.g_ret_sts_success;
3016:
3017: IF l_debug THEN
3018: debug('Fixing the Trip #' || p_trip_id, l_api_name, fnd_log.level_procedure);
3019: END IF;
3020:
3021: IF p_trip_id IS NULL OR p_trip_id = fnd_api.g_miss_num THEN
3022: -- Invalid Trip ID passed.
3053: );
3054:
3055: IF x_return_status <> fnd_api.g_ret_sts_success THEN
3056: IF l_debug THEN
3057: debug(' Unable to fix the Trip', l_api_name, fnd_log.level_error);
3058: END IF;
3059: fnd_message.set_name('CSF', 'CSF_TRIP_FIX_FAIL_OTHER');
3060: fnd_message.set_token('RESOURCE', get_resource_info(l_trip.resource_id, l_trip.resource_type));
3061: fnd_message.set_token('START_TIME', format_date(l_trip.start_date_time));
3078: WHEN fnd_api.g_exc_unexpected_error THEN
3079: x_return_status := fnd_api.g_ret_sts_unexp_error;
3080: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3081: WHEN OTHERS THEN
3082: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3083: x_return_status := fnd_api.g_ret_sts_unexp_error;
3084: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3085: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3086: END IF;
3156: WHEN fnd_api.g_exc_unexpected_error THEN
3157: x_return_status := fnd_api.g_ret_sts_unexp_error;
3158: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3159: WHEN OTHERS THEN
3160: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3161: x_return_status := fnd_api.g_ret_sts_unexp_error;
3162: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3163: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3164: END IF;
3224: WHEN fnd_api.g_exc_unexpected_error THEN
3225: x_return_status := fnd_api.g_ret_sts_unexp_error;
3226: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3227: WHEN OTHERS THEN
3228: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3229: x_return_status := fnd_api.g_ret_sts_unexp_error;
3230: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3231: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3232: END IF;
3274: WHEN fnd_api.g_exc_unexpected_error THEN
3275: x_return_status := fnd_api.g_ret_sts_unexp_error;
3276: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3277: WHEN OTHERS THEN
3278: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3279: x_return_status := fnd_api.g_ret_sts_unexp_error;
3280: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3281: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3282: END IF;
3325: -- Initialize Return Status
3326: x_return_status := fnd_api.g_ret_sts_success;
3327: l_shift_type := p_shift_type;
3328: IF l_debug THEN
3329: debug('Generating Resource Trips for a Resource', l_api_name, fnd_log.level_procedure);
3330: debug(' Action = ' || p_action, l_api_name, fnd_log.level_statement);
3331: IF p_trip_id IS NOT NULL THEN
3332: debug(' Trip ID = ' || p_trip_id, l_api_name, fnd_log.level_statement);
3333: END IF;
3326: x_return_status := fnd_api.g_ret_sts_success;
3327: l_shift_type := p_shift_type;
3328: IF l_debug THEN
3329: debug('Generating Resource Trips for a Resource', l_api_name, fnd_log.level_procedure);
3330: debug(' Action = ' || p_action, l_api_name, fnd_log.level_statement);
3331: IF p_trip_id IS NOT NULL THEN
3332: debug(' Trip ID = ' || p_trip_id, l_api_name, fnd_log.level_statement);
3333: END IF;
3334:
3328: IF l_debug THEN
3329: debug('Generating Resource Trips for a Resource', l_api_name, fnd_log.level_procedure);
3330: debug(' Action = ' || p_action, l_api_name, fnd_log.level_statement);
3331: IF p_trip_id IS NOT NULL THEN
3332: debug(' Trip ID = ' || p_trip_id, l_api_name, fnd_log.level_statement);
3333: END IF;
3334:
3335: IF p_start_date IS NOT NULL THEN
3336: debug(' Time Frame = ' || p_start_date || ' to ' || p_end_date, l_api_name, fnd_log.level_statement);
3332: debug(' Trip ID = ' || p_trip_id, l_api_name, fnd_log.level_statement);
3333: END IF;
3334:
3335: IF p_start_date IS NOT NULL THEN
3336: debug(' Time Frame = ' || p_start_date || ' to ' || p_end_date, l_api_name, fnd_log.level_statement);
3337: END IF;
3338:
3339: IF p_resource_tbl IS NOT NULL AND p_resource_tbl.COUNT = 1 THEN
3340: debug(' Resource = ' || p_resource_tbl(p_resource_tbl.FIRST).resource_id, l_api_name, fnd_log.level_statement);
3336: debug(' Time Frame = ' || p_start_date || ' to ' || p_end_date, l_api_name, fnd_log.level_statement);
3337: END IF;
3338:
3339: IF p_resource_tbl IS NOT NULL AND p_resource_tbl.COUNT = 1 THEN
3340: debug(' Resource = ' || p_resource_tbl(p_resource_tbl.FIRST).resource_id, l_api_name, fnd_log.level_statement);
3341: END IF;
3342: END IF;
3343:
3344: -- Checking whether all required parameters are passed.
3502: WHEN fnd_api.g_exc_unexpected_error THEN
3503: x_return_status := fnd_api.g_ret_sts_unexp_error;
3504: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3505: WHEN OTHERS THEN
3506: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3507: x_return_status := fnd_api.g_ret_sts_unexp_error;
3508: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3509: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3510: END IF;
3696: fnd_message.set_token('RESOURCE', l_resource_info);
3697: debug(fnd_message.get, 'GEN_RESOURCE_TRIPS', g_level_cp_output);
3698: IF l_debug THEN
3699: debug('*****Starting generating Trips for Resource ID #' || l_resource(1).resource_id||
3700: ' Resource Type #'|| l_resource(1).resource_type, l_api_name, fnd_log.level_statement);
3701: END IF;
3702: process_action(
3703: p_api_version => 1.0
3704: , p_init_msg_list => fnd_api.g_true
3809: -- Initialize Return Status
3810: x_return_status := fnd_api.g_ret_sts_success;
3811:
3812: IF l_debug THEN
3813: debug('CSF_TRIPS_PUB.Optimize Across Trips', l_api_name, fnd_log.level_procedure);
3814: debug(' No of resources in list = ' || p_resource_tbl.COUNT, l_api_name, fnd_log.level_statement);
3815: debug(' Time Frame = ' || p_start_date || ' to ' || p_end_date, l_api_name, fnd_log.level_statement);
3816: END IF;
3817:
3810: x_return_status := fnd_api.g_ret_sts_success;
3811:
3812: IF l_debug THEN
3813: debug('CSF_TRIPS_PUB.Optimize Across Trips', l_api_name, fnd_log.level_procedure);
3814: debug(' No of resources in list = ' || p_resource_tbl.COUNT, l_api_name, fnd_log.level_statement);
3815: debug(' Time Frame = ' || p_start_date || ' to ' || p_end_date, l_api_name, fnd_log.level_statement);
3816: END IF;
3817:
3818: l_resources_tbl := p_resource_tbl;
3811:
3812: IF l_debug THEN
3813: debug('CSF_TRIPS_PUB.Optimize Across Trips', l_api_name, fnd_log.level_procedure);
3814: debug(' No of resources in list = ' || p_resource_tbl.COUNT, l_api_name, fnd_log.level_statement);
3815: debug(' Time Frame = ' || p_start_date || ' to ' || p_end_date, l_api_name, fnd_log.level_statement);
3816: END IF;
3817:
3818: l_resources_tbl := p_resource_tbl;
3819: FOR i IN 1..l_resources_tbl.COUNT LOOP
3871: x_return_status := fnd_api.g_ret_sts_unexp_error;
3872: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3873: WHEN OTHERS THEN
3874: ROLLBACK;
3875: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3876: x_return_status := fnd_api.g_ret_sts_unexp_error;
3877: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3878: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3879: END IF;
3963: CLOSE c_trip_exist;
3964:
3965: IF (l_shift_tasks_exist = 'Y' or l_trip_exist = 'Y') THEN
3966: IF l_debug THEN
3967: debug(' Shift Tasks exist for the Resource in the specified interval', l_api_name, fnd_log.level_error);
3968: END IF;
3969: fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_ST_EXIST');
3970: fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
3971: fnd_message.set_token('START_TIME', format_date(p_start_date_time));
4032: WHEN fnd_api.g_exc_unexpected_error THEN
4033: x_return_status := fnd_api.g_ret_sts_unexp_error;
4034: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4035: WHEN OTHERS THEN
4036: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
4037: x_return_status := fnd_api.g_ret_sts_unexp_error;
4038: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4039: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4040: END IF;
4109:
4110: x_return_status := fnd_api.g_ret_sts_success;
4111:
4112: IF l_debug THEN
4113: debug(' Creating Trip between ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
4114: END IF;
4115:
4116: -- Trip Available Hours
4117: l_available_hours := (p_end_date_time - p_start_date_time) * g_hours_in_day;
4120: IF l_available_hours > g_hours_in_day THEN
4121: IF check_dst(p_resource_id,p_start_date_time,p_end_date_time) = 'FALSE'
4122: THEN
4123: IF l_debug THEN
4124: debug(' The specified Trip Length is greater than one day', l_api_name, fnd_log.level_error);
4125: END IF;
4126: fnd_message.set_name('CSF', 'CSF_TRIP_LENGTH_MORE_THAN_DAY');
4127: fnd_msg_pub.ADD;
4128: RAISE fnd_api.g_exc_error;
4155: );
4156:
4157: IF x_return_status <> fnd_api.g_ret_sts_success THEN
4158: IF l_debug THEN
4159: debug(' Unable to Create Shift Tasks: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
4160: END IF;
4161: IF x_return_status = fnd_api.g_ret_sts_error THEN
4162: RAISE fnd_api.g_exc_error;
4163: ELSE
4164: RAISE fnd_api.g_exc_unexpected_error;
4165: END IF;
4166: END IF;
4167: IF l_debug THEN
4168: debug(' Created new Shift Tasks - Dep#' || l_dep_task_id || ' : Arr#' || l_arr_task_id, l_api_name, fnd_log.level_statement);
4169: END IF;
4170: l_dep_task_id := NVL(p_dep_task_id, l_dep_task_id);
4171: l_arr_task_id := NVL(p_arr_task_id, l_arr_task_id);
4172: ELSE
4173: -- Use the existing ones.
4174: l_dep_task_id := p_dep_task_id;
4175: l_arr_task_id := p_arr_task_id;
4176: IF l_debug THEN
4177: debug(' Using existing Shift Tasks - Dep#' || l_dep_task_id || ' : Arr#' || l_arr_task_id, l_api_name, fnd_log.level_statement);
4178: END IF;
4179: END IF;
4180:
4181: i := 0;
4185: --l_time_occupied := l_time_occupied + NVL(v_task.travel_time, 0) / g_mins_in_day; -- Scheduled Travel Duration
4186: --l_available_hours := l_available_hours - l_time_occupied * g_hours_in_day;
4187:
4188: IF l_debug THEN
4189: debug(' Linking TaskID #' || v_task.task_id || ' : Time Used = ' || l_time_occupied * g_hours_in_day, l_api_name, fnd_log.level_statement);
4190: END IF;
4191:
4192: i := i + 1;
4193: l_object_tasks_tbl(i).task_assignment_id := v_task.task_assignment_id;
4196: END LOOP;
4197: ELSE
4198: FOR v_task IN c_shift_tasks_info LOOP
4199: IF l_debug THEN
4200: debug(' Linking Shift TaskID #' || v_task.task_id, l_api_name, fnd_log.level_statement);
4201: END IF;
4202:
4203: i := i + 1;
4204: l_object_tasks_tbl(i).task_assignment_id := v_task.task_assignment_id;
4217: l_object_capacity_tbl(1).availability_type := p_shift_type;
4218: l_object_capacity_tbl(1).schedule_detail_id := p_schedule_detail_id;
4219:
4220: IF l_debug THEN
4221: debug(' Trip Available Hours = ' || l_available_hours, l_api_name, fnd_log.level_statement);
4222: END IF;
4223:
4224: -- Create the Trip by calling Object Capacity Table Handlers
4225: cac_sr_object_capacity_pub.insert_object_capacity(
4235:
4236: IF x_return_status <> fnd_api.g_ret_sts_success THEN
4237: IF l_debug THEN
4238: x_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
4239: debug(' Unable to Create the Object Capacity: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
4240: END IF;
4241: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4242: RAISE fnd_api.g_exc_unexpected_error;
4243: END IF;
4254: x_trip.status := p_status;
4255: x_trip.schedule_detail_id := p_schedule_detail_id;
4256:
4257: IF l_debug THEN
4258: debug(' Created Trip - TripID#' || x_trip.trip_id, l_api_name, fnd_log.level_statement);
4259: END IF;
4260: EXCEPTION
4261: WHEN fnd_api.g_exc_error THEN
4262: ROLLBACK TO csf_new_trip;
4266: ROLLBACK TO csf_new_trip;
4267: x_return_status := fnd_api.g_ret_sts_unexp_error;
4268: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4269: WHEN OTHERS THEN
4270: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
4271: x_return_status := fnd_api.g_ret_sts_unexp_error;
4272: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4273: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4274: END IF;
4352: RAISE fnd_api.g_exc_error;
4353: END IF;
4354:
4355: IF l_debug THEN
4356: debug(' Created Departure Task - Task ID = ' || x_dep_task_id, l_api_name, fnd_log.level_statement);
4357: END IF;
4358: END IF;
4359:
4360: -- Create the Arrival Task
4394: END IF;
4395: RAISE fnd_api.g_exc_error;
4396: END IF;
4397: IF l_debug THEN
4398: debug(' Created Arrival Task - Task ID = ' || x_arr_task_id, l_api_name, fnd_log.level_statement);
4399: END IF;
4400: END IF;
4401: EXCEPTION
4402: WHEN fnd_api.g_exc_error THEN
4514: CLOSE c_trip_exist;
4515:
4516: IF (l_trip_exist = 'Y') THEN
4517: IF l_debug THEN
4518: debug(' Shift Tasks exist for the Resource in the specified interval', l_api_name, fnd_log.level_error);
4519: END IF;
4520: fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_ST_EXIST');
4521: fnd_message.set_token('RESOURCE', get_resource_info(l_trip.resource_id,l_trip.resource_type));
4522: fnd_message.set_token('START_TIME', format_date(p_start_date_time));
4565: WHEN fnd_api.g_exc_unexpected_error THEN
4566: x_return_status := fnd_api.g_ret_sts_unexp_error;
4567: fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4568: WHEN OTHERS THEN
4569: debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
4570: x_return_status := fnd_api.g_ret_sts_unexp_error;
4571: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4572: fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4573: END IF;
5029:
5030: BEGIN
5031:
5032: IF l_debug THEN
5033: debug(' Inside Dangling Procedure # ', l_api_name, fnd_log.level_statement);
5034: debug(' Checking Dangling Trip between ' || format_date(p_start) || ' and ' || format_date(p_end), l_api_name, fnd_log.level_statement);
5035:
5036: END IF;
5037: FOR i in p_resource_tbl.first .. p_resource_tbl.last
5030: BEGIN
5031:
5032: IF l_debug THEN
5033: debug(' Inside Dangling Procedure # ', l_api_name, fnd_log.level_statement);
5034: debug(' Checking Dangling Trip between ' || format_date(p_start) || ' and ' || format_date(p_end), l_api_name, fnd_log.level_statement);
5035:
5036: END IF;
5037: FOR i in p_resource_tbl.first .. p_resource_tbl.last
5038: LOOP
5039: FOR l_tasks IN c_dangling_tasks(p_resource_tbl(i).resource_id,p_resource_tbl(i).resource_type)
5040: LOOP
5041:
5042: IF l_debug THEN
5043: debug(' Deleting the Dangling Shift Task #' || l_tasks.task_id, l_api_name, fnd_log.level_statement);
5044: END IF;
5045: -- Departure Task already exists... Delete this Duplicate.
5046: jtf_tasks_pub.delete_task(
5047: p_api_version => 1.0
5053: );
5054:
5055: IF x_return_status <> fnd_api.g_ret_sts_success THEN
5056: IF l_debug THEN
5057: debug(' Unable to Delete the dangling shift Task', l_api_name, fnd_log.level_error);
5058: END IF;
5059:
5060: add_message( p_resource_tbl(i).resource_id
5061: , p_resource_tbl(i).resource_type
5069: END LOOP;
5070: FOR l_alone_trip_task in c_alone_trip_tasks(p_resource_tbl(i).resource_id,p_resource_tbl(i).resource_type)
5071: LOOP
5072: IF l_debug THEN
5073: debug(' Details of Trip which have only one Departure/Arrival Task for given below period # ', l_api_name, fnd_log.level_statement);
5074: debug(' From Date # ' || format_date(p_start) || ' TO Date # ' || format_date(p_end), l_api_name, fnd_log.level_statement);
5075: debug(' Task Number # ' ||l_alone_trip_task.task_number||' Scheduled Start Date #'||format_date(l_alone_trip_task.scheduled_start_date) ||
5076: ' Scheduled End Date #'||format_date(l_alone_trip_task.scheduled_end_date) ||' Owner Id #'||l_alone_trip_task.owner_id||
5077: ' Owner Type #'||l_alone_trip_task.owner_type_code, l_api_name, fnd_log.level_statement);
5070: FOR l_alone_trip_task in c_alone_trip_tasks(p_resource_tbl(i).resource_id,p_resource_tbl(i).resource_type)
5071: LOOP
5072: IF l_debug THEN
5073: debug(' Details of Trip which have only one Departure/Arrival Task for given below period # ', l_api_name, fnd_log.level_statement);
5074: debug(' From Date # ' || format_date(p_start) || ' TO Date # ' || format_date(p_end), l_api_name, fnd_log.level_statement);
5075: debug(' Task Number # ' ||l_alone_trip_task.task_number||' Scheduled Start Date #'||format_date(l_alone_trip_task.scheduled_start_date) ||
5076: ' Scheduled End Date #'||format_date(l_alone_trip_task.scheduled_end_date) ||' Owner Id #'||l_alone_trip_task.owner_id||
5077: ' Owner Type #'||l_alone_trip_task.owner_type_code, l_api_name, fnd_log.level_statement);
5078: END IF;
5073: debug(' Details of Trip which have only one Departure/Arrival Task for given below period # ', l_api_name, fnd_log.level_statement);
5074: debug(' From Date # ' || format_date(p_start) || ' TO Date # ' || format_date(p_end), l_api_name, fnd_log.level_statement);
5075: debug(' Task Number # ' ||l_alone_trip_task.task_number||' Scheduled Start Date #'||format_date(l_alone_trip_task.scheduled_start_date) ||
5076: ' Scheduled End Date #'||format_date(l_alone_trip_task.scheduled_end_date) ||' Owner Id #'||l_alone_trip_task.owner_id||
5077: ' Owner Type #'||l_alone_trip_task.owner_type_code, l_api_name, fnd_log.level_statement);
5078: END IF;
5079: END LOOP;
5080: END LOOP;
5081:
5080: END LOOP;
5081:
5082:
5083: IF l_debug THEN
5084: debug(' OutSide Dangling Procedure # ', l_api_name, fnd_log.level_statement);
5085: END IF;
5086:
5087: END check_dangling_tasks;
5088:
5125:
5126: BEGIN
5127:
5128: IF l_debug THEN
5129: debug(' Inside Duplicate Procedure # ', l_api_name, fnd_log.level_statement);
5130: END IF;
5131: FOR i in p_resource_tbl.first .. p_resource_tbl.last
5132: LOOP
5133:
5133:
5134: FOR l_tasks IN c_duplicate_tasks(p_resource_tbl(i).resource_id,p_resource_tbl(i).resource_type)
5135: LOOP
5136: IF l_debug THEN
5137: debug(' Deleting the Duplicate Shift Task #' || l_tasks.task_id, l_api_name, fnd_log.level_statement);
5138: END IF;
5139: -- Departure Task already exists... Delete this Duplicate.
5140: jtf_tasks_pub.delete_task(
5141: p_api_version => 1.0
5147: );
5148:
5149: IF x_return_status <> fnd_api.g_ret_sts_success THEN
5150: IF l_debug THEN
5151: debug(' Unable to Delete the Duplicate shift Task', l_api_name, fnd_log.level_error);
5152: END IF;
5153:
5154: add_message( p_resource_tbl(i).resource_id
5155: , p_resource_tbl(i).resource_type
5163: END IF;
5164: END LOOP;
5165: END LOOP;
5166: IF l_debug THEN
5167: debug(' OutSide Duplicate Procedure # ', l_api_name, fnd_log.level_statement);
5168: END IF;
5169:
5170: END check_duplicate_tasks;
5171:
5227: l_debug CONSTANT BOOLEAN := g_debug = 'Y';
5228:
5229: BEGIN
5230: IF l_debug THEN
5231: debug(' Inside Procedure check_multiple_trip_tasks # ', l_api_name, fnd_log.level_statement);
5232: debug(' Checking Trips for Tasks between ' || format_date(p_start) || ' and ' || format_date(p_end), l_api_name, fnd_log.level_statement);
5233: END IF;
5234:
5235: FOR i in p_resource_tbl.first .. p_resource_tbl.last
5228:
5229: BEGIN
5230: IF l_debug THEN
5231: debug(' Inside Procedure check_multiple_trip_tasks # ', l_api_name, fnd_log.level_statement);
5232: debug(' Checking Trips for Tasks between ' || format_date(p_start) || ' and ' || format_date(p_end), l_api_name, fnd_log.level_statement);
5233: END IF;
5234:
5235: FOR i in p_resource_tbl.first .. p_resource_tbl.last
5236: LOOP
5240: FETCH c_trip_count INTO l_trip_count;
5241: CLOSE c_trip_count;
5242:
5243: IF l_debug THEN
5244: debug(' Resource Id # ' || p_resource_tbl(i).resource_id || ' . ' || ' The Trip Count for Task Id # ' || l_tasks.task_id || ' is # ' || l_trip_count, l_api_name, fnd_log.level_statement);
5245: END IF;
5246:
5247: IF l_trip_count > 1 THEN
5248: IF l_debug THEN
5245: END IF;
5246:
5247: IF l_trip_count > 1 THEN
5248: IF l_debug THEN
5249: debug('Updating Task Assignment for Task Id # ' || l_tasks.task_id || ' booking_start_date # ' || format_date(l_tasks.booking_start_date) || ' booking_end_date # ' || format_date(l_tasks.booking_end_date),l_api_name, fnd_log.level_statement);
5250: END IF;
5251:
5252: IF l_tasks.object_capacity_id IS NOT NULL THEN
5253: OPEN c_trip_info(l_tasks.object_capacity_id);
5268: );
5269:
5270: IF x_return_status <> fnd_api.g_ret_sts_success THEN
5271: IF l_debug THEN
5272: debug('Unable to Update the Object Capacity Id for Task ' || l_tasks.task_id, l_api_name, fnd_log.level_error);
5273: END IF;
5274: END IF;
5275: END IF;
5276: ELSIF l_trip_count = 1 THEN
5274: END IF;
5275: END IF;
5276: ELSIF l_trip_count = 1 THEN
5277: IF l_debug THEN
5278: debug(' Updating the Available Hours for Object Capacity Id # ' || l_tasks.object_capacity_id || ' . ' || ' Task Id # ' || l_tasks.task_id, l_api_name, fnd_log.level_statement);
5279: END IF;
5280:
5281: OPEN c_trip_info(l_tasks.object_capacity_id);
5282: FETCH c_trip_info INTO l_trip_info;
5298: );
5299:
5300: IF x_return_status <> fnd_api.g_ret_sts_success THEN
5301: IF l_debug THEN
5302: debug('Unable to update the available hours for Object Capacity Id ' || l_tasks.task_id, l_api_name, fnd_log.level_error);
5303: END IF;
5304: END IF;
5305: END IF;
5306: END LOOP;
5307:
5308: END LOOP;
5309:
5310: IF l_debug THEN
5311: debug(' OutSide Procedure check_multiple_trip_tasks # ', l_api_name, fnd_log.level_statement);
5312: END IF;
5313:
5314: END check_multiple_trip_tasks;
5315: