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
CURSOR C_Old_Line 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,
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 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) 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,
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,
--
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 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
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;
reject_update EXCEPTION;
reject_delete EXCEPTION;
delete_detail_failure EXCEPTION;
l_call_update VARCHAR2(1);
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 );
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;
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);
WSH_DEBUG_SV.logmsg(l_module_name, 'VALID UPDATE QUANTITY '||L_VALID_UPDATE_QUANTITY );
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');
RAISE reject_update;
update wsh_delivery_details set
requested_quantity2 = old_delivery_detail_rec.requested_quantity2 - 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')
AND wdd.requested_quantity = 0
AND wdd.picked_quantity > 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);
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;
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 => (p_requested_quantity - ABS(p_changed_detail_quantity)),
x_return_status => x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
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;
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);
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 ' );