[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SHIP_METHOD_CODE
FROM wsh_trips
WHERE trip_id = p_trip_id;
select defer_interface,
enforce_ship_method
from wsh_global_parameters;
SELECT sequence_number
FROM wsh_document_instances
WHERE entity_name = 'WSH_NEW_DELIVERIES'
AND entity_id = p_delivery_id
AND document_type = 'PACK_TYPE';
SELECT WRS.Report_Set_Id
FROM Wsh_Report_Sets Wrs,
Wsh_Report_Set_Lines Wrsl
WHERE Wrsl.Report_Set_Id = Wrs.Report_Set_Id
AND Wrs.Name = 'Packing Slip Report';
select distinct trip_id from wsh_trip_stops
where stop_id in
( select distinct pick_up_stop_id
from wsh_delivery_legs
where delivery_id = t_delivery_id );
SELECT b.delivery_leg_id,
b.pick_up_location_id,
b.drop_off_location_id,
b.ship_method,
b.carrier_id,
b.trip_id,
b.trip_name,
l.parent_delivery_leg_id
FROM wsh_bols_db_v b, wsh_delivery_legs l
WHERE b.delivery_id = p_delivery_id
AND l.delivery_leg_id = b.delivery_leg_id;
select child.delivery_id
from wsh_delivery_legs parent,
wsh_delivery_legs child
where parent.delivery_id = p_delivery_id
and parent.delivery_leg_id = child.parent_delivery_leg_id;
SELECT initial_pickup_location_id
FROM wsh_new_deliveries
WHERE delivery_id = c_delivery_id;
SELECT distinct t.trip_id, t.ship_method_code, t.lane_id
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg,
wsh_trip_stops st,
wsh_trips t
WHERE dl.delivery_id = c_delivery_id AND
dl.delivery_id = dg.delivery_id AND
(dg.pick_up_stop_id = st.stop_id
OR
dg.DROP_OFF_STOP_ID = st.stop_id) AND
st.trip_id = t.trip_id;
SELECT wdl.delivery_id,
WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER interface_flag
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_new_deliveries wnd,
wsh_trips wt
WHERE wt.trip_id = p_trip_id
AND wts.trip_id = wt.trip_id
AND wt.status_code = 'OP'
AND wdl.pick_up_stop_id = wts.stop_id
AND wnd.delivery_id = wdl.delivery_id
AND wnd.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED;
AND p_action_prms.action_code = 'DELETE'
) THEN --BUG 4354579
WSH_NEW_DELIVERIES_PVT.Lock_Delivery(
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 => l_valid_ids_tab,
x_return_status => l_return_status,
p_action => p_action_prms.action_code);
l_delivery_id_tab.delete;
l_org_id_tab.delete;
l_ship_method_code_tab.delete;
x_delivery_out_rec.selection_issue_flag := 'Y';
l_delivery_id_tab_t.DELETE;
WSH_WV_UTILS.G_RESET_WV := 'Y'; -- set to Y to enable the update
p_update_flag => p_action_prms.override_flag,
x_return_status => l_return_status);
update wsh_new_deliveries
set ap_batch_id = NULL
where delivery_id = l_delivery_id_tab(i);
update wsh_new_deliveries
set ap_batch_id = NULL
where delivery_id = l_delivery_id_tab(i);
update wsh_new_deliveries
set batch_id = NULL
where delivery_id = l_delivery_id_tab(i);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_NEW_DELIVERIES_PVT.update_tms_interface_flag(
p_delivery_id_tab => l_new_delivery_id_tab,
p_tms_interface_flag_tab => l_new_interface_flag_tab,
x_return_status => l_return_status);
wsh_debug_sv.log(l_module_name,'Return Status After Calling update_tms_interface_flag',l_return_status);
DELETE FROM WSH_FREIGHT_COSTS
WHERE delivery_id = l_delivery_id_tab(i);
update wsh_trips
set SHIP_METHOD_CODE=null,
CARRIER_ID = null,
MODE_OF_TRANSPORT = null,
SERVICE_LEVEL = null
where trip_id= x_delivery_out_rec.result_id_tab(l_tripindex);
l_delivery_id_tab_temp.DELETE; -- added this line
update wsh_trips
set SHIP_METHOD_CODE=null,
CARRIER_ID = null,
MODE_OF_TRANSPORT = null,
SERVICE_LEVEL = null
where trip_id= l_tripID;
FND_MESSAGE.SET_NAME('WSH','WSH_UI_MULTI_SELECTION');
wsh_debug_sv.log (l_module_name,'WSH_UI_MULTI_SELECTION');
ELSIF p_action_prms.action_code = 'SELECT-CARRIER' THEN
--
IF wsh_util_core.fte_is_installed = 'Y' THEN
--
IF p_rec_attr_tab.COUNT > 1 and p_action_prms.caller in('WSH_FSTRX', 'WSH_PUB') THEN
FND_MESSAGE.SET_NAME('WSH','WSH_UI_MULTI_SELECTION');
wsh_debug_sv.logmsg(l_module_name,'WSH_UI_MULTI_SELECTION');
l_delivery_id_tab.delete;
WSH_NEW_DELIVERY_ACTIONS.process_carrier_selection(
p_delivery_id_tab => l_delivery_id_tab,
p_batch_id => NULL,
p_form_flag => l_form_flag,
p_caller => p_action_prms.caller,
x_return_message => l_msg_data,
x_return_status => l_return_status);
wsh_debug_sv.log(l_module_name,'Return Status After Calling Process_Carrier_Selection',l_return_status);
ELSIF p_action_prms.action_code = 'DELETE' THEN
--OTM R12, prevent delete of delivery that's not empty and ignore for planning
IF (l_gc3_is_installed = 'Y') THEN
i := l_delivery_id_tab.FIRST;
wsh_debug_sv.logmsg(l_module_name,'ERROR: delivery ' || l_delivery_id_tab(i) || ' cannot be deleted when it is not NS status');
FND_MESSAGE.SET_NAME('WSH','WSH_OTM_DEL_DELETE_ERROR');
WSH_DEBUG_SV.log(l_module_name,'0 deliveries to delete');
l_delivery_id_tab.DELETE;
WSH_UTIL_CORE.Delete(
p_type => 'DLVY',
p_rows => l_delivery_id_tab,
x_return_status => l_return_status);
wsh_debug_sv.log(l_module_name,'Return Status After Calling Delete',l_return_status);
FND_MESSAGE.SET_NAME('WSH','WSH_UI_MULTI_SELECTION');
wsh_debug_sv.log (l_module_name,'WSH_UI_MULTI_SELECTION');
WSH_PACKING_SLIPS_PVT.Insert_Row(
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_entity_name => 'WSH_NEW_DELIVERIES',
p_entity_id => l_delivery_id_tab(1),
p_application_id => 665 --FND_GLOBAL.RESP_APPL_ID
p_location_id => l_pickup_loc_id,
p_document_type => 'PACK_TYPE',
p_document_sub_type => 'SALES_ORDER',
p_reason_of_transport => p_action_prms.reason_of_transport,
p_description => p_action_prms.description,
x_document_number => x_delivery_out_rec.packing_slip_number);
wsh_debug_sv.log(l_module_name,'Return Status After Calling Insert_Row',l_return_status);
FND_MESSAGE.SET_NAME('WSH','WSH_UI_MULTI_SELECTION');
wsh_debug_sv.log (l_module_name,'WSH_UI_MULTI_SELECTION');
FND_MESSAGE.SET_NAME('WSH','WSH_UI_MULTI_SELECTION');
wsh_debug_sv.log (l_module_name,'WSH_UI_MULTI_SELECTION');
WSH_PACKING_SLIPS_PVT.Insert_Row(
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_entity_name => 'WSH_NEW_DELIVERIES',
p_entity_id => p_rec_attr_tab(i).delivery_id,
p_application_id => 665 /* FND_GLOBAL.RESP_APPL_ID */,
--Bug 6625788 For all Iteration Same l_pickup_loc_id is passed, so sending pickup_location_id for corresponding Delivery
-- p_location_id => l_pickup_loc_id,
p_location_id => p_rec_attr_tab(i).INITIAL_PICKUP_LOCATION_ID,
p_document_type => 'PACK_TYPE',
p_document_sub_type => 'SALES_ORDER',
--Bug 6625788 For all Iteration Same Reason of Transport and Description is passed, so sending corresponding info for Each Delivery
--p_reason_of_transport => p_action_prms.reason_of_transport,
--p_description => p_action_prms.description,
p_reason_of_transport => p_rec_attr_tab(i).REASON_OF_TRANSPORT,
p_description => p_rec_attr_tab(i).DESCRIPTION,
x_document_number => x_delivery_out_rec.packing_slip_number);
wsh_debug_sv.log(l_module_name,'Return Status After Calling Insert_Row',l_return_status);
FND_MESSAGE.SET_NAME('WSH','WSH_UI_MULTI_SELECTION');
wsh_debug_sv.log (l_module_name,'WSH_UI_MULTI_SELECTION');
FND_MESSAGE.SET_NAME('WSH','WSH_UI_MULTI_SELECTION');
wsh_debug_sv.logmsg(l_module_name,'WSH_UI_MULTI_SELECTION');
l_delivery_id_tab.delete;
IF p_action_prms.caller = 'WSH_DLMG' THEN -- R12 Select Carrier
l_in_param_rec.seq_tender_flag := 'Y';
l_rec_attr_tab.delete;
l_child_deliveries_tab.delete;
l_dleg_tab.delete;
l_dleg_action_out_rec.result_id_tab.delete;
select location_id
from hr_organization_units
where organization_id = p_org_id;
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).delivery_id IS NULL ) OR (p_in_rec_attr_tab(l_index).delivery_id <> fnd_api.g_miss_num)
OR (p_in_rec_attr_tab(l_index).name IS NULL ) OR (p_in_rec_attr_tab(l_index).name <> fnd_api.g_miss_char)
))) THEN
--
wsh_util_validate.validate_delivery_name(
p_delivery_id => x_rec_attr_tab(l_index).delivery_id,
p_delivery_name => x_rec_attr_tab(l_index).name,
x_return_status => l_return_status);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).organization_id IS NULL ) OR (p_in_rec_attr_tab(l_index).organization_id <> fnd_api.g_miss_num)
OR (p_in_rec_attr_tab(l_index).organization_code IS NULL ) OR (p_in_rec_attr_tab(l_index).organization_code <> fnd_api.g_miss_char)
))) THEN
--{
IF ( p_action_code = 'UPDATE'
AND x_rec_attr_tab(l_index).organization_id IS NULL
AND x_rec_attr_tab(l_index).organization_code IS NULL) THEN
--{ NULL case
x_return_status := wsh_util_core.g_ret_sts_error;
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).loading_order_flag IS NULL ) OR (p_in_rec_attr_tab(l_index).loading_order_flag <> fnd_api.g_miss_char)
OR (p_in_rec_attr_tab(l_index).loading_order_desc IS NULL ) OR (p_in_rec_attr_tab(l_index).loading_order_desc <> fnd_api.g_miss_char)
))) THEN
--
wsh_util_validate.validate_loading_order(
p_loading_order_flag => x_rec_attr_tab(l_index).loading_order_flag,
p_loading_order_desc => x_rec_attr_tab(l_index).loading_order_desc,
x_return_status => l_return_status);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).initial_pickup_location_id IS NULL ) OR (p_in_rec_attr_tab(l_index).initial_pickup_location_id <> fnd_api.g_miss_num)
OR (p_in_rec_attr_tab(l_index).initial_pickup_location_code IS NULL ) OR (p_in_rec_attr_tab(l_index).initial_pickup_location_code <> fnd_api.g_miss_char)
))) THEN
--{
IF (p_action_code = 'UPDATE'
AND x_rec_attr_tab(l_index).initial_pickup_location_id IS NULL
AND x_rec_attr_tab(l_index).initial_pickup_location_code IS NULL) THEN
--{ NULL case
x_return_status := wsh_util_core.g_ret_sts_error;
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).ultimate_dropoff_location_id IS NULL ) OR (p_in_rec_attr_tab(l_index).ultimate_dropoff_location_id <> fnd_api.g_miss_num)
OR (p_in_rec_attr_tab(l_index).ultimate_dropoff_location_code IS NULL ) OR (p_in_rec_attr_tab(l_index).ultimate_dropoff_location_code <> fnd_api.g_miss_char)
))) THEN
--{
IF ( p_action_code = 'UPDATE'
AND x_rec_attr_tab(l_index).ultimate_dropoff_location_id IS NULL
AND x_rec_attr_tab(l_index).ultimate_dropoff_location_code IS NULL ) THEN
--{ NULL case
x_return_status := wsh_util_core.g_ret_sts_error;
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).intmed_ship_to_location_id IS NULL ) OR (p_in_rec_attr_tab(l_index).intmed_ship_to_location_id <> fnd_api.g_miss_num)
OR (p_in_rec_attr_tab(l_index).intmed_ship_to_location_code IS NULL ) OR (p_in_rec_attr_tab(l_index).intmed_ship_to_location_code <> fnd_api.g_miss_char)
))) THEN
IF l_debug_on THEN
wsh_debug_sv.log(l_module_name,'loc id ',x_rec_attr_tab(l_index).intmed_ship_to_location_id);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).pooled_ship_to_location_id IS NULL ) OR (p_in_rec_attr_tab(l_index).pooled_ship_to_location_id <> fnd_api.g_miss_num)
OR (p_in_rec_attr_tab(l_index).pooled_ship_to_location_code IS NULL ) OR (p_in_rec_attr_tab(l_index).pooled_ship_to_location_code <> fnd_api.g_miss_char)
))) THEN
--
wsh_util_validate.validate_location(
p_location_id => x_rec_attr_tab(l_index).pooled_ship_to_location_id,
p_location_code => x_rec_attr_tab(l_index).pooled_ship_to_location_code,
x_return_status => l_return_status,
p_isWshLocation => l_isWshLocation);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).fob_location_id IS NULL ) OR (p_in_rec_attr_tab(l_index).fob_location_id <> fnd_api.g_miss_num)
OR (p_in_rec_attr_tab(l_index).fob_location_code IS NULL ) OR (p_in_rec_attr_tab(l_index).fob_location_code <> fnd_api.g_miss_char)
))) THEN
--
wsh_util_validate.validate_location(
p_location_id => x_rec_attr_tab(l_index).fob_location_id,
p_location_code => x_rec_attr_tab(l_index).fob_location_code,
x_return_status => l_return_status,
p_isWshLocation => l_isWshLocation);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).customer_id IS NULL ) OR (p_in_rec_attr_tab(l_index).customer_id <> fnd_api.g_miss_num)
OR (p_in_rec_attr_tab(l_index).customer_number IS NULL ) OR (p_in_rec_attr_tab(l_index).customer_number <> fnd_api.g_miss_char)
))) THEN
--
wsh_util_validate.validate_customer(
p_customer_id => x_rec_attr_tab(l_index).customer_id,
p_customer_number => x_rec_attr_tab(l_index).customer_number,
x_return_status => l_return_status);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).freight_terms_code IS NULL ) OR (p_in_rec_attr_tab(l_index).freight_terms_code <> fnd_api.g_miss_char)
OR (p_in_rec_attr_tab(l_index).freight_terms_name IS NULL ) OR (p_in_rec_attr_tab(l_index).freight_terms_name <> fnd_api.g_miss_char)
))) THEN
--
wsh_util_validate.validate_freight_terms(
p_freight_terms_code => x_rec_attr_tab(l_index).freight_terms_code,
p_freight_terms_name => x_rec_attr_tab(l_index).freight_terms_name,
x_return_status => l_return_status);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).fob_code IS NULL ) OR (p_in_rec_attr_tab(l_index).fob_code <> fnd_api.g_miss_char)
OR (p_in_rec_attr_tab(l_index).fob_name IS NULL ) OR (p_in_rec_attr_tab(l_index).fob_name <> fnd_api.g_miss_char)
))) THEN
--
wsh_util_validate.validate_fob(
p_fob_code => x_rec_attr_tab(l_index).fob_code,
p_fob_name => x_rec_attr_tab(l_index).fob_name,
x_return_status => l_return_status);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).weight_uom_code IS NULL ) OR (p_in_rec_attr_tab(l_index).weight_uom_code <> fnd_api.g_miss_char)
))) THEN
--
IF x_rec_attr_tab(l_index).weight_uom_code is not null or x_rec_attr_tab(l_index).weight_uom_desc is not null THEN
wsh_util_validate.validate_uom(
p_type => 'WEIGHT',
p_organization_id => x_rec_attr_tab(l_index).organization_id,
p_uom_code => x_rec_attr_tab(l_index).weight_uom_code,
p_uom_desc => x_rec_attr_tab(l_index).weight_uom_desc,
x_return_status => l_return_status);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).volume_uom_code IS NULL ) OR (p_in_rec_attr_tab(l_index).volume_uom_code <> fnd_api.g_miss_char)
))) THEN
--
IF x_rec_attr_tab(l_index).volume_uom_code is not null or x_rec_attr_tab(l_index).volume_uom_desc is not null THEN
wsh_util_validate.validate_uom(
p_type => 'VOLUME',
p_organization_id => x_rec_attr_tab(l_index).organization_id,
p_uom_code => x_rec_attr_tab(l_index).volume_uom_code,
p_uom_desc => x_rec_attr_tab(l_index).volume_uom_desc,
x_return_status => l_return_status);
AND ((p_action_code = 'CREATE' OR p_action_code = 'UPDATE') AND
((p_in_rec_attr_tab(l_index).currency_code <> fnd_api.g_miss_char)
OR (p_in_rec_attr_tab(l_index).currency_name <> fnd_api.g_miss_char)
)) THEN
--
--OTM R12, added parameter l_adjusted_amount
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_CURRENCY',WSH_DEBUG_SV.C_PROC_LEVEL);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).initial_pickup_date IS NULL ) OR (p_in_rec_attr_tab(l_index).initial_pickup_date <> fnd_api.g_miss_date)
OR (p_in_rec_attr_tab(l_index).ultimate_dropoff_date IS NULL ) OR (p_in_rec_attr_tab(l_index).ultimate_dropoff_date <> fnd_api.g_miss_date)
))) THEN
--
wsh_util_validate.validate_from_to_dates(
p_from_date => x_rec_attr_tab(l_index).initial_pickup_date,
p_to_date => x_rec_attr_tab(l_index).ultimate_dropoff_date,
x_return_status => l_return_status);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).ROUTED_EXPORT_TXN IS NULL ) OR (p_in_rec_attr_tab(l_index).ROUTED_EXPORT_TXN <> fnd_api.g_miss_char)
))) THEN
--
WSH_DELIVERY_VALIDATIONS.Validate_Routed_Export_Txn(
x_rtd_expt_txn_code => x_rec_attr_tab(l_index).ROUTED_EXPORT_TXN,
p_rtd_expt_txn_meaning => l_dummy_meaning,
x_return_status => l_return_status);
(p_action_code = 'UPDATE' AND
((p_in_rec_attr_tab(l_index).ship_method_name IS NULL ) OR (p_in_rec_attr_tab(l_index).ship_method_name <> fnd_api.g_miss_char)
OR (p_in_rec_attr_tab(l_index).ship_method_code IS NULL ) OR (p_in_rec_attr_tab(l_index).ship_method_code <> fnd_api.g_miss_char)
OR (p_in_rec_attr_tab(l_index).carrier_code IS NULL ) OR (p_in_rec_attr_tab(l_index).carrier_code <> fnd_api.g_miss_char)
OR (p_in_rec_attr_tab(l_index).carrier_id IS NULL ) OR (p_in_rec_attr_tab(l_index).carrier_id <> fnd_api.g_miss_num)
OR (p_in_rec_attr_tab(l_index).service_level IS NULL ) OR (p_in_rec_attr_tab(l_index).service_level <> fnd_api.g_miss_char)
OR (p_in_rec_attr_tab(l_index).mode_of_transport IS NULL ) OR (p_in_rec_attr_tab(l_index).mode_of_transport <> fnd_api.g_miss_char)
))) THEN
--
wsh_util_validate.validate_freight_carrier(
p_ship_method_name => x_rec_attr_tab(l_index).ship_method_name,
x_ship_method_code => x_rec_attr_tab(l_index).ship_method_code,
p_carrier_name => x_rec_attr_tab(l_index).carrier_code,
x_carrier_id => x_rec_attr_tab(l_index).carrier_id,
x_service_level => x_rec_attr_tab(l_index).service_level,
x_mode_of_transport => x_rec_attr_tab(l_index).mode_of_transport,
p_entity_id => x_rec_attr_tab(l_index).delivery_id,
p_entity_type => 'DLVY',
p_organization_id => x_rec_attr_tab(l_index).organization_id,
x_return_status => l_return_status,
p_caller => p_caller);
(p_action_code = 'UPDATE' AND
( (p_in_rec_attr_tab(l_index).volume_uom_code IS NULL ) OR (p_in_rec_attr_tab(l_index).volume_uom_code <> fnd_api.g_miss_char)
OR (p_in_rec_attr_tab(l_index).weight_uom_code IS NULL ) OR (p_in_rec_attr_tab(l_index).weight_uom_code <> fnd_api.g_miss_char)
))) THEN
--
wsh_delivery_validations.derive_delivery_uom(
p_delivery_id => x_rec_attr_tab(l_index).delivery_id,
p_organization_id => x_rec_attr_tab(l_index).organization_id,
x_volume_uom_code => x_rec_attr_tab(l_index).volume_uom_code,
x_weight_uom_code => x_rec_attr_tab(l_index).weight_uom_code,
x_wt_nullify_flag => l_wt_nullify_flag,
x_vol_nullify_flag => l_vol_nullify_flag,
x_return_status => l_return_status);
PROCEDURE Create_Update_Delivery
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_in_rec IN del_In_Rec_Type,
p_rec_attr_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
x_del_out_rec_tab OUT NOCOPY Del_Out_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_DELIVERY';
l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Delivery';
SELECT organization_id, name, ship_method_code, carrier_id
FROM wsh_new_deliveries
WHERE delivery_id = p_delid
and ignore_for_planning<>'Y';
SAVEPOINT CREATE_UPDATE_DELIVERY_GRP;
SAVEPOINT CREATE_UPDATE_DLVY_GRP_LOOP;
WSH_DELIVERY_AUTOCREATE.Create_Update_Hash(
p_delivery_rec => l_rec_attr_tab(l_index),
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.PROCESS_CARRIER_SELECTION',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_NEW_DELIVERY_ACTIONS.PROCESS_CARRIER_SELECTION(p_delivery_id_tab => l_delivery_rows,
p_batch_id => null,
p_form_flag => 'N',
x_return_message => l_error_text,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR OCCURRED WHILST PROCESSING CARRIER SELECTION' );
WSH_NEW_DELIVERY_ACTIONS.update_ship_from_location
(
p_delivery_id => l_rec_attr_tab(l_index).delivery_id,
p_location_id => l_rec_attr_tab(l_index).initial_pickup_location_id,
x_return_status => l_return_status
);
wsh_debug_sv.log(l_module_name,'Return Status After Calling update_ship_from_location',l_return_status);
WSH_DELIVERY_AUTOCREATE.Create_Update_Hash(
p_delivery_rec => l_rec_attr_tab(l_index),
x_return_status => l_return_status);
WSH_NEW_DELIVERIES_PVT.Update_Delivery(
p_rowid => l_rec_attr_tab(l_index).rowid,
p_delivery_info => l_rec_attr_tab(l_index),
x_return_status => l_return_status);
wsh_debug_sv.log(l_module_name,'Return Status After Calling Update Delivery',l_return_status);
l_tmp_del_ids.delete;
ROLLBACK TO CREATE_UPDATE_DLVY_GRP_LOOP;
ROLLBACK TO CREATE_UPDATE_DLVY_GRP_LOOP;
ROLLBACK TO CREATE_UPDATE_DLVY_GRP_LOOP;
ROLLBACK TO CREATE_UPDATE_DELIVERY_GRP;
ROLLBACK TO CREATE_UPDATE_DELIVERY_GRP;
ROLLBACK TO CREATE_UPDATE_DELIVERY_GRP;
ROLLBACK TO CREATE_UPDATE_DELIVERY_GRP;
ROLLBACK TO CREATE_UPDATE_DELIVERY_GRP;
ROLLBACK TO CREATE_UPDATE_DELIVERY_GRP;
wsh_util_core.default_handler('WSH_DELIVERIES_GRP.CREATE_UPDATE_DELIVERY');
END Create_Update_Delivery;
x_pvt_rec.LAST_UPDATE_DATE := p_pub_rec.LAST_UPDATE_DATE;
x_pvt_rec.LAST_UPDATED_BY := p_pub_rec.LAST_UPDATED_BY;
x_pvt_rec.LAST_UPDATE_LOGIN := p_pub_rec.LAST_UPDATE_LOGIN;
x_pvt_rec.PROGRAM_UPDATE_DATE := p_pub_rec.PROGRAM_UPDATE_DATE;
PROCEDURE Create_Update_Delivery(
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_delivery_info IN OUT NOCOPY Delivery_Pub_Rec_Type,
p_delivery_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
x_delivery_id OUT NOCOPY NUMBER,
x_name OUT NOCOPY VARCHAR2)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Delivery';
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_DELIVERY';
IF (p_action_code = 'UPDATE') THEN
IF (p_delivery_name IS NOT NULL) OR (p_delivery_name <> FND_API.G_MISS_CHAR) THEN
p_delivery_info.name := p_delivery_name;
wsh_interface_grp.create_update_delivery(
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => l_commit,
p_in_rec => l_delivery_in_rec,
p_rec_attr_tab => l_rec_attr_tab,
x_del_out_rec_tab => l_del_out_rec_tab,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
wsh_debug_sv.log(l_module_name,'Return Status After Calling create_update_delivery',l_return_status);
END Create_Update_Delivery;
SELECT dg.pick_up_stop_id,
pu_stop.status_code pu_status,
dg.drop_off_stop_id,
do_stop.status_code do_status
FROM wsh_delivery_legs dg,
wsh_trip_stops pu_stop,
wsh_trip_stops do_stop
WHERE dg.delivery_id = p_delivery_id
AND pu_stop.stop_id = dg.pick_up_stop_id
AND do_stop.stop_id = dg.drop_off_stop_id;
SELECT stop_sequence_number, trip_id
FROM wsh_trip_stops
WHERE stop_id = p_stop_id;
SELECT min(stop_sequence_number), max(stop_sequence_number)
FROM wsh_trip_stops
WHERE trip_id = p_trip_id;
l_stop_rows.delete;