[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT UOM_CODE
FROM mtl_units_of_measure
WHERE base_uom_flag = 'Y' AND
uom_class = x_uom_class;
SELECT weight_uom_class, volume_uom_class
FROM wsh_shipping_parameters
WHERE organization_id = p_organization_id;
p_update_flag IN VARCHAR2,
p_calc_wv_if_frozen IN VARCHAR2 DEFAULT 'Y',
x_net_weight OUT NOCOPY NUMBER,
x_volume OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) AS
CURSOR Item_Net_Wt_Vol(v_delivery_detail_id NUMBER) IS
SELECT convert_uom(
msi.weight_uom_code,
NVL(wdd.weight_uom_code,msi.weight_uom_code),
(NVL(wdd.unit_weight,msi.unit_weight) *
convert_uom(
wdd.requested_quantity_uom,
msi.primary_uom_code,
nvl(wdd.received_quantity, nvl(wdd.shipped_quantity, NVL(wdd.picked_quantity, wdd.requested_quantity))),
wdd.inventory_item_id) ),
wdd.inventory_item_id ) WEIGHT,
convert_uom(
msi.volume_uom_code,
NVL(wdd.volume_uom_code,msi.volume_uom_code),
(NVL(wdd.unit_volume,msi.unit_volume) *
convert_uom(
wdd.requested_quantity_uom,
msi.primary_uom_code,
nvl(wdd.received_quantity, nvl(wdd.shipped_quantity, NVL(wdd.picked_quantity, wdd.requested_quantity))),
wdd.inventory_item_id) ),
wdd.inventory_item_id ) VOLUME,
msi.weight_uom_code,
msi.volume_uom_code,
msi.unit_weight,
msi.unit_volume
FROM wsh_delivery_details wdd,
mtl_system_items msi
WHERE wdd.delivery_detail_id = v_delivery_detail_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND msi.organization_id = wdd.organization_id;
select wdd.inventory_item_id,
wdd.organization_id,
wdd.net_weight,
wdd.volume,
wdd.gross_weight ,
wdd.unit_weight,
wdd.unit_volume,
nvl(wdd.received_quantity, nvl(wdd.shipped_quantity, NVL(wdd.picked_quantity, wdd.requested_quantity))) qty,
wdd.weight_uom_code,
wdd.volume_uom_code,
--lpn conv
wdd.container_flag,
NVL(wdd.wv_frozen_flag,'Y'),
nvl(line_direction,'O') line_direction -- LPN CONV. rv
from wsh_delivery_details wdd
where wdd.delivery_detail_id = v_delivery_detail_id;
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_FLAG',P_UPDATE_FLAG);
IF p_update_flag = 'Y' THEN
--lpn conv
IF NVL(l_frozen_flag,'Y') = 'Y' THEN
IF l_wms_org = 'Y' AND l_container_flag IN ('Y', 'C') THEN
l_frozen_flag := 'N';
l_sync_tmp_rec.operation_type := 'UPDATE';
UPDATE WSH_DELIVERY_DETAILS
SET net_weight = x_net_weight,
volume = x_volume,
gross_weight = x_net_weight,
weight_uom_code = nvl(weight_uom_code,l_item_wt_code),
volume_uom_code = nvl(volume_uom_code,l_item_vol_code),
unit_weight = NVL(unit_weight,l_item_unit_wt),
unit_volume = NVL(unit_volume,l_item_unit_vol),
wv_frozen_flag = decode(l_frozen_flag,NULL,wv_frozen_flag,l_frozen_flag),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE delivery_detail_id = p_delivery_detail_id;
END IF; -- p_update_flag
SELECT wts.trip_id,
wdl.pick_up_stop_id,
wdl.drop_off_stop_id,
wdl.parent_delivery_leg_id
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl
WHERE wdl.delivery_id = p_entity_id
AND wdl.pick_up_stop_id = wts.stop_id;
SELECT wts.stop_id,
wts.departure_gross_weight,
wts.departure_net_weight,
wts.departure_volume
FROM wsh_trip_stops wts
WHERE wts.trip_id = c_trip_id
AND wts.stop_sequence_number < (
select wts1.stop_sequence_number
from wsh_trip_stops wts1
where wts1.stop_id = c_dpoff_stop_id)
AND wts.stop_sequence_number >= (
select wts2.stop_sequence_number
from wsh_trip_stops wts2
where wts2.stop_id = c_pkup_stop_id)
FOR UPDATE NOWAIT -- BugFix 3570954 Added NOWAIT
ORDER BY wts.stop_id, wts.planned_departure_date ASC;
SELECT delivery_id
FROM wsh_delivery_legs
WHERE delivery_leg_id = p_delivery_leg_id;
l_update_wms_org varchar2(10) := 'N';
SELECT departure_gross_weight,
departure_net_weight,
departure_volume,
weight_uom_code,
volume_uom_code,
status_code,
NVL(shipments_type_flag,'O'),
NVL(wv_frozen_flag,'Y'),
pick_up_weight,
pick_up_volume,
drop_off_weight,
drop_off_volume
INTO l_gross_weight,
l_net_weight,
l_volume,
l_wt_uom_code,
l_vol_uom_code,
l_status_code,
l_shipment_type_flag,
l_frozen_flag,
l_pick_up_weight,
l_pick_up_volume,
l_drop_off_weight,
l_drop_off_volume
FROM wsh_trip_stops
WHERE stop_id = p_entity_id
FOR UPDATE NOWAIT;
update wsh_trip_stops
set last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
drop_off_weight = l_drop_off_weight,
drop_off_volume = l_drop_off_volume
where stop_id = p_entity_id;
update wsh_trip_stops
set last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
pick_up_weight = l_pick_up_weight,
pick_up_volume = l_pick_up_volume
where stop_id = p_entity_id;
update wsh_trip_stops
set departure_gross_weight = l_gross_weight,
departure_net_weight = l_net_weight,
departure_volume = l_volume,
departure_fill_percent = l_fill_percent,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
pick_up_weight = l_pick_up_weight,
pick_up_volume = l_pick_up_volume
where stop_id = p_entity_id;
update wsh_trip_stops
set departure_gross_weight = l_gross_weight,
departure_net_weight = l_net_weight,
departure_volume = l_volume,
departure_fill_percent = l_fill_percent,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where stop_id = p_entity_id;
SELECT gross_weight,
net_weight,
volume,
weight_uom_code,
volume_uom_code,
status_code,
NVL(shipment_direction,'O'),
NVL(wv_frozen_flag,'Y')
INTO l_gross_weight,
l_net_weight,
l_volume,
l_wt_uom_code,
l_vol_uom_code,
l_status_code,
l_shipment_direction,
l_frozen_flag
FROM wsh_new_deliveries
WHERE delivery_id = p_entity_id
AND NVL(wv_frozen_flag, 'Y') <> 'Y' -- BugFix 3570954
FOR UPDATE NOWAIT;
update wsh_new_deliveries
set gross_weight = l_gross_weight,
net_weight = l_net_weight,
volume = l_volume,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where delivery_id = p_entity_id;
SELECT gross_weight,
net_weight,
volume,
filled_volume,
parent_delivery_detail_id,
delivery_id,
wdd.weight_uom_code,
wdd.volume_uom_code,
wdd.inventory_item_id,
wdd.container_flag,
wdd.organization_id,
nvl(wdd.wv_frozen_flag,'Y'),
NVL(wda.type, 'S'),
wdd.line_direction
INTO l_gross_weight,
l_net_weight,
l_volume,
l_filled_volume,
l_container_id,
l_delivery_id,
l_wt_uom_code,
l_vol_uom_code,
l_inv_item_id,
--lpn conv
l_container_flag,
l_organization_id,
l_frozen_flag,
-- MDC
l_wda_type,
l_line_direction
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wdd.delivery_detail_id = p_entity_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND NVL(wda.type, 'S') in ('S', 'C')
FOR UPDATE NOWAIT;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit UPDATE_CONTAINER_WT_VOL',WSH_DEBUG_SV.C_PROC_LEVEL);
update_container_wt_vol(
p_container_instance_id => p_entity_id,
p_gross_weight => l_gross_weight,
p_net_weight => l_net_weight,
p_volume => l_volume,
p_filled_volume => l_filled_volume,
p_fill_pc_flag => 'Y',
x_cont_fill_pc => l_cont_fill_pc,
x_return_status => l_return_status);
WSH_WMS_LPN_GRP.g_update_to_containers := 'Y';
FND_MESSAGE.Set_Name('WSH','WSH_UPDATE_WTVOL_FAIL');
WSH_DEBUG_SV.log(l_module_name,'Cannot lock delivery for update',l_delivery_id);
SELECT wda.parent_delivery_detail_id,
wda.delivery_id,
wdd.organization_id,
wdd.container_flag,
wdd.weight_uom_code,
wdd.volume_uom_code,
wdd.inventory_item_id,
--lpn conv
wdd.line_direction,
-- MDC
NVL(wda.type, 'S')
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = p_delivery_detail_id
AND NVL(wda.type, 'S') in ('S', 'C')
AND wdd.delivery_detail_id = wda.delivery_detail_id;
SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda
WHERE wda.parent_delivery_detail_id = c_container_id
AND wda.delivery_detail_id <> p_delivery_detail_id;
SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda
WHERE wda.delivery_id = c_delivery_id
AND wda.delivery_detail_id <> p_delivery_detail_id;
SELECT unit_weight,
unit_volume,
gross_weight,
net_weight,
volume,
filled_volume
FROM wsh_delivery_details
WHERE delivery_detail_id = c_container_id;
SELECT gross_weight,
net_weight,
volume
FROM wsh_new_deliveries
WHERE delivery_id = c_delivery_id
FOR UPDATE NOWAIT; --BugFix 3570954;
l_update_wms_org varchar2(10) := 'N';
SELECT inventory_item_id
FROM wsh_delivery_details
WHERE delivery_detail_id = v_delivery_detail_id;
l_update_wms_org := wsh_util_validate.Check_Wms_Org(l_organization_id);
AND l_update_wms_org = 'Y' THEN --{
IF l_item_id IS NOT NULL THEN --{
WSH_TPA_CONTAINER_PKG.Calc_Cont_Fill_Pc (
p_container_instance_id =>
l_parent_container_id,
p_update_flag => 'Y',
p_fill_pc_basis => NULL,
x_fill_percent => l_cont_fill_pc,
x_return_status => l_return_status
);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Update_Container_Wt_Vol',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_WV_UTILS.update_container_wt_vol(
p_container_instance_id => l_parent_container_id,
p_gross_weight => l_tmp_unit_wt,
p_net_weight => null,
p_volume => l_tmp_unit_vol,
p_filled_volume => null,
p_fill_pc_flag => 'Y',
x_cont_fill_pc => l_tmp_fill_pc,
x_return_status => l_return_status);
UPDATE wsh_new_deliveries
SET gross_weight = null,
net_weight = null,
volume = null,
wv_frozen_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE delivery_id = l_delivery_id;
WSH_DEBUG_SV.log(l_module_name,'Cannot lock delivery for update',l_delivery_id);
SELECT wts.stop_id,
wts.departure_gross_weight,
wts.departure_net_weight,
wts.departure_volume
FROM wsh_trip_stops wts
WHERE wts.trip_id = c_trip_id
AND wts.stop_sequence_number <= (
select wts1.stop_sequence_number
from wsh_trip_stops wts1
where wts1.stop_id = c_dpoff_stop_id)
AND wts.stop_sequence_number >= (
select wts2.stop_sequence_number
from wsh_trip_stops wts2
where wts2.stop_id = c_pkup_stop_id)
FOR UPDATE NOWAIT
ORDER BY wts.stop_id, wts.planned_departure_date ASC;
SELECT wts.trip_id,
wdl.pick_up_stop_id,
wdl.drop_off_stop_id,
wdl.parent_delivery_leg_id
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl
WHERE wdl.delivery_leg_id = p_leg_id
AND wdl.pick_up_stop_id = wts.stop_id
AND p_leg_id is not null
UNION
SELECT wts.trip_id,
wdl.pick_up_stop_id,
wdl.drop_off_stop_id,
wdl.parent_delivery_leg_id
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl
WHERE wdl.delivery_id = p_delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND p_leg_id is null;
SELECT delivery_id
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts
WHERE wts.trip_id = c_trip_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wdl.parent_delivery_leg_id is NULL
AND wdl.delivery_id <> p_delivery_id;
SELECT delivery_id from wsh_delivery_legs
WHERE delivery_leg_id = p_delivery_leg_id;
SELECT delivery_leg_id
FROM wsh_delivery_legs wdl
WHERE wdl.delivery_id = p_delivery_id
AND wdl.pick_up_stop_id = p_stop_id;
SELECT delivery_leg_id
FROM wsh_delivery_legs wdl
WHERE wdl.delivery_id = p_delivery_id
AND wdl.drop_off_stop_id = p_stop_id;
SELECT weight_uom_code,
volume_uom_code,
status_code,
NVL(shipment_direction,'O'),
NVL(wv_frozen_flag,'Y')
INTO l_wt_uom_code,
l_vol_uom_code,
l_status_code,
l_shipment_direction,
l_frozen_flag
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
UPDATE wsh_trip_stops
SET departure_gross_weight = null,
departure_net_weight = null,
departure_volume = null,
departure_fill_percent = null,
wv_frozen_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
pick_up_weight = null,
pick_up_volume = null,
drop_off_weight = null,
drop_off_volume = null
WHERE trip_id = crec.trip_id;
p_update_flag IN VARCHAR2,
p_post_process_flag IN VARCHAR2,
p_calc_wv_if_frozen IN VARCHAR2 DEFAULT 'Y',
x_net_weight OUT NOCOPY NUMBER,
x_volume OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_org_gross_wt number;
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_FLAG',P_UPDATE_FLAG);
SELECT gross_weight,
net_weight,
volume,
NVL(wv_frozen_flag,'Y')
INTO l_org_gross_wt,
l_org_net_wt,
l_org_vol,
l_frozen_flag
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id
AND container_flag = 'N';
p_update_flag => p_update_flag,
p_calc_wv_if_frozen => p_calc_wv_if_frozen,
x_net_weight => x_net_weight,
x_volume => x_volume,
x_return_status => l_return_status);
IF (p_update_flag = 'Y' AND p_post_process_flag = 'Y' AND
( (NVL(x_net_weight,0) <> NVL(l_org_gross_wt,0)) OR
(NVL(x_net_weight,0) <> NVL(l_org_net_wt,0)) OR
(NVL(x_volume,0) <> NVL(l_org_vol,0))
)) THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit DD_WV_Post_Process',WSH_DEBUG_SV.C_PROC_LEVEL);
PROCEDURE Update_Container_Wt_Vol(
p_container_instance_id IN NUMBER,
p_gross_weight IN NUMBER,
p_net_weight IN NUMBER,
p_volume IN NUMBER,
p_filled_volume IN NUMBER,
p_fill_pc_flag IN VARCHAR2,
p_unit_weight IN NUMBER DEFAULT -99,
p_unit_volume IN NUMBER DEFAULT -99,
x_cont_fill_pc OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR c_get_detail_wv IS
SELECT net_weight,
gross_weight,
volume,
filled_volume,
container_flag,
nvl(line_direction, 'O') line_direction, -- LPN CONV. rv
organization_id -- LPN CONV. rv
FROM wsh_delivery_details
WHERE delivery_detail_id = p_container_instance_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CONTAINER_WT_VOL';
l_sync_tmp_rec.operation_type := 'UPDATE';
UPDATE WSH_DELIVERY_DETAILS
SET net_weight = p_net_weight,
gross_weight = p_gross_weight,
volume = p_volume,
filled_volume= p_filled_volume,
unit_weight = NVL(unit_weight,decode(p_unit_weight,-99,NULL,p_unit_weight)),
unit_volume = NVL(unit_volume,decode(p_unit_volume,-99,NULL,p_unit_volume)),
wv_frozen_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE delivery_detail_id = p_container_instance_id;
l_details_marked.delete;
wsh_util_core.default_handler('WSH_WV_UTIL.update_container_wt_vol');
END update_container_wt_vol;
SELECT delivery_detail_id,
LEVEL
FROM wsh_delivery_assignments wda
WHERE LEVEL <= 2
AND NVL(wda.type, 'S') in ('S', 'C')
START WITH wda.delivery_detail_id = p_containerInstanceId
AND NVL(wda.type, 'S') in ('S', 'C')
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
SELECT wdd.container_flag ,
wdd.delivery_detail_id,
wdd.inventory_item_id,
NVL(wdd.wv_frozen_flag,'Y') wv_frozen_flag,
wdd.net_weight,
wdd.gross_weight,
wdd.volume,
wdd.filled_volume,
wdd.weight_uom_code,
wdd.volume_uom_code,
wdd.organization_id ,
nvl(wdd.unit_weight,msi.unit_weight) unit_weight,
nvl(wdd.unit_volume,msi.unit_volume) unit_volume,
WSH_WV_UTILS.convert_uom
(
wdd.requested_quantity_uom,
nvl(msi.primary_uom_code,wdd.requested_quantity_uom), --Bug7165744
nvl(wdd.received_quantity, nvl(wdd.shipped_quantity, NVL(wdd.picked_quantity, wdd.requested_quantity))),
wdd.inventory_item_id
) qty
FROM mtl_system_items msi,
wsh_delivery_details wdd
WHERE msi.inventory_item_id (+) = wdd.inventory_item_id
AND msi.organization_id (+) = wdd.organization_id
AND wdd.delivery_detail_id = p_deliveryDetailId;
WSH_DEBUG_SV.logmsg(l_module_name,'Update W/V for DD '||childInfo_rec.delivery_detail_id);
UPDATE wsh_delivery_details
SET net_weight = l_line_grossWeight_orig,
gross_weight = l_line_grossWeight_orig,
volume = l_line_volume_orig,
unit_weight = NVL(unit_weight,childInfo_rec.unit_weight),
unit_volume = NVL(unit_volume,childInfo_rec.unit_volume),
wv_frozen_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE delivery_detail_id = childInfo_rec.delivery_detail_id;
l_details_marked.delete;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Update_container_Wt_Vol',WSH_DEBUG_SV.C_PROC_LEVEL);
update_container_wt_vol(
p_container_instance_id => p_containerInstanceId,
p_gross_weight => l_grossWeight,
p_net_weight => l_netWeight,
p_volume => l_volume,
p_filled_volume => l_filledVolume,
p_fill_pc_flag => p_fillPcFlag,
p_unit_weight => l_unit_wt,
p_unit_volume => l_unit_vol,
x_cont_fill_pc => x_contFillPc,
x_return_status => l_returnStatus);
FND_MESSAGE.Set_Name('WSH','WSH_UPDATE_WTVOL_FAIL');
SELECT gross_weight,
net_weight,
volume,
fill_percent,
organization_id,
NVL(wv_frozen_flag,'Y')
INTO l_org_gross_wt,
l_org_net_wt,
l_org_vol,
l_org_fill_pc,
l_organization_id,
l_frozen_flag
FROM wsh_delivery_details
WHERE delivery_detail_id = p_container_instance_id
AND container_flag IN ('Y', 'C');
SELECT container_flag
FROM wsh_delivery_details
WHERE delivery_detail_id = x_id;
SELECT wda.delivery_id
FROM WSH_DELIVERY_ASSIGNMENTS wda,
WSH_NEW_DELIVERIES wnd
WHERE wda.delivery_detail_id = p_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wnd.tms_interface_flag IN (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)
AND NVL(wnd.ignore_for_planning, 'N') = 'N';
p_update_flag => p_override_flag,
p_post_process_flag => 'Y',
p_calc_wv_if_frozen => p_calc_wv_if_frozen,
x_net_weight => l_net_weight,
x_volume => l_volume,
x_return_status => x_return_status);
l_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
WSH_NEW_DELIVERIES_PVT.update_tms_interface_flag(
p_delivery_id_tab => l_delivery_id_tab,
p_tms_interface_flag_tab => l_interface_flag_tab,
x_return_status => l_return_status);
WSH_DEBUG_SV.pop(l_module_name, 'UPDATE_TMS_INTERFACE_FLAG ERROR');
p_update_flag IN VARCHAR2,
p_fill_pc_basis IN VARCHAR2,
x_fill_percent OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Get_Cont_Info (v_cont_instance_id NUMBER) IS
SELECT inventory_item_id, gross_weight, net_weight,
-- J: W/V Changes
filled_volume,
nvl(unit_weight,0),
weight_uom_code, volume_uom_code, organization_id,
maximum_load_weight, maximum_volume, fill_percent, container_flag, container_name,
nvl(line_direction, 'O') line_direction, organization_id
FROM wsh_delivery_details
WHERE delivery_detail_id = v_cont_instance_id;
SELECT wda.delivery_detail_id,container_flag,
inventory_item_id,
NVL(received_quantity, -- J-IB-NPARIKH
nvl(shipped_quantity, NVL(picked_quantity, requested_quantity))) pack_qty
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE wda.parent_delivery_detail_id = v_cont_instance_id AND
wdd.delivery_detail_id = wda.delivery_detail_id;
SELECT percent_fill_basis_flag
FROM wsh_shipping_parameters
WHERE organization_id = v_org_id;
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_FLAG',P_UPDATE_FLAG);
IF p_update_flag = 'Y' THEN
-- K LPN CONV. rv
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'line direction', l_line_direction);
l_sync_tmp_rec.operation_type := 'UPDATE';
UPDATE WSH_DELIVERY_DETAILS
SET fill_percent = x_fill_percent
WHERE delivery_detail_id = p_container_instance_id;
SELECT unit_weight, weight_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v_cont_item_id
AND organization_id = v_org_id;
SELECT inventory_item_id, weight_uom_code, organization_id
FROM wsh_delivery_details
WHERE delivery_detail_id = v_cont_inst_id AND
container_flag = 'Y';
SELECT fill_percent, minimum_fill_percent, inventory_item_id, organization_id
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = p_container_instance_id AND
container_flag = 'Y';
p_update_flag IN VARCHAR2,
p_calc_wv_if_frozen IN VARCHAR2,
x_gross_weight OUT NOCOPY NUMBER,
x_net_weight OUT NOCOPY NUMBER,
x_volume OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR loose_detail_wt_vol IS
SELECT wdd.delivery_detail_id dd_id,
wdd.gross_weight gross_wt,
wdd.net_weight net_wt,
wdd.volume vol,
wdd.inventory_item_id,
wdd.weight_uom_code wt_uom,
wdd.volume_uom_code vol_uom
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
wda.parent_delivery_detail_id IS NULL;
SELECT distinct(dd.delivery_detail_id) c_id,
dd.gross_weight gr_wt,
dd.net_weight net_wt,
dd.volume vol,
dd.inventory_item_id inventory_item_id,
dd.weight_uom_code wt_uom,
dd.volume_uom_code vol_uom
FROM wsh_delivery_assignments_v da,
wsh_delivery_details dd
WHERE da.delivery_id = p_delivery_id AND
da.delivery_id IS NOT NULL AND
da.delivery_detail_id = dd.delivery_detail_id AND
dd.container_flag = 'Y' AND
da.parent_delivery_detail_id IS NULL;
SELECT weight_uom_code,
volume_uom_code,
organization_id,
gross_weight,
net_weight,
volume,
NVL(wv_frozen_flag,'Y'),
delivery_type,
NVL(ignore_for_planning, 'N'), -- OTM R12 : packing ECO
tms_interface_flag -- OTM R12 : packing ECO
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT d.weight_uom_code wt_uom,
d.volume_uom_code vol_uom,
d.delivery_id del_id
FROM wsh_delivery_legs l1,
wsh_delivery_legs l2,
wsh_new_deliveries d
WHERE l1.delivery_id = p_delivery_id
AND l1.delivery_leg_id = l2.parent_delivery_leg_id
AND l2.delivery_id = d.delivery_id;
select da.delivery_detail_id,
dd.gross_weight gr_wt,
dd.volume vol,
dd.inventory_item_id inventory_item_id,
dd.weight_uom_code wt_uom,
dd.volume_uom_code vol_uom
from wsh_delivery_details dd, wsh_delivery_assignments da
where dd.delivery_detail_id = da.delivery_detail_id
and dd.container_flag = 'C'
and NVL(da.type, 'S') = 'S'
and da.delivery_id = p_delivery_id
and da.delivery_id is not null;
SELECT dd.delivery_detail_id,
dd.gross_weight gr_wt,
dd.net_weight net_wt,
dd.volume vol,
dd.inventory_item_id inventory_item_id,
dd.weight_uom_code wt_uom,
dd.volume_uom_code vol_uom
FROM wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE da.parent_delivery_detail_id = p_consol_lpn AND
da.delivery_detail_id = dd.delivery_detail_id AND
da.type = 'C';
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_FLAG',P_UPDATE_FLAG);
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
-- setting this will impact the detail_weight_volume,
-- container_weight_volume calls later
G_DELIVERY_TMS_IMPACT := 'N';
p_update_flag => p_update_flag,
p_calc_wv_if_frozen => p_calc_wv_if_frozen,
x_gross_weight => l_gross_weight,
x_net_weight => l_net_weight,
x_volume => l_volume,
x_return_status => x_return_status);
p_override_flag => p_update_flag,
p_fill_pc_flag => 'Y',
p_post_process_flag => 'N',
p_calc_wv_if_frozen => p_calc_wv_if_frozen,
x_gross_weight => l_cont_gross_weight,
x_net_weight => l_cont_net_weight,
x_volume => l_cont_volume,
x_cont_fill_pc => l_cont_fill_pc,
x_return_status => x_return_status);
p_override_flag => p_update_flag,
p_fill_pc_flag => 'Y',
p_post_process_flag => 'N',
p_calc_wv_if_frozen => p_calc_wv_if_frozen,
x_gross_weight => l_cont_gross_weight,
x_net_weight => l_cont_net_weight,
x_volume => l_cont_volume,
x_cont_fill_pc => l_cont_fill_pc,
x_return_status => x_return_status);
p_update_flag => p_update_flag,
p_calc_wv_if_frozen => p_calc_wv_if_frozen,
x_net_weight => l_net_weight,
x_volume => l_volume,
x_return_status => x_return_status);
p_override_flag => p_update_flag,
p_fill_pc_flag => 'Y',
p_post_process_flag => 'N',
p_calc_wv_if_frozen => p_calc_wv_if_frozen,
x_gross_weight => l_cont_gross_weight,
x_net_weight => l_cont_net_weight,
x_volume => l_cont_volume,
x_cont_fill_pc => l_cont_fill_pc,
x_return_status => x_return_status);
IF (p_update_flag = 'Y') THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Updating Del '||p_delivery_id||' With Gross '||x_gross_weight||' Net '||x_net_weight||' Vol '||x_volume);
UPDATE wsh_new_deliveries
SET net_weight = x_net_weight,
gross_weight = x_gross_weight,
volume = x_volume,
wv_frozen_flag = 'N',
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_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED) AND
l_ignore_for_planning = 'N') THEN
l_delivery_id_tab(1) := p_delivery_id;
l_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
WSH_NEW_DELIVERIES_PVT.update_tms_interface_flag(
p_delivery_id_tab => l_delivery_id_tab,
p_tms_interface_flag_tab => l_interface_flag_tab,
x_return_status => l_return_status);
WSH_DEBUG_SV.pop(l_module_name, 'UPDATE_TMS_INTERFACE_FLAG ERROR');
p_update_flag IN VARCHAR2,
p_post_process_flag IN VARCHAR2,
p_calc_wv_if_frozen IN VARCHAR2 DEFAULT 'Y',
x_gross_weight OUT NOCOPY NUMBER,
x_net_weight OUT NOCOPY NUMBER,
x_volume OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_org_gross_wt number;
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_FLAG',P_UPDATE_FLAG);
SELECT gross_weight,
net_weight,
volume,
organization_id,
status_code,
NVL(shipment_direction,'O'),
NVL(wv_frozen_flag,'Y')
INTO l_org_gross_wt,
l_org_net_wt,
l_org_vol,
l_organization_id,
l_status_code,
l_shipment_direction,
l_wv_frozen_flag
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
p_update_flag => p_update_flag,
p_calc_wv_if_frozen => p_calc_wv_if_frozen,
x_gross_weight => x_gross_weight,
x_net_weight => x_net_weight,
x_volume => x_volume,
x_return_status => l_return_status);
IF (p_update_flag = 'Y' AND p_post_process_flag = 'Y' AND
( (NVL(x_gross_weight,0) <> NVL(l_org_gross_wt,0)) OR
(NVL(x_net_weight,0) <> NVL(l_org_net_wt,0)) OR
(NVL(x_volume,0) <> NVL(l_org_vol,0)))) THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Del_WV_Post_Process',WSH_DEBUG_SV.C_PROC_LEVEL);
p_update_flag IN VARCHAR2,
p_calc_wv_if_frozen IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_net_weight NUMBER;
WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_FLAG',P_UPDATE_FLAG);
p_update_flag => p_update_flag,
p_post_process_flag => 'Y',
p_calc_wv_if_frozen => p_calc_wv_if_frozen,
x_gross_weight => l_gross_weight,
x_net_weight => l_net_weight,
x_volume => l_volume,
x_return_status => x_return_status);
SELECT wda.delivery_id,
wnd.weight_uom_code
FROM WSH_DELIVERY_ASSIGNMENTS wda,
WSH_NEW_DELIVERIES wnd
WHERE wda.delivery_detail_id = p_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wda.delivery_id IS NOT NULL
AND NVL(wnd.ignore_for_planning, 'N') = 'N'
AND NVL(wnd.tms_interface_flag, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)
IN (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED);
SELECT delivery_id,
weight_uom_code
FROM WSH_NEW_DELIVERIES
WHERE delivery_id = p_delivery_id
AND NVL(ignore_for_planning, 'N') = 'N'
AND NVL(tms_interface_flag, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)
IN (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_NEW_DELIVERIES_PVT.update_tms_interface_flag(
p_delivery_id_tab => l_delivery_id_tab,
p_tms_interface_flag_tab => l_interface_flag_tab,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG: ' || l_return_status);
SELECT wdd.delivery_detail_id,
nvl(wdd.net_weight,0) net_wt,
nvl(wdd.gross_weight,0) gross_wt,
nvl(wdd.gross_weight,0) - nvl(wdd.net_weight,0) tare_wt,
decode(wdd.weight_uom_code,p_wt_uom,nvl(wdd.net_weight,0),wsh_wv_utils.convert_uom(wdd.weight_uom_code,p_wt_uom,nvl(wdd.net_weight,0),wdd.inventory_item_id)) net_wt_in_parent_uom,
decode(wdd.weight_uom_code,p_wt_uom,nvl(wdd.gross_weight,0)-nvl(wdd.net_weight,0),wsh_wv_utils.convert_uom(wdd.weight_uom_code,p_wt_uom,(nvl(wdd.gross_weight,0)-nvl(wdd.net_weight,0)),wdd.inventory_item_id)) tare_wt_in_parent_uom,
wdd.weight_uom_code,
wdd.inventory_item_id,
wdd.container_flag,
nvl(wdd.line_direction, 'O') line_direction, -- LPN CONV. rv
wdd.organization_id organization_id -- LPN CONV. rv
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE wda.delivery_id = p_delivery_id
AND wda.parent_delivery_detail_id IS NULL
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND nvl(wdd.gross_weight,0) > 0
FOR UPDATE NOWAIT;
SELECT wdd.delivery_detail_id,
nvl(wdd.net_weight,0) net_wt,
nvl(wdd.gross_weight,0) gross_wt,
nvl(wdd.gross_weight,0) - nvl(wdd.net_weight,0) tare_wt,
decode(wdd.weight_uom_code,p_wt_uom,nvl(wdd.net_weight,0),wsh_wv_utils.convert_uom(wdd.weight_uom_code,p_wt_uom,nvl(wdd.net_weight,0),wdd.inventory_item_id)) net_wt_in_parent_uom,
decode(wdd.weight_uom_code,p_wt_uom,nvl(wdd.gross_weight,0)-nvl(wdd.net_weight,0),wsh_wv_utils.convert_uom(wdd.weight_uom_code,p_wt_uom,(nvl(wdd.gross_weight,0)-nvl(wdd.net_weight,0)),wdd.inventory_item_id)) tare_wt_in_parent_uom,
wdd.weight_uom_code,
wdd.inventory_item_id,
wdd.container_flag,
nvl(wdd.line_direction, 'O') line_direction, -- LPN CONV. rv
wdd.organization_id organization_id -- LPN CONV. rv
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE wda.parent_delivery_detail_id IS NOT NULL
AND wda.parent_delivery_detail_id = p_container_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND nvl(wdd.gross_weight,0) > 0
FOR UPDATE NOWAIT;
l_update_to_containers VARCHAR2(2) := WSH_WMS_LPN_GRP.g_update_to_containers;
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 gross_weight = l_dd_upd_gross_wt_tbl(i),
net_weight = l_dd_upd_net_wt_tbl(i),
wv_frozen_flag = 'Y',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE delivery_detail_id = l_dd_upd_tbl(i);
WSH_WMS_LPN_GRP.g_update_to_containers := 'N';
WSH_WMS_LPN_GRP.g_update_to_containers := l_update_to_containers;
WSH_WMS_LPN_GRP.g_update_to_containers := l_update_to_containers;