[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT * FROM wsh_new_deliveries
WHERE delivery_id = c_dlv;
SELECT count(delivery_detail_id) FROM wsh_delivery_assignments
WHERE (delivery_id = c_delivery_id OR parent_delivery_id= c_delivery_id);
SELECT count(delivery_detail_id) FROM wsh_delivery_assignments
WHERE delivery_id in
(SELECT wdl.delivery_id
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts1,
wsh_trip_stops wts2
WHERE wts1.trip_id = c_trip_id
AND wts2.trip_id = c_trip_id
AND wts1.stop_id = wdl.pick_up_stop_id
AND wts2.stop_id = wdl.drop_off_stop_id
);
SELECT count(wd.delivery_id)
FROM wsh_new_deliveries wd, wsh_global_parameters wgp
WHERE (
((wd.shipment_direction in ('I'))
and (wgp.rate_ib_dels_fgt_term is not null)
and (wd.freight_terms_code is not null)
and (wgp.rate_ib_dels_fgt_term <> wd.freight_terms_code)
)
OR
((wd.shipment_direction in ('D'))
and (wgp.rate_ds_dels_fgt_term_id is not null)
and (wd.freight_terms_code is not null)
and (wgp.rate_ds_dels_fgt_term_id <> wd.freight_terms_code)
)
OR
((nvl(wd.shipment_direction,'O') in ('O','IO'))
and (wgp.skip_rate_ob_dels_fgt_term is not null)
and (wd.freight_terms_code is not null)
and (wgp.skip_rate_ob_dels_fgt_term = wd.freight_terms_code)
)
)
AND wd.delivery_id = c_delivery_id;
SELECT count(wd.delivery_id)
FROM wsh_new_deliveries wd, wsh_global_parameters wgp
WHERE (
((wd.shipment_direction in ('I'))
and (wgp.rate_ib_dels_fgt_term is not null)
and (wd.freight_terms_code is not null)
and (wgp.rate_ib_dels_fgt_term <> wd.freight_terms_code)
)
OR
((wd.shipment_direction in ('D'))
and (wgp.rate_ds_dels_fgt_term_id is not null)
and (wd.freight_terms_code is not null)
and (wgp.rate_ds_dels_fgt_term_id <> wd.freight_terms_code)
)
OR
((nvl(wd.shipment_direction,'O') in ('O','IO'))
and (wgp.skip_rate_ob_dels_fgt_term is not null)
and (wd.freight_terms_code is not null)
and (wgp.skip_rate_ob_dels_fgt_term = wd.freight_terms_code)
)
)
AND wd.delivery_id in
(SELECT wdl.delivery_id
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts1,
wsh_trip_stops wts2
WHERE wts1.trip_id = c_trip_id
AND wts2.trip_id = c_trip_id
AND wts1.stop_id = wdl.pick_up_stop_id
AND wts2.stop_id = wdl.drop_off_stop_id
);
SELECT count(a.delivery_id)
FROM wsh_new_deliveries a,
mtl_parameters b,
wsh_carriers c
WHERE a.organization_id = b.organization_id
AND a.carrier_id = c.carrier_id
AND c.manifesting_enabled_flag = 'Y'
AND b.carrier_manifesting_flag = 'Y'
AND a.delivery_id = c_delivery_id;
SELECT count(a.delivery_id)
FROM wsh_new_deliveries a,
mtl_parameters b,
wsh_carriers c
WHERE a.organization_id = b.organization_id
AND a.carrier_id = c.carrier_id
AND c.manifesting_enabled_flag = 'Y'
AND b.carrier_manifesting_flag = 'Y'
AND a.delivery_id in
(SELECT wdl.delivery_id
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts1,
wsh_trip_stops wts2
WHERE wts1.trip_id = c_trip_id
AND wts2.trip_id = c_trip_id
AND wts1.stop_id = wdl.pick_up_stop_id
AND wts2.stop_id = wdl.drop_off_stop_id
);
SELECT manifesting_enabled_flag
FROM wsh_carriers
WHERE carrier_id = c_carrier_id;
SELECT b.carrier_manifesting_flag
FROM wsh_new_deliveries a,
mtl_parameters b
WHERE a.organization_id = b.organization_id
AND a.delivery_id = c_delivery_id;
SELECT ship_method_code,carrier_id, service_level, mode_of_transport, ship_method_meaning
FROM wsh_carrier_services
WHERE ship_method_code = c_shp_mthd_cd;
SELECT ship_method_code, ship_method_meaning
FROM wsh_carrier_services
WHERE carrier_id = c_carrier_id
AND service_level = c_service_level
AND mode_of_transport = c_mode_of_trans;
SELECT a.ship_method_code, a.ship_method_meaning
FROM wsh_carrier_services a, wsh_org_carrier_services b
WHERE a.carrier_service_id = b.carrier_service_id
AND b.organization_id = c_org_id
AND b.enabled_flag = 'Y'
AND a.enabled_flag = 'Y'
AND a.mode_of_transport = c_mode_of_trans
AND a.service_level = c_service_level
AND a.carrier_id = c_carrier_id;
SELECT generic_flag
FROM wsh_carriers
WHERE carrier_id = c_carrier_id;
SELECT hz.party_name carrier_name
FROM hz_parties hz, wsh_carriers wc
WHERE hz.party_id = wc.carrier_id
AND nvl(wc.generic_flag,'N') = 'N'
AND wc.carrier_id = c_carrier_id;
SELECT meaning mode_of_transport
FROM fnd_lookup_values_vl
WHERE lookup_type = 'WSH_MODE_OF_TRANSPORT'
AND nvl(start_date_active, sysdate) <= sysdate
AND nvl(end_date_active, sysdate) >= sysdate
AND enabled_flag='Y'
AND lookup_code = c_mode_of_transport_code;
SELECT meaning service_type
FROM fnd_lookup_values_vl
WHERE lookup_type = 'WSH_SERVICE_LEVELS'
AND nvl(start_date_active, sysdate) <= sysdate
AND nvl(end_date_active, sysdate) >= sysdate
AND enabled_flag='Y'
AND lookup_code = c_service_type_code;
SELECT delivery_leg_id,
delivery_id,
sequence_number,
pick_up_stop_id,
drop_off_stop_id,
reprice_required,
status_code,
parent_delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_id = c_dlv_id;
SELECT wt.trip_id,
wt.name,
wt.planned_flag,
wt.status_code,
wt.carrier_id,
wt.ship_method_code,
wt.service_level,
wt.mode_of_transport,
wt.consolidation_allowed,
wt.lane_id,
wt.schedule_id,
wt.load_tender_status
FROM wsh_trips wt,
wsh_delivery_legs wdl,
wsh_trip_stops wts1,
wsh_trip_stops wts2
WHERE wts1.stop_id = wdl.pick_up_stop_id
AND wts2.stop_id = wdl.drop_off_stop_id
AND wts1.trip_id = wt.trip_id
AND wts2.trip_id = wt.trip_id
AND wdl.delivery_leg_id = c_delivery_leg_id;
SELECT wt.trip_id,
wt.name,
wt.planned_flag,
wt.status_code,
wt.carrier_id,
wt.ship_method_code,
wt.service_level,
wt.mode_of_transport,
wt.consolidation_allowed,
wt.lane_id,
wt.schedule_id,
wt.load_tender_status
FROM wsh_trips wt
WHERE wt.trip_id = c_trip_id;
SELECT stop_location_id, planned_departure_date
FROM wsh_trip_stops
WHERE trip_id = c_trip_id
AND stop_sequence_number =
(SELECT min(stop_sequence_number)
FROM wsh_trip_stops
WHERE trip_id = c_trip_id);
SELECT stop_location_id, planned_arrival_date
FROM wsh_trip_stops
WHERE trip_id = c_trip_id
AND stop_sequence_number =
(SELECT max(stop_sequence_number)
FROM wsh_trip_stops
WHERE trip_id = c_trip_id);
SELECT count(wdl.delivery_leg_id)
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts1,
wsh_trip_stops wts2
WHERE wts1.trip_id = c_trip_id
AND wts2.trip_id = c_trip_id
AND wts1.stop_id = wdl.pick_up_stop_id
AND wts2.stop_id = wdl.drop_off_stop_id;
Select count(*)
from wsh_delivery_legs wdl, wsh_trip_stops wts1, wsh_trip_stops wts2,wsh_trips wt
where wdl.pick_up_stop_id = wts1.stop_id
and wdl.drop_off_stop_id = wts2.stop_id
and wdl.reprice_required = 'Y'
and wts1.trip_id = wt.trip_id
and wts2.trip_id = wt.trip_id
and wt.trip_id = c_trip_id;
SELECT wdl.delivery_leg_id,
wdl.delivery_id,
wdl.sequence_number,
wdl.pick_up_stop_id,
wdl.drop_off_stop_id,
wdl.reprice_required,
wdl.status_code,
wdl.parent_delivery_leg_id
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts1,
wsh_trip_stops wts2
WHERE wts1.trip_id = c_trip_id
AND wts2.trip_id = c_trip_id
AND wts1.stop_id = wdl.pick_up_stop_id
AND wts2.stop_id = wdl.drop_off_stop_id;
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_id = c_delivery_id;
x_dlv_rec.LAST_UPDATE_DATE := p_dlv_rec.LAST_UPDATE_DATE;
x_dlv_rec.LAST_UPDATED_BY := p_dlv_rec.LAST_UPDATED_BY;
x_dlv_rec.LAST_UPDATE_LOGIN := p_dlv_rec.LAST_UPDATE_LOGIN;
x_dlv_rec.PROGRAM_UPDATE_DATE := p_dlv_rec.PROGRAM_UPDATE_DATE;
PROCEDURE update_single_trip (p_trip_id IN NUMBER,
p_lane_id IN NUMBER,
p_carrier_id IN NUMBER,
p_ship_method_code IN VARCHAR2,
p_ship_method_name IN VARCHAR2,
p_service_level IN VARCHAR2,
p_mode_of_transport IN VARCHAR2,
-- p_consolidation_allowed IN VARCHAR2,
p_vehicle_type_id IN NUMBER,-- If p_vehicle_type id is not passed in
p_vehicle_item_id IN NUMBER,--- then p_vehicle_item/org_id is considered
p_vehicle_org_id IN NUMBER,
p_rank_id IN NUMBER DEFAULT NULL,
p_append_flag VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_version_number NUMBER := 1;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'UPDATE_SINGLE_TRIP';
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SINGLE_TRIP';
select v.inventory_item_id,
v.ORGANIZATION_ID
from fte_vehicle_types v
where v.vehicle_type_id = c_vehicle_type_id;
select * from wsh_trips
where trip_id = c_trip_id;
l_trip_info.LAST_UPDATE_DATE := c_trip_rec.LAST_UPDATE_DATE ;
l_trip_info.LAST_UPDATED_BY := c_trip_rec.LAST_UPDATED_BY ;
l_trip_info.LAST_UPDATE_LOGIN := c_trip_rec.LAST_UPDATE_LOGIN ;
l_trip_info.PROGRAM_UPDATE_DATE := c_trip_rec.PROGRAM_UPDATE_DATE ;
l_trip_in_rec.action_code:='UPDATE';
FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'calling WSH_INTERFACE_GRP.Create_Update_Trip...');
WSH_INTERFACE_GRP.Create_Update_Trip
(
p_api_version_number =>l_api_version_number,
p_init_msg_list =>FND_API.G_FALSE,
p_commit =>FND_API.G_FALSE,
x_return_status =>l_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data,
p_trip_info_tab =>l_trip_info_tab,
p_in_rec =>l_trip_in_rec,
x_out_tab =>l_out_tab
);
p_api_name => 'WSH_INTERFACE_GRP.CREATE_UPDATE_TRIP',
p_api_return_status => l_return_status,
p_message_name => 'FTE_PRC_UPD_TRP_FAIL',
p_trip_id => p_trip_id,
p_delivery_id => null,
p_delivery_leg_id => null,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => l_return_status_1
);
END update_single_trip;
PROCEDURE update_trips ( p_delivery_id IN NUMBER,
p_matched_lanes IN lane_match_tab,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
i NUMBER;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TRIPS';
SAVEPOINT UPDATE_TRIPS;
update_single_trip (p_trip_id => p_matched_lanes(i).trip_id,
p_lane_id => p_matched_lanes(i).lane_id,
p_carrier_id => p_matched_lanes(i).carrier_id,
p_ship_method_code => p_matched_lanes(i).ship_method_code,
p_ship_method_name => p_matched_lanes(i).ship_method_name,
p_service_level => p_matched_lanes(i).service_level,
p_mode_of_transport => p_matched_lanes(i).mode_of_transport,
p_vehicle_type_id =>NULL,
p_vehicle_item_id =>NULL,
p_vehicle_org_id =>NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
ROLLBACK TO UPDATE_TRIPS;
ROLLBACK TO UPDATE_TRIPS;
ROLLBACK TO UPDATE_TRIPS;
WSH_UTIL_CORE.DEFAULT_HANDLER(G_PKG_NAME||'.update_trips');
END update_trips;
SELECT freight_cost_id
FROM wsh_freight_costs
WHERE delivery_leg_id = c_dleg_id
AND line_type_code = 'SUMMARY'
AND delivery_detail_id IS NULL ;
g_dlv_tab.DELETE;
g_dlv_leg_tab.DELETE;
g_trip_info_tab.DELETE;
g_dleg_trip_tab.DELETE;
update_single_trip (p_trip_id => g_trip_info_tab(i).trip_id,
p_lane_id => null,
p_carrier_id => null,
p_ship_method_code => null,
p_ship_method_name => null,
p_service_level => null,
p_mode_of_transport => null,
p_vehicle_type_id => null,
p_vehicle_item_id =>NULL,
p_vehicle_org_id =>NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
l_del_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_GRP.Create_Update_Delivery
( p_api_version_number =>1.0,
p_init_msg_list =>FND_API.G_FALSE,
-- p_commit =>'F',
p_commit =>FND_API.G_FALSE,
p_in_rec =>l_del_in_rec,
p_rec_attr_tab => g_dlv_tab,
x_del_out_rec_tab => l_del_out_tab,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT count(freight_cost_id) INTO l_fc_count
FROM WSH_FREIGHT_COSTS
WHERE delivery_leg_id = g_dlv_leg_tab(i).delivery_leg_id
AND (
( charge_source_code = 'PRICING_ENGINE'
AND line_type_code <> 'SUMMARY'
)
OR
(line_type_code = 'SUMMARY'
AND delivery_detail_id IS NOT NULL
)
);
WSH_DEBUG_SV.log(l_module_name,'About to update wfc for delivery_leg_id =>'
||g_dlv_leg_tab(i).delivery_leg_id
,WSH_DEBUG_SV.C_PROC_LEVEL);
DELETE FROM WSH_FREIGHT_COSTS
WHERE delivery_leg_id = g_dlv_leg_tab(i).delivery_leg_id
AND (
( charge_source_code = 'PRICING_ENGINE'
AND line_type_code <> 'SUMMARY'
)
OR
(line_type_code = 'SUMMARY'
AND delivery_detail_id IS NOT NULL
)
);
WSH_DEBUG_SV.logmsg(l_module_name,'wfc records deleted : '||l_fc_count
,WSH_DEBUG_SV.C_PROC_LEVEL);
UPDATE wsh_freight_costs
SET freight_cost_type_id = -1,
charge_source_code = NULL,
unit_amount = NULL,
total_amount = NULL,
currency_code = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE freight_cost_id = l_freight_cost_id;
WSH_DEBUG_SV.logmsg(l_module_name,'wfc updated : freight_cost_id =>'||l_freight_cost_id
,WSH_DEBUG_SV.C_PROC_LEVEL);
Select wdd.delivery_detail_id,
wda.delivery_id,
wdl.delivery_leg_id,
nvl(wdl.reprice_required,'N') as reprice_required,
wda.parent_delivery_detail_id,
wdd.customer_id ,
wdd.sold_to_contact_id ,
wdd.inventory_item_id ,
wdd.item_description ,
wdd.hazard_class_id ,
wdd.country_of_origin ,
wdd.classification ,
wdd.requested_quantity ,
wdd.requested_quantity_uom ,
wdd.master_container_item_id ,
wdd.detail_container_item_id ,
wdd.customer_item_id ,
wdd.net_weight ,
wdd.organization_id ,
wdd.container_flag ,
wdd.container_type_code ,
wdd.container_name ,
wdd.fill_percent ,
wdd.gross_weight ,
wdd.currency_code ,
wdd.freight_class_cat_id ,
wdd.commodity_code_cat_id ,
wdd.weight_uom_code ,
wdd.volume ,
wdd.volume_uom_code ,
wdd.tp_attribute_category ,
wdd.tp_attribute1 ,
wdd.tp_attribute2 ,
wdd.tp_attribute3 ,
wdd.tp_attribute4 ,
wdd.tp_attribute5 ,
wdd.tp_attribute6 ,
wdd.tp_attribute7 ,
wdd.tp_attribute8 ,
wdd.tp_attribute9 ,
wdd.tp_attribute10 ,
wdd.tp_attribute11 ,
wdd.tp_attribute12 ,
wdd.tp_attribute13 ,
wdd.tp_attribute14 ,
wdd.tp_attribute15 ,
wdd.attribute_category ,
wdd.attribute1 ,
wdd.attribute2 ,
wdd.attribute3 ,
wdd.attribute4 ,
wdd.attribute5 ,
wdd.attribute6 ,
wdd.attribute7 ,
wdd.attribute8 ,
wdd.attribute9 ,
wdd.attribute10 ,
wdd.attribute11 ,
wdd.attribute12 ,
wdd.attribute13 ,
wdd.attribute14,
wdd.attribute15,
'FTE', -- source_type
NULL, -- source_line_id
NULL, -- source_header_id
NULL, -- source_consolidation_id
NULL, -- ship_date
NULL, -- arrival_date
NULL, -- comm_category_id : FTE J estimate rate
wda.type,
wda.parent_delivery_id,
wdl.parent_delivery_leg_id
from wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_delivery_legs wdl,
wsh_new_deliveries wd
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wdl.delivery_id
and wdl.delivery_id = wd.delivery_id
and (wda.type IS null OR wda.type <> 'O')
and wdl.delivery_leg_id = c_delivery_leg_id;
Select wdd.delivery_detail_id,
wda.delivery_id,
wdl.delivery_leg_id,
nvl(wdl.reprice_required,'N') as reprice_required,
wda.parent_delivery_detail_id,
wdd.customer_id ,
wdd.sold_to_contact_id ,
wdd.inventory_item_id ,
wdd.item_description ,
wdd.hazard_class_id ,
wdd.country_of_origin ,
wdd.classification ,
wdd.requested_quantity ,
wdd.requested_quantity_uom ,
wdd.master_container_item_id ,
wdd.detail_container_item_id ,
wdd.customer_item_id ,
wdd.net_weight ,
wdd.organization_id ,
wdd.container_flag ,
wdd.container_type_code ,
wdd.container_name ,
wdd.fill_percent ,
wdd.gross_weight ,
wdd.currency_code ,
wdd.freight_class_cat_id ,
wdd.commodity_code_cat_id ,
wdd.weight_uom_code ,
wdd.volume ,
wdd.volume_uom_code ,
wdd.tp_attribute_category ,
wdd.tp_attribute1 ,
wdd.tp_attribute2 ,
wdd.tp_attribute3 ,
wdd.tp_attribute4 ,
wdd.tp_attribute5 ,
wdd.tp_attribute6 ,
wdd.tp_attribute7 ,
wdd.tp_attribute8 ,
wdd.tp_attribute9 ,
wdd.tp_attribute10 ,
wdd.tp_attribute11 ,
wdd.tp_attribute12 ,
wdd.tp_attribute13 ,
wdd.tp_attribute14 ,
wdd.tp_attribute15 ,
wdd.attribute_category ,
wdd.attribute1 ,
wdd.attribute2 ,
wdd.attribute3 ,
wdd.attribute4 ,
wdd.attribute5 ,
wdd.attribute6 ,
wdd.attribute7 ,
wdd.attribute8 ,
wdd.attribute9 ,
wdd.attribute10 ,
wdd.attribute11 ,
wdd.attribute12 ,
wdd.attribute13 ,
wdd.attribute14,
wdd.attribute15,
'FTE', -- source_type
NULL, -- source_line_id
NULL, -- source_header_id
NULL, -- source_consolidation_id
NULL, -- ship_date
NULL, -- arrival_date
NULL, -- comm_category_id : FTE J estimate rate
wda.type,
wda.parent_delivery_id,
wdl.parent_delivery_leg_id
from wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_delivery_legs wdl,
wsh_new_deliveries wd, wsh_trips wt, wsh_trip_stops wts1, wsh_trip_stops wts2
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wdl.delivery_id
and wdl.delivery_id = wd.delivery_id
and wdl.pick_up_stop_id = wts1.stop_id
and wdl.drop_off_stop_id = wts2.stop_id
and wts1.trip_id = wt.trip_id
and (wda.type IS null OR wda.type <> 'O')
and wt.trip_id = c_trip_id;
fte_freight_pricing.g_shipment_line_rows.DELETE;
SELECT wgp.expand_carrier_rankings
FROM WSH_GLOBAL_PARAMETERS wgp;
SELECT t.rank_id
FROM WSH_TRIPS t
WHERE t.trip_id=c_trip_id;
select v.inventory_item_id,
v.ORGANIZATION_ID
from fte_vehicle_types v
where v.vehicle_type_id = c_vehicle_type_id;
FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Before calling FTE_CARRIER_RANK_LIST_PVT.RANK_LIST_ACTION UPDATE');
p_action_code =>FTE_CARRIER_RANK_LIST_PVT.S_UPDATE,
p_ranklist =>l_rank_tab,
p_trip_id =>p_rank_rec.trip_id,
p_rank_id =>l_rank_id,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data);
raise FTE_FREIGHT_PRICING_UTIL.g_rank_list_update_fail;
--Commenting out as Rank list API handles update of rank on trip
--raise FTE_FREIGHT_PRICING_UTIL.g_rank_list_update_fail;
update_single_trip (p_trip_id => p_rank_rec.trip_id,
p_lane_id => p_rank_rec.lane_id,
p_carrier_id => p_rank_rec.carrier_id,
p_ship_method_code => l_ship_method_code,
p_ship_method_name => l_ship_method_meaning,
p_service_level => p_rank_rec.service_level,
p_mode_of_transport => p_rank_rec.mode_of_transport,
p_vehicle_type_id => p_vehicle_type,
p_vehicle_item_id => NULL,
p_vehicle_org_id => NULL,
--p_rank_id =>l_rank_id, This update is handled by rank list API
p_append_flag =>l_append_flag,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
WHEN FTE_FREIGHT_PRICING_UTIL.g_rank_list_update_fail THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_rank_list_update_fail');
-- update trip with lane_id, ship_method
update_single_trip(
p_trip_id => p_trip_id,
p_lane_id => l_matched_lanes(l_lowest_lane_index).lane_id,
p_carrier_id => l_matched_lanes(l_lowest_lane_index).carrier_id,
p_ship_method_code => l_matched_lanes(l_lowest_lane_index).ship_method_code,
p_ship_method_name => l_matched_lanes(l_lowest_lane_index).ship_method_name,
p_service_level => l_matched_lanes(l_lowest_lane_index).service_level,
p_mode_of_transport => l_matched_lanes(l_lowest_lane_index).mode_of_transport,
p_vehicle_type_id => l_vehicle_type,
p_vehicle_item_id =>NULL,
p_vehicle_org_id =>NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Calling Trip_Select_Service_Init for trip:'||p_trip_id||'At:'||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
FTE_WORKFLOW_UTIL.Trip_Select_Service_Init(
p_trip_id =>p_trip_id,
x_return_status =>l_return_status);
FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Trip_Select_Service_Init return_status:'||l_return_status||'At:'||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
g_dlv_tab.DELETE;
g_dlv_leg_tab.DELETE;
g_trip_info_tab.DELETE;
g_dleg_trip_tab.DELETE;
l_matched_lanes.DELETE;
FTE_TRIP_RATING_GRP.Delete_Main_Records(
p_trip_id=>l_trip_id,
p_init_prc_log=>'N',
x_return_status => l_return_status ) ;
-- update trip with lane_id, ship_method
update_single_trip(
p_trip_id => l_trip_id,
p_lane_id => l_matched_lanes(l_lowest_lane_index).lane_id,
p_carrier_id => l_matched_lanes(l_lowest_lane_index).carrier_id,
p_ship_method_code => l_matched_lanes(l_lowest_lane_index).ship_method_code,
p_ship_method_name => l_matched_lanes(l_lowest_lane_index).ship_method_name,
p_service_level => l_matched_lanes(l_lowest_lane_index).service_level,
p_mode_of_transport => l_matched_lanes(l_lowest_lane_index).mode_of_transport,
p_vehicle_type_id => null,
p_vehicle_item_id =>NULL,
p_vehicle_org_id =>NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
-- update wsh_freight_costs with l_lowest_rate
fte_freight_pricing.Move_fc_temp_to_main (
p_delivery_leg_id => l_dleg_id,
p_freight_cost_temp_price => l_lowest_lane_fct_price,
p_freight_cost_temp_charge => l_lowest_lane_fct_charge,
x_return_status => l_return_status);
FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Calling Trip_Select_Service_Init for trip:'||l_trip_id||'At:'||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
FTE_WORKFLOW_UTIL.Trip_Select_Service_Init(
p_trip_id =>l_trip_id,
x_return_status =>l_return_status);
FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Trip_Select_Service_Init return_status:'||l_return_status||'At:'||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
g_dlv_tab.DELETE;
g_dlv_leg_tab.DELETE;
g_trip_info_tab.DELETE;
g_dleg_trip_tab.DELETE;
l_matched_lanes.DELETE;
-- Need to delete existing freight cost records
-- for this delivery leg
FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'deleting existing freight cost records...');
FTE_TRIP_RATING_GRP.Delete_Main_Records(
p_trip_id=>l_trip_id,
p_init_prc_log=>'N',
x_return_status => l_return_status ) ;
--fte_freight_pricing.delete_invalid_fc_recs (
-- p_delivery_leg_id => l_dleg_id,
-- x_return_status => l_return_status ) ;
SELECT reprice_required INTO l_dummy
FROM wsh_delivery_legs
WHERE delivery_leg_id = l_dleg_id
FOR UPDATE OF reprice_required NOWAIT;
UPDATE wsh_delivery_legs
SET reprice_required = 'Y'
WHERE delivery_leg_id = l_dleg_id;
l_tl_lane_rows.delete;
l_tl_lane_refs.delete;
l_stop_charge_tab.delete;
l_del_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_GRP.Create_Update_Delivery
( p_api_version_number =>1.0,
p_init_msg_list =>FND_API.G_FALSE,
p_commit =>FND_API.G_FALSE,
p_in_rec =>l_del_in_rec,
p_rec_attr_tab => g_dlv_tab,
x_del_out_rec_tab => l_del_out_tab,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
update_single_trip(
p_trip_id => l_trip_id,
p_lane_id => l_matched_lanes(l_lowest_lane_index).lane_id,
p_carrier_id => l_matched_lanes(l_lowest_lane_index).carrier_id,
p_ship_method_code => l_matched_lanes(l_lowest_lane_index).ship_method_code,
p_ship_method_name => l_matched_lanes(l_lowest_lane_index).ship_method_name,
p_service_level => l_matched_lanes(l_lowest_lane_index).service_level,
p_mode_of_transport => l_matched_lanes(l_lowest_lane_index).mode_of_transport,
p_vehicle_type_id => l_vehicle_type,
p_vehicle_item_id =>NULL,
p_vehicle_org_id =>NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Calling Trip_Select_Service_Init for trip:'||l_trip_id||'At:'||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
FTE_WORKFLOW_UTIL.Trip_Select_Service_Init(
p_trip_id =>l_trip_id,
x_return_status =>l_return_status);
FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Trip_Select_Service_Init return_status:'||l_return_status||'At:'||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));