[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT parent_delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id = detail_id;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
START WITH parent_delivery_detail_id = cont_id
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
l_contents.delete;
g_container_lines.delete;
g_container_lines.delete;
g_traversed_containers.delete;
x_unassigned_cont.delete;
select 'N'
from wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
mtl_system_items msi
where wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.inventory_item_id = msi.inventory_item_id
and wdd.organization_id = msi.organization_id
and nvl(msi.shippable_item_flag,'Y') = 'N'
and wdd.container_flag = p_cnt_flag
and wda.delivery_id = p_del_id;
select 'Y'
from wsh_new_deliveries
where delivery_id=p_delid AND
planned_flag='F';
SELECT wnd.organization_id, wnd.planned_flag , wnd.initial_pickup_location_id
FROM wsh_new_deliveries wnd
WHERE wnd.delivery_id = p_delivery_id AND
wnd.status_code = 'OP' AND
wnd.planned_flag = 'N' AND
NVL(wnd.shipment_direction,'O') in ('O','IO');
SELECT wnd.organization_id, wnd.planned_flag
FROM wsh_new_deliveries wnd
WHERE wnd.delivery_id = p_delivery_id AND
wnd.status_code = 'OP' AND
NVL(wnd.shipment_direction,'O') in ('O','IO');
SELECT count(wdd.delivery_detail_id)
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id AND
wda.delivery_id = p_delivery_id AND
wda.delivery_id is not NULL AND
wdd.container_flag = 'N' AND
wdd.source_code = 'OE' AND
wdd.released_status in ('Y', 'C') AND
NVL(wdd.line_direction,'O') in ('O','IO');
SELECT wdd.released_status
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id AND
wda.delivery_id = p_delivery_id AND
wda.delivery_id is not NULL AND
wdd.container_flag = 'N' AND
wdd.source_code = 'OE' AND
NVL(wdd.line_direction,'O') in ('O','IO');
l_select_carrier_tbl WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
l_dels_to_update WSH_UTIL_CORE.Id_Tab_Type;
Select_Carrier_Err EXCEPTION;
l_select_carrier_tbl.delete;
l_auto_rate_tbl.delete;
l_dels_to_update.delete;
SELECT organization_id, planned_flag INTO l_organization_id, l_planned_flag
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_ids(i)
FOR UPDATE NOWAIT;
l_dels_to_update(l_dels_to_update.count+1) := p_delivery_ids(i);
l_select_carrier_tbl(l_select_carrier_tbl.count+1).delivery_id := p_delivery_ids(i);
l_select_carrier_tbl(l_select_carrier_tbl.count).organization_id := l_organization_id;
l_select_carrier_tbl(l_select_carrier_tbl.count).planned_flag:= 'Y';
l_select_carrier_tbl(l_select_carrier_tbl.count).status_code := 'OP';
SELECT planned_flag INTO l_planned_flag
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_ids(i) AND status_code = 'OP'
AND planned_flag = 'N'
FOR UPDATE NOWAIT;
l_dels_to_update(l_dels_to_update.count+1) := p_delivery_ids(i);
l_select_carrier_tbl(l_select_carrier_tbl.count+1).delivery_id := p_delivery_ids(i);
l_select_carrier_tbl(l_select_carrier_tbl.count).organization_id := l_organization_id;
l_select_carrier_tbl(l_select_carrier_tbl.count).planned_flag:= 'Y';
l_select_carrier_tbl(l_select_carrier_tbl.count).status_code := 'OP';
SELECT planned_flag INTO l_planned_flag
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_ids(i) AND status_code = 'OP'
AND planned_flag = 'N'
FOR UPDATE NOWAIT;
l_dels_to_update(l_dels_to_update.count+1) := p_delivery_ids(i);
l_select_carrier_tbl(l_select_carrier_tbl.count+1).delivery_id := p_delivery_ids(i);
l_select_carrier_tbl(l_select_carrier_tbl.count).organization_id := l_organization_id;
l_select_carrier_tbl(l_select_carrier_tbl.count).planned_flag:= 'Y';
l_select_carrier_tbl(l_select_carrier_tbl.count).status_code := 'OP';
SELECT planned_flag INTO l_planned_flag
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_ids(i) AND status_code = 'OP'
FOR UPDATE NOWAIT;
l_dels_to_update(l_dels_to_update.count+1) := p_delivery_ids(i);
l_select_carrier_tbl(l_select_carrier_tbl.count+1).delivery_id := p_delivery_ids(i);
l_select_carrier_tbl(l_select_carrier_tbl.count).organization_id := l_organization_id;
l_select_carrier_tbl(l_select_carrier_tbl.count).planned_flag:= 'Y';
l_select_carrier_tbl(l_select_carrier_tbl.count).status_code := l_status_code;
IF l_select_carrier_tbl.count > 0 THEN
IF l_debug_on THEN
wsh_debug_sv.logmsg(l_module_name, 'Call wsh_deliveries_grp.delivery_action for '|| to_char(l_select_carrier_tbl.count)||' deliveries');
l_action_prms.action_code := 'SELECT-CARRIER';
wsh_debug_sv.logmsg(l_module_name, 'Calling wsh_deliveries_grp.delivery_action SELECT-CARRIER with '||l_select_carrier_tbl.count||' deliveries');
p_rec_attr_tab => l_select_carrier_tbl,
x_delivery_out_rec => l_delivery_out_rec,
x_defaults_rec => l_defaults_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
raise Select_Carrier_Err;
IF l_dels_to_update.count > 0 THEN
FORALL i in l_dels_to_update.FIRST .. l_dels_to_update.LAST
update wsh_new_deliveries
set planned_flag = l_planned_flag_true --Bugfix 3778944
where delivery_id = l_dels_to_update(i);
WHEN Select_Carrier_Err THEN
rollback to before_adjust_planned_flag;
wsh_debug_sv.logmsg(l_module_name, 'Carrier Selection failed');
wsh_debug_sv.pop(l_module_name, 'EXCEPTION:Select_Carrier_Err');
select 'Y'
from wsh_new_deliveries
where delivery_id=p_delid AND
planned_flag='F';
UPDATE wsh_new_deliveries
SET planned_flag = l_planned_flag_false --bugfix 3778944
WHERE delivery_id = p_del_rows(i);
SELECT dg.delivery_leg_id,
dl.initial_pickup_location_id,
t.ship_method_code,
dl.organization_id,
t.name
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg,
wsh_trip_stops st,
wsh_trips t
WHERE dl.delivery_id = p_delivery_id AND
dl.delivery_id = dg.delivery_id AND
dg.pick_up_stop_id = st.stop_id AND
st.trip_id = t.trip_id;
SELECT hoi.org_information1 ledger_id
from hr_organization_information hoi
WHERE hoi.organization_id = l_org_id
AND hoi.org_information_context = 'Accounting Information';
SELECT ood.set_of_books_id INTO l_set_of_books_id
FROM org_organization_definitions ood
WHERE ood.organization_id = l_organization_id;
SELECT dl.initial_pickup_location_id,
dl.organization_id
FROM wsh_new_deliveries dl
WHERE dl.delivery_id = p_delivery_id;
SELECT pack_slip_required_flag
FROM wsh_shipping_parameters
WHERE organization_id = l_org_id;
SELECT packing_slip_number
FROM wsh_packing_slips_db_v
WHERE delivery_id = p_delivery_id;
SELECT hoi.org_information1 ledger_id
from hr_organization_information hoi
WHERE hoi.organization_id = l_org_id
AND hoi.org_information_context = 'Accounting Information';
SELECT ood.set_of_books_id INTO l_set_of_books_id
FROM org_organization_definitions ood
WHERE ood.organization_id = l_organization_id;
SELECT rs.name,
rs.report_set_id
FROM wsh_report_sets rs
WHERE rs.report_set_id = p_report_set_id;
select wts.trip_id
from wsh_delivery_legs wdl, wsh_trip_stops wts
where wdl.pick_up_stop_id=wts.stop_id
and wdl.delivery_id=l_delivery_id
and rownum=1;
SELECT meaning
FROM fnd_lookup_values_vl
WHERE lookup_code = x_ship_method_code
AND lookup_type = 'SHIP_METHOD'
AND view_application_id = 3;
SELECT wsc.name,
wsc.ship_confirm_rule_id,
wsc.ac_intransit_flag,
wsc.ac_close_trip_flag,
wsc.ac_bol_flag,
wsc.ac_defer_interface_flag,
wsc.report_set_id,
wsc.ship_method_code,
wsc.effective_end_date
FROM wsh_ship_confirm_rules wsc
WHERE wsc.ship_confirm_rule_id = p_ship_confirm_rule_id;
SELECT wt.ship_method_code
FROM wsh_new_deliveries del,
wsh_delivery_legs dlg,
wsh_trip_stops st,
wsh_trips wt
WHERE del.delivery_id = dlg.delivery_id
AND dlg.pick_up_stop_id = st.stop_id
AND del.initial_pickup_location_id = st.stop_location_id
AND st.trip_id = wt.trip_id
AND del.delivery_id = p_delivery_id
AND rownum < 3;
SELECT count(distinct wda.delivery_detail_id)
FROM wsh_delivery_assignments_v wda
WHERE wda.parent_delivery_detail_id is null
AND LEVEL > 1
CONNECT BY PRIOR wda.parent_delivery_detail_id = wda.delivery_detail_id
START WITH wda.delivery_id = l_delivery_id;
SELECT rs.name, rs.report_set_id
FROM wsh_shipping_parameters sp,
wsh_report_sets rs
WHERE sp.organization_id = l_organization_id AND
rs.report_set_id = sp.delivery_report_set_id;
SELECT l1.pick_up_stop_id, l1.drop_off_stop_id, l1.delivery_leg_id, l2.delivery_id parent_delivery_id
FROM wsh_delivery_legs l1, wsh_delivery_legs l2
WHERE l1.delivery_id = l_delivery_id
AND l1.parent_delivery_leg_id = l2.delivery_leg_id(+);
SELECT l2.delivery_id parent_delivery_id
FROM wsh_delivery_legs l1, wsh_delivery_legs l2
WHERE l1.delivery_id = l_delivery_id
AND l1.parent_delivery_leg_id = l2.delivery_leg_id;
SELECT freight_code
FROM wsh_carriers wc,wsh_carrier_services wcs,wsh_org_carrier_services wocs
WHERE wc.carrier_id=wcs.carrier_id AND
wcs.carrier_service_id=wocs.carrier_service_id AND
wcs.ship_method_code = p_ship_method_code AND
wocs.organization_id = p_organization_id;
SELECT freight_code
FROM wsh_carrier_ship_methods_v
WHERE ship_method_code = p_ship_method_code AND
organization_id = p_organization_id;
SELECT user_name
FROM fnd_user
WHERE user_id = l_user_id;
SELECT da.parent_delivery_detail_id,
dd.delivery_detail_id,
dd.released_status ,
dd.inspection_flag ,
dd.shipped_quantity ,
dd.shipped_quantity2 ,
dd.cycle_count_quantity ,
dd.cycle_count_quantity2 ,
dd.requested_quantity ,
dd.requested_quantity2 ,
NVL(dd.picked_quantity, dd.requested_quantity) pick_qty, -- overpicking
NVL(dd.picked_quantity2, dd.requested_quantity2) pick_qty2,
dd.organization_id ,
dd.inventory_item_id ,
dd.subinventory ,
dd.locator_id,
dd.source_code, /*Bug 2096052 for OKE */
dd.source_line_id -- Consolidation of BO Delivery Details project
FROM wsh_delivery_details dd,
wsh_delivery_assignments_v da
WHERE da.delivery_id = v_delivery_id AND
da.delivery_id IS NOT NULL AND
da.delivery_detail_id = dd.delivery_detail_id AND
dd.container_flag = 'N';
SELECT wt.trip_id , wt.carrier_id, wt.ship_method_code, wt.mode_of_transport,
--OTM R12
wt.tp_plan_name
--
FROM wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt
WHERE wdl.pick_up_stop_id=wts.stop_id
AND wdl.delivery_id=v_del_id
AND wts.trip_id=wt.trip_id;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id <> v_delivery_detail_id
START WITH delivery_detail_id = v_delivery_detail_id
CONNECT BY PRIOR parent_delivery_detail_id = delivery_detail_id;
SELECT da.delivery_detail_id
FROM wsh_delivery_assignments_v da,
WSH_DELIVERY_DETAILS dd
WHERE da.delivery_id = v_delivery_id
AND da.delivery_detail_id = dd.delivery_detail_id
AND dd.container_flag = 'Y'
AND NOT EXISTS(
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v da2
WHERE da2.parent_delivery_detail_id = da.delivery_detail_id) ;
SELECT name,batch_id,
--OTM R12
ignore_for_planning,
tms_interface_flag
--
FROM wsh_new_deliveries
WHERE delivery_id = v_delivery_id;
SELECT gross_weight,
volume
FROM wsh_new_deliveries
WHERE delivery_id = v_delivery_id;
SELECT wts.trip_id
FROM wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_new_deliveries wnd
WHERE wdl.pick_up_stop_id=wts.stop_id
AND wnd.initial_pickup_location_id = wts.stop_location_id
AND wnd.delivery_id = wdl.delivery_id
AND wnd.delivery_id=v_del_id;
SELECT stop_id
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
AND status_code IN ('OP','AR')
AND stop_sequence_number < p_stop_sequence
AND nvl(shipments_type_flag,'0') <> 'I' --bugfix 3925963
ORDER BY stop_sequence_number;
SELECT stop_id,shipments_type_flag,stop_location_id --bugfix 3925963
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
AND status_code IN ('OP','AR')
ORDER BY stop_sequence_number;
Select wt.name
from wsh_trips wt
where wt.trip_id = p_first_trip_id;
SELECT stop_sequence_number
FROM wsh_trip_stops
WHERE stop_id = p_stop_id;
select distinct trip_id from wsh_trip_stops
where stop_id in
( select distinct pick_up_stop_id
from wsh_delivery_legs
where delivery_id = t_delivery_id );
u_initial_pickup_date wsh_util_core.Date_tab_type; -- Bulk update variable
SELECT UPPER(default_severity)
FROM wsh_exception_definitions_vl
WHERE exception_name = c_exception_name
AND enabled = 'Y';
cursor l_delete_wms_empty_cnt_csr is
select distinct wwst.delivery_detail_id
from wsh_wms_sync_tmp wwst,
wsh_delivery_details wdd
where wwst.operation_type = 'DELETE'
and wdd.delivery_detail_id = wwst.delivery_detail_id
and wdd.container_flag = 'Y'
and wdd.lpn_id is not null;
l_delete_cnt_id_tbl wsh_util_core.id_tab_type;
l_bo_rows.delete ;
l_cc_rows.delete ;
l_unassign_dds.delete ;
l_bo_source_lines.delete;
l_cc_source_lines.delete;
l_assigned_source_lines.delete;
l_unassign_rel_status.delete;
l_unassign_source_lines.delete;
l_bo_line_ids.delete;
l_cancel_line_ids.delete;
l_assigned_line_ids.delete;
l_assigned_cont_ids.delete;
l_assigned_orgs.delete;
l_assigned_items.delete;
l_assigned_subs.delete;
l_assigned_locs.delete;
l_out_bo_rows.delete;
l_out_cc_rows.delete;
l_dd_org_ids.delete;
l_item_ids.delete;
l_shp_dd_shipped_qtys.delete ;
l_shp_dd_shipped_qtys2.delete ;
l_shp_dd_cc_qtys.delete ;
l_shp_dd_cc_qtys2.delete ;
l_shp_dd_req_qtys.delete ;
l_shp_dd_ids.delete ;
l_shp_dd_items.delete ;
l_shp_dd_orgs.delete ;
l_shp_dd_subs.delete ;
l_shp_dd_locs.delete ;
l_shp_dd_cont_ids.delete ;
l_shp_dd_source.delete;
l_assigned_req_qtys.delete ;
l_assigned_shp_qtys.delete ;
l_assigned_cc_qtys.delete ;
l_assigned_req_qtys2.delete ;
l_assigned_shp_qtys2.delete ;
l_assigned_cc_qtys2.delete ;
l_assigned_pick_qtys.delete;
l_assigned_pick_qtys2.delete;
l_assigned_overpick_qtys.delete;
l_assigned_overpick_qtys2.delete;
l_assigned_source.delete;
l_stop_rows.delete ;
l_del_rows.delete ;
l_cc_req_qtys.delete ;
l_cc_overpick_qtys.delete ;
l_cc_qtys.delete ;
l_cc_qtys2.delete ;
l_bo_qtys.delete ;
l_bo_req_qtys.delete ;
l_bo_overpick_qtys.delete ;
l_bo_qtys2.delete ;
L_stage_ROWS.delete ;
SELECT status_code ,
initial_pickup_date ,
ultimate_dropoff_date ,
organization_id ,
ship_method_code ,
initial_pickup_location_id,
ultimate_dropoff_location_id ,
gross_weight ,
net_weight ,
volume
INTO l_del_status_code,
l_initial_pickup_date(i),
l_ultimate_dropoff_date(i),
l_organization_id ,
l_ship_method_code ,
l_ship_from_location,
l_ship_to_location ,
l_gross_weight ,
l_net_weight ,
l_volume
FROM wsh_new_deliveries
WHERE delivery_id = p_del_rows(i)
AND nvl(shipment_direction,'O') IN ('O','IO') -- J-IB-NPARIKH
FOR UPDATE NOWAIT;
l_exceptions_tab.delete;
SELECT delivery_detail_id INTO l_dummy_detail_id
FROM wsh_delivery_details
WHERE delivery_detail_id = l_delivery_detail_id
FOR UPDATE NOWAIT;
UPDATE WSH_DELIVERY_DETAILS
SET INSPECTION_FLAG = 'I'
WHERE DELIVERY_DETAIL_ID = l_dummy_detail_id ;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_SHIPPING_LPN_UTILS_PUB.UPDATE_LPN_CONTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
WMS_Shipping_LPN_Utils_PUB.update_lpn_context
(p_delivery_id => p_del_rows(i),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
FND_MESSAGE.SET_TOKEN('API_NAME', 'WMS_SHIPPING_LPN_UTILS_PUB.UPDATE_LPN_CONTEXT');
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_SHIPPING_LPN_UTILS_PUB.UPDATE_LPN_CONTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
WMS_Shipping_LPN_Utils_PUB.update_lpn_context
(p_delivery_id => p_del_rows(i),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
FND_MESSAGE.SET_TOKEN('API_NAME', 'WMS_SHIPPING_LPN_UTILS_PUB.UPDATE_LPN_CONTEXT');
UPDATE wsh_delivery_Details
SET shipped_quantity = l_shp_dd_shipped_qtys(tmp_counter),
shipped_quantity2 = l_shp_dd_shipped_qtys2(tmp_counter),
cycle_count_quantity = l_shp_dd_cc_qtys(tmp_counter),
cycle_count_quantity2 = l_shp_dd_cc_qtys2(tmp_counter)
WHERE delivery_detail_id = l_shp_dd_ids(tmp_counter);
p_update_flag => 'Y',
p_post_process_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_net_weight => l_net_weight,
x_volume => l_volume,
x_return_status => l_return_status);
UPDATE WSH_DELIVERY_DETAILS
SET -- TRACKING_NUMBER = NULL, Bug# 3632485
SHIPPED_QUANTITY = DECODE (SERIAL_NUMBER, NULL,
DECODE(TRANSACTION_TEMP_ID, NULL, NULL, PICKED_QUANTITY),
PICKED_QUANTITY),
SHIPPED_QUANTITY2 = DECODE (SERIAL_NUMBER, NULL,
DECODE(TRANSACTION_TEMP_ID, NULL, NULL, PICKED_QUANTITY2),
PICKED_QUANTITY2),
CYCLE_COUNT_QUANTITY = DECODE (SERIAL_NUMBER, NULL ,
DECODE(TRANSACTION_TEMP_ID, NULL, NULL, 0) , 0),
CYCLE_COUNT_QUANTITY2 = DECODE (SERIAL_NUMBER, NULL,
DECODE(TRANSACTION_TEMP_ID, NULL, NULL, 0), 0)
WHERE DELIVERY_DETAIL_ID = l_stage_rows(tmp_counter);
update wsh_delivery_details set
subinventory = NVL(subinventory, dft_subinv),
locator_id = NVL(locator_id, dft_loc_id)
where delivery_detail_id = l_shp_dd_ids ( dd_count );
-- delete the entry in l_unassign_source_lines if it is not there in l_bo_line_ids.
-- These changes are done to restrict the filtering of l_unassign_dds only
-- if the corresponding line is backordered.
l_idx := l_unassign_source_lines.FIRST;
l_unassign_source_lines.DELETE(l_idx);
-- in l_unassign_backordered_dds, if NOT then Delete that dd_id from l_unassign_dds because
-- it must already be physically deleted by the backorder API.
-- [ This check should happen for dd_ids in l_unassign_dds, ONLY if the released_status is 'B'
-- and if the dd_id is under the backordered source line ]
-- Filter l_unassign_dds only if the corresponding Order line is backordered. ie., l_unassign_source_lines.EXISTS
IF (l_unassign_source_lines.EXISTS(l_idx) AND l_unassign_rel_status(l_idx) = 'B') THEN --{
l_flag := 'N';
l_unassign_dds.DELETE(l_idx);
-- Now, l_unassign_dds contains only the not-deleted dd_ids.
l_unassign_backordered_dds.DELETE;
l_unassign_dds.DELETE;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE.DELETE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_interface.delete_details(
p_details_id => l_cancel_line_ids,
x_return_status => l_return_status);
p_update_flag => 'Y',
p_post_process_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_net_weight => l_net_weight,
x_volume => l_volume,
x_return_status => l_return_status);
p_update_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_gross_weight => l_gross_weight,
x_net_weight => l_net_wt,
x_volume => l_volume,
x_return_status => l_return_status);
l_trip_info_tab.delete;
p_action_code => 'UPDATE',
p_del_attr_tab => l_cc_del_attr_tab,
p_det_attr_tab => l_cc_det_attr_tab,
p_trip_attr_tab => l_trip_info_tab,
p_stop_attr_tab => l_cc_stop_attr_tab,
p_in_ids => l_cc_in_ids,
x_fail_ids => l_cc_fail_ids,
x_validate_result => l_cc_validate_result,
x_failed_lines => l_cc_failed_records,
x_line_groups => l_cc_line_groups,
x_group_info => l_cc_group_info,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
UPDATE wsh_trips
SET ship_method_code = p_ship_method,
service_level = l_carrier_rec.service_level,
mode_of_transport = l_carrier_rec.mode_of_transport,
carrier_id = l_carrier_rec.carrier_id
WHERE trip_id = l_del_first_trip;
p_action => 'UPDATE',
x_return_status => l_return_status);
p_action_code => 'UPDATE',
p_del_attr_tab => l_cc_del_attr_tab,
p_det_attr_tab => l_cc_det_attr_tab,
p_trip_attr_tab => l_trip_info_tab,
p_stop_attr_tab => l_cc_stop_attr_tab,
p_in_ids => l_cc_in_ids,
x_fail_ids => l_cc_fail_ids,
x_validate_result => l_cc_validate_result,
x_failed_lines => l_cc_failed_records,
x_line_groups => l_cc_line_groups,
x_group_info => l_cc_group_info,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
UPDATE wsh_trips
SET ship_method_code = p_ship_method,
service_level = l_carrier_rec.service_level,
mode_of_transport = l_carrier_rec.mode_of_transport,
carrier_id = l_carrier_rec.carrier_id
WHERE trip_id = l_trip_id;
l_mdc_co_del_tab.delete(i);
l_mdc_cl_del_tab.delete;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.DELETE_DELIVERY_LEG',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_delivery_legs_pvt.delete_delivery_leg
(NULL, dg.delivery_leg_id, l_return_status);
-- also select hold 'E' messages for WMS org, since hold errors
-- bug 3455640 added 'WSH_INVALID_CATCHWEIGHT'
-- should prevent the delivery from being ship confirmed, not just unassign
-- the delivery details
l_count := 0;
l_msg_table.delete;
G_HASH_TRIP.delete;
l_del_tmp_rows.delete;
l_del_tmp_rows.delete;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.UPDATE_LEG_SEQUENCE',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_new_delivery_actions.update_leg_sequence(
p_delivery_id => u_del_rows(i),
x_return_status => l_return_status);
UPDATE wsh_new_deliveries
SET status_code = u_status_code(i),
confirm_date = sysdate,
confirmed_by = nvl(confirmed_by, l_user_name),
-- number_of_lpn = decode(u_number_of_lpn(i),0,null,u_number_of_lpn(i)) , --Bug 1729723
number_of_lpn = decode(number_of_lpn, null, decode(u_number_of_lpn(i),0,null,u_number_of_lpn(i)), number_of_lpn),
initial_pickup_date = u_initial_pickup_date(i) ,
ultimate_dropoff_date = u_ultimate_dropoff_date(i) ,
--OTM R12, when setting delivery to closed, set the tms flag
TMS_INTERFACE_FLAG = DECODE(l_gc3_is_installed,
'Y', DECODE(NVL(u_status_code(i), 'XXXXX'),
'CL', DECODE(NVL(tms_interface_flag,
WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
NVL(tms_interface_flag,
WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
NVL(tms_interface_flag,
WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
--END OTM R12
last_update_date = SYSDATE ,
last_updated_by = l_user_id,
last_update_login = l_login_id,
--/==Workflow Changes
del_wf_intransit_attr = decode(p_intransit_flag,'Y','I','X'),
del_wf_close_attr = decode(p_close_flag,'Y','I','X'),
del_wf_interface_attr = decode(l_defer_interface_flag,'N','I','X')
--==/
WHERE delivery_id = u_del_rows(i);
u_stop_rows.delete(i);
for l_delete_cnt_rec in l_delete_wms_empty_cnt_csr loop
--{
--
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.DELETE_CONTAINERS',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_container_actions.delete_containers (
p_container_id => l_delete_cnt_rec.delivery_detail_id,
x_return_status => l_return_status);
select wdd.organization_id organization_id,
nvl(wdd.line_direction,'O') line_direction,
wdd.delivery_detail_id delivery_detail_id
from wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
where wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.container_flag IN ('Y','C')
and nvl(wdd.line_direction,'O') IN ('O', 'IO')
and wda.delivery_id = p_delivery_id;
/* H Integration: 940/945 do not update cancelled delivery lines wrudge */
-- Bug 2335270 : last_update_date = SYSDATE is included in
-- the update statement
--
-- LPN CONV. rv
--
--
IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
THEN
--{
--l_child_cnt_counter := 1;
l_sync_tmp_wms_recTbl.operation_type_tbl(l_cnt_wms_counter) := 'UPDATE';
l_sync_tmp_inv_recTbl.operation_type_tbl(l_cnt_inv_counter) := 'UPDATE';
UPDATE wsh_Delivery_details
SET released_Status = 'C',
shipped_quantity = DECODE(container_flag, 'Y', 1, shipped_quantity),
inv_interfaced_flag = DECODE(container_flag, 'N', NVL(inv_interfaced_flag,'N'), NULL),
oe_interfaced_flag = DECODE(container_flag, 'N', NVL(oe_interfaced_flag, 'N'), NULL),
last_update_date = SYSDATE,
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE delivery_detail_id IN (
SELECT delivery_Detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_id = p_in_rec.delivery_id
)
AND released_status <> 'D'
RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab; -- Added for DBI Project
WSH_DEBUG_SV.log(l_module_name,'Number of Lines updated',SQL%ROWCOUNT);
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
UPDATE wsh_new_deliveries
SET initial_pickup_date = p_in_rec.actual_date,
ultimate_dropoff_date = GREATEST(
NVL(ultimate_dropoff_date,p_in_rec.actual_date),
p_in_rec.actual_date
),
status_code = 'IT',
last_update_date = SYSDATE ,
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE delivery_id = p_in_rec.delivery_id
RETURNING organization_id INTO l_org_id;
SELECT stop_id
FROM wsh_delivery_legs wdl,
wsh_Trip_stops wts
WHERE wdl.delivery_id = p_delivery_id
AND (
wdl.pick_up_stop_id = wts.stop_id
OR wdl.drop_off_stop_id = wts.stop_id
)
AND wts.status_code <> 'CL';
UPDATE wsh_new_deliveries
SET ultimate_dropoff_date = GREATEST(
nvl(ultimate_dropoff_date,p_in_rec.actual_date),
p_in_rec.actual_date
),
status_code = 'CL',
--OTM R12, when setting delivery to closed, set tms flag
tms_interface_flag = DECODE(l_gc3_is_installed,
'Y', DECODE(NVL(tms_interface_flag,
WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED),
NVL(tms_interface_flag,
WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
--
last_update_date = SYSDATE ,
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE delivery_id = p_in_rec.delivery_id
RETURNING organization_id INTO l_org_id;
SELECT status_code, name,organization_id
FROM wsh_new_deliveries
WHERE delivery_id = l_delivery_id;
SELECT dg.pick_up_stop_id,
pu_stop.status_code pu_status,
dg.drop_off_stop_id,
do_stop.status_code do_status
FROM wsh_delivery_legs dg,
wsh_trip_stops pu_stop,
wsh_trip_stops do_stop
WHERE dg.delivery_id = l_delivery_id
AND pu_stop.stop_id = dg.pick_up_stop_id
AND do_stop.stop_id = dg.drop_off_stop_id;
update wsh_new_deliveries
set status_code = l_status_code,
confirm_date = null,--6453740
confirmed_by = null,--6453740
last_update_date = SYSDATE ,
last_updated_by = l_user_id,
last_update_login = l_login_id
where delivery_id = p_del_rows(i);
update wsh_new_deliveries d1
set d1.status_code = l_status_code,
d1.last_update_date = SYSDATE,
d1.last_updated_by = l_user_id,
d1.last_update_login = l_login_id
where d1.status_code = 'CO'
and exists (
select 'X'
from wsh_delivery_legs l1, wsh_delivery_legs l2
where l2.delivery_id = p_del_rows(i)
and l2.parent_delivery_leg_id = l1.delivery_leg_id
and l1.delivery_id = d1.delivery_id);
/* bug 2335270 Included last_update_date,last_updated_by and last_update_login
in wsh_new_deliveries and wsh_delivery_details */
-- J-IB-HEALI---{
PROCEDURE Complete_Leg_Sequence
( p_delivery_id IN NUMBER,
p_update_flag IN VARCHAR2,
p_insert_msg IN BOOLEAN default true,
x_leg_count OUT NOCOPY NUMBER,
x_leg_complete OUT NOCOPY varchar2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR delivery_info IS
SELECT initial_pickup_location_id,
ultimate_dropoff_location_id
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT count(*)
FROM wsh_delivery_legs
WHERE delivery_id = p_delivery_id;
SELECT dg.delivery_leg_id leg_id,
st1.stop_location_id pickup,
st2.stop_location_id dropoff
FROM wsh_delivery_legs dg,
wsh_trip_stops st1,
wsh_trip_stops st2
WHERE dg.delivery_id = p_delivery_id AND
st1.stop_id = dg.pick_up_stop_id AND
st2.stop_id = dg.drop_off_stop_id AND
st1.stop_location_id = l_pickup_location_id;
SELECT count(*)
FROM wsh_delivery_legs dg,
wsh_trip_stops st1,
wsh_trip_stops st2
WHERE dg.delivery_id = p_delivery_id AND
st1.stop_id = dg.pick_up_stop_id AND
st2.stop_id = dg.drop_off_stop_id AND
st2.stop_location_id = l_pickup_location_id;
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_FLAG',P_UPDATE_FLAG);
IF p_update_flag = 'Y' THEN
UPDATE wsh_delivery_legs
SET sequence_number = l_seq_num * 10
WHERE delivery_leg_id = l_leg_id;
END IF; /* added for p_update_flag */
IF (p_insert_msg) THEN
FND_MESSAGE.SET_NAME('WSH','WSH_DEL_MULTIPLE_LEGS');
IF (p_insert_msg) THEN
FND_MESSAGE.SET_NAME('WSH','WSH_DEL_MULTIPLE_LEGS');
IF (p_insert_msg) THEN
FND_MESSAGE.SET_NAME('WSH','WSH_DEL_INVALID_FLOW');
IF (p_insert_msg) THEN
FND_MESSAGE.SET_NAME('WSH','WSH_DEL_NO_ULTIMATE_DROPOFF');
PROCEDURE Update_Leg_Sequence
( p_delivery_id IN NUMBER,
p_update_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_leg_count NUMBER := 0;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_LEG_SEQUENCE';
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_FLAG',P_UPDATE_FLAG);
p_update_flag => p_update_flag,
x_leg_count => l_leg_count,
x_leg_complete => l_leg_complete,
x_return_status => l_return_status);
wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.UPDATE_LEG_SEQUENCE');
END Update_Leg_Sequence;
p_update_del_flag IN VARCHAR2,
p_update_leg_flag IN VARCHAR2,
x_leg_complete OUT NOCOPY boolean,
x_return_status OUT NOCOPY VARCHAR2) IS
l_leg_count NUMBER := 0;
l_update_flag varchar2(1):='N';
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_del_FLAG',P_UPDATE_del_FLAG);
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_leg_FLAG',P_UPDATE_leg_FLAG);
p_update_flag => p_update_leg_flag,
p_insert_msg => false,
x_leg_count => l_leg_count,
x_leg_complete => l_leg_complete,
x_return_status => x_return_status);
l_update_flag:='Y';
l_update_flag:= null;
IF (p_update_del_flag='Y') THEN
UPDATE wsh_new_deliveries
SET ITINERARY_COMPLETE = l_update_flag,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID
WHERE delivery_id= p_delivery_id;
wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.UPDATE_LEG_SEQUENCE');
SELECT loading_order_flag
FROM wsh_new_deliveries
WHERE delivery_id = l_delivery_id;
'SELECT dd.delivery_detail_id '||
'FROM wsh_delivery_details dd, '||
'wsh_delivery_assignments_v da '||
'WHERE dd.delivery_detail_id = da.delivery_detail_id AND '||
'da.parent_delivery_detail_id = :cont_id AND '||
'container_flag = ''N'' '||
'ORDER BY customer_prod_seq ';
'SELECT da.parent_delivery_detail_id, '||
'avg(customer_prod_seq) avg_prod_seq, '||
'count(*) '||
'FROM wsh_delivery_details dd, '||
'wsh_delivery_assignments_v da '||
'WHERE dd.delivery_detail_id = da.delivery_detail_id AND '||
'dd.customer_prod_seq IS NOT NULL AND '||
'dd.container_flag = ''N'' AND '||
'da.parent_delivery_detail_id IS NOT NULL AND '||
'da.delivery_id IS NOT NULL AND '||
'da.delivery_id = :del_id '||
'GROUP BY parent_delivery_detail_id '||
'ORDER BY avg_prod_seq ';
UPDATE wsh_delivery_details
SET load_seq_number = l_cnt
WHERE delivery_detail_id = l_delivery_detail_id;
PROCEDURE Assign_Delivery_Update
(p_delivery_id IN NUMBER,
p_del_params IN wsh_delivery_autocreate.grp_attr_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_del_info wsh_new_deliveries_pvt.delivery_rec_type;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ASSIGN_DELIVERY_UPDATE';
WSH_DELIVERY_AUTOCREATE.Create_Update_Hash(
p_delivery_rec => l_del_info,
x_return_status => x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_new_deliveries_pvt.update_delivery( null, l_del_info, x_return_status);
END Assign_Delivery_Update;
PROCEDURE update_freight_terms
(
p_delivery_id IN NUMBER,
p_action_code IN VARCHAR2 DEFAULT 'UNASSIGN',
p_line_freight_terms_Code IN VARCHAR2 DEFAULT NULL,
x_freight_terms_Code OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--
-- Get delivery information
--
CURSOR dlvy_csr (p_delivery_id IN NUMBER)
IS
SELECT freight_terms_code, name,
nvl(shipment_direction,'O') shipment_direction
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT 1
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id
FOR UPDATE OF freight_terms_code NOWAIT;
SELECT distinct freight_terms_code
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND NVL(wdd.container_flag,'N') = 'N';
e_update EXCEPTION;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'update_freight_terms';
RAISE e_update;
RAISE e_update;
WHEN e_update THEN
l_cnt := 1;
UPDATE wsh_new_deliveries
SET freight_terms_code = l_dlvy_freight_terms_code,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_id = p_delivery_id;
UPDATE wsh_new_deliveries
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_id = p_delivery_id;
wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.update_freight_terms',l_module_name);
END update_freight_terms;
PROCEDURE update_ship_from_location
(
p_delivery_id IN NUMBER,
p_location_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--
-- Get Delivery Info
--
CURSOR dlvy_csr (p_delivery_id IN NUMBER)
IS
SELECT wnd.name,
initial_pickup_location_id
FROM wsh_new_deliveries wnd
WHERE delivery_id = p_delivery_id;
SELECT stop_id
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts
WHERE wdl.delivery_id = p_delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wts.stop_location_id = p_location_id;
SELECT wnd.delivery_id, wnd.name
FROM wsh_delivery_legs wdl,
wsh_new_deliveries wnd
WHERE pick_up_stop_id = p_stop_id
AND wdl.delivery_id <> p_delivery_id
AND wdl.delivery_id = wnd.delivery_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'update_ship_from_location';
SAVEPOINT update_ship_from_location_sp;
UPDATE WSH_NEW_DELIVERIES
SET INITIAL_PICKUP_LOCATION_ID = p_location_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_id = p_delivery_id;
UPDATE WSH_DELIVERY_DETAILS
SET SHIP_FROM_LOCATION_ID = p_location_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id IN (
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_id = p_delivery_id
);
p_action => 'UPDATE',
x_return_status => l_return_status);
UPDATE wsh_trip_stops
SET stop_location_id = p_location_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
physical_stop_id = l_physical_stop_id, -- J+ Internal Locations
physical_location_id = l_physical_location_id -- J+ Internal Locations
WHERE stop_id = stop_rec.stop_id;
p_caller => 'WSH_UPDATE_SHIP_FROM_LOC',
x_success_trip_ids => l_success_trip_ids,
x_return_status => l_return_status);
UPDATE WSH_NEW_DELIVERIES
SET INITIAL_PICKUP_LOCATION_ID = p_location_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_id = pickup_dlvy_rec.delivery_id;
UPDATE WSH_DELIVERY_DETAILS
SET SHIP_FROM_LOCATION_ID = p_location_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id IN (
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_id = pickup_dlvy_rec.delivery_id
);
FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_PU_LOCN_UPDATE');
ROLLBACK TO update_ship_from_location_sp;
ROLLBACK TO update_ship_from_location_sp;
ROLLBACK TO update_ship_from_location_sp;
wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.update_ship_from_location', l_module_name);
END update_ship_from_location;
SELECT s1.trip_id
FROM wsh_trip_stops s1,
wsh_delivery_legs dl1,
wsh_new_deliveries d1,
wsh_trip_stops s2,
wsh_delivery_legs dl2
WHERE d1.delivery_id <> l_delivery_id
AND s1.stop_id = dl1.pick_up_stop_id
AND d1.delivery_id = dl1.delivery_id
AND d1.status_code = 'OP'
AND d1.delivery_type = 'STANDARD'
AND s2.trip_id = s1.trip_id
AND s2.stop_id = dl2.pick_up_stop_id
AND dl2.delivery_id = l_delivery_id
AND rownum = 1;
SELECT count(delivery_leg_id), min(delivery_leg_id)
FROM wsh_delivery_legs
WHERE delivery_id = x_delivery_id;
SELECT count(s.trip_id ), min(s.trip_id)
FROM wsh_delivery_legs d, wsh_trip_stops s
WHERE d.pick_up_stop_id = s.stop_id
AND s.trip_id = (SELECT s1.trip_id
FROM wsh_trip_stops s1, wsh_delivery_legs d1
WHERE d1.delivery_leg_id = x_delivery_leg_id
AND s1.stop_id = d1.pick_up_stop_id);
SELECT count(*), min(s.trip_id)
FROM wsh_new_deliveries n, wsh_delivery_legs d, wsh_trip_stops s
WHERE n.delivery_id = d.delivery_id
AND d.pick_up_stop_id = s.stop_id
AND s.trip_id = (SELECT s1.trip_id
FROM wsh_trip_stops s1, wsh_delivery_legs d1
WHERE d1.delivery_leg_id = x_delivery_leg_id
AND s1.stop_id = d1.pick_up_stop_id)
AND d.parent_delivery_leg_id IS NULL;
SELECT lane_id
FROM wsh_trips
WHERE trip_id = x_trip_id;
select pleg.delivery_id
from wsh_delivery_legs pleg,
wsh_delivery_legs cleg
where pleg.delivery_leg_id = cleg.parent_delivery_leg_id
and cleg.delivery_id = c_delivery_id;
SELECT DISTINCT wdd.ship_to_site_use_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.ship_to_site_use_id IS NOT NULL;
SELECT SITE.SITE_USE_ID
FROM HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_SITE_USES_ALL SITE
WHERE
SITE.SITE_USE_CODE = 'SHIP_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
AND LOC.LOCATION_ID = c_location_id;
SELECT hcas.cust_account_id
FROM wsh_locations wl,
hz_party_sites hps,
hz_cust_acct_sites_all hcas
WHERE wl.wsh_location_id = p_location_id
AND wl.location_source_code = 'HZ'
AND wl.source_location_id = hps.location_id
AND hps.party_site_id = hcas.party_site_id;
SELECT distinct hcas.cust_account_id
FROM hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas
WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_id IN (SELECT DISTINCT wdd.ship_to_site_use_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = p_delivery_id);
SELECT INTRANSIT_TIME
FROM MTL_INTERORG_SHIP_METHODS
WHERE FROM_LOCATION_ID = p_from_location_id
AND TO_LOCATION_ID = p_to_location_id
AND SHIP_METHOD = p_ship_method;
PROCEDURE FORMAT_DEL_UPDATE_TABLE(--p_cs_result_tab IN FTE_ACS_PKG.FTE_CS_RESULT_TAB_TYPE,
p_cs_result_tab IN WSH_FTE_INTEGRATION.WSH_CS_RESULT_TAB_TYPE,
p_caller IN VARCHAR2 DEFAULT NULL,
x_rec_attr_tab OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
x_trip_info_tab OUT NOCOPY WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
x_return_message OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
fmc NUMBER;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FORMAT_DEL_UPDATE_TABLE';
x_rec_attr_tab(fmc).LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
x_rec_attr_tab(fmc).LAST_UPDATED_BY := FND_API.G_MISS_NUM;
x_rec_attr_tab(fmc).LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
x_rec_attr_tab(fmc).PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
x_trip_info_tab(fmct).LAST_UPDATE_DATE := SYSDATE;
x_trip_info_tab(fmct).LAST_UPDATED_BY := fnd_global.user_id;
x_trip_info_tab(fmct).LAST_UPDATE_LOGIN := fnd_global.login_id;
x_trip_info_tab(fmct).PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
x_rec_attr_tab.DELETE;
WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM WSH_NEW_DELIVERY_ACTIONS.FORMAT_DEL_UPDATE_TABLE IS ' ||l_error_text);
WSH_UTIL_CORE.default_handler('WSH_NEW_DELIVERY_ACTIONS.FORMAT_DEL_UPDATE_TABLE');
END FORMAT_DEL_UPDATE_TABLE;
PROCEDURE PROCESS_CARRIER_SELECTION(p_delivery_id_tab IN OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
p_batch_id IN NUMBER,
p_form_flag IN VARCHAR2,
p_organization_id IN NUMBER DEFAULT NULL,
p_caller IN VARCHAR2 DEFAULT NULL,
x_return_message OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
--
-- Cursor to get delivery info from the table of delivery ids
-- Carrier selection is not required if delivery's ship from location id is null.
--
cursor c_get_del_info_by_del_id(x_delivery_id NUMBER) IS
select delivery_id,
name,
organization_id,
gross_weight,
weight_uom_code,
volume,
volume_uom_code,
initial_pickup_location_id,
ultimate_dropoff_location_id,
customer_id,
freight_terms_code,
NULL, -- planned_flag,
initial_pickup_date,
ultimate_dropoff_date,
fob_code,
shipment_direction,
delivery_type,
NULL -- l_trip_id
from wsh_new_deliveries
where delivery_id = x_delivery_id
and ship_method_code is null
AND initial_pickup_location_id <> WSH_UTIL_CORE.C_NULL_SF_LOCN_ID -- J-IB-NPARIKH
and planned_flag NOT IN ('Y','F');
select delivery_id,
name,
organization_id,
gross_weight,
weight_uom_code,
volume,
volume_uom_code,
initial_pickup_location_id,
ultimate_dropoff_location_id,
customer_id,
freight_terms_code,
NULL, -- planned_flag,
initial_pickup_date,
ultimate_dropoff_date,
fob_code,
shipment_direction,
delivery_type,
NULL -- l_trip_id
from wsh_new_deliveries
where batch_id = x_batch_id
and organization_id = x_organization_id
and ship_method_code is null
and planned_flag NOT IN ('Y','F');
select delivery_id,
name,
organization_id,
gross_weight,
weight_uom_code,
volume,
volume_uom_code,
initial_pickup_location_id,
ultimate_dropoff_location_id,
customer_id,
freight_terms_code,
planned_flag,
initial_pickup_date,
ultimate_dropoff_date,
fob_code,
shipment_direction,
delivery_type,
NULL -- l_trip_id
from wsh_new_deliveries
where delivery_id = f_delivery_id;
select wts.trip_id,
wt.rank_id
from wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_trips wt
where wdl.delivery_id = p_del_id
and wts.stop_id = wdl.pick_up_stop_id
and wts.trip_id = wt.trip_id;
select wnd.organization_id
from wsh_new_deliveries wnd
where wnd.delivery_id = p_del_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_CARRIER_SELECTION';
WSH_DEBUG_SV.logmsg(l_module_name, 'DELIVERY '||l_dlvy_info_rec.l_delivery_id||' IS A PLANNED DELIVERY , CARRIER SELECTION IS NOT ALLOWED' );
WSH_DEBUG_SV.logmsg(l_module_name,'DELIVERY '||l_dlvy_info_rec.l_delivery_id||' IS ASSIGNED TO MANY TRIPS, CARRIER SELECTION IS NOT ALLOWED' );
WSH_DEBUG_SV.logmsg(l_module_name, 'DELIVERY '||l_dlvy_info_rec.l_delivery_id||' IS ASSIGNED TO A SHARED TRIP, CARRIER SELECTION IS NOT ALLOWED' );
WSH_DEBUG_SV.logmsg(l_module_name, 'DELIVERY '||l_dlvy_info_rec.l_delivery_id||' IS ASSIGNED TO A TRIP WITH SERVICE, CARRIER SELECTION IS NOT ALLOWED' );
-- In format carrier selection. If weight/volume UOM code is NULL then get the
-- default uoms. (This statement is in accordance with previous code)
--
IF ((l_dlvy_info_tab(i).l_weight_uom_code is null) AND
(l_dlvy_info_tab(i).l_volume_uom_code is null)) THEN
WSH_WV_UTILS.get_default_uoms( p_organization_id => l_dlvy_info_tab(i).l_organization_id,
x_weight_uom_code => l_carrier_sel_entity_tab(l_cnt).weight_uom_code,
x_volume_uom_code => l_carrier_sel_entity_tab(l_cnt).volume_uom_code,
x_return_status => l_return_status);
l_carrier_sel_entity_tab.DELETE(l_cnt);
l_carrier_sel_entity_tab.DELETE(l_cnt);
l_carrier_sel_entity_tab.DELETE(l_cnt);
l_carrier_sel_entity_tab.DELETE(l_cnt);
wsh_debug_sv.logmsg(l_module_name, 'None of the selected deliveries have been found as eligible for Carrier Selection');
WSH_FTE_INTEGRATION.CARRIER_SELECTION( p_format_cs_tab => l_carrier_sel_entity_tab,
p_messaging_yn => l_messaging_yn,
p_caller => p_caller,
p_entity => 'DLVY',
x_cs_output_tab => l_cs_result_tab,
x_cs_output_message_tab => l_cs_output_message_tab,
x_return_message => l_return_message,
x_return_status => l_return_status);
l_rec_attr_tab.DELETE;
l_del_out_rec_tab.DELETE;
l_in_rec.caller := 'FTE_CARRIER_SELECTION_AUTO';
l_in_rec.action_code := 'UPDATE';
l_trip_in_rec.action_code := 'UPDATE';
l_ranked_list.DELETE;
l_ranked_list.DELETE;
l_ranked_list.DELETE;
FORMAT_DEL_UPDATE_TABLE(p_cs_result_tab => l_cs_result_tab,
p_caller => p_caller,
x_rec_attr_tab => l_rec_attr_tab,
x_trip_info_tab => l_trip_info_tab,
x_return_status => l_return_status,
x_return_message => x_return_message);
WSH_INTERFACE_GRP.Create_Update_Delivery(p_api_version_number => 1.0,
p_init_msg_list => 'F',
p_commit => null,
p_in_rec => l_in_rec,
p_rec_attr_tab => l_rec_attr_tab,
x_del_out_rec_tab => l_del_out_rec_tab,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
WSH_DEBUG_SV.logmsg(l_module_name, 'CARRIER SELECTION - AUTO CREATE - COULD NOT UPDATE DELIVERY');
WSH_INTERFACE_GRP.Create_Update_Trip(
p_api_version_number => 1.0,
p_init_msg_list => 'F',
p_commit => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trip_info_tab => l_trip_info_tab,
p_in_rec => l_trip_in_rec,
x_out_tab => l_trip_out_rec_tab);
WSH_DEBUG_SV.logmsg(l_module_name, 'CARRIER SELECTION - FORM MANUAL - COULD NOT UPDATE TRIP');
WSH_UTIL_CORE.default_handler('WSH_NEW_DELIVERY_ACTIONS.PROCESS_CARRIER_SELECTION');
END PROCESS_CARRIER_SELECTION;
select s1.trip_id,
l.pick_up_stop_id,
l.drop_off_stop_id,
l.delivery_leg_id,
d.ultimate_dropoff_location_id dropoff_stop_location,
s1.stop_location_id pickup_stop_location,
t.ignore_for_planning,
s1.planned_arrival_date pu_ar_date,
s1.planned_departure_date pu_dep_date,
s2.planned_arrival_date do_ar_date,
s2.planned_departure_date do_dep_date
from wsh_trip_stops s1, wsh_trip_stops s2, wsh_delivery_legs l, wsh_new_deliveries d, wsh_trips t
where s1.stop_id = l.pick_up_stop_id
and s2.stop_id = l.drop_off_stop_id
and l.delivery_id = d.delivery_id
and d.delivery_id = p_parent_del_id
and d.status_code = 'OP'
and d.delivery_type = 'CONSOLIDATION'
and s1.trip_id = t.trip_id ;
select d.delivery_id
from wsh_new_deliveries d
where d.delivery_id = p_delivery_id
and d.status_code = 'OP'
and d.delivery_type = 'STANDARD'
and not exists (select 1 from wsh_delivery_legs
where parent_delivery_leg_id is not null
and delivery_id = d.delivery_id);
select delivery_leg_id
from wsh_delivery_legs
where delivery_id = p_delivery_id
and rownum = 1;
select l.delivery_leg_id, s1.trip_id
from wsh_delivery_legs l, wsh_trip_stops s1, wsh_new_deliveries d
where l.delivery_id = p_delivery_id
and d.delivery_id = l.delivery_id
and d.initial_pickup_location_id = s1.stop_location_id
and s1.stop_id = l.pick_up_stop_id;
select a.delivery_detail_id
from wsh_delivery_assignments a
where a.delivery_id is not null
and a.delivery_id = p_delivery_id
and a.type = 'O'
and a.parent_delivery_detail_id is null
and not exists
(select '1'
from wsh_delivery_assignments b
where a.delivery_detail_id = b.delivery_detail_id
and b.type = 'C');
select distinct s2.trip_id
from wsh_trip_stops s1, wsh_trip_stops s2,
wsh_delivery_legs l1, wsh_delivery_legs l2
where s1.trip_id = p_trip_id
and s1.stop_id = l1.pick_up_stop_id
and l1.delivery_id = l2.delivery_id
and l2.pick_up_stop_id = s2.stop_id;
select s.trip_id
from wsh_trip_stops s, wsh_delivery_legs l
where l.delivery_id = p_delivery_id
and l.pick_up_stop_id = s.stop_id;
update wsh_delivery_assignments
set type = 'O'
where delivery_id = l_valid_children_tab(i)
and delivery_id is not null
and nvl(type, 'S') = 'S'
and parent_delivery_detail_id is NULL;
INSERT INTO wsh_delivery_assignments (
delivery_id,
parent_delivery_id,
delivery_detail_id,
parent_delivery_detail_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
active_flag,
delivery_assignment_id,
type
) VALUES (
l_valid_children_tab(i),
p_parent_del_id,
l_child_details_tab(k),
NULL,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
NULL,
NULL,
NULL,
NULL,
NULL,
wsh_delivery_assignments_s.nextval,
'C'
);
l_child_del_trips_dummy.delete;
l_trip_id_tab.delete;
update wsh_delivery_legs
set parent_delivery_leg_id = l_parent_trip_rec.delivery_leg_id
where delivery_id = l_valid_children_tab(i)
and pick_up_stop_id = l_parent_trip_rec.pick_up_stop_id;
p_update_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_gross_weight => l_gross_wt,
x_net_weight => l_net_wt,
x_volume => l_volume,
x_return_status => l_return_status);
select l1.delivery_id, l2.delivery_id, l1.delivery_leg_id, s.trip_id
from wsh_delivery_legs l1, wsh_delivery_legs l2, wsh_new_deliveries d, wsh_trip_stops s
where l1.parent_delivery_leg_id = l2.delivery_leg_id
and l1.delivery_id = p_child_delivery_id
and l2.delivery_id = NVL(p_parent_delivery_id, l2.delivery_id)
and d.delivery_id = l1.delivery_id
and d.status_code = 'OP'
and s.stop_id = l1.pick_up_stop_id
order by s.trip_id, l2.delivery_id;
select l1.delivery_id
from wsh_delivery_legs l1, wsh_delivery_legs l2
where l1.parent_delivery_leg_id = l2.delivery_leg_id
and l2.delivery_id = p_parent_delivery_id
and rownum = 1;
select l1.delivery_id, l1.delivery_leg_id, s.trip_id
from wsh_delivery_legs l1, wsh_delivery_legs l2, wsh_new_deliveries d, wsh_trip_stops s
where l1.parent_delivery_leg_id = l2.delivery_leg_id
and l2.delivery_id = p_parent_delivery_id
and d.delivery_id = l1.delivery_id
and d.status_code = 'OP'
and l1.pick_up_stop_id = s.stop_id;
select parent_delivery_detail_id
from wsh_delivery_assignments
where delivery_id = p_delivery_id
and delivery_id is not null
and parent_delivery_detail_id IS NOT NULL
and type = 'C'
and rownum = 1;
l_delete_parent_dels wsh_util_core.id_tab_type;
delete wsh_delivery_assignments
where type = 'C'
and delivery_id in l_assigned_dels_tab(i);
update wsh_delivery_assignments
set type = 'S'
where delivery_id in l_assigned_dels_tab(i)
and type = 'O';
update wsh_delivery_legs
set parent_delivery_leg_id = NULL
where delivery_leg_id = l_child_legs_tab(i);
l_delete_parent_dels(m) := l_distinct_parent_dels_tab(i);
WSH_DEBUG_SV.log(l_module_name,'l_delete_parent_dels ', l_delete_parent_dels(m));
IF l_delete_parent_dels.count > 0 THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.Delete',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_UTIL_CORE.Delete(
p_type => 'DLVY',
p_rows => l_delete_parent_dels,
p_caller => p_caller,
x_return_status => l_return_status);
p_update_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_gross_weight => l_gross_weight,
x_net_weight => l_net_weight,
x_volume => l_volume,
x_return_status => l_return_status);
select d.delivery_id
from wsh_delivery_legs l1, wsh_delivery_legs l2, wsh_new_deliveries d
where l1.parent_delivery_leg_id = l2.delivery_leg_id
and l2.delivery_id = p_del_id
and l1.delivery_id = d.delivery_id
and d.status_code = 'OP'
and rownum = 1;
select count(*)
from wsh_delivery_assignments da,
wsh_delivery_details dd
where da.type = 'C'
and da.parent_delivery_detail_id IS NULL
and da.delivery_detail_id = dd.delivery_detail_id
and dd.container_flag = 'Y'
and da.parent_delivery_id = p_delivery_id
and da.parent_delivery_id IS NOT NULL;
select count(*)
from wsh_delivery_assignments
where type = 'S'
and delivery_id = p_delivery_id;
select organization_id
from wsh_new_deliveries
where delivery_id = p_del_id;
UPDATE wsh_new_deliveries
SET status_code = 'CO',
number_of_lpn = decode(number_of_lpn, NULL, l_number_of_lpn_tab(m), number_of_lpn)
WHERE delivery_id = l_co_consol_del_tab(m);
SELECT parent.ultimate_dropoff_location_id pu_location,
stop.planned_departure_date pu_date,
child.ultimate_dropoff_location_id do_location,
child.ultimate_dropoff_date do_date,
child.ship_method_code,
child.carrier_id,
child.service_level,
child.mode_of_transport,
child.ignore_for_planning
FROM wsh_new_deliveries child,
wsh_new_deliveries parent,
wsh_delivery_legs child_leg,
wsh_delivery_legs parent_leg,
wsh_trip_stops stop
WHERE child_leg.delivery_id = p_delivery_id
AND child_leg.parent_delivery_leg_id = parent_leg.delivery_leg_id
AND child_leg.delivery_id = child.delivery_id
AND parent_leg.delivery_id = parent.delivery_id
AND child_leg.drop_off_stop_id = stop.stop_id
AND child.ultimate_dropoff_location_id <> parent.ultimate_dropoff_location_id;
SELECT stop.trip_id
FROM wsh_delivery_legs leg,
wsh_trip_stops stop
WHERE leg.parent_delivery_leg_id IS NULL
AND leg.delivery_id = p_delivery_id
AND leg.pick_up_stop_id = stop.stop_id;
l_trip_info_tab.DELETE;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.Create_Update_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_TRIPS_GRP.Create_Update_Trip(
p_api_version_number => l_api_version_number,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trip_info_tab => l_trip_info_tab,
p_in_rec => l_trip_in_rec,
x_out_tab => l_trip_out_rec_tab);
SELECT 1
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE wda.delivery_id = p_del_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.container_flag = 'N';