[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Ordered_Quantity(
p_changed_attribute IN WSH_INTERFACE.ChangedAttributeRecType
, p_source_code IN VARCHAR2
, p_action_flag IN VARCHAR2
, p_wms_flag IN VARCHAR2 DEFAULT 'N'
, p_context IN VARCHAR2 DEFAULT NULL
-- determines context of quantity update:
-- 'OVERPICK' = overpick normalization (bug 2942655 / 2936559)
-- NULL = normal order line quantity update
, x_return_status OUT NOCOPY VARCHAR2
)
IS
-- R12, X-dock
-- If 'Released to Warehouse' lines have to be deleted from WSH, first line with NULL MOL should be
-- reduced/deleted and then either of details from Inventory or X-dock can be picked
-- Add nvl(move_order_line_id,0) asc to ORDER BY clause
-- muom
CURSOR C_Old_Line(c_fulfillment_base IN varchar2) is
SELECT wdd.delivery_detail_id,
wdd.serial_number,
wdd.transaction_temp_id,
wdd.source_line_id,
wdd.pickable_flag,
wdd.move_order_line_id,
wdd.ship_from_location_id,
wdd.organization_id,
wdd.inventory_item_id,
wdd.subinventory,
wdd.revision,
wdd.locator_id,
wdd.lot_number,
wdd.released_status,
wdd.requested_quantity,
wdd.picked_quantity,
wdd.cancelled_quantity,
wdd.shipped_quantity,
wdd.requested_quantity2,
wdd.picked_quantity2,
wdd.cancelled_quantity2,
wdd.shipped_quantity2,
wdd.ship_tolerance_above,
wda.parent_delivery_detail_id,
wda.delivery_assignment_id,
wnd.planned_flag,
wnd.delivery_id,
nvl(wnd.status_code,'NO') status_code,
-- Included Shipment Batch Id for TPW - Distributed Organization Changes
wdd.shipment_batch_id,
0 serial_quantity,
--OTM R12
wdd.weight_uom_code,
wdd.requested_quantity_uom,
--
wnd.ignore_for_planning, -- OTM R12 : update requested quantity change
wnd.tms_interface_flag, -- OTM R12 : update requested quantity change
wdd.replenishment_status --bug# 6689448 (replenishment project)
FROM wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id (+)
AND wdd.source_line_id = p_changed_attribute.source_line_id
AND wdd.source_code = p_source_code
AND wdd.delivery_detail_id = decode (p_changed_attribute.delivery_detail_id,
FND_API.G_MISS_NUM , wdd.delivery_detail_id ,
p_changed_attribute.delivery_detail_id)
AND wdd.container_flag = 'N'
AND wdd.released_status <> 'D' -- New
AND DECODE(p_context, 'OVERPICK',wdd.requested_quantity,1) > 0 -- bug 2941581. Skip the 0 requested quantities for overpick normalization.
ORDER BY -- TPW - Distributed Organization Changes (Delivery Lines not associated with Shipment Batch has to be processed first)
nvl(wdd.shipment_batch_id,-1) asc,
decode(nvl(wnd.status_code,'NO'),'NO',1,'OP',2,10),
decode(wda.parent_delivery_detail_id,NULL,1,10),
decode(wnd.planned_flag,'N',1,'Y',2,'F',3,10),
decode(wdd.released_status,'N',1,'R',2,'X',3,'B',4,'S',5,'Y',6,10),
nvl(wdd.move_order_line_id,0) asc,
--muom
decode(c_fulfillment_base,'S',nvl(wdd.requested_quantity2,0),nvl(wdd.requested_quantity,0)) asc, -- This will make sure that maximum number of details are
wdd.delivery_detail_id; -- accounted for
SELECT wdd.delivery_detail_id,
wdd.serial_number,
wdd.transaction_temp_id,
wdd.source_line_id,
wdd.pickable_flag,
wdd.move_order_line_id,
wdd.ship_from_location_id,
wdd.organization_id,
wdd.inventory_item_id,
wdd.subinventory,
wdd.revision,
wdd.locator_id,
wdd.lot_number,
wdd.released_status,
wdd.requested_quantity,
wdd.picked_quantity,
wdd.cancelled_quantity,
wdd.shipped_quantity,
wdd.requested_quantity2,
wdd.picked_quantity2,
wdd.cancelled_quantity2,
wdd.shipped_quantity2,
wdd.ship_tolerance_above,
wda.parent_delivery_detail_id,
wda.delivery_assignment_id,
wnd.planned_flag,
wnd.delivery_id,
nvl(wnd.status_code,'NO') status_code,
-- TPW - Distributed Organization Changes
wdd.shipment_batch_id,
sum(to_number(msnt.serial_prefix)) serial_quantity,
--OTM R12
wdd.weight_uom_code,
wdd.requested_quantity_uom,
--
wnd.ignore_for_planning, -- OTM R12 : update requested quantity change
wnd.tms_interface_flag, -- OTM R12 : update requested quantity change
wdd.replenishment_status --bug# 6689448 (replenishment project)
FROM wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda,
mtl_serial_numbers_temp msnt
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id (+)
AND wdd.source_line_id = p_changed_attribute.source_line_id
AND wdd.source_code = p_source_code
AND wdd.delivery_detail_id = decode (p_changed_attribute.delivery_detail_id,
FND_API.G_MISS_NUM , wdd.delivery_detail_id ,
p_changed_attribute.delivery_detail_id)
AND wdd.container_flag = 'N'
AND wdd.released_status <> 'D'
AND wdd.transaction_temp_id = msnt.transaction_temp_id(+)
AND DECODE(p_context, 'OVERPICK',wdd.requested_quantity,1) > 0 -- bug 2941581. Skip the 0 requested quantities for overpick normalization.
GROUP BY wdd.delivery_detail_id,
wdd.serial_number,
wdd.transaction_temp_id,
wdd.source_line_id,
wdd.pickable_flag,
wdd.move_order_line_id,
wdd.ship_from_location_id,
wdd.organization_id,
wdd.inventory_item_id,
wdd.subinventory,
wdd.revision,
wdd.locator_id,
wdd.lot_number,
wdd.released_status,
wdd.requested_quantity,
wdd.picked_quantity,
wdd.cancelled_quantity,
wdd.shipped_quantity,
wdd.requested_quantity2,
wdd.picked_quantity2,
wdd.cancelled_quantity2,
wdd.shipped_quantity2,
wdd.ship_tolerance_above,
wda.parent_delivery_detail_id,
wda.delivery_assignment_id,
wnd.planned_flag,
wnd.delivery_id,
nvl(wnd.status_code,'NO'),
--OTM R12
wdd.weight_uom_code,
wdd.requested_quantity_uom,
--
-- TPW - Distributed Organization Changes
wdd.shipment_batch_id,
wnd.ignore_for_planning, -- OTM R12 : update requested quantity change
wnd.tms_interface_flag, -- OTM R12 : update requested quantity change
wdd.replenishment_status -- bug# 6689448 (replenishment project)
ORDER BY -- TPW - Distributed Organization Changes (Delivery Lines not associated with Shipment Batch has to be processed first)
nvl(wdd.shipment_batch_id,-1) asc,
decode(nvl(wnd.status_code,'NO'),'NO',1,'OP',2,10),
decode(wda.parent_delivery_detail_id,NULL,1,10),
decode(wnd.planned_flag,'N',1,'Y',2,'F',3,10),
decode(wdd.released_status,'N',1,'R',2,'X',3,'B',4,'S',5,'Y',6,10),
nvl(wdd.move_order_line_id,0) asc,
nvl(wdd.requested_quantity,0) - decode(sum(to_number(msnt.serial_prefix)),NULL,decode(wdd.serial_number,NULL,0,1),
sum(to_number(msnt.serial_prefix))) desc,
/*
nvl(wdd.requested_quantity,0) asc, -- This will make sure that maximum number of details are
*/
decode(c_fulfillment_base,'S',nvl(wdd.requested_quantity2,0),nvl(wdd.requested_quantity,0)) asc, -- This will make sure that maximum number of details are
wdd.delivery_detail_id; -- accounted for
SELECT sum(nvl(requested_quantity,0)),sum(nvl(requested_quantity2,0))
FROM wsh_delivery_details
WHERE source_line_id = p_changed_attribute.source_line_id
AND source_code = p_source_code
GROUP BY source_line_id;
SELECT organization_id,inventory_item_id,requested_quantity_uom,requested_quantity_uom2
FROM wsh_delivery_details
WHERE source_line_id = p_changed_attribute.source_line_id
AND source_code = p_source_code
AND rownum < 2;
select serial_number_control_code
from mtl_system_items
where inventory_item_id = c_item_id
and organization_id = c_organization_id ;
SELECT freight_class_cat_set_id, commodity_code_cat_set_id, enforce_ship_set_and_smc --
FROM wsh_shipping_parameters
WHERE organization_id = c_organization_id;
SELECT set_name
FROM oe_sets
WHERE set_id = c_set_id;
SELECT top_model_line_id FROM
wsh_delivery_details WHERE
top_model_line_id = c_top_model_line_id AND
source_code = 'OE' AND
ship_model_complete_flag = 'Y' AND
source_header_id = c_p_source_header_id AND
released_status IN ('S', 'Y', 'C','I') AND
rownum =1;
SELECT primary_uom_code
from mtl_system_items
where inventory_item_id = c_item_id
and organization_id = c_organization_id ;
l_valid_update_quantity NUMBER := 0;
l_valid_update_quantity2 NUMBER := 0; -- muom
reject_update EXCEPTION;
reject_delete EXCEPTION;
delete_detail_failure EXCEPTION;
l_call_update VARCHAR2(1);
l_update_qty number;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'UPDATE_ORDERED_QUANTITY';
WSH_DEBUG_SV.logmsg(l_module_name, 'IN WSH_USA_QUANTITY_PVT.UPDATE_ORDERED_QUANTITY, ACTION = '|| P_ACTION_FLAG );
raise reject_update;
select sum(wdd.requested_quantity)
into l_oke_cancel_qty_allowed
from wsh_delivery_details wdd
where
wdd.source_line_id = p_changed_attribute.source_line_id
and wdd.source_code = p_source_code
and not exists (select 'x' from
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where wda.delivery_detail_id = wdd.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and wnd.status_code in ('CL', 'IT', 'CO'));
UPDATE WSH_DELIVERY_DETAILS
set src_requested_quantity = (src_requested_quantity - l_src_cancel_qty_allowed),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where source_code = p_source_code
and source_line_id = p_changed_attribute.source_line_id;
WSH_DEBUG_SV.log(l_module_name,' After Update- l_src_cancel_qty_allowed : ',l_src_cancel_qty_allowed);
/* csun: the delete details will release the reservation and delete
delivery detail line, freight cost, delivery assignment entry
*/
IF old_delivery_detail_rec.delivery_id is not NULL THEN
i := i+1;
l_details_id.delete;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL' ,WSH_DEBUG_SV.C_PROC_LEVEL);
WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL (
p_delivery_detail_id => old_delivery_detail_rec.delivery_detail_id,
p_primary_quantity => 0, --- WMS will delete the records from replenishment table.
x_return_status => x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
WSH_INTERFACE.Delete_Details(
p_details_id => l_details_id ,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'WSH_INTERFACE.DELETE_DETAILS l_return_status',l_return_status);
raise delete_detail_failure;
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_DELETE_QUANTITY');
RAISE reject_delete;
l_update_qty := old_delivery_detail_rec.requested_quantity + l_changed_detail_quantity;
WSH_DEBUG_SV.logmsg(l_module_name,'l_update_qty is '||l_update_qty);
l_update_qty := wsh_wv_utils.convert_uom(
from_uom => l_requested_quantity_uom2,
to_uom => l_requested_quantity_uom,
quantity => old_delivery_detail_rec.requested_quantity2 + l_changed_line_quantity2,
item_id => old_delivery_detail_rec.inventory_item_id,
org_id => old_delivery_detail_rec.organization_id);
l_changed_detail_quantity := l_update_qty - old_delivery_detail_rec.requested_quantity;
WSH_DEBUG_SV.logmsg(l_module_name,'new l_update_qty '||l_update_qty);
UPDATE wsh_delivery_details SET
requested_quantity = l_update_qty,
requested_quantity2 = old_delivery_detail_rec.requested_quantity2 + l_changed_line_quantity2,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE source_line_id = old_delivery_detail_rec.source_line_id
AND delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
UPDATE wsh_delivery_details SET
requested_quantity = old_delivery_detail_rec.requested_quantity + l_changed_detail_quantity,
requested_quantity2 = old_delivery_detail_rec.requested_quantity2 + l_changed_line_quantity2, -- OPM B2187389
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE source_line_id = old_delivery_detail_rec.source_line_id
AND delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
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_rs_ignored);
l_call_update := 'Y';
l_delivery_id_tab.DELETE;
l_interface_flag_tab.DELETE;
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS) THEN
l_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
l_call_update := 'N';
IF (l_call_update = 'Y') 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');
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATED MOVE ORDER LINE TO NULL FOR NEW DD '||L_DUMMY_DELIVERY_DETAIL_ID );
p_update_flag => 'Y',
x_net_weight => l_net_weight,
x_volume => l_volume,
x_return_status => l_rs_ignored);
/* LG for OPM need to update the inv*/
--HW OPMCONV - Removed code forking
EXIT; -- Exit delivery details loop as for increase in order line quantity the first detail obtained for the
FND_MESSAGE.SET_NAME('WSH', 'WSH_WMS_UPDATE_NOT_ALLOWED');
RAISE reject_update;
FND_MESSAGE.SET_NAME('WSH', 'WSH_WMS_UPDATE_NOT_ALLOWED');
RAISE reject_update;
l_valid_update_quantity := ABS(l_changed_line_quantity) - ABS(l_changed_detail_quantity);
l_valid_update_quantity2 := ABS(l_changed_line_quantity2) - ABS(l_changed_detail_quantity2);
WSH_DEBUG_SV.logmsg(l_module_name, 'VALID UPDATE QUANTITY '||L_VALID_UPDATE_QUANTITY );
WSH_DEBUG_SV.logmsg(l_module_name, 'VALID UPDATE QUANTITY2 '||L_VALID_UPDATE_QUANTITY2 );
IF l_valid_update_quantity > 0 THEN
-- Throw message saying the quantity that can be cancelled
FND_MESSAGE.Set_Name('WSH', 'WSH_VALID_UPDATE_QUANTITY');
FND_MESSAGE.Set_Token('UPDATE_QUANTITY',l_valid_update_quantity);
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_QUANTITY');
IF l_valid_update_quantity2 > 0 THEN
-- Throw message saying the quantity that can be cancelled
FND_MESSAGE.Set_Name('WSH', 'WSH_VALID_UPDATE_QUANTITY2');
FND_MESSAGE.Set_Token('UPDATE_QUANTITY',l_valid_update_quantity2);
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_QUANTITY');
RAISE reject_update;
UPDATE wsh_delivery_details
SET requested_quantity2 = old_delivery_detail_rec.requested_quantity2 - ABS(l_changed_detail_quantity2)
--Added bug 13812257, if requested_quantity2 is decremented then cancelled_quantity2 should be incremented.
, cancelled_quantity2 = (NVL(cancelled_quantity2,0) + ABS(l_changed_detail_quantity2))
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_INV_PVT.DELETE_RESERVATION',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_USA_INV_PVT.delete_reservation( -- For that source header and line id
p_query_input => l_rsv_array(l_counter),
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'delete_reservation l_return_status',l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'DELETE RESERVATION FAILED FOR SOURCE LINE '||P_CHANGED_ATTRIBUTE.SOURCE_LINE_ID );
WHEN reject_update THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
WSH_DEBUG_SV.logmsg(l_module_name,'REJECT_UPDATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:REJECT_UPDATE');
WHEN reject_delete THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
WSH_DEBUG_SV.logmsg(l_module_name,'REJECT_DELETE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:REJECT_DELETE');
WHEN delete_detail_failure THEN
FND_MESSAGE.Set_Name('WSH', 'WSH_DET_DELETE_DET_FAILED');
WSH_DEBUG_SV.logmsg(l_module_name,'DELETE_DETAIL_FAILURE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DELETE_DETAIL_FAILURE');
WSH_DEBUG_SV.logmsg(l_module_name,'DELETE_DETAIL_FAILURE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DELETE_DETAIL_FAILURE');
wsh_util_core.default_handler('WSH_USA_QUANTITY_PVT.Update_Ordered_Quantity',l_module_name);
END Update_Ordered_Quantity;
SELECT wdd.delivery_detail_id
,wdd.released_status
,wdd.move_order_line_id
,wdd.serial_number,transaction_temp_id
,wdd.organization_id
,wda.delivery_id
,wdd.inventory_item_id
,wdd.subinventory
,wdd.revision
,wdd.lot_number
,wdd.locator_id
,wdd.source_header_id
,wdd.picked_quantity
,wdd.picked_quantity2
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.source_line_id = p_source_line_id
AND wdd.source_code = p_source_code
AND wdd.released_status IN ('S','Y')
-- muom
AND (
(p_fulfillment_base = 'P' and wdd.requested_quantity = 0 and wdd.picked_quantity > 0) OR
(p_fulfillment_base = 'S' and wdd.requested_quantity2 = 0 and wdd.picked_quantity2 > 0)
)
AND wda.delivery_detail_id = wdd.delivery_detail_id;
,p_delete_reservations => 'Y'
,p_txn_source_line_id => p_source_line_id
,p_delivery_detail_id => l_delivery_detail_id -- X-dock
);
WSH_USA_INV_PVT.update_serial_numbers(
p_delivery_detail_id => l_delivery_detail_id,
p_serial_number => l_serial_number,
p_transaction_temp_id => l_txn_temp_id,
x_return_status => x_return_status);
WSH_DEBUG_SV.log(l_module_name,'update_serial_numbers x_return_status',x_return_status);
UPDATE wsh_delivery_details set
requested_quantity = 0,
requested_quantity2 = 0,
picked_quantity = NULL,
picked_quantity2 = NULL,
src_requested_quantity = 0,
src_requested_quantity2 = 0,
shipped_quantity = 0,
shipped_quantity2 = 0,
delivered_quantity = 0,
delivered_quantity2 = 0,
quality_control_quantity = 0,
quality_control_quantity2 = 0,
cycle_count_quantity = 0,
cycle_count_quantity2 = 0,
released_status = 'D',
subinventory = NULL,
revision = NULL,
lot_number = NULL,
locator_id = NULL,
cancelled_quantity = 0,
cancelled_quantity2 = 0,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE source_line_id = p_source_line_id
AND source_code = p_source_code
AND released_status in ('S','Y')
AND requested_quantity = 0
AND picked_quantity > 0
RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab; -- Added for DBI Project
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
SELECT wda.delivery_id, wnd.planned_flag,
wnd.ignore_for_planning, -- OTM R12 : cancel quantity
wnd.tms_interface_flag -- OTM R12 : cancel quantity
FROM wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
WHERE wda.delivery_id is not null
AND wda.delivery_detail_id = p_del_det
AND wda.delivery_id = wnd.delivery_id;
l_call_update VARCHAR2(1);
l_update_qty number := 0;
l_update_qty := p_requested_quantity - ABS(p_changed_detail_quantity);
IF (l_update_qty <= 0) THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'l_update_qty '||l_update_qty);
l_update_qty := wsh_wv_utils.convert_uom(
from_uom => p_requested_quantity2_uom,
to_uom => p_requested_quantity_uom,
quantity => p_requested_quantity2 - ABS(p_changed_detail_quantity2),
item_id => p_inventory_item_id,
org_id => p_organization_id);
WSH_DEBUG_SV.logmsg(l_module_name,'new l_update_qty '||l_update_qty);
x_requested_adj_quantity := p_requested_quantity - ABS(p_changed_detail_quantity) - l_update_qty;
UPDATE wsh_delivery_details
SET requested_quantity = p_requested_quantity - ABS(p_changed_detail_quantity)
, requested_quantity2 = p_requested_quantity2 - ABS(p_changed_detail_quantity2)
-- HW OPMCONV - Update Qty2 similar to Qty1
-- requested_quantity2 = p_requested_quantity2 + p_changed_detail_quantity2, -- OPM B2187389
, picked_quantity = l_picked_quantity
, picked_quantity2 = l_picked_quantity2
, cycle_count_quantity = decode(cycle_count_quantity,null,null,0,0,(p_requested_quantity - nvl(p_shipped_quantity,0)) - ABS(p_changed_detail_quantity))
, cycle_count_quantity2 = decode(cycle_count_quantity2,null,null,0,0,(p_requested_quantity2 - nvl(p_shipped_quantity2,0)) - ABS(p_changed_detail_quantity2))
, cancelled_quantity = nvl(cancelled_quantity,0) + ABS(p_changed_detail_quantity)
, cancelled_quantity2 = nvl(cancelled_quantity2,0) + ABS(p_changed_detail_quantity2)
, 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;
UPDATE wsh_delivery_details
SET requested_quantity = p_requested_quantity - ABS(p_changed_detail_quantity)
, requested_quantity2 = p_requested_quantity2 - ABS(p_changed_detail_quantity2)
-- HW OPMCONV - Update Qty2 similar to Qty1
-- requested_quantity2 = p_requested_quantity2 + p_changed_detail_quantity2, -- OPM B2187389
, picked_quantity = l_picked_quantity
, picked_quantity2 = l_picked_quantity2
, shipped_quantity = p_requested_quantity - ABS(p_changed_detail_quantity)
, shipped_quantity2 = p_requested_quantity2 - ABS(p_changed_detail_quantity2)
, cycle_count_quantity = decode(cycle_count_quantity,null,null,0)
, cycle_count_quantity2 = decode(cycle_count_quantity2,null,null,0)
, cancelled_quantity = nvl(cancelled_quantity,0) + ABS(p_changed_detail_quantity)
, cancelled_quantity2 = nvl(cancelled_quantity2,0) + ABS(p_changed_detail_quantity2)
, 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;
UPDATE wsh_delivery_details
SET requested_quantity = l_update_qty
, requested_quantity2 = p_requested_quantity2 - ABS(p_changed_detail_quantity2)
, picked_quantity = l_picked_quantity
, picked_quantity2 = l_picked_quantity2
, cycle_count_quantity = decode(cycle_count_quantity,null,null,0,0,l_update_qty - nvl(p_shipped_quantity,0))
, cycle_count_quantity2 = decode(cycle_count_quantity2,null,null,0,0,(p_requested_quantity2 - nvl(p_shipped_quantity2,0)) - ABS(p_changed_detail_quantity2))
, cancelled_quantity = nvl(cancelled_quantity,0) + p_requested_quantity - l_update_qty
, cancelled_quantity2 = nvl(cancelled_quantity2,0) + ABS(p_changed_detail_quantity2)
, 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;
UPDATE wsh_delivery_details
SET requested_quantity = l_update_qty
, requested_quantity2 = p_requested_quantity2 - ABS(p_changed_detail_quantity2)
, picked_quantity = l_picked_quantity
, picked_quantity2 = l_picked_quantity2
, shipped_quantity = l_update_qty
, shipped_quantity2 = p_requested_quantity2 - ABS(p_changed_detail_quantity2)
, cycle_count_quantity = decode(cycle_count_quantity,null,null,0)
, cycle_count_quantity2 = decode(cycle_count_quantity2,null,null,0)
, cancelled_quantity = nvl(cancelled_quantity,0) + p_requested_quantity - l_update_qty
, cancelled_quantity2 = nvl(cancelled_quantity2,0) + ABS(p_changed_detail_quantity2)
, 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;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL' ,WSH_DEBUG_SV.C_PROC_LEVEL);
WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL (
p_delivery_detail_id => p_delivery_detail_id,
-- muom
p_primary_quantity => l_update_qty,
x_return_status => x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
IF ((p_fulfillment_base = 'P' and nvl(p_changed_detail_quantity, 0) <> 0) OR (p_fulfillment_base = 'S' and p_requested_quantity <> l_update_qty)) AND
l_gc3_is_installed = 'Y' THEN
l_call_update := 'Y';
l_delivery_id_tab.DELETE;
l_interface_flag_tab.DELETE;
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS) THEN
l_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
l_call_update := 'N';
IF (l_call_update = 'Y') 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_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_details SET
requested_quantity2 = p_requested_quantity2 + p_changed_detail_quantity2 -- OPM B2187389
WHERE delivery_detail_id = p_delivery_detail_id;
IF (p_fulfillment_base = 'P') OR (p_fulfillment_base = 'S' and p_requested_quantity <> l_update_qty) THEN
-- J: W/V Changes
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.Detail_Weight_Volume',WSH_DEBUG_SV.C_PROC_LEVEL);
p_update_flag => 'Y',
p_post_process_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_net_weight => l_net,
x_volume => l_volume,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'p_reduction_quantity',p_requested_quantity - l_update_qty);
p_reduction_quantity => p_requested_quantity - l_update_qty,
p_sec_reduction_quantity => ABS(p_changed_detail_quantity2),
p_txn_source_line_id => p_source_line_id,
p_delivery_detail_id => p_delivery_detail_id -- X-dock changes
);
update wsh_delivery_details set
requested_quantity = 0,
requested_quantity2 = 0,
picked_quantity = NULL,
picked_quantity2 = NULL,
src_requested_quantity = 0,
src_requested_quantity2 = 0,
shipped_quantity = 0,
shipped_quantity2 = 0,
delivered_quantity = 0,
delivered_quantity2 = 0,
quality_control_quantity = 0,
quality_control_quantity2 = 0,
cycle_count_quantity = 0,
cycle_count_quantity2 = 0,
released_status = 'D',
subinventory = NULL,
revision = NULL,
lot_number = NULL,
locator_id = NULL,
cancelled_quantity = nvl(cancelled_quantity,0) + p_requested_quantity,
cancelled_quantity2 = nvl(cancelled_quantity2,0) + p_requested_quantity2,
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;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL' ,WSH_DEBUG_SV.C_PROC_LEVEL);
WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL (
p_delivery_detail_id => p_delivery_detail_id,
p_primary_quantity => 0,
x_return_status => x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
p_update_flag => 'Y',
p_post_process_flag => 'Y',
x_net_weight => l_net,
x_volume => l_volume,
x_return_status => l_return_status);
,p_delete_reservations => 'Y'
,p_txn_source_line_id => p_source_line_id
,p_delivery_detail_id => p_delivery_detail_id --X-dock
);
,p_delete_reservations => 'N'
,p_txn_source_line_id => p_source_line_id
,p_delivery_detail_id => p_delivery_detail_id -- X-dock
);
WSH_DEBUG_SV.logmsg(l_module_name, l_serial_quantity||' Serial Number(s) have to be deleted for dd '
||p_delivery_detail_id);
WSH_DEBUG_SV.logmsg(l_module_name, l_serial_quantity||' Serial Number(s) have to be deleted for dd '
||p_delivery_detail_id);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_INV_PVT.UPDATE_SERIAL_NUMBERS',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_USA_INV_PVT.update_serial_numbers(
p_delivery_detail_id => p_delivery_detail_id,
p_serial_number => p_serial_number,
p_transaction_temp_id => p_transaction_temp_id,
x_return_status => x_return_status);
WSH_DEBUG_SV.log(l_module_name,'Update_Serial_Numbers x_return_status',x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_USA_INV_PVT.UPDATE_SERIAL_NUMBERS ' );