[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select organization_id
from wsh_delivery_details
where delivery_detail_id = p_cnt_inst_id
and container_flag = 'Y'
and source_code = 'WSH';
SELECT ignore_for_planning
FROM wsh_delivery_details
WHERE delivery_detail_id = p_detail_id;
l_call_update VARCHAR2(1);
UPDATE wsh_delivery_assignments_v
SET delivery_id = decode(p_backorder_rec_tbl(i).planned_flag, 'N', null, delivery_id),
parent_delivery_detail_id = decode(p_org_info_tbl(p_backorder_rec_tbl(i).organization_id).wms_org,
'Y', null,
decode(p_backorder_rec_tbl(i).planned_flag, 'N', null, parent_delivery_detail_id))
WHERE delivery_detail_id = p_backorder_rec_tbl(i).delivery_detail_id
RETURNING delivery_id, parent_delivery_detail_id
INTO l_new_delivery_id, l_new_parent_detail_id;
l_call_update := 'Y';
l_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_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,'Unexpected error in WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG');
WSH_DEBUG_SV.logmsg(l_module_name,'Warning in WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG');
WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
p_detail_id_tab => l_mdc_detail_tab,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record');
WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error in WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record ');
WSH_DEBUG_SV.log(l_module_name,'Updated wsh_delivery_assignments_v for backordered_delivery_detail ' ,p_backorder_rec_tbl(i).delivery_detail_id);
This API updates the delivery detail and calls unassign_backordered_detail for
unassignment and other post processing calls
Parameters :
p_delivery_detail_id - Delivery Detail which is getting backordered
p_requested_quantity - Requested quantity
p_requested_quantity2 - Requested quantity2
p_planned_flag - Delivery is Planned or not (Y/N)
p_wms_enabled_flag - Organization is WMS Organization or not (Y/N)
p_del_batch_id - Delivery's Pick Release Batch Id
x_split_quantity - Split Quantity
x_return_status - Return Status (Success/Unexpected Error)
*/
PROCEDURE backorder_delivery_detail (
p_delivery_detail_id IN NUMBER,
p_requested_quantity IN NUMBER,
p_requested_quantity2 IN NUMBER,
p_planned_flag IN VARCHAR2,
p_wms_enabled_flag IN VARCHAR2,
p_replenishment_status IN VARCHAR2 DEFAULT NULL, --bug# 6689448 (replenishment project)
p_del_batch_id IN NUMBER,
x_split_quantity OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
-- J: W/V Changes
CURSOR C1 is
select parent_delivery_detail_id,
delivery_id
from wsh_delivery_assignments_v
where delivery_detail_id = p_delivery_detail_id;
SELECT oelines.preferred_grade,
wdd.organization_id, -- LPN CONV. rv
nvl(wdd.line_direction,'O') -- LPN CONV. rv
FROM oe_order_lines_all oelines, wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = wdd_id
AND wdd.source_code = 'OE'
AND wdd.source_line_id = oelines.line_id;
UPDATE wsh_delivery_details
SET released_status = DECODE(requested_quantity, 0, 'D', decode(pickable_flag,'Y','B','X')) ,
requested_quantity = DECODE(p_requested_quantity, NULL, requested_quantity, p_requested_quantity),
requested_quantity2 = DECODE(p_requested_quantity2, NULL, requested_quantity2, p_requested_quantity2),
subinventory = original_subinventory,
locator_id = NULL ,
move_order_line_id = DECODE(requested_quantity, 0, move_order_line_id, NULL),--Bug 2114166
picked_quantity = NULL,
picked_quantity2 = NULL,
lot_number = NULL,
preferred_grade = l_oeline_pref_grade,
revision = NULL,
serial_number = NULL,
-- Batch_id is required for additional processing in Release_Batch API and hence this is retained for Pick Release
-- Pick Release sets batch_id as Null for backordered details at the end of Pick Release process
batch_id = WSH_PICK_LIST.G_BATCH_ID,
lpn_id = NULL,
last_update_date = SYSDATE,
last_updated_by = l_user_id,
last_update_login = l_login_id,
transaction_id = NULL,
replenishment_status = p_replenishment_status --bug# 6689448 (replenishment project)
WHERE delivery_detail_id = p_delivery_detail_id
RETURNING requested_quantity, gross_weight, net_weight, volume, organization_id
INTO x_split_quantity, l_gross_weight, l_net_weight, l_volume, l_organization_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);
WSH_DEBUG_SV.log(l_module_name,'Updated wsh_delivery_details for backordered_delivery_detail ' ,p_delivery_detail_id);
PROCEDURE delete_reservation (
p_query_input IN inv_reservation_global.mtl_reservation_rec_type,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_msg_count NUMBER;
delete_reservation_failed EXCEPTION;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'DELETE_RESERVATION';
WSH_DEBUG_SV.logmsg(l_module_name, 'IN WSH_USA_INV_PVT.DELETE_RESERVATION ORDER LINE = '|| P_QUERY_INPUT.DEMAND_SOURCE_LINE_ID );
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_RESERVATION_PUB.DELETE_RESERVATION',WSH_DEBUG_SV.C_PROC_LEVEL);
INV_RESERVATION_PUB.delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => p_query_input
, p_serial_number => l_dummy_sn
);
WSH_DEBUG_SV.log(l_module_name,'delete_reservation x_return_status',x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'RESERVATION DELETED FOR RESERVATION ID : '||P_QUERY_INPUT.RESERVATION_ID );
WSH_DEBUG_SV.logmsg(l_module_name, 'COULD NOT DELETE RESERVATION FOR RESERVATION ID : '||P_QUERY_INPUT.RESERVATION_ID );
FND_MESSAGE.Set_Name('WSH', 'WSH_DELETE_RESERVATION');
raise delete_reservation_failed;
WHEN delete_reservation_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
WSH_DEBUG_SV.logmsg(l_module_name,'DELETE_RESERVATION_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DELETE_RESERVATION_FAILED');
END delete_reservation;
PROCEDURE update_reservation (
p_query_input IN inv_reservation_global.mtl_reservation_rec_type,
p_new_resv_rec IN inv_reservation_global.mtl_reservation_rec_type,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_msg_count NUMBER;
update_reservation_failed EXCEPTION;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'UPDATE_RESERVATION';
WSH_DEBUG_SV.logmsg(l_module_name, 'DEBUGGING IN UPDATE RESER' );
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_RESERVATION_PUB.UPDATE_RESERVATION',WSH_DEBUG_SV.C_PROC_LEVEL);
INV_RESERVATION_PUB.update_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_original_rsv_rec => p_query_input
, p_to_rsv_rec => p_new_resv_rec
, p_original_serial_number => l_dummy_sn -- no serial contorl
, p_to_serial_number => l_dummy_sn -- no serial control
, p_validation_flag => fnd_api.g_true
-- Bug 5099694
, p_over_reservation_flag =>3
);
WSH_DEBUG_SV.log(l_module_name,'INV_RESERVATION_PUB.update_reservation',x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'RESERVATION UPDATED FOR RESERVATION ID : '||TO_CHAR ( P_QUERY_INPUT.RESERVATION_ID ) );
FND_MESSAGE.Set_Name('WSH', 'WSH_UPDATE_RESERVATION');
raise update_reservation_failed;
WHEN update_reservation_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE_RESERVATION_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UPDATE_RESERVATION_FAILED');
Select organization_id
From wsh_delivery_details
Where delivery_detail_id = p_delivery_detail_id;
select pickable_flag
from wsh_delivery_details
where delivery_detail_id = p_delivery_detail;
WSH_DEBUG_SV.logmsg(l_module_name, 'GOING TO DELETE_RESERVATION IN CANCEL_STAGED_RESERVATION ' );
delete_reservation (p_query_input => l_rsv_array(l_counter),
x_return_status => x_return_status );
WSH_DEBUG_SV.log(l_module_name, 'delete_reservation x_return_status',x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE QTY2 '|| l_rsv_new.secondary_reservation_quantity );
update_reservation (p_query_input => l_rsv_array(l_counter),
p_new_resv_rec => l_rsv_new,
x_return_status => x_return_status);
WSH_DEBUG_SV.log(l_module_name, 'update_reservation x_return_status',x_return_status);
SELECT nvl(sum(requested_quantity),0),
nvl(sum(requested_quantity2),0)
FROM wsh_delivery_details
WHERE source_line_id = p_source_line_id
and source_header_id = p_source_header_id
and source_code = p_source_code
and organization_id = p_organization_id
and ((released_status in ('R','B','N'))
OR
(released_status = 'S' and move_order_line_id IS NULL) -- Bug 5185995
);
update_reservation (
p_query_input => l_rsv_array(l_counter),
p_new_resv_rec => l_rsv_new,
x_return_status => x_return_status);
WSH_DEBUG_SV.log(l_module_name,'update_reservation x_return_status',x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'GOING TO DELETE_RESERVATION IN CANCEL_NON__STAGED_RESERVATION ' );
delete_reservation (
p_query_input => l_rsv_array(l_counter),
x_return_status => x_return_status );
WSH_DEBUG_SV.log(l_module_name,'delete_reservation x_return_status',x_return_status);
update_reservation (
p_query_input => l_rsv_array(l_counter),
p_new_resv_rec => l_rsv_new,
x_return_status => x_return_status);
WSH_DEBUG_SV.log(l_module_name,'update_reservation x_return_status',x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_STAGED_RESERVATION_UTIL.UPDATE_STAGED_FLAG',WSH_DEBUG_SV.C_PROC_LEVEL);
INV_STAGED_RESERVATION_UTIL.update_staged_flag
( x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_reservation_id => l_new_rsv_id,
p_staged_flag => p_staged
);
WSH_DEBUG_SV.log(l_module_name,'update_staged_flag l_status',l_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'STAGED FLAG UPDATED FOR RESERVATION ID : '||L_NEW_RSV_ID );
WSH_DEBUG_SV.logmsg(l_module_name, 'COULD NOT UPDATE STAGED FLAG FOR RESERVATION ID : '||L_NEW_RSV_ID );
p_delete_reservations => 'N',
p_txn_source_line_id => p_original_source_line_id,
p_delivery_detail_id => p_delivery_detail_split_rec.delivery_detail_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE wsh_delivery_details
SET released_status = 'R',
move_order_line_id = NULL
WHERE delivery_detail_id = p_delivery_detail_split_rec.delivery_detail_id
RETURNING organization_id -- done for Workflow Project
INTO l_organization_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);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_Transfer_Order_PVT.Update_Txn_Source_Line',WSH_DEBUG_SV.C_PROC_LEVEL);
INV_Transfer_Order_PVT.Update_Txn_Source_Line
(p_line_id => p_delivery_detail_split_rec.move_order_line_id,
p_new_source_line_id => p_split_source_line_id);
PROCEDURE update_serial_numbers(
p_delivery_detail_id IN NUMBER,
p_serial_number IN VARCHAR2,
p_transaction_temp_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR Get_Sl_Num_Ctrl_Cd IS
SELECT msi.serial_number_control_code
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_delivery_detail_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'UPDATE_SERIAL_NUMBERS';
WSH_DEBUG_SV.logmsg(l_module_name, 'IN WSH_USA_INV_PVT.UPDATE_SERIAL_NUMBERS' );
UPDATE wsh_delivery_details SET
serial_number = NULL,
to_serial_number = NULL,
transaction_temp_id = NULL,
shipped_quantity = 0
WHERE delivery_detail_id = p_delivery_detail_id;
WSH_UTIL_CORE.default_handler('WSH_USA_INV_PVT.update_serial_numbers',l_module_name);
END update_serial_numbers;
PROCEDURE Update_Inventory_Info(
p_Changed_Attributes IN WSH_INTERFACE.ChangedAttributeTabType
, x_return_status OUT NOCOPY VARCHAR2)
IS
-- Bug 2657652 : Changed cursor to fetch selective columns instead of *
-- Columns added for bug 4093619(FP-4145867) to fetch delivery grouping
-- attributes
CURSOR C_Old_Detail (p_Counter NUMBER)
IS
SELECT organization_id,
source_header_number,
ship_from_location_id,
delivery_detail_id,
released_status,
move_order_line_id,
requested_quantity,
requested_quantity_uom,
requested_quantity2,
picked_quantity,
picked_quantity2,
inventory_item_id,
subinventory,
locator_id,
lot_number,
-- HW OPMCONV - No need for sublot_number
-- sublot_number,
preferred_grade,
revision,
serial_number,
transaction_temp_id,
ship_set_id,
top_model_line_id,
source_line_number, -- Bug 3481801
-- deliveryMerge
pickable_flag,
-- J inbound logistics --jckwok
nvl(line_direction, 'O') line_direction,
-- J Consolidation of Back Order Delivery Details Enhancement
source_line_id,
tracking_number, -- bug# 3632485
nvl(ignore_for_planning,'N') ignore_for_planning,
customer_id, -- Start of Bug 4093619(FP-4145867)
ship_to_location_id,
intmed_ship_to_location_id,
fob_code,
freight_terms_code,
ship_method_code,
deliver_to_location_id,
shipping_control,
mode_of_transport,
carrier_id,
service_level, -- End of Bug 4093619(FP-4145867)
requested_quantity_uom2 --Bug# 5436033
FROM wsh_delivery_details
WHERE delivery_detail_id = p_Changed_Attributes(p_Counter).delivery_detail_id ;
l_multiple_update VARCHAR2(1);
l_reject_update VARCHAR2(1);
l_update_sub VARCHAR2(1);
l_update_loc VARCHAR2(1);
l_update_rev VARCHAR2(1);
l_update_lot VARCHAR2(1);
l_update_preferred_grade VARCHAR2(1);
l_update_serial_number VARCHAR2(1);
l_update_rel_status VARCHAR2(1);
l_update_quantities VARCHAR2(1);
l_update_transaction_temp_id VARCHAR2(1);
l_update_shipped_quantity VARCHAR2(1);
l_details_to_delete WSH_UTIL_CORE.Id_Tab_Type;
l_delete_count NUMBER := 0;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'UPDATE_INVENTORY_INFO';
SELECT count (wdd.delivery_detail_id), delivery_id
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE
wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = ( SELECT delivery_id
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id = c_delivery_detail_id )
AND wdd.released_status NOT IN ('R', 'X', 'N')
AND wdd.pickable_flag = 'Y'
AND wdd.container_flag = 'N'
GROUP BY delivery_id;
l_multiple_update := 'N';
l_adjust_planned_del_tab.delete;
WSH_DEBUG_SV.logmsg(l_module_name, 'IN UPDATE_INVENTORY_INFO PROCEDURE ...' );
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_REQUEST');
FND_MESSAGE.Set_Token('UPDATE_ATTRIBUTE', 'transaction_temp_id');
l_update_sub := 'N';
l_update_loc := 'N';
l_update_rev := 'N';
l_update_lot := 'N';
l_update_preferred_grade := 'N';
l_update_serial_number := 'N';
l_update_rel_status := 'N';
l_update_transaction_temp_id := 'N';
l_update_shipped_quantity := 'N';
l_update_quantities := 'N';
SELECT wnd.status_code,
wnd.delivery_id,
wnd.planned_flag,
wnd.batch_id
INTO l_delivery_status,
l_delivery_id,
l_planned_flag,
l_del_batch_id
FROM wsh_new_deliveries wnd,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = p_changed_attributes(l_Counter).delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id ;
WSH_DEBUG_SV.logmsg(l_module_name, 'CALLED WITH MULTIPLE UPDATES' );
l_multiple_update := 'Y';
UPDATE WSH_DELIVERY_DETAILS
SET picked_quantity = old_delivery_detail_rec.picked_quantity,
picked_quantity2 = old_delivery_detail_rec.picked_quantity2,
requested_quantity_uom2 = DECODE(l_wms_enabled_flag,
'Y',
DECODE(p_changed_attributes(l_counter).ordered_quantity_uom2,
FND_API.G_MISS_CHAR, requested_quantity_uom2,
p_changed_attributes(l_counter).ordered_quantity_uom2),
requested_quantity_uom2)
WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
UPDATE wsh_delivery_details
SET picked_quantity = decode(requested_quantity, 0, picked_quantity, NULL),
picked_quantity2 = decode(requested_quantity2, 0, picked_quantity2, NULL)
WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
UPDATE WSH_DELIVERY_DETAILS
SET picked_quantity = old_delivery_detail_rec.picked_quantity,
picked_quantity2 = old_delivery_detail_rec.picked_quantity2
WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
UPDATE wsh_delivery_details
SET requested_quantity = LEAST(old_delivery_detail_rec.requested_quantity,
picked_quantity),
requested_quantity2 = LEAST(old_delivery_detail_rec.requested_quantity2,
picked_quantity2)
WHERE delivery_detail_id = old_delivery_detail_rec.delivery_detail_id
RETURNING requested_quantity, requested_quantity2 INTO l_split_quantity, l_split_quantity2;
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_quantity = l_split_quantity,
requested_quantity2 = l_split_quantity2,
picked_quantity = decode(l_split_quantity, 0, picked_quantity, NULL),
picked_quantity2 = decode(l_split_quantity2, 0, picked_quantity2, NULL)
WHERE delivery_detail_id = l_dummy_detail_id;
p_update_flag => 'Y',
p_post_process_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_net_weight => l_net_weight,
x_volume => l_volume,
x_return_status => l_return_status);
UPDATE wsh_delivery_details
SET released_status = 'S'
WHERE delivery_detail_id = l_dummy_detail_id
RETURNING organization_id
INTO l_organization_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);
SELECT inventory_item_id
INTO l_backordered_item_id
FROM wsh_delivery_details
WHERE delivery_detail_id = g_delivery_detail_id;
SELECT inventory_item_id
INTO l_backordered_item_id
FROM wsh_delivery_details
WHERE move_order_line_id = g_move_order_line_id;
SELECT set_name
INTO g_ship_set_name
FROM oe_sets
WHERE set_id = g_ship_set_id
AND set_type = 'SHIP_SET';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTEGRATION.G_BACKORDERREC_TBL.DELETE',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_INTEGRATION.G_BackorderRec_Tbl.DELETE(l_backorder_cnt);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTEGRATION.G_BACKORDERREC_TBL.DELETE',WSH_DEBUG_SV.C_PROC_LEVEL);
SELECT inventory_item_id
INTO l_backordered_item_id
FROM wsh_delivery_details
WHERE delivery_detail_id = g_delivery_detail_id;
SELECT inventory_item_id
INTO l_backordered_item_id
FROM wsh_delivery_details
WHERE move_order_line_id = g_move_order_line_id;
SELECT inventory_item_id
INTO l_top_model_item_id
FROM oe_order_lines_all
WHERE line_id = g_ship_model_id;
WSH_INTEGRATION.G_BackorderRec_Tbl.DELETE(l_backorder_cnt);
l_delete_count := l_delete_count + 1;
l_details_to_delete(l_delete_count) := l_dummy_detail_id;
l_reject_update := 'Y' ;
l_reject_update := 'Y' ;
l_reject_update := 'Y' ;
SELECT parent_delivery_detail_id
INTO l_parent_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id = p_changed_attributes(l_Counter).delivery_detail_id
AND parent_delivery_detail_id IS NOT NULL;
SELECT container_name
INTO l_container_name
FROM wsh_delivery_details
WHERE delivery_detail_id = l_parent_detail_id;
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE TRANSFER LPN ID' );
IF ( l_reject_update = 'Y' ) THEN
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE REJECTED' );
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_REQUEST');
FND_MESSAGE.Set_Token('UPDATE_ATTRIBUTE', 'transfer lpn id');
SELECT license_plate_number ,
inventory_item_id ,
organization_id
INTO l_cont_name ,
l_cont_item_id ,
l_organization_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_Changed_Attributes(l_Counter).transfer_lpn_id ;
SELECT count(*)
INTO l_num_containers
FROM wsh_delivery_Details
WHERE lpn_id = p_Changed_Attributes(l_Counter).transfer_lpn_id
AND container_flag = 'Y'
AND delivery_detail_id <> p_changed_attributes(l_Counter).delivery_detail_id
AND nvl(line_direction , 'O') IN ('O', 'IO') -- J-IB-JCKWOK
--LPN reuse project
AND released_status = 'X'
AND rownum = 1 ;
SELECT delivery_Detail_id, customer_id ,
ship_to_location_id, intmed_ship_to_location_id,
deliver_to_location_id, fob_code,
freight_terms_code, ship_method_code,
nvl(line_direction,'O'),
nvl(ignore_for_planning, 'N'),
shipping_control,
carrier_id,
service_level,
mode_of_transport
INTO l_cont_instance_id, l_customer_id,
l_ship_to_location_id, l_intmed_ship_to_location_id,
l_deliver_to_location_id, l_fob_code,
l_freight_terms_code, l_ship_method_code,
l_line_direction, l_ignore_for_planning,
l_shipping_control, l_carrier_id,
l_service_level, l_mode_of_transport
FROM wsh_Delivery_Details
WHERE lpn_id = p_changed_attributes(l_Counter).transfer_lpn_id
AND container_flag = 'Y'
--LPN reuse project
AND released_status = 'X'
AND nvl(line_direction , 'O') IN ('O', 'IO'); -- J-IB-JCKWOK
UPDATE WSH_DELIVERY_DETAILS
SET master_serial_number = (select master_serial_number
from wsh_delivery_details
where delivery_detail_id = l_cont_instance_id
and container_flag = 'Y')
WHERE delivery_detail_id = p_changed_attributes(l_Counter).delivery_detail_id;
WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Update_Cont_Hierarchy as Grouping attributes do not match');
WSH_CONTAINER_ACTIONS.Update_Cont_Hierarchy (
p_changed_attributes(l_Counter).delivery_detail_id,
NULL,
l_cont_instance_id,
l_return_status);
WSH_DEBUG_SV.log(l_module_name,'Update_Cont_Hierarchy l_return_status',l_return_status);
END IF; -- End of if reject_update
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE INVENTORY' );
IF ( l_reject_update = 'Y' ) THEN
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_REQUEST');
FND_MESSAGE.Set_Token('UPDATE_ATTRIBUTE', 'subinventory');
l_update_sub := 'Y';
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE LOCATOR' );
IF (l_reject_update = 'Y') THEN
-- At least one line is shipped, reject user's request
l_ship_status := 'confirmed, in-transit or closed';
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_REQUEST');
FND_MESSAGE.Set_Token('UPDATE_ATTRIBUTE', 'locator_id');
l_update_loc := 'Y';
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE REVISION' );
IF (l_reject_update = 'Y') THEN
-- At least one line is shipped, reject user's request
l_ship_status := 'confirmed, in-transit or closed';
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_REQUEST');
FND_MESSAGE.Set_Token('UPDATE_ATTRIBUTE', 'revision');
l_update_rev := 'Y';
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE LOT NUMBER' );
IF (l_reject_update = 'Y') THEN
-- At least one line is shipped, reject user's request
l_ship_status := 'confirmed, in-transit or closed';
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_REQUEST');
FND_MESSAGE.Set_Token('UPDATE_ATTRIBUTE', 'lot_number');
l_update_lot := 'Y';
IF (l_reject_update = 'Y') THEN
-- At least one line is shipped, reject user's request
l_ship_status := 'confirmed, in-transit or closed';
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_REQUEST');
FND_MESSAGE.Set_Token('UPDATE_ATTRIBUTE', 'preferred_grade');
l_update_preferred_grade := 'Y';
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE TRANSACTION TEMP ID with transaction_temp_id :'||
l_transaction_temp_id ||
', picked_quantity :'||
p_changed_attributes(l_Counter).picked_quantity );
IF (l_reject_update = 'Y') THEN
-- At least one line is shipped, reject user's request
l_ship_status := 'confirmed, in-transit or closed';
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_REQUEST');
FND_MESSAGE.Set_Token('UPDATE_ATTRIBUTE', 'transaction_temp_id');
l_update_transaction_temp_id := 'Y';
END IF; -- End of if l_reject_update = 'Y'
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE SERIAL NUMBER' );
IF (l_reject_update = 'Y') THEN
-- At least one line is shipped, reject user's request
l_ship_status := 'confirmed, in-transit or closed';
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_REQUEST');
FND_MESSAGE.Set_Token('UPDATE_ATTRIBUTE', 'serial_number');
l_update_serial_number := 'Y';
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE RELEASED STATUS ' );
IF (l_reject_update = 'Y') THEN
-- At least one line is shipped, reject user's request
l_ship_status := 'confirmed, in-transit or closed';
FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_REQUEST');
FND_MESSAGE.Set_Token('UPDATE_ATTRIBUTE', 'released_status');
l_update_rel_status := 'Y';
SELECT source_line_id,
source_code,
organization_id, -- bug 7131800
requested_quantity,
requested_quantity2
FROM wsh_delivery_details
WHERE delivery_detail_id = x_delivery_detail_id;
SELECT ordered_quantity,
order_quantity_uom,
ordered_quantity2,
ordered_quantity_uom2
FROM oe_order_lines_all
WHERE line_id = x_source_line_id
AND x_source_code = 'OE';
l_update_quantities := 'Y';
l_update_quantities := 'N';
WSH_DEBUG_SV.logmsg(l_module_name, ' l_update_transaction_temp_id '||l_update_transaction_temp_id||
' l_update_serial_number '||l_update_serial_number||
' p_changed_attributes(l_counter).picked_quantity '||p_changed_attributes(l_counter).picked_quantity||
' l_new_req_quantity '||l_new_req_quantity||
' old_delivery_detail_rec.requested_quantity '||old_delivery_detail_rec.requested_quantity );
IF ((l_update_transaction_temp_id = 'Y' AND l_update_serial_number = 'Y') OR
(l_update_serial_number = 'Y' AND
((p_changed_attributes(l_counter).picked_quantity = FND_API.G_MISS_NUM AND
NVL(l_new_req_quantity, old_delivery_detail_rec.requested_quantity) > 1) OR
(p_changed_attributes(l_counter).picked_quantity <> FND_API.G_MISS_NUM AND
p_changed_attributes(l_counter).picked_quantity > 1))
)
) THEN
OE_DEBUG_PUB.Add('REJECTING INVENTORY REQUEST');
IF ((l_update_sub = 'Y') OR
(l_update_loc = 'Y') OR
(l_update_rev = 'Y')
-- HW OPMCONV - Removed check for sublot code
OR
(l_update_lot = 'Y') OR
(l_update_preferred_grade = 'Y') OR
(l_update_transaction_temp_id = 'Y') OR
(l_update_serial_number = 'Y') OR
(l_update_rel_status = 'Y')) THEN
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE WSH_DELIVERY_DETAILS' );
l_update_shipped_quantity := 'N';
IF (l_update_transaction_temp_id = 'Y' OR l_update_serial_number = 'Y' or l_wms_enabled_flag = 'Y' ) THEN
l_update_shipped_quantity := 'Y';
UPDATE wsh_delivery_details
SET subinventory = decode(l_update_sub,'Y',p_Changed_Attributes(l_Counter).subinventory,subinventory),
locator_id = decode(l_update_loc, 'Y',p_Changed_Attributes(l_Counter).locator_id,locator_id),
revision = decode(l_update_rev ,'Y',p_Changed_Attributes(l_Counter).revision,revision),
-- HW OPMCONV - No need for sublot_number
-- sublot_number = decode(l_update_sublot,'Y',p_Changed_Attributes(l_Counter).sublot_number,sublot_number),
lot_number = decode(l_update_lot,'Y',p_Changed_Attributes(l_Counter).lot_number,lot_number),
preferred_grade = decode(l_update_preferred_grade,'Y',p_Changed_Attributes(l_Counter).preferred_grade,preferred_grade),
-- Bug 2657652: Added transaction_temp_id and modified Shipped Quantity / Cycle Count Quantity clause
transaction_temp_id = decode(l_update_transaction_temp_id, 'Y',l_transaction_temp_id,transaction_temp_id),
serial_number = decode(l_update_serial_number,'Y',p_Changed_Attributes(l_Counter).serial_number,serial_number),
shipped_quantity = decode(l_update_shipped_quantity,
'Y',decode(p_changed_attributes(l_counter).picked_quantity,
FND_API.G_MISS_NUM, NVL(l_new_req_quantity, requested_quantity),
p_changed_attributes(l_counter).picked_quantity),
shipped_quantity),
shipped_quantity2 = decode(l_update_shipped_quantity,
'Y',decode(p_changed_attributes(l_counter).picked_quantity2,
FND_API.G_MISS_NUM, NVL(l_new_req_quantity2, requested_quantity2),
p_changed_attributes(l_counter).picked_quantity2),
shipped_quantity2),
-- Bug 1851473 : Set backordered qty = 0 in STF
cycle_count_quantity = decode(l_update_shipped_quantity,'Y',0,cycle_count_quantity),
cycle_count_quantity2 = decode(l_update_shipped_quantity,'Y',0,cycle_count_quantity2),
released_status = decode(l_update_rel_status,'Y',decode(pickable_flag,'Y',p_Changed_Attributes(l_Counter).released_status,'X'), released_status),
requested_quantity = NVL(l_new_req_quantity, requested_quantity),
requested_quantity2 = NVL(l_new_req_quantity2, requested_quantity2),
picked_quantity = DECODE(p_changed_attributes(l_counter).picked_quantity, FND_API.G_MISS_NUM, NULL,
p_changed_attributes(l_counter).picked_quantity),
picked_quantity2 = DECODE(p_changed_attributes(l_counter).picked_quantity2, FND_API.G_MISS_NUM, NULL,
p_changed_attributes(l_counter).picked_quantity2),
requested_quantity_uom2 = DECODE(l_wms_enabled_flag,
'Y',
DECODE(p_changed_attributes(l_counter).ordered_quantity_uom2,
FND_API.G_MISS_CHAR, requested_quantity_uom2,
p_changed_attributes(l_counter).ordered_quantity_uom2),
requested_quantity_uom2),
batch_id = DECODE(batch_id, NULL, WSH_PICK_LIST.G_BATCH_ID, batch_id),
last_update_date = SYSDATE,
last_updated_by = l_user_id,
last_update_login = l_login_id,
----Bug#5104847:transaction_id updated only when l_transaction_id is not FND_API.G_MISS_NUM (default value).
transaction_id = DECODE(l_transaction_id,FND_API.G_MISS_NUM,transaction_id,l_transaction_id)
,tracking_number = old_Delivery_detail_rec.tracking_number --Bug# 3632485
WHERE delivery_detail_id = l_dummy_detail_id
RETURNING organization_id -- Done for Workflow Project
INTO l_organization_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);
update wsh_delivery_details
set tracking_number = NULL
where delivery_detail_id = p_Changed_attributes(l_counter).delivery_detail_id;
p_update_flag => 'Y',
p_post_process_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_net_weight => l_net_weight,
x_volume => l_volume,
x_return_status => l_return_status);
IF l_update_quantities = 'Y' THEN
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_QUANTITY_PVT.UPDATE_ORDERED_QUANTITY',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_USA_QUANTITY_PVT.Update_Ordered_Quantity(
p_changed_attribute => l_overpick_rec,
p_source_code => l_source_code,
p_action_flag => 'U',
p_context => 'OVERPICK',
x_return_status => l_return_status
);
IF l_update_rel_status = 'Y'
and old_delivery_detail_rec.pickable_flag = 'Y'
and p_changed_attributes(l_counter).released_status = 'Y'
and l_delivery_id is not NULL
and NVL(WSH_PICK_LIST.G_AUTO_PICK_CONFIRM, 'N') <> 'Y' THEN
l_delivery_already_included := false;
IF l_details_to_delete.count > 0 THEN
--
WSH_INTERFACE.Delete_Details(
p_details_id => l_details_to_delete,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'Delete_Details l_return_status',l_return_status);
<>
IF (l_multiple_update = 'Y') THEN
update_inventory_info(l_changed_attributes, l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'EXITING UPDATE_INVENTORY_INFO PROCEDURE ...' );
wsh_util_core.default_handler('WSH_USA_INV_PVT.Update_Inventory_Info',l_module_name);
END Update_Inventory_Info;
select sum(abs(transaction_quantity)) detailed_quantity,
sum(abs(SECONDARY_TRANSACTION_QUANTITY )) secondary_detailed_quantity
from mtl_material_transactions_temp
where move_order_line_id = p_line_id;
SELECT released_status,
move_order_line_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_detail_id;