[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 object_id,object_type,start_date_time,end_date_time
from cac_sr_object_capacity
where object_id = p_resource_id
and object_type = p_resource_type
and start_date_time = p_start_date_time
and end_date_time = p_end_date_time;
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));
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
, p_start_date IN DATE DEFAULT NULL
, p_end_date IN DATE 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_availability_type => p_availability_type
, p_status => p_status
, p_start_date_time => p_start_date
, p_end_date_time => p_end_date
);
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 id - ' || v_shift_task.task_id , 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);
debug('Unable to Delete the Object Capacity: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
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 tb.task_id
FROM jtf_task_assignments jta,
jtf_tasks_b tb,
jtf_task_statuses_b jts
WHERE jta.object_capacity_id=l_trip
AND jta.task_id=tb.task_id
AND jts.task_status_id = tb.task_status_id
AND NVL(jts.closed_flag, 'N') = 'N'
AND NVL(jts.completed_flag, 'N') = 'N'
AND NVL(jts.cancelled_flag, 'N') = 'N'
AND NVL(jts.rejected_flag, 'N') = 'N'
AND NVL(tb.deleted_flag, 'N') <> 'Y'
AND tb.task_type_id not in (20,21);
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
IF l_debug THEN
debug(' Error : CSF_TRIP_CREATE_FAIL_DUP ' , l_api_name, fnd_log.level_error);
debug(' remove_trip Error : CSF_TRIP_DELETE_FAIL_OTHER ' , l_api_name, fnd_log.level_error);
l_msg_name := 'CSF_TRIP_DELETE_FAIL_OTHER';
l_old_trips.DELETE(l_trip_idx);
l_old_trips_temp.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_temp(l_trip_idx).trip_id
|| ' between ' || format_date(l_old_trips_temp(l_trip_idx).start_date_Time)
|| ' and ' || format_date(l_old_trips_temp(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
);
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;
IF p_action = g_action_delete_trip
THEN
check_dangling_tasks(p_resource_tbl => p_resource_tbl
, p_start => p_start_date
, p_end => p_end_date
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count);
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(
SELECT RESOURCE_ID,
RESOURCE_TYPE
FROM CSF_SELECTED_RESOURCES_V
MINUS
SELECT DISTINCT
A.RESOURCE_ID,
A.RESOURCE_TYPE
FROM CSF_SELECTED_RESOURCES_V A,
JTF_RS_DEFRESROLES_VL B,
JTF_RS_ALL_RESOURCES_VL C,
JTF_RS_ROLES_B D
WHERE B.ROLE_RESOURCE_ID=A.RESOURCE_ID
AND C.RESOURCE_ID = B.ROLE_RESOURCE_ID
AND C.RESOURCE_TYPE =A.RESOURCE_TYPE
AND D.ROLE_ID = B.ROLE_ID
AND B.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
AND NVL( B.DELETE_FLAG, 'N') = 'N'
AND (SYSDATE >= TRUNC (B.RES_RL_START_DATE) OR B.RES_RL_START_DATE IS NULL)
AND (SYSDATE <= TRUNC (B.RES_RL_END_DATE) + 1 OR B.RES_RL_END_DATE IS NULL)
AND ROLE_CODE IN ( 'CSF_THIRD_PARTY_SERVICE_PROVID', 'CSF_THIRD_PARTY_ADMINISTRATOR')
)
ORDER BY RESOURCE_TYPE, RESOURCE_ID;
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;
SELECT 'Y'
FROM cac_sr_object_capacity
WHERE p_start_date_time <= (end_date_time + g_overtime)
AND p_end_date_time >= start_date_time
AND object_id=p_resource_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
);
PROCEDURE update_dc_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
, p_availability_type in varchar2 default null
, p_start_date_time in date
, p_end_date_time in date
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRIP';
SELECT 'Y'
FROM cac_sr_object_capacity
WHERE p_start_date_time <= (end_date_time + g_overtime)
AND p_end_date_time >= start_date_time
AND object_id=p_resource_id
AND OBJECT_CAPACITY_ID NOT IN (p_trip_id);
fnd_message.set_name('CSF', 'CSF_TRIP_UPDATE_FAIL_OTHER');
PROCEDURE update_shift_tasks(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT NULL
, p_commit IN VARCHAR2 DEFAULT NULL
, p_object_version_number in out nocopy number
, p_task_id IN NUMBER
, p_Task_type_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_resource_id IN NUMBER
, p_resource_type IN VARCHAR2
, p_start_date_time IN DATE
, p_end_date_time IN DATE
, p_arr_party_site IN NUMBER
, p_arr_party IN NUMBER
, p_dep_party_site IN NUMBER
, p_dep_party IN NUMBER
, p_update_dep_task IN BOOLEAN default null
, p_update_arr_task IN BOOLEAN default null
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SHIFT_TASKS';
IF p_update_dep_task = FALSE AND p_update_arr_task = FALSE THEN
RETURN;
IF p_update_dep_task THEN
jtf_tasks_pub.update_task(
p_api_version => 1.0
, p_task_id => p_task_id
, p_object_version_number => p_object_version_number
, p_task_type_id => p_task_type_id
, p_task_status_id => g_assigned_status_id
, p_owner_id => p_resource_id
, p_owner_type_code => p_resource_type
, p_address_id => p_dep_party_site
, p_customer_id => p_dep_party
, p_planned_start_date => p_start_date_time
, p_planned_end_date => p_start_date_time
, p_scheduled_start_date => p_start_date_time
, p_scheduled_end_date => p_start_date_time
, p_duration => 0
, p_duration_uom => g_duration_uom
, p_bound_mode_code => 'BTS'
, p_soft_bound_flag => 'Y'
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
IF p_update_arr_task THEN
jtf_tasks_pub.update_task(
p_api_version => 1.0
, p_task_id => p_task_id
, p_object_version_number => p_object_version_number
, p_task_type_id => p_task_type_id
, p_task_status_id => g_assigned_status_id
, p_owner_id => p_resource_id
, p_owner_type_code => p_resource_type
, p_address_id => p_arr_party_site
, p_customer_id => p_arr_party
, p_planned_start_date => p_end_date_time
, p_planned_end_date => p_end_date_time
, p_scheduled_start_date => p_end_date_time
, p_scheduled_end_date => p_end_date_time
, p_duration => 0
, p_duration_uom => g_duration_uom
, p_bound_mode_code => 'BTS'
, p_soft_bound_flag => 'Y'
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
END update_shift_tasks;
, p_delete_trips IN BOOLEAN DEFAULT FALSE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DC_TRIPS';
, p_delete_trips => null
);
Select TIME_ZONE
From JTF_RS_RESOURCE_EXTNS
Where RESOURCE_ID = p_resource_id
And trunc(sysdate) between trunc(nvl(START_DATE_ACTIVE,sysdate))
and trunc(nvl(END_DATE_ACTIVE,sysdate));
SELECT t.task_id,t.object_version_number,scheduled_start_date,scheduled_end_date
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
UNION
SELECT t.task_id,t.object_version_number,scheduled_start_date,scheduled_end_date
FROM jtf_tasks_b t
WHERE t.owner_id = p_res_id
AND t.owner_type_code = p_res_type
AND t.task_type_id IN (20, 21)
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND( t.planned_start_date BETWEEN p_start AND p_end )
AND TASK_ID NOT IN (SELECT ta.TASK_ID FROM JTF_TASK_ASSIGNMENTS ta WHERE ta.task_id=t.task_id and RESOURCE_ID = p_res_id
AND RESOURCE_TYPE_CODE = p_res_type);
SELECT task_number,
scheduled_start_date,
scheduled_end_date,
owner_id,
owner_type_code
FROM JTF_TASKS_B
WHERE TASK_ID IN
(SELECT TASK_ID
FROM JTF_TASK_ASSIGNMENTS
WHERE OBJECT_CAPACITY_ID IN
(SELECT co.object_capacity_id
FROM cac_sr_object_capacity co,
jtf_tasks_b jtb,
jtf_task_assignments jta,
jtf_task_statuses_b jts,
jtf_Task_statuses_b jtsa
WHERE jtb.task_id =jta.task_id
AND jta.assignment_status_id =jts.task_status_id
AND NVL(jts.cancelled_flag,'N') <> 'Y'
AND jtb.task_status_id =jtsa.task_status_id
AND NVL(jtsa.cancelled_flag,'N') <> 'Y'
AND NVL(jtb.deleted_flag,'N') <> 'Y'
AND co.object_capacity_id =jta.object_capacity_id
AND resource_id =object_id
AND resource_type_code =object_type
AND jtb.task_type_id IN (20,21)
AND resource_id =p_res_id
AND resource_type_code =p_res_type
AND( co.start_date_time BETWEEN p_start AND p_end )
GROUP BY co.object_capacity_id
HAVING COUNT(jta.task_id) =1
)
);
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 => l_tasks.task_id
, p_object_version_number => l_tasks.object_version_number
);
debug(' Unable to Delete the dangling shift Task', l_api_name, fnd_log.level_error);
, 'CSF_TASK_DELETE_FAIL'
, g_error_message );
SELECT task_id
, task_type_id
, object_version_number
, task_name
, task_number FROM (
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,resource_id,resource_type_code,scheduled_start_date
ORDER BY t.scheduled_start_date) duplicate
FROM jtf_task_assignments ta
, jtf_tasks_vl t
WHERE t.task_id = ta.task_id
AND NVL(t.deleted_flag, 'N') = 'N'
AND t.task_type_id IN (20, 21)
AND resource_id =p_res_id
AND resource_type_code=p_res_type
AND booking_start_date <= p_end + 1
AND booking_end_date >= p_start - 1
) WHERE duplicate IS NOT NULL;
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 => l_tasks.task_id
, p_object_version_number => l_tasks.object_version_number
);
debug(' Unable to Delete the Duplicate shift Task', l_api_name, fnd_log.level_error);
, 'CSF_TASK_DELETE_FAIL'
, g_error_message );
SELECT ta.task_assignment_id
, ta.object_version_number
, ta.task_id
, ta.booking_start_date
, ta.booking_end_date
, ta.object_capacity_id
, 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 + g_overtime)
AND ta.booking_end_date >= p_start
AND (t.task_type_id NOT IN (20, 21));
select count(distinct object_capacity_id)
FROM cac_sr_object_capacity co
WHERE co.start_date_time <= p_end_date
and co.end_date_time >= p_start_date
and object_id= p_res_id
and object_type= p_res_type;
SELECT oc.object_version_number
, oc.available_hours
FROM cac_sr_object_capacity oc
WHERE oc.object_capacity_id = p_trip_id;
JTF_TASK_ASSIGNMENTS_PUB.UPDATE_TASK_ASSIGNMENT(
p_api_version => 1.0,
p_commit => fnd_api.G_FALSE,
p_object_version_number => l_tasks.OBJECT_VERSION_NUMBER,
p_task_assignment_id => l_tasks.TASK_ASSIGNMENT_ID,
p_enable_workflow => NULL,
p_abort_workflow => NULL,
p_object_capacity_id => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
debug('Unable to Update the Object Capacity Id for Task ' || l_tasks.task_id, l_api_name, fnd_log.level_error);
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_tasks.object_capacity_id
, p_object_version_number => l_trip_info.object_version_number
, p_available_hours => l_available_hours
);
debug('Unable to update the available hours for Object Capacity Id ' || l_tasks.task_id, l_api_name, fnd_log.level_error);