[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT uom_for_num_of_units
FROM wsh_global_parameters;
SELECT mc.category_id
FROM mtl_categories mc, mtl_item_categories mic,
mtl_category_sets_tl mcstl
WHERE mic.inventory_item_id = c_item_id
AND mic.organization_id = c_org_id
AND mic.category_set_id = mcstl.category_set_id
AND mc.category_id = mic.category_id
AND mc.segment1 = c_classification_code
AND mcstl.category_set_name = 'WSH_COMMODITY_CODE';
SELECT basis
FROM fte_lane_commodities
WHERE lane_id = c_lane_id
AND commodity_catg_id = c_category_id;
SELECT delivery_id
FROM wsh_delivery_legs
WHERE delivery_leg_id = c_leg_id;
SELECT count(delivery_detail_id) FROM wsh_delivery_assignments
WHERE 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 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;
updated_adjusted_unit_price NUMBER,
line_unit_price NUMBER,
percent_price NUMBER
);
--Delete this charge for the consol LPN
x_freight_cost_main_charge.DELETE(i);
--Delete this charge for the consol LPN
x_freight_cost_temp_charge.DELETE(i);
x_fc_main_update_rows IN OUT NOCOPY Freight_Cost_main_Tab_Type,
x_freight_cost_temp_price IN OUT NOCOPY Freight_Cost_temp_Tab_Type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_child_dleg_summaries DBMS_UTILITY.NUMBER_ARRAY;
x_fc_main_update_rows(l_existing_dlegs(i)).total_amount:=l_child_dleg_summaries(i);
x_fc_main_update_rows(l_existing_dlegs(i)).unit_amount:=l_child_dleg_summaries(i);
x_fc_main_update_rows(x_fc_main_update_rows.LAST+1):=l_freight_cost_main_price;
l_existing_LPN DBMS_UTILITY.NUMBER_ARRAY;--If it is required to update existing LPN summaries
p_fc_main_update_rows IN Freight_Cost_Main_Tab_Type, -- For update of SUMMARY records
x_child_details_rated OUT NOCOPY VARCHAR2,
x_consol_LPNs_rated OUT NOCOPY VARCHAR2,
x_child_dlegs_rated OUT NOCOPY VARCHAR2,
x_parent_dlegs_rated OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_child_detail_rated DBMS_UTILITY.NUMBER_ARRAY;
l_child_detail_rated.DELETE;
l_consol_LPN_rated.DELETE;
l_child_dlegs_rated.DELETE;
l_parent_dlegs_rated.DELETE;
i:=p_fc_main_update_rows.FIRST;
IF ((p_fc_main_update_rows(i).delivery_detail_id IS NULL)
AND (p_fc_main_update_rows(i).delivery_leg_id IS NOT NULL)
AND (p_child_dlegs.EXISTS(p_fc_main_update_rows(i).delivery_leg_id)))
THEN
l_child_dlegs_rated(p_fc_main_update_rows(i).delivery_leg_id):=1;
IF ((p_fc_main_update_rows(i).delivery_detail_id IS NULL)
AND (p_fc_main_update_rows(i).delivery_leg_id IS NOT NULL)
AND (p_parent_dlegs.EXISTS(p_fc_main_update_rows(i).delivery_leg_id)))
THEN
l_parent_dlegs_rated(p_fc_main_update_rows(i).delivery_leg_id):=1;
i:=p_fc_main_update_rows.NEXT(i);
x_fc_main_update_rows IN OUT NOCOPY Freight_Cost_Main_Tab_Type, -- For update of SUMMARY records
x_return_status OUT NOCOPY VARCHAR2 ) IS
l_parent_deliveries DBMS_UTILITY.NUMBER_ARRAY;
p_fc_main_update_rows=>x_fc_main_update_rows,
x_child_details_rated=>l_child_details_rated,
x_consol_LPNs_rated=>l_consol_LPNs_rated,
x_child_dlegs_rated=>l_child_dlegs_rated,
x_parent_dlegs_rated=>l_parent_dlegs_rated,
x_return_status=>l_return_status);
x_freight_cost_main_price=>x_fc_main_update_rows,
x_freight_cost_temp_price=>x_freight_cost_temp_price,
x_fc_main_update_rows=>x_fc_main_update_rows,
x_return_status=>l_return_status);
x_freight_cost_main_price=>x_fc_main_update_rows,
x_freight_cost_temp_price=>x_freight_cost_temp_price,
x_return_status=>l_return_status);
SELECT count(delivery_leg_id) FROM wsh_delivery_legs
WHERE delivery_id = c_delivery_id;
SELECT structure_id
FROM mtl_category_sets mcs, mtl_category_sets_tl mcst
WHERE mcs.category_set_id = mcst.category_set_id
AND mcst.category_set_name='WSH_COMMODITY_CODE'
AND mcst.language='US';
SELECT category_id
FROM mtl_categories_b
WHERE segment1=c_classification_code
AND segment4='Y'
AND structure_id=c_structure_id;
SELECT t.trip_id, t.mode_of_transport
FROM wsh_delivery_legs dl,
wsh_trip_stops s,
wsh_trips t
WHERE dl.delivery_leg_id =c_dleg_id AND
dl.pick_up_stop_id=s.stop_id AND
s.trip_id=t.trip_id ;
select t.mode_of_transport
FROM wsh_trips t
WHERE t.trip_id=c_trip_id;
SELECT fte_freight_costs_temp_s.nextval
FROM sys.dual;
INSERT INTO fte_freight_costs_temp(
freight_cost_id,
freight_cost_type_id,
charge_unit_value,
unit_amount,
calculation_method,
uom,
quantity,
total_amount,
currency_code,
line_type_code,
charge_source_code,
estimated_flag,
comparison_request_id,
lane_id,
schedule_id,
moved_to_main_flag,
service_type_code, -- bug2741467
conversion_date,
conversion_rate,
conversion_type_code,
trip_id,
stop_id,
delivery_id,
delivery_leg_id,
delivery_detail_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
commodity_category_id,
billable_quantity,
billable_uom,
billable_basis
) VALUES (
x_freight_cost_temp_id,
p_freight_cost_temp_info.freight_cost_type_id,
p_freight_cost_temp_info.charge_unit_value,
p_freight_cost_temp_info.unit_amount,
p_freight_cost_temp_info.calculation_method,
p_freight_cost_temp_info.uom,
p_freight_cost_temp_info.quantity,
p_freight_cost_temp_info.total_amount,
p_freight_cost_temp_info.currency_code,
p_freight_cost_temp_info.line_type_code,
p_freight_cost_temp_info.charge_source_code,
p_freight_cost_temp_info.estimated_flag,
p_freight_cost_temp_info.comparison_request_id,
p_freight_cost_temp_info.lane_id,
p_freight_cost_temp_info.schedule_id,
p_freight_cost_temp_info.moved_to_main_flag,
p_freight_cost_temp_info.service_type_code,
p_freight_cost_temp_info.conversion_date,
p_freight_cost_temp_info.conversion_rate,
p_freight_cost_temp_info.conversion_type_code,
p_freight_cost_temp_info.trip_id,
p_freight_cost_temp_info.stop_id,
p_freight_cost_temp_info.delivery_id,
p_freight_cost_temp_info.delivery_leg_id,
p_freight_cost_temp_info.delivery_detail_id,
p_freight_cost_temp_info.attribute_category,
p_freight_cost_temp_info.attribute1,
p_freight_cost_temp_info.attribute2,
p_freight_cost_temp_info.attribute3,
p_freight_cost_temp_info.attribute4,
p_freight_cost_temp_info.attribute5,
p_freight_cost_temp_info.attribute6,
p_freight_cost_temp_info.attribute7,
p_freight_cost_temp_info.attribute8,
p_freight_cost_temp_info.attribute9,
p_freight_cost_temp_info.attribute10,
p_freight_cost_temp_info.attribute11,
p_freight_cost_temp_info.attribute12,
p_freight_cost_temp_info.attribute13,
p_freight_cost_temp_info.attribute14,
p_freight_cost_temp_info.attribute15,
p_freight_cost_temp_info.creation_date,
p_freight_cost_temp_info.created_by,
p_freight_cost_temp_info.last_update_date,
p_freight_cost_temp_info.last_updated_by,
p_freight_cost_temp_info.last_update_login,
p_freight_cost_temp_info.program_application_id,
p_freight_cost_temp_info.program_id,
p_freight_cost_temp_info.program_update_date,
p_freight_cost_temp_info.request_id,
p_freight_cost_temp_info.commodity_category_id,
p_freight_cost_temp_info.billable_quantity,
p_freight_cost_temp_info.billable_uom,
p_freight_cost_temp_info.billable_basis
);
SELECT freight_cost_type_id
FROM WSH_FREIGHT_COST_TYPES
WHERE freight_cost_type_code = p_line_type_code;
SELECT freight_cost_type_id
FROM WSH_FREIGHT_COST_TYPES
WHERE freight_cost_type_code = p_line_type_code
AND name = p_charge_subtype_code;
x_container_summary.DELETE(p_delivery_detail_id); -- No summary record for this one
END IF; -- No new fc records were inserted
x_pricing_engine_input.DELETE(l_instance_output(x_pricing_engine_input(x_qp_output_line_rows(i).line_index).instance_index).input_index);
FTE_QP_ENGINE.delete_lines (
p_line_index => l_instance_output(x_pricing_engine_input(x_qp_output_line_rows(i).line_index).instance_index).input_index,
x_qp_output_line_rows => x_qp_output_line_rows ,
x_qp_output_detail_rows => x_qp_output_line_details,
x_return_status => l_return_status);
FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'Highest current higher delete_lines ');
raise FTE_FREIGHT_PRICING_UTIL.g_delete_qpline_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_INF,'Deleted QP output : '||l_instance_output(x_pricing_engine_input(x_qp_output_line_rows(i).line_index).instance_index).input_index);
x_pricing_engine_input.DELETE(x_qp_output_line_rows(i).line_index);
FTE_QP_ENGINE.delete_lines (
p_line_index => i,
x_qp_output_line_rows => x_qp_output_line_rows ,
x_qp_output_detail_rows => x_qp_output_line_details,
x_return_status => l_return_status);
FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'Highest current not higher delete_lines');
raise FTE_FREIGHT_PRICING_UTIL.g_delete_qpline_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_INF,'Deleted QP output : '||i);
WHEN FTE_FREIGHT_PRICING_UTIL.g_delete_qpline_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('resolve_pricing_objective',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_delete_qpline_failed');
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'delete_qpline_failed failed ');
x_fc_main_update_rows OUT NOCOPY Freight_Cost_Main_Tab_Type, -- For update of SUMMARY records
x_summary_lanesched_price OUT NOCOPY NUMBER, -- Only in case of 'T'
x_summary_lanesched_price_uom OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2 )
IS
CURSOR c_get_fc_id(p_dleg_id IN NUMBER) IS
SELECT freight_cost_id,created_by,creation_date
FROM wsh_freight_costs
WHERE delivery_leg_id = p_dleg_id
AND delivery_detail_id IS NULL
AND line_type_code = 'SUMMARY';
SELECT delivery_id
FROM wsh_delivery_legs
WHERE delivery_leg_id = c_dleg_id;
x_fc_main_update_rows(l_main_price_index).freight_cost_type_id := l_freight_cost_type_id;
x_fc_main_update_rows(l_main_price_index).freight_cost_id := l_freight_cost_id;
x_fc_main_update_rows(l_main_price_index).created_by := l_created_by;
x_fc_main_update_rows(l_main_price_index).creation_date := l_creation_date;
x_fc_main_update_rows(l_main_price_index).charge_source_code := 'PRICING_ENGINE';
x_fc_main_update_rows(l_main_price_index).line_type_code := 'SUMMARY';
x_fc_main_update_rows(l_main_price_index).delivery_leg_id := l;
x_fc_main_update_rows(l_main_price_index).currency_code := l_currency_code;
x_fc_main_update_rows(l_main_price_index).total_amount := round(l_fc_dleg_rows(l).summary_amount,2);
x_fc_main_update_rows(l_main_price_index).unit_amount := round(l_fc_dleg_rows(l).summary_amount,2);
x_fc_main_update_rows(l_main_price_index).delivery_id := l_dlv_id;
l_unmatched_categ.DELETE(p_rolled_up_lines(j).category_id);
l_unmatched_basis.DELETE(p_rolled_up_lines(j).rate_basis);
l_unmatched_categories.DELETE(p_rolled_up_lines(j).category_id);
g_rolledup_lines.DELETE;
l_rolledup_category_rows.DELETE;
l_rolledup_category_basis.DELETE;
l_rolledup_lines.DELETE;
l_rolledup_lines.DELETE; -- bug 2779306
SELECT currency_code
FROM wsh_carriers
WHERE carrier_id = p_carrier_id;
SELECT fl.lane_id,fl.carrier_id,flrc.list_header_id pricelist_id,
fl.mode_of_transportation_code,fl.origin_id,fl.destination_id,
fl.basis,fl.commodity_catg_id,fl.service_type_code,fl.comm_fc_class_code
FROM fte_lanes fl, fte_lane_rate_charts flrc
WHERE fl.lane_id = c_lane_id
AND fl.lane_id = flrc.lane_id
AND (flrc.start_date_active is null OR flrc.start_date_active <= p_ship_date)
AND (flrc.end_date_active is null OR flrc.end_date_active > p_ship_date);
SELECT fl.lane_id,fl.carrier_id,flrc.list_header_id pricelist_id,
fl.mode_of_transportation_code,fl.origin_id,fl.destination_id,
fl.basis,fl.commodity_catg_id,fl.service_type_code,fl.comm_fc_class_code
FROM fte_lanes fl, wsh_trips wt, fte_lane_rate_charts flrc
WHERE fl.lane_id = wt.lane_id
AND wt.trip_id = c_wsh_trip_id
AND fl.lane_id = flrc.lane_id
AND (flrc.start_date_active is null OR flrc.start_date_active <= p_ship_date)
AND (flrc.end_date_active is null OR flrc.end_date_active > p_ship_date);
SELECT fl.lane_id,fl.carrier_id,flrc.list_header_id pricelist_id,
fl.mode_of_transportation_code,fl.origin_id,fl.destination_id,
fl.basis,fl.commodity_catg_id,fl.service_type_code,fl.comm_fc_class_code
FROM fte_lanes fl, fte_schedules fs, fte_lane_rate_charts flrc
WHERE fl.lane_id = fs.lane_id
AND fs.schedules_id = c_schedule_id
AND fl.lane_id = flrc.lane_id
AND (flrc.start_date_active is null OR flrc.start_date_active <= p_ship_date)
AND (flrc.end_date_active is null OR flrc.end_date_active > p_ship_date);
SELECT service_level
FROM wsh_trips
WHERE trip_id = c_wsh_trip_id;
l_fc_main_update_rows Freight_Cost_Main_Tab_Type;
x_fc_main_update_rows => l_fc_main_update_rows,
x_summary_lanesched_price => x_summary_lanesched_price,
x_summary_lanesched_price_uom => x_summary_lanesched_price_uom,
x_return_status => l_return_status ) ;
x_fc_main_update_rows => l_fc_main_update_rows,
x_summary_lanesched_price => x_summary_lanesched_price,
x_summary_lanesched_price_uom => x_summary_lanesched_price_uom,
x_return_status => l_return_status ) ;
x_fc_main_update_rows => l_fc_main_update_rows, -- For update of SUMMARY records
x_summary_lanesched_price => x_summary_lanesched_price, -- Only in case of 'T'
x_summary_lanesched_price_uom => x_summary_lanesched_price_uom,
x_return_status => l_return_status ) ;
x_fc_main_update_rows => l_fc_main_update_rows, -- For update of SUMMARY records
x_return_status => l_return_status ) ;
l_freight_cost_temp_price(k).last_update_date := sysdate;
l_freight_cost_temp_price(k).last_updated_by := FND_GLOBAL.USER_ID;
l_freight_cost_temp_price(k).last_update_login := FND_GLOBAL.LOGIN_ID;
l_freight_cost_temp_price(k).lane_id := p_lane_id; -- Need to insert schedule_id/lane_id
l_freight_cost_temp_charge(n).last_update_date := sysdate;
l_freight_cost_temp_charge(n).last_updated_by := FND_GLOBAL.USER_ID;
l_freight_cost_temp_charge(n).last_update_login := FND_GLOBAL.LOGIN_ID;
l_freight_cost_temp_charge(n).lane_id := p_lane_id; -- Need to insert schedule_id/lane_id
l := l_fc_main_update_rows.FIRST;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Update row delivery leg id : '||l_fc_main_update_rows(l).delivery_leg_id);
l_fc_main_update_rows(l).last_update_date := sysdate;
l_fc_main_update_rows(l).last_updated_by := FND_GLOBAL.USER_ID;
l_fc_main_update_rows(l).last_update_login := FND_GLOBAL.LOGIN_ID;
WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost(
p_rowid => l_rowid,
p_freight_cost_info => l_fc_main_update_rows(l),
x_return_status => l_return_status);
FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'Update_Freight_Cost');
raise FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,'Update_Freight_Cost returned warning ');
EXIT WHEN l=l_fc_main_update_rows.LAST;
l := l_fc_main_update_rows.NEXT(l);
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Done Update_Freight_Cost ');
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_INF,'No Main fc update record got created');
l_freight_cost_main_price(m).last_update_date := sysdate;
l_freight_cost_main_price(m).last_updated_by := FND_GLOBAL.USER_ID;
l_freight_cost_main_price(m).last_update_login := FND_GLOBAL.LOGIN_ID;
l_freight_cost_main_charge(o).last_update_date := sysdate;
l_freight_cost_main_charge(o).last_updated_by := FND_GLOBAL.USER_ID;
l_freight_cost_main_charge(o).last_update_login := FND_GLOBAL.LOGIN_ID;
WHEN FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('shipment_pricing',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_update_freight_cost_failed');
SELECT unit_length,unit_width,unit_height,dimension_uom_code,
unit_weight,weight_uom_code,unit_volume,volume_uom_code
FROM mtl_system_items
WHERE inventory_item_id = c_inv_item_id;
SELECT uomw.uom_code weight_uom, uomv.uom_code volume_uom
FROM mtl_units_of_measure uomw,
mtl_units_of_measure uomv,
wsh_shipping_parameters wsp
WHERE wsp.weight_uom_class = uomw.uom_class
AND wsp.volume_uom_class = uomv.uom_class
AND uomw.base_uom_flag='Y'
AND uomv.base_uom_flag='Y'
AND wsp.organization_id = c_organization_id;
SELECT DISTINCT wfct.freight_cost_type_code,wfct.freight_cost_type_id
FROM wsh_freight_cost_types wfct,
wsh_freight_costs wfc
WHERE wfc.delivery_detail_id = c_delivery_detail_id
AND wfc.charge_source_code = 'REQUESTED'
AND wfc.freight_cost_type_id = wfct.freight_cost_type_id;
SELECT nvl(value_from, 'NONE')
FROM wsh_trips a, fte_prc_parameters b
WHERE a.trip_id = p_segment_id
AND a.lane_id = b.lane_id
AND b.parameter_id = 1;
SELECT wt.trip_id
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 = p_delivery_leg_id;
SELECT delivery_id
FROM wsh_delivery_legs
WHERE delivery_leg_id = p_delivery_leg_id;
SELECT freight_cost_id
FROM wsh_freight_costs
WHERE delivery_leg_id = p_delivery_leg_id
AND delivery_detail_id IS NULL
AND line_type_code = 'SUMMARY';
PROCEDURE delete_invalid_fc_recs (
p_segment_id IN NUMBER DEFAULT NULL,
p_delivery_leg_id IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2 )
IS
l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
SELECT wfc.freight_cost_id
FROM wsh_freight_costs wfc,
wsh_delivery_legs dl ,
wsh_trip_stops s
WHERE wfc.delivery_leg_id = dl.delivery_leg_id
and dl.pick_up_stop_id=s.stop_id
and s.trip_id=c_segment_id
and wfc.charge_source_code='PRICING_ENGINE'
FOR UPDATE OF wfc.freight_cost_id NOWAIT;
SELECT wfc.freight_cost_id
FROM wsh_freight_costs wfc
WHERE wfc.delivery_leg_id = c_delivery_leg_id
AND charge_source_code = 'PRICING_ENGINE'
FOR UPDATE NOWAIT;
FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'delete_invalid_fc_recs','start');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'delete_invalid_fc_recs');
DELETE
FROM wsh_freight_costs wfc
WHERE wfc.freight_cost_id = l_wfc_detail_ids(i)
AND (line_type_code <> 'SUMMARY'
OR (line_type_code = 'SUMMARY' AND delivery_detail_id IS NOT NULL));
UPDATE wsh_freight_costs wfc
SET unit_amount=NULL,
total_amount=NULL,
currency_code=NULL
WHERE wfc.freight_cost_id=l_wfc_detail_ids(i)
AND line_type_code = 'SUMMARY'
AND delivery_detail_id IS NULL;
DELETE
FROM wsh_freight_costs wfc
WHERE wfc.freight_cost_id=l_wfc_dleg_ids(i)
AND (line_type_code <> 'SUMMARY'
OR (line_type_code = 'SUMMARY' AND delivery_detail_id IS NOT NULL));
UPDATE wsh_freight_costs wfc
SET unit_amount=NULL,
total_amount=NULL,
currency_code=NULL
WHERE wfc.freight_cost_id=l_wfc_dleg_ids(i)
AND line_type_code = 'SUMMARY'
AND delivery_detail_id IS NULL;
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'delete_invalid_fc_recs');
FTE_FREIGHT_PRICING_UTIL.set_exception('delete_invalid_fc_recs',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'delete_invalid_fc_recs');
END delete_invalid_fc_recs;
UPDATE wsh_delivery_legs
SET reprice_required = 'N'
WHERE delivery_leg_id IN (
SELECT wdl.delivery_leg_id
FROM wsh_delivery_legs wdl,
wsh_trips wt,
wsh_trip_stops wts1,
wsh_trip_stops wts2
WHERE wt.trip_id = wts1.trip_id
AND wt.trip_id = wts2.trip_id
AND wts1.stop_id = wdl.pick_up_stop_id
AND wts2.stop_id = wdl.drop_off_stop_id
AND wt.trip_id = p_segment_id );
UPDATE wsh_delivery_legs
SET reprice_required = 'N'
WHERE delivery_leg_id = p_delivery_leg_id;
Select wdd.delivery_detail_id,
wda.delivery_id,
wdl.delivery_leg_id,
nvl(wdl.reprice_required,'N') as reprice_required, -- Added AG 05/10
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
wda.type,
wda.parent_delivery_id,
wdl.parent_delivery_leg_id
from wsh_delivery_details wdd, wsh_delivery_assignments wda,
wsh_new_deliveries wd,
wsh_delivery_legs wdl, wsh_trip_stops wts1, wsh_trip_stops wts2,wsh_trips wt
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 wdl.reprice_required = 'Y' -- Not required AG 05/10
and wts1.trip_id = wt.trip_id
and wts2.trip_id = wt.trip_id
and (wda.type IS null OR wda.type <> 'O')--MDC
and wt.trip_id = c_trip_id;
Select wdd.delivery_detail_id,
wda.delivery_id,
wdl.delivery_leg_id,
nvl(wdl.reprice_required,'N') as reprice_required, -- Added AG 05/10
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
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 ((wdl.reprice_required = 'Y' AND p_check_reprice_flag = 'Y') OR (p_check_reprice_flag = 'N'))
and (wda.type IS null OR wda.type <> 'O')--MDC
and wdl.delivery_leg_id = c_delivery_leg_id;
SELECT 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 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);
g_shipment_line_rows.DELETE;
delete_invalid_fc_recs (
p_segment_id => p_segment_id,
x_return_status => l_return_status ) ;
FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'segment:delete_invalid_fc_recs ');
raise FTE_FREIGHT_PRICING_UTIL.g_delete_invalid_fc_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_INF,p_segment_id||' : Existing freight cost records deleted ');
l_first_level_rows.delete;
l_first_level_charges.delete;
delete_invalid_fc_recs (
p_delivery_leg_id => l_dlvy(j),
x_return_status => l_return_status ) ;
FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'dleg:consolidate:N:delete_invalid_fc_recs ');
raise FTE_FREIGHT_PRICING_UTIL.g_delete_invalid_fc_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_INF,l_dlvy(j)||' : Existing freight cost records deleted ');
delete_invalid_fc_recs (
p_segment_id => l_segment_id,
x_return_status => l_return_status ) ;
FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'dleg:consolidate:Y:delete_invalid_fc_recs ');
raise FTE_FREIGHT_PRICING_UTIL.g_delete_invalid_fc_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_INF,l_segment_id||' : Existing freight cost records deleted ');
delete_invalid_fc_recs (
p_delivery_leg_id => p_delivery_leg_id,
x_return_status => l_return_status ) ;
FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'dleg:consolidate:N:delete_invalid_fc_recs ');
raise FTE_FREIGHT_PRICING_UTIL.g_delete_invalid_fc_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_INF,p_delivery_leg_id||' : Existing freight cost records deleted ');
WHEN FTE_FREIGHT_PRICING_UTIL.g_delete_invalid_fc_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception('shipment_price_consolidate',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_delete_invalid_fc_failed');
SELECT fwt.wsh_trip_id
FROM fte_trips ft, fte_wsh_trips fwt
WHERE ft.fte_trip_id = fwt.fte_trip_id
AND ft.fte_trip_id = p_fte_trip_id;
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_id = p_delivery_id;
SELECT wdl.delivery_id,delivery_leg_id,wnd.name
FROM wsh_delivery_legs wdl, wsh_new_deliveries wnd
WHERE wdl.delivery_id= c_delivery_id
AND wnd.delivery_id = wdl.delivery_id;
SELECT DISTINCT wdl.delivery_id,wts1.trip_id ,wdl.delivery_leg_id,wnd.name
FROM wsh_delivery_legs wdl ,
wsh_trip_stops wts1,
wsh_trip_stops wts2,
wsh_trips wt,
wsh_new_deliveries wnd
WHERE wdl.pick_up_stop_id = wts1.stop_id
AND wdl.drop_off_stop_id = wts2.stop_id
AND wdl.delivery_leg_id = c_dleg_id
AND wt.trip_id = wts1.trip_id
AND wnd.delivery_id = wdl.delivery_id;
SELECT 'X' FROM WSH_NEW_DELIVERIES
WHERE ( shipment_direction IN ('O','IO') AND status_code = 'OP')
OR ( shipment_direction IN ('D','I') )
AND delivery_id = c_delivery_id ;
SELECT total_amount FROM wsh_freight_costs
WHERE line_type_code='SUMMARY'
AND delivery_detail_id is null
AND freight_cost_type_id is not null
AND 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 shipments_type_flag
FROM wsh_trips
WHERE trip_id = c_trip_id;
SELECT meaning
FROM wsh_lookups
WHERE lookup_code = 'CL'
AND lookup_type = 'TRIP_STATUS';
FND_MSG_PUB.Delete_Msg ( p_msg_index => null);
PROCEDURE delete_fc_temp_pvt (
p_request_id IN NUMBER, -- Comparison Request ID to move to main
p_initialized IN BOOLEAN DEFAULT TRUE,
p_lane_id IN NUMBER DEFAULT NULL,
p_schedule_id IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(1);
FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'delete_fc_temp_pvt','start');
DELETE
FROM fte_freight_costs_temp
WHERE comparison_request_id = p_request_id
--AND nvl(lane_id,-9999) <> nvl(p_lane_id,-19999)
AND nvl(lane_id,-9999) <> nvl(decode(p_schedule_id,NULL,p_lane_id,NULL),-19999)
AND nvl(schedule_id,-9999) <> nvl(p_schedule_id,-19999);
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'delete_fc_temp_pvt');
FTE_FREIGHT_PRICING_UTIL.set_exception('delete_fc_temp_pvt',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'delete_fc_temp_pvt');
END delete_fc_temp_pvt;
l_update_rowid VARCHAR2(30);
l_freight_cost_main_row.LAST_UPDATE_DATE := SYSDATE;
l_freight_cost_main_row.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_freight_cost_main_row.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Calling WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost...');
WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost(
p_rowid => l_update_rowid,
p_freight_cost_info => l_freight_cost_main_row,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'After Update_Freight_Cost ');
l_freight_cost_main_charge.LAST_UPDATE_DATE := SYSDATE;
l_freight_cost_main_charge.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_freight_cost_main_charge.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
WHEN FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_update_freight_cost_failed');
l_update_rowid VARCHAR2(30);
l_freight_cost_main_row.LAST_UPDATE_DATE := SYSDATE;
l_freight_cost_main_row.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_freight_cost_main_row.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Calling WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost...');
WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost(
p_rowid => l_update_rowid,
p_freight_cost_info => l_freight_cost_main_row,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'After Update_Freight_Cost ');
WHEN FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_update_freight_cost_failed');
SELECT *
FROM FTE_FREIGHT_COSTS_TEMP
WHERE comparison_request_id = p_request_id
--AND nvl(lane_id,-9999) = nvl(p_lane_id,-9999)
AND nvl(lane_id,-9999) = nvl(decode(p_schedule_id,NULL,p_lane_id,NULL),-9999)
AND nvl(schedule_id,-9999) = nvl(p_schedule_id,-9999)
AND moved_to_main_flag = 'N'
AND nvl(service_type_code,'X') = nvl(p_service_type_code,'X')
AND line_type_code NOT IN ('CHARGE','DISCOUNT');
SELECT *
FROM FTE_FREIGHT_COSTS_TEMP
WHERE comparison_request_id = p_request_id
--AND nvl(lane_id,-9999) = nvl(p_lane_id,-9999)
AND nvl(lane_id,-9999) = nvl(decode(p_schedule_id,NULL,p_lane_id,NULL),-9999)
AND nvl(schedule_id,-9999) = nvl(p_schedule_id,-9999)
AND moved_to_main_flag = 'N'
AND nvl(service_type_code,'X') = nvl(p_service_type_code,'X')
AND line_type_code IN ('CHARGE','DISCOUNT');
SELECT mode_of_transportation_code
FROM fte_lanes
WHERE lane_id = c_lane_id;
SELECT mode_of_transportation_code
FROM fte_lanes l, fte_schedules s
WHERE l.lane_id = s.lane_id
AND s.schedules_id = c_schedule_id;
l_update_rowid VARCHAR2(30);
-- When does estimated flag get updated ?
l_freight_cost_main_row.FREIGHT_COST_TYPE_ID := l_freight_cost_temp_row.FREIGHT_COST_TYPE_ID;
l_freight_cost_main_row.LAST_UPDATE_DATE := SYSDATE;
l_freight_cost_main_row.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_freight_cost_main_row.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
--l_freight_cost_main_row.PROGRAM_UPDATE_DATE := SYSDATE;
-- To update the delivery leg summary row
-- Get the delivery leg id as input and get the freight cost id for that
-- The lane level summary amount becomes the delivery leg level summary amount
l_freight_cost_main_row.FREIGHT_COST_ID := get_fc_id_from_dleg(l_freight_cost_main_row.DELIVERY_LEG_ID);
WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost(
p_rowid => l_update_rowid,
p_freight_cost_info => l_freight_cost_main_row,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'After Update_Freight_Cost ');
l_freight_cost_main_charge.LAST_UPDATE_DATE := SYSDATE;
l_freight_cost_main_charge.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_freight_cost_main_charge.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
--l_freight_cost_main_charge.PROGRAM_UPDATE_DATE := SYSDATE;
-- enclosed update statement fails
UPDATE fte_freight_costs_temp
SET moved_to_main_flag = 'Y'
WHERE comparison_request_id = p_request_id
--AND nvl(lane_id,-9999) = nvl(p_lane_id,-9999)
AND nvl(lane_id,-9999) = nvl(decode(p_schedule_id,NULL,p_lane_id,NULL),-9999)
AND nvl(schedule_id,-9999) = nvl(p_schedule_id,-9999)
AND nvl(service_type_code,'X') = nvl(p_service_type_code,'X')
AND moved_to_main_flag = 'N';
delete_fc_temp_pvt (
p_request_id => p_request_id, -- Comparison Request ID to move to main
p_lane_id => p_lane_id,
p_schedule_id => p_schedule_id,
x_return_status => l_return_status);
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'delete_fc_temp failed ');
raise FTE_FREIGHT_PRICING_UTIL.g_delete_fc_temp_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'delete_fc_temp successful ');
WHEN FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exit_exception('Move_fc_temp_to_main','g_update_freight_cost_failed');
WHEN FTE_FREIGHT_PRICING_UTIL.g_delete_fc_temp_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
FTE_FREIGHT_PRICING_UTIL.set_exit_exception('Move_fc_temp_to_main','g_delete_fc_temp_failed');
SELECT *
FROM FTE_FREIGHT_COSTS_TEMP
WHERE comparison_request_id = p_request_id
--AND nvl(lane_id,-9999) = nvl(p_lane_id,-9999)
AND nvl(lane_id,-9999) = nvl(decode(p_schedule_id,NULL,p_lane_id,NULL),-9999)
AND nvl(schedule_id,-9999) = nvl(p_schedule_id,-9999)
AND moved_to_main_flag = 'N'
AND nvl(service_type_code,'X') = nvl(p_service_type_code,'X')
AND line_type_code NOT IN ('CHARGE','DISCOUNT');
SELECT *
FROM FTE_FREIGHT_COSTS_TEMP
WHERE comparison_request_id = p_request_id
--AND nvl(lane_id,-9999) = nvl(p_lane_id,-9999)
AND nvl(lane_id,-9999) = nvl(decode(p_schedule_id,NULL,p_lane_id,NULL),-9999)
AND nvl(schedule_id,-9999) = nvl(p_schedule_id,-9999)
AND moved_to_main_flag = 'N'
AND nvl(service_type_code,'X') = nvl(p_service_type_code,'X')
AND line_type_code IN ('CHARGE','DISCOUNT');
SELECT dl.delivery_leg_id,dl.delivery_id
FROM wsh_delivery_legs dl ,
wsh_trip_stops s
WHERE dl.pick_up_stop_id = s.stop_id
and s.trip_id=c_trip_id;
l_update_rowid VARCHAR2(30);
l_dleg_ids.delete;
l_freight_cost_main_row.LAST_UPDATE_DATE := SYSDATE;
l_freight_cost_main_row.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_freight_cost_main_row.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost(
p_rowid => l_update_rowid,
p_freight_cost_info => l_freight_cost_main_row,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'After Update_Freight_Cost ');
l_freight_cost_main_charge.LAST_UPDATE_DATE := SYSDATE;
l_freight_cost_main_charge.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_freight_cost_main_charge.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
UPDATE fte_freight_costs_temp
SET moved_to_main_flag = 'Y'
WHERE comparison_request_id = p_request_id
--AND nvl(lane_id,-9999) = nvl(p_lane_id,-9999)
AND nvl(lane_id,-9999) = nvl(decode(p_schedule_id,NULL,p_lane_id,NULL),-9999)
AND nvl(schedule_id,-9999) = nvl(p_schedule_id,-9999)
AND nvl(service_type_code,'X') = nvl(p_service_type_code,'X')
AND moved_to_main_flag = 'N';
delete_fc_temp_pvt (
p_request_id => p_request_id, -- Comparison Request ID to move to main
p_lane_id => p_lane_id,
p_schedule_id => p_schedule_id,
x_return_status => l_return_status);
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'delete_fc_temp failed ');
raise FTE_FREIGHT_PRICING_UTIL.g_delete_fc_temp_failed;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'delete_fc_temp successful ');
WHEN FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exit_exception('Move_fc_temp_to_main','g_update_freight_cost_failed');
WHEN FTE_FREIGHT_PRICING_UTIL.g_delete_fc_temp_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
FTE_FREIGHT_PRICING_UTIL.set_exit_exception('Move_fc_temp_to_main','g_delete_fc_temp_failed');
PROCEDURE delete_fc_temp (
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_true,
p_request_id IN NUMBER, -- Comparison Request ID to move to main
x_return_status OUT NOCOPY VARCHAR2)
IS
l_initialized BOOLEAN := TRUE;
SAVEPOINT DELETE_FC_TEMP;
FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'delete_fc_temp','start');
delete_fc_temp_pvt (
p_request_id => p_request_id, -- Comparison Request ID to move to main
p_initialized => l_initialized,
x_return_status => l_return_status);
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'delete_fc_temp failed ');
ROLLBACK TO DELETE_FC_TEMP;
FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'delete_fc_temp successful ');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'delete_fc_temp');
FTE_FREIGHT_PRICING_UTIL.set_exit_exception('delete_fc_temp','g_others');
FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'delete_fc_temp');
END delete_fc_temp;
Select wdd.delivery_detail_id,
wda.delivery_id,
NULL, -- No delivery leg here
NULL, -- No reprice_required flag here
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
wda.type,
wda.parent_delivery_id,
NULL--wdl.parent_delivery_leg_id
from wsh_delivery_details wdd, wsh_delivery_assignments wda,
wsh_new_deliveries wd
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = c_delivery_id
and (wda.type IS null OR wda.type <> 'O')--MDC
and wda.delivery_id = wd.delivery_id;
Select wdd.delivery_detail_id,
wda.delivery_id,
wdl.delivery_leg_id,
nvl(wdl.reprice_required,'N') as reprice_required, -- Added AG 05/10
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
wda.type,
wda.parent_delivery_id,
wdl.parent_delivery_leg_id
from wsh_delivery_details wdd, wsh_delivery_assignments wda,
wsh_new_deliveries wd,
wsh_delivery_legs wdl, wsh_trip_stops wts1, wsh_trip_stops wts2,wsh_trips wt
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 wts2.trip_id = wt.trip_id
and (wda.type IS null OR wda.type <> 'O')--MDC
and wt.trip_id = c_trip_id;
SELECT fte_pricing_comp_request_s.nextval
FROM sys.dual;
SELECT carrier_id
FROM fte_lanes
WHERE lane_id = c_lane_id;
g_shipment_line_rows.DELETE;
SELECT wdd.delivery_detail_id,
wdd.gross_weight ,
wdd.weight_uom_code
from wsh_delivery_details wdd, wsh_delivery_assignments wda
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.container_flag = 'N'
and (wda.type IS null OR wda.type <> 'O')--MDC
and wda.delivery_id = c_delivery_id;
SELECT weight_uom_code
FROM wsh_new_deliveries
WHERE delivery_id = c_delivery_id;
SELECT fte_pricing_comp_request_s.nextval
FROM sys.dual;