[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_cont_item_id
AND container_item_flag = 'Y'
AND organization_id = p_org_id
AND nvl(vehicle_item_flag,'N') = 'N'
AND shippable_item_flag = 'Y';
SELECT Description, Container_Type_Code, weight_uom_code, volume_uom_code,
minimum_fill_percent, maximum_load_weight, internal_volume, primary_uom_code,
-- J: W/V Changes
unit_weight, unit_volume
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = v_cont_item_id
AND container_item_flag = 'Y'
AND organization_id = v_org_id
AND nvl(vehicle_item_flag,'N') = 'N'
AND ((shippable_item_flag = 'Y' AND v_wms_org = 'N')
OR v_wms_org = 'Y') ;
l_last_update_by NUMBER;
SELECT Description, Container_Type_Code, weight_uom_code, volume_uom_code,
minimum_fill_percent, maximum_load_weight, internal_volume, primary_uom_code,
-- J: W/V Changes
unit_weight, unit_volume
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = v_cont_item_id
AND container_item_flag = 'Y'
AND organization_id = v_org_id
AND nvl(vehicle_item_flag,'N') = 'N'
AND shippable_item_flag = 'Y';
SELECT container_name
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_cont_inst_id
AND container_flag = 'Y';
SELECT wsh_delivery_details_s.nextval
FROM sys.dual;
SELECT wsh_delivery_assignments_s.nextval
FROM sys.dual;
l_last_update_by NUMBER;
l_orig_update_cont_value VARCHAR2(2);
SELECT LPN_PREFIX,
LPN_SUFFIX,
LPN_STARTING_NUMBER ,
TOTAL_LPN_LENGTH
FROM mtl_parameters
WHERE ORGANIZATION_ID = v_organization_id;
SELECT gross_weight ,
GROSS_WEIGHT_UOM_CODE,
TARE_WEIGHT_UOM_CODE,
TARE_WEIGHT,
--container_volume,
--CONTAINER_VOLUME_UOM,
content_volume, --filled volume
CONTENT_VOLUME_UOM_CODE ,
locator_id,
subinventory_code,
license_plate_number ,
organization_id,
inventory_item_id
FROM wms_license_plate_numbers
WHERE lpn_id = v_lpn_id;
SELECT wlh.LICENSE_PLATE_NUMBER,
wlh.organization_id,
wlh.inventory_item_id
FROM wms_lpn_histories wlh
WHERE wlh.lpn_id = v_lpn_id
--AND wlh.OPERATION_MODE = 1
AND wlh.lpn_context = 7
AND wlh.SOURCE_TYPE_ID = 1;
SELECT rowid , container_name
from wsh_delivery_details
WHERE delivery_detail_id = v_delivery_detail;
l_orig_update_cont_value := WSH_WMS_LPN_GRP.g_update_to_container;
WSH_WMS_LPN_GRP.g_update_to_container := 'N';
WSH_WMS_LPN_GRP.g_update_to_container := l_orig_update_cont_value;
WSH_WMS_LPN_GRP.g_update_to_container := l_orig_update_cont_value;
WSH_WMS_LPN_GRP.g_update_to_container := l_orig_update_cont_value;
PROCEDURE : Delete_Containers
PARAMETERS : p_cont_tab - table of container instances to be deleted.
x_return_status - return status of API
DESCRIPTION : This procedure in a container instance and
deletes the container. If the containers are not empty or
they are assigned to deliveries that are not open, they will
not be deleted. Also, if the containers are either assigned to
or container other containers packed into it, they will not be
deleted.
------------------------------------------------------------------------------
*/
PROCEDURE Delete_Containers (
p_container_id IN number,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Check_Hierarchy (v_detail_id NUMBER) IS
SELECT 'NOT EMPTY' FROM DUAL
WHERE EXISTS (
SELECT delivery_detail_id
FROM wsh_delivery_assignments
WHERE parent_delivery_detail_id = v_detail_id
AND NVL(type, 'S') in ('C', 'S')
AND rownum < 2
UNION
SELECT delivery_detail_id
FROM wsh_delivery_assignments
WHERE delivery_detail_id = v_detail_id
AND parent_delivery_detail_id IS NOT NULL
AND NVL(type, 'S') in ('C', 'S')
AND rownum < 2);
SELECT container_flag
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_detail_id;
Delete_Det_Error EXCEPTION;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_CONTAINERS';
SAVEPOINT Delete_Cont;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details (
p_rowid => null,
p_delivery_detail_id => p_container_id,
x_return_status => x_return_status);
FND_MESSAGE.SET_NAME('WSH','WSH_CONT_NULL_DELETE');
FND_MESSAGE.SET_NAME('WSH','WSH_CONT_DELETE_NOT_CONT');
FND_MESSAGE.SET_NAME('WSH','WSH_CONT_DELETE_NOT_EMPTY');
FND_MESSAGE.SET_NAME('WSH','WSH_CONT_DELETE_ERROR');
ROLLBACK to Delete_Cont;
WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_ACTIONS.Delete_Containers',l_module_name);
END Delete_Containers;
PROCEDURE : Update_Container
PARAMETERS : p_container_name - new container name that needs to be assigned
to the existing container.
p_container_instance_id - the delivery detail id for the
container that needs to be updated.
p_old_cont_name - exisiting container name for the container,
to be used only if container instance id in the input parameter
is null.
x_return_status - return status of API
DESCRIPTION : This procedure takes in a new container name and existing
container information like the delivery detail id and existing
container name that needs to be updated. The API checks to see
if the container that is being updated is assigned to a closed,
confirmed or in-transit delivery. If it is, no update is
allowed - if not, only the container name can be updated.
------------------------------------------------------------------------------
*/
--THIS PROCEDURE IS OBSOLETE
PROCEDURE Update_Container (
p_container_name IN VARCHAR2,
p_container_instance_id IN NUMBER,
p_old_cont_name IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
END Update_Container;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE parent_delivery_detail_id = v_cont_instance_id
AND rownum < 2;
SELECT inventory_item_id, master_serial_number
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = p_container_instance_id
AND container_flag = 'Y';
SELECT nvl(detail_container_item_id, master_container_item_id),
source_line_id, source_header_id,source_code
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_detail_id
AND container_flag = 'N';
-- now update the container grouping attribute columns with the
-- attributes from the delivery details (only for first line).
-- we need to do this regardless of the fact the the line may have
-- grouping attributes populated as in the case of updating line direction
-- of a container already assigned to a delivery.
IF NOT l_upd_flag THEN
--dbms_output.put_line('calling update cont hierarchy with ' || p_container_instance_id);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.UPDATE_CONT_HIERARCHY',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_container_actions.Update_Cont_Hierarchy (
l_del_detail_id,
NULL,
p_container_instance_id,
x_return_status);
UPDATE WSH_DELIVERY_DETAILS
SET master_serial_number = l_master_serial_number
WHERE delivery_detail_id = l_del_detail_id;
SELECT minimum_fill_percent
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = p_container_instance_id
AND container_flag = 'Y';
SELECT container_flag, serial_number, master_serial_number, inventory_item_id
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_detail_id;
SELECT wda.parent_delivery_detail_id container_instance_id, wda.delivery_id,
wddp.organization_id, nvl(wddp.line_direction,'O'), -- K LPN CONV. rv
wddp.container_flag -- K: MDC
FROM wsh_delivery_assignments wda,
wsh_delivery_details wddp -- K LPN CONV. rv
WHERE wda.delivery_detail_id = v_detail_id
AND wda.parent_delivery_detail_id = wddp.delivery_detail_id(+)
AND NVL(wda.type, 'S') in ('S', 'C');
select organization_id
from wsh_delivery_details
where delivery_detail_id = p_cnt_inst_id;
l_sync_tmp_rec.operation_type := 'UPDATE';
UPDATE WSH_DELIVERY_DETAILS
SET customer_id = NULL,
ship_to_location_id = NULL,
intmed_ship_to_location_id = NULL,
fob_code = NULL,
freight_terms_code = NULL,
ship_method_code = NULL,
carrier_id = NULL,
mode_of_transport = NULL,
service_level = NULL,
deliver_to_location_id = NULL,
line_direction = DECODE(line_direction,'IO','O',line_direction) -- J-IB-NPARIKH
WHERE delivery_detail_id = l_cont_tab(i);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.UPDATE_CONT_HIERARCHY',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_container_actions.Update_Cont_Hierarchy (
l_cont_tab(i),
NULL,
l_cont_tab(i),
x_return_status);
UPDATE wsh_delivery_details --bug 5165197
SET master_serial_number = NULL
WHERE delivery_detail_id = l_del_detail_rec(i);
l_sync_tmp_rec.operation_type := 'UPDATE';
UPDATE WSH_DELIVERY_DETAILS
SET customer_id = NULL,
ship_to_location_id = NULL,
intmed_ship_to_location_id = NULL,
fob_code = NULL,
freight_terms_code = NULL,
ship_method_code = NULL,
service_level = NULL,
carrier_id = NULL,
mode_of_transport = NULL,
deliver_to_location_id = NULL,
line_direction = DECODE(line_direction,'IO','O',line_direction) -- J-IB-NPARIKH
WHERE delivery_detail_id = l_del_detail_rec(i);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.UPDATE_CONT_HIERARCHY',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_container_actions.Update_Cont_Hierarchy (
l_del_detail_rec(i),
NULL,
l_del_detail_rec(i),
x_return_status);
-- update child containers of the current hierarchy that was
-- unassigned with the serial number of the current top most
-- container..
--
-- K LPN CONV. rv
IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
AND l_cont_line_dir_tab(i) IN ('O','IO')
AND
(
(WSH_WMS_LPN_GRP.GK_WMS_UPD_MISC and l_wms_org = 'Y')
OR
(WSH_WMS_LPN_GRP.GK_INV_UPD_MISC and l_wms_org = 'N')
)
THEN
--{
l_sync_tmp_rec.delivery_detail_id := l_del_detail_rec(i);
l_sync_tmp_rec.operation_type := 'UPDATE';
UPDATE WSH_DELIVERY_DETAILS
SET master_serial_number = serial_number
WHERE delivery_detail_id = l_del_detail_rec(i);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.UPDATE_CHILD_CONTAINERS',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_CONTAINER_UTILITIES.Update_Child_Containers (
l_del_detail_rec(i),
l_master_cont_name,
l_master_serial_number,
x_return_status);
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE parent_delivery_detail_id = v_cont_instance_id
AND rownum < 2;
SELECT organization_id
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_cont_instance_id
AND container_flag = 'Y';
SELECT organization_id, freight_terms_code
FROM WSH_NEW_DELIVERIES
WHERE delivery_id = v_del_id;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE parent_delivery_detail_id = v_cont_instance_id
AND rownum < 2;
SELECT organization_id
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_cont_instance_id
AND container_flag in ('Y', 'C');
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.UPDATE_CHILD_CONTAINERS',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_CONTAINER_UTILITIES.Update_Child_Containers (
p_det_cont_inst_id,
l_master_cont_id,
l_master_serial_number,
x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.UPDATE_CONT_HIERARCHY',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_container_actions.Update_Cont_Hierarchy (
p_par_cont_inst_id,
l_det_del_id,
p_det_cont_inst_id,
x_return_status );
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.UPDATE_CHILD_CONTAINERS',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_CONTAINER_UTILITIES.Update_Child_Containers (
p_det_cont_inst_id,
l_master_cont_id,
l_master_serial_number,
x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.UPDATE_CONT_HIERARCHY',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_container_actions.Update_Cont_Hierarchy (
p_det_cont_inst_id,
l_par_del_id,
p_par_cont_inst_id,
x_return_status );
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.UPDATE_CHILD_CONTAINERS',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_CONTAINER_UTILITIES.Update_Child_Containers (
p_det_cont_inst_id,
l_master_cont_id,
l_master_serial_number,
x_return_status);
-- call update cont attributes to update grouping attr.
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_UTILITIES.UPDATE_CHILD_CONTAINERS',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_CONTAINER_UTILITIES.Update_Child_Containers (
p_det_cont_inst_id,
l_master_cont_id,
l_master_serial_number,
x_return_status);
SELECT maximum_load_weight, internal_volume,
weight_uom_code, volume_uom_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = v_cont_item_id
AND organization_id = v_org_id
-- bug 2828591 - remove the condition since it will prevent user to create container with other status
-- AND inventory_item_status_code = 'Active'
AND container_item_flag = 'Y'
AND nvl(vehicle_item_flag,'N') = 'N'
AND shippable_item_flag = 'Y' ;
SELECT container_item_id,
max_load_quantity
FROM WSH_CONTAINER_ITEMS
WHERE load_item_id = v_inv_item_id
AND master_organization_id = v_organization_id
AND preferred_flag = 'Y';
l_dd_id_tab.DELETE;
g_empty_cont_tab.DELETE(i);
g_empty_cont_tab.DELETE(i);
g_empty_cont_tab.DELETE(i);
l_dd_id_tab.DELETE;
SELECT wdd.inventory_item_id inventory_item_id,
NVL(wdd.shipped_quantity, NVL(wdd.picked_quantity, wdd.requested_quantity)) packed_quantity,
NVL(wdd.shipped_quantity2, NVL(wdd.picked_quantity2, wdd.requested_quantity2)) packed_quantity2,
NVL(wdd.picked_quantity, wdd.requested_quantity) picked_quantity,
NVL(wdd.picked_quantity2, wdd.requested_quantity2) picked_quantity2,
wdd.requested_quantity_uom requested_quantity_uom,
nvl(wdd.wv_frozen_flag,'Y') wv_frozen_flag,
wdd.gross_weight gross_weight,
wdd.net_weight net_weight,
wdd.weight_uom_code weight_uom_code,
wdd.volume volume,
wdd.volume_uom_code volume_uom_code,
wdd.detail_container_item_id detail_container_item_id,
wdd.master_container_item_id master_container_item_id,
wdd.organization_id organization_id,
wdd.source_line_id source_line_id,
wdd.delivery_detail_id delivery_detail_id,
wdd.released_status,
wdd.source_code,
wda.parent_delivery_detail_id parent_delivery_detail_id,
wda.delivery_id delivery_id, -- added delivery id
msi.indivisible_flag indivisible_flag
FROM WSH_DELIVERY_DETAILS wdd,
wsh_delivery_assignments_v wda,
MTL_SYSTEM_ITEMS msi,
WSH_TMP wt
WHERE wdd.delivery_detail_id = wt.id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.container_flag = 'N'
AND wdd.inventory_item_id = msi.inventory_item_id
AND wdd.organization_id = msi.organization_id
ORDER BY wdd.organization_id,wdd.inventory_item_id,source_line_id;
SELECT max_load_quantity
FROM WSH_CONTAINER_ITEMS
WHERE load_item_id = v_inv_item_id
AND container_item_id = v_cont_item_id
AND master_organization_id = v_organization_id;
DELETE FROM wsh_tmp;
INSERT INTO wsh_tmp (id) VALUES(p_del_detail_tab(i));
g_cache_cont_load_info_tab.DELETE;
g_cont_msi_tab.DELETE;
p_update_flag => 'Y',
p_post_process_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_net_weight => l_dd_net_wt,
x_volume => l_dd_volume ,
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_dd_net_wt,
x_volume => l_dd_volume ,
x_return_status => l_return_status);
DELETE FROM wsh_tmp;
SELECT customer_id,ship_to_location_id,
intmed_ship_to_location_id,
fob_code,freight_terms_code,ship_method_code,
mode_of_transport, carrier_id, service_level,
deliver_to_location_id,
NVL(line_direction,'O') line_direction, -- J-IB-NPARIKH
shipping_control, -- J-IB-NPARIKH
NVL(ignore_for_planning,'N') ignore_for_planning
FROM wsh_delivery_details
WHERE delivery_detail_id = v_delivery_detail_id;
SELECT nvl(unit_weight, 0)
FROM wsh_delivery_details
WHERE delivery_detail_id = v_detail_id;
SELECT nvl(ignore_for_planning, 'N'),
nvl(tms_interface_flag, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)
FROM wsh_new_deliveries
WHERE delivery_id = v_delivery_id;
l_index_update NUMBER;
g_empty_cont_tab.DELETE;
g_assign_detail_tab.DELETE;
g_new_container_tab.DELETE;
g_cache_organization_info_tab.DELETE;
g_cont_msi_tab.DELETE;
g_new_contid_tab.DELETE;
l_attr_tab.delete;
g_empty_cont_tab.DELETE;
l_sync_tmp_wms_contlist.operation_type_tbl(l_wms_contlist_cnt) := 'UPDATE';
l_sync_tmp_inv_contlist.operation_type_tbl(l_inv_contlist_cnt) := 'UPDATE';
UPDATE wsh_delivery_assignments_v
SET PARENT_DELIVERY_DETAIL_ID = l_contlist(cnt),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE DELIVERY_DETAIL_ID = l_ddlist(cnt);
l_mdc_id_tab.delete;
WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
p_detail_id_tab => l_mdc_id_tab,
x_return_status => x_return_status);
UPDATE WSH_DELIVERY_DETAILS
SET customer_id = l_tab_cust_id(cnt),
ship_to_location_id = l_tab_ship_location_id(cnt),
intmed_ship_to_location_id = l_tab_intmed_location_id(cnt),
fob_code = l_tab_fob_code(cnt),
freight_terms_code = l_tab_freight_terms_code(cnt),
ship_method_code = l_tab_ship_method_code(cnt),
carrier_id = l_tab_carrier_id(cnt),
service_level = l_tab_service_level(cnt),
mode_of_transport = l_tab_mode_of_transport(cnt),
deliver_to_location_id = l_tab_deliver_location_id(cnt),
line_direction = l_tab_line_direction(cnt),
shipping_control = l_tab_shipping_control(cnt),
ignore_for_planning = l_tab_ignore_for_planning(cnt)
WHERE DELIVERY_DETAIL_ID = l_contlist(cnt);
UPDATE wsh_delivery_assignments_v
SET delivery_id = l_dellist1(cnt),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE DELIVERY_DETAIL_ID = l_contlist1(cnt);
l_index_update := 0;
l_index_update := l_index_update + 1;
l_delivery_id_tab(l_index_update) := l_dellist1(l_index);
l_interface_flag_tab(l_index_update) := WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED;
WSH_DEBUG_SV.log(l_module_name,'l_interface_flag_tab', l_interface_flag_tab(l_index_update));
(WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS)) THEN
l_index_update := l_index_update + 1;
l_delivery_id_tab(l_index_update) := l_dellist1(l_index);
l_interface_flag_tab(l_index_update) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
WSH_DEBUG_SV.log(l_module_name,'l_interface_flag_tab', l_interface_flag_tab(l_index_update));
l_index_update := l_index_update + 1;
l_delivery_id_tab(l_index_update) := l_dellist1(l_index);
l_interface_flag_tab(l_index_update) := NULL;
IF (l_index_update > 0) THEN
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,'Error in WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG '||l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Warning in WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG '||l_return_status);
l_mdc_id_tab.delete;
UPDATE WSH_DELIVERY_DETAILS
SET NET_WEIGHT = l_NetWtlist(cnt),
GROSS_WEIGHT = l_GrossWtlist(cnt),
VOLUME = l_Vollist(cnt),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE DELIVERY_DETAIL_ID = l_ddlist(cnt);
l_group_id_tab.delete;
SELECT mp.wms_enabled_flag
FROM wsh_new_deliveries wnd,
mtl_parameters mp
WHERE wnd.delivery_id = x_delivery_id
AND mp.organization_id = wnd.organization_id;
SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda,
WSH_DELIVERY_DETAILS wdd
WHERE wda.delivery_id = v_delivery_id
AND wda.delivery_id IS NOT NULL
AND wda.parent_delivery_detail_id IS NULL
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.container_flag = 'N';
l_del_rows.delete;
l_del_detail_tab.delete;
packs them into the selected containers in either the full mode
or equal mode. In the full mode, it packs the first container
fully before packing the next. In the equal mode, all lines
are split equally between all the containers and packed
equally between them.
------------------------------------------------------------------------------
*/
PROCEDURE Pack_Multi (
p_cont_tab IN WSH_UTIL_CORE.id_tab_type,
p_del_detail_tab IN WSH_UTIL_CORE.id_tab_type,
p_pack_mode IN VARCHAR2,
p_split_pc IN NUMBER,
x_pack_status OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Get_Cont_Info (v_cont_instance_id NUMBER) IS
SELECT inventory_item_id, organization_id, gross_weight, net_weight,
volume, weight_uom_code, volume_uom_code, fill_percent,
minimum_fill_percent, maximum_load_weight, maximum_volume
FROM wsh_delivery_details
WHERE delivery_detail_id = v_cont_instance_id
AND container_flag = 'Y';
SELECT inventory_item_id, nvl(shipped_quantity, NVL(picked_quantity, requested_quantity)) pack_qty,
requested_quantity_uom, net_weight, volume,
weight_uom_code, volume_uom_code, organization_id,
nvl(detail_container_item_id, master_container_item_id) cont_item_id,
nvl(shipped_quantity2, NVL(picked_quantity2, requested_quantity2)) pack_qty2
FROM wsh_delivery_details
WHERE delivery_detail_id = v_del_detail_id
AND container_flag = 'N';
-- update detail attributes by decrementing shipped quantity
-- (if not null) by the split quantity and updating the shipped
-- quantity of the new line to be eqaul to the split quantity
l_tmp_det_tab(1) := l_split_det_id;
-- update detail attributes by decrementing shipped quantity
-- (if not null) by the split quantity and updating the shipped
-- quantity of the new line to be eqaul to the split quantity
l_tmp_det_tab(1) := l_split_det_id;
PROCEDURE : Update_Shipped_Qty
PARAMETERS : p_delivery_detail_id - delivery detail id of the original line
that was split
p_split_detail_id - delivery detail id of the newly created
split line
p_split_qty - quantity used to split original delivery line
x_return_status - return status of API
DESCRIPTION : This procedure updates the shipped quantities of the original
delivery line that was split and the new line that was created
due to the split. The shipped quantity of the original line is
decremented by split qty and that of the new line is increased
to be equal to the split qty. The updating is done only if the
original shipped quantity is not null.
------------------------------------------------------------------------------
*/
PROCEDURE Update_Shipped_Qty(
p_delivery_detail_id IN NUMBER,
p_split_detail_id IN NUMBER,
p_split_qty IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Get_Ship_Qty (v_det_id NUMBER) IS
SELECT nvl(shipped_quantity,-99)
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_det_id
AND nvl(line_direction,'O') IN ('O','IO')
AND container_flag = 'N';
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_SHIPPED_QTY';
UPDATE WSH_DELIVERY_DETAILS
SET shipped_quantity = l_shp_qty
WHERE delivery_detail_id = p_delivery_detail_id;
UPDATE WSH_DELIVERY_DETAILS
SET shipped_quantity = p_split_qty
WHERE delivery_detail_id = p_split_detail_id;
WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_ACTIONS.Update_Shipped_Qty',l_module_name);
END Update_Shipped_Qty;
g_empty_cont_tab.DELETE(i);
g_empty_cont_tab.DELETE(i);
g_empty_cont_tab.DELETE(i);
SELECT wdd.inventory_item_id inventory_item_id,
wdd.gross_weight gross_weight,
wdd.weight_uom_code weight_uom,
wdd.volume volume,
wdd.volume_uom_code volume_uom,
wdd.organization_id organization_id,
wdd.delivery_detail_id,
wda.parent_delivery_detail_id parent_delivery_detail_id,
nvl(wdd.detail_container_item_id,wdd.master_container_item_id) detail_container_item_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = c_dd_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND source_code = 'WSH';
SELECT max_load_quantity
FROM WSH_CONTAINER_ITEMS
WHERE load_item_id = v_inv_item_id
AND container_item_id = v_cont_item_id
AND master_organization_id = v_organization_id;
g_cache_cont_load_info_tab.DELETE;
g_cont_msi_tab.DELETE;
SELECT nvl(ignore_for_planning, 'N') ignore_for_planning,
organization_id, -- K LPN CON. rv
nvl(line_direction,'O') line_direction -- K LPN CONV. rv
FROM wsh_delivery_details
WHERE delivery_detail_id=p_del_det_id;
g_empty_cont_tab.DELETE;
g_assign_detail_tab.DELETE;
g_new_container_tab.DELETE;
g_cache_organization_info_tab.DELETE;
g_cont_msi_tab.DELETE;
l_attr_tab.delete;
g_empty_cont_tab.DELETE;
l_sync_tmp_rec.operation_type := 'UPDATE';
UPDATE wsh_delivery_details
SET ignore_for_planning=cur.ignore_for_planning
WHERE delivery_detail_id=l_cont_instance_id;
PROCEDURE : Update_Cont_Attributes
PARAMETERS : p_delivery_detail_id - delivery detail id
p_delivery_id - delivery id if container assigned to delivery
p_container_instance_id - delivery detail id for the container
x_return_status - return status of API
DESCRIPTION : This procedure updates the grouping attribute columns of the
container with the grouping attribute values derived from the
delivery line that is input.
------------------------------------------------------------------------------
*/
PROCEDURE Update_Cont_Attributes (
p_delivery_detail_id IN NUMBER,
p_delivery_id IN NUMBER,
p_container_instance_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Get_Detail_Attr (v_detail_id NUMBER) IS
SELECT organization_id, customer_id, ship_to_location_id,
intmed_ship_to_location_id,
fob_code, freight_terms_code, ship_method_code,
mode_of_transport, service_level, carrier_id,
deliver_to_location_id,
NVL(line_direction,'O') line_direction, -- J-IB-NPARIKH
shipping_control, -- J-IB-NPARIKH
vendor_id, -- J-IB-NPARIKH
party_id, -- J-IB-NPARIKH
nvl(ignore_for_planning, 'N') ignore_for_planning --J TP Release
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_detail_id;
SELECT organization_id, customer_id, ultimate_dropoff_location_id,
intmed_ship_to_location_id,
fob_code, freight_terms_code, ship_method_code,
mode_of_transport, service_level, carrier_id,
ultimate_dropoff_location_id deliver_to_location_id,
NVL(shipment_direction,'O') line_direction, -- J-IB-NPARIKH
shipping_control, -- J-IB-NPARIKH
vendor_id, -- J-IB-NPARIKH
party_id, -- J-IB-NPARIKH
nvl(ignore_for_planning,'N') ignore_for_planning --J TP Release
FROM WSH_NEW_DELIVERIES
WHERE delivery_id = v_del_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CONT_ATTRIBUTES';
l_sync_tmp_rec.operation_type := 'UPDATE';
UPDATE WSH_DELIVERY_DETAILS SET
customer_id = l_cust_id,
ship_to_location_id = l_ship_to_loc_id,
intmed_ship_to_location_id = l_intmed_loc_id,
fob_code = l_fob_code,
freight_terms_code = l_freight_terms_code,
ship_method_code = l_ship_method_code,
mode_of_transport = l_mode_of_transport,
carrier_id = l_carrier_id,
service_level = l_service_level,
deliver_to_location_id = l_deliver_to_loc_id,
line_direction = l_line_direction , -- J-IB-NPARIKH
shipping_control = l_shipping_control, -- J-IB-NPARIKH
--vendor_id = l_vendor_id, -- J-IB-NPARIKH
--party_id = l_party_id -- J-IB-NPARIKH
ignore_for_planning = l_ignore_for_planning
WHERE delivery_detail_id = p_container_instance_id;
WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_ACTIONS.Update_Cont_Attributes',l_module_name);
END Update_Cont_Attributes;
SELECT customer_id, intmed_ship_to_location_id,
fob_code, freight_terms_code, ship_method_code,
mode_of_transport, service_level, carrier_id,
deliver_to_location_id
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_detail_id;
PROCEDURE : Update_Cont_Hierarchy
PARAMETERS : p_delivery_detail_id - delivery detail id
p_delivery_id - delivery id if container assigned to delivery
p_container_instance_id - delivery detail id for the container
x_return_status - return status of API
DESCRIPTION : This procedure updates the grouping attribute columns of the
the entire container hierarchy for the specified container
with the grouping attribute values derived from the
delivery line that is input.
------------------------------------------------------------------------------
*/
PROCEDURE Update_Cont_Hierarchy (
p_del_detail_id IN NUMBER,
p_delivery_id IN NUMBER,
p_container_instance_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Get_Child_Containers(v_cont_instance_id NUMBER) IS
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
START WITH delivery_detail_id = v_cont_instance_id
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
SELECT container_flag
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_detail_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CONT_HIERARCHY';
--dbms_output.put_line('calling update cont attr with ' || cont.delivery_detail_id);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.UPDATE_CONT_ATTRIBUTES',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_container_actions.Update_Cont_Attributes (
p_del_detail_id,
p_delivery_id,
cont.delivery_detail_id,
l_return_status);
WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_ACTIONS.Update_Cont_Hierarchy',l_module_name);
END Update_Cont_Hierarchy;
SELECT count(*)
FROM wsh_delivery_assignments_v
START WITH delivery_detail_id = v_detail_id
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
SELECT count(*)
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id IN
(SELECT delivery_detail_id
FROM WSH_DELIVERY_DETAILS
WHERE container_flag = 'Y')
START WITH delivery_detail_id = v_cont_id
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
SELECT organization_id,
nvl(shipment_direction,'O') shipment_direction,
initial_pickup_location_id,
ultimate_dropoff_location_id,
customer_id,
intmed_ship_to_location_id,
fob_code,
freight_terms_code,
ship_method_code,
shipping_control,
vendor_id,
party_id,
name,
nvl(ignore_for_planning,'N') ignore_for_planning
FROM wsh_new_deliveries wnd
WHERE delivery_id = p_delivery_id;
select wnd.delivery_id,
wnd.name,
wnd.status_code,
wda.delivery_detail_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
where wdd.container_name = p_lpn_name
and wdd.container_flag = 'Y'
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.delivery_id = wnd.delivery_id(+);
update wsh_delivery_details
set container_name = SUBSTRB(p_lpn_name || '.' ||l_dup_del_name,1,50)
where delivery_detail_id = l_dup_cnt_id;
UPDATE WSH_DELIVERY_DETAILS
SET lpn_id = p_lpn_id,
shipped_quantity = DECODE(p_transactionType,'ASN',1,shipped_quantity),
received_quantity = DECODE(p_transactionType,'RECEIPT',1,received_quantity),
released_status = DECODE(p_transactionType,'ASN','C','RECEIPT','L','C'),
src_requested_quantity = requested_quantity,
src_requested_quantity_uom = 'Ea' ,
requested_quantity_uom = 'Ea' ,
line_direction = l_dlvy_rec.shipment_direction,
ship_from_location_id = l_dlvy_rec.initial_pickup_location_id,
ship_to_location_id = l_dlvy_rec.ultimate_dropoff_location_id,
customer_id = l_dlvy_rec.customer_id,
intmed_ship_to_location_id = l_dlvy_rec.intmed_ship_to_location_id,
fob_code = l_dlvy_rec.fob_code,
freight_terms_code = l_dlvy_rec.freight_terms_code,
ship_method_code = l_dlvy_rec.ship_method_code,
shipping_control = l_dlvy_rec.shipping_control,
vendor_id = l_dlvy_rec.vendor_id,
party_id = l_dlvy_rec.party_id,
ignore_for_planning = l_dlvy_rec.ignore_for_planning,
tracking_number = NVL(p_waybill_number,tracking_number),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE rowid = l_rowid;
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_delivery_assignments_v
SET delivery_id = p_delivery_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = l_container_instance_id;
UPDATE wsh_delivery_assignments_v
SET parent_delivery_detail_id = l_container_instance_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = p_lines_tbl(i);
SELECT nvl(shipment_direction,'O') shipment_direction,
initial_pickup_location_id,
name
FROM wsh_new_deliveries wnd
WHERE delivery_id = p_delivery_id;
DELETE WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID IN (
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wda.delivery_id = p_delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND NVL(wdd.container_flag,'N') = 'Y'
)
RETURNING delivery_detail_id BULK COLLECT INTO l_lines_tbl;
DELETE wsh_delivery_assignments_v
WHERE delivery_detail_id = l_lines_tbl(i);
UPDATE wsh_delivery_assignments_v
SET parent_delivery_detail_id = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_id = p_delivery_id;
container names and will be deleted once WMS provides the functionality.
lpn conv
*/
PROCEDURE Create_Multiple_Cont_name (
p_cont_name IN VARCHAR2,
p_cont_name_pre IN VARCHAR2,
p_cont_name_suf IN VARCHAR2,
p_cont_name_num IN NUMBER,
p_cont_name_dig IN NUMBER,
p_quantity IN NUMBER,
x_cont_names OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.v50_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Get_Exist_Cont(v_cont_name VARCHAR2) IS
SELECT NVL(MAX(1),0) FROM DUAL
WHERE EXISTS ( SELECT 1 FROM WSH_DELIVERY_DETAILS
WHERE container_name = v_cont_name
AND container_flag = 'Y');
PROCEDURE Update_child_inv_info
This procedure updates all the containers included in p_container_id
with p_locator_id and p_subinventory (downward in hirearchy)
Prameters
p_container_id
p_locator_id
p_subinventory
x_return_status
------------------------------------------------------------------------*/
--lpn conv
PROCEDURE Update_child_inv_info(p_container_id IN NUMBER,
P_locator_id IN NUMBER,
P_subinventory IN VARCHAR2,
X_return_status OUT NOCOPY VARCHAR2)
IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CHILD_INV_INFO';
SELECT delivery_detail_id
FROM wsh_delivery_details
--WHERE container_flag = 'Y'
WHERE delivery_detail_id IN
(SELECT delivery_detail_id
FROM wsh_delivery_assignments
START WITH parent_delivery_detail_id = v_container_id
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id
)
FOR UPDATE NOWAIT;
SAVEPOINT s_Update_child_inv_info;
UPDATE wsh_delivery_details
SET locator_id = p_locator_id,
subinventory = p_subinventory
WHERE delivery_detail_id = l_container_id;
rollback to s_Update_child_inv_info;
rollback to s_Update_child_inv_info;
WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_ACTIONS.Update_child_inv_info',l_module_name);
END Update_child_inv_info;
select d.container_flag, a.delivery_id, a.parent_delivery_id, d.inventory_item_id, d.organization_id
from wsh_delivery_details d, wsh_delivery_assignments a
where d.delivery_detail_id = p_delivery_detail_id
and d.container_flag in ('Y', 'C')
and a.type in ('C', 'S')
and a.delivery_detail_id = d.delivery_detail_id
and a.parent_delivery_detail_id is null
and a.delivery_id is not null;
select s.trip_id, d2.delivery_id
from wsh_delivery_legs l1, wsh_delivery_legs l2,
wsh_new_deliveries d1, wsh_new_deliveries d2,
wsh_trip_stops s
where l1.delivery_id = p_del_id
and l1.delivery_id = d1.delivery_id -- bug 4891897
and s.stop_id = l1.pick_up_stop_id
and s.stop_location_id = d1.initial_pickup_location_id
and l1.pick_up_stop_id = l2.pick_up_stop_id
and l2.delivery_id = d2.delivery_id
and d2.delivery_type = 'CONSOLIDATION';
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE parent_delivery_detail_id = p_parent_detail_id;
SELECT Container_Type_Code, weight_uom_code, volume_uom_code,
minimum_fill_percent, maximum_load_weight, internal_volume, primary_uom_code,
unit_weight, unit_volume
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = v_cont_item_id
AND container_item_flag = 'Y'
AND organization_id = v_org_id
AND nvl(vehicle_item_flag,'N') = 'N'
AND shippable_item_flag = 'Y';
SELECT trip_id
FROM wsh_trip_stops s, wsh_delivery_legs l
WHERE s.stop_id = l.pick_up_stop_id
AND l.delivery_id = p_delivery_id;
SELECT filled_volume
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = p_container_id;
update wsh_delivery_details
set container_flag = 'C',
ship_to_location_id = l_parent_del_info_rec.ultimate_dropoff_location_id,
intmed_ship_to_location_id = l_parent_del_info_rec.intmed_ship_to_location_id,
customer_id = l_parent_del_info_rec.customer_id,
fob_code = l_parent_del_info_rec.fob_code,
freight_terms_code = l_parent_del_info_rec.freight_terms_code,
ship_method_code = l_parent_del_info_rec.ship_method_code,
carrier_id = l_parent_del_info_rec.carrier_id,
mode_of_transport = l_parent_del_info_rec.mode_of_transport,
service_level = l_parent_del_info_rec.service_level,
deliver_to_location_id = l_parent_del_info_rec.ultimate_dropoff_location_id,
line_direction = 'O'
where delivery_detail_id = p_parent_container_id;
delete from wsh_delivery_assignments
where delivery_detail_id = p_parent_container_id
and type = 'C';
update wsh_delivery_assignments
set parent_delivery_detail_id = NULL,
type = 'O'
where delivery_detail_id = l_child_details_tab(i)
and NVL(type, 'S') = 'S';
update wsh_delivery_assignments
set delivery_id = l_new_parent_delivery_id,
parent_delivery_id = NULL,
type = 'S'
where delivery_detail_id = p_parent_container_id;
update wsh_delivery_assignments
set parent_delivery_detail_id = p_parent_container_id
where delivery_detail_id = p_child_container_id
and type = 'C';
UPDATE wsh_delivery_details
SET filled_volume = l_filled_volume
WHERE delivery_detail_id = p_parent_container_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 wdd1.delivery_detail_id,
wdd2.delivery_detail_id,
wdd1.gross_weight,
wdd1.net_weight,
wdd1.volume,
wdd1.weight_uom_code,
wdd1.volume_uom_code,
wdd1.inventory_item_id,
wdd2.weight_uom_code,
wdd2.volume_uom_code
from wsh_delivery_details wdd1,
wsh_delivery_details wdd2,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where wdd1.delivery_detail_id = p_dd_id
and wda.delivery_detail_id = wdd1.delivery_detail_id
and wda.type = 'C'
and wda.delivery_id = wnd.delivery_id
and wnd.status_code = 'OP'
and wdd1.container_flag in ('Y', 'N')
and wda.parent_delivery_detail_id = wdd2.delivery_detail_id
and wdd2.container_flag = 'C'
order by wdd2.delivery_detail_id;
select delivery_detail_id
from wsh_delivery_assignments
where parent_delivery_detail_id = p_consol_lpn_id
and type = 'C'
and rownum = 1;
update wsh_delivery_assignments
set parent_delivery_detail_id = NULL
where delivery_detail_id = p_delivery_details_tab(i)
and type = 'C';