[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select released_status,
gross_weight,
net_weight,
volume,
container_flag,
delivery_id
from wsh_Delivery_details wdd,
wsh_delivery_assignments_v wda
where wdd.delivery_detail_id = detail_id
and wdd.delivery_detail_id = wda.delivery_detail_id;
delete_reservations until the p_quantity_to_unreserve
is satisfied.
Parameters : p_delivery_detail_id -> WSH_DELIVERY_DETAILS.delivery_detail_id corresponding to
which the reservations are to be Unreserved
p_quantity_to_unreserve -> Quantity to Unreserve / Cycle Count
p_unreserve_mode -> p_unreserve_mode -> Either 'UNRESERVE' or 'CYCLE_COUNT' or 'RETAIN_RSV'
Added 'RETAIN_RSV' for bug 4721577. WMS wants
reservations to be retained while Backordering
delivery detail lines.'
p_override_retain_ato_rsv -> A 'Y' for this parameter will indicate to override the
Retain ATO REservations profile (yes). In short, this
will indicate that the Unreservation has to take place.
This is Passed as 'Y' from WSH_INTERFACE.delete_details,
an instance where the Reservations have to be reduced.
Brief Logic of Underlying IFs :
Loop Until All Resevation Records are Read; Classify the Rsvn. into Staged and UnStaged Rsvtns.
IF ( Ato Line with Retain Rsvtn.) Don't Update or Delete the REservations
Otherwise -- Reduce Detailed Qty and Prim.Rsvtn Qty and Call [Update Rsvn] OR [Delete Rsvtn]
ELSIF ( LineType = 'staged' and unreserve_mode = 'UNRESERVE' and Reservation is staged)
OR ( unreserve_mode <> 'UNRESERVE' , e.g. Cycle Count ) THEN
Handle: ( rsv.Qty <= Qty. to UnReserve)
IF ( ATO line ) then Update Staged Flag to UnStaged Otherwise (Delete REservation )
OR Cycle_Count depending on the unreserve_mode
Handle: ( rsv.Qty > Qty. to UnReserve)
IF ( ATO Line ) THEN (Transfer Reservation and Update Rsvntn. Staged Flag to UnStaged for the
Split or Transferred Rsvtn.) Otherwise (Update Rsvtn.)
OR (Cycle Count Process) depending on the unreserve_mode
End Loop;
select o.source_document_type_id ,
o.line_id ,
o.header_id ,
o.preferred_grade ,
o.ordered_quantity_uom2 ,
o.ordered_quantity2 ,
o.ato_line_id, -- 2587777
wdd.organization_id,
-- HW 4178299 - Get Item_id and uom
wdd.inventory_item_id,
wdd.requested_quantity_uom,
wdd.subinventory,
wdd.revision,
wdd.locator_id,
-- X-dock changes, add 2 fields
wdd.requested_quantity,
wdd.requested_quantity2,
-- HW OPMCONV - Changed length from 30 to 80
substr(wdd.lot_number,1,80) lot_number ,
wdd.move_order_line_id,
wdd.released_status, -- 2747520
wdd.date_scheduled, -- 2847687
wda.parent_delivery_detail_id --4721577
from wsh_delivery_details wdd ,
oe_order_lines_all o,
wsh_delivery_assignments_v wda
where wdd.delivery_detail_id = p_delivery_Detail_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.source_code = 'OE'
and wdd.source_line_id = o.line_id ;
SELECT wdd.lpn_id
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = c_delivery_detail_id;
SELECT nvl(sum(requested_quantity),0),nvl(sum(requested_quantity2),0)
FROM wsh_delivery_details
WHERE
source_line_id = l_line_rec.line_id
and source_header_id = l_line_rec.header_id
and organization_id = l_line_rec.organization_id
and released_status in ('R','B','N','S');
NULL; -- Don't Update or Delete the REservations if the above criteria (ATO -Unstgd. Rsvn)is met
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_RESERVATION_pub.update_resevation 1',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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_original_rsv_rec => l_rsv_rec,
p_to_rsv_rec => l_rsv_new_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.logmsg(l_module_name, 'AFTER CALLING INVS UPDATE REservation 1: ' || L_RETURN_STATUS );
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_RESERVATION_pub.delete_resevation 1',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 => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv_rec
, p_serial_number => l_dummy_sn
);
WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING INVS DELETE REservation 1: ' || L_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_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_staged_flag => 'N'
, p_reservation_id => l_rsv_rec.reservation_id);
WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING INVS UPDATE STAGED FLAG: ' || L_RETURN_STATUS );
WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING INVS DELETE_RESERVATION' );
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rsv_rec => l_rsv_rec,
p_serial_number => l_dummy_sn
);
WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING INVS DELETE_RESERVATION: ' || L_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_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_staged_flag => 'N'
, p_reservation_id => l_new_rsv_id);
WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER CALLING INVS UPDATE STAGED FLAG: ' || L_RETURN_STATUS );
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_original_rsv_rec => l_rsv_rec,
p_to_rsv_rec => l_rsv_new_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.logmsg(l_module_name, 'AFTER CALLING INVS UPDATE_RESERVATION: ' || L_RETURN_STATUS );
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE parent_delivery_detail_id is null
START WITH delivery_detail_id =p_detail_id
CONNECT BY prior parent_delivery_detail_id = delivery_detail_id
and rownum < 10;
SELECT
COUNT(1) lines_count,
COUNT(DECODE(WDD.pickable_flag,'Y',DECODE(WDD.released_status,'R',NULL,
'X',NULL,
'N',NULL,
1
),
NULL
)
) picked_lines_count
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE
wda.delivery_detail_id= wdd.delivery_detail_id AND
wdd.container_flag = 'N' AND
wda.delivery_id = p_delv_id;
SELECT organization_id
FROM wsh_new_deliveries
WHERE delivery_id=p_delv_id;
l_raise_carrierselect_event BOOLEAN;
/* if detail is not assigned to a container, update WDA. else find topmost container
in the hierarchy (this can be a detail in case of loose details), iterate thru each
level and assign each level to delivery any error in any level => rollback changes*/
SAVEPOINT before_assign_topmost_cont;
--l_raise_carrierselect_event :=TRUE;
-- Purging is required since a New Delivery Workflow will be selected according
-- to the Delivery Detail Assigned to it
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.CHECK_WF_EXISTS',WSH_DEBUG_SV.C_PROC_LEVEL);
IF (l_raise_carrierselect_event) THEN
l_del_ids(1) := p_delivery_id;
x_assign_update OUT NOCOPY BOOLEAN,
x_gross_weight1 OUT NOCOPY NUMBER,
x_gross_weight2 OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR c_get_delivery_id(p_id IN NUMBER) IS
SELECT delivery_id
FROM wsh_delivery_assignments
WHERE delivery_detail_id = p_id;
SELECT nvl(gross_weight, 0)
FROM wsh_delivery_details
WHERE delivery_detail_id = p_id;
x_assign_update := TRUE; -- default to true unless everything on the same delivery.
x_assign_update := FALSE; --same delivery, no need to update
x_assign_update := FALSE; --same delivery, no need to update
WSH_DEBUG_SV.log(l_module_name, 'x_assign_update', x_assign_update);
l_call_update VARCHAR2(1);
l_call_update := 'Y';
l_call_update := 'N';
(WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS)) THEN
l_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
l_call_update := 'N';
l_call_update := 'N'; -- do not update if assign/unassign 0/NULL
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 '||l_return_status);
SELECT ship_to_location_id
FROM wsh_delivery_details
WHERE delivery_detail_id = v_container_id;
SELECT nvl(planned_flag,'N')
FROM wsh_new_deliveries
WHERE delivery_id = v_delivery_id;
SELECT count(*)
FROM wsh_delivery_assignments_v wda
WHERE wda.parent_delivery_detail_id = v_delivery_detail_id And rownum = 1;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE parent_delivery_detail_id is null
START WITH delivery_detail_id =p_detail_id
CONNECT BY prior parent_delivery_detail_id = delivery_detail_id
and rownum < 10;
l_tms_update VARCHAR2(1);
l_assign_update BOOLEAN;
l_assign_update := FALSE; --default assignment tms update to false
/* bug 2691385 and 2655474: avoid unnecessary update if line is already in delivery. */
/****
!!!!! We should NOT assume that the delivery grouping attributes of the delivery match
!!!!! with that of the delivery details.
!!!!! Bugs 2794866, 2397552.
****/
IF (l_del_id_for_container.delivery_id IS not null) and (l_del_id_for_detail.delivery_id is null) THEN
-- OTM R12 : assign delivery detail, this is the case where detail is assigned to the delivery
IF (l_gc3_is_installed = 'Y' AND
nvl(l_del_id_for_container.ignore_for_planning, 'N') = 'N') THEN
Pre_Otm_Assign_Del_Detail
(p_delivery_id => NULL,
p_detail_id => p_detail_id,
p_container1_id => p_parent_detail_id,
p_container2_id => NULL,
p_assignment_type => 'DD2C',
x_delivery_was_empty => l_delivery_was_empty,
x_assign_update => l_assign_update,
x_gross_weight1 => l_gross_weight1,
x_gross_weight2 => l_gross_weight2,
x_return_status => l_return_status);
l_tms_update := 'N';
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED)) THEN
l_tms_update := 'Y';
l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
WSH_DEBUG_SV.log(l_module_name, 'l_tms_update', l_tms_update);
IF (l_tms_update = 'Y') THEN
WSH_DEBUG_SV.log(l_module_name, 'l_new_interface_flag_tab', l_new_interface_flag_tab(1));
UPDATE wsh_new_deliveries
SET shipment_direction = l_del_id_for_detail.line_direction,
service_level = NVL(service_level,l_group_tab(l_group_index).service_level),
mode_of_transport = NVL(mode_of_transport, l_group_tab(l_group_index).mode_of_transport),
carrier_id = NVL(carrier_id, l_group_tab(l_group_index).carrier_id),
-- OTM R12
TMS_INTERFACE_FLAG = decode(l_tms_update, 'Y', l_new_interface_flag_tab(1), nvl(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
TMS_VERSION_NUMBER = decode(l_tms_update, 'Y', l_tms_version_number, nvl(tms_version_number, 1)),
-- End of OTM R12
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where delivery_id = l_del_id_for_container.delivery_id;
IF (l_gc3_is_installed = 'Y' AND l_tms_update = 'Y') THEN
WSH_XC_UTIL.LOG_OTM_EXCEPTION(
p_delivery_info_tab => l_delivery_info_tab,
p_new_interface_flag_tab => l_new_interface_flag_tab,
x_return_status => l_return_status);
UPDATE wsh_delivery_assignments_v
SET parent_delivery_detail_id = p_parent_detail_id,
delivery_id = l_del_id_for_container.delivery_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = p_detail_id;
IF (l_assign_update AND
l_gc3_is_installed = 'Y' AND
nvl(l_del_id_for_container.ignore_for_planning, 'N') = 'N') THEN
IF (l_tms_update = 'Y') THEN
l_tms_interface_flag := l_new_interface_flag_tab(1);
WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
p_detail_id_tab => l_mdc_detail_tab,
x_return_status => x_return_status);
UPDATE wsh_delivery_assignments_v
SET parent_delivery_detail_id = p_parent_detail_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = p_detail_id;
WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
p_detail_id_tab => l_mdc_detail_tab,
x_return_status => x_return_status);
/* bug 2677298 frontport from bug 2655474: update container only if it is not in delivery */
IF ( (l_del_id_for_detail.delivery_id is not null)
AND (l_del_id_for_container.delivery_id is null) ) THEN
-- K LPN CONV. rv
-- Based on assumption that we are using wsh_delivery_assignments_v,
-- delivery and its contents will belong to same organization.
-- Similarly, container and its contents will belong to same organization.
-- Hence, we are checking for WMS org or non-WMS org. at the
-- parent level (i.e. delivery/container)
-- rather than at line-level for performance reasons.
-- If this assumptions were to be violated in anyway
-- i.e Query was changed to refer to base table wsh_delivery_assignments instead of
-- wsh_delivery_assignments_v
-- or
-- if existing query were to somehow return/fetch records where
-- delivery and its contents may belong to diff. org.
-- container and its contents may belong to diff. org.
-- then
-- Calls to check_wms_org needs to be re-adjusted at
-- appropriate level (line/delivery/container).
-- K LPN CONV. rv
-- Bug 4452930
OPEN c_topmost_container(p_parent_detail_id);
x_assign_update => l_assign_update,
x_gross_weight1 => l_gross_weight1,
x_gross_weight2 => l_gross_weight2,
x_return_status => l_return_status);
UPDATE wsh_delivery_assignments_v
SET delivery_id = l_del_id_FOR_detail.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_sync_tmp_recTbl.delivery_detail_id_tbl(i);
IF (l_assign_update AND
l_gc3_is_installed = 'Y' AND
nvl(l_del_id_for_detail.ignore_for_planning, 'N') = 'N') THEN
-- when it comes here, OTM R12 update delivery is not called before
-- inside the same procedure, so can't use those variables
l_tms_interface_flag := NULL;
l_mdc_detail_tab.delete;
l_sync_tmp_recTbl.delivery_detail_id_tbl.delete;
l_sync_tmp_recTbl.parent_detail_id_tbl.delete;
l_sync_tmp_recTbl.delivery_id_tbl.delete;
l_sync_tmp_recTbl.operation_type_tbl.delete;
select organization_id,
nvl(line_direction,'O')
from wsh_delivery_details
where delivery_detail_id = p_cnt_inst_id
and container_flag = 'Y'
and source_code = 'WSH';
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_detail_id = p_detail_id;
l_del_tab.delete;
update_mol_carton_group_error exception;
SELECT status_code,planned_flag, initial_pickup_location_id,ultimate_dropoff_location_id,
customer_id, intmed_ship_to_location_id, fob_code, freight_terms_code, ship_method_code,
carrier_id, mode_of_transport, service_level,
-- deliveryMerge
batch_id,
NVL(shipment_direction,'O') shipment_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 ttrichy
organization_id -- K LPN CONV. rv
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT wdd.delivery_detail_id, wdd.released_status, wdd.container_flag, wdd.ship_from_location_id, wdd.ship_to_location_id, wda.delivery_id, wdd.move_order_line_id, wdd.organization_id,
wdd.freight_terms_code, -- J-IB-NPARIKH
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 ttrichy
mode_of_transport, carrier_id, service_level,
wda.parent_delivery_detail_id, -- K LPN CONV. rv
wdd.gross_weight -- OTM R12 : assign delivery detail
FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id and wdd.delivery_detail_id = c_detail_id;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
START WITH delivery_detail_id = p_detail_id
CONNECT BY prior delivery_detail_id = parent_delivery_detail_id;
select NVL(ignore_for_planning, 'N') ignore_for_planning
from wsh_delivery_details
where delivery_detail_id=p_detail_id;
SELECT meaning
FROM wsh_lookups
WHERE lookup_type = 'DELIVERY_STATUS'
AND lookup_code = l_code;
l_update_dlvy BOOLEAN;
l_tms_update VARCHAR2(1);
l_assign_update BOOLEAN;
l_assign_update := FALSE; --default assignment tms update to false
/* the value for the flag can be Y or N updated with Bug 1559785*/
IF ((l_del.status_code = 'CO') OR
(l_del.status_code = 'IT') OR
(l_del.status_code = 'CL') OR
(l_del.status_code = 'SR') OR -- sperera 940/945
(l_del.status_code = 'SC') OR
(l_del.planned_flag IN ('Y','F')))
AND l_del.shipment_direction IN ('O','IO') -- J-IB-NPARIKH
THEN
IF l_del.planned_flag IN ('Y','F') THEN
fnd_message.SET_name('WSH', 'WSH_PLAN_DEL_NOT_UPDATABLE');
x_assign_update => l_assign_update,
x_gross_weight1 => l_gross_weight1,
x_gross_weight2 => l_gross_weight2,
x_return_status => l_return_status);
SELECT l_dd_id(i) into l_dummy_del_det_id
FROM wsh_delivery_details
WHERE delivery_detail_id=l_dd_id(i)
FOR UPDATE NOWAIT;
l_sync_tmp_rec.operation_type := 'UPDATE';
UPDATE WSH_DELIVERY_DETAILS
SET ship_from_location_id = l_del.initial_pickup_location_id,
ship_to_location_id = l_del.ultimate_dropoff_location_id,
customer_id = l_del.customer_id,
intmed_ship_to_location_id = l_del.intmed_ship_to_location_id,
fob_code = l_del.fob_code,
freight_terms_code = l_del.freight_terms_code,
ship_method_code = l_del.ship_method_code,
service_level = l_del.service_level,
carrier_id = l_del.carrier_id,
mode_of_transport = l_del.mode_of_transport,
line_direction = l_detail.line_direction , -- J-IB-NPARIKH
shipping_control = l_del.shipping_control -- J-IB-NPARIKH
WHERE delivery_detail_id = l_dd_id(j);
l_tms_update := 'N';
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED)) THEN
l_tms_update := 'Y';
l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
WSH_DEBUG_SV.log(l_module_name, 'l_tms_update', l_tms_update);
IF (l_tms_update = 'Y') THEN
WSH_DEBUG_SV.log(l_module_name, 'l_new_interface_flag_tab', l_new_interface_flag_tab(1));
UPDATE WSH_NEW_DELIVERIES
SET MODE_OF_TRANSPORT = decode(mode_of_transport, NULL, l_mode_of_transport, mode_of_transport),
SERVICE_LEVEL = decode(service_level, NULL, l_service_level, service_level),
CARRIER_ID = decode(carrier_id, NULL, l_carrier_id, carrier_id),
SHIP_METHOD_CODE = decode(ship_method_code, NULL, l_ship_method, ship_method_code),
SHIPMENT_DIRECTION = l_detail.line_direction,
-- OTM R12
TMS_INTERFACE_FLAG = decode(l_tms_update, 'Y', l_new_interface_flag_tab(1), nvl(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
TMS_VERSION_NUMBER = decode(l_tms_update, 'Y', l_tms_version_number, nvl(tms_version_number, 1))
-- End of OTM R12
WHERE delivery_id = p_delivery_id;
IF (l_gc3_is_installed = 'Y' AND l_tms_update = 'Y') THEN
WSH_XC_UTIL.LOG_OTM_EXCEPTION(
p_delivery_info_tab => l_delivery_info_tab,
p_new_interface_flag_tab => l_new_interface_flag_tab,
x_return_status => l_return_status);
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_detail.delivery_detail_id;
WSH_DEBUG_SV.log(l_module_name,'Delivery updated Freight Terms',x_dlvy_freight_terms_code);
UPDATE wsh_new_deliveries
SET freight_terms_code = x_dlvy_freight_terms_code,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_id = p_delivery_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_MO_CANCEL_PVT.update_mol_carton_group',WSH_DEBUG_SV.C_PROC_LEVEL);
INV_MO_Cancel_PVT.update_mol_carton_group
(p_line_id => l_detail.move_order_line_id,
p_carton_grouping_id => p_delivery_id,
x_return_status => x_return_status,
x_msg_cnt => l_msg_count,
x_msg_data => l_msg_data);
RAISE update_mol_carton_group_error;
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 (
NULL,
p_delivery_id,
l_detail.delivery_detail_id,
x_return_status);
IF (l_assign_update AND
l_gc3_is_installed = 'Y' AND
nvl(l_del.ignore_for_planning, 'N') = 'N') THEN
IF (l_tms_update = 'Y') THEN
l_tms_interface_flag := l_new_interface_flag_tab(1);
l_del_tab.delete;
WHEN update_mol_carton_group_error THEN
fnd_message.SET_name('WSH', 'WSH_MOL_CARTON_GROUP_ERROR');
WSH_DEBUG_SV.logmsg(l_module_name,'update_mol_carton_group_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:update_mol_carton_group_error');
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
START WITH delivery_detail_id = x_delivery_detail_id
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
SELECT ship_to_location_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_container_id;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id = p_outer_cont_id
START WITH delivery_detail_id = p_inner_cont_id
CONNECT BY prior delivery_detail_id = parent_delivery_detail_id;
SELECT nvl(planned_flag,'N')
FROM wsh_new_deliveries
WHERE delivery_id = v_delivery_id;
SELECT count(*)
FROM wsh_delivery_assignments_v wda
WHERE wda.parent_delivery_detail_id = v_delivery_detail_id and rownum = 1;
SELECT nvl(minimum_fill_percent,0)
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_cont_id
AND container_flag = 'Y';
l_assign_update BOOLEAN;
l_assign_update := FALSE; --default assignment tms update to false
x_assign_update => l_assign_update,
x_gross_weight1 => l_gross_weight1,
x_gross_weight2 => l_gross_weight2,
x_return_status => l_return_status);
UPDATE wsh_delivery_assignments_v
SET delivery_id = l_del_id_for_container1.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_sync_tmp_recTbl.delivery_detail_id_tbl(i);
IF (l_assign_update AND
l_gc3_is_installed = 'Y' AND
nvl(l_del_id_for_container1.ignore_for_planning, 'N') = 'N') THEN
l_tms_interface_flag := NULL;
l_mdc_detail_tab.delete;
l_sync_tmp_recTbl.delivery_detail_id_tbl.delete;
l_sync_tmp_recTbl.parent_detail_id_tbl.delete;
l_sync_tmp_recTbl.delivery_id_tbl.delete;
l_sync_tmp_recTbl.operation_type_tbl.delete;
UPDATE wsh_delivery_assignments_v
SET delivery_id = l_del_id_for_container2.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_sync_tmp_recTbl.delivery_detail_id_tbl(i);
IF (l_assign_update AND
l_gc3_is_installed = 'Y' AND
nvl(l_del_id_for_container2.ignore_for_planning, 'N') = 'N') THEN
l_tms_interface_flag := NULL;
l_mdc_detail_tab.delete;
l_sync_tmp_recTbl.delivery_detail_id_tbl.delete;
l_sync_tmp_recTbl.parent_detail_id_tbl.delete;
l_sync_tmp_recTbl.delivery_id_tbl.delete;
l_sync_tmp_recTbl.operation_type_tbl.delete;
UPDATE wsh_delivery_assignments_v
SET parent_delivery_detail_id = p_detail_id2,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = p_detail_id1;
WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
p_detail_id_tab => l_mdc_detail_tab,
x_return_status => x_return_status);
select container_name into detail_cont_name
from wsh_delivery_details where delivery_detail_id = p_detail_id1;
select container_name into parent_cont_name
from wsh_delivery_details where delivery_detail_id = p_detail_id2;
SELECT status_code, planned_flag,name,
nvl(shipment_direction,'O') shipment_direction, -- J-IB-NPARIKH
ignore_for_planning -- OTM R12 : unassign delivery detail
FROM wsh_new_deliveries
WHERE delivery_id = p_del_id;
SELECT wda.delivery_id, wda.parent_delivery_detail_id,
wdd.organization_id,
wdd.weight_uom_code,
wdd.volume_uom_code,
wdd.inventory_item_id
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE wda.delivery_detail_id = p_detail_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and ((wda.delivery_id IS not null) or (wda.parent_delivery_detail_id IS not null));
update_mol_carton_group_error exception;
select move_order_line_id, organization_id
from wsh_delivery_details
where delivery_detail_id = p_del_det_id
and released_status = 'S';
select wsh_delivery_group_s.nextval from dual;
SELECT delivery_detail_id,
parent_delivery_detail_id, -- LPN CONV. rv
delivery_id -- LPN CONV. rv
FROM wsh_delivery_assignments_v
START WITH delivery_detail_id = p_detail_id
CONNECT BY prior delivery_detail_id = parent_delivery_detail_id;
SELECT meaning
FROM wsh_lookups
WHERE lookup_type = 'DELIVERY_STATUS'
AND lookup_code = l_code;
select organization_id,
nvl(line_direction,'O')
from wsh_delivery_details
where delivery_detail_id = p_cnt_inst_id
and container_flag = 'Y'
and source_code = 'WSH';
l_call_update VARCHAR2(1);
l_del_tab.delete;
SELECT delivery_detail_id, nvl(line_direction, 'O'), container_flag,
organization_id,
gross_weight -- OTM R12 : unassign delivery detail
INTO l_dummy_del_det_id, l_line_dir_tbl(i), l_cnt_flag_tbl(i),
l_det_org_id_tbl(i),
l_gross_weight_tbl(i) -- OTM R12 : unassign delivery detail
FROM wsh_delivery_details
WHERE delivery_detail_id=l_dd_id(i)
FOR UPDATE NOWAIT;
UPDATE wsh_delivery_assignments_v
SET delivery_id = l_null_delivery_id, --bugfix 3768823
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = l_dd_id(j);
UPDATE wsh_delivery_details
SET load_seq_number = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = l_dd_id(j);
WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
p_detail_id_tab => l_mdc_detail_tab,
x_return_status => x_return_status);
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,
mode_of_transport = NULL,
service_level = NULL,
carrier_id = NULL,
deliver_to_location_id = NULL,
-- tracking_number = NULL, Bug# 3632485
line_direction = DECODE(line_direction,'IO','O',line_direction), -- J-IB-NPARIKH
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = l_dd_id(j);
UPDATE wsh_delivery_details
SET ship_from_location_id = -1, -- J-IB-NPARIKH
routing_req_id = NULL, -- J-IB-NPARIKH
rcv_shipment_line_id = NULL, -- J-IB-NPARIKH
shipped_quantity = NULL, -- J-IB-NPARIKH
shipped_quantity2 = NULL, -- J-IB-NPARIKH
picked_quantity = NULL, -- J-IB-NPARIKH
picked_quantity2 = NULL, -- J-IB-NPARIKH
received_quantity = NULL, -- J-IB-NPARIKH
received_quantity2 = NULL, -- J-IB-NPARIKH
returned_quantity = NULL, -- J-IB-NPARIKH
returned_quantity2 = NULL, -- J-IB-NPARIKH
earliest_pickup_date = NULL, -- J-IB-NPARIKH
latest_pickup_date = NULL, -- J-IB-NPARIKH
released_status = DECODE(nvl(requested_quantity,0),0,
'D',
'X'), -- J-IB-NPARIKH
ignore_for_planning = 'Y', -- J-IB-NPARIKH
--wv_frozen_flag = DECODE(l_ib_upd_flag,'Y','N',wv_frozen_flag), -- J-IB-NPARIKH
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = l_dd_id(j)
returning released_status into l_rel_status;
WSH_DEBUG_SV.log(l_module_name,'Rel Status after the update is ', l_rel_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_MO_CANCEL_PVT.update_mol_carton_group',WSH_DEBUG_SV.C_PROC_LEVEL);
INV_MO_Cancel_PVT.update_mol_carton_group(
x_return_status => x_return_status,
x_msg_cnt => l_msg_count,
x_msg_data => l_msg_data,
p_line_id => l_mo_line_id,
p_carton_grouping_id => l_carton_grouping_id);
RAISE update_mol_carton_group_error;
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 '||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_assignments_v
SET parent_delivery_detail_id=NULL
WHERE delivery_detail_id = p_detail_id;
UPDATE wsh_delivery_details
SET load_seq_number = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = p_detail_id;
WHEN update_mol_carton_group_error THEN
fnd_message.SET_name('WSH', 'WSH_MOL_CARTON_GROUP_ERROR');
WSH_DEBUG_SV.logmsg(l_module_name,'update_mol_carton_group_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:update_mol_carton_group_error');
SELECT delivery_id, parent_delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id = c_delivery_Detail_id
AND ((delivery_id IS not null) or (parent_delivery_detail_id IS not null));
SELECT *
FROM wsh_new_deliveries
WHERE delivery_id = c_del_id;
SELECT wnd.delivery_id, wnd.organization_id
FROM wsh_new_deliveries wnd
WHERE wnd.delivery_id = p_del_id and wnd.shipment_direction='I'
and not exists ( SELECT wda.delivery_id
FROM wsh_delivery_assignments_v wda
WHERE wda.delivery_id =wnd.delivery_id );
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.update_freight_terms',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_NEW_DELIVERY_ACTIONS.update_freight_terms
(
p_delivery_id => l_dlvy_tbl(l_index).value,
p_action_code => 'UNASSIGN',
x_return_status => l_return_status,
x_freight_terms_code => l_dlvy_freight_terms_code
);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.update_freight_terms',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_NEW_DELIVERY_ACTIONS.update_freight_terms
(
p_delivery_id => l_dlvy_ext_tbl(l_index).value,
p_action_code => 'UNASSIGN',
x_return_status => l_return_status,
x_freight_terms_code => l_dlvy_freight_terms_code
);
SELECT delivery_id
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id = c_container_id;
l_del_tab.delete;
SELECT delivery_id, freight_Terms_code -- J-IB-NPARIKH
INTO l_delivery_id1, l_dlvy_freight_Terms_code
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id
FOR UPDATE NOWAIT;
l_serial_tab.delete;
l_dd_id_tab.delete;
l_transaction_id_tab.delete;
l_serial_number_tab.delete;
UPDATE wsh_delivery_details
SET serial_number = decode(l_serial_number_tab(i),FND_API.G_MISS_CHAR,NULL,
NULL,serial_number,l_serial_number_tab(i)) ,
transaction_temp_id = decode(l_transaction_id_tab(i),FND_API.G_MISS_NUM,NULL,
NULL,transaction_temp_id,l_transaction_id_tab(i))
WHERE delivery_detail_id = l_dd_id_tab(i);
B - called from update_inventory_info for OPM's use
M - called from update_inventory_info for OPM's use
S - called from update_inventory_info for OPM's use
U - called from update_inventory_info for OPM's use
*******************************************************************/
-- This API is called from split_delivery_details_bulk with
-- new parameters and functionality of Bulk creation of records
/*****************************************************
----- SPLIT_DETAIL_INT_BULK api
*****************************************************/
-- HW OPMCONV - Removed parameter p_process_flag
PROCEDURE Split_Detail_INT_bulk(
p_old_delivery_detail_rec IN SplitDetailRecType,
p_new_source_line_id IN NUMBER,
p_quantity_to_split IN NUMBER,
p_quantity_to_split2 IN NUMBER ,
p_unassign_flag IN VARCHAR2 ,
p_converted_flag IN VARCHAR2 ,
p_manual_split IN VARCHAR2 ,
p_split_sn IN VARCHAR2 ,
p_num_of_split IN NUMBER, -- for empty container cases
x_split_detail_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_dd_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type
) IS
l_new_delivery_detail_id number;
l_updated_delivery_detail_rec SplitDetailRecType;
l_req_qty_update_index NUMBER;
l_req_qty2_update_index NUMBER;
SELECT requested_quantity, picked_quantity,requested_quantity2
FROM wsh_delivery_details
WHERE delivery_detail_id = p_del_det;
SELECT NVL(decode(l_split_shipped_qty,FND_API.G_MISS_NUM,null,l_split_shipped_qty),
NVL(decode(l_delivery_details_info.picked_quantity,FND_API.G_MISS_NUM,null,l_delivery_details_info.picked_quantity),
decode(l_delivery_details_info.requested_quantity,FND_API.G_MISS_NUM,null,l_delivery_details_info.requested_quantity)))
INTO l_new_wv_qty
FROM dual;
l_delivery_details_info.last_update_date := SYSDATE;
l_delivery_details_info.last_updated_by := FND_GLOBAL.USER_ID;
l_delivery_details_info.last_update_login := FND_GLOBAL.LOGIN_ID;
l_updated_delivery_detail_rec := p_old_delivery_detail_rec;
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_old_delivery_detail_rec.delivery_detail_id,
p_primary_quantity => l_final_req_qty,
p_split_delivery_detail_id => l_dd_id_tab(i),
x_return_status => x_return_status );
WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
l_req_qty_update_index := CEIL(l_requested_quantity/l_delivery_details_info.requested_quantity);
l_req_qty2_update_index := CEIL(l_requested_quantity2/l_delivery_details_info.requested_quantity2);
l_req_qty2_update_index := NULL;
IF l_req_qty_update_index > (l_requested_quantity/l_delivery_details_info.requested_quantity) THEN
-- Bug 3178233 - Need to add the IF condition because the update will fail in the following example:
-- requested_quantity = 3, picked_quantity = 4, and split quantity is 2
-- l_req_qty_update_index = CEIL(3/2) = 2
-- Update at l_dd_id_tab(l_req_qty_update_index) = l_dd_id_tab(2) is a non-exist value which will fail.
IF l_req_qty_update_index <= l_dd_id_tab.count THEN
-- 12345 HW added qty2
update wsh_delivery_details
set requested_quantity = l_requested_quantity - ((l_req_qty_update_index - 1) * l_delivery_details_info.requested_quantity),
requested_quantity2 = decode(l_delivery_details_info.requested_quantity2, fnd_api.g_miss_num, NULL, l_requested_quantity2 - ((l_req_qty2_update_index - 1) * l_delivery_details_info.requested_quantity2))
where delivery_detail_id = l_dd_id_tab(l_req_qty_update_index);
FORALL i in (l_req_qty_update_index + 1) .. l_dd_id_tab.count
-- HW added qty2
update wsh_delivery_details
set requested_quantity = 0,
requested_quantity2 = 0
where delivery_detail_id = l_dd_id_tab(i);
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_dd_id_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
p_update_flag => 'Y',
x_net_weight => l_split_weight,
x_volume => l_split_volume,
x_return_status => l_split_return_status);
/* Bug 2177410, also update net_weight and volume of original delivery detail
because non item does not use WSH_WV_UTILS.Detail_Weight_Volume to
adjust the net_weight and volume */
x_split_detail_id := l_new_delivery_detail_id;
UPDATE wsh_delivery_details
SET requested_quantity = GREATEST(requested_quantity - (l_num_of_split*l_delivery_details_info.requested_quantity), 0),
requested_quantity2 = GREATEST(requested_quantity2 -(l_num_of_split*l_qty2), 0),
picked_quantity = GREATEST(picked_quantity - (l_num_of_split*p_quantity_to_split),0),
-- HW OPMCONV. No need to use l_new_pick_qty2
-- picked_quantity2 = GREATEST(picked_quantity2 - (l_num_of_split* nvl(l_qty2,l_new_pick_qty2)),0),
picked_quantity2 = GREATEST(picked_quantity2 - (l_num_of_split* l_qty2) ,0),
--shipped_quantity = l_original_shipped_qty - p_quantity_to_split,
shipped_quantity = l_original_shipped_qty - GREATEST((l_num_of_split -1) * p_quantity_to_split,0),
shipped_quantity2 = l_original_shipped_qty2 - GREATEST((l_num_of_split -1) * nvl(l_qty2,0),0),
--shipped_quantity2 = l_original_shipped_qty2 - ((greatest(l_num_of_split -1),0) * p_quantity_to_split),
cycle_count_quantity = l_original_cc_qty,
cycle_count_quantity2 = l_original_cc_qty2,
serial_number = decode(l_updated_delivery_detail_rec.serial_number,FND_API.G_MISS_CHAR,NULL,
NULL,serial_number,l_updated_delivery_detail_rec.serial_number),
to_serial_number = decode(l_updated_delivery_detail_rec.to_serial_number,FND_API.G_MISS_CHAR,NULL,
NULL,to_serial_number,l_updated_delivery_detail_rec.to_serial_number),
transaction_temp_id = decode(l_updated_delivery_detail_rec.transaction_temp_id,FND_API.G_MISS_NUM,NULL,
NULL,transaction_temp_id,l_updated_delivery_detail_rec.transaction_temp_id),
-- J: W/V Changes
gross_weight = gross_weight - l_total_gross_wt,
net_weight = net_weight - l_total_net_wt,
volume = volume - l_total_vol,
-- End J: W/V Changes
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = p_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);
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 => old_delivery_detail_rec.requested_quantity - p_req_quantity,
p_split_delivery_detail_id => x_new_detail_id,
x_return_status => x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
B - called from update_inventory_info for OPM's use
M - called from update_inventory_info for OPM's use
S - called from update_inventory_info for OPM's use
U - called from update_inventory_info for OPM's use
*******************************************************************/
/*****************************************************
----- SPLIT_DETAIL_INT api
*****************************************************/
-- HW OPMCONV. Removed p_process_flag
PROCEDURE Split_Detail_INT(
p_old_delivery_detail_rec IN SplitDetailRecType,
p_new_source_line_id IN NUMBER ,
p_quantity_to_split IN NUMBER,
p_quantity_to_split2 IN NUMBER ,
p_unassign_flag IN VARCHAR2 ,
p_converted_flag IN VARCHAR2 ,
p_manual_split IN VARCHAR2 ,
p_split_sn IN VARCHAR2 ,
x_split_detail_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_new_delivery_detail_id number;
l_updated_delivery_detail_rec SplitDetailRecType;
SELECT NVL(decode(l_split_recvd_qty,FND_API.G_MISS_NUM,null,l_split_recvd_qty),
NVL(decode(l_split_shipped_qty,FND_API.G_MISS_NUM,null,l_split_shipped_qty),
NVL(decode(l_delivery_details_info.picked_quantity,FND_API.G_MISS_NUM,null,l_delivery_details_info.picked_quantity),
decode(l_delivery_details_info.requested_quantity,FND_API.G_MISS_NUM,null,l_delivery_details_info.requested_quantity))))
INTO l_new_wv_qty
FROM dual;
l_delivery_details_info.last_update_date := SYSDATE;
l_delivery_details_info.last_updated_by := FND_GLOBAL.USER_ID;
l_delivery_details_info.last_update_login := FND_GLOBAL.LOGIN_ID;
l_updated_delivery_detail_rec := p_old_delivery_detail_rec;
x_old_detail_rec => l_updated_delivery_detail_rec,
x_new_delivery_detail_rec => l_delivery_details_info,
p_old_shipped_quantity => l_original_shipped_qty,
p_new_shipped_quantity => l_split_shipped_qty,
x_return_status => l_split_return_status);
p_update_flag => 'Y',
x_net_weight => l_split_weight,
x_volume => l_split_volume,
x_return_status => l_split_return_status);
/* Bug 2177410, also update net_weight and volume of original delivery detail
because non item does not use WSH_WV_UTILS.Detail_Weight_Volume to
adjust the net_weight and volume */
x_split_detail_id := l_new_delivery_detail_id;
WSH_DEBUG_SV.logmsg(l_module_name, 'Before Update wsh_delivery_details');
WSH_DEBUG_SV.log(l_module_name, 'serial_number',l_updated_delivery_detail_rec.serial_number);
WSH_DEBUG_SV.log(l_module_name, 'to_serial_number',l_updated_delivery_detail_rec.to_serial_number);
WSH_DEBUG_SV.log(l_module_name, 'transaction_temp_id',l_updated_delivery_detail_rec.transaction_temp_id);
UPDATE wsh_delivery_details
SET requested_quantity = l_original_req_qty, -- J-IB-NPARIKH, GREATEST(requested_quantity - l_delivery_details_info.requested_quantity, 0),
requested_quantity2 = l_original_req_qty2, -- J-IB-NPARIKH, GREATEST(requested_quantity2 - l_qty2, 0),
picked_quantity = l_original_picked_qty, -- J-IB-NPARIKH, picked_quantity - p_quantity_to_split,
picked_quantity2 = l_original_picked_qty2, -- J-IB-NPARIKH, picked_quantity2 - nvl(l_qty2,l_new_pick_qty2),
shipped_quantity = l_original_shipped_qty,
shipped_quantity2 = l_original_shipped_qty2,
received_quantity = l_original_recvd_qty, -- J-IB-NPARIKH
received_quantity2 = l_original_recvd_qty2, -- J-IB-NPARIKH
--returned_quantity = l_original_rtv_qty, -- J-IB-NPARIKH
--returned_quantity2 = l_original_rtv_qty2, -- J-IB-NPARIKH
cycle_count_quantity = l_original_cc_qty,
cycle_count_quantity2 = l_original_cc_qty2,
serial_number = decode(l_updated_delivery_detail_rec.serial_number,FND_API.G_MISS_CHAR,NULL,
NULL,serial_number,l_updated_delivery_detail_rec.serial_number),
to_serial_number = decode(l_updated_delivery_detail_rec.to_serial_number,FND_API.G_MISS_CHAR,NULL,
NULL,to_serial_number,l_updated_delivery_detail_rec.to_serial_number),
transaction_temp_id = decode(l_updated_delivery_detail_rec.transaction_temp_id, FND_API.G_MISS_NUM,NULL,
NULL,transaction_temp_id,l_updated_delivery_detail_rec.transaction_temp_id),
-- J: W/V Changes
gross_weight = gross_weight - l_total_gross_wt,
net_weight = net_weight - l_total_net_wt,
volume = volume - l_total_vol,
-- End J: W/V Changes
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = p_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);
SELECT msnt.rowid,
msnt.transaction_temp_id,
msnt.fm_serial_number,
msnt.to_serial_number,
msnt.attribute_category,
msnt.attribute1,
msnt.attribute2,
msnt.attribute3,
msnt.attribute4,
msnt.attribute5,
msnt.attribute6,
msnt.attribute7,
msnt.attribute8,
msnt.attribute9,
msnt.attribute10,
msnt.attribute11,
msnt.attribute12,
msnt.attribute13,
msnt.attribute14,
msnt.attribute15,
msnt.dff_updated_flag
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = x_tt_id
ORDER BY msnt.fm_serial_number DESC;
select mtl_material_transactions_s.nextval
from dual;
UPDATE mtl_serial_numbers_temp
SET to_serial_number = l_old_to_sn,
serial_prefix = TO_CHAR(l_range_count - l_qty_to_split),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE rowid = c.rowid;
'inserting into mtl_serial_numbers_temp'
,l_transaction_temp_id);
INSERT INTO mtl_serial_numbers_temp
(TRANSACTION_TEMP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
SERIAL_PREFIX,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
DFF_UPDATED_FLAG)
VALUES
(l_transaction_temp_id,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_new_sn,
l_new_to_sn,
TO_CHAR(l_qty_to_split),
c.attribute_category,
c.attribute1,
c.attribute2,
c.attribute3,
c.attribute4,
c.attribute5,
c.attribute6,
c.attribute7,
c.attribute8,
c.attribute9,
c.attribute10,
c.attribute11,
c.attribute12,
c.attribute13,
c.attribute14,
c.attribute15,
c.dff_updated_flag
);
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_transaction_temp_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE rowid = c.rowid;
DELETE mtl_serial_numbers_temp
WHERE rowid = c.rowid;
SELECT parent_delivery_detail_id,
delivery_id -- Bug#3542095
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id = detail_id;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE parent_delivery_detail_id = cont_id
AND delivery_detail_id <> detail_id;
select organization_id,
nvl(line_direction,'O')
from wsh_delivery_details
where delivery_detail_id = p_detail_id;
insert into wsh_wms_sync_tmp
(delivery_detail_id,
operation_type,
creation_date)
values (l_parent_container_id(1),
'DELETE',
WSH_WMS_LPN_GRP.G_HW_TIME_STAMP);
SELECT message,
exception_name,
trip_id,
trip_name,
trip_stop_id,
delivery_id,
delivery_name,
delivery_assignment_id,
container_name,
inventory_item_id,
lot_number,
-- HW OPMCONV - No need for sublot_number
-- sublot_number,
revision,
serial_number,
unit_of_measure,
unit_of_measure2,
subinventory,
locator_id,
arrival_date,
departure_date,
error_message,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
logged_at_location_id,
logging_entity,
logging_entity_id,
exception_location_id,
manually_logged,
batch_id,
status
--select *
from wsh_exceptions
where delivery_detail_id = p_old_delivery_detail_id;
select requested_quantity,requested_quantity2 into l_qty1,l_qty2
from wsh_delivery_details
where delivery_detail_id = p_new_delivery_detail_id;
SELECT wdd.delivery_detail_id, wdd.requested_quantity,wdd.requested_quantity2
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.source_line_id = p_source_line_id
AND wdd.delivery_detail_id <> p_delivery_detail_id
AND wdd.released_status = 'B'
AND wdd.replenishment_status IS NULL --bug# 6749200 (replenishment project)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.container_flag = 'N'
AND wdd.source_code = 'OE' -- Enables the Consolidation ONLY for the lines imported from Order Management.
AND (( wda.delivery_id is NULL AND wda.parent_delivery_detail_id is NULL )
OR ( wda.delivery_id = nvl(p_delivery_id,-99)))
FOR UPDATE NOWAIT;
SELECT shipping_instructions,
packing_instructions
FROM oe_order_lines_all
WHERE line_id = p_line_id;
l_delete_dd_ids WSH_UTIL_CORE.Id_Tab_Type; -- To store the Del Det to be deleted
l_temp_cnt := l_delete_dd_ids.COUNT;
l_delete_dd_ids(l_delete_dd_ids.COUNT + 1) :=l_del_det_Id;
IF ( l_delete_dd_ids.COUNT > l_temp_cnt ) --{ Consolidation is possible or not
THEN
-- Use the last Deliver Detail found for the consolidation purpose, accordingly
-- delete the delivery details id from l_delete_dd_ids table.
l_cons_dd_ids (l_cons_dd_ids.COUNT + 1) := l_delete_dd_ids(l_delete_dd_ids.COUNT);
l_delete_dd_ids.delete(l_delete_dd_ids.COUNT);
l_delete_dd_ids(l_delete_dd_ids.COUNT+1):= p_Cons_Source_Line_Rec_Tab(i).delivery_detail_id;
WSH_DEBUG_SV.log(l_module_name,'Deleting the Del Det Id (Bo Qty = Req qty)',l_delete_dd_ids(l_delete_dd_ids.COUNT));
FOR i IN 1..l_delete_dd_ids.COUNT
LOOP -- {
-- deleting the delivery detail line
--
-- Debug Statements
--
IF l_debug_on THEN
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_delivery_detail_id => l_delete_dd_ids(i),
x_return_status => l_return_status );
WSH_DEBUG_SV.logmsg(l_module_name, 'After calling DELETE_DELIVERY_DETAILS: ' || l_return_status );
UPDATE wsh_delivery_details
SET requested_quantity = l_cons_qtys(i),
requested_quantity2 = l_cons_qty2s(i),
tracking_number = null,
master_container_item_id = null,
detail_container_item_id = null,
seal_code = null,
shipping_instructions = l_ship_instructions(i),
packing_instructions = l_pack_instructions(i)
WHERE delivery_detail_id = l_cons_dd_ids (i);
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_cons_dd_ids,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
DELETE FROM WSH_FREIGHT_COSTS
WHERE delivery_detail_id = l_cons_dd_ids(i);
WSH_DEBUG_SV.log(l_module_name,'Freight Cost Rows deleted',SQL%ROWCOUNT);
p_update_flag => 'Y',
p_post_process_flag => 'Y',
p_calc_wv_if_frozen => 'Y',
x_net_weight => l_tmp_weight,
x_volume => l_tmp_volume,
x_return_status => l_return_status);
UPDATE wsh_delivery_details
SET requested_quantity = l_partial_req_qtys(i),
requested_quantity2 = l_partial_req_qty2s(i)
WHERE delivery_detail_id = l_partial_dd_ids (i);
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_partial_dd_ids,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
SELECT gross_weight,
net_weight,
volume,
nvl(wv_frozen_flag,'Y')
INTO l_gross_weight,
l_net_weight,
l_volume,
l_wv_frozen_flag
FROM wsh_delivery_details
WHERE delivery_detail_id = l_partial_dd_ids(l_index);
UPDATE wsh_delivery_details
set gross_weight = l_new_gross_wt,
net_weight = l_new_net_wt,
volume = l_new_vol
WHERE delivery_detail_id = l_partial_dd_ids(l_index);
p_delete_flag IN VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_delivery_detail_ids WSH_UTIL_CORE.Id_Tab_Type;
WSH_DEBUG_SV.log(l_module_name,'P_DELETE_FLAG',P_DELETE_FLAG);
IF (p_delete_flag = 'Y') THEN --{
WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont (
p_ids_tobe_unassigned => l_delivery_detail_ids ,
p_validate_flag => 'N',
x_return_status => l_return_status
);
IF (p_delete_flag = 'Y') THEN --{
--
-- Debug Statements
--
IF l_debug_on THEN
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_delivery_detail_id => p_delivery_detail_id,
p_cancel_flag => 'N',
x_return_status => l_return_status);
l_freight_detail_ids WSH_UTIL_CORE.Id_Tab_Type; -- Stores the delivery details for which the freight costs need to be deleted.
l_delete_dd_ids WSH_UTIL_CORE.Id_Tab_Type; -- Stores the all delivery details need to be deleted(that are getting
l_delivery_details_tab.DELETE(l_next_idx);
l_delivery_details_tab.DELETE(l_idx);
-- Delete the delivery_details of l_curr_line_id from the pl/sql table l_detail_ids,
-- after getting the corresponding backorder quantity.
l_total_bo_qty := 0; -- Used to store the Consolidated BO qty.
p_delete_flag => 'Y',
p_bo_qty2s => l_bo_qty2s(l_cmp_idx),
x_return_status => l_return_status
);
-- quantity and later update its requested quantity. This delivery detail should not be
-- deleted ( pass p_delete_flag as 'N' to process_delivery_details).
ELSIF ((l_bo_qtys(l_cmp_idx) > 0 OR l_overpick_qtys(l_cmp_idx) > 0)) THEN
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Process_Delivery_Details',WSH_DEBUG_SV.C_PROC_LEVEL);
p_delete_flag => 'N',
p_bo_qty2s => l_bo_qty2s(l_cmp_idx),
x_return_status => l_return_status
);
update wsh_delivery_details
set requested_quantity = requested_quantity - l_bo_qtys(l_cmp_idx),
requested_quantity2 = requested_quantity2 - l_bo_qty2s(l_cmp_idx),
picked_quantity = picked_quantity - l_bo_qtys(l_cmp_idx) - l_overpick_qtys(l_cmp_idx),
picked_quantity2 = picked_quantity2 - l_bo_qty2s(l_cmp_idx),
cycle_count_quantity = 0,
cycle_count_quantity2 = 0
where delivery_detail_id = l_detail_ids(l_cmp_idx);
l_detail_ids.DELETE(l_cmp_idx);
-- delivery detail with l_cons_dd_flags(l_cmp_idx)='Y' should be deleted from l_detail_ids.
ELSIF ( l_line_ids(l_cmp_idx) = l_curr_line_id ) THEN
l_detail_ids.DELETE(l_cmp_idx);
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
-- Delete the Freight Costs only if Consolidation happens and
-- if delivery detail is going to be completely backordered.
IF (l_total_bo_qty > 0 AND
x_cons_delivery_details_tab(l_next_idx).req_qty = x_cons_delivery_details_tab(l_next_idx).bo_qty) THEN
l_freight_detail_ids(l_freight_detail_ids.COUNT+1) := x_cons_delivery_details_tab(l_next_idx).delivery_detail_id;
DELETE FROM WSH_FREIGHT_COSTS
WHERE delivery_detail_id = l_freight_detail_ids(i);
WSH_DEBUG_SV.log(l_module_name,'Freight Cost Rows deleted',SQL%ROWCOUNT);
l_req_qtys.DELETE;
l_bo_qtys.DELETE;
l_bo_qty2s.DELETE;
PROCEDURE Delete_Consol_Record(
p_detail_id_tab IN wsh_util_core.id_tab_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'delete_consol_record';
update wsh_delivery_assignments
set type = 'S'
where type = 'O'
and delivery_detail_id = p_detail_id_tab(i);
delete from wsh_delivery_assignments
where delivery_detail_id = p_detail_id_tab(i)
and type = 'C';
END Delete_Consol_Record;
select l1.delivery_id, l2.delivery_id
from wsh_delivery_legs l1, wsh_delivery_legs l2, wsh_delivery_assignments a
where a.delivery_detail_id = p_det_id
and l1.delivery_id = a.delivery_id
and l1.parent_delivery_leg_id = l2.delivery_leg_id
and a.parent_delivery_detail_id is NULL
and NVL(a.type, 'S') = 'S';
update wsh_delivery_assignments
set type = 'O',
parent_delivery_detail_id = NULL
where NVL(type, 'S') = 'S'
and delivery_detail_id = l_detail_id_tab(i);
INSERT INTO wsh_delivery_assignments (
delivery_id,
parent_delivery_id,
delivery_detail_id,
parent_delivery_detail_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
active_flag,
delivery_assignment_id,
type
) VALUES (
l_delivery_id_tab(i),
l_consol_delivery_id_tab(i),
l_detail_id_tab(i),
NULL,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
NULL,
NULL,
NULL,
NULL,
NULL,
wsh_delivery_assignments_s.nextval,
'C'
);