[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rowid
FROM wsh_delivery_legs
WHERE delivery_leg_id = x_delivery_leg_id;
SELECT wsh_delivery_legs_s.nextval
FROM sys.dual;
INSERT INTO wsh_delivery_legs (
delivery_leg_id
,delivery_id
,sequence_number
,pick_up_stop_id
,drop_off_stop_id
,gross_weight
,net_weight
,weight_uom_code
,volume
,volume_uom_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_application_id
,program_id
,program_update_date
,request_id
,load_tender_status
/* H Integration: datamodel changes wrudge */
,fte_trip_id
,reprice_required
,actual_arrival_date
,actual_departure_date
,actual_receipt_date
,tracking_drilldown_flag
,status_code
,tracking_remarks
,carrier_est_departure_date
,carrier_est_arrival_date
,loading_start_datetime
,loading_end_datetime
,unloading_start_datetime
,unloading_end_datetime
,delivered_quantity
,loaded_quantity
,received_quantity
,origin_stop_id
,destination_stop_id
,parent_delivery_leg_id
) VALUES (
x_delivery_leg_id
,p_delivery_leg_info.delivery_id
,nvl(p_delivery_leg_info.sequence_number, -99)
,nvl(p_delivery_leg_info.pick_up_stop_id, -99)
,nvl(p_delivery_leg_info.drop_off_stop_id, -99)
,p_delivery_leg_info.gross_weight
,p_delivery_leg_info.net_weight
,p_delivery_leg_info.weight_uom_code
,p_delivery_leg_info.volume
,p_delivery_leg_info.volume_uom_code
,nvl(p_delivery_leg_info.creation_date, SYSDATE)
,nvl(p_delivery_leg_info.created_by, FND_GLOBAL.USER_ID)
,nvl(p_delivery_leg_info.last_update_date, SYSDATE)
,nvl(p_delivery_leg_info.last_updated_by, FND_GLOBAL.USER_ID)
,nvl(p_delivery_leg_info.last_update_login, FND_GLOBAL.LOGIN_ID)
,p_delivery_leg_info.program_application_id
,p_delivery_leg_info.program_id
,p_delivery_leg_info.program_update_date
,p_delivery_leg_info.request_id
,nvl(p_delivery_leg_info.load_tender_status, 'N')
/* H Integration: datamodel changes wrudge */
,p_delivery_leg_info.fte_trip_id
,NVL(p_delivery_leg_info.reprice_required, 'N')
,p_delivery_leg_info.actual_arrival_date
,p_delivery_leg_info.actual_departure_date
,p_delivery_leg_info.actual_receipt_date
,p_delivery_leg_info.tracking_drilldown_flag
,p_delivery_leg_info.status_code
,p_delivery_leg_info.tracking_remarks
,p_delivery_leg_info.carrier_est_departure_date
,p_delivery_leg_info.carrier_est_arrival_date
,p_delivery_leg_info.loading_start_datetime
,p_delivery_leg_info.loading_end_datetime
,p_delivery_leg_info.unloading_start_datetime
,p_delivery_leg_info.unloading_end_datetime
,p_delivery_leg_info.delivered_quantity
,p_delivery_leg_info.loaded_quantity
,p_delivery_leg_info.received_quantity
,p_delivery_leg_info.origin_stop_id
,p_delivery_leg_info.destination_stop_id
,p_delivery_leg_info.parent_delivery_leg_id
);
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_DEBUG_SV.log(l_module_name,'Rows inserted',SQL%ROWCOUNT);
l_freight_cost_info.last_update_date := SYSDATE;
l_freight_cost_info.last_updated_by := FND_GLOBAL.USER_ID;
PROCEDURE Update_Delivery_Leg(
p_rowid IN VARCHAR2 := NULL,
p_delivery_leg_info IN Delivery_Leg_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR get_rowid IS
SELECT rowid
FROM wsh_delivery_legs
WHERE delivery_leg_id = p_delivery_leg_info.delivery_leg_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY_LEG';
UPDATE wsh_delivery_legs
SET
delivery_leg_id = p_delivery_leg_info.delivery_leg_id
, delivery_id = p_delivery_leg_info.delivery_id
,sequence_number = p_delivery_leg_info.sequence_number
,pick_up_stop_id = p_delivery_leg_info.pick_up_stop_id
,drop_off_stop_id = p_delivery_leg_info.drop_off_stop_id
,Gross_Weight = p_delivery_leg_info.Gross_Weight
,Net_Weight = p_delivery_leg_info.Net_Weight
,Weight_Uom_Code = p_delivery_leg_info.Weight_Uom_Code
,Volume = p_delivery_leg_info.Volume
,Volume_Uom_Code = p_delivery_leg_info.Volume_Uom_Code
,Last_Update_Date = NVL(p_delivery_leg_info.Last_Update_Date, sysdate)
,Last_Updated_By = NVL(p_delivery_leg_info.Last_Updated_By, fnd_global.user_id)
,Last_Update_Login = p_delivery_leg_info.Last_Update_Login
,Program_Application_Id = p_delivery_leg_info.Program_Application_Id
,Program_Id = p_delivery_leg_info.Program_Id
,Program_Update_Date = p_delivery_leg_info.Program_Update_Date
,Request_Id = p_delivery_leg_info.Request_Id
,Load_Tender_Status = p_delivery_leg_info.Load_Tender_Status
/* Changes for the shipping data model Bug#1918342*/
,SHIPPER_TITLE = p_delivery_leg_info.SHIPPER_TITLE
,SHIPPER_PHONE = p_delivery_leg_info.SHIPPER_PHONE
,POD_FLAG = p_delivery_leg_info.POD_FLAG
,POD_BY = p_delivery_leg_info.POD_BY
,POD_DATE = p_delivery_leg_info.POD_DATE
,EXPECTED_POD_DATE = p_delivery_leg_info.EXPECTED_POD_DATE
,BOOKING_OFFICE = p_delivery_leg_info.BOOKING_OFFICE
,SHIPPER_EXPORT_REF = p_delivery_leg_info.SHIPPER_EXPORT_REF
,CARRIER_EXPORT_REF = p_delivery_leg_info.CARRIER_EXPORT_REF
,DOC_NOTIFY_PARTY = p_delivery_leg_info.DOC_NOTIFY_PARTY
,AETC_NUMBER = p_delivery_leg_info.AETC_NUMBER
,SHIPPER_SIGNED_BY = p_delivery_leg_info.SHIPPER_SIGNED_BY
,SHIPPER_DATE = p_delivery_leg_info.SHIPPER_DATE
,CARRIER_SIGNED_BY = p_delivery_leg_info.CARRIER_SIGNED_BY
,CARRIER_DATE = p_delivery_leg_info.CARRIER_DATE
,DOC_ISSUE_OFFICE = p_delivery_leg_info.DOC_ISSUE_OFFICE
,DOC_ISSUED_BY = p_delivery_leg_info.DOC_ISSUED_BY
,DOC_DATE_ISSUED = p_delivery_leg_info.DOC_DATE_ISSUED
,SHIPPER_HM_BY = p_delivery_leg_info.SHIPPER_HM_BY
,SHIPPER_HM_DATE = p_delivery_leg_info.SHIPPER_HM_DATE
,CARRIER_HM_BY = p_delivery_leg_info.CARRIER_HM_BY
,CARRIER_HM_DATE = p_delivery_leg_info.CARRIER_HM_DATE
,BOOKING_NUMBER = p_delivery_leg_info.BOOKING_NUMBER
,PORT_OF_LOADING = P_delivery_leg_info.PORT_OF_LOADING
,PORT_OF_DISCHARGE = p_delivery_leg_info.PORT_OF_DISCHARGE
,SERVICE_CONTRACT = p_delivery_leg_info.SERVICE_CONTRACT
,BILL_FREIGHT_TO = p_delivery_leg_info.BILL_FREIGHT_TO
/* H Integration: datamodel changes wrudge */
,FTE_TRIP_ID = p_delivery_leg_info.FTE_TRIP_ID
,REPRICE_REQUIRED = p_delivery_leg_info.REPRICE_REQUIRED
,ACTUAL_ARRIVAL_DATE = p_delivery_leg_info.ACTUAL_ARRIVAL_DATE
,ACTUAL_DEPARTURE_DATE = p_delivery_leg_info.ACTUAL_DEPARTURE_DATE
,ACTUAL_RECEIPT_DATE = p_delivery_leg_info.ACTUAL_RECEIPT_DATE
,TRACKING_DRILLDOWN_FLAG = p_delivery_leg_info.TRACKING_DRILLDOWN_FLAG
,STATUS_CODE = p_delivery_leg_info.STATUS_CODE
,TRACKING_REMARKS = p_delivery_leg_info.TRACKING_REMARKS
,CARRIER_EST_DEPARTURE_DATE = p_delivery_leg_info.CARRIER_EST_DEPARTURE_DATE
,CARRIER_EST_ARRIVAL_DATE = p_delivery_leg_info.CARRIER_EST_ARRIVAL_DATE
,LOADING_START_DATETIME = p_delivery_leg_info.LOADING_START_DATETIME
,LOADING_END_DATETIME = p_delivery_leg_info.LOADING_END_DATETIME
,UNLOADING_START_DATETIME = p_delivery_leg_info.UNLOADING_START_DATETIME
,UNLOADING_END_DATETIME = p_delivery_leg_info.UNLOADING_END_DATETIME
,DELIVERED_QUANTITY = p_delivery_leg_info.DELIVERED_QUANTITY
,LOADED_QUANTITY = p_delivery_leg_info.LOADED_QUANTITY
,RECEIVED_QUANTITY = p_delivery_leg_info.RECEIVED_QUANTITY
,ORIGIN_STOP_ID = p_delivery_leg_info.ORIGIN_STOP_ID
,DESTINATION_STOP_ID = p_delivery_leg_info.DESTINATION_STOP_ID
,parent_delivery_leg_id = p_delivery_leg_info.parent_delivery_leg_id
WHERE rowid = l_rowid;
WSH_DEBUG_SV.log(l_module_name,'Rows updated',SQL%ROWCOUNT);
wsh_util_core.default_handler('WSH_DELIVERY_LEGS_PVT.UPDATE_DELIVERY_LEG',l_module_name);
END Update_Delivery_Leg;
PROCEDURE Delete_Delivery_Leg
(p_rowid IN VARCHAR2 := NULL,
p_delivery_leg_id IN NUMBER := NULL,
x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR get_del_leg_id_rowid (v_rowid VARCHAR2) IS
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE rowid = v_rowid;
SELECT entity_id
FROM wsh_document_instances
WHERE entity_id = l_leg_id AND
entity_name = 'WSH_DELIVERY_LEGS' AND
status <> 'CANCELLED'
FOR UPDATE NOWAIT;
SELECT pick_up_stop_id,drop_off_stop_id
FROM wsh_delivery_legs
WHERE delivery_leg_id = v_del_leg_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_DELIVERY_LEG';
SELECT pick_up_stop_id, drop_off_stop_id, wts1.trip_id,
nvl(shipment_direction,'O') shipment_direction,
wt.name trip_name,
wdl.delivery_id --J-IB-HEALI
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts1,
wsh_new_deliveries wnd,
wsh_trips wt
WHERE delivery_leg_id = p_delivery_leg_id
AND wdl.pick_up_stop_id = wts1.stop_id
AND wdl.delivery_id = wnd.delivery_id
AND wts1.trip_id = wt.trip_id;
SELECT NVL(wts1.shipments_type_flag,'O') pu_stop_shipments_type_flag,
NVL(wts2.shipments_type_flag,'O') do_stop_shipments_type_flag,
NVL(wt.shipments_type_flag,'O') trip_shipments_type_flag,
wt.status_Code trip_status_Code
FROM wsh_trip_stops wts1,
wsh_trip_stops wts2,
wsh_trips wt
WHERE wt.trip_id = p_trip_id
AND wts1.stop_id = p_pickup_stop_id
AND wts2.stop_id = p_dropoff_stop_id
FOR UPDATE OF wt.shipments_type_flag NOWAIT;
SELECT 1
FROM wsh_delivery_legs wdl,
wsh_new_deliveries wnd
WHERE wdl.delivery_id = wnd.delivery_id
AND wnd.status_code IN ('IT','CL')
AND ( wdl.pick_up_stop_id = p_stop_id or wdl.drop_off_stop_id = p_stop_id)
AND rownum = 1;
DELETE FROM wsh_delivery_legs
WHERE delivery_leg_id = l_delivery_leg_id;
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
delete corresponding freight cost record, ignore if
the record is not found
*/
IF WSH_UTIL_CORE.FTE_Is_Installed = 'Y'
--OTM R12, allow delete when OTM is installed
OR l_gc3_is_installed = 'Y'
--
THEN
DELETE FROM wsh_freight_costs
WHERE delivery_leg_id = l_delivery_leg_id;
'Rows deleted from wsh_freight_costs',SQL%ROWCOUNT);
p_action => 'UPDATE',
x_return_status => l_return_status);
update wsh_trip_stops
set shipments_type_flag = lock_trip_rec.pu_stop_shipments_type_flag, -- J-IB-NPARIKH
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where stop_id = leg_rec.pick_up_stop_id;
p_action => 'UPDATE',
x_return_status => l_return_status);
update wsh_trip_stops
set shipments_type_flag = lock_trip_rec.do_stop_shipments_type_flag, -- J-IB-NPARIKH
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where stop_id = leg_rec.drop_off_stop_id;
UPDATE WSH_TRIPS
SET shipments_type_flag = lock_trip_rec.trip_shipments_type_flag,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE trip_id = leg_rec.trip_id;
UPDATE WSH_TRIP_STOPS
SET shipments_type_flag = lock_trip_rec.trip_shipments_type_flag,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE trip_id = leg_rec.trip_id
AND shipments_type_flag <> lock_trip_rec.trip_shipments_type_flag;
WSH_DEBUG_SV.log(l_module_name,' Number of rows updated in WTS are',SQL%ROWCOUNT);
p_update_del_flag => 'Y',
p_update_leg_flag => 'N',
x_leg_complete => l_leg_complete,
x_return_status => l_return_status);
wsh_util_core.default_handler('WSH_DELIVERY_LEGS_PVT.DELETE_DELIVERY_LEG',l_module_name);
END Delete_Delivery_Leg;
SELECT *
FROM wsh_delivery_legs
WHERE rowid = p_rowid
FOR UPDATE OF delivery_leg_id NOWAIT;
WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_DELETED');
FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
AND ( (Recinfo.Last_Update_Date = p_delivery_leg_info.Last_Update_Date)
OR ( (Recinfo.Last_Update_Date IS NULL)
AND (p_delivery_leg_info.Last_Update_Date IS NULL)))
AND ( (Recinfo.Last_Updated_By = p_delivery_leg_info.Last_Updated_By)
OR ( (Recinfo.Last_Updated_By IS NULL)
AND (p_delivery_leg_info.Last_Updated_By IS NULL)))
AND ( (Recinfo.Last_Update_Login = p_delivery_leg_info.Last_Update_Login)
OR ( (Recinfo.Last_Update_Login IS NULL)
AND (p_delivery_leg_info.Last_Update_Login IS NULL)))
AND ( (Recinfo.Program_Application_Id = p_delivery_leg_info.Program_Application_Id)
OR ( (Recinfo.Program_Application_Id IS NULL)
AND (p_delivery_leg_info.Program_Application_Id IS NULL)))
AND ( (Recinfo.Program_Id = p_delivery_leg_info.Program_Id)
OR ( (Recinfo.Program_Id IS NULL)
AND (p_delivery_leg_info.Program_Id IS NULL)))
AND ( (Recinfo.Program_Update_Date = p_delivery_leg_info.Program_Update_Date)
OR ( (Recinfo.Program_Update_Date IS NULL)
AND (p_delivery_leg_info.Program_Update_Date IS NULL)))
AND ( (Recinfo.Load_Tender_Status = p_delivery_leg_info.Load_Tender_Status)
OR ( (Recinfo.Load_Tender_Status IS NULL)
AND (p_delivery_leg_info.Load_Tender_Status IS NULL)))
/*Changes for Shipping Data Model Bug#1918342*/
AND ( (Recinfo.shipper_title= p_delivery_leg_info.shipper_title)
OR ( (Recinfo.shipper_title IS NULL)
AND (p_delivery_leg_info.shipper_title IS NULL)))
AND ( (Recinfo.shipper_phone= p_delivery_leg_info.shipper_phone)
OR ( (Recinfo.shipper_phone IS NULL)
AND (p_delivery_leg_info.shipper_phone IS NULL)))
AND ( (Recinfo.pod_flag = p_delivery_leg_info.pod_flag)
OR ( (Recinfo.pod_flag IS NULL)
AND (p_delivery_leg_info.pod_flag IS NULL)))
AND ( (Recinfo.pod_by = p_delivery_leg_info.pod_by)
OR ( (Recinfo.pod_by IS NULL)
AND (p_delivery_leg_info.pod_by IS NULL)))
AND ( (Recinfo.pod_date = p_delivery_leg_info.pod_date)
OR ( (Recinfo.pod_date IS NULL)
AND (p_delivery_leg_info.pod_date IS NULL)))
AND ( (Recinfo.expected_pod_date = p_delivery_leg_info.expected_pod_date)
OR ( (Recinfo.expected_pod_date IS NULL)
AND (p_delivery_leg_info.expected_pod_date IS NULL)))
AND ( (Recinfo.booking_office = p_delivery_leg_info.booking_office)
OR ( (Recinfo.booking_office IS NULL)
AND (p_delivery_leg_info.booking_office IS NULL)))
AND ( (Recinfo.SHIPPER_EXPORT_REF = p_delivery_leg_info.SHIPPER_EXPORT_REF )
OR ( (Recinfo.SHIPPER_EXPORT_REF IS NULL)
AND (p_delivery_leg_info.SHIPPER_EXPORT_REF IS NULL)))
AND ( (Recinfo.CARRIER_EXPORT_REF = p_delivery_leg_info.CARRIER_EXPORT_REF )
OR ( (Recinfo.CARRIER_EXPORT_REF IS NULL)
AND (p_delivery_leg_info.CARRIER_EXPORT_REF IS NULL)))
AND ( (Recinfo.DOC_NOTIFY_PARTY = p_delivery_leg_info.DOC_NOTIFY_PARTY )
OR ( (Recinfo.DOC_NOTIFY_PARTY IS NULL)
AND (p_delivery_leg_info.DOC_NOTIFY_PARTY IS NULL)))
AND ( (Recinfo.AETC_NUMBER = p_delivery_leg_info.AETC_NUMBER )
OR ( (Recinfo.AETC_NUMBER IS NULL)
AND (p_delivery_leg_info.AETC_NUMBER IS NULL)))
AND ( (Recinfo.SHIPPER_SIGNED_BY = p_delivery_leg_info.SHIPPER_SIGNED_BY )
OR ( (Recinfo.SHIPPER_SIGNED_BY IS NULL)
AND (p_delivery_leg_info.SHIPPER_SIGNED_BY IS NULL)))
AND ( (Recinfo.SHIPPER_DATE = p_delivery_leg_info.SHIPPER_DATE)
OR ( (Recinfo.SHIPPER_DATE IS NULL)
AND (p_delivery_leg_info.SHIPPER_DATE IS NULL)))
AND ( (Recinfo.CARRIER_SIGNED_BY = p_delivery_leg_info.CARRIER_SIGNED_BY )
OR ( (Recinfo.CARRIER_SIGNED_BY IS NULL)
AND (p_delivery_leg_info.CARRIER_SIGNED_BY IS NULL)))
AND ( (Recinfo.CARRIER_DATE = p_delivery_leg_info.CARRIER_DATE )
OR ( (Recinfo.CARRIER_DATE IS NULL)
AND (p_delivery_leg_info.CARRIER_DATE IS NULL)))
AND ( (Recinfo.DOC_ISSUE_OFFICE = p_delivery_leg_info.DOC_ISSUE_OFFICE)
OR ( (Recinfo.DOC_ISSUE_OFFICE IS NULL)
AND (p_delivery_leg_info.DOC_ISSUE_OFFICE IS NULL)))
AND ( (Recinfo.DOC_ISSUED_BY = p_delivery_leg_info.DOC_ISSUED_BY)
OR ( (Recinfo.DOC_ISSUED_BY IS NULL)
AND (p_delivery_leg_info.DOC_ISSUED_BY IS NULL)))
AND ( (Recinfo.DOC_DATE_ISSUED = p_delivery_leg_info.DOC_DATE_ISSUED )
OR ( (Recinfo.DOC_DATE_ISSUED IS NULL)
AND (p_delivery_leg_info.DOC_DATE_ISSUED IS NULL)))
AND ( (Recinfo.SHIPPER_HM_BY = p_delivery_leg_info.SHIPPER_HM_BY )
OR ( (Recinfo.SHIPPER_HM_BY IS NULL)
AND (p_delivery_leg_info.SHIPPER_HM_BY IS NULL)))
AND ( (Recinfo.SHIPPER_HM_DATE = p_delivery_leg_info.SHIPPER_HM_DATE )
OR ( (Recinfo.SHIPPER_HM_DATE IS NULL)
AND (p_delivery_leg_info.SHIPPER_HM_DATE IS NULL)))
AND ( (Recinfo.CARRIER_HM_BY = p_delivery_leg_info.CARRIER_HM_BY )
OR ( (Recinfo.CARRIER_HM_BY IS NULL)
AND (p_delivery_leg_info.CARRIER_HM_BY IS NULL)))
AND ( (Recinfo.CARRIER_HM_DATE = p_delivery_leg_info.CARRIER_HM_DATE )
OR ( (Recinfo.CARRIER_HM_DATE IS NULL)
AND (p_delivery_leg_info.CARRIER_HM_DATE IS NULL)))
AND ( (Recinfo.BOOKING_NUMBER = p_delivery_leg_info.BOOKING_NUMBER )
OR ( (Recinfo.BOOKING_NUMBER IS NULL)
AND (p_delivery_leg_info.BOOKING_NUMBER IS NULL)))
AND ( (Recinfo.PORT_OF_LOADING = p_delivery_leg_info.PORT_OF_LOADING )
OR ( (Recinfo.PORT_OF_LOADING IS NULL)
AND (p_delivery_leg_info.PORT_OF_LOADING IS NULL)))
AND ( (Recinfo.PORT_OF_DISCHARGE = p_delivery_leg_info.PORT_OF_DISCHARGE )
OR ( (Recinfo.PORT_OF_DISCHARGE IS NULL)
AND (p_delivery_leg_info.PORT_OF_DISCHARGE IS NULL)))
AND ( (Recinfo.SERVICE_CONTRACT = p_delivery_leg_info.SERVICE_CONTRACT )
OR ( (Recinfo.SERVICE_CONTRACT IS NULL)
AND (p_delivery_leg_info.SERVICE_CONTRACT IS NULL)))
AND ( (Recinfo.BILL_FREIGHT_TO = p_delivery_leg_info.BILL_FREIGHT_TO )
OR ( (Recinfo.BILL_FREIGHT_TO IS NULL)
AND (p_delivery_leg_info.BILL_FREIGHT_TO IS NULL)))
/* H Integration: datamodel changes wrudge */
AND ( (Recinfo.FTE_TRIP_ID = p_delivery_leg_info.FTE_TRIP_ID )
OR ( (Recinfo.FTE_TRIP_ID IS NULL)
AND (p_delivery_leg_info.FTE_TRIP_ID IS NULL)))
AND ( (Recinfo.REPRICE_REQUIRED = p_delivery_leg_info.REPRICE_REQUIRED )
OR ( (Recinfo.REPRICE_REQUIRED IS NULL)
AND (p_delivery_leg_info.REPRICE_REQUIRED IS NULL)))
AND ( (Recinfo.ACTUAL_ARRIVAL_DATE = p_delivery_leg_info.ACTUAL_ARRIVAL_DATE )
OR ( (Recinfo.ACTUAL_ARRIVAL_DATE IS NULL)
AND (p_delivery_leg_info.ACTUAL_ARRIVAL_DATE IS NULL)))
AND ( (Recinfo.ACTUAL_DEPARTURE_DATE = p_delivery_leg_info.ACTUAL_DEPARTURE_DATE )
OR ( (Recinfo.ACTUAL_DEPARTURE_DATE IS NULL)
AND (p_delivery_leg_info.ACTUAL_DEPARTURE_DATE IS NULL)))
AND ( (Recinfo.ACTUAL_RECEIPT_DATE = p_delivery_leg_info.ACTUAL_RECEIPT_DATE )
OR ( (Recinfo.ACTUAL_RECEIPT_DATE IS NULL)
AND (p_delivery_leg_info.ACTUAL_RECEIPT_DATE IS NULL)))
AND ( (Recinfo.TRACKING_DRILLDOWN_FLAG = p_delivery_leg_info.TRACKING_DRILLDOWN_FLAG )
OR ( (Recinfo.TRACKING_DRILLDOWN_FLAG IS NULL)
AND (p_delivery_leg_info.TRACKING_DRILLDOWN_FLAG IS NULL)))
AND ( (Recinfo.STATUS_CODE = p_delivery_leg_info.STATUS_CODE )
OR ( (Recinfo.STATUS_CODE IS NULL)
AND (p_delivery_leg_info.STATUS_CODE IS NULL)))
AND ( (Recinfo.TRACKING_REMARKS = p_delivery_leg_info.TRACKING_REMARKS )
OR ( (Recinfo.TRACKING_REMARKS IS NULL)
AND (p_delivery_leg_info.TRACKING_REMARKS IS NULL)))
AND ( (Recinfo.CARRIER_EST_DEPARTURE_DATE = p_delivery_leg_info.CARRIER_EST_DEPARTURE_DATE )
OR ( (Recinfo.CARRIER_EST_DEPARTURE_DATE IS NULL)
AND (p_delivery_leg_info.CARRIER_EST_DEPARTURE_DATE IS NULL)))
AND ( (Recinfo.CARRIER_EST_ARRIVAL_DATE = p_delivery_leg_info.CARRIER_EST_ARRIVAL_DATE )
OR ( (Recinfo.CARRIER_EST_ARRIVAL_DATE IS NULL)
AND (p_delivery_leg_info.CARRIER_EST_ARRIVAL_DATE IS NULL)))
AND ( (Recinfo.LOADING_START_DATETIME = p_delivery_leg_info.LOADING_START_DATETIME )
OR ( (Recinfo.LOADING_START_DATETIME IS NULL)
AND (p_delivery_leg_info.LOADING_START_DATETIME IS NULL)))
AND ( (Recinfo.LOADING_END_DATETIME = p_delivery_leg_info.LOADING_END_DATETIME )
OR ( (Recinfo.LOADING_END_DATETIME IS NULL)
AND (p_delivery_leg_info.LOADING_END_DATETIME IS NULL)))
AND ( (Recinfo.UNLOADING_START_DATETIME = p_delivery_leg_info.UNLOADING_START_DATETIME )
OR ( (Recinfo.UNLOADING_START_DATETIME IS NULL)
AND (p_delivery_leg_info.UNLOADING_START_DATETIME IS NULL)))
AND ( (Recinfo.UNLOADING_END_DATETIME = p_delivery_leg_info.UNLOADING_END_DATETIME )
OR ( (Recinfo.UNLOADING_END_DATETIME IS NULL)
AND (p_delivery_leg_info.UNLOADING_END_DATETIME IS NULL)))
AND ( (Recinfo.DELIVERED_QUANTITY = p_delivery_leg_info.DELIVERED_QUANTITY )
OR ( (Recinfo.DELIVERED_QUANTITY IS NULL)
AND (p_delivery_leg_info.DELIVERED_QUANTITY IS NULL)))
AND ( (Recinfo.LOADED_QUANTITY = p_delivery_leg_info.LOADED_QUANTITY )
OR ( (Recinfo.LOADED_QUANTITY IS NULL)
AND (p_delivery_leg_info.LOADED_QUANTITY IS NULL)))
AND ( (Recinfo.RECEIVED_QUANTITY = p_delivery_leg_info.RECEIVED_QUANTITY )
OR ( (Recinfo.RECEIVED_QUANTITY IS NULL)
AND (p_delivery_leg_info.RECEIVED_QUANTITY IS NULL)))
AND ( (Recinfo.ORIGIN_STOP_ID = p_delivery_leg_info.ORIGIN_STOP_ID )
OR ( (Recinfo.ORIGIN_STOP_ID IS NULL)
AND (p_delivery_leg_info.ORIGIN_STOP_ID IS NULL)))
AND ( (Recinfo.DESTINATION_STOP_ID = p_delivery_leg_info.DESTINATION_STOP_ID )
OR ( (Recinfo.DESTINATION_STOP_ID IS NULL)
AND (p_delivery_leg_info.DESTINATION_STOP_ID IS NULL)))
AND ( (Recinfo.parent_delivery_leg_id = p_delivery_leg_info.parent_delivery_leg_id )
OR ( (Recinfo.parent_delivery_leg_id IS NULL)
AND (p_delivery_leg_info.parent_delivery_leg_id IS NULL)))
) THEN
--
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'Nothing has changed');
SELECT
DELIVERY_LEG_ID,
DELIVERY_ID,
SEQUENCE_NUMBER,
LOADING_ORDER_FLAG,
PICK_UP_STOP_ID,
DROP_OFF_STOP_ID,
GROSS_WEIGHT,
NET_WEIGHT,
WEIGHT_UOM_CODE,
VOLUME,
VOLUME_UOM_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
LOAD_TENDER_STATUS,
/* Changes in the shipping datamodel Bug#1918342*/
SHIPPER_TITLE,
SHIPPER_PHONE,
POD_FLAG,
POD_BY,
POD_DATE,
EXPECTED_POD_DATE,
BOOKING_OFFICE,
SHIPPER_EXPORT_REF,
CARRIER_EXPORT_REF,
DOC_NOTIFY_PARTY,
AETC_NUMBER,
SHIPPER_SIGNED_BY,
SHIPPER_DATE,
CARRIER_SIGNED_BY,
CARRIER_DATE,
DOC_ISSUE_OFFICE,
DOC_ISSUED_BY,
DOC_DATE_ISSUED,
SHIPPER_HM_BY,
SHIPPER_HM_DATE,
CARRIER_HM_BY,
CARRIER_HM_DATE,
BOOKING_NUMBER,
PORT_OF_LOADING,
PORT_OF_DISCHARGE,
SERVICE_CONTRACT,
BILL_FREIGHT_TO,
/* H Integration: datamodel changes wrudge */
FTE_TRIP_ID,
REPRICE_REQUIRED,
ACTUAL_ARRIVAL_DATE,
ACTUAL_DEPARTURE_DATE,
ACTUAL_RECEIPT_DATE,
TRACKING_DRILLDOWN_FLAG,
STATUS_CODE,
TRACKING_REMARKS,
CARRIER_EST_DEPARTURE_DATE,
CARRIER_EST_ARRIVAL_DATE,
LOADING_START_DATETIME,
LOADING_END_DATETIME,
UNLOADING_START_DATETIME,
UNLOADING_END_DATETIME,
DELIVERED_QUANTITY,
LOADED_QUANTITY,
RECEIVED_QUANTITY,
ORIGIN_STOP_ID,
DESTINATION_STOP_ID,
/* Harmonization Project I **heali */
ROWID,
/* K: MDC: sperera */
parent_delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_leg_id = p_delivery_leg_id;
SELECT status_code, planned_flag
FROM wsh_new_deliveries
WHERE delivery_id = x_delivery_id;
SELECT delivery_id, pick_up_stop_id, drop_off_stop_id
FROM wsh_delivery_legs
WHERE delivery_leg_id = x_leg_id;
SELECT status_code
FROM wsh_trip_stops
WHERE stop_id = x_stop_id;
x_disabled_list.delete;
does a SELECT using FOR UPDATE NOWAIT
Created: Harmonization Project. Patchset I
----------------------------------------------------------------------- */
PROCEDURE Lock_Dlvy_Leg_No_Compare(
p_dlvy_leg_id IN NUMBER, -- default null in spec
p_delivery_id IN NUMBER -- DEFAULT null in spec
)
IS
l_dummy_leg_id NUMBER;
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_leg_id = p_leg_id
FOR UPDATE NOWAIT;
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_id = p_dlvy_id
FOR UPDATE NOWAIT;