[Home] [Help]
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
325: AND NVL(ts.closed_flag, 'N') = 'N'
326: AND NVL(ts.completed_flag, 'N') = 'N'
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
641: AND ta.assignee_role = 'ASSIGNEE'
642: AND ts.task_status_id = ta.assignment_status_id
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
857: AND ta.resource_type_code = cac.object_type
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
1198: AND ta.resource_type_code = oc.object_type
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
1222: AND oc.object_type = ta.resource_type_code
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:
1244: CURSOR c_used_time IS
1600:
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
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
1635: AND NVL(ts.closed_flag, 'N') = 'N'
1636: AND NVL(ts.completed_flag, 'N') = 'N'
2156: SELECT d.task_id dep_task_id
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
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
2166: AND d.planned_start_date BETWEEN p_start_date AND p_end_date
2386:
2387: -- Query for the existence of any Shift Task in the Trip Inteval for the Resource.
2388: CURSOR c_st_exist IS
2389: SELECT 'Y'
2390: FROM jtf_tasks_b t
2391: WHERE t.owner_id = p_resource_id
2392: AND t.owner_type_code = p_resource_type
2393: AND t.scheduled_start_date BETWEEN p_start_date_time AND p_end_date_time
2394: AND t.task_type_id IN (20, 21)