The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT t.vehicle_item_id
FROM wsh_trips t
WHERE t.trip_id=c_trip_id;
SELECT flrc.list_header_id
FROM fte_lane_rate_charts flrc
WHERE flrc.lane_id = c_lane_id
AND (flrc.start_date_active is null
OR flrc.start_date_active <= c_departure_date )
AND (flrc.end_date_active is null
OR flrc.end_date_active > c_departure_date );
SELECT wgp.pallet_item_type
FROM wsh_global_parameters wgp;
SELECT m.container_type_code
FROM mtl_system_items_b m ,
wsh_delivery_details d
WHERE d.inventory_item_id=m.inventory_item_id and
d.organization_id = m.organization_id and
d.delivery_detail_id=c_dtl_id;
PROCEDURE Partially_Delete_Cache(
p_trip_index IN NUMBER,
p_carrier_index IN NUMBER,
p_stop_index IN NUMBER,
p_dleg_index IN NUMBER,
p_child_dleg_index IN NUMBER) IS
BEGIN
g_tl_trip_rows.DELETE(p_trip_index);
g_tl_carrier_pref_rows.DELETE(p_carrier_index);
g_tl_trip_stop_rows.DELETE(p_stop_index,g_tl_trip_stop_rows.LAST);
g_tl_delivery_leg_rows.DELETE(p_dleg_index,g_tl_delivery_leg_rows.LAST);
g_tl_chld_delivery_leg_rows.DELETE(p_child_dleg_index,g_tl_chld_delivery_leg_rows.LAST);
END Partially_Delete_Cache;
PROCEDURE Delete_Cache(x_return_status OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(1);
FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Delete_Cache','start');
g_tl_delivery_leg_rows.DELETE;
g_tl_chld_delivery_leg_rows.DELETE;
g_tl_trip_rows.DELETE;
g_tl_trip_stop_rows.DELETE;
g_tl_carrier_pref_rows.DELETE;
g_tl_delivery_detail_map.DELETE;
g_tl_delivery_detail_hash.DELETE;
--Delete cache of delivery details
g_tl_shipment_line_rows.DELETE;
g_tl_int_shipment_line_rows.DELETE;
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Delete_Cache');
FTE_FREIGHT_PRICING_UTIL.set_exception('Delete_Cache',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Delete_Cache');
END Delete_Cache;
PROCEDURE Insert_Into_Dlv_Dtl_Cache(
p_dlv_dtl_rec IN FTE_FREIGHT_PRICING.shipment_line_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_map_index NUMBER;
FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Insert_Into_Dlv_Dtl_Cache','start');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Insert_Into_Dlv_Dtl_Cache');
FTE_FREIGHT_PRICING_UTIL.set_exception('Insert_Into_Dlv_Dtl_Cache',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Insert_Into_Dlv_Dtl_Cache');
END Insert_Into_Dlv_Dtl_Cache;
l_stop_distance_rec.from_stop_id||' inserted in tab');
SELECT latitude, longitude
FROM wsh_locations
WHERE wsh_location_id = c_location_id;
SELECT tl_hway_dis_emp_constant
FROM wsh_global_parameters;
SELECT avg_hway_speed
FROM wsh_global_parameters;
SELECT distance_uom
FROM wsh_global_parameters;
SELECT time_uom
FROM wsh_global_parameters;
SELECT SIN(l_from_latitude_in_radians) INTO l_t1 FROM DUAL;
SELECT SIN(l_to_latitude_in_radians) INTO l_t2 FROM DUAL;
SELECT COS(l_from_latitude_in_radians) INTO l_t3 FROM DUAL;
SELECT COS(l_to_latitude_in_radians) INTO l_t4 FROM DUAL;
SELECT COS(l_degrees_to_radians * (l_from_longitude_in_degrees - l_to_longitude_in_degrees))
INTO l_t5 FROM DUAL;
SELECT ACOS(l_t1 * l_t2 + l_t3 * l_t4 * l_t5)
INTO l_t6 FROM DUAL;
PROCEDURE Update_Trip_With_Stop_Info(
p_stop_rec IN TL_TRIP_STOP_INPUT_REC_TYPE ,
x_trip_rec IN OUT NOCOPY TL_trip_data_input_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(1);
FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Update_Trip_With_Stop_Info','start');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Update_Trip_With_Stop_Info');
FTE_FREIGHT_PRICING_UTIL.set_exception('Update_Trip_With_Stop_Info',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Update_Trip_With_Stop_Info');
END Update_Trip_With_Stop_Info;
SELECT rl.region_id,
rl.region_type
FROM wsh_region_locations rl
WHERE rl.location_id= c_location_id and
rl.region_type >= c_region_type
ORDER BY rl.region_type ASC;
SELECT r.region_id,
r.region_type,
r.parent_region_id
FROM wsh_regions r
WHERE r.region_id = c_region_id AND
r.region_type >=c_region_type;
SELECT dd.delivery_detail_id,
dl.delivery_id,
dl.delivery_leg_id,
dl.reprice_required,
da.parent_delivery_detail_id,
dd.customer_id,
dd.sold_to_contact_id,
dd.inventory_item_id,
dd.item_description,
dd.hazard_class_id,
dd.country_of_origin,
dd.classification,
dd.requested_quantity,
dd.requested_quantity_uom,
dd.master_container_item_id,
dd.detail_container_item_id,
dd.customer_item_id,
dd.net_weight,
dd.organization_id,
dd.container_flag,
dd.container_type_code,
dd.container_name,
dd.fill_percent,
dd.gross_weight,
dd.currency_code,dd.freight_class_cat_id,
dd.commodity_code_cat_id,
dd.weight_uom_code ,
dd.volume,
dd.volume_uom_code,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,
da.type,
da.parent_delivery_id,
dl.parent_delivery_leg_id
FROM wsh_delivery_assignments da,
wsh_delivery_legs dl ,
wsh_delivery_details dd,
wsh_trip_stops s
WHERE da.delivery_id=dl.delivery_id and
dl.pick_up_stop_id=s.stop_id and
s.trip_id = c_trip_id and
da.parent_delivery_detail_id is NOT null and
(da.type IS NULL OR da.type='S') and
da.delivery_detail_id = dd.delivery_detail_id
ORDER BY
da.delivery_id;
l_curr_dleg_list.DELETE(g_tl_delivery_leg_rows(l_parent_dleg_index).delivery_leg_id);
l_curr_dleg_list.DELETE(g_tl_chld_delivery_leg_rows(l_parent_dleg_index).delivery_leg_id);
l_next_dleg_list.DELETE;
l_curr_dleg_list.DELETE;
l_next_dleg_list.DELETE;
SELECT dd.delivery_detail_id,
dl.delivery_id,
dl.delivery_leg_id,
dl.reprice_required,
da.parent_delivery_detail_id,
dd.customer_id,
dd.sold_to_contact_id,
dd.inventory_item_id,
dd.item_description,
dd.hazard_class_id,
dd.country_of_origin,
dd.classification,
dd.requested_quantity,
dd.requested_quantity_uom,
dd.master_container_item_id,
dd.detail_container_item_id,
dd.customer_item_id,
dd.net_weight,
dd.organization_id,
dd.container_flag,
dd.container_type_code,
dd.container_name,
dd.fill_percent,
dd.gross_weight,
dd.currency_code,dd.freight_class_cat_id,
dd.commodity_code_cat_id,
dd.weight_uom_code ,
dd.volume,
dd.volume_uom_code,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,
da.type,
da.parent_delivery_id,
dl.parent_delivery_leg_id
FROM wsh_delivery_assignments da,
wsh_delivery_legs dl ,
wsh_delivery_details dd
WHERE da.delivery_id=dl.delivery_id and
dl.pick_up_stop_id=c_pick_up_stop_id and
da.delivery_detail_id = dd.delivery_detail_id and
(
( (da.type IS NULL OR da.type='S') and (da.parent_delivery_detail_id is null) and (dl.parent_delivery_leg_id is null)) -- non-MDC trips top level details
OR
((da.type='O') and (da.parent_delivery_detail_id is null) and (dl.parent_delivery_leg_id is null))
--MDC trip top level details of top level parent deliveries .If in these cases the type is made 'S' we would not need an extra clause.
OR
( (da.type='C' ) and (dl.parent_delivery_leg_id is not null) and
exists ( select pdl.pick_up_stop_id from wsh_delivery_legs pdl where pdl.delivery_leg_id=dl.parent_delivery_leg_id and pdl.pick_up_stop_id=c_pick_up_stop_id and pdl.delivery_id=da.parent_delivery_id))
--MDC trip ,top level details of deliveries that have parents that are on the same trip
)
ORDER BY
da.delivery_id;
SELECT dd.delivery_detail_id,
dl.delivery_id,
dl.delivery_leg_id,
dl.reprice_required,
da.parent_delivery_detail_id,
dd.customer_id,
dd.sold_to_contact_id,
dd.inventory_item_id,
dd.item_description,
dd.hazard_class_id,
dd.country_of_origin,
dd.classification,
dd.requested_quantity,
dd.requested_quantity_uom,
dd.master_container_item_id,
dd.detail_container_item_id,
dd.customer_item_id,
dd.net_weight,
dd.organization_id,
dd.container_flag,
dd.container_type_code,
dd.container_name,
dd.fill_percent,
dd.gross_weight,
dd.currency_code,dd.freight_class_cat_id,
dd.commodity_code_cat_id,
dd.weight_uom_code ,
dd.volume,
dd.volume_uom_code,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,
da.type,
da.parent_delivery_id,
dl.parent_delivery_leg_id
FROM wsh_delivery_assignments da,
wsh_delivery_legs dl ,
wsh_delivery_details dd
WHERE da.delivery_id=dl.delivery_id and
dl.drop_off_stop_id=c_drop_off_stop_id and
da.delivery_detail_id = dd.delivery_detail_id and
(
( (da.type IS NULL OR da.type='S') and (da.parent_delivery_detail_id is null) and (dl.parent_delivery_leg_id is null))
-- non-MDC trips top level details
OR
((da.type='O') and (da.parent_delivery_detail_id is null) and (dl.parent_delivery_leg_id is null))
--MDC trip top level details of top level parent deliveries.If in these cases the type is made 'S' we would not need an extra clause.
OR
( (da.type='C' ) and (dl.parent_delivery_leg_id is not null) and
exists ( select pdl.drop_off_stop_id from wsh_delivery_legs pdl where pdl.delivery_leg_id=dl.parent_delivery_leg_id and pdl.drop_off_stop_id=c_drop_off_stop_id and pdl.delivery_id=da.parent_delivery_id))
--MDC trip ,top level details of deliveries that have parents that are on the same trip
)
ORDER BY
da.delivery_id;
SELECT c.carrier_id,
c.max_out_of_route,
c.min_cm_distance,
c.min_cm_time,
c.cm_free_dh_mileage,
c.cm_first_load_discount,
c.currency_code,
c.cm_rate_variant,
c.unit_rate_basis,
null,
c.weight_uom,
null,
c.volume_uom,
null,
c.distance_uom,
null,
c.time_uom,
c.origin_dstn_surcharge_level,
c.distance_calculation_method,
c.dim_dimensional_factor,
c.dim_weight_uom,
c.dim_volume_uom,
c.dim_dimension_uom,
c.dim_min_pack_vol
FROM WSH_CARRIERS c
WHERE c.carrier_id=c_carrier_id;
SELECT c.carrier_id,
c.max_out_of_route,
c.min_cm_distance,
c.min_cm_time,
c.cm_free_dh_mileage,
c.cm_first_load_discount,
null,
c.cm_rate_variant,
c.unit_rate_basis,
null,
null,
null,
null,
null,
null,
null,
null,
c.origin_dstn_surcharge_level,
c.distance_calculation_method,
c.dim_dimensional_factor,
c.dim_weight_uom,
c.dim_volume_uom,
c.dim_dimension_uom,
c.dim_min_pack_vol
FROM WSH_CARRIER_SERVICES c
WHERE c.carrier_id=c_carrier_id and
c.service_level=c_service_level;
SELECT s.stop_id ,
s.trip_id,
s.stop_location_id,
NVL(s.wkday_layover_stops,0),
NVL(s.wkend_layover_stops,0),
null,
null,
0,
0,
0,
0,
null,
0,
0,
0,
0,
null,
null,
s.planned_arrival_date,
s.planned_departure_date,
null,
s.physical_stop_id,
s.physical_location_id,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
FROM wsh_trip_stops s
WHERE s.trip_id=c_trip_id
ORDER by s.stop_sequence_number;
SELECT dl.delivery_leg_id,
s.trip_id,
dl.delivery_id,
dl.pick_up_stop_id,
null,
dl.drop_off_stop_id,
s.stop_location_id,
0,
0,
0,
0,
0,
0,
dl.parent_delivery_leg_id,
0,
0,
null,
null
FROM wsh_delivery_legs dl,
wsh_trip_stops s
WHERE dl.drop_off_stop_id = s.stop_id and
dl.pick_up_stop_id=c_pick_up_stop_id;
--Insert into delivery detail cache
Insert_Into_Dlv_Dtl_Cache(
p_dlv_dtl_rec=>l_dlv_detail_rec,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_tl_insert_dlv_dtl_fail;
--Insert into delivery detail cache
Insert_Into_Dlv_Dtl_Cache(
p_dlv_dtl_rec=>l_dlv_detail_rec,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_tl_insert_dlv_dtl_fail;
--Insert into dleg cache
Validate_Dleg_Info(
x_dleg_info=> l_dleg_rec,
x_return_status => l_return_status);
--MDC insert into child dleg cache
g_tl_chld_delivery_leg_rows(x_child_dleg_index):=l_dleg_rec;
--11.5.10+Update current weight of the trip
l_current_weight:=l_current_weight+l_stop_rec.pickup_weight-
l_stop_rec.dropoff_weight
-(g_tl_trip_stop_rows(x_stop_index-1).pickup_weight-g_tl_trip_stop_rows(x_stop_index-1).dropoff_weight);
--11.5.10+Update the trip with internal stop pickup dropoff quantities
x_trip_rec.number_of_pallets:=x_trip_rec.number_of_pallets +
l_stop_rec.pickup_pallets-g_tl_trip_stop_rows(x_stop_index-1).pickup_pallets;
--Update current weight of the trip
l_current_weight:=l_current_weight+l_stop_rec.pickup_weight-
l_stop_rec.dropoff_weight;
--Update trip rec
Update_Trip_With_Stop_Info(
p_stop_rec => l_stop_rec,
x_trip_rec => x_trip_rec,
x_return_status => l_return_status);
--Insert Stop info into Cache
--Perform validation after getting dist,time,fac info
g_tl_trip_stop_rows(x_stop_index):=l_stop_rec;
--GEt distances/time from mileage table, update, stop, dleg buffer, trip
--loaded, unlaoded distances
Get_Distances(
p_stop_index => l_initial_stop_index,
p_dleg_index => l_initial_dleg_index,
p_carrier_rec => l_carrier_service_rec,
x_stop_distance_tab =>l_stop_distance_tab,
x_trip_rec => x_trip_rec,
x_return_status => l_return_status);
--Update trip rec
x_trip_rec.number_of_stops:=l_stop_count;
--Insert into trip cache
Validate_Trip_Info(
x_trip_info=> x_trip_rec,
x_return_status => l_return_status);
--Insert carrier info into cache
g_tl_carrier_pref_rows(x_carrier_index):=l_carrier_service_rec;
WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_insert_dlv_dtl_fail THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('Cache_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_insert_dlv_dtl_fail');
SELECT dd.delivery_detail_id,
da.delivery_id,
FAKE_DLEG_ID,
'Y',
da.parent_delivery_detail_id,
dd.customer_id,
dd.sold_to_contact_id,
dd.inventory_item_id,
dd.item_description,
dd.hazard_class_id,
dd.country_of_origin,
dd.classification,
dd.requested_quantity,
dd.requested_quantity_uom,
dd.master_container_item_id,
dd.detail_container_item_id,
dd.customer_item_id,
dd.net_weight,
dd.organization_id,
dd.container_flag,
dd.container_type_code,
dd.container_name,
dd.fill_percent,
dd.gross_weight,
dd.currency_code,
dd.freight_class_cat_id,
dd.commodity_code_cat_id,
dd.weight_uom_code ,
dd.volume,
dd.volume_uom_code,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,
null,--MDC columns
null,--MDC columns
null--MDC columns
FROM wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE da.delivery_id=c_delivery_id and
da.parent_delivery_detail_id is null and
da.delivery_detail_id = dd.delivery_detail_id;
--Insert into delivery detail cache
Add_Dropoff_Quantity(
p_dlv_detail_rec =>l_dlv_detail_rec,
p_carrier_pref =>p_carrier_pref_rec,
x_stop_rec =>x_dropoff_stop_rec,
x_return_status => l_return_status);
--Insert into dlv details cache
Insert_Into_Dlv_Dtl_Cache(
p_dlv_dtl_rec=>l_dlv_detail_rec,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_tl_insert_dlv_dtl_fail;
WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_insert_dlv_dtl_fail THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('Add_Delivery_Details',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_insert_dlv_dtl_fail');
SELECT c.carrier_id,
c.max_out_of_route,
c.min_cm_distance,
c.min_cm_time,
c.cm_free_dh_mileage,
c.cm_first_load_discount,
c.currency_code,
c.cm_rate_variant,
c.unit_rate_basis,
null,
c.weight_uom,
null,
c.volume_uom,
null,
c.distance_uom,
null,
c.time_uom,
c.origin_dstn_surcharge_level,
c.distance_calculation_method,
c.dim_dimensional_factor,
c.dim_weight_uom,
c.dim_volume_uom,
c.dim_dimension_uom,
c.dim_min_pack_vol
FROM WSH_CARRIERS c ,
FTE_LANES l,
FTE_SCHEDULES s
WHERE c.carrier_id=l.carrier_id and
s.schedules_id=c_schedule_id and
s.lane_id=l.lane_id;
SELECT c.carrier_id,
c.max_out_of_route,
c.min_cm_distance,
c.min_cm_time,
c.cm_free_dh_mileage,
c.cm_first_load_discount,
null,
c.cm_rate_variant,
c.unit_rate_basis,
null,
null,
null,
null,
null,
null,
null,
null,
c.origin_dstn_surcharge_level,
c.distance_calculation_method,
c.dim_dimensional_factor,
c.dim_weight_uom,
c.dim_volume_uom,
c.dim_dimension_uom,
c.dim_min_pack_vol
FROM WSH_CARRIER_SERVICES c ,
FTE_LANES l,
FTE_SCHEDULES s
WHERE c.carrier_id=l.carrier_id and
c.service_level=l.service_type_code and
s.schedules_id=c_schedule_id and
s.lane_id=l.lane_id;
SELECT c.carrier_id,
c.max_out_of_route,
c.min_cm_distance,
c.min_cm_time,
c.cm_free_dh_mileage,
c.cm_first_load_discount,
c.currency_code,
c.cm_rate_variant,
c.unit_rate_basis,
null,
c.weight_uom,
null,
c.volume_uom,
null,
c.distance_uom,
null,
c.time_uom,
c.origin_dstn_surcharge_level,
c.distance_calculation_method,
c.dim_dimensional_factor,
c.dim_weight_uom,
c.dim_volume_uom,
c.dim_dimension_uom,
c.dim_min_pack_vol
FROM WSH_CARRIERS c ,
FTE_LANES l
WHERE c.carrier_id=l.carrier_id and
l.lane_id=c_lane_id;
SELECT c.carrier_id,
c.max_out_of_route,
c.min_cm_distance,
c.min_cm_time,
c.cm_free_dh_mileage,
c.cm_first_load_discount,
null,
c.cm_rate_variant,
c.unit_rate_basis,
null,
null,
null,
null,
null,
null,
null,
null,
c.origin_dstn_surcharge_level,
c.distance_calculation_method,
c.dim_dimensional_factor,
c.dim_weight_uom,
c.dim_volume_uom,
c.dim_dimension_uom,
c.dim_min_pack_vol
FROM WSH_CARRIER_SERVICES c ,
FTE_LANES l
WHERE c.carrier_id=l.carrier_id and
c.service_level=l.service_type_code and
l.lane_id=c_lane_id;
SELECT l.lane_id,
l.carrier_id,
l.mode_of_transportation_code,
l.service_type_code,
flrc.list_header_id
FROM fte_lanes l,
fte_lane_rate_charts flrc
WHERE l.lane_id=c_lane_id
AND (l.lane_id = flrc.lane_id )
AND (flrc.start_date_active is null
OR flrc.start_date_active <= x_trip_rec.planned_departure_date )
AND (flrc.end_date_active is null
OR flrc.end_date_active > x_trip_rec.planned_departure_date );
SELECT l.lane_id,
l.carrier_id,
l.mode_of_transportation_code,
l.service_type_code,
s.schedules_id,
flrc.list_header_id
FROM fte_lanes l,
fte_schedules s,
fte_lane_rate_charts flrc
WHERE l.lane_id=s.lane_id
AND (l.lane_id = flrc.lane_id )
AND (flrc.start_date_active is null
OR flrc.start_date_active <= x_trip_rec.planned_departure_date )
AND (flrc.end_date_active is null
OR flrc.end_date_active > x_trip_rec.planned_departure_date )
AND s.schedules_id=c_schedule_id;
PROCEDURE Update_Dummy_Records(
p_weight_uom IN VARCHAR2,
p_volume_uom IN VARCHAR2,
p_weight IN NUMBER,
p_volume IN NUMBER,
p_containers IN NUMBER,
p_pallets IN NUMBER,
x_carrier_rec IN OUT NOCOPY TL_CARRIER_PREF_REC_TYPE ,
x_trip_rec IN OUT NOCOPY TL_trip_data_input_rec_type,
x_pickup_stop IN OUT NOCOPY TL_TRIP_STOP_INPUT_REC_TYPE,
x_dropoff_stop IN OUT NOCOPY TL_TRIP_STOP_INPUT_REC_TYPE,
x_dleg IN OUT NOCOPY TL_delivery_leg_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_quantity NUMBER;
FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Update_Dummy_Records','start');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Update_Dummy_Records');
FTE_FREIGHT_PRICING_UTIL.set_exception('Update_Dummy_Records',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_weight_uom_conv_fail');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Update_Dummy_Records');
FTE_FREIGHT_PRICING_UTIL.set_exception('Update_Dummy_Records',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_vol_uom_conv_fail');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Update_Dummy_Records');
FTE_FREIGHT_PRICING_UTIL.set_exception('Update_Dummy_Records',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Update_Dummy_Records');
END Update_Dummy_Records;
SELECT FAKE_DLEG_ID,
FAKE_TRIP_ID,
d.delivery_id,
FAKE_STOP_ID_1,
d.initial_pickup_location_id,
FAKE_STOP_ID_2,
d.ultimate_dropoff_location_id,
0,
0,
0,
0,
0,
0,
null,--MDC
0,
0,
null,
null
FROM wsh_new_deliveries d
WHERE d.delivery_id =c_delivery_id;
SELECT d.initial_pickup_date,
d.ultimate_dropoff_date,
d.initial_pickup_location_id,
d.ultimate_dropoff_location_id
FROM wsh_new_deliveries d
WHERE d.delivery_id=c_delivery_id;
SELECT dl.delivery_leg_id,
null,
dl.delivery_id,
dl.pick_up_stop_id,
null,
dl.drop_off_stop_id,
null,
0,
0,
0,
0,
0,
0,
dl.parent_delivery_leg_id,
0,
0,
null,
null
FROM wsh_delivery_legs dl
WHERE dl.delivery_leg_id=c_dleg_id;
SELECT t.trip_id,
t.lane_id,
null,
t.service_level,
t.carrier_id,
t.mode_of_transport,
null, --t.vehicle_item_id,
null,
0,
0,
0,
0,
0,
0,
null, -- t.total_trip_distance,
null, -- t.total_direct_distance,
null,
0,
0,
null,
null,
null,
null,
null,
null,
null
FROM wsh_trips t ,
wsh_trip_stops s
WHERE t.trip_id=s.trip_id AND
s.stop_id=c_stop_id;
SELECT s.trip_id
FROM wsh_delivery_legs dl,
wsh_trip_stops s
WHERE dl.delivery_leg_id=c_dleg_id AND
dl.pick_up_stop_id=s.stop_id;
SELECT l.lane_id,
l.service_type_code,
l.mode_of_transportation_code,
l.pricelist_id
FROM FTE_LANES l
WHERE l.lane_id=c_lane_id;
SELECT l.lane_id,
l.service_type_code,
l.mode_of_transportation_code,
l.pricelist_id
FROM FTE_LANES l,
FTE_SCHEDULES s
WHERE l.lane_id=s.lane_id and
s.schedules_id=c_schedule_id;
--Insert into Carrier Cache
g_tl_carrier_pref_rows(l_carrier_index):=l_carrier_rec;
--Insert into Trip Cache
g_tl_trip_rows(l_carrier_index):=l_trip_rec;
--Insert into Stop cache
g_tl_trip_stop_rows(2*l_carrier_index):=
l_pickup_stop_rec;
--Insert into dleg cache
g_tl_delivery_leg_rows(l_carrier_index):=l_dleg_rec;
Update_Dummy_Records(
p_weight_uom =>l_weight_uom ,
p_volume_uom =>l_volume_uom,
p_weight =>l_weight,
p_volume =>l_volume,
p_containers =>l_containers,
p_pallets =>l_pallets,
x_carrier_rec =>g_tl_carrier_pref_rows(l_carrier_index),
x_trip_rec =>g_tl_trip_rows(l_carrier_index),
x_pickup_stop =>g_tl_trip_stop_rows(2*l_carrier_index),
x_dropoff_stop =>g_tl_trip_stop_rows((2*l_carrier_index)+1),
x_dleg =>g_tl_delivery_leg_rows(l_carrier_index),
x_return_status =>l_return_status
);
--Insert into Carrier Cache
g_tl_carrier_pref_rows(l_carrier_index):=l_carrier_rec;
--Insert into Trip Cache
g_tl_trip_rows(l_carrier_index):=l_trip_rec;
--Insert into Stop cache
g_tl_trip_stop_rows(2*l_carrier_index):=
l_pickup_stop_rec;
--Insert into dleg cache
g_tl_delivery_leg_rows(l_carrier_index):=l_dleg_rec;
Update_Dummy_Records(
p_weight_uom=> l_weight_uom ,
p_volume_uom=> l_volume_uom,
p_weight=> l_weight,
p_volume=> l_volume,
p_containers=> l_containers,
p_pallets=> l_pallets,
x_carrier_rec=> g_tl_carrier_pref_rows(l_carrier_index),
x_trip_rec=> g_tl_trip_rows(l_carrier_index),
x_pickup_stop=> g_tl_trip_stop_rows(2*l_carrier_index),
x_dropoff_stop=>g_tl_trip_stop_rows((2*l_carrier_index)+1),
x_dleg=>g_tl_delivery_leg_rows(l_carrier_index),
x_return_status =>l_return_status);
SELECT t.trip_id,
t.lane_id,
null,
t.service_level,
t.carrier_id,
t.mode_of_transport,
t.vehicle_item_id,
null,
0,
0,
0,
0,
0,
0,
null, -- t.total_trip_distance,
null, -- t.total_direct_distance,
null,
0,
0,
'N',
null,
null,
null,
null,
null,
null
FROM wsh_trips t
WHERE t.trip_id=c_trip_id;
SELECT t.trip_id,
t.lane_id,
null,
t.service_level,
t.carrier_id,
t.mode_of_transport,
t.vehicle_item_id,
null,
0,
0,
0,
0,
0,
0,
null, -- t.total_trip_distance,
null, -- t.total_direct_distance,
null,
0,
0,
'Y',
null,
null,
null,
null,
null,
null
FROM wsh_trips t ,
fte_trip_moves m
WHERE m.move_id=c_move_id and
t.trip_id=m.trip_id
ORDER BY m.sequence_number;
SELECT t.trip_id,
t.lane_id,
null,
t.service_level,
t.carrier_id,
t.mode_of_transport,
null, --t.vehicle_item_id,
null,
0,
0,
0,
0,
0,
0,
null, -- t.total_trip_distance,
null, -- t.total_direct_distance,
null,
0,
0,
null,
null,
null,
null,
null,
null,
null
FROM wsh_trips t
WHERE t.trip_id=c_trip_id;
SELECT null,
l.carrier_id,
l.service_type_code,
l.mode_of_transportation_code
FROM fte_lanes l
WHERE l.lane_id=c_lane_id;
SELECT l.lane_id,
null,
l.carrier_id,
l.service_type_code,
l.mode_of_transportation_code
FROM fte_lanes l,
fte_schedules s
WHERE s.schedules_id=c_schedule_id and
s.lane_id=l.lane_id;
l_stop_quantity_tab.DELETE;
Delete_Cache(x_return_status=>l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
--DELETE Newly added cache
Partially_Delete_Cache(
p_trip_index=>l_trip_index,
p_carrier_index=>l_carrier_index,
p_stop_index=>l_stop_index,
p_dleg_index=>l_dleg_index,
p_child_dleg_index=>l_child_dleg_index);
WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('TL_BUILD_CACHE_FOR_TRP_COMPARE',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');
SELECT ploc.LOCATION_ID internal_org_location_id
FROM PO_LOCATION_ASSOCIATIONS_ALL ploc,
hz_cust_site_uses_all site_uses,
hz_cust_acct_sites_all acct_sites,
HZ_PARTY_SITES sites
WHERE ploc.SITE_USE_ID = site_uses.SITE_USE_ID
AND site_uses.CUST_ACCT_SITE_ID = acct_sites.CUST_ACCT_SITE_ID
AND acct_sites.PARTY_SITE_ID = sites.PARTY_SITE_ID
AND ploc.CUSTOMER_ID = acct_sites.CUST_ACCOUNT_ID
AND sites.location_id = c_int_cust_loc_id;
SELECT d.initial_pickup_date,
d.ultimate_dropoff_date,
d.initial_pickup_location_id,
d.ultimate_dropoff_location_id
FROM wsh_new_deliveries d
WHERE d.delivery_id=c_delivery_id;
--Insert carrier info into cache
g_tl_carrier_pref_rows(x_carrier_index):=l_carrier_rec;
--Update trip rec
Update_Trip_With_Stop_Info(
p_stop_rec => l_pickup_stop_rec,
x_trip_rec => l_trip_rec,
x_return_status => l_return_status);
--Insert Stop info into Cache
--Perform validation after getting dist,time,fac info
g_tl_trip_stop_rows(x_stop_index):=l_pickup_stop_rec;
--Update trip rec
Update_Trip_With_Stop_Info(
p_stop_rec => l_dropoff_stop_rec,
x_trip_rec => l_trip_rec,
x_return_status => l_return_status);
--Insert Stop info into Cache
--Perform validation after getting dist,time,fac info
g_tl_trip_stop_rows(x_stop_index):=l_dropoff_stop_rec;
--GEt distances/time from mileage table, update, stop, dleg buffer, trip
--loaded, unlaoded distances
Get_Distances(
p_stop_index => l_initial_stop_index,
p_dleg_index => l_initial_dleg_index,
p_carrier_rec => l_carrier_rec,
x_stop_distance_tab =>l_stop_distance_tab,
x_trip_rec => l_trip_rec,
x_return_status => l_return_status);
--Update trip rec
l_trip_rec.number_of_stops:=2;
--Insert into trip cache
Validate_Trip_Info(
x_trip_info=> l_trip_rec,
x_return_status => l_return_status);
Delete_Cache(x_return_status=>l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
--DELETE Newly added cache
Partially_Delete_Cache(
p_trip_index=>l_trip_index,
p_carrier_index=>l_carrier_index,
p_stop_index=>l_stop_index,
p_dleg_index=>l_dleg_index,
p_child_dleg_index=>l_child_dleg_index);
WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('TL_BUILD_CACHE_FOR_DLV_COMPARE',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');
SELECT t.trip_id,
t.lane_id,
null,
t.service_level,
t.carrier_id,
t.mode_of_transport,
t.vehicle_item_id,
null,
0,
0,
0,
0,
0,
0,
null, -- t.total_trip_distance,
null, -- t.total_direct_distance,
null,
0,
0,
'N',
null,
null,
null,
null,
null,
null
FROM wsh_trips t
WHERE t.trip_id=c_trip_id;
SELECT c.carrier_id,
c.max_out_of_route,
c.min_cm_distance,
c.min_cm_time,
c.cm_free_dh_mileage,
c.cm_first_load_discount,
c.currency_code,
c.cm_rate_variant,
c.unit_rate_basis,
null,
c.weight_uom,
null,
c.volume_uom,
null,
c.distance_uom,
null,
c.time_uom,
c.origin_dstn_surcharge_level,
c.distance_calculation_method,
c.dim_dimensional_factor,
c.dim_weight_uom,
c.dim_volume_uom,
c.dim_dimension_uom,
c.dim_min_pack_vol
FROM WSH_CARRIERS c
WHERE c.carrier_id=c_carrier_id;
SELECT c.carrier_id,
c.max_out_of_route,
c.min_cm_distance,
c.min_cm_time,
c.cm_free_dh_mileage,
c.cm_first_load_discount,
null,
c.cm_rate_variant,
c.unit_rate_basis,
null,
null,
null,
null,
null,
null,
null,
null,
c.origin_dstn_surcharge_level,
c.distance_calculation_method,
c.dim_dimensional_factor,
c.dim_weight_uom,
c.dim_volume_uom,
c.dim_dimension_uom,
c.dim_min_pack_vol
FROM WSH_CARRIER_SERVICES c
WHERE c.carrier_id=c_carrier_id and
c.service_level=c_service_level;
SELECT dd.delivery_detail_id,
dl.delivery_id,
dl.delivery_leg_id,
dl.reprice_required,
da.parent_delivery_detail_id,
dd.customer_id,
dd.sold_to_contact_id,
dd.inventory_item_id,
dd.item_description,
dd.hazard_class_id,
dd.country_of_origin,
dd.classification,
dd.requested_quantity,
dd.requested_quantity_uom,
dd.master_container_item_id,
dd.detail_container_item_id,
dd.customer_item_id,
dd.net_weight,
dd.organization_id,
dd.container_flag,
dd.container_type_code,
dd.container_name,
dd.fill_percent,
dd.gross_weight,
dd.currency_code,dd.freight_class_cat_id,
dd.commodity_code_cat_id,
dd.weight_uom_code ,
dd.volume,
dd.volume_uom_code,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,
da.type,
da.parent_delivery_id,
dl.parent_delivery_leg_id
FROM wsh_delivery_assignments da,
wsh_delivery_legs dl ,
wsh_delivery_details dd,
wsh_trip_stops s
WHERE da.delivery_id=dl.delivery_id and
s.trip_id=c_trip_id and
dl.pick_up_stop_id=s.stop_id and
da.parent_delivery_detail_id is null and
da.delivery_detail_id = dd.delivery_detail_id and
((da.type IS NULL OR da.type='S')
OR
(da.type='O' and dl.parent_delivery_leg_id is null)
)
ORDER BY
da.delivery_id;
SELECT s.stop_id ,
s.trip_id,
s.stop_location_id,
NVL(s.wkday_layover_stops,0),
NVL(s.wkend_layover_stops,0),
null,
null,
0,
0,
0,
0,
null,
0,
0,
0,
0,
null,
null,
s.planned_arrival_date,
s.planned_departure_date,
null,
s.physical_stop_id,
s.physical_location_id,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
FROM wsh_trip_stops s
WHERE s.trip_id=c_trip_id AND (s.physical_stop_id is NULL)
ORDER by s.stop_sequence_number;
Delete_Cache(x_return_status=>l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
--Insert Stop info into Cache
g_tl_trip_stop_rows(l_stop_index):=l_stop_rec;
--GEt distances/time from mileage table, update, stop, dleg buffer, trip
--loaded, unlaoded distances
Get_Distances(
p_stop_index => l_initial_stop_index,
p_dleg_index => l_initial_dleg_index,
p_carrier_rec => p_carrier_rec,
x_stop_distance_tab =>l_stop_distance_tab,
x_trip_rec => l_trip_rec,
x_return_status => l_return_status);
Delete_Cache(x_return_status=>l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('Get_Trip_Distance',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');
SELECT avg_hway_speed
FROM wsh_global_parameters;
SELECT distance_uom
FROM wsh_global_parameters;
SELECT time_uom
FROM wsh_global_parameters;
SELECT null,
l.carrier_id,
l.service_type_code,
l.mode_of_transportation_code
FROM fte_lanes l
WHERE l.lane_id=c_lane_id;
SELECT l.lane_id,
null,
l.carrier_id,
l.service_type_code,
l.mode_of_transportation_code
FROM fte_lanes l,
fte_schedules s
WHERE s.schedules_id=c_schedule_id and
s.lane_id=l.lane_id;
--Insert carrier info into cache
g_tl_carrier_pref_rows(x_carrier_index):=l_carrier_rec;
Update_Dummy_Records(
p_weight_uom =>l_carrier_rec.weight_uom ,
p_volume_uom =>l_carrier_rec.volume_uom,
p_weight =>l_trip_rec.total_weight,
p_volume =>l_trip_rec.total_volume,
p_containers =>l_trip_rec.number_of_containers,
p_pallets =>l_trip_rec.number_of_pallets,
x_carrier_rec =>l_carrier_rec,
x_trip_rec =>l_trip_rec,
x_pickup_stop =>l_pickup_stop_rec,
x_dropoff_stop =>l_dropoff_stop_rec,
x_dleg =>l_dleg_rec,
x_return_status =>l_return_status
);
--Insert pickup stop
g_tl_trip_stop_rows(x_stop_index):=l_pickup_stop_rec;
--Insert dropoff stop
g_tl_trip_stop_rows(x_stop_index):=l_dropoff_stop_rec;
--Insert dleg
Validate_Dleg_Info(
x_dleg_info=> l_dleg_rec,
x_return_status => l_return_status);
Insert_Into_Dlv_Dtl_Cache(
p_dlv_dtl_rec=>l_dlv_detail_info,
x_return_status=>l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_tl_insert_dlv_dtl_fail;
--Insert into trip cache
Validate_Trip_Info(
x_trip_info=> l_trip_rec,
x_return_status => l_return_status);
WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_insert_dlv_dtl_fail THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Cache_First_Estimate_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_insert_dlv_dtl_fail');
Delete_Cache(x_return_status=>l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
--DELETE Newly added cache
Partially_Delete_Cache(
p_trip_index=>l_trip_index,
p_carrier_index=>l_carrier_index,
p_stop_index=>l_stop_index,
p_dleg_index=>l_dleg_index,
p_child_dleg_index=>l_child_dleg_index);
WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('TL_BUILD_CACHE_FOR_ESTIMATE',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');
--Insert into delivery detail cache
Add_Dropoff_Quantity(
p_dlv_detail_rec =>l_dlv_detail_rec,
p_carrier_pref =>p_carrier_pref_rec,
x_stop_rec =>x_dropoff_stop_rec,
x_return_status => l_return_status);
--Insert into dlv details cache
Insert_Into_Dlv_Dtl_Cache(
p_dlv_dtl_rec=>l_dlv_detail_rec,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_tl_insert_dlv_dtl_fail;
WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_insert_dlv_dtl_fail THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('Add_Source_Lines_As_Details',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_insert_dlv_dtl_fail');
--Insert carrier info into cache
g_tl_carrier_pref_rows(x_carrier_index):=l_carrier_rec;
--Update trip rec
Update_Trip_With_Stop_Info(
p_stop_rec => l_pickup_stop_rec,
x_trip_rec => l_trip_rec,
x_return_status => l_return_status);
--Insert Stop info into Cache
--Perform validation after getting dist,time,fac info
g_tl_trip_stop_rows(x_stop_index):=l_pickup_stop_rec;
--Update trip rec
Update_Trip_With_Stop_Info(
p_stop_rec => l_dropoff_stop_rec,
x_trip_rec => l_trip_rec,
x_return_status => l_return_status);
--Insert Stop info into Cache
--Perform validation after getting dist,time,fac info
g_tl_trip_stop_rows(x_stop_index):=l_dropoff_stop_rec;
--GEt distances/time from mileage table, update, stop, dleg buffer, trip
--loaded, unlaoded distances
Get_Distances(
p_stop_index => l_initial_stop_index,
p_dleg_index => l_initial_dleg_index,
p_carrier_rec => l_carrier_rec,
x_stop_distance_tab =>l_stop_distance_tab,
x_trip_rec => l_trip_rec,
x_return_status => l_return_status);
--Update trip rec
l_trip_rec.number_of_stops:=2;
--Insert into trip cache
Validate_Trip_Info(
x_trip_info=> l_trip_rec,
x_return_status => l_return_status);
Delete_Cache(x_return_status=>l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
--DELETE Newly added cache
Partially_Delete_Cache(
p_trip_index=>l_trip_index,
p_carrier_index=>l_carrier_index,
p_stop_index=>l_stop_index,
p_dleg_index=>l_dleg_index,
p_child_dleg_index=>l_child_dleg_index);
WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('TL_BUILD_CACHE_FOR_OM',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');