[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Add_to_Delete_List(
p_stop_tab IN WSH_TRIP_STOPS_VALIDATIONS.stop_details_tab,
p_caller IN VARCHAR2,
x_stop_delete_tab OUT NOCOPY wsh_util_core.id_tab_type,
x_trip_affected_tab OUT NOCOPY wsh_util_core.id_tab_type,
x_return_status OUT NOCOPY VARCHAR2) ;
l_stop_delete_tab wsh_util_core.id_tab_type;
l_delete_tmp_tab WSH_TRIP_STOPS_VALIDATIONS.stop_details_tab;
l_delete_t WSH_TRIP_STOPS_VALIDATIONS.stop_details_tab;
SELECT trip_id
FROM WSH_TRIPS
WHERE trip_id=p_trip_id
AND status_code='IT'
AND NOT EXISTS (select 'x'
from wsh_trip_stops
where trip_id=p_trip_id
and status_code IN ('OP', 'AR')
and rownum=1);
l_delete_tmp_tab(l_next).stop_id := p_rec_attr_tab(l_next).stop_id;
l_delete_tmp_tab(l_next).trip_id := p_rec_attr_tab(l_next).trip_id;
AND p_action_prms.action_code = 'DELETE'
) THEN --BUG 4354579
WSH_TRIP_STOPS_PVT.Lock_Trip_Stop(
p_rec_attr_tab=>p_rec_attr_tab,
p_caller=>p_action_prms.caller,
p_valid_index_tab =>l_valid_index_tab,
x_valid_ids_tab =>x_stop_out_rec.valid_ids_tab,
x_return_status=>l_return_status);
x_stop_out_rec.selection_issue_flag := 'Y';
IF p_action_prms.action_code IN ('UPDATE-STATUS', 'PRINT-DOC-SETS')
AND nvl(p_action_prms.phase,1) = 1
AND p_action_prms.caller = 'WSH_FSTRX'
THEN
--Bugfix 4070732 { Phase 1 need to call the reset API only
l_return_status := NULL;
FND_MESSAGE.SET_NAME('WSH','WSH_STOP_DELETE_ERROR');
l_delete_t(l_cc_count_success).stop_id := l_delete_tmp_tab(i).stop_id;
l_delete_t(l_cc_count_success).trip_id := l_delete_tmp_tab(i).trip_id;
l_delete_tmp_tab := l_delete_t;
WSH_WV_UTILS.G_RESET_WV := 'Y'; -- set to Y to enable the update
ELSIF p_action_prms.action_code = 'UPDATE-STATUS' THEN
IF l_debug_on THEN
wsh_debug_sv.log (l_module_name,'stop_action',
p_action_prms.stop_action);
p_action => 'UPDATE',
x_return_status => l_return_status
);
IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_CHK_UPDATE_STATUS_LVL) = 1 THEN
if l_debug_on then
wsh_debug_sv.log(l_module_name, 'l_stop_id_tab count', l_stop_id_tab.count);
WSH_TRIP_STOPS_ACTIONS.Check_Update_Stops(
p_stop_rows => l_stop_id_tab,
p_action => p_action_prms.stop_action,
--tkt
p_caller => p_action_prms.caller,
x_return_status => l_return_status
);
ELSIF p_action_prms.action_code = 'DELETE' THEN
--TL Rating
IF( WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
IF l_stop_id_tab.count > 0 THEN
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.MARK_REPRICE_REQUIRED',WSH_DEBUG_SV.C_PROC_LEVEL);
Add_to_Delete_List(
p_stop_tab => l_delete_tmp_tab,
p_caller => p_action_prms.caller,
x_stop_delete_tab => l_stop_delete_tab,
x_trip_affected_tab => l_trip_affected_tab,
x_return_status => l_return_status);
WSH_UTIL_CORE.delete(
p_type => 'STOP',
p_rows => l_stop_delete_tab,
x_return_status => l_return_status,
--tkt
p_caller => p_action_prms.caller
);
FND_MESSAGE.SET_NAME('WSH','WSH_UI_MULTI_SELECTION');
wsh_debug_sv.log (l_module_name,'WSH_UI_MULTI_SELECTION');
x_pvt_stop_rec.LAST_UPDATE_DATE := p_grp_stop_rec.LAST_UPDATE_DATE;
x_pvt_stop_rec.LAST_UPDATED_BY := p_grp_stop_rec.LAST_UPDATED_BY;
x_pvt_stop_rec.LAST_UPDATE_LOGIN := p_grp_stop_rec.LAST_UPDATE_LOGIN;
x_pvt_stop_rec.PROGRAM_UPDATE_DATE := p_grp_stop_rec.PROGRAM_UPDATE_DATE;
IF p_action_code = 'UPDATE' THEN
l_status_code := p_rec_attr_tab(l_index).status_code;
l_action := 'UPDATE';
PROCEDURE CREATE_UPDATE_STOP(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_in_rec IN stopInRecType,
p_rec_attr_tab IN WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type,
x_stop_out_tab OUT NOCOPY stop_out_tab_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_stop_wt_vol_out_tab OUT NOCOPY Stop_Wt_Vol_tab_type --bug 2796095
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Create_Update_Stop';
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_STOP';
SELECT stop_id,
departure_gross_weight,
departure_net_weight,
departure_volume,
departure_fill_percent
FROM wsh_trip_stops
WHERE trip_id = p_trip_id ;
SELECT mode_of_transport,
NVL(ignore_for_planning,'N'), -- OTM R12,glog project
tp_plan_name
FROM wsh_trips
WHERE trip_id = p_trip_id;
SAVEPOINT create_update_stop_grp;
l_trips.delete;
wsh_debug_sv.push (l_module_name, 'Create_Update_Stop');
IF (p_in_rec.action_code IS NULL OR p_in_rec.action_code NOT IN ('CREATE','UPDATE') ) THEN
FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
ELSIF (p_in_rec.action_code = 'UPDATE' ) THEN
-- csun start of stop sequence change
l_handle_internal_stops := FALSE;
WSH_TRIP_STOPS_PVT.UPDATE_TRIP_STOP (
p_rowid => l_rec_attr_tab(x_index_id_tab(l_index)).rowid,
p_stop_info => l_rec_attr_tab(x_index_id_tab(l_index)),
x_return_status => l_return_status);
IF p_in_rec.action_code = 'UPDATE' THEN
l_status_code := l_rec_attr_tab(l_index).status_code;
ELSIF (p_in_rec.action_code = 'UPDATE' ) THEN
IF( WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
-- Bug 3848771
-- Mark as reprice required only when certain conditions are met
-- during update.
IF l_debug_on THEN
wsh_debug_sv.log(l_module_name,'l_rec_attr_tab(x_index_id_tab(l_index)).planned_arrival_date', l_rec_attr_tab(x_index_id_tab(l_index)).planned_arrival_date);
ROLLBACK to create_update_stop_grp;
rollback to create_update_stop_grp;
ROLLBACK TO create_update_stop_grp;
ROLLBACK TO create_update_stop_grp;
ROLLBACK TO create_update_stop_grp;
rollback to create_update_stop_grp;
wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
ROLLBACK TO create_update_stop_grp;
END CREATE_UPDATE_STOP;
PROCEDURE Create_Update_Stop
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_action_code IN VARCHAR2,
p_stop_info IN OUT NOCOPY WSH_TRIP_STOPS_GRP.Trip_Stop_Pub_Rec_Type,
p_trip_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_trip_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_stop_location_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
p_stop_location_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
p_planned_dep_date IN DATE DEFAULT FND_API.G_MISS_DATE,
x_stop_id OUT NOCOPY NUMBER) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Stop';
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_STOP';
wsh_debug_sv.push (l_module_name, 'Create_Update_Stop');
wsh_debug_sv.push (l_module_name, 'Create_Update_Stop');
WSH_INTERFACE_GRP.CREATE_UPDATE_STOP(
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => l_commit,
p_in_rec => l_in_rec,
p_rec_attr_tab => l_rec_attr_tab,
x_stop_out_tab => l_stop_out_tab,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
WSH_DEBUG_SV.log(l_module_name,'WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP x_return_status',x_return_status);
wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
END CREATE_UPDATE_STOP;
PROCEDURE Create_Update_Stop_New
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_stop_info IN OUT NOCOPY WSH_TRIP_STOPS_GRP.Trip_Stop_Pub_Rec_Type,
p_stop_IN_rec IN stopInRecType,
x_stop_OUT_rec OUT NOCOPY stopOutRecType) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Stop';
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_STOP_NEW';
WSH_INTERFACE_GRP.CREATE_UPDATE_STOP(
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => l_commit,
p_in_rec => p_stop_in_rec,
p_rec_attr_tab => l_rec_attr_tab,
x_stop_out_tab => l_stop_out_tab,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
WSH_DEBUG_SV.log(l_module_name,'WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP x_return_status',x_return_status);
wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
END CREATE_UPDATE_STOP_NEW;
SELECT wnd.delivery_id
FROM wsh_new_deliveries wnd, wsh_delivery_legs wdl
WHERE wnd.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = p_stop_id
AND wnd.initial_pickup_location_id = p_stop_loc_id;
SELECT wnd.delivery_id
FROM wsh_new_deliveries wnd, wsh_delivery_legs wdl
WHERE wnd.delivery_id = wdl.delivery_id
AND wdl.drop_off_stop_id = p_stop_id
AND wnd.ultimate_dropoff_location_id = p_stop_loc_id;
SELECT wts.trip_id, wts.stop_location_id, wt.status_code
FROM wsh_trip_stops wts, wsh_trips wt
WHERE wts.stop_id = p_stop_id
AND wts.trip_id = wt.trip_id;
SELECT wts.stop_id
FROM wsh_trip_stops wts
WHERE wts.trip_id = p_trip_id
AND wts.status_code <> 'CL'
AND wts.stop_id <> p_stop_id;
IF p_action_prms.action_code = 'UPDATE-STATUS'
AND p_action_prms.stop_action = 'CLOSE'
THEN
--{
--need to loop through each record in p_stop_attr_tab
l_index := p_stop_attr_tab.FIRST;
If trip is open, closing any stop requires trip status to be updated and hence trip's lock is needed.
If trip is In-Transit, then lock the trip only if all other stops have been closed already.
*/
IF l_trip_status = 'OP'
THEN
-- {
WSH_TRIPS_PVT.lock_trip_no_compare(
p_trip_id => l_trip_id
);
PROCEDURE Add_to_Delete_List(
p_stop_tab IN WSH_TRIP_STOPS_VALIDATIONS.stop_details_tab,
p_caller IN VARCHAR2,
x_stop_delete_tab OUT NOCOPY wsh_util_core.id_tab_type,
x_trip_affected_tab OUT NOCOPY wsh_util_core.id_tab_type,
x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR c_linked_stop(c_stop_id IN NUMBER, c_trip_id NUMBER) IS
SELECT physical_stop_id linked_stop_id,
1 link_type ,
trip_id
FROM wsh_trip_stops
WHERE stop_id = c_stop_id
AND physical_stop_id IS NOT NULL
AND trip_id = c_trip_id
UNION
SELECT stop_id linked_stop_id,
2 link_type ,
trip_id
FROM wsh_trip_stops
WHERE physical_stop_id = c_stop_id
AND status_code = 'OP'
AND trip_id = c_trip_id;
l_stop_delete_tab wsh_util_core.id_tab_type;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Add_to_Delete_List';
x_stop_delete_tab.delete;
l_stop_delete_tab.delete;
x_trip_affected_tab.delete;
j := l_stop_delete_tab.first;
IF l_stop_delete_tab(j) = p_stop_tab(i).stop_id THEN
l_stop_exist := TRUE;
j := l_stop_delete_tab.next(j);
l_stop_delete_tab(l_stop_delete_tab.count+1) := p_stop_tab(i).stop_id;
j := l_stop_delete_tab.first;
IF l_stop_delete_tab(j) = l_linked_stop.linked_stop_id THEN
l_stop_exist := TRUE;
j := l_stop_delete_tab.next(j);
l_stop_delete_tab(l_stop_delete_tab.count+1) := l_linked_stop.linked_stop_id;
l_stop_delete_tab(l_stop_delete_tab.count+1) := p_stop_tab(i).stop_id;
x_stop_delete_tab := l_stop_delete_tab;
wsh_util_core.default_handler('WSH_TRIP_STOPS_VALIDATIONS.Add_to_Delete_List',l_module_name);
END Add_to_Delete_List;