[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wsh_trip_stops_s.nextval
FROM sys.dual;
SELECT rowid
FROM wsh_trip_stops
WHERE stop_id = x_stop_id;
select decode(shipments_type_flag, 'M', 'O', shipments_type_flag)
from wsh_trips
where trip_id = p_trip_id;
l_trips.DELETE;
INSERT INTO wsh_trip_stops(
stop_id,
trip_id,
stop_location_id,
status_code,
stop_sequence_number,
planned_arrival_date,
planned_departure_date,
actual_arrival_date,
actual_departure_date,
departure_gross_weight,
departure_net_weight,
weight_uom_code,
departure_volume,
volume_uom_code,
departure_seal_code,
departure_fill_percent,
tp_attribute_category,
tp_attribute1,
tp_attribute2,
tp_attribute3,
tp_attribute4,
tp_attribute5,
tp_attribute6,
tp_attribute7,
tp_attribute8,
tp_attribute9,
tp_attribute10,
tp_attribute11,
tp_attribute12,
tp_attribute13,
tp_attribute14,
tp_attribute15,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
wsh_location_id,
tracking_drilldown_flag,
tracking_remarks,
carrier_est_departure_date,
carrier_est_arrival_date,
loading_start_datetime,
loading_end_datetime,
unloading_start_datetime,
unloading_end_datetime,
shipments_type_flag,
-- J: W/V Changes
wv_frozen_flag,
wkend_layover_stops,
wkday_layover_stops,
tp_stop_id,
physical_stop_id,
physical_location_id,
tms_interface_flag -- OTM R12, glog proj
) values(
x_stop_id,
p_trip_stop_info.trip_id,
p_trip_stop_info.stop_location_id,
nvl(p_trip_stop_info.status_code,'OP'),
nvl(p_trip_stop_info.stop_sequence_number,0),
nvl(p_trip_stop_info.planned_arrival_date,SYSDATE),
nvl(p_trip_stop_info.planned_departure_date,SYSDATE),
p_trip_stop_info.actual_arrival_date,
p_trip_stop_info.actual_departure_date,
p_trip_stop_info.departure_gross_weight,
p_trip_stop_info.departure_net_weight,
p_trip_stop_info.weight_uom_code,
p_trip_stop_info.departure_volume,
p_trip_stop_info.volume_uom_code,
p_trip_stop_info.departure_seal_code,
p_trip_stop_info.departure_fill_percent,
p_trip_stop_info.tp_attribute_category,
p_trip_stop_info.tp_attribute1,
p_trip_stop_info.tp_attribute2,
p_trip_stop_info.tp_attribute3,
p_trip_stop_info.tp_attribute4,
p_trip_stop_info.tp_attribute5,
p_trip_stop_info.tp_attribute6,
p_trip_stop_info.tp_attribute7,
p_trip_stop_info.tp_attribute8,
p_trip_stop_info.tp_attribute9,
p_trip_stop_info.tp_attribute10,
p_trip_stop_info.tp_attribute11,
p_trip_stop_info.tp_attribute12,
p_trip_stop_info.tp_attribute13,
p_trip_stop_info.tp_attribute14,
p_trip_stop_info.tp_attribute15,
p_trip_stop_info.attribute_category,
p_trip_stop_info.attribute1,
p_trip_stop_info.attribute2,
p_trip_stop_info.attribute3,
p_trip_stop_info.attribute4,
p_trip_stop_info.attribute5,
p_trip_stop_info.attribute6,
p_trip_stop_info.attribute7,
p_trip_stop_info.attribute8,
p_trip_stop_info.attribute9,
p_trip_stop_info.attribute10,
p_trip_stop_info.attribute11,
p_trip_stop_info.attribute12,
p_trip_stop_info.attribute13,
p_trip_stop_info.attribute14,
p_trip_stop_info.attribute15,
nvl(p_trip_stop_info.creation_date, SYSDATE),
nvl(p_trip_stop_info.created_by, FND_GLOBAL.USER_ID),
nvl(p_trip_stop_info.last_update_date, SYSDATE),
nvl(p_trip_stop_info.last_updated_by, FND_GLOBAL.USER_ID),
nvl(p_trip_stop_info.last_update_login, FND_GLOBAL.LOGIN_ID),
p_trip_stop_info.program_application_id,
p_trip_stop_info.program_id,
p_trip_stop_info.program_update_date,
p_trip_stop_info.request_id,
p_trip_stop_info.wsh_location_id,
p_trip_stop_info.tracking_drilldown_flag,
p_trip_stop_info.tracking_remarks,
p_trip_stop_info.carrier_est_departure_date,
p_trip_stop_info.carrier_est_arrival_date,
p_trip_stop_info.loading_start_datetime,
p_trip_stop_info.loading_end_datetime,
p_trip_stop_info.unloading_start_datetime,
p_trip_stop_info.unloading_end_datetime,
nvl(l_shipments_type_flag, 'O'),
-- J: W/V Changes
nvl(p_trip_stop_info.wv_frozen_flag, 'N'),
p_trip_stop_info.wkend_layover_stops,
p_trip_stop_info.wkday_layover_stops,
p_trip_stop_info.tp_stop_id,
p_trip_stop_info.physical_stop_id,
nvl(p_trip_stop_info.physical_location_id, l_physical_loc_id),
NULL --OTM R12, glog proj , create stops with null value for tms_interface_flag
);
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'INSERT',
x_return_status => l_dbi_rs);
PROCEDURE Delete_Trip_Stop(
p_rowid IN VARCHAR2,
p_stop_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_validate_flag IN VARCHAR2 DEFAULT 'Y',
--tkt
p_caller IN VARCHAR2
--tkt
) IS
CURSOR get_stop_id_rowid (v_rowid VARCHAR2) IS
SELECT stop_id, trip_id
FROM wsh_trip_stops
WHERE rowid = v_rowid;
SELECT trip_id
FROM wsh_trip_stops
WHERE stop_id = v_stop_id;
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE pick_up_stop_id = cp_stop_id OR
drop_off_stop_id = cp_stop_id;
SELECT NVL(ignore_for_planning, 'N'),
tp_plan_name
FROM WSH_TRIPS
WHERE trip_id = p_trip_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_TRIP_STOP';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.CHECK_STOP_DELETE',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_TRIP_VALIDATIONS.check_stop_delete(
p_stop_id => l_stop_id,
x_return_status => l_return_status,
p_caller => p_caller);
WSH_DEBUG_SV.log(l_module_name,'CHECK_STOP_DELETE x_return_status',x_return_status);
FND_MESSAGE.SET_NAME('WSH','WSH_STOP_DELETE_ERROR');
DELETE FROM wsh_freight_costs
WHERE stop_id = l_stop_id;
WSH_DEBUG_SV.log(l_module_name,'Calling Delete_Delivery_Leg delivery_leg_id',rec.delivery_leg_id);
WSH_DELIVERY_LEGS_PVT.Delete_Delivery_Leg (
p_delivery_leg_id => rec.delivery_leg_id,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'After Calling Delete_Delivery_Leg x_return_status',x_return_status);
DELETE FROM wsh_trip_stops
WHERE stop_id = l_stop_id;
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'DELETE',
x_return_status => l_dbi_rs);
wsh_util_core.default_handler('WSH_TRIP_STOPS_PVT.DELETE_TRIP_STOP',l_module_name);
END Delete_Trip_Stop;
procedure Update_Trip_Stop(
p_rowid IN VARCHAR2,
p_stop_info IN trip_stop_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
-- J: W/V Changes
CURSOR get_stop_info IS
SELECT rowid,
departure_gross_weight,
departure_net_weight,
departure_volume,
weight_uom_code,
volume_uom_code,
NVL(wv_frozen_flag,'Y')
FROM wsh_trip_stops
WHERE stop_id = p_stop_info.stop_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TRIP_STOP';
UPDATE wsh_trip_stops SET
stop_id = p_stop_info.stop_id,
trip_id = p_stop_info.trip_id,
stop_location_id = p_stop_info.stop_location_id,
status_code = p_stop_info.status_code,
stop_sequence_number = p_stop_info.stop_sequence_number,
planned_arrival_date = p_stop_info.planned_arrival_date,
planned_departure_date = p_stop_info.planned_departure_date,
actual_arrival_date = p_stop_info.actual_arrival_date,
actual_departure_date = p_stop_info.actual_departure_date,
departure_gross_weight = p_stop_info.departure_gross_weight,
departure_net_weight = p_stop_info.departure_net_weight,
weight_uom_code = p_stop_info.weight_uom_code,
departure_volume = p_stop_info.departure_volume,
volume_uom_code = p_stop_info.volume_uom_code,
departure_seal_code = p_stop_info.departure_seal_code,
departure_fill_percent = p_stop_info.departure_fill_percent,
tp_attribute_category = p_stop_info.tp_attribute_category,
tp_attribute1 = p_stop_info.tp_attribute1,
tp_attribute2 = p_stop_info.tp_attribute2,
tp_attribute3 = p_stop_info.tp_attribute3,
tp_attribute4 = p_stop_info.tp_attribute4,
tp_attribute5 = p_stop_info.tp_attribute5,
tp_attribute6 = p_stop_info.tp_attribute6,
tp_attribute7 = p_stop_info.tp_attribute7,
tp_attribute8 = p_stop_info.tp_attribute8,
tp_attribute9 = p_stop_info.tp_attribute9,
tp_attribute10 = p_stop_info.tp_attribute10,
tp_attribute11 = p_stop_info.tp_attribute11,
tp_attribute12 = p_stop_info.tp_attribute12,
tp_attribute13 = p_stop_info.tp_attribute13,
tp_attribute14 = p_stop_info.tp_attribute14,
tp_attribute15 = p_stop_info.tp_attribute15,
attribute_category = p_stop_info.attribute_category,
attribute1 = p_stop_info.attribute1,
attribute2 = p_stop_info.attribute2,
attribute3 = p_stop_info.attribute3,
attribute4 = p_stop_info.attribute4,
attribute5 = p_stop_info.attribute5,
attribute6 = p_stop_info.attribute6,
attribute7 = p_stop_info.attribute7,
attribute8 = p_stop_info.attribute8,
attribute9 = p_stop_info.attribute9,
attribute10 = p_stop_info.attribute10,
attribute11 = p_stop_info.attribute11,
attribute12 = p_stop_info.attribute12,
attribute13 = p_stop_info.attribute13,
attribute14 = p_stop_info.attribute14,
attribute15 = p_stop_info.attribute15,
last_update_date = p_stop_info.last_update_date,
last_updated_by = p_stop_info.last_updated_by,
last_update_login = p_stop_info.last_update_login,
program_application_id = p_stop_info.program_application_id,
program_id = p_stop_info.program_id,
program_update_date = p_stop_info.program_update_date,
request_id = p_stop_info.request_id,
wsh_location_id = p_stop_info.wsh_location_id,
tracking_drilldown_flag = p_stop_info.tracking_drilldown_flag,
tracking_remarks = p_stop_info.tracking_remarks,
carrier_est_departure_date = p_stop_info.carrier_est_departure_date,
carrier_est_arrival_date = p_stop_info.carrier_est_arrival_date,
loading_start_datetime = p_stop_info.loading_start_datetime,
loading_end_datetime = p_stop_info.loading_end_datetime,
unloading_start_datetime = p_stop_info.unloading_start_datetime,
unloading_end_datetime = p_stop_info.unloading_end_datetime,
shipments_type_flag = nvl(p_stop_info.shipments_type_flag, 'O'),
-- J: W/V Changes
wv_frozen_flag = l_frozen_flag,
wkend_layover_stops = p_stop_info.wkend_layover_stops,
wkday_layover_stops = p_stop_info.wkday_layover_stops,
tp_stop_id = p_stop_info.tp_stop_id,
physical_stop_id = p_stop_info.physical_stop_id,
physical_location_id = p_stop_info.physical_location_id,
TMS_INTERFACE_FLAG = p_stop_info.tms_interface_flag --OTM R12,glog proj
WHERE rowid = l_rowid;
WSH_INTEGRATION.dbi_update_trip_stop_log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
wsh_util_core.default_handler('WSH_TRIP_STOPS_PVT.UPDATE_TRIP_STOP',l_module_name);
END Update_Trip_Stop;
SELECT *
FROM wsh_trip_stops
WHERE rowid = p_rowid
FOR UPDATE OF trip_id NOWAIT;
FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
AND ( (Recinfo.Last_Update_Date = p_stop_info.Last_Update_Date)
OR ( (Recinfo.Last_Update_Date IS NULL)
AND (p_stop_info.Last_Update_Date IS NULL)))
AND ( (Recinfo.Last_Updated_By = p_stop_info.Last_Updated_By)
OR ( (Recinfo.Last_Updated_By IS NULL)
AND (p_stop_info.Last_Updated_By IS NULL)))
AND ( (Recinfo.Last_Update_Login = p_stop_info.Last_Update_Login)
OR ( (Recinfo.Last_Update_Login IS NULL)
AND (p_stop_info.Last_Update_Login IS NULL)))
AND ( (Recinfo.Program_Application_Id = p_stop_info.Program_Application_Id)
OR ( (Recinfo.Program_Application_Id IS NULL)
AND (p_stop_info.Program_Application_Id IS NULL)))
AND ( (Recinfo.Program_Id = p_stop_info.Program_Id)
OR ( (Recinfo.Program_Id IS NULL)
AND (p_stop_info.Program_Id IS NULL)))
AND ( (Recinfo.Program_Update_Date = p_stop_info.Program_Update_Date)
OR ( (Recinfo.Program_Update_Date IS NULL)
AND (p_stop_info.Program_Update_Date IS NULL)))
AND ( (Recinfo.Request_Id = p_stop_info.Request_Id)
OR ( (Recinfo.Request_Id IS NULL)
AND (p_stop_info.Request_Id IS NULL)))
AND ( (Recinfo.Attribute_Category = p_stop_info.Attribute_Category)
OR ( (Recinfo.Attribute_Category IS NULL)
AND (p_stop_info.Attribute_Category IS NULL)))
AND ( (Recinfo.Attribute1 = p_stop_info.Attribute1)
OR ( (Recinfo.Attribute1 IS NULL)
AND (p_stop_info.Attribute1 IS NULL)))
AND ( (Recinfo.Attribute2 = p_stop_info.Attribute2)
OR ( (Recinfo.Attribute2 IS NULL)
AND (p_stop_info.Attribute2 IS NULL)))
AND ( (Recinfo.Attribute3 = p_stop_info.Attribute3)
OR ( (Recinfo.Attribute3 IS NULL)
AND (p_stop_info.Attribute3 IS NULL)))
AND ( (Recinfo.Attribute4 = p_stop_info.Attribute4)
OR ( (Recinfo.Attribute4 IS NULL)
AND (p_stop_info.Attribute4 IS NULL)))
AND ( (Recinfo.Attribute5 = p_stop_info.Attribute5)
OR ( (Recinfo.Attribute5 IS NULL)
AND (p_stop_info.Attribute5 IS NULL)))
AND ( (Recinfo.Attribute6 = p_stop_info.Attribute6)
OR ( (Recinfo.Attribute6 IS NULL)
AND (p_stop_info.Attribute6 IS NULL)))
AND ( (Recinfo.Attribute7 = p_stop_info.Attribute7)
OR ( (Recinfo.Attribute7 IS NULL)
AND (p_stop_info.Attribute7 IS NULL)))
AND ( (Recinfo.Attribute8 = p_stop_info.Attribute8)
OR ( (Recinfo.Attribute8 IS NULL)
AND (p_stop_info.Attribute8 IS NULL)))
AND ( (Recinfo.Attribute9 = p_stop_info.Attribute9)
OR ( (Recinfo.Attribute9 IS NULL)
AND (p_stop_info.Attribute9 IS NULL)))
AND ( (Recinfo.Attribute10 = p_stop_info.Attribute10)
OR ( (Recinfo.Attribute10 IS NULL)
AND (p_stop_info.Attribute10 IS NULL)))
AND ( (Recinfo.Attribute11 = p_stop_info.Attribute11)
OR ( (Recinfo.Attribute11 IS NULL)
AND (p_stop_info.Attribute11 IS NULL)))
AND ( (Recinfo.Attribute12 = p_stop_info.Attribute12)
OR ( (Recinfo.Attribute12 IS NULL)
AND (p_stop_info.Attribute12 IS NULL)))
AND ( (Recinfo.Attribute13 = p_stop_info.Attribute13)
OR ( (Recinfo.Attribute13 IS NULL)
AND (p_stop_info.Attribute13 IS NULL)))
AND ( (Recinfo.Attribute14 = p_stop_info.Attribute14)
OR ( (Recinfo.Attribute14 IS NULL)
AND (p_stop_info.Attribute14 IS NULL)))
AND ( (Recinfo.Attribute15 = p_stop_info.Attribute15)
OR ( (Recinfo.Attribute15 IS NULL)
AND (p_stop_info.Attribute15 IS NULL)))
AND ( (Recinfo.Tp_Attribute_Category = p_stop_info.Tp_Attribute_Category)
OR ( (Recinfo.Tp_Attribute_Category IS NULL)
AND (p_stop_info.Tp_Attribute_Category IS NULL)))
AND ( (Recinfo.Tp_Attribute1 = p_stop_info.Tp_Attribute1)
OR ( (Recinfo.Tp_Attribute1 IS NULL)
AND (p_stop_info.Tp_Attribute1 IS NULL)))
AND ( (Recinfo.Tp_Attribute2 = p_stop_info.Tp_Attribute2)
OR ( (Recinfo.Tp_Attribute2 IS NULL)
AND (p_stop_info.Tp_Attribute2 IS NULL)))
AND ( (Recinfo.Tp_Attribute3 = p_stop_info.Tp_Attribute3)
OR ( (Recinfo.Tp_Attribute3 IS NULL)
AND (p_stop_info.Tp_Attribute3 IS NULL)))
AND ( (Recinfo.Tp_Attribute4 = p_stop_info.Tp_Attribute4)
OR ( (Recinfo.Tp_Attribute4 IS NULL)
AND (p_stop_info.Tp_Attribute4 IS NULL)))
AND ( (Recinfo.Tp_Attribute5 = p_stop_info.Tp_Attribute5)
OR ( (Recinfo.Tp_Attribute5 IS NULL)
AND (p_stop_info.Tp_Attribute5 IS NULL)))
AND ( (Recinfo.Tp_Attribute6 = p_stop_info.Tp_Attribute6)
OR ( (Recinfo.Tp_Attribute6 IS NULL)
AND (p_stop_info.Tp_Attribute6 IS NULL)))
AND ( (Recinfo.Tp_Attribute7 = p_stop_info.Tp_Attribute7)
OR ( (Recinfo.Tp_Attribute7 IS NULL)
AND (p_stop_info.Tp_Attribute7 IS NULL)))
AND ( (Recinfo.Tp_Attribute8 = p_stop_info.Tp_Attribute8)
OR ( (Recinfo.Tp_Attribute8 IS NULL)
AND (p_stop_info.Tp_Attribute8 IS NULL)))
AND ( (Recinfo.Tp_Attribute9 = p_stop_info.Tp_Attribute9)
OR ( (Recinfo.Tp_Attribute9 IS NULL)
AND (p_stop_info.Tp_Attribute9 IS NULL)))
AND ( (Recinfo.Tp_Attribute10 = p_stop_info.Tp_Attribute10)
OR ( (Recinfo.Tp_Attribute10 IS NULL)
AND (p_stop_info.Tp_Attribute10 IS NULL)))
AND ( (Recinfo.Tp_Attribute11 = p_stop_info.Tp_Attribute11)
OR ( (Recinfo.Tp_Attribute11 IS NULL)
AND (p_stop_info.Tp_Attribute11 IS NULL)))
AND ( (Recinfo.Tp_Attribute12 = p_stop_info.Tp_Attribute12)
OR ( (Recinfo.Tp_Attribute12 IS NULL)
AND (p_stop_info.Tp_Attribute12 IS NULL)))
AND ( (Recinfo.Tp_Attribute13 = p_stop_info.Tp_Attribute13)
OR ( (Recinfo.Tp_Attribute13 IS NULL)
AND (p_stop_info.Tp_Attribute13 IS NULL)))
AND ( (Recinfo.Tp_Attribute14 = p_stop_info.Tp_Attribute14)
OR ( (Recinfo.Tp_Attribute14 IS NULL)
AND (p_stop_info.Tp_Attribute14 IS NULL)))
AND ( (Recinfo.Tp_Attribute15 = p_stop_info.Tp_Attribute15)
OR ( (Recinfo.Tp_Attribute15 IS NULL)
AND (p_stop_info.Tp_Attribute15 IS NULL)))
AND ( (Recinfo.wsh_location_id = p_stop_info.wsh_location_id)
OR ( (Recinfo.wsh_location_id IS NULL)
AND (p_stop_info.wsh_location_id IS NULL)))
AND ( (Recinfo.tracking_drilldown_flag = p_stop_info.tracking_drilldown_flag)
OR ( (Recinfo.tracking_drilldown_flag IS NULL)
AND (p_stop_info.tracking_drilldown_flag IS NULL)))
AND ( (Recinfo.tracking_remarks = p_stop_info.tracking_remarks)
OR ( (Recinfo.tracking_remarks IS NULL)
AND (p_stop_info.tracking_remarks IS NULL)))
AND ( (Recinfo.carrier_est_departure_date = p_stop_info.carrier_est_departure_date)
OR ( (Recinfo.carrier_est_departure_date IS NULL)
AND (p_stop_info.carrier_est_departure_date IS NULL)))
AND ( (Recinfo.carrier_est_arrival_date = p_stop_info.carrier_est_arrival_date)
OR ( (Recinfo.carrier_est_arrival_date IS NULL)
AND (p_stop_info.carrier_est_arrival_date IS NULL)))
AND ( (Recinfo.loading_start_datetime = p_stop_info.loading_start_datetime)
OR ( (Recinfo.loading_start_datetime IS NULL)
AND (p_stop_info.loading_start_datetime IS NULL)))
AND ( (Recinfo.loading_end_datetime = p_stop_info.loading_end_datetime)
OR ( (Recinfo.loading_end_datetime IS NULL)
AND (p_stop_info.loading_end_datetime IS NULL)))
AND ( (Recinfo.unloading_start_datetime = p_stop_info.unloading_start_datetime)
OR ( (Recinfo.unloading_start_datetime IS NULL)
AND (p_stop_info.unloading_start_datetime IS NULL)))
AND ( (Recinfo.unloading_end_datetime = p_stop_info.unloading_end_datetime)
OR ( (Recinfo.unloading_end_datetime IS NULL)
AND (p_stop_info.unloading_end_datetime IS NULL)))
AND ( (nvl(Recinfo.shipments_type_flag, 'O') = nvl(p_stop_info.shipments_type_flag,'O'))
OR ( (Recinfo.shipments_type_flag IS NULL)
AND (p_stop_info.shipments_type_flag IS NULL)))
-- J: W/V Changes
AND ( (Recinfo.wv_frozen_flag = p_stop_info.wv_frozen_flag)
OR ( (Recinfo.wv_frozen_flag IS NULL)
AND (p_stop_info.wv_frozen_flag IS NULL)))
AND ( (Recinfo.tp_stop_id = p_stop_info.tp_stop_id)
OR ( (Recinfo.tp_stop_id IS NULL)
AND (p_stop_info.tp_stop_id IS NULL)))
AND ( (Recinfo.wkend_layover_stops = p_stop_info.wkend_layover_stops)
OR ( (Recinfo.wkend_layover_stops IS NULL)
AND (p_stop_info.wkend_layover_stops IS NULL)))
AND ( (Recinfo.wkday_layover_stops = p_stop_info.wkday_layover_stops)
OR ( (Recinfo.wkday_layover_stops IS NULL)
AND (p_stop_info.wkday_layover_stops IS NULL)))
AND ( (Recinfo.physical_stop_id = p_stop_info.physical_stop_id)
OR ( (Recinfo.physical_stop_id IS NULL)
AND (p_stop_info.physical_stop_id IS NULL)))
AND ( (Recinfo.physical_location_id = p_stop_info.physical_location_id)
OR ( (Recinfo.physical_location_id IS NULL)
AND (p_stop_info.physical_location_id IS NULL)))
-- OTM R12, glog proj
AND ( (Recinfo.TMS_INTERFACE_FLAG = p_stop_info.TMS_INTERFACE_FLAG)
OR ( (Recinfo.TMS_INTERFACE_FLAG is NULL)
AND (p_stop_info.TMS_INTERFACE_FLAG is NULL)))
) THEN
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.pop(l_module_name,'RETURN');
SELECT
STOP_ID,
TRIP_ID,
STOP_LOCATION_ID,
STATUS_CODE,
STOP_SEQUENCE_NUMBER,
PLANNED_ARRIVAL_DATE,
PLANNED_DEPARTURE_DATE,
ACTUAL_ARRIVAL_DATE,
ACTUAL_DEPARTURE_DATE,
DEPARTURE_GROSS_WEIGHT,
DEPARTURE_NET_WEIGHT,
WEIGHT_UOM_CODE,
DEPARTURE_VOLUME,
VOLUME_UOM_CODE,
DEPARTURE_SEAL_CODE,
DEPARTURE_FILL_PERCENT,
TP_ATTRIBUTE_CATEGORY,
TP_ATTRIBUTE1,
TP_ATTRIBUTE2,
TP_ATTRIBUTE3,
TP_ATTRIBUTE4,
TP_ATTRIBUTE5,
TP_ATTRIBUTE6,
TP_ATTRIBUTE7,
TP_ATTRIBUTE8,
TP_ATTRIBUTE9,
TP_ATTRIBUTE10,
TP_ATTRIBUTE11,
TP_ATTRIBUTE12,
TP_ATTRIBUTE13,
TP_ATTRIBUTE14,
TP_ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
WSH_LOCATION_ID,
TRACKING_DRILLDOWN_FLAG,
TRACKING_REMARKS,
CARRIER_EST_DEPARTURE_DATE,
CARRIER_EST_ARRIVAL_DATE,
LOADING_START_DATETIME,
LOADING_END_DATETIME,
UNLOADING_START_DATETIME,
UNLOADING_END_DATETIME,
ROWID,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
SHIPMENTS_TYPE_FLAG,
-- J: W/V Changes
WV_FROZEN_FLAG,
wkend_layover_stops,
wkday_layover_stops,
tp_stop_id,
physical_stop_id,
physical_location_id,
tms_interface_flag -- OTM R12, glog proj
FROM wsh_trip_stops
WHERE stop_id = p_stop_id;
SELECT stop_location_id, physical_location_id
FROM wsh_trip_stops
WHERE stop_id = p_stop_id;
SELECT status_code,
stop_sequence_number, -- Bug 3814592
planned_arrival_date, -- Bug 3814592
SHIPMENTS_TYPE_FLAG , --3509004:public api changes
physical_stop_id -- csun 10+ internal location
FROM wsh_trip_stops
WHERE stop_id = x_stop_id;
SELECT stop_id
FROM wsh_trip_stops
WHERE trip_id = x_trip_id
AND status_code = 'CL'
AND physical_stop_id = x_stop_id;
SELECT status_code, planned_flag,tp_plan_name
FROM wsh_trips
WHERE trip_id = p_entity_id;
SELECT delivery_id
FROM wsh_delivery_legs
WHERE pick_up_stop_id = x_stop_id
AND rownum = 1;
SELECT delivery_id
FROM wsh_delivery_legs
WHERE drop_off_stop_id = x_stop_id
AND rownum = 1;
CURSOR get_updateable_open_stop (v_stop_sequence_number NUMBER)IS
SELECT cur.stop_id
FROM wsh_trip_stops cur
WHERE cur.trip_id = p_parent_entity_id
AND cur.stop_id <> p_stop_id
AND cur.status_code in ('CL','AR')
AND cur.stop_sequence_number > v_stop_sequence_number;
l_open_stop get_updateable_open_stop%ROWTYPE;
x_disabled_list.DELETE;
OPEN get_updateable_open_stop(l_stop_sequence_number);
FETCH get_updateable_open_stop
INTO l_open_stop;
l_found := get_updateable_open_stop%FOUND;
CLOSE get_updateable_open_stop;
-- Update on inbound trip stops are allowed only if caller
-- starts with one of the following:
-- - FTE
-- - WSH_IB
-- - WSH_PUB
-- - WSH_TP_RELEASE
--
IF NVL(l_shipments_type_flag,'O') = 'I'
AND NVL(p_caller, '!!!') NOT LIKE 'FTE%'
AND NVL(p_caller, '!!!') NOT LIKE 'WSH_PUB%'
AND NVL(p_caller, '!!!') NOT LIKE 'WSH_IB%'
AND NVL(p_caller, '!!!') NOT LIKE 'WSH_TP_RELEASE%'
THEN
RAISE e_all_disabled;
-- For inbound/mixed stops, weight/volume are updateable even if stop is closed.
--
-- For mixed stops, update is allowed only if caller starts with FTE/WSH_PUB/WSH_IB
--
IF x_disabled_list(1) = 'FULL'
THEN
i := x_disabled_list.count;
IF get_updateable_open_stop%ISOPEN THEN
CLOSE get_updateable_open_stop;
SELECT stop_id
FROM wsh_trip_stops
WHERE stop_id = p_stop_id
FOR UPDATE NOWAIT;
Procedure Update_Tms_interface_flag
(p_stop_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
p_tms_interface_flag_tab IN WSH_UTIL_CORE.COLUMN_TAB_TYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TMS_INTERFACE_FLAG';
SAVEPOINT update_tms_interface;
UPDATE wsh_trip_stops
SET TMS_INTERFACE_FLAG = p_tms_interface_flag_tab(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE STOP_ID = p_stop_id_tab(i);
WSH_INTEGRATION.dbi_update_trip_stop_log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
ROLLBACK TO update_tms_interface;
ROLLBACK TO update_tms_interface;
ROLLBACK TO update_tms_interface;
WSH_DEBUG_SV.logmsg(l_module_name,'Record_locked exception has occured. Cannot update stop tms_interface_flag', WSH_DEBUG_SV.C_EXCEP_LEVEL);
ROLLBACK TO update_tms_interface;
wsh_util_core.default_handler('WSH_TRIP_STOPS_PVT.UPDATE_TMS_INTERFACE_FLAG',l_module_name);
END UPDATE_TMS_INTERFACE_FLAG;