[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT v.flex_value_meaning meaning
FROM fnd_flex_value_sets s, fnd_flex_values_vl v
WHERE s.flex_value_set_name = 'CSF_GTR_ACTIONS'
AND s.flex_value_set_id = v.flex_value_set_id
AND v.flex_value = p_action;
SELECT name INTO g_dep_task_name
FROM jtf_task_types_vl WHERE task_type_id = g_dep_task_type_id;
SELECT name INTO g_arr_task_name
FROM jtf_task_types_vl WHERE task_type_id = g_arr_task_type_id;
SELECT 1
FROM cac_sr_object_capacity oc
WHERE object_capacity_id = p_trip_id
AND EXISTS (SELECT 1
FROM jtf_task_assignments ta
, jtf_task_statuses_b ts
, jtf_tasks_b t
WHERE ta.object_capacity_id = oc.object_capacity_id
AND ts.task_status_id = ta.assignment_status_id
AND NVL(ts.closed_flag, 'N') = 'N'
AND NVL(ts.completed_flag, 'N') = 'N'
AND NVL(ts.cancelled_flag, 'N') = 'N'
AND NVL(ts.rejected_flag, 'N') = 'N'
AND t.task_id = ta.task_id
AND NVL(t.deleted_flag, 'N') = 'N'
AND t.task_type_id NOT IN (20, 21));
SELECT *
FROM cac_sr_object_capacity
WHERE object_id = p_resource_id
AND object_type = p_resource_type
AND p_start_date_time <= (end_date_time + l_overtime)
AND p_end_date_time >= start_date_time
ORDER BY start_date_time, object_capacity_id;
SELECT *
FROM cac_sr_object_capacity
WHERE object_capacity_id = p_trip_id;
SELECT ta.task_assignment_id
, ta.object_version_number
, ta.task_id
, ta.booking_start_date
, ta.booking_end_date
, csf_util_pvt.convert_to_minutes(
ta.sched_travel_duration
, ta.sched_travel_duration_uom
) travel_time
FROM jtf_task_assignments ta
, jtf_task_statuses_b ts
, jtf_tasks_b t
WHERE ta.resource_id = p_resource_id
AND ta.resource_type_code = p_resource_type
AND ta.assignee_role = 'ASSIGNEE'
AND ts.task_status_id = ta.assignment_status_id
AND NVL(ts.closed_flag, 'N') = 'N'
AND NVL(ts.completed_flag, 'N') = 'N'
AND NVL(ts.cancelled_flag, 'N') = 'N'
AND t.task_id = ta.task_id
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND ta.booking_start_date <= (p_end_date_time + g_overtime)
AND ta.booking_end_date >= p_start_date_time
AND (t.task_type_id NOT IN (20, 21) OR t.task_id IN (l_dep_task_id, l_arr_task_id));
SELECT ta.task_assignment_id, ta.object_version_number, ta.task_id
FROM jtf_task_assignments ta
WHERE ta.task_id IN (l_dep_task_id, l_arr_task_id);
cac_sr_object_capacity_pub.insert_object_capacity(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_object_capacity => l_object_capacity_tbl
, p_update_tasks => fnd_api.g_true
, p_object_tasks => l_object_tasks_tbl
);
, p_update_tasks IN VARCHAR2 DEFAULT NULL
, p_task_action IN VARCHAR2 DEFAULT NULL
) IS
l_api_name CONSTANT VARCHAR2(30) := 'CHANGE_TRIP';
SELECT ta.task_assignment_id
, ta.object_version_number ta_object_version_number
, ta.assignment_status_id
, t.task_id
, t.task_number
, t.object_version_number task_ovn
, t.task_status_id
FROM cac_sr_object_capacity cac
, jtf_task_assignments ta
, jtf_tasks_b t
, jtf_task_statuses_b ts
WHERE cac.object_capacity_id = p_trip.trip_id
AND ta.resource_id = cac.object_id
AND ta.resource_type_code = cac.object_type
AND ( (ta.object_capacity_id IS NOT NULL AND ta.object_capacity_id = cac.object_capacity_id)
OR (ta.booking_start_date <= (cac.end_date_time + g_overtime) AND ta.booking_end_date >= cac.start_date_time) )
AND t.task_id = ta.task_id
AND ts.task_status_id = ta.assignment_status_id
AND NVL(ts.closed_flag, 'N') = 'N'
AND NVL(ts.completed_flag, 'N') = 'N'
AND NVL(ts.cancelled_flag, 'N') = 'N'
AND NVL(ts.working_flag, 'N') = 'N'
AND NVL(t.deleted_flag, 'N') = 'N'
AND ta.actual_start_date IS NULL
AND (t.source_object_type_code = 'SR' OR t.task_type_id IN (20, 21))
AND (p_task_type = 'ALL' OR t.task_type_id IN (20, 21));
cac_sr_object_capacity_pub.update_object_capacity(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_object_capacity_id => p_trip.trip_id
, p_object_version_number => p_object_version_number
, p_available_hours => l_available_hours
, p_available_hours_before => l_available_hours_before
, p_available_hours_after => l_available_hours_after
, p_status => p_status
);
debug(' Unable to Update the Object Capacity', l_api_name, fnd_log.level_error);
IF p_update_tasks = fnd_api.g_false THEN
RETURN;
csf_task_assignments_pub.update_assignment_status(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_validation_level => l_validation_level
, p_commit => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_assignment_id => v_task.task_assignment_id
, p_object_version_number => v_task.ta_object_version_number
, p_assignment_status_id => l_new_task_status
, x_task_object_version_number => v_task.task_ovn
, x_task_status_id => v_task.task_status_id
);
debug(' Unable to update the Assignment: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
fnd_message.set_name('CSF', 'CSF_ASSIGNMENT_UPDATE_FAIL');
SELECT t.task_id
, t.object_version_number
, t.task_name
, t.task_number
FROM jtf_task_assignments ta
, jtf_tasks_vl t
WHERE ta.object_capacity_id = p_trip.trip_id
AND t.task_id = ta.task_id
AND NVL(t.deleted_flag, 'N') = 'N'
AND t.task_type_id IN (g_dep_task_type_id, g_arr_task_type_id);
debug(' The Trip is unavailable and so cant be deleted', l_api_name, fnd_log.level_error);
debug(' Trip has active Tasks and so cant be deleted', l_api_name, fnd_log.level_error);
jtf_tasks_pub.delete_task(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_id => v_shift_task.task_id
, p_object_version_number => v_shift_task.object_version_number
);
debug(' Unable to Delete the Shift Task - ' || v_shift_task.task_name, l_api_name, fnd_log.level_error);
fnd_message.set_name('CSF', 'CSF_TASK_DELETE_FAIL');
cac_sr_object_capacity_pub.delete_object_capacity(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_object_capacity_id => p_trip.trip_id
, p_object_version_number => p_object_version_number
, p_update_tasks => fnd_api.g_false
);
debug(' Unable to Delete the Object Capacity', l_api_name, fnd_log.level_error);
debug(' Deleted the Trip', l_api_name, fnd_log.level_statement);
SELECT ta.task_id
, t.task_number
, ta.task_assignment_id
, ta.object_version_number
, ta.object_capacity_id wrong_trip_id
, oc.object_capacity_id correct_trip_id
FROM cac_sr_object_capacity oc
, jtf_task_assignments ta
, jtf_tasks_b t
, jtf_task_statuses_b ts
WHERE oc.object_capacity_id = p_trip.trip_id
AND ta.resource_id = oc.object_id
AND ta.resource_type_code = oc.object_type
AND ta.assignee_role = 'ASSIGNEE'
AND t.task_id = ta.task_id
AND t.task_type_id NOT IN (20, 21)
AND ts.task_status_id = ta.assignment_status_id
AND NVL(ts.closed_flag, 'N') = 'N'
AND NVL(ts.completed_flag, 'N') = 'N'
AND NVL(ts.cancelled_flag, 'N') = 'N'
AND NVL(ta.object_capacity_id, -1) <> oc.object_capacity_id
AND ta.booking_start_date < (oc.end_date_time + g_overtime)
AND ta.booking_end_date > oc.start_date_time
UNION ALL
SELECT ta.task_id
, t.task_number
, ta.task_assignment_id
, ta.object_version_number
, p_trip.trip_id wrong_trip_id
, oc.object_capacity_id correct_trip_id
FROM cac_sr_object_capacity oc
, jtf_task_assignments ta
, jtf_tasks_b t
, jtf_task_statuses_b ts
WHERE ta.object_capacity_id = p_trip.trip_id
AND oc.object_id = ta.resource_id
AND oc.object_type = ta.resource_type_code
AND oc.object_capacity_id <> ta.object_capacity_id
AND t.task_id = ta.task_id
AND t.task_type_id NOT IN (20, 21)
AND ts.task_status_id = ta.assignment_status_id
AND NVL(ts.closed_flag, 'N') = 'N'
AND NVL(ts.completed_flag, 'N') = 'N'
AND NVL(ts.cancelled_flag, 'N') = 'N'
AND ta.booking_start_date < (oc.end_date_time + g_overtime)
AND ta.booking_end_date > oc.start_date_time
UNION ALL
SELECT ta.task_id
, t.task_number
, ta.task_assignment_id
, ta.object_version_number
, to_number(NULL) wrong_trip_id
, p_trip.trip_id correct_trip_id
FROM jtf_task_assignments ta
, jtf_tasks_b t
WHERE ta.task_id IN (l_dep_task_id, l_arr_task_id)
AND t.task_id = ta.task_id;
SELECT SUM (ta.booking_end_date - ta.booking_start_date) used_time
, SUM (NVL(csf_util_pvt.convert_to_minutes(
ta.sched_travel_duration
, ta.sched_travel_duration_uom
), 0)) travel_time
FROM jtf_task_assignments ta
, jtf_task_statuses_b ts
WHERE ta.object_capacity_id = p_trip.trip_id
AND ts.task_status_id = ta.assignment_status_id
AND NVL(ts.closed_flag, 'N') = 'N'
AND NVL(ts.completed_flag, 'N') = 'N'
AND NVL(ts.cancelled_flag, 'N') = 'N';
SELECT t.task_id
, t.task_type_id
, t.object_version_number
, t.task_name
, t.task_number
, LAG(t.task_id) OVER (PARTITION BY t.task_type_id
ORDER BY t.scheduled_start_date) duplicate
FROM jtf_task_assignments ta
, jtf_tasks_vl t
WHERE ta.object_capacity_id = p_trip.trip_id
AND t.task_id = ta.task_id
AND NVL(t.deleted_flag, 'N') = 'N'
AND t.task_type_id IN (20, 21);
jtf_tasks_pub.delete_task(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_id => v_shift_task.task_id
, p_object_version_number => v_shift_task.object_version_number
);
debug(' Unable to Delete the Task', l_api_name, fnd_log.level_error);
fnd_message.set_name('CSF', 'CSF_TASK_DELETE_FAIL');
jtf_task_assignments_pub.update_task_assignment(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_task_assignment_id => v_task.task_assignment_id
, p_object_version_number => v_task.object_version_number
, p_object_capacity_id => v_task.correct_trip_id
, p_enable_workflow => fnd_api.g_miss_char
, p_abort_workflow => fnd_api.g_miss_char
);
fnd_message.set_name('CSF', 'CSF_ASSIGNMENT_UPDATE_FAIL');
cac_sr_object_capacity_pub.update_object_capacity(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_object_capacity_id => p_trip.trip_id
, p_object_version_number => p_object_version_number
, p_available_hours => l_available_hours * g_hours_in_day
);
PROCEDURE delete_trips(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_trips IN trip_tbl_type
)IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRIPS';
, p_msg_name => 'CSF_TRIP_DELETE_FAIL_OTHER'
, p_msg_type => g_error_message
);
END delete_trips;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRIPS';
, p_delete_trips IN BOOLEAN DEFAULT FALSE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TRIPS';
SELECT 'Y'
FROM jtf_tasks_b t
, jtf_task_assignments ta
WHERE t.owner_id = p_res_id
AND t.owner_type_code = p_res_type
AND t.planned_start_date BETWEEN p_start AND p_end
AND t.task_type_id IN (20, 21)
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND ta.task_id = t.task_id
AND ta.assignee_role = 'ASSIGNEE'
AND ta.object_capacity_id IS NULL
AND ROWNUM = 1;
SELECT /*+ cardinality (oc 1) */
ta.task_assignment_id
, ta.object_version_number
, ta.object_capacity_id
, ta.task_id
, ta.booking_start_date
, ta.booking_end_date
, csf_util_pvt.convert_to_minutes(
ta.sched_travel_duration
, ta.sched_travel_duration_uom
) travel_time
FROM TABLE ( CAST(l_del_trip_tbl AS jtf_number_table) ) oc
, jtf_task_assignments ta
, jtf_task_statuses_b ts
, jtf_tasks_b t
WHERE ta.object_capacity_id = oc.COLUMN_VALUE
AND ts.task_status_id = ta.assignment_status_id
AND NVL(ts.closed_flag, 'N') = 'N'
AND NVL(ts.completed_flag, 'N') = 'N'
AND NVL(ts.cancelled_flag, 'N') = 'N'
AND NVL(ts.rejected_flag, 'N') = 'N'
AND t.task_id = ta.task_id
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND t.task_type_id NOT IN (20, 21)
ORDER BY ta.object_capacity_id;
SELECT oc.object_version_number
, oc.available_hours
FROM cac_sr_object_capacity oc
WHERE oc.object_capacity_id = p_trip_id;
IF p_delete_trips THEN
debug('Replacing Trips for Resource#' || l_res_id || ' between ' || p_start_date || ' and ' || p_end_date, l_api_name, fnd_log.level_procedure);
IF NOT p_delete_trips
OR NOT time_overlaps(l_old_trips(l_trip_idx), p_start_date, p_end_date)
THEN
l_msg_name := 'CSF_TRIP_CREATE_FAIL_DUP';
l_msg_name := 'CSF_TRIP_DELETE_FAIL_OTHER';
l_old_trips.DELETE(l_trip_idx);
l_temp_trip_tbl.DELETE;
IF l_msg_name = 'CSF_TRIP_DELETE_FAIL_OTHER' THEN
l_start := l_old_trips(l_trip_idx).start_date_time;
IF NOT p_delete_trips THEN
debug( ' Since delete trips not allowed.. we have overlap with existing trip'
, l_api_name, fnd_log.level_error
);
debug( ' Delete trips allowed.. but we have conflict with new trip'
, l_api_name, fnd_log.level_error
);
ELSIF l_msg_name = 'CSF_TRIP_DELETE_FAIL_OTHER' THEN
debug( ' Error occurred while deleting the trip between '
|| format_date(l_start) || ' and ' || format_date(l_end)
|| ' : Error = ' || l_reason
, l_api_name, fnd_log.level_error
);
IF p_delete_trips THEN
-- Link all the Unlinked Task Assignments to the corresponding shifts
IF l_debug THEN
debug(' Linking unlinked Task Assignments if any of old trips to new trips', l_api_name, fnd_log.level_statement);
cac_sr_object_capacity_pub.update_object_capacity(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_object_capacity_id => l_prev_trip_id
, p_object_version_number => l_trip_info.object_version_number
, p_available_hours => l_trip_info.available_hours
);
jtf_task_assignments_pub.update_task_assignment(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_task_assignment_id => v_task.task_assignment_id
, p_object_version_number => v_task.object_version_number
, p_object_capacity_id => l_new_trips(l_trip_idx).trip_id
, p_enable_workflow => fnd_api.g_miss_char
, p_abort_workflow => fnd_api.g_miss_char
);
debug( ' Cant delete trip' || l_old_trips(l_trip_idx).trip_id
|| ' between ' || format_date(l_old_trips(l_trip_idx).start_date_Time)
|| ' and ' || format_date(l_old_trips(l_trip_idx).end_date_Time)
|| ' as there active tasks present'
, l_api_name, fnd_log.level_error
);
, p_msg_name => 'CSF_TRIP_DELETE_FAIL_OTHER'
, p_msg_type => g_error_message
);
SELECT d.task_id dep_task_id
, a.task_id arr_task_id
, d.planned_start_date start_time
, a.planned_end_date end_time
FROM jtf_tasks_b d
, jtf_task_assignments dta
, jtf_tasks_b a
, jtf_task_assignments ata
WHERE d.owner_id = p_resource_tbl(1).resource_id
AND d.owner_type_code = p_resource_tbl(1).resource_type
AND d.planned_start_date BETWEEN p_start_date AND p_end_date
AND d.task_type_id = 20
AND NVL(d.deleted_flag, 'N') = 'N'
AND dta.task_id = d.task_id
AND dta.assignee_role = 'ASSIGNEE'
AND dta.object_capacity_id IS NULL
AND a.owner_id = d.owner_id
AND a.owner_type_code = d.owner_type_code
AND a.planned_end_date BETWEEN d.planned_start_date AND (d.planned_start_date + 1)
AND a.task_type_id = 21
AND NVL(a.deleted_flag, 'N') = 'N'
AND ata.task_id = a.task_id
AND ata.assignee_role = 'ASSIGNEE'
AND ata.object_capacity_id IS NULL
AND dta.shift_construct_id = ata.shift_construct_id
ORDER BY d.planned_start_date;
PROCEDURE update_trip_status(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_trip_action IN VARCHAR2
, p_trips IN trip_tbl_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRIP_STATUS';
, p_update_tasks => fnd_api.g_true
, p_task_action => l_trip_action
);
, p_msg_name => 'CSF_TRIP_UPDATE_FAIL_OTHER'
, p_msg_type => g_error_message
);
END update_trip_status;
SELECT 'Y'
FROM jtf_tasks_b t
WHERE t.owner_id = p_resource_id
AND t.owner_type_code = p_resource_type
AND t.scheduled_start_date BETWEEN p_start_date_time AND p_end_date_time
AND t.task_type_id IN (20, 21)
AND NVL(t.deleted_flag, 'N') = 'N'
AND ROWNUM = 1;
PROCEDURE update_trip(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_trip_id IN NUMBER
, p_object_version_number IN NUMBER
, p_available_hours IN NUMBER
, p_upd_available_hours IN NUMBER
, p_available_hours_before IN NUMBER
, p_available_hours_after IN NUMBER
, p_status IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRIP';
fnd_message.set_name('CSF', 'CSF_TRIP_UPDATE_FAIL_OTHER');
PROCEDURE delete_trip (
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_trip_id IN NUMBER
, p_object_version_number IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRIP';
SAVEPOINT delete_trip;
debug( ' Unable to delete the Trip: Error = '
|| fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
, l_api_name, fnd_log.level_error);
fnd_message.set_name('CSF', 'CSF_TRIP_DELETE_FAIL_OTHER');
ROLLBACK TO delete_trip;
ROLLBACK TO delete_trip;
ROLLBACK TO delete_trip;
END delete_trip;
g_messages.DELETE;
, p_delete_trips => (p_action = g_action_replace_trip)
);
ELSIF p_action = g_action_delete_trip THEN
delete_trips(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_trips => l_trips
);
update_trip_status(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_trip_action => p_action
, p_trips => l_trips
);
SELECT resource_id, resource_type
FROM csf_selected_resources_v
ORDER BY resource_type, resource_id;