[Home] [Help]
316: SELECT 1
317: FROM cac_sr_object_capacity oc
318: WHERE object_capacity_id = p_trip_id
319: AND EXISTS (SELECT 1
320: FROM jtf_task_assignments ta
321: , jtf_task_statuses_b ts
322: , jtf_tasks_b t
323: WHERE ta.object_capacity_id = oc.object_capacity_id
324: AND ts.task_status_id = ta.assignment_status_id
632: , csf_util_pvt.convert_to_minutes(
633: ta.sched_travel_duration
634: , ta.sched_travel_duration_uom
635: ) travel_time
636: FROM jtf_task_assignments ta
637: , jtf_task_statuses_b ts
638: , jtf_tasks_b t
639: WHERE ta.resource_id = p_resource_id
640: AND ta.resource_type_code = p_resource_type
650: AND (t.task_type_id NOT IN (20, 21) OR t.task_id IN (l_dep_task_id, l_arr_task_id));
651:
652: CURSOR c_shift_tasks_info IS
653: SELECT ta.task_assignment_id, ta.object_version_number, ta.task_id
654: FROM jtf_task_assignments ta
655: WHERE ta.task_id IN (l_dep_task_id, l_arr_task_id);
656:
657: BEGIN
658: SAVEPOINT csf_new_trip;
848: , t.task_number
849: , t.object_version_number task_ovn
850: , t.task_status_id
851: FROM cac_sr_object_capacity cac
852: , jtf_task_assignments ta
853: , jtf_tasks_b t
854: , jtf_task_statuses_b ts
855: WHERE cac.object_capacity_id = p_trip.trip_id
856: AND ta.resource_id = cac.object_id
1046: SELECT t.task_id
1047: , t.object_version_number
1048: , t.task_name
1049: , t.task_number
1050: FROM jtf_task_assignments ta
1051: , jtf_tasks_vl t
1052: WHERE ta.object_capacity_id = p_trip.trip_id
1053: AND t.task_id = ta.task_id
1054: AND NVL(t.deleted_flag, 'N') = 'N'
1189: , ta.object_version_number
1190: , ta.object_capacity_id wrong_trip_id
1191: , oc.object_capacity_id correct_trip_id
1192: FROM cac_sr_object_capacity oc
1193: , jtf_task_assignments ta
1194: , jtf_tasks_b t
1195: , jtf_task_statuses_b ts
1196: WHERE oc.object_capacity_id = p_trip.trip_id
1197: AND ta.resource_id = oc.object_id
1213: , ta.object_version_number
1214: , p_trip.trip_id wrong_trip_id
1215: , oc.object_capacity_id correct_trip_id
1216: FROM cac_sr_object_capacity oc
1217: , jtf_task_assignments ta
1218: , jtf_tasks_b t
1219: , jtf_task_statuses_b ts
1220: WHERE ta.object_capacity_id = p_trip.trip_id
1221: AND oc.object_id = ta.resource_id
1235: , ta.task_assignment_id
1236: , ta.object_version_number
1237: , to_number(NULL) wrong_trip_id
1238: , p_trip.trip_id correct_trip_id
1239: FROM jtf_task_assignments ta
1240: , jtf_tasks_b t
1241: WHERE ta.task_id IN (l_dep_task_id, l_arr_task_id)
1242: AND t.task_id = ta.task_id;
1243:
1246: , SUM (NVL(csf_util_pvt.convert_to_minutes(
1247: ta.sched_travel_duration
1248: , ta.sched_travel_duration_uom
1249: ), 0)) travel_time
1250: FROM jtf_task_assignments ta
1251: , jtf_task_statuses_b ts
1252: WHERE ta.object_capacity_id = p_trip.trip_id
1253: AND ts.task_status_id = ta.assignment_status_id
1254: AND NVL(ts.closed_flag, 'N') = 'N'
1262: , t.task_name
1263: , t.task_number
1264: , LAG(t.task_id) OVER (PARTITION BY t.task_type_id
1265: ORDER BY t.scheduled_start_date) duplicate
1266: FROM jtf_task_assignments ta
1267: , jtf_tasks_vl t
1268: WHERE ta.object_capacity_id = p_trip.trip_id
1269: AND t.task_id = ta.task_id
1270: AND NVL(t.deleted_flag, 'N') = 'N'
1353: IF l_debug THEN
1354: 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);
1355: END IF;
1356:
1357: jtf_task_assignments_pub.update_task_assignment(
1358: p_api_version => 1.0
1359: , x_return_status => x_return_status
1360: , x_msg_data => x_msg_data
1361: , x_msg_count => x_msg_count
1601: -- Query to check for the existence of Stray Shift Tasks
1602: CURSOR c_shift_tasks_exist (p_res_id NUMBER, p_res_type VARCHAR2, p_start DATE, p_end DATE) IS
1603: SELECT 'Y'
1604: FROM jtf_tasks_b t
1605: , jtf_task_assignments ta
1606: WHERE t.owner_id = p_res_id
1607: AND t.owner_type_code = p_res_type
1608: AND t.planned_start_date BETWEEN p_start AND p_end
1609: AND t.task_type_id IN (20, 21)
1626: ta.sched_travel_duration
1627: , ta.sched_travel_duration_uom
1628: ) travel_time
1629: FROM TABLE ( CAST(l_del_trip_tbl AS jtf_number_table) ) oc
1630: , jtf_task_assignments ta
1631: , jtf_task_statuses_b ts
1632: , jtf_tasks_b t
1633: WHERE ta.object_capacity_id = oc.COLUMN_VALUE
1634: AND ts.task_status_id = ta.assignment_status_id
1974: l_trip_length := l_trip_length
1975: + v_task.booking_end_date - v_task.booking_start_date
1976: + NVL(v_task.travel_time, 0) / g_mins_in_day;
1977:
1978: jtf_task_assignments_pub.update_task_assignment(
1979: p_api_version => 1.0
1980: , x_return_status => x_return_status
1981: , x_msg_data => x_msg_data
1982: , x_msg_count => x_msg_count
2157: , a.task_id arr_task_id
2158: , d.planned_start_date start_time
2159: , a.planned_end_date end_time
2160: FROM jtf_tasks_b d
2161: , jtf_task_assignments dta
2162: , jtf_tasks_b a
2163: , jtf_task_assignments ata
2164: WHERE d.owner_id = p_resource_tbl(1).resource_id
2165: AND d.owner_type_code = p_resource_tbl(1).resource_type
2159: , a.planned_end_date end_time
2160: FROM jtf_tasks_b d
2161: , jtf_task_assignments dta
2162: , jtf_tasks_b a
2163: , jtf_task_assignments ata
2164: WHERE d.owner_id = p_resource_tbl(1).resource_id
2165: AND d.owner_type_code = p_resource_tbl(1).resource_type
2166: AND d.planned_start_date BETWEEN p_start_date AND p_end_date
2167: AND d.task_type_id = 20